<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class M_inventories extends CI_Model {

    public $table = "at_inventories";
    public $detail = "at_inventdetail";
    public $item = "am_item";
    public $item_category = "am_itemcategory";

    function join_all() {

        /* ===========================declar post by find================================================ */
        $Code = $this->input->post('ItemCode') ? $this->input->post('ItemCode') : '';
        $Desc = $this->input->post('ItemDesc') ? $this->input->post('ItemDesc') : '';

        $BranchCode = $this->input->post('BranchCode') ? $this->input->post('BranchCode') : '';
        $CompanyCode = $this->input->post('CompanyCode') ? $this->input->post('CompanyCode') : '';
        /* ========================================end================================================== */

        /* ======================================scrol view 1-150======================================= */
        $page = ($this->input->post('page')) ? intval($this->input->post('page')) : 1;
        $rows = ($this->input->post('rows')) ? intval($this->input->post('rows')) : 150;

        $beginning = ($page - 1) * $rows;
        $end = $page * $rows;
        /* ============================================================================================== */

        /* ==========================================by session========================================== */
        $sess_company = $this->session->userdata('company');
        $sess_branch = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =============================================================================================== */

        $this->db->select('at_inventories.InventoriesID, at_inventories.BranchCode, am_branch.BranchName, at_inventories.LockStatus,
                           at_inventories.ItemCode, am_item.ItemDesc, am_item.ItemUnit, 
                          (at_inventories.ItemQty - at_inventories.ItemBooks) as ItemQty');
        $this->db->from($this->table);
        $this->db->join('am_item', 'am_item.ItemCode = at_inventories.ItemCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_inventories.BranchCode', 'inner');

        /*
          get data dari  combobox find di view
         */
        if ($BranchCode != "") {
            $this->db->where('at_inventories.BranchCode', $BranchCode);
        } else {
            /*
              inisialisasi untuk loading pertama page branch info
             */
            //----------------kondisi 1 untuk branch-------------------------------------//
            /*
              branch status 0 = HO
              branch status 1 = depo/branch
              jangan lupa sesion data di atas untuk setiap kondisi
              jangan lupa tambahin case when trans status
             */
            $this->db->where('at_inventories.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }

        if ($Code != "") {
            $this->db->like('at_inventories.ItemCode', $Code);
        }
        if ($Desc != "") {
            $this->db->like('am_item.ItemDesc', $Desc);
        }

        $query = $this->db->order_by('at_inventories.ItemCode', 'ASC');
        $query = $this->db->limit($end, $beginning);
        $query = $this->db->get();
        $result = $query->result();
        return $result;
    }

    function join_all_by_branch($BranchCode) {
        $this->db->select('at_inventories.InventoriesID, at_inventories.BranchCode, am_branch.BranchName, 
                           at_inventories.ItemCode, at_inventories.ItemQty, am_item.ItemDesc, am_item.ItemUnit, am_item.ItemCategoryID');
        $this->db->from($this->table);
        $this->db->join('am_item', 'am_item.ItemCode = at_inventories.ItemCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_inventories.BranchCode', 'inner');
        $this->db->where($this->table . '.BranchCode', $BranchCode);
        $this->db->order_by("at_inventories.BranchCode", "ASC");
        $query = $this->db->get();
        $result = $query->result();

        return $result;
    }

    function join_all_by_branch_itemcode($BranchCode) {
        $this->db->select('at_inventories.ItemCode');
        $this->db->from($this->table);
        $this->db->join('am_item', 'am_item.ItemCode = at_inventories.ItemCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_inventories.BranchCode', 'inner');
        $this->db->where($this->table . '.BranchCode', $BranchCode);
        $this->db->order_by("at_inventories.BranchCode", "ASC");
        $query = $this->db->get();
        $result = $query->result();

        return $result;
    }

    function item_edit($id) {
        $this->db->select('at_inventories.InventoriesID, at_inventories.BranchCode, am_branch.BranchName, 
                           at_inventories.ItemCode, at_inventories.ItemQty, am_item.ItemDesc, am_item.ItemUnit, at_inventories.ItemQty, at_inventories.ItemBooks, am_item.ItemCategoryID, am_item.ItemTypeID, am_itemdetail.*');
        $this->db->from($this->table);
        $this->db->join('am_item', 'am_item.ItemCode = at_inventories.ItemCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_inventories.BranchCode', 'inner');
        $this->db->join('am_itemdetail', 'am_itemdetail.ItemCode = at_inventories.ItemCode', 'left');
        $this->db->where($this->table . '.InventoriesID', $id);
        $this->db->order_by("at_inventories.BranchCode", "ASC");
        $query = $this->db->get();

        $result = $query->row();
        return $result;
    }

    //get for do (fahri)
    function get_item_qty($item_code, $branch_code) {
        $this->db->select('ItemQty');
        $this->db->from($this->table);
        $this->db->where('BranchCode', $branch_code);
        $this->db->where('ItemCode', $item_code);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $ItemQty = $row->ItemQty;
            }

            return $ItemQty;
        } else {
            return 0;
        }
    }

    function get_item_qty_book($item_code, $branch_code) {
        $this->db->select('(ItemQty - ItemBooks) as ItemQty');
        $this->db->from($this->table);
        $this->db->where('BranchCode', $branch_code);
        $this->db->where('ItemCode', $item_code);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $ItemQty = $row->ItemQty;
            }

            return $ItemQty;
        } else {
            return 0;
        }
    }

    //end

    function get_inventories($item_code) {
        $branch_code = $this->session->userdata('branch');

        $this->db->select('inv.*, itc.AssetStatus');
        $this->db->from($this->table . ' inv');
        $this->db->join($this->item . ' itm', 'itm.ItemCode = inv.ItemCode', 'INNER');
        $this->db->join($this->item_category . ' itc', 'itc.ItemCategoryID = itm.ItemCategoryID', 'INNER');
        $this->db->where('inv.BranchCode', $branch_code);
        $this->db->where('inv.ItemCode', $item_code);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    function get_invent_detail($item_code) {
        $branch_code = $this->session->userdata('branch');

        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('BranchCode', $branch_code);
        $this->db->where('ItemCode', $item_code);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    function get_invent_detail_for_asset($item_code, $RINumber) {
        $this->db->_protect_identifiers = FALSE;
        $branch_code = $this->session->userdata('branch');

        $this->db->select("*, 'No' as Status");
        $this->db->from($this->detail);
        $this->db->where('BranchCode', $branch_code);
        $this->db->where('ItemCode', $item_code);
        $this->db->where('ReceiptNumber', $RINumber);
        $this->db->where_in('SoldStatus', array(0, 2));
        $this->db->order_by('IventDetailID', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;

        return $query->result();
    }

    function get_item_booked($id) {
        $this->db->select('ItemBooks');
        $this->db->from($this->table);
        $this->db->where('ItemCode', $id);
        $this->db->where('BranchCode', $this->session->userdata('branch'));
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $ItemBooks = $row->ItemBooks;
            }

            return $ItemBooks;
        } else {
            return 0;
        }
    }

    function set_item_qty($id, $qty) {
        $this->db->set('ItemQty', $qty);
        $this->db->where('ItemCode', $id);
        $this->db->where('at_inventories.BranchCode', $this->session->userdata('branch'));
        $this->db->update($this->table);
    }

    function set_item_booked($id, $qty) {
        $this->db->set('ItemBooks', $qty);
        $this->db->where('ItemCode', $id);
        $this->db->where('at_inventories.BranchCode', $this->session->userdata('branch'));
        $this->db->update($this->table);
    }

    function set_item_value($id, $amount) {
        $this->db->set('ItemValue', $amount);
        $this->db->where('ItemCode', $id);
        $this->db->where('at_inventories.BranchCode', $this->session->userdata('branch'));
        $this->db->update($this->table);
    }

    function set_item_average($id, $amount) {
        $this->db->set('ItemAverage', $amount);
        $this->db->where('ItemCode', $id);
        $this->db->where('at_inventories.BranchCode', $this->session->userdata('branch'));
        $this->db->update($this->table);
    }

    function set_item_hpp($id, $amount) {
        $this->db->set('ItemAverage', $amount);
        $this->db->where('ItemCode', $id);
        $this->db->where('at_inventories.BranchCode', $this->session->userdata('branch'));
        $this->db->update($this->table);
    }

    function set_do_no($id, $do, $limit) {
        $this->db->set('DordNumber', $do);
        $this->db->where('ItemCode', $id);
        $this->db->where('at_inventdetail.BranchCode', $this->session->userdata('branch'));
        $this->db->order_by('IventDetailID', 'ASC');
        $this->db->limit($limit);
        $this->db->update($this->detail);
    }

    function set_sold_status($id, $status, $limit) {
        $this->db->set('SoldStatus', $status);
        $this->db->where('ItemCode', $id);
        $this->db->where('at_inventdetail.BranchCode', $this->session->userdata('branch'));
        $this->db->order_by('IventDetailID', 'ASC');
        $this->db->limit($limit);
        $this->db->update($this->detail);
    }

    function insert($data) {
        $this->db->insert($this->table, $data);
    }

    function insert_detail($data) {
        $this->db->insert($this->detail, $data);
    }

    function update($data, $item_code) {
        $branch_code = $this->session->userdata('branch');
        $this->db->update($this->table, $data, array('ItemCode' => $item_code, 'BranchCode' => $branch_code));
    }

    function update_average($data, $item_code, $branch_code) {
        $this->db->update($this->table, $data, array('ItemCode' => $item_code, 'BranchCode' => $branch_code));
    }

    function update_detail($data, $asset_code) {
        $branch_code = $this->session->userdata('branch');
        $this->db->update($this->detail, $data, array('BranchCode' => $branch_code, 'AssetCode' => $asset_code));
    }

    function create_serial_number($item_code) {
        $branch_code = $this->session->userdata('branch');
        $month = date('m');
        $year = date('y');
        $serial_number = $branch_code . "/" . $month . $year . "SN/" . $item_code . "/";

        $this->db->like('ReceiveSN', $serial_number);
        $num = $this->db->get($this->detail)->num_rows() + 1;
        $long = strlen($num);

        if ($long == 1) {
            $number = '00' . $num;
        } elseif ($long == 2) {
            $number = '0' . $num;
        } else {
            $number = $num;
        }

        return $serial_number . $number;
    }

    function get_item_average($id) {
        $this->db->select('ItemAverage');
        $this->db->from($this->table);
        $this->db->where('ItemCode', $id);
        $this->db->where('at_inventories.BranchCode', $this->session->userdata('branch'));
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $ItemAverage = $row->ItemAverage;
            }

            return $ItemAverage;
        } else {
            return 0;
        }
    }

    function get_item_hpp($id) {
        $this->db->select('ItemAverage');
        $this->db->from($this->table);
        $this->db->where('ItemCode', $id);
        $this->db->where('at_inventories.BranchCode', $this->session->userdata('branch'));
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $ItemAverage = $row->ItemAverage;
            }

            return $ItemAverage;
        } else {
            return 0;
        }
    }

    function get_cetgory_name($item_code) {
        $this->db->select('ItemCategoryName');
        $this->db->from($this->item . ' as it');
        $this->db->join($this->item_category . ' as ic', 'ic.ItemCategoryID = it.ItemCategoryID', 'INNER');
        $this->db->where('ItemCode', $item_code);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            $data = $query->row();
            return $data->ItemCategoryName;
        } else {
            return false;
        }
    }

    function create_asset_code($item_code) {
        $branch_code = $this->session->userdata('branch');
        $result = $this->get_cetgory_name($item_code);

        if ($result == false) {
            return false;
        } else {
            $category_name = explode(' ', $result);

            if (count($category_name) > 1) {
                $name_1st = substr($category_name[0], 0, 1);
                $name_2nd = substr($category_name[1], 0, 1);
                $asset_name = $name_1st . $name_2nd;
            } else {
                $asset_name = substr($category_name[0], 0, 2);
            }

            $asset_code = $branch_code . '/' . $asset_name . '-';

            $this->db->_protect_identifiers = false;
            $this->db->select('(CASE 
                                  WHEN MAX(CONVERT(SUBSTR(AssetCode, -4),signed)) IS NULL then 1
                                  ELSE MAX(CONVERT(SUBSTR(AssetCode, -4),signed)) + 1 END) as New_Number');
            $this->db->like('AssetCode', $asset_code);
            $query = $this->db->get($this->detail);
            $this->db->_protect_identifiers = true;

            $asset_no = $query->row()->New_Number;
            $length_no = strlen($asset_no);

            if ($length_no == 1) {
                $number = '000' . $asset_no;
            } elseif ($length_no == 2) {
                $number = '00' . $asset_no;
            } elseif ($length_no == 3) {
                $number = '0' . $asset_no;
            } else {
                $number = $asset_no;
            }

            $asset_code = $asset_code . $number;
            return $asset_code;
        }
    }

    //function lock status bye fahri
    function get_inventori_by_item($id, $branch) {
        $query = $this->db->get_where('at_inventories', array('ItemCode' => $id, 'BranchCode' => $branch));

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }
	
	function get_inventori_detail($id, $branch, $RINumber) {
        $query = $this->db->get_where($this->detail, array('ItemCode' => $id, 'BranchCode' => $branch, 'ReceiptNumber' => $RINumber));

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }
	
    function get_item_inventori_by_branch($ItemCode, $BranchCode) {
        $this->db->_protect_identifiers = false;
        $this->db->select("at_inventories.InventoriesID, at_inventories.BranchCode, at_inventories.ItemCode, at_inventories.ItemBooks,
							at_inventories.ItemQty as Qty, at_inventories.ItemValue as Value, at_inventories.ItemAverage as Hpp, '0' as ControlValue, '0' as ControlQty,
							at_inventories.LockStatus, am_item.ItemDesc as Description, am_item.ItemUnit, 
							am_item.ItemCategoryID");
        $this->db->from($this->table);

        $this->db->join('am_item', 'am_item.ItemCode = at_inventories.ItemCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_inventories.BranchCode', 'inner');

        $this->db->where($this->table . '.BranchCode', $BranchCode);
        $this->db->where_in($this->table . '.ItemCode', $ItemCode);

        $this->db->order_by("at_inventories.BranchCode", "ASC");
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

    function get_inventories_all_pt($item_code) {
        $this->db->select('ItemCode');
        $this->db->from($this->table);
        $this->db->where('ItemCode', $item_code);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }

    //================ DHIDHIK, 29 Mei 2016 ==================================//   
    function get_all_to_print($data_filter) {
        /* ===========================declar post by find================================================ */
        $Code           = $data_filter['txt_item_code'];
        $Desc           = $data_filter['txt_item_desc'];
        $BranchCode     = $data_filter['cmb_branch'];
        $CompanyCode    = $data_filter['cmb_company'];
        /* ========================================end================================================== */

        /* ==========================================by session========================================== */
        $sess_company       = $this->session->userdata('company');
        $sess_branch        = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =============================================================================================== */

        $this->db->select('at_inventories.InventoriesID, at_inventories.BranchCode, am_branch.BranchName, at_inventories.LockStatus,
                           at_inventories.ItemCode, am_item.ItemDesc, am_item.ItemUnit, 
                          (at_inventories.ItemQty - at_inventories.ItemBooks) as ItemQty');
        $this->db->from($this->table);
        $this->db->join('am_item', 'am_item.ItemCode = at_inventories.ItemCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_inventories.BranchCode', 'inner');

        /* get data dari  combobox find di view */
        if ($BranchCode != "") {
            $this->db->where('at_inventories.BranchCode', $BranchCode);
        } else {
            $this->db->where('at_inventories.BranchCode', $sess_branch);
        }

        if ($Code != "") {
            $this->db->like('at_inventories.ItemCode', $Code);
        }
        if ($Desc != "") {
            $this->db->like('am_item.ItemDesc', $Desc);
        }

        $query = $this->db->order_by('at_inventories.ItemCode', 'ASC');
        $query = $this->db->get();
//        echo $this->db->last_query();
        $result = $query->result();
        return $result;
    }

    //================ END DHIDHIK, 29 Mei 2016 ==================================//
}

/* End of file m_inventories.php */
/* Location: ./application/models/m_inventories.php */