<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

    if (!empty($data)) {
        $default_loc = '../../webdocs/ClaimFiles/';

        //Emp Details
        $sql = "SELECT emp_id, employee_name, department, designation, orgid, claim_group
                  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_fetch_all($sqlarr, $rows_emp, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        //Medical
        if ($rows_emp[0]["CLAIM_GROUP"] == "YTY_CL_A") {
			$medicalType = "003";
		} else if ($rows_emp[0]["CLAIM_GROUP"] == "YTY_CL_A") {
			$medicalType = "004";
		}

        //Check if any existing pending claim
        if ( ($rows_emp[0]["CLAIM_GROUP"] == "YTY_CL_A" && $data->CLAIM_TYPE == '003') || 
			 ($rows_emp[0]["CLAIM_GROUP"] == "YTY_CL_B" && $data->CLAIM_TYPE == '004')) {

            $sql = "SELECT distinct a.claim_id
                      FROM claim_info a, claim_header b
                     WHERE a.claim_id = b.claim_id
                       AND a.employee_id = :EMP_ID
                       AND b.claim_type = :MEDICAL_TYPE
					   AND a.claim_status = 'Process'";
        
        } else {

            $sql = "SELECT distinct a.claim_id
                      FROM claim_info a, claim_header b
                     WHERE a.claim_id = b.claim_id
                       AND a.employee_id = :EMP_ID
                       AND b.claim_type != :MEDICAL_TYPE
					   AND a.claim_status = 'Process'";
        } 

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':EMP_ID', $data->EMP_ID);
        oci_bind_by_name($sqlarr, ':MEDICAL_TYPE', $medicalType);
        $r = oci_execute($sqlarr);
        while ($row = oci_fetch_array($sqlarr)) {
            $current_id = $row['CLAIM_ID'];
        }

        //If no existing pending claim, generate New ID
        if (empty($current_id)) {
            
            //Claim ID
            $sql = "SELECT lpad(:EMP_ID, 9, '0')
                           || to_char(sysdate, 'RRMMDD')
                           || to_char(nvl(COUNT(1), 0)+ 1) as newid
                      FROM production.claim_info
                     WHERE employee_id = :EMP_ID";

            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':EMP_ID', $data->EMP_ID);
            $r = oci_execute($sqlarr);
            while ($row = oci_fetch_array($sqlarr)) {
                $current_id = $row['NEWID'];
            }

            //Insert into Claim Info
            $sql = "INSERT INTO production.claim_info 
                                (claim_id, employee_id, employee_name, dept_id, claim_date, 
                                created_by, creation_date, organization_id, claim_status, ip_address)
                         VALUES (:CLAIM_ID, :EMPLOYEE_ID, :EMPLOYEE_NAME, :DEPT_ID, SYSDATE, 
                                :CREATED_BY, SYSDATE, :ORGANIZATION_ID, 'Process', 'Mobile')";
            
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':CLAIM_ID', $current_id);
            oci_bind_by_name($sqlarr, ':EMPLOYEE_ID', $data->EMP_ID);
            oci_bind_by_name($sqlarr, ':EMPLOYEE_NAME', $rows_emp[0]["EMPLOYEE_NAME"]);
            oci_bind_by_name($sqlarr, ':DEPT_ID', $rows_emp[0]["EMP_ID"]);
            oci_bind_by_name($sqlarr, ':CREATED_BY', $rows_emp[0]["EMPLOYEE_NAME"]);
            oci_bind_by_name($sqlarr, ':ORGANIZATION_ID', $rows_emp[0]["ORGID"]);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
    
            if (!$r) {
                $e = oci_error($sqlarr);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }
        }

        //Claim Header
        $sql = "SELECT claim_header_id
                  FROM production.claim_header
                 WHERE claim_id = :CLAIM_ID
                   AND claim_type = :CLAIM_TYPE";
        
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':CLAIM_ID', $current_id);
        oci_bind_by_name($sqlarr, ':CLAIM_TYPE', $data->CLAIM_TYPE);
        $r = oci_execute($sqlarr);
        while ($row = oci_fetch_array($sqlarr)) {
            $header_id = $row['CLAIM_HEADER_ID'];
        }

        if (empty($header_id)) {
            
            //New Header ID
            $sql = "SELECT max(to_number(nvl(claim_header_id,0)))+1 as MAX_VAL 
                      FROM production.claim_header";

            $sqlarr = oci_parse($dbcon , $sql);
            $r = oci_execute($sqlarr);
            while ($row = oci_fetch_array($sqlarr)) {
                $header_id = $row['MAX_VAL'];
            }

            //Insert into Claim Header
            $sql = "INSERT INTO production.claim_header
                                (claim_header_id, claim_id, claim_type, claim_description, created_by, creation_date, organization_id, claim_tot_amt_myr, claim_tot_tax_amt, claim_total_amt)
                         VALUES (:claim_header_id, :claim_id, :claim_type, case when :claim_type = '002' then 'MILEAGE' end, :created_by, sysdate, :organization_id, 0, 0, 0)";
            
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':claim_header_id', $header_id);
            oci_bind_by_name($sqlarr, ':claim_id', $current_id);
            oci_bind_by_name($sqlarr, ':claim_type', $data->CLAIM_TYPE);
            oci_bind_by_name($sqlarr, ':created_by', $rows_emp[0]["EMPLOYEE_NAME"]);
            oci_bind_by_name($sqlarr, ':organization_id', $rows_emp[0]["ORGID"]);
            $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);
            
            if (!$r) {
                $e = oci_error($sqlarr);
                $errorMsg = json_encode($e['message']);
                goto finalStep;
            }

        }

        //New Detail ID
        $sql = "SELECT max(to_number(nvl(claim_detail_id,0)))+1 as MAX_VAL 
                  FROM production.claim_detail";

        $sqlarr = oci_parse($dbcon , $sql);
        $r = oci_execute($sqlarr);
        while ($row = oci_fetch_array($sqlarr)) {
            $line_id = $row['MAX_VAL'];
        }

        //Insert into Claim Detail
        //Get Exchange Rate
        $exchange_rate = 1;
        if ($data->CLAIM_CURRENCY_TYPE != 'MYR') {
            $sql = "SELECT production.curr_exch_rate(TO_DATE(:DT, 'YYYY-MM-DD'), :CURR) rate
                      FROM dual";
        
            $sqlarr = oci_parse($dbcon , $sql);
            oci_bind_by_name($sqlarr, ':DT', $data->DT);
            oci_bind_by_name($sqlarr, ':CURR', $data->CLAIM_CURRENCY_TYPE);
            $r = oci_execute($sqlarr);
            while ($row = oci_fetch_array($sqlarr)) {
                $exchange_rate = $row['RATE'];
            }
        }

        //MILAGE
        $rateperkm = 0.65;
        if ($data->CLAIM_TYPE == '002') {
            $claim_in_myr = $data->KILOMETER * $rateperkm;
            $tax_in_myr = 0;
            $total_in_myr = $claim_in_myr + $tax_in_myr;

        } else {
            $claim_in_myr = $data->CLAIM_BILL_AMT * $exchange_rate;
            $tax_in_myr = $data->CLAIM_TAX_AMT * $exchange_rate;
            $total_in_myr = $claim_in_myr + $tax_in_myr;
        }

        //Upload the Attachment
        $attachment = base64_decode($data->ATTACHMENT);
        $ext = end(explode('.', $data->CLAIM_FILE_EXTENSION));
        $filenameonly = $current_id."_".$data->CLAIM_TYPE."_".$line_id.".".$ext;
        $newfilename = $default_loc.$current_id."_".$data->CLAIM_TYPE."_".$line_id.".".$ext;
        file_put_contents($newfilename, $attachment);

        $sql = "INSERT INTO production.claim_detail
                            (claim_line_no, claim_header_id, claim_id, claim_type, claim_description, claim_bill_amt, claim_tax_amt, 
                             claim_currency_type, claim_exchange_rate, total_claim_amt_myr, remark, created_by, creation_date, 
                             organization_id, claim_detail_id, claim_file_extension, kilometer, rate_per_kilometer,
                             last_updated_by, last_updated_date)
                     VALUES (:claim_line_no, :claim_header_id, :claim_id, :claim_type, :claim_description, :claim_bill_amt, :claim_tax_amt, 
                             :claim_currency_type, :claim_exchange_rate, :total_claim_amt_myr, :remark, :created_by, to_date(:creation_date,'YYYY-MM-DD'), 
                             :organization_id, :claim_detail_id, :claim_file_extension, :kilometer, :rate_per_kilometer,
                             :last_updated_by, sysdate)";
        
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':claim_line_no', $line_id);
        oci_bind_by_name($sqlarr, ':claim_header_id', $header_id);
        oci_bind_by_name($sqlarr, ':claim_id', $current_id);
        oci_bind_by_name($sqlarr, ':claim_type', $data->CLAIM_TYPE);
        oci_bind_by_name($sqlarr, ':claim_description', $data->CLAIM_DESCRIPTION);
        oci_bind_by_name($sqlarr, ':claim_bill_amt', $data->CLAIM_BILL_AMT);
        oci_bind_by_name($sqlarr, ':claim_tax_amt', $data->CLAIM_TAX_AMT);
        oci_bind_by_name($sqlarr, ':claim_currency_type', $data->CLAIM_CURRENCY_TYPE);
        oci_bind_by_name($sqlarr, ':claim_exchange_rate', $exchange_rate);
        oci_bind_by_name($sqlarr, ':total_claim_amt_myr', $total_in_myr);
        oci_bind_by_name($sqlarr, ':remark', $data->REMARK);
        oci_bind_by_name($sqlarr, ':created_by', $rows_emp[0]["EMPLOYEE_NAME"]);
        oci_bind_by_name($sqlarr, ':creation_date', $data->DT);
        oci_bind_by_name($sqlarr, ':organization_id', $rows_emp[0]["ORGID"]);
        oci_bind_by_name($sqlarr, ':claim_detail_id', $line_id);
        oci_bind_by_name($sqlarr, ':claim_file_extension', $filenameonly);
        oci_bind_by_name($sqlarr, ':kilometer', $data->KILOMETER);
        oci_bind_by_name($sqlarr, ':rate_per_kilometer', $rateperkm);
        oci_bind_by_name($sqlarr, ':claim_url', $data->CLAIM_URL);
        oci_bind_by_name($sqlarr, ':last_updated_by', $rows_emp[0]["EMPLOYEE_NAME"]);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

        //Update Claim Header
        //Get total
        $sql = "SELECT trunc(sum(claim_bill_amt * claim_exchange_rate),2) claim_bill_amt,
                       trunc(sum(claim_tax_amt * claim_exchange_rate),2) claim_tax_amt,
                       trunc(sum(total_claim_amt_myr),2) total_claim_amt_myr
                  FROM production.claim_detail 
                 WHERE claim_id = :claim_id
                   AND claim_type = :claim_type";

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':claim_id', $current_id);
        oci_bind_by_name($sqlarr, ':claim_type', $data->CLAIM_TYPE);
        $r = oci_execute($sqlarr);
        while ($row = oci_fetch_array($sqlarr)) {
            $total_bill_amt = $row['CLAIM_BILL_AMT'];
            $total_tax_amt = $row['CLAIM_TAX_AMT'];
            $total_claim_amt_myr = $row['TOTAL_CLAIM_AMT_MYR'];
        }

        $sql = "UPDATE production.claim_header
                   SET claim_tot_amt_myr = :claim_tot_amt_myr,
                       claim_tot_tax_amt = :claim_tot_tax_amt,
                       claim_total_amt = :claim_total_amt,
                       last_updated_by = :last_updated_by,
                       last_updated_date = sysdate
                 WHERE claim_id = :claim_id
                   AND claim_type = :claim_type";

        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':claim_tot_amt_myr', $total_bill_amt);
        oci_bind_by_name($sqlarr, ':claim_tot_tax_amt', $total_tax_amt);
        oci_bind_by_name($sqlarr, ':claim_total_amt', $total_claim_amt_myr);
        oci_bind_by_name($sqlarr, ':last_updated_by', $rows_emp[0]["EMPLOYEE_NAME"]);
        oci_bind_by_name($sqlarr, ':claim_id', $current_id);
        oci_bind_by_name($sqlarr, ':claim_type', $data->CLAIM_TYPE);
        $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

        if (!$r) {
            $e = oci_error($sqlarr);
            $errorMsg = json_encode($e['message']);
            goto finalStep;
        }

        //Claim Mobile
        $sql = "INSERT INTO production.claim_detail_mobile
                            (claim_type, claim_description, claim_bill_amt, claim_tax_amt, claim_currency_type, claim_exchange_rate, total_claim_amt_myr, remark, created_by, creation_date, organization_id, claim_file_extension, kilometer, rate_per_kilometer, claim_url)
                     VALUES (:CLAIM_TYPE, :CLAIM_DESCRIPTION, :CLAIM_BILL_AMT, :CLAIM_TAX_AMT, :CLAIM_CURRENCY_TYPE, :CLAIM_EXCHANGE_RATE, :TOTAL_CLAIM_AMT_MYR, :REMARK, :CREATED_BY, SYSDATE, :ORGANIZATION_ID, :CLAIM_FILE_EXTENSION, :KILOMETER, :RATE_PER_KILOMETER, :CLAIM_URL)
                  RETURNING ID INTO :NEWID";
    
        $sqlarr = oci_parse($dbcon , $sql);
        oci_bind_by_name($sqlarr, ':CLAIM_TYPE', $data->CLAIM_TYPE);
        oci_bind_by_name($sqlarr, ':CLAIM_DESCRIPTION', $data->CLAIM_DESCRIPTION);
        oci_bind_by_name($sqlarr, ':CLAIM_BILL_AMT', $data->CLAIM_BILL_AMT);
        oci_bind_by_name($sqlarr, ':CLAIM_TAX_AMT', $data->CLAIM_TAX_AMT);
        oci_bind_by_name($sqlarr, ':CLAIM_CURRENCY_TYPE', $data->CLAIM_CURRENCY_TYPE);
        oci_bind_by_name($sqlarr, ':CLAIM_EXCHANGE_RATE', $exchange_rate);
        oci_bind_by_name($sqlarr, ':TOTAL_CLAIM_AMT_MYR', $total_in_myr);
        oci_bind_by_name($sqlarr, ':REMARK', $data->REMARK);
        oci_bind_by_name($sqlarr, ':CREATED_BY', $rows_emp[0]["EMPLOYEE_NAME"]);
        oci_bind_by_name($sqlarr, ':CREATION_DATE', $data->DT);
        oci_bind_by_name($sqlarr, ':ORGANIZATION_ID', $rows_emp[0]["ORGID"]);
        oci_bind_by_name($sqlarr, ':CLAIM_FILE_EXTENSION', $filenameonly);
        oci_bind_by_name($sqlarr, ':KILOMETER', $data->KILOMETER);
        oci_bind_by_name($sqlarr, ':RATE_PER_KILOMETER', $rateperkm);
        oci_bind_by_name($sqlarr, ':CLAIM_URL', $data->CLAIM_URL);
        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 {
        $errorMsg = "Invalid request";
    }

finalStep:
    $result = [];
    if ($errorMsg == "") {
        oci_commit($dbcon);
        $result[] = [
            "message" => "OK",
            "data" => "Save Success"
        ];
    } else {
        oci_rollback($dbcon);
        $result[] = [
            "message" => $errorMsg,
            "data" => ""
        ];
    }
    print json_encode($result);
?>
