' . t('Open Sessions') . ' - ' . $sl->getName($sim_list[$s]->simulator_id) . ': ' . $sim_list[$s]->schedule_name . '
'; $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= ''; } return $content; } catch(Exception $e) { watchdog(SCH_SCHEDULER, '_get_open_time_list() ' . $e->getMessage(), array(), WATCHDOG_ERROR); } } /* * report_customer_detail() * * handles both the normal customer detail report & the customer cut_bill report */ function report_customer_detail($mode = 'DETAIL', $customer_id = 0, $simulator_id = 0, $view_month = '', $view_year = '') { if ( !user_access('view manage menu') ) { drupal_set_message( t('Unauthorized Access. Permission is required.')); drupal_goto(''); return ''; } // if not specified, use the current month and year if ( $view_month == '' ) { $view_month = date('F'); } if ( $view_year == '' ) { $view_year = date('Y'); } drupal_add_js(drupal_get_path('module', 'sabreScheduler') . '/js/sabreScheduler.report.detail.js'); global $base_url; global $theme; $content = ''; $sub_title = ''; if ( $customer_id ) { // printing -- heading (logo & title) to top each grid page if ( 'DETAIL' == $mode ) { $sub_title = t('Customer Schedule Detail Report'); } else { // CUTBILL $sub_title = t('Customer Cut/Billable Report'); } $print_page_heading = '
' .
//'
' .
'
';
$content .= $print_page_heading;
}
$form_elems = drupal_get_form('customer_detail_form', $mode, $customer_id, $simulator_id, $view_month, $view_year);
$content .= render($form_elems);
if ( $customer_id ) {
$content .= _get_customer_detail_report($mode, $customer_id, $simulator_id, $view_month, $view_year);
}
return $content;
}
/*
* customer_detail_form()
*
*/
function customer_detail_form($form, $form_state, $mode, $customer_id, $simulator_id, $view_month = '', $view_year = '') {
global $base_url;
$month_list = new MonthList();
$year_list = new YearList();
$width = ($customer_id ? '100%' : '95%');
$goto_url = ( 'DETAIL' == $mode ) ? 'reports/customer/detail' : 'reports/customer/cutbill';
$month_array = $month_list->get(); // includes '- select -' option, need to remove this to calc next/prev month
array_shift($month_array);
$href_prev_month = _st_array_key_relative($month_array, $view_month, -1);
$href_prev_year = ((int)$view_year);
if ( ($href_prev_month === False) ) {
$href_prev_month = 'December';
$href_prev_year--;
}
$href_next_month = _st_array_key_relative($month_array, $view_month, 1);
$href_next_year = ((int)$view_year);
if ( ($href_next_month === False) || ($href_next_month == 'January') ) {
$href_next_month = 'January';
$href_next_year++;
}
$form['report_box'] = array(
'#type' => 'fieldset',
'#title' => t('Settings'),
'#attributes' => array('id' => 'sch-report-box')
);
$form['report_box']['form_table_begin'] = array(
'#markup' => '' .
'' . drupal_get_title() . '
' .
'' . $sub_title . '
' .
'' .
'
',
);
$form['message_box'] = array(
'#markup' => '',
);
return $form;
}
/*
* _get_customer_detail_report()
*
*/
function _get_customer_detail_report($mode, $customer_id, $simulator_id, $view_month = '', $view_year = '') {
try {
$content = '';
$now = _st_modify_timestamp(0);
$begin_date = 'first day of ' . $view_month . ' ' . $view_year . ' 00:00:00';
$begin_date_ts = _st_modify_timestamp($now, $begin_date);
$end_date = 'last day of ' . $view_month . ' ' . $view_year . ' 23:59:59';
$end_date_ts = _st_modify_timestamp($now, $end_date);
$cutbill = ('CUTBILL' == $mode) ? 1 : 0;
$assignments = _get_customer_assignments($customer_id, $simulator_id, $begin_date_ts, $end_date_ts, $cutbill);
//$content = 'Found ' . count($assignments) . ' records';
$table_header = array(
array( 'data' => t('Date'), 'class' => array('sch-tbl-assignment-date2') ),
array( 'data' => t('Day'), 'class' => array('sch-tbl-assignment-dow') ),
array( 'data' => t('Start'), 'class' => array('sch-tbl-assignment-begins') ),
array( 'data' => t('Finish'), 'class' => array('sch-tbl-assignment-ends') ),
array( 'data' => t('Total Time'), 'class' => array('sch-tbl-assignment-time') ),
array( 'data' => t('Simulator'), 'class' => array('sch-tbl-assignment-simulator') ),
);
if ( $cutbill ) {
$table_header[] = array( 'data' => t('Cancelled'), 'class' => array('sch-tbl-assignment-cut') );
}
$table_rows = array();
$i = 0;
if (count($assignments)) {
foreach($assignments as $assign) {
$ihours = 0;
$imins = 0;
$diff = $assign->end_timestamp - $assign->begin_timestamp;
_st_calc_seconds_to_hours_minutes($diff, $ihours, $imins);
$row_data = array(
_st_format_system_date($assign->begin_timestamp, 'm/d/Y'),
_st_format_system_date($assign->begin_timestamp, 'D'),
_st_left($assign->begin_time_actual, 2) . ':' . _st_right($assign->begin_time_actual, 2),
_st_left($assign->end_time_actual, 2) . ':' . _st_right($assign->end_time_actual, 2),
sprintf('%02d:%02d', $ihours, $imins),
_st_format_sim_name($assign->sim_name, $assign->device_id_internal),
);
if ( $cutbill ) {
$row_data[] = _st_format_system_date($assign->updated_date, 'm/d/Y');
}
$table_rows[] = array('data' => $row_data);
$i++;
}
}
$content = '| ' , '#suffix' => ' | ', ); $customer_list = new CustomerList(); $form['report_box']['customer'] = array( '#type' => 'select', '#title' => t('Customer'), '#required' => True, '#options' => ($customer_id ? $customer_list->getAll(CustomerList::NO_SELECT_OPT) : $customer_list->getAll()), '#default_value' => ($customer_id ? $customer_id : 0), '#validated' => True, '#attributes' => array('id' => 'sch-customer-select', 'class' => array('qms-select')), '#prefix' => '' , '#suffix' => ' | ', ); $form['report_box']['form_table_markup'] = array( '#markup' => ' | |||
' .
'' .
' ' .
' | ',
);
$form['report_box']['view_month'] = array(
'#type' => 'select',
'#title' => t('View Month'),
'#options' => $month_list->get(),
'#default_value' => $view_month,
'#required' => False,
'#attributes' => array('id' => 'sch-view-month-select'),
'#prefix' => '', '#suffix' => ' | ', ); $form['report_box']['view_year'] = array( '#type' => 'select', '#title' => t('View Year'), '#options' => $year_list->get(), '#default_value' => (int)$view_year, '#required' => False, '#attributes' => array('id' => 'sch-view-year-select'), '#prefix' => '', '#suffix' => ' | ', ); $form['report_box']['go_to'] = array( '#type' => 'button', '#default_value' => ( $customer_id ? t('GO') : t('Submit') ), '#attributes' => array('class' => array('sch-btn'), 'id' => 'sch-btn-submit'), '#prefix' => '', '#suffix' => ' | ', ); if ( $customer_id ) { $form['report_box']['view_today'] = array( '#type' => 'button', '#default_value' => t('Today'), '#attributes' => array('class' => array('sch-btn'), 'id' => 'sch-btn-view-today'), '#prefix' => '', '#suffix' => ' | ', ); } $form['report_box']['go_right'] = array( '#markup' => '' .
'' .
' ' .
' | ',
);
$form['report_box']['href_base'] = array(
'#type' => 'textfield',
'#default_value' => url($goto_url),
'#attributes' => array('class' => array('qms-hidden'),
'id' => 'sch-schedule-grid-href-base'),
//'#suffix' => '',
);
$form['report_box']['form_table_end'] = array(
'#markup' => '
';
$content .= theme( 'table', array(
'header' => $table_header,
'rows' => $table_rows,
'empty' => t('None'),
));
$content .= '
';
return $content;
}
catch(Exception $e) {
watchdog(SCH_SCHEDULER, '_get_customer_detail_report() ' . $e->getMessage(), array(), WATCHDOG_ERROR);
}
}
/*
* report_summary_month()
*
*/
function report_monthly_summary($view_month = '', $view_year = '', $show_form_only = True) {
if ( !user_access('view manage menu') ) {
drupal_set_message( t('Unauthorized Access. Permission is required.'));
drupal_goto('');
return '';
}
drupal_add_js(drupal_get_path('module', 'sabreTools') . '/js/sabreTools.lib.js');
drupal_add_js(drupal_get_path('module', 'sabreScheduler') . '/js/sabreScheduler.report.summary.js');
global $base_url;
global $theme;
$content = '';
// if not specified, use the current month and year
if ( $view_month == '' ) {
$view_month = date('F');
}
if ( $view_year == '' ) {
$view_year = date('Y');
}
$sub_title = t($view_month) . ' ' . $view_year;
$print_page_heading =
'' .
//'' .
'
' .
'' . drupal_get_title() . '
' .
'' . $sub_title . '
' .
''; $content .= (!$show_form_only) ? $print_page_heading : '
'; $form_elems = drupal_get_form('monthly_summary_form', $view_month, $view_year, $show_form_only); $content .= render($form_elems); if ( !$show_form_only ) { $content .= _get_monthly_summary_report($view_month, $view_year); } return $content; } /* * monthly_summary_form() * */ function monthly_summary_form($form, $form_state, $view_month = '', $view_year = '', $show_form_only = True) { global $base_url; $month_list = new MonthList(); $year_list = new YearList(); $goto_url = 'reports/summary/monthly'; $hidden = ''; if ( $show_form_only ) { $hidden = ' style="visibility:hidden;" '; } $month_array = $month_list->get(); // includes '- select -' option, need to remove this to calc next/prev month array_shift($month_array); $href_prev_month = _st_array_key_relative($month_array, $view_month, -1); $href_prev_year = ((int)$view_year); if ( ($href_prev_month === False) ) { $href_prev_month = 'December'; $href_prev_year--; } $href_next_month = _st_array_key_relative($month_array, $view_month, 1); $href_next_year = ((int)$view_year); if ( ($href_next_month === False) || ($href_next_month == 'January') ) { $href_next_month = 'January'; $href_next_year++; } $form['report_box'] = array( '#type' => 'fieldset', '#title' => t('Settings'), '#attributes' => array('id' => 'sch-report-box') ); $form['report_box']['form_table_begin'] = array( '#markup' => '
' .
'
',
);
$form['report_box']['href_base'] = array(
'#type' => 'textfield',
'#default_value' => url($goto_url),
'#attributes' => array('class' => array('qms-hidden'),
'id' => 'sch-report-href-base'),
);
return $form;
}
/*
* _get_monthly_summary_report()
*
* retrieves a list of hours
* grouped by simulator
* grouped by customer
* within the date range
* dates are expected in unix timestamp format
* returns: array of objects
*/
function _get_monthly_summary_report($view_month, $view_year) {
try {
$content = '';
$now = _st_modify_timestamp(0);
if ( !strlen($view_month) || !strlen($view_year) ) { return $content; }
$begin_date = 'first day of ' . $view_month . ' ' . $view_year . ' 00:00:00';
$begin_date_ts = _st_modify_timestamp($now, $begin_date);
$end_date = 'last day of ' . $view_month . ' ' . $view_year . ' 23:59:59';
$end_date_ts = _st_modify_timestamp($now, $end_date);
$table_header = array(
array( 'data' => t('Simulator'), 'class' => array('sch-tbl-summary-sims') ),
array( 'data' => t('Customer'), 'class' => array('sch-tbl-summary-customers') ),
array( 'data' => t('Hours'), 'class' => array('sch-tbl-summary-hours') ),
);
$table_rows = array();
$i = 0;
// include cut_bill'ed assignments for this report
$sql = "SELECT sim.simulator_id, sim.sim_name, sim.device_id_internal,
c.customer_id, c.customer, SUM(a.total_time) as `sum_time`
FROM {sch_assignments} a, {sch_schedules} sch, {sch_customers} c, {sch_simulators} sim
WHERE a.schedule_id = sch.schedule_id
AND a.customer_id = c.customer_id
AND sch.simulator_id = sim.simulator_id
AND a.begin_timestamp >= :bdr
AND a.begin_timestamp <= :edr
AND a.cut_bill = 0
GROUP BY sch.simulator_id, a.customer_id
ORDER BY sim.sim_name, sim.device_id_internal, c.customer";
$results = db_query($sql, array(':bdr' => $begin_date_ts, ':edr' => $end_date_ts ));
$last_sim_id = 0;
$last_sim_name = '';
$subtotal = 0;
$grand_total = 0;
$i = 0;
if ( $results->rowCount() ) {
foreach($results as $row) {
if ( $i && ($last_sim_id != $row->simulator_id)) {
$row_data = array(
'',
array('data' => t('Total for') . ' ' . $last_sim_name,
'class' => array('sch-tbl-summary-subtotal')),
array('data' => sprintf('%0.1f', $subtotal / (60 * 60)),
'class' => array('sch-tbl-summary-subtotal')),
);
$table_rows[] = array('data' => $row_data);
$subtotal = 0;
}
$sim_name = _st_format_sim_name($row->sim_name, $row->device_id_internal);
$row_data = array(
// only print the sim name if it's different than the one before
array('data' => (($last_sim_id != $row->simulator_id) ? $sim_name : ''),
'class' => array('sch-tbl-summary-sims')) ,
$row->customer,
array('data' => sprintf('%0.1f', $row->sum_time / (60 * 60)),
'class' => array('sch-tbl-summary-hours')),
);
$table_rows[] = array('data' => $row_data);
$subtotal += $row->sum_time;
$grand_total += $row->sum_time;
$last_sim_id = $row->simulator_id;
$last_sim_name = $sim_name;
$i++;
}
if ( $i ) {
$row_data = array(
'',
array('data' => t('Total for') . ' ' . $last_sim_name,
'class' => array('sch-tbl-summary-subtotal')),
array('data' => sprintf('%0.1f', $subtotal / (60 * 60)),
'class' => array('sch-tbl-summary-subtotal')),
);
$table_rows[] = array('data' => $row_data);
}
}
$row_data = array(
'',
array('data' => t('GRAND TOTAL'), 'class' => array('sch-tbl-summary-grand-total')),
array('data' => sprintf('%0.1f', $grand_total / (60 * 60)), 'class' => array('sch-tbl-summary-grand-total')),
);
$table_rows[] = array('data' => $row_data);
$content = '| ' .
'' .
'' .
' | ',
);
$form['report_box']['view_month'] = array(
'#type' => 'select',
'#title' => t('View Month'),
'#options' => $month_list->get(),
'#default_value' => $view_month,
'#required' => False,
'#attributes' => array('id' => 'sch-view-month-select'),
'#prefix' => '', '#suffix' => ' | ', ); $form['report_box']['view_year'] = array( '#type' => 'select', '#title' => t('View Year'), '#options' => $year_list->get(), '#default_value' => (int)$view_year, '#required' => False, '#attributes' => array('id' => 'sch-view-year-select'), '#prefix' => '', '#suffix' => ' | ', ); $form['report_box']['go_to'] = array( '#type' => 'button', '#default_value' => ( $show_form_only ? t('Submit') : t('GO') ), '#attributes' => array('class' => array('sch-btn'), 'id' => 'sch-btn-submit'), '#prefix' => '', '#suffix' => ' | ', ); if ( !$show_form_only ) { $form['report_box']['view_today'] = array( '#type' => 'button', '#default_value' => t('Today'), '#attributes' => array('class' => array('sch-btn'), 'id' => 'sch-btn-view-today'), '#prefix' => '', '#suffix' => ' | ', ); } $form['report_box']['go_right'] = array( '#markup' => '' .
'' .
'' .
' | ',
);
$form['report_box']['form_table_end'] = array(
'#markup' => '
';
$content .= theme( 'table', array(
'header' => $table_header,
'rows' => $table_rows,
'empty' => t('None'),
));
$content .= '
';
return $content;
}
catch (Exception $e) {
watchdog(SCH_SCHEDULER, '_get_summary_report() ' . $e->getMessage(), array(), WATCHDOG_ERROR);
}
}
/*
* report_schedule_by_month_excel_export ()
*
* Derived from 'schedule_grid_display' that builds the main schedule grid
*
* IN: $simulator_id,
* $customer_id,
* $view_month ['January|February|March|...],
* $view_year [4-digit year (string)]
* RETURN: Schedule Grid display, single page, by month
*/
/*
function report_schedule_by_month_excel_export($simulator_id, $customer_id = 0,
$view_month = '', $view_year = '') {
if (!$simulator_id) {
drupal_set_message(t('Error: Invalid simulator id.'));
drupal_goto('schedules');
return;
}
drupal_add_js('(function($) {
Drupal.behaviors.schViewPrint = {
attach: function(context, settings) {
$(".qms-print", context).click(function() {
window.print();
return false;
});
}
}
}(jQuery));', 'inline');
// if not specified, use the current month and year
if ($view_month == '') {
$view_month = date('F');
}
if ($view_year == '') {
$view_year = date('Y');
}
_build_schedule_report_excel_export($simulator_id, $customer_id,
$view_month, $view_year);
//$form_elems = drupal_get_form('schedule_excel_report_form');
//$content .= render($form_elems);
}
* */
/*
* report_schedule_excel_export_form()
*
*
*/
function report_schedule_excel_export_form($form, $form_state,
$simulator_id, $schedule_id, $customer_id, $view_month = '', $view_year = '') {
$month_list = new MonthList();
$year_list = new YearList();
// if not specified, use the current month and year
if ($view_month == '') {
$view_month = date('F');
}
if ($view_year == '') {
$view_year = date('Y');
}
$destination = drupal_get_destination();
$goto_url = $destination['destination'];
if( !strlen($goto_url) || ($goto_url == current_path()) ) {
$goto_url = 'schedule/grid/' . $simulator_id . '/' . $schedule_id . '/' . $customer_id . '/' .
$view_month . '/' . $view_year;
}
$form['settings'] = array(
'#type' => 'fieldset',
'#title' => t('Export Settings'),
'#attributes' => array('id' => 'sch-report-settings-div'),
);
$form['settings']['begin_table'] = array (
'#markup' => '| ', '#suffix' => ' | ', ); $form['settings']['from_year'] = array( '#type' => 'select', '#title' => t('Year'), '#options' => $year_list->get(YearList::NO_SELECT_OPT), '#default_value' => (int) $view_year, '#required' => False, '#attributes' => array('id' => 'sch-from-year-select'), '#prefix' => '', '#suffix' => ' | ', ); $form['settings']['table_middle'] = array ( '#markup' => '', ); $form['settings']['to_month'] = array( '#type' => 'select', '#title' => t('To Month'), '#options' => $month_list->get(MonthList::NO_SELECT_OPT), '#default_value' => $view_month, '#required' => False, '#attributes' => array('id' => 'sch-to-month-select'), '#prefix' => ' | ', '#suffix' => ' | ', ); $form['settings']['to_year'] = array( '#type' => 'select', '#title' => t('Year'), '#options' => $year_list->get(YearList::NO_SELECT_OPT), '#default_value' => (int) $view_year, '#required' => False, '#attributes' => array('id' => 'sch-to-year-select'), '#prefix' => '', '#suffix' => ' | ', ); $form['settings']['end_table'] = array ( '#markup' => '
';
$content .= theme( 'table', array(
'header' => $table_headers,
'rows' => $table_rows,
'empty' => t('None'),
));
$content .= '
';
return $content;
}
catch (Exception $e) {
watchdog(SCH_SCHEDULER, '_get_excel_export_files_table() ' . $e->getMessage(),
array(), WATCHDOG_ERROR);
}
}
/*
* discrepancy_file_download()
*
* implements hook_file_download()
*/
function excel_file_download($file_id) {
try {
$file_name = '';
$sql = "SELECT file_name " .
"FROM {sch_export_files} " .
"WHERE file_id = :fid";
$results = db_query($sql, array(':fid' => $file_id));
if ( !$results->rowCount() ) {
drupal_set_message(t('Oops! Something went wrong. Unable to find file.'));
return;
}
$file_name = $results->fetchField();
// use the local file path, not the file URI which is saved in the table
$private_path = variable_get('file_private_path', QMS_PRIVATE_FILES_DIR ) . '/';
$file_path = $private_path . SCH_FILE_EXPORT_EXCEL_DIR . '/' . $file_name;
_st_return_requested_file($file_path);
}
catch (Exception $e) {
watchdog(SCH_SCHEDULER, 'excel_file_download() ' . $e->getMessage(),
array(), WATCHDOG_ERROR);
}
}
/*
* report_schedule_by_month ()
*
* Derived from 'schedule_grid_display' that builds the main schedule grid
*
* IN: $simulator_id,
* $customer_id,
* $view_month ['January|February|March|...],
* $view_year [4-digit year (string)]
* RETURN: Schedule Grid display, single page, by month
*/
function report_schedule_by_month($simulator_id, $customer_id = 0, $view_month = '', $view_year = '') {
if (!$simulator_id) {
drupal_set_message(t('Error: Invalid simulator id.'));
drupal_goto('schedules');
return;
}
drupal_add_js('(function($) {
Drupal.behaviors.schViewPrint = {
attach: function(context, settings) {
$(".qms-print", context).click(function() {
window.print();
return false;
});
}
}
}(jQuery));', 'inline');
// if not specified, use the current month and year
if ($view_month == '') {
$view_month = date('F');
}
if ($view_year == '') {
$view_year = date('Y');
}
$content = _build_schedule_report($simulator_id, $customer_id, $view_month, $view_year);
$form_elems = drupal_get_form('report_form');
$content .= render($form_elems);
return $content;
}
/*
* report_form()
*
* Appends buttons to bottom of page and returns to $_GET['destination'] setting
* when 'Done' button clicked
*
*/
function report_form($form, $form_state) {
$form['dummy'] = array(
'#markup' => '',
);
$form['actions'] = array( '#type' => 'actions');
$form['actions']['submit'] = array(
'#type' => 'submit',
'#value' => t('Done'),
'#attributes' => array('id' => 'qms-btn-done'), // close screen, go to home page
);
$form['actions']['print'] = array(
'#type' => 'button',
'#value' => t('Print'),
'#name' => 'print',
'#attributes' => array('class' => array('qms-print', 'qms-btn-extra')),
);
return $form;
}
/*
* status_display_form_submit()
*
*/
function report_form_submit($form, $form_state) {
$destination = drupal_get_destination();
$goto_url = $destination['destination'];
if( !strlen($goto_url) || ($goto_url == current_path()) ) {
$goto_url = '';
}
drupal_goto($goto_url);
}
/*
* _build_schedule_report()
*
* if no month or year specified, returns the report for the current month/year
*
* IN: $simulator_id,
* $customer_id,
* $view_month ['January|February|March|...],
* $view_year [4-digit year (string)]
* RETURN: Schedule Grid display, single page, by month
*/
function _build_schedule_report($simulator_id, $customer_id = 0, $view_month = '', $view_year = '') {
try {
// schedule display
global $base_url;
global $theme;
// get the schedule_sessions for this simulator schedule
$sessions = array();
$assignments = array();
$exceptions = array();
$schedule_id = 0;
$session_id = 0;
$schedule_id = 0;
$date_count = 0;
$record_count = 0;
$years = '';
$months = '';
$date_row = array(); // only 1 date row for this, but this array is for each table cell
$dow_row = array();
$prev_year = '';
$prev_month = '';
$sess_ptr = 0;
$year_text = '';
$month_text = '';
$month_and_year = '';
$n_view_month = 0;
$n_view_year = '';
$content = '';
$timezone = '';
// $sessions array keyed by session_id
$sessions = _get_sessions(0, $simulator_id, True);
if ( !count($sessions) ) {
$content = '';
$content .= '
[' . t('Schedule Unavailable') . ']'; $content .= '
';
return $content;
}
$time_blocks = _split_sessions_into_hours($sessions, True);
foreach($sessions as $sess) {
$assignments[$session_id] = array();
if (!$schedule_id) { $schedule_id = $sess['schedule_id']; }
if (empty($timezone)) { $timezone = $sess['timezone']; }
}
$now = _st_modify_timestamp();
/*----------------- Set Grid Date Range -------------------*/
$bd_ts = _st_modify_timestamp($now, 'first day of ' . $view_month . ' ' . $view_year);
$ed_ts = _st_modify_timestamp($now, 'last day of ' . $view_month . ' ' . $view_year);
$grid_begins = _format_timestamp($bd_ts, '00:00:00', $timezone);
$grid_ends = _format_timestamp($ed_ts, '23:59:59', $timezone);
/*----------------- Query: Calendar Date ------------------*/
$results_cal = _get_calendar_range($grid_begins, $grid_ends);
$date_count = 0;
$date_group = 0;
$prev_date = 0;
foreach ($results_cal as $cal) {
// determine what year should be displayed for this date
if ( !$date_count ) {
$year_text = _st_format_system_date($cal->calendar_date, 'Y');
$month_text = _st_format_system_date($cal->calendar_date, 'M');
$month_and_year = '[' . t('Schedule Unavailable') . ']'; $content .= '
' . $month_text . ' ' . $year_text . '
';
$n_view_month = date('n', $cal->calendar_date);
$n_view_year = date('Y', $cal->calendar_date);
}
if ($prev_date <> $cal->calendar_date) {
$date_count++;
$dow_row [$date_count] = _st_format_system_date($cal->calendar_date, 'D');
$date_row[$date_count] = _st_format_system_date($cal->calendar_date, 'n/j');
$prev_date = $cal->calendar_date;
}
}
/*------------------- Query: Assignments -------------------*/
$assignments = _get_assignments($grid_begins, $grid_ends, 0, $simulator_id);
/*------------------- Query: Exceptions -------------------*/
$exceptions = _get_exceptions($grid_begins, $grid_ends, $timezone, $simulator_id);
$return_url = 'schedule/grid/' . $simulator_id . '/' . $schedule_id . '/' . $customer_id . '/' .
$view_month . '/' . $view_year;
$content = '';
$content .= '';
//------------------- output: DATE Headings -----------------
// this prints a blank first cell at the top left corner
$content .= '
'; return $content; } catch (Exception $e) { watchdog(SCH_SCHEDULER, '_build_schedule_report() ' . $e->getMessage(), array(), WATCHDOG_ERROR); } } /* * _split_sessions_into_hours($sessions) * * returns an array of hour time_blocks * $sort_by_hour == True, orders by start hour of time block * $sort_by_hour == False, default sort by session_id * */ function _split_sessions_into_hours($sessions, $sort_by_hour = False) { $time_blocks = array(); foreach($sessions as $sess) { $session_id = $sess['schedule_session_id']; // break session time down into hourly segments $et = (int)$sess['end_time']; $bt = (int)$sess['begin_time']; $iend_time = $et; $beg_hour = $bt; $end_hour = 0; $i = 0; if ( $bt > $et ) { // this session overlaps into next day, accommodate that $iend_time += 2400; } // walk through the session time range to break it into blocks while ($beg_hour <= ($iend_time - 100)) { // end on the session end_time if it doesn't break evenly into hours $end_hour = (($beg_hour + 100) < $iend_time) ? ($beg_hour + 100) : ($iend_time); // index the timeblock array by the beg_hour & store the rest $tb = new stdClass(); $tb->session_id = $session_id; $tb->begin_hour = (($bt > $et) && ($beg_hour >= 2400)) ? $beg_hour - 2400 : $beg_hour; //$tb->end_hour = (($bt > $et) && ($end_hour > 2400)) ? $end_hour - 2400 : $end_hour; if ( ($bt > $et) && ($end_hour > 2400) ) { if ( ($end_hour > 2400) && ($end_hour < 2500) ) { $tb->end_hour = 2400; $beg_hour = 2400; } else { $tb->end_hour = $end_hour - 2400; $beg_hour = $end_hour; } } else { $tb->end_hour = $end_hour; $beg_hour = $end_hour; } if ($sort_by_hour) { $time_blocks[$tb->begin_hour] = $tb; } else { $time_blocks[$tb->session_id][$tb->begin_hour] = $tb; } $i++; if ( $i == 10 ) { $debug = 1; break; } } } // sort by the array key ksort($time_blocks); return $time_blocks; } /* * _check_for_assignment() * * Not really a validator * For the provided assignment array & time range, does an assignment occupy the time range? * * IN: assignment array * $begin_ts, $end_ts (time range to check for assignment) */ function _check_for_assignment($assignments = array(), $begin_ts = 0, $end_ts = 0, &$total_time = 0) { // expects the timestamp range and assignment array have been provided // doesn't retrieve them // assignment array passed are only assignment items for a specific day & session. $assign_text = ''; $total_time = 0; if ( !$begin_ts || !$end_ts || !count($assignments) ) { return $assign_text; } // bump the seconds by 1 in either direction to avoid problems with perceived overlap (by seconds) $begin_range_ts = $begin_ts + 1; $end_range_ts = $end_ts - 1; // CHECK ASSIGNMENTS // step through all of the assignments provided foreach ($assignments as $assign) { $total_time = _date_diff_seconds($assign->begin_timestamp, $assign->end_timestamp); //check the assignment begin & end times against the timerange if ( ((int)$assign->begin_timestamp <= $begin_range_ts) && ($end_range_ts <= (int)$assign->end_timestamp) ) { // assignment falls in the middle of the time range $assign_text = $assign->customer_code; } else if ( ($begin_range_ts <= (int)$assign->begin_timestamp ) && ((int)$assign->end_timestamp <= $end_range_ts ) ) { // assignment completely contained by the time range $assign_text = $assign->customer_code; } else if ( ($begin_range_ts <= (int)$assign->begin_timestamp) && ((int)$assign->begin_timestamp < $end_range_ts ) && ( $end_range_ts <= $assign->end_timestamp ) ) { // assignment overlaps middle to end of session //$assign_text = $assign->customer_code; $assign_text = ' /'; } else if ( ((int)$assign->begin_timestamp <= $begin_range_ts) && ($begin_range_ts < (int)$assign->end_timestamp ) && ( $assign->end_timestamp <= $end_range_ts) ) { // assignment overlaps beginning to middle of session //$assign_text = $assign->customer_code; $assign_text = ' /'; } } // End - Foreach Loop return $assign_text; } /* * _check_for_assignment_excel() * * Not really a validator * For the provided assignment array & time range, does an assignment occupy the time range? * Customized for Excel Export within the context of Excel cell merging and session times * * IN: assignment array * $begin_ts, $end_ts (time range to check for assignment) * OUT: $start_ts (when within the time range the assignment begins * $total_time (duration of the assignment within the time range) * RETURN: $assign_text (customer code if an assignment exists for this time range) */ function _check_for_assignment_excel($assignments = array(), $customer_id = 0, $begin_ts = 0, $end_ts = 0, &$start_ts = 0, &$total_time = 0) { // expects the timestamp range and assignment array have been provided // doesn't retrieve them // assignment array passed are only assignment items for a specific day & session. $assign_text = ''; $start_ts = 0; $total_time = 0; if ( !$begin_ts || !$end_ts || !count($assignments) ) { return $assign_text; } // bump the seconds by 1 in either direction to avoid problems with perceived overlap (by seconds) $begin_range_ts = $begin_ts + 1; $end_range_ts = $end_ts - 1; // CHECK ASSIGNMENTS // step through all of the assignments provided foreach ($assignments as $assign) { //check the assignment begin & end times against the timerange if ( ((int)$assign->begin_timestamp <= $begin_range_ts) && ($end_range_ts <= (int)$assign->end_timestamp) ) { // assignment falls in the middle of the time range if ( !$customer_id || ($customer_id && ($customer_id == $assign->customer_id)) ) { $assign_text = $assign->customer_code; } else { $assign_text = '***'; } $start_ts = $begin_ts; $total_time = round(_date_diff_seconds($begin_ts,$end_ts)); break; } else if ( ($begin_range_ts <= (int)$assign->begin_timestamp ) && ((int)$assign->end_timestamp <= $end_range_ts ) ) { // assignment completely contained by the time range if ( !$customer_id || ($customer_id && ($customer_id == $assign->customer_id)) ) { $assign_text = $assign->customer_code; } else { $assign_text = '***'; } $start_ts = (int)$assign->begin_timestamp; $total_time = round(_date_diff_seconds($start_ts, $assign->end_timestamp)); if ( $start_ts == $begin_ts ) { break; } } else if ( ($begin_range_ts <= (int)$assign->begin_timestamp) && ((int)$assign->begin_timestamp < $end_range_ts ) && ( $end_range_ts <= $assign->end_timestamp ) ) { // assignment overlaps middle to end of time range if ( !$customer_id || ($customer_id && ($customer_id == $assign->customer_id)) ) { $assign_text = $assign->customer_code; } else { $assign_text = '***'; } //$assign_text = ' /'; $start_ts = (int)$assign->begin_timestamp; $total_time = round(_date_diff_seconds((int)$assign->begin_timestamp, $end_ts)); } else if ( ((int)$assign->begin_timestamp <= $begin_range_ts) && ($begin_range_ts < (int)$assign->end_timestamp ) && ( $assign->end_timestamp <= $end_range_ts) ) { // assignment overlaps beginning to middle of time range if ( !$customer_id || ($customer_id && ($customer_id == $assign->customer_id)) ) { $assign_text = $assign->customer_code; } else { $assign_text = '***'; } //$assign_text = ' /'; $start_ts = (int)$assign->begin_timestamp; $total_time = round(_date_diff_seconds($begin_ts, (int)$assign->end_timestamp)); break; } } // End - Foreach Loop return $assign_text; } /* * _check_for_exception() * * Not for validation * For the provided exception array & time range, does an exception occupy the time range? * * IN: exceptions array * $begin_ts, $end_ts (time range to check for assignment) * RETURN: True (if exception) * False (no exception) */ function _check_for_exception($exceptions = array(), $begin_ts = 0, $end_ts = 0, &$total_time = 0) { // we are dealing with hour time ranges for this if ( !$begin_ts || !$end_ts || !count($exceptions) ) { return false; } // bump the seconds by 1 in either direction to avoid problems with perceived overlap (by seconds) $begin_range_ts = $begin_ts + 1; $end_range_ts = $end_ts - 1; // CHECK EXCEPTIONS $has_except = False; $total_time = 0; // step through all of the assignments provided $i = 0; foreach ($exceptions as $except) { $total_time = _date_diff_seconds($except->begin_timestamp, $except->end_timestamp); //check the begin & end times if ( ($except->begin_timestamp <= $begin_range_ts) && ($end_range_ts <= $except->end_timestamp) ) { // $except falls in the middle of the time range if ( !$except->allow_override ) { $has_except = True; } } else if ( ($begin_range_ts <= $except->begin_timestamp ) && ($except->end_timestamp <= $end_range_ts ) ) { // $except completely contained by the time range if ( !$except->allow_override ) { $has_except = True; } } else if ( ($begin_range_ts <= $except->begin_timestamp) && ($except->begin_timestamp < $end_range_ts ) && ( $end_range_ts <= $except->end_timestamp ) ) { // $except overlaps middle to end of session if ( !$except->allow_override ) { $percent = round(($end_ts - $except->begin_timestamp) / ($end_ts - $begin_ts), 2) * 100; if ( $percent >= 25 ) { $has_except = True; } } } else if ( ($except->begin_timestamp <= $begin_range_ts) && ($begin_range_ts < $except->end_timestamp ) && ( $except->end_timestamp <= $end_range_ts) ) { // $except overlaps beginning to middle of session if ( !$except->allow_override ) { $percent = round(($except->end_timestamp - $begin_ts) / ($end_ts - $begin_ts), 2) * 100; if ( $percent >= 25 ) { $has_except = True; } } } $i++; } // End - Foreach Loop return $has_except; } /* * _check_for_exception_excel() * * Not for validation * For the provided exception array & time range, does an exception occupy the time range? * Customized for the Excel Exported Report where the grid is defined in hour intervals, but * cells will be merged for full sessions * * IN: exceptions array * $begin_ts, $end_ts (time range of session) * OUT: $start_ts (when during the session the exception actual begins, * may be the beginning or later in the session time range) * $total_time (duration of the exception within the session block * RETURN: True (if exception) * False (no exception) */ function _check_for_exception_excel($exceptions = array(), $begin_ts = 0, $end_ts = 0, &$start_ts = 0, &$total_time = 0) { // we are dealing with hour time ranges for this if ( !$begin_ts || !$end_ts || !count($exceptions) ) { return false; } // bump the seconds by 1 in either direction to avoid problems with perceived overlap (by seconds) $begin_range_ts = $begin_ts + 1; $end_range_ts = $end_ts - 1; // CHECK EXCEPTIONS $has_except = False; $start_ts = 0; $total_time = 0; // step through all of the assignments provided foreach ($exceptions as $except) { //check the begin & end times if ( ($except->begin_timestamp <= $begin_range_ts) && ($end_range_ts <= $except->end_timestamp) ) { // time range is completely overlapped by the exception if ( !$except->allow_override ) { $has_except = True; $start_ts = $begin_range_ts; $total_time = round(_date_diff_seconds($begin_ts, $end_ts)); } } else if ( ($begin_range_ts <= $except->begin_timestamp ) && ($except->end_timestamp <= $end_range_ts ) ) { // $except completely contained by the time range if ( !$except->allow_override ) { $has_except = True; $start_ts = $except->begin_timestamp; $total_time = round(_date_diff_seconds($except->begin_timestamp, $except->end_timestamp)); } } else if ( ($begin_range_ts <= $except->begin_timestamp) && ($except->begin_timestamp < $end_range_ts ) && ( $end_range_ts <= $except->end_timestamp ) ) { // $except overlaps middle to end of session if ( !$except->allow_override ) { $percent = round(($end_range_ts - $except->begin_timestamp) / ($end_range_ts - $begin_range_ts), 2) * 100; if ( $percent >= 10 ) { $has_except = True; $start_ts = $except->begin_timestamp; $total_time = round(_date_diff_seconds($except->begin_timestamp, $end_ts)); } } } else if ( ($except->begin_timestamp <= $begin_range_ts) && ($begin_range_ts < $except->end_timestamp ) && ( $except->end_timestamp <= $end_range_ts) ) { // $except overlaps beginning to middle of session if ( !$except->allow_override ) { $percent = round(($except->end_timestamp - $begin_range_ts ) / ($end_range_ts - $begin_range_ts), 2) * 100; if ( $percent >= 10 ) { $has_except = True; $start_ts = $begin_range_ts; $total_time = round(_date_diff_seconds($begin_ts, $except->end_timestamp)); } } } } // End - Foreach Loop return $has_except; } /* * report_elogs() */ function report_elogs($customer_id = 0, $report_type = 0, $begin_date = '', $end_date = '', $show_form_only = True) { if ( user_access('view manage menu') == FALSE ) { drupal_set_message( t('Unauthorized: Permission is required'), 'status'); return ''; } if ( '0' == $begin_date ) { $begin_date = ''; } if ( '0' == $end_date ) { $end_date = ''; } $subtitle = ''; $year = date('Y'); if ( !strlen($begin_date) && !strlen($end_date )) { $subtitle = $year; } else if ( !strlen($begin_date) ) { $ed = _st_right($end_date, 5); $ed = str_replace('-', '/', $ed) . '/' . $year; $subtitle = '1/1/' . $year . ' - ' . $ed; } else if ( !strlen($end_date) ) { $bd = _st_right($begin_date, 5); $bd = str_replace('-', '/', $bd) . '/' . $year; $subtitle = $bd . ' - ' . '12/31/' . $year; } $content = ''; $print_page_heading = '
' . $return_url . '
';
//----------------------- BUILD the SCHEDULE Table ---------------------------------
// OUTPUT: Month and Year subheading
$content .= '' . $month_and_year . '
'; // BEGIN TABLE $content .= '| ' . $year_text . ' | '; foreach($dow_row as $dow) { $content .= '' . $dow . ' | '; } $content .= '||
|---|---|---|---|
| ' . $month_text . ' | '; foreach($date_row as $date) { $content .= '' . $date . ' | '; } $content .= '||
| ' . sprintf('%02d:%02d - %02d:%02d', $tb->begin_hour/100, $tb->begin_hour%100, $tb->end_hour/100, $tb->end_hour%100) . ' | '; for ($day = 1; $day <= $date_count; $day++) { $blocked = False; $assign_text = ''; $begin_ts = _format_timestamp_parts($n_view_year, $n_view_month, $day, $s_begin_hour, $timezone); $end_ts = _format_timestamp_parts($n_view_year, $n_view_month, (($tb->begin_hour > $tb->end_hour)? $day+1 : $day), $s_end_hour, $timezone); if ( isset($exceptions[$day]) ) { $blocked = _check_for_exception($exceptions[$day], $begin_ts, $end_ts); } if ( isset($assignments[$tb->session_id][$day]) ) { $assign_text = _check_for_assignment($assignments[$tb->session_id][$day], $begin_ts, $end_ts); } if ( strlen($assign_text) ) { $content .= '' . $assign_text . ' | '; } else if ($blocked) { $content .= '' . ' ***' . ' | '; } else { // open $content .= ''; } } // for ($day) loop $content .= ' |
'; return $content; } catch (Exception $e) { watchdog(SCH_SCHEDULER, '_build_schedule_report() ' . $e->getMessage(), array(), WATCHDOG_ERROR); } } /* * _split_sessions_into_hours($sessions) * * returns an array of hour time_blocks * $sort_by_hour == True, orders by start hour of time block * $sort_by_hour == False, default sort by session_id * */ function _split_sessions_into_hours($sessions, $sort_by_hour = False) { $time_blocks = array(); foreach($sessions as $sess) { $session_id = $sess['schedule_session_id']; // break session time down into hourly segments $et = (int)$sess['end_time']; $bt = (int)$sess['begin_time']; $iend_time = $et; $beg_hour = $bt; $end_hour = 0; $i = 0; if ( $bt > $et ) { // this session overlaps into next day, accommodate that $iend_time += 2400; } // walk through the session time range to break it into blocks while ($beg_hour <= ($iend_time - 100)) { // end on the session end_time if it doesn't break evenly into hours $end_hour = (($beg_hour + 100) < $iend_time) ? ($beg_hour + 100) : ($iend_time); // index the timeblock array by the beg_hour & store the rest $tb = new stdClass(); $tb->session_id = $session_id; $tb->begin_hour = (($bt > $et) && ($beg_hour >= 2400)) ? $beg_hour - 2400 : $beg_hour; //$tb->end_hour = (($bt > $et) && ($end_hour > 2400)) ? $end_hour - 2400 : $end_hour; if ( ($bt > $et) && ($end_hour > 2400) ) { if ( ($end_hour > 2400) && ($end_hour < 2500) ) { $tb->end_hour = 2400; $beg_hour = 2400; } else { $tb->end_hour = $end_hour - 2400; $beg_hour = $end_hour; } } else { $tb->end_hour = $end_hour; $beg_hour = $end_hour; } if ($sort_by_hour) { $time_blocks[$tb->begin_hour] = $tb; } else { $time_blocks[$tb->session_id][$tb->begin_hour] = $tb; } $i++; if ( $i == 10 ) { $debug = 1; break; } } } // sort by the array key ksort($time_blocks); return $time_blocks; } /* * _check_for_assignment() * * Not really a validator * For the provided assignment array & time range, does an assignment occupy the time range? * * IN: assignment array * $begin_ts, $end_ts (time range to check for assignment) */ function _check_for_assignment($assignments = array(), $begin_ts = 0, $end_ts = 0, &$total_time = 0) { // expects the timestamp range and assignment array have been provided // doesn't retrieve them // assignment array passed are only assignment items for a specific day & session. $assign_text = ''; $total_time = 0; if ( !$begin_ts || !$end_ts || !count($assignments) ) { return $assign_text; } // bump the seconds by 1 in either direction to avoid problems with perceived overlap (by seconds) $begin_range_ts = $begin_ts + 1; $end_range_ts = $end_ts - 1; // CHECK ASSIGNMENTS // step through all of the assignments provided foreach ($assignments as $assign) { $total_time = _date_diff_seconds($assign->begin_timestamp, $assign->end_timestamp); //check the assignment begin & end times against the timerange if ( ((int)$assign->begin_timestamp <= $begin_range_ts) && ($end_range_ts <= (int)$assign->end_timestamp) ) { // assignment falls in the middle of the time range $assign_text = $assign->customer_code; } else if ( ($begin_range_ts <= (int)$assign->begin_timestamp ) && ((int)$assign->end_timestamp <= $end_range_ts ) ) { // assignment completely contained by the time range $assign_text = $assign->customer_code; } else if ( ($begin_range_ts <= (int)$assign->begin_timestamp) && ((int)$assign->begin_timestamp < $end_range_ts ) && ( $end_range_ts <= $assign->end_timestamp ) ) { // assignment overlaps middle to end of session //$assign_text = $assign->customer_code; $assign_text = ' /'; } else if ( ((int)$assign->begin_timestamp <= $begin_range_ts) && ($begin_range_ts < (int)$assign->end_timestamp ) && ( $assign->end_timestamp <= $end_range_ts) ) { // assignment overlaps beginning to middle of session //$assign_text = $assign->customer_code; $assign_text = ' /'; } } // End - Foreach Loop return $assign_text; } /* * _check_for_assignment_excel() * * Not really a validator * For the provided assignment array & time range, does an assignment occupy the time range? * Customized for Excel Export within the context of Excel cell merging and session times * * IN: assignment array * $begin_ts, $end_ts (time range to check for assignment) * OUT: $start_ts (when within the time range the assignment begins * $total_time (duration of the assignment within the time range) * RETURN: $assign_text (customer code if an assignment exists for this time range) */ function _check_for_assignment_excel($assignments = array(), $customer_id = 0, $begin_ts = 0, $end_ts = 0, &$start_ts = 0, &$total_time = 0) { // expects the timestamp range and assignment array have been provided // doesn't retrieve them // assignment array passed are only assignment items for a specific day & session. $assign_text = ''; $start_ts = 0; $total_time = 0; if ( !$begin_ts || !$end_ts || !count($assignments) ) { return $assign_text; } // bump the seconds by 1 in either direction to avoid problems with perceived overlap (by seconds) $begin_range_ts = $begin_ts + 1; $end_range_ts = $end_ts - 1; // CHECK ASSIGNMENTS // step through all of the assignments provided foreach ($assignments as $assign) { //check the assignment begin & end times against the timerange if ( ((int)$assign->begin_timestamp <= $begin_range_ts) && ($end_range_ts <= (int)$assign->end_timestamp) ) { // assignment falls in the middle of the time range if ( !$customer_id || ($customer_id && ($customer_id == $assign->customer_id)) ) { $assign_text = $assign->customer_code; } else { $assign_text = '***'; } $start_ts = $begin_ts; $total_time = round(_date_diff_seconds($begin_ts,$end_ts)); break; } else if ( ($begin_range_ts <= (int)$assign->begin_timestamp ) && ((int)$assign->end_timestamp <= $end_range_ts ) ) { // assignment completely contained by the time range if ( !$customer_id || ($customer_id && ($customer_id == $assign->customer_id)) ) { $assign_text = $assign->customer_code; } else { $assign_text = '***'; } $start_ts = (int)$assign->begin_timestamp; $total_time = round(_date_diff_seconds($start_ts, $assign->end_timestamp)); if ( $start_ts == $begin_ts ) { break; } } else if ( ($begin_range_ts <= (int)$assign->begin_timestamp) && ((int)$assign->begin_timestamp < $end_range_ts ) && ( $end_range_ts <= $assign->end_timestamp ) ) { // assignment overlaps middle to end of time range if ( !$customer_id || ($customer_id && ($customer_id == $assign->customer_id)) ) { $assign_text = $assign->customer_code; } else { $assign_text = '***'; } //$assign_text = ' /'; $start_ts = (int)$assign->begin_timestamp; $total_time = round(_date_diff_seconds((int)$assign->begin_timestamp, $end_ts)); } else if ( ((int)$assign->begin_timestamp <= $begin_range_ts) && ($begin_range_ts < (int)$assign->end_timestamp ) && ( $assign->end_timestamp <= $end_range_ts) ) { // assignment overlaps beginning to middle of time range if ( !$customer_id || ($customer_id && ($customer_id == $assign->customer_id)) ) { $assign_text = $assign->customer_code; } else { $assign_text = '***'; } //$assign_text = ' /'; $start_ts = (int)$assign->begin_timestamp; $total_time = round(_date_diff_seconds($begin_ts, (int)$assign->end_timestamp)); break; } } // End - Foreach Loop return $assign_text; } /* * _check_for_exception() * * Not for validation * For the provided exception array & time range, does an exception occupy the time range? * * IN: exceptions array * $begin_ts, $end_ts (time range to check for assignment) * RETURN: True (if exception) * False (no exception) */ function _check_for_exception($exceptions = array(), $begin_ts = 0, $end_ts = 0, &$total_time = 0) { // we are dealing with hour time ranges for this if ( !$begin_ts || !$end_ts || !count($exceptions) ) { return false; } // bump the seconds by 1 in either direction to avoid problems with perceived overlap (by seconds) $begin_range_ts = $begin_ts + 1; $end_range_ts = $end_ts - 1; // CHECK EXCEPTIONS $has_except = False; $total_time = 0; // step through all of the assignments provided $i = 0; foreach ($exceptions as $except) { $total_time = _date_diff_seconds($except->begin_timestamp, $except->end_timestamp); //check the begin & end times if ( ($except->begin_timestamp <= $begin_range_ts) && ($end_range_ts <= $except->end_timestamp) ) { // $except falls in the middle of the time range if ( !$except->allow_override ) { $has_except = True; } } else if ( ($begin_range_ts <= $except->begin_timestamp ) && ($except->end_timestamp <= $end_range_ts ) ) { // $except completely contained by the time range if ( !$except->allow_override ) { $has_except = True; } } else if ( ($begin_range_ts <= $except->begin_timestamp) && ($except->begin_timestamp < $end_range_ts ) && ( $end_range_ts <= $except->end_timestamp ) ) { // $except overlaps middle to end of session if ( !$except->allow_override ) { $percent = round(($end_ts - $except->begin_timestamp) / ($end_ts - $begin_ts), 2) * 100; if ( $percent >= 25 ) { $has_except = True; } } } else if ( ($except->begin_timestamp <= $begin_range_ts) && ($begin_range_ts < $except->end_timestamp ) && ( $except->end_timestamp <= $end_range_ts) ) { // $except overlaps beginning to middle of session if ( !$except->allow_override ) { $percent = round(($except->end_timestamp - $begin_ts) / ($end_ts - $begin_ts), 2) * 100; if ( $percent >= 25 ) { $has_except = True; } } } $i++; } // End - Foreach Loop return $has_except; } /* * _check_for_exception_excel() * * Not for validation * For the provided exception array & time range, does an exception occupy the time range? * Customized for the Excel Exported Report where the grid is defined in hour intervals, but * cells will be merged for full sessions * * IN: exceptions array * $begin_ts, $end_ts (time range of session) * OUT: $start_ts (when during the session the exception actual begins, * may be the beginning or later in the session time range) * $total_time (duration of the exception within the session block * RETURN: True (if exception) * False (no exception) */ function _check_for_exception_excel($exceptions = array(), $begin_ts = 0, $end_ts = 0, &$start_ts = 0, &$total_time = 0) { // we are dealing with hour time ranges for this if ( !$begin_ts || !$end_ts || !count($exceptions) ) { return false; } // bump the seconds by 1 in either direction to avoid problems with perceived overlap (by seconds) $begin_range_ts = $begin_ts + 1; $end_range_ts = $end_ts - 1; // CHECK EXCEPTIONS $has_except = False; $start_ts = 0; $total_time = 0; // step through all of the assignments provided foreach ($exceptions as $except) { //check the begin & end times if ( ($except->begin_timestamp <= $begin_range_ts) && ($end_range_ts <= $except->end_timestamp) ) { // time range is completely overlapped by the exception if ( !$except->allow_override ) { $has_except = True; $start_ts = $begin_range_ts; $total_time = round(_date_diff_seconds($begin_ts, $end_ts)); } } else if ( ($begin_range_ts <= $except->begin_timestamp ) && ($except->end_timestamp <= $end_range_ts ) ) { // $except completely contained by the time range if ( !$except->allow_override ) { $has_except = True; $start_ts = $except->begin_timestamp; $total_time = round(_date_diff_seconds($except->begin_timestamp, $except->end_timestamp)); } } else if ( ($begin_range_ts <= $except->begin_timestamp) && ($except->begin_timestamp < $end_range_ts ) && ( $end_range_ts <= $except->end_timestamp ) ) { // $except overlaps middle to end of session if ( !$except->allow_override ) { $percent = round(($end_range_ts - $except->begin_timestamp) / ($end_range_ts - $begin_range_ts), 2) * 100; if ( $percent >= 10 ) { $has_except = True; $start_ts = $except->begin_timestamp; $total_time = round(_date_diff_seconds($except->begin_timestamp, $end_ts)); } } } else if ( ($except->begin_timestamp <= $begin_range_ts) && ($begin_range_ts < $except->end_timestamp ) && ( $except->end_timestamp <= $end_range_ts) ) { // $except overlaps beginning to middle of session if ( !$except->allow_override ) { $percent = round(($except->end_timestamp - $begin_range_ts ) / ($end_range_ts - $begin_range_ts), 2) * 100; if ( $percent >= 10 ) { $has_except = True; $start_ts = $begin_range_ts; $total_time = round(_date_diff_seconds($begin_ts, $except->end_timestamp)); } } } } // End - Foreach Loop return $has_except; } /* * report_elogs() */ function report_elogs($customer_id = 0, $report_type = 0, $begin_date = '', $end_date = '', $show_form_only = True) { if ( user_access('view manage menu') == FALSE ) { drupal_set_message( t('Unauthorized: Permission is required'), 'status'); return ''; } if ( '0' == $begin_date ) { $begin_date = ''; } if ( '0' == $end_date ) { $end_date = ''; } $subtitle = ''; $year = date('Y'); if ( !strlen($begin_date) && !strlen($end_date )) { $subtitle = $year; } else if ( !strlen($begin_date) ) { $ed = _st_right($end_date, 5); $ed = str_replace('-', '/', $ed) . '/' . $year; $subtitle = '1/1/' . $year . ' - ' . $ed; } else if ( !strlen($end_date) ) { $bd = _st_right($begin_date, 5); $bd = str_replace('-', '/', $bd) . '/' . $year; $subtitle = $bd . ' - ' . '12/31/' . $year; } $content = ''; $print_page_heading = '
' .
'
';
$content .= (!$show_form_only) ? $print_page_heading : '';
$form_elems = drupal_get_form('elog_report_form', $customer_id,
$report_type,
$begin_date,
$end_date);
$content .= render($form_elems);
if ( !$show_form_only ) {
// if no customer or date_range provided, return all elogs for the current year only.
if ( 1 == $report_type ) {
// elog session report
$content .= _get_elog_session_report_list($customer_id,
$begin_date,
$end_date);
}
else if ( 2 == $report_type ) {
$content .= _get_elog_quality_report_list($customer_id,
$begin_date,
$end_date);
}
}
return $content;
}
/*
* elog_report_form()
* display form to get user input of search parameters
* IN: $args -- array of arguments passed into the report
* 'customer_id'
* 'report_type'
* 'begin_date'
* 'end_date'
* 'show_form_only' => True | False
*/
function elog_report_form($form, $form_state,
$customer_id = 0, $report_type = 0,
$begin_date = '', $end_date = '') {
drupal_add_library('system','ui.dialog');
drupal_add_library('system','ui.datepicker');
drupal_add_js(drupal_get_path('module', 'sabreScheduler') .
'/js/sabreScheduler.report.elogs.js');
//------------ BUILD SEARCH FORM ------------------
global $base_url;
//$simulator_list = new SimulatorList();
$customer_list = new CustomerList();
//$session_list = new SessionNameList();
$form['search'] = array(
'#type' => 'fieldset',
'#title' => t('Report Settings'),
'#collapsible' => True,
'#collapsed' => False,
'#attributes' => array('id' => array('sch-report-settings-div'),
'style' => 'padding-left:20px;'),
);
$form['search']['customer'] = array(
'#type' => 'select',
'#title' => t('Customer'),
'#options' => $customer_list->getAll(CustomerList::INCL_ALL),
'#default_value' => $customer_id,
'#attributes' => array('class' => array('qms-select'),
'id' => 'sch-customer-list'),
'#prefix' => '' . drupal_get_title() . '
' .
'' . $subtitle . '
' .
'',
//'#suffix' => '
',
);
$report_type_options = array(
'0' => '- ' . t('Select') . ' -',
'1' => t('Session Report'),
'2' => t('Quality Report'),
);
$form['search']['report_type'] = array(
'#type' => 'select',
'#title' => t('Report Type'),
'#options' => $report_type_options,
'#default_value' => $report_type,
'#attributes' => array('class' => array('qms-select'),
'id' => 'sch-report-type-list'),
//'#prefix' => '',
'#suffix' => '
',
);
// $form['search']['search_date_label'] = array(
// '#markup' => '',
// //'
', ); //-------------------------------------------- // Adds a simple submit button that refreshes the form and clears its contents // -- this is the default behavior for forms. $form['search']['actions'] = array('#type' => 'actions'); $form['search']['actions']['submit'] = array( '#type' => 'button', '#value' => t('Submit'), '#name' => 'submit', '#attributes' => array('id' => 'sch-btn-submit', 'sch-url' => url('reports/elogs'), 'style' => 'font-weight:bold;'), ); $form['search']['actions']['clear'] = array( '#type' => 'button', '#value' => t('Clear'), '#attributes' => array('class' => array('sch-clear', 'qms-btn-extra'), 'id' => 'sch-btn-clear'), /* '#suffix' => '
',
*
*/
);
// storage area for dynamic dialog element
$form['popup_dialog'] = array(
'#markup' => '',
);
return $form;
}
/*
* _get_elog_session_report_list()
*
*/
function _get_elog_session_report_list($customer_id = 0, $begin_date = '', $end_date = '') {
try{
$begin_ts = 0;
$end_ts = 0;
$simulator_list = new SimulatorList();
define('ELOG_FLIGHT_ROLE_INSTRUCTOR', 3);
define('ELOG_FLIGHT_ROLE_PILOT2', 2);
define('ELOG_FLIGHT_ROLE_PILOT1', 1);
$year = date('Y');
// if no date range supplied, or a partial date range, only get elogs for this year
$begin_ts = ( !strlen($begin_date) ?
_st_format_system_timestamp($year . '-01-01 00:00:00') :
_st_format_system_timestamp($begin_date . ' 00:00:00') );
$end_ts = ( !strlen($end_date) ?
_st_format_system_timestamp($year . '-12-31 23:59:59') :
_st_format_system_timestamp($end_date . ' 23:59:59') );
$table_header = array(
array( 'data' => t('Date'), 'class' => array('sch-tbl-elog-date') ),
array( 'data' => t('EL No.'), 'class' => array('sch-tbl-elog-no') ),
array( 'data' => t('Start'), 'class' => array('sch-tbl-elog-begintime') ),
array( 'data' => t('End'), 'class' => array('sch-tbl-elog-endtime') ),
array( 'data' => t('Simulator'), 'class' => array('sch-tbl-elog-sim') ),
array( 'data' => t('Instructor'), 'class' => array('sch-tbl-elog-instructor') ),
array( 'data' => t('Student 1'), 'class' => array('sch-tbl-elog-pilot1') ),
array( 'data' => t('Student 2'), 'class' => array('sch-tbl-elog-pilot2') ),
);
$table_rows = array();
$query = db_select('sch_elogs', 'e');
$query->innerJoin('sch_assignments', 'a', 'a.assignment_id = e.assignment_id');
$query->innerJoin('sch_customers', 'c', 'c.customer_id = a.customer_id');
$query->innerJoin('sch_schedules', 'sch', 'sch.schedule_id = a.schedule_id');
if ( $customer_id ) {
$query->condition('a.customer_id', $customer_id);
}
$query->condition('a.begin_timestamp', $begin_ts, '>=');
$query->condition('a.end_timestamp', $end_ts, '<=');
$query->fields('e', array('elog_id', 'el_no'))
->fields('a', array('assignment_id', 'customer_id',
'begin_timestamp', 'end_timestamp'))
->fields('c', array('customer'))
->fields('sch', array('simulator_id', 'timezone'));
$query->orderBy('customer')->orderBy('simulator_id')->orderBy('a.begin_timestamp');
$results = $query->execute();
$i = 0;
$last_customer_id = 0;
$content = '
' . t('Report Date Range') . '
', // ); $form['search']['from_date'] = array( '#type' => 'date_popup', '#title' => t('From Date'), '#size' => 10, '#date_format' => 'm-d-Y', '#default_value' => $begin_date, '#attributes' => array('style' => array('float:left', 'width:50px'), 'id' => 'sch-from-date', 'class' => array('qms-date-picker')), '#prefix' => '',
);
$form['search']['to_date'] = array(
'#type' => 'date_popup',
'#title' => t('To Date'),
'#size' => 10,
'#date_format' => 'm-d-Y',
'#default_value' => $end_date,
'#attributes' => array('style' => array('float:left', 'clear:right', 'width:50px'),
'id' => 'sch-to-date',
'class' => array('qms-date-picker')),
'#suffix' => '
', ); //-------------------------------------------- // Adds a simple submit button that refreshes the form and clears its contents // -- this is the default behavior for forms. $form['search']['actions'] = array('#type' => 'actions'); $form['search']['actions']['submit'] = array( '#type' => 'button', '#value' => t('Submit'), '#name' => 'submit', '#attributes' => array('id' => 'sch-btn-submit', 'sch-url' => url('reports/elogs'), 'style' => 'font-weight:bold;'), ); $form['search']['actions']['clear'] = array( '#type' => 'button', '#value' => t('Clear'), '#attributes' => array('class' => array('sch-clear', 'qms-btn-extra'), 'id' => 'sch-btn-clear'), /* '#suffix' => '
',
*
*/
);
// storage area for dynamic dialog element
$form['popup_dialog'] = array(
'#markup' => '',
);
return $form;
}
/*
* _get_elog_session_report_list()
*
*/
function _get_elog_session_report_list($customer_id = 0, $begin_date = '', $end_date = '') {
try{
$begin_ts = 0;
$end_ts = 0;
$simulator_list = new SimulatorList();
define('ELOG_FLIGHT_ROLE_INSTRUCTOR', 3);
define('ELOG_FLIGHT_ROLE_PILOT2', 2);
define('ELOG_FLIGHT_ROLE_PILOT1', 1);
$year = date('Y');
// if no date range supplied, or a partial date range, only get elogs for this year
$begin_ts = ( !strlen($begin_date) ?
_st_format_system_timestamp($year . '-01-01 00:00:00') :
_st_format_system_timestamp($begin_date . ' 00:00:00') );
$end_ts = ( !strlen($end_date) ?
_st_format_system_timestamp($year . '-12-31 23:59:59') :
_st_format_system_timestamp($end_date . ' 23:59:59') );
$table_header = array(
array( 'data' => t('Date'), 'class' => array('sch-tbl-elog-date') ),
array( 'data' => t('EL No.'), 'class' => array('sch-tbl-elog-no') ),
array( 'data' => t('Start'), 'class' => array('sch-tbl-elog-begintime') ),
array( 'data' => t('End'), 'class' => array('sch-tbl-elog-endtime') ),
array( 'data' => t('Simulator'), 'class' => array('sch-tbl-elog-sim') ),
array( 'data' => t('Instructor'), 'class' => array('sch-tbl-elog-instructor') ),
array( 'data' => t('Student 1'), 'class' => array('sch-tbl-elog-pilot1') ),
array( 'data' => t('Student 2'), 'class' => array('sch-tbl-elog-pilot2') ),
);
$table_rows = array();
$query = db_select('sch_elogs', 'e');
$query->innerJoin('sch_assignments', 'a', 'a.assignment_id = e.assignment_id');
$query->innerJoin('sch_customers', 'c', 'c.customer_id = a.customer_id');
$query->innerJoin('sch_schedules', 'sch', 'sch.schedule_id = a.schedule_id');
if ( $customer_id ) {
$query->condition('a.customer_id', $customer_id);
}
$query->condition('a.begin_timestamp', $begin_ts, '>=');
$query->condition('a.end_timestamp', $end_ts, '<=');
$query->fields('e', array('elog_id', 'el_no'))
->fields('a', array('assignment_id', 'customer_id',
'begin_timestamp', 'end_timestamp'))
->fields('c', array('customer'))
->fields('sch', array('simulator_id', 'timezone'));
$query->orderBy('customer')->orderBy('simulator_id')->orderBy('a.begin_timestamp');
$results = $query->execute();
$i = 0;
$last_customer_id = 0;
$content = '';
$customer_name = '';
$row_data = array();
$crew = array();
if ( $results->rowCount() ) {
$elog_results = $results->fetchAll();
foreach ($elog_results as $row) {
$query = db_select('sch_elog_crew', 'ec');
$query->leftJoin('sch_flight_roles', 'fr',
'ec.flight_role_1_id = fr.flight_role_id');
$query->condition('ec.elog_id', $row->elog_id);
$query->condition('ec.flight_role_1_id', 3, '<=');
$query->fields('ec', array('lname', 'fname', 'flight_role_1_id'));
$query->orderBy('ec.flight_role_1_id', 'desc');
$results = $query->execute();
if ( $results->rowCount() ) {
$crew = $results->fetchAll();
}
//$query->condition('fr.flight_role_1_id', ELOG_FLIGHT_ROLE_INSTRUCTOR);
// instructor
if ( $i && ($row->customer_id <> $last_customer_id) ) {
$content .= '
'; $table_rows = array(); } $row_data = array( _st_format_schedule_date($row->begin_timestamp, 'n/j/Y', $row->timezone), $row->el_no, _st_format_schedule_date($row->begin_timestamp, 'H:i', $row->timezone), _st_format_schedule_date($row->end_timestamp, 'H:i', $row->timezone), $simulator_list->getName($row->simulator_id), // returns a formatted name '', // crew will popuplate in these fields '', // otherwise blank '', ); for($c=0; $c<3; $c++) { if ( isset($crew[$c]) ) { $crew_name = $crew[$c]->lname . (strlen($crew[$c]->fname) ? ', ' . $crew[$c]->fname : '' ); if ( (int)$crew[$c]->flight_role_1_id == ELOG_FLIGHT_ROLE_INSTRUCTOR ) { $row_data[5] = $crew_name; } else if ( (int)$crew[$c]->flight_role_1_id == ELOG_FLIGHT_ROLE_PILOT1 ) { $row_data[6] = $crew_name; } else if ( (int)$crew[$c]->flight_role_1_id == ELOG_FLIGHT_ROLE_PILOT2 ) { $row_data[7] = $crew_name; } } } //ksort($row_data); unset($crew); $table_rows[] = array('data' => $row_data); $last_customer_id = $row->customer_id; $customer_name = $row->customer; $i++; } if ( $last_customer_id ) { $content .= '
'; } } if ( !$i ) { $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= '
'; } $content .= '
';
return $content;
}
catch(Exception $e) {
watchdog(SCH_SCHEDULER, '_get_elog_session_report_list() ' . $e->getMessage(), array(), WATCHDOG_ERROR);
}
}
/*
* _get_elog_quality_report_list()
*
*/
function _get_elog_quality_report_list($customer_id = 0, $begin_date = '', $end_date = '') {
try{
$begin_ts = 0;
$end_ts = 0;
$simulator_list = new SimulatorList();
$year = date('Y');
// if no date range supplied, or a partial date range, only get elogs for this year
$begin_ts = ( !strlen($begin_date) ?
_st_format_system_timestamp($year . '-01-01 00:00:00') : _st_format_system_timestamp($begin_date . ' 00:00:00') );
$end_ts = ( !strlen($end_date) ?
_st_format_system_timestamp($year . '-12-31 23:59:59') : _st_format_system_timestamp($end_date . ' 23:59:59') );
$table_header = array(
array( 'data' => t('Date'), 'class' => array('sch-tbl-elog-date') ),
array( 'data' => t('Customer'), 'class' => array('sch-tbl-elog-customer') ),
array( 'data' => t('Simulator'), 'class' => array('sch-tbl-elog-simulator') ),
array( 'data' => t('Session'), 'class' => array('sch-tbl-elog-session') ),
array( 'data' => t('Rating'), 'class' => array('sch-tbl-elog-rating') ),
array( 'data' => t('Comments'), 'class' => array('sch-tbl-elog-comments') ),
//array( 'data' => t('Start'), 'class' => array('sch-tbl-elog-begintime') ),
//array( 'data' => t('End'), 'class' => array('sch-tbl-elog-endtime') ),
//array( 'data' => t('Instructor'), 'class' => array('sch-tbl-elog-instructor') ),
);
$table_rows = array();
$query = db_select('sch_elogs', 'e');
$query->innerJoin('sch_assignments', 'a', 'a.assignment_id = e.assignment_id');
$query->innerJoin('sch_customers', 'c', 'c.customer_id = a.customer_id');
$query->innerJoin('sch_schedules', 'sch', 'sch.schedule_id = a.schedule_id');
$query->innerJoin('sch_schedule_sessions', 'ss',
'ss.schedule_session_id = a.schedule_session_id');
$query->innerJoin('sch_session_names', 'sn',
'sn.session_name_id = ss.session_name_id');
if ( $customer_id ) {
$query->condition('a.customer_id', $customer_id);
}
$query->condition('a.begin_timestamp', $begin_ts, '>=');
$query->condition('a.end_timestamp', $end_ts, '<=');
$query->fields('e', array('elog_id', 'el_no', 'sim_quality_rating', 'log_text'))
->fields('a', array('assignment_id', 'customer_id',
'begin_timestamp', 'end_timestamp'))
->fields('sn', array('session_name'))
->fields('c', array('customer'))
->fields('sch', array('simulator_id'));
$query->orderBy('customer')->orderBy('simulator_id')->orderBy('a.begin_timestamp');
$results = $query->execute();
$i = 0;
$last_customer_id = 0;
$content = '' . $customer_name . '
'; $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= ''; $table_rows = array(); } $row_data = array( _st_format_schedule_date($row->begin_timestamp, 'n/j/Y', $row->timezone), $row->el_no, _st_format_schedule_date($row->begin_timestamp, 'H:i', $row->timezone), _st_format_schedule_date($row->end_timestamp, 'H:i', $row->timezone), $simulator_list->getName($row->simulator_id), // returns a formatted name '', // crew will popuplate in these fields '', // otherwise blank '', ); for($c=0; $c<3; $c++) { if ( isset($crew[$c]) ) { $crew_name = $crew[$c]->lname . (strlen($crew[$c]->fname) ? ', ' . $crew[$c]->fname : '' ); if ( (int)$crew[$c]->flight_role_1_id == ELOG_FLIGHT_ROLE_INSTRUCTOR ) { $row_data[5] = $crew_name; } else if ( (int)$crew[$c]->flight_role_1_id == ELOG_FLIGHT_ROLE_PILOT1 ) { $row_data[6] = $crew_name; } else if ( (int)$crew[$c]->flight_role_1_id == ELOG_FLIGHT_ROLE_PILOT2 ) { $row_data[7] = $crew_name; } } } //ksort($row_data); unset($crew); $table_rows[] = array('data' => $row_data); $last_customer_id = $row->customer_id; $customer_name = $row->customer; $i++; } if ( $last_customer_id ) { $content .= '
' . $customer_name . '
'; $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= ''; } } if ( !$i ) { $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= '
'; } $content .= '
';
$customer_name = '';
$row_data = array();
$crew = array();
if ( $results->rowCount() ) {
foreach($results as $row) {
$quality = '';
switch ($row->sim_quality_rating) {
case 1: $quality = t('Deficient'); break;
case 2: $quality = t('Unsatisfactory'); break;
case 3: $quality = t('Satisfactory'); break;
case 4: $quality = t('Good'); break;
case 5: $quality = t('Excellent'); break;
default:
break;
}
$row_data = array(
_st_format_system_date($row->begin_timestamp, 'n/j/Y'),
$row->customer,
$simulator_list->getName($row->simulator_id),
$row->session_name . ':
' . _st_format_system_date($row->begin_timestamp, 'H:i') . ' - ' . _st_format_system_date($row->end_timestamp, 'H:i'), $quality, trim(nl2br(_st_convert_symbols($row->log_text))), ); $table_rows[] = array('data' => $row_data); $last_customer_id = $row->customer_id; $customer_name = $row->customer; $i++; } if ( $last_customer_id ) { $content .= '
'; } } if ( !$i ) { $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= '
'; } $content .= '
';
return $content;
}
catch(Exception $e) {
watchdog(SCH_SCHEDULER, '_get_elog_quality_report_list() ' . $e->getMessage(), array(), WATCHDOG_ERROR);
}
}
/*
* report_customer_sessions()
*
*/
function report_customer_sessions($customer_id = 0, $begin_date = '', $end_date = '',
$show_form_only = True) {
if ( user_access('view manage menu') == FALSE ) {
drupal_set_message( t('Unauthorized: Permission is required'), 'status');
return '';
}
if ( '0' == $begin_date ) { $begin_date = ''; }
if ( '0' == $end_date ) { $end_date = ''; }
$subtitle = '';
$year = date('Y');
if ( !strlen($begin_date) && !strlen($end_date) ) {
$subtitle = $year;
}
else if ( !strlen($begin_date) ) {
$ed = _st_right($end_date, 5);
$ed = str_replace('-', '/', $ed) . '/' . $year;
$subtitle = '1/1/' . $year . ' - ' . $ed;
}
else if ( !strlen($end_date) ) {
$bd = _st_right($begin_date, 5);
$bd = str_replace('-', '/', $bd) . '/' . $year;
$subtitle = $bd . ' - ' . '12/31/' . $year;
}
$content = '';
$print_page_heading =
'' . _st_format_system_date($row->begin_timestamp, 'H:i') . ' - ' . _st_format_system_date($row->end_timestamp, 'H:i'), $quality, trim(nl2br(_st_convert_symbols($row->log_text))), ); $table_rows[] = array('data' => $row_data); $last_customer_id = $row->customer_id; $customer_name = $row->customer; $i++; } if ( $last_customer_id ) { $content .= '
' . $customer_name . '
'; $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= ''; } } if ( !$i ) { $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= '
'; } $content .= '
' .
'
';
$content .= (!$show_form_only) ? $print_page_heading : '';
$form_elems = drupal_get_form('customer_sessions_report_form', $customer_id, $begin_date, $end_date);
$content .= render($form_elems);
if ( !$show_form_only ) {
// if no customer or date_range provided, return all elogs for the current year only.
$content .= _get_customer_sessions_list($customer_id, $begin_date, $end_date);
}
return $content;
}
/*
* customer_sessions_report_form()
* display form to get user input of search parameters
*
*/
function customer_sessions_report_form($form, $form_state,
$customer_id = 0, $begin_date = '', $end_date = '') {
drupal_add_library('system','ui.dialog');
drupal_add_library('system','ui.datepicker');
drupal_add_js(drupal_get_path('module', 'sabreTools') . '/js/sabreTools.lib.js');
drupal_add_js(drupal_get_path('module', 'sabreScheduler') . '/js/sabreScheduler.report.cust.sess.js');
//------------ BUILD SEARCH FORM ------------------
global $base_url;
//$simulator_list = new SimulatorList();
$customer_list = new CustomerList();
//$session_list = new SessionNameList();
$form['search'] = array(
'#type' => 'fieldset',
'#title' => t('Report Settings'),
'#collapsible' => True,
'#collapsed' => False,
'#attributes' => array('id' => array('sch-report-settings-div'),
'style' => 'padding-left:20px;'),
);
$form['search']['customer'] = array(
'#type' => 'select',
'#title' => t('Customer'),
'#options' => $customer_list->getAll(CustomerList::INCL_ALL),
'#default_value' => $customer_id,
'#attributes' => array('class' => array('qms-select'),
'id' => 'sch-customer-list'),
'#prefix' => '' . drupal_get_title() . '
' .
'' . $subtitle . '
' .
'',
'#suffix' => '
',
);
/*
$form['search']['include_unavailable'] = array(
'#type' => 'checkbox',
'#title' => t('Include Unavailable Sessions'),
'#default_value' => 0,
'#attributes' => array('id' => 'sch-include-unavailable-chk'),
//'#prefix' => '',
'#suffix' => '
',
);
*
*/
// $form['search']['search_date_label'] = array(
// '#markup' => '',
// //'
', ); //-------------------------------------------- // Adds a simple submit button that refreshes the form and clears its contents // -- this is the default behavior for forms. $form['search']['actions'] = array('#type' => 'actions'); $form['search']['actions']['submit'] = array( '#type' => 'button', '#value' => t('Submit'), '#name' => 'submit', '#attributes' => array('id' => 'sch-btn-submit', 'sch-url' => url('reports/customer/sessions'), 'style' => 'font-weight:bold;'), ); $form['search']['actions']['clear'] = array( '#type' => 'button', '#value' => t('Clear'), '#attributes' => array('class' => array('sch-clear', 'qms-btn-extra'), 'id' => 'sch-btn-clear'), '#suffix' => '',
);
// storage area for dynamic dialog element
$form['popup_dialog'] = array(
'#markup' => '',
);
return $form;
}
/*
* _get_customer_sessions_list()
*
*/
function _get_customer_sessions_list($customer_id = 0, $begin_date = '', $end_date = '') {
try{
$begin_ts = 0;
$end_ts = 0;
$simulator_list = new SimulatorList();
$year = date('Y');
// if no date range supplied, or a partial date range, only get elogs for this year
$begin_ts = ( !strlen($begin_date) ?
_st_format_system_timestamp($year . '-01-01 00:00:00') : _st_format_system_timestamp($begin_date . ' 00:00:00') );
$end_ts = ( !strlen($end_date) ?
_st_format_system_timestamp($year . '-12-31 23:59:59') : _st_format_system_timestamp($end_date . ' 23:59:59') );
$table_header = array(
array( 'data' => t('Date'), 'class' => array('sch-tbl-elog-date') ),
array( 'data' => t('Start'), 'class' => array('sch-tbl-elog-begintime') ),
array( 'data' => t('End'), 'class' => array('sch-tbl-elog-endtime') ),
array( 'data' => t('Simulator'), 'class' => array('sch-tbl-elog-simulator') ),
);
$table_rows = array();
//--------- Get Assignments/Bookings ---------------
$query = db_select('sch_assignments', 'a');
$query->innerJoin('sch_schedules', 'sch', 'a.schedule_id = sch.schedule_id');
$query->innerJoin('sch_simulators', 'si', 'sch.simulator_id = si.simulator_id');
//$query->innerJoin('sch_session_types', 't', 'a.session_type = t.session_type');
$query->innerJoin('sch_customers', 'c', 'a.customer_id = c.customer_id');
//$query->leftJoin('sch_groups', 'g', 'a.group_id = g.group_id');
//$query->innerJoin('sch_users', 'u', 'c.customer_id = u.customer_id');
if ( $customer_id ) {
$query->condition('a.customer_id', $customer_id);
}
$query->condition('a.cut_bill', 0); // do not include cut_bill'ed assignments
$query->condition('a.begin_timestamp', $begin_ts, '>=');
$query->condition('a.begin_timestamp', $end_ts, '<=');
$query->fields('a', array('assignment_id', 'begin_timestamp', 'end_timestamp',
'customer_id', 'group_id', 'session_type'))
->fields('si', array('simulator_id', 'sim_name', 'device_id_internal'))
->fields('sch', array('schedule_id', 'timezone'))
//->fields('t', array('session_type_desc'))
->fields('c', array('customer'));
//->fields('g', array('group_name'));
$query->orderBy('c.customer')->orderBy('simulator_id')->orderBy('a.begin_timestamp');
$results = $query->execute();
$i = 0;
$last_customer_id = 0;
$last_simulator_id = 0;
$sim_class = '';
$content = '
' . t('Report Date Range') . '
', // ); $form['search']['from_date'] = array( '#type' => 'date_popup', '#title' => t('From Date'), '#size' => 10, '#date_format' => 'm-d-Y', '#default_value' => $begin_date, '#attributes' => array('style' => array('float:left', 'width:50px'), 'id' => 'sch-from-date', 'class' => array('qms-date-picker')), '#prefix' => '',
);
$form['search']['to_date'] = array(
'#type' => 'date_popup',
'#title' => t('To Date'),
'#size' => 10,
'#date_format' => 'm-d-Y',
'#default_value' => $end_date,
'#attributes' => array('style' => array('float:left', 'clear:right', 'width:50px'),
'id' => 'sch-to-date',
'class' => array('qms-date-picker')),
'#suffix' => '
', ); //-------------------------------------------- // Adds a simple submit button that refreshes the form and clears its contents // -- this is the default behavior for forms. $form['search']['actions'] = array('#type' => 'actions'); $form['search']['actions']['submit'] = array( '#type' => 'button', '#value' => t('Submit'), '#name' => 'submit', '#attributes' => array('id' => 'sch-btn-submit', 'sch-url' => url('reports/customer/sessions'), 'style' => 'font-weight:bold;'), ); $form['search']['actions']['clear'] = array( '#type' => 'button', '#value' => t('Clear'), '#attributes' => array('class' => array('sch-clear', 'qms-btn-extra'), 'id' => 'sch-btn-clear'), '#suffix' => '
',
);
// storage area for dynamic dialog element
$form['popup_dialog'] = array(
'#markup' => '',
);
return $form;
}
/*
* _get_customer_sessions_list()
*
*/
function _get_customer_sessions_list($customer_id = 0, $begin_date = '', $end_date = '') {
try{
$begin_ts = 0;
$end_ts = 0;
$simulator_list = new SimulatorList();
$year = date('Y');
// if no date range supplied, or a partial date range, only get elogs for this year
$begin_ts = ( !strlen($begin_date) ?
_st_format_system_timestamp($year . '-01-01 00:00:00') : _st_format_system_timestamp($begin_date . ' 00:00:00') );
$end_ts = ( !strlen($end_date) ?
_st_format_system_timestamp($year . '-12-31 23:59:59') : _st_format_system_timestamp($end_date . ' 23:59:59') );
$table_header = array(
array( 'data' => t('Date'), 'class' => array('sch-tbl-elog-date') ),
array( 'data' => t('Start'), 'class' => array('sch-tbl-elog-begintime') ),
array( 'data' => t('End'), 'class' => array('sch-tbl-elog-endtime') ),
array( 'data' => t('Simulator'), 'class' => array('sch-tbl-elog-simulator') ),
);
$table_rows = array();
//--------- Get Assignments/Bookings ---------------
$query = db_select('sch_assignments', 'a');
$query->innerJoin('sch_schedules', 'sch', 'a.schedule_id = sch.schedule_id');
$query->innerJoin('sch_simulators', 'si', 'sch.simulator_id = si.simulator_id');
//$query->innerJoin('sch_session_types', 't', 'a.session_type = t.session_type');
$query->innerJoin('sch_customers', 'c', 'a.customer_id = c.customer_id');
//$query->leftJoin('sch_groups', 'g', 'a.group_id = g.group_id');
//$query->innerJoin('sch_users', 'u', 'c.customer_id = u.customer_id');
if ( $customer_id ) {
$query->condition('a.customer_id', $customer_id);
}
$query->condition('a.cut_bill', 0); // do not include cut_bill'ed assignments
$query->condition('a.begin_timestamp', $begin_ts, '>=');
$query->condition('a.begin_timestamp', $end_ts, '<=');
$query->fields('a', array('assignment_id', 'begin_timestamp', 'end_timestamp',
'customer_id', 'group_id', 'session_type'))
->fields('si', array('simulator_id', 'sim_name', 'device_id_internal'))
->fields('sch', array('schedule_id', 'timezone'))
//->fields('t', array('session_type_desc'))
->fields('c', array('customer'));
//->fields('g', array('group_name'));
$query->orderBy('c.customer')->orderBy('simulator_id')->orderBy('a.begin_timestamp');
$results = $query->execute();
$i = 0;
$last_customer_id = 0;
$last_simulator_id = 0;
$sim_class = '';
$content = '';
$customer_name = '';
$row_data = array();
if ( $results->rowCount() ) {
foreach ($results as $row) {
if ( $i && ($row->customer_id <> $last_customer_id) ) {
$content .= '
'; $table_rows = array(); } $row_data = array( _st_format_schedule_date($row->begin_timestamp, 'n/j/Y', $row->timezone), _st_format_schedule_date($row->begin_timestamp, 'H:i', $row->timezone), _st_format_schedule_date($row->end_timestamp, 'H:i', $row->timezone), $simulator_list->getName($row->simulator_id), // formatted name ); if ( $last_simulator_id && ($row->simulator_id <> $last_simulator_id) ) { $sim_class = 'sch-sim-divider'; } else { $sim_class = 'sch-sim'; } $table_rows[] = array('data' => $row_data, 'class' => array($sim_class)); $last_customer_id = $row->customer_id; $last_simulator_id = $row->simulator_id; $customer_name = $row->customer; $i++; } if ( $last_customer_id ) { $content .= '
'; } } if ( !$i ) { $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= '
'; } //------------------------- for ALL Customers: Append Exceptions ------------------------ if ( !$customer_id ) { $results_except = _get_exceptions($begin_ts, $end_ts, '', 0, True); $table_rows = array(); $i = 0; if ( $results_except->rowCount() ) { foreach ($results_except as $row) { $sim_name = (($row->simulator_id) ? $simulator_list->getName($row->simulator_id) : t('All Simulators')); $row_data = array( _st_format_system_date($row->begin_timestamp, 'n/j/Y'), _st_format_system_date($row->begin_timestamp, 'H:i'), _st_format_system_date($row->end_timestamp, 'H:i'), $sim_name, // formatted name ); if ( $i && ($row->simulator_id <> $last_simulator_id)) { $sim_class = 'sch-sim-divider'; } else { $sim_class = 'sch-sim'; } $table_rows[] = array('data' => $row_data, 'class' => array($sim_class)); //$last_customer_id = $row->customer_id; $last_simulator_id = $row->simulator_id; $i++; } } $content .= '
'; } $content .= '
';
return $content;
}
catch(Exception $e) {
watchdog(SCH_SCHEDULER, '_get_customer_sessions_list() ' . $e->getMessage(), array(), WATCHDOG_ERROR);
}
}
' . $customer_name . '
'; $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= ''; $table_rows = array(); } $row_data = array( _st_format_schedule_date($row->begin_timestamp, 'n/j/Y', $row->timezone), _st_format_schedule_date($row->begin_timestamp, 'H:i', $row->timezone), _st_format_schedule_date($row->end_timestamp, 'H:i', $row->timezone), $simulator_list->getName($row->simulator_id), // formatted name ); if ( $last_simulator_id && ($row->simulator_id <> $last_simulator_id) ) { $sim_class = 'sch-sim-divider'; } else { $sim_class = 'sch-sim'; } $table_rows[] = array('data' => $row_data, 'class' => array($sim_class)); $last_customer_id = $row->customer_id; $last_simulator_id = $row->simulator_id; $customer_name = $row->customer; $i++; } if ( $last_customer_id ) { $content .= '
' . $customer_name . '
'; $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= ''; } } if ( !$i ) { $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= '
'; } //------------------------- for ALL Customers: Append Exceptions ------------------------ if ( !$customer_id ) { $results_except = _get_exceptions($begin_ts, $end_ts, '', 0, True); $table_rows = array(); $i = 0; if ( $results_except->rowCount() ) { foreach ($results_except as $row) { $sim_name = (($row->simulator_id) ? $simulator_list->getName($row->simulator_id) : t('All Simulators')); $row_data = array( _st_format_system_date($row->begin_timestamp, 'n/j/Y'), _st_format_system_date($row->begin_timestamp, 'H:i'), _st_format_system_date($row->end_timestamp, 'H:i'), $sim_name, // formatted name ); if ( $i && ($row->simulator_id <> $last_simulator_id)) { $sim_class = 'sch-sim-divider'; } else { $sim_class = 'sch-sim'; } $table_rows[] = array('data' => $row_data, 'class' => array($sim_class)); //$last_customer_id = $row->customer_id; $last_simulator_id = $row->simulator_id; $i++; } } $content .= '
' . t('Maintenance Exceptions') . '
'; $content .= theme( 'table', array( 'header' => $table_header, 'rows' => $table_rows, 'empty' => t('None'), )); $content .= ''; } $content .= '