- better description of payment search

- some clarification of services/packages vs. service/package
  definitions in browse/part_{svc,pkg}.cgi
- package definition report by # of active customer packages (with links
  to customer package listing)

diff --git a/httemplate/browse/part_pkg.cgi b/httemplate/browse/part_pkg.cgi
index 58422c6..7b9436c 100755 (executable)
--- a/httemplate/browse/part_pkg.cgi
+++ b/httemplate/browse/part_pkg.cgi
@@ -11,15 +11,35 @@ if ( $cgi->param('showdisabled') ) {
 my @part_pkg = qsearch('part_pkg', \%search );
 my $total = scalar(@part_pkg);
 
 my @part_pkg = qsearch('part_pkg', \%search );
 my $total = scalar(@part_pkg);
 

+my $sortby;
+my %num_active_cust_pkg;
+if ( $cgi->param('active') ) {
+  my $active_sth = dbh->prepare(
+    'SELECT COUNT(*) FROM cust_pkg WHERE pkgpart = ?'.
+    ' AND ( cancel IS NULL OR cancel = 0 )'.
+    ' AND ( susp IS NULL OR susp = 0 )'
+  ) or die dbh->errstr;
+  foreach my $part_pkg ( @part_pkg ) {
+    $active_sth->execute($part_pkg->pkgpart) or die $active_sth->errstr;
+    $num_active_cust_pkg{$part_pkg->pkgpart} =
+      $active_sth->fetchrow_arrayref->[0];
+  }
+  $sortby = \*active_cust_pkg_sort;
+} else {
+  $sortby = \*pkgpart_sort;
+}
+

 %>
 <%= header("Package Definition Listing",menubar( 'Main Menu' => $p )) %>
 %>
 <%= header("Package Definition Listing",menubar( 'Main Menu' => $p )) %>

-One or more services are grouped together into a package and given pricing
-information. Customers purchase packages rather than purchase services
-directly.<BR><BR>
-<A HREF="<%= $p %>edit/part_pkg.cgi"><I>Add a new package definition</I></A>
-<BR><BR>
+<% unless ( $cgi->param('active') ) { %>
+  One or more service definitions are grouped together into a package 
+  definition and given pricing information.  Customers purchase packages
+  rather than purchase services directly.<BR><BR>
+  <A HREF="<%= $p %>edit/part_pkg.cgi"><I>Add a new package definition</I></A>
+  <BR><BR>
+<% } %>

 
 

-<%= $total %> packages
+<%= $total %> package definitions

 <%
 if ( $cgi->param('showdisabled') ) {
   $cgi->param('showdisabled', 0);
 <%
 if ( $cgi->param('showdisabled') ) {
   $cgi->param('showdisabled', 0);


@@ -34,6 +54,10 @@ print &table(), <<END;
       <TR>
         <TH COLSPAN=$colspan>Package</TH>
         <TH>Comment</TH>
       <TR>
         <TH COLSPAN=$colspan>Package</TH>
         <TH>Comment</TH>

+END
+print '        <TH><FONT SIZE=-1>Customer<BR>packages</FONT></TH>'
+  if $cgi->param('active');
+print <<END;

         <TH><FONT SIZE=-1>Freq.</FONT></TH>
         <TH><FONT SIZE=-1>Plan</FONT></TH>
         <TH><FONT SIZE=-1>Data</FONT></TH>
         <TH><FONT SIZE=-1>Freq.</FONT></TH>
         <TH><FONT SIZE=-1>Plan</FONT></TH>
         <TH><FONT SIZE=-1>Data</FONT></TH>


@@ -42,9 +66,7 @@ print &table(), <<END;
       </TR>
 END
 
       </TR>
 END
 

-foreach my $part_pkg ( sort { 
-  $a->getfield('pkgpart') <=> $b->getfield('pkgpart')
-} @part_pkg ) {
+foreach my $part_pkg ( sort $sortby @part_pkg ) {

   my($hashref)=$part_pkg->hashref;
   my(@pkg_svc)=grep $_->getfield('quantity'),
     qsearch('pkg_svc',{'pkgpart'=> $hashref->{pkgpart} });
   my($hashref)=$part_pkg->hashref;
   my(@pkg_svc)=grep $_->getfield('quantity'),
     qsearch('pkg_svc',{'pkgpart'=> $hashref->{pkgpart} });


@@ -73,6 +95,16 @@ END
   print <<END;
         <TD ROWSPAN=$rowspan><A HREF="${p}edit/part_pkg.cgi?$hashref->{pkgpart}">$hashref->{pkg}</A></TD>
         <TD ROWSPAN=$rowspan>$hashref->{comment}</TD>
   print <<END;
         <TD ROWSPAN=$rowspan><A HREF="${p}edit/part_pkg.cgi?$hashref->{pkgpart}">$hashref->{pkg}</A></TD>
         <TD ROWSPAN=$rowspan>$hashref->{comment}</TD>

+END
+  if ( $cgi->param('active') ) {
+    print "        <TD ROWSPAN=$rowspan>";
+    print '<FONT COLOR="#00CC00"><B>'.
+          $num_active_cust_pkg{$hashref->{'pkgpart'}}.
+          qq!</B></FONT>&nbsp;<A HREF="${p}search/cust_pkg.cgi?magic=active;pkgpart=$hashref->{pkgpart}">active</A>!;
+    # suspended/cancelled
+    print '</TD>';
+  }
+  print <<END;

         <TD ROWSPAN=$rowspan>$hashref->{freq}</TD>
         <TD ROWSPAN=$rowspan>$hashref->{plan}</TD>
         <TD ROWSPAN=$rowspan>$plandata</TD>
         <TD ROWSPAN=$rowspan>$hashref->{freq}</TD>
         <TD ROWSPAN=$rowspan>$hashref->{plan}</TD>
         <TD ROWSPAN=$rowspan>$plandata</TD>


@@ -99,4 +131,14 @@ print <<END;
   </BODY>
 </HTML>
 END
   </BODY>
 </HTML>
 END

+
+
+sub pkgpart_sort {
+  $a->pkgpart <=> $b->pkgpart;
+}
+
+sub active_cust_pkg_sort {
+  $num_active_cust_pkg{$b->pkgpart} <=> $num_active_cust_pkg{$a->pkgpart};
+}
+

 %>
 %>

diff --git a/httemplate/browse/part_svc.cgi b/httemplate/browse/part_svc.cgi
index ee7a262..7c83924 100755 (executable)
--- a/httemplate/browse/part_svc.cgi
+++ b/httemplate/browse/part_svc.cgi
@@ -23,7 +23,7 @@ function part_export_areyousure(href) {
 }
 </SCRIPT>
 
 }
 </SCRIPT>
 

-    Services are items you offer to your customers.<BR><BR>
+    Service definitions are the templates for items you offer to your customers.<BR><BR>

 
 <FORM METHOD="POST" ACTION="<%= $p %>edit/part_svc.cgi">
 <A HREF="<%= $p %>edit/part_svc.cgi"><I>Add a new service definition</I></A><% if ( @part_svc ) { %>&nbsp;or&nbsp;<SELECT NAME="clone"><OPTION></OPTION>
 
 <FORM METHOD="POST" ACTION="<%= $p %>edit/part_svc.cgi">
 <A HREF="<%= $p %>edit/part_svc.cgi"><I>Add a new service definition</I></A><% if ( @part_svc ) { %>&nbsp;or&nbsp;<SELECT NAME="clone"><OPTION></OPTION>


@@ -34,7 +34,7 @@ function part_export_areyousure(href) {
 <% } %>
 </FORM><BR>
 
 <% } %>
 </FORM><BR>
 

-<%= $total %> services
+<%= $total %> service definitions

 <%= $cgi->param('showdisabled')
       ? do { $cgi->param('showdisabled', 0);
              '( <a href="'. $cgi->self_url. '">hide disabled services</a> )'; }
 <%= $cgi->param('showdisabled')
       ? do { $cgi->param('showdisabled', 0);
              '( <a href="'. $cgi->self_url. '">hide disabled services</a> )'; }

diff --git a/httemplate/index.html b/httemplate/index.html
index 7354b1c..779a42e 100644 (file)
--- a/httemplate/index.html
+++ b/httemplate/index.html
@@ -67,7 +67,7 @@
               <LI>120 day open invoices (<A HREF="search/cust_bill.cgi?OPEN120_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN120_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN120_custnum">by customer number</A>)
               <LI>all invoices (<A HREF="search/cust_bill.cgi?invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?date">by date</A>) (<A HREF="search/cust_bill.cgi?custnum">by customer number</A>)
             </UL>
               <LI>120 day open invoices (<A HREF="search/cust_bill.cgi?OPEN120_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN120_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN120_custnum">by customer number</A>)
               <LI>all invoices (<A HREF="search/cust_bill.cgi?invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?date">by date</A>) (<A HREF="search/cust_bill.cgi?custnum">by customer number</A>)
             </UL>

-      <A HREF="search/report_cust_pay.html">Payments</A>
+      <A HREF="search/report_cust_pay.html">Payment Report (by type and/or date range)</A>

       <BR><BR>Financial reports
             <UL>
               <LI> <A HREF="search/report_receivables.cgi">current receivables</A>
       <BR><BR>Financial reports
             <UL>
               <LI> <A HREF="search/report_receivables.cgi">current receivables</A>


@@ -125,7 +125,8 @@
         <LI><A HREF="search/cust_pkg.cgi?APKG_pkgnum">packages with unconfigured services (by package number)</A>
         <LI><A HREF="search/cust_pkg.html">packages (by next bill date range)</A>
       </UL>
         <LI><A HREF="search/cust_pkg.cgi?APKG_pkgnum">packages with unconfigured services (by package number)</A>
         <LI><A HREF="search/cust_pkg.html">packages (by next bill date range)</A>
       </UL>

-      Invoices
+      <A HREF="browse/part_pkg.cgi?active=1">Package definitions (by number of active packages)</A>
+      <BR><BR>Invoices

       <UL>
         <LI><a href="search/cust_bill_event.html">Invoice event errors (failed credit cards)</a>
         <LI>open invoices (<A HREF="search/cust_bill.cgi?OPEN_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN_custnum">by customer number</A>)
       <UL>
         <LI><a href="search/cust_bill_event.html">Invoice event errors (failed credit cards)</a>
         <LI>open invoices (<A HREF="search/cust_bill.cgi?OPEN_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN_custnum">by customer number</A>)


@@ -135,7 +136,7 @@
         <LI>120 day open invoices (<A HREF="search/cust_bill.cgi?OPEN120_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN120_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN120_custnum">by customer number</A>)
         <LI>all invoices (<A HREF="search/cust_bill.cgi?invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?date">by date</A>) (<A HREF="search/cust_bill.cgi?custnum">by customer number</A>)
       </UL>
         <LI>120 day open invoices (<A HREF="search/cust_bill.cgi?OPEN120_invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?OPEN120_date">by date</A>) (<A HREF="search/cust_bill.cgi?OPEN120_custnum">by customer number</A>)
         <LI>all invoices (<A HREF="search/cust_bill.cgi?invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?date">by date</A>) (<A HREF="search/cust_bill.cgi?custnum">by customer number</A>)
       </UL>

-    <A HREF="search/report_cust_pay.html">Payments</A>
+    <A HREF="search/report_cust_pay.html">Payment Report (by type and/or date range)</A>

     <BR><BR>Financial reports
             <UL>
               <LI> <A HREF="search/report_receivables.cgi">current receivables</A>
     <BR><BR>Financial reports
             <UL>
               <LI> <A HREF="search/report_receivables.cgi">current receivables</A>

diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi
index 603b565..8b2fd0c 100755 (executable)
--- a/httemplate/search/cust_pkg.cgi
+++ b/httemplate/search/cust_pkg.cgi
@@ -46,7 +46,19 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) {
 } else {
 
   my $qual = '';
 } else {
 
   my $qual = '';

-  if ( $query eq 'pkgnum' ) {
+  if ( $cgi->param('magic') && $cgi->param('magic') eq 'active' ) {
+
+    $qual = 'WHERE ( susp IS NULL OR susp = 0 )'.
+            ' AND ( cancel IS NULL OR cancel = 0)';
+
+    $sortby = \*pkgnum_sort;
+
+    if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) {
+      $qual .= " AND pkgpart = $1";
+    }
+
+  } elsif ( $query eq 'pkgnum' ) {
+

     $sortby=\*pkgnum_sort;
 
   } elsif ( $query eq 'SUSP_pkgnum' ) {
     $sortby=\*pkgnum_sort;
 
   } elsif ( $query eq 'SUSP_pkgnum' ) {