additional by-otaker searches fixed for the brave new world of usernum, RT#9555

8 files changed, 56 insertions, 34 deletions

diff --git a/httemplate/elements/select-user.html b/httemplate/elements/select-user.html
index 6264398e2..ec2341be6 100644
--- a/httemplate/elements/select-user.html
+++ b/httemplate/elements/select-user.html
@@ -4,7 +4,10 @@
   <OPTION VALUE="">all</OPTION>
 % }
 
-% foreach my $usernum ( keys %{ $opt{'access_user'} } ) { 
+% foreach my $usernum (
+%   sort { $opt{'access_user'}->{$a} cmp $opt{'access_user'}->{$b} }
+%   keys %{ $opt{'access_user'} }
+% ) { 
     <OPTION VALUE="<%$usernum%>"><% $opt{'access_user'}->{$usernum} %></OPTION>
 % } 
 
@@ -16,12 +19,9 @@ my %opt = @_;
 
 unless ( $opt{'access_user'} ) {
 
-  tie %{ $opt{'access_user'} }, 'Tie::IxHash';
-
   my $sth = dbh->prepare("
     SELECT usernum, username FROM access_user
       WHERE disabled = '' or disabled IS NULL
-        ORDER BY username
   ") or die dbh->errstr;
   $sth->execute or die $sth->errstr;
   while ( my $row = $sth->fetchrow_arrayref ) {
diff --git a/httemplate/search/cust_bill_pkg_discount.html b/httemplate/search/cust_bill_pkg_discount.html
index 088b29115..b472366be 100644
--- a/httemplate/search/cust_bill_pkg_discount.html
+++ b/httemplate/search/cust_bill_pkg_discount.html
@@ -85,6 +85,11 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.agentnum = $1";
 }
 
+#usernum
+if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
+  push @where, "cust_pkg_discount.usernum = $1";
+}
+
 # #classnum
 # # not specified: all classes
 # # 0: empty class
@@ -110,18 +115,25 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 
 my $count_query = "SELECT COUNT(*), SUM(amount)";
 
-my $join_cust =  '      JOIN cust_bill_pkg USING ( billpkgnum )
-                        JOIN cust_bill USING ( invnum ) 
-                   LEFT JOIN cust_main USING ( custnum ) ';
+my $join_cust_pkg_discount =
+  'LEFT JOIN cust_pkg_discount USING (pkgdiscountnum)';
+
+my $join_cust =
+  '      JOIN cust_bill_pkg USING ( billpkgnum )
+         JOIN cust_bill USING ( invnum ) 
+    LEFT JOIN cust_main USING ( custnum ) ';
 
-my $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum )
-                 LEFT JOIN part_pkg USING ( pkgpart ) ';
-                 #LEFT JOIN part_pkg AS override
-                 #  ON pkgpart_override = override.pkgpart ';
+my $join_pkg =
+  ' LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum )
+  LEFT JOIN part_pkg USING ( pkgpart ) ';
+  #LEFT JOIN part_pkg AS override
+  #  ON pkgpart_override = override.pkgpart ';
 
 my $where = ' WHERE '. join(' AND ', @where);
 
-$count_query .= " FROM cust_bill_pkg_discount $join_cust $join_pkg $where";
+$count_query .=
+  " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_cust $join_pkg ".
+  $where;
 
 my @select = (
                'cust_bill_pkg_discount.*',
@@ -135,7 +147,7 @@ push @select, 'cust_main.custnum',
 
 my $query = {
   'table'     => 'cust_bill_pkg_discount',
-  'addl_from' => "$join_cust $join_pkg",
+  'addl_from' => "$join_cust_pkg_discount $join_cust $join_pkg",
   'hashref'   => {},
   'select'    => join(', ', @select ),
   'extra_sql' => $where,
diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html
index 9a14dceca..ad8472134 100755
--- a/httemplate/search/cust_credit.html
+++ b/httemplate/search/cust_credit.html
@@ -56,8 +56,8 @@ my $title = 'Credit Search Results';
 
 my @search = ();
 
-if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) {
-  push @search, "cust_credit.otaker = '$1'";
+if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
+  push @search, "cust_credit.usernum = $1";
 }
 
 if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) {
diff --git a/httemplate/search/cust_pkg_discount.html b/httemplate/search/cust_pkg_discount.html
index 233345e1c..d70c3116f 100644
--- a/httemplate/search/cust_pkg_discount.html
+++ b/httemplate/search/cust_pkg_discount.html
@@ -78,9 +78,9 @@ if ( $cgi->param('status') eq 'active' ) {
                ";     #XXX also end date
 }
 
-#otaker
-if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) {
-  push @where, "cust_pkg_discount.otaker = '$1'";
+#usernum
+if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
+  push @where, "cust_pkg_discount.usernum = $1";
 }
 
 #agent
diff --git a/httemplate/search/report_cust_bill_pkg_discount.html b/httemplate/search/report_cust_bill_pkg_discount.html
index f1879d4a9..f9ab901b5 100644
--- a/httemplate/search/report_cust_bill_pkg_discount.html
+++ b/httemplate/search/report_cust_bill_pkg_discount.html
@@ -5,9 +5,9 @@
 
 <TABLE>
 
-  <% include( '/elements/tr-select-otaker.html',
-                'label'   => 'Discounts by employee: ',
-                'otakers' => \@otakers,
+  <% include( '/elements/tr-select-user.html',
+                'label'       => 'Discounts by employee: ',
+                'access_user' => \%access_user,
             )
   %>
 
@@ -39,9 +39,12 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 
-my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_pkg_discount")
+my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_pkg_discount")
   or die dbh->errstr;
 $sth->execute or die $sth->errstr;
-my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref};
+my @usernum = map $_->[0], @{$sth->fetchall_arrayref};
+my %access_user =
+  map { $_ => qsearchs('access_user',{'usernum'=>$_})->username }
+      @usernum;
 
 </%init>
diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html
index 9c719b787..c7e552238 100644
--- a/httemplate/search/report_cust_credit.html
+++ b/httemplate/search/report_cust_credit.html
@@ -5,9 +5,9 @@
 
 <TABLE>
 
-  <% include( '/elements/tr-select-otaker.html',
-                'label'   => 'Credits by employee: ',
-                'otakers' => \@otakers,
+  <% include( '/elements/tr-select-user.html',
+                'label'       => 'Credits by employee: ',
+                'access_user' => \%access_user,
             )
   %>
 
@@ -40,9 +40,13 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 
-my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_credit")
+my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_credit")
   or die dbh->errstr;
 $sth->execute or die $sth->errstr;
-my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref};
+my @usernum = map $_->[0], @{$sth->fetchall_arrayref};
+my %access_user =
+  map { $_ => qsearchs('access_user',{'usernum'=>$_})->username }
+      @usernum;
+
 
 </%init>
diff --git a/httemplate/search/report_cust_pkg_discount.html b/httemplate/search/report_cust_pkg_discount.html
index 7ebd44f75..31774c384 100644
--- a/httemplate/search/report_cust_pkg_discount.html
+++ b/httemplate/search/report_cust_pkg_discount.html
@@ -16,9 +16,9 @@
     </TD>
   </TR>
 
-  <% include( '/elements/tr-select-otaker.html',
-                'label'   => 'Discounts by employee: ',
-                'otakers' => \@otakers,
+  <% include( '/elements/tr-select-user.html',
+                'label'       => 'Discounts by employee: ',
+                'access_user' => \%access_user,
             )
   %>
 
@@ -42,9 +42,12 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 
-my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_pkg_discount")
+my $sth = dbh->prepare("SELECT DISTINCT usernum FROM cust_pkg_discount")
   or die dbh->errstr;
 $sth->execute or die $sth->errstr;
-my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref};
+my @usernum = map $_->[0], @{$sth->fetchall_arrayref};
+my %access_user =
+  map { $_ => qsearchs('access_user',{'usernum'=>$_})->username }
+      @usernum;
 
 </%init>
diff --git a/httemplate/search/report_h_cust_pay.html b/httemplate/search/report_h_cust_pay.html
index fe7c4a9fa..5c7f27afb 100644
--- a/httemplate/search/report_h_cust_pay.html
+++ b/httemplate/search/report_h_cust_pay.html
@@ -88,7 +88,7 @@
              )
   %>
 
-  <% include( '/elements/tr-select-otaker.html' ) %>
+  <% include( '/elements/tr-select-user.html' ) %>
 
   <TR>
     <TD ALIGN="right" VALIGN="center">Payment</TD>