X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable%2FMonthly.pm;h=ee4dc5fe8b8fdeaf939d17ff69552e6b5ec42fc5;hb=044e4ea5533f1c14697b7ad408dc0cf0e0327abb;hp=e7f05e5f2b0c624e0f9206f69d3dc4327f99c256;hpb=f29c752d6f9e813c10295b334eefb924216e34cf;p=freeside.git

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index e7f05e5f2..ee4dc5fe8 100644
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -1,15 +1,13 @@
 package FS::Report::Table::Monthly;
 
 use strict;
-use vars qw( @ISA $expenses_kludge );
-use Time::Local;
-use FS::UID qw( dbh );
+use vars qw( @ISA );
+use FS::UID qw(dbh);
 use FS::Report::Table;
+use Time::Local qw( timelocal );
 
 @ISA = qw( FS::Report::Table );
 
-$expenses_kludge = 0;
-
 =head1 NAME
 
 FS::Report::Table::Monthly - Tables of report data, indexed monthly
@@ -24,245 +22,237 @@ FS::Report::Table::Monthly - Tables of report data, indexed monthly
     'start_year'  => 2000,
     'end_month'   => 4,
     'end_year'    => 2020,
+    #opt
+    'agentnum'    => 54
+    'refnum'      => 54
+    'params'      => [ [ 'paramsfor', 'item_one' ], [ 'item', 'two' ] ], # ...
+    'remove_empty' => 1, #collapse empty rows, default 0
+    'item_labels' => [ ], #useful with remove_empty
   );
 
   my $data = $report->data;
 
-=head1 METHODS
+=head1 PARAMETERS
+
+=head2 TIME PERIOD
+
+C<start_month>, C<start_year>, C<end_month>, and C<end_year> specify the date
+range to be included in the report.  The start and end months are included.
+Each month's values are summed from midnight on the first of the month to 
+23:59:59 on the last day of the month.
+
+=head2 REPORT ITEMS
 
 =over 4
 
-=item data
+=item items: An arrayref of observables to calculate for each month.  See 
+L<FS::Report::Table> for a list of observables and their parameters.
 
-Returns a hashref of data (!! describe)
+=item params: An arrayref, parallel to C<items>, of arrayrefs of parameters
+(in paired name/value form) to be passed to the observables.
 
-=cut
+=item cross_params: Cross-product parameters.  This must be an arrayref of 
+arrayrefs of parameters (paired name/value form).  This creates an additional 
+"axis" (orthogonal to the time and C<items> axes) in which the item is 
+calculated once with each set of parameters in C<cross_params>.  These 
+parameters are merged with those in C<params>.  Instead of being nested two
+levels, C<data> will be nested three levels, with the third level 
+corresponding to this arrayref.
 
-sub data {
-  my $self = shift;
+=back
 
-  my $smonth = $self->{'start_month'};
-  my $syear = $self->{'start_year'};
-  my $emonth = $self->{'end_month'};
-  my $eyear = $self->{'end_year'};
-  my $agentnum = $self->{'agentnum'};
+=head2 FILTERING
 
-  my %data;
+=over 4
 
-  while ( $syear < $eyear || ( $syear == $eyear && $smonth < $emonth+1 ) ) {
+=item agentnum: Limit to customers with this agent.
 
-    push @{$data{label}}, "$smonth/$syear";
+=item refnum: Limit to customers with this advertising source.
 
-    my $speriod = timelocal(0,0,0,1,$smonth-1,$syear);
-    push @{$data{speriod}}, $speriod;
-    if ( ++$smonth == 13 ) { $syear++; $smonth=1; }
-    my $eperiod = timelocal(0,0,0,1,$smonth-1,$syear);
-    push @{$data{eperiod}}, $eperiod;
-  
-    foreach my $item ( @{$self->{'items'}} ) {
-      push @{$data{$item}}, $self->$item($speriod, $eperiod, $agentnum);
-    }
+=item remove_empty: Set this to a true value to hide rows that contain 
+only zeroes.  The C<indices> array in the returned data will list the item
+indices that are actually present in the output so that you know what they
+are.  Ignored if C<cross_params> is in effect.
 
-  }
+=back
 
-  \%data;
+=head2 PASS-THROUGH
 
-}
+C<item_labels>, C<colors>, and C<links> may be specified as arrayrefs
+parallel to C<items>.  Those values will be returned in C<data>, with any
+hidden rows (due to C<remove_empty>) filtered out, which is the only 
+reason to do this.  Now that we have C<indices> it's probably better to 
+use that.
 
-sub invoiced { #invoiced
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $self->scalar_sql("
-    SELECT SUM(charged)
-      FROM cust_bill
-        LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-  );
-}
+=head1 RETURNED DATA
 
-sub netsales { #net sales
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+The C<data> method runs the report and returns a hashref of the following:
 
-  my $credited = $self->scalar_sql("
-    SELECT SUM(cust_credit_bill.amount)
-      FROM cust_credit_bill
-        LEFT JOIN cust_bill USING ( invnum  )
-        LEFT JOIN cust_main USING ( custnum )
-    WHERE ".  $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill')
-  );
+=over 4
 
-  #horrible local kludge
-  my $expenses = !$expenses_kludge ? 0 : $self->scalar_sql("
-    SELECT SUM(cust_bill_pkg.setup)
-      FROM cust_bill_pkg
-        LEFT JOIN cust_bill USING ( invnum  )
-        LEFT JOIN cust_main USING ( custnum )
-        LEFT JOIN cust_pkg  USING ( pkgnum  )
-        LEFT JOIN part_pkg  USING ( pkgpart )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill'). "
-        AND LOWER(part_pkg.pkg) LIKE 'expense _%'
-  ");
-
-  $self->invoiced($speriod,$eperiod,$agentnum) - $credited - $expenses;
-}
+=item label
 
-#deferred revenue
+Month labels, in MM/YYYY format.
 
-sub receipts { #cashflow
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
+=item speriod, eperiod
 
-  my $refunded = $self->scalar_sql("
-    SELECT SUM(refund)
-      FROM cust_refund
-        LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-  );
+Absolute start and end times of each month, in unix time format.
 
-  #horrible local kludge that doesn't even really work right
-  my $expenses = !$expenses_kludge ? 0 : $self->scalar_sql("
-    SELECT SUM(cust_bill_pay.amount)
-      FROM cust_bill_pay
-        LEFT JOIN cust_bill USING ( invnum  )
-        LEFT JOIN cust_main USING ( custnum )
-    WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill_pay'). "
-    AND 0 < ( SELECT COUNT(*) from cust_bill_pkg, cust_pkg, part_pkg
-              WHERE cust_bill.invnum = cust_bill_pkg.invnum
-              AND cust_pkg.pkgnum = cust_bill_pkg.pkgnum
-              AND cust_pkg.pkgpart = part_pkg.pkgpart
-              AND LOWER(part_pkg.pkg) LIKE 'expense _%'
-            )
-  ");
-  #    my $expenses_sql2 = "SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay, cust_bill_pkg, cust_bill, cust_pkg, part_pkg WHERE cust_bill_pay.invnum = cust_bill.invnum AND cust_bill.invnum = cust_bill_pkg.invnum AND cust_bill_pay._date >= $speriod AND cust_bill_pay._date < $eperiod AND cust_pkg.pkgnum = cust_bill_pkg.pkgnum AND cust_pkg.pkgpart = part_pkg.pkgpart AND LOWER(part_pkg.pkg) LIKE 'expense _%'";
-  
-  $self->payments($speriod, $eperiod, $agentnum) - $refunded - $expenses;
-}
+=item items
 
-sub payments {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $self->scalar_sql("
-    SELECT SUM(paid)
-      FROM cust_pay
-        LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-  );
-}
+The values passed in as C<items>, with any suppressed rows deleted.
 
-sub credits {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $self->scalar_sql("
-    SELECT SUM(amount)
-      FROM cust_credit
-        LEFT JOIN cust_main USING ( custnum )
-      WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
-  );
-}
+=item indices
 
-#these should be auto-generated
-sub invoiced_12mo {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $speriod = $self->_subtract_11mo($speriod);
-  $self->invoiced($speriod, $eperiod, $agentnum);
-}
+The indices of items in the input C<items> list that appear in the result
+set.  Useful for figuring out what they are when C<remove_empty> has deleted 
+some items.
 
-sub netsales_12mo {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $speriod = $self->_subtract_11mo($speriod);
-  $self->netsales($speriod, $eperiod, $agentnum);
-}
+=item item_labels, colors, links - see PASS-THROUGH above
 
-sub receipts_12mo {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $speriod = $self->_subtract_11mo($speriod);
-  $self->receipts($speriod, $eperiod, $agentnum);
-}
+=item data
 
-sub payments_12mo {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $speriod = $self->_subtract_11mo($speriod);
-  $self->payments($speriod, $eperiod, $agentnum);
-}
+The actual results.  An arrayref corresponding to C<label> (the time axis),
+containing arrayrefs corresponding to C<items>, containing either numbers
+or, if C<cross_params> is given, arrayrefs corresponding to C<cross_params>.
 
-sub credits_12mo {
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $speriod = $self->_subtract_11mo($speriod);
-  $self->credits($speriod, $eperiod, $agentnum);
-}
+=back
 
-#not being too bad with the false laziness
-use Time::Local qw(timelocal);
-sub _subtract_11mo {
-  my($self, $time) = @_;
-  my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
-  $mon -= 11;
-  if ( $mon < 0 ) { $mon+=12; $year--; }
-  timelocal($sec,$min,$hour,$mday,$mon,$year);
-}
+=cut
 
-# NEEDS TO BE AGENTNUM-capable
-sub canceled { #active
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $self->scalar_sql("
-    SELECT COUNT(*)
-      FROM cust_pkg
-        LEFT JOIN cust_main USING ( custnum )
-      WHERE 0 = ( SELECT COUNT(*)
-                    FROM cust_pkg
-                    WHERE cust_pkg.custnum = cust_main.custnum
-                      AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
-                )
-        AND cust_pkg.cancel > $speriod AND cust_pkg.cancel < $eperiod
-  ");
-}
- 
-# NEEDS TO BE AGENTNUM-capable
-sub newaccount { #newaccount
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $self->scalar_sql("
-     SELECT COUNT(*) FROM cust_pkg
-     WHERE cust_pkg.custnum = cust_main.custnum
-     AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
-     AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 )
-     AND cust_pkg.setup > $speriod AND cust_pkg.setup < $eperiod
-  ");
-}
- 
-# NEEDS TO BE AGENTNUM-capable
-sub suspended { #suspended
-  my( $self, $speriod, $eperiod, $agentnum ) = @_;
-  $self->scalar_sql("
-     SELECT COUNT(*) FROM cust_pkg
-     WHERE cust_pkg.custnum = cust_main.custnum
-     AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
-     AND 0 = ( SELECT COUNT(*) FROM cust_pkg
-               WHERE cust_pkg.custnum = cust_main.custnum
-               AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 )
-             )
-     AND cust_pkg.susp > $speriod AND cust_pkg.susp < $eperiod
-  ");
-}
+sub data {
+  local $FS::UID::AutoCommit = 0;
+  my $self = shift;
 
-sub in_time_period_and_agent {
-  my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
-  my $table = @_ ? shift().'.' : '';
-  my $sql = "${table}_date >= $speriod AND ${table}_date < $eperiod";
-  $sql .= " AND agentnum = $agentnum"
-    if $agentnum;
-  $sql;
-}
+  my $smonth  = $self->{'start_month'};
+  my $syear   = $self->{'start_year'};
+  my $emonth  = $self->{'end_month'};
+  my $eyear   = $self->{'end_year'};
+  # whether to extrapolate into the future
+  my $projecting = $self->{'projection'};
+
+  # sanity checks
+  if ( $eyear < $syear or
+      ($eyear == $syear and $emonth < $smonth) ) {
+    return { error => 'Start month must be before end month' };
+  }
+
+  my $agentnum = $self->{'agentnum'};
+  my $refnum = $self->{'refnum'};
+
+  if ( $projecting ) {
+
+    $self->init_projection;
+
+    my $thismonth = $smonth;
+    my $thisyear  = $syear;
+    while ( $thisyear < $eyear || 
+      ( $thisyear == $eyear and $thismonth <= $emonth )
+    ) {
+      my $speriod = timelocal(0,0,0,1,$thismonth-1,$thisyear);
+      $thismonth++;
+      if ( $thismonth == 13 ) { $thisyear++; $thismonth = 1; }
+      my $eperiod = timelocal(0,0,0,1,$thismonth-1,$thisyear);
+
+      $self->extend_projection($speriod, $eperiod);
+    }
+  }
+
+  my %data;
+
+  my $max_year  = $eyear;
+  my $max_month = $emonth;
+
+  while ( $syear < $max_year
+     || ( $syear == $max_year && $smonth < $max_month+1 ) ) {
 
-sub scalar_sql {
-  my( $self, $sql ) = ( shift, shift );
-  my $sth = dbh->prepare($sql) or die dbh->errstr;
-  $sth->execute
-    or die "Unexpected error executing statement $sql: ". $sth->errstr;
-  $sth->fetchrow_arrayref->[0] || 0;
+    push @{$data{label}}, "$smonth/$syear"; # sprintf?
+
+    my $speriod = timelocal(0,0,0,1,$smonth-1,$syear);
+    push @{$data{speriod}}, $speriod;
+    if ( ++$smonth == 13 ) { $syear++; $smonth=1; }
+    my $eperiod = timelocal(0,0,0,1,$smonth-1,$syear);
+    push @{$data{eperiod}}, $eperiod;
+
+    my $col = 0;
+    my @items = @{$self->{'items'}};
+    my $i;
+
+    for ( $i = 0; $i < scalar(@items); $i++ ) {
+      my $item = $items[$i];
+      my @param = $self->{'params'} ? @{ $self->{'params'}[$col] }: ();
+      push @param, 'project', $projecting;
+      push @param, 'refnum' => $refnum if $refnum;
+
+      if ( $self->{'cross_params'} ) {
+        my @xdata;
+        foreach my $xparam (@{ $self->{'cross_params'} }) {
+          # @$xparam is a list of additional params to merge into the list
+          my $value = $self->$item($speriod, $eperiod, $agentnum,
+                        @param, 
+                        @$xparam);
+          push @xdata, $value;
+        }
+        push @{$data{data}->[$col++]}, \@xdata;
+      } else {
+        my $value = $self->$item($speriod, $eperiod, $agentnum, @param);
+        push @{$data{data}->[$col++]}, $value;
+      }
+    }
+  }
+
+  #these need to get generalized, sheesh
+  $data{'items'}       = $self->{'items'};
+  $data{'item_labels'} = $self->{'item_labels'} || $self->{'items'};
+  $data{'colors'}      = $self->{'colors'};
+  $data{'links'}       = $self->{'links'} || [];
+
+  if ( !$self->{'cross_params'} and $self->{'remove_empty'} ) {
+
+    my $col = 0;
+    #these need to get generalized, sheesh
+    #(though we now return a list of item indices that are present in the 
+    #output, so the front-end code could do this)
+    my @newitems = ();
+    my @newlabels = ();
+    my @newdata = ();
+    my @newcolors = ();
+    my @newlinks = ();
+    my @indices = ();
+    foreach my $item ( @{$self->{'items'}} ) {
+
+      if ( grep { $_ != 0 } @{$data{'data'}->[$col]} ) {
+        push @newitems,  $data{'items'}->[$col];
+        push @newlabels, $data{'item_labels'}->[$col];
+        push @newdata,   $data{'data'}->[$col];
+        push @newcolors, $data{'colors'}->[$col];
+        push @newlinks,  $data{'links'}->[$col];
+        push @indices,   $col;
+      }
+
+      $col++;
+    }
+
+    $data{'items'}       = \@newitems;
+    $data{'item_labels'} = \@newlabels;
+    $data{'data'}        = \@newdata;
+    $data{'colors'}      = \@newcolors;
+    $data{'links'}       = \@newlinks;
+    $data{'indices'}     = \@indices;
+
+  }
+  # clean up after ourselves
+  #dbh->rollback;
+  # leave in until development is finished, for diagnostics
+  dbh->commit;
+
+  \%data;
 }
 
 =back
 
 =head1 BUGS
 
-Documentation.
-
 =head1 SEE ALSO
 
 =cut