SURVEY OF CHRONIC DISEASES ANDROID APP TABLE
SQL> CREATE TABLE survey_diseases
2 ( DIS_ID VARCHAR2(6) NOT NULL,
3 DIS_NAME varchar2(50) NOT NULL,
4 PRIMARY KEY(DIS_ID)
5 );
Table created. NOW insert VALUES
SQL> commit;
Commit complete.
SQL> insert into survey_diseases values('1','Diabetes on medicine');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into survey_diseases values('2','High blood pressure on medicine');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into survey_diseases values('3','Heart Stent/bypass and or heart medicine');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into survey_diseases values('4','Kidney dialysis');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into survey_diseases values('5','Any cancer on treatment');
1 row created.
SQL> commit;
Commit complete.
------------------------------------------------------------------------------------------------------------------------
SQL> CREATE TABLE survey_diseases_hospital
2 ( EMPNO VARCHAR2(6) NOT NULL,
3 EMP_NAME varchar2(50) NOT NULL,
4 SEX varchar2(1) NOT NULL,
5 REL_CD varchar2(2) NOT NULL,
6 AGE varchar2(2) NOT NULL,
7 DISEASES varchar2(2) NOT NULL,
8 PRIMARY KEY(EMPNO,REL_CD)
9 );
Table created.
SQL> commit;
Commit complete.
SQL>
Make PHP script To insert Check values Inside above tables:
<?php
$db = oci_connect('XXXX', 'XXXX', 'XXXXXX');
$response = array();
//http://localhost:81/cmms/hospitalscript/SURVEY/insert/survey_register.php?EMPNO=0266663&EMP_NAME=ak&SEX=M&AGE=36&REL_CD=01&DISEASES=[{"traceValue":"1"},{"traceValue":"3"},{"traceValue":"4"}]
//http://localhost:81/cmms/hospitalscript/SURVEY/insert/survey_register.php?EMPNO=026666&EMP_NAME=akl&SEX=M&AGE=36&REL_CD=01&DISEASES=[{"traceValue":"1"}]
$EMPNO=$_REQUEST['EMPNO'];
$EMP_NAME=$_REQUEST['EMP_NAME'];
$SEX=$_REQUEST['SEX'];
$REL_CD=$_REQUEST['REL_CD'];
$AGE=$_REQUEST['AGE'];
VERY IMPORTANT LINES BELOW TO TAKE JSON ARRAY SELECTED VALUES FROM ANDROID APP
$DISEASES=$_REQUEST['DISEASES']; //it will hold the DISEASES code and table for that code list is survey_diseases table
$traceArray = json_decode($DISEASES, true); //Convert JSON String into PHP Array
$countHeader=0;
$countTrace=0;
foreach($traceArray as $row) //Extract the Array Values by using Foreach Loop
{
// Make Multiple Insert Query
$sql="INSERT INTO survey_diseases_hospital (EMPNO, EMP_NAME, SEX, REL_CD, AGE, DISEASES)
VALUES('$EMPNO','$EMP_NAME','$SEX','$REL_CD','$AGE','".$row["traceValue"]."')";
$compiled = oci_parse($db, $sql);
$countTrace++;
$rTrace = oci_execute($compiled, OCI_NO_AUTO_COMMIT);
}
if (!$rTrace )
{
$e = oci_error($compiled);
oci_rollback($db); // rollback changes to both tables
trigger_error(htmlentities($e['message']), E_USER_ERROR);
}
$result = oci_commit($db);
if (!$result) {
$err = oci_error($db);
trigger_error(htmlentities($err['message']), E_USER_ERROR);
}
//echo $countTrace." rows inserted in survey_diseases_hospital ";
if ($result)
{
$response["success"] = $countTrace;
$response["message"] = "Registered successfully.";
echo json_encode($response);
}
else
{
$response["success"] = $countTrace;
$response["message"] = "There seems to be problem";
echo json_encode($response);
}
?>
REPORTS TIME
SELECT age_group,
(SELECT COUNT(DISTINCT EMPNO) FROM survey_diseases_hospital WHERE SEX='M'
AND AGE BETWEEN TO_NUMBER(SUBSTR(age_group,1,INSTR(age_group, '-')-1)) AND TO_NUMBER(SUBSTR(age_group,INSTR(age_group, '-')+1))) TOTAL_MALE,
(SELECT COUNT(DISTINCT EMPNO) FROM survey_diseases_hospital WHERE SEX='F'
AND AGE BETWEEN TO_NUMBER(SUBSTR(age_group,1,INSTR(age_group, '-')-1)) AND TO_NUMBER(SUBSTR(age_group,INSTR(age_group, '-')+1))) TOTAL_FEMALE,
SUM(diseases_1_M) diseases_1_M, SUM(diseases_1_F) diseases_1_F,
SUM(diseases_2_M) diseases_2_M, SUM(diseases_2_F) diseases_2_F,
SUM(diseases_3_M) diseases_3_M, SUM(diseases_3_F) diseases_3_F,
SUM(diseases_4_M) diseases_4_M, SUM(diseases_4_F) diseases_4_F,
SUM(diseases_5_M) diseases_5_M, SUM(diseases_5_F) diseases_5_F
FROM (select
case
when age between 0 and 9 then '0-9'
when age between 10 and 19 then '10-19'
when age between 20 and 29 then '20-29'
when age between 30 and 39 then '30-39'
when age between 40 and 49 then '40-49'
when age between 50 and 59 then '50-59'
when age between 60 and 69 then '60-69'
when age between 70 and 79 then '70-79'
when age between 80 and 89 then '80-89'
else '90-99'
end as age_group,
case
when diseases='1' and sex='M' then 1
else 0
end as diseases_1_M,
case
when diseases='1' and sex='F' then 1
else 0
end as diseases_1_F,
case
when diseases='2' and sex='M' then 1
else 0
end as diseases_2_M,
case
when diseases='2' and sex='F' then 1
else 0
end as diseases_2_F,
case
when diseases='3' and sex='M' then 1
else 0
end as diseases_3_M,
case
when diseases='3' and sex='F' then 1
else 0
end as diseases_3_F,
case
when diseases='4' and sex='M' then 1
else 0
end as diseases_4_M,
case
when diseases='4' and sex='F' then 1
else 0
end as diseases_4_F,
case
when diseases='5' and sex='M' then 1
else 0
end as diseases_5_M,
case
when diseases='5' and sex='F' then 1
else 0
end as diseases_5_F
from survey_diseases_hospital) GROUP BY age_group order by age_group;
Post a Comment
0Comments