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