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

class othersPayment_model extends CI_Model{
	
	
	public function otherpay_all(){
                $this->db->select('comp_payment_others.*');
                $this->db->select('concat(comp_payment_others.formNo,"[",lpad(comp_payment_others.serialNo,8,"0"),"]") as formNo', false);
		$this->db->where('is_delete', 0);
		$query = $this->db->get('comp_payment_others');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	 public function othersPaymentWithItemDetails_ID($ID){
		$this->db->where('paymentID', $ID);
		$this->db->from('view_unpost_otherpayment');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	 public function othersPaymentWithItem_ID($ID){
                $this->db->select('comp_payment_others_with_item.*');
                $this->db->select('concat(comp_payment_others_with_item.formNo,"[",lpad(comp_payment_others_with_item.serialNo,8,"0"),"]") as formNo', false);

		$this->db->where('otherPaymentID', $ID);
		$this->db->from('comp_payment_others_with_item');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	 public function othersPaymentWithoutItem_ID($ID){
                 $this->db->select('comp_payment_others_without_item.*');
                $this->db->select('concat(comp_payment_others_without_item.formNo,"[",lpad(comp_payment_others_without_item.serialNo,8,"0"),"]") as formNo', false);

		$this->db->where('otherPaymentID', $ID);
		$this->db->from('comp_payment_others_without_item');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	 }
	 
	public function othersPaymentWithItempost($id, $data){
		$this->db->where('ID', $id);
//		$this->db->where('updateAble', 1);
		$query = $this->db->update('comp_payment_others', $data);
//		var_dump($this->db->last_query());
		if($query){
			$this->db->flush_cache();
                         $this->db->select('comp_payment_others.*');
                         $this->db->select('concat(comp_payment_others.formNo,"[",lpad(comp_payment_others.serialNo,8,"0"),"]") as formNo', false);

			$this->db->where('ID', $id);
			$this->db->from('comp_payment_others');
			$query1 = $this->db->get();
			return $query1->result();
		}else{
			return false;
		}
	}
	
	 public function othersPayment_otherPaymentID($journalID, $header, $detailsData, $year, $period){
		 if($header->exchangeRate == 0 || $header->exchangeRate == NULL){
			 $exchangeRate = 1;
		 }else{
		 	$exchangeRate =$header->exchangeRate ;
		 }
		if($detailsData){
			$seq = 0;
			$journalAmount = 0;
			$lineAmount = 0;
			foreach($detailsData as $row):
				$seq++;
				if($row->amtPaid_amtAppl == 0 OR $row->amtPaid_amtAppl == NULL){
				}else{
					if($row->crAccountID){
				$lineAmount = $row->amtPaid_amtAppl * $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' => $exchangeRate,
					'currency_amount' => $row->amtPaid_amtAppl,
					'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++;
					}
					if($row->drAccountID){
				$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' => $exchangeRate,
					'currency_amount' => $row->amtPaid_amtAppl,
					'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++;
					}
				$journalAmount = $journalAmount + $lineAmount;
				}
				if($row->discount_bankCharges == 0 OR $row->discount_bankCharges == NULL){
				}else{
					if($row->drDiscountAccountID){
				$lineAmount = $row->discount_bankCharges * $exchangeRate;
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'sequence' => $seq,
					'journalID' => $journalID,
					'description' => $row->itemName,
					'acctCode' => $row->drDiscountAccountID,
//					'supplier' => $row->supplier,
					'amount_dr' => $lineAmount,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $exchangeRate,
					'currency_amount' => $row->discount_bankCharges,
					'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++;
					}
					if($row->crDiscountAccountID){
				$newData = array(
					'companyID' => element('compID', $this->session->userdata('logged_in')),
					'sequence' => $seq,
					'journalID' => $journalID,
					'description' => $row->itemName,
					'acctCode' => $row->crDiscountAccountID,
//					'supplier' => $row->supplier,
					'amount_cr' => $lineAmount,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $exchangeRate,
					'currency_amount' => $row->discount_bankCharges,
					'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;
		}
	 }
	
	public function otherpayWithItem_all(){
                $this->db->select('otherPaymentType.*');
                $this->db->select('concat(otherPaymentType.formNo,"[",lpad(otherPaymentType.serialNo,8,"0"),"]") as formNo', false);

		$this->db->where('is_delete', 0);
		$this->db->where('otherPaymentType', 0);
		$query = $this->db->get('comp_payment_others');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	/*public function otherpayWithItem_date($dateFrom, $dateTo){
		$this->db->select('comp_payment_others.*');
		$this->db->select('date_format(comp_payment_others.paymentDate,"%d-%m-%Y")  as payDate ', false);
		$this->db->where('is_delete', 0);
		$this->db->where('paymentDate >=',$dateFrom);
		$this->db->where('paymentDate <=',$dateTo);
		$this->db->where('otherPaymentType', 0);
		$query = $this->db->get('comp_payment_others');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}*/
	
	public function otherpayWithItem_date($dateFrom, $dateTo){
		$this->db->select('a.*');
                $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('b.fldcurr_code');
		$this->db->select('date_format(a.paymentDate,"%d-%m-%Y")  as payDate ', false);
		$this->db->where('a.companyID', element('compID',	$this->session->userdata('logged_in')));
		$this->db->where('is_delete', 0);
		$this->db->where('a.paymentDate >=',$dateFrom);
		$this->db->where('a.paymentDate <=',$dateTo);
		$this->db->where('a.otherPaymentType', 0); //0=pay with item. 1=without item
		$this->db->from('comp_payment_others a');
		$this->db->join('_ref_currency b', 'b.fldid = a.currencyID','left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function otherpayWithItem_byID($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as otherPaymentNo', false);
		$this->db->where('a.ID', $ID);
		$this->db->where('a.otherPaymentType', 0);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_payment_others a');
		$this->db->join('comp_payment_others_with_item b', 'b.otherPaymentID = a.ID', 'left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function otherpayWithOutItem_all(){
                $this->db->select('comp_payment_others.*');
                $this->db->select('concat(comp_payment_others.formNo,"[",lpad(comp_payment_others.serialNo,8,"0"),"]") as formNo', false);

		$this->db->where('is_delete', 0);
		$this->db->where('otherPaymentType', 1);
		$query = $this->db->get('comp_payment_others');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function otherpayWithOutItem_date($dateFrom, $dateTo){
		$this->db->select('a.*');
                $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
                
		$this->db->select('b.fldcurr_code');
		$this->db->select('date_format(a.paymentDate,"%d-%m-%Y")  as payDate ', false);
		$this->db->where('a.companyID', element('compID',	$this->session->userdata('logged_in')));
		$this->db->where('a.paymentDate >=',$dateFrom);
		$this->db->where('a.paymentDate <=',$dateTo);
		$this->db->where('a.otherPaymentType', 1);
		$this->db->from('comp_payment_others a');
		$this->db->join('_ref_currency b', 'b.fldid = a.currencyID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function otherpayWithoutItem_byID($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as otherPaymentNo', false);
		$this->db->where('is_delete', 0);
		$this->db->where('a.ID', $ID);
		$this->db->where('a.otherPaymentType', 1);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_payment_others a');
		$this->db->join('comp_payment_others_without_item b', 'b.otherPaymentID = a.ID', 'left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function otherpay_edit($updatepayment,$ID){
                $this->db->select('comp_payment_others.*');
                $this->db->select('concat(comp_payment_others.formNo,"[",lpad(comp_payment_others.serialNo,8,"0"),"]") as formNo', false);

		$this->db->where('ID', $ID);
		$query = $this->db->update('comp_payment_others', $updatepayment); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	
	public function updatePosting($ID){
		$this->db->where('ID', $ID);
		$updatepayment = array('updateAble'=>0);
		$query = $this->db->update('comp_payment_others', $updatepayment); 
		if($query){
			return TRUE;
		}else{
			return FALSE;
		}
		
	}
	
	public function otherpaywithitem_unpost($dateFrom, $dateTo){
		$this->db->select('comp_payment_others.*');
                 $this->db->select('concat(comp_payment_others.formNo,"[",lpad(comp_payment_others.serialNo,8,"0"),"]") as formNo', false);
                 
		$this->db->select('comp_payment_others.updateAble as Yes');
		//$this->db->select('customer.customerName');
		$this->db->where('comp_payment_others.companyID', element('compID',	$this->session->userdata('logged_in')));
		$this->db->where('paymentDate >=', $dateFrom);
		$this->db->where('paymentDate <=', $dateTo);
		$this->db->where('comp_payment_others.updateAble', 1);
		$this->db->order_by('paymentDate', 'desc');
		$this->db->from('comp_payment_others');
		//$this->db->join('customer', 'customer.ID = comp_payment_others.customerID');
//		$this->db->join('comp_sales_creditnote','comp_sales_creditnote.ID = comp_sales_creditnote.salesInvoiceID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
     
	 public function otherpaywithitem_post($id, $data){
		$this->db->where('ID', $id);
		$this->db->where('updateAble', 1);
		$query = $this->db->update('comp_payment_others', $data);
		if($query){
			$this->db->flush_cache();
                         $this->db->select('comp_payment_others.*');
                         $this->db->select('concat(comp_payment_others.formNo,"[",lpad(comp_payment_others.serialNo,8,"0"),"]") as formNo', false);

			$this->db->where('ID', $id);
			$this->db->from('comp_payment_others');
			$query1 = $this->db->get();
			return $query1->result();
		}else{
			return false;
		}
	 }
	 
	 
	 // Without Unpost
	 public function otherpaywithoutitem_unpost($dateFrom, $dateTo){
		$this->db->select('comp_payment_others.*');
                $this->db->select('concat(comp_payment_others.formNo,"[",lpad(comp_payment_others.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('comp_payment_others.updateAble as Yes');
		//$this->db->select('customer.customerName');
		$this->db->where('comp_payment_others.companyID', element('compID',	$this->session->userdata('logged_in')));
		$this->db->where('paymentDate >=', $dateFrom);
		$this->db->where('paymentDate <=', $dateTo);
		$this->db->where('comp_payment_others.updateAble', 1);
		$this->db->order_by('paymentDate', 'desc');
		$this->db->from('comp_payment_others');
		//$this->db->join('customer', 'customer.ID = comp_payment_others.customerID');
//		$this->db->join('comp_sales_creditnote','comp_sales_creditnote.ID = comp_sales_creditnote.salesInvoiceID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
     
	 //Without Post
	 public function otherpaywithoutitem_post($id, $data){
		$this->db->where('ID', $id);
		$this->db->where('updateAble', 1);
		$query = $this->db->update('comp_payment_others', $data);
		if($query){
			$this->db->flush_cache();
                         $this->db->select('comp_payment_others.*');
                         $this->db->select('concat(comp_payment_others.formNo,"[",lpad(comp_payment_others.serialNo,8,"0"),"]") as formNo', false);

			$this->db->where('ID', $id);
			$this->db->from('comp_payment_others');
			$query1 = $this->db->get();
			return $query1->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  getLasIdInsert($tname){
		$sql='SELECT LAST_INSERT_ID() as myId from '.$tname;
		$query = $this->db->query($sql);
		$return = NULL;
 
		if($query->num_rows() >= 1){
			foreach($query->result() as $row)
				$return =  $row->myId;
		}
		return $return;
	}
	
		
	public function deleteByTname($id, $tname){
		$this->db->where('ID', $id);
		$query = $this->db->delete($tname); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function updateByTname ($id, $data, $tname){
		$this->db->where('ID', $id);
		$query = $this->db->update($tname, $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	public function getOthersPayment($id){
		
	 $sql='SELECT comp_payment_others.*, CONCAT(comp_payment_others.formNo,"[",lpad(comp_payment_others.serialNo,8,0),"]") as formNo, comp_payment_others.ID as pID, comp_project.project_name, tbl_location.code,
	  		 tbl_location.address, master_code.name AS methodPay, comp_bank.branch, comp_bank.bankShortName, comp_bank.bankAccountNo, comp_bank.currentBalance
		   FROM (((comp_payment_others left outer join comp_project on comp_payment_others.projectID = comp_project.ID) 
				LEFT OUTER JOIN tbl_location ON tbl_location.fldid=comp_payment_others.locationID ) 
				LEFT OUTER JOIN master_code ON master_code.ID =comp_payment_others.paymentMethodID)
				LEFT OUTER JOIN comp_bank ON comp_payment_others.bankId = comp_bank.ID
			WHERE comp_payment_others.updateAble = 1 AND comp_payment_others.ID =  '.$id;
      		 	
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	
	public function getOthersPaymentDetail($id, $tname){
		$sql ="";
		switch ($tname) {
    		case "comp_payment_others_with_item":
       			$sql='SELECT comp_payment_others_with_item.*,  comp_payment_others_with_item.ID AS pDetailID, 
                            item.ID as itemID, item.itemCode, item.name, tax_master.code as taxCode
					  FROM (comp_payment_others_with_item
					  LEFT JOIN item ON comp_payment_others_with_item.itemID=item.ID)
					  LEFT OUTER JOIN tax_master ON tax_master.ID =  comp_payment_others_with_item.taxID
					  WHERE comp_payment_others_with_item.otherPaymentID ='.$id;
      		 	 break;
  	 		
			case "comp_payment_others_without_item":
			 $sql='SELECT comp_payment_others_without_item.*, comp_payment_others_without_item.ID AS itemID, comp_chart_of_acct.ID as itemAccID, 
			 comp_chart_of_acct.acctName, comp_chart_of_acct.acctCode, tax_master.ID as taxid, tax_master.code, tax_master.taxPercentage
				   FROM (comp_payment_others_without_item left outer join comp_chart_of_acct on comp_payment_others_without_item.accountID = comp_chart_of_acct.ID) LEFT OUTER JOIN tax_master ON comp_payment_others_without_item.taxID = tax_master.ID
					WHERE comp_payment_others_without_item.otherPaymentID =  '.$id;
      		 	 break;
		   }
		
		$query = $this->db->query($sql);	

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	

} // end