/** * Returns data for the custom fields report, based on the field and options passed in. * * @access public * @param integer $fld_id The id of the custom field. * @param array $cfo_ids An array of option ids. * @param string $group_by How the data should be grouped. * @param boolean $list If the values should be listed out instead of just counted. * @return array An array of data. */ function getCustomFieldReport($fld_id, $cfo_ids, $group_by = "issue", $list = false) { $prj_id = Auth::getCurrentProject(); $fld_id = Misc::escapeInteger($fld_id); $cfo_ids = array_map(array('Misc', 'escapeString'), $cfo_ids); $backend = Custom_Field::getBackend($fld_id); if (is_object($backend)) { $options = array(); foreach ($cfo_ids as $cfo_id) { $options[$cfo_id] = Custom_Field::getOptionValue($fld_id, $cfo_id); } $in_field = 'icf_value'; } else { // get field values $stmt = "SELECT\n cfo_id,\n cfo_value\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field_option\n WHERE\n cfo_fld_id = {$fld_id} AND\n cfo_id IN('" . join("','", $cfo_ids) . "')\n ORDER BY\n cfo_id"; $options = $GLOBALS["db_api"]->dbh->getAssoc($stmt); if (PEAR::isError($options)) { Error_Handler::logError(array($options->getMessage(), $options->getDebugInfo()), __FILE__, __LINE__); return array(); } $in_field = 'cfo_id'; } if ($group_by == "customer") { $group_by_field = "iss_customer_id"; } else { $group_by_field = "iss_id"; } if ($list == true) { $sql = "SELECT\n DISTINCT({$group_by_field}),\n iss_id,\n iss_summary,\n iss_customer_id,\n count(DISTINCT(iss_id)) as row_count\n FROM\n"; if (!is_object($backend)) { $sql .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field_option,\n"; } $sql .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n"; if (!is_object($backend)) { $sql .= "cfo_id = icf_value AND"; } $sql .= "\nicf_iss_id = iss_id AND\n icf_fld_id = {$fld_id} AND\n {$in_field} IN('" . join("','", array_keys($options)) . "')\n GROUP BY\n {$group_by_field}\n ORDER BY\n row_count DESC"; $res = $GLOBALS["db_api"]->dbh->getAll($sql, DB_FETCHMODE_ASSOC); if (PEAR::isError($res)) { Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__); return array(); } if (Customer::hasCustomerIntegration($prj_id)) { Customer::getCustomerTitlesByIssues($prj_id, $res); if ($group_by == "issue") { usort($res, create_function('$a,$b', 'if ($a["customer_title"] < $b["customer_title"]) { return -1; } elseif ($a["customer_title"] > $b["customer_title"]) { return 1; } else { return 0; }')); } } return $res; } $data = array(); foreach ($options as $cfo_id => $value) { $stmt = "SELECT\n COUNT(DISTINCT {$group_by_field})\n FROM\n"; if (!is_object($backend)) { $stmt .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field_option,\n"; } $stmt .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n"; if (!is_object($backend)) { $stmt .= "cfo_id = icf_value AND"; } $stmt .= "\nicf_iss_id = iss_id AND\n icf_fld_id = {$fld_id} AND\n {$in_field} = '" . Misc::escapeString($cfo_id) . "'"; $count = $GLOBALS["db_api"]->dbh->getOne($stmt); if (PEAR::isError($count)) { Error_Handler::logError(array($count->getMessage(), $count->getDebugInfo()), __FILE__, __LINE__); return array(); } $data[$value] = $count; } // include count of all other values (used in pie chart) $stmt = "SELECT\n COUNT(DISTINCT {$group_by_field})\n FROM\n"; if (!is_object($backend)) { $stmt .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field_option,\n"; } $stmt .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n"; if (!is_object($backend)) { $stmt .= "cfo_id = icf_value AND"; } $stmt .= "\nicf_iss_id = iss_id AND\n icf_fld_id = {$fld_id} AND\n {$in_field} NOT IN('" . join("','", $cfo_ids) . "')"; $res = $GLOBALS["db_api"]->dbh->getOne($stmt); if (PEAR::isError($res)) { Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__); return array(); } $data["All Others"] = $res; return $data; }
/** * Returns the value for the specified field * * @access public * @param integer $iss_id The ID of the issue * @param integer $fld_id The ID of the field * @param boolean $raw If the raw value should be displayed * @param mixed an array or string containing the value */ function getDisplayValue($iss_id, $fld_id, $raw = false) { $sql = "SELECT\n fld_id,\n fld_type,\n icf_value\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field\n WHERE\n fld_id=icf_fld_id AND\n icf_iss_id=" . Misc::escapeInteger($iss_id) . " AND\n fld_id = " . Misc::escapeInteger($fld_id); $res = $GLOBALS["db_api"]->dbh->getAll($sql, DB_FETCHMODE_ASSOC); if (PEAR::isError($res)) { Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__); return ''; } else { $values = array(); for ($i = 0; $i < count($res); $i++) { if ($res[$i]['fld_type'] == 'text' || $res[$i]['fld_type'] == 'textarea') { $values[] = $res[$i]['icf_value']; } elseif ($res[$i]["fld_type"] == "combo" || $res[$i]['fld_type'] == 'multiple') { if ($raw) { $values[] = $res[$i]['icf_value']; } else { $values[] = Custom_Field::getOptionValue($res[$i]["fld_id"], $res[$i]["icf_value"]); } } } if ($raw) { return $values; } else { return join(', ', $values); } } }