public function actionCardexReport()
{
$model = new Report();
if ($date_report = Yii::$app->request->post('Report')) {
$ward = Yii::$app->user->identity->ward;
$month = $date_report['month'];
$year = $date_report['year'];
$days = date('t', strtotime($year . '-' . $month . '-01'));
$this->callDatereport($month, $year, $days);
$month_name = $model->getMonthName($month);
$ward_name = $model->getWard($ward);
$sql = "select t.date,\n\tt.period,\n\ta.ward,\n\ta.admit1,\n\ta.admit2,\n\ta.admit3,\n a.admit4,\n\ta.disc1,\n\ta.disc2,\n\ta.disc3,\n\ta.disc4,\n\ta.disc5,\n\ta.disc6,\n\ta.prepare,\n\ta.cpr_time,\n\ta.cpr_no,\n\ta.respirator,\n\ta.rm_normal,\n\ta.rm_vip,\n\ta.disability,\n\ta.4a,\n\ta.3a,\n a.3b,\n\ta.2a,\n\ta.2b,\n\ta.2c,\n\ta.1a,\n\ta.1b,\n\ta.1c,\n\ta.1d,\n\tb.hn,\n\tb.rn,\n\tb.tn,\n\tb.pn,\n\tb.na,\n b.worker,\n\tb.total\nfrom tmp_report t LEFT JOIN\n(select \nne.ref,\nne.date,\nne.period,\nne.ward\n-- Admit\n,sum(if(p.admit_type=1,1,0)) as 'admit1'\n,sum(if(p.admit_type=2,1,0)) as 'admit2'\n,sum(if(p.admit_type=3,1,0)) as 'admit3'\n,sum(if(p.admit_type=4,1,0)) as 'admit4'\n-- Disc\n,sum(if(p.disc_type=1,1,0)) as 'disc1'\n,sum(if(p.disc_type=2,1,0)) as 'disc2'\n,sum(if(p.disc_type=3,1,0)) as 'disc3'\n,sum(if(p.disc_type=4,1,0)) as 'disc4'\n,sum(if(p.disc_type=5,1,0)) as 'disc5'\n,sum(if(p.disc_type=6,1,0)) as 'disc6'\n-- หัตถการ\n,sum(if(p.prepare=1,1,0)) as 'prepare'\n,sum(p.cpr) as 'cpr_time'\n,(if(p.cpr>0 and p.disc_type=0,1,0)) as 'cpr_no'\n,sum(if((p.uti>0 || p.vap>0 || p.phleb>0 || p.cutdown >0) and p.disc_type=0,1,0)) as 'respirator'\n-- คงพยาบาล\n,sum(if(p.bed_type in ('ส','น','ย','ท') and p.disc_type=0,1,0)) as 'rm_normal'\n,sum(if(p.bed_type='พ' and p.disc_type=0,1,0)) as 'rm_vip'\n,sum(if(p.disability=1 and p.disc_type=0,1,0)) as 'disability'\n-- จำแนกผู้ป่วย\n,sum(if(p.pt_type='4a' and p.disc_type=0,1,0)) as '4a'\n,sum(if(p.pt_type='3a' and p.disc_type=0,1,0)) as '3a'\n,sum(if(p.pt_type='3b' and p.disc_type=0,1,0)) as '3b'\n,sum(if(p.pt_type='2A' and p.disc_type=0,1,0)) as '2a'\n,sum(if(p.pt_type='2B' and p.disc_type=0,1,0)) as '2b'\n,sum(if(p.pt_type='2C' and p.disc_type=0,1,0)) as '2c'\n,sum(if(p.pt_type='1a' and p.disc_type=0,1,0)) as '1a'\n,sum(if(p.pt_type='1b' and p.disc_type=0,1,0)) as '1b'\n,sum(if(p.pt_type='1c' and p.disc_type=0,1,0)) as '1c'\n,sum(if(p.pt_type='1d' and p.disc_type=0,1,0)) as '1d'\nfrom nurse_event ne left join nurse_patient p on p.event_ref = ne.ref\nwhere (ne.date is not null and date != 0000-00-00) and ne.ward = " . $ward . " and month(date) = '" . $month . "' and year(date) = '" . $year . "'\ngroup by ne.date,ne.period,ne.ward\norder by ne.date,ne.period,ne.ward) a\non t.date = a.date and t.period = a.period\nleft join\n(select \tne.ref,\n\t\tne.date,\n\t\tne.period,\n\t\tne.ward,\n\t\tsum(if(s.hn =1,1,0)) as 'hn',\n\t\tsum(if(s.position in (1,2,3,4,5,10) and hn!=1,1,0)) as 'rn',\n\t\tsum(if(s.position in (6) and hn!=1,1,0)) as 'tn',\n\t\tsum(if(s.position in (7) and hn!=1,1,0)) as 'pn',\n\t\tsum(if(s.position in (8) and hn!=1,1,0)) as 'na',\n sum(if(s.position in (9) and hn!=1,1,0)) as 'worker',\n\t\tcount(se.event_ref) as 'total'\nfrom nurse_event ne left join nurse_staffevent se on ne.ref = se.event_ref\n\t\t\t\t\tleft join nurse_staff s on se.staff_ref = s.staff_ref\nwhere (ne.date is not null and date != 0000-00-00) and ne.ward = " . $ward . " and month(date) = '" . $month . "' and year(date) = '" . $year . "'\ngroup by date,period,ward) b\non a.ref = b.ref";
// $patient = NursePatient::findBySql($sql)->all();
// print_r($patient);
$command = Yii::$app->db->createCommand($sql);
$cardex = $command->queryAll();
$this->clearDate();
return $this->renderPartial('_cardexreport', ['cardex' => $cardex, 'month' => $month, 'year' => $year, 'month_name' => $month_name, 'ward_name' => $ward_name]);
} else {
return $this->render('_callcardexreport', ['model' => $model]);
}
}
