<% include('/elements/header.html', $title) %> <% include('/elements/table-grid.html') %> % foreach (@head) { <% $_ %> % } % my $r=0; % foreach my $row (@rows) { % foreach (@$row) { <% $_ %> % } % $r++; % } % foreach (@totals) { <% $_ %> % } <%init> my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" unless $curuser->access_right('Summarize packages'); my $money_char = FS::Conf->new()->config('money_char') || '$'; $FS::Record::DEBUG=0; my $title = 'Suspension/Unsuspension Report'; my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi); if($begin > 0) { $title = "$title (". ($cgi->param('beginning') || 'beginning').' - '. ($cgi->param('ending') || 'present').')'; } my $begin_sql = $begin ? "AND h2.history_date > $begin" : ''; my $end_sql = $end ? "AND h2.history_date < $end" : ''; my $h_sql = # self-join FTW! "SELECT h1.pkgpart, count(h1.pkgnum) as pkgcount FROM h_cust_pkg AS h1 INNER JOIN h_cust_pkg AS h2 ON (h1.pkgnum = h2.pkgnum) WHERE h1.history_action = 'replace_old' AND h2.history_action = 'replace_new' AND h2.historynum - h1.historynum = 1 $begin_sql $end_sql"; # This assumes replace_old and replace_new records get consecutive # numbers. That's true in every case I've seen but is not actually # enforced anywhere. If this is a problem we can match them up # line by line but that's cumbersome. my @conds = ( '(h1.susp is null OR h1.susp = 0) AND (h2.susp is not null AND h2.susp != 0)', '(h1.susp is not null AND h1.susp != 0) AND (h2.susp is null OR h2.susp = 0)', ); my @results; foreach my $cond (@conds) { my $sql = "$h_sql AND $cond GROUP BY h1.pkgpart"; my $sth = dbh->prepare($sql) or die dbh->errstr; $sth->execute() or die $sth->errstr; push @results, { map { @$_ } @{ $sth->fetchall_arrayref() } }; } my @pay_cond; push @pay_cond, "cust_bill_pay._date < $end" if $end; push @pay_cond, "cust_bill_pay._date > $begin" if $begin; my $extra_sql = ''; $extra_sql = 'WHERE '. join(' AND ', @pay_cond) if @pay_cond; $extra_sql .= ( $extra_sql ? ' AND ' : ' WHERE ' ). $curuser->agentnums_sql( 'table' => 'cust_main' ); my $pkg_payments = { map { $_->pkgpart => $_->total_pay } qsearch({ 'table' => 'cust_pkg', 'select' => 'pkgpart, sum(cust_bill_pay_pkg.amount) AS total_pay', 'addl_from' => 'LEFT JOIN cust_main USING ( custnum ) INNER JOIN cust_bill_pkg USING (pkgnum) INNER JOIN cust_bill_pay_pkg USING (billpkgnum) INNER JOIN cust_bill_pay USING (billpaynum)', 'extra_sql' => "$extra_sql GROUP BY pkgpart", }) }; my @head = ('Package', 'Suspended', 'Unsuspended', 'Payments'); my @rows = (); my @totals = map {0} @head; $totals[0] = 'Total'; my $pkg_agentnums_sql = $curuser->agentnums_sql( 'null' => 1, 'table' => 'part_pkg', ); my $extra_sql = " WHERE $pkg_agentnums_sql"; foreach my $part_pkg (qsearch({ 'table' => 'part_pkg', 'hashref' => {}, 'extra_sql' => $extra_sql, }) ) { my @row = (); next if !$part_pkg->freq; # exclude one-time packages my $pkgpart = $part_pkg->pkgpart; push @row, $part_pkg->pkg, $results[0]->{$pkgpart} || 0, $results[1]->{$pkgpart} || 0, sprintf("%.02f",$pkg_payments->{$pkgpart}); $totals[$_] += $row[$_] foreach (1..3); $row[3] = $money_char.$row[3]; push @rows, \@row; } $totals[3] = $money_char.$totals[3];