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 part_pkg
  84 %###
  85 %
  86 %my $pkgpart = join (' OR pkgpart=',
  87 %                    grep {$_} map { /^(\d+)$/; } ($cgi->param('pkgpart')));
  88 %push @where,  '(pkgpart=' . $pkgpart . ')' if $pkgpart;
  89 %
  90 %###
  91 %# parse magic, legacy, etc.
  92 %###
  93 %
  94 %my $orderby;
  95 %if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) {
  96 %  $orderby = 'ORDER BY bill';
  97 %
  98 %  my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
  99 %  push @where,
 100 %    #"bill >= $beginning ",
 101 %    #"bill <= $ending",
 102 %    "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ",
 103 %    "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending",
 104 %    #'( cancel IS NULL OR cancel = 0 )'
 105 %  ;
 106 %
 107 %} else {
 108 %
 109 %  if ( $cgi->param('magic') &&
 110 %       $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/
 111 %  ) {
 112 %
 113 %    $orderby = 'ORDER BY pkgnum';
 114 %
 115 %    if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) {
 116 %      push @where, "pkgpart = $1";
 117 %    }
 118 %
 119 %  } elsif ( $query eq 'pkgnum' ) {
 120 %
 121 %    $orderby = 'ORDER BY pkgnum';
 122 %
 123 %  } elsif ( $query eq 'APKG_pkgnum' ) {
 124 %
 125 %    $orderby = 'ORDER BY pkgnum';
 126 %
 127 %    push @where, '0 < (
 128 %      SELECT count(*) FROM pkg_svc
 129 %       WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
 130 %         AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
 131 %                                   WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
 132 %                                     AND cust_svc.svcpart = pkg_svc.svcpart
 133 %                                )
 134 %    )';
 135 %
 136 %  } else {
 137 %    die "Empty or unknown QUERY_STRING!";
 138 %  }
 139 %
 140 %}
 141 %
 142 %##
 143 %# setup queries, links, subs, etc. for the search
 144 %##
 145 %
 146 %# here is the agent virtualization
 147 %push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
 148 %
 149 %my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 150 %
 151 %my $addl_from = 'LEFT JOIN cust_main USING ( custnum  ) '.
 152 %                'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
 153 %                'LEFT JOIN pkg_class USING ( classnum ) ';
 154 %
 155 %my $count_query = "SELECT COUNT(*) FROM cust_pkg $addl_from $extra_sql";
 156 %
 157 %my $sql_query = {
 158 %  'table'     => 'cust_pkg',
 159 %  'hashref'   => {},
 160 %  'select'    => join(', ',
 161 %                            'cust_pkg.*',
 162 %                            ( map "part_pkg.$_", qw( pkg freq ) ),
 163 %                            'pkg_class.classname',
 164 %                            'cust_main.custnum as cust_main_custnum',
 165 %                            FS::UI::Web::cust_sql_fields(
 166 %                              $cgi->param('cust_fields')
 167 %                            ),
 168 %                 ),
 169 %  'extra_sql' => "$extra_sql $orderby",
 170 %  'addl_from' => $addl_from,
 171 %};
 172 %
 173 %my $link = sub {
 174 %  [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ];
 175 %};
 176 %
 177 %my $clink = sub {
 178 %  my $cust_pkg = shift;
 179 %  $cust_pkg->cust_main_custnum
 180 %    ? [ "${p}view/cust_main.cgi?", 'custnum' ]
 181 %    : '';
 182 %};
 183 %
 184 %#if ( scalar(@cust_pkg) == 1 ) {
 185 %#  print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum.
 186 %#                       "#cust_pkg". $cust_pkg[0]->pkgnum );
 187 %
 188 %#    my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } );
 189 %#    my $rowspan = scalar(@cust_svc) || 1;
 190 %
 191 %#    my $n2 = '';
 192 %#    foreach my $cust_svc ( @cust_svc ) {
 193 %#      my($label, $value, $svcdb) = $cust_svc->label;
 194 %#      my $svcnum = $cust_svc->svcnum;
 195 %#      my $sview = $p. "view";
 196 %#      print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!,
 197 %#            qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!;
 198 %#      $n2="</TR><TR>";
 199 %#    }
 200 %
 201 %sub time_or_blank {
 202 %   my $column = shift;
 203 %   return sub {
 204 %     my $record = shift;
 205 %     my $value = $record->get($column); #mmm closures
 206 %     $value ? time2str('%b %d %Y', $value ) : '';
 207 %   };
 208 %}
 209 %
 210 %###
 211 %# and finally, include the search template
 212 %###
 213 %
 214 %
 215 <%  include( 'elements/search.html',
 216                   'title'       => 'Package Search Results',
 217                   'name'        => 'packages',
 218                   'query'       => $sql_query,
 219                   'count_query' => $count_query,
 220                   #'redirect'    => $link,
 221                   'header'      => [ '#',
 222                                      'Package',
 223                                      'Class',
 224                                      'Status',
 225                                      'Freq.',
 226                                      'Setup',
 227                                      'Last bill',
 228                                      'Next bill',
 229                                      'Susp.',
 230                                      'Expire',
 231                                      'Cancel',
 232                                      FS::UI::Web::cust_header(
 233                                        $cgi->param('cust_fields')
 234                                      ),
 235                                      'Services',
 236                                    ],
 237                   'fields'      => [
 238                     'pkgnum',
 239                     sub { #my $part_pkg = $part_pkg{shift->pkgpart};
 240                           #$part_pkg->pkg; # ' - '. $part_pkg->comment;
 241                           $_[0]->pkg; # ' - '. $_[0]->comment;
 242                         },
 243                     'classname',
 244                     sub { ucfirst(shift->status); },
 245                     sub { #shift->part_pkg->freq_pretty;
 246
 247                           #my $part_pkg = $part_pkg{shift->pkgpart};
 248                           #$part_pkg->freq_pretty;
 249
 250                           FS::part_pkg::freq_pretty(shift);
 251                         },
 252
 253                     #sub { time2str('%b %d %Y', shift->setup); },
 254                     #sub { time2str('%b %d %Y', shift->last_bill); },
 255                     #sub { time2str('%b %d %Y', shift->bill); },
 256                     #sub { time2str('%b %d %Y', shift->susp); },
 257                     #sub { time2str('%b %d %Y', shift->expire); },
 258                     #sub { time2str('%b %d %Y', shift->get('cancel')); },
 259                     ( map { time_or_blank($_) }
 260                           qw( setup last_bill bill susp expire cancel ) ),
 261
 262                     \&FS::UI::Web::cust_fields,
 263                     #sub { '<table border=0 cellspacing=0 cellpadding=0 STYLE="border:none">'.
 264                     #      join('', map { '<tr><td align="right" style="border:none">'. $_->[0].
 265                     #                     ':</td><td style="border:none">'. $_->[1]. '</td></tr>' }
 266                     #                   shift->labels
 267                     #          ).
 268                     #      '</table>';
 269                     #    },
 270                     sub {
 271                           [ map {
 272                                   [
 273                                     { 'data' => $_->[0]. ':',
 274                                       'align'=> 'right',
 275                                     },
 276                                     { 'data' => $_->[1],
 277                                       'align'=> 'left',
 278                                       'link' => $p. 'view/' .
 279                                                 $_->[2]. '.cgi?'. $_->[3],
 280                                     },
 281                                   ];
 282                                 } shift->labels
 283                           ];
 284                         },
 285                   ],
 286                   'color' => [
 287                     '',
 288                     '',
 289                     '',
 290                     sub { shift->statuscolor; },
 291                     '',
 292                     '',
 293                     '',
 294                     '',
 295                     '',
 296                     '',
 297                     '',
 298                     ( map { '' }
 299                           FS::UI::Web::cust_header(
 300                                                     $cgi->param('cust_fields')
 301                                                   )
 302                     ),
 303                     '',
 304                   ],
 305                   'style' => [ '', '', '', 'b' ],
 306                   'size'  => [ '', '', '', '-1', ],
 307                   'align' => 'rllclrrrrrr',
 308                   'links' => [
 309                     $link,
 310                     $link,
 311                     '',
 312                     '',
 313                     '',
 314                     '',
 315                     '',
 316                     '',
 317                     '',
 318                     '',
 319                     '',
 320                     ( map { $clink }
 321                           FS::UI::Web::cust_header(
 322                                                     $cgi->param('cust_fields')
 323                                                   )
 324                     ),
 325                     '',
 326                   ],
 327               )
 328 %>