X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=17b12ae2340880e15ae661e1f61eeccaaddb6cdd;hp=da4916176ff75a416e1c1e53c2b84f7bb472f94f;hb=e9e0cf0989259b94d9758eceff448666a2e5a5cc;hpb=8cc50a2ad12ec3d5bd3f31db741290664064ef06 diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index da4916176..17b12ae23 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -141,7 +141,7 @@ sub payments { sub credits { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" - SELECT SUM(amount) + SELECT SUM(cust_credit.amount) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). @@ -390,9 +390,6 @@ unspecified, defaults to all three. 'use_override': for line items generated by an add-on package, use the class of the add-on rather than the base package. -'freq': limit to packages with this frequency. Currently uses the part_pkg -frequency, so term discounted packages may give odd results. - 'distribute': for non-monthly recurring charges, ignore the invoice date. Instead, consider the line item's starting/ending dates. Determine the fraction of the line item duration that falls within the specified @@ -421,7 +418,8 @@ my $cust_bill_pkg_join = ' 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'; + LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart + LEFT JOIN part_fee USING ( feepart )'; sub cust_bill_pkg_setup { my $self = shift; @@ -434,9 +432,9 @@ sub cust_bill_pkg_setup { $agentnum ||= $opt{'agentnum'}; my @where = ( - 'pkgnum != 0', + '(pkgnum != 0 OR feepart IS NOT NULL)', $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), - $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}), + $self->with_report_option(%opt), $self->in_time_period_and_agent($speriod, $eperiod, $agentnum), ); @@ -461,9 +459,9 @@ sub cust_bill_pkg_recur { my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg'; my @where = ( - 'pkgnum != 0', + '(pkgnum != 0 OR feepart IS NOT NULL)', $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), - $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}), + $self->with_report_option(%opt), ); push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'}; @@ -476,13 +474,14 @@ sub cust_bill_pkg_recur { $item_usage = 'usage'; #already calculated } else { - $item_usage = '( SELECT COALESCE(SUM(amount),0) + $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0) FROM cust_bill_pkg_detail WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )'; } my $recur_fraction = ''; if ( $opt{'distribute'} ) { + $where[0] = 'pkgnum != 0'; # specifically exclude fees push @where, "cust_main.agentnum = $agentnum" if $agentnum; push @where, "$cust_bill_pkg.sdate < $eperiod", @@ -521,7 +520,8 @@ Arguments as for C, plus: sub cust_bill_pkg_detail { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - my @where = ( "cust_bill_pkg.pkgnum != 0" ); + my @where = + ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" ); push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'}; @@ -532,11 +532,13 @@ sub cust_bill_pkg_detail { push @where, $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), $self->with_usageclass($opt{'usageclass'}), - $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}), + $self->with_report_option(%opt), ; if ( $opt{'distribute'} ) { - # then limit according to the usage time, not the billing date + # exclude fees + $where[0] = 'cust_bill_pkg.pkgnum != 0'; + # and limit according to the usage time, not the billing date push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill_pkg_detail.startdate' ); @@ -547,7 +549,7 @@ sub cust_bill_pkg_detail { ); } - my $total_sql = " SELECT SUM(amount) "; + my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) "; $total_sql .= " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END " @@ -561,6 +563,7 @@ sub cust_bill_pkg_detail { 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 + LEFT JOIN part_fee USING ( feepart ) WHERE ".join( ' AND ', grep $_, @where ); $self->scalar_sql($total_sql); @@ -683,14 +686,14 @@ sub with_classnum { @$classnum = grep /^\d+$/, @$classnum; my $in = 'IN ('. join(',', @$classnum). ')'; - if ( $use_override ) { - "( + my $expr = " ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL) - OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL ) - )"; - } else { - "COALESCE(part_pkg.classnum, 0) $in"; + OR ( COALESCE(part_fee.classnum, 0) $in AND feepart IS NOT NULL )"; + if ( $use_override ) { + $expr .= " + OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )"; } + "( $expr )"; } sub with_usageclass { @@ -708,44 +711,71 @@ sub with_usageclass { } sub with_report_option { - my ($self, $num, $use_override) = @_; - # $num can be a single number, or a comma-delimited list of numbers, - # or an arrayref. 0 matches the empty set - # or the word 'multiple' for all packages with more than one report class - return '' if !defined($num); - - $num = join(',', @$num) if ref($num); - - # stringify the set of report options for each pkgpart - my $table = $use_override ? 'override' : 'part_pkg'; - my $subselect = " - SELECT replace(optionname, 'report_option_', '') AS num - FROM part_pkg_option - WHERE optionname like 'report_option_%' - AND part_pkg_option.pkgpart = $table.pkgpart - ORDER BY num"; - - my $comparison; - if ( $num eq 'multiple' ) { - $comparison = "(SELECT COUNT(*) FROM ($subselect) AS x) > 1"; - } else { - - my @num = split(/\s*,\s*/, $num); - - #$comparison = "(SELECT COALESCE(string_agg(num, ','), '') FROM ( #Pg 9-ism - $comparison = "(SELECT COALESCE(array_to_string(array_agg(num), ','), '') - FROM ($subselect) AS x - ) = '". join(',', grep $_, @num). "'"; - - $comparison = "( $comparison OR NOT EXISTS ($subselect) )" - if grep !$_, @num; - + my ($self, %opt) = @_; + # %opt can contain: + # - report_optionnum: a comma-separated list of numbers. Zero means to + # include packages with _no_ report classes. + # - not_report_optionnum: a comma-separated list. Packages that have + # any of these report options will be excluded from the result. + # Zero does nothing. + # - use_override: also matches line items that are add-ons to a package + # matching the report class. + # - all_report_options: returns only packages that have ALL of the + # report classes listed in $num. Otherwise, will return packages that + # have ANY of those classes. + + my @num = ref($opt{'report_optionnum'}) + ? @{ $opt{'report_optionnum'} } + : split(/\s*,\s*/, $opt{'report_optionnum'}); + my @not_num = ref($opt{'not_report_optionnum'}) + ? @{ $opt{'not_report_optionnum'} } + : split(/\s*,\s*/, $opt{'not_report_optionnum'}); + my $null; + $null = 1 if ( grep {$_ == 0} @num ); + @num = grep {$_ > 0} @num; + @not_num = grep {$_ > 0} @not_num; + + # brute force + my $table = $opt{'use_override'} ? 'override' : 'part_pkg'; + my $op = ' OR '; + if ( $opt{'all_report_options'} ) { + if ( @num and $null ) { + return 'false'; # mutually exclusive criteria, so just bail out + } + $op = ' AND '; } - if ( $use_override ) { - # then also allow the non-override package to match - $comparison = "( $comparison OR " . $self->with_report_option($num) . ")"; + my @where_num = map { + "EXISTS(SELECT 1 FROM part_pkg_option ". + "WHERE optionname = 'report_option_$_' ". + "AND part_pkg_option.pkgpart = $table.pkgpart)" + } @num; + if ( $null ) { + push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ". + "WHERE optionname LIKE 'report_option_%' ". + "AND part_pkg_option.pkgpart = $table.pkgpart)"; + } + my @where_not_num = map { + "NOT EXISTS(SELECT 1 FROM part_pkg_option ". + "WHERE optionname = 'report_option_$_' ". + "AND part_pkg_option.pkgpart = $table.pkgpart)" + } @not_num; + + my @where; + if (@where_num) { + push @where, '( '.join($op, @where_num).' )'; } - $comparison; + if (@where_not_num) { + push @where, '( '.join(' AND ', @where_not_num).' )'; + } + + return @where; + # this messes up totals + #if ( $opt{'use_override'} ) { + # # then also allow the non-override package to match + # delete $opt{'use_override'}; + # $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")"; + #} + } sub with_cust_classnum { @@ -807,7 +837,8 @@ sub init_projection { # sdate/edate overlapping the ROI, for performance "INSERT INTO v_cust_bill_pkg ( SELECT cust_bill_pkg.*, - (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail + (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0) + FROM cust_bill_pkg_detail WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum), cust_bill._date, cust_pkg.expire