FS/FS/Report/Table.pm

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