httemplate/search/phone_state.html

   1 <& elements/search.html,
   2   'title'       => $title,
   3   'name'        => 'states',
   4   'query'       => $query,
   5   'count_query' => $count_query,
   6   'count_addl'  => $count_addl,
   7   'header'      => [ 'State', # if we add more group fields, change this
   8                      'Count',
   9                      'Phone numbers'
  10                    ],
  11   'fields'      => [ 'state',
  12                      'num_svcnums',
  13                      $detail_sub
  14                    ],
  15   'html_init'   => include('.head', $time),
  16 &>
  17 <%def .head>
  18 % my $time = shift;
  19 <FORM STYLE="display:inline" ACTION=<% $cgi->url %> METHOD="GET">
  20 Active phone services as of <& /elements/input-date-field.html, {
  21   'name'    => 'date',
  22   'value'   => $time,
  23   'format'  => FS::Conf->new->config('date_format') || '%m/%d/%Y'
  24 } &>
  25 <INPUT TYPE="hidden" NAME="order_by" VALUE="<% $cgi->param('order_by') %>">
  26 <INPUT TYPE="submit" VALUE="Refresh">
  27 </FORM>
  28 <BR>
  29 <BR>
  30 </%def>
  31 <%init>
  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';
  36
  37 my $time = time;
  38 if ( $cgi->param('date') ) {
  39   $time = parse_datetime($cgi->param('date'));
  40   $title .= time2str(' (%b %o, %Y)', $time);
  41 }
  42
  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;
  46
  47 my $addl_from = '';
  48 my @where;
  49
  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
  57   my $alias = $h_table;
  58   my $inside = '';
  59   if ( $i > 0 ) {
  60     $alias = "t$i";
  61     $inside = " AS $alias";
  62   }
  63   $inside .= "
  64       JOIN
  65         (SELECT $pkey AS num, MAX(history_date) AS history_date
  66                 FROM $h_table
  67                 WHERE history_date <= $time AND
  68                       history_action IN ('insert', 'replace_new')
  69                 GROUP BY $pkey
  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')
  74         )
  75       LEFT JOIN
  76         (SELECT $pkey AS num, MAX(history_date) AS history_date, 1 AS deleted
  77                 FROM $h_table
  78                 WHERE history_date <= $time AND
  79                       history_action = 'delete'
  80                 GROUP BY $pkey
  81         ) AS deleted_$table
  82         ON (mostrecent_$table.num = deleted_$table.num AND
  83             mostrecent_$table.history_date < deleted_$table.history_date
  84         )
  85 ";
  86   # join to the preceding table if there is one, and filter out
  87   # deleted records
  88   if ( $i > 0 ) {
  89     $addl_from .= "
  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";
  93   } else {
  94     $addl_from .= $inside;
  95     push @where, "deleted_$table.deleted IS NULL";
  96   }
  97 }
  98
  99 # so that we know which services are still active
 100 $addl_from .= "
 101   LEFT JOIN svc_phone ON (h_svc_phone.svcnum = svc_phone.svcnum AND
 102                           h_svc_phone.phonenum = svc_phone.phonenum)";
 103
 104 #warn "\n\nJOIN EXPRESSION:\n$addl_from\n\n";
 105
 106 push @where, $curuser->agentnums_sql(
 107   'table' => 'h_cust_main',
 108   'null_right' => 'View/link unlinked services'
 109 );
 110 my $where = " WHERE ".join(' AND ', map "($_)", @where);
 111
 112 my $group_field = 'h_cust_location.state';
 113 my @select = (
 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",
 120 );
 121
 122 my $query = {
 123   'select'    => join(',', @select),
 124   'table'     => 'h_svc_phone',
 125   'addl_from' => $addl_from,
 126   'extra_sql' => " $where GROUP BY $group_field",
 127 };
 128
 129 # DISTINCT on these because of cross-producting effects when a cust_pkg
 130 # record (usually) was replaced more than once within one second.
 131 my $count_query =
 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' ];
 135
 136 my $detail_sub = sub {
 137   my $rec = shift;
 138   warn Dumper $rec;
 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
 144   my @return;
 145   my %seen;
 146   while (my $svcnum = shift @svcnums) {
 147     my $phonenum = shift @phonenums;
 148     next if $seen{$svcnum};
 149     $seen{$svcnum} = 1;
 150     my $link = $active{$svcnum} ?
 151                 $p.'view/svc_phone.cgi?'.$svcnum :
 152                 '';
 153     push @return, [ { data => $phonenum,
 154                       link => $link,
 155                       data_style => ($active{$svcnum} ? '' : 'i')
 156                      } ];
 157   }
 158   \@return;
 159 };
 160
 161 </%init>