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

class SupplierPayment_model extends CI_Model{
	
	 
	 public function supppayment_all(){
	     $this->db->where('is_delete', 0);
	    $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
		$query = $this->db->get('comp_payment');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function supplierPayment_all(){
		$this->db->where('comp_payment.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->select('comp_payment.*');
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->from('comp_payment');
		$this->db->join('supplier', 'supplier.ID = comp_payment.supplierID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}

	 public function supppayment_byID($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('concat(a.formNo,"[",lpad(a.ID,8,"0"),"]") as paymentNo', false);
		$this->db->where('a.ID', $ID);
	    $this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_payment a');
		$this->db->join('supplier b', 'b.ID = a.supplierID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}

	public function updatePosting($ID){
		$this->db->where('ID', $ID);
		$updatepayment = array('updateAble'=>0);
		$query = $this->db->update('comp_payment', $updatepayment); 
		if($query){
			return TRUE;
		}else{
			return FALSE;
		}
		
	}
	public function paymentDetail_insert($paymentdetails){
		$currentUser = element('role', $this->session->userdata('logged_in'));

		$paymentID = $paymentdetails["paymentID"];
                $rowNo = 0;
		for ($i=0; $i< sizeof ($paymentdetails["amountApplied"]); $i++) {
			$amountApplied = ($paymentdetails["amountApplied"][$i]==''?0:$paymentdetails["amountApplied"][$i]);
			$suppInvoiceID = ($paymentdetails["suppInvoiceID"][$i]==''?0:$paymentdetails["suppInvoiceID"][$i]);
			$discount = ($paymentdetails["discount"][$i]==''?0:$paymentdetails["discount"][$i]);
//			echo '<br>amt:'.$amountApplied.' inv:'.$suppInvoiceID.' disc:'.$discount; 
			if ($discount > 0 || $amountApplied > 0) {
                            $rowNo++;
//				echo '<br>'.$col[$i].'/'.$paymentdetailsl->amountApplied[$i].'/'.$paymentdetailsl->discount[$i];
				$sql1 = 'insert into comp_payment_detail (paymentID, lineNo, purchaseInvoiceID,totalAmount,APBalance,discount,amountApplied,createdBy) ';
				$sql1 .= 'select '.$paymentID.','.$rowNo.',ID,totalAmount, totalPayable  ,'. number_format($discount,2,".","").','.number_format($amountApplied,2,".","").','.$currentUser ;
				$sql1 .= ' from comp_supplier_invoice where ID=' . $suppInvoiceID;
				
                                $sql2 = 'update comp_supplier_invoice set ';
				$sql2 .= 'amountPaid = amountPaid + '.number_format($amountApplied,2,".","").', ';
//				$sql2 .= 'totalPayable = totalAmount - amountPaid - '.$amountApplied.' - '.$discount;
				$sql2 .= 'totalPayable = totalAmount - amountPaid ';
				$sql2 .= ' where ID = '.$suppInvoiceID;
				$query = $this->db->query($sql1);
//				echo '<br>'.$this->db->last_query();
				$query = $this->db->query($sql2);
//				echo '<br>'.$this->db->last_query();
			}
		}
				echo '<script>alert("save controller");</script>';

		if($query === TRUE){
			return TRUE;
		} else {
			$last_query = $this->db->last_query();
			return $last_query;
		}
//insert into comp_payment_detail (paymentID, purchaseInvoiceID,totalAmount,APBalance,discount,amountApplied,createdBy) 
//select $paymentID,ID,totalAmount, totalAmount - amountPaid + $amountApplied[$i], $discount[$i],$amountApplied[$i],$currentUser
//from comp_supplier_invoice 
//where ID = $suppInvoiceID[$i]
/* example insert into comp_payment_detail (paymentID, purchaseInvoiceID,totalAmount,APBalance,discount,amountApplied,createdBy) 
select 1,ID,totalAmount, totalAmount - amountPaid + 100, 1,100,1
from comp_supplier_invoice 
where ID = 71
update comp_supplier_invoice set amountPaid = amountPaid + $amountApplied[$i], totalPayable = totalAmount - amountPaid where ID = $suppInvoiceID[$i]

*/
	}

	
	public function suppPayment_insert($newsupppayment){
		$sql=$this->db->insert_string('comp_payment', $newsupppayment);
		$query = $this->db->query($sql);
//		echo '<br>'.$this->db->last_query();
		if($query === TRUE){
			return $this->db->insert_id();//TRUE;
		} else {
			$last_query = $this->db->last_query();
			return 0;
		}
	}
		public function suppPayment_edit($updatepayment,$ID){
		$this->db->where('ID', $ID);
		$query = $this->db->update('comp_payment', $updatepayment); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
        public function supppayment_delete($deletpayment, $ID){
		$this->db->where('ID', $ID);
		$query = $this->db->update('comp_payment', $deletepayment); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	 public function supppaymentDetailspost($id, $data){
		$this->db->where('ID', $id);
		$this->db->where('updateAble', 1);
		$query = $this->db->update('comp_payment', $data);
		if($query){
			$this->db->flush_cache();
			$this->db->where('ID', $id);
			$this->db->from('comp_payment');
			$query1 = $this->db->get();
			return $query1->result();
		}else{
			return false;
		}
	 }
	 public function supppaymentDetails_ID($ID){
		$this->db->where('paymentID', $ID);
		$this->db->from('view_unpost_payment');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	 //print detail
	 public function supppaymentPrintDetails_ID($ID){
		$this->db->where('paymentID', $ID);
		$query = $this->db->get('comp_payment_detail');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	public function supplierPaymentDate($dateFrom, $dateTo){
		$this->db->where('comp_payment.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('comp_payment.paymentDate between  "'.$dateFrom.'" and "'.$dateTo.'"');
		$this->db->where('comp_payment.updateAble', 1);
		$this->db->select('comp_payment.*');
		$this->db->from('comp_payment');
		$this->db->select('supplier.supplierName as supplierName');
		$this->db->join('supplier', 'supplier.ID = comp_payment.supplierID');
		$this->db->order_by('paymentDate desc');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	 public function supppaymentDetails_paymentID($journalID, $header, $detailsData, $year, $period){
		if($detailsData){
			$seq = 0;
			$journalAmount = 0;
			$lineAmount = 0;
			foreach($detailsData as $row):
				if($row->amountExcludedtax != 0){
				$seq++;
				$lineAmount = $row->amountExcludedtax * $header->exchangeRate;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'journalID' => $journalID,
					'sequence' => $seq,
					'description' => $row->itemName,
					'acctCode' => $row->crAccountID,
					'supplier' => $row->supplier,
					'amount_cr' => $lineAmount,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->amountExcludedtax,
					'period' => $period,
					'year' => $year,
					'line' => $row->ID,
					'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$seq++;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'sequence' => $seq,
					'journalID' => $journalID,
					'description' => $row->itemName,
					'acctCode' => $row->drAccountID,
					'supplier' => $row->supplier,
					'amount_dr' => $lineAmount,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->amountExcludedtax,
					'period' => $period,
					'year' => $year,
					'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
					'line' => $row->ID,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$journalAmount = $journalAmount + $lineAmount;
				}
				if($row->taxamount !=0 ){
				$lineAmount = $row->taxamount * $header->exchangeRate;
				$seq++;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'sequence' => $seq,
					'journalID' => $journalID,
					'description' => $row->itemName,
					'acctCode' => $row->drTaxAccountID,
					'supplier' => $row->supplier,
					'amount_dr' => $row->taxamount * $header->exchangeRate,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->taxamount,
					'period' => $period,
					'year' => $year,
					'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
					'line' => $row->ID,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$seq++;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'sequence' => $seq,
					'journalID' => $journalID,
					'description' => $row->itemName,
					'acctCode' => $row->crTaxAccountID,
					'supplier' => $row->supplier,
					'amount_cr' => $row->taxamount * $header->exchangeRate,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->taxamount,
					'period' => $period, 
					'year' => $year,
					'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
					'line' => $row->ID,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$journalAmount = $journalAmount + $lineAmount;
				}
			endforeach;
			if($journalAmount != 0){
				$data = array(
					'total_amount_dr'=>$journalAmount,
					'total_amount_cr'=>$journalAmount,
					'total_amount'=>0
				);
				$this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
				$this->db->where('journalID', $journalID);
				$queryJournal = $this->db->update('comp_journal', $data); 
			}
		}else{
			return false;
		}
	 }
         
         function supplierPaymentGetMaster($id){
              $sql = "SELECT 
                                supplier.supplierName, comp_payment.supplierID, _ref_currency.fldcurr_code As currencyWord, comp_payment.exchangeRate, comp_payment.ID AS myid,
                                comp_project.project_name, CONCAT(comp_payment.formNo, '[', lpad(comp_payment.ID,8,'0'), ']') as formNo,
                                comp_payment.paymentDate, CONCAT (comp_bank.bankShortName, ' | ',comp_bank.branch ) AS bankName, comp_bank.bankAccountNo, comp_payment.referenceNo,
                                comp_payment.outOfBalance, comp_payment.amountPaid, comp_payment.totalPaid, comp_payment.totalApplied, master_code.name as paymentMethod, 
                                comp_payment.memo, comp_payment.totalOtherCharge, comp_payment.totalDiscount, comp_payment.bankCharges
                            FROM 
                                ((((comp_payment 
                                    LEFT OUTER JOIN _ref_currency ON _ref_currency.fldid=comp_payment.currencyID) 
                                    LEFT OUTER JOIN comp_project ON comp_project.ID=comp_payment.projectID)
                                    LEFT OUTER JOIN comp_bank ON comp_bank.ID=comp_payment.bankId)
                                    LEFT OUTER JOIN master_code ON comp_payment.paymentMethodId=master_code.ID),
                                 supplier
                            WHERE
                                supplier.ID = comp_payment.supplierID AND comp_payment.ID = ".$id;
                $query = $this->db->query($sql);
		
		if($query->num_rows() >0 ){
			return $query->result();
		}else{
			return false;
		}
         }
         
         function supplierPaymentGetDetail($id){
              $sql = "SELECT 
                                comp_payment_detail.lineNo, comp_payment_detail.ID AS myid, comp_payment_detail.purchaseInvoiceID,comp_payment_detail.discount, 
                                comp_payment_detail.amountApplied, comp_supplier_invoice.id as invoiceID, comp_supplier_invoice.totalPayable, comp_supplier_invoice.totalAmount,comp_supplier_invoice.invoiceDate,
                                CONCAT(comp_supplier_invoice.formNo, '[', lpad(comp_supplier_invoice.ID,8,'0'), ']') as formNo
                            FROM 
                                comp_payment_detail,comp_supplier_invoice
                            WHERE 
                                comp_payment_detail.purchaseInvoiceID = comp_supplier_invoice.ID AND comp_payment_detail.paymentID =". $id;
         
              $query = $this->db->query($sql);
		
		if($query->num_rows() >0 ){
			return $query->result();
		}else{
			return false;
		}
         }
         
         
	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;
		}
	}

}