* User: Allan Wiz
* Date: 4/23/15
* Time: 1:59 PM
*/
// Connect to the DB
require '../classes/aardb_conn.php';
header('Content-Type: Text/xml');
include "../Charts/JSClass/FusionCharts.php";
include "../Charts/JSClass/FC_Colors.php";
include "../Charts/JSClass/FusionCharts_Gen.php";
// SQL query for category labels
$start = date("d/M/Y", strtotime('-14 days'));
$today = date("d/M/Y");
$sql = "SELECT NAVS.NAV_ID, NAVS.NAV_DATE as NAV_DATE, NAVS.SECURITY_CODE, NAVS.AMOUNT as AMOUNT, SECURITIES.DESCRIPT as DESCRIPTION\r FROM NAVS INNER JOIN SECURITIES ON NAVS.SECURITY_CODE = SECURITIES.SECURITY_CODE\r WHERE NAVS.CONFIRMD = 1 AND NAVS.NAV_DATE BETWEEN '{$start}' AND '{$today}' ORDER BY NAVS.NAV_DATE ASC";
$result = oci_parse($conn, $sql) or die("");
oci_execute($result);
//$strXML will be used to store the entire XML document generated
//Generate the chart element
$strXML = "<chart legendPostion='' caption='Factory Output report' subCaption='By Quantity' xAxisName='Factory' yAxisName='Units' showValues='0' formatNumberScale='0' rotateValues='1' animation='1'>";
// Build category XML
$strXML .= buildCategories($result, "DESCRIPTION");
// Build datasets XML
$strXML .= buildDatasets($result, "NAV_DATE", "AMOUNT");
//Finally, close <chart> element
$strXML .= "</chart>";
//Create the chart - Pie 3D Chart with data from strXML
//echo renderChart("../../FusionCharts/MSLine.swf", "", $strXML, "Rates", 700, 400, false, true);
// Free database resource
/*mysql_free_result($resultCategories);
mysql_free_result($resultData);
mysql_close($link);*/
$strQueryData = "select Factory_Master.FactoryName, DATE_FORMAT(Factory_Output.DatePro,'%c-%d-%Y') as DatePro, Factory_Output.Quantity from Factory_Master Factory_Master, Factory_Output Factory_Output where Factory_Output.FactoryID = Factory_Master.FactoryId order by Factory_Output.FactoryID, Factory_Output.DatePro";
// Query database
$resultData = mysql_query($strQueryData) or die(mysql_error());
//We also keep a flag to specify whether we've to animate the chart or not.
//If the user is viewing the detailed chart and comes back to this page, he shouldn't
//see the animation again.
$animateChart = @$_GET['animate'];
//Set default value of 1
if ($animateChart == "") {
$animateChart = "1";
}
//$strXML will be used to store the entire XML document generated
//Generate the chart element
$strXML = "<chart legendPostion='' caption='Factory Output report' subCaption='By Quantity' xAxisName='Factory' yAxisName='Units' showValues='0' formatNumberScale='0' rotateValues='1' animation=' " . $animateChart . "'>";
// Build category XML
$strXML .= buildCategories($resultCategories, "DatePro");
// Build datasets XML
$strXML .= buildDatasets($resultData, "Quantity", "FactoryName");
//Finally, close <chart> element
$strXML .= "</chart>";
//Create the chart - Pie 3D Chart with data from strXML
echo renderChart("../../FusionCharts/MSLine.swf", "", $strXML, "FactorySum", 700, 400, false, false);
// Free database resource
mysql_free_result($resultCategories);
mysql_free_result($resultData);
mysql_close($link);
/***********************************************************************************************
* Function to build XML for categories
* @param $result Database resource
* @param $labelField Field name as String that contains value for chart category labels
*
$strQueryData = "SELECT\ncase when getageyearnum(person.birth,'{$str}') between 0 and 4 then 'อายุ 0 - 4 ปี' \n when getageyearnum(person.birth,'{$str}') between 5 and 9 then 'อายุ 5 - 9 ปี'\n when getageyearnum(person.birth,'{$str}') between 10 and 14 then 'อายุ 10 - 14 ปี' \n when getageyearnum(person.birth,'{$str}') between 15 and 19 then 'อายุ 15 - 19 ปี' \n when getageyearnum(person.birth,'{$str}') between 20 and 24 then 'อายุ 20 - 24 ปี' \n when getageyearnum(person.birth,'{$str}') between 25 and 29 then 'อายุ 25 - 29 ปี' \n when getageyearnum(person.birth,'{$str}') between 30 and 34 then 'อายุ 30 - 34 ปี' \n when getageyearnum(person.birth,'{$str}') between 35 and 39 then 'อายุ 35 - 39 ปี' \n when getageyearnum(person.birth,'{$str}') between 40 and 44 then 'อายุ 40 - 44 ปี' \n when getageyearnum(person.birth,'{$str}') between 45 and 49 then 'อายุ 45 - 49 ปี' \n when getageyearnum(person.birth,'{$str}') between 50 and 54 then 'อายุ 50 - 54 ปี' \n when getageyearnum(person.birth,'{$str}') between 55 and 59 then 'อายุ 55 - 59 ปี' \n when getageyearnum(person.birth,'{$str}') between 60 and 64 then 'อายุ 60 - 64 ปี' \n when getageyearnum(person.birth,'{$str}') between 65 and 69 then 'อายุ 65 - 69 ปี' \n when getageyearnum(person.birth,'{$str}') between 70 and 74 then 'อายุ 70 - 74 ปี' \n when getageyearnum(person.birth,'{$str}') between 75 and 79 then 'อายุ 75 - 79 ปี' \n when getageyearnum(person.birth,'{$str}') between 80 and 120 then 'อายุ 80 ปีขึ้นไป' else null end as detail,\n'ชาย' as sex,\nsum(case when person.sex = '1' then 1 else 0 end)/(select count(distinct p.pid) from house h\nInner Join person p ON h.pcucode = p.pcucodeperson AND h.hcode = p.hcode\nleft join persondeath pd on p.pcucodeperson = pd.pcucodeperson and p.pid = pd.pid\nWHERE (((p.dischargetype is null) or (p.dischargetype = '9')) or DATE_FORMAT(pd.deaddate,'%Y') <= DATE_FORMAT('{$str}','%Y')) and DATE_FORMAT(p.birth,'%Y') <= DATE_FORMAT('{$str}','%Y') and SUBSTRING(h.villcode,7,2) <> '00')*-100 as gender\nFROM\nhouse\nInner Join person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nleft join persondeath on person.pcucodeperson = persondeath.pcucodeperson and person.pid = persondeath.pid\nWHERE (((person.dischargetype is null) or (person.dischargetype = '9')) or DATE_FORMAT(persondeath.deaddate,'%Y') <= DATE_FORMAT('{$str}','%Y')) and DATE_FORMAT(person.birth,'%Y') <= DATE_FORMAT('{$str}','%Y') and SUBSTRING(house.villcode,7,2) <> '00'\ngroup by detail\nUNION\nSELECT\ncase when getageyearnum(person.birth,'{$str}') between 0 and 4 then 'อายุ 0 - 4 ปี' \n when getageyearnum(person.birth,'{$str}') between 5 and 9 then 'อายุ 5 - 9 ปี'\n when getageyearnum(person.birth,'{$str}') between 10 and 14 then 'อายุ 10 - 14 ปี' \n when getageyearnum(person.birth,'{$str}') between 15 and 19 then 'อายุ 15 - 19 ปี' \n when getageyearnum(person.birth,'{$str}') between 20 and 24 then 'อายุ 20 - 24 ปี' \n when getageyearnum(person.birth,'{$str}') between 25 and 29 then 'อายุ 25 - 29 ปี' \n when getageyearnum(person.birth,'{$str}') between 30 and 34 then 'อายุ 30 - 34 ปี' \n when getageyearnum(person.birth,'{$str}') between 35 and 39 then 'อายุ 35 - 39 ปี' \n when getageyearnum(person.birth,'{$str}') between 40 and 44 then 'อายุ 40 - 44 ปี' \n when getageyearnum(person.birth,'{$str}') between 45 and 49 then 'อายุ 45 - 49 ปี' \n when getageyearnum(person.birth,'{$str}') between 50 and 54 then 'อายุ 50 - 54 ปี' \n when getageyearnum(person.birth,'{$str}') between 55 and 59 then 'อายุ 55 - 59 ปี' \n when getageyearnum(person.birth,'{$str}') between 60 and 64 then 'อายุ 60 - 64 ปี' \n when getageyearnum(person.birth,'{$str}') between 65 and 69 then 'อายุ 65 - 69 ปี' \n when getageyearnum(person.birth,'{$str}') between 70 and 74 then 'อายุ 70 - 74 ปี' \n when getageyearnum(person.birth,'{$str}') between 75 and 79 then 'อายุ 75 - 79 ปี' \n when getageyearnum(person.birth,'{$str}') between 80 and 120 then 'อายุ 80 ปีขึ้นไป' else null end as detail,\n'หญิง' as sex,\nsum(case when person.sex = '2' then 1 else 0 end)/(select count(distinct p.pid) from house h\nInner Join person p ON h.pcucode = p.pcucodeperson AND h.hcode = p.hcode\nleft join persondeath pd on p.pcucodeperson = pd.pcucodeperson and p.pid = pd.pid\nWHERE (((p.dischargetype is null) or (p.dischargetype = '9')) or DATE_FORMAT(pd.deaddate,'%Y') <= DATE_FORMAT('{$str}','%Y')) and DATE_FORMAT(p.birth,'%Y') <= DATE_FORMAT('{$str}','%Y') and SUBSTRING(h.villcode,7,2) <> '00')*100 as gender\nFROM\nhouse\nInner Join person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode\nleft join persondeath on person.pcucodeperson = persondeath.pcucodeperson and person.pid = persondeath.pid\nWHERE (((person.dischargetype is null) or (person.dischargetype = '9')) or DATE_FORMAT(persondeath.deaddate,'%Y') <= DATE_FORMAT('{$str}','%Y')) and DATE_FORMAT(person.birth,'%Y') <= DATE_FORMAT('{$str}','%Y') and SUBSTRING(house.villcode,7,2) <> '00'\ngroup by detail\norder by sex, SUBSTRING(detail,6,2)*1 desc";
// Query database
$resultData = mysql_query($strQueryData) or die(mysql_error());
//We also keep a flag to specify whether we've to animate the chart or not.
//If the user is viewing the detailed chart and comes back to this page, he shouldn't
//see the animation again.
$animateChart = @$_GET['animate'];
//Set default value of 1
if ($animateChart == "") {
$animateChart = "1";
}
//$strXML will be used to store the entire XML document generated
//Generate the chart element
$strXML = "<chart legendPostion='' caption='ปิรามิดประชากร ปี พ.ศ." . $sto . "' subCaption='" . $hosp . "' xAxisName='' yAxisName='ร้อยละ' showValues='1' formatNumberScale='0' rotateValues='1' animation=' " . $animateChart . "'>";
// Build category XML
$strXML .= buildCategories($resultCategories, "detail");
// Build datasets XML
$strXML .= buildDatasets($resultData, "gender", "sex");
//Finally, close <chart> element
$strXML .= "</chart>";
//Create the chart - Pie 3D Chart with data from strXML
echo renderChart("FusionCharts/StackedBar2D.swf", "", $strXML, "FactorySum", 750, 450, false, false);
// Free database resource
mysql_free_result($resultCategories);
mysql_free_result($resultData);
mysql_close($link);
/***********************************************************************************************
* Function to build XML for categories
* @param $result Database resource
* @param $labelField Field name as String that contains value for chart category labels
*
$strQueryData = "select\npcucodeperson,\nvillcode,\nvillname,\n'{$gagename}' as mark,\ncount(distinct pid) as per\nfrom\n(SELECT\np.pcucodeperson,\np.pid,\np.fname,\nCONCAT(ctitle.titlename,p.fname,' ',p.lname) AS pname,\nh.hno,\nh.villcode,\nvillage.villname,\nh.xgis,\nh.ygis,\np.birth,\nFLOOR((TO_DAYS('{$daymidyear}')-TO_DAYS(p.birth))/365.25) AS age\nFROM\nvillage\nINNER JOIN house as h ON village.pcucode = h.pcucode AND village.villcode = h.villcode\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\nperson.pcucodeperson as pcucodeperson1,\nperson.pid as pid1,\nvisit.visitno as visitno1,\nvisit.symptoms,\nvisit.vitalcheck,\nvisitdiag.diagcode,\nvisit.visitdate as visitdate\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\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 visitdiag.diagcode = 'Z11.6' and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 )\n) as fp\non per.pcucodeperson = fp.pcucodeperson1 and per.pid = fp.pid1\nleft join\n(SELECT\nperson.pcucodeperson as pcucodeperson2,\nperson.pid as pid2,\nvisit.visitno as visitno2,\nvisitdiag.diagcode as para,\ncdisease.diseasenamethai as diseasenamethai,\ncdisease.diseasename as diseasename\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 cdisease on visitdiag.diagcode = cdisease.diseasecode\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 visitdiag.diagcode != 'Z11.6' and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 ) \n) as para\non para.pcucodeperson2 = fp.pcucodeperson1 and para.pid2 = fp.pid1 and para.visitno2 = fp.visitno1\ngroup by pcucodeperson, villcode\nunion\nselect\npcucodeperson,\nvillcode,\nvillname,\n'ได้รับการตรวจ' as mark,\ncount(distinct pid1) as per\nfrom\n(SELECT\np.pcucodeperson,\np.pid,\np.fname,\nCONCAT(ctitle.titlename,p.fname,' ',p.lname) AS pname,\nh.hno,\nh.villcode,\nvillage.villname,\nh.xgis,\nh.ygis,\np.birth,\nFLOOR((TO_DAYS(NOW())-TO_DAYS(p.birth))/365.25) AS age\nFROM\nvillage\nINNER JOIN house as h ON village.pcucode = h.pcucode AND village.villcode = h.villcode\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\nperson.pcucodeperson as pcucodeperson1,\nperson.pid as pid1,\nvisit.visitno as visitno1,\nvisit.symptoms,\nvisit.vitalcheck,\nvisitdiag.diagcode,\nvisit.visitdate as visitdate\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\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 visit.vitalcheck like '%พบ%' and visitdiag.diagcode = 'Z11.6' and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 )\n) as fp\non per.pcucodeperson = fp.pcucodeperson1 and per.pid = fp.pid1\nleft join\n(SELECT\nperson.pcucodeperson as pcucodeperson2,\nperson.pid as pid2,\nvisit.visitno as visitno2,\nvisitdiag.diagcode as para,\ncdisease.diseasenamethai as diseasenamethai,\ncdisease.diseasename as diseasename\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 cdisease on visitdiag.diagcode = cdisease.diseasecode\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 visit.vitalcheck like 'พบ' and visitdiag.diagcode != 'Z11.6' and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 ) \n) as para\non para.pcucodeperson2 = fp.pcucodeperson1 and para.pid2 = fp.pid1 and para.visitno2 = fp.visitno1\ngroup by pcucodeperson, villcode\nunion\nselect\npcucodeperson,\nvillcode,\nvillname,\n'ได้ตรวจพยาธิใบไม้ตับ' as mark,\nsum(case when para between 'B66.0' and 'B66.3' then 1 else 0 end) as per\nfrom\n(SELECT\np.pcucodeperson,\np.pid,\np.fname,\nCONCAT(ctitle.titlename,p.fname,' ',p.lname) AS pname,\nh.hno,\nh.villcode,\nvillage.villname,\nh.xgis,\nh.ygis,\np.birth,\nFLOOR((TO_DAYS('{$daymidyear}')-TO_DAYS(p.birth))/365.25) AS age\nFROM\nvillage\nINNER JOIN house as h ON village.pcucode = h.pcucode AND village.villcode = h.villcode\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\nperson.pcucodeperson as pcucodeperson1,\nperson.pid as pid1,\nvisit.visitno as visitno1,\nvisit.symptoms,\nvisit.vitalcheck,\nvisitdiag.diagcode,\nvisit.visitdate as visitdate\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\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 visit.vitalcheck like '%พบ%' and visitdiag.diagcode = 'Z11.6' and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 )\n) as fp\non per.pcucodeperson = fp.pcucodeperson1 and per.pid = fp.pid1\nleft join\n(SELECT\nperson.pcucodeperson as pcucodeperson2,\nperson.pid as pid2,\nvisit.visitno as visitno2,\nvisitdiag.diagcode as para,\ncdisease.diseasenamethai as diseasenamethai,\ncdisease.diseasename as diseasename\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 cdisease on visitdiag.diagcode = cdisease.diseasecode\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 visit.vitalcheck like 'พบ' and visitdiag.diagcode != 'Z11.6' and (visit.flagservice <'04' OR visit.flagservice is null OR length(trim(visit.flagservice))=0 ) \n) as para\non para.pcucodeperson2 = fp.pcucodeperson1 and para.pid2 = fp.pid1 and para.visitno2 = fp.visitno1\ngroup by pcucodeperson, villcode";
// Query database
$resultData = mysql_query($strQueryData) or die(mysql_error());
//We also keep a flag to specify whether we've to animate the chart or not.
//If the user is viewing the detailed chart and comes back to this page, he shouldn't
//see the animation again.
$animateChart = @$_GET['animate'];
//Set default value of 1
if ($animateChart == "") {
$animateChart = "1";
}
//$strXML will be used to store the entire XML document generated
//Generate the chart element
$strXML = "<chart legendPostion='' caption='ประชาชน" . $gagename . "ที่ได้รับการตรวจหาไข่พยาธิในอุจจาระ ข้อมูลระหว่างวันที่ " . $_GET[str] . " ถึง " . $_GET[sto] . " " . $mu . " " . $live_type_name . "' subCaption='" . $hosp . "' xAxisName='' yAxisName='คน' showValues='1' formatNumberScale='0' rotateValues='1' animation=' " . $animateChart . "'>";
// Build category XML
$strXML .= buildCategories($resultCategories, "villname");
// Build datasets XML
$strXML .= buildDatasets($resultData, "per", "mark");
//Finally, close <chart> element
$strXML .= "</chart>";
//Create the chart - Pie 3D Chart with data from strXML
echo renderChart("FusionCharts/MSColumn2D.swf", "", $strXML, "FactorySum", 750, 450, false, false);
// Free database resource
mysql_free_result($resultCategories);
mysql_free_result($resultData);
mysql_close($link);
/***********************************************************************************************
* Function to build XML for categories
* @param $result Database resource
* @param $labelField Field name as String that contains value for chart category labels
*