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

class M_sales_expense extends CI_Model {
    public $table           = "at_salesreceipt";
    public $detail          = "at_srecdetail";
    public $journal         = "at_srecjournal";
    public $branch          = "am_branch";
    public $customer        = "am_customer";
    public $employee        = "am_employee";
    public $shipment        = "am_shipment";
    public $termspayment    = "am_termspayment";
	public $sorddetaildp    = "at_sorddetaildp";
	public $receipt 		= "at_invoicereceipt";
	
    
	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 check_code_exist($sr_number) {
        $query = $this->db->get_where($this->table, array('SRecNumber' => $sr_number), 1, 0);
		
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }	
    }
    
    function create_sr_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');
        }
		
		$sr_number  = $branch."/".$month.$year."SJ/";
        //$sr_number	= $branch ."/". $month . $year ."SR/";
		
        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(SRecNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(SRecNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('SRecNumber', $sr_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_sr_number = $sr_number . $number;

        while($this->check_code_exist($new_sr_number)){
            $num++;
            $long = strlen($num);
    
            if($long == 1){
                $number = '00' . $num;
            }
            elseif($long == 2){
                $number = '0' . $num;
            }
            else{
                $number = $num;
            }
            
            $new_sr_number = $sr_number . $number;		
        }
        
        return  $new_sr_number;
    }
	
	function get_for_expense($CustomerCode) {
		$this->db->_protect_identifiers = false;
        $this->db->select("
			InvoicePaymentID, InvoiceNumber, InvoiceDate, SUM(InvoiceAmount) as InvoiceAmount, SUM(InvoiceOwing) as InvoiceAmountCopy, 
			(SUM(InvoiceAmount) - (SUM(ReceiptAmount) + SUM(ReceiptDiscount) + SUM(ReceiptDPUsed) + SUM(ReceiptRounded))) as InvoiceOwing, (SUM(InvoiceAmount) - SUM(ReceiptAmount)) as OwingHidden, 0 as ReceiptTotalDP, 0 as ReceiptDiscount, '' as ReceiptDiscDate, 0 as BebanPembulatan,
			0 as PaymentAmount, 0 as PaymentCurrency, at_invoicereceipt.ItemCategoryID, at_invoicereceipt.ItemCategoryCode, at_invoicereceipt.InvoiceCurrRate, at_invoicereceipt.InvoiceCurrCode, 
			(case when InvoiceType = 0 and PPnFiscalStatus = 0 then 'Invoice' 
								when InvoiceType = 0 and PPnFiscalStatus = 1 then 'Invoice'
                                when InvoiceType = 2 and PPnFiscalStatus = 1 then 'PPn Fiscal'
                                else 'Down Payment' end) as ItemCategoryName,
			(case when InvoiceType = 0 then 'SI' when InvoiceType = 1 then 'SO' when InvoiceType = 2 then 'PPN' else '-' end) as InvoiceType, 'No' as Paid, at_invoicereceipt.InvoiceFiscalRate, at_invoicereceipt.DPStatus, at_invoicereceipt.PPnFiscalStatus as PPnFiscalStatus, at_invoicereceipt.InvoiceType as InvoiceTypeID,
			(case when InvoiceType = 0 then 'Invoice' when InvoiceType = 1 then 'Down Payment' when InvoiceType = 2 then 'Tax' else '-' end) as InvoiceTypeName
		");
        $this->db->from($this->receipt);
		$this->db->join('am_itemcategory','at_invoicereceipt.ItemCategoryID=am_itemcategory.ItemCategoryID','LEFT');
        $this->db->where('CustomerCode', $CustomerCode);
        $this->db->where('InvoiceOwing >', 0);
        $this->db->where('LockedStatus', 0);
		$this->db->where('InvoiceType !=', 1);
		
        $this->db->group_by('InvoiceType, InvoiceNumber');
        $this->db->order_by('InvoiceDate', 'DESC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;
        
        return $query->result();
    }
	
	function get_for_dp($CustomerCode) {
		$this->db->_protect_identifiers = false;
        $this->db->select("at_sorddetaildp.SOrdNumber,at_invoicereceipt.InvoicePaymentID, at_invoicereceipt.InvoiceNumber, at_invoicereceipt.InvoiceDate,
							InvoiceAmount as InvoiceAmount, InvoiceOwing as InvoiceAmountCopy, 
			(InvoiceAmount - (ReceiptAmount + ReceiptDiscount + ReceiptDPUsed + ReceiptRounded)) as InvoiceOwing, (InvoiceAmount - ReceiptAmount) as OwingHidden, 0 as ReceiptTotalDP, 0 as ReceiptDiscount, '' as ReceiptDiscDate, 0 as BebanPembulatan,
			0 as PaymentAmount, 0 as PaymentCurrency, at_invoicereceipt.ItemCategoryID, at_invoicereceipt.ItemCategoryCode, at_invoicereceipt.InvoiceCurrRate, at_invoicereceipt.InvoiceCurrCode, 
			(case when InvoiceType = 0 and at_invoicereceipt.PPnFiscalStatus = 0 then 'Invoice' 
								when InvoiceType = 0 and at_invoicereceipt.PPnFiscalStatus = 1 then 'Invoice'
                                when InvoiceType = 2 and at_invoicereceipt.PPnFiscalStatus = 1 then 'PPn Fiscal'
                                else 'Down Payment' end) as ItemCategoryName,
			(case when InvoiceType = 0 then 'SI' when InvoiceType = 1 then 'SO' when InvoiceType = 2 then 'PPN' else '-' end) as InvoiceType, 'No' as Paid, at_invoicereceipt.InvoiceFiscalRate, at_invoicereceipt.DPStatus, at_invoicereceipt.PPnFiscalStatus as PPnFiscalStatus, at_invoicereceipt.InvoiceType as InvoiceTypeID,
			(case when InvoiceType = 0 then 'Invoice' when InvoiceType = 1 then 'Down Payment' when InvoiceType = 2 then 'Tax' else '-' end) as InvoiceTypeName
		");
        $this->db->from('at_sorddetaildp');
		$this->db->join('at_invoicereceipt','at_sorddetaildp.SOrdNumber=at_invoicereceipt.InvoiceNumber','INNER');
		
        $this->db->where('at_sorddetaildp.CustomerCode', $CustomerCode);
		$this->db->where('(at_sorddetaildp.DPUsed - at_sorddetaildp.SaldoDPBalance) != ', 0);
		//$this->db->group_by('at_invoicereceipt.InvoiceType, at_invoicereceipt.InvoiceNumber');
        $this->db->order_by('at_sorddetaildp.SOrdNumber', 'DESC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;
        
        return $query->result();
    }
	
	function get_for_expense_invoice($InvoiceNumber, $ReceiptTaxStatus='', $invoice_name) {
		$this->db->_protect_identifiers = false;
        if($invoice_name == 'SO'){
			 $this->db->select("at_sorddetaildp.SOrdNumber,at_invoicereceipt.InvoicePaymentID, at_invoicereceipt.InvoiceNumber, at_invoicereceipt.InvoiceDate,
							InvoiceAmount as InvoiceAmount, (at_sorddetaildp.SaldoDPBalance - at_sorddetaildp.DPUsed) as InvoiceAmountCopy, 
				(at_sorddetaildp.SaldoDPBalance - at_sorddetaildp.DPUsed) as InvoiceOwing,(at_sorddetaildp.SaldoDPBalance - at_sorddetaildp.DPUsed) as OwingHidden, 0 as ReceiptTotalDP, 0 as ReceiptDiscount, '' as ReceiptDiscDate, 0 as BebanPembulatan,
				0 as PaymentAmount, 0 as PaymentCurrency, at_invoicereceipt.ItemCategoryID, at_invoicereceipt.ItemCategoryCode, at_invoicereceipt.InvoiceCurrRate, at_invoicereceipt.InvoiceCurrCode, 
				(case when InvoiceType = 0 and at_invoicereceipt.PPnFiscalStatus = 0 then 'Invoice' 
									when InvoiceType = 0 and at_invoicereceipt.PPnFiscalStatus = 1 then 'Invoice'
									when InvoiceType = 2 and at_invoicereceipt.PPnFiscalStatus = 1 then 'PPn Fiscal'
									else 'Down Payment' end) as ItemCategoryName,
				(case when InvoiceType = 0 then 'SI' when InvoiceType = 1 then 'SO' when InvoiceType = 2 then 'PPN' else '-' end) as InvoiceType, 'No' as Paid, at_invoicereceipt.InvoiceFiscalRate, at_invoicereceipt.DPStatus, at_invoicereceipt.PPnFiscalStatus as PPnFiscalStatus, at_invoicereceipt.InvoiceType as InvoiceTypeID,
				(case when InvoiceType = 0 then 'Invoice' when InvoiceType = 1 then 'Down Payment' when InvoiceType = 2 then 'Tax' else '-' end) as InvoiceTypeName
			");
			$this->db->from('at_sorddetaildp');
			$this->db->join('at_invoicereceipt','at_sorddetaildp.SOrdNumber=at_invoicereceipt.InvoiceNumber','INNER');
			$this->db->where('at_sorddetaildp.SOrdNumber', $InvoiceNumber);
			//$this->db->group_by('at_invoicereceipt.InvoiceType, at_invoicereceipt.InvoiceNumber');
			$this->db->order_by('at_sorddetaildp.SOrdNumber', 'DESC');
			$query = $this->db->get();
			$this->db->_protect_identifiers = true;
		}
		else{
			$this->db->select("
				InvoicePaymentID, InvoiceNumber, InvoiceDate, SUM(InvoiceAmount) as InvoiceAmount, SUM(InvoiceOwing) as InvoiceAmountCopy, 
				(SUM(InvoiceAmount) - (SUM(ReceiptAmount) + SUM(ReceiptDiscount) + SUM(ReceiptDPUsed) + SUM(ReceiptRounded))) as InvoiceOwing, (SUM(InvoiceAmount) - SUM(ReceiptAmount)) as OwingHidden, 0 as ReceiptTotalDP, 0 as ReceiptDiscount, '' as ReceiptDiscDate, 0 as BebanPembulatan,
				0 as PaymentAmount, 0 as PaymentCurrency, at_invoicereceipt.ItemCategoryID, at_invoicereceipt.ItemCategoryCode, at_invoicereceipt.InvoiceCurrRate, at_invoicereceipt.InvoiceCurrCode, 
				(case when InvoiceType = 0 and PPnFiscalStatus = 0 then 'Invoice' 
									when InvoiceType = 0 and PPnFiscalStatus = 1 then 'Invoice'
									when InvoiceType = 2 and PPnFiscalStatus = 1 then 'PPn Fiscal'
									else 'Down Payment' end) as ItemCategoryName,
				(case when InvoiceType = 0 then 'SI' when InvoiceType = 1 then 'SO' when InvoiceType = 2 then 'PPN' else '-' end) as InvoiceType, 'No' as Paid, at_invoicereceipt.InvoiceFiscalRate, at_invoicereceipt.DPStatus, at_invoicereceipt.PPnFiscalStatus as PPnFiscalStatus,
				(case when InvoiceType = 0 then 'Invoice' when InvoiceType = 1 then 'Down Payment' when InvoiceType = 2 then 'Tax' else '-' end) as InvoiceTypeName
			");
			$this->db->from($this->receipt);
			$this->db->join('am_itemcategory','at_invoicereceipt.ItemCategoryID=am_itemcategory.ItemCategoryID','LEFT');
			$this->db->where('InvoiceNumber', $InvoiceNumber);
			//$this->db->where('InvoiceOwing >', 0);
			$this->db->where('LockedStatus', 0);
			
			if($ReceiptTaxStatus==0){
				$this->db->where('InvoiceType <>', 2);
			}
			else{
				$this->db->where('InvoiceType', 2);
			}
			
			$this->db->group_by('InvoiceType, InvoiceNumber');
			$this->db->order_by('InvoiceDate', 'DESC');
			$query = $this->db->get();
			$this->db->_protect_identifiers = true;
		}
        return $query->result();
    }
	
	function get_detail($id){
        $this->db->select($this->table.".*,".$this->branch.".BranchName, ".$this->customer.".CustomerName, ".$this->customer.".CustomerAddress, ".$this->customer.".CurrencyCode as CurrCustomer, ".$this->customer.".SaldoDPPartial,
                          ".$this->customer.".SaldoDPBalance, am_accountgl.AccountName");
        $this->db->join($this->branch,$this->branch.".BranchCode=".$this->table.".BranchCode","INNER");
        $this->db->join($this->customer,$this->customer.".CustomerCode=".$this->table.".CustomerCode","INNER");
        $this->db->join("am_accountgl","am_accountgl.AccountNumber=".$this->table.".BankReceipt","INNER");
        $query = $this->db->get_where($this->table, array('SRecNumber' => $id));
	
        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }      
    }
	
	function get_detail_invoice($Number){
        $this->db->select("(case when at_srecdetail.SRecPaidStatus = 1 then 'Yes' else 'No' end) as Paid,
                           (case when at_srecdetail.SRecInvoiceType = 0 then 'SI' when at_srecdetail.SRecInvoiceType = 1 then 'SO' when at_srecdetail.SRecInvoiceType = 2 then 'PPN' else '-' end) as InvoiceType,
                            at_srecdetail.SRecDetailID, at_salesreceipt.SRecNumber, at_srecdetail.SInvNumber as InvoiceNumber, at_srecdetail.FiscalStatus as PPnFiscalStatus, 
                            at_srecdetail.SInvDate as InvoiceDate, at_srecdetail.SInvAmount as InvoiceAmount, at_srecdetail.SRecAmount as PaymentAmountCopy, at_srecdetail.SInvAmountTemp as InvoiceAmountCopy,
                            at_srecdetail.SRecOwing as InvoiceOwing, at_srecdetail.SRecOwing as OwingHidden, at_srecdetail.SRecDiscDate as ReceiptDiscDate, at_srecdetail.SRecRounded as BebanPembulatan, at_salesreceipt.CurrencyCode as InvoiceCurrCode,
                            at_srecdetail.SRecDiscount as ReceiptDiscount, at_srecdetail.SRecDPUsed as ReceiptTotalDP, 
                            at_srecdetail.SRecAmount as PaymentAmount, at_srecdetail.SRecAmount as DataAmount, at_srecdetail.ItemCategoryID, at_srecdetail.DPStatus, at_srecdetail.ItemCategoryCode, at_srecdetail.CategoryName,
							at_srecdetail.SInvFiscalRate as InvoiceFiscalRate, at_srecdetail.SInvCurrRate as InvoiceCurrRate, at_srecdetail.SRecCurrency as PaymentCurrency,
                           (case when at_srecdetail.SRecInvoiceType = 0 then at_srecdetail.SRecDPUsed else at_srecdetail.SRecAmount end) as ReceiveTotalDP");
        $this->db->from($this->table);
        $this->db->join($this->detail, 'at_salesreceipt.SRecNumber = at_srecdetail.SRecNumber', 'INNER');
        $this->db->join('am_itemCategory', 'am_itemCategory.ItemCategoryID = at_srecdetail.ItemCategoryID', 'LEFT');
        $this->db->where('at_salesreceipt.SRecNumber', $Number);
        $query = $this->db->get();
        
        return $query->result();
    }
	
	function check_app_level_end_grid($inv_number){
		$this->db->select("sr.AppLevel2Status, sr.AppLevel1Status, sr.CustomerCode, sr.BranchCode, sr.SRecTotalAmount, ssd.SInvNumber");
        $this->db->from($this->table . ' as sr');
		$this->db->join($this->detail . ' as srd', 'srd.SRecNumber = sr.SRecNumber', 'INNER');
		 
        $this->db->where('sr.SInvNumber', $inv_number);        
        $query = $this->db->get();
        
        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }      
    }
}