<?php
class Generatefutureorder extends MY_Controller
{
    var $db;
    function __construct()
    {
        parent::__construct();
    }
    function index()
    {
        echo "Api: Receiver Order DEVKEDUNGDORO";
    }
    function generate()
    {
        try {
            $this->db->trans_begin();

            $this->insertpatient();
            $this->insertfutureorder();

            $this->db->trans_commit();
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $qry = $this->db->last_query();
            $rst = [
                'msg' => $exc->getMessage(),
                'qry' => $qry,
            ];
            $this->db->trans_rollback();
            $this->sys_error($rst);
        }
    }
    function insertpatient()
    {
        try {
            $sql = "SELECT * 
            FROM one_mitra.m_patient
            WHERE M_PatientPatientID IS NULL
            AND M_PatientIsActive = 'Y'";
            $query = $this->db->query($sql, []);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                $this->db->trans_rollback();

                exit;
            }
            $patients = $query->result_array();
            for ($i = 0; $i < count($patients); $i++) {
                $idtype = 4;
                $nik = $patients[$i]['M_PatientNIK'];
                if ($patients[$i]['M_PatientIsNIK'] == 'Y') {
                    $idtype = 1;
                }
                $sqlQuery = "SELECT  
                    M_PatientID 
                    FROM m_patient
                    WHERE M_PatientM_IdTypeID = ?
                    AND M_PatientIDNumber = ?
                    AND M_PatientIsActive = 'Y'";
                $qry = $this->db->query($sqlQuery, [$idtype, $nik]);
                if (!$qry) {
                    $message = $this->db->error();
                    $this->sys_error($message);
                    $this->db->trans_rollback();

                    exit;
                }
                $result = $qry->result_array();
                if (count($result) > 0) {
                    $sqlQuery = "UPDATE one_mitra.m_patient SET M_PatientPatientID = ? WHERE M_PatientID = ?;";
                    $qry = $this->db->query($sqlQuery, [$result[0]['M_PatientID'], $patients[$i]['M_PatientID']]);
                    if (!$qry) {
                        $message = $this->db->error();
                        $this->sys_error($message);
                        $this->db->trans_rollback();

                        exit;
                    }
                } else {
                    //apabila tidak ada 
                    //insert m patient bisone
                    $sqlQuery = "INSERT INTO m_patient (
                                    M_PatientPrefix,
                                    M_PatientM_TitleID,
                                    M_PatientSuffix,
                                    M_PatientM_SexID,
                                    M_PatientDOB,
                                    M_PatientHP,
                                    M_PatientM_IdTypeID,
                                    M_PatientIDNumber,
                                    M_PatientName,
                                    M_PatientJabatan,
                                    M_PatientKedudukan,
                                    M_PatientLocation,
                                    M_PatientJob
                                ) VALUES (?,?,?,?,?,?,?,?, ?,?,?,?,?)";
                    $qry = $this->db->query($sqlQuery, [
                        $patients[$i]['M_PatientPrefix'],
                        $patients[$i]['M_PatientTitleID'],
                        $patients[$i]['M_PatientSuffix'],
                        $patients[$i]['M_PatientM_SexID'],
                        $patients[$i]['M_PatientDOB'],
                        $patients[$i]['M_PatientHP'],
                        $idtype,
                        $nik,
                        $patients[$i]['M_PatientName'],
                        $patients[$i]['M_PatientJabatan'],
                        $patients[$i]['M_PatientKedudukan'],
                        $patients[$i]['M_PatientLocation'],
                        $patients[$i]['M_PatientJob']
                    ]);
                    if (!$qry) {
                        $message = $this->db->error();
                        $this->sys_error($message);
                        $this->db->trans_rollback();
                        exit;
                    }
                    $bisonePatientID = $this->db->insert_id();

                    $sqlQuery = "UPDATE one_mitra.m_patient SET M_PatientPatientID = ? WHERE M_PatientID = ?;";
                    $qry = $this->db->query($sqlQuery, [$bisonePatientID, $patients[$i]['M_PatientID']]);
                    if (!$qry) {
                        $message = $this->db->error();
                        $this->sys_error($message);
                        $this->db->trans_rollback();
                        exit;
                    }

                    //get mcompanykelurahan
                    $sqlQuery = "SELECT M_CompanyM_KelurahanID,
                                 M_CompanyAddress 
                                 FROM m_company
                                 WHERE M_CompanyID = ?
                                 AND M_CompanyIsActive= 'Y'";
                    $qry = $this->db->query($sqlQuery, [
                        $patients[$i]['M_PatientM_CompanyID'],
                    ]);
                    if (!$qry) {
                        $message = $this->db->error();
                        $this->sys_error($message);
                        $this->db->trans_rollback();
                        exit;
                    }
                    $companyKelurahanID = $qry->result_array()[0]['M_CompanyM_KelurahanID'];
                    $companyAddress = $qry->result_array()[0]['M_CompanyAddress'];


                    $patientAddress = $patients[$i]['M_PatientAddress'];
                    if ($patientAddress == null || trim($patientAddress) == "") {
                        $patientAddress = $companyAddress;
                    }

                    //get mcompanykelurahan
                    $sqlQuery = "INSERT INTO m_patientaddress
                                (M_PatientAddressM_PatientID,
                                M_PatientAddressNote,
                                M_PatientAddressDescription,
                                M_PatientAddressM_KelurahanID,
                                M_PatientAddressCreated,
                                M_PatientAddressLastUpdated,
                                M_PatientAddressUserID)
                                VALUES(?,?,?,?,NOW(), NOW(),'0')";
                    $qry = $this->db->query($sqlQuery, [
                        $bisonePatientID,
                        "Rumah",
                        $patientAddress,
                        $companyKelurahanID
                    ]);
                    if (!$qry) {
                        $message = $this->db->error();
                        $this->sys_error($message);
                        $this->db->trans_rollback();
                        exit;
                    }
                }
            }
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->db->trans_rollback();
            $this->sys_error($message);
        }
    }
    function insertfutureorder()
    {
        try {
            $sqlDoctor = "SELECT
                    M_DoctorID,
                    M_DoctorAddressID
                    FROM m_doctor
                    JOIN m_doctoraddress
                    ON M_DoctorID = M_DoctorAddressM_DoctorID
                    AND M_DoctorAddressIsActive = 'Y'
                    WHERE M_DoctorName = '-'
                    AND M_DoctorIsActive = 'Y'
                    GROUP BY M_DoctorID
                    LIMIT 1;";
            $qryDoctor = $this->db->query($sqlDoctor);
            if (!$qryDoctor) {
                $message = $this->db->error();
                $this->sys_error($message);
                $this->db->trans_rollback();

                exit;
            }
            $doctor = $qryDoctor->result_array()[0];
            $sqlDoctorPj = "SELECT 
                            M_DoctorPjM_DoctorID AS ID 
                            FROM m_doctorpj
                            WHERE M_DoctorPjIsActive = 'Y'
                            LIMIT 1";
            $qryDoctorPj = $this->db->query($sqlDoctorPj);
            if (!$qryDoctorPj) {
                $message = $this->db->error();
                $this->sys_error($message);
                $this->db->trans_rollback();

                exit;
            }
            $doctorPj = $qryDoctorPj->result_array()[0];
            $sql = "SELECT 
                pm.M_PatientPatientID,
                pb.M_PatientID AS bisonePatientID,
                fn_global_age_count(pb.M_PatientDOB, NOW()) AS age,
                pm.M_PatientHP AS hp,
                one_mitra.t_order.* ,
                IFNULL(one_mitra.fn_get_order_detail (T_OrderID),'[]') AS orderDetail,
                IFNULL(one_mitra.fn_get_order_detail_packet(T_OrderID),'[]') AS orderPacket,
                T_OrderDeliveryDate
                FROM one_mitra.t_order
                JOIN one_mitra.m_patient AS pm
                ON T_OrderM_PatientID = pm.M_PatientID
                AND  M_PatientIsActive = 'Y'
                JOIN m_patient AS pb
                ON pm.M_PatientPatientID = pb.M_PatientID
                AND pb.M_PatientIsActive = 'Y'
                JOIN one_mitra.t_orderdetaildelivery
                ON T_OrderID = T_OrderDetailDeliveryT_OrderID
                AND T_OrderDetailDeliveryIsActive = 'Y'
                JOIN one_mitra.t_orderdelivery
                ON T_OrderDetailDeliveryT_OrderDeliveryID = T_OrderDeliveryID
                AND T_OrderDeliveryIsActive = 'Y'
                WHERE 
                T_OrderIsActive = 'Y'
                AND T_OrderFutureOrderID IS NULL";
            $qry = $this->db->query($sql);
            if (!$qry) {
                $message = $this->db->error();
                $this->sys_error($message);
                $this->db->trans_rollback();
                exit;
            }
            $order = $qry->result_array();
            for ($i = 0; $i < count($order); $i++) {
                if ($order[$i]['orderDetail'] != "[]") {
                    $order[$i]['orderDetail'] = json_decode($order[$i]['orderDetail'], true);
                } else {
                    $order[$i]['orderDetail'] = [];
                }
                if ($order[$i]['orderPacket'] != "[]") {
                    $order[$i]['orderPacket'] = json_decode($order[$i]['orderPacket'], true);
                } else {
                    $order[$i]['orderPacket'] = [];
                }
            }
            //insert future order
            for ($i = 0; $i < count($order); $i++) {
                $date = $order[$i]['T_OrderDeliveryDate'];
                $sql_insert = "INSERT INTO future_order
                                    (FutureOrderNumber,
                                    FutureOrderDateBooking,
                                    FutureOrderM_PatientID,
                                    FutureOrderM_CompanyID,
                                    FutureOrderM_MouID,
                                    FutureOrderSenderM_DoctorID,
                                    FutureOrderSenderM_DoctorAddressID,
                                    FutureOrderSubTotal,
                                    FutureOrderTotal,
                                    FutureOrderUserID)
                                    VALUES(fn_numbering_future('FO'),?,?,?,?,?,?,?,?,?)";

                $qry_insert = $this->db->query($sql_insert, [
                    $date,
                    $order[$i]["bisonePatientID"],
                    $order[$i]['T_OrderM_CompanyID'],
                    $order[$i]['T_OrderM_MouID'],
                    $doctor['M_DoctorID'],
                    $doctor['M_DoctorAddressID'],
                    $order[$i]['T_OrderTotal'],
                    $order[$i]['T_OrderTotal'],
                    "1500"
                ]);
                if (!$qry_insert) {
                    $message = $this->db->error();
                    $err = [
                        'msg' => $message,
                        'qry' => $this->db->last_query(),
                    ];
                    $this->sys_error($err);
                    $this->db->trans_rollback();
                    exit;
                }
                $futureOrderInsertedID = $this->db->insert_id();
                // print_r($order[$i]);
                // exit;
                $total = 0;
                $jsonHeader = array(
                    "booking_date" => date("dmY"),
                    "patient_id" => $order[$i]["bisonePatientID"],
                    "age" => $order[$i]["age"],
                    "alias_doctor" => "",
                    "alias_doctor_address" => "",
                    "sender_doctor_id" => intval($doctor['M_DoctorID']),
                    "sender_address_id" => $doctor['M_DoctorAddressID'],
                    "pj_doctor_id" => $doctorPj['ID'],
                    "lang_id" => "1",
                    "lang_si" => "N",
                    "lang_id_2" => 0,
                    "lang_si_2" => "N",
                    "doctor_note" => "",
                    "fo_note" => $order[$i]['T_OrderNote'],
                    "patient_note" => "",
                    "company_id" => $order[$i]['T_OrderM_CompanyID'],
                    "mou_id" => intval($order[$i]['T_OrderM_MouID']),
                    "received_sample" => "N",
                    "received_sample_time" => "",
                    "queue" => "",
                    "diagnose" => $order[$i]['T_OrderDiagnosis'],
                    "is_cito" => "N",
                    "cito_id" => "2",
                    "mcu_pre_id" => 0,
                );
                $jsonDetail = array();
                // print_r($order[$i]['orderDetail'][0]);
                // exit;
                for ($k = 0; $k < count($order[$i]['orderDetail']); $k++) {
                    $detail = $order[$i]['orderDetail'][$k];

                    $sql_test = "SELECT 
                            T_PriceTotal
                            FROM ss_price_mou 
                            join t_test on T_PriceIsCito= 'N' and is_packet = 'N'
                            AND Ss_PriceMouM_MouID = ?
                            AND T_PriceM_CompanyID = ?
                            and ss_price_mou.T_TestID = t_test.T_TestID
                            AND t_test.T_TestID= ?";
                    $qry_test = $this->db->query($sql_test, [
                        $order[$i]['T_OrderM_MouID'],
                        $order[$i]['T_OrderM_CompanyID'],
                        $detail['T_OrderDetailTestID']
                    ]);
                    if (!$qry_test) {
                        $message = $this->db->error();
                        $err = [
                            'msg' => $message,
                            'qry' => $this->db->last_query(),
                        ];
                        $this->sys_error($err);
                        $this->db->trans_rollback();
                        exit;
                    }
                    $testPrice = $qry_test->result_array();
                    if (count($testPrice) == 0) {
                        $testPrice = 0;
                    } else {
                        $testPrice = $testPrice[0]['T_PriceTotal'];
                    }

                    $sql_insert = "INSERT INTO future_orderdetail (
                                       Future_OrderDetailFutureOrderID,
                                       Future_OrderDetailPacketType,
                                       Future_OrderDetailT_PacketID,
                                       Future_OrderDetailT_TestID,
                                       Future_OrderDetailPrice,
                                       Future_OrderDetailDisc,
                                       Future_OrderDetailDiscTotal,
                                       Future_OrderDetailTotal,
                                       Future_OrderDetailUserID)
                                       VALUES (?,?,?,?,?,?,?,?,?)";

                    $qry_insert = $this->db->query($sql_insert, [
                        $futureOrderInsertedID,
                        0,
                        0,
                        $detail['T_OrderDetailTestID'],
                        doubleval($testPrice),
                        "0",
                        0,
                        $testPrice,
                        "0"
                    ]);
                    if (!$qry_insert) {
                        $message = $this->db->error();
                        $err = [
                            'msg' => $message . "FutureOrderdetail bukan paket",
                            'qry' => $this->db->last_query(),
                            "sql" => $sql_insert
                        ];
                        $this->sys_error($err);
                        $this->db->trans_rollback();
                        exit;
                    }
                    $jsonDetail[] = array(
                        "t_id" => intval($detail['T_OrderDetailTestID']),
                        "t_price" => $testPrice,
                        "t_disc" => "0",
                        "t_discrp" => "0",
                        "t_cito" => "N",
                        "t_req" => "N",
                        "t_reqnote" => "",
                        "t_ispacket" => "N",
                        "t_packetid" => 0,
                        "t_packettype" => "PX"
                    );
                    $total = $total + doubleval($testPrice);
                }
                // print_r($jsonDetail);
                // exit;
                //Insert packet 
                for ($k = 0; $k < count($order[$i]['orderPacket']); $k++) {
                    $packet = $order[$i]['orderPacket'][$k];

                    $sql_packet = "SELECT 
                            T_PacketType AS type,
                            T_PacketPrice AS price
                            FROM t_packet
                            WHERE T_PacketID = ?
                            AND T_PacketIsActive = 'Y'";
                    $qry_packet = $this->db->query($sql_packet, [
                        $packet['T_OrderDetailPacketT_PacketID']
                    ]);
                    if (!$qry_packet) {
                        $message = $this->db->error();
                        $this->sys_error($message);
                        $this->db->trans_rollback();
                        exit;
                    }
                    $packetData = $qry_packet->result_array();
                    $sql_insert = "INSERT INTO future_orderdetail(
                                         Future_OrderDetailFutureOrderID,
                                         Future_OrderDetailPacketType,
                                         Future_OrderDetailT_PacketID,
                                         Future_OrderDetailT_TestID,
                                         Future_OrderDetailPrice,
                                         Future_OrderDetailDisc,
                                         Future_OrderDetailDiscTotal,
                                         Future_OrderDetailTotal,
                                         Future_OrderDetailUserID)
                                         VALUES (?,?,?,?,?,?,?,?,?)";

                    $qry_insert = $this->db->query($sql_insert, [
                        $futureOrderInsertedID,
                        $packetData[0]['type'],
                        $packet['T_OrderDetailPacketT_PacketID'],
                        "0",
                        doubleval($packetData[0]['price']),
                        "0",
                        0,
                        $packetData[0]['price'],
                        "0"
                    ]);
                    if (!$qry_insert) {
                        $message = $this->db->error();
                        $err = [
                            'msg' => $message . " Futureorderdetailpacket",
                            'qry' => $this->db->last_query(),
                        ];
                        $this->sys_error($err);
                        $this->db->trans_rollback();
                        exit;
                    }
                    $jsonDetail[] = array(
                        "t_id" => intval($packet['T_OrderDetailPacketT_PacketID']),
                        "t_price" => $packetData[0]['price'],
                        "t_disc" => "0",
                        "t_discrp" => "0",
                        "t_cito" => "N",
                        "t_req" => "N",
                        "t_reqnote" => "",
                        "t_ispacket" => "Y",
                        "t_packetid" => intval($packet['T_OrderDetailPacketT_PacketID']),
                        "t_packettype" => $packetData[0]['type']
                    );
                    $total = $total + doubleval($packetData[0]['price']);
                }
                $FutureOrderJSONReq = ["status" => "Y", "reqs" => []];

                $sql_company = "SELECT 
                                M_CompanyAddress,
                                M_CompanyM_KelurahanID,
                                M_CompanyAddressRegionalCd
                                FROM m_company
                                WHERE M_CompanyID = ?
                                AND M_CompanyIsActive = 'Y'";
                $qry_company = $this->db->query($sql_company, [$order[$i]['T_OrderM_CompanyID']]);
                if (!$qry_company) {
                    $message = $this->db->error();
                    $err = [
                        'msg' => $message,
                        'qry' => $this->db->last_query(),
                    ];
                    $this->sys_error($err);

                    $this->db->trans_rollback();
                    exit;
                }
                $company = $qry_company->result_array()[0];

                $jsonDelivery = [[
                    "delivery_id" => "10",
                    "delivery_code" => "ADDRESS",
                    "delivery_type_id" => "2",
                    "address_id" => "0",
                    "note" => $company['M_CompanyAddress'],
                    "noteplus" => "",
                    "kelurahan" => $company['M_CompanyAddressRegionalCd']
                ]];
                $FutureOrderJSONDeliveryNote = [];

                //insert future_qrcode_confirm
                if ($order[$i]['T_OrderIsQRCode'] == 'Y') {
                    $hp = trim($order[$i]['hp']);
                    if ($hp != "0" && $hp != "-" && !empty($hp)) {
                        $sql_insert = "INSERT INTO future_qrcode_confirm
                            (FutureQrCodeConfirmFutureOrderID,
                            FutureQrCodeConfirmPhone
                            )VALUES
                            (?,?)";
                        $qry_insert = $this->db->query($sql_insert, [
                            $futureOrderInsertedID,
                            $hp
                        ]);
                        if (!$qry_insert) {
                            $message = $this->db->error();
                            $err = [
                                'msg' => $message,
                                'qry' => $this->db->last_query(),
                            ];
                            $this->sys_error($err);

                            $this->db->trans_rollback();
                            exit;
                        }
                    };
                }
                $headerJsn = json_encode($jsonHeader);
                $detailJsn = json_encode($jsonDetail);
                $deliveryJsn = json_encode($jsonDelivery);
                $reqJsn = json_encode($FutureOrderJSONReq);
                $noteJsn = json_encode($FutureOrderJSONDeliveryNote);

                $sql_update = "UPDATE future_order
                        SET FutureOrderJSONHeader = '$headerJsn',
                        FutureOrderJSONDetail = '$detailJsn',
                        FutureOrderJSONDelivery='$deliveryJsn',
                        FutureOrderJSONReq = '$reqJsn',
                        FutureOrderJSONDeliveryNote = '$noteJsn'
                        WHERE FutureOrderID = ?";
                $qry_update = $this->db->query($sql_update, [
                    json_encode($futureOrderInsertedID)
                ]);
                if (!$qry_update) {
                    $message = $this->db->error();
                    $err = [
                        'msg' => $message,
                        'qry' => $this->db->last_query(),
                    ];
                    $this->sys_error($err);
                    $this->db->trans_rollback();
                    exit;
                }

                $sql_update = "UPDATE one_mitra.t_order
                        SET T_OrderFutureOrderID = ?
                        WHERE T_OrderID = ?";
                $qry_update = $this->db->query($sql_update, [
                    $futureOrderInsertedID,
                    $order[$i]['T_OrderID']
                ]);
                if (!$qry_update) {
                    $message = $this->db->error();
                    $err = [
                        'msg' => $message,
                        'qry' => $this->db->last_query(),
                    ];
                    $this->sys_error($err);
                    $this->db->trans_rollback();
                    exit;
                }
            }
            $this->sys_ok($order);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $qry = $this->db->last_query();
            $rst = [
                'msg' => $exc->getMessage(),
                'qry' => $qry,
            ];
            $this->db->trans_rollback();
            $this->sys_error($rst);
        }
    }
}