X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill_pkg.cgi;h=820b17864f84cc1cd3c437b107238207252e51a0;hb=d54109cc3035ce63ab0f6c2ec94317e378887d49;hp=f2a5ccd4f1a25da1389494abdb02167481ff13f0;hpb=34ca0c7b49bb6411155a4b8023598d3dd7d11a56;p=freeside.git

diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index f2a5ccd4f..820b17864 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -1,32 +1,32 @@
-<% include( 'elements/search.html',
-                 'title'       => 'Line items',
-                 'name'        => 'line items',
+<& elements/search.html,
+                 'title'       => emt('Line items'),
+                 'name'        => emt('line items'),
                  'query'       => $query,
                  'count_query' => $count_query,
                  'count_addl'  => [ $money_char. '%.2f total',
                                     $unearned ? ( $money_char. '%.2f unearned revenue' ) : (),
                                   ],
                  'header'      => [
-                   #'#',
-                   'Description',
+                   emt('Description'),
                    ( $unearned
-                     ? ( 'Unearned', 'Owed', 'Payment date' )
-                     : ( 'Setup charge' )
+                     ? ( emt('Unearned'), 
+                         emt('Owed'), # useful in 'paid' mode?
+                         emt('Payment date') )
+                     : ( emt('Setup charge') )
                    ),
                    ( $use_usage eq 'usage'
-                     ? 'Usage charge'
-                     : 'Recurring charge'
+                     ? emt('Usage charge')
+                     : emt('Recurring charge')
                    ),
                    ( $unearned
-                     ? ( 'Charge start', 'Charge end' )
+                     ? ( emt('Charge start'), emt('Charge end') )
                      : ()
                    ),
-                   'Invoice',
-                   'Date',
+                   emt('Invoice'),
+                   emt('Date'),
                    FS::UI::Web::cust_header(),
                  ],
                  'fields'      => [
-                   #'billpkgnum',
                    sub { $_[0]->pkgnum > 0
                            ? $_[0]->get('pkg')      # possibly use override.pkg
                            : $_[0]->get('itemdesc') # but i think this correct
@@ -41,9 +41,10 @@
                            $elapsed = 0 if $elapsed < 0;
 
                            my $remaining = 1 - $elapsed/$period;
+                           my $base = ($unearned_mode eq 'paid' ? 'total_paid' : 'recur');
 
                            sprintf($money_char. '%.2f',
-                             $remaining * $cust_bill_pkg->recur );
+                             $remaining * $cust_bill_pkg->$base );
 
                          } else {
                            sprintf($money_char.'%.2f', $cust_bill_pkg->setup );
@@ -74,6 +75,18 @@
                    sub { time2str('%b %d %Y', shift->_date ) },
                    \&FS::UI::Web::cust_fields,
                  ],
+                 'sort_fields' => [
+                   '',
+                   'setup', #broken in $unearned case i guess
+                   ( $unearned ? ('', '') : () ),
+                   ( $use_usage eq 'recurring' ? 'recur - usage' :
+                     $use_usage eq 'usage'     ? 'usage'
+                                               : 'recur'
+                   ),
+                   ( $unearned ? ('sdate', 'edate') : () ),
+                   'invnum',
+                   '_date',
+                 ],
                  'links'       => [
                    #'',
                    '',
@@ -116,8 +129,7 @@
                               '',
                               FS::UI::Web::cust_styles(),
                             ],
-           )
-%>
+&>
 <%init>
 
 #LOTS of false laziness below w/cust_credit_bill_pkg.cgi
@@ -128,6 +140,12 @@ die "access denied"
 my $conf = new FS::Conf;
 
 my $unearned = '';
+my $unearned_mode = '';
+my $unearned_base = '';
+
+my @select = ( 'cust_bill_pkg.*',
+               'cust_bill._date', );
+my ($join_cust, $join_pkg ) = ('', '');
 
 #here is the agent virtualization
 my $agentnums_sql =
@@ -136,8 +154,20 @@ my $agentnums_sql =
 my @where = ( $agentnums_sql );
 
 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
-push @where, "_date >= $beginning",
-             "_date <= $ending";
+
+if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
+  push @where, FS::cust_main->cust_status_sql . " = '$1'";
+}
+
+if ( $cgi->param('distribute') == 1 ) {
+  push @where, "sdate <= $ending",
+               "edate >  $beginning",
+  ;
+}
+else {
+  push @where, "cust_bill._date >= $beginning",
+               "cust_bill._date <= $ending";
+}
 
 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.agentnum = $1";
@@ -193,18 +223,15 @@ if ( $cgi->param('taxclass')
   #
   #} else {
 
-    push @where,
-      ' ( '. join(' OR ',
-                    map ' part_pkg.taxclass = '.dbh->quote($_),
-                        $cgi->param('taxclass')
-                 ).
-      ' ) ';
+    push @where, ' part_pkg.taxclass IN ( '.
+                   join(', ', map dbh->quote($_), $cgi->param('taxclass') ).
+                 ' ) ';
 
   #}
 
 }
 
-my @loc_param = qw( city county state country );
+my @loc_param = qw( district city county state country );
 
 if ( $cgi->param('out') ) {
 
@@ -252,7 +279,7 @@ if ( $cgi->param('out') ) {
 
           my %ph = ( 'county' => dbh->quote($_),
                      map { $_ => dbh->quote( $cgi->param($_) ) }
-                       qw( city state country )
+                       qw( district city state country )
                    );
 
           my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
@@ -294,8 +321,11 @@ if ( $cgi->param('out') ) {
     #warn "neither nottax nor istax parameters specified";
   }
 
-  if ( $cgi->param('taxclassNULL') ) {
-
+  if ( $cgi->param('taxclassNULL')
+       && ! $cgi->param('istax')  #no part_pkg.taxclass in this case
+                                  #(see comment above?)
+     )
+  {
     my %hash = ( 'country' => scalar($cgi->param('country')) );
     foreach (qw( state county )) {
       $hash{$_} = scalar($cgi->param($_)) if $cgi->param($_);
@@ -304,43 +334,55 @@ if ( $cgi->param('out') ) {
     die "unknown base region for empty taxclass" unless $cust_main_county;
 
     my $same_sql = $cust_main_county->sql_taxclass_sameregion;
+    $same_sql =~ s/taxclass/part_pkg.taxclass/g;
     push @where, $same_sql if $same_sql;
 
   }
 
 } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
 
-  # this should really be shoved out to FS::cust_pkg->location_sql or something
-  # along with the code in report_newtax.cgi
-
-  my %pn = (
-   'county'        => 'tax_rate_location.county',
-   'state'         => 'tax_rate_location.state',
-   'city'          => 'tax_rate_location.city',
-   'locationtaxid' => 'cust_bill_pkg_tax_rate_location.locationtaxid',
-  );
-
-  my %ph = map { ( $pn{$_} => dbh->quote( $cgi->param($_) || '' ) ) }
-           qw( city county state locationtaxid );
-
-  push @where,
-    join( ' AND ', map { "( $_ = $ph{$_} OR $ph{$_} = '' AND $_ IS NULL)" }
-                   keys %ph
-    );
+  push @where, FS::tax_rate_location->location_sql(
+                 map { $_ => (scalar($cgi->param($_)) || '') }
+                   qw( district city county state locationtaxid )
+               );
 
 } elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) {
 
   $unearned = $1;
+  $unearned_mode = $cgi->param('mode');
 
   push @where, "cust_bill_pkg.sdate < $unearned",
                "cust_bill_pkg.edate > $unearned",
                "cust_bill_pkg.recur != 0",
-               "part_pkg.freq != '0'",
+               "part_pkg.freq != '0'";
+
+  if ( !$cgi->param('include_monthly') ) {
+    push @where,
                "part_pkg.freq != '1'",
                "part_pkg.freq NOT LIKE '%h'",
                "part_pkg.freq NOT LIKE '%d'",
                "part_pkg.freq NOT LIKE '%w'";
+  }
 
+  if ( !$unearned_mode or $unearned_mode eq 'billed' ) {
+    $unearned_base = 'cust_bill_pkg.recur';
+  }
+  elsif ( $unearned_mode eq 'paid' ) {
+    $join_pkg .= "JOIN (
+      SELECT billpkgnum, SUM(cust_bill_pay_pkg.amount) AS total_paid
+      FROM      cust_bill_pay_pkg
+           JOIN cust_bill_pay USING (billpaynum)
+           JOIN cust_pay      USING (paynum)
+      WHERE cust_bill_pay_pkg.setuprecur = 'recur'
+        AND cust_pay._date <= $unearned
+      GROUP BY billpkgnum
+    ) AS cust_bill_pkg_paid USING (billpkgnum)";
+    $unearned_base = 'total_paid';
+    push @select, 'total_paid';
+  }
+  else {
+    die "invalid mode '$unearned_mode'";
+  }
 }
 
 if ( $cgi->param('itemdesc') ) {
@@ -452,14 +494,22 @@ if ( $cgi->param('pkg_tax') ) {
 
 } else {
 
-  $count_query = "SELECT COUNT(*), ";
+  if ( $use_usage ) {
+    $count_query = "SELECT COUNT(*), ";
+  } else {
+    $count_query = "SELECT COUNT(DISTINCT billpkgnum), ";
+  }
 
   if ( $use_usage eq 'recurring' ) {
     $count_query .= "SUM(setup + recur - usage)";
   } elsif ( $use_usage eq 'usage' ) {
     $count_query .= "SUM(usage)";
   } elsif ( $unearned ) {
-    $count_query .= "SUM(cust_bill_pkg.recur)";
+    $count_query .= "SUM($unearned_base)";
+  } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
+    $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))";
+  } elsif ( $cgi->param('iscredit') eq 'rate') {
+    $count_query .= "SUM( cust_credit_bill_pkg.amount )";
   } else {
     $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
   }
@@ -479,23 +529,21 @@ if ( $cgi->param('pkg_tax') ) {
 
     my $remaining = "(1 - $elapsed/$period)";
 
-    $count_query .= ", SUM($remaining * cust_bill_pkg.recur)";
+    $count_query .= ", SUM($remaining * $unearned_base)";
 
   }
 
 }
 
-my $join_cust =  '      JOIN cust_bill USING ( invnum ) 
-                   LEFT JOIN cust_main USING ( custnum ) ';
+$join_cust =  '      JOIN cust_bill USING ( invnum ) 
+                LEFT JOIN cust_main USING ( custnum ) ';
 
-
-my $join_pkg;
 if ( $cgi->param('nottax') ) {
 
-  $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 ';
+  $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 ';
   $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) '
     if $conf->exists('tax-pkg_address');
 
@@ -554,9 +602,6 @@ if ($use_usage) {
   $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
 }
 
-my @select = ( 'cust_bill_pkg.*',
-               'cust_bill._date', );
-
 push @select, 'part_pkg.pkg',
               'part_pkg.freq',
   unless $cgi->param('istax');
@@ -570,7 +615,7 @@ my $query = {
   'hashref'   => {},
   'select'    => join(', ', @select ),
   'extra_sql' => $where,
-  'order_by'  => 'ORDER BY _date, billpkgnum',
+  'order_by'  => 'ORDER BY cust_bill._date, billpkgnum',
 };
 
 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
@@ -580,9 +625,8 @@ my $conf = new FS::Conf;
 my $money_char = $conf->config('money_char') || '$';
 
 my $owed_sub = sub {
-  $money_char. shift->owed_recur; #_recur :/
+  $money_char . shift->owed_recur;
 };
-
 my $payment_date_sub = sub {
   #my $cust_bill_pkg = shift;
   my @cust_pay = sort { $a->_date <=> $b->_date }