lilunixbtch: trying to pull accounts based on next billdate

tofu_beast420: hmm a report ordered by next bill date?
tofu_beast420: i don't know how you'd do that per _customer_ since a customer could have lots of packages, but you could do a per-package report maybe?

diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi
index 54e02ce..dee2fbd 100755 (executable)
--- a/httemplate/search/cust_pkg.cgi
+++ b/httemplate/search/cust_pkg.cgi
@@ -13,64 +13,93 @@ $limit .= " OFFSET $offset" if $offset;
 
 my $total;
 
-my $unconf = '';
 my($query) = $cgi->keywords;
 my $sortby;
-if ( $query eq 'pkgnum' ) {
-  $sortby=\*pkgnum_sort;
-
-} elsif ( $query eq 'APKG_pkgnum' ) {
-
-  $sortby=\*pkgnum_sort;
-
-  $unconf = "
-    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
-                                   )
-      )
-  ";
-
-  #@cust_pkg=();
-  ##perhaps this should go in cust_pkg as a qsearch-like constructor?
-  #my($cust_pkg);
-  #foreach $cust_pkg (
-  #  qsearch('cust_pkg',{}, '', "ORDER BY pkgnum $limit" )
-  #) {
-  #  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;
-  #}
+my @cust_pkg;
+
+if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) {
+  $sortby=\*bill_sort;
+  my($beginning, $ending) = (0, 0);
+  my $range = '';
+  if ( $cgi->param('beginning')
+       && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) {
+    my $beginning = str2time($1);
+    $range = " WHERE bill >= $beginning ";
+  } elsif ( $cgi->param('ending')
+            && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) {
+    $ending = str2time($1) + 86400;
+    $range = ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending ";
+  }
+
+  #false laziness with below
+  my $statement = "SELECT COUNT(*) FROM cust_pkg $range";
+  my $sth = dbh->prepare($statement)
+    or die dbh->errstr. " doing $statement";
+  $sth->execute or die "Error executing \"$statement\": ". $sth->errstr;
   
-} else {
-  die "Empty QUERY_STRING!";
-}
+  $total = $sth->fetchrow_arrayref->[0];
+  
+  @cust_pkg = qsearch('cust_pkg',{}, '', " $range ORDER BY bill $limit" );
 
-my $statement = "SELECT COUNT(*) FROM cust_pkg $unconf";
-my $sth = dbh->prepare($statement)
-  or die dbh->errstr. " doing $statement";
-$sth->execute or die "Error executing \"$statement\": ". $sth->errstr;
+} else {
 
-$total = $sth->fetchrow_arrayref->[0];
+  my $unconf = '';
+  if ( $query eq 'pkgnum' ) {
+    $sortby=\*pkgnum_sort;
 
-my @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" );
+  } elsif ( $query eq 'APKG_pkgnum' ) {
+  
+    $sortby=\*pkgnum_sort;
+  
+    $unconf = "
+      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
+                                     )
+        )
+    ";
+  
+    #@cust_pkg=();
+    ##perhaps this should go in cust_pkg as a qsearch-like constructor?
+    #my($cust_pkg);
+    #foreach $cust_pkg (
+    #  qsearch('cust_pkg',{}, '', "ORDER BY pkgnum $limit" )
+    #) {
+    #  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 $statement = "SELECT COUNT(*) FROM cust_pkg $unconf";
+  my $sth = dbh->prepare($statement)
+    or die dbh->errstr. " doing $statement";
+  $sth->execute or die "Error executing \"$statement\": ". $sth->errstr;
+  
+  $total = $sth->fetchrow_arrayref->[0];
+  
+  @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" );
 
+}
 
 if ( scalar(@cust_pkg) == 1 ) {
   my($pkgnum)=$cust_pkg[0]->pkgnum;
@@ -243,4 +272,8 @@ sub pkgnum_sort {
   $a->getfield('pkgnum') <=> $b->getfield('pkgnum');
 }
 
+sub bill_sort {
+  $a->getfield('bill') <=> $b->getfield('bill');
+}
+
 %>

diff --git a/httemplate/search/cust_pkg.html b/httemplate/search/cust_pkg.html
new file mode 100755 (executable)
index 0000000..c69e8d7
--- /dev/null
+++ b/httemplate/search/cust_pkg.html
@@ -0,0 +1,24 @@
+<HTML>
+  <HEAD>
+    <TITLE>Packages</TITLE>
+  </HEAD>
+  <BODY>
+    <CENTER>
+      <H1>Packages</H1>
+    </CENTER>
+    <HR>
+    <FORM ACTION="cust_bill_event.cgi" METHOD="post">
+    <INPUT TYPE="hidden" NAME="magic" VALUE="bill">
+      Return <B>packages</B> with next bill date: 
+      from <INPUT TYPE="text" NAME="beginning"> <i>m/d/y</i>
+      to <INPUT TYPE="text" NAME="ending"> <i>m/d/y</i>
+
+      <P><INPUT TYPE="submit" VALUE="Get Report">
+
+    </FORM>
+
+  <HR>
+
+  </BODY>
+</HTML>
+