From f3729cae00c3282bb34ff071f0dd5f9bcd5ce9e1 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Mon, 27 Nov 2017 19:13:40 +0000 Subject: [PATCH] Added option for Credit Report to include Voided Credits RT#73200 --- FS/FS/Record.pm | 36 +++- .../elements/tr-select-show_voided_credits.html | 15 ++ httemplate/search/cust_credit.html | 181 ++++++++++++++++++++- httemplate/search/elements/search.html | 58 +++++-- httemplate/search/report_cust_credit.html | 6 + 5 files changed, 277 insertions(+), 19 deletions(-) create mode 100644 httemplate/elements/tr-select-show_voided_credits.html diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index bf676d1cc..e646b399e 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -482,6 +482,26 @@ sub qsearch { croak $error; } + + # Determine how to format rows returned form a union query: + # + # * When all queries involved in the union are from the same table: + # Return an array of FS::$table_name objects + # + # * When union query is performed on multiple tables, + # Return an array of FS::Record objects + # ! Note: As far as I can tell, this functionality was broken, and + # ! actually results in a crash. Behavior is left intact + # ! as-is, in case the results are in use somewhere + # + # * Union query is performed on multiple table, + # and $union_options{classname_from_column} = 1 + # Return an array of FS::$classname objects, where $classname is + # derived for each row from a static field inserted each returned + # row of data. + # e.g.: SELECT custnum,first,last,'cust_main' AS `__classname`'. + + my $table = $stable[0]; my $pkey = ''; $table = '' if grep { $_ ne $table } @stable; @@ -499,7 +519,21 @@ sub qsearch { $sth->finish; my @return; - if ( eval 'scalar(@FS::'. $table. '::ISA);' ) { + if ($union_options{classname_from_column}) { + + # todo + # I'm not implementing the cache for this use case, at least not yet + # -mjackson + + for my $row (@stuff) { + my $table_class = $row->{__classname} + or die "`__classname` column must be set when ". + "using \$union_options{classname_from_column}"; + push @return, new("FS::$table_class",$row); + } + + } + elsif ( eval 'scalar(@FS::'. $table. '::ISA);' ) { if ( eval 'FS::'. $table. '->can(\'new\')' eq \&new ) { #derivied class didn't override new method, so this optimization is safe if ( $cache ) { diff --git a/httemplate/elements/tr-select-show_voided_credits.html b/httemplate/elements/tr-select-show_voided_credits.html new file mode 100644 index 000000000..35c0cf401 --- /dev/null +++ b/httemplate/elements/tr-select-show_voided_credits.html @@ -0,0 +1,15 @@ + + <% $opt{'label'} || 'Show Voided Credits' %> + + + + +<%init> + +my %opt = @_; + + diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index c0da01afe..468a01a77 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -11,13 +11,16 @@ 'links' => \@links, 'color' => \@color, 'style' => \@style, + 'classname_from_column' => 1, &> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my $money_char = FS::Conf->new->config('money_char') || '$'; +my $conf = new FS::Conf; + +my $money_char = $conf->config('money_char') || '$'; my $title = emt('Credit Search Results'); @@ -30,10 +33,19 @@ my $clink = sub { : ''; }; +# form selectbox for show_voided_credits: +# - value='': use default from $conf +# - value="0" : override default, do not show voided credits +# - value="1" : override default, show voided credits +my $show_voided_credits; +$show_voided_credits = $conf->config('show_voided_credits'); +$show_voided_credits = $cgi->param('show_voided_credits') + if $cgi->param('show_voided_credits') =~ /^(\d)$/; + my (@header, @fields, @sort_fields, $align, @links, @color, @style); $align = ''; -#amount +# Report Column: Amount push @header, emt('Amount'); push @fields, sub { $money_char .sprintf('%.2f', shift->amount) }; push @sort_fields, 'amount'; @@ -42,7 +54,7 @@ push @links, ''; push @color, ''; push @style, ''; -# unapplied amount +# Report Column: Unapplied Amount if ($unapplied) { push @header, emt('Unapplied'); push @fields, sub { $money_char .sprintf('%.2f', shift->unapplied_amount) }; @@ -53,6 +65,7 @@ if ($unapplied) { push @style, ''; } +# Report Columns: Date, By, Reason, Info push @header, emt('Date'), emt('By'), emt('Reason'), @@ -113,6 +126,51 @@ push @links, map { $_ ne 'Cust. Status' ? $clink : '' } 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 = ''; @@ -181,13 +239,69 @@ push @search, "_date >= $beginning ", push @search, FS::UI::Web::parse_lt_gt($cgi, 'amount' ); -#here is the agent virtualization +# Agent virtualization push @search, $FS::CurrentUser::CurrentUser->agentnums_sql(table=>'cust_main'); my @select = ( - 'cust_credit.*', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), + "'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 ) { @@ -214,4 +328,57 @@ my $sql_query = { 'addl_from' => $addl_from. FS::UI::Web::join_cust_main('cust_credit') }; +# 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"; +} + diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 3b8895e94..62e08252b 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -411,6 +411,7 @@ my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ]; my $rows; my ($order_by_key,$order_by_desc) = ($order_by =~ /^\s*(.*?)(\s+DESC)?\s*$/i); +my $union_order_by; $opt{'order_by_sql'} ||= {}; $order_by_desc ||= ''; $order_by = $opt{'order_by_sql'}{$order_by_key} . $order_by_desc @@ -421,6 +422,8 @@ if ( ref $query ) { if (ref($query) eq 'HASH') { @query = $query; + # Assemble peices of order_by information as SQL fragment, + # store as query->{order_by} if ( $order_by ) { if ( $query->{'order_by'} ) { if ( $query->{'order_by'} =~ /^(\s*ORDER\s+BY\s+)?(\S.*)$/is ) { @@ -433,27 +436,60 @@ if ( ref $query ) { $query->{'order_by'} = "ORDER BY $order_by"; } } - $query->{'order_by'} .= " $limit"; } elsif (ref($query) eq 'ARRAY') { - # do we still use this? it was for the old 477 report. + # Presented query is a UNION query, with multiple query references @query = @{ $query }; + + # Assemble peices of order_by information as SQL fragment, + # store as $union_order_by. Omit order_by/limit from individual + # $query hashrefs, because this is a union query + # + # ! Currently, order_by data is only fetched from $cgi->param('order_by') + # ! for union queries. If it eventually needs to be passed within query + # ! hashrefs, or as mason template options, would need implemented + $union_order_by = " ORDER BY $order_by " if $order_by; + $union_order_by .= " $limit " if $limit; + } else { - die "invalid query reference"; + die "invalid query reference ($query)"; } #eval "use FS::$opt{'query'};"; my @param = qw( select table addl_from hashref extra_sql order_by debug ); - $rows = [ qsearch( [ map { my $query = $_; - ({ map { $_ => $query->{$_} } @param }); - } - @query - ], - #'order_by' => $opt{order_by}. " ". $limit, - ) - ]; + if ($opt{classname_from_column}) { + # Perform a union of multiple queries, while using the + # classname_from_column qsearch union option + + # Constrain hashkeys for each query from @param + @query = map{ + my $query = $_; + my $new_query = {}; + $new_query->{$_} = $query->{$_} for @param; + $new_query; + } @query; + + $rows = [ + qsearch( + \@query, + order_by => $union_order_by, + classname_from_column => 1, + ) + ]; + + } else { + # default perform a query with qsearch + $rows = [ qsearch( [ map { my $query = $_; + ({ map { $_ => $query->{$_} } @param }); + } + @query + ], + #'order_by' => $opt{order_by}. " ". $limit, + ) + ]; + } } else { # not ref $query; plain SQL (still used as of 07/2015) $query .= " $limit"; diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html index cf54c34b4..05282e3be 100644 --- a/httemplate/search/report_cust_credit.html +++ b/httemplate/search/report_cust_credit.html @@ -24,6 +24,11 @@ 'field' => 'amount', &> + <& /elements/tr-select-show_voided_credits.html, + 'label' => emt('Show Voided Credits'), + &> + +
@@ -46,3 +51,4 @@ my $title = $cgi->param('unapplied') ? 'Unapplied credit report' : 'Credit report'; + -- 2.11.0