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

class requistion_model extends CI_Model
{
   
    /**
     * This function is used to get the SupplierID and SupplierName information
     * @return array $result : This is result of the query
     */
    function getSupplierName()
    {
        $this->db->select('SupplierID, SupplierName,ADeleteRequistionLineItemddress');
        $this->db->from('T_SupplierDetailsN');
        //$this->db->where('roleId !=', 1);
        $query = $this->db->get();
        
        return $query->result();
    }
	/**
     * This function is used to get the userId information
     * @return array $result : This is result of the query
     */
    function getusers()
    {
        $this->db->select('userId, name');
        $this->db->from('tbl_users');
        //$this->db->where('roleId !=', 1);
        $query = $this->db->get();
        
        return $query->result();
    }
 /* This function is used to  getEditRequistItemList view the information
     * @return array $result : This is result of the query
     */
	function getEditRequistItemList($ReqNo)
    {

		/* $result =   $this->db-> query("CALL P_GET_REQUISTITEMLIST('".$ReqNo."')")  or die(mysql_error());
		
		
			//echo 'REQUISTITEMLIST Line item view successfully';
		
	 

		$result ->next_result();

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

     */
	 $subQuery ='select distinct Req.ReqNo, Mat.MaterialCode,Mat.MaterialName,Mat.UOM, Req.Quantity,if (Po.Quantity is null,0,Po.Quantity)  as POQTY,sta.StatusName 	from T_MaterialMaster Mat join T_Requestion_Details Req on Req.MaterialCode = Mat.MaterialCode
       left join T_PurchaseOrder_LineItem Po on Po.ReqNo =  Req.ReqNo
        left join T_Status sta on sta.StatusCode = Req.Status
        where Req.ReqNo = ?';
			
			 $query = $this->db->query($subQuery, array($ReqNo));	
		 
		   // print_r($this->db->last_query()); 
			return $query->result();
       
    }

	/**
     * This function is used to get the userId information
     * @return array $result : This is result of the query
     */
    function getStatus()
    {
        $this->db->select('StatusCode,StatusName ');
        $this->db->from('T_Status');
		 $this->db->where('StatusType', REQUISTSTATUS);
        $this->db->where('StatusCode !=', REQ_DRAFT);
        $query = $this->db->get();
        
        return $query->result();
    }
    /* This function is used to get the Raw Material  information
     * @return array $result : This is result of the query
     */
	function getRequistionList($ReqNo,$IsArray='')
    {

		$result =   $this->db-> query("CALL P_GET_AllREQUISTION('".$ReqNo."')")  or die(mysql_error());
		
		{
			//echo 'PO Line item Inserted successfully';
			if($IsArray != '')
			{				
				
				 return $result->result_array();
			}
			else
			{
             return $result->result();
			}
		}
		
       
    }
	
	/**
     * This function is used to get Employee details based on Employee ID Selection
     * @return array $result : This is result of the query
     */
	function GetEmployeeDetails($EmpID)
    {
		
		 $this->db->select(' EMP.EmpID,EMP.Designation,DEPT.DEPCode,DEPT.DepartmentName');
         $this->db->from('T_Employee_Details EMP');
		 $this->db->join('T_DepartmentDetails DEPT', 'EMP.Departmentcode = DEPT.DEPCode');
		 $this->db->where('EMP.EmpID ',$EmpID );
		 $query = $this->db->get();
        
         return $query->result_array();
    }
	
	/**
     * This function is used to get the Cost Center details for the Login user department
     * @return array $result : This is result of the query
     */
	function GetCostCenterByDept($DeptId)
    {
		
		 $this->db->select('Mast.CostCenterCode,Mast.CostCenterName');
         $this->db->from('T_CostCenter_Master Mast ');
		 $this->db->join('T_CostCenter_Departments COST', 'COST.CostCenterCode = Mast.CostCenterCode');
		 $this->db->where('COST.DEPCode ',$DeptId );
		 $query = $this->db->get();
        // print_r( $this->db->last_query());
         return $query->result_array();
    }
	/**
     * This function is used to get the Available BudgetAmount for the Cost center
     * @return array $result : This is result of the query
     */
	function GetAvailableBudgetAmount($CostCode,$Year,$BudgetType)
    {
		 $this->db->select('BudgetAmount');
         $this->db->from('T_CostCenter_Budget');
		 $this->db->where('CostCenterCode',$CostCode );
		  $this->db->where('BudgetYear',$Year );
		   $this->db->where('BudgetType',$BudgetType );
		 $query = $this->db->get();
        
         return $query->result_array();
    }
	
	function EditMaterialCode($ReqNo,$ReqType)
	{
			$subQuery ='SELECT Mat.MaterialCode,Mat.MaterialName,Mat.UOM
			from T_MaterialMaster Mat where  Mat.MaterialCode not in (select Req.MaterialCode from T_Requestion_Details Req
			join  T_Requestion_Master mast on Req.ReqNo = mast.ReqNo where mast.ReqNo=? and mast.ReqType=?
			) and Mat.MaterialType=?';
			
			 $query = $this->db->query($subQuery, array($ReqNo,$ReqType,$ReqType));	
		 
		
			return $query->result();
			
	}
	
	
	/**
     * This function is used to get the Material details for the Request type
     * @return array $result : This is result of the query
     */
	function GetMaterialCode($ReqType,$NotArray='')
    {
		 $this->db->select('MaterialCode, MaterialName,UOM');
         $this->db->from('T_MaterialMaster');
		 $this->db->where('MaterialType',$ReqType );
		 
		 $query = $this->db->get();
         if($NotArray == '')
		 {
         return $query->result_array();
		 }
		 else
		 {
			 return $query->result(); 
		 }
    }
	/**
     * This function is used to get the Raw Material  information
     * @return array $result : This is result of the query
     */
	function getRawMaterialList($MaterialCode)
    {		
        $this->db->select('MaterialName,UOM');
        $this->db->from('T_MaterialMaster');
		 $this->db->where('MaterialCode',$MaterialCode );		
        $query = $this->db->get();
        
        return $query->result_array();
    }
	
		/**
     * This function is used to get the Config value from configuration table
     * @return array $result : This is result of the query
     */
	function getConfigValue($ConfigID ,$ConfigValue = '')
    {
		
        $this->db->select('ConfigValue');
        $this->db->from('T_ConfigDetails');
		 $this->db->where('Config_id ',$ConfigID );
		 if($ConfigValue != '')
		 {
			  $this->db->where('ConfigValue != ',$ConfigValue );
		 }
        $query = $this->db->get();
        
        return $query->result();
    }
	
	
	/**
     * This function is used to get the All employees
     * @return array $result : This is result of the query
     */
	function getAllEmployees()
    {
		$this->db->select('EmpID,FirstName,LastName');
        $this->db->from('T_Employee_Details');
		$query = $this->db->get();
        
        return $query->result();
    }
 
  /**
     * This function is used to get the Employee Details
     * @return array $result : This is result of the query
     */
	function getEmpIdByUserID($UserID)
    {
		$subQuery ='SELECT   EMP.EmpID,EMP.FirstName,EMP.Designation,DEPT.DEPCode,DEPT.DepartmentName 
		FROM T_Employee_Details EMP	JOIN T_DepartmentDetails DEPT ON
		EMP.Departmentcode = DEPT.DEPCode
		WHERE EMP.EmpID=(select EmpID from tbl_users where userId=?)';

		 $query = $this->db->query($subQuery, array($UserID));	
		 
		
        return $query->result();
    }
	
	 /**
     * This function is used to get the CostCenter for the Selected Employee
     * @return array $result : This is result of the query
     */
	function getCostCenterUserID($UserID)
    {
		$subQuery ='SELECT  Mast.CostCenterCode,Mast.CostCenterName from T_CostCenter_Master Mast 
		LEFT JOIN T_CostCenter_Departments COST ON COST.CostCenterCode = Mast.CostCenterCode
		WHERE COST.DEPCode=(select Departmentcode from T_Employee_Details where EmpID = (select EmpID from tbl_users where userId=?))';

		  $query = $this->db->query($subQuery, array($UserID));	
		//print_r($this->db->last_query());
        return $query->result();
    }
	
	/**
     * This function is used to get the Purchase order Type
     * @return array $result : This is result of the query
     */
	function CheckDraftStatus($UserID)
    {
		
		$subQuery = 'select count(ReqNo) as cnt,ReqNo from T_Requestion_Master where 
						Requestedby= ( select EmpID from tbl_users where userId=?) and status=?';   
          
        $query = $this->db->query($subQuery, array($UserID,REQ_DRAFT));		
		  
        return $query->result();
    }
	/**
     * This function is used to get the Requsition List for the login user
     * @return array $result : This is result of the query
     */
	function getRequistDetails($ReqID,$NoArray='')  
	{
		
			$subQuery = 'select distinct ReqNo,ReqType,Status,Requestedby,DATE_FORMAT(mast.CreatedDate, "%d-%m-%Y") as CreatedDate ,emp.FirstName,emp.EmpID,emp.Designation, Dept.DEPCode,Dept.DepartmentName,DATE_FORMAT(mast.ReqDate, "%d-%m-%Y") as  ReqDate,CostCenterCode
						from T_Requestion_Master  mast
						join T_Employee_Details emp  on mast.Requestedby = emp.EmpID
						join T_DepartmentDetails Dept on  emp.Departmentcode = Dept.DEPCode
						where ReqNo=?';   
          
        $query = $this->db->query($subQuery, array($ReqID));		
		  if($NoArray == '')
		  {
			return $query->result_array();
		  }
		  else
		  {
			  return $query->result();
		  }
    }
	 /**
     * This function is used to get the Requsition List for the login user
     * @return array $result : This is result of the query
     */
	function getRequistListUserID($UserID)
    {
		
			$subQuery = 'select ReqNo, ReqType ,Status,CreatedDate ,StatusName from  T_Requestion_Master mast
						join T_Status st on st.StatusCode= mast.Status  where 
						Requestedby = ( select EmpID from tbl_users where userId=?) and Status != ? ';   
          
        $query = $this->db->query($subQuery, array($UserID,REQ_DELETED));		
		  
        return $query->result();
    }
	 /**
     * This function is used to get the Requsition List for the login user
     * @return array $result : This is result of the query
     */
	function getRequistItemList($ReqID,$NoArray='')
    {
		
			$subQuery = 'select  distinct  Req.ReqNo, Mat.MaterialCode,Mat.MaterialName,Mat.UOM, Req.Quantity,Po.Quantity as POQTY,sta.StatusName from T_MaterialMaster Mat  
	left join T_Requestion_Details Req on Req.MaterialCode = Mat.MaterialCode
      left join T_PurchaseOrder_LineItem Po on Po.ReqNo =  Req.ReqNo and Po.MaterialCode = Req.MaterialCode
	   left join T_Status sta on sta.StatusCode = Req.Status where Req.ReqNo=?';
  
          
         $query = $this->db->query($subQuery, array($ReqID));		
		  if($NoArray == '')
		  {
			return $query->result_array();
		  }
		  else
		  {
			  return $query->result();
		  }
    }
	
	/**
     * This function is used to add the Requistion into the Database
     * @return array $result : This is result of the query
     */
    function addRequistion($Requistion)
    {
        $this->db->trans_start();
        $this->db->insert('T_Requestion_Master', $Requistion);        
        $insert_id = $this->db->affected_rows();         
        $this->db->trans_complete();      
      	if($insert_id>0)
		{
		$subQuery = 'select max(ReqNo) as ReqNo from T_Requestion_Master';   
          
        $query = $this->db->query($subQuery);
        
        return $query->result_array();
		}
	    
    }
	/**
     * This function is used to check the Request is in Draft status 
     * @return array $result : This is result of the query
     */
	function RequistionIsExists($ReqBy)
    {
		$this->db->select('ReqNo');
        $this->db->from('T_Requestion_Master');
		$this->db->where('Status ',REQ_DRAFT );
		$this->db->where('Requestedby ',$ReqBy );
		$query = $this->db->get();
        
        if ($query->num_rows > 0) {
			//echo $result->num_rows;
            return $query->result_array();
		}       
    }
	/**
     * This function is used to check the Request Line Item is in Draft status 
     * @return array $result : This is result of the query
     */
	function LineItemIsExists($ReqNo,$MaterialCode)
    {
		$this->db->select('ReqNo');
        $this->db->from('T_Requestion_Details');
		$this->db->where('ReqNo ',$ReqNo );
		$this->db->where('MaterialCode ',$MaterialCode );
		$query = $this->db->get();
        
        if ($query->num_rows > 0) {
			//echo $result->num_rows;
            return $query->result_array();
		}       
    }
	/**
     * This function is used to add the Line item details to the Database
     * @return array $result : This is result of the query
     */
    function addRequistionDetails($Requistion)
    {
        $this->db->trans_start();
        $this->db->insert('T_Requestion_Details', $Requistion);        
        $insert_id = $this->db->affected_rows();         
        $this->db->trans_complete();
        // print_r($this->db->last_query());		
      	return $insert_id;
	    
    }
	 /**
     * This function is used to delete the Line item from details into Database
     * @return array $result : This is result of the query
     */
	function DeleteRequistionLineItem( $ReqNo,$MaterialCode)
    {
        $this->db->where('ReqNo', $ReqNo);
		 $this->db->where('MaterialCode', $MaterialCode);
        $this->db->delete('T_Requestion_Details');
        
        return TRUE;
    }
	
	 /**
     * This function is used to update the details into Database
     * @return array $result : This is result of the query
     */
	function UpdateRequistionLineItem($Requist, $ReqNo,$MaterialCode)
    {
        $this->db->where('ReqNo', $ReqNo);
		 $this->db->where('MaterialCode', $MaterialCode);
        $this->db->update('T_Requestion_Details',$Requist);
        
        return TRUE;
    }
	
    /**
     * This function is used to update the details into Database
     * @return array $result : This is result of the query
     */
	function UpdateRequistion($Requistion, $ReqNo)
    {
        $this->db->where('ReqNo', $ReqNo);
        $this->db->update('T_Requestion_Master', $Requistion);
        
        return TRUE;
    }
	
	
	
	 /**
     * This function is used to get the List of  Requist details to the approver
     * @return array $result : This is result of the query
     */
	function getApproverRequistList($UserID,$Status = '',$FromDate='' ,$ToDate='')
    {
		// echo 'UserId: '.$UserID . 'From: '.$FromDate .'TO: '. $ToDate. 'Status: '. $Status;
		
			$subQuery = 'select distinct ReqNo,Status,mast.CreatedDate,Requestedby,emp.FirstName,mast.Comments,st.StatusName
						from T_Requestion_Master  mast join T_Employee_Details emp  on mast.Requestedby = emp.EmpID
						join T_DepartmentDetails Dept on  emp.Departmentcode = Dept.DEPCode
						join T_Status st on st.StatusCode = mast.Status
						where  status not in(?,?,?) and emp.Departmentcode  in (select DEPCode from T_DepartmentDetails where  HeadDept= 
						(select HeadDept from T_DepartmentDetails where DEPCode = (select Departmentcode from T_Employee_Details where 
						EmpID=(select EmpID from tbl_users where userId=?))))';
			$subQuery1 = $subQuery;
			$query ='';
			
         if($FromDate != '' && $ToDate != '' && $Status !='' )
			 {
			
			 $Where = 'and 	mast.CreatedDate >= ?  and   mast.CreatedDate <= ? and Status=?';
			 $subQuery1 .= $Where;
			 $query = $this->db->query($subQuery1, array(REQ_DRAFT,REQ_DELETED,REQ_REJECTED,$UserID,$FromDate,$ToDate,$Status));
		 }	
		 else if($FromDate != '' && $ToDate != '' &&  $Status =='')
		 {
			  $Where = 'and 	mast.CreatedDate >= ?  and   mast.CreatedDate <= ? ';
			  $subQuery1 .= $Where;
			  $query = $this->db->query($subQuery1, array(REQ_DRAFT,REQ_DELETED,REQ_REJECTED,$UserID,$FromDate,$ToDate));
		 }
          else if($Status != '' && $FromDate == '' && $ToDate == '')
		 {
			  $Where = 'and Status = ?';
			  $subQuery1 .= $Where;
			  $query = $this->db->query($subQuery1, array(REQ_DRAFT,REQ_DELETED,REQ_REJECTED,$UserID,$Status));
		 } 
		
		else
		{
			$query = $this->db->query($subQuery, array(REQ_DRAFT,REQ_DELETED,REQ_REJECTED,$UserID));	
		}		
		  
		  //print_r($this->db->last_query());
        return $query->result();
    }
	
	
	
}