X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=17b12ae2340880e15ae661e1f61eeccaaddb6cdd;hp=9d85b9de8dc5cac6f9ed93f99692085065629bdc;hb=e9e0cf0989259b94d9758eceff448666a2e5a5cc;hpb=c8bf9e1f13a96ed4464afed8c81507686ac16058 diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 9d85b9de8..17b12ae23 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -32,6 +32,37 @@ options in %opt. =over 4 +=item signups: The number of customers signed up. Options are "refnum" +(limit by advertising source) and "indirect" (boolean, tells us to limit +to customers that have a referral_custnum that matches the advertising source). + +=cut + +sub signups { + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my @where = ( $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, + 'cust_main.signupdate') + ); + my $join = ''; + if ( $opt{'indirect'} ) { + $join = " JOIN cust_main AS referring_cust_main". + " ON (cust_main.referral_custnum = referring_cust_main.custnum)"; + + if ( $opt{'refnum'} ) { + push @where, "referring_cust_main.refnum = ".$opt{'refnum'}; + } + } + elsif ( $opt{'refnum'} ) { + push @where, "refnum = ".$opt{'refnum'}; + } + + push @where, $self->with_cust_classnum(%opt); + + $self->scalar_sql( + "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where) + ); +} + =item invoiced: The total amount charged on all invoices. =cut @@ -39,12 +70,18 @@ options in %opt. sub invoiced { #invoiced my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $sql = 'SELECT SUM(cust_bill.charged) FROM cust_bill'; + if ( $opt{'setuprecur'} ) { + $sql = 'SELECT SUM('. + FS::cust_bill_pkg->charged_sql($speriod, $eperiod, %opt). + ') FROM cust_bill_pkg JOIN cust_bill USING (invnum)'; + } + $self->scalar_sql(" - SELECT SUM(charged) - FROM cust_bill + $sql LEFT JOIN cust_main USING ( custnum ) - WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) - . (%opt ? $self->for_custnum(%opt) : '') + WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). + $self->for_opts(%opt) ); } @@ -56,8 +93,8 @@ sub invoiced { #invoiced sub netsales { #net sales my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - $self->invoiced($speriod,$eperiod,$agentnum,%opt) - - $self->netcredits($speriod,$eperiod,$agentnum,%opt); + $self->invoiced( $speriod, $eperiod, $agentnum, %opt) + - $self->netcredits($speriod, $eperiod, $agentnum, %opt); } =item cashflow: payments - refunds @@ -76,10 +113,10 @@ sub cashflow { =cut sub netcashflow { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - $self->receipts($speriod, $eperiod, $agentnum) - - $self->netrefunds( $speriod, $eperiod, $agentnum); + $self->receipts( $speriod, $eperiod, $agentnum, %opt) + - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt); } =item payments: The sum of payments received in the period. @@ -92,8 +129,8 @@ sub payments { 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) : '') + WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). + $self->for_opts(%opt) ); } @@ -102,12 +139,13 @@ sub payments { =cut sub credits { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + 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) + WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). + $self->for_opts(%opt) ); } @@ -121,8 +159,8 @@ sub refunds { 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) : '') + WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). + $self->for_opts(%opt) ); } @@ -132,17 +170,24 @@ sub refunds { sub netcredits { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + + my $sql = 'SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill'; + if ( $opt{'setuprecur'} ) { + $sql = 'SELECT SUM('. + FS::cust_bill_pkg->credited_sql($speriod, $eperiod, %opt). + ') FROM cust_bill_pkg'; + } + $self->scalar_sql(" - SELECT SUM(cust_credit_bill.amount) - FROM cust_credit_bill + $sql LEFT JOIN cust_bill USING ( invnum ) LEFT JOIN cust_main USING ( custnum ) WHERE ". $self->in_time_period_and_agent( $speriod, $eperiod, $agentnum, 'cust_bill._date' - ) - . (%opt ? $self->for_custnum(%opt) : '') + ). + $self->for_opts(%opt) ); } @@ -151,17 +196,25 @@ sub netcredits { =cut sub receipts { #net payments - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + + 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). + ') FROM cust_bill_pkg'; + } + $self->scalar_sql(" - SELECT SUM(cust_bill_pay.amount) - FROM cust_bill_pay + $sql LEFT JOIN cust_bill USING ( invnum ) LEFT JOIN cust_main USING ( custnum ) WHERE ". $self->in_time_period_and_agent( $speriod, $eperiod, $agentnum, 'cust_bill._date' - ) + ). + $self->for_opts(%opt) ); } @@ -170,7 +223,7 @@ sub receipts { #net payments =cut sub netrefunds { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" SELECT SUM(cust_credit_refund.amount) FROM cust_credit_refund @@ -180,7 +233,8 @@ sub netrefunds { $eperiod, $agentnum, 'cust_credit._date' - ) + ). + $self->for_opts(%opt) ); } @@ -266,16 +320,15 @@ sub _subtract_11mo { 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"; + + if ( $opt{'classnum'} ne '' ) { + my $classnums = $opt{'classnum'}; + $classnums = [ $classnums ] if !ref($classnums); + @$classnums = grep /^\d+$/, @$classnums; + $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums). + ')'; } + $agentnum ||= $opt{'agentnum'}; my $total_sql = " SELECT SUM(part_pkg.setup_cost) "; @@ -298,16 +351,15 @@ sub cust_pkg_setup_cost { 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"; + + if ( $opt{'classnum'} ne '' ) { + my $classnums = $opt{'classnum'}; + $classnums = [ $classnums ] if !ref($classnums); + @$classnums = grep /^\d+$/, @$classnums; + $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums). + ')'; } + $agentnum ||= $opt{'agentnum'}; # duplication of in_time_period_and_agent # because we do it a little differently here @@ -338,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 @@ -369,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; @@ -382,11 +432,17 @@ 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), $self->in_time_period_and_agent($speriod, $eperiod, $agentnum), ); + # yuck, false laziness + push @where, "cust_main.refnum = ". $opt{'refnum'} if $opt{'refnum'}; + + push @where, $self->with_cust_classnum(%opt); + my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0) FROM cust_bill_pkg $cust_bill_pkg_join @@ -403,27 +459,33 @@ 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), ); + push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'}; + + push @where, $self->with_cust_classnum(%opt); + # subtract all usage from the line item regardless of date my $item_usage; if ( $opt{'project'} ) { $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", - "$cust_bill_pkg.edate > $speriod", + "$cust_bill_pkg.sdate < $eperiod", + "$cust_bill_pkg.edate >= $speriod", ; # the fraction of edate - sdate that's within [speriod, eperiod] $recur_fraction = " * @@ -458,17 +520,25 @@ 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'}; + + push @where, $self->with_cust_classnum(%opt); $agentnum ||= $opt{'agentnum'}; push @where, $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), $self->with_usageclass($opt{'usageclass'}), + $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' ); @@ -479,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 " @@ -493,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); @@ -502,6 +573,7 @@ sub cust_bill_pkg_detail { sub cust_bill_pkg_discount { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + #need to do this the new multi-classnum way if it gets re-enabled #my $where = ''; #my $comparison = ''; #if ( $opt{'classnum'} =~ /^(\d+)$/ ) { @@ -590,32 +662,38 @@ sub in_time_period_and_agent { $sql; } -sub for_custnum { +sub for_opts { my ( $self, %opt ) = @_; - return '' unless $opt{'custnum'}; - $opt{'custnum'} =~ /^\d+$/ ? " and custnum = $opt{custnum} " : ''; + my $sql = ''; + if ( $opt{'custnum'} =~ /^(\d+)$/ ) { + $sql .= " and custnum = $1 "; + } + if ( $opt{'refnum'} =~ /^(\d+)$/ ) { + $sql .= " and refnum = $1 "; + } + if ( my $where = $self->with_cust_classnum(%opt) ) { + $sql .= " and $where"; + } + + $sql; } sub with_classnum { - my $self = shift; - my ($classnum, $use_override) = @_; - return '' unless $classnum =~ /^\d+$/; - my $comparison; - if ( $classnum == 0 ) { - $comparison = 'IS NULL'; - } - else { - $comparison = "= $classnum"; - } + my ($self, $classnum, $use_override) = @_; + return '' if $classnum eq ''; + + $classnum = [ $classnum ] if !ref($classnum); + @$classnum = grep /^\d+$/, @$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 ) { - return "( - part_pkg.classnum $comparison AND pkgpart_override IS NULL OR - override.classnum $comparison AND pkgpart_override IS NOT NULL - )"; - } - else { - return "part_pkg.classnum $comparison"; + $expr .= " + OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )"; } + "( $expr )"; } sub with_usageclass { @@ -632,6 +710,87 @@ sub with_usageclass { return "cust_bill_pkg_detail.classnum $comparison"; } +sub with_report_option { + 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 '; + } + 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).' )'; + } + 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 { + my ($self, %opt) = @_; + if ( $opt{'cust_classnum'} ) { + my $classnums = $opt{'cust_classnum'}; + $classnums = [ $classnums ] if !ref($classnums); + @$classnums = grep /^\d+$/, @$classnums; + return 'cust_main.classnum in('. join(',',@$classnums) .')' + if @$classnums; + } + (); +} + + sub scalar_sql { my( $self, $sql ) = ( shift, shift ); my $sth = dbh->prepare($sql) or die dbh->errstr; @@ -678,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 @@ -705,14 +865,26 @@ sub extend_projection { my ($speriod, $eperiod) = @_; my %items = map {$_ => 1} @{ $self->{items} }; if ($items{'cust_bill_pkg'}) { - # append, head-to-tail, new line items identical to any that end within the - # period (and aren't expiring) + # What we do here: + # Find all line items that end after the start of the period (and have + # recurring fees, and don't expire before they end). Choose the latest + # one for each package. If it ends before the end of the period, copy + # it forward by one billing period. + # Repeat this until the latest line item for each package no longer ends + # within the period. This is certain to happen in finitely many + # iterations as long as freq > 0. + # - Pg only, obviously. + # - Gives bad results if freq_override is used. my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) ); my $insert_fields = join(',', @fields); - #advance (sdate, edate) by one billing period + my $add_freq = sub { # emulate FS::part_pkg::add_freq + my $field = shift; + "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ". + "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field"; + }; foreach (@fields) { if ($_ eq 'edate') { - $_ = '(edate + (edate - sdate)) AS edate' #careful of integer overflow + $_ = $add_freq->('edate'); } elsif ($_ eq 'sdate') { $_ = 'edate AS sdate' @@ -721,20 +893,31 @@ sub extend_projection { $_ = '0 AS setup' #because recurring only } elsif ($_ eq '_date') { - $_ = '(_date + (edate - sdate)) AS _date' + $_ = $add_freq->('_date'); } } my $select_fields = join(',', @fields); my $dbh = dbh; my $sql = + # Subquery here because we need to DISTINCT the whole set, select the + # latest charge per pkgnum, and _then_ check edate < $eperiod + # and edate < expire. "INSERT INTO v_cust_bill_pkg ($insert_fields) - SELECT $select_fields FROM v_cust_bill_pkg - WHERE edate >= $speriod AND edate < $eperiod + SELECT $select_fields FROM ( + SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg + WHERE edate >= $speriod AND recur > 0 - AND (expire IS NULL OR expire > edate)"; - warn "[extend_projection] $sql\n" if $DEBUG; - my $rows = $dbh->do($sql) or die $dbh->errstr; - warn "[extend_projection] $rows rows\n" if $DEBUG; + AND freq IS NOT NULL + AND freq != '0' + ORDER BY pkgnum, edate DESC + ) AS v1 + WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)"; + my $rows; + do { + warn "[extend_projection] $sql\n" if $DEBUG; + $rows = $dbh->do($sql) or die $dbh->errstr; + warn "[extend_projection] $rows rows\n" if $DEBUG; + } until $rows == 0; } }