X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=WEB-INF%2Flib%2FttTimesheetHelper.class.php;h=17119edace1c8eb78ffaca461bc0c4a060d2227d;hb=237f5fae68a9410971ce1c4895a07fe2fa0fbd52;hp=1fff195549fa69b4a48d2a5a1565e41ec7dc9b69;hpb=b25ffbdc86bf97657cbca486aaa1aa068fb16288;p=timetracker.git diff --git a/WEB-INF/lib/ttTimesheetHelper.class.php b/WEB-INF/lib/ttTimesheetHelper.class.php index 1fff1955..17119eda 100644 --- a/WEB-INF/lib/ttTimesheetHelper.class.php +++ b/WEB-INF/lib/ttTimesheetHelper.class.php @@ -27,6 +27,7 @@ // +----------------------------------------------------------------------+ import('ttUserHelper'); +import('ttFileHelper'); // Class ttTimesheetHelper is used to help with project related tasks. class ttTimesheetHelper { @@ -74,9 +75,12 @@ class ttTimesheetHelper { $end_date = new DateAndTime($user->date_format, $fields['end_date']); $end = $end_date->toString(DB_DATEFORMAT); - $sql = "insert into tt_timesheets (user_id, group_id, org_id, client_id, project_id, name, comment, start_date, end_date)". + $created_part = ', now(), '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', '.$user->id; + + $sql = "insert into tt_timesheets (user_id, group_id, org_id, client_id, project_id, name, comment,". + " start_date, end_date, created, created_ip, created_by)". " values ($user_id, $group_id, $org_id, ".$mdb2->quote($client_id).", ".$mdb2->quote($project_id).", ".$mdb2->quote($name). - ", ".$mdb2->quote($comment).", ".$mdb2->quote($start).", ".$mdb2->quote($end).")"; + ", ".$mdb2->quote($comment).", ".$mdb2->quote($start).", ".$mdb2->quote($end).$created_part.")"; $affected = $mdb2->exec($sql); if (is_a($affected, 'PEAR_Error')) return false; @@ -111,10 +115,17 @@ class ttTimesheetHelper { $group_id = $user->getGroup(); $org_id = $user->org_id; + if ($user->isPluginEnabled('at')) { + $filePart = ', if(Sub1.entity_id is null, 0, 1) as has_files'; + $fileJoin = " left join (select distinct entity_id from tt_files". + " where entity_type = 'timesheet' and group_id = $group_id and org_id = $org_id and status = 1) Sub1". + " on (ts.id = Sub1.entity_id)"; + } + $result = array(); $sql = "select ts.id, ts.name, ts.client_id, c.name as client_name,". - " ts.submit_status, ts.approve_status from tt_timesheets ts". - " left join tt_clients c on (c.id = ts.client_id)". + " ts.submit_status, ts.approve_status $filePart from tt_timesheets ts". + " left join tt_clients c on (c.id = ts.client_id) $fileJoin". " where ts.status = 1 and ts.group_id = $group_id and ts.org_id = $org_id and ts.user_id = $user_id". " order by ts.name"; $res = $mdb2->query($sql); @@ -137,10 +148,17 @@ class ttTimesheetHelper { $group_id = $user->getGroup(); $org_id = $user->org_id; + if ($user->isPluginEnabled('at')) { + $filePart = ', if(Sub1.entity_id is null, 0, 1) as has_files'; + $fileJoin = " left join (select distinct entity_id from tt_files". + " where entity_type = 'timesheet' and group_id = $group_id and org_id = $org_id and status = 1) Sub1". + " on (ts.id = Sub1.entity_id)"; + } + $result = array(); $sql = "select ts.id, ts.name, ts.client_id, c.name as client_name,". - " ts.submit_status, ts.approve_status from tt_timesheets ts". - " left join tt_clients c on (c.id = ts.client_id)". + " ts.submit_status, ts.approve_status $filePart from tt_timesheets ts". + " left join tt_clients c on (c.id = ts.client_id) $fileJoin". " where ts.status = 0 and ts.group_id = $group_id and ts.org_id = $org_id and ts.user_id = $user_id". " order by ts.name"; $res = $mdb2->query($sql); @@ -181,6 +199,15 @@ class ttTimesheetHelper { global $user; $mdb2 = getConnection(); + // Delete associated files. + if ($user->isPluginEnabled('at')) { + import('ttFileHelper'); + global $err; + $fileHelper = new ttFileHelper($err); + if (!$fileHelper->deleteEntityFiles($timesheet_id, 'timesheet')) + return false; + } + $user_id = $user->getUser(); $group_id = $user->getGroup(); $org_id = $user->org_id; @@ -191,8 +218,10 @@ class ttTimesheetHelper { $affected = $mdb2->exec($sql); if (is_a($affected, 'PEAR_Error')) return false; + $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$user->id; + // Delete timesheet. - $sql = "update tt_timesheets set status = null". + $sql = "update tt_timesheets set status = null".$modified_part. " where id = $timesheet_id and user_id = $user_id and group_id = $group_id and org_id = $org_id"; $affected = $mdb2->exec($sql); return (!is_a($affected, 'PEAR_Error')); @@ -212,7 +241,10 @@ class ttTimesheetHelper { $comment = $fields['comment']; $status = $fields['status']; // Timesheet status. - $sql = "update tt_timesheets set name = ".$mdb2->quote($name).", comment = ".$mdb2->quote($comment). + $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$user->id; + + $sql = "update tt_timesheets set name = ".$mdb2->quote($name). + ", comment = ".$mdb2->quote($comment).$modified_part. ", status = ".$mdb2->quote($status). " where id = $timesheet_id and user_id = $user_id and group_id = $group_id and org_id = $org_id"; $affected = $mdb2->exec($sql); @@ -264,9 +296,31 @@ class ttTimesheetHelper { return $approvers; } - // submitTimesheet marks a timesheet as submitted and also sends an email - // to a selected approver. - static function submitTimesheet($fields) { + // getApprover obtains approver properties such as name and email. + static function getApprover($user_id) { + global $user; + $mdb2 = getConnection(); + + $group_id = $user->getGroup(); + $org_id = $user->org_id; + + $rank = ttUserHelper::getUserRank($user->getUser()); + $sql = "select u.name, u.email". + " from tt_users u". + " left join tt_roles r on (r.id = u.role_id)". + " where u.id = $user_id and u.status = 1 and u.email is not null and u.group_id = $group_id and u.org_id = $org_id". + " and (r.rank > $rank and r.rights like '%approve_timesheets%')"; + $res = $mdb2->query($sql); + if (!is_a($res, 'PEAR_Error')) { + if ($val = $res->fetchRow()) { + return $val; + } + } + return false; + } + + // markSubmitted marks a timesheet as submitted. + static function markSubmitted($fields) { global $user; $mdb2 = getConnection(); @@ -274,23 +328,92 @@ class ttTimesheetHelper { $group_id = $user->getGroup(); $org_id = $user->org_id; - // First, mark timesheet as submitted. - // Even if mail part below does not work, this will get us a functioning workflow - // without email notification. + $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$user->id; + $timesheet_id = $fields['timesheet_id']; - $sql = "update tt_timesheets set submit_status = 1". + $sql = "update tt_timesheets set submit_status = 1".$modified_part. " where id = $timesheet_id and user_id = $user_id and group_id = $group_id and org_id = $org_id"; $affected = $mdb2->exec($sql); - if (is_a($affected, 'PEAR_Error')) return false; + return (!is_a($affected, 'PEAR_Error')); + } + + // sendSubmitEmail sends a notification to an approver about a timesheet submit. + static function sendSubmitEmail($fields) { + global $i18n; + global $user; + + // Send email to a selected approver. + if (!$fields['approver_id']) return true; // No approver, nothing to do. + + $approver = ttTimesheetHelper::getApprover($fields['approver_id']); + if (!$approver) return false; // Invalid approver id. + + $fields['to'] = $approver['email']; + $fields['subject'] = $i18n->get('form.timesheet_view.submit_subject'); + $fields['body'] = sprintf($i18n->get('form.timesheet_view.submit_body'), $user->getName()); + + return ttTimesheetHelper::sendEmail($fields); + } + + // sendApprovedEmail sends a notification to user about a timesheet approval. + static function sendApprovedEmail($fields) { + global $i18n; + global $user; + + // Obtain user email. + $user_details = $user->getUserDetails($fields['user_id']); + $email = $user_details['email']; + if (!$email) return true; // No email to send to, nothing to do. + + $fields['to'] = $email; + $fields['subject'] = $i18n->get('form.timesheet_view.approve_subject'); + $fields['body'] = sprintf($i18n->get('form.timesheet_view.approve_body'), htmlspecialchars($fields['name']), htmlspecialchars($fields['comment'])); + + return ttTimesheetHelper::sendEmail($fields); + } + + // sendDisapprovedEmail sends a notification to user about a timesheet disapproval. + static function sendDisapprovedEmail($fields) { + global $i18n; + global $user; + + // Obtain user email. + $user_details = $user->getUserDetails($fields['user_id']); + $email = $user_details['email']; + if (!$email) return true; // No email to send to, nothing to do. + + $fields['to'] = $email; + $fields['subject'] = $i18n->get('form.timesheet_view.disapprove_subject'); + $fields['body'] = sprintf($i18n->get('form.timesheet_view.disapprove_body'), htmlspecialchars($fields['name']), htmlspecialchars($fields['comment'])); + + return ttTimesheetHelper::sendEmail($fields); + } - // TODO: send email to approver here... - // $approver_id = $fields['approver_id']; + // sendEmail is a generic finction that sends a timesheet related email. + // TODO: perhaps make it even more generic for the entire application. + static function sendEmail($fields, $html = true) { + global $i18n; + global $user; + + // Send email. + import('mail.Mailer'); + $mailer = new Mailer(); + $mailer->setCharSet(CHARSET); + if ($html) + $mailer->setContentType('text/html'); + $mailer->setSender(SENDER); + $mailer->setReceiver($fields['to']); + if (!empty($user->bcc_email)) + $mailer->setReceiverBCC($user->bcc_email); + $mailer->setMailMode(MAIL_MODE); + if (!$mailer->send($fields['subject'], $fields['body'])) + return false; return true; } - // approveTimesheet marks a timesheet as approved and sends an email to submitter. - static function approveTimesheet($fields) { + // markApproved marks a timesheet as approved. + static function markApproved($fields) { global $user; $mdb2 = getConnection(); @@ -298,23 +421,19 @@ class ttTimesheetHelper { $group_id = $user->getGroup(); $org_id = $user->org_id; - // First, mark timesheet as approved. - // Even if mail part below does not work, this will get us a functioning workflow - // without email notification. $timesheet_id = $fields['timesheet_id']; $comment = $fields['comment']; - $sql = "update tt_timesheets set approve_status = 1, approve_comment = ".$mdb2->quote($comment). + $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$user->id; + + $sql = "update tt_timesheets set approve_status = 1, approve_comment = ".$mdb2->quote($comment).$modified_part. " where id = $timesheet_id and submit_status = 1 and user_id = $user_id and group_id = $group_id and org_id = $org_id"; $affected = $mdb2->exec($sql); - if (is_a($affected, 'PEAR_Error')) return false; - - // TODO: send email to submitter here... - return true; + return (!is_a($affected, 'PEAR_Error')); } - // disapproveTimesheet marks a timesheet as approved and sends an email to submitter. - static function disapproveTimesheet($fields) { + // markDisapproved marks a timesheet as not approved. + static function markDisapproved($fields) { global $user; $mdb2 = getConnection(); @@ -322,19 +441,15 @@ class ttTimesheetHelper { $group_id = $user->getGroup(); $org_id = $user->org_id; - // First, mark timesheet as disapproved. - // Even if mail part below does not work, this will get us a functioning workflow - // without email notification. $timesheet_id = $fields['timesheet_id']; $comment = $fields['comment']; - $sql = "update tt_timesheets set approve_status = 0, approve_comment = ".$mdb2->quote($comment). + $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$user->id; + + $sql = "update tt_timesheets set approve_status = 0, approve_comment = ".$mdb2->quote($comment).$modified_part. " where id = $timesheet_id and submit_status = 1 and user_id = $user_id and group_id = $group_id and org_id = $org_id"; $affected = $mdb2->exec($sql); - if (is_a($affected, 'PEAR_Error')) return false; - - // TODO: send email to submitter here... - return true; + return (!is_a($affected, 'PEAR_Error')); } // The timesheetItemsExist determines whether tt_log records exist in the specified period @@ -374,4 +489,100 @@ class ttTimesheetHelper { return false; } + + // The overlaps function determines if a new timesheet overlaps with + // an already existing timesheet. + static function overlaps($fields) { + global $user; + $mdb2 = getConnection(); + + $user_id = $user->getUser(); + $group_id = $user->getGroup(); + $org_id = $user->org_id; + + if (isset($fields['client_id'])) $client_id = (int) $fields['client_id']; + if (isset($fields['project_id'])) $project_id = (int) $fields['project_id']; + + $start_date = new DateAndTime($user->date_format, $fields['start_date']); + $start = $start_date->toString(DB_DATEFORMAT); + $quoted_start = $mdb2->quote($start); + + $end_date = new DateAndTime($user->date_format, $fields['end_date']); + $end = $end_date->toString(DB_DATEFORMAT); + $quoted_end = $mdb2->quote($end); + + // sql parts. + if ($client_id) $client_part = " and client_id = $client_id"; + if ($project_id) $project_part = " and project_id = $project_id"; + + $sql = "select id from tt_timesheets". + " where status is not null $client_part $project_part". + " and (($quoted_start >= start_date and $quoted_start <= end_date)". + " or ($quoted_end >= start_date and $quoted_end <= end_date))". + " and user_id = $user_id and group_id = $group_id and org_id = $org_id"; + $res = $mdb2->query($sql); + if (!is_a($res, 'PEAR_Error')) { + $val = $res->fetchRow(); + if ($val['id']) { + return true; + } + } + return false; + } + + // The getMatchingTimesheets function retrieves a timesheet that "matches" + // a report for an option to assign report items to it. + // + // Condition: report range is fully enclosed in an existing timesheet with + // matching client_id and project_id and null approved_status. + static function getMatchingTimesheets($options) { + global $user; + $mdb2 = getConnection(); + + $user_id = $user->getUser(); + $group_id = $user->getGroup(); + $org_id = $user->org_id; + + // Check users. + if (isset($options['users'])) { + $comma_separated = $options['users']; + $users = explode(',', $comma_separated); + if (count($users) > 1 || $users[0] != $user->getUser()) + return false; + } + + // No timesheets for expenses. + if ($options['show_cost'] && $user->isPluginEnabled('ex')) return false; + + // Parts for client and project. + if ($options['client_id']) $client_part = ' and (client_id is null or client_id = '.(int)$options['client_id'].')'; + if ($options['project_id']) $project_part = ' and (project_id is null or project_id = '.(int)$options['project_id'].')'; + + // Determine start and end dates. + $dateFormat = $user->getDateFormat(); + if ($options['period']) + $period = new Period($options['period'], new DateAndTime($dateFormat)); + else { + $period = new Period(); + $period->setPeriod( + new DateAndTime($dateFormat, $options['period_start']), + new DateAndTime($dateFormat, $options['period_end'])); + } + $start = $period->getStartDate(DB_DATEFORMAT); + $end = $period->getEndDate(DB_DATEFORMAT); + + $result = false; + $sql = "select id, name from tt_timesheets". + " where ".$mdb2->quote($start)." >= start_date and ".$mdb2->quote($end)." <= end_date". + "$client_part $project_part". + " and user_id = $user_id and group_id = $group_id and org_id = $org_id". + " and approve_status is null and status is not null"; + $res = $mdb2->query($sql); + if (!is_a($res, 'PEAR_Error')) { + while ($val = $res->fetchRow()) { + $result[] = $val; + } + } + return $result; + } }