<?php

/*
 *	sabreScheduler --> sabreQMS cross-app functions
 *
 * functions that involve connecting with the external QMS database
 */

require_once( drupal_get_path('module', 'sabreTools') . '/sabreTools.files.inc');

/*
 * _qms_simulator_add()
 *
 * IN:  $simulator record object
 * OUT: True on success
 *      False on fail
 */

function _qms_simulator_add($simulator) {

  try {

    $rc = False;

    if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

      // unset fields that are not stored in QMS
      /*
      $simulator->created_by_user = 1; // users can be diff in each app
      $simulator->customer_id = 1;     // this exists in QMS, not scheduler
      $simulator->status_code = 'D';   // QMS uses operational statuses
       */

      // drupal_write_record will fail here
      $rc = db_insert('qms_simulators')
              ->fields(array(
                  'simulator_id' => $simulator->simulator_id,
                  'sim_name' => $simulator->sim_name,
                  'active' => $simulator->active,
                  'faa_id' => $simulator->faa_id,
                  'device_id_internal' => $simulator->device_id_internal,
                  'created_date' => $simulator->created_date,
                  'created_by_user' => 1,
                  'customer_id' => 1,
                  'status_code' => 'D',
              ))->execute();


      _st_disconnect_database(SCH_SCHEDULER);
    }
    return $rc;
  }
  catch(Exception $e) {
		watchdog(SCH_SCHEDULER, '_qms_simulator_add()' . $e->getMessage(), array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
  }
}


/*
 * _qms_simulator_delete()
 *
 * IN:       $simulator_id
 * RETURNS:  $num_deleted (0 or 1), returns FALSE if $simulator_id is invalid.
 */

function _qms_simulator_delete($simulator_id) {

  try {

    if ( !$simulator_id ) return False;

    $qms_num_deleted = 0;

    if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

      // check if the same simulator in the QMS database has it's deleted flag set to true, if so, delete both
					$sql = "SELECT deleted FROM {qms_simulators} WHERE simulator_id = :sid";
					$flag = db_query($sql, array(':sid' => $simulator_id))->fetchField();

					if ( $flag == 1 ) { // already flagged for delete from QMS...delete both from QMS & Scheduler
						// delete from database
						$qms_num_deleted = db_delete('qms_simulators')
												->condition('simulator_id', $simulator_id)
												->execute();

						$m = db_delete('qms_prev_maint')
												->condition('simulator_id', $simulator_id, '=')
												->execute();

						$n = db_delete('qms_notifications')
												->condition('simulator_id', $simulator_id, '=')
												->execute();

					}

					_st_disconnect_database(SCH_SCHEDULER);
    }
    return $qms_num_deleted;
  }
  catch(Exception $e) {
		watchdog(SCH_SCHEDULER, '_qms_simulator_delete()' . $e->getMessage(), array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
  }
}


/*
 * _qms_add_app_codes()
 *
 * connect to QMS db and load the simulators
 */

function _qms_add_app_codes() {
	try {
    $this_app_root = variable_get(SABRE_SCHEDULER_ROOT, DRUPAL_ROOT);
    $that_app_root = '';

		if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

			variable_set(SCH_QMS_LINK, 1);
			variable_set(SABRE_APP_CODE, SABRE_APP_CODE_QMS);
      // set the scheduler app root in QMS
      variable_set(SABRE_SCHEDULER_ROOT, $this_app_root);

      $that_app_root = variable_get(SABRE_QMS_ROOT, '');

			_st_disconnect_database(SCH_SCHEDULER);
		}
    // set the QMS app root in scheduler
    variable_set(SABRE_QMS_ROOT, $that_app_root);

	}
	catch(Exception $e) {
		watchdog(SCH_SCHEDULER, '_qms_add_app_codes() ' . $e->getMessage(), array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
  }
}


/*
 * _qms_load_simulators()
 *
 * connect to QMS db and load the simulators
 */

function _qms_load_simulators() {

	try{

		$values = array();

		// check the scheduler database to see if simulators have already been loaded
		$query = db_select('sch_simulators', 's');
		$query->fields('s', array('simulator_id'));
		$results = $query->execute();
		if ($results->rowCount() > 1) {
			drupal_set_message('Unable to load QMS simulators.  The simulator table already has simulator records');
			return False;
		}
		foreach($results as $row) {
			if ( $row->simulator_id != 1) {
				drupal_set_message('Unable to load QMS simulators.  ' .
												'The simulator table already has simulator records');
				return False;
			}
			break;
		}

		if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

			$query = db_select('qms_simulators', 's');
			$query->fields('s', array('simulator_id', 'sim_name', 'faa_id',
                                'device_id_internal', 'active'));
			$results = $query->execute();

			if ( $results->rowCount() ) {
				$values = $results->fetchAll();
			}
			_st_disconnect_database(SCH_SCHEDULER);
		}

		if ( !count($values) ) {
			drupal_set_message('QMS Simulators not found');
			return False;
		}

		db_delete('sch_simulators')
				->condition('simulator_id', 1)
				->execute();

		$query = db_insert('sch_simulators')
						 ->fields(array('simulator_id', 'sim_name', 'faa_id', 'device_id_internal',
                            'active', 'created_date', 'created_by_user'));


	  for( $i = 0; $i < count($values); $i++) {
				$v = $values[$i];
				$record = array(
						'simulator_id' => $v->simulator_id,
						'sim_name' => $v->sim_name,
            'faa_id' => $v->faa_id,
            'device_id_internal' => $v->device_id_internal,
						'active' => $v->active,
						'created_date' => REQUEST_TIME,
						'created_by_user' => 1,
				);
	    	$query->values($record);
	  }
	  $num = $query->execute();

		$content = "QMS Simulators have been successfully loaded";
		return $content;

	}
	catch(Exception $e) {
		watchdog(SCH_SCHEDULER, '_qms_load_simulators() ' . $e->getMessage(), array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
	}
}


/*
 * _qms_simulator_get_status()
 *
 * IN:       $simulator_id
 * RETURNS:  $sim_status = array(
 *              'status_code' =>  [D,C,B,A,6,5,4], // 1 char
 *              'preflight_not_cleared' => n,      // indicator preflight
 *                                                 // not cleared in past 24hrs
 *              'open_items' => n,                 // count of open DRs for this sim
 *              'recent_closed' => n,              // count of closed DRs
 *                                                        (past 48hrs) for this sim
 *              'sim_files' => 0,                  // files attached to sim
 *           );
 */

function _qms_simulator_get_status($simulator_id) {

  try {

    $sim_status = array(
        'status_code' => 0,
        'preflight_not_cleared' => 0,
        'open_items' => 0,
        'mmi_items' => 0,
        'recent_closed' => 0,
        'sim_files' => 0,
    );

    if ( !$simulator_id ) return $sim_status;

    if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

//      $prev_24_hours = strtotime('- 24 hours', REQUEST_TIME);
//      $prev_48_hours = strtotime('- 48 hours', REQUEST_TIME);

      $prev_24_hours = _st_modify_system_timestamp(0, '-24 hours');
      $prev_48_hours = _st_modify_system_timestamp(0, '-48 hours');


      // get the operational status_code
      $sql = "SELECT status_code FROM qms_simulators WHERE simulator_id = :sid";
      $sim_status['status_code'] = db_query($sql, array(':sid' => $simulator_id))->fetchField();

      /*  REVISED PREFLIGHT ALERTS to last closed 4/21/2013
      // get a count of any Preflight DRs not cleared from the past 24 hours
      $sql = "SELECT d.discrepancy_id, d.dr_no, d.preflight_email_sent,
              s.sim_name, , s.device_id_internal
              FROM {qms_discrepancy_log} d, {qms_simulators} s
              WHERE d.simulator_id = s.simulator_id
              AND d.simulator_id = :sid
              AND d.date_opened >= :t
              AND d.date_closed = 0
              AND d.preflight = 1";

      $result = db_query($sql, array( ':sid' => $simulator_id,
                                      ':t' => $prev_24_hours));
       */

      // determine when the last preflight was closed
      $sql = "SELECT d.discrepancy_id, d.dr_no, d.date_closed,
                     d.preflight_email_sent, s.sim_name, s.device_id_internal
              FROM {qms_discrepancy_log} d, {qms_simulators} s
              WHERE d.simulator_id = s.simulator_id
              AND d.simulator_id = :sid
              AND d.date_closed > 0
              AND d.preflight = 1
              ORDER BY d.date_closed DESC LIMIT 1";

      $result = db_query($sql, array( ':sid' => $simulator_id));
                                      //':t' => $prev_24_hours));

      if ( $result->rowCount() ) {
        $preflight_last_cleared = $result->fetchObject();

        //$sim_status['preflight_last_cleared'] =
        //                      $preflight_last_cleared->date_closed;
        if ( $preflight_last_cleared->date_closed < $prev_24_hours ) {
          $sim_status['preflight_not_cleared'] = 1;
        }
      }
      else {
        // no closed preflights found, flag as an error
        $sim_status['preflight_not_cleared'] = 1;
      }


      // get a count of Open DRs for this sim (non-preflight)
      $sql = "SELECT discrepancy_id
              FROM {qms_discrepancy_log}
              WHERE simulator_id = :sid
              AND date_closed = 0
              AND mmi = 0
              AND show_customer = 1";     //AND preflight = 0

      $result = db_query($sql, array( ':sid' => $simulator_id ));

      $sim_status['open_items'] = $result->rowCount();

      // get a count of MMI DRs for this sim
      $sql = "SELECT discrepancy_id
              FROM {qms_discrepancy_log}
              WHERE simulator_id = :sid
              AND date_closed = 0
              AND mmi = 1
              AND show_customer = 1";  //AND preflight = 0

      $result = db_query($sql, array( ':sid' => $simulator_id ));

      $sim_status['mmi_items'] = $result->rowCount();


      // get a count of any Closed DRs from the past 48 hours
      $sql = "SELECT discrepancy_id
              FROM {qms_discrepancy_log}
              WHERE simulator_id = :sid
              AND date_closed >= :t";

      $result = db_query($sql, array( ':sid' => $simulator_id,
                                      ':t' => $prev_48_hours));
      $sim_status['recent_closed'] = $result->rowCount();


      // get a count of any Files attached to this simulator
      $sql = "SELECT file_id
              FROM {qms_simulator_files}
              WHERE simulator_id = :sid";

      $result = db_query($sql, array( ':sid' => $simulator_id));
      $sim_status['sim_files'] = $result->rowCount();


      // Do we need to send the preflight alert email?
      if ( $sim_status['preflight_not_cleared'] ) {


        if ( isset($preflight_last_cleared->preflight_email_sent) &&
            !$preflight_last_cleared->preflight_email_sent ) {
          // preflight warning email hasn't been sent
          // send it now
          $email_list = _st_get_simulator_notification_email_list(SCH_SCHEDULER,
                                       $simulator_id, QMS_NOTIFY_PREFLIGHT_OPEN);

          if ( strlen($email_list) ) {

            $subject = 'Simulator Preflight Maintenance Not Cleared';
            $body = "Simulator, " .
                    _st_format_sim_name($preflight_last_cleared->sim_name,
                                     $preflight_last_cleared->device_id_internal) .
                    " has not cleared a preflight requirement".
                    " within the past 24 hours.";

           $email_result = _st_send_drupal_email(
                    $preflight_last_cleared->discrepancy_id . '_preflight',
                    $subject, $body, $email_list);

            if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) && $email_result ) {
              db_update('qms_discrepancy_log')
                ->fields(array('preflight_email_sent' => 1))
                ->condition('discrepancy_id',
                            $preflight_last_cleared->discrepancy_id)
                ->execute();
            }
          }
        }
      }

      _st_disconnect_database(SCH_SCHEDULER);
    }
    return $sim_status;
  }
  catch(Exception $e) {
		watchdog(SCH_SCHEDULER, '_qms_simulator_get_status()' . $e->getMessage(), array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
  }
}

/*
 * _qms_simulator_get_preflight_status()
 *
 * IN:       $simulator_id
 * RETURNS:  $preflight_not_cleared value ( 0 == all items were cleared, or
 *                                          1 == preflight not cleared for 24hrs )
 */

function _qms_simulator_get_preflight_status($simulator_id) {

  try {


    if ( !$simulator_id ) return False;

    $preflight_alert = 0;


    if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

//      $prev_24_hours = strtotime('- 24 hours', REQUEST_TIME);
      $prev_24_hours = _st_modify_system_timestamp(0, '-24 hours');

      $sql = "SELECT discrepancy_id, date_closed
              FROM {qms_discrepancy_log} d
              WHERE simulator_id = :sid
              AND d.date_closed > 0
              AND d.preflight = 1
              ORDER BY d.date_closed DESC LIMIT 1";

      $result = db_query($sql, array( ':sid' => $simulator_id));


      $preflight_last_cleared = $result->fetchObject();
      if ( isset($preflight_last_cleared->date_closed) &&
              $preflight_last_cleared->date_closed < $prev_24_hours ) {
        $preflight_alert = 1;
      }


      // get a count of any Preflight DRs not cleared from the past 24 hours
      /*
      $sql = "SELECT discrepancy_id
              FROM {qms_discrepancy_log}
              WHERE simulator_id = :sid
              AND date_opened >= :t
              AND date_closed = 0
              AND preflight = 1";

      $result = db_query($sql, array( ':sid' => $simulator_id,
                                      ':t' => $prev_24_hours));
      $preflight_count = $result->rowCount();
       *
       */

      _st_disconnect_database(SCH_SCHEDULER);
    }
    return $preflight_alert;
  }
  catch(Exception $e) {
		watchdog(SCH_SCHEDULER, '_qms_simulator_get_preflight_status()' . $e->getMessage(),
              array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
  }
}


/*
 *  _format_simulator_status_block()
 *
 *  Accepts the $simulator id and the status array from _qms_simulator_get_status()
 *  Formats it to display on the home pages and E-log
 *
 *  RETURNS:  html formatted string
 */

function _format_simulator_status_block($simulator_id, $sim_status,
                                        $dest_url = '' ) {

  if ( !$simulator_id || !isset($sim_status['status_code']) ) return '';

  drupal_add_js('(function($) {
										Drupal.behaviors.schSimStatus = {
											attach: function(context, settings) {

                      $(\'div.sch-preflight-warning-img\').bind("fadeToggle", function() {
                        $(this).fadeToggle(1000, function() {
                            $(this).trigger("fadeToggle");
                        });
                      }).trigger("fadeToggle");

											}
										}
									}(jQuery));', 'inline');


  $preflight_class = '';
  $preflight_image_url = '';
  $goto_url = '';

  if ($sim_status['preflight_not_cleared']) {
    $preflight_class = 'sch-preflight-warning-img';
    $preflight_image_url = QMS_IMAGES_DIR . 'x_white.gif';
  }
  else {
    $preflight_class = 'sch-preflight-img' ;
    $preflight_image_url = QMS_IMAGES_DIR . 'check_white.gif';
  }

  if ( strlen($dest_url) ) {
    $goto_url = '?destination=' . $dest_url;
  }


  $content = '<div class="sch-sim-status">';

                // sim status_code
  $content .=   '<div class="sch-sim-status-item sch-sim-status-code">' .
                  '<div class="sch-status-img">' .
                      $sim_status['status_code'] .
                  '</div>' .
                  '<p>' . t('Operational') . '<br />' . t('Status') . '</p>' .
                '</div>';

                // preflight status
                // just show the icon w/out the counter
                // warning icon (red) will flash when there are uncleared DPF items
  $content .=   '<div class="sch-sim-status-item sch-sim-status-preflight">' .
                  '<a href="' .
                      url('simulator/status/preflight/' . $simulator_id) .
                      $goto_url .
                      '"><div class="' . $preflight_class . '">' .
                          '<img src="' . $preflight_image_url . '" />' .
                        '</div>' .
                      //$sim_status['preflight_not_cleared'] . '</div>' .
                  '<p>' . t('Preflight') .'</p></a>' .
                '</div>';

                // open items
  $content .=   '<div class="sch-sim-status-item sch-sim-status-open-items">' .
                  '<a href="' .
                      url('simulator/status/openitems/' . $simulator_id) .
                        $goto_url .
                      '"><div class="sch-open-img">' .
                      $sim_status['open_items'] .
                  '</div>' .
                  '<p>' . t('Open Items') . '</p></a>' .
                '</div>';

                 // MMI items
  $content .=   '<div class="sch-sim-status-item sch-sim-status-mmi-items">' .
                  '<a href="' .
                      url('simulator/status/mmi/' . $simulator_id) .
                        $goto_url .
                      '"><div class="sch-mmi-img">' .
                      $sim_status['mmi_items'] .
                  '</div>' .
                  '<p>' . t('MMI Items') . '</p></a>' .
                '</div>';


                // recently closed items
  $content .=   '<div class="sch-sim-status-item sch-sim-status-closed-items">' .
                  '<a href="' .
                      url('simulator/status/closeditems/' . $simulator_id) .
                      $goto_url .
                      '"><div class="sch-closed-img">' .
                      $sim_status['recent_closed'] .
                  '</div>' .
                  '<p>' . t('Recently Closed') . '</p></a>' .
                '</div>';

                  // simulator files
  $content .=   '<div class="sch-sim-status-item sch-sim-status-files">' .
                  '<a href="' . url('simulator/status/files/' . $simulator_id) .
                      $goto_url .
                      '"><div class="sch-files-img">' .
                      $sim_status['sim_files'] .
                  '</div>' .
                  '<p>' . t('View Documents') . '</p></a>' .
                '</div>';


  $content .= '</div>';

  if ( $sim_status['preflight_not_cleared'] > 0 ) {
    // add a warning div
    $content .= _show_preflight_warning();
  }

  return $content;
}


/*
 * _show_preflight_warning()
 *
 */
function _show_preflight_warning() {
  $warning =  '<div class="sch-preflight-warning-box">' .
                  '<div class="sch-warning-img"></div>' .
                  '<p>' . t('Daily Preflight maintenance items have not been cleared!') . '</p>' .
                '</div>';

  return $warning;
}

/*
$subject = 'QMS NOTICE -- New Discrepancy Report for ' . $simulator_list->getName($discrepancy->simulator_id);
			$body = _notify_new_discrepancy_email($discrepancy, $dr_comments, $dr_parts, $dr_files);

			_st_send_drupal_email($discrepancy->discrepancy_id, $subject, $body, $emails);
*/


/*
 * _qms_simulator_discrepancy_items()
 *
 * IN:       $simulator_id
 * RETURNS:  formatted display of discrepancies based on mode (preflight not cleared, open, closed)
 */

function _qms_simulator_discrepancy_items($mode, $simulator_id) {

  try {


    if ( !$simulator_id ) return '';

    // get this from the scheduler db before switching to the QMS db
    $sim_name = _get_simulator_name($simulator_id);


    if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

      drupal_add_js('(function($) {
										Drupal.behaviors.schDiscrepancyItems = {
											attach: function(context, settings) {
                        $(".sch-dr-comments", context).hide();
												$(".sch-open-comment", context).click(function() {

                          var comments_block =
                              $(this).parents(".sch-dr-comments-block")
                                .find(".sch-dr-comments");
                          if ( comments_block.is(":hidden") ) {
                            comments_block.slideDown("slow");
                            $(this).text("Hide");
                          }
                          else {
                           comments_block.slideUp();
                            $(this).text("Show");
                          }
                          return false;
                        });
											}
										}
									}(jQuery));', 'inline');


      $table_header = array();
      $table_rows = array();

      // set table header
      // nothing special here.  Displaying this in a block for easier viewing
      switch ($mode) {
        case SCH_SIM_PREFLIGHT:
          $table_header = array(
            array( 'data' => 'Preflight Last Cleared', 'class' => 'qms-sim-discrepancy'),
          );
          break;
        case SCH_SIM_OPENITEMS:
        case SCH_SIM_MMIITEMS:
        case SCH_SIM_CLOSEDITEMS:
          $table_header = array(
            array( 'data' => 'Discrepancies', 'class' => 'qms-sim-discrepancy'),
          );
          break;

          /*
          $table_header = array(
            array( 'data' => 'DR No.', 'class' => 'qms-sim-drno'),
            array( 'data' => 'Opened', 'class' => 'qms-sim-opened'),
            //array( 'data' => 'Closed', 'class' => 'qms-sim-closed'),
            array( 'data' => 'Discrepancy', 'class' => 'qms-sim-discrepancy'),
            array( 'data' => 'Comments'),
          );
          break;

          $table_header = array(
            array( 'data' => 'DR No.', 'class' => 'qms-sim-drno'),
            array( 'data' => 'Opened', 'class' => 'qms-sim-opened'),
            array( 'data' => 'Closed', 'class' => 'qms-sim-closed'),
            array( 'data' => 'Discrepancy', 'class' => 'qms-sim-discrepancy'),
            array( 'data' => 'Comments'),
          );
          break;
           */
      }

      $order_field = 'date_opened';
      $sort_order = 'DESC';
      $cmt_col = 0;

      $query = db_select('qms_discrepancy_log', 'd');

      //$query->innerJoin('qms_simulators', 's', 'd.simulator_id = s.simulator_id');


      switch ($mode) {
        case SCH_SIM_PREFLIGHT:
          //$prev_24_hours = strtotime('- 24 hours', REQUEST_TIME);
          $prev_24_hours = _st_modify_system_timestamp(0, '-24 hours');

          $query->leftJoin('qms_employees', 'e', 'd.updated_by_user = e.user_id');
          $query->leftJoin('users', 'u', 'd.updated_by_user = u.uid');

          $query->condition('d.simulator_id', $simulator_id)
                ->condition('d.preflight', 1);
          $query->fields('e', array('full_name'))
                ->fields('u', array('name'));
          /*
          $pf_or = db_or()->condition('date_closed', 0)
                       ->condition('date_closed', $prev_24_hours, '<');
          $query->condition($pf_or);*/

          $order_field = 'd.date_closed';

          //$cmt_col = 3;		// index of column containing the comments
          break;
        case SCH_SIM_OPENITEMS:
          $query->condition('d.simulator_id', $simulator_id)
                ->condition('d.date_closed', 0)
                ->condition('d.mmi', 0)
                ->condition('d.show_customer', 1);
                //->condition('d.preflight', 0);

          //$cmt_col = 3;		// index of column containing the comments
          $order_field = 'd.updated_date';
          break;
        case SCH_SIM_MMIITEMS:
          $query->condition('d.simulator_id', $simulator_id)
                ->condition('d.date_closed', 0)
                ->condition('d.mmi', 1)
                ->condition('d.show_customer', 1);
                //->condition('d.preflight', 0);

          //$cmt_col = 3;		// index of column containing the comments
          $order_field = 'd.updated_date';
          break;
        case SCH_SIM_CLOSEDITEMS:
          $query->leftJoin('qms_corrective_actions', 'a',
                        'd.corrective_action_id = a.action_id');

          //$prev_48_hours = strtotime('- 48 hours', REQUEST_TIME);
          $prev_48_hours = _st_modify_system_timestamp(0, '-48 hours');

          $query->condition('d.simulator_id', $simulator_id)
                ->condition('d.date_closed', $prev_48_hours, '>=');

          //$cmt_col = 4;		// index of column containing the comments
          $query->fields('a', array('action'));

          $order_field = 'd.date_closed';
      }

      $query->fields('d', array('discrepancy_id', 'dr_no', 'tech_user_id',
                                'date_opened', 'date_closed', 'discrepancy',
                                'clear_time',
                                'corrective_action_id', 'updated_date'));

            //->fields('s', array('sim_name'));
      $query->orderBy($order_field, $sort_order);

      if ( SCH_SIM_PREFLIGHT == $mode ) {
        $query->range(0,1);
      }

      $results = $query->execute()->fetchAll();


      $i = 0;


      foreach( $results as $row ) {

        $row_data = '';

        //if ( !strlen($sim_name) ) { $sim_name = $row->sim_name; }

        switch ($mode) {
        case SCH_SIM_PREFLIGHT:

          $name = $row->full_name;
          if( !strlen($name) ) { $name = $row->name; }

          $row_data = $name . '<br />' .
                _st_format_system_date($row->date_closed, 'short');

          break;
        case SCH_SIM_OPENITEMS:
        case SCH_SIM_MMIITEMS:
        case SCH_SIM_CLOSEDITEMS:

          $row_data =
            '<span class="sch-dr-no">' . $row->dr_no . '</span><br />' .
            '<span class="sch-dr-opened">' . t('Opened') . ':</span>' .
                  _st_format_system_date($row->date_opened, 'short') . '<br />';

          if ( SCH_SIM_OPENITEMS == $mode ) {
            $row_data .=
              '<span class="sch-dr-updated">' . t('Updated') . ':</span>' .
                    _st_format_system_date($row->updated_date, 'short') . '<br />';
          }

          if ( SCH_SIM_MMIITEMS == $mode ) {
            $row_data .=
              '<span class="sch-dr-updated">' . t('Updated') .
                    ':</span>' .
                    _st_format_system_date($row->updated_date, 'short') . '<br />';
            $row_data .=
              '<span class="sch-dr-clear-time">' . t('Clear Time') .
                    ':</span>' . $row->clear_time . ' ' . t('days') . '<br />';
          }

          if ( SCH_SIM_CLOSEDITEMS == $mode ) {
            $row_data .=
              '<span class="sch-dr-closed">' . t('Closed') . ':</span>' .
                    _st_format_system_date($row->date_closed, 'short') . '<br />' .
              '<span class="sch-dr-action">' . t('Action') . ':</span>' .
                    $row->action . '<br />';
          }

          $row_data .=
            '<br /><span class="sch-dr-report">' . t('Report') . ':' . '</span><br />' .
            '<div class="sch-dr-text">' .  $row->discrepancy . '</div>';


          $comments = _qms_get_formatted_discrepancy_comments($row->discrepancy_id);

          if ( strlen($comments) ) {
            $row_data .=
              '<div class="sch-dr-comments-block">' .
                '<span class="sch-dr-comments-label">' .
                    t('Comments') .
                    ' [<a href="#" class="sch-open-comment">' . t("Show") . '</a>]:' .
                            '</span><br />' .
                '<div class="sch-dr-comments">' .
                  $comments .
                '</div>' .
              '</div>';


          }
          break;
        }

          /*
            //$row->discrepancy_id

          //$cmt_col = 3;		// index of column containing the comments
          //break;


//          $row_data = array(
//            $row->dr_no,
//            _st_format_system_date($row->date_opened, 'short'),
//            _st_format_system_date($row->date_closed, 'short'),
//            array('data' => $row->discrepancy, 'class' => 'qms-sim-discrepancy'),
//            $row->discrepancy_id,
//          );
          //$cmt_col = 4;
          //break;

           */
        //}

        $table_rows[] = array('data' => array($row_data));
        $i++;
      }

      /*
      if ( $i > 0 ) {

        for($c = 0; $c < count($table_rows); $c++) {
          $discrepancy_id = $table_rows[$c]['data'][$cmt_col];  // grab the discrep id, temporarily stored here
          $table_rows[$c]['data'][$cmt_col] = array(
                          'data' => _qms_get_formatted_discrepancy_comments($discrepancy_id),
                          'class' => array('qms-sim-dr-comments'));  // get the DR comments
        }
      }*/

      $content = '<h3>' . $sim_name . '</h3>';

      $content .= '<div id="qms-report-div">';

      $content .= theme( 'table', array(
        'header' => $table_header,
        'rows' => $table_rows,
        'empty' => t('None'),
      ));
      if ( $i && (SCH_SIM_PREFLIGHT != $mode) ) {
        // display row count
        $content .= '<p class="qms-text">Total Items: ' . $i . '</p>';
      }
      $content .= '</div>';


      _st_disconnect_database(SCH_SCHEDULER);

      return $content;
    }
    return $sim_status;
  }
  catch(Exception $e) {
		watchdog(SCH_SCHEDULER, '_qms_simulator_discrepancy_items()' . $e->getMessage(), array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
  }
}


/*
 * _qms_get_formatted_discrepancy_comments()
*/
function _qms_get_formatted_discrepancy_comments($discrepancy_id) {

	$content = '';

	if ( $discrepancy_id == 0 ) {
		return $content;
	}


	// get discrepancy comments
	$sql = "SELECT c.discrepancy_comment_id, u.name, c.`timestamp`, c.comment
					FROM {qms_discrepancy_comments} c, {users} u
					WHERE c.discrepancy_id = :did AND c.user_id = u.uid
					ORDER BY c.discrepancy_comment_id";
	$results = db_query($sql, array(':did' => $discrepancy_id));

	foreach ( $results as $row ) {
		$c  = '<div class="qms-comment-hdr">' . $row->name . ' - ' .
            _st_format_system_date($row->timestamp, 'short') . '</div>';
		$c .= $row->comment;
		$content .= $c;
	}
	return $content;
}


/*
 *    _qms_get_simulator_files($simulator_id)
 *
 *  IN:  $simulator_id (QMS & Scheduler maintain identical simulator_ids
 *  RETURN:  array of files for this simulator
 */
function _qms_get_simulator_files($simulator_id) {

  try {

    $file_list = array();

    if ( !$simulator_id ) { return $file_list; }


    if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

      $sql = "SELECT f.file_id, u.name, f.uploaded_timestamp, f.file_uri, f.file_name
              FROM {qms_simulator_files} f, {users} u
              WHERE f.simulator_id = :sid
              AND  f.user_id = u.uid  ORDER BY f.file_id";
      $files_list = db_query($sql, array(':sid' => $simulator_id))->fetchAll();

      _st_disconnect_database(SCH_SCHEDULER);


    }
    return $files_list;
  }
  catch(Exception $e) {
    watchdog(SCH_SCHEDULER, '_qms_get_simulator_files()' . $e->getMessage(), array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
  }
}


/*
 *  _qms_simulator_file_download()
 *
 *  IN: $file_id (referenced in QMS, not meaningful in Scheduler )
 *  RETURN: the requested file
 */

function _qms_simulator_file_download($file_id) {

  try {

    $file = (object) null;

    if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

      $sql = "SELECT f.file_uri, f.file_name, s.simulator_id, s.sim_name, s.device_id_internal " .
             "FROM {qms_simulator_files} f, {qms_simulators} s " .
              "WHERE f.simulator_id = s.simulator_id AND f.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 = $results->fetchObject();


      $qms_path = variable_get('sabre_qms_root');
      $base_file_path = variable_get('file_private_path', 'sites/default/files/private' );
      $sim_file = 'SIM'. $file->simulator_id . '/' . $file->file_name;

      $file_path = "$qms_path/$base_file_path/$sim_file";

      _st_disconnect_database(SCH_SCHEDULER);

      _st_return_requested_file($file_path);
    }


   }
  catch(Exception $e) {
    watchdog(SCH_SCHEDULER, '_qms_simulator_file_download()' . $e->getMessage(), array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
  }

}


/*
 *	_qms_create_discrepancy_report()
 *
 *  Creates the discrepancy in QMS
 *
 *  The only way to get here is if QMS & Scheduler are linked
 *  Otherwise, the option switch to route here does not appear
 */

function _qms_create_discrepancy_report($form, $form_state) {

  try {

    $assignment = new stdClass();
    $discrepancy = new stdClass();
    $emails = array();
    $discrepancy = new stdClass();
    $discrepancy_id = 0;
    $qms_result = 0;
    $sch_result = 0;

    $goto_url = '';


    // ------------------ GET IDS -----------------
    // $assignment_id will always be > 0
    // $elog_id may or may not exist when DR is created

    $assignment_id = (int)$form_state['values']['assignment_id'];
    $elog_id = (int)$form_state['values']['elog_id'];


    //------------------- ROUTING ---------------------------
    // determine the return routing url

    if ( user_access('view manage menu') ) {
      $destination = drupal_get_destination();
      $goto_url = $destination['destination'];

      if( !strlen($goto_url) || ($goto_url == current_path()) ) {
        $goto_url = '';

        if ( $elog_id ) {
          $goto_url = 'elog/view/' . $elog_id;
        }
//        else {
//          $goto_url = 'elog/assign/' . $assignment_id;
//        }
      }
    }
    else {
      $goto_url = '';
      unset($_GET['destination']);
    }


    //------------------- Get the Assignment ------------------
    // get the assignment that the DR will be linked to

    $assignment = _get_assignment($assignment_id);

    if ( $assignment == (object) Null ) {
      drupal_set_message(
        t('Oops!  Something seems to have gone wrong.  E-Log was saved, but the
          Session Assignment was not found.  Unable to create Discrepancy Report.'));
      drupal_goto($goto_url);
      return;
    }

    //-------------------- GET FORM VALUES ----------------------
    // DR tech user is always unassigned when created by training customer
    $discrepancy->tech_user_id = 0;
    // simulator ids are in sync between QMS & Scheduler
    $discrepancy->simulator_id = $assignment->simulator_id;
    $discrepancy->date_opened = REQUEST_TIME;
    $discrepancy->ata_chapter_id = 23;    // QMS_ATACHAPTER_OTHER
    $discrepancy->from_sch_assignment_id = $assignment_id;
    $discrepancy->show_customer = 1;      // ALWAYS SHOW CUSTOMER

    // extra info appended at the end from scheduler, to store it in QMS DR
    $dr_text = $form_state['values']['discrepancy_text_editor_value'];
    $append_text = $form_state['values']['append_text'];

    $discrepancy->discrepancy = _st_clean_ckeditor_text($dr_text, QMS_TEXTAREA_MAX);
    $discrepancy->discrepancy .= $append_text;

    // need to populate update date on create
    // so it gets picked up in the 24/72hr reports
    // behaves as a switch with created_date == updated_date (to the second)
    $discrepancy->created_date = REQUEST_TIME;
    $discrepancy->updated_date = REQUEST_TIME;

    // this always has to be qms.admin user as the customer will not have a login
    // for the QMS system... just a passthrough ID, tied to the assignment & elog
    // both are required to be populated when adding a DR
    $discrepancy->created_by_user = 1;
    $discrepancy->updated_by_user = 1;


    //--------------- CONNECT TO QMS --------------------
    // Must be connected to QMS db to do this
    if ( _st_connect_database(SCH_SCHEDULER, QMS_DB_QMS) ) {

      $baseno = _st_generate_report_base_no('DR');
      $discrepancy->dr_no = _st_generate_report_no('DR', $baseno);


      //----------------- Save to DB ------------------------
      // DO NOT USE drupal_write_record() here.
      // it will fail.  Relies on the schema library which is
      // currently set for Scheduler and not QMS


      $discrepancy_id = db_insert('qms_discrepancy_log')
                  ->fields(array(
                    'dr_no' => $discrepancy->dr_no,
                    'simulator_id' => $discrepancy->simulator_id,
                    'tech_user_id' => $discrepancy->tech_user_id,
                    'date_opened' => $discrepancy->date_opened,
                    'ata_chapter_id' => $discrepancy->ata_chapter_id,
                    'from_sch_assignment_id' => $discrepancy->from_sch_assignment_id,
                    'show_customer' => $discrepancy->show_customer,
                    'discrepancy' => $discrepancy->discrepancy,
                    'created_date' => $discrepancy->created_date,
                    'created_by_user' => $discrepancy->created_by_user,
                    'updated_date' => $discrepancy->updated_date,
                    'updated_by_user' => $discrepancy->updated_by_user,
                  ))->execute();

        $discrepancy = _qms_get_discrepancy($discrepancy_id);

        $emails = _st_get_simulator_notification_email_list(SCHEDULER_APP,
                                                      $assignment->simulator_id,
                                                      QMS_NOTIFY_DISCREPANCY_NEW);

        _st_disconnect_database(SCH_SCHEDULER);
      }


    if ( $discrepancy_id ) {
      // there could be multiple DRs filed for a single session
      // the linkage is contained within the qms_discrepancy_log table
      // back to the assignment
      db_update('sch_assignments')
                  ->fields(array('discrepancy_reported' => 1))
                  ->condition('assignment_id', $assignment_id)
                  ->execute();

      $msg = 'Discrepancy Report ' . $discrepancy->dr_no .
              ' has been created successfully.  ' .
              'Thank you!';

      if (!empty($emails)) {
        // send the new discrepancy notifications
        global $user;

        // add some extra non-standard fields to the discrepany record
        $discrepancy->created_by_user_email = _st_get_user_email($user->uid);  // this may return '' if created_by_user==0
        $discrepancy->user_link = ( strlen($discrepancy->created_by_user_email) ?
                        l($user->name, 'mailto:' . $discrepancy->created_by_user_email) : $user->name );

        $subject = t('QMS NOTICE -- ') . t('New Discrepancy Report') . t(' for ') .
                  $discrepancy->sim_name;
        $body = _qms_notify_new_discrepancy_email($discrepancy);

        _st_send_drupal_email($discrepancy->discrepancy_id, $subject, $body, $emails);

        watchdog(SCH_SCHEDULER, "New Discrepancy Emails Sent: ". var_export($emails, True), array(), WATCHDOG_NOTICE);
      }
    }
    else {
      $msg = 'A problem occurred while attempting the create the discrepancy report.             Unable to create the discrepancy.';
    }
    drupal_set_message(t($msg));
    drupal_goto($goto_url);
  }
  catch(Exception $e) {
    watchdog(SCH_SCHEDULER, '_qms_create_discrepancy_report()' . $e->getMessage(), array(), WATCHDOG_ERROR);
    _st_disconnect_database(SCH_SCHEDULER);
  }

}


/*
 * _st_get_discrepancy($discrepancy_id)
 * Returns and object pointing to the discrepancy record data
 */

function _qms_get_discrepancy($discrepancy_id) {
	$discrepancy = Null;


	$sql = "SELECT d.discrepancy_id, d.dr_no, d.tech_user_id, d.orig_tech_user_id,
          d.ata_chapter_id, d.simulator_id, d.discrepancy, d.corrective_action_id,
          d.date_opened, d.date_closed, d.faa, d.mmi,
          d.clear_time, d.parts_used, d.files_attached,
          d.has_comments, d.show_customer, d.engineering, d.is_pm, d.preflight,
          d.from_trouble_call_id, d.from_sch_assignment_id,
          d.qtg_event_id, d.qtg_test_id,
          d.created_date, d.created_by_user, d.updated_date, d.updated_by_user,
          s.sim_name
					FROM {qms_discrepancy_log} d, {qms_simulators} s
					WHERE d.discrepancy_id = :did
          AND d.simulator_id = s.simulator_id";
	$result = db_query($sql, array(':did' => $discrepancy_id));

	if ( $result->rowCount() ) {
		$discrepancy = $result->fetchObject();
	}
	return $discrepancy;
}


/*
 *
 *
 *	_st_notify_new_discrepancy_email() - for both apps
 *
 */

function _qms_notify_new_discrepancy_email($discrepancy,
                                          $dr_comments = array(),
                                          $dr_parts = array(),
                                          $dr_files = array()) {

  /*
	$email = _st_get_user_email($discrepancy->created_by_user);  // this may return '' if created_by_user==0
	$user_link = ( strlen($email) ? l(_get_user_name($discrepancy->created_by_user), 'mailto:' . $email) : _get_user_name($discrepancy->created_by_user) );
   *
   */

	$body =
		'<h2><strong>New Discrepancy</strong></h2>' .
		'<hr />' .
		'<p>This discrepancy was created:  <br />' .
		( $discrepancy->created_by_user ? $discrepancy->user_link . ', ' : '' ) .
		( $discrepancy->created_date ? _st_format_date($discrepancy->created_date, 'medium') : '') .
		'</p>' .
		'<hr />';

	$body .= _qms_format_discrepancy_report_for_email($discrepancy, $dr_comments, $dr_parts, $dr_files);

	return $body;
}

/*
 *	_format_discrepancy_report_for_email()
 *
 *   Format the discrepancy report information for email
 *   This is slightly different than the one in QMS
 */

function _qms_format_discrepancy_report_for_email($discrepancy,
                                              $dr_comments = array(),
                                              $dr_parts = array(),
                                              $dr_files = array()) {


	///////////////////////////////////////////////
	// format the contents of the DR for email  //
	///////////////////////////////////////////////
	global $chapter_list;
	global $simulator_list;


	// DR Header Information
	$body =
		'<table>' .
		'<tr><td colspan="2"><h2><strong>' . $discrepancy->dr_no . '</strong></h2></td></tr>' .
    /*
		'<tr><td><strong>Technician/Instructor</strong></td><td>' .
         _get_user_name($discrepancy->tech_user_id) . '</td></tr>' .
     *
     */
		'<tr><td><strong>Simulator</strong></td><td>' .
         $discrepancy->sim_name . '</td></tr>' .
    /*
		'<tr><td><strong>ATA Chapter</strong></td><td>' .
         $chapter_list->getName($discrepancy->ata_chapter_id) . '</td></tr>' .
     *
     */
		'<tr><td><strong>Date Opened</strong></td><td>' .
         _st_format_date($discrepancy->date_opened, 'short') . '</td></tr>';
    /*
		'<tr><td><strong>Engineering</strong></td><td>' .
         ($discrepancy->engineering ? 'Yes' : 'No') . '</td></tr>';
    '<tr><td><strong>Prev. Maint.</strong></td><td>' .
         ($discrepancy->is_pm ? 'Yes' : 'No') . '</td></tr>';
    '<tr><td><strong>Preflight</strong></td><td>' .
         ($discrepancy->preflight ? 'Yes' : 'No') . '</td></tr>';
     *
     */

	if ( $discrepancy->faa || $discrepancy->mmi ) {
		$body .=
			'<tr><td><strong>FAA/MMA</strong></td><td>' .
          ($discrepancy->faa ? 'FAA' : '') .
          ($discrepancy->mmi ? 'MMI' : '') .
					(($discrepancy->clear_time > 0) ? (' (' . $discrepancy->clear_time . 'days)') : '' ) .
			'</td></tr>';
	}

	// DR Text
	$body .=
		'<tr><td colspan="2">&nbsp;</td></tr>' .
		'<tr><td colspan="2"><h3><strong>Discrepancy</strong></h3></td></tr>' .
		'<tr><td colspan="2">' . $discrepancy->discrepancy . '</td></tr>' .
		'<tr><td colspan="2">&nbsp;</td></tr>';

  /*

	// DR Comments
	if ( count($dr_comments) ) {
		$body .=
			'<tr><td colspan="2">&nbsp;</td></tr>' .
			'<tr><td colspan="2"><h3><strong>Comments</strong></h3></td></tr>';

		foreach ( $dr_comments as $cmt ) {
			$body .= '<tr><td colspan="2">' . $cmt->name . ' - ' . _st_format_date($cmt->timestamp, 'short') . '<hr />' .
										$cmt->comment . '</td></tr>' .
							 '<tr><td colspan="2">&nbsp;</td></tr>';
		}
	}

	// DR Parts
	if ( count($dr_parts) ) {
		$body .=
			'<tr><td colspan="2">&nbsp;</td></tr>' .
			'<tr><td colspan="2"><h3><strong>Parts</strong></h3></td></tr>';

		foreach ( $dr_parts as $pt ) {
			$body .=
        '<tr><td><strong>Part No.:   </strong></td><td>' . $pt->part_no . '</td></tr>' .
         '<tr><td><strong>Quantity:   </strong></td><td>' . $pt->quantity . '</td></tr>' .
         '<tr><td><strong>Description:</strong></td><td>' . $pt->description . '</td></tr>' .
         '<tr><td><strong>S/N In:</strong></td><td>' . $pt->serial_number_in . '</td></tr>' .
         '<tr><td><strong>S/N Out:</strong></td><td>' . $pt->serial_number_out . '</td></tr>' .
         '<tr><td colspan="2">&nbsp;</td></tr>';
		}
	}

	// DR Files
	if ( count($dr_files) ) {
		$body .=
			'<tr><td colspan="2">&nbsp;</td></tr>' .
			'<tr><td colspan="2"><h3><strong>Files</strong></h3></td></tr>' .
			'<tr><td colspan="2">You must log into the QMS to access the associated files</td></tr>';

		foreach ( $dr_files as $f ) {
			$body .= '<tr><td><strong>Uploaded:   </strong>' .
                $f->name . ' - ' . _st_format_date($f->uploaded_timestamp, 'short') . '</td><td>' .
								$f->file_name . '</td></tr>' .
							 '<tr><td colspan="2">&nbsp;</td></tr>';
		}
	}
   *
   */

	// End of DR
	$body .= '</table>';

	return $body;
}