',
);
$job_list->init($user->uid);
/*
$sql = "SELECT j.job_id, j.job_name FROM {qms_jobs} j, {qms_punch_clock} p
WHERE j.active = 1 AND j.job_id = p.job_id AND p.employee_user_id = :uid";
$result = db_query($sql, array(':uid' => $user->uid));
if ( $result->rowCount() == 0 ) {
drupal_set_message('Unable to locate active user punch clock records', 'status');
return;
}
$job_list = array(0 => '- Select -');
foreach($result as $row) {
$job_list[$row->job_id] = $row->job_name;
}
*/
}
// this could be the current user id or zero since a user has not yet been selected
$form['user_id'] = array(
'#markup' => '
', ); $form['job'] = array( '#type' => 'select', '#title' => t('Select a Job'), '#options' => $job_list->get(), '#attributes' => array( 'id' => 'qms-job-select', 'class' => array('qms-select'), 'qms-url' => url('reports/time_worked_report') ), //'#default_value' => $job_id, '#validated' => True, '#suffix' => '
',
);
// this could be the current user id or zero since a user has not yet been selected
$form['report_div'] = array(
'#markup' => '',
);
//--------------------- FORM ACTIONS (Buttons) -----------------------
$form['actions'] = array( '#type' => 'actions');
$form['actions']['submit'] = array(
'#type' => 'submit',
'#value' => t('Done'),
'#name' => 'submit',
'#attributes' => array('class' => array('qms-btn-submit')),
'#submit' => array('report_form_submit'),
);
$form['actions']['clear'] = array(
'#type' => 'button',
'#value' => t('Clear'),
'#name' => 'clear',
'#attributes' => array('class' => array('qms-clear', 'qms-btn-extra')),
);
global $base_url;
$form['actions']['print'] = array(
'#type' => 'button',
'#value' => t('Print'),
'#name' => 'print',
'#attributes' => array('class' => array('qms-print', 'qms-btn-extra')),
'#suffix' => '' . $user_id . '
',
);
$form['page_title'] = array(
'#markup' => '' .
'
' .
drupal_get_title() .
'
' .
'', ); $form['job'] = array( '#type' => 'select', '#title' => t('Select a Job'), '#options' => $job_list->get(), '#attributes' => array( 'id' => 'qms-job-select', 'class' => array('qms-select'), 'qms-url' => url('reports/time_worked_report') ), //'#default_value' => $job_id, '#validated' => True, '#suffix' => '
',
);
// storage area for dynamic dialog element
$form['popup_dialog'] = array(
'#markup' => '',
);
return $form;
}
function get_employee_jobs() {
$user_id = (int)$_POST['user_id'];
$job_list = new ReportJobList;
if ( !$user_id ) {
return; // user not selected
}
//$job_list[0] = '- Select -';
if ( $user_id == -1 ) {
// GET ALL active jobs (ALL USERS)
$job_list->init();
}
else {
// Get Jobs list for the specified user
$job_list->init($user_id);
}
return drupal_json_output($job_list->get());
}
/*
* get_time_worked_report()
*
*/
function get_time_worked_report() {
$user_id = (int)$_POST['user_id'];
$job_id = (int)$_POST['job_id'];
if ( ($user_id == 0) || ($job_id == 0) ) {
return '';
}
$sql = "SELECT clock_in, clock_out, time_worked FROM {qms_punch_clock} p WHERE job_id = :jid ";
$tokens[':jid'] = $job_id;
if ( $user_id != -1 ) {
// specific user, not ALL users
$sql.= " AND employee_user_id = :uid ";
$tokens[':uid'] = $user_id;
}
$sql.= " ORDER BY clock_in ASC";
$results = db_query($sql, $tokens);
$table_rows = array();
$table_header = array(
array( 'data' => 'Date', 'class' => 'qms-time-worked-date'),
array( 'data' => 'Clock-In', 'class' => 'qms-time-worked-clocked'),
array( 'data' => 'Clock-Out', 'class' => 'qms-time-worked-clocked'),
array( 'data' => 'Time Worked (Hours)', 'class' => 'qms-time-worked-hours'),
);
$i = 0;
$curr_date = '';
$div = ' ';
$subtotal = 0.00;
$grand_total = 0.00;
foreach ($results as $row) {
// time_worked stored in seconds. Convert to hours (2 decimal places)
$new_hours = round(($row->time_worked / (60 * 60) ), 2);
$new_date = _st_format_date($row->clock_in, 'custom', 'm-d-Y');
if ( $curr_date == $new_date) {
$new_date = ''; // don't list it in the table
}
else {
$curr_date = $new_date;
if ( $i > 0 ) { // don't do this for the first row
// print prev date total
$table_rows[] = array( ' ', ' ',
array('data' => 'Total:', 'class' => array('qms-summary-cell')),
array('data' => sprintf("%0.2f", $subtotal), 'class' => array('qms-summary-cell')),
);
$subtotal = 0.00;
}
$table_rows[] = array(array('data' => $div, 'colspan' => '4', 'class' => array('qms-divrow')));
}
$subtotal += $new_hours;
$grand_total += $new_hours;
$row_data = array(
array('data' => $new_date, 'class' => array('qms-time-worked-section')),
_st_format_date($row->clock_in, 'short'),
_st_format_date($row->clock_out, 'short'),
//($row->time_worked / (60 * 60) ),
array('data' => sprintf("%0.2f", $new_hours), 'class' => array('qms-time-worked-hours')),
);
$table_rows[] = array('data' => $row_data);
$i++;
}
if ( $i == 0 ) {
$table_rows[] = array('data' => array('None', '', '', ''));
}
else {
// print final subtotal and grand total
$table_rows[] = array( ' ', ' ',
array('data' => 'Total:', 'class' => array('qms-summary-cell')),
array('data' => sprintf("%0.2f", $subtotal), 'class' => array('qms-summary-cell')),
);
$table_rows[] = array(array('data' => $div, 'colspan' => '4', 'class' => array('qms-divrow')));
$table_rows[] = array( ' ', ' ',
array('data' => 'Grand Total:', 'class' => array('qms-summary-cell')),
array('data' => sprintf("%0.2f", $grand_total), 'class' => array('qms-summary-cell')),
);
}
$content = '';
$content .= '
';
die($content);
}
/*
* timesheet_export()
*
* exports employee timesheet data as a csv, compatible with QuickBooks
*
* @IN $user_id | may be 0 or equal to the employee user id
* @Retn csv file for download
*/
/*
function timesheet_export_tab($user_id = -1) {
try {
$col_delim = "\t";
$sql = "SELECT p.clock_in, p.clock_out, p.time_worked,
u.uid, e.full_name, u.name, c.customer, j.job_name
FROM qms_punch_clock p
INNER JOIN qms_jobs j on p.job_id = j.job_id
INNER JOIN qms_simulators s on j.simulator_id = s.simulator_id
INNER JOIN users u on p.employee_user_id = u.uid
LEFT JOIN qms_employees e on p.employee_user_id = e.user_id
LEFT JOIN qms_customers c on s.customer_id = c.customer_id
WHERE j.active = 1 ";
$tokens = array();
if ( $user_id == -1 ) {
// all employees, broken up by employee and weeks
$sql .= " ORDER BY e.full_name ASC, u.name ASC, p.clock_in ASC";
} else {
// specific user, not ALL users
$sql.= " AND p.employee_user_id = :uid ";
$tokens[':uid'] = $user_id;
$sql .= " ORDER BY p.clock_in ASC";
}
$results = db_query($sql, $tokens);
$punchclock_list = $results->fetchAll();
$current_user_id = 0;
$current_begin_range = '';
$content = '';
$total_columns = 12;
$header_format = array(
'job' => '"'. t('Customer').':'.t('Job').'"',
'service' => '"'. t('Service').'"',
'payroll' => '"'. t('Payroll Item'). '"',
'notes' => '"'. t('Notes').'"',
'date1' => '',
'date2' => '',
'date3' => '',
'date4' => '',
'date5' => '',
'date6' => '',
'date7' => '',
);
$header = array();
if (count($punchclock_list) > 0) {
// build CSV from records
foreach ($punchclock_list as $pc) {
// print the name for the next user
if ($pc->uid != $current_user_id) {
$name = $pc->name;
$content .= "\n\n";
$content .= '"'.( !empty($pc->full_name) ? $pc->full_name : $pc->name ).'"';
$current_user_id = $pc->uid;
}
// get the week range for this record.
$dt1 = new DateTime();
$dt1->setTimestamp($pc->clock_in);
if ($dt1->format('l') != 'Sunday') {
$dt1->modify('last Sunday');
}
$begin_range = $dt1->format('M j');
$jdt = new DateTime();
$jdt->setTimestamp($pc->clock_in);
$jdtf = $jdt->format('M j');
// print the new range and time event header
if ($begin_range != $current_begin_range) {
$begin_range_text = $dt1->format('M d Y');
$dt2 = new DateTime();
$dt2->setTimestamp($pc->clock_in);
$dt2->modify('next Saturday');
//$end_range = $dt2->getTimestamp;
$end_range_text = $dt2->format('M d Y');
$content .= "\n";
$content .= '"'. t('Week of') . ' ' .
$begin_range_text . ' ' . t('to') . ' ' . $end_range_text . '"' . "\n";
$header = $header_format;
for ($i = 1; $i <= 7; $i++) {
$header['date'.$i] = $dt1->format('M j');
$dt1->modify('+1 day');
}
$content .= implode($col_delim, $header) . "\n";
$current_begin_range = $begin_range;
}
// print the punch clock data
$content .= '"'. $pc->customer . ':' . $pc->job_name . '"' . $col_delim;
$content .= $col_delim; // 'Database' ?
$content .= $col_delim; // Payroll Item
$content .= $col_delim; // Notes
for ($i = 1; $i <= 7; $i++) {
$job_date = $jdt->format('M j');
if ( $header['date'.$i] == $job_date ) {
// convert seconds to hours
$content .= sprintf("%0.2f", ($pc->time_worked / 3600)) . $col_delim;
} else {
$content .= $col_delim;
}
}
// convert seconds to hours
$content .= "\n";
}
}
// export to csv
$curr_date = new DateTime();
$file_timestamp = $curr_date->format('mdY.His');
$export_uri = 'private://timesheets';
if ( !file_prepare_directory($export_uri, FILE_CREATE_DIRECTORY)) {
drupal_set_message(t('Error: Export Directory'));
return;
}
$file_basename = 'timesheet_export';
$dir = file_stream_wrapper_get_instance_by_uri($export_uri)->realpath();
if ($user_id == -1) {
$name = t('all');
}
$filename = "$dir/$file_basename.$name.txt";
file_put_contents($filename, $content);
if (file_exists($filename)) {
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($filename));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($filename));
ob_clean();
flush();
readfile($filename);
echo l('Return', '/reports/time_worked');
}
} catch (Exception $e) {
watchdog('sabreQMS', 'ReportJobList::init() ' . $e->getMessage(), array(), WATCHDOG_ERROR);
}
}
*/
/*
* timesheet_export()
*
* exports employee timesheet data as a csv, compatible with QuickBooks
*
* @IN $user_id | may be 0 or equal to the employee user id
* @Retn csv file for download
*/
/*
function timesheet_export($user_id = -1) {
try {
$col_delim = "\t";
$file_ext = "iif";
$eol = "\r\n";
$sql = "SELECT p.clock_in, p.clock_out, p.time_worked,
u.uid, e.full_name, u.name, c.customer, j.job_name
FROM qms_punch_clock p
INNER JOIN qms_jobs j on p.job_id = j.job_id
INNER JOIN qms_simulators s on j.simulator_id = s.simulator_id
INNER JOIN users u on p.employee_user_id = u.uid
LEFT JOIN qms_employees e on p.employee_user_id = e.user_id
LEFT JOIN qms_customers c on s.customer_id = c.customer_id
WHERE j.active = 1
AND p.clock_in >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 day))";
$tokens = array();
if ( $user_id == -1 ) {
// all employees, broken up by employee and weeks
$sql .= " ORDER BY e.full_name ASC, u.name ASC, p.clock_in ASC";
} else {
// specific user, not ALL users
$sql.= " AND p.employee_user_id = :uid ";
$tokens[':uid'] = $user_id;
$sql .= " ORDER BY p.clock_in ASC";
}
$results = db_query($sql, $tokens);
$punchclock_list = $results->fetchAll();
$today = new DateTime();
$name = '';
// Quickbooks IIF format -- build the report header
// $content = implode($col_delim, array(
// '!TIMERHDR',
// 'VER',
// 'REL',
// 'COMPANYNAME',
// 'IMPORTEDBEFORE',
// 'FROMTIMER',
// 'COMPANYCREATETIME',
// )) . $eol;
// $content .= implode($col_delim, array(
// 'TIMERHDR',
// '16',
// '0',
// 'Sabre Updates LLC',
// 'N',
// 'N',
// $today->getTimestamp(),
// )) . $eol;
$content .= implode($col_delim, array(
'!HDR',
'PROD',
'VER',
'REL',
'IIFVER',
'DATE',
'TIME',
'ACCNTNT',
'ACCNTNTSPLITTIME',
)) . $eol;
$content .= implode($col_delim, array(
'HDR',
'QuickBooks Enterprise',
'16',
'0',
'1',
$today->format('m/d/Y'),
$today->getTimestamp(),
'N',
'0',
)) . $eol;
$content .= implode($col_delim, array(
'!TIMEACT',
'DATE',
'JOB',
'EMP',
'ITEM',
'PITEM',
'DURATION',
'PROJ',
'NOTE',
'BILLINGSTATUS',
)) . $eol;
// QuickBooks -- export timesheet data in IIF format
if (count($punchclock_list) > 0) {
foreach ($punchclock_list as $pc) {
if (($user_id > 0) && empty($name)) {
$name = $pc->name;
}
$jdt = new DateTime();
$jdt->setTimestamp($pc->clock_in);
$content .= implode($col_delim, array(
'TIMEACT',
$jdt->format('m/d/y'),
$pc->customer . ':' . $pc->job_name,
( !empty($pc->full_name) ? $pc->full_name : $pc->name ),
'Service',
'',
sprintf("%0.2f", ($pc->time_worked / 3600)),
'',
'',
'1',
)) . $eol;
}
// Export to QuickBooks format
$file_timestamp = $today->format('mdY.His');
$export_uri = 'private://timesheets';
if ( !file_prepare_directory($export_uri, FILE_CREATE_DIRECTORY)) {
drupal_set_message(t('Error: Export Directory'));
return;
}
$file_basename = 'timesheet_export';
$dir = file_stream_wrapper_get_instance_by_uri($export_uri)->realpath();
if ($user_id == -1) {
$name = t('all');
}
$filename = "$dir/$file_basename.$name.$file_ext";
file_put_contents($filename, $content);
if (file_exists($filename)) {
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($filename));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($filename));
ob_clean();
flush();
readfile($filename);
echo l('Return', '/reports/time_worked');
}
} else {
drupal_goto('reports/time_worked');
}
} catch (Exception $e) {
watchdog('sabreQMS', 'ReportJobList::init() ' . $e->getMessage(), array(), WATCHDOG_ERROR);
}
}
*/
/*
* QMS_alerts()
*
* returns: Discrepancy Early Warning list & QTG expiring list
*
*/
function QMS_alerts() {
// $print_page_heading =
// '' . _get_user_name($user_id) . '
'; $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= '' .
// '
' .
// drupal_get_title() .
// '
' .
// ''; // $content = (!$called_by_pager) ? $print_page_heading : '
'; drupal_set_title(t('Alerts')); $vars = array( 'filter' => False, 'pager' => False, ); unset($_SESSION['dew_selected']); unset($_SESSION['qtg_selected']); $_SESSION['return_url'] = 'reports/alerts'; $content = '
