X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fagent_commission.html;fp=httemplate%2Fsearch%2Fagent_commission.html;h=b8fbe200f4882ddc7efd80065c47c33edb7a86d7;hb=968a0f3089c4e0e18ec2c72f5a0917188b1dffc1;hp=0000000000000000000000000000000000000000;hpb=f0264bb8437b5ea5775c99a85efefc3e535b1822;p=freeside.git
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 &>
+
+
+<& /elements/table-grid.html &>
+
+ Package |
+ Sales |
+ Percentage |
+ Commission |
+
+% 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;
+
+
+ <% $label %>
+ |
+
+% }
+
+ <% $cust_pkg->pkg_label %> |
+ <% sprintf('%.2f', $cust_pkg->sum_charged) %> |
+ <% $cust_pkg->percent %>% |
+ <% sprintf('%.2f',
+ $cust_pkg->sum_charged * $cust_pkg->percent / 100) %> |
+
+% $sales += $cust_pkg->sum_charged;
+% $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100;
+% $row++;
+% $bgcolor = 1-$bgcolor;
+% $custnum = $cust_pkg->custnum;
+% }
+
+
+ <% emt('[quant,_1,package] with commission', $row) %>
+ |
+ <% sprintf('%.2f', $sales) %> |
+ |
+ <% sprintf('%.2f', $commission) %> |
+
+
+<& /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>