unprovisioned services by type report, RT10255

diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index ec176c9..c4422a0 100644 (file)
--- a/httemplate/elements/menu.html
+++ b/httemplate/elements/menu.html
@@ -130,6 +130,12 @@ if ( $curuser->access_right('Configuration') ) {
   $report_services{'Service definitions'} =  [ $fsurl.'browse/part_svc.cgi?orderby=active', 'Service definitions by number of active packages' ];
   $report_services{'separator'} =  '';
 }
+
+if ( $curuser->access_right('List services') ) {
+  $report_services{'Unprovisioned services'} =  [ $fsurl.'search/report_unprovisioned_services.html', 'Unprovisioned services' ];
+  $report_services{'separator2'} =  '';
+}
+
 foreach my $svcdb ( FS::part_svc->svc_tables() ) {
 
   my $name =        "FS::$svcdb"->table_info->{'name_plural'}

diff --git a/httemplate/search/report_unprovisioned_services.html b/httemplate/search/report_unprovisioned_services.html
new file mode 100755 (executable)
index 0000000..fe4d46b
--- /dev/null
+++ b/httemplate/search/report_unprovisioned_services.html
@@ -0,0 +1,32 @@
+<% include('/elements/header.html', 'Unprovisioned Services Report' ) %>
+
+<FORM ACTION="unprovisioned_services.html" METHOD="GET">
+
+  <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+    <TR>
+      <TH CLASS="background" COLSPAN=2 ALIGN="left"><FONT SIZE="+1">Search options</FONT></TH>
+    </TR>
+
+    <% include( '/elements/tr-select-part_svc.html',
+                   'id' => 'svcpart',
+                   'field' => 'svcpart',
+                   'label' => 'Services',
+                   'multiple' => 1,
+             )
+    %>
+    
+  </TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="Get Report">
+
+</FORM>
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('List services');
+
+</%init>

diff --git a/httemplate/search/unprovisioned_services.html b/httemplate/search/unprovisioned_services.html
new file mode 100644 (file)
index 0000000..f85e4fb
--- /dev/null
+++ b/httemplate/search/unprovisioned_services.html
@@ -0,0 +1,91 @@
+<% include( 'elements/search.html',
+              'title'         => 'Unprovisioned Service Search Results',
+              'name' => 'packages with unprovisioned services',
+              'query'         => {
+                                   'table'   => 'cust_pkg',
+                                   'hashref' => {},
+                                   'select'  => join(', ',
+                                      'cust_pkg.*',
+                                      'pkg_svc.*',
+
+                                      # everything fails gloriously otherwise
+                                      'cust_pkg.custnum as cust_main_custnum', 
+
+                                       FS::UI::Web::cust_sql_fields(),
+                                   ),
+                                   'extra_sql' => $search,
+                                   'addl_from' => $addl_from,
+                                 },
+              'count_query'   => $count_query,
+             'header'          => [ 'Package',
+                                    'Unprovisioned Services',
+                                   FS::UI::Web::cust_header(),
+                                  ],
+             'fields'   => [ sub {
+                               my $cust_pkg = shift;
+                               $cust_pkg->pkg_label;
+                             },
+                             sub {
+                               my $cust_pkg = shift;
+                               my @available_part_svc = $cust_pkg->available_part_svc;
+                               my $out = '';
+                               foreach my $part_svc ( @available_part_svc ) {
+                                   $out .= $part_svc->svc . ' (' 
+                                           . $part_svc->num_avail . ')<BR>'
+                                       if grep{ $part_svc->svcpart eq $_ } @svcpart;
+                               }
+                               $out;
+                             },
+                             \&FS::UI::Web::cust_fields,
+                           ],
+              'align'    => 'll'.FS::UI::Web::cust_aligns(),
+             'links'    => [
+                             '',       
+                             '',
+                             ( map { $_ ne 'Cust. Status' ? $link_cust : '' }
+                                 FS::UI::Web::cust_header()
+                             ),
+                           ],
+              'color' =>    [ '',
+                             '',
+                              FS::UI::Web::cust_colors(),
+                           ],
+              'style' =>    [ '',
+                             '',
+                              FS::UI::Web::cust_styles(),
+                           ],
+      )
+%>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('List services');
+
+my $svcpart_limit = "";
+my @svcpart = $cgi->param('svcpart');
+foreach my $svcpart ( @svcpart ) {
+    die 'invalid svcpart' if $svcpart !~ /^\d+$/;
+}
+$svcpart_limit = " and pkg_svc.svcpart in (". join(',',@svcpart) . ")"
+    if scalar(@svcpart);
+
+my $search = " where cust_pkg.cancel is null and pkg_svc.quantity > 0 and "
+           . " pkg_svc.quantity > (select count(1) from cust_svc where "
+           . " cust_svc.pkgnum = cust_pkg.pkgnum and "
+           . " cust_svc.svcpart = pkg_svc.svcpart) $svcpart_limit";
+
+my $addl_from = " join pkg_svc using (pkgpart) join cust_main using (custnum) ";
+
+# this was very painful to derive but it appears correct
+#select cust_pkg.custnum,cust_pkg.pkgpart,cust_pkg.pkgnum, pkg_svc.svcpart from cust_pkg join
+#pkg_svc using (pkgpart) where cancel is null and pkg_svc.quantity > 0 and pkg_svc.quantity >
+#(select count(1) from cust_svc where cust_svc.pkgnum = cust_pkg.pkgnum and cust_svc.svcpart =
+#pkg_svc.svcpart) order by pkgnum, svcpart;
+
+my $count_query = "select count(*) from cust_pkg $addl_from $search";
+
+my $link_cust = sub {
+  return [ "${p}view/cust_main.cgi?", 'custnum' ];
+};
+
+</%init>