-- =============================================
		-- Author:		Sandeep Singh
		-- Create date: 24-01-2013
		-- Description:	To Insert Values In CalenderDays
		-- =============================================
		ALTER PROCEDURE [dbo].[checkLeaveDays] 
		(
		        @EMPID varchar(50),
				@stdate VARCHAR(30),
				@endate VARCHAR(30),
				@UNITID INT,
				@leaveid VARCHAR(20),
                @Finyear varchar(10),
                @MESSAGE VARCHAR(20) OUTPUT,
				@DAY VARCHAR(20) OUTPUT
				--@RESULT INT OUT
		)	
		AS
		
		DECLARE @RESULT INT,
		@R4_PUBHDAY INT,
        @R4_WKLYOFF INT,
		@Sund INT,
		@Mon INT,
		@Tues INT,
		@Wedn INT,
		@Thurs INT,
		@Frid INT,
		@Satr INT,
		@IsLastSaturdayOff INT,
		@IsSecondSaturdayOff INT,
		@OnlyOnHoliday INT,
		@WEEKDAY VARCHAR(20),
        @FROMDATE DATETIME,
        @INDATE VARCHAR(10),
        @TODATE DATETIME,
        @COLNAME VARCHAR(100),
        @id int
		--SET @INDATE=@stdate;
		 CREATE TABLE #TEMPHOLIDAYMAST(STATUS INT)
		select @id=ID from employee where ALTID=@EMPID
		SET @FROMDATE=CONVERT(DATETIME,@stdate,103)
		SET @TODATE=CONVERT(DATETIME,@endate,103)
        select @COLNAME=EmpCol from OrganisationUploadMast where OrgMastID in(SELECT OrgID FROM HOLIDAYMAST WHERE HOLIDAY_DATE BETWEEN  @FROMDATE AND @TODATE  AND HOLIDAY_STATUS=1)
		SELECT @R4_PUBHDAY=R4_PUBHDAY,@R4_WKLYOFF=R4_WKLYOFF,@OnlyOnHoliday=OnlyOnHoliday FROM LEAVERULES WHERE LVIDNO=@leaveid
        --SELECT  FROM LEAVERULES WHERE LVIDNO=@leaveid
        SELECT  @Sund=Sunday,@Mon=Monday,@Tues=Tuesday,@Wedn=Wednesday,@Thurs=Thursday,@Frid=Friday,@Satr=Saturday,@IsLastSaturdayOff=IsLastSaturdayOff,@IsSecondSaturdayOff=@IsSecondSaturdayOff FROM CALENDERDAYS WHERE UNITID=@UNITID AND FINYEAR=@Finyear
            IF @R4_PUBHDAY=1
        BEGIN
			--IF EXISTS(SELECT * FROM HOLIDAYMAST WHERE HOLIDAY_DATE BETWEEN @FROMDATE AND @TODATE AND HOLIDAY_STATUS=1)
		if(@COLNAME !='')
		BEGIN
		 INSERT INTO #TEMPHOLIDAYMAST  EXEC('SELECT COUNT(*) FROM EMPLOYEE WHERE ID='+@id+' AND '+@COLNAME+' IN(SELECT ORGDETID FROM HolidayFlow WHERE HOLIDAY_ID IN(SELECT HOLIDAY_ID FROM HOLIDAYMAST WHERE  HOLIDAY_DATE BETWEEN '''+@FROMDATE+''' AND '''+@TODATE+''' AND HOLIDAY_STATUS=1))')
		 IF EXISTS(SELECT * FROM #TEMPHOLIDAYMAST WHERE STATUS=1)
			BEGIN
            SET @RESULT=1
			RETURN @RESULT
            END 
		END
		ELSE
		BEGIN
			SET @RESULT=0	
		END	
        END
  ELSE IF @OnlyOnHoliday=1
	BEGIN
		--IF EXISTS(SELECT * FROM HOLIDAYMAST WHERE HOLIDAY_DATE BETWEEN @FROMDATE AND @TODATE AND HOLIDAY_STATUS=1)
		SET @RESULT=0	
		if(@COLNAME !='')
		BEGIN
			INSERT INTO #TEMPHOLIDAYMAST  EXEC('SELECT COUNT(*) FROM EMPLOYEE WHERE ID='+@id+' AND '+@COLNAME+' IN(SELECT ORGDETID FROM HolidayFlow WHERE HOLIDAY_ID IN(SELECT HOLIDAY_ID FROM HOLIDAYMAST WHERE  HOLIDAY_DATE BETWEEN '''+@FROMDATE+''' AND '''+@TODATE+''' AND HOLIDAY_STATUS=1))')
			IF EXISTS(SELECT * FROM #TEMPHOLIDAYMAST WHERE STATUS=1)
			BEGIN
				SET @RESULT=1
			END	
		END
	END 
           
           ELSE IF @R4_WKLYOFF=1
				BEGIN
					WHILE (@FROMDATE <= @TODATE)
					BEGIN
						--SET @FROMDATE=CONVERT(DATETIME,@INDATE,103)
						SELECT @WEEKDAY=datename(weekday,@FROMDATE)
						IF (@Sund=0 AND @WEEKDAY='SUNDAY' OR @Mon=0 AND @WEEKDAY='MONDAY' OR @Tues=0 AND @WEEKDAY='TUESDAY' OR @Wedn=0 AND @WEEKDAY='WEDNESDAY' OR @Thurs=0 AND @WEEKDAY='THURSDAY' OR @Frid=0 AND @WEEKDAY='FRIDAY' OR @Satr=0 AND @WEEKDAY='SATURDAY')
					BEGIN
						SET @RESULT=2
						SET @MESSAGE=CONVERT(VARCHAR(30),@FROMDATE,103)
						SET @DAY=@WEEKDAY			
						RETURN @RESULT
					END
						ELSE
					BEGIN
								SET @FROMDATE = DATEADD(DAY,1, @FROMDATE)  -- basically increment by 1 day
					END
						END
								SET @RESULT=-2			
								
		END
		
		RETURN @RESULT