REG_IBL/one-api/application/controllers/one_mitra/Dashboard.php

<?php

class Dashboard extends MY_Controller
{
    var $db_regional;
    var $load;
    var $db;
    public function __construct()
    {
        parent::__construct();
        $this->db_regional = $this->load->database("regional", true);
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $userID = $this->sys_user['M_UserID'];

        $sql_cek_token = "SELECT  M_UserActiveToken
        from one_mitra.m_user
        WHERE M_UserID = ?
        AND M_UserActiveToken IS NOT NULL";

        $qry_token = $this->db->query($sql_cek_token, [$userID]);
        if (!$qry_token) {
            $this->sys_error('Invalid token');
            exit;
        }

        $rows_token = $qry_token->result_array();
        if (count($rows_token) == 0) {
            $this->sys_error('Invalid token');
            exit;
        }
    }
    function chartdata()
    {
        try {
            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            //month/year
            $filter = 'month';
            if (isset($prm['filter'])) {
                $filter =  $prm['filter'];
            }
            $company_id = $prm['company_id'];
            $filter_sql = "";
            $filter_sql2 = "";
            $filter_sql_total = "";
            $select_sql = "";
            if ($filter == 'month') {
                $select_sql = "DATE_FORMAT(T_OrderDate, '%d') AS day";
                $filter_sql2 = "AND MONTH(T_OrderDate) = MONTH(CURDATE())";
                $filter_sql =  ", DATE_FORMAT(T_OrderDate, '%Y-%m-%d') ORDER BY day";
                $filter_sql_total =  " DATE_FORMAT(T_OrderDate, '%Y-%m-%d') ORDER BY day";
            }
            if ($filter == 'year') {
                $select_sql = "DATE_FORMAT(T_OrderDate, '%m') AS month";

                $filter_sql =  ", DATE_FORMAT(T_OrderDate, '%Y-%m')  ORDER BY month";
                $filter_sql_total =  " DATE_FORMAT(T_OrderDate, '%Y-%m')  ORDER BY month";
            }
            // SELECT COUNT(T_OrderID) AS total,
            // T_OrderStatus AS status,
            // DATE_FORMAT(T_OrderDate, '%d') AS day
            // from one_mitra.t_order
            // WHERE T_OrderIsActive = 'Y'
            // AND YEAR(T_OrderDate) = YEAR(CURDATE())
            // AND T_OrderM_CompanyID = 1222
            // GROUP BY T_OrderStatus
            // ,DATE_FORMAT(T_OrderDate, '%Y-%m-%d') ORDER BY day

            $sql = "SELECT COUNT(T_OrderID) AS total,
                    T_OrderStatus AS status,
                    $select_sql
                    FROM one_mitra.t_order 
                    WHERE T_OrderIsActive = 'Y'
                    AND YEAR(T_OrderDate) = YEAR(CURDATE())
                    $filter_sql2
                    AND T_OrderM_CompanyID = ?
                    GROUP BY T_OrderStatus
                    $filter_sql";
            $query = $this->db->query($sql, [$company_id]);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $data = $query->result_array();
            $sql_total = "SELECT COUNT(T_OrderID) AS total,
                    T_OrderStatus AS status,
                    $select_sql
                    FROM one_mitra.t_order 
                    WHERE T_OrderIsActive = 'Y'
                    AND YEAR(T_OrderDate) = YEAR(CURDATE())
                    $filter_sql2
                    AND T_OrderM_CompanyID = ?
                    GROUP BY 
                    $filter_sql_total";
            $query_total = $this->db->query($sql_total, [$company_id]);
            if (!$query_total) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $data_total = $query_total->result_array();
            $result = array(
                "N" => [],
                "S" => [],
                "Y" => [],
                "T" => [],
                "last_query" => $this->db->last_query()
            );
            // N = New, S= Send, P= Parsial, D=Done,
            if ($filter == 'month') {
                for ($i = 0; $i < count($data); $i++) {
                    if ($data[$i]['status'] == 'N') {
                        $result['N'][] = "{$data[$i]['day']}|{$data[$i]['total']}";
                    }
                    if ($data[$i]['status'] == 'S') {
                        $result['S'][] = "{$data[$i]['day']}|{$data[$i]['total']}";
                    }
                    if ($data[$i]['status'] == 'Y') {
                        $result['Y'][] = "{$data[$i]['day']}|{$data[$i]['total']}";
                    }
                }
                for ($i = 0; $i < count($data_total); $i++) {
                    $result['T'][] = "{$data_total[$i]['day']}|{$data_total[$i]['total']}";
                }
            }
            if ($filter == 'year') {
                for ($i = 0; $i < count($data); $i++) {
                    if ($data[$i]['status'] == 'N') {
                        $result['N'][] = "{$data[$i]['month']}|{$data[$i]['total']}";
                    }
                    if ($data[$i]['status'] == 'S') {
                        $result['S'][] = "{$data[$i]['month']}|{$data[$i]['total']}";
                    }
                    if ($data[$i]['status'] == 'Y') {
                        $result['Y'][] = "{$data[$i]['month']}|{$data[$i]['total']}";
                    }
                }
                for ($i = 0; $i < count($data_total); $i++) {
                    $result['T'][] = "{$data_total[$i]['month']}|{$data_total[$i]['total']}";
                }
            }
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function getdelivery()
    {
        try {
            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            // print_r($prm);



            $companyID = $prm['company_id'];


            $sql = "SELECT 
                T_OrderDeliveryID AS id,
               DATE_FORMAT(T_OrderDeliveryDate, '%d/%m/%Y')  AS date,
                T_OrderDeliveryNumber AS order_number,
                M_UserUsername AS pic,
                T_DeliveryTypeName AS type,
                T_OrderDeliveryStatus AS status,
                M_BranchName AS destination
                FROM one_mitra.t_orderdelivery
                JOIN one_mitra.m_user
                ON T_OrderDeliveryStaffID = M_UserID
                AND M_UserIsActive = 'Y'
                JOIN one_mitra.t_deliverytype
                ON T_OrderDeliveryT_DeliverytypeID = T_DeliveryTypeID
                AND T_DeliveryTypeIsActive = 'Y'
                JOIN m_branch
                ON T_OrderDeliveryDestination = M_BranchID
                AND M_BranchIsActive = 'Y'
                WHERE 
                T_OrderDeliveryIsActive = 'Y' AND
                T_OrderDeliveryM_CompanyID = ?
                AND T_OrderDeliveryStatus IN ('S', 'P')
                ORDER BY T_OrderDeliveryDate DESC
               ";
            $query = $this->db->query($sql, [$companyID]);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $search = $query->result_array();
            // print_r($search);

            $this->sys_ok($search);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}