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     # special case to make pre-3.x data work; remove this later
  90     if ( $table eq 'cust_main' ) {
  91       $last_table = 'h_cust_pkg';
  92     }
  93     $addl_from .= "
  94       LEFT JOIN ( $h_table $inside ) AS $h_table
  95         ON ($h_table.$pkey = $last_table.$pkey)";
  96     push @where, "$h_table.deleted IS NULL";
  97   } else {
  98     $addl_from .= $inside;
  99     push @where, "deleted_$table.deleted IS NULL";
 100   }
 101 }
 102
 103 # so that we know which services are still active
 104 $addl_from .= "
 105   LEFT JOIN svc_phone ON (h_svc_phone.svcnum = svc_phone.svcnum AND
 106                           h_svc_phone.phonenum = svc_phone.phonenum)";
 107
 108 #warn "\n\nJOIN EXPRESSION:\n$addl_from\n\n";
 109
 110 push @where, $curuser->agentnums_sql(
 111   'table' => 'h_cust_main',
 112   'null_right' => 'View/link unlinked services'
 113 );
 114 my $where = " WHERE ".join(' AND ', map "($_)", @where);
 115
 116 # for pre-3.x data
 117 my $group_field = 'COALESCE(h_cust_location.state, h_cust_main.ship_state, h_cust_main.state)';
 118
 119 my @select = (
 120   "$group_field AS state",
 121   'count(DISTINCT h_svc_phone.svcnum) AS num_svcnums',
 122   # don't DISTINCT these (it reorders them)
 123   "array_to_string(array_agg(h_svc_phone.phonenum), ',') AS all_phonenums",
 124   "array_to_string(array_agg(h_svc_phone.svcnum), ',') AS all_svcnums",
 125   "array_to_string(array_agg(svc_phone.svcnum), ',') AS active_svcnums",
 126 );
 127
 128 my $query = {
 129   'select'    => join(',', @select),
 130   'table'     => 'h_svc_phone',
 131   'addl_from' => $addl_from,
 132   'extra_sql' => " $where GROUP BY $group_field",
 133 };
 134
 135 # DISTINCT on these because of cross-producting effects when a cust_pkg
 136 # record (usually) was replaced more than once within one second.
 137 my $count_query =
 138   "SELECT COUNT(DISTINCT $group_field), COUNT(DISTINCT h_svc_phone.svcnum) ".
 139   "FROM h_svc_phone $addl_from $where";
 140 my $count_addl = [ '%d phone services' ];
 141
 142 my $detail_sub = sub {
 143   my $rec = shift;
 144   warn Dumper $rec;
 145   my @svcnums = split(',', $rec->all_svcnums);
 146   my @phonenums = split(',', $rec->all_phonenums);
 147   # identifies services that still exist with the same svcnum+phonenum
 148   my %active = map { $_ => 1 } split(',', $rec->active_svcnums);
 149   # make a single column of phonenums
 150   my @return;
 151   my %seen;
 152   while (my $svcnum = shift @svcnums) {
 153     my $phonenum = shift @phonenums;
 154     next if $seen{$svcnum};
 155     $seen{$svcnum} = 1;
 156     my $link = $active{$svcnum} ?
 157                 $p.'view/svc_phone.cgi?'.$svcnum :
 158                 '';
 159     push @return, [ { data => $phonenum,
 160                       link => $link,
 161                       data_style => ($active{$svcnum} ? '' : 'i')
 162                      } ];
 163   }
 164   \@return;
 165 };
 166
 167 </%init>