<?php
ini_set('max_execution_time', 120000);
include('includes/config/config.php');
include('includes/functions.php');
include('log_entry.php');
require_once('excel/vendor/php-excel-reader/excel_reader2.php');
require_once('excel/vendor/SpreadsheetReader.php');
header('Content-type: application/json');
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);

function removeSpecialCharactersAndSpaces($inputString)
{
    // Remove all characters that are not letters or digits
    return preg_replace('/[^A-Za-z0-9]/', '', $inputString);
}
function removeSpecialCharacters($inputString)
{
    // Remove all characters that are not letters, digits, or whitespace
    return preg_replace('/[^A-Za-z0-9\s]/', '', $inputString);
}

function copyFileWithTimestamp($sourceFolder, $destinationFolder, $filename)
{
    error_log("source folder " . $sourceFolder);
    error_log("destination folder " . $destinationFolder);
    error_log("file  " . $filename);

    // Construct the full paths of the source and destination files
    $sourceFilePath = $sourceFolder . '/' . $filename;
    $destinationFilePath = $destinationFolder . '/' . $filename;

    // Check if the source file exists
    if (file_exists($sourceFilePath)) {

        if (rename($sourceFilePath, $destinationFilePath)) {

            error_log(" file moved successfully " . true);
        } else {

            error_log(" file moved successfully " . false);
        }
    } else {

        error_log(" file doesn't exist ");
    }
    // copy($sourceFilePath,$destinationFilePath);
}

function processData($folderPath)
{

    $sourceFolder = $folderPath;
    $destinationFolder = 'uploads/in_process/';
    $destinationFolder_success = 'uploads/success/';
    $destinationFolder_failure = 'uploads/failure/';

    $curr_date = date('Y-m-d');

    $files_xlsx = glob($folderPath . '*.xlsx');
    $files_xls = glob($folderPath . '*.xls');
    $files = array_merge($files_xlsx, $files_xls);

    $insert_counter = 0;
    $update_counter = 0;
    $fail_counter = 0;

    foreach ($files as $file) {

        error_log("file name " . $file);

        // $file = basename($file);

        // error_log("file name " . $file);

        // copyFileWithTimestamp($sourceFolder, $destinationFolder, basename($file));

        $dbColumnNameList = array();

        $Reader = new SpreadsheetReader($file);

        $sheetCount = count($Reader->sheets());

        for ($i = 0; $i < 1; $i++) {

            $j = 0;

            error_log("IN LOOP");

            $Reader->ChangeSheet($i);
            $j = 0;
            $max_cols = 0;
            $crntColIndex = 0;

            foreach ($Reader as $Row) {

                $crntColIndex = 0;

                // read row first to read table fields

                if ($j == 0) {
                    $crntColIndex = 0;
                    $nondb_col = 0;

                    while (isset($Row[$crntColIndex]) && mysqli_real_escape_string($GLOBALS['conn'], $Row[$crntColIndex]) != null && trim($Row[$crntColIndex]) != '') {

                        $colVal = mysqli_real_escape_string($GLOBALS['conn'], $Row[$crntColIndex]);

                        if ($colVal != 'NA') {
                            $dbColumnNameList[$crntColIndex] = $Row[$crntColIndex];
                        } else {
                            // if NA for DB col found... then just record it with NA_Prefix to distinguish and discard later
                            $dbColumnNameList[$crntColIndex] = "NA_" + $nondb_col;
                            $nondb_col++;
                        }
                        $crntColIndex++;
                    } // end of while -- reading all columns for first row

                    $j++;
                    $max_cols = $crntColIndex;
                    error_log("db_fields_Array:" . print_r($dbColumnNameList, true));
                    // print_r($dbColumnNameList);
                    continue;
                } // end reading first row db fields

                if ($j < 1) {
                    // ignore for other header rows
                    $j++;
                    continue;
                }
                if ($Row[0] == '') {
                    // if a blank row is encountered stop reading
                    break;
                }
                // read data rows - start

                $emp_code = "";
                $employee_name = "";
                $designation = "";
                $department = "";
                $father_name = "";
                $aadhar_no = "";
                $dbColumnValueList = array();

                $dob = "";
                $doj = "";
                $patient_category = "";
                $employer_contractor_id = "";
                $gender = "";
                $phone_number = "";
                $dept_phone = "";
                $village = "";
                $post = "";
                $ps = "";
                $tehsil = "";
                $district = "";
                $state = "";
                $pin_code = "";
                $is_first_aid = "";
                $blood_group = "";
                $health_risks = "";
                $health_advices = "";
                $ohc_location = "";
                $status = "";
                $primary_contact_person = "";
                $ohc_location = "";
                $status = "";
                $primary_contact_person = "";
                $primary_contact_no = "";
                $secondary_contact_person = "";
                $secondary_contact_no = "";
                $name1 = "";
                $age1 = "";
                $gender1 = "";
                $relation_type = "";
                $typhoid = "";
                $major_injury = "";
                $diabetes = "";
                $tb = "";
                $hbd = "";
                $epilepsy = "";
                $psychiatric = "";
                $heart_deases = "";
                $height = "";
                $weight = "";
                $bp = "";
                $waist = "";
                $is_ohc_staff = "";
                $emp_type = "";
                $sub_emp_type = "";
                $group_comp = "";
                $emp_cadre_name = "";

                $query_part = "";
                error_log("max value" . $max_cols);
                $crntColIndex = 0;
                while ($crntColIndex < $max_cols) {

                    $colVal = mysqli_real_escape_string($GLOBALS['conn'], $Row[$crntColIndex]);

                    $dbColumnValueList[$crntColIndex] = $colVal; // store the column value into value array

                    // custom logic, validations and calculations -- start
                    if (strcasecmp($dbColumnNameList[$crntColIndex], 'employee_id') == 0) {
                        $emp_code = trim($colVal);

                        if ($emp_code != '') $query_part = " emp_code='" . $emp_code . "'";

                        error_log("emp code or id " . $emp_code);
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'full_name') == 0) {
                        $employee_name = removeSpecialCharacters($colVal);

                        if (trim($employee_name) != '') $query_part .= ",patient_name='" . trim($employee_name) . "'";

                        error_log("employee name " . $employee_name);
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'fathers_name') == 0) {
                        $father_name = removeSpecialCharacters($colVal);

                        if ($father_name != '') $query_part .= ",father_name='" . trim($father_name) . "'";
                        error_log("fathers  name " . $employee_name);
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'contribution_level') == 0) {
                        $designation = trim($colVal);
                        error_log("designation  name " . $designation);
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'department') == 0) {
                        $department = trim($colVal);
                        error_log("department  name " . $department);
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'section') == 0) {
                        $section = trim($colVal);
                        error_log("section  name " . $department);
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'date_of_birth') == 0) {
                        $dob = getDateInDbFormat(trim($colVal));
                        error_log("date we getting 1 ".$dob);
                        if ($dob != '') $query_part .= " ,dob = STR_TO_DATE('" . $dob . "','%Y-%m-%d')";
                        $dbColumnValueList[$crntColIndex] = $dob;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'date_of_joining') == 0) {
                        $doj = getDateInDbFormat(trim($colVal));

                        if ($doj != '') $query_part .= " ,doj = STR_TO_DATE('" . $doj . "','%Y-%m-%d')";

                        $dbColumnValueList[$crntColIndex] = $doj;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'sub_employee_type') == 0) {
                        $sub_emp_type = trim($colVal);
                        $dbColumnValueList[$crntColIndex] = $sub_emp_type;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'direct_manager_employee_id') == 0) {
                        $mgr_emp_id = trim($colVal);
                        $dbColumnValueList[$crntColIndex] = $mgr_emp_id;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'employee_type') == 0) {
                        $emp_cadre_name = trim($colVal);
                        error_log("employee cadre ::" . $emp_cadre_name);
                        $dbColumnValueList[$crntColIndex] = $emp_cadre_name;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'gender') == 0) {
                        $gender = removeSpecialCharactersAndSpaces($colVal);

                        if ($gender != '') $query_part .= " ,gender='" . $gender . "'";

                        $dbColumnValueList[$crntColIndex] = $gender;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'office_mobile_no') == 0) {
                        $phone_number = preg_replace('/^\+91/', '', $colVal);
                        $phone_number = removeSpecialCharactersAndSpaces($phone_number);
                        $dbColumnValueList[$crntColIndex] = $phone_number;

                        if ($phone_number != '') $query_part .= " ,primary_phone='" . $phone_number . "'";
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'primary_mobile_number') == 0) {
                        $personal_phone = preg_replace('/^\+91/', '', $colVal);
                        $personal_phone = removeSpecialCharactersAndSpaces($personal_phone);
                        $dbColumnValueList[$crntColIndex] = $personal_phone;

                        if ($personal_phone != '') $query_part .= " ,personal_phone ='" . $personal_phone . "'";
                    } else if(strcasecmp($dbColumnNameList[$crntColIndex], 'phone') == 0) {
                        $dept_phone = preg_replace('/^\+91/', '', $colVal);
                        $dept_phone = removeSpecialCharactersAndSpaces($dept_phone);
                        // $dbColumnValueList[$crntColIndex] = $dept_phone;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'company_email_id') == 0) {
                        $email_id = trim($colVal);
                        $dbColumnValueList[$crntColIndex] = $email_id;

                        if ($email_id != '') $query_part .= " ,offiial_email_id='" . $email_id . "'";
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'group_company') == 0) {
                        $employee_contractor = trim($colVal);
                        $dbColumnValueList[$crntColIndex] = $employee_contractor;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'Aadhar_card') == 0) {
                        $aadhar_no = removeSpecialCharactersAndSpaces($colVal);
                        $dbColumnValueList[$crntColIndex] = $aadhar_no;

                        if ($aadhar_no != '') $query_part .= " , aadhar_no='" . $aadhar_no . "'";
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'dependent_name') == 0) {
                        $name1 = removeSpecialCharacters($colVal);
                        error_log("dependent name: " . $name1);
                        $dbColumnValueList[$crntColIndex] = trim($name1);
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'Dependent_DOB') == 0) {
                        $dob1 = getDateInDbFormat(trim($colVal));
                        error_log("dependent dob: " . $dob1);
                        $dbColumnValueList[$crntColIndex] = $dob1;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'relation_type') == 0) {
                        $relation_type = $colVal;
                        $dbColumnValueList[$crntColIndex] = $relation_type;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'streetAddress') == 0) {
                        $village = $colVal;
                        $dbColumnValueList[$crntColIndex] = $village;
                        if ($village != '') $query_part .= " ,village='" . $village . "'";
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'district') == 0) {
                        $district = trim($colVal);
                        $dbColumnValueList[$crntColIndex] = $district;
                        if ($district != '') $query_part .= " ,district='" . $district . "'";
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'state') == 0) {
                        $state = trim($colVal);
                        if ($state != '') $query_part .= " ,state='" . $state . "'";
                        $dbColumnValueList[$crntColIndex] = $state;
                    } else if (strcasecmp($dbColumnNameList[$crntColIndex], 'pincode') == 0) {

                        $pin_code = removeSpecialCharactersAndSpaces($colVal);

                        if ($pin_code != '') $query_part .= ",pin_code='" . $pin_code . "'";
                        $dbColumnValueList[$crntColIndex] = $pin_code;
                    }


                    $crntColIndex++; // continue reading next column value
                }

                if (!empty($emp_code)) {
                    $id = getTableFieldValue('patient_master', 'id', 'emp_code', "'" . $emp_code . "'");
                    error_log("Found Existing Record with empcode/gatepass no: " . $emp_code . "emp_id:" . $id);
                }

                if ($sub_emp_type == 'White Collar - Regular - Corporate' || $sub_emp_type == 'White Collar - Regular' || $sub_emp_type == 'White Collar - Trainee - Corporate') {
                    $patient_category_id = getFieldFromTable('patient_cat_id', 'patient_category', 'patient_cat_name', 'Employee');
                } else if ($sub_emp_type == 'White Collar - Consultant') {
                    $patient_category_id = getFieldFromTable('patient_cat_id', 'patient_category', 'patient_cat_name', 'Contract Worker');
                } else if(trim(strtolower($emp_cadre_name)) == 'contract_labour'){
                    $patient_category_id = getFieldFromTable('patient_cat_id', 'patient_category', 'patient_cat_name', 'Contract Worker');
                }else{
                    $patient_category_id = '';
                }

                $query_part .= ",patient_cat_id='" . $patient_category_id . "'";

                if (isset($department)) {
                    $dept_id = getTableFieldValue('department', 'dept_id', 'dept_name', "'" . $department . "'");
                    if ($dept_id == null or $dept_id == "") {
                        mysqli_query($GLOBALS['conn'], "insert into department set dept_name='" . $department . "'");
                        $dept_id = getTableFieldValue('department', 'dept_id', 'dept_name', "'" . $department . "'");
                    }
                    if (trim($dept_id) != '') $query_part .= ",dept_id='" . $dept_id . "'";
                }

                if (isset($employee_contractor)) {

                    $employer_contractor_id = getTableFieldValue('employer_contractor', 'id', 'employer_contractor_name', "'" . $employee_contractor . "'");
                    if ($employer_contractor_id == null or $employer_contractor_id == "") {
                        mysqli_query($GLOBALS['conn'], "insert into employer_contractor set employer_contractor_name='" . $employee_contractor . "'");
                        $employer_contractor_id = getTableFieldValue('employer_contractor', 'id', 'employer_contractor_name', "'" . $employee_contractor . "'");
                    }
                    if (trim($employer_contractor_id) != '')  $query_part .= ",employer_contractor_id='" . $employer_contractor_id . "'";
                }

                if (isset($section)) {
                    $section_id = getTableFieldValue('section', 'section_id', 'section_name', "'" . $section . "'");
                    if ($section_id == null or $section_id == "") {
                        mysqli_query($GLOBALS['conn'], "insert into section set section_name='" . $section . "'");
                        $section_id = getTableFieldValue('section', 'section_id', 'section_name', "'" . $section . "'");
                    }

                    if (trim($section_id) != '') $query_part .= ",section_id='" . $section_id . "'";
                }

                if (isset($sub_section)) {
                    $sub_section_id = getTableFieldValue('sub_section', 'sub_section_id', 'sub_section_name', "'" . $sub_section . "'");
                    if ($sub_section_id == null or $sub_section_id == "") {
                        mysqli_query($GLOBALS['conn'], "insert into sub_section set sub_section_name='" . $sub_section . "'");
                        $sub_section_id = getTableFieldValue('sub_section', 'sub_section_id', 'sub_section_name', "'" . $sub_section . "'");
                    }

                    if (trim($sub_section_id) != '') $query_part .= ",sub_section_id='" . $sub_section_id . "'";
                }

                if (isset($designation)) {
                    $designation_id = getTableFieldValue('designation', 'designation_id', 'designation_name', "'" . $designation . "'");
                    if ($designation_id == null or $designation_id == "") {
                        mysqli_query($GLOBALS['conn'], "insert into designation set designation_name='" . $designation . "'");
                        $designation_id = getTableFieldValue('designation', 'designation_id', 'designation_name', "'" . $designation . "'");
                    }
                    if (trim($designation) != '') $query_part .= ",designation_id='" . $designation_id . "'";
                }


                if (isset($sub_emp_type)) {
                    $emp_cadre_id = getTableFieldValue('emp_cadre', 'emp_cadre_id', 'emp_cadre', "'" . $sub_emp_type . "'");

                    if ($emp_cadre_id == null && $emp_cadre_id == "") {
                        mysqli_query($GLOBALS['conn'], "insert into emp_cadre set emp_cadre='" . $sub_emp_type . "'");
                        $emp_cadre_id = getTableFieldValue('emp_cadre', 'emp_cadre_id', 'emp_cadre', "'" . $sub_emp_type . "'");
                    }

                    if (trim($emp_cadre_id) != '') $query_part .= ",emp_cadre ='" . $emp_cadre_id . "'";
                }

                $initquery = "";
                $endquery = "";

                if ($employee_name != null && $employee_name != "") {

                    if (isset($id)) {
                        $initquery = "update patient_master set  ";
                        $endquery = " where id = '" . $id . "'   ";
                        $update_counter++;
                    } else {
                        $initquery = "insert into patient_master set  ";
                        $endquery = "";
                        $insert_counter++;
                    }
                    $query_patient = $initquery . " $query_part " . $endquery;

                    error_log("new query for employee.." . $query_patient);

                    if (!$result = @mysqli_query($GLOBALS['conn'], $query_patient)) {
                        // $response_array['responseText']=mysqli_error($conn);
                        copyFileWithTimestamp($sourceFolder, $destinationFolder_failure, basename($file));

                        // mysqli_query($GLOBALS['conn'], "insert into batch_status set run_date = '$curr_date', insert_count = '$insert_counter', update_count = '$update_counter', status = 'Failed'");

                        error_log("Exception:" . mysqli_error($GLOBALS['conn']));
                        error_log("Failed to Execute Patient Insert/Update Query::: " . $query_patient);
                        rollback();
                        exit(mysqli_error($GLOBALS['conn']));
                    } else {
                        error_log("NO. OF ROWS EXECUTED::" . $j++);
                    }
                }
                if (empty($id)) {
                    $id = @mysqli_insert_id($GLOBALS['conn']);
                    error_log("Newly Added employees Id:" . $id);
                }


                // $result = mysqli_query($conn, $query_patient);
                $My_Family1 = "";
                $id = getTableFieldValue('patient_master', 'id', 'emp_code', "'" . $emp_code . "'");
                //mysqli_query($conn," delete from emp_family_members where emp_id='" . $id . "'" );
                if (trim($name1) != null && trim($name1) != "" && strtolower(trim($name1)) !='na') {

                    $initqueryd = "";
                    $endqueryd = "";

                    $is_exists = "select id from patient_master where primary_patient_id='" . $id . "' and patient_name= '" . $name1 . "'";
                    $is_result = mysqli_query($GLOBALS['conn'], $is_exists);

                    $is_row = mysqli_fetch_assoc($is_result);

                    $dependent_primary_id = $is_row['id'];

                    if (isset($dependent_primary_id)) {
                        $initqueryd = "update patient_master set  ";
                        $endqueryd = " where id = '" . $dependent_primary_id . "'   ";
                        $update_counter++;
                    } else {
                        $initqueryd = "insert into patient_master set  ";
                        $endqueryd = "";
                        $insert_counter++;
                    }

                   
                    $pat_cat_id = "";

                    $pat_cat_id = getFieldFromTable('patient_cat_id', 'patient_category', 'patient_cat_name', 'Non Employee');

                    $My_Family1 = $initqueryd . " patient_name='" . $name1 . "',dob = STR_TO_DATE('" . $dob1 . "','%Y-%m-%d'),gender='" . $gender1 . "',relation_type='" . $relation_type . "',personal_phone='".$dept_phone."',patient_cat_id='" . $pat_cat_id . "',primary_patient_id='" . $id . "' " . $endqueryd;
                    error_log("family meme1" . $My_Family1);
                    if (!$result1 = @mysqli_query($GLOBALS['conn'], $My_Family1)) {
                        copyFileWithTimestamp($sourceFolder, $destinationFolder_failure, basename($file));
                        error_log("Exception:" . mysqli_error($GLOBALS['conn']));
                        error_log("Failed to Execute Family Member Insert/Update Query::: " . $My_Family1);
                        rollback();
                        exit(mysqli_error($GLOBALS['conn']));
                    }
                }
                if ($result) {
                    $response_array['status'] = 'success';
                    // $message = "Excel Data Imported Into the Database";
                } else {
                    $response_array['status'] = 'error';
                    // $messaged = "Problem In Importing Excel Data";
                }
                // }
            }
        }

        copyFileWithTimestamp($sourceFolder, $destinationFolder_success, basename($file));
    }

    commit();
    // mysqli_query($GLOBALS['conn'], "insert into batch_status set run_date = '$curr_date', insert_count = '$insert_counter', update_count = '$update_counter', status = 'Completed'");
}


$folderPath = 'upload_emp_script/';
processData($folderPath);