The followings are the available columns in table 'et_ophtroperationnote_procedurelist_procedure_assignment':
public function reportOperations($params = array())
{
$where = '';
if (strtotime($params['date_from'])) {
$where .= " and e.created_date >= '" . date('Y-m-d', strtotime($params['date_from'])) . " 00:00:00'";
}
if (strtotime($params['date_to'])) {
$where .= " and e.created_date <= '" . date('Y-m-d', strtotime($params['date_to'])) . " 23:59:59'";
}
if ($user = User::model()->findByPk($params['surgeon_id'])) {
$clause = '';
if (@$params['match_surgeon']) {
$clause .= "s.surgeon_id = {$user->id}";
}
if (@$params['match_assistant_surgeon']) {
if ($clause) {
$clause .= ' or ';
}
$clause .= "s.assistant_id = {$user->id}";
}
if (@$params['match_supervising_surgeon']) {
if ($clause) {
$clause .= ' or ';
}
$clause .= "s.supervising_surgeon_id = {$user->id}";
}
$where .= " and ({$clause})";
}
if (!($db = Yii::app()->params['report_db'])) {
$db = 'db';
}
foreach (Yii::app()->{$db}->createCommand()->select('p.hos_num, c.first_name, c.last_name, e.created_date, s.surgeon_id, s.assistant_id, s.supervising_surgeon_id, pl.id as pl_id, e.id as event_id, cat.id as cat_id, eye.name as eye')->from('patient p')->join('contact c', "c.parent_class = 'Patient' and c.parent_id = p.id")->join('episode ep', 'ep.patient_id = p.id')->join('event e', 'e.episode_id = ep.id')->join('et_ophtroperationnote_procedurelist pl', 'pl.event_id = e.id')->join('eye', 'pl.eye_id = eye.id')->join('et_ophtroperationnote_surgeon s', 's.event_id = e.id')->leftJoin('et_ophtroperationnote_cataract cat', 'cat.event_id = e.id')->where("p.deleted = 0 and c.deleted = 0 and ep.deleted = 0 and e.deleted = 0 and pl.deleted = 0 and s.deleted = 0 and (cat.id is null or cat.deleted = 0) {$where}")->order('e.created_date asc')->queryAll() as $row) {
$operations[] = array('date' => date('j M Y', strtotime($row['created_date'])), 'hos_num' => $row['hos_num'], 'first_name' => $row['first_name'], 'last_name' => $row['last_name'], 'procedures' => array(), 'complications' => array(), 'role' => $row['surgeon_id'] == $user->id ? 'Surgeon' : ($row['assistant_id'] == $user->id ? 'Assistant surgeon' : 'Supervising surgeon'));
foreach (OphTrOperationnote_ProcedureListProcedureAssignment::model()->findAll('procedurelist_id=?', array($row['pl_id'])) as $i => $pa) {
$operations[count($operations) - 1]['procedures'][] = array('eye' => $row['eye'], 'procedure' => $pa->procedure->term);
}
if ($row['cat_id']) {
foreach (OphTrOperationnote_CataractComplication::model()->findAll('cataract_id=?', array($row['cat_id'])) as $complication) {
$operations[count($operations) - 1]['complications'][] = array('complication' => $complication->complication->name);
}
}
}
return array('operations' => $operations);
}
/**
* Generate operation report.
*
* @param User $surgeon
* @param array $filter_procedures
* @param array $filter_complications
* @param $from_date
* @param $to_date
* @param array $appenders - list of methods to call with patient id and date to retrieve additional data for each row
*
* @return array
*/
protected function getOperations($surgeon = null, $filter_procedures = array(), $filter_complications = array(), $from_date, $to_date, $patient_oph_diagnoses, $booking_diagnosis, $theatre, $bookingcomments, $surgerydate, $comorbidities, $target_refraction, $cataract_surgical_management, $first_eye, $va_values, $refraction_values, $anaesthetic_type, $anaesthetic_delivery, $anaesthetic_comments, $anaesthetic_complications, $cataract_report, $incision_site, $cataract_complication_notes, $cataract_predicted_refraction, $cataract_iol_type, $cataract_iol_power, $tamponade_used, $surgeon, $surgeon_role, $assistant, $assistant_role, $supervising_surgeon, $supervising_surgeon_role, $opnote_comments, $surgeon_id)
{
$filter_procedures_method = 'OR';
$filter_complications_method = 'OR';
$command = Yii::app()->db->createCommand()->select('e.id, c.first_name, c.last_name, e.created_date, su.surgeon_id, su.assistant_id, su.supervising_surgeon_id, p.hos_num,p.gender, p.dob, pl.id as plid, cat.id as cat_id, eye.name AS eye')->from('event e')->join('episode ep', 'e.episode_id = ep.id')->join('patient p', 'ep.patient_id = p.id')->join('et_ophtroperationnote_procedurelist pl', 'pl.event_id = e.id')->join('et_ophtroperationnote_surgeon su', 'su.event_id = e.id')->join('contact c', 'p.contact_id = c.id')->join('eye', 'eye.id = pl.eye_id')->leftJoin('et_ophtroperationnote_cataract cat', 'cat.event_id = e.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('p.id, e.created_date asc');
$params = array(':from_date' => $from_date, ':to_date' => $to_date);
if ($surgeon_id) {
$command->andWhere('(su.surgeon_id = :user_id or su.assistant_id = :user_id or su.supervising_surgeon_id = :user_id)');
$params[':user_id'] = $surgeon_id;
}
$results = array();
$cache = array();
foreach ($command->queryAll(true, $params) as $row) {
set_time_limit(1);
$complications = array();
if ($row['cat_id']) {
foreach (OphTrOperationnote_CataractComplication::model()->findAll('cataract_id = ?', array($row['cat_id'])) as $complication) {
if (!isset($cache['complications'][$complication->complication_id])) {
$cache['complications'][$complication->complication_id] = $complication->complication->name;
}
$complications[(string) $complication->complication_id] = $cache['complications'][$complication->complication_id];
}
}
$matched_complications = 0;
if ($filter_complications) {
foreach ($filter_complications as $filter_complication) {
if (isset($complications[$filter_complication])) {
++$matched_complications;
}
}
if ($filter_complications_method == 'AND' && $matched_complications < count($filter_complications) || !$matched_complications) {
continue;
}
}
$procedures = array();
foreach (OphTrOperationnote_ProcedureListProcedureAssignment::model()->findAll('procedurelist_id = ?', array($row['plid'])) as $pa) {
if (!isset($cache['procedures'][$pa->proc_id])) {
$cache['procedures'][$pa->proc_id] = $pa->procedure->term;
}
$procedures[(string) $pa->proc_id] = $cache['procedures'][$pa->proc_id];
}
$matched_procedures = 0;
if ($filter_procedures) {
foreach ($filter_procedures as $filter_procedure) {
if (isset($procedures[$filter_procedure])) {
++$matched_procedures;
}
}
if ($filter_procedures_method == 'AND' && $matched_procedures < count($filter_procedures) || !$matched_procedures) {
continue;
}
}
$record = array('operation_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'], 'procedures' => implode(', ', $procedures), 'complications' => implode(', ', $complications));
$this->operation_date = strtotime($row['created_date']);
if ($surgeon) {
if ($row['surgeon_id'] == $surgeon_id) {
$record['surgeon_role'] = 'Surgeon';
} else {
if ($row['assistant_id'] == $surgeon_id) {
$record['surgeon_role'] = 'Assistant surgeon';
} else {
if ($row['supervising_surgeon_id'] == $surgeon_id) {
$record['surgeon_role'] = 'Supervising surgeon';
}
}
}
}
//appenders
$this->appendPatientValues($record, $row['id'], $patient_oph_diagnoses);
$this->appendBookingValues($record, $row['id'], $booking_diagnosis, $theatre, $bookingcomments, $surgerydate);
$this->appendOpNoteValues($record, $row['id'], $anaesthetic_type, $anaesthetic_delivery, $anaesthetic_comments, $anaesthetic_complications, $cataract_report, $incision_site, $cataract_complication_notes, $cataract_predicted_refraction, $cataract_iol_type, $cataract_iol_power, $tamponade_used, $surgeon, $surgeon_role, $assistant, $assistant_role, $supervising_surgeon, $supervising_surgeon_role, $opnote_comments);
$this->appendExaminationValues($record, $row['id'], $comorbidities, $target_refraction, $cataract_surgical_management, $first_eye, $va_values, $refraction_values);
$results[] = $record;
}
return $results;
}
/**
* Update the procedures for this element with the given ids.
*
* @param $procedure_ids
*
* @throws Exception
*/
public function updateProcedures($procedure_ids)
{
$current_procedures = array();
foreach ($this->procedure_assignments as $pa) {
$current_procedures[$pa->proc_id] = $pa;
}
foreach ($procedure_ids as $i => $proc_id) {
$display_order = $i + 1;
if (isset($current_procedures[$proc_id])) {
$procedure_assignment = $current_procedures[$proc_id];
if ($procedure_assignment->display_order != $display_order) {
$procedure_assignment->display_order = $display_order;
if (!$procedure_assignment->save()) {
throw new Exception('Unable to save procedure assignment');
}
}
unset($current_procedures[$proc_id]);
} else {
$procedure_assignment = new OphTrOperationnote_ProcedureListProcedureAssignment();
$procedure_assignment->procedurelist_id = $this->id;
$procedure_assignment->proc_id = $proc_id;
$procedure_assignment->display_order = $display_order;
if (!$procedure_assignment->save()) {
throw new Exception('Unable to save procedure assignment');
}
}
}
// delete remaining current procedures
foreach ($current_procedures as $pa) {
if (!$pa->delete()) {
throw new Exception('Unable to delete procedure assignment: ' . print_r($pa->getErrors(), true));
}
}
}
