X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fpart_pkg.html;h=8d06ebd02d86622e73ea7a34919918cf4535d7da;hb=ea3ce8d7f076e7fecff4be7ae63bc413adb0adf5;hp=915dbf4482f56509aead0c5ee874c09f2c70a7d3;hpb=0fb307c305e4bc2c9c27dc25a3308beae3a4d33c;p=freeside.git

diff --git a/httemplate/search/part_pkg.html b/httemplate/search/part_pkg.html
index 915dbf448..8d06ebd02 100644
--- a/httemplate/search/part_pkg.html
+++ b/httemplate/search/part_pkg.html
@@ -38,15 +38,24 @@ my @header = ();
 my @fields = ();
 my @links  = ();
 my $align  = '';
+my $group_by = '';
+my $match = '';
 
 if (1) { #commission reports
 
   if (1) { #employee commission reports 
 
-    $select = 'DISTINCT usernum, username, part_pkg.*';
+    $group_by = join(', ',
+      ( map "access_user.$_", qw( usernum username    ) ),
+      ( map "part_pkg.$_",    qw( pkgpart pkg comment plan ) ),
+    );
+
+    $select = $group_by;
 
     $addl_from .= ' CROSS JOIN access_user ';
 
+    $match = 'cust_pkg.usernum = access_user.usernum';
+
     if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
 
       #XXX in this context, agent virt for employees, not package defs
@@ -70,6 +79,8 @@ if (1) { #commission reports
 
   } elsif (0) { #agent commission reports
 
+    $match = 'cust_main.agentnum = agent.agentnum'; #XXX need to join to this
+
     if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 
       #agent virt
@@ -91,6 +102,13 @@ if (1) { #commission reports
 
   }
 
+  $addl_from .= "
+    LEFT JOIN cust_pkg ON ( part_pkg.pkgpart = cust_pkg.pkgpart
+                              AND $match )
+    LEFT JOIN cust_bill_pkg USING ( pkgnum )
+    LEFT JOIN cust_bill USING ( invnum )
+  ";
+
   $title .= ' commission report';
   $name = "commissionable $name";
 
@@ -106,40 +124,15 @@ if (1) { #commission reports
 
   my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 
-  my $match = '';
-  if (1) { #employee commission reports 
-    $match = 'cust_pkg.usernum = access_user.usernum';
-  } elsif (0) { #agent commission reports
-    $match = 'cust_main.agentnum = agent.agentnum';
-  }
-
-  my $from_cust_bill_pkg_where = "FROM cust_bill_pkg
-                                      LEFT JOIN cust_bill USING ( invnum )
-                                      LEFT JOIN cust_pkg  USING ( pkgnum )
-                                    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, "EXISTS( SELECT 1 $from_cust_bill_pkg_where $and )";
+  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
   $align .= 'r';
   push @fields, 'num_cust_pkg';
-  $select .= ", ( SELECT COUNT(DISTINCT pkgnum)
-                    $from_cust_bill_pkg_where $and )
-                AS num_cust_pkg";
-#  push @fields, sub {
-#    my $part_pkg = shift;
-#    my $sql =
-#      #"SELECT COUNT( SELECT DISTINCT pkgnum $from_cust_bill_pkg_where )";
-#      "SELECT COUNT(DISTINCT pkgnum) $from_cust_bill_pkg_where";
-#    my $sth = dbh->prepare($sql) or die dbh->errstr;
-#    $sth->execute or die $sth->errstr;
-#    $sth->fetchrow_arrayref->[0];
-#  };
+  $select .= ", COUNT(DISTINCT cust_pkg.pkgnum) AS num_cust_pkg";
 
   push @header, 'Sales';
   push @links, ''; #link to detail report
@@ -149,7 +142,13 @@ if (1) { #commission reports
 #    ", SUM( SELECT setup+recur $from_cust_bill_pkg_where ) AS pkg_sales";
   push @fields, sub {
     my $part_pkg = shift;
-    my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $from_cust_bill_pkg_where AND pkgpart = ? AND ";
+    my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur)".
+#              " FROM cust_bill_pkg $addl_from $where AND pkgpart = ? AND ";
+              " FROM cust_bill_pkg
+                LEFT JOIN cust_pkg  USING ( pkgnum )
+                LEFT JOIN cust_bill USING ( invnum )
+               WHERE ". join(' AND ', @where).
+               " AND pkgpart = ? AND ";
     my @arg = ($part_pkg->pkgpart);
     if (1) { #employee commission reports 
       $sql .= 'usernum = ?';
@@ -206,8 +205,11 @@ push @order_by, 'pkgpart'; #pkg?
 
 $select ||= 'part_pkg.*';
 my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @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";
+warn $count_query;
 
 </%init>