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

class Company_info_model extends CI_Model{
	
	public function companymaster_all_state(){
		$this->db->select('company.*');
		$this->db->select('_ref_state.fldname');
	    $this->db->where('is_delete', 0);
	    $this->db->where('ID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('company');
		$this->db->join('_ref_state', '_ref_state.fldid = company.companyStateID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function companymaster_id($ID=0){
		//$ID = element('compID', $this->session->userdata('logged_in'));
	    $this->db->where('ID', $ID);
//		$this->db->select('gstRegisteredNo');
		$query = $this->db->get('company', 1);
		//echo $this->db->last_query();		
		if($query->num_rows() == 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function companyInsert($newCompany){
		$sql = $this->db->insert_string('company', $newCompany);
		$query = $this->db->query($sql);
		if($query === TRUE){
			//return new companyid
			return $this->db->insert_id();
		} else {
//			$last_query = $this->db->last_query();
//			return $last_query;
			return false;
		}
	}
	
	
	public function company_all(){
		$this->db->where('is_delete', '');
		$query = $this->db->get('company');		
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	
	public function company_edit($updateuser, $ID){
		$this->db->where('ID', $ID);
		$query = $this->db->update('company', $updateuser); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function company_delete($deletuser, $ID){
		$this->db->where('ID', $ID);
		$query = $this->db->update('company', $deletuser); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function company_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.code as dtFormat');
		$this->db->select('b.codeJava as dtFormatJava');
		$this->db->where('a.ID', $ID);
		$this->db->from('company a');
		$this->db->join('_ref_dateformat b', 'b.ID = a.dateFormat','left outer');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function companyById($id){
		$sql='SELECT company.*, _ref_state.fldname, _ref_country.fldcountry
			  FROM  (company LEFT OUTER JOIN _ref_state ON company.companyStateID=_ref_state.fldid)
			  		 LEFT OUTER JOIN _ref_country ON company.countryID=_ref_country.fldid
					 where company.ID='.$id;
		$query = $this->db->query($sql);
		$return = NULL;
 
		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_receive_payment', $updatepayment); 
		if($query){
			return TRUE;
		}else{
			return FALSE;
		}
		
	}
	
	public function compRecPayment_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.*');
		$this->db->select('c.fldcurr_code');
		$this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as receivePaymentNo', false);
		$this->db->where('a.ID', $ID);
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->from('comp_receive_payment a');
		$this->db->join('customer b', 'b.ID = a.customerID', 'left outer');
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function compRecPaymentDetail_id($ID){
		$this->db->select('a.*');
		$this->db->select('b.formNo as invoiceNo');
		$this->db->select('date_format(b.salesInvoiceDate, "%d-%m-%Y") as invoiceDate', false);
		$this->db->where('a.receivePaymentID', $ID);
		$this->db->from('comp_receive_payment_detail a');
		$this->db->join('comp_sales_invoice b', 'b.ID = a.invoiceID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	public function compRecPaymentDate($dateFrom, $dateTo){
		$this->db->where('comp_receive_payment.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('receivePaymentDate between  "'.$dateFrom.'" and "'.$dateTo.'"');
		$this->db->where('updateAble', 1);
		$this->db->select('comp_receive_payment.*');
                $this->db->select('concat(comp_receive_payment.formNo,"[",lpad(comp_receive_payment.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('date_format(comp_receive_payment.receivePaymentDate, "%d-%m-%Y") as recpaydate', false);
		$this->db->from('comp_receive_payment');
		$this->db->select('customer.customerName as customerName');
		$this->db->join('customer', 'customer.ID = comp_receive_payment.customerID');
		$this->db->order_by('receivePaymentDate desc');
		$query = $this->db->get();
		//echo $this->db->last_query();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//added by akmal 20140530
	//to add default chart of account on creating new company
	public function company_groupChartOfAccount_byCompanyID($id){
		$this->db->where('companyID',$id);
		$query = $this->db->get('comp_acct_group');
		return $query->num_rows();
	}

	public function company_ref_groupChartOfAccount_all(){
		$query = $this->db->get('_ref_acct_group');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	public function company_ref_groupChartOfAccount_byParentID($id){
		$this->db->where('parentID',$id);
		$query = $this->db->get('_ref_acct_group');
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	public function company_new_groupChartOfAccount_insert($data){
//		$sql = $this->db->insert_string('company', $newCompany);
//		$query = $this->db->query($sql);
		$insertString = $this->db->insert_string('comp_acct_group',$data);
//		echo '<br>'.$insertString;
		$query = $this->db->query($insertString);
		//echo $this->db->last_query(); 
		if($query){
			return $this->db->insert_id();
		}else{
			return 0;
		}
	}
	public function company_new_ChartOfAccount_insert($companyID, $bussinessTypeID){
		// comment create company group chart of acct. will user _ref table since the grouping same for all company. 
		//1. make sure comp_acct_group is created. else create first. 
		//2. insert into comp_chart_of_acct follow comp_acct_group
/*		$this->db->where('companyID',$companyID)
//		$this->db->from('comp_acct_group');

		if ($this->db->count_all_results() == 0) {
			//return false
			echo '<script> alert("acct Group not created.'.$companyID.'");
					</script>';

			return false;
			
		}
*/
//will use default group chart of acct as per _ref
		$insertString = 'insert into comp_chart_of_acct ';
		$insertString .= ' (companyID, acctGroupID, acctCode, acctName, acctDesc,createdBy)';
		$insertString .= ' select '.$companyID;
		$insertString .= ',a.acctGroupID,a.acctCode,a.acctName,a.acctDescription,';
		$insertString .= element('role', $this->session->userdata('logged_in'));
		$insertString .= ' from _ref_chart_of_acct a,';
//		$insertString .= ' comp_acct_group b';
		$insertString .= ' where a.businessTypeID='.$bussinessTypeID;
//		$insertString .= ' and b.companyID ='.$companyID;
//		$insertString .= ' and b.refID = a.acctGroupID';
		$insertString .= ' and a.acctCode not in (select acctCode from comp_chart_of_acct where companyID = '.$companyID.')';

/***backup
		/*$insertString = 'insert into comp_chart_of_acct ';
		$insertString .= ' (companyID, acctGroupID, acctCode, acctName, acctDesc,createdBy)';
		$insertString .= ' select '.$companyID;
		$insertString .= ',b.ID as acctGroupID,a.acctCode,a.acctName,a.acctDescription,';
		$insertString .= element('role', $this->session->userdata('logged_in'));
		$insertString .= ' from _ref_chart_of_acct a,';
		$insertString .= ' comp_acct_group b';
		$insertString .= ' where a.businessTypeID='.$bussinessTypeID;
		$insertString .= ' and b.companyID ='.$companyID;
//		$insertString .= ' and b.refID = a.ID';
		$insertString .= ' and a.acctCode not in (select acctCode from comp_chart_of_acct where companyID = '.$companyID.')';*/
//		echo '<br>'.$insertString;
		
		$query = $this->db->query($insertString);
		if($this->db->affected_rows() > 0 ){
			$this->db->where('ID',$companyID)->update('company',array('businessTypeID'=>$bussinessTypeID));
			return true;
		}else{
			return false;
		}
	}
	//end added by akmal 20140530

	public function industry_id($companyID) {
		$this->db->select('a.industryID,b.name as industry,a.businessTypeID,c.name as bussinessType');
		$this->db->where('companyID',$companyID);
		$this->db->from('comp_industry a');
		$this->db->join('master_code b', 'b.ID = a.industryID');
		$this->db->join('master_code c', 'c.ID = a.businessTypeID');

		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}

	public function industry_add($companyID, $industryID, $businessTypeID){
		$insertString = 'insert into comp_industry (companyID, industryID, bussinessTypeID)';
		$insertString .= ' values ('. $companyID.','. $industryID.','. $bussinessTypeID.')';
		$query = $this->db->query($insertString);
		//echo $this->db->last_query();
		return; 
	}
        
    public function getCompanyBuzType($cId){
        $this->db->select('i.name as indusName,biz.name as bizName');
        $this->db->from('_ref_industry as i, company c, _ref_business_type as biz');
		$this->db->where('biz.industryID = i.ID And c.businessTypeID=biz.ID AND c.ID='.$cId);
		$query = $this->db->get();
		//echo $this->db->last_query();
		//exit();
		if($query->num_rows() >= 1){
			$return = $query->result(); 
                        $r =array();
                        foreach ($return as $row){
                            $r["industry"] = $row->indusName;
                            $r["bizName"] = $row->bizName;
                        }
                        return $r;
		}else{
			return false;
		}
    }

}

/* End of file company_info_model.php */
/* Location: ./application/model/company/company_info_model.php */
/* Created : Fadhirul Hilmi edited 26/5/2014 */
