protected function queryData($surgeon, $dateFrom, $dateTo, $months = 4, $method = 'best', $type = 'distance')
{
$table = 'ophciexamination_visualacuity_reading';
if ($type !== 'distance') {
$table = 'ophciexamination_nearvisualacuity_reading';
}
$this->getExaminationEvent();
$this->command->select('pre_examination.episode_id, note_event.episode_id, note_event.event_date as op_date, note_event.id, op_procedure.eye_id, pre_reading.method_id,
pre_examination.event_date as pre_exam_date, post_examination.event_date as post_exam_date, pre_examination.id as pre_id, post_examination.id as post_id,
pre_reading.value as pre_value, post_reading.value as post_value')->from('et_ophtroperationnote_surgeon')->join('event note_event', 'note_event.id = et_ophtroperationnote_surgeon.event_id')->join('et_ophtroperationnote_procedurelist op_procedure', 'op_procedure.event_id = note_event.id #And the operation notes procedures')->join('episode', 'note_event.episode_id = episode.id')->join('event pre_examination', 'pre_examination.episode_id = note_event.episode_id AND pre_examination.event_type_id = :examination
AND pre_examination.event_date <= note_event.event_date', array('examination' => $this->examinationEvent['id']))->join('event post_examination', 'post_examination.episode_id = note_event.episode_id
AND post_examination.event_type_id = :examination
AND post_examination.event_date >= note_event.event_date
AND post_examination.created_date > note_event.created_date
AND post_examination.event_date BETWEEN DATE_ADD(note_event.event_date, INTERVAL :monthsBefore MONTH) AND DATE_ADD(note_event.event_date, INTERVAL :monthsAfter MONTH)', array('examination' => $this->examinationEvent['id'], 'monthsBefore' => $months - 1, 'monthsAfter' => $months + 1))->join('et_ophciexamination_visualacuity pre_acuity', 'pre_examination.id = pre_acuity.event_id
AND (pre_acuity.eye_id = op_procedure.eye_id
OR pre_acuity.eye_id = 3)')->join('et_ophciexamination_visualacuity post_acuity', 'post_examination.id = post_acuity.event_id
AND (post_acuity.eye_id = op_procedure.eye_id
OR post_acuity.eye_id = 3)')->join($table . ' pre_reading', 'pre_acuity.id = pre_reading.element_id
AND IF(op_procedure.eye_id = 1, pre_reading.side = 1, IF(op_procedure.eye_id = 2,
pre_reading.side = 0,
pre_reading.side IS NOT NULL))')->join($table . ' post_reading', 'post_acuity.id = post_reading.element_id
AND post_reading.side = pre_reading.side
AND post_reading.method_id = pre_reading.method_id')->where('surgeon_id = :surgeon', array('surgeon' => $surgeon))->andWhere('pre_examination.deleted <> 1 and post_examination.deleted <> 1 and note_event.deleted <> 1')->order('pre_exam_date asc, post_exam_date desc');
if ($dateFrom) {
$this->command->andWhere('note_event.event_date >= :dateFrom', array('dateFrom' => $dateFrom));
}
if ($dateTo) {
$this->command->andWhere('note_event.event_date <= :dateTo', array('dateTo' => $dateTo));
}
if ($method) {
if (is_numeric($method)) {
$this->command->andWhere('pre_reading.method_id = :method', array('method' => $method));
} else {
$this->command->join('ophciexamination_visualacuity_method', 'ophciexamination_visualacuity_method.id = pre_reading.method_id')->andWhere('ophciexamination_visualacuity_method.name = "Glasses"
OR ophciexamination_visualacuity_method.name = "Contact lens"
OR ophciexamination_visualacuity_method.name = "Unaided"');
}
}
return $this->command->queryAll();
}
