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;
}
