X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill_pkg.cgi;h=61093d262685c61e6ec2c541b2b0d1812ddec14e;hb=5b670255328fbe875196e16bc8dfc57771753e90;hp=bf73d74bd5d267a9cf005ced37344fda9be66605;hpb=91dbe4c3834f38d428367d9a1e2c6cf9ea9d84a4;p=freeside.git

diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index bf73d74bd..61093d262 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -49,8 +49,9 @@
                    @currency,
                    'invnum',
                    '_date',
-                   #'pay_amount',
-                   #'credit_amount',
+                   '', #'pay_amount',
+                   '', #'credit_amount',
+                   FS::UI::Web::cust_sort_fields(),
                  ],
                  'links'       => [
                    @pkgnum_null,
@@ -132,8 +133,8 @@ Filtering parameters:
 - classnum: Filter on package class.
 
 - report_optionnum: Filter on package report class.  Can be a single report
-  class number, a comma-separated list, the word "multiple", or an empty 
-  string (for "no report class").
+  class number or a comma-separated list (where 0 is "no report class"), or the
+  word "multiple".
 
 - use_override: Apply "classnum" and "taxclass" filtering based on the 
   override (bundle) pkgpart, rather than always using the true pkgpart.
@@ -144,8 +145,8 @@ Filtering parameters:
 
 - taxnum: Limit to items whose tax definition matches this taxnum.
   With "nottax" that means items that are subject to that tax;
-  with "istax" it's the tax charges themselves.  Can be specified 
-  more than once to include multiple taxes.
+  with "istax" it's the tax charges themselves.  Can be a comma-separated
+  list to include multiple taxes.
 
 - country, state, county, city: Limit to items whose tax location 
   matches these fields.  If "nottax" it's the tax location of the package;
@@ -283,6 +284,7 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.agentnum = $1";
 }
 
+# salesnum--see below
 # refnum
 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.refnum = $1";
@@ -309,7 +311,9 @@ my $join_pkg =
   LEFT JOIN part_pkg      USING (pkgpart)';
 
 my $part_pkg = 'part_pkg';
-if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
+# "Separate sub-packages from parents"
+my $use_override = $cgi->param('use_override') ? 1 : 0;
+if ( $use_override ) {
   # still need the real part_pkg for tax applicability, 
   # so alias this one
   $join_pkg .= " LEFT JOIN part_pkg AS override ON (
@@ -331,16 +335,25 @@ if ( $cgi->param('nottax') ) {
   # not specified: all classes
   # 0: empty class
   # N: classnum
-  if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
-    push @where, "COALESCE($part_pkg.classnum, 0) = $1";
+  if ( grep { $_ eq 'classnum' } $cgi->param ) {
+    my @classnums = grep /^\d*$/, $cgi->param('classnum');
+    push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
+                     join(',', @classnums ).
+                 ' )'
+      if @classnums;
   }
 
-  if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
-    # code reuse FTW
-    my $num = $1;
-    push @where, 
-      FS::Report::Table->with_report_option( $1, $cgi->param('use_override') )
-    ;
+  if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
+    my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
+    my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
+    my $all = $cgi->param('all_report_options') ? 1 : 0;
+    push @where, # code reuse FTW
+      FS::Report::Table->with_report_option(
+        report_optionnum      => $num,
+        not_report_optionnum  => $not_num,
+        use_override          => $use_override,
+        all_report_options    => $all,
+      );
   }
 
   # taxclass
@@ -365,11 +378,8 @@ if ( $cgi->param('nottax') ) {
   # we don't handle exempt_monthly here
   
   if ( $cgi->param('taxname') ) { # specific taxname
-      push @tax_where, 'cust_main_county.taxname = '.
+      push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
                         dbh->quote($cgi->param('taxname'));
-  } elsif ( $cgi->param('taxnameNULL') ) {
-      push @tax_where, 'cust_main_county.taxname IS NULL OR '.
-                       'cust_main_county.taxname = \'Tax\'';
   }
 
   # country:state:county:city:district (may be repeated)
@@ -397,12 +407,8 @@ if ( $cgi->param('nottax') ) {
   }
 
   # specific taxnums
-  if ( $cgi->param('taxnum') ) {
-    my $taxnum_in = join(',', 
-      grep /^\d+$/, $cgi->param('taxnum')
-    );
-    push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
-      if $taxnum_in;
+  if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
+    push @tax_where, "cust_main_county.taxnum IN ($1)";
   }
 
   # If we're showing exempt items, we need to find those with 
@@ -432,22 +438,16 @@ if ( $cgi->param('nottax') ) {
     USING (billpkgnum)";
   }
  
-  if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) { 
-    # process tax restrictions
-    unshift @tax_where,
-      'cust_main_county.tax > 0';
+  # process tax restrictions
+  unshift @tax_where,
+    'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
+    'cust_main_county.tax > 0';
 
-    my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
+  my $tax_sub = "SELECT 1
     FROM cust_bill_pkg_tax_location
     JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
     JOIN cust_main_county USING (taxnum)
-    WHERE ". join(' AND ', @tax_where).
-    " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
-
-    $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
-    ON (item_tax.invnum = cust_bill_pkg.invnum AND
-        item_tax.pkgnum = cust_bill_pkg.pkgnum)";
-  }
+    WHERE ". join(' AND ', @tax_where);
 
   # now do something with that
   if ( @exempt_where ) {
@@ -464,23 +464,17 @@ if ( $cgi->param('nottax') ) {
     my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
                   '- COALESCE(item_exempt.exempt_amount, 0)';
 
-    push @where,    'item_tax.invnum IS NOT NULL';
     push @select,   "($taxable) AS taxable_amount";
+    push @where,    "EXISTS($tax_sub)";
     push @peritem,  'taxable_amount';
     push @peritem_desc, 'Taxable';
     push @total,    "SUM($taxable)";
     push @total_desc, "$money_char%.2f taxable";
 
-  } elsif ( $cgi->param('out') ) {
-  
-    push @where,    'item_tax.invnum IS NULL',
-                    'item_exempt.billpkgnum IS NULL';
-
   } elsif ( @tax_where ) {
 
     # union of taxable + all exempt_ cases
-    push @where,
-      '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
+    push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
 
   }
 
@@ -547,6 +541,21 @@ if ( $cgi->param('nottax') ) {
     # don't double-count the components of consolidated taxes
     $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
     $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
+
+    # package classnum
+    if ( grep { $_ eq 'classnum' } $cgi->param ) {
+      my @classnums = grep /^\d*$/, $cgi->param('classnum');
+      $join_pkg .= '
+        JOIN cust_pkg AS taxed_pkg 
+          ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
+        JOIN part_pkg AS taxed_part_pkg
+          ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
+      ';
+      push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
+                       join(',', @classnums ).
+                   ' )'
+        if @classnums;
+    }
   }
 
   # taxclass
@@ -564,25 +573,8 @@ if ( $cgi->param('nottax') ) {
   }
 
   # specific taxnums
-  if ( $cgi->param('taxnum') ) {
-    my $taxnum_in = join(',', 
-      grep /^\d+$/, $cgi->param('taxnum')
-    );
-    push @where, "cust_main_county.taxnum IN ($taxnum_in)"
-      if $taxnum_in;
-  }
-
-  # report group (itemdesc)
-  if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
-    my ( $group_op, $group_value ) = ( $1, $2 );
-    if ( $group_op eq '=' ) {
-      #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
-      push @where, 'itemdesc = '. dbh->quote($group_value);
-    } elsif ( $group_op eq '!=' ) {
-      push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
-    } else {
-      die "guru meditation #00de: group_op $group_op\n";
-    }
+  if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
+    push @where, "cust_main_county.taxnum IN ($1)";
   }
 
   # itemdesc, for some reason
@@ -679,6 +671,28 @@ if ( $cgi->param('credit') ) {
 
 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 
+#salesnum
+if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
+
+  my $salesnum = $1;
+  my $sales = FS::sales->by_key($salesnum)
+    or die "salesnum $salesnum not found";
+
+  my $subsearch = $sales->cust_bill_pkg_search('', '',
+    'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
+    'paid'            => ($cgi->param('paid') ? 1 : 0),
+    'classnum'        => scalar($cgi->param('classnum'))
+  );
+  $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
+
+  my $extra_sql = $subsearch->{extra_sql};
+  $extra_sql =~ s/^WHERE//;
+  push @where, $extra_sql;
+
+  $cgi->param('classnum', 0) unless $cgi->param('classnum');
+}
+
+
 my $where = join(' AND ', @where);
 $where &&= "WHERE $where";