From: Jonathan Prykop Date: Sat, 11 Jun 2016 07:47:53 +0000 (-0500) Subject: RT#42348: Add IDT transaction ID to the Agent commission report X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=a9d7da1e256e73dbdcc63e9091d22f5902c5f68d RT#42348: Add IDT transaction ID to the Agent commission report --- diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 93bebb17a..0f98bc960 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -364,7 +364,8 @@ tie my %report_commissions, 'Tie::IxHash', 'Agent per package' => [ $fsurl.'search/report_agent_commission_pkg.html' ], 'Sales Person' => [ $fsurl.'search/report_sales_commission.html' ], 'Sales Person per package' => [ $fsurl.'search/report_sales_commission_pkg.html' ], - 'Employee' => [ $fsurl.'search/report_employee_commission.html', '' ] + 'Employee' => [ $fsurl.'search/report_employee_commission.html', '' ], + 'Agent Credits and Payments' => [ $fsurl.'search/report_agent_credit_payment.html' ], ; tie my %report_financial, 'Tie::IxHash'; diff --git a/httemplate/search/agent_credit_payment.html b/httemplate/search/agent_credit_payment.html new file mode 100644 index 000000000..0dda83bbd --- /dev/null +++ b/httemplate/search/agent_credit_payment.html @@ -0,0 +1,155 @@ +<& elements/grid-report.html, + title => $title.'Package Agent Credits and Payments', + rows => $rows, + cells => $cells, + head => <Shows agent commission credits, and payments applied to invoices for packages that triggered those credits.

+ +END +&> + +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; +die "access denied" + unless $curuser->access_right('Financial reports'); + +my $extra_sql = ''; + +# search for agent +my ($agentnum,$sel_agent); +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + die "agentnum $agentnum not found!" unless $sel_agent; + $extra_sql .= " AND cust_credit.commission_agentnum = $agentnum\n"; +} +my $title = $sel_agent ? $sel_agent->agent.' ' : ''; + +# search for credits in time period (applied to payments in $query) +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +$extra_sql .= " AND cust_credit._date >= $beginning\n"; +$extra_sql .= " AND cust_credit._date <= $ending\n"; + +# agent virtualization +my $agentnums_sql = $curuser->agentnums_sql( table => 'agent' ); + +my $join = <= $beginning + AND cust_pay._date <= $ending + AND $agentnums_sql +$extra_sql +UNION +SELECT DISTINCT + agent, + cust_pkg.custnum AS xcustnum, + cust_credit.commission_pkgnum AS xpkgnum, + pkg, + 'cust_credit' AS xtable, + cust_credit.crednum AS xnum, + to_timestamp(cust_credit._date) AS xdate, + cust_credit.amount AS xamount, + '' AS order_number +FROM cust_credit +$join +WHERE cust_credit.commission_pkgnum is not null + AND $agentnums_sql +$extra_sql +ORDER BY agent, xcustnum, xpkgnum, xdate +END + +my $sth = dbh->prepare($query) or die dbh->errstr; +$sth->execute() or die $sth->errstr; + +my $cells = []; +my $rows = []; +my $agentstack = []; +my $custstack = []; +my $pkgstack = []; +my ($prev_agent,$count_agent,$prev_cust,$count_cust,$prev_pkg,$count_pkg); +while (my $row = $sth->fetchrow_arrayref) { + my @row = @$row; + my $curr_agent = shift @row; + my $curr_cust = shift @row; + my $curr_pkg = (shift @row) . ': ' . (shift @row); + + if ($curr_pkg eq $prev_pkg) { + $count_pkg += 1; + } else { + unshift @{$$pkgstack[0]}, { value => $prev_pkg, rowspan => $count_pkg } if @$pkgstack;; + push @$custstack, @$pkgstack; + $pkgstack = []; + $count_pkg = 1; + } + $prev_pkg = $curr_pkg; + + if ($curr_cust eq $prev_cust) { + $count_cust += 1; + } else { + if (@$custstack) { + my $cust_main = qsearchs('cust_main',{ custnum => $prev_cust }); + unshift @{$$custstack[0]}, { value => $cust_main->name, rowspan => $count_cust } if @$custstack;; + } + push @$agentstack, @$custstack; + $custstack = []; + $count_cust = 1; + } + $prev_cust = $curr_cust; + + if ($curr_agent eq $prev_agent) { + $count_agent += 1; + } else { + unshift @{$$agentstack[0]}, { value => $prev_agent, rowspan => $count_agent } if @$agentstack;; + push @$cells, @$agentstack; + $agentstack = []; + $count_agent = 1; + } + $prev_agent = $curr_agent; + + my %coloropts = ($row[0] eq 'cust_credit') ? ( 'class' => 'creditcell' ) : ( 'class' => 'paycell' ); + push @$pkgstack, [ map { { value => $_, %coloropts } } @row ]; +} + +unshift @{$$pkgstack[0]}, { value => $prev_pkg, rowspan => $count_pkg } if @$pkgstack;; +push @$custstack, @$pkgstack; +if (@$custstack) { + my $cust_main = qsearchs('cust_main',{ custnum => $prev_cust }); + unshift @{$$custstack[0]}, { value => $cust_main->name, rowspan => $count_cust } if @$custstack;; +} +push @$agentstack, @$custstack; +unshift @{$$agentstack[0]}, { value => $prev_agent, rowspan => $count_agent } if @$agentstack;; +push @$cells, @$agentstack; + +$sth->finish; + +my $rows = [ map { {} } @$cells ]; + +unshift @$cells, [ map { { value => $_, header => 1 } } ('Agent','Customer','Package','Table','#','Date','Amount','Order Number') ]; +unshift @$rows, { header => 1 }; + + diff --git a/httemplate/search/report_agent_credit_payment.html b/httemplate/search/report_agent_credit_payment.html new file mode 100755 index 000000000..57fc6a79c --- /dev/null +++ b/httemplate/search/report_agent_credit_payment.html @@ -0,0 +1,30 @@ +<& /elements/header.html, 'Package Agent Credits and Payments' &> + +

Shows agent commission credits, and payments applied to invoices for packages that triggered those credits.

+ +
+ + + + <& /elements/tr-select-agent.html, + 'curr_value' => scalar( $cgi->param('agentnum') ), + 'label' => 'Agent ', + 'disable_empty' => 0, + &> + + <& /elements/tr-input-beginning_ending.html &> + +
+ +
+ + +
+ +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +