],
});
+ #necessary for queries that want to look at *who* made changes
+ $h_indices{"h_${table}_usernum"} =
+ DBIx::DBSchema::Index->new({
+ 'name' => "h_${table}_usernum",
+ 'unique' => 0,
+ 'columns' => [ 'history_usernum'],
+ });
+
+ # necessary because of the evil OR username for older data, be really nice if everything was just migrated to usernum and we could drop username
+ # This will not be helpful to mysql, but postgres smartly does a bitmap across both indexes, mysql will just use one
+
+ $h_indices{"h_${table}_user"} =
+ DBIx::DBSchema::Index->new({
+ 'name' => "h_${table}_user",
+ 'unique' => 0,
+ 'columns' => [ 'history_user'],
+ });
}
my $primary_key_col = $tableobj->column($tableobj->primary_key)
'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
#customer balance info at invoice generation time
+ #(deprecated)
'previous_balance', @money_typen, '', '', #eventually not nullable
'billing_balance', @money_typen, '', '', #eventually not nullable
'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements
'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy
'promised_date', @date_type, '', '',
+
+ 'pending', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'invnum',
'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh
'cust_bill_pkg_detail' => {
'columns' => [
- 'detailnum', 'serial', '', '', '', '',
+ 'detailnum', 'serial', '', '', '', '',
+ # bigserial? this table will eventually be as big as cdr...
'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable
'pkgnum', 'int', 'NULL', '', '', '', # deprecated
'invnum', 'int', 'NULL', '', '', '', # deprecated
'accountcode', 'varchar', 'NULL', 20, '', '',
'startdate', @date_type, '', '',
'regionname', 'varchar', 'NULL', $char_d, '', '',
- 'detail', 'varchar', '', 255, '', '',
+ 'detail', 'varchar', '', 255, '', '',
],
'primary_key' => 'detailnum',
'unique' => [],
'message_noemail', 'char', 'NULL', 1, '', '',
'bill_locationnum', 'int', 'NULL', '', '', '',
'ship_locationnum', 'int', 'NULL', '', '', '',
+ 'taxstatusnum', 'char', 'NULL', 32, '', '',
],
'primary_key' => 'custnum',
'unique' => [ [ 'agentnum', 'agent_custid' ] ],
'add_date', @date_type, '', '',
'disabled', 'char', 'NULL', 1, '', '',
'custnum', 'int', 'NULL', '', '', '',
+ 'refnum', 'int', 'NULL', '', '', '',
],
'primary_key' => 'prospectnum',
'unique' => [],
- 'index' => [ [ 'company' ], [ 'agentnum' ], [ 'disabled' ] ],
+ 'index' => [ ['company'], ['agentnum'], ['disabled'], ['refnum'] ],
'foreign_keys' => [
{ columns => [ 'agentnum' ],
table => 'agent',
{ columns => [ 'custnum' ],
table => 'cust_main',
},
+ { columns => [ 'refnum' ],
+ table => 'part_referral',
+ },
],
},
'locationnum', 'serial', '', '', '', '',
'prospectnum', 'int', 'NULL', '', '', '',
'custnum', 'int', 'NULL', '', '', '',
+ 'locationname', 'varchar', 'NULL', $char_d, '', '',
'address1', 'varchar', '', $char_d, '', '',
'address2', 'varchar', 'NULL', $char_d, '', '',
'city', 'varchar', '', $char_d, '', '',
'location_type', 'varchar', 'NULL', 20, '', '',
'location_number', 'varchar', 'NULL', 20, '', '',
'location_kind', 'char', 'NULL', 1, '', '',
+ 'incorporated', 'char', 'NULL', 1, '', '',
'disabled', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'locationnum',
'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', '', '', '', '',
+ 'taxclassnum', 'int', 'NULL', '', '', '',
'effective_date', @date_type, '', '',
'tax', @taxrate_type, '', '', # tax %
'excessrate', @taxrate_typen, '', '', # second tax %
'city', 'varchar', 'NULL', $char_d, '', '',
'county', 'varchar', 'NULL', $char_d, '', '',
'state', 'char', 'NULL', 2, '', '',
+ 'country', 'char', 'NULL', 2, '', '',
'disabled', 'char', 'NULL', 1, '', '',
],
'primary_key' => 'taxratelocationnum',
'city', 'varchar', 'NULL', $char_d, '', '',
'postalcity', 'varchar', 'NULL', $char_d, '', '',
'county', 'varchar', 'NULL', $char_d, '', '',
- 'zip', 'char', '', 5, '', '',
+ 'zip', 'char', 'NULL', 5, '', '',
'state', 'char', '', 2, '', '',
+ 'country', 'char', 'NULL', 2, '', '', # should not actually be NULL, but cch...
+ 'ziphi', 'char', 'NULL', 10, '', '',
+ 'ziplo', 'char', 'NULL', 10, '', '',
'plus4hi', 'char', 'NULL', 4, '', '',
'plus4lo', 'char', 'NULL', 4, '', '',
'default_location','char', 'NULL', 1, '', '', # Y = default for zip
],
'primary_key' => 'custlocationnum',
'unique' => [],
- 'index' => [ [ 'zip', 'plus4lo', 'plus4hi' ] ],
+ 'index' => [ [ 'zip', 'plus4lo', 'plus4hi', 'ziphi', 'ziplo' ] ],
},
'tax_class' => {
'index' => [],
},
+ 'tax_status' => {
+ 'columns' => [
+ 'taxstatusnum', 'serial', '', '', '', '',
+ 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
+ 'taxstatus', 'varchar', '', $char_d, '', '',
+ 'description', 'varchar', '', $char_d, '', '',
+ ],
+ 'primary_key' => 'taxstatusnum',
+ 'unique' => [ [ 'data_vendor', 'taxstatus' ] ],
+ 'index' => [],
+ },
+
'cust_pay_pending' => {
'columns' => [
'paypendingnum', 'serial', '', '', '', '',
'index' => [ ['custnum'], ['pkgpart'], ['pkgbatch'],
['locationnum'], ['usernum'], ['agent_pkgid'],
['order_date'], [ 'start_date' ], ['setup'], ['bill'],
- ['last_bill'], ['susp'], ['adjourn'], ['cancel'],
- ['expire'], ['contract_end'], ['change_date'],
+ ['last_bill'], ['susp'], ['adjourn'], ['resume'],
+ ['cancel'], ['expire'], ['contract_end'],
+ ['change_date'],
['no_auto'],
#['contactnum'],
['salesnum'],
'successor', 'int', 'NULL', '', '', '',
'family_pkgpart','int', 'NULL', '', '', '',
'delay_start', 'int', 'NULL', '', '', '',
+ 'start_on_hold', 'char', 'NULL', 1, '', '',
'agent_pkgpartid', 'varchar', 'NULL', 20, '', '',
],
'primary_key' => 'pkgpart',
'data_vendor', 'varchar', 'NULL', $char_d, '', '',
'taxproduct', 'varchar', '', $char_d, '', '',
'description', 'varchar', '', 3*$char_d, '', '',
+ 'note', 'text', 'NULL', '', '', '',
],
'primary_key' => 'taxproductnum',
'unique' => [ [ 'data_vendor', 'taxproduct' ] ],
[ 'job' ], [ 'svcnum' ], [ 'custnum' ], [ 'status' ],
],
'foreign_keys' => [
- { columns => [ 'svcnum' ],
- table => 'cust_svc',
- },
+# { columns => [ 'svcnum' ],
+# table => 'cust_svc',
+# },
{ columns => [ 'custnum' ],
table => 'cust_main',
},
'rate' => {
'columns' => [
- 'ratenum', 'serial', '', '', '', '',
- 'ratename', 'varchar', '', $char_d, '', '',
- 'agentnum', 'int', 'NULL', '', '', '',
+ 'ratenum', 'serial', '', '', '', '',
+ 'ratename', 'varchar', '',$char_d, '', '',
+ 'agentnum', 'int', 'NULL', '', '', '',
+ 'default_detailnum', 'int', 'NULL', '', '', '',
],
'primary_key' => 'ratenum',
'unique' => [],
'ratedetailnum', 'serial', '', '', '', '',
'ratenum', 'int', '', '', '', '',
'orig_regionnum', 'int', 'NULL', '', '', '',
- 'dest_regionnum', 'int', '', '', '', '',
+ 'dest_regionnum', 'int', 'NULL', '', '', '',
'min_included', 'int', '', '', '', '',
'conn_charge', 'decimal', '', '10,4', '0.0000', '',
'conn_cost', 'decimal', '', '10,4', '0.0000', '',
'classnum', 'int', 'NULL', '', '', '',
'cdrtypenum', 'int', 'NULL', '', '', '',
'region_group', 'char', 'NULL', 1, '', '',
+ 'upstream_mult_charge', 'decimal', '', '10,4', '0.0000', '',
+ 'upstream_mult_cost', 'decimal', '', '10,4', '0.0000', '',
],
'primary_key' => 'ratedetailnum',
'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
#new
'cdrbatchnum', 'int', 'NULL', '', '', '',
+ # FK to cust_bill_pkg_detail; having a value here absolutely means
+ # that the CDR appears on an invoice
+ 'detailnum', 'bigint', 'NULL', '', '', '',
],
'primary_key' => 'acctid',
'unique' => [],
'columns' => [
'logcontextnum', 'serial', '', '', '', '',
'lognum', 'int', '', '', '', '',
- 'context', 'varchar', '', 32, '', '',
+ 'context', 'varchar', '', $char_d, '', '',
],
'primary_key' => 'logcontextnum',
'unique' => [ [ 'lognum', 'context' ] ],
'svc_alarm' => {
'columns' => [
- 'svcnum', 'int', '', '', '', '',
- 'alarmsystemnum', 'int', '', '', '', '',
- 'alarmtypenum', 'int', '', '', '', '',
- 'alarmstationnum', 'int', '', '', '', '',
- 'acctnum', 'varchar', '', $char_d, '', '',
- '_password', 'varchar', '', $char_d, '', '',
- 'location', 'varchar', 'NULL', $char_d, '', '',
+# name type null length default local
+ 'svcnum', 'int', '', '', '', '',
+ 'alarmsystemnum', 'int', '', '', '', '',
+ 'alarmtypenum', 'int', '', '', '', '',
+ 'alarmstationnum', 'int', '', '', '', '',
+ 'acctnum', 'varchar', '', $char_d, '', '',
+ '_password', 'varchar', '', $char_d, '', '',
+ 'location', 'varchar', 'NULL', $char_d, '', '',
+ 'cs_receiver', 'int', 'NULL', '', '', '',
+ 'cs_phonenum', 'varchar', 'NULL', $char_d, '', '',
+ 'serialnum', 'varchar', 'NULL', $char_d, '', '',
#installer (rep)
],
'primary_key' => 'svcnum',
'mac_addr', 'varchar', 'NULL', 12, '', '',
],
'primary_key' => 'svcnum',
- 'unique' => [],
+ 'unique' => [ ['serialnum'] , ['mac_addr'] ],
'index' => [],
'foreign_keys' => [
{ columns => [ 'svcnum' ],
'index' => [],
},
+ # eventually link to tower/sector?
+ 'deploy_zone' => {
+ 'columns' => [
+ 'zonenum', 'serial', '', '', '', '',
+ 'description', 'char', 'NULL', $char_d, '', '',
+ 'agentnum', 'int', '', '', '', '',
+ 'dbaname', 'char', 'NULL', $char_d, '', '',
+ 'zonetype', 'char', '', 1, '', '',
+ 'technology', 'int', '', '', '', '',
+ 'spectrum', 'int', 'NULL', '', '', '',
+ 'adv_speed_up', 'decimal', '', '10,3', '0', '',
+ 'adv_speed_down', 'decimal', '', '10,3', '0', '',
+ 'cir_speed_up', 'decimal', '', '10,3', '0', '',
+ 'cir_speed_down', 'decimal', '', '10,3', '0', '',
+ 'is_broadband', 'char', 'NULL', 1, '', '',
+ 'is_voice', 'char', 'NULL', 1, '', '',
+ 'is_consumer', 'char', 'NULL', 1, '', '',
+ 'is_business', 'char', 'NULL', 1, '', '',
+ 'active_date', @date_type, '', '',
+ 'expire_date', @date_type, '', '',
+ ],
+ 'primary_key' => 'zonenum',
+ 'unique' => [],
+ 'index' => [ [ 'agentnum' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'agentnum' ],
+ table => 'agent',
+ references => [ 'agentnum' ],
+ },
+ ],
+ },
+
+ 'deploy_zone_block' => {
+ 'columns' => [
+ 'blocknum', 'serial', '', '', '', '',
+ 'zonenum', 'int', '', '', '', '',
+ 'censusblock', 'char', '', 15, '', '',
+ 'censusyear', 'char', '', 4, '', '',
+ ],
+ 'primary_key' => 'blocknum',
+ 'unique' => [],
+ 'index' => [ [ 'zonenum' ] ],
+ 'foreign_keys' => [
+ { columns => [ 'zonenum' ],
+ table => 'deploy_zone',
+ references => [ 'zonenum' ],
+ },
+ ],
+ },
+
+ 'deploy_zone_vertex' => {
+ 'columns' => [
+ 'vertexnum', 'serial', '', '', '', '',
+ 'zonenum', 'int', '', '', '', '',
+ 'latitude', 'decimal', '', '10,7', '', '',
+ 'longitude', 'decimal', '', '10,7', '', '',
+ ],
+ 'primary_key' => 'vertexnum',
+ 'unique' => [ ],
+ 'index' => [ ],
+ 'foreign_keys' => [
+ { columns => [ 'zonenum' ],
+ table => 'deploy_zone',
+ references => [ 'zonenum' ],
+ },
+ ],
+ },
+
+
+
+
# name type nullability length default local
projects / freeside.git / blobdiff