1<?php 2// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project 3// 4// All Rights Reserved. See copyright.txt for details and a complete list of authors. 5// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details. 6// $Id$ 7 8/** 9 * Tracker Query Library 10 * 11 * A full featured, chainable, ORM for trackers 12 * <p> 13 * chainable became popular in jQuery $(this)->fn()->fn() and is more and more popular in php. Tracker_Query uses them 14 * to make Trackers, which are somewhat complex, easy. 15 * 16 * 17 * Examples of usage (fake tracker called 'Event Tracker': 18 * //using id 19 * $results = Tracker_Query(1) 20 * ->byName() 21 * ->itemId(100) 22 * ->query(); 23 * 24 * //using by name 25 * $results = Tracker_Query('Event Tracker') 26 * ->byName() 27 * ->limit(1) 28 * ->query(); 29 * 30 * 31 *<p> 32 * The Output of tracker query is built as tracker(items(fields())) with the keys being the id (or name for fields if byName is called). 33 * Standard output example ($this->byName() not called): 34 * Array 35 * ( 36 * [367] => Array //item repeats, key = itemId 37 * ( 38 * [19] => 369 //item field values, key = fieldId 39 * [20] => 366 40 * [status5] => c 41 * [trackerId] => 5 42 * [itemId] => 367 43 * [11] => internal 44 * [162] => Array //items list associated to filedId 162 45 * ( 46 * [0] => 176 // itemId 47 * [1] => Event Name // static name of an itemId 48 * ) 49 * ) 50 * ) 51 * ) 52 * 53 * ByName output example ($this->byName() called): 54 * Array 55 * ( 56 * [367] => Array //item repeats, key = itemId 57 * ( 58 * [Minute End] => 369 //item field values, key = fieldId 59 * [Minute Start] => 366 60 * [status5] => c 61 * [trackerId] => 5 62 * [itemId] => 367 63 * [Use Case] => internal 64 * [Events] => Array //items list associated to filedId 162 65 * ( 66 * [0] => 176 // itemId 67 * [1] => Event Name // static name of an itemId 68 * ) 69 * ) 70 * ) 71 * ) 72 * 73 * @package Tiki 74 * @subpackage Trackers 75 * @author Robert Plummer 76 * @link http://dev.tiki.org/Tracker_Query 77 * @since TIki 8 78 */ 79 80class Tracker_Query 81{ 82 private $tracker; 83 private $start = 0; 84 private $end = 0; 85 private $itemId = 0; 86 private $equals = []; 87 private $search = []; 88 private $fields = []; 89 private $status = "opc"; 90 private $sort = null; 91 private $limit = 100; //added limit so default wouldn't crash system 92 private $offset = 0; 93 private $byName = false; 94 private $desc = false; 95 private $render = true; 96 private $excludeDetails = false; 97 private $lastModif = true; 98 private $delimiter = "[{|!|}]"; 99 private $debug = false; 100 private $concat = true; 101 private $filterType = []; 102 private $inputDefaults = []; 103 public $itemsRaw = []; 104 public $permissionsChecks = true; 105 public $limitReached = false; 106 107 /** 108 * Instantiates a tracker query 109 * 110 * @access public static 111 * @param mixed $tracker id, (or name if called $this->byName() before query) 112 * @return new self 113 */ 114 public static function tracker($tracker) 115 { 116 return (new Tracker_Query($tracker)); 117 } 118 119 /** 120 * Overrides permissions 121 * 122 * @access public 123 * @param bool $permissionsChecks, default true 124 * @return new self 125 */ 126 public function permissionsChecks($permissionsChecks = true) 127 { 128 $this->permissionsChecks = $permissionsChecks; 129 130 return $this; 131 } 132 133 /** 134 * change the start date unit, needs called before $this->query() 135 * 136 * @access public 137 * @param mixed $start unix time stamp, int or string 138 * @return $this for chainability 139 */ 140 public function start($start) 141 { 142 $this->start = $start; 143 return $this; 144 } 145 146 /** 147 * change the end date unit, needs called before $this->query() 148 * 149 * @access public 150 * @param mixed $end unix time stamp, int or string 151 * @return $this for chainability 152 */ 153 public function end($end) 154 { 155 $this->end = $end; 156 return $this; 157 } 158 159 /** 160 * change the itemid, needs called before $this->query() 161 * 162 * @access public 163 * @param int $itemId to limit output to 1 item with this id 164 * @return $this for chainability 165 */ 166 public function itemId($itemId) 167 { 168 $this->itemId = (int)$itemId; 169 return $this; 170 } 171 172 /** 173 * add a filter for refining results, needs called before $this->query() 174 * 175 * @access public 176 * @param array $filter an array with keys type (like, and, or), field (id or name), and value (value needed 177 * from tracker file item to be returned as a result) 178 * @return $this for chainability 179 */ 180 public function filter($filter = []) 181 { 182 $filter = array_merge( 183 [ 184 'field' => '', 185 'type' => 'and', 186 'value' => '' 187 ], 188 $filter 189 ); 190 191 $this->fields[] = $filter['field']; 192 $this->filterType[] = $filter['type']; //really only things that should be accepted are "and" and "or", woops, and "like" 193 194 if ($filter['type'] == 'like') { 195 $this->search[] = $filter['value']; 196 } else { 197 $this->equals[] = $filter['value']; 198 } 199 200 return $this; 201 } 202 203 /** 204 * filter results on a mysql level using 'and' type, needs called before $this->query() 205 * 206 * @access public 207 * @param mixed $field either id or name when $this->byName() is called 208 * @param string $value 209 * @return $this for chainability 210 */ 211 public function filterFieldByValue($field, $value) 212 { 213 return $this->filter(['field' => $field, 'value' => $value, 'type' => 'and']); 214 } 215 216 /** 217 * filter results on a mysql level using 'like' + 'and' type, needs called before $this->query() 218 * 219 * @access public 220 * @param mixed $field either id or name when $this->byName() is called 221 * @param string $value 222 * @return $this for chainability 223 */ 224 public function filterFieldByValueLike($field, $value) 225 { 226 return $this->filter(['field' => $field, 'value' => $value, 'type' => 'like']); 227 } 228 229 /** 230 * filter results on a mysql level using 'or' type, needs called before $this->query() 231 * 232 * @access public 233 * @param mixed $field either id or name when $this->byName() is called 234 * @param string $value 235 * @return $this for chainability 236 */ 237 public function filterFieldByValueOr($field, $value) 238 { 239 return $this->filter(['field' => $field, 'value' => $value, 'type' => 'or']); 240 } 241 242 /** 243 * deprecated, filter results on a mysql level on field value, needs called before $this->query() 244 * 245 * @access public 246 * @param array $equals 247 * @return $this for chainability 248 */ 249 public function equals($equals = []) 250 { 251 trigger_error("Deprecated, use filter method instead"); 252 253 $this->equals = $equals; 254 return $this; 255 } 256 257 /** 258 * deprecated, filter results on a mysql level on field value, needs called before $this->query() 259 * 260 * @access public 261 * @param array $search either id or name when $this->byName() is called 262 * @return $this for chainability 263 */ 264 public function search($search) 265 { 266 trigger_error("Deprecated, use filter method instead"); 267 268 $this->search = $search; 269 return $this; 270 } 271 272 /** 273 * deprecated, filter results on a mysql level on field value, needs called before $this->query() 274 * 275 * @access public 276 * @param array $fields either id or name when $this->byName() is called 277 * @return $this for chainability 278 */ 279 public function fields($fields = []) 280 { 281 trigger_error("Deprecated, use filter method instead"); 282 283 $this->fields = $fields; 284 return $this; 285 } 286 287 /** 288 * Filter tracker items on status, needs called before $this->query() 289 * 290 * @access public 291 * @param string $status any of or any combination of the 3 characters 'opc' 292 * @return $this for chainability 293 */ 294 public function status($status) 295 { 296 $this->status = $status; 297 return $this; 298 } 299 300 /** 301 * Not yet implemented 302 * 303 * @access public 304 * @param string $sort any of or any combination of the 3 characters 'opc' 305 * @return $this for chainability 306 */ 307 public function sort($sort) 308 { 309 $this->sort = $sort; 310 return $this; 311 } 312 313 /** 314 * Change limit of items, danger with large numbers, needs called before $this->query() 315 * 316 * @access public 317 * @param int $limit amount of items to return, maximum 318 * @return $this for chainability 319 */ 320 public function limit($limit) 321 { 322 $this->limit = $limit; 323 return $this; 324 } 325 326 /** 327 * Change offset, needs called before $this->query() 328 * 329 * @access public 330 * @param int $offset amount of items to offset 331 * @return $this for chainability 332 */ 333 public function offset($offset) 334 { 335 $this->offset = $offset; 336 return $this; 337 } 338 339 /** 340 * Change tracker to use all, in tracker and fields, needs called before $this->query() 341 * 342 * @access public 343 * @param bool $byName default to true, optional 344 * @return $this for chainability 345 */ 346 public function byName($byName = true) 347 { 348 $this->byName = $byName; 349 return $this; 350 } 351 352 /** 353 * order by lastModified, needs called before $this->query(), default to true, needs called before $this->query() 354 * 355 * @access public 356 * @return $this for chainability 357 */ 358 public function lastModif() 359 { 360 $this->lastModif = true; 361 return $this; 362 } 363 364 /** 365 * order by created, needs called before $this->query(), default to false, needs called before $this->query() 366 * 367 * @access public 368 * @return $this for chainability 369 */ 370 public function created() 371 { 372 $this->lastModif = false; 373 return $this; 374 } 375 376 /** 377 * Remove details that come with each tracker item, status, itemId, trackerId, needs called before $this->query() 378 * Default is to include details 379 * 380 * @access public 381 * @param bool $excludeDetails default to true, optional 382 * @return $this for chainability 383 */ 384 public function excludeDetails($excludeDetails = true) 385 { 386 $this->excludeDetails = $excludeDetails; 387 return $this; 388 } 389 390 /** 391 * Sort descending, default false, needs called before $this->query() 392 * 393 * @access public 394 * @param bool $desc default to true, optional 395 * @return $this for chainability 396 */ 397 public function desc($desc = true) 398 { 399 $this->desc = $desc; 400 return $this; 401 } 402 403 /** 404 * Turn rendering for tracker item fields off, effective to make tracker interactions much MUCH faster, needs called before $this->query() 405 * 406 * @access public 407 * @param bool $render 408 * @return $this for chainability 409 */ 410 public function render($render) 411 { 412 $this->render = $render; 413 return $this; 414 } 415 416 /** 417 * sets limit to 1 and calls $this->query() 418 * 419 * @access public 420 * @return query 421 */ 422 public function getOne() 423 { 424 return $this 425 ->limit(1) 426 ->query(); 427 } 428 429 /** 430 * calls desc, sets limit to 1 and calls $this->query() 431 * 432 * @access public 433 * @return query 434 */ 435 public function getLast() 436 { 437 return $this 438 ->desc(true) 439 ->limit(1) 440 ->query(); 441 } 442 443 /** 444 * calls getOne, and returns only the itemId 445 * 446 * @access public 447 * @return int $key itemId 448 */ 449 public function getItemId() 450 { 451 $query = $this->getOne(); 452 $key = (int)end(array_keys($query)); 453 $key = ($key > 0 ? $key : 0); 454 return $key; 455 } 456 457 /** 458 * turn debug on, if having problems, outputs the built mysql query and result set of the query, kills php 459 * 460 * @access public 461 * @param bool $debug, default = true 462 * @param bool $concat, default = true 463 * @return $this for chainability 464 */ 465 public function debug($debug = true, $concat = true) 466 { 467 $this->debug = $debug; 468 $this->concat = $concat; 469 return $this; 470 } 471 472 /** 473 * permission check on view 474 * 475 * @access public 476 * @return bool view 477 */ 478 public function canView() 479 { 480 if ($this->permissionsChecks == false) { 481 return true; 482 } 483 484 return Perms::get([ 'type' => 'tracker', 'object' => $this->trackerId() ])->view; 485 } 486 487 /** 488 * permission check on edit 489 * 490 * @access public 491 * @return bool edit 492 */ 493 public function canEdit() 494 { 495 if ($this->permissionsChecks == false) { 496 return true; 497 } 498 499 return Perms::get([ 'type' => 'tracker', 'object' => $this->trackerId() ])->edit; 500 } 501 502 /** 503 * permission check on delete 504 * 505 * @access public 506 * @return bool delete 507 */ 508 public function canDelete() 509 { 510 if ($this->permissionsChecks == false) { 511 return true; 512 } 513 514 return Perms::get([ 'type' => 'tracker', 'object' => $this->trackerId() ])->delete; 515 } 516 517 /** 518 * Setup temporary table for joining trackers together 519 * 520 * @access public 521 * @param mixed $tracker, id or tracker name if $this->byName() called 522 */ 523 function __construct($tracker = '') 524 { 525 global $tikilib; 526 $this->tracker = $tracker; 527 528 $tikilib->query( 529 "DROP TABLE IF EXISTS temp_tracker_field_options;" 530 ); 531 $tikilib->query( 532 "CREATE TEMPORARY TABLE temp_tracker_field_options ( 533 trackerIdHere INT, 534 trackerIdThere INT, 535 fieldIdThere INT, 536 fieldIdHere INT, 537 displayFieldIdThere INT, 538 displayFieldIdHere INT, 539 linkToItems INT, 540 type VARCHAR(1), 541 options VARCHAR(50) 542 );" 543 ); 544 $tikilib->query( 545 "INSERT INTO temp_tracker_field_options 546 SELECT 547 tiki_tracker_fields.trackerId, 548 REPLACE(SUBSTRING( 549 SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 1), 550 LENGTH(SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 1 -1)) + 1 551 ), 552 ',', ''), 553 REPLACE(SUBSTRING( 554 SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 2), 555 LENGTH(SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 2 -1)) + 1 556 ), 557 ',', ''), 558 REPLACE(SUBSTRING( 559 SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 3), 560 LENGTH(SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 3 -1)) + 1 561 ), 562 ',', ''), 563 REPLACE(SUBSTRING( 564 SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 4), 565 LENGTH(SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 4 -1)) + 1 566 ), 567 ',', ''), 568 tiki_tracker_fields.fieldId, 569 REPLACE(SUBSTRING( 570 SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 5), 571 LENGTH(SUBSTRING_INDEX(tiki_tracker_fields.options, ',', 5 -1)) + 1 572 ), 573 ',', ''), 574 tiki_tracker_fields.type, 575 tiki_tracker_fields.options 576 FROM tiki_tracker_fields 577 WHERE tiki_tracker_fields.type = 'l';" 578 ); 579 $tikilib->query( 580 "SET group_concat_max_len = 4294967295;" 581 ); 582 583 /*For any fields that have multi items, we use php to parse those out, there shouldn't be too many 584 */ 585 586 foreach ($tikilib->fetchAll("SELECT * FROM temp_tracker_field_options WHERE options LIKE '%|%'") as $row) { 587 $option = explode(",", $row["options"]); 588 $displayFieldIdsThere = explode("|", $option["3"]); 589 foreach ($displayFieldIdsThere as $key => $displayFieldIdThere) { 590 if ($key > 0) { 591 $tikilib->query( 592 "INSERT INTO temp_tracker_field_options VALUES (?,?,?,?,?,?,?,?,?)", 593 [ 594 $row["trackerIdHere"], 595 $row["trackerIdThere"], 596 $row["fieldIdThere"], 597 $row["fieldIdHere"], 598 $displayFieldIdThere, 599 $row["displayFieldIdHere"], 600 $row["linkToItems"], 601 $row["type"], 602 $row["options"] 603 ] 604 ); 605 } 606 } 607 } 608 } 609 610 /** 611 * Adds the field names to the beginning of the array of tracker items 612 * 613 */ 614 static function prepend_field_header(&$trackerPrimary = [], $nameOrder = []) 615 { 616 global $tikilib; 617 $result = $tikilib->fetchAll("SELECT fieldId, trackerId, name FROM tiki_tracker_fields"); 618 619 $header = []; 620 621 foreach ($result as $row) { 622 $header[$row['fieldId']] = $row['name']; 623 } 624 625 $joinedTrackerHeader = []; 626 627 foreach ($trackerPrimary as $item) { 628 foreach ($item as $key => $field) { 629 $joinedTrackerHeader[$key] = $header[$key]; 630 } 631 } 632 633 if (! empty($nameOrder)) { 634 $sortedHeader = []; 635 $unsortedHeader = []; 636 foreach ($nameOrder as $name) { 637 foreach ($joinedTrackerHeader as $key => $field) { 638 if ($field == $name) { 639 $sortedHeader[$key] = $field; 640 } else { 641 $unsortedHeader[$key] = $field; 642 } 643 } 644 } 645 $joinedTrackerHeader = $sortedHeader + $unsortedHeader; 646 } 647 648 $joinedTrackerHeader = ["HEADER" => $joinedTrackerHeader]; 649 650 return $joinedTrackerHeader + $trackerPrimary; 651 } 652 653 /** 654 * Simple direction parsing from string to type 655 * 656 */ 657 private static function sort_direction($dir) 658 { 659 switch ($dir) { 660 case "asc": 661 $dir = SORT_ASC; 662 break; 663 664 case "desc": 665 $dir = SORT_DESC; 666 break; 667 668 case "regular": 669 $dir = SORT_REGULAR; 670 break; 671 672 case "numeric": 673 $dir = SORT_NUMERIC; 674 break; 675 676 case "string": 677 $dir = SORT_STRING; 678 break; 679 680 default: 681 $dir = SORT_ASC; 682 } 683 684 return $dir; 685 } 686 687 static function arfsort(&$array, $fieldList) 688 { 689 if (! is_array($fieldList)) { 690 $fieldList = explode('|', $fieldList); 691 $fieldList = [[$fieldList[0], self::sort_direction($fieldList[1])]]; 692 } else { 693 for ($i = 0, $count_fieldList = count($fieldList); $i < $count_fieldList; ++$i) { 694 $fieldList[$i] = explode('|', $fieldList[$i]); 695 $fieldList[$i] = [$fieldList[$i][0], self::sort_direction($fieldList[$i][1])]; 696 } 697 } 698 699 $GLOBALS['__ARFSORT_LIST__'] = $fieldList; 700 usort($array, 'arfsort_func'); 701 } 702 703 function arfsort_func($a, $b) 704 { 705 foreach ($GLOBALS['__ARFSORT_LIST__'] as $f) { 706 switch ($f[1]) { 707 case SORT_NUMERIC: 708 $strc = ((float)$b[$f[0]] > (float)$a[$f[0]] ? -1 : 1); 709 return $strc; 710 break; 711 712 default: 713 $strc = strcasecmp($b[$f[0]], $a[$f[0]]); 714 if ($strc != 0) { 715 return $strc * (! empty($f[1]) && $f[1] == SORT_DESC ? 1 : -1); 716 } 717 } 718 } 719 return 0; 720 } 721 722 private function concat_str($field) 723 { 724 if ($this->concat == false) { 725 return $field; 726 } else { 727 return "GROUP_CONCAT(" . $field . " SEPARATOR '" . $this->delimiter . "')"; 728 } 729 } 730 731 /** 732 * Get current tracker id 733 * 734 * @access public 735 * @return int $trackerId 736 */ 737 public function trackerId() 738 { 739 if ($this->byName == true) { 740 $trackerId = TikiLib::lib('trk')->get_tracker_by_name($this->tracker); 741 } else { 742 $trackerId = $this->tracker; 743 } 744 745 if (! empty($trackerId) && ! is_numeric($trackerId)) { 746 throw new Exception("Opps, looks like you need to call ->byName();"); 747 } 748 749 return $trackerId; 750 } 751 752 /** 753 * Query, where the mysql command is built and executed, filtered, and rendered 754 * Orders results in a way that is human understandable and can be manipulated easily 755 * The end result is a very simple array setup as follows: 756 * array( //tracker(s) 757 * array( //items 758 * [itemId] => array ( 759 * [fieldId or FieldName] => value, 760 * [fieldId or FieldName] => array( //items list 761 * [0] => '', 762 * [1] => '' 763 * ) 764 * ) 765 * ) 766 * ) 767 */ 768 function query() 769 { 770 $trklib = TikiLib::lib('trk'); 771 $tikilib = TikiLib::lib('tiki'); 772 $params = []; 773 $fields_safe = ""; 774 $status_safe = ""; 775 $isSearch = false; 776 777 $trackerId = $this->trackerId(); 778 779 if (empty($trackerId) || $this->canView() == false) {//if we can't find a tracker, then return 780 return []; 781 } 782 783 $trackerDefinition = Tracker_Definition::get($trackerId); 784 785 $trackerFieldDefinition = $trackerDefinition->getFieldsIdKeys(); 786 787 $params[] = $trackerId; 788 789 if (! empty($this->start) && empty($this->search)) { 790 $params[] = $this->start; 791 } 792 793 if (! empty($this->end) && empty($this->search)) { 794 $params[] = $this->end; 795 } 796 797 if (! empty($this->itemId) && empty($this->search)) { 798 $params[] = $this->itemId; 799 } 800 801 802 /*Get field ids from names*/ 803 if ($this->byName == true && ! empty($this->fields)) { 804 $fieldIds = []; 805 foreach ($this->fields as $field) { 806 $fieldIds[] = $tikilib->getOne( 807 "SELECT fieldId FROM tiki_tracker_fields" . 808 " LEFT JOIN tiki_trackers ON (tiki_trackers.trackerId = tiki_tracker_fields.trackerId)" . 809 " WHERE" . 810 " tiki_trackers.name = ? AND tiki_tracker_fields.name = ?", 811 [$this->tracker, $field] 812 ); 813 } 814 $this->fields = $fieldIds; 815 } 816 817 if (count($this->fields) > 0 && (count($this->equals) > 0 || count($this->search) > 0)) { 818 for ($i = 0, $count_fields = count($this->fields); $i < $count_fields; $i++) { 819 if (strlen($this->fields[$i]) > 0) { 820 if ($i > 0) { 821 switch ($this->filterType[$i]) { 822 case "or": 823 $fields_safe .= " OR "; 824 break; 825 case "and": 826 $fields_safe .= " OR "; //Even though this is OR, we do a check later to limit more values, so initially we may have more results than are given later on, simply because of how trackers are stored and how group_concat allows us to manipulate trackers 827 break; 828 case "like": 829 $fields_safe .= " AND "; 830 break; 831 } 832 } 833 834 $fields_safe .= " ( search_item_fields.fieldId = ? "; 835 $params[] = $this->fields[$i]; 836 837 if (isset($this->equals[$i])) { 838 $fields_safe .= " AND search_item_fields.value = ? "; 839 $params[] = $this->equals[$i]; 840 } 841 842 if (isset($this->search[$i]) && strlen($this->search[$i]) > 0 && $this->filterType[$i] == "like") { 843 $fields_safe .= " AND search_item_fields.value LIKE ? "; 844 $params[] = '%' . $this->search[$i] . '%'; 845 } 846 847 $fields_safe .= " ) "; 848 } 849 } 850 851 if (strlen($fields_safe) > 0) { 852 $fields_safe = " AND ( $fields_safe ) "; 853 $isSearch = true; 854 } 855 } 856 857 if (strlen($this->status) > 0) { 858 for ($i = 0, $strlen_status = strlen($this->status); $i < $strlen_status; $i++) { 859 if (strlen($this->status[$i]) > 0) { 860 $status_safe .= " tiki_tracker_items.status = ? "; 861 if ($i + 1 < strlen($this->status) && strlen($this->status) > 1) { 862 $status_safe .= " OR "; 863 } 864 $params[] = $this->status[$i]; 865 } 866 } 867 868 if (strlen($status_safe) > 0) { 869 $status_safe = " AND ( $status_safe ) "; 870 } 871 } 872 873 if (! empty($this->limit) && is_numeric($this->limit) == false) { 874 unset($this->limit); 875 } 876 877 if (isset($this->offset) && ! empty($this->offset) && is_numeric($this->offset) == false) { 878 unset($this->offset); 879 } 880 881 $dateUnit = ($this->lastModif ? 'lastModif' : 'created'); 882 883 $query = 884 "SELECT 885 tiki_tracker_items.status, 886 tiki_tracker_item_fields.itemId, 887 tiki_tracker_fields.trackerId, 888 " . $this->concat_str("tiki_tracker_fields.name") . " AS fieldNames, 889 " . $this->concat_str("tiki_tracker_item_fields.fieldId") . " AS fieldIds, 890 " . $this->concat_str("IFNULL(items_right.value, tiki_tracker_item_fields.value)") . " AS item_values 891 892 FROM tiki_tracker_item_fields " . ($isSearch == true ? " AS search_item_fields " : "") . " 893 894 " . ($isSearch == true ? " 895 LEFT JOIN tiki_tracker_item_fields ON 896 search_item_fields.itemId = tiki_tracker_item_fields.itemId 897 " : "" ) . " 898 LEFT JOIN tiki_tracker_fields ON 899 tiki_tracker_fields.fieldId = tiki_tracker_item_fields.fieldId 900 LEFT JOIN tiki_trackers ON 901 tiki_trackers.trackerId = tiki_tracker_fields.trackerId 902 LEFT JOIN tiki_tracker_items ON tiki_tracker_items.itemId = tiki_tracker_item_fields.itemId 903 904 905 906 LEFT JOIN temp_tracker_field_options items_left_display ON 907 items_left_display.displayFieldIdHere = tiki_tracker_item_fields.fieldId 908 909 LEFT JOIN tiki_tracker_item_fields items_left ON ( 910 items_left.fieldId = items_left_display.fieldIdHere AND 911 items_left.itemId = tiki_tracker_item_fields.itemId 912 ) 913 914 LEFT JOIN tiki_tracker_item_fields items_middle ON ( 915 items_middle.value = items_left.value AND 916 items_left_display.fieldIdThere = items_middle.fieldId 917 ) 918 919 LEFT JOIN tiki_tracker_item_fields items_right ON ( 920 items_right.itemId = items_middle.itemId AND 921 items_right.fieldId = items_left_display.displayFieldIdThere 922 ) 923 924 925 WHERE 926 tiki_trackers.trackerId = ? 927 928 " . (! empty($this->start) ? " AND tiki_tracker_items." . $dateUnit . " > ? " : "") . " 929 " . (! empty($this->end) ? " AND tiki_tracker_items." . $dateUnit . " < ? " : "") . " 930 " . (! empty($this->itemId) ? " AND tiki_tracker_item_fields.itemId = ? " : "") . " 931 " . (! empty($fields_safe) ? $fields_safe : "") . " 932 " . (! empty($status_safe) ? $status_safe : "") . " 933 934 GROUP BY 935 tiki_tracker_item_fields.itemId 936 " . ($isSearch == true ? ", search_item_fields.fieldId, search_item_fields.itemId " : "" ) . " 937 ORDER BY 938 tiki_tracker_items." . $dateUnit . " " . ($this->desc == true ? 'DESC' : 'ASC') . " 939 " . (! empty($this->limit) ? " LIMIT " . $this->limit : "") . " 940 " . (! empty($this->offset) ? " OFFSET " . $this->offset : ""); 941 942 if ($this->debug == true) { 943 $result = [$query, $params]; 944 print_r($result); 945 print_r($tikilib->fetchAll($query, $params)); 946 die; 947 } else { 948 $result = $tikilib->fetchAll($query, $params); 949 } 950 951 $newResult = []; 952 $neededMatches = count($this->fields); 953 foreach ($this->fields as $i => $field) { 954 if ($this->filterType[$i] != 'and') { 955 $neededMatches--; 956 } 957 } 958 959 foreach ($result as $key => $row) { 960 if (isset($newResult[$row['itemId']])) { 961 continue; 962 } 963 964 $newRow = []; 965 $fieldNames = explode($this->delimiter, $row['fieldNames']); 966 $fieldIds = explode($this->delimiter, $row['fieldIds']); 967 $itemValues = explode($this->delimiter, $row['item_values']); 968 969 $matchCount = 0; 970 foreach ($fieldIds as $key => $fieldId) { 971 $field = ($this->byName == true ? $fieldNames[$key] : $fieldId); 972 $value = ''; 973 974 //This script attempts to narrow the results further by using an "AND" style checking of the returned result since it cannot be made at this time in mysql 975 if ($neededMatches > 0) { 976 $i = array_search($fieldId, $this->fields, true); 977 if ($i !== false) { 978 if ($this->equals[$i] == $itemValues[$key] && $this->filterType[$i] == 'and') { 979 $matchCount++; 980 } 981 } 982 } 983 //End "AND" style checking of results 984 985 if ($this->render == true) { 986 $value = $this->render_field_value($trackerFieldDefinition[$fieldId], $itemValues[$key]); 987 } else { 988 $value = $itemValues[$key]; 989 } 990 991 if (! isset($this->itemsRaw[$row['itemId']])) { 992 $this->itemsRaw[$row['itemId']] = []; 993 } 994 995 if (isset($newRow[$field])) { 996 if (is_array($newRow[$field]) == false) { 997 $newRow[$field] = [$newRow[$field]]; 998 $this->itemsRaw[$row['itemId']][$field] = [$itemValues[$key]]; //raw values 999 } 1000 1001 $newRow[$field][] = $value; 1002 $this->itemsRaw[$row['itemId']][$field][] = $itemValues[$key]; //raw values 1003 } else { 1004 $newRow[$field] = $value; 1005 $this->itemsRaw[$row['itemId']][$field] = $itemValues[$key]; //raw values 1006 } 1007 } 1008 if ($this->excludeDetails == false) { 1009 $newRow['status' . $trackerId] = $row['status']; 1010 $newRow['trackerId'] = $row['trackerId']; 1011 $newRow['itemId'] = $row['itemId']; 1012 } 1013 1014 if ($neededMatches == 0 || $neededMatches == $matchCount) { 1015 $newResult[$row['itemId']] = $newRow; 1016 } 1017 } 1018 unset($result); 1019 1020 $this->limitReached = (count($newResult) > $this->limit ? true : false); 1021 1022 return $newResult; 1023 } 1024 1025 /** 1026 * renders the field value 1027 * 1028 * @access private 1029 * @param array $fieldDefinition 1030 * @param string $value 1031 * @return mixed $value rendered field value 1032 */ 1033 private function render_field_value($fieldDefinition, $value) 1034 { 1035 $trklib = TikiLib::lib('trk'); 1036 $fieldDefinition['value'] = $value; 1037 1038 //if type is text, no need to render value 1039 switch ($fieldDefinition['type']) { 1040 case 't'://text 1041 case 'S'://static text 1042 return $value; 1043 } 1044 1045 return $trklib->field_render_value( 1046 [ 1047 'field' => $fieldDefinition, 1048 'process' => 'y', 1049 'list_mode' => 'y' 1050 ] 1051 ); 1052 } 1053 1054 /** 1055 * Removed fields from result 1056 * 1057 * @access private 1058 * @param array $fieldDefinition 1059 * @param string $value 1060 * @return mixed $value rendered field value 1061 */ 1062 static function filter_fields_from_tracker_query($tracker, $fieldIdsToRemove = [], $fieldIdsToShow = []) 1063 { 1064 if (empty($fieldIdsToShow) == false) { 1065 $newTracker = []; 1066 foreach ($tracker as $key => $item) { 1067 $newTracker[$key] = []; 1068 foreach ($fieldIdsToShow as $fieldIdToShow) { 1069 $newTracker[$key][$fieldIdToShow] = $tracker[$key][$fieldIdToShow]; 1070 } 1071 } 1072 1073 return $newTracker; 1074 } 1075 1076 if (empty($fieldIdsToRemove) == false) { 1077 foreach ($tracker as $key => $item) { 1078 foreach ($fieldIdsToRemove as $fieldIdToRemove) { 1079 unset($tracker[$key][$fieldIdToRemove]); 1080 } 1081 } 1082 } 1083 1084 return $tracker; 1085 } 1086 1087 /** 1088 * Joins tracker arrays together. 1089 * 1090 */ 1091 static function join_trackers($trackerLeft, $trackerRight, $fieldLeftId, $joinType) 1092 { 1093 $joinedTracker = []; 1094 1095 switch ($joinType) { 1096 case "outer": 1097 foreach ($trackerRight as $key => $itemRight) { 1098 $match = false; 1099 foreach ($trackerLeft as $itemLeft) { 1100 if ($key == $itemLeft[$fieldLeftId]) { 1101 $match = true; 1102 $joinedTracker[$key] = $itemLeft + $itemRight; 1103 } else { 1104 $joinedTracker[$key] = $itemLeft; 1105 } 1106 } 1107 1108 if ($match == false) { 1109 $joinedTracker[$key] = $itemRight; 1110 } 1111 } 1112 break; 1113 1114 default: 1115 foreach ($trackerLeft as $key => $itemLeft) { 1116 if (isset($trackerRight[$itemLeft[$fieldLeftId]]) == true) { 1117 $joinedTracker[$key] = $itemLeft + $trackerRight[$itemLeft[$fieldLeftId]]; 1118 } else { 1119 $joinedTracker[$key] = $itemLeft; 1120 } 1121 } 1122 } 1123 1124 return $joinedTracker; 1125 } 1126 1127 1128 static function to_csv($array, $header = false, $col_sep = ",", $row_sep = "\n", $qut = '"', $fileName = 'file.csv') 1129 { 1130 1131 header("Content-type: application/csv"); 1132 header("Content-Disposition: attachment; filename=" . $fileName); 1133 header("Pragma: no-cache"); 1134 header("Expires: 0"); 1135 1136 if (! is_array($array)) { 1137 return false; 1138 } 1139 $output = ''; 1140 1141 //Header row. 1142 if ($header == true) { 1143 foreach ($array[0] as $key => $val) { 1144 //Escaping quotes. 1145 $key = str_replace($qut, "$qut$qut", $key); 1146 $output .= "$col_sep$qut$key$qut"; 1147 } 1148 $output = substr($output, 1) . "\n"; 1149 } 1150 1151 $cellKeys = []; 1152 $cellKeysSet = false; 1153 foreach ($array as $key => $val) { 1154 $tmp = ''; 1155 1156 if ($cellKeysSet == false) { 1157 foreach ($val as $cell_key => $cell_val) { 1158 $cellKeys[] = $cell_key; 1159 } 1160 $cellKeysSet = true; 1161 } 1162 1163 foreach ($cellKeys as $cellKey) { 1164 //Escaping quotes. 1165 if (is_array($val[$cellKey]) == true) { 1166 $val[$cellKey] = implode(" ", $val[$cellKey]); 1167 } 1168 1169 $cell_val = str_replace("\n", " ", $val[$cellKey]); 1170 $cell_val = str_replace($qut, "$qut$qut", $cell_val); 1171 $tmp .= "$col_sep$qut$cell_val$qut"; 1172 } 1173 1174 $output .= substr($tmp, 1) . $row_sep; 1175 } 1176 1177 return $output; 1178 } 1179 1180 /** 1181 * Programmatic and simplified way of replacing or updating a tracker item, meant for api ease and accessibility 1182 * Does not check permissions 1183 * 1184 * @param array $data example array(fieldId=>'value', fieldId=>'value') or array('fieldName'=>'value', 'fieldName'=>'value') 1185 * @return int $itemId 1186 */ 1187 public function replaceItem($data = []) 1188 { 1189 $itemData = []; 1190 1191 $fields = TikiLib::lib("trk")->list_tracker_fields($this->trackerId()); 1192 for ($i = 0, $fieldCount = count($fields['data']); $i < $fieldCount; $i++) { 1193 if ($this->byName == true) { 1194 $fields['data'][$i]['value'] = $data[$fields['data'][$i]['name']]; 1195 } else { 1196 $fields['data'][$i]['value'] = $data[$fields['data'][$i]['fieldId']]; 1197 } 1198 } 1199 1200 $itemId = TikiLib::lib("trk")->replace_item($this->trackerId(), $this->itemId, $fields); 1201 1202 return $itemId; 1203 } 1204 1205 /** 1206 * Get inputs for tracker item, useful for building interface for interacting with trackers 1207 * 1208 * @param int $itemId, 0 for new item 1209 * @param bool $includeJs injects header js for item into field value 1210 * @return array $fields array of fields just like that found in query, but the value of each field being the input 1211 */ 1212 private function getInputsForItem($itemId = 0, $includeJs = true) 1213 { 1214 $headerlib = TikiLib::lib("header"); 1215 $itemId = (int)$itemId; 1216 1217 if ($includeJs == true) { 1218 $headerlibClone = clone $headerlib; 1219 } 1220 1221 $trackerId = $this->trackerId(); 1222 if ($trackerId < 1) { 1223 return []; 1224 } 1225 1226 $trackerDefinition = Tracker_Definition::get($trackerId); 1227 1228 $fields = []; 1229 1230 $fieldFactory = new Tracker_Field_Factory($trackerDefinition); 1231 $itemData = TikiLib::lib("trk")->get_tracker_item($itemId); 1232 1233 foreach ($trackerDefinition->getFields() as $field) { 1234 $fieldKey = ($this->byName == true ? $field['name'] : $field['fieldId']); 1235 1236 if ($includeJs == true) { 1237 $headerlib->clear_js(); 1238 } 1239 1240 $field['ins_id'] = "ins_" . $field['fieldId']; 1241 1242 if ($itemId == 0 && isset($this->inputDefaults)) { 1243 $field['value'] = $this->inputDefaults[$fieldKey]; 1244 } 1245 1246 $fieldHandler = $fieldFactory->getHandler($field, $itemData); 1247 1248 $fieldInput = $fieldHandler->renderInput(); 1249 1250 if ($includeJs == true) { 1251 $fieldInput = $fieldInput . $headerlib->output_js(); 1252 } 1253 1254 $fields[$fieldKey] = $fieldInput; 1255 } 1256 1257 if ($includeJs == true) { //restore the header to the way it was originally 1258 $headerlib = $headerlibClone; 1259 } 1260 1261 return $fields; 1262 } 1263 1264 /** 1265 * Set input defaults, useful when inserting a new item and you want to set the default values 1266 * 1267 * @param array $defaults, array of defaults, array(array(fieldKey=>defaultValue),array(fieldKey=>defaultValue)) 1268 * @return $this for chainability 1269 */ 1270 public function inputDefaults($defaults = []) 1271 { 1272 $this->inputDefaults = $defaults; 1273 return $this; 1274 } 1275 1276 /** 1277 * A set of tracker items with inputs 1278 * 1279 * @param bool $includeJs, default = false 1280 * @return $this for chainability 1281 */ 1282 public function queryInputs($includeJs = false) 1283 { 1284 if ($this->canEdit() == false) { 1285 return []; 1286 } 1287 1288 $query = $this->query(); 1289 1290 $items = []; 1291 foreach ($query as $itemId => $item) { 1292 $items[] = $this->getInputsForItem($itemId, $includeJs); 1293 } 1294 1295 return $items; 1296 } 1297 1298 /** 1299 * A single tracker item with inputs 1300 * 1301 * @param bool $includeJs, default = false 1302 * @return $this for chainability 1303 */ 1304 public function queryInput($includeJs = false) 1305 { 1306 return $this->getInputsForItem($this->itemId, $includeJs); 1307 } 1308 1309 /** 1310 * Delete a tracker item 1311 * 1312 * @param bool $bulkMode, default = false 1313 * @return $this for chainability 1314 */ 1315 public function delete($bulkMode = false) 1316 { 1317 $trklib = TikiLib::lib('trk'); 1318 1319 if ($this->canDelete()) { 1320 $results = $this->query(); 1321 foreach ($results as $itemId => $result) { 1322 $trklib->remove_tracker_item($itemId, $bulkMode); 1323 } 1324 } 1325 } 1326} 1327