From fadaa67e77ad8d5d966e252aba7f193e9e3840e3 Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 18 Feb 2006 11:14:21 +0000 Subject: [PATCH] CDR schema and class --- FS/FS/Schema.pm | 1004 +++++++++++++++++-------------- FS/FS/cdr.pm | 385 ++++++++++++ FS/FS/cdr_calltype.pm | 115 ++++ FS/FS/cdr_carrier.pm | 116 ++++ FS/FS/cdr_type.pm | 119 ++++ FS/FS/cust_main.pm | 5 +- FS/FS/part_pkg/voip_sqlradacct.pm | 1 + FS/MANIFEST | 11 +- FS/t/cdr.t | 5 + FS/t/cdr_calltype.t | 5 + FS/t/cdr_carrier.t | 5 + FS/t/cdr_type.t | 5 + FS/t/part_pkg-voip_cdr.t | 5 + README.2.0.0 => README.1.7.0 | 2 + bin/cdr_calltype.import | 41 ++ htetc/handler.pl | 9 +- httemplate/misc/cdr-import.html | 15 + httemplate/misc/process/cdr-import.html | 26 + httemplate/search/cdr.html | 20 + httemplate/search/report_cdr.html | 7 + 20 files changed, 1452 insertions(+), 449 deletions(-) create mode 100644 FS/FS/cdr.pm create mode 100644 FS/FS/cdr_calltype.pm create mode 100644 FS/FS/cdr_carrier.pm create mode 100644 FS/FS/cdr_type.pm create mode 100644 FS/t/cdr.t create mode 100644 FS/t/cdr_calltype.t create mode 100644 FS/t/cdr_carrier.t create mode 100644 FS/t/cdr_type.t create mode 100644 FS/t/part_pkg-voip_cdr.t rename README.2.0.0 => README.1.7.0 (93%) create mode 100755 bin/cdr_calltype.import create mode 100644 httemplate/misc/cdr-import.html create mode 100644 httemplate/misc/process/cdr-import.html create mode 100644 httemplate/search/cdr.html create mode 100644 httemplate/search/report_cdr.html diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 33d0fd6d8..3ca599b49 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -3,9 +3,9 @@ package FS::Schema; use vars qw(@ISA @EXPORT_OK $DEBUG $setup_hack %dbdef_cache); use subs qw(reload_dbdef); use Exporter; -use DBIx::DBSchema 0.25; +use DBIx::DBSchema 0.30; use DBIx::DBSchema::Table; -use DBIx::DBSchema::Column; +use DBIx::DBSchema::Column 0.06; use DBIx::DBSchema::ColGroup::Unique; use DBIx::DBSchema::ColGroup::Index; use FS::UID qw(datasrc); @@ -92,9 +92,18 @@ sub dbdef_dist { my $dbdef = new DBIx::DBSchema map { my @columns; while (@{$tables_hashref->{$_}{'columns'}}) { - my($name, $type, $null, $length) = - splice @{$tables_hashref->{$_}{'columns'}}, 0, 4; - push @columns, new DBIx::DBSchema::Column ( $name,$type,$null,$length ); + #my($name, $type, $null, $length, $default, $local) = + my @coldef = + splice @{$tables_hashref->{$_}{'columns'}}, 0, 6; + my %hash = map { $_ => shift @coldef } + qw( name type null length default local ); + + unless ( defined $hash{'default'} ) { + warn "$_:\n". + join('', map "$_ => $hash{$_}\n", keys %hash) ;# $stop = ; + } + + push @columns, new DBIx::DBSchema::Column ( \%hash ); } DBIx::DBSchema::Table->new( $_, @@ -239,15 +248,15 @@ sub tables_hashref { 'agent' => { 'columns' => [ - 'agentnum', 'serial', '', '', - 'agent', 'varchar', '', $char_d, - 'typenum', 'int', '', '', - 'freq', 'int', 'NULL', '', - 'prog', @perl_type, - 'disabled', 'char', 'NULL', 1, - 'username', 'varchar', 'NULL', $char_d, - '_password','varchar', 'NULL', $char_d, - 'ticketing_queueid', 'int', 'NULL', '', + 'agentnum', 'serial', '', '', '', '', + 'agent', 'varchar', '', $char_d, '', '', + 'typenum', 'int', '', '', '', '', + 'freq', 'int', 'NULL', '', '', '', + 'prog', @perl_type, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'username', 'varchar', 'NULL', $char_d, '', '', + '_password','varchar', 'NULL', $char_d, '', '', + 'ticketing_queueid', 'int', 'NULL', '', '', '', ], 'primary_key' => 'agentnum', 'unique' => [], @@ -256,8 +265,8 @@ sub tables_hashref { 'agent_type' => { 'columns' => [ - 'typenum', 'serial', '', '', - 'atype', 'varchar', '', $char_d, + 'typenum', 'serial', '', '', '', '', + 'atype', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'typenum', 'unique' => [], @@ -266,9 +275,9 @@ sub tables_hashref { 'type_pkgs' => { 'columns' => [ - 'typepkgnum', 'serial', '', '', - 'typenum', 'int', '', '', - 'pkgpart', 'int', '', '', + 'typepkgnum', 'serial', '', '', '', '', + 'typenum', 'int', '', '', '', '', + 'pkgpart', 'int', '', '', '', '', ], 'primary_key' => 'typepkgnum', 'unique' => [ ['typenum', 'pkgpart'] ], @@ -277,12 +286,12 @@ sub tables_hashref { 'cust_bill' => { 'columns' => [ - 'invnum', 'serial', '', '', - 'custnum', 'int', '', '', - '_date', @date_type, - 'charged', @money_type, - 'printed', 'int', '', '', - 'closed', 'char', 'NULL', 1, + 'invnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'charged', @money_type, '', '', + 'printed', 'int', '', '', '', '', + 'closed', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'invnum', 'unique' => [], @@ -291,12 +300,12 @@ sub tables_hashref { 'cust_bill_event' => { 'columns' => [ - 'eventnum', 'serial', '', '', - 'invnum', 'int', '', '', - 'eventpart', 'int', '', '', - '_date', @date_type, - 'status', 'varchar', '', $char_d, - 'statustext', 'text', 'NULL', '', + 'eventnum', 'serial', '', '', '', '', + 'invnum', 'int', '', '', '', '', + 'eventpart', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'status', 'varchar', '', $char_d, '', '', + 'statustext', 'text', 'NULL', '', '', '', ], 'primary_key' => 'eventnum', #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ], @@ -306,16 +315,16 @@ sub tables_hashref { 'part_bill_event' => { 'columns' => [ - 'eventpart', 'serial', '', '', - 'freq', 'varchar', 'NULL', $char_d, - 'payby', 'char', '', 4, - 'event', 'varchar', '', $char_d, - 'eventcode', @perl_type, - 'seconds', 'int', 'NULL', '', - 'weight', 'int', '', '', - 'plan', 'varchar', 'NULL', $char_d, - 'plandata', 'text', 'NULL', '', - 'disabled', 'char', 'NULL', 1, + 'eventpart', 'serial', '', '', '', '', + 'freq', 'varchar', 'NULL', $char_d, '', '', + 'payby', 'char', '', 4, '', '', + 'event', 'varchar', '', $char_d, '', '', + 'eventcode', @perl_type, '', '', + 'seconds', 'int', 'NULL', '', '', '', + 'weight', 'int', '', '', '', '', + 'plan', 'varchar', 'NULL', $char_d, '', '', + 'plandata', 'text', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'eventpart', 'unique' => [], @@ -324,14 +333,14 @@ sub tables_hashref { 'cust_bill_pkg' => { 'columns' => [ - 'billpkgnum', 'serial', '', '', - 'pkgnum', 'int', '', '', - 'invnum', 'int', '', '', - 'setup', @money_type, - 'recur', @money_type, - 'sdate', @date_type, - 'edate', @date_type, - 'itemdesc', 'varchar', 'NULL', $char_d, + 'billpkgnum', 'serial', '', '', '', '', + 'pkgnum', 'int', '', '', '', '', + 'invnum', 'int', '', '', '', '', + 'setup', @money_type, '', '', + 'recur', @money_type, '', '', + 'sdate', @date_type, '', '', + 'edate', @date_type, '', '', + 'itemdesc', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'billpkgnum', 'unique' => [], @@ -340,10 +349,10 @@ sub tables_hashref { 'cust_bill_pkg_detail' => { 'columns' => [ - 'detailnum', 'serial', '', '', - 'pkgnum', 'int', '', '', - 'invnum', 'int', '', '', - 'detail', 'varchar', '', $char_d, + 'detailnum', 'serial', '', '', '', '', + 'pkgnum', 'int', '', '', '', '', + 'invnum', 'int', '', '', '', '', + 'detail', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'detailnum', 'unique' => [], @@ -352,13 +361,13 @@ sub tables_hashref { 'cust_credit' => { 'columns' => [ - 'crednum', 'serial', '', '', - 'custnum', 'int', '', '', - '_date', @date_type, - 'amount', @money_type, - 'otaker', 'varchar', '', 32, - 'reason', 'text', 'NULL', '', - 'closed', 'char', 'NULL', 1, + 'crednum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'amount', @money_type, '', '', + 'otaker', 'varchar', '', 32, '', '', + 'reason', 'text', 'NULL', '', '', '', + 'closed', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'crednum', 'unique' => [], @@ -367,11 +376,11 @@ sub tables_hashref { 'cust_credit_bill' => { 'columns' => [ - 'creditbillnum', 'serial', '', '', - 'crednum', 'int', '', '', - 'invnum', 'int', '', '', - '_date', @date_type, - 'amount', @money_type, + 'creditbillnum', 'serial', '', '', '', '', + 'crednum', 'int', '', '', '', '', + 'invnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'amount', @money_type, '', '', ], 'primary_key' => 'creditbillnum', 'unique' => [], @@ -380,54 +389,54 @@ sub tables_hashref { 'cust_main' => { 'columns' => [ - 'custnum', 'serial', '', '', - 'agentnum', 'int', '', '', -# 'titlenum', 'int', 'NULL', '', - 'last', 'varchar', '', $char_d, -# 'middle', 'varchar', 'NULL', $char_d, - 'first', 'varchar', '', $char_d, - 'ss', 'varchar', 'NULL', 11, - 'company', 'varchar', 'NULL', $char_d, - 'address1', 'varchar', '', $char_d, - 'address2', 'varchar', 'NULL', $char_d, - 'city', 'varchar', '', $char_d, - 'county', 'varchar', 'NULL', $char_d, - 'state', 'varchar', 'NULL', $char_d, - 'zip', 'varchar', 'NULL', 10, - 'country', 'char', '', 2, - 'daytime', 'varchar', 'NULL', 20, - 'night', 'varchar', 'NULL', 20, - 'fax', 'varchar', 'NULL', 12, - 'ship_last', 'varchar', 'NULL', $char_d, -# 'ship_middle', 'varchar', 'NULL', $char_d, - 'ship_first', 'varchar', 'NULL', $char_d, - 'ship_company', 'varchar', 'NULL', $char_d, - 'ship_address1', 'varchar', 'NULL', $char_d, - 'ship_address2', 'varchar', 'NULL', $char_d, - 'ship_city', 'varchar', 'NULL', $char_d, - 'ship_county', 'varchar', 'NULL', $char_d, - 'ship_state', 'varchar', 'NULL', $char_d, - 'ship_zip', 'varchar', 'NULL', 10, - 'ship_country', 'char', 'NULL', 2, - 'ship_daytime', 'varchar', 'NULL', 20, - 'ship_night', 'varchar', 'NULL', 20, - 'ship_fax', 'varchar', 'NULL', 12, - 'payby', 'char', '', 4, - 'payinfo', 'varchar', 'NULL', 512, - 'paycvv', 'varchar', 'NULL', 512, - 'paymask', 'varchar', 'NULL', $char_d, - #'paydate', @date_type, - 'paydate', 'varchar', 'NULL', 10, - 'paystart_month', 'int', 'NULL', '', - 'paystart_year', 'int', 'NULL', '', - 'payissue', 'varchar', 'NULL', 2, - 'payname', 'varchar', 'NULL', $char_d, - 'payip', 'varchar', 'NULL', 15, - 'tax', 'char', 'NULL', 1, - 'otaker', 'varchar', '', 32, - 'refnum', 'int', '', '', - 'referral_custnum', 'int', 'NULL', '', - 'comments', 'text', 'NULL', '', + 'custnum', 'serial', '', '', '', '', + 'agentnum', 'int', '', '', '', '', +# 'titlenum', 'int', 'NULL', '', '', '', + 'last', 'varchar', '', $char_d, '', '', +# 'middle', 'varchar', 'NULL', $char_d, '', '', + 'first', 'varchar', '', $char_d, '', '', + 'ss', 'varchar', 'NULL', 11, '', '', + 'company', 'varchar', 'NULL', $char_d, '', '', + 'address1', 'varchar', '', $char_d, '', '', + 'address2', 'varchar', 'NULL', $char_d, '', '', + 'city', 'varchar', '', $char_d, '', '', + 'county', 'varchar', 'NULL', $char_d, '', '', + 'state', 'varchar', 'NULL', $char_d, '', '', + 'zip', 'varchar', 'NULL', 10, '', '', + 'country', 'char', '', 2, '', '', + 'daytime', 'varchar', 'NULL', 20, '', '', + 'night', 'varchar', 'NULL', 20, '', '', + 'fax', 'varchar', 'NULL', 12, '', '', + 'ship_last', 'varchar', 'NULL', $char_d, '', '', +# 'ship_middle', 'varchar', 'NULL', $char_d, '', '', + 'ship_first', 'varchar', 'NULL', $char_d, '', '', + 'ship_company', 'varchar', 'NULL', $char_d, '', '', + 'ship_address1', 'varchar', 'NULL', $char_d, '', '', + 'ship_address2', 'varchar', 'NULL', $char_d, '', '', + 'ship_city', 'varchar', 'NULL', $char_d, '', '', + 'ship_county', 'varchar', 'NULL', $char_d, '', '', + 'ship_state', 'varchar', 'NULL', $char_d, '', '', + 'ship_zip', 'varchar', 'NULL', 10, '', '', + 'ship_country', 'char', 'NULL', 2, '', '', + 'ship_daytime', 'varchar', 'NULL', 20, '', '', + 'ship_night', 'varchar', 'NULL', 20, '', '', + 'ship_fax', 'varchar', 'NULL', 12, '', '', + 'payby', 'char', '', 4, '', '', + 'payinfo', 'varchar', 'NULL', 512, '', '', + 'paycvv', 'varchar', 'NULL', 512, '', '', + 'paymask', 'varchar', 'NULL', $char_d, '', '', + #'paydate', @date_type, '', '', + 'paydate', 'varchar', 'NULL', 10, '', '', + 'paystart_month', 'int', 'NULL', '', '', '', + 'paystart_year', 'int', 'NULL', '', '', '', + 'payissue', 'varchar', 'NULL', 2, '', '', + 'payname', 'varchar', 'NULL', $char_d, '', '', + 'payip', 'varchar', 'NULL', 15, '', '', + 'tax', 'char', 'NULL', 1, '', '', + 'otaker', 'varchar', '', 32, '', '', + 'refnum', 'int', '', '', '', '', + 'referral_custnum', 'int', 'NULL', '', '', '', + 'comments', 'text', 'NULL', '', '', '', ], 'primary_key' => 'custnum', 'unique' => [], @@ -440,9 +449,9 @@ sub tables_hashref { 'cust_main_invoice' => { 'columns' => [ - 'destnum', 'serial', '', '', - 'custnum', 'int', '', '', - 'dest', 'varchar', '', $char_d, + 'destnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'dest', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'destnum', 'unique' => [], @@ -453,16 +462,16 @@ sub tables_hashref { #cust_main_county for validation and to provide # a tax rate. 'columns' => [ - 'taxnum', 'serial', '', '', - 'state', 'varchar', 'NULL', $char_d, - 'county', 'varchar', 'NULL', $char_d, - 'country', 'char', '', 2, - 'taxclass', 'varchar', 'NULL', $char_d, - 'exempt_amount', @money_type, - 'tax', 'real', '', '', #tax % - 'taxname', 'varchar', 'NULL', $char_d, - 'setuptax', 'char', 'NULL', 1, # Y = setup tax exempt - 'recurtax', 'char', 'NULL', 1, # Y = recur tax exempt + 'taxnum', 'serial', '', '', '', '', + 'state', 'varchar', 'NULL', $char_d, '', '', + 'county', 'varchar', 'NULL', $char_d, '', '', + 'country', 'char', '', 2, '', '', + 'taxclass', 'varchar', 'NULL', $char_d, '', '', + 'exempt_amount', @money_type, '', '', + 'tax', 'real', '', '', '', '', #tax % + 'taxname', 'varchar', 'NULL', $char_d, '', '', + 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt + 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt ], 'primary_key' => 'taxnum', 'unique' => [], @@ -472,16 +481,17 @@ sub tables_hashref { 'cust_pay' => { 'columns' => [ - 'paynum', 'serial', '', '', - #now cust_bill_pay #'invnum', 'int', '', '', - 'custnum', 'int', '', '', - 'paid', @money_type, - '_date', @date_type, - 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into - # payment type table. - 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above - 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes. - 'closed', 'char', 'NULL', 1, + 'paynum', 'serial', '', '', '', '', + #now cust_bill_pay #'invnum', 'int', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'paid', @money_type, '', '', + '_date', @date_type, '', '', + 'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should be + # index into payby table + # eventually + 'payinfo', 'varchar', 'NULL', $char_d, '', '', #see cust_main above + 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. + 'closed', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'paynum', 'unique' => [], @@ -490,18 +500,19 @@ sub tables_hashref { 'cust_pay_void' => { 'columns' => [ - 'paynum', 'int', '', '', - 'custnum', 'int', '', '', - 'paid', @money_type, - '_date', @date_type, - 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index into - # payment type table. - 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above - 'paybatch', 'varchar', 'NULL', $char_d, #for auditing purposes. - 'closed', 'char', 'NULL', 1, - 'void_date', @date_type, - 'reason', 'varchar', 'NULL', $char_d, - 'otaker', 'varchar', '', 32, + 'paynum', 'int', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'paid', @money_type, '', '', + '_date', @date_type, '', '', + 'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should be + # index into payby table + # eventually + 'payinfo', 'varchar', 'NULL', $char_d, '', '', #see cust_main above + 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes. + 'closed', 'char', 'NULL', 1, '', '', + 'void_date', @date_type, '', '', + 'reason', 'varchar', 'NULL', $char_d, '', '', + 'otaker', 'varchar', '', 32, '', '', ], 'primary_key' => 'paynum', 'unique' => [], @@ -510,11 +521,11 @@ sub tables_hashref { 'cust_bill_pay' => { 'columns' => [ - 'billpaynum', 'serial', '', '', - 'invnum', 'int', '', '', - 'paynum', 'int', '', '', - 'amount', @money_type, - '_date', @date_type + 'billpaynum', 'serial', '', '', '', '', + 'invnum', 'int', '', '', '', '', + 'paynum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + '_date', @date_type, '', '', ], 'primary_key' => 'billpaynum', 'unique' => [], @@ -524,23 +535,23 @@ sub tables_hashref { 'cust_pay_batch' => { #what's this used for again? list of customers #in current CARD batch? (necessarily CARD?) 'columns' => [ - 'paybatchnum', 'serial', '', '', - 'invnum', 'int', '', '', - 'custnum', '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, - 'country', 'char', '', 2, -# 'trancode', 'int', '', '', - 'cardnum', 'varchar', '', 16, - #'exp', @date_type, - 'exp', 'varchar', '', 11, - 'payname', 'varchar', 'NULL', $char_d, - 'amount', @money_type, + 'paybatchnum', 'serial', '', '', '', '', + 'invnum', 'int', '', '', '', '', + 'custnum', '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, '', '', + 'country', 'char', '', 2, '', '', + # 'trancode', 'int', '', '', '', '' + 'cardnum', 'varchar', '', 16, '', '', + #'exp', @date_type, '', '' + 'exp', 'varchar', '', 11, '', '', + 'payname', 'varchar', 'NULL', $char_d, '', '', + 'amount', @money_type, '', '', ], 'primary_key' => 'paybatchnum', 'unique' => [], @@ -549,17 +560,17 @@ sub tables_hashref { 'cust_pkg' => { 'columns' => [ - 'pkgnum', 'serial', '', '', - 'custnum', 'int', '', '', - 'pkgpart', 'int', '', '', - 'otaker', 'varchar', '', 32, - 'setup', @date_type, - 'bill', @date_type, - 'last_bill', @date_type, - 'susp', @date_type, - 'cancel', @date_type, - 'expire', @date_type, - 'manual_flag', 'char', 'NULL', 1, + 'pkgnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'pkgpart', 'int', '', '', '', '', + 'otaker', 'varchar', '', 32, '', '', + 'setup', @date_type, '', '', + 'bill', @date_type, '', '', + 'last_bill', @date_type, '', '', + 'susp', @date_type, '', '', + 'cancel', @date_type, '', '', + 'expire', @date_type, '', '', + 'manual_flag', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'pkgnum', 'unique' => [], @@ -568,18 +579,19 @@ sub tables_hashref { 'cust_refund' => { 'columns' => [ - 'refundnum', 'serial', '', '', - #now cust_credit_refund #'crednum', 'int', '', '', - 'custnum', 'int', '', '', - '_date', @date_type, - 'refund', @money_type, - 'otaker', 'varchar', '', 32, - 'reason', 'varchar', '', $char_d, - 'payby', 'char', '', 4, # CARD/BILL/COMP, should be index - # into payment type table. - 'payinfo', 'varchar', 'NULL', $char_d, #see cust_main above - 'paybatch', 'varchar', 'NULL', $char_d, - 'closed', 'char', 'NULL', 1, + 'refundnum', 'serial', '', '', '', '', + #now cust_credit_refund #'crednum', 'int', '', '', '', '', + 'custnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'refund', @money_type, '', '', + 'otaker', 'varchar', '', 32, '', '', + 'reason', 'varchar', '', $char_d, '', '', + 'payby', 'char', '', 4, '', '', # CARD/BILL/COMP, should + # be index into payby + # table eventually + 'payinfo', 'varchar', 'NULL', $char_d, '', '', #see cust_main above + 'paybatch', 'varchar', 'NULL', $char_d, '', '', + 'closed', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'refundnum', 'unique' => [], @@ -588,11 +600,11 @@ sub tables_hashref { 'cust_credit_refund' => { 'columns' => [ - 'creditrefundnum', 'serial', '', '', - 'crednum', 'int', '', '', - 'refundnum', 'int', '', '', - 'amount', @money_type, - '_date', @date_type + 'creditrefundnum', 'serial', '', '', '', '', + 'crednum', 'int', '', '', '', '', + 'refundnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + '_date', @date_type, '', '', ], 'primary_key' => 'creditrefundnum', 'unique' => [], @@ -602,9 +614,9 @@ sub tables_hashref { 'cust_svc' => { 'columns' => [ - 'svcnum', 'serial', '', '', - 'pkgnum', 'int', 'NULL', '', - 'svcpart', 'int', '', '', + 'svcnum', 'serial', '', '', '', '', + 'pkgnum', 'int', 'NULL', '', '', '', + 'svcpart', 'int', '', '', '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -613,20 +625,20 @@ sub tables_hashref { 'part_pkg' => { 'columns' => [ - 'pkgpart', 'serial', '', '', - 'pkg', 'varchar', '', $char_d, - 'comment', 'varchar', '', $char_d, - 'promo_code', 'varchar', 'NULL', $char_d, - 'setup', @perl_type, - 'freq', 'varchar', '', $char_d, #billing frequency - 'recur', @perl_type, - 'setuptax', 'char', 'NULL', 1, - 'recurtax', 'char', 'NULL', 1, - 'plan', 'varchar', 'NULL', $char_d, - 'plandata', 'text', 'NULL', '', - 'disabled', 'char', 'NULL', 1, - 'taxclass', 'varchar', 'NULL', $char_d, - 'classnum', 'int', 'NULL', '', + 'pkgpart', 'serial', '', '', '', '', + 'pkg', 'varchar', '', $char_d, '', '', + 'comment', 'varchar', '', $char_d, '', '', + 'promo_code', 'varchar', 'NULL', $char_d, '', '', + 'setup', @perl_type, '', '', + 'freq', 'varchar', '', $char_d, '', '', #billing frequency + 'recur', @perl_type, '', '', + 'setuptax', 'char', 'NULL', 1, '', '', + 'recurtax', 'char', 'NULL', 1, '', '', + 'plan', 'varchar', 'NULL', $char_d, '', '', + 'plandata', 'text', 'NULL', '', '', '', + 'disabled', 'char', 'NULL', 1, '', '', + 'taxclass', 'varchar', 'NULL', $char_d, '', '', + 'classnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'pkgpart', 'unique' => [], @@ -645,11 +657,11 @@ sub tables_hashref { 'pkg_svc' => { 'columns' => [ - 'pkgsvcnum', 'serial', '', '', - 'pkgpart', 'int', '', '', - 'svcpart', 'int', '', '', - 'quantity', 'int', '', '', - 'primary_svc','char', 'NULL', 1, + 'pkgsvcnum', 'serial', '', '', '', '', + 'pkgpart', 'int', '', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'quantity', 'int', '', '', '', '', + 'primary_svc','char', 'NULL', 1, '', '', ], 'primary_key' => 'pkgsvcnum', 'unique' => [ ['pkgpart', 'svcpart'] ], @@ -658,9 +670,9 @@ sub tables_hashref { 'part_referral' => { 'columns' => [ - 'refnum', 'serial', '', '', - 'referral', 'varchar', '', $char_d, - 'disabled', 'char', 'NULL', 1, + 'refnum', 'serial', '', '', '', '', + 'referral', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'refnum', 'unique' => [], @@ -669,10 +681,10 @@ sub tables_hashref { 'part_svc' => { 'columns' => [ - 'svcpart', 'serial', '', '', - 'svc', 'varchar', '', $char_d, - 'svcdb', 'varchar', '', $char_d, - 'disabled', 'char', 'NULL', 1, + 'svcpart', 'serial', '', '', '', '', + 'svc', 'varchar', '', $char_d, '', '', + 'svcdb', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'svcpart', 'unique' => [], @@ -681,11 +693,11 @@ sub tables_hashref { 'part_svc_column' => { 'columns' => [ - 'columnnum', 'serial', '', '', - 'svcpart', 'int', '', '', - 'columnname', 'varchar', '', 64, - 'columnvalue', 'varchar', 'NULL', $char_d, - 'columnflag', 'char', 'NULL', 1, + 'columnnum', 'serial', '', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'columnname', 'varchar', '', 64, '', '', + 'columnvalue', 'varchar', 'NULL', $char_d, '', '', + 'columnflag', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'columnnum', 'unique' => [ [ 'svcpart', 'columnname' ] ], @@ -695,12 +707,12 @@ sub tables_hashref { #(this should be renamed to part_pop) 'svc_acct_pop' => { 'columns' => [ - 'popnum', 'serial', '', '', - 'city', 'varchar', '', $char_d, - 'state', 'varchar', '', $char_d, - 'ac', 'char', '', 3, - 'exch', 'char', '', 3, - 'loc', 'char', 'NULL', 4, #NULL for legacy purposes + 'popnum', 'serial', '', '', '', '', + 'city', 'varchar', '', $char_d, '', '', + 'state', 'varchar', '', $char_d, '', '', + 'ac', 'char', '', 3, '', '', + 'exch', 'char', '', 3, '', '', + 'loc', 'char', 'NULL', 4, '', '', #NULL for legacy purposes ], 'primary_key' => 'popnum', 'unique' => [], @@ -709,12 +721,12 @@ sub tables_hashref { 'part_pop_local' => { 'columns' => [ - 'localnum', 'serial', '', '', - 'popnum', 'int', '', '', - 'city', 'varchar', 'NULL', $char_d, - 'state', 'char', 'NULL', 2, - 'npa', 'char', '', 3, - 'nxx', 'char', '', 3, + 'localnum', 'serial', '', '', '', '', + 'popnum', 'int', '', '', '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', + 'state', 'char', 'NULL', 2, '', '', + 'npa', 'char', '', 3, '', '', + 'nxx', 'char', '', 3, '', '', ], 'primary_key' => 'localnum', 'unique' => [], @@ -723,20 +735,20 @@ sub tables_hashref { 'svc_acct' => { 'columns' => [ - 'svcnum', 'int', '', '', - 'username', 'varchar', '', $username_len, #unique (& remove dup code) - '_password', 'varchar', '', 72, #13 for encryped pw's plus ' *SUSPENDED* (md5 passwords can be 34, blowfish 60) - 'sec_phrase', 'varchar', 'NULL', $char_d, - 'popnum', 'int', 'NULL', '', - 'uid', 'int', 'NULL', '', - 'gid', 'int', 'NULL', '', - 'finger', 'varchar', 'NULL', $char_d, - 'dir', 'varchar', 'NULL', $char_d, - 'shell', 'varchar', 'NULL', $char_d, - 'quota', 'varchar', 'NULL', $char_d, - 'slipip', 'varchar', 'NULL', 15, #four TINYINTs, bah. - 'seconds', 'int', 'NULL', '', #uhhhh - 'domsvc', 'int', '', '', + 'svcnum', 'int', '', '', '', '', + 'username', 'varchar', '', $username_len, '', '', #unique (& remove dup code) + '_password', 'varchar', '', 72, '', '', #13 for encryped pw's plus ' *SUSPENDED* (md5 passwords can be 34, blowfish 60) + 'sec_phrase', 'varchar', 'NULL', $char_d, '', '', + 'popnum', 'int', 'NULL', '', '', '', + 'uid', 'int', 'NULL', '', '', '', + 'gid', 'int', 'NULL', '', '', '', + 'finger', 'varchar', 'NULL', $char_d, '', '', + 'dir', 'varchar', 'NULL', $char_d, '', '', + 'shell', 'varchar', 'NULL', $char_d, '', '', + 'quota', 'varchar', 'NULL', $char_d, '', '', + 'slipip', 'varchar', 'NULL', 15, '', '', #four TINYINTs, bah. + 'seconds', 'int', 'NULL', '', '', '', #uhhhh + 'domsvc', 'int', '', '', '', '', ], 'primary_key' => 'svcnum', #'unique' => [ [ 'username', 'domsvc' ] ], @@ -756,9 +768,9 @@ sub tables_hashref { 'svc_domain' => { 'columns' => [ - 'svcnum', 'int', '', '', - 'domain', 'varchar', '', $char_d, - 'catchall', 'int', 'NULL', '', + 'svcnum', 'int', '', '', '', '', + 'domain', 'varchar', '', $char_d, '', '', + 'catchall', 'int', 'NULL', '', '', '', ], 'primary_key' => 'svcnum', 'unique' => [ ['domain'] ], @@ -767,14 +779,12 @@ sub tables_hashref { 'domain_record' => { 'columns' => [ - 'recnum', 'serial', '', '', - 'svcnum', 'int', '', '', - #'reczone', 'varchar', '', $char_d, - 'reczone', 'varchar', '', 255, - 'recaf', 'char', '', 2, - 'rectype', 'varchar', '', 5, - #'recdata', 'varchar', '', $char_d, - 'recdata', 'varchar', '', 255, + 'recnum', 'serial', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'reczone', 'varchar', '', 255, '', '', + 'recaf', 'char', '', 2, '', '', + 'rectype', 'varchar', '', 5, '', '', + 'recdata', 'varchar', '', 255, '', '', ], 'primary_key' => 'recnum', 'unique' => [], @@ -783,11 +793,11 @@ sub tables_hashref { 'svc_forward' => { 'columns' => [ - 'svcnum', 'int', '', '', - 'srcsvc', 'int', 'NULL', '', - 'src', 'varchar', 'NULL', 255, - 'dstsvc', 'int', 'NULL', '', - 'dst', 'varchar', 'NULL', 255, + 'svcnum', 'int', '', '', '', '', + 'srcsvc', 'int', 'NULL', '', '', '', + 'src', 'varchar', 'NULL', 255, '', '', + 'dstsvc', 'int', 'NULL', '', '', '', + 'dst', 'varchar', 'NULL', 255, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -796,9 +806,9 @@ sub tables_hashref { 'svc_www' => { 'columns' => [ - 'svcnum', 'int', '', '', - 'recnum', 'int', '', '', - 'usersvc', 'int', '', '', + 'svcnum', 'int', '', '', '', '', + 'recnum', 'int', '', '', '', '', + 'usersvc', 'int', '', '', '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -820,11 +830,11 @@ sub tables_hashref { 'prepay_credit' => { 'columns' => [ - 'prepaynum', 'serial', '', '', - 'identifier', 'varchar', '', $char_d, - 'amount', @money_type, - 'seconds', 'int', 'NULL', '', - 'agentnum', 'int', 'NULL', '', + 'prepaynum', 'serial', '', '', '', '', + 'identifier', 'varchar', '', $char_d, '', '', + 'amount', @money_type, '', '', + 'seconds', 'int', 'NULL', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'prepaynum', 'unique' => [ ['identifier'] ], @@ -833,10 +843,10 @@ sub tables_hashref { 'port' => { 'columns' => [ - 'portnum', 'serial', '', '', - 'ip', 'varchar', 'NULL', 15, - 'nasport', 'int', 'NULL', '', - 'nasnum', 'int', '', '', + 'portnum', 'serial', '', '', '', '', + 'ip', 'varchar', 'NULL', 15, '', '', + 'nasport', 'int', 'NULL', '', '', '', + 'nasnum', 'int', '', '', '', '', ], 'primary_key' => 'portnum', 'unique' => [], @@ -845,11 +855,11 @@ sub tables_hashref { 'nas' => { 'columns' => [ - 'nasnum', 'serial', '', '', - 'nas', 'varchar', '', $char_d, - 'nasip', 'varchar', '', 15, - 'nasfqdn', 'varchar', '', $char_d, - 'last', 'int', '', '', + 'nasnum', 'serial', '', '', '', '', + 'nas', 'varchar', '', $char_d, '', '', + 'nasip', 'varchar', '', 15, '', '', + 'nasfqdn', 'varchar', '', $char_d, '', '', + 'last', 'int', '', '', '', '', ], 'primary_key' => 'nasnum', 'unique' => [ [ 'nas' ], [ 'nasip' ] ], @@ -858,11 +868,11 @@ sub tables_hashref { 'session' => { 'columns' => [ - 'sessionnum', 'serial', '', '', - 'portnum', 'int', '', '', - 'svcnum', 'int', '', '', - 'login', @date_type, - 'logout', @date_type, + 'sessionnum', 'serial', '', '', '', '', + 'portnum', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'login', @date_type, '', '', + 'logout', @date_type, '', '', ], 'primary_key' => 'sessionnum', 'unique' => [], @@ -871,12 +881,12 @@ sub tables_hashref { 'queue' => { 'columns' => [ - 'jobnum', 'serial', '', '', - 'job', 'text', '', '', - '_date', 'int', '', '', - 'status', 'varchar', '', $char_d, - 'statustext', 'text', 'NULL', '', - 'svcnum', 'int', 'NULL', '', + 'jobnum', 'serial', '', '', '', '', + 'job', 'text', '', '', '', '', + '_date', 'int', '', '', '', '', + 'status', 'varchar', '', $char_d, '', '', + 'statustext', 'text', 'NULL', '', '', '', + 'svcnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'jobnum', 'unique' => [], @@ -885,9 +895,9 @@ sub tables_hashref { 'queue_arg' => { 'columns' => [ - 'argnum', 'serial', '', '', - 'jobnum', 'int', '', '', - 'arg', 'text', 'NULL', '', + 'argnum', 'serial', '', '', '', '', + 'jobnum', 'int', '', '', '', '', + 'arg', 'text', 'NULL', '', '', '', ], 'primary_key' => 'argnum', 'unique' => [], @@ -896,9 +906,9 @@ sub tables_hashref { 'queue_depend' => { 'columns' => [ - 'dependnum', 'serial', '', '', - 'jobnum', 'int', '', '', - 'depend_jobnum', 'int', '', '', + 'dependnum', 'serial', '', '', '', '', + 'jobnum', 'int', '', '', '', '', + 'depend_jobnum', 'int', '', '', '', '', ], 'primary_key' => 'dependnum', 'unique' => [], @@ -907,9 +917,9 @@ sub tables_hashref { 'export_svc' => { 'columns' => [ - 'exportsvcnum' => 'serial', '', '', - 'exportnum' => 'int', '', '', - 'svcpart' => 'int', '', '', + 'exportsvcnum' => 'serial', '', '', '', '', + 'exportnum' => 'int', '', '', '', '', + 'svcpart' => 'int', '', '', '', '', ], 'primary_key' => 'exportsvcnum', 'unique' => [ [ 'exportnum', 'svcpart' ] ], @@ -918,11 +928,10 @@ sub tables_hashref { 'part_export' => { 'columns' => [ - 'exportnum', 'serial', '', '', - #'svcpart', 'int', '', '', - 'machine', 'varchar', '', $char_d, - 'exporttype', 'varchar', '', $char_d, - 'nodomain', 'char', 'NULL', 1, + 'exportnum', 'serial', '', '', '', '', + 'machine', 'varchar', '', $char_d, '', '', + 'exporttype', 'varchar', '', $char_d, '', '', + 'nodomain', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'exportnum', 'unique' => [], @@ -931,10 +940,10 @@ sub tables_hashref { 'part_export_option' => { 'columns' => [ - 'optionnum', 'serial', '', '', - 'exportnum', 'int', '', '', - 'optionname', 'varchar', '', $char_d, - 'optionvalue', 'text', 'NULL', '', + 'optionnum', 'serial', '', '', '', '', + 'exportnum', 'int', '', '', '', '', + 'optionname', 'varchar', '', $char_d, '', '', + 'optionvalue', 'text', 'NULL', '', '', '', ], 'primary_key' => 'optionnum', 'unique' => [], @@ -943,9 +952,9 @@ sub tables_hashref { 'radius_usergroup' => { 'columns' => [ - 'usergroupnum', 'serial', '', '', - 'svcnum', 'int', '', '', - 'groupname', 'varchar', '', $char_d, + 'usergroupnum', 'serial', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'groupname', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'usergroupnum', 'unique' => [], @@ -954,10 +963,10 @@ sub tables_hashref { 'msgcat' => { 'columns' => [ - 'msgnum', 'serial', '', '', - 'msgcode', 'varchar', '', $char_d, - 'locale', 'varchar', '', 16, - 'msg', 'text', '', '', + 'msgnum', 'serial', '', '', '', '', + 'msgcode', 'varchar', '', $char_d, '', '', + 'locale', 'varchar', '', 16, '', '', + 'msg', 'text', '', '', '', '', ], 'primary_key' => 'msgnum', 'unique' => [ [ 'msgcode', 'locale' ] ], @@ -966,12 +975,12 @@ sub tables_hashref { 'cust_tax_exempt' => { 'columns' => [ - 'exemptnum', 'serial', '', '', - 'custnum', 'int', '', '', - 'taxnum', 'int', '', '', - 'year', 'int', '', '', - 'month', 'int', '', '', - 'amount', @money_type, + 'exemptnum', 'serial', '', '', '', '', + 'custnum', 'int', '', '', '', '', + 'taxnum', 'int', '', '', '', '', + 'year', 'int', '', '', '', '', + 'month', 'int', '', '', '', '', + 'amount', @money_type, '', '', ], 'primary_key' => 'exemptnum', 'unique' => [ [ 'custnum', 'taxnum', 'year', 'month' ] ], @@ -980,13 +989,13 @@ sub tables_hashref { 'cust_tax_exempt_pkg' => { 'columns' => [ - 'exemptpkgnum', 'serial', '', '', - #'custnum', 'int', '', '', - 'billpkgnum', 'int', '', '', - 'taxnum', 'int', '', '', - 'year', 'int', '', '', - 'month', 'int', '', '', - 'amount', @money_type, + 'exemptpkgnum', 'serial', '', '', '', '', + #'custnum', 'int', '', '', '', '' + 'billpkgnum', 'int', '', '', '', '', + 'taxnum', 'int', '', '', '', '', + 'year', 'int', '', '', '', '', + 'month', 'int', '', '', '', '', + 'amount', @money_type, '', '', ], 'primary_key' => 'exemptpkgnum', 'unique' => [], @@ -998,9 +1007,9 @@ sub tables_hashref { 'router' => { 'columns' => [ - 'routernum', 'serial', '', '', - 'routername', 'varchar', '', $char_d, - 'svcnum', 'int', 'NULL', '', + 'routernum', 'serial', '', '', '', '', + 'routername', 'varchar', '', $char_d, '', '', + 'svcnum', 'int', 'NULL', '', '', '', ], 'primary_key' => 'routernum', 'unique' => [], @@ -1009,9 +1018,9 @@ sub tables_hashref { 'part_svc_router' => { 'columns' => [ - 'svcrouternum', 'serial', '', '', - 'svcpart', 'int', '', '', - 'routernum', 'int', '', '', + 'svcrouternum', 'serial', '', '', '', '', + 'svcpart', 'int', '', '', '', '', + 'routernum', 'int', '', '', '', '', ], 'primary_key' => 'svcrouternum', 'unique' => [], @@ -1020,10 +1029,10 @@ sub tables_hashref { 'addr_block' => { 'columns' => [ - 'blocknum', 'serial', '', '', - 'routernum', 'int', '', '', - 'ip_gateway', 'varchar', '', 15, - 'ip_netmask', 'int', '', '', + 'blocknum', 'serial', '', '', '', '', + 'routernum', 'int', '', '', '', '', + 'ip_gateway', 'varchar', '', 15, '', '', + 'ip_netmask', 'int', '', '', '', '', ], 'primary_key' => 'blocknum', 'unique' => [ [ 'blocknum', 'routernum' ] ], @@ -1032,11 +1041,11 @@ sub tables_hashref { 'svc_broadband' => { 'columns' => [ - 'svcnum', 'int', '', '', - 'blocknum', 'int', '', '', - 'speed_up', 'int', '', '', - 'speed_down', 'int', '', '', - 'ip_addr', 'varchar', '', 15, + 'svcnum', 'int', '', '', '', '', + 'blocknum', 'int', '', '', '', '', + 'speed_up', 'int', '', '', '', '', + 'speed_down', 'int', '', '', '', '', + 'ip_addr', 'varchar', '', 15, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -1045,13 +1054,13 @@ sub tables_hashref { 'part_virtual_field' => { 'columns' => [ - 'vfieldpart', 'int', '', '', - 'dbtable', 'varchar', '', 32, - 'name', 'varchar', '', 32, - 'check_block', 'text', 'NULL', '', - 'length', 'int', 'NULL', '', - 'list_source', 'text', 'NULL', '', - 'label', 'varchar', 'NULL', 80, + 'vfieldpart', 'int', '', '', '', '', + 'dbtable', 'varchar', '', 32, '', '', + 'name', 'varchar', '', 32, '', '', + 'check_block', 'text', 'NULL', '', '', '', + 'length', 'int', 'NULL', '', '', '', + 'list_source', 'text', 'NULL', '', '', '', + 'label', 'varchar', 'NULL', 80, '', '', ], 'primary_key' => 'vfieldpart', 'unique' => [], @@ -1060,10 +1069,10 @@ sub tables_hashref { 'virtual_field' => { 'columns' => [ - 'vfieldnum', 'serial', '', '', - 'recnum', 'int', '', '', - 'vfieldpart', 'int', '', '', - 'value', 'varchar', '', 128, + 'vfieldnum', 'serial', '', '', '', '', + 'recnum', 'int', '', '', '', '', + 'vfieldpart', 'int', '', '', '', '', + 'value', 'varchar', '', 128, '', '', ], 'primary_key' => 'vfieldnum', 'unique' => [ [ 'vfieldpart', 'recnum' ] ], @@ -1072,12 +1081,12 @@ 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', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'machine', 'varchar', '', 255, '', '', + 'protocol', 'varchar', '', $char_d, '', '', + 'username', 'varchar', '', $char_d, '', '', + '_password', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'snarfnum', 'unique' => [], @@ -1086,9 +1095,9 @@ sub tables_hashref { 'svc_external' => { 'columns' => [ - 'svcnum', 'int', '', '', - 'id', 'int', 'NULL', '', - 'title', 'varchar', 'NULL', $char_d, + 'svcnum', 'int', '', '', '', '', + 'id', 'int', 'NULL', '', '', '', + 'title', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'svcnum', 'unique' => [], @@ -1097,11 +1106,11 @@ sub tables_hashref { 'cust_pay_refund' => { 'columns' => [ - 'payrefundnum', 'serial', '', '', - 'paynum', 'int', '', '', - 'refundnum', 'int', '', '', - '_date', @date_type, - 'amount', @money_type, + 'payrefundnum', 'serial', '', '', '', '', + 'paynum', 'int', '', '', '', '', + 'refundnum', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'amount', @money_type, '', '', ], 'primary_key' => 'payrefundnum', 'unique' => [], @@ -1110,10 +1119,10 @@ sub tables_hashref { 'part_pkg_option' => { 'columns' => [ - 'optionnum', 'serial', '', '', - 'pkgpart', 'int', '', '', - 'optionname', 'varchar', '', $char_d, - 'optionvalue', 'text', 'NULL', '', + 'optionnum', 'serial', '', '', '', '', + 'pkgpart', 'int', '', '', '', '', + 'optionname', 'varchar', '', $char_d, '', '', + 'optionvalue', 'text', 'NULL', '', '', '', ], 'primary_key' => 'optionnum', 'unique' => [], @@ -1122,8 +1131,8 @@ sub tables_hashref { 'rate' => { 'columns' => [ - 'ratenum', 'serial', '', '', - 'ratename', 'varchar', '', $char_d, + 'ratenum', 'serial', '', '', '', '', + 'ratename', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'ratenum', 'unique' => [], @@ -1132,13 +1141,13 @@ sub tables_hashref { 'rate_detail' => { 'columns' => [ - 'ratedetailnum', 'serial', '', '', - 'ratenum', 'int', '', '', - 'orig_regionnum', 'int', 'NULL', '', - 'dest_regionnum', 'int', '', '', - 'min_included', 'int', '', '', - 'min_charge', @money_type, - 'sec_granularity', 'int', '', '', + 'ratedetailnum', 'serial', '', '', '', '', + 'ratenum', 'int', '', '', '', '', + 'orig_regionnum', 'int', 'NULL', '', '', '', + 'dest_regionnum', 'int', '', '', '', '', + 'min_included', 'int', '', '', '', '', + 'min_charge', @money_type, '', '', + 'sec_granularity', 'int', '', '', '', '', #time period (link to table of periods)? ], 'primary_key' => 'ratedetailnum', @@ -1148,8 +1157,8 @@ sub tables_hashref { 'rate_region' => { 'columns' => [ - 'regionnum', 'serial', '', '', - 'regionname', 'varchar', '', $char_d, + 'regionnum', 'serial', '', '', '', '', + 'regionname', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'regionnum', 'unique' => [], @@ -1158,11 +1167,11 @@ sub tables_hashref { 'rate_prefix' => { 'columns' => [ - 'prefixnum', 'serial', '', '', - 'regionnum', 'int', '', '',, - 'countrycode', 'varchar', '', 3, - 'npa', 'varchar', 'NULL', 6, - 'nxx', 'varchar', 'NULL', 3, + 'prefixnum', 'serial', '', '', '', '', + 'regionnum', 'int', '', '',, '', '', + 'countrycode', 'varchar', '', 3, '', '', + 'npa', 'varchar', 'NULL', 6, '', '', + 'nxx', 'varchar', 'NULL', 3, '', '', ], 'primary_key' => 'prefixnum', 'unique' => [], @@ -1171,9 +1180,9 @@ sub tables_hashref { 'reg_code' => { 'columns' => [ - 'codenum', 'serial', '', '', - 'code', 'varchar', '', $char_d, - 'agentnum', 'int', '', '', + 'codenum', 'serial', '', '', '', '', + 'code', 'varchar', '', $char_d, '', '', + 'agentnum', 'int', '', '', '', '', ], 'primary_key' => 'codenum', 'unique' => [ [ 'agentnum', 'code' ] ], @@ -1182,9 +1191,9 @@ sub tables_hashref { 'reg_code_pkg' => { 'columns' => [ - 'codepkgnum', 'serial', '', '', - 'codenum', 'int', '', '', - 'pkgpart', 'int', '', '', + 'codepkgnum', 'serial', '', '', '', '', + 'codenum', 'int', '', '', '', '', + 'pkgpart', 'int', '', '', '', '', ], 'primary_key' => 'codepkgnum', 'unique' => [ [ 'codenum', 'pkgpart' ] ], @@ -1193,9 +1202,9 @@ sub tables_hashref { 'clientapi_session' => { 'columns' => [ - 'sessionnum', 'serial', '', '', - 'sessionid', 'varchar', '', $char_d, - 'namespace', 'varchar', '', $char_d, + 'sessionnum', 'serial', '', '', '', '', + 'sessionid', 'varchar', '', $char_d, '', '', + 'namespace', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'sessionnum', 'unique' => [ [ 'sessionid', 'namespace' ] ], @@ -1204,10 +1213,10 @@ sub tables_hashref { 'clientapi_session_field' => { 'columns' => [ - 'fieldnum', 'serial', '', '', - 'sessionnum', 'int', '', '', - 'fieldname', 'varchar', '', $char_d, - 'fieldvalue', 'text', 'NULL', '', + 'fieldnum', 'serial', '', '', '', '', + 'sessionnum', 'int', '', '', '', '', + 'fieldname', 'varchar', '', $char_d, '', '', + 'fieldvalue', 'text', 'NULL', '', '', '', ], 'primary_key' => 'fieldnum', 'unique' => [ [ 'sessionnum', 'fieldname' ] ], @@ -1216,12 +1225,12 @@ sub tables_hashref { 'payment_gateway' => { 'columns' => [ - 'gatewaynum', 'serial', '', '', - 'gateway_module', 'varchar', '', $char_d, - 'gateway_username', 'varchar', 'NULL', $char_d, - 'gateway_password', 'varchar', 'NULL', $char_d, - 'gateway_action', 'varchar', 'NULL', $char_d, - 'disabled', 'char', 'NULL', 1, + 'gatewaynum', 'serial', '', '', '', '', + 'gateway_module', 'varchar', '', $char_d, '', '', + 'gateway_username', 'varchar', 'NULL', $char_d, '', '', + 'gateway_password', 'varchar', 'NULL', $char_d, '', '', + 'gateway_action', 'varchar', 'NULL', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'gatewaynum', 'unique' => [], @@ -1230,10 +1239,10 @@ sub tables_hashref { 'payment_gateway_option' => { 'columns' => [ - 'optionnum', 'serial', '', '', - 'gatewaynum', 'int', '', '', - 'optionname', 'varchar', '', $char_d, - 'optionvalue', 'text', 'NULL', '', + 'optionnum', 'serial', '', '', '', '', + 'gatewaynum', 'int', '', '', '', '', + 'optionname', 'varchar', '', $char_d, '', '', + 'optionvalue', 'text', 'NULL', '', '', '', ], 'primary_key' => 'optionnum', 'unique' => [], @@ -1242,11 +1251,11 @@ sub tables_hashref { 'agent_payment_gateway' => { 'columns' => [ - 'agentgatewaynum', 'serial', '', '', - 'agentnum', 'int', '', '', - 'gatewaynum', 'int', '', '', - 'cardtype', 'varchar', 'NULL', $char_d, - 'taxclass', 'varchar', 'NULL', $char_d, + 'agentgatewaynum', 'serial', '', '', '', '', + 'agentnum', 'int', '', '', '', '', + 'gatewaynum', 'int', '', '', '', '', + 'cardtype', 'varchar', 'NULL', $char_d, '', '', + 'taxclass', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'agentgatewaynum', 'unique' => [], @@ -1255,13 +1264,13 @@ sub tables_hashref { 'banned_pay' => { 'columns' => [ - 'bannum', 'serial', '', '', - 'payby', 'char', '', 4, - 'payinfo', 'varchar', '', 128, #say, a 512-big digest _hex encoded - #'paymask', 'varchar', 'NULL', $char_d, - '_date', @date_type, - 'otaker', 'varchar', '', 32, - 'reason', 'varchar', 'NULL', $char_d, + 'bannum', 'serial', '', '', '', '', + 'payby', 'char', '', 4, '', '', + 'payinfo', 'varchar', '', 128, '', '', #say, a 512-big digest _hex encoded + #'paymask', 'varchar', 'NULL', $char_d, '', '' + '_date', @date_type, '', '', + 'otaker', 'varchar', '', 32, '', '', + 'reason', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'bannum', 'unique' => [ [ 'payby', 'payinfo' ] ], @@ -1270,9 +1279,9 @@ sub tables_hashref { 'cancel_reason' => { 'columns' => [ - 'reasonnum', 'serial', '', '', - 'reason', 'varchar', '', $char_d, - 'disabled', 'char', 'NULL', 1, + 'reasonnum', 'serial', '', '', '', '', + 'reason', 'varchar', '', $char_d, '', '', + 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'reasonnum', 'unique' => [], @@ -1281,14 +1290,129 @@ sub tables_hashref { 'pkg_class' => { 'columns' => [ - 'classnum', 'serial', '', '', - 'classname', 'varchar', '', $char_d, + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', '', $char_d, '', '', ], 'primary_key' => 'classnum', 'unique' => [], 'index' => [], }, + 'cdr' => { + 'columns' => [ + # qw( name type null length default local ); + + ### + #asterisk fields + ### + + 'acctid', 'bigserial', '', '', '', '', + 'calldate', 'TIMESTAMP with time zone', '', '', \'now()', '', + 'clid', 'varchar', '', $char_d, \"''", '', + 'src', 'varchar', '', $char_d, \"''", '', + 'dst', 'varchar', '', $char_d, \"''", '', + 'dcontext', 'varchar', '', $char_d, \"''", '', + 'channel', 'varchar', '', $char_d, \"''", '', + 'dstchannel', 'varchar', '', $char_d, \"''", '', + 'lastapp', 'varchar', '', $char_d, \"''", '', + 'lastdata', 'varchar', '', $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? + # and, what they hey, i went ahead and changed the names and data types + # to freeside-style dates... + #'start', 'timestamp', 'NULL', '', '', '', + #'answer', 'timestamp', 'NULL', '', '', '', + #'end', 'timestamp', 'NULL', '', '', '', + 'startdate', @date_type, '', '', + 'answerdate', @date_type, '', '', + 'enddate', @date_type, '', '', + # + + 'duration', 'int', '', '', 0, '', + 'billsec', 'int', '', '', 0, '', + 'disposition', 'varchar', '', 45, \"''", '', + 'amaflags', 'int', '', '', 0, '', + 'accountcode', 'varchar', '', 20, \"''", '', + 'uniqueid', 'varchar', '', 32, \"''", '', + 'userfield', 'varchar', '', 255, \"''", '', + + ### + # fields for unitel/RSLCOM/convergent that don't map well to asterisk + # defaults + ### + + #cdr_type: Usage = 1, S&E = 7, OC&C = 8 + 'cdrtypenum', 'int', 'NULL', '', '', '', + + 'charged_party', 'varchar', 'NULL', $char_d, '', '', + + 'upstream_currency', 'char', 'NULL', 3, '', '', + 'upstream_price', 'decimal', 'NULL', '10,2', '', '', + 'upstream_rateplanid', 'int', 'NULL', '', '', '', #? + + 'distance', 'decimal', 'NULL', '', '', '', + 'islocal', 'int', 'NULL', '', '', '', # '', '', 0, '' instead? + + #cdr_calltype: the big list in appendix 2 + 'calltypenum', 'int', 'NULL', '', '', '', + + 'description', 'varchar', 'NULL', $char_d, '', '', + 'quantity', 'int', 'NULL', '', '', '', + + #cdr_carrier: Telstra =1, Optus = 2, RSL COM = 3 + 'carrierid', 'int', 'NULL', '', '', '', + + 'upstream_rateid', 'int', 'NULL', '', '', '', + + ### + #and now for our own fields + ### + + # a svcnum... right..? + 'svcnum', 'int', 'NULL', '', '', '', + + #NULL, done, skipped, pushed_downstream (or something) + 'freesidestatus', 'varchar', 'NULL', 32, '', '', + + ], + 'primary_key' => 'acctid', + 'unique' => [], + 'index' => [ [ 'calldate' ], [ 'dst' ], [ 'accountcode' ], [ 'freesidestatus' ] ], + }, + + 'cdr_calltype' => { + 'columns' => [ + 'calltypenum', 'serial', '', '', '', '', + 'calltypename', 'varchar', '', $char_d, '', '', + ], + 'primary_key' => 'calltypenum', + 'unique' => [], + 'index' => [], + }, + + 'cdr_type' => { + 'columns' => [ + 'cdrtypenum' => 'serial', '', '', '', '', + 'cdrtypename' => 'varchar', '', '', '', '', + ], + 'primary_key' => 'cdrtypenum', + 'unique' => [], + 'index' => [], + }, + + 'cdr_carrier' => { + 'columns' => [ + 'carrierid' => 'serial', '', '', '', '', + 'carriername' => 'varchar', '', '', '', '', + ], + 'primary_key' => 'carrierid', + 'unique' => [], + 'index' => [], + }, + + }; } diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm new file mode 100644 index 000000000..2d40177f5 --- /dev/null +++ b/FS/FS/cdr.pm @@ -0,0 +1,385 @@ +package FS::cdr; + +use strict; +use vars qw( @ISA ); +use Date::Parse; +use FS::UID qw( dbh ); +use FS::Record qw( qsearch qsearchs ); +use FS::cdr_type; +use FS::cdr_calltype; +use FS::cdr_carrier; + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::cdr - Object methods for cdr records + +=head1 SYNOPSIS + + use FS::cdr; + + $record = new FS::cdr \%hash; + $record = new FS::cdr { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::cdr object represents an Call Data Record, typically from a telephony +system or provider of some sort. FS::cdr inherits from FS::Record. The +following fields are currently supported: + +=over 4 + +=item acctid - primary key + +=item calldate - Call timestamp (SQL timestamp) + +=item clid - Caller*ID with text + +=item src - Caller*ID number / Source number + +=item dst - Destination extension + +=item dcontext - Destination context + +=item channel - Channel used + +=item dstchannel - Destination channel if appropriate + +=item lastapp - Last application if appropriate + +=item lastdata - Last application data + +=item startdate - Start of call (UNIX-style integer timestamp) + +=item answerdate - Answer time of call (UNIX-style integer timestamp) + +=item enddate - End time of call (UNIX-style integer timestamp) + +=item duration - Total time in system, in seconds + +=item billsec - Total time call is up, in seconds + +=item disposition - What happened to the call: ANSWERED, NO ANSWER, BUSY + +=item amaflags - What flags to use: BILL, IGNORE etc, specified on a per channel basis like accountcode. + +=cut + + #ignore the "omit" and "documentation" AMAs?? + #AMA = Automated Message Accounting. + #default: Sets the system default. + #omit: Do not record calls. + #billing: Mark the entry for billing + #documentation: Mark the entry for documentation. + +=back + +=item accountcode - CDR account number to use: account + +=item uniqueid - Unique channel identifier (Unitel/RSLCOM Event ID) + +=item userfield - CDR user-defined field + +=item cdr_type - CDR type - see L (Usage = 1, S&E = 7, OC&C = 8) + +=item charged_party - Service number to be billed + +=item upstream_currency - Wholesale currency from upstream + +=item upstream_price - Wholesale price from upstream + +=item upstream_rateplanid - Upstream rate plan ID + +=item distance - km (need units field?) + +=item islocal - Local - 1, Non Local = 0 + +=item calltypenum - Type of call - see L + +=item description - Description (cdr_type 7&8 only) (used for cust_bill_pkg.itemdesc) + +=item quantity - Number of items (cdr_type 7&8 only) + +=item carrierid - Upstream Carrier ID (see L) + +=cut + +#Telstra =1, Optus = 2, RSL COM = 3 + +=back + +=item upstream_rateid - Upstream Rate ID + +=item svcnum - Link to customer service (see L) + +=item freesidestatus - NULL, done, skipped, pushed_downstream (or something) + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new CDR. To add the CDR to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'cdr'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +# the insert method can be inherited from FS::Record + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +# the replace method can be inherited from FS::Record + +=item check + +Checks all fields to make sure this is a valid CDR. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +Note: Unlike most types of records, we don't want to "reject" a CDR and we want +to process them as quickly as possible, so we allow the database to check most +of the data. + +=cut + +sub check { + my $self = shift; + +# we don't want to "reject" a CDR like other sorts of input... +# my $error = +# $self->ut_numbern('acctid') +## || $self->ut_('calldate') +# || $self->ut_text('clid') +# || $self->ut_text('src') +# || $self->ut_text('dst') +# || $self->ut_text('dcontext') +# || $self->ut_text('channel') +# || $self->ut_text('dstchannel') +# || $self->ut_text('lastapp') +# || $self->ut_text('lastdata') +# || $self->ut_numbern('startdate') +# || $self->ut_numbern('answerdate') +# || $self->ut_numbern('enddate') +# || $self->ut_number('duration') +# || $self->ut_number('billsec') +# || $self->ut_text('disposition') +# || $self->ut_number('amaflags') +# || $self->ut_text('accountcode') +# || $self->ut_text('uniqueid') +# || $self->ut_text('userfield') +# || $self->ut_numbern('cdrtypenum') +# || $self->ut_textn('charged_party') +## || $self->ut_n('upstream_currency') +## || $self->ut_n('upstream_price') +# || $self->ut_numbern('upstream_rateplanid') +## || $self->ut_n('distance') +# || $self->ut_numbern('islocal') +# || $self->ut_numbern('calltypenum') +# || $self->ut_textn('description') +# || $self->ut_numbern('quantity') +# || $self->ut_numbern('carrierid') +# || $self->ut_numbern('upstream_rateid') +# || $self->ut_numbern('svcnum') +# || $self->ut_textn('freesidestatus') +# ; +# return $error if $error; + + #check the foreign keys even? + #do we want to outright *reject* the CDR? + my $error = + $self->ut_numbern('acctid') + + #Usage = 1, S&E = 7, OC&C = 8 + || $self->ut_foreign_keyn('cdrtypenum', 'cdr_type', 'cdrtypenum' ) + + #the big list in appendix 2 + || $self->ut_foreign_keyn('calltypenum', 'cdr_calltype', 'calltypenum' ) + + # Telstra =1, Optus = 2, RSL COM = 3 + || $self->ut_foreign_keyn('carrierid', 'cdr_carrier', 'carrierid' ) + ; + return $error if $error; + + $self->SUPER::check; +} + +my %formats = ( + 'asterisk' => [ + 'accountcode', + 'src', + 'dst', + 'dcontext', + 'clid', + 'channel', + 'dstchannel', + 'lastapp', + 'lastdata', + 'startdate', # XXX will need massaging + 'answer', # XXX same + 'end', # XXX same + 'duration', + 'billsec', + 'disposition', + 'amaflags', + 'uniqueid', + 'userfield', + ], + 'unitel' => [ + 'uniqueid', + 'cdr_type', + 'calldate', # XXX may need massaging + 'billsec', #XXX duration and billsec? + # sub { $_[0]->billsec( $_[1] ); + # $_[0]->duration( $_[1] ); + # }, + 'src', + 'dst', + 'charged_party', + 'upstream_currency', + 'upstream_price', + 'upstream_rateplanid', + 'distance', + 'islocal', + 'calltypenum', + 'startdate', # XXX will definitely need massaging + 'enddate', # XXX same + 'description', + 'quantity', + 'carrierid', + 'upstream_rateid', + ] +); + +sub batch_import { + my $param = shift; + + my $fh = $param->{filehandle}; + my $format = $param->{format}; + + return "Unknown format $format" unless exists $formats{$format}; + + eval "use Text::CSV_XS;"; + die $@ if $@; + + my $csv = new Text::CSV_XS; + + my $imported = 0; + #my $columns; + + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + my $line; + while ( defined($line=<$fh>) ) { + + $csv->parse($line) or do { + $dbh->rollback if $oldAutoCommit; + return "can't parse: ". $csv->error_input(); + }; + + my @columns = $csv->fields(); + #warn join('-',@columns); + + my @later = (); + my %cdr = + map { + + my $field_or_sub = $_; + if ( ref($field_or_sub) ) { + push @later, $field_or_sub, shift(@columns); + (); + } else { + ( $field_or_sub => shift @columns ); + } + + } + @{ $formats{$format} } + ; + + my $cdr = new FS::cdr ( \%cdr ); + + while ( scalar(@later) ) { + my $sub = shift @later; + my $data = shift @later; + &{$sub}($cdr, $data); # $cdr->&{$sub}($data); + } + + my $error = $cdr->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + + #or just skip? + #next; + } + + $imported++; + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + + #might want to disable this if we skip records for any reason... + return "Empty file!" unless $imported; + + ''; + +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/FS/cdr_calltype.pm b/FS/FS/cdr_calltype.pm new file mode 100644 index 000000000..fe456086f --- /dev/null +++ b/FS/FS/cdr_calltype.pm @@ -0,0 +1,115 @@ +package FS::cdr_calltype; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( qsearch qsearchs ); + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::cdr_calltype - Object methods for cdr_calltype records + +=head1 SYNOPSIS + + use FS::cdr_calltype; + + $record = new FS::cdr_calltype \%hash; + $record = new FS::cdr_calltype { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::cdr_calltype object represents an CDR call type. FS::cdr_calltype +inherits from FS::Record. The following fields are currently supported: + +=over 4 + +=item calltypenum - primary key + +=item calltypename - CDR call type name + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new call type. To add the call type to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'cdr_calltype'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +# the insert method can be inherited from FS::Record + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +# the replace method can be inherited from FS::Record + +=item check + +Checks all fields to make sure this is a valid call type. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('calltypenum') + || $self->ut_text('calltypename') + ; + return $error if $error; + + $self->SUPER::check; +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/FS/cdr_carrier.pm b/FS/FS/cdr_carrier.pm new file mode 100644 index 000000000..609c93923 --- /dev/null +++ b/FS/FS/cdr_carrier.pm @@ -0,0 +1,116 @@ +package FS::cdr_carrier; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( qsearch qsearchs ); + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::cdr_carrier - Object methods for cdr_carrier records + +=head1 SYNOPSIS + + use FS::cdr_carrier; + + $record = new FS::cdr_carrier \%hash; + $record = new FS::cdr_carrier { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::cdr_carrier object represents an CDR carrier or upstream. +FS::cdr_carrier inherits from FS::Record. The following fields are currently +supported: + +=over 4 + +=item carrierid - primary key + +=item carriername - Carrier name + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new carrier. To add the carrier to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'cdr_carrier'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +# the insert method can be inherited from FS::Record + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +# the replace method can be inherited from FS::Record + +=item check + +Checks all fields to make sure this is a valid carrier. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('carrierid') + || $self->ut_text('carriername') + ; + return $error if $error; + + $self->SUPER::check; +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/FS/cdr_type.pm b/FS/FS/cdr_type.pm new file mode 100644 index 000000000..e258bf878 --- /dev/null +++ b/FS/FS/cdr_type.pm @@ -0,0 +1,119 @@ +package FS::cdr_type; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( qsearch qsearchs ); + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::cdr_type - Object methods for cdr_type records + +=head1 SYNOPSIS + + use FS::cdr_type; + + $record = new FS::cdr_type \%hash; + $record = new FS::cdr_type { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::cdr_type object represents an CDR type. FS::cdr_type inherits from +FS::Record. The following fields are currently supported: + +=over 4 + +=item cdrtypenum - primary key + +=item typename - CDR type name + + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new CDR type. To add the CDR type to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'cdr_type'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +# the insert method can be inherited from FS::Record + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +# the replace method can be inherited from FS::Record + +=item check + +Checks all fields to make sure this is a valid CDR type. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +# the check method should currently be supplied - FS::Record contains some +# data checking routines + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('cdrtypenum') + || $self->ut_text('typename') + ; + return $error if $error; + + $self->SUPER::check; +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 973fe7c81..50faeb422 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -16,6 +16,7 @@ BEGIN { } use Digest::MD5 qw(md5_base64); use Date::Format; +use Date::Parse; #use Date::Manip; use String::Approx qw(amatch); use Business::CreditCard 0.28; @@ -3964,8 +3965,6 @@ sub batch_import { my $pkgpart = $param->{pkgpart}; my @fields = @{$param->{fields}}; - eval "use Date::Parse;"; - die $@ if $@; eval "use Text::CSV_XS;"; die $@ if $@; @@ -4071,8 +4070,6 @@ sub batch_charge { my $fh = $param->{filehandle}; my @fields = @{$param->{fields}}; - eval "use Date::Parse;"; - die $@ if $@; eval "use Text::CSV_XS;"; die $@ if $@; diff --git a/FS/FS/part_pkg/voip_sqlradacct.pm b/FS/FS/part_pkg/voip_sqlradacct.pm index fd9c1ddb5..bf18003ab 100644 --- a/FS/FS/part_pkg/voip_sqlradacct.pm +++ b/FS/FS/part_pkg/voip_sqlradacct.pm @@ -41,6 +41,7 @@ sub calc_setup { $self->option('setup_fee'); } +#false laziness w/voip_cdr... resolve it if this one ever gets used again sub calc_recur { my($self, $cust_pkg, $sdate, $details ) = @_; diff --git a/FS/MANIFEST b/FS/MANIFEST index 452041e35..f6f833589 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -122,6 +122,7 @@ FS/part_pkg/sql_generic.pm FS/part_pkg/sqlradacct_hour.pm FS/part_pkg/subscription.pm FS/part_pkg/voip_sqlradacct.pm +FS/part_pkg/voip_cdr.pm FS/part_pop_local.pm FS/part_referral.pm FS/part_svc.pm @@ -261,7 +262,7 @@ t/part_pkg-sql_external.t t/part_pkg-sql_generic.t t/part_pkg-sqlradacct_hour.t t/part_pkg-subscription.t -t/part_pkg-voip_sqlradacct.t +t/part_pkg-voip_cdr.t t/part_pop_local.t t/part_referral.t t/part_svc.t @@ -308,3 +309,11 @@ t/banned_pay.t FS/cancel_reason.pm t/cancel_reason.t bin/freeside-prepaidd +FS/cdr.pm +t/cdr.t +FS/cdr_calltype.pm +t/cdr_calltype.t +FS/cdr_type.pm +t/cdr_type.t +FS/cdr_carrier.pm +t/cdr_carrier.t diff --git a/FS/t/cdr.t b/FS/t/cdr.t new file mode 100644 index 000000000..1d1f3eb4e --- /dev/null +++ b/FS/t/cdr.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::cdr; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/cdr_calltype.t b/FS/t/cdr_calltype.t new file mode 100644 index 000000000..d4e13943e --- /dev/null +++ b/FS/t/cdr_calltype.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::cdr_calltype; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/cdr_carrier.t b/FS/t/cdr_carrier.t new file mode 100644 index 000000000..1e2161558 --- /dev/null +++ b/FS/t/cdr_carrier.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::cdr_carrier; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/cdr_type.t b/FS/t/cdr_type.t new file mode 100644 index 000000000..9dff15a32 --- /dev/null +++ b/FS/t/cdr_type.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::cdr_type; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/part_pkg-voip_cdr.t b/FS/t/part_pkg-voip_cdr.t new file mode 100644 index 000000000..2d988a34f --- /dev/null +++ b/FS/t/part_pkg-voip_cdr.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::part_pkg::voip_cdr; +$loaded=1; +print "ok 1\n"; diff --git a/README.2.0.0 b/README.1.7.0 similarity index 93% rename from README.2.0.0 rename to README.1.7.0 index 3f5f1158f..834972ae2 100644 --- a/README.2.0.0 +++ b/README.1.7.0 @@ -1,4 +1,6 @@ +install DBIx::DBSchema 0.29 (or later) + make install-perl-modules run "freeside-upgrade username" to uprade your database schema diff --git a/bin/cdr_calltype.import b/bin/cdr_calltype.import new file mode 100755 index 000000000..a998284f6 --- /dev/null +++ b/bin/cdr_calltype.import @@ -0,0 +1,41 @@ +#!/usr/bin/perl -w +# +# bin/cdr_calltype.import ivan ~ivan/convergent/newspecs/fixed_inbound/calltypes.csv + +use strict; +use FS::UID qw(dbh adminsuidsetup); +use FS::cdr_calltype; + +my $user = shift or die &usage; +adminsuidsetup $user; + +while (<>) { + + chomp; + my $line = $_; + + #$line =~ /^(\d+),"([^"]+)"$/ or do { + $line =~ /^(\d+),"([^"]+)"/ or do { + warn "unparsable line: $line\n"; + next; + }; + + my $cdr_calltype = new FS::cdr_calltype { + 'calltypenum' => $1, + 'calltypename' => $2, + }; + + #my $error = $cdr_calltype->check; + my $error = $cdr_calltype->insert; + if ( $error ) { + warn "********** $error FOR LINE: $line\n"; + dbh->commit; + #my $wait = scalar(); + } + +} + +sub usage { + "Usage:\n\ncdr_calltype.import username filename ...\n"; +} + diff --git a/htetc/handler.pl b/htetc/handler.pl index 06060b20a..1bbea16d1 100644 --- a/htetc/handler.pl +++ b/htetc/handler.pl @@ -88,7 +88,7 @@ sub handler #rar { package HTML::Mason::Commands; use strict; - use vars qw( $cgi $p ); + use vars qw( $cgi $p $fsurl); use vars qw( %session ); use CGI 2.47 qw(-private_tempfiles); #use CGI::Carp qw(fatalsToBrowser); @@ -118,8 +118,8 @@ sub handler use FS::UID qw(cgisuidsetup dbh getotaker datasrc driver_name); use FS::Record qw(qsearch qsearchs fields dbdef); use FS::Conf; - use FS::CGI qw(header menubar popurl table itable ntable idiot eidiot - small_custview myexit http_header); + use FS::CGI qw(header menubar popurl rooturl table itable ntable idiot + eidiot small_custview myexit http_header); use FS::UI::Web; use FS::Msgcat qw(gettext geterror); use FS::Misc qw( send_email send_fax ); @@ -172,6 +172,7 @@ sub handler use FS::agent_payment_gateway; use FS::XMLRPC; use FS::payby; + use FS::cdr; if ( %%%RT_ENABLED%%% ) { eval ' @@ -234,9 +235,9 @@ sub handler &cgisuidsetup($cgi); #&cgisuidsetup($r); $p = popurl(2); + $fsurl = rooturl(); } - sub include { use vars qw($m); $m->scomp(@_); diff --git a/httemplate/misc/cdr-import.html b/httemplate/misc/cdr-import.html new file mode 100644 index 000000000..dc1733249 --- /dev/null +++ b/httemplate/misc/cdr-import.html @@ -0,0 +1,15 @@ +<%= include("/elements/header.html",'Call Detail Record Import') %> +
+Import a CSV file containing Call Detail Records (CDRs).

+CDR Format:

+ +Filename:

+ + +
+ +<%= include('/elements/footer.html') %> + diff --git a/httemplate/misc/process/cdr-import.html b/httemplate/misc/process/cdr-import.html new file mode 100644 index 000000000..381b07820 --- /dev/null +++ b/httemplate/misc/process/cdr-import.html @@ -0,0 +1,26 @@ +<% + + my $fh = $cgi->upload('csvfile'); + + my $error = defined($fh) + ? FS::cdr::batch_import( { + 'filehandle' => $fh, + 'format' => $cgi->param('format'), + } ) + : 'No file'; + + if ( $error ) { + %> + + <% + eidiot($error); +# $cgi->param('error', $error); +# print $cgi->redirect( "${p}cust_main-import.cgi + } else { + %> + + <%= include("/elements/header.html",'Import sucessful') %> + + <%= include("/elements/footer.html",'Import sucessful') %> <% + } +%> diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html new file mode 100644 index 000000000..75049b443 --- /dev/null +++ b/httemplate/search/cdr.html @@ -0,0 +1,20 @@ +<% + +my $hashref = {}; +#process params for CDR search, populate $hashref... + +my $count_query = 'SELECT COUNT(*) FROM cdr'; +# and fixup $count_query + +%><%= include( 'elements/search.html', + 'title' => 'Call Detail Records', + 'name' => 'call detail records', + 'query' => { 'table' => 'cdr', + 'hashref' => $hashref + }, + 'count_query' => $count_query, + 'header' => [ fields('cdr') ], #XXX fill in some nice names + 'fields' => [ fields('cdr') ], #XXX fill in some pretty-print + # processing, etc. + ) +%> diff --git a/httemplate/search/report_cdr.html b/httemplate/search/report_cdr.html new file mode 100644 index 000000000..b9ad55e10 --- /dev/null +++ b/httemplate/search/report_cdr.html @@ -0,0 +1,7 @@ +<%= include('/elements/header.html', 'Call Detail Record Search' ) %> + +
+ + +<%= include('/elements/footer.html') %> + -- 2.11.0