mvhub-dev team mailing list archive

[HsienWen Hsu <hsienwen2007@xxxxxxxxx>]

HsienWen Hsu has proposed merging lp:~hsienwen2007/mvhub/report_4_reminder_email into lp:mvhub.

Requested reviews:
  mvhub-dev (mvhub-dev)
Related bugs:
  #527291 sub system to mange manual calls/reminders v1
  https://bugs.launchpad.net/bugs/527291


Create the first version of report to make phone calls easier
-- 
https://code.launchpad.net/~hsienwen2007/mvhub/report_4_reminder_email/+merge/22246
Your team mvhub-dev is subscribed to branch lp:mvhub.

=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl	2010-01-04 08:28:17 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl	2010-03-26 17:25:32 +0000
@@ -133,6 +133,44 @@
             GROUP BY agency_name ORDER BY "Oldest Record (yyyy-mm-dd)", agency_name
         /
     },
+    {   title => 'Agencies not responding to email or with bad contact email',
+        comment =>
+            'If the agency or program contact does not update their records after 4 emails we pick up the phone',
+        sql => q/
+            SELECT 
+                a.agency_name, 
+                a.contact_phone,
+                a.contact_first_name || ' '
+                || a.contact_last_name AS contact,
+                a.contact_email,
+                ' MAIN AGENCY RECORD' AS program_name
+            FROM 
+                agency a
+            WHERE
+                a.contact_email like '%bad@xxxxxxxxxxxxxx%'  
+                OR a.contact_email = 'none'
+                OR a.contact_email = 'unknown'
+                OR a.reminders_sent >= 4
+            UNION 
+            SELECT
+                a.agency_name, 
+                p.contact_phone,
+                p.contact_first_name || ' '
+                || p.contact_last_name AS contact,
+                p.contact_email,
+                p.program_name 
+            FROM 
+                program p
+            JOIN agency a ON a.agency_id = p.agency_id
+            WHERE
+                p.contact_email like '%bad@xxxxxxxxxxxxxx%'  
+                OR p.contact_email = 'none'
+                OR p.contact_email = 'unknown'
+                OR p.reminders_sent >=4
+            ORDER BY
+                agency_name,program_name
+            /
+    },
     {   title => 'Number of Programs under each Category',
 
    # We use a left join below so that we also see which categories are unused.