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

class M_purchase_expense extends CI_Model {
   public $table           = "at_purchasepayment";
    public $payment         = "at_invoicepayment";
    public $detail          = "at_ppaydetail";
    public $journal         = "at_ppayjournal";
    
    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_detail_item_for_pj($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, '0' as Payment_Amount, '0' as Payment_Currency, '0' as Load_Rounding,
                           sum(InvoiceOwing) as Invoice_Owing, InvoiceCurrCode, InvoiceCurrRate, InvoiceFiscalRate, 'No' as Paid, at_invoicepayment.ItemCategoryID, PPnFiscalStatus,
                          (case when InvoiceType = 0 and PPnFiscalStatus = 0 then 'Invoice' 
                                when InvoiceType = 0 and PPnFiscalStatus = 1 then 'PPn Fiscal'
                                else 'Down Payment' end) as ItemCategoryName,
                          (case when InvoiceType = 0 then 'PI' else 'PO' end) as Invoice_Type");
        $this->db->from($this->payment);
		$this->db->join('am_itemcategory','at_invoicepayment.ItemCategoryID=am_itemcategory.ItemCategoryID','LEFT');
        $this->db->where('VendorCode', $vendor_code);
        $this->db->where('InvoiceOwing > ', 0);
		
		$this->db->where('InvoiceType', 0);
        $this->db->group_by('InvoiceType, InvoiceNumber, PPnFiscalStatus');
        $this->db->order_by('InvoiceDate', 'DESC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;
        
        return $query->result();
    }  
	
	function get_detail_invoice_number($si_number, $invoice_type, $invoice_name){
        if($invoice_name == 'PO'){
			$this->db->_protect_identifiers = false;
			$this->db->select("at_invoicepayment.InvoicePaymentID, at_invoicepayment.InvoiceNumber as Invoice_Number, at_invoicepayment.InvoiceDate as Invoice_Date, sum(at_invoicepayment.InvoiceAmount) as Invoice_Amount, sum(at_invoicepayment.InvoiceOwing) as InvoiceAmountCopy, '0' as Payment_Amount, '0' as Payment_Currency, '0' as Load_Rounding,
							   sum(at_pordetaildp.SaldoDPBalance - at_pordetaildp.DPUsed) as Invoice_Owing, (at_pordetaildp.SaldoDPBalance - at_pordetaildp.DPUsed) as InvoiceAmountCopy, at_invoicepayment.InvoiceCurrCode, at_invoicepayment.InvoiceCurrRate, at_invoicepayment.InvoiceFiscalRate, 'No' as Paid, at_invoicepayment.ItemCategoryID, at_invoicepayment.ItemCategoryCode,
							  (case when at_invoicepayment.InvoiceType = 0 and at_invoicepayment.PPnFiscalStatus = 0 then 'Invoice' 
									when at_invoicepayment.InvoiceType = 0 and at_invoicepayment.PPnFiscalStatus = 1 then 'PPn Fiscal'
									else 'Down Payment' end) as ItemCategoryName,
							  (case when at_invoicepayment.InvoiceType = 0 then 'PI' else 'PO' end) as Invoice_Type");
			$this->db->from('at_pordetaildp');
			$this->db->join('at_invoicepayment','at_pordetaildp.POrdNumber=at_invoicepayment.InvoiceNumber','INNER');
			
			$this->db->where('at_invoicepayment.InvoiceNumber', $si_number);
			$this->db->group_by('at_invoicepayment.InvoiceNumber');
			$this->db->order_by('at_invoicepayment.InvoiceDate', 'DESC');
			$query = $this->db->get();
			$this->db->_protect_identifiers = true;
		}
		else{
			$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, '0' as Payment_Amount, '0' as Payment_Currency, '0' as Load_Rounding,
							   sum(InvoiceOwing) as Invoice_Owing, InvoiceCurrCode, InvoiceCurrRate, InvoiceFiscalRate, 'No' as Paid, ItemCategoryID,ItemCategoryCode,
							  (case when InvoiceType = 0 and PPnFiscalStatus = 0 then 'Invoice' 
									when InvoiceType = 0 and PPnFiscalStatus = 1 then 'PPn Fiscal'
									else 'Down Payment' end) as ItemCategoryName,
							  (case when InvoiceType = 0 then 'PI' else 'PO' end) as Invoice_Type");
			$this->db->from($this->payment);
			$this->db->where('InvoiceNumber', $si_number);
			$this->db->where('InvoiceOwing > ', 0);

			
			if($invoice_type==0){
				$this->db->where('PPnFiscalStatus <>', 1);
			}
			else{
				$this->db->where('PPnFiscalStatus', 1);
			}
			
			$this->db->group_by('InvoiceNumber');
			$this->db->order_by('InvoiceDate', 'DESC');
			$query = $this->db->get();
			$this->db->_protect_identifiers = true;
		}
        return $query->result();
    }  
	
	function get_data_dp($VendorCode) {
		$this->db->_protect_identifiers = false;
        $this->db->select("at_pordetaildp.POrdNumber,InvoicePaymentID, InvoiceNumber as Invoice_Number, InvoiceDate as Invoice_Date, sum(InvoiceAmount) as Invoice_Amount, sum(InvoiceOwing) as InvoiceAmountCopy, '0' as Payment_Amount, '0' as Payment_Currency, '0' as Load_Rounding,
                           sum(InvoiceOwing) as Invoice_Owing, InvoiceCurrCode, InvoiceCurrRate, InvoiceFiscalRate, 'No' as Paid, ItemCategoryID, PPnFiscalStatus,
                          (case when InvoiceType = 0 and PPnFiscalStatus = 0 then 'Invoice' 
                                when InvoiceType = 0 and PPnFiscalStatus = 1 then 'PPn Fiscal'
                                else 'Down Payment' end) as ItemCategoryName,
                          (case when InvoiceType = 0 then 'PI' else 'PO' end) as Invoice_Type
		");
        $this->db->from('at_pordetaildp');
		$this->db->join('at_invoicepayment','at_pordetaildp.POrdNumber=at_invoicepayment.InvoiceNumber','INNER');
		
        $this->db->where('at_pordetaildp.VendorCode', $VendorCode);
		$this->db->where('(at_pordetaildp.DPUsed - at_pordetaildp.SaldoDPBalance) != ', 0);
        $this->db->order_by('at_pordetaildp.POrdNumber', 'DESC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;
        
        return $query->result();
    }
	
	function get_detail_expense_invoice($Number){
        $this->db->select("(case when at_ppaydetail.PPayPaidStatus = 1 then 'Yes' else 'No' end) as Paid,
                           (case when at_ppaydetail.PPayInvoiceType = 0 then 'PI' when at_ppaydetail.PPayInvoiceType = 1 then 'PO' when at_ppaydetail.PPayInvoiceType = 2 then 'PPN' else '-' end) as Invoice_Type,
                            at_ppaydetail.PPayDetailID, at_ppaydetail.PPayNumber, at_ppaydetail.PInvNumber as Invoice_Number, at_ppaydetail.PPayFiscalStatus as PPnFiscalStatus, at_ppaydetail.PPayInvoiceType as InvoiceTypeID, 
                            at_ppaydetail.PInvDate as Invoice_Date, at_ppaydetail.PInvAmount as Invoice_Amount, at_ppaydetail.PPayAmount as PaymentAmountCopy, at_ppaydetail.PInvAmountTemp as InvoiceAmountCopy, at_ppaydetail.InvoicePaymentID,
                            at_ppaydetail.PInvOwing as Invoice_Owing, at_ppaydetail.PInvOwing as OwingHidden, at_purchasepayment.CurrencyCode as InvoiceCurrCode,
                             
                            at_ppaydetail.PPayAmount as Payment_Amount, at_ppaydetail.ItemCategoryID, at_ppaydetail.ItemCategoryCode, at_ppaydetail.CategoryName as ItemCategoryName, at_ppaydetail.PPayDetailID,
							
							at_ppaydetail.PInvFiscalRate as InvoiceFiscalRate, at_ppaydetail.PInvCurrRate as InvoiceCurrRate, at_ppaydetail.PPayCurrency as PaymentCurrency");
        $this->db->from($this->table);
        $this->db->join($this->detail, 'at_purchasepayment.PPayNumber = at_ppaydetail.PPayNumber', 'INNER');
        $this->db->join('am_itemCategory', 'am_itemCategory.ItemCategoryID = at_ppaydetail.ItemCategoryID', 'LEFT');
        $this->db->where('at_purchasepayment.PPayNumber', $Number);
        $query = $this->db->get();
        
        return $query->result();
    }
	
	function check_code_exist($Number) {
        $query = $this->db->get_where($this->table, array('PPayNumber' => $Number, 'PaymentStatus' =>1), 1, 0);
		
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }	
    }
	
	function create_pj_number($PPayDate){
        $branch     = $this->session->userdata('branch');
        
        if($PPayDate != ''){        
            $month      = date('m', strtotime($PPayDate));
            $year       = date('y', strtotime($PPayDate));
        }
        else{
            $month	= date('m');
            $year	= date('y');
        }

        $pp_number  = $branch."/".$month.$year."PJ/";

        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(PPayNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(PPayNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('PPayNumber', $pp_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_pp_number = $pp_number . $number;
        
        while($this->check_code_exist($new_pp_number)){
            $num++;
            $long = strlen($num);
            
            if($long == 1){
                $number = '00' . $num;
            }
            elseif($long == 2){
                $number = '0' . $num;
            }
            else{
                $number = $num;
            }
            
            $new_pp_number = $pp_number . $number;		
        }
    
        return $new_pp_number;
    }
	
	function get_detail_journal($ppay_number){
        $this->db->select("pp.*, GLSaldoBalance" . CURRENT_PERIOD . " as BankBalance, br.BranchName, cp.CompanyName,
                           vd.VendorName, vd.VendorAddress, vd.VendorCity, vd.VendorState, vd.VendorCountry, vd.SaldoDPPartial, 
                           vd.VendorPostalCode, vd.SaldoDPBalance, vd.SaldoDPUsed, cy.CurrencyCode, cy.CurrencyName, cy.CurrencyRate");
        $this->db->from($this->table . ' as pp');
        $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = pp.BankPayment', 'INNER');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join($this->vendor . ' as vd', 'vd.VendorCode = pp.VendorCode', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = pp.BranchCode', 'INNER');
        $this->db->join($this->company . ' as cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->currency . ' as cy','cy.CurrencyCode = pp.CurrencyCode', 'INNER');
        $this->db->where('pp.PPayNumber', $ppay_number);        
        $query = $this->db->get();
        
        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }      
    }
	
	function get_detail_expense($ppay_number){
        $this->db->select("pp.*, pd.*, GLSaldoBalance" . CURRENT_PERIOD . " as BankBalance, br.BranchName, cp.CompanyName,
                           vd.VendorName, vd.VendorAddress, vd.VendorCity, vd.VendorState, vd.VendorCountry, vd.SaldoDPPartial, 
                           vd.VendorPostalCode, vd.SaldoDPBalance, vd.SaldoDPUsed, cy.CurrencyCode, cy.CurrencyName, cy.CurrencyRate");
        $this->db->from($this->table . ' as pp');
		$this->db->join($this->detail . ' as pd', 'pp.PPayNumber = pd.PPayNumber', 'INNER');
		
        $this->db->join($this->comp_gl . ' as cg', 'cg.AccountNumber = pp.BankPayment', 'INNER');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');

        $this->db->join($this->vendor . ' as vd', 'vd.VendorCode = pp.VendorCode', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = pp.BranchCode', 'INNER');
        $this->db->join($this->company . ' as cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->currency . ' as cy','cy.CurrencyCode = pp.CurrencyCode', 'INNER');
        $this->db->where('pp.PPayNumber', $ppay_number);  
		$this->db->where('pp.PaymentStatus', 1);        
        $query = $this->db->get();
        
        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }      
    }
	
	function get_detail_invoice_print($ppay_number){
        $this->db->_protect_identifiers = false;
        $this->db->select("(case when ppd.PPayPaidStatus = 1 then 'Yes' else 'No' end) as Paid,
                           (case when ppd.PPayInvoiceType = 0 then 'PI' else 'PO' end) as Invoice_Type,
                            ppd.PPayDetailID, ppd.InvoicePaymentID, pp.PPayNumber as PaymentNumber, ppd.PInvNumber as Invoice_Number, 
                            ppd.PInvDate as Invoice_Date, sum(ppd.PInvAmount) as Invoice_Amount, sum(PInvOwing) as Invoice_Owing, 
                            ppd.PPayDiscDate as Payment_DiscDate, sum(ppd.PPayDiscount) as Payment_Discount, sum(ppd.PPayDPUsed) as Payment_DP, sum(ppd.PPayRounded) as Load_Rounding, 
                            sum(ppd.PPayAmount) as Payment_Amount, sum(ppd.PPayCurrency) as Payment_Currency, 
                            ppd.PInvCurrRate as InvoiceCurrRate, ppd.ItemCategoryID, 0 as UnPaid,
                           (case when PPayInvoiceType = 0 then 'Invoice' else 'Down Payment' end) as PaymentType,
                           (case when ppd.PPayInvoiceType = 1 then 0 else sum(ppd.PPayDPUsed) end) as Old_Payment_DP,ppd.CategoryName,
                           (case when PPayInvoiceType = 0 and PPayFiscalStatus = 0 then 'Invoice' 
                                 when PPayInvoiceType = 0 and PPayFiscalStatus = 1 then 'PPn Fiscal'
                                 else 'Down Payment' end) as ItemCategoryCode");
        $this->db->from($this->table . ' as pp');
        $this->db->join($this->detail . ' as ppd', 'ppd.PPayNumber = pp.PPayNumber', 'LEFT');
        $this->db->where('pp.PPayNumber', $ppay_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();
    }
	
	function get_detail_print($ppay_number){
        $this->db->select("pp.*,  br.BranchName, cp.CompanyName,ag.AccountName,
                           vd.VendorName, vd.VendorAddress, vd.VendorCity, vd.VendorState, vd.VendorCountry, 
                           vd.VendorPostalCode,vd.SaldoDPPartial, vd.SaldoDPBalance, vd.SaldoDPUsed,vd.PhoneNumber, cy.CurrencyCode, cy.CurrencyName, cy.CurrencyRate,
                           cr.EmployeeName as nm_prepared, mng.EmployeeName as nm_manager, dir.EmployeeName as nm_direktur");
        $this->db->from($this->table . ' as pp');
        $this->db->join($this->account_gl . ' as ag', 'ag.AccountNumber = pp.BankPayment', 'INNER');
        $this->db->join($this->vendor . ' as vd', 'vd.VendorCode = pp.VendorCode', 'INNER');
        $this->db->join($this->branch . ' as br', 'br.BranchCode = pp.BranchCode', 'INNER');
        $this->db->join($this->company . ' as cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->currency . ' as cy','cy.CurrencyCode = pp.CurrencyCode', 'INNER');
        $this->db->join($this->employee . ' as cr', 'cr.EmployeeCode = pp.CreatedBy', 'LEFT');
        $this->db->join($this->employee . ' as mng', 'mng.EmployeeCode = pp.AppLevel1Code', 'LEFT');
        $this->db->join($this->employee . ' as dir', 'mng.EmployeeCode = pp.AppLevel2Code', 'LEFT');
        $this->db->where('pp.PPayNumber', $ppay_number);        
        $query = $this->db->get();
        
        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }      
    }
	
	function get_account_expense(){
		$CurrencyCode = $this->input->get('CurrencyCode');
		
        $this->db->select('at_compaccountgl.AccountNumber,am_accountgl.AccountName,am_accountgl.CurrencyCode');
        
		$this->db->from('at_compaccountgl');
        $this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber','INNER');
        
		$this->db->join('am_accountsubtype','am_accountsubtype.AccountSubTypeID=am_accountgl.AccountSubTypeID','INNER');
		$this->db->join('am_accounttype','am_accounttype.AccountTypeID=am_accountsubtype.AccountTypeID','INNER');
      
        
		$this->db->where('at_compaccountgl.CompanyCode',$this->session->userdata('company'));
		
        $this->db->where('at_compaccountgl.BranchCode', $this->session->userdata('branch'));
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        //$this->db->where_in('am_accounttype.AccountTypeName',array('Other Expense','Expense','Revenue','Other Revenue'));
        $this->db->where('am_accountgl.AccountChild',1);
        $this->db->where('at_compaccountgl.TransStatus',2);
		
        $this->db->where_in('am_accountgl.CurrencyCode', array('IDR'));            

        return $this->db->get()->result();
    }
	
	function get_m_account_gl($BankCode){
		$this->db->select('am_accountsubtype.AccountTypeName as SubtypeName, am_accounttype.AccountTypeName as TypeName');
		$this->db->from('am_accountgl');
      	$this->db->join('am_accountsubtype','am_accountsubtype.AccountSubTypeID=am_accountgl.AccountSubTypeID','INNER');
		$this->db->join('at_compaccountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber','INNER');
		$this->db->join('am_accounttype','am_accounttype.AccountTypeID=am_accountsubtype.AccountTypeID','INNER');
		
		$this->db->where('at_compaccountgl.CompanyCode',$this->session->userdata('company'));
        $this->db->where('at_compaccountgl.BranchCode', $this->session->userdata('branch'));
		
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('at_compaccountgl.TransStatus',2);
		$this->db->where('at_compaccountgl.AccountNumber', $BankCode);
        $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 */