From: Mark Wells Date: Sun, 29 Sep 2013 00:52:54 +0000 (-0700) Subject: report showing phone number breakdown by state, #24789 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=c1285aad3e79b4fd9312002cd03c0bdc325acc25 report showing phone number breakdown by state, #24789 --- diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index edb5e8159..c8fe5f2e8 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -197,6 +197,10 @@ foreach my $svcdb ( FS::part_svc->svc_tables() ) { [ $fsurl. 'search/report_svc_phone_usage.html', 'Total usage (minutes, and amount billed) for the specified time period, per phone number.', ]; + $report_svc{"${name} by state"} = + [ $fsurl. 'search/phone_state.html', + 'Current or historical phone services broken down by state.', + ]; } diff --git a/httemplate/search/phone_state.html b/httemplate/search/phone_state.html new file mode 100644 index 000000000..7b3acbbbf --- /dev/null +++ b/httemplate/search/phone_state.html @@ -0,0 +1,161 @@ +<& 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; +
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' +} &> + + +
+
+
+ +<%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 ) { + $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); + +my $group_field = 'h_cust_location.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; +}; + +