X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=133b6d81a739e870b676a4a044c854dd7f433459;hp=3cb1b773ab3d84891bcdf35a0314c10eff0cf874;hb=167dbdad01e2c1b62fd9be43cc05212e8c874a02;hpb=965d64dbe634d499face4cea77c8b73188282a46 diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 3cb1b773a..133b6d81a 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -200,7 +200,7 @@ sub dbdef_dist { grep { ! /^(clientapi|access_user)_session/ && ! /^h_/ && ! /^log(_context)?$/ - && ( ! /^queue(_arg)?$/ || ! $opt->{'queue-no_history'} ) + && ( ! /^queue(_arg|_depend|_stat)?$/ || ! $opt->{'queue-no_history'} ) && ! $tables_hashref_torrus->{$_} } $dbdef->tables @@ -243,6 +243,23 @@ sub dbdef_dist { ], }); + #necessary for queries that want to look at *who* made changes + $h_indices{"h_${table}_usernum"} = + DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_usernum", + 'unique' => 0, + 'columns' => [ 'history_usernum'], + }); + + # necessary because of the evil OR username for older data, be really nice if everything was just migrated to usernum and we could drop username + # This will not be helpful to mysql, but postgres smartly does a bitmap across both indexes, mysql will just use one + + $h_indices{"h_${table}_user"} = + DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_user", + 'unique' => 0, + 'columns' => [ 'history_user'], + }); } my $primary_key_col = $tableobj->column($tableobj->primary_key) @@ -466,7 +483,7 @@ sub tables_hashref { my $char_d = 80; #default maxlength for text fields #my(@date_type) = ( 'timestamp', '', '' ); - my @date_type = ( 'int', 'NULL', '' ); + my @date_type = ( 'int', 'NULL', '' ); my @perl_type = ( 'text', 'NULL', '' ); my @money_type = ( 'decimal', '', '10,2' ); my @money_typen = ( 'decimal', 'NULL', '10,2' ); @@ -539,12 +556,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' => { @@ -662,6 +680,7 @@ sub tables_hashref { 'invoice_terms', 'varchar', 'NULL', $char_d, '', '', #customer balance info at invoice generation time + #(deprecated) 'previous_balance', @money_typen, '', '', #eventually not nullable 'billing_balance', @money_typen, '', '', #eventually not nullable @@ -673,6 +692,8 @@ sub tables_hashref { 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy 'promised_date', @date_type, '', '', + + 'pending', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'invnum', 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh @@ -937,6 +958,30 @@ sub tables_hashref { ], }, + 'cust_event_fee' => { + 'columns' => [ + 'eventfeenum', 'serial', '', '', '', '', + 'eventnum', 'int', '', '', '', '', + 'billpkgnum', 'int', 'NULL', '', '', '', + 'feepart', 'int', '', '', '', '', + 'nextbill', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'eventfeenum', # I'd rather just use eventnum + 'unique' => [ [ 'billpkgnum' ], [ 'eventnum' ] ], # one-to-one link + 'index' => [ [ 'feepart' ] ], + 'foreign_keys' => [ + { columns => [ 'eventnum' ], + table => 'cust_event', + }, + { columns => [ 'billpkgnum' ], + table => 'cust_bill_pkg', + }, + { columns => [ 'feepart' ], + table => 'part_fee', + }, + ], + }, + 'cust_bill_pkg' => { 'columns' => [ 'billpkgnum', 'serial', '', '', '', '', @@ -959,6 +1004,7 @@ sub tables_hashref { 'freq', 'varchar', 'NULL', $char_d, '', '', 'quantity', 'int', 'NULL', '', '', '', 'hidden', 'char', 'NULL', 1, '', '', + 'feepart', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgnum', 'unique' => [], @@ -975,12 +1021,16 @@ sub tables_hashref { table => 'part_pkg', references => [ 'pkgpart' ], }, + { columns => [ 'feepart' ], + table => 'part_fee', + }, ], }, 'cust_bill_pkg_detail' => { 'columns' => [ 'detailnum', 'serial', '', '', '', '', + # bigserial? this table will eventually be as big as cdr... 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable 'pkgnum', 'int', 'NULL', '', '', '', # deprecated 'invnum', 'int', 'NULL', '', '', '', # deprecated @@ -989,10 +1039,10 @@ 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, '', '', + 'detail', 'varchar', '', 255, '', '', ], 'primary_key' => 'detailnum', 'unique' => [], @@ -1017,7 +1067,7 @@ sub tables_hashref { 'cust_bill_pkg_display' => { 'columns' => [ - 'billpkgdisplaynum', 'serial', '', '', '', '', + 'billpkgdisplaynum', 'serial', '', '', '', '', 'billpkgnum', 'int', '', '', '', '', 'section', 'varchar', 'NULL', $char_d, '', '', #'unitsetup', @money_typen, '', '', #override the linked real one? @@ -1036,6 +1086,35 @@ sub tables_hashref { ], }, + 'cust_bill_pkg_fee' => { + 'columns' => [ + 'billpkgfeenum', 'serial', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'base_invnum', 'int', '', '', '', '', + 'base_billpkgnum', 'int', 'NULL', '', '', '', + 'amount', @money_type, '', '', + ], + 'primary_key' => 'billpkgfeenum', + 'unique' => [], + 'index' => [ ['billpkgnum'], + ['base_invnum'], + ['base_billpkgnum'], + ], + 'foreign_keys' => [ + { columns => [ 'billpkgnum' ], + table => 'cust_bill_pkg', + }, + { columns => [ 'base_billpkgnum' ], + table => 'cust_bill_pkg', + references => [ 'billpkgnum' ], + }, + { columns => [ 'base_invnum' ], + table => 'cust_bill', + references => [ 'invnum' ], + }, + ], + }, + 'cust_bill_pkg_tax_location' => { 'columns' => [ 'billpkgtaxlocationnum', 'serial', '', '', '', '', @@ -1060,9 +1139,9 @@ sub tables_hashref { { columns => [ 'billpkgnum' ], table => 'cust_bill_pkg', }, - { columns => [ 'pkgnum' ], - table => 'cust_pkg', - }, + #{ columns => [ 'pkgnum' ], + # table => 'cust_pkg', + #}, # taxes can apply to fees { columns => [ 'locationnum' ], table => 'cust_location', }, @@ -1164,7 +1243,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, '', '', @@ -1447,6 +1526,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', '', '', '', '', @@ -1464,8 +1564,10 @@ sub tables_hashref { 'stateid_state', 'varchar', 'NULL', $char_d, '', '', 'national_id', 'varchar', 'NULL', $char_d, '', '', 'birthdate' ,@date_type, '', '', - 'spouse_birthdate' ,@date_type, '', '', - 'anniversary_date' ,@date_type, '', '', + 'spouse_last', 'varchar', 'NULL', 2*$char_d, '', '', + 'spouse_first', 'varchar', 'NULL', $char_d, '', '', + 'spouse_birthdate', @date_type, '', '', + 'anniversary_date', @date_type, '', '', 'signupdate',@date_type, '', '', 'dundate', @date_type, '', '', 'company', 'varchar', 'NULL', $char_d, '', '', @@ -1548,6 +1650,7 @@ sub tables_hashref { 'message_noemail', 'char', 'NULL', 1, '', '', 'bill_locationnum', 'int', 'NULL', '', '', '', 'ship_locationnum', 'int', 'NULL', '', '', '', + 'taxstatusnum', 'char', 'NULL', 32, '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], @@ -1558,6 +1661,8 @@ sub tables_hashref { [ 'referral_custnum' ], [ 'payby' ], [ 'paydate' ], [ 'archived' ], + [ 'ship_locationnum' ], + [ 'bill_locationnum' ], ], 'foreign_keys' => [ { columns => [ 'agentnum' ], @@ -1597,6 +1702,7 @@ sub tables_hashref { 'weight', 'int', '', '', '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', + 'cardtype', 'varchar', 'NULL', $char_d, '', '', 'paycvv', 'varchar', 'NULL', 512, '', '', 'paymask', 'varchar', 'NULL', $char_d, '', '', #'paydate', @date_type, '', '', @@ -1623,32 +1729,6 @@ sub tables_hashref { ], }, - 'cust_recon' => { # (some sort of not-well understood thing for OnPac) - 'columns' => [ - 'reconid', 'serial', '', '', '', '', - 'recondate', @date_type, '', '', - 'custnum', 'int' , '', '', '', '', - 'agentnum', 'int', '', '', '', '', - 'last', 'varchar', '', $char_d, '', '', - 'first', 'varchar', '', $char_d, '', '', - 'address1', 'varchar', '', $char_d, '', '', - 'address2', 'varchar', 'NULL', $char_d, '', '', - 'city', 'varchar', '', $char_d, '', '', - 'state', 'varchar', 'NULL', $char_d, '', '', - 'zip', 'varchar', 'NULL', 10, '', '', - 'pkg', 'varchar', 'NULL', $char_d, '', '', - 'adjourn', @date_type, '', '', - 'status', 'varchar', 'NULL', 10, '', '', - 'agent_custid', 'varchar', '', $char_d, '', '', - 'agent_pkg', 'varchar', 'NULL', $char_d, '', '', - 'agent_adjourn', @date_type, '', '', - 'comments', 'text', 'NULL', '', '', '', - ], - 'primary_key' => 'reconid', - 'unique' => [], - 'index' => [], - }, - 'contact_class' => { 'columns' => [ 'classnum', 'serial', '', '', '', '', @@ -1660,20 +1740,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, '', '', @@ -1758,10 +1887,11 @@ sub tables_hashref { 'add_date', @date_type, '', '', 'disabled', 'char', 'NULL', 1, '', '', 'custnum', 'int', 'NULL', '', '', '', + 'refnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'prospectnum', 'unique' => [], - 'index' => [ [ 'company' ], [ 'agentnum' ], [ 'disabled' ] ], + 'index' => [ ['company'], ['agentnum'], ['disabled'], ['refnum'] ], 'foreign_keys' => [ { columns => [ 'agentnum' ], table => 'agent', @@ -1769,6 +1899,9 @@ sub tables_hashref { { columns => [ 'custnum' ], table => 'cust_main', }, + { columns => [ 'refnum' ], + table => 'part_referral', + }, ], }, @@ -1786,7 +1919,7 @@ sub tables_hashref { ], 'primary_key' => 'quotationnum', 'unique' => [], - 'index' => [ [ 'prospectnum' ], ['custnum'], ], + 'index' => [ [ 'prospectnum' ], ['custnum'], ['disabled'], ], 'foreign_keys' => [ { columns => [ 'prospectnum' ], table => 'prospect_main', @@ -1811,6 +1944,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' => [], @@ -1833,6 +1968,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', @@ -1853,6 +1990,7 @@ sub tables_hashref { 'locationnum', 'serial', '', '', '', '', 'prospectnum', 'int', 'NULL', '', '', '', 'custnum', 'int', 'NULL', '', '', '', + 'locationname', 'varchar', 'NULL', $char_d, '', '', 'address1', 'varchar', '', $char_d, '', '', 'address2', 'varchar', 'NULL', $char_d, '', '', 'city', 'varchar', '', $char_d, '', '', @@ -1871,6 +2009,7 @@ sub tables_hashref { 'location_type', 'varchar', 'NULL', 20, '', '', 'location_number', 'varchar', 'NULL', 20, '', '', 'location_kind', 'char', 'NULL', 1, '', '', + 'incorporated', 'char', 'NULL', 1, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'locationnum', @@ -1905,15 +2044,36 @@ sub tables_hashref { ], }, + 'cust_main_credit_limit' => { + 'columns' => [ + 'creditlimitnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'amount', @money_typen, '', '', + #'amount_currency', 'char', 'NULL', 3, '', '', + 'credit_limit', @money_typen, '', '', + #'credit_limit_currency', 'char', 'NULL', 3, '', '', + ], + 'primary_key' => 'creditlimitnum', + 'unique' => [], + 'index' => [ ['custnum'], ], + 'foreign_keys' => [ + { columns => [ 'custnum' ], + table => 'cust_main', + }, + ], + }, + 'cust_main_note' => { 'columns' => [ - 'notenum', 'serial', '', '', '', '', - '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' => [], @@ -2080,7 +2240,7 @@ sub tables_hashref { 'geocode', 'varchar', 'NULL', $char_d, '', '',#cch provides 10 char 'data_vendor', 'varchar', 'NULL', $char_d, '', '',#auto update source 'location', 'varchar', 'NULL', $char_d, '', '',#provided by tax authority - 'taxclassnum', 'int', '', '', '', '', + 'taxclassnum', 'int', 'NULL', '', '', '', 'effective_date', @date_type, '', '', 'tax', @taxrate_type, '', '', # tax % 'excessrate', @taxrate_typen, '', '', # second tax % @@ -2124,6 +2284,7 @@ sub tables_hashref { 'city', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', 'state', 'char', 'NULL', 2, '', '', + 'country', 'char', 'NULL', 2, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'taxratelocationnum', @@ -2138,8 +2299,11 @@ sub tables_hashref { 'city', 'varchar', 'NULL', $char_d, '', '', 'postalcity', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', - 'zip', 'char', '', 5, '', '', + 'zip', 'char', 'NULL', 5, '', '', 'state', 'char', '', 2, '', '', + 'country', 'char', 'NULL', 2, '', '', # should not actually be NULL, but cch... + 'ziphi', 'char', 'NULL', 10, '', '', + 'ziplo', 'char', 'NULL', 10, '', '', 'plus4hi', 'char', 'NULL', 4, '', '', 'plus4lo', 'char', 'NULL', 4, '', '', 'default_location','char', 'NULL', 1, '', '', # Y = default for zip @@ -2148,7 +2312,7 @@ sub tables_hashref { ], 'primary_key' => 'custlocationnum', 'unique' => [], - 'index' => [ [ 'zip', 'plus4lo', 'plus4hi' ] ], + 'index' => [ [ 'zip', 'plus4lo', 'plus4hi', 'ziphi', 'ziplo' ] ], }, 'tax_class' => { @@ -2163,6 +2327,18 @@ sub tables_hashref { 'index' => [], }, + 'tax_status' => { + 'columns' => [ + 'taxstatusnum', 'serial', '', '', '', '', + 'data_vendor', 'varchar', 'NULL', $char_d, '', '', + 'taxstatus', 'varchar', '', $char_d, '', '', + 'description', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'taxstatusnum', + 'unique' => [ [ 'data_vendor', 'taxstatus' ] ], + 'index' => [], + }, + 'cust_pay_pending' => { 'columns' => [ 'paypendingnum', 'serial', '', '', '', '', @@ -2179,11 +2355,12 @@ sub tables_hashref { 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'status', 'varchar', '', $char_d, '', '', - 'session_id', 'varchar', 'NULL', $char_d, '', '', #only need 32 + 'session_id', 'varchar', 'NULL', 1024, '', '', # SHA-512-hex 'statustext', 'text', 'NULL', '', '', '', 'gatewaynum', 'int', 'NULL', '', '', '', #'cust_balance', @money_type, '', '', 'paynum', 'int', 'NULL', '', '', '', + 'void_paynum', 'int', 'NULL', '', '', '', 'jobnum', 'bigint', 'NULL', '', '', '', 'invnum', 'int', 'NULL', '', '', '', 'manual', 'char', 'NULL', 1, '', '', @@ -2192,7 +2369,9 @@ sub tables_hashref { ], 'primary_key' => 'paypendingnum', 'unique' => [ [ 'payunique' ] ], - 'index' => [ [ 'custnum' ], [ 'status' ], ], + 'index' => [ [ 'custnum' ], [ 'status' ], + ['paynum'], ['void_paynum'], ['jobnum'], ['invnum'], + ], 'foreign_keys' => [ { columns => [ 'custnum' ], table => 'cust_main', @@ -2206,6 +2385,10 @@ sub tables_hashref { { columns => [ 'paynum' ], table => 'cust_pay', }, + { columns => [ 'void_paynum' ], + table => 'cust_pay_void', + references => [ 'paynum' ], + }, { columns => [ 'jobnum' ], table => 'queue', }, @@ -2515,7 +2698,7 @@ sub tables_hashref { 'manual_flag', 'char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'quantity', 'int', 'NULL', '', '', '', - 'agent_pkgid', 'int', 'NULL', '', '', '', + 'agent_pkgid', 'varchar', 'NULL', $char_d, '', '', 'waive_setup', 'char', 'NULL', 1, '', '', 'recur_show_zero', 'char', 'NULL', 1, '', '', 'setup_show_zero', 'char', 'NULL', 1, '', '', @@ -2526,9 +2709,17 @@ sub tables_hashref { 'index' => [ ['custnum'], ['pkgpart'], ['pkgbatch'], ['locationnum'], ['usernum'], ['agent_pkgid'], ['order_date'], [ 'start_date' ], ['setup'], ['bill'], - ['last_bill'], ['susp'], ['adjourn'], ['cancel'], - ['expire'], ['contract_end'], ['change_date'], + ['last_bill'], ['susp'], ['adjourn'], ['resume'], + ['cancel'], ['expire'], ['contract_end'], + ['change_date'], ['no_auto'], + #['contactnum'], + ['salesnum'], + #['uncancel_pkgnum'], + #['change_pkgnum'], ['change_locationnum'], + #['change_custnum'], + ['main_pkgnum'], + #['pkglinknum'], ['change_to_pkgnum'], ], 'foreign_keys' => [ { columns => [ 'custnum' ], @@ -2675,7 +2866,7 @@ sub tables_hashref { 'columns' => [ 'pkgusagenum', 'serial', '', '', '', '', 'pkgnum', 'int', '', '', '', '', - 'minutes', 'int', '', '', '', '', + 'minutes', 'double precision', '', '', '', '', 'pkgusagepart', 'int', '', '', '', '', ], 'primary_key' => 'pkgusagenum', @@ -2696,7 +2887,7 @@ sub tables_hashref { 'cdrusagenum', 'bigserial', '', '', '', '', 'acctid', 'bigint', '', '', '', '', 'pkgusagenum', 'int', '', '', '', '', - 'minutes', 'int', '', '', '', '', + 'minutes', 'double precision', '', '', '', '', ], 'primary_key' => 'cdrusagenum', 'unique' => [], @@ -2797,7 +2988,8 @@ sub tables_hashref { 'currency', 'char', 'NULL', 3, '', '', 'otaker', 'varchar', 'NULL', 32, '', '', 'usernum', 'int', 'NULL', '', '', '', - 'reason', 'varchar', '', $char_d, '', '', + 'reason', 'varchar', 'NULL', $char_d, '', '', + 'reasonnum', 'int', 'NULL', '', '', '', 'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should # be index into payby # table eventually @@ -2821,6 +3013,9 @@ sub tables_hashref { { columns => [ 'usernum' ], table => 'access_user', }, + { columns => [ 'reasonnum' ], + table => 'reason', + }, { columns => [ 'gatewaynum' ], table => 'payment_gateway', }, @@ -2932,6 +3127,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, '', '', @@ -2958,11 +3154,13 @@ sub tables_hashref { 'successor', 'int', 'NULL', '', '', '', 'family_pkgpart','int', 'NULL', '', '', '', 'delay_start', 'int', 'NULL', '', '', '', + 'start_on_hold', 'char', 'NULL', 1, '', '', + 'agent_pkgpartid', 'varchar', 'NULL', 20, '', '', ], 'primary_key' => 'pkgpart', 'unique' => [], 'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'classnum' ], - [ 'agentnum' ], ['no_auto'], + [ 'agentnum' ], ['no_auto'], ['agent_pkgpartid'], ], 'foreign_keys' => [ { columns => [ 'classnum' ], @@ -3077,6 +3275,83 @@ sub tables_hashref { ], }, + 'part_fee' => { + 'columns' => [ + 'feepart', 'serial', '', '', '', '', + 'itemdesc', 'varchar', '', $char_d, '', '', + 'comment', 'varchar', 'NULL', 2*$char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'taxclass', 'varchar', 'NULL', $char_d, '', '', + 'taxproductnum', 'int', 'NULL', '', '', '', + 'pay_weight', 'real', 'NULL', '', '', '', + 'credit_weight', 'real', 'NULL', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'amount', @money_type, '', '', + 'percent', 'decimal', '', '7,4', '', '', + 'basis', 'varchar', '', 16, '', '', + 'minimum', @money_typen, '', '', + 'maximum', @money_typen, '', '', + 'limit_credit', 'char', 'NULL', 1, '', '', + 'setuprecur', 'char', '', 5, '', '', + 'taxable', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'feepart', + 'unique' => [], + 'index' => [ [ 'disabled' ], [ 'classnum' ], [ 'agentnum' ] + ], + 'foreign_keys' => [ + { columns => [ 'classnum' ], + table => 'pkg_class', + }, + { columns => [ 'taxproductnum' ], + table => 'part_pkg_taxproduct', + }, + { columns => [ 'agentnum' ], + table => 'agent', + }, + ], + }, + + 'part_fee_msgcat' => { + 'columns' => [ + 'feepartmsgnum', 'serial', '', '', '', '', + 'feepart', 'int', '', '', '', '', + 'locale', 'varchar', '', 16, '', '', + 'itemdesc', 'varchar', '', $char_d, '', '', #longer/no limit? + 'comment', 'varchar', 'NULL', 2*$char_d, '', '', #longer/no limit? + ], + 'primary_key' => 'feepartmsgnum', + 'unique' => [ [ 'feepart', 'locale' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'feepart' ], + table => 'part_fee', + }, + ], + }, + + 'part_fee_usage' => { + 'columns' => [ + 'feepartusagenum','serial', '', '', '', '', + 'feepart', 'int', '', '', '', '', + 'classnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'percent', 'decimal', '', '7,4', '', '', + ], + 'primary_key' => 'feepartusagenum', + 'unique' => [ [ 'feepart', 'classnum' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'feepart' ], + table => 'part_fee', + }, + { columns => [ 'classnum' ], + table => 'usage_class', + }, + ], + }, + 'part_pkg_link' => { 'columns' => [ 'pkglinknum', 'serial', '', '', '', '', @@ -3138,6 +3413,7 @@ sub tables_hashref { 'data_vendor', 'varchar', 'NULL', $char_d, '', '', 'taxproduct', 'varchar', '', $char_d, '', '', 'description', 'varchar', '', 3*$char_d, '', '', + 'note', 'text', 'NULL', '', '', '', ], 'primary_key' => 'taxproductnum', 'unique' => [ [ 'data_vendor', 'taxproduct' ] ], @@ -3519,20 +3795,27 @@ sub tables_hashref { 'suffix', 'varchar', 'NULL', $char_d, '', '', 'catchall', 'int', 'NULL', '', '', '', 'parent_svcnum', 'int', 'NULL', '', '', '', + 'quota', 'varchar', 'NULL', $char_d, '', '', + + #registration info 'registrarnum', 'int', 'NULL', '', '', '', 'registrarkey', 'varchar', 'NULL', 512, '', '', 'setup_date', @date_type, '', '', 'renewal_interval', 'int', 'NULL', '', '', '', 'expiration_date', @date_type, '', '', + + #some weird shit australia-specific shit? yuck.. seems totally unused 'au_registrant_name', 'varchar', 'NULL', $char_d, '', '', 'au_eligibility_type', 'varchar', 'NULL', $char_d, '', '', + #communigate pro fields (quota = MaxAccountSize) 'max_accounts', 'int', 'NULL', '', '', '', 'trailer', 'text', 'NULL', '', '', '', 'cgp_aliases', 'varchar', 'NULL', 255, '', '', 'cgp_accessmodes','varchar','NULL', 255, '', '', #DomainAccessModes 'cgp_certificatetype','varchar','NULL', $char_d, '', '', - #settings + + #(account default) settings 'acct_def_password_selfchange', 'char', 'NULL', 1, '', '', 'acct_def_password_recover', 'char', 'NULL', 1, 'Y', '', 'acct_def_cgp_accessmodes', 'varchar', 'NULL', 255, '', '', @@ -3545,7 +3828,8 @@ sub tables_hashref { 'acct_def_cgp_mailtoall', 'char', 'NULL', 1, '', '', 'acct_def_cgp_addmailtrailer', 'char', 'NULL', 1, '', '', 'acct_def_cgp_archiveafter', 'int', 'NULL', '', '', '', - #preferences + + #(account default) preferences 'acct_def_cgp_deletemode', 'varchar', 'NULL', $char_d, '', '', 'acct_def_cgp_emptytrash', 'varchar', 'NULL', $char_d, '', '', 'acct_def_cgp_language', 'varchar', 'NULL', $char_d, '', '', @@ -3892,7 +4176,7 @@ sub tables_hashref { ], 'primary_key' => 'prepaynum', 'unique' => [ ['identifier'] ], - 'index' => [], + 'index' => [ ['agentnum'] ], 'foreign_keys' => [ { columns => [ 'agentnum' ], table => 'agent', @@ -3978,9 +4262,9 @@ sub tables_hashref { [ 'job' ], [ 'svcnum' ], [ 'custnum' ], [ 'status' ], ], 'foreign_keys' => [ - { columns => [ 'svcnum' ], - table => 'cust_svc', - }, +# { columns => [ 'svcnum' ], +# table => 'cust_svc', +# }, { columns => [ 'custnum' ], table => 'cust_main', }, @@ -4026,11 +4310,27 @@ sub tables_hashref { ], }, + 'queue_stat' => { + 'columns' => [ + 'statnum', 'bigserial', '', '', '', '', + 'jobnum', 'bigint', '', '', '', '', + 'job', 'varchar', '', 512, '', '', + 'custnum', 'int', 'NULL', '', '', '', + 'insert_date', @date_type, '', '', + 'start_date', @date_type, '', '', + 'end_date', @date_type, '', '', + ], + 'primary_key' => 'statnum', + 'unique' => [], #[ ['jobnum'] ], + 'index' => [], + }, + 'export_svc' => { 'columns' => [ 'exportsvcnum' => 'serial', '', '', '', '', 'exportnum' => 'int', '', '', '', '', 'svcpart' => 'int', '', '', '', '', + 'role' => 'varchar', 'NULL', 16, '', '', ], 'primary_key' => 'exportsvcnum', 'unique' => [ [ 'exportnum', 'svcpart' ] ], @@ -4315,9 +4615,9 @@ sub tables_hashref { 'unique' => [ [ 'blocknum', 'routernum' ] ], 'index' => [], 'foreign_keys' => [ - { columns => [ 'routernum' ], - table => 'router', - }, + #{ columns => [ 'routernum' ], + # table => 'router', + #}, { columns => [ 'agentnum' ], table => 'agent', }, @@ -4360,6 +4660,7 @@ sub tables_hashref { 'rssi', 'int', 'NULL', '', '', '', 'suid', 'int', 'NULL', '', '', '', 'shared_svcnum', 'int', 'NULL', '', '', '', + 'serviceid', 'varchar', 'NULL', 64, '', '',#srvexport/reportfields ], 'primary_key' => 'svcnum', 'unique' => [ [ 'ip_addr' ], [ 'mac_addr' ] ], @@ -4405,16 +4706,16 @@ sub tables_hashref { 'tower_sector' => { 'columns' => [ - 'sectornum', 'serial', '', '', '', '', - 'towernum', 'int', '', '', '', '', - 'sectorname', 'varchar', '', $char_d, '', '', - 'ip_addr', 'varchar', 'NULL', 15, '', '', - 'height', 'decimal', 'NULL', '', '', '', - 'freq_mhz', 'int', 'NULL', '', '', '', - 'direction', 'int', 'NULL', '', '', '', - 'width', 'int', 'NULL', '', '', '', + 'sectornum', 'serial', '', '', '', '', + 'towernum', 'int', '', '', '', '', + 'sectorname', 'varchar', '', $char_d, '', '', + 'ip_addr', 'varchar', 'NULL', 15, '', '', + 'height', 'decimal', 'NULL', '', '', '', + 'freq_mhz', 'int', 'NULL', '', '', '', + 'direction', 'int', 'NULL', '', '', '', + 'width', 'int', 'NULL', '', '', '', #downtilt etc? rfpath has profile files for devices/antennas you upload? - 'range', 'decimal', 'NULL', '', '', '', #? + 'sector_range', 'decimal', 'NULL', '', '', '', #? ], 'primary_key' => 'sectornum', 'unique' => [ [ 'towernum', 'sectorname' ], [ 'ip_addr' ], ], @@ -4570,7 +4871,7 @@ sub tables_hashref { 'columns' => [ 'pkgusagepart', 'serial', '', '', '', '', 'pkgpart', 'int', '', '', '', '', - 'minutes', 'int', '', '', '', '', + 'minutes', 'double precision', '', '', '', '', 'priority', 'int', 'NULL', '', '', '', 'shared', 'char', 'NULL', 1, '', '', 'rollover', 'char', 'NULL', 1, '', '', @@ -4605,14 +4906,33 @@ sub tables_hashref { ], }, + 'part_pkg_fcc_option' => { + 'columns' => [ + 'num', 'serial', '', '', '', '', + 'fccoptionname', 'varchar', '', $char_d, '', '', + 'pkgpart', 'int', '', '', '', '', + 'optionvalue', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'num', + 'unique' => [ [ 'fccoptionname', 'pkgpart' ] ], + 'index' => [], + }, + 'rate' => { 'columns' => [ - 'ratenum', 'serial', '', '', '', '', - 'ratename', 'varchar', '', $char_d, '', '', + 'ratenum', 'serial', '', '', '', '', + 'ratename', 'varchar', '',$char_d, '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'default_detailnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'ratenum', 'unique' => [], 'index' => [], + 'foreign_keys' => [ + { columns => [ 'agentnum' ], + table => 'agent', + }, + ], }, 'rate_detail' => { @@ -4620,16 +4940,20 @@ sub tables_hashref { 'ratedetailnum', 'serial', '', '', '', '', 'ratenum', 'int', '', '', '', '', 'orig_regionnum', 'int', 'NULL', '', '', '', - 'dest_regionnum', 'int', '', '', '', '', + 'dest_regionnum', 'int', 'NULL', '', '', '', 'min_included', 'int', '', '', '', '', 'conn_charge', 'decimal', '', '10,4', '0.0000', '', + 'conn_cost', 'decimal', '', '10,4', '0.0000', '', 'conn_sec', 'int', '', '', '0', '', 'min_charge', 'decimal', '', '10,5', '', '', + 'min_cost', 'decimal', '', '10,5','0.00000', '', 'sec_granularity', 'int', '', '', '', '', 'ratetimenum', 'int', 'NULL', '', '', '', 'classnum', 'int', 'NULL', '', '', '', 'cdrtypenum', 'int', 'NULL', '', '', '', - 'region_group', 'char', 'NULL', 1, '', '', + 'region_group', 'char', 'NULL', 1, '', '', + 'upstream_mult_charge', 'decimal', '', '10,4', '0.0000', '', + 'upstream_mult_cost', 'decimal', '', '10,4', '0.0000', '', ], 'primary_key' => 'ratedetailnum', 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ], @@ -4909,11 +5233,12 @@ sub tables_hashref { 'pkg_category' => { 'columns' => [ - 'categorynum', 'serial', '', '', '', '', - 'categoryname', 'varchar', '', $char_d, '', '', - 'weight', 'int', 'NULL', '', '', '', - 'condense', 'char', 'NULL', 1, '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'categorynum', 'serial', '', '', '', '', + 'categoryname', 'varchar', '', $char_d, '', '', + 'weight', 'int', 'NULL', '', '', '', + 'ticketing_queueid', 'int', 'NULL', '', '', '', + 'condense', 'char', 'NULL', 1, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'categorynum', 'unique' => [], @@ -4958,8 +5283,10 @@ sub tables_hashref { 'lastapp', 'varchar', '', $char_d, \"''", '', 'lastdata', 'varchar', '', 255, \"''", '', - #currently only opensips + #currently only opensips & voipswitch 'src_ip_addr', 'varchar', 'NULL', 15, '', '', + + #currently only opensips 'dst_ip_addr', 'varchar', 'NULL', 15, '', '', #currently only u4: @@ -4983,7 +5310,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, \"''", '', @@ -5028,6 +5355,10 @@ sub tables_hashref { 'charged_party_imsi', 'varchar', 'NULL', $char_d, '', '', 'upstream_price', 'decimal', 'NULL', '10,5', '', '', + + #currently only voipswitch + #'upstream_rate', 'decimal', 'NULL', '10,5', '', '', + 'upstream_src_regionname', 'varchar', 'NULL', $char_d, '', '', 'upstream_dst_regionname', 'varchar', 'NULL', $char_d, '', '', @@ -5065,6 +5396,9 @@ sub tables_hashref { #new 'cdrbatchnum', 'int', 'NULL', '', '', '', + # FK to cust_bill_pkg_detail; having a value here absolutely means + # that the CDR appears on an invoice + 'detailnum', 'bigint', 'NULL', '', '', '', ], 'primary_key' => 'acctid', 'unique' => [], @@ -5333,6 +5667,44 @@ sub tables_hashref { 'index' => [], }, + 'sched_item' => { + 'columns' => [ + 'itemnum', 'serial', '', '', '', '', + 'usernum', 'int', 'NULL', '', '', '', + #'itemname', 'varchar', $char_d, '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'itemnum', + 'unique' => [ [ 'usernum' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'usernum' ], + table => 'access_user', + }, + ], + }, + + #'sched_item_class' + + 'sched_avail' => { + 'columns' => [ + 'availnum', 'serial', '', '', '', '', + 'itemnum', 'int', '', '', '', '', + 'wday', 'int', '', '', '', '', + 'stime', 'int', '', '', '', '', + 'etime', 'int', '', '', '', '', + 'override_date', @date_type, '', '', + ], + 'primary_key' => 'availnum', + 'unique' => [], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'itemnum' ], + table => 'sched_item', + }, + ], + }, + 'svc_phone' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', @@ -5358,6 +5730,10 @@ sub tables_hashref { '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'] ], @@ -5383,6 +5759,10 @@ sub tables_hashref { table => 'cdr_carrier', references => [ 'carrierid' ], }, + { columns => [ 'circuit_svcnum' ], + table => 'svc_circuit', + references => [ 'svcnum' ], + }, ], }, @@ -5586,6 +5966,7 @@ sub tables_hashref { 'disabled', 'char', 'NULL', 1, '', '', 'unsuspend_pkgpart', 'int', 'NULL', '', '', '', 'unsuspend_hold','char', 'NULL', 1, '', '', + 'unused_credit', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'reasonnum', 'unique' => [], @@ -5643,13 +6024,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', @@ -5657,6 +6040,64 @@ sub tables_hashref { ], }, + 'pbx_extension' => { + 'columns' => [ + 'extensionnum', 'serial', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'extension', 'varchar', '', $char_d, '', '', + 'pin', 'varchar', 'NULL', $char_d, '', '', + 'sip_password', 'varchar', 'NULL', $char_d, '', '', + 'phone_name', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'extensionnum', + 'unique' => [ [ 'svcnum', 'extension' ] ], + 'index' => [ [ 'svcnum' ] ], + 'foreign_keys' => [ + { columns => [ 'svcnum' ], + table => 'svc_pbx', + }, + ], + }, + + 'pbx_device' => { + 'columns' => [ + 'devicenum', 'serial', '', '', '', '', + 'devicepart', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'mac_addr', 'varchar', 'NULL', 12, '', '', + ], + 'primary_key' => 'devicenum', + 'unique' => [ [ 'mac_addr' ], ], + 'index' => [ [ 'devicepart' ], [ 'svcnum' ], ], + 'foreign_keys' => [ + { columns => [ 'devicepart' ], + table => 'part_device', + }, + { columns => [ 'svcnum' ], + table => 'svc_pbx', + }, + ], + }, + + 'extension_device' => { + 'columns' => [ + 'extensiondevicenum', 'serial', '', '', '', '', + 'extensionnum', 'int', '', '', '', '', + 'devicenum', 'int', '', '', '', '', + ], + 'primary_key' => 'extensiondevicenum', + 'unique' => [ [ 'extensionnum', 'devicenum' ] ], + 'index' => [],#both? which way do we need to query? + 'foreign_keys' => [ + { columns => [ 'extensionnum' ], + table => 'pbx_extension', + }, + { columns => [ 'devicenum' ], + table => 'pbx_device', + }, + ], + }, + 'svc_mailinglist' => { #svc_group? 'columns' => [ 'svcnum', 'int', '', '', '', '', @@ -5815,7 +6256,7 @@ sub tables_hashref { 'cust_msg' => { 'columns' => [ 'custmsgnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', 'msgnum', 'int', 'NULL', '', '', '', '_date', @date_type, '', '', 'env_from', 'varchar', 'NULL', 255, '', '', @@ -5824,6 +6265,7 @@ sub tables_hashref { 'body', 'blob', 'NULL', '', '', '', 'error', 'varchar', 'NULL', 255, '', '', 'status', 'varchar', '',$char_d, '', '', + 'msgtype', 'varchar', 'NULL', 16, '', '', ], 'primary_key' => 'custmsgnum', 'unique' => [ ], @@ -5955,7 +6397,7 @@ sub tables_hashref { 'columns' => [ 'logcontextnum', 'serial', '', '', '', '', 'lognum', 'int', '', '', '', '', - 'context', 'varchar', '', 32, '', '', + 'context', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'logcontextnum', 'unique' => [ [ 'lognum', 'context' ] ], @@ -5969,13 +6411,17 @@ sub tables_hashref { 'svc_alarm' => { 'columns' => [ - 'svcnum', 'int', '', '', '', '', - 'alarmsystemnum', 'int', '', '', '', '', - 'alarmtypenum', 'int', '', '', '', '', - 'alarmstationnum', 'int', '', '', '', '', - 'acctnum', 'varchar', '', $char_d, '', '', - '_password', 'varchar', '', $char_d, '', '', - 'location', 'varchar', 'NULL', $char_d, '', '', +# name type null length default local + 'svcnum', 'int', '', '', '', '', + 'alarmsystemnum', 'int', '', '', '', '', + 'alarmtypenum', 'int', '', '', '', '', + 'alarmstationnum', 'int', '', '', '', '', + 'acctnum', 'varchar', '', $char_d, '', '', + '_password', 'varchar', '', $char_d, '', '', + 'location', 'varchar', 'NULL', $char_d, '', '', + 'cs_receiver', 'int', 'NULL', '', '', '', + 'cs_phonenum', 'varchar', 'NULL', $char_d, '', '', + 'serialnum', 'varchar', 'NULL', $char_d, '', '', #installer (rep) ], 'primary_key' => 'svcnum', @@ -6059,7 +6505,7 @@ sub tables_hashref { 'mac_addr', 'varchar', 'NULL', 12, '', '', ], 'primary_key' => 'svcnum', - 'unique' => [], + 'unique' => [ ['serialnum'] , ['mac_addr'] ], 'index' => [], 'foreign_keys' => [ { columns => [ 'svcnum' ], @@ -6168,6 +6614,75 @@ 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, '', '', + ], + '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', + }, + ], + }, + 'vend_main' => { 'columns' => [ 'vendnum', 'serial', '', '', '', '', @@ -6200,7 +6715,8 @@ sub tables_hashref { 'columns' => [ 'vendbillnum', 'serial', '', '', '', '', 'vendnum', 'int', '', '', '', '', - '_date', @date_type, '', '', + #'_date', @date_type, '', '', + '_date', 'int', '', '', '', '', 'charged', @money_type, '', '', ], 'primary_key' => 'vendbillnum', @@ -6217,7 +6733,8 @@ sub tables_hashref { 'columns' => [ 'vendpaynum', 'serial', '', '', '', '', 'vendnum', 'int', '', '', '', '', - '_date', @date_type, '', '', + #'_date', @date_type, '', '', + '_date', 'int', '', '', '', '', 'paid', @money_type, '', '', ], 'primary_key' => 'vendpaynum', @@ -6311,21 +6828,9 @@ sub tables_hashref { 'latexnotes', 'text', 'NULL', '', '', '', 'latexfooter', 'text', 'NULL', '', '', '', 'latexsummary', 'text', 'NULL', '', '', '', - 'latexcoupon', 'text', 'NULL', '', '', '', 'latexsmallfooter', 'text', 'NULL', '', '', '', 'latexreturnaddress', 'text', 'NULL', '', '', '', - 'latextopmargin', 'varchar', 'NULL', 16, '', '', - 'latexheadsep', 'varchar', 'NULL', 16, '', '', - 'latexaddresssep', 'varchar', 'NULL', 16, '', '', - 'latextextheight', 'varchar', 'NULL', 16, '', '', - 'latexextracouponspace','varchar', 'NULL', 16, '', '', - 'latexcouponfootsep', 'varchar', 'NULL', 16, '', '', - 'latexcouponamountenclosedsep', 'varchar', 'NULL', 16, '', '', - 'latexcoupontoaddresssep', 'varchar', 'NULL', 16, '', '', - 'latexverticalreturnaddress', 'char', 'NULL', 1, '', '', - 'latexcouponaddcompanytoaddress', 'char', 'NULL', 1, '', '', - 'logo_png', 'blob', 'NULL', '', '', '', - 'logo_eps', 'blob', 'NULL', '', '', '', + 'with_latexcoupon', 'char', 'NULL', '1', '', '', 'lpr', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'confnum', @@ -6338,6 +6843,130 @@ sub tables_hashref { ], }, + 'export_batch' => { + 'columns' => [ + 'batchnum', 'serial', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + '_date', 'int', '', '', '', '', + 'status', 'varchar', 'NULL', 32, '', '', + 'statustext', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'batchnum', + 'unique' => [], + 'index' => [ [ 'exportnum' ], [ 'status' ] ], + 'foreign_keys' => [ + { columns => [ 'exportnum' ], + table => 'part_export', + references => [ 'exportnum' ] + }, + ], + }, + + 'export_batch_item' => { + 'columns' => [ + 'itemnum', 'serial', '', '', '', '', + 'batchnum', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'action', 'varchar', '', 32, '', '', + 'data', 'text', 'NULL', '', '', '', + 'frozen', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'itemnum', + 'unique' => [], + 'index' => [ [ 'batchnum' ], [ 'svcnum' ] ], + 'foreign_keys' => [ + { columns => [ 'batchnum' ], + table => 'export_batch', + references => [ 'batchnum' ] + }, + ], + }, + + # lookup table for states, similar to msa and lata + 'state' => { + 'columns' => [ + 'statenum', 'int', '', '', '', '', + 'country', 'char', '', 2, '', '', + 'state', 'char', '', $char_d, '', '', + 'fips', 'char', '', 3, '', '', + ], + 'primary_key' => 'statenum', + 'unique' => [ [ 'country', 'state' ], ], + 'index' => [], + }, + + # eventually link to tower/sector? + 'deploy_zone' => { + 'columns' => [ + 'zonenum', 'serial', '', '', '', '', + 'description', 'char', 'NULL', $char_d, '', '', + 'agentnum', 'int', '', '', '', '', + 'dbaname', 'char', 'NULL', $char_d, '', '', + 'zonetype', 'char', '', 1, '', '', + 'technology', 'int', '', '', '', '', + 'spectrum', 'int', 'NULL', '', '', '', + 'adv_speed_up', 'decimal', '', '10,3', '0', '', + 'adv_speed_down', 'decimal', '', '10,3', '0', '', + 'cir_speed_up', 'decimal', '', '10,3', '0', '', + 'cir_speed_down', 'decimal', '', '10,3', '0', '', + 'is_broadband', 'char', 'NULL', 1, '', '', + 'is_voice', 'char', 'NULL', 1, '', '', + 'is_consumer', 'char', 'NULL', 1, '', '', + 'is_business', 'char', 'NULL', 1, '', '', + 'active_date', @date_type, '', '', + 'expire_date', @date_type, '', '', + ], + 'primary_key' => 'zonenum', + 'unique' => [], + 'index' => [ [ 'agentnum' ] ], + 'foreign_keys' => [ + { columns => [ 'agentnum' ], + table => 'agent', + references => [ 'agentnum' ], + }, + ], + }, + + 'deploy_zone_block' => { + 'columns' => [ + 'blocknum', 'serial', '', '', '', '', + 'zonenum', 'int', '', '', '', '', + 'censusblock', 'char', '', 15, '', '', + 'censusyear', 'char', '', 4, '', '', + ], + 'primary_key' => 'blocknum', + 'unique' => [], + 'index' => [ [ 'zonenum' ] ], + 'foreign_keys' => [ + { columns => [ 'zonenum' ], + table => 'deploy_zone', + references => [ 'zonenum' ], + }, + ], + }, + + 'deploy_zone_vertex' => { + 'columns' => [ + 'vertexnum', 'serial', '', '', '', '', + 'zonenum', 'int', '', '', '', '', + 'latitude', 'decimal', '', '10,7', '', '', + 'longitude', 'decimal', '', '10,7', '', '', + ], + 'primary_key' => 'vertexnum', + 'unique' => [ ], + 'index' => [ ], + 'foreign_keys' => [ + { columns => [ 'zonenum' ], + table => 'deploy_zone', + references => [ 'zonenum' ], + }, + ], + }, + + + + + # name type nullability length default local #'new_table' => {