1 package FS::h_cust_pkg;
8 @ISA = qw( FS::h_Common FS::cust_pkg );
10 sub table { 'h_cust_pkg' };
14 FS::h_cust_pkg - Historical record of customer package changes
20 An FS::h_cust_pkg object represents historical changes to packages.
21 FS::h_cust_pkg inherits from FS::h_Common and FS::cust_pkg.
29 Like L<FS::cust_pkg::search>, but adapted for searching historical records.
30 Takes the additional parameter "date", which is the timestamp to perform
31 the search "as of" (i.e. search the most recent insert or replace_new record
32 for each pkgnum that is not later than that date).
37 my ($class, $params) = @_;
38 my $date = delete $params->{'date'};
39 $date =~ /^\d*$/ or die "invalid search date '$date'\n";
41 my $query = FS::cust_pkg->search($params);
43 # allow multiple status criteria
44 # this might be useful in the base cust_pkg search, but I haven't
46 my $status = delete $params->{'status'};
49 foreach ( split(',', $status) ) {
51 push @status_where, $class->active_sql();
52 } elsif ( /^not[ _]yet[ _]billed$/ ) {
53 push @status_where, $class->not_yet_billed_sql();
54 } elsif ( /^(one-time charge|inactive)$/ ) {
55 push @status_where, $class->inactive_sql();
56 } elsif ( /^suspended$/ ) {
57 push @status_where, $class->suspended_sql();
58 } elsif ( /^cancell?ed$/ ) {
59 push @status_where, $class->cancelled_sql();
62 if ( @status_where ) {
63 $query->{'extra_sql'} .= ' AND ('.join(' OR ', @status_where).')';
64 $query->{'count_query'} .= ' AND ('.join(' OR ', @status_where).')';
68 # make some adjustments
69 $query->{'table'} = 'h_cust_pkg';
70 foreach (qw(select addl_from extra_sql count_query order_by)) {
71 $query->{$_} =~ s/cust_pkg\b/h_cust_pkg/g;
72 $query->{$_} =~ s/cust_main\b/h_cust_main/g;
75 my $and_where = " AND h_cust_pkg.historynum =
76 (SELECT historynum FROM h_cust_pkg AS mostrecent
77 WHERE mostrecent.pkgnum = h_cust_pkg.pkgnum
78 AND mostrecent.history_date <= $date
79 AND mostrecent.history_action IN ('insert', 'replace_new')
80 ORDER BY history_date DESC,historynum DESC LIMIT 1
81 ) AND h_cust_main.historynum =
82 (SELECT historynum FROM h_cust_main AS mostrecent
83 WHERE mostrecent.custnum = h_cust_main.custnum
84 AND mostrecent.history_date <= h_cust_pkg.history_date
85 AND mostrecent.history_action IN ('insert', 'replace_new')
86 ORDER BY history_date DESC,historynum DESC LIMIT 1
89 $query->{'extra_sql'} .= $and_where;
90 $query->{'count_query'} .= $and_where;
95 =item churn_fromwhere_sql STATUS, START, END
97 Returns SQL fragments to do queries related to "package churn". STATUS
98 is one of "active", "setup", "cancel", "susp", or "unsusp". These do NOT
99 correspond directly to package statuses. START and END define a date range.
101 - active: limit to packages that were active on START. END is ignored.
102 - setup: limit to packages that were set up between START and END, except
103 those created by package changes.
104 - cancel: limit to packages that were canceled between START and END, except
105 those changed into other packages.
106 - susp: limit to packages that were suspended between START and END.
107 - unsusp: limit to packages that were unsuspended between START and END.
109 The logic of these may change in the future, especially with respect to
110 package changes. Watch this space.
113 - a fragment usable as a FROM clause (without the keyword FROM), in which
114 the package table is named or aliased to 'cust_pkg'
115 - one or more conditions to include in the WHERE clause
119 sub churn_fromwhere_sql {
120 my ($self, $status, $speriod, $eperiod) = @_;
123 if ( $status eq 'active' ) {
124 # for all packages that were setup before $speriod, find the pkgnum
125 # and the most recent update of the package before $speriod
126 my $setup_before = "SELECT DISTINCT ON (pkgnum) pkgnum, historynum
128 WHERE setup < $speriod
129 AND history_date < $speriod
130 AND history_action IN('insert', 'replace_new')
131 ORDER BY pkgnum ASC, history_date DESC";
132 # for each of these, exclude if the package was suspended or canceled
133 # in the most recent update before $speriod
134 $from = "h_cust_pkg AS cust_pkg
135 JOIN ($setup_before) AS setup_before USING (historynum)";
136 @where = ( 'susp IS NULL', 'cancel IS NULL' );
137 } elsif ( $status eq 'setup' ) {
138 # the simple case, because packages should only get set up once
139 # (but exclude those that were created due to a package change)
140 # XXX or should we include if they were created by a pkgpart change?
145 "change_pkgnum IS NULL"
147 } elsif ( $status eq 'cancel' ) {
148 # also simple, because packages should only be canceled once
149 # (exclude those that were canceled due to a package change)
152 "cust_pkg.cancel >= $speriod",
153 "cust_pkg.cancel < $eperiod",
154 "NOT EXISTS(SELECT 1 FROM cust_pkg AS changed_to_pkg ".
155 "WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum)",
157 } elsif ( $status eq 'susp' ) {
159 # find packages that were changed from susp = null to susp != null
160 my $susp_during = $self->sql_diff($speriod, $eperiod) .
161 ' WHERE old.susp IS NULL AND new.susp IS NOT NULL';
162 $from = "h_cust_pkg AS cust_pkg
163 JOIN ($susp_during) AS susp_during
164 ON (susp_during.new_historynum = cust_pkg.historynum)";
165 @where = ( 'cust_pkg.cancel IS NULL' );
166 } elsif ( $status eq 'unsusp' ) {
168 my $unsusp_during = $self->sql_diff($speriod, $eperiod) .
169 ' WHERE old.susp IS NOT NULL AND new.susp IS NULL';
170 $from = "h_cust_pkg AS cust_pkg
171 JOIN ($unsusp_during) AS unsusp_during
172 ON (unsusp_during.new_historynum = cust_pkg.historynum)";
173 @where = ( 'cust_pkg.cancel IS NULL' );
175 die "'$status' makes no sense";
177 return ($from, @where);
182 churn_fromwhere_sql fails on MySQL.
186 L<FS::cust_pkg>, L<FS::h_Common>, L<FS::Record>, schema.html from the base