From a989f40712bcbef325a566802118432063315240 Mon Sep 17 00:00:00 2001
From: Ivan Kohler <ivan@freeside.biz>
Date: Thu, 27 Apr 2017 13:41:58 -0700
Subject: [PATCH] customer referral report, RT#75757

---
 FS/FS/cust_main/Search.pm        | 23 +++++++++++++++++++++++
 httemplate/elements/menu.html    |  1 +
 httemplate/search/cust_main.html |  9 ++++++++-
 3 files changed, 32 insertions(+), 1 deletion(-)

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index 4473aeda2..0eb00202b 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -866,6 +866,15 @@ sub search {
     if $params->{'no_tax'};
 
   ##
+  # with referrals
+  ##
+  if ( $params->{'with_referrals'} ) {
+    push @where,
+      ' EXISTS ( SELECT 1 FROM cust_main AS referred_cust_main
+                   WHERE cust_main.custnum = referred_cust_main.referral_custnum )';
+  }
+
+  ##
   # dates
   ##
 
@@ -1101,6 +1110,20 @@ sub search {
 
   }
 
+  if ( $params->{'with_referrals'} ) {
+
+    #XXX next: num for each customer status
+     
+    push @select,
+      '( SELECT COUNT(*) FROM cust_main AS referred_cust_main
+           WHERE cust_main.custnum = referred_cust_main.referral_custnum
+       ) AS num_referrals';
+
+    unshift @extra_headers, 'Referrals';
+    unshift @extra_fields, 'num_referrals';
+
+  }
+
   my $select = join(', ', @select);
 
   my $sql_query = {
diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index 752c312c1..58a7d5783 100644
--- a/httemplate/elements/menu.html
+++ b/httemplate/elements/menu.html
@@ -129,6 +129,7 @@ $report_customers_lists{'by active trouble tickets'} = [ $fsurl. 'search/cust_ma
   if $conf->config('ticket_system');
 $report_customers_lists{'with USPS-unvalidated addresses'} = [ $fsurl. 'search/cust_main.cgi?browse=uspsunvalid', '' ]
   if $conf->config('usps_webtools-userid') && $conf->config('usps_webtools-password');
+$report_customers_lists{'with referrals'} = [ $fsurl. 'search/cust_main.html?with_referrals=1' ];
 
 tie my %report_customers, 'Tie::IxHash';
 $report_customers{'List customers'} = [ \%report_customers_lists, 'List customers' ]
diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html
index b0ce31ae9..41309fdd1 100755
--- a/httemplate/search/cust_main.html
+++ b/httemplate/search/cust_main.html
@@ -27,7 +27,11 @@
                                                       $cgi->param('cust_fields')
                                                                    )
                                      ),
-                                     map '', @extra_fields
+                                     map  { $_ eq 'num_referrals'
+                                              ? $referral_link
+                                              : ''
+                                          }
+                                       @extra_fields
                                    ],
 &>
 <%init>
@@ -51,6 +55,7 @@ my @scalars = qw (
   all_tags
   all_pkg_classnums
   any_pkg_status
+  with_referrals
 );
 
 for my $param ( @scalars ) {
@@ -111,6 +116,8 @@ my @extra_fields  = @{ delete($sql_query->{'extra_fields'})  };
 
 my $link = [ "${p}view/cust_main.cgi?", 'custnum' ];
 
+my $referral_link = [ "${p}search/cust_main.cgi?referral_custnum=", 'custnum' ];
+
 ###
 # email links
 ###
-- 
2.11.0