unprovisioned services by type report, RT10255

3 files changed, 129 insertions, 0 deletions

diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index ec176c972..c4422a017 100644
--- 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
index 000000000..fe4d46bf7
--- /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
index 000000000..f85e4fb19
--- /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>