Changeset 1112


Ignore:
Timestamp:
Mar 29, 2006, 12:33:29 AM (18 years ago)
Author:
rub
Message:

[NBM] Step 5: Change notification queries in order to try to optimize treatment duration.

Location:
trunk
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • trunk/admin/notification_by_mail.php

    r1105 r1112  
    231231      }
    232232
    233       $message = '';
    234       $news = news($row['last_send'], $dbnow);
    235       if (count($news) > 0)
    236       {
    237         array_push($return_list, $row);
    238 
    239         if ($is_action_send)
     233      if ($is_action_send)
     234      {
     235        $message = '';
     236        $news = news($row['last_send'], $dbnow);
     237        if (count($news) > 0)
    240238        {
     239          array_push($return_list, $row);
     240
    241241          $subject = '['.$conf['gallery_title'].']: '.l10n('nbm_ContentObject');
    242242          $message .= sprintf(l10n('nbm_ContentHello'), $row['username']).",\n\n";
     
    282282        }
    283283      }
     284      else
     285      {
     286        if (news_exists($row['last_send'], $dbnow))
     287        {
     288          array_push($return_list, $row);
     289        }
     290      }
    284291    }
    285292
  • trunk/include/functions_notification.inc.php

    r1070 r1112  
    2626// +-----------------------------------------------------------------------+
    2727
    28 
    29 /**
    30  * Extract news fonctions of feed.php
    31  */
    32 
    3328// +-----------------------------------------------------------------------+
    3429// |                               functions                               |
    3530// +-----------------------------------------------------------------------+
    3631
    37 /**
    38  * new comments between two dates, according to authorized categories
    39  *
    40  * @param string start (mysql datetime format)
    41  * @param string end (mysql datetime format)
    42  * @param string forbidden categories (comma separated)
    43  * @return array comment ids
    44  */
    45 function new_comments($start, $end)
     32/*
     33 * Execute custom notification query
     34 *
     35 * @param string action ('count' or 'info')
     36 * @param string type of query ('new_comments', 'unvalidated_comments', 'new_elements', 'updated_categories', ' new_users', 'waiting_elements')
     37 * @param string start (mysql datetime format)
     38 * @param string end (mysql datetime format)
     39 *
     40 * @return integer for action count
     41 *         array for info
     42 */
     43function custom_notification_query($action, $type, $start, $end)
    4644{
    4745  global $user;
    48  
    49   $query = '
    50 SELECT DISTINCT c.id AS comment_id
     46
     47  switch($type)
     48  {
     49    case 'new_comments':
     50      $query = '
    5151  FROM '.COMMENTS_TABLE.' AS c
    5252     , '.IMAGE_CATEGORY_TABLE.' AS ic
     
    5656    AND category_id NOT IN ('.$user['forbidden_categories'].')
    5757;';
    58   return array_from_query($query, 'comment_id');
    59 }
    60 
    61 /**
    62  * unvalidated at a precise date
    63  *
    64  * Comments that are registered and not validated yet on a precise date
    65  *
    66  * @param string date (mysql datetime format)
    67  * @return array comment ids
    68  */
    69 function unvalidated_comments($date)
    70 {
    71   $query = '
    72 SELECT DISTINCT id
     58      break;
     59    case 'unvalidated_comments':
     60      $query = '
    7361  FROM '.COMMENTS_TABLE.'
    74   WHERE date <= \''.$date.'\'
     62  WHERE date <= \''.$end.'\'
    7563    AND (validated = \'false\'
    76          OR validation_date > \''.$date.'\')
    77 ;';
    78   return array_from_query($query, 'id');
    79 }
    80 
    81 /**
    82  * new elements between two dates, according to authorized categories
    83  *
    84  * @param string start (mysql datetime format)
    85  * @param string end (mysql datetime format)
    86  * @param string forbidden categories (comma separated)
    87  * @return array element ids
    88  */
    89 function new_elements($start, $end)
    90 {
    91   global $user;
    92  
    93   $query = '
    94 SELECT DISTINCT image_id
     64         OR validation_date > \''.$end.'\')
     65;';
     66      break;
     67    case 'new_elements':
     68      $query = '
    9569  FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON image_id = id
    9670  WHERE date_available > \''.$start.'\'
     
    9872    AND category_id NOT IN ('.$user['forbidden_categories'].')
    9973;';
    100   return array_from_query($query, 'image_id');
    101 }
    102 
    103 /**
    104  * updated categories between two dates, according to authorized categories
    105  *
    106  * @param string start (mysql datetime format)
    107  * @param string end (mysql datetime format)
    108  * @param string forbidden categories (comma separated)
    109  * @return array element ids
    110  */
    111 function updated_categories($start, $end)
    112 {
    113   global $user;
    114  
    115   $query = '
    116 SELECT DISTINCT category_id
     74      break;
     75    case 'updated_categories':
     76      $query = '
    11777  FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON image_id = id
    11878  WHERE date_available > \''.$start.'\'
     
    12080    AND category_id NOT IN ('.$user['forbidden_categories'].')
    12181;';
    122   return array_from_query($query, 'category_id');
    123 }
    124 
    125 /**
    126  * new registered users between two dates
    127  *
    128  * @param string start (mysql datetime format)
    129  * @param string end (mysql datetime format)
    130  * @return array user ids
    131  */
    132 function new_users($start, $end)
    133 {
    134   $query = '
    135 SELECT user_id
     82      break;
     83    case ' new_users':
     84      $query = '
    13685  FROM '.USER_INFOS_TABLE.'
    13786  WHERE registration_date > \''.$start.'\'
    13887    AND registration_date <= \''.$end.'\'
    13988;';
    140   return array_from_query($query, 'user_id');
    141 }
    142 
    143 /**
    144  * currently waiting pictures
    145  *
    146  * @return array waiting ids
    147  */
    148 function waiting_elements()
    149 {
    150   $query = '
    151 SELECT id
     89      break;
     90    case 'waiting_elements':
     91      $query = '
    15292  FROM '.WAITING_TABLE.'
    15393  WHERE validated = \'false\'
    15494;';
    155 
    156   return array_from_query($query, 'id');
    157 }
    158 
    159 /**
    160  * What's new between two dates ?
     95      break;
     96    default:
     97      // stop this function and return nothing
     98      return;
     99      break;
     100  }
     101
     102  switch($action)
     103  {
     104    case 'count':
     105      switch($type)
     106      {
     107        case 'new_comments':
     108          $field_id = 'c.id';
     109          break;
     110        case 'unvalidated_comments':
     111          $field_id = 'id';
     112          break;
     113        case 'new_elements':
     114          $field_id = 'image_id';
     115          break;
     116        case 'updated_categories':
     117          $field_id = 'category_id';
     118          break;
     119        case ' new_users':
     120          $field_id = 'user_id';
     121          break;
     122        case 'waiting_elements':
     123          $field_id = 'id';
     124          break;
     125    }
     126    $query = 'SELECT count(distinct '.$field_id.') as CountId
     127'.$query;
     128    list($count) = mysql_fetch_array(pwg_query($query));
     129    return $count;
     130   
     131    break;
     132    case 'info':
     133      switch($type)
     134      {
     135        case 'new_comments':
     136          $fields = array('c.id');
     137          break;
     138        case 'unvalidated_comments':
     139          $fields = array('id');
     140          break;
     141        case 'new_elements':
     142          $fields = array('image_id');
     143          break;
     144        case 'updated_categories':
     145          $fields = array('category_id');
     146          break;
     147        case ' new_users':
     148          $fields = array('user_id');
     149          break;
     150        case 'waiting_elements':
     151          $fields = array('id');
     152          break;
     153      }
     154
     155    $query = 'SELECT distinct '.implode(', ', $fields).'
     156'.$query;
     157    $result = pwg_query($query);
     158
     159    $infos = array();
     160 
     161    while ($row = mysql_fetch_array($result))
     162    {
     163      array_push($infos, $row);
     164    }
     165
     166    return $infos;
     167
     168    break;
     169  }
     170
     171  //return is done on previous switch($action)
     172}
     173
     174/**
     175 * new comments between two dates, according to authorized categories
     176 *
     177 * @param string start (mysql datetime format)
     178 * @param string end (mysql datetime format)
     179 * @param string forbidden categories (comma separated)
     180 * @return count comment ids
     181 */
     182function nb_new_comments($start, $end)
     183{
     184  return custom_notification_query('count', 'new_comments', $start, $end);
     185}
     186
     187/**
     188 * new comments between two dates, according to authorized categories
     189 *
     190 * @param string start (mysql datetime format)
     191 * @param string end (mysql datetime format)
     192 * @param string forbidden categories (comma separated)
     193 * @return array comment ids
     194 */
     195function new_comments($start, $end)
     196{
     197  return custom_notification_query('info', 'new_comments', $start, $end);
     198}
     199
     200/**
     201 * unvalidated at a precise date
     202 *
     203 * Comments that are registered and not validated yet on a precise date
     204 *
     205 * @param string date (mysql datetime format)
     206 * @return count comment ids
     207 */
     208function nb_unvalidated_comments($date)
     209{
     210  return custom_notification_query('count', 'unvalidated_comments', $date, $date);
     211}
     212
     213/**
     214 * unvalidated at a precise date
     215 *
     216 * Comments that are registered and not validated yet on a precise date
     217 *
     218 * @param string date (mysql datetime format)
     219 * @return array comment ids
     220 */
     221function unvalidated_comments($date)
     222{
     223  return custom_notification_query('info', 'unvalidated_comments', $start, $end);
     224}
     225
     226/**
     227 * new elements between two dates, according to authorized categories
     228 *
     229 * @param string start (mysql datetime format)
     230 * @param string end (mysql datetime format)
     231 * @param string forbidden categories (comma separated)
     232 * @return count element ids
     233 */
     234function nb_new_elements($start, $end)
     235{
     236  return custom_notification_query('count', 'new_elements', $start, $end);
     237}
     238
     239/**
     240 * new elements between two dates, according to authorized categories
     241 *
     242 * @param string start (mysql datetime format)
     243 * @param string end (mysql datetime format)
     244 * @param string forbidden categories (comma separated)
     245 * @return array element ids
     246 */
     247function new_elements($start, $end)
     248{
     249  return custom_notification_query('info', 'new_elements', $start, $end);
     250}
     251
     252/**
     253 * updated categories between two dates, according to authorized categories
     254 *
     255 * @param string start (mysql datetime format)
     256 * @param string end (mysql datetime format)
     257 * @param string forbidden categories (comma separated)
     258 * @return count element ids
     259 */
     260function nb_updated_categories($start, $end)
     261{
     262  return custom_notification_query('count', 'updated_categories', $start, $end);
     263}
     264
     265/**
     266 * updated categories between two dates, according to authorized categories
     267 *
     268 * @param string start (mysql datetime format)
     269 * @param string end (mysql datetime format)
     270 * @param string forbidden categories (comma separated)
     271 * @return array element ids
     272 */
     273function updated_categories($start, $end)
     274{
     275  return custom_notification_query('info', 'updated_categories', $start, $end);
     276}
     277
     278/**
     279 * new registered users between two dates
     280 *
     281 * @param string start (mysql datetime format)
     282 * @param string end (mysql datetime format)
     283 * @return count user ids
     284 */
     285function nb_new_users($start, $end)
     286{
     287  return custom_notification_query('count', 'new_users', $start, $end);
     288}
     289
     290/**
     291 * new registered users between two dates
     292 *
     293 * @param string start (mysql datetime format)
     294 * @param string end (mysql datetime format)
     295 * @return array user ids
     296 */
     297function new_users($start, $end)
     298{
     299  return custom_notification_query('info', 'new_users', $start, $end);
     300}
     301
     302/**
     303 * currently waiting pictures
     304 *
     305 * @return count waiting ids
     306 */
     307function nb_waiting_elements()
     308{
     309  return custom_notification_query('count', 'waiting_elements', '', '');
     310}
     311
     312/**
     313 * currently waiting pictures
     314 *
     315 * @return array waiting ids
     316 */
     317function waiting_elements()
     318{
     319  return custom_notification_query('info', 'waiting_elements', $start, $end);
     320}
     321
     322/**
     323 * There are new between two dates ?
    161324 *
    162325 * Informations : number of new comments, number of new elements, number of
     
    167330 * @param string start date (mysql datetime format)
    168331 * @param string end date (mysql datetime format)
     332 *
     333 * @return boolean : true if exist news else false
     334 */
     335function news_exists($start, $end)
     336{
     337  return (
     338          (nb_new_comments($start, $end) > 0) or
     339          (nb_new_elements($start, $end) > 0) or
     340          (nb_updated_categories($start, $end) > 0) or
     341          ((is_admin()) and (nb_unvalidated_comments($end) > 0)) or
     342          ((is_admin()) and (nb_new_users($start, $end) > 0)) or
     343          ((is_admin()) and (nb_waiting_elements() > 0))
     344        );
     345}
     346
     347/**
     348 * What's new between two dates ?
     349 *
     350 * Informations : number of new comments, number of new elements, number of
     351 * updated categories. Administrators are also informed about : number of
     352 * unvalidated comments, number of new users (TODO : number of unvalidated
     353 * elements)
     354 *
     355 * @param string start date (mysql datetime format)
     356 * @param string end date (mysql datetime format)
     357 *
     358 * @return array of news
    169359 */
    170360function news($start, $end)
    171361{
    172   global $user;
    173 
    174362  $news = array();
    175  
    176   $nb_new_comments = count(new_comments($start, $end));
     363
     364  $nb_new_comments = nb_new_comments($start, $end);
    177365  if ($nb_new_comments > 0)
    178366  {
     
    180368  }
    181369
    182   $nb_new_elements = count(new_elements($start, $end));
     370  $nb_new_elements = nb_new_elements($start, $end);
    183371  if ($nb_new_elements > 0)
    184372  {
     
    186374  }
    187375
    188   $nb_updated_categories = count(updated_categories($start, $end));
     376  $nb_updated_categories = nb_updated_categories($start, $end);
    189377  if ($nb_updated_categories > 0)
    190378  {
     
    195383  if (is_admin())
    196384  {
    197     $nb_unvalidated_comments = count(unvalidated_comments($end));
     385    $nb_unvalidated_comments = nb_unvalidated_comments($end);
    198386    if ($nb_unvalidated_comments > 0)
    199387    {
     
    202390    }
    203391
    204     $nb_new_users = count(new_users($start, $end));
     392    $nb_new_users = nb_new_users($start, $end);
    205393    if ($nb_new_users > 0)
    206394    {
     
    208396    }
    209397
    210     $nb_waiting_elements = count(waiting_elements());
     398    $nb_waiting_elements = nb_waiting_elements();
    211399    if ($nb_waiting_elements > 0)
    212400    {
Note: See TracChangeset for help on using the changeset viewer.