X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=70f32502b2b981b9b5ef9a7e9aca80fa6abe051c;hp=719f3a87b04139d1f174769812237b4e4754d5e6;hb=947c1f964f1304242f8a6ffabacccf040f1d505e;hpb=4e77f6927631e226e13da84082be66867b71330f diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 719f3a87b..70f32502b 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -320,6 +320,8 @@ sub tables_hashref { my @perl_type = ( 'text', 'NULL', '' ); my @money_type = ( 'decimal', '', '10,2' ); my @money_typen = ( 'decimal', 'NULL', '10,2' ); + my @taxrate_type = ( 'decimal', '', '14,8' ); # requires pg 8 for + my @taxrate_typen = ( 'decimal', 'NULL', '14,8' ); # fs-upgrade to work my $username_len = 32; #usernamemax config file @@ -370,18 +372,48 @@ sub tables_hashref { 'index' => [ ['typenum'] ], }, + 'cust_attachment' => { + 'columns' => [ + 'attachnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'otaker', 'varchar', '', 32, '', '', + 'filename', 'varchar', '', 32, '', '', + 'mime_type', 'varchar', '', 32, '', '', + 'body', 'blob', 'NULL', '', '', '', + 'disabled', @date_type, '', '', + ], + 'primary_key' => 'attachnum', + 'unique' => [], + 'index' => [ ['custnum'] ], + }, + 'cust_bill' => { 'columns' => [ - 'invnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', - '_date', @date_type, '', '', - 'charged', @money_type, '', '', - 'printed', 'int', '', '', '', '', - 'closed', 'char', 'NULL', 1, '', '', + 'invnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'charged', @money_type, '', '', + 'previous_balance', @money_typen, '', '', #eventually not nullable + 'billing_balance', @money_typen, '', '', #eventually not nullable + 'printed', 'int', '', '', '', '', + 'closed', 'char', 'NULL', 1, '', '', + 'statementnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'invnum', 'unique' => [], - 'index' => [ ['custnum'], ['_date'] ], + 'index' => [ ['custnum'], ['_date'], ['statementnum'], ], + }, + + 'cust_statement' => { + 'columns' => [ + 'statementnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + ], + 'primary_key' => 'statementnum', + 'unique' => [], + 'index' => [ ['custnum'], ['_date'], ], }, 'cust_bill_event' => { @@ -396,7 +428,9 @@ sub tables_hashref { 'primary_key' => 'eventnum', #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ], 'unique' => [], - 'index' => [ ['invnum'], ['status'] ], + 'index' => [ ['invnum'], ['status'], ['eventpart'], + ['statustext'], ['_date'], + ], }, 'part_bill_event' => { @@ -493,28 +527,32 @@ sub tables_hashref { 'primary_key' => 'eventnum', #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ], 'unique' => [], - 'index' => [ ['eventpart'], ['tablenum'], ['status'] ], + 'index' => [ ['eventpart'], ['tablenum'], ['status'], + ['statustext'], ['_date'], + ], }, 'cust_bill_pkg' => { 'columns' => [ - 'billpkgnum', 'serial', '', '', '', '', - 'pkgnum', 'int', '', '', '', '', - 'pkgpart_override', 'int', 'NULL', '', '', '', - 'invnum', 'int', '', '', '', '', - 'setup', @money_type, '', '', - 'recur', @money_type, '', '', - 'sdate', @date_type, '', '', - 'edate', @date_type, '', '', - 'itemdesc', 'varchar', 'NULL', $char_d, '', '', - 'section', 'varchar', 'NULL', $char_d, '', '', - 'quantity', 'int', 'NULL', '', '', '', - 'unitsetup', @money_typen, '', '', - 'unitrecur', @money_typen, '', '', + 'billpkgnum', 'serial', '', '', '', '', + 'invnum', 'int', '', '', '', '', + 'pkgnum', 'int', '', '', '', '', + 'pkgpart_override', 'int', 'NULL', '', '', '', + 'setup', @money_type, '', '', + 'recur', @money_type, '', '', + 'sdate', @date_type, '', '', + 'edate', @date_type, '', '', + 'itemdesc', 'varchar', 'NULL', $char_d, '', '', + 'itemcomment', 'varchar', 'NULL', $char_d, '', '', + 'section', 'varchar', 'NULL', $char_d, '', '', + 'quantity', 'int', 'NULL', '', '', '', + 'unitsetup', @money_typen, '', '', + 'unitrecur', @money_typen, '', '', + 'hidden', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'billpkgnum', 'unique' => [], - 'index' => [ ['invnum'], [ 'pkgnum' ] ], + 'index' => [ ['invnum'], [ 'pkgnum' ], [ 'itemdesc' ], ], }, 'cust_bill_pkg_detail' => { @@ -523,10 +561,11 @@ sub tables_hashref { 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable 'pkgnum', 'int', 'NULL', '', '', '', # deprecated 'invnum', 'int', 'NULL', '', '', '', # deprecated - 'amount', @money_typen, '', '', + 'amount', 'decimal', 'NULL', '10,4', '', '', 'format', 'char', 'NULL', 1, '', '', - 'classnum', 'char', 'NULL', 1, '', '', - 'detail', 'varchar', '', $char_d, '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'phonenum', 'varchar', 'NULL', 15, '', '', + 'detail', 'varchar', '', 255, '', '', ], 'primary_key' => 'detailnum', 'unique' => [], @@ -549,6 +588,36 @@ sub tables_hashref { 'index' => [ ['billpkgnum'], ], }, + 'cust_bill_pkg_tax_location' => { + 'columns' => [ + 'billpkgtaxlocationnum', 'serial', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'taxnum', 'int', '', '', '', '', + 'taxtype', 'varchar', '', $char_d, '', '', + 'pkgnum', 'int', '', '', '', '', + 'locationnum', 'int', '', '', '', '', #redundant? + 'amount', @money_type, '', '', + ], + 'primary_key' => 'billpkgtaxlocationnum', + 'unique' => [], + 'index' => [ [ 'billpkgnum' ], [ 'taxnum' ], [ 'pkgnum' ], [ 'locationnum' ] ], + }, + + 'cust_bill_pkg_tax_rate_location' => { + 'columns' => [ + 'billpkgtaxratelocationnum', 'serial', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'taxnum', 'int', '', '', '', '', + 'taxtype', 'varchar', '', $char_d, '', '', + 'locationtaxid', 'varchar', 'NULL', $char_d, '', '', + 'taxratelocationnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + ], + 'primary_key' => 'billpkgtaxratelocationnum', + 'unique' => [], + 'index' => [ [ 'billpkgnum' ], [ 'taxnum' ], [ 'taxratelocationnum' ] ], + }, + 'cust_credit' => { 'columns' => [ 'crednum', 'serial', '', '', '', '', @@ -560,6 +629,7 @@ sub tables_hashref { 'reasonnum', 'int', 'NULL', '', '', '', 'addlinfo', 'text', 'NULL', '', '', '', 'closed', 'char', 'NULL', 1, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances ], 'primary_key' => 'crednum', 'unique' => [], @@ -573,6 +643,7 @@ sub tables_hashref { 'invnum', 'int', '', '', '', '', '_date', @date_type, '', '', 'amount', @money_type, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances ], 'primary_key' => 'creditbillnum', 'unique' => [], @@ -649,6 +720,7 @@ sub tables_hashref { 'paytype', 'varchar', 'NULL', $char_d, '', '', 'payip', 'varchar', 'NULL', 15, '', '', 'geocode', 'varchar', 'NULL', 20, '', '', + 'censustract', 'varchar', 'NULL', 20, '', '', # 7 to save space? 'tax', 'char', 'NULL', 1, '', '', 'otaker', 'varchar', '', 32, '', '', 'refnum', 'int', '', '', '', '', @@ -656,21 +728,80 @@ sub tables_hashref { 'comments', 'text', 'NULL', '', '', '', 'spool_cdr','char', 'NULL', 1, '', '', 'squelch_cdr','char', 'NULL', 1, '', '', + 'cdr_termination_percentage', 'decimal', 'NULL', '', '', '', 'invoice_terms', 'varchar', 'NULL', $char_d, '', '', + 'archived', 'char', 'NULL', 1, '', '', + 'email_csv_cdr', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], #'index' => [ ['last'], ['company'] ], - 'index' => [ ['last'], [ 'company' ], [ 'referral_custnum' ], - [ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'refnum' ], - [ 'county' ], [ 'state' ], [ 'country' ], [ 'zip' ], + 'index' => [ + [ 'agentnum' ], [ 'refnum' ], [ 'custbatch' ], + [ 'referral_custnum' ], + [ 'payby' ], [ 'paydate' ], + [ 'archived' ], + #billing + [ 'last' ], [ 'company' ], + [ 'county' ], [ 'state' ], [ 'country' ], + [ 'zip' ], + [ 'daytime' ], [ 'night' ], [ 'fax' ], + #shipping [ 'ship_last' ], [ 'ship_company' ], + [ 'ship_county' ], [ 'ship_state' ], [ 'ship_country' ], + [ 'ship_zip' ], [ 'ship_daytime' ], [ 'ship_night' ], [ 'ship_fax' ], - [ 'payby' ], [ 'paydate' ], - [ 'agentnum' ], [ 'custbatch' ], ], }, + 'cust_recon' => { # what purpose does this serve? + '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' => [], + }, + + #eventually use for billing & ship from cust_main too + #for now, just cust_pkg locations + 'cust_location' => { + 'columns' => [ + 'locationnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'address1', 'varchar', '', $char_d, '', '', + 'address2', 'varchar', 'NULL', $char_d, '', '', + 'city', 'varchar', '', $char_d, '', '', + 'county', 'varchar', 'NULL', $char_d, '', '', + 'state', 'varchar', 'NULL', $char_d, '', '', + 'zip', 'varchar', 'NULL', 10, '', '', + 'country', 'char', '', 2, '', '', + 'geocode', 'varchar', 'NULL', 20, '', '', + ], + 'primary_key' => 'locationnum', + 'unique' => [], + 'index' => [ [ 'custnum' ], + [ 'county' ], [ 'state' ], [ 'country' ], [ 'zip' ], + ], + }, + 'cust_main_invoice' => { 'columns' => [ 'destnum', 'serial', '', '', '', '', @@ -695,6 +826,33 @@ sub tables_hashref { 'index' => [ [ 'custnum' ], [ '_date' ], ], }, + 'cust_main_exemption' => { + 'columns' => [ + 'exemptionnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'taxname', 'varchar', '', $char_d, '', '', + #start/end dates? for reporting? + ], + 'primary_key' => 'exemptionnum', + 'unique' => [], + 'index' => [ [ 'custnum' ] ], + }, + + 'cust_tax_adjustment' => { + 'columns' => [ + 'adjustmentnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'taxname', 'varchar', '', $char_d, '', '', + 'amount', @money_type, '', '', + 'comment', 'varchar', 'NULL', $char_d, '', '', + 'billpkgnum', 'int', 'NULL', '', '', '', + #more? no cust_bill_pkg_tax_location? + ], + 'primary_key' => 'adjustmentnum', + 'unique' => [], + 'index' => [ [ 'custnum' ], [ 'billpkgnum' ] ], + }, + 'cust_main_county' => { #county+state+country are checked off the #cust_main_county for validation and to provide # a tax rate. @@ -713,7 +871,9 @@ sub tables_hashref { 'primary_key' => 'taxnum', 'unique' => [], # 'unique' => [ ['taxnum'], ['state', 'county'] ], - 'index' => [ [ 'county' ], [ 'state' ], [ 'country' ] ], + 'index' => [ [ 'county' ], [ 'state' ], [ 'country' ], + [ 'taxclass' ], + ], }, 'tax_rate' => { @@ -724,17 +884,17 @@ sub tables_hashref { 'location', 'varchar', 'NULL', $char_d, '', '',#provided by tax authority 'taxclassnum', 'int', '', '', '', '', 'effective_date', @date_type, '', '', - 'tax', 'real', '', '', '', '', # tax % - 'excessrate', 'real', 'NULL','', '', '', # second tax % + 'tax', @taxrate_type, '', '', # tax % + 'excessrate', @taxrate_typen, '', '', # second tax % 'taxbase', @money_typen, '', '', # amount at first tax rate 'taxmax', @money_typen, '', '', # maximum about at both rates - 'usetax', 'real', 'NULL', '', '', '', # tax % when non-local - 'useexcessrate', 'real', 'NULL', '', '', '', # second tax % when non-local + 'usetax', @taxrate_typen, '', '', # tax % when non-local + 'useexcessrate', @taxrate_typen, '', '', # second tax % when non-local 'unittype', 'int', 'NULL', '', '', '', # for fee - 'fee', 'real', 'NULL', '', '', '', # amount tax per unit - 'excessfee', 'real', 'NULL', '', '', '', # second amount tax per unit - 'feebase', 'real', 'NULL', '', '', '', # units taxed at first rate - 'feemax', 'real', 'NULL', '', '', '', # maximum number of unit taxed + 'fee', @taxrate_typen, '', '', # amount tax per unit + 'excessfee', @taxrate_typen, '', '', # second amount tax per unit + 'feebase', @taxrate_typen, '', '', # units taxed at first rate + 'feemax', @taxrate_typen, '', '', # maximum number of unit taxed 'maxtype', 'int', 'NULL', '', '', '', # indicator of how thresholds accumulate 'taxname', 'varchar', 'NULL', $char_d, '', '', # may appear on invoice 'taxauth', 'int', 'NULL', '', '', '', # tax authority @@ -751,6 +911,21 @@ sub tables_hashref { 'index' => [ ['taxclassnum'], ['data_vendor', 'geocode'] ], }, + 'tax_rate_location' => { + 'columns' => [ + 'taxratelocationnum', 'serial', '', '', '', '', + 'data_vendor', 'varchar', 'NULL', $char_d, '', '', + 'geocode', 'varchar', '', 20, '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', + 'county', 'varchar', 'NULL', $char_d, '', '', + 'state', 'char', 'NULL', 2, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'taxratelocationnum', + 'unique' => [], + 'index' => [ [ 'data_vendor', 'geocode', 'disabled' ] ], + }, + 'cust_tax_location' => { 'columns' => [ 'custlocationnum', 'serial', '', '', '', '', @@ -795,14 +970,18 @@ sub tables_hashref { 'payinfo', 'varchar', 'NULL', 512, '', '', #see cust_main above 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paydate', 'varchar', 'NULL', 10, '', '', + 'recurring_billing', 'varchar', 'NULL', $char_d, '', '', #'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. 'payunique', 'varchar', 'NULL', $char_d, '', '', #separate paybatch "unique" functions from current usage + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'status', 'varchar', '', $char_d, '', '', + 'session_id', 'varchar', 'NULL', $char_d, '', '', #only need 32 'statustext', 'text', 'NULL', '', '', '', 'gatewaynum', 'int', 'NULL', '', '', '', #'cust_balance', @money_type, '', '', 'paynum', 'int', 'NULL', '', '', '', + 'jobnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'paypendingnum', 'unique' => [ [ 'payunique' ] ], @@ -825,6 +1004,7 @@ sub tables_hashref { '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 ], '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' ] ], @@ -844,6 +1024,7 @@ sub tables_hashref { 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. 'closed', 'char', 'NULL', 1, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'void_date', @date_type, '', '', 'reason', 'varchar', 'NULL', $char_d, '', '', 'otaker', 'varchar', '', 32, '', '', @@ -860,6 +1041,7 @@ sub tables_hashref { 'paynum', 'int', '', '', '', '', 'amount', @money_type, '', '', '_date', @date_type, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances ], 'primary_key' => 'billpaynum', 'unique' => [], @@ -938,28 +1120,31 @@ sub tables_hashref { 'cust_pkg' => { 'columns' => [ - 'pkgnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', - 'pkgpart', 'int', '', '', '', '', - 'otaker', 'varchar', '', 32, '', '', - 'setup', @date_type, '', '', - 'bill', @date_type, '', '', - 'last_bill', @date_type, '', '', - 'susp', @date_type, '', '', - 'adjourn', @date_type, '', '', - 'cancel', @date_type, '', '', - 'expire', @date_type, '', '', - 'change_date', @date_type, '', '', - 'change_pkgnum', 'int', 'NULL', '', '', '', - 'change_pkgpart', 'int', 'NULL', '', '', '', - 'manual_flag', 'char', 'NULL', 1, '', '', - 'quantity', 'int', 'NULL', '', '', '', + 'pkgnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'pkgpart', 'int', '', '', '', '', + 'locationnum', 'int', 'NULL', '', '', '', + 'otaker', 'varchar', '', 32, '', '', + 'start_date', @date_type, '', '', + 'setup', @date_type, '', '', + 'bill', @date_type, '', '', + 'last_bill', @date_type, '', '', + 'susp', @date_type, '', '', + 'adjourn', @date_type, '', '', + 'cancel', @date_type, '', '', + 'expire', @date_type, '', '', + 'change_date', @date_type, '', '', + 'change_pkgnum', 'int', 'NULL', '', '', '', + 'change_pkgpart', 'int', 'NULL', '', '', '', + 'change_locationnum', 'int', 'NULL', '', '', '', + 'manual_flag', 'char', 'NULL', 1, '', '', + 'quantity', 'int', 'NULL', '', '', '', ], 'primary_key' => 'pkgnum', 'unique' => [], - 'index' => [ ['custnum'], ['pkgpart'], - ['setup'], ['last_bill'], ['bill'], ['susp'], ['adjourn'], - ['expire'], ['cancel'], + 'index' => [ ['custnum'], ['pkgpart'], [ 'locationnum' ], + [ 'start_date' ], ['setup'], ['last_bill'], ['bill'], + ['susp'], ['adjourn'], ['expire'], ['cancel'], ['change_date'], ], }, @@ -1076,9 +1261,12 @@ sub tables_hashref { 'plan', 'varchar', 'NULL', $char_d, '', '', 'plandata', 'text', 'NULL', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'custom', 'char', 'NULL', 1, '', '', 'taxclass', 'varchar', 'NULL', $char_d, '', '', 'classnum', 'int', 'NULL', '', '', '', 'taxproductnum', 'int', 'NULL', '', '', '', + 'setup_cost', @money_typen, '', '', + 'recur_cost', @money_typen, '', '', 'pay_weight', 'real', 'NULL', '', '', '', 'credit_weight', 'real', 'NULL', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', @@ -1091,24 +1279,28 @@ sub tables_hashref { 'part_pkg_link' => { 'columns' => [ - 'pkglinknum', 'serial', '', '', '', '', - 'src_pkgpart', 'int', '', '', '', '', - 'dst_pkgpart', 'int', '', '', '', '', - 'link_type', 'varchar', '', $char_d, '', '', + 'pkglinknum', 'serial', '', '', '', '', + 'src_pkgpart', 'int', '', '', '', '', + 'dst_pkgpart', 'int', '', '', '', '', + 'link_type', 'varchar', '', $char_d, '', '', + 'hidden', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'pkglinknum', - 'unique' => [ [ 'src_pkgpart', 'dst_pkgpart', 'link_type' ] ], + 'unique' => [ [ 'src_pkgpart', 'dst_pkgpart', 'link_type', 'hidden' ] ], 'index' => [ [ 'src_pkgpart' ] ], }, + # XXX somewhat borked unique: we don't really want a hidden and unhidden + # it turns out we'd prefer to use svc, bill, and invisibill (or something) 'part_pkg_taxclass' => { 'columns' => [ 'taxclassnum', 'serial', '', '', '', '', 'taxclass', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'taxclassnum', 'unique' => [ [ 'taxclass' ] ], - 'index' => [], + 'index' => [ [ 'disabled' ] ], }, 'part_pkg_taxproduct' => { @@ -1177,7 +1369,7 @@ sub tables_hashref { ], 'primary_key' => 'pkgsvcnum', 'unique' => [ ['pkgpart', 'svcpart'] ], - 'index' => [ ['pkgpart'] ], + 'index' => [ ['pkgpart'], ['quantity'] ], }, 'part_referral' => { @@ -1209,6 +1401,7 @@ sub tables_hashref { 'columnnum', 'serial', '', '', '', '', 'svcpart', 'int', '', '', '', '', 'columnname', 'varchar', '', 64, '', '', + 'columnlabel', 'varchar', 'NULL', $char_d, '', '', 'columnvalue', 'varchar', 'NULL', $char_d, '', '', 'columnflag', 'char', 'NULL', 1, '', '', ], @@ -1250,7 +1443,7 @@ sub tables_hashref { 'columns' => [ 'svcnum', 'int', '', '', '', '', 'username', 'varchar', '', $username_len, '', '', - '_password', 'varchar', '', 512, '', '', + '_password', 'varchar', 'NULL', 512, '', '', '_password_encoding', 'varchar', 'NULL', $char_d, '', '', 'sec_phrase', 'varchar', 'NULL', $char_d, '', '', 'popnum', 'int', 'NULL', '', '', '', @@ -1439,24 +1632,28 @@ sub tables_hashref { 'queue' => { 'columns' => [ - 'jobnum', 'serial', '', '', '', '', - 'job', 'text', '', '', '', '', - '_date', 'int', '', '', '', '', - 'status', 'varchar', '', $char_d, '', '', - 'statustext', 'text', 'NULL', '', '', '', - 'svcnum', 'int', 'NULL', '', '', '', - 'secure', 'char', 'NULL', 1, '', '', # Y = needs to be run on machine - # w/private key + 'jobnum', 'serial', '', '', '', '', + 'job', 'text', '', '', '', '', + '_date', 'int', '', '', '', '', + 'status', 'varchar', '', $char_d, '', '', + 'statustext', 'text', 'NULL', '', '', '', + 'svcnum', 'int', 'NULL', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', + 'secure', 'char', 'NULL', 1, '', '', + 'priority', 'int', 'NULL', '', '', '', ], 'primary_key' => 'jobnum', 'unique' => [], - 'index' => [ [ 'svcnum' ], [ 'status' ] ], + 'index' => [ [ 'secure' ], [ 'priority' ], + [ 'job' ], [ 'svcnum' ], [ 'custnum' ], [ 'status' ], + ], }, 'queue_arg' => { 'columns' => [ 'argnum', 'serial', '', '', '', '', 'jobnum', 'int', '', '', '', '', + 'frozen', 'char', 'NULL', 1, '', '', 'arg', 'text', 'NULL', '', '', '', ], 'primary_key' => 'argnum', @@ -1606,19 +1803,20 @@ sub tables_hashref { 'columns' => [ 'svcnum', 'int', '', '', '', '', 'description', 'varchar', 'NULL', $char_d, '', '', - 'blocknum', 'int', '', '', '', '', + 'blocknum', 'int', 'NULL', '', '', '', 'speed_up', 'int', '', '', '', '', 'speed_down', 'int', '', '', '', '', - 'ip_addr', 'varchar', '', 15, '', '', + 'ip_addr', 'varchar', 'NULL', 15, '', '', 'mac_addr', 'varchar', 'NULL', 12, '', '', 'authkey', 'varchar', 'NULL', 32, '', '', 'latitude', 'decimal', 'NULL', '', '', '', 'longitude', 'decimal', 'NULL', '', '', '', 'altitude', 'decimal', 'NULL', '', '', '', 'vlan_profile', 'varchar', 'NULL', $char_d, '', '', + 'performance_profile', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', - 'unique' => [], + 'unique' => [ [ 'mac_addr' ] ], 'index' => [], }, @@ -1699,6 +1897,17 @@ sub tables_hashref { 'index' => [ [ 'pkgpart' ], [ 'optionname' ] ], }, + 'part_pkg_report_option' => { + 'columns' => [ + 'num', 'serial', '', '', '', '', + 'name', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'num', + 'unique' => [ [ 'name' ] ], + 'index' => [ [ 'disabled' ] ], + }, + 'rate' => { 'columns' => [ 'ratenum', 'serial', '', '', '', '', @@ -1739,11 +1948,11 @@ sub tables_hashref { 'rate_prefix' => { 'columns' => [ - 'prefixnum', 'serial', '', '', '', '', - 'regionnum', 'int', '', '',, '', '', - 'countrycode', 'varchar', '', 3, '', '', - 'npa', 'varchar', 'NULL', 6, '', '', - 'nxx', 'varchar', 'NULL', 3, '', '', + 'prefixnum', 'serial', '', '', '', '', + 'regionnum', 'int', '', '', '', '', + 'countrycode', 'varchar', '', 3, '', '', + 'npa', 'varchar', 'NULL', 10, '', '', #actually the whole prefix + 'nxx', 'varchar', 'NULL', 3, '', '', #actually not used ], 'primary_key' => 'prefixnum', 'unique' => [], @@ -1809,10 +2018,12 @@ sub tables_hashref { 'payment_gateway' => { 'columns' => [ 'gatewaynum', 'serial', '', '', '', '', + 'gateway_namespace','varchar', 'NULL', $char_d, '', '', 'gateway_module', 'varchar', '', $char_d, '', '', 'gateway_username', 'varchar', 'NULL', $char_d, '', '', 'gateway_password', 'varchar', 'NULL', $char_d, '', '', 'gateway_action', 'varchar', 'NULL', $char_d, '', '', + 'gateway_callback_url', 'varchar', 'NULL', $char_d, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'gatewaynum', @@ -1864,6 +2075,7 @@ sub tables_hashref { 'columns' => [ 'categorynum', 'serial', '', '', '', '', 'categoryname', 'varchar', '', $char_d, '', '', + 'weight', 'int', 'NULL', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'categorynum', @@ -1940,7 +2152,7 @@ sub tables_hashref { # how it was rated internally... 'ratedetailnum', 'int', 'NULL', '', '', '', - 'rated_price', 'decimal', 'NULL', '10,2', '', '', + 'rated_price', 'decimal', 'NULL', '10,4', '', '', 'distance', 'decimal', 'NULL', '', '', '', 'islocal', 'int', 'NULL', '', '', '', # '', '', 0, '' instead? @@ -1966,14 +2178,48 @@ sub tables_hashref { #NULL, done (or something) 'freesidestatus', 'varchar', 'NULL', 32, '', '', - 'cdrbatch', 'varchar', 'NULL', $char_d, '', '', + #NULL, done (or something) + 'freesiderewritestatus', 'varchar', 'NULL', 32, '', '', + + 'cdrbatch', 'varchar', 'NULL', 255, '', '', ], 'primary_key' => 'acctid', 'unique' => [], - 'index' => [ [ 'calldate' ], [ 'dst' ], [ 'accountcode' ], [ 'freesidestatus' ], [ 'cdrbatch' ], ], + 'index' => [ [ 'calldate' ], + [ 'src' ], [ 'dst' ], [ 'dcontext' ], [ 'charged_party' ], + [ 'accountcode' ], [ 'carrierid' ], + [ 'freesidestatus' ], [ 'freesiderewritestatus' ], + [ 'cdrbatch' ], + ], }, + 'cdr_termination' => { + 'columns' => [ + 'cdrtermnum', 'bigserial', '', '', '', '', + 'acctid', 'bigint', '', '', '', '', + 'termpart', 'int', '', '', '', '',#future use see below + 'rated_price', 'decimal', 'NULL', '10,4', '', '', + 'status', 'varchar', 'NULL', 32, '', '', + ], + 'primary_key' => 'cdrtermnum', + 'unique' => [ [ 'acctid', 'termpart' ] ], + 'index' => [ [ 'acctid' ], [ 'status' ], ], + }, + + #to handle multiple termination/settlement passes... + # 'part_termination' => { + # 'columns' => [ + # 'termpart', 'int', '', '', '', '', + # 'termname', 'varchar', '', $char_d, '', '', + # 'cdr_column', 'varchar', '', $char_d, '', '', #maybe set it here instead of in the price plan? + # ], + # 'primary_key' => 'termpart', + # 'unique' => [], + # 'index' => [], + # }, + + #the remaining cdr_ tables are not really used 'cdr_calltype' => { 'columns' => [ 'calltypenum', 'serial', '', '', '', '', @@ -2004,17 +2250,20 @@ sub tables_hashref { 'index' => [], }, - #map upstream rateid to ours... - 'cdr_upstream_rate' => { - 'columns' => [ - 'upstreamratenum', 'serial', '', '', '', '', - 'upstream_rateid', 'varchar', '', $char_d, '', '', - 'ratedetailnum', 'int', 'NULL', '', '', '', - ], - 'primary_key' => 'upstreamratenum', #XXX need a primary key - 'unique' => [ [ 'upstream_rateid' ] ], #unless we add another field, yeah - 'index' => [], - }, + #'cdr_file' => { + # 'columns' => [ + # 'filenum', 'serial', '', '', '', '', + # 'filename', 'varchar', '', '', '', '', + # 'status', 'varchar', 'NULL', '', '', '', + # ], + # 'primary_key' => 'filenum', + # 'unique' => [ [ 'filename' ], ], #just change the index if we need to + # # agent-virtualize or have a customer + # # with dup-filename needs or something + # # (only used by cdr.http_and_import for + # # chrissakes) + # 'index' => [], + #}, 'inventory_item' => { 'columns' => [ @@ -2123,17 +2372,41 @@ sub tables_hashref { 'index' => [ [ 'countrycode', 'phonenum' ] ], }, + 'phone_device' => { + 'columns' => [ + 'devicenum', 'serial', '', '', '', '', + 'devicepart', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'mac_addr', 'varchar', 'NULL', 12, '', '', + ], + 'primary_key' => 'devicenum', + 'unique' => [ [ 'mac_addr' ], ], + 'index' => [ [ 'devicepart' ], [ 'svcnum' ], ], + }, + + 'part_device' => { + 'columns' => [ + 'devicepart', 'serial', '', '', '', '', + 'devicename', 'varchar', '', $char_d, '', '', + #'classnum', #tie to an inventory class? + ], + 'primary_key' => 'devicepart', + 'unique' => [ [ 'devicename' ] ], #? + 'index' => [], + }, + 'phone_avail' => { 'columns' => [ - 'availnum', 'serial', '', '', '', '', - 'exportnum', 'int', '', '', '', '', - 'countrycode', 'varchar', '', 3, '', '', - 'state', 'char', 'NULL', 2, '', '', - 'npa', 'char', '', 3, '', '', - 'nxx', 'char', 'NULL', 3, '', '', - 'station', 'char', 'NULL', 4, '', '', + 'availnum', 'serial', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + 'countrycode', 'varchar', '', 3, '', '', + 'state', 'char', 'NULL', 2, '', '', + 'npa', 'char', '', 3, '', '', + 'nxx', 'char', 'NULL', 3, '', '', + 'station', 'char', 'NULL', 4, '', '', + 'name', 'varchar', 'NULL', $char_d, '', '', 'svcnum', 'int', 'NULL', '', '', '', - 'availbatch', 'varchar', 'NULL', $char_d, '', '', + 'availbatch', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'availnum', 'unique' => [],