<?php
include('includes/config/config.php');
include('includes/functions.php');
begin();

$arr = array("Pre_Employment_Regular", "Pre_Employment_40_Years_and_Above", "Pre_Employment_Manager", "Health_Checkup_50_Years_and_Above", "Statutory_Medical_Checkup", "Service_Area_Medical_Checkup", "C2_And_Above_Medical_Checkup");

for ($x = 0; $x < sizeof($arr); $x++) {

    $get_data = "select * from checkup_form where checkup_type_id=(select checkup_type_id from checkup_type where checkup_type_code like '%$arr[$x]%') and (health_index IS NULL OR health_index = '');";

    error_log("query " . $get_data);

    $get_result = mysqli_query($conn, $get_data);
    while ($get_row = mysqli_fetch_assoc($get_result)) {

        $section_ids = $get_row['checkup_section_ids'];


        $checkup_column_name = "select column_name as name from checkup_parameter where checkup_form_section_id in ($section_ids) and key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='systolic blood pressure' )";
        error_log("section column getting query " . $checkup_column_name);
        $result_column_name = mysqli_query($conn, $checkup_column_name);
        $row_column_name = mysqli_fetch_assoc($result_column_name);


        // for sbp
        $get_param = "select * from checkup_form_key_value where checkup_form_id='" . $get_row['checkup_id'] . "' and checkup_form_key='" . $row_column_name['name'] . "'";

        $result_param = mysqli_query($conn, $get_param);
        $row_param = mysqli_fetch_assoc($result_param);

        $sbp = $row_param['checkup_form_value'];

        error_log("sbp " . $sbp);


        $checkup_column_name = "select column_name as name from checkup_parameter where checkup_form_section_id in ($section_ids) and key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='diastolic blood pressure' )";
        error_log("section column getting query " . $checkup_column_name);
        $result_column_name = mysqli_query($conn, $checkup_column_name);
        $row_column_name = mysqli_fetch_assoc($result_column_name);

        // for dbp
        $get_param = "select * from checkup_form_key_value where checkup_form_id='" . $get_row['checkup_id'] . "' and checkup_form_key='" . $row_column_name['name'] . "'";

        $result_param = mysqli_query($conn, $get_param);
        $row_param = mysqli_fetch_assoc($result_param);

        $dbp = $row_param['checkup_form_value'];

        error_log("dbp " . $dbp);


        $checkup_column_name = "select column_name as name from checkup_parameter where checkup_form_section_id in ($section_ids) and key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='fbs' )";
        error_log("section column getting query " . $checkup_column_name);
        $result_column_name = mysqli_query($conn, $checkup_column_name);
        $row_column_name = mysqli_fetch_assoc($result_column_name);

        // for fbs
        $get_param = "select * from checkup_form_key_value where checkup_form_id='" . $get_row['checkup_id'] . "' and checkup_form_key='" . $row_column_name['name'] . "'";

        $result_param = mysqli_query($conn, $get_param);
        $row_param = mysqli_fetch_assoc($result_param);

        $fbs = $row_param['checkup_form_value'];

        error_log("fbs " . $fbs);


        $checkup_column_name = "select column_name as name from checkup_parameter where checkup_form_section_id in ($section_ids) and key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='height' )";
        error_log("section column getting query " . $checkup_column_name);
        $result_column_name = mysqli_query($conn, $checkup_column_name);
        $row_column_name = mysqli_fetch_assoc($result_column_name);

        // for height

        $get_param = "select * from checkup_form_key_value where checkup_form_id='" . $get_row['checkup_id'] . "' and checkup_form_key='" . $row_column_name['name'] . "'";

        $result_param = mysqli_query($conn, $get_param);
        $row_param = mysqli_fetch_assoc($result_param);

        $height = $row_param['checkup_form_value'];

        error_log("height " . $height);


        $checkup_column_name = "select column_name as name from checkup_parameter where checkup_form_section_id in ($section_ids) and key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='weight' )";
        error_log("section column getting query " . $checkup_column_name);
        $result_column_name = mysqli_query($conn, $checkup_column_name);
        $row_column_name = mysqli_fetch_assoc($result_column_name);

        // for weight
        $get_param = "select * from checkup_form_key_value where checkup_form_id='" . $get_row['checkup_id'] . "' and checkup_form_key='" . $row_column_name['name'] . "'";

        $result_param = mysqli_query($conn, $get_param);
        $row_param = mysqli_fetch_assoc($result_param);

        $weight = $row_param['checkup_form_value'];

        error_log("weight " . $weight);


        $bmi = 0;

        if ($height != null && $height != '' && $weight != null && $weight != '') {

            $height = floatval($height);

            $weight = floatval($weight);

            if ($height != 0.0) {
                $bmi = ($weight / ($height * $height)) * 100 * 100;
            }
        }

        $checkup_column_name = "select column_name as name from checkup_parameter where checkup_form_section_id in ($section_ids) and key_health_map_name=(select key_param_id from key_health_reportable_parameter_master where key_param_name='total cholesterol' )";
        error_log("section column getting query " . $checkup_column_name);
        $result_column_name = mysqli_query($conn, $checkup_column_name);
        $row_column_name = mysqli_fetch_assoc($result_column_name);

        // for cholestrol
        $get_param = "select * from checkup_form_key_value where checkup_form_id='" . $get_row['checkup_id'] . "' and checkup_form_key='" . $row_column_name['name'] . "'";

        $result_param = mysqli_query($conn, $get_param);
        $row_param = mysqli_fetch_assoc($result_param);

        $cholesterol = $row_param['checkup_form_value'];

        error_log("cholesterol " . $cholesterol);



        $health_score = calculateHealthIndex($sbp, $dbp, $bmi, $fbs, $cholesterol);

        $update_health_index = "update checkup_form set health_index='" . $health_score . "' where checkup_id='" . $get_row['checkup_id'] . "'";
        error_log("query for HI " . $update_health_index);
        if (!$result_update = mysqli_query($conn, $update_health_index)) {
            error_log("error while updating " . mysqli_error($conn));
            rollback();
        }
        commit();
    }
}