<?php

class Samplestation extends MY_Controller
{
    var $db;
    public function index()
    {
        echo "SAMPLE STATION API";
    }
    public function __construct()
    {
        parent::__construct();
        // $this->db = $this->load->database("regional", true);
    }


    function lookupbahan()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $id = $prm['id'];
            $sql = "select T_BahanID as id,
            t_bahan.*
                    from t_bahan 
                    JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID
                    WHERE
                    T_BahanT_SampleStationID = {$id} AND T_BahanIsActive = 'Y'";
            //	echo $sql;
            $query = $this->db->query($sql, $sql_param);
            $rows = $query->result_array();
            if ($rows) {
            }

            $result = array("total" => count($rows), "records" => $rows);
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function lookup()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $search = $prm['search'];
            $all = $prm['all'];
            $limit = '';
            if ($all == 'N') {
                $limit = ' LIMIT 10';
            }
            $sql = "select COUNT(*) as total
                from t_samplestation 
                where 
                T_SampleStationIsActive	 = 'Y'";
            $sql_param = array($search);
            $total = $this->db->query($sql, $sql_param)->row()->total;


            $sql = "select T_SampleStationID as id,
        T_SampleStationCode as code,
        T_SampleStationName as name,
        T_SampleStationIsNonLab as T_TestIsNonLabID,IF(T_SampleStationIsNonLab = '', 'LAB',T_SampleStationIsNonLab) as T_TestIsNonLabName,
        t_samplestation.*
                from t_samplestation 
                where 
                ( 	T_SampleStationName LIKE CONCAT('%','{$search}','%') OR
                T_SampleStationCode LIKE CONCAT('%','{$search}','%') OR
                IF(T_SampleStationIsNonLab = '', 'LAB',T_SampleStationIsNonLab) LIKE CONCAT('%','{$search}','%')
				)AND
                T_SampleStationIsActive = 'Y'
                GROUP BY T_SampleStationID
                ORDER BY T_SampleStationID DESC $limit";
            $sql_param = array($search);
            $query = $this->db->query($sql);
            //echo $this->db->last_query();
            if ($query) {
                $rows = $query->result_array();
            } else {
                $this->sys_error_db("t_samplestation select");
                exit;
            }

            $result = array("total" => $total, "total_filter" => count($rows), "records" => $rows);
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }


    public function addnewsamplestation()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $name = $prm['name'];
            $code = $prm['code'];
            $nonlab = $prm['nonlab'];
            $query = "SELECT COUNT(*) as exist FROM t_samplestation WHERE T_SampleStationIsActive = 'Y' AND T_SampleStationCode = '{$code}'";
            $exist_code =  $this->db->query($query)->row()->exist;
            if ($exist_code == 0) {
                $sql = "insert into t_samplestation(
                T_SampleStationCode,
                T_SampleStationName,	
                T_SampleStationIsNonLab,
                T_SampleStationCreated,	
                T_SampleStationLastUpdated
				) 
				values( ?, ?, ?, now(), now())";
                $query = $this->db->query(
                    $sql,
                    array(
                        $code,
                        $name,
                        $nonlab
                    )
                );
                //echo $query;
                if (!$query) {
                    $this->sys_error_db("t_samplestation insert");
                    exit;
                }

                $result = array("total" => 1, "records" => array("xid" => 0));
                $this->sys_ok($result);
                $last_id = $this->db->insert_id();
            } else {
                $errors = array();
                if ($exist_code != 0) {
                    array_push($errors, array('field' => 'code', 'msg' => 'Kode sudah ada yang pakai dong'));
                }
                $result = array("total" => -1, "errors" => $errors, "records" => 0);
                $this->sys_ok($result);
            }
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function editsamplestation()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $id = $prm['id'];
            $name = $prm['name'];
            $code = $prm['code'];
            $nonlab = $prm['nonlab'];
            $userid = $this->sys_user["M_UserID"];
            $query = "SELECT COUNT(*) as exist FROM t_samplestation WHERE T_SampleStationIsActive = 'Y' AND T_SampleStationCode = '{$code}' AND T_SampleStationID <> {$prm['id']}";
            $exist_code =  $this->db->query($query)->row()->exist;
            if ($exist_code == 0) {
                $sqlcompany = "update t_samplestation SET 
            T_SampleStationCode = ?,
            T_SampleStationName = ?,	
            T_SampleStationIsNonLab = ?,
            T_SampleStationLastUpdated = now()
                where
                T_SampleStationID = ?
                ";
                $querycompany = $this->db->query(
                    $sqlcompany,
                    array(
                        $code,
                        $name,
                        $nonlab,
                        $id
                    )
                );
                //  echo $query;
                if (!$querycompany) {
                    $this->sys_error_db("t_samplestation update");
                    exit;
                }
                $result = array("total" => 1, "records" => array("xid" => $id));
                $this->sys_ok($result);
            } else {
                $errors = array();
                if ($exist_code != 0) {
                    array_push($errors, array('field' => 'code', 'msg' => 'Kode sudah ada yang pakai dong'));
                }
                $result = array("total" => -1, "errors" => $errors, "records" => 0);
                $this->sys_ok($result);
            }
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function addnewbahan()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $samplestationid = $prm['samplestationid'];
            $bahanid = $prm['bahanid'];
            $userid = $this->sys_user["M_UserID"];
            //echo $query;
            $sql = "UPDATE t_bahan SET T_BahanT_SampleStationID = '{$samplestationid}'
                WHERE T_BahanID = '{$prm['bahanid']}'";
            //echo $sql;
            $query = $this->db->query($sql);
            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function addnewconvert()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $instrumentid = $prm['instrumentid'];
            $resultorigin = $prm['resultorigin'];
            $resultconvert = $prm['resultconvert'];
            $userid = $this->sys_user["M_UserID"];
            if ($prm['xid'] == 0) {

                $sql = "insert into m_instrumentconvert(
                M_InstrumentConvertNat_InstrumentID,	
                M_InstrumentConvertResultOrigin,	
                M_InstrumentConvertResultConvert,
                M_InstrumentConvertUserID,
                M_InstrumentConvertCreated,	
                M_InstrumentConvertLastUpdated

             )
             values( ?,?,?,?,now(),now())";
                $query = $this->db->query(
                    $sql,
                    array(
                        $instrumentid,
                        $resultorigin,
                        $resultconvert,
                        $userid
                    )
                );
                if (!$query) {
                    $this->sys_error_db("m_instrumentconvert insert", $this->db);
                    exit;
                }
                $result = array("total" => 1, "records" => array("xid" => 0));
                $this->sys_ok($result);
            } else {

                //echo $query;
                $sql = "UPDATE m_instrumentconvert SET M_InstrumentConvertResultOrigin = '{$resultorigin}',
                 M_InstrumentConvertResultConvert = '{$resultconvert}',
                 M_InstrumentConvertUserID = '{$userid}'  WHERE M_InstrumentConvertID = '{$prm['xid']}'";
                //echo $sql;
                $query = $this->db->query($sql);
                $result = array("total" => 1, "records" => array("xid" => 0));
                $this->sys_ok($result);
            }
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function deleteuser()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;

            $sql = "update m_user SET
            M_UserIsActive = 'N',
            M_UserLastUpdated = now()
            WHERE
            M_UserID = ?

";

            $query = $this->db->query(
                $sql,
                array(
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("m_user delete");
                exit;
            }
            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function confirmmou()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;

            $sql = "update m_mou SET 
        M_MouIsApproved = 'Y',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db->query(
                $sql,
                array(
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            }





            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function verifymou()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];
            $id = $prm['id'];
            $sql = "update m_mou SET 
        M_MouIsVerified = 'Y',
        M_MouVerifyDate = now(),
        M_MouStatus = 'V',
        M_MouVerifyUserID = '{$userid}',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db->query(
                $sql,
                array(
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            }

            $querystatus = "INSERT g_moustatuslog       
            (G_MouStatusLogDate,	 
            G_MouStatusLogM_MouID, 
            G_MouStatusLogStatus, 
            G_MouStatusLogUserID, 
            G_MouStatusLogCreated, 
            G_MouStatusLogLastUpdated)
            VALUES(
            date(now()),
            '{$id}',
            'V',
            '{$userid}',
            now(),
            now())
        ";
            $rows = $this->db->query($querystatus);



            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function unverifymou()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];
            $id = $prm['id'];
            $sql = "update m_mou SET 
        M_MouIsVerified = 'N',
        M_MouStatus = 'UV',
        M_MouVerifyDate = now(),
        M_MouVerifyUserID = '{$userid}',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db->query(
                $sql,
                array(
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            }
            $querystatus = "INSERT g_moustatuslog       
            (G_MouStatusLogDate,	 
            G_MouStatusLogM_MouID, 
            G_MouStatusLogStatus, 
            G_MouStatusLogUserID, 
            G_MouStatusLogCreated, 
            G_MouStatusLogLastUpdated)
            VALUES(
            date(now()),
            '{$id}',
            'UV',
            '{$userid}',
            now(),
            now())
        ";
            $rows = $this->db->query($querystatus);




            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function releasemou()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];
            $id = $prm['id'];
            $sql = "update m_mou SET 
        M_MouIsReleased = 'Y',
        M_MouStatus = 'R',
        M_MouReleaseDate = now(),
        M_MouReleaseUserID = '{$userid}',
        M_MouIsApproved = 'Y',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db->query(
                $sql,
                array(
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            }
            $querystatus = "INSERT g_moustatuslog       
            (G_MouStatusLogDate,	 
            G_MouStatusLogM_MouID, 
            G_MouStatusLogStatus, 
            G_MouStatusLogUserID, 
            G_MouStatusLogCreated, 
            G_MouStatusLogLastUpdated)
            VALUES(
            date(now()),
            '{$id}',
            'R',
            '{$userid}',
            now(),
            now())
        ";
            $rows = $this->db->query($querystatus);




            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function unreleasemou()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];
            $id = $prm['id'];
            $sql = "update m_mou SET 
        M_MouIsReleased = 'N',
        M_MouStatus = 'UR',
        M_MouReleaseDate = now(),
        M_MouReleaseUserID = '{$userid}',
        M_MouIsApproved = 'N',
            M_MouLastUpdated = now()
            WHERE
            M_MouID = ?
            
            ";

            $query = $this->db->query(
                $sql,
                array(
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("m_mou update");
                exit;
            }
            $querystatus = "INSERT g_moustatuslog       
            (G_MouStatusLogDate,	 
            G_MouStatusLogM_MouID, 
            G_MouStatusLogStatus, 
            G_MouStatusLogUserID, 
            G_MouStatusLogCreated, 
            G_MouStatusLogLastUpdated)
            VALUES(
            date(now()),
            '{$id}',
            'UR',
            '{$userid}',
            now(),
            now())
        ";
            $rows = $this->db->query($querystatus);




            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function deletesamplestation()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];
            $sql = "update t_samplestation SET 
        T_SampleStationIsActive = 'N',
        T_SampleStationLastUpdated = now()
				WHERE
				T_SampleStationID = ?
            
				";

            $query = $this->db->query(
                $sql,
                array(
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("t_samplestation delete");
                exit;
            }
            $sql = "update t_bahan SET 
            T_BahanIsActive = 'N',
            T_BahanLastUpdated = now()
                    WHERE
                    T_BahanT_SampleStationID = ?
                
                    ";

            $query = $this->db->query(
                $sql,
                array(
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("t_bahan delete");
                exit;
            }
            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    public function deletebahan()
    {
        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            //# ambil parameter input
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];
            $sql = "update t_bahan SET 
            T_BahanT_SampleStationID = 0,
            T_BahanLastUpdated = now()
                    WHERE
                    T_BahanID = ?
                
                    ";

            $query = $this->db->query(
                $sql,
                array(
                    $prm['id']
                )
            );
            // echo $query;
            if (!$query) {
                $this->sys_error_db("t_bahan delete");
                exit;
            }
            $result = array("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function selectnonlab()
    {

        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $rows['nonlabs'] = array(array("T_TestIsNonLabID" => "", "T_TestIsNonLabName" => "LAB"), array("T_TestIsNonLabID" => "RADIODIAGNOSTIC", "T_TestIsNonLabName" => "RADIODIAGNOSTIC"), array("T_TestIsNonLabID" => "ELEKTROMEDIS", "T_TestIsNonLabName" => "ELEKTROMEDIS"), array("T_TestIsNonLabID" => "OTHERS", "T_TestIsNonLabName" => "OTHERS"));

            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function selectcompanybusiness()
    {

        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query = "	SELECT *, COUNT(M_CompanyID) as used
    FROM (SELECT m_companybusiness.*,M_CompanyID
    FROM
    m_companybusiness
    LEFT JOIN m_company ON M_CompanyBusinessID = M_CompanyM_CompanyBusinessID AND M_CompanyIsActive = 'Y'
    WHERE M_CompanyBusinessIsActive = 'Y') a
    GROUP BY M_CompanyBusinessID
                    ";
            //echo $query;
            $rows['companybusinesss'] = $this->db->query($query)->result_array();


            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function selectcompanylevel()
    {

        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $id = $prm['id'];
            $rows = [];
            $query = "	SELECT *,CONCAT(Nat_CompanyLevelName,' [',M_CompanyName,']') as Nat_CompanyLevelName, COUNT(M_CompanyID) as used
        FROM (SELECT nat_companylevel.*,M_CompanyID,M_CompanyName
        FROM
        nat_companylevel
        LEFT JOIN m_company ON Nat_CompanyLevelNat_CompanyID = M_CompanyID AND M_CompanyIsActive = 'Y'
        WHERE Nat_CompanyLevelIsActive = 'Y') a
        GROUP BY Nat_CompanyLevelID
                        ";
            //echo $query;
            $rows['companylevels'] = $this->db->query($query)->result_array();


            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function selecthierarchy()
    {

        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query = "	SELECT *, COUNT(M_CompanyID) as used
            FROM (SELECT nat_hierarchy.*,M_CompanyID
            FROM
            nat_hierarchy
            LEFT JOIN m_company ON Nat_HierarchyID = M_CompanyNat_HierarchyID AND M_CompanyIsActive = 'Y'
            WHERE Nat_HierarchyIsActive = 'Y') a
            GROUP BY Nat_HierarchyID
                            ";
            //echo $query;
            $rows['hierarchys'] = $this->db->query($query)->result_array();


            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function selectdoctor()
    {

        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query = "	SELECT * 
                        FROM m_doctor 
                        WHERE	
                            M_DoctorIsActive = 'Y'
                    ";
            //echo $query;
            $rows['doctors'] = $this->db->query($query)->result_array();


            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function searchdoctor()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM 
                m_doctor
                WHERE 
                M_DoctorName like ?
                AND M_DoctorIsActive = 'Y'";
        $query = $this->db->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("m_doctor count", $this->db);
            exit;
        }

        $sql = "
        SELECT * FROM(
        SELECT 0 as M_DoctorID, 'Semua Dokter' as M_DoctorName, 'Semua Dokter' as M_DoctorNames
        UNION
        SELECT M_DoctorID, M_DoctorName, CONCAT(IFNULL(M_DoctorPrefix,''),IFNULL(M_DoctorPrefix2,''),' ',M_DoctorName,' ',IFNULL(M_DoctorSufix,''),IFNULL(M_DoctorSufix2,''),IFNULL(M_DoctorSufix3,'')) as M_DoctorNames
        FROM m_doctor
        WHERE M_DoctorIsActive = 'Y') a
            WHERE 
            M_DoctorNames like ?
            ORDER BY M_DoctorName DESC
          ";
        $query = $this->db->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("m_doctor rows", $this->db);
            exit;
        }
    }
    function searchbahan()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM t_bahan
                WHERE 
                T_BahanName like ?
                AND T_BahanIsActive = 'Y'";
        $query = $this->db->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("t_bahan count", $this->db);
            exit;
        }

        $sql = "
        SELECT T_BahanID, T_BahanName
        FROM t_bahan
            WHERE 
            T_BahanName like ?
            AND T_BahanIsActive = 'Y'
            GROUP BY T_BahanID
            ORDER BY T_BahanName ASC
          ";
        $query = $this->db->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("t_bahan rows", $this->db);
            exit;
        }
    }
    function searchtemplate()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM so_template
                WHERE 
                So_TemplateName like ?
                AND So_TemplateIsActive = 'Y'";
        $query = $this->db->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("so_template count", $this->db);
            exit;
        }

        $sql = "
        SELECT So_TemplateID, So_TemplateName
        FROM so_template
            WHERE 
            So_TemplateName like ?
            AND So_TemplateIsActive = 'Y'
            GROUP BY So_TemplateID
            ORDER BY So_TemplateName ASC
          ";
        $query = $this->db->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("so_template rows", $this->db);
            exit;
        }
    }
    function searchcompanylevel()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $max_rst = 12;
        $tot_count = 0;


        $name = $prm['name'];
        $hirarkiid = intval($prm['id']) - 1;

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM nat_companylevel
                WHERE 
                Nat_CompanyLevelName like '%{$name}%'
                AND
                Nat_CompanyLevelNat_HierarchyID = '{$hirarkiid}'
                AND Nat_CompanyLevelIsActive = 'Y'";
        $query = $this->db->query($sql);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("nat_companylevel count", $this->db);
            exit;
        }

        $sql = "
        SELECT * FROM(SELECT *, CONCAT(Nat_CompanyLevelName, ' [',M_CompanyName,']') as Nat_CompanyLevelNames
        FROM nat_companylevel
        LEFT JOIN m_company ON Nat_CompanyLevelNat_CompanyID = M_CompanyID
        WHERE Nat_CompanyLevelIsActive = 'Y') a
            WHERE 
            Nat_CompanyLevelName like '%{$name}%'
            AND
            Nat_CompanyLevelNat_HierarchyID = '{$hirarkiid}'
            AND Nat_CompanyLevelIsActive = 'Y'
            ORDER BY Nat_CompanyLevelName DESC
          ";
        $query = $this->db->query($sql);

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("nat_companylevel rows", $this->db);
            exit;
        }
    }
    function searchcity()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $max_rst = 12;
        $tot_count = 0;

        $q = [
            'search'     => '%'
        ];

        if ($prm['search'] != '') {
            $q['search'] = "%{$prm['search']}%";
        }

        // QUERY TOTAL
        $sql = "SELECT count(*) as total
            FROM m_city
            WHERE 
            M_CityName like ?
            AND M_CityIsActive = 'Y'";
        $query = $this->db->query($sql, $q['search']);
        //echo $query;
        if ($query) {
            $tot_count = $query->result_array()[0]["total"];
        } else {
            $this->sys_error_db("m_city count", $this->db);
            exit;
        }

        $sql = "
        SELECT *
        FROM m_city
        WHERE 
        M_CityName like ?
        AND M_CityIsActive = 'Y'
        ORDER BY M_CityName DESC
      ";
        $query = $this->db->query($sql, array($q['search']));

        if ($query) {
            $rows = $query->result_array();
            //echo $this->db->last_query();
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
            $this->sys_ok($result);
        } else {
            $this->sys_error_db("m_city rows", $this->db);
            exit;
        }
    }
    function getstaff()
    {
        $prm = $this->sys_input;
        $query = "	SELECT * 
                FROM m_staff
                WHERE	
                    M_StaffIsActive = 'Y' AND M_StaffM_PositionID = 2";
        //echo $query;
        $rows = $this->db->query($query, array($prm['id']))->result_array();

        $result = array(
            "total" => count($rows),
            "records" => $rows,
        );
        $this->sys_ok($result);
        exit;
    }
    function getprovince()
    {
        $prm = $this->sys_input;
        $query = "	SELECT * 
                FROM m_province
                WHERE	
                    M_ProvinceIsActive = 'Y'";
        //echo $query;
        $rows = $this->db->query($query, array($prm['id']))->result_array();

        $result = array(
            "total" => count($rows),
            "records" => $rows,
        );
        $this->sys_ok($result);
        exit;
    }
    function getcity()
    {
        $prm = $this->sys_input;
        $query = "	SELECT * 
                FROM m_city
                WHERE	
                    M_CityIsActive = 'Y' AND M_CityM_ProvinceID = ?
            ";
        //echo $query;
        $rows = $this->db->query($query, array($prm['id']))->result_array();

        $result = array(
            "total" => count($rows),
            "records" => $rows,
        );
        $this->sys_ok($result);
        exit;
    }
    function getdistrict()
    {
        $prm = $this->sys_input;
        $query = "	SELECT * 
                FROM m_district 
                WHERE	
                    M_DistrictIsActive = 'Y' AND M_DistrictM_CityID = ?
            ";
        //echo $query;
        $rows = $this->db->query($query, array($prm['id']))->result_array();

        $result = array(
            "total" => count($rows),
            "records" => $rows,
        );
        $this->sys_ok($result);
        exit;
    }

    function getkelurahan()
    {
        $prm = $this->sys_input;
        $query = "	SELECT * 
                FROM m_kelurahan 
                WHERE	
                    M_KelurahanIsActive = 'Y' AND M_KelurahanM_DistrictID = ?
            ";
        //echo $query;
        $rows = $this->db->query($query, array($prm['id']))->result_array();

        $result = array(
            "total" => count($rows),
            "records" => $rows,
        );
        $this->sys_ok($result);
        exit;
    }
    function selectbase()
    {

        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query = "	SELECT 'SPK' as baseid, 'SPK' as basename
            UNION SELECT 'MOU' as baseid, 'MOU' as basename
                    ";
            //echo $query;
            $rows['bases'] = $this->db->query($query)->result_array();


            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function selectomzettype()
    {

        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query = "	SELECT * 
                            FROM m_omzettype 
                            WHERE	
                            M_OmzetTypeIsActive = 'Y'
                        ";
            //echo $query;
            $rows['omzettypes'] = $this->db->query($query)->result_array();


            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function selectmoutype()
    {

        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query = "	SELECT * 
                                FROM m_moutype 
                                WHERE	
                                M_MouTypeIsActive = 'Y'
                            ";
            //echo $query;
            $rows['moutypes'] = $this->db->query($query)->result_array();


            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function selectagingtype()
    {

        try {
            //# cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $rows = [];
            $query = "	SELECT * 
                                    FROM m_agingtype 
                                    WHERE	
                                    M_AgingIsActive = 'Y'
                                ";
            //echo $query;
            $rows['agingtypes'] = $this->db->query($query)->result_array();


            $result = array(
                "total" => count($rows),
                "records" => $rows,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}