/*
Script to find Benefit Type skipped, which are added in Workflow but employee did not do election for the benefit neither waived.
*/
DECLARE @BenefittypeID bigint
DECLARE @Effectivedate as datetime
DECLARE @PlandesignID bigint
DECLARE @EmployeeID bigint
DECLARE @DemographicCriteria varchar(MAX)
DECLARE @BenefitTypeContingencyCriteria  varchar(MAX)
DECLARE @PlanDesignContingencyCriteria  varchar(MAX)
DECLARE @Query as varchar(5000)
DECLARE @Tablename varchar(500)

SET @Effectivedate ='01/01/2017'

--=========================================BENEFIT Cursor Start ===================================

DECLARE @BenefitTypeCur CURSOR
SET @BenefitTypeCur = CURSOR FAST_FORWARD
FOR			
--Active benefits for respective  Effectivedate 
SELECT ID FROM BenefitType WHERE ID IN (
SELECT DISTINCT BenefitTypeID FROM PlanDesign WHERE ID IN (
SELECT PlandesignID from DatingEligibilityCriteria WHERE DATEDIFF(day,@Effectivedate,Planterminationdate) > =0))
AND ID IN (SELECT EmployeeDataScreenID FROM CompanyEmployeeDataScreen WHERE IsEnrollNow = 1)

OPEN @BenefitTypeCur 

FETCH NEXT FROM @BenefitTypeCur INTO @BenefittypeID
WHILE (@@FETCH_status <> -1)
BEGIN

		--=========================================Employee Cursor Start ===================================
		DECLARE @EmployeeIDCur CURSOR
		SET @EmployeeIDCur = CURSOR FAST_FORWARD
		FOR	
		----Query  for not Enrolled employeed for Benefittype
		SELECT  ID FROM Employee WHERE LoginStatusID=1 and ID NOT IN 
		(
			SELECT EmployeeID from OES_VIEW_Enrollment
			WHERE TerminationDate IS NULL
			AND PlanDesignID IN (select ID from Plandesign where BenefittypeID =@BenefittypeID)
			UNION ALL
			SELECT EmployeeID FROM Employee_Enrollment_WaivedBenefitTypes
			WHERE TerminationDate IS NULL
			AND BenefitTypeID=@BenefittypeID
			UNION ALL
			SELECT EmployeeID from Employee_Enrollment_WaivedBenefitTypes_Intermediate
			WHERE TerminationDate IS NULL
			AND BenefitTypeID=@BenefittypeID
		) AND EmployeeStatusID in(1,4,5,8,9,10)
				
		OPEN @EmployeeIDCur 

		FETCH NEXT FROM @EmployeeIDCur INTO @EmployeeID
		WHILE (@@FETCH_status <> -1)
		BEGIN		




			--=========================================PlanDesign Cursor Start ===================================		
			DECLARE @PlandesignCur CURSOR
			SET @PlandesignCur = CURSOR FAST_FORWARD
			FOR	
			SELECT PlandesignID,  Criteria as DemographicHistoryCriteria, BenefitTypeContingencyCriteria, PlanDesignContingencyCriteria 
			FROM  DatingEligibilityCriteria DE
			INNER JOIN PlanDesign p ON p.ID = DE.PlanDesignID
			INNER JOIN genericeligibilityrules GER ON GER.ID= P.GenericEligibilityRulesID		
			WHERE DATEDIFF(DAY,@Effectivedate,Planterminationdate) > =0
			AND PlanDesignID in (select ID from Plandesign where BenefittypeID =@BenefittypeID)	--and p.ID =406

			OPEN @PlandesignCur 

			FETCH NEXT FROM @PlandesignCur INTO @PlandesignID,@DemographicCriteria,@BenefitTypeContingencyCriteria,@PlanDesignContingencyCriteria
			WHILE (@@FETCH_status <> -1)
			BEGIN

			SET @DemographicCriteria ='DECLARE @EmployeeID bigint
			DECLARE @Effectivedate datetime   
			SET @EmployeeID= ' + cast(@EmployeeID as varchar(36)) + ' 
			 SET @Effectivedate = ''' + CONVERT(varchar(10),@Effectivedate,101)+ '''' 
			+ ' INSERT INTO OEAudit (EmployeeID,PlandesignID ,BenefittypeID)  select ID,'  + cast(@PlandesignID as varchar(36)) + ',' 
			+ cast(@BenefittypeID as varchar(36)) + 
			'  from employee
			where ID =@EmployeeID AND Exists ( '  +  @DemographicCriteria + '  ) '
			
					
			IF @BenefitTypeContingencyCriteria <> ''
			BEGIN
				SET @DemographicCriteria =  @DemographicCriteria +  'AND Exists ( '  +  @BenefitTypeContingencyCriteria + '  ) '
			END

			IF @PlanDesignContingencyCriteria <> ''
			BEGIN
				SET @DemographicCriteria =  @DemographicCriteria +  'AND Exists ( '  +  @PlanDesignContingencyCriteria + '   ) '
			END

			Exec( @DemographicCriteria)
			--OEAudit
					
			      
			FETCH NEXT FROM @PlandesignCur INTO @PlandesignID,@DemographicCriteria,@BenefitTypeContingencyCriteria,@PlanDesignContingencyCriteria
			END

			CLOSE @PlandesignCur
			DEALLOCATE @PlandesignCur

			--=========================================PlanDesign Cursor END ===================================		      
		
		FETCH NEXT FROM @EmployeeIDCur INTO @EmployeeID
		END

		CLOSE @EmployeeIDCur
		DEALLOCATE @EmployeeIDCur
		--=========================================Employee Cursor END ===================================

  
FETCH NEXT FROM @BenefitTypeCur INTO @BenefittypeID
END
CLOSE @BenefitTypeCur
DEALLOCATE @BenefitTypeCur
