From: Ivan Kohler Date: Wed, 21 Jan 2015 05:41:15 +0000 (-0800) Subject: credit sources, RT#28917 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=908a4522939c0225ef4e7ba4236dab2eebe9daca credit sources, RT#28917 --- diff --git a/FS/FS.pm b/FS/FS.pm index b87c1666e..33105ba46 100644 --- a/FS/FS.pm +++ b/FS/FS.pm @@ -452,6 +452,8 @@ L - Credit application to invoice class L - Line-item specific credit application to invoice class +L - Line-item sources for triggered package credits + L - Refund application to payment class L - Credit card transaction queue class diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index f51b5762d..b6358b9bb 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -1526,6 +1526,27 @@ sub tables_hashref { ], }, + 'cust_credit_source_bill_pkg' => { + 'columns' => [ + 'creditsourcebillpkgnum', 'serial', '', '', '', '', + 'crednum', 'int', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + 'currency', 'char', 'NULL', 3, '', '', + ], + 'primary_key' => 'creditsourcebillpkgnum', + 'unique' => [], + 'index' => [ ['crednum'], ['billpkgnum'] ], + 'foreign_keys' => [ + { columns => ['billpkgnum'], + table => 'cust_bill_pkg', + }, + { columns => ['crednum'], + table => 'cust_credit', + }, + ], + }, + 'cust_main' => { 'columns' => [ 'custnum', 'serial', '', '', '', '', diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm index 212be7a37..dde847a56 100644 --- a/FS/FS/cust_credit.pm +++ b/FS/FS/cust_credit.pm @@ -176,6 +176,17 @@ sub insert { return "error inserting $self: $error"; } + if ( $options{'cust_credit_source_bill_pkg'} ) { + foreach my $ccsbr ( @{ $options{'cust_credit_source_bill_pkg'} } ) { + $ccsbr->crednum( $self->crednum ); + $error = $ccsbr->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "error inserting $ccsbr: $error"; + } + } + } + $dbh->commit or die $dbh->errstr if $oldAutoCommit; #false laziness w/ cust_pay::insert diff --git a/FS/FS/cust_credit_source_bill_pkg.pm b/FS/FS/cust_credit_source_bill_pkg.pm new file mode 100644 index 000000000..dae0d0f9b --- /dev/null +++ b/FS/FS/cust_credit_source_bill_pkg.pm @@ -0,0 +1,122 @@ +package FS::cust_credit_source_bill_pkg; +use base qw( FS::cust_main_Mixin FS::Record ); + +use strict; +#use FS::Record qw( qsearch qsearchs ); + +=head1 NAME + +FS::cust_credit_source_bill_pkg - Object methods for cust_credit_source_bill_pkg records + +=head1 SYNOPSIS + + use FS::cust_credit_source_bill_pkg; + + $record = new FS::cust_credit_source_bill_pkg \%hash; + $record = new FS::cust_credit_source_bill_pkg { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::cust_credit_source_bill_pkg object represents the record that a credit +was triggered by a specific line item. FS::cust_credit_source_bill_pkg +inherits from FS::Record. The following fields are currently supported: + +=over 4 + +=item creditsourcebillpkgnum + +Primary key + +=item crednum + +Credit (see L) + +=item billpkgnum + +Line item (see L) + +=item amount + +Amount specific to this line item. + +=item currency + +Currency + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new record. To add the record 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 + +sub table { 'cust_credit_source_bill_pkg'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=item delete + +Delete this record from the database. + +=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. + +=item check + +Checks all fields to make sure this is a valid record. 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('creditsourcebillpkgnum') + || $self->ut_foreign_key('crednum', 'cust_credit', 'crednum') + || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg', 'billpkgnum') + || $self->ut_money('amount') + || $self->ut_currencyn('currency') + ; + return $error if $error; + + $self->SUPER::check; +} + +=back + +=head1 BUGS + +Terminology/documentation surrounding credit "sources" vs. credit +"applications" is hard to understand. + +=head1 SEE ALSO + +L, L, L + +=cut + +1; + diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index d55bd7bf0..5e070e38d 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -1440,7 +1440,11 @@ sub credit_remaining { and $next_bill > 0 # the package has a next bill date and $next_bill >= $time # which is in the future ) { - my $remaining_value = $self->calc_remain('time' => $time); + my @cust_credit_source_bill_pkg = (); + my $remaining_value = $self->calc_remain( + 'time' => $time, + 'cust_credit_source_bill_pkg' => \@cust_credit_source_bill_pkg, + ); if ( $remaining_value > 0 ) { warn "Crediting for $remaining_value on package ".$self->pkgnum."\n" if $DEBUG; @@ -1448,6 +1452,7 @@ sub credit_remaining { $remaining_value, 'Credit for unused time on '. $self->part_pkg->pkg, 'reason_type' => $reason_type, + 'cust_credit_source_bill_pkg' => \@cust_credit_source_bill_pkg, ); return "Error crediting customer \$$remaining_value for unused time". " on ". $self->part_pkg->pkg. ": $error" diff --git a/FS/FS/part_pkg/flat.pm b/FS/FS/part_pkg/flat.pm index cb2986efd..d9d458809 100644 --- a/FS/FS/part_pkg/flat.pm +++ b/FS/FS/part_pkg/flat.pm @@ -7,6 +7,7 @@ use base qw( FS::part_pkg::prorate_Mixin use strict; use vars qw( %info %usage_recharge_fields @usage_recharge_fieldorder ); use FS::Record qw( qsearch ); +use FS::cust_credit_source_bill_pkg; use Tie::IxHash; use List::Util qw( min ); use FS::UI::bytecount; @@ -242,7 +243,7 @@ sub calc_remain { # Use sdate < $time and edate >= $time because when billing on # cancellation, edate = $time. my $credit = 0; - foreach my $item ( + foreach my $cust_bill_pkg ( qsearch('cust_bill_pkg', { pkgnum => $cust_pkg->pkgnum, sdate => {op => '<' , value => $time}, @@ -250,16 +251,28 @@ sub calc_remain { recur => {op => '>' , value => 0}, }) ) { + # hack to deal with the weird behavior of edate on package cancellation - my $edate = $item->edate; + my $edate = $cust_bill_pkg->edate; if ( $self->recur_temporality eq 'preceding' ) { - $edate = $self->add_freq($item->sdate); + $edate = $self->add_freq($cust_bill_pkg->sdate); } - $credit += ($item->recur - $item->usage) * - ($edate - $time) / ($edate - $item->sdate); + + my $amount = ($cust_bill_pkg->recur - $cust_bill_pkg->usage) * + ($edate - $time) / ($edate - $cust_bill_pkg->sdate); + $credit += $amount; + + push @{ $options{'cust_credit_source_bill_pkg'} }, + new FS::cust_credit_source_bill_pkg { + 'billpkgnum' => $cust_bill_pkg->billpkgnum, + 'amount' => sprintf('%.2f', $amount), + 'currency' => $cust_bill_pkg->cust_bill->currency, + } + if $options{'cust_credit_source_bill_pkg'}; + } + sprintf('%.2f', $credit); - #sprintf("%.2f", $self->base_recur($cust_pkg, \$time) * ( $next_bill - $time ) / $freq_sec ); } diff --git a/FS/MANIFEST b/FS/MANIFEST index 581ab0d1f..6e36c3344 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -832,3 +832,5 @@ FS/circuit_termination.pm t/circuit_termination.t FS/svc_circuit.pm t/svc_circuit.t +FS/cust_credit_source_bill_pkg.pm +t/cust_credit_source_bill_pkg.t diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 816f74ab6..5f6921c36 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -336,6 +336,7 @@ $report_payments{'Deleted Payments / Payment history table'} = [ $fsurl.'search/ tie my %report_credits, 'Tie::IxHash', 'Credit Report' => [ $fsurl.'search/report_cust_credit.html', 'Credit report (by employee and/or date range)' ], + 'Credit package source detail' => [ $fsurl.'search/report_cust_credit_source_bill_pkg.html', 'Line-item detail for triggered package credits' ], 'Credit application detail' => [ $fsurl.'search/report_cust_credit_bill_pkg.html', 'Line item application detail' ], 'Unapplied Credits' => [ $fsurl.'search/report_cust_credit.html?unapplied=1', 'Unapplied credit report (by type and/or date range)' ], ; diff --git a/httemplate/search/cust_credit_source_bill_pkg.html b/httemplate/search/cust_credit_source_bill_pkg.html new file mode 100644 index 000000000..3ef88bdf9 --- /dev/null +++ b/httemplate/search/cust_credit_source_bill_pkg.html @@ -0,0 +1,210 @@ +<& elements/search.html, + 'title' => 'Credit package source detail', #from line item + 'name_singular' => 'credit source', + 'query' => $query, + 'count_query' => $count_query, + 'count_addl' => [ $money_char. '%.2f total', ], + 'header' => [ + #'#', + + 'Amount', + + #credit + 'Date', + 'By', + 'Reason', + + # line item + 'Line item', + + #invoice + 'Invoice', + 'Date', + FS::UI::Web::cust_header(), + ], + 'fields' => [ + #'creditsourcebillpkgnum', + sub { sprintf($money_char.'%.2f', shift->amount ) }, + + sub { time2str('%b %d %Y', shift->get('cust_credit_date') ) }, + sub { shift->cust_credit->otaker }, + sub { shift->cust_credit->reason }, + + sub { $_[0]->pkgnum > 0 + ? $_[0]->get('pkg') # possibly use override.pkg + : $_[0]->get('itemdesc') # but i think this correct + }, + 'invnum', + sub { time2str('%b %d %Y', shift->_date ) }, + \&FS::UI::Web::cust_fields, + ], + 'sort_fields' => [ + 'amount', + 'cust_credit_date', + '', #'otaker', + '', #reason + '', #line item description + 'invnum', + '_date', + FS::UI::Web::cust_sort_fields(), + ], + 'links' => [ + '', + '', + '', + '', + '', + $ilink, + $ilink, + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), + ], + 'align' => 'rrlll'. + 'rr'. + FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], + +&> +<%init> + +#copied from cust_credit_source_bill.html, which itself was cribbed from +# cust_bill_pkg.cgi and cust_credit.html, but then i took out a bunch of the +# tax reports stuff... maybe look for parts of all three that can be made into +# common subs? + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $conf = new FS::Conf; + +#here is the agent virtualization +my $agentnums_sql = + $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' ); + +my @where = ( $agentnums_sql ); + +if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @where, "cust_credit.usernum = $1"; +} + +#source invoice date (not yet in UI) +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +push @where, "cust_bill._date >= $beginning", + "cust_bill._date <= $ending"; + +#credit date +my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit'); +push @where, "cust_credit._date >= $cr_begin", + "cust_credit._date <= $cr_end"; + +#credit amount? seems more what is expected than the applied amount (not in UI) +my @lt_gt = FS::UI::Web::parse_lt_gt($cgi, 'amount' ); +s/amount/cust_credit.amount/g foreach (@lt_gt); +push @where, @lt_gt; + +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + push @where, "cust_main.agentnum = $1"; +} + +#(not in UI) +if ( $cgi->param('billpkgnum') =~ /^(\d+)$/ ) { + push @where, "billpkgnum = $1"; +} + +#(not in UI) +#classnum +# not specified: all classes +# 0: empty class +# N: classnum +my $use_override = $cgi->param('use_override'); +if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { + my $comparison = ''; + if ( $1 == 0 ) { + $comparison = "IS NULL"; + } else { + $comparison = "= $1"; + } + + if ( $use_override ) { + push @where, "( + part_pkg.classnum $comparison AND pkgpart_override IS NULL OR + override.classnum $comparison AND pkgpart_override IS NOT NULL + )"; + } else { + push @where, "part_pkg.classnum $comparison"; + } +} + +my $count_query = "SELECT COUNT(DISTINCT creditsourcebillpkgnum), + SUM(cust_credit_source_bill_pkg.amount)"; + +my $join_cust = + ' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum )'. + FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); + + +my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum )'; + +my $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) '; + +my $where = ' WHERE '. join(' AND ', @where); + +my $join_credit = ' LEFT JOIN cust_credit USING ( crednum ) '; + +$count_query .= " FROM cust_credit_source_bill_pkg + $join_cust_bill_pkg + $join_pkg + $join_credit + $join_cust + $where"; + +my @select = ( 'cust_credit_source_bill_pkg.*', + 'cust_bill_pkg.*', + 'cust_credit.otaker', + 'cust_credit._date AS cust_credit_date', + 'cust_bill._date', + ); +push @select, 'part_pkg.pkg' unless $cgi->param('istax'); +push @select, 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(); + +my $query = { + 'table' => 'cust_credit_source_bill_pkg', + 'addl_from' => "$join_cust_bill_pkg + $join_pkg + $join_credit + $join_cust", + 'hashref' => {}, + 'select' => join(', ', @select ), + 'extra_sql' => $where, + 'order_by' => 'ORDER BY creditsourcebillpkgnum', #cust_bill. or cust_credit._date? +}; + +my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; +my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; #well, no guarantee we're totaling these up in the multi-currency world + + diff --git a/httemplate/search/report_cust_credit_source_bill_pkg.html b/httemplate/search/report_cust_credit_source_bill_pkg.html new file mode 100644 index 000000000..05f21c097 --- /dev/null +++ b/httemplate/search/report_cust_credit_source_bill_pkg.html @@ -0,0 +1,82 @@ +<& /elements/header.html, mt('Credit package source report') &> + +
+ + + + +<& /elements/tr-select-user.html, + 'label' => emt('Employee: '), + 'access_user' => \%access_user, +&> + +<& /elements/tr-select-agent.html, + curr_value => scalar( $cgi->param('agentnum') ), + #label => emt('Line items for agent: '), + disable_empty => 0, +&> + + + + + + +<& /elements/tr-input-beginning_ending.html, + 'prefix' => 'credit', +&> + + + + + +
+ +
+ + +
+ +<& /elements/footer.html &> +<%init> + +#Financial reports? +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +#false laziness w/report_cust_credit.html +my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_credit") + or die dbh->errstr; +$sth->execute or die $sth->errstr; +my @usernum = map $_->[0], @{$sth->fetchall_arrayref}; +my %access_user = + map { $_ => qsearchs('access_user',{'usernum'=>$_})->username } + @usernum; + +my $conf = new FS::Conf; + + +