<?php

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

class M_receive_item extends CI_Model {

    public $table = "at_receiveitem";
    public $detail = "at_precdetail";
    public $table_po = "at_purchaseorder";
    public $detail_po = "at_porddetail";
    public $prec_journal = "at_precjournal";
    public $comp_gl = "at_compaccountgl";
    public $branch = "am_branch";
    public $company = "am_company";
    public $item_category = "am_itemcategory";
    public $vendor = "am_vendor";
    public $item = "am_item";
    public $account_gl = "am_accountgl";
    public $employee = "am_employee";
    public $payment = "am_termspayment";
    public $shipment = "am_shipment";
    public $inven_detail = "at_inventdetail";

    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'] : '';
        $VendorCode = isset($param['cmb_VendorCode']) ? $param['cmb_VendorCode'] : '';
        /* ============================================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_receiveitem.*, am_branch.BranchName, 
							(case 
								when at_receiveitem.TransStatus = 0 then 'Waiting' 
								when at_receiveitem.TransStatus = 1 then 'On Progress' 
								when at_receiveitem.TransStatus = 2 then 'Finished' 
								when at_receiveitem.TransStatus = 3 then 'Suspended'
								when at_receiveitem.TransStatus = 4 then 'Need App. Mgr.'  
								when at_receiveitem.TransStatus = 5 then 'Need App. Dir.' 
								when at_receiveitem.TransStatus = 6 then 'App. Dir & Mgr' end) as Status,
							am_vendor.VendorName, am_employee.EmployeeName as CreatedBy");
        $this->db->from($this->table);
        $this->db->join('am_vendor', 'at_receiveitem.VendorCode=am_vendor.VendorCode', 'INNER');
        $this->db->join('am_branch', 'at_receiveitem.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_employee', 'at_receiveitem.CreatedBy=am_employee.EmployeeCode', 'INNER');

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

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

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

        /* =====================================================find by status================================================== */
        if ($TransStatus != '') {
            $this->db->where_in('at_receiveitem.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_receiveitem.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 . '.PRecNumber', 'ASC');
        /* ===============================end================================================================================= */
        $query = $this->db->limit($end, $beginning);
        $query = $this->db->get();
        return $query->result();
    }

    function get_all_to_print($data_filter) {
        /* =================================post dari find di kiri==================================================== */
        $BranchCode     = $data_filter['cmb_branch'];
        $Number         = $data_filter['txt_IDNumber'];
        $Desc           = $data_filter['txt_Description'];
        $DateFrom       = $data_filter['txt_DateFrom'];
        $DateTo         = $data_filter['txt_DateTo'];
        $TransStatus    = $data_filter['chk_TransStatus'];
        $VendorCode     = $data_filter['cmb_VendorCode'];
        /* ============================================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_receiveitem.*, am_branch.BranchName, 
							(case 
								when at_receiveitem.TransStatus = 0 then 'Waiting' 
								when at_receiveitem.TransStatus = 1 then 'On Progress' 
								when at_receiveitem.TransStatus = 2 then 'Finished' 
								when at_receiveitem.TransStatus = 3 then 'Suspended'
								when at_receiveitem.TransStatus = 4 then 'Need App. Mgr.'  
								when at_receiveitem.TransStatus = 5 then 'Need App. Dir.' 
								when at_receiveitem.TransStatus = 6 then 'App. Dir & Mgr' end) as Status,
							am_vendor.VendorName, am_employee.EmployeeName as CreatedBy");
        $this->db->from($this->table);
        $this->db->join('am_vendor', 'at_receiveitem.VendorCode=am_vendor.VendorCode', 'INNER');
        $this->db->join('am_branch', 'at_receiveitem.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_employee', 'at_receiveitem.CreatedBy=am_employee.EmployeeCode', 'INNER');

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

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

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

        /* =====================================================find by status================================================== */
        if (!empty($TransStatus)) {
            $this->db->where_in('at_receiveitem.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_receiveitem.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 . '.PRecNumber', 'ASC');
        /* ===============================end================================================================================= */
        $query = $this->db->get();
        return $query->result();
    }

    function get_all_print() {
        /* ==========================================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_receiveitem.*, am_branch.BranchName, 
							(case 
								when at_receiveitem.TransStatus = 0 then 'Waiting' 
								when at_receiveitem.TransStatus = 1 then 'On Progress' 
								when at_receiveitem.TransStatus = 2 then 'Finished' 
								when at_receiveitem.TransStatus = 3 then 'Suspended'
								when at_receiveitem.TransStatus = 4 then 'Need App. Mgr.'  
								when at_receiveitem.TransStatus = 5 then 'Need App. Dir.' 
								when at_receiveitem.TransStatus = 6 then 'App. Dir & Mgr' end) as Status,
							am_vendor.VendorName, am_employee.EmployeeName as CreatedBy");
        $this->db->from($this->table);
        $this->db->join('am_vendor', 'at_receiveitem.VendorCode=am_vendor.VendorCode', 'INNER');
        $this->db->join('am_branch', 'at_receiveitem.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_employee', 'at_receiveitem.CreatedBy=am_employee.EmployeeCode', 'INNER');

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

    function get_detail($prec_number) {
        $this->db->select('ri.*, br.BranchName, cp.CompanyName, vd.VendorAddress, rd.ItemCategoryID, rd.ItemType');
        $this->db->from($this->table . ' ri');
        $this->db->join($this->vendor . ' vd', 'vd.VendorCode = ri.VendorCode', 'INNER');
        $this->db->join($this->branch . ' br', 'br.BranchCode = ri.BranchCode', 'INNER');
        $this->db->join($this->company . ' cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->detail . ' rd', 'rd.PRecNumber = ri.PRecNumber', 'INNER');
        // $this->db->join($this->payment . ' as pay', 'pay.TermspaymentID = po.TermspaymentID', 'INNER');
        $this->db->where('ri.PRecNumber', $prec_number);
        $query = $this->db->get();

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

    //Daswan get detail to print
    function get_detail_print($prec_number) {
        $this->db->select("ri.*, br.BranchName, cp.CompanyName, vd.VendorAddress, vd.VendorName, vd.PhoneNumber,emp_created.EmployeeName as created_by, emp_manager.EmployeeName as nm_manager, pay.TermDesc,
             (case when ri.FOBID = 1 then 'Shiping Point' when ri.FOBID = 2 then 'Destination' else 'No' end) as FOB, sm.ShipmentName");
        $this->db->from($this->table . ' ri');
        $this->db->join($this->vendor . ' vd', 'vd.VendorCode = ri.VendorCode', 'INNER');
        $this->db->join($this->branch . ' br', 'br.BranchCode = ri.BranchCode', 'INNER');
        $this->db->join($this->company . ' cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->employee . ' as emp_created', 'emp_created.EmployeeCode = ri.CreatedBy', 'LEFT');
        $this->db->join($this->employee . ' as emp_manager', 'emp_manager.EmployeeCode = ri.AppLevel1Code', 'LEFT');
        $this->db->join($this->payment . ' as pay', 'pay.TermspaymentID = ri.TermspaymentID', 'INNER');
        $this->db->join($this->shipment . ' as sm', 'sm.ShipmentID = ri.ShipmentID', 'LEFT');
        $this->db->where('PRecNumber', $prec_number);
        $query = $this->db->get();

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

    function get_detail_item($prec_number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("rd.*, rd.ItemQty as ItemQty_Old, pd.ItemQtyRI as POrdQtyRI, ((pd.ItemQty - pd.ItemQtyRI) + rd.ItemQty) as ItemMax, '0' as Modus_Type");
        $this->db->from($this->detail . ' as rd');
        $this->db->join("at_porddetail as pd", "pd.POrdDetailID = rd.POrdDetailID", "LEFT");
        $this->db->where('PRecNumber', $prec_number);
        $this->db->order_by('PRecDetailID', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;

        return $query->result();
    }

    //Daswan get detail item to print
    function get_detail_item_print($prec_number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("rd.*, rd.ItemQty as ItemQty_Old, pd.ItemQtyRI as POrdQtyRI, ((pd.ItemQty - pd.ItemQtyRI) + rd.ItemQty) as ItemMax, '0' as Modus_Type");
        $this->db->from($this->detail . ' as rd');
        $this->db->join("at_porddetail as pd", "pd.POrdDetailID = rd.POrdDetailID", "INNER");
        $this->db->where('PRecNumber', $prec_number);
        $this->db->order_by('PRecDetailID', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;

        return $query->result_array();
    }

    function get_detail_item_for_pi($pord_number) {
        $this->db->_protect_identifiers = false;
        $this->db->select('(ri.PRecNumber) as PRecNumber, pod.ItemCode, pod.ItemDesc, (rid.ItemQty) as ItemQty, pod.ItemUnit, pod.ItemCodeVendor, 
                           rid.ItemPrice, (rid.ItemAmount) as ItemAmount, po.POrdPPn10Pcnt, po.POrdPPh22Pcnt, po.POrdPPh23Pcnt,
                           (rid.ItemTaxPPn10) as ItemTaxPPn10, pod.ItemTaxPPh22 as ItemTaxPPh22, pod.ItemTaxPPh23 as ItemTaxPPh23, (rid.ItemTotalAmount) as ItemTotalAmount, pod.ItemCategoryID, pod.ItemCategoryCode, (rid.ItemAmount) as ItemAmount_Hide,
                           pod.ItemType, pod.ItemPPh22Status, pod.ItemPPh23Status, pod.ItemAdvNetDueIn, pod.ItemPriceNTax, pod.ItemStatus, pod.ItemDisc, rid.ItemDisc as ItemDiscPcnt, ((pod.ItemAmount * pod.ItemDisc) / 100) as AmountDisc');
        $this->db->from($this->table . ' as ri');
        $this->db->join($this->detail . ' as rid', 'rid.PRecNumber = ri.PRecNumber', 'INNER');
        $this->db->join($this->table_po . ' as po', 'po.POrdNumber = ri.POrdNumber', 'INNER');
        $this->db->join($this->detail_po . ' as pod', 'pod.POrdDetailID = rid.POrdDetailID', 'INNER');
        $this->db->where('ri.POrdNumber', $pord_number);
        $this->db->where('ri.LockedStatus != ', 1);
        $this->db->where('ri.TransStatus = ', 1);
        $this->db->where('po.TransStatus', 2);
        $this->db->where('pod.ItemQty - (pod.ItemQtyRI + pod.ItemQtySpd) = ', 0);
        //$this->db->group_by('po.POrdNumber, pod.ItemCode');
        $this->db->order_by('pod.POrdDetailID', 'Asc');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

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

    function get_ri_for_pi($vendor_code, $item_type) {
        $this->db->distinct();
        $this->db->select("group_concat(ri.PRecnumber) as PRecNumber, group_concat(ri.PRecDate) as PRecDate, group_concat(ri.PRecDesc) as PRecDesc, ri.ShipmentDate, po.POrdNumber, po.OldPOrdNumber, 
                           po.POrdDate, po.CurrencyCode, po.CurrencyRate, po.POrdDesc, ri.PRecSubTotal as POrdSubTotal, po.ShipmentID, po.TermsPaymentID, 
                           po.FOBID, po.POrdTaxesStatus, po.POrdInTaxStatus, po.POrdDPStatus, po.POrdDiscount, po.POrdPPn10Pcnt, po.POrdPPh22Pcnt, 
                           po.POrdPPh23Pcnt, ri.PRecPPn10 as POrdPPn10, po.POrdPPh22, po.POrdPPh23, po.POrdEstFreight, po.POrdTotalDP, ri.PRecTotalAmount as POrdTotalAmount");
        $this->db->from($this->table . ' as ri');
        $this->db->join($this->table_po . ' as po', 'po.POrdNumber = ri.POrdNumber', 'INNER');
        $this->db->join($this->detail_po . ' as pod', 'pod.POrdNumber = po.POrdNumber', 'INNER');
        $this->db->join($this->vendor . ' as vd', 'vd.VendorCode = ri.VendorCode', 'INNER');
        //$this->db->where('ri.BranchCode', $branch_code);
        $this->db->where('ri.VendorCode', $vendor_code);
        $this->db->where('ri.LockedStatus != ', 1);
        $this->db->where('ri.TransStatus', 1);
        $this->db->where('po.TransStatus', 2);
        $this->db->where('pod.ItemQty - (pod.ItemQtyRI + pod.ItemQtySpd) = ', 0);

        $this->db->where_in('pod.ItemType', array(0, 1));

        $this->db->group_by('po.POrdNumber, pod.ItemCode');
        $this->db->order_by('ri.PRecNumber', 'ASC');
        $query = $this->db->get();

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

    function get_ri_for_pi_adv($vendor_code, $item_type) {
        $this->db->distinct();
        $this->db->select("group_concat(ri.PRecnumber) as PRecNumber, group_concat(ri.PRecDate) as PRecDate,group_concat(ri.PRecDesc) as PRecDesc, ri.ShipmentDate, po.POrdNumber, po.OldPOrdNumber, 
                           po.POrdDate, po.CurrencyCode, po.CurrencyRate, po.POrdDesc, ri.PRecSubTotal as POrdSubTotal, po.ShipmentID, po.TermsPaymentID, 
                           po.FOBID, po.POrdTaxesStatus, po.POrdInTaxStatus, po.POrdDPStatus, po.POrdDiscount, po.POrdPPn10Pcnt, po.POrdPPh22Pcnt, 
                           po.POrdPPh23Pcnt, ri.PRecPPn10 as POrdPPn10, po.POrdPPh22, po.POrdPPh23, po.POrdEstFreight, po.POrdTotalDP, ri.PRecTotalAmount as POrdTotalAmount");
        $this->db->from($this->table . ' as ri');
        $this->db->join($this->table_po . ' as po', 'po.POrdNumber = ri.POrdNumber', 'INNER');
        $this->db->join($this->detail_po . ' as pod', 'pod.POrdNumber = po.POrdNumber', 'INNER');
        $this->db->join($this->vendor . ' as vd', 'vd.VendorCode = ri.VendorCode', 'INNER');
        // $this->db->where('ri.BranchCode', $branch_code);
        $this->db->where('ri.VendorCode', $vendor_code);
        $this->db->where('ri.LockedStatus != ', 1);
        $this->db->where('ri.TransStatus', 1);
        $this->db->where('po.TransStatus', 2);
        $this->db->where('pod.ItemQty - (pod.ItemQtyRI + pod.ItemQtySpd) = ', 0);

        $this->db->where('pod.ItemType', 2);

        $this->db->group_by('po.POrdNumber, pod.ItemCode');
        $this->db->order_by('ri.PRecNumber', 'ASC');
        $query = $this->db->get();

        return $query->result();
    }

    function check_item_ri_approved_by_pordnumber($pord_number) {
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('POrdNumber', $pord_number);
        $this->db->where_in('TransStatus', array(4, 5, 6));
        $query = $this->db->get();

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

//    function get_total_item_ri_by_pordnumber($pord_number, $item_code){
//        $this->db->select('sum(rd.ItemQty) as Total_Qty');
//        $this->db->from($this->table . ' ri');
//        $this->db->join($this->detail . ' rd', 'rd.PRecNumber = ri.PRecNumber', 'INNER');
//        $this->db->where('ri.POrdNumber', $pord_number);
//        $this->db->where('rd.ItemCode', $item_code);
//        $this->db->where_in('ri.TransStatus', array(1,2));
//        $query = $this->db->get();
//        
//        if ($query->row()->Total_Qty != NULL){
//            return $query->row()->Total_Qty;            
//        }
//        else{
//            return 0;
//        }
//    }

    function get_journal_by_number($prec_number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('pjl.*, agl.AccountNumber, agl.AccountName');
        $this->db->from($this->prec_journal . ' as pjl');
        $this->db->join($this->account_gl . ' as agl', 'agl.AccountNumber = pjl.PRecAccountNumber', 'INNER');
        $this->db->where('PRecNumber', $prec_number);
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;

        return $query->result();
    }

    function get_tax_status($id) {
        $this->db->select('PRecTaxesStatus, PRecInTaxStatus');
        $this->db->where('PRecNumber', $id);
        $query = $this->db->get($this->table);

        return $query->result();
    }

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

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

    function check_lock_status($prec_number, $branch_code, $item_code) {
        $this->db->select('*');
        $this->db->from($this->detail . ' as rid');
        $this->db->join('at_inventories as it', 'it.ItemCode = rid.ItemCode', 'LEFT');
        $this->db->where('rid.PRecNumber', $prec_number);
        $this->db->where('it.ItemCode', $item_code);

        $this->db->where('it.BranchCode', $branch_code);
        $this->db->where('it.LockStatus', 1);
        $query = $this->db->get();

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

    function get_journal_receive_empty($PRecAccountNumber, $PRecNumber) {
        $this->db->select('PRecAccountNumber, PRecSaldoDebet, AmountCurrency');
        $this->db->from($this->prec_journal . ' as rij');

        $this->db->where('rij.PRecAccountNumber', $PRecAccountNumber);
        $this->db->where('rij.PRecNumber', $PRecNumber);

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

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

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

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

        $ri_number = $branch_code . "/" . $month . $year . "RI/";

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

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

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

            $new_ri_number = $ri_number . $number;
        }
        return $new_ri_number;
    }

    function insert_all_table_new($data, $arr_detail_pr, $arr_detail_po, $arr_detail, $prec_no, $arr_journal_inventory, $journal_unbilled, $account_number) {
        $this->db->trans_start();
        $inventory_journal = 0;
        $inventory_currency = 0;

        $this->insert($data);

        /*
          Loop detail receive item
         */
        for ($i = 0; $i < count($arr_detail); $i++) {
            $detail_data = array(
                'POrdDetailID' => $arr_detail[$i]['POrdDetailID'],
                'POrdNumber' => $arr_detail[$i]['POrdNumber'],
                'PRecNumber' => $prec_no,
                'ItemCode' => $arr_detail[$i]['ItemCode'],
                'ItemDesc' => $arr_detail[$i]['ItemDesc'],
                'itemUnit' => $arr_detail[$i]['itemUnit'],
                'ItemQty' => $arr_detail[$i]['ItemQty'],
                'ItemDisc' => $arr_detail[$i]['ItemDisc'],
                'ItemPrice' => $arr_detail[$i]['ItemPrice'],
                'ItemAmount' => $arr_detail[$i]['ItemAmount'],
                'ItemTaxPPn10' => $arr_detail[$i]['ItemTaxPPn10'],
                'ItemTotalAmount' => $arr_detail[$i]['ItemTotalAmount'],
                'ItemCategoryID' => $arr_detail[$i]['ItemCategoryID'],
                'ItemCategoryCode' => $arr_detail[$i]['ItemCategoryCode'],
                'ItemType' => $arr_detail[$i]['ItemType'],
                'ItemStatus' => $arr_detail[$i]['ItemStatus'],
				'ItemCodeVendor' => $arr_detail[$i]['ItemCodeVendor'],
                'LastUser' => $arr_detail[$i]['LastUser']
            );
            $this->insert_detail($detail_data);
        }
        /* ====================End================================================== */

        /*
          Loop Qty RI To Pr
         */
        for ($pr = 0; $pr < count($arr_detail_pr); $pr++) {
            $PReqDetailID = $arr_detail_pr[$pr]['PReqDetailID'];
            $detail_data_preq = array(
                'ItemQtyRI' => $arr_detail_pr[$pr]['ItemQtyRI'],
                'LastUser' => $arr_detail_pr[$pr]['LastUser']
            );
            $this->m_purchase_requisition->update_detail($detail_data_preq, $PReqDetailID);
        }
        /* =====================End====================================================== */

        /*
          Loop Qty RI To Po
         */
        for ($po = 0; $po < count($arr_detail_po); $po++) {
            $POrdDetailID = $arr_detail_po[$po]['POrdDetailID'];
            $detail_data_po = array(
                'ItemQtyRI' => $arr_detail_po[$po]['ItemQtyRI'],
                'LastUser' => $arr_detail_po[$po]['LastUser']
            );
            $this->m_purchase_order->update_detail($detail_data_po, $POrdDetailID);
        }
        /* =====================End==================================================== */

        /*
          Loop journal Receive Item
         */
        for ($rj = 0; $rj < count($arr_journal_inventory); $rj++) {
            $get_journal_receipt = $this->m_receive_item->get_journal_receive_empty($account_number, $prec_no);
            if (empty($get_journal_receipt->PRecAccountNumber)) {
                $journal_inventory = array(
                    'PRecDate' => $arr_journal_inventory[$rj]['PRecDate'],
                    'PRecNumber' => $arr_journal_inventory[$rj]['PRecNumber'],
                    'PRecDesc' => $arr_journal_inventory[$rj]['PRecDesc'],
                    'PRecAccountNumber' => $arr_journal_inventory[$rj]['PRecAccountNumber'],
                    'PRecSaldoDebet' => $arr_journal_inventory[$rj]['PRecSaldoDebet'],
                    'PRecSaldoCredit' => $arr_journal_inventory[$rj]['PRecSaldoCredit'],
                    'AmountCurrency' => $arr_journal_inventory[$rj]['AmountCurrency'],
                    'PRecSignStatus' => $arr_journal_inventory[$rj]['PRecSignStatus'],
                    'ItemCategoryID' => $arr_journal_inventory[$rj]['ItemCategoryID'],
                    'LastUser' => $arr_journal_inventory[$rj]['LastUser']
                );
                $this->insert_journal($journal_inventory);
            } else {
                $inventory_value = $arr_journal_inventory[$rj]['PRecSaldoDebet'] + $get_journal_receipt->PRecSaldoDebet;
                $inventory_value_curr = $arr_journal_inventory[$rj]['AmountCurrency'] + $get_journal_receipt->AmountCurrency;

                $journal_inventory = array(
                    'PRecSaldoDebet' => $inventory_value,
                    'PRecSaldoCredit' => $arr_journal_inventory[$rj]['PRecSaldoCredit'],
                    'AmountCurrency' => $inventory_value_curr
                );
                $this->update_journal($journal_inventory, $account_number, $prec_no);
            }
        }

        if ($account_number != "") {
            $this->insert_journal($journal_unbilled);
        }
        /* =========================End======================================================= */

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

    function update_all_new_table($data, $prec_no, $arr_update_detail, $arr_update_po, $arr_update_pr, $arr_journal_inventory, $journal_unbilled, $account_number, $calculate_qty_ri, $arr_delete_item_ri) {
        $this->db->trans_start();
        $inventory_journal = 0;
        $inventory_currency = 0;

        $this->update($data, $prec_no);
        $this->delete_journal($prec_no);
        /*
          Loop detail receive item
         */
        for ($i = 0; $i < count($arr_update_detail); $i++) {
            $PRecDetailID = $arr_update_detail[$i]['PRecDetailID'];
            $detail_data = array(
                'ItemQty' => $arr_update_detail[$i]['ItemQty'],
                'ItemAmount' => $arr_update_detail[$i]['ItemAmount'],
                'ItemTaxPPn10' => $arr_update_detail[$i]['ItemTaxPPn10'],
                'ItemTotalAmount' => $arr_update_detail[$i]['ItemTotalAmount'],
				'ItemCodeVendor' => $arr_update_detail[$i]['ItemCodeVendor'],
                'LastUser' => $arr_update_detail[$i]['LastUser']
            );
            $this->update_detail($detail_data, $PRecDetailID);
        }
        /* ==================End==================================================== */

        /*
          Loop Item Qty RI To Pr
         */
        if (!empty($arr_update_pr)) {
            for ($pr = 0; $pr < count($arr_update_pr); $pr++) {
                $PReqDetailID = $arr_update_pr[$pr]['PReqDetailID'];
                $detail_data_preq = array(
                    'ItemQtyRI' => $arr_update_pr[$pr]['ItemQtyRI'],
                    'LastUser' => $arr_update_pr[$pr]['LastUser']
                );
                $this->m_purchase_requisition->update_detail($detail_data_preq, $PReqDetailID);
            }
        }
        /* ==================End===================================== */

        /*
          Loop Item Qty RI To Po
         */
        if (!empty($arr_update_po)) {
            for ($po = 0; $po < count($arr_update_po); $po++) {
                $POrdDetailID = $arr_update_po[$po]['POrdDetailID'];
                $detail_data_po = array(
                    'ItemQtyRI' => $arr_update_po[$po]['ItemQtyRI'],
                    'LastUser' => $arr_update_po[$po]['LastUser']
                );
                $this->m_purchase_order->update_detail($detail_data_po, $POrdDetailID);
            }
        }
        /* ==================End===================================== */

        /*
          Loop Journal
         */
        if (!empty($arr_journal_inventory)) {
            for ($rj = 0; $rj < count($arr_journal_inventory); $rj++) {
                $get_journal_receipt = $this->m_receive_item->get_journal_receive_empty($account_number, $prec_no);
                if (empty($get_journal_receipt->PRecAccountNumber)) {
                    $journal_inventory = array(
                        'PRecDate' => $arr_journal_inventory[$rj]['PRecDate'],
                        'PRecNumber' => $arr_journal_inventory[$rj]['PRecNumber'],
                        'PRecDesc' => $arr_journal_inventory[$rj]['PRecDesc'],
                        'PRecAccountNumber' => $arr_journal_inventory[$rj]['PRecAccountNumber'],
                        'PRecSaldoDebet' => $arr_journal_inventory[$rj]['PRecSaldoDebet'],
                        'PRecSaldoCredit' => $arr_journal_inventory[$rj]['PRecSaldoCredit'],
                        'AmountCurrency' => $arr_journal_inventory[$rj]['AmountCurrency'],
                        'PRecSignStatus' => $arr_journal_inventory[$rj]['PRecSignStatus'],
                        'ItemCategoryID' => $arr_journal_inventory[$rj]['ItemCategoryID'],
                        'LastUser' => $arr_journal_inventory[$rj]['LastUser']
                    );
                    $this->insert_journal($journal_inventory);
                } else {
                    $inventory_value = $arr_journal_inventory[$rj]['PRecSaldoDebet'] + $get_journal_receipt->PRecSaldoDebet;
                    $inventory_value_curr = $arr_journal_inventory[$rj]['AmountCurrency'] + $get_journal_receipt->AmountCurrency;

                    $journal_inventory = array(
                        'PRecSaldoDebet' => $inventory_value,
                        'PRecSaldoCredit' => $arr_journal_inventory[$rj]['PRecSaldoCredit'],
                        'AmountCurrency' => $inventory_value_curr
                    );
                    $this->update_journal($journal_inventory, $account_number, $prec_no);
                }
            }
        }

        if (!empty($journal_unbilled)) {
            if ($account_number != "") {
                $this->insert_journal($journal_unbilled);
            }
        }
        /* ==========================End===================================================== */

        /*
          Loop Calculate Jika item RI Di delete row
          hitung ulang qty RI
         */
        for ($item_ri = 0; $item_ri < count($arr_delete_item_ri); $item_ri++) {
            $PRecDetailID = $arr_delete_item_ri[$item_ri]['PRecDetailID'];
            $this->delete_detail($PRecDetailID);
        }

        for ($qty_ri = 0; $qty_ri < count($calculate_qty_ri); $qty_ri++) {
            $POrdDetailID = $calculate_qty_ri[$qty_ri]['POrdDetailID'];
            $PReqDetailID = $calculate_qty_ri[$qty_ri]['PReqDetailID'];

            $detail_data_po = array(
                'ItemQtyRI' => $calculate_qty_ri[$qty_ri]['ItemQtyRI'],
                'LastUser' => $calculate_qty_ri[$qty_ri]['LastUser']
            );
            $this->m_purchase_order->update_detail($detail_data_po, $POrdDetailID);
            $this->m_purchase_requisition->update_detail($detail_data_po, $PReqDetailID);
        }
        /* =========================End============================================ */

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

    function suspend_data($transaction_data) {
        $this->db->trans_start();
        $this->update($transaction_data['update_group']['data'], $transaction_data['update_group']['PrecNo']);
        /*
          Loop Qty RI to PO Dan PR
         */
        for ($x = 0; $x < count($transaction_data['update_qty_ri']['data']); $x++) {
            $this->m_purchase_order->update_detail($transaction_data['update_qty_ri']['data'][$x], $transaction_data['update_qty_ri']['POrdDetailID'][$x]);
            $this->m_purchase_requisition->update_detail($transaction_data['update_qty_ri']['data'][$x], $transaction_data['update_qty_ri']['PReqDetailID'][$x]);
        }
        /* ======================End================================================= */
        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function approved_all($transaction_data, $detail) {
        $this->db->trans_start();

        for ($x = 0; $x < count($transaction_data['update_po']['data']); $x++) {
            $this->m_purchase_order->update($transaction_data['update_po']['data'][$x], $transaction_data['update_po']['POrdNumber'][$x]);
        }

        if (!empty($transaction_data['insert_inventory'])) {
            for ($x = 0; $x < count($transaction_data['insert_inventory']); $x++) {
                $this->m_inventories->insert($transaction_data['insert_inventory'][$x]);
            }
        }

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

        if (!empty($transaction_data['insert_asset'])) {
            for ($x = 0; $x < count($transaction_data['insert_asset']); $x++) {
                $this->m_inventories->insert_detail($transaction_data['insert_asset'][$x]);
            }
        }

        $this->update($transaction_data['update_group']['data'], $transaction_data['update_group']['PrecNo']);

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

    function reverse_trasaction($transaction_data) {
        $this->db->trans_start();
        if (!empty($transaction_data['delete_invendetail']['PRecNumber'])) {
            for ($x = 0; $x < count($transaction_data['delete_invendetail']['PRecNumber']); $x++) {
                $this->delete_inven_detail($transaction_data['delete_invendetail']['PRecNumber'][$x], $transaction_data['delete_invendetail']['ItemCode'][$x], $transaction_data['delete_invendetail']['BranchCode'][$x]);
            }
        }

        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->m_purchase_order->update($transaction_data['update_po']['data'], $transaction_data['update_po']['POrdNumber']);
        $this->update($transaction_data['update_ri']['data'], $transaction_data['update_ri']['PRecNumber']);

        $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) {
        $this->db->insert($this->detail, $data);
    }

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

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

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

    function update_journal($data, $AccountNumber, $id) {
        $this->db->update($this->prec_journal, $data, array('PRecNumber' => $id, 'PRecAccountNumber' => $AccountNumber));
    }

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

    function delete_journal($prec_no) {
        $this->db->delete($this->prec_journal, array('PRecNumber' => $prec_no));
    }

    function delete_inven_detail($prec_no, $item_code, $branch_code) {
        $this->db->delete($this->inven_detail, array('ReceiptNumber' => $prec_no, 'ItemCode' => $item_code, 'BranchCode' => $branch_code));
    }

    function set_finish_invoice($id) {
        $this->db->select('*');
        $this->db->where('PRecNumber', $id);
        $this->db->where('ItemQty - ItemQtyPI >', 0);
        $query = $this->db->get($this->detail);

        if ($query->num_rows() == 0) {
            $data_ri = array(
                'TransStatus' => 2,
                'LastUser' => $this->session->userdata("username")
            );

            $this->update($data_ri, $id);

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

    function get_detail_purchase_order_for_ri($POrdNumber, $ItemCode) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('(pod.ItemQty) as ItemQtyPO, (pod.ItemQtyRI + pod.ItemQtySpd) as ItemQtyRI, pod.ItemQtySpd');
        $this->db->from('at_purchaseorder as po');
        $this->db->join('at_porddetail as pod', 'pod.POrdNumber = po.POrdNumber', 'INNER');

        $this->db->where('pod.POrdNumber', $POrdNumber);
        $this->db->where('pod.ItemCode', $ItemCode);

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

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

    function get_detail_item_by_id($POrdNumber) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('(pod.ItemQty) as ItemQtyPO, (pod.ItemQtyRI + pod.ItemQtySpd) as ItemQtyRI');
        $this->db->from('at_purchaseorder as po');
        $this->db->join('at_porddetail as pod', 'pod.POrdNumber = po.POrdNumber', 'INNER');

        $this->db->where('pod.POrdNumber', $POrdNumber);

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

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

    public function check_ri_app_status($pord_number) {
        $finish_preq = 0;
        $this->db->select('at_receiveitem.TransStatus');
        $this->db->from($this->table);
        $this->db->where('at_receiveitem.POrdNumber', $pord_number);

        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $result = $query->result();
            foreach ($result as $row) {
                if (in_array($row->TransStatus, array(0, 1, 2))) {
                    $finish_preq = 1;
                }
            }

            if ($finish_preq == 0) {
                return 0;
            } else {
                return 1;
            }
        } else {
            return 0;
        }
    }
	
	function get_purchase_order($POrdNumber){
		$this->db->select('po.POrdNumber');
        $this->db->from('at_purchaseorder as po');
        $this->db->where('po.POrdNumber', $POrdNumber);
		$this->db->where_in('po.TransStatus', array(0));
        $query_po=$this->db->get();
		
		if($query_po->num_rows() > 0){
			return true;
        }
        else{
            return false;
        }
	}
	
	function get_receive_item($POrdNumber){
		$this->db->select('ri.PRecNumber');
        $this->db->from('at_receiveitem as ri');
        $this->db->where('ri.POrdNumber', $POrdNumber);
		$this->db->where_in('ri.TransStatus', array(1,2));
        $query_po=$this->db->get();
		
		if($query_po->num_rows() > 0){
			return true;
        }
        else{
            return false;
        }
	}
}

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