[opw-web] view_participants: distinguish participants who have uploaded contracts

[Owen Taylor <otaylor src gnome org>]

commit addfbd2c6da3eccdc7747e18e2b6469a9fa75619
Author: Owen W. Taylor <otaylor fishsoup net>
Date:   Mon Apr 21 10:31:04 2014 -0400

    view_participants: distinguish participants who have uploaded contracts
    
    Show participants who have uploaded contract documents but who have not
    yet been approved as 'uploaded' rather than 'no'.
    
    Add an index on attachments.uploader to make this efficient.

 lang/en-gb.php                    |    1 +
 modules/mod_view_participants.php |   17 ++++++++++++-----
 schema.sql                        |    4 +++-
 3 files changed, 16 insertions(+), 6 deletions(-)
---
diff --git a/lang/en-gb.php b/lang/en-gb.php
index b532121..3d8a41e 100644
--- a/lang/en-gb.php
+++ b/lang/en-gb.php
@@ -314,6 +314,7 @@ $lang_data = array(
     'role_m'                => 'Mentor',
     'role_i'                => 'Unapproved mentor',
     'role_g'                => 'Guest',
+    'uploaded'              => 'Uploaded',
     'no_participants'       => 'This programs has no participants',
     'projects'              => 'Projects',
 
diff --git a/modules/mod_view_participants.php b/modules/mod_view_participants.php
index 2a740bb..9d0fcae 100644
--- a/modules/mod_view_participants.php
+++ b/modules/mod_view_participants.php
@@ -13,7 +13,10 @@ $program_id = $core->variable('prg', '');
 
 $sql = "SELECT r.username, r.role AS role, r.contract_approved, " .
        "pr.id AS project_id, pr.title AS project_title, pr.is_accepted, " .
-       "pf.fullname " .
+       "pf.fullname, " .
+       "(SELECT COUNT(*) FROM {$db->prefix}attachments a " .
+            "WHERE a.uploader = r.username AND a.is_contract = 1 AND a.program_id = {$program_id}) " .
+            " AS contract_count " .
        "FROM {$db->prefix}roles r " .
        "LEFT JOIN {$db->prefix}profiles pf " .
        "ON r.username = pf.username ".
@@ -67,7 +70,8 @@ foreach ($list_data as $row)
         'role'      => $lang->get('role_' . $row['role']),
         'projects'  => $project,
         'any_accepted' => $row['is_accepted'] == 1,
-        'contract_approved'  => $row['contract_approved']
+        'contract_approved'  => $row['contract_approved'],
+        'contract_count'     => $row['contract_count']
     );
 
     $prev_row = $row;
@@ -80,9 +84,12 @@ foreach ($list as $item)
 {
     if ($item['contract_approved'] == 1)
         $contract_approved = $lang->get('yes');
-    else if ($item['any_accepted'] == 1)
-        $contract_approved = $lang->get('no');
-    else
+    else if ($item['any_accepted'] == 1) {
+        if ($item['contract_count'] > 0)
+            $contract_approved = $lang->get('uploaded');
+        else
+            $contract_approved = $lang->get('no');
+    } else
         $contract_approved = '';
 
     // Assign data for each mentor
diff --git a/schema.sql b/schema.sql
index 30ebffb..df29ffd 100644
--- a/schema.sql
+++ b/schema.sql
@@ -123,6 +123,7 @@ CREATE TABLE `opw_profiles` (
 alter table opw_attachments add `is_contract` tinyint(1) NOT NULL DEFAULT 0;
 alter table opw_attachments add `program_id` mediumint(6) unsigned NOT NULL;
 alter table opw_attachments add `uploader` varchar(255) NOT NULL;
+create index uploader on opw_attachments (uploader) ;
 update opw_attachments set uploader = (select username from opw_participants p where p.project_id = 
opw_attachments.project_id and p.role = 's');
 update opw_attachments set program_id = (select p.program_id from opw_projects p where p.id = 
opw_attachments.project_id);
 */
@@ -138,5 +139,6 @@ CREATE TABLE `opw_attachments` (
   `size` mediumint(10) unsigned NOT NULL,
   `data` MEDIUMBLOB NOT NULL,
   `is_contract` tinyint(1) NOT NULL DEFAULT 0,
-  PRIMARY KEY (`id`)
+  PRIMARY KEY (`id`),
+  KEY (`uploader`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;