quick script to convert rt links from one database name to another

[freeside.git] / httemplate / search / cust_bill_pkg.cgi

diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index f84293a..4779071 100644 (file)
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -4,7 +4,7 @@ my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 
 my $join_cust = "
     JOIN cust_bill USING ( invnum ) 
-    JOIN cust_main USING ( custnum )
+    LEFT JOIN cust_main USING ( custnum )
 ";
 
 my $join_pkg = "
@@ -12,26 +12,41 @@ my $join_pkg = "
     LEFT JOIN part_pkg USING ( pkgpart )
 ";
 
-my $where = "
-  WHERE _date >= $beginning AND _date <= $ending
-    AND payby != 'COMP'
-";
+my $where = " WHERE _date >= $beginning AND _date <= $ending ";
+
+$where .= " AND payby != 'COMP' "
+  unless $cgi->param('include_comp_cust');
+
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+  $where .= " AND agentnum = $1 ";
+}
+
+if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
+  if ( $1 == 0 ) {
+    $where .= " AND classnum IS NULL ";
+  } else {
+    $where .= " AND classnum = $1 ";
+  }
+}
 
 if ( $cgi->param('out') ) {
 
   $where .= "
-    AND 0 = ( SELECT COUNT(*) FROM cust_main_county
-               WHERE (    cust_main_county.county  = cust_main.county
-                       OR cust_main_county.county IS NULL AND cust_main.county  =  ''
-                       OR cust_main_county.county  =  ''  AND cust_main.county IS NULL
-                     )
-                 AND (    cust_main_county.state   = cust_main.state
-                       OR cust_main_county.state  IS NULL AND cust_main.state  =  ''
-                       OR cust_main_county.state   =  ''  AND cust_main.state IS NULL
-                     )
-                 AND cust_main_county.country = cust_main.country
-                 AND cust_main_county.tax > 0
+    AND 0 = (
+      SELECT COUNT(*) FROM cust_main_county
+      WHERE (    cust_main_county.county  = cust_main.county
+              OR ( cust_main_county.county IS NULL AND cust_main.county  =  '' )
+              OR ( cust_main_county.county  =  ''  AND cust_main.county IS NULL)
+              OR ( cust_main_county.county IS NULL AND cust_main.county IS NULL)
+            )
+        AND (    cust_main_county.state   = cust_main.state
+              OR ( cust_main_county.state  IS NULL AND cust_main.state  =  ''  )
+              OR ( cust_main_county.state   =  ''  AND cust_main.state IS NULL )
+              OR ( cust_main_county.state  IS NULL AND cust_main.state IS NULL )
             )
+        AND cust_main_county.country = cust_main.country
+        AND cust_main_county.tax > 0
+    )
   ";
 
 } elsif ( $cgi->param('country' ) ) {
@@ -59,19 +74,27 @@ my $count_query;
 if ( $cgi->param('pkg_tax') ) {
 
   $count_query =
-    "SELECT COUNT(*), SUM( ( CASE WHEN part_pkg.setuptax = 'Y'
-                                 THEN cust_bill_pkg.setup
-                                 ELSE 0 )
-                          +
-                          ( CASE WHEN part_pkg.recurtax = 'Y'
-                                 THEN cust_bill_pkg.recur
-                                 ELSE 0 )
-                        )";
+    "SELECT COUNT(*), SUM(
+                           ( CASE WHEN part_pkg.setuptax = 'Y'
+                                  THEN cust_bill_pkg.setup
+                                  ELSE 0
+                             END
+                           )
+                           +
+                           ( CASE WHEN part_pkg.recurtax = 'Y'
+                                  THEN cust_bill_pkg.recur
+                                  ELSE 0
+                             END
+                           )
+                         )
+    ";
 
   $where .= " AND (
                        ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 )
                     OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 )
-                  )";
+                  )
+              AND ( tax != 'Y' OR tax IS NULL )
+            ";
 
 } else {