}
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',
my @date_type = ( 'int', 'NULL', '' );
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
'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' => {
'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, '', '',
+ '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' => {
'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' => {
'columns' => [
- 'billpkgnum', 'serial', '', '', '', '',
- 'pkgnum', 'int', '', '', '', '',
- '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', '', '', '', '',
- 'detail', 'varchar', '', $char_d, '', '',
+ 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable
+ 'pkgnum', 'int', 'NULL', '', '', '', # deprecated
+ 'invnum', 'int', 'NULL', '', '', '', # deprecated
+ 'amount', 'decimal', 'NULL', '10,4', '', '',
+ 'format', 'char', 'NULL', 1, '', '',
+ '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' => {
'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' => [],
- 'index' => [ ['custnum'] ],
+ 'index' => [ ['custnum'], ['_date'] ],
},
'cust_credit_bill' => {
'invnum', 'int', '', '', '', '',
'_date', @date_type, '', '',
'amount', @money_type, '', '',
+ 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
],
'primary_key' => 'creditbillnum',
'unique' => [],
'custnum', 'serial', '', '', '', '',
'agentnum', 'int', '', '', '', '',
'agent_custid', 'varchar', 'NULL', $char_d, '', '',
+ 'custbatch', 'varchar', 'NULL', $char_d, '', '',
# 'titlenum', 'int', 'NULL', '', '', '',
'last', 'varchar', '', $char_d, '', '',
# 'middle', 'varchar', 'NULL', $char_d, '', '',
'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, '', '',
'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' ], [ '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', '', '', '', '',
'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.
'primary_key' => 'taxnum',
'unique' => [],
# 'unique' => [ ['taxnum'], ['state', 'county'] ],
- 'index' => [ [ 'county' ], [ 'state' ], [ 'country' ] ],
+ 'index' => [ [ 'county' ], [ 'state' ], [ 'country' ],
+ [ 'taxclass' ],
+ ],
+ },
+
+ 'tax_rate' => {
+ 'columns' => [
+ 'taxnum', 'serial', '', '', '', '',
+ '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', '', '', '', '',
+ 'effective_date', @date_type, '', '',
+ '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', @taxrate_typen, '', '', # tax % when non-local
+ 'useexcessrate', @taxrate_typen, '', '', # second tax % when non-local
+ 'unittype', 'int', 'NULL', '', '', '', # for fee
+ '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
+ 'basetype', 'int', 'NULL', '', '', '', # indicator of basis for tax
+ 'passtype', 'int', 'NULL', '', '', '', # indicator declaring how item should be shown
+ 'passflag', 'char', 'NULL', 1, '', '', # Y = required to list as line item, N = Prohibited
+ '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', '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',
+ 'unique' => [],
+ 'index' => [ [ 'zip', 'plus4lo', 'plus4hi' ] ],
+ },
+
+ 'tax_class' => {
+ 'columns' => [
+ 'taxclassnum', 'serial', '', '', '', '',
+ 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
+ 'taxclass', 'varchar', '', $char_d, '', '',
+ 'description', 'varchar', '', 2*$char_d, '', '',
+ ],
+ 'primary_key' => 'taxclassnum',
+ 'unique' => [ [ 'data_vendor', 'taxclass' ] ],
+ 'index' => [],
},
'cust_pay_pending' => {
'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' ] ],
'cust_pay' => {
'columns' => [
'paynum', 'serial', '', '', '', '',
- #now cust_bill_pay #'invnum', 'int', '', '', '', '',
'custnum', 'int', '', '', '', '',
- 'paid', @money_type, '', '',
'_date', @date_type, '', '',
+ 'paid', @money_type, '', '',
+ 'otaker', 'varchar', 'NULL', 32, '', '', #NULL for the upgrade so we can create & populate the field
'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should be
# index into payby table
# eventually
'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' ] ],
'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, '', '',
'paynum', 'int', '', '', '', '',
'amount', @money_type, '', '',
'_date', @date_type, '', '',
+ 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
],
'primary_key' => 'billpaynum',
'unique' => [],
'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'],
],
},
'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' => {
'columns' => [
'refundnum', 'serial', '', '', '', '',
- #now cust_credit_refund #'crednum', 'int', '', '', '', '',
'custnum', 'int', '', '', '', '',
'_date', @date_type, '', '',
'refund', @money_type, '', '',
],
'primary_key' => 'refundnum',
'unique' => [],
- 'index' => [ [ 'custnum' ] ],
+ 'index' => [ ['custnum'], ['_date'] ],
},
'cust_credit_refund' => {
],
'primary_key' => 'creditrefundnum',
'unique' => [],
- 'index' => [ [ 'crednum', 'refundnum' ] ],
+ 'index' => [ ['crednum'], ['refundnum'] ],
},
'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', '', '', '', '',
'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', '', '', '',
'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'agentnum' ], ],
},
+ 'part_pkg_link' => {
+ 'columns' => [
+ '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', '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' => [ [ 'disabled' ] ],
+ },
+
+ 'part_pkg_taxproduct' => {
+ 'columns' => [
+ 'taxproductnum', 'serial', '', '', '', '',
+ 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
+ 'taxproduct', 'varchar', '', $char_d, '', '',
+ 'description', 'varchar', '', 3*$char_d, '', '',
+ ],
+ 'primary_key' => 'taxproductnum',
+ 'unique' => [ [ 'data_vendor', 'taxproduct' ] ],
+ 'index' => [],
+ },
+
+ 'part_pkg_taxrate' => {
+ 'columns' => [
+ 'pkgtaxratenum', 'serial', '', '', '', '',
+ 'data_vendor', 'varchar', 'NULL', $char_d, '', '', # update source
+ 'geocode', 'varchar', 'NULL', $char_d, '', '', # cch provides 10
+ 'taxproductnum', 'int', '', '', '', '',
+ 'city', 'varchar', 'NULL', $char_d, '', '', # tax_location?
+ 'county', 'varchar', 'NULL', $char_d, '', '',
+ 'state', 'varchar', 'NULL', $char_d, '', '',
+ 'local', 'varchar', 'NULL', $char_d, '', '',
+ 'country', 'char', 'NULL', 2, '', '',
+ 'taxclassnumtaxed', 'int', 'NULL', '', '', '',
+ 'taxcattaxed', 'varchar', 'NULL', $char_d, '', '',
+ 'taxclassnum', 'int', 'NULL', '', '', '',
+ 'effdate', @date_type, '', '',
+ 'taxable', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'pkgtaxratenum',
+ 'unique' => [],
+ 'index' => [ [ 'data_vendor', 'geocode', 'taxproductnum' ] ],
+ },
+
+ 'part_pkg_taxoverride' => {
+ 'columns' => [
+ 'taxoverridenum', 'serial', '', '', '', '',
+ 'pkgpart', 'serial', '', '', '', '',
+ 'taxclassnum', 'serial', '', '', '', '',
+ 'usage_class', 'varchar', 'NULL', $char_d, '', '',
+ ],
+ 'primary_key' => 'taxoverridenum',
+ 'unique' => [],
+ 'index' => [ [ 'pkgpart' ], [ 'taxclassnum' ] ],
+ },
+
# 'part_title' => {
# 'columns' => [
# 'titlenum', 'int', '', '',
],
'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, '', '',
],
'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',
'routernum', 'serial', '', '', '', '',
'routername', 'varchar', '', $char_d, '', '',
'svcnum', 'int', 'NULL', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
],
'primary_key' => 'routernum',
'unique' => [],
'routernum', 'int', '', '', '', '',
'ip_gateway', 'varchar', '', 15, '', '',
'ip_netmask', 'int', '', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'manual_flag', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'blocknum',
'unique' => [ [ 'blocknum', 'routernum' ] ],
'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' => [],
},
'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', '', '', '', '',
'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' ] ],
'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', '', '', '', '',
'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',
'index' => [],
},
+ 'pkg_category' => {
+ 'columns' => [
+ 'categorynum', 'serial', '', '', '', '',
+ 'categoryname', 'varchar', '', $char_d, '', '',
+ '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' => [],
# 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?
#NULL, done (or something)
'freesidestatus', 'varchar', 'NULL', 32, '', '',
+ #NULL, done (or something)
+ 'freesiderewritestatus', 'varchar', 'NULL', 32, '', '',
+
+ 'cdrbatch', 'varchar', 'NULL', 255, '', '',
+
],
'primary_key' => 'acctid',
'unique' => [],
- 'index' => [ [ 'calldate' ], [ 'dst' ], [ 'accountcode' ], [ 'freesidestatus' ] ],
+ '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', '', '', '', '',
'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' => [
'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', '', '', '', '',