<?php

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

class M_purchase_giro extends CI_Model {

    public $table = "at_purchasegiro";
    public $payment = "at_invoicepayment";
    public $detail = "at_pgirdetail";
    public $bank = "am_bank";
    public $vendor = "am_vendor";
    public $branch = "am_branch";
    public $company = "am_company";
    public $currency = "am_currency";
    public $account_gl = "am_accountgl";
    public $comp_gl = "at_compaccountgl";
    public $employee = "am_employee";

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

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

        $DateFrom = isset($param['txt_DateFrom']) ? $param['txt_DateFrom'] : '';
        $DateTo = isset($param['txt_DateTo']) ? $param['txt_DateTo'] : '';
        $TransStatus = isset($param['chk_TransStatus']) ? $param['chk_TransStatus'] : '';
        $BranchCode = isset($param['cmb_branch']) ? $param['cmb_branch'] : '';
        $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_purchasegiro.PGirNumber, at_purchasegiro.PGirDate, at_purchasegiro.PGirDesc,br_paid.BranchName as BranchPaid, at_purchasegiro.TransStatus,
							am_branch.BranchName,am_vendor.VendorName, created.EmployeeName as CreatedBy, at_purchasegiro.PGirTotalAmount");
        $this->db->from($this->table);

        $this->db->join('am_vendor', 'at_purchasegiro.VendorCode=am_vendor.VendorCode', 'INNER');
        $this->db->join('am_branch', 'at_purchasegiro.BranchCode=am_branch.BranchCode', 'INNER');
		$this->db->join('am_branch as br_paid', 'at_purchasegiro.BranchPaidFor=br_paid.BranchCode', 'INNER');
		$this->db->join('am_employee as created', 'created.EmployeeCode=at_purchasegiro.CreatedBy', 'INNER');

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

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

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

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

    function get_detail_item_for_pgiro($vendor_code) {
        $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', 0);
        $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 check_code_exist($pinv_number) {
        $query = $this->db->get_where($this->table, array('PGirNumber' => $pinv_number), 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 . "PG/";

        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(PGirNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(PGirNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('PGirNumber', $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 get_payment($invoice_number, $invoice_type, $ppn_fiscal) {
        $this->db->select('*');
        $this->db->from($this->payment);
        $this->db->where('InvoiceNumber', $invoice_number);
        $this->db->where('InvoiceType', $invoice_type);
        $this->db->where('PPnFiscalStatus', $ppn_fiscal);
        $this->db->order_by('InvoicePaymentID', 'ASC');
        $query = $this->db->get();

        return $query->result();
    }
	
	function insert($data) {
        $this->db->insert($this->table, $data);
    }
	
	function insert_detail($data) {
        $this->db->insert($this->detail, $data);
    }
	
	function update($data, $id) {
        $this->db->update($this->table, $data, array('PGirNumber' => $id));
    }

    function update_detail($data, $id) {
        $this->db->update($this->detail, $data, array('PGirDetailID' => $id));
    }
	
	function get_detail($ppay_number) {
        $this->db->select("pg.*, cg.GLSaldoBSheet as BankBalance, br.BranchName, cp.CompanyName, 
                           bk.BankOutCode, vd.VendorCode, vd.VendorName, vd.VendorAddress, vd.VendorCity, vd.VendorState, vd.VendorCountry, vd.SaldoDPPartial, 
                           vd.VendorPostalCode, vd.SaldoDPBalance,vd.SaldoDPReturn, vd.SaldoDPUsed, cy.CurrencyCode, cy.CurrencyName, cy.CurrencyRate");
        $this->db->from($this->table . ' as pg');
        $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = pg.BankPayment', 'INNER');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->bank . ' as bk', 'bk.BankID = ag.BankID', 'INNER');
        $this->db->join($this->vendor . ' as vd', 'vd.VendorCode = pg.VendorCode', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = pg.BranchCode', 'INNER');
        $this->db->join($this->company . ' as cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->currency . ' as cy', 'cy.CurrencyCode = pg.CurrencyCode', 'INNER');
        $this->db->where('pg.PGirNumber', $ppay_number);

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

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }
	
	function get_detail_invoice($ppay_number) {
        $this->db->_protect_identifiers = false;
        $this->db->select("(case when pgd.PGirPaidStatus = 1 then 'Yes' else 'No' end) as Paid,
                           (case when pgd.PGirInvoiceType = 0 then 'PI' else 'PO' end) as Invoice_Type, pgd.PInvDate as Invoice_Date,
                            pgd.PGirDetailID, pgd.InvoicePaymentID, pg.PGirNumber as PaymentNumber, pgd.PInvNumber as Invoice_Number,sum(pgd.PGirRounded) as Load_Rounding,sum(pgd.PInvAmount) as Invoice_Amount
							,sum(pgd.PInvOwing) as Invoice_Owing,sum(pgd.PGirAmount) as Payment_Amount,sum(pgd.PGirCurrency) as Payment_Currency,
							(case when PGirInvoiceType = 0 then 'Invoice' 
                                 when PGirInvoiceType = 0 then 'PPn Fiscal'
                                 else 'Down Payment' end) as ItemCategoryCode");
        $this->db->from($this->table . ' as pg');
        $this->db->join($this->detail . ' as pgd', 'pgd.PGirNumber = pg.PGirNumber', 'LEFT');
        $this->db->where('pg.PGirNumber', $ppay_number);
        $this->db->group_by('Invoice_Number');
        $this->db->order_by('PGirDate', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = false;

        return $query->result();
    }
	
	function get_pgir_detail($pinv_number, $ppay_number) {
        $this->db->select('*');
        $this->db->from($this->detail);
        $this->db->where('PInvNumber', $pinv_number);
        $this->db->where('PGirNumber', $ppay_number);
        $this->db->order_by('PGirDetailID', 'ASC');
        $query = $this->db->get();

        return $query->result();
    }
	
	function get_payment_by_id($invoice_payment_id) {
        $this->db->select('*');
        $this->db->from($this->payment);
        $this->db->where('InvoicePaymentID', $invoice_payment_id);
        $query = $this->db->get();

        return $query->result();
    }
	
	function get_purchase_giro_status_on_progres($pinv_number) {
        $this->db->select('sum(pgd.PGirRounded + pgd.PGirAmount) as PaymentAmount');
        $this->db->from($this->detail. ' as pgd');
		$this->db->join('at_purchasegiro', 'pgd.PGirNumber = at_purchasegiro.PGirNumber', 'INNER');
        $this->db->where('pgd.PInvNumber', $pinv_number);
		$this->db->where_in('at_purchasegiro.TransStatus', array(1,5));
		$this->db->group_by('pgd.PInvNumber');
        $query = $this->db->get();

        return $query->row();
    }
	
	function get_purchase_invoice($pinv_number) {
        $this->db->select('PInvTotalDP, PInvTotalAmount');
        $this->db->from('at_purchaseinvoice');
        $this->db->where('at_purchaseinvoice.PInvNumber', $pinv_number);
        $query = $this->db->get();

        return $query->row();
    }
	
	function get_purchase_payment($pinv_number) {
        $this->db->select('sum(at_ppaydetail.PPayRounded + at_ppaydetail.PPayDiscount + at_ppaydetail.PPayDPUsed + at_ppaydetail.PPayAmount) as PaymentAmount');
        $this->db->from('at_ppaydetail');
		$this->db->join('at_purchasepayment', 'at_ppaydetail.PPayNumber = at_purchasepayment.PPayNumber', 'INNER');
        $this->db->where('at_ppaydetail.PInvNumber', $pinv_number);
		$this->db->where('at_purchasepayment.ChequeNumber', "");
		$this->db->where_in('at_purchasepayment.TransStatus', array(5));
		$this->db->group_by('at_ppaydetail.PInvNumber');
        $query = $this->db->get();

        return $query->row();
    }
	
	function get_purchase_payment_finish($pinv_number) {
        $this->db->select('sum(at_ppaydetail.PPayRounded + at_ppaydetail.PPayDiscount + at_ppaydetail.PPayDPUsed + at_ppaydetail.PPayAmount) as PaymentAmount');
        $this->db->from('at_ppaydetail');
		$this->db->join('at_purchasepayment', 'at_ppaydetail.PPayNumber = at_purchasepayment.PPayNumber', 'INNER');
        $this->db->where('at_ppaydetail.PInvNumber', $pinv_number);
		$this->db->where_in('at_purchasepayment.TransStatus', array(2));
		$this->db->group_by('at_ppaydetail.PInvNumber');
        $query = $this->db->get();

        return $query->row();
    }
	
	function get_purchase_journal($pinv_number) {
        $this->db->select('sum(PJrnAmount) as PaymentAmount');
        $this->db->from('at_purchasejournal');
        $this->db->where('at_purchasejournal.PInvNumber', $pinv_number);
		$this->db->where_in('at_purchasejournal.TransStatus', array(2,5));
		$this->db->group_by('at_purchasejournal.PInvNumber');
        $query = $this->db->get();

        return $query->row();
    }
	
	function get_purchase_payment_reverse($pgirnumber) {
        $this->db->select('PGirNumber');
        $this->db->from('at_purchasepayment');
        $this->db->where('at_purchasepayment.PGirNumber', $pgirnumber);
		$this->db->where_in('at_purchasepayment.TransStatus', array(2,5));
        $query = $this->db->get();

        if ($query->num_rows() > 0){
            return true;
        }
        else{
            return false;
        }
    }
	
	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'];
        $PaymentStatus  = $data_filter['cmb_PaymentStatus'];
        /* ============================================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_purchasegiro.PGirNumber, at_purchasegiro.PGirDate, at_purchasegiro.PGirDesc,
                            at_purchasegiro.PGirTotalAmount,at_purchasegiro.DateGiro,
                            am_branch.BranchName,am_vendor.VendorName
                            ,(case 
                            when at_purchasegiro.TransStatus = 1 then 'On Progress'
                            when at_purchasegiro.TransStatus = 2 then 'Finished'
                            when at_purchasegiro.TransStatus = 3 then 'Suspend'
                            when at_purchasegiro.TransStatus = 4 then 'Need App. Mgr.'
                            when at_purchasegiro.TransStatus = 5 then 'Need App. Dir.'
                            when at_purchasegiro.TransStatus = 6 then 'Need App. Dir. & Mgr.'
                            when at_purchasegiro.TransStatus = 9 then 'Canceled by Dir.'
                            else 'Unknown' end) as TransStatus");
        $this->db->from($this->table);

        $this->db->join('am_vendor', 'at_purchasegiro.VendorCode=am_vendor.VendorCode', 'INNER');
        $this->db->join('am_branch', 'at_purchasegiro.BranchCode=am_branch.BranchCode', 'INNER');

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

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

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

        /* =====================================================find by status================================================== */
        if (!empty($TransStatus)) {
            $this->db->where_in('at_purchasegiro.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_purchasegiro.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 . '.PGirNumber', 'ASC');
        /* ===============================end================================================================================= */
        $query = $this->db->get();
        return $query->result();
    }
	
	function get_detail_print($pgir_number) {
        $this->db->select("pg.*, GLSaldoBalance" . CURRENT_PERIOD . " as BankBalance, br.BranchName, cp.CompanyName,
                           bk.BankOutCode, bk.BankName,bk.BankCode, vd.VendorName, vd.VendorAddress, vd.VendorCity, vd.VendorState, vd.VendorCountry, 
                           vd.VendorPostalCode, vd.SaldoDPBalance, vd.SaldoDPUsed,vd.PhoneNumber, cy.CurrencyCode, cy.CurrencyName, cy.CurrencyRate,
                           cr.EmployeeName as nm_prepared, mng.EmployeeName as nm_manager");
        $this->db->from($this->table . ' as pg');
        $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = pg.BankPayment', 'INNER');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->bank . ' as bk', 'bk.BankID = ag.BankID', 'INNER');
        $this->db->join($this->vendor . ' as vd', 'vd.VendorCode = pg.VendorCode', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = pg.BranchCode', 'INNER');
        $this->db->join($this->company . ' as cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->currency . ' as cy', 'cy.CurrencyCode = pg.CurrencyCode', 'INNER');
        $this->db->join($this->employee . ' as cr', 'cr.EmployeeCode = pg.CreatedBy', 'LEFT');
        $this->db->join($this->employee . ' as mng', 'mng.EmployeeCode = pg.AppLevel1Code', 'LEFT');
        $this->db->where('pg.PGirNumber', $pgir_number);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }
	
	function get_detail_invoice_print($pgir_number) {
        $this->db->_protect_identifiers = false;
        $this->db->select("(case when pgd.PGirPaidStatus = 1 then 'Yes' else 'No' end) as Paid,
                           (case when pgd.PGirInvoiceType = 0 then 'PI' else 'PO' end) as Invoice_Type,
                            pgd.PGirDetailID, pgd.InvoicePaymentID, pg.PGirNumber as PaymentNumber, pgd.PInvNumber as Invoice_Number, 
                            pgd.PInvDate as Invoice_Date, sum(pgd.PInvAmount) as Invoice_Amount, sum(PInvOwing) as Invoice_Owing, 
                            sum(pgd.PGirRounded) as Load_Rounding, 
                            sum(pgd.PGirAmount) as Payment_Amount, sum(pgd.PGirCurrency) as Payment_Currency, 
                             0 as UnPaid,
                           (case when PGirInvoiceType = 0 then 'Invoice' else 'Down Payment' end) as PaymentType");
        $this->db->from($this->table . ' as pg');
        $this->db->join($this->detail . ' as pgd', 'pgd.PGirNumber = pg.PGirNumber', 'LEFT');
        $this->db->where('pg.PGirNumber', $pgir_number);
        $this->db->group_by('Invoice_Number');
        $this->db->order_by('PInvDate', 'ASC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = false;

        return $query->result_array();
    }
}

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