X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=1828cba8ba4220b0c81205f282d444eccd757a04;hp=bf4723ec4b8d3b892201058cde333f8de093f590;hb=7e0eb79c2efc03e27a4e8207b92526a2ac436602;hpb=85ef9f4033bd1f1214d7815960bc3f4d7d183192 diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index bf4723ec4..1828cba8b 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -204,7 +204,7 @@ sub dbdef_dist { my %h_indices = (); - unless ( $table eq 'cust_event' ) { #others? + unless ( $table eq 'cust_event' || $table eq 'cdr' ) { #others? my %indices = $tableobj->indices; @@ -448,7 +448,7 @@ sub tables_hashref { my $char_d = 80; #default maxlength for text fields #my(@date_type) = ( 'timestamp', '', '' ); - my @date_type = ( 'int', 'NULL', '' ); + my @date_type = ( 'int', 'NULL', '' ); my @perl_type = ( 'text', 'NULL', '' ); my @money_type = ( 'decimal', '', '10,2' ); my @money_typen = ( 'decimal', 'NULL', '10,2' ); @@ -570,6 +570,7 @@ sub tables_hashref { '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 @@ -761,6 +762,19 @@ sub tables_hashref { ], }, + 'cust_event_fee' => { + 'columns' => [ + 'eventfeenum', 'serial', '', '', '', '', + 'eventnum', 'int', '', '', '', '', + 'billpkgnum', 'int', 'NULL', '', '', '', + 'feepart', 'int', '', '', '', '', + 'nextbill', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'eventfeenum', # I'd rather just use eventnum + 'unique' => [ [ 'billpkgnum' ], [ 'eventnum' ] ], # one-to-one link + 'index' => [ [ 'feepart' ] ], + }, + 'cust_bill_pkg' => { 'columns' => [ 'billpkgnum', 'serial', '', '', '', '', @@ -779,6 +793,7 @@ sub tables_hashref { 'unitsetup', @money_typen, '', '', 'unitrecur', @money_typen, '', '', 'hidden', 'char', 'NULL', 1, '', '', + 'feepart', 'int', 'NULL', '', '', '', ], 'primary_key' => 'billpkgnum', 'unique' => [], @@ -787,7 +802,7 @@ sub tables_hashref { 'cust_bill_pkg_detail' => { 'columns' => [ - 'detailnum', 'serial', '', '', '', '', + 'detailnum', 'serial', '', '', '', '', 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable 'pkgnum', 'int', 'NULL', '', '', '', # deprecated 'invnum', 'int', 'NULL', '', '', '', # deprecated @@ -808,7 +823,7 @@ sub tables_hashref { 'cust_bill_pkg_display' => { 'columns' => [ - 'billpkgdisplaynum', 'serial', '', '', '', '', + 'billpkgdisplaynum', 'serial', '', '', '', '', 'billpkgnum', 'int', '', '', '', '', 'section', 'varchar', 'NULL', $char_d, '', '', #'unitsetup', @money_typen, '', '', #override the linked real one? @@ -822,6 +837,22 @@ sub tables_hashref { 'index' => [ ['billpkgnum'], ], }, + 'cust_bill_pkg_fee' => { + 'columns' => [ + 'billpkgfeenum', 'serial', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'base_invnum', 'int', '', '', '', '', + 'base_billpkgnum', 'int', 'NULL', '', '', '', + 'amount', @money_type, '', '', + ], + 'primary_key' => 'billpkgfeenum', + 'unique' => [], + 'index' => [ ['billpkgnum'], + ['base_invnum'], + ['base_billpkgnum'], + ], + }, + 'cust_bill_pkg_tax_location' => { 'columns' => [ 'billpkgtaxlocationnum', 'serial', '', '', '', '', @@ -973,6 +1004,37 @@ sub tables_hashref { 'commission_agentnum', 'int', 'NULL', '', '', '', # 'commission_salesnum', 'int', 'NULL', '', '', '', # 'commission_pkgnum', 'int', 'NULL', '', '', '', # + 'credbatch', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'crednum', + 'unique' => [], + 'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'], + ['commission_salesnum'], ['credbatch'], + ], + }, + + 'cust_credit_void' => { + 'columns' => [ + 'crednum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'amount',@money_type, '', '', + 'currency', 'char', 'NULL', 3, '', '', + 'otaker', 'varchar', 'NULL', 32, '', '', + 'usernum', 'int', 'NULL', '', '', '', + 'reason', 'text', 'NULL', '', '', '', + 'reasonnum', 'int', 'NULL', '', '', '', + 'addlinfo', 'text', 'NULL', '', '', '', + 'closed', 'char', 'NULL', 1, '', '', + 'pkgnum', 'int', 'NULL', '', '','', + 'eventnum', 'int', 'NULL', '', '','', + 'commission_agentnum', 'int', 'NULL', '', '', '', + 'commission_salesnum', 'int', 'NULL', '', '', '', + 'commission_pkgnum', 'int', 'NULL', '', '', '', + #void fields + 'void_date', @date_type, '', '', + 'void_reason', 'varchar', 'NULL', $char_d, '', '', + 'void_usernum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'crednum', 'unique' => [], @@ -981,6 +1043,7 @@ sub tables_hashref { ], }, + 'cust_credit_bill' => { 'columns' => [ 'creditbillnum', 'serial', '', '', '', '', @@ -1033,8 +1096,10 @@ sub tables_hashref { 'stateid_state', 'varchar', 'NULL', $char_d, '', '', 'national_id', 'varchar', 'NULL', $char_d, '', '', 'birthdate' ,@date_type, '', '', - 'spouse_birthdate' ,@date_type, '', '', - 'anniversary_date' ,@date_type, '', '', + 'spouse_last', 'varchar', 'NULL', 2*$char_d, '', '', + 'spouse_first', 'varchar', 'NULL', $char_d, '', '', + 'spouse_birthdate', @date_type, '', '', + 'anniversary_date', @date_type, '', '', 'signupdate',@date_type, '', '', 'dundate', @date_type, '', '', 'company', 'varchar', 'NULL', $char_d, '', '', @@ -1122,6 +1187,8 @@ sub tables_hashref { [ 'referral_custnum' ], [ 'payby' ], [ 'paydate' ], [ 'archived' ], + [ 'ship_locationnum' ], + [ 'bill_locationnum' ], ], }, @@ -1175,7 +1242,10 @@ sub tables_hashref { 'first', 'varchar', '', $char_d, '', '', 'title', 'varchar', 'NULL', $char_d, '', '', #eg Head Bottle Washer 'comment', 'varchar', 'NULL', 255, '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'selfservice_access', 'char', 'NULL', 1, '', '', + '_password', 'varchar', 'NULL', $char_d, '', '', + '_password_encoding', 'varchar', 'NULL', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'contactnum', 'unique' => [], @@ -1217,7 +1287,8 @@ sub tables_hashref { 'emailaddress', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'contactemailnum', - 'unique' => [ [ 'contactnum', 'emailaddress' ], ], + #'unique' => [ [ 'contactnum', 'emailaddress' ], ], + 'unique' => [ [ 'emailaddress' ], ], 'index' => [], }, @@ -1325,6 +1396,21 @@ sub tables_hashref { 'index' => [ ['custnum'], ], }, + 'cust_main_credit_limit' => { + 'columns' => [ + 'creditlimitnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'amount', @money_typen, '', '', + #'amount_currency', 'char', 'NULL', 3, '', '', + 'credit_limit', @money_typen, '', '', + #'credit_limit_currency', 'char', 'NULL', 3, '', '', + ], + 'primary_key' => 'creditlimitnum', + 'unique' => [], + 'index' => [ ['custnum'], ], + }, + 'cust_main_note' => { 'columns' => [ 'notenum', 'serial', '', '', '', '', @@ -1557,11 +1643,12 @@ sub tables_hashref { 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'status', 'varchar', '', $char_d, '', '', - 'session_id', 'varchar', 'NULL', $char_d, '', '', #only need 32 + 'session_id', 'varchar', 'NULL', 1024, '', '', # SHA-512-hex 'statustext', 'text', 'NULL', '', '', '', 'gatewaynum', 'int', 'NULL', '', '', '', #'cust_balance', @money_type, '', '', 'paynum', 'int', 'NULL', '', '', '', + 'void_paynum', 'int', 'NULL', '', '', '', 'jobnum', 'bigint', 'NULL', '', '', '', 'invnum', 'int', 'NULL', '', '', '', 'manual', 'char', 'NULL', 1, '', '', @@ -1782,7 +1869,7 @@ sub tables_hashref { 'manual_flag', 'char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'quantity', 'int', 'NULL', '', '', '', - 'agent_pkgid', 'int', 'NULL', '', '', '', + 'agent_pkgid', 'varchar', 'NULL', $char_d, '', '', 'waive_setup', 'char', 'NULL', 1, '', '', 'recur_show_zero', 'char', 'NULL', 1, '', '', 'setup_show_zero', 'char', 'NULL', 1, '', '', @@ -1793,8 +1880,17 @@ 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'], ['no_auto'], + ['last_bill'], ['susp'], ['adjourn'], ['resume'], + ['cancel'], ['expire'], ['contract_end'], + ['change_date'], + ['no_auto'], + #['contactnum'], + ['salesnum'], + #['uncancel_pkgnum'], + #['change_pkgnum'], ['change_locationnum'], + #['change_custnum'], + ['main_pkgnum'], + #['pkglinknum'], ['change_to_pkgnum'], ], }, @@ -1858,7 +1954,7 @@ sub tables_hashref { 'columns' => [ 'pkgusagenum', 'serial', '', '', '', '', 'pkgnum', 'int', '', '', '', '', - 'minutes', 'int', '', '', '', '', + 'minutes', 'double precision', '', '', '', '', 'pkgusagepart', 'int', '', '', '', '', ], 'primary_key' => 'pkgusagenum', @@ -1871,7 +1967,7 @@ sub tables_hashref { 'cdrusagenum', 'bigserial', '', '', '', '', 'acctid', 'bigint', '', '', '', '', 'pkgusagenum', 'int', '', '', '', '', - 'minutes', 'int', '', '', '', '', + 'minutes', 'double precision', '', '', '', '', ], 'primary_key' => 'cdrusagenum', 'unique' => [], @@ -1908,6 +2004,7 @@ sub tables_hashref { 'columns' => [ 'discountnum', 'serial', '', '', '', '', #'agentnum', 'int', 'NULL', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', 'name', 'varchar', 'NULL', $char_d, '', '', 'amount', @money_type, '', '', 'percent', 'decimal', '', '7,4', '', '', @@ -1921,6 +2018,18 @@ sub tables_hashref { 'index' => [], # [ 'agentnum' ], ], }, + 'discount_class' => { + 'columns' => [ + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + #'categorynum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'classnum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, + 'cust_refund' => { 'columns' => [ 'refundnum', 'serial', '', '', '', '', @@ -2015,7 +2124,7 @@ sub tables_hashref { 'columns' => [ 'pkgpart', 'serial', '', '', '', '', 'pkg', 'varchar', '', $char_d, '', '', - 'comment', 'varchar', '', 2*$char_d, '', '', + 'comment', 'varchar', 'NULL', 2*$char_d, '', '', 'promo_code', 'varchar', 'NULL', $char_d, '', '', 'freq', 'varchar', '', $char_d, '', '', #billing frequency 'setuptax', 'char', 'NULL', 1, '', '', @@ -2040,11 +2149,12 @@ sub tables_hashref { 'successor', 'int', 'NULL', '', '', '', 'family_pkgpart','int', 'NULL', '', '', '', 'delay_start', 'int', 'NULL', '', '', '', + 'agent_pkgpartid', 'varchar', 'NULL', 20, '', '', ], 'primary_key' => 'pkgpart', 'unique' => [], 'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'classnum' ], - [ 'agentnum' ], ['no_auto'], + [ 'agentnum' ], ['no_auto'], ['agent_pkgpartid'], ], }, @@ -2061,6 +2171,59 @@ sub tables_hashref { 'index' => [], }, + 'part_fee' => { + 'columns' => [ + 'feepart', 'serial', '', '', '', '', + 'itemdesc', 'varchar', '', $char_d, '', '', + 'comment', 'varchar', 'NULL', 2*$char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'classnum', 'int', 'NULL', '', '', '', + 'taxclass', 'varchar', 'NULL', $char_d, '', '', + 'taxproductnum', 'int', 'NULL', '', '', '', + 'pay_weight', 'real', 'NULL', '', '', '', + 'credit_weight', 'real', 'NULL', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'amount', @money_type, '', '', + 'percent', 'decimal', '', '7,4', '', '', + 'basis', 'varchar', '', 16, '', '', + 'minimum', @money_typen, '', '', + 'maximum', @money_typen, '', '', + 'limit_credit', 'char', 'NULL', 1, '', '', + 'setuprecur', 'char', '', 5, '', '', + 'taxable', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'feepart', + 'unique' => [], + 'index' => [ [ 'disabled' ], [ 'classnum' ], [ 'agentnum' ] + ], + }, + + 'part_fee_msgcat' => { + 'columns' => [ + 'feepartmsgnum', 'serial', '', '', '', '', + 'feepart', 'int', '', '', '', '', + 'locale', 'varchar', '', 16, '', '', + 'itemdesc', 'varchar', '', $char_d, '', '', #longer/no limit? + 'comment', 'varchar', 'NULL', 2*$char_d, '', '', #longer/no limit? + ], + 'primary_key' => 'feepartmsgnum', + 'unique' => [ [ 'feepart', 'locale' ] ], + 'index' => [], + }, + + 'part_fee_usage' => { + 'columns' => [ + 'feepartusagenum','serial', '', '', '', '', + 'feepart', 'int', '', '', '', '', + 'classnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'percent', 'decimal', '', '7,4', '', '', + ], + 'primary_key' => 'feepartusagenum', + 'unique' => [ [ 'feepart', 'classnum' ] ], + 'index' => [], + }, + 'part_pkg_link' => { 'columns' => [ 'pkglinknum', 'serial', '', '', '', '', @@ -2654,7 +2817,7 @@ sub tables_hashref { ], 'primary_key' => 'prepaynum', 'unique' => [ ['identifier'] ], - 'index' => [], + 'index' => [ ['agentnum'] ], }, 'port' => { @@ -2740,6 +2903,21 @@ sub tables_hashref { 'index' => [ [ 'jobnum' ], [ 'depend_jobnum' ] ], }, + 'queue_stat' => { + 'columns' => [ + 'statnum', 'bigserial', '', '', '', '', + 'jobnum', 'bigint', '', '', '', '', + 'job', 'varchar', '', 512, '', '', + 'custnum', 'int', 'NULL', '', '', '', + 'insert_date', @date_type, '', '', + 'start_date', @date_type, '', '', + 'end_date', @date_type, '', '', + ], + 'primary_key' => 'statnum', + 'unique' => [], #[ ['jobnum'] ], + 'index' => [], + }, + 'export_svc' => { 'columns' => [ 'exportsvcnum' => 'serial', '', '', '', '', @@ -2944,6 +3122,18 @@ sub tables_hashref { 'index' => [], }, + 'addr_range' => { + 'columns' => [ + 'rangenum', 'serial', '', '', '', '', + 'start', 'varchar', '', 15, '', '', + 'length', 'int', '', '', '', '', + 'status', 'varchar', 'NULL', 32, '', '', + ], + 'primary_key' => 'rangenum', + 'unique' => [], + 'index' => [], + }, + 'svc_broadband' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', @@ -2968,6 +3158,7 @@ sub tables_hashref { 'rssi', 'int', 'NULL', '', '', '', 'suid', 'int', 'NULL', '', '', '', 'shared_svcnum', 'int', 'NULL', '', '', '', + 'serviceid', 'varchar', 'NULL', 64, '', '',#srvexport/reportfields ], 'primary_key' => 'svcnum', 'unique' => [ [ 'ip_addr' ], [ 'mac_addr' ] ], @@ -2976,14 +3167,17 @@ sub tables_hashref { 'tower' => { 'columns' => [ - 'towernum', 'serial', '', '', '', '', - #'agentnum', 'int', 'NULL', '', '', '', - 'towername', 'varchar', '', $char_d, '', '', - 'disabled', 'char', 'NULL', 1, '', '', - 'latitude', 'decimal', 'NULL', '10,7', '', '', - 'longitude','decimal', 'NULL', '10,7', '', '', - 'altitude', 'decimal', 'NULL', '', '', '', - 'coord_auto', 'char', 'NULL', 1, '', '', + 'towernum', 'serial', '', '', '', '', + #'agentnum', 'int', 'NULL', '', '', '', + 'towername', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'latitude', 'decimal', 'NULL', '10,7', '', '', + 'longitude', 'decimal', 'NULL', '10,7', '', '', + 'coord_auto', 'char', 'NULL', 1, '', '', + 'altitude', 'decimal', 'NULL', '', '', '', + 'height', 'decimal', 'NULL', '', '', '', + 'veg_height', 'decimal', 'NULL', '', '', '', + 'color', 'varchar', 'NULL', 6, '', '', ], 'primary_key' => 'towernum', 'unique' => [ [ 'towername' ] ], # , 'agentnum' ] ], @@ -2992,10 +3186,16 @@ sub tables_hashref { 'tower_sector' => { 'columns' => [ - 'sectornum', 'serial', '', '', '', '', - 'towernum', 'int', '', '', '', '', - 'sectorname', 'varchar', '', $char_d, '', '', - 'ip_addr', 'varchar', 'NULL', 15, '', '', + 'sectornum', 'serial', '', '', '', '', + 'towernum', 'int', '', '', '', '', + 'sectorname', 'varchar', '', $char_d, '', '', + 'ip_addr', 'varchar', 'NULL', 15, '', '', + 'height', 'decimal', 'NULL', '', '', '', + 'freq_mhz', 'int', 'NULL', '', '', '', + 'direction', 'int', 'NULL', '', '', '', + 'width', 'int', 'NULL', '', '', '', + #downtilt etc? rfpath has profile files for devices/antennas you upload? + 'sector_range', 'decimal', 'NULL', '', '', '', #? ], 'primary_key' => 'sectornum', 'unique' => [ [ 'towernum', 'sectorname' ], [ 'ip_addr' ], ], @@ -3110,7 +3310,7 @@ sub tables_hashref { 'columns' => [ 'pkgusagepart', 'serial', '', '', '', '', 'pkgpart', 'int', '', '', '', '', - 'minutes', 'int', '', '', '', '', + 'minutes', 'double precision', '', '', '', '', 'priority', 'int', 'NULL', '', '', '', 'shared', 'char', 'NULL', 1, '', '', 'rollover', 'char', 'NULL', 1, '', '', @@ -3132,10 +3332,23 @@ sub tables_hashref { 'index' => [], }, + 'part_pkg_fcc_option' => { + 'columns' => [ + 'num', 'serial', '', '', '', '', + 'fccoptionname', 'varchar', '', $char_d, '', '', + 'pkgpart', 'int', '', '', '', '', + 'optionvalue', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'num', + 'unique' => [ [ 'fccoptionname', 'pkgpart' ] ], + 'index' => [], + }, + 'rate' => { 'columns' => [ - 'ratenum', 'serial', '', '', '', '', - 'ratename', 'varchar', '', $char_d, '', '', + 'ratenum', 'serial', '', '', '', '', + 'ratename', 'varchar', '', $char_d, '', '', + 'agentnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'ratenum', 'unique' => [], @@ -3150,13 +3363,15 @@ sub tables_hashref { 'dest_regionnum', 'int', '', '', '', '', 'min_included', 'int', '', '', '', '', 'conn_charge', 'decimal', '', '10,4', '0.0000', '', + 'conn_cost', 'decimal', '', '10,4', '0.0000', '', 'conn_sec', 'int', '', '', '0', '', 'min_charge', 'decimal', '', '10,5', '', '', + 'min_cost', 'decimal', '', '10,5','0.00000', '', 'sec_granularity', 'int', '', '', '', '', 'ratetimenum', 'int', 'NULL', '', '', '', 'classnum', 'int', 'NULL', '', '', '', 'cdrtypenum', 'int', 'NULL', '', '', '', - 'region_group', 'char', 'NULL', 1, '', '', + 'region_group', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'ratedetailnum', 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ], @@ -3358,11 +3573,12 @@ sub tables_hashref { 'pkg_category' => { 'columns' => [ - 'categorynum', 'serial', '', '', '', '', - 'categoryname', 'varchar', '', $char_d, '', '', - 'weight', 'int', 'NULL', '', '', '', - 'condense', 'char', 'NULL', 1, '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'categorynum', 'serial', '', '', '', '', + 'categoryname', 'varchar', '', $char_d, '', '', + 'weight', 'int', 'NULL', '', '', '', + 'ticketing_queueid', 'int', 'NULL', '', '', '', + 'condense', 'char', 'NULL', 1, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'categorynum', 'unique' => [], @@ -3406,6 +3622,10 @@ sub tables_hashref { 'src_ip_addr', 'varchar', 'NULL', 15, '', '', 'dst_ip_addr', 'varchar', 'NULL', 15, '', '', + #currently only u4: + # terminating number (as opposed to dialed destination) + 'dst_term', 'varchar', 'NULL', $char_d, '', '', + #these don't seem to be logged by most of the SQL cdr_* modules #except tds under sql-illegal names, so; # ... don't rely on them for rating? @@ -3515,7 +3735,7 @@ sub tables_hashref { [ 'sessionnum' ], [ 'subscriber' ], [ 'freesidestatus' ], [ 'freesiderewritestatus' ], [ 'cdrbatch' ], [ 'cdrbatchnum' ], - [ 'src_ip_addr' ], [ 'dst_ip_addr' ], + [ 'src_ip_addr' ], [ 'dst_ip_addr' ], [ 'dst_term' ], ], }, @@ -3631,13 +3851,14 @@ sub tables_hashref { 'access_user' => { 'columns' => [ - 'usernum', 'serial', '', '', '', '', - 'username', 'varchar', '', $char_d, '', '', - '_password', 'varchar', '', $char_d, '', '', - 'last', 'varchar', '', $char_d, '', '', - 'first', 'varchar', '', $char_d, '', '', - 'user_custnum', 'int', 'NULL', '', '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'usernum', 'serial', '', '', '', '', + 'username', 'varchar', '', $char_d, '', '', + '_password', 'varchar', '', $char_d, '', '', + 'last', 'varchar', '', $char_d, '', '', + 'first', 'varchar', '', $char_d, '', '', + 'user_custnum', 'int', 'NULL', '', '', '', + 'report_salesnum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'usernum', 'unique' => [ [ 'username' ] ], @@ -3701,6 +3922,44 @@ sub tables_hashref { 'index' => [], }, + 'sched_item' => { + 'columns' => [ + 'itemnum', 'serial', '', '', '', '', + 'usernum', 'int', 'NULL', '', '', '', + #'itemname', 'varchar', $char_d, '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'itemnum', + 'unique' => [ [ 'usernum' ] ], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'usernum' ], + table => 'access_user', + }, + ], + }, + + #'sched_item_class' + + 'sched_avail' => { + 'columns' => [ + 'availnum', 'serial', '', '', '', '', + 'itemnum', 'int', '', '', '', '', + 'wday', 'int', '', '', '', '', + 'stime', 'int', '', '', '', '', + 'etime', 'int', '', '', '', '', + 'override_date', @date_type, '', '', + ], + 'primary_key' => 'availnum', + 'unique' => [], + 'index' => [], + 'foreign_keys' => [ + { columns => [ 'itemnum' ], + table => 'sched_item', + }, + ], + }, + 'svc_phone' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', @@ -3726,6 +3985,8 @@ sub tables_hashref { 'sms_carrierid', 'int', 'NULL', '', '', '', 'sms_account', 'varchar', 'NULL', $char_d, '', '', 'max_simultaneous', 'int', 'NULL', '', '', '', + 'e911_class', 'char', 'NULL', 1, '', '', + 'e911_type', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'svcnum', 'unique' => [ [ 'sms_carrierid', 'sms_account'] ], @@ -3921,6 +4182,25 @@ sub tables_hashref { 'index' => [ [ 'id' ] ], }, + 'pbx_extension' => { + 'columns' => [ + 'extensionnum', 'serial', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'extension', 'varchar', '', $char_d, '', '', + 'pin', 'varchar', 'NULL', $char_d, '', '', + 'sip_password', 'varchar', 'NULL', $char_d, '', '', + 'phone_name', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'extensionnum', + 'unique' => [ [ 'svcnum', 'extension' ] ], + 'index' => [ [ 'svcnum' ] ], + 'foreign_keys' => [ + { columns => [ 'svcnum' ], + table => 'svc_pbx', + }, + ], + }, + 'svc_mailinglist' => { #svc_group? 'columns' => [ 'svcnum', 'int', '', '', '', '', @@ -4028,7 +4308,7 @@ sub tables_hashref { 'cust_msg' => { 'columns' => [ 'custmsgnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', 'msgnum', 'int', 'NULL', '', '', '', '_date', @date_type, '', '', 'env_from', 'varchar', 'NULL', 255, '', '', @@ -4037,6 +4317,7 @@ sub tables_hashref { 'body', 'blob', 'NULL', '', '', '', 'error', 'varchar', 'NULL', 255, '', '', 'status', 'varchar', '',$char_d, '', '', + 'msgtype', 'varchar', 'NULL', 16, '', '', ], 'primary_key' => 'custmsgnum', 'unique' => [ ], @@ -4096,7 +4377,7 @@ sub tables_hashref { 'statustext', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'upgradenum', - 'unique' => [ [ 'upgradenum' ] ], + 'unique' => [], 'index' => [ [ 'upgrade' ] ], }, @@ -4137,7 +4418,7 @@ sub tables_hashref { 'columns' => [ 'logcontextnum', 'serial', '', '', '', '', 'lognum', 'int', '', '', '', '', - 'context', 'varchar', '', 32, '', '', + 'context', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'logcontextnum', 'unique' => [ [ 'lognum', 'context' ] ], @@ -4147,19 +4428,56 @@ sub tables_hashref { 'svc_alarm' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', - 'alarm_system', 'varchar', '', $char_d, '', '', # dropdowns? - 'alarm_type', 'varchar', '', $char_d, '', '', # + 'alarmsystemnum', 'int', '', '', '', '', + 'alarmtypenum', 'int', '', '', '', '', + 'alarmstationnum', 'int', '', '', '', '', 'acctnum', 'varchar', '', $char_d, '', '', '_password', 'varchar', '', $char_d, '', '', 'location', 'varchar', 'NULL', $char_d, '', '', - #cs - #rep + #installer (rep) ], 'primary_key' => 'svcnum', - 'unique' => [], #system/type/acctnum?? + 'unique' => [], 'index' => [], }, + 'alarm_system' => { #vendors + 'columns' => [ + 'alarmsystemnum', 'serial', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'systemname', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'alarmsystemnum', + 'unique' => [ ['agentnum', 'systemname'] ], + 'index' => [ ['agentnum'], ['disabled'] ], + }, + + 'alarm_type' => { #inputs and outputs + 'columns' => [ + 'alarmtypenum', 'serial', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'inputs', 'int', '', '', '', '', + 'outputs', 'int', '', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'alarmtypenum', + 'unique' => [ ['agentnum', 'inputs', 'outputs'] ], + 'index' => [ ['agentnum'], ['disabled'] ], + }, + + 'alarm_station' => { #central station (where the alarm reports to) + 'columns' => [ + 'alarmstationnum', 'serial', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'stationname', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'alarmstationnum', + 'unique' => [ ['agentnum', 'stationname'], ], + 'index' => [ ['agentnum'], ['disabled'] ], + }, + 'svc_cable' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', @@ -4170,7 +4488,7 @@ sub tables_hashref { 'mac_addr', 'varchar', 'NULL', 12, '', '', ], 'primary_key' => 'svcnum', - 'unique' => [], + 'unique' => [ ['serialnum'] , ['mac_addr'] ], 'index' => [], }, @@ -4246,21 +4564,9 @@ sub tables_hashref { 'latexnotes', 'text', 'NULL', '', '', '', 'latexfooter', 'text', 'NULL', '', '', '', 'latexsummary', 'text', 'NULL', '', '', '', - 'latexcoupon', 'text', 'NULL', '', '', '', 'latexsmallfooter', 'text', 'NULL', '', '', '', 'latexreturnaddress', 'text', 'NULL', '', '', '', - 'latextopmargin', 'varchar', 'NULL', 16, '', '', - 'latexheadsep', 'varchar', 'NULL', 16, '', '', - 'latexaddresssep', 'varchar', 'NULL', 16, '', '', - 'latextextheight', 'varchar', 'NULL', 16, '', '', - 'latexextracouponspace','varchar', 'NULL', 16, '', '', - 'latexcouponfootsep', 'varchar', 'NULL', 16, '', '', - 'latexcouponamountenclosedsep', 'varchar', 'NULL', 16, '', '', - 'latexcoupontoaddresssep', 'varchar', 'NULL', 16, '', '', - 'latexverticalreturnaddress', 'char', 'NULL', 1, '', '', - 'latexcouponaddcompanytoaddress', 'char', 'NULL', 1, '', '', - 'logo_png', 'blob', 'NULL', '', '', '', - 'logo_eps', 'blob', 'NULL', '', '', '', + 'with_latexcoupon', 'char', 'NULL', '1', '', '', 'lpr', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'confnum', @@ -4268,6 +4574,130 @@ sub tables_hashref { 'index' => [ ], }, + 'export_batch' => { + 'columns' => [ + 'batchnum', 'serial', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + '_date', 'int', '', '', '', '', + 'status', 'varchar', 'NULL', 32, '', '', + 'statustext', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'batchnum', + 'unique' => [], + 'index' => [ [ 'exportnum' ], [ 'status' ] ], + 'foreign_keys' => [ + { columns => [ 'exportnum' ], + table => 'part_export', + references => [ 'exportnum' ] + }, + ], + }, + + 'export_batch_item' => { + 'columns' => [ + 'itemnum', 'serial', '', '', '', '', + 'batchnum', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'action', 'varchar', '', 32, '', '', + 'data', 'text', 'NULL', '', '', '', + 'frozen', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'itemnum', + 'unique' => [], + 'index' => [ [ 'batchnum' ], [ 'svcnum' ] ], + 'foreign_keys' => [ + { columns => [ 'batchnum' ], + table => 'export_batch', + references => [ 'batchnum' ] + }, + ], + }, + + # lookup table for states, similar to msa and lata + 'state' => { + 'columns' => [ + 'statenum', 'int', '', '', '', '', + 'country', 'char', '', 2, '', '', + 'state', 'char', '', $char_d, '', '', + 'fips', 'char', '', 3, '', '', + ], + 'primary_key' => 'statenum', + 'unique' => [ [ 'country', 'state' ], ], + '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 #'new_table' => {