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

class receivepayment_model extends CI_Model{
	
	public function insert($tname, $newData){
		$sql = $this->db->insert_string($tname, $newData);
		$query = $this->db->query($sql);
		if($query === TRUE){
			return $this->db->insert_id();
		} else {
			//$last_query = $this->db->last_query();
			//return $last_query;
			return 0;
		}
	}
	
	public function delete_byID($tname,$id){
		$this->db->where('ID', $id);
		$query = $this->db->delete($tname); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function update_byID($tname,$id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update($tname, $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function update_byFieldName($fName, $fName, $data, $tname){
		$this->db->where($fName, $fValue);
		$query = $this->db->update($tname, $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
        public function sqlStatementBuilder($foption,$fvalue){
            $sql ="";
            switch ($foption) {
                case "getPayerPendingInvoices":
                    $sql="SELECT ID, CONCAT(formNo, '[', lpad(serialNo,8,'0'), ']') as formNo, salesInvoiceDate, totalamount, totalReceivable
                          FROM comp_sales_invoice
                          WHERE customerID =$fvalue AND totalReceivable > 0
                              AND companyID=".element('compID', $this->session->userdata('logged_in'));
                break;
                
                case "getLastOutOfBalance" :
                    $sql = "SELECT totalOtherCharge
                            FROM comp_receive_payment
                            WHERE  customerID=".$fvalue." AND companyID=".element('compID', $this->session->userdata('logged_in'))
                          ." ORDER BY ID DESC LIMIT 1";
                break;
            
                case "getReceivePaymentMaster" :
                     $sql = "SELECT 
                                customer.customerName, comp_receive_payment.customerID, _ref_currency.fldcurr_code As currencyWord, comp_receive_payment.exchangeRate, comp_receive_payment.ID AS myid,
                                comp_project.project_name, CONCAT(comp_receive_payment.formNo, '[', lpad(comp_receive_payment.serialNo,8,'0'), ']') as formNo,
                                comp_receive_payment.receivePaymentDate, CONCAT (comp_bank.bankShortName, ' | ',comp_bank.branch ) AS bankName, comp_bank.bankAccountNo,
                                comp_receive_payment.outOfBalance, comp_receive_payment.totalReceived, master_code.name as paymentMethod, comp_receive_payment.referenceNo,
                                comp_receive_payment.memo, comp_receive_payment.totalApplied, comp_receive_payment.totalOtherCharge, comp_receive_payment.totalDiscount, comp_receive_payment.bankCharges
                            FROM 
                                ((((comp_receive_payment 
                                    LEFT OUTER JOIN _ref_currency ON _ref_currency.fldid=comp_receive_payment.currencyID) 
                                    LEFT OUTER JOIN comp_project ON comp_project.ID=comp_receive_payment.projectID)
                                    LEFT OUTER JOIN comp_bank ON comp_bank.ID=comp_receive_payment.bankId)
                                    LEFT OUTER JOIN master_code ON comp_receive_payment.paymentMethodId=master_code.ID),
                                 customer
                            WHERE
                                customer.ID = comp_receive_payment.customerID AND comp_receive_payment.ID = ".$fvalue;
                 break;
                case "getReceivePaymentDetails" :
                     $sql = "SELECT 
                                comp_receive_payment_detail.lineNo, comp_receive_payment_detail.ID AS myid, comp_receive_payment_detail.invoiceID,comp_receive_payment_detail.discount, comp_receive_payment_detail.ID AS myid,
                                comp_receive_payment_detail.amountApplied,  comp_sales_invoice.totalReceivable, comp_sales_invoice.totalamount,comp_sales_invoice.salesInvoiceDate,
                                CONCAT(comp_sales_invoice.formNo, '[', lpad(comp_sales_invoice.serialNo,8,'0'), ']') as formNo
                            FROM 
                                comp_receive_payment_detail,comp_sales_invoice
                            WHERE 
                                comp_receive_payment_detail.invoiceID = comp_sales_invoice.ID AND comp_receive_payment_detail.receivePaymentID =". $fvalue;
                break;
            }
            return $sql;
        }
        
        public function getDataByFvalue ($foption, $fvalue){
            
		$sql = $this->sqlStatementBuilder($foption,$fvalue);
                
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function getJsonDataByTname(){
		$sql ="";
		switch ($tname) {
    		case "":
			break;
		}
		
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return json_encode(array("status"=>"success", "recordSet"=>$query));
		}
		else{
			print json_encode(array("status"=>"false", "message"=>$query,));
		}
	}
        
       
        
        
}
