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

class purchaseorder_model extends CI_Model
{
    /**
     * This function is used to get the user listing count
     * @param string $searchText : This is optional search text
     * @return number $count : This is row count
     */
    function purchaseorderListingCount()
    {
        $this->db->distinct();
        $this->db->select('POMast.PONO,supp.SupplierName,TotalOrderValue,PODate,Stat.StatusName,Req.ReqType,DeliveryDate');
        $this->db->from('T_PurchaseOrder_Master POMast');
        $this->db->join('T_SupplierDetailsN supp', 'supp.SupplierID = POMast.SupplierID');
        $this->db->join('T_Status Stat', 'Stat.StatusCode = POMast.Status');
		$this->db->join('T_PurchaseOrder_LineItem LineItem', 'POMast.PONO = LineItem.PONO');
		$this->db->join('T_Requestion_Master Req', 'Req.ReqNo = LineItem.ReqNo');	
       // print_r( $this->db->last_query());
        $query = $this->db->get();
     
        return count($query->result());
    }
    
    /**
     * This function is used to get the user listing count
     * @param string $searchText : This is optional search text
     * @param number $page : This is pagination offset
     * @param number $segment : This is pagination limit
     * @return array $result : This is result
     */
    function purchaseorderListing()
    {
		$this->db->distinct();
        $this->db->select('POMast.PONO,supp.SupplierName,TotalOrderValue,PODate,Stat.StatusName,Stat.StatusCode,Req.ReqType,DeliveryDate');
        $this->db->from('T_PurchaseOrder_Master POMast');
        $this->db->join('T_SupplierDetailsN supp', 'supp.SupplierID = POMast.SupplierID');
        $this->db->join('T_Status Stat', 'Stat.StatusCode = POMast.Status');
		$this->db->join('T_PurchaseOrder_LineItem LineItem', 'POMast.PONO = LineItem.PONO');
		$this->db->join('T_Requestion_Master Req', 'Req.ReqNo = LineItem.ReqNo');	
		
        $query = $this->db->get();
        //print_r( $this->db->last_query());
        $result = $query->result();        
        return $result;
    }
    
    /**
     * This function is used to get the SupplierID and SupplierName information
     * @return array $result : This is result of the query
     */
    function getSupplierName($supID='')
    {
        $this->db->select('SupplierID, SupplierName,Address');
        $this->db->from('T_SupplierDetailsN');
		if($supID != '')
		{
			$this->db->where('SupplierID',$supID);
		}
        //$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 get the Raw Material  information
     * @return array $result : This is result of the query
     */
	function getRawPOMaterialList($ReqNo,$IsArray='')
    {
		
		$result =   $this->db-> query("CALL P_GET_REQUISTIONLIST('".$ReqNo."')")  or die(mysql_error());
		
		$result ->next_result();
		
		//print_r($this->db->last_query());
		
		if ($result->num_rows > 0) 
		{
			//echo 'PO Line item Inserted successfully';
			if($IsArray != '')
			{				
				 return $result->result_array();
			}
			else
			{
             return $result->result();
			}
		}		
       
    }
	
	/** 
	This Function to get the List of Requistion which require to create PO
	*/
	function getAllRequistionListToCreatePO()
    {
		  $result =   $this->db-> query("CALL P_PURCHASEORDER_DETAILS()")  or die(mysql_error());
        
        $result ->next_result();
        
       
        if ($result->num_rows > 0) 
        {
           
             return $result->result();
		}
	}
	
	/**
     * This function is used to get the Raw Material  information
     * @return array $result : This is result of the query
     */
	function getRawMaterialList($MatType='',$ReqNoList='')
    {
		
		$this->db->select('ReqNo,Req.MaterialCode,MaterialName,UOM,Req.Quantity');
		$this->db->from('T_Requestion_Details Req');
		$this->db->join('T_MaterialMaster mat', 'Req.MaterialCode = mat.MaterialCode');
		$this->db->where('mat.MaterialType', $MatType);	
		$this->db->where_in('Req.ReqNo', $ReqNoList);		
        
		$query = $this->db->get();
		//print_r($this->db->last_query());
		return $query->result();
    }
	/**
     * This function is used to get the Raw Material  information
     * @return array $result : This is result of the query
     */
	function getRawMaterialListForPO($MatType='',$ReqNo='')
    {	
		$subQuery ='SELECT ReqNo,Req.MaterialCode,MaterialName,UOM,Req.Quantity 
					FROM T_Requestion_Details Req
					JOIN T_MaterialMaster mat ON Req.MaterialCode = mat.MaterialCode
					where mat.MaterialType = ?
					AND Req.ReqNo = ? 
					AND mat.MaterialCode !=  (SELECT MaterialCode FROM T_PurchaseOrder_LineItem WHERE ReqNo =?
					)';

		 $query = $this->db->query($subQuery,array($MatType,$ReqNo,$ReqNo));			 
		
        return $query->result_array();			
		
		
    }
	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(); */
		 
		 $subQuery ='select BudgetAmount, (select ifnull(sum(TotalOrderValue),0) as Totalvalue from 
					 T_PurchaseOrder_Master PO join  T_PurchaseOrder_LineItem  Item on
					 Item.PONO = PO.PONO  join T_Requestion_Master mast on mast.ReqNo = Item.ReqNo where Item.CostCenterCode=?  and mast.ReqType=?)as Totalvalue 
					from T_CostCenter_Budget Bud where Bud.CostCenterCode=? and 
					BudgetYear = ? and BudgetType =?';
		 
		  $query = $this->db->query($subQuery,array($CostCode,$BudgetType,$CostCode,$Year,$BudgetType));			 
		//print_r($this->db->last_query());
        return $query->result_array();	
		 
    }
	
	/**
     * This function is used to get the Requsition List for the login user
     * @return array $result : This is result of the query
     */
	function getRequistDetails($ReqNoList='')  
	{
		$this->db->distinct();
		$this->db->select('ReqNo,ReqType,Status,Requestedby,mast.CreatedDate,emp.FirstName,emp.EmpID,emp.Designation, Dept.DEPCode,Dept.DepartmentName,mast.ReqDate,mast.CostCenterCode');
		$this->db->from('T_Requestion_Master  mast');
		$this->db->join('T_Employee_Details emp', 'mast.Requestedby = emp.EmpID');
		$this->db->join('T_DepartmentDetails Dept', 'emp.Departmentcode = Dept.DEPCode');
		$this->db->where_in('mast.ReqNo', $ReqNoList);		
        
		$query = $this->db->get();
        //print_r($this->db->last_query());
		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 getRequistDetailsForPO($ReqNoList='')  
	{
		$this->db->distinct();
		$this->db->select('ReqNo,ReqType,Status,Requestedby,mast.CreatedDate,emp.FirstName,emp.EmpID,emp.Designation, Dept.DEPCode,Dept.DepartmentName,mast.ReqDate');
		$this->db->from('T_Requestion_Master  mast');
		$this->db->join('T_Employee_Details emp', 'mast.Requestedby = emp.EmpID');
		$this->db->join('T_DepartmentDetails Dept', 'emp.Departmentcode = Dept.DEPCode');
		$this->db->where_in('mast.ReqNo', $ReqNoList);		

		$query = $this->db->get();

		return $query->result();
    }
	/**
     * This function is used to get the Cost center  information
     * @return array $result : This is result of the query
     */
	function getCostCenter()
    {
		
        $this->db->select('CostName, Description');
        $this->db->from('T_CostCentre');
				
        $query = $this->db->get();
        
        return $query->result();
    }
	
	
	 /**
     * This function is used to get the CostCenter for the Selected Requistion Number
     * @return array $result : This is result of the query
     */
	function getCostCenterbyRequist($ReqNo='',$BudYear='',$ReqType='')
    {
		
		$this->db->distinct();
		$this->db->select('Mast.CostCenterCode,Mast.CostCenterName,Bud.BudgetAmount,ReqNo');
		$this->db->from('T_CostCenter_Master Mast');
		$this->db->join('T_CostCenter_Departments CostDept', 'CostDept.CostCenterCode = Mast.CostCenterCode');
		$this->db->join('T_CostCenter_Budget Bud', 'Bud.CostCenterCode = Mast.CostCenterCode');
		$this->db->join('T_Employee_Details emp', 'CostDept.DEPCode = emp.Departmentcode');
		$this->db->join('T_Requestion_Master req', 'req.Requestedby = emp.EmpID');

		$this->db->where('BudgetType ',$ReqType );
		$this->db->where('BudgetYear ',$BudYear );
		if($ReqNo != '')
		{
		$this->db->where('ReqNo ',$ReqNo);
		}

		$query = $this->db->get();
		return $query->result();
    }
	
/**
     * This function is used to get the Config value from configuration table
     * @return array $result : This is result of the query
     */
	function getRequistionNoFromPO($PONO='')
    {
		
        $this->db->select('ReqNo,');
        $this->db->from('T_PurchaseOrder_LineItem');
		if($PONO != '')
		{
		 $this->db->where('PONO ',$PONO );
		}
		 
        $query = $this->db->get();
        
        return $query->result();
    }
	

	/**
     * 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 Company information from T_Company_Details table
     * @return array $result : This is result of the query
     */
	function getCompanyInformation()
    {
		
        $this->db->select('CompanyName,Address');
        $this->db->from('T_Company_Details');
		
        $query = $this->db->get();
        
        return $query->result();
    }
	/**
     * This function is used to get the Employee List
     * @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 List
     * @return array $result : This is result of the query
     */
	function getLastCreatedPODate()
    {
		$this->db->select('max(PODate) as PODate');
        $this->db->from('T_PurchaseOrder_Master');
		$query = $this->db->get();
        
        return $query->result();
    }
  
    
    /**
     * This function is used to add New Purchase order to  the system
    
     */
    function addPOMaster($POMaster)
	{
			$this->db->trans_start();
			$this->db->insert('T_PurchaseOrder_Master', $POMaster);        
			$insert_id = $this->db->affected_rows();         
			$this->db->trans_complete();
			// print_r($this->db->last_query());		
			if($insert_id>0)
			{
				$subQuery = 'select max(PONO) as PONO from T_PurchaseOrder_Master';   
          
				$query = $this->db->query($subQuery);
        
				return $query->result_array();
			}
	}
	
	
     /**
     * This function is used to add New Purchase order Line Item to  the system    
     */
    function addPOLineItem($POLineItem)
	{
			$this->db->trans_start();
			$this->db->insert('T_PurchaseOrder_LineItem', $POLineItem);        
			$insert_id = $this->db->affected_rows();         
			$this->db->trans_complete();
			// print_r($this->db->last_query());		
			if($insert_id>0)
			{
				$subQuery = 'select max(LineItemNo) as LineItemNo from T_PurchaseOrder_LineItem';   
          
				$query = $this->db->query($subQuery);
        
				return $query->result_array();
			}
	}
    
     /**
     * This function is used to add Revenue Tax  to  the system    
     */
    function addRevenueTax($RevenueTaxList)
	{
			$this->db->trans_start();
			$this->db->insert('T_Revenue_Tax', $RevenueTaxList);        
			$insert_id = $this->db->affected_rows();         
			$this->db->trans_complete();
			return $insert_id;
	}
	 /**
     *  This function is used to add Service Tax  to  the system      
     */
    function addServiceTax($ServiceTaxList)
	{
			$this->db->trans_start();
			$this->db->insert('T_Service_Tax', $ServiceTaxList);        
			$insert_id = $this->db->affected_rows();         
			$this->db->trans_complete();
			return $insert_id;
	}	
	
	
	/**
     *  This function is used to add Service Tax  to  the system      
     */
    function addImportTax($ImportTaxList)
	{
			$this->db->trans_start();
			$this->db->insert('T_Import_Tax', $ImportTaxList);        
			$insert_id = $this->db->affected_rows();         
			$this->db->trans_complete();
			return $insert_id;
	}
	
	
    /**
     * This function used to get user information by id
     * @param number $userId : This is user id
     * @return array $result : This is user information
     */
    function getuserInfo($PO)
    {
        $this->db->select('POType, PODate, SupplierID,SpecialOrder,UserID,RequestedDate,RequesterEmail,RequesterPh, PurchasingCategory,BudgetManager,BudgetManagerDepartment,');
        $this->db->from('T_PurchaseOrderDetails');      
        $this->db->where('PONO', $PO);
        $query = $this->db->get();
        
        return $query->result();
    }
	
	// This Method to get the Service PO Details for Edit the Item
	function GetServicePurchaseOrder($PONO = '')
	{
	  $this->db->distinct();
	  $this->db->select('ReqNo,POMast.PONO,ServiceDescription,supp.SupplierID,supp.SupplierName,supp.Address,PODate,Stat.StatusName,DeliveryDate,DeliveryAddress,Stat.StatusCode');
      $this->db->from('T_PurchaseOrder_Master POMast');
	  $this->db->join('T_SupplierDetailsN supp', 'supp.SupplierID = POMast.SupplierID');
	  $this->db->join('T_Status Stat', 'Stat.StatusCode = POMast.Status');
	  $this->db->join('T_PurchaseOrder_LineItem LineItem', 'POMast.PONO = LineItem.PONO');
	  
      $this->db->where('POMast.PONO', $PONO);
      $query = $this->db->get();
	  
	  $result = $query->result();        
	 
      return $result;	
	}
    
	// This Method to get the Service PO  Child Details for Edit the Item
	function GetServicePurchaseOrderDetails($PONO = '')
	{
	     
	$subQuery ='SELECT distinct LineItem.LineItemNo,ReqNo,Mat.MaterialCode,Mat.MaterialName,Mat.UOM,Quantity,Rate,Status,ROUND((Quantity *Rate),2 ) as BasicValue ,ROUND((ServiceTax + EducessTax + SecHigherEducessTax + KrishiKalyantax + SwachhBharattax),2)as Taxamount
				,TotalValue,ServiceTax,EducessTax,SecHigherEducessTax,KrishiKalyantax,SwachhBharattax,CostCenterCode
				FROM T_PurchaseOrder_LineItem LineItem 
				join T_MaterialMaster Mat on Mat.MaterialCode = LineItem.MaterialCode
				join T_Service_Tax Tax on Tax.LineItemNo = LineItem.LineItemNo
				where LineItem.PONO =?';

		 $query = $this->db->query($subQuery,array($PONO));			 
		
        return $query->result();	  
	 
			
	}
	
	// This Method to get the Service PO  Child Details for Edit the Item
	function GetServicePurchaseOrderDetailsForPrint($PONO = '')
	{
	     
	$subQuery ='SELECT distinct LineItem.LineItemNo,ReqNo,Mat.MaterialCode,Mat.MaterialName,Mat.UOM,
				Quantity,Rate,ROUND((Quantity *Rate),2 ) as BasicValue ,ROUND((ServiceTax + EducessTax + SecHigherEducessTax + KrishiKalyantax + SwachhBharattax),2)as Taxamount
				,TotalValue,ServiceTax,EducessTax,SecHigherEducessTax,KrishiKalyantax,SwachhBharattax,CostCenterCode				,sup.SupplierName,sup.Address,POMaster.DeliveryAddress,POMaster.DeliveryDate,POMaster.PODate,POMaster.PONO,POMaster.ServiceDescription FROM T_PurchaseOrder_LineItem LineItem 
				join T_MaterialMaster Mat on Mat.MaterialCode = LineItem.MaterialCode
				join T_Service_Tax Tax on Tax.LineItemNo = LineItem.LineItemNo
				join T_PurchaseOrder_Master POMaster on POMaster.PONO = LineItem.PONO
				join T_SupplierDetailsN sup on sup.SupplierID = POMaster.SupplierID
				where LineItem.PONO =?';

		 $query = $this->db->query($subQuery,array($PONO));			 
		
        return $query->result();	  
	 
			
	}
	
	// This Method to get the Revenue PO  Child Details for Edit the Item
	function GetRevenuePurchaseOrderDetails($PONO = '')
	{
	     
	$subQuery ='SELECT distinct LineItem.LineItemNo,Req.ReqNo,Mat.MaterialCode,Mat.MaterialName,Mat.UOM,Quantity,Rate,Req.Status,ROUND((Quantity *Rate),2 ) as BasicValue , ROUND(((AfterPackagingValue + AfterFreightValue + AfterExciseDuty + AfterVAT + AfterCST + AfterGST + AfterOtherTaxes + Insurance) - AfterDiscount ),2)as  Taxamount 	,TotalValue,Tax.*,Req.CostCenterCode,Dept.DepartmentName
		FROM T_PurchaseOrder_LineItem LineItem 
				join T_MaterialMaster Mat on Mat.MaterialCode = LineItem.MaterialCode
				join T_Revenue_Tax Tax on Tax.LineItemNo = LineItem.LineItemNo
				join T_Requestion_Master Req on  Req.ReqNo = LineItem.ReqNo 
				join T_Employee_Details emp  on Req.Requestedby = emp.EmpID
				join T_DepartmentDetails Dept on  emp.Departmentcode = Dept.DEPCode
				where LineItem.PONO =?';

		 $query = $this->db->query($subQuery,array($PONO));			 
		
        return $query->result();	  
	 
			
	}
	
	// This Method to get the Revenue PO  Child Details for Edit the Item
	function GetRevenuePurchaseOrderDetailsForPDF($PONO = '')
	{
	     
			$subQuery ='SELECT distinct LineItem.LineItemNo,ReqNo,Mat.MaterialCode,Mat.MaterialName,
			Mat.UOM,Quantity,Rate,ROUND((Quantity 	*Rate),2 ) as BasicValue ,
			ROUND(((AfterPackagingValue + AfterFreightValue + AfterExciseDuty + AfterVAT + AfterCST + 
			AfterGST + AfterOtherTaxes + Insurance)) ,2)as  Taxamount 	,
			TotalValue,Tax.*,CostCenterCode ,	POMaster.*,sup.SupplierName,sup.Address
			FROM T_PurchaseOrder_LineItem LineItem 
			join T_MaterialMaster Mat on Mat.MaterialCode = LineItem.MaterialCode
			join T_Revenue_Tax Tax on Tax.LineItemNo = LineItem.LineItemNo
			join T_PurchaseOrder_Master POMaster on POMaster.PONO = LineItem.PONO
			join T_SupplierDetailsN sup on sup.SupplierID = POMaster.SupplierID
			where LineItem.PONO =?';

		 $query = $this->db->query($subQuery,array($PONO));			 
		
        return $query->result();	  
	 
			
	}


function GetImportPurchaseOrderDetailsForPDF($PONO = '')
	{
	     
			$subQuery ='SELECT distinct LineItem.LineItemNo,ReqNo,Mat.MaterialCode,Mat.MaterialName,
			Mat.UOM,Quantity,Rate,ROUND((RMCIncludingCustomersPerKG*Quantity),2)as  Taxamount 	,
			ProductPrice,Tax.*,CostCenterCode ,	POMaster.*,sup.SupplierName,sup.Address
			FROM T_PurchaseOrder_LineItem LineItem 
			join T_MaterialMaster Mat on Mat.MaterialCode = LineItem.MaterialCode
			join T_Import_Tax Tax on Tax.LineItemNo = LineItem.LineItemNo
			join T_PurchaseOrder_Master POMaster on POMaster.PONO = LineItem.PONO
			join T_SupplierDetailsN sup on sup.SupplierID = POMaster.SupplierID
			where LineItem.PONO =?';

		 $query = $this->db->query($subQuery,array($PONO));			 
		
        return $query->result();	  
	 
			
	}

   
function GetImportPurchaseOrderDetails($PONO = '')
	{
	     
	$subQuery ='SELECT distinct LineItem.LineItemNo,Req.ReqNo,Mat.MaterialCode,Mat.MaterialName,
Mat.UOM,Quantity,Rate,Req.Status,ROUND((Quantity *Rate),2 ) as BasicValue ,
 ROUND((RMCIncludingCustomersPerKG*Quantity),2)as Taxamount,LandingCharge,HighSeasSalesCharge,CustomDuty,ExciseDuty,ExciseDutyEdCess,CustomEdCess,ExchangeRate,AfterLandingCharge,AfterHighSeasSalesCharge,AfterCustomDuty,AfterExciseDuty,AfterExciseDutyEdCess,AddlExciseDuty,AfterAddlExciseDuty,Grossdutypayable,AvailableModvat,Grossexpensesduetocustomduty,purchaseratePerKG,CustomDutyExpensesPerKG,RMCIncludingCustomersPerKG,QuantityKG,BasicPriceInMTon,ProductPrice,CustomSHCess,AfterCustomSHCess,AfterExciseDutySHCess,ExciseDutySHCess,AfterCustomEdCess,ROUND((Quantity *Rate)+(RMCIncludingCustomersPerKG*Quantity),2)as TotalOrderValue,ROUND((ExchangeRate*BasicPriceInMTon),2) as BasicINRValue,
 
 Req.CostCenterCode,Dept.DepartmentName FROM T_PurchaseOrder_LineItem LineItem 
 join T_MaterialMaster Mat on Mat.MaterialCode = LineItem.MaterialCode 
 join T_Import_Tax Tax on Tax.LineItemNo = LineItem.LineItemNo 
 join T_Requestion_Master Req on Req.ReqNo = LineItem.ReqNo 
 join T_Employee_Details emp on Req.Requestedby = emp.EmpID 
 join T_DepartmentDetails Dept on emp.Departmentcode = Dept.DEPCode where LineItem.PONO =?';
				//print_r($subQuery);

		 $query = $this->db->query($subQuery,array($PONO));			 
		
        return $query->result();	  
	 
			
	}


function GetCurrencytype()
    {
			
			$subQuery = 'select CurrencyCode,CurrencyName from T_Currency_Details';
	
        $query = $this->db->query($subQuery);
     
       
        return $query->result();
    }


	// To Update the PO
	function updatePOMaster($PONO,$PODetails)    
	{
	     $this->db->where('PONO', $PONO);
         $this->db->update('T_PurchaseOrder_Master', $PODetails);
        
        return TRUE;
	}
	/**
     * This function is used to check the Line Item is already available in the database 
     * @return array $result : This is result of the query
     */
	function LineItemExists($LineItemNo)
    {
		$this->db->select('LineItemNo,TaxID');
        $this->db->from('T_Service_Tax');
		$this->db->where('LineItemNo ',$LineItemNo );
		//$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 Revenue Line Item is already available in the database 
     * @return array $result : This is result of the query
     */
	function LineItemExistsinRevenueTax($LineItemNo)
    {
		$this->db->select('LineItemNo');
        $this->db->from('T_Revenue_Tax');
		$this->db->where('LineItemNo ',$LineItemNo );
		$query = $this->db->get();
        
        if ($query->num_rows > 0) {
			 return $query->result_array();
		}       
    }


    function LineItemExistsinImportTax($LineItemNo)
    {
		$this->db->select('LineItemNo');
        $this->db->from('T_Import_Tax');
		$this->db->where('LineItemNo ',$LineItemNo );
		$query = $this->db->get();
        
        if ($query->num_rows > 0) {
			 return $query->result_array();
		}       
    }
	// To Update the PO Line Item
	function updatePOLineItem($PONO,$LineItemNo,$PODetails)    
	{
	     $this->db->where('PONO', $PONO);
		 $this->db->where('LineItemNo', $LineItemNo);
         $this->db->update('T_PurchaseOrder_LineItem', $PODetails);
        
        return TRUE;
	}
	/*// To Update the Service Tax Item */
	function updateServiceTax($LineItemNo,$PODetails)    
	{
	    
		   $this->db->where('LineItemNo', $LineItemNo);
         $this->db->update('T_Service_Tax', $PODetails);
		 
		// print_r($this->db->last_query());
        
        return TRUE;
	}
	
	/*// To Update the Revenue Tax Item */
	function updateRevenueTax($LineItemNo,$PODetails)    
	{
	      $this->db->where('LineItemNo', $LineItemNo);
         $this->db->update('T_Revenue_Tax', $PODetails);
		 
		// print_r($this->db->last_query());
        
        return TRUE;
	}

	function updateImportTax($LineItemNo,$PODetails)    
	{
	      $this->db->where('LineItemNo', $LineItemNo);
         $this->db->update('T_Import_Tax', $PODetails);
		 
		// print_r($this->db->last_query());
        
        return TRUE;
	}
  
	function getLastInsertedPO()
	{
		$this->db->select('max(PONO) as PO');
		$this->db->from('T_PurchaseOrderDetails');    		
		$query = $this->db->get();
        
		return $query->result_array();
	}
  
  
  /* This Method to get all the Department name for the requistion	*/
	function getDepartmentListForAllReq()
	{
		$subQuery ='SELECT DISTINCT DEPT.DEPCode,DEPT.DepartmentName FROM T_Requestion_Master REQ
					JOIN T_Employee_Details EMP ON REQ.Requestedby = EMP.EmpID
					JOIN T_DepartmentDetails DEPT ON EMP.Departmentcode = DEPT.DEPCode WHERE REQ.Status not in(?,?,?,?)';

		 $query = $this->db->query($subQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED));			 
		//print_r($query);
        return $query->result();
	}
	/*
	This Method to get all department list
	*/
	function getDepartmentAllList()
	{
		$subQuery ='SELECT DISTINCT DEPCode,DepartmentName FROM T_DepartmentDetails';

		 $query = $this->db->query($subQuery);			 
		//print_r($query);
        return $query->result();
	}
	
	
	function getStatusListforAllReq()
	{
		$subQuery ='SELECT DISTINCT STAT.StatusCode, STAT.StatusName FROM T_Requestion_Master REQ
					JOIN T_Status STAT on REQ.Status = STAT.StatusCode where STAT.StatusCode not in (?,?,?,?)';
	   $query = $this->db->query($subQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED));

         // print_r( $this->db->last_query());	   
		
        return $query->result();
	}
	
	function getStatusByDepartment($DepNo='')
	{
		
		$subQuery ='SELECT DISTINCT STAT.StatusCode ,STAT.StatusName FROM T_Requestion_Master REQ
					JOIN T_Employee_Details EMP ON REQ.Requestedby = EMP.EmpID
					JOIN T_DepartmentDetails DEPT ON EMP.Departmentcode = DEPT.DEPCode
					JOIN T_Status STAT ON REQ.Status = STAT.StatusCode 
					WHERE DEPT.DEPCode=? AND REQ.Status NOT IN (?,?,?,?)';
	   $query = $this->db->query($subQuery,array($DepNo,REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED));
    	
        return $query->result_array();
	}
	
	function getRequistionListbySearch($Depid='',$Status='',$FromDate='',$ToDate='')
	{
		$subQuery =' SELECT  ReqNo,ReqType,Requestedby,EMP.FirstName,EMP.Designation,STAT.StatusCode ,STAT.StatusName,ReqDate,DEPT.DEPCode,DEPT.DepartmentName FROM T_Requestion_Master REQ
			JOIN T_Employee_Details EMP ON REQ.Requestedby = EMP.EmpID
		JOIN T_DepartmentDetails DEPT ON EMP.Departmentcode = DEPT.DEPCode
		JOIN T_Status STAT on REQ.Status = STAT.StatusCode 
		where REQ.Status  NOT IN (?,?,?,?,?)';
		
		$appendQuery = $subQuery ;
		
		if($Depid != '' && $Status  != '' && $FromDate != '' && $ToDate != '')
		{
		 $appendQuery .= ' AND DEPT.DEPCode=? AND STAT.StatusCode=? AND 	ReqDate >= ?  AND   ReqDate <= ?';
		//echo "$appendQuery";die();
		 $query = $this->db->query($appendQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED,REQ_PO_CREATED,$Depid ,$Status,$FromDate,$ToDate));
    	 //print_r($this->db->last_query());
         return $query->result_array();
		}
		else if($Depid != '' && $Status  == '' && $FromDate == '' && $ToDate == '')
		{
			$appendQuery .= ' AND DEPT.DEPCode=?';
		 
		 $query = $this->db->query($appendQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED,REQ_PO_CREATED,$Depid));
    	 //print_r($this->db->last_query());
         return $query->result_array();
		}
		else if($Depid == '' && $Status  != '' && $FromDate == '' && $ToDate == '')
		{
			$appendQuery .= ' AND STAT.StatusCode=?';
		 
		 $query = $this->db->query($appendQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED,REQ_PO_CREATED,$Status));
    	 //print_r($this->db->last_query());
         return $query->result_array();
		}
		else if($Depid == '' && $Status  == '' && $FromDate != '' && $ToDate != '')
		{
			$appendQuery .= ' AND 	ReqDate >= ?  AND   ReqDate <= ?';
		 
		   $query = $this->db->query($appendQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED,REQ_PO_CREATED,$FromDate,$ToDate));
    	 //print_r($this->db->last_query());
           return $query->result_array();
		}
		else if($Depid != '' && $Status  != '' && $FromDate == '' && $ToDate == '')
		{
			$appendQuery .= ' AND DEPT.DEPCode=? AND STAT.StatusCode=?';
		 
		   $query = $this->db->query($appendQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED,REQ_PO_CREATED,$Depid ,$Status));
    	// print_r($this->db->last_query());
           return $query->result_array();
		}
		else if($Depid != '' && $Status  == '' && $FromDate != '' && $ToDate != '')
		{
			$appendQuery .= ' AND DEPT.DEPCode=? AND 	ReqDate >= ?  AND   ReqDate <= ?';
		 
		   $query = $this->db->query($appendQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED,REQ_PO_CREATED,$Depid ,$FromDate,$ToDate));
    	 //print_r($this->db->last_query());
           return $query->result_array();
		}
		else if($Depid == '' && $Status  != '' && $FromDate != '' && $ToDate != '')
		{
			$appendQuery .= ' AND STAT.StatusCode=? AND 	ReqDate >= ?  AND   ReqDate <= ?';
		 
		   $query = $this->db->query($appendQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED,REQ_PO_CREATED,$Status ,$FromDate,$ToDate));
    	// print_r($this->db->last_query());
           return $query->result_array();
		}
		else
		{
			 $query = $this->db->query($subQuery,array(REQ_DRAFT,REQ_PENDING_APPROVAL,REQ_DELETED,REQ_REJECTED,REQ_PO_CREATED));
			//print_r($this->db->last_query());
             return $query->result();
		}
	
	}
	
	function UpdateRequistItemStatus()
	{
		$query = $this->db->query("update T_Requestion_Details REQ
						JOIN T_PurchaseOrder_LineItem line ON REQ.ReqNo = line.ReqNo
						JOIN T_PurchaseOrder_Master PO ON PO.PONO = line.PONO 
						set    REQ.Status = CASE WHEN REQ.Quantity = line.Quantity THEN 'ST022' WHEN REQ.Quantity > line.Quantity THEN 'ST023' ELSE 'ST003' END ,
						REQ.updatedOn= CURRENT_TIMESTAMP 
						where  PO.Status ='".PO_CREATED."'");
		
		//$query ->next_result();	
		
	}
	function UpdateRequistionStatus()
	{
		$result =   $this->db-> query("CALL P_UPDATE_REQUISTIONSTATUS()")  or die(mysql_error());
		
		$result ->next_result();	
		
	}
	/**
     * This function is used to delete the Line item from details into Database
     * @return array $result : This is result of the query
     */
	function DeletePOLineItem( $LineItem='',$ReqNo='',$MaterialCode='')
    {
        $this->db->where('LineItemNo', $LineItem);
		 $this->db->where('ReqNo', $ReqNo);		  
		 $this->db->where('MaterialCode', $MaterialCode);
        $this->db->delete('T_PurchaseOrder_LineItem');
        
        return TRUE;
    }
	
	/**
     * This function is used to delete the Revenue item from details into Database
     * @return array $result : This is result of the query
     */
	function DeletePORevenueTax( $LineItem='')
    {
        $this->db->where('LineItemNo', $LineItem);
		$this->db->delete('T_Revenue_Tax');
        
        return TRUE;
    }
	
	/**
     * This function is used to delete Service Tax from details into Database
     * @return array $result : This is result of the query
     */
	function DeletePOServiceTax( $LineItem='')
    {
        $this->db->where('LineItemNo', $LineItem);
		$this->db->delete('T_Service_Tax');
        
        return TRUE;
    }
	

	function DeletePOImportTax( $LineItem='')
    {
        $this->db->where('LineItemNo', $LineItem);
		$this->db->delete('T_Import_Tax');
        
        return TRUE;
    }
	
	/**
     * This function is used to get the List of  PO details to the approver
     * @return array $result : This is result of the query
	 join T_DepartmentDetails Dept on  emp.Departmentcode = Dept.DEPCode 
     */
	function getPOList($q)
    {
		
		  $this->db->distinct();
		  $this->db->select('mast.PONO,mast.TotalOrderValue,st.StatusName,emp.FirstName,mast.CreatedDate,ccm.CostCenterName,rmast.ReqType,bud.BudgetAmount,mast.Remarks');
		  $this->db->from('T_PurchaseOrder_Master mast');
		  $this->db->join('T_Status st', 'st.StatusCode=mast.Status');
		  $this->db->join('tbl_users tbl', 'mast.createdby=tbl.userid');
		  $this->db->join('T_Employee_Details as emp', 'emp.empid=tbl.empid');
		  $this->db->join('T_PurchaseOrder_LineItem req', 'req.PONO= mast.PONO');
		  $this->db->join('T_Requestion_Master rmast','rmast.ReqNo=req.ReqNo');
		  $this->db->join('T_CostCenter_Budget bud','bud.CostCenterCode = req.CostCenterCode and bud.BudgetType = rmast.ReqType');
		  $this->db->join('T_CostCenter_Master ccm','ccm.CostCenterCode = req.CostCenterCode and ccm.CostCenterCode = bud.CostCenterCode');
		  if($q=='A'){
		  $this->db->where('mast.Status','ST015');//PO CREATED
		  $this->db->or_where('mast.Status','ST020');//ON HOLD
		  $this->db->or_where('mast.Status','ST025');// PO APPROVED
		  $this->db->or_where('mast.Status','ST026');//PO RELEASED
		  $this->db->or_where('mast.Status','ST024'); // EMERGENGY PO
		  }
		  else if($q=='R'){
		  $this->db->or_where('mast.Status','ST025');// PO APPROVED
		  }
		  $result=$this->db->get();
         // print_r($result->result());
		  return $result->result();
		
		
    }
	
	
	function UpdatePO($store, $PONO)
    {
		
        $this->db->where('PONO', $PONO);
        $this->db->update('T_PurchaseOrder_Master', $store);
        
        return TRUE;
    }
	
	function GetReleasedDetails($PONO){
	$this->db->select('mast.ReleasedOn,re.FirstName');
	$this->db->from('T_PurchaseOrder_Master mast');
	$this->db->join('tbl_users tbl','mast.ReleasedBy=tbl.userid');
	$this->db->join('T_Employee_Details re','re.EmpID=tbl.EmpID');
	$this->db->where('mast.PONO',$PONO);
	$result=$this->db->get();
	
	return $result->result();  
	
	}
}