BE_CPONE/application/controllers/mockup/masterdata/one-md-ticket-dispenser/Ticketbooth.php

<?php
class Ticketbooth extends MY_Controller
{
    var $db_antrione;
    function __construct()
    {
        parent::__construct();
        $this->db_antrione = $this->load->database("antrione", true);
    }

    function index()
    {
        $sql_data = "SELECT DISTINCT ticketBoothID as id,
        ticketBoothCode as code,
        ticketBoothName as name,
        ticketBoothPriority as priority
        FROM ticket_booth
        WHERE ticketBoothIsActive = 'Y'";

        $qry = $this->db_antrione->query($sql_data);
        if($qry) {
            $rows = $qry->result_array();
        }else{
            $this->sys_error_db("ticketbooth select error", $this->db_antrione);
            exit;
        }

        $result = array(
            "total" => count($rows),
            "records" => $rows
        );
        $this->sys_ok($result);
        exit;
    }

    function save()
    {
        try {
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $this->db_antrione->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user["M_UserID"];
            $code = "";
            if (isset($prm['code'])) {
                $code = trim($prm["code"]);
            }

            $name = "";
            if (isset($prm['name'])) {
                $name = trim($prm["name"]);
            }

            $priority = 0;
            if (isset($prm['priority'])) {
                if (is_numeric($prm["priority"])) {
                    $priority = trim($prm["priority"]);
                }
            }

            $sql_data = "INSERT INTO ticket_booth(
                ticketBoothCode,
                ticketBoothName,
                ticketBoothPriority,
                ticketBoothUserID,
                ticketBoothCreated,
                ticketBoothLastUpdated)
                VALUES (?, ?, ?, ?, NOW(), NOW())";

            $qry_data = $this->db_antrione->query($sql_data, [
                $code,
                $name,
                $priority,
                $userid
            ]);
            $last_qry = $this->db_antrione->last_query();
            // echo $last_qry;

            if(!$qry_data) {
                $this->db_antrione->trans_rollback();
                $error = array(
                    "message" => $this->db_antrione->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error, $this->db_antrione);
                exit;
            }

            $this->db_antrione->trans_commit();
            $result = array(
                "affected_rows" => $this->db_antrione->affected_rows(),
                "inserted_id" => $this->db_antrione->insert_id(),
                // "sql" => $last_qry,
            );
            $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;
            }

            $prm = $this->sys_input;
            // print_r($prm);
            $search = "";
            if(isset($prm["search"])) {
                $search = trim($prm["search"]);
                if ($search != ""){
                    $search = "%" . $prm["search"] . "%";
                }else{
                    $search = "%%";
                }
            }

            $sortBy = $prm["sortBy"];
            $sortStatus = $prm["sortStatus"];
            if($sortBy){
                $q_sort = "ORDER BY ".$sortBy." ".$sortStatus;
            }

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

            $sql_filter = "SELECT COUNT(DISTINCT ticketBoothID,
            ticketBoothCode,
            ticketBoothName,
            ticketBoothPriority,
            ticketBoothIsActive) as total
            FROM ticket_booth
            WHERE ticketBoothName like ? 
            AND ticketBoothIsActive = 'Y'";

            $qry_filter = $this->db_antrione->query($sql_filter, [$search]);
            // $last_qry = $this->db->last_query();
            // print_r($last_qry);

            $tot_count = 0;
            $tot_page = 0;
            if($qry_filter) {
                $tot_count = $qry_filter->result_array()[0]["total"];
                $tot_page = ceil($tot_count/$number_limit);
            }else{
                $this->sys_error_db("ticket_booth select count", $this->db_antrione);
                exit;
            }

            $sql_data = "SELECT DISTINCT ticketBoothID,
            ticketBoothCode,
            ticketBoothName,
            ticketBoothPriority,
            ticketBoothIsActive
            FROM ticket_booth
            WHERE ticketBoothName like ? 
            AND ticketBoothIsActive = 'Y' $q_sort
            limit ? offset ?";

            $qry_data =$this->db_antrione->query($sql_data, [$search, $number_limit, $number_offset]);

            if($qry_data) {
                $rows = $qry_data->result_array();
            }else{
                $this->sys_error_db("ticket_booth select", $this->db_antrione);
                exit;
            }
            // $last_qry = $this->db->last_query();
            // print_r($last_qry);

            $result = array(
                "total" => $tot_page,
                "total_filter" => $tot_count,
                "records" => $rows
            );
            $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;
            }

            $this->db_antrione->trans_begin();
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $code = "";
            if (isset($prm['code'])) {
                $code = trim($prm["code"]);
            }
            $name = "";
            if (isset($prm['name'])) {
                $name = trim($prm["name"]);
            }
            $priority = 0;
            if (isset($prm['priority'])) {
                if (is_numeric($prm["priority"])) {
                    $priority = trim($prm["priority"]);
                }
            }
            $id = "";
            if (isset($prm['id'])) {
                if (is_numeric($prm["id"])) {
                    $id = trim($prm["id"]);
                }
            }
            // print_r($prm);
            
            $sql = "UPDATE ticket_booth
            SET ticketBoothCode = ?,
            ticketBoothName = ?,
            ticketBoothPriority = ?,
            ticketBoothUserID = ?,
            ticketBoothLastUpdated = NOW()
            WHERE ticketBoothID = ?";

            $qry = $this->db_antrione->query($sql, [$code, $name, $priority, $userid, $id]);
            $last_qry = $this->db_antrione->last_query();
            if(!$qry) {
                $this->db_antrione->trans_rollback();
                $error = array(
                    "message" => $this->db_antrione->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error, $this->db_antrione);
                exit;
            }
            // print_r($last_qry);

            $this->db_antrione->trans_commit();
            $result = array(
                "total" => 1,
                "affected_rows" => $this->db_antrione->affected_rows()
            );
            $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;
            }

            $this->db_antrione->trans_begin();
            $prm = $this->sys_input;

            $id = "";
            if (isset($prm['id'])) {
                if (is_numeric($prm["id"])) {
                    $id = trim($prm["id"]);
                }
            }

            $sql = "UPDATE ticket_booth
            SET ticketBoothIsActive = 'N',
            ticketBoothLastUpdated = NOW()
            WHERE ticketBoothID = ?";

            $qry = $this->db_antrione->query($sql, [
                $id
            ]);
            $last_qry = $this->db_antrione->last_query();
            if(!$qry){
                $this->db_antrione->trans_rollback();
                $error = array(
                    "message" => $this->db_antrione->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error, $this->db_antrione);
                exit;
            }
            // print_r($last_qry);

            $this->db_antrione->trans_commit();
            $result = array(
                "affected_rows" => $this->db_antrione->affected_rows()
            );
            $this->sys_ok($result);

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