/**
* 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);
}
}
}
