1<?php 2# MantisBT - A PHP based bugtracking system 3 4# MantisBT is free software: you can redistribute it and/or modify 5# it under the terms of the GNU General Public License as published by 6# the Free Software Foundation, either version 2 of the License, or 7# (at your option) any later version. 8# 9# MantisBT is distributed in the hope that it will be useful, 10# but WITHOUT ANY WARRANTY; without even the implied warranty of 11# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12# GNU General Public License for more details. 13# 14# You should have received a copy of the GNU General Public License 15# along with MantisBT. If not, see <http://www.gnu.org/licenses/>. 16 17/** 18 * BugFilterQuery class. 19 * @copyright Copyright 2017 MantisBT Team - mantisbt-dev@lists.sourceforge.net 20 * @link http://www.mantisbt.org 21 * @package MantisBT 22 * @subpackage classes 23 * 24 * @uses access_api.php 25 * @uses authentication_api.php 26 * @uses config_api.php 27 * @uses constant_inc.php 28 * @uses custom_field_api.php 29 * @uses database_api.php 30 * @uses filter_api.php 31 * @uses filter_constants_inc.php 32 * @uses gpc_api.php 33 * @uses helper_api.php 34 * @uses logging_api.php 35 * @uses project_api.php 36 * @uses tag_api.php 37 * @uses user_api.php 38 * @uses utility_api.php 39 */ 40 41require_api( 'access_api.php' ); 42require_api( 'authentication_api.php' ); 43require_api( 'config_api.php' ); 44require_api( 'constant_inc.php' ); 45require_api( 'custom_field_api.php' ); 46require_api( 'database_api.php' ); 47require_api( 'filter_api.php' ); 48require_api( 'filter_constants_inc.php' ); 49require_api( 'gpc_api.php' ); 50require_api( 'helper_api.php' ); 51require_api( 'logging_api.php' ); 52require_api( 'project_api.php' ); 53require_api( 'tag_api.php' ); 54require_api( 'user_api.php' ); 55require_api( 'utility_api.php' ); 56 57/** 58 * Bug Filter Query class 59 * 60 * Allows building a database query based on a filter. 61 * Contains logic for translating the filter properties into corresponding sql 62 * clauses to retrieve bugs matched by the filter. 63 * By inheriting from DbQury class, it can be used transparently as a query object 64 * that can be executed and fetched in the same way. 65 * 66 * With the constructor options, several types of queries can be built, oriented 67 * to retrieving a sorted list of bug rows, a single total count, a subquery for 68 * only bug ids... 69 * See constructor and set_query_type() for details of query type settings. 70 * After construction, and after a query type change, the base DbQuery object is 71 * ready to be executed. 72 * 73 * The simplest usage is: 74 * $fq = new BugFilterQuery( $filter ); 75 * $fq->execute(); 76 * 77 * Optionally, create with a different type: 78 * $fq = new BugFilterQuery( $filter, BugFilterData::QUERY_TYPE_COUNT ); 79 * $bugcount = $fq->value(); 80 * 81 * Or change its type dynamically: 82 * $fq->set_query_type( BugFilterData::QUERY_TYPE_IDS ); 83 * 84 * The query object can be used within other queries: 85 * $subquery = new BugFilterQuery( $some_filter, BugFilterData::QUERY_TYPE_IDS ); 86 * $main_query = new DbQuery( 'SELECT * FROM {bug} WHERE id IN :filter_ids' ); 87 * $main_query->bind( 'filter_ids', $subquery ); 88 * $main_query->execute(); 89 * 90 * Right after the BugFilterQuery object is created, all the internal query parts 91 * are constructed based on the filter, and the actual DbQuery sql is built based 92 * on the query type. 93 * If at a later time, externally new parts are added (where, join, order, etc), 94 * the actual sql will be rebuilt once the DbQuery 'execute' method is used, or 95 * build_query() is explicitly called. 96 */ 97 98class BugFilterQuery extends DbQuery { 99 100 const QUERY_TYPE_LIST = 0; 101 const QUERY_TYPE_COUNT = 1; 102 const QUERY_TYPE_IDS = 2; 103 const QUERY_TYPE_DISTINCT_IDS = 3; 104 105 # properties used to build the query 106 public $filter; 107 public $project_id; 108 public $user_id; 109 public $use_sticky; 110 111 # internal storage for intermediate data 112 protected $query_type; 113 protected $parts_select = array(); 114 protected $parts_from = array(); 115 protected $parts_join = array(); 116 protected $parts_where = array(); # these are joined by the filter operator 117 protected $parts_order = array(); 118 protected $fixed_where = array(); # these are joined always by AND 119 protected $filter_operator; 120 121 # runtime variables for building the filter query 122 protected $rt_stop_build; # flag to stop building the query parts, if there is no need to. 123 protected $rt_included_projects; # calculated list of projects in the filter scope, to be reused at each build step 124 protected $rt_table_alias_cf; # keep track of the custom field table joins, to reuse them in order by, or serach matching. 125 protected $rt_table_alias_bugnote = null; # keep track of the bugnote table joins. 126 protected $needs_rebuild; # flag to force a rebuild of the final sql when additions are made after the object is first created. 127 128 /** 129 * Constructor. 130 * Will build a query based on the provided filter. 131 * 132 * $p_config can be either: 133 * - A single type constant, for easy object creation using default attributes 134 * - An array of options, for more advanced configuration. 135 * 136 * Option array uses "option => value" pairs, supported as: 137 * - 'query_type': Any of QUERY_TYPE_xxx class constants, meaning: 138 * QUERY_TYPE_LIST, query listing all fields of matched bugs. This is the default. 139 * QUERY_TYPE_COUNT, query to return number of matched bugs. 140 * QUERY_TYPE_IDS, query to return only matched bug ids, which may not 141 * be unique, but may be faster and convenient for use as a subquery. 142 * QUERY_TYPE_DISTINCT_IDS, query to return unique matched bug ids . 143 * - 'project_id': (integer) A project id to be used, if needed by the filer. By default, 144 * current project is used. 145 * - 'user_id': (integer) A user id to be used to determine visibility for the filter. 146 * By default current user is used. 147 * - 'use_sticky': (boolean) Whether to allow returning the bug list sorted so that sticky 148 * bugs are placed first in the result order. This is false by default. 149 * 150 * @param array $p_filter Filter array 151 * @param array|integer $p_config Options array, or single query type identifier 152 * @return void 153 */ 154 public function __construct( $p_filter, $p_config = self::QUERY_TYPE_LIST ) { 155 # defaults 156 $t_query_type = self::QUERY_TYPE_LIST; 157 $this->filter = $p_filter; 158 $this->use_sticky = false; 159 $this->project_id = helper_get_current_project(); 160 $this->user_id = auth_get_current_user_id(); 161 162 # $p_config can be an array or an integer 163 if( is_array( $p_config ) ) { 164 foreach( $p_config as $t_option => $t_value ) { 165 if( null === $t_value ) { 166 continue; 167 } 168 switch( $t_option ) { 169 case 'query_type': 170 $t_query_type = (int)$t_value; 171 break; 172 case 'project_id': 173 $this->project_id = (int)$t_value; 174 break; 175 case 'user_id': 176 $this->user_id = (int)$t_value; 177 break; 178 case 'use_sticky': 179 $this->use_sticky = (boolean)$t_value; 180 break; 181 } 182 } 183 } else { 184 $t_query_type = $p_config; 185 } 186 $this->query_type = $t_query_type; 187 $this->needs_rebuild = true; 188 189 # The query string must be built here to have a valid DbQuery object ready for use 190 $this->build_main(); 191 $this->set_query_type( $t_query_type ); 192 } 193 194 /** 195 * Changes the effective database query to be of one of the selected types 196 * See constructor documentation for details about each query type. 197 * 198 * After calling this method, the actual query string is modified and ready to 199 * be used as a DbQuery objet for execution, or subquery composition. 200 * 201 * @param integer $p_query_type Query type identifier 202 * @return void 203 */ 204 public function set_query_type( $p_query_type ) { 205 $this->query_type = (int)$p_query_type; 206 $this->build_query(); 207 } 208 209 /** 210 * Builds the actual DbQuery object based on the current query type and query parts 211 * 212 * @return void 213 */ 214 public function build_query() { 215 switch( $this->query_type ) { 216 case self::QUERY_TYPE_COUNT: 217 $this->sql( $this->string_query_count() ); 218 break; 219 case self::QUERY_TYPE_IDS: 220 $this->sql( $this->string_query_ids() ); 221 break; 222 case self::QUERY_TYPE_DISTINCT_IDS: 223 $this->sql( $this->string_query_dinstinct_ids() ); 224 break; 225 case self::QUERY_TYPE_LIST: 226 default: 227 $this->sql( $this->string_query_list() ); 228 break; 229 } 230 $this->needs_rebuild = false; 231 $this->db_result = null; 232 } 233 234 /** 235 * Override DbQuery execute method to check first if the query is already buils and up to date 236 * with current query parts. 237 * 238 * @param array $p_bind_array Array for binding values 239 * @param integer $p_limit Limit value 240 * @param integer $p_offset Offset value 241 * @return IteratorAggregate|boolean ADOdb result set or false if the query failed. 242 */ 243 public function execute( array $p_bind_array = null, $p_limit = null, $p_offset = null ) { 244 if( $this->needs_rebuild ) { 245 $this->build_query(); 246 } 247 return parent::execute( $p_bind_array, $p_limit, $p_offset ); 248 } 249 250 /** 251 * Shorthand method to get the total number of issues matched by the filter 252 * It creates a copy of current object, set its type to a count query, 253 * executes it and returns the count value. 254 * This call does not modify current object. 255 * 256 * @return integer Number of issues matched by the filter 257 */ 258 public function get_bug_count() { 259 # create a copy from current query 260 $t_query_count = clone $this; 261 # rebuild clauses for count query type 262 $t_query_count->set_query_type( self::QUERY_TYPE_COUNT ); 263 # set defaults 264 $t_query_count->set_limit(); 265 $t_query_count->set_offset(); 266 $t_query_count->execute(); 267 return $t_query_count->value(); 268 } 269 270 /** 271 * Adds a query part to the "select" elements 272 * @param string $p_string 273 * @return void 274 */ 275 public function add_select( $p_string ) { 276 $this->parts_select[] = $p_string; 277 $this->needs_rebuild = true; 278 } 279 280 /** 281 * Adds a query part to the "from" elements 282 * @param string $p_string 283 * @return void 284 */ 285 public function add_from( $p_string ) { 286 $this->parts_from[] = $p_string; 287 $this->needs_rebuild = true; 288 } 289 290 /** 291 * Adds a query part to the "join" elements 292 * @param string $p_string 293 * @return void 294 */ 295 public function add_join( $p_string ) { 296 $this->parts_join[] = $p_string; 297 $this->needs_rebuild = true; 298 } 299 300 /** 301 * Adds a query part to the "where" elements. 302 * These elements will be combined with the operator (and/or) defined by the filter 303 * @param string $p_string 304 * @return void 305 */ 306 public function add_where( $p_string ) { 307 $this->parts_where[] = $p_string; 308 $this->needs_rebuild = true; 309 } 310 311 /** 312 * Adds a query part to the "fixed where" elements. 313 * The elements will always be ANDed in the query. 314 * @param string $p_string 315 * @return void 316 */ 317 public function add_fixed_where( $p_string ) { 318 $this->fixed_where[] = $p_string; 319 $this->needs_rebuild = true; 320 } 321 322 /** 323 * Adds a query part to the "order by" elements 324 * @param string $p_string 325 * @return void 326 */ 327 public function add_order( $p_string ) { 328 $this->parts_order[] = $p_string; 329 $this->needs_rebuild = true; 330 } 331 332 /** 333 * Builds the query string block which is common to other query constructions, 334 * based on the from, join, and where parts 335 * @return string The constructed query string block 336 */ 337 protected function helper_string_query_inner() { 338 $t_from_string = ' FROM ' . implode( ', ', $this->parts_from ); 339 $t_join_string = count( $this->parts_join ) > 0 ? ' ' . implode( ' ', $this->parts_join ) : ''; 340 $t_where_string = ' WHERE '. implode( ' AND ', $this->fixed_where ); 341 if( count( $this->parts_where ) > 0 ) { 342 $t_where_string .= ' AND ( '; 343 $t_where_string .= implode( $this->filter_operator, $this->parts_where ); 344 $t_where_string .= ' )'; 345 } 346 return $t_from_string . $t_join_string . $t_where_string; 347 } 348 349 /** 350 * Builds a query string destinated to listing the issues with all the selected fields 351 * @return string The constructed query string 352 */ 353 protected function string_query_list() { 354 if( empty( $this->parts_order ) ) { 355 $this->build_order_by(); 356 $this->unique_query_parts(); 357 } 358 $t_select_string = 'SELECT DISTINCT ' . implode( ', ', $this->parts_select ); 359 $t_order_string = ' ORDER BY ' . implode( ', ', $this->parts_order ); 360 return $t_select_string . $this->helper_string_query_inner() . $t_order_string; 361 } 362 363 /** 364 * Builds a query string destinated to listing the matched issues count 365 * @return string The constructed query string 366 */ 367 protected function string_query_count() { 368 $t_select_string = 'SELECT COUNT( DISTINCT {bug}.id )'; 369 return $t_select_string . $this->helper_string_query_inner(); 370 } 371 372 /** 373 * Builds a query string destinated to listing the matched issue ids. 374 * The values returned by this query are not unique. 375 * @return string The constructed query string 376 */ 377 protected function string_query_ids() { 378 $t_select_string = 'SELECT {bug}.id'; 379 return $t_select_string . $this->helper_string_query_inner(); 380 } 381 382 /** 383 * Builds a query string destinated to listing the matched issue ids 384 * The values returned by this query are unique ids 385 * @return string The constructed query string 386 */ 387 protected function string_query_dinstinct_ids() { 388 $t_select_string = 'SELECT DISTINCT {bug}.id'; 389 return $t_select_string . $this->helper_string_query_inner(); 390 } 391 392 /** 393 * Build all the query parts needed based on the filter 394 * @return void 395 */ 396 protected function build_main() { 397 $this->rt_stop_build = false; 398 $this->add_from( '{bug}' ); 399 $this->add_select( '{bug}.*' ); 400 401 if( $this->filter[FILTER_PROPERTY_MATCH_TYPE] == FILTER_MATCH_ANY ) { 402 $this->filter_operator = ' OR '; 403 } else { 404 $this->filter_operator = ' AND '; 405 } 406 407 $this->build_projects(); 408 # if no projects where found, stop here 409 if( $this->rt_stop_build ) { 410 return; 411 } 412 413 foreach( $this->filter as $t_prop => $t_value ) { 414 # These are the main entries for filter properties 415 switch( $t_prop ) { 416 case FILTER_PROPERTY_REPORTER_ID: 417 $this->build_prop_reporter(); 418 break; 419 case FILTER_PROPERTY_HANDLER_ID: 420 $this->build_prop_handler(); 421 break; 422 case FILTER_PROPERTY_MONITOR_USER_ID: 423 $this->build_prop_monitor_by(); 424 break; 425 case FILTER_PROPERTY_NOTE_USER_ID: 426 $this->build_prop_note_by(); 427 break; 428 case FILTER_PROPERTY_FILTER_BY_DATE_SUBMITTED: 429 $this->build_prop_date_created(); 430 break; 431 case FILTER_PROPERTY_FILTER_BY_LAST_UPDATED_DATE: 432 $this->build_prop_date_updated(); 433 break; 434 case FILTER_PROPERTY_BUILD: 435 $this->build_prop_build(); 436 break; 437 case FILTER_PROPERTY_VERSION: 438 $this->build_prop_version(); 439 break; 440 case FILTER_PROPERTY_FIXED_IN_VERSION: 441 $this->build_prop_fixed_version(); 442 break; 443 case FILTER_PROPERTY_TARGET_VERSION: 444 $this->build_prop_target_version(); 445 break; 446 case FILTER_PROPERTY_VIEW_STATE: 447 $this->build_prop_view_state(); 448 break; 449 case FILTER_PROPERTY_CATEGORY_ID: 450 $this->build_prop_category(); 451 break; 452 case FILTER_PROPERTY_SEVERITY: 453 $this->build_prop_severity(); 454 break; 455 case FILTER_PROPERTY_RESOLUTION: 456 $this->build_prop_resolution(); 457 break; 458 case FILTER_PROPERTY_PRIORITY: 459 $this->build_prop_priority(); 460 break; 461 case FILTER_PROPERTY_PROFILE_ID: 462 $this->build_prop_profile(); 463 break; 464 case FILTER_PROPERTY_PLATFORM: 465 $this->build_prop_platform(); 466 break; 467 case FILTER_PROPERTY_OS: 468 $this->build_prop_os(); 469 break; 470 case FILTER_PROPERTY_OS_BUILD: 471 $this->build_prop_os_build(); 472 break; 473 case FILTER_PROPERTY_SEARCH: 474 $this->build_prop_search(); 475 break; 476 case FILTER_PROPERTY_RELATIONSHIP_TYPE: 477 $this->build_prop_relationship(); 478 break; 479 } 480 } 481 # these have several properties that must be built only once 482 if( isset( $this->filter[FILTER_PROPERTY_TAG_STRING] ) 483 || isset( $this->filter[FILTER_PROPERTY_TAG_SELECT] ) ) { 484 $this->build_prop_tags(); 485 } 486 if( isset( $this->filter['custom_fields'] ) ) { 487 $this->build_prop_custom_fields(); 488 } 489 490 if( isset( $this->filter[FILTER_PROPERTY_HIDE_STATUS] ) 491 || isset( $this->filter[FILTER_PROPERTY_STATUS] )) { 492 $this->build_prop_status(); 493 } 494 495 $this->build_prop_plugin_filters(); 496 497 $this->unique_query_parts(); 498 } 499 500 protected function unique_query_parts() { 501 $this->parts_select = array_unique( $this->parts_select ); 502 $this->parts_from = array_unique( $this->parts_from ); 503 $this->parts_join = array_unique( $this->parts_join ); 504 $this->parts_order = array_unique( $this->parts_order ); 505 } 506 507 /** 508 * Build the query parts for the filter projects 509 * @return void 510 */ 511 protected function build_projects() { 512 $this->add_join( 'JOIN {project} ON {project}.id = {bug}.project_id' ); 513 $this->add_fixed_where( '{project}.enabled = ' . $this->param( true ) ); 514 515 $t_user_id = $this->user_id; 516 $t_project_id = $this->project_id; 517 518 $t_projects_query_required = true; 519 $t_included_project_ids = filter_get_included_projects( $this->filter, $t_project_id, $t_user_id, true /* return ALL_PROJECTS */ ); 520 if( ALL_PROJECTS == $t_included_project_ids ) { 521 # The list of expanded projects is needed later even if project_query is not required 522 $t_included_project_ids = filter_get_included_projects( $this->filter, $t_project_id, $t_user_id, false /* return ALL_PROJECTS */ ); 523 # this special case can skip the projects query clause: 524 if( user_is_administrator( $t_user_id ) ) { 525 log_event( LOG_FILTERING, 'all projects + administrator, hence no project filter.' ); 526 $t_projects_query_required = false; 527 } 528 } 529 $this->rt_included_projects = $t_included_project_ids; 530 531 if( $t_projects_query_required ) { 532 533 # if no projects are accessible, then stop here 534 if( count( $t_included_project_ids ) == 0 ) { 535 log_event( LOG_FILTERING, 'no accessible projects' ); 536 $this->add_fixed_where( '{project}.id = ' . $this->param( 0 ) ); 537 $this->rt_stop_build = true; 538 return; 539 } 540 541 # Arrays for project visibility conditions. Each array will translate 542 # to a set of conditions for visibility. 543 # Based on the user access level, each project will be placed in one 544 # or several of these arrays for later treatment. 545 546 # this array is populated with projects that the current user 547 # hasfull access to (public and private issues) 548 $t_private_and_public_project_ids = array(); 549 # this array is populated with projects to search only public issues. 550 $t_public_only_project_ids = array(); 551 # this array is populated with projects to search only accesible private 552 # issues by being the reporter of those. 553 $t_private_is_reporter_project_ids = array(); 554 555 # these arrays are populated with projects where the user has limited view, 556 # with 'limit_view_unless_threshold' configuration 557 558 # projects where the user has limited view, but can see any private issue 559 $t_limited_public_and_private_project_ids = array(); 560 # projects where the user has limited view, and can't see private issues, 561 # only public ones 562 $t_limited_public_only_project_ids = array(); 563 564 # these arrays are populated with projects where the user has limited view, 565 # with the old 'limit_reporters' configuration 566 567 # projects where the user has limited view, but can see any private issue 568 $t_old_limit_public_and_private_project_ids = array(); 569 # projects where the user has limited view, and can't see private issues, 570 # only public ones 571 $t_old_limit_public_only_project_ids = array(); 572 573 # make sure the project rows are cached, as they will be used to check access levels. 574 project_cache_array_rows( $t_included_project_ids ); 575 576 # Old 'limit_reporters' option was previously only supported for ALL_PROJECTS, 577 $t_old_limit_reporters = ( ON == config_get( 'limit_reporters', null, $t_user_id, ALL_PROJECTS ) ); 578 579 foreach( $t_included_project_ids as $t_pid ) { 580 $t_access_required_to_view_private_bugs = config_get( 'private_bug_threshold', null, null, $t_pid ); 581 $t_can_see_private = access_has_project_level( $t_access_required_to_view_private_bugs, $t_pid, $t_user_id ); 582 583 if( access_has_limited_view( $t_pid, $t_user_id ) ) { 584 if( $t_old_limit_reporters ) { 585 # we have a reduced access (show only own reported issues) 586 $t_old_limit_public_and_private_project_ids[] = $t_pid; 587 if( !$t_can_see_private ) { 588 $t_old_limit_public_only_project_ids[] = $t_pid; 589 } 590 } else{ 591 # we have a reduced access (show only own reported, handled, monitored issues) 592 if( $t_can_see_private ) { 593 $t_limited_public_and_private_project_ids[] = $t_pid; 594 } else { 595 $t_limited_public_only_project_ids[] = $t_pid; 596 # private issues can be seen by the reporter, which is also a valid 597 # case for the limited view configuration 598 $t_private_is_reporter_project_ids[] = $t_pid; 599 } 600 } 601 } else { 602 # if there is no special limit, use the general project clauses 603 if( $t_can_see_private ) { 604 $t_private_and_public_project_ids[] = $t_pid; 605 } else { 606 $t_public_only_project_ids[] = $t_pid; 607 $t_private_is_reporter_project_ids[] = $t_pid; 608 } 609 } 610 } 611 612 $t_query_projects_or = array(); 613 # for these projects, search all issues 614 if( !empty( $t_private_and_public_project_ids ) ) { 615 $t_query_projects_or[] = $this->sql_in( '{bug}.project_id', $t_private_and_public_project_ids ); 616 } 617 618 # for these projects, search public issues 619 if( !empty( $t_public_only_project_ids ) ) { 620 $t_query_projects_or[] = $this->sql_in( '{bug}.project_id', $t_public_only_project_ids ) 621 . ' AND {bug}.view_state = ' . $this->param( VS_PUBLIC ); 622 } 623 624 # for these projects, search private issues where the user is reporter 625 if( !empty( $t_private_is_reporter_project_ids ) ) { 626 $t_query_projects_or[] = $this->sql_in( '{bug}.project_id', $t_private_is_reporter_project_ids ) 627 . ' AND {bug}.view_state <> ' . $this->param( VS_PUBLIC ) 628 . ' AND {bug}.reporter_id = ' . $this->param( $t_user_id ); 629 } 630 631 # for these projects, search any issue (public or private) valid for the old 'limit_reporters' configuration 632 if( !empty( $t_old_limit_public_and_private_project_ids ) ) { 633 $t_query_projects_or[] = $this->sql_in( '{bug}.project_id', $t_old_limit_public_and_private_project_ids ) 634 . ' AND {bug}.reporter_id = ' . $this->param( $t_user_id ); 635 } 636 637 # for these projects, search public issues valid for the old 'limit_reporters' configuration 638 if( !empty( $t_old_limit_public_only_project_ids ) ) { 639 $t_query_projects_or[] = $this->sql_in( '{bug}.project_id', $t_old_limit_public_only_project_ids ) 640 . ' AND {bug}.view_state = ' . $this->param( VS_PUBLIC ) 641 . ' AND {bug}.reporter_id = ' . $this->param( $t_user_id ); 642 } 643 644 # for these projects, search any issue (public or private) valid for limited view 645 if( !empty( $t_limited_public_and_private_project_ids ) ) { 646 $t_query_projects_or[] = $this->sql_in( '{bug}.project_id', $t_limited_public_and_private_project_ids ) 647 . ' AND (' 648 . ' {bug}.reporter_id = ' . $this->param( $t_user_id ) 649 . ' OR {bug}.handler_id = ' . $this->param( $t_user_id ) 650 . ' OR EXISTS ( SELECT 1 FROM {bug_monitor} bm' 651 . ' WHERE bm.user_id = ' . $this->param( $t_user_id ) 652 . ' AND bm.bug_id = {bug}.id )' 653 . ' )'; 654 } 655 656 # for these projects, search public issues valid for limited view 657 if( !empty( $t_limited_public_only_project_ids ) ) { 658 $t_query_projects_or[] = $this->sql_in( '{bug}.project_id', $t_limited_public_only_project_ids ) 659 . ' AND {bug}.view_state = ' . $this->param( VS_PUBLIC ) 660 . ' AND (' 661 . ' {bug}.reporter_id = ' . $this->param( $t_user_id ) 662 . ' OR {bug}.handler_id = ' . $this->param( $t_user_id ) 663 . ' OR EXISTS ( SELECT 1 FROM {bug_monitor} bm' 664 . ' WHERE bm.user_id = ' . $this->param( $t_user_id ) 665 . ' AND bm.bug_id = {bug}.id )' 666 . ' )'; 667 } 668 669 $t_project_query = '(' . implode( ' OR ', $t_query_projects_or ) . ')'; 670 671 $this->add_fixed_where( $t_project_query ); 672 } 673 } 674 675 /** 676 * Build the query parts for the filter properties related to "status" 677 * @return void 678 */ 679 protected function build_prop_status() { 680 # take a list of all available statuses then remove the ones that we want hidden, then make sure 681 # the ones we want shown are still available 682 $t_desired_statuses = $this->filter[FILTER_PROPERTY_STATUS]; 683 684 # simple filtering: restrict by the hide status value if present 685 if( FILTER_VIEW_TYPE_SIMPLE == $this->filter['_view_type'] ) { 686 if( isset( $this->filter[FILTER_PROPERTY_HIDE_STATUS][0] ) && !filter_field_is_none( $this->filter[FILTER_PROPERTY_HIDE_STATUS][0] ) ) { 687 $t_selected_status_array = $this->filter[FILTER_PROPERTY_STATUS]; 688 # if we have metavalue for "any", expand to all status, to filter them 689 if( filter_field_is_any( $t_selected_status_array ) ) { 690 $t_selected_status_array = MantisEnum::getValues( config_get( 'status_enum_string' ) ); 691 } 692 $t_hide_status = $this->filter[FILTER_PROPERTY_HIDE_STATUS][0]; 693 # Filter out status that must be hidden 694 $t_desired_statuses = array(); 695 foreach( $t_selected_status_array as $t_this_status ) { 696 if( $t_hide_status > $t_this_status ) { 697 $t_desired_statuses[] = $t_this_status; 698 } 699 } 700 } 701 } 702 # advanced filtering: ignore hide_status, do nothing. 703 704 # if show_status is "any", empty the array, to not include any condition on status. 705 if( filter_field_is_any( $t_desired_statuses ) ) { 706 $t_desired_statuses = array(); 707 } 708 709 if( count( $t_desired_statuses ) > 0 ) { 710 $t_clauses = $this->helper_array_map_int( $t_desired_statuses ); 711 $this->add_where( $this->sql_in( '{bug}.status', $t_clauses ) ); 712 } 713 } 714 715 /** 716 * Build the query parts for the filter property "creation date" 717 * @return void 718 */ 719 protected function build_prop_date_created() { 720 if( ( gpc_string_to_bool( $this->filter[FILTER_PROPERTY_FILTER_BY_DATE_SUBMITTED] ) ) 721 && is_numeric( $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_START_MONTH] ) 722 && is_numeric( $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_START_DAY] ) 723 && is_numeric( $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_START_YEAR] ) 724 && is_numeric( $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_END_MONTH] ) 725 && is_numeric( $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_END_DAY] ) 726 && is_numeric( $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_END_YEAR] ) 727 ) { 728 $t_start_string = $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_START_YEAR] 729 . '-' . $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_START_MONTH] 730 . '-' . $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_START_DAY] 731 . ' 00:00:00'; 732 $t_end_string = $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_END_YEAR] 733 . '-' . $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_END_MONTH] 734 . '-' . $this->filter[FILTER_PROPERTY_DATE_SUBMITTED_END_DAY] 735 . ' 23:59:59'; 736 737 $t_query_created_at = '{bug}.date_submitted BETWEEN ' 738 . $this->param( strtotime( $t_start_string ) ) . ' AND ' 739 . $this->param( strtotime( $t_end_string ) ) ; 740 $this->add_fixed_where( $t_query_created_at ); 741 } 742 } 743 744 /** 745 * Build the query parts for the filter property "last updated date" 746 * @return void 747 */ 748 protected function build_prop_date_updated() { 749 if( ( gpc_string_to_bool( $this->filter[FILTER_PROPERTY_FILTER_BY_LAST_UPDATED_DATE] ) ) 750 && is_numeric( $this->filter[FILTER_PROPERTY_LAST_UPDATED_START_MONTH] ) 751 && is_numeric( $this->filter[FILTER_PROPERTY_LAST_UPDATED_START_DAY] ) 752 && is_numeric( $this->filter[FILTER_PROPERTY_LAST_UPDATED_START_YEAR] ) 753 && is_numeric( $this->filter[FILTER_PROPERTY_LAST_UPDATED_END_MONTH] ) 754 && is_numeric( $this->filter[FILTER_PROPERTY_LAST_UPDATED_END_DAY] ) 755 && is_numeric( $this->filter[FILTER_PROPERTY_LAST_UPDATED_END_YEAR] ) 756 ) { 757 $t_start_string = $this->filter[FILTER_PROPERTY_LAST_UPDATED_START_YEAR] 758 . '-' . $this->filter[FILTER_PROPERTY_LAST_UPDATED_START_MONTH] 759 . '-' . $this->filter[FILTER_PROPERTY_LAST_UPDATED_START_DAY] 760 . ' 00:00:00'; 761 $t_end_string = $this->filter[FILTER_PROPERTY_LAST_UPDATED_END_YEAR] 762 . '-' . $this->filter[FILTER_PROPERTY_LAST_UPDATED_END_MONTH] 763 . '-' . $this->filter[FILTER_PROPERTY_LAST_UPDATED_END_DAY] 764 . ' 23:59:59'; 765 766 $t_query_updated_at = '{bug}.last_updated BETWEEN ' 767 . $this->param( strtotime( $t_start_string ) ) . ' AND ' 768 . $this->param( strtotime( $t_end_string ) ) ; 769 $this->add_fixed_where( $t_query_updated_at ); 770 } 771 } 772 773 /** 774 * Build the query parts for the filter property "view state" 775 * @return void 776 */ 777 protected function build_prop_view_state() { 778 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_VIEW_STATE] ) ) { 779 return; 780 } 781 $t_view_state = (int)$this->filter[FILTER_PROPERTY_VIEW_STATE]; 782 $t_view_state_query = '{bug}.view_state = ' . $this->param( $t_view_state ) ; 783 log_event( LOG_FILTERING, 'view_state query = ' . $t_view_state_query ); 784 $this->add_where( $t_view_state_query ); 785 } 786 787 /** 788 * Utility function to process the values for a filter property that is related 789 * to a database id number, used to represent users. 790 * Manages special case meta-value-none, which is translated to id "0", to be able 791 * to match database values where "0" is the default for empty/none 792 * Manages special case meta-value-myself, by replacing with the actual current user id. 793 * 794 * @param array $p_users_array Input array with user ids 795 * @return array Converted array 796 */ 797 protected function helper_process_users_property( array $p_users_array ) { 798 $t_new_array = array(); 799 foreach( $p_users_array as $t_user ) { 800 if( filter_field_is_none( $t_user ) ) { 801 $t_new_array[] = 0; 802 } else { 803 $c_user_id = (int)$t_user; 804 if( filter_field_is_myself( $c_user_id ) ) { 805 $t_new_array[] = $this->user_id; 806 } else { 807 $t_new_array[] = $c_user_id; 808 } 809 } 810 } 811 return $t_new_array; 812 } 813 814 /** 815 * Build the query parts for the filter property "reporter" 816 * @return void 817 */ 818 protected function build_prop_reporter() { 819 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_REPORTER_ID] ) ) { 820 return; 821 } 822 $t_user_ids = $this->helper_process_users_property( $this->filter[FILTER_PROPERTY_REPORTER_ID] ); 823 $t_users_query = $this->sql_in( '{bug}.reporter_id', $t_user_ids ); 824 log_event( LOG_FILTERING, 'reporter query = ' . $t_users_query ); 825 $this->add_where( $t_users_query ); 826 } 827 828 /** 829 * Build the query parts for the filter property "handler" 830 * @return void 831 */ 832 protected function build_prop_handler() { 833 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_HANDLER_ID] ) ) { 834 return; 835 } 836 837 # the user can view handler if he meets access level for 838 # 'view_handler_threshold' or if he is the handler 839 $t_projects_can_view = $this->helper_filter_projects_using_access( 'view_handler_threshold' ); 840 if( ALL_PROJECTS == $t_projects_can_view ) { 841 $t_view_condition = null; 842 } else { 843 $t_view_condition = '{bug}.handler_id = ' . $this->param( $this->user_id ); 844 if( !empty( $t_projects_can_view ) ) { 845 $t_view_condition = '(' . $t_view_condition . ' OR ' 846 . $this->sql_in( '{bug}.project_id', $t_projects_can_view ) . ')'; 847 } 848 } 849 if( $t_view_condition ) { 850 $t_view_condition = ' AND ' . $t_view_condition; 851 } 852 853 $t_user_ids = $this->helper_process_users_property( $this->filter[FILTER_PROPERTY_HANDLER_ID] ); 854 $t_query = $this->sql_in( '{bug}.handler_id', $t_user_ids ) . $t_view_condition; 855 log_event( LOG_FILTERING, 'handler query = ' . $t_query ); 856 $this->add_where( $t_query ); 857 } 858 859 /** 860 * Build the query parts for the filter property "category" 861 * @return void 862 */ 863 protected function build_prop_category() { 864 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_CATEGORY_ID] ) ) { 865 return; 866 } 867 $t_names = array(); 868 $t_use_none = false; 869 foreach( $this->filter[FILTER_PROPERTY_CATEGORY_ID] as $t_filter_member ) { 870 if( filter_field_is_none( $t_filter_member ) ) { 871 $t_use_none = true; 872 } else { 873 $t_names[] = $t_filter_member; 874 } 875 } 876 877 $t_join = 'LEFT JOIN {category} ON {bug}.category_id = {category}.id'; 878 $t_query_or = array(); 879 if( !empty( $t_names ) ) { 880 $t_query_or[] = $this->sql_in( '{category}.name', $t_names ); 881 } 882 if( $t_use_none ) { 883 $t_query_or[] = '{category}.name IS NULL'; 884 } 885 $t_where = '(' . implode( ' OR ', $t_query_or ) . ')'; 886 log_event( LOG_FILTERING, 'category query = ' . $t_where ); 887 $this->add_join( $t_join ); 888 $this->add_where( $t_where ); 889 } 890 891 /** 892 * Build the query parts for the filter property "severity" 893 * @return void 894 */ 895 protected function build_prop_severity() { 896 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_SEVERITY] ) ) { 897 return; 898 } 899 $t_query = $this->sql_in( '{bug}.severity', $this->filter[FILTER_PROPERTY_SEVERITY] ); 900 $this->add_where( $t_query ); 901 } 902 903 /** 904 * Build the query parts for the filter property "resolution" 905 * @return void 906 */ 907 protected function build_prop_resolution() { 908 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_RESOLUTION] ) ) { 909 return; 910 } 911 $t_query = $this->sql_in( '{bug}.resolution', $this->filter[FILTER_PROPERTY_RESOLUTION] ); 912 $this->add_where( $t_query ); 913 } 914 915 /** 916 * Build the query parts for the filter property "priority" 917 * @return void 918 */ 919 protected function build_prop_priority() { 920 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_PRIORITY] ) ) { 921 return; 922 } 923 $t_query = $this->sql_in( '{bug}.priority', $this->filter[FILTER_PROPERTY_PRIORITY] ); 924 $this->add_where( $t_query ); 925 } 926 927 /** 928 * Utility function to process the values for a filter property that is related 929 * to a database string field. 930 * Manages special case meta-value-none, which is translated to "", to be able 931 * to match database values where "" is the default for empty/null 932 * 933 * @param array $p_array Input array with strings 934 * @return array Converted array 935 */ 936 protected function helper_process_string_property( $p_array ) { 937 $t_new_array = array(); 938 foreach( $p_array as $t_elem ) { 939 if( filter_field_is_none( $t_elem ) ) { 940 $t_new_array[] = ''; 941 } else { 942 $t_new_array[] = $t_elem; 943 } 944 } 945 return $t_new_array; 946 } 947 948 /** 949 * Build the query parts for the filter property "build" 950 * @return void 951 */ 952 protected function build_prop_build() { 953 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_BUILD] ) ) { 954 return; 955 } 956 $t_array = $this->helper_process_string_property( $this->filter[FILTER_PROPERTY_BUILD] ); 957 $t_query = $this->sql_in( '{bug}.build', $t_array ); 958 $this->add_where( $t_query ); 959 } 960 961 /** 962 * Build the query parts for the filter property "version" 963 * @return void 964 */ 965 protected function build_prop_version() { 966 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_VERSION] ) ) { 967 return; 968 } 969 $t_array = $this->helper_process_string_property( $this->filter[FILTER_PROPERTY_VERSION] ); 970 $t_query = $this->sql_in( '{bug}.version', $t_array ); 971 $this->add_where( $t_query ); 972 } 973 974 /** 975 * Utility function to process the values for a filter property that is related 976 * to a database id number. 977 * Manages special cases like meta-value-none, which is translated to id "0", to be able 978 * to match database values where "0" is the default for empty/none 979 * @param array $p_array Input array with ids 980 * @return array Converted array 981 */ 982 protected function helper_process_id_property( $p_array ) { 983 $t_new_array = array(); 984 foreach( $p_array as $t_elem ) { 985 if( filter_field_is_none( $t_elem ) ) { 986 $t_new_array[] = 0; 987 } else { 988 $t_new_array[] = $t_elem; 989 } 990 } 991 return $t_new_array; 992 } 993 994 /** 995 * Build the query parts for the filter property "profile" 996 * @return void 997 */ 998 protected function build_prop_profile() { 999 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_PROFILE_ID] ) ) { 1000 return; 1001 } 1002 $t_array = $this->helper_process_id_property( $this->filter[FILTER_PROPERTY_PROFILE_ID] ); 1003 $t_query = $this->sql_in( '{bug}.profile_id', $t_array ); 1004 $this->add_where( $t_query ); 1005 } 1006 1007 /** 1008 * Build the query parts for the filter property "platform" 1009 * @return void 1010 */ 1011 protected function build_prop_platform() { 1012 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_PLATFORM] ) ) { 1013 return; 1014 } 1015 $t_array = $this->helper_process_string_property( $this->filter[FILTER_PROPERTY_PLATFORM] ); 1016 $t_query = $this->sql_in( '{bug}.platform', $t_array ); 1017 $this->add_where( $t_query ); 1018 } 1019 1020 /** 1021 * Build the query parts for the filter property "OS" 1022 * @return void 1023 */ 1024 protected function build_prop_os() { 1025 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_OS] ) ) { 1026 return; 1027 } 1028 $t_array = $this->helper_process_string_property( $this->filter[FILTER_PROPERTY_OS] ); 1029 $t_query = $this->sql_in( '{bug}.os', $t_array ); 1030 $this->add_where( $t_query ); 1031 } 1032 1033 /** 1034 * Build the query parts for the filter property "OS build" 1035 * @return void 1036 */ 1037 protected function build_prop_os_build() { 1038 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_OS_BUILD] ) ) { 1039 return; 1040 } 1041 $t_array = $this->helper_process_string_property( $this->filter[FILTER_PROPERTY_OS_BUILD] ); 1042 $t_query = $this->sql_in( '{bug}.os_build', $t_array ); 1043 $this->add_where( $t_query ); 1044 } 1045 1046 /** 1047 * Build the query parts for the filter property "fixed in version" 1048 * @return void 1049 */ 1050 protected function build_prop_fixed_version() { 1051 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_FIXED_IN_VERSION] ) ) { 1052 return; 1053 } 1054 $t_array = $this->helper_process_string_property( $this->filter[FILTER_PROPERTY_FIXED_IN_VERSION] ); 1055 $t_query = $this->sql_in( '{bug}.fixed_in_version', $t_array ); 1056 $this->add_where( $t_query ); 1057 } 1058 1059 /** 1060 * Build the query parts for the filter property "target version" 1061 * @return void 1062 */ 1063 protected function build_prop_target_version() { 1064 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_TARGET_VERSION] ) ) { 1065 return; 1066 } 1067 $t_array = $this->helper_process_string_property( $this->filter[FILTER_PROPERTY_TARGET_VERSION] ); 1068 $t_query = $this->sql_in( '{bug}.target_version', $t_array ); 1069 $this->add_where( $t_query ); 1070 } 1071 1072 /** 1073 * Build the query parts for the filter property "monitor by" 1074 * @return void 1075 */ 1076 protected function build_prop_monitor_by() { 1077 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_MONITOR_USER_ID] ) ) { 1078 return; 1079 } 1080 $t_user_ids = $this->helper_process_users_property( $this->filter[FILTER_PROPERTY_MONITOR_USER_ID] ); 1081 $t_use_none = ( in_array( 0, $t_user_ids ) ); 1082 1083 # Build a condition for determining monitoring visibility, the user can view: 1084 # - his own monitored issues 1085 # - other users monitoring if he meets access level for 'show_monitor_list_threshold' 1086 $t_projects_can_view = $this->helper_filter_projects_using_access( 'show_monitor_list_threshold' ); 1087 if( ALL_PROJECTS == $t_projects_can_view ) { 1088 $t_view_condition = null; 1089 } else { 1090 $t_view_condition = '{bug_monitor}.user_id = ' . $this->param( $this->user_id ); 1091 if( !empty( $t_projects_can_view ) ) { 1092 $t_view_condition = '(' . $t_view_condition . ' OR ' 1093 . $this->sql_in( '{bug}.project_id', $t_projects_can_view ) . ')'; 1094 } 1095 } 1096 if( $t_view_condition ) { 1097 $t_view_condition = ' AND ' . $t_view_condition; 1098 } 1099 1100 $this->add_join( 'LEFT JOIN {bug_monitor} ON {bug}.id = {bug_monitor}.bug_id' . $t_view_condition ); 1101 if( $t_use_none ) { 1102 $t_expr = 'COALESCE( {bug_monitor}.user_id, 0 )'; 1103 } else { 1104 $t_expr = '{bug_monitor}.user_id'; 1105 } 1106 1107 $t_where = $this->sql_in( $t_expr, $t_user_ids ); 1108 $this->add_where( $t_where ); 1109 } 1110 1111 /** 1112 * Creates a JOIN clause for the bugnote table and returns the table alias used 1113 * for this join, to be used in sql expressions. 1114 * This JOIN is built with restrictions to meet user permissions to view private notes. 1115 * 1116 * The JOIN is created only once for this class, If it's already created, this function 1117 * returns the alias to be reused. 1118 * 1119 * @return string A table alias for this join clause 1120 */ 1121 protected function helper_table_alias_for_bugnote() { 1122 if( $this->rt_table_alias_bugnote ) { 1123 return $this->rt_table_alias_bugnote; 1124 } 1125 # Build a condition for determining note visibility, the user can view: 1126 # - public notes 1127 # - his own private notes 1128 # - private notes if meets access level for 'private_bugnote_threshold' 1129 $t_projects_can_view_private = $this->helper_filter_projects_using_access( 'private_bugnote_threshold' ); 1130 $t_table_alias = 'visible_bugnote'; 1131 if( ALL_PROJECTS == $t_projects_can_view_private ) { 1132 $t_view_condition = null; 1133 } else { 1134 $t_view_condition = $t_table_alias . '.view_state = ' . $this->param( VS_PUBLIC ) 1135 . ' OR ' . $t_table_alias . '.reporter_id = ' . $this->param( $this->user_id ); 1136 if( !empty( $t_projects_can_view_private ) ) { 1137 $t_view_condition .= ' OR ' . $this->sql_in( '{bug}.project_id', $t_projects_can_view_private ); 1138 } 1139 } 1140 if( $t_view_condition ) { 1141 $t_view_condition = ' AND (' . $t_view_condition . ')'; 1142 } 1143 $t_join = 'LEFT JOIN {bugnote} ' . $t_table_alias 1144 . ' ON {bug}.id = ' . $t_table_alias . '.bug_id' 1145 . $t_view_condition; 1146 1147 $this->add_join( $t_join ); 1148 $this->rt_table_alias_bugnote = $t_table_alias; 1149 return $this->rt_table_alias_bugnote; 1150 } 1151 1152 /** 1153 * Build the query parts for the filter property "note by" 1154 * @return void 1155 */ 1156 protected function build_prop_note_by() { 1157 if( filter_field_is_any( $this->filter[FILTER_PROPERTY_NOTE_USER_ID] ) ) { 1158 return; 1159 } 1160 $t_user_ids = $this->helper_process_users_property( $this->filter[FILTER_PROPERTY_NOTE_USER_ID] ); 1161 $t_use_none = ( in_array( 0, $t_user_ids ) ); 1162 1163 $t_table_alias = $this->helper_table_alias_for_bugnote(); 1164 1165 if( $t_use_none ) { 1166 $t_alias = 'COALESCE( ' . $t_table_alias . '.reporter_id, 0 )'; 1167 } else { 1168 $t_alias = $t_table_alias . '.reporter_id'; 1169 } 1170 1171 $t_where = $this->sql_in( $t_alias, $t_user_ids ); 1172 $this->add_where( $t_where ); 1173 } 1174 1175 /** 1176 * Build the query parts for the filter property "relationship" 1177 * @return void 1178 */ 1179 protected function build_prop_relationship() { 1180 $c_rel_type = (int)$this->filter[FILTER_PROPERTY_RELATIONSHIP_TYPE]; 1181 $c_rel_bug = (int)$this->filter[FILTER_PROPERTY_RELATIONSHIP_BUG]; 1182 if( BUG_REL_ANY == $c_rel_type && META_FILTER_ANY == $c_rel_bug ) { 1183 return; 1184 } 1185 # use the complementary type 1186 if( $c_rel_type >= 0 ) { 1187 $t_comp_type = relationship_get_complementary_type( $c_rel_type ); 1188 } 1189 $t_table_dst = 'rel_dst'; 1190 $t_table_src = 'rel_src'; 1191 $t_use_join = true; 1192 1193 # build conditions for relation type and bug match 1194 if( BUG_REL_NONE == $c_rel_type ) { 1195 if( META_FILTER_NONE == $c_rel_bug 1196 || META_FILTER_ANY == $c_rel_bug ) { 1197 # rel NONE, bug ANY/NONE, those bugs that are not related in any way to another 1198 $t_where = $t_table_dst . '.relationship_type IS NULL AND ' . $t_table_src . '.relationship_type IS NULL'; 1199 } else { 1200 # rel NONE, bug ID, those bugs that are not related in any way to bug ID 1201 # also, exclude target id from results 1202 $t_where = 'NOT EXISTS ( SELECT 1 FROM {bug_relationship} WHERE source_bug_id = ' . $this->param( $c_rel_bug ) 1203 . ' AND destination_bug_id = {bug}.id' 1204 . ' OR destination_bug_id = ' . $this->param( $c_rel_bug ) 1205 . ' AND source_bug_id = {bug}.id )' 1206 . ' AND NOT {bug}.id = ' . $this->param( $c_rel_bug ); 1207 $t_use_join = false; 1208 } 1209 } elseif( BUG_REL_ANY == $c_rel_type ) { 1210 if( META_FILTER_NONE == $c_rel_bug ) { 1211 # rel ANY, bug NONE, bugs that are not related in any way to another 1212 $t_where = $t_table_dst . '.relationship_type IS NULL AND ' . $t_table_src . '.relationship_type IS NULL'; 1213 } elseif ( META_FILTER_ANY == $c_rel_bug ) { 1214 # rel ANY, bug ANY, do nothing 1215 return; 1216 } else { 1217 # rel ANY, bug ID, those bugs that have any relation to bug ID 1218 $t_where = '(' . $t_table_dst . '.source_bug_id = ' . $this->param( $c_rel_bug ) 1219 . ' OR ' . $t_table_src . '.destination_bug_id = ' . $this->param( $c_rel_bug ) . ')'; 1220 } 1221 } else { 1222 # relation is specified 1223 if( META_FILTER_NONE == $c_rel_bug ) { 1224 # rel REL, bug NONE, those bugs that don't have any REL relation (may have other types) 1225 # map to a non-existent relation type -1 to include nulls 1226 $t_where = 'COALESCE(' . $t_table_dst . '.relationship_type, -1) <> ' . $this->param( $t_comp_type ) 1227 . ' AND COALESCE(' . $t_table_src . '.relationship_type, -1) <> ' . $this->param( $c_rel_type ); 1228 } elseif( META_FILTER_ANY == $c_rel_bug ) { 1229 # rel REL, bug ANY, those bugs that are related by REL to any bug 1230 $t_where = '(' . $t_table_dst . '.relationship_type=' . $this->param( $t_comp_type ) 1231 . ' OR ' . $t_table_src . '.relationship_type=' . $this->param( $c_rel_type ) . ')'; 1232 } else { 1233 # rel REL, bug ID, those bugs that are related by REL to bug ID 1234 $t_where = '(' 1235 . $t_table_dst . '.relationship_type=' . $this->param( $t_comp_type ) 1236 . ' AND ' . $t_table_dst . '.source_bug_id=' . $this->param( $c_rel_bug ) 1237 . ' OR ' 1238 . $t_table_src . '.relationship_type=' . $this->param( $c_rel_type ) 1239 . ' AND ' . $t_table_src . '.destination_bug_id=' . $this->param( $c_rel_bug ) 1240 . ')'; 1241 } 1242 } 1243 1244 if( $t_use_join ) { 1245 $this->add_join( 'LEFT JOIN {bug_relationship} ' . $t_table_dst . ' ON ' . $t_table_dst . '.destination_bug_id = {bug}.id' ); 1246 $this->add_join( 'LEFT JOIN {bug_relationship} ' . $t_table_src . ' ON ' . $t_table_src . '.source_bug_id = {bug}.id' ); 1247 } 1248 $this->add_where( $t_where ); 1249 } 1250 1251 /** 1252 * Utility function to return the projects, from the current filter scope, that meets some 1253 * access level threshold. The specified access can be either a int/array threshold, or 1254 * a configuration option to be evaluated at each project 1255 * (see documentation for access_project_array_filter() ) 1256 * 1257 * The returned value can be: 1258 * - All_PROJECTS constant: meaning that all projects reached by the current filter meets the requested access. 1259 * - Empty array: meaning that none of the filter projects meets the required access 1260 * - Array of project ids: containing those projects which meets the requested access 1261 * Note that if all projects meet the access, then ALL_PROJECTS will be returned. 1262 * 1263 * @param integer|array|string $p_access An access level threshold or configuration option 1264 * @return array|integer 1265 */ 1266 protected function helper_filter_projects_using_access( $p_access ) { 1267 $t_filtered_projects = access_project_array_filter( $p_access, $this->rt_included_projects, $this->user_id ); 1268 $t_diff = array_diff( $this->rt_included_projects, $t_filtered_projects ); 1269 if( empty( $t_diff ) ) { 1270 return ALL_PROJECTS; 1271 } else { 1272 return $t_filtered_projects; 1273 } 1274 } 1275 1276 /** 1277 * Utility function to cast all array element to int type 1278 * @param array $p_array Input array 1279 * @return array Converted array 1280 */ 1281 protected function helper_array_map_int( $p_array ) { 1282 $t_new_array = array(); 1283 foreach( $p_array as $t_elem ) { 1284 $t_new_array[] = (int)$t_elem; 1285 } 1286 return $t_new_array; 1287 } 1288 1289 /** 1290 * Build the query parts for the filter property "tags" 1291 * @return void 1292 */ 1293 protected function build_prop_tags() { 1294 $c_tag_string = trim( $this->filter[FILTER_PROPERTY_TAG_STRING] ); 1295 $c_tag_select = (int)$this->filter[FILTER_PROPERTY_TAG_SELECT]; 1296 if( is_blank( $c_tag_string ) && $c_tag_select == 0 ) { 1297 # shortcut exit 1298 return; 1299 } 1300 1301 $t_tags = tag_parse_filters( $c_tag_string ); 1302 if( empty( $t_tags ) && $c_tag_select == 0 ) { 1303 # shortcut exit 1304 return; 1305 } 1306 1307 $t_projects_can_view_tags = $this->helper_filter_projects_using_access( 'tag_view_threshold' ); 1308 if( ALL_PROJECTS == $t_projects_can_view_tags ) { 1309 $t_tag_projects_clause = ''; 1310 } else { 1311 if( empty( $t_projects_can_view_tags ) ) { 1312 # if can't view tags in any project, exit 1313 log_event( LOG_FILTERING, 'tags query, no accessible projects ' ); 1314 return; 1315 } else { 1316 $t_tag_projects_clause = ' AND ' . $this->sql_in( '{bug}.project_id', $t_projects_can_view_tags ); 1317 log_event( LOG_FILTERING, 'tags query, accessible projects = @P' . implode( ', @P', $t_projects_can_view_tags ) ); 1318 } 1319 } 1320 1321 $t_tags_always = array(); 1322 $t_tags_any = array(); 1323 $t_tags_never = array(); 1324 1325 # @TODO, use constants for tag modifiers 1326 foreach( $t_tags as $t_tag_row ) { 1327 switch( $t_tag_row['filter'] ) { 1328 case 1: 1329 # A matched issue must always have this tag 1330 $t_tags_always[] = $t_tag_row; 1331 break; 1332 case 0: 1333 # A matched issue may have this tag 1334 $t_tags_any[] = $t_tag_row; 1335 break; 1336 case -1: 1337 # A matched must never have this tag 1338 $t_tags_never[] = $t_tag_row; 1339 break; 1340 } 1341 } 1342 1343 # Consider those tags that must always match, to also be part of those that can be 1344 # optionally matched. This solves the scenario for an issue that matches one tag 1345 # from the "always" group, and none from the "any" group. 1346 if( !empty( $t_tags_always ) && !empty( $t_tags_any ) ) { 1347 $t_tags_any = array_merge( $t_tags_any, $t_tags_always ); 1348 } 1349 1350 # Add the tag id to the array, from filter field "tag_select" 1351 if( 0 < $c_tag_select && tag_exists( $c_tag_select ) ) { 1352 $t_tags_any[] = tag_get( $c_tag_select ); 1353 } 1354 1355 $t_where = array(); 1356 1357 if( count( $t_tags_always ) ) { 1358 foreach( $t_tags_always as $t_tag_row ) { 1359 $t_tag_alias = 'bug_tag_alias_alw_' . $t_tag_row['id']; 1360 $t_join_inc = 'LEFT JOIN {bug_tag} ' . $t_tag_alias . ' ON ' . $t_tag_alias . '.bug_id = {bug}.id' 1361 . ' AND ' . $t_tag_alias . '.tag_id = ' . $this->param( (int)$t_tag_row['id'] ) 1362 . $t_tag_projects_clause; 1363 $this->add_join( $t_join_inc ); 1364 $t_where[] = $t_tag_alias . '.tag_id IS NOT NULL'; 1365 } 1366 } 1367 1368 if( count( $t_tags_any ) ) { 1369 $t_tag_alias = 'bug_tag_alias_any'; 1370 $t_tag_ids = $this->helper_array_map_int( array_column( $t_tags_any, 'id' ) ); 1371 $t_join_any = 'LEFT JOIN {bug_tag} ' . $t_tag_alias . ' ON ' . $t_tag_alias . '.bug_id = {bug}.id' 1372 . ' AND ' . $this->sql_in( $t_tag_alias . '.tag_id', $t_tag_ids ) 1373 . $t_tag_projects_clause; 1374 $this->add_join( $t_join_any ); 1375 $t_where[] = $t_tag_alias . '.tag_id IS NOT NULL'; 1376 } 1377 1378 if( count( $t_tags_never ) ) { 1379 $t_tag_alias = 'bug_tag_alias_nev'; 1380 $t_tag_ids = $this->helper_array_map_int( array_column( $t_tags_never, 'id' ) ); 1381 $t_join_exc = 'LEFT JOIN {bug_tag} ' . $t_tag_alias . ' ON ' . $t_tag_alias . '.bug_id = {bug}.id' 1382 . ' AND ' . $this->sql_in( $t_tag_alias . '.tag_id', $t_tag_ids ) 1383 . $t_tag_projects_clause; 1384 $this->add_join( $t_join_exc ); 1385 $t_where[] = $t_tag_alias . '.tag_id IS NULL'; 1386 } 1387 1388 if( !empty( $t_where ) ) { 1389 $this->add_where( implode( ' AND ', $t_where ) ); 1390 } 1391 } 1392 1393 /** 1394 * Creates a JOIN clause for the custom field table and returns the table 1395 * alias used for this join. 1396 * May return false if the join was not created. This may happen, if no 1397 * values are viewable. 1398 * @param array $p_cfdef Custom field definition array 1399 * @return string A table alias for this join clause 1400 */ 1401 protected function helper_table_alias_for_cf( $p_cfdef ) { 1402 $t_id = (int)$p_cfdef['id']; 1403 if( isset( $this->rt_table_alias_cf[$t_id] ) ) { 1404 return $this->rt_table_alias_cf[$t_id]; 1405 } 1406 $t_table_name = 'cf_alias_' . $t_id; 1407 $t_cf_join_clause = 'LEFT OUTER JOIN {custom_field_string} ' . $t_table_name . ' ON {bug}.id = ' . $t_table_name . '.bug_id AND ' . $t_table_name . '.field_id = ' . $this->param( $t_id ); 1408 1409 # get which projects are valid for this custom field 1410 $t_searchable_projects = array_intersect( $this->rt_included_projects, custom_field_get_project_ids( $t_id ) ); 1411 # and for which of those projects the user have read access to this field 1412 $t_projects_can_view_field = access_project_array_filter( (int)$p_cfdef['access_level_r'], $t_searchable_projects, $this->user_id ); 1413 if( empty( $t_projects_can_view_field ) ) { 1414 $this->rt_table_alias_cf[$t_id] = false; 1415 } else { 1416 # This diff will contain those included projects that can't view this custom field 1417 $t_diff = array_diff( $this->rt_included_projects, $t_projects_can_view_field ); 1418 # If not empty, it means there are some projects that can't view the field values, 1419 # so a project filter must be used to not include values from those projects 1420 if( !empty( $t_diff ) ) { 1421 $t_cf_join_clause .= ' AND ' . $this->sql_in( '{bug}.project_id', $t_projects_can_view_field ); 1422 } 1423 $this->rt_table_alias_cf[$t_id] = $t_table_name; 1424 $this->add_join( $t_cf_join_clause ); 1425 } 1426 return $this->rt_table_alias_cf[$t_id]; 1427 } 1428 1429 /** 1430 * Build the query parts for the filter properties related to custom fields 1431 * @return void 1432 */ 1433 protected function build_prop_custom_fields() { 1434 if( ON != config_get( 'filter_by_custom_fields' ) ) { 1435 log_event( LOG_FILTERING, 'filter custom fields is globally disabled, skip' ); 1436 return; 1437 } 1438 1439 $t_custom_fields = custom_field_get_linked_ids( $this->rt_included_projects ); 1440 1441 foreach( $t_custom_fields as $t_cfid ) { 1442 $t_field_info = custom_field_cache_row( $t_cfid, true ); 1443 if( !$t_field_info['filter_by'] ) { 1444 # skip this custom field if it shouldn't be filterable 1445 log_event( LOG_FILTERING, 'filter custom fields, field_id=' . $t_cfid . ' is not valid for filtering'); 1446 continue; 1447 } 1448 1449 $t_field = $this->filter['custom_fields'][$t_cfid]; 1450 1451 $t_custom_where_clause = ''; 1452 $t_def = custom_field_get_definition( $t_cfid ); 1453 1454 # Skip date custom fields with value of "any", these have a special array format 1455 if( $t_def['type'] == CUSTOM_FIELD_TYPE_DATE && $t_field[0] == CUSTOM_FIELD_DATE_ANY ) { 1456 continue; 1457 } 1458 # Ignore custom fields that are not set, or that are set to '' or "any" 1459 if( filter_field_is_any( $t_field ) ) { 1460 continue; 1461 } 1462 1463 $t_table_name = $this->helper_table_alias_for_cf( $t_def ); 1464 if( !$t_table_name ) { 1465 continue; 1466 } 1467 1468 if( $t_def['type'] == CUSTOM_FIELD_TYPE_DATE ) { 1469 # Define the value field with type cast to integer 1470 $t_value_field = 'CAST(COALESCE(NULLIF(' . $t_table_name . '.value, \'\'), \'0\') AS DECIMAL)'; 1471 switch( $t_field[0] ) { 1472 # Closing parenthesis intentionally omitted, will be added later on 1473 # CUSTOM_FIELD_DATE_ANY can't appear here, it was previously skipped 1474 case CUSTOM_FIELD_DATE_NONE: 1475 $t_custom_where_clause = '( ' . $t_table_name . '.bug_id IS NULL OR ' . $t_value_field . ' = 0 '; 1476 break; 1477 case CUSTOM_FIELD_DATE_BEFORE: 1478 $t_custom_where_clause = '( ' . $t_value_field . ' != 0 AND ' . $t_value_field . ' < ' . $this->param( $t_field[2] ); 1479 break; 1480 case CUSTOM_FIELD_DATE_AFTER: 1481 $t_custom_where_clause = '( ' . $t_value_field . ' > ' . $this->param( $t_field[1] + 1 ); 1482 break; 1483 default: 1484 $t_custom_where_clause = '( ' . $t_value_field . ' BETWEEN ' . $this->param( $t_field[1] ) . ' AND ' . $this->param( $t_field[2] ); 1485 break; 1486 } 1487 } else { 1488 $t_filter_array = array(); 1489 foreach( $t_field as $t_filter_member ) { 1490 $t_filter_member = stripslashes( $t_filter_member ); 1491 if( filter_field_is_none( $t_filter_member ) ) { 1492 # but also add those _not_ present in the custom field string table 1493 $t_filter_array[] = $t_table_name . '.value IS NULL'; 1494 1495 switch( $t_def['type'] ) { 1496 case CUSTOM_FIELD_TYPE_TEXTAREA: 1497 $t_filter_array[] = $t_table_name . '.text = ' . $this->param( '' ); 1498 break; 1499 default; 1500 $t_filter_array[] = $t_table_name . '.value = ' . $this->param( '' ); 1501 } 1502 } else { 1503 switch( $t_def['type'] ) { 1504 case CUSTOM_FIELD_TYPE_CHECKBOX: 1505 case CUSTOM_FIELD_TYPE_MULTILIST: 1506 $t_filter_array[] = $this->sql_like( $t_table_name . '.value', '%|' . $t_filter_member . '|%' ); 1507 break; 1508 case CUSTOM_FIELD_TYPE_TEXTAREA: 1509 $t_filter_array[] = $this->sql_like( $t_table_name . '.text', '%' . $t_filter_member . '%' ); 1510 break; 1511 default: 1512 $t_filter_array[] = $t_table_name . '.value = ' . $this->param( $t_filter_member ); 1513 } 1514 } 1515 } 1516 $t_custom_where_clause .= '(' . implode( ' OR ', $t_filter_array ); 1517 } 1518 if( !is_blank( $t_custom_where_clause ) ) { 1519 $this->add_where( $t_custom_where_clause . ')' ); 1520 } 1521 } # foreach cf 1522 } 1523 1524 /** 1525 * Build the query parts for the filter property "text search" 1526 * @return void 1527 */ 1528 protected function build_prop_search() { 1529 if( is_blank( $this->filter[FILTER_PROPERTY_SEARCH] ) ) { 1530 return; 1531 } 1532 1533 # break up search terms by spacing or quoting 1534 preg_match_all( "/-?([^'\"\s]+|\"[^\"]+\"|'[^']+')/", $this->filter[FILTER_PROPERTY_SEARCH], $t_matches, PREG_SET_ORDER ); 1535 1536 # organize terms without quoting, paying attention to negation 1537 $t_search_terms = array(); 1538 foreach( $t_matches as $t_match ) { 1539 $t_search_terms[trim( $t_match[1], "\'\"" )] = ( $t_match[0][0] == '-' ); 1540 } 1541 1542 $t_bugnote_table = $this->helper_table_alias_for_bugnote(); 1543 1544 # build a big where-clause and param list for all search terms, including negations 1545 $t_first = true; 1546 $t_textsearch_where_clause = '( '; 1547 foreach( $t_search_terms as $t_search_term => $t_negate ) { 1548 if( !$t_first ) { 1549 $t_textsearch_where_clause .= ' AND '; 1550 } 1551 1552 if( $t_negate ) { 1553 $t_textsearch_where_clause .= 'NOT '; 1554 } 1555 1556 $c_search = '%' . $t_search_term . '%'; 1557 $t_textsearch_where_clause .= '( ' . $this->sql_like( '{bug}.summary', $c_search ) 1558 . ' OR ' . $this->sql_like( '{bug_text}.description', $c_search ) 1559 . ' OR ' . $this->sql_like( '{bug_text}.steps_to_reproduce', $c_search ) 1560 . ' OR ' . $this->sql_like( '{bug_text}.additional_information', $c_search ) 1561 . ' OR ' . $this->sql_like( '{bugnote_text}.note', $c_search ); 1562 1563 if( is_numeric( $t_search_term ) ) { 1564 # Note: no need to test negative values, '-' sign has been removed 1565 if( $t_search_term <= DB_MAX_INT ) { 1566 $c_search_int = (int)$t_search_term; 1567 $t_textsearch_where_clause .= ' OR {bug}.id = ' . $this->param( $c_search_int ); 1568 $t_textsearch_where_clause .= ' OR ' . $t_bugnote_table . '.id = ' . $this->param( $c_search_int ); 1569 } 1570 } 1571 1572 $t_textsearch_where_clause .= ' )'; 1573 $t_first = false; 1574 } 1575 $t_textsearch_where_clause .= ' )'; 1576 1577 # add text query elements to arrays 1578 if( !$t_first ) { 1579 # join with bugnote table has already been created or reused 1580 $this->add_join( 'JOIN {bug_text} ON {bug}.bug_text_id = {bug_text}.id' ); 1581 # Outer join required otherwise we don't retrieve issues without notes 1582 $this->add_join( 'LEFT JOIN {bugnote_text} ON ' . $t_bugnote_table . '.bugnote_text_id = {bugnote_text}.id' ); 1583 $this->add_where( $t_textsearch_where_clause ); 1584 } 1585 1586 } 1587 1588 /** 1589 * Translates a sql string created with legacy db_param() syntax, into 1590 * a string with valid parameters and values binded to current query object. 1591 * @param string $p_string Sql string 1592 * @param array $p_params Array of parameter values 1593 * @return string 1594 */ 1595 protected function helper_convert_legacy_clause( $p_string, array $p_params = null ) { 1596 if( empty( $p_params ) ) { 1597 # shortcut, if there are no parameters, there's no need to translate 1598 return $p_string; 1599 } 1600 $t_params = array_values( $p_params ); 1601 $t_param_index = 0; 1602 $cb_add_param = function ( $t_matches ) use ( $t_params, &$t_param_index ) { 1603 return $this->param( $t_params[$t_param_index++] ); 1604 }; 1605 1606 $t_new_string = preg_replace_callback( '/(?<token>\?|\$|:)(?<index>[0-9]*)/', $cb_add_param, $p_string ); 1607 return $t_new_string; 1608 } 1609 1610 /** 1611 * Build the query parts for the filter properties related to plugin filter fields 1612 * @return void 1613 */ 1614 protected function build_prop_plugin_filters() { 1615 $t_plugin_filters = filter_get_plugin_filters(); 1616 foreach( $t_plugin_filters as $t_field_name => $t_filter_object ) { 1617 if( !filter_field_is_any( $this->filter[$t_field_name] ) || $t_filter_object->type == FILTER_TYPE_BOOLEAN ) { 1618 $t_filter_query = $t_filter_object->query( $this->filter[$t_field_name] ); 1619 if( is_array( $t_filter_query ) ) { 1620 if( isset( $t_filter_query['join'] ) ) { 1621 $this->add_join( $t_filter_query['join'] ); 1622 } 1623 $t_params = null; 1624 if( isset( $t_filter_query['params'] ) && is_array( $t_filter_query['params'] ) ) { 1625 $t_params = $t_filter_query['params']; 1626 } 1627 if( isset( $t_filter_query['where'] ) ) { 1628 $t_where = $this->helper_convert_legacy_clause( $t_filter_query['where'], $t_params ); 1629 $this->add_where( $t_where ); 1630 } 1631 } 1632 } 1633 } 1634 } 1635 1636 /** 1637 * Return a column name for the specified property to sort on. 1638 * Valid only for standard bug table fields 1639 * Manages joining with other tables to allow sorting by display names instead 1640 * of its numerical ids. 1641 * @param string $p_prop Filter property for sorting 1642 * @return string A column alias to be used in the sql order part 1643 */ 1644 protected function helper_sort_column_alias( $p_prop ) { 1645 1646 switch( $p_prop ) { 1647 1648 case 'category_id': 1649 # This join will be reduced as unique, if category search is active 1650 $this->add_join( 'LEFT JOIN {category} ON {bug}.category_id = {category}.id' ); 1651 $this->add_select( '{category}.name' ); 1652 return '{category}.name'; 1653 break; 1654 1655 case 'project_id': 1656 # project table is already joined by default 1657 $this->add_select( '{project}.name' ); 1658 return '{project}.name'; 1659 break; 1660 1661 case 'handler_id': 1662 case 'reporter_id': 1663 $t_table_alias = $p_prop . '_sort_table'; 1664 $t_join = 'LEFT JOIN {user} ' . $t_table_alias 1665 . ' ON {bug}.' . $p_prop . ' = ' . $t_table_alias . '.id'; 1666 $this->add_join( $t_join ); 1667 $t_col_alias = $p_prop . '_sort_alias'; 1668 1669 # sorting by username: coalesce( username, $prefix_for_deleted || id ) 1670 # sorting by realname: coalesce( nullif(realname,''), username, $prefix_for_deleted || id ) 1671 $t_select = 'COALESCE('; 1672 # Note: show_realname should only be set at global or all_projects 1673 # Note: sort_by_last_name is not supported here 1674 if( user_show_realname() ) { 1675 $t_select .= 'NULLIF(' . $t_table_alias . '.realname, \'\'), '; 1676 } 1677 $t_select .= $t_table_alias . '.username, '; 1678 $t_select .= 'CONCAT(\'' . lang_get( 'prefix_for_deleted_users' ) . '\', {bug}.' . $p_prop . ')'; 1679 $t_select .= ') AS ' . $t_col_alias; 1680 1681 $this->add_select( $t_select ); 1682 return $t_col_alias; 1683 break; 1684 1685 default: 1686 return '{bug}.' . $p_prop; 1687 } 1688 } 1689 1690 /** 1691 * Build the query parts for the filter related to sorting 1692 * @return void 1693 */ 1694 protected function build_order_by() { 1695 1696 # Get only the visible, and sortable, column properties 1697 # @TODO cproensa: this defaults to COLUMNS_TARGET_VIEW_PAGE 1698 # are we sure that filters are only used with the column set for view page? 1699 $t_sort_properties = filter_get_visible_sort_properties_array( $this->filter ); 1700 $t_sort_fields = $t_sort_properties[FILTER_PROPERTY_SORT_FIELD_NAME]; 1701 $t_dir_fields = $t_sort_properties[FILTER_PROPERTY_SORT_DIRECTION]; 1702 1703 if( gpc_string_to_bool( $this->filter[FILTER_PROPERTY_STICKY] ) && ( $this->use_sticky ) ) { 1704 $this->add_order( '{bug}.sticky DESC' ); 1705 } 1706 1707 $t_count = count( $t_sort_fields ); 1708 for( $i = 0; $i < $t_count; $i++ ) { 1709 $c_sort = $t_sort_fields[$i]; 1710 $c_dir = 'DESC' == $t_dir_fields[$i] ? 'DESC' : 'ASC'; 1711 1712 # if sorting by a custom field 1713 if( column_is_custom_field( $c_sort ) ) { 1714 $t_custom_field = column_get_custom_field_name( $c_sort ); 1715 $t_custom_field_id = custom_field_get_id_from_name( $t_custom_field ); 1716 $t_def = custom_field_get_definition( $t_custom_field_id ); 1717 $t_value_field = ( $t_def['type'] == CUSTOM_FIELD_TYPE_TEXTAREA ? 'text' : 'value' ); 1718 1719 $t_table_name = $this->helper_table_alias_for_cf( $t_def ); 1720 if( !$t_table_name ) { 1721 continue; 1722 } 1723 1724 # if no join can be used (eg, no view access), skip this field from the order clause 1725 if( empty( $t_table_name ) ) { 1726 continue; 1727 } 1728 1729 $t_field_alias = 'cf_sortfield_' . $t_custom_field_id; 1730 $t_sort_col = $t_table_name . '.' . $t_value_field; 1731 1732 # which types need special type cast 1733 switch( $t_def['type'] ) { 1734 case CUSTOM_FIELD_TYPE_FLOAT: 1735 # mysql can't cast to float, use alternative syntax 1736 $t_sort_expr = db_is_mysql() ? $t_sort_col . '+0.0' : 'CAST(NULLIF(' . $t_sort_col . ',\'\') AS FLOAT)'; 1737 break; 1738 case CUSTOM_FIELD_TYPE_DATE: 1739 case CUSTOM_FIELD_TYPE_NUMERIC: 1740 $t_sort_expr = 'CAST(NULLIF(' . $t_sort_col . ',\'\') AS DECIMAL)'; 1741 break; 1742 default: # no cast needed 1743 $t_sort_expr = $t_sort_col; 1744 } 1745 1746 # which types need special treatment for null sorting 1747 switch( $t_def['type'] ) { 1748 case CUSTOM_FIELD_TYPE_DATE: 1749 case CUSTOM_FIELD_TYPE_NUMERIC: 1750 case CUSTOM_FIELD_TYPE_FLOAT: 1751 $t_null_last = true; 1752 break; 1753 default: 1754 $t_null_last = false; 1755 } 1756 1757 if( $t_null_last ) { 1758 $t_null_expr = 'CASE WHEN NULLIF(' . $t_sort_col . ', \'\') IS NULL THEN 1 ELSE 0 END'; 1759 $t_clause_for_select = $t_null_expr . ' AS ' . $t_field_alias . '_null'; 1760 $t_clause_for_select .= ', ' . $t_sort_expr . ' AS ' . $t_field_alias; 1761 $t_clause_for_order = $t_field_alias . '_null ASC, ' . $t_field_alias . ' ' . $c_dir; 1762 } else { 1763 $t_clause_for_select = $t_sort_expr . ' AS ' . $t_field_alias; 1764 $t_clause_for_order = $t_field_alias . ' ' . $c_dir; 1765 } 1766 1767 # Note: pgsql needs the sort expression to appear as member of the "select distinct" 1768 $this->add_select( $t_clause_for_select ); 1769 $this->add_order( $t_clause_for_order ); 1770 1771 # if sorting by plugin columns 1772 } else if( column_is_plugin_column( $c_sort ) ) { 1773 $t_plugin_columns = columns_get_plugin_columns(); 1774 $t_column_object = $t_plugin_columns[$c_sort]; 1775 1776 $t_clauses = $t_column_object->sortquery( $c_dir ); 1777 if( is_array( $t_clauses ) ) { 1778 if( isset( $t_clauses['select'] ) ) { 1779 $this->add_select( $t_clauses['select'] ); 1780 } 1781 if( isset( $t_clauses['join'] ) ) { 1782 $this->add_join( $t_clauses['join'] ); 1783 } 1784 if( isset( $t_clauses['order'] ) ) { 1785 $this->add_order( $t_clauses['order'] ); 1786 } 1787 } 1788 1789 # standard column 1790 } else { 1791 $t_sort_col = $this->helper_sort_column_alias( $c_sort ); 1792 1793 # When sorting by due_date, always display undefined dates last. 1794 # Undefined date is defaulted as "1" in database, so add a special 1795 # sort clause to group and sort by this. 1796 if( 'due_date' == $c_sort && 'ASC' == $c_dir ) { 1797 $t_null_expr = 'CASE ' . $t_sort_col . ' WHEN 1 THEN 1 ELSE 0 END'; 1798 $this->add_select( $t_null_expr . ' AS due_date_sort_null' ); 1799 $this->add_order( 'due_date_sort_null ASC' ); 1800 } 1801 # main sort clause for due date 1802 $this->add_order( $t_sort_col . ' ' .$c_dir ); 1803 } 1804 } 1805 1806 # add basic sorting if necessary 1807 if( !in_array( 'last_updated', $t_sort_fields ) ) { 1808 $this->add_order( '{bug}.last_updated DESC' ); 1809 } 1810 if( !in_array( 'date_submitted', $t_sort_fields ) ) { 1811 $this->add_order( '{bug}.date_submitted DESC' ); 1812 } 1813 } 1814} 1815