<?php
require_once('../_connections/conORTHO.php');
$hospcode ='';
$pid ='';
$diagcode ='';
$diagall ='';
$typein ='';
$referinhosp ='';
$causein ='';
$typeout ='';
$referouthosp ='';
$causeout ='';
$seq ='';
$clinic ='';
$date_serv ='';
$dischtype ='';
$an ='';
$warddiag ='';
$datetime_admit ='';
$chwname ='';
$hname ='';
$splevel ='';
$total ='';
$hcode ='';
$refer ='';
$percent ='';
$d_start ='2017-01-01';
$d_end ='2017-02-28';
$recno=1; //นับ จำนวน record
$strSQL = "CREATE TABLE if not EXISTS tmp_kpi1
REPLACE
SELECT
serv.hospcode,
serv.pid,
GROUP_CONCAT(dopd.diagcode) as diagall,
serv.typein,
serv.referinhosp,
serv.causein,
serv.typeout,
serv.referouthosp,
serv.causeout
FROM service serv
INNER JOIN diagnosis_opd dopd on dopd.hospcode=serv.hospcode
AND dopd.pid=serv.pid
AND dopd.seq=serv.seq
AND dopd.date_serv=serv.date_serv
WHERE SUBSTR(dopd.clinic, 2, 2)='08'
AND serv.date_serv BETWEEN '$d_start' AND '$d_end'
GROUP BY serv.hospcode,serv.pid
UNION
SELECT
adm.hospcode,
adm.pid,
GROUP_CONCAT(dipd.diagcode) as diagall,
adm.typein,
adm.referinhosp,
adm.causein,
adm.dischtype,
adm.referouthosp,
adm.causeout
FROM admission adm
INNER JOIN diagnosis_ipd dipd ON dipd.hospcode=adm.hospcode
AND dipd.PID=adm.PID
AND dipd.an=adm.an
WHERE SUBSTR(dipd.warddiag, 2, 2)='08'
AND adm.datetime_admit BETWEEN '$d_start' AND '$d_end'
GROUP BY adm.hospcode,adm.pid ;
SELECT tmppt_all.chwname,tmppt_all.hospcode,
tmppt_all.hname,tmppt_all.splevel,
tmppt_all.total,tmppt_refer.refer,
round((IFNULL(tmppt_refer.refer,0) /tmppt_all.total)*100,2) as percent
from (SELECT
ccm.chwname,
k01.hospcode,
ccm.hname,
ccm.splevel,
COUNT(k01.pid) as total
FROM tmp_kpi1 k01
LEFT JOIN ccmihosp ccm on ccm.hcode=k01.hospcode and byear=(543 + YEAR(CURDATE()))
LEFT JOIN chospital cho on cho.hoscode=k01.hospcode
WHERE ccm.chwcode IN('70','71','72','73','74','75','76','77'))tmppt_all
LEFT JOIN (SELECT k01.hospcode,COUNT(k01.pid) as refer
FROM tmp_kpi1 k01
LEFT JOIN ccmihosp ccm on ccm.hcode=k01.hospcode and byear=(543 + YEAR(CURDATE()))
LEFT JOIN chospital cho on cho.hoscode=k01.hospcode
WHERE k01.referouthosp <>''
AND ccm.chwcode IN('70','71','72','73','74','75','76','77'))
tmppt_refer on tmppt_all.hospcode=tmppt_refer.hospcode ";
$objQuery = mysql_query($strSQL) or die (mysql_error());
while($objResult = mysql_fetch_array($objQuery))
{
$recno=$recno;
$chwname=$objResult["chwname"];
$hname=$objResult["hname"];
$splevel=$objResult["splevel"];
$total=$objResult["total"];
$refer=$objResult["refer"];
$percent=$objResult["percent"];
echo "<tr bgcolor=\"#E6E6AA\">\n";
echo "<td class=\"FontSize8\"align=center><nobr>$recno</nobr></td>\n";
echo "<td class=\"FontSize8\"align=left><nobr>$chwname</nobr></td>\n";
echo "<td class=\"FontSize8\"align=left><nobr>$hname</nobr></td>\n";
echo "<td class=\"FontSize8\"align=center><nobr>$splevel</nobr></td>\n";
echo "<td class=\"FontSize8\"align=center><nobr>$total</nobr></td>\n";
echo "<td class=\"FontSize8\"align=center><nobr>$refer</nobr></td>\n";
echo "<td class=\"FontSize8\"align=center><nobr>$percent</nobr></td>\n";
echo "</tr>\n";
$recno++;
}
echo "</table>";
mysql_close($conORTHO);
?>