<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data)) {
        //Get Clinic Detail
        $sql = "SELECT a.clinic_id, b.clinic_name
                  FROM ytycustom.medical_user_account a, 
                       ytycustom.medical_clinic_master b
                 WHERE a.clinic_id = b.id
                   AND a.user_id = :user_id";

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':user_id', $data->USER_ID);
        $r = oci_execute($sqlarr);
        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
        }
        oci_fetch_all($sqlarr, $rows_clinic, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        if (empty($data->VISIT_ID)) {
            $sql = "INSERT INTO ytycustom.medical_visit
                                (VISIT_DATE, EMP_ID, EMPLOYEE_NAME, CLINIC_ID, MC_FLAG, 
                                 LDP_FROM_DATE, LDP_TO_DATE, REASON, MEDICATION, CHARGES, REMARKS, 
                                 CLINIC_NAME, CREATED_BY, CREATED_DATE)
                         VALUES (TO_DATE(:VISIT_DATE,'YYYY-MM-DD'), :EMP_ID, :EMPLOYEE_NAME, :CLINIC_ID, :MC_FLAG, 
                                 TO_DATE(:LDP_FROM_DATE,'YYYY-MM-DD'), TO_DATE(:LDP_TO_DATE,'YYYY-MM-DD'), :REASON, 
                                 :MEDICATION, :CHARGES, :REMARKS, :CLINIC_NAME, :CREATED_BY, SYSDATE)
                      RETURNING VISIT_ID INTO :NEWID";
    
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':VISIT_DATE', $data->VISIT_DATE);
            oci_bind_by_name($sqlarr, ':EMP_ID', $data->EMP_ID);
            oci_bind_by_name($sqlarr, ':EMPLOYEE_NAME', $data->EMPLOYEE_NAME);
            oci_bind_by_name($sqlarr, ':CLINIC_ID', $rows_clinic[0]["CLINIC_ID"]);
            oci_bind_by_name($sqlarr, ':CLINIC_NAME', $rows_clinic[0]["CLINIC_NAME"]);
            oci_bind_by_name($sqlarr, ':MC_FLAG', $data->MC_FLAG);
            oci_bind_by_name($sqlarr, ':LDP_FROM_DATE', $data->LDP_FROM_DATE);
            oci_bind_by_name($sqlarr, ':LDP_TO_DATE', $data->LDP_TO_DATE);
            oci_bind_by_name($sqlarr, ':REASON', $data->REASON);
            oci_bind_by_name($sqlarr, ':MEDICATION', $data->MEDICATION);
            oci_bind_by_name($sqlarr, ':CHARGES', $data->CHARGES);
            oci_bind_by_name($sqlarr, ':REMARKS', $data->REMARKS);
            oci_bind_by_name($sqlarr, ':CREATED_BY', $data->USER_ID);
            oci_bind_by_name($sqlarr, ':NEWID', $newid, 20);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
    
            if (!$r) {
                $e = oci_error($sqlarr);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }

        } else {

            $sql = "UPDATE ytycustom.medical_visit
                       SET VISIT_DATE = TO_DATE(:VISIT_DATE,'YYYY-MM-DD'),
                           REASON = :REASON,
                           MEDICATION = :MEDICATION,
                           CHARGES = :CHARGES,
                           REMARKS = :REMARKS,
                           MODIFIED_BY = :MODIFIED_BY,
                           MODIFIED_DATE = SYSDATE
                     WHERE VISIT_ID = :VISIT_ID";
            
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':VISIT_DATE', $data->VISIT_DATE);
            oci_bind_by_name($sqlarr, ':REASON', $data->REASON);
            oci_bind_by_name($sqlarr, ':MEDICATION', $data->MEDICATION);
            oci_bind_by_name($sqlarr, ':CHARGES', $data->CHARGES);
            oci_bind_by_name($sqlarr, ':REMARKS', $data->REMARKS);
            oci_bind_by_name($sqlarr, ':MODIFIED_BY', $data->USER_ID);
            oci_bind_by_name($sqlarr, ':VISIT_ID', $data->VISIT_ID);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
    
            if (!$r) {
                $e = oci_error($sqlarr);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }

        }

        //Leave Record
        if (empty($data->VISIT_ID) && $data->MC_FLAG == 'Yes') {
            $sql = "SELECT 'W' || to_char(SYSDATE, 'YY') ||
                           LPAD(NVL(MAX(TO_NUMBER(SUBSTR(REQ_ID, 4))), 0) + 1, 6, 0) req_id
                      FROM YTYCUSTOM.EMP_LEAVE_ENTRY_HD H
                     WHERE SUBSTR(REQ_ID, 2, 2) = to_char(SYSDATE, 'YY')
                  ORDER BY REQ_ID DESC";

            $sqlarr = oci_parse($dbcon, $sql);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            while ($row = oci_fetch_array($sqlarr)) {
                $reqid = $row['REQ_ID'];
            }

            //Check if balance still available
            $sql = " SELECT a.yr, a.emp_no, a.leave_type, nvl(a.leave_ent,0) leave_ent, nvl(b.days_taken,0) days_taken, 
                            (nvl(a.leave_ent,0)-nvl(b.days_taken,0)) leave_balance,
                            nvl(c.days_taken,0) unpaid_leave
                       FROM production.employee_master em,
                            ytycustom.expat_leave_balance_master a,
                            (SELECT to_char(ldp_from_date, 'YYYY') yr, emp_no,
                                    SUM((ldp_to_date - ldp_from_date) + 1) days_taken
                            FROM ytycustom.emp_leave_entry_hd b
                            WHERE ldp_code = '02SL'
                                AND nvl(cancel_flag, 'N') = 'N'
                                AND nvl(status, '-') = 'APPROVED'
                                AND emp_no = :emp_id
                                AND to_char(ldp_from_date, 'YYYY') = to_char(SYSDATE, 'YYYY')
                            GROUP BY to_char(ldp_from_date, 'YYYY'), emp_no) b,
                            (SELECT to_char(ldp_from_date, 'YYYY') yr, emp_no,
                                    SUM(case when ldp_code = '14UD' then 0.5 else (ldp_to_date - ldp_from_date) + 1 end) days_taken
                            FROM ytycustom.emp_leave_entry_hd b
                            WHERE ldp_code IN ('07UP','14UD')
                                AND nvl(cancel_flag, 'N') = 'N'
                                AND nvl(status, '-') = 'APPROVED'
                                AND emp_no = :emp_id
                                AND to_char(ldp_from_date, 'YYYY') = to_char(SYSDATE, 'YYYY')
                            GROUP BY to_char(ldp_from_date, 'YYYY'), emp_no) c  
                    WHERE em.emp_id = a.emp_no(+) 
                        AND a.yr = b.yr(+)
                        AND a.emp_no = b.emp_no(+)
                        AND a.yr = c.yr(+)
                        AND a.emp_no = c.emp_no(+)
                        AND a.yr(+) = to_char(SYSDATE, 'YYYY')
                        AND em.emp_id = :emp_id
                        AND a.leave_type(+) = '02SL'
                        AND nvl(em.emp_status, 'N') = 'N'
                        --AND nvl(em.virtual_flag, 'N') = 'N'
                        ";  

            $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);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }
            oci_fetch_all($sqlarr, $rows_balance, null, null, OCI_FETCHSTATEMENT_BY_ROW);

            if ($rows_balance[0]["LEAVE_BALANCE"] > 0) {
                $leave_type = "02SL";
                
            } else {
                $leave_type = "07UP";
            }

            // $sql = "INSERT INTO ytycustom.emp_leave_entry_hd
            //                     (req_id, req_date, emp_no, tra_type, ldp_from_date, ldp_to_date, ldp_code, 
            //                      applicant_status, applicant_date, next_level, comm_detail, entered_by, entered_dt, 
            //                      no_of_email, status, last_processed_by, status_date, re_join_date, effective_days)
            //               VALUES (:req_id, to_date(:req_date,'YYYY-MM-DD'), :emp_no, 'REGULAR LTA', to_date(:ldp_from_date,'YYYY-MM-DD'), 
            //                      to_date(:ldp_to_date,'YYYY-MM-DD'), :ldp_code, 'SUBMITTED', to_date(:applicant_date,'YYYY-MM-DD'), 
            //                      '1', 'Submitted on '||:req_date||' by e-Medical Portal', :entered_by, SYSDATE, 0, 'APPROVED', 'SYSTEM', 
            //                      SYSDATE, to_date(:ldp_from_date,'YYYY-MM-DD')+1, (to_date(:ldp_to_date,'YYYY-MM-DD') - to_date(:ldp_from_date,'YYYY-MM-DD') + 1))";
            
            // $sqlarr = oci_parse($dbcon , $sql);
            // oci_bind_by_name($sqlarr, ':req_id', $reqid);
            // oci_bind_by_name($sqlarr, ':req_date', $data->VISIT_DATE);
            // oci_bind_by_name($sqlarr, ':emp_no', $data->EMP_ID);
            // oci_bind_by_name($sqlarr, ':ldp_from_date', $data->LDP_FROM_DATE);
            // oci_bind_by_name($sqlarr, ':ldp_to_date', $data->LDP_TO_DATE);
            // oci_bind_by_name($sqlarr, ':ldp_code', $leave_type);
            // oci_bind_by_name($sqlarr, ':entered_by', $data->USER_ID);
            // oci_bind_by_name($sqlarr, ':applicant_date', $data->VISIT_DATE);
            // $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

            // if (!$r) {
            //     $e = oci_error($sqlarr);
            //     $errorMsg = json_encode($e['message']);
            //     goto finalStep;
            // }

            // $sql = "UPDATE ytycustom.medical_visit
            //            SET req_id = :req_id
            //          WHERE visit_id = :id";
            // $sqlarr = oci_parse($dbcon , $sql);
            // oci_bind_by_name($sqlarr, ':req_id', $reqid);
            // oci_bind_by_name($sqlarr, ':id', $newid);
            // $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

            // if (!$r) {
            //     $e = oci_error($sqlarr);
            //     $errorMsg = json_encode($e['message']);
            //     goto finalStep;
            // }
            
        }
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        oci_commit($dbcon);
        $result[] = [
            "message" => "Success",
            "id" => $newid
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => $errorMsg,
            "id" => ""
        ];
    }
    print json_encode($result);
?>