FS/FS/Report/Table/Monthly.pm

   1 package FS::Report::Table::Monthly;
   2
   3 use strict;
   4 use vars qw( @ISA $DEBUG );
   5 use Time::Local;
   6 use FS::UID qw( dbh );
   7 use FS::Report::Table;
   8 use FS::CurrentUser;
   9
  10 @ISA = qw( FS::Report::Table );
  11 $DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy
  12
  13 =head1 NAME
  14
  15 FS::Report::Table::Monthly - Tables of report data, indexed monthly
  16
  17 =head1 SYNOPSIS
  18
  19   use FS::Report::Table::Monthly;
  20
  21   my $report = new FS::Report::Table::Monthly (
  22     'items' => [ 'invoiced', 'netsales', 'credits', 'receipts', ],
  23     'start_month' => 4,
  24     'start_year'  => 2000,
  25     'end_month'   => 4,
  26     'end_year'    => 2020,
  27     #opt
  28     'agentnum'    => 54
  29     'params'      => [ [ 'paramsfor', 'item_one' ], [ 'item', 'two' ] ], # ...
  30     'remove_empty' => 1, #collapse empty rows, default 0
  31     'item_labels' => [ ], #useful with remove_empty
  32   );
  33
  34   my $data = $report->data;
  35
  36 =head1 METHODS
  37
  38 =over 4
  39
  40 =item data
  41
  42 Returns a hashref of data (!! describe)
  43
  44 =cut
  45
  46 sub data {
  47   my $self = shift;
  48
  49   #use Data::Dumper;
  50   #warn Dumper($self);
  51
  52   my $smonth = $self->{'start_month'};
  53   my $syear = $self->{'start_year'};
  54   my $emonth = $self->{'end_month'};
  55   my $eyear = $self->{'end_year'};
  56   my $agentnum = $self->{'agentnum'};
  57
  58   my %data;
  59
  60   while ( $syear < $eyear || ( $syear == $eyear && $smonth < $emonth+1 ) ) {
  61
  62     push @{$data{label}}, "$smonth/$syear";
  63
  64     my $speriod = timelocal(0,0,0,1,$smonth-1,$syear);
  65     push @{$data{speriod}}, $speriod;
  66     if ( ++$smonth == 13 ) { $syear++; $smonth=1; }
  67     my $eperiod = timelocal(0,0,0,1,$smonth-1,$syear);
  68     push @{$data{eperiod}}, $eperiod;
  69
  70     my $col = 0;
  71     my @row = ();
  72     foreach my $item ( @{$self->{'items'}} ) {
  73       my @param = $self->{'params'} ? @{ $self->{'params'}[$col] }: ();
  74       my $value = $self->$item($speriod, $eperiod, $agentnum, @param);
  75       #push @{$data{$item}}, $value;
  76       push @{$data{data}->[$col++]}, $value;
  77     }
  78
  79   }
  80
  81   #these need to get generalized, sheesh
  82   $data{'items'}       = $self->{'items'};
  83   $data{'item_labels'} = $self->{'item_labels'} || $self->{'items'};
  84   $data{'colors'}      = $self->{'colors'};
  85   $data{'links'}       = $self->{'links'} || [];
  86
  87   #use Data::Dumper;
  88   #warn Dumper(\%data);
  89
  90   if ( $self->{'remove_empty'} ) {
  91
  92     #warn "removing empty rows\n";
  93
  94     my $col = 0;
  95     #these need to get generalized, sheesh
  96     my @newitems = ();
  97     my @newlabels = ();
  98     my @newdata = ();
  99     my @newcolors = ();
 100     my @newlinks = ();
 101     foreach my $item ( @{$self->{'items'}} ) {
 102
 103       if ( grep { $_ != 0 } @{$data{'data'}->[$col]} ) {
 104         push @newitems,  $data{'items'}->[$col];
 105         push @newlabels, $data{'item_labels'}->[$col];
 106         push @newdata,   $data{'data'}->[$col];
 107         push @newcolors, $data{'colors'}->[$col];
 108         push @newlinks,  $data{'links'}->[$col];
 109       }
 110
 111       $col++;
 112     }
 113
 114     $data{'items'}       = \@newitems;
 115     $data{'item_labels'} = \@newlabels;
 116     $data{'data'}        = \@newdata;
 117     $data{'colors'}      = \@newcolors;
 118     $data{'links'}       = \@newlinks;
 119
 120   }
 121
 122   #use Data::Dumper;
 123   #warn Dumper(\%data);
 124
 125   \%data;
 126
 127 }
 128
 129 sub invoiced { #invoiced
 130   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 131
 132   $self->scalar_sql("
 133     SELECT SUM(charged)
 134       FROM cust_bill
 135         LEFT JOIN cust_main USING ( custnum )
 136       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
 137   );
 138
 139 }
 140
 141 sub netsales { #net sales
 142   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 143
 144     $self->invoiced($speriod,$eperiod,$agentnum)
 145   - $self->netcredits($speriod,$eperiod,$agentnum);
 146 }
 147
 148 #deferred revenue
 149
 150 sub cashflow {
 151   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 152
 153     $self->payments($speriod, $eperiod, $agentnum)
 154   - $self->refunds( $speriod, $eperiod, $agentnum);
 155 }
 156
 157 sub netcashflow {
 158   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 159
 160     $self->receipts($speriod, $eperiod, $agentnum)
 161   - $self->netrefunds( $speriod, $eperiod, $agentnum);
 162 }
 163
 164 sub payments {
 165   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 166   $self->scalar_sql("
 167     SELECT SUM(paid)
 168       FROM cust_pay
 169         LEFT JOIN cust_main USING ( custnum )
 170       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
 171   );
 172 }
 173
 174 sub credits {
 175   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 176   $self->scalar_sql("
 177     SELECT SUM(amount)
 178       FROM cust_credit
 179         LEFT JOIN cust_main USING ( custnum )
 180       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
 181   );
 182 }
 183
 184 sub refunds {
 185   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 186   $self->scalar_sql("
 187     SELECT SUM(refund)
 188       FROM cust_refund
 189         LEFT JOIN cust_main USING ( custnum )
 190       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
 191   );
 192 }
 193
 194 sub netcredits {
 195   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 196   $self->scalar_sql("
 197     SELECT SUM(cust_credit_bill.amount)
 198       FROM cust_credit_bill
 199         LEFT JOIN cust_bill USING ( invnum  )
 200         LEFT JOIN cust_main USING ( custnum )
 201       WHERE ". $self->in_time_period_and_agent( $speriod,
 202                                                 $eperiod,
 203                                                 $agentnum,
 204                                                 'cust_bill._date'
 205                                               )
 206   );
 207 }
 208
 209 sub receipts { #net payments
 210   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 211   $self->scalar_sql("
 212     SELECT SUM(cust_bill_pay.amount)
 213       FROM cust_bill_pay
 214         LEFT JOIN cust_bill USING ( invnum  )
 215         LEFT JOIN cust_main USING ( custnum )
 216       WHERE ". $self->in_time_period_and_agent( $speriod,
 217                                                 $eperiod,
 218                                                 $agentnum,
 219                                                 'cust_bill._date'
 220                                               )
 221   );
 222 }
 223
 224 sub netrefunds {
 225   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 226   $self->scalar_sql("
 227     SELECT SUM(cust_credit_refund.amount)
 228       FROM cust_credit_refund
 229         LEFT JOIN cust_credit USING ( crednum  )
 230         LEFT JOIN cust_main   USING ( custnum )
 231       WHERE ". $self->in_time_period_and_agent( $speriod,
 232                                                 $eperiod,
 233                                                 $agentnum,
 234                                                 'cust_credit._date'
 235                                               )
 236   );
 237 }
 238
 239 #these should be auto-generated or $AUTOLOADed or something
 240 sub invoiced_12mo {
 241   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 242   $speriod = $self->_subtract_11mo($speriod);
 243   $self->invoiced($speriod, $eperiod, $agentnum);
 244 }
 245
 246 sub netsales_12mo {
 247   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 248   $speriod = $self->_subtract_11mo($speriod);
 249   $self->netsales($speriod, $eperiod, $agentnum);
 250 }
 251
 252 sub receipts_12mo {
 253   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 254   $speriod = $self->_subtract_11mo($speriod);
 255   $self->receipts($speriod, $eperiod, $agentnum);
 256 }
 257
 258 sub payments_12mo {
 259   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 260   $speriod = $self->_subtract_11mo($speriod);
 261   $self->payments($speriod, $eperiod, $agentnum);
 262 }
 263
 264 sub credits_12mo {
 265   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 266   $speriod = $self->_subtract_11mo($speriod);
 267   $self->credits($speriod, $eperiod, $agentnum);
 268 }
 269
 270 sub netcredits_12mo {
 271   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 272   $speriod = $self->_subtract_11mo($speriod);
 273   $self->netcredits($speriod, $eperiod, $agentnum);
 274 }
 275
 276 sub cashflow_12mo {
 277   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 278   $speriod = $self->_subtract_11mo($speriod);
 279   $self->cashflow($speriod, $eperiod, $agentnum);
 280 }
 281
 282 sub netcashflow_12mo {
 283   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 284   $speriod = $self->_subtract_11mo($speriod);
 285   $self->cashflow($speriod, $eperiod, $agentnum);
 286 }
 287
 288 sub refunds_12mo {
 289   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 290   $speriod = $self->_subtract_11mo($speriod);
 291   $self->refunds($speriod, $eperiod, $agentnum);
 292 }
 293
 294 sub netrefunds_12mo {
 295   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 296   $speriod = $self->_subtract_11mo($speriod);
 297   $self->netrefunds($speriod, $eperiod, $agentnum);
 298 }
 299
 300
 301 #not being too bad with the false laziness
 302 use Time::Local qw(timelocal);
 303 sub _subtract_11mo {
 304   my($self, $time) = @_;
 305   my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
 306   $mon -= 11;
 307   if ( $mon < 0 ) { $mon+=12; $year--; }
 308   timelocal($sec,$min,$hour,$mday,$mon,$year);
 309 }
 310
 311 sub cust_pkg_setup_cost {
 312   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 313   my $where = '';
 314   my $comparison = '';
 315   if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
 316     if ( $1 == 0 ) {
 317       $comparison = 'IS NULL';
 318     }
 319     else {
 320       $comparison = "= $1";
 321     }
 322     $where = "AND part_pkg.classnum $comparison";
 323   }
 324   $agentnum ||= $opt{'agentnum'};
 325
 326   my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
 327   $total_sql .= " FROM cust_pkg
 328              LEFT JOIN cust_main USING ( custnum )
 329              LEFT JOIN part_pkg  USING ( pkgpart )
 330                   WHERE pkgnum != 0
 331                   $where
 332                   AND ".$self->in_time_period_and_agent(
 333                     $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
 334   return $self->scalar_sql($total_sql);
 335 }
 336
 337 sub cust_pkg_recur_cost {
 338   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 339   my $where = '';
 340   my $comparison = '';
 341   if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
 342     if ( $1 == 0 ) {
 343       $comparison = 'IS NULL';
 344     }
 345     else {
 346       $comparison = "= $1";
 347     }
 348     $where = " AND part_pkg.classnum $comparison";
 349   }
 350   $agentnum ||= $opt{'agentnum'};
 351   # duplication of in_time_period_and_agent
 352   # because we do it a little differently here
 353   $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
 354   $where .= " AND ".
 355           $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
 356
 357   my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
 358   $total_sql .= " FROM cust_pkg
 359              LEFT JOIN cust_main USING ( custnum )
 360              LEFT JOIN part_pkg  USING ( pkgpart )
 361                   WHERE pkgnum != 0
 362                   $where
 363                   AND cust_pkg.setup < $eperiod
 364                   AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
 365                   ";
 366   return $self->scalar_sql($total_sql);
 367 }
 368
 369 sub cust_bill_pkg {
 370   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 371
 372   my $where = '';
 373   my $comparison = '';
 374   if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
 375     if ( $1 == 0 ) {
 376       $comparison = "IS NULL";
 377     } else {
 378       $comparison = "= $1";
 379     }
 380
 381     if ( $opt{'use_override'} ) {
 382       $where = "AND (
 383         part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
 384         override.classnum $comparison AND pkgpart_override IS NOT NULL
 385       )";
 386     } else {
 387       $where = "AND part_pkg.classnum $comparison";
 388     }
 389   }
 390
 391   $agentnum ||= $opt{'agentnum'};
 392
 393   my $total_sql =
 394     " SELECT COALESCE( SUM(cust_bill_pkg.setup + cust_bill_pkg.recur), 0 ) ";
 395
 396   $total_sql .=
 397     " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
 398       if $opt{average_per_cust_pkg};
 399
 400   $total_sql .=
 401     " FROM cust_bill_pkg
 402         LEFT JOIN cust_bill USING ( invnum )
 403         LEFT JOIN cust_main USING ( custnum )
 404         LEFT JOIN cust_pkg USING ( pkgnum )
 405         LEFT JOIN part_pkg USING ( pkgpart )
 406         LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
 407       WHERE pkgnum != 0
 408         $where
 409         AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
 410
 411   if ($opt{use_usage} && $opt{use_usage} eq 'recurring') {
 412     my $total = $self->scalar_sql($total_sql);
 413     my $usage = cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt
 414     return $total-$usage;
 415   } elsif ($opt{use_usage} && $opt{use_usage} eq 'usage') {
 416     return cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt
 417   } else {
 418     return $self->scalar_sql($total_sql);
 419   }
 420 }
 421
 422 sub cust_bill_pkg_detail {
 423   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 424
 425   my @where = ( "cust_bill_pkg.pkgnum != 0" );
 426   my $comparison = '';
 427   if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
 428     if ( $1 == 0 ) {
 429       $comparison = "IS NULL";
 430     } else {
 431       $comparison = "= $1";
 432     }
 433
 434     if ( $opt{'use_override'} ) {
 435       push @where, "(
 436         part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
 437         override.classnum $comparison AND pkgpart_override IS NOT NULL
 438       )";
 439     } else {
 440       push @where, "part_pkg.classnum $comparison";
 441     }
 442   }
 443
 444   if ( $opt{'usageclass'} =~ /^(\d+)$/ ) {
 445     if ( $1 == 0 ) {
 446       $comparison = "IS NULL";
 447     } else {
 448       $comparison = "= $1";
 449     }
 450
 451     push @where, "cust_bill_pkg_detail.classnum $comparison";
 452   }
 453
 454   $agentnum ||= $opt{'agentnum'};
 455
 456   my $where = join( ' AND ', @where );
 457
 458   my $total_sql = " SELECT SUM(amount) ";
 459
 460   $total_sql .=
 461     " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
 462       if $opt{average_per_cust_pkg};
 463
 464   $total_sql .=
 465     " FROM cust_bill_pkg_detail
 466         LEFT JOIN cust_bill_pkg USING ( billpkgnum )
 467         LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
 468         LEFT JOIN cust_main USING ( custnum )
 469         LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
 470         LEFT JOIN part_pkg USING ( pkgpart )
 471         LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
 472       WHERE $where
 473         AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
 474
 475   $self->scalar_sql($total_sql);
 476
 477 }
 478
 479 sub cust_bill_pkg_discount {
 480   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 481
 482   #my $where = '';
 483   #my $comparison = '';
 484   #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
 485   #  if ( $1 == 0 ) {
 486   #    $comparison = "IS NULL";
 487   #  } else {
 488   #    $comparison = "= $1";
 489   #  }
 490   #
 491   #  if ( $opt{'use_override'} ) {
 492   #    $where = "(
 493   #      part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
 494   #      override.classnum $comparison AND pkgpart_override IS NOT NULL
 495   #    )";
 496   #  } else {
 497   #    $where = "part_pkg.classnum $comparison";
 498   #  }
 499   #}
 500
 501   $agentnum ||= $opt{'agentnum'};
 502
 503   my $total_sql =
 504     " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
 505
 506   #$total_sql .=
 507   #  " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
 508   #    if $opt{average_per_cust_pkg};
 509
 510   $total_sql .=
 511     " FROM cust_bill_pkg_discount
 512         LEFT JOIN cust_bill_pkg USING ( billpkgnum )
 513         LEFT JOIN cust_bill USING ( invnum )
 514         LEFT JOIN cust_main USING ( custnum )
 515       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
 516   #      LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
 517   #      LEFT JOIN discount USING ( discountnum )
 518   #      LEFT JOIN cust_pkg USING ( pkgnum )
 519   #      LEFT JOIN part_pkg USING ( pkgpart )
 520   #      LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
 521
 522   return $self->scalar_sql($total_sql);
 523
 524 }
 525
 526 sub setup_pkg  { shift->pkg_field( @_, 'setup' ); }
 527 sub susp_pkg   { shift->pkg_field( @_, 'susp'  ); }
 528 sub cancel_pkg { shift->pkg_field( @_, 'cancel'); }
 529
 530 sub pkg_field {
 531   my( $self, $speriod, $eperiod, $agentnum, $field ) = @_;
 532   $self->scalar_sql("
 533     SELECT COUNT(*) FROM cust_pkg
 534         LEFT JOIN cust_main USING ( custnum )
 535       WHERE ". $self->in_time_period_and_agent( $speriod,
 536                                                 $eperiod,
 537                                                 $agentnum,
 538                                                 "cust_pkg.$field",
 539                                               )
 540   );
 541
 542 }
 543
 544 #this is going to be harder..
 545 #sub unsusp_pkg {
 546 #  my( $self, $speriod, $eperiod, $agentnum ) = @_;
 547 #  $self->scalar_sql("
 548 #    SELECT COUNT(*) FROM h_cust_pkg
 549 #      WHERE
 550 #
 551 #}
 552
 553 sub in_time_period_and_agent {
 554   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
 555   my $col = @_ ? shift() : '_date';
 556
 557   my $sql = "$col >= $speriod AND $col < $eperiod";
 558
 559   #agent selection
 560   $sql .= " AND cust_main.agentnum = $agentnum"
 561     if $agentnum;
 562
 563   #agent virtualization
 564   $sql .= ' AND '.
 565           $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
 566
 567   $sql;
 568 }
 569
 570 sub scalar_sql {
 571   my( $self, $sql ) = ( shift, shift );
 572   my $sth = dbh->prepare($sql) or die dbh->errstr;
 573   warn "FS::Report::Table::Monthly\n$sql\n" if $DEBUG;
 574   $sth->execute
 575     or die "Unexpected error executing statement $sql: ". $sth->errstr;
 576   $sth->fetchrow_arrayref->[0] || 0;
 577 }
 578
 579 =back
 580
 581 =head1 BUGS
 582
 583 Documentation.
 584
 585 =head1 SEE ALSO
 586
 587 =cut
 588
 589 1;
 590