Merge lp:~raulraat/eventum/customFieldWeeklyReport into lp:eventum

[Raul Raat]

Sorry about the previous Merge proposal, i removed that, now the merge proposal contains 2 changes:
* if multiple custom field options match, all of them are summarized, use subquery to avoid it
* the table issue_user is no longer joined in the query (as data from that table is not needed)

=== modified file 'lib/eventum/class.report.php'
--- lib/eventum/class.report.php	2010-07-28 08:50:32 +0000
+++ lib/eventum/class.report.php	2010-08-03 15:56:47 +0000
@@ -791,54 +791,47 @@
         $options = Custom_Field::getOptions($fld_id, $cfo_ids);
 
         $sql = "SELECT
-                    iss_id,
+                    iss_id, 
                     SUM(ttr_time_spent) ttr_time_spent_sum,
                     iss_summary,
                     iss_customer_id,
-                    iss_private,
-                    fld_id
+                    iss_private
                ";
 
-
             if ($per_user) {
                 $sql .= ', usr_full_name ';
             }
             $sql .= "
                  FROM
-                    " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field,
                     " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "time_tracking,";
 
             if ($per_user) {
                     $sql .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "user, ";
             }
 
-            if (count($options) > 0) {
-                $sql .= "
-                        " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field_option,";
-            }
             $sql .= "
-                        " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field,
-                        " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue,
-                        " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_user
+                        " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue
                     WHERE
                         ttr_created_date BETWEEN '" . Misc::escapeString($start_date) . "' AND '" . Misc::escapeString($end_date) . "' AND
-                        fld_id = icf_fld_id AND
                        	ttr_iss_id = iss_id AND
                   		";
-            if (count($options) > 0) {
-                $sql .=
-                        " cfo_id = icf_value AND";
-            }
             if ($per_user) {
                  $sql .= " usr_id = ttr_usr_id AND ";
             }
             $sql .= "
-                        icf_iss_id = iss_id AND
-                        isu_iss_id = iss_id AND
-                        icf_fld_id = $fld_id";
+                        ttr_iss_id = iss_id
+                        ";
             if (count($options) > 0) {
-                $sql .= " AND
-                        cfo_id IN('" . join("','", Misc::escapeString(array_keys($options))) . "')";
+            $sql .= " AND (
+                SELECT
+                    count(*)
+                FROM
+                    " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field a
+                WHERE
+                    a.icf_fld_id = $fld_id AND
+                    a.icf_value IN('" . join("','", Misc::escapeString(array_keys($options))) . "') AND
+                    a.icf_iss_id = ttr_iss_id
+                ) > 0";
             }
             if ($per_user) {
                 $sql .= "
@@ -856,7 +849,7 @@
             return array();
         } else {
         	for ($i = 0; $i < count($res); $i++) {
-                $res[$i]['field_value'] = Custom_Field::getDisplayValue($res[$i]['iss_id'], $res[$i]['fld_id']);
+                $res[$i]['field_value'] = Custom_Field::getDisplayValue($res[$i]['iss_id'], $fld_id);
                 $res[$i]['ttr_time_spent_sum_formatted'] = Misc::getFormattedTime($res[$i]['ttr_time_spent_sum'], false);
             }
             return $res;