<?php
class Expedition extends MY_Controller
{
    var $inventory;
    var $log_inventory;
    // var $staff;
    function __construct()
    {
        parent::__construct();
        $this->inventory = $this->load->database("inventory", true);
        $this->log_inventory = $this->load->database("inventory_log", true);
        // $this->staff = $this->load->database("inventory_log", true);
    }
    function index()
    {
        echo "Api: Expedition";
    }
    function search()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $search = "%%";
            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                $search = '%' . $prm['search'] . '%';
            }
            $order_by = "ExpeditionName";
            if (isset($prm['order_by'])) {
                $order_by = trim($prm["order_by"]);
            }
            $order_type = "asc";
            if (isset($prm['order_type'])) {
                $order_type = trim($prm["order_type"]);
            }
            $order = $order_by . ' ' . $order_type;
            $perpage = 10;
            $offset = ($prm['current_page'] - 1) * $perpage;
            $count = "SELECT count(ExpeditionID) as total
                        FROM expedition 
                        WHERE
                            ExpeditionIsActive = 'Y' 
                            AND ExpeditionName like ?";
            $qry_count = $this->inventory->query($count, array($search));
            $total_count = 0;
            $total_page = 0;
            if ($qry_count) {
                $total_count = $qry_count->row()->total;
                $total_page = ceil($total_count / $perpage);
            } else {
                $this->sys_error_db("Expedition count error", $this->inventory->last_query());
                exit;
            }
            $rows = [];
            $sql = "SELECT ExpeditionID as id,
                        ExpeditionName as name,
                        ExpeditionIsInternal as internal
                    FROM expedition 
                    WHERE
                        ExpeditionIsActive = 'Y' 
                        AND ExpeditionName like ?
                    ORDER BY $order LIMIT ? OFFSET ?";
            $qry = $this->inventory->query($sql, array($search, $perpage, $offset));
            $lst_qryyy = $this->inventory->last_query();
            if ($qry) {
                $rows = $qry->result_array();

                if (count($rows) > 0) {
                    foreach ($rows as $key => $value) {
                        $sql = "SELECT ExpeditionStaffID as id ,
                                ExpeditionStaffName as staffName,
                                ExpeditionStaffMobile as staffMobile,
                                ExpeditionStaffM_StaffID as staffId
                                FROM expeditionstaff 
                                WHERE ExpeditionStaffIsActive = 'Y' 
                                    AND ExpeditionStaffExpeditionID = ?";
                        $qry = $this->inventory->query($sql, array($value['id']));
                        if ($qry) {
                            $rows[$key]['staff'] =
                                $qry->result_array();
                        }
                    }
                }
            } else {
                $this->sys_error_db("Expedition data error", $this->inventory->last_query());
                exit;
            }

            $result = array(
                "total" => $total_page,
                "total_filter" => $total_count,
                "records" => $rows,
                "qry" => $lst_qryyy
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function get_staff()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $count = "SELECT count(M_StaffID) as total  
                        FROM m_staff 
                        WHERE M_StaffIsActive = 'Y' 
                        AND M_StaffIsCourier = 'Y' ;";
            $qry_count = $this->db->query($count);
            $total_count = 0;
            if ($qry_count) {
                $total_count = $qry_count->row()->total;
            } else {
                $this->sys_error_db("Expedition count error", $this->db->last_query());
                exit;
            }
            $sql = "SELECT M_StaffID as staffId, 
                    M_StaffName as staffName,
                    M_StaffHP as staffMobile  
                    FROM m_staff 
                    WHERE M_StaffIsActive = 'Y' 
                        AND M_StaffIsCourier = 'Y' ORDER BY M_StaffName asc ";
            $qry = $this->db->query($sql);
            $staff = $qry->result_array();
            $result = array(
                "total_filter" => $total_count,
                "records" => $staff,
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function add()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $name = "";
            if (isset($prm['name'])) {
                $name = trim($prm["name"]);
            }
            $isInternal = "";
            if (isset($prm['isInternal'])) {
                $isInternal = trim($prm["isInternal"]);
            }

            $this->inventory->trans_start();
            $this->inventory->trans_strict(FALSE);
            $last_id = 0;
            $staff_log = array();
            $sql = "INSERT INTO expedition
                    (ExpeditionName,
                    ExpeditionIsInternal,
                    ExpeditionIsActive,
                    ExpeditionCreated,
                    ExpeditionLastUpdated,
                    ExpeditionUserID)
                    VALUES
                    (?,
                    ?,
                    'Y',
                    NOW(),
                    NOW(),
                    ?)";
            $qry = $this->inventory->query($sql, array($name, $isInternal, $userid));
            if ($qry) {
                $last_id = $this->inventory->insert_id();
            } else {
                $this->sys_error_db("save expedition error", $this->inventory->last_query());
                exit;
            }
            if (count($prm['staff']) > 0) {
                foreach ($prm['staff'] as $key => $value) {
                    $stafName = trim($value['staffName']);
                    $stafMobile = trim($value['staffMobile']);
                    $staffId = trim($value['staffId']);
                    $sql  = "INSERT INTO expeditionstaff
                            (ExpeditionStaffExpeditionID,
                            ExpeditionStaffName,
                            ExpeditionStaffMobile,
                            ExpeditionStaffM_StaffID,
                            ExpeditionStaffIsActive,
                            ExpeditionStaffUserID,
                            ExpeditionStaffCreated,
                            ExpeditionStaffLastUpdated)
                            VALUES
                            (?,
                            ?,
                            ?,
                            ?,
                            'Y',
                            ?,
                            NOW(),
                            NOW()
                            )";
                    $qry = $this->inventory->query($sql, array($last_id, $stafName, $stafMobile, $staffId, $userid));
                    $insert_id = $this->inventory->insert_id();
                    if (!$qry) {
                        $this->sys_error_db("save expedition error", $this->inventory->last_query());
                        exit;
                    }
                    $sql_json_after = "SELECT * FROM 
                                        expeditionstaff 
                                        WHERE 
                                        ExpeditionStaffID = ?";
                    $qry_json_after = $this->inventory->query($sql_json_after, [$insert_id]);

                    $json_after = $qry_json_after->row_array();

                    array_push($staff_log, $qry_json_after->row_array());

                    if (!$qry_json_after) {
                        $error = array(
                            "message" => $this->inventory->error()["message"],
                            "qry" => $this->inventory->last_query()

                        );
                        $this->sys_error_db($error);
                        exit;
                    }

                    $sql_insert_log = "INSERT INTO expeditionstaff_log 
                                        (ExpeditionStaffLogExpeditionStaffID, 
                                        ExpeditionStaffLogStatus,
                                        ExpeditionStaffLogJSONAfter,
                                        ExpeditionStaffLogUserID ,
                                        ExpeditionStaffLogCreated) 
                                        VALUES(?,'ADD', ?,?, now() )";
                    $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$insert_id, json_encode($json_after), $userid]);

                    if (!$qry_insert_log) {
                        $error = array(
                            "message" => $this->log_inventory->error()["message"],
                            "qry" => $this->log_inventory->last_query()

                        );
                        $this->sys_error_db($error);
                        exit;
                    }
                }
            }
            $sql_json_after = "SELECT * FROM 
                                expedition
                                WHERE ExpeditionID = ?";
            $affected_rows = $this->inventory->affected_rows();
            $qry_json_after = $this->inventory->query($sql_json_after, [$last_id]);
            $json_after = $qry_json_after->row_array();
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "qry" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            $json_after['expeditionstaff'] = $staff_log;
            $sql_insert_log = "INSERT INTO expedition_log
                                (ExpeditionLogStatus, 
                                ExpeditionLogExpeditionID,
                                ExpeditionLogJSONAfter,
                                ExpeditionLogUserID,
                                ExpeditionLogCreated)
                                VALUES('ADD',?, ?,?, NOW() )";
            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$last_id, json_encode($json_after), $userid]);
            if (!$qry_insert_log) {
                $error = array(
                    "msg" => "failed insert expedition log",
                    "message" => $this->log_inventory->error()["message"],
                    "qry" => $this->log_inventory->last_query(),
                    "json_after" => $json_after
                );
                $this->sys_error_db($error);
                exit;
            }
            $this->inventory->trans_complete();

            $result = array(
                "affected_rows" => $affected_rows,
                "inserted_id" => $last_id,

            );
            $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;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $name = "";
            if (isset($prm['name'])) {
                $name = trim($prm["name"]);
            }
            $isInternal = "";
            if (isset($prm['isInternal'])) {
                $isInternal = trim($prm["isInternal"]);
            }
            $id = $prm['id'];
            $sql_json_before = "SELECT * FROM 
                                expedition
                                WHERE ExpeditionID = ?";
            $affected_rows = $this->inventory->affected_rows();
            $qry_json_before = $this->inventory->query($sql_json_before, [$id]);
            if (!$qry_json_before) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            $json_expedition_before = $qry_json_before->row_array();

            $sql_json_before = "SELECT * FROM 
                                        expeditionstaff 
                                        WHERE ExpeditionStaffExpeditionID = ?";
            $qry_json_before = $this->inventory->query($sql_json_before, [$id]);
            if (!$qry_json_before) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            $json_expedition_before['expeditionstaff'] = $qry_json_before->result_array();
            $isInternalBefore = $json_expedition_before['ExpeditionIsInternal'];
            $this->inventory->trans_start();
            $this->inventory->trans_strict(FALSE);
            $sql = "UPDATE expedition SET 
                    ExpeditionName = ? ,
                    ExpeditionIsInternal = ?,
                    ExpeditionLastUpdated = now()
                    WHERE ExpeditionID = ?";
            $qry = $this->inventory->query($sql, [$name, $isInternal, $id]);
            if (!$qry) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            }
            $last_qry = "";
            //ISINTERNAL SAMA DENGAN KONDISI SEBELUMNYA
            if ($isInternalBefore == $isInternal) {
                // APABILA STAFF LEBIH DARI 0
                if (count($prm['staff']) > 0) {
                    $sql =  "UPDATE expeditionstaff SET 
                    ExpeditionStaffIsActive = 'N'
                    WHERE ExpeditionStaffExpeditionID = ?";
                    $qry = $this->inventory->query($sql, [$id]);
                    if (!$qry) {
                        $error = array(
                            "message" => $this->inventory->error()["message"],
                            "sql" => $this->inventory->last_query()
                        );
                        $this->sys_error_db($error);
                        exit;
                    }
                    //PERULANGAN SETIAP STAFF 
                    foreach ($prm['staff'] as $key => $value) {
                        $staffName = trim($value['staffName']);
                        $staffMobile = trim($value['staffMobile']);
                        $staffId = trim($value['id']);
                        $mStaffId = trim($value['staffId']);
                        //APABILA STAFFID YANG AKAN DI UBAH LEBIH DARI 0
                        if (intval($staffId) > 0) {
                            $sql_before = "SELECT * FROM 
                                            expeditionstaff 
                                            where ExpeditionStaffID = ?";

                            $qry_before = $this->inventory->query($sql_before, [$staffId]);
                            if (!$qry_before) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "sql" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $json_before = json_encode($qry_before->row_array());
                            $sql = "UPDATE expeditionstaff SET 
                                    ExpeditionStaffName = ?,
                                    ExpeditionStaffMobile =?,
                                    ExpeditionStaffM_StaffID= ?,
                                    ExpeditionStaffIsActive= 'Y',
                                    ExpeditionStaffLastUpdated= now()
                                    WHERE ExpeditionStaffID = ?";
                            $qry = $this->inventory->query($sql, [$staffName, $staffMobile, $mStaffId, $staffId]);
                            $last_qry = $this->inventory->last_query();
                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "sql" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $sql_after = "SELECT * FROM 
                                            expeditionstaff 
                                            where ExpeditionStaffID = ?";
                            $qry_after = $this->inventory->query($sql_after, [$staffId]);
                            if (!$qry_after) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "sql" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $json_after = json_encode($qry_after->row_array());
                            $sql_insert_log = "INSERT INTO expeditionstaff_log 
                                        VALUES(null,?,'EDIT', ?, ?,?, now() )";
                            $qry_insert_log = $this->log_inventory->query($sql_insert_log, [$id, $json_before, $json_after, $userid]);
                            if (!$qry) {
                                $this->sys_error_db("save expedition error", $this->inventory->last_query());
                                exit;
                            }
                            if (!$qry_insert_log) {
                                $error = array(
                                    "message" => $this->log_inventory->error()["message"],
                                    "sql" => $this->log_inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            //APABILA STAFF ID == 0
                        } else {
                            $sql  = "INSERT INTO expeditionstaff 
                            VALUES(null, ?, ?, ?,?,'Y',?, now(), now())";
                            $qry = $this->inventory->query($sql, array($id, $staffName, $staffMobile, $mStaffId, $userid));
                            $last_qry = $this->inventory->last_query();
                            $insert_id = $this->inventory->insert_id();
                            if (!$qry) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "sql" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $sql_after = "SELECT * FROM 
                                            expeditionstaff 
                                            where ExpeditionStaffID = ?";
                            $qry_after = $this->inventory->query($sql_after, [$insert_id]);
                            if (!$qry_after) {
                                $error = array(
                                    "message" => $this->inventory->error()["message"],
                                    "sql" => $this->inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                            $json_after = json_encode($qry_after->row_array());
                            $sql_insert_log = "INSERT INTO expeditionstaff_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) {
                                $error = array(
                                    "message" => $this->log_inventory->error()["message"],
                                    "sql" => $this->log_inventory->last_query()
                                );
                                $this->sys_error_db($error);
                                exit;
                            }
                        }
                    }
                }
                //ISINTERNAL TIDAK SAMA DENGAN KONDISI SEBELUMNYA
            } else {
                if (count($prm['staff']) > 0) {
                    $sql =  "UPDATE expeditionstaff SET 
                    ExpeditionStaffIsActive = 'N'
                    WHERE ExpeditionStaffExpeditionID = ?";
                    $qry = $this->inventory->query($sql, [$id]);
                    if (!$qry) {
                        $error = array(
                            "message" => $this->inventory->error()["message"],
                            "sql" => $this->inventory->last_query()
                        );
                        $this->sys_error_db($error);
                        exit;
                    }
                    foreach ($prm['staff'] as $key => $value) {
                        $staffName = trim($value['staffName']);
                        $staffMobile = trim($value['staffMobile']);
                        $staffId = trim($value['id']);
                        $mStaffId = trim($value['staffId']);
                        $sql  = "INSERT INTO expeditionstaff 
                            VALUES(null, ?, ?, ?,?,'Y',?, now(), now())";
                        $qry = $this->inventory->query($sql, array($id, $staffName, $staffMobile, $mStaffId, $userid));
                        $last_qry = $this->inventory->last_query();
                        $insert_id = $this->inventory->insert_id();
                        if (!$qry) {
                            $error = array(
                                "message" => $this->inventory->error()["message"],
                                "sql" => $this->inventory->last_query()
                            );
                            $this->sys_error_db($error);
                            exit;
                        }
                        $sql_after = "SELECT * FROM 
                                            expeditionstaff 
                                            where ExpeditionStaffID = ?";
                        $qry_after = $this->inventory->query($sql_after, [$insert_id]);
                        if (!$qry_after) {
                            $error = array(
                                "message" => $this->inventory->error()["message"],
                                "sql" => $this->inventory->last_query()

                            );
                            $this->sys_error_db($error);
                            exit;
                        }
                        $json_after = json_encode($qry_after->row_array());
                        $sql_insert_log = "INSERT INTO expeditionstaff_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) {
                            $error = array(
                                "message" => $this->log_inventory->error()["message"],
                                "sql" => $this->log_inventory->last_query()

                            );
                            $this->sys_error_db($error);
                            exit;
                        }
                    }
                }
            }
            $this->inventory->trans_complete();
            $sql_json_after = "SELECT expedition.* FROM 
                                expedition
                                WHERE ExpeditionID = ?";
            $qry_json_after = $this->inventory->query($sql_json_after, [$id]);
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()

                );
                $this->sys_error_db($error);
                exit;
            }
            $json_after = $qry_json_after->row_array();
            $sql_json_after = "SELECT * FROM 
                                        expeditionstaff 
                                        WHERE ExpeditionStaffExpeditionID = ?";
            $qry_json_after = $this->inventory->query($sql_json_after, [$id]);
            $json_after['expeditionstaff'] = $qry_json_after->result_array();
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()

                );
                $this->sys_error_db($error);
                exit;
            }
            $sql_insert_log = "INSERT INTO expedition_log
                                        VALUES(null,'EDIT',?, ?, ?,?, now() )";
            $qry_insert_log = $this->log_inventory->query(
                $sql_insert_log,
                [$id, json_encode($json_expedition_before), json_encode($json_after), $userid]
            );
            if (!$qry_insert_log) {
                $error = array(
                    "message" => $this->log_inventory->error()["message"],
                    "sql" => $this->log_inventory->last_query()

                );
                $this->sys_error_db($error);
                exit;
            }
            $result = array(
                "message" => '',
                "sql_log" => $this->log_inventory->last_query(),
                "sql" => $this->inventory->last_query(),
                "last_qry" => $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;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];
            $id = "";
            if (isset($prm['id'])) {
                $id = trim($prm["id"]);
            }
            if ($id == "" || !$id) {
                $error = array(
                    "message" => "id is mandatory",
                );
                $this->sys_error_db($error);
                exit;
            }
            $this->inventory->trans_start();
            $this->inventory->trans_strict(FALSE);
            $sql = "UPDATE expedition 
                    SET ExpeditionIsActive = 'N',
                    ExpeditionLastUpdated = NOW(),
                    ExpeditionUserID = ?
                    WHERE ExpeditionID = ?";
            $qry = $this->inventory->query($sql, [$userid, $id]);
            if (!$qry) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()
                );
                $this->sys_error_db($error);
                exit;
            } else {
                $sql =  "UPDATE expeditionstaff SET 
                    ExpeditionStaffIsActive = 'N',
                    ExpeditionStaffUserID = ?,
                    ExpeditionStaffLastUpdated = NOW()
                    WHERE ExpeditionStaffExpeditionID = ?";
                $qry = $this->inventory->query($sql, [$userid, $id]);
                if (!$qry) {
                    $error = array(
                        "message" => $this->inventory->error()["message"],
                        "sql" => $this->inventory->last_query()
                    );
                    $this->sys_error_db($error);
                    exit;
                }
            }
            $this->inventory->trans_complete();
            $sql_json_after = "SELECT * FROM 
                                expedition
                                WHERE ExpeditionID = ?";
            $qry_json_after = $this->inventory->query($sql_json_after, [$id]);
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()

                );
                $this->sys_error_db($error);
                exit;
            }
            $json_after = $qry_json_after->row_array();
            $sql_json_after = "SELECT * FROM 
                                        expeditionstaff 
                                        WHERE ExpeditionStaffExpeditionID = ?";
            $qry_json_after = $this->inventory->query($sql_json_after, [$id]);
            $json_after['expeditionstaff'] = $qry_json_after->result_array();
            if (!$qry_json_after) {
                $error = array(
                    "message" => $this->inventory->error()["message"],
                    "sql" => $this->inventory->last_query()

                );
                $this->sys_error_db($error);
                exit;
            }
            $sql_insert_log = "INSERT INTO expedition_log
                                        VALUES(null,'DELETE',?, NULL, ?,?, now() )";
            $qry_insert_log = $this->log_inventory->query(
                $sql_insert_log,
                [$id,  json_encode($json_after), $userid]
            );
            if (!$qry_insert_log) {
                $error = array(
                    "message" => $this->log_inventory->error()["message"],
                    "sql" => $this->log_inventory->last_query()

                );
                $this->sys_error_db($error);
                exit;
            }
            $result = array(
                "message" => '',
                "sql_log" => $this->log_inventory->last_query(),
                "sql" => $this->inventory->last_query(),
                "json_after" => $json_after
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}