From a2dd39df73d6c29073dbd8d7d87f34adf9aef621 Mon Sep 17 00:00:00 2001 From: rub Date: Tue, 28 Mar 2006 22:33:29 +0000 Subject: [NBM] Step 5: Change notification queries in order to try to optimize treatment duration. git-svn-id: http://piwigo.org/svn/trunk@1112 68402e56-0260-453c-a942-63ccdbb3a9ee --- admin/notification_by_mail.php | 19 +- include/functions_notification.inc.php | 316 ++++++++++++++++++++++++++------- 2 files changed, 265 insertions(+), 70 deletions(-) diff --git a/admin/notification_by_mail.php b/admin/notification_by_mail.php index c6212299a..9959708ee 100644 --- a/admin/notification_by_mail.php +++ b/admin/notification_by_mail.php @@ -230,14 +230,14 @@ order by include(get_language_filepath('admin.lang.php')); } - $message = ''; - $news = news($row['last_send'], $dbnow); - if (count($news) > 0) + if ($is_action_send) { - array_push($return_list, $row); - - if ($is_action_send) + $message = ''; + $news = news($row['last_send'], $dbnow); + if (count($news) > 0) { + array_push($return_list, $row); + $subject = '['.$conf['gallery_title'].']: '.l10n('nbm_ContentObject'); $message .= sprintf(l10n('nbm_ContentHello'), $row['username']).",\n\n"; @@ -281,6 +281,13 @@ order by } } } + else + { + if (news_exists($row['last_send'], $dbnow)) + { + array_push($return_list, $row); + } + } } // Restore $user, $lang_info and $lang arrays (include/user.inc.php has been executed) diff --git a/include/functions_notification.inc.php b/include/functions_notification.inc.php index 6ac995bb8..a88aba47b 100644 --- a/include/functions_notification.inc.php +++ b/include/functions_notification.inc.php @@ -25,29 +25,29 @@ // | USA. | // +-----------------------------------------------------------------------+ - -/** - * Extract news fonctions of feed.php - */ - // +-----------------------------------------------------------------------+ // | functions | // +-----------------------------------------------------------------------+ -/** - * new comments between two dates, according to authorized categories +/* + * Execute custom notification query * + * @param string action ('count' or 'info') + * @param string type of query ('new_comments', 'unvalidated_comments', 'new_elements', 'updated_categories', ' new_users', 'waiting_elements') * @param string start (mysql datetime format) * @param string end (mysql datetime format) - * @param string forbidden categories (comma separated) - * @return array comment ids + * + * @return integer for action count + * array for info */ -function new_comments($start, $end) +function custom_notification_query($action, $type, $start, $end) { global $user; - - $query = ' -SELECT DISTINCT c.id AS comment_id + + switch($type) + { + case 'new_comments': + $query = ' FROM '.COMMENTS_TABLE.' AS c , '.IMAGE_CATEGORY_TABLE.' AS ic WHERE c.image_id = ic.image_id @@ -55,7 +55,159 @@ SELECT DISTINCT c.id AS comment_id AND c.validation_date <= \''.$end.'\' AND category_id NOT IN ('.$user['forbidden_categories'].') ;'; - return array_from_query($query, 'comment_id'); + break; + case 'unvalidated_comments': + $query = ' + FROM '.COMMENTS_TABLE.' + WHERE date <= \''.$end.'\' + AND (validated = \'false\' + OR validation_date > \''.$end.'\') +;'; + break; + case 'new_elements': + $query = ' + FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON image_id = id + WHERE date_available > \''.$start.'\' + AND date_available <= \''.$end.'\' + AND category_id NOT IN ('.$user['forbidden_categories'].') +;'; + break; + case 'updated_categories': + $query = ' + FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON image_id = id + WHERE date_available > \''.$start.'\' + AND date_available <= \''.$end.'\' + AND category_id NOT IN ('.$user['forbidden_categories'].') +;'; + break; + case ' new_users': + $query = ' + FROM '.USER_INFOS_TABLE.' + WHERE registration_date > \''.$start.'\' + AND registration_date <= \''.$end.'\' +;'; + break; + case 'waiting_elements': + $query = ' + FROM '.WAITING_TABLE.' + WHERE validated = \'false\' +;'; + break; + default: + // stop this function and return nothing + return; + break; + } + + switch($action) + { + case 'count': + switch($type) + { + case 'new_comments': + $field_id = 'c.id'; + break; + case 'unvalidated_comments': + $field_id = 'id'; + break; + case 'new_elements': + $field_id = 'image_id'; + break; + case 'updated_categories': + $field_id = 'category_id'; + break; + case ' new_users': + $field_id = 'user_id'; + break; + case 'waiting_elements': + $field_id = 'id'; + break; + } + $query = 'SELECT count(distinct '.$field_id.') as CountId +'.$query; + list($count) = mysql_fetch_array(pwg_query($query)); + return $count; + + break; + case 'info': + switch($type) + { + case 'new_comments': + $fields = array('c.id'); + break; + case 'unvalidated_comments': + $fields = array('id'); + break; + case 'new_elements': + $fields = array('image_id'); + break; + case 'updated_categories': + $fields = array('category_id'); + break; + case ' new_users': + $fields = array('user_id'); + break; + case 'waiting_elements': + $fields = array('id'); + break; + } + + $query = 'SELECT distinct '.implode(', ', $fields).' +'.$query; + $result = pwg_query($query); + + $infos = array(); + + while ($row = mysql_fetch_array($result)) + { + array_push($infos, $row); + } + + return $infos; + + break; + } + + //return is done on previous switch($action) +} + +/** + * new comments between two dates, according to authorized categories + * + * @param string start (mysql datetime format) + * @param string end (mysql datetime format) + * @param string forbidden categories (comma separated) + * @return count comment ids + */ +function nb_new_comments($start, $end) +{ + return custom_notification_query('count', 'new_comments', $start, $end); +} + +/** + * new comments between two dates, according to authorized categories + * + * @param string start (mysql datetime format) + * @param string end (mysql datetime format) + * @param string forbidden categories (comma separated) + * @return array comment ids + */ +function new_comments($start, $end) +{ + return custom_notification_query('info', 'new_comments', $start, $end); +} + +/** + * unvalidated at a precise date + * + * Comments that are registered and not validated yet on a precise date + * + * @param string date (mysql datetime format) + * @return count comment ids + */ +function nb_unvalidated_comments($date) +{ + return custom_notification_query('count', 'unvalidated_comments', $date, $date); } /** @@ -68,14 +220,20 @@ SELECT DISTINCT c.id AS comment_id */ function unvalidated_comments($date) { - $query = ' -SELECT DISTINCT id - FROM '.COMMENTS_TABLE.' - WHERE date <= \''.$date.'\' - AND (validated = \'false\' - OR validation_date > \''.$date.'\') -;'; - return array_from_query($query, 'id'); + return custom_notification_query('info', 'unvalidated_comments', $start, $end); +} + +/** + * new elements between two dates, according to authorized categories + * + * @param string start (mysql datetime format) + * @param string end (mysql datetime format) + * @param string forbidden categories (comma separated) + * @return count element ids + */ +function nb_new_elements($start, $end) +{ + return custom_notification_query('count', 'new_elements', $start, $end); } /** @@ -88,16 +246,20 @@ SELECT DISTINCT id */ function new_elements($start, $end) { - global $user; - - $query = ' -SELECT DISTINCT image_id - FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON image_id = id - WHERE date_available > \''.$start.'\' - AND date_available <= \''.$end.'\' - AND category_id NOT IN ('.$user['forbidden_categories'].') -;'; - return array_from_query($query, 'image_id'); + return custom_notification_query('info', 'new_elements', $start, $end); +} + +/** + * updated categories between two dates, according to authorized categories + * + * @param string start (mysql datetime format) + * @param string end (mysql datetime format) + * @param string forbidden categories (comma separated) + * @return count element ids + */ +function nb_updated_categories($start, $end) +{ + return custom_notification_query('count', 'updated_categories', $start, $end); } /** @@ -110,16 +272,19 @@ SELECT DISTINCT image_id */ function updated_categories($start, $end) { - global $user; - - $query = ' -SELECT DISTINCT category_id - FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON image_id = id - WHERE date_available > \''.$start.'\' - AND date_available <= \''.$end.'\' - AND category_id NOT IN ('.$user['forbidden_categories'].') -;'; - return array_from_query($query, 'category_id'); + return custom_notification_query('info', 'updated_categories', $start, $end); +} + +/** + * new registered users between two dates + * + * @param string start (mysql datetime format) + * @param string end (mysql datetime format) + * @return count user ids + */ +function nb_new_users($start, $end) +{ + return custom_notification_query('count', 'new_users', $start, $end); } /** @@ -131,13 +296,17 @@ SELECT DISTINCT category_id */ function new_users($start, $end) { - $query = ' -SELECT user_id - FROM '.USER_INFOS_TABLE.' - WHERE registration_date > \''.$start.'\' - AND registration_date <= \''.$end.'\' -;'; - return array_from_query($query, 'user_id'); + return custom_notification_query('info', 'new_users', $start, $end); +} + +/** + * currently waiting pictures + * + * @return count waiting ids + */ +function nb_waiting_elements() +{ + return custom_notification_query('count', 'waiting_elements', '', ''); } /** @@ -147,13 +316,32 @@ SELECT user_id */ function waiting_elements() { - $query = ' -SELECT id - FROM '.WAITING_TABLE.' - WHERE validated = \'false\' -;'; + return custom_notification_query('info', 'waiting_elements', $start, $end); +} - return array_from_query($query, 'id'); +/** + * There are new between two dates ? + * + * Informations : number of new comments, number of new elements, number of + * updated categories. Administrators are also informed about : number of + * unvalidated comments, number of new users (TODO : number of unvalidated + * elements) + * + * @param string start date (mysql datetime format) + * @param string end date (mysql datetime format) + * + * @return boolean : true if exist news else false + */ +function news_exists($start, $end) +{ + return ( + (nb_new_comments($start, $end) > 0) or + (nb_new_elements($start, $end) > 0) or + (nb_updated_categories($start, $end) > 0) or + ((is_admin()) and (nb_unvalidated_comments($end) > 0)) or + ((is_admin()) and (nb_new_users($start, $end) > 0)) or + ((is_admin()) and (nb_waiting_elements() > 0)) + ); } /** @@ -166,26 +354,26 @@ SELECT id * * @param string start date (mysql datetime format) * @param string end date (mysql datetime format) + * + * @return array of news */ function news($start, $end) { - global $user; - $news = array(); - - $nb_new_comments = count(new_comments($start, $end)); + + $nb_new_comments = nb_new_comments($start, $end); if ($nb_new_comments > 0) { array_push($news, sprintf(l10n('%d new comments'), $nb_new_comments)); } - $nb_new_elements = count(new_elements($start, $end)); + $nb_new_elements = nb_new_elements($start, $end); if ($nb_new_elements > 0) { array_push($news, sprintf(l10n('%d new elements'), $nb_new_elements)); } - $nb_updated_categories = count(updated_categories($start, $end)); + $nb_updated_categories = nb_updated_categories($start, $end); if ($nb_updated_categories > 0) { array_push($news, sprintf(l10n('%d categories updated'), @@ -194,20 +382,20 @@ function news($start, $end) if (is_admin()) { - $nb_unvalidated_comments = count(unvalidated_comments($end)); + $nb_unvalidated_comments = nb_unvalidated_comments($end); if ($nb_unvalidated_comments > 0) { array_push($news, sprintf(l10n('%d comments to validate'), $nb_unvalidated_comments)); } - $nb_new_users = count(new_users($start, $end)); + $nb_new_users = nb_new_users($start, $end); if ($nb_new_users > 0) { array_push($news, sprintf(l10n('%d new users'), $nb_new_users)); } - $nb_waiting_elements = count(waiting_elements()); + $nb_waiting_elements = nb_waiting_elements(); if ($nb_waiting_elements > 0) { array_push( -- cgit v1.2.3