function util_date_to_unixtime($date) { $time = 0; if (!$date || $date == "") { return array($time, false); } list($year, $month, $day) = util_date_explode($date); $time = mktime(0, 0, 0, $month, $day, $year); return array($time, true); }
/** * Return the value to find for a field, for the current query * * @param field: the field object * @param prefs: field values array (HTTP GET variable) * @param field_value: the field name * @param advsrch: advance search or simple search * @param notany: is true if the value of the field is not "Any" * * @return string */ function getValuesWhereClause($field, $prefs, $field_name, $advsrch, &$notany) { $notany = true; $where = ''; //echo $field_name."->prefs[".$field->getName()."]=".$prefs[$field->getName()][0]."<br>"; if (($field->isSelectBox() || $field->isMultiSelectBox()) && (isset($prefs[$field->getName()]) && !$this->isvarany($prefs[$field->getName()]))) { // Only select box criteria to where clause if argument is not ANY return " AND " . $field_name . " IN (" . db_es(implode(",", $prefs[$field->getName()])) . ") "; } else { if ($field->isDateField() && (isset($prefs[$field->getName()]) && $prefs[$field->getName()][0] || isset($prefs[$field->getName() . '_end']) && $prefs[$field->getName() . '_end'][0])) { // transform a date field into a unix time and use <, > or = list($time, $ok) = util_date_to_unixtime($prefs[$field->getName()][0]); if ($advsrch) { list($time_end, $ok_end) = util_date_to_unixtime($prefs[$field->getName() . '_end'][0]); if ($ok) { list($year, $month, $day) = util_date_explode($prefs[$field->getName()][0]); $time_after = mktime(0, 0, 0, $month, $day, $year); $where .= " AND " . $field_name . " >= " . $time_after; } if ($ok_end) { list($year, $month, $day) = util_date_explode($prefs[$field->getName() . '_end'][0]); $time_before = mktime(23, 59, 59, $month, $day, $year); $where .= " AND " . $field_name . " <= " . $time_before; } } else { if (isset($prefs[$field->getName()][1])) { $operator = $prefs[$field->getName()][1]; } else { $operator = $prefs[$field->getName() . '_op'][0]; } // '=' means that day between 00:00 and 23:59 if ($operator == '=') { list($year, $month, $day) = util_date_explode($prefs[$field->getName()][0]); $time_end = mktime(23, 59, 59, $month, $day, $year); $where = " AND " . $field_name . " >= " . $time . " AND " . $field_name . " <= " . $time_end; } else { if ($operator == '>') { list($year, $month, $day) = util_date_explode($prefs[$field->getName()][0]); $time_after = mktime(0, 0, 0, $month, $day + 1, $year); $where = " AND " . $field_name . " " . $operator . "=" . $time_after; } else { if ($operator == '<') { list($year, $month, $day) = util_date_explode($prefs[$field->getName()][0]); $time_before = mktime(23, 59, 59, $month, $day - 1, $year); $where = " AND " . $field_name . " " . $operator . "=" . $time_before; } } } } // Always exclude undefined dates (0) $where .= " AND " . $field_name . " <> 0 "; return $where; } else { if (($field->isTextField() || $field->isTextArea()) && isset($prefs[$field->getName()][0]) && $prefs[$field->getName()][0]) { // It's a text field accept. Process INT or TEXT,VARCHAR fields differently return " AND " . $field->buildMatchExpression($field_name, $prefs[$field->getName()][0]); } } } $notany = false; }
function _getStartStatement($value) { $stmt = ''; list($time, $ok) = util_date_to_unixtime($value); if ($ok) { list($year, $month, $day) = util_date_explode($value); $time_after = mktime(0, 0, 0, $month, $day + 1, $year); $stmt = $this->field . " >= " . $time_after; } return $stmt; }
/** * getArtifacts - get an array of Artifact objects * * @param $criteria : array of items field => value * @param $offset : the index of artifact to begin * @param $max_rows : number of artifacts to return * * @param OUT $total_artifacts : total number of artifacts (if offset and max_rows were not here) * * @return array The array of Artifact objects. */ function getArtifacts($criteria, $offset, $max_rows, &$total_artifacts) { global $Language, $art_field_fact; $ACCEPTED_OPERATORS = array('=', '<', '>', '<>', '<=', '>='); $artifacts = array(); if (is_array($criteria) && count($criteria) > 0) { $sql_select = "SELECT a.* "; $sql_from = " FROM artifact_group_list agl, artifact a "; $sql_where = " WHERE a.group_artifact_id = " . db_ei($this->ArtifactType->getID()) . " AND \n a.group_artifact_id = agl.group_artifact_id "; $cpt_criteria = 0; // counter for criteria (used to build the SQL query) foreach ($criteria as $c => $cr) { $af = $art_field_fact->getFieldFromName($cr->field_name); if (!$af || !is_object($af)) { $this->setError('Cannot Get ArtifactField From Name : ' . $cr->field_name); return false; } elseif ($art_field_fact->isError()) { $this->setError($art_field_fact->getErrorMessage()); return false; } if ($af->isDateField() && ($cr->field_name != 'open_date' && $cr->field_name != 'close_date' && $cr->field_name != 'last_update_date')) { // The SQL query expects a timestamp, whereas the given date is in YYYY-MM-DD format $cr->field_value = strtotime($cr->field_value); } $operator = "="; // operator by default if (isset($cr->operator) && in_array($cr->operator, $ACCEPTED_OPERATORS)) { $operator = $cr->operator; } if ($af->isStandardField()) { if ($cr->operator == '=' && ($cr->field_name == 'open_date' || $cr->field_name == 'close_date' || $cr->field_name == 'last_update_date')) { // special case for open_date and close_date with operator = : the hours, minutes, and seconds are stored, so we have to compare an interval list($year, $month, $day) = util_date_explode($cr->field_value); $time_end = mktime(23, 59, 59, $month, $day, $year); $sql_where .= " AND (a." . $cr->field_name . " >= '" . strtotime($cr->field_value) . "')"; $sql_where .= " AND (a." . $cr->field_name . " <= '" . $time_end . "')"; } else { if ($af->isDateField()) { $sql_where .= " AND (a." . $cr->field_name . " " . $operator . " '" . strtotime($cr->field_value) . "')"; } else { $sql_where .= " AND (a." . $cr->field_name . " " . $operator . " '" . db_es($cr->field_value) . "')"; } } } else { $sql_select .= ", afv" . $cpt_criteria . ".valueInt "; $sql_from .= ", artifact_field af" . $cpt_criteria . ", artifact_field_value afv" . $cpt_criteria . " "; $sql_where .= " AND af" . $cpt_criteria . ".group_artifact_id = agl.group_artifact_id\n AND (af" . $cpt_criteria . ".field_name = '" . $cr->field_name . "' \n AND afv" . $cpt_criteria . "." . $af->getValueFieldName() . " " . $operator . " '" . $cr->field_value . "') \n AND af" . $cpt_criteria . ".field_id = afv" . $cpt_criteria . ".field_id \n AND a.artifact_id = afv" . $cpt_criteria . ".artifact_id "; } $cpt_criteria += 1; } $sql = $sql_select . $sql_from . $sql_where; } else { $sql = "SELECT a.artifact_id \n FROM artifact_group_list agl, artifact a \n WHERE a.group_artifact_id = " . db_ei($this->ArtifactType->getID()) . " AND \n a.group_artifact_id = agl.group_artifact_id"; } // we count the total number of artifact (without offset neither limit) to be able to perform the pagination $result_count = db_query($sql); $rows_count = db_numrows($result_count); $total_artifacts = $rows_count; $offset = intval($offset); $max_rows = intval($max_rows); if ($max_rows > 0) { if (!$offset || $offset < 0) { $offset = 0; } $sql .= " LIMIT " . db_ei($offset) . "," . db_ei($max_rows); } $result = db_query($sql); $rows = db_numrows($result); $this->fetched_rows = $rows; if (db_error()) { $this->setError($Language->getText('tracker_common_factory', 'db_err') . ': ' . db_error()); return false; } else { while ($arr = db_fetch_array($result)) { $artifact = new Artifact($this->ArtifactType, $arr['artifact_id'], true); // artifact is not added if the user can't view it if ($artifact->userCanView()) { $artifacts[$arr['artifact_id']] = $artifact; } } } return $artifacts; }