From de2a69912438a6d35b2b41e7ab17cc65b124a4c1 Mon Sep 17 00:00:00 2001 From: garvinhicking Date: Thu, 20 Apr 2006 16:03:05 +0000 Subject: [PATCH] search/filter for media --- docs/NEWS | 7 +- include/functions.inc.php | 32 +++++ include/functions_config.inc.php | 1 - include/functions_images.inc.php | 164 ++++++++++++++++++----- serendipity_admin_image_selector.php | 3 + templates/default/admin/media_choose.tpl | 2 +- templates/default/admin/media_items.tpl | 2 +- templates/default/admin/media_pane.tpl | 32 ++++- 8 files changed, 196 insertions(+), 47 deletions(-) diff --git a/docs/NEWS b/docs/NEWS index 8c675a9..d53857c 100644 --- a/docs/NEWS +++ b/docs/NEWS @@ -35,14 +35,12 @@ Version 1.1-alpha4() - Restrict file upload by size/dimensions via configuration options - Sort media files by all custom property fields + - Search/Filter for specific properties/keywords. - Enhanced media filter for keywords - Use entered comments in the "You choose..." picking page TODO: - - Search/Filter for specific properties/keywords: - Search by: custom property fields. Evaluate input data from - serendipity_getImageFields(): 'date', 'intrange', 'text' - Make sure timestamps are saved as UNIX timestamp always! + - Store uploader authorid (new DB column required) - New option for image_Selector to save a specific sized version (?fid=XXX&targetSize=XX) @@ -52,7 +50,6 @@ Version 1.1-alpha4() fix up image paths [using ]. Also move ALL images of a directory, like when moving s9y installations. Put this into "Sync Thumbs" or "Manage Directories" panel. - ? Create Smarty functions to access media properties by ID/filename (garvinhicking) * Create a new index on the plugin DB table, optimize fetching config diff --git a/include/functions.inc.php b/include/functions.inc.php index cc3ea57..d43d24f 100644 --- a/include/functions.inc.php +++ b/include/functions.inc.php @@ -87,6 +87,38 @@ function serendipity_serverOffsetHour($timestamp = null, $negative = false) { } } +/* Converts a date string (DD.MM.YYYY, YYYY-MM-DD, MM/DD/YYYY) into a unix timestamp + * + * @access public + * @param string The input date + * @return int The output unix timestamp + */ +function &serendipity_convertToTimestamp($in) { + if (preg_match('@([0-9]+)([/\.-])([0-9]+)([/\.-])([0-9]+)@', $in, $m)) { + if ($m[2] != $m[4]) { + return $in; + } + + switch($m[2]) { + case '.': + return mktime(0, 0, 0, /* month */ $m[3], /* day */ $m[1], /* year */ $m[5]); + break; + + case '/': + return mktime(0, 0, 0, /* month */ $m[1], /* day */ $m[3], /* year */ $m[5]); + break; + + case '-': + return mktime(0, 0, 0, /* month */ $m[3], /* day */ $m[5], /* year */ $m[1]); + break; + } + + return $in; + } + + return $in; +} + /** * Format a timestamp * diff --git a/include/functions_config.inc.php b/include/functions_config.inc.php index 438f8b9..1eb7c84 100644 --- a/include/functions_config.inc.php +++ b/include/functions_config.inc.php @@ -1703,7 +1703,6 @@ function serendipity_checkFormToken() { return true; } -/** /** * Prevent XSRF attacks by setting a form token within HTTP Forms * diff --git a/include/functions_images.inc.php b/include/functions_images.inc.php index 3a06068..ef88587 100644 --- a/include/functions_images.inc.php +++ b/include/functions_images.inc.php @@ -38,12 +38,16 @@ function serendipity_isActiveFile($file) { * @param string Only fetch media with specific keyword * @return array Resultset of images */ -function serendipity_fetchImagesFromDatabase($start=0, $limit=0, &$total, $order = false, $ordermode = false, $directory = '', $filename = '', $keywords = '') { +function serendipity_fetchImagesFromDatabase($start=0, $limit=0, &$total, $order = false, $ordermode = false, $directory = '', $filename = '', $keywords = '', $filter = array()) { global $serendipity; $orderfields = serendipity_getImageFields(); if (empty($order) || !isset($orderfields[$order])) { - $order = 'date'; + $order = 'i.date'; + } + + if (!is_array($filter)) { + $filter = array(); } if (empty($ordermode) || ($ordermode != 'DESC' && $ordermode != 'ASC')) { @@ -58,18 +62,14 @@ function serendipity_fetchImagesFromDatabase($start=0, $limit=0, &$total, $order $limitsql = serendipity_db_limit_sql(serendipity_db_limit($start, $limit)); } + $where_sql = "WHERE 1 \n"; if (!empty($directory)) { - $directorysql = ' WHERE i.path LIKE \'' . serendipity_db_escape_string($directory) . '%\' '; + $where_sql .= " AND i.path LIKE '" . serendipity_db_escape_string($directory) . "'%'\n"; } if (!empty($filename)) { - $fsql = "(i.name like '%" . serendipity_db_escape_string($filename) . "%' OR - i.realname like '%" . serendipity_db_escape_string($filename) . "%')"; - if (empty($directorysql)) { - $directorysql = " WHERE $fsql"; - } else { - $directorysql .= " AND $fsql"; - } + $where_sql .= " AND (i.name like '%" . serendipity_db_escape_string($filename) . "%' OR + i.realname like '%" . serendipity_db_escape_string($filename) . "%')\n"; } if (!is_array($keywords)) { @@ -84,46 +84,84 @@ function serendipity_fetchImagesFromDatabase($start=0, $limit=0, &$total, $order $keywords[$i] = serendipity_db_escape_string($keyword); } + $join_properties = $join_keywords = $join_filterproperties = false; if (count($keywords) > 0) { - $fsql = "(mk.property IN ('" . implode("', '", $keywords) . "'))"; - if (empty($directorysql)) { - $directorysql = " WHERE $fsql"; + $where_sql .= " AND (mk.property IN ('" . implode("', '", $keywords) . "'))\n"; + $join_keywords = true; + } + + foreach($filter AS $f => $fval) { + if (!isset($orderfields[$f]) || empty($fval)) { + continue; + } + + if (is_array($fval)) { + if (empty($fval['from']) || empty($fval['to'])) { + continue; + } + + if ($orderfields[$f]['type'] == 'date') { + $fval['from'] = serendipity_convertToTimestamp(trim($fval['from'])); + $fval['to'] = serendipity_convertToTimestamp(trim($fval['to'])); + } + + if (substr($f, 0, 3) === 'bp.') { + $realf = substr($f, 3); + $where_sql .= " AND (bp2.property = '$realf' AND bp2.value >= " . (int)$fval['from'] . " AND bp2.value <= " . (int)$fval['to'] . ")\n"; + } else { + $where_sql .= " AND ($f >= " . (int)$fval['from'] . " AND $f <= " . (int)$fval['to'] . ")\n"; + } + } elseif ($orderfields[$f]['type'] == 'int') { + if (substr($f, 0, 3) === 'bp.') { + $realf = substr($f, 3); + $where_sql .= " AND (bp2.property = '$realf' AND bp2.value = '" . serendipity_db_escape_string(trim($fval)) . "')\n"; + } else { + $where_sql .= " AND ($f = '" . serendipity_db_escape_string(trim($fval)) . "')\n"; + } } else { - $directorysql .= " AND $fsql"; + if (substr($f, 0, 3) === 'bp.') { + $realf = substr($f, 3); + $where_sql .= " AND (bp2.property = '$realf' AND bp2.value LIKE '%" . serendipity_db_escape_string(trim($fval)) . "%')\n"; + } else { + $where_sql .= " AND ($f LIKE '%" . serendipity_db_escape_string(trim($fval)) . "%')\n"; + } } + $join_filterproperties = true; } - $perm = $permsql = ''; if (isset($serendipity['authorid']) && !serendipity_checkPermission('adminImagesViewOthers')) { - $perm = " (i.authorid = 0 OR i.authorid = " . (int)$serendipity['authorid'] . ")"; - if (empty($directorysql)) { - $directorysql = " WHERE $perm"; - } else { - $directorysql .= " AND $perm"; - } - $permsql = " WHERE $perm"; + $where_sql .= " AND (i.authorid = 0 OR i.authorid = " . (int)$serendipity['authorid'] . ")\n"; } $cond = array( - 'and' => $directorysql + 'and' => $where_sql ); serendipity_ACL_SQL($cond, false, 'directory'); - if (count($keywords) > 0) { - $cond['joins'] .= "LEFT OUTER JOIN {$serendipity['dbPrefix']}mediaproperties AS mk - ON (mk.mediaid = i.id AND mk.property_group = 'base_keyword')"; + if ($join_keywords) { + $cond['joins'] .= "\n LEFT OUTER JOIN {$serendipity['dbPrefix']}mediaproperties AS mk + ON (mk.mediaid = i.id AND mk.property_group = 'base_keyword')\n"; } if (substr($order, 0, 3) === 'bp.') { $cond['orderproperty'] = substr($order, 3); - $cond['joins'] .= "LEFT OUTER JOIN {$serendipity['dbPrefix']}mediaproperties AS bp - ON (bp.mediaid = i.id AND bp.property_group = 'base_property' AND bp.property = '{$cond['orderproperty']}')"; $cond['orderkey'] = 'bp.value'; $order = 'bp.value'; + $join_properties = true; } else { $cond['orderkey'] = "''"; } + if ($join_properties) { + $cond['joins'] .= "\n LEFT OUTER JOIN {$serendipity['dbPrefix']}mediaproperties AS bp + ON (bp.mediaid = i.id AND bp.property_group = 'base_property' AND bp.property = '{$cond['orderproperty']}')\n"; + } + + if ($join_filterproperties) { + $cond['joins'] .= "\n LEFT OUTER JOIN {$serendipity['dbPrefix']}mediaproperties AS bp2 + ON (bp2.mediaid = i.id AND bp2.property_group = 'base_property')\n"; + } + if ($serendipity['dbType'] == 'postgres') { $cond['group'] = ''; $cond['distinct'] = 'DISTINCT'; @@ -1381,7 +1419,8 @@ function serendipity_displayImageList($page = 0, $lineBreak = NULL, $manage = fa (isset($serendipity['GET']['sortorder']['ordermode']) ? $serendipity['GET']['sortorder']['ordermode'] : false), (isset($serendipity['GET']['only_path']) ? $serendipity['GET']['only_path'] : ''), (isset($serendipity['GET']['only_filename']) ? $serendipity['GET']['only_filename'] : ''), - (isset($serendipity['GET']['keywords']) ? $serendipity['GET']['keywords'] : '') + (isset($serendipity['GET']['keywords']) ? $serendipity['GET']['keywords'] : ''), + (isset($serendipity['GET']['filter']) ? $serendipity['GET']['filter'] : '') ); $pages = ceil($totalImages / $perPage); @@ -1697,29 +1736,29 @@ function serendipity_getImageFields() { global $serendipity; $x = array( - 'date' => array('desc' => SORT_ORDER_DATE, + 'i.date' => array('desc' => SORT_ORDER_DATE, 'type' => 'date' ), - 'name' => array('desc' => SORT_ORDER_NAME + 'i.name' => array('desc' => SORT_ORDER_NAME ), - 'authorid' => array('desc' => AUTHOR, + 'i.authorid' => array('desc' => AUTHOR, 'type' => 'authors' ), - 'extension' => array('desc' => SORT_ORDER_EXTENSION + 'i.extension' => array('desc' => SORT_ORDER_EXTENSION ), - 'size' => array('desc' => SORT_ORDER_SIZE, + 'i.size' => array('desc' => SORT_ORDER_SIZE, 'type' => 'intrange' ), - 'dimensions_width' => array('desc' => SORT_ORDER_WIDTH, + 'i.dimensions_width' => array('desc' => SORT_ORDER_WIDTH, 'type' => 'intrange' ), - 'dimensions_height' => array('desc' => SORT_ORDER_HEIGHT, + 'i.dimensions_height' => array('desc' => SORT_ORDER_HEIGHT, 'type' => 'intrange' ) ); @@ -1735,6 +1774,9 @@ function serendipity_getImageFields() { if (preg_match('@length@i', $name)) { $x['bp.' . $name]['type'] = 'intrange'; } + if (preg_match('@dpi@i', $name)) { + $x['bp.' . $name]['type'] = 'int'; + } } return $x; @@ -2046,7 +2088,8 @@ function serendipity_showPropertyForm(&$new_media, $keywordsPerBlock = 3, $is_ed if (!$is_edit) { $type = 'readonly'; } - $val =& $props['base_property'][$parts[0]]; + $val =& serendipity_mediaTypeCast($parts[0], $props['base_property'][$parts[0]], true); + $propkey = htmlspecialchars($parts[0]) . $idx; $show[$idx]['base_property'][$propkey] = array( @@ -2127,6 +2170,50 @@ function serendipity_showPropertyForm(&$new_media, $keywordsPerBlock = 3, $is_ed $smarty_vars); } +/** + * Tries to auto-convert specific fields into DB-storable values + * + * @param string The keyname + * @param string The value + * @param string Invert? + * @return array array('image_id') holding the last created thumbnail for immediate processing + * + */ +function serendipity_mediaTypeCast($key, $val, $invert = false) { + if (stristr($key, 'date') !== FALSE) { + if ($invert && is_int($val)) { + return serendipity_strftime(DATE_FORMAT_SHORT, $val, false); + } elseif ($invert === false) { + $tmp = strtotime($val); + if ($tmp !== FALSE && $tmp > 1) { + return $tmp; + } + } + } elseif ($invert && stristr($key, 'length') !== FALSE) { + $tmp = ''; + + $hours = intval(intval($val) / 3600); + $minutes = intval(($val / 60) % 60); + $seconds = intval($val % 60); + $mseconds = intval((($val - $seconds) * 100) % 100); + + $tmp .= str_pad($hours, 2, '0', STR_PAD_LEFT) . ':'; + $tmp .= str_pad($minutes, 2, '0', STR_PAD_LEFT). ':'; + $tmp .= str_pad($seconds, 2, '0', STR_PAD_LEFT) . '.'; + $tmp .= str_pad($mseconds, 2, '0', STR_PAD_LEFT); + + return $tmp; + } elseif ($invert === false && preg_match('@^([0-9]+):([0-9]+):([0-9]+).([0-9]+)$@i', $val, $m)) { + $tmp = ($m[1] * 3600) + + ($m[2] * 60) + + ($m[3]) + + ($m[4] / 100); + return $tmp; + } + + return $val; +} + /** * Inserts the submitted properties of uploaded media items * @@ -2149,6 +2236,7 @@ function serendipity_parsePropertyForm() { AND property_group = 'base_property'"); foreach($media AS $key => $val) { if ($key == 'image_id') continue; + $val = serendipity_mediaTypeCast($key, $val); $q = sprintf("INSERT INTO {$serendipity['dbPrefix']}mediaproperties (mediaid, property_group, property_subgroup, property, value) VALUES (%d, 'base_property', '', '%s', '%s')", @@ -2167,6 +2255,7 @@ function serendipity_parsePropertyForm() { property_group = 'base_metadata'"); foreach($s9y_img['metadata'] AS $maingroup => $items) { foreach($items AS $key => $val) { + $val = serendipity_mediaTypeCast($key, $val); $q = sprintf("INSERT INTO {$serendipity['dbPrefix']}mediaproperties (mediaid, property_group, property_subgroup, property, value) VALUES (%d, 'base_metadata', '%s', '%s', '%s')", @@ -2414,6 +2503,7 @@ function serendipity_showMedia(&$file, &$paths, $url = '', $manage = false, $lin 'keywords_selected' => $serendipity['GET']['keywords'], 'filter' => $serendipity['GET']['filter'], 'sort_order' => serendipity_getImageFields(), + 'authors' => serendipity_fetchUsers(), 'sort_row_interval' => array(8, 16, 50, 100), 'nr_files' => count($file), 'keywords' => explode(';', $serendipity['mediaKeywords']), diff --git a/serendipity_admin_image_selector.php b/serendipity_admin_image_selector.php index 41d7512..b74949b 100644 --- a/serendipity_admin_image_selector.php +++ b/serendipity_admin_image_selector.php @@ -107,6 +107,9 @@ switch ($serendipity['GET']['step']) { serendipity_prepareMedia($file); + $media['file']['props'] =& serendipity_fetchMediaProperties((int)$serendipity['GET']['image']); + serendipity_plugin_api::hook_event('media_getproperties_cached', $media['file']['props']['base_metadata'], $media['file']['realfile']); + if ($file['is_image']) { $file['finishJSFunction'] = 'serendipity_imageSelector_done(\'' . htmlspecialchars($serendipity['GET']['textarea']) . '\')'; diff --git a/templates/default/admin/media_choose.tpl b/templates/default/admin/media_choose.tpl index 9380f1c..0c74ecc 100644 --- a/templates/default/admin/media_choose.tpl +++ b/templates/default/admin/media_choose.tpl @@ -182,7 +182,7 @@ {$CONST.COMMENT}:
- + {serendipity_hookPlugin hookAll=true hook='frontend_image_selector_imagecomment' eventData=$media.file}
{/if} diff --git a/templates/default/admin/media_items.tpl b/templates/default/admin/media_items.tpl index 5e31eab..d34a307 100644 --- a/templates/default/admin/media_items.tpl +++ b/templates/default/admin/media_items.tpl @@ -51,7 +51,7 @@ {/if} {if NOT $media.enclose} -

{$file.realname} [{$file.mime}{if $file.realname != $file.diskname}, {$file.diskname}]{/if}]

+

{$file.realname} [{$file.mime}{if $file.realname != $file.diskname}, {$file.diskname}{/if}]

{if $file.authorid != 0}{$CONST.POSTED_BY} {$file.authorname}{/if} {$CONST.ON} {$file.date|@formatTime:DATE_FORMAT_SHORT}. {if $file.hotlink} diff --git a/templates/default/admin/media_pane.tpl b/templates/default/admin/media_pane.tpl index ff9a3d3..4a81411 100644 --- a/templates/default/admin/media_pane.tpl +++ b/templates/default/admin/media_pane.tpl @@ -58,17 +58,45 @@ {foreach from=$media.sort_order item="so_val" key="so_key"} + {if $media.filter[$so_key] != ''}{assign var="show_filter" value="true"}{/if} {$so_val.desc} - + {if $so_val.type == 'date'} + + + + + - (DD.MM.YYYY | YYYY-MM-DD | MM/DD/YYYY) + + {elseif $so_val.type == 'intrange'} + + + + + - + + {elseif $so_val.type == 'authors'} + + + + {else} + + +   + {/if} {/foreach}
-- 2.39.5