1 <& elements/grid-report.html,
2 title => $title.'Package Agent Credits and Payments',
6 <P>Shows agent commission credits, and payments applied to invoices for packages that triggered those credits.</P>
8 td.creditcell { background-color: #ffff99; }
9 td.paycell { background-color: #66ff66; }
16 my $curuser = $FS::CurrentUser::CurrentUser;
18 unless $curuser->access_right('Financial reports');
23 my ($agentnum,$sel_agent);
24 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
26 $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } );
27 die "agentnum $agentnum not found!" unless $sel_agent;
28 $extra_sql .= " AND cust_credit.commission_agentnum = $agentnum\n";
30 my $title = $sel_agent ? $sel_agent->agent.' ' : '';
32 # search for credits in time period (applied to payments in $query)
33 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
34 $extra_sql .= " AND cust_credit._date >= $beginning\n";
35 $extra_sql .= " AND cust_credit._date <= $ending\n";
37 # agent virtualization
38 my $agentnums_sql = $curuser->agentnums_sql( table => 'agent' );
41 LEFT JOIN agent ON ( cust_credit.commission_agentnum = agent.agentnum )
42 LEFT JOIN cust_pkg ON ( cust_credit.commission_pkgnum = cust_pkg.pkgnum )
43 LEFT JOIN part_pkg ON ( cust_pkg.pkgpart = part_pkg.pkgpart )
49 cust_pkg.custnum AS xcustnum,
50 cust_credit.commission_pkgnum AS xpkgnum,
53 cust_pay.paynum AS xnum,
54 to_timestamp(cust_pay._date) AS xdate,
55 cust_pay.paid AS xamount,
56 cust_pay.order_number AS order_number
58 INNER JOIN cust_bill_pay ON ( cust_pay.paynum = cust_bill_pay.paynum )
59 INNER JOIN cust_bill_pkg ON ( cust_bill_pay.invnum = cust_bill_pkg.invnum )
60 INNER JOIN cust_credit ON ( cust_bill_pkg.pkgnum = cust_credit.commission_pkgnum )
62 WHERE cust_credit.commission_pkgnum IS NOT NULL
63 AND cust_pay._date >= $beginning
64 AND cust_pay._date <= $ending
70 cust_pkg.custnum AS xcustnum,
71 cust_credit.commission_pkgnum AS xpkgnum,
73 'cust_credit' AS xtable,
74 cust_credit.crednum AS xnum,
75 to_timestamp(cust_credit._date) AS xdate,
76 cust_credit.amount AS xamount,
80 WHERE cust_credit.commission_pkgnum is not null
83 ORDER BY agent, xcustnum, xpkgnum, xdate
86 my $sth = dbh->prepare($query) or die dbh->errstr;
87 $sth->execute() or die $sth->errstr;
94 my ($prev_agent,$count_agent,$prev_cust,$count_cust,$prev_pkg,$count_pkg);
95 while (my $row = $sth->fetchrow_arrayref) {
97 my $curr_agent = shift @row;
98 my $curr_cust = shift @row;
99 my $curr_pkg = (shift @row) . ': ' . (shift @row);
101 if ($curr_pkg eq $prev_pkg) {
104 unshift @{$$pkgstack[0]}, { value => $prev_pkg, rowspan => $count_pkg } if @$pkgstack;;
105 push @$custstack, @$pkgstack;
109 $prev_pkg = $curr_pkg;
111 if ($curr_cust eq $prev_cust) {
115 my $cust_main = qsearchs('cust_main',{ custnum => $prev_cust });
116 unshift @{$$custstack[0]}, { value => $cust_main->name, rowspan => $count_cust } if @$custstack;;
118 push @$agentstack, @$custstack;
122 $prev_cust = $curr_cust;
124 if ($curr_agent eq $prev_agent) {
127 unshift @{$$agentstack[0]}, { value => $prev_agent, rowspan => $count_agent } if @$agentstack;;
128 push @$cells, @$agentstack;
132 $prev_agent = $curr_agent;
134 my %coloropts = ($row[0] eq 'cust_credit') ? ( 'class' => 'creditcell' ) : ( 'class' => 'paycell' );
135 push @$pkgstack, [ map { { value => $_, %coloropts } } @row ];
138 unshift @{$$pkgstack[0]}, { value => $prev_pkg, rowspan => $count_pkg } if @$pkgstack;;
139 push @$custstack, @$pkgstack;
141 my $cust_main = qsearchs('cust_main',{ custnum => $prev_cust });
142 unshift @{$$custstack[0]}, { value => $cust_main->name, rowspan => $count_cust } if @$custstack;;
144 push @$agentstack, @$custstack;
145 unshift @{$$agentstack[0]}, { value => $prev_agent, rowspan => $count_agent } if @$agentstack;;
146 push @$cells, @$agentstack;
150 my $rows = [ map { {} } @$cells ];
152 unshift @$cells, [ map { { value => $_, header => 1 } } ('Agent','Customer','Package','Table','#','Date','Amount','Order Number') ];
153 unshift @$rows, { header => 1 };