protected function getApplications($date_from, $date_to, $firm = null)
{
$command = Yii::app()->db->createCommand()->select("p.id as patient_id, diag.left_diagnosis1_id, diag.left_diagnosis2_id, diag.right_diagnosis1_id, diag.right_diagnosis2_id, e.id,\n\t\t\t\t\t\tc.first_name, c.last_name, e.created_date, p.hos_num,p.gender, p.dob, eye.name AS eye, site.name as site_name,\n\t\t\t\t\t\tfirm.name as firm_name, concat(uc.first_name, ' ', uc.last_name) as created_user,\n\t\t\t\t\t\tps.left_treatment_id, ps.right_treatment_id, ps.left_nice_compliance, ps.right_nice_compliance")->from('et_ophcotherapya_therapydiag diag')->join('event e', 'e.id = diag.event_id')->join('user u', 'u.id = e.created_user_id')->join('contact uc', 'uc.id = u.contact_id')->join('episode ep', 'e.episode_id = ep.id')->join('patient p', 'ep.patient_id = p.id')->join('contact c', 'p.contact_id = c.id')->join('eye', 'eye.id = diag.eye_id')->join('et_ophcotherapya_mrservicein mrinfo', 'mrinfo.event_id = diag.event_id')->leftJoin('site', 'mrinfo.site_id = site.id')->join('firm', 'mrinfo.consultant_id = firm.id')->join('et_ophcotherapya_patientsuit ps', 'diag.event_id = ps.event_id')->where('e.deleted = 0 and ep.deleted = 0 and e.created_date >= :from_date and e.created_date < (:to_date + interval 1 day)')->order('e.created_date asc');
$params = array(':from_date' => $date_from, ':to_date' => $date_to);
if ($firm) {
$command->andWhere('(mrinfo.consultant_id = :consultant_id)');
$params[':consultant_id'] = $firm->id;
}
$results = array();
$cache = array();
foreach ($command->queryAll(true, $params) as $row) {
$record = array('application_date' => date('j M Y', strtotime($row['created_date'])), 'patient_hosnum' => $row['hos_num'], 'patient_firstname' => $row['first_name'], 'patient_surname' => $row['last_name'], 'patient_gender' => $row['gender'], 'patient_dob' => date('j M Y', strtotime($row['dob'])), 'eye' => $row['eye'], 'site_name' => $row['site_name'] ? $row['site_name'] : 'N/A', 'consultant' => $row['firm_name'], 'created_user' => $row['created_user'], 'left_diagnosis' => $this->getDiagnosisString($row['left_diagnosis1_id']), 'left_secondary_to' => $this->getDiagnosisString($row['left_diagnosis2_id']), 'right_diagnosis' => $this->getDiagnosisString($row['right_diagnosis1_id']), 'right_secondary_to' => $this->getDiagnosisString($row['right_diagnosis2_id']), 'left_treatment' => $this->getTreatmentString($row['left_treatment_id']), 'right_treatment' => $this->getTreatmentString($row['right_treatment_id']), 'left_compliant' => $this->sideCompliance('left', $row), 'right_compliant' => $this->sideCompliance('right', $row));
$this->appendSubmissionValues($record, $row['id']);
$this->appendInjectionValues($record, $row['patient_id'], $row['left_treatment_id'], $row['right_treatment_id']);
$results[] = $record;
}
return $results;
}