X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=ecfb0e4d270264b2e8166d43b7d6b84ee3b9ee92;hp=d73b2822822f926ca95404caf643b0241fb75f6b;hb=e3a2ca0a7ef6e0931283136321ba8c1494b37c19;hpb=20f38e4c256094bd938b732950e8f93319f851a5 diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index d73b28228..ecfb0e4d2 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -184,11 +184,15 @@ sub dbdef_dist { # )); #} + my $tables_hashref_torrus = tables_hashref_torrus(); + #create history tables (false laziness w/create-history-tables) foreach my $table ( - grep { ! /^clientapi_session/ } - grep { ! /^h_/ } - $dbdef->tables + grep { ! /^clientapi_session/ + && ! /^h_/ + && ! $tables_hashref_torrus->{$_} + } + $dbdef->tables ) { my $tableobj = $dbdef->table($table) or die "unknown table $table"; @@ -325,6 +329,101 @@ sub dbdef_dist { } +#torrus tables http://torrus.org/reporting_setup.pod.html#create_sql_tables +sub tables_hashref_torrus { + + return { + + # Collector export table. It usually grows at several megabytes + # per month, and is updated every 5 minutes + 'srvexport' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'srv_date', 'date', '', '', '', '',#date and time of the data sample + 'srv_time', 'time', '', '', '', '', + 'serviceid', 'varchar', '', 64, '', '',#unique service ID per counter + 'value', 'double precision', '', '', '', '',#collected rate or gauge value + 'intvl', 'int', '', '', '', '', # collection interval - for counter volume calculation + ], + 'primary_key' => 'id', + 'unique' => [], + 'index' => [ ['srv_date'], ['srv_date', 'srv_time'], ['serviceid'], ], + }, + + #Tables for (currently monthly only) report contents. + #These are updated usually once per month, and read at the moment of + #rendering the report output (HTML now, PDF or XML or Excel or whatever + #in the future) + + #DBIx::Sequence backend, theplatform-independent inplementation + #of sequences + 'dbix_sequence_state' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'dataset', 'varchar', '', 50, '', '', + 'state_id', 'int', '', '', '', '', + ], + 'primary_key' => 'id', + #CONSTRAINT pk_dbix_sequence PRIMARY KEY (dataset, state_id) + 'unique' => [ [ 'dataset', 'state_id' ], ], + 'index' => [], + }, + + 'dbix_sequence_release' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'dataset', 'varchar', '', 50, '', '', + 'released_id', 'int', '', '', '', '', + ], + 'primary_key' => 'id', + #CONSTRAINT pk_dbi_release PRIMARY KEY (dataset, released_id) + 'unique' => [ [ 'dataset', 'released_id', ] ], + 'index' => [], + }, + + #Each report is characterized by name, date and time. + #Monthly reports are automatically assigned 00:00 of the 1st day + #in the month. The report contains fields for every service ID + #defined across all datasource trees. + 'reports' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'rep_date', 'date', '', '', '', '',#Start date of the report + 'rep_time', 'time', '', '', '', '',#Start time of the report + 'reportname', 'varchar', '', 64, '', '',#Report name, such as + # MonthlyUsage + 'iscomplete', 'int', '', '', '', '',#0 when the report is in + # progress, 1 when it is ready + ], + 'primary_key' => 'id', + 'unique' => [ [ qw(rep_date rep_time reportname) ] ], + 'index' => [ [ 'rep_date' ] ], + }, + + #Each report contains fields. For each service ID, + #the report may contain several fields for various statistics. + #Each field contains information about the units of the value it + #contains + 'reportfields' => { + 'columns' => [ + 'id', 'serial', '', '', '', '', + 'rep_id', 'int', 'NULL', '', '', '', + 'name', 'varchar', '', 64, '', '',#name of the field, + # such as AVG or MAX + 'serviceid', 'varchar', '', 64, '', '',#service ID + 'value', 'double precision', '', '', '', '',#Numeric value + 'units', 'varchar', '', 64, \"''", '',#Units, such as bytes + # or Mbps + ], + 'primary_key', => 'id', + 'unique' => [ [ qw(rep_id name serviceid) ] ], + 'index' => [], + }, + + }; + +} + sub tables_hashref { my $char_d = 80; #default maxlength for text fields @@ -352,8 +451,8 @@ sub tables_hashref { '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 + 'username', 'varchar', 'NULL', $char_d, '', '', + '_password', 'varchar', 'NULL', $char_d, '', '', 'freq', 'int', 'NULL', '', '', '', #deprecated (never used) 'prog', @perl_type, '', '', #deprecated (never used) ], @@ -570,6 +669,7 @@ sub tables_hashref { 'itemdesc', 'varchar', 'NULL', $char_d, '', '', 'itemcomment', 'varchar', 'NULL', $char_d, '', '', 'section', 'varchar', 'NULL', $char_d, '', '', + 'freq', 'varchar', 'NULL', $char_d, '', '', 'quantity', 'int', 'NULL', '', '', '', 'unitsetup', @money_typen, '', '', 'unitrecur', @money_typen, '', '', @@ -767,6 +867,7 @@ sub tables_hashref { 'squelch_cdr','char', 'NULL', 1, '', '', 'cdr_termination_percentage', 'decimal', 'NULL', '', '', '', 'invoice_terms', 'varchar', 'NULL', $char_d, '', '', + 'credit_limit', @money_typen, '', '', 'archived', 'char', 'NULL', 1, '', '', 'email_csv_cdr', 'char', 'NULL', 1, '', '', ], @@ -829,8 +930,8 @@ sub tables_hashref { 'last', 'varchar', '', $char_d, '', '', # 'middle', 'varchar', 'NULL', $char_d, '', '', 'first', 'varchar', '', $char_d, '', '', - 'title', 'varchar', '', $char_d, '', '', #eg Head Bottle Washer - 'comment', 'varchar', '', $char_d, '', '', + 'title', 'varchar', 'NULL', $char_d, '', '', #eg Head Bottle Washer + 'comment', 'varchar', 'NULL', $char_d, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'contactnum', @@ -842,13 +943,13 @@ sub tables_hashref { 'contact_phone' => { 'columns' => [ - 'contactphonenum', 'serial', '', '', '', '', - 'contactnum', 'int', '', '', '', '', - 'phonetypenum', 'int', '', '', '', '', - 'countrycode', 'varchar', '', 3, '', '', - 'phonenum', 'varchar', '', 14, '', '', - 'extension', 'varchar', '', 7, '', '', - #?#'comment', 'varchar', '', $char_d, '', '', + 'contactphonenum', 'serial', '', '', '', '', + 'contactnum', 'int', '', '', '', '', + 'phonetypenum', 'int', '', '', '', '', + 'countrycode', 'varchar', '', 3, '', '', + 'phonenum', 'varchar', '', 14, '', '', + 'extension', 'varchar', 'NULL', 7, '', '', + #?#'comment', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'contactphonenum', 'unique' => [], @@ -904,6 +1005,10 @@ sub tables_hashref { 'zip', 'varchar', 'NULL', 10, '', '', 'country', 'char', '', 2, '', '', 'geocode', 'varchar', 'NULL', 20, '', '', + 'location_type', 'varchar', 'NULL', 20, '', '', + 'location_number', 'varchar', 'NULL', 20, '', '', + 'location_kind', 'char', 'NULL', 1, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'locationnum', 'unique' => [], @@ -927,6 +1032,7 @@ sub tables_hashref { 'columns' => [ 'notenum', 'serial', '', '', '', '', 'custnum', 'int', '', '', '', '', + 'classnum', 'int', 'NULL', '', '', '', '_date', @date_type, '', '', 'otaker', 'varchar', 'NULL', 32, '', '', 'usernum', 'int', 'NULL', '', '', '', @@ -936,6 +1042,17 @@ sub tables_hashref { 'unique' => [], 'index' => [ [ 'custnum' ], [ '_date' ], [ 'usernum' ], ], }, + + 'cust_note_class' => { + 'columns' => [ + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'classnum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, 'cust_category' => { 'columns' => [ @@ -960,6 +1077,30 @@ sub tables_hashref { 'unique' => [], 'index' => [ ['disabled'] ], }, + + 'cust_tag' => { + 'columns' => [ + 'custtagnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'tagnum', 'int', '', '', '', '', + ], + 'primary_key' => 'custtagnum', + 'unique' => [ [ 'custnum', 'tagnum' ] ], + 'index' => [ [ 'custnum' ] ], + }, + + 'part_tag' => { + 'columns' => [ + 'tagnum', 'serial', '', '', '', '', + 'tagname', 'varchar', '', $char_d, '', '', + 'tagdesc', 'varchar', 'NULL', $char_d, '', '', + 'tagcolor', 'varchar', 'NULL', 6, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'tagnum', + 'unique' => [], #[ [ 'tagname' ] ], #? + 'index' => [ [ 'disabled' ] ], + }, 'cust_main_exemption' => { 'columns' => [ @@ -1168,10 +1309,11 @@ sub tables_hashref { 'reason', 'varchar', 'NULL', $char_d, '', '', 'otaker', 'varchar', 'NULL', 32, '', '', 'usernum', 'int', 'NULL', '', '', '', + 'void_usernum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'paynum', 'unique' => [], - 'index' => [ [ 'custnum' ], [ 'usernum' ], ], + 'index' => [ [ 'custnum' ], [ 'usernum' ], [ 'void_usernum' ] ], }, 'cust_bill_pay' => { @@ -1265,9 +1407,11 @@ sub tables_hashref { 'pkgnum', 'serial', '', '', '', '', 'custnum', 'int', '', '', '', '', 'pkgpart', 'int', '', '', '', '', + 'pkgbatch', 'varchar', 'NULL', $char_d, '', '', 'locationnum', 'int', 'NULL', '', '', '', 'otaker', 'varchar', 'NULL', 32, '', '', 'usernum', 'int', 'NULL', '', '', '', + 'order_date', @date_type, '', '', 'start_date', @date_type, '', '', 'setup', @date_type, '', '', 'bill', @date_type, '', '', @@ -1276,6 +1420,7 @@ sub tables_hashref { 'adjourn', @date_type, '', '', 'cancel', @date_type, '', '', 'expire', @date_type, '', '', + 'contract_end', @date_type, '', '', 'change_date', @date_type, '', '', 'change_pkgnum', 'int', 'NULL', '', '', '', 'change_pkgpart', 'int', 'NULL', '', '', '', @@ -1283,13 +1428,15 @@ sub tables_hashref { 'manual_flag', 'char', 'NULL', 1, '', '', 'no_auto', 'char', 'NULL', 1, '', '', 'quantity', 'int', 'NULL', '', '', '', + 'agent_pkgid', 'int', 'NULL', '', '', '', ], 'primary_key' => 'pkgnum', 'unique' => [], - 'index' => [ ['custnum'], ['pkgpart'], [ 'locationnum' ], [ 'usernum' ], + 'index' => [ ['custnum'], ['pkgpart'], [ 'pkgbatch' ], [ 'locationnum' ], + [ 'usernum' ], [ 'agent_pkgid' ], [ 'start_date' ], ['setup'], ['last_bill'], ['bill'], ['susp'], ['adjourn'], ['expire'], ['cancel'], - ['change_date'], + ['change_date'], ['order_date'], ], }, @@ -1461,6 +1608,7 @@ sub tables_hashref { 'pay_weight', 'real', 'NULL', '', '', '', 'credit_weight', 'real', 'NULL', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', + 'fcc_ds0s', 'int', 'NULL', '', '', '', 'no_auto', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'pkgpart', @@ -1485,6 +1633,17 @@ sub tables_hashref { # 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_discount' => { + 'columns' => [ + 'pkgdiscountnum', 'serial', '', '', '', '', + 'pkgpart', 'int', '', '', '', '', + 'discountnum', 'int', '', '', '', '', + ], + 'primary_key' => 'pkgdiscountnum', + 'unique' => [ [ 'pkgpart', 'discountnum' ] ], + 'index' => [], + }, + 'part_pkg_taxclass' => { 'columns' => [ 'taxclassnum', 'serial', '', '', '', '', @@ -1559,6 +1718,7 @@ sub tables_hashref { 'svcpart', 'int', '', '', '', '', 'quantity', 'int', '', '', '', '', 'primary_svc','char', 'NULL', 1, '', '', + 'hidden', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'pkgsvcnum', 'unique' => [ ['pkgpart', 'svcpart'] ], @@ -1631,6 +1791,35 @@ sub tables_hashref { 'unique' => [], 'index' => [ [ 'npa', 'nxx' ], [ 'popnum' ] ], }, + + 'qual' => { + 'columns' => [ + 'qualnum', 'serial', '', '', '', '', + 'custnum', 'int', 'NULL', '', '', '', + 'prospectnum', 'int', 'NULL', '', '', '', + 'locationnum', 'int', 'NULL', '', '', '', + 'phonenum', 'varchar', 'NULL', 24, '', '', + 'exportnum', 'int', 'NULL', '', '', '', + 'vendor_qual_id', 'varchar', 'NULL', $char_d, '', '', + 'status', 'char', '', 1, '', '', + ], + 'primary_key' => 'qualnum', + 'unique' => [], + 'index' => [ [ 'locationnum' ], ['custnum'], ['prospectnum'], + ['phonenum'], ['vendor_qual_id'] ], + }, + + 'qual_option' => { + 'columns' => [ + 'optionnum', 'serial', '', '', '', '', + 'qualnum', 'int', '', '', '', '', + 'optionname', 'varchar', '', $char_d, '', '', + 'optionvalue', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'optionnum', + 'unique' => [], + 'index' => [], + }, 'svc_acct' => { 'columns' => [ @@ -1659,17 +1848,34 @@ sub tables_hashref { 'pbxsvc', 'int', 'NULL', '', '', '', 'last_login', @date_type, '', '', 'last_logout', @date_type, '', '', + #cardfortress field(s) + 'cf_privatekey', 'text', 'NULL', '', '', '', #communigate pro fields (quota = MaxAccountSize) + 'cgp_aliases', 'varchar', 'NULL', 255, '', '', + #settings + 'cgp_type', 'varchar', 'NULL', $char_d, '', '', #AccountType 'file_quota', 'varchar', 'NULL', $char_d, '', '', #MaxWebSize 'file_maxnum', 'varchar', 'NULL', $char_d, '', '', #MaxWebFiles 'file_maxsize', 'varchar', 'NULL', $char_d, '', '', #MaxFileSize 'cgp_accessmodes', 'varchar', 'NULL', 255, '', '', #AccessModes 'password_selfchange','char', 'NULL', 1, '', '', #PWDAllowed - 'password_recover', 'char', 'NULL', 1, '', '', #PasswordRecovery - 'cgp_type', 'varchar', 'NULL', $char_d, '', '', #AccountType - 'cgp_aliases', 'varchar', 'NULL', 255, '', '', - 'cgp_deletemode', 'varchar', 'NULL', $char_d, '', '', #DeleteMode - 'cgp_emptytrash', 'varchar', 'NULL', $char_d, '', '', #EmptyTrash + 'password_recover', 'char', 'NULL', 1, 'Y','', #PasswordRecovery + 'cgp_rulesallowed','varchar', 'NULL', $char_d, '', '', #RulesAllowed + 'cgp_rpopallowed', 'char', 'NULL', 1, '', '', #RPOPAllowed + 'cgp_mailtoall', 'char', 'NULL', 1, '', '', #MailToAll + 'cgp_addmailtrailer', 'char', 'NULL', 1, '', '', #AddMailTrailer + 'cgp_archiveafter', 'int', 'NULL', '', '', '', #ArchiveMessagesAfter + #XXX mailing lists + #preferences + 'cgp_deletemode', 'varchar', 'NULL', $char_d, '', '',#DeleteMode + 'cgp_emptytrash', 'varchar', 'NULL', $char_d, '', '',#EmptyTrash + 'cgp_language', 'varchar', 'NULL', $char_d, '', '',#Language + 'cgp_timezone', 'varchar', 'NULL', $char_d, '', '',#TimeZone + 'cgp_skinname', 'varchar', 'NULL', $char_d, '', '',#SkinName + 'cgp_prontoskinname', 'varchar', 'NULL', $char_d, '', '',#ProntoSkinName + 'cgp_sendmdnmode', 'varchar', 'NULL', $char_d, '', '',#SendMDNMode + #mail + #XXX RPOP settings ], 'primary_key' => 'svcnum', #'unique' => [ [ 'username', 'domsvc' ] ], @@ -1713,30 +1919,87 @@ sub tables_hashref { 'setup_date', @date_type, '', '', 'renewal_interval', 'int', 'NULL', '', '', '', 'expiration_date', @date_type, '', '', + '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 - 'acct_def_password_selfchange','char', 'NULL', 1, '', '', - 'acct_def_password_recover', 'char', 'NULL', 1, '', '', - 'acct_def_cgp_accessmodes', 'varchar', 'NULL', 255, '', '', - 'acct_def_quota', 'varchar', 'NULL', $char_d, '', '', - 'acct_def_file_quota', 'varchar', 'NULL', $char_d, '', '', - 'acct_def_file_maxnum', 'varchar', 'NULL', $char_d, '', '', - 'acct_def_file_maxsize', 'varchar', 'NULL', $char_d, '', '', - 'acct_def_cgp_deletemode', 'varchar', 'NULL', $char_d, '', '', - 'acct_def_cgp_emptytrash', 'varchar', 'NULL', $char_d, '', '', - 'acct_def_cgp_rulesallowed','varchar', 'NULL', $char_d, '', '', - 'acct_def_cgp_rpopallowed', 'char', 'NULL', 1, '', '', - 'acct_def_cgp_mailtoall', 'char', 'NULL', 1, '', '', - 'acct_def_cgp_addmailtrailer', 'char', 'NULL', 1, '', '', - + 'cgp_certificatetype','varchar','NULL', $char_d, '', '', + #settings + 'acct_def_password_selfchange', 'char', 'NULL', 1, '', '', + 'acct_def_password_recover', 'char', 'NULL', 1, 'Y', '', + 'acct_def_cgp_accessmodes', 'varchar', 'NULL', 255, '', '', + 'acct_def_quota', 'varchar', 'NULL', $char_d, '', '', + 'acct_def_file_quota', 'varchar', 'NULL', $char_d, '', '', + 'acct_def_file_maxnum', 'varchar', 'NULL', $char_d, '', '', + 'acct_def_file_maxsize', 'varchar', 'NULL', $char_d, '', '', + 'acct_def_cgp_rulesallowed', 'varchar', 'NULL', $char_d, '', '', + 'acct_def_cgp_rpopallowed', 'char', 'NULL', 1, '', '', + 'acct_def_cgp_mailtoall', 'char', 'NULL', 1, '', '', + 'acct_def_cgp_addmailtrailer', 'char', 'NULL', 1, '', '', + 'acct_def_cgp_archiveafter', 'int', 'NULL', '', '', '', + #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, '', '', + 'acct_def_cgp_timezone', 'varchar', 'NULL', $char_d, '', '', + 'acct_def_cgp_skinname', 'varchar', 'NULL', $char_d, '', '', + 'acct_def_cgp_prontoskinname', 'varchar', 'NULL', $char_d, '', '', + 'acct_def_cgp_sendmdnmode', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', 'unique' => [ ], 'index' => [ ['domain'] ], }, + + 'svc_dsl' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'pushed', 'int', 'NULL', '', '', '', + '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, '', '', + 'first', 'varchar', 'NULL', $char_d, '', '', + 'last', 'varchar', 'NULL', $char_d, '', '', + 'company', 'varchar', 'NULL', $char_d, '', '', + 'phonenum', 'varchar', 'NULL', 24, '', '', + 'loop_type', 'char', 'NULL', 1, '', '', + 'local_voice_provider', 'varchar', 'NULL', $char_d, '', '', + 'circuitnum', 'varchar', 'NULL', $char_d, '', '', + 'rate_band', 'varchar', 'NULL', $char_d, '', '', + 'vpi', 'int', 'NULL', '', '', '', + 'vci', 'int', 'NULL', '', '', '', + 'isp_chg', 'char', 'NULL', 1, '', '', + 'isp_prev', 'varchar', 'NULL', $char_d, '', '', + 'username', 'varchar', 'NULL', $char_d, '', '', + 'password', 'varchar', 'NULL', $char_d, '', '', + 'staticips', 'text', 'NULL', '', '', '', + 'monitored', 'char', 'NULL', 1, '', '', + 'last_pull', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [ ], + 'index' => [ ['phonenum'], ['vendor_order_id'] ], + }, + + 'dsl_note' => { + 'columns' => [ + 'notenum', 'serial', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'author', 'varchar', 'NULL', $char_d, '', '', + 'priority', 'char', 'NULL', 1, '', '', + '_date', 'int', 'NULL', '', '', '', + 'note', 'text', '', '', '', '', + ], + 'primary_key' => 'notenum', + 'unique' => [ ], + 'index' => [ ['svcnum'] ], + }, 'domain_record' => { 'columns' => [ @@ -1746,6 +2009,7 @@ sub tables_hashref { 'recaf', 'char', '', 2, '', '', 'rectype', 'varchar', '', 5, '', '', 'recdata', 'varchar', '', 255, '', '', + 'ttl', 'int', 'NULL', '', '', '', ], 'primary_key' => 'recnum', 'unique' => [], @@ -1762,6 +2026,44 @@ sub tables_hashref { 'index' => [], }, + 'cgp_rule' => { + 'columns' => [ + 'rulenum', 'serial', '', '', '', '', + 'name', 'varchar', '', $char_d, '', '', + 'comment', 'varchar', 'NULL', $char_d, '', '', + 'svcnum', 'int', '', '', '', '', + 'priority', 'int', '', '', '', '', + ], + 'primary_key' => 'rulenum', + 'unique' => [ [ 'svcnum', 'name' ] ], + 'index' => [ [ 'svcnum' ] ], + }, + + 'cgp_rule_condition' => { + 'columns' => [ + 'ruleconditionnum', 'serial', '', '', '', '', + 'conditionname', 'varchar', '', $char_d, '', '', + 'op', 'varchar', 'NULL', $char_d, '', '', + 'params', 'varchar', 'NULL', 255, '', '', + 'rulenum', 'int', '', '', '', '', + ], + 'primary_key' => 'ruleconditionnum', + 'unique' => [], + 'index' => [ [ 'rulenum' ] ], + }, + + 'cgp_rule_action' => { + 'columns' => [ + 'ruleactionnum', 'serial', '', '', '', '', + 'action', 'varchar', '', $char_d, '', '', + 'params', 'varchar', 'NULL', 255, '', '', + 'rulenum', 'int', '', '', '', '', + ], + 'primary_key' => 'ruleactionnum', + 'unique' => [], + 'index' => [ [ 'rulenum' ] ], + }, + 'svc_forward' => { 'columns' => [ 'svcnum', 'int', '', '', '', '', @@ -1921,6 +2223,7 @@ sub tables_hashref { 'part_export' => { 'columns' => [ 'exportnum', 'serial', '', '', '', '', + 'exportname', 'varchar', 'NULL', $char_d, '', '', 'machine', 'varchar', '', $char_d, '', '', 'exporttype', 'varchar', '', $char_d, '', '', 'nodomain', 'char', 'NULL', 1, '', '', @@ -2051,6 +2354,7 @@ sub tables_hashref { 'altitude', 'decimal', 'NULL', '', '', '', 'vlan_profile', 'varchar', 'NULL', $char_d, '', '', 'performance_profile', 'varchar', 'NULL', $char_d, '', '', + 'plan_id', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', 'unique' => [ [ 'mac_addr' ] ], @@ -2086,12 +2390,18 @@ sub tables_hashref { 'acct_snarf' => { 'columns' => [ - 'snarfnum', 'int', '', '', '', '', - 'svcnum', 'int', '', '', '', '', - 'machine', 'varchar', '', 255, '', '', - 'protocol', 'varchar', '', $char_d, '', '', - 'username', 'varchar', '', $char_d, '', '', - '_password', 'varchar', '', $char_d, '', '', + 'snarfnum', 'serial', '', '', '', '', + 'snarfname', 'varchar', 'NULL', $char_d, '', '', + 'svcnum', 'int', '', '', '', '', + 'machine', 'varchar', '', 255, '', '', + 'protocol', 'varchar', '', $char_d, '', '', + 'username', 'varchar', '', $char_d, '', '', + '_password', 'varchar', '', $char_d, '', '', + 'check_freq', 'int', 'NULL', '', '', '', + 'leavemail', 'char', 'NULL', 1, '', '', + 'apop', 'char', 'NULL', 1, '', '', + 'tls', 'char', 'NULL', 1, '', '', + 'mailbox', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'snarfnum', 'unique' => [], @@ -2134,6 +2444,18 @@ sub tables_hashref { 'index' => [ [ 'pkgpart' ], [ 'optionname' ] ], }, + 'part_pkg_vendor' => { + 'columns' => [ + 'num', 'serial', '', '', '', '', + 'pkgpart', 'int', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + 'vendor_pkg_id', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'num', + 'unique' => [ [ 'pkgpart', 'exportnum' ] ], + 'index' => [ [ 'pkgpart' ] ], + }, + 'part_pkg_report_option' => { 'columns' => [ 'num', 'serial', '', '', '', '', @@ -2162,12 +2484,13 @@ sub tables_hashref { 'orig_regionnum', 'int', 'NULL', '', '', '', 'dest_regionnum', 'int', '', '', '', '', 'min_included', 'int', '', '', '', '', - 'conn_charge', @money_type, '0', '', #'decimal','','10,5','0','', + 'conn_charge', 'decimal', '', '10,4', '0', '', 'conn_sec', 'int', '', '', '0', '', 'min_charge', 'decimal', '', '10,5', '', '', #@money_type, '', '', 'sec_granularity', 'int', '', '', '', '', - #time period (link to table of periods)? + 'ratetimenum', 'int', 'NULL', '', '', '', 'classnum', 'int', 'NULL', '', '', '', + 'cdrtypenum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'ratedetailnum', 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ], @@ -2197,6 +2520,28 @@ sub tables_hashref { 'index' => [ [ 'countrycode' ], [ 'npa' ], [ 'regionnum' ] ], }, + 'rate_time' => { + 'columns' => [ + 'ratetimenum', 'serial', '', '', '', '', + 'ratetimename', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'ratetimenum', + 'unique' => [], + 'index' => [], + }, + + 'rate_time_interval' => { + 'columns' => [ + 'intervalnum', 'serial', '', '', '', '', + 'stime', 'int', '', '', '', '', + 'etime', 'int', '', '', '', '', + 'ratetimenum', 'int', '', '', '', '', + ], + 'primary_key' => 'intervalnum', + 'unique' => [], + 'index' => [], + }, + 'usage_class' => { 'columns' => [ 'classnum', 'serial', '', '', '', '', @@ -2378,6 +2723,8 @@ sub tables_hashref { 'uniqueid', 'varchar', '', 32, \"''", '', 'userfield', 'varchar', '', 255, \"''", '', + 'max_callers', 'int', 'NULL', '', '', '', + ### # fields for unitel/RSLCOM/convergent that don't map well to asterisk # defaults @@ -2462,6 +2809,7 @@ sub tables_hashref { 'termpart', 'int', '', '', '', '',#future use see below 'rated_price', 'decimal', 'NULL', '10,4', '', '', 'status', 'varchar', 'NULL', 32, '', '', + 'svcnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'cdrtermnum', 'unique' => [ [ 'acctid', 'termpart' ] ], @@ -2530,12 +2878,13 @@ sub tables_hashref { 'columns' => [ 'itemnum', 'serial', '', '', '', '', 'classnum', 'int', '', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', 'item', 'varchar', '', $char_d, '', '', 'svcnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'itemnum', 'unique' => [ [ 'classnum', 'item' ] ], - 'index' => [ [ 'classnum' ], [ 'svcnum' ] ], + 'index' => [ [ 'classnum' ], [ 'agentnum' ], [ 'svcnum' ] ], }, 'inventory_class' => { @@ -2631,6 +2980,16 @@ sub tables_hashref { 'pbxsvc', 'int', 'NULL', '', '', '', 'domsvc', 'int', 'NULL', '', '', '', 'locationnum', 'int', 'NULL', '', '', '', + 'forwarddst', 'varchar', 'NULL', 15, '', '', + 'email', 'varchar', 'NULL', 255, '', '', + 'lnp_status', 'varchar', 'NULL', $char_d, '', '', + 'portable', 'char', 'NULL', 1, '', '', + 'lrn', 'char', 'NULL', 10, '', '', + 'lnp_desired_due_date', 'int', 'NULL', '', '', '', + 'lnp_due_date', 'int', 'NULL', '', '', '', + 'lnp_other_provider', 'varchar', 'NULL', $char_d, '', '', + 'lnp_other_provider_account', 'varchar', 'NULL', $char_d, '', '', + 'lnp_reject_reason', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -2655,7 +3014,7 @@ sub tables_hashref { 'columns' => [ 'devicepart', 'serial', '', '', '', '', 'devicename', 'varchar', '', $char_d, '', '', - #'classnum', #tie to an inventory class? + 'inventory_classnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'devicepart', 'unique' => [ [ 'devicename' ] ], #? @@ -2672,6 +3031,8 @@ sub tables_hashref { 'nxx', 'char', 'NULL', 3, '', '', 'station', 'char', 'NULL', 4, '', '', 'name', 'varchar', 'NULL', $char_d, '', '', + 'rate_center_abbrev', 'varchar', 'NULL', $char_d, '', '', + 'ordernum', 'int', 'NULL', '', '', '', 'svcnum', 'int', 'NULL', '', '', '', 'availbatch', 'varchar', 'NULL', $char_d, '', '', ], @@ -2685,6 +3046,45 @@ sub tables_hashref { [ 'availbatch' ], ], }, + + 'lata' => { + 'columns' => [ + 'latanum', 'int', '', '', '', '', + 'description', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'latanum', + 'unique' => [], + 'index' => [], + }, + + 'did_vendor' => { + 'columns' => [ + 'vendornum', 'serial', '', '', '', '', + 'vendorname', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'vendornum', + 'unique' => [], + 'index' => [], + }, + + 'did_order' => { + 'columns' => [ + 'ordernum', 'serial', '', '', '', '', + 'vendornum', 'int', '', '', '', '', + 'vendor_order_id', 'varchar', '', $char_d, '', '', + 'msa', 'varchar', 'NULL', $char_d, '', '', + 'latanum', 'int', 'NULL', '', '', '', + 'rate_center', 'varchar', 'NULL', $char_d, '', '', + 'state', 'char', 'NULL', 2, '', '', + 'quantity', 'int', '', '', '', '', + 'submitted', 'int', '', '', '', '', + 'confirmed', 'int', 'NULL', '', '', '', + 'received', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'ordernum', + 'unique' => [ [ 'vendornum', 'vendor_order_id' ] ], + 'index' => [], + }, 'reason_type' => { 'columns' => [ @@ -2734,10 +3134,11 @@ sub tables_hashref { 'svc_pbx' => { 'columns' => [ - 'svcnum', 'int', '', '', '', '', - 'id', 'int', 'NULL', '', '', '', - 'title', 'varchar', 'NULL', $char_d, '', '', - 'max_extensions', 'int', 'NULL', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'id', 'int', 'NULL', '', '', '', + 'title', 'varchar', 'NULL', $char_d, '', '', + 'max_extensions', 'int', 'NULL', '', '', '', + 'max_simultaneous', 'int', 'NULL', '', '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -2783,6 +3184,125 @@ sub tables_hashref { 'index' => [['listnum'],['svcnum'],['contactemailnum'],['email']], }, + 'bill_batch' => { + 'columns' => [ + 'batchnum', 'serial', '', '', '', '', + 'status', 'char', 'NULL','1', '', '', + 'pdf', 'blob', 'NULL', '', '', '', + ], + 'primary_key' => 'batchnum', + 'unique' => [], + 'index' => [], + }, + + 'cust_bill_batch' => { + 'columns' => [ + 'billbatchnum', 'serial', '', '', '', '', + 'batchnum', 'int', '', '', '', '', + 'invnum', 'int', '', '', '', '', + ], + 'primary_key' => 'billbatchnum', + 'unique' => [], + 'index' => [ [ 'batchnum' ], [ 'invnum' ] ], + }, + + 'cust_bill_batch_option' => { + 'columns' => [ + 'optionnum', 'serial', '', '', '', '', + 'billbatchnum', 'int', '', '', '', '', + 'optionname', 'varchar', '', $char_d, '', '', + 'optionvalue', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'optionnum', + 'unique' => [], + 'index' => [ [ 'billbatchnum' ], [ 'optionname' ] ], + }, + + 'msg_template' => { + 'columns' => [ + 'msgnum', 'serial', '', '', '', '', + 'msgname', 'varchar', '', $char_d, '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'subject', 'varchar', 'NULL', 512, '', '', + 'mime_type', 'varchar', '', $char_d, '', '', + 'body', 'blob', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'from_addr', 'varchar', 'NULL', 255, '', '', + 'bcc_addr', 'varchar', 'NULL', 255, '', '', + ], + 'primary_key' => 'msgnum', + 'unique' => [ ['msgname', 'mime_type'] ], + 'index' => [ ['agentnum'], ] + }, + + 'svc_cert' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'recnum', 'int', 'NULL', '', '', '', + 'privatekey', 'text', 'NULL', '', '', '', + 'csr', 'text', 'NULL', '', '', '', + 'certificate', 'text', 'NULL', '', '', '', + 'cacert', 'text', 'NULL', '', '', '', + 'common_name', 'varchar', 'NULL', $char_d, '', '', + 'organization', 'varchar', 'NULL', $char_d, '', '', + 'organization_unit', 'varchar', 'NULL', $char_d, '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', + 'state', 'varchar', 'NULL', $char_d, '', '', + 'country', 'char', 'NULL', 2, '', '', + 'cert_contact', 'varchar', 'NULL', $char_d, '', '', + ], + 'primary_key' => 'svcnum', + 'unique' => [], + 'index' => [], #recnum + }, + + 'svc_port' => { + 'columns' => [ + 'svcnum', 'int', '', '', '', '', + 'serviceid', 'varchar', '', 64, '', '', #srvexport / reportfields + ], + 'primary_key' => 'svcnum', + 'unique' => [], + 'index' => [], #recnum + }, + + 'areacode' => { + 'columns' => [ + 'code', 'char', '', 3, '', '', + 'country', 'char', 'NULL', 2, '', '', + 'state', 'char', 'NULL', 2, '', '', + 'description','varchar', 'NULL', 255, '', '', + ], + 'primary_key' => 'code', + 'unique' => [], + 'index' => [], + }, + + %{ tables_hashref_torrus() }, + + # tables of ours for doing torrus virtual port combining + 'torrus_srvderive' => { + 'columns' => [ + 'derivenum', 'serial', '', '', '', '', + 'serviceid', 'varchar', '', 64, '', '', #srvexport / reportfields + 'last_srv_date', 'date', 'NULL', '', '', '', + ], + 'primary_key' => 'derivenum', + 'unique' => [ ['serviceid'] ], + 'index' => [], + }, + + 'torrus_srvderive_component' => { + 'columns' => [ + 'componentnum', 'serial', '', '', '', '', + 'derivenum', 'int', '', '', '', '', + 'serviceid', 'varchar', '', 64, '', '', #srvexport / reportfields + ], + 'primary_key' => 'componentnum', + 'unique' => [ [ 'derivenum', 'serviceid' ], ], + 'index' => [ [ 'derivenum', ], ], + }, + # name type nullability length default local