httemplate/search/cust_pkg.cgi

   1 %
   2 %
   3 %# my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {});
   4 %
   5 %my($query) = $cgi->keywords;
   6 %
   7 %my @where = ();
   8 %
   9 %##
  10 %# parse agent
  11 %##
  12 %
  13 %if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) {
  14 %  push @where,
  15 %    "agentnum = $1";
  16 %}
  17 %
  18 %##
  19 %# parse status
  20 %##
  21 %
  22 %if (    $cgi->param('magic')  eq 'active'
  23 %     || $cgi->param('status') eq 'active' ) {
  24 %
  25 %  push @where, FS::cust_pkg->active_sql();
  26 %
  27 %} elsif (    $cgi->param('magic')  eq 'inactive'
  28 %          || $cgi->param('status') eq 'inactive' ) {
  29 %
  30 %  push @where, FS::cust_pkg->inactive_sql();
  31 %
  32 %
  33 %} elsif (    $cgi->param('magic')  eq 'suspended'
  34 %          || $cgi->param('status') eq 'suspended'  ) {
  35 %
  36 %  push @where, FS::cust_pkg->suspended_sql();
  37 %
  38 %} elsif (    $cgi->param('magic')  =~ /^cancell?ed$/
  39 %          || $cgi->param('status') =~ /^cancell?ed$/ ) {
  40 %
  41 %  push @where, FS::cust_pkg->cancelled_sql();
  42 %
  43 %} elsif ( $cgi->param('status') =~ /^(one-time charge|inactive)$/ ) {
  44 %
  45 %  push @where, FS::cust_pkg->inactive_sql();
  46 %
  47 %}
  48 %
  49 %###
  50 %# parse package class
  51 %###
  52 %
  53 %#false lazinessish w/graph/cust_bill_pkg.cgi
  54 %my $classnum = 0;
  55 %my @pkg_class = ();
  56 %if ( exists($cgi->Vars->{'classnum'})
  57 %     && $cgi->param('classnum') =~ /^(\d*)$/
  58 %   )
  59 %{
  60 %  $classnum = $1;
  61 %  if ( $classnum ) { #a specific class
  62 %    push @where, "classnum = $classnum";
  63 %
  64 %    #@pkg_class = ( qsearchs('pkg_class', { 'classnum' => $classnum } ) );
  65 %    #die "classnum $classnum not found!" unless $pkg_class[0];
  66 %    #$title .= $pkg_class[0]->classname.' ';
  67 %
  68 %  } elsif ( $classnum eq '' ) { #the empty class
  69 %
  70 %    push @where, "classnum IS NULL";
  71 %    #$title .= 'Empty class ';
  72 %    #@pkg_class = ( '(empty class)' );
  73 %  } elsif ( $classnum eq '0' ) {
  74 %    #@pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } );
  75 %    #push @pkg_class, '(empty class)';
  76 %  } else {
  77 %    die "illegal classnum";
  78 %  }
  79 %}
  80 %#eslaf
  81 %
  82 %###
  83 %# parse magic, legacy, etc.
  84 %###
  85 %
  86 %my $orderby;
  87 %if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) {
  88 %  $orderby = 'ORDER BY bill';
  89 %
  90 %  my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
  91 %  push @where,
  92 %    #"bill >= $beginning ",
  93 %    #"bill <= $ending",
  94 %    "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ",
  95 %    "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending",
  96 %    #'( cancel IS NULL OR cancel = 0 )'
  97 %  ;
  98 %
  99 %} else {
 100 %
 101 %  if ( $cgi->param('magic') &&
 102 %       $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/
 103 %  ) {
 104 %
 105 %    $orderby = 'ORDER BY pkgnum';
 106 %
 107 %    if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) {
 108 %      push @where, "pkgpart = $1";
 109 %    }
 110 %
 111 %  } elsif ( $query eq 'pkgnum' ) {
 112 %
 113 %    $orderby = 'ORDER BY pkgnum';
 114 %
 115 %  } elsif ( $query eq 'APKG_pkgnum' ) {
 116 %
 117 %    $orderby = 'ORDER BY pkgnum';
 118 %
 119 %    push @where, '0 < (
 120 %      SELECT count(*) FROM pkg_svc
 121 %       WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
 122 %         AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
 123 %                                   WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
 124 %                                     AND cust_svc.svcpart = pkg_svc.svcpart
 125 %                                )
 126 %    )';
 127 %
 128 %  } else {
 129 %    die "Empty or unknown QUERY_STRING!";
 130 %  }
 131 %
 132 %}
 133 %
 134 %##
 135 %# setup queries, links, subs, etc. for the search
 136 %##
 137 %
 138 %# here is the agent virtualization
 139 %push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
 140 %
 141 %my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 142 %
 143 %my $addl_from = 'LEFT JOIN cust_main USING ( custnum  ) '.
 144 %                'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
 145 %                'LEFT JOIN pkg_class USING ( classnum ) ';
 146 %
 147 %my $count_query = "SELECT COUNT(*) FROM cust_pkg $addl_from $extra_sql";
 148 %
 149 %my $sql_query = {
 150 %  'table'     => 'cust_pkg',
 151 %  'hashref'   => {},
 152 %  'select'    => join(', ',
 153 %                            'cust_pkg.*',
 154 %                            ( map "part_pkg.$_", qw( pkg freq ) ),
 155 %                            'pkg_class.classname',
 156 %                            'cust_main.custnum as cust_main_custnum',
 157 %                            FS::UI::Web::cust_sql_fields(
 158 %                              $cgi->param('cust_fields')
 159 %                            ),
 160 %                 ),
 161 %  'extra_sql' => "$extra_sql $orderby",
 162 %  'addl_from' => $addl_from,
 163 %};
 164 %
 165 %my $link = sub {
 166 %  [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ];
 167 %};
 168 %
 169 %my $clink = sub {
 170 %  my $cust_pkg = shift;
 171 %  $cust_pkg->cust_main_custnum
 172 %    ? [ "${p}view/cust_main.cgi?", 'custnum' ]
 173 %    : '';
 174 %};
 175 %
 176 %#if ( scalar(@cust_pkg) == 1 ) {
 177 %#  print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum.
 178 %#                       "#cust_pkg". $cust_pkg[0]->pkgnum );
 179 %
 180 %#    my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } );
 181 %#    my $rowspan = scalar(@cust_svc) || 1;
 182 %
 183 %#    my $n2 = '';
 184 %#    foreach my $cust_svc ( @cust_svc ) {
 185 %#      my($label, $value, $svcdb) = $cust_svc->label;
 186 %#      my $svcnum = $cust_svc->svcnum;
 187 %#      my $sview = $p. "view";
 188 %#      print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!,
 189 %#            qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!;
 190 %#      $n2="</TR><TR>";
 191 %#    }
 192 %
 193 %sub time_or_blank {
 194 %   my $column = shift;
 195 %   return sub {
 196 %     my $record = shift;
 197 %     my $value = $record->get($column); #mmm closures
 198 %     $value ? time2str('%b %d %Y', $value ) : '';
 199 %   };
 200 %}
 201 %
 202 %###
 203 %# and finally, include the search template
 204 %###
 205 %
 206 %
 207 <%  include( 'elements/search.html',
 208                   'title'       => 'Package Search Results',
 209                   'name'        => 'packages',
 210                   'query'       => $sql_query,
 211                   'count_query' => $count_query,
 212                   #'redirect'    => $link,
 213                   'header'      => [ '#',
 214                                      'Package',
 215                                      'Class',
 216                                      'Status',
 217                                      'Freq.',
 218                                      'Setup',
 219                                      'Last bill',
 220                                      'Next bill',
 221                                      'Susp.',
 222                                      'Expire',
 223                                      'Cancel',
 224                                      FS::UI::Web::cust_header(
 225                                        $cgi->param('cust_fields')
 226                                      ),
 227                                      'Services',
 228                                    ],
 229                   'fields'      => [
 230                     'pkgnum',
 231                     sub { #my $part_pkg = $part_pkg{shift->pkgpart};
 232                           #$part_pkg->pkg; # ' - '. $part_pkg->comment;
 233                           $_[0]->pkg; # ' - '. $_[0]->comment;
 234                         },
 235                     'classname',
 236                     sub { ucfirst(shift->status); },
 237                     sub { #shift->part_pkg->freq_pretty;
 238
 239                           #my $part_pkg = $part_pkg{shift->pkgpart};
 240                           #$part_pkg->freq_pretty;
 241
 242                           FS::part_pkg::freq_pretty(shift);
 243                         },
 244
 245                     #sub { time2str('%b %d %Y', shift->setup); },
 246                     #sub { time2str('%b %d %Y', shift->last_bill); },
 247                     #sub { time2str('%b %d %Y', shift->bill); },
 248                     #sub { time2str('%b %d %Y', shift->susp); },
 249                     #sub { time2str('%b %d %Y', shift->expire); },
 250                     #sub { time2str('%b %d %Y', shift->get('cancel')); },
 251                     ( map { time_or_blank($_) }
 252                           qw( setup last_bill bill susp expire cancel ) ),
 253
 254                     \&FS::UI::Web::cust_fields,
 255                     #sub { '<table border=0 cellspacing=0 cellpadding=0 STYLE="border:none">'.
 256                     #      join('', map { '<tr><td align="right" style="border:none">'. $_->[0].
 257                     #                     ':</td><td style="border:none">'. $_->[1]. '</td></tr>' }
 258                     #                   shift->labels
 259                     #          ).
 260                     #      '</table>';
 261                     #    },
 262                     sub {
 263                           [ map {
 264                                   [
 265                                     { 'data' => $_->[0]. ':',
 266                                       'align'=> 'right',
 267                                     },
 268                                     { 'data' => $_->[1],
 269                                       'align'=> 'left',
 270                                       'link' => $p. 'view/' .
 271                                                 $_->[2]. '.cgi?'. $_->[3],
 272                                     },
 273                                   ];
 274                                 } shift->labels
 275                           ];
 276                         },
 277                   ],
 278                   'color' => [
 279                     '',
 280                     '',
 281                     '',
 282                     sub { shift->statuscolor; },
 283                     '',
 284                     '',
 285                     '',
 286                     '',
 287                     '',
 288                     '',
 289                     '',
 290                     ( map { '' }
 291                           FS::UI::Web::cust_header(
 292                                                     $cgi->param('cust_fields')
 293                                                   )
 294                     ),
 295                     '',
 296                   ],
 297                   'style' => [ '', '', '', 'b' ],
 298                   'size'  => [ '', '', '', '-1', ],
 299                   'align' => 'rllclrrrrrr',
 300                   'links' => [
 301                     $link,
 302                     $link,
 303                     '',
 304                     '',
 305                     '',
 306                     '',
 307                     '',
 308                     '',
 309                     '',
 310                     '',
 311                     '',
 312                     ( map { $clink }
 313                           FS::UI::Web::cust_header(
 314                                                     $cgi->param('cust_fields')
 315                                                   )
 316                     ),
 317                     '',
 318                   ],
 319               )
 320 %>