ESH/health_index_report.php

<?php
include('includes/config/config.php');
error_reporting(E_ERROR | E_PARSE);

$ohc_type = $_REQUEST['ohc_location'];

$patient_category = $_REQUEST['patient_category'];

$dept = $_REQUEST['dept'];

$emp_designation = $_REQUEST['emp_designation'];

$emp_cadre = $_REQUEST['emp_cadre'];

// $employer_contractor = $_REQUEST['employer_contractor'];

$gender = $_REQUEST['gender'];

$start_date = $_REQUEST['startDate'];

$end_date = $_REQUEST['endDate'];

$year = implode(',', $_REQUEST['year']);

$year2 = implode(',', $_REQUEST['year']);

$year1 = explode(',', $year);

// $year1 = array(2020,2021,2018,2019,2022,2017);

error_log("values::" . $ohc_location . "   " . $patient_category . "  " . $dept . "   " . $emp_designation . "   " . $emp_cadre . "  " . $gender . "   " . $start_date . "   " . $end_date . "  " . $year);

$qry_filter = "";
$qry_date = "";

if (isset($dept) && $dept != '') {
    $qry_filter .= " and dept_id ='" . $dept . "' ";
}


if (isset($ohc_type) && $ohc_type != '') {
    $qry_filter .= " and ohc_type_id ='" . $ohc_type . "' ";
}

if (isset($gender) && $gender != '') {
    $qry_filter .= " and gender ='" . $gender . "' ";
}

if (isset($patient_category) && $patient_category != '') {
    $qry_filter .= " and emp_cat_id ='" . $patient_category . "' ";
}

if (isset($emp_designation) && $emp_designation != '') {
    $qry_filter .= " and designation_id ='" . $emp_designation . "' ";
}

if (isset($emp_cadre) && $emp_cadre != '') {
    $qry_filter .= " and emp_cadre ='" . $emp_cadre . "' ";
}

// if(isset($employer_contractor) && $employer_contractor!=''){
// 	$qry_filter.=" and employer_contractor_id ='".$employer_contractor."' ";
// }

if (isset($start_date) && $start_date != '') {
    $qry_date .= " and date(medical_entry_date) >=str_to_date('" . $start_date . "','%d-%m-%Y') ";
}
if (isset($end_date) && $end_date != '') {
    $qry_date .= " and date(medical_entry_date) <=str_to_date('" . $end_date . "','%d-%m-%Y') ";
}

// if(isset($month) && $month!=''){
//     $qry_date1.=" and month(medical_entry_date) in (".$month.")";
// }



$i = 0;
$data = array();

if (isset($year) && $year != '' && sizeof($year1) > 1) {
    for ($j = 0; $j < sizeof($year1); $j++) {

        error_log("year:" . $year1[$j] . " size:" . sizeof($year1));
        $qry_date = "";
        $qry_doj = "";

        $qry_date .= "and year(medical_entry_date) ='" . $year1[$j] . "'";
        $qry_doj .= "and year(doj) <='" . $year1[$j] . "'";

        $pat_query = "SELECT count(*) as total FROM patient_master where status='Active' $qry_filter $qry_doj";

        $pat_result = mysqli_query($conn, $pat_query);

        $pat_num_row = mysqli_fetch_assoc($pat_result);

        error_log("total:" . $pat_num_row['total']);

        $percentage_for_less_10 = 0;
        $count_health_index = "select max(medical_exam_id),health_index,patient_id from medical_examination where patient_id in (SELECT id FROM `patient_master` where status='Active' $qry_filter $qry_doj ORDER by id asc) and health_index<10 $qry_date GROUP BY patient_id ORDER BY patient_id asc";

        error_log("count health_index:" . $count_health_index);

        $health_index_result = mysqli_query($conn, $count_health_index);

        $health_index_row_count = mysqli_num_rows($health_index_result);
        error_log("count:" . $health_index_row_count);
        $percentage_for_less_10 = ($health_index_row_count / $pat_num_row['total']) * 100;
        $data['Less_Than_10'][$year1[$j]]['total'] = round($percentage_for_less_10, 2);

        $percentage_for_10_15 = 0;
        $count_health_index = "select max(medical_exam_id),health_index,patient_id from medical_examination where patient_id in (SELECT id FROM `patient_master` where status='Active' $qry_filter $qry_doj ORDER by id asc) and health_index>=10 and health_index<15 $qry_date GROUP BY patient_id ORDER BY patient_id asc";

        error_log("count health_index:" . $count_health_index);

        $health_index_result = mysqli_query($conn, $count_health_index);

        $health_index_row_count = mysqli_num_rows($health_index_result);
        error_log("count:" . $health_index_row_count);
        $percentage_for_10_15 = ($health_index_row_count / $pat_num_row['total']) * 100;
        $data['10_15'][$year1[$j]]['total'] = round($percentage_for_10_15, 2);

        $percentage_for_15_20 = 0;
        $count_health_index = "select max(medical_exam_id),health_index,patient_id from medical_examination where patient_id in (SELECT id FROM `patient_master` where status='Active' $qry_filter $qry_doj ORDER by id asc) and health_index>=15 and health_index<20 $qry_date GROUP BY patient_id ORDER BY patient_id asc";

        error_log("count health_index:" . $count_health_index);

        $health_index_result = mysqli_query($conn, $count_health_index);

        $health_index_row_count = mysqli_num_rows($health_index_result);
        error_log("count:" . $health_index_row_count);
        $percentage_for_15_20 = ($health_index_row_count / $pat_num_row['total']) * 100;
        $data['15_20'][$year1[$j]]['total'] = round($percentage_for_15_20, 2);

        $percentage_for_more_20 = 0;
        $count_health_index = "select max(medical_exam_id),health_index,patient_id from medical_examination where patient_id in (SELECT id FROM `patient_master` where status='Active' $qry_filter $qry_doj ORDER by id asc) and health_index>=20 $qry_date GROUP BY patient_id ORDER BY patient_id asc";

        error_log("count health_index:" . $count_health_index);

        $health_index_result = mysqli_query($conn, $count_health_index);

        $health_index_row_count = mysqli_num_rows($health_index_result);
        error_log("count:" . $health_index_row_count);
        $percentage_for_more_20 = ($health_index_row_count / $pat_num_row['total']) * 100;
        $data['More_Than_20'][$year1[$j]]['total'] = round($percentage_for_more_20, 2);
    }
} else {
    if (isset($year2) && $year2 != '') {
        $qry_date .= " and year(medical_entry_date) ='" . $year2 . "'";
        $qry_filter .= " and year(doj) <='" . $year2 . "'";
    }
    $pat_query = "SELECT count(*) as total FROM patient_master where status='Active' $qry_filter";

    $pat_result = mysqli_query($conn, $pat_query);

    $pat_num_row = mysqli_fetch_assoc($pat_result);

    $data['total_count'] = $pat_num_row['total'];

    $percentage_emp_per_health_index = 0;
    $count_health_index = "select max(medical_exam_id),health_index,patient_id from medical_examination where patient_id in (SELECT id FROM `patient_master` where status='Active' $qry_filter ORDER by id asc) and health_index<10 $qry_date GROUP BY patient_id ORDER BY patient_id asc";

    error_log("count health_index:" . $count_health_index);

    $health_index_result = mysqli_query($conn, $count_health_index);

    $health_index_row_count = mysqli_num_rows($health_index_result);
    error_log("count:" . $health_index_row_count);
    $data['health_index_less_10'] = $health_index_row_count;
    $percentage_emp_per_health_index = ($health_index_row_count / $pat_num_row['total']) * 100;
    $data['health_index_less_10_per'] = round($percentage_emp_per_health_index, 2);


    $percentage_emp_per_health_index = 0;
    $count_health_index = "select max(medical_exam_id),health_index,patient_id from medical_examination where patient_id in (SELECT id FROM `patient_master` where status='Active' $qry_filter ORDER by id asc) and health_index>=10 and  health_index<15 $qry_date GROUP BY patient_id ORDER BY patient_id asc";

    error_log("count health_index:" . $count_health_index);

    $health_index_result = mysqli_query($conn, $count_health_index);

    $health_index_row_count = mysqli_num_rows($health_index_result);
    error_log("count:" . $health_index_row_count);
    $data['health_index_10_15'] = $health_index_row_count;
    $percentage_emp_per_health_index = ($health_index_row_count / $pat_num_row['total']) * 100;
    $data['health_index_10_15_per'] = round($percentage_emp_per_health_index, 2);


    $percentage_emp_per_health_index = 0;
    $count_health_index = "select max(medical_exam_id),health_index,patient_id from medical_examination where patient_id in (SELECT id FROM `patient_master` where status='Active' $qry_filter ORDER by id asc) and health_index>=15 and  health_index<20 $qry_date GROUP BY patient_id ORDER BY patient_id asc";

    error_log("count health_index:" . $count_health_index);

    $health_index_result = mysqli_query($conn, $count_health_index);

    $health_index_row_count = mysqli_num_rows($health_index_result);
    error_log("count:" . $health_index_row_count);
    $data['health_index_15_20'] = $health_index_row_count;
    $percentage_emp_per_health_index = ($health_index_row_count / $pat_num_row['total']) * 100;
    $data['health_index_15_20_per'] = round($percentage_emp_per_health_index, 2);

    $percentage_emp_per_health_index = 0;
    $count_health_index = "select max(medical_exam_id),health_index,patient_id from medical_examination where patient_id in (SELECT id FROM `patient_master` where status='Active' $qry_filter ORDER by id asc) and health_index>=20 $qry_date GROUP BY patient_id ORDER BY patient_id asc";

    error_log("count health_index:" . $count_health_index);

    $health_index_result = mysqli_query($conn, $count_health_index);

    $health_index_row_count = mysqli_num_rows($health_index_result);
    error_log("count:" . $health_index_row_count);
    $data['health_index_20'] = $health_index_row_count;
    $percentage_emp_per_health_index = ($health_index_row_count / $pat_num_row['total']) * 100;
    $data['health_index_20_per'] = round($percentage_emp_per_health_index, 2);
}

echo json_encode($data);