From 69678d308805f5ca4b171ea0c5ac1da957811aa0 Mon Sep 17 00:00:00 2001 From: ivan Date: Thu, 2 Jul 2009 11:22:48 +0000 Subject: [PATCH] settlement cdr processing, RT#5495 --- FS/FS/Schema.pm | 2 +- FS/FS/cdr.pm | 2 +- FS/FS/cdr_termination.pm | 28 ++++---- FS/FS/part_pkg/cdr_termination.pm | 124 +++++++++++++++++++++++++++++++-- httemplate/edit/cust_main/billing.html | 13 ++-- httemplate/search/cdr.html | 79 ++++++++++++++++----- httemplate/search/report_cdr.html | 18 +++++ 7 files changed, 220 insertions(+), 46 deletions(-) diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 1ae37197f..52e119083 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -2142,7 +2142,7 @@ sub tables_hashref { ], 'primary_key' => 'acctid', 'unique' => [], - 'index' => [ [ 'calldate' ], [ 'src' ], [ 'dst' ], [ 'charged_party' ], [ 'accountcode' ], [ 'freesidestatus' ], [ 'freesiderewritestatus' ], [ 'cdrbatch' ], ], + 'index' => [ [ 'calldate' ], [ 'src' ], [ 'dst' ], [ 'charged_party' ], [ 'accountcode' ], [ 'carrierid' ], [ 'freesidestatus' ], [ 'freesiderewritestatus' ], [ 'cdrbatch' ], ], }, 'cdr_termination' => { diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm index ab87a6893..7b1ee7a7e 100644 --- a/FS/FS/cdr.pm +++ b/FS/FS/cdr.pm @@ -527,8 +527,8 @@ my %export_formats = ( sub { time2str('%D', shift->calldate_unix ) }, #DATE sub { time2str('%r', shift->calldate_unix ) }, #TIME #'userfield', #USER - 'dst', #NUMBER_DIALED 'src', #called from + 'dst', #NUMBER_DIALED $duration_sub, #DURATION #sub { sprintf('%.3f', shift->upstream_price ) }, #PRICE sub { my($cdr, %opt) = @_; $opt{money_char}. $opt{charge}; }, #PRICE diff --git a/FS/FS/cdr_termination.pm b/FS/FS/cdr_termination.pm index 5fe8db6bf..e0cde6e97 100644 --- a/FS/FS/cdr_termination.pm +++ b/FS/FS/cdr_termination.pm @@ -117,26 +117,26 @@ sub check { #|| $self->ut_foreign_key('termpart', 'part_termination', 'termpart') || $self->ut_number('termpart') || $self->ut_float('rated_price') - || $self->ut_enum('status', '', 'done' ) # , 'skipped' ) + || $self->ut_enum('status', [ '', 'done' ] ) # , 'skipped' ] ) ; return $error if $error; $self->SUPER::check; } -=item set_status_and_rated_price STATUS [ RATED_PRICE ] - -Sets the status to the provided string. If there is an error, returns the -error, otherwise returns false. - -=cut - -sub set_status_and_rated_price { - my($self, $status, $rated_price) = @_; - $self->status($status); - $self->rated_price($rated_price); - $self->replace(); -} +#=item set_status_and_rated_price STATUS [ RATED_PRICE ] +# +#Sets the status to the provided string. If there is an error, returns the +#error, otherwise returns false. +# +#=cut +# +#sub set_status_and_rated_price { +# my($self, $status, $rated_price) = @_; +# $self->status($status); +# $self->rated_price($rated_price); +# $self->replace(); +#} =back diff --git a/FS/FS/part_pkg/cdr_termination.pm b/FS/FS/part_pkg/cdr_termination.pm index c0d99b72a..15103378e 100644 --- a/FS/FS/part_pkg/cdr_termination.pm +++ b/FS/FS/part_pkg/cdr_termination.pm @@ -4,6 +4,9 @@ use strict; use base qw( FS::part_pkg::recur_Common ); use vars qw( $DEBUG %info ); use Tie::IxHash; +use FS::Record qw( qsearch ); #qsearchs ); +use FS::cdr; +use FS::cdr_termination; tie my %temporalities, 'Tie::IxHash', 'upcoming' => "Upcoming (future)", @@ -22,6 +25,24 @@ tie my %temporalities, 'Tie::IxHash', 'default' => 0, }, + #'cdr_column' => { 'name' => 'Column from CDR records', + # 'type' => 'select', + # 'select_enum' => [qw( + # dcontext + # channel + # dstchannel + # lastapp + # lastdata + # accountcode + # userfield + # cdrtypenum + # calltypenum + # description + # carrierid + # upstream_rateid + # )], + # }, + #false laziness w/flat.pm 'recur_temporality' => { 'name' => 'Charge recurring fee for period', 'type' => 'select', @@ -44,11 +65,28 @@ tie my %temporalities, 'Tie::IxHash', 'type' => 'select', 'select_options' => \%FS::part_pkg::recur_Common::recur_method, }, + + #false laziness w/cdr_termination.pm + 'output_format' => { 'name' => 'CDR invoice display format', + 'type' => 'select', + 'select_options' => { FS::cdr::invoice_formats() }, + 'default' => 'simple2', #XXX test + }, + + 'usage_section' => { 'name' => 'Section in which to place separate usage charges', + }, + + 'summarize_usage' => { 'name' => 'Include usage summary with recurring charges when usage is in separate section', + 'type' => 'checkbox', + }, + }, 'fieldorder' => [qw( - setup_fee recur_fee recur_temporality unused_credit - recur_method cutoff_day + setup_fee recur_fee + cdr_column + recur_temporality unused_credit recur_method cutoff_day + output_format usage_section summarize_usage ) ], @@ -72,16 +110,88 @@ sub calc_recur { if $self->option('recur_temporality', 1) eq 'preceding' && ( $last_bill eq '' || $last_bill == 0 ); + # termination calculations + + my $term_percent = $cust_pkg->cust_main->cdr_termination_percentage; + die "no customer termination percentage" unless $term_percent; + + my $output_format = $self->option('output_format', 'Hush!') || 'simple2'; + my $charges = 0; - # termination calculations + #find an svc_external record + my @svc_external = map { $_->svc_x } + grep { $_->part_svc->svcdb eq 'svc_external' } + $cust_pkg->cust_svc; + + die "cdr_termination package has no svc_external service" + unless @svc_external; + die "cdr_termination package has multiple svc_external services" + if scalar(@svc_external) > 1; + + my $svc_external = $svc_external[0]; + + # find CDRs: + # - matching our customer via svc_external.id/title? (and via what field?) + + #let's try carrierid for now, can always make it configurable or rewrite + my $cdr_column = 'carrierid'; + + my %hashref = ( 'freesidestatus' => 'done' ); + + # try matching on svc_external.id for now... (or title? if ints don't cut it) + $hashref{$cdr_column} = $svc_external[0]->id; + + # - with no cdr_termination.status + + my $termpart = 1; #or from an option + + #false lazienss w/search/cdr.html (i should be a part_termination method) + my $where_term = + "( cdr.acctid = cdr_termination.acctid AND termpart = $termpart ) "; + #my $join_term = "LEFT JOIN cdr_termination ON ( $where_term )"; + my $extra_sql = + "AND NOT EXISTS ( SELECT 1 FROM cdr_termination WHERE $where_term )"; + + #may need to process in batches if there's waaay too many + my @cdrs = qsearch({ + 'table' => 'cdr', + #'addl_from' => $join_term, + 'hashref' => \%hashref, + 'extra_sql' => "$extra_sql FOR UPDATE", + }); + + foreach my $cdr (@cdrs) { + + #add a cdr_termination record and the charges + + my $term_price = sprintf('%.2f', $cdr->rated_price * $term_percent / 100 ); + + my $cdr_termination = new FS::cdr_termination { + 'acctid' => $cdr->acctid, + 'termpart' => $termpart, + 'rated_price' => $term_price, + 'status' => 'done', + }; + + my $error = $cdr_termination->insert; + die $error if $error; #next if $error; #or just skip this one??? why? + + $charges += $term_price; + + # and add a line to the invoice + + my $call_details = $cdr->downstream_csv( 'format' => $output_format, + 'charge' => $term_price, + ); - # find CDRs with cdr_termination.status NULL - # and matching our customer via svc_external.id/title? (and via what field?) + my $classnum = ''; #usage class? - #for each cdr, set status and rated price and add the charges, and add a line - #to the invoice + #option to turn off? or just use squelch_cdr for the customer probably + push @$details, [ 'C', $call_details, $term_price, $classnum ]; + } + # eotermiation calculation $charges += $self->calc_recur_Common(@_); diff --git a/httemplate/edit/cust_main/billing.html b/httemplate/edit/cust_main/billing.html index 4c4be23c0..3f3d80176 100644 --- a/httemplate/edit/cust_main/billing.html +++ b/httemplate/edit/cust_main/billing.html @@ -477,10 +477,13 @@ my @payby = grep /\w/, $conf->config('payby'); @payby = (qw( CARD DCRD CHEK DCHK LECB BILL CASH COMP )) unless @payby; -#false laziness w/view/cust_main/billing.html -my $term_sql = "SELECT COUNT(*) FROM cust_pkg LEFT JOIN part_pkg USING ( pkgpart ) WHERE custnum = ? AND plan = 'cdr_termination' LIMIT 1"; -my $term_sth = dbh->prepare($term_sql) or die dbh->errstr; -$term_sth->execute($cust_main->custnum) or die $term_sth->errstr; -my $show_term = $term_sth->fetchrow_arrayref->[0]; +my $show_term = ''; +if ( $cust_main->custnum ) { + #false laziness w/view/cust_main/billing.html + my $term_sql = "SELECT COUNT(*) FROM cust_pkg LEFT JOIN part_pkg USING ( pkgpart ) WHERE custnum = ? AND plan = 'cdr_termination' LIMIT 1"; + my $term_sth = dbh->prepare($term_sql) or die dbh->errstr; + $term_sth->execute($cust_main->custnum) or die $term_sth->errstr; + $show_term = $term_sth->fetchrow_arrayref->[0]; +} diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html index cef8ec016..26eac75b3 100644 --- a/httemplate/search/cdr.html +++ b/httemplate/search/cdr.html @@ -65,46 +65,87 @@ my $hashref = {}; my @search = (); ### +# dates +### + +my $str2time_sql = str2time_sql; + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +push @search, "$str2time_sql calldate) >= $beginning ", + "$str2time_sql calldate) <= $ending"; + +### +# duration / billsec +### + +push @search, FS::UI::Web::parse_lt_gt($cgi, 'duration'); +push @search, FS::UI::Web::parse_lt_gt($cgi, 'billsec'); + +#above here things just push @search +#below here things also have to define $hashref->{} or push @qsearch +my @qsearch = @search; + +### # freesidestatus ### if ( $cgi->param('freesidestatus') eq 'NULL' ) { - my $title = "Unprocessed $title"; + $title = "Unprocessed $title"; $hashref->{'freesidestatus'} = ''; # Record.pm will take care of it push @search, "( freesidestatus IS NULL OR freesidestatus = '' )"; } elsif ( $cgi->param('freesidestatus') =~ /^([\w ]+)$/ ) { - my $title = "Processed $title"; + $title = "Processed $title"; $hashref->{'freesidestatus'} = $1; push @search, "freesidestatus = '$1'"; } ### -# dates +# termpartNstatus ### -my $str2time_sql = str2time_sql; +foreach my $param ( grep /^termpart\d+status$/, $cgi->param ) { -my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -push @search, "$str2time_sql calldate) >= $beginning ", - "$str2time_sql calldate) <= $ending"; + my $status = $cgi->param($param); -### -# duration / billsec -### + $param =~ /^termpart(\d+)status$/ or die 'guru meditation 54something'; + my $termpart = $1; -push @search, FS::UI::Web::parse_lt_gt($cgi, 'duration'); -push @search, FS::UI::Web::parse_lt_gt($cgi, 'billsec'); + my $search = ''; + if ( $status eq 'NULL' ) { + + #false lazienss w/cdr_termination.pm (i should be a part_termination method) + my $where_term = + "( cdr.acctid = cdr_termination.acctid AND termpart = $termpart ) "; + #my $join_term = "LEFT JOIN cdr_termination ON ( $where_term )"; + $search = + "NOT EXISTS ( SELECT 1 FROM cdr_termination WHERE $where_term )"; + + } elsif ( $cgi->param('freesidestatus') =~ /^([\w ]+)$/ ) { + + #false lazienss w/cdr_termination.pm (i should be a part_termination method) + my $where_term = + "( cdr.acctid = cdr_termination.acctid AND termpart = $termpart AND status = '$1' ) "; + #my $join_term = "LEFT JOIN cdr_termination ON ( $where_term )"; + $search = + "EXISTS ( SELECT 1 FROM cdr_termination WHERE $where_term )"; + + } + + if ( $search ) { + push @search, $search; + push @qsearch, $search; + } + +} ### # src/dest/charged_party ### -my @qsearch = @search; - if ( $cgi->param('src') =~ /^\s*([\d\-\+\ ]+)\s*$/ ) { ( my $src = $1 ) =~ s/\D//g; $hashref->{'src'} = $src; @@ -122,10 +163,12 @@ if ( $cgi->param('charged_party') =~ /^\s*([\d\-\+\ ]+)\s*$/ ) { #$hashref->{'charged_party'} = $charged_party; #push @search, "charged_party = '$charged_party'"; #XXX countrycode - push @search, " ( charged_party = '$charged_party' - OR charged_party = '1$charged_party' ) "; - push @qsearch, " ( charged_party = '$charged_party' - OR charged_party = '1$charged_party' ) "; + + my $search = " ( charged_party = '$charged_party' + OR charged_party = '1$charged_party' ) "; + + push @search, $search; + push @qsearch, $search; } ### diff --git a/httemplate/search/report_cdr.html b/httemplate/search/report_cdr.html index 28516313b..6a0b89bf8 100644 --- a/httemplate/search/report_cdr.html +++ b/httemplate/search/report_cdr.html @@ -3,6 +3,7 @@
+ +% #if ( ) { # disable for everyone not using termination billing... +% foreach my $termpart ( 1..1 ) { #qsearch('part_termination + + + + + + +% } +% #} + <% include ( '/elements/tr-input-beginning_ending.html' ) %> -- 2.11.0
Status: @@ -14,6 +15,23 @@
Termination Status: + +