2 // +----------------------------------------------------------------------+
3 // | Anuko Time Tracker
4 // +----------------------------------------------------------------------+
5 // | Copyright (c) Anuko International Ltd. (https://www.anuko.com)
6 // +----------------------------------------------------------------------+
7 // | LIBERAL FREEWARE LICENSE: This source code document may be used
8 // | by anyone for any purpose, and freely redistributed alone or in
9 // | combination with other software, provided that the license is obeyed.
11 // | There are only two ways to violate the license:
13 // | 1. To redistribute this code in source form, with the copyright
14 // | notice or license removed or altered. (Distributing in compiled
15 // | forms without embedded copyright notices is permitted).
17 // | 2. To redistribute modified versions of this code in *any* form
18 // | that bears insufficient indications that the modifications are
19 // | not the work of the original author(s).
21 // | This license applies to this document only, not any other software
22 // | that it may be combined with.
24 // +----------------------------------------------------------------------+
26 // | https://www.anuko.com/time_tracker/credits.htm
27 // +----------------------------------------------------------------------+
29 import('DateAndTime');
31 // The ttTimeHelper is a class to help with time-related values.
34 // isWeekend determines if $date falls on weekend.
35 static function isWeekend($date) {
36 $weekDay = date('w', strtotime($date));
37 return ($weekDay == WEEKEND_START_DAY || $weekDay == (WEEKEND_START_DAY + 1) % 7);
40 // isHoliday determines if $date falls on a holiday.
41 static function isHoliday($date) {
45 if (!$user->show_holidays) return false;
47 // $date is expected as string in DB_DATEFORMAT.
48 $month = date('m', strtotime($date));
49 $day = date('d', strtotime($date));
50 if (in_array($month.'/'.$day, $i18n->holidays))
56 // isValidTime validates a value as a time string.
57 static function isValidTime($value) {
58 if (strlen($value)==0 || !isset($value)) return false;
61 if ($value == '24:00' || $value == '2400') return true;
63 if (preg_match('/^([0-1]{0,1}[0-9]|[2][0-3]):?[0-5][0-9]$/', $value )) { // 0:00 - 23:59, 000 - 2359
66 if (preg_match('/^([0-1]{0,1}[0-9]|[2][0-4])$/', $value )) { // 0 - 24
71 if (preg_match('/^[1-9]\s?(am|AM|pm|PM)$/', $value)) { // 1 - 9 am
74 if (preg_match('/^(0[1-9]|1[0-2])\s?(am|AM|pm|PM)$/', $value)) { // 01 - 12 am
77 if (preg_match('/^[1-9]:?[0-5][0-9]\s?(am|AM|pm|PM)$/', $value)) { // 1:00 - 9:59 am, 100 - 959 am
80 if (preg_match('/^(0[1-9]|1[0-2]):?[0-5][0-9]\s?(am|AM|pm|PM)$/', $value)) { // 01:00 - 12:59 am, 0100 - 1259 am
87 // isValidDuration validates a value as a time duration string (in hours and minutes).
88 static function isValidDuration($value) {
89 if (strlen($value) == 0 || !isset($value)) return false;
91 if ($value == '24:00' || $value == '2400') return true;
93 if (preg_match('/^([0-1]{0,1}[0-9]|2[0-3]):?[0-5][0-9]$/', $value )) { // 0:00 - 23:59, 000 - 2359
96 if (preg_match('/^([0-1]{0,1}[0-9]|2[0-4])h?$/', $value )) { // 0, 1 ... 24
101 $localizedPattern = '/^([0-1]{0,1}[0-9]|2[0-3])?['.$user->decimal_mark.'][0-9]{1,4}h?$/';
102 if (preg_match($localizedPattern, $value )) { // decimal values like 0.5, 1.25h, ... .. 23.9999h (or with comma)
109 // validateDuration - a future replacement of the isValidDuration above.
110 // Validates a passed in $value as a time duration string in hours and / or minutes.
111 // Returns either a normalized duration (hh:mm) or false if $value is invalid.
113 // This is a convenience function that allows users to pass in data in a variety of formats.
115 // 3 or 3h - means 3 hours - normalized 3:00. Note: h and m letters are not localized.
116 // 0.25 or 0.25h or .25 or .25h - means a quarter of hour - normalized 0:15.
117 // 0,25 0r 0,25h or ,25 or ,25h - means the same as above for users with comma ad decimal mark.
118 // 1:30 - means 1 hour 30 mminutes - normalized 1:30.
119 // 25m - means 25 minutes - normalized 0:25.
120 static function validateDuration($value) {
121 // Handle empty value.
122 if (!isset($value) || strlen($value) == 0)
125 // Handle whole hours.
126 if (preg_match('/^([0-1]{0,1}[0-9]|2[0-4])h?$/', $value )) { // 0, 1 ... 24
127 $normalized = trim($value, 'h');
128 $normalized .= ':00';
131 // Handle already normalized value.
132 if (preg_match('/^([0-1]{0,1}[0-9]|2[0-3]):?[0-5][0-9]$/', $value )) { // 0:00 - 23:59, 000 - 2359
135 // Handle a special case of 24:00.
136 if ($value == '24:00') {
139 // Handle localized fractional hours.
141 $localizedPattern = '/^([0-1]{0,1}[0-9]|2[0-3])?['.$user->decimal_mark.'][0-9]{1,4}h?$/';
142 if (preg_match($localizedPattern, $value )) { // decimal values like 0.5, 1.25h, ... .. 23.9999h (or with comma)
143 if ($user->decimal_mark == ',')
144 $value = str_replace (',', '.', $value);
146 $val = floatval($value);
147 $mins = round($val * 60);
148 $hours = (string)((int)($mins / 60));
149 $mins = (string)($mins % 60);
150 if (strlen($mins) == 1)
152 return $hours.':'.$mins;
155 if (preg_match('/^\d{1,4}m$/', $value )) { // ddddm
156 $mins = (int) trim($value, 'm');
157 if ($mins > 1440) // More minutes than an entire day could hold.
159 $hours = (string)((int)($mins / 60));
160 $mins = (string)($mins % 60);
161 if (strlen($mins) == 1)
163 return $hours.':'.$mins;
168 // normalizeDuration - converts a valid time duration string to format 00:00.
169 static function normalizeDuration($value, $leadingZero = true) {
170 $time_value = $value;
172 // If we have a decimal format - convert to time format 00:00.
174 if ($user->decimal_mark == ',')
175 $time_value = str_replace (',', '.', $time_value);
177 if((strpos($time_value, '.') !== false) || (strpos($time_value, 'h') !== false)) {
178 $val = floatval($time_value);
179 $mins = round($val * 60);
180 $hours = (string)((int)($mins / 60));
181 $mins = (string)($mins % 60);
182 if ($leadingZero && strlen($hours) == 1)
184 if (strlen($mins) == 1)
186 return $hours.':'.$mins;
189 $time_a = explode(':', $time_value);
193 if ((strlen($time_value) >= 1) && (strlen($time_value) <= 2) && !isset($time_a[1])) {
195 if ($leadingZero && strlen($hours) == 1)
201 if ((strlen($time_value) >= 3) && (strlen($time_value) <= 4) && !isset($time_a[1])) {
202 if (strlen($time_value)==3) $time_value = '0'.$time_value;
203 $hours = substr($time_value,0,2);
204 if ($leadingZero && strlen($hours) == 1)
206 return $hours.':'.substr($time_value,2,2);
209 // 0:00-23:59 (24:00)
210 if ((strlen($time_value) >= 4) && (strlen($time_value) <= 5) && isset($time_a[1])) {
212 if ($leadingZero && strlen($hours) == 1)
214 return $hours.':'.$time_a[1];
220 // toMinutes - converts a time string in format 00:00 to a number of minutes.
221 static function toMinutes($value) {
222 $time_a = explode(':', $value);
223 return (int)@$time_a[1] + ((int)@$time_a[0]) * 60;
226 // toAbsDuration - converts a number of minutes to format 0:00
227 // even if $minutes is negative.
228 static function toAbsDuration($minutes, $abbreviate = false){
229 $hours = (string)((int)abs($minutes / 60));
230 $mins = (string) round(abs(fmod($minutes, 60)));
231 if (strlen($mins) == 1)
233 if ($abbreviate && $mins == '00')
236 return $hours.':'.$mins;
239 // toDuration - calculates duration between start and finish times in 00:00 format.
240 static function toDuration($start, $finish) {
241 $duration_minutes = ttTimeHelper::toMinutes($finish) - ttTimeHelper::toMinutes($start);
242 if ($duration_minutes <= 0) return false;
244 return ttTimeHelper::toAbsDuration($duration_minutes);
247 // The to12HourFormat function converts a 24-hour time value (such as 15:23) to 12 hour format (03:23 PM).
248 static function to12HourFormat($value) {
249 if ('24:00' == $value) return '12:00 AM';
251 $time_a = explode(':', $value);
253 $res = (string)((int)$time_a[0] - 12).':'.$time_a[1].' PM';
254 elseif ($time_a[0] == 12)
256 elseif ($time_a[0] == 0)
257 $res = '12:'.$time_a[1].' AM';
263 // The to24HourFormat function attempts to convert a string value (human readable notation of time of day)
264 // to a 24-hour time format HH:MM.
265 static function to24HourFormat($value) {
268 // Algorithm: use regular expressions to find a matching pattern, starting with most popular patterns first.
269 $tmp_val = trim($value);
272 if (preg_match('/^([01][0-9]|2[0-3]):[0-5][0-9]$/', $tmp_val)) { // 00:00 - 23:59
273 // We already have a 24-hour format. Just return it.
277 if (preg_match('/^[0-9]:[0-5][0-9]$/', $tmp_val)) { // 0:00 - 9:59
278 // This is a 24-hour format without a leading zero. Add 0 and return.
282 if (preg_match('/^[0-9]$/', $tmp_val)) { // 0 - 9
283 // Single digit. Assuming hour number.
284 $res = '0'.$tmp_val.':00';
287 if (preg_match('/^([01][0-9]|2[0-4])$/', $tmp_val)) { // 00 - 24
288 // Two digit hour number.
289 $res = $tmp_val.':00';
292 if (preg_match('/^[0-9][0-5][0-9]$/', $tmp_val)) { // 000 - 959
293 // Missing colon. We'll assume the first digit is the hour, the rest is minutes.
294 $tmp_arr = str_split($tmp_val);
295 $res = '0'.$tmp_arr[0].':'.$tmp_arr[1].$tmp_arr[2];
298 if (preg_match('/^([01][0-9]|2[0-3])[0-5][0-9]$/', $tmp_val)) { // 0000 - 2359
299 // Missing colon. We'll assume the first 2 digits are the hour, the rest is minutes.
300 $tmp_arr = str_split($tmp_val);
301 $res = $tmp_arr[0].$tmp_arr[1].':'.$tmp_arr[2].$tmp_arr[3];
304 // Special handling for midnight.
305 if ($tmp_val == '24:00' || $tmp_val == '2400')
308 // 12 hour AM patterns.
309 if (preg_match('/.(am|AM)$/', $tmp_val)) {
311 // The $value ends in am or AM. Strip it.
312 $tmp_val = rtrim(substr($tmp_val, 0, -2));
314 // Special case to handle 12, 12:MM, and 12MM AM.
315 if (preg_match('/^12:?([0-5][0-9])?$/', $tmp_val))
316 $tmp_val = '00'.substr($tmp_val, 2);
318 // We are ready to convert AM time.
319 if (preg_match('/^(0[0-9]|1[0-1]):[0-5][0-9]$/', $tmp_val)) { // 00:00 - 11:59
320 // We already have a 24-hour format. Just return it.
324 if (preg_match('/^[1-9]:[0-5][0-9]$/', $tmp_val)) { // 1:00 - 9:59
325 // This is a 24-hour format without a leading zero. Add 0 and return.
329 if (preg_match('/^[1-9]$/', $tmp_val)) { // 1 - 9
330 // Single digit. Assuming hour number.
331 $res = '0'.$tmp_val.':00';
334 if (preg_match('/^(0[0-9]|1[0-1])$/', $tmp_val)) { // 00 - 11
335 // Two digit hour number.
336 $res = $tmp_val.':00';
339 if (preg_match('/^[1-9][0-5][0-9]$/', $tmp_val)) { // 100 - 959
340 // Missing colon. Assume the first digit is the hour, the rest is minutes.
341 $tmp_arr = str_split($tmp_val);
342 $res = '0'.$tmp_arr[0].':'.$tmp_arr[1].$tmp_arr[2];
345 if (preg_match('/^(0[0-9]|1[0-1])[0-5][0-9]$/', $tmp_val)) { // 0000 - 1159
346 // Missing colon. We'll assume the first 2 digits are the hour, the rest is minutes.
347 $tmp_arr = str_split($tmp_val);
348 $res = $tmp_arr[0].$tmp_arr[1].':'.$tmp_arr[2].$tmp_arr[3];
351 } // AM cases handling.
353 // 12 hour PM patterns.
354 if (preg_match('/.(pm|PM)$/', $tmp_val)) {
356 // The $value ends in pm or PM. Strip it.
357 $tmp_val = rtrim(substr($tmp_val, 0, -2));
359 if (preg_match('/^[1-9]$/', $tmp_val)) { // 1 - 9
360 // Single digit. Assuming hour number.
361 $hour = (string)(12 + (int)$tmp_val);
365 if (preg_match('/^((0[1-9])|(1[0-2]))$/', $tmp_val)) { // 01 - 12
366 // Double digit hour.
367 if ('12' != $tmp_val)
368 $tmp_val = (string)(12 + (int)$tmp_val);
369 $res = $tmp_val.':00';
372 if (preg_match('/^[1-9][0-5][0-9]$/', $tmp_val)) { // 100 - 959
373 // Missing colon. We'll assume the first digit is the hour, the rest is minutes.
374 $tmp_arr = str_split($tmp_val);
375 $hour = (string)(12 + (int)$tmp_arr[0]);
376 $res = $hour.':'.$tmp_arr[1].$tmp_arr[2];
379 if (preg_match('/^(0[1-9]|1[0-2])[0-5][0-9]$/', $tmp_val)) { // 0100 - 1259
380 // Missing colon. We'll assume the first 2 digits are the hour, the rest is minutes.
381 $hour = substr($tmp_val, 0, -2);
382 $min = substr($tmp_val, 2);
384 $hour = (string)(12 + (int)$hour);
385 $res = $hour.':'.$min;
388 if (preg_match('/^[1-9]:[0-5][0-9]$/', $tmp_val)) { // 1:00 - 9:59
389 $hour = substr($tmp_val, 0, -3);
390 $min = substr($tmp_val, 2);
391 $hour = (string)(12 + (int)$hour);
392 $res = $hour.':'.$min;
395 if (preg_match('/^(0[1-9]|1[0-2]):[0-5][0-9]$/', $tmp_val)) { // 01:00 - 12:59
396 $hour = substr($tmp_val, 0, -3);
397 $min = substr($tmp_val, 3);
399 $hour = (string)(12 + (int)$hour);
400 $res = $hour.':'.$min;
403 } // PM cases handling.
408 // isValidInterval - checks if finish time is greater than start time.
409 static function isValidInterval($start, $finish) {
410 $start = ttTimeHelper::to24HourFormat($start);
411 $finish = ttTimeHelper::to24HourFormat($finish);
412 if ('00:00' == $finish) $finish = '24:00';
414 $minutesStart = ttTimeHelper::toMinutes($start);
415 $minutesFinish = ttTimeHelper::toMinutes($finish);
416 if ($minutesFinish > $minutesStart)
422 // insert - inserts a time record into log table. Does not deal with custom fields.
423 static function insert($fields)
425 $mdb2 = getConnection();
427 $timestamp = isset($fields['timestamp']) ? $fields['timestamp'] : '';
428 $user_id = $fields['user_id'];
429 $date = $fields['date'];
430 $start = $fields['start'];
431 $finish = $fields['finish'];
432 $duration = $fields['duration'];
433 $client = $fields['client'];
434 $project = $fields['project'];
435 $task = $fields['task'];
436 $invoice = $fields['invoice'];
437 $note = $fields['note'];
438 $billable = $fields['billable'];
439 $paid = $fields['paid'];
440 if (array_key_exists('status', $fields)) { // Key exists and may be NULL during migration of data.
441 $status_f = ', status';
442 $status_v = ', '.$mdb2->quote($fields['status']);
445 $start = ttTimeHelper::to24HourFormat($start);
447 $finish = ttTimeHelper::to24HourFormat($finish);
448 if ('00:00' == $finish) $finish = '24:00';
450 $duration = ttTimeHelper::normalizeDuration($duration);
453 $timestamp = date('YmdHis'); //yyyymmddhhmmss
454 // TODO: this timestamp could be illegal if we hit inside DST switch deadzone, such as '2016-03-13 02:30:00'
455 // Anything between 2am and 3am on DST introduction date will not work if we run on a system with DST on.
456 // We need to address this properly to avoid potential complications.
459 if (!$billable) $billable = 0;
460 if (!$paid) $paid = 0;
463 $sql = "insert into tt_log (timestamp, user_id, date, duration, client_id, project_id, task_id, invoice_id, comment, billable, paid $status_f) ".
464 "values ('$timestamp', $user_id, ".$mdb2->quote($date).", '$duration', ".$mdb2->quote($client).", ".$mdb2->quote($project).", ".$mdb2->quote($task).", ".$mdb2->quote($invoice).", ".$mdb2->quote($note).", $billable, $paid $status_v)";
465 $affected = $mdb2->exec($sql);
466 if (is_a($affected, 'PEAR_Error'))
469 $duration = ttTimeHelper::toDuration($start, $finish);
470 if ($duration === false) $duration = 0;
471 if (!$duration && ttTimeHelper::getUncompleted($user_id)) return false;
473 $sql = "insert into tt_log (timestamp, user_id, date, start, duration, client_id, project_id, task_id, invoice_id, comment, billable, paid $status_f) ".
474 "values ('$timestamp', $user_id, ".$mdb2->quote($date).", '$start', '$duration', ".$mdb2->quote($client).", ".$mdb2->quote($project).", ".$mdb2->quote($task).", ".$mdb2->quote($invoice).", ".$mdb2->quote($note).", $billable, $paid $status_v)";
475 $affected = $mdb2->exec($sql);
476 if (is_a($affected, 'PEAR_Error'))
480 $id = $mdb2->lastInsertID('tt_log', 'id');
484 // update - updates a record in log table. Does not update its custom fields.
485 static function update($fields)
488 $mdb2 = getConnection();
491 $date = $fields['date'];
492 $user_id = $fields['user_id'];
493 $client = $fields['client'];
494 $project = $fields['project'];
495 $task = $fields['task'];
496 $start = $fields['start'];
497 $finish = $fields['finish'];
498 $duration = $fields['duration'];
499 $note = $fields['note'];
502 if ($user->isPluginEnabled('iv')) {
503 $billable_part = $fields['billable'] ? ', billable = 1' : ', billable = 0';
506 if ($user->canManageTeam() && $user->isPluginEnabled('ps')) {
507 $paid_part = $fields['paid'] ? ', paid = 1' : ', paid = 0';
510 $start = ttTimeHelper::to24HourFormat($start);
511 $finish = ttTimeHelper::to24HourFormat($finish);
512 if ('00:00' == $finish) $finish = '24:00';
513 $duration = ttTimeHelper::normalizeDuration($duration);
515 if ($start) $duration = '';
518 $sql = "UPDATE tt_log set start = NULL, duration = '$duration', client_id = ".$mdb2->quote($client).", project_id = ".$mdb2->quote($project).", task_id = ".$mdb2->quote($task).", ".
519 "comment = ".$mdb2->quote($note)."$billable_part $paid_part, date = '$date' WHERE id = $id";
520 $affected = $mdb2->exec($sql);
521 if (is_a($affected, 'PEAR_Error'))
524 $duration = ttTimeHelper::toDuration($start, $finish);
525 if ($duration === false)
527 $uncompleted = ttTimeHelper::getUncompleted($user_id);
528 if (!$duration && $uncompleted && ($uncompleted['id'] != $id))
531 $sql = "UPDATE tt_log SET start = '$start', duration = '$duration', client_id = ".$mdb2->quote($client).", project_id = ".$mdb2->quote($project).", task_id = ".$mdb2->quote($task).", ".
532 "comment = ".$mdb2->quote($note)."$billable_part $paid_part, date = '$date' WHERE id = $id";
533 $affected = $mdb2->exec($sql);
534 if (is_a($affected, 'PEAR_Error'))
540 // delete - deletes a record from tt_log table and its associated custom field values.
541 static function delete($id, $user_id) {
542 $mdb2 = getConnection();
544 $sql = "update tt_log set status = NULL where id = $id and user_id = $user_id";
545 $affected = $mdb2->exec($sql);
546 if (is_a($affected, 'PEAR_Error'))
549 $sql = "update tt_custom_field_log set status = NULL where log_id = $id";
550 $affected = $mdb2->exec($sql);
551 if (is_a($affected, 'PEAR_Error'))
557 // getTimeForDay - gets total time for a user for a specific date.
558 static function getTimeForDay($user_id, $date) {
559 $mdb2 = getConnection();
561 $sql = "select sum(time_to_sec(duration)) as sm from tt_log where user_id = $user_id and date = '$date' and status = 1";
562 $res = $mdb2->query($sql);
563 if (!is_a($res, 'PEAR_Error')) {
564 $val = $res->fetchRow();
565 return sec_to_time_fmt_hm($val['sm']);
570 // getTimeForWeek - gets total time for a user for a given week.
571 static function getTimeForWeek($user_id, $date) {
573 $mdb2 = getConnection();
575 $period = new Period(INTERVAL_THIS_WEEK, $date);
576 $sql = "select sum(time_to_sec(duration)) as sm from tt_log where user_id = $user_id and date >= '".$period->getStartDate(DB_DATEFORMAT)."' and date <= '".$period->getEndDate(DB_DATEFORMAT)."' and status = 1";
577 $res = $mdb2->query($sql);
578 if (!is_a($res, 'PEAR_Error')) {
579 $val = $res->fetchRow();
580 return sec_to_time_fmt_hm($val['sm']);
585 // getTimeForMonth - gets total time for a user for a given month.
586 static function getTimeForMonth($user_id, $date){
588 $mdb2 = getConnection();
590 $period = new Period(INTERVAL_THIS_MONTH, $date);
591 $sql = "select sum(time_to_sec(duration)) as sm from tt_log where user_id = $user_id and date >= '".$period->getStartDate(DB_DATEFORMAT)."' and date <= '".$period->getEndDate(DB_DATEFORMAT)."' and status = 1";
592 $res = $mdb2->query($sql);
593 if (!is_a($res, 'PEAR_Error')) {
594 $val = $res->fetchRow();
595 return sec_to_time_fmt_hm($val['sm']);
600 // getUncompleted - retrieves an uncompleted record for user, if one exists.
601 static function getUncompleted($user_id) {
602 $mdb2 = getConnection();
604 $sql = "select id, start from tt_log
605 where user_id = $user_id and start is not null and time_to_sec(duration) = 0 and status = 1";
606 $res = $mdb2->query($sql);
607 if (!is_a($res, 'PEAR_Error')) {
608 if (!$res->numRows()) {
611 if ($val = $res->fetchRow()) {
618 // overlaps - determines if a record overlaps with an already existing record.
621 // $user_id - user id for whom to determine overlap
623 // $start - new record start time
624 // $finish - new record finish time, may be null
625 // $record_id - optional record id we may be editing, excluded from overlap set
626 static function overlaps($user_id, $date, $start, $finish, $record_id = null) {
627 // Do not bother checking if we allow overlaps.
628 if (defined('ALLOW_OVERLAP') && ALLOW_OVERLAP == true)
631 $mdb2 = getConnection();
633 $start = ttTimeHelper::to24HourFormat($start);
635 $finish = ttTimeHelper::to24HourFormat($finish);
636 if ('00:00' == $finish) $finish = '24:00';
638 // Handle these 3 overlap situations:
639 // - start time in existing record
640 // - end time in existing record
641 // - record fully encloses existing record
642 $sql = "select id from tt_log
643 where user_id = $user_id and date = ".$mdb2->quote($date)."
644 and start is not null and duration is not null and status = 1 and (
645 (cast(".$mdb2->quote($start)." as time) >= start and cast(".$mdb2->quote($start)." as time) < addtime(start, duration))";
647 $sql .= " or (cast(".$mdb2->quote($finish)." as time) <= addtime(start, duration) and cast(".$mdb2->quote($finish)." as time) > start)
648 or (cast(".$mdb2->quote($start)." as time) < start and cast(".$mdb2->quote($finish)." as time) > addtime(start, duration))";
652 $sql .= " and id <> $record_id";
654 $res = $mdb2->query($sql);
655 if (!is_a($res, 'PEAR_Error')) {
656 if (!$res->numRows()) {
659 if ($val = $res->fetchRow()) {
666 // getRecord - retrieves a time record identified by its id.
667 static function getRecord($id, $user_id) {
669 $sql_time_format = "'%k:%i'"; // 24 hour format.
670 if ('%I:%M %p' == $user->time_format)
671 $sql_time_format = "'%h:%i %p'"; // 12 hour format for MySQL TIME_FORMAT function.
673 $mdb2 = getConnection();
675 $sql = "select l.id as id, l.timestamp as timestamp, TIME_FORMAT(l.start, $sql_time_format) as start,
676 TIME_FORMAT(sec_to_time(time_to_sec(l.start) + time_to_sec(l.duration)), $sql_time_format) as finish,
677 TIME_FORMAT(l.duration, '%k:%i') as duration,
678 p.name as project_name, t.name as task_name, l.comment, l.client_id, l.project_id, l.task_id, l.invoice_id, l.billable, l.paid, l.date
680 left join tt_projects p on (p.id = l.project_id)
681 left join tt_tasks t on (t.id = l.task_id)
682 where l.id = $id and l.user_id = $user_id and l.status = 1";
683 $res = $mdb2->query($sql);
684 if (!is_a($res, 'PEAR_Error')) {
685 if (!$res->numRows()) {
688 if ($val = $res->fetchRow()) {
695 // getAllRecords - returns all time records for a certain user.
696 static function getAllRecords($user_id) {
699 $mdb2 = getConnection();
701 $sql = "select l.id, l.timestamp, l.user_id, l.date, TIME_FORMAT(l.start, '%k:%i') as start,
702 TIME_FORMAT(sec_to_time(time_to_sec(l.start) + time_to_sec(l.duration)), '%k:%i') as finish,
703 TIME_FORMAT(l.duration, '%k:%i') as duration,
704 l.client_id, l.project_id, l.task_id, l.invoice_id, l.comment, l.billable, l.paid, l.status
705 from tt_log l where l.user_id = $user_id order by l.id";
706 $res = $mdb2->query($sql);
707 if (!is_a($res, 'PEAR_Error')) {
708 while ($val = $res->fetchRow()) {
716 // getRecords - returns time records for a user for a given date.
717 static function getRecords($user_id, $date) {
719 $sql_time_format = "'%k:%i'"; // 24 hour format.
720 if ('%I:%M %p' == $user->time_format)
721 $sql_time_format = "'%h:%i %p'"; // 12 hour format for MySQL TIME_FORMAT function.
724 $mdb2 = getConnection();
726 $client_field = null;
727 if ($user->isPluginEnabled('cl'))
728 $client_field = ", c.name as client";
730 $left_joins = " left join tt_projects p on (l.project_id = p.id)".
731 " left join tt_tasks t on (l.task_id = t.id)";
732 if ($user->isPluginEnabled('cl'))
733 $left_joins .= " left join tt_clients c on (l.client_id = c.id)";
735 $sql = "select l.id as id, TIME_FORMAT(l.start, $sql_time_format) as start,
736 TIME_FORMAT(sec_to_time(time_to_sec(l.start) + time_to_sec(l.duration)), $sql_time_format) as finish,
737 TIME_FORMAT(l.duration, '%k:%i') as duration, p.name as project, t.name as task, l.comment, l.billable, l.invoice_id $client_field
740 where l.date = '$date' and l.user_id = $user_id and l.status = 1
741 order by l.start, l.id";
742 $res = $mdb2->query($sql);
743 if (!is_a($res, 'PEAR_Error')) {
744 while ($val = $res->fetchRow()) {
745 if($val['duration']=='0:00')