<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!in_array('read', $authenticatedUser['permissions'])) {
        $resMsg = "Forbidden: You do not have permission to read data.";
        goto finalStep;

    } else {

        //Department Master
        $sql = "SELECT COUNT(1) CTR_DEPT
                  FROM production.yty_department
                 WHERE DEPARTMENT_CODE = :DEPARTMENT_CODE";
        
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':DEPARTMENT_CODE', $data->DEPARTMENT_CODE);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
        if (!$r) {
            $e = oci_error($sqlarr);
            $resMsg = json_encode($e['message']);
            goto finalStep;
        }

        while ($rw = oci_fetch_array($sqlarr)) {
            $dept_exists = $rw['CTR_DEPT'];
        }

        if ($dept_exists == 0) {
            //Get Max ID
            $sql = "SELECT NVL(MAX(department_no), 0) + 1 MAXID,
                           'Y' || LPAD(TO_CHAR(NVL(MAX(department_no), 0) + 1), 3, '0') newcode
                      FROM production.yty_department";

            $sqlarr = oci_parse($dbcon , $sql);
            $r = oci_execute($sqlarr);
            if (!$r) {
                $e = oci_error($sqlarr);
                $resMsg = json_encode($e['message']);
            }
            oci_fetch_all($sqlarr, $rows_max, null, null, OCI_FETCHSTATEMENT_BY_ROW);

            //$deptCode = $rows_max[0]["NEWCODE"];

            $sql = "INSERT INTO production.yty_department
                                (DEPARTMENT_NO, DEPARTMENT_CODE, DEPARTMENT_NAME, CREATION_DATE, CREATED_BY)
                         VALUES ('".$rows_max[0]["MAXID"]."', :DEPARTMENT_CODE, :DEPARTMENT_NAME, SYSDATE, 'API')";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':DEPARTMENT_CODE', $data->DEPARTMENT_CODE);
            oci_bind_by_name($sqlarr, ':DEPARTMENT_NAME', $data->DEPARTMENT);
            $r = oci_execute($sqlarr);
            if (!$r) {
                $e = oci_error($sqlarr);
                $resMsg = json_encode($e['message']);

                sendJsonResponse(['message' => 'Success',
                                  'data' => $resMsg], 500);
                die();
            }
        
        } 
        // else {

        //     $sql = "UPDATE production.yty_department
        //                SET DEPARTMENT_NAME = :DEPARTMENT_NAME, 
        //                    LAST_UPDATE_DATE = SYSDATE,
        //                    LAST_UPDATED_BY = 'API'
        //              WHERE department_code = :DEPARTMENT_CODE";

        //     $sqlarr = oci_parse($dbcon , $sql);
        //     oci_bind_by_name($sqlarr, ':DEPARTMENT_CODE', $data->DEPARTMENT_CODE);
        //     oci_bind_by_name($sqlarr, ':DEPARTMENT_NAME', $data->DEPARTMENT_NAME);
        //     $r = oci_execute($sqlarr);
        //     if (!$r) {
        //         $e = oci_error($sqlarr);
        //         $resMsg = json_encode($e['message']);

        //         sendJsonResponse(['message' => 'Success',
        //                           'data' => $resMsg], 500);
        //         die();
        //     }
        // } 

        //Designation Master
        $sql = "SELECT COUNT(1) CTR_DESIGN
                  FROM employee_designation_master
                 WHERE DESIGNATION_CODE = :DESIGNATION_CODE";
        
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':DESIGNATION_CODE', $data->DESIGNATION_CODE);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
        if (!$r) {
            $e = oci_error($sqlarr);
            $resMsg = json_encode($e['message']);
            goto finalStep;
        }

        while ($rw = oci_fetch_array($sqlarr)) {
            $design_exists = $rw['CTR_DESIGN'];
        }

        if ($design_exists == 0) { 

            //Get the code
            $sql = "SELECT MAX(REGEXP_REPLACE(designation_code, '[0-9]', '')) design_code,
                           NVL(MAX(TO_NUMBER(REGEXP_SUBSTR(designation_code, '\d+'))),
                                0) + 1 design_num,
                           MAX(designation_level) designation_level
                      FROM employee_designation_master
                     WHERE designation_name = :DESIGNATION_NAME";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':DESIGNATION_CODE', $data->DESIGNATION_CODE);
            oci_execute($sqlarr);
            while ($row = oci_fetch_array($sqlarr)) {
                $designcode = $row['DESIGN_CODE'];
                $designnum = $row['DESIGN_NUM'];
                $designlevel = $row['DESIGNATION_LEVEL'];
            }

            $fulldesignationcode = (!empty($designcode) ? $designcode : 'CUS').$designnum;
            $designCode = $fulldesignationcode;

            $sql = "INSERT INTO production.employee_designation_master 
                                (DESIGNATION_CODE, DESIGNATION_LEVEL, DESIGNATION_DESC, DESIGNATION_NAME, ACTIVE_FLAG, INPUT_SOURCE, LAST_UPDATED)
                         VALUES (:DESIGNATION_CODE, :DESIGNATION_LEVEL, :DESIGNATION_DESC, :DESIGNATION_NAME, 'Y', 'API', SYSDATE)";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':DESIGNATION_CODE', $data->DESIGNATION_CODE);
            oci_bind_by_name($sqlarr, ':DESIGNATION_LEVEL', $data->DESIGNATION_LEVEL);
            oci_bind_by_name($sqlarr, ':DESIGNATION_DESC', $data->DESIGNATION);
            oci_bind_by_name($sqlarr, ':DESIGNATION_NAME', $data->DESIGNATION_NAME);
            $r = oci_execute($sqlarr);
            if (!$r) {
                $e = oci_error($sqlarr);
                $resMsg = json_encode($e['message']);

                sendJsonResponse(['message' => 'Success',
                                  'data' => $resMsg], 500);
                die();
            }

        } 
        // else {

        //     $sql = "UPDATE production.employee_designation_master 
        //                SET DESIGNATION_DESC = :DESIGNATION_DESC, 
        //                    INPUT_SOURCE = 'API', 
        //                    LAST_UPDATED = SYSDATE
        //              WHERE designation_code = :DESIGNATION_CODE";

        //     $sqlarr = oci_parse($dbcon , $sql);
        //     oci_bind_by_name($sqlarr, ':DESIGNATION_CODE', $data->DESIGNATION_CODE);
        //     oci_bind_by_name($sqlarr, ':DESIGNATION_DESC', $data->DESIGNATION_DESC);
        //     $r = oci_execute($sqlarr);
        //     if (!$r) {
        //         $e = oci_error($sqlarr);
        //         $resMsg = json_encode($e['message']);

        //         sendJsonResponse(['message' => 'Success',
        //                           'data' => $resMsg], 500);
        //         die();
        //     }
        // }

        //Employee Master
        //Check if exist
        $sql = "SELECT COUNT(1) CTR
                FROM production.employee_master
               WHERE emp_id = :EMP_ID";

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':EMP_ID', $data->EMP_ID);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
        if (!$r) {
            $e = oci_error($sqlarr);
            $resMsg = json_encode($e['message']);
            goto finalStep;
        }
        oci_fetch_all($sqlarr, $rows_count, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        $fullemp = str_pad($data->ORGID, 3, "0", STR_PAD_LEFT).$data->EMP_ID;

        if ($rows_count[0]["CTR"] == 0) {
            $sql = "INSERT INTO production.employee_master 
                                (EMP_ID, OLD_EMP_ID, NEW_EMP_ID, DATE_JOIN, EMPLOYEE_NAME, EMAIL_ID,
                                 GENDER, DOB, NATIONALITY, RACE, QUALIFICATION, LOCATION,
                                 DESIGNATION_CODE, DESIGNATION, GRADING, DEPARTMENT_CODE, DEPARTMENT,
                                 SUB_DEPARTMENT, SUBDEPARTMENT_CODE,
                                 ORGID, EMP_MOBILE_NO, EMP_PAY_DIVISION, PASS_NUM, EMP_LEAVE_FLAG,
                                 EMP_LEVEL, BLOCK, BANK_ACCOUNT_NO, BANK_NAME, CLAIM_GROUP,
                                 EMP_STATUS, EMP_END_DATE, REPORTING_1, REPORTING_2, INPUT_SOURCE, LAST_UPDATED_BY, LAST_UPDATED_DATE)
                         VALUES (:EMP_ID, :OLD_EMP_ID, :NEW_EMP_ID, TO_DATE(:DATE_JOIN,'YYYY-MM-DD'), :EMPLOYEE_NAME, :EMAIL_ID,
                                 :GENDER, TO_DATE(:DOB,'YYYY-MM-DD'), :NATIONALITY, :RACE, :QUALIFICATION, :LOCATION,
                                 :DESIGNATION_CODE, :DESIGNATION, :GRADING, :DEPARTMENT_CODE, :DEPARTMENT,
                                 :SUB_DEPARTMENT, :SUBDEPARTMENT_CODE,
                                 :ORGID, :EMP_MOBILE_NO, :EMP_PAY_DIVISION, :PASS_NUM, NVL(:EMP_LEAVE_FLAG,'N'),
                                 to_number(trim(replace(:EMP_LEVEL,'Level',''))), :BLOCK, :BANK_ACCOUNT_NO, :BANK_NAME, :CLAIM_GROUP,
                                 :EMP_STATUS, TO_DATE(:EMP_END_DATE,'YYYY-MM-DD'), :REPORTING_1, :REPORTING_2, 'API', 'API', SYSDATE)";

        } else {
            $sql = "UPDATE production.employee_master 
                       SET NEW_EMP_ID = :NEW_EMP_ID, 
                           DATE_JOIN = TO_DATE(:DATE_JOIN,'YYYY-MM-DD'), 
                           EMPLOYEE_NAME = :EMPLOYEE_NAME, 
                           EMAIL_ID = :EMAIL_ID,
                           GENDER = :GENDER, 
                           DOB = TO_DATE(:DOB,'YYYY-MM-DD'), 
                           NATIONALITY = :NATIONALITY, 
                           RACE = :RACE, 
                           QUALIFICATION = :QUALIFICATION, 
                           LOCATION = :LOCATION,
                           DESIGNATION_CODE = :DESIGNATION_CODE, 
                           DESIGNATION = :DESIGNATION, 
                           GRADING = :GRADING, 
                           DEPARTMENT_CODE = :DEPARTMENT_CODE, 
                           DEPARTMENT = :DEPARTMENT,
                           SUB_DEPARTMENT = :SUB_DEPARTMENT, 
                           SUBDEPARTMENT_CODE = :SUBDEPARTMENT_CODE,
                           ORGID = :ORGID, 
                           EMP_MOBILE_NO = :EMP_MOBILE_NO, 
                           EMP_PAY_DIVISION = :EMP_PAY_DIVISION, 
                           PASS_NUM = :PASS_NUM, 
                           EMP_LEAVE_FLAG = :EMP_LEAVE_FLAG,
                           EMP_LEVEL = to_number(trim(replace(:EMP_LEVEL,'Level',''))), 
                           BLOCK = :BLOCK, 
                           BANK_ACCOUNT_NO = :BANK_ACCOUNT_NO, 
                           BANK_NAME = :BANK_NAME,
                           CLAIM_GROUP = :CLAIM_GROUP,
                           EMP_STATUS = :EMP_STATUS, 
                           EMP_END_DATE = TO_DATE(:EMP_END_DATE,'YYYY-MM-DD'),
                           REPORTING_1 = :REPORTING_1,
                           REPORTING_2 = :REPORTING_2,
                           INPUT_SOURCE = 'API',
                           LAST_UPDATED_BY = 'API', 
                           LAST_UPDATED_DATE = SYSDATE
                     WHERE EMP_ID = :EMP_ID";
        }

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':EMP_ID', $fullemp);
        oci_bind_by_name($sqlarr, ':OLD_EMP_ID', $data->OLD_EMP_ID);
        oci_bind_by_name($sqlarr, ':NEW_EMP_ID', $data->NEW_EMP_ID);
        oci_bind_by_name($sqlarr, ':DATE_JOIN', $data->DATE_JOIN);
        oci_bind_by_name($sqlarr, ':EMPLOYEE_NAME', $data->EMPLOYEE_NAME);
        oci_bind_by_name($sqlarr, ':EMAIL_ID', $data->EMAIL_ID);
        oci_bind_by_name($sqlarr, ':GENDER', $data->GENDER);
        oci_bind_by_name($sqlarr, ':DOB', $data->DOB);
        oci_bind_by_name($sqlarr, ':NATIONALITY', $data->NATIONALITY);
        oci_bind_by_name($sqlarr, ':RACE', $data->RACE);
        oci_bind_by_name($sqlarr, ':QUALIFICATION', $data->QUALIFICATION);
        oci_bind_by_name($sqlarr, ':LOCATION', $data->LOCATION);
        oci_bind_by_name($sqlarr, ':DESIGNATION_CODE', $data->DESIGNATION_CODE);
        oci_bind_by_name($sqlarr, ':DESIGNATION', $data->DESIGNATION);
        oci_bind_by_name($sqlarr, ':GRADING', $data->GRADING);
        oci_bind_by_name($sqlarr, ':DEPARTMENT_CODE', $data->DEPARTMENT_CODE);
        oci_bind_by_name($sqlarr, ':DEPARTMENT', $data->DEPARTMENT);
        oci_bind_by_name($sqlarr, ':SUB_DEPARTMENT', $data->SUB_DEPARTMENT);
        oci_bind_by_name($sqlarr, ':SUBDEPARTMENT_CODE', $data->SUBDEPARTMENT_CODE);
        oci_bind_by_name($sqlarr, ':ORGID', $data->ORGID);
        oci_bind_by_name($sqlarr, ':EMP_MOBILE_NO', $data->EMP_MOBILE_NO);
        oci_bind_by_name($sqlarr, ':EMP_PAY_DIVISION', $data->EMP_PAY_DIVISION);
        oci_bind_by_name($sqlarr, ':PASS_NUM', $data->PASS_NUM);
        oci_bind_by_name($sqlarr, ':EMP_LEAVE_FLAG', $data->EMP_LEAVE_FLAG);
        oci_bind_by_name($sqlarr, ':EMP_LEVEL', $data->EMP_LEVEL);
        oci_bind_by_name($sqlarr, ':BLOCK', $data->BLOCK);
        oci_bind_by_name($sqlarr, ':BANK_ACCOUNT_NO', $data->BANK_ACCOUNT_NO);
        oci_bind_by_name($sqlarr, ':BANK_NAME', $data->BANK_NAME);
        oci_bind_by_name($sqlarr, ':CLAIM_GROUP', $data->CLAIM_GROUP);
        oci_bind_by_name($sqlarr, ':EMP_STATUS', $data->EMP_STATUS);
        oci_bind_by_name($sqlarr, ':EMP_END_DATE', $data->EMP_END_DATE);
        oci_bind_by_name($sqlarr, ':REPORTING_1', $data->REPORTING_MGR_1);
        oci_bind_by_name($sqlarr, ':REPORTING_2', $data->REPORTING_MGR_2);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
        if (!$r) {
            $e = oci_error($sqlarr);
            $resMsg = json_encode($e['message']);
            goto finalStep;
        }

        //API Log
        $log_sql = "INSERT INTO hrms_voyon_log (identity_mark, transaction_type, payload, execute_date)
                         VALUES (:IDENTITY_MARK, 'EMPLOYEE_MASTER', :PAYLOAD, sysdate)";

        $log_stmt = oci_parse($dbcon, $log_sql);

        oci_bind_by_name($log_stmt, ':IDENTITY_MARK', $fullemp);
        oci_bind_by_name($log_stmt, ':PAYLOAD', $json);

        $log_r = oci_execute($log_stmt, OCI_NO_AUTO_COMMIT);

        if (!$log_r) {
            $e = oci_error($log_stmt);
            $resMsg = json_encode($e['message']);
            goto finalStep;
        }

    }

finalStep:
    if ($resMsg == "") {
        oci_commit($dbcon);
        sendJsonResponse(['message' => 'Success',
                          'data' => '']);
    } else {
        oci_rollback($dbcon);
        sendJsonResponse(['message' => $resMsg,
                          'data' => null], 403);
    }
?>