/** * Returns data for the custom fields weekly report, based on the field and options passed in. * * @param integer $fld_id The id of the custom field. * @param array $cfo_ids An array of option ids. * @param string $start_date * @param string $end_date * @param boolean $per_user Show time spent per user * @return array An array of data. */ public static function getCustomFieldWeeklyReport($fld_id, $cfo_ids, $start_date, $end_date, $per_user = false) { $fld_id = (int) $fld_id; $cfo_ids = (array) $cfo_ids; // get field values $options = Custom_Field::getOptions($fld_id, $cfo_ids); $params = array(); $sql = 'SELECT iss_id, SUM(ttr_time_spent) ttr_time_spent_sum, iss_summary, iss_customer_id, iss_private '; if ($per_user) { $sql .= ', usr_full_name '; } $sql .= ' FROM {{%time_tracking}},'; if ($per_user) { $sql .= '{{%user}}, '; } $sql .= ' {{%issue}} WHERE iss_prj_id=? AND ttr_created_date BETWEEN ? AND ? AND ttr_iss_id = iss_id AND '; $params[] = Auth::getCurrentProject(); $params[] = "{$start_date} 00:00:00"; $params[] = "{$end_date} 23:59:59"; if ($per_user) { $sql .= ' usr_id = ttr_usr_id AND '; } $sql .= ' ttr_iss_id = iss_id '; if (count($options) > 0) { $ids = array_keys($options); $list = DB_Helper::buildList($ids); $sql .= " AND (\n SELECT\n count(*)\n FROM\n {{%issue_custom_field}} a\n WHERE\n a.icf_fld_id = ? AND\n a.icf_value IN({$list}) AND\n a.icf_iss_id = ttr_iss_id\n ) > 0"; $params[] = $fld_id; $params = array_merge($params, $ids); } if ($per_user) { $sql .= ' GROUP BY iss_id, ttr_usr_id'; } else { $sql .= ' GROUP BY iss_id'; } try { $res = DB_Helper::getInstance()->getAll($sql, $params); } catch (DbException $e) { return array(); } foreach ($res as &$row) { $row['field_value'] = Custom_Field::getDisplayValue($row['iss_id'], $fld_id); $row['ttr_time_spent_sum_formatted'] = Misc::getFormattedTime($row['ttr_time_spent_sum'], false); } return $res; }
/** * Method used to update the values stored in the database. * * @access public * @return integer 1 if the update worked properly, any other value otherwise */ function updateValues() { global $HTTP_POST_VARS; $prj_id = Auth::getCurrentProject(); $issue_id = Misc::escapeInteger($HTTP_POST_VARS["issue_id"]); $old_values = Custom_Field::getValuesByIssue($prj_id, $issue_id); // get the types for all of the custom fields being submitted $stmt = "SELECT\n fld_id,\n fld_type\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field\n WHERE\n fld_id IN (" . implode(", ", Misc::escapeInteger(@array_keys($HTTP_POST_VARS['custom_fields']))) . ")"; $field_types = $GLOBALS["db_api"]->dbh->getAssoc($stmt); // get the titles for all of the custom fields being submitted $stmt = "SELECT\n fld_id,\n fld_title\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field\n WHERE\n fld_id IN (" . implode(", ", Misc::escapeInteger(@array_keys($HTTP_POST_VARS['custom_fields']))) . ")"; $field_titles = $GLOBALS["db_api"]->dbh->getAssoc($stmt); $updated_fields = array(); foreach ($HTTP_POST_VARS["custom_fields"] as $fld_id => $value) { $fld_id = Misc::escapeInteger($fld_id); // security check $sql = "SELECT\n fld_min_role\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field\n WHERE\n fld_id = {$fld_id}"; $min_role = $GLOBALS["db_api"]->dbh->getOne($sql); if ($min_role > Auth::getCurrentRole()) { continue; } $option_types = array('multiple', 'combo'); if (!in_array($field_types[$fld_id], $option_types)) { // check if this is a date field if ($field_types[$fld_id] == 'date') { $value = $value['Year'] . "-" . $value['Month'] . "-" . $value['Day']; if ($value == '--') { $value = ''; } } // first check if there is actually a record for this field for the issue $stmt = "SELECT\n icf_id,\n icf_value\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field\n WHERE\n icf_iss_id=" . $issue_id . " AND\n icf_fld_id={$fld_id}"; $res = $GLOBALS["db_api"]->dbh->getRow($stmt, DB_FETCHMODE_ASSOC); if (PEAR::isError($res)) { Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__); return -1; } $icf_id = $res['icf_id']; $icf_value = $res['icf_value']; if ($icf_value == $value) { continue; } if (empty($icf_id)) { // record doesn't exist, insert new record $stmt = "INSERT IGNORE INTO\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field\n (\n icf_iss_id,\n icf_fld_id,\n icf_value\n ) VALUES (\n " . $issue_id . ",\n {$fld_id},\n '" . Misc::escapeString($value) . "'\n )"; $res = $GLOBALS["db_api"]->dbh->query($stmt); if (PEAR::isError($res)) { Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__); return -1; } } else { // record exists, update it $stmt = "UPDATE\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field\n SET\n icf_value='" . Misc::escapeString($value) . "'\n WHERE\n icf_id={$icf_id}"; $res = $GLOBALS["db_api"]->dbh->query($stmt); if (PEAR::isError($res)) { Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__); return -1; } } if ($field_types[$fld_id] == 'textarea') { $updated_fields[$field_titles[$fld_id]] = ''; } else { $updated_fields[$field_titles[$fld_id]] = History::formatChanges($icf_value, $value); } } else { $old_value = Custom_Field::getDisplayValue($HTTP_POST_VARS['issue_id'], $fld_id, true); if (!is_array($old_value)) { $old_value = array($old_value); } if (!is_array($value)) { $value = array($value); } if (count(array_diff($old_value, $value)) > 0 || count(array_diff($value, $old_value)) > 0) { $old_display_value = Custom_Field::getDisplayValue($HTTP_POST_VARS['issue_id'], $fld_id); // need to remove all associated options from issue_custom_field and then // add the selected options coming from the form Custom_Field::removeIssueAssociation($fld_id, $HTTP_POST_VARS["issue_id"]); if (@count($value) > 0) { Custom_Field::associateIssue($HTTP_POST_VARS["issue_id"], $fld_id, $value); } $new_display_value = Custom_Field::getDisplayValue($HTTP_POST_VARS['issue_id'], $fld_id); $updated_fields[$field_titles[$fld_id]] = History::formatChanges($old_display_value, $new_display_value); } } } Workflow::handleCustomFieldsUpdated($prj_id, $issue_id, $old_values, Custom_Field::getValuesByIssue($prj_id, $issue_id)); Issue::markAsUpdated($HTTP_POST_VARS["issue_id"]); // need to save a history entry for this if (count($updated_fields) > 0) { // log the changes $changes = ''; $i = 0; foreach ($updated_fields as $key => $value) { if ($i > 0) { $changes .= "; "; } if (!empty($value)) { $changes .= "{$key}: {$value}"; } else { $changes .= "{$key}"; } $i++; } History::add($HTTP_POST_VARS["issue_id"], Auth::getUserID(), History::getTypeID('custom_field_updated'), "Custom field updated ({$changes}) by " . User::getFullName(Auth::getUserID())); } return 1; }