<?php

class M_transfer_item extends CI_Model {

    // deklarasi nama table
    public $table = "at_transfer_item";
    public $detail = "at_transitem_detail";
    public $journal = "at_transitem_journal";
    public $branch = "am_branch";
    public $company = "am_company";
    public $employee = "am_employee";

    function get_all() {
        /* =========================lemparan parah dari custom.js untuk searching_data_transaksi===================== */
        $param = $this->input->post('param');
        /* ====================================end=================================================================== */

        /* =================================post dari find di kiri==================================================== */
        $Number = isset($param['txt_IDNumber']) ? $param['txt_IDNumber'] : '';
        $Desc = isset($param['txt_Description']) ? $param['txt_Description'] : '';

        $DateFrom = isset($param['txt_DateFrom']) ? $param['txt_DateFrom'] : '';
        $DateTo = isset($param['txt_DateTo']) ? $param['txt_DateTo'] : '';
        $TransStatus = isset($param['chk_TransStatus']) ? $param['chk_TransStatus'] : '';
        $BranchCode = isset($param['cmb_branch']) ? $param['cmb_branch'] : '';
        /* ============================================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_transfer_item.TransStatus, at_transfer_item.ProdNumber, at_transfer_item.ProdDesc, at_transfer_item.ProdDate, emp.EmployeeName, 
		brnto.BranchName, brnfrom.NamaArea as BranchNameTo");
        $this->db->from($this->table);
        $this->db->join('am_branch as brnto', 'brnto.BranchCode = at_transfer_item.BranchCode', 'inner');
        $this->db->join('at_area as brnfrom', 'brnfrom.IDArea = at_transfer_item.BranchFor', 'inner');
        $this->db->join('am_employee as emp', 'emp.EmployeeCode = at_transfer_item.CreatedBy', 'inner');

        /* ===================================find by number dan desc========================================================= */
        if ($Number != "") {
            $this->db->like('at_transfer_item.ProdNumber', $Number);
        }
        if ($Desc != "") {
            $this->db->like('at_transfer_item.ProdDesc', $Desc);
        }
        /* =============================================end=================================================================== */

        /* ===================================find by branch code========================================================= */
        /*
          get data dari  combobox find di view
         */
        if ($BranchCode != "") {
            $this->db->where('at_transfer_item.BranchCode', $BranchCode);
        } else {
            /*
              inisialisasi untuk loading pertama page branch info
             */
            //----------------kondisi by company-------------------------------------//
            /*
              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_transfer_item.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }
        /* =======================================================end======================================================== */

        /* =====================================================find by date================================================== */
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where('at_transfer_item.ProdDate >=', date("Y-m-d", strtotime($DateFrom)));
                $this->db->where('at_transfer_item.ProdDate <=', date("Y-m-d", strtotime($DateTo)));
            }
        }
        /* ======================================================end=========================================================== */

        /* =====================================================find by status================================================== */
        if ($TransStatus != '') {
            $this->db->where_in('at_transfer_item.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_transfer_item.TransStatus', array(0, 1, 4, 5, 6, 7));
        }
        /* =======================================================end======================================================== */

        /* ==============================================sort tabel asc========================================================== */
        $this->db->order_by($this->table . '.TransStatus', 'ASC');
        $this->db->order_by($this->table . '.ProdNumber', 'ASC');
        /* ===============================end================================================================================= */
        $query = $this->db->limit($end, $beginning);
        $query = $this->db->get();
        return $query->result();
    }

    function get_branch() {
        $company = $this->session->userdata('company');
        $this->db->select('am_branch.BranchCode, am_branch.BranchName');
        $this->db->from('am_branch');
        if ($this->session->userdata('branch_status') == 0) {
            $this->db->where('CompanyCode', $company);
        } else {
            $this->db->where('BranchCode', $this->session->userdata('branch'));
        }

        $this->db->order_by('am_branch.BranchName', 'ASC');
        $query = $this->db->get();

        return $query->result();
    }

    function get_area() {
        $this->db->select('*');
        $this->db->from('at_area');
        $this->db->order_by('NamaArea', 'ASC');
        $query = $this->db->get();

        return $query->result();
    }

    function get_item_price($itemcode, $branch){
        $this->db->select('ItemAverage,ItemCode,BranchCode');
        $this->db->from('at_inventories');
        $this->db->where('BranchCode', $branch);
        $this->db->where('ItemCode', $itemcode);

        $query = $this->db->get();

        return $query->row();
    }

    function insert_inventori($data) {
        $this->db->insert('at_inventories', $data);
    }

    function get_journal_by_number($Number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("at_transitem_journal.*,am_accountgl.AccountNumber,am_accountgl.AccountName");
        $this->db->join("am_accountgl", "am_accountgl.AccountNumber=at_transitem_journal.ProdAccountNumber", "INNER");
        $this->db->order_by('at_transitem_journal.ProdSaldoDebet', 'DESC');
        $query = $this->db->get_where($this->journal, array('ProdNumber' => $Number));
        $this->db->_protect_identifiers = TRUE;

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

    function get_detail_journal($Number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("am_branch.BranchName, am_company.CompanyName");

        $this->db->join("am_branch", "am_branch.BranchCode=at_transfer_item.BranchCode", "INNER");
        $this->db->join("am_company", "am_company.CompanyCode=am_branch.CompanyCode", "INNER");

        $query = $this->db->get_where($this->table, array('ProdNumber' => $Number));
        $this->db->_protect_identifiers = TRUE;

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

    function get_detail_print($id) {
        $this->db->select("at_transfer_item.*, am_branch.BranchName, am_branch.BranchAddress, am_company.CompanyName, em_createby.EmployeeName as nm_createby, em_approve.EmployeeName as nm_approve");
        $this->db->join($this->branch, $this->branch . ".BranchCode = " . $this->table . ".BranchFor", "INNER");
        $this->db->join($this->company, $this->company . ".CompanyCode = " . $this->branch . ".CompanyCode", "INNER");
        $this->db->join($this->employee . " as em_createby", "em_createby.EmployeeCode = " . $this->table . ".CreatedBy", "INNER");
        $this->db->join($this->employee . " as em_approve", "em_approve.EmployeeCode = " . $this->table . ".AppLevel1Code", "INNER");
        $query = $this->db->get_where($this->table, array($this->table . '.ProdNumber' => $id));

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

    function get_detail_item_print($ProdNumber) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("at_transitem_detail.ProdDetailID, at_transitem_detail.ProdNumber, 
							at_transitem_detail.ItemCode, at_transitem_detail.ItemQty, am_item.ItemCategoryID,
							at_transitem_detail.ItemUnit, at_transitem_detail.ItemDesc, 
							at_transfer_item.BranchCode, at_transitem_detail.ItemAmount, '0' as status_remove
						");
        $this->db->join('at_transitem_detail', 'at_transitem_detail.ProdNumber = at_transfer_item.ProdNumber', 'inner');
        $this->db->join('am_item', 'am_item.ItemCode = at_transitem_detail.ItemCode', 'inner');

        $this->db->where('at_transitem_detail.ProdNumber', $ProdNumber);
        $this->db->distinct();
        $query = $this->db->get('at_transfer_item');
        $this->db->_protect_identifiers = TRUE;
        return $query->result_array();
    }

    function get_detail_item($ProdNumber) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("at_transitem_detail.ProdDetailID, at_transitem_detail.ProdNumber, 
							at_transitem_detail.ItemCode, at_transitem_detail.ItemQty, am_item.ItemCategoryID, am_item.ItemTypeID, '0' as ItemType,
							at_transitem_detail.ItemUnit, at_transitem_detail.ItemDesc, 
							at_transfer_item.BranchCode, at_transitem_detail.ItemAmount, '0' as status_remove
						");
        $this->db->join('at_transitem_detail', 'at_transitem_detail.ProdNumber = at_transfer_item.ProdNumber', 'inner');
        $this->db->join('am_item', 'am_item.ItemCode = at_transitem_detail.ItemCode', 'inner');

        $this->db->where('at_transitem_detail.ProdNumber', $ProdNumber);
        $this->db->distinct();
        $query = $this->db->get('at_transfer_item');
        $this->db->_protect_identifiers = TRUE;
        return $query->result();
    }

    function get_detail($id) {
        $query = $this->db->get_where($this->table, array('ProdNumber' => $id));
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

	function reverse_trasaction($transaction_data) {
		$this->db->trans_start();
        if (!empty($transaction_data['delete_inventory']['ItemCode'])) {
            for ($x = 0; $x < count($transaction_data['delete_inventory']['ItemCode']); $x++) {
                $this->delete_inventory($transaction_data['delete_inventory']['ItemCode'][$x], $transaction_data['delete_inventory']['BranchCode'][$x]);
            }
        }
		
		$this->update($transaction_data['update_group']['data'], $transaction_data['update_group']['ProdNumber']);
		
		$this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
	}

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

    function insert_journal_credit($data_journal_credit) {      
        $this->db->insert($this->journal, $data_journal_credit);
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        }
        else {
            //$this->db->trans_commit();
            return 1;
        }       
    }
	
    function update($data, $id) {
        $this->db->update($this->table, $data, array('ProdNumber' => $id));
    }

    function update_detail($update_detail, $id) {
        $this->db->update($this->detail, $update_detail, array('ProdDetailID' => $id));
    }

    function update_inventori($update_inventories, $id) {
        $this->db->update('at_inventories', $update_inventories, array('ItemCode' => $id));
    }

    function update_debet($data_journal_debet, $id, $akun_number) {
        $this->db->update('at_transitem_journal', $data_journal_debet, array('ProdNumber' => $id, 'ProdAccountNumber' => $akun_number));
    }

    function get_inventoris($kode_item, $branch) {
        $this->db->select('at_inventories.*');
        $this->db->from('at_inventories');
        $this->db->where('at_inventories.ItemCode', $kode_item);
        $this->db->where('at_inventories.BranchCode', $branch);
        $query = $this->db->get();

        return $query->row();
    }

    function delete_inventory($id, $branch_code) {
        $this->db->delete("at_inventories", array('ItemCode' => $id, 'BranchCode'=>$branch_code));
    }
	
	function delete_detail_by_id($id) {
        $this->db->delete($this->detail, array('ProdDetailID' => $id));
    }

    function delete_detail_jurnal_by_id($prod_number) {
        $this->db->delete($this->journal, array('ProdNumber' => $prod_number));
    }

    function get_detail_by_id($id) {
        $this->db->select('ProdNumber');
        $this->db->from('at_transitem_detail');
        $this->db->where('ProdDetailID', $id);
        $query = $this->db->get();

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

            return $ProdNumber;
        } else {
            return FALSE;
        }
    }

    function update_trans_status($id, $data) {
        $this->db->where('ProdNumber', $id);
        $this->db->update('at_transfer_item', $data);
    }

    function search_not_in_production() {
        $branch = $this->input->post('branch');
        $item_catcode = isset($_POST['item_categori']) ? mysql_real_escape_string($_POST['item_categori']) : '';
        $item_code = isset($_POST['item_code']) ? mysql_real_escape_string($_POST['item_code']) : '';
        $item_desc = isset($_POST['item_desc']) ? mysql_real_escape_string($_POST['item_desc']) : '';
        //$sess_branch    = $this->session->userdata('branch');

        $not_in = "SELECT ItemCode, ItemDesc, ItemUnit FROM am_item 
					WHERE ItemCode NOT IN (SELECT ItemCode FROM at_inventories WHERE BranchCode = '$branch') 
					AND TransStatus = 1";
        return $this->db->query($not_in)->result();
    }

    /* ======================lemparan dari modul item master saat suspen data============================== */

    function get_initial_amount_by_item($item_code) {
        $this->db->select('at_transitem_detail.ItemCode');
        $this->db->from('at_transitem_detail');
        $this->db->join('at_transfer_item', 'at_transfer_item.ProdNumber = at_transitem_detail.ProdNumber', 'INNER');

        $this->db->where('at_transitem_detail.ItemCode', $item_code);
        $this->db->where_in('at_transfer_item.TransStatus', array(1, 4, 5, 6));
        $query = $this->db->get();

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

    /* =================================end=============================================================== */

    /* ======================penomeran transaksi new======================================================= */
    /*
      ia = inventori initial amount
     */

    function check_code_exist($new_ia_number) {
        $query = $this->db->get_where($this->table, array('ProdNumber' => $new_ia_number), 1, 0);

        if ($query->num_rows() > 0) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    function create_ia_number($date = '') {
        $branch = $this->session->userdata('branch');

        if ($date != '') {
            $month = date('m', strtotime($date));
            $year = date('y', strtotime($date));
        } else {
            $month = date('m');
            $year = date('y');
        }

        $ia_number = $branch . "/" . $month . $year . "SJA/";

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

        $num = $query->row()->New_Number;
        $long = strlen($num);

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

        $new_ia_number = $ia_number . $number;

        while ($this->check_code_exist($new_ia_number)) {
            $num++;
            $long = strlen($num);

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

            $new_ia_number = $ia_number . $number;
        }

        return $new_ia_number;
    }

    /* ==============================================================end================================= */

    function insert_transsaction($trans_data) {

        $this->db->trans_start();

        /* ================= insert group transaction ============================== */
        $this->insert($trans_data['insert_group']['data']);

        for ($x = 0; $x < count($trans_data['insert_detail']['data']); $x++) {
            $this->insert_detail($trans_data['insert_detail']['data'][$x]);
        }

        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->insert_journal_credit($trans_data['insert_journal']['data'][$x]);
        }

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function update_transsaction($trans_data) {

        $this->db->trans_start();

        $this->update($trans_data['update_group']['data'], $trans_data['update_group']['TransNumber']);

        for ($x = 0; $x < count($trans_data['update_detail']['data']); $x++) {
            $this->update_detail($trans_data['update_detail']['data'][$x], $trans_data['update_detail']['ProdDetailID'][$x]);
        }

        if ($trans_data['delete_detail']['total'] > 0) {
            for ($x = 0; $x < count($trans_data['delete_detail']['ProdDetailID']); $x++) {
                $this->delete_detail_by_id($trans_data['delete_detail']['ProdDetailID'][$x]);
            }
        }

        $this->delete_detail_jurnal_by_id($trans_data['delete_journal']['TransNumber']);

        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->m_transfer_item->insert_journal_credit($trans_data['insert_journal']['data'][$x]);
        }

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function approve_transsaction($trans_data) {

        $this->db->trans_start();

        $this->m_transfer_item->update($trans_data['update_group']['data'], $trans_data['update_group']['TransNumber']);

        for ($x = 0; $x < count($trans_data['insert_inventori']['data']); $x++) {
            $this->insert_inventori($trans_data['insert_inventori']['data'][$x]);
        }

        $this->db->trans_complete();

        if ($this->db->trans_status() === false) {
            return false;
        } else {
            return true;
        }
    }

    function suspend_transsaction($trans_data) {

        $this->db->trans_start();

        $this->update_trans_status($trans_data['suspend_data']['TransNumber'], $trans_data['suspend_data']['data']);

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function reverse_transsaction($trans_data) {

        $this->db->trans_start();

        $this->update_to_reverse_payment($trans_data['reverse_data']['TransNumber'], $trans_data['reverse_data']['data']);
        $this->delete_reconcile($trans_data['delete_reconcile']['TransNumber']);

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    //============== Dhidhik, 2 Mei 2016 =====================================//
    function get_all_to_print($data_filter) {
        /* =================================post dari find di kiri==================================================== */
        $CompanyCode    = $data_filter['cmb_company'];
        $BranchCode     = $data_filter['cmb_branch'];
        $Number         = $data_filter['txt_IDNumber'];
        $Desc           = $data_filter['txt_item_desc'];
        $DateFrom       = $data_filter['txt_DateFrom'];
        $DateTo         = $data_filter['txt_DateTo'];
        $TransStatus    = $data_filter['chk_TransStatus'];
        /* ============================================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_transfer_item.ProdNumber, at_transfer_item.ProdDesc, at_transfer_item.ProdDate, emp.EmployeeName, 
		brnto.BranchName, brnfrom.BranchName as BranchNameTo,
                (case 
                            when at_transfer_item.TransStatus = 1 then 'On Progress'
                            when at_transfer_item.TransStatus = 2 then 'Finished'
                            when at_transfer_item.TransStatus = 3 then 'Suspend'
                            when at_transfer_item.TransStatus = 4 then 'Need App. Mgr.'
                            when at_transfer_item.TransStatus = 5 then 'Need App. Dir.'
                            when at_transfer_item.TransStatus = 6 then 'Need App. Dir. & Mgr.'
                            when at_transfer_item.TransStatus = 9 then 'Canceled by Dir.'
                            else 'Unknown' end) as TransStatus");
        $this->db->from($this->table);
        $this->db->join('am_branch as brnto', 'brnto.BranchCode = at_transfer_item.BranchCode', 'inner');
        $this->db->join('am_branch as brnfrom', 'brnfrom.BranchCode = at_transfer_item.BranchFor', 'inner');
        $this->db->join('am_employee as emp', 'emp.EmployeeCode = at_transfer_item.CreatedBy', 'inner');

        /* ===================================find by number dan desc========================================================= */
        if ($Number != "") {
            $this->db->like('at_transfer_item.ProdNumber', $Number);
        }
        if ($Desc != "") {
            $this->db->like('at_transfer_item.ProdDesc', $Desc);
        }
        /* =============================================end=================================================================== */

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "") {
            $this->db->where('at_transfer_item.BranchCode', $BranchCode);
        } else {
            $this->db->where('at_transfer_item.BranchCode', $sess_branch);
        }
        /* =======================================================end======================================================== */

        /* =====================================================find by date================================================== */
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where('at_transfer_item.ProdDate >=', date("Y-m-d", strtotime($DateFrom)));
                $this->db->where('at_transfer_item.ProdDate <=', date("Y-m-d", strtotime($DateTo)));
            }
        }
        /* ======================================================end=========================================================== */

        /* =====================================================find by status================================================== */
        if (!empty($TransStatus)) {
            $this->db->where_in('at_transfer_item.TransStatus', $TransStatus);
        } else {
            $this->db->where_in('at_transfer_item.TransStatus', array(0, 1, 4, 5, 6, 7));
        }
        /* =======================================================end======================================================== */

        /* ==============================================sort tabel asc========================================================== */
        $this->db->order_by($this->table . '.TransStatus', 'ASC');
        $this->db->order_by($this->table . '.ProdNumber', 'ASC');
        /* ===============================end================================================================================= */
        $query = $this->db->get();
        return $query->result();
    }
    //============== End Dhidhik, 2 Mei 2016 =================================//

}
