mvhub-dev team mailing list archive

[noreply@xxxxxxxxxxxxx]

Merge authors:
  HsienWen Hsu (hsienwen2007)
Related merge proposals:
  https://code.launchpad.net/~hsienwen2007/mvhub/report_4_reminder_email/+merge/22246
  proposed by: HsienWen Hsu (hsienwen2007)
  review: Approve - Dan MacNeil (omacneil)
------------------------------------------------------------
revno: 355 [merge]
committer: Hsienwen Hsu <hsienwen2007@xxxxxxxxx>
branch nick: trunk
timestamp: Fri 2010-04-02 11:18:39 -0400
message:
  merge report_4_reminder_email
modified:
  app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl


--
lp:mvhub
https://code.launchpad.net/~mvhub-dev/mvhub/trunk

Your team mvhub-dev is subscribed to branch lp:mvhub.
To unsubscribe from this branch go to https://code.launchpad.net/~mvhub-dev/mvhub/trunk/+edit-subscription

=== 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 16:42:55 +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.