X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=690cba591f3997d2efe14be234831b08c3e55a36;hb=6235082affb5884d3caaaf18728771d322e0b2cf;hp=a9b5469403a8ec5ccda0e6bbf01fc30d6ab7f93c;hpb=794a4505360fec404e2b9d5c6daf79f750186bfe;p=freeside.git diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index a9b546940..690cba591 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -372,18 +372,58 @@ sub tables_hashref { 'index' => [ ['typenum'] ], }, + 'cust_attachment' => { + 'columns' => [ + 'attachnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'otaker', 'varchar', '', 32, '', '', + 'filename', 'varchar', '', 32, '', '', + 'mime_type', 'varchar', '', 32, '', '', + 'title', 'varchar', 'NULL', 32, '', '', + 'body', 'blob', 'NULL', '', '', '', + 'disabled', 'varchar', 'NULL', '10', '', + ], + 'primary_key' => 'attachnum', + 'unique' => [], + 'index' => [ ['custnum'] ], + }, + 'cust_bill' => { 'columns' => [ - 'invnum', 'serial', '', '', '', '', - 'custnum', 'int', '', '', '', '', - '_date', @date_type, '', '', - 'charged', @money_type, '', '', - 'printed', 'int', '', '', '', '', - 'closed', 'char', 'NULL', 1, '', '', + #regular fields + 'invnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'charged', @money_type, '', '', + 'invoice_terms', 'varchar', 'NULL', $char_d, '', '', + + #customer balance info at invoice generation time + 'previous_balance', @money_typen, '', '', #eventually not nullable + 'billing_balance', @money_typen, '', '', #eventually not nullable + + #deprecated (unused by now, right?) + 'printed', 'int', '', '', '', '', + + #specific use cases + 'closed', 'char', 'NULL', 1, '', '', #not yet used much + 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements + 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy ], 'primary_key' => 'invnum', + 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh + 'index' => [ ['custnum'], ['_date'], ['statementnum'], ['agent_invid'] ], + }, + + 'cust_statement' => { + 'columns' => [ + 'statementnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + ], + 'primary_key' => 'statementnum', 'unique' => [], - 'index' => [ ['custnum'], ['_date'] ], + 'index' => [ ['custnum'], ['_date'], ], }, 'cust_bill_event' => { @@ -513,14 +553,16 @@ sub tables_hashref { 'sdate', @date_type, '', '', 'edate', @date_type, '', '', 'itemdesc', 'varchar', 'NULL', $char_d, '', '', + 'itemcomment', 'varchar', 'NULL', $char_d, '', '', 'section', 'varchar', 'NULL', $char_d, '', '', 'quantity', 'int', 'NULL', '', '', '', 'unitsetup', @money_typen, '', '', 'unitrecur', @money_typen, '', '', + 'hidden', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'billpkgnum', 'unique' => [], - 'index' => [ ['invnum'], [ 'pkgnum' ] ], + 'index' => [ ['invnum'], [ 'pkgnum' ], [ 'itemdesc' ], ], }, 'cust_bill_pkg_detail' => { @@ -529,9 +571,12 @@ sub tables_hashref { 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable 'pkgnum', 'int', 'NULL', '', '', '', # deprecated 'invnum', 'int', 'NULL', '', '', '', # deprecated - 'amount', @money_typen, '', '', + 'amount', 'decimal', 'NULL', '10,4', '', '', 'format', 'char', 'NULL', 1, '', '', 'classnum', 'int', 'NULL', '', '', '', + 'duration', 'int', 'NULL', '', 0, '', + 'phonenum', 'varchar', 'NULL', 15, '', '', + 'regionname', 'varchar', 'NULL', $char_d, '', '', 'detail', 'varchar', '', 255, '', '', ], 'primary_key' => 'detailnum', @@ -596,6 +641,7 @@ sub tables_hashref { 'reasonnum', 'int', 'NULL', '', '', '', 'addlinfo', 'text', 'NULL', '', '', '', 'closed', 'char', 'NULL', 1, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances ], 'primary_key' => 'crednum', 'unique' => [], @@ -609,6 +655,7 @@ sub tables_hashref { 'invnum', 'int', '', '', '', '', '_date', @date_type, '', '', 'amount', @money_type, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances ], 'primary_key' => 'creditbillnum', 'unique' => [], @@ -620,6 +667,8 @@ sub tables_hashref { 'creditbillpkgnum', 'serial', '', '', '', '', 'creditbillnum', 'int', '', '', '', '', 'billpkgnum', 'int', '', '', '', '', + 'billpkgtaxlocationnum', 'int', 'NULL', '', '', '', + 'billpkgtaxratelocationnum', 'int', 'NULL', '', '', '', 'amount', @money_type, '', '', 'setuprecur', 'varchar', '', $char_d, '', '', 'sdate', @date_type, '', '', @@ -627,7 +676,11 @@ sub tables_hashref { ], 'primary_key' => 'creditbillpkgnum', 'unique' => [], - 'index' => [ [ 'creditbillnum' ], [ 'billpkgnum' ], ], + 'index' => [ [ 'creditbillnum' ], + [ 'billpkgnum' ], + [ 'billpkgtaxlocationnum' ], + [ 'billpkgtaxratelocationnum' ], + ], }, 'cust_main' => { @@ -635,6 +688,7 @@ sub tables_hashref { 'custnum', 'serial', '', '', '', '', 'agentnum', 'int', '', '', '', '', 'agent_custid', 'varchar', 'NULL', $char_d, '', '', + 'classnum', 'int', 'NULL', '', '', '', 'custbatch', 'varchar', 'NULL', $char_d, '', '', # 'titlenum', 'int', 'NULL', '', '', '', 'last', 'varchar', '', $char_d, '', '', @@ -685,6 +739,7 @@ sub tables_hashref { 'paytype', 'varchar', 'NULL', $char_d, '', '', 'payip', 'varchar', 'NULL', 15, '', '', 'geocode', 'varchar', 'NULL', 20, '', '', + 'censustract', 'varchar', 'NULL', 20, '', '', # 7 to save space? 'tax', 'char', 'NULL', 1, '', '', 'otaker', 'varchar', '', 32, '', '', 'refnum', 'int', '', '', '', '', @@ -692,14 +747,17 @@ sub tables_hashref { 'comments', 'text', 'NULL', '', '', '', 'spool_cdr','char', 'NULL', 1, '', '', 'squelch_cdr','char', 'NULL', 1, '', '', + 'cdr_termination_percentage', 'decimal', 'NULL', '', '', '', 'invoice_terms', 'varchar', 'NULL', $char_d, '', '', 'archived', 'char', 'NULL', 1, '', '', + 'email_csv_cdr', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'custnum', 'unique' => [ [ 'agentnum', 'agent_custid' ] ], #'index' => [ ['last'], ['company'] ], 'index' => [ - [ 'agentnum' ], [ 'refnum' ], [ 'custbatch' ], + [ 'agentnum' ], [ 'refnum' ], [ 'classnum' ], + [ 'custbatch' ], [ 'referral_custnum' ], [ 'payby' ], [ 'paydate' ], [ 'archived' ], @@ -716,6 +774,32 @@ sub tables_hashref { ], }, + 'cust_recon' => { # what purpose does this serve? + 'columns' => [ + 'reconid', 'serial', '', '', '', '', + 'recondate', @date_type, '', '', + 'custnum', 'int' , '', '', '', '', + 'agentnum', 'int', '', '', '', '', + 'last', 'varchar', '', $char_d, '', '', + 'first', 'varchar', '', $char_d, '', '', + 'address1', 'varchar', '', $char_d, '', '', + 'address2', 'varchar', 'NULL', $char_d, '', '', + 'city', 'varchar', '', $char_d, '', '', + 'state', 'varchar', 'NULL', $char_d, '', '', + 'zip', 'varchar', 'NULL', 10, '', '', + 'pkg', 'varchar', 'NULL', $char_d, '', '', + 'adjourn', @date_type, '', '', + 'status', 'varchar', 'NULL', 10, '', '', + 'agent_custid', 'varchar', '', $char_d, '', '', + 'agent_pkg', 'varchar', 'NULL', $char_d, '', '', + 'agent_adjourn', @date_type, '', '', + 'comments', 'text', 'NULL', '', '', '', + ], + 'primary_key' => 'reconid', + 'unique' => [], + 'index' => [], + }, + #eventually use for billing & ship from cust_main too #for now, just cust_pkg locations 'cust_location' => { @@ -762,13 +846,65 @@ sub tables_hashref { 'index' => [ [ 'custnum' ], [ '_date' ], ], }, + 'cust_category' => { + 'columns' => [ + 'categorynum', 'serial', '', '', '', '', + 'categoryname', 'varchar', '', $char_d, '', '', + 'weight', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'categorynum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, + + 'cust_class' => { + 'columns' => [ + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + 'categorynum', 'int', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'classnum', + 'unique' => [], + 'index' => [ ['disabled'] ], + }, + + 'cust_main_exemption' => { + 'columns' => [ + 'exemptionnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'taxname', 'varchar', '', $char_d, '', '', + #start/end dates? for reporting? + ], + 'primary_key' => 'exemptionnum', + 'unique' => [], + 'index' => [ [ 'custnum' ] ], + }, + + 'cust_tax_adjustment' => { + 'columns' => [ + 'adjustmentnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'taxname', 'varchar', '', $char_d, '', '', + 'amount', @money_type, '', '', + 'comment', 'varchar', 'NULL', $char_d, '', '', + 'billpkgnum', 'int', 'NULL', '', '', '', + #more? no cust_bill_pkg_tax_location? + ], + 'primary_key' => 'adjustmentnum', + 'unique' => [], + 'index' => [ [ 'custnum' ], [ 'billpkgnum' ] ], + }, + 'cust_main_county' => { #county+state+country are checked off the #cust_main_county for validation and to provide # a tax rate. 'columns' => [ 'taxnum', 'serial', '', '', '', '', - 'state', 'varchar', 'NULL', $char_d, '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', + 'state', 'varchar', 'NULL', $char_d, '', '', 'country', 'char', '', 2, '', '', 'taxclass', 'varchar', 'NULL', $char_d, '', '', 'exempt_amount', @money_type, '', '', @@ -780,7 +916,7 @@ sub tables_hashref { 'primary_key' => 'taxnum', 'unique' => [], # 'unique' => [ ['taxnum'], ['state', 'county'] ], - 'index' => [ [ 'county' ], [ 'state' ], [ 'country' ], + 'index' => [ [ 'city' ], [ 'county' ], [ 'state' ], [ 'country' ], [ 'taxclass' ], ], }, @@ -812,6 +948,8 @@ sub tables_hashref { 'passflag', 'char', 'NULL', 1, '', '', # Y = required to list as line item, N = Prohibited 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt + 'inoutcity', 'char', 'NULL', 1, '', '', # '', 'I', or 'O' + 'inoutlocal', 'char', 'NULL', 1, '', '', # '', 'I', or 'O' 'manual', 'char', 'NULL', 1, '', '', # Y = manually edited 'disabled', 'char', 'NULL', 1, '', '', # Y = tax disabled ], @@ -827,7 +965,7 @@ sub tables_hashref { 'geocode', 'varchar', '', 20, '', '', 'city', 'varchar', 'NULL', $char_d, '', '', 'county', 'varchar', 'NULL', $char_d, '', '', - 'state', 'char', '', 2, '', '', + 'state', 'char', 'NULL', 2, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'taxratelocationnum', @@ -883,6 +1021,7 @@ sub tables_hashref { #'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. 'payunique', 'varchar', 'NULL', $char_d, '', '', #separate paybatch "unique" functions from current usage + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'status', 'varchar', '', $char_d, '', '', 'session_id', 'varchar', 'NULL', $char_d, '', '', #only need 32 'statustext', 'text', 'NULL', '', '', '', @@ -912,6 +1051,7 @@ sub tables_hashref { '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 ], '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' ] ], @@ -931,6 +1071,7 @@ sub tables_hashref { 'paymask', 'varchar', 'NULL', $char_d, '', '', 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. 'closed', 'char', 'NULL', 1, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances 'void_date', @date_type, '', '', 'reason', 'varchar', 'NULL', $char_d, '', '', 'otaker', 'varchar', '', 32, '', '', @@ -947,6 +1088,7 @@ sub tables_hashref { 'paynum', 'int', '', '', '', '', 'amount', @money_type, '', '', '_date', @date_type, '', '', + 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances ], 'primary_key' => 'billpaynum', 'unique' => [], @@ -971,6 +1113,8 @@ sub tables_hashref { 'billpaypkgnum', 'serial', '', '', '', '', 'billpaynum', 'int', '', '', '', '', 'billpkgnum', 'int', '', '', '', '', + 'billpkgtaxlocationnum', 'int', 'NULL', '', '', '', + 'billpkgtaxratelocationnum', 'int', 'NULL', '', '', '', 'amount', @money_type, '', '', 'setuprecur', 'varchar', '', $char_d, '', '', 'sdate', @date_type, '', '', @@ -1030,6 +1174,7 @@ sub tables_hashref { 'pkgpart', 'int', '', '', '', '', 'locationnum', 'int', 'NULL', '', '', '', 'otaker', 'varchar', '', 32, '', '', + 'start_date', @date_type, '', '', 'setup', @date_type, '', '', 'bill', @date_type, '', '', 'last_bill', @date_type, '', '', @@ -1047,8 +1192,8 @@ sub tables_hashref { 'primary_key' => 'pkgnum', 'unique' => [], 'index' => [ ['custnum'], ['pkgpart'], [ 'locationnum' ], - ['setup'], ['last_bill'], ['bill'], ['susp'], ['adjourn'], - ['expire'], ['cancel'], + [ 'start_date' ], ['setup'], ['last_bill'], ['bill'], + ['susp'], ['adjourn'], ['expire'], ['cancel'], ['change_date'], ], }, @@ -1165,9 +1310,13 @@ sub tables_hashref { 'plan', 'varchar', 'NULL', $char_d, '', '', 'plandata', 'text', 'NULL', '', '', '', 'disabled', 'char', 'NULL', 1, '', '', + 'custom', 'char', 'NULL', 1, '', '', 'taxclass', 'varchar', 'NULL', $char_d, '', '', 'classnum', 'int', 'NULL', '', '', '', + 'addon_classnum','int', 'NULL', '', '', '', 'taxproductnum', 'int', 'NULL', '', '', '', + 'setup_cost', @money_typen, '', '', + 'recur_cost', @money_typen, '', '', 'pay_weight', 'real', 'NULL', '', '', '', 'credit_weight', 'real', 'NULL', '', '', '', 'agentnum', 'int', 'NULL', '', '', '', @@ -1175,29 +1324,35 @@ sub tables_hashref { ], 'primary_key' => 'pkgpart', 'unique' => [], - 'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'agentnum' ], ], + 'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'classnum' ], + [ 'agentnum' ], + ], }, 'part_pkg_link' => { 'columns' => [ - 'pkglinknum', 'serial', '', '', '', '', - 'src_pkgpart', 'int', '', '', '', '', - 'dst_pkgpart', 'int', '', '', '', '', - 'link_type', 'varchar', '', $char_d, '', '', + 'pkglinknum', 'serial', '', '', '', '', + 'src_pkgpart', 'int', '', '', '', '', + 'dst_pkgpart', 'int', '', '', '', '', + 'link_type', 'varchar', '', $char_d, '', '', + 'hidden', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'pkglinknum', - 'unique' => [ [ 'src_pkgpart', 'dst_pkgpart', 'link_type' ] ], + 'unique' => [ [ 'src_pkgpart', 'dst_pkgpart', 'link_type', 'hidden' ] ], 'index' => [ [ 'src_pkgpart' ] ], }, + # 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_taxclass' => { 'columns' => [ 'taxclassnum', 'serial', '', '', '', '', 'taxclass', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'taxclassnum', 'unique' => [ [ 'taxclass' ] ], - 'index' => [], + 'index' => [ [ 'disabled' ] ], }, 'part_pkg_taxproduct' => { @@ -1340,7 +1495,7 @@ sub tables_hashref { 'columns' => [ 'svcnum', 'int', '', '', '', '', 'username', 'varchar', '', $username_len, '', '', - '_password', 'varchar', '', 512, '', '', + '_password', 'varchar', 'NULL', 512, '', '', '_password_encoding', 'varchar', 'NULL', $char_d, '', '', 'sec_phrase', 'varchar', 'NULL', $char_d, '', '', 'popnum', 'int', 'NULL', '', '', '', @@ -1530,7 +1685,7 @@ sub tables_hashref { 'queue' => { 'columns' => [ 'jobnum', 'serial', '', '', '', '', - 'job', 'text', '', '', '', '', + 'job', 'varchar', '', 512, '', '', '_date', 'int', '', '', '', '', 'status', 'varchar', '', $char_d, '', '', 'statustext', 'text', 'NULL', '', '', '', @@ -1550,6 +1705,7 @@ sub tables_hashref { 'columns' => [ 'argnum', 'serial', '', '', '', '', 'jobnum', 'int', '', '', '', '', + 'frozen', 'char', 'NULL', 1, '', '', 'arg', 'text', 'NULL', '', '', '', ], 'primary_key' => 'argnum', @@ -1699,10 +1855,10 @@ sub tables_hashref { 'columns' => [ 'svcnum', 'int', '', '', '', '', 'description', 'varchar', 'NULL', $char_d, '', '', - 'blocknum', 'int', '', '', '', '', + 'blocknum', 'int', 'NULL', '', '', '', 'speed_up', 'int', '', '', '', '', 'speed_down', 'int', '', '', '', '', - 'ip_addr', 'varchar', '', 15, '', '', + 'ip_addr', 'varchar', 'NULL', 15, '', '', 'mac_addr', 'varchar', 'NULL', 12, '', '', 'authkey', 'varchar', 'NULL', 32, '', '', 'latitude', 'decimal', 'NULL', '', '', '', @@ -1793,6 +1949,17 @@ sub tables_hashref { 'index' => [ [ 'pkgpart' ], [ 'optionname' ] ], }, + 'part_pkg_report_option' => { + 'columns' => [ + 'num', 'serial', '', '', '', '', + 'name', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'num', + 'unique' => [ [ 'name' ] ], + 'index' => [ [ 'disabled' ] ], + }, + 'rate' => { 'columns' => [ 'ratenum', 'serial', '', '', '', '', @@ -1841,14 +2008,16 @@ sub tables_hashref { ], 'primary_key' => 'prefixnum', 'unique' => [], - 'index' => [ [ 'countrycode' ], [ 'regionnum' ] ], + 'index' => [ [ 'countrycode' ], [ 'npa' ], [ 'regionnum' ] ], }, 'usage_class' => { 'columns' => [ - 'classnum', 'serial', '', '', '', '', - 'classname', 'varchar', '', $char_d, '', '', - 'disabled', 'char', 'NULL', 1, '', '', + 'classnum', 'serial', '', '', '', '', + 'weight', 'int', 'NULL', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', + 'format', 'varchar', 'NULL', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'classnum', 'unique' => [], @@ -1960,6 +2129,8 @@ sub tables_hashref { 'columns' => [ 'categorynum', 'serial', '', '', '', '', 'categoryname', 'varchar', '', $char_d, '', '', + 'weight', 'int', 'NULL', '', '', '', + 'condense', 'char', 'NULL', 1, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'categorynum', @@ -2023,6 +2194,8 @@ sub tables_hashref { ### # fields for unitel/RSLCOM/convergent that don't map well to asterisk # defaults + # though these are now used elsewhere: + # charged_party, upstream_price, rated_price, carrierid ### #cdr_type: Usage = 1, S&E = 7, OC&C = 8 @@ -2036,7 +2209,7 @@ sub tables_hashref { # how it was rated internally... 'ratedetailnum', 'int', 'NULL', '', '', '', - 'rated_price', 'decimal', 'NULL', '10,2', '', '', + 'rated_price', 'decimal', 'NULL', '10,4', '', '', 'distance', 'decimal', 'NULL', '', '', '', 'islocal', 'int', 'NULL', '', '', '', # '', '', 0, '' instead? @@ -2065,14 +2238,62 @@ sub tables_hashref { #NULL, done (or something) 'freesiderewritestatus', 'varchar', 'NULL', 32, '', '', - 'cdrbatch', 'varchar', 'NULL', $char_d, '', '', + #an indexed place to put big numbers + 'cdrid', 'bigint', 'NULL', '', '', '', + + #old + 'cdrbatch', 'varchar', 'NULL', 255, '', '', + #new + 'cdrbatchnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'acctid', 'unique' => [], - 'index' => [ [ 'calldate' ], [ 'src' ], [ 'dst' ], [ 'charged_party' ], [ 'accountcode' ], [ 'freesidestatus' ], [ 'freesiderewritestatus' ], [ 'cdrbatch' ], ], + 'index' => [ [ 'calldate' ], + [ 'src' ], [ 'dst' ], [ 'dcontext' ], [ 'charged_party' ], + [ 'accountcode' ], [ 'carrierid' ], [ 'cdrid' ], + [ 'freesidestatus' ], [ 'freesiderewritestatus' ], + [ 'cdrbatch' ], + ], + }, + + 'cdr_batch' => { + 'columns' => [ + 'cdrbatchnum', 'serial', '', '', '', '', + 'cdrbatch', 'varchar', 'NULL', 255, '', '', + '_date', @date_type, '', '', + ], + 'primary_key' => 'cdrbatchnum', + 'unique' => [ [ 'cdrbatch' ] ], + 'index' => [], + }, + + 'cdr_termination' => { + 'columns' => [ + 'cdrtermnum', 'bigserial', '', '', '', '', + 'acctid', 'bigint', '', '', '', '', + 'termpart', 'int', '', '', '', '',#future use see below + 'rated_price', 'decimal', 'NULL', '10,4', '', '', + 'status', 'varchar', 'NULL', 32, '', '', + ], + 'primary_key' => 'cdrtermnum', + 'unique' => [ [ 'acctid', 'termpart' ] ], + 'index' => [ [ 'acctid' ], [ 'status' ], ], }, + #to handle multiple termination/settlement passes... + # 'part_termination' => { + # 'columns' => [ + # 'termpart', 'int', '', '', '', '', + # 'termname', 'varchar', '', $char_d, '', '', + # 'cdr_column', 'varchar', '', $char_d, '', '', #maybe set it here instead of in the price plan? + # ], + # 'primary_key' => 'termpart', + # 'unique' => [], + # 'index' => [], + # }, + + #the remaining cdr_ tables are not really used 'cdr_calltype' => { 'columns' => [ 'calltypenum', 'serial', '', '', '', '', @@ -2103,18 +2324,6 @@ sub tables_hashref { 'index' => [], }, - #map upstream rateid to ours... - 'cdr_upstream_rate' => { - 'columns' => [ - 'upstreamratenum', 'serial', '', '', '', '', - 'upstream_rateid', 'varchar', '', $char_d, '', '', - 'ratedetailnum', 'int', 'NULL', '', '', '', - ], - 'primary_key' => 'upstreamratenum', #XXX need a primary key - 'unique' => [ [ 'upstream_rateid' ] ], #unless we add another field, yeah - 'index' => [], - }, - #'cdr_file' => { # 'columns' => [ # 'filenum', 'serial', '', '', '', '', @@ -2237,6 +2446,29 @@ sub tables_hashref { 'index' => [ [ 'countrycode', 'phonenum' ] ], }, + 'phone_device' => { + 'columns' => [ + 'devicenum', 'serial', '', '', '', '', + 'devicepart', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'mac_addr', 'varchar', 'NULL', 12, '', '', + ], + 'primary_key' => 'devicenum', + 'unique' => [ [ 'mac_addr' ], ], + 'index' => [ [ 'devicepart' ], [ 'svcnum' ], ], + }, + + 'part_device' => { + 'columns' => [ + 'devicepart', 'serial', '', '', '', '', + 'devicename', 'varchar', '', $char_d, '', '', + #'classnum', #tie to an inventory class? + ], + 'primary_key' => 'devicepart', + 'unique' => [ [ 'devicename' ] ], #? + 'index' => [], + }, + 'phone_avail' => { 'columns' => [ 'availnum', 'serial', '', '', '', '',