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;
}
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;
}
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$hosp = $row[chospital_hosname];
//ได้ตัวแปร $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);
