
ALTER PROCEDURE [sBiometric].[pMonthTransProcess]

AS
SET NOCOUNT ON

BEGIN
--Drop temp table for server issue(tempdb)
if object_id('tempdb..#tTable') is not null
BEGIN
	DROP TABLE #tTable
END
	CREATE TABLE #tTable(
	Id bigint identity,
	[SourceId] [bigint] NOT NULL,
	[In_Out_Time] [datetime] NOT NULL,
	[ShiftCode] [int] NOT NULL,
	[Attn_Dt] [date] NOT NULL,
	[Mode] [nvarchar](10) NOT NULL,
	[CreatedDateTime] [datetime] NOT NULL,
	[DeviceIP] [nvarchar](20) NOT NULL,
	[EntryMode] [tinyint] NOT NULL,
	[PunchId] [nvarchar](50) NOT NULL,
	[TransactionId] [bigint] NOT NULL,
	[SourceTypeId] [smallint] NULL,
	[IsProcessed] [bit] NULL
)

DECLARE @lPrevAttn_Dt date = ''
DECLARE @lPrevSourceId bigint = 0
DECLARE @lPrevSourceTypeId smallint = 0

IF(DAY(sCommon.fGetDate()) > 10)
BEGIN
	INSERT INTO #tTable
	SELECT [SourceId],[In_Out_Time],[ShiftCode],[Attn_Dt] ,[Mode],[CreatedDateTime],[DeviceIP],[EntryMode],[PunchId],[TransactionId],[SourceTypeId],[IsProcessed] 
	FROM [sBiometric].[tCloudTransactionData] WHERE MONTH(Attn_Dt) = MONTH(sCommon.fGetDate()) AND YEAR(Attn_Dt) = YEAR(sCommon.fGetDate()) AND IsProcessed = 0
END
ELSE
BEGIN
	INSERT INTO #tTable
	SELECT [SourceId],[In_Out_Time],[ShiftCode],[Attn_Dt] ,[Mode],[CreatedDateTime],[DeviceIP],[EntryMode],[PunchId],[TransactionId],[SourceTypeId],[IsProcessed] 
	 FROM [sBiometric].[tCloudTransactionData] WHERE YEAR(Attn_Dt) = YEAR(sCommon.fGetDate()) AND MONTH(Attn_Dt) IN (MONTH(sCommon.fGetDate()),MONTH(sCommon.fGetDate()) - 1) AND IsProcessed = 0	  --mantis: 34236, change (MONTH(sCommon.fGetDate()),MONTH(sCommon.fGetDate() - 1)) => (MONTH(sCommon.fGetDate()),MONTH(sCommon.fGetDate()) - 1)
END

	SELECT * FROM #tTable
	DECLARE @lCount int = 1
	DECLARE @lTotal int = (SELECT COUNT(Id) FROM #tTable)
	WHILE @lCount <= @lTotal
	BEGIN
		DECLARE @lQuery nvarchar(max) = ''	
		DECLARE @lAttn_Dt date = (SELECT Attn_Dt FROM #tTable WHERE Id = @lCount)
		DECLARE @lSourceId bigint = (SELECT SourceId FROM #tTable WHERE Id = @lCount)
		DECLARE @lSourceTypeId smallint = (SELECT SourceTypeId FROM #tTable WHERE Id = @lCount)
		DECLARE @lTransactionId bigint = (SELECT TransactionId FROM #tTable WHERE Id = @lCount)

		IF(@lPrevAttn_Dt = @lAttn_Dt AND @lPrevSourceId = @lSourceId AND @lPrevSourceTypeId = @lSourceTypeId)
		BEGIN
			print('same employee')
			UPDATE sBiometric.tCloudTransactionData SET IsProcessed = 1 WHERE TransactionId = @lTransactionId
		END
		ELSE
		BEGIN
			DECLARE @lCode int = (SELECT ISNULL(MAX(EmpCode),0) + 1 FROM sBiometric.tMonthTrans)
			IF EXISTS (SELECT 1 FROM sBiometric.tMonthTrans WHERE EmployeeId = @lSourceId AND SourceTypeId = @lSourceTypeId)
			BEGIN
				SET @lCode = (SELECT TOP 1 EmpCode FROM sBiometric.tMonthTrans WHERE EmployeeId = @lSourceId AND SourceTypeId = @lSourceTypeId)
			END					
			DECLARE @lShift varchar(25) = (SELECT ShiftName FROM sBiometric.tShiftMasterCloud WHERE ShiftCode IN (SELECT ShiftCode FROM #tTable WHERE Id = @lCount))
			DECLARE @lShiftStartTime datetime = (SELECT StartTime FROM sBiometric.tShiftMasterCloud WHERE ShiftCode IN (SELECT ShiftCode FROM #tTable WHERE Id = @lCount))
			DECLARE @lShiftEndTime datetime = (SELECT EndTime FROM sBiometric.tShiftMasterCloud WHERE ShiftCode IN (SELECT ShiftCode FROM #tTable WHERE Id = @lCount))
			DECLARE @lMinHoursHalfDay time = CAST(CAST((SELECT MinHrsHalfDay FROM sBiometric.tShiftMasterCloud WHERE ShiftCode IN (SELECT ShiftCode FROM #tTable WHERE Id = @lCount)) AS nvarchar) AS time)
			DECLARE @lMinHoursFullDay time = CAST(CAST((SELECT MinHrsFullDay FROM sBiometric.tShiftMasterCloud WHERE ShiftCode IN (SELECT ShiftCode FROM #tTable WHERE Id = @lCount)) AS nvarchar) AS time)
			DECLARE @lIn time = CONVERT(time,(SELECT MIN(In_Out_Time) FROM sBiometric.tCloudTransactionData WHERE Attn_Dt = @lAttn_Dt AND SourceId = @lSourceId AND SourceTypeId = @lSourceTypeId))
			DECLARE @lOut time = CASE WHEN (SELECT COUNT(In_Out_Time) FROM sBiometric.tCloudTransactionData WHERE Attn_Dt = @lAttn_Dt AND SourceId = @lSourceId AND SourceTypeId = @lSourceTypeId) > 1 THEN CONVERT(time,(SELECT MAX(In_Out_Time) FROM #tTable WHERE Attn_Dt = @lAttn_Dt AND SourceId = @lSourceId AND SourceTypeId = @lSourceTypeId))
									ELSE '00:00' END

			DECLARE @lTotalTime varchar(10) = CASE WHEN @lOut <> '00:00' THEN (SELECT CONVERT(varchar(10), DATEADD(minute, DATEDIFF(minute, @lIn, @lOut), 0), 114))	
												ELSE '00:00' END

			DECLARE @lEarlyBy varchar(10) = '00:00'
			DECLARE @lLateBy varchar(10) = '00:00'
			DECLARE @lOverTime varchar(10) = ''
			DECLARE @lStatus varchar(10) = ''

			--Add by Nimesh MantisId:0027752
			--Start
			IF(DATEDIFF(minute, @lShiftStartTime,@lIn) > 0 AND @lIn<>'00:00')
			BEGIN
				--SET @lLateBy = '00:00'
				SET @lLateBy = (SELECT CONVERT(varchar(10), DATEADD(minute, DATEDIFF(minute, @lShiftStartTime, @lIn), 0), 114))
			END
			IF(DATEDIFF(minute,@lShiftEndTime, @lOut) < 0 AND @lOut<> '00:00')
			BEGIN
				--SET @lEarlyBy = '00:00'
				SET @lEarlyBy = (SELECT CONVERT(varchar(10), DATEADD(minute, DATEDIFF(minute, @lOut, @lShiftEndTime), 0), 114))
			END
			--End

			IF(DATEDIFF(minute, @lShiftEndTime,@lOut) < 0)
			BEGIN
				SET @lOverTime = '00:00'
			END
			ELSE
			BEGIN
				SET @lOverTime  = CASE WHEN @lOut <> '00:00' THEN (SELECT CONVERT(varchar(10), DATEADD(minute, DATEDIFF(minute, @lShiftEndTime, @lOut), 0), 114))
												ELSE '00:00' END
			END			

			IF(CAST(@lTotalTime AS TIME) >= @lMinHoursFullDay)
			BEGIN
				SET @lStatus = 'P'
			END
			IF(CAST(@lTotalTime AS TIME) >= @lMinHoursHalfDay AND CAST(@lTotalTime AS TIME) < @lMinHoursFullDay)
			BEGIN
				SET @lStatus = 'HD'
			END
			IF(CAST(@lTotalTime AS TIME) < @lMinHoursHalfDay)
			BEGIN
				SET @lStatus = 'LH'
			END
			IF (@lOut = '00:00' AND @lIn <> '00:00')
			BEGIN
				SET @lStatus = 'E'
			END		
			
			 --Uncomment this for testing purposes
			--PRINT('In Time: ' + CONVERT(VARCHAR(5),@lIn,108))
			--PRINT('Out Time: ' + CONVERT(VARCHAR(5),@lOut,108))
			--PRINT('Total Time : ' + CONVERT(VARCHAR(5),@lTotalTime,108))
			--PRINT('Overtime : ' + CONVERT(VARCHAR(5),@lOverTime,108))
			--PRINT('Min Hours Half Day : ' + CONVERT(VARCHAR(5),@lMinHoursHalfDay,108))
			--PRINT('Min Hours Full Day : ' + CONVERT(VARCHAR(5),@lMinHoursFullDay,108))
			--PRINT('EarlyBy : ' + CONVERT(VARCHAR(5),@lEarlyBy,108))
			--PRINT('LateBy : ' + CONVERT(VARCHAR(5),@lLateBy,108))
			--PRINT('Status : ' + CONVERT(VARCHAR(5),@lStatus,108))
			--BREAK


			DECLARE @lDay nvarchar(10) = (SELECT DAY(IN_Out_Time) FROM #tTable WHERE Id = @lCount)
			SET @lDay = (SELECT CASE WHEN @lDay BETWEEN 0 AND 9 THEN RIGHT('0' + CAST(@lDay AS VARCHAR(2)), 2) ELSE CAST(@lDay AS NVARCHAR(10)) END)
			DECLARE @lMonthTransAttn_DtTemp datetime = (select cast((cast(YEAR(@lAttn_Dt) as varchar(255)) + '-' + cast(MONTH(@lAttn_Dt) as varchar(255)) + '-' + '01') as datetime))

			DECLARE @lMonthTransAttn_Dt varchar(20) = convert(varchar(20), @lMonthTransAttn_DtTemp, 20)
			PRINT(@lMonthTransAttn_Dt)

			IF NOT EXISTS(SELECT 1 FROM sBiometric.tMonthTrans WHERE EmployeeId = @lSourceId AND SourceTypeId = @lSourceTypeId AND Attn_Dt = @lMonthTransAttn_Dt)
			BEGIN
				--Insert 
				INSERT INTO sBiometric.tMonthTrans(EmpCode,EmployeeId,Attn_Dt,
				D01,D02,D03,D04,D05,D06,D07,D08,D09,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31,
				D01_IN,D02_IN,D03_IN,D04_IN,D05_IN,D06_IN,D07_IN,D08_IN,D09_IN,D10_IN,D11_IN,D12_IN,D13_IN,D14_IN,D15_IN,D16_IN,D17_IN,D18_IN,D19_IN,D20_IN,D21_IN,D22_IN,D23_IN,D24_IN,D25_IN,D26_IN,D27_IN,D28_IN,D29_IN,D30_IN,D31_IN,
				D01_OUT,D02_OUT,D03_OUT,D04_OUT,D05_OUT,D06_OUT,D07_OUT,D08_OUT,D09_OUT,D10_OUT,D11_OUT,D12_OUT,D13_OUT,D14_OUT,D15_OUT,D16_OUT,D17_OUT,D18_OUT,D19_OUT,D20_OUT,D21_OUT,D22_OUT,D23_OUT,D24_OUT,D25_OUT,D26_OUT,D27_OUT,D28_OUT,D29_OUT,D30_OUT,D31_OUT,
				D01_O,D02_O,D03_O,D04_O,D05_O,D06_O,D07_O,D08_O,D09_O,D10_O,D11_O,D12_O,D13_O,D14_O,D15_O,D16_O,D17_O,D18_O,D19_O,D20_O,D21_O,D22_O,D23_O,D24_O,D25_O,D26_O,D27_O,D28_O,D29_O,D30_O,D31_O,
				D01_L,D02_L,D03_L,D04_L,D05_L,D06_L,D07_L,D08_L,D09_L,D10_L,D11_L,D12_L,D13_L,D14_L,D15_L,D16_L,D17_L,D18_L,D19_L,D20_L,D21_L,D22_L,D23_L,D24_L,D25_L,D26_L,D27_L,D28_L,D29_L,D30_L,D31_L,
				D01_T,D02_T,D03_T,D04_T,D05_T,D06_T,D07_T,D08_T,D09_T,D10_T,D11_T,D12_T,D13_T,D14_T,D15_T,D16_T,D17_T,D18_T,D19_T,D20_T,D21_T,D22_T,D23_T,D24_T,D25_T,D26_T,D27_T,D28_T,D29_T,D30_T,D31_T,
				D01_S,D02_S,D03_S,D04_S,D05_S,D06_S,D07_S,D08_S,D09_S,D10_S,D11_S,D12_S,D13_S,D14_S,D15_S,D16_S,D17_S,D18_S,D19_S,D20_S,D21_S,D22_S,D23_S,D24_S,D25_S,D26_S,D27_S,D28_S,D29_S,D30_S,D31_S,
				D01_E,D02_E,D03_E,D04_E,D05_E,D06_E,D07_E,D08_E,D09_E,D10_E,D11_E,D12_E,D13_E,D14_E,D15_E,D16_E,D17_E,D18_E,D19_E,D20_E,D21_E,D22_E,D23_E,D24_E,D25_E,D26_E,D27_E,D28_E,D29_E,D30_E,D31_E,
				[Tot_Attn],[Tot_Abs],[Tot_WeekOFF],[Tot_Holiday],[TotalHour],[Tot_HD],[Tot_Early],[Tot_Late],[Tot_OT],[Tot_OD],[Tot_Leave],[Count_Late],[Count_Early],[Count_OT],Tot_CL,Tot_SL,Tot_EL,Tot_SP,Tot_C,Tot_PL,Tot_ML,SourceTypeId
				) 
				VALUES(@lCode,@lSourceId,@lMonthTransAttn_Dt,
				'XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX',
				'00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00',
				'00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00',
				'00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00',
				'00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00',
				'00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00',
				'XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX','XX',
				'00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00','00:00',
				0,0,0,0,'',0,'','','',0,0,0,0,0,0,0,0,0,0,0,0,@lSourceTypeId)
			END
			 --Update
			SET @lQuery = 'UPDATE sBiometric.tMonthTrans
			SET D'+CAST(@lDay AS VARCHAR)+'_IN = '''+CONVERT(VARCHAR(5),@lIn,108)+''',
			D'+CAST(@lDay AS VARCHAR)+'_OUT = '''+CONVERT(VARCHAR(5),@lOut,108)+''',
			D'+CAST(@lDay AS VARCHAR)+'_E = '''+CONVERT(VARCHAR(5),@lEarlyBy,108)+''',
			D'+CAST(@lDay AS VARCHAR)+'_L = '''+CONVERT(VARCHAR(5),@lLateBy,108)+''',
			D'+CAST(@lDay AS VARCHAR)+'_O = '''+CONVERT(VARCHAR(5),@lOverTime,108)+''',
			D'+CAST(@lDay AS VARCHAR)+'_T = '''+CONVERT(VARCHAR(5),@lTotalTime,108)+''',
			D'+CAST(@lDay AS VARCHAR)+' = '''+CONVERT(VARCHAR(5),@lStatus,108)+''',
			D'+CAST(@lDay AS VARCHAR)+'_S = '''+CAST(@lShift AS VARCHAR)+'''
			WHERE EmployeeId = '+CAST(@lSourceId AS VARCHAR) + ' AND Attn_Dt = ''' + CAST(@lMonthTransAttn_Dt AS VARCHAR) + ''' AND SourceTypeId = '+CAST(@lSourceTypeId AS VARCHAR)

			PRINT ('Query : '+ @lQuery)

			EXEC (@lQuery)			
			
			 
			CREATE TABLE #tSummary (PresentStatus varchar(10), PresentStatusCount int)
			INSERT INTO #tSummary
			SELECT [Status],COUNT(*)
			AS [Count] FROM (
				SELECT D01,D02,D03,D04,D05,D06,D07,D08,D09,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31
				FROM sBiometric.tMonthTrans WHERE EmployeeId = @lSourceId AND CAST(Attn_Dt AS DATE) = @lMonthTransAttn_Dt AND SourceTypeId = @lSourceTypeId ) as Temp
				UNPIVOT
				(
					[Status]   
				   for [Dd] in (D01,D02,D03,D04,D05,D06,D07,D08,D09,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31)
				) as [unpivoted] 	
				GROUP BY
					[Status]
				HAVING 
					COUNT(*) > 0
			
			--SELECT * FROM #tSummary
			--BREAK

			DECLARE @lTotal_HalfDay int = (SELECT ISNULL(SUM(PresentStatusCount),0) FROM #tSummary WHERE PresentStatus IN ('HD'))
			DECLARE @lTotal_Present float = (SELECT ISNULL(SUM(PresentStatusCount),0) FROM #tSummary WHERE PresentStatus IN ('P')) + @lTotal_HalfDay*0.5
			DECLARE @lTotal_Absent float = (SELECT ISNULL(SUM(PresentStatusCount),0) FROM #tSummary WHERE PresentStatus IN ('A','XX','LH')) + @lTotal_HalfDay*0.5
			DECLARE @lTotal_WeekOff int = (SELECT ISNULL(SUM(PresentStatusCount),0) FROM #tSummary WHERE PresentStatus IN ('W','PW'))
			DECLARE @lTotal_Holiday int = (SELECT ISNULL(SUM(PresentStatusCount),0) FROM #tSummary WHERE PresentStatus IN ('H','PH'))

			DECLARE @lTotal_Hours varchar(50) = (SELECT CAST(t.time_sum/3600 AS VARCHAR(10)) + ':'
													 + CAST(t.time_sum%3600/60 AS VARCHAR(10)) 
												FROM
												(
													SELECT SUM(DATEDIFF(S, '00:00', [Status])) AS time_sum FROM (
													SELECT D01_T,D02_T,D03_T,D04_T,D05_T,D06_T,D07_T,D08_T,D09_T,D10_T,D11_T,D12_T,D13_T,D14_T,D15_T,D16_T,D17_T,D18_T,D19_T,D20_T,D21_T,D22_T,D23_T,D24_T,D25_T,D26_T,D27_T,D28_T,D29_T,D30_T,D31_T				
													FROM sBiometric.tMonthTrans WHERE EmployeeId = @lSourceId AND CAST(Attn_Dt AS DATE) = @lMonthTransAttn_Dt AND SourceTypeId = @lSourceTypeId) as Temp
													UNPIVOT
													(
														[Status]   
														for [Dd] in (D01_T,D02_T,D03_T,D04_T,D05_T,D06_T,D07_T,D08_T,D09_T,D10_T,D11_T,D12_T,D13_T,D14_T,D15_T,D16_T,D17_T,D18_T,D19_T,D20_T,D21_T,D22_T,D23_T,D24_T,D25_T,D26_T,D27_T,D28_T,D29_T,D30_T,D31_T)
													) as [unpivoted]				
												) t)

			DECLARE @lTotal_Early varchar(50) = (SELECT CAST(t.time_sum/3600 AS VARCHAR(10)) + ':'
													 + CAST(t.time_sum%3600/60 AS VARCHAR(10)) 
												FROM
												(
													SELECT SUM(DATEDIFF(S, '00:00', [Status])) AS time_sum FROM (
													SELECT D01_E,D02_E,D03_E,D04_E,D05_E,D06_E,D07_E,D08_E,D09_E,D10_E,D11_E,D12_E,D13_E,D14_E,D15_E,D16_E,D17_E,D18_E,D19_E,D20_E,D21_E,D22_E,D23_E,D24_E,D25_E,D26_E,D27_E,D28_E,D29_E,D30_E,D31_E				
													FROM sBiometric.tMonthTrans WHERE EmployeeId = @lSourceId AND CAST(Attn_Dt AS DATE) = @lMonthTransAttn_Dt AND SourceTypeId = @lSourceTypeId) as Temp
													UNPIVOT
													(
														[Status]   
														for [Dd] in (D01_E,D02_E,D03_E,D04_E,D05_E,D06_E,D07_E,D08_E,D09_E,D10_E,D11_E,D12_E,D13_E,D14_E,D15_E,D16_E,D17_E,D18_E,D19_E,D20_E,D21_E,D22_E,D23_E,D24_E,D25_E,D26_E,D27_E,D28_E,D29_E,D30_E,D31_E)
													) as [unpivoted]				
												) t)

			DECLARE @lTotal_Late varchar(50) = (SELECT CAST(t.time_sum/3600 AS VARCHAR(10)) + ':'
													 + CAST(t.time_sum%3600/60 AS VARCHAR(10)) 
												FROM
												(
													SELECT SUM(DATEDIFF(S, '00:00', [Status])) AS time_sum FROM (
													SELECT D01_L,D02_L,D03_L,D04_L,D05_L,D06_L,D07_L,D08_L,D09_L,D10_L,D11_L,D12_L,D13_L,D14_L,D15_L,D16_L,D17_L,D18_L,D19_L,D20_L,D21_L,D22_L,D23_L,D24_L,D25_L,D26_L,D27_L,D28_L,D29_L,D30_L,D31_L				
													FROM sBiometric.tMonthTrans WHERE EmployeeId = @lSourceId AND CAST(Attn_Dt AS DATE) = @lMonthTransAttn_Dt AND SourceTypeId = @lSourceTypeId) as Temp
													UNPIVOT
													(
														[Status]   
														for [Dd] in (D01_L,D02_L,D03_L,D04_L,D05_L,D06_L,D07_L,D08_L,D09_L,D10_L,D11_L,D12_L,D13_L,D14_L,D15_L,D16_L,D17_L,D18_L,D19_L,D20_L,D21_L,D22_L,D23_L,D24_L,D25_L,D26_L,D27_L,D28_L,D29_L,D30_L,D31_L)
													) as [unpivoted]				
												) t)

			DECLARE @lTotal_OverTime varchar(50) = (SELECT CAST(t.time_sum/3600 AS VARCHAR(10)) + ':'
													 + CAST(t.time_sum%3600/60 AS VARCHAR(10)) 
												FROM
												(
													SELECT SUM(DATEDIFF(S, '00:00', [Status])) AS time_sum FROM (
													SELECT D01_O,D02_O,D03_O,D04_O,D05_O,D06_O,D07_O,D08_O,D09_O,D10_O,D11_O,D12_O,D13_O,D14_O,D15_O,D16_O,D17_O,D18_O,D19_O,D20_O,D21_O,D22_O,D23_O,D24_O,D25_O,D26_O,D27_O,D28_O,D29_O,D30_O,D31_O				
													FROM sBiometric.tMonthTrans WHERE EmployeeId = @lSourceId AND CAST(Attn_Dt AS DATE) = @lMonthTransAttn_Dt AND SourceTypeId = @lSourceTypeId) as Temp
													UNPIVOT
													(
														[Status]   
														for [Dd] in (D01_O,D02_O,D03_O,D04_O,D05_O,D06_O,D07_O,D08_O,D09_O,D10_O,D11_O,D12_O,D13_O,D14_O,D15_O,D16_O,D17_O,D18_O,D19_O,D20_O,D21_O,D22_O,D23_O,D24_O,D25_O,D26_O,D27_O,D28_O,D29_O,D30_O,D31_O)
													) as [unpivoted]				
												) t)

			UPDATE sBiometric.tMonthTrans SET [Tot_Attn] = @lTotal_Present,[Tot_Abs] = @lTotal_Absent,[Tot_WeekOFF] = @lTotal_WeekOff,
			[Tot_Holiday] = @lTotal_Holiday,[TotalHour] = @lTotal_Hours,[Tot_HD] = @lTotal_HalfDay,[Tot_Early] = @lTotal_Early,[Tot_Late] = @lTotal_Late,
			[Tot_OT] = @lTotal_OverTime
			WHERE EmployeeId = @lSourceId AND CAST(Attn_Dt AS DATE) = @lMonthTransAttn_Dt AND SourceTypeId = @lSourceTypeId			
			
			UPDATE sBiometric.tCloudTransactionData SET IsProcessed = 1 WHERE TransactionId = @lTransactionId

			DROP TABLE #tSummary

		END
			
		SET @lPrevAttn_Dt = @lAttn_Dt
		SET @lPrevSourceId = @lSourceId
		SET @lPrevSourceTypeId = @lSourceTypeId
		

		SET @lCount = @lCount + 1
	END

	--Drop temp table for server issue(tempdb)
	Drop Table #tTable
END
