/**
* Returns the SQL clause for a search.
* Transforms the array returned by get_search_array() into SQL sub-query.
*
* @param array $search
* @return string
*/
function get_sql_search_clause($search)
{
// SQL where clauses are stored in $clauses array during query
// construction
$clauses = array();
foreach (array('file', 'name', 'comment', 'author') as $textfield) {
if (isset($search['fields'][$textfield])) {
$local_clauses = array();
foreach ($search['fields'][$textfield]['words'] as $word) {
if ('author' == $textfield) {
$local_clauses[] = $textfield . "='" . $word . "'";
} else {
$local_clauses[] = $textfield . " LIKE '%" . $word . "%'";
}
}
// adds brackets around where clauses
$local_clauses = prepend_append_array_items($local_clauses, '(', ')');
$clauses[] = implode(' ' . $search['fields'][$textfield]['mode'] . ' ', $local_clauses);
}
}
if (isset($search['fields']['allwords'])) {
$fields = array('file', 'name', 'comment');
if (isset($search['fields']['allwords']['fields']) and count($search['fields']['allwords']['fields']) > 0) {
$fields = array_intersect($fields, $search['fields']['allwords']['fields']);
}
// in the OR mode, request bust be :
// ((field1 LIKE '%word1%' OR field2 LIKE '%word1%')
// OR (field1 LIKE '%word2%' OR field2 LIKE '%word2%'))
//
// in the AND mode :
// ((field1 LIKE '%word1%' OR field2 LIKE '%word1%')
// AND (field1 LIKE '%word2%' OR field2 LIKE '%word2%'))
$word_clauses = array();
foreach ($search['fields']['allwords']['words'] as $word) {
$field_clauses = array();
foreach ($fields as $field) {
$field_clauses[] = $field . " LIKE '%" . $word . "%'";
}
// adds brackets around where clauses
$word_clauses[] = implode("\n OR ", $field_clauses);
}
array_walk($word_clauses, create_function('&$s', '$s="(".$s.")";'));
// make sure the "mode" is either OR or AND
if ($search['fields']['allwords']['mode'] != 'AND' and $search['fields']['allwords']['mode'] != 'OR') {
$search['fields']['allwords']['mode'] = 'AND';
}
$clauses[] = "\n " . implode("\n " . $search['fields']['allwords']['mode'] . "\n ", $word_clauses);
}
foreach (array('date_available', 'date_creation') as $datefield) {
if (isset($search['fields'][$datefield])) {
$clauses[] = $datefield . " = '" . $search['fields'][$datefield]['date'] . "'";
}
foreach (array('after', 'before') as $suffix) {
$key = $datefield . '-' . $suffix;
if (isset($search['fields'][$key])) {
$clauses[] = $datefield . ($suffix == 'after' ? ' >' : ' <') . ($search['fields'][$key]['inc'] ? '=' : '') . " '" . $search['fields'][$key]['date'] . "'";
}
}
}
if (isset($search['fields']['cat'])) {
if ($search['fields']['cat']['sub_inc']) {
// searching all the categories id of sub-categories
$cat_ids = get_subcat_ids($search['fields']['cat']['words']);
} else {
$cat_ids = $search['fields']['cat']['words'];
}
$local_clause = 'category_id IN (' . implode(',', $cat_ids) . ')';
$clauses[] = $local_clause;
}
// adds brackets around where clauses
$clauses = prepend_append_array_items($clauses, '(', ')');
$where_separator = implode("\n " . $search['mode'] . ' ', $clauses);
$search_clause = $where_separator;
return $search_clause;
}
/**
* Perform history search.
*
* @param array $data - used in trigger_change
* @param array $search
* @param string[] $types
* @param array
*/
function get_history($data, $search, $types)
{
if (isset($search['fields']['filename'])) {
$query = '
SELECT
id
FROM ' . IMAGES_TABLE . '
WHERE file LIKE \'' . $search['fields']['filename'] . '\'
;';
$search['image_ids'] = array_from_query($query, 'id');
}
// echo '<pre>'; print_r($search); echo '</pre>';
$clauses = array();
if (isset($search['fields']['date-after'])) {
$clauses[] = "date >= '" . $search['fields']['date-after'] . "'";
}
if (isset($search['fields']['date-before'])) {
$clauses[] = "date <= '" . $search['fields']['date-before'] . "'";
}
if (isset($search['fields']['types'])) {
$local_clauses = array();
foreach ($types as $type) {
if (in_array($type, $search['fields']['types'])) {
$clause = 'image_type ';
if ($type == 'none') {
$clause .= 'IS NULL';
} else {
$clause .= "= '" . $type . "'";
}
$local_clauses[] = $clause;
}
}
if (count($local_clauses) > 0) {
$clauses[] = implode(' OR ', $local_clauses);
}
}
if (isset($search['fields']['user']) and $search['fields']['user'] != -1) {
$clauses[] = 'user_id = ' . $search['fields']['user'];
}
if (isset($search['fields']['image_id'])) {
$clauses[] = 'image_id = ' . $search['fields']['image_id'];
}
if (isset($search['fields']['filename'])) {
if (count($search['image_ids']) == 0) {
// a clause that is always false
$clauses[] = '1 = 2 ';
} else {
$clauses[] = 'image_id IN (' . implode(', ', $search['image_ids']) . ')';
}
}
if (isset($search['fields']['ip'])) {
$clauses[] = 'IP LIKE "' . $search['fields']['ip'] . '"';
}
$clauses = prepend_append_array_items($clauses, '(', ')');
$where_separator = implode("\n AND ", $clauses);
$query = '
SELECT
date,
time,
user_id,
IP,
section,
category_id,
tag_ids,
image_id,
image_type
FROM ' . HISTORY_TABLE . '
WHERE ' . $where_separator . '
;';
// LIMIT '.$conf['nb_logs_page'].' OFFSET '.$page['start'].'
$result = pwg_query($query);
while ($row = pwg_db_fetch_assoc($result)) {
$data[] = $row;
}
return $data;
}
