X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=a10b5c023a9986be737daa0f1761612c259cce3c;hp=715a603f843206d14299f5800320d70aa456d575;hb=20961c94ea06ad3aa71fad2bd7f6903f83f02ebc;hpb=3cda3a85eb0daa5b8b9f4d30cd0896da6ccf4a9b diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 715a603f8..4ebda4e11 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -56,6 +56,7 @@ sub reload_dbdef { warn "[debug]$me re-using cached dbdef for $file\n" if $DEBUG; } $dbdef = $dbdef_cache{$file}; + FS::Record::fk_methods_init(); } =item dbdef @@ -200,7 +201,9 @@ sub dbdef_dist { grep { ! /^(clientapi|access_user)_session/ && ! /^h_/ && ! /^log(_context)?$/ + && ! /^(legacy_cust_history|cacti_page|template_image|access_user_log)$/ && ( ! /^queue(_arg|_depend|_stat)?$/ || ! $opt->{'queue-no_history'} ) + && ! /^addr_status$/ && ! $tables_hashref_torrus->{$_} } $dbdef->tables @@ -492,8 +495,44 @@ sub tables_hashref { my $username_len = 64; #usernamemax config file - # name type nullability length default local + # Return a hashref defining the entire application database schema + # Each key of the hashref contains a structure describing a database table + # + # table_name => { + # columns => [...], + # primary_key => 'column', + # unique => [column,column,...], + # index => [[column],[column,column],...], + # foreign_keys => [{...},{...},...], + # } + # + # + # columns => [ + # + # 'column_name', + # + # 'column_type', + # + # 'NULL' or '', # 'NULL' : Allow null values + # # '' : Disallow null values + # + # 'length', # Column size value. eg: + # # 40 : VARCHAR(40) + # # '10,2' : FLOAT(10,2) + # + # 'default', # Default column value for a new record + # # (Unclear if setting this to '' results in a default + # # value of NULL or empty string?) + # + # '', # local ? + # + # name, type, nullability, length, default, local, + # name, type, nullability, length, default, local, + # ... + # + # ], + # name type nullability length default local return { 'agent' => { @@ -556,12 +595,13 @@ sub tables_hashref { 'agent_type' => { 'columns' => [ - 'typenum', 'serial', '', '', '', '', - 'atype', 'varchar', '', $char_d, '', '', + 'typenum', 'serial', '', '', '', '', + 'atype', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'typenum', 'unique' => [], - 'index' => [], + 'index' => [ ['disabled'] ], }, 'type_pkgs' => { @@ -654,6 +694,7 @@ sub tables_hashref { 'title', 'varchar', 'NULL', $char_d, '', '', 'body', 'blob', 'NULL', '', '', '', 'disabled', @date_type, '', '', + 'attachnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'attachnum', 'unique' => [], @@ -691,6 +732,7 @@ sub tables_hashref { 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy 'promised_date', @date_type, '', '', + 'taxengine_request', 'text', 'NULL', '', '', '', 'pending', 'char', 'NULL', 1, '', '', ], @@ -728,11 +770,13 @@ sub tables_hashref { 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy 'promised_date', @date_type, '', '', + 'taxengine_request', 'text', 'NULL', '', '', '', #void fields 'void_date', @date_type, '', '', - 'reason', 'varchar', 'NULL', $char_d, '', '', - 'void_usernum', 'int', 'NULL', '', '', '', + 'reason', 'varchar', 'NULL', $char_d, '', '', + 'reasonnum', 'int', 'NULL', '', '', '', + 'void_usernum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'invnum', 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh @@ -746,6 +790,9 @@ sub tables_hashref { { columns => [ 'statementnum' ], table => 'cust_statement', #_void? both? }, + { columns => [ 'reasonnum' ], + table => 'reason', + }, { columns => [ 'void_usernum' ], table => 'access_user', references => [ 'usernum' ], @@ -777,70 +824,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', }, ], }, @@ -943,12 +963,13 @@ sub tables_hashref { '_date', @date_type, '', '', 'status', 'varchar', '', $char_d, '', '', 'statustext', 'text', 'NULL', '', '', '', + 'no_action', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'eventnum', #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ], 'unique' => [], 'index' => [ ['eventpart'], ['tablenum'], ['status'], - ['statustext'], ['_date'], + ['statustext'], ['_date'], ['no_action'], ], 'foreign_keys' => [ { columns => [ 'eventpart' ], @@ -972,9 +993,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', }, @@ -1004,6 +1025,7 @@ sub tables_hashref { 'quantity', 'int', 'NULL', '', '', '', 'hidden', 'char', 'NULL', 1, '', '', 'feepart', 'int', 'NULL', '', '', '', + 'billpkgnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgnum', 'unique' => [], @@ -1038,10 +1060,11 @@ 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, '', '', + 'detailnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'detailnum', 'unique' => [], @@ -1074,6 +1097,7 @@ sub tables_hashref { 'post_total', 'char', 'NULL', 1, '', '', 'type', 'char', 'NULL', 1, '', '', 'summary', 'char', 'NULL', 1, '', '', + 'billpkgdisplaynum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgdisplaynum', 'unique' => [], @@ -1103,17 +1127,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', '', '', '', '', @@ -1125,6 +1163,7 @@ sub tables_hashref { 'amount', @money_type, '', '', 'currency', 'char', 'NULL', 3, '', '', 'taxable_billpkgnum', 'int', 'NULL', '', '', '', + 'billpkgtaxlocationnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgtaxlocationnum', 'unique' => [], @@ -1144,10 +1183,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' ], + #}, ], }, @@ -1162,6 +1201,7 @@ sub tables_hashref { 'amount', @money_type, '', '', 'currency', 'char', 'NULL', 3, '', '', 'taxable_billpkgnum', 'int', 'NULL', '', '', '', + 'taxclass', 'varchar', 'NULL', 10, '', '', ], 'primary_key' => 'billpkgtaxratelocationnum', 'unique' => [], @@ -1175,10 +1215,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' ], + #}, ], }, @@ -1202,10 +1242,13 @@ 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, '', '', - 'void_usernum', 'int', 'NULL', '', '', '', + 'reason', 'varchar', 'NULL', $char_d, '', '', + 'reasonnum', 'int', 'NULL', '', '', '', + 'void_usernum', 'int', 'NULL', '', '', '', + 'billpkgnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgnum', 'unique' => [], @@ -1216,6 +1259,9 @@ sub tables_hashref { { columns => [ 'invnum' ], table => 'cust_bill_void', }, + { columns => [ 'reasonnum' ], + table => 'reason', + }, #pkgnum 0 and -1 are used for special things #{ columns => [ 'pkgnum' ], # table => 'cust_pkg', @@ -1242,10 +1288,11 @@ 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, '', '', + 'detailnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'detailnum', 'unique' => [], @@ -1276,6 +1323,7 @@ sub tables_hashref { 'post_total', 'char', 'NULL', 1, '', '', 'type', 'char', 'NULL', 1, '', '', 'summary', 'char', 'NULL', 1, '', '', + 'billpkgdisplaynum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgdisplaynum', 'unique' => [], @@ -1298,6 +1346,7 @@ sub tables_hashref { 'amount', @money_type, '', '', 'currency', 'char', 'NULL', 3, '', '', 'taxable_billpkgnum', 'int', 'NULL', '', '', '', + 'billpkgtaxlocationnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgtaxlocationnum', 'unique' => [], @@ -1308,16 +1357,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' ], + #}, ], }, @@ -1363,7 +1409,9 @@ sub tables_hashref { 'commission_agentnum', 'int', 'NULL', '', '', '', # 'commission_salesnum', 'int', 'NULL', '', '', '', # 'commission_pkgnum', 'int', 'NULL', '', '', '', # - 'credbatch', 'varchar', 'NULL', $char_d, '', '', + 'commission_invnum', 'int', 'NULL', '', '', '', + 'credbatch', 'varchar', 'NULL', $char_d, '', '', + 'crednum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'crednum', 'unique' => [], @@ -1398,6 +1446,10 @@ sub tables_hashref { table => 'cust_pkg', references => [ 'pkgnum' ], }, + { columns => [ 'commission_invnum' ], + table => 'cust_bill', + references => [ 'invnum' ], + }, ], }, @@ -1419,10 +1471,13 @@ sub tables_hashref { 'commission_agentnum', 'int', 'NULL', '', '', '', 'commission_salesnum', 'int', 'NULL', '', '', '', 'commission_pkgnum', 'int', 'NULL', '', '', '', + 'commission_invnum', 'int', 'NULL', '', '', '', #void fields 'void_date', @date_type, '', '', 'void_reason', 'varchar', 'NULL', $char_d, '', '', + 'void_reasonnum', 'int', 'NULL', '', '', '', 'void_usernum', 'int', 'NULL', '', '', '', + 'crednum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'crednum', 'unique' => [], @@ -1457,6 +1512,14 @@ sub tables_hashref { table => 'cust_pkg', references => [ 'pkgnum' ], }, + { columns => [ 'commission_invnum' ], + table => 'cust_bill', + references => [ 'invnum' ], + }, + { columns => [ 'void_reasonnum' ], + table => 'reason', + references => [ 'reasonnum' ], + }, { columns => [ 'void_usernum' ], table => 'access_user', references => [ 'usernum' ], @@ -1473,6 +1536,7 @@ sub tables_hashref { '_date', @date_type, '', '', 'amount', @money_type, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances + 'creditbillnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'creditbillnum', 'unique' => [], @@ -1501,6 +1565,7 @@ sub tables_hashref { 'setuprecur', 'varchar', '', $char_d, '', '', 'sdate', @date_type, '', '', 'edate', @date_type, '', '', + 'creditbillpkgnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'creditbillpkgnum', 'unique' => [], @@ -1525,6 +1590,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', '', '', '', '', @@ -1585,20 +1671,7 @@ sub tables_hashref { 'ship_mobile', 'varchar', 'NULL', 12, '', '', 'currency', 'char', 'NULL', 3, '', '', - #deprecated, info moved to cust_payby - 'payby', 'char', 'NULL', 4, '', '', - 'payinfo', 'varchar', 'NULL', 512, '', '', - 'paycvv', 'varchar', 'NULL', 512, '', '', - 'paymask', 'varchar', 'NULL', $char_d, '', '', - #'paydate', @date_type, '', '', - 'paydate', 'varchar', 'NULL', 10, '', '', - 'paystart_month', 'int', 'NULL', '', '', '', - 'paystart_year', 'int', 'NULL', '', '', '', - 'payissue', 'varchar', 'NULL', 2, '', '', - 'payname', 'varchar', 'NULL', 2*$char_d, '', '', - 'paystate', 'varchar', 'NULL', $char_d, '', '', - 'paytype', 'varchar', 'NULL', $char_d, '', '', - 'payip', 'varchar', 'NULL', 15, '', '', + 'is_tokenized', 'char', 'NULL', '1', '', '', 'geocode', 'varchar', 'NULL', 20, '', '', 'censustract', 'varchar', 'NULL', 20, '', '', # 7 to save space? @@ -1621,6 +1694,7 @@ sub tables_hashref { 'accountcode_cdr', 'char', 'NULL', 1, '', '', 'billday', 'int', 'NULL', '', '', '', 'prorate_day', 'int', 'NULL', '', '', '', + 'force_prorate_day', 'char', 'NULL', 1, '', '', 'edit_subject', 'char', 'NULL', 1, '', '', 'locale', 'varchar', 'NULL', 16, '', '', 'calling_list_exempt', 'char', 'NULL', 1, '', '', @@ -1628,7 +1702,13 @@ sub tables_hashref { 'message_noemail', 'char', 'NULL', 1, '', '', 'bill_locationnum', 'int', 'NULL', '', '', '', 'ship_locationnum', 'int', 'NULL', '', '', '', - 'taxstatusnum', 'char', 'NULL', 32, '', '', + 'taxstatusnum', 'int', 'NULL', '', '', '', + 'complimentary', 'char', 'NULL', 1, '', '', + 'po_number', 'varchar', 'NULL', $char_d, '', '', + 'invoice_attn', 'varchar', 'NULL', $char_d, '', '', + 'invoice_ship_address', 'char', 'NULL', 1, '', '', + 'postal_invoice', 'char', 'NULL', 1, '', '', + 'paymentreceipt_noemail', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], @@ -1637,10 +1717,10 @@ sub tables_hashref { ['agentnum'], ['refnum'], ['classnum'], ['usernum'], [ 'custbatch' ], [ 'referral_custnum' ], - [ 'payby' ], [ 'paydate' ], [ 'archived' ], [ 'ship_locationnum' ], [ 'bill_locationnum' ], + [ 'is_tokenized' ], ], 'foreign_keys' => [ { columns => [ 'agentnum' ], @@ -1677,9 +1757,10 @@ sub tables_hashref { 'columns' => [ 'custpaybynum', 'serial', '', '', '', '', 'custnum', 'int', '', '', '', '', - 'weight', 'int', '', '', '', '', + 'weight', 'int', 'NULL', '', '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', + 'paycardtype', 'varchar', 'NULL', $char_d, '', '', 'paycvv', 'varchar', 'NULL', 512, '', '', 'paymask', 'varchar', 'NULL', $char_d, '', '', #'paydate', @date_type, '', '', @@ -1692,10 +1773,11 @@ sub tables_hashref { 'paytype', 'varchar', 'NULL', $char_d, '', '', 'payip', 'varchar', 'NULL', 15, '', '', 'locationnum', 'int', 'NULL', '', '', '', + 'is_tokenized', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'custpaybynum', 'unique' => [], - 'index' => [ [ 'custnum' ] ], + 'index' => [ [ 'custnum' ], [ 'is_tokenized' ] ], 'foreign_keys' => [ { columns => [ 'custnum' ], table => 'cust_main', @@ -1711,29 +1793,83 @@ sub tables_hashref { 'classnum', 'serial', '', '', '', '', 'classname', 'varchar', '', $char_d, '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'classnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'classnum', 'unique' => [], 'index' => [ ['disabled'] ], }, + 'cust_contact' => { + 'columns' => [ + 'custcontactnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'contactnum', 'int', '', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'comment', 'varchar', 'NULL', 255, '', '', + 'selfservice_access', 'char', 'NULL', 1, '', '', + 'invoice_dest', 'char', 'NULL', 1, '', '', # Y or NULL + 'message_dest', 'char', 'NULL', 1, '', '', # Y or NULL + ], + '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, '', '', + 'invoice_dest', 'char', 'NULL', 1, '', '', + 'contactnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'contactnum', 'unique' => [], @@ -1765,6 +1901,7 @@ sub tables_hashref { 'phonenum', 'varchar', '', 14, '', '', 'extension', 'varchar', 'NULL', 7, '', '', #?#'comment', 'varchar', '', $char_d, '', '', + 'contactphonenum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'contactphonenum', 'unique' => [], @@ -1795,6 +1932,7 @@ sub tables_hashref { 'contactemailnum', 'serial', '', '', '', '', 'contactnum', 'int', '', '', '', '', 'emailaddress', 'varchar', '', $char_d, '', '', + 'contactemailnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'contactemailnum', #'unique' => [ [ 'contactnum', 'emailaddress' ], ], @@ -1816,6 +1954,7 @@ sub tables_hashref { 'disabled', 'char', 'NULL', 1, '', '', 'custnum', 'int', 'NULL', '', '', '', 'refnum', 'int', 'NULL', '', '', '', + 'taxstatusnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'prospectnum', 'unique' => [], @@ -1836,18 +1975,21 @@ sub tables_hashref { 'quotation' => { 'columns' => [ #regular fields - 'quotationnum', 'serial', '', '', '', '', - 'prospectnum', 'int', 'NULL', '', '', '', - 'custnum', 'int', 'NULL', '', '', '', - '_date', @date_type, '', '', - 'disabled', 'char', 'NULL', 1, '', '', - 'usernum', 'int', 'NULL', '', '', '', + 'quotationnum', 'serial', '', '', '', '', + 'prospectnum', 'int', 'NULL', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', + '_date', @date_type, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'usernum', 'int', 'NULL', '', '', '', + 'close_date', @date_type, '', '', + 'confidence', 'int', 'NULL', '', '', '', + 'quotation_description', 'varchar', 'NULL', '50', '', '', #'total', @money_type, '', '', #'quotation_term', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'quotationnum', 'unique' => [], - 'index' => [ [ 'prospectnum' ], ['custnum'], ], + 'index' => [ [ 'prospectnum' ], ['custnum'], ['disabled'], ], 'foreign_keys' => [ { columns => [ 'prospectnum' ], table => 'prospect_main', @@ -1872,6 +2014,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' => [], @@ -1889,11 +2033,32 @@ sub tables_hashref { ], }, + 'quotation_pkg_detail' => { + 'columns' => [ + 'detailnum', 'serial', '', '', '', '', + 'quotationpkgnum', 'int', '', '', '', '', + 'format', 'char', 'NULL', 1, '', '', # not used for anything + 'detail', 'varchar', '', 255, '', '', + 'copy_on_order', 'char', 'NULL', 1, '', '', # 'Y' to copy when ordering + ], + 'primary_key' => 'detailnum', + 'unique' => [], + 'index' => [ [ 'quotationpkgnum' ] ], + 'foreign_keys' => [ + { columns => [ 'quotationpkgnum' ], + table => 'quotation_pkg', + references => [ 'quotationpkgnum' ], + }, + ], + }, + 'quotation_pkg_discount' => { 'columns' => [ 'quotationpkgdiscountnum', 'serial', '', '', '', '', 'quotationpkgnum', 'int', '', '', '', '', 'discountnum', 'int', '', '', '', '', + 'setuprecur', 'varchar', 'NULL', $char_d, '', '', + 'amount', @money_typen, '', '', #'end_date', @date_type, '', '', ], 'primary_key' => 'quotationpkgdiscountnum', @@ -1909,6 +2074,24 @@ sub tables_hashref { ], }, + 'quotation_pkg_tax' => { + 'columns' => [ + 'quotationtaxnum', 'serial', '', '', '', '', + 'quotationpkgnum', 'int', '', '', '', '', + 'itemdesc', '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', '', '', '', '', @@ -1917,7 +2100,7 @@ sub tables_hashref { 'locationname', 'varchar', 'NULL', $char_d, '', '', 'address1', 'varchar', '', $char_d, '', '', 'address2', 'varchar', 'NULL', $char_d, '', '', - 'city', 'varchar', '', $char_d, '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', 'state', 'varchar', 'NULL', $char_d, '', '', 'zip', 'varchar', 'NULL', 10, '', '', @@ -1930,11 +2113,15 @@ sub tables_hashref { 'district', 'varchar', 'NULL', 20, '', '', 'censustract', 'varchar', 'NULL', 20, '', '', 'censusyear', 'char', 'NULL', 4, '', '', + + #used only in... DSL qualification? .CA DSL qualification? 'location_type', 'varchar', 'NULL', 20, '', '', 'location_number', 'varchar', 'NULL', 20, '', '', 'location_kind', 'char', 'NULL', 1, '', '', + 'incorporated', 'char', 'NULL', 1, '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'locationnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'locationnum', 'unique' => [], @@ -1957,6 +2144,7 @@ sub tables_hashref { 'destnum', 'serial', '', '', '', '', 'custnum', 'int', '', '', '', '', 'dest', 'varchar', '', $char_d, '', '', + 'destnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'destnum', 'unique' => [], @@ -1990,13 +2178,15 @@ 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, '', + 'notenum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'notenum', 'unique' => [], @@ -2030,7 +2220,8 @@ sub tables_hashref { 'categorynum', 'serial', '', '', '', '', 'categoryname', 'varchar', '', $char_d, '', '', 'weight', 'int', 'NULL', '', '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'categorynum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'categorynum', 'unique' => [], @@ -2147,6 +2338,8 @@ sub tables_hashref { 'taxname', 'varchar', 'NULL', $char_d, '', '', 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt + 'source', 'varchar', 'NULL', $char_d, '', '', + 'charge_prediscount', 'char', 'NULL', 1, '', '', # Y = charge this tax pre discount ], 'primary_key' => 'taxnum', 'unique' => [], @@ -2204,6 +2397,7 @@ sub tables_hashref { 'taxratelocationnum', 'serial', '', '', '', '', 'data_vendor', 'varchar', 'NULL', $char_d, '', '', 'geocode', 'varchar', '', 20, '', '', + 'district', 'varchar', 'NULL', $char_d, '', '', 'city', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', 'state', 'char', 'NULL', 2, '', '', @@ -2265,7 +2459,7 @@ sub tables_hashref { 'cust_pay_pending' => { 'columns' => [ 'paypendingnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', 'paid', @money_type, '', '', 'currency', 'char', 'NULL', 3, '', '', '_date', @date_type, '', '', @@ -2289,11 +2483,13 @@ sub tables_hashref { 'manual', 'char', 'NULL', 1, '', '', 'discount_term','int', 'NULL', '', '', '', 'failure_status','varchar','NULL', 16, '', '', + 'is_tokenized', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'paypendingnum', 'unique' => [ [ 'payunique' ] ], 'index' => [ [ 'custnum' ], [ 'status' ], ['paynum'], ['void_paynum'], ['jobnum'], ['invnum'], + [ 'is_tokenized' ], ], 'foreign_keys' => [ { columns => [ 'custnum' ], @@ -2332,13 +2528,16 @@ sub tables_hashref { 'usernum', 'int', 'NULL', '', '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', + 'paycardtype', 'varchar', 'NULL', $char_d, '', '', 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paydate', 'varchar', 'NULL', 10, '', '', 'paybatch', 'varchar', 'NULL', $char_d, '', '',#for auditing purposes 'payunique', 'varchar', 'NULL', $char_d, '', '',#separate paybatch "unique" functions from current usage 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances - + 'no_auto_apply', 'char', 'NULL', 1, '', '', + 'is_tokenized', 'char', 'NULL', 1, '', '', + # cash/check deposit info fields 'bank', 'varchar', 'NULL', $char_d, '', '', 'depositor', 'varchar', 'NULL', $char_d, '', '', @@ -2351,12 +2550,13 @@ sub tables_hashref { 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK 'processor', 'varchar', 'NULL', $char_d, '', '', # module name 'auth', 'varchar', 'NULL', 16, '', '', # CC auth number - 'order_number','varchar', 'NULL', $char_d, '', '', # transaction number + 'order_number','varchar', 'NULL', 256, '', '', # transaction number + 'paynum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'paynum', #i guess not now, with cust_pay_pending, if we actually make it here, we _do_ want to record it# 'unique' => [ [ 'payunique' ] ], 'index' => [ ['custnum'], ['paybatch'], ['payby'], ['_date'], - ['usernum'], + ['usernum'], ['is_tokenized'], ], 'foreign_keys' => [ { columns => [ 'custnum' ], @@ -2388,11 +2588,13 @@ sub tables_hashref { 'usernum', 'int', 'NULL', '', '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', - 'paymask', 'varchar', 'NULL', $char_d, '', '', + 'paycardtype', 'varchar', 'NULL', $char_d, '', '', + 'paymask', 'varchar', 'NULL', $char_d, '', '', #'paydate' ? 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. 'closed', 'char', 'NULL', 1, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances + 'is_tokenized', 'char', 'NULL', 1, '', '', # cash/check deposit info fields 'bank', 'varchar', 'NULL', $char_d, '', '', @@ -2405,16 +2607,21 @@ sub tables_hashref { 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK 'processor', 'varchar', 'NULL', $char_d, '', '', # module name 'auth', 'varchar', 'NULL', 16, '', '', # CC auth number - 'order_number','varchar', 'NULL', $char_d, '', '', # transaction number + 'order_number','varchar', 'NULL', 256, '', '', # transaction number #void fields 'void_date', @date_type, '', '', 'reason', 'varchar', 'NULL', $char_d, '', '', + 'reasonnum', 'int', 'NULL', '', '', '', 'void_usernum', 'int', 'NULL', '', '', '', + + 'paynum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'paynum', 'unique' => [], - 'index' => [ ['custnum'], ['usernum'], ['void_usernum'] ], + 'index' => [ ['custnum'], ['usernum'], ['void_usernum'], + ['is_tokenized'], + ], 'foreign_keys' => [ { columns => [ 'custnum' ], table => 'cust_main', @@ -2431,6 +2638,9 @@ sub tables_hashref { { columns => [ 'gatewaynum' ], table => 'payment_gateway', }, + { columns => [ 'reasonnum' ], + table => 'reason', + }, { columns => [ 'void_usernum' ], table => 'access_user', references => [ 'usernum' ], @@ -2446,6 +2656,7 @@ sub tables_hashref { 'amount', @money_type, '', '', '_date', @date_type, '', '', 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances + 'billpaynum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpaynum', 'unique' => [], @@ -2495,6 +2706,7 @@ sub tables_hashref { 'setuprecur', 'varchar', '', $char_d, '', '', 'sdate', @date_type, '', '', 'edate', @date_type, '', '', + 'billpaypkgnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpaypkgnum', 'unique' => [], @@ -2524,6 +2736,8 @@ sub tables_hashref { 'download', @date_type, '', '', 'upload', @date_type, '', '', 'title', 'varchar', 'NULL',255, '', '', + 'processor_id', 'varchar', 'NULL',255, '', '', + 'type', 'char', '', 6, 'DEBIT', '', # DEBIT/CREDIT ], 'primary_key' => 'batchnum', 'unique' => [], @@ -2545,31 +2759,34 @@ sub tables_hashref { 'first', 'varchar', '', $char_d, '', '', 'address1', 'varchar', '', $char_d, '', '', 'address2', 'varchar', 'NULL', $char_d, '', '', - 'city', 'varchar', '', $char_d, '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', 'state', 'varchar', 'NULL', $char_d, '', '', 'zip', 'varchar', 'NULL', 10, '', '', 'country', 'char', '', 2, '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', + #'paymask', 'varchar', 'NULL', $char_d, '', '', #'exp', @date_type, '', '', 'exp', 'varchar', 'NULL', 11, '', '', 'payname', 'varchar', 'NULL', $char_d, '', '', + 'paytype', 'varchar', 'NULL', $char_d, '', '', 'amount', @money_type, '', '', 'currency', 'char', 'NULL', 3, '', '', 'status', 'varchar', 'NULL', $char_d, '', '', 'failure_status','varchar', 'NULL', 16, '', '', 'error_message', 'varchar', 'NULL', $char_d, '', '', + 'paycode', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'paybatchnum', 'unique' => [], - 'index' => [ ['batchnum'], ['invnum'], ['custnum'] ], + 'index' => [ ['batchnum'], ['invnum'], ['custnum'],['status'] ], 'foreign_keys' => [ { columns => [ 'batchnum' ], table => 'pay_batch', }, - { columns => [ 'invnum' ], - table => 'cust_bill', - }, + #{ columns => [ 'invnum' ], + # table => 'cust_bill', + #}, { columns => [ 'custnum' ], table => 'cust_main', }, @@ -2618,7 +2835,6 @@ sub tables_hashref { 'change_custnum', 'int', 'NULL', '', '', '', 'main_pkgnum', 'int', 'NULL', '', '', '', 'pkglinknum', 'int', 'NULL', '', '', '', - 'manual_flag', 'char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'quantity', 'int', 'NULL', '', '', '', 'agent_pkgid', 'varchar', 'NULL', $char_d, '', '', @@ -2626,6 +2842,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' => [], @@ -2703,6 +2920,7 @@ sub tables_hashref { 'pkgnum', 'int', '', '', '', '', 'optionname', 'varchar', '', $char_d, '', '', 'optionvalue', 'text', 'NULL', '', '', '', + 'optionnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'optionnum', 'unique' => [], @@ -2716,11 +2934,12 @@ 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', '', '', '', '', + 'pkgdetailnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'pkgdetailnum', 'unique' => [], @@ -2741,6 +2960,7 @@ sub tables_hashref { 'otaker', 'varchar', 'NULL', 32, '', '', 'usernum', 'int', 'NULL', '', '', '', 'date', @date_type, '', '', + 'num_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'num', 'unique' => [], @@ -2758,6 +2978,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', '', '', '', '', @@ -2768,6 +3011,8 @@ sub tables_hashref { 'otaker', 'varchar', 'NULL', 32, '', '', 'usernum', 'int', 'NULL', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'setuprecur', 'char', 'NULL', 5, '', '', + 'pkgdiscountnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'pkgdiscountnum', 'unique' => [], @@ -2832,6 +3077,7 @@ sub tables_hashref { 'pkgdiscountnum', 'int', '', '', '', '', 'amount', @money_type, '', '', 'months', 'decimal', 'NULL', '7,4', '', '', + 'billpkgdiscountnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgdiscountnum', 'unique' => [], @@ -2853,6 +3099,7 @@ sub tables_hashref { 'pkgdiscountnum', 'int', '', '', '', '', 'amount', @money_type, '', '', 'months', 'decimal', 'NULL', '7,4', '', '', + 'billpkgdiscountnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgdiscountnum', 'unique' => [], @@ -2879,6 +3126,7 @@ sub tables_hashref { 'disabled', 'char', 'NULL', 1, '', '', 'setup', 'char', 'NULL', 1, '', '', #'linked', 'char', 'NULL', 1, '', '', + 'discountnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'discountnum', 'unique' => [], @@ -2896,6 +3144,7 @@ sub tables_hashref { 'classname', 'varchar', '', $char_d, '', '', #'categorynum', 'int', 'NULL', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'classnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'classnum', 'unique' => [], @@ -2917,18 +3166,22 @@ sub tables_hashref { # be index into payby # table eventually 'payinfo', 'varchar', 'NULL', 512, '', '', #see cust_main above - 'paymask', 'varchar', 'NULL', $char_d, '', '', + 'paycardtype', 'varchar', 'NULL', $char_d, '', '', + 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paybatch', 'varchar', 'NULL', $char_d, '', '', 'closed', 'char', 'NULL', 1, '', '', + 'source_paynum', 'int', 'NULL', '', '', '', # link to cust_payby, to prevent unapply of gateway-generated refunds # credit card/EFT fields (formerly in paybatch) 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK 'processor', 'varchar', 'NULL', $char_d, '', '', # module name 'auth', 'varchar','NULL',16, '', '', # CC auth number 'order_number', 'varchar','NULL',$char_d, '', '', # transaction number + 'is_tokenized', 'char', 'NULL', 1, '', '', + 'refundnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'refundnum', 'unique' => [], - 'index' => [ ['custnum'], ['_date'], [ 'usernum' ], ], + 'index' => [ ['custnum'], ['_date'], [ 'usernum' ], ['is_tokenized'] ], 'foreign_keys' => [ { columns => [ 'custnum' ], table => 'cust_main', @@ -2942,6 +3195,10 @@ sub tables_hashref { { columns => [ 'gatewaynum' ], table => 'payment_gateway', }, + { columns => [ 'source_paynum' ], + table => 'cust_pay', + references => [ 'paynum' ], + }, ], }, @@ -2952,6 +3209,7 @@ sub tables_hashref { 'refundnum', 'int', '', '', '', '', 'amount', @money_type, '', '', '_date', @date_type, '', '', + 'creditrefundnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'creditrefundnum', 'unique' => [], @@ -2974,6 +3232,7 @@ sub tables_hashref { 'svcpart', 'int', '', '', '', '', 'agent_svcid', 'int', 'NULL', '', '', '', 'overlimit', @date_type, '', '', + 'svcnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -3050,7 +3309,8 @@ sub tables_hashref { 'part_pkg' => { 'columns' => [ 'pkgpart', 'serial', '', '', '', '', - 'pkg', 'varchar', '', $char_d, '', '', + 'pkgpartbatch', 'varchar', 'NULL', $char_d, '', '', + 'pkg', 'varchar', '', 104, '', '', 'comment', 'varchar', 'NULL', 2*$char_d, '', '', 'promo_code', 'varchar', 'NULL', $char_d, '', '', 'freq', 'varchar', '', $char_d, '', '', #billing frequency @@ -3078,6 +3338,11 @@ sub tables_hashref { 'delay_start', 'int', 'NULL', '', '', '', 'start_on_hold', 'char', 'NULL', 1, '', '', 'agent_pkgpartid', 'varchar', 'NULL', 20, '', '', + 'expire_months', 'int', 'NULL', '', '', '', + 'adjourn_months', 'int', 'NULL', '', '', '', + 'contract_end_months','int','NULL', '', '', '', + 'change_to_pkgpart', 'int', 'NULL', '', '', '', + 'units_taxproductnum','int','NULL', '', '', '', ], 'primary_key' => 'pkgpart', 'unique' => [], @@ -3095,6 +3360,10 @@ sub tables_hashref { { columns => [ 'taxproductnum' ], table => 'part_pkg_taxproduct', }, + { columns => [ 'units_taxproductnum' ], + table => 'part_pkg_taxproduct', + references => [ 'taxproductnum' ], + }, { columns => [ 'agentnum' ], table => 'agent', }, @@ -3106,6 +3375,10 @@ sub tables_hashref { table => 'part_pkg', references => [ 'pkgpart' ], }, + { columns => [ 'change_to_pkgpart' ], + table => 'part_pkg', + references => [ 'pkgpart' ], + }, ], }, @@ -3415,6 +3688,8 @@ sub tables_hashref { 'primary_svc', 'char', 'NULL', 1, '', '', 'hidden', 'char', 'NULL', 1, '', '', 'bulk_skip', 'char', 'NULL', 1, '', '', + 'provision_hold', 'char', 'NULL', 1, '', '', + 'pkgsvcnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'pkgsvcnum', 'unique' => [ ['pkgpart', 'svcpart'] ], @@ -3434,10 +3709,12 @@ sub tables_hashref { 'refnum', 'serial', '', '', '', '', 'referral', 'varchar', '', $char_d, '', '', 'disabled', 'char', 'NULL', 1, '', '', - 'agentnum', 'int', 'NULL', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'title', 'varchar', 'NULL', $char_d, '', '', + 'refnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'refnum', - 'unique' => [], + 'unique' => [ ['agentnum', 'title'] ], 'index' => [ ['disabled'], ['agentnum'], ], 'foreign_keys' => [ { columns => [ 'agentnum' ], @@ -3457,6 +3734,7 @@ sub tables_hashref { 'classnum', 'int', 'NULL', '', '', '', 'restrict_edit_password','char', 'NULL', 1, '', '', 'has_router', 'char', 'NULL', 1, '', '', + 'svcpart_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'svcpart', 'unique' => [], @@ -3476,6 +3754,8 @@ sub tables_hashref { 'columnlabel', 'varchar', 'NULL', $char_d, '', '', 'columnvalue', 'varchar', 'NULL', 512, '', '', 'columnflag', 'char', 'NULL', 1, '', '', + 'required', 'char', 'NULL', 1, '', '', + 'columnnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'columnnum', 'unique' => [ [ 'svcpart', 'columnname' ] ], @@ -3491,13 +3771,57 @@ sub tables_hashref { 'columns' => [ 'classnum', 'serial', '', '', '', '', 'classname', 'varchar', '', $char_d, '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'classnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'classnum', 'unique' => [], 'index' => [ ['disabled'] ], }, + 'part_svc_link' => { + 'columns' => [ + 'svclinknum', 'serial', '', '', '', '', + #'linkname', 'varchar', 'NULL', $char_d, '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'src_svcpart', 'int', '', '', '', '', + 'dst_svcpart', 'int', '', '', '', '', + 'link_type', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'svclinknum', + 'unique' => [ ['agentnum','src_svcpart','dst_svcpart','link_type'] ], + 'index' => [ [ 'src_svcpart' ], [ 'src_svcpart', 'link_type' ], [ 'disabled' ] ], + 'foreign_keys' => [ + { columns => [ 'src_svcpart' ], + table => 'part_svc', + references => [ 'svcpart' ] + }, + { columns => [ 'dst_svcpart' ], + table => 'part_svc', + references => [ 'svcpart' ] + }, + ], + }, + + 'part_svc_msgcat' => { + 'columns' => [ + 'svcpartmsgnum', 'serial', '', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'locale', 'varchar', '', 16, '', '', + 'svc', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'svcpartmsgnum', + 'unique' => [ [ 'svcpart', 'locale' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'svcpart' ], + table => 'part_svc', + }, + ], + }, + + #(this should be renamed to part_pop) 'svc_acct_pop' => { 'columns' => [ @@ -3584,6 +3908,7 @@ sub tables_hashref { 'svc_acct' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', + 'svcnum_import', 'int', 'NULL', '', '', '', 'username', 'varchar', '', $username_len, '', '', '_password', 'varchar', 'NULL', 512, '', '', '_password_encoding', 'varchar', 'NULL', $char_d, '', '', @@ -3686,9 +4011,12 @@ sub tables_hashref { 'unique' => [], 'index' => [ ['svcnum', 'transaction_id'] ], 'foreign_keys' => [ - { columns => [ 'svcnum' ], - table => 'svc_acct', #'cust_svc', - }, + # problems w/deleted services, and as per below, this + # is our internal hack, not a customer-facing feature + #{ columns => [ 'svcnum' ], + # table => 'svc_acct', #'cust_svc', + #}, + # 1. RT tables aren't part of our data structure, so # we can't make sure Queue is created already # 2. This is our internal hack for time tracking, not @@ -3713,6 +4041,7 @@ sub tables_hashref { 'svc_domain' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', + 'svcnum_import', 'int', 'NULL', '', '', '', 'domain', 'varchar', '', $char_d, '', '', 'suffix', 'varchar', 'NULL', $char_d, '', '', 'catchall', 'int', 'NULL', '', '', '', @@ -3886,7 +4215,7 @@ sub tables_hashref { 'note', 'text', 'NULL', '', '', '', ], 'primary_key' => 'svcnum', - 'unique' => [], + 'unique' => [ ['typenum', 'serial'], ['ip_addr'], ['hw_addr'] ], 'index' => [], 'foreign_keys' => [ { columns => [ 'svcnum' ], @@ -3917,6 +4246,7 @@ sub tables_hashref { 'classnum', 'int', '', '', '', '', 'model', 'varchar', '', $char_d, '', '', 'revision','varchar', 'NULL', $char_d, '', '', + 'title', 'varchar', 'NULL', $char_d, '', '', # external id ], 'primary_key' => 'typenum', 'unique' => [ [ 'classnum', 'model', 'revision' ] ], @@ -4177,6 +4507,7 @@ sub tables_hashref { 'custnum', 'int', 'NULL', '', '', '', 'secure', 'char', 'NULL', 1, '', '', 'priority', 'int', 'NULL', '', '', '', + 'usernum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'jobnum', 'unique' => [], @@ -4190,6 +4521,9 @@ sub tables_hashref { { columns => [ 'custnum' ], table => 'cust_main', }, + { columns => [ 'usernum' ], + table => 'access_user', + }, ], }, @@ -4249,9 +4583,11 @@ sub tables_hashref { 'export_svc' => { 'columns' => [ - 'exportsvcnum' => 'serial', '', '', '', '', - 'exportnum' => 'int', '', '', '', '', - 'svcpart' => 'int', '', '', '', '', + 'exportsvcnum', 'serial', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'role', 'varchar', 'NULL', 16, '', '', + 'exportsvcnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'exportsvcnum', 'unique' => [ [ 'exportnum', 'svcpart' ] ], @@ -4266,6 +4602,26 @@ sub tables_hashref { ], }, + 'export_cust_svc' => { + 'columns' => [ + 'exportcustsvcnum', 'serial', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'remoteid', 'varchar', '', 512, '', '', + ], + 'primary_key' => 'exportcustsvcnum', + 'unique' => [ [ 'exportnum', 'svcnum' ] ], + 'index' => [ [ 'exportnum', 'svcnum' ] ], + 'foreign_keys' => [ + { columns => [ 'exportnum' ], + table => 'part_export', + }, + { columns => [ 'svcnum' ], + table => 'cust_svc', + }, + ], + }, + 'export_device' => { 'columns' => [ 'exportdevicenum' => 'serial', '', '', '', '', @@ -4293,6 +4649,9 @@ sub tables_hashref { 'exporttype', 'varchar', '', $char_d, '', '', 'nodomain', 'char', 'NULL', 1, '', '', 'default_machine','int', 'NULL', '', '', '', + 'no_suspend', 'char', 'NULL', 1, '', '', + # could also have 'no_insert', 'no_replace', etc. + 'exportnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'exportnum', 'unique' => [], @@ -4311,6 +4670,7 @@ sub tables_hashref { 'exportnum', 'int', '', '', '', '', 'optionname', 'varchar', '', $char_d, '', '', 'optionvalue', 'text', 'NULL', '', '', '', + 'optionnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'optionnum', 'unique' => [], @@ -4328,6 +4688,7 @@ sub tables_hashref { 'svcnum', 'int', '', '', '', '', 'groupname', 'varchar', 'NULL', $char_d, '', '', #deprecated 'groupnum', 'int', 'NULL', '', '', '', + 'usergroupnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'usergroupnum', 'unique' => [], @@ -4350,6 +4711,7 @@ sub tables_hashref { 'priority', 'int', '', '', '1', '', 'speed_up', 'int', 'NULL', '', '', '', 'speed_down', 'int', 'NULL', '', '', '', + 'groupnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'groupnum', 'unique' => [ ['groupname'] ], @@ -4415,6 +4777,7 @@ sub tables_hashref { #'custnum', 'int', '', '', '', '' 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', + 'taxtype', 'varchar', 'NULL', $char_d, '', '', 'year', 'int', 'NULL', '', '', '', 'month', 'int', 'NULL', '', '', '', 'creditbillpkgnum', 'int', 'NULL', '', '', '', @@ -4430,16 +4793,13 @@ sub tables_hashref { 'unique' => [], 'index' => [ [ 'taxnum', 'year', 'month' ], [ 'billpkgnum' ], - [ 'taxnum' ], + [ 'taxnum', 'taxtype' ], [ 'creditbillpkgnum' ], ], 'foreign_keys' => [ { columns => [ 'billpkgnum' ], table => 'cust_bill_pkg', }, - { columns => [ 'taxnum' ], - table => 'cust_main_county', - }, { columns => [ 'creditbillpkgnum' ], table => 'cust_credit_bill_pkg', }, @@ -4452,6 +4812,7 @@ sub tables_hashref { #'custnum', 'int', '', '', '', '' 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', + 'taxtype', 'varchar', 'NULL', $char_d, '', '', 'year', 'int', 'NULL', '', '', '', 'month', 'int', 'NULL', '', '', '', 'creditbillpkgnum', 'int', 'NULL', '', '', '', @@ -4467,7 +4828,7 @@ sub tables_hashref { 'unique' => [], 'index' => [ [ 'taxnum', 'year', 'month' ], [ 'billpkgnum' ], - [ 'taxnum' ], + [ 'taxnum', 'taxtype' ], [ 'creditbillpkgnum' ], ], 'foreign_keys' => [ @@ -4560,6 +4921,7 @@ sub tables_hashref { 'svc_broadband' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', + 'svcnum_import', 'int', 'NULL', '', '', '', 'description', 'varchar', 'NULL', $char_d, '', '', 'routernum', 'int', 'NULL', '', '', '', 'blocknum', 'int', 'NULL', '', '', '', @@ -4582,6 +4944,9 @@ sub tables_hashref { 'suid', 'int', 'NULL', '', '', '', 'shared_svcnum', 'int', 'NULL', '', '', '', 'serviceid', 'varchar', 'NULL', 64, '', '',#srvexport/reportfields + 'speed_test_up', 'int', 'NULL', '', '', '', + 'speed_test_down', 'int', 'NULL', '', '', '', + 'speed_test_latency', 'int', 'NULL', '', '', '', ], 'primary_key' => 'svcnum', 'unique' => [ [ 'ip_addr' ], [ 'mac_addr' ] ], @@ -4619,6 +4984,8 @@ sub tables_hashref { 'height', 'decimal', 'NULL', '', '', '', 'veg_height', 'decimal', 'NULL', '', '', '', 'color', 'varchar', 'NULL', 6, '', '', + 'up_rate_limit', 'int', 'NULL', '', '', '', + 'down_rate_limit', 'int', 'NULL', '', '', '', ], 'primary_key' => 'towernum', 'unique' => [ [ 'towername' ] ], # , 'agentnum' ] ], @@ -4635,9 +5002,24 @@ sub tables_hashref { 'freq_mhz', 'int', 'NULL', '', '', '', 'direction', 'int', 'NULL', '', '', '', 'width', 'int', 'NULL', '', '', '', - #downtilt etc? rfpath has profile files for devices/antennas you upload? 'sector_range', 'decimal', 'NULL', '', '', '', #? - ], + 'downtilt', 'decimal', 'NULL', '', '', '', + 'v_width', 'int', 'NULL', '', '', '', + 'power', 'decimal', 'NULL', '', '', '', + 'line_loss', 'decimal', 'NULL', '', '', '', + 'antenna_gain', 'decimal', 'NULL', '', '', '', + 'hardware_typenum', 'int', 'NULL', '', '', '', + 'db_high', 'int', 'NULL', '', '', '', + 'db_low', 'int', 'NULL', '', '', '', + 'image', 'blob', 'NULL', '', '', '', + 'west', 'decimal', 'NULL', '10,7', '', '', + 'east', 'decimal', 'NULL', '10,7', '', '', + 'south', 'decimal', 'NULL', '10,7', '', '', + 'north', 'decimal', 'NULL', '10,7', '', '', + 'title', 'varchar', 'NULL', $char_d,'', '', + 'up_rate_limit', 'int', 'NULL', '', '', '', + 'down_rate_limit', 'int', 'NULL', '', '', '', + ], 'primary_key' => 'sectornum', 'unique' => [ [ 'towernum', 'sectorname' ], [ 'ip_addr' ], ], 'index' => [ [ 'towernum' ] ], @@ -4645,6 +5027,27 @@ sub tables_hashref { { columns => [ 'towernum' ], table => 'tower', }, + { columns => [ 'hardware_typenum' ], + table => 'hardware_type', + references => [ 'typenum' ], + }, + ], + }, + + 'sector_coverage' => { + 'columns' => [ + 'coveragenum', 'serial', '', '', '', '', + 'sectornum', 'int', '', '', '', '', + 'db_loss', 'int', '', '', '', '', + 'geometry', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'coveragenum', + 'unique' => [], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'sectornum' ], + table => 'tower_sector' + }, ], }, @@ -4726,6 +5129,7 @@ sub tables_hashref { 'refundnum', 'int', '', '', '', '', '_date', @date_type, '', '', 'amount', @money_type, '', '', + 'payrefundnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'payrefundnum', 'unique' => [], @@ -4746,6 +5150,7 @@ sub tables_hashref { 'pkgpart', 'int', '', '', '', '', 'optionname', 'varchar', '', $char_d, '', '', 'optionvalue', 'text', 'NULL', '', '', '', + 'optionnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'optionnum', 'unique' => [], @@ -4833,6 +5238,7 @@ sub tables_hashref { 'fccoptionname', 'varchar', '', $char_d, '', '', 'pkgpart', 'int', '', '', '', '', 'optionvalue', 'varchar', 'NULL', $char_d, '', '', + 'num_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'num', 'unique' => [ [ 'fccoptionname', 'pkgpart' ] ], @@ -4841,13 +5247,14 @@ sub tables_hashref { 'rate' => { 'columns' => [ - 'ratenum', 'serial', '', '', '', '', - 'ratename', 'varchar', '',$char_d, '', '', - 'agentnum', 'int', 'NULL', '', '', '', - 'default_detailnum', 'int', 'NULL', '', '', '', + 'ratenum', 'serial', '', '', '', '', + 'ratename', 'varchar', '', $char_d, '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'default_detailnum', 'int', 'NULL', '', '', '', + 'agent_rateid', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'ratenum', - 'unique' => [], + 'unique' => [ ['agentnum','agent_rateid'], ], 'index' => [], 'foreign_keys' => [ { columns => [ 'agentnum' ], @@ -4910,10 +5317,11 @@ sub tables_hashref { 'regionnum', 'serial', '', '', '', '', 'regionname', 'varchar', '', $char_d, '', '', 'exact_match', 'char', 'NULL', 1, '', '', + 'agent_regionid', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'regionnum', 'unique' => [], - 'index' => [], + 'index' => [ ['agent_regionid'] ], }, 'rate_prefix' => { @@ -4928,7 +5336,7 @@ sub tables_hashref { 'ocn', 'char', 'NULL', 4, '', '', ], 'primary_key' => 'prefixnum', - 'unique' => [], + 'unique' => [ [ 'countrycode', 'npa', 'nxx' ] ], 'index' => [ [ 'countrycode' ], [ 'npa' ], [ 'regionnum' ] ], 'foreign_keys' => [ { columns => [ 'regionnum' ], @@ -5131,16 +5539,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' => [], @@ -5160,6 +5569,7 @@ sub tables_hashref { 'ticketing_queueid', 'int', 'NULL', '', '', '', 'condense', 'char', 'NULL', 1, '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'categorunum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'categorynum', 'unique' => [], @@ -5173,6 +5583,7 @@ sub tables_hashref { 'categorynum', 'int', 'NULL', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', 'fcc_ds0s', 'int', 'NULL', '', '', '', + 'classnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'classnum', 'unique' => [], @@ -5231,7 +5642,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, \"''", '', @@ -5293,6 +5704,11 @@ sub tables_hashref { 'rated_ratedetailnum', 'int', 'NULL', '', '', '', 'rated_classnum', 'int', 'NULL', '', '', '', 'rated_ratename', 'varchar', 'NULL', $char_d, '', '', + 'rated_cost', 'decimal', 'NULL', '10,4', '', '', + + # real endpoints of the call + 'src_lrn', 'varchar', 'NULL', '15', '', '', + 'dst_lrn', 'varchar', 'NULL', '15', '', '', 'carrierid', 'bigint', 'NULL', '', '', '', @@ -5302,6 +5718,9 @@ sub tables_hashref { #NULL, done (or something) 'freesidestatus', 'varchar', 'NULL', 32, '', '', + #additional status info (i.e. reason for "skipped" freesidestatus) + 'freesidestatustext', 'text', 'NULL', '', '', '', + #NULL, done (or something) 'freesiderewritestatus', 'varchar', 'NULL', 32, '', '', @@ -5312,9 +5731,6 @@ sub tables_hashref { 'sessionnum', 'int', 'NULL', '', '', '', 'subscriber', 'varchar', 'NULL', $char_d, '', '', - #old - 'cdrbatch', 'varchar', 'NULL', 255, '', '', - #new 'cdrbatchnum', 'int', 'NULL', '', '', '', # FK to cust_bill_pkg_detail; having a value here absolutely means @@ -5325,12 +5741,15 @@ sub tables_hashref { 'unique' => [], 'index' => [ [ 'calldate' ], [ 'src' ], [ 'dst' ], [ 'dcontext' ], [ 'charged_party' ], + [ 'startdate' ], [ 'duration' ], + [ 'cdrtypenum' ], [ 'calltypenum' ], [ 'lastapp' ], ['accountcode'], ['uniqueid'], ['carrierid'], ['cdrid'], [ 'sessionnum' ], [ 'subscriber' ], [ 'freesidestatus' ], [ 'freesiderewritestatus' ], - [ 'cdrbatch' ], [ 'cdrbatchnum' ], + [ 'cdrbatchnum' ], [ 'src_ip_addr' ], [ 'dst_ip_addr' ], [ 'dst_term' ], + [ 'detailnum' ], ], #no FKs on cdr table... choosing not to throw errors no matter what's # thrown in here. better to have the data. @@ -5355,6 +5774,7 @@ sub tables_hashref { 'rated_price', 'decimal', 'NULL', '10,4', '', '', 'rated_seconds', 'int', 'NULL', '', '', '', 'rated_minutes', 'double precision', 'NULL', '', '', '', + 'rated_granularity','int', 'NULL', '', '', '', 'status', 'varchar', 'NULL', 32, '', '', 'svcnum', 'int', 'NULL', '', '', '', ], @@ -5483,6 +5903,25 @@ sub tables_hashref { ], }, + 'access_user_session_log' => { + 'columns' => [ + 'sessionlognum', 'serial', '', '', '', '', + 'usernum', 'int', '', '', '', '', + 'start_date', @date_type, '', '', + 'last_date', @date_type, '', '', + 'logout_date', @date_type, '', '', + 'logout_type', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'sessionlognum', + 'unique' => [], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'usernum' ], + table => 'access_user', + }, + ], + }, + 'access_user' => { 'columns' => [ 'usernum', 'serial', '', '', '', '', @@ -5530,8 +5969,9 @@ sub tables_hashref { 'access_group' => { 'columns' => [ - 'groupnum', 'serial', '', '', '', '', - 'groupname', 'varchar', '', $char_d, '', '', + 'groupnum', 'serial', '', '', '', '', + 'groupname', 'varchar', '', $char_d, '', '', + 'session_timeout', 'int', 'NULL', '', '', '', ], 'primary_key' => 'groupnum', 'unique' => [ [ 'groupname' ] ], @@ -5588,6 +6028,40 @@ sub tables_hashref { 'index' => [], }, + 'access_user_log' => { + 'columns' => [ + 'lognum', 'serial', '', '', '', '', + 'usernum', 'int', '', '', '', '', + 'path', 'varchar', '', 2*$char_d, '', '', + '_date', @date_type, '', '', + 'render_seconds', 'int', 'NULL', '', '', '', + 'pid', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'lognum', + 'unique' => [], + 'index' => [ ['usernum'], ['path'], ['_date'] ], + }, + + 'access_user_page_pref' => { + 'columns' => [ + 'prefnum' => 'serial', '', '', '', '', + 'usernum' => 'int', '', '', '', '', + 'path' => 'text', '', '', '', '', + 'tablenum' => 'int', 'NULL', '', '', '', + '_date' => @date_type, '', '', + 'prefname' => 'varchar', '', $char_d, '', '', + 'prefvalue' => 'text', '', '', '', '', + ], + 'primary_key' => 'prefnum', + 'unique' => [ [ 'usernum', 'path', 'tablenum', 'prefname' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'usernum' ], + table => 'access_user' + }, + ], + }, + 'sched_item' => { 'columns' => [ 'itemnum', 'serial', '', '', '', '', @@ -5638,6 +6112,7 @@ sub tables_hashref { 'pbxsvc', 'int', 'NULL', '', '', '', 'domsvc', 'int', 'NULL', '', '', '', 'locationnum', 'int', 'NULL', '', '', '', + 'forward_svcnum', 'int', 'NULL', '', '', '', 'forwarddst', 'varchar', 'NULL', 15, '', '', 'email', 'varchar', 'NULL', 255, '', '', 'lnp_status', 'varchar', 'NULL', $char_d, '', '', @@ -5648,11 +6123,16 @@ sub tables_hashref { 'lnp_other_provider', 'varchar', 'NULL', $char_d, '', '', 'lnp_other_provider_account', 'varchar', 'NULL', $char_d, '', '', 'lnp_reject_reason', 'varchar', 'NULL', $char_d, '', '', + 'lnp_portid', 'varchar', 'NULL', $char_d, '', '', + 'lnp_signature', 'char', 'NULL', 1, '', '', + 'lnp_bill', 'char', 'NULL', 1, '', '', 'sms_carrierid', 'int', 'NULL', '', '', '', 'sms_account', 'varchar', 'NULL', $char_d, '', '', 'max_simultaneous', 'int', 'NULL', '', '', '', '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'] ], @@ -5678,6 +6158,10 @@ sub tables_hashref { table => 'cdr_carrier', references => [ 'carrierid' ], }, + { columns => [ 'circuit_svcnum' ], + table => 'svc_circuit', + references => [ 'svcnum' ], + }, ], }, @@ -5706,6 +6190,7 @@ sub tables_hashref { 'devicepart', 'serial', '', '', '', '', 'devicename', 'varchar', '', $char_d, '', '', 'inventory_classnum', 'int', 'NULL', '', '', '', + 'title', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'devicepart', 'unique' => [ [ 'devicename' ] ], #? @@ -5867,6 +6352,7 @@ sub tables_hashref { 'typenum', 'serial', '', '', '', '', 'class', 'char', '', 1, '', '', 'type', 'varchar', '', $char_d, '', '', + 'typenum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'typenum', 'unique' => [], @@ -5882,6 +6368,10 @@ 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, '', '', + 'reasonnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'reasonnum', 'unique' => [], @@ -5921,6 +6411,7 @@ sub tables_hashref { 'pkgrefnum', 'serial', '', '', '', '', 'pkgnum', 'int', '', '', '', '', 'refnum', 'int', '', '', '', '', + 'pkgrefnum_import', 'int', 'NULL', '', '', '', ], 'primary_key' => 'pkgrefnum', 'unique' => [ [ 'pkgnum', 'refnum' ] ], @@ -5939,13 +6430,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', @@ -6135,8 +6628,11 @@ sub tables_hashref { 'mime_type', 'varchar', '', $char_d, '', '', 'body', 'blob', 'NULL', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', + # migrate these to msg_template_email 'from_addr', 'varchar', 'NULL', 255, '', '', 'bcc_addr', 'varchar', 'NULL', 255, '', '', + # change to not null on v5 + 'msgclass', 'varchar', 'NULL', 16, '', '', ], 'primary_key' => 'msgnum', 'unique' => [ ], @@ -6148,6 +6644,26 @@ sub tables_hashref { ], }, + 'msg_template_http' => { + 'columns' => [ + 'num', 'serial', '', '', '', '', + 'msgnum', 'int', '', '', '', '', + 'prepare_url', 'varchar', 'NULL', 255, '', '', + 'send_url', 'varchar', 'NULL', 255, '', '', + 'username', 'varchar', 'NULL', $char_d, '', '', + 'password', 'varchar', 'NULL', $char_d, '', '', + 'content', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'num', + 'unique' => [ [ 'msgnum' ], ], + 'index' => [ ], + 'foreign_keys' => [ + { columns => [ 'msgnum' ], + table => 'msg_template', + }, + ], + }, + 'template_content' => { 'columns' => [ 'contentnum', 'serial', '', '', '', '', @@ -6166,6 +6682,19 @@ sub tables_hashref { ], }, + 'template_image' => { + 'columns' => [ + 'imgnum', 'serial', '', '', '', '', + 'name', 'varchar', '', $char_d, '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'mime_type', 'varchar', '', $char_d, '', '', + 'base64', 'text', '', '', '', '', + ], + 'primary_key' => 'imgnum', + 'unique' => [ ], + 'index' => [ ['name'], ['agentnum'] ], + }, + 'cust_msg' => { 'columns' => [ 'custmsgnum', 'serial', '', '', '', '', @@ -6179,6 +6708,7 @@ sub tables_hashref { 'error', 'varchar', 'NULL', 255, '', '', 'status', 'varchar', '',$char_d, '', '', 'msgtype', 'varchar', 'NULL', 16, '', '', + 'preview', 'text', 'NULL', '', '', '', ], 'primary_key' => 'custmsgnum', 'unique' => [ ], @@ -6322,6 +6852,26 @@ sub tables_hashref { ], }, + 'log_email' => { + 'columns' => [ + 'logemailnum', 'serial', '', '', '', '', + 'context', 'varchar', 'NULL', $char_d, '', '', + 'min_level', 'int', 'NULL', '', '', '', + 'msgnum', 'int', '', '', '', '', + 'to_addr', 'varchar', 'NULL', 255, '', '', + 'context_height', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'logemailnum', + 'unique' => [], + 'index' => [ ['context'], ['min_level'] ], + 'foreign_keys' => [ + { columns => [ 'msgnum' ], + table => 'msg_template', + references => [ 'msgnum' ], + }, + ], + }, + 'svc_alarm' => { 'columns' => [ # name type null length default local @@ -6418,7 +6968,7 @@ sub tables_hashref { 'mac_addr', 'varchar', 'NULL', 12, '', '', ], 'primary_key' => 'svcnum', - 'unique' => [ ['serialnum'] , ['mac_addr'] ], + 'unique' => [ ['modelnum', 'serialnum'] , ['mac_addr'] ], 'index' => [], 'foreign_keys' => [ { columns => [ 'svcnum' ], @@ -6527,6 +7077,144 @@ sub tables_hashref { ], }, + 'circuit_type' => { + 'columns' => [ + 'typenum', 'serial', '', '', '', '', + 'typename', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + # speed? number of voice lines? anything else? + ], + 'primary_key' => 'typenum', + 'unique' => [ [ 'typename' ] ], + 'index' => [], + }, + + 'circuit_provider' => { + 'columns' => [ + 'providernum', 'serial', '', '', '', '', + 'provider', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'providernum', + 'unique' => [ [ 'provider' ], ], + 'index' => [], + }, + + 'circuit_termination' => { + 'columns' => [ + 'termnum', 'serial', '', '', '', '', + 'termination','varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'termnum', + 'unique' => [ [ 'termination' ] ], + 'index' => [], + }, + + 'svc_circuit' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'typenum', 'int', '', '', '', '', + 'providernum', 'int', '', '', '', '', + 'termnum', 'int', '', '', '', '', + 'circuit_id', 'varchar', '', 64, '', '', + 'desired_due_date', 'int', 'NULL', '', '', '', + 'due_date', 'int', 'NULL', '', '', '', + 'vendor_order_id', 'varchar', 'NULL', $char_d, '', '', + 'vendor_qual_id', 'varchar', 'NULL', $char_d, '', '', + 'vendor_order_type', 'varchar', 'NULL', $char_d, '', '', + 'vendor_order_status', 'varchar', 'NULL', $char_d, '', '', + 'endpoint_ip_addr', 'varchar', 'NULL', 40, '', '', + 'endpoint_mac_addr', 'varchar', 'NULL', 12, '', '', + 'internal_circuit_id', 'varchar', 'NULL', 64, '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [], + 'index' => [ [ 'providernum' ], [ 'typenum' ] ], + 'foreign_keys' => [ + { columns => [ 'svcnum' ], + table => 'cust_svc', + }, + { columns => [ 'typenum' ], + table => 'circuit_type', + }, + { columns => [ 'providernum' ], + table => 'circuit_provider', + }, + { columns => [ 'termnum' ], + table => 'circuit_termination', + }, + ], + }, + + 'svc_fiber' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'oltnum', 'int', 'NULL', '', '', '', + 'shelf', 'int', 'NULL', '', '', '', + 'card', 'int', 'NULL', '', '', '', + 'olt_port', 'int', 'NULL', '', '', '', + 'ont_id', 'int', 'NULL', '', '', '', + 'ont_typenum', 'int', 'NULL', '', '', '', + 'ont_serial', 'varchar', 'NULL', $char_d, '', '', + 'ont_port', 'varchar', 'NULL', 16, '', '', + 'circuit_id', 'varchar', 'NULL', $char_d, '', '', + 'vlan', 'int', 'NULL', '', '', '', + 'signal', 'decimal', 'NULL', '10,2', '', '', + 'speed_up', 'int', 'NULL', '', '', '', + 'speed_down', 'int', 'NULL', '', '', '', + 'ont_install','varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [ ], + 'index' => [ [ 'ont_serial' ] ], + 'foreign_keys' => [ + { columns => [ 'svcnum' ], + table => 'cust_svc', + }, + { columns => [ 'oltnum' ], + table => 'fiber_olt', + }, + { columns => [ 'ont_typenum' ], + table => 'hardware_type', + references => [ 'typenum' ], + }, + ], + }, + + 'fiber_olt' => { + 'columns' => [ + 'oltnum', 'serial', '', '', '', '', + 'oltname', 'varchar', '', $char_d, '', '', + 'sitenum', 'int', 'NULL', '', '', '', + 'serial', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'oltnum', + 'unique' => [ ], + 'index' => [ ], + 'foreign_keys' => [ + { columns => [ 'sitenum' ], + table => 'olt_site', + }, + ], + }, + + 'olt_site' => { + 'columns' => [ + 'sitenum', 'serial', '', '', '', '', + 'market', 'varchar', '', $char_d, '', '', + 'site', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'sitenum', + 'unique' => [ [ 'market', 'site' ] ], + 'index' => [ ], + }, + + + + + 'vend_main' => { 'columns' => [ 'vendnum', 'serial', '', '', '', '', @@ -6675,6 +7363,8 @@ sub tables_hashref { 'latexsmallfooter', 'text', 'NULL', '', '', '', 'latexreturnaddress', 'text', 'NULL', '', '', '', 'with_latexcoupon', 'char', 'NULL', '1', '', '', + 'htmlwatermark', 'text', 'NULL', '', '', '', + 'latexwatermark', 'text', 'NULL', '', '', '', 'lpr', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'confnum', @@ -6745,6 +7435,7 @@ sub tables_hashref { 'zonenum', 'serial', '', '', '', '', 'description', 'char', 'NULL', $char_d, '', '', 'agentnum', 'int', '', '', '', '', + 'censusyear', 'char', 'NULL', 4, '', '', 'dbaname', 'char', 'NULL', $char_d, '', '', 'zonetype', 'char', '', 1, '', '', 'technology', 'int', '', '', '', '', @@ -6759,6 +7450,7 @@ sub tables_hashref { 'is_business', 'char', 'NULL', 1, '', '', 'active_date', @date_type, '', '', 'expire_date', @date_type, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'zonenum', 'unique' => [], @@ -6776,7 +7468,7 @@ sub tables_hashref { 'blocknum', 'serial', '', '', '', '', 'zonenum', 'int', '', '', '', '', 'censusblock', 'char', '', 15, '', '', - 'censusyear', 'char', '', 4, '', '', + 'censusyear', 'char','NULL', 4, '', '', ], 'primary_key' => 'blocknum', 'unique' => [], @@ -6807,8 +7499,282 @@ sub tables_hashref { ], }, + 'cacti_page' => { + 'columns' => [ + 'cacti_pagenum', 'serial', '', '', '', '', + 'exportnum', 'int', 'NULL', '', '', '', + 'svcnum', 'int', 'NULL', '', '', '', + 'graphnum', 'int', 'NULL', '', '', '', + 'imported', @date_type, '', '', + 'content', 'text', 'NULL', '', '', '', + 'thumbnail', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'cacti_pagenum', + 'unique' => [ ], + 'index' => [ ['svcnum'], ['imported'] ], + 'foreign_keys' => [ + { columns => [ 'svcnum' ], + table => 'cust_svc', + references => [ 'svcnum' ], + }, + { columns => [ 'exportnum' ], + table => 'part_export', + references => [ 'exportnum' ], + }, + ], + }, + 'report_batch' => { + 'columns' => [ + 'reportbatchnum', 'serial', '', '', '', '', + 'reportname', 'varchar', '', 255, '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'send_date', @date_type, '', '', + 'sdate', @date_type, '', '', + 'edate', @date_type, '', '', + 'usernum', 'int', 'NULL', '', '', '', + 'msgnum', 'int', 'NULL', '', '', '', + # add report params here as necessary + ], + 'primary_key' => 'reportbatchnum', + 'unique' => [], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'agentnum' ], + table => 'agent', + references => [ 'agentnum' ], + }, + { columns => [ 'usernum' ], + table => 'access_user', + references => [ 'usernum' ], + }, + { columns => [ 'msgnum' ], + table => 'msg_template', + references => [ 'msgnum' ], + }, + ], + }, + 'password_history' => { + 'columns' => [ + 'passwordnum', 'serial', '', '', '', '', + '_password', 'varchar', 'NULL', $char_d, '', '', + 'encryption_method', 'varchar', 'NULL', $char_d, '', '', + 'created', @date_type, '', '', + # each table that needs password history gets a column here, and + # an entry in foreign_keys. + 'svc_acct__svcnum', 'int', 'NULL', '', '', '', + 'svc_dsl__svcnum', 'int', 'NULL', '', '', '', + 'svc_alarm__svcnum', 'int', 'NULL', '', '', '', + 'agent__agentnum', 'int', 'NULL', '', '', '', + 'contact__contactnum', 'int', 'NULL', '', '', '', + 'access_user__usernum', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'passwordnum', + 'unique' => [], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'svc_acct__svcnum' ], + table => 'svc_acct', + references => [ 'svcnum' ], + }, + { columns => [ 'svc_dsl__svcnum' ], + table => 'svc_dsl', + references => [ 'svcnum' ], + }, + { columns => [ 'svc_alarm__svcnum' ], + table => 'svc_alarm', + references => [ 'svcnum' ], + }, + { columns => [ 'agent__agentnum' ], + table => 'agent', + references => [ 'agentnum' ], + }, + { columns => [ 'contact__contactnum' ], + table => 'contact', + references => [ 'contactnum' ], + }, + { columns => [ 'access_user__usernum' ], + table => 'access_user', + references => [ 'usernum' ], + }, + ], + }, + + 'webservice_log' => { + 'columns' => [ + 'webservicelognum', 'serial', '', '', '', '', #big? hubrus + 'svcnum', 'int', 'NULL', '', '', '', #just in case + 'custnum', 'int', '', '', '', '', + 'method', 'varchar', '', $char_d, '', '', + 'quantity', 'int', '', '', '', '', #i.e. pages + '_date', @date_type, '', '', + 'status', 'varchar', 'NULL', $char_d, '', '', + 'rated_price', 'decimal', 'NULL', '10,2', '', '', + ], + 'primary_key' => 'webservicelognum', + 'unique' => [], + 'index' => [ ['custnum'], ['status'] ], + 'foreign_keys' => [ + { columns => [ 'custnum' ], + table => 'cust_main', + }, + #no FK on svcnum... we don't want to purge these on + # service deletion + ], + }, + + 'rt_field_charge' => { + 'columns' => [ + 'rtfieldchargenum', 'serial', '', '', '', '', + 'pkgnum', 'int', '', '', '', '', + 'ticketid', 'int', '', '', '', '', + 'rate', @money_type, '', '', + 'units', 'decimal', '', '10,4', '', '', + 'charge', @money_type, '', '', + '_date', @date_type, '', '', + ], + 'primary_key' => 'rtfieldchargenum', + 'unique' => [], + 'index' => [ ['pkgnum', 'ticketid'] ], + 'foreign_keys' => [ + { columns => [ 'pkgnum' ], + table => 'cust_pkg', + }, + ], + }, + + 'commission_schedule' => { + 'columns' => [ + 'schedulenum', 'serial', '', '', '', '', + 'schedulename', 'varchar', '', $char_d, '', '', + 'reasonnum', 'int', 'NULL', '', '', '', + 'basis', 'varchar', 'NULL', 32, '', '', + ], + 'primary_key' => 'schedulenum', + 'unique' => [], + 'index' => [], + }, + + 'commission_rate' => { + 'columns' => [ + 'commissionratenum', 'serial', '', '', '', '', + 'schedulenum', 'int', '', '', '', '', + 'cycle', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'percent', 'decimal','', '7,4', '', '', + ], + 'primary_key' => 'commissionratenum', + 'unique' => [ [ 'schedulenum', 'cycle', ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'schedulenum' ], + table => 'commission_schedule', + }, + ], + }, + + 'saved_search' => { + 'columns' => [ + 'searchnum', 'serial', '', '', '', '', + 'usernum', 'int', 'NULL', '', '', '', + 'searchname', 'varchar', '', $char_d, '', '', + 'path', 'varchar', '', $char_d, '', '', + 'params', 'text', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'freq', 'varchar', 'NULL', 16, '', '', + 'last_sent', 'int', 'NULL', '', '', '', + 'format', 'varchar', 'NULL', 32, '', '', + ], + 'primary_key' => 'searchnum', + 'unique' => [], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'usernum' ], + table => 'access_user', + }, + ], + }, + + 'addr_status' => { + 'columns' => [ + 'addrnum', 'serial', '', '', '', '', + 'ip_addr', 'varchar', 'NULL', 40, '', '', + '_date', @date_type, '', '', + 'up', 'char', 'NULL', 1, '', '', + 'delay', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'addrnum', + 'unique' => [ [ 'ip_addr' ] ], + 'index' => [ [ '_date' ] ], + 'foreign_keys' => [], + }, + + 'realestate_unit' => { + 'columns' => [ + 'realestatenum', 'serial', '', '', '', '', + 'realestatelocnum', 'int', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'unit_title', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'realestatenum', + 'unique' => [ ['unit_title'] ], + 'index' => [ + ['agentnum'], + ['realestatelocnum'], + ['disabled'], + ['unit_title'], + ], + 'foreign_keys' => [ + {columns => ['agentnum'], table => 'agent'}, + {columns => ['realestatelocnum'] => table => 'realestate_location'}, + ], + }, + + 'realestate_location' => { + 'columns' => [ + 'realestatelocnum', 'serial', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'location_title', 'varchar', '', $char_d, '', '', + 'address1', 'varchar', 'NULL', $char_d, '', '', + 'address2', 'varchar', 'NULL', $char_d, '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', + 'state', 'varchar', 'NULL', $char_d, '', '', + 'zip', 'char', 'NULL', 5, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'realestatelocnum', + 'unique' => [ ['location_title'] ], + 'index' => [ ['agentnum'], ['disabled'] ], + 'foreign_keys' => [ + {columns => ['agentnum'], table => 'agent'}, + ], + }, + + 'svc_realestate' => { + 'columns' => [ + 'svcnum', 'serial', '', '', '', '', + 'realestatenum', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'svcnum', + 'index' => [], + }, + + 'svc_group' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'max_accounts', 'int', '', '', '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'svcnum' ], + table => 'cust_svc', + }, + ], + }, # name type nullability length default local @@ -6837,4 +7803,3 @@ L =cut 1; -