<?php

class Adjustmentreason extends MY_Controller
{
    var $db_inventory;
    var $db_inventory_log;
    function __construct()
    {
        parent::__construct();
        $this->db_inventory = $this->load->database("inventory", true);
        $this->db_inventory_log = $this->load->database('inventory_log', true);
    }

    function index()
    {
        echo "Api: Training Playground";
        echo "<br>";
        $cek = $this->db_inventory->query("select database() as cunrrent_db")->result();

    }

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

            $this->db_inventory->trans_begin();
            $param = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
    
            $sql_data = "INSERT INTO adjustmentreason(
                AdjustmentReasonDescription, 
                AdjustmentReasonCreated, 
                AdjustmentReasonLastUpdated, 
                AdjustmentReasonUserID) 
                VALUES (?, NOW(), NOW(), ?)";
        
            $qry = $this->db_inventory->query($sql_data, [
                $param["AdjustmentReasonDescription"],
                $userid
            ]);

            if(!$qry){
                $this->db_inventory->trans_rollback();
                $this->sys_error_db("Adjustmentreason add", $this->db_inventory);
                exit;
            }

            $insert_id = $this->db_inventory->insert_id();

            $sql_json_before = "SELECT *
            FROM adjustmentreason
            WHERE AdjustmentReasonIsActive = 'Y'
            AND AdjustmentReasonID = ?";

            $qry_json_before = $this->db_inventory->query($sql_json_before, [
                $insert_id
            ]);

            if (!$qry_json_before) {
                $this->db->trans_rollback();
                $this->sys_error_db("adjustmentreason select json, $this->db_inventory");
                exit;
            }

            $data_by_id = $qry_json_before->row();

            $json_after_log = json_encode($data_by_id);

            $sql_insert_log = "INSERT INTO adjustmentreason_log(
                AdjustmentReasonLogStatus,
                AdjustmentReasonLogJSONBefore,
                AdjustmentReasonLogJSONAfter,
                AdjustmentReasonLogAdjustmentReasonID,
                AdjustmentReasonLogUserID,
                AdjustmentReasonLogCreated
            ) VALUES('ADD',NULL,?,?,?,NOW())";

            $qry_insert_log = $this->db_inventory_log->query($sql_insert_log, [
                $json_after_log,
                $insert_id,
                $userid
            ]);

            if (!$qry_insert_log) {
                $this->db->trans_rollback();
                $this->sys_error_db("insert log error, $this->db_inventory");
                exit;
            }

            $this->db_inventory->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("id" => 0)
            );

            $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;
            $sql_data = "";
            $sql_filter = "";
            $search = "";
            if(isset($param["search"])) {
                $search = trim($param["search"]);
                if ($search != "") {
                    $search = "%" . $param["search"] . "%";
                }else{
                    $search = "%%";
                }
            }

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

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

            $sql_filter = "SELECT COUNT(DISTINCT AdjustmentReasonID, AdjustmentReasonIsActive)
            AS total
            FROM adjustmentreason
            WHERE AdjustmentReasonDescription LIKE ? AND AdjustmentReasonIsActive = 'Y'";

            $qry_filter = $this->db_inventory->query($sql_filter, [$search]);

            $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("Adjustmentreason select count", $this->db_inventory);
                exit;
            }

            $sql_data = "SELECT DISTINCT
            AdjustmentReasonID AS id,
            AdjustmentReasonDescription
            FROM adjustmentreason 
            WHERE AdjustmentReasonDescription LIKE ? AND AdjustmentReasonIsActive = 'Y'  $q_sort
            LIMIT ? offset ?";

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

            if($qry_data) {
                $rows = $qry_data->result_array();
            }else{
                $this->sys_error_db("Adjustmentreason select", $this->db_inventory);
                exit;
            }

            $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 update()
    {
        try {
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $this->db_inventory->trans_begin();
            $param = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $id = $param["id"];

            $sql_data = "UPDATE adjustmentreason
            SET AdjustmentReasonDescription = ?,
            AdjustmentReasonLastUpdated = NOW(),
            AdjustmentReasonUserID = ?
            WHERE AdjustmentReasonID = ?";

            $qry_data = $this->db_inventory->query($sql_data, [
                $param["AdjustmentReasonDescription"],
                $userid,
                $id
            ]);

            if(!$qry_data){
                $this->db_inventory->trans_rollback();
                $this->sys_error_db("Adjustmentreason update", $this->db_inventory);
                exit;
            }

            // json before
            $sql_json_before = "SELECT *
            FROM adjustmentreason
            WHERE AdjustmentReasonIsActive = 'Y'
            AND AdjustmentReasonID = ?";

            $qry_json_before = $this->db_inventory->query($sql_json_before, [
                $id
            ]);

            if (!$qry_json_before) {
                $this->db->trans_rollback();
                $this->sys_error_db("adjustmentreason select json before, $this->db_inventory");
                exit;
            }

            $data_before_by_id = $qry_json_before->row();

            $json_before = json_encode($data_before_by_id);
            
            // print_r($json_before);

            // json after
            $sql_json_after = "SELECT *
            FROM adjustmentreason
            WHERE AdjustmentReasonIsActive = 'Y'
            AND AdjustmentReasonID = ?";

            $qry_json_after = $this->db_inventory->query($sql_json_after, [
                $id
            ]);

            if (!$qry_json_after) {
                $this->db->trans_rollback();
                $this->sys_error_db("adjustmentreason select json after, $this->db_inventory ");
                exit;
            } 

            $data_after_by_id = $qry_json_after->row();

            $json_after = json_encode($data_after_by_id);

            // print_r($json_after);

            $sql_insert_log = "INSERT INTO adjustmentreason_log(
                AdjustmentReasonLogStatus,
                AdjustmentReasonLogJSONBefore,
                AdjustmentReasonLogJSONAfter,
                AdjustmentReasonLogAdjustmentReasonID,
                AdjustmentReasonLogUserID,
                AdjustmentReasonLogCreated
            ) VALUES('EDIT',?,?,?,?,NOW())";

            $qry_insert_log = $this->db_inventory_log->query($sql_insert_log, [
                $json_before,
                $json_after,
                $id,
                $userid
            ]);

            if (!$qry_insert_log) {
                $this->db->trans_rollback();
                $this->sys_error_db("update log error, $this->db_inventory");
                exit;
            }

            $this->db_inventory->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("xid" => 0)
            );

            $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_inventory->trans_begin();
            $param = $this->sys_input;
            $id = $param["id"];
            $userid = $this->sys_user['M_UserID'];

            $sql_data = "update adjustmentreason
            set AdjustmentReasonIsActive = 'N',
            AdjustmentReasonLastUpdated = now(),
            AdjustmentReasonUserID = ?
            where AdjustmentReasonID = ?";

            $qry_data = $this->db_inventory->query($sql_data, [
                $userid,
                $id
            ]);

            if(!$qry_data){
                $this->db_inventory->trans_commit();
                $this->sys_error_db("Adjustmentreason delete", $this->db_inventory);
                exit;
            }

            // json before
            $sql_json_before = "SELECT *
            FROM adjustmentreason
            WHERE AdjustmentReasonIsActive = 'N'
            AND AdjustmentReasonID = ?";

            $qry_json_before = $this->db_inventory->query($sql_json_before, [
                $id
            ]);

            if (!$qry_json_before) {
                $this->db->trans_rollback();
                $this->sys_error_db("adjustmentreason select json, $this->db_inventory");
                exit;
            } 

            $data_before_by_id = $qry_json_before->row();

            $json_before = json_encode($data_before_by_id);

            // print_r($json_before);

            $sql_insert_log = "INSERT INTO adjustmentreason_log(
                AdjustmentReasonLogStatus,
                AdjustmentReasonLogJSONBefore,
                AdjustmentReasonLogJSONAfter,
                AdjustmentReasonLogAdjustmentReasonID,
                AdjustmentReasonLogUserID,
                AdjustmentReasonLogCreated
            ) VALUES('DELETE',NULL,?,?,?,NOW())";

            $qry_insert_log = $this->db_inventory_log->query($sql_insert_log, [
                $json_before,
                $id,
                $userid
            ]);

            if (!$qry_insert_log) {
                $this->db->trans_rollback();
                $this->sys_error_db("delete log error, $this->db_inventory");
                exit;
            }
            
            $this->db_inventory->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("xid" => 0)
            );

            $this->sys_ok($result);

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