function pregtest()
{
//function นัด pregtest
$village = $_GET[village];
if ($village == "00000000") {
$wvill = "";
} else {
$wvill = " AND house.villcode='{$village}' ";
}
if ($village == "00000000") {
$mu = "ทุกหมู่บ้าน";
} else {
$mu = getvillagename($village);
}
$str = retdaterangstr($_GET[str]);
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
$sql = "SELECT\nperson.pcucodeperson,\nperson.pid,\nperson.idcard,\nperson.fname,\nconcat(ctitle.titlename, person.fname , ' ' , person.lname) AS pname,\nperson.birth,\nROUND(DATEDIFF(now(),person.birth)/365.25) AS age,\nhouse.villcode,\nhouse.hno,\nhouse.hcode,\nhouse.xgis,\nhouse.ygis,\nvisit.visitdate,\nvisitfp.pregtest,\nvisitfp.pregtestresult,\nvisit.username\nFROM\nhouse\nINNER JOIN person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nLEFT JOIN ctitle on ctitle.titlecode = person.prename\nINNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid\nINNER JOIN visitfp ON visit.pcucodeperson = visitfp.pcucodeperson AND visit.pid = visitfp.pid AND visit.visitdate = visitfp.datefp\nwhere visitfp.pregtest = '17' and visit.visitdate between '{$str}' and '{$sto}' {$wvill}\norder by visit.visitdate,person.fname";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$userv = getusername($row[username]);
if ($row[pregtestresult] == "0") {
$pregtestname = "ไม่ตั้งครรภ์";
} elseif ($row[pregtestresult] == "1") {
$pregtestname = "ตั้งครรภ์";
} elseif ($row[pregtestresult] == "3") {
$pregtestname = "แปลผลไม่ได้";
} else {
$pregtestname = "";
}
if ($row[visitdate] == "") {
$appsick = "";
} else {
$appsick = retDatets($row[visitdate]);
}
$xml .= '<marker ';
$xml .= 'pid="' . $row[pid] . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'age="' . $row[age] . '" ';
$xml .= 'hno="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'sick="' . $appsick . '" ';
$xml .= 'labresult="' . $pregtestname . '" ';
$xml .= 'userv="' . $userv . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
$xml .= '/>';
}
$xml .= '</markers>';
echo $xml;
}
$gagename = "อายุ 60 ปี ขึ้นไป";
} else {
$gagename = "ทั้งหมด";
}
$sql = "select person.pcucodeperson,\nperson.pid,\nperson.fname,\nperson.idcard,\nCONCAT(ctitle.titlename,person.fname,' ',person.lname) AS pname,\nhouse.hno,\nhouse.hcode,\nhouse.villcode,\nhouse.xgis,\nhouse.ygis,\nperson.birth,\nperson.typelive,\ngetAgeYearNum(person.birth,'{$str}') AS age,\ncancer.datecheck,\ncancer.typecancer,\ncancer.result,\ncase when cancer.result = 'x' then 'รอผล'\nwhen cancer.result = '0' then 'ปกติ'\nwhen cancer.result = '1' then 'พบความผิดปกติ Cat II'\nwhen cancer.result = '2' then 'พบความผิดปกติ Cat III,IV'\nwhen cancer.result = '5' then 'Negative(-VIA)'\nwhen cancer.result = '6' then 'Positive(+VIA)'\nwhen cancer.result = '9' then 'พบวคามผิดปกตที่ไม่ใช่มะเร็ง' else null end as resultmean,\ncancer.hoslab,\ncancer.hosservice\nfrom person \ninner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode\ninner join village on house.villcode = village.villcode and village.villno <>'0'\nleft join ctitle on person.prename = ctitle.titlecode\nleft join (select visit.visitno,visit.pid,visitlabcancer.datecheck as datecheck,visitlabcancer.typecancer, visitlabcancer.result,\nvisitlabcancer.hosservice,\nvisitlabcancer.hoslab\nfrom visit inner join visitlabcancer on visit.visitno = visitlabcancer.visitno and visit.pcucode = visitlabcancer.pcucode\nwhere visitlabcancer.typecancer in ('2','3') and visitlabcancer.datecheck between '{$str}' and '{$sto}')cancer on person.pid = cancer.pid \nwhere ((person.dischargetype is null) or (person.dischargetype = '9')) and right(house.villcode,2) <> '00' and person.sex = '2' {$gage} {$wvill} {$chksto} {$live_type2}\norder by house.pcucode, house.villcode,person.fname";
$result = mysql_query($sql);
$txt = '<p align=\'center\'><b>ประชาชน ';
$txt .= "{$gagename} ที่ได้รับการตรวจคัดกรองมะเร็งปากมดลูก</b><br><b>{$mu} </b></p>ประชากร {$live_type_name} <br><b>{$hosp}</b><table width='99%' border='0' cellspacing='1' cellpadding='1' class='table table-striped table-hover table-bordered'>\n <tr>\n <th width='4%' scope='col'><div align='center'>ลำดับ</div></th>\n\t<th width='6%' scope='col'><div align='center'>HN</div></th>\n\t<th width='10%' scope='col'><div align='center'>เลขบัตรประชาชน</div></th>\n <th width='10%' scope='col'><div align='center'>ชื่อ - สกุล</div></th>\n\t<th width='5%' scope='col'><div align='center'>อายุ</div></th>\n <th width='6%' scope='col'><div align='center'>บ้านเลขที่</div></th>\n <th width='4%' scope='col'><div align='center'>หมู่ที่</div></th>\n\t<th width='9%' scope='col'><div align='center'>วันที่ตรวจ</div></th>\n <th width='15%' scope='col'><div align='center'>ผลการตรวจ</div></th>\n </tr>";
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$title = getTitle($row[prename]);
if ($row[datecheck] == "") {
$sick = "";
} else {
$sick = retDatets($row[datecheck]);
}
++$i;
if ($i % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$i}</div></td>\n\t<td><div align='center'>{$row['pid']}</div></td>\n\t<td><div align='center'>{$row['idcard']}</div></td>\n <td>{$row['pname']}</td>\n\t<td><div align='center'>{$row['age']}</div></td>\n <td><div align='center'>{$row['hno']}</div></td>\n <td><div align='center'>{$moo}</div></td>\n\t<td> {$sick}</td>\n <td> {$row['resultmean']}</td>\n </tr>";
}
$txt .= "</table><p div align='right' class='text-danger'>ข้อมูลระหว่างวันที่ {$strx} ถึง {$stox} </p></div><br>";
echo $txt;
} else {
header("Location: login.php");
}
?>
<?php
session_start();
if ($_SESSION[username]) {
include "includes/conndb.php";
include "includes/config.inc.php";
$dm = date("d/m");
$d = date("d");
$m = date("m");
$yx = date("Y");
$y = date("Y") + 543;
$daysdatestr = $d . "/" . $m . "/" . $y;
$daystart = "01/" . $m . "/" . $y;
$daylast = $yx . "/" . $m;
$daylast2 = lastday($daylast);
$dayend = retDatets($daylast2);
$daysdatepick = $dm . "/" . $y;
$daledcy = date("Y");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php
echo $titleweb;
?>
</title>
<link href="css/style.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="js/functions.js"></script>
<script type="text/javascript" src="js/jquery-1.4.2.min.js"></script>
//ได้ตัวแปร $hosp
$villcode = $_GET[village];
if ($villcode == "00000000") {
$wvill = "";
} else {
$wvill = " AND village.villcode='{$villcode}' ";
}
if ($villcode == "00000000") {
$mu = "ทุกหมู่บ้าน";
} else {
$mu = substr($_GET[village], 6, 2);
}
$str = retdaterangstr($_GET[str]);
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
//ตัวแปร array ที่ใ้ชสำหรับแสดงกราฟ
$villname = array();
// ตัวแปรแกน x
//ตัวแปรแกน y
$lv1 = array();
$lv2 = array();
$lv3 = array();
//หมดตัวแปรแกน y
//sql สำหรับดึงข้อมูล จาก jhcis
$sql = "select \ntmp_per.pcucodeperson,\nvillcode,\nvillname,\nsum(case when chk like 'ผอม' then 1 else 0 end)/sum(case when chk is not null then 1 else 0 end)*100 as lv1,\nsum(case when chk like 'ปกติ' then 1 else 0 end)/sum(case when chk is not null then 1 else 0 end)*100 as lv2,\nsum(case when chk like 'อ้วน' then 1 else 0 end)/sum(case when chk is not null then 1 else 0 end)*100 as lv3\nfrom\n(select\nperson.pcucodeperson,\nperson.pid,\nperson.fname, \nconcat(ctitle.titlename, person.fname , ' ' , person.lname) AS pname,\nperson.birth,\nROUND(DATEDIFF('{$str}',person.birth)/365.25) AS age,\nvillage.villcode,\nvillage.villname,\nhouse.hno,\nhouse.hcode,\nhouse.xgis,\nhouse.ygis\nFROM\nvillage\nInner Join house ON village.pcucode = house.pcucode AND village.villcode = house.villcode\nInner Join person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nInner Join ctitle ON person.prename = ctitle.titlecode\nwhere FLOOR((TO_DAYS('{$str}')-TO_DAYS(person.birth))/365.25) >59 and ((person.dischargetype is null) or (person.dischargetype = '9')) and SUBSTRING(house.villcode,7,2) <> '00' {$wvill}) as tmp_per\nleft join\n(select\nvisit.pcucodeperson,\nvisit.pid,\nmax(visit.visitdate) as m_visit,\nvisit.visitno,\nvisit.weight,\nvisit.height,\nvisit.weight/pow(visit.height/100,2) as bmi,\ncase when visit.weight/pow(visit.height/100,2) < 18.9 then 'ผอม' when (visit.weight/pow(visit.height/100,2) ) between 18.9 and 22.9 then 'ปกติ' when (visit.weight/pow(visit.height/100,2) ) > 22.9 then 'อ้วน' else '' end as chk\nFROM\nvillage\nInner Join house ON village.pcucode = house.pcucode and village.villcode = house.villcode\nInner Join person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nInner Join ctitle ON person.prename = ctitle.titlecode\nInner Join visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid\nInner Join visitdiag ON visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno\nwhere FLOOR((TO_DAYS('{$str}')-TO_DAYS(person.birth))/365.25) >59 and ((person.dischargetype is null) or (person.dischargetype = '9')) and SUBSTRING(house.villcode,7,2) <> '00' and visit.weight is not null and visit.height is not null\nand visit.visitdate between '{$str}' and '{$sto}' and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 )\ngroup by person.pid) as tmp_ncd\nON tmp_per.pcucodeperson = tmp_ncd.pcucodeperson AND tmp_per.pid = tmp_ncd.pid\ngroup by tmp_per.pcucodeperson,villcode";
//จบ sql
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
$lvr1 = number_format($row[lv1], 2, '.', '');
$lvr2 = number_format($row[lv2], 2, '.', '');
}
$str = retdaterangstr($_GET[str]);
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
$sql = "select\n*\nfrom\n(SELECT\np.pcucodeperson,\np.pid,\np.fname,\nCONCAT(ctitle.titlename,p.fname,' ',p.lname) AS pname,\nh.hno,\nh.villcode,\nh.xgis,\nh.ygis,\np.birth,\nFLOOR((TO_DAYS(NOW())-TO_DAYS(p.birth))/365.25) AS age\nFROM\nhouse AS h\nInner Join person AS p ON h.pcucode = p.pcucodeperson AND h.hcode = p.hcode\nInner Join cstatus ON p.marystatus = cstatus.statuscode\nleft Join ctitle ON p.prename = ctitle.titlecode\nWHERE ((p.dischargetype is null) or (p.dischargetype = '9')) AND\n\t\t\t\tSUBSTRING(h.villcode,7,2) <> '00' AND\n\t\t\t\tFLOOR((TO_DAYS(NOW())-TO_DAYS(p.birth))/365.25) > 59 {$wvill} ORDER BY h.villcode, p.fname\n) as per\nleft join \n(SELECT\nperson.pcucodeperson as pcucodeperson1,\nperson.pid as pid1,\ncount(distinct visit.visitno) as count_visit,\nvisit.symptoms,\nvisit.vitalcheck,\nvisitdiag.diagcode,\nmax(visit.visitdate) as visitdate,\nvisithomehealthindividual.homehealthtype,\nvisithomehealthindividual.patientsign,\nvisithomehealthindividual.homehealthdetail,\nvisithomehealthindividual.homehealthresult,\nvisithomehealthindividual.homehealthplan,\nvisithomehealthindividual.dateappoint,\nvisithomehealthindividual.`user`,\nconcat(ctitle.titlename,`user`.fname,' ',`user`.lname) as userh\nfrom\nvillage\nINNER JOIN house ON village.pcucode = house.pcucode AND village.villcode = house.villcode\nINNER JOIN person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nINNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid\ninner join visithomehealthindividual ON visit.pcucode = visithomehealthindividual.pcucode AND visit.visitno = visithomehealthindividual.visitno\nINNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno\ninner join `user` on `user`.username = visithomehealthindividual.`user`\nleft join ctitle on ctitle.titlecode = `user`.prename\nwhere visit.visitdate between '{$str}' and '{$sto}' and ((person.dischargetype is null) or (person.dischargetype = '9')) AND\n\t\t\t\tSUBSTRING(house.villcode,7,2) <> '00' AND \n\t\t\t\tFLOOR((TO_DAYS(NOW())-TO_DAYS(person.birth))/365.25) > 59 and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 )\ngroup by visit.pid) as fp\non per.pcucodeperson = fp.pcucodeperson1 and per.pid = fp.pid1\n{$chksto}\norder by villcode, fname";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$birth = retDatets($row[birth]);
if ($row[visitdate] == "") {
$sick = "--/--/----";
} else {
$sick = retDatets($row[visitdate]);
}
if ($row[count_visit] == "") {
$count_visit = "--";
} else {
$count_visit = $row[count_visit];
}
if ($row[count_visit] != "") {
$chk = "ได้รับการเยี่ยม";
} else {
$chk = "ไม่ได้เยี่ยม";
}
$xml .= '<marker ';
$xml .= 'hcode="' . $row[hcode] . '" ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
$ds = $_GET[ds];
if ($ds == '00') {
$ect = "";
} else {
$ect = " dc.group506code = '{$ds}' AND ";
}
$sql = "SELECT CONCAT(t.titlename,p.fname,' ',p.lname) AS pname,h.hno,h.villcode,h.xgis,h.ygis,vd.sickdatestart,dc.group506name\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\thouse AS h\n\t\t\t\t\t\t\tInner Join person AS p ON h.pcucode = p.pcucodeperson AND h.hcode = p.hcode\n\t\t\t\t\t\t\tInner Join visit AS v ON p.pcucodeperson = v.pcucodeperson AND p.pid = v.pid\n\t\t\t\t\t\t\tInner Join visitdiag506address AS vd ON v.pcucode = vd.pcucode AND v.visitno = vd.visitno\n\t\t\t\t\t\t\tInner Join cdisease AS d ON vd.diagcode = d.diseasecode\n\t\t\t\t\t\t\tInner Join cdisease506 AS dc ON d.code506 = dc.group506code\n\t\t\t\t\t\t\tInner Join ctitle AS t ON p.prename = t.titlecode\n\t\t\t\t\t\t\tWHERE {$ect} \n\t\t\t\t\t\t\tvd.sickdatestart BETWEEN '{$str}' AND '{$sto}' and dc.group506code in ('26','27','66') {$wvill}\n\t\t\t\t\t\t\tORDER BY vd.sickdatestart\n\t\t\t\t\t\t\t ";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$sick = retDatets($row[sickdatestart]);
$xml .= '<marker ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'dc="' . $row[group506name] . '" ';
$xml .= 'sick="' . $sick . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
$xml .= '/>';
}
$xml .= '</markers>';
echo $xml;
?>
<?php
set_time_limit(0);
include "../includes/conndb.php";
include "../includes/config.inc.php";
//ดึงชื่อสถานบริการ
$sql = "SELECT\n concat('สถานบริการ(สถานีอนามัย/PCU): ',chospital.`hosname`,' หมู่ที่:',ifnull(chospital.`mu`,'...'),' ต.',\n\tifnull(csubdistrict.`subdistname`,' ...'),' อ.',ifnull(cdistrict.`distname`,' ...'),' จ.',\n\tifnull(cprovince.`provname`,'...')) AS chospital_hosname\nFROM\n `chospital` chospital \n INNER JOIN `office` office ON chospital.`hoscode` = office.`offid`\n left outer join `csubdistrict` csubdistrict ON chospital.`provcode` = csubdistrict.`provcode`\n AND chospital.`distcode` = csubdistrict.`distcode`\n AND chospital.`subdistcode` = csubdistrict.`subdistcode`\n left outer JOIN `cdistrict` cdistrict ON chospital.`provcode` = cdistrict.`provcode`\n AND chospital.`distcode` = cdistrict.`distcode`\n INNER JOIN `cprovince` cprovince ON chospital.`provcode` = cprovince.`provcode`";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$hosp = $row[chospital_hosname];
$datemask = $_GET[datemask];
$strx = retDatets($datemask);
$age = $_GET[age];
$ect0 = "'10000'";
if (strpos($age, ",", 0) > 0) {
$listage = explode(',', $age);
foreach ($listage as $a) {
if (strpos($a, "-", 0) > 0) {
list($str, $end) = split("-", $a, 2);
for ($i = $str; $i <= $end; $i++) {
$ect0 .= ",'" . $i . "'";
}
} else {
$ect0 .= ",'" . $a . "'";
}
}
} else {
if (strpos($age, "-", 0) > 0) {
list($str, $end) = split("-", $age, 2);
for ($i = $str; $i <= $end; $i++) {
$ect0 .= ",'" . $i . "'";
}
$str = retdaterangstr($_GET[str]);
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
$ovyear = substr($sto, 0, 4);
$sql = "SELECT\nperson.pcucodeperson,\nperson.pid,\nCONVERT(concat(ifnull(ctitle.titlename,ifnull(person.prename,'ไม่ระบุ') ),person.fname,' ' ,person.lname) using utf8) as pname,\nctitle.titlename,\nperson.fname,\nperson.lname,\nperson.birth,\ngetageyearnum(person.birth,'{$str}') AS age,\nhouse.hno,\nhouse.villcode,\nhouse.xgis,\nhouse.ygis,\nvisitepi.vaccinecode,\nvisitepi.dateepi\nFROM\nvillage\nINNER JOIN house ON village.pcucode = house.pcucode AND village.villcode = house.villcode\nINNER JOIN person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nINNER JOIN visitepi ON person.pcucodeperson = visitepi.pcucodeperson AND person.pid = visitepi.pid\nINNER JOIN ctitle ON person.prename = ctitle.titlecode\nWHERE visitepi.vaccinecode in ('dT1','dTs1') and visitepi.dateepi between '{$str}' and '{$sto}' {$wvill}\nORDER BY house.villcode asc ,house.hno*1 asc,getageyearnum(person.birth,'{$str}') desc\n";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
if ($row[dateepi] == "") {
$sick = "ยังไม่ได้รับการตรวจ";
} else {
$sick = retDatets($row[dateepi]);
}
$xml .= '<marker ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'age="' . $row[age] . '" ';
$xml .= 'vaccinecode="' . $row[vaccinecode] . '" ';
$xml .= 'sick="' . $sick . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
$xml .= '/>';
}
$xml .= '</markers>';
echo $xml;
}
$str = retdaterangstr($_GET[str]);
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
$ovyear = substr($sto, 0, 4);
$sql = "SELECT\nperson.pcucodeperson,\nperson.pid,\nCONVERT(concat(ifnull(ctitle.titlename,ifnull(person.prename,'ไม่ระบุ') ),person.fname,' ' ,person.lname) using utf8) as pname,\nctitle.titlename,\nperson.fname,\nperson.lname,\nperson.birth,\ngetageyearnum(person.birth,visit.visitdate) AS age,\nhouse.hno,\nhouse.villcode,\nhouse.xgis,\nhouse.ygis,\nvisit.pcucode,\nvisit.visitno,\nvisit.visitdate,\nvisit.symptoms,\nvisit.vitalcheck,\ngroup_concat(visitdiag.diagcode) as xx,\ntmp.visitno as ultravisitno,\ntmp.visitdate as ultravisitdate,\ntmp.symptoms as ultrasys,\ntmp.vitalcheck as ultravital,\ntmp.diagcode as ultradiag\nFROM\nvillage\nINNER JOIN house ON village.pcucode = house.pcucode AND village.villcode = house.villcode\nINNER JOIN person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nINNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid\nINNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno\nINNER JOIN ctitle ON person.prename = ctitle.titlecode\nleft JOIN\n(SELECT\nv.pcucode,\nv.visitno,\nv.visitdate,\nv.pcucodeperson,\nv.pid,\nv.symptoms,\nv.vitalcheck,\nvd.diagcode\nFROM\nvisit v\nINNER JOIN visitdiag vd ON v.pcucode = vd.pcucode AND v.visitno = vd.visitno\nwhere v.visitdate between '{$str}' and '{$sto}' and v.symptoms like '%ตรวจอัลตร้าซาว%' and vd.diagcode like 'Z12.8') as tmp\non tmp.pcucodeperson = visit.pcucodeperson and tmp.pid = visit.pid\nwhere right(house.villcode,2) <> '00' and getageyearnum(person.birth,visit.visitdate) > 39 and visit.visitdate between '2010-10-01' and '{$sto}' {$wvill} {$chksto}\ngroup by visit.pcucode, visit.visitno\nhaving xx like '%Z11.6%' and xx like '%B66%'\n";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
if ($row[ultravisitdate] == "") {
$sick = "ยังไม่ได้รับการตรวจ";
} else {
$sick = retDatets($row[ultravisitdate]);
}
$xml .= '<marker ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'age="' . $row[age] . '" ';
$xml .= 'ultrasys="' . $row[ultrasys] . '" ';
$xml .= 'ultravital="' . $row[ultravital] . '" ';
$xml .= 'sick="' . $sick . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
$xml .= '/>';
}
$xml .= '</markers>';
$live_type2 = "typelive in ('0','1','2','3')";
}
if ($getage == '15') {
$gage = "AND age > 14";
} elseif ($getage == '35') {
$gage = "AND age > 34";
} else {
$gage = "AND age between '15 and '34'";
}
$sql = "select\n*,\ncase when resultht = '' or resultht is null then '0'\n\twhen resultht in ('เสี่ยง','สูง') or resultdm in ('เสี่ยง','สูง') then '2' else '1' end as chk\nfrom\n(SELECT\nperson.pcucodeperson,\nperson.pid,\nperson.fname,\nCONCAT(ctitle.titlename,person.fname,' ',person.lname) AS pname,\nhouse.hno,\nhouse.villcode,\nvillage.villname,\nhouse.xgis,\nhouse.ygis,\nperson.birth,\nperson.typelive,\nFLOOR((TO_DAYS(NOW())-TO_DAYS(person.birth))/365.25) AS age\nFROM\nvillage\nINNER JOIN house ON village.pcucode = house.pcucode AND village.villcode = house.villcode\nInner Join person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nInner Join cstatus ON person.marystatus = cstatus.statuscode\nInner Join ctitle ON person.prename = ctitle.titlecode\nWHERE {$ect2} ((person.dischargetype is null) or (person.dischargetype = '9')) AND\n\t\t\t\tSUBSTRING(house.villcode,7,2) <> '00' ORDER BY house.villcode,house.hno*1\n) as per\nleft join \n(SELECT \nperson.pcucodeperson as pcucodeperson1,\nperson.pid as pid1,\nncd_person_ncd_screen.screen_date, ncd_person_ncd_screen.bmi, ncd_person_ncd_screen.weight, ncd_person_ncd_screen.height, ncd_person_ncd_screen.waist, ncd_person_ncd_screen.hbp_s1,\nncd_person_ncd_screen.hbp_d1, ncd_person_ncd_screen.result_new_dm, ncd_person_ncd_screen.result_new_hbp, ncd_person_ncd_screen.result_new_waist, ncd_person_ncd_screen.result_new_obesity, if(ncd_person_ncd_screen.hbp_s2 is null ,if(ncd_person_ncd_screen.hbp_s1 between 120 and 139 or ncd_person_ncd_screen.hbp_d1 between 80 and 89, 'เสี่ยง',if(ncd_person_ncd_screen.hbp_s1 > 139 or ncd_person_ncd_screen.hbp_d1 > 89,'สูง','ปกติ')),if(ncd_person_ncd_screen.hbp_s2 between 120 and 139 or ncd_person_ncd_screen.hbp_d2 between 80 and 89, 'เสี่ยง',if(ncd_person_ncd_screen.hbp_s2 > 139 or ncd_person_ncd_screen.hbp_d2 > 89,'สูง','ปกติ'))) as resultht,\nif(ncd_person_ncd_screen.bstest = '3' or ncd_person_ncd_screen.bstest = '1',if(ncd_person_ncd_screen.bsl between 100 and 125,'เสี่ยง',if(ncd_person_ncd_screen.bsl > 125,'สูง','ปกติ')),if(ncd_person_ncd_screen.bsl between 140 and 199,'เสี่ยง',if(ncd_person_ncd_screen.bsl > 199,'สูง','ปกติ'))) as resultdm,\nncd_person_ncd_screen.bsl,\nncd_person_ncd_screen.hbp_s2, ncd_person_ncd_screen.hbp_d2, ncd_person_ncd_screen.bstest,\nif(ncd_person_ncd_screen.waist is null,null,if( (person.sex='1' and ncd_person_ncd_screen.waist >89 ) or (person.sex='2' and ncd_person_ncd_screen.waist >79),'รอบเอวเกิน','รอบเอวปกติ')) as resultwaist\nfrom\nhouse\nInner Join person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nInner Join village ON village.pcucode = house.pcucode AND village.villcode = house.villcode\ninner join ncd_person_ncd_screen on person.pid = ncd_person_ncd_screen.pid AND person.pcucodeperson = ncd_person_ncd_screen.pcucode\nInner Join ctitle ON person.prename = ctitle.titlecode\nwhere {$ect2} ((person.dischargetype is null) or (person.dischargetype = '9')) and SUBSTRING(house.villcode,7,2) <> '00' and ncd_person_ncd_screen.screen_date BETWEEN '{$str}' AND '{$sto}' \n\t\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\thouse.villcode,house.hno*1\n) as fp\non per.pcucodeperson = fp.pcucodeperson1 and per.pid = fp.pid1\nwhere {$live_type2} {$gage} {$risk2} {$gchronic}\nORDER BY villcode, fname";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$sick = retDatets($row[screen_date]);
$bmi = number_format($row[bmi], 2, '.', '');
$xml .= '<marker ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'dm="' . $row[resultdm] . '" ';
$xml .= 'ht="' . $row[resultht] . '" ';
$xml .= 'waist="' . $row[resultwaist] . '" ';
$xml .= 'sick="' . $sick . '" ';
$xml .= 'chk="' . $row[chk] . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
$xml .= '/>';
}
mysql_query($sql);
$sql = "UPDATE tmp_ncd_risk set ball=2\nwhere\nball = 0 AND\n((sys BETWEEN 120 and 139) OR\n(dias BETWEEN 80 and 89)) AND\nfbs < 100";
mysql_query($sql);
$sql = "UPDATE tmp_ncd_risk set ball=1\nwhere\nball = 0 AND\nsys < 120 AND\ndias <80 AND\nfbs < 100";
mysql_query($sql);
$sql = "UPDATE tmp_ncd_risk set ball=0\nwhere\nsys = 0 AND\nfbs = 0";
mysql_query($sql);
$sql = "select * from tmp_ncd_risk";
$result = mysql_query($sql);
$num = mysql_num_rows($result);
//echo "row". $num;
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$bod = retDatets($row[birth]);
$age = $row[age];
$xml .= '<marker ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'bod="' . $bod . '" ';
$xml .= 'age="' . $age . '" ';
$xml .= 'fbs="' . $row[fbs] . '" ';
$xml .= 'sys="' . $row[sys] . '" ';
$xml .= 'dias="' . $row[dias] . '" ';
$xml .= 'a1c="' . $row[a1c] . '" ';
$xml .= 'type="' . $row[ball] . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
function pregtest()
{
//function นัด pregtest
$sql = "SELECT\n concat('สถานบริการ(สถานีอนามัย/PCU): ',chospital.`hosname`,' หมู่ที่:',ifnull(chospital.`mu`,'...'),' ต.',\n\tifnull(csubdistrict.`subdistname`,' ...'),' อ.',ifnull(cdistrict.`distname`,' ...'),' จ.',\n\tifnull(cprovince.`provname`,'...')) AS chospital_hosname\nFROM\n `chospital` chospital \n INNER JOIN `office` office ON chospital.`hoscode` = office.`offid`\n left outer join `csubdistrict` csubdistrict ON chospital.`provcode` = csubdistrict.`provcode`\n AND chospital.`distcode` = csubdistrict.`distcode`\n AND chospital.`subdistcode` = csubdistrict.`subdistcode`\n left outer JOIN `cdistrict` cdistrict ON chospital.`provcode` = cdistrict.`provcode`\n AND chospital.`distcode` = cdistrict.`distcode`\n INNER JOIN `cprovince` cprovince ON chospital.`provcode` = cprovince.`provcode`";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$hosp = $row[chospital_hosname];
$village = $_GET[village];
if ($village == "00000000") {
$wvill = "";
} else {
$wvill = " AND house.villcode='{$village}' ";
}
if ($village == "00000000") {
$mu = "ทุกหมู่บ้าน";
} else {
$mu = getvillagename($village);
}
$str = retdaterangstr($_GET[str]);
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
$sql = "SELECT\nperson.pcucodeperson,\nperson.pid,\nperson.idcard,\nperson.fname,\nconcat(ctitle.titlename, person.fname , ' ' , person.lname) AS pname,\nperson.birth,\nROUND(DATEDIFF(now(),person.birth)/365.25) AS age,\nhouse.villcode,\nhouse.hno,\nhouse.hcode,\nhouse.xgis,\nhouse.ygis,\nvisit.visitdate,\nvisitfp.pregtest,\nvisitfp.pregtestresult,\nvisit.username\nFROM\nhouse\nINNER JOIN person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nLEFT JOIN ctitle on ctitle.titlecode = person.prename\nINNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid\nINNER JOIN visitfp ON visit.pcucodeperson = visitfp.pcucodeperson AND visit.pid = visitfp.pid AND visit.visitdate = visitfp.datefp\nwhere visitfp.pregtest = '17' and visit.visitdate between '{$str}' and '{$sto}' {$wvill}\norder by visit.visitdate,person.fname";
$result = mysql_query($sql);
$txt = '<p align=\'center\'><b>รายงานการตรวจทดสอบการตั้งครรภ์<br>';
$txt .= "ข้อมูลวันที่ {$strx} ถึง {$stox} {$mu}</b></p><b>{$hosp}</b><table width='99%' border='0' cellspacing='1' cellpadding='1' class='table table-striped table-hover table-bordered'>\n <tr>\n <th width='4%' scope='col'><div align='center'>ลำดับ</th>\n\t<th width='5%' scope='col'><div align='center'>HN</th>\n <th width='10%' scope='col'><div align='center'>ชื่อ - สกุล</th>\n\t<th width='5%' scope='col'><div align='center'>อายุ</th>\n <th width='6%' scope='col'><div align='center'>บ้านเลขที่</th>\n\t<th width='4%' scope='col'><div align='center'>หมู่ที่</th>\n <th width='4%' scope='col'><div align='center'>วันที่ตรวจ</th>\n\t<th width='9%' scope='col'><div align='center'>ผลการตรวจ</th>\n\t<th width='9%' scope='col'><div align='center'>ผู้ตรวจ</th>\n </tr>";
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$userv = getusername($row[username]);
if ($row[pregtestresult] == "0") {
$pregtestname = "ไม่ตั้งครรภ์";
} elseif ($row[pregtestresult] == "1") {
$pregtestname = "ตั้งครรภ์";
} elseif ($row[pregtestresult] == "3") {
$pregtestname = "แปลผลไม่ได้";
} else {
$pregtestname = "";
}
if ($row[visitdate] == "") {
$appsick = "";
} else {
$appsick = retDatets($row[visitdate]);
}
++$i;
if ($i % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$i}</div></td>\n\t<td><div align='center'>{$row['pid']}</div></td>\n <td>{$row['pname']}</td>\n\t<td><div align='center'>{$row['age']}</div></td>\n <td><div align='center'>{$row['hno']}</div></td>\n <td><div align='center'>{$moo}</div></td>\n\t<td><div align='center'>{$appsick}</div></td>\n\t<td><div align='center'>{$pregtestname}</div></td>\n\t<td><div align='left'>{$userv}</div></td>\n </tr>";
}
$txt .= "</table><br>";
echo $txt;
}
<![endif]-->
<script src="../js/jquery.1.11.0.min.js"></script>
<script src="../js/bootstrap.min.js"></script>
</head>
<body">
<?php
$txt = "<b>การตรวจพยาธิระหว่างวันที่ {$str} - {$sto}</b>";
$txt .= "<table width='100%' border='0' cellspacing='0' cellpadding='0' class='table table-striped table-hover table-bordered'>\n <tr>\n <th width='5%' scope='col'><div align='center'>ลำดับ</div></th>\n <th width='25%' scope='col'><div align='center'>ชื่อ - สกุล</div></th>\n <th width='6%' scope='col'><div align='center'>อายุ</div></th>\n <th width='48%' scope='col'><div align='center'>ผลการตรวจ</div></th>\n <th width='16%' scope='col'><div align='center'>วันที่ตรวจ</div></th>\n </tr>";
$sql = "SELECT CONCAT(ctitle.titlename,p.fname,' ',p.lname) AS pname,YEAR(NOW())-YEAR(p.birth) AS age,p.pid\n\t\t\tFROM house AS h\n\t\t\tInner Join person AS p ON h.pcucode = p.pcucodeperson AND h.hcode = p.hcode\n\t\t\tInner Join ctitle ON p.prename = ctitle.titlecode\n\t\t\tWHERE ((p.dischargetype is null) or (p.dischargetype = '9')) AND FLOOR((TO_DAYS(NOW())-TO_DAYS(p.birth))/365.25) > 29 and h.hcode={$hcode}\n\t\t\tORDER BY p.birth";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
$i++;
$sqlv = "SELECT\n\t\t\tvisit.vitalcheck,\n\t\t\tvisit.visitdate \n\t\t\tFROM\nvillage\nINNER JOIN house ON village.pcucode = house.pcucode AND village.villcode = house.villcode\nINNER JOIN person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nINNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid\nINNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno\nwhere visit.visitdate between '{$nstr}' and '{$nsto}' and ((person.dischargetype is null) or (person.dischargetype = '9')) AND\n\t\t\t\tSUBSTRING(house.villcode,7,2) <> '00' AND \n\t\t\t\tFLOOR((TO_DAYS(NOW())-TO_DAYS(person.birth))/365.25) > 29 and visit.symptoms like '%พยาธิ%' and visit.vitalcheck like '%พบ%' and visitdiag.diagcode = 'Z11.6' AND visit.pid = {$row['pid']} \n\t\t\tORDER BY visit.visitdate DESC";
$resultv = mysql_query($sqlv);
$rowv = mysql_fetch_array($resultv);
if ($rowv[1]) {
$ndate = retDatets($rowv[1]);
} else {
$ndate = "";
}
$txt .= "<tr>\n <td><div align='center'>{$i}</div></td>\n <td>{$row['pname']}</td>\n <td><div align='center'>{$row['age']}</div></td>\n <td>{$rowv['0']}</td>\n <td><div align='center'>{$ndate}</div></td>\n </tr>";
}
$txt .= "</table>";
echo $txt;
} else {
header("Location: login.php");
}
?>
</body>
</html>
$result = mysql_query($sql);
$txt = '<p align=\'center\'><b>รายงานการประเมินภาวะโภชนาการผู้สูงอายุ </b><br>';
$txt .= "<b>ข้อมูลระหว่างวันที่ {$strx} ถึง {$stox} {$mu} </b></p><br><b>{$hosp}</b><table width='98%' border='0' cellspacing='1' cellpadding='1' class='table table-striped table-hover table-bordered'>\n <tr>\n <th width='4%' scope='col'><div align='center'>ลำดับ</div></th>\n <th width='13%' scope='col'><div align='center'>ชื่อ - สกุล</div></th>\n\t<th width='8%' scope='col'><div align='center'>ว/ด/ป เกิด</div></th>\n\t<th width='5%' scope='col'><div align='center'>อายุ</div></th>\n <th width='7%' scope='col'><div align='center'>บ้านเลขที่</div></th>\n <th width='4%' scope='col'><div align='center'>หมู่ที่</div></th>\n\t<th width='4%' scope='col'><div align='center'>วันที่สำรวจ</div></th>\n\t<th width='15%' scope='col'><div align='center'>น้ำหนัก</div></th>\n\t<th width='15%' scope='col'><div align='center'>ส่วนสูง</div></th>\n\t<th width='15%' scope='col'><div align='center'>BMI</div></th>\n\t<th width='15%' scope='col'><div align='center'>สรุปผล</div></th>\n </tr>";
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
if ($row[chk] == "") {
$old_chk = 'ไม่ได้ชัั่งน้ำหนัก';
} else {
$old_chk = $row[chk];
}
$birth = retDatets($row[birth]);
if ($row[m_visit] == "") {
$visitdate = '--/--/----';
} else {
$visitdate = retDatets($row[m_visit]);
}
$bmi = number_format($row[bmi], 2, '.', '');
++$i;
if ($i % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$i}</div></td>\n <td>{$row['pname']}</td>\n\t<td><div align='center'> {$birth}</div></td>\n\t<td><div align='center'> {$row['age']}</div></td>\n <td><div align='center'> {$row['hno']}</div></td>\n <td><div align='center'> {$moo}</div></td>\n <td><div align='center'> {$visitdate}</div></td>\n\t<td><div align='center'> {$row['weight']}</div></td>\n\t<td><div align='center'> {$row['height']}</div></td>\n\t<td><div align='center'> {$bmi}</div></td>\n\t<td><div align='center'> {$old_chk}</div></td>\n </tr>";
}
$txt .= "</table><br>";
echo $txt;
} else {
header("Location: ../main/login.php");
}
$txt = '<p align=\'center\'><b>รายชื่อผู้ป่วยโรคเบาหวานความดันโลหิตสูง ';
$txt .= " {$mu} </b></p><br><b>{$hosp}</b><div class='table-responsive'><table width='98%' border='0' cellspacing='1' cellpadding='1' class='table table-striped table-hover table-bordered'>\n <tr>\n <th width='5%' scope='col'><div align='center'>ลำดับ</div></th>\n <th width='14%' scope='col'><div align='center'>ชื่อ - สกุล</div></th>\n\t<th width='4%' scope='col'><div align='center'>อายุ</div></th>\n <th width='5%' scope='col'><div align='center'>บ้านเลขที่</div></th>\n <th width='4%' scope='col'><div align='center'>หมู่ที่</div></th>\n <th width='13%' scope='col'><div align='center'>โรคเรื้อรัง</div></th>\n\t<th width='9%' scope='col'><div align='center'>วันที่วินิจฉัยครั้งแรก</div></th>\n\t<th width='7%' scope='col'><div align='center'>สถานะปัจจุบัน</div></th>\n\t<th width='17%' scope='col'><div align='center'>รพ.ประจำ</div></th>\n </tr>";
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$title = getTitle($row[prename]);
$pcuname = gethospname($row[cup]);
if ($row[datefirstdiag] == "") {
$sick = "...";
} else {
$sick = retDatets($row[datefirstdiag]);
}
if ($row[datedxfirst] == "") {
$last = "...";
} else {
$last = retDatets($row[datedxfirst]);
}
++$i;
if ($i % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$i}</div></td>\n <td>{$row['pname']}</td>\n\t<td><div align='center'>{$row['age']}</div></td>\n <td><div align='center'>{$row['hno']}</div></td>\n <td><div align='center'>{$moo}</div></td>\n <td> {$row['chronicx']}</td>\n\t<td> {$sick}</td>\n\t<td><div align='center'>{$row['typedischart']}</div></td>\n\t<td> {$pcuname}</td>\n </tr>";
}
$txt .= "</table><br>";
echo $txt;
} else {
header("Location: login.php");
}
?>
if ($row[vac2] == "") {
$vac2 = '---';
} else {
$vac2 = retDatets($row[vac2]);
}
if ($row[vac3] == "") {
$vac3 = '---';
} else {
$vac3 = retDatets($row[vac3]);
}
if ($row[chk] == "1") {
$anc_chk = 'ฝากครรภ์ครบ4ครั้งคุณภาพ';
} else {
$anc_chk = 'ฝากครรภ์ยังไม่ครบ4ครั้งคุณภาพ';
}
$birth = retDatets($row[birth]);
++$i;
if ($i % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$i}</div></td>\n <td>{$row['pname']}</td>\n\t<td><div align='center'> {$birth}</div></td>\n\t<td><div align='center'> {$row['age']}</div></td>\n <td><div align='center'> {$row['hno']}</div></td>\n <td><div align='center'> {$moo}</div></td>\n\t<td><div align='center'> {$row['lmp']}</div></td>\n <td><div align='center'> {$row['pregno']}</div></td>\n <td><div align='center'> {$anc1}</div></td>\n\t<td><div align='center'> {$anc2}</div></td>\n\t<td><div align='center'> {$anc3}</div></td>\n\t<td><div align='center'> {$anc4}</div></td>\n\t<td><div align='center'> {$anc5}</div></td>\n </tr>";
}
$txt .= "</table><br>";
echo $txt;
} else {
header("Location: ../main/login.php");
}
?>
$rt = $y . "/" . $m . "/" . $dn;
return $rt;
}
$str = retdaterangstr($_GET[str]);
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
$sql = "SELECT person.idcard\n,CONVERT(concat(ifnull(c.titlename,ifnull(person.prename,'ไม่ระบุ') ),person.fname,' ' ,person.lname) using utf8) as pname \n,DATE_FORMAT(visitdate,'%Y-%m-%d') as visitdate\n,office.offid\n,v.pcucode\n,if(v.pcucode = office.offid,'หน่วยบริการ' ,'ที่อื่น ' ) as pcu\n,drugname\n,CONVERT(concat(c.titlename,u.fname,' ' ,u.lname) using utf8) as uname\n,count(visitdrug.drugcode) as ctime\n,DATE_FORMAT(visitdrug.dateupdate,'%Y-%m-%d') as dateupdate\n,curdate() as cdate,\nhouse.hno,\nhouse.villcode,\nhouse.xgis,\nhouse.ygis,\nvisitdrug.unit,\ncdrugunitsell.unitsellname\nFROM person left join ctitle on person.prename = ctitle.titlecode\n\tleft join visit v on person.pid = v.pid and person.pcucodeperson = v.pcucodeperson\n\tleft join visitdrug on v.visitno = visitdrug.visitno and v.pcucode = visitdrug.pcucode\n\tleft join cdrug on visitdrug.drugcode = cdrug.drugcode\n\tleft join cdrugunitsell on cdrug.unitsell = cdrugunitsell.unitsellcode\n\tleft join user u on v.username = u.username\n\tleft join ctitle c on c.titlecode = u.prename\n\tleft join office on v.pcucode = office.offid\n\tInner Join house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nWHERE cdrug.drugcode like'CONDOM%' \n \tand visitdate between '{$str}' and '{$sto}' and (v.flagservice <'04' OR v.flagservice is null OR length(trim(v.flagservice))=0 )\nGROUP BY v.visitno,v.pcucodeperson\nORDER BY v.visitdate";
$result = mysql_query($sql);
$txt = '<p align=\'center\'><b>รายชื่อผู้ได้รับถุงยางอนามัยในงานป้องกันเอดส์';
$txt .= "<p div align='center' class='text-danger'>ข้อมูลระหว่างวันที่ {$strx} ถึง {$stox} </p></div></p>{$hosp}</b><br><table width='99%' border='0' cellspacing='1' cellpadding='1' class='table table-striped table-hover table-bordered'>\n <tr>\n <th width='5%' scope='col'>ลำดับ</th>\n\t<th width='13%' scope='col'>เลขบัตรประชาชน</th>\n <th width='16%' scope='col'>ชื่อ - สกุล</th>\n\t<th width='13%' scope='col'>ที่อยู่</th>\n\t<th width='10%' scope='col'>วันที่ใช้บริการ</th>\n\t<th width='16%' scope='col'>ยาสมุนไพร</th>\n <th width='16%' scope='col'>ผู้ให้บริการ</th>\n\t<th width='12%' scope='col'>จำนวน</th>\n <th width='10%' scope='col'>วันที่บันทึกข้อมูล</th>\n </tr>";
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$sick = retDatets($row[visitdate]);
$dupdate = retDatets($row[dateupdate]);
++$x;
if ($x % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$x}</div></td>\n\t<td>{$row['idcard']}</td>\n <td>{$row['pname']}</td>\n\t<td>{$row['hno']} หมู่ที่ {$moo}</td>\n <td>{$sick}</td>\n <td>{$row['drugname']}</td>\n\t<td>{$row['uname']}</td>\n\t<td><div align='center'>{$row['unit']} {$row['unitsellname']}</td>\n <td>{$dupdate}</td>\n </tr>";
}
$txt .= "</table><br>";
echo $txt;
} else {
header("Location: login.php");
}
?>
$gage = "";
}
$str = retdaterangstr($_GET[str]);
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
$sql = "select *,\ncase when fp.visitdate is not null and para.para is not null then concat('ผลการตรวจ พบ',para.para,' ',para.diseasenamethai)\nwhen fp.visitdate is not null and para.para is null then 'ผลการตรวจ ไม่พบ'\nwhen fp.visitdate is null then 'ยังไม่ได้ตรวจ' ELSE '' end as chk\nfrom\n(SELECT\np.pcucodeperson,\np.pid,\np.fname,\nCONCAT(ctitle.titlename,p.fname,' ',p.lname) AS pname,\nh.hno,\nh.villcode,\nh.xgis,\nh.ygis,\np.birth,\np.typelive,\nFLOOR((TO_DAYS('{$daymidyear}')-TO_DAYS(p.birth))/365.25) AS age\nFROM\nhouse AS h\nInner Join person AS p ON h.pcucode = p.pcucodeperson AND h.hcode = p.hcode\nInner Join cstatus ON p.marystatus = cstatus.statuscode\nInner Join ctitle ON p.prename = ctitle.titlecode\nWHERE {$live_type2} ((p.dischargetype is null) or (p.dischargetype = '9')) AND\n\t\t\t\tSUBSTRING(h.villcode,7,2) <> '00' {$gage} {$wvill} ORDER BY h.villcode,h.hno*1\n) as per\nleft join \n(SELECT\nvisit.pcucodeperson as pcucodeperson1,\nvisit.pid as pid1,\nvisit.visitno as visitno1,\nvisitdiag.diagcode,\nvisit.visitdate\nFROM\nvisit\nINNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno\nwhere visit.visitdate between '{$str}' and '{$sto}' and visitdiag.diagcode = 'Z11.6' and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 )) as fp\non per.pcucodeperson = fp.pcucodeperson1 and per.pid = fp.pid1\nleft join\n(SELECT\nvisit.pcucodeperson as pcucodeperson2,\nvisit.pid as pid2,\nvisit.visitno as visitno2,\nGROUP_CONCAT(visitdiag.diagcode) as para,\nGROUP_CONCAT(cdisease.diseasenamethai) as diseasenamethai\nFROM\nvisit\nINNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno\ninner join cdisease on visitdiag.diagcode = cdisease.diseasecode\nwhere visit.visitdate between '{$str}' and '{$sto}' and visitdiag.diagcode like 'B%' and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 ) \ngroup by visit.pcucode,visit.visitno) as para\non para.pcucodeperson2 = fp.pcucodeperson1 and para.pid2 = fp.pid1 and para.visitno2 = fp.visitno1\n{$chksto}\norder by villcode, fname";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
if ($row[visitdate] == "") {
$sick = "";
} else {
$sick = "วันที่ตรวจ " . retDatets($row[visitdate]);
}
$xml .= '<marker ';
$xml .= 'hcode="' . $row[hcode] . '" ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'age="' . $row[age] . '" ';
$xml .= 'chk="' . $row[chk] . '" ';
$xml .= 'para="' . $row[para] . '" ';
$xml .= 'diseasenamethai="' . $row[diseasenamethai] . '" ';
$xml .= 'sick="' . $sick . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
$xml .= '/>';
if ($row[chk] == "") {
$old_chk = 'ยังไม่ได้ประเมิน';
} elseif ($row[chk] == 3) {
$old_chk = 'ติดเตียง';
} elseif ($row[chk] == 2) {
$old_chk = 'ติดบ้าน';
} elseif ($row[chk] == 1) {
$old_chk = 'ติดสังคม';
} else {
$old_chk = 'ไม่ทราบ';
}
$birth = retDatets($row[birth]);
if ($row[dateupdate] == "") {
$visitdate = '';
} else {
$visitdate = retDatets($row[dateupdate]);
}
if ($row[chk] == "") {
$mark_old = 'ยังไม่ได้ประเมิน';
} else {
$mark_old = '';
}
++$i;
if ($i % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$i}</div></td>\n <td>{$row['pname']}</td>\n\t<td><div align='center'> {$birth}</div></td>\n\t<td><div align='center'> {$row['age']}</div></td>\n <td><div align='center'> {$row['hno']}</div></td>\n <td><div align='center'> {$moo}</div></td>\n <td><div align='center'> {$visitdate}</div></td>\n\t<td><div align='center'> {$old_chk}</div></td>\n\t<td><div align='center'> {$mark_old}</div></td>\n </tr>";
}
$txt .= "</table><br>";
} elseif ($live_type == '1') {
$live_type2 = "and p.typelive in ('0','1','3')";
} else {
$live_type2 = "and p.typelive in ('0','1','2','3')";
}
$sql = "SELECT \nt1.pcucode,\nt1.pcucodeperson,\nt1.pid,\nt1.pname,\nt1.birth,\nt1.agemonth,\nt1.hno, \nt1.mumoi,\nt1.villname,\nt1.villcode,\nt1.xgis,\nt1.ygis,\nt2.visitdate ,\nt2.tall,\nt2.weight,\nt2.bw_level,\nt2.bmi_level,\nt2.heigth_level,\nCASE when t2.heigth_level in ('3','4','5') or t2.bmi_level=3 THEN 3 when t2.bmi_level is null then 0 else 1 end as bwok\nFROM\n(SELECT \nh.pcucode,\nh.xgis,\nh.ygis,\np.pcucodeperson,\np.pid,\nconcat(ctitle.titlename,p.fname,' ',p.lname) as pname,\np.birth,\nFLOOR((TO_DAYS('{$sto}')-TO_DAYS(p.birth))/30.44) as agemonth,\nh.hno, \nRIGHT(h.villcode,2) AS mumoi,\nvillname,h.villcode\nFROM person p\nLEFT JOIN ctitle on ctitle.titlecode = p.prename\nLEFT JOIN house h ON h.hcode=p.hcode and h.pcucodeperson=p.pcucodeperson\nLEFT JOIN village v ON v.villcode=h.villcode and v.pcucode=h.pcucode\nWHERE {$wvill} p.BIRTH<'{$sto}' AND RIGHT(h.villcode,2)<>'00' {$live_type2} AND CONCAT(p.pid,p.pcucodeperson) NOT IN (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson) FROM persondeath)\nGROUP BY p.pcucodeperson,p.pid\nHAVING agemonth <72\nORDER BY p.mumoi,hnomoi) t1\n\nLEFT JOIN\n\n(SELECT nu.pcucode,nu.pid,nu.sex,nu.visitdate ,nu.agemonth,nu.tall,nu.weight\n,max(CASE when nu.tall BETWEEN hc.hmi and hc.hmx THEN hc.nul else null end) as 'heigth_level'\n,MAX(case when nu.weight BETWEEN bmi_c.bwmi and bmi_c.bwmx THEN bmi_c.bwnul else null end) as 'bmi_level'\n,MAX(case when nu.weight BETWEEN bc.bmin and bc.bmax THEN bc.bnul else null end) as 'bw_level'\nfrom\n(SELECT n.pcucode,v.pid,p.sex,p.birth,FLOOR((TO_DAYS(v.visitdate)-TO_DAYS(p.birth))/30.44) as agemonth,n.tall,n.weight,v.visitdate,CONCAT(getAgeMonth(p.birth,v.visitdate)\n,case when p.sex=1 then 'm' Else 'f'end)as 'ms'\n,CONCAT(CEILING(n.tall)\n,case when p.sex=1 then 'm' Else 'f'end)as 'ts'\n\nFROM visitnutrition as n\nINNER JOIN visit as v on n.visitno=v.visitno\nINNER JOIN person as p on v.pcucodeperson=p.pcucodeperson and v.pid=p.pid\nWHERE v.visitdate BETWEEN '{$str}' and '{$sto}')as nu\nINNER JOIN(SELECT cchart_bh.height_min as hmi ,cchart_bh.height_max as hmx,cchart_bh.nutrition_level as 'nul'\n,concat(cchart_bh.age_month,case when cchart_bh.sex=1 then 'm' Else 'f'end)as 'ms' from cchart_bh) as hc on nu.ms=hc.ms\nINNER JOIN (SELECT cchart_bmi.bw_min as bwmi ,cchart_bmi.bw_max as bwmx,cchart_bmi.nutrition_level as 'bwnul'\n,concat(cchart_bmi.height,case when cchart_bmi.sex=1 then 'm' Else 'f'end)as 'bws' from cchart_bmi) as bmi_c on nu.ts=bmi_c.bws\nINNER JOIN(SELECT cchart_bw.bw_min as bmin ,cchart_bw.bw_max as bmax,cchart_bw.nutrition_level as 'bnul'\n,concat(cchart_bw.age_month,case when cchart_bw.sex=1 then 'm' Else 'f'end)as 'bs' from cchart_bw) as bc on nu.ms=bc.bs\nwhere nu.agemonth < 72\nGROUP BY nu.pcucode,nu.pid\nORDER BY nu.pid) t2\nON t2.pid=t1.pid and t2.pcucode=t1.pcucode\n{$chksto}\norder by t1.villcode,t1.agemonth";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$birth = retDatets($row[birth]);
if ($row[visitdate] == "") {
$visitdate = "";
} else {
$visitdate = "วันที่ " . retDatets($row[visitdate]);
}
if ($row[bwok] == "3") {
$bwokm = "สูงระดับดี/สมส่วน";
} elseif ($row[bwok] == "1") {
$bwokm = "ต้องเฝ้าระวัง";
} else {
$bwokm = "ยังไม่ได้ชั่งน้ำหนัก";
}
$xml .= '<marker ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'birth="' . $birth . '" ';
$xml .= 'age="' . $row[agemonth] . '" ';
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
if ($row[birth] != "") {
$birth = retDatets($row[birth]);
} else {
$birth = "";
}
if ($row[lmp] != "") {
$lmp = retDatets($row[lmp]);
} else {
$lmp = "";
}
if ($row[edc] != "") {
$edc = retDatets($row[edc]);
} else {
$edc = "";
}
$title = getTitle($row[prename]);
$xml .= '<marker ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $title . $row[pname] . '" ';
$xml .= 'pregno="' . $row[pregno] . '" ';
$xml .= 'birth="' . $birth . '" ';
$xml .= 'lmp="' . $lmp . '" ';
$xml .= 'edc="' . $edc . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
if ($row[chk] == "0") {
$waist_chk = 'รอบเอวปกติ';
} elseif ($row[chk] == "1") {
$waist_chk = 'รอบเอวเกิน';
} else {
$waist_chk = 'ยังไม่ได้วัดรอบเอว';
}
$birth = retDatets($row[birth]);
if ($row[screen_date] == "") {
$screen_date = '--/--/----';
} else {
$screen_date = retDatets($row[screen_date]);
}
if ($row[waist] == "") {
$waist = '--';
} else {
$waist = $row[waist];
}
$xml .= '<marker ';
$xml .= 'hcode="' . $row[hcode] . '" ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'birth="' . $birth . '" ';
$xml .= 'age="' . $row[age] . '" ';
$xml .= 'waist_chk="' . $waist_chk . '" ';
$result = mysql_query($sql);
$txt = '<p align=\'center\'><b>รายงานการคัดกรองภาวะซึมเศร้า ';
$txt .= "อายุ {$age} ปี<br></p><br>{$live_type_name}<br>{$hosp}</b><div class='table-responsive'><table width='98%' border='0' cellspacing='1' cellpadding='1' class='table table-striped table-hover table-bordered'>\n <tr>\n <th width='4%' scope='col'><div align='center'>ลำดับ</div></th>\n <th width='13%' scope='col'><div align='center'>ชื่อ - สกุล</div></th>\n\t<th width='8%' scope='col'><div align='center'>ว/ด/ป เกิด</div></th>\n\t<th width='5%' scope='col'><div align='center'>อายุ</div></th>\n <th width='7%' scope='col'><div align='center'>บ้านเลขที่</div></th>\n <th width='4%' scope='col'><div align='center'>หมู่ที่</div></th>\n\t<th width='4%' scope='col'><div align='center'>วันที่ตรวจ</div></th>\n\t<th width='7%' scope='col'><div align='center'>ผลการตรวจ</div></th>\n\t<th width='7%' scope='col'><div align='center'>2Q</div></th>\n\t<th width='7%' scope='col'><div align='center'>9Q</div></th>\n </tr>";
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
if ($row[visitdate] == "") {
$old_chk = 'ยังไม่ได้ตรวจ';
} else {
$old_chk = 'ได้รับการตรวจ';
}
$birth = retDatets($row[birth]);
if ($row[visitdate] == "") {
$visitdate = '--/--/----';
} else {
$visitdate = retDatets($row[visitdate]);
}
if ($row[coderesult] == "1" and $row[visitdate] != "") {
$chkx = 'ปกติ';
} elseif ($row[coderesult] == "2" and $row[visitdate] != "") {
$chkx = 'ผิดปกติ(2Q มากกว่า0)';
} else {
$chkx = '';
}
++$x;
if ($x % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$x}</div></td>\n <td>{$row['pname']}</td>\n\t<td><div align='center'> {$birth}</div></td>\n\t<td><div align='center'> {$row['age']}</div></td>\n <td><div align='center'> {$row['hno']}</div></td>\n <td><div align='center'> {$moo}</div></td>\n <td><div align='center'> {$visitdate}</div></td>\n\t<td><div align='center'> {$chkx}</div></td>\n\t<td><div align='center'> {$row['s2q']}</div></td>\n\t<td><div align='center'> {$row['s9q']}</div></td>\n </tr>";
if ($village == '00000000') {
$ect2 = "";
} else {
$ect2 = " h.villcode = '{$village}' AND ";
}
$sql = "SELECT p.pid,p.prename,CONCAT(p.fname,' ',p.lname) AS pname,p.birth,h.hno,h.villcode,h.xgis,h.ygis,Max(n.screen_date) AS date_exam,({$nyear} - SUBSTRING(p.birth,1,4)) AS age\nFROM\nhouse AS h\nInner Join person AS p ON h.pcucode = p.pcucodeperson AND h.hcode = p.hcode\nLeft Join ncd_person_ncd_screen AS n ON p.pcucodeperson = n.pcucode AND p.pid = n.pid\nWHERE\np.typelive IN ('0', '1', '3') AND\n((p.dischargetype is null) or (p.dischargetype = '9')) AND\n{$ect2}\n({$nyear} - SUBSTRING(p.birth,1,4)) >= {$strage}\nGROUP BY\np.pid";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$bod = retDatets($row[birth]);
$title = getTitle($row[prename]);
$age = $row[age];
if (strlen($row[date_exam]) == 10) {
$dexam = retDatets($row[date_exam]);
} else {
$dexam = '';
}
if ($row[date_exam] == '') {
$type = '0';
} else {
if ($row[date_exam] >= $str && $row[date_exam] <= $sto) {
$type = '1';
} else {
$type = '2';
}
}
$xml .= '<marker ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$hosp = $row[chospital_hosname];
$villcode = $_GET[village];
if ($villcode == "00000000") {
$wvill = "";
} else {
$wvill = " AND house.villcode='{$villcode}' ";
}
if ($villcode == "00000000") {
$mu = "ทุกหมู่บ้าน";
} else {
$mu = getvillagename($villcode);
}
$str = $_GET[str];
$strx = retDatets($str);
$live_type = $_GET[live_type];
if ($live_type == '2') {
$live_type_name = "ตามทะเบียนบ้าน(0,1,2)";
} elseif ($live_type == '1') {
$live_type_name = "ที่อาศัยอยู่จริง (0,1,3)";
} else {
$live_type_name = "ทั้งหมดในเขตรับผิดชอบ(0,1,2,3)";
}
if ($live_type == '2') {
$live_type2 = "and person.typelive in ('0','1','2')";
} elseif ($live_type == '1') {
$live_type2 = "and person.typelive in ('0','1','3')";
} else {
$live_type2 = "and person.typelive in ('0','1','2','3')";
}
$live_type2 = "and person.typelive in ('0','1','3')";
} else {
$live_type2 = "and person.typelive in ('0','1','2','3')";
}
$sql = "SELECT\npepi.*,\nvepi.dateepi,\nvepi.vaccinecode,\nvepi.hosservice\nFROM\n(SELECT\nperson.pcucodeperson,\nperson.pid,\nperson.idcard,\nconcat(ifnull(titlename,'..') ,fname,' ',lname) as pname,\nperson.birth,\ngetagemonth(person.birth,now()) as age,\nhouse.hno,\nhouse.villcode,\nhouse.xgis,\nhouse.ygis\nFROM\nhouse\nINNER JOIN person on person.pcucodeperson = house.pcucode and person.hcode = house.hcode\nLEFT JOIN ctitle on ctitle.titlecode = person.prename\nwhere person.birth BETWEEN '{$str}' and '{$sto}' and ((person.dischargetype is null) or (person.dischargetype = '9')) {$wvill} {$live_type2}) as pepi\nleft JOIN\n(SELECT\nvisitepi.pcucodeperson,\nvisitepi.pid,\nvisitepi.dateepi,\nvisitepi.vaccinecode,\nvisitepi.hosservice\nFROM\nvisitepi\nINNER JOIN cdrug on cdrug.drugcode = visitepi.vaccinecode\nwhere cdrug.files18epi = '061') as vepi\non pepi.pcucodeperson = vepi.pcucodeperson and pepi.pid = vepi.pid\n{$chksto}\norder by vepi.vaccinecode,pepi.villcode,pepi.age";
$result = mysql_query($sql);
$txt = '<p align=\'center\'><b>รายงานเด็กอายุ 1 ปี ได้รับวัคซีน MMR<br>';
$txt .= "(เด็กเกิดระหว่างวันที่ {$_GET['str']} ถึง {$_GET['sto']}) <br>{$mu}</b></p><b>{$live_type_name}</b><br><b>{$hosp}</b><table width='98%' border='0' cellspacing='1' cellpadding='1' class='tbhl'>\n <tr>\n <th width='4%' scope='col'>ลำดับ</th>\n\t<th width='4%' scope='col'>HN</th>\n <th width='13%' scope='col'>ชื่อ - สกุล</th>\n\t<th width='8%' scope='col'>ว/ด/ป เกิด</th>\n\t<th width='5%' scope='col'>อายุ (เดือน)</th>\n <th width='7%' scope='col'>บ้านเลขที่</th>\n <th width='4%' scope='col'>หมู่ที่</th>\n\t<th width='4%' scope='col'>วันที่รับวัคซีน</th>\n\t<th width='5%' scope='col'>วัคซีน</th>\n\t<th width='15%' scope='col'>สถานที่รับ</th>\n </tr>";
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$birth = retDatets($row[birth]);
if ($row[dateepi] == "") {
$visitdate = '';
} else {
$visitdate = retDatets($row[dateepi]);
}
if ($row[hosservice] == "00000") {
$place = 'ไม่ระบุ';
} else {
$place = gethospserv($row[hosservice]);
}
++$i;
if ($i % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$i}</div></td>\n\t<td><div align='center'>{$row['pid']}</div></td>\n <td>{$row['pname']}</td>\n\t<td><div align='center'> {$birth}</div></td>\n\t<td><div align='center'> {$row['age']}</div></td>\n <td><div align='center'> {$row['hno']}</div></td>\n <td><div align='center'> {$moo}</div></td>\n <td><div align='center'> {$visitdate}</div></td>\n\t<td><div align='center'> {$row['vaccinecode']}</div></td>\n\t<td><div align='center'> {$place}</div></td>\n </tr>";
}
$txt .= "</table><br>";
}
$village = $_GET[village];
if ($village == '00000000') {
$ect2 = "";
} else {
$ect2 = " villcode = '{$village}' AND ";
}
$sql = "select\npcu,\npid,\npname,\nbirth,\nage,\nhno,\nvillcode,\nvillname,\nvaccinecode,\ndrugname,\ndateepi,\nxgis,\nygis\nFROM\n(SELECT\nperson.pcucodeperson as pcu,\nperson.pid as pid,\nconcat(ctitle.titlename,person.fname,' ',person.lname) AS pname,\nperson.fname,\nperson.birth as birth,\nround(DATEDIFF(now(),person.birth) /30) AS age,\nhouse.hno as hno,\nhouse.villcode as villcode,\nCONVERT(village.villname using utf8) AS villname,\nhouse.xgis,\nhouse.ygis\nFROM\nhouse\nInner Join person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nInner Join village ON village.pcucode = house.pcucode AND village.villcode = house.villcode\nInner Join ctitle ON person.prename = ctitle.titlecode\nwhere right(house.villcode,2) <> '00' and ((person.dischargetype is null) or (person.dischargetype = '9')) and round(DATEDIFF(now(),person.birth) /30) IN({$ect0})\norder by age) as per_epi\nleft Join (SELECT\nperson.pcucodeperson as pcu1,\nperson.pid as pid1,\nvisitepi.vaccinecode,\ncdrug.drugname,\nvisitepi.dateepi\nFROM\nhouse\nInner Join person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nInner Join village ON village.pcucode = house.pcucode AND village.villcode = house.villcode\nInner Join visitepi ON person.pcucodeperson = visitepi.pcucodeperson AND person.pid = visitepi.pid\nInner Join ctitle ON person.prename = ctitle.titlecode\nInner Join cdrug ON visitepi.vaccinecode = cdrug.drugcode\nwhere right(house.villcode,2) <> '00' and ((person.dischargetype is null) or (person.dischargetype = '9')) and round(DATEDIFF(now(),person.birth) /30) IN({$ect0}) and {$ect1}\n) as visit_epi ON per_epi.pcu = visit_epi.pcu1 AND per_epi.pid = visit_epi.pid1\nwhere {$ect2} pid1 is null\norder by right(villcode,2),fname";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$bod = retDatets($row[birth]);
$title = getTitle($row[prename]);
$dateepix = retDatets($row[dateepi]);
$xml .= '<marker ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $title . $row[pname] . '" ';
$xml .= 'bod="' . $bod . '" ';
$xml .= 'ag="' . $row[age] . '" ';
$xml .= 'drugname="' . $row[drugname] . '" ';
$xml .= 'vaccinecode="' . $row[vaccinecode] . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
$xml .= '/>';
}
$xml .= '</markers>';
echo $xml;
$wvill = "AND house.villcode='{$villcode}' ";
}
$str = retdaterangstr($_GET[str]);
$sto = retdaterangsto($_GET[str]);
$strx = retDatets($str);
$stox = retDatets($sto);
$sql = "select *,\ncase when CH99 is not null then 1 else 0 end as chk\n from\n(SELECT\nperson.pcucodeperson,\nperson.pid,\nperson.fname,\nconcat(ctitle.titlename,person.fname ,' ' ,person.lname) AS pname,\nperson.birth,\nROUND(DATEDIFF(now(),person.birth)/365.25) AS age,\nvillage.villcode,\nvillage.villname,\nhouse.hno,\nhouse.hcode,\nhouse.xgis,\nhouse.ygis,\ngroup_concat(cdisease.codechronic) as codex,\ngroup_concat(cdiseasechronic.groupname) as chronicx\nFROM\npersonchronic\ninner join person on person.pcucodeperson = personchronic.pcucodeperson AND person.pid = personchronic.pid\ninner join cdisease on personchronic.chroniccode = cdisease.diseasecode\nleft join cdiseasechronic on cdiseasechronic.groupcode = cdisease.codechronic\ninner join ctitle on person.prename = ctitle.titlecode\ninner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode\ninner join village ON house.villcode = village.villcode and house.pcucode = village.pcucode\nwhere ((person.dischargetype is null) or (person.dischargetype = '9')) AND SUBSTRING(house.villcode,7,2) <> '00' {$wvill}\ngroup by person.pcucodeperson, person.pid\nhaving codex like '%10%'\n) as tmp_per\nleft join\n(select\nperson.pid as pid1,\nperson.pcucodeperson as pcucodeperson1,\n(select l1.datecheck from visitlabchcyhembmsse l1 where person.pid = l1.pid and person.pcucodeperson=l1.pcucodeperson and l1.labcode='CH99' and (l1.datecheck IS NOT NULL OR left(l1.datecheck,4) != '0000') group by l1.pid,l1.pcucodeperson) as CH99,\n(select l1.datecheck from visitlabchcyhembmsse l1 where person.pid = l1.pid and person.pcucodeperson=l1.pcucodeperson and l1.labcode='CH25' and (l1.datecheck IS NOT NULL OR left(l1.datecheck,4) != '0000') group by l1.pid,l1.pcucodeperson) as CH25,\n(select l1.datecheck from visitlabchcyhembmsse l1 where person.pid = l1.pid and person.pcucodeperson=l1.pcucodeperson and l1.labcode='CH07' and (l1.datecheck IS NOT NULL OR left(l1.datecheck,4) != '0000') group by l1.pid,l1.pcucodeperson) as CH07,\n(select l1.datecheck from visitlabchcyhembmsse l1 where person.pid = l1.pid and person.pcucodeperson=l1.pcucodeperson and l1.labcode='CH14' and (l1.datecheck IS NOT NULL OR left(l1.datecheck,4) != '0000') group by l1.pid,l1.pcucodeperson) as CH14,\n(select l1.datecheck from visitlabchcyhembmsse l1 where person.pid = l1.pid and person.pcucodeperson=l1.pcucodeperson and l1.labcode='CH17' and (l1.datecheck IS NOT NULL OR left(l1.datecheck,4) != '0000') group by l1.pid,l1.pcucodeperson) as CH17,\n(select l1.datecheck from visitlabchcyhembmsse l1 where person.pid = l1.pid and person.pcucodeperson=l1.pcucodeperson and l1.labcode='CH04' and (l1.datecheck IS NOT NULL OR left(l1.datecheck,4) != '0000') group by l1.pid,l1.pcucodeperson) as CH04,\n(select l1.datecheck from visitlabchcyhembmsse l1 where person.pid = l1.pid and person.pcucodeperson=l1.pcucodeperson and l1.labcode='CH09' and (l1.datecheck IS NOT NULL OR left(l1.datecheck,4) != '0000') group by l1.pid,l1.pcucodeperson) as CH09,\n(select l1.datecheck from visitlabchcyhembmsse l1 where person.pid = l1.pid and person.pcucodeperson=l1.pcucodeperson and l1.labcode='Cha1' and (l1.datecheck IS NOT NULL OR left(l1.datecheck,4) != '0000') group by l1.pid,l1.pcucodeperson) as Cha1,\n(select l1.datecheck from visitlabchcyhembmsse l1 where person.pid = l1.pid and person.pcucodeperson=l1.pcucodeperson and l1.labcode='Chc1' and (l1.datecheck IS NOT NULL OR left(l1.datecheck,4) != '0000') group by l1.pid,l1.pcucodeperson) as Chc1\nFROM\npersonchronic\ninner join person on person.pcucodeperson = personchronic.pcucodeperson AND person.pid = personchronic.pid\ninner join cdisease on personchronic.chroniccode = cdisease.diseasecode\ninner join cdiseasechronic on cdiseasechronic.groupcode = cdisease.codechronic\ninner Join visitlabchcyhembmsse ON person.pcucodeperson = visitlabchcyhembmsse.pcucodeperson AND visitlabchcyhembmsse.pid = person.pid\ninner join clabchcyhembmsse ON visitlabchcyhembmsse.labcode = clabchcyhembmsse.labcode\ninner join ctitle on person.prename = ctitle.titlecode\ninner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode\ninner join village ON house.villcode = village.villcode and house.pcucode = village.pcucode\nwhere visitlabchcyhembmsse.datecheck between '{$str}' and '{$sto}'\ngroup by person.pid,person.pcucodeperson) as tmp_lab\non tmp_per.pid = tmp_lab.pid1 and tmp_per.pcucodeperson = tmp_lab.pcucodeperson1\norder by villcode, fname";
$result = mysql_query($sql);
$xml = '<markers>';
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
if ($row[CH99] == '') {
$ch99 = 'ยังไม่ตรวจ';
} else {
$ch99 = retDatets($row[CH99]);
}
$xml .= '<marker ';
$xml .= 'hcode="' . $row[hcode] . '" ';
$xml .= 'hono="' . $row[hno] . '" ';
$xml .= 'moo="' . $moo . '" ';
$xml .= 'vill="' . $vill . '" ';
$xml .= 'pname="' . $row[pname] . '" ';
$xml .= 'age="' . $row[age] . '" ';
$xml .= 'codex="' . $row[codex] . '" ';
$xml .= 'chronicx="' . $row[chronicx] . '" ';
$xml .= 'ch99="' . $ch99 . '" ';
$xml .= 'chk="' . $row[chk] . '" ';
$xml .= 'lat="' . $row[ygis] . '" ';
$xml .= 'lng="' . $row[xgis] . '" ';
$xml .= '/>';
function fp()
{
//function นัด fp
$village = $_GET[village];
if ($village == "00000000") {
$wvill = "";
} else {
$wvill = " AND perapp.villcode='{$village}' ";
}
if ($village == "00000000") {
$mu = "ทุกหมู่บ้าน";
} else {
$mu = getvillagename($village);
}
$chk_ncd = $_GET[chk_ncd];
if ($chk_ncd == "2") {
$chkncd = " AND vper.visitdate is not null";
} else {
if ($chk_ncd == "3") {
$chkncd = " AND vper.visitdate is null";
} else {
$chkncd = "";
}
}
$str = $_GET[str];
$strx = retDatets($str);
$sql = "SELECT\nperapp.*,\nvper.visitdate as datechk\nFROM\n(\nSELECT\nperson.pcucodeperson,\nperson.pid,\nperson.idcard,\nperson.fname,\nconcat(ctitle.titlename, person.fname , ' ' , person.lname) AS pname,\nperson.birth,\nROUND(DATEDIFF(now(),person.birth)/365.25) AS age,\nhouse.villcode,\nhouse.hno,\nhouse.hcode,\nhouse.xgis,\nhouse.ygis,\nvisitfp.datedue,\ncdrug.drugname,\ncdrug.drugtypesub,\nvisitfp.datefp\nFROM\nhouse\nINNER JOIN person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nINNER JOIN visitfp ON person.pcucodeperson = visitfp.pcucodeperson AND person.pid = visitfp.pid\ninner JOIN cdrug on visitfp.fpcode = cdrug.drugcode\nLEFT JOIN ctitle ON person.prename = ctitle.titlecode\nwhere visitfp.datedue = '{$str}') as perapp\nleft JOIN (SELECT visit.* FROM visit WHERE visit.visitdate = '{$str}') as vper \non perapp.pcucodeperson = vper.pcucodeperson and perapp.pid = vper.pid\nwhere perapp.pcucodeperson is not null {$chkncd} {$wvill}\norder by perapp.villcode,perapp.fname";
$result = mysql_query($sql);
$txt = '<p align=\'center\'><b>รายงานการนัดวางแผนครอบครัว';
$txt .= "<br>ข้อมูลการนัดวันที่ {$_GET['str']} {$mu}</b></p><br><b>{$hosp}</b><table width='99%' border='0' cellspacing='1' cellpadding='1' class='table table-striped table-hover table-bordered'>\n <tr>\n <th width='4%' scope='col'><div align='center'>ลำดับ</div></th>\n\t<th width='10%' scope='col'><div align='center'>เลขบัตรประชาชน</div></th>\n <th width='10%' scope='col'><div align='center'>ชื่อ - สกุล</div></th>\n\t<th width='5%' scope='col'><div align='center'>อายุ</div></th>\n <th width='6%' scope='col'><div align='center'>บ้านเลขที่</div></th>\n\t<th width='4%' scope='col'><div align='center'>หมู่ที่</div></th>\n <th width='4%' scope='col'><div align='center'>วันที่นัด</div></th>\n\t<th width='4%' scope='col'><div align='center'>ประเภทการนัด</div></th>\n\t<th width='9%' scope='col'><div align='center'>##</div></th>\n\t<th width='9%' scope='col'><div align='center'>วันที่มารับบริการ</div></th>\n\t\n </tr>";
while ($row = mysql_fetch_array($result)) {
$moo = substr($row[villcode], 6, 2);
$vill = getMooVillage($row[villcode]);
$title = getTitle($row[prename]);
if ($row[drugtypesub] == "0") {
$apptypename = "วชย.ทดสอบตั้งครรภ์";
} elseif ($row[drugtypesub] == "1") {
$apptypename = "ยาเม็ด";
} elseif ($row[drugtypesub] == "2") {
$apptypename = "ยาฉีด";
} elseif ($row[drugtypesub] == "3") {
$apptypename = "ยาฝัง";
} elseif ($row[drugtypesub] == "4") {
$apptypename = "ห่วง";
} elseif ($row[drugtypesub] == "5") {
$apptypename = "ถุงยางอนามัย";
} elseif ($row[drugtypesub] == "6") {
$apptypename = "หมันชาย";
} else {
$apptypename = "หมันหญิง";
}
if ($row[datedue] == "") {
$appsick = "";
} else {
$appsick = retDatets($row[datedue]);
}
if ($row[datechk] == "") {
$sick = "";
} else {
$sick = retDatets($row[datechk]);
}
if ($row[datechk] == "") {
$sicksign = "ขาดนัด";
} else {
$sicksign = "มาตามนัด";
}
++$i;
if ($i % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$i}</div></td>\n\t<td><div align='center'>{$row['idcard']}</div></td>\n <td>{$row['pname']}</td>\n\t<td><div align='center'>{$row['age']}</div></td>\n <td><div align='center'>{$row['hno']}</div></td>\n <td><div align='center'>{$moo}</div></td>\n\t<td><div align='center'>{$appsick}</div></td>\n\t<td><div align='center'>{$apptypename}</div></td>\n\t<td><div align='center'>{$sicksign}</div></td>\n\t<td><div align='center'>{$sick}</div></td>\n </tr>";
}
$txt .= "</table><br>";
echo $txt;
}
$vill = getMooVillage($row[villcode]);
$birth = retDatets($row[birth]);
if ($row[m1] == "") {
$m1 = '__/__/____';
} else {
$m1 = retDatets($row[m1]);
}
if ($row[m2] == "") {
$m2 = '__/__/____';
} else {
$m2 = retDatets($row[m2]);
}
if ($row[m3] == "") {
$m3 = '__/__/____';
} else {
$m3 = retDatets($row[m3]);
}
++$i;
if ($i % 2 == 1) {
$cr = " class='altrow'";
} else {
$cr = "";
}
$txt .= " <tr {$cr}>\n <td><div align='center'>{$i}</div></td>\n <td>{$row['mothername']}</td>\n\t<td><div align='center'>{$birth}</div></td>\n\t<td><div align='center'>{$row['age']}</div></td>\n <td><div align='center'>{$row['hno']}</div></td>\n <td><div align='center'>{$moo}</div></td>\n <td><div align='center'>{$row['pregno']}</div></td>\n\t<td>{$row['childname']}</td>\n\t<td><div align='center'>{$m1}</div></td>\n\t<td><div align='center'>{$m2}</div></td>\n\t<td><div align='center'>{$m3}</div></td>\n </tr>";
}
$txt .= "</table><br>";
echo $txt;
} else {
header("Location: ../main/login.php");
}
?>
