add unlinked mail forward (svc_forward) report

3 files changed, 27 insertions, 10 deletions

diff --git a/httemplate/index.html b/httemplate/index.html
index 167cd6f7b..067fec5d1 100644
--- a/httemplate/index.html
+++ b/httemplate/index.html
@@ -173,7 +173,7 @@
       Auditing pre-Freeside services with no customer record
       <UL>
         <LI>unlinked accounts (<A HREF="search/svc_acct.cgi?UN_svcnum">by service number</A>) (<A HREF="search/svc_acct.cgi?UN_username">by username</A>) (<A HREF="search/svc_acct.cgi?UN_uid">by uid</A>)
-<!--        <LI>unlinked mail forwards (<A HREF="search/svc_forward.cgi?UN_svcnum">by service number</A>) (by ?)) -->
+        <LI>unlinked mail forwards (<A HREF="search/svc_forward.cgi?UN_svcnum">by service number</A>)
         <LI>unlinked domains (<A HREF="search/svc_domain.cgi?UN_svcnum">by service number</A>) (<A HREF="search/svc_domain.cgi?UN_domain">by domain</A>)
         <LI>unlinked externals (<A HREF="search/svc_external.cgi?UN_svcnum">by service number</A>) (<A HREF="search/svc_external.cgi?UN_id">by id</A>)
       </UL>
diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi
index d4565aefc..b14591958 100755
--- a/httemplate/search/svc_acct.cgi
+++ b/httemplate/search/svc_acct.cgi
@@ -94,10 +94,10 @@ my $sql_query = {
                     FS::UI::Web::cust_sql_fields(),
                   ),
   'extra_sql' => "$extra_sql $orderby",
-  'addl_from' => 'LEFT JOIN cust_svc  USING ( svcnum  ) '.
-                 'LEFT JOIN part_svc  USING ( svcpart ) '.
-                 'LEFT JOIN cust_pkg  USING ( pkgnum  ) '.
-                 'LEFT JOIN cust_main USING ( custnum ) ',
+  'addl_from' => ' LEFT JOIN cust_svc  USING ( svcnum  ) '.
+                 ' LEFT JOIN part_svc  USING ( svcpart ) '.
+                 ' LEFT JOIN cust_pkg  USING ( pkgnum  ) '.
+                 ' LEFT JOIN cust_main USING ( custnum ) ',
 };
 
 my $link      = [ "${p}view/svc_acct.cgi?",   'svcnum'  ];
diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi
index 330c0ee83..a204e345f 100755
--- a/httemplate/search/svc_forward.cgi
+++ b/httemplate/search/svc_forward.cgi
@@ -4,14 +4,30 @@ my $conf = new FS::Conf;
 
 my($query)=$cgi->keywords;
 $query ||= ''; #to avoid use of unitialized value errors
+
+
 my $orderby;
+
+my $cjoin = '';
+my @extra_sql = ();
+if ( $query =~ /^UN_(.*)$/ ) {
+  $query = $1;
+  $cjoin = 'LEFT JOIN cust_svc USING ( svcnum )';
+  push @extra_sql, 'pkgnum IS NULL';
+}
+
 if ( $query eq 'svcnum' ) {
   $orderby = 'ORDER BY svcnum';
 } else {
   eidiot('unimplemented');
 }
 
-my $count_query = 'SELECT COUNT(*) FROM svc_forward';
+my $extra_sql = 
+  scalar(@extra_sql)
+    ? ' WHERE '. join(' AND ', @extra_sql )
+    : '';
+
+my $count_query = "SELECT COUNT(*) FROM svc_forward $cjoin $extra_sql";
 my $sql_query = {
   'table'     => 'svc_forward',
   'hashref'   => {},
@@ -20,10 +36,11 @@ my $sql_query = {
                     'cust_main.custnum',
                     FS::UI::Web::cust_sql_fields(),
                  ),
-  'extra_sql' => $orderby,
-  'addl_from' => 'LEFT JOIN cust_svc  USING ( svcnum  )'.
-                 'LEFT JOIN cust_pkg  USING ( pkgnum  )'.
-                 'LEFT JOIN cust_main USING ( custnum )',
+  'extra_sql' => "$extra_sql $orderby",
+  'addl_from' => ' LEFT JOIN cust_svc  USING ( svcnum  ) '.
+                 ' LEFT JOIN part_svc  USING ( svcpart ) '.
+                 ' LEFT JOIN cust_pkg  USING ( pkgnum  ) '.
+                 ' LEFT JOIN cust_main USING ( custnum ) ',
 };
 
 #        <TH>Service #<BR><FONT SIZE=-1>(click to view forward)</FONT></TH>