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
'primary_key' => 'eventnum',
#no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ],
'unique' => [],
- 'index' => [ ['invnum'], ['status'] ],
+ 'index' => [ ['invnum'], ['status'], ['eventpart'],
+ ['statustext'], ['_date'],
+ ],
},
'part_bill_event' => {
'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' => {
'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, '', '',
],
'primary_key' => 'billpkgnum',
'unique' => [],
- 'index' => [ ['invnum'], [ 'pkgnum' ] ],
+ 'index' => [ ['invnum'], [ 'pkgnum' ], [ 'itemdesc' ], ],
},
'cust_bill_pkg_detail' => {
'invnum', 'int', 'NULL', '', '', '', # deprecated
'amount', @money_typen, '', '',
'format', 'char', 'NULL', 1, '', '',
- 'classnum', 'char', 'NULL', 1, '', '',
+ 'classnum', 'int', 'NULL', '', '', '',
'detail', 'varchar', '', 255, '', '',
],
'primary_key' => 'detailnum',
'billpkgtaxlocationnum', 'serial', '', '', '', '',
'billpkgnum', 'int', '', '', '', '',
'taxnum', 'int', '', '', '', '',
- 'taxtype', 'varchar', $char_d, '', '', '',
+ 'taxtype', 'varchar', '', $char_d, '', '',
'pkgnum', 'int', '', '', '', '',
'locationnum', 'int', '', '', '', '', #redundant?
'amount', @money_type, '', '',
'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', '', '', '', '',
'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', '', '', '', '',
'spool_cdr','char', 'NULL', 1, '', '',
'squelch_cdr','char', 'NULL', 1, '', '',
'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
+ 'archived', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'custnum',
'unique' => [ [ 'agentnum', 'agent_custid' ] ],
[ 'agentnum' ], [ 'refnum' ], [ 'custbatch' ],
[ 'referral_custnum' ],
[ 'payby' ], [ 'paydate' ],
+ [ 'archived' ],
#billing
[ 'last' ], [ 'company' ],
[ 'county' ], [ 'state' ], [ 'country' ],
],
},
+ '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' => {
'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.
'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
'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', '', '', '', '',
'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
'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' ] ],
'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', '', '', '',
],
'primary_key' => 'pkgsvcnum',
'unique' => [ ['pkgpart', 'svcpart'] ],
- 'index' => [ ['pkgpart'] ],
+ 'index' => [ ['pkgpart'], ['quantity'] ],
},
'part_referral' => {
'columnnum', 'serial', '', '', '', '',
'svcpart', 'int', '', '', '', '',
'columnname', 'varchar', '', 64, '', '',
+ 'columnlabel', 'varchar', 'NULL', $char_d, '', '',
'columnvalue', 'varchar', 'NULL', $char_d, '', '',
'columnflag', 'char', 'NULL', 1, '', '',
],
'svcnum', 'int', 'NULL', '', '', '',
'custnum', 'int', 'NULL', '', '', '',
'secure', 'char', 'NULL', 1, '', '',
+ 'priority', 'int', 'NULL', '', '', '',
],
'primary_key' => 'jobnum',
'unique' => [],
- 'index' => [ [ 'job' ], [ 'svcnum' ], [ 'custnum' ], [ 'status' ] ],
+ 'index' => [ [ 'secure' ], [ 'priority' ],
+ [ 'job' ], [ 'svcnum' ], [ 'custnum' ], [ 'status' ],
+ ],
},
'queue_arg' => {
'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' => [],
},
'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', '', '', '', '',
'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' => [],
'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',
#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' ], [ 'src' ], [ 'dst' ], [ 'charged_party' ], [ 'accountcode' ], [ 'freesidestatus' ], [ 'cdrbatch' ], ],
+ 'index' => [ [ 'calldate' ], [ 'src' ], [ 'dst' ], [ 'charged_party' ], [ 'accountcode' ], [ 'freesidestatus' ], [ 'freesiderewritestatus' ], [ 'cdrbatch' ], ],
},
'cdr_calltype' => {
'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' => [],