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// Tikiwiki Sheet Library {{{1
9
10require_once("grid.php");
11
12if (strpos($_SERVER["SCRIPT_NAME"], basename(__FILE__)) !== false) {
13	header("location: index.php");
14	exit;
15}
16
17class SheetLib extends TikiLib
18{
19	private $setup_jQuery_sheet_files;
20
21	function get_sheet_info($sheetId) // {{{2
22	{
23		$result = $this->query("SELECT * FROM `tiki_sheets` WHERE `sheetId` = ?", [ $sheetId ]);
24		$result = $result->fetchRow();
25
26		if (! empty($result)) {
27			$result['tiki_p_edit_sheet'] = $this->user_can_edit($sheetId);
28			$ids = $this->get_related_sheet_ids($sheetId, true);
29			$lastId = end($ids);
30			$result['parentSheetId'] = $lastId;
31			$result['childSheetIds'] = $this->get_related_sheet_ids($sheetId);
32			$result['childTrackerIds'] = $this->get_related_tracker_ids($sheetId);
33			$result['childFileIds'] = $this->get_related_file_ids($sheetId);
34			$result['created'] = $this->get_sheet_created($result['sheetId']);
35			$result['lastModif'] = $this->get_lastModif($result['sheetId']);
36			if (! $result['lastModif']) {
37				if ($this->get_sheet_lastModif($result['sheetId'])) {
38					$result['lastModif'] = $this->get_sheet_lastModif($result['sheetId']);
39				} else {
40					$result['lastModif'] = $result['created'];
41				}
42			}
43
44			return $result;
45		}
46	}
47
48	function get_sheet_layout($sheetId) // {{{2
49	{
50		$result = $this->query("SELECT `className`, `headerRow`, `footerRow`, `parseValues`, `metadata` FROM `tiki_sheet_layout` WHERE `sheetId` = ? AND `end` IS NULL", [ $sheetId ]);
51
52		return $result->fetchRow();
53	}
54
55	/**
56	 * Get sheet values
57	 *
58	 * @param int $sheetId
59	 * @return array
60	 */
61	public function getSheetValue($sheetId) // {{{2
62	{
63		$result = $this->fetchAll("SELECT `sheetId`, `begin`, `end`, `rowIndex`, `columnIndex`, `value`, `calculation`, `width`, `height`, `format`, `user`, `style`, `class`, `clonedSheetId` FROM `tiki_sheet_values` WHERE `sheetId` = ?", [ $sheetId ]);
64
65		return $result;
66	}
67
68	//general relationships management
69	function add_relate($type, $sheetId, $childId)
70	{
71		$relationlib = TikiLib::lib('relation');
72		$relationlib->add_relation("tiki.sheet." . $type, "sheetId", $sheetId, $type . "Id", $childId);
73	}
74
75	function remove_relate($type, $sheetId, $childId)
76	{
77		$relationlib = TikiLib::lib('relation');
78		foreach ($relationlib->get_relations_from("sheetId", $sheetId, "tiki.sheet." . $type) as $result) {
79			if ($result['itemId'] == $childId) {
80				$relationlib->remove_relation($result['relationId']);
81			}
82		}
83	}
84
85	function get_relate_all($type, $sheetId, $inverted = false)
86	{
87		$relationlib = TikiLib::lib('relation');
88		$entityIds = [];
89		if ($inverted == true) {
90			foreach ($relationlib->get_relations_to("sheetId", $sheetId, "tiki.sheet." . $type) as $result) {
91				$entityIds[] = $result['itemId'];
92			}
93		} else {
94			foreach ($relationlib->get_relations_from("sheetId", $sheetId, "tiki.sheet." . $type) as $result) {
95				$entityIds[] = $result['itemId'];
96			}
97		}
98		return $entityIds;
99	}
100
101	function remove_relate_all($type, $sheetId)
102	{
103		foreach ($this->get_relate_all($type, $sheetId) as $entityId) {
104			$this->remove_related_tracker($sheetId, $entityId);
105		}
106	}
107
108	function update_relate($type, $sheetId, $entityIds)
109	{
110		$this->remove_relate_all($type, $sheetId);
111
112		foreach ($entityIds as $entityId) {
113			$this->add_relate($type, $sheetId, $entityId);
114		}
115	}
116
117	//file relationships
118	function add_related_file($sheetId, $fileId)
119	{
120		$this->add_relate("file", $sheetId, $fileId);
121	}
122
123	function remove_related_file($sheetId, $fileId)
124	{
125		$this->remove_relate("file", $sheetId, $fileId);
126	}
127
128	function remove_related_files($sheetId)
129	{
130		$this->remove_relate_all("file", $sheetId);
131	}
132
133	function get_related_file_ids($sheetId)
134	{
135		return $this->get_relate_all("file", $sheetId);
136	}
137
138	function update_related_files($sheetId, $fileId)
139	{
140		$this->update_relate("file", $sheetId, $fileId);
141	}
142
143	//tracker relationships
144	function add_related_tracker($sheetId, $trackerId)
145	{
146		$this->add_relate("tracker", $sheetId, $trackerId);
147	}
148
149	function remove_related_tracker($sheetId, $trackerId)
150	{
151		$this->remove_relate("tracker", $sheetId, $trackerId);
152	}
153
154	function remove_related_trackers($sheetId)
155	{
156		$this->remove_relate_all("tracker", $sheetId);
157	}
158
159	function get_related_tracker_ids($sheetId)
160	{
161		return $this->get_relate_all("tracker", $sheetId);
162	}
163
164	function update_related_trackers($sheetId, $trackerIds)
165	{
166		$this->update_relate("tracker", $sheetId, $trackerIds);
167	}
168
169	//sheet relationships
170	function add_related_sheet($sheetId, $childSheetId)
171	{
172		$this->remove_related_sheet($sheetId, $childSheetId);
173
174		$this->add_relate("sheet", $sheetId, $childSheetId);
175	}
176
177	function remove_related_sheets($sheetId)
178	{
179		$this->query(" UPDATE `tiki_sheets` SET `parentSheetId` = 0 WHERE `parentSheetId` = ? ", [ $sheetId ]);
180		$this->remove_relate_all("sheet", $sheetId);
181	}
182
183	function remove_related_sheet($childSheetId)
184	{
185		$this->query(" UPDATE `tiki_sheets` SET `parentSheetId` = 0 WHERE `sheetId` = ? ", [ $childSheetId ]);
186		$sheetIds = $this->get_related_sheet_ids($childSheetId, true);
187		$sheetId = end($sheetIds);
188		$this->remove_relate("sheet", $sheetId, $childSheetId);
189	}
190
191	function update_related_sheets($sheetId, $childSheetIds)
192	{
193		foreach ($childSheetIds as $childSheetId) {
194			$this->remove_related_sheet($sheetId, $childSheetId);
195		}
196
197		$this->update_relate("sheet", $sheetId, $childSheetIds);
198	}
199
200	function get_related_sheet_ids($sheetId, $getParent = false) // {{{2
201	{
202		$sheetIds = [];
203		foreach ($this->fetchAll("SELECT `sheetId` FROM `tiki_sheets` WHERE `parentSheetId` = ?", [ $sheetId ]) as $result) {
204			$sheetIds[] = $result['sheetId'];
205		}
206
207		$sheetIds = array_merge($this->get_relate_all("sheet", $sheetId, $getParent), $sheetIds);
208
209		foreach ($sheetIds as $childSheetId) {
210			$sheetIds = array_merge($this->get_relate_all("sheet", $childSheetId, $getParent), $sheetIds);
211		}
212
213		return $sheetIds;
214	}
215
216	function list_sheets($offset = 0, $maxRecord = -1, $sort_mode = 'title_desc', $find = '') // {{{2
217	{
218		global $user;
219		$userlib = TikiLib::lib('user');
220		$tikilib = TikiLib::lib('tiki');
221		switch ($sort_mode) {
222			case "author_asc":
223				$sort = "`author` ASC";
224				break;
225			case "author_desc":
226				$sort = "`author` DESC";
227				break;
228			case "description_asc":
229				$sort = "`description` ASC";
230				break;
231			case "description_desc":
232				$sort = "`description` DESC";
233				break;
234			case "title_asc":
235				$sort = "`title` ASC";
236				break;
237			case "title_desc":
238				$sort = "`title` DESC";
239				break;
240			case "sheetId_asc":
241				$sort = "`sheetId` ASC";
242				break;
243			case "sheetId_desc":
244				$sort = "`sheetId` DESC";
245				break;
246			default:
247				$sort = "`title` ASC";
248				break;
249		}
250		$bindvars = [];
251		$mid = '';
252		if (! empty($find)) {
253			$bindvars[] = "%$find%";
254			if (empty($mid)) {
255				$mid = ' WHERE ';
256			}
257			$mid .= ' `title` like ? ';
258		}
259
260		$result = $this->fetchAll("SELECT sheetId FROM `tiki_sheets`  $mid ORDER BY $sort", $bindvars, $maxRecord, $offset);
261
262		$sheets = [];
263		foreach ($result as $key => $sheet) {
264			$children = [];
265
266			foreach ($this->get_related_sheet_ids($sheet['sheetId']) as $childSheetId) {
267				$children[$childSheetId] = $this->get_sheet_info($childSheetId);
268			}
269
270			$sheet = $this->get_sheet_info($sheet['sheetId']);
271
272			$sheet['children'] = $children;
273
274			if ($this->user_can_view($sheet['sheetId'])) {
275				$sheets[$sheet['sheetId']] = $sheet;
276			}
277		}
278		//print_r($sheets);
279		$results = [];
280
281		$results['data'] = $sheets;
282
283		foreach ($results['data'] as $key => $sheet) {
284			foreach ($sheet['children'] as $key => $childSheetId) {
285				if (! empty($results['data'][$key])) {
286					unset($results['data'][$key]);
287				}
288			}
289		}
290
291		//print_r($results);
292		$results['cant'] = $this->getOne("SELECT COUNT(*) FROM `tiki_sheets` $mid", $bindvars);
293
294		return $results;
295	}
296
297	function get_created($sheetId)
298	{
299		return $this->getOne("
300				SELECT begin
301				FROM tiki_sheet_values
302				WHERE sheetId = ?
303				ORDER BY begin ASC
304			", [ $sheetId ]);
305	}
306
307	function get_sheet_created($sheetId)
308	{
309		return $this->getOne("
310				SELECT begin
311				FROM tiki_sheet_layout
312				WHERE sheetId = ?
313				ORDER BY begin ASC
314			", [ $sheetId ]);
315	}
316
317	function get_lastModif($sheetId)
318	{
319		return $this->getOne("
320				SELECT begin
321				FROM tiki_sheet_values
322				WHERE
323					sheetId = ?
324				ORDER BY end DESC
325			", [ $sheetId ]);
326	}
327
328	function get_sheet_lastModif($sheetId)
329	{
330		return $this->getOne("
331				SELECT end
332				FROM tiki_sheet_layout
333				WHERE
334					sheetId = ?
335				ORDER BY end DESC
336			", [ $sheetId ]);
337	}
338
339	function remove_sheet($sheetId) // {{{2
340	{
341		global $prefs;
342		$this->query("DELETE FROM `tiki_sheets` WHERE `sheetId` = ?", [ $sheetId ]);
343		$this->query("DELETE FROM `tiki_sheet_values` WHERE `sheetId` = ?", [ $sheetId ]);
344		$this->query("DELETE FROM `tiki_sheet_layout` WHERE `sheetId` = ?", [ $sheetId ]);
345
346		$this->remove_related_sheet($sheetId);
347
348		if ($prefs['feature_actionlog'] == 'y') {
349			$logslib = TikiLib::lib('logs');
350			$logslib->add_action('Removed', $sheetId, 'sheet');
351		}
352	}
353
354	function replace_sheet($sheetId, $title, $description, $author, $parentSheetId = 0, $layout = []) // {{{2
355	{
356		global $prefs;
357
358		if ($sheetId == 0) {
359			$this->query("INSERT INTO `tiki_sheets` ( `title`, `description`, `author` ) VALUES( ?, ?, ? )", [ $title, $description, $author ]);
360
361			$sheetId = $this->getOne("SELECT MAX(`sheetId`) FROM `tiki_sheets` WHERE `author` = ?", [ $author ]);
362			if ($prefs['feature_actionlog'] == 'y') {
363				$logslib = TikiLib::lib('logs');
364				$query = 'select `sheetId` from `tiki_sheets` where `title`=? and `description`= ? and `author`=?';
365				$id = $this->getOne($query, [$title, $description, $author ]);
366				$logslib->add_action('Created', $id, 'sheet');
367			}
368		} else {
369			$this->query("UPDATE `tiki_sheets` SET `title` = ?, `description` = ?, `author` = ? WHERE `sheetId` = ?", [ $title, $description, $author, (int) $sheetId ]);
370
371			$this->query("UPDATE `tiki_sheet_layout` SET `end` = ? WHERE `sheetId` = ?", [time(), $sheetId]);
372		}
373
374		$layoutDefault = [
375			"sheetId" => $sheetId,
376			"begin" => time(),
377			"headerRow" => 1,
378			"footerRow" => 1,
379			"className" => '',
380			"parseValues" => 'n',
381			"clonedSheetId" => 0,
382			"metadata" => ''
383		];
384
385		foreach ($layoutDefault as $key => $value) {
386			if (empty($layout[$key])) {
387				$layout[$key] = $layoutDefault[$key];
388			}
389		}
390
391		$this->query("INSERT INTO `tiki_sheet_layout` (`sheetId`, `begin`, `headerRow`, `footerRow`, `className`, `parseValues`, `clonedSheetId`, `metadata`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", [
392			$sheetId,
393			$layout["begin"],
394			$layout["headerRow"],
395			$layout["footerRow"],
396			$layout["className"],
397			$layout["parseValues"],
398			$layout["clonedSheetId"],
399			$layout["metadata"]
400		]);
401
402		$this->add_related_sheet($parentSheetId, $sheetId);
403
404		return $sheetId;
405	}
406
407	function set_sheet_title($sheetId, $title)
408	{
409		if ($sheetId) {
410			$this->query("UPDATE `tiki_sheets` SET `title` = ? WHERE `sheetId` = ?", [ $title, $sheetId ]);
411		}
412	}
413
414	function setup_jquery_sheet()
415	{
416		$headerlib = TikiLib::lib('header');
417		if (! $this->setup_jQuery_sheet_files) {
418			$headerlib
419				//core
420				->add_cssfile('vendor_bundled/vendor/jquery/jquery-sheet/jquery.sheet.css')
421				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/jquery.sheet.js')
422
423				//parsers
424				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/parser/formula/formula.js')
425				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/parser/tsv/tsv.js')
426
427				//tiki integration
428				->add_jsfile('lib/sheet/grid.js')
429
430				// plugins
431				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/plugins/jquery.sheet.dts.js')
432				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/plugins/jquery.sheet.advancedfn.js')
433				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/plugins/jquery.sheet.financefn.js')
434
435				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/plugins/globalize.js')
436				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/plugins/globalize.cultures.js')
437
438				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/plugins/jquery.nearest.min.js', true)
439				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/plugins/raphael-min.js', true)
440				->add_jsfile('vendor_bundled/vendor/jquery/jquery-sheet/plugins/g.raphael-min.js', true);
441
442			$this->setup_jQuery_sheet_files = true;
443		}
444	}
445
446	function sheet_history($sheetId)
447	{
448		return $this->fetchAll("
449			SELECT DISTINCT
450				`tiki_sheet_values`.`begin` as stamp,
451				`tiki_sheet_values`.`user`,
452				DATE_FORMAT(FROM_UNIXTIME(`tiki_sheet_values`.`begin`), '%M %D %Y %h:%i:%s') as prettystamp
453			FROM `tiki_sheet_values`
454			INNER JOIN `tiki_sheets` ON `tiki_sheets`.`sheetId` = `tiki_sheet_values`.`sheetId`
455			WHERE `tiki_sheets`.`sheetId` = ? OR `tiki_sheets`.`parentSheetId` = ?
456			ORDER BY begin DESC", [ $sheetId, $sheetId ]);
457	}
458
459	function rollback_sheet($id, $readdate = null)
460	{
461		global $user, $prefs;
462
463		if ($readdate) {
464			$now = (int)time();
465
466			 $this->query("
467				 UPDATE `tiki_sheet_values`
468				 SET `end` = ?
469				 WHERE
470				 	`sheetId` = ? AND
471				 	`end` IS NULL
472			 ", [ $now, $id ]);
473
474			 $this->query("
475				 INSERT INTO `tiki_sheet_values` (`sheetId`, `begin`, `rowIndex`, `columnIndex`, `value`, `calculation`, `width`, `height`, `format`, `user`, `style`, `class`, `clonedSheetId`)
476				 SELECT `sheetId`, ?, `rowIndex`, `columnIndex`, `value`, `calculation`, `width`, `height`, `format`, `user`, `style`, `class`, `clonedSheetId`
477				 FROM `tiki_sheet_values`
478				 WHERE
479				 	`sheetId` = ? AND
480				    ? >= `begin` AND
481				    `end` > ?
482			", [ $now, $id, $readdate, $readdate ]);
483		}
484
485		if ($prefs['feature_actionlog'] == 'y') {
486			$logslib = TikiLib::lib('logs');
487			$logslib->add_action('Spreadsheet-Rollback', $id, 'sheet');
488		}
489
490		$children = $this->fetchAll("SELECT `sheetId` FROM `tiki_sheets` WHERE `parentSheetId` = ?", [$id]);
491		foreach ($children as $child) {
492			$this->rollback_sheet($child['sheetId'], $readdate);
493		}
494
495		return $id;
496	}
497
498	function clone_sheet($sheetId, $readdate = null, $parentSheetId = 0)
499	{
500		global $user, $prefs;
501
502		if (! isset($readdate)) {
503			$readdate = time();
504		}
505
506		$readdate = (int)$readdate;
507		$parentSheetId = (int)$parentSheetId;
508
509		//clone the parent sheet & get it's id
510		$this->query("
511			INSERT INTO `tiki_sheets` (`title`, `description`, `author`, `parentSheetId`, `clonedSheetId`)
512			SELECT CONCAT('CLONED - ', `title`), `description`, ?, ?, `sheetid`
513			FROM `tiki_sheets`
514			WHERE `sheetid` = ?
515		", [ $user, $parentSheetId, $sheetId ]);
516
517		$newSheetId = $this->getOne("SELECT MAX(`sheetId`) FROM `tiki_sheets` WHERE `author` = ?", [ $user ]);
518		//clone the sheet layout
519		$this->query("
520			INSERT INTO `tiki_sheet_layout` (`sheetId`, `begin`, `end`, `headerRow`, `footerRow`, `className`, `parseValues`, `clonedSheetId`)
521			SELECT ?, `begin`, `end`, `headerRow`, `footerRow`, `className`, `parseValues`, `sheetId`
522			FROM `tiki_sheet_layout`
523			WHERE `sheetid` = ?
524		", [ $newSheetId, $sheetId ]);
525
526		//clone sheet's values
527		$this->query("
528	      INSERT INTO `tiki_sheet_values` (`sheetId`, `begin`, `end`, `rowIndex`, `columnIndex`, `value`, `calculation`, `width`, `height`, `format`, `user`, `style`, `class`, `clonedSheetId`)
529	      SELECT ?, `begin`, NULL, `rowIndex`, `columnIndex`, `value`, `calculation`, `width`, `height`, `format`, `user`, `style`, `class`, ?
530	      FROM `tiki_sheet_values`
531	    	WHERE
532	        `sheetId` = ? AND
533	        ? >= `begin` AND
534	        (
535	        	`end` IS NULL OR
536	        	`end` > ?
537	        )
538      ", [ $newSheetId, $sheetId, $sheetId, $readdate, $readdate ]);
539
540		//clone the children sheets if they exist
541		$result = $this->query("SELECT `sheetId` FROM `tiki_sheets` WHERE `parentSheetId` = ?", [ $sheetId ]);
542		while ($row = $result->fetchRow()) {
543			if ($row['sheetId']) {
544				$this->clone_sheet($row['sheetId'], $readdate, $newSheetId);
545			}
546		}
547
548		if ($prefs['feature_actionlog'] == 'y') {
549			$logslib = TikiLib::lib('logs');
550			$logslib->add_action('Cloning', $sheetId, 'sheet');
551			$logslib->add_action('Cloned', $newSheetId, 'sheet');
552		}
553
554		return $newSheetId;
555	}
556
557	function clone_layout($sheetId, $className, $headerRow, $footerRow, $parseValues = 'n') // {{{2
558	{
559		if ($row = $this->get_sheet_layout($sheetId)) {
560			if ($row[ 'className' ] == $className
561			 && $row[ 'headerRow' ] == $headerRow
562			 && $row[ 'footerRow' ] == $footerRow
563			 && $row[ 'parseValues' ] == $parseValues ) {
564				return true; // No changes have to be made
565			}
566		}
567
568		$headerRow = empty($headerRow) ? 0 : $headerRow;
569
570		$footerRow = empty($footerRow) ? 0 : $footerRow;
571
572		$stamp = time();
573
574		$this->query("UPDATE `tiki_sheet_layout` SET `end` = ? WHERE sheetId = ? AND `end` IS NULL", [ $stamp, $sheetId ]);
575		$this->query(
576			"INSERT INTO `tiki_sheet_layout` ( `sheetId`, `begin`, `className`, `headerRow`, `footerRow`, `parseValues` ) VALUES( ?, ?, ?, ?, ?, ? )",
577			[ $sheetId, $stamp, $className, (int)$headerRow, (int)$footerRow, $parseValues ]
578		);
579
580		return true;
581	}
582
583	function save_sheet($sheets, $sheetId, $layout = [])
584	{
585		global $user;
586
587		$sheets = json_decode($sheets);
588
589		$rc = '';
590
591		if (! empty($sheetId)) {
592			$grid = new TikiSheet();
593			if (is_array($sheets)) {
594				foreach ($sheets as $sheet) {
595					$handler = new TikiSheetHTMLTableHandler($sheet);
596					$res = $grid->import($handler);
597					// Save the changes
598					$rc .= strlen($rc) === 0 ? '' : ', ';
599
600					if ($sheet->id != $sheetId) {
601						$sheetIds[] = $sheet->id;
602					}
603
604					if ($res) {
605						if (! $sheet->id) {
606							if (! empty($sheet->title)) {
607								$title = $sheet->title;
608							} else {
609								$title = $info['title'] . ' subsheet';
610							}
611
612							$newId = $this->replace_sheet(0, $title, '', $user, $sheetId, $layout);
613							$rc .= tra('new') . " (sheetId=$newId) ";
614							$sheet->id = $newId;
615							$handler = new TikiSheetHTMLTableHandler($sheet);
616							$res = $grid->import($handler);
617						}
618						if ($sheetId && $res) {
619							$handler = new TikiSheetDatabaseHandler($sheet->id, null, json_encode($sheet->metadata));
620							$grid->export($handler);
621							$rc .= $grid->getColumnCount() . ' x ' . $grid->getRowCount() . ' ' . tra('sheet') . " (sheetId=" . $sheet->id . ")";
622						}
623						if (! empty($sheet->title)) {
624							$this->set_sheet_title($sheet->id, $sheet->title);
625						}
626					}
627				}
628			}
629		}
630		return ($res ? tra('Saved') . ': ' . $rc : tra('Save failed'));
631	}
632
633	/** get_attr_from_css_string {{{2
634	 * Grabs a css setting from a string
635	 * @param $style A simple css style string used with an html dom object
636	 * @param $attr The name of the css attribute you'd like to extract from $style
637	 */
638	function get_attr_from_css_string($style, $attr, $default)
639	{
640		$style = strtolower($style);
641		$style = str_replace(' ', '', $style);
642
643		$attr = strtolower($attr);
644
645		$cssAttrs = explode(';', $style);
646		foreach ($cssAttrs as &$v) {
647			$v = explode(':', $v);
648		}
649
650		$key = $this->array_searchRecursive($attr, $cssAttrs);
651		$result = '';
652		if ($key === false) {
653			$result = $default;
654		} else {
655			$result = $cssAttrs[$key[0]][$key[1] + 1];
656		}
657
658		return ($result != 'auto' ? $result : $default);
659	}
660
661	// array_search with recursive searching, optional partial matches and optional search by key
662	function array_searchRecursive($needle, $haystack, $strict = false, $path = [])
663	{
664		if (! is_array($haystack)) {
665			return false;
666		}
667
668		foreach ($haystack as $key => $val) {
669			if (is_array($val) && $subPath = $this->array_searchRecursive($needle, $val, $strict, $path)) {
670				$path = array_merge($path, [$key], $subPath);
671				return $path;
672			} elseif ((! $strict && $val == $needle) || ($strict && $val === $needle)) {
673				$path[] = $key;
674				return $path;
675			}
676		}
677		return false;
678	}
679
680	function diff_sheets_as_html($id, $dates = null)
681	{
682		global $prefs;
683
684		$count_longest = function ($array1, $array2) {
685			return max(count($array1), count($array2));
686		};
687
688		$join_with_sub_grids = function ($id, $date) {
689			global $prefs;
690
691			$handler = new TikiSheetDatabaseHandler($id, $date);
692			$handler->setReadDate($date);
693			$grid = new TikiSheet();
694			$grid->import($handler);
695
696			$childSheetIds = $this->get_related_sheet_ids($grid->id);
697			$i = 0;
698			$grids = [$grid];
699			foreach ($childSheetIds as $childSheetId) {
700				$handler = new TikiSheetDatabaseHandler($childSheetId, $date);
701				$handler->setReadDate($date);
702				$childSheet = new TikiSheet();
703				$childSheet->import($handler);
704
705				array_push($grids, $childSheet);
706				$i++;
707			}
708			return $grids;
709		};
710
711		$sanitize_for_diff = function ($val) {
712			$val = str_replace("<br/>", "<br>", $val);
713			$val = str_replace("<br />", "<br>", $val);
714			$val = str_replace("<br  />", "<br>", $val);
715			$val = str_replace("<BR/>", "<br>", $val);
716			$val = str_replace("<BR />", "<br>", $val);
717			$val = str_replace("<BR  />", "<br>", $val);
718
719			return explode("<br>", $val);
720		};
721
722		$diff_to_html = function ($changes) use ($count_longest) {
723			$result = ["", ""];
724			for ($i = 0; $i < $count_longest($changes->orig, $changes->final); $i++) {
725				$class = ["", ""];
726				$char = ["", ""];
727				$vals = [ trim($changes->orig[$i]), trim($changes->final[$i]) ];
728
729				if ($vals[0] && $vals[1]) {
730					if ($vals[0] != $vals[1]) {
731						$class[1] .= "diffadded";
732					}
733				} elseif ($vals[0]) {
734					$class[0] .= "diffadded";
735					$class[1] .= "diffdeleted";
736					$vals[1] = $vals[0];
737					$char[1] = "-";
738				} elseif ($vals[1]) {
739					$class[0] .= "diffdeleted";
740					$class[1] .= "diffadded";
741					$char[1] = "+";
742				}
743
744				if ($vals[0]) {
745					$result[0] .= "<td class='$class[0]'>" . $char[0] . $vals[0] . "</td>";
746				}
747				if ($vals[1]) {
748					$result[1] .= "<td class='$class[1]'>" . $char[1] . $vals[1] . "</td>";
749				}
750			}
751			return $result;
752		};
753
754		$grids1 = $join_with_sub_grids($id, $dates[0]);
755		$grids2 = $join_with_sub_grids($id, $dates[1]);
756
757		$result1 = '';
758		$result2 = '';
759
760		for ($i = 0; $i < $count_longest($grids1, $grids2); $i++) { //cycle through the sheets within a spreadsheet
761			$result1 .= "<table title='" . $grids1[$i]->name() . "'>";
762			$result2 .= "<table title='" . $grids2[$i]->name() . "'>";
763			for ($row = 0; $row < $count_longest($grids1[$i]->dataGrid, $grids2[$i]->dataGrid); $row++) { //cycle through rows
764				$result1 .= "<tr>";
765				$result2 .= "<tr>";
766				for ($col = 0; $col < $count_longest($grids1[$i]->dataGrid[$row], $grids2[$i]->dataGrid[$row]); $col++) { //cycle through columns
767					$diff = new Text_Diff($sanitize_for_diff(html_entity_decode($grids1[$i]->dataGrid[$row][$col])), $sanitize_for_diff(html_entity_decode($grids2[$i]->dataGrid[$row][$col])));
768					$changes = $diff->getDiff();
769
770					//I left this diff switch, but it really isn't being used as of now, in the future we may though.
771					switch (get_class($changes[0])) {
772						case 'Text_Diff_Op_copy':
773							$values = $diff_to_html($changes[0]);
774							break;
775						case 'Text_Diff_Op_change':
776							$values = $diff_to_html($changes[0]);
777							break;
778						case 'Text_Diff_Op_delete':
779							$values = $diff_to_html($changes[0]);
780							break;
781						case 'Text_Diff_Op_add':
782							$values = $diff_to_html($changes[0]);
783							break;
784						default:
785							$values = $diff_to_html($changes[0]);
786					}
787					$result1 .= (empty($values[0]) ? '<td></td>' : $values[0]);
788					$result2 .= (empty($values[1]) ? '<td></td>' : $values[1]);
789				}
790				$result1 .= "</tr>";
791				$result2 .= "</tr>";
792			}
793			$result1 .= "</table>";
794			$result2 .= "</table>";
795		}
796
797		return [$result1, $result2];
798	}
799
800	function user_can_view($id)
801	{
802		global $user;
803		$objectperms = Perms::get('sheet', $id);
804		return ( $objectperms->view_sheet || $objectperms->admin );
805	}
806
807	function user_can_edit($id)
808	{
809		global $user;
810		$objectperms = Perms::get('sheet', $id);
811		return ( $objectperms->edit_sheet || $objectperms->admin );
812	}
813} // }}}1
814