<?php

class M_job_costing extends CI_Model {

    // deklarasi nama table
    public $table = "at_jobcosting";
    public $journal = "at_jcostjournal";
    public $branch = "am_branch";
    public $detail = "at_jcostdetail";
    public $period = "at_periodend";
    public $inventories = "at_inventories";
    public $rollover = "at_rollover";

    //create nomor
    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_jobcosting.BranchCode as cpay_branch, am_accountgl.AccountName, at_jobcosting.JCostNumber, am_employee.EmployeeName as CreatedBy,
			at_jobcosting.JCostDate, at_jobcosting.JCostDesc, am_branch.BranchName, am_department.DepartmentName, 
			at_jobcosting.JCostSubTotal, at_jobcosting.TransStatus'
        );
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_jobcosting.BranchCode');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_jobcosting.CreatedBy', 'LEFT');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.JCostAccount');
		$this->db->join('am_department', 'am_department.DepartmentCode = at_jobcosting.DepartmentCode', 'INNER');

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

        /* ===================================find by branch code========================================================= */
        /*
          get data dari  combobox find di view
         */
        if ($BranchCode != "") {
            $this->db->where($this->table . '.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($this->table . '.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }
        /* =======================================================end======================================================== */

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

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

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

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

    function get_all_print_list_LAMA($BranchCode) {
        $this->db->select("at_jobcosting.*,
						(case 
                            when at_jobcosting.TransStatus = 0 then 'Waiting' 
                            when at_jobcosting.TransStatus = 1 then 'On Progress' 
                            when at_jobcosting.TransStatus = 2 then 'Active' 
                            when at_jobcosting.TransStatus = 3 then 'Suspended'
							when at_jobcosting.TransStatus = 4 then 'Need App. Mgr.'  
							when at_jobcosting.TransStatus = 5 then 'Need App. Dir.' 
							when at_jobcosting.TransStatus = 6 then 'App. Dir & Mgr' end) as Status, am_employee.EmployeeName as CreatedBy
					");
        $this->db->from($this->table);
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_jobcosting.CreatedBy', 'LEFT');
        $this->db->where('at_jobcosting.BranchCode', $BranchCode);
        $query = $this->db->get();
        return $query->result();
    }

    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_jobcosting.BranchCode as cpay_branch, am_accountgl.AccountName, at_jobcosting.JCostNumber, am_employee.EmployeeName as CreatedBy,
			at_jobcosting.JCostDate, at_jobcosting.JCostDesc, am_branch.BranchName, 
			at_jobcosting.JCostSubTotal, 
                        (case 
                            when at_jobcosting.TransStatus = 0 then 'Waiting' 
                            when at_jobcosting.TransStatus = 1 then 'On Progress' 
                            when at_jobcosting.TransStatus = 2 then 'Active' 
                            when at_jobcosting.TransStatus = 3 then 'Suspended'
                            when at_jobcosting.TransStatus = 4 then 'Need App. Mgr.'  
                            when at_jobcosting.TransStatus = 5 then 'Need App. Dir.' 
                            when at_jobcosting.TransStatus = 6 then 'App. Dir & Mgr' end) as Status"
        );
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_jobcosting.BranchCode');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_jobcosting.CreatedBy', 'LEFT');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.JCostAccount');

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

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

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

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

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

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

    /* function get_coa_barang_dalam_proses($id_kategori){
      $branch 	= $this->session->userdata('branch');
      $company 	= $this->session->userdata('company');

      $this->db->select('am_accountgl.AccountNumber');
      $this->db->from('am_accountgl');
      $this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber = am_accountgl.AccountNumber', 'inner');
      $this->db->where('am_accountgl.ItemCategory', $id_kategori);
      $this->db->where('am_accountgl.AccountParent', "1-3-06-000-00");
      $this->db->where('at_compaccountgl.CompanyCode', $company);
      $this->db->where('at_compaccountgl.BranchCode', $branch);
      $this->db->where('at_compaccountgl.GLSaldoYears', date("Y"));
      $query = $this->db->get();
      return $query->row();
      } */

    function get_item_account($account_code, $item_category_id) {
        $this->db->select('SysItemCategoryID');
        $this->db->from('sys_itemcategory');
        $this->db->where('ItemCategoryID', $item_category_id);
        $this->db->where('CompanyCode', $this->session->userdata('company'));
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            $data = $query->row();

            $this->db->select('sys_ia.AccountNumber, ag.AccountName');
            $this->db->from('sys_itemaccount as sys_ia');
            $this->db->join('at_compaccountgl as cg', 'cg.AccountNumber = sys_ia.AccountNumber', 'INNER');
            $this->db->join('am_accountgl as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
            $this->db->join('am_branch as br', 'br.BranchCode = cg.BranchCode', 'INNER');
            $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
            $this->db->where('cg.BranchCode', $this->session->userdata('branch'));
            $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');
            $this->db->where('cg.GLSaldoYears', date('Y'));
            $this->db->where('ag.CurrencyCode', 'IDR');
            $this->db->where('sys_ia.AccountCode', $account_code);
            $this->db->where('sys_ia.SysItemCategoryID', $data->SysItemCategoryID);
            $query_account = $this->db->get();

            if ($query_account->num_rows() > 0) {
                $data = $query_account->row();

                if ($data->AccountNumber != NULL) {
                    return $data;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    /* ======================penomeran transaksi new======================================================= */

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

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

    function create_jc_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');
        }

        $jc_number = $branch . "/" . $month . $year . "JC/";

        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(JCostNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(JCostNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('JCostNumber', $jc_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_jc_number = $jc_number . $number;

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

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

            $new_jc_number = $jc_number . $number;
        }

        return $new_jc_number;
    }

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

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

    function update_inventori($data, $ItemCode, $sess_branch) {
        $this->db->update('at_inventories', $data, array('ItemCode' => $ItemCode, 'BranchCode' => $sess_branch));
    }

    function insert_tojournal_debet($data_debet) {
        $this->db->insert('at_jcostjournal', $data_debet);
    }

    function insert_tojournal_credit($data_credit) {
        $this->db->insert('at_jcostjournal', $data_credit);
    }

    function update_tojournal_credit($data_credit_update, $akun_number, $txt_no) {
        $this->db->update('at_jcostjournal', $data_credit_update, array('JCostAccountNumber' => $akun_number, 'JCostNumber' => $txt_no));
    }

    // Function Insert detail 
    function insert_fromgrid($detail_data) {
        $this->db->insert($this->detail, $detail_data);
    }

    function get_inventori($sess_branch, $item_code) {
        $this->db->select('ItemAverage');
        $this->db->from('at_inventories');
        $this->db->where('at_inventories.ItemCode', $item_code);
        $this->db->where('at_inventories.BranchCode', $sess_branch);
        $query = $this->db->get();

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

            return $ItemAverage;
        } else {
            return FALSE;
        }
    }

    function get_item_detail($sess_branch, $item_code) {
        $this->db->select('ItemCOde');
        $this->db->from('at_jcostdetail');
        $this->db->where('at_jcostdetail.ItemCode', $item_code);
        $query = $this->db->get();

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

            return $ItemCOde;
        } else {
            return FALSE;
        }
    }

    function get_persedian_bahan_baku($CompanyCode, $BranchCode) {
        //variable jobtitle kayanya sudah tidak dipakai
        $jobtitle = $this->session->userdata("jobtitle");
        $BranchStatus = $this->session->userdata('branch_status');

        $this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName');
        $this->db->from('at_compaccountgl');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
        $this->db->join('am_branch', 'at_compaccountgl.BranchCode=am_branch.BranchCode');

        $this->db->where('(case when am_branch.BranchStatus = 0 then at_compaccountgl.PLStatus in (0, 1) else at_compaccountgl.PLStatus = 1 end)');
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where('at_compaccountgl.BranchCode', $BranchCode);

        // query diganti ke barang dalam proses di coa
        //$this->db->where('am_accountgl.AccountParent', '1-3-05-008-00');
        $this->db->like('am_accountgl.AccountName', 'Barang Dalam Proses', 'both');
        $this->db->where('am_accountgl.AccountChild', 1);
        $this->db->where('am_accountgl.TransStatus', 2);

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

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

    function get_edit_jobcost($id, $sess_branch) {

        $query = $this->db->get_where($this->table, array('JCostNumber' => $id, 'BranchCode' => $sess_branch));

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

    function get_brg_dlm_proses() {
        $this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, am_accountgl.ItemCategory');
        $this->db->from('am_accountgl');
        $this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber = am_accountgl.AccountNumber', 'inner');
        $this->db->join('am_accountsubtype', 'am_accountsubtype.AccountSubTypeID = am_accountgl.AccountSubTypeID', 'inner');
        $this->db->join('am_branch ', 'am_branch.BranchCode = at_compaccountgl.BranchCode', 'INNER');

        $this->db->where('at_compaccountgl.BranchCode', $this->session->userdata('branch'));
        $this->db->where('at_compaccountgl.CompanyCode', $this->session->userdata('company'));
        $this->db->where('(case when am_branch.BranchStatus = 0 then at_compaccountgl.PLStatus in (0, 1) else at_compaccountgl.PLStatus = 1 end)');

        $this->db->like('am_accountsubtype.AccountTypeName', 'Persediaan');
        $this->db->where('am_accountgl.AccountChild', 1);
        $this->db->where('am_accountgl.TransStatus', 2);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $query = $this->db->get();

        return $query->result();
    }

    function get_edit_jobcost_detail($jcos_number, $BranchCode) {
        $this->db->_protect_identifiers = FALSE;

        $this->db->select($this->detail . '.ItemCode,' . $this->detail . '.ItemQty,' . $this->detail . '.ItemQty as OldQty,' . $this->detail . '.ItemQty as QtyReal,' . $this->detail . '.ItemUnit,' . $this->detail . '.JCostDetailDesc,' . $this->detail . '.JcostDetailDate,' . $this->detail . '.ItemAmount,'
						. $this->detail . '.ItemStatus,' . $this->detail . '.ItemType,' . $this->detail . '.ItemCategoryID,'. $this->detail . '.ItemHPP,' . $this->detail . '.JCostDetailID,'. $this->detail . '.ItemHPP as ItemAverage_Real, "0" as RowStatus');
        $this->db->from($this->detail);
        //$this->db->join('am_item', 'am_item.ItemCode = ' . $this->detail . '.ItemCode', 'LEFT');
        //$this->db->join('at_inventories', 'at_inventories.ItemCode = at_jcostdetail.ItemCode', 'LEFT');
        //$this->db->join('am_department','am_department.DepartmentCode = '.$this->detail.'.CPayDeptCode','INNER');
        $this->db->where($this->detail . '.JCostNumber', $jcos_number);
        //$this->db->where('at_inventories.BranchCode', $BranchCode);
        //$this->db->where('at_invetories.ItemCode', $branch);
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        return $query->result();
    }

    function check_lock_status_inventories($ItemCode, $branch, $status) {
        $this->db->select('LockStatus');
        $this->db->from($this->inventories);
        $this->db->where('BranchCode', $branch);
        $this->db->where('ItemCode', $ItemCode);
        $this->db->where('LockStatus', $status);

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

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

    function get_data_inventories($ItemCode, $branch) {
        $this->db->select('ItemQty,ItemValue,ItemAverage,ItemBooks');
        $this->db->from($this->inventories);
        $this->db->where('BranchCode', $branch);
        $this->db->where('ItemCode', $ItemCode);

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

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

    function update($data, $txt_no) {
        $this->db->update($this->table, $data, array('JCostNumber' => $txt_no));
    }

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

    function update_jurnal_debet($data_jurnal_debet_item, $akun_nomer, $txt_no) {
        $this->db->update('at_jcostjournal', $data_jurnal_debet_item, array('JCostAccountNumber' => $akun_nomer, 'JCostNumber' => $txt_no));
    }

    function update_jurnal_kredit($data_update_credit, $cmb_jcost, $txt_no) {
        $this->db->update('at_jcostjournal', $data_update_credit, array('JCostAccountNumber' => $cmb_jcost, 'JCostNumber' => $txt_no));
    }

    function delete_data_grid($JCostDetailID) {
        $this->db->where('JCostDetailID', $JCostDetailID);
        $this->db->delete($this->detail);
    }

    function get_transstatus($id) {
        $this->db->select('TransStatus');
        $this->db->from($this->table);
        $this->db->where('JCostNumber', $id);
        $query = $this->db->get();

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

            return $TransStatus;
        } else {
            return FALSE;
        }
    }

    function suspend($data, $id) {
        $this->db->update($this->table, $data, array('JCostNumber' => $id));
    }

    function set_trans_status($id, $status) {
        $this->db->set('TransStatus', $status);
        $this->db->where('JCostNumber', $id);
        $this->db->update($this->table);
    }

    function update_app($data, $id) {
        $this->db->update($this->table, $data, array('JCostNumber' => $id));
    }

    function get_detail($id) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("am_branch.BranchName, am_company.CompanyName, at_jobcosting.JCostNumber, at_jobcosting.JCostDesc");
        $this->db->join("am_branch", "am_branch.BranchCode=at_jobcosting.BranchCode", "INNER");
        $this->db->join("am_company", "am_company.CompanyCode=am_branch.CompanyCode", "INNER");
        $query = $this->db->get_where($this->table, array('JCostNumber' => $id));
        $this->db->_protect_identifiers = TRUE;
        return $query->result();
    }

    function get_journal_by_number($Number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("at_jcostjournal.*,am_accountgl.AccountNumber,am_accountgl.AccountName");
        $this->db->join("am_accountgl", "am_accountgl.AccountNumber=at_jcostjournal.JCostAccountNumber", "INNER");
        $this->db->order_by('at_jcostjournal.JCostSaldoDebet', 'DESC');
        $query = $this->db->get_where($this->journal, array('JCostNumber' => $Number));
        $this->db->_protect_identifiers = TRUE;
        return $query->result();
    }

    function delete_detail($id) {
        $this->db->delete($this->detail, array('JCostDetailID' => $id));
    }

    function delete_jurnal($id) {
        $this->db->delete($this->journal, array('JCostNumber' => $id));
    }

    function search_job_costing() {
        /* ======================================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;
        /* ============================================================================================== */

        /* =================================post dari find di atas pop up==================================================== */
        $param = $this->input->post('param');
        $ItemCategoryID = isset($param['cmb_ItemCategoryID']) ? $param['cmb_ItemCategoryID'] : '';
        $ItemCode = isset($param['txt_ItemCode']) ? $param['txt_ItemCode'] : '';
        $ItemDesc = isset($param['txt_ItemDesc']) ? $param['txt_ItemDesc'] : '';
        /* ============================================end============================================================ */

        /* =========================ny session==================================================================== */
        $sess_branch = $this->session->userdata('branch');
        /* =============================end====================================================================== */

        $this->db->select('am_item.ItemCode,am_item.ItemDesc, am_item.ItemUnit, 
							at_inventories.ItemQty, at_inventories.ItemAverage, at_inventories.ItemValue, 
							am_itemcategory.ItemCategoryID, at_inventories.LockStatus as IsLock');
        $this->db->from('am_itemcategory');
        $this->db->join('am_item', 'am_item.ItemCategoryID = am_itemcategory.ItemCategoryID', 'inner');
        $this->db->join('at_inventories', 'at_inventories.ItemCode = am_item.ItemCode', 'inner');

        /* $explode_arr 	= array();
          $explode_arr 	= explode(",", $item_categori);
          $var_array 		= implode(",", $explode_arr); */

        $this->db->where('at_inventories.BranchCode', $sess_branch);
        $this->db->where_in('am_item.TransStatus', array(0, 1));

        /* ===============================================pencarian pas pop up jika kondisi tidak sama dengan kosong ================== */
        if ($ItemCategoryID != "") {
            if ($ItemCategoryID == "All") {
                
            } else {
                $this->db->like('am_item.ItemCategoryID', $ItemCategoryID);
            }
        }
        if ($ItemCode != '') {
            $this->db->like('am_item.ItemCode', $ItemCode);
        }
        if ($ItemDesc != '') {
            $this->db->like('am_item.ItemDesc', $ItemDesc);
        }
        /* ====================================end====================================================================================== */

        $this->db->order_by('am_item.ItemCode', 'ASC');
        $this->db->limit($end, $beginning);

        return $this->db->get()->result();
    }

    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;
        }
    }

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

    function get_job_cos_detail_by_item($item_code) {
        $this->db->select('at_jcostdetail.ItemCode');
        $this->db->from('at_jcostdetail');
        $this->db->join('at_jobcosting', 'at_jobcosting.JCostNumber = at_jcostdetail.JCostNumber', 'INNER');

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

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

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

    function get_detail_print($id) {
        $this->db->select('at_jobcosting.TransStatus, at_jobcosting.JCostDate, am_company.CompanyName, am_branch.BranchName,
							am_branch.BranchAddress,
							at_jobcosting.JCostNumber, am_branch.BranchName, at_jobcosting.JCostDesc, at_jobcosting.JCostSubTotal,
							prepared.EmployeeName as nm_createby, dir.EmployeeName as nm_approve');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_jobcosting.BranchCode');
        $this->db->join('am_company', 'am_company.CompanyCode = am_branch.CompanyCode');
        $this->db->join('am_employee as prepared', 'prepared.EmployeeCode = at_jobcosting.CreatedBy', 'LEFT');
        $this->db->join('am_employee as dir', 'dir.EmployeeCode = at_jobcosting.AppLevel1Code', 'LEFT');

        $this->db->where('at_jobcosting.JCostNumber', $id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    function get_detail_item_print($id) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select(" 
							at_jcostdetail.ItemCode, at_jcostdetail.JCostDetailDesc, at_jcostdetail.ItemUnit, at_jcostdetail.ItemQty,
							at_jcostdetail.JcostDetailDate
						");
        $this->db->join('am_item', 'am_item.ItemCode = at_jcostdetail.ItemCode', 'inner');

        $this->db->where('at_jcostdetail.JCostNumber', $id);
        $this->db->distinct();
        $query = $this->db->get('at_jcostdetail');
        $this->db->_protect_identifiers = TRUE;
        return $query->result_array();
    }

    function check_period_end($periodend) {
        $this->db->select('PeriodEnd');
        $this->db->from($this->period);
        $this->db->where('PeriodEnd', $periodend);

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

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

    function reverse_data($JCostNumber, $data_reverse) {
        $this->db->update($this->table, $data_reverse, array('JCostNumber' => $JCostNumber));
    }

    function check_rollover_transaction($no_jobcosting) {
        $this->db->where('JCostNumber', $no_jobcosting);
        $this->db->where_not_in('TransStatus', array(3, 9));
        $query = $this->db->get($this->rollover)->result_array();

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

    function get_no_rollover($no_jobcosting) {
        $this->db->select('ROvrNumber');
        $this->db->from($this->rollover.' as ro');
        $this->db->where_not_in('TransStatus', array(3, 9));

        $this->db->where('ro.JCostNumber', $no_jobcosting);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    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['inventori_book']['ItemBooks']); $x++) {
            $this->m_inventories->update($trans_data['inventori_book']['ItemBooks'][$x], $trans_data['inventori_book']['ItemCode'][$x]);
        }

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

        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->insert_tojournal_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['inventori_book']['ItemBooks']); $x++) {
            $this->m_inventories->update($trans_data['inventori_book']['ItemBooks'][$x], $trans_data['inventori_book']['ItemCode'][$x]);
        }

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

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

        if ($trans_data['delete_journal']['total'] > 0) {
            $this->delete_jurnal($trans_data['delete_journal']['TransNumber']);
        }

        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->insert_tojournal_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();

        if ($trans_data['delete_journal']['total'] > 0) {
            $this->delete_jurnal($trans_data['delete_journal']['TransNumber']);
        }

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

        if ($trans_data['update_inventori']['total'] > 0) {
            if(!empty($trans_data['update_inventori']['data'])){
				for ($x = 0; $x < count($trans_data['update_inventori']['data']); $x++) {
					$this->update_inventori($trans_data['update_inventori']['data'][$x], $trans_data['update_inventori']['ItemCode'][$x], $trans_data['update_inventori']['branch'][$x]);
				}
			}
        }

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

        $this->set_trans_status($trans_data['trans_status']['TransNumber'], $trans_data['trans_status']['data']);

        $this->db->trans_complete();

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

    function suspend_transsaction($trans_data) {

        $this->db->trans_start();

        if ($trans_data['inventori_book']['total'] > 0) {
            for ($x = 0; $x < count($trans_data['inventori_book']['ItemBooks']); $x++) {
                $this->m_inventories->update($trans_data['inventori_book']['ItemBooks'][$x], $trans_data['inventori_book']['ItemCode'][$x]);
            }
        }

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

        $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->reverse_data($trans_data['reverse_data']['TransNumber'], $trans_data['reverse_data']['data']);

        for ($x = 0; $x < count($trans_data['update_inventori']['LockStatus']); $x++) {
            if ($trans_data['update_inventori']['LockStatus'][$x]) {
                $this->update_inventori($trans_data['update_inventori']['data'][$x], $trans_data['update_inventori']['ItemCode'][$x], $trans_data['update_inventori']['branch'][$x]);
            }
        }

        $this->db->trans_complete();

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

}
