X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_pkg.cgi;h=a2fb89c12fd01e3880f9edf422099dff9252e93e;hp=d63d2d188348699b621d5e93fbec969ef663a86f;hb=1a033848671cad2cbe7687b37fc718b3b2a68b83;hpb=51984ac3d3da3006809c6866fdecd4ad83610731

diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi
index d63d2d188..a2fb89c12 100755
--- a/httemplate/search/cust_pkg.cgi
+++ b/httemplate/search/cust_pkg.cgi
@@ -1,155 +1,236 @@
 <%
-#
-# $Id: cust_pkg.cgi,v 1.1 2001-07-30 07:36:04 ivan Exp $
-#
-# based on search/svc_acct.cgi ivan@sisd.com 98-jul-17
-#
-# $Log: cust_pkg.cgi,v $
-# Revision 1.1  2001-07-30 07:36:04  ivan
-# templates!!!
-#
-# Revision 1.11  2000/07/17 16:45:41  ivan
-# first shot at invoice browsing and some other cleanups
-#
-# Revision 1.10  2000/07/17 12:49:29  ivan
-# better error message if a package isn't linked to a customer (that shouldn't happen)
-#
-# Revision 1.9  1999/07/17 10:38:52  ivan
-# scott nelson <scott@ultimanet.com> noticed this mod_perl-triggered bug and
-# gave me a great bugreport at the last rhythmethod
-#
-# Revision 1.8  1999/02/09 09:22:57  ivan
-# visual and bugfixes
-#
-# Revision 1.7  1999/02/07 09:59:37  ivan
-# more mod_perl fixes, and bugfixes Peter Wemm sent via email
-#
-# Revision 1.6  1999/01/19 05:14:13  ivan
-# for mod_perl: no more top-level my() variables; use vars instead
-# also the last s/create/new/;
-#
-# Revision 1.5  1999/01/18 09:41:38  ivan
-# all $cgi->header calls now include ( '-expires' => 'now' ) for mod_perl
-# (good idea anyway)
-#
-# Revision 1.4  1999/01/18 09:22:33  ivan
-# changes to track email addresses for email invoicing
-#
-# Revision 1.3  1998/12/23 03:05:59  ivan
-# $cgi->keywords instead of $cgi->query_string
-#
-# Revision 1.2  1998/12/17 09:41:09  ivan
-# s/CGI::(Base|Request)/CGI.pm/;
-#
-
-use strict;
-use vars qw ( $cgi @cust_pkg $sortby $query );
-use CGI;
-use CGI::Carp qw(fatalsToBrowser);
-use FS::UID qw(cgisuidsetup);
-use FS::Record qw(qsearch qsearchs);
-use FS::CGI qw(header eidiot popurl);
-use FS::cust_pkg;
-use FS::pkg_svc;
-use FS::cust_svc;
-use FS::cust_main;
-
-$cgi = new CGI;
-&cgisuidsetup($cgi);
-
-($query) = $cgi->keywords;
-#this tree is a little bit redundant
-if ( $query eq 'pkgnum' ) {
-  $sortby=\*pkgnum_sort;
-  @cust_pkg=qsearch('cust_pkg',{});
-} elsif ( $query eq 'APKG_pkgnum' ) {
-  $sortby=\*pkgnum_sort;
-  @cust_pkg=();
-  #perhaps this should go in cust_pkg as a qsearch-like constructor?
-  my($cust_pkg);
-  foreach $cust_pkg (qsearch('cust_pkg',{})) {
-    my($flag)=0;
-    my($pkg_svc);
-    PKG_SVC: 
-    foreach $pkg_svc (qsearch('pkg_svc',{ 'pkgpart' => $cust_pkg->pkgpart })) {
-      if ( $pkg_svc->quantity 
-           > scalar(qsearch('cust_svc',{
-               'pkgnum' => $cust_pkg->pkgnum,
-               'svcpart' => $pkg_svc->svcpart,
-             }))
-         )
-      {
-        $flag=1;
-        last PKG_SVC;
-      }
-    }
-    push @cust_pkg, $cust_pkg if $flag;
-  }
-} else {
-  die "Empty QUERY_STRING!";
+
+my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {});
+
+my($query) = $cgi->keywords;
+
+my $orderby;
+my @where;
+my $cjoin = '';
+
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) {
+  $cjoin = "LEFT JOIN cust_main USING ( custnum )";
+  push @where,
+    "agentnum = $1";
 }
 
-if ( scalar(@cust_pkg) == 1 ) {
-  my($pkgnum)=$cust_pkg[0]->pkgnum;
-  print $cgi->redirect(popurl(2). "view/cust_pkg.cgi?$pkgnum");
-  exit;
-} elsif ( scalar(@cust_pkg) == 0 ) { #error
-  eidiot("No packages found");
+if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) {
+  $orderby = 'ORDER BY bill';
+
+  my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
+  push @where,
+    #"bill >= $beginning ",
+    #"bill <= $ending",
+    "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ",
+    "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending",
+    '( cancel IS NULL OR cancel = 0 )';
+
 } else {
-  my($total)=scalar(@cust_pkg);
-  print $cgi->header( '-expires' => 'now' ), header('Package Search Results',''), <<END;
-    $total matching packages found
-    <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0>
-      <TR>
-        <TH>Package #</TH>
-        <TH>Customer #</TH>
-        <TH>Contact name</TH>
-        <TH>Company</TH>
-      </TR>
-END
-
-  my(%saw,$cust_pkg);
-  foreach $cust_pkg (
-    sort $sortby grep(!$saw{$_->pkgnum}++, @cust_pkg)
+
+  if ( $cgi->param('magic') &&
+       $cgi->param('magic') =~ /^(active|suspended|cancell?ed)$/
   ) {
-    my($cust_main)=qsearchs('cust_main',{'custnum'=>$cust_pkg->custnum});
-    my($pkgnum,$custnum,$name,$company)=(
-      $cust_pkg->pkgnum,
-      $cust_pkg->custnum,
-      $cust_main ? $cust_main->last. ', '. $cust_main->first : '',
-      $cust_main ? $cust_main->company : '',
-    );
-    my $p = popurl(2);
-    print <<END;
-    <TR>
-      <TD><A HREF="${p}view/cust_pkg.cgi?$pkgnum"><FONT SIZE=-1>$pkgnum</FONT></A></TD>
-END
-    if ( $cust_main ) {
-      print <<END;
-      <TD><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$custnum</A></FONT></TD>
-      <TD><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$name</A></FONT></TD>
-      <TD><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$company</A></FONT></TD>
-    </TR>
-END
+
+    $orderby = 'ORDER BY pkgnum';
+
+    if ( $cgi->param('magic') eq 'active' ) {
+
+      #push @where,
+      #  '( susp IS NULL OR susp = 0 )',
+      #  '( cancel IS NULL OR cancel = 0)';
+      push @where, FS::cust_pkg->active_sql();
+
+    } elsif ( $cgi->param('magic') eq 'suspended' ) {
+
+      push @where,
+        'susp IS NOT NULL',
+        'susp != 0',
+        '( cancel IS NULL OR cancel = 0)';
+
+    } elsif ( $cgi->param('magic') =~ /^cancell?ed$/ ) {
+
+      push @where,
+        'cancel IS NOT NULL',
+        'cancel != 0';
+
     } else {
-      print <<END;
-      <TD COLSPAN=3>WARNING: couldn't find cust_main.custnum $custnum (cust_pkg.pkgnum $pkgnum)</TD>
-    </TR>
-END
+      die "guru meditation #420";
+    }
+
+    if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) {
+      push @where, "pkgpart = $1";
     }
+
+  } elsif ( $query eq 'pkgnum' ) {
+
+    $orderby = 'ORDER BY pkgnum';
+
+  } elsif ( $query eq 'APKG_pkgnum' ) {
+  
+    $orderby = 'ORDER BY pkgnum';
+  
+    push @where, '0 < (
+      SELECT count(*) FROM pkg_svc
+       WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
+         AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
+                                   WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
+                                     AND cust_svc.svcpart = pkg_svc.svcpart
+                                )
+    )';
+    
+  } else {
+    die "Empty or unknown QUERY_STRING!";
   }
- 
-  print <<END;
-    </TABLE>
-  </BODY>
-</HTML>
-END
-  exit;
 
 }
 
-sub pkgnum_sort {
-  $a->getfield('pkgnum') <=> $b->getfield('pkgnum');
+my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
+
+my $count_query = "SELECT COUNT(*) FROM cust_pkg $cjoin $extra_sql";
+
+my $sql_query = {
+  'table'     => 'cust_pkg',
+  'hashref'   => {},
+  'select'    => join(', ',
+                            'cust_pkg.*',
+                            'cust_main.custnum as cust_main_custnum',
+                            FS::UI::Web::cust_sql_fields(),
+                 ),
+  'extra_sql' => "$extra_sql $orderby",
+  'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ',
+                 #' LEFT JOIN part_pkg  USING ( pkgpart ) '
+};
+
+my $link = sub {
+  [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ];
+};
+
+my $clink = sub {
+  my $cust_pkg = shift;
+  $cust_pkg->cust_main_custnum
+    ? [ "${p}view/cust_main.cgi?", 'custnum' ] 
+    : '';
+};
+
+#if ( scalar(@cust_pkg) == 1 ) {
+#  print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum.
+#                       "#cust_pkg". $cust_pkg[0]->pkgnum );
+
+#    my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } );
+#    my $rowspan = scalar(@cust_svc) || 1;
+
+#    my $n2 = '';
+#    foreach my $cust_svc ( @cust_svc ) {
+#      my($label, $value, $svcdb) = $cust_svc->label;
+#      my $svcnum = $cust_svc->svcnum;
+#      my $sview = $p. "view";
+#      print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!,
+#            qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!;
+#      $n2="</TR><TR>";
+#    }
+
+sub time_or_blank {
+   my $column = shift;
+   return sub {
+     my $record = shift;
+     my $value = $record->get($column); #mmm closures
+     $value ? time2str('%b %d %Y', $value ) : '';
+   };
 }
 
+%><%=  include( 'elements/search.html',
+                  'title'       => 'Package Search Results', 
+                  'name'        => 'packages',
+                  'query'       => $sql_query,
+                  'count_query' => $count_query,
+                  #'redirect'    => $link,
+                  'header'      => [ '#',
+                                     'Package',
+                                     'Status',
+                                     'Freq.',
+                                     'Setup',
+                                     'Last bill',
+                                     'Next bill',
+                                     'Susp.',
+                                     'Expire',
+                                     'Cancel',
+                                     FS::UI::Web::cust_header(),
+                                     'Services',
+                                   ],
+                  'fields'      => [
+                    'pkgnum',
+                    sub { my $part_pkg = $part_pkg{shift->pkgpart};
+                          $part_pkg->pkg; # ' - '. $part_pkg->comment;
+                        },
+                    sub { ucfirst(shift->status); },
+                    sub { #shift->part_pkg->freq_pretty;
+                          my $part_pkg = $part_pkg{shift->pkgpart};
+                          $part_pkg->freq_pretty;
+                        },
+
+                    #sub { time2str('%b %d %Y', shift->setup); },
+                    #sub { time2str('%b %d %Y', shift->last_bill); },
+                    #sub { time2str('%b %d %Y', shift->bill); },
+                    #sub { time2str('%b %d %Y', shift->susp); },
+                    #sub { time2str('%b %d %Y', shift->expire); },
+                    #sub { time2str('%b %d %Y', shift->get('cancel')); },
+                    ( map { time_or_blank($_) }
+                          qw( setup last_bill bill susp expire cancel ) ),
+
+                    \&FS::UI::Web::cust_fields,
+                    #sub { '<table border=0 cellspacing=0 cellpadding=0 STYLE="border:none">'.
+                    #      join('', map { '<tr><td align="right" style="border:none">'. $_->[0].
+                    #                     ':</td><td style="border:none">'. $_->[1]. '</td></tr>' }
+                    #                   shift->labels
+                    #          ).
+                    #      '</table>';
+                    #    },
+                    sub {
+                          [ map {
+                                  [ 
+                                    { 'data' => $_->[0]. ':',
+                                      'align'=> 'right',
+                                    },
+                                    { 'data' => $_->[1],
+                                      'align'=> 'left',
+                                      'link' => $p. 'view/' .
+                                                $_->[2]. '.cgi?'. $_->[3],
+                                    },
+                                  ];
+                                } shift->labels
+                          ];
+                        },
+                  ],
+                  'color' => [
+                    '',
+                    '',
+                    sub { shift->statuscolor; },
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    ( map { '' } FS::UI::Web::cust_header() ),
+                    '',
+                  ],
+                  'style' => [ '', '', 'b' ],
+                  'size'  => [ '', '', '-1', ],
+                  'align' => 'rlclrrrrrr',
+                  'links' => [
+                    $link,
+                    $link,
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    '',
+                    ( map { $clink } FS::UI::Web::cust_header() ),
+                    '',
+                  ],
+              )
 %>