mvhub-dev team mailing list archive

[Priya Ravindran <pthirumal@xxxxxxxxxx>]

Priya Ravindran has proposed merging lp:~priya/mvhub/move_to_sql_lib_agencyaccount into lp:mvhub.

Requested reviews:
  mvhub-dev (mvhub-dev)


Query from agency_account.pm moved into sql_select.lib

-- 
https://code.launchpad.net/~priya/mvhub/move_to_sql_lib_agencyaccount/+merge/22161
Your team mvhub-dev is subscribed to branch lp:mvhub.

=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib	2010-03-17 14:09:50 +0000
+++ app-mvhub/conf/sql_select.lib	2010-03-25 18:20:35 +0000
@@ -36,6 +36,20 @@
   p.program_id = ?
 AND 
   a.agency_id = p.agency_id
+
+[AGENCY_X_AGENCY_ID]
+SELECT agency_name, password 
+FROM 
+    agency 
+WHERE agency_id IN (
+			  SELECT agency_id 
+			  FROM agency 
+			  WHERE contact_email = ? 
+			UNION
+			  SELECT agency_id  
+			  FROM program 
+			  WHERE contact_email = ?
+        	    )
   
 [AGENCY_X_ALL]
 SELECT 
@@ -43,6 +57,7 @@
   FROM 
        agency
 
+           
 [AGENCY_X_ALL_PUBLIC]
 SELECT
   agency_id, last_updated, hidden, 
@@ -58,7 +73,7 @@
   staff_internet_access, contact_email_available, tty_tdd
  FROM 
        agency
-      
+       
 [AGENCY_X_WEBSITE]       
 SELECT
   website 
@@ -88,20 +103,20 @@
    category_id = ?
   
 [PROGRAM_AGENCY_PROGRAM_CATEGORY_X_AGENCY_NAME_ID_PROGRAM_NAME]
-    SELECT 
-          a.agency_name, p.program_id, p.program_name,
-	  p.program_alias, p.address1, p.address2, 
-	  p.city, p.state, p.zip, p.main_phone, 
-          p.short_program_description 
-    FROM 
-	  program p, agency a, program_category pc 
-    WHERE 
-	  p.program_id = pc.program_id 
-          AND pc.category_id = ?
-          AND a.agency_id = p.agency_id 
-          AND p.hidden_reason IS NULL
-    ORDER BY
-          p.program_name
+SELECT 
+    a.agency_name, p.program_id, p.program_name,
+    p.program_alias, p.address1, p.address2, 
+    p.city, p.state, p.zip, p.main_phone, 
+    p.short_program_description 
+FROM 
+   program p, agency a, program_category pc 
+WHERE 
+   p.program_id = pc.program_id 
+   AND pc.category_id = ?
+   AND a.agency_id = p.agency_id 
+   AND p.hidden_reason IS NULL
+ORDER BY
+   p.program_name
 
 [PROGRAM_CATEGORY_HEADING_CATEGORY_X_HEADING_NAME]
 SELECT DISTINCT

=== modified file 'app-mvhub/t/sql_lib_sanity.t'
--- app-mvhub/t/sql_lib_sanity.t	2010-03-17 14:09:50 +0000
+++ app-mvhub/t/sql_lib_sanity.t	2010-03-25 18:20:35 +0000
@@ -37,6 +37,8 @@
 
 sub get_test_placeholders {
     my %data = (
+        'AGENCY_X_AGENCY_ID' =>
+            [ 'chester@xxxxxxxxxxxxxx', 'chester@xxxxxxxxxxxxxx' ],
         'AGENCY_X_WEBSITE'                          => [103553],
         'AGENCY_PROGRAM_X_AGENCY_NAME_AGENCY_ALIAS' => [510296],
         'CATEGORY_PROGRAM_VIEW_HEADING_CATEGORY_X_ID_NAME_COUNT' =>
@@ -46,7 +48,6 @@
             [800363],
         'PROGRAM_X_WEBSITE'      => [509548],
         'PROGRAM_X_LAST_UPDATED' => [509548],
-
     );
     return \%data;
 }

=== modified file 'lib-mvhub/lib/MVHub/AgencyAccount.pm'
--- lib-mvhub/lib/MVHub/AgencyAccount.pm	2010-02-01 23:43:18 +0000
+++ lib-mvhub/lib/MVHub/AgencyAccount.pm	2010-03-25 18:20:35 +0000
@@ -188,18 +188,11 @@
     if ($email) {
 
         # Process a password request
-        my $sql
-            = 'SELECT agency_name, password FROM agency WHERE agency_id IN ('
-            . 'SELECT agency_id FROM agency '
-            . 'WHERE contact_email = '
-            . $dbh->quote($email)
-            . ' UNION '
-            . 'SELECT agency_id  FROM program '
-            . 'WHERE contact_email = '
-            . $dbh->quote($email) . ')';
+        my $sql = $self->retr_select_sql('AGENCY_X_AGENCY_ID');
 
         my $agency_passwords_aref
-            = $dbh->selectall_arrayref( $sql, { Slice => {} } );
+            = $dbh->selectall_arrayref( $sql, { Slice => {} }, $email,
+            $email );
         if (@$agency_passwords_aref) {
             my $from_name = $self->get_config_param('NOTIFICATION.team_name')
                 or die "failed to get team_name";

=== modified file 'lib-mvhub/lib/MVHub/Utils/DB.pm'
--- lib-mvhub/lib/MVHub/Utils/DB.pm	2010-03-17 14:33:14 +0000
+++ lib-mvhub/lib/MVHub/Utils/DB.pm	2010-03-25 18:20:35 +0000
@@ -11,6 +11,7 @@
 use SQL::Library;
 
 use base 'Exporter';
+use SQL::Library;
 
 our @EXPORT_OK = qw(
     get_data_source