X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable%2FMonthly.pm;h=9e7a2882f56d1c1eac327c10dbbf5edce280f28f;hb=65f0f73df04de553919ee50145376955da20b315;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..9e7a2882f 100644 --- a/FS/FS/Report/Table/Monthly.pm +++ b/FS/FS/Report/Table/Monthly.pm @@ -311,28 +311,157 @@ 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 = "( + 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'}; - $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) - ); + 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' ); }