1<?php 2// +-----------------------------------------------------------------------+ 3// | This file is part of Piwigo. | 4// | | 5// | For copyright and license information, please view the COPYING.txt | 6// | file that was distributed with this source code. | 7// +-----------------------------------------------------------------------+ 8 9/** 10 * @package functions\notification 11 */ 12 13 14/** 15 * Get standard sql where in order to restrict and filter categories and images. 16 * IMAGE_CATEGORY_TABLE must be named "ic" in the query 17 * 18 * @param string $prefix_condition 19 * @param string $img_field 20 * @param bool $force_one_condition 21 * @return string 22 */ 23function get_std_sql_where_restrict_filter($prefix_condition, 24 $img_field = 'ic.image_id', 25 $force_one_condition = false) 26{ 27 return get_sql_condition_FandF( 28 array( 29 'forbidden_categories' => 'ic.category_id', 30 'visible_categories' => 'ic.category_id', 31 'visible_images' => $img_field 32 ), 33 $prefix_condition, 34 $force_one_condition 35 ); 36} 37 38/** 39 * Execute custom notification query. 40 * @todo use a cache for all data returned by custom_notification_query() 41 * 42 * @param string $action 'count', 'info' 43 * @param string $type 'new_comments', 'unvalidated_comments', 'new_elements', 'updated_categories', 'new_users' 44 * @param string $start (mysql datetime format) 45 * @param string $end (mysql datetime format) 46 * @return int|array int for action count array for info 47 */ 48function custom_notification_query($action, $type, $start=null, $end=null) 49{ 50 global $user; 51 52 switch($type) 53 { 54 case 'new_comments': 55 { 56 $query = ' 57 FROM '.COMMENTS_TABLE.' AS c 58 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON c.image_id = ic.image_id 59 WHERE 1=1'; 60 if (!empty($start)) 61 { 62 $query.= ' 63 AND c.validation_date > \''.$start.'\''; 64 } 65 if (!empty($end)) 66 { 67 $query.= ' 68 AND c.validation_date <= \''.$end.'\''; 69 } 70 $query.= get_std_sql_where_restrict_filter('AND'); 71 break; 72 } 73 74 case 'unvalidated_comments': 75 { 76 $query = ' 77 FROM '.COMMENTS_TABLE.' 78 WHERE 1=1'; 79 if (!empty($start)) 80 { 81 $query.= ' 82 AND date > \''.$start.'\''; 83 } 84 if (!empty($end)) 85 { 86 $query.= ' 87 AND date <= \''.$end.'\''; 88 } 89 $query.= ' 90 AND validated = \'false\''; 91 break; 92 } 93 94 case 'new_elements': 95 { 96 $query = ' 97 FROM '.IMAGES_TABLE.' 98 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON image_id = id 99 WHERE 1=1'; 100 if (!empty($start)) 101 { 102 $query.= ' 103 AND date_available > \''.$start.'\''; 104 } 105 if (!empty($end)) 106 { 107 $query.= ' 108 AND date_available <= \''.$end.'\''; 109 } 110 $query.= get_std_sql_where_restrict_filter('AND', 'id'); 111 break; 112 } 113 114 case 'updated_categories': 115 { 116 $query = ' 117 FROM '.IMAGES_TABLE.' 118 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON image_id = id 119 WHERE 1=1'; 120 if (!empty($start)) 121 { 122 $query.= ' 123 AND date_available > \''.$start.'\''; 124 } 125 if (!empty($end)) 126 { 127 $query.= ' 128 AND date_available <= \''.$end.'\''; 129 } 130 $query.= get_std_sql_where_restrict_filter('AND', 'id'); 131 break; 132 } 133 134 case 'new_users': 135 { 136 $query = ' 137 FROM '.USER_INFOS_TABLE.' 138 WHERE 1=1'; 139 if (!empty($start)) 140 { 141 $query.= ' 142 AND registration_date > \''.$start.'\''; 143 } 144 if (!empty($end)) 145 { 146 $query.= ' 147 AND registration_date <= \''.$end.'\''; 148 } 149 break; 150 } 151 152 default: 153 return null; // stop and return nothing 154 } 155 156 switch($action) 157 { 158 case 'count': 159 { 160 switch($type) 161 { 162 case 'new_comments': 163 $field_id = 'c.id'; 164 break; 165 case 'unvalidated_comments': 166 $field_id = 'id'; 167 break; 168 case 'new_elements': 169 $field_id = 'image_id'; 170 break; 171 case 'updated_categories': 172 $field_id = 'category_id'; 173 break; 174 case 'new_users': 175 $field_id = 'user_id'; 176 break; 177 } 178 $query = 'SELECT COUNT(DISTINCT '.$field_id.') '.$query.';'; 179 list($count) = pwg_db_fetch_row(pwg_query($query)); 180 return $count; 181 break; 182 } 183 184 case 'info': 185 { 186 switch($type) 187 { 188 case 'new_comments': 189 $field_id = 'c.id'; 190 break; 191 case 'unvalidated_comments': 192 $field_id = 'id'; 193 break; 194 case 'new_elements': 195 $field_id = 'image_id'; 196 break; 197 case 'updated_categories': 198 $field_id = 'category_id'; 199 break; 200 case 'new_users': 201 $field_id = 'user_id'; 202 break; 203 } 204 $query = 'SELECT DISTINCT '.$field_id.' '.$query.';'; 205 $infos = query2array($query); 206 return $infos; 207 break; 208 } 209 210 default: 211 return null; // stop and return nothing 212 } 213} 214 215/** 216 * Returns number of new comments between two dates. 217 * 218 * @param string $start (mysql datetime format) 219 * @param string $end (mysql datetime format) 220 * @return int 221 */ 222function nb_new_comments($start=null, $end=null) 223{ 224 return custom_notification_query('count', 'new_comments', $start, $end); 225} 226 227/** 228 * Returns new comments between two dates. 229 * 230 * @param string $start (mysql datetime format) 231 * @param string $end (mysql datetime format) 232 * @return int[] comment ids 233 */ 234function new_comments($start=null, $end=null) 235{ 236 return custom_notification_query('info', 'new_comments', $start, $end); 237} 238 239/** 240 * Returns number of unvalidated comments between two dates. 241 * 242 * @param string $start (mysql datetime format) 243 * @param string $end (mysql datetime format) 244 * @return int 245 */ 246function nb_unvalidated_comments($start=null, $end=null) 247{ 248 return custom_notification_query('count', 'unvalidated_comments', $start, $end); 249} 250 251 252/** 253 * Returns number of new photos between two dates. 254 * 255 * @param string $start (mysql datetime format) 256 * @param string $end (mysql datetime format) 257 * @return int 258 */ 259function nb_new_elements($start=null, $end=null) 260{ 261 return custom_notification_query('count', 'new_elements', $start, $end); 262} 263 264/** 265 * Returns new photos between two dates.es 266 * 267 * @param string $start (mysql datetime format) 268 * @param string $end (mysql datetime format) 269 * @return int[] photos ids 270 */ 271function new_elements($start=null, $end=null) 272{ 273 return custom_notification_query('info', 'new_elements', $start, $end); 274} 275 276/** 277 * Returns number of updated categories between two dates. 278 * 279 * @param string $start (mysql datetime format) 280 * @param string $end (mysql datetime format) 281 * @return int 282 */ 283function nb_updated_categories($start=null, $end=null) 284{ 285 return custom_notification_query('count', 'updated_categories', $start, $end); 286} 287 288/** 289 * Returns updated categories between two dates. 290 * 291 * @param string $start (mysql datetime format) 292 * @param string $end (mysql datetime format) 293 * @return int[] categories ids 294 */ 295function updated_categories($start=null, $end=null) 296{ 297 return custom_notification_query('info', 'updated_categories', $start, $end); 298} 299 300/** 301 * Returns number of new users between two dates. 302 * 303 * @param string $start (mysql datetime format) 304 * @param string $end (mysql datetime format) 305 * @return int 306 */ 307function nb_new_users($start=null, $end=null) 308{ 309 return custom_notification_query('count', 'new_users', $start, $end); 310} 311 312/** 313 * Returns new users between two dates. 314 * 315 * @param string $start (mysql datetime format) 316 * @param string $end (mysql datetime format) 317 * @return int[] user ids 318 */ 319function new_users($start=null, $end=null) 320{ 321 return custom_notification_query('info', 'new_users', $start, $end); 322} 323 324/** 325 * Returns if there was new activity between two dates. 326 * 327 * Takes in account: number of new comments, number of new elements, number of 328 * updated categories. Administrators are also informed about: number of 329 * unvalidated comments, number of new users. 330 * @todo number of unvalidated elements 331 * 332 * @param string $start (mysql datetime format) 333 * @param string $end (mysql datetime format) 334 * @return boolean 335 */ 336function news_exists($start=null, $end=null) 337{ 338 return ( 339 (nb_new_comments($start, $end) > 0) or 340 (nb_new_elements($start, $end) > 0) or 341 (nb_updated_categories($start, $end) > 0) or 342 ((is_admin()) and (nb_unvalidated_comments($start, $end) > 0)) or 343 ((is_admin()) and (nb_new_users($start, $end) > 0))); 344} 345 346/** 347 * Formats a news line and adds it to the array (e.g. '5 new elements') 348 * 349 * @param array &$news 350 * @param int $count 351 * @param string $singular_key 352 * @param string $plural_key 353 * @param string $url 354 * @param bool $add_url 355 */ 356function add_news_line(&$news, $count, $singular_key, $plural_key, $url='', $add_url=false) 357{ 358 if ($count > 0) 359 { 360 $line = l10n_dec($singular_key, $plural_key, $count); 361 if ($add_url and !empty($url) ) 362 { 363 $line = '<a href="'.$url.'">'.$line.'</a>'; 364 } 365 $news[] = $line; 366 } 367} 368 369/** 370 * Returns new activity between two dates. 371 * 372 * Takes in account: number of new comments, number of new elements, number of 373 * updated categories. Administrators are also informed about: number of 374 * unvalidated comments, number of new users. 375 * @todo number of unvalidated elements 376 * 377 * @param string $start (mysql datetime format) 378 * @param string $end (mysql datetime format) 379 * @param bool $exclude_img_cats if true, no info about new images/categories 380 * @param bool $add_url add html link around news 381 * @return array 382 */ 383function news($start=null, $end=null, $exclude_img_cats=false, $add_url=false, $auth_key=null) 384{ 385 $news = array(); 386 387 $add_url_params = array(); 388 if (isset($auth_key)) 389 { 390 $add_url_params['auth'] = $auth_key; 391 } 392 393 if (!$exclude_img_cats) 394 { 395 add_news_line( 396 $news, 397 nb_new_elements($start, $end), 398 '%d new photo', 399 '%d new photos', 400 add_url_params(make_index_url(array('section'=>'recent_pics')), $add_url_params), 401 $add_url 402 ); 403 404 add_news_line( 405 $news, 406 nb_updated_categories($start, $end), 407 '%d album updated', 408 '%d albums updated', 409 add_url_params(make_index_url(array('section'=>'recent_cats')), $add_url_params), 410 $add_url 411 ); 412 } 413 414 add_news_line( 415 $news, 416 nb_new_comments($start, $end), 417 '%d new comment', 418 '%d new comments', 419 add_url_params(get_root_url().'comments.php', $add_url_params), 420 $add_url 421 ); 422 423 if (is_admin()) 424 { 425 add_news_line( $news, 426 nb_unvalidated_comments($start, $end), '%d comment to validate', '%d comments to validate', 427 get_root_url().'admin.php?page=comments', $add_url ); 428 429 add_news_line( $news, 430 nb_new_users($start, $end), '%d new user', '%d new users', 431 get_root_url().'admin.php?page=user_list', $add_url ); 432 } 433 434 return $news; 435} 436 437/** 438 * Returns information about recently published elements grouped by post date. 439 * 440 * @param int $max_dates maximum number of recent dates 441 * @param int $max_elements maximum number of elements per date 442 * @param int $max_cats maximum number of categories per date 443 * @return array 444 */ 445function get_recent_post_dates($max_dates, $max_elements, $max_cats) 446{ 447 global $conf, $user, $persistent_cache; 448 449 $cache_key = $persistent_cache->make_key('recent_posts'.$user['id'].$user['cache_update_time'].$max_dates.$max_elements.$max_cats); 450 if ($persistent_cache->get($cache_key, $cached)) 451 { 452 return $cached; 453 } 454 $where_sql = get_std_sql_where_restrict_filter('WHERE', 'i.id', true); 455 456 $query = ' 457SELECT 458 date_available, 459 COUNT(DISTINCT id) AS nb_elements, 460 COUNT(DISTINCT category_id) AS nb_cats 461 FROM '.IMAGES_TABLE.' i INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id=image_id 462 '.$where_sql.' 463 GROUP BY date_available 464 ORDER BY date_available DESC 465 LIMIT '.$max_dates.' 466;'; 467 $dates = query2array($query); 468 469 for ($i=0; $i<count($dates); $i++) 470 { 471 if ($max_elements>0) 472 { // get some thumbnails ... 473 $query = ' 474SELECT DISTINCT i.* 475 FROM '.IMAGES_TABLE.' i 476 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id=image_id 477 '.$where_sql.' 478 AND date_available=\''.$dates[$i]['date_available'].'\' 479 ORDER BY '.DB_RANDOM_FUNCTION.'() 480 LIMIT '.$max_elements.' 481;'; 482 $dates[$i]['elements'] = query2array($query); 483 } 484 485 if ($max_cats>0) 486 {// get some categories ... 487 $query = ' 488SELECT 489 DISTINCT c.uppercats, 490 COUNT(DISTINCT i.id) AS img_count 491 FROM '.IMAGES_TABLE.' i 492 INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON i.id=image_id 493 INNER JOIN '.CATEGORIES_TABLE.' c ON c.id=category_id 494 '.$where_sql.' 495 AND date_available=\''.$dates[$i]['date_available'].'\' 496 GROUP BY category_id, c.uppercats 497 ORDER BY img_count DESC 498 LIMIT '.$max_cats.' 499;'; 500 $dates[$i]['categories'] = query2array($query); 501 } 502 } 503 504 $persistent_cache->set($cache_key, $dates); 505 return $dates; 506} 507 508/** 509 * Returns information about recently published elements grouped by post date. 510 * Same as get_recent_post_dates() but parameters as an indexed array. 511 * @see get_recent_post_dates() 512 * 513 * @param array $args 514 * @return array 515 */ 516function get_recent_post_dates_array($args) 517{ 518 return get_recent_post_dates( 519 (empty($args['max_dates']) ? 3 : $args['max_dates']), 520 (empty($args['max_elements']) ? 3 : $args['max_elements']), 521 (empty($args['max_cats']) ? 3 : $args['max_cats']) 522 ); 523} 524 525 526/** 527 * Returns html description about recently published elements grouped by post date. 528 * @todo clean up HTML output, currently messy and invalid ! 529 * 530 * @param array $date_detail returned value of get_recent_post_dates() 531 * @return string 532 */ 533function get_html_description_recent_post_date($date_detail, $auth_key=null) 534{ 535 global $conf; 536 537 $add_url_params = array(); 538 if (isset($auth_key)) 539 { 540 $add_url_params['auth'] = $auth_key; 541 } 542 543 $description = '<ul>'; 544 545 $description .= 546 '<li>' 547 .l10n_dec('%d new photo', '%d new photos', $date_detail['nb_elements']) 548 .' (' 549 .'<a href="'.add_url_params(make_index_url(array('section'=>'recent_pics')), $add_url_params).'">' 550 .l10n('Recent photos').'</a>' 551 .')' 552 .'</li><br>'; 553 554 foreach($date_detail['elements'] as $element) 555 { 556 $tn_src = DerivativeImage::thumb_url($element); 557 $description .= '<a href="'. 558 add_url_params( 559 make_picture_url( 560 array( 561 'image_id' => $element['id'], 562 'image_file' => $element['file'], 563 ) 564 ), 565 $add_url_params 566 ) 567 .'"><img src="'.$tn_src.'"></a>'; 568 } 569 $description .= '...<br>'; 570 571 $description .= 572 '<li>' 573 .l10n_dec('%d album updated', '%d albums updated', $date_detail['nb_cats']) 574 .'</li>'; 575 576 $description .= '<ul>'; 577 foreach($date_detail['categories'] as $cat) 578 { 579 $description .= 580 '<li>' 581 .get_cat_display_name_cache($cat['uppercats'],'', false, null, $auth_key) 582 .' ('. 583 l10n_dec('%d new photo', '%d new photos', $cat['img_count']).')' 584 .'</li>'; 585 } 586 $description .= '</ul>'; 587 588 $description .= '</ul>'; 589 590 return $description; 591} 592 593/** 594 * Returns title about recently published elements grouped by post date. 595 * 596 * @param array $date_detail returned value of get_recent_post_dates() 597 * @return string 598 */ 599function get_title_recent_post_date($date_detail) 600{ 601 global $lang; 602 603 $date = $date_detail['date_available']; 604 $exploded_date = strptime($date, '%Y-%m-%d %H:%M:%S'); 605 606 $title = l10n_dec('%d new photo', '%d new photos', $date_detail['nb_elements']); 607 $title .= ' ('.$lang['month'][1+$exploded_date['tm_mon']].' '.$exploded_date['tm_mday'].')'; 608 609 return $title; 610} 611 612if (!function_exists('strptime')) 613{ 614 function strptime($date, $fmt) 615 { 616 if ($fmt != '%Y-%m-%d %H:%M:%S') 617 die('Invalid strptime format '.$fmt); 618 list($y,$m,$d,$H,$M,$S) = preg_split('/[-: ]/', $date); 619 $res = localtime( mktime($H,$M,$S,$m,$d,$y), true ); 620 return $res; 621 } 622} 623 624?>