BE_CPONE/application/controllers/mockup/refout/status/Status.php

<?php

class Status extends MY_Controller
{
    var $db_smartone;
    public function index()
    {
        echo "REF OUT STATUS API";
    }
   
    public function __construct()
    {
        parent::__construct();
        $this->db_smartone = $this->load->database("onedev", true);
    }

    public function search()
    {
        $prm = $this->sys_input;

        $max_rst = 10;
        $tot_count =0;

        $page = $prm['page'];
        if ($page == null)
            $page = 1;
        $offset = ($page - 1) * $max_rst;

        $q = [
            'search'    => isset($prm['search']) ? "%{$prm['search']}%" : '%'
        ];

        $q_company = "m_company";
        if ($prm['is_internal'] == 'Y')
            $q_company = "m_branch";

        $sql = "SELECT COUNT(T_OrderRefOutHeaderID) total
                FROM t_orderrefoutheader
                JOIN {$q_company} ON T_OrderRefOutHeader{$q_company}ID = {$q_company}ID
                LEFT JOIN m_courier ON T_OrderRefOutHeaderM_CourierID = M_CourierID
                LEFT JOIN m_staff ON M_CourierM_StaffID = M_StaffID
                WHERE T_OrderRefOutHeaderIsSent = ?
                    AND T_OrderRefOutHeaderIsActive = 'Y'
                    AND ({$q_company}Name LIKE ? OR T_OrderRefOutHeaderNumber LIKE ?)";

        $query = $this->db_smartone->query($sql, array($prm['status'], $q['search'], $q['search']));
        
        if ($query) { 
            $tot_count = $query->result_array()[0]["total"]; 
        } 
        else {
            $this->sys_error_db("Ref Out Status count",$this->db_smartone);
            exit;
        }

        $sql = "SELECT T_OrderRefOutHeaderID, T_OrderRefOutHeaderDate, 
                    {$q_company}ID company_id, {$q_company}Name company_name,
                    T_OrderRefOutHeaderNumber, T_OrderRefOutHeaderIsSent,
                    M_StaffID, M_StaffName, M_KelurahanName, M_DistrictName,
                    M_CityName, M_ProvinceName, {$q_company}Address company_address,
                    M_CourierID, M_StaffName
                FROM t_orderrefoutheader
                JOIN {$q_company} ON T_OrderRefOutHeader{$q_company}ID = {$q_company}ID
                LEFT JOIN m_courier ON T_OrderRefOutHeaderM_CourierID = M_CourierID
                LEFT JOIN m_staff ON M_CourierM_StaffID = M_StaffID
                LEFT JOIN m_kelurahan ON {$q_company}M_KelurahanID = M_KelurahanID
                LEFT JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
                LEFT JOIN m_city ON M_DistrictM_CityID = M_CityID
                LEFT JOIN m_province ON M_CityM_ProvinceID = M_ProvinceID
                WHERE T_OrderRefOutHeaderIsSent = ?
                    AND T_OrderRefOutHeaderIsActive = 'Y'
                    AND ({$q_company}Name LIKE ? OR T_OrderRefOutHeaderNumber LIKE ?)
                limit {$offset}, {$max_rst}";

        $query = $this->db_smartone->query($sql, array($prm['status'], $q['search'], $q['search']));

        if ($query) { 
            $rows = $query->result_array();

            $result = array("total" => $tot_count, "total_page" => ceil($tot_count/$max_rst), "cur_page" => $page, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } 
        else {
            $this->sys_error_db("Ref Out Status rows",$this->db_smartone);
            exit;
        }
    }

    public function search_sample()
    {
        $prm = $this->sys_input;

        $max_rst = 100;
        $tot_count =0;

        $page = $prm['page'];
        if ($page == null)
            $page = 1;
        $offset = ($page - 1) * $max_rst;

        $q = [
            'search'    => isset($prm['search']) ? "%{$prm['search']}%" : '%'
        ];

        $sql = "SELECT COUNT(T_OrderRefOutSampleID) total
                FROM t_orderrefoutsample
                JOIN t_orderrefoutheader ON T_OrderRefOutSampleT_OrderRefOutHeaderID = T_OrderRefOutHeaderID
                JOIN t_barcodelab ON T_OrderRefOutSampleT_BarcodeLabID = T_BarcodeLabID
                WHERE T_ORderRefOutSampleIsActive = 'Y'
                    AND T_OrderRefOutSampleT_OrderRefOutHeaderID = ?";

        $query = $this->db_smartone->query($sql, array($prm['header_id']));
        
        if ($query) { 
            $tot_count = $query->result_array()[0]["total"]; 
        } 
        else {
            $this->sys_error_db("Ref Out Status count",$this->db_smartone);
            exit;
        }

        $sql = "SELECT T_OrderRefOutSampleID, T_BarcodeLabBarcode, T_SampleTypeName, 'N' selected
                FROM t_orderrefoutsample
                JOIN t_orderrefoutheader ON T_OrderRefOutSampleT_OrderRefOutHeaderID = T_OrderRefOutHeaderID
                JOIN t_barcodelab ON T_OrderRefOutSampleT_BarcodeLabID = T_BarcodeLabID
                JOIN t_sampletype ON T_OrderRefOutSampleT_SampleTypeID = T_SampleTypeID
                WHERE T_ORderRefOutSampleIsActive = 'Y'
                    AND T_OrderRefOutSampleT_OrderRefOutHeaderID = ?
                limit {$offset}, {$max_rst}";

        $query = $this->db_smartone->query($sql, array($prm['header_id']));

        if ($query) { 
            $rows = $query->result_array();

            $result = array("total" => $tot_count, "total_page" => ceil($tot_count/$max_rst), "cur_page" => $page, "records" => $rows, "total_display" => sizeof($rows), "query" => $this->db_smartone->last_query());
            $this->sys_ok($result);
        } 
        else {
            $this->sys_error_db("Ref Out Status rows",$this->db_smartone);
            exit;
        }
    }

    public function search_courier()
    {
        $prm = $this->sys_input;

        $max_rst = 100;

        $sql = "SELECT M_CourierID, M_StaffName
                FROM m_courier
                JOIN m_staff ON M_CourierM_StaffID = M_StaffID
                WHERE M_CourierIsActive = 'Y'";

        $query = $this->db_smartone->query($sql);
        
        if ($query) { 
            $rows = $query->result_array();

            $result = array("records" => $rows);
            $this->sys_ok($result); 
        } 
        else {
            $this->sys_error_db("Ref Out Courier",$this->db_smartone);
            exit;
        }
    }

    public function send()
    {
        $prm = $this->sys_input;

        $sql = "CALL sp_process_refout_status_send(?, ?, ?, ?)";
        $query = $this->db_smartone->query($sql, [$prm['header_id'], $prm['ids'], $prm['courier_id'], $this->sys_user['M_UserID']]);

        if ($query)
        {
            $row = $query->row();
            if ($row->status == "OK")
            {
                $this->sys_ok($row);
                exit;
            }
        }

        $this->sys_error_db("Ref Out Confirm", $this->db_smartone);
    }
}

?>