X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=4b22b60b85bb801369bf80397e27af930267b77e;hb=e0e76b55a2f83c19e4114eefe4dabcab092808b4;hp=0a0d24a7985ac1c4f4adbbcf93451199a35931d4;hpb=57e3a0e08b81d52851314c60f37115a05b9be79e;p=freeside.git

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 0a0d24a79..4b22b60b8 100644
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -229,7 +229,8 @@ sub receipts { #net payments
   my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay';
   if ( $opt{'setuprecur'} ) {
     $sql = 'SELECT SUM('.
-            FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt).
+            #in practice, but not appearance, paid_sql accepts end before start
+            FS::cust_bill_pkg->paid_sql($eperiod, $speriod, %opt).
            ') FROM cust_bill_pkg';
   }
 
@@ -272,17 +273,62 @@ sub netrefunds {
 
 sub discounted {
   my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
-  $self->scalar_sql('SELECT SUM(cust_bill_pkg_discount.amount)
-    FROM cust_bill_pkg_discount
-      JOIN cust_bill_pkg USING  ( billpkgnum )
-      JOIN cust_bill     USING  ( invnum )
-      JOIN cust_main     USING  ( custnum )
-    WHERE '. $self->in_time_period_and_agent( $speriod,
-                                              $eperiod,
-                                              $agentnum,
-                                              'cust_bill._date'
-                                            ).
-              $self->for_opts(%opt)
+
+  my $sql = 'SELECT SUM(';
+  if ($opt{'setuprecur'}) {
+    # (This isn't exact but it works in most cases.)
+    # When splitting into setup/recur values, 
+    # if the discount is allowed to apply to setup fees (discount.setup = 'Y')
+    # then split it between the "setup" and "recurring" rows in proportion to 
+    # the "unitsetup" and "unitrecur" fields of the line item. 
+    $sql .= <<EOF;
+CASE
+  WHEN discount.setup = 'Y' 
+    AND ((COALESCE(cust_bill_pkg.unitsetup,0) > 0) 
+          OR (COALESCE(cust_bill_pkg.unitrecur,0) > 0))
+  THEN
+EOF
+    if ($opt{'setuprecur'} eq 'setup') {
+      $sql .= '    (COALESCE(cust_bill_pkg.unitsetup,0)';
+    } elsif ($opt{'setuprecur'} eq 'recur') {
+      $sql .= '    (COALESCE(cust_bill_pkg.unitrecur,0)';
+    } else {
+      die 'Unrecognized value for setuprecur';
+    }
+    $sql .= ' / (COALESCE(cust_bill_pkg.unitsetup,0) + COALESCE(cust_bill_pkg.unitrecur,0)))';
+    $sql .= " * cust_bill_pkg_discount.amount\n";
+    # Otherwise, show it all as "recurring"
+    if ($opt{'setuprecur'} eq 'setup') {
+      $sql .= "  ELSE 0\n";
+    } elsif ($opt{'setuprecur'} eq 'recur') {
+      $sql .= "  ELSE cust_bill_pkg_discount.amount\n";
+    }
+    $sql .= "END\n";
+  } else {
+    # simple case, no setuprecur
+    $sql .= "cust_bill_pkg_discount.amount\n";
+  }
+  $sql .= <<EOF;
+) FROM cust_bill_pkg_discount
+  JOIN cust_bill_pkg     USING  ( billpkgnum )
+  JOIN cust_bill         USING  ( invnum )
+  JOIN cust_main         USING  ( custnum )
+EOF
+  if ($opt{'setuprecur'}) {
+    $sql .= <<EOF;
+  JOIN cust_pkg_discount USING ( pkgdiscountnum )
+  LEFT JOIN discount          USING ( discountnum )
+EOF
+  }
+  $self->scalar_sql(
+    $sql 
+    . 'WHERE '
+    . $self->in_time_period_and_agent( $speriod,
+                                       $eperiod,
+                                       $agentnum,
+                                       'cust_bill._date'
+                                      )
+    . $self->for_opts(%opt)
   );
 }
 
@@ -439,9 +485,9 @@ sub cust_pkg_recur_cost {
 
 =item cust_bill_pkg: the total package charges on invoice line items.
 
-'charges': limit the type of charges included (setup, recur, usage, discount).
-Should be a string containing one or more of 'S', 'R', 'U', or 'D'; if 
-unspecified, defaults to all three.
+'charges': limit the type of charges included (setup, recur, usage, discount, taxes).
+Should be a string containing one or more of 'S', 'R', or 'U'; or 'D' or 'T' (discount
+and taxes should not be combined with the others.)  If unspecified, defaults to 'SRU'.
 
 'classnum': limit to this package class.
 
@@ -471,6 +517,7 @@ sub cust_bill_pkg {
   $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
   $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
   $sum += $self->cust_bill_pkg_discount(@_) if $charges{D};
+  $sum += $self->cust_bill_pkg_taxes(@_) if $charges{T};
 
   if ($opt{'average_per_cust_pkg'}) {
     my $count = $self->cust_bill_pkg_count_pkgnum(@_);
@@ -681,6 +728,58 @@ sub cust_bill_pkg_discount {
   $self->scalar_sql($total_sql);
 }
 
+sub cust_bill_pkg_taxes {
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+  $agentnum ||= $opt{'agentnum'};
+
+  my @where = (
+    '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)',
+    $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+    $self->with_report_option(%opt),
+    $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
+    $self->with_refnum(%opt),
+    $self->with_cust_classnum(%opt)
+  );
+
+  my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_tax_location.amount),0)
+    FROM cust_bill_pkg
+    $cust_bill_pkg_join
+    LEFT JOIN cust_bill_pkg_tax_location 
+      ON (cust_bill_pkg.billpkgnum = cust_bill_pkg_tax_location.taxable_billpkgnum)
+    WHERE " . join(' AND ', grep $_, @where);
+
+  $self->scalar_sql($total_sql);
+}
+
+#all credits applied to matching pkg line items (ie not taxes or fees)
+
+sub cust_bill_pkg_credits {
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+  $agentnum ||= $opt{'agentnum'};
+
+  my @where = (
+    '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)',
+    $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+    $self->with_report_option(%opt),
+    $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
+    $self->with_refnum(%opt),
+    $self->with_cust_classnum(%opt)
+  );
+
+  my $total_sql = "SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
+    FROM cust_bill_pkg
+    $cust_bill_pkg_join
+    LEFT JOIN cust_credit_bill_pkg 
+      USING ( billpkgnum )
+    WHERE " . join(' AND ', grep $_, @where);
+
+  $self->scalar_sql($total_sql);
+}
+
 ##### package churn report #####
 
 =item active_pkg: The number of packages that were active at the start of