X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=b6358b9bb9809baac96e890a5ff5098b7c66425f;hp=2b6dc6db24b6d5e83af092c9d67d32cc35719381;hb=908a4522939c0225ef4e7ba4236dab2eebe9daca;hpb=d7cf0d6bb3b81b1c91ef1bcc3252d56f96b65b0f diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 2b6dc6db2..b6358b9bb 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -243,6 +243,23 @@ sub dbdef_dist { ], }); + #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) @@ -539,12 +556,13 @@ sub tables_hashref { 'agent_type' => { 'columns' => [ - 'typenum', 'serial', '', '', '', '', - 'atype', 'varchar', '', $char_d, '', '', + 'typenum', 'serial', '', '', '', '', + 'atype', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'typenum', 'unique' => [], - 'index' => [], + 'index' => [ ['disabled'] ], }, 'type_pkgs' => { @@ -674,6 +692,8 @@ sub tables_hashref { '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 @@ -1009,7 +1029,8 @@ sub tables_hashref { '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 @@ -1021,7 +1042,7 @@ sub tables_hashref { 'accountcode', 'varchar', 'NULL', 20, '', '', 'startdate', @date_type, '', '', 'regionname', 'varchar', 'NULL', $char_d, '', '', - 'detail', 'varchar', '', 255, '', '', + 'detail', 'varchar', '', 255, '', '', ], 'primary_key' => 'detailnum', 'unique' => [], @@ -1505,6 +1526,27 @@ sub tables_hashref { ], }, + 'cust_credit_source_bill_pkg' => { + 'columns' => [ + 'creditsourcebillpkgnum', 'serial', '', '', '', '', + 'crednum', 'int', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'currency', 'char', 'NULL', 3, '', '', + ], + 'primary_key' => 'creditsourcebillpkgnum', + 'unique' => [], + 'index' => [ ['crednum'], ['billpkgnum'] ], + 'foreign_keys' => [ + { columns => ['billpkgnum'], + table => 'cust_bill_pkg', + }, + { columns => ['crednum'], + table => 'cust_credit', + }, + ], + }, + 'cust_main' => { 'columns' => [ 'custnum', 'serial', '', '', '', '', @@ -1608,6 +1650,7 @@ sub tables_hashref { 'message_noemail', 'char', 'NULL', 1, '', '', 'bill_locationnum', 'int', 'NULL', '', '', '', 'ship_locationnum', 'int', 'NULL', '', '', '', + 'taxstatusnum', 'char', 'NULL', 32, '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], @@ -1659,6 +1702,7 @@ sub tables_hashref { 'weight', 'int', '', '', '', '', 'payby', 'char', '', 4, '', '', 'payinfo', 'varchar', 'NULL', 512, '', '', + 'cardtype', 'varchar', 'NULL', $char_d, '', '', 'paycvv', 'varchar', 'NULL', 512, '', '', 'paymask', 'varchar', 'NULL', $char_d, '', '', #'paydate', @date_type, '', '', @@ -1794,10 +1838,11 @@ sub tables_hashref { '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', @@ -1805,6 +1850,9 @@ sub tables_hashref { { columns => [ 'custnum' ], table => 'cust_main', }, + { columns => [ 'refnum' ], + table => 'part_referral', + }, ], }, @@ -1822,7 +1870,7 @@ sub tables_hashref { ], 'primary_key' => 'quotationnum', 'unique' => [], - 'index' => [ [ 'prospectnum' ], ['custnum'], ], + 'index' => [ [ 'prospectnum' ], ['custnum'], ['disabled'], ], 'foreign_keys' => [ { columns => [ 'prospectnum' ], table => 'prospect_main', @@ -1889,6 +1937,7 @@ sub tables_hashref { '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, '', '', @@ -1907,6 +1956,7 @@ sub tables_hashref { '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', @@ -2136,7 +2186,7 @@ sub tables_hashref { '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 % @@ -2180,6 +2230,7 @@ sub tables_hashref { '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', @@ -2194,8 +2245,11 @@ sub tables_hashref { '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 @@ -2204,7 +2258,7 @@ sub tables_hashref { ], 'primary_key' => 'custlocationnum', 'unique' => [], - 'index' => [ [ 'zip', 'plus4lo', 'plus4hi' ] ], + 'index' => [ [ 'zip', 'plus4lo', 'plus4hi', 'ziphi', 'ziplo' ] ], }, 'tax_class' => { @@ -2219,6 +2273,18 @@ sub tables_hashref { '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', '', '', '', '', @@ -2249,7 +2315,9 @@ sub tables_hashref { ], 'primary_key' => 'paypendingnum', 'unique' => [ [ 'payunique' ] ], - 'index' => [ [ 'custnum' ], [ 'status' ], ], + 'index' => [ [ 'custnum' ], [ 'status' ], + ['paynum'], ['void_paynum'], ['jobnum'], ['invnum'], + ], 'foreign_keys' => [ { columns => [ 'custnum' ], table => 'cust_main', @@ -2587,8 +2655,9 @@ sub tables_hashref { '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'], @@ -3004,6 +3073,7 @@ sub tables_hashref { 'part_pkg' => { 'columns' => [ 'pkgpart', 'serial', '', '', '', '', + 'pkgpartbatch', 'varchar', 'NULL', $char_d, '', '', 'pkg', 'varchar', '', $char_d, '', '', 'comment', 'varchar', 'NULL', 2*$char_d, '', '', 'promo_code', 'varchar', 'NULL', $char_d, '', '', @@ -3030,6 +3100,7 @@ sub tables_hashref { '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', @@ -3288,6 +3359,7 @@ sub tables_hashref { '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' ] ], @@ -3669,20 +3741,27 @@ sub tables_hashref { 'suffix', 'varchar', 'NULL', $char_d, '', '', 'catchall', 'int', 'NULL', '', '', '', 'parent_svcnum', 'int', 'NULL', '', '', '', + 'quota', 'varchar', 'NULL', $char_d, '', '', + + #registration info 'registrarnum', 'int', 'NULL', '', '', '', 'registrarkey', 'varchar', 'NULL', 512, '', '', 'setup_date', @date_type, '', '', 'renewal_interval', 'int', 'NULL', '', '', '', 'expiration_date', @date_type, '', '', + + #some weird shit australia-specific shit? yuck.. seems totally unused 'au_registrant_name', 'varchar', 'NULL', $char_d, '', '', 'au_eligibility_type', 'varchar', 'NULL', $char_d, '', '', + #communigate pro fields (quota = MaxAccountSize) 'max_accounts', 'int', 'NULL', '', '', '', 'trailer', 'text', 'NULL', '', '', '', 'cgp_aliases', 'varchar', 'NULL', 255, '', '', 'cgp_accessmodes','varchar','NULL', 255, '', '', #DomainAccessModes 'cgp_certificatetype','varchar','NULL', $char_d, '', '', - #settings + + #(account default) settings 'acct_def_password_selfchange', 'char', 'NULL', 1, '', '', 'acct_def_password_recover', 'char', 'NULL', 1, 'Y', '', 'acct_def_cgp_accessmodes', 'varchar', 'NULL', 255, '', '', @@ -3695,7 +3774,8 @@ sub tables_hashref { 'acct_def_cgp_mailtoall', 'char', 'NULL', 1, '', '', 'acct_def_cgp_addmailtrailer', 'char', 'NULL', 1, '', '', 'acct_def_cgp_archiveafter', 'int', 'NULL', '', '', '', - #preferences + + #(account default) preferences 'acct_def_cgp_deletemode', 'varchar', 'NULL', $char_d, '', '', 'acct_def_cgp_emptytrash', 'varchar', 'NULL', $char_d, '', '', 'acct_def_cgp_language', 'varchar', 'NULL', $char_d, '', '', @@ -4128,9 +4208,9 @@ sub tables_hashref { [ 'job' ], [ 'svcnum' ], [ 'custnum' ], [ 'status' ], ], 'foreign_keys' => [ - { columns => [ 'svcnum' ], - table => 'cust_svc', - }, +# { columns => [ 'svcnum' ], +# table => 'cust_svc', +# }, { columns => [ 'custnum' ], table => 'cust_main', }, @@ -4196,6 +4276,7 @@ sub tables_hashref { 'exportsvcnum' => 'serial', '', '', '', '', 'exportnum' => 'int', '', '', '', '', 'svcpart' => 'int', '', '', '', '', + 'role' => 'varchar', 'NULL', 16, '', '', ], 'primary_key' => 'exportsvcnum', 'unique' => [ [ 'exportnum', 'svcpart' ] ], @@ -4785,9 +4866,10 @@ sub tables_hashref { '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' => [], @@ -4804,7 +4886,7 @@ sub tables_hashref { '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', '', @@ -4816,6 +4898,8 @@ sub tables_hashref { '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' ] ], @@ -5258,6 +5342,9 @@ sub tables_hashref { #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' => [], @@ -5591,6 +5678,8 @@ sub tables_hashref { 'max_simultaneous', 'int', 'NULL', '', '', '', 'e911_class', 'char', 'NULL', 1, '', '', 'e911_type', 'char', 'NULL', 1, '', '', + 'circuit_svcnum', 'int', 'NULL', '', '', '', + 'sip_server', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', 'unique' => [ [ 'sms_carrierid', 'sms_account'] ], @@ -5616,6 +5705,10 @@ sub tables_hashref { table => 'cdr_carrier', references => [ 'carrierid' ], }, + { columns => [ 'circuit_svcnum' ], + table => 'svc_circuit', + references => [ 'svcnum' ], + }, ], }, @@ -5819,6 +5912,7 @@ sub tables_hashref { 'disabled', 'char', 'NULL', 1, '', '', 'unsuspend_pkgpart', 'int', 'NULL', '', '', '', 'unsuspend_hold','char', 'NULL', 1, '', '', + 'unused_credit', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'reasonnum', 'unique' => [], @@ -5876,13 +5970,15 @@ sub tables_hashref { 'columns' => [ 'svcnum', 'int', '', '', '', '', 'id', 'int', 'NULL', '', '', '', + 'uuid', 'char', 'NULL', 36, '', '', 'title', 'varchar', 'NULL', $char_d, '', '', 'max_extensions', 'int', 'NULL', '', '', '', 'max_simultaneous', 'int', 'NULL', '', '', '', + 'ip_addr', 'varchar', 'NULL', 40, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], - 'index' => [ [ 'id' ] ], + 'index' => [ [ 'id' ], [ 'uuid' ] ], 'foreign_keys' => [ { columns => [ 'svcnum' ], table => 'cust_svc', @@ -6247,7 +6343,7 @@ sub tables_hashref { 'columns' => [ 'logcontextnum', 'serial', '', '', '', '', 'lognum', 'int', '', '', '', '', - 'context', 'varchar', '', 32, '', '', + 'context', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'logcontextnum', 'unique' => [ [ 'lognum', 'context' ] ], @@ -6261,13 +6357,17 @@ sub tables_hashref { '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', @@ -6351,7 +6451,7 @@ sub tables_hashref { 'mac_addr', 'varchar', 'NULL', 12, '', '', ], 'primary_key' => 'svcnum', - 'unique' => [], + 'unique' => [ ['serialnum'] , ['mac_addr'] ], 'index' => [], 'foreign_keys' => [ { columns => [ 'svcnum' ], @@ -6460,6 +6560,75 @@ sub tables_hashref { ], }, + 'circuit_type' => { + 'columns' => [ + 'typenum', 'serial', '', '', '', '', + 'typename', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + # speed? number of voice lines? anything else? + ], + 'primary_key' => 'typenum', + 'unique' => [ [ 'typename' ] ], + 'index' => [], + }, + + 'circuit_provider' => { + 'columns' => [ + 'providernum', 'serial', '', '', '', '', + 'provider', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'providernum', + 'unique' => [ [ 'provider' ], ], + 'index' => [], + }, + + 'circuit_termination' => { + 'columns' => [ + 'termnum', 'serial', '', '', '', '', + 'termination','varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'termnum', + 'unique' => [ [ 'termination' ] ], + 'index' => [], + }, + + 'svc_circuit' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'typenum', 'int', '', '', '', '', + 'providernum', 'int', '', '', '', '', + 'termnum', 'int', '', '', '', '', + 'circuit_id', 'varchar', '', 64, '', '', + 'desired_due_date', 'int', 'NULL', '', '', '', + 'due_date', 'int', 'NULL', '', '', '', + 'vendor_order_id', 'varchar', 'NULL', $char_d, '', '', + 'vendor_qual_id', 'varchar', 'NULL', $char_d, '', '', + 'vendor_order_type', 'varchar', 'NULL', $char_d, '', '', + 'vendor_order_status', 'varchar', 'NULL', $char_d, '', '', + 'endpoint_ip_addr', 'varchar', 'NULL', 40, '', '', + 'endpoint_mac_addr', 'varchar', 'NULL', 12, '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [], + 'index' => [ [ 'providernum' ], [ 'typenum' ] ], + 'foreign_keys' => [ + { columns => [ 'svcnum' ], + table => 'cust_svc', + }, + { columns => [ 'typenum' ], + table => 'circuit_type', + }, + { columns => [ 'providernum' ], + table => 'circuit_provider', + }, + { columns => [ 'termnum' ], + table => 'circuit_termination', + }, + ], + }, + 'vend_main' => { 'columns' => [ 'vendnum', 'serial', '', '', '', '', @@ -6682,11 +6851,12 @@ sub tables_hashref { 'zonetype', 'char', '', 1, '', '', 'technology', 'int', '', '', '', '', 'spectrum', 'int', 'NULL', '', '', '', - 'servicetype', 'char', '', '12', '', '', '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, '', '', @@ -6727,10 +6897,9 @@ sub tables_hashref { 'zonenum', 'int', '', '', '', '', 'latitude', 'decimal', '', '10,7', '', '', 'longitude', 'decimal', '', '10,7', '', '', - 'sequence', 'int', '', '', '', '', ], 'primary_key' => 'vertexnum', - 'unique' => [ [ 'zonenum', 'sequence' ] ], + 'unique' => [ ], 'index' => [ ], 'foreign_keys' => [ { columns => [ 'zonenum' ],