/**
* 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;
}