/**
  * Reads all the Readings for a specific observation.
  *
  * @param id {Integer}
  *      The database Id of the observation.
  *
  * @return {Array{Object}}
  *
  * @throws {Exception}
  *      Can throw an exception if an SQL error occurs. See "triggerError"
  */
 protected function getReadings($id)
 {
     $readings = array();
     $statement = $this->db->prepare('SELECT * FROM reading WHERE ' . 'observation_id = :id ORDER by set_number');
     $statement->bindParam(':id', $id, PDO::PARAM_INT);
     try {
         $statement->execute();
         while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
             $reading_id = intval($row['ID']);
             $measurements = $this->getMeasurements($reading_id);
             $reading = new Reading($reading_id, intval($row['observation_id']), safeintval($row['set_number']), $row['declination_valid'], safeintval($row['declination_shift']), $row['horizontal_intensity_valid'], $row['vertical_intensity_valid'], $row['startH'], $row['endH'], $row['absH'], $row['baseH'], $row['startZ'], $row['endZ'], $row['absZ'], $row['baseZ'], $row['startD'], $row['endD'], $row['absD'], $row['baseD'], $row['annotation'], $measurements);
             $readings[] = $reading;
         }
     } catch (Exception $ex) {
         $this->triggerError($statement);
     }
     $statement->closeCursor();
     return $readings;
 }
 /**
  * Get baseline observations.
  *
  * @param $observatory {String}
  *     observatory code, e.g. 'BOU'.
  * @param $startTime {Number}
  *     unix epoch timestamp.
  * @param $endTime {Number}
  *     unix epoch timestamp.
  * @param $includeMeasurements {Boolean} default false
  *     whether to include raw measurements for each observation.
  * @return {Array<Array>}
  *     array of observations matching criteria.
  */
 public function getObservations($observatory, $startTime, $endTime, $includeMeasurements = false)
 {
     $observations = array();
     // load observations
     $observationStatement = $this->db->prepare('
   SELECT
     o.id,
     o.begin,
     o.end,
     o.pier_temperature,
     o.elect_temperature,
     o.flux_temperature,
     o.proton_temperature,
     o.outside_temperature,
     o.mark_id,
     o.electronics_id,
     o.theodolite_id,
     o.reviewed,
     ei.serial_number as electronics_serial,
     ti.serial_number as theodolite_serial,
     m.azimuth as mark_azimuth,
     m.name as mark_name,
     p.id as pier_id,
     p.correction as pier_correction,
     p.name as pier_name,
     ou.name as observer_name,
     ru.name as reviewer_name
   FROM observation o
   JOIN observatory obs ON (obs.id = o.observatory_id)
   LEFT JOIN instrument ei ON (ei.id = o.electronics_id)
   LEFT JOIN instrument ti ON (ti.id = o.theodolite_id)
   LEFT JOIN mark m ON (m.id = o.mark_id)
   LEFT JOIN pier p ON (p.id = m.pier_id)
   LEFT JOIN user ou ON (ou.id = o.observer_user_id)
   LEFT JOIN user ru ON (ru.id = o.reviewer_user_id)
   WHERE obs.code = :observatory
   AND o.begin >= :startTime
   AND o.begin <= :endTime
   ORDER BY o.begin
 ');
     try {
         $observationStatement->execute(array('observatory' => $observatory, 'startTime' => $startTime, 'endTime' => $endTime));
         while ($row = $observationStatement->fetch(PDO::FETCH_ASSOC)) {
             $observations[$row['id']] = array('id' => safeintval($row['id']), 'time' => safeISO8601($row['begin']), 'pier_temperature' => safefloatval($row['pier_temperature']), 'elect_temperature' => safefloatval($row['elect_temperature']), 'flux_temperature' => safefloatval($row['flux_temperature']), 'proton_temperature' => safefloatval($row['proton_temperature']), 'outside_temperature' => safefloatval($row['outside_temperature']), 'reviewed' => $row['reviewed'] === 'Y', 'electronics' => array('id' => safeintval($row['electronics_id']), 'serial' => $row['electronics_serial']), 'theodolite' => array('id' => safeintval($row['theodolite_id']), 'serial' => $row['theodolite_serial']), 'mark' => array('id' => safeintval($row['mark_id']), 'azimuth' => safefloatval($row['mark_azimuth']), 'name' => $row['mark_name']), 'pier' => array('id' => safeintval($row['pier_id']), 'correction' => safefloatval($row['pier_correction']), 'name' => $row['pier_name']), 'observer' => $row['observer_name'], 'reviewer' => $row['reviewer_name']);
         }
     } finally {
         $observationStatement->closeCursor();
     }
     // load observation readings
     $readingStatement = $this->db->prepare('
   SELECT
     r.id,
     r.observation_id,
     r.set_number,
     r.declination_valid,
     r.declination_shift,
     r.horizontal_intensity_valid,
     r.vertical_intensity_valid,
     r.startH,
     r.endH,
     r.absH,
     r.baseH,
     r.startZ,
     r.endZ,
     r.absZ,
     r.baseZ,
     r.startD,
     r.endD,
     r.absD,
     r.baseD
   FROM reading r
   JOIN observation o ON (o.id = r.observation_id)
   JOIN observatory obs ON (obs.id = o.observatory_id)
   WHERE obs.code = :observatory
   AND o.begin >= :startTime
   AND o.begin <= :endTime
   ORDER BY r.observation_id, r.set_number
 ');
     try {
         $readingStatement->execute(array('observatory' => $observatory, 'startTime' => $startTime, 'endTime' => $endTime));
         while ($row = $readingStatement->fetch(PDO::FETCH_ASSOC)) {
             $observations[$row['observation_id']]['readings'][$row['id']] = array('id' => safeintval($row['id']), 'set' => safeintval($row['set_number']), 'D' => array('absolute' => safefloatval($row['absD']), 'baseline' => safefloatval($row['baseD']), 'end' => safeintval($row['endD']), 'shift' => safeintval($row['declination_shift']), 'start' => safeintval($row['startD']), 'valid' => $row['declination_valid'] === 'Y'), 'H' => array('absolute' => safefloatval($row['absH']), 'baseline' => safefloatval($row['baseH']), 'end' => safeintval($row['endH']), 'start' => safeintval($row['startH']), 'valid' => $row['horizontal_intensity_valid'] === 'Y'), 'Z' => array('absolute' => safefloatval($row['absZ']), 'baseline' => safefloatval($row['baseZ']), 'end' => safeintval($row['endZ']), 'start' => safeintval($row['startZ']), 'valid' => $row['vertical_intensity_valid'] === 'Y'));
         }
     } finally {
         $readingStatement->closeCursor();
     }
     // load reading measurements
     if ($includeMeasurements) {
         $measurementStatement = $this->db->prepare('
     SELECT
       r.observation_id,
       m.id,
       m.reading_id,
       m.type,
       m.time,
       m.angle,
       m.h,
       m.e,
       m.z,
       m.f
     FROM measurement m
     JOIN reading r ON (r.id = m.reading_id)
     JOIN observation o ON (o.id = r.observation_id)
     JOIN observatory obs ON (obs.id = o.observatory_id)
     WHERE obs.code = :observatory
     AND o.begin >= :startTime
     AND o.begin <= :endTime
     ORDER BY r.observation_id, r.set_number, m.time
   ');
         try {
             $measurementStatement->execute(array('observatory' => $observatory, 'startTime' => $startTime, 'endTime' => $endTime));
             while ($row = $measurementStatement->fetch(PDO::FETCH_ASSOC)) {
                 $observations[$row['observation_id']]['readings'][$row['reading_id']]['measurements'][] = array('id' => safeintval($row['id']), 'type' => $row['type'], 'time' => safeintval($row['time']), 'angle' => safefloatval($row['angle']), 'h' => safefloatval($row['h']), 'e' => safefloatval($row['e']), 'z' => safefloatval($row['z']), 'f' => safefloatval($row['f']));
             }
         } finally {
             $measurementStatement->closeCursor();
         }
     }
     // convert Array<id => object> to Array<object>
     foreach ($observations as $id => &$observation) {
         $observation['readings'] = array_values($observation['readings']);
     }
     $observations = array_values($observations);
     return $observations;
 }
 /**
  * Reads the all piers linked to a specific observatory.
  *
  * @param id {Integer}
  *      The database Id of the observatory.
  *
  * @return {Array{Object}}
  *
  * @throws {Exception}
  *      Can throw an exception if an SQL error occurs. See "triggerError"
  */
 public function getPiers($id)
 {
     $piers = array();
     $statement = $this->db->prepare('SELECT * FROM pier WHERE ' . 'observatory_id=:id ORDER BY name, begin');
     $statement->bindParam(':id', $id, PDO::PARAM_INT);
     try {
         $statement->execute();
         while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
             $pier_id = intval($row['ID']);
             $marks = $this->getMarks($pier_id);
             $pier = new Pier($pier_id, intval($row['observatory_id']), $row['name'], safeintval($row['begin']), safeintval($row['end']), safefloatval($row['correction']), safeintval($row['default_mark_id']), safeintval($row['default_electronics_id']), safeintval($row['default_theodolite_id']), $marks);
             $piers[] = $pier;
         }
     } catch (Exception $ex) {
         $this->triggerError($statement);
     }
     $statement->closeCursor();
     return $piers;
 }