<?php

class M_Adjustment extends CI_Model {

    public $table = "at_inventadjustment";
    public $detail = "at_invadjdetail";
    public $journal = "at_invadjjournal";

    function view_all() {
        $this->db->select('*');
        $this->db->from($this->table);
        $query = $this->db->get();
        return $query;
    }

    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($this->table . '.IAdjDesc,' . $this->table . '.IAdjDate,' . $this->table . '.IAdjNumber,' . $this->table . '.TransStatus as inv_adjstatus,am_accountgl.AccountName, am_employee.EmployeeName as CreatedBy,am_branch.BranchName,am_employee.EmployeeName');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = ' . $this->table . '.BranchCode');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.IAdjAccountNumber');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = ' . $this->table . '.CreatedBy', 'LEFT');

        /* ===================================find by number dan desc========================================================= */
        if ($Number != "") {
            $this->db->like($this->table . '.IAdjNumber', $Number);
        }
        if ($Desc != "") {
            $this->db->like($this->table . '.IAdjDesc', $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 . '.IAdjDate >=', date("Y-m-d", strtotime($DateFrom)));
                $this->db->where($this->table . '.IAdjDate <=', 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 . '.IAdjNumber', '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_latest_inv_adjustment_no($sess_branch) {
        $this->db->select_max('IAdjNumber');
        $query = $this->db->get_where($this->table, array('BranchCode' => $sess_branch));

        return $query->result();
    }

    //function get account by company
    function get_account_bycompany($sess_branch, $sess_company) {
        $accTypeID = array("Assets", "Fixed Asset", "Accumulated Depreciation Fixed Asset");
        $accSubTypeName = array("Aktiva Tetap", "Aktiva Lancar", "Piutang Migrasi", "Piutang Aktiva Tetap", "Piutang Expense", "Persediaan Barang Dagang", "Persediaan Bahan Baku / Mentah", "Persediaan Barang Dalam Proses", "Kewajiban Pajak Tangguhan", "Equity", "Modal");
        $this->db->select('am_accountgl.AccountName,am_accountgl.AccountNumber');
        $this->db->from('at_compaccountgl');

        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber', 'INNER');
        $this->db->join('am_accountsubtype', 'am_accountsubtype.AccountSubTypeID = am_accountgl.AccountSubTypeID', 'INNER');
        //$this->db->join('am_accounttype','am_accounttype.AccountTypeID = am_accountsubtype.AccountTypeID','INNER');

        $this->db->where('at_compaccountgl.BranchCode', $sess_branch);
        $this->db->where('at_compaccountgl.CompanyCode', $sess_company);
        //$this->db->where_in('am_accounttype.AccountTypeName',$accTypeID);
        $this->db->where_in('am_accountsubtype.AccountTypeName', $accSubTypeName);
        $this->db->where('am_accountgl.AccountChild =', 1);
        $this->db->where('at_compaccountgl.TransStatus =', 2);
        $this->db->order_by('am_accountgl.AccountName', 'asc');

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

    function items() {
        $this->db->select('at_inventories.ItemCode, at_inventories.ItemQty, am_item.ItemDesc, am_item.ItemUnit, am_itemprice.ItemPrice, am_item.ItemCategoryID');
        $this->db->from('at_inventories');
        $this->db->join('am_item', 'am_item.ItemCode = at_inventories.ItemCode', 'inner');
        $this->db->join('am_itemprice', 'am_itemprice.ItemCode = at_inventories.ItemCode', 'inner');
        /* $this->db->select('am_item.ItemCode as AmItemCode,am_item.ItemDesc,am_item.ItemUnit,at_inventories.ItemQty,at_inventories.ItemValue');
          $this->db->from('am_item');
          $this->db->join('at_inventories','at_inventories.ItemCode = am_item.ItemCode');
          return $this->db->get()->result(); */
    }

    function get_list_item_group() {

        $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');

        if ($item_catcode != '') {
            $this->db->select('*, 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_catcode);
            $var_array = implode(",", $explode_arr);

            $this->db->where('at_inventories.BranchCode', $sess_branch);
            $this->db->where_in('am_item.ItemCategoryID', $explode_arr);

            if ($item_code != '') {
                $this->db->like('am_item.ItemCode', $item_code);
            }

            if ($item_desc != '') {
                $this->db->like('am_item.ItemDesc', $item_desc);
            }

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

            return $this->db->get()->result();
        } else {
            return '[]';
        }
    }

    /* function get_list_item_group($GroupId,$sess_branch){
      $this->db->select('am_item.ItemCode, at_inventories.ItemQty, at_inventories.ItemHPP, am_item.ItemDesc, am_item.ItemUnit, at_inventories.ItemValue,
      am_item.ItemCategoryID, at_inventories.LockStatus');
      $this->db->from('am_item');
      $this->db->join('at_inventories', 'at_inventories.ItemCode = am_item.ItemCode', 'inner');
      $this->db->where('am_item.ItemCategoryID', $GroupId);
      $this->db->where('at_inventories.BranchCode', $sess_branch);
      $query = $this->db->get();
      return $query->result();
      } */

    function get_item_bycode($item_code) {
        $this->db->select('*');
        $this->db->from('am_item');
        $this->db->where('ItemCode', $item_code);
        return $this->db->get()->result();
    }

    function get_by_id($id) {
        $this->db->select($this->table . '.BranchCode,' . $this->table . '.IAdjDate,' . $this->table . '.IAdjNumber,' . $this->table . '.IAdjAccountNumber,' . $this->table . '.IAdjDesc,'. $this->table . '.CreatedBy,
							' . $this->table . '.AppLevel2Status,' . $this->table . '.StatusCheck,' . $this->table . '.TransStatus as status,am_accountgl.AccountName, am_accountgl.AccountSubTypeID');
        $this->db->from($this->table);
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.IAdjAccountNumber');
        $this->db->where($this->table . '.IAdjNumber', $id);
        $query = $this->db->get();
        return $query->result();
    }

    function get_detail_print($id) {
        $this->db->select('at_inventadjustment.StatusCheck, at_inventadjustment.TransStatus, at_inventadjustment.IAdjDate, am_company.CompanyName, am_branch.BranchName,
							am_branch.BranchAddress,
							at_inventadjustment.IAdjNumber, am_branch.BranchName, at_inventadjustment.IAdjDesc, 
							prepared.EmployeeName as nm_createby, mng.EmployeeName as nm_approve');
        $this->db->from($this->table);
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_inventadjustment.IAdjAccountNumber');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_inventadjustment.BranchCode');
        $this->db->join('am_company', 'am_company.CompanyCode = am_branch.CompanyCode');
        $this->db->join('am_employee as prepared', 'prepared.EmployeeCode = at_inventadjustment.CreatedBy', 'LEFT');
        $this->db->join('am_employee as mng', 'mng.EmployeeCode = at_inventadjustment.AppLevel1Code', 'LEFT');

        $this->db->where('at_inventadjustment.IAdjNumber', $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_invadjdetail.ItemCode, at_invadjdetail.ItemQty, at_invadjdetail.ItemDesc, at_invadjdetail.ItemUnit, at_invadjdetail.ItemQty,
							at_invadjdetail.ItemValue, at_invadjdetail.ItemNewQty, at_invadjdetail.ItemNewValue
						");
        $this->db->join('am_item', 'am_item.ItemCode = at_invadjdetail.ItemCode', 'inner');

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

    function get_data_grid($IAdjNumber) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select($this->detail . '.IAdjDetailID,' . $this->detail . '.IAdjNumber,' . $this->detail . '.ItemCode,' . $this->detail . '.ItemUnit,' . $this->detail . '.ItemQty as Qty, at_inventories.ItemQty as QtyInv, at_inventories.ItemValue as ValueInv, "0" as RowStatus,
							' . $this->detail . '.ItemValue as Value,' . $this->detail . '.ItemDesc as Description,' . $this->detail . '.ItemNewQty as ControlQty,
							' . $this->detail . '.ItemNewValue as ControlValue,at_inventories.ItemAverage as Hpp, am_item.ItemCategoryID, am_item.ItemCode as item_editor');
        $this->db->from($this->detail);
        $this->db->join('at_inventories', $this->detail . '.ItemCode = at_inventories.ItemCode', 'inner');
        $this->db->join('am_item', $this->detail . '.ItemCode = am_item.ItemCode', 'inner');
        $this->db->where($this->detail . '.IAdjNumber', $IAdjNumber);
        //if($this->session->userdata('jobtitle') != "DIR"){
        $this->db->where('at_inventories.BranchCode', $this->session->userdata('branch'));
        //}
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        return $query->result();
    }

    function get_journal_by_adjust_no($IAdjNumber) {
        return $this->db->get_where($this->journal, array('IAdjNumber' => $IAdjNumber))->row();
    }

    function delete_exist_journal($IAdjNumber) {
        $this->db->delete($this->journal, array('IAdjNumber' => $IAdjNumber));
    }

    /* function update_fromgrid($detail_data, $adjust_detail_id) {
      $this->db->update($this->detail, $detail_data, array('IAdjDetailID' => $adjust_detail_id));
      } */

    function delete_journal($IAdjNumber) {
        $this->db->delete($this->journal, array('IAdjNumber' => $IAdjNumber));
    }

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

    function get_adjustment_detail($txt_adjust_number) {
        $this->db->select('StatusCheck');
        $this->db->from('at_invadjdetail');
        $this->db->where('IAdjNumber', $txt_adjust_number);
        $query = $this->db->get();

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

            return $StatusCheck;
        } else {
            return FALSE;
        }
    }

    function get_menulink($id) {
        $this->db->select('MenuLink');
        $this->db->from('tbl_menu');
        $this->db->where('MenuID', $id);
        $query = $this->db->get();

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

            return $MenuLink;
        } else {
            return FALSE;
        }
    }

    //get adjustment number to update status datagrid
    function get_all_by_no($trans_number) {
        $query = $this->db->get_where($this->table, array('IAdjNumber' => $trans_number));
        return $query->result();
    }

    function check_invenadj_number($invenadj_number) {
        $query = $this->db->get_where($this->table, array('IAdjNumber' => $invenadj_number));
        return $query->result();
    }

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

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

    function update_jurnal_debet($debet_journal, $cmb_adj_account, $txt_adjust_number) {
        $this->db->update($this->journal, $debet_journal, array('IAdjNumber' => $txt_adjust_number, 'IAdjAccountNumber' => $cmb_adj_account));
    }

    function update_jurnal_kredit($credit_journal, $akun_by_kategori, $txt_adjust_number) {
        $this->db->update($this->journal, $credit_journal, array('IAdjNumber' => $txt_adjust_number, 'IAdjAccountNumber' => $akun_by_kategori));
    }

    function update_chek($data, $id) {
        $this->db->update($this->detail, $data, array('IAdjNumber' => $id));
        $this->db->update($this->table, $data, array('IAdjNumber' => $id));
    }

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

    function insert_tojournal_debet($debet_journal) {
        $this->db->insert($this->journal, $debet_journal);
    }

    function update_tojournal_debet($debet_journal, $invenadj_number, $cmb_adj_account) {
        $this->db->update($this->journal, $debet_journal, array('IAdjNumber' => $invenadj_number, 'IAdjAccountNumber' => $cmb_adj_account));
    }

    function insert_tojournal_kredit($credit_journal) {
        $this->db->insert($this->journal, $credit_journal);
    }

    function update_tojournal_kredit($credit_journal, $invenadj_number, $account_item) {
        $this->db->update($this->journal, $credit_journal, array('IAdjNumber' => $invenadj_number, 'IAdjAccountNumber' => $account_item));
    }

    function category_item($item_code) {
        $this->db->select('ItemCategoryID');
        $this->db->from('am_item');
        $this->db->where('am_item.ItemCode', $item_code);
        $query = $this->db->get();

        return $query->row();
    }

    function akun_biaya_adjustment($company_code) {
        $branch = $this->session->userdata('branch');

        $this->db->select('am_accountgl.AccountNumber,am_accountgl.AccountName, am_accountgl.AccountSubTypeID, am_accountgl.ItemCategory');
        $this->db->from('at_compaccountgl');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber');
        $this->db->join('am_accountsubtype', 'am_accountsubtype.AccountSubTypeID = am_accountgl.AccountSubTypeID', 'INNER');
        $this->db->join('am_accounttype', 'am_accounttype.AccountTypeID = am_accountsubtype.AccountTypeID', 'INNER');

        $this->db->where_in('am_accounttype.AccountTypeName', array("Inventory"));
        $this->db->where_not_in('am_accountsubtype.AccountTypeName', array("Persediaan Barang Dalam Proses"));
        $this->db->where('am_accountgl.AccountChild', 1);
        $this->db->where('at_compaccountgl.CompanyCode', $company_code);
        $this->db->where('at_compaccountgl.BranchCode', $branch);

        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));

        $this->db->where('at_compaccountgl.TransStatus', 2);

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

    function get_itemcategory($AccountNumber) {
        $this->db->select('am_itemcategory.ItemCategoryID,am_itemcategory.ItemCategoryName');
        $this->db->from('am_itemcategory');
        $this->db->join('am_accountgl', 'am_accountgl.ItemCategory = am_itemcategory.ItemCategoryID', 'inner');
        $this->db->where('am_accountgl.AccountNumber', $AccountNumber);
        $query = $this->db->get();
        return $query->result();
    }

    /* function get_itemcategory($AccountNumber, $company_code){
      $this->db->select('am_itemcategory.ItemCategoryID,am_itemcategory.ItemCategoryName');
      $this->db->from('am_itemcategory');
      $this->db->join('sys_itemcategory','sys_itemcategory.ItemCategoryID = am_itemcategory.ItemCategoryID','inner');
      $this->db->join('sys_itemaccount','sys_itemaccount.SysItemCategoryID = sys_itemcategory.SysItemCategoryID','inner');
      $this->db->where('sys_itemaccount.AccountCode', "Expense");
      $this->db->where('sys_itemcategory.CompanyCode', $company_code);
      //$this->db->where('am_accountgl.AccountNumber', $AccountNumber);
      $query = $this->db->get();
      return $query->result();
      } */

    function sys_category_item($ItemCategoryID, $company_code) {
        $this->db->select('am_accountgl.AccountNumber');
        $this->db->from('am_accountgl');
        $this->db->join('sys_itemcategory', 'am_accountgl.ItemCategory = sys_itemcategory.ItemCategoryID');
        $this->db->where('sys_itemcategory.ItemCategoryID', $ItemCategoryID);
        $this->db->where('sys_itemcategory.CompanyCode', $company_code);
        $query = $this->db->get();

        return $query->row();
    }

    function get_item_account($account_code, $item_category_id, $branch, $company) {

        $this->db->select('SysItemCategoryID');
        $this->db->from('sys_itemcategory');
        $this->db->where('ItemCategoryID', $item_category_id);
        $this->db->where('CompanyCode', $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', $company);
            $this->db->where('cg.BranchCode', $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();

//            $this->db->select('AccountNumber');
//            $this->db->from('sys_itemaccount');
//            $this->db->where('AccountCode', $AccountCode);
//            $this->db->where('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;
        }
    }

    function get_account_persediaan($adjust_number) {
        $this->db->select('IAdjAccountNumber,IAdjNumber');
        $this->db->from($this->table);
        $this->db->where('IAdjNumber', $adjust_number);
        $query = $this->db->get();

        return $query->row();
    }

    function account_name_persediaan($account_number) {
        $this->db->select('AccountCode');
        $this->db->from('sys_itemaccount');
        $this->db->where('AccountNumber', $account_number);
        $query = $this->db->get();

        return $query->row();
    }

    function account_number_adjustment($adjust_number) {
        $this->db->select('at_invadjjournal.IAdjAccountNumber, am_accountgl.AccountName, at_invadjjournal.IAdjSaldoDebet, at_invadjjournal.IAdjSaldoCredit');
        $this->db->from('at_invadjjournal');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_invadjjournal.IAdjAccountNumber');

        $this->db->where('at_invadjjournal.IAdjNumber', $adjust_number);
        $query = $this->db->get();

        return $query->result();
    }

    function account_inv_adjustment() {
        $this->db->select('AccountNumber,AccountName');
        $this->db->from('am_accountgl');
        $this->db->where('AccountName', 'Biaya Selisih Stock');
        $query = $this->db->get();
        return $query->row();
    }

    function account_name_adjustment($adjust_number) {
        $this->db->select('am_accountgl.AccountName');
        $this->db->from($this->table);
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.IAdjAccountNumber');
        $this->db->where($this->table . '.IAdjNumber', $adjust_number);
        $query = $this->db->get();

        return $query->row();
    }

    function detail_to_journal($adjust_number) {
        $this->db->select('*');
        $this->db->from($this->journal);
        $this->db->where($this->journal . '.IAdjNumber', $adjust_number);
        $query = $this->db->get();

        return $query->row();
    }

    function company_name($sess_branch) {
        $this->db->select('am_company.CompanyName, am_company.CompanyCode, am_branch.BranchName');
        $this->db->from('am_company');
        $this->db->join('am_branch', 'am_branch.CompanyCode = am_company.CompanyCode');
        $this->db->where('am_branch.BranchCode', $sess_branch);
        $query = $this->db->get();

        return $query->row();
    }

    // Function Update
    function update($data, $id) {
        $this->db->update($this->table, $data, array('IAdjNumber' => $id));
    }

    function update_status($status_and_approval, $txt_adjust_number) {
        $this->db->update($this->table, $status_and_approval, array('IAdjNumber' => $txt_adjust_number));
    }

    // Function Suspend	
    function suspend($data, $adjust_number) {
        $this->db->update($this->table, $data, array('IAdjNumber' => $adjust_number));
    }

    function get_company_by_branch($sess_branch) {
        $this->db->select('CompanyName');
        $this->db->from('am_company');
        $this->db->join('am_branch', 'am_branch.CompanyCode = am_company.CompanyCode', 'inner');
        $this->db->join($this->table, $this->table . '.BranchCode = am_branch.BranchCode', 'inner');
        $this->db->where($this->table . '.BranchCode', $sess_branch);
        $query = $this->db->get();
        return $query->row();
    }

    function get_all_to_print($data_filter) {
        /* ===========================declar post by find================================================ */
        $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================================================== */

        $this->db->distinct();
        $this->db->select($this->table . ".IAdjDate," . $this->table . ".IAdjNumber," . $this->table . ".TransStatus as inv_adjstatus,am_accountgl.AccountName,am_employee.EmployeeName,
							am_branch.BranchName,
							(case 
                                when at_inventadjustment.TransStatus = 0 then 'Waiting' 
                                when at_inventadjustment.TransStatus = 1 then 'On Progress' 
                                when at_inventadjustment.TransStatus = 2 then 'Active' 
                                when at_inventadjustment.TransStatus = 3 then 'Suspended' 
                                when at_inventadjustment.TransStatus = 4 then 'Need Mgr. App.' 
                                when at_inventadjustment.TransStatus = 5 then 'Need Dir. App.' 
                                when at_inventadjustment.TransStatus = 6 then 'Need Dir & Mgr App.' end) as Status");
        $this->db->from($this->table);
        $this->db->join($this->detail, $this->detail . '.IAdjNumber = ' . $this->table . '.IAdjNumber');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.IAdjAccountNumber');
        $this->db->join('am_branch', 'am_branch.BranchCode = ' . $this->table . '.BranchCode');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = ' . $this->table . '.CreatedBy');

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

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

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

        /* ======================================================end=========================================================== */
        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));
        }
//        chk_TransStatus : arTransStatus //adjustment
        $query = $this->db->get();
        return $query->result();
    }

    function get_all_to_print_LAMA($sess_branch) {
        $this->db->distinct();
        $this->db->select($this->table . ".IAdjDate," . $this->table . ".IAdjNumber," . $this->table . ".TransStatus as inv_adjstatus,am_accountgl.AccountName,am_employee.EmployeeName,
							am_branch.BranchName,
							(case 
                                when at_inventadjustment.TransStatus = 0 then 'Waiting' 
                                when at_inventadjustment.TransStatus = 1 then 'On Progress' 
                                when at_inventadjustment.TransStatus = 2 then 'Active' 
                                when at_inventadjustment.TransStatus = 3 then 'Suspended' 
                                when at_inventadjustment.TransStatus = 4 then 'Need Mgr. App.' 
                                when at_inventadjustment.TransStatus = 5 then 'Need Dir. App.' 
                                when at_inventadjustment.TransStatus = 6 then 'Need Dir & Mgr App.' end) as Status");
        $this->db->from($this->table);
        $this->db->join($this->detail, $this->detail . '.IAdjNumber = ' . $this->table . '.IAdjNumber');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.IAdjAccountNumber');
        $this->db->join('am_branch', 'am_branch.BranchCode = ' . $this->table . '.BranchCode');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = ' . $this->table . '.CreatedBy');
        $this->db->where($this->table . '.BranchCode', $sess_branch);

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

    function get_tabel_detail($adjust_number) {
        $this->db->select('ItemCode');
        $this->db->from('at_invadjdetail');
        $this->db->where('IAdjNumber', $adjust_number);

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

    function get_by_id_suspend($adjust_number) {
        $this->db->select($this->table . '.BranchCode');
        $this->db->from($this->table);
        $this->db->where($this->table . '.IAdjNumber', $adjust_number);
        $query = $this->db->get();
        return $query->row();
    }

    function asset($branch, $item_code) {
        $this->db->select('at_inventdetail.*');
        $this->db->from('at_inventdetail');

        $this->db->where('at_inventdetail.BranchCode', $branch);
        $this->db->where('at_inventdetail.ItemCode', $item_code);
        $this->db->where('at_inventdetail.SoldStatus', 0);

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

    function update_inventories_detail($qty, $AssetCode, $sess_branch) {
        $this->db->set('ItemQty', $qty);
        $this->db->set('SoldStatus', 2);
        $this->db->where('AssetCode', $AssetCode);
        $this->db->where('BranchCode', $sess_branch);
        $this->db->update('at_inventdetail');

        if ($this->db->trans_status() === FALSE) {
            //$this->db->trans_rollback();
            return 0;
        } else {
            //$this->db->trans_commit();
            return 1;
        }
    }

    function insert_inven_detail($data) {
        $this->db->insert('at_inventdetail', $data);
        if ($this->db->trans_status() === FALSE) {
            //$this->db->trans_rollback();
            return 0;
        } else {
            //$this->db->trans_commit();
            return 1;
        }
    }

    function get_detail_item_foradjust($ItemCode, $branch) {
        $this->db->_protect_identifiers = false;
        $this->db->select("at_inventories.ItemAverage as Hpp,at_inventories.ItemQty as Qty, at_inventories.ItemValue as Value, at_inventories.ItemCode, am_item.ItemCode as item_editor, am_item.ItemDesc, am_item.ItemUnit, am_item.ItemCategoryID as id_kategori");
        $this->db->join('am_item', 'am_item.ItemCode = at_inventories.ItemCode');
        $this->db->where_in('at_inventories.ItemCode', $ItemCode);
        $this->db->where('at_inventories.BranchCode', $branch);
        $query = $this->db->get('at_inventories');
        $this->db->_protect_identifiers = true;

        return $query->result();
    }

    function get_inventories_adjustment($ItemCode, $branch) {
        $this->db->select("LockStatus");
        $this->db->where_in('ItemCode', $ItemCode);
        $this->db->where('BranchCode', $branch);
        $query = $this->db->get('at_inventories');

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

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

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

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

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

        $adj_number = $branch . "/" . $month . $year . "IA/";

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

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

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

            $new_adj_number = $adj_number . $number;
        }

        return $new_adj_number;
    }

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

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

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

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

    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_fromgrid($trans_data['insert_detail']['data'][$x]);
        }

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

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

        $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_fromgrid($trans_data['update_detail']['data'][$x], $trans_data['update_detail']['IAdjDetailID'][$x]);
        }

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

                $this->update_inventories($trans_data['update_inventories']['data'][$x], $trans_data['update_inventories']['ItemCode'][$x], $trans_data['update_inventories']['branch'][$x]);
            }
        }

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

        for ($x = 0; $x < count($trans_data['update_inventories']['data']); $x++) {
            $this->update_inventories($trans_data['update_inventories']['data'][$x], $trans_data['update_inventories']['ItemCode'][$x], $trans_data['update_inventories']['branch'][$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->suspend($trans_data['suspend_data']['data'], $trans_data['suspend_data']['TransNumber']);

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

        $this->db->trans_complete();

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

    function reverse_transsaction($transaction_data) {
        $this->db->trans_start();

        if (!empty($transaction_data['update_inventory']['data'])) {
            for ($x = 0; $x < count($transaction_data['update_inventory']['data']); $x++) {
                $this->m_inventories->update($transaction_data['update_inventory']['data'][$x], $transaction_data['update_inventory']['ItemCode'][$x]);
            }
        }
		
		$this->update_status($transaction_data['update_status']['data'], $transaction_data['update_status']['TransNumber']);
		
        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }
	
	function get_check_transaction($table_group, $table_detail, $Field_Item_code, $branch, $ItemCode, $TransNumber){
		$this->db->select($table_detail.".".$Field_Item_code);
		$this->db->from($table_detail);
		$this->db->join($table_group, $table_group.'.'.$TransNumber.'='.$table_detail.'.'.$TransNumber);
        $this->db->where($table_detail.'.'.$Field_Item_code, $ItemCode);
        $this->db->where($table_group.'.BranchCode', $branch);
		$this->db->where_in($table_group.'.TransStatus', array(1,2,4,5,6,8));
		
		$query = $this->db->get();
        if ($query->num_rows() > 0) {
            return TRUE;
        } else {
            return FALSE;
        }
	}

}
