<?php

/**
 *		sabreQMS Module for plugin to Drupal 7 Framework
 *
 *		QTG Search List functions
 *    
*/


/*
 * _get_qtg_results_table_header()
 * generates the headers for the search results table so it only gets called once
 *
 */

function _get_qtg_results_table_header($bAdmin = True) {
	$table_header = array( 
    'qtg_no' => array( 
			'data' => t('QTG No.'), 
			'field' => 'qtg_no', 
			'sort' => 'asc', 
			'class' => array('qms-results-qtg-no') 
		),
    'created_date' => array( 
			'data' => t('Opened'), 
			'field' => 'created_date', 
			'sort' => 'desc', 
			'class' => array('qms-results-qtg-date-opened') 
		),
    'date_closed' => array( 
			'data' => 'Closed', 
			'field' => 'date_closed', 
      'sort' => 'desc', 
			'class' => array('qms-results-qtg-date-closed') 
		),
//		'test_code' => array( 
//			'data' => t('Test Code'), 
//			'field' => 'test_code', 
//			'sort' => 'asc', 
//			'class' => array('qms-results-test-code') 
//		),
		'sim_name' => array( 
			'data' => t('Simulator'), 
			'field' => 'sim_name', 
			'sort' => 'asc', 
			'class' => array('qms-results-qtg-simulator') 
		),
    'plan_group' => array( 
			'data' => t('Plan/Group'), 
			'field' => 'plan_group', 
			'sort' => 'asc', 
			'class' => array('qms-results-qtg-plan-group')  
		),
		
	);	
	
	//if ($bAdmin) {
		$table_header['admin'] = array( 'data' => t('Admin'), 'class' => array('qms-results-admin'));
	//}
  
	// add hidden column that gets used for the text popup display
//	$table_header['test_desc'] = array( 
//		'data' => t('Test Desc'), 
//		'field' => 'test_desc', 
//		'sort' => 'asc', 
//		'class' => array('qms-hidden-field') 
//	);
	
	return $table_header;
}

/*
 * _get_qtg_search_results()
 * display the search results
 * This function gets called by qtg_search_form_callback() and qtg_pager_callback()
 * also rebuilds the search results and cache after a delete
 *
 */

function _get_qtg_search_results($search,	$search_results = Null) {
	
	if ( user_access('search view reports') == FALSE ) {
		drupal_set_message(t('Unauthorized:  User authentication required'));
    return;
  }

	$bAdmin = user_access('administer sabreQMS');
	$bAllowEdit = user_access('edit qtg event');
  
  global $simulator_list;
						 		
	if ( Null == $search_results ) {
		$search_results = new SearchResultsTable('QTG');
	}
	
	$table_header = array();
  global $simulator_list;
	
	if ( !$search_results->hasHeader() ) {
		$table_header = _get_qtg_results_table_header($bAllowEdit);
		$search_results->setHeader($table_header);
	}
	else {
		$table_header = $search_results->getHeader();
	}
	
	// get the selected dr cache, if anything has been stored
	$saved_selected_text = '';
	$selected_cache_name = _get_qms_cache_name(QMS_QTG_SELECTED);

	$selected_cache = cache_get($selected_cache_name);
	if ( isset($selected_cache->data) && ($selected_cache->data <> '') ) {
		$saved_selected_text = $selected_cache->data;
	}
	
	$search_init = array(
		'table_name' => 'qms_qtg_events',
		'table_alias' => 'qe',
		'table_header' => $table_header,
		'pager_path' => 'search/qtg/pager',
		'order' => QMS_QTG_DEFAULT_SORT_COLUMN,
		'order_default' => 'QTG No.',
		'sort' => QMS_QTG_DEFAULT_SORT,
		'sort_default' => 'desc',
		'variable' => True,
	);
	
	$query = _st_setup_paged_search($search_init);
	
	
	//------------------ Add Search Conditions ------------------------
	
	// Date filtering 
	
	//  Date opened
	if ( $search->open == 1 ) {
		if ( (strlen($search->from_date) > 0) && (strlen($search->to_date) > 0)) {
			$query->condition('qe.created_date', _st_format_timestamp($search->from_date . " 00:00:00"), '>=');
			$query->condition('qe.created_date', _st_format_timestamp($search->to_date . " 23:59:59"), '<=');
		}
		else if (strlen($search->from_date) > 0) {
			$query->condition('qe.created_date', _st_format_timestamp($search->from_date . " 00:00:00"), '>=');
		}
		else if (strlen($search->to_date) > 0) {
			$query->condition('qe.created_date', _st_format_timestamp($search->to_date . " 23:59:59"), '<=');
		}
		else {
			$query->condition('qe.date_closed', 0, '=');
		}
	}
	
	//  Date closed
	if ( $search->closed == 1 ) {
		if ( (strlen($search->from_date) > 0) && (strlen($search->to_date) > 0)) {
			$query->condition('qe.date_closed', _st_format_timestamp($search->from_date . " 00:00:00"), '>=');
			$query->condition('qe.date_closed', _st_format_timestamp($search->to_date . " 23:59:59"), '<=');
		}
		else if (strlen($search->from_date) > 0) {
			$query->condition('qe.date_closed', _st_format_timestamp($search->from_date . " 00:00:00"), '>=');
		}
		else if (strlen($search->to_date) > 0) {
			$query->condition('qe.date_closed', _st_format_timestamp($search->to_date . " 23:59:59"), '<=');
		}
		else {
			$query->condition('qe.date_closed', 0, '>');	
		}
	}
  
  $query->leftJoin('qms_qtg_event_tests', 'et', 'qe.qtg_event_id = et.qtg_event_id');
  $query->leftJoin('qms_qtg_tests', 't', 'et.qtg_test_id = t.qtg_test_id');
  $query->leftJoin('qms_qtg_test_defs', 'td', 't.qtg_test_def_id = td.qtg_test_def_id');
  $query->leftJoin('qms_qtg_groups', 'g', 'qe.qtg_group_id = g.qtg_group_id');
  $query->leftJoin('qms_qtg_test_plans', 'p', 'qe.qtg_test_plan_id = p.qtg_test_plan_id');
	

  // QTG No.
	if ( ($search->qtg_no <> '') && ($search->qtg_no <> 'QTG') ) { // if just QTG, ignore it 
		$search->qtg_no .= '%';  // append wildcard for fuzzy match
		$query->condition('qe.qtg_no', $search->qtg_no, 'LIKE');
	}
  
  
	// Test Code
	if ( $search->test_code <> '' ) { 
		// append wildcard for fuzzy match
		$query->condition('td.test_code', $search->test_code . '%', 'LIKE');
	}
	
	// Plan Name
	if ( $search->plan_name <> '' ) {
    $query->condition('p.plan_name', '%' . $search->plan_name . '%', 'LIKE');
	}
  
  // Group Name
	if ( $search->group_name <> '' ) {
    $query->condition('g.group_name', '%' . $search->group_name . '%', 'LIKE');
	}
	
  // Simulator
  $query->innerJoin('qms_simulators', 's', 's.simulator_id = p.simulator_id');
	if ( $search->simulator_id > 0 ) {
    $query->condition('p.simulator_id', $search->simulator_id);
	}
  else {
    // ALL SIMS SEARCH - ignore the sims that have been turned off for ALL searches
    $query->condition('s.include_all_searches', 1);
  }
  
  // Note_Text
  if ( $search->note_text <> '' ) {
    $query->leftJoin('qms_qtg_test_status_history', 'sh', 
                      'et.qtg_event_id = sh.qtg_event_id AND et.qtg_test_id = sh.qtg_test_id');
		//---------------------- Keyword Seaching:  Note Text --------------
		// Combo Keyword and Exact Phrase Matching ( ex:  keyword "exact phrase" )
		_st_add_keyword_search_condition($query, 'sh.note_text', $search->note_text);
	}
  
  $query->addExpression('CONCAT_WS(\', \', p.plan_name, g.group_name)', 'plan_group');
	
	
	$query->fields('qe', array('qtg_event_id', 'qtg_no', 'created_date', 'date_closed') )
              ->fields('p', array('plan_name', 'simulator_id'))
              ->fields('g', array('group_name'));
  
              //->fields('td', array('test_code', 'test_desc'))
              //->fields('e', array('full_name'))
              //->fields('u', array('name'));
							//->fields('c', array('customer'))
							//->fields('s', array('sim_name', 'active'))
              
			
	// determine if items were selected or not		
	if ( strlen($saved_selected_text) ) {
		$list = explode(",", $saved_selected_text);
		$query->addExpression("IF( qe.qtg_event_id IN(:sel_list), :y, :n )", "selected", 
																array(':sel_list' => $list, ':y' => 1, ':n' => 0));	
	}				
  
  $query->distinct();	
  
	$max_count = 0;
	$results = _st_execute_paged_search($query, $max_count);
	
	$i = 0;
	$table_rows = array();
	$selected_list = array();
  $qtg_url = ($bAllowEdit ? 'qtg/event/edit/' : 'qtg/event/view/');
	
	global $base_url;
	
	
	foreach ($results as $row) {
		
		// the QTG test has been closed, 
    // only admin edit allowed despite user's edit permissions
		$bEdit = ($row->date_closed ? $bAdmin : $bAllowEdit );
		
		
//		$tech_name = 
//			( (0 == $row->tech_user_id) ? '[unassigned]' : 
//					( strlen($row->full_name) ? $row->full_name : $row->name . "*"));
		
		
		// set the id into the array index which is linked to the tableselect's checkbox
		$table_rows[$row->qtg_event_id] = array(
			'qtg_no' => array(
				'data' => array(
					'#type' => 'link',
					'#title' => $row->qtg_no,
					'#href' => ($qtg_url . $row->qtg_event_id),
					'#attributes' => array('class' => array('qms-qtg-no')),
					'#options' => array('query' => array('destination' => 'search/qtg')),
				),
			),
			'created_date' => _st_format_date($row->created_date, 'short'),
			'date_closed' => ( $row->date_closed ? _st_format_date($row->date_closed, 'short') : ''),
			//'test_code' => $row->test_code,
			'sim_name' => $simulator_list->getName($row->simulator_id),
			'plan_group' => $row->plan_name . ', ' . $row->group_name,
		);
		
		// don't allow for the column to exist if user is not an admin, otherwise we get an empty column
		if ($bAllowEdit) {
      // user may be allowed to edit events, but not allowed to edit
      // a specific closed Test.  Display the column even if not providing
      // the edit link
      $table_rows[$row->qtg_event_id]['admin'] = array(
        'data' => _get_results_table_options('qtg/event', $row->qtg_event_id, 
                                              $bEdit, 'search/qtg'), 
        'class' => array('qms-results-admin'),
      );
    }
		
//		$table_rows[$row->qtg_event_id]['test_desc'] = array(
//			'data' => check_plain(( strlen($row->test_desc) > 650 ) ? 
//									(_st_convert_symbols(substr($row->test_desc, 0, 650)) . '...') : 
//									_st_convert_symbols($row->test_desc)),
//			'class' => 'qms-hidden-popup-text',
//		);
		
		if ( strlen($saved_selected_text) ) { $selected_list[$row->qtg_event_id] = $row->selected; }

		$i++;
	}
	
	$pager = theme('pager', array('tags' => array(), 'quantity' => QMS_RECORDS_PER_PAGE));
	$search_results->setPager($pager);
	$search_results->setResults($table_rows);
	$search_results->setSelected($selected_list);
	
	//Save to cache
	$results_cache_name = _get_qms_cache_name(QMS_QTG_SEARCH_RESULTS);
	
	// cache the nested table array structure without rendering
	// rendering has to be done each time the results are displayed or the checkbox selections won't work
	cache_set($results_cache_name, $search_results, 'cache', time() + QMS_CACHE_TIMEOUT);
	
	return $table_rows;
	
		
}


/*
 * _get_qtg_results_table_header()
 * generates the headers for the search results table so it only gets called once
 *
 */

function _get_qtg_expiring_table_header($bAdmin = True) {
	$table_header = array( 
    'qtg_no' => array( 
			'data' => t('QTG No.'), 
			'field' => 'qtg_no', 
			'sort' => 'asc', 
			'class' => array('qms-results-qtg-no') 
		),
    'created_date' => array( 
			'data' => t('Opened'), 
			'field' => 'created_date', 
			'sort' => 'desc', 
			'class' => array('qms-results-qtg-date-opened') 
		),
    'expires' => array( 
			'data' => t('Expires'), 
			'field' => 'date_expires', 
      'sort' => 'desc', 
			'class' => array('qms-results-qtg-date-expires') 
		),
//		'test_code' => array( 
//			'data' => t('Test Code'), 
//			'field' => 'test_code', 
//			'sort' => 'asc', 
//			'class' => array('qms-results-test-code') 
//		),
		'sim_name' => array( 
			'data' => t('Simulator'), 
			'field' => 'sim_name', 
			'sort' => 'asc', 
			'class' => array('qms-results-qtg-simulator') 
		),
    'plan_group' => array( 
			'data' => t('Plan/Group'), 
			'field' => 'plan_group', 
			'sort' => 'asc', 
			'class' => array('qms-results-qtg-plan-group')  
		),
		
	);	
	
	if ($bAdmin) {
		$table_header['admin'] = array( 'data' => 'Admin', 'class' => array('qms-results-admin'));
	}
  
	// add hidden column that gets used for the text popup display
//	$table_header['test_desc'] = array( 
//		'data' => t('Test Desc'), 
//		'field' => 'test_desc', 
//		'sort' => 'asc', 
//		'class' => array('qms-hidden-field') 
//	);
	
	return $table_header;
}


/*
 * _get_qtg_expiring_list()
 * 
 * Called by QMS Alerts Page
 *
 */

function qtg_expiring_form($form, &$form_state, $vars = array()) {
	
	if ( user_access('search view reports') == FALSE ) {
		drupal_set_message(t('Unauthorized:  User authentication required'));
    return;
  }

	$bAdmin = user_access('administer sabreQMS');
	$bAllowEdit = user_access('edit qtg event');
  $bFilter = False;
  
  
  global $simulator_list;
  $table_header = _get_qtg_expiring_table_header($bAllowEdit);
  
  
//	if ( Null == $search_results ) {
//		$search_results = new SearchResultsTable('QTG');
//	}
	
//	$table_header = array();
//  global $simulator_list;
//	
//	if ( !$search_results->hasHeader() ) {
//		$table_header = _get_qtg_results_table_header(($bEdit || $bAdmin));
//		$search_results->setHeader($table_header);
//	}
//	else {
//		$table_header = $search_results->getHeader();
//	}
	
	// get the selected dr cache, if anything has been stored
//	$saved_selected_text = '';
//	$selected_cache_name = _get_qms_cache_name(QMS_QTG_SELECTED);
//
//	$selected_cache = cache_get($selected_cache_name);
//	if ( isset($selected_cache->data) && ($selected_cache->data <> '') ) {
//		$saved_selected_text = $selected_cache->data;
//	}
	
	$search_init = array(
		'table_name' => 'qms_qtg_events',
		'table_alias' => 'qe',
		'table_header' => $table_header,
		'pager_path' => 'reports/qtgexpiring/pager',
		'order' => QMS_QTG_DEFAULT_SORT_COLUMN,
		'order_default' => 'QTG No.',
		'sort' => QMS_QTG_DEFAULT_SORT,
		'sort_default' => 'desc',
		'variable' => True,
	);
	
	$query = _st_setup_paged_search($search_init);
	
	
	//------------------ Add Search Conditions ------------------------
	
	
  $query->leftJoin('qms_qtg_event_tests', 'et', 'qe.qtg_event_id = et.qtg_event_id');
  $query->leftJoin('qms_qtg_tests', 't', 'et.qtg_test_id = t.qtg_test_id');
  $query->leftJoin('qms_qtg_test_defs', 'td', 't.qtg_test_def_id = td.qtg_test_def_id');
  $query->leftJoin('qms_qtg_groups', 'g', 'qe.qtg_group_id = g.qtg_group_id');
  $query->leftJoin('qms_qtg_test_plans', 'p', 'qe.qtg_test_plan_id = p.qtg_test_plan_id');
	

  // Simulator
  $query->innerJoin('qms_simulators', 's', 's.simulator_id = p.simulator_id');
	  
  $query->addExpression('CONCAT_WS(\', \', p.plan_name, g.group_name)', 'plan_group');
  
  
  if ( isset($_SESSION['qtg_selected'])) {
    
    $qtg_selected = $_SESSION['qtg_selected'];
    //unset($_SESSION['qtg_selected']);
    $qtg_list = explode(",", $qtg_selected);
    $query->where('qe.qtg_event_id IN (:sel_list)', 
                  array(':sel_list' => $qtg_list));
    $bFilter = True;
    $vars['pager'] = False;
  }
	
	
	$query->fields('qe', array('qtg_event_id', 'qtg_no', 
                             'created_date', 'date_closed') )
        ->fields('p', array('plan_name', 'simulator_id'))
        ->fields('g', array('group_name'));
  
  $query->addExpression('(SELECT next_event 
                          FROM qms_qtg_groups 
                          WHERE qtg_test_plan_id = qe.qtg_test_plan_id 
                          ORDER BY next_event ASC 
                          LIMIT 1)', 'date_expires');
  
//  $query->where("DATE_ADD(FROM_UNIXTIME(qe.created_date), INTERVAL CAST((d.clear_time * 24) AS UNSIGNED) HOUR) <= DATE_ADD(NOW(), INTERVAL (5*24) HOUR)");

  $query->condition('qe.date_closed', 0, '=');
  $query->where("qe.qtg_test_plan_id IN 
        (SELECT qtg_test_plan_id FROM qms_qtg_groups as g  
         WHERE DATE_SUB(FROM_UNIXTIME(g.next_event), INTERVAL 15 DAY) 
         <= NOW())");	

              
//	// determine if items were selected or not		
//	if ( strlen($saved_selected_text) ) {
//		$list = explode(",", $saved_selected_text);
//		$query->addExpression("IF( qe.qtg_event_id IN(:sel_list), :y, :n )", "selected", 
//																array(':sel_list' => $list, ':y' => 1, ':n' => 0));	
//	}				
  
  $query->distinct();	
  
	$max_count = 0;
	$results = _st_execute_paged_search($query, $max_count);
	
	$i = 0;
	$table_rows = array();
	$selected_list = array();
  $qtg_url = (($bAdmin || $bAllowEdit) ? 'qtg/event/edit/' : 'qtg/event/view/');
	
	global $base_url;
	
	
	foreach ($results as $row) {
		
		// the TC has been closed, only admin edit allowed despite user's edit permissions
		$bEdit = ($row->date_closed ? $bAdmin : $bAllowEdit );
		
		
//		$tech_name = 
//			( (0 == $row->tech_user_id) ? '[unassigned]' : 
//					( strlen($row->full_name) ? $row->full_name : $row->name . "*"));
		
		
		// set the id into the array index which is linked to the tableselect's checkbox
		$table_rows[$row->qtg_event_id] = array(
			'qtg_no' => array(
				'data' => array(
					'#type' => 'link',
					'#title' => $row->qtg_no,
					'#href' => ($qtg_url . $row->qtg_event_id),
					'#attributes' => array('class' => array('qms-qtg-no')),
					'#options' => array('query' => array('destination' => 'search/qtg')),
				),
			),
			'created_date' => _st_format_date($row->created_date, 'short'),
			'expires' => ( $row->date_expires ? _st_format_date($row->date_expires, 'short') : ''),
			//'test_code' => $row->test_code,
			'sim_name' => $simulator_list->getName($row->simulator_id),
			'plan_group' => $row->plan_name . ', ' . $row->group_name,
		);
		
		// don't allow for the column to exist if user is not an admin, otherwise we get an empty column
		//if ($bAllowEdit || $bAdmin) {
      $table_rows[$row->qtg_event_id]['admin'] = array(
        'data' => _get_results_table_options('qtg/event', $row->qtg_event_id, 
                                              $bEdit), 
        'class' => array('qms-results-admin'),
      );
    //
		$i++;
	}
  
  
  //drupal_add_js(drupal_get_path('module', 'sabreQMS') . '/js/sabreQMS.qtgexpiring.js'); 
  
  $pager = theme('pager', array('tags' => array(), 
                       'quantity' => QMS_RECORDS_PER_PAGE));
  
  $form['qtg_div'] = array(
		'#markup' => '<div id="qms-qtg-div">',
	);
  
  // do not display the filter link if there is nothing to filter
  if ( !$bFilter ) {
    
    if ($i > 1) {
      $form['filter_link'] = array(
        '#markup' => 
          l(t('Filter Report'), 
            'reports/qtgexpiring/filter',
            array('attributes' => 
                      array('class' => array('qms-btn-filter-report')) )),
                  //'query' => array('destination' => $goto_url) ) ),

      );
    }
    else {
      $bFilter = True;  // fake -- removes the checkboxes
    }
  }
  
  $form['pager_top'] = array(
		'#markup' => $pager,	
	);
	
  
  if ( !$bFilter ) {
    
    // regular DEW report, display table select to allow for filtering
    $form['qtg_table'] = array(
      //'#name' => 'qms-qtg-table',
      '#type' => 'tableselect',
      '#header' => $table_header,
      '#options' => $table_rows,
      '#js_select' => True,
      '#multiple' => True,
      '#advanced_select' => True,
      '#empty' => t('None'),
      '#sticky' => True,
      '#attributes' => array('id' => 'qms-qtg-table'),
    );
  }
  else {
    // if this is a filtered report from the original qtg expiring report,
    // display an ordinary table
    $form['qtg_table'] = array(
      '#markup' => 
        theme('table', array(
          'header' => $table_header,
          'rows' => $table_rows,
          'empty' => t('None'),
          'attributes' => array('class' => array('qtg_table')),
        )),
    );
  }
  
  
  $form['pager_bottom'] = array(
		'#markup' => $pager . '</div>',	
	);
  
  $form['record_count'] = array(
    '#markup' => (($max_count > 0) ? 
                  '<br/><p class="qms-text">Total Records: ' . 
                   $max_count . 
                  '</p>' : ''),
  );
  
  //if ( !isset($vars['pager']) || !$vars['pager'] ) {
    //if ( !$bFilter ) {
      //hidden textarea for storing table_select clicks
      $form['qtg_selected'] = array(
        '#type' => 'textarea',
        '#resizable' => False,
        '#default_value' => '',
        '#attributes' => array('id' => 'qms-qtg-selected',
                               'class' => array('qms-hidden')),
        '#prefix' => '<div class="qms-hidden">',
        '#suffix' => '</div>',
      );

      //hidden submit button triggered by 'Filter Report' link above table
      $form['submit'] = array(
        '#type' => 'submit',
        '#value' => t('Submit'),
        '#submit' => array('qtg_expiring_form_submit'),
        '#attributes' => array('class' => array('qms-hidden'),
                               'id' => 'qms-qtg-submit'),  
      );

    //}
  //}
  
  return $form;

}


/*
 *  qtg_expiring_form_submit()
 *  Gets the list of selected qtg_event_ids and reroutes to the filtered report
 */
function qtg_expiring_form_submit($form, $form_state) {
  $qtg_selected = $form_state['values']['qtg_selected'];
  
  
  if (strlen($qtg_selected)) {
    $_SESSION['qtg_selected'] = $qtg_selected;
  }
  else { 
    unset($_SESSION['qtg_selected']); 
    unset($_GET['destination']);
  }
  drupal_goto('reports/qtgexpiring/filter'); 
}