143 lines
6.7 KiB
PHP
143 lines
6.7 KiB
PHP
<?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();
|
|
}
|
|
}
|