<?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, 
                            to_char(to_date(:leave_from,'YYYY-MM-DD'), 'DD-Mon-YYYY') fromdate,
                            to_char(to_date(:leave_to,'YYYY-MM-DD'), 'DD-Mon-YYYY') todate
                       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);
            oci_bind_by_name($sqlarr, ':leave_from', $data->LDP_FROM_DATE);
            oci_bind_by_name($sqlarr, ':leave_to', $data->LDP_TO_DATE);
            $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);

            //Effective Days
            $url = 'https://ytygroup.app/LeavePortal/api/effdays.php?fromDate='.$rows_balance[0]["FROMDATE"].'&toDate='.$rows_balance[0]["TODATE"].'&empno='.$data->EMP_ID.'&leave_type=02SL';
            $content = file_get_contents($url);
            $effdata = json_decode($content);

            if ($rows_balance[0]["LEAVE_BALANCE"] >= $effdata->data[0]->ctr) {
                $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, :effective_days)";
            
            $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);
            oci_bind_by_name($sqlarr, ':effective_days', $effdata->data[0]->ctr);
            $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;
            }

            //Email
            //Select all approvers
            $sql = "SELECT b.email_id apprv_email
                      FROM YTYCUSTOM.USER_REGISTRY_APPROVAL_LINES a,
                           production.employee_master b
                     WHERE a.approver_emp_id = b.emp_id
                       AND nvl(b.emp_status, 'N') = 'N'
                       AND a.emp_id = :emp_id
                       AND a.doc_type_code = '1001'";
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':emp_id', $data->EMP_ID);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            oci_fetch_all($sqlarr, $rows_apprv, null, null, OCI_FETCHSTATEMENT_BY_ROW);

            if (count($rows_apprv) > 0) {
                $mailApprv = implode(',', array_map(function ($row) {
                                return $row['APPRV_EMAIL'];
                            }, $rows_apprv));

                //Notification
                $mailContent = '<p>Dear Immediate Superior,</p>
                                <p>We would like to inform you that the following employee have taken sick leave, with the following details.</p>
                                <table border="0">
                                <tr>
                                    <td style="font-weight:bold;">Employee ID</td>
                                    <td style="font-weight:bold;"> : </td>
                                    <td>'.$data->EMP_ID.'</td>
                                </tr>
                                <tr>
                                    <td style="font-weight:bold;">Employee Name</td>
                                    <td style="font-weight:bold;"> : </td>
                                    <td>'.$data->EMPLOYEE_NAME.'</td>
                                </tr>
                                <tr>
                                    <td style="font-weight:bold;">Duration</td>
                                    <td style="font-weight:bold;"> : </td>
                                    <td>From '.$data->LDP_FROM_DATE.' until '.$data->LDP_TO_DATE.'</td>
                                </tr>
                                <tr>
                                    <td style="font-weight:bold;">Issued By</td>
                                    <td style="font-weight:bold;"> : </td>
                                    <td>'.$rows_clinic[0]['CLINIC_NAME'].'</td>
                                </tr>
                                </table>
                                <p>Thank you.</p>
                                <p style="font-size:11px;">Note: This message is auto-generated so please do not respond to this email.</p>';

                $mailQuery = "BEGIN
                                ytycustom.set_notification.generate(ytycustom.set_notification.get_id,
                                                                    'EMAIL',
                                                                    '".$mailApprv."',
                                                                    NULL,
                                                                    NULL,
                                                                    'YTY E-Medical Notification',
                                                                    '".$mailContent."',
                                                                    'HTML',
                                                                    SYSDATE,
                                                                    'E-Medical',
                                                                    NULL);
                            END;";
                $sqlarr = oci_parse($dbcon , $mailQuery);
                $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            }
                
            //Low MC Notification
            if ($leave_type == "02SL" && ($rows_balance[0]["LEAVE_BALANCE"] - $effdata->data[0]->ctr) <= 3) {
                if (($rows_balance[0]["LEAVE_BALANCE"] - $effdata->data[0]->ctr) == 0) {
                    $mailMC = '<p>Dear Immediate Superior,</p>
                                <p>We would like to inform you that the following employee have utilized all of their entitled sick leave.</p>
                                <table border="0">
                                <tr>
                                    <td style="font-weight:bold;">Employee ID</td>
                                    <td style="font-weight:bold;"> : </td>
                                    <td>'.$data->EMP_ID.'</td>
                                </tr>
                                <tr>
                                    <td style="font-weight:bold;">Employee Name</td>
                                    <td style="font-weight:bold;"> : </td>
                                    <td>'.$data->EMPLOYEE_NAME.'</td>
                                </tr>
                                </table>
                                <br />
                                <p>Kindly advise the employee accordingly.<br />
                                If further support is required:</p> 
                                <ol>
                                    <li>Kindly refer the employee to your superior</li>
                                    <li>If necessary, refer to HR department</li>
                                </ol>
                                <p>HR will ensure the employee`s wellbeing by placing them in our "One Panel Clinic Program".<br />
                                His/Her health condition will be monitored closely by selected doctors.</p>
                                
                                <p style="font-size:11px;">Note: This message is auto-generated so please do not respond to this email.</p>';
                
                } else {

                    $mailMC = '<p>Dear Immediate Superior,</p>
                                <p>We would like to inform you that the following employee have utilized nearly 80% of their entitled sick leave.</p>
                                <table border="0">
                                <tr>
                                    <td style="font-weight:bold;">Employee ID</td>
                                    <td style="font-weight:bold;"> : </td>
                                    <td>'.$data->EMP_ID.'</td>
                                </tr>
                                <tr>
                                    <td style="font-weight:bold;">Employee Name</td>
                                    <td style="font-weight:bold;"> : </td>
                                    <td>'.$data->EMPLOYEE_NAME.'</td>
                                </tr>
                                </table>
                                <br />
                                <p>Kindly advise the employee accordingly.<br />
                                If further support is required:</p> 
                                <ol>
                                    <li>Kindly refer the employee to your superior</li>
                                    <li>If necessary, refer to HR department</li>
                                </ol>
                                <p>HR will ensure the employee`s wellbeing by placing them in our "One Panel Clinic Program".<br />
                                His/Her health condition will be monitored closely by selected doctors.</p>
                                
                                <p style="font-size:11px;">Note: This message is auto-generated so please do not respond to this email.</p>';
                }

                $mailSubject="YTY Workflow E-People Management Tracking";
                $strMCEmailQuery = "BEGIN
                    ytycustom.set_notification.generate(ytycustom.set_notification.get_id,
                    'EMAIL',
                    '".$rws['EMAIL_ID']."',
                    'raguraman.kanayesan@ytygroup.com.my,muhamadnorsyafiq.omar@ytygroup.com.my,zakwan.sohari@ytygroup.com.my',
                    NULL,
                    '".$mailSubject."',
                    '".$mailMC."',
                    'HTML',
                    SYSDATE,
                    'MC NOTICE',
                    Null
                    );
                    END;";
                
                $MCEmailQuery = oci_parse($dbcon, $strMCEmailQuery);
                $r = oci_execute($MCEmailQuery, OCI_NO_AUTO_COMMIT);
            }

        }
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        oci_commit($dbcon);
        $result[] = [
            "message" => "Success",
            "id" => $newid
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => $errorMsg,
            "id" => ""
        ];
    }
    print json_encode($result);
?>