- Timestamp:
- Nov 19, 2013, 7:45:48 PM (10 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/include/functions_notification.inc.php
r25018 r25578 22 22 // +-----------------------------------------------------------------------+ 23 23 24 / / +-----------------------------------------------------------------------+25 // | functions | 26 // +-----------------------------------------------------------------------+ 27 28 / *29 * get standard sql where in order to 30 * restict an filter caregories and images 31 32 * IMAGE_CATEGORY_TABLE muste named ic in the query33 * 34 * @param none35 * 36 * @ return string sql where37 * /38 function get_std_sql_where_restrict_filter($prefix_condition, $img_field='ic.image_id', $force_one_condition = false) 39 { 40 return get_sql_condition_FandF 41 (42 array43 ( 44 'forbidden_categories' => 'ic.category_id',45 'visible_categories' => 'ic.category_id',46 'visible_images' => $img_field47 ),48 $prefix_condition,49 $force_one_condition50 );51 } 52 53 /* 54 * Execute custom notification query 55 56 * @param string action ('count' or 'info')57 * @param string type of query ('new_comments', 'unvalidated_comments', 'new_elements', 'updated_categories', 'new_users')58 * @param string start (mysql datetime format)59 * @param string end (mysql datetime format)60 * 61 * @ return integer for action count62 * 63 */ 64 function custom_notification_query($action, $type, $start , $end)24 /** 25 * @package functions\notification 26 */ 27 28 // TODO : use a cache for all data returned by custom_notification_query() 29 30 31 /** 32 * Get standard sql where in order to restrict and filter categories and images. 33 * IMAGE_CATEGORY_TABLE must be named "ic" in the query 34 * 35 * @param string $prefix_condition 36 * @param string $img_field 37 * @param bool $force_one_condition 38 * @return string 39 */ 40 function get_std_sql_where_restrict_filter($prefix_condition, 41 $img_field = 'ic.image_id', 42 $force_one_condition = false) 43 { 44 return get_sql_condition_FandF( 45 array( 46 'forbidden_categories' => 'ic.category_id', 47 'visible_categories' => 'ic.category_id', 48 'visible_images' => $img_field 49 ), 50 $prefix_condition, 51 $force_one_condition 52 ); 53 } 54 55 /** 56 * Execute custom notification query. 57 * 58 * @param string $action 'count', 'info' 59 * @param string $type 'new_comments', 'unvalidated_comments', 'new_elements', 'updated_categories', 'new_users' 60 * @param string $start (mysql datetime format) 61 * @param string $end (mysql datetime format) 62 * @return int|array int for action count array for info 63 */ 64 function custom_notification_query($action, $type, $start=null, $end=null) 65 65 { 66 66 global $user; … … 69 69 { 70 70 case 'new_comments': 71 { 71 72 $query = ' 72 73 FROM '.COMMENTS_TABLE.' AS c 73 , '.IMAGE_CATEGORY_TABLE.' AS ic74 WHERE c.image_id = ic.image_id';74 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON c.image_id = ic.image_id 75 WHERE 1=1'; 75 76 if (!empty($start)) 76 77 { 77 $query.= '78 $query.= ' 78 79 AND c.validation_date > \''.$start.'\''; 79 80 } 80 81 if (!empty($end)) 81 82 { 82 $query.= '83 $query.= ' 83 84 AND c.validation_date <= \''.$end.'\''; 84 85 } 85 $query .= get_std_sql_where_restrict_filter('AND').' 86 ;'; 86 $query.= get_std_sql_where_restrict_filter('AND'); 87 87 break; 88 } 89 88 90 case 'unvalidated_comments': 91 { 89 92 $query = ' 90 93 FROM '.COMMENTS_TABLE.' … … 92 95 if (!empty($start)) 93 96 { 94 $query .= ' AND date> \''.$start.'\''; 97 $query.= ' 98 AND date > \''.$start.'\''; 95 99 } 96 100 if (!empty($end)) 97 101 { 98 $query .= ' AND date <= \''.$end.'\''; 99 } 100 $query .= ' AND validated = \'false\' 101 ;'; 102 $query.= ' 103 AND date <= \''.$end.'\''; 104 } 105 $query.= ' 106 AND validated = \'false\''; 102 107 break; 108 } 109 103 110 case 'new_elements': 111 { 104 112 $query = ' 105 FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON image_id = id 113 FROM '.IMAGES_TABLE.' 114 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON image_id = id 106 115 WHERE 1=1'; 107 116 if (!empty($start)) 108 117 { 109 $query .= ' AND date_available > \''.$start.'\''; 118 $query.= ' 119 AND date_available > \''.$start.'\''; 110 120 } 111 121 if (!empty($end)) 112 122 { 113 $query .= ' AND date_available <= \''.$end.'\''; 114 }115 $query .= get_std_sql_where_restrict_filter('AND', 'id').'116 ;';123 $query.= ' 124 AND date_available <= \''.$end.'\''; 125 } 126 $query.= get_std_sql_where_restrict_filter('AND', 'id'); 117 127 break; 128 } 129 118 130 case 'updated_categories': 131 { 119 132 $query = ' 120 FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON image_id = id 133 FROM '.IMAGES_TABLE.' 134 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON image_id = id 121 135 WHERE 1=1'; 122 136 if (!empty($start)) 123 137 { 124 $query .= ' AND date_available > \''.$start.'\''; 138 $query.= ' 139 AND date_available > \''.$start.'\''; 125 140 } 126 141 if (!empty($end)) 127 142 { 128 $query .= ' AND date_available <= \''.$end.'\''; 129 }130 $query .= get_std_sql_where_restrict_filter('AND', 'id').'131 ;';143 $query.= ' 144 AND date_available <= \''.$end.'\''; 145 } 146 $query.= get_std_sql_where_restrict_filter('AND', 'id'); 132 147 break; 148 } 149 133 150 case 'new_users': 151 { 134 152 $query = ' 135 153 FROM '.USER_INFOS_TABLE.' … … 137 155 if (!empty($start)) 138 156 { 139 $query .= ' AND registration_date > \''.$start.'\''; 157 $query.= ' 158 AND registration_date > \''.$start.'\''; 140 159 } 141 160 if (!empty($end)) 142 161 { 143 $query .= ' AND registration_date <= \''.$end.'\''; 144 } 145 $query .= ' 146 ;'; 162 $query.= ' 163 AND registration_date <= \''.$end.'\''; 164 } 147 165 break; 166 } 167 148 168 default: 149 // stop this function and return nothing 150 return; 151 break; 169 return null; // stop and return nothing 152 170 } 153 171 … … 155 173 { 156 174 case 'count': 175 { 157 176 switch($type) 158 177 { … … 172 191 $field_id = 'user_id'; 173 192 break; 174 }175 $query = 'SELECT count(distinct '.$field_id.') as CountId176 '.$query;177 list($count) = pwg_db_fetch_row(pwg_query($query));178 return $count;179 180 break; 193 } 194 $query = 'SELECT COUNT(DISTINCT '.$field_id.') '.$query.';'; 195 list($count) = pwg_db_fetch_row(pwg_query($query)); 196 return $count; 197 break; 198 } 199 181 200 case 'info': 201 { 182 202 switch($type) 183 203 { 184 204 case 'new_comments': 185 $field s = array('c.id');205 $field_id = 'c.id'; 186 206 break; 187 207 case 'unvalidated_comments': 188 $field s = array('id');208 $field_id = 'id'; 189 209 break; 190 210 case 'new_elements': 191 $field s = array('image_id');211 $field_id = 'image_id'; 192 212 break; 193 213 case 'updated_categories': 194 $field s = array('category_id');214 $field_id = 'category_id'; 195 215 break; 196 216 case 'new_users': 197 $fields = array('user_id'); 198 break; 199 } 200 201 $query = 'SELECT distinct '.implode(', ', $fields).' 202 '.$query; 203 $result = pwg_query($query); 204 205 $infos = array(); 206 207 while ($row = pwg_db_fetch_assoc($result)) 208 { 209 $infos[] = $row; 210 } 211 212 return $infos; 213 214 break; 215 } 216 217 //return is done on previous switch($action) 218 } 219 220 /** 221 * new comments between two dates, according to authorized categories 222 * 223 * @param string start (mysql datetime format) 224 * @param string end (mysql datetime format) 225 * @param string forbidden categories (comma separated) 226 * @return count comment ids 227 */ 228 function nb_new_comments($start, $end) 217 $field_id = 'user_id'; 218 break; 219 } 220 $query = 'SELECT DISTINCT '.$field_id.' '.$query.';'; 221 $infos = array_from_query($query); 222 return $infos; 223 break; 224 } 225 226 default: 227 return null; // stop and return nothing 228 } 229 } 230 231 /** 232 * Returns number of new comments between two dates. 233 * 234 * @param string $start (mysql datetime format) 235 * @param string $end (mysql datetime format) 236 * @return int 237 */ 238 function nb_new_comments($start=null, $end=null) 229 239 { 230 240 return custom_notification_query('count', 'new_comments', $start, $end); … … 232 242 233 243 /** 234 * new comments between two dates, according to authorized categories 235 * 236 * @param string start (mysql datetime format) 237 * @param string end (mysql datetime format) 238 * @param string forbidden categories (comma separated) 239 * @return array comment ids 240 */ 241 function new_comments($start, $end) 244 * Returns new comments between two dates. 245 * 246 * @param string $start (mysql datetime format) 247 * @param string $end (mysql datetime format) 248 * @return int[] comment ids 249 */ 250 function new_comments($start=null, $end=null) 242 251 { 243 252 return custom_notification_query('info', 'new_comments', $start, $end); … … 245 254 246 255 /** 247 * unvalidated at a precise date 248 * 249 * Comments that are registered and not validated yet on a precise date 250 * 251 * @param string start (mysql datetime format) 252 * @param string end (mysql datetime format) 253 * @return count comment ids 254 */ 255 function nb_unvalidated_comments($start, $end) 256 * Returns number of unvalidated comments between two dates. 257 * 258 * @param string $start (mysql datetime format) 259 * @param string $end (mysql datetime format) 260 * @return int 261 */ 262 function nb_unvalidated_comments($start=null, $end=null) 256 263 { 257 264 return custom_notification_query('count', 'unvalidated_comments', $start, $end); … … 260 267 261 268 /** 262 * new elements between two dates, according to authorized categories 263 * 264 * @param string start (mysql datetime format) 265 * @param string end (mysql datetime format) 266 * @param string forbidden categories (comma separated) 267 * @return count element ids 268 */ 269 function nb_new_elements($start, $end) 269 * Returns number of new photos between two dates. 270 * 271 * @param string $start (mysql datetime format) 272 * @param string $end (mysql datetime format) 273 * @return int 274 */ 275 function nb_new_elements($start=null, $end=null) 270 276 { 271 277 return custom_notification_query('count', 'new_elements', $start, $end); … … 273 279 274 280 /** 275 * new elements between two dates, according to authorized categories 276 * 277 * @param string start (mysql datetime format) 278 * @param string end (mysql datetime format) 279 * @param string forbidden categories (comma separated) 280 * @return array element ids 281 */ 282 function new_elements($start, $end) 281 * Returns new photos between two dates.es 282 * 283 * @param string $start (mysql datetime format) 284 * @param string $end (mysql datetime format) 285 * @return int[] photos ids 286 */ 287 function new_elements($start=null, $end=null) 283 288 { 284 289 return custom_notification_query('info', 'new_elements', $start, $end); … … 286 291 287 292 /** 288 * updated categories between two dates, according to authorized categories 289 * 290 * @param string start (mysql datetime format) 291 * @param string end (mysql datetime format) 292 * @param string forbidden categories (comma separated) 293 * @return count element ids 294 */ 295 function nb_updated_categories($start, $end) 293 * Returns number of updated categories between two dates. 294 * 295 * @param string $start (mysql datetime format) 296 * @param string $end (mysql datetime format) 297 * @return int 298 */ 299 function nb_updated_categories($start=null, $end=null) 296 300 { 297 301 return custom_notification_query('count', 'updated_categories', $start, $end); … … 299 303 300 304 /** 301 * updated categories between two dates, according to authorized categories 302 * 303 * @param string start (mysql datetime format) 304 * @param string end (mysql datetime format) 305 * @param string forbidden categories (comma separated) 306 * @return array element ids 307 */ 308 function updated_categories($start, $end) 305 * Returns updated categories between two dates. 306 * 307 * @param string $start (mysql datetime format) 308 * @param string $end (mysql datetime format) 309 * @return int[] categories ids 310 */ 311 function updated_categories($start=null, $end=null) 309 312 { 310 313 return custom_notification_query('info', 'updated_categories', $start, $end); … … 312 315 313 316 /** 314 * new registered users between two dates315 * 316 * @param string start (mysql datetime format)317 * @param string end (mysql datetime format)318 * @return count user ids319 */ 320 function nb_new_users($start , $end)317 * Returns number of new users between two dates. 318 * 319 * @param string $start (mysql datetime format) 320 * @param string $end (mysql datetime format) 321 * @return int 322 */ 323 function nb_new_users($start=null, $end=null) 321 324 { 322 325 return custom_notification_query('count', 'new_users', $start, $end); … … 324 327 325 328 /** 326 * new registered users between two dates327 * 328 * @param string start (mysql datetime format)329 * @param string end (mysql datetime format)330 * @return arrayuser ids331 */ 332 function new_users($start , $end)329 * Returns new users between two dates. 330 * 331 * @param string $start (mysql datetime format) 332 * @param string $end (mysql datetime format) 333 * @return int[] user ids 334 */ 335 function new_users($start=null, $end=null) 333 336 { 334 337 return custom_notification_query('info', 'new_users', $start, $end); … … 336 339 337 340 /** 338 * There are new between two dates ? 339 * 340 * Informations : number of new comments, number of new elements, number of 341 * updated categories. Administrators are also informed about : number of 342 * unvalidated comments, number of new users (TODO : number of unvalidated 343 * elements) 344 * 345 * @param string start date (mysql datetime format) 346 * @param string end date (mysql datetime format) 347 * 348 * @return boolean : true if exist news else false 349 */ 350 function news_exists($start, $end) 341 * Returns if there was new activity between two dates. 342 * 343 * Takes in account: number of new comments, number of new elements, number of 344 * updated categories. Administrators are also informed about: number of 345 * unvalidated comments, number of new users. 346 * TODO : number of unvalidated elements 347 * 348 * @param string $start (mysql datetime format) 349 * @param string $end (mysql datetime format) 350 * @return boolean 351 */ 352 function news_exists($start=null, $end=null) 351 353 { 352 354 return ( … … 360 362 /** 361 363 * Formats a news line and adds it to the array (e.g. '5 new elements') 362 */ 363 function add_news_line(&$news, $count, $singular_fmt_key, $plural_fmt_key, $url='', $add_url=false) 364 * 365 * @param array $news 366 * @param int $count 367 * @param string $singular_key 368 * @param string $plural_key 369 * @param string $url 370 * @param bool $add_url 371 */ 372 function add_news_line(&$news, $count, $singular_key, $plural_key, $url='', $add_url=false) 364 373 { 365 374 if ($count > 0) 366 375 { 367 $line = l10n_dec($singular_ fmt_key, $plural_fmt_key, $count);376 $line = l10n_dec($singular_key, $plural_key, $count); 368 377 if ($add_url and !empty($url) ) 369 378 { … … 375 384 376 385 /** 377 * What's new between two dates ? 378 * 379 * Informations : number of new comments, number of new elements, number of 380 * updated categories. Administrators are also informed about : number of 381 * unvalidated comments, number of new users (TODO : number of unvalidated 382 * elements) 383 * 384 * @param string start date (mysql datetime format) 385 * @param string end date (mysql datetime format) 386 * @param bool exclude_img_cats if true, no info about new images/categories 387 * @param bool add_url add html A link around news 388 * 389 * @return array of news 390 */ 391 function news($start, $end, $exclude_img_cats=false, $add_url=false) 386 * Returns new activity between two dates. 387 * 388 * Takes in account: number of new comments, number of new elements, number of 389 * updated categories. Administrators are also informed about: number of 390 * unvalidated comments, number of new users. 391 * TODO : number of unvalidated elements 392 * 393 * @param string $start (mysql datetime format) 394 * @param string $end (mysql datetime format) 395 * @param bool $exclude_img_cats if true, no info about new images/categories 396 * @param bool $add_url add html link around news 397 * @return array 398 */ 399 function news($start=null, $end=null, $exclude_img_cats=false, $add_url=false) 392 400 { 393 401 $news = array(); … … 426 434 427 435 /** 428 * returns information about recently published elements grouped by post date 429 * @param int max_dates maximum returned number of recent dates 430 * @param int max_elements maximum returned number of elements per date 431 * @param int max_cats maximum returned number of categories per date 436 * Returns information about recently published elements grouped by post date. 437 * 438 * @param int $max_dates maximum number of recent dates 439 * @param int $max_elements maximum number of elements per date 440 * @param int $max_cats maximum number of categories per date 441 * @return array 432 442 */ 433 443 function get_recent_post_dates($max_dates, $max_elements, $max_cats) … … 438 448 439 449 $query = ' 440 SELECT date_available, 441 COUNT(DISTINCT id) AS nb_elements, 442 COUNT(DISTINCT category_id) AS nb_cats 450 SELECT 451 date_available, 452 COUNT(DISTINCT id) AS nb_elements, 453 COUNT(DISTINCT category_id) AS nb_cats 443 454 FROM '.IMAGES_TABLE.' i INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id=image_id 444 455 '.$where_sql.' … … 447 458 LIMIT '.$max_dates.' 448 459 ;'; 449 $result = pwg_query($query); 450 $dates = array(); 451 while ($row = pwg_db_fetch_assoc($result)) 452 { 453 $dates[] = $row; 454 } 460 $dates = array_from_query($query); 455 461 456 462 for ($i=0; $i<count($dates); $i++) … … 460 466 $query = ' 461 467 SELECT DISTINCT i.* 462 FROM '.IMAGES_TABLE.' i INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id=image_id 468 FROM '.IMAGES_TABLE.' i 469 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id=image_id 463 470 '.$where_sql.' 464 471 AND date_available=\''.$dates[$i]['date_available'].'\' … … 466 473 LIMIT '.$max_elements.' 467 474 ;'; 468 $dates[$i]['elements'] = array(); 469 $result = pwg_query($query); 470 while ($row = pwg_db_fetch_assoc($result)) 471 { 472 $dates[$i]['elements'][] = $row; 473 } 475 $dates[$i]['elements'] = array_from_query($query); 474 476 } 475 477 … … 477 479 {// get some categories ... 478 480 $query = ' 479 SELECT DISTINCT c.uppercats, COUNT(DISTINCT i.id) AS img_count 480 FROM '.IMAGES_TABLE.' i INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON i.id=image_id 481 SELECT 482 DISTINCT c.uppercats, 483 COUNT(DISTINCT i.id) AS img_count 484 FROM '.IMAGES_TABLE.' i 485 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON i.id=image_id 481 486 INNER JOIN '.CATEGORIES_TABLE.' c ON c.id=category_id 482 487 '.$where_sql.' … … 486 491 LIMIT '.$max_cats.' 487 492 ;'; 488 $dates[$i]['categories'] = array(); 489 $result = pwg_query($query); 490 while ($row = pwg_db_fetch_assoc($result)) 491 { 492 $dates[$i]['categories'][] = $row; 493 } 494 } 495 } 493 $dates[$i]['categories'] = array_from_query($query); 494 } 495 } 496 496 497 return $dates; 497 498 } 498 499 499 /* 500 Call function get_recent_post_dates but 501 the parameters to be passed to the function, as an indexed array. 502 503 */ 500 /** 501 * Returns information about recently published elements grouped by post date. 502 * Same as get_recent_post_dates() but parameters as an indexed array. 503 * @see get_recent_post_dates() 504 * 505 * @param array $args 506 * @return array 507 */ 504 508 function get_recent_post_dates_array($args) 505 509 { 506 return 507 get_recent_post_dates 508 ( 509 (empty($args['max_dates']) ? 3 : $args['max_dates']), 510 (empty($args['max_elements']) ? 3 : $args['max_elements']), 511 (empty($args['max_cats']) ? 3 : $args['max_cats']) 510 return get_recent_post_dates( 511 (empty($args['max_dates']) ? 3 : $args['max_dates']), 512 (empty($args['max_elements']) ? 3 : $args['max_elements']), 513 (empty($args['max_cats']) ? 3 : $args['max_cats']) 512 514 ); 513 515 } … … 515 517 516 518 /** 517 * returns html description about recently published elements grouped by post date 518 * @param $date_detail: selected date computed by get_recent_post_dates function 519 * Returns html description about recently published elements grouped by post date. 520 * TODO : clean up HTML output, currently messy and invalid ! 521 * 522 * @param array $date_detail returned value of get_recent_post_dates() 523 * @return string 519 524 */ 520 525 function get_html_description_recent_post_date($date_detail) … … 568 573 569 574 /** 570 * returns title about recently published elements grouped by post date 571 * @param $date_detail: selected date computed by get_recent_post_dates function 575 * Returns title about recently published elements grouped by post date. 576 * 577 * @param array $date_detail returned value of get_recent_post_dates() 578 * @return string 572 579 */ 573 580 function get_title_recent_post_date($date_detail) … … 584 591 } 585 592 586 if(!function_exists("strptime")) 587 { 588 function strptime($date, $fmt) 589 { 590 if ($fmt != '%Y-%m-%d %H:%M:%S') 591 die('Invalid strptime format '.$fmt); 592 list($y,$m,$d,$H,$M,$S) = preg_split('/[-: ]/', $date); 593 $res = localtime( mktime($H,$M,$S,$m,$d,$y), true ); 594 return $res; 595 } 596 } 593 if (!function_exists('strptime')) 594 { 595 function strptime($date, $fmt) 596 { 597 if ($fmt != '%Y-%m-%d %H:%M:%S') 598 die('Invalid strptime format '.$fmt); 599 list($y,$m,$d,$H,$M,$S) = preg_split('/[-: ]/', $date); 600 $res = localtime( mktime($H,$M,$S,$m,$d,$y), true ); 601 return $res; 602 } 603 } 604 597 605 ?>
Note: See TracChangeset
for help on using the changeset viewer.