From 968a0f3089c4e0e18ec2c72f5a0917188b1dffc1 Mon Sep 17 00:00:00 2001
From: Mark Wells <mark@freeside.biz>
Date: Tue, 5 Mar 2013 12:02:30 -0800
Subject: [PATCH] agent commission report, #20558

---
 httemplate/elements/menu.html                  |   3 +-
 httemplate/search/agent_commission.html        | 109 +++++++++++++++++++++++++
 httemplate/search/report_agent_commission.html |  22 +++++
 3 files changed, 133 insertions(+), 1 deletion(-)
 create mode 100644 httemplate/search/agent_commission.html
 create mode 100644 httemplate/search/report_agent_commission.html

diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index 63ff5ceeb..34f660e28 100644
--- a/httemplate/elements/menu.html
+++ b/httemplate/elements/menu.html
@@ -336,7 +336,8 @@ tie my %report_sales, 'Tie::IxHash',
   'Daily Sales, Credits and Receipts' => [ $fsurl.'graph/report_money_time_daily.html', 'Sales, credits and receipts (broken down by day) summary graph' ],
   'Sales Report' => [ $fsurl.'graph/report_cust_bill_pkg.html', 'Sales report and graph (by agent, package class and/or date range)' ],
   'Rated Call Sales Report' => [ $fsurl.'graph/report_cust_bill_pkg_detail.html', 'Sales report and graph (by agent, package class, usage class and/or date range)' ],
-  'Sales With Advertising Source' => [ $fsurl.'search/report_cust_bill_pkg_referral.html' ],
+  'Sales with Advertising Source' => [ $fsurl.'search/report_cust_bill_pkg_referral.html' ],
+  'Sales with Agent Commissions' => [ $fsurl.'search/report_agent_commission.html' ],
 ;
 
 tie my %report_financial, 'Tie::IxHash';
diff --git a/httemplate/search/agent_commission.html b/httemplate/search/agent_commission.html
new file mode 100644
index 000000000..b8fbe200f
--- /dev/null
+++ b/httemplate/search/agent_commission.html
@@ -0,0 +1,109 @@
+%# still not a good way to do rows grouped by some field in a search.html 
+%# report
+<& /elements/header.html, $title &>
+<BR>
+<STYLE TYPE="text/css">
+td.cust_head {
+  border-left: none;
+  border-right: none;
+  padding-top: 0.5em;
+  font-weight: bold;
+  background-color: #ffffff;
+}
+td.money { text-align: right; }
+td.money:before { content: '<% $money_char %>'; }
+.row0 { background-color: #eeeeee; }
+.row1 { background-color: #ffffff; }
+</STYLE>
+<& /elements/table-grid.html &>
+  <TR STYLE="background-color: #cccccc">
+    <TH CLASS="grid">Package</TH>
+    <TH CLASS="grid">Sales</TH>
+    <TH CLASS="grid">Percentage</TH>
+    <TH CLASS="grid">Commission</TH>
+  </TR>
+% my ($custnum, $sales, $commission, $row, $bgcolor) = (0, 0, 0, 0);
+% foreach my $cust_pkg ( @cust_pkg ) {
+%   if ( $custnum ne $cust_pkg->custnum ) {
+%     # start of a new customer section
+%     my $cust_main = $cust_pkg->cust_main;
+%     my $label = $cust_main->custnum . ': '. $cust_main->name;
+%     $bgcolor = 0;
+  <TR>
+    <TD COLSPAN=4 CLASS="cust_head">
+      <A HREF="<%$p%>view/cust_main.cgi?<%$cust_main->custnum%>"><% $label %></A>
+    </TD>
+  </TR>
+%   }
+  <TR CLASS="row<% $bgcolor %>">
+    <TD CLASS="grid"><% $cust_pkg->pkg_label %></TD>
+    <TD CLASS="money"><% sprintf('%.2f', $cust_pkg->sum_charged) %></TD>
+    <TD ALIGN="right"><% $cust_pkg->percent %>%</TD>
+    <TD CLASS="money"><% sprintf('%.2f',
+                      $cust_pkg->sum_charged * $cust_pkg->percent / 100) %></TD>
+  </TR>
+%   $sales += $cust_pkg->sum_charged;
+%   $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100;
+%   $row++;
+%   $bgcolor = 1-$bgcolor;
+%   $custnum = $cust_pkg->custnum;
+% }
+  <TR STYLE="background-color: #f5f6be">
+    <TD CLASS="grid">
+      <% emt('[quant,_1,package] with commission', $row) %>
+    </TD>
+    <TD CLASS="money"><% sprintf('%.2f', $sales) %></TD>
+    <TD></TD>
+    <TD CLASS="money"><% sprintf('%.2f', $commission) %></TD>
+  </TR>
+</TABLE>
+<& /elements/footer.html &>
+<%init>
+die "access denied" 
+  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
+$cgi->param('agentnum') =~ /^(\d+)$/ or die "bad agentnum";
+my $agentnum = $1;
+my $agent = FS::agent->by_key($agentnum);
+
+my $title = $agent->agent . ' commissions';
+
+my $sum_charged =
+  '(SELECT SUM(setup + recur) FROM cust_bill_pkg JOIN cust_bill USING (invnum)'.
+    'WHERE cust_bill_pkg.pkgnum = cust_pkg.pkgnum AND '.
+    "cust_bill._date >= $begin AND cust_bill._date < $end)";
+
+my @select = (
+  'cust_pkg.*',
+  'agent_pkg_class.commission_percent AS percent',
+  "$sum_charged AS sum_charged",
+);
+
+my $query = {
+  'table'       => 'cust_pkg',
+  'select'      => join(',', @select),
+  'addl_from'   => 'JOIN cust_main  USING (custnum) '.
+                   'JOIN part_pkg   USING (pkgpart) '.
+                   'JOIN agent_pkg_class ON (  '.
+                     'cust_main.agentnum = agent_pkg_class.agentnum AND '.
+                     '( agent_pkg_class.classnum = part_pkg.classnum OR '.
+                     '(agent_pkg_class IS NULL AND part_pkg.classnum IS NULL)'.
+                     ' )  ) ',
+  'extra_sql'   => "WHERE cust_main.agentnum = $agentnum AND ".
+                   'agent_pkg_class.commission_percent > 0 AND '.
+                   "$sum_charged > 0",
+  'order_by'    => 'ORDER BY cust_pkg.custnum ASC',
+};
+
+my @cust_pkg = qsearch($query);
+
+my $money_char = FS::Conf->new->config('money_char') || '$';
+
+#my $count_query = 
+#  'SELECT COUNT(*) FROM cust_pkg '.$query->{'addl_from'}.$query->{'extra_sql'}.
+#  ' AND EXISTS(SELECT 1 FROM cust_bill_pkg JOIN cust_bill USING (invnum) '.
+#    ' WHERE cust_bill_pkg.pkgnum = cust_pkg.pkgnum AND '.
+#    "cust_bill._date >= $begin AND cust_bill._date < $end".
+#  ')';
+</%init>
diff --git a/httemplate/search/report_agent_commission.html b/httemplate/search/report_agent_commission.html
new file mode 100644
index 000000000..79f94c52e
--- /dev/null
+++ b/httemplate/search/report_agent_commission.html
@@ -0,0 +1,22 @@
+<% include('/elements/header.html', 'Agent commission report' ) %>
+
+<FORM ACTION="agent_commission.html">
+
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+<% include( '/elements/tr-select-agent.html', disable_empty => 1 ) %>
+
+<% include( '/elements/tr-input-beginning_ending.html', ) %>
+
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="Get Report">
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+</%init>
-- 
2.11.0