An FS::h_cust_pkg object represents historical changes to packages.
FS::h_cust_pkg inherits from FS::h_Common and FS::cust_pkg.
+=head1 CLASS METHODS
+
+=over 4
+
+=item search HASHREF
+
+Like L<FS::cust_pkg::search>, but adapted for searching historical records.
+Takes the additional parameter "date", which is the timestamp to perform
+the search "as of" (i.e. search the most recent insert or replace_new record
+for each pkgnum that is not later than that date).
+
+=cut
+
+sub search {
+ my ($class, $params) = @_;
+ my $date = delete $params->{'date'};
+ $date =~ /^\d*$/ or die "invalid search date '$date'\n";
+
+ my $query = FS::cust_pkg->search($params);
+
+ # allow multiple status criteria
+ # this might be useful in the base cust_pkg search, but I haven't
+ # tested it there yet
+ my $status = delete $params->{'status'};
+ if( $status ) {
+ my @status_where;
+ foreach ( split(',', $status) ) {
+ if ( /^active$/ ) {
+ push @status_where, $class->active_sql();
+ } elsif ( /^not[ _]yet[ _]billed$/ ) {
+ push @status_where, $class->not_yet_billed_sql();
+ } elsif ( /^(one-time charge|inactive)$/ ) {
+ push @status_where, $class->inactive_sql();
+ } elsif ( /^suspended$/ ) {
+ push @status_where, $class->suspended_sql();
+ } elsif ( /^cancell?ed$/ ) {
+ push @status_where, $class->cancelled_sql();
+ }
+ }
+ if ( @status_where ) {
+ $query->{'extra_sql'} .= ' AND ('.join(' OR ', @status_where).')';
+ $query->{'count_query'} .= ' AND ('.join(' OR ', @status_where).')';
+ }
+ }
+
+ # make some adjustments
+ $query->{'table'} = 'h_cust_pkg';
+ foreach (qw(select addl_from extra_sql count_query)) {
+ $query->{$_} =~ s/cust_pkg\b/h_cust_pkg/g;
+ $query->{$_} =~ s/cust_main\b/h_cust_main/g;
+ }
+
+ my $and_where = " AND h_cust_pkg.historynum =
+ (SELECT historynum FROM h_cust_pkg AS mostrecent
+ WHERE mostrecent.pkgnum = h_cust_pkg.pkgnum
+ AND mostrecent.history_date <= $date
+ AND mostrecent.history_action IN ('insert', 'replace_new')
+ ORDER BY history_date DESC,historynum DESC LIMIT 1
+ ) AND h_cust_main.historynum =
+ (SELECT historynum FROM h_cust_main AS mostrecent
+ WHERE mostrecent.custnum = h_cust_main.custnum
+ AND mostrecent.history_date <= h_cust_pkg.history_date
+ AND mostrecent.history_action IN ('insert', 'replace_new')
+ ORDER BY history_date DESC,historynum DESC LIMIT 1
+ )";
+
+ $query->{'extra_sql'} .= $and_where;
+ $query->{'count_query'} .= $and_where;
+
+ $query;
+}
+
+
=head1 BUGS
=head1 SEE ALSO
<& /elements/standardize_locations.html,
'form' => 'EditLocationForm',
'callback' => 'document.EditLocationForm.submit();',
+ 'with_census' => 1,
&>
</TABLE>
document.EditLocationForm.submit();
% }
}
+
+function submit_abort() {
+ nd(1);
+}
</SCRIPT>
<INPUT TYPE="button" NAME="submitButton" VALUE="Submit" onclick="go()">
</FORM>
% if ( $billship ) {
returnobj['same'] = cf.elements['same'].checked;
% }
-% if ( $withfirm ) {
-% # not part of either address, really
- returnobj['company'] = cf.elements['company'].value;
-% }
% if ( $withcensus ) {
% # "entered" censustract always goes with the ship_ address if there is one
- returnobj['ship_censustract'] = cf.elements['enter_censustract'].value;
+% if ( $billship ) {
+ returnobj['ship_censustract'] = cf.elements['enter_censustract'].value;
+% } else { # there's only a package address, so it's just "censustract"
+ returnobj['censustract'] = cf.elements['enter_censustract'].value;
+% }
% }
% for my $pre (@prefixes) {
if ( <% $pre eq 'ship_' ? 1 : 0 %> && returnobj['same'] ) {
% # censustract so that we don't ask the user to confirm it again.
if ( !changed && <% $withcensus %> ) {
+% if ( $billship ) {
if ( address_info['same'] ) {
cf.elements['bill_censustract'].value =
address_info['bill_censustract'];
cf.elements['ship_censustract'].value =
address_info['ship_censustract'];
}
+% } else {
+ cf.elements['censustract'].value =
+ address_info['censustract'];
+% }
}
% if ( $conf->config('address_standardize_method') ) {
%# not much to do in this case, just confirm the censustract
% if ( $withcensus ) {
var cf = document.<% $formname %>;
+% if ( $billship ) {
if ( cf.elements['same'] && cf.elements['same'].checked ) {
cf.elements['bill_censustract'].value =
cf.elements['enter_censustract'].value;
cf.elements['ship_censustract'].value =
cf.elements['enter_censustract'].value;
}
+% } else {
+ cf.elements['censustract'].value = cf.elements['enter_censustract'].value;
+% }
% }
post_standardization();
}
my %opt = @_;
my $conf = new FS::Conf;
-my $withfirm = $opt{'with_firm'} ? 1 : 0;
my $withcensus = $opt{'with_census'} ? 1 : 0;
my @prefixes = '';
my $billship = $opt{'billship'} ? 1 : 0; # whether to have bill_ and ship_ prefixes
my $taxpre = '';
+# probably should just geocode both addresses, since either one could
+# be a package address in the future
if ($billship) {
@prefixes = qw(bill_ ship_);
$taxpre = $conf->exists('tax-ship_address') ? 'ship_' : 'bill_';
'no_bold' => $opt{'no_bold'},
'alt_format' => $opt{'alt_format'},
'enable_coords'=> 1,
+ 'enable_censustract' => 1,
)
%>
<SCRIPT TYPE="text/javascript">
var custnum = document.getElementById('custnum'+rownum).value;
// if there is a custnum and more than one open invoice, enable
// (and check) the box
- var show_applications = (custnum > 0 && num_open_invoices[rownum] > 1);
+ var show_applications = !(custnum > 0 && num_open_invoices[rownum] > 1);
var enable_app_checkbox = document.getElementById('enable_app'+rownum);
enable_app_checkbox.disabled = show_applications;
% unless ( $cgi->param('lock_locationnum') ) {
<& /elements/standardize_locations.html,
- 'form' => "OrderPkgForm",
- 'callback' => 'document.OrderPkgForm.submit();',
+ 'form' => "OrderPkgForm",
+ 'callback' => 'document.OrderPkgForm.submit();',
+ 'with_census' => 1,
&>
% }
-<% include('/elements/header.html', $title) %>
-<% include('/elements/table-grid.html') %>
- <TR>
-% foreach (@head) {
- <TH CLASS="grid" BGCOLOR="#cccccc"><% $_ %></TH>
-% }
- </TR>
-% my $r=0;
-% foreach my $row (@rows) {
- <TR>
-% foreach (@$row) {
- <TD CLASS="grid" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><% $_ %></TD>
-% }
- </TR>
-% $r++;
-% }
- <TR>
-% foreach (@totals) {
- <TD CLASS="grid" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><B><% $_ %></B></TD>
-% }
- </TR>
-</TABLE>
+<& elements/search.html,
+ 'title' => $title,
+ 'name' => 'package types',
+ 'query' => $query,
+ 'count_query' => $count_query,
+ 'header' => \@head,
+ 'fields' => \@fields,
+ 'links' => \@links,
+ 'align' => 'clrrrrr',
+ 'footer_data' => $totals,
+&>
<%init>
my $curuser = $FS::CurrentUser::CurrentUser;
$cgi->param('beginning').' - '.$cgi->param('ending').')';
}
-my @h_sql = FS::h_cust_pkg->sql_h_search($end);
-
-my ($end_sql, $addl_from) = @h_sql[1,3];
-$end_sql =~ s/ORDER BY.*//; # breaks aggregate queries
-
-my $begin_sql = $end_sql;
-$begin_sql =~ s/$end/$begin/g;
-
-my $active_sql = FS::cust_pkg->active_sql;
-my $suspended_sql = FS::cust_pkg->suspended_sql;
-my @conds = (
- # SQL WHERE clauses for each column of the table.
- " $begin_sql AND ($active_sql OR $suspended_sql)",
- '',
- " $end_sql AND ($active_sql OR $suspended_sql)",
- " $end_sql AND $active_sql",
- " $end_sql AND $suspended_sql",
- );
-
-$_ =~ s/\bcust_pkg/maintable/g foreach @conds;
-
-my @head = ('Package', 'Before Period', 'Sales', 'Total', 'Active', 'Suspended');
-my @rows = ();
-my @totals = ('Total', 0, 0, 0, 0, 0);
-
-if( !$begin ) {
- splice @conds, 1, 1;
- splice @head, 1, 1;
-}
-
my $agentnums_sql = $curuser->agentnums_sql(
'null' => 1,
- 'table' => 'part_pkg',
+ 'table' => 'main',
);
-my $extra_sql = " WHERE $agentnums_sql";
+my $extra_sql = " freq != '0' AND $agentnums_sql";
#tiny bit of false laziness w/cust_pkg.pm::search
if ( grep { $_ eq 'classnum' } $cgi->param ) {
if ( $cgi->param('classnum') eq '' ) {
- $extra_sql .= ' AND part_pkg.classnum IS NULL';
+ $extra_sql .= ' AND main.classnum IS NULL';
} elsif ( $cgi->param('classnum') =~ /^(\d+)$/ && $1 ne '0' ) {
- $extra_sql .= " AND part_pkg.classnum = $1 ";
+ $extra_sql .= " AND main.classnum = $1 ";
}
}
-foreach my $part_pkg (qsearch({ 'table' => 'part_pkg',
- 'hashref' => {},
- 'extra_sql' => $extra_sql,
- })
- )
-{
- my @row = ();
- next if !$part_pkg->freq; # exclude one-time packages
- push @row, $part_pkg->pkg;
- my $i=1;
- foreach my $cond (@conds) {
- if($cond) {
- my $result = qsearchs({
- 'table' => 'h_cust_pkg',
- 'addl_from' => $addl_from.
- ' LEFT JOIN cust_main USING ( custnum )',
-
- 'hashref' => {},
- 'select' => 'count(*)',
- 'extra_sql' => 'WHERE pkgpart = '.$part_pkg->pkgpart.$cond.
- ' AND '. $curuser->agentnums_sql(
- 'table' => 'cust_main',
- ),
- });
- $row[$i] = $result->getfield('count');
- $totals[$i] += $row[$i];
- }
- $i++;
- }
- $row[2] = $row[3]-$row[1];
- $totals[2] += $row[2];
- push @rows, \@row;
+my $active_sql = 'setup IS NOT NULL AND susp IS NULL AND cancel IS NULL';
+my $suspended_sql = 'setup IS NOT NULL AND susp IS NOT NULL AND cancel IS NULL';
+my $active_or_suspended_sql = 'setup IS NOT NULL AND cancel IS NULL';
+my %conds;
+
+$conds{'before'} = { 'date' => $begin, 'status' => 'active,suspended' };
+$conds{'after'} = { 'date' => $end, 'status' => 'active,suspended' };
+$conds{'active'} = { 'date' => $end, 'status' => 'active' };
+$conds{'suspended'} = { 'date' => $end, 'status' => 'suspended' };
+
+my @select;
+my $totals = FS::part_pkg->new({pkg => 'Total'});
+foreach my $column (keys %conds) {
+ my $h_search = FS::h_cust_pkg->search($conds{$column});
+ my $count_query = $h_search->{count_query};
+
+ # push a select expression for the total packages with pkgpart=main.pkgpart
+ push @select, "($count_query AND h_cust_pkg.pkgpart = main.pkgpart) AS $column";
+
+ # and query the total packages with pkgpart=any of the main.pkgparts
+ my $total = FS::Record->scalar_sql($count_query .
+ " AND h_cust_pkg.pkgpart IN(SELECT pkgpart FROM part_pkg AS main WHERE $extra_sql)"
+ );
+ $totals->set($column => $total);
+}
+
+my $query = {
+ 'table' => 'part_pkg',
+ 'addl_from' => 'AS main',
+ 'select' => join(', ', 'main.*', @select),
+ 'extra_sql' => "WHERE $extra_sql",
+};
+
+my $count_query = "SELECT COUNT(*) FROM part_pkg AS main WHERE $extra_sql";
+
+my $baselink = "h_cust_pkg.html?";
+if ( $cgi->param('classnum') =~ /^\d*$/ ) {
+ $baselink .= "classnum=".$cgi->param('classnum').';';
+}
+my @links = ( #arguments to h_cust_pkg.html, except for pkgpart
+ '',
+ '',
+ [ $baselink . "status=active,suspended;date=$begin;pkgpart=", 'pkgpart' ],
+ '',
+ [ $baselink . "status=active,suspended;date=$end;pkgpart=", 'pkgpart' ],
+ [ $baselink . "status=active;date=$end;pkgpart=", 'pkgpart' ],
+ [ $baselink . "status=suspended;date=$end;pkgpart=", 'pkgpart' ],
+);
+
+my @head = ('#',
+ 'Package',
+ 'Before Period',
+ 'Sales',
+ 'Total',
+ 'Active',
+ 'Suspended');
+
+my @fields = (
+ 'pkgpart',
+ 'pkg',
+ 'before',
+ sub { $_[0]->after - $_[0]->before },
+ 'after',
+ 'active',
+ 'suspended',
+ );
+
+if ( !$begin ) {
+ # remove the irrelevant 'before' column
+ splice(@$_,2,1) foreach \@head, \@fields, \@links;
}
+
</%init>
my $extra_sql = ' WHERE '. join(' AND ', @extra_sql );
$sql_query = {
- #'select' => 'svcnum',
- 'select' => 'cust_svc.*',
+ 'select' => 'cust_svc.*, part_svc.*',
'table' => 'cust_svc',
'addl_from' => $addl_from,
'hashref' => {},
}
+# at this point the query must provide all fields from
+# cust_svc and part_svc, and must include join_cust_main.
$sql_query->{'select'} = join(', ',
$sql_query->{'select'},
- #'part_svc.*',
'cust_main.custnum',
FS::UI::Web::cust_sql_fields(),
);
% $bgcolor = $bgcolor1;
% }
+% my $rowstyle = '';
+% if ( $row eq $opt{'footer_data'} ) {
+% $rowstyle = ' STYLE="border-top: dashed 1px black; font-style: italic"';
+% $bgcolor = '#dddddd';
+% }
+
% my $trid = '';
% if ( $opt{'link_field' } ) {
% my $link_field = $opt{'link_field'};
% $trid = $row->$link_field();
% }
% }
- <TR ID="<%$trid |h%>">
+ <TR ID="<%$trid |h%>"<%$rowstyle%>>
% if ( $opt{'fields'} ) {
my $workbook = $format->{class}->new($XLS)
or die "Error opening Excel file: $!";
-my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31));
+my $title = $opt{'title'};
+$title =~ s/[\[\]\:\*\?\/\/]//g;
+$title = substr($title, 0, 31);
+my $worksheet = $workbook->add_worksheet($title);
$worksheet->protect();
$header ||= $sth->{NAME};
}
+push @$rows, $opt{'footer_data'} if $opt{'footer_data'};
+
</%init>
--- /dev/null
+<& elements/search.html,
+ 'html_init' => $html_init,
+ 'title' => $title,
+ 'name' => 'packages',
+ 'query' => $query,
+ 'count_query' => $count_query,
+ 'header' => [ emt('#'),
+ emt('Quan.'),
+ emt('Package'),
+ emt('Class'),
+ emt('Status'),
+ emt('Ordered by'),
+ emt('Setup'),
+ emt('Base Recur'),
+ emt('Freq.'),
+ emt('Setup'),
+ emt('Last bill'),
+ emt('Next bill'),
+ emt('Adjourn'),
+ emt('Susp.'),
+ emt('Susp. delay'),
+ emt('Expire'),
+ emt('Contract end'),
+ emt('Changed'),
+ emt('Cancel'),
+ emt('Reason'),
+ FS::UI::Web::cust_header(
+ $cgi->param('cust_fields')
+ ),
+ emt('As of'),
+ ],
+ 'fields' => [
+ 'pkgnum',
+ 'quantity',
+ 'pkg',
+ 'classname',
+ sub { ucfirst(shift->status); },
+ 'otaker',
+ sub { sprintf( $money_char.'%.2f',
+ shift->part_pkg->option('setup_fee'),
+ );
+ },
+ sub { my $c = shift;
+ sprintf( $money_char.'%.2f',
+ $c->part_pkg->base_recur($c)
+ );
+ },
+ sub { FS::part_pkg::freq_pretty(shift); },
+
+ ( map { time_or_blank($_) }
+ qw( setup last_bill bill adjourn susp dundate expire contract_end change_date cancel ) ),
+
+ sub { my $self = shift;
+ my $return = '';
+ foreach my $action ( qw ( cancel susp ) ) {
+ my $reason = $self->last_reason($action);
+ $return = $reason->reason if $reason;
+ last if $return;
+ }
+ $return;
+ },
+
+ \&FS::UI::Web::cust_fields,
+ # in cust_pkg.cgi, service labels would go here
+ time_or_blank('history_date'),
+ ],
+ 'color' => [
+ '',
+ '',
+ '',
+ '',
+ sub { shift->statuscolor; },
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ FS::UI::Web::cust_colors(),
+ '',
+ ],
+ 'style' => [ '', '', '', '', 'b', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
+ FS::UI::Web::cust_styles() ],
+ 'size' => [ '', '', '', '', '-1' ],
+ 'align' => 'rrlcccrrlrrrrrrrrrrl'. FS::UI::Web::cust_aligns(). 'r',
+ 'links' => [
+ $link,
+ $link,
+ $link,
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '',
+ '', # link to changed-from package?
+ '',
+ '',
+ '',
+ ( map { $_ ne 'Cust. Status' ? $clink : '' }
+ FS::UI::Web::cust_header(
+ $cgi->param('cust_fields')
+ )
+ ),
+ '',
+ ],
+&>
+<%init>
+
+# shamelessly cloned from cust_pkg.cgi, with minimal changes to make it work
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+ unless $curuser->access_right('List packages');
+
+my $conf = new FS::Conf;
+my $money_char = $conf->config('money_char') || '$';
+
+my %search_hash = ();
+
+#some false laziness w/misc/bulk_change_pkg.cgi
+
+$search_hash{'query'} = $cgi->keywords;
+
+#scalars
+for (qw( agentnum custnum magic status custom cust_fields pkgbatch )) {
+ $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
+}
+
+#arrays
+for my $param (qw( pkgpart classnum )) {
+ $search_hash{$param} = [ $cgi->param($param) ]
+ if grep { $_ eq $param } $cgi->param;
+}
+
+#scalars that need to be passed if empty
+for my $param (qw( censustract censustract2 )) {
+ $search_hash{$param} = $cgi->param($param) || ''
+ if grep { $_ eq $param } $cgi->param;
+}
+
+my $report_option = $cgi->param('report_option');
+$search_hash{report_option} = $report_option if $report_option;
+
+for my $param (grep /^report_option_any/, $cgi->param) {
+ $search_hash{$param} = $cgi->param($param);
+}
+
+###
+# parse dates
+###
+
+#false laziness w/report_cust_pkg.html
+my %disable = (
+ 'all' => {},
+ 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, 'contract_end'=>1, 'dundate'=>1, },
+ 'active' => { 'susp'=>1, 'cancel'=>1 },
+ 'suspended' => { 'cancel' =>1, 'dundate'=>1, },
+ 'cancelled' => {},
+ '' => {},
+);
+
+foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel active )) {
+
+ my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field);
+
+ next if $beginning == 0 && $ending == 4294967295
+ or $disable{$cgi->param('status')}->{$field};
+
+ $search_hash{$field} = [ $beginning, $ending ];
+
+}
+
+my $date;
+if ( $cgi->param('date') =~ /^(\d+)$/ ) {
+ $date = $1;
+ $search_hash{'date'} = $date;
+}
+
+my $query = FS::h_cust_pkg->search(\%search_hash);
+my $count_query = delete($query->{'count_query'});
+
+my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/
+ ? ''
+ : ';show=packages';
+
+my $link = sub {
+ my $self = shift;
+ my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment
+ [ "${p}view/cust_main.cgi?custnum=".$self->custnum.
+ "$show;fragment=$frag#cust_pkg",
+ 'pkgnum'
+ ];
+};
+
+my $clink = sub {
+ my $cust_pkg = shift;
+ $cust_pkg->cust_main_custnum
+ ? [ "${p}view/cust_main.cgi?", 'custnum' ]
+ : '';
+};
+
+sub time_or_blank {
+ my $column = shift;
+ return sub {
+ my $record = shift;
+ my $value = $record->get($column); #mmm closures
+ $value ? time2str('%b %d %Y', $value ) : '';
+ };
+}
+
+my $html_init = '';
+
+my $title = 'Historical Package View - ';
+if ( $date == 0 ) {
+ $title .= 'start';
+} elsif ( $date == 4294967295 ) {
+ $title .= 'present';
+} else {
+ $title .= time2str('%h %o %Y', $date);
+}
+</%init>
'label' => emt('Change location'),
'actionlabel' => emt('Change'),
'cust_pkg' => $cust_pkg,
+ 'width' => 763,
+ 'height' => 380,
);
}
'action' => $p. "edit/cust_location.cgi?locationnum=$locationnum",
'label' => emt('Edit location'),
'actionlabel' => emt('Edit'),
- );
+ 'width' => 700,
+ 'height' => 355,
+ );
}
</%init>
'label' => emt('Change package'),
'actionlabel' => emt('Change'),
'cust_pkg' => $cust_pkg,
+ 'width' => 763,
+ 'height' => 380,
);
}
'label' => emt('Change location'),
'actionlabel' => emt('Change'),
'cust_pkg' => $cust_pkg,
+ 'width' => 763,
+ 'height' => 380,
);
}
projects / freeside.git / commitdiff