='" . $start_age . "'";
} else {
$query_age_part = "year(CURDATE())-year(dob)>='" . $start_age . "' and year(CURDATE())-year(dob)<'" . $end_age . "'";
}
$query = "select max(checkup_id),emp_id from checkup_form where emp_id in (select id from patient_master where status='Active' and $query_age_part order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id ";
error_log("query for abnormality count " . $query);
if (!$result = mysqli_query($conn, $query)) {
error_log("error " . mysqli_error($conn) . " query " . $query);
} else {
$count = mysqli_num_rows($result);
$i = 0;
if ($count > 0) {
while ($row_age = mysqli_fetch_assoc($result)) {
$data_age[$start_age][$i] = $row_age['emp_id'];
$i++;
}
} else {
$data_age[$start_age][$i] = 0;
}
}
}
}
// for gender distribution
$query1 = "select max(checkup_id),emp_id from checkup_form where emp_id in (select id from patient_master where status='Active' and gender='M' order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id ";
if (!$result1 = mysqli_query($conn, $query1)) {
error_log("error in query for male count " . mysqli_error($conn));
}
$count = mysqli_num_rows($result1);
$i = 0;
if ($count > 0) {
while ($row_gender = mysqli_fetch_assoc($result1)) {
$data_gender['male'][$i] = $row_gender['emp_id'];
$i++;
}
} else {
$data_gender['male'][$i] = 0;
}
error_log("query for male count " . $query1);
$query2 = "select max(checkup_id),emp_id from checkup_form where emp_id in (select id from patient_master where status='Active' and gender='F' order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id ";
if (!$result2 = mysqli_query($conn, $query2)) {
error_log("error in female count query " . mysqli_error($conn));
}
$count = mysqli_num_rows($result2);
$i = 0;
if ($count > 0) {
while ($row_female = mysqli_fetch_assoc($result2)) {
$data_gender['female'][$i] = $row_female['emp_id'];
$i++;
}
} else {
$data_gender['female'][$i] = 0;
}
error_log("query for female count " . $query2);
// for patient category distribution
$query_cat = "select * from patient_category";
$result_cat = mysqli_query($conn, $query_cat);
while ($row_cat = mysqli_fetch_assoc($result_cat)) {
$pat_cat = $row_cat['patient_cat_id'];
$query3 = "select max(checkup_id),emp_id from checkup_form where emp_id in (select id from patient_master where status='Active' and patient_cat_id = '" . $pat_cat . "' order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id ";
if (!$result3 = mysqli_query($conn, $query3)) {
error_log("error in cat wise query " . $query3);
}
error_log("query " . $query3);
$count = mysqli_num_rows($result3);
$i = 0;
if ($count > 0) {
while ($row_cat1 = mysqli_fetch_assoc($result3)) {
$data_pat_cat[$pat_cat][$i] = $row_cat1['emp_id'];
$i++;
}
} else {
$data_pat_cat[$pat_cat][$i] = 0;
}
}
$data = array();
$data = array(
'age' => $data_age,
'gender' => $data_gender,
'patient_category' => $data_pat_cat
);
error_log("data detailed " . print_r($data, true));
$default_age = array();
$k = 0;
$sql = "select * from rule_age_range";
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_assoc($result)) {
$default_age[$k] = $row['rule_age_start'];
$k++;
}
$labels = array();
for ($i = 0; $i < sizeof($default_age); $i++) {
$end_age = $default_age[$i + 1];
if ($end_age == "" || $end_age == null) {
$end_age = '+';
}
$labels[$i] = $default_age[$i] . "-" . $end_age;
?>
DATA
GENDER
EMPLOYEES RECORD
MALE
FEMALE
DATA
CATEGORY
EMPLOYEES RECORD
DATA
CRITERIA
EMPLOYEES RECORD(BMI < 25)
EMPLOYEES RECORD(BMI >= 25)
='" . $start_age . "'";
} else {
$query_age_part = "year(CURDATE())-year(dob)>='" . $start_age . "' and year(CURDATE())-year(dob)<'" . $end_age . "'";
}
$query = "select distinct(cf.checkup_form_id) from checkup_form_key_value cf left join checkup_parameter cp on cp.`column_name` = cf.checkup_form_key
where cp.key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='bmi')
and cf.checkup_form_value<25 and cf.checkup_form_value!='' and cf.checkup_form_id in (select new_table.id from (select max(checkup_id) as id from checkup_form where emp_id in
(select id from patient_master where status='Active' and $query_age_part order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id) as new_table );
";
error_log("query for abnormality count bmi < 25" . $query);
if (!$result = mysqli_query($conn, $query)) {
error_log("error " . mysqli_error($conn) . " query " . $query);
} else {
$count = mysqli_num_rows($result);
$i = 0;
if ($count > 0) {
while ($row_bmi_age = mysqli_fetch_assoc($result)) {
$data_age_bmi['bmi<25'][$start_age][$i] = $row_bmi_age['checkup_form_id'];
$i++;
}
} else {
$data_age_bmi['bmi<25'][$start_age][$i] = 0;
}
}
$query2 = "select distinct(cf.checkup_form_id) from checkup_form_key_value cf left join checkup_parameter cp on cp.`column_name` = cf.checkup_form_key
where cp.key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='bmi')
and cf.checkup_form_value>=25 and cf.checkup_form_value!='' and cf.checkup_form_id in (select new_table.id from (select max(checkup_id) as id from checkup_form where emp_id in
(select id from patient_master where status='Active' and $query_age_part order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id) as new_table );
";
error_log("query for abnormality count bmi >= 25 " . $query2);
if (!$result_second = mysqli_query($conn, $query2)) {
error_log("error " . mysqli_error($conn) . " query2 " . $query2);
}
$count = mysqli_num_rows($result_second);
$i = 0;
if ($count > 0) {
while ($row_bmi_age_2 = mysqli_fetch_assoc($result_second)) {
$data_age_bmi['bmi>=25'][$start_age][$i] = $row_bmi_age_2['checkup_from_id'];
$i++;
}
} else {
$data_age_bmi['bmi>=25'][$start_age][$i] = 0;
}
}
}
// for gender distribution
// for bmi < 25
$query_gender = "select distinct(cf.checkup_form_id),cf.* from checkup_form_key_value cf left join checkup_parameter cp on cp.`column_name` = cf.checkup_form_key
where cp.key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='bmi')
and cf.checkup_form_value<25 and cf.checkup_form_value!='' and cf.checkup_form_id in (select new_table.id from (select max(checkup_id) as id from checkup_form where emp_id in
(select id from patient_master where status='Active' and gender='M' order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id) as new_table )";
if (!$result_gender = mysqli_query($conn, $query_gender)) {
error_log("error in query for male count bmi < 25 " . mysqli_error($conn));
}
$count = mysqli_num_rows($result_gender);
$i = 0;
if ($count > 0) {
while ($row_bmi_male = mysqli_fetch_assoc($result_gender)) {
$data_gender_bmi['bmi<25']['male'][$i] = $row_bmi_male['checkup_form_id'];
$i++;
}
} else {
$data_gender_bmi['bmi<25']['male'][$i] = 0;
}
error_log("query for male count bmi < 25" . $query_gender);
$query_gender_2 = "select distinct(cf.checkup_form_id),cf.* from checkup_form_key_value cf left join checkup_parameter cp on cp.`column_name` = cf.checkup_form_key
where cp.key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='bmi')
and cf.checkup_form_value>=25 and cf.checkup_form_value!='' and cf.checkup_form_id in (select new_table.id from (select max(checkup_id) as id from checkup_form where emp_id in
(select id from patient_master where status='Active' and gender='M' order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id) as new_table )";
if (!$result_gender_2 = mysqli_query($conn, $query_gender_2)) {
error_log("error in query for male count bmi >= 25 " . mysqli_error($conn));
}
$count = mysqli_num_rows($result_gender_2);
$i = 0;
if ($count > 0) {
while ($row_bmi_male_2 = mysqli_fetch_assoc($result_gender_2)) {
$data_gender_bmi['bmi>=25']['male'][$i] = $row_bmi_male_2['checkup_form_id'];
$i++;
}
} else {
$data_gender_bmi['bmi>=25']['male'][$i] = 0;
}
$query_gender_female = "select distinct(cf.checkup_form_id),cf.* from checkup_form_key_value cf left join checkup_parameter cp on cp.`column_name` = cf.checkup_form_key
where cp.key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='bmi')
and cf.checkup_form_value<25 and cf.checkup_form_value!='' and cf.checkup_form_id in (select new_table.id from (select max(checkup_id) as id from checkup_form where emp_id in
(select id from patient_master where status='Active' and gender='F' order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id) as new_table )";
if (!$result_gender_female = mysqli_query($conn, $query_gender_female)) {
error_log("error in female count query bmi < 25 " . mysqli_error($conn));
}
$count = mysqli_num_rows($result_gender_female);
$i = 0;
if ($count > 0) {
while ($row_bmi_female = mysqli_fetch_assoc($result_gender_female)) {
$data_gender_bmi['bmi<25']['female'][$i] = $row_bmi_female['checkup_form_id'];
$i++;
}
} else {
$data_gender_bmi['bmi<25']['female'][$i] = 0;
}
error_log("query for female count bmi < 25" . $query2);
$query_gender_female_2 = "select distinct(cf.checkup_form_id),cf.* from checkup_form_key_value cf left join checkup_parameter cp on cp.`column_name` = cf.checkup_form_key
where cp.key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='bmi')
and cf.checkup_form_value>=25 and cf.checkup_form_value!='' and cf.checkup_form_id in (select new_table.id from (select max(checkup_id) as id from checkup_form where emp_id in
(select id from patient_master where status='Active' and gender='F' order by id asc) $qry_filter $qry_date and (abnormality_ids ='" . $abnormality_id_main . "' or abnormality_ids like '%," . $abnormality_id_main . "' or abnormality_ids like '" . $abnormality_id_main . ",%' or abnormality_ids like '%," . $abnormality_id_main . ",%') group by emp_id order by emp_id) as new_table )";
if (!$result_gender_female_2 = mysqli_query($conn, $query_gender_female_2)) {
error_log("error in female count query bmi >= 25 " . mysqli_error($conn));
}
$count = mysqli_num_rows($result_gender_female_2);
$i = 0;
if ($count > 0) {
while ($row_bmi_female_2 = mysqli_fetch_assoc($result_gender_female_2)) {
$data_gender_bmi['bmi>=25']['female'][$i] = $row_bmi_female_2['checkup_form_id'];
$i++;
}
} else {
$data_gender_bmi['bmi>=25']['female'][$i] = 0;
}
error_log("query for female count bmi >= 25" . $query2);
$data = array();
$data = array(
'age' => $data_age_bmi,
'gender' => $data_gender_bmi
);
error_log("final graph data " . print_r($data, true));
$labels = array();
for ($i = 0; $i < sizeof($default_age); $i++) {
$end_age = $default_age[$i + 1];
if ($end_age == "" || $end_age == null) {
$end_age = '+';
}
$labels[$i] = $default_age[$i] . "-" . $end_age;
$gender_less_male = $data['gender']['bmi<25']['male'];
$gender_greater_male = $data['gender']['bmi>=25']['male'];
$gender_less_female = $data['gender']['bmi<25']['female'];
$gender_greater_female = $data['gender']['bmi>=25']['female'];
$age_data_arr = $data['age']['bmi<25'][$default_age[$i]];
$age_data_arr_less = $data['age']['bmi>=25'][$default_age[$i]];
?>