diff options
| -rw-r--r-- | httemplate/elements/menu.html | 3 | ||||
| -rw-r--r-- | httemplate/search/agent_commission.html | 109 | ||||
| -rw-r--r-- | httemplate/search/report_agent_commission.html | 22 |
3 files changed, 133 insertions, 1 deletions
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> |