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

class M_sales_invoice extends CI_Model {
    public $table 			= "at_salesinvoice";
    public $detail 			= "at_sinvdetail";
	public $receipt 		= "at_invoicereceipt";
    public $journal 		= "at_sinvjournal";
    public $branch 			= "am_branch";
    public $customer 		= "am_customer";
    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'] : '';
		$cmb_CustomerCode	= isset($param['cmb_CustomerCode']) ? $param['cmb_CustomerCode'] : '';
		/*============================================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_salesinvoice.TransStatus,	at_salesinvoice.SInvNumber, at_salesinvoice.SInvDate, at_salesinvoice.SInvDesc, am_employee.EmployeeName as CreatedBy,
		at_salesinvoice.DOrdNumber, at_salesinvoice.DOrdDate, at_salesinvoice.SOrdNumber, at_salesinvoice.CustPONumber,
		at_salesinvoice.CustomerCode, at_salesinvoice.BranchCode, am_customer.CustomerName, am_branch.BranchName");
        $this->db->from('at_salesinvoice');
        //$this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesinvoice.SalesmanCode', 'inner');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesinvoice.CustomerCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesinvoice.BranchCode', 'inner');
		$this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesinvoice.CreatedBy', 'inner');
		
        /*===================================find by number dan desc=========================================================*/
		if($Number != ""){$this->db->like('at_salesinvoice.SInvNumber', $Number);}
		if($Desc != ""){$this->db->like('at_salesinvoice.SInvDesc', $Desc);}
		if($cmb_CustomerCode != ""){$this->db->where('am_customer.CustomerCode', $cmb_CustomerCode);}
		/*=============================================end===================================================================*/
		
		/*===================================find by branch code=========================================================*/
		/*
			get data dari  combobox find di view
		*/
        if($BranchCode != ""){
			$this->db->where('at_salesinvoice.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_salesinvoice.BranchCode', $sess_branch);
			//-----------------------end-------------------------------------------------//
		}
		/*=======================================================end========================================================*/
		
		/*=====================================================find by date==================================================*/
		if((($DateFrom != '') && ($DateTo != ''))){
            if($DateFrom <= $DateTo){
				$this->db->where('at_salesinvoice.SInvDate >=', date("Y-m-d", strtotime($DateFrom)));
            	$this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo)));
			}
        }
		/*======================================================end===========================================================*/
		
		/*=====================================================find by status==================================================*/
		if($TransStatus!=''){ 
			$this->db->where_in('at_salesinvoice.TransStatus',$TransStatus); 
		}
		else{
			/*
				trans status 0,1 adalah aktif
			*/
			$this->db->where_in('at_salesinvoice.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.'.SInvNumber', 'ASC');
		/*===============================end=================================================================================*/
		$query  = $this->db->limit($end, $beginning);
		$query = $this->db->get();
		return $query->result();
    }
	
	function get_all_print($param){
		/*=========================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'] : '';
		$cmb_CustomerCode	= isset($param['cmb_CustomerCode']) ? $param['cmb_CustomerCode'] : '';
		/*============================================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_salesinvoice.TransStatus,	at_salesinvoice.SInvNumber, at_salesinvoice.SInvDate, at_salesinvoice.SInvDesc, am_employee.EmployeeName as CreatedBy,
		at_salesinvoice.DOrdNumber, at_salesinvoice.DOrdDate, at_salesinvoice.SOrdNumber, at_salesinvoice.CustPONumber,
		at_salesinvoice.CustomerCode, at_salesinvoice.BranchCode, am_customer.CustomerName, 
		(case 
                                when at_salesinvoice.TransStatus = 0 then 'Waiting' 
                                when at_salesinvoice.TransStatus = 1 then 'On Progress' 
                                when at_salesinvoice.TransStatus = 2 then 'Active' 
                                when at_salesinvoice.TransStatus = 3 then 'Suspended' 
                                when at_salesinvoice.TransStatus = 4 then 'Need Mgr. App.' 
                                when at_salesinvoice.TransStatus = 5 then 'Need Dir. App.' 
                                when at_salesinvoice.TransStatus = 6 then 'Need Dir & Mgr App.' end) as Status, am_branch.BranchName");
        $this->db->from('at_salesinvoice');
        //$this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesinvoice.SalesmanCode', 'inner');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesinvoice.CustomerCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesinvoice.BranchCode', 'inner');
		$this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesinvoice.CreatedBy', 'inner');
		
        /*===================================find by number dan desc=========================================================*/
		if($Number != ""){$this->db->like('at_salesinvoice.SInvNumber', $Number);}
		if($Desc != ""){$this->db->like('at_salesinvoice.SInvDesc', $Desc);}
		if($cmb_CustomerCode != ""){$this->db->where('am_customer.CustomerCode', $cmb_CustomerCode);}
		/*=============================================end===================================================================*/
		
		/*===================================find by branch code=========================================================*/
		/*
			get data dari  combobox find di view
		*/
        if($BranchCode != ""){
			$this->db->where('at_salesinvoice.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_salesinvoice.BranchCode', $sess_branch);
			//-----------------------end-------------------------------------------------//
		}
		/*=======================================================end========================================================*/
		
		/*=====================================================find by date==================================================*/
		if((($DateFrom != '') && ($DateTo != ''))){
            if($DateFrom <= $DateTo){
				$this->db->where('at_salesinvoice.SInvDate >=', date("Y-m-d", strtotime($DateFrom)));
            	$this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo)));
			}
        }
		/*======================================================end===========================================================*/
		
		/*=====================================================find by status==================================================*/
		if($TransStatus!=''){ 
			$this->db->where_in('at_salesinvoice.TransStatus',$TransStatus); 
		}
		else{
			/*
				trans status 0,1 adalah aktif
			*/
			$this->db->where_in('at_salesinvoice.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.'.SInvNumber', 'ASC');
		/*===============================end=================================================================================*/
		$query  = $this->db->limit($end, $beginning);
		$query = $this->db->get();
		return $query->result();
    }
	
    function get_detail($id){
        $this->db->select("at_salesinvoice.*, (case when at_salesinvoice.FOBID=1 then 'Shiping Point' when at_salesinvoice.FOBID=2 then 'Destination' else 'No' end) as FOB,".$this->branch.".BranchName,".$this->customer.".CustomerName,".$this->customer.".TaxIncludedStatus,".$this->termspayment.".TermDesc");
        $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($this->employee,$this->employee.".EmployeeCode=".$this->table.".SalesmanCode","INNER");
        //$this->db->join($this->shipment,$this->shipment.".ShipmentID=".$this->table.".ShipmentID","INNER");
        $this->db->join($this->termspayment,$this->termspayment.".TermspaymentID=".$this->table.".TermsPaymentID","INNER");
        $query = $this->db->get_where($this->table, array('SInvNumber' => $id));

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


    // Daswan function get detail to print
    function get_detail_print($id){
        $this->db->select("at_salesinvoice.*, (case when at_salesinvoice.FOBID=1 then 'Shiping Point' when at_salesinvoice.FOBID=2 then 'Destination' else 'No' end) as FOB,".$this->branch.".BranchName,".$this->customer.".CustomerName,".$this->customer.".TaxIncludedStatus,".$this->termspayment.".TermDesc,".$this->customer.".CustomerAddress,".$this->customer.".PhoneNumber,".$this->shipment.".ShipmentName,
            (case when at_salesinvoice.FOBID = 1 then 'Shiping Point' when at_salesinvoice.FOBID = 2 then 'Destination' else 'No' end) as FOB,(receipt.InvoiceOwing + receipt.JVoucherBalance) as outstanding, prepared.EmployeeName as nm_prepared, mng.EmployeeName as nm_manager");
        $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($this->receipt." as receipt","receipt.InvoiceNumber=".$this->table.".SInvNumber","LEFT");
        $this->db->join($this->shipment,$this->shipment.".ShipmentID=".$this->table.".ShipmentID","LEFT");
        $this->db->join($this->employee." as prepared","prepared.EmployeeCode=".$this->table.".CreatedBy","LEFT");
         $this->db->join($this->employee." as mng","mng.EmployeeCode=".$this->table.".AppLevel1Code","LEFT");
        $this->db->join($this->termspayment,$this->termspayment.".TermspaymentID=".$this->table.".TermsPaymentID","INNER");
        $query = $this->db->get_where($this->table, array('SInvNumber' => $id));

        if($query->num_rows()>0){
            return $query->row();
        }
        else{
            return false;
        }      
    }	
	
    function get_detail_item($SInvNumber){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('at_sinvdetail.ItemQty as max_item, at_sinvdetail.ItemDesc, at_sinvdetail.ItemPPH23, at_sinvdetail.ItemCategoryID, at_sinvdetail.ItemCategoryCode,
						   at_sinvdetail.SInvDetailID, at_sinvdetail.SInvNumber, (at_sinvdetail.ItemAmount + at_sinvdetail.ItemTax) as ItemTotalAmount,
                           at_sinvdetail.SalesmanCode, at_sinvdetail.ItemCode, at_sinvdetail.ItemQty, at_sinvdetail.ItemUnit, at_sinvdetail.ItemDiscPcnt, at_sinvdetail.ItemType,
                           at_sinvdetail.ItemPrice, at_sinvdetail.ItemAmount, at_sinvdetail.ItemDiscPcnt, at_sinvdetail.ItemTax, at_sinvdetail.ItemAmount as ItemAmountKoma, at_sinvdetail.ItemTax as ItemTaxtKoma,
                           at_sinvdetail.ItemDiscount');
        
        $this->db->where('at_sinvdetail.SInvNumber', $SInvNumber);
		//$this->db->join($this->employee,$this->employee.".EmployeeCode=".$this->detail.".SalesmanCode","INNER");
        $query = $this->db->get('at_sinvdetail');
		$this->db->order_by('at_sinvdetail.SInvDetailID', 'ASC');
        $this->db->_protect_identifiers = TRUE;

        return $query->result();
    }

    //Daswan get detail item to print
    function get_detail_item_print($SInvNumber){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('at_sinvdetail.ItemQty as max_item, at_sinvdetail.ItemDesc, at_sinvdetail.ItemCategoryID, at_sinvdetail.ItemCategoryCode,
						   at_sinvdetail.SInvDetailID, at_sinvdetail.SInvNumber, (at_sinvdetail.ItemAmount + at_sinvdetail.ItemTax) as ItemTotalAmount,
                           at_sinvdetail.SalesmanCode, at_sinvdetail.ItemCode, at_sinvdetail.ItemQty, at_sinvdetail.ItemUnit, at_sinvdetail.ItemType,
                           at_sinvdetail.ItemPrice, at_sinvdetail.ItemAmount, at_sinvdetail.ItemDiscPcnt, at_sinvdetail.ItemTax, at_sinvdetail.ItemAmount as ItemAmountKoma, at_sinvdetail.ItemTax as ItemTaxtKoma,
                           at_sinvdetail.ItemDiscount');
        
        $this->db->where('at_sinvdetail.SInvNumber', $SInvNumber);
		//$this->db->join($this->employee,$this->employee.".EmployeeCode=".$this->detail.".SalesmanCode","INNER");
        $query = $this->db->get('at_sinvdetail');
        $this->db->_protect_identifiers = TRUE;

        return $query->result_array();
    }
	
	function get_detail_item_for_return($SInvNumber){
        $this->db->_protect_identifiers = FALSE;
        $this->db->select('at_sinvdetail.ItemQty as max_item_copy, at_sinvdetail.ItemDesc, at_sinvdetail.ItemCategoryID, at_sinvdetail.ItemCategoryCode, "0" as ItemQty, at_sinvdetail.QtyReturn as ItemQtyRet, at_sinvdetail.QtyBookReturn as ItemQtyBookRet,
						   at_sinvdetail.SInvDetailID, at_sinvdetail.SInvNumber, (at_sinvdetail.ItemAmount + at_sinvdetail.ItemTax) as ItemTotalAmount, (at_sinvdetail.ItemQty - at_sinvdetail.QtyReturn) as max_item,
                           at_sinvdetail.SalesmanCode, at_sinvdetail.ItemCode, at_sinvdetail.ItemUnit, at_sinvdetail.ItemType,at_sinvdetail.ItemType as ItemStatus,
                           at_sinvdetail.ItemPrice, "0" as ItemAmount, at_sinvdetail.ItemDiscPcnt, "0" as ItemTax, at_sinvdetail.ItemAmount as ItemAmountKoma, at_sinvdetail.ItemTax as ItemTaxtKoma,
                           at_sinvdetail.ItemDiscount, am_employee.EmployeeName as SalesmanName, at_sinvdetail.ItemPriceCogs, "0" as ItemAmountCogs, "0" as Item_Amount_Hide, "0" as Total_Amount_Hide');
        
        $this->db->where('at_sinvdetail.SInvNumber', $SInvNumber);
		$this->db->where('(at_sinvdetail.ItemQty - at_sinvdetail.QtyReturn) > 0');
		$this->db->join($this->employee,$this->employee.".EmployeeCode=".$this->detail.".SalesmanCode","INNER");
        $query = $this->db->get('at_sinvdetail');
        $this->db->_protect_identifiers = TRUE;

        return $query->result();
    }
	
    function get_by_cust($id){
        $this->db->select("(case when at_salesinvoice.TransStatus=1 then 'On Progress'
                                 when at_salesinvoice.TransStatus=2 then 'Finished'
                                 when at_salesinvoice.TransStatus=3 then 'Susppend'
                                 when at_salesinvoice.TransStatus=4 then 'Managerial App'
                                 when at_salesinvoice.TransStatus=5 then 'Direksi App'
                                 when at_salesinvoice.TransStatus=6 then 'Mgr & Dir App'
                                 when at_salesinvoice.TransStatus=7 then 'Approval Limit'
                                 else 'Waiting' end) as Status,
                            at_salesinvoice.SInvNumber, at_salesinvoice.SInvDate, at_salesinvoice.SInvDesc, at_salesinvoice.DOrdNumber, at_salesinvoice.DOrdDate, at_salesinvoice.SOrdNumber, at_salesinvoice.CustPONumber, at_salesinvoice.LockStatus,
                            at_salesinvoice.SInvTaxesStatus, at_salesinvoice.SInvInTaxStatus, at_salesinvoice.CustomerCode, at_salesinvoice.BranchCode, at_salesinvoice.CurrencyCode, 
                            am_customer.CustomerName, am_branch.BranchName");		
        $this->db->from('at_salesinvoice');
        //$this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesinvoice.SalesmanCode', 'inner');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesinvoice.CustomerCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesinvoice.BranchCode', 'inner');
        $this->db->where('at_salesinvoice.BranchCode',$this->session->userdata('branch'));
        $this->db->where('at_salesinvoice.CustomerCode', $id);
        $this->db->where('at_salesinvoice.TransStatus !=', 4);
        $this->db->order_by($this->table.'.SInvNumber','ASC');

        return $this->db->get()->result();
    }
	
	function get_si_detail_for_return($id){
        $this->db->select(" at_salesinvoice.SInvNumber, at_salesinvoice.SInvDate, at_salesinvoice.SInvDesc, at_salesinvoice.SInvPPnPcnt, 
							at_salesinvoice.DOrdNumber, at_salesinvoice.DOrdDate, at_salesinvoice.SOrdNumber, 
							at_salesinvoice.CustPONumber, at_salesinvoice.LockStatus, at_salesinvoice.SInvTaxesStatus, 
							at_salesinvoice.SInvInTaxStatus, at_salesinvoice.CustomerCode, at_salesinvoice.BranchCode, 
							at_salesinvoice.CurrencyCode, am_customer.CustomerName, am_branch.BranchName, at_salesinvoice.CurrencyRate, at_salesinvoice.FiscalRate");		
        $this->db->from('at_salesinvoice');
        $this->db->join('at_sinvdetail', 'at_sinvdetail.SInvNumber = at_salesinvoice.SInvNumber', 'LEFT');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesinvoice.CustomerCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesinvoice.BranchCode', 'inner');
        
		$this->db->where('at_salesinvoice.BranchCode',$this->session->userdata('branch'));
        $this->db->where('at_salesinvoice.CustomerCode', $id);
        $this->db->where('at_salesinvoice.TransStatus !=', 4);
		$this->db->where('(at_sinvdetail.ItemQty - at_sinvdetail.QtyReturn) != ', 0);
		$this->db->where('at_salesinvoice.LockStatus', 0);
		
		$this->db->group_by('at_sinvdetail.SInvNumber, SInvNumber');
        $this->db->order_by($this->table.'.SInvNumber','ASC');

        return $this->db->get()->result();
    }
	
	
	function get_detail_item_for_sr($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, '0' as Receipt_Amount, '0' as Receipt_Currency, '0' as Load_Rounding,
                           sum(InvoiceOwing) as Invoice_Owing, InvoiceCurrCode, InvoiceCurrRate, InvoiceFiscalRate, 'No' as Paid, ItemCategoryID, ItemCategoryRatio, sum(InvoiceOwing) as InvoiceOwingHidden, sum(InvoiceOwing) as InvoiceOwingHiddenNew,
                          (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 'SI' else 'SO' end) as Invoice_Type");
        $this->db->from($this->receipt);
        $this->db->where('CustomerCode', $vendor_code);
        $this->db->where('PPnFiscalStatus', $ppn_fiscal);
        $this->db->where('ROUND(InvoiceOwing, 2) > ', 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_payment($CustomerCode, $ReceiptTaxStatus='') {
//        $this->db->where('CompanyCode', $this->session->userdata('company'));
//
//        if($this->session->userdata('$branch_status') == 1) {
//            $this->db->where('BranchCode', $this->session->userdata('Branch'));
//        }
//
//        $this->db->where('CustomerCode', $CustomerCode);        
//        $query = $this->db->get('av_salesreceipt');
//
//        return $query->result();
		
		$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,
			(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('PPnFiscalStatus', $ppn_fiscal);
        $this->db->where('InvoiceOwing >', 0);
        $this->db->where('LockedStatus', 0);
		
		if($ReceiptTaxStatus==0){
			$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_for_payment_fiscal($CustomerCode) {
//        $this->db->where('CompanyCode', $this->session->userdata('company'));
//
//        if($this->session->userdata('$branch_status') == 1) {
//            $this->db->where('BranchCode', $this->session->userdata('Branch'));
//        }
//
//        $this->db->where('CustomerCode', $CustomerCode);        
//        $query = $this->db->get('av_salesreceipt');
//
//        return $query->result();
		
		$this->db->_protect_identifiers = false;
        $this->db->select("
			InvoicePaymentID, InvoiceNumber, InvoiceDate, SUM(InvoiceAmount) as InvoiceAmount, SUM(InvoiceOwing) as InvoiceAmountCopy, 
			(SUM(InvoiceAmount) - SUM(ReceiptAmount)) 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 = 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,
			(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('InvoiceType', 2);
        $this->db->where('(InvoiceAmount - ReceiptAmount) > ', 0);
        $this->db->where('LockedStatus', 0);
		
        $this->db->group_by('InvoiceType, InvoiceNumber');
        $this->db->order_by('InvoiceDate', 'DESC');
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;
        
        return $query->result();
    }
    
	/*======================penomeran transaksi new=======================================================*/
	function check_code_exist($new_si_number) {
        $query = $this->db->get_where($this->table, array('SInvNumber' => $new_si_number), 1, 0);
		
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }	
    }
	
	function create_si_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');
        }
		
        $si_number	= $branch ."/". $month . $year ."SI/";
		
        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(SInvNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(SInvNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('SInvNumber', $si_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_si_number = $si_number . $number;

        while($this->check_code_exist($new_si_number)){
            $num++;
            $long = strlen($num);
    
            if($long == 1){
                $number = '00' . $num;
            }
            elseif($long == 2){
                $number = '0' . $num;
            }
            else{
                $number = $num;
            }
            
            $new_si_number = $si_number . $number;		
        }
        
        return  $new_si_number;
    }
	/*==============================================================end=================================*/
	
	function update_transaction($trans_data){
		$this->db->trans_start();
		$this->update($trans_data['update_group']['data'],$trans_data['update_group']['SInvNumber']);
		
		$this->delete_journal($trans_data['delete_journal']['SInvNumber']);
		/*
			Journal sales invoice
		*/
		if(!empty($trans_data['insert_journal']['data'])){
			for($x=0;$x<count($trans_data['insert_journal']['data']);$x++){
				$this->insert_journal($trans_data['insert_journal']['data'][$x]);
			}
		}
		
		if(!empty($trans_data['update_journal']['data'])){
			for($x=0;$x<count($trans_data['update_journal']['data']);$x++){
				$this->update_journal($trans_data['update_journal']['data'][$x],$trans_data['update_journal']['SInvNumber'][$x], $trans_data['update_journal']['SInvAccountNumber'][$x]);
			}
		}
		
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;
		}
	}
	
	function approval_transaction($trans_data){
		$this->db->trans_start();
		$this->update($trans_data['update_group']['data'],$trans_data['update_group']['SInvNumber']);
		$this->m_delivery_order->update($trans_data['update_do']['data'],$trans_data['update_do']['DOrdNumber']);
		$this->m_customer->update($trans_data['update_customer']['data'],$trans_data['update_customer']['CustomerCode']);
		
		if(!empty($trans_data['insert_receipt']['data'])){
			for($x=0;$x<count($trans_data['insert_receipt']['data']);$x++){
				$this->insert_sinv_receipt($trans_data['insert_receipt']['data'][$x]);
			}
		}
		
		if(!empty($trans_data['insert_receipt_fiscal']['data'])){
			$this->insert_sinv_receipt($trans_data['insert_receipt_fiscal']['data']);
		}
		
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;
		}
	}
	
	function suspend_transaction($trans_data){
		$this->db->trans_start();
		$this->update($trans_data['update_group']['data'], $trans_data['update_group']['SInvNumber']);
		$this->m_delivery_order->update($trans_data['update_group_do']['data'], $trans_data['update_group_do']['DOrdNumber']);			 
		$this->db->trans_complete();
        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
	}
	
	function reverse_trasaction($trans_data){
		$this->db->trans_start();
		$this->update($trans_data['update_group']['data'], $trans_data['update_group']['SInvNumber']);
		
		$this->delete_sinv_receipt($trans_data['delete_invoice_receipt']['InvoiceNumber']);
		$this->m_delivery_order->update($trans_data['update_group_do']['data'], $trans_data['update_group_do']['DOrdNumber']);
				 
		$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_detail) {		
        $this->db->insert($this->detail, $data_detail);
    }
	
	function insert_sinv_receipt($data_si_receipt){
		$this->db->insert($this->receipt, $data_si_receipt);
	}
	
	function update($data, $id) {
		$this->db->update($this->table, $data, array('SInvNumber' => $id));
	}
	
	function update_simple($data, $id) {
		 $this->db->update($this->table, $data, array('SInvNumber' => $id));		
	}
	
	function update_sinv_receipt($data_si_receipt, $SInvNumber, $ItemCategoryID){
		$this->db->update($this->receipt, $data_si_receipt, array('InvoiceNumber'=>$SInvNumber,'ItemCategoryID'=>$ItemCategoryID));
	}
	
	function update_sinv_receipt_2($data_si_receipt, $SInvNumber){
		$this->db->update($this->receipt, $data_si_receipt, array('InvoiceNumber'=>$SInvNumber));
	}
	
	function insert_journal($data) {		
		$this->db->insert($this->journal, $data);
	}
	
	function update_journal($data, $id, $account) {		
		$this->db->update($this->journal, $data, array('SInvNumber' => $id, 'SInvAccountNumber' => $account));
	}
	
	function delete_group($SInvNumber){
		$this->db->delete($this->table, array('SInvNumber'=>$SInvNumber));
	}
	
	function delete_detail($SInvNumber){
		$this->db->delete($this->detail, array('SInvNumber'=>$SInvNumber));
	}
	
	function delete_journal($SInvNumber){
		$this->db->delete($this->journal, array('SInvNumber'=>$SInvNumber));
	}
	
	function delete_sinv_receipt($id){
		$this->db->delete($this->receipt, array('InvoiceNumber'=>$id));
	}
	
	function update_detail($data, $id) {		
		$this->db->update($this->detail, $data, array('SInvNumber' => $id));
	}
	
	function update_detail_for_return($data, $id) {		
		$this->db->update($this->detail, $data, array('SInvDetailID' => $id));
	}
	
	function set_trans_status($id, $status) {
		$this->db->set('TransStatus', $status);
		$this->db->where('SInvNumber', $id);
		$this->db->update($this->table);
	}
	
	/*
		Delete Invoice from DO
	*/
	function delete_group_reverse_do($SInvNumber){
		$this->db->delete($this->table, array('SInvNumber'=>$SInvNumber));
	}
	
	function delete_detail_reverse_do($SInvNumber){
		$this->db->delete($this->detail, array('SInvNumber'=>$SInvNumber));
	}
	
	function delete_journal_reverse_do($SInvNumber){
		$this->db->delete($this->journal, array('SInvNumber'=>$SInvNumber));
	}
	/*==================End===============================================*/
	
	function get_transstatus($id) {
        $this->db->select('TransStatus');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$TransStatus = $row->TransStatus;
			}    

			return $TransStatus;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_for_invoice(){
        $this->db->select("
			at_salesinvoice.SInvNumber as invoice_number, at_salesinvoice.SInvDate as invoice_date, at_salesinvoice.SInvTotalAmount as invoice_amount, at_salesinvoice.SRecTotalAmount as invoice_owning
		");
		
		$this->db->from('at_salesinvoice');
		$this->db->where('at_salesinvoice.BranchCode',$this->session->userdata('branch'));
		$this->db->where_in('at_salesinvoice.TransStatus', array(0,1));
		$this->db->order_by($this->table.'.SInvNumber','ASC');
		return $this->db->get()->result();
    }
	
	function get_for_invoice_by_cust($id){
        $this->db->select("
			at_salesinvoice.SInvNumber as invoice_number, at_salesinvoice.SInvDate as invoice_date, at_salesinvoice.SInvSubTotal as invoice_amount, at_salesinvoice.SInvTotalAmount as invoice_total, at_salesinvoice.SRecTotalAmount as invoice_owning
		");
		
		$this->db->from('at_salesinvoice');
		$this->db->where('at_salesinvoice.BranchCode',$this->session->userdata('branch'));
		$this->db->where('at_salesinvoice.CustomerCode', $id);
		$this->db->where_in('at_salesinvoice.TransStatus', array(0,1));
		$this->db->order_by($this->table.'.SInvNumber','ASC');
		return $this->db->get()->result();
    }
	
	function get_rec_total_dp($id) {
        $this->db->select('SInvTotalDP');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvTotalDP = $row->SInvTotalDP;
			}    

			return $SInvTotalDP;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_rec_total_amount($id) {
        $this->db->select('SRecTotalAmount');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SRecTotalAmount = $row->SRecTotalAmount;
			}    

			return $SRecTotalAmount;
		}
		else {
			return FALSE;
		}	
    }
	
	//cek ulang disini harus nya trans status adalah 1 dan dia sudah over due.
	function get_invoice_for_check_overdue($cmb_customer){
		$this->db->select('at_salesinvoice.SInvDate,am_termspayment.NetDueIn');
		$this->db->join('am_customer','at_salesinvoice.CustomerCode=am_customer.CustomerCode');
		$this->db->join('am_termspayment','am_customer.TermsPaymentId=am_termspayment.TermsPaymentID');
		//$this->db->where('at_salesinvoice.TransStatus <>',2);
		$this->db->where('at_salesinvoice.TransStatus',1);
		//$this->db->where('at_salesinvoice.AppFinished',0);
		$this->db->where('SInvTotalAmount <>','SRecTotalAmount');
		$this->db->where('am_customer.CustomerCode',$cmb_customer);
		
		$this->db->order_by('SInvDate', 'ASC');
		$this->db->limit(1);
		$query = $this->db->get($this->table);
		
		return $query->result();
	}
	
	function set_rec_total_amount($id, $amount) {
		$this->db->set('SRecTotalAmount', $amount);
		$this->db->where('SInvNumber', $id);
		$this->db->update($this->table);
	}
	
	function check_journal($id, $account) {
		$query = $this->db->get_where($this->journal, array('SInvNumber' => $id, 'SInvAccountNumber' => $account), 1, 0);
		
		if ($query->num_rows() > 0) {
			return TRUE;
		}
		else {
			return FALSE;
		}	
	}
	
	function check_approval_status($SInvNumber){
		$query = $this->db->get_where($this->table, array('SInvNumber' => $SInvNumber, 'AppLevel1Status' => 1, 'AppLevel2Status' => 1), 1, 0);
		
		if ($query->num_rows() > 0) {
			return TRUE;
		}else {
			return FALSE;
		}
	}
	
	function set_amount_debet($id, $account, $amount) {
		$this->db->set('SInvSaldoDebet', $amount);
		$this->db->where('SInvNumber', $id);
		$this->db->where('SInvAccountNumber', $account);
		$this->db->update($this->journal);
	}
	
	function set_amount_credit($id, $account, $amount) {
		$this->db->set('SInvSaldoCredit', $amount);
		$this->db->where('SInvNumber', $id);
		$this->db->where('SInvAccountNumber', $account);
		$this->db->update($this->journal);
	}
	
	function get_amount_debet($id, $account) {
		$this->db->select('SInvSaldoDebet');
		$this->db->from($this->journal);
		$this->db->where('SInvNumber', $id);
		$this->db->where('SInvAccountNumber', $account);
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvSaldoDebet = $row->SInvSaldoDebet;
			}    

			return $SInvSaldoDebet;
		}
		else {
			return 0;
		}  	
	}
	
	function get_amount_credit($id, $account) {
		$this->db->select('SInvSaldoCredit');
		$this->db->from($this->journal);
		$this->db->where('SInvNumber', $id);
		$this->db->where('SInvAccountNumber', $account);
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvSaldoCredit = $row->SInvSaldoCredit;
			}    

			return $SInvSaldoCredit;
		}
		else {
			return 0;
		}  	
	}
	
	function get_price_by_detail($id) {
		$this->db->select('ItemPrice');
		$this->db->from($this->detail);
		$this->db->where('SInvDetailID', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$ItemPrice = $row->ItemPrice;
			}    

			return $ItemPrice;
		}
		else {
			return 0;
		}  	
	}
	
	function get_journal_by_number($Number){
	    $this->db->_protect_identifiers = FALSE;
	    $this->db->select("at_sinvjournal.*,am_accountgl.AccountNumber,am_accountgl.AccountName");
		$this->db->join("am_accountgl","am_accountgl.AccountNumber=at_sinvjournal.SInvAccountNumber","INNER");
        $this->db->order_by('at_sinvjournal.SInvNumber','ASC');
		$query = $this->db->get_where($this->journal, array('SInvNumber' => $Number));
		$this->db->_protect_identifiers = TRUE;
        return $query->result();
    }
	
	function get_do_by_si($id) {
        $this->db->select('DOrdNumber');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$DOrdNumber = $row->DOrdNumber;
			}    

			return $DOrdNumber;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_si_by_sr($id) {
        $this->db->select('SInvNumber');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvNumber = $row->SInvNumber;
			}    

			return $SInvNumber;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_total_invoice($id) {
        $this->db->select('SInvTotalAmount');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvTotalAmount = $row->SInvTotalAmount;
			}    

			return $SInvTotalAmount;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_total_receipt($id) {
        $this->db->select('SRecTotalAmount');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SRecTotalAmount = $row->SRecTotalAmount;
			}    

			return $SRecTotalAmount;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_diskon_persen($id) {
        $this->db->select('SInvDiscPcnt');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvDiscPcnt = $row->SInvDiscPcnt;
			}    

			return $SInvDiscPcnt;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_diskon_value($id) {
        $this->db->select('SInvDiscount');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvDiscount = $row->SInvDiscount;
			}    

			return $SInvDiscount;
		}
		else {
			return 0;
		}	
    }
	
	function get_detail_qty($id) {
        $this->db->select('ItemQty');
		$this->db->from($this->detail);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$ItemQty = $row->ItemQty;
			}    

			return $ItemQty;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_detail_amount($id) {
        $this->db->select('ItemAmount');
		$this->db->from($this->detail);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$ItemAmount = $row->ItemAmount;
			}    

			return $ItemAmount;
		}
		else {
			return FALSE;
		}	
    }
	
    function get_invoice_cust($cust){
        $this->db->select("at_salesinvoice.SInvNumber, at_salesinvoice.SInvDate, at_salesinvoice.SInvDesc, at_salesinvoice.SInvTotalAmount, 
                           at_salesinvoice.SRecTotalAmount, at_salesinvoice.SInvDesc, at_salesinvoice.DOrdNumber, at_salesinvoice.DOrdDate, 
                           at_salesinvoice.SOrdNumber, at_salesinvoice.CustPONumber, am_customer.CustomerName, 
                           am_branch.BranchName, at_salesinvoice.CustomerCode, at_salesinvoice.BranchCode");		
        $this->db->from('at_salesinvoice');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesinvoice.CustomerCode', 'inner');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesinvoice.BranchCode', 'inner');
        $this->db->where('at_salesinvoice.BranchCode', $this->session->userdata('branch'));
        $this->db->where('at_salesinvoice.CustomerCode', $cust);
        $this->db->order_by($this->table.'.SInvNumber','ASC');

        return $this->db->get()->result();
    }
	
	function get_tax_status($id) {
        $this->db->select('SInvTaxesStatus');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvTaxesStatus = $row->SInvTaxesStatus;
			}    

			return $SInvTaxesStatus;
		}
		else {
			return 0;
		}	
    }
	
	function get_taxin_status($id) {
        $this->db->select('SInvInTaxStatus');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvInTaxStatus = $row->SInvInTaxStatus;
			}    

			return $SInvInTaxStatus;
		}
		else {
			return 0;
		}	
    }
	
	function get_date($id) {
        $this->db->select('SInvDate');
		$this->db->from($this->table);
		$this->db->where('SInvNumber', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvDate = $row->SInvDate;
			}    

			return $SInvDate;
		}
		else {
			return FALSE;
		}	
    }
	
	function get_detail_id_amount_by_category($SInvNumber, $Category){
	    $this->db->_protect_identifiers = FALSE;
	    $this->db->select('
			at_sinvdetail.SInvDetailID,at_sinvdetail.ItemAmount, at_sinvdetail.SInvPaidStatus, at_sinvdetail.SInvOwing, at_sinvdetail.SInvNumber, at_sinvdetail.ItemCode,
			at_salesinvoice.SInvDiscPcnt, at_salesinvoice.SInvTaxesStatus, at_salesinvoice.SInvInTaxStatus
		');
		$this->db->join('at_salesinvoice', 'at_salesinvoice.SInvNumber = at_sinvdetail.SInvNumber', 'inner');
        $this->db->join('am_item', 'am_item.ItemCode = at_sinvdetail.ItemCode', 'inner');
		$this->db->where('at_sinvdetail.SInvNumber', $SInvNumber);
		$this->db->where('am_item.ItemCategoryID', $Category);
		$query = $this->db->get('at_sinvdetail');
		$this->db->_protect_identifiers = TRUE;
        return $query->result();
    }
	
	function set_paid_status($id, $status) {
		$this->db->set('SInvPaidStatus', $status);
		$this->db->where('SInvDetailID', $id);
		$this->db->update($this->detail);
	}
	
	function set_inv_owing($id, $amount) {
		$this->db->set('SInvOwing', $amount);
		$this->db->where('SInvDetailID', $id);
		$this->db->update($this->detail);
	}
	
	function get_finish_invoice($id){
        $this->db->select("
			at_salesinvoice.SInvNumber as invoice_number, at_salesinvoice.SInvDate as invoice_date, at_salesinvoice.SInvSubTotal as invoice_amount, at_salesinvoice.SInvTotalAmount as invoice_total, at_salesinvoice.SRecTotalAmount as invoice_owning
		");
		
		$this->db->from('at_salesinvoice');
		$this->db->where('at_salesinvoice.BranchCode',$this->session->userdata('branch'));
		$this->db->where('at_salesinvoice.SInvNumber', $id);
		$this->db->where_in('at_salesinvoice.TransStatus', array(0,1));
		$this->db->order_by($this->table.'.SInvNumber','ASC');
		return $this->db->get()->result();
    }
	
	function get_detail_inv_owing($id) {
        $this->db->select('SInvOwing');
		$this->db->from($this->detail);
		$this->db->where('SInvDetailID', $id); 
		$query = $this->db->get();    
        
		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				$SInvOwing = $row->SInvOwing;
			}    

			return $SInvOwing;
		}
		else {
			return FALSE;
		}	
    }
    
    function get_sales_account($AccountCode, $ItemCategoryID){   
        $this->db->select('SysSalesCategoryID');
        $this->db->from('sys_salescategory');
        $this->db->where('ItemCategoryID', $ItemCategoryID);
        $this->db->where('CompanyCode', $this->session->userdata('company'));
        $query = $this->db->get();
		
        if ($query->num_rows() > 0) {
            $data = $query->row();
            $this->db->select('AccountNumber');
            $this->db->from('sys_salesaccount');
            $this->db->where('AccountCode', $AccountCode);
            $this->db->where('SysSalesCategoryID', $data->SysSalesCategoryID);
			
            //get AccountNumber			
            $query_account = $this->db->get();
			
            if ($query_account->num_rows() > 0) {
                $data = $query_account->row();

                if($data->AccountNumber!=NULL){
                    return $data;
                }
                else{
                    return false;	
                }
            }
            else {
                return false;
            }
        }
        else {
            return false;
        }			
    }    

    function get_summary_si($srecnumber) {
        $this->db->select('at_sinvdetail.SInvNumber, sum(SRecItemDPUsed) as Total_DPUsed, sum(SRecItemDiscount) as Total_Discount,  
                           sum(SRecItemAmount) as Total_Receive, sum(SRecItemOwing) as Total_Owing');
        $this->db->join('at_srecdetail', 'at_srecdetail.SInvNumber = at_sinvdetail.SInvNumber', 'INNER');
        $this->db->where('at_srecdetail.SRecNumber', $srecnumber);
        $this->db->group_by('at_sinvdetail.SInvNumber');
        $this->db->order_by('at_sinvdetail.SInvNumber', 'ASC');
        $query = $this->db->get($this->detail);

        return $query->result();
    }
    
    function set_finish_invoice($id) {
        $this->db->select('*');
        $this->db->from('at_salesinvoice');
        $this->db->where('at_salesinvoice.SInvNumber', $id);
        //$this->db->where('at_salesinvoice.SRecItemOwing', 0);
        $query = $this->db->get();

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

            $this->update($data_si, $id);
            
            if ($this->db->trans_status() === FALSE) {
                return 0;
            }
            else {
                return 1;
            }            
        }
        else{
            return 1;
        }		
    }
	
	function check_si_receipt_transaction($SInvNumber, $ItemCategoryID){
		$this->db->select('*');
		$this->db->from($this->receipt);
		$this->db->where('InvoiceNumber',$SInvNumber);
		$this->db->where('ItemCategoryID',$ItemCategoryID);
		
		$query = $this->db->get();
		if($query->num_rows() > 0){
			return false;
		}
		else{
			return true;
		}
	}
	
	function get_sales_invoice_for_check($DOrdNumber, $number_unik){
		$this->db->select("TransStatus, SInvNumber");		
        $this->db->from('at_salesinvoice');
       	$this->db->where('at_salesinvoice.DOrdNumber', $DOrdNumber);
		$this->db->where('at_salesinvoice.NumberUnik', $number_unik);
		
		$query = $this->db->get();
		if($query->num_rows() > 0){
			return $query->row();
		}
		else{
			return true;
		}
	}
	
	function get_invoice_on_progres($CustomerCode, $InvoiceType, $PPnFiscalStatus){
		$this->db->select("sum(InvoiceOwing) as InvoiceOwing");		
        $this->db->from('at_invoicereceipt');
		$this->db->where('CustomerCode', $CustomerCode);
		$this->db->where('ROUND(InvoiceOwing, 2) >', 0);
		$this->db->where('InvoiceType', $InvoiceType);
		$this->db->where('PPnFiscalStatus', $PPnFiscalStatus);
		$this->db->group_by('InvoiceNumber');
		$query = $this->db->get();
		if($query->num_rows() > 0){
			return $query->row();
		}
		else{
			return true;
		}
	}
	
	function get_sales_invoice_on_progres($CustomerCode){
		$this->db->select("sum(SInvTotalAmount) as SInvTotalAmount");		
        $this->db->from('at_salesinvoice');
		$this->db->join('at_sinvdetail', 'at_salesinvoice.SInvNumber = at_sinvdetail.SInvNumber', 'inner');
       	$this->db->where('at_salesinvoice.CustomerCode', $CustomerCode);
		$this->db->where('at_salesinvoice.TransStatus', 1);
		$this->db->where('at_sinvdetail.ItemCode', "ITM-MGRS");
		$this->db->group_by('CustomerCode');
		
		$query = $this->db->get();
		if($query->num_rows() > 0){
			return $query->row();
		}
		else{
			return true;
		}
	}
	
	function get_sales_invoice_journal($SInvAccountNumber, $sinv_number) {
		$this->db->select('SInvSaldoCredit, SInvSaldoDebet, SInvAccountNumber, AmountCurrency');
        $this->db->from('at_sinvjournal');
        $this->db->where('SInvNumber', $sinv_number);
		$this->db->where('SInvAccountNumber', $SInvAccountNumber);
		
        $query = $this->db->get();

        if($query->num_rows() > 0){
            return $query->row();
        }
        else{
            return false;
        }	
    }
	
	function get_sales_invoice_group($SInvNumber){
		$this->db->select("DOrdNumber");		
        $this->db->from('at_salesinvoice');
       	$this->db->where('SInvNumber', $SInvNumber);
		
		$query = $this->db->get();
		if($query->num_rows() > 0){
			return $query->row();
		}
		else{
			return true;
		}
	} 
}

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