],
},
+ 'cust_event_fee' => {
+ 'columns' => [
+ 'eventfeenum', 'serial', '', '', '', '',
+ 'eventnum', 'int', '', '', '', '',
+ 'billpkgnum', 'int', 'NULL', '', '', '',
+ 'feepart', 'int', '', '', '', '',
+ ],
+ 'primary_key' => 'eventfeenum', # I'd rather just use eventnum
+ 'unique' => [ [ 'billpkgnum' ], [ 'eventnum' ] ], # one-to-one link
+ 'index' => [ [ 'feepart' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'eventnum' ],
+ table => 'cust_event',
+ },
+ { columns => [ 'billpkgnum' ],
+ table => 'cust_bill_pkg',
+ },
+ { columns => [ 'feepart' ],
+ table => 'part_fee',
+ },
+ ],
+ },
+
'cust_bill_pkg' => {
'columns' => [
'billpkgnum', 'serial', '', '', '', '',
'freq', 'varchar', 'NULL', $char_d, '', '',
'quantity', 'int', 'NULL', '', '', '',
'hidden', 'char', 'NULL', 1, '', '',
+ 'feepart', 'int', 'NULL', '', '', '',
],
'primary_key' => 'billpkgnum',
'unique' => [],
table => 'part_pkg',
references => [ 'pkgpart' ],
},
+ { columns => [ 'feepart' ],
+ table => 'part_fee',
+ },
],
},
'format', 'char', 'NULL', 1, '', '',
'classnum', 'int', 'NULL', '', '', '',
'duration', 'int', 'NULL', '', 0, '',
- 'phonenum', 'varchar', 'NULL', 25, '', '',
+ 'phonenum', 'varchar', 'NULL', 255, '', '', # has to hold a service label
'accountcode', 'varchar', 'NULL', 20, '', '',
'startdate', @date_type, '', '',
'regionname', 'varchar', 'NULL', $char_d, '', '',
'cust_bill_pkg_display' => {
'columns' => [
- 'billpkgdisplaynum', 'serial', '', '', '', '',
+ 'billpkgdisplaynum', 'serial', '', '', '', '',
'billpkgnum', 'int', '', '', '', '',
'section', 'varchar', 'NULL', $char_d, '', '',
#'unitsetup', @money_typen, '', '', #override the linked real one?
],
},
+ 'cust_bill_pkg_fee' => {
+ 'columns' => [
+ 'billpkgfeenum', 'serial', '', '', '', '',
+ 'billpkgnum', 'int', '', '', '', '',
+ 'base_invnum', 'int', '', '', '', '',
+ 'base_billpkgnum', 'int', 'NULL', '', '', '',
+ 'amount', @money_type, '', '',
+ ],
+ 'primary_key' => 'billpkgfeenum',
+ 'unique' => [],
+ 'index' => [ ['billpkgnum'],
+ ['base_invnum'],
+ ['base_billpkgnum'],
+ ],
+ 'foreign_keys' => [
+ { columns => [ 'billpkgnum' ],
+ table => 'cust_bill_pkg',
+ },
+ { columns => [ 'base_billpkgnum' ],
+ table => 'cust_bill_pkg',
+ references => [ 'billpkgnum' ],
+ },
+ { columns => [ 'base_invnum' ],
+ table => 'cust_bill',
+ references => [ 'invnum' ],
+ },
+ ],
+ },
+
'cust_bill_pkg_tax_location' => {
'columns' => [
'billpkgtaxlocationnum', 'serial', '', '', '', '',
{ columns => [ 'billpkgnum' ],
table => 'cust_bill_pkg',
},
- { columns => [ 'pkgnum' ],
- table => 'cust_pkg',
- },
+ #{ columns => [ 'pkgnum' ],
+ # table => 'cust_pkg',
+ #}, # taxes can apply to fees
{ columns => [ 'locationnum' ],
table => 'cust_location',
},
'format', 'char', 'NULL', 1, '', '',
'classnum', 'int', 'NULL', '', '', '',
'duration', 'int', 'NULL', '', 0, '',
- 'phonenum', 'varchar', 'NULL', 25, '', '',
+ 'phonenum', 'varchar', 'NULL', 255, '', '',
'accountcode', 'varchar', 'NULL', 20, '', '',
'startdate', @date_type, '', '',
'regionname', 'varchar', 'NULL', $char_d, '', '',
'commission_agentnum', 'int', 'NULL', '', '', '', #
'commission_salesnum', 'int', 'NULL', '', '', '', #
'commission_pkgnum', 'int', 'NULL', '', '', '', #
+ 'credbatch', 'varchar', 'NULL', $char_d, '', '',
],
'primary_key' => 'crednum',
'unique' => [],
'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'],
- ['commission_salesnum'],
+ ['commission_salesnum'], ['credbatch'],
],
'foreign_keys' => [
{ columns => [ 'custnum' ],
],
},
- 'cust_recon' => { # (some sort of not-well understood thing for OnPac)
- 'columns' => [
- 'reconid', 'serial', '', '', '', '',
- 'recondate', @date_type, '', '',
- 'custnum', 'int' , '', '', '', '',
- 'agentnum', 'int', '', '', '', '',
- 'last', 'varchar', '', $char_d, '', '',
- 'first', 'varchar', '', $char_d, '', '',
- 'address1', 'varchar', '', $char_d, '', '',
- 'address2', 'varchar', 'NULL', $char_d, '', '',
- 'city', 'varchar', '', $char_d, '', '',
- 'state', 'varchar', 'NULL', $char_d, '', '',
- 'zip', 'varchar', 'NULL', 10, '', '',
- 'pkg', 'varchar', 'NULL', $char_d, '', '',
- 'adjourn', @date_type, '', '',
- 'status', 'varchar', 'NULL', 10, '', '',
- 'agent_custid', 'varchar', '', $char_d, '', '',
- 'agent_pkg', 'varchar', 'NULL', $char_d, '', '',
- 'agent_adjourn', @date_type, '', '',
- 'comments', 'text', 'NULL', '', '', '',
- ],
- 'primary_key' => 'reconid',
- 'unique' => [],
- 'index' => [],
- },
-
'contact_class' => {
'columns' => [
'classnum', 'serial', '', '', '', '',
],
},
+ 'cust_main_credit_limit' => {
+ 'columns' => [
+ 'creditlimitnum', 'serial', '', '', '', '',
+ 'custnum', 'int', '', '', '', '',
+ '_date', @date_type, '', '',
+ 'amount', @money_typen, '', '',
+ #'amount_currency', 'char', 'NULL', 3, '', '',
+ 'credit_limit', @money_typen, '', '',
+ #'credit_limit_currency', 'char', 'NULL', 3, '', '',
+ ],
+ 'primary_key' => 'creditlimitnum',
+ 'unique' => [],
+ 'index' => [ ['custnum'], ],
+ 'foreign_keys' => [
+ { columns => [ 'custnum' ],
+ table => 'cust_main',
+ },
+ ],
+ },
+
'cust_main_note' => {
'columns' => [
'notenum', 'serial', '', '', '', '',
'currency', 'char', 'NULL', 3, '', '',
'otaker', 'varchar', 'NULL', 32, '', '',
'usernum', 'int', 'NULL', '', '', '',
- 'reason', 'varchar', '', $char_d, '', '',
+ 'reason', 'varchar', 'NULL', $char_d, '', '',
+ 'reasonnum', 'int', 'NULL', '', '', '',
'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should
# be index into payby
# table eventually
{ columns => [ 'usernum' ],
table => 'access_user',
},
+ { columns => [ 'reasonnum' ],
+ table => 'reason',
+ },
{ columns => [ 'gatewaynum' ],
table => 'payment_gateway',
},
],
},
- 'part_pkg' => {
+ 'part_pkg' => {
'columns' => [
'pkgpart', 'serial', '', '', '', '',
'pkg', 'varchar', '', $char_d, '', '',
- 'comment', 'varchar', '', 2*$char_d, '', '',
+ 'comment', 'varchar', 'NULL', 2*$char_d, '', '',
'promo_code', 'varchar', 'NULL', $char_d, '', '',
'freq', 'varchar', '', $char_d, '', '', #billing frequency
'setuptax', 'char', 'NULL', 1, '', '',
'successor', 'int', 'NULL', '', '', '',
'family_pkgpart','int', 'NULL', '', '', '',
'delay_start', 'int', 'NULL', '', '', '',
+ 'agent_pkgpartid', 'varchar', 'NULL', 20, '', '',
],
'primary_key' => 'pkgpart',
'unique' => [],
'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'classnum' ],
- [ 'agentnum' ], ['no_auto'],
+ [ 'agentnum' ], ['no_auto'], ['agent_pkgpartid'],
],
'foreign_keys' => [
{ columns => [ 'classnum' ],
'index' => [],
},
+ 'part_pkg_usageprice' => {
+ 'columns' => [
+ 'usagepricepart', 'serial', '', '', '', '',
+ 'pkgpart', 'int', '', '', '', '',
+ 'price', @money_type, '', '',
+ 'currency', 'char', 'NULL', 3, '', '',
+ 'action', 'varchar', '', $char_d, '', '',
+ 'target', 'varchar', '', $char_d, '', '',
+ 'amount', 'varchar', '', $char_d, '', '',
+ ],
+ 'primary_key' => 'usagepricepart',
+ 'unique' => [ [ 'pkgpart', 'currency', 'target' ] ],
+ 'index' => [ [ 'pkgpart' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'pkgpart' ],
+ table => 'part_pkg',
+ },
+ ],
+ },
+
+ 'cust_pkg_usageprice' => {
+ 'columns' => [
+ 'usagepricenum', 'serial', '', '', '', '',
+ 'pkgnum', 'int', '', '', '', '',
+ 'usagepricepart', 'int', '', '', '', '',
+ 'quantity', 'int', '', '', '', '',
+ ],
+ 'primary_key' => 'usagepricenum',
+ 'unique' => [ [ 'pkgnum', 'usagepricepart' ] ],
+ 'index' => [ [ 'pkgnum' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'pkgnum' ],
+ table => 'cust_pkg',
+ },
+ { columns => [ 'usagepricepart' ],
+ table => 'part_pkg_usageprice',
+ },
+ ],
+ },
+
+ 'part_fee' => {
+ 'columns' => [
+ 'feepart', 'serial', '', '', '', '',
+ 'itemdesc', 'varchar', '', $char_d, '', '',
+ 'comment', 'varchar', 'NULL', 2*$char_d, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ 'classnum', 'int', 'NULL', '', '', '',
+ 'taxclass', 'varchar', 'NULL', $char_d, '', '',
+ 'taxproductnum', 'int', 'NULL', '', '', '',
+ 'pay_weight', 'real', 'NULL', '', '', '',
+ 'credit_weight', 'real', 'NULL', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'amount', @money_type, '', '',
+ 'percent', 'decimal', '', '7,4', '', '',
+ 'basis', 'varchar', '', 16, '', '',
+ 'minimum', @money_typen, '', '',
+ 'maximum', @money_typen, '', '',
+ 'limit_credit', 'char', 'NULL', 1, '', '',
+ 'setuprecur', 'char', '', 5, '', '',
+ 'taxable', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'feepart',
+ 'unique' => [],
+ 'index' => [ [ 'disabled' ], [ 'classnum' ], [ 'agentnum' ]
+ ],
+ 'foreign_keys' => [
+ { columns => [ 'classnum' ],
+ table => 'pkg_class',
+ },
+ { columns => [ 'taxproductnum' ],
+ table => 'part_pkg_taxproduct',
+ },
+ { columns => [ 'agentnum' ],
+ table => 'agent',
+ },
+ ],
+ },
+
+ 'part_fee_msgcat' => {
+ 'columns' => [
+ 'feepartmsgnum', 'serial', '', '', '', '',
+ 'feepart', 'int', '', '', '', '',
+ 'locale', 'varchar', '', 16, '', '',
+ 'itemdesc', 'varchar', '', $char_d, '', '', #longer/no limit?
+ 'comment', 'varchar', 'NULL', 2*$char_d, '', '', #longer/no limit?
+ ],
+ 'primary_key' => 'feepartmsgnum',
+ 'unique' => [ [ 'feepart', 'locale' ] ],
+ 'index' => [],
+ 'foreign_keys' => [
+ { columns => [ 'feepart' ],
+ table => 'part_fee',
+ },
+ ],
+ },
+
+
'part_pkg_link' => {
'columns' => [
'pkglinknum', 'serial', '', '', '', '',
'lastapp', 'varchar', '', $char_d, \"''", '',
'lastdata', 'varchar', '', 255, \"''", '',
- #currently only opensips
+ #currently only opensips & voipswitch
'src_ip_addr', 'varchar', 'NULL', 15, '', '',
+
+ #currently only opensips
'dst_ip_addr', 'varchar', 'NULL', 15, '', '',
#currently only u4:
'charged_party_imsi', 'varchar', 'NULL', $char_d, '', '',
'upstream_price', 'decimal', 'NULL', '10,5', '', '',
+
+ #currently only voipswitch
+ #'upstream_rate', 'decimal', 'NULL', '10,5', '', '',
+
'upstream_src_regionname', 'varchar', 'NULL', $char_d, '', '',
'upstream_dst_regionname', 'varchar', 'NULL', $char_d, '', '',
],
},
+ 'pbx_extension' => {
+ 'columns' => [
+ 'extensionnum', 'serial', '', '', '', '',
+ 'svcnum', 'int', '', '', '', '',
+ 'extension', 'varchar', '', $char_d, '', '',
+ 'pin', 'varchar', 'NULL', $char_d, '', '',
+ 'sip_password', 'varchar', 'NULL', $char_d, '', '',
+ 'phone_name', 'varchar', 'NULL', $char_d, '', '',
+ ],
+ 'primary_key' => 'extensionnum',
+ 'unique' => [ [ 'svcnum', 'extension' ] ],
+ 'index' => [ [ 'svcnum' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'svcnum' ],
+ table => 'svc_pbx',
+ },
+ ],
+ },
+
+ 'pbx_device' => {
+ 'columns' => [
+ 'devicenum', 'serial', '', '', '', '',
+ 'devicepart', 'int', '', '', '', '',
+ 'svcnum', 'int', '', '', '', '',
+ 'mac_addr', 'varchar', 'NULL', 12, '', '',
+ ],
+ 'primary_key' => 'devicenum',
+ 'unique' => [ [ 'mac_addr' ], ],
+ 'index' => [ [ 'devicepart' ], [ 'svcnum' ], ],
+ 'foreign_keys' => [
+ { columns => [ 'devicepart' ],
+ table => 'part_device',
+ },
+ { columns => [ 'svcnum' ],
+ table => 'svc_pbx',
+ },
+ ],
+ },
+
+ 'extension_device' => {
+ 'columns' => [
+ 'extensiondevicenum', 'serial', '', '', '', '',
+ 'extensionnum', 'int', '', '', '', '',
+ 'devicenum', 'int', '', '', '', '',
+ ],
+ 'primary_key' => 'extensiondevicenum',
+ 'unique' => [ [ 'extensionnum', 'devicenum' ] ],
+ 'index' => [],#both? which way do we need to query?
+ 'foreign_keys' => [
+ { columns => [ 'extensionnum' ],
+ table => 'pbx_extension',
+ },
+ { columns => [ 'devicenum' ],
+ table => 'pbx_device',
+ },
+ ],
+ },
+
'svc_mailinglist' => { #svc_group?
'columns' => [
'svcnum', 'int', '', '', '', '',
'index' => [],
},
+ 'svc_conferencing' => {
+ 'columns' => [
+ 'svcnum', 'int', '', '', '', '',
+ 'conf_id', 'int', 'NULL', '', '', '', #"system assigned"
+ 'conf_name', 'varchar', '', $char_d, '', '',
+ 'conf_password', 'varchar', '', $char_d, '', '',
+ 'access_code', 'varchar', '', 16, '', '',
+ 'duration', 'int', '', '', '', '',
+ 'participants', 'int', '', '', '', '',
+ 'conftypenum', 'int', '', '', '', '',
+ 'confqualitynum', 'int', '', '', '', '',
+ 'opt_recording', 'char', 'NULL', 1, '', '',
+ 'opt_sip', 'char', 'NULL', 1, '', '',
+ 'opt_phone', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'svcnum',
+ 'unique' => [],
+ 'index' => [],
+ 'foreign_keys' => [
+ { columns => [ 'svcnum' ],
+ table => 'cust_svc',
+ },
+ { columns => [ 'conftypenum' ],
+ table => 'conferencing_type',
+ },
+ { columns => [ 'confqualitynum' ],
+ table => 'conferencing_quality',
+ },
+ ],
+ },
+
+ 'conferencing_type' => {
+ 'columns' => [
+ 'conftypenum', 'int', '', '', '', '',
+ 'typeid' , 'int', '', '', '', '',
+ 'typename', 'varchar', '', $char_d, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'conftypenum',
+ 'unique' => [ [ 'typeid', 'disabled' ], [ 'typename', 'disabled' ] ],
+ 'index' => [],
+ },
+
+ 'conferencing_quality' => {
+ 'columns' => [
+ 'confqualitynum', 'int', '', '', '', '',
+ 'qualityid' , 'int', '', '', '', '',
+ 'qualityname', 'varchar', '', $char_d, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'confqualitynum',
+ 'unique' => [ [ 'qualityid', 'disabled' ], [ 'qualityname', 'disabled' ] ],
+ 'index' => [],
+ },
+
+ 'svc_video' => {
+ 'columns' => [
+ 'svcnum', 'int', '', '', '', '',
+ 'smartcard_num', 'varchar', '', 16, '', '',
+ 'mac_addr', 'varchar', '', 12, '', '',
+ 'duration', 'int', '', '', '', '',
+ ],
+ 'primary_key' => 'svcnum',
+ 'unique' => [], # [ 'smartcard_num' ], [ 'mac_addr' ], ],
+ 'index' => [],
+ 'foreign_keys' => [
+ { columns => [ 'svcnum' ],
+ table => 'cust_svc',
+ },
+ ],
+ },
+
'vend_main' => {
'columns' => [
'vendnum', 'serial', '', '', '', '',
'latexnotes', 'text', 'NULL', '', '', '',
'latexfooter', 'text', 'NULL', '', '', '',
'latexsummary', 'text', 'NULL', '', '', '',
- 'latexcoupon', 'text', 'NULL', '', '', '',
'latexsmallfooter', 'text', 'NULL', '', '', '',
'latexreturnaddress', 'text', 'NULL', '', '', '',
- 'latextopmargin', 'varchar', 'NULL', 16, '', '',
- 'latexheadsep', 'varchar', 'NULL', 16, '', '',
- 'latexaddresssep', 'varchar', 'NULL', 16, '', '',
- 'latextextheight', 'varchar', 'NULL', 16, '', '',
- 'latexextracouponspace','varchar', 'NULL', 16, '', '',
- 'latexcouponfootsep', 'varchar', 'NULL', 16, '', '',
- 'latexcouponamountenclosedsep', 'varchar', 'NULL', 16, '', '',
- 'latexcoupontoaddresssep', 'varchar', 'NULL', 16, '', '',
- 'latexverticalreturnaddress', 'char', 'NULL', 1, '', '',
- 'latexcouponaddcompanytoaddress', 'char', 'NULL', 1, '', '',
- 'logo_png', 'blob', 'NULL', '', '', '',
- 'logo_eps', 'blob', 'NULL', '', '', '',
+ 'with_latexcoupon', 'char', 'NULL', '1', '', '',
'lpr', 'varchar', 'NULL', $char_d, '', '',
],
'primary_key' => 'confnum',