<?php

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

class M_purchase_invoice extends CI_Model {

    public $table = "at_purchaseinvoice";
    public $payment = "at_invoicepayment";
    public $detail = "at_pinvdetail";
    public $detailrecur = "at_pinvdetailrecur";
    public $branch = "am_branch";
    public $company = "am_company";
    public $journal = "at_pinvjournal";
    public $coa = "am_accountgl";
    public $item = "am_item";
    public $vendor = "am_vendor";
    public $employee = "am_employee";
    public $shipment = "am_shipment";
    public $termspayment = "am_termspayment";

    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_purchaseinvoice.*, am_branch.BranchName, am_vendor.VendorName, created.EmployeeName as CreatedByName");
        $this->db->from($this->table);
        $this->db->join('am_vendor', 'at_purchaseinvoice.VendorCode=am_vendor.VendorCode', 'INNER');
        $this->db->join('am_branch', 'am_branch.BranchCode=at_purchaseinvoice.BranchCode', 'INNER');
		$this->db->join('am_employee as created', 'created.EmployeeCode=at_purchaseinvoice.CreatedBy', 'INNER');

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

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

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

        /* =====================================================find by status================================================== */
        if ($TransStatus != '') {
            $this->db->where_in('at_purchaseinvoice.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_purchaseinvoice.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 . '.PInvNumber', '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_purchaseinvoice.*, am_branch.BranchName, am_vendor.VendorName"
                . ",(case 
                        when at_purchaseinvoice.TransStatus = 0 then 'Need DP'
                        when at_purchaseinvoice.TransStatus = 1 then 'On Progress'
                        when at_purchaseinvoice.TransStatus = 2 then 'Finished'
                        when at_purchaseinvoice.TransStatus = 3 then 'Suspend'
                        when at_purchaseinvoice.TransStatus = 4 then 'Need App. Mgr.'
                        when at_purchaseinvoice.TransStatus = 5 then 'Need App. Dir.'
                        when at_purchaseinvoice.TransStatus = 6 then 'Need App. Dir. & Mgr.'
                        when at_purchaseinvoice.TransStatus = 9 then 'Canceled by Dir.'
                        else 'Unknown' end) as Status");
        $this->db->from($this->table);
        $this->db->join('am_vendor', 'at_purchaseinvoice.VendorCode=am_vendor.VendorCode', 'INNER');
        $this->db->join('am_branch', 'am_branch.BranchCode=at_purchaseinvoice.BranchCode', 'INNER');

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

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

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

        /* =====================================================find by status================================================== */
        if (!empty($TransStatus)) {
            $this->db->where_in('at_purchaseinvoice.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_purchaseinvoice.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 . '.PInvNumber', '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_purchaseinvoice.*,
						 	(case 
								when at_purchaseinvoice.TransStatus = 0 then 'Waiting' 
								when at_purchaseinvoice.TransStatus = 1 then 'On Progress' 
								when at_purchaseinvoice.TransStatus = 2 then 'Finished' 
								when at_purchaseinvoice.TransStatus = 3 then 'Suspended'
								when at_purchaseinvoice.TransStatus = 4 then 'Need App. Mgr.'  
								when at_purchaseinvoice.TransStatus = 5 then 'Need App. Dir.' 
								when at_purchaseinvoice.TransStatus = 6 then 'App. Dir & Mgr' end) as Status,
								
							am_branch.BranchName, am_vendor.VendorName");
        $this->db->from($this->table);
        $this->db->join('am_vendor', 'at_purchaseinvoice.VendorCode=am_vendor.VendorCode', 'INNER');
        $this->db->join('am_branch', 'am_branch.BranchCode=at_purchaseinvoice.BranchCode', 'INNER');

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

    function get_pi_data($pinv_number) {
        $this->db->select('pi.*, br.BranchName, cp.CompanyName, vd.VendorAddress, vd.SaldoDPBalance, vd.SaldoDPUsed');
        $this->db->from($this->table . ' as pi');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = pi.BranchCode', 'INNER');
        $this->db->join($this->company . ' as cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->vendor . ' vd', 'vd.VendorCode = pi.VendorCode', 'INNER');
        $this->db->where('PInvNumber', $pinv_number);
        $query = $this->db->get();

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

    function get_pi_data_print($pi_number) {
        $this->db->select('pi.*, br.BranchName, cp.CompanyName, vd.VendorName, vd.PhoneNumber, vd.VendorAddress, vd.SaldoDPBalance, vd.SaldoDPUsed, cr.EmployeeName as nm_prepared, mng.EmployeeName as nm_manager, dir.EmployeeName as nm_direktur, ship.ShipmentName,
            (case when pi.FOBID = 1 then "Shiping Point" when pi.FOBID = 2 then "Destination" else "No" end) as FOB, payment.TermDesc');
        $this->db->from($this->table . ' as pi');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = pi.BranchCode', 'INNER');
        $this->db->join($this->company . ' as cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->vendor . ' vd', 'vd.VendorCode = pi.VendorCode', 'INNER');
        $this->db->join($this->employee . ' as cr', 'cr.EmployeeCode = pi.CreatedBy', 'LEFT');
        $this->db->join($this->employee . ' as mng', 'mng.EmployeeCode = pi.AppLevel1Code', 'LEFT');
        $this->db->join($this->employee . ' as dir', 'dir.EmployeeCode = pi.AppLevel2Code', 'LEFT');
        $this->db->join($this->shipment . ' as ship', 'ship.ShipmentID = pi.ShipmentID', 'LEFT');
        $this->db->join($this->termspayment . ' as payment', 'payment.TermspaymentID = pi.TermspaymentID', 'LEFT');
        $this->db->where('PInvNumber', $pi_number);
        $query = $this->db->get();

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

    function get_item_by_categorty($pinvnumber, $itemcategori_id) {
        $this->db->where('PInvNumber', $pinvnumber);
        $this->db->where('ItemCategoryID', $itemcategori_id);
        $this->db->order_by('PInvDetailID', 'Asc');
        $query = $this->db->get($this->detail);

        return $query->result();
    }

    function get_detail_item($pinv_number) {
        $this->db->_protect_identifiers = false;
        $this->db->select("pi.BranchCode, pi.VendorCode, pi.PInvDate, pi.PInvNumber, pi.VendorAddress, pi.CurrencyCode, 
                           pi.CurrencyRate, pi.PInvPPn10Pcnt, pi.PInvPPh22Pcnt, pi.PInvTaxesStatus, pi.PInvInTaxStatus,
                           pid.PInvDetailID, pid.ItemCode, pid.ItemDesc, pid.ItemQty, pid.ItemUnit, pid.ItemPrice, pid.ItemType, pid.ItemCodeVendor,  
                           pid.PRecNumber, pid.ItemAmount, pid.ItemCategoryID, pid.ItemCategoryCode, pid.ItemPPh22Status, pid.ItemDisc as ItemDiscPcnt, 
                           pid.ItemTaxPPn10, pid.ItemTaxPPh22, pid.ItemTaxPPh23, pid.ItemTotalAmount, br.BranchName, vnd.VendorName, 
                           pid.ItemQty as InvoiceQty, '0' as Item_Qty, 'No' as Paid, pid.ItemCode as Item_Code, pid.ItemStatus, pid.ItemAdvNetDueIn, ((pid.ItemQty * pid.ItemPrice)*(pid.ItemDisc/ 100)) as AmountDisc, 
						   pid.ItemAmount as ItemAmount_Hide, pid.ItemPrice as ItemPrice_Hide, pid.ItemAmount as ItemAmount_Hide, pid.ItemTotalAmount as Item_TotalAmount_Hide, 
						   pid.ItemTaxPPh22 as Item_TaxPPh22_Hide, pid.ItemTaxPPn10 as Item_TaxPPn10_Hide");
        $this->db->from($this->table . ' pi');
        $this->db->join($this->detail . ' pid', 'pid.PInvNumber = pi.PInvNumber', 'INNER');
        $this->db->join($this->vendor . ' vnd', 'vnd.VendorCode = pi.VendorCode', 'INNER');
        $this->db->join($this->branch . ' br', ' br.BranchCode = pi.BranchCode', 'INNER');
        $this->db->where('pi.PInvNumber', $pinv_number);
        //$this->db->where('(pid.ItemQty - pid.ReturnQty) != ', 0);
        // $this->db->where('pi.LockedStatus', 0);
        $this->db->order_by('pid.PInvDetailID', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

        return $query->result();
    }

    //Daswan get detail item to print
    function get_detail_item_print($PInvNumber) {
        $this->db->_protect_identifiers = false;
        $this->db->select("pi.BranchCode, pi.VendorCode, pi.PInvDate, pi.PInvNumber, pi.VendorAddress, pi.CurrencyCode, 
                           pi.CurrencyRate, pi.PInvPPn10Pcnt, pi.PInvPPh22Pcnt, pi.PInvTaxesStatus, pi.PInvInTaxStatus,pid.ItemDisc,pid.ItemCodeVendor,
                           pid.PInvDetailID, pid.ItemCode, pid.ItemDesc, pid.ItemQty, pid.ItemUnit, pid.ItemPrice, pid.ItemType, 
                           pid.PRecNumber, pid.ItemAmount, pid.ItemCategoryID, pid.ItemCategoryCode, pid.ItemPPh22Status, 
                           pid.ItemTaxPPn10, pid.ItemTaxPPh22, pid.ItemTaxPPh23, pid.ItemTotalAmount, br.BranchName, vnd.VendorName, 
                           pid.ItemQty as InvoiceQty, '0' as Item_Qty, 'No' as Paid, pid.ItemCode as Item_Code, pid.ItemStatus, pid.ItemAdvNetDueIn");
        $this->db->from($this->table . ' pi');
        $this->db->join($this->detail . ' pid', 'pid.PInvNumber = pi.PInvNumber', 'INNER');
        $this->db->join($this->vendor . ' vnd', 'vnd.VendorCode = pi.VendorCode', 'INNER');
        $this->db->join($this->branch . ' br', ' br.BranchCode = pi.BranchCode', 'INNER');
        $this->db->where('pi.PInvNumber', $PInvNumber);
        $this->db->where('pi.LockedStatus', 0);
        $this->db->order_by('pid.PInvDetailID', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

        return $query->result_array();
    }

//    function get_detail_item_to_print_journal($pinv_number){
//        $this->db->select("pi.*, pd.*, pd.ItemQty as ItemMax, ab.BranchName, av.VendorName");
//        $this->db->from($this->table . ' pi');
//        $this->db->join('at_pinvdetail pd', 'pd.PInvNumber = pi.PInvNumber', 'INNER');		
//        $this->db->join('am_vendor av','av.VendorCode = pi.VendorCode', 'INNER');		
//        $this->db->join('am_branch ab','ab.BranchCode = pi.BranchCode', 'INNER');
//        $this->db->where('pi.PInvNumber', $pinv_number);
//        $query = $this->db->get();
//
//        if($query->num_rows() > 0){
//            return $query->row();
//        }
//        else{
//            return false;
//        }      
//    }

    function get_summary_pi($ppaynumber) {
        $this->db->select('at_pinvdetail.PInvNumber, sum(PPayItemDPUsed) as Total_DPUsed, sum(PPayItemDiscount) as Total_Discount,  
                           sum(PPayItemAmount) as Total_Payment, sum(PPayItemOwing) as Total_Owing');
        $this->db->join('at_ppaydetail', 'at_ppaydetail.PInvNumber = at_pinvdetail.PInvNumber', 'INNER');
        $this->db->where('at_ppaydetail.PPayNumber', $ppaynumber);
        $this->db->group_by('at_pinvdetail.PInvNumber');
        $this->db->order_by('at_pinvdetail.PInvNumber', 'ASC');
        $query = $this->db->get($this->detail);

        return $query->result();
    }

    function get_detail_item_group_by_category($id) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("sum(at_pinvdetail.ItemAmount)as CategoryAmount,am_item.ItemCategoryID,PInvTaxesStatus,PInvInTaxStatus");
        $this->db->join($this->table, "at_purchaseinvoice.PInvNumber=at_pinvdetail.PInvNumber", "INNER");
        $this->db->join("am_item", "am_item.ItemCode=at_pinvdetail.ItemCode", "INNER");
        $query = $this->db->get_where($this->detail, array('at_pinvdetail.PInvNumber' => $id));
        $this->db->group_by('ItemCategoryID');
        $this->db->_protect_identifiers = TRUE;

        return $query->result();
    }

    function get_journal_by_number($Number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("at_pinvjournal.*, am_accountgl.AccountNumber, am_accountgl.AccountName");
        $this->db->join($this->coa, "am_accountgl.AccountNumber = at_pinvjournal.PInvAccountNumber", "INNER");
        $query = $this->db->get_where($this->journal, array('at_pinvjournal.PInvNumber' => $Number));
        $this->db->_protect_identifiers = TRUE;

        return $query->result();
    }

    function get_detail_item_for_pp($vendor_code, $ppn_fiscal) {
        $this->db->_protect_identifiers = false;
        $this->db->select("InvoicePaymentID, InvoiceNumber as Invoice_Number, InvoiceDate as Invoice_Date, sum(InvoiceAmount) as Invoice_Amount, sum(InvoiceOwing) as InvoiceAmountCopy, sum(InvoiceOwing) as InvoiceOwingHidden, sum(InvoiceOwing) as InvoiceOwingHiddenNew, '0' as Payment_Amount, '0' as Payment_Currency, '0' as Load_Rounding,
                           sum(InvoiceOwing) as Invoice_Owing, InvoiceCurrCode, InvoiceCurrRate, InvoiceFiscalRate, 'No' as Paid, ItemCategoryID, ItemCategoryRatio,
                          (case when InvoiceType = 0 and PPnFiscalStatus = 0 then 'Invoice' 
                                when InvoiceType = 0 and PPnFiscalStatus = 1 then 'PPn Fiscal'
                                else 'Down Payment' end) as ItemCategoryCode,
                          (case when InvoiceType = 0 then 'PI' else 'PO' end) as Invoice_Type");
        $this->db->from($this->payment);
        $this->db->where('VendorCode', $vendor_code);
        $this->db->where('PPnFiscalStatus', $ppn_fiscal);
        $this->db->where('ROUND(InvoiceOwing, 2) > ', 0);
        $this->db->where('LockedStatus', 0);
        $this->db->where('TransStatus', 0);
        $this->db->group_by('InvoiceNumber');
        $this->db->order_by('InvoiceNumber', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

        return $query->result();
    }

    function get_for_purchase_return($VendorCode) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('at_purchaseinvoice.*');
        $this->db->from($this->table);
        $this->db->join('at_pinvdetail', 'at_purchaseinvoice.PInvNumber = at_pinvdetail.PInvNumber', 'INNER');

        $this->db->where($this->table . '.BranchCode', $this->session->userdata('branch'));
        $this->db->where($this->table . '.VendorCode', $VendorCode);
        $this->db->where($this->table . '.LockedStatus', 0);
        $this->db->where('(at_pinvdetail.ItemQty - at_pinvdetail.ReturnQty) > ', 0);

        $this->db->where_in($this->table . '.TransStatus', array(1, 2));
        $this->db->where_in('at_pinvdetail.ItemType', array(0, 1));

        $this->db->group_by('at_pinvdetail.PInvNumber, at_purchaseinvoice.PInvNumber');
        $this->db->order_by($this->table . '.PInvNumber', 'ASC');

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

    function get_for_purchase_return_adv($VendorCode) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('at_purchaseinvoice.*');
        $this->db->from($this->table);
        $this->db->join('at_pinvdetail', 'at_purchaseinvoice.PInvNumber = at_pinvdetail.PInvNumber', 'INNER');

        $this->db->where($this->table . '.BranchCode', $this->session->userdata('branch'));
        $this->db->where($this->table . '.VendorCode', $VendorCode);
        $this->db->where($this->table . '.LockedStatus', 0);
        $this->db->where('(at_pinvdetail.ItemQty - at_pinvdetail.ReturnQty) > ', 0);

        $this->db->where_in($this->table . '.TransStatus', array(1, 2));
        $this->db->where_in('at_pinvdetail.ItemType', array(2));

        $this->db->group_by('at_pinvdetail.PInvNumber, at_purchaseinvoice.PInvNumber');
        $this->db->order_by($this->table . '.PInvNumber', 'ASC');

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

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

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

    function check_approval_status($PInvNumber) {
        $query = $this->db->get_where($this->table, array('PInvNumber' => $PInvNumber, 'AppLevel1Status' => 1, 'AppLevel2Status' => 1), 1, 0);

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

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

        $pi_number = $branch . "/" . $month . $year . "PI/";

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

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

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

            $new_pi_number = $pi_number . $number;
        }

        return $new_pi_number;
    }

    function insert_transaction($trans_data) {
        $this->db->trans_start();
        $this->insert($trans_data['insert_group']['data']);
        for ($x = 0; $x < count($trans_data['insert_detail']['data']); $x++) {
            $this->insert_detail($trans_data['insert_detail']['data'][$x]);
        }

        for ($x = 0; $x < count($trans_data['update_ri']['data']); $x++) {
            $this->m_receive_item->update($trans_data['update_ri']['data'][$x], $trans_data['update_ri']['PRecNumber'][$x]);
        }

        /*
          Insert Journal
		  journal_unbilled
         */
        $this->insert_journal($trans_data['journal_unbilled']['data']);
        /* =================================================== */

        /*
          Biaya Jasa atau Biaya DiBayar DiMuka
         */
        for ($x = 0; $x < count($trans_data['journal_expense']['data']); $x++) {
            if ($trans_data['journal_expense']['data'][$x] != NULL) {
                $this->insert_journal($trans_data['journal_expense']['data'][$x]);
            }
        }
        /* =================================================== */

        /*
          journal_freight
         */
        if ($trans_data['journal_freight']['total'] > 0) {
            $this->insert_journal($trans_data['journal_freight']['data']);
        }
        /* =================================================== */

        /*
          Insert PPn
         */
        if ($trans_data['journal_taxppn10']['total'] > 0) {
            if ($trans_data['journal_taxppn10']['currency'] == 'IDR') {
                if (!empty($trans_data['journal_taxppn10']['data'])) {
                    for ($x = 0; $x < count($trans_data['journal_taxppn10']['data']); $x++) {
                        $this->insert_journal($trans_data['journal_taxppn10']['data'][$x]);
                    }
                }

                if (!empty($trans_data['journal_taxppn10']['data'])) {
                    for ($x = 0; $x < count($trans_data['journal_update_ppn']['data']); $x++) {
                        $this->update_journal($trans_data['journal_update_ppn']['PInvNumber'][$x], $trans_data['journal_update_ppn']['data'][$x], $trans_data['journal_update_ppn']['PInvAccountNumber'][$x]);
                    }
                }
            }
        }
        /* =================================================== */

        /*
          Insert PPh
         */
        if ($trans_data['journal_taxpph22']['total'] > 0) {
            if ($trans_data['journal_taxpph22']['data'][$x] != NULL) {
                if (!empty($trans_data['journal_taxpph22']['data'])) {
                    for ($x = 0; $x < count($trans_data['journal_taxpph22']['data']); $x++) {
                        $this->insert_journal($trans_data['journal_taxpph22']['data'][$x]);
                    }
                }
            }

            if ($trans_data['journal_taxpph22_update']['data'][$x] != NULL) {
                if (!empty($trans_data['journal_taxpph22_update']['data'])) {
                    for ($x = 0; $x < count($trans_data['journal_taxpph22_update']['data']); $x++) {
                        $this->update_journal($trans_data['journal_taxpph22_update']['PInvNumber'][$x], $trans_data['journal_taxpph22_update']['data'][$x], $trans_data['journal_taxpph22_update']['PInvAccountNumber'][$x]);
                    }
                }
            }
        }
        /* =================================================== */

        /* =====================End============================ */

        $this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }
	
	function reverse_trasaction($trans_data){
		$this->db->trans_start();
		if (!empty($trans_data['delete_recurdetail']['PInvNumber'])) {
			for ($x = 0; $x < count($trans_data['delete_recurdetail']['PInvNumber']); $x++) {
				$this->delete_recur($trans_data['delete_recurdetail']['PInvNumber'][$x], $trans_data['delete_recurdetail']['ItemCode'][$x]);
			}
		}
		
		for ($x = 0; $x < count($trans_data['update_group_ri']['data']); $x++) {
			$this->m_receive_item->update($trans_data['update_group_ri']['data'][$x], $trans_data['update_group_ri']['PRecNumber'][$x]);
		}
		
		$this->update($trans_data['update_group']['data'], $trans_data['update_group']['PInvNumber']);
		
		$this->delete_payment($trans_data['delete_invoice_payment']['InvoiceNumber']);
		
				 
		$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_detail_recur($data) {
        $this->db->insert($this->detailrecur, $data);
    }

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

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

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

    function update_journal($txt_no, $data, $id) {
        $this->db->update($this->journal, $data, array('PInvNumber' => $txt_no, 'PInvAccountNumber' => $id));
    }

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

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

    function update_payment($data, $invoice_number) {
        $this->db->update($this->payment, $data, array('InvoiceNumber' => $invoice_number));
    }
	
	function update_po_detail_dp_by_vendor($data, $VendorCode, $POrdNumber, $POrdDetailDPID) {
        $this->db->update('at_pordetaildp', $data, array('VendorCode' => $VendorCode, 'POrdDetailDPID' => $POrdDetailDPID, 'POrdNumber' => $POrdNumber));
    }
	
    function delete_payment($invoice_number) {
        $this->db->delete($this->payment, array('InvoiceNumber' => $invoice_number));
    }
	
	function delete_recur($pinv_number, $ItemCode) {
        $this->db->delete($this->detailrecur, array('PInvNumber' => $pinv_number, 'ItemCode' => $ItemCode));
    }


    function set_finish_invoice($id) {
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('PInvNumber', $id);
        $this->db->where('PInvTotalOwing', 0);
        $query = $this->db->get();

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

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

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

    function check_code_exist_pord_number($pord_number) {
        $this->db->select('POrdNumber');
        $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 $query->row();
        } else {
            return false;
        }
    }

    function get_detail_item_return($pinv_number) {
        $this->db->_protect_identifiers = false;
        $this->db->select("pi.BranchCode, pi.VendorCode, pi.PInvDate, pi.PInvNumber, pi.VendorAddress, pi.CurrencyCode, 
                           pi.CurrencyRate, pi.PInvPPn10Pcnt, pi.PInvPPh22Pcnt, pi.PInvTaxesStatus, pi.PInvInTaxStatus,
                           pid.PInvDetailID, pid.ItemCode, pid.ItemDesc, pid.ItemQty, pid.ItemUnit, pid.ItemPrice, pid.ItemType, pid.ItemDisc, pid.ItemStatus as ItemAssetStatus, 
                           pid.PRecNumber, pid.PRecNumber as RINumber, pid.ItemAmount, pid.ItemCategoryID, pid.ItemCategoryCode, pid.ItemPPh22Status, pid.ItemDisc as ItemDiscPcnt, 
                           pid.ItemTaxPPn10, pid.ItemTaxPPh22, pid.ItemTaxPPh23, pid.ItemTotalAmount, br.BranchName, vnd.VendorName, 
                           (pid.ItemQty - pid.ReturnQty) as InvoiceQty, 'No' as Paid,'0' as Item_Qty, pid.ItemCode as Item_Code, pid.ItemStatus, pid.ItemAdvNetDueIn, ((pid.ItemAmount * pid.ItemDisc) / 100) as AmountDisc, 
						   pid.ItemAmount as ItemAmount_Hide, pid.ItemPrice as ItemPrice_Hide, pid.ItemAmount as ItemAmount_Hide, pid.ItemTotalAmount as Item_TotalAmount_Hide, 
						   pid.ItemTaxPPh22 as Item_TaxPPh22_Hide, pid.ItemTaxPPn10 as Item_TaxPPn10_Hide");
        $this->db->from($this->table . ' pi');
        $this->db->join($this->detail . ' pid', 'pid.PInvNumber = pi.PInvNumber', 'INNER');
        $this->db->join($this->vendor . ' vnd', 'vnd.VendorCode = pi.VendorCode', 'INNER');
        $this->db->join($this->branch . ' br', ' br.BranchCode = pi.BranchCode', 'INNER');
        $this->db->where('pi.PInvNumber', $pinv_number);
        $this->db->where('(pid.ItemQty - pid.ReturnQty) > ', 0);

        $this->db->where('pi.LockedStatus', 0);
        //$this->db->order_by('pid.PInvDetailID', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

        return $query->result();
    }

    function get_detail_item_adv($pinv_number) {
        $this->db->_protect_identifiers = false;
        $this->db->distinct();
        $this->db->select("pi.BranchCode, pi.VendorCode, pi.PInvDate, pi.PInvNumber, pi.VendorAddress, pi.CurrencyCode, 
                           pi.CurrencyRate, pi.PInvPPn10Pcnt, pi.PInvPPh22Pcnt, pi.PInvTaxesStatus, pi.PInvInTaxStatus,
                           pid.PInvDetailID, pid.ItemCode, pid.ItemDesc, pid.ItemQty, pid.ItemUnit, pid.ItemPrice, pid.ItemType,pid.ItemDisc, '0' as ItemAssetStatus,
                           pid.PRecNumber, pid.ItemAmount, pid.ItemCategoryID, pid.ItemCategoryCode, pid.ItemPPh22Status, pid.ItemDisc as ItemDiscPcnt, 
                           pid.ItemTaxPPn10, pid.ItemTaxPPh22, pid.ItemTaxPPh23, pid.ItemTotalAmount, br.BranchName, vnd.VendorName, 
                           (pid.ItemQty - pid.ReturnQty) as InvoiceQty, '0' as Item_Qty, 'No' as Paid, pid.ItemCode as Item_Code, pid.ItemStatus, pid.ItemAdvNetDueIn, ((pid.ItemAmount * pid.ItemDisc) / 100) as AmountDisc, 
						   pid.ItemAmount as ItemAmount_Hide, pid.ItemPrice as ItemPrice_Hide, pid.ItemAmount as ItemAmount_Hide, pid.ItemTotalAmount as Item_TotalAmount_Hide, 
						   pid.ItemTaxPPh22 as Item_TaxPPh22_Hide, pid.ItemTaxPPn10 as Item_TaxPPn10_Hide");
        $this->db->from($this->table . ' pi');
        $this->db->join($this->detail . ' pid', 'pid.PInvNumber = pi.PInvNumber', 'INNER');
        $this->db->join($this->vendor . ' vnd', 'vnd.VendorCode = pi.VendorCode', 'INNER');
        $this->db->join($this->branch . ' br', ' br.BranchCode = pi.BranchCode', 'INNER');
        $this->db->join('at_pinvdetailrecur as recc', ' recc.PInvDetailID = pid.PInvDetailID', 'INNER');

        $this->db->where('pi.PInvNumber', $pinv_number);
        $this->db->where('(pid.ItemQty - pid.ReturnQty) > ', 0);
        $this->db->where('pi.LockedStatus', 0);
        $this->db->where('(recc.ItemTotalAmount - recc.PaymentAmount) > ', 0);

        $this->db->order_by('pid.PInvDetailID', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

        return $query->result();
    }

    function get_purchase_invoice($pinv_number) {
        $this->db->select('POrdNumber, VendorCode');
        $this->db->from($this->table);
        $this->db->where('PInvNumber', $pinv_number);
        $this->db->where_in('TransStatus', array(1, 2));
        $query = $this->db->get();

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

    function get_journal_purchase_invoice($PInvAccountNumber, $pinv_number) {
        $this->db->select('PInvSaldoCredit, PInvSaldoDebet, PInvAccountNumber, AmountCurrency');
        $this->db->from($this->journal);
        $this->db->where('PInvNumber', $pinv_number);
        $this->db->where('PInvAccountNumber', $PInvAccountNumber);

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

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }
	
	function get_saldo_detail_dp_reverse($id, $POrdNumber, $akun_number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("at_pordetaildp.SOrdStatus, at_pordetaildp.JVoucherBalance, at_pordetaildp.POrdTotalDP, at_pordetaildp.Owing, 
							at_pordetaildp.PaymentAmount, at_pordetaildp.VendorCode, at_pordetaildp.SaldoDPBalance, at_pordetaildp.POrdNumber, 
							at_pordetaildp.CurrencyRate, at_pordetaildp.DPUsedBook, at_pordetaildp.IsEdit, at_pordetaildp.POrdDetailDPID, 
							at_pordetaildp.DPUsed, at_pordetaildp.PaymentBefore, at_pinvjournal.AmountCurrency");
        $this->db->join('at_purchaseinvoice', 'at_purchaseinvoice.PInvNumber = at_pinvjournal.PInvNumber', 'INNER');
        $this->db->join('at_pordetaildp', 'at_pordetaildp.POrdDetailDPID = at_pinvjournal.POrdDetailDPID', 'INNER');

        $this->db->from('at_pinvjournal');
        $this->db->where('at_pordetaildp.VendorCode', $id);

        $this->db->where('at_pordetaildp.POrdNumber', $POrdNumber);
        $this->db->where('at_pinvjournal.PInvAccountNumber', $akun_number);
        $this->db->where_in('at_purchaseinvoice.TransStatus', array(1,2));

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

        return $query->result();
    }
    /* function get_detail_recur_from_return($PInvNumber){
      $this->db->select('PInvNumber, SUM(ItemTotalAmount) as ItemTotalAmount, SUM(PaymentAmount) as PaymenAmount');
      $this->db->from('at_pinvdetailrecur');
      $this->db->where('PInvNumber', $PInvNumber);
      $this->db->where('ItemType', 2);
      $query = $this->db->get();

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

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