fix top subtotals on refund reports

[freeside.git] / httemplate / search / part_pkg.html

diff --git a/httemplate/search/part_pkg.html b/httemplate/search/part_pkg.html
index c1088b4..a90f13c 100644 (file)
--- a/httemplate/search/part_pkg.html
+++ b/httemplate/search/part_pkg.html
@@ -1,20 +1,21 @@
-<% include( 'elements/search.html',
-              'title'       => $title,
-              'name'        => $name,
-              'header'      => \@header,
-              'query'       => { 'select'    => $select,
-                                 'table'     => 'part_pkg',
-                                 'addl_from' => $addl_from,
-                                 'hashref'   => {},
-                                 'extra_sql' => $extra_sql,
-                                 'order_by'  => "ORDER BY $order_by",
-                               },
-              'count_query' => $count_query,
-              'fields'      => \@fields,
-              'links'       => \@links,
-              'align'       => $align,
-          )
-%>
+<& elements/search.html,
+              'title'        => $title,
+              'name_singular' => $name,
+              'header'       => \@header,
+              'query'        => { 'select'    => $select,
+                                  'table'     => 'part_pkg',
+                                  'addl_from' => $addl_from,
+                                  'hashref'   => {},
+                                  'extra_sql' => $extra_sql,
+                                  'order_by'  => "ORDER BY $order_by",
+                                },
+              'count_query'  => $count_query,
+              'fields'       => \@fields,
+              'links'        => \@links,
+              'align'        => $align,
+              'sort_fields'  => [],
+          
+&>
 <%init>
 
 #this is about reports about packages definitions (starting w/commission ones)
@@ -22,7 +23,7 @@
 
 my $curuser = $FS::CurrentUser::CurrentUser;
 die "access denied"
-  unless $curuser->access_right('Financial reports');
+  unless $curuser->access_right('Employees: Commission Report'); #that's all this does so far
 
 my $conf = new FS::Conf;
 my $money_char = $conf->config('money_char') || '$';
@@ -33,6 +34,7 @@ my $name = 'package definition';
 my $select = '';
 my $addl_from = '';
 my @where = ();
+my @top_where = ();
 my @order_by = ();
 my @header = ();
 my @fields = ();
@@ -45,9 +47,10 @@ if (1) { #commission reports
 
   if (1) { #employee commission reports 
 
-    $group_by = join(', ', ( map "access_user.$_", qw( usernum username    ) ),
-                           ( map "part_pkg.$_",    qw( pkgpart pkg comment ) ),
-                    );
+    $group_by = join(', ',
+      ( map "access_user.$_", qw( usernum username    ) ),
+      ( map "part_pkg.$_",    qw( pkgpart pkg comment plan ) ),
+    );
 
     $select = $group_by;
 
@@ -63,6 +66,8 @@ if (1) { #commission reports
 
       $title = $access_user->name;
 
+      push @top_where, 'access_user.usernum = '. $access_user->usernum;
+
     } else {
 
       push @header, 'Employee';
@@ -123,11 +128,9 @@ if (1) { #commission reports
 
   my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 
-  my $where = "WHERE cust_bill_pkg.pkgnum > 0
-                 AND cust_bill._date >= $beginning
-                 AND cust_bill._date <= $ending         ";
-  my $and = "    AND $match
-                 AND cust_pkg.pkgpart = part_pkg.pkgpart";
+  push @where, " cust_bill_pkg.pkgnum > 0      ",
+               " cust_bill._date >= $beginning ",
+               " cust_bill._date <= $ending    ";
 
   push @header, '#'; # of sales';
   push @links, ''; #link to detail report
@@ -148,7 +151,8 @@ if (1) { #commission reports
               " FROM cust_bill_pkg
                 LEFT JOIN cust_pkg  USING ( pkgnum )
                 LEFT JOIN cust_bill USING ( invnum )
-                $where AND pkgpart = ? AND ";
+               WHERE ". join(' AND ', @where).
+               " AND pkgpart = ? AND ";
     my @arg = ($part_pkg->pkgpart);
     if (1) { #employee commission reports 
       $sql .= 'usernum = ?';
@@ -187,12 +191,14 @@ if (1) { #commission reports
       my $access_user = qsearchs('access_user', { 'usernum' => $part_pkg->get('usernum'), })
         or die "unknown usernum";
 
-      return 0 unless $access_user->user_custnum;
+      return $money_char.'0.00' unless $access_user->user_custnum;
       push @arg, $access_user->user_custnum;
 
     } elsif (0) { #agent commission reports
       push @arg, 'XXXagent_custnum'; #$agent->agent_custnum
     }
+    #warn $sql;
+    #warn join(',', @arg);
     my $sth = dbh->prepare($sql) or die dbh->errstr;
     $sth->execute(@arg) or die $sth->errstr;
     $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] );
@@ -204,10 +210,15 @@ if (1) { #commission reports
 push @order_by, 'pkgpart'; #pkg?
 
 $select ||= 'part_pkg.*';
-my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @where) : ''; 
+push @top_where, @where;
+my $extra_sql = scalar(@top_where) ? 'WHERE ' . join(' AND ', @top_where) : ''; 
 $extra_sql .= "GROUP BY $group_by" if $group_by;
 my $order_by = join(', ', @order_by);
 
-my $count_query = "SELECT COUNT(*) FROM part_pkg $addl_from $extra_sql";
+#XXX agent?
+#my $count_query = "SELECT COUNT(*) FROM ( SELECT 1 FROM part_pkg $addl_from $extra_sql ) AS num";
+#mysql?
+my $count_query = "SELECT COUNT(*) FROM ( SELECT DISTINCT part_pkg.pkgpart FROM part_pkg $addl_from $extra_sql ) AS num";
+#warn $count_query;
 
 </%init>