X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=faafcc6efaa0ebcdb563caca93ee33a53f4cc0ea;hp=457e5c80daa2c26eabf7b09af6886b2d18e60842;hb=9d77a21db3642ca66d9a0e545b804b7e6b4090ee;hpb=dc3217c61d6a62a8e599d8804e05ba45b3224c7e diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 457e5c80d..faafcc6ef 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -192,6 +192,22 @@ sub dbdef_dist { } keys %indices; + $h_indices{"h_${table}_srckey"} = DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_srckey", + 'unique' => 0, + 'columns' => [ 'history_action', #right? + $tableobj->primary_key, + ], + }); + + $h_indices{"h_${table}_srckey2"} = DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_srckey2", + 'unique' => 0, + 'columns' => [ 'history_date', + $tableobj->primary_key, + ], + }); + my $h_tableobj = DBIx::DBSchema::Table->new( { 'name' => "h_$table", 'primary_key' => 'historynum', @@ -304,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 @@ -316,18 +334,21 @@ sub tables_hashref { 'agentnum', 'serial', '', '', '', '', 'agent', 'varchar', '', $char_d, '', '', 'typenum', 'int', '', '', '', '', - 'disabled', 'char', 'NULL', 1, '', '', 'ticketing_queueid', 'int', 'NULL', '', '', '', 'invoice_template', 'varchar', 'NULL', $char_d, '', '', + 'agent_custnum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', 'username', 'varchar', 'NULL', $char_d, '', '', #deprecated '_password', 'varchar', 'NULL', $char_d, '', '', #deprecated 'freq', 'int', 'NULL', '', '', '', #deprecated (never used) 'prog', @perl_type, '', '', #deprecated (never used) - ], 'primary_key' => 'agentnum', + #'unique' => [ [ 'agent_custnum' ] ], #one agent per customer? + #insert is giving it a value, tho.. + #'index' => [ ['typenum'], ['disabled'] ], 'unique' => [], - 'index' => [ ['typenum'], ['disabled'] ], + 'index' => [ ['typenum'], ['disabled'], ['agent_custnum'] ], }, 'agent_type' => { @@ -351,18 +372,57 @@ 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, '', '', + #regular fields + 'invnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'charged', @money_type, '', '', + 'invoice_terms', 'varchar', 'NULL', $char_d, '', '', + + #customer balance info at invoice generation time + 'previous_balance', @money_typen, '', '', #eventually not nullable + 'billing_balance', @money_typen, '', '', #eventually not nullable + + #deprecated (unused by now, right?) + 'printed', 'int', '', '', '', '', + + #specific use cases + 'closed', 'char', 'NULL', 1, '', '', #not yet used much + 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements + 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy ], 'primary_key' => 'invnum', + 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh + 'index' => [ ['custnum'], ['_date'], ['statementnum'], ['agent_invid'] ], + }, + + 'cust_statement' => { + 'columns' => [ + 'statementnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + ], + 'primary_key' => 'statementnum', 'unique' => [], - 'index' => [ ['custnum'], ['_date'] ], + 'index' => [ ['custnum'], ['_date'], ], }, 'cust_bill_event' => { @@ -377,7 +437,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' => { @@ -474,37 +536,95 @@ 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, '', '', + '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' => { 'columns' => [ 'detailnum', 'serial', '', '', '', '', - 'pkgnum', 'int', '', '', '', '', - 'invnum', 'int', '', '', '', '', + 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable + 'pkgnum', 'int', 'NULL', '', '', '', # deprecated + 'invnum', 'int', 'NULL', '', '', '', # deprecated + 'amount', 'decimal', 'NULL', '10,4', '', '', 'format', 'char', 'NULL', 1, '', '', - 'detail', 'varchar', '', $char_d, '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'phonenum', 'varchar', 'NULL', 15, '', '', + 'detail', 'varchar', '', 255, '', '', ], 'primary_key' => 'detailnum', 'unique' => [], - 'index' => [ [ 'pkgnum', 'invnum' ] ], + 'index' => [ [ 'billpkgnum' ], [ 'classnum' ], [ 'pkgnum', 'invnum' ] ], + }, + + 'cust_bill_pkg_display' => { + 'columns' => [ + 'billpkgdisplaynum', 'serial', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'section', 'varchar', 'NULL', $char_d, '', '', + #'unitsetup', @money_typen, '', '', #override the linked real one? + #'unitrecur', @money_typen, '', '', #this too? + 'post_total', 'char', 'NULL', 1, '', '', + 'type', 'char', 'NULL', 1, '', '', + 'summary', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'billpkgdisplaynum', + 'unique' => [], + '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' => { @@ -516,7 +636,9 @@ sub tables_hashref { 'otaker', 'varchar', '', 32, '', '', 'reason', 'text', 'NULL', '', '', '', 'reasonnum', 'int', 'NULL', '', '', '', + 'addlinfo', 'text', 'NULL', '', '', '', 'closed', 'char', 'NULL', 1, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances ], 'primary_key' => 'crednum', 'unique' => [], @@ -530,6 +652,7 @@ sub tables_hashref { 'invnum', 'int', '', '', '', '', '_date', @date_type, '', '', 'amount', @money_type, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances ], 'primary_key' => 'creditbillnum', 'unique' => [], @@ -541,6 +664,8 @@ sub tables_hashref { 'creditbillpkgnum', 'serial', '', '', '', '', 'creditbillnum', 'int', '', '', '', '', 'billpkgnum', 'int', '', '', '', '', + 'billpkgtaxlocationnum', 'int', 'NULL', '', '', '', + 'billpkgtaxratelocationnum', 'int', 'NULL', '', '', '', 'amount', @money_type, '', '', 'setuprecur', 'varchar', '', $char_d, '', '', 'sdate', @date_type, '', '', @@ -548,7 +673,11 @@ sub tables_hashref { ], 'primary_key' => 'creditbillpkgnum', 'unique' => [], - 'index' => [ [ 'creditbillnum' ], [ 'billpkgnum' ], ], + 'index' => [ [ 'creditbillnum' ], + [ 'billpkgnum' ], + [ 'billpkgtaxlocationnum' ], + [ 'billpkgtaxratelocationnum' ], + ], }, 'cust_main' => { @@ -556,6 +685,8 @@ sub tables_hashref { 'custnum', 'serial', '', '', '', '', 'agentnum', 'int', '', '', '', '', 'agent_custid', 'varchar', 'NULL', $char_d, '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'custbatch', 'varchar', 'NULL', $char_d, '', '', # 'titlenum', 'int', 'NULL', '', '', '', 'last', 'varchar', '', $char_d, '', '', # 'middle', 'varchar', 'NULL', $char_d, '', '', @@ -565,6 +696,7 @@ sub tables_hashref { 'stateid_state', 'varchar', 'NULL', $char_d, '', '', 'birthdate' ,@date_type, '', '', 'signupdate',@date_type, '', '', + 'dundate', @date_type, '', '', 'company', 'varchar', 'NULL', $char_d, '', '', 'address1', 'varchar', '', $char_d, '', '', 'address2', 'varchar', 'NULL', $char_d, '', '', @@ -603,26 +735,90 @@ sub tables_hashref { 'paystate', 'varchar', 'NULL', $char_d, '', '', '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', '', '', '', '', 'referral_custnum', 'int', 'NULL', '', '', '', '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' ], [ 'classnum' ], + [ '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' ], ], }, + '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', '', '', '', '', @@ -647,13 +843,65 @@ sub tables_hashref { 'index' => [ [ 'custnum' ], [ '_date' ], ], }, + 'cust_category' => { + 'columns' => [ + 'categorynum', 'serial', '', '', '', '', + 'categoryname', 'varchar', '', $char_d, '', '', + 'weight', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'categorynum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, + + 'cust_class' => { + 'columns' => [ + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + 'categorynum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'classnum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, + + '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. 'columns' => [ 'taxnum', 'serial', '', '', '', '', - 'state', 'varchar', 'NULL', $char_d, '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', + 'state', 'varchar', 'NULL', $char_d, '', '', 'country', 'char', '', 2, '', '', 'taxclass', 'varchar', 'NULL', $char_d, '', '', 'exempt_amount', @money_type, '', '', @@ -665,7 +913,9 @@ sub tables_hashref { 'primary_key' => 'taxnum', 'unique' => [], # 'unique' => [ ['taxnum'], ['state', 'county'] ], - 'index' => [ [ 'county' ], [ 'state' ], [ 'country' ] ], + 'index' => [ [ 'city' ], [ 'county' ], [ 'state' ], [ 'country' ], + [ 'taxclass' ], + ], }, 'tax_rate' => { @@ -676,17 +926,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 @@ -696,21 +946,41 @@ sub tables_hashref { 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt 'manual', 'char', 'NULL', 1, '', '', # Y = manually edited + 'disabled', 'char', 'NULL', 1, '', '', # Y = tax disabled ], 'primary_key' => 'taxnum', 'unique' => [], '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', '', '', '', '', 'data_vendor', 'varchar', 'NULL', $char_d, '', '', # update source + 'city', 'varchar', 'NULL', $char_d, '', '', + 'postalcity', 'varchar', 'NULL', $char_d, '', '', + 'county', 'varchar', 'NULL', $char_d, '', '', 'zip', 'char', '', 5, '', '', 'state', 'char', '', 2, '', '', - 'plus4hi', 'char', '', 4, '', '', - 'plus4lo', 'char', '', 4, '', '', + 'plus4hi', 'char', 'NULL', 4, '', '', + 'plus4lo', 'char', 'NULL', 4, '', '', 'default_location','char', 'NULL', 1, '', '', # Y = default for zip + 'cityflag', 'char', 'NULL', 1, '', '', # I(n)/O(out)/B(oth)/NULL 'geocode', 'varchar', '', 20, '', '', ], 'primary_key' => 'custlocationnum', @@ -742,14 +1012,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' ] ], @@ -772,6 +1046,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' ] ], @@ -791,6 +1066,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, '', '', @@ -807,6 +1083,7 @@ sub tables_hashref { 'paynum', 'int', '', '', '', '', 'amount', @money_type, '', '', '_date', @date_type, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances ], 'primary_key' => 'billpaynum', 'unique' => [], @@ -831,6 +1108,8 @@ sub tables_hashref { 'billpaypkgnum', 'serial', '', '', '', '', 'billpaynum', 'int', '', '', '', '', 'billpkgnum', 'int', '', '', '', '', + 'billpkgtaxlocationnum', 'int', 'NULL', '', '', '', + 'billpkgtaxratelocationnum', 'int', 'NULL', '', '', '', 'amount', @money_type, '', '', 'setuprecur', 'varchar', '', $char_d, '', '', 'sdate', @date_type, '', '', @@ -885,27 +1164,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, '', '', + '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'], ], }, @@ -922,17 +1205,31 @@ sub tables_hashref { 'index' => [ [ 'pkgnum' ], [ 'optionname' ] ], }, + 'cust_pkg_detail' => { + 'columns' => [ + 'pkgdetailnum', 'serial', '', '', '', '', + 'pkgnum', 'int', '', '', '', '', + 'detail', 'varchar', '', $char_d, '', '', + 'detailtype', 'char', '', 1, '', '', # "I"nvoice or "C"omment + 'weight', 'int', '', '', '', '', + ], + 'primary_key' => 'pkgdetailnum', + 'unique' => [], + 'index' => [ [ 'pkgnum', 'detailtype' ] ], + }, + 'cust_pkg_reason' => { 'columns' => [ 'num', 'serial', '', '', '', '', 'pkgnum', 'int', '', '', '', '', 'reasonnum','int', '', '', '', '', + 'action', 'char', 'NULL', 1, '', '', #should not be nullable 'otaker', 'varchar', '', 32, '', '', 'date', @date_type, '', '', ], 'primary_key' => 'num', 'unique' => [], - 'index' => [], + 'index' => [ [ 'pkgnum' ], [ 'reasonnum' ], ['action'], ], }, 'cust_refund' => { @@ -982,6 +1279,18 @@ sub tables_hashref { 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ], }, + 'cust_svc_option' => { + 'columns' => [ + 'optionnum', 'serial', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'optionname', 'varchar', '', $char_d, '', '', + 'optionvalue', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'optionnum', + 'unique' => [], + 'index' => [ [ 'svcnum' ], [ 'optionname' ] ], + }, + 'part_pkg' => { 'columns' => [ 'pkgpart', 'serial', '', '', '', '', @@ -996,9 +1305,13 @@ 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', '', '', '', + 'addon_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', '', '', '', @@ -1006,29 +1319,35 @@ sub tables_hashref { ], 'primary_key' => 'pkgpart', 'unique' => [], - 'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'agentnum' ], ], + 'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'classnum' ], + [ 'agentnum' ], + ], }, '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' => { @@ -1036,7 +1355,7 @@ sub tables_hashref { 'taxproductnum', 'serial', '', '', '', '', 'data_vendor', 'varchar', 'NULL', $char_d, '', '', 'taxproduct', 'varchar', '', $char_d, '', '', - 'description', 'varchar', '', 2*$char_d, '', '', + 'description', 'varchar', '', 3*$char_d, '', '', ], 'primary_key' => 'taxproductnum', 'unique' => [ [ 'data_vendor', 'taxproduct' ] ], @@ -1070,6 +1389,7 @@ sub tables_hashref { 'taxoverridenum', 'serial', '', '', '', '', 'pkgpart', 'serial', '', '', '', '', 'taxclassnum', 'serial', '', '', '', '', + 'usage_class', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'taxoverridenum', 'unique' => [], @@ -1096,7 +1416,7 @@ sub tables_hashref { ], 'primary_key' => 'pkgsvcnum', 'unique' => [ ['pkgpart', 'svcpart'] ], - 'index' => [ ['pkgpart'] ], + 'index' => [ ['pkgpart'], ['quantity'] ], }, 'part_referral' => { @@ -1128,6 +1448,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, '', '', ], @@ -1169,7 +1490,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', '', '', '', @@ -1358,24 +1679,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', 'varchar', '', 512, '', '', + '_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', @@ -1514,6 +1839,7 @@ sub tables_hashref { 'ip_gateway', 'varchar', '', 15, '', '', 'ip_netmask', 'int', '', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', + 'manual_flag', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'blocknum', 'unique' => [ [ 'blocknum', 'routernum' ] ], @@ -1524,19 +1850,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' => [], }, @@ -1617,6 +1944,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', '', '', '', '', @@ -1638,6 +1976,7 @@ sub tables_hashref { 'min_charge', 'decimal', '', '10,5', '', '', 'sec_granularity', 'int', '', '', '', '', #time period (link to table of periods)? + 'classnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'ratedetailnum', 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ], @@ -1656,17 +1995,28 @@ 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' => [], 'index' => [ [ 'countrycode' ], [ 'regionnum' ] ], }, + 'usage_class' => { + 'columns' => [ + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'classnum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, + 'reg_code' => { 'columns' => [ 'codenum', 'serial', '', '', '', '', @@ -1715,10 +2065,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', @@ -1766,11 +2118,24 @@ sub tables_hashref { 'index' => [], }, + 'pkg_category' => { + 'columns' => [ + 'categorynum', 'serial', '', '', '', '', + 'categoryname', 'varchar', '', $char_d, '', '', + 'weight', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'categorynum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, + 'pkg_class' => { 'columns' => [ - 'classnum', 'serial', '', '', '', '', - 'classname', 'varchar', '', $char_d, '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + 'categorynum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'classnum', 'unique' => [], @@ -1821,6 +2186,8 @@ sub tables_hashref { ### # fields for unitel/RSLCOM/convergent that don't map well to asterisk # defaults + # though these are now used elsewhere: + # charged_party, upstream_price, rated_price, carrierid ### #cdr_type: Usage = 1, S&E = 7, OC&C = 8 @@ -1834,7 +2201,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? @@ -1860,12 +2227,64 @@ sub tables_hashref { #NULL, done (or something) 'freesidestatus', 'varchar', 'NULL', 32, '', '', + #NULL, done (or something) + 'freesiderewritestatus', 'varchar', 'NULL', 32, '', '', + + #an indexed place to put big numbers + 'cdrid', 'bigint', 'NULL', '', '', '', + + #i should become a table + 'cdrbatch', 'varchar', 'NULL', 255, '', '', + #'cdrbatchnum', 'int', 'NULL', '', '', '', + ], 'primary_key' => 'acctid', 'unique' => [], - 'index' => [ [ 'calldate' ], [ 'dst' ], [ 'accountcode' ], [ 'freesidestatus' ] ], + 'index' => [ [ 'calldate' ], + [ 'src' ], [ 'dst' ], [ 'dcontext' ], [ 'charged_party' ], + [ 'accountcode' ], [ 'carrierid' ], [ 'cdrid' ], + [ 'freesidestatus' ], [ 'freesiderewritestatus' ], + [ 'cdrbatch' ], + ], + }, + + #'cdr_batch' => { + # 'columns' => [ + # 'cdrbatchnum', 'serial', '', '', '', '', + # 'cdrbatch', 'varchar', 'NULL', 255, '', '', + # '_date', @date_type, '', '', + # ], + # 'primary_key' => 'cdrbatchnum', + # 'unique' => [ [ 'cdrbatch' ] ], + # 'index' => [], + #}, + + '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', '', '', '', '', @@ -1896,17 +2315,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' => [ @@ -2003,16 +2425,65 @@ sub tables_hashref { 'svc_phone' => { 'columns' => [ - 'svcnum', 'int', '', '', '', '', - 'countrycode', 'varchar', '', 3, '', '', - 'phonenum', 'varchar', '', 15, '', '', #12 ? - 'pin', 'varchar', 'NULL', $char_d, '', '', + 'svcnum', 'int', '', '', '', '', + 'countrycode', 'varchar', '', 3, '', '', + 'phonenum', 'varchar', '', 15, '', '', #12 ? + 'pin', 'varchar', 'NULL', $char_d, '', '', + 'sip_password', 'varchar', 'NULL', $char_d, '', '', + 'phone_name', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], '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, '', '', + 'name', 'varchar', 'NULL', $char_d, '', '', + 'svcnum', 'int', 'NULL', '', '', '', + 'availbatch', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'availnum', + 'unique' => [], + 'index' => [ [ 'exportnum', 'countrycode', 'state' ], #npa search + [ 'exportnum', 'countrycode', 'npa' ], #nxx search + [ 'exportnum', 'countrycode', 'npa', 'nxx' ],#station search + [ 'exportnum', 'countrycode', 'npa', 'nxx', 'station' ], # # + [ 'svcnum' ], + [ 'availbatch' ], + ], + }, + 'reason_type' => { 'columns' => [ 'typenum', 'serial', '', '', '', '',