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

class AccountPayable_model extends CI_Model{
    
    public function updateMaster($master,$field,$idValue){
        $selectField = implode(",",$field);
        $myView = "view_total_".$master;
        $sql ='Select '.$selectField .' From '.$myView. ' WHERE ID = '.$idValue .' LIMIT 1';
        $query = $this->db->query($sql);
        $result = $query->result();
        
        $data =array();
         foreach($result as $row){
             $array = get_object_vars( $row);
             foreach ($field AS $f){
                 $data [$f] = $array [$f];
             }
         }
        $this->db->where('ID', $idValue);
	$query = $this->db->update($master, $data); 
        if($query === TRUE){
                return TRUE;
        } else {
                return FALSE;
        }
         
    }
    
    public function getCompanyInvoiceListBySup($supID){
        $sql = "SELECT CONCAT(formNo, '[', lpad(ID,8,'0'), ']') as formNO, ID as id, supplierInvoiceNo ";
		$sql .= ' from comp_supplier_invoice ';
        $sql .= ' WHERE supplierID='.$supID;
		$sql .= ' And companyID='.element('compID', $this->session->userdata('logged_in')) ; 
        $query = $this->db->query($sql);
        
        if($query->num_rows() >0){
            echo json_encode(array("status"=>"success", "recordSet"=>$query->result()));
        }
        else{
            echo json_encode(array("status"=>"false", "message"=>$query->result()));
        }
    }

        public function loadSupplierDefaultValue($id){
		$this->db->select('b.fldcurr_code as currencyWord');		
		$this->db->select('ifnull(c.rateConv,1) as exchangeRate', false);		
		$this->db->select('a.currencyID');		
		$this->db->where('a.ID', $id);
		$this->db->from('supplier a');
		$this->db->join('_ref_currency b', 'b.fldid = a.currencyID','left outer');
		$this->db->join('comp_currency_conv c', 'c.currencyTo = a.currencyID and c.startDate >='.date("Y-m-d",strtotime($this->input->post('effdate'))).' and c.endDate <='.date("Y-m-d",strtotime($this->input->post('effdate'))),'left outer');
		$query = $this->db->get();
//		$sql='SELECT cu.fldcurr_code as currencyWord, conv.rateConv as exchangeRate, su.currencyID FROM supplier su, _ref_currency cu, comp_currency_conv conv WHERE   conv.currencyTo = cu.fldid And su.ID = '.$id.' AND su.currencyID = cu.fldid ';
		if($query->num_rows() >=1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	
	//Gather supplier invoice information
	public function getSupplierInvoiceAll($dateForm = '',$dateTo = ''){//
		$query = $this->db->select('si.ID,CONCAT(si.formNo,"[",lpad(si.ID,8,0),"]") as formNo,si.supplierInvoiceNo,s.supplierName,si.totalAmount,DATE_FORMAT(si.createdTS,"%d-%m-%Y") as date',false)
				->from('comp_supplier_invoice as si')
				->join('supplier as s','s.ID = si.supplierID','left')
				->where(array('si.companyID'=>element('compID', $this->session->userdata('logged_in')),'si.updateAble'=>1,'si.createdTS >='=>$dateForm,'si.createdTS <='=>$dateTo))
				->get();
		
		if($query->num_rows() >= 1){
			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($id,$tname){
		$this->db->where('ID', $id);
		$query = $this->db->delete($tname); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function getCrditNotDetailById($id){
		$sql='SELECT comp_supplier_creditnote_detail.*, tax_master.code, tax_master.taxPercentage,
		item.itemCode,item.name as name, item.itemcode, item.id as itemid, comp_supplier_creditnote_detail.itemdescription as description
			FROM (comp_supplier_creditnote_detail LEFT OUTER JOIN tax_master ON tax_master.ID = comp_supplier_creditnote_detail.taxID)
			LEFT JOIN item 
			ON comp_supplier_creditnote_detail.itemID=item.ID
			where creditnoteID ='.$id;
			$query = $this->db->query($sql);

		if($query->num_rows() >=1){
			return $query->result();
		}else{
			return FALSE;
		}
	} // to be combined with get_detail_byid using tname as paramater
	
	public function get_DebitNoteDetail_byID($id){
		$sql='SELECT comp_supplier_debitnote_detail.*, tax_master.code, tax_master.taxPercentage,item.name, item.itemCode,item.ID AS itemid, comp_supplier_debitnote_detail.itemdescription as description
			FROM (comp_supplier_debitnote_detail LEFT OUTER JOIN tax_master ON tax_master.ID = comp_supplier_debitnote_detail.taxID)
			LEFT JOIN item
			ON comp_supplier_debitnote_detail.itemID=item.ID
			where debitnoteID ='.$id;
			$query = $this->db->query($sql);

		if($query->num_rows() >=1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	
	public function get_Detail_byID($id){
		$sql='SELECT comp_supplier_invoice_detail.*, tax_master.code, item.itemCode, item.name,comp_supplier_invoice_detail.itemdescription as description
			FROM (comp_supplier_invoice_detail LEFT OUTER JOIN tax_master ON tax_master.ID = comp_supplier_invoice_detail.taxID)
			LEFT JOIN item
			ON comp_supplier_invoice_detail.itemID=item.ID
			where invoiceID ='.$id;
			$query = $this->db->query($sql);
		if($query->num_rows() >=1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function get_byID($tname, $id){
		$sql ="";
		switch ($tname) {
    		case "comp_supplier_invoice":
       			 $sql='SELECT comp_employee.employeeName,master_code.name as shippingmethod, comp_supplier_invoice.*, comp_supplier_invoice.ID as InvID,tbl_location.*, comp_address.*,';
				 $sql.=' supplier.supplierName, comp_project.project_name, terms.termName, terms.termDescription, _ref_currency.fldcurr_code as currencyWord ';
				 $sql.=' FROM ((((((comp_supplier_invoice';
				 $sql.=' left outer join comp_project on comp_supplier_invoice.projectID=comp_project.ID)';
				 $sql.=' left outer join terms on comp_supplier_invoice.termsID = terms.ID)';
                 $sql.=' LEFT OUTER JOIN master_code ON master_code.ID=comp_supplier_invoice.shippingMethodID)';
                 $sql.=' LEFT OUTER JOIN comp_employee ON comp_employee.ID=comp_supplier_invoice.purchaserID)';
				 $sql.=' LEFT OUTER JOIN tbl_location on comp_supplier_invoice.locationID=tbl_location.fldid )';
				 $sql.=' LEFT OUTER JOIN comp_address ON comp_address.ID=comp_supplier_invoice.shipToID),';
				 $sql.=' supplier LEFT OUTER JOIN _ref_currency ON _ref_currency.fldid=supplier.currencyID';
				 $sql.=' where comp_supplier_invoice.updateAble = TRUE AND ';
				 $sql.='  supplier.ID=comp_supplier_invoice.supplierID ';
				 $sql.=' AND  comp_supplier_invoice.ID =  '.$id;
      		 	 break;
			case "comp_supplier_debitnote" :
				$sql='SELECT comp_supplier_debitnote.*,  supplier.supplierName, supplier.line1, supplier.line2, supplier.line3, supplier.city, supplier.supplierPostCode, comp_project.project_name,  _ref_currency.fldcurr_code, CONCAT(comp_supplier_invoice.formNo, "[", lpad(comp_supplier_invoice.ID,8,"0"), "]")as IformNo,  comp_supplier_invoice.supplierInvoiceNo ';
				$sql .= ' FROM (comp_supplier_debitnote left outer join comp_project on comp_supplier_debitnote.projectID=comp_project.ID) left outer join comp_supplier_invoice ON comp_supplier_invoice.ID = comp_supplier_debitnote.supplierInvoiceID, _ref_currency , supplier ';
				$sql .= ' where comp_supplier_debitnote.updateAble = TRUE ';
				$sql .= ' AND supplier.ID=comp_supplier_debitnote.supplierID ';
				$sql .= ' AND _ref_currency.fldid=supplier.currencyID AND comp_supplier_debitnote.ID =  '.$id;
			   break;
			/* case "comp_supplier_creditnote_detail" :
			  	 $sql='SELECT comp_supplier_creditnote.*,  '
                                 . 'supplier.supplierName, comp_project.project_name, '
                                 . 'currency.currencyWord FROM (comp_supplier_creditnote '
                                 . 'left outer join comp_project on'
                                 . ' comp_supplier_creditnote.projectID=comp_project.ID) LEFT OUTER JOIN  comp_supplier_invoice'
                                 . 'ON  comp_supplier_invoice.ID = , currency , '
                                 . 'supplier where comp_supplier_creditnote.updateAble ='
                                 . ' TRUE AND supplier.ID=comp_supplier_creditnote.supplierID'
                                 . ' AND currency.ID=supplier.currencyID AND comp_supplier_creditnote.ID =  '.$id;
			 	 break; */
			  case "comp_supplier_creditnote" :
			  	 $sql='SELECT comp_supplier_creditnote.*, supplier.supplierName, supplier.line1, supplier.line2, supplier.line3, supplier.city, supplier.supplierPostCode, comp_project.project_name,  _ref_currency.fldcurr_code, CONCAT(comp_supplier_invoice.formNo, "[", lpad(comp_supplier_invoice.ID,8,"0"), "]")as IformNo, comp_supplier_invoice.supplierInvoiceNo ';
				 $sql .= 'FROM (comp_supplier_creditnote left outer join comp_project on comp_supplier_creditnote.projectID=comp_project.ID) left outer join comp_supplier_invoice ON comp_supplier_invoice.ID = comp_supplier_creditnote.supplierInvoiceID,';
				 $sql .= '  _ref_currency , supplier where comp_supplier_creditnote.updateAble = TRUE ';
				 $sql .= 'AND supplier.ID=comp_supplier_creditnote.supplierID ';
				 $sql .= 'AND  _ref_currency.fldid=supplier.currencyID AND comp_supplier_creditnote.ID =  '.$id;
			 	 break;
  	 		
		   }
		$query = $this->db->query($sql);

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

	}
        public function update_byTname($tname,$id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update($tname, $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	//get credit note by id. abaikan updateable
	public function get_rpt_byID($tname, $id){
		$sql ="";
		switch ($tname) {
    		case "comp_supplier_invoice":
       			 $sql='SELECT comp_supplier_invoice.*, comp_supplier_invoice.ID as InvID,tbl_location.*, comp_address.*,';
				 $sql.=' supplier.supplierName, comp_project.project_name, terms.termName, terms.termDescription, currency.currencyWord ';
				 $sql.=' FROM ((((comp_supplier_invoice';
				 $sql.=' left outer join comp_project on comp_supplier_invoice.projectID=comp_project.ID)';
				 $sql.=' left outer join terms on comp_supplier_invoice.termsID = terms.ID)';
				 $sql.=' LEFT OUTER JOIN tbl_location on comp_supplier_invoice.locationID=tbl_location.fldid )';
				 $sql.=' LEFT OUTER JOIN comp_address ON comp_address.ID=comp_supplier_invoice.shipToID),';
				 $sql.=' currency , supplier';
				 $sql.=' where ';
				 $sql.=' supplier.ID=comp_supplier_invoice.supplierID ';
				 $sql.=' AND currency.ID=supplier.currencyID';
				 $sql.=' AND comp_supplier_invoice.ID =  '.$id;
      		 	 break;
			case "comp_supplier_debitnote" :
				$sql='SELECT comp_supplier_debitnote.*,  supplier.supplierName, supplier.line1, supplier.line2, supplier.line3, supplier.city, supplier.supplierPostCode, comp_project.project_name, currency.currencyWord FROM (comp_supplier_debitnote left outer join comp_project on comp_supplier_debitnote.projectID=comp_project.ID), currency , supplier where supplier.ID=comp_supplier_debitnote.supplierID AND currency.ID=supplier.currencyID AND comp_supplier_debitnote.ID =  '.$id;
			   break;
			   /*case "comp_supplier_creditnote_detail" :
			  	 $sql='SELECT comp_supplier_creditnote.*,  supplier.supplierName, comp_project.project_name, currency.currencyWord FROM (comp_supplier_creditnote left outer join comp_project on comp_supplier_creditnote.projectID=comp_project.ID), currency , supplier where comp_supplier_creditnote.updateAble = TRUE AND supplier.ID=comp_supplier_creditnote.supplierID AND currency.ID=supplier.currencyID AND comp_supplier_creditnote.ID =  '.$id;
			 	 break;*/
			  case "comp_supplier_creditnote" :
			  	 $sql='SELECT comp_supplier_creditnote.*, supplier.supplierName, supplier.line1, supplier.line2, supplier.line3, supplier.city, supplier.supplierPostCode, comp_project.project_name, currency.currencyWord ';
				 $sql .= 'FROM (comp_supplier_creditnote left outer join comp_project on comp_supplier_creditnote.projectID=comp_project.ID),';
				 $sql .= ' currency , supplier where  ';
				 $sql .= ' supplier.ID=comp_supplier_creditnote.supplierID ';
				 $sql .= 'AND currency.ID=supplier.currencyID AND comp_supplier_creditnote.ID =  '.$id;
			 	 break;
  	 		
		   }
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}
	}
		public function get_bySupplierID($id,$companyID=0){
		if ($companyID > 0)
		    $this->db->where('companyID',$companyID); 
		$this->db->select('comp_supplier_invoice.*');
               
		$this->db->select('comp_supplier_invoice.ID as suppInvoiceID');
                //$this->db->select("CONCAT(comp_supplier_invoice.formNo, '[', lpad(comp_supplier_invoice.ID,8,'0'), ']') as formNo'");
		$this->db->where('supplierID', $id);
		$this->db->where('totalPayable >', 0);
		$query = $this->db->get('comp_supplier_invoice');
		if($query->num_rows() >0 ){
			return $query->result();
		}else{
			return false;
		}

	}
	
	
	
	public function getChargeByid($id){
		
		$sql='SELECT  comp_supplier_invoice_charges.*, comp_supplier_invoice_charges.ID as myid, tax_master.ID as taxId,  tax_master.code, tax_master.taxPercentage,
		comp_chart_of_acct.* From comp_supplier_invoice_charges, tax_master, comp_chart_of_acct
		Where comp_supplier_invoice_charges.taxID = tax_master.ID and  comp_supplier_invoice_charges.accountNoID = 
		comp_chart_of_acct.ID AND 
		 comp_supplier_invoice_charges.invoiceID =  '.$id;
		$query = $this->db->query($sql);
		$return = NULL;
 
		if($query->num_rows() >0 ){
			return $query->result();
		}else{
			return false;
		}
	}
        
        public function getDebitNoteChargeByid($id){
		
		$sql='SELECT  comp_supplier_debitnote_charges.*, comp_supplier_debitnote_charges.ID as myid, tax_master.ID as taxId,  tax_master.code, tax_master.taxPercentage,
		comp_chart_of_acct.* From comp_supplier_debitnote_charges, tax_master, comp_chart_of_acct
		Where comp_supplier_debitnote_charges.taxID = tax_master.ID and  comp_supplier_debitnote_charges.accountNoID = 
		comp_chart_of_acct.ID AND 
		 comp_supplier_debitnote_charges.debitNoteId =  '.$id;
		$query = $this->db->query($sql);
		$return = NULL;
 
		if($query->num_rows() >0 ){
			return $query->result();
		}else{
			return false;
		}
	}
	
        
          public function getCreditNoteChargeByid($id){
		
		$sql='SELECT  comp_supplier_creditnote_charges.*, comp_supplier_creditnote_charges.ID as myid, tax_master.ID as taxId,  tax_master.code, tax_master.taxPercentage,
		comp_chart_of_acct.* From comp_supplier_creditnote_charges, tax_master, comp_chart_of_acct
		Where comp_supplier_creditnote_charges.taxID = tax_master.ID and  comp_supplier_creditnote_charges.accountNoID = 
		comp_chart_of_acct.ID AND 
		 comp_supplier_creditnote_charges.creditNoteId =  '.$id;
		$query = $this->db->query($sql);
		$return = NULL;
 
		if($query->num_rows() >0 ){
			return $query->result();
		}else{
			return false;
		}
          }
                
	public function checkformNumber_model($t, $fN){
		$sql='SELECT id from '.$t.' where formNo = "'.$fN.'"';
		$query = $this->db->query($sql);

		if($query->num_rows() >= 1){
			return "yes";
		}else{
			return "no";
		}
		}
	
	public function deleteCharge($idCharge){
		$sql='Delete from comp_supplier_invoice_charges where ID = '.$idCharge;
		$query = $this->db->query($sql);
		$return = NULL;
 
		if($query->num_rows() >= 1){
			$return = "true";
		}
		
		return $return;
	}
	
	public function deleteCompanyDetail($id){
		$sql='Delete from comp_supplier_invoice_detail where ID = '.$id;
		$query = $this->db->query($sql);
		$return = NULL;
 
		if($query->num_rows() >= 1){
			$return = "true";
		}
		
		return $return;
	}
	
	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 update_charge ($id, $data) {
		$this->db->where('ID', $id);
		$query = $this->db->update('comp_supplier_invoice_charges', $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
		}
	
	public function update_byID($id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update('comp_supplier_invoice_detail', $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
		public function update_debitNoteDetail($id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update('comp_supplier_debitnote_detail', $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function update_CreditNoteDetail($id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update('comp_supplier_creditnote_detail', $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function updateInvoice($id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update('comp_supplier_invoice', $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
// invoice details
	public function purchaseInvoiceDetails_ID($ID){
		$this->db->where('invoiceID', $ID);
		$this->db->from('view_unpost_ap_invoice');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	 public function purchaseInvoice_post($id, $data){
		$this->db->where('ID', $id);
		$this->db->where('updateAble', 1);
		$query = $this->db->update('comp_supplier_invoice', $data);
		if($query){
			$this->db->flush_cache();
			$this->db->where('ID', $id);
			$this->db->from('comp_supplier_invoice');
			$query1 = $this->db->get();
			return $query1->result();
		}else{
			return false;
		}
	 }
	 public function purchaseInvoiceDetails_invoiceID($journalID, $header, $detailsData, $year, $period){
		if($detailsData){
			$seq = 0;
			$journalAmount = 0;
			foreach($detailsData as $row):
				if($row->amountExcludedtax != 0){
				$seq++;
				$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' => $row->amountExcludedtax * $header->exchangeRate,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->amountExcludedtax,
					'period' => $period,
					'year' => $year,
					'line' => $row->lineNo,
					'formNo' => $row->formNo,
					'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' => $row->amountExcludedtax * $header->exchangeRate,
					'currencyID' => ($header->currencyID == 0)? 1: $header->currencyID,
					'exchange_rate' => $header->exchangeRate,
					'currency_amount' => $row->amountExcludedtax,
					'period' => $period,
					'year' => $year,
					'line' => $row->lineNo,
					'formNo' => $row->formNo,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$journalAmount = $journalAmount + ($row->amountExcludedtax * $header->exchangeRate);
				}
				if($row->taxamount != 0){
				$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,
					'line' => $row->lineNo,
					'formNo' => $row->formNo,
					'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,
					'line' => $row->lineNo,
					'formNo' => $row->formNo,
					'createdBy' => element('role', $this->session->userdata('logged_in'))
				);
				$queryInsert = $this->db->insert('comp_journal_detail', $newData);
				$journalAmount = $journalAmount + ($row->taxamount * $header->exchangeRate);
				}
			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;
		}
	 }
}