1 <& elements/search.html,
5 'count_query' => $count_query,
6 'count_addl' => $count_addl,
7 'header' => [ 'State', # if we add more group fields, change this
11 'fields' => [ 'state',
15 'html_init' => include('.head', $time),
19 <FORM STYLE="display:inline" ACTION=<% $cgi->url %> METHOD="GET">
20 Active phone services as of <& /elements/input-date-field.html, {
23 'format' => FS::Conf->new->config('date_format') || '%m/%d/%Y'
25 <INPUT TYPE="hidden" NAME="order_by" VALUE="<% $cgi->param('order_by') %>">
26 <INPUT TYPE="submit" VALUE="Refresh">
32 # svc_phone-specific for now; may change later
33 my $curuser = $FS::CurrentUser::CurrentUser;
34 die "access denied" unless $curuser->access_right('Services: Phone numbers');
35 my $title = 'Phone services by state';
38 if ( $cgi->param('date') ) {
39 $time = parse_datetime($cgi->param('date'));
40 $title .= time2str(' (%b %o, %Y)', $time);
43 my @tables = qw(svc_phone cust_svc cust_pkg cust_location cust_main);
44 my @pkeys = qw(svcnum svcnum pkgnum locationnum custnum);
45 my @h_tables = map "h_$_", @tables;
50 for(my $i = 0; $i < scalar(@tables); $i++) {
51 my $last_table = $h_tables[$i-1];
52 my $pkey = $pkeys[$i];
53 my $table = $tables[$i];
54 my $h_table = $h_tables[$i];
55 # alias the preceding table, and join to a subquery that finds the most
56 # recent change to $table.$pkey before $time
61 $inside = " AS $alias";
65 (SELECT $pkey AS num, MAX(history_date) AS history_date
67 WHERE history_date <= $time AND
68 history_action IN ('insert', 'replace_new')
70 ) AS mostrecent_$table
71 ON ($alias.$pkey = mostrecent_$table.num AND
72 $alias.history_date = mostrecent_$table.history_date AND
73 $alias.history_action IN ('insert', 'replace_new')
76 (SELECT $pkey AS num, MAX(history_date) AS history_date, 1 AS deleted
78 WHERE history_date <= $time AND
79 history_action = 'delete'
82 ON (mostrecent_$table.num = deleted_$table.num AND
83 mostrecent_$table.history_date < deleted_$table.history_date
86 # join to the preceding table if there is one, and filter out
90 LEFT JOIN ( $h_table $inside ) AS $h_table
91 ON ($h_table.$pkey = $last_table.$pkey)";
92 push @where, "$h_table.deleted IS NULL";
94 $addl_from .= $inside;
95 push @where, "deleted_$table.deleted IS NULL";
99 # so that we know which services are still active
101 LEFT JOIN svc_phone ON (h_svc_phone.svcnum = svc_phone.svcnum AND
102 h_svc_phone.phonenum = svc_phone.phonenum)";
104 #warn "\n\nJOIN EXPRESSION:\n$addl_from\n\n";
106 push @where, $curuser->agentnums_sql(
107 'table' => 'h_cust_main',
108 'null_right' => 'View/link unlinked services'
110 my $where = " WHERE ".join(' AND ', map "($_)", @where);
112 my $group_field = 'h_cust_location.state';
114 "$group_field AS state",
115 'count(DISTINCT h_svc_phone.svcnum) AS num_svcnums',
116 # don't DISTINCT these (it reorders them)
117 "array_to_string(array_agg(h_svc_phone.phonenum), ',') AS all_phonenums",
118 "array_to_string(array_agg(h_svc_phone.svcnum), ',') AS all_svcnums",
119 "array_to_string(array_agg(svc_phone.svcnum), ',') AS active_svcnums",
123 'select' => join(',', @select),
124 'table' => 'h_svc_phone',
125 'addl_from' => $addl_from,
126 'extra_sql' => " $where GROUP BY $group_field",
129 # DISTINCT on these because of cross-producting effects when a cust_pkg
130 # record (usually) was replaced more than once within one second.
132 "SELECT COUNT(DISTINCT $group_field), COUNT(DISTINCT h_svc_phone.svcnum) ".
133 "FROM h_svc_phone $addl_from $where";
134 my $count_addl = [ '%d phone services' ];
136 my $detail_sub = sub {
139 my @svcnums = split(',', $rec->all_svcnums);
140 my @phonenums = split(',', $rec->all_phonenums);
141 # identifies services that still exist with the same svcnum+phonenum
142 my %active = map { $_ => 1 } split(',', $rec->active_svcnums);
143 # make a single column of phonenums
146 while (my $svcnum = shift @svcnums) {
147 my $phonenum = shift @phonenums;
148 next if $seen{$svcnum};
150 my $link = $active{$svcnum} ?
151 $p.'view/svc_phone.cgi?'.$svcnum :
153 push @return, [ { data => $phonenum,
155 data_style => ($active{$svcnum} ? '' : 'i')