X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=fc56d901bb910a4f7df5cd96c9e41130e26d7746;hp=492f8e2b73126dbeb6823bed6c4e4f4dacdfa955;hb=92b6628c08e4478e48b6f250320a3e3e93262ec2;hpb=e68a2a20972de41485c438c46d197b5abeee3267 diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 492f8e2b7..fc56d901b 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -200,6 +200,7 @@ sub dbdef_dist { grep { ! /^(clientapi|access_user)_session/ && ! /^h_/ && ! /^log(_context)?$/ + && ! /^legacy_cust_history$/ && ( ! /^queue(_arg|_depend|_stat)?$/ || ! $opt->{'queue-no_history'} ) && ! $tables_hashref_torrus->{$_} } @@ -778,70 +779,43 @@ sub tables_hashref { ], }, - 'cust_statement' => { + 'legacy_cust_history' => { 'columns' => [ - 'statementnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', - '_date', @date_type, '', '', + 'legacyhistorynum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'history_action', 'varchar', '', $char_d, '', '', + 'history_date', @date_type, '', '', + 'history_usernum', 'int', 'NULL', '', '', '', + 'item', 'varchar', 'NULL', $char_d, '', '', + 'description', 'varchar', 'NULL', 2*$char_d, '', '', + 'change_data', 'text', 'NULL', '', '', '', ], - 'primary_key' => 'statementnum', + 'primary_key' => 'legacyhistorynum', 'unique' => [], - 'index' => [ ['custnum'], ['_date'], ], + 'index' => [ ['custnum'], ['history_date'], ], 'foreign_keys' => [ { columns => [ 'custnum' ], table => 'cust_main', }, - ], - }, - - #old "invoice" events, deprecated - 'cust_bill_event' => { - 'columns' => [ - 'eventnum', 'serial', '', '', '', '', - 'invnum', 'int', '', '', '', '', - 'eventpart', 'int', '', '', '', '', - '_date', @date_type, '', '', - 'status', 'varchar', '', $char_d, '', '', - 'statustext', 'text', 'NULL', '', '', '', - ], - 'primary_key' => 'eventnum', - #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ], - 'unique' => [], - 'index' => [ ['invnum'], ['status'], ['eventpart'], - ['statustext'], ['_date'], - ], - 'foreign_keys' => [ - { columns => [ 'invnum' ], - table => 'cust_bill', - }, - { columns => [ 'eventpart' ], - table => 'part_bill_event', + { columns => [ 'history_usernum' ], + table => 'access_user', + references => [ 'usernum' ], }, ], }, - #old "invoice" events, deprecated - 'part_bill_event' => { + 'cust_statement' => { 'columns' => [ - 'eventpart', 'serial', '', '', '', '', - 'freq', 'varchar', 'NULL', $char_d, '', '', - 'payby', 'char', '', 4, '', '', - 'event', 'varchar', '', $char_d, '', '', - 'eventcode', @perl_type, '', '', - 'seconds', 'int', 'NULL', '', '', '', - 'weight', 'int', '', '', '', '', - 'plan', 'varchar', 'NULL', $char_d, '', '', - 'plandata', 'text', 'NULL', '', '', '', - 'reason', 'int', 'NULL', '', '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'statementnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', ], - 'primary_key' => 'eventpart', + 'primary_key' => 'statementnum', 'unique' => [], - 'index' => [ ['payby'], ['disabled'], ], + 'index' => [ ['custnum'], ['_date'], ], 'foreign_keys' => [ - { columns => [ 'reason' ], - table => 'reason', - references => [ 'reasonnum' ], + { columns => [ 'custnum' ], + table => 'cust_main', }, ], }, @@ -973,9 +947,9 @@ sub tables_hashref { { columns => [ 'eventnum' ], table => 'cust_event', }, - { columns => [ 'billpkgnum' ], - table => 'cust_bill_pkg', - }, + #{ columns => [ 'billpkgnum' ], + # table => 'cust_bill_pkg', + #}, { columns => [ 'feepart' ], table => 'part_fee', }, @@ -1039,7 +1013,7 @@ sub tables_hashref { 'classnum', 'int', 'NULL', '', '', '', 'duration', 'int', 'NULL', '', 0, '', 'phonenum', 'varchar', 'NULL', 255, '', '', # has to hold a service label - 'accountcode', 'varchar', 'NULL', 20, '', '', + 'accountcode', 'varchar', 'NULL', 32, '', '', 'startdate', @date_type, '', '', 'regionname', 'varchar', 'NULL', $char_d, '', '', 'detail', 'varchar', '', 255, '', '', @@ -1104,17 +1078,31 @@ sub tables_hashref { { 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_fee_void' => { + '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_void', }, ], }, + 'cust_bill_pkg_tax_location' => { 'columns' => [ 'billpkgtaxlocationnum', 'serial', '', '', '', '', @@ -1145,10 +1133,10 @@ sub tables_hashref { { columns => [ 'locationnum' ], table => 'cust_location', }, - { columns => [ 'taxable_billpkgnum' ], - table => 'cust_bill_pkg', - references => [ 'billpkgnum' ], - }, + #{ columns => [ 'taxable_billpkgnum' ], + # table => 'cust_bill_pkg', + # references => [ 'billpkgnum' ], + #}, ], }, @@ -1176,10 +1164,10 @@ sub tables_hashref { { columns => [ 'taxratelocationnum' ], table => 'tax_rate_location', }, - { columns => [ 'taxable_billpkgnum' ], - table => 'cust_bill_pkg', - references => [ 'billpkgnum' ], - }, + #{ columns => [ 'taxable_billpkgnum' ], + # table => 'cust_bill_pkg', + # references => [ 'billpkgnum' ], + #}, ], }, @@ -1203,6 +1191,7 @@ sub tables_hashref { 'unitsetup', @money_typen, '', '', 'unitrecur', @money_typen, '', '', 'hidden', 'char', 'NULL', 1, '', '', + 'feepart', 'int', 'NULL', '', '', '', #void fields 'void_date', @date_type, '', '', 'reason', 'varchar', 'NULL', $char_d, '', '', @@ -1243,7 +1232,7 @@ sub tables_hashref { 'classnum', 'int', 'NULL', '', '', '', 'duration', 'int', 'NULL', '', 0, '', 'phonenum', 'varchar', 'NULL', 255, '', '', - 'accountcode', 'varchar', 'NULL', 20, '', '', + 'accountcode', 'varchar', 'NULL', 32, '', '', 'startdate', @date_type, '', '', 'regionname', 'varchar', 'NULL', $char_d, '', '', 'detail', 'varchar', '', 255, '', '', @@ -1309,16 +1298,13 @@ sub tables_hashref { { columns => [ 'billpkgnum' ], table => 'cust_bill_pkg_void', }, - { columns => [ 'pkgnum' ], - table => 'cust_pkg', - }, { columns => [ 'locationnum' ], table => 'cust_location', }, - { columns => [ 'taxable_billpkgnum' ], - table => 'cust_bill_pkg_void', - references => [ 'billpkgnum' ], - }, + #{ columns => [ 'taxable_billpkgnum' ], + # table => 'cust_bill_pkg_void', + # references => [ 'billpkgnum' ], + #}, ], }, @@ -1423,6 +1409,7 @@ sub tables_hashref { #void fields 'void_date', @date_type, '', '', 'void_reason', 'varchar', 'NULL', $char_d, '', '', + 'void_reasonnum', 'int', 'NULL', '', '', '', 'void_usernum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'crednum', @@ -1458,6 +1445,10 @@ sub tables_hashref { table => 'cust_pkg', references => [ 'pkgnum' ], }, + { columns => [ 'void_reasonnum' ], + table => 'reason', + references => [ 'reasonnum' ], + }, { columns => [ 'void_usernum' ], table => 'access_user', references => [ 'usernum' ], @@ -1526,6 +1517,27 @@ sub tables_hashref { ], }, + 'cust_credit_source_bill_pkg' => { + 'columns' => [ + 'creditsourcebillpkgnum', 'serial', '', '', '', '', + 'crednum', 'int', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'currency', 'char', 'NULL', 3, '', '', + ], + 'primary_key' => 'creditsourcebillpkgnum', + 'unique' => [], + 'index' => [ ['crednum'], ['billpkgnum'] ], + 'foreign_keys' => [ + { columns => ['billpkgnum'], + table => 'cust_bill_pkg', + }, + { columns => ['crednum'], + table => 'cust_credit', + }, + ], + }, + 'cust_main' => { 'columns' => [ 'custnum', 'serial', '', '', '', '', @@ -1630,6 +1642,10 @@ sub tables_hashref { 'bill_locationnum', 'int', 'NULL', '', '', '', 'ship_locationnum', 'int', 'NULL', '', '', '', 'taxstatusnum', 'char', 'NULL', 32, '', '', + 'complimentary', 'char', 'NULL', 1, '', '', + 'po_number', 'varchar', 'NULL', $char_d, '', '', + 'invoice_attn', 'varchar', 'NULL', $char_d, '', '', + 'invoice_ship_address', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], @@ -1678,9 +1694,10 @@ sub tables_hashref { 'columns' => [ 'custpaybynum', 'serial', '', '', '', '', 'custnum', 'int', '', '', '', '', - 'weight', 'int', '', '', '', '', + 'weight', 'int', 'NULL', '', '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', + 'cardtype', 'varchar', 'NULL', $char_d, '', '', 'paycvv', 'varchar', 'NULL', 512, '', '', 'paymask', 'varchar', 'NULL', $char_d, '', '', #'paydate', @date_type, '', '', @@ -1718,20 +1735,69 @@ sub tables_hashref { 'index' => [ ['disabled'] ], }, + 'cust_contact' => { + 'columns' => [ + 'custcontactnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'contactnum', 'int', '', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'comment', 'varchar', 'NULL', 255, '', '', + 'selfservice_access', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'custcontactnum', + 'unique' => [ [ 'custnum', 'contactnum' ], ], + 'index' => [ [ 'custnum' ], [ 'contactnum' ], ], + 'foreign_keys' => [ + { columns => [ 'custnum' ], + table => 'cust_main', + }, + { columns => [ 'contactnum' ], + table => 'contact', + }, + { columns => [ 'classnum' ], + table => 'contact_class', + }, + ], + }, + + 'prospect_contact' => { + 'columns' => [ + 'prospectcontactnum', 'serial', '', '', '', '', + 'prospectnum', 'int', '', '', '', '', + 'contactnum', 'int', '', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'comment', 'varchar', 'NULL', 255, '', '', + ], + 'primary_key' => 'prospectcontactnum', + 'unique' => [ [ 'prospectnum', 'contactnum' ], ], + 'index' => [ [ 'prospectnum' ], [ 'contactnum' ], ], + 'foreign_keys' => [ + { columns => [ 'prospectnum' ], + table => 'prospect_main', + }, + { columns => [ 'contactnum' ], + table => 'contact', + }, + { columns => [ 'classnum' ], + table => 'contact_class', + }, + ], + }, + 'contact' => { 'columns' => [ 'contactnum', 'serial', '', '', '', '', - 'prospectnum', 'int', 'NULL', '', '', '', - 'custnum', 'int', 'NULL', '', '', '', + 'prospectnum', 'int', 'NULL', '', '', '', #deprecated, now prospect_contact table + 'custnum', 'int', 'NULL', '', '', '', #deprecated, now cust_contact table 'locationnum', 'int', 'NULL', '', '', '', #not yet - 'classnum', 'int', 'NULL', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', #deprecated, now prospect_contact or cust_contact # 'titlenum', 'int', 'NULL', '', '', '', #eg Mr. Mrs. Dr. Rev. 'last', 'varchar', '', $char_d, '', '', # 'middle', 'varchar', 'NULL', $char_d, '', '', 'first', 'varchar', '', $char_d, '', '', 'title', 'varchar', 'NULL', $char_d, '', '', #eg Head Bottle Washer - 'comment', 'varchar', 'NULL', 255, '', '', - 'selfservice_access', 'char', 'NULL', 1, '', '', + 'comment', 'varchar', 'NULL', 255, '', '', #depredated, now prospect_contact or cust_contact + 'selfservice_access', 'char', 'NULL', 1, '', '', #deprecated, now cust_contact '_password', 'varchar', 'NULL', $char_d, '', '', '_password_encoding', 'varchar', 'NULL', $char_d, '', '', 'disabled', 'char', 'NULL', 1, '', '', @@ -1848,7 +1914,7 @@ sub tables_hashref { ], 'primary_key' => 'quotationnum', 'unique' => [], - 'index' => [ [ 'prospectnum' ], ['custnum'], ], + 'index' => [ [ 'prospectnum' ], ['custnum'], ['disabled'], ], 'foreign_keys' => [ { columns => [ 'prospectnum' ], table => 'prospect_main', @@ -1873,6 +1939,8 @@ sub tables_hashref { 'contract_end', @date_type, '', '', 'quantity', 'int', 'NULL', '', '', '', 'waive_setup', 'char', 'NULL', 1, '', '', + 'unitsetup', @money_typen, '', '', + 'unitrecur', @money_typen, '', '', ], 'primary_key' => 'quotationpkgnum', 'unique' => [], @@ -1895,6 +1963,8 @@ sub tables_hashref { 'quotationpkgdiscountnum', 'serial', '', '', '', '', 'quotationpkgnum', 'int', '', '', '', '', 'discountnum', 'int', '', '', '', '', + 'setup_amount', @money_typen, '', '', + 'recur_amount', @money_typen, '', '', #'end_date', @date_type, '', '', ], 'primary_key' => 'quotationpkgdiscountnum', @@ -1910,6 +1980,26 @@ sub tables_hashref { ], }, + 'quotation_pkg_tax' => { + 'columns' => [ + 'quotationtaxnum', 'serial', '', '', '', '', + 'quotationpkgnum', 'int', '', '', '', '', + 'itemdesc', 'varchar', '', $char_d, '', '', + 'taxnum', 'int', '', '', '', '', + 'taxtype', 'varchar', '', $char_d, '', '', + 'setup_amount', @money_type, '', '', + 'recur_amount', @money_type, '', '', + ], + 'primary_key' => 'quotationtaxnum',, + 'unique' => [], + 'index' => [ [ 'quotationpkgnum' ] ], + 'foreign_keys' => [ + { columns => [ 'quotationpkgnum' ], + table => 'quotation_pkg', + }, + ], +}, + 'cust_location' => { #'location' now that its prospects too, but... 'columns' => [ 'locationnum', 'serial', '', '', '', '', @@ -1991,13 +2081,14 @@ sub tables_hashref { 'cust_main_note' => { 'columns' => [ - 'notenum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', - 'classnum', 'int', 'NULL', '', '', '', - '_date', @date_type, '', '', - 'otaker', 'varchar', 'NULL', 32, '', '', - 'usernum', 'int', 'NULL', '', '', '', - 'comments', 'text', 'NULL', '', '', '', + 'notenum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', + '_date', @date_type, '', '', + 'otaker', 'varchar', 'NULL', 32, '', '', + 'usernum', 'int', 'NULL', '', '', '', + 'comments', 'text', 'NULL', '', '', '', + 'sticky', 'int', '', '', 0, '', ], 'primary_key' => 'notenum', 'unique' => [], @@ -2627,6 +2718,7 @@ sub tables_hashref { 'recur_show_zero', 'char', 'NULL', 1, '', '', 'setup_show_zero', 'char', 'NULL', 1, '', '', 'change_to_pkgnum', 'int', 'NULL', '', '', '', + 'separate_bill', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'pkgnum', 'unique' => [], @@ -2717,11 +2809,11 @@ sub tables_hashref { 'cust_pkg_detail' => { 'columns' => [ - 'pkgdetailnum', 'serial', '', '', '', '', - 'pkgnum', 'int', '', '', '', '', - 'detail', 'varchar', '', $char_d, '', '', - 'detailtype', 'char', '', 1, '', '', # "I"nvoice or "C"omment - 'weight', 'int', '', '', '', '', + 'pkgdetailnum', 'serial', '', '', '', '', + 'pkgnum', 'int', '', '', '', '', + 'detail', 'varchar', '', 2*$char_d, '', '', + 'detailtype', 'char', '', 1, '', '', #"I"nvoice or "C"omment + 'weight', 'int', '', '', '', '', ], 'primary_key' => 'pkgdetailnum', 'unique' => [], @@ -2759,6 +2851,29 @@ sub tables_hashref { ], }, + 'cust_pkg_reason_fee' => { + 'columns' => [ + 'pkgreasonfeenum', 'serial', '', '', '', '', + 'pkgreasonnum', 'int', '', '', '', '', + 'billpkgnum', 'int', 'NULL', '', '', '', + 'feepart', 'int', '', '', '', '', + 'nextbill', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'pkgreasonfeenum', + 'unique' => [ [ 'billpkgnum' ], [ 'pkgreasonnum' ] ], # one-to-one link + 'index' => [ [ 'feepart' ] ], + 'foreign_keys' => [ + { columns => [ 'pkgreasonnum' ], + table => 'cust_pkg_reason', + references => [ 'num' ], + }, + { columns => [ 'feepart' ], + table => 'part_fee', + }, + # can't link billpkgnum, because of voids + ], + }, + 'cust_pkg_discount' => { 'columns' => [ 'pkgdiscountnum', 'serial', '', '', '', '', @@ -3051,6 +3166,7 @@ sub tables_hashref { 'part_pkg' => { 'columns' => [ 'pkgpart', 'serial', '', '', '', '', + 'pkgpartbatch', 'varchar', 'NULL', $char_d, '', '', 'pkg', 'varchar', '', $char_d, '', '', 'comment', 'varchar', 'NULL', 2*$char_d, '', '', 'promo_code', 'varchar', 'NULL', $char_d, '', '', @@ -4253,6 +4369,7 @@ sub tables_hashref { 'exportsvcnum' => 'serial', '', '', '', '', 'exportnum' => 'int', '', '', '', '', 'svcpart' => 'int', '', '', '', '', + 'role' => 'varchar', 'NULL', 16, '', '', ], 'primary_key' => 'exportsvcnum', 'unique' => [ [ 'exportnum', 'svcpart' ] ], @@ -5132,16 +5249,17 @@ sub tables_hashref { 'banned_pay' => { 'columns' => [ - 'bannum', 'serial', '', '', '', '', - 'payby', 'char', '', 4, '', '', - 'payinfo', 'varchar', '', 128, '', '', #say, a 512-big digest _hex encoded - #'paymask', 'varchar', 'NULL', $char_d, '', '' - '_date', @date_type, '', '', - 'end_date', @date_type, '', '', - 'otaker', 'varchar', 'NULL', 32, '', '', - 'usernum', 'int', 'NULL', '', '', '', - 'bantype', 'varchar', 'NULL', $char_d, '', '', - 'reason', 'varchar', 'NULL', $char_d, '', '', + 'bannum', 'serial', '', '', '', '', + 'payby', 'char', '', 4, '', '', + 'payinfo', 'varchar', '', 128, '', '', #say, a 512-big digest _hex encoded + 'payinfo_hash', 'varchar', 'NULL', 32, '', '', + #'paymask', 'varchar', 'NULL', $char_d, '', '' + '_date', @date_type, '', '', + 'end_date', @date_type, '', '', + 'otaker', 'varchar', 'NULL', 32, '', '', + 'usernum', 'int', 'NULL', '', '', '', + 'bantype', 'varchar', 'NULL', $char_d, '', '', + 'reason', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'bannum', 'unique' => [], @@ -5232,7 +5350,7 @@ sub tables_hashref { 'billsec', 'int', '', '', 0, '', 'disposition', 'varchar', '', 45, \"''", '', 'amaflags', 'int', '', '', 0, '', - 'accountcode', 'varchar', '', 20, \"''", '', + 'accountcode', 'varchar', '', 32, \"''", '', 'uniqueid', 'varchar', '', $char_d, \"''", '', 'userfield', 'varchar', '', 512, \"''", '', @@ -5655,6 +5773,7 @@ sub tables_hashref { 'e911_class', 'char', 'NULL', 1, '', '', 'e911_type', 'char', 'NULL', 1, '', '', 'circuit_svcnum', 'int', 'NULL', '', '', '', + 'sip_server', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', 'unique' => [ [ 'sms_carrierid', 'sms_account'] ], @@ -5888,6 +6007,9 @@ sub tables_hashref { 'unsuspend_pkgpart', 'int', 'NULL', '', '', '', 'unsuspend_hold','char', 'NULL', 1, '', '', 'unused_credit', 'char', 'NULL', 1, '', '', + 'feepart', 'int', 'NULL', '', '', '', + 'fee_on_unsuspend','char', 'NULL', 1, '', '', + 'fee_hold', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'reasonnum', 'unique' => [], @@ -5945,13 +6067,15 @@ sub tables_hashref { 'columns' => [ 'svcnum', 'int', '', '', '', '', 'id', 'int', 'NULL', '', '', '', + 'uuid', 'char', 'NULL', 36, '', '', 'title', 'varchar', 'NULL', $char_d, '', '', 'max_extensions', 'int', 'NULL', '', '', '', 'max_simultaneous', 'int', 'NULL', '', '', '', + 'ip_addr', 'varchar', 'NULL', 40, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], - 'index' => [ [ 'id' ] ], + 'index' => [ [ 'id' ], [ 'uuid' ] ], 'foreign_keys' => [ { columns => [ 'svcnum' ], table => 'cust_svc',