httemplate/search/svc_phone.cgi

   1 <& elements/svc_Common.html,
   2                  'title'             => "Phone number search results",
   3                  'name'              => 'phone numbers',
   4                  'query'             => $sql_query,
   5                  'count_query'       => $count_query,
   6                  'redirect'          => $redirect,
   7                  'header'            => [ '#',
   8                                           'Service',
   9                                           'Country code',
  10                                           'Phone number',
  11                                           @header,
  12                                           FS::UI::Web::cust_header($cgi->param('cust_fields')),
  13                                         ],
  14                  'fields'            => [ 'svcnum',
  15                                           'svc',
  16                                           'countrycode',
  17                                           'phonenum',
  18                                           @fields,
  19                                           \&FS::UI::Web::cust_fields,
  20                                         ],
  21                  'links'             => [ $link,
  22                                           $link,
  23                                           $link,
  24                                           $link,
  25                                           ( map '', @header ),
  26                                           ( map { $_ ne 'Cust. Status' ? $link_cust : '' }
  27                                                 FS::UI::Web::cust_header($cgi->param('cust_fields'))
  28                                           ),
  29                                         ],
  30                  'align' => 'rlrr'.
  31                             join('', map 'r', @header).
  32                             FS::UI::Web::cust_aligns(),
  33                  'color' => [
  34                               '',
  35                               '',
  36                               '',
  37                               '',
  38                               ( map '', @header ),
  39                               FS::UI::Web::cust_colors(),
  40                             ],
  41                  'style' => [
  42                               '',
  43                               '',
  44                               '',
  45                               '',
  46                               ( map '', @header ),
  47                               FS::UI::Web::cust_styles(),
  48                             ],
  49
  50 &>
  51 <%init>
  52
  53 die "access denied"
  54   unless $FS::CurrentUser::CurrentUser->access_right('List services');
  55
  56 my $conf = new FS::Conf;
  57
  58 my @select = ();
  59 my $orderby = 'ORDER BY svcnum';
  60
  61 my @header = ();
  62 my @fields = ();
  63 my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ];
  64 my $redirect = $link;
  65
  66 my %search_hash = ();
  67 my @extra_sql = ();
  68
  69 if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
  70
  71   $search_hash{'unlinked'} = 1
  72     if $cgi->param('magic') eq 'unlinked';
  73
  74   if ( $cgi->param('sortby') =~ /^(\w+)$/ ) {
  75     my $sortby = $1;
  76     $orderby = "ORDER BY $sortby";
  77   }
  78
  79   if ( $cgi->param('usage_total') ) {
  80
  81     my($beginning,$ending) = FS::UI::Web::parse_beginning_ending($cgi, 'usage');
  82
  83     $redirect = '';
  84
  85     #my $and_date = " AND startdate >= $beginning AND startdate <= $ending ";
  86     my $and_date = " AND enddate >= $beginning AND enddate <= $ending ";
  87
  88     my $fromwhere = " FROM cdr WHERE cdr.svcnum = svc_phone.svcnum $and_date";
  89
  90     #more efficient to join against cdr just once... this will do for now
  91     push @select, map { " ( SELECT SUM($_) $fromwhere ) AS $_ " }
  92                       qw( billsec rated_price );
  93
  94     my $money_char = $conf->config('money_char') || '$';
  95
  96     push @header, 'Minutes', 'Billed';
  97     push @fields,
  98       sub { sprintf('%.3f', shift->get('billsec') / 60 ); },
  99       sub { $money_char. sprintf('%.2f', shift->get('rated_price') ); };
 100
 101     #XXX and termination... (this needs a config to turn on, not by default)
 102     if ( 1 ) { # $conf->exists('cdr-termination_hack') { #}
 103
 104       my $f_w =
 105         " FROM cdr_termination LEFT JOIN cdr USING ( acctid ) ".
 106         " WHERE cdr.carrierid = CAST(svc_phone.phonenum AS BIGINT) ". # XXX connectone-specific, has to match svc_external.id :/
 107         $and_date;
 108
 109       push @select,
 110         " ( SELECT SUM(billsec) $f_w ) AS term_billsec ",
 111         " ( SELECT SUM(cdr_termination.rated_price) $f_w ) AS term_rated_price";
 112
 113       push @header, 'Term Min', 'Term Billed';
 114       push @fields,
 115         sub { sprintf('%.3f', shift->get('term_billsec') / 60 ); },
 116         sub { $money_char. sprintf('%.2f', shift->get('rated_price') ); };
 117
 118     }
 119
 120
 121   }
 122
 123 } elsif ( $cgi->param('magic') =~ /^advanced$/ ) {
 124
 125   for (qw( agentnum custnum cust_status balance balance_days cust_fields )) {
 126     $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
 127   }
 128
 129   for (qw( payby pkgpart svcpart )) {
 130     $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_);
 131   }
 132
 133 } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) {
 134   $search_hash{'svcpart'} = [ $1 ];
 135 } else {
 136   $cgi->param('phonenum') =~ /^([\d\- ]+)$/;
 137   my $phonenum = $1;
 138   $phonenum =~ s/\D//g;
 139   push @extra_sql, "phonenum = '$phonenum'";
 140 }
 141
 142 $search_hash{'addl_select'} = \@select;
 143 $search_hash{'order_by'} = $orderby;
 144 $search_hash{'where'} = \@extra_sql;
 145
 146 my $sql_query = FS::svc_phone->search(\%search_hash);
 147 my $count_query = delete($sql_query->{'count_query'});
 148
 149 #smaller false laziness w/svc_*.cgi here
 150 my $link_cust = sub {
 151   my $svc_x = shift;
 152   $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : '';
 153 };
 154
 155 </%init>