X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_credit.html;h=88f9bca1332b6edc58301abfdee8078fba0600bc;hp=e4975c8de22dd2647110bbddd3705e81cc7b4684;hb=HEAD;hpb=590603ecaea3184f64530755a76626be8205da49 diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index e4975c8de..88f9bca13 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -1,63 +1,187 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => $title, - 'name' => 'credits', + 'name' => emt('credits'), 'query' => $sql_query, 'count_query' => $count_query, - 'count_addl' => [ '$%.2f total credited', ], - #'redirect' => $link, - 'header' => [ 'Amount', - 'Date', - FS::UI::Web::cust_header(), - 'By', - 'Reason' - ], - 'fields' => [ - #'crednum', - sub { sprintf('$%.2f', shift->amount ) }, - sub { time2str('%b %d %Y', shift->_date ) }, - \&FS::UI::Web::cust_fields, - 'otaker', - 'reason', - ], - #'align' => 'rrrllll', - 'align' => 'rr'.FS::UI::Web::cust_aligns().'ll', - 'links' => [ - '', - '', - ( map { $_ ne 'Cust. Status' ? $clink : '' } - FS::UI::Web::cust_header() - ), - '', - '', - ], - 'color' => [ - '', - '', - FS::UI::Web::cust_colors(), - '', - '', - ], - 'style' => [ - '', - '', - FS::UI::Web::cust_styles(), - '', - '', - ], - ) -%> + 'count_addl' => \@count_addl, + 'header' => \@header, + 'fields' => \@fields, + 'sort_fields' => \@sort_fields, + 'align' => $align, + 'links' => \@links, + 'color' => \@color, + 'style' => \@style, + 'classname_from_column' => 1, +&> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $title = 'Credit Search Results'; -#my( $count_query, $sql_query ); +my $conf = new FS::Conf; + +my $money_char = $conf->config('money_char') || '$'; + +my $title = emt('Credit Search Results'); + +my $unapplied = $cgi->param('unapplied'); +$title = emt("Unapplied $title") if $unapplied; +my $clink = sub { + my $cust_bill = shift; + $cust_bill->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +# form checkbox for show_voided_credits: +# - value=unset : do not show voided credits +# - value="1" : show voided credits +my $show_voided_credits; +$show_voided_credits = $cgi->param('show_voided_credits') + if $cgi->param('show_voided_credits'); + +my (@header, @fields, @sort_fields, $align, @links, @color, @style); +$align = ''; + +# Report Column: crednum +push @header, emt('Ref No'); +push @fields, sub { shift->crednum }; +push @sort_fields, ''; +$align .= 'l'; +push @links, ''; +push @color, ''; +push @style, ''; + +# Report Column: Amount +push @header, emt('Amount'); +push @fields, sub { $money_char .sprintf('%.2f', shift->amount) }; +push @sort_fields, 'amount'; +$align .= 'r'; +push @links, ''; +push @color, ''; +push @style, ''; + +# Report Column: Unapplied Amount +if ($unapplied) { + push @header, emt('Unapplied'); + push @fields, sub { $money_char .sprintf('%.2f', shift->unapplied_amount) }; + push @sort_fields, ''; + $align .= 'r'; + push @links, ''; + push @color, ''; + push @style, ''; +} + +# Report Columns: Date, By, Reason, Info +push @header, emt('Date'), + emt('By'), + emt('Reason'), + emt('Info'), + ; +push @fields, sub { time2str('%b %d %Y', shift->_date ) }, + 'otaker', + 'reason_only', + 'addlinfo', + ; +push @sort_fields, '_date', 'otaker', 'reasonnum', 'addlinfo'; +$align .= 'rlll'; +push @links, '', + '', + '', + '', + ; +push @color, '', + '', + '', + '', + ; +push @style, '', + '', + '', + '', + ; + +# insert customer email after 'Reason' if this is a commission report +if ( $cgi->param('commission_salesnum') or $cgi->param('commission_agentnum') ) { + my $sub_customer_email = sub { + my $cust_credit = shift; + my $cust_event = $cust_credit->cust_event or return ''; + my $cust_X = $cust_event->cust_X or return ''; + my $cust_main = $cust_X->cust_main or return ''; + my ($email) = $cust_main->invoicing_list_emailonly; + return encode_entities("<$email>") if length($email); + ''; + }; + push @header, ''; + push @fields, $sub_customer_email; + push @sort_fields, ''; + $align .= 'l'; + push @links, ''; + push @color, ''; + push @style, ''; +} + +# cust fields +push @header, FS::UI::Web::cust_header(); +push @fields, \&FS::UI::Web::cust_fields; +push @sort_fields, FS::UI::Web::cust_sort_fields; +$align .= FS::UI::Web::cust_aligns(); +push @links, map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header(); +push @color, FS::UI::Web::cust_colors(); +push @style, FS::UI::Web::cust_styles(); + +if ( $show_voided_credits ) { + + # Report Column: Void By: + push @header, emt('Void By'); + push @fields, sub { + my $rec = shift; + return $rec->void_username + if $rec->isa('FS::cust_credit_void'); + return ''; + }; + push @sort_fields, ''; + $align .= 'l'; + push @links, ''; + push @color, ''; + push @style, ''; + + # Report Column: Void Date: + push @header, emt('Void Date'); + push @fields, sub { + my $rec = shift; + return time2str('%b %d %Y', $rec->void_date ) + if $rec->isa('FS::cust_credit_void'); + return ''; + }; + push @sort_fields, ''; + $align .= 'l'; + push @links, ''; + push @color, ''; + push @style, ''; + + # Report Column: Void Reason: + push @header, emt('Void Reason'); + push @fields, sub { + my $rec = shift; + return $rec->void_reason_text + if $rec->isa('FS::cust_credit_void'); + return ''; + }; + push @sort_fields, ''; + $align .= 'l'; + push @links, ''; + push @color, ''; + push @style, ''; +} + my @search = (); +my $addl_from = ''; -if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { - push @search, "cust_credit.otaker = '$1'"; +if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { + push @search, "cust_credit.usernum = $1"; } if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -67,38 +191,203 @@ if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { $title = $agent->agent. " $title"; } +if ( $cgi->param('credbatch') =~ /^([\w\-\/\.\:]+)$/ ) { + push @search, "cust_credit.credbatch = '$1'"; +} + +if ( $cgi->param('reasonnum') =~ /^(\d+)$/ && $1 ) { + push @search, "cust_credit.reasonnum = $1"; +} + +# commission_salesnum +if ( $cgi->param('commission_salesnum') =~ /^(\d+)$/ ) { + push @search, "commission_salesnum = $1"; +} + +# commission agentnum +if ( $cgi->param('commission_agentnum') =~ /^(\d+)$/ ) { + push @search, "commission_agentnum = $1"; +} + +# commission_classnum +if ( grep { $_ eq 'commission_classnum' } $cgi->param ) { + $cgi->param('commission_classnum') =~ /^(\d*)$/ or die 'guru meditation #13'; + my $classnum = $1; + push @search, "part_pkg.classnum ". ( $classnum ? " = $classnum" + : " IS NULL " ); + + $addl_from .= + ' LEFT JOIN cust_pkg ON ( commission_pkgnum = cust_pkg.pkgnum ) '. + ' LEFT JOIN part_pkg USING ( pkgpart ) '; +} + +if ( $cgi->param('refnum') && $cgi->param('refnum') =~ /^(\d+)$/ ) { + push @search, "refnum = $1"; + my $part_referral = qsearchs('part_referral', { 'refnum' => $1 } ); + die "unknown refnum $1" unless $part_referral; + $title = $part_referral->referral. " $title"; +} + + +# cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit_refund.html, cust_main::Search::search_sql) +if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { + my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); + push @search, 'COALESCE( cust_main.classnum, 0) IN ( '. + join(',', map { $_ || '0' } @classnums ). + ' )' + if @classnums; +} + +if ( $unapplied ) { + push @search, FS::cust_credit->unapplied_sql . ' > 0'; +} + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @search, "_date >= $beginning ", "_date <= $ending"; push @search, FS::UI::Web::parse_lt_gt($cgi, 'amount' ); -#here is the agent virtualization -push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; +# Agent virtualization +push @search, $FS::CurrentUser::CurrentUser->agentnums_sql(table=>'cust_main'); + +my @select = ( + "'cust_credit' as __classname", + qw(cust_credit.crednum + cust_credit.custnum + cust_credit._date + cust_credit.amount + cust_credit.currency + cust_credit.otaker + cust_credit.usernum + cust_credit.reason + cust_credit.reasonnum + cust_credit.addlinfo + cust_credit.closed + cust_credit.pkgnum + cust_credit.eventnum + cust_credit.commission_agentnum + cust_credit.commission_salesnum + cust_credit.commission_pkgnum + cust_credit.commission_invnum + cust_credit.credbatch + ), + 'Null as void_date', + 'Null as void_usernum', + 'Null as void_reasonnum', + 'Null as void_reason', + 'Null as void_reason_text', + 'Null as void_username', + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), +); +my @select_void = ( + "'cust_credit_void' as __classname", + qw(cust_credit_void.crednum + cust_credit_void.custnum + cust_credit_void._date + cust_credit_void.amount + cust_credit_void.currency + cust_credit_void.otaker + cust_credit_void.usernum + cust_credit_void.reason + cust_credit_void.reasonnum + cust_credit_void.addlinfo + cust_credit_void.closed + cust_credit_void.pkgnum + cust_credit_void.eventnum + cust_credit_void.commission_agentnum + cust_credit_void.commission_salesnum + cust_credit_void.commission_pkgnum + cust_credit_void.commission_invnum + ), + 'Null as credbatch', + qw(cust_credit_void.void_date + cust_credit_void.void_usernum + cust_credit_void.void_reasonnum + cust_credit_void.void_reason + ), + 'reason.reason as void_reason_text', + 'vusers.username as void_username', + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), +); + +if ( $unapplied ) { + push @select, '('.FS::cust_credit->unapplied_sql .') AS unapplied_amount'; + push @search, FS::cust_credit->unapplied_sql .' > 0'; +} my $where = 'WHERE '. join(' AND ', @search); -my $count_query = 'SELECT COUNT(*), SUM(amount) '. - 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. +my $count_query = 'SELECT COUNT(*), SUM(amount) '; +$count_query .= ', SUM(' . FS::cust_credit->unapplied_sql . ') ' if $unapplied; +$count_query .= 'FROM cust_credit'. + $addl_from. FS::UI::Web::join_cust_main('cust_credit'). $where; +my @count_addl = ( $money_char.'%.2f total credited (gross)' ); +push @count_addl, $money_char.'%.2f unapplied' if $unapplied; + my $sql_query = { 'table' => 'cust_credit', - 'select' => join(', ', - 'cust_credit.*', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), + 'select' => join(', ',@select), 'hashref' => {}, 'extra_sql' => $where, - 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + 'addl_from' => $addl_from. FS::UI::Web::join_cust_main('cust_credit') }; - my $clink = sub { - my $cust_bill = shift; - $cust_bill->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; - }; +# Join to get reason text and void username to avoid two extra query per row +my $addl_from_void = join(' ', + $addl_from, + FS::UI::Web::join_cust_main('cust_credit_void'), + ' LEFT JOIN reason ON (reason.reasonnum = cust_credit_void.void_reasonnum) ', + ' LEFT JOIN access_user as vusers '. + 'on (vusers.usernum = cust_credit_void.void_usernum) ', +); + +my $where_void = $where; +$where_void =~ s/cust_credit/cust_credit_void/g; + +my $sql_query_void = { + 'table' => 'cust_credit_void', + 'select' => join(', ',@select_void), + 'hashref' => {}, + 'extra_sql' => $where_void, + 'addl_from' => $addl_from_void, +}; + +if ($show_voided_credits) { + + $sql_query = [$sql_query, $sql_query_void]; + + my $count_cust_credit; + my $count_cust_credit_void; + my $count_sum; + + # Expected fields for count query are count, sum + # Get those totals here, and send a fake count query + my $count_row = qsearchs({ + table => 'cust_credit', + select => 'count(*), sum(amount)', + extra_sql => $where, + addl_from => $addl_from . FS::UI::Web::join_cust_main('cust_credit'), + }); + $count_cust_credit = $count_row->count || 0; + $count_sum = $count_row->sum || 0; + + $count_row = qsearchs({ + table => 'cust_credit_void', + select => 'count(*)', + extra_sql => $where_void, + addl_from => $addl_from_void, + }); + $count_cust_credit_void = $count_row->count || 0; + + my $count_combined = $count_cust_credit + $count_cust_credit_void; + + # Fake count query providing needed values + $count_query = "SELECT $count_combined as count, $count_sum as sum"; +}