X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=5fb56404d9cd414d7fad879aa4f3ba6ea237ce5e;hb=389b6f1116c3309c2ee57a6c295ed1a793503095;hp=98f66e9041dd12f3d728728d6c5facecb737fa90;hpb=db21275347bb469868e81d4ca2bbed846d95f143;p=freeside.git diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 98f66e904..5fb56404d 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -229,7 +229,8 @@ sub receipts { #net payments my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay'; if ( $opt{'setuprecur'} ) { $sql = 'SELECT SUM('. - FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt). + #in practice, but not appearance, paid_sql accepts end before start + FS::cust_bill_pkg->paid_sql($eperiod, $speriod, %opt). ') FROM cust_bill_pkg'; } @@ -266,6 +267,81 @@ sub netrefunds { ); } +=item discounted: The sum of discounts on invoices in the period. + +=cut + +sub discounted { + my( $self, $speriod, $eperiod, $agentnum, %opt) = @_; + + my $sql = 'SELECT SUM('; + if ($opt{'setuprecur'}) { + # (This isn't exact but it works in most cases.) + # When splitting into setup/recur values, + # if the discount is allowed to apply to setup fees (discount.setup = 'Y') + # then split it between the "setup" and "recurring" rows in proportion to + # the "unitsetup" and "unitrecur" fields of the line item. + $sql .= < 0) + OR (COALESCE(cust_bill_pkg.unitrecur,0) > 0)) + THEN +EOF + if ($opt{'setuprecur'} eq 'setup') { + $sql .= ' (COALESCE(cust_bill_pkg.unitsetup,0)'; + } elsif ($opt{'setuprecur'} eq 'recur') { + $sql .= ' (COALESCE(cust_bill_pkg.unitrecur,0)'; + } else { + die 'Unrecognized value for setuprecur'; + } + $sql .= ' / (COALESCE(cust_bill_pkg.unitsetup,0) + COALESCE(cust_bill_pkg.unitrecur,0)))'; + $sql .= " * cust_bill_pkg_discount.amount\n"; + # Otherwise, show it all as "recurring" + if ($opt{'setuprecur'} eq 'setup') { + $sql .= " ELSE 0\n"; + } elsif ($opt{'setuprecur'} eq 'recur') { + $sql .= " ELSE cust_bill_pkg_discount.amount\n"; + } + $sql .= "END\n"; + } else { + # simple case, no setuprecur + $sql .= "cust_bill_pkg_discount.amount\n"; + } + $sql .= <scalar_sql( + $sql + . 'WHERE ' + . $self->in_time_period_and_agent( $speriod, + $eperiod, + $agentnum, + 'cust_bill._date' + ) + . $self->for_opts(%opt) + ); +} + +=item gross: invoiced + discounted + +=cut + +sub gross { + my( $self, $speriod, $eperiod, $agentnum, %opt) = @_; + $self->invoiced( $speriod, $eperiod, $agentnum, %opt) + + $self->discounted( $speriod, $eperiod, $agentnum, %opt); +} + #XXX docs #these should be auto-generated or $AUTOLOADed or something @@ -409,9 +485,9 @@ sub cust_pkg_recur_cost { =item cust_bill_pkg: the total package charges on invoice line items. -'charges': limit the type of charges included (setup, recur, usage). -Should be a string containing one or more of 'S', 'R', or 'U'; if -unspecified, defaults to all three. +'charges': limit the type of charges included (setup, recur, usage, discount, taxes). +Should be a string containing one or more of 'S', 'R', or 'U'; or 'D' or 'T' (discount +and taxes should not be combined with the others.) If unspecified, defaults to 'SRU'. 'classnum': limit to this package class. @@ -440,6 +516,8 @@ sub cust_bill_pkg { $sum += $self->cust_bill_pkg_setup(@_) if $charges{S}; $sum += $self->cust_bill_pkg_recur(@_) if $charges{R}; $sum += $self->cust_bill_pkg_detail(@_) if $charges{U}; + $sum += $self->cust_bill_pkg_discount(@_) if $charges{D}; + $sum += $self->cust_bill_pkg_taxes(@_) if $charges{T}; if ($opt{'average_per_cust_pkg'}) { my $count = $self->cust_bill_pkg_count_pkgnum(@_); @@ -495,12 +573,17 @@ sub _cust_bill_pkg_recurring { my @where = ( '(pkgnum != 0 OR feepart IS NOT NULL)', - $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), $self->with_report_option(%opt), $self->with_refnum(%opt), $self->with_cust_classnum(%opt) ); + my $where_classnum = $self->with_classnum($opt{'classnum'}, $opt{'use_override'}); + if ($opt{'project'}) { + $where_classnum =~ s/\bcust_bill_pkg/v_cust_bill_pkg/g; + } + push @where, $where_classnum; + if ( $opt{'distribute'} ) { $where[0] = 'pkgnum != 0'; # specifically exclude fees push @where, "cust_main.agentnum = $agentnum" if $agentnum; @@ -516,6 +599,10 @@ sub _cust_bill_pkg_recurring { $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date); } + if ( $opt{'custnum'} =~ /^(\d+)$/ ) { + push @where, "(cust_main.custnum = $1)"; + } + return " FROM $cust_bill_pkg $cust_bill_pkg_join @@ -523,6 +610,16 @@ sub _cust_bill_pkg_recurring { } +=item cust_bill_pkg_recur: the total recur charges + +Most arguments as for C, plus: + +'custnum': limit to this customer + +'cost': if true, return total recur costs instead + +=cut + sub cust_bill_pkg_recur { my $self = shift; my ($speriod, $eperiod, $agentnum, %opt) = @_; @@ -549,9 +646,11 @@ sub cust_bill_pkg_recur { ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)"; } - my $total_sql = - "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" . - $self->_cust_bill_pkg_recurring(@_); + my $total_sql = $opt{'cost'} + ? "SELECT SUM(part_pkg.recur_cost)" + : "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)"; + + $total_sql .= $self->_cust_bill_pkg_recurring(@_); $self->scalar_sql($total_sql); } @@ -567,10 +666,14 @@ sub cust_bill_pkg_count_pkgnum { =item cust_bill_pkg_detail: the total usage charges in detail lines. -Arguments as for C, plus: +Most arguments as for C, plus: 'usageclass': limit to this usage class number. +'custnum': limit to this customer + +'cost': if true, return total usage costs instead + =cut sub cust_bill_pkg_detail { @@ -603,7 +706,16 @@ sub cust_bill_pkg_detail { ); } + if ( $opt{'custnum'} =~ /^(\d+)$/ ) { + push @where, "(cust_main.custnum = $1)"; + } + my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) "; + my $extra_join = ''; + if ($opt{'cost'}) { + $extra_join = " JOIN cdr USING ( detailnum ) "; + $total_sql = " SELECT SUM(cdr.rated_cost) "; + } $total_sql .= " FROM cust_bill_pkg_detail @@ -613,88 +725,95 @@ 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 ); + LEFT JOIN part_fee USING ( feepart ) + ".$extra_join. + " WHERE ".join( ' AND ', grep $_, @where ); $self->scalar_sql($total_sql); } sub cust_bill_pkg_discount { - my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; + # apply all the same constraints here as for setup/recur - #need to do this the new multi-classnum way if it gets re-enabled - #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 @where = ( + '(pkgnum != 0 OR feepart IS NOT NULL)', + $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), + $self->with_report_option(%opt), + $self->in_time_period_and_agent($speriod, $eperiod, $agentnum), + $self->with_refnum(%opt), + $self->with_cust_classnum(%opt) + ); + + my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0) + FROM cust_bill_pkg_discount + JOIN cust_bill_pkg USING (billpkgnum) + $cust_bill_pkg_join + WHERE " . join(' AND ', grep $_, @where); + + $self->scalar_sql($total_sql); +} + +sub cust_bill_pkg_taxes { + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; $agentnum ||= $opt{'agentnum'}; - my $total_sql = - " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) "; + my @where = ( + '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)', + $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), + $self->with_report_option(%opt), + $self->in_time_period_and_agent($speriod, $eperiod, $agentnum), + $self->with_refnum(%opt), + $self->with_cust_classnum(%opt) + ); - $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); + my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) + FROM cust_bill_pkg + $cust_bill_pkg_join + LEFT JOIN cust_bill_pkg_tax_location + ON (cust_bill_pkg.billpkgnum = cust_bill_pkg_tax_location.taxable_billpkgnum) + WHERE " . join(' AND ', grep $_, @where); + $self->scalar_sql($total_sql); } -sub pkg_field_where { - my( $self, $field, $speriod, $eperiod, $agentnum, %opt ) = @_; - # someday this will use an aggregate query and return all the columns - # at once - # and I will drive a Tesla and have a live-in sushi chef who is also a - # ninja bodyguard +#all credits applied to matching pkg line items (ie not taxes) + +sub cust_bill_pkg_credits { + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; + + $agentnum ||= $opt{'agentnum'}; + my @where = ( - $self->in_time_period_and_agent($speriod, - $eperiod, - $agentnum, - "cust_pkg.$field", - ), + '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)', + $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), + $self->with_report_option(%opt), + $self->in_time_period_and_agent($speriod, $eperiod, $agentnum), $self->with_refnum(%opt), - $self->with_towernum(%opt), - $self->with_zip(%opt), - # can't use with_classnum here... + $self->with_cust_classnum(%opt) ); - if ($opt{classnum}) { - my $classnum = $opt{classnum}; - $classnum = [ $classnum ] if !ref($classnum); - @$classnum = grep /^\d+$/, @$classnum; - my $in = 'IN ('. join(',', @$classnum). ')'; - push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum; - } - ' WHERE ' . join(' AND ', grep $_, @where); + my $total_sql = "SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0) + FROM cust_bill_pkg + $cust_bill_pkg_join + LEFT JOIN cust_credit_bill_pkg + USING ( billpkgnum ) + WHERE " . join(' AND ', grep $_, @where); + + $self->scalar_sql($total_sql); } -=item setup_pkg: The number of packages with setup dates in the period. +##### package churn report ##### -This excludes packages created by package changes. Options: +=item active_pkg: The number of packages that were active at the start of +the period. The end date of the period is ignored. Options: - refnum: Limit to customers with this advertising source. - classnum: Limit to packages with this class. @@ -704,61 +823,170 @@ This excludes packages created by package changes. Options: Except for zip, any of these can be an arrayref to allow multiple values for the field. -=item susp_pkg: The number of suspended packages that were last suspended -in the period. Options are as for setup_pkg. +=item setup_pkg: The number of packages with setup dates in the period. This +excludes packages created by package changes. Options are as for active_pkg. + +=item susp_pkg: The number of packages that were suspended in the period +(and not canceled). Options are as for active_pkg. + +=item unsusp_pkg: The number of packages that were unsuspended in the period. +Options are as for active_pkg. =item cancel_pkg: The number of packages with cancel dates in the period. Excludes packages that were canceled to be changed to a new package. Options -are as for setup_pkg. +are as for active_pkg. =cut +sub active_pkg { + my $self = shift; + $self->churn_pkg('active', @_); +} + sub setup_pkg { my $self = shift; - my $sql = 'SELECT COUNT(*) FROM cust_pkg - LEFT JOIN part_pkg USING (pkgpart) - LEFT JOIN cust_main USING (custnum)'. - $self->pkg_field_where('setup', @_) . - ' AND change_pkgnum IS NULL'; + $self->churn_pkg('setup', @_); +} - $self->scalar_sql($sql); +sub cancel_pkg { + my $self = shift; + $self->churn_pkg('cancel', @_); } sub susp_pkg { - # number of currently suspended packages that were suspended in the period my $self = shift; - my $sql = 'SELECT COUNT(*) FROM cust_pkg - LEFT JOIN part_pkg USING (pkgpart) - LEFT JOIN cust_main USING (custnum) '. - $self->pkg_field_where('susp', @_); + $self->churn_pkg('susp', @_); +} - $self->scalar_sql($sql); +sub unsusp_pkg { + my $self = shift; + $self->churn_pkg('unsusp', @_); } -sub cancel_pkg { - # number of packages canceled in the period and not changed to another - # package +sub churn_pkg { my $self = shift; - my $sql = 'SELECT COUNT(*) FROM cust_pkg - LEFT JOIN part_pkg USING (pkgpart) - LEFT JOIN cust_main USING (custnum) - LEFT JOIN cust_pkg changed_to_pkg ON( - cust_pkg.pkgnum = changed_to_pkg.change_pkgnum - ) '. - $self->pkg_field_where('cancel', @_) . - ' AND changed_to_pkg.pkgnum IS NULL'; + my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_; + my ($from, @where) = + FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod); + + push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum); + + my $sql = "SELECT COUNT(*) FROM $from + JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart) + JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)"; + $sql .= ' WHERE '.join(' AND ', @where) + if scalar(@where); $self->scalar_sql($sql); } -#this is going to be harder.. -#sub unsusp_pkg { -# my( $self, $speriod, $eperiod, $agentnum ) = @_; -# $self->scalar_sql(" -# SELECT COUNT(*) FROM h_cust_pkg -# WHERE -# -#} +sub pkg_where { + my $self = shift; + my %opt = @_; + my @where = ( + "part_pkg.freq != '0'", + $self->with_refnum(%opt), + $self->with_towernum(%opt), + $self->with_zip(%opt), + ); + if ($opt{agentnum} =~ /^(\d+)$/) { + push @where, "cust_main.agentnum = $1"; + } + if ($opt{classnum}) { + my $classnum = $opt{classnum}; + $classnum = [ $classnum ] if !ref($classnum); + @$classnum = grep /^\d+$/, @$classnum; + my $in = 'IN ('. join(',', @$classnum). ')'; + push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum; + } + @where; +} + +##### end of package churn report stuff ##### + +##### customer churn report ##### + +=item active_cust: The number of customers who had any active recurring +packages at the start of the period. The end date is ignored, agentnum is +mandatory, and no other parameters are accepted. + +=item started_cust: The number of customers who had no active packages at +the start of the period, but had active packages at the end. Like +active_cust, agentnum is mandatory and no other parameters are accepted. + +=item suspended_cust: The number of customers who had active packages at +the start of the period, and at the end had no active packages but some +suspended packages. Note that this does not necessarily mean that their +packages were suspended during the period. + +=item resumed_cust: The inverse of suspended_cust: the number of customers +who had suspended packages and no active packages at the start of the +period, and active packages at the end. + +=item cancelled_cust: The number of customers who had active packages +at the start of the period, and only cancelled packages at the end. + +=cut + +sub active_cust { + my $self = shift; + $self->churn_cust(@_)->{active}; +} +sub started_cust { + my $self = shift; + $self->churn_cust(@_)->{started}; +} +sub suspended_cust { + my $self = shift; + $self->churn_cust(@_)->{suspended}; +} +sub resumed_cust { + my $self = shift; + $self->churn_cust(@_)->{resumed}; +} +sub cancelled_cust { + my $self = shift; + $self->churn_cust(@_)->{cancelled}; +} + +sub churn_cust { + my $self = shift; + my ( $speriod ) = @_; + + # run one query for each interval + return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_); +} + +sub calculate_churn_cust { + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; + + my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod); + my $where = ''; + $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum; + my $cust_sql = + "SELECT churn.* ". + "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)". + $where; + + # query to count the ones with certain status combinations + my $total_sql = " + SELECT SUM((s_active > 0)::int) as active, + SUM((s_active = 0 and e_active > 0)::int) as started, + SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int) + as suspended, + SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int) + as resumed, + SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int) + as cancelled + FROM ($cust_sql) AS x + "; + + my $sth = dbh->prepare($total_sql); + $sth->execute or die "failed to execute churn query: " . $sth->errstr; + + $self->{_interval}{$speriod} = $sth->fetchrow_hashref; +} sub in_time_period_and_agent { my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4); @@ -802,16 +1030,27 @@ sub with_classnum { $classnum = [ $classnum ] if !ref($classnum); @$classnum = grep /^\d+$/, @$classnum; + return '' if !@$classnum; my $in = 'IN ('. join(',', @$classnum). ')'; - my $expr = " - ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL) - 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 )"; + # then include packages if their base package is in the set and they are + # not overridden, + # or if they are overridden and their override package is in the set, + # or fees if they are in the set + return "( + ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL ) + OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL ) + OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL ) + )"; + } else { + # include packages if their base package is in the set, + # or fees if they are in the set + return "( + ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL ) + OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL ) + )"; } - "( $expr )"; } sub with_usageclass {