<?php
class Location extends MY_Controller
{
    function __construct()
    {
        parent::__construct();
    }
    function index()
    {
        //SELECT * FROM `m_location` 
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $param = $this->sys_input;
            $page = $param["page"];
            $ROW_PER_PAGE = 10;
            $start_offset = 0;
            if (isset($param["page"])) {
                if (
                    is_numeric($param["page"]) && $param["page"] > 0
                ) {
                    $start_offset = ($page - 1) * $ROW_PER_PAGE;
                }
            }

            $sql = "SELECT * FROM `m_location`  where M_LocationIsActive = 'Y' limit 10 offset ?";
            $qry = $this->db->query($sql, [$start_offset]);
            $last_qry = $this->db->last_query();

            $count = "SELECT count(M_LocationID) as total FROM `m_location`  where M_LocationIsActive = 'Y' ";
            $qry_total_filter = $this->db->query($count);
            $last_qry_total_filter = $this->db->last_query();
            if (!$qry) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error);
                exit;
            }
            if (!$qry_total_filter) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry_total_filter
                );
                $this->sys_error_db($error);
                exit;
            }
            $total_filter = (int)$qry_total_filter->result_array()[0]["total"];

            $total = ceil($total_filter / $ROW_PER_PAGE);
            $rows = $qry->result_array();
            $result = array(
                "total" => $total,
                "total_filter" => $total_filter,
                "records" => $rows,
                "sql" => $last_qry,
                "count" => $last_qry_total_filter
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function get_station()
    {
        //SELECT * FROM `m_location` 
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }


            $sql = "SELECT * FROM `t_samplestation`  where T_SampleStationIsActive = 'Y' ";
            $qry = $this->db->query($sql);
            $last_qry = $this->db->last_query();

            $count = "SELECT count(T_SampleStationID) as total FROM `t_samplestation`  where T_SampleStationIsActive = 'Y' ";
            $qry_total_filter = $this->db->query($count);
            $last_qry_total_filter = $this->db->last_query();
            if (!$qry) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error);
                exit;
            }
            if (!$qry_total_filter) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry_total_filter
                );
                $this->sys_error_db($error);
                exit;
            }
            $total_filter = (int)$qry_total_filter->result_array()[0]["total"];
            $rows = $qry->result_array();
            $result = array(
                "total_filter" => $total_filter,
                "records" => $rows,
                "sql" => $last_qry,
                "count" => $last_qry_total_filter
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function search()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $param = $this->sys_input;
            $page = $param["page"];
            $ROW_PER_PAGE = 10;
            $start_offset = 0;
            $name = "%%";
            if (isset($param['name'])) {
                $name = trim($param["name"]);
                $name = '%' . $param['name'] . '%';
            }
            $order_by = "M_LocationID";
            if (isset($param['order_by'])) {
                $order_by = trim($param["order_by"]);
            }
            $order = "asc";
            if (isset($param['order'])) {
                $order = trim($param["order"]);
            }
            $sort = "order by " . $order_by . " " . $order;
            if (isset($param["page"])) {
                if (
                    is_numeric($param["page"]) && $param["page"] > 0
                ) {
                    $start_offset = ($page - 1) * $ROW_PER_PAGE;
                }
            }

            $sql = "SELECT * FROM `m_location`  
                    join t_samplestation on M_LocationT_SampleStationID = T_SampleStationID 
                        and M_LocationIsActive = 'Y' 
                        and M_LocationName   like ?
                    $sort
            limit 10 offset ?";
            $qry = $this->db->query($sql, [$name, $start_offset]);
            $last_qry = $this->db->last_query();

            $count = "SELECT count(M_LocationID) as total FROM `m_location` 
                        join t_samplestation on M_LocationT_SampleStationID = T_SampleStationID 
                            and M_LocationIsActive = 'Y' 
                            and M_LocationName   like ? 
                        $sort ";
            $qry_total_filter = $this->db->query($count, [$name]);
            $last_qry_total_filter = $this->db->last_query();
            if (!$qry) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error);
                exit;
            }
            if (!$qry_total_filter) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry_total_filter
                );
                $this->sys_error_db($error);
                exit;
            }
            $total_filter = (int)$qry_total_filter->result_array()[0]["total"];

            $total = ceil($total_filter / $ROW_PER_PAGE);
            $rows = $qry->result_array();
            $result = array(
                "total" => $total,
                "total_filter" => $total_filter,
                "records" => $rows,
                "sql" => $last_qry,
                "count" => $last_qry_total_filter
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function edit()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $param = $this->sys_input;

            $name = "";
            if (isset($param['name'])) {
                $name = trim($param["name"]);
            }
            $priority = 0;
            if (isset($param['priority'])) {
                if (is_numeric($param["priority"])) {
                    $priority = trim($param["priority"]);
                }
            }
            $id = "";
            if (isset($param['id'])) {
                if (is_numeric($param["id"])) {
                    $id = trim($param["id"]);
                }
            }
            $stationid = "";
            if (isset($param['stationid'])) {
                if (is_numeric($param["stationid"])) {
                    $stationid = trim($param["stationid"]);
                }
            }
            if ($stationid == "" or $name == "" or $priority == "") {
                $error = array(
                    "message" => "name, stationid, priority is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            $sql = "update m_location
                    set M_LocationT_SampleStationID = ?,
                        M_LocationName = ?,
                        M_LocationPriority = ?,
                        M_LocationLastUpdated = now()
                    where M_LocationID = ?";
            $qry = $this->db->query($sql, [$stationid, $name, $priority, $id]);
            $last_qry = $this->db->last_query();
            if (!$qry) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error);
                exit;
            }
            $last_qry = $this->db->last_query();
            $result = array(
                "affected_rows" => $this->db->affected_rows(),
                "sql" => $last_qry,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function delete()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $param = $this->sys_input;
            $id = "";
            if (isset($param['id'])) {
                if (is_numeric($param["id"])) {
                    $id = trim($param["id"]);
                }
            }
            if ($id == "") {
                $error = array(
                    "message" => "id is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            $sql = "update m_location
                    set M_LocationIsActive = 'N',
                    M_LocationLastUpdated = now()
                    where M_LocationID = ?
                    ";
            $qry = $this->db->query($sql, [$id]);
            $last_qry = $this->db->last_query();
            if (!$qry) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error);
                exit;
            }
            $last_qry = $this->db->last_query();
            $result = array(
                "affected_rows" => $this->db->affected_rows(),
                "sql" => $last_qry,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function add()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $param = $this->sys_input;
            $name = "";
            if (isset($param['name'])) {
                $name = trim($param["name"]);
            }

            $stationid = 1;
            if (isset($param['stationid'])) {
                if (is_numeric($param["stationid"])) {
                    $stationid = trim($param["stationid"]);
                }
            }
            $priority = 0;
            if (isset($param['priority'])) {
                if (is_numeric($param["priority"])) {
                    $priority = trim($param["priority"]);
                }
            }


            if ($stationid == "" or $name == "" or $priority == "") {
                $error = array(
                    "message" => "name, station id, priority is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            $sql = "insert into m_location values(null, ?,?,?,'Y', now(), now());";
            $qry = $this->db->query($sql, [$stationid, $name, $priority]);
            $last_qry = $this->db->last_query();

            if (!$qry) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error);
                exit;
            }

            $result = array(
                "affected_rows" => $this->db->affected_rows(),
                "inserted_id" => $this->db->insert_id(),
                "sql" => $last_qry,
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}