BE_IBL/application/controllers/satu_sehat/Listpatient.php

<?php
class Listpatient extends MY_Controller
{
    var $db;
    var $load;
    var $satusehat;
    function __construct()
    {
        parent::__construct();
        $this->satusehat =  $this->load->library('Satusehat');
    }

    function index()
    {
        echo "SATU SEHAT LIST PATIENT";
    }

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

            $startdate = $prm["startdate"];
            $enddate = $prm["enddate"];
            $nomorlab = $prm["nomorlab"];
            $companyid = $prm["companyid"];
            $status = $prm["status"];
            if ($status == "NEW") {
                $filter_status = " AND (IFNULL(BundleStatus, 'NEW') = '{$status}') ";
            } else {
                $filter_status = " AND (BundleStatus = '{$status}') ";
            }

            $filter_date = " AND DATE(T_OrderHeaderDate) BETWEEN '{$startdate}' AND '{$enddate}'";

            $where = " T_OrderHeaderIsActive = 'Y' $filter_date ";

            if ($nomorlab != "") {
                $where .= " AND ( T_OrderHeaderLabNumber LIKE '%{$nomorlab}%' )";
            }

            if ($companyid != 0 || $companyid != "0") {
                $filter_company = " AND T_OrderHeaderM_CompanyID = {$companyid}";
            }

            $number_offset = 0;
            $number_limit = 20;

            if ($prm["current_page"] > 0) {
                $number_offset = ($prm["current_page"] - 1) * $number_limit;
            }

            $sql_total = "SELECT count(*) as total FROM ( 
                SELECT T_OrderHeaderID
                FROM t_orderheader
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
                AND M_PatientIsActive = 'Y'
                LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
                AND M_TitleIsActive = 'Y'
                JOIN m_company ON T_OrderHeaderM_CompanyID = M_CompanyID
                AND M_CompanyIsActive = 'Y'
                JOIN result_processtooffice ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
                AND Result_ProcessToOfficeIsActive = 'Y'
                LEFT JOIN one_health.bundle ON T_OrderHeaderID  = BundleT_orderHeaderID
                WHERE $where $filter_company $filter_status
                GROUP BY T_OrderHeaderID) x";
            $qry_total = $this->db->query($sql_total);
            // print_r($this->db->last_query());
            // exit;

            $tot_count = 0;
            $tot_page = 0;
            if ($qry_total) {
                $tot_count = $qry_total->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->db_regional->trans_rollback();
                $this->sys_error_db("orderheader count error", $this->db);
                exit;
            }
            // print_r($tot_count);
            // exit;

            $sql = "SELECT * FROM( 
                SELECT T_OrderHeaderID,
                T_OrderHeaderDate,
                T_OrderHeaderLabNumber,
                T_OrderHeaderLabNumberExt,
                M_PatientNoReg,
                M_PatientName,
                concat(M_TitleName,'. ',M_PatientName) as patient_fullname,
                M_CompanyID,
                M_CompanyName,
                BundleID,
                IFNULL(BundleStatus, NULL) as BundleStatus,
                Result_ProcessToOfficeSendTime,
                Result_ProcessToOfficeStatus
                FROM t_orderheader
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
                AND M_PatientIsActive = 'Y'
                LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
                AND M_TitleIsActive = 'Y'
                JOIN m_company ON T_OrderHeaderM_CompanyID = M_CompanyID
                AND M_CompanyIsActive = 'Y'
                JOIN result_processtooffice ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
                AND Result_ProcessToOfficeIsActive = 'Y'
                LEFT JOIN one_health.bundle ON T_OrderHeaderID  = BundleT_orderHeaderID
                WHERE $where $filter_company $filter_status
                GROUP BY T_OrderHeaderID) x
                limit $number_limit offset $number_offset";
            $qry = $this->db->query($sql);
            // print_r($this->db->last_query());
            // exit;
            if ($qry) {
                $rows = $qry->result_array();
            } else {
                $this->db_regional->trans_rollback();
                $this->sys_error_db("Select order error", $this->db);
                exit;
            }

            $result = array(
                "total_page" => $tot_page,
                "total_filter" => $tot_count,
                "records" => $rows,
                // "sql" => $this->db->last_query()
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getcompany()
    {
        try {
            $prm = $this->sys_input;

            $search = "";
            $number_limit = 10;

            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = '%' . $prm['search'] . '%';
                } else {
                    $search = '%%';
                }
            }

            $sql_search = "SELECT M_CompanyID,
                M_CompanyName
                FROM m_company
                WHERE M_CompanyIsActive = 'Y'  
                AND (M_CompanyName LIKE ?)
                limit ?";
            $query_search = $this->db->query($sql_search, [$search, $number_limit]);
            if ($query_search) {
                $rows = $query_search->result_array();
                $rows[] = array('M_CompanyID'=>0,'M_CompanyName'=> 'All');
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("m_company select error", $this->db);
                exit;
            }

            $sql_filter = "SELECT COUNT(*) as total
            FROM m_company
            WHERE M_CompanyIsActive = 'Y' 
            AND (M_CompanyName LIKE ?)
            limit ?";

            $tot_count = 0;
            $qry_filter = $this->db->query($sql_filter, [$search, $number_limit]);
            if ($qry_filter) {
                $tot_count = $qry_filter->result_array()[0]["total"];
            } else {
                $this->sys_error_db("company count");
                exit;
            }

            $result = array(
                "total" => $tot_count,
                "total_display" => sizeof($rows),
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}