1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
|
<& elements/search.html,
'title' => $title,
'name' => 'states',
'query' => $query,
'count_query' => $count_query,
'count_addl' => $count_addl,
'header' => [ 'State', # if we add more group fields, change this
'Count',
'Phone numbers'
],
'fields' => [ 'state',
'num_svcnums',
$detail_sub
],
'html_init' => include('.head', $time),
&>
<%def .head>
% my $time = shift;
<FORM STYLE="display:inline" ACTION=<% $cgi->url %> METHOD="GET">
Active phone services as of <& /elements/input-date-field.html, {
'name' => 'date',
'value' => $time,
'format' => FS::Conf->new->config('date_format') || '%m/%d/%Y'
} &>
<INPUT TYPE="hidden" NAME="order_by" VALUE="<% $cgi->param('order_by') %>">
<INPUT TYPE="submit" VALUE="Refresh">
</FORM>
<BR>
<BR>
</%def>
<%init>
# svc_phone-specific for now; may change later
my $curuser = $FS::CurrentUser::CurrentUser;
die "access denied" unless $curuser->access_right('Services: Phone numbers');
my $title = 'Phone services by state';
my $time = time;
if ( $cgi->param('date') ) {
$time = parse_datetime($cgi->param('date'));
$title .= time2str(' (%b %o, %Y)', $time);
}
my @tables = qw(svc_phone cust_svc cust_pkg cust_location cust_main);
my @pkeys = qw(svcnum svcnum pkgnum locationnum custnum);
my @h_tables = map "h_$_", @tables;
my $addl_from = '';
my @where;
for(my $i = 0; $i < scalar(@tables); $i++) {
my $last_table = $h_tables[$i-1];
my $pkey = $pkeys[$i];
my $table = $tables[$i];
my $h_table = $h_tables[$i];
# alias the preceding table, and join to a subquery that finds the most
# recent change to $table.$pkey before $time
my $alias = $h_table;
my $inside = '';
if ( $i > 0 ) {
$alias = "t$i";
$inside = " AS $alias";
}
$inside .= "
JOIN
(SELECT $pkey AS num, MAX(history_date) AS history_date
FROM $h_table
WHERE history_date <= $time AND
history_action IN ('insert', 'replace_new')
GROUP BY $pkey
) AS mostrecent_$table
ON ($alias.$pkey = mostrecent_$table.num AND
$alias.history_date = mostrecent_$table.history_date AND
$alias.history_action IN ('insert', 'replace_new')
)
LEFT JOIN
(SELECT $pkey AS num, MAX(history_date) AS history_date, 1 AS deleted
FROM $h_table
WHERE history_date <= $time AND
history_action = 'delete'
GROUP BY $pkey
) AS deleted_$table
ON (mostrecent_$table.num = deleted_$table.num AND
mostrecent_$table.history_date < deleted_$table.history_date
)
";
# join to the preceding table if there is one, and filter out
# deleted records
if ( $i > 0 ) {
# special case to make pre-3.x data work; remove this later
if ( $table eq 'cust_main' ) {
$last_table = 'h_cust_pkg';
}
$addl_from .= "
LEFT JOIN ( $h_table $inside ) AS $h_table
ON ($h_table.$pkey = $last_table.$pkey)";
push @where, "$h_table.deleted IS NULL";
} else {
$addl_from .= $inside;
push @where, "deleted_$table.deleted IS NULL";
}
}
# so that we know which services are still active
$addl_from .= "
LEFT JOIN svc_phone ON (h_svc_phone.svcnum = svc_phone.svcnum AND
h_svc_phone.phonenum = svc_phone.phonenum)";
#warn "\n\nJOIN EXPRESSION:\n$addl_from\n\n";
push @where, $curuser->agentnums_sql(
'table' => 'h_cust_main',
'null_right' => 'View/link unlinked services'
);
my $where = " WHERE ".join(' AND ', map "($_)", @where);
# for pre-3.x data
my $group_field = 'COALESCE(h_cust_location.state, h_cust_main.ship_state, h_cust_main.state)';
my @select = (
"$group_field AS state",
'count(DISTINCT h_svc_phone.svcnum) AS num_svcnums',
# don't DISTINCT these (it reorders them)
"array_to_string(array_agg(h_svc_phone.phonenum), ',') AS all_phonenums",
"array_to_string(array_agg(h_svc_phone.svcnum), ',') AS all_svcnums",
"array_to_string(array_agg(svc_phone.svcnum), ',') AS active_svcnums",
);
my $query = {
'select' => join(',', @select),
'table' => 'h_svc_phone',
'addl_from' => $addl_from,
'extra_sql' => " $where GROUP BY $group_field",
};
# DISTINCT on these because of cross-producting effects when a cust_pkg
# record (usually) was replaced more than once within one second.
my $count_query =
"SELECT COUNT(DISTINCT $group_field), COUNT(DISTINCT h_svc_phone.svcnum) ".
"FROM h_svc_phone $addl_from $where";
my $count_addl = [ '%d phone services' ];
my $detail_sub = sub {
my $rec = shift;
warn Dumper $rec;
my @svcnums = split(',', $rec->all_svcnums);
my @phonenums = split(',', $rec->all_phonenums);
# identifies services that still exist with the same svcnum+phonenum
my %active = map { $_ => 1 } split(',', $rec->active_svcnums);
# make a single column of phonenums
my @return;
my %seen;
while (my $svcnum = shift @svcnums) {
my $phonenum = shift @phonenums;
next if $seen{$svcnum};
$seen{$svcnum} = 1;
my $link = $active{$svcnum} ?
$p.'view/svc_phone.cgi?'.$svcnum :
'';
push @return, [ { data => $phonenum,
link => $link,
data_style => ($active{$svcnum} ? '' : 'i')
} ];
}
\@return;
};
</%init>
|
