<?php
class Itemgroup extends MY_Controller
{
    var $db;
    var $log_inventory;
    function __construct()
    {
        parent::__construct();
        $this->db = $this->load->database("inventory", true);
        $this->log_inventory = $this->load->database("inventory_log", true);
    }

    function index()
    {
        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 
                    itemgroup  
                    where 
                        ItemGroupIsActive = 'Y' 
                    limit 10 offset ?";
            $qry = $this->db->query($sql, [$start_offset]);
            $last_qry = $this->db->last_query();

            $count = "select count(ItemGroupID) as total from 
                        itemgroup 
                        where 
                            ItemGroupIsActive = '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 add()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $param = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $name = "";
            if (isset($param['name'])) {
                $name = trim($param["name"]);
            }
            $reagen = "N";
            if (isset($param['reagen'])) {
                $reagen = trim($param["reagen"]);
            }
            $id = "";
            if (isset($param['id'])) {
                if (is_numeric($param["id"])) {
                    $id = trim($param["id"]);
                }
            }
            if ($id == "" && $name == "") {
                $error = array(
                    "message" => "name is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            $sql = "insert into itemgroup
                    (ItemGroupCode,
                    ItemGroupName,
                    ItemGroupIsReagen,
                    ItemGroupUserID,
                    ItemGroupCreated,
                    ItemGroupLastUpdated
                    )
                    values 
                    ((select fn_numbering('GI')), 
                    ?, 
                    ?,?,NOW(), NOW())";
            $qry = $this->db->query($sql, [$name, $reagen, $id]);
            $last_qry = $this->db->last_query();
            //log
            $insert_id = $this->db->insert_id();
            // print_r($insert_id);
            $sql_json_after = "select * from 
                                itemgroup 
                                where 
                                    ItemGroupID = ?";
            $qry_json_after = $this->db->query($sql_json_after, [$insert_id]);
            $json_after = json_encode($qry_json_after->row());
            $sql_insert_log = "insert into itemgroup_log 
                                values(
                                    null, 
                                    ?, 
                                    'ADD',
                                    null,
                                    ?,
                                    ?, 
                                    now())";
            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$insert_id, $json_after, $userid]);
            if (!$qry_insert_log) {
                $this->log_inventory->trans_rollback();
                $error = array(
                    "message" => $this->log_inventory->error()["message"],

                );
                $this->sys_error_db($error);
                exit;
            }
            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);
        }
    }
    function edit()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $userid = $this->sys_user['M_UserID'];
            $param = $this->sys_input;
            $name = "";
            if (isset($param['name'])) {
                $name = trim($param["name"]);
            }
            $reagen = "";
            if (isset($param['reagen'])) {
                $reagen = trim($param["reagen"]);
            }
            $id = "";
            if (isset($param['id'])) {
                if (is_numeric($param["id"])) {
                    $id = trim($param["id"]);
                }
            }
            if ($id == "" or $name == "" && $reagen == "") {
                $error = array(
                    "message" => "name & reagen is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            $sql_json_before = "select * from 
                                itemgroup 
                                where 
                                    ItemGroupID = ?";
            $qry_json_before = $this->db->query($sql_json_before, [$id]);
            $json_before = json_encode($qry_json_before->row());
            $sql = "update itemgroup 
                    set ItemGroupName = ?, 
                    ItemGroupLastUpdated =now(),
                    ItemGroupIsReagen =? 
                    where ItemGroupID = ?";
            $qry = $this->db->query($sql, [$name, $reagen, $id]);
            $last_qry = $this->db->last_query();
            $sql_json_after = "select * from 
                                itemgroup 
                                where 
                                    ItemGroupID = ?";
            $qry_json_after = $this->db->query($sql_json_after, [$id]);
            $json_after = json_encode($qry_json_after->row());
            $sql_insert_log = "insert into itemgroup_log 
                                values(
                                    null, 
                                    ?, 
                                    'EDIT',
                                    ?,
                                    ?,
                                    ?, 
                                    now())";
            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$id, $json_before, $json_after, $userid]);
            if (!$qry_insert_log) {
                $this->log_inventory->trans_rollback();
                $error = array(
                    "message" => $this->log_inventory->error()["message"],

                );
                $this->sys_error_db($error);
                exit;
            }
            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;
            $userid = $this->sys_user['M_UserID'];
            $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 itemgroup 
                    set ItemGroupIsActive= 'N', 
                    ItemGroupLastUpdated =now() 
                    where ItemGroupID = ?
                    ";
            $qry = $this->db->query($sql, [$id]);
            $last_qry = $this->db->last_query();
            $sql_json_after = "select * from 
                                itemgroup 
                                where 
                                    ItemGroupID = ?";
            $qry_json_after = $this->db->query($sql_json_after, [$id]);
            $json_after = json_encode($qry_json_after->row());
            $sql_insert_log = "insert into itemgroup_log 
                                values(
                                    null, 
                                    ?, 
                                    'DELETE',
                                    null,
                                    ?,
                                    ?, 
                                    now())";
            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$id, $json_after, $userid]);
            if (!$qry_insert_log) {
                $this->log_inventory->trans_rollback();
                $error = array(
                    "message" => $this->log_inventory->error()["message"],

                );
                $this->sys_error_db($error);
                exit;
            }
            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 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 = "ItemGroupID";
            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 
                    itemgroup  
                    where 
                        ItemGroupIsActive = 'Y' 
                        and (ItemGroupName like ? or ItemGroupCode like ?) 
                    $sort limit 10 offset ?";
            $qry = $this->db->query($sql, [$name, $name, $start_offset]);
            $last_qry = $this->db->last_query();

            $count = "select count(ItemGroupID) as total from 
                        itemgroup 
                        where 
                            ItemGroupIsActive = 'Y' 
                            and (ItemGroupName like ? or ItemGroupCode like ?) ";
            $qry_total_filter = $this->db->query($count, [$name, $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);
        }
    }
}