'index' => [ ['typenum'], ['disabled'], ['agent_custnum'] ],
},
+ 'sales' => {
+ 'columns' => [
+ 'salesnum', 'serial', '', '', '', '',
+ 'salesperson', 'varchar', '', $char_d, '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'salesnum',
+ 'unique' => [],
+ 'index' => [ ['salesnum'], ['disabled'] ],
+ },
+
'agent_type' => {
'columns' => [
'typenum', 'serial', '', '', '', '',
'stateid', 'varchar', 'NULL', $char_d, '', '',
'stateid_state', 'varchar', 'NULL', $char_d, '', '',
'birthdate' ,@date_type, '', '',
+ 'spouse_birthdate' ,@date_type, '', '',
+ 'anniversary_date' ,@date_type, '', '',
'signupdate',@date_type, '', '',
'dundate', @date_type, '', '',
'company', 'varchar', 'NULL', $char_d, '', '',
- 'address1', 'varchar', '', $char_d, '', '',
+ 'address1', 'varchar', 'NULL', $char_d, '', '',
'address2', 'varchar', 'NULL', $char_d, '', '',
- 'city', 'varchar', '', $char_d, '', '',
+ 'city', 'varchar', 'NULL', $char_d, '', '',
'county', 'varchar', 'NULL', $char_d, '', '',
'state', 'varchar', 'NULL', $char_d, '', '',
'zip', 'varchar', 'NULL', 10, '', '',
- 'country', 'char', '', 2, '', '',
+ 'country', 'char', 'NULL', 2, '', '',
'latitude', 'decimal', 'NULL', '10,7', '', '',
'longitude','decimal', 'NULL', '10,7', '', '',
'coord_auto', 'char', 'NULL', 1, '', '',
'payby', 'char', '', 4, '', '',
'payinfo', 'varchar', 'NULL', 512, '', '',
'paycvv', 'varchar', 'NULL', 512, '', '',
- 'paymask', 'varchar', 'NULL', $char_d, '', '',
+ 'paymask', 'varchar', 'NULL', $char_d, '', '',
#'paydate', @date_type, '', '',
'paydate', 'varchar', 'NULL', 10, '', '',
'paystart_month', 'int', 'NULL', '', '', '',
'edit_subject', 'char', 'NULL', 1, '', '',
'locale', 'varchar', 'NULL', 16, '', '',
'calling_list_exempt', 'char', 'NULL', 1, '', '',
+ 'invoice_noemail', 'char', 'NULL', 1, '', '',
+ 'bill_locationnum', 'int', 'NULL', '', '', '',
+ 'ship_locationnum', 'int', 'NULL', '', '', '',
],
'primary_key' => 'custnum',
'unique' => [ [ 'agentnum', 'agent_custid' ] ],
[ 'referral_custnum' ],
[ 'payby' ], [ 'paydate' ],
[ 'archived' ],
- #billing
- [ 'last' ], [ 'company' ],
- [ 'county' ], [ 'state' ], [ 'country' ],
- [ 'zip' ],
- [ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'mobile' ],
- #shipping
- [ 'ship_last' ], [ 'ship_company' ],
- [ 'ship_county' ], [ 'ship_state' ], [ 'ship_country' ],
- [ 'ship_zip' ],
- [ 'ship_daytime' ], [ 'ship_night' ], [ 'ship_fax' ], [ 'ship_mobile' ]
],
},
- 'cust_recon' => { # what purpose does this serve?
+ 'cust_recon' => { # (some sort of not-well understood thing for OnPac)
'columns' => [
'reconid', 'serial', '', '', '', '',
'recondate', @date_type, '', '',
'index' => [],
},
- #eventually for cust_main too
+ 'contact_class' => {
+ 'columns' => [
+ 'classnum', 'serial', '', '', '', '',
+ 'classname', 'varchar', '', $char_d, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'classnum',
+ 'unique' => [],
+ 'index' => [ ['disabled'] ],
+ },
+
'contact' => {
'columns' => [
'contactnum', 'serial', '', '', '', '',
'prospectnum', 'int', 'NULL', '', '', '',
'custnum', 'int', 'NULL', '', '', '',
'locationnum', 'int', 'NULL', '', '', '', #not yet
+ 'classnum', 'int', 'NULL', '', '', '',
# 'titlenum', 'int', 'NULL', '', '', '', #eg Mr. Mrs. Dr. Rev.
'last', 'varchar', '', $char_d, '', '',
# 'middle', 'varchar', 'NULL', $char_d, '', '',
'index' => [ [ 'company' ], [ 'agentnum' ], [ 'disabled' ] ],
},
- #eventually use for billing & ship from cust_main too
- #for now, just cust_pkg locations
+ 'quotation' => {
+ 'columns' => [
+ #regular fields
+ 'quotationnum', 'serial', '', '', '', '',
+ 'prospectnum', 'int', 'NULL', '', '', '',
+ 'custnum', 'int', 'NULL', '', '', '',
+ '_date', @date_type, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ 'usernum', 'int', 'NULL', '', '', '',
+ #'total', @money_type, '', '',
+ #'quotation_term', 'varchar', 'NULL', $char_d, '', '',
+ ],
+ 'primary_key' => 'quotationnum',
+ 'unique' => [],
+ 'index' => [ [ 'prospectnum' ], ['custnum'], ],
+ },
+
+ 'quotation_pkg' => {
+ 'columns' => [
+ 'quotationpkgnum', 'serial', '', '', '', '',
+ 'pkgpart', 'int', '', '', '', '',
+ 'locationnum', 'int', 'NULL', '', '', '',
+ 'start_date', @date_type, '', '',
+ 'contract_end', @date_type, '', '',
+ 'quantity', 'int', 'NULL', '', '', '',
+ 'waive_setup', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'quotationpkgnum',
+ 'unique' => [],
+ 'index' => [ ['pkgpart'], ],
+ },
+
+ 'quotation_pkg_discount' => {
+ 'columns' => [
+ 'quotationpkgdiscountnum', 'serial', '', '', '', '',
+ 'quotationpkgnum', 'int', '', '', '', '',
+ 'discountnum', 'int', '', '', '', '',
+ #'end_date', @date_type, '', '',
+ ],
+ 'primary_key' => 'quotationpkgdiscountnum',
+ 'unique' => [],
+ 'index' => [ [ 'quotationpkgnum' ], ], #[ 'discountnum' ] ],
+ },
+
'cust_location' => { #'location' now that its prospects too, but...
'columns' => [
'locationnum', 'serial', '', '', '', '',
'country', 'char', '', 2, '', '',
'geocode', 'varchar', 'NULL', 20, '', '',
'district', 'varchar', 'NULL', 20, '', '',
+ 'censustract', 'varchar', 'NULL', 20, '', '',
+ 'censusyear', 'char', 'NULL', 4, '', '',
'location_type', 'varchar', 'NULL', 20, '', '',
'location_number', 'varchar', 'NULL', 20, '', '',
'location_kind', 'char', 'NULL', 1, '', '',
'unique' => [],
'index' => [ [ 'prospectnum' ], [ 'custnum' ],
[ 'county' ], [ 'state' ], [ 'country' ], [ 'zip' ],
+ [ 'city' ], [ 'district' ]
],
},
'cust_class' => {
'columns' => [
- 'classnum', 'serial', '', '', '', '',
- 'classname', 'varchar', '', $char_d, '', '',
- 'categorynum', 'int', 'NULL', '', '', '',
- 'disabled', 'char', 'NULL', 1, '', '',
+ 'classnum', 'serial', '', '', '', '',
+ 'classname', 'varchar', '', $char_d, '', '',
+ 'categorynum', 'int', 'NULL', '', '', '',
+ 'tax', 'char', 'NULL', 1, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'classnum',
'unique' => [],
'cust_main_exemption' => {
'columns' => [
- 'exemptionnum', 'serial', '', '', '', '',
- 'custnum', 'int', '', '', '', '',
- 'taxname', 'varchar', '', $char_d, '', '',
+ 'exemptionnum', 'serial', '', '', '', '',
+ 'custnum', 'int', '', '', '', '',
+ 'taxname', 'varchar', '', $char_d, '', '',
+ 'exempt_number', 'varchar', 'NULL', $char_d, '', '',
#start/end dates? for reporting?
],
'primary_key' => 'exemptionnum',
# index into payby table
# eventually
'payinfo', 'varchar', 'NULL', 512, '', '', #see cust_main above
- 'paymask', 'varchar', 'NULL', $char_d, '', '',
+ 'paymask', 'varchar', 'NULL', $char_d, '', '',
'paydate', 'varchar', 'NULL', 10, '', '',
'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
+ # cash/check deposit info fields
+ 'bank', 'varchar', 'NULL', $char_d, '', '',
+ 'depositor', 'varchar', 'NULL', $char_d, '', '',
+ 'account', 'varchar', 'NULL', 20, '', '',
+ 'teller', 'varchar', 'NULL', 20, '', '',
+ 'batchnum', 'int', 'NULL', '', '', '', #pay_batch foreign key
],
'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' ] ],
'columns' => [
'batchnum', 'serial', '', '', '', '',
'agentnum', 'int', 'NULL', '', '', '',
- 'payby', 'char', '', 4, '', '', # CARD/CHEK
+ 'payby', 'char', '', 4, '', '', # CARD/CHEK
'status', 'char', 'NULL', 1, '', '',
'download', @date_type, '', '',
'upload', @date_type, '', '',
+ 'title', 'varchar', 'NULL',255, '', '',
],
'primary_key' => 'batchnum',
'unique' => [],
'adjourn', @date_type, '', '',
'resume', @date_type, '', '',
'cancel', @date_type, '', '',
+ 'uncancel', @date_type, '', '',
+ 'uncancel_pkgnum', 'int', 'NULL', '', '', '',
'expire', @date_type, '', '',
'contract_end', @date_type, '', '',
'dundate', @date_type, '', '',
'percent', 'decimal', '', '7,4', '', '',
'months', 'decimal', 'NULL', '7,4', '', '',
'disabled', 'char', 'NULL', 1, '', '',
- 'setup', 'char', 'NULL', 1, '', '',
+ 'setup', 'char', 'NULL', 1, '', '',
+ #'linked', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'discountnum',
'unique' => [],
'cust_svc' => {
'columns' => [
- 'svcnum', 'serial', '', '', '', '',
- 'pkgnum', 'int', 'NULL', '', '', '',
- 'svcpart', 'int', '', '', '', '',
- 'overlimit', @date_type, '', '',
+ 'svcnum', 'serial', '', '', '', '',
+ 'pkgnum', 'int', 'NULL', '', '', '',
+ 'svcpart', 'int', '', '', '', '',
+ 'agent_svcid', 'int', 'NULL', '', '', '',
+ 'overlimit', @date_type, '', '',
],
'primary_key' => 'svcnum',
'unique' => [],
- 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ],
+ 'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'], [ 'agent_svcid' ] ],
},
'cust_svc_option' => {
'no_auto', 'char', 'NULL', 1, '', '',
'recur_show_zero', 'char', 'NULL', 1, '', '',
'setup_show_zero', 'char', 'NULL', 1, '', '',
+ 'successor', 'int', 'NULL', '', '', '',
+ 'family_pkgpart','int', 'NULL', '', '', '',
],
'primary_key' => 'pkgpart',
'unique' => [],
'disabled', 'char', 'NULL', 1, '', '',
'preserve', 'char', 'NULL', 1, '', '',
'selfservice_access', 'varchar', 'NULL', $char_d, '', '',
+ 'classnum', 'int', 'NULL', '', '', '',
],
'primary_key' => 'svcpart',
'unique' => [],
'part_svc_column' => {
'columns' => [
- 'columnnum', 'serial', '', '', '', '',
- 'svcpart', 'int', '', '', '', '',
- 'columnname', 'varchar', '', 64, '', '',
+ 'columnnum', 'serial', '', '', '', '',
+ 'svcpart', 'int', '', '', '', '',
+ 'columnname', 'varchar', '', 64, '', '',
'columnlabel', 'varchar', 'NULL', $char_d, '', '',
- 'columnvalue', 'varchar', 'NULL', $char_d, '', '',
- 'columnflag', 'char', 'NULL', 1, '', '',
+ 'columnvalue', 'varchar', 'NULL', 512, '', '',
+ 'columnflag', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'columnnum',
'unique' => [ [ 'svcpart', 'columnname' ] ],
'index' => [ [ 'svcpart' ] ],
},
+ 'part_svc_class' => {
+ 'columns' => [
+ 'classnum', 'serial', '', '', '', '',
+ 'classname', 'varchar', '', $char_d, '', '',
+ 'disabled', 'char', 'NULL', 1, '', '',
+ ],
+ 'primary_key' => 'classnum',
+ 'unique' => [],
+ 'index' => [ ['disabled'] ],
+ },
+
#(this should be renamed to part_pop)
'svc_acct_pop' => {
'columns' => [
'plan_id', 'varchar', 'NULL', $char_d, '', '',
],
'primary_key' => 'svcnum',
- 'unique' => [ [ 'mac_addr' ] ],
+ 'unique' => [ [ 'ip_addr' ], [ 'mac_addr' ] ],
'index' => [],
},
###
'upstream_currency', 'char', 'NULL', 3, '', '',
- 'upstream_price', 'decimal', 'NULL', '10,4', '', '',
'upstream_rateplanid', 'int', 'NULL', '', '', '', #?
# how it was rated internally...
'charged_party', 'varchar', 'NULL', $char_d, '', '',
+ 'upstream_price', 'decimal', 'NULL', '10,4', '', '',
+ 'upstream_src_regionname', 'varchar', 'NULL', $char_d, '', '',
+ 'upstream_dst_regionname', 'varchar', 'NULL', $char_d, '', '',
+
# how it was rated internally...
'rated_pretty_dst', 'varchar', 'NULL', $char_d, '', '',
'rated_regionname', 'varchar', 'NULL', $char_d, '', '',
'inventory_item' => {
'columns' => [
- 'itemnum', 'serial', '', '', '', '',
- 'classnum', 'int', '', '', '', '',
- 'agentnum', 'int', 'NULL', '', '', '',
- 'item', 'varchar', '', $char_d, '', '',
- 'svcnum', 'int', 'NULL', '', '', '',
+ 'itemnum', 'serial', '', '', '', '',
+ 'classnum', 'int', '', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'item', 'varchar', '', $char_d, '', '',
+ 'svcnum', 'int', 'NULL', '', '', '',
+ 'svc_field', 'varchar', 'NULL', $char_d, '', '',
],
'primary_key' => 'itemnum',
'unique' => [ [ 'classnum', 'item' ] ],
'index' => [ [ 'groupnum' ] ],
},
+ 'access_groupsales' => {
+ 'columns' => [
+ 'groupsalesnum', 'serial', '', '', '', '',
+ 'groupnum', 'int', '', '', '', '',
+ 'salesnum', 'int', '', '', '', '',
+ ],
+ 'primary_key' => 'groupsalesnum',
+ 'unique' => [ [ 'groupnum', 'salesnum' ] ],
+ 'index' => [ [ 'groupnum' ] ],
+ },
+
'access_right' => {
'columns' => [
'rightnum', 'serial', '', '', '', '',
'index' => [],
},
+ 'upgrade_journal' => {
+ 'columns' => [
+ 'upgradenum', 'serial', '', '', '', '',
+ '_date', 'int', '', '', '', '',
+ 'upgrade', 'varchar', '', $char_d, '', '',
+ 'status', 'varchar', '', $char_d, '', '',
+ 'statustext', 'varchar', 'NULL', $char_d, '', '',
+ ],
+ 'primary_key' => 'upgradenum',
+ 'unique' => [ [ 'upgradenum' ] ],
+ 'index' => [ [ 'upgrade' ] ],
+ },
+
+ 'ftp_target' => {
+ 'columns' => [
+ 'targetnum', 'serial', '', '', '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'hostname', 'varchar', '', $char_d, '', '',
+ 'port', 'int', '', '', '', '',
+ 'username', 'varchar', '', $char_d, '', '',
+ 'password', 'varchar', '', $char_d, '', '',
+ 'path', 'varchar', '', $char_d, '', '',
+ 'secure', 'char', 'NULL', 1, '', '',
+ 'handling', 'varchar', 'NULL', $char_d, '', '',
+ ],
+ 'primary_key' => 'targetnum',
+ 'unique' => [ [ 'targetnum' ] ],
+ 'index' => [],
+ },
+
%{ tables_hashref_torrus() },
# tables of ours for doing torrus virtual port combining