1<?php
2use dokuwiki\Form\Form;
3use dokuwiki\Form\TagOpenElement;
4use dokuwiki\Form\TagElement;
5
6/**
7 * Plugin timetrack
8 *
9 * @package dokuwiki\plugin\timetrack
10 * @author     peterfromearth <coder@peterfromearth.de>
11 */
12// must be run within Dokuwiki
13if(!defined('DOKU_INC')) die();
14
15require_once(DOKU_PLUGIN . 'syntax.php');
16require_once(DOKU_INC . 'inc/infoutils.php');
17
18/**
19 * This is the base class for all syntax classes, providing some general stuff
20*/
21class helper_plugin_timetrack extends DokuWiki_Plugin {
22
23	/**
24	 * @var helper_plugin_sqlite initialized via getDb()
25	 */
26	protected $db = null;
27
28	/**
29	 * Simple function to check if the database is ready to use
30	 *
31	 * @return bool
32	 */
33	public function ready() {
34		return (bool) $this->getDb();
35	}
36
37
38	/**
39	 * load the sqlite helper
40	 *
41	 * @return helper_plugin_sqlite
42	 */
43	public function getDb() {
44		if($this->db === null) {
45			$this->db = plugin_load('helper', 'sqlite');
46			if($this->db === null) {
47				msg('The timetrack plugin needs the sqlite plugin', -1);
48				return false;
49			}
50			if(!$this->db->init('timetrack', dirname(__FILE__) . '/db/')) {
51				$this->db = null;
52				return false;
53			}
54		}
55
56		return $this->db;
57	}
58
59	//////////////////////////////////////////////////////////////////////////
60	// MODIFY FUNCTIONS
61	//////////////////////////////////////////////////////////////////////////
62
63	public function getUserIdByName($user, $create = true) {
64		$sqlite = $this->getDb();
65		if(!$sqlite) return false;
66
67		$res = $sqlite->query("SELECT id FROM user WHERE user = ?", $user);
68		$db_user_id = (int) $sqlite->res2single($res);
69		$sqlite->res_close($res);
70
71		if(!$db_user_id && $create) {
72			$sqlite->query('INSERT OR IGNORE INTO user (user) VALUES (?)', $user);
73
74			$res = $sqlite->query("SELECT id FROM user WHERE user = ?", $user);
75			$db_user_id = (int) $sqlite->res2single($res);
76			$sqlite->res_close($res);
77		}
78
79		return $db_user_id;
80	}
81
82	/**
83	 *
84	 * @param string $page dokuwiki pageid
85	 * @param boolean $create
86	 * @return number
87	 */
88	public function getPageId($page, $create = true) {
89		$sqlite = $this->getDb();
90
91		$res = $sqlite->query("SELECT id FROM page WHERE page = ?", $page);
92		$page_id = (int) $sqlite->res2single($res);
93
94		if(!$page_id && $create) {
95			$sqlite->query('INSERT OR IGNORE INTO page(page) VALUES (?)',$page);
96		} else if(!$page_id){
97			return null;
98		} else {
99			return $page_id;
100		}
101
102		$res = $sqlite->query("SELECT id FROM page WHERE page = ?", $page);
103		$page_id = (int) $sqlite->res2single($res);
104
105		return $page_id;
106
107	}
108
109	/**
110	 *
111	 * @param string $pageid dokuwiki pageid
112	 * @param string $abbr id
113	 * @param string $name project name
114	 * @return boolean|integer projects db id
115	 */
116	public function updateProject($pageid, $abbr = '', $name = '', $from = 0, $to = 0) {
117		$sqlite = $this->getDb();
118
119		if(!$abbr) $abbr = noNS($pageid);
120		if(!$name) $name = noNS($pageid);
121
122		$page_id = $this->getPageId($pageid);
123
124		if(!$page_id) dbg(['no page id',$pageid,$abbr,$name]);
125		$res = $sqlite->query('INSERT OR IGNORE INTO project (page_id, abbr) VALUES (?,?)', $page_id, $abbr);
126
127		//fetch project_id
128		$res = $sqlite->query("SELECT id FROM project WHERE page_id = ? AND abbr = ?", $page_id, $abbr);
129		$project_id = (int) $sqlite->res2single($res);
130		$sqlite->res_close($res);
131
132		if(!$project_id) {
133			msg("timetrack plugin: failed saving project", -1);
134			return false;
135		}
136
137		$sqlite->query("UPDATE project SET name = ?, `from` = ?, `to` = ? WHERE id = ?", $name, $from, $to, $project_id);
138
139		$task_id = $this->updateTask($project_id,$abbr,$name);
140
141		return $project_id;
142	}
143
144
145
146
147	/**
148	 *
149	 * @param integer $project_id
150	 * @param string $abbr
151	 * @param string $name
152	 * @return boolean|integer task_id
153	 */
154	public function updateTask($project_id,$abbr,$name) {
155		$sqlite = $this->getDb();
156
157		$res = $sqlite->query('INSERT OR IGNORE INTO task (project_id,abbr,active) VALUES (?,?,1)',$project_id,$abbr);
158
159		$res = $sqlite->query("SELECT id FROM task WHERE project_id = ? AND abbr = ?", $project_id,$abbr);
160		$task_id = (int) $sqlite->res2single($res);
161		$sqlite->res_close($res);
162
163		if(!$task_id) {
164			msg("timetrack plugin: failed saving task", -1);
165			return false;
166		}
167
168		$sqlite->query("UPDATE task SET name = ?, active = 1 WHERE id = ?",	$name, $task_id);
169
170		return $task_id;
171
172	}
173
174	/**
175	 * set all tasks not in array to inactive
176	 *
177	 * @param array $task_abbrs items of abbrs
178	 * @return boolean
179	 */
180	public function setActiveTasks($project_id,$task_abbrs = array()) {
181		$sqlite = $this->getDb();
182
183		$task_abbrs = $sqlite->quote_and_join($task_abbrs);
184
185		$sqlite->query("UPDATE task SET active = 0
186				WHERE
187					project_id = ? AND
188					abbr NOT IN (".$task_abbrs.")",$project_id);
189
190	}
191
192	/**
193	 *
194	 * @param integer $user_time_id
195	 * @param integer $value
196	 * @return boolean
197	 */
198	public function updateUserTime($user_time_id, $value) {
199		$sqlite = $this->getDb();
200
201		$sqlite->query('UPDATE user_time SET value = ?, update_time = ? WHERE id = ?', $value, time(), $user_time_id);
202		return true;
203	}
204
205	/**
206	 *
207	 * @param string $user
208	 * @param integer $task_id
209	 * @param date $date
210	 * @param integer $value
211	 * @return boolean
212	 */
213	public function insertUserTime($user, $task_id, $date, $value){
214		$sqlite = $this->getDb();
215
216		$sqlite->query('INSERT INTO user_time (update_time,user_id,task_id, date, value) VALUES (?,?,?,?,?)',
217			time(),
218			$this->getUserIdByName($user),
219			$task_id,
220			$date,
221			$value);
222		return true;
223	}
224
225
226
227	//////////////////////////////////////////////////////////////////////////
228	// QUERY FUNCTIONS
229	//////////////////////////////////////////////////////////////////////////
230	/**
231	 *
232	 * @param string $pageid dokuwiki pageid
233	 * @param string $abbr id
234	 * @param string $name project name
235	 * @return boolean|integer projects db id
236	 */
237	public function getProject($pageid, $abbr = '') {
238		$sqlite = $this->getDb();
239
240		if(!$abbr) $abbr = noNS($pageid);
241		if(!$name) $name = noNS($pageid);
242
243		$page_id = $this->getPageId($pageid);
244
245		//fetch project_id
246		$res = $sqlite->query("SELECT id FROM project WHERE page_id = ? AND abbr = ?", $page_id, $abbr);
247		$project_id = (int) $sqlite->res2single($res);
248		$sqlite->res_close($res);
249
250		if(!$project_id) {
251			msg("timetrack plugin: failed loading project", -1);
252			return false;
253		}
254		return $project_id;
255	}
256
257	public function getPageByProjectId($project_id) {
258		$sqlite = $this->getDb();
259
260		$res = $sqlite->query("SELECT
261				pa.page
262				FROM page AS pa
263				JOIN project pr ON pa.id=pr.page_id
264				WHERE
265					pr.id = ?",
266				$project_id);
267		$data = $sqlite->res2single($res);
268
269		return $data;
270	}
271
272	public function getProjectTaskIdByPageid($pageid) {
273		$sqlite = $this->getDb();
274
275		$res = $sqlite->query("SELECT
276				pr.name project_name,
277				pr.abbr project_abbr,
278				t.name task_name,
279				t.abbr task_abbr,
280				t.id task_id
281				FROM page AS pa
282				JOIN project pr ON pa.id=pr.page_id
283				JOIN task t ON pr.id=t.project_id
284				WHERE
285					pa.page = ?", //TODO
286				$pageid);
287		$data = $sqlite->res2arr($res);
288
289		return $data;
290	}
291
292	public function getProjectIdsByPageId($pageid) {
293		$sqlite = $this->getDb();
294
295		$res = $sqlite->query("SELECT pr.id
296				FROM page AS pa
297				JOIN project pr on pa.id=pr.page_id
298				WHERE
299					pa.page = ?",
300				$pageid);
301		$data = $sqlite->res2arr($res);
302
303		$data = array_map(function($item){return $item['id'];},$data);
304
305		return $data;
306	}
307
308	public function getProjectDetails($project_id) {
309		$sqlite = $this->getDb();
310
311		$res = $sqlite->query("SELECT
312				*
313				FROM project AS pr
314				WHERE
315					pr.id = ?",
316				$project_id);
317		$data = $sqlite->res2row($res);
318
319		return $data;
320	}
321
322
323	public function getUserPageTaskData($user, $task_id, $daterange = null) {
324		$sqlite = $this->getDb();
325
326		if($daterange === null) $daterange = $this->getDateRangeByYearWeek();
327
328		$res = $sqlite->query("SELECT
329					ut.id,
330					ut.date,
331					ut.value
332				FROM
333					task t
334				JOIN
335					user_time ut on t.id=ut.task_id
336				WHERE
337					t.id = ? and
338					user_id = ? and
339					date between ? and ?",
340				$task_id,
341				$this->getUserIdByName($user),
342				$daterange['start'],
343				$daterange['end']);
344		$data = $sqlite->res2arr($res);
345
346		$return = array();
347		foreach($data as $entry) {
348			$return[$entry['date']] = $entry;
349		}
350		return $return;
351	}
352
353
354
355	public function isPageTracked($pageid) {
356		$sqlite = $this->getDb();
357		if(!$sqlite) return false;
358		$res = $sqlite->query('SELECT
359				pa.id
360				FROM page pa
361				JOIN project pr on pa.id=pr.page_id
362				JOIN task t on pr.id=t.project_id
363				WHERE pa.page = ? AND t.active = 1
364				GROUP BY pa.id',$pageid);
365		$db_id = (int) $sqlite->res2single($res);
366		$sqlite->res_close($res);
367
368		if($db_id) return true;
369
370		return false;
371	}
372
373	public function isUserInDb($user = null) {
374		$sqlite = $this->getDb();
375		if(!$sqlite) return false;
376		if($user === null) {
377			$user = $this->getCurrentUser();
378		}
379
380		$res = $sqlite->query("SELECT u.id
381				FROM user u
382				JOIN user_time ut on ut.user_id = u.id
383				WHERE u.user = ?", $user);
384		$db_user_id = (int) $sqlite->res2single($res);
385		$sqlite->res_close($res);
386		return $db_user_id ? true : false;
387	}
388
389	public function getUserData($user, $daterange = null) {
390		$sqlite = $this->getDb();
391
392		if($daterange === null) $daterange = $this->getDateRangeByYearWeek();
393
394		$res = $sqlite->query("SELECT
395				pr.name project_name,
396				pr.id project_id,
397				t.name task_name,
398				t.id task_id,
399				ut.date,
400				ut.value,
401				ut.id id
402				FROM page AS pa
403				JOIN project pr ON pa.id=pr.page_id
404				JOIN task t ON pr.id=t.project_id
405				JOIN user_time ut ON t.id=ut.task_id
406				WHERE
407					user_id = ? AND
408					date between ? AND ?
409				ORDER BY date",
410				$this->getUserIdByName($user),
411				$daterange['start'],
412				$daterange['end']);
413		$data = $sqlite->res2arr($res);
414
415		return $data;
416	}
417
418	public function getOverviewData($user, $daterange = null) {
419		$sqlite = $this->getDb();
420
421		if($daterange === null) $daterange = $this->getDateRangeByYearWeek();
422
423		$res = $sqlite->query("SELECT
424				ut.date,
425				sum(ut.value) value
426				FROM user_time AS ut
427				WHERE
428					user_id = ? AND
429					date between ? AND ?
430				GROUP BY date
431				ORDER BY date DESC",
432				$this->getUserIdByName($user),
433				$daterange['start'],
434				$daterange['end']);
435		$data = $sqlite->res2arr($res);
436
437		$return = array();
438		foreach($data as $entry) {
439			$return[$entry['date']] = $entry;
440		}
441		return $return;
442	}
443
444
445
446	public function getTaskDataById($task_id) {
447		$sqlite = $this->getDb();
448
449		$res = $sqlite->query("SELECT
450				pr.name project_name,
451				t.name task_name,
452				t.id task_id
453				FROM project AS pr
454					JOIN task t ON pr.id=t.prpject_id
455				WHERE
456					t.id = ?",
457				$page_sub_id);
458		$data = $sqlite->res2row($res);
459
460		return $data;
461	}
462
463	public function getHoursNotInTasksOnDate($user, $date, $task_ids) {
464		$sqlite = $this->getDb();
465
466		$res = $sqlite->query("SELECT
467				sum(ut.value)
468				FROM user_time AS ut
469				WHERE
470					user_id = ? AND
471					date = ? AND
472				 	ut.task_id NOT IN (".$sqlite->quote_and_join($task_ids).")",
473				$this->getUserIdByName($user),
474				$date);
475		$data = $sqlite->res2single($res);
476
477		return $data;
478	}
479
480
481
482	public function getUserDataRecent($user,$daterange) {
483		$data = $this->getUserData($user,$daterange);
484
485		$return = array();
486		foreach($data as $entry) {
487			$return[$entry['task_id']][$entry['date']] = $entry;
488		}
489		return $return;
490	}
491
492
493	public function getUserTimeData($user,$daterange,$project_ids = null) {
494		$sqlite = $this->getDb();
495
496		if($project_ids === null) {
497			$cond_project = '';
498		} else {
499			if(!is_array($project_ids)) {
500				$project_ids = array($project_ids);
501			}
502			$cond_project = " pr.id IN (".$sqlite->quote_and_join($project_ids).") AND ";
503
504		}
505
506		$res = $sqlite->query("SELECT
507				pr.id project_id,
508				pr.name project_name,
509				t.name task_name,
510				t.id task_id,
511				ut.value value,
512				ut.date date,
513				ut.id id
514				FROM page pa
515				JOIN project pr ON pa.id=pr.page_id
516				JOIN task t ON pr.id=t.project_id
517				JOIN user_time ut ON t.id=ut.task_id
518				WHERE
519					ut.user_id = ? AND
520					ut.date between ? AND ? AND
521					$cond_project
522					t.active = 1",
523				$this->getUserIdByName($user),
524				$daterange['start'],
525				$daterange['end']);
526		$data = $sqlite->res2arr($res);
527
528		$return = array();
529		foreach($data as $entry) {
530			$return[$entry['project_id']][$entry['task_id']][$entry['date']] = $entry;
531		}
532
533		return $return;
534	}
535
536
537	public function getTasks($project_ids = null) {
538		$sqlite = $this->getDb();
539
540		if($project_ids === null) {
541			$cond_project = '';
542		} else {
543			if(!is_array($project_ids)) {
544				$project_ids = array($project_ids);
545			}
546			$cond_project = " pr.id IN (".$sqlite->quote_and_join($project_ids).") AND ";
547
548		}
549
550		$res = $sqlite->query("SELECT
551				pr.name project_name,
552				t.name task_name,
553				t.id task_id,
554				pr.id project_id
555				FROM page pa
556				JOIN project pr ON pa.id=pr.page_id
557				JOIN task t ON pr.id=t.project_id
558				WHERE
559					$cond_project
560					t.active = 1
561				ORDER BY pr.name,t.name");
562		$taskdata = $sqlite->res2arr($res);
563
564		return $taskdata;
565	}
566
567	public function getRecentTasks($user, $daterange) {
568		$sqlite = $this->getDb();
569		if(!$sqlite) return false;
570
571		$start = new DateTime($daterange['start']);
572		$end = new DateTime($daterange['end']);
573
574		$days = $this->getConf('days_recent_project_active');
575		if($days < 1) $days = 1;
576		if($days > 200) $days = 200;
577		$interval = new DateInterval("P{$days}D");
578
579		$res = $sqlite->query("SELECT
580				pr.name project_name,
581				t.name task_name,
582				t.id task_id,
583				pr.id project_id
584				FROM page AS pa
585					JOIN project pr on pa.id = pr.page_id
586					JOIN task t ON pr.id=t.project_id
587					JOIN user_time AS ut  ON t.id=ut.task_id
588				WHERE
589					user_id = ? AND
590					date between ? AND ? AND
591					ut.value > 0 AND ut.value != ''
592				GROUP BY task_id
593				ORDER BY pr.name,t.name",
594				$this->getUserIdByName($user),
595				$start->sub($interval)->format('Y-m-d'),
596				$end->add($interval)->format('Y-m-d'));
597		$data = $sqlite->res2arr($res);
598
599		return $data;
600	}
601
602	public function getAll() {
603		$sqlite = $this->getDb();
604		if(!$sqlite) return false;
605
606		$res = $sqlite->query("SELECT
607				pr.id project_id,
608				pr.name project_name,
609				t.name task_name,
610				us.user as user_name,
611				ut.date as date,
612				ut.value as hours
613				FROM page AS pa
614					JOIN project pr on pa.id = pr.page_id
615					JOIN task t ON pr.id=t.project_id
616					JOIN user_time AS ut  ON t.id=ut.task_id
617					JOIN user AS us ON ut.user_id = us.id
618				WHERE
619					ut.value > 0 AND ut.value != ''
620				ORDER BY pr.name,t.name,us.user");
621		$data = $sqlite->res2arr($res);
622
623		return $data;
624	}
625
626	/**
627	 *
628	 * http://stackoverflow.com/a/15511864/2455349
629	 * @param unknown $project_id
630	 */
631	public function getProjectSumByWeek($project_id) {
632		$sqlite = $this->getDb();
633
634		$res = $sqlite->query("SELECT
635				pr.name project_name,
636				t.name task_name,
637				t.id task_id,
638				strftime('%Y', date(ut.date, '-3 days', 'weekday 4')) || substr('00'||((strftime('%j', date(ut.date, '-3 days', 'weekday 4')) - 1) / 7 + 1), -2, 2) week,
639				sum(ut.value) hours
640				FROM project pr
641				JOIN task t on pr.id = t.project_id
642				LEFT OUTER JOIN user_time ut on t.id = ut.task_id
643				WHERE
644					pr.id = ?
645				GROUP BY t.id,week
646				ORDER BY week DESC",
647				$project_id);
648		$data = $sqlite->res2arr($res);
649
650		$return = array();
651		foreach($data as $entry) {
652			$return[$entry['task_id']][$entry['week']] = $entry;
653		}
654		return $return;
655	}
656
657	public function getMinMaxDayByProject($project_id) {
658		$sqlite = $this->getDb();
659
660		$res = $sqlite->query("SELECT
661				MIN(ut.date) min,
662				MAX(ut.date) max
663				FROM project pr
664				JOIN task t on pr.id = t.project_id
665				JOIN user_time ut on t.id = ut.task_id
666				WHERE
667					pr.id = ?",
668				$project_id);
669		$data = $sqlite->res2row($res);
670
671		return $data;
672	}
673
674
675	public function getMaxUpdateTime($pageid) {
676		$sqlite = $this->getDb();
677		if(!$sqlite) return false;
678		$res = $sqlite->query("SELECT
679				MAX(ut.update_time)
680				FROM page pa
681				JOIN project pr on pa.id=pr.page_id
682				JOIN task t on pr.id = t.project_id
683				JOIN user_time ut on t.id = ut.task_id
684				WHERE
685					pa.page = ?",
686				$pageid);
687		$data = $sqlite->res2single($res);
688
689		return $data;
690	}
691
692	public function getHoursByPage($pageid) {
693		$sqlite = $this->getDb();
694
695		$res = $sqlite->query("SELECT
696				SUM(ut.value)
697				FROM page pa
698				JOIN project pr on pa.id=pr.page_id
699				JOIN task t on pr.id = t.project_id
700				JOIN user_time ut on t.id = ut.task_id
701				WHERE
702					pa.page = ?",
703				$pageid);
704		$data = $sqlite->res2single($res);
705
706		return $data;
707	}
708
709
710	//////////////////////////////////////////////////////////////////////////
711	// HTML FUNCTIONS
712	//////////////////////////////////////////////////////////////////////////
713
714
715	public function html_prepareDialog($currenttab, $content, $pageid) {
716
717		$tabs = array(
718			'overview' => $this->getLang('overview'),
719			'recent' => $this->getLang('recent'),
720		);
721		if(auth_quickaclcheck($pageid) > AUTH_NONE && $this->isPageTracked($pageid)) {
722			$tabs['current'] = $this->getLang('current page');
723		} else if ($currenttab === 'current') {
724			$currenttab = 'overview';
725		}
726		if(!in_array($currenttab, array_keys($tabs))) return 'currenttab not defined';
727
728
729		$html = '<div id="timetrack-dialog-tabs"><ul>';
730		$ii = 0;
731		foreach($tabs as $tabid => $tab) {
732			$html .= '<li data-tabid="'.$tabid.'"><a href="#timetrack-tab-'.$tabid.'" '.($tabid===$currenttab?'selected="selected"':'').' data-index='.$ii++.'>'.hsc($tab).'</a></li>';
733		}
734		$html .= '</ul><div id="timetrack-tab-'.$currenttab.'">';
735		$html .= $content;
736		$html .= '</div></div>';
737
738		return $html;
739	}
740
741	public function html_overview($user, $daterange,$pageid) {
742		$form = new Form(array(
743				'id'=>'timetrack-form'
744		));
745
746		$dateStart = new DateTime($daterange['start']);
747		$dateEnd = new DateTime($daterange['end']);
748		$dateInterval = new DateInterval('P1D');
749		$dateIntervalWeek = new DateInterval('P7D');
750
751		$datePeriod = new DatePeriod($dateStart,$dateInterval,$dateEnd);
752
753		$form->setHiddenField('yearweek', $daterange['yearweek']);
754		$form->setHiddenField('call', 'plugin_timetrack');
755		$form->setHiddenField('cmd', 'overview');
756		$form->setHiddenField('pageid',$pageid);
757
758
759		$form->addButton('back', sprintf('<< [KW%s]',$dateStart->sub($dateIntervalWeek)->format('W')))->val($dateStart->format('YW'));
760		$form->addButton('today', $this->getLang('today') . ' [KW'. date('W') . ']')->val(date('YW'));
761		$form->addButton('forward', sprintf('>> [KW%s]',$dateEnd->add($dateIntervalWeek)->format('W')))->val($dateEnd->format('YW'));
762		$html = $form->toHTML();
763		$html .= '<h3>KW'.$dateStart->add($dateIntervalWeek)->format('W') . '</h3>';
764		$r = new Doku_Renderer_xhtml();
765
766		$r->table_open();
767		$r->tablerow_open();
768		$r->tableheader_open();
769		$r->cdata($this->getLang('date'));
770		$r->tableheader_close();
771		$r->tableheader_open();
772		$r->cdata($this->getLang('hours'));
773		$r->tableheader_close();
774		$r->tablerow_close();
775
776		$data = $this->getOverviewData($user, $daterange);
777		$sum = 0;
778		foreach($datePeriod as $date) {
779
780			$dateText = $date->format("Y-m-d");
781
782			$r->tablerow_open();
783			$r->tablecell_open();
784			$r->cdata($dateText);
785			$r->tablecell_close();
786			$r->tablecell_open();
787			$r->cdata((int)$data[$dateText]['value']);
788			$r->tablecell_close();
789			$r->tablerow_close();
790
791			$sum += $data[$dateText]['value'];
792		}
793
794		//display Sum
795		$r->tablerow_open();
796		$r->tableheader_open();
797		$r->cdata($this->getLang('sum'));
798		$r->tableheader_close();
799		$r->tableheader_open();
800		$r->cdata($sum);
801		$r->tableheader_close();
802		$r->tablerow_close();
803
804		$r->table_close();
805		$html .= $r->doc;
806		return $html;
807	}
808
809	public function html_week($dbUserValues,$daterange,$dbUserProjects, $cmd,$pageid='') {
810
811		$form = new Form(array(
812				'id'=>'timetrack-form'
813		));
814
815
816		$dateStart = new DateTime($daterange['start']);
817		$dateEnd = new DateTime($daterange['end']);
818		$dateInterval = new DateInterval('P1D');
819		$dateIntervalWeek = new DateInterval('P7D');
820
821		$datePeriod = new DatePeriod($dateStart,$dateInterval,$dateEnd);
822
823		$weekData = $this->getOverviewData($this->getCurrentUser(), $daterange);
824
825		$form->setHiddenField('yearweek', $daterange['yearweek']);
826		$form->setHiddenField('call', 'plugin_timetrack');
827		$form->setHiddenField('cmd', $cmd);
828		$form->setHiddenField('pageid',$pageid);
829
830
831		$form->addButton('back', sprintf('<< [KW%s]',$dateStart->sub($dateIntervalWeek)->format('W')))->val($dateStart->format('YW'));
832		$form->addButton('today', $this->getLang('today') . ' [KW'. date('W') . ']')->val(date('YW'));
833		$form->addButton('forward', sprintf('>> [KW%s]',$dateEnd->add($dateIntervalWeek)->format('W')))->val($dateEnd->format('YW'));
834		$form->addHTML('<h3>KW'.$dateStart->add($dateIntervalWeek)->format('W') . '</h3>');
835		$form->addTagOpen('table');
836		$form->addTagOpen('tr');
837		$form->addTagOpen('th');
838		$form->addHTML($this->getLang('project'));
839		$form->addTagClose('th');
840		$form->addTagOpen('th');
841		$form->addHTML($this->getLang('task'));
842		$form->addTagClose('th');
843		foreach($datePeriod as $date) {
844			$dateName = $this->getLang('Abbr_' . $date->format('l')) .'<br>'. $date->format("d.m.");
845			$form->addTagOpen('th');
846			$form->addHTML($dateName);
847			$form->addTagClose('th');
848		}
849		$form->addTagClose('tr');
850
851		$project_counts = $this->countIndexValue($dbUserProjects, 'project_id');
852		$date_sum_db = array();
853		$date_sum_user = array();
854
855		foreach($dbUserProjects as $task) {
856			$task_id = $task['task_id'];
857			$project_id = $task['project_id'];
858			$projectDetails = $this->getProjectDetails($project_id);
859
860			$form->addTagOpen('tr');
861
862			if($old_project_id != $project_id) {
863				$form->addElement(new TagOpenElement('th',array(
864					'rowspan' => $project_counts[$project_id]
865				)));
866				$form->addLabel($task['project_name']);
867				$form->addTagClose('th');
868			}
869
870			$form->addTagOpen('td');
871			$form->addLabel($task['task_name']);
872			$form->addTagClose('td');
873			foreach($datePeriod as $date) {
874				$dateText = $date->format("Y-m-d");
875				$dateValue = $dbUserValues[$project_id][$task_id][$dateText]['value'];
876
877				if(!isset($date_sum_db[$dateText])) $date_sum_db[$dateText] = 0;
878				if(!isset($date_sum_user[$dateText])) $date_sum_user[$dateText] = 0;
879				if(isset($dbUserValues[$project_id][$task_id][$dateText]['value_db'])) {
880					$date_sum_db[$dateText] += $dbUserValues[$project_id][$task_id][$dateText]['value_db'];
881					$date_sum_user[$dateText] += $dateValue;
882				} else {
883					$date_sum_db[$dateText] += $dateValue;
884					$date_sum_user[$dateText] += $dateValue;
885
886				}
887
888				$form->addTagOpen('td');
889				$el = $form->addTextInput("UserTime[$project_id][$task_id][$dateText]",'')
890				->attr('size',2)
891				->attr('data-date',$dateText)
892				->useInput(false);
893
894				if(($projectDetails['from'] && $projectDetails['from'] > $date->getTimestamp()) ||
895					($projectDetails['to'] && $projectDetails['to'] < $date->getTimestamp())) {
896						$el->attr('readonly','readonly');
897				}
898
899				$el->val($dateValue);
900				$form->addTagClose('td');
901			}
902
903			$form->addTagClose('tr');
904
905			$old_project_id = $project_id;
906		}
907// 		dbg([$date_sum_db,$date_sum_user,$dbUserValues]);
908		$form->addTagOpen('tr');
909		$form->addTag('td')->attr('colspan',2);
910		$form->addTag('td');
911		$form->addTag('td');
912		$form->addTag('td');
913		$form->addTag('td');
914		$form->addTag('td');
915		$form->addTagClose('tr');
916		$form->addTagOpen('tr');
917		$form->addTagOpen('td')->attr('colspan',2);
918		$form->addHTML($this->getLang('sum_day_all'));
919		$form->addTagClose('td');
920
921		foreach($datePeriod as $date) {
922			$dateText = $date->format("Y-m-d");
923			$form->addTagOpen('th')->attrs(array(
924					'data-otherhours' => ($weekData[$dateText]['value'] - $date_sum_db[$dateText]),
925					'data-date' => $dateText
926
927			));
928			$form->addHTML($date_sum_user[$dateText] + $weekData[$dateText]['value'] - $date_sum_db[$dateText]);
929			$form->addTagClose('th');
930		}
931		$form->addTagClose('tr');
932
933		$form->addTagClose('table');
934
935		return $form->toHTML();
936
937
938	}
939
940
941	//////////////////////////////////////////////////////////////////////////
942	// HELPER FUNCTIONS
943	//////////////////////////////////////////////////////////////////////////
944	public function countIndexValue($data,$index) {
945		$return = array();
946		foreach($data as $e) {
947			$return[$e[$index]] += 1;
948		}
949		return $return;
950	}
951
952	public function getCurrentUser() {
953		return $_SERVER['REMOTE_USER'];
954	}
955
956	public function canAccessProject($project_id) {
957
958		$pageid = $this->getPageByProjectId($project_id);
959		if(auth_quickaclcheck($pageid) > AUTH_NONE) return true;
960
961		return false;
962	}
963
964
965	public function checkPageInNamespace($pageid,$namespace) {
966		$namespace = explode(' ',$namespace);
967		$namespace = array_filter($namespace);
968
969		$pageid = trim($pageid,' :');
970
971		foreach($namespace as $ns) {
972
973			$ns  = trim($ns,' :') . ':';
974			if (substr($pageid, 0, strlen($ns)) === $ns) {
975				return true;
976			}
977		}
978		return false;
979	}
980
981	//////////////////////////////////////////////////////////////////////////
982	// DATE FUNCTIONS
983	//////////////////////////////////////////////////////////////////////////
984
985
986	public function getDateWeekRangeByDate($date = null) {
987		if($date === null) $date = date('Y-m-d');
988		$dt = strtotime($datestr);
989		$res['start'] = date('N', $dt)==1 ? date('Y-m-d', $dt) : date('Y-m-d', strtotime('last monday', $dt));
990		$res['end'] = date('N', $dt)==7 ? date('Y-m-d', $dt) : date('Y-m-d', strtotime('next sunday', $dt));
991		return $res;
992	}
993
994	/**
995	 * return daterange by yearweek
996	 * @param string $yearweek 201503
997	 * @return array [start],[end]
998	 */
999	public function getDateRangeByYearWeek($yearweek = null) {
1000		if($yearweek === null) $yearweek = date('YW');
1001
1002		$year = substr($yearweek,0,4);
1003		$week = substr($yearweek,4,2);
1004
1005		$ret = array(
1006				'yearweek' => $yearweek,
1007				'year' => $year,
1008				'week' => $week
1009		);
1010
1011		$days = $this->getConf('weekdays');
1012		if($days < 1) $days = 1;
1013		if($days > 7) $days = 7;
1014
1015		$dto = new DateTime();
1016		$dto->setISODate($year, $week);
1017		$ret['start'] = $dto->format('Y-m-d');
1018		$dto->modify("+$days days");
1019		$ret['end'] = $dto->format('Y-m-d');
1020		return $ret;
1021	}
1022
1023	public function th() {
1024		return 'header';
1025	}
1026	public function td($id) {
1027		return $this->getHoursByPage($id);
1028	}
1029
1030
1031}