X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=bf756d129041accb864a9f8ef16f7624c4ab7742;hp=862e40afa19d8318f3b6cdaff6f61ff67f9783a8;hb=e9e0cf0989259b94d9758eceff448666a2e5a5cc;hpb=d535444f90993fb7b16928e3f2f342603016623b diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 862e40afa..bf756d129 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -937,6 +937,30 @@ sub tables_hashref { ], }, + 'cust_event_fee' => { + 'columns' => [ + 'eventfeenum', 'serial', '', '', '', '', + 'eventnum', 'int', '', '', '', '', + 'billpkgnum', 'int', 'NULL', '', '', '', + 'feepart', 'int', '', '', '', '', + 'nextbill', 'char', 'NULL', 1, '', '', + ], + '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', '', '', '', '', @@ -959,6 +983,7 @@ sub tables_hashref { 'freq', 'varchar', 'NULL', $char_d, '', '', 'quantity', 'int', 'NULL', '', '', '', 'hidden', 'char', 'NULL', 1, '', '', + 'feepart', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgnum', 'unique' => [], @@ -975,6 +1000,9 @@ sub tables_hashref { table => 'part_pkg', references => [ 'pkgpart' ], }, + { columns => [ 'feepart' ], + table => 'part_fee', + }, ], }, @@ -988,7 +1016,7 @@ sub tables_hashref { '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, '', '', @@ -1017,7 +1045,7 @@ sub tables_hashref { 'cust_bill_pkg_display' => { 'columns' => [ - 'billpkgdisplaynum', 'serial', '', '', '', '', + 'billpkgdisplaynum', 'serial', '', '', '', '', 'billpkgnum', 'int', '', '', '', '', 'section', 'varchar', 'NULL', $char_d, '', '', #'unitsetup', @money_typen, '', '', #override the linked real one? @@ -1036,6 +1064,35 @@ sub tables_hashref { ], }, + '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', '', '', '', '', @@ -1060,9 +1117,9 @@ sub tables_hashref { { 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', }, @@ -1163,7 +1220,7 @@ sub tables_hashref { '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, '', '', @@ -1285,11 +1342,12 @@ sub tables_hashref { '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' ], @@ -1622,32 +1680,6 @@ sub tables_hashref { ], }, - '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', '', '', '', '', @@ -1904,6 +1936,26 @@ sub tables_hashref { ], }, + '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', '', '', '', '', @@ -2183,6 +2235,7 @@ sub tables_hashref { 'gatewaynum', 'int', 'NULL', '', '', '', #'cust_balance', @money_type, '', '', 'paynum', 'int', 'NULL', '', '', '', + 'void_paynum', 'int', 'NULL', '', '', '', 'jobnum', 'bigint', 'NULL', '', '', '', 'invnum', 'int', 'NULL', '', '', '', 'manual', 'char', 'NULL', 1, '', '', @@ -2205,6 +2258,10 @@ sub tables_hashref { { columns => [ 'paynum' ], table => 'cust_pay', }, + { columns => [ 'void_paynum' ], + table => 'cust_pay_void', + references => [ 'paynum' ], + }, { columns => [ 'jobnum' ], table => 'queue', }, @@ -2796,7 +2853,8 @@ sub tables_hashref { '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 @@ -2820,6 +2878,9 @@ sub tables_hashref { { columns => [ 'usernum' ], table => 'access_user', }, + { columns => [ 'reasonnum' ], + table => 'reason', + }, { columns => [ 'gatewaynum' ], table => 'payment_gateway', }, @@ -2928,11 +2989,11 @@ sub tables_hashref { ], }, - '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, '', '', @@ -2957,11 +3018,12 @@ sub tables_hashref { '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' ], @@ -3036,6 +3098,123 @@ sub tables_hashref { '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_fee_usage' => { + 'columns' => [ + 'feepartusagenum','serial', '', '', '', '', + 'feepart', 'int', '', '', '', '', + 'classnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'percent', 'decimal', '', '7,4', '', '', + ], + 'primary_key' => 'feepartusagenum', + 'unique' => [ [ 'feepart', 'classnum' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'feepart' ], + table => 'part_fee', + }, + { columns => [ 'classnum' ], + table => 'usage_class', + }, + ], + }, + 'part_pkg_link' => { 'columns' => [ 'pkglinknum', 'serial', '', '', '', '', @@ -4274,9 +4453,9 @@ sub tables_hashref { 'unique' => [ [ 'blocknum', 'routernum' ] ], 'index' => [], 'foreign_keys' => [ - { columns => [ 'routernum' ], - table => 'router', - }, + #{ columns => [ 'routernum' ], + # table => 'router', + #}, { columns => [ 'agentnum' ], table => 'agent', }, @@ -4917,8 +5096,10 @@ sub tables_hashref { '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: @@ -4987,6 +5168,10 @@ sub tables_hashref { '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, '', '', @@ -5616,6 +5801,64 @@ sub tables_hashref { ], }, + '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', '', '', '', '', @@ -5774,7 +6017,7 @@ sub tables_hashref { 'cust_msg' => { 'columns' => [ 'custmsgnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', 'msgnum', 'int', 'NULL', '', '', '', '_date', @date_type, '', '', 'env_from', 'varchar', 'NULL', 255, '', '', @@ -5783,6 +6026,7 @@ sub tables_hashref { 'body', 'blob', 'NULL', '', '', '', 'error', 'varchar', 'NULL', 255, '', '', 'status', 'varchar', '',$char_d, '', '', + 'msgtype', 'varchar', 'NULL', 16, '', '', ], 'primary_key' => 'custmsgnum', 'unique' => [ ], @@ -6055,6 +6299,78 @@ sub tables_hashref { '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', '', '', '', '', @@ -6198,21 +6514,9 @@ sub tables_hashref { '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',