X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable%2FMonthly.pm;h=2d8dd7ee92dac0ffe8e0781a1f46281b43ea8668;hb=71c4c439c4a5b414802802beabc8060986d4a065;hp=d75f0be79f7eb3c00fb3347579e5e2a1cb076f4f;hpb=4ad29235ceb48ec0c5a0af07e6ccfcb64b40f466;p=freeside.git diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm index d75f0be79..2d8dd7ee9 100644 --- a/FS/FS/Report/Table/Monthly.pm +++ b/FS/FS/Report/Table/Monthly.pm @@ -1,13 +1,14 @@ package FS::Report::Table::Monthly; use strict; -use vars qw( @ISA ); +use vars qw( @ISA $DEBUG ); use Time::Local; use FS::UID qw( dbh ); use FS::Report::Table; use FS::CurrentUser; @ISA = qw( FS::Report::Table ); +$DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy =head1 NAME @@ -58,7 +59,14 @@ sub data { while ( $syear < $eyear || ( $syear == $eyear && $smonth < $emonth+1 ) ) { - push @{$data{label}}, "$smonth/$syear"; + if ( $self->{'doublemonths'} ) { + my($firstLabel,$secondLabel) = @{$self->{'doublemonths'}}; + push @{$data{label}}, "$smonth/$syear $firstLabel"; + push @{$data{label}}, "$smonth/$syear $secondLabel"; + } + else { + push @{$data{label}}, "$smonth/$syear"; + } my $speriod = timelocal(0,0,0,1,$smonth-1,$syear); push @{$data{speriod}}, $speriod; @@ -67,12 +75,25 @@ sub data { push @{$data{eperiod}}, $eperiod; my $col = 0; - my @row = (); - foreach my $item ( @{$self->{'items'}} ) { - my @param = $self->{'params'} ? @{ $self->{'params'}[$col] }: (); - my $value = $self->$item($speriod, $eperiod, $agentnum, @param); - #push @{$data{$item}}, $value; - push @{$data{data}->[$col++]}, $value; + my @items = @{$self->{'items'}}; + my $i; + for ( $i = 0; $i < scalar(@items); $i++ ) { + if ( $self->{'doublemonths'} ) { + my $item = $items[$i]; + my @param = $self->{'params'} ? @{ $self->{'params'}[$i] }: (); + my $value = $self->$item($speriod, $eperiod, $agentnum, @param); + push @{$data{data}->[$col]}, $value; + $item = $items[$i+1]; + @param = $self->{'params'} ? @{ $self->{'params'}[++$i] }: (); + $value = $self->$item($speriod, $eperiod, $agentnum, @param); + push @{$data{data}->[$col++]}, $value; + } + else { + my $item = $items[$i]; + my @param = $self->{'params'} ? @{ $self->{'params'}[$col] }: (); + my $value = $self->$item($speriod, $eperiod, $agentnum, @param); + push @{$data{data}->[$col++]}, $value; + } } } @@ -126,31 +147,32 @@ sub data { } sub invoiced { #invoiced - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $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) + . (%opt ? $self->for_custnum(%opt) : '') ); } sub netsales { #net sales - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - $self->invoiced($speriod,$eperiod,$agentnum) - - $self->credits( $speriod,$eperiod,$agentnum); + $self->invoiced($speriod,$eperiod,$agentnum,%opt) + - $self->netcredits($speriod,$eperiod,$agentnum,%opt); } #deferred revenue sub cashflow { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - $self->payments($speriod, $eperiod, $agentnum) - - $self->refunds( $speriod, $eperiod, $agentnum); + $self->payments($speriod, $eperiod, $agentnum, %opt) + - $self->refunds( $speriod, $eperiod, $agentnum, %opt); } sub netcashflow { @@ -161,12 +183,13 @@ sub netcashflow { } sub payments { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $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) + . (%opt ? $self->for_custnum(%opt) : '') ); } @@ -181,17 +204,18 @@ sub credits { } sub refunds { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $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) + . (%opt ? $self->for_custnum(%opt) : '') ); } sub netcredits { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill @@ -202,6 +226,7 @@ sub netcredits { $agentnum, 'cust_bill._date' ) + . (%opt ? $self->for_custnum(%opt) : '') ); } @@ -307,32 +332,219 @@ sub _subtract_11mo { timelocal($sec,$min,$hour,$mday,$mon,$year); } +sub cust_pkg_setup_cost { + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $where = ''; + my $comparison = ''; + if ( $opt{'classnum'} =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $comparison = 'IS NULL'; + } + else { + $comparison = "= $1"; + } + $where = "AND part_pkg.classnum $comparison"; + } + $agentnum ||= $opt{'agentnum'}; + + my $total_sql = " SELECT SUM(part_pkg.setup_cost) "; + $total_sql .= " FROM cust_pkg + LEFT JOIN cust_main USING ( custnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + WHERE pkgnum != 0 + $where + AND ".$self->in_time_period_and_agent( + $speriod, $eperiod, $agentnum, 'cust_pkg.setup'); + return $self->scalar_sql($total_sql); +} + +sub cust_pkg_recur_cost { + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $where = ''; + my $comparison = ''; + if ( $opt{'classnum'} =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $comparison = 'IS NULL'; + } + else { + $comparison = "= $1"; + } + $where = " AND part_pkg.classnum $comparison"; + } + $agentnum ||= $opt{'agentnum'}; + # duplication of in_time_period_and_agent + # because we do it a little differently here + $where .= " AND cust_main.agentnum = $agentnum" if $agentnum; + $where .= " AND ". + $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main'); + + my $total_sql = " SELECT SUM(part_pkg.recur_cost) "; + $total_sql .= " FROM cust_pkg + LEFT JOIN cust_main USING ( custnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + WHERE pkgnum != 0 + $where + AND cust_pkg.setup < $eperiod + AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL) + "; + return $self->scalar_sql($total_sql); +} + sub cust_bill_pkg { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; my $where = ''; + my $comparison = ''; if ( $opt{'classnum'} =~ /^(\d+)$/ ) { if ( $1 == 0 ) { - $where = "classnum IS NULL"; + $comparison = "IS NULL"; } else { - $where = "classnum = $1"; + $comparison = "= $1"; + } + + if ( $opt{'use_override'} ) { + $where = "AND ( + part_pkg.classnum $comparison AND pkgpart_override IS NULL OR + override.classnum $comparison AND pkgpart_override IS NOT NULL + )"; + } else { + $where = "AND part_pkg.classnum $comparison"; } } $agentnum ||= $opt{'agentnum'}; - $self->scalar_sql(" - SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) - FROM cust_bill_pkg + my $total_sql = + " SELECT COALESCE( SUM(cust_bill_pkg.setup + cust_bill_pkg.recur), 0 ) "; + + $total_sql .= + " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END " + if $opt{average_per_cust_pkg}; + + $total_sql .= + " 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 ) + LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart WHERE pkgnum != 0 - AND $where - AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) - ); + $where + AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum); + if ($opt{use_usage} && $opt{use_usage} eq 'recurring') { + my $total = $self->scalar_sql($total_sql); + my $usage = cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt + return $total-$usage; + } elsif ($opt{use_usage} && $opt{use_usage} eq 'usage') { + return cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt + } else { + return $self->scalar_sql($total_sql); + } +} + +sub cust_bill_pkg_detail { + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + + my @where = ( "cust_bill_pkg.pkgnum != 0" ); + my $comparison = ''; + if ( $opt{'classnum'} =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $comparison = "IS NULL"; + } else { + $comparison = "= $1"; + } + + if ( $opt{'use_override'} ) { + push @where, "( + part_pkg.classnum $comparison AND pkgpart_override IS NULL OR + override.classnum $comparison AND pkgpart_override IS NOT NULL + )"; + } else { + push @where, "part_pkg.classnum $comparison"; + } + } + + if ( $opt{'usageclass'} =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $comparison = "IS NULL"; + } else { + $comparison = "= $1"; + } + + push @where, "cust_bill_pkg_detail.classnum $comparison"; + } + + $agentnum ||= $opt{'agentnum'}; + + my $where = join( ' AND ', @where ); + + my $total_sql = " SELECT SUM(amount) "; + + $total_sql .= + " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END " + if $opt{average_per_cust_pkg}; + + $total_sql .= + " FROM cust_bill_pkg_detail + LEFT JOIN cust_bill_pkg USING ( billpkgnum ) + LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum + LEFT JOIN cust_main USING ( custnum ) + LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart + WHERE $where + AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum); + + $self->scalar_sql($total_sql); + +} + +sub cust_bill_pkg_discount { + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + + #my $where = ''; + #my $comparison = ''; + #if ( $opt{'classnum'} =~ /^(\d+)$/ ) { + # if ( $1 == 0 ) { + # $comparison = "IS NULL"; + # } else { + # $comparison = "= $1"; + # } + # + # if ( $opt{'use_override'} ) { + # $where = "( + # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR + # override.classnum $comparison AND pkgpart_override IS NOT NULL + # )"; + # } else { + # $where = "part_pkg.classnum $comparison"; + # } + #} + + $agentnum ||= $opt{'agentnum'}; + + my $total_sql = + " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) "; + + #$total_sql .= + # " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END " + # if $opt{average_per_cust_pkg}; + + $total_sql .= + " FROM cust_bill_pkg_discount + LEFT JOIN cust_bill_pkg USING ( billpkgnum ) + LEFT JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) + WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum); + # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum ) + # LEFT JOIN discount USING ( discountnum ) + # LEFT JOIN cust_pkg USING ( pkgnum ) + # LEFT JOIN part_pkg USING ( pkgpart ) + # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart + + return $self->scalar_sql($total_sql); + } sub setup_pkg { shift->pkg_field( @_, 'setup' ); } @@ -379,9 +591,16 @@ sub in_time_period_and_agent { $sql; } +sub for_custnum { + my ( $self, %opt ) = @_; + return '' unless $opt{'custnum'}; + $opt{'custnum'} =~ /^\d+$/ ? " and custnum = $opt{custnum} " : ''; +} + sub scalar_sql { my( $self, $sql ) = ( shift, shift ); my $sth = dbh->prepare($sql) or die dbh->errstr; + warn "FS::Report::Table::Monthly\n$sql\n" if $DEBUG; $sth->execute or die "Unexpected error executing statement $sql: ". $sth->errstr; $sth->fetchrow_arrayref->[0] || 0;