public function actionIndex() { $ward = Yii::$app->user->identity->ward; $team = WorkloadTeam::find()->where('ward = :ward', [':ward' => $ward])->all(); // $event = NurseEvent::findOne($event_ref); $ward_name = Ward::find()->where('code=:code', [':code' => $ward])->one(); $sql = "SELECT bed.ward\n, bed.team_name\n, bed.bed_type\n, bed.bed_no\n, patient.patient_name\n, patient.pt_type\n, bed.team_ref\n\nFROM (SELECT a.ward\n, a.team_name\n, b.bed_type\n, b.bed_no\n, b.ref\n, a.ref as team_ref\n\nFROM workload_team a\nRIGHT JOIN workload_bed b ON a.ref = b.team_ref\n\nWHERE a.ward = '" . $ward . "') bed\nLEFT OUTER JOIN (SELECT c.bed_type\n, c.bed_no\n, CONCAT(c.title,c.`name`,' ',c.surname) patient_name\n, c.pt_type\nFROM nurse_patient c\nWHERE c.event_ref = \n(SELECT MAX(ref) \nFROM nurse_event \nWHERE ward = '" . $ward . "' AND patient_flag = '1' and disc_type=0)) patient \nON bed.bed_type = patient.bed_type\nAND bed.bed_no = patient.bed_no\nORDER BY bed.ref,bed.bed_no"; // $patient = NursePatient::findBySql($sql)->all(); // print_r($patient); $command = Yii::$app->db->createCommand($sql); $patient = $command->queryAll(); //var_dump($patient); //echo $sql; return $this->render('index', ['patient' => $patient, 'team' => $team]); }
public function actionAssignReport($event_ref) { $ward = Yii::$app->user->identity->ward; $event = NurseEvent::findOne($event_ref); $ward_name = Ward::find()->where('code=:code', [':code' => $ward])->one(); $team = WorkloadTeam::find()->where('ward = :ward', [':ward' => $ward])->all(); $sql = "SELECT bed.ward \n, bed.team_name \n, bed.bed_type \n, bed.bed_no \n, patient.title \n, patient.name \n, patient.surname \n, patient.pt_type\n, bed.team_ref\n, diag.diag\n, diag.last_update\n\nFROM (SELECT a.ward\n, a.ref as team_ref\n, a.team_name\n, b.bed_type\n, b.bed_no\n, b.ref\nFROM workload_team a\nRIGHT JOIN workload_bed b ON a.ref = b.team_ref\n\nWHERE a.ward = '" . $ward . "') bed\nLEFT OUTER JOIN (SELECT c.bed_type\n, c.bed_no\n, c.title\n, c.name\n, c.surname\n, c.pt_type\n, c.an\nFROM nurse_patient c\nWHERE c.event_ref = " . $event_ref . " and c.disc_type=0) patient ON bed.bed_type = patient.bed_type\n AND bed.bed_no = patient.bed_no\nLEFT JOIN (\nSELECT *\nFROM workload_diag\nWHERE (an,last_update) IN (SELECT an,MAX(last_update) FROM workload_diag GROUP BY an)\n) diag on patient.an = diag.an\n ORDER BY bed.ref,bed.bed_no"; // $patient = NursePatient::findBySql($sql)->all(); // print_r($patient); $command = Yii::$app->db->createCommand($sql); $patient = $command->queryAll(); //var_dump($patient); //echo $sql; return $this->renderPartial('assign_report', ['patient' => $patient, 'event' => $event, 'ward_name' => $ward_name, 'team' => $team]); }