function getTimeSpentWorkerReportAction() { $db = DevblocksPlatform::getDatabaseService(); $subtotal = array(); $total_cm = array(); DevblocksPlatform::getExtensions('timetracking.source', true); @($sel_worker_id = DevblocksPlatform::importGPC($_REQUEST['worker_id'], 'integer', 0)); @($report_type = DevblocksPlatform::importGPC($_REQUEST['report_type'], 'integer', 0)); // Security if (null == ($active_worker = CerberusApplication::getActiveWorker())) { die($translate->_('common.access_denied')); } $tpl = DevblocksPlatform::getTemplateService(); $tpl->cache_lifetime = "0"; $tpl->assign('path', $this->tpl_path); // import dates from form @($start = DevblocksPlatform::importGPC($_REQUEST['start'], 'string', '')); @($end = DevblocksPlatform::importGPC($_REQUEST['end'], 'string', '')); // use date rang@$sel_worker_id = DevblocksPlatform::importGPC($_REQUEST['worker_id'],'integer',0);e if specified, else use duration prior to now $start_time = 0; $end_time = 0; if (empty($start) && empty($end)) { $start = "Last Monday"; $end = "now"; $start_time = strtotime($start); $end_time = strtotime($end); } else { $start_time = strtotime($start); $end_time = strtotime($end); } if ($start_time === false || $end_time === false) { $start = "Last Monday"; $end = "now"; $start_time = strtotime($start); $end_time = strtotime($end); $tpl->assign('invalidDate', true); } // reload variables in template $tpl->assign('start', $start); $tpl->assign('end', $end); $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); $sources = DAO_TimeTrackingEntry::getSources(); $tpl->assign('sources', $sources); $sql = "SELECT tte.log_date, tte.time_actual_mins, tte.worker_id, tte.notes, "; $sql .= "tte.source_extension_id, tte.source_id, "; $sql .= "tta.name activity_name "; $sql .= "FROM timetracking_entry tte "; $sql .= "INNER JOIN timetracking_activity tta ON tte.activity_id = tta.id "; $sql .= "INNER JOIN worker w ON tte.worker_id = w.id "; $sql .= sprintf("WHERE log_date > %d AND log_date <= %d ", $start_time, $end_time); if ($sel_worker_id) { $sql .= sprintf("AND tte.worker_id = %d ", $sel_worker_id); } // Do Not use Group By it breaks things. // $sql .= "GROUP BY activity_name "; $sql .= "ORDER BY w.last_name, w.first_name, activity_name, w.id, tte.log_date "; // echo $sql; $rs = $db->Execute($sql); $time_entries = array(); $filename = "worker-" . $active_worker->id . ".csv"; $full_filename = getcwd() . '/storage/answernet/' . $filename; if (file_exists($full_filename)) { if (!is_writable($full_filename)) { die("The file: {$full_filename} is not writable"); } } elseif (!is_writable(getcwd() . '/storage/answernet/')) { die("you cannot create files in this directory. Check the permissions"); } //open the file for Writing $fh = fopen($full_filename, "w"); //Lock the file for the write operation flock($fh, LOCK_EX); $label = array("Worker Name", "Ticket No", "Client", "Asset", "Site Name", "Billing Group", "Billing Min", "Sub-Total", "Total", "Date Recorded", "Notes"); fputcsv($fh, $label, ",", "\""); if (is_a($rs, 'ADORecordSet')) { while (!$rs->EOF) { $csv = array(); $custom_fields = array(); $mins = intval($rs->fields['time_actual_mins']); $worker_id = intval($rs->fields['worker_id']); $org_id = intval($rs->fields['org_id']); $activity = $rs->fields['activity_name']; $log_date = intval($rs->fields['log_date']); $notes = $rs->fields['notes']; if (!isset($time_entries[$worker_id])) { $time_entries[$worker_id] = array(); $time_entries[$worker_id]['mins'] = array(); } if (!isset($subtotal)) { $subtotal = array(); $subtotal_activity = $activity; $subtotal['name'] = $workers[$worker_id]->getName(false); $subtotal['source_id'] = ""; $subtotal['client'] = ""; $subtotal['asset'] = ""; $subtotal['sitename'] = ""; $subtotal['activity_name'] = $activity; $subtotal['mins'] = ""; } else { if ($subtotal_activity != $activity) { //Dump Sub Total fputcsv($fh, $subtotal, ",", "\""); $subtotal = array(); $subtotal_activity = $activity; $subtotal['name'] = $workers[$worker_id]->getName(false); $subtotal['source_id'] = ""; $subtotal['client'] = ""; $subtotal['asset'] = ""; $subtotal['sitename'] = ""; $subtotal['activity_name'] = $activity; $subtotal['mins'] = ""; } } if (!isset($total_cm)) { $total_cm = array(); $total_worker_id = $worker_id; $total_cm['name'] = $workers[$worker_id]->getName(false); $total_cm['source_id'] = ""; $total_cm['client'] = ""; $total_cm['asset'] = ""; $total_cm['sitename'] = ""; $total_cm['activity_name'] = ""; $total_cm['mins'] = ""; } else { if ($total_worker_id != $worker_id) { //Dump Total fputcsv($fh, $total_cm, ",", "\""); $total_cm = array(); $total_worker_id = $worker_id; $total_cm['name'] = $workers[$worker_id]->getName(false); $total_cm['source_id'] = ""; $total_cm['client'] = ""; $total_cm['asset'] = ""; $total_cm['sitename'] = ""; $total_cm['activity_name'] = ""; $total_cm['mins'] = ""; } } $csv['name'] = $workers[$worker_id]->getName(false); unset($time_entry); $time_entry['activity_name'] = $activity; $time_entry['mins'] = $mins; $time_entry['log_date'] = $log_date; $time_entry['notes'] = $notes; $time_entry['source_extension_id'] = $rs->fields['source_extension_id']; $time_entry['source_id'] = intval($rs->fields['source_id']); $csv['source_id'] = intval($rs->fields['source_id']); $custom_fields = DAO_CustomFieldValue::getValuesBySourceIds(ChCustomFieldSource_Ticket::ID, $csv['source_id']); if (isset($custom_fields[$csv['source_id']][10])) { $csv['client'] = $custom_fields[$csv['source_id']][10]; } else { $csv['client'] = ""; } if (isset($custom_fields[$csv['source_id']][11])) { $csv['asset'] = $custom_fields[$csv['source_id']][11]; } else { $csv['asset'] = ""; } if (isset($custom_fields[$csv['source_id']][1])) { $csv['sitename'] = $custom_fields[$csv['source_id']][1]; } else { $csv['sitename'] = ""; } $csv['activity_name'] = $activity; $csv['mins'] = $mins; $csv['subtotal'] = ""; $csv['total'] = ""; $csv['log_date'] = date("Y-m-d h:i A", $log_date); $csv['notes'] = $notes; $time_entries[$worker_id]['entries'][] = $time_entry; @($time_entries[$worker_id]['mins'][$activity] = intval($time_entries[$worker_id]['mins'][$activity]) + $mins); @($time_entries[$worker_id]['mins']['total'] = intval($time_entries[$worker_id]['mins']['total']) + $mins); $subtotal['subtotal'] = $time_entries[$worker_id]['mins'][$activity]; $total_cm['subtotal'] = ""; $subtotal['total'] = ""; $total_cm['total'] = $time_entries[$worker_id]['mins']['total']; fputcsv($fh, $csv, ",", "\""); $rs->MoveNext(); } } // print_r($time_entries); $tpl->assign('time_entries', $time_entries); $tpl->assign('report_type', $report_type); $tpl->assign('href_filename', $href_filename); fputcsv($fh, $subtotal, ",", "\""); fputcsv($fh, $total_cm, ",", "\""); fclose($fh); $tpl->display('file:' . $this->tpl_path . '/report_worker_time_html.tpl'); }
function getTimeSpentPlus1ReportAction() { $db = DevblocksPlatform::getDatabaseService(); DevblocksPlatform::getExtensions('timetracking.source', true); // Security if (null == ($active_worker = CerberusApplication::getActiveWorker())) { die($translate->_('common.access_denied')); } $tpl = DevblocksPlatform::getTemplateService(); $tpl->cache_lifetime = "0"; $tpl->assign('path', $this->tpl_path); // import dates from form @($start = DevblocksPlatform::importGPC($_REQUEST['start'], 'string', '')); @($end = DevblocksPlatform::importGPC($_REQUEST['end'], 'string', '')); $start_time = 0; $end_time = 0; if (empty($start) && empty($end)) { $start = "-30 days"; $end = "now"; $start_time = strtotime($start); $end_time = strtotime($end); } else { $start_time = strtotime($start); $end_time = strtotime($end); } if ($start_time === false || $end_time === false) { $start = "-30 days"; $end = "now"; $start_time = strtotime($start); $end_time = strtotime($end); $tpl->assign('invalidDate', true); } $groups = DAO_Group::getAll(); $buckets = DAO_Bucket::getAll(); // reload variables in template $tpl->assign('start', $start); $tpl->assign('end', $end); $sources = DAO_TimeTrackingEntry::getSources(); $tpl->assign('sources', $sources); $sql = "SELECT t.mask, t.id, sum(tte.time_actual_mins) mins, a.email, "; $sql .= "t.subject, t.created_date, t.updated_date, t.is_closed, "; $sql .= "t.is_waiting, t.team_id, t.category_id "; $sql .= "FROM timetracking_entry tte "; $sql .= "INNER JOIN ticket t ON tte.source_id = t.id "; $sql .= "INNER JOIN address a ON t.first_wrote_address_id = a.id "; $sql .= sprintf("WHERE log_date > %d AND log_date <= %d ", $start_time, $end_time); $sql .= "GROUP BY t.id "; $sql .= "ORDER BY t.id, tte.log_date "; // echo $sql; $rs = $db->Execute($sql); $time_entries = array(); $filename = "report-plus1-" . $active_worker->id . ".csv"; $full_filename = getcwd() . '/storage/answernet/' . $filename; if (file_exists($full_filename)) { if (!is_writable($full_filename)) { die("The file: {$full_filename} is not writable"); } } elseif (!is_writable(getcwd() . '/storage/answernet/')) { die("you cannot create files in this directory. Check the permissions"); } //open the file for Writing $fh = fopen($full_filename, "w"); //Lock the file for the write operation flock($fh, LOCK_EX); $label = array("Ticket Mask", "Ticket Number", "Client Name", "Asset Name", "Site Name", "Requestor", "Subject", "Created Date", "Last Updated", "Group", "Bucket", "Status", "Total Min"); fputcsv($fh, $label, ",", "\""); if (is_a($rs, 'ADORecordSet')) { while (!$rs->EOF) { $csv = array(); $custom_fields = array(); $mask = $rs->fields['mask']; $id = intval($rs->fields['id']); $email = $rs->fields['email']; $subject = $rs->fields['subject']; $team_id = intval($rs->fields['team_id']); $category_id = intval($rs->fields['category_id']); $created_date = intval($rs->fields['created_date']); $updated_date = intval($rs->fields['updated_date']); $status = "Open"; if (intval($rs->fields['is_waiting'])) { $status = "Waiting for Reply"; } if (intval($rs->fields['is_closed'])) { $status = "Completed"; } $mins = intval($rs->fields['mins']); if (!isset($time_entries[$id])) { $time_entries[$id] = array(); } $csv['mask'] = $mask; $csv['id'] = $id; unset($time_entry); $time_entry['mask'] = $mask; $custom_fields = DAO_CustomFieldValue::getValuesBySourceIds(ChCustomFieldSource_Ticket::ID, $id); if (isset($custom_fields[$id][10])) { $csv['client'] = $custom_fields[$id][10]; $time_entry['client'] = $custom_fields[$id][10]; } else { $csv['client'] = ""; $time_entry['client'] = ""; } if (isset($custom_fields[$id][11])) { $csv['asset'] = $custom_fields[$id][11]; $time_entry['asset'] = $custom_fields[$id][11]; } else { $csv['asset'] = ""; $time_entry['asset'] = ""; } if (isset($custom_fields[$id][1])) { $csv['sitename'] = $custom_fields[$id][1]; $time_entry['sitename'] = $custom_fields[$id][1]; } else { $csv['sitename'] = ""; $time_entry['sitename'] = ""; } $csv['email'] = $email; $time_entry['email'] = $email; $csv['subject'] = $subject; $time_entry['subject'] = $subject; $csv['created_date'] = date("Y-m-d h:i A", $created_date); $time_entry['created_date'] = $created_date; $csv['updated_date'] = date("Y-m-d h:i A", $updated_date); $time_entry['updated_date'] = $updated_date; $csv['group'] = $groups[$team_id]->name; $time_entry['group'] = $groups[$team_id]->name; if ($category_id) { $csv['bucket'] = $buckets[$category_id]->name; $time_entry['bucket'] = $buckets[$category_id]->name; } else { $csv['bucket'] = 'Inbox'; $time_entry['bucket'] = 'Inbox'; } $csv['status'] = $status; $time_entry['status'] = $status; $csv['mins'] = $mins; $time_entry['mins'] = $mins; $time_entries[$id] = $time_entry; fputcsv($fh, $csv, ",", "\""); $rs->MoveNext(); } } fclose($fh); $tpl->assign('time_entries', $time_entries); $tpl->display('file:' . $this->tpl_path . '/report_plus1_time_html.tpl'); }
function getTimeSpentActivityReportAction() { $db = DevblocksPlatform::getDatabaseService(); $tpl = DevblocksPlatform::getTemplateService(); $tpl->cache_lifetime = "0"; $tpl->assign('path', $this->tpl_path); // import dates from form @($start = DevblocksPlatform::importGPC($_REQUEST['start'], 'string', '')); @($end = DevblocksPlatform::importGPC($_REQUEST['end'], 'string', '')); // use date range if specified, else use duration prior to now $start_time = 0; $end_time = 0; if (empty($start) && empty($end)) { $start = "-30 days"; $end = "now"; $start_time = strtotime($start); $end_time = strtotime($end); } else { $start_time = strtotime($start); $end_time = strtotime($end); } if ($start_time === false || $end_time === false) { $start = "-30 days"; $end = "now"; $start_time = strtotime($start); $end_time = strtotime($end); $tpl->assign('invalidDate', true); } $workers = DAO_Worker::getAll(); $tpl->assign('workers', $workers); $sources = DAO_TimeTrackingEntry::getSources(); $tpl->assign('sources', $sources); // reload variables in template $tpl->assign('start', $start); $tpl->assign('end', $end); $sql = sprintf("SELECT tte.log_date, tte.time_actual_mins, tte.notes, tte.source_extension_id, tte.source_id," . "tta.id activity_id, tta.name activity_name, " . "o.name org_name, tte.worker_id " . "FROM timetracking_entry tte " . "LEFT JOIN timetracking_activity tta ON tte.activity_id = tta.id " . "LEFT JOIN contact_org o ON tte.debit_org_id = o.id " . "WHERE log_date > %d AND log_date <= %d " . "ORDER BY activity_name, log_date ", $start_time, $end_time); //echo $sql; $rs = $db->Execute($sql); $time_entries = array(); if (is_a($rs, 'ADORecordSet')) { while (!$rs->EOF) { $mins = intval($rs->fields['time_actual_mins']); $activity = $rs->fields['activity_name']; $log_date = intval($rs->fields['log_date']); $activity_id = intval($rs->fields['activity_id']); $notes = $rs->fields['notes']; $worker_id = intval($rs->fields['worker_id']); $org_name = $rs->fields['org_name']; if (!isset($time_entries[$activity_id])) { $time_entries[$activity_id] = array(); } if (!isset($time_entries[$activity_id]['entries'])) { $time_entries[$activity_id]['entries'] = array(); } unset($time_entry); $time_entry['mins'] = $mins; $time_entry['log_date'] = $log_date; $time_entry['notes'] = $notes; $time_entry['worker_name'] = $workers[$worker_id]->getName(); $time_entry['org_name'] = $org_name; $time_entry['source_extension_id'] = $rs->fields['source_extension_id']; $time_entry['source_id'] = intval($rs->fields['source_id']); $time_entries[$activity_id]['entries'][] = $time_entry; @($time_entries[$activity_id]['total_mins'] = intval($time_entries[$activity_id]['total_mins']) + $mins); @($time_entries[$activity_id]['activity_name'] = $activity); $rs->MoveNext(); } } //print_r($time_entries); $tpl->assign('time_entries', $time_entries); $tpl->display('file:' . $this->tpl_path . '/reports/time_spent_activity/html.tpl'); }