· 8 years ago · Jul 10, 2018, 09:34 PM
1USE [TPAYROLL] // use the TPAYROLL database
2TRUNCATE TABLE [AttandanceDetails] // truncate AttandanceDetails table
3
4
5USE [PARASON] // use the PARASON database
6GO // continue
7/****** Object: StoredProcedure [dbo].[procDaily_Rpt_Parason] Script Date: 06/08/2010 16:10:46 ******/
8SET ANSI_NULLS ON // change ANSI_NULLS setting to on
9GO // continue
10SET QUOTED_IDENTIFIER ON // set QUOTED_IDENTIFIER setting to on
11GO // continue
12
13
14
15
16
17--- Updated on 21st jan 2009
18/*
19
20
21exec [procDaily_Rpt_Parason] @inDepartment=N'--All--',@inUserCode=1,@inLoginType=N'A',@inEmpID=N'0',
22@dtFrom_Date='2009-2-1 00:00:00:000',@dtTo_Date='2009-2-23 00:00:00:000',@Dflt_Shft_In_Time='2009-09-29 08:30:00:000',@Dflt_Shft_Out_Time='2009-09-29 17:00:00:000',@DfltGraceTime=15,@Dflt_Weekly_Off=N'70',@Shft_Tolerance_InTime=180,@Shft_Tolerance_OutTime=600
23
24*/
25/************
26ALTER PROCEDURE [dbo].[procDaily_Rpt_Parason] // ALTER the PROCEDURE table
27(
28 @inLoginType NVARCHAR(2)='', // change datatype to NVARCHAR with size of 2 chars and set value to nothing.
29 @inUserCode int=0, // change datatype to int with value of 0
30 @inDepartment NVARCHAR(100)='', // change datatype to NVARCHAR with size of 1 chars and set value to nothing
31 @inEmpID NVARCHAR(100)='', // change datatype to NVARCHAR with size of 1 chars and set value to nothing
32 @dtFrom_Date DATETIME, // set value to the current date and time
33 @dtTo_Date DATETIME, // set value to the current date and time
34 @Dflt_Shft_In_Time DATETIME, // set value to the current date and time
35 @Dflt_Shft_Out_Time DATETIME, // set value to the current date and time
36 @DfltGraceTime INT, // change grace time datatype to integer
37 @Dflt_Weekly_Off VARCHAR(2), // change datatype to VARCHAR with char size of 2
38 @Shft_Tolerance_InTime SMALLINT, // change datatype to SMALLINT
39 @Shft_Tolerance_OutTime SMALLINT // change datatype to SMALLINT
40)
41AS
42BEGIN
43*************/
44 DECLARE @inLoginType NVARCHAR(2)
45 DECLARE @inUserCode int
46 DECLARE @inDepartment NVARCHAR(100)
47 DECLARE @inEmpID NVARCHAR(100)
48 DECLARE @dtFrom_Date DATETIME
49 DECLARE @dtTo_Date DATETIME
50 DECLARE @Dflt_Shft_In_Time DATETIME
51 DECLARE @Dflt_Shft_Out_Time DATETIME
52 DECLARE @DfltGraceTime INT
53 DECLARE @Dflt_Weekly_Off VARCHAR(2)
54 DECLARE @Shft_Tolerance_InTime SMALLINT
55 DECLARE @Shft_Tolerance_OutTime SMALLINT
56
57 DECLARE @CompanyID NVARCHAR(8)
58 DECLARE @Name NVARCHAR(100)
59 DECLARE @Department NVARCHAR(30)
60 DECLARE @Remarks NVARCHAR(15)
61 DECLARE @Func_IN NVARCHAR(15)
62 DECLARE @Func_Out NVARCHAR(15)
63 --declare @ShiftName NVARCHAR(15)
64 declare @ShiftCode NVARCHAR(15)
65 DECLARE @Date DATETIME
66 DECLARE @Shift_In_Time DATETIME
67 DECLARE @In_Time DATETIME
68 DECLARE @Shift_Out_Time DATETIME
69 DECLARE @Out_Time DATETIME
70 DECLARE @Grace_Time INTEGER
71 DECLARE @Weekly_Off NVARCHAR(10)
72 DECLARE @Tmp_In_Time DATETIME
73 DECLARE @Tmp_Out_Time DATETIME
74 DECLARE @UserId VARCHAR(10)
75 DECLARE @IsWeeklyOff BIT
76 DECLARE @IsHoliday BIT
77 DECLARE @sqlqry NVARCHAR(4000) -- ADDED BY RAju
78 DECLARE @cnt INT
79 DECLARE @max INT
80 declare @permiss NVARCHAR(50)
81 declare @Date_new NVARCHAR(50)
82
83 declare @Tot_hrs integer
84 declare @Shift_Hrs integer
85 declare @Extra_Hrs integer
86 declare @Branch NVARCHAR(50)
87 declare @Event_Year NVARCHAR(4)
88 declare @Event_Month NVARCHAR(02)
89 declare @Shift_code NVARCHAR(10)
90declare @Tot_hrs1 NVARCHAR(15)
91 declare @Shift_Hrs1 NVARCHAR(15)
92 declare @Extra_Hrs1 NVARCHAR(15)
93
94declare @time_Tot1 FLOAT
95 declare @time_hh1 float
96declare @time_mm1 float
97
98declare @time_Tot FLOAT
99declare @Tot_Hrs_work float
100
101 declare @time_hh float
102declare @time_mm float
103declare @Date1 NVARCHAR(10)
104declare @In_Time1 NVARCHAR(8)
105declare @Out_Time1 NVARCHAR(8)
106
107SET @inLoginType = N'A' // set value to A
108SET @inUserCode = 1 // set value to 1
109SET @inDepartment= N'--All--' // set value to --All--
110SET @inEmpID = N'0' // set value to 0
111--SELECT @date2 = DATEADD(DAY,-7,GETDATE ()) // set value to currenty date minus 7 days
112--SELECT @date2 AS '@date'
113SET @dtFrom_Date = DATEADD(DAY,-2,(SELECT CONVERT(VARCHAR(10),GETDATE (),111))) // sets value to current date -2 days
114--'2010-6-01 00:00:00:000' // commented out
115--DATEADD(DAY,-7,GETDATE ()) //commented out
116/***'2010-5-25 00:00:00:000'***/ // commented out
117--DATEADD(DAY,-7,SYSDATETIME ()) //commented out
118/******** '2010-5-25 00:00:00:000' *********/ //commented out
119--SELECT @date2 = SYSDATETIME () //commented out
120--SELECT @date2 AS '@date' //commented out
121SET @dtTo_Date = (SELECT CONVERT(VARCHAR(10),GETDATE (),111)) // sets dtTo_Date to current date
122--'2010-6-08 00:00:00:000' //commented out
123SET @Dflt_Shft_In_Time = '2010-5-25 08:30:00:000' // set Dflt_Shft_In_Time to 2010-5-25 08:30:00:000
124SET @Dflt_Shft_Out_Time = '2010-5-25 17:00:00:000' // set Dflt_Shft_In_Time to 2010-5-25 17:00:00:000
125SET @DfltGraceTime = 15 // set DfltGraceTime to 15
126SET @Dflt_Weekly_Off = N'70' // set Dflt_Weekly_Off to 70
127SET @Shft_Tolerance_InTime = 180 // set Shft_Tolerance_InTime to 180
128SET @Shft_Tolerance_OutTime = 600 // set Shft_Tolerance_OutTime to 600
129
130
131 CREATE table #History_tbl // create a new table called History_tbl
132 (
133 EventDateTime DATETIME, // make the table have a row called EventDateTime with datatype DATETIME
134 FuncCode NVARCHAR(2), // make the table have a row called FuncCode with a datatype of NVARCHAR and a length of 2 chars
135 UserID NVARCHAR(15) // make the table have a row called FuncCode with a datatype of NVARCHAR and a length of 15 chars
136 )
137 INSERT #History_tbl select EventDateTime,FuncCode,UserID from History where EventDate BETWEEN @dtFrom_Date AND DATEADD(dd,1,@dtTo_Date)-- AND USERNAME <> '' and EVENTNO IN (1,2,3,4,5,6,7)
138
139 // insert into History_tbl table the returned values of the query:
140 // (select EventDateTime,FuncCode,UserID from History where EventDate BETWEEN @dtFrom_Date AND DATEADD(dd,1,@dtTo_Date)-- AND USERNAME <> ' and //EVENTNO IN (1,2,3,4,5,6,7) )
141
142 CREATE INDEX ind_Temphist ON #History_tbl(UserID,EventDateTime) // create a new index on History_tbl
143 SET @Date=@dtFrom_Date // set the Date variable to the value of dtFrom_Date
144
145 CREATE TABLE #Daily_Rpt_temp_tbl // create a new table called Daily_Rpt_temp_tbl
146 (
147
148 CompanyID NVARCHAR(8), //
149 EventDate NVARCHAR(10),
150 [Status] NVARCHAR(15),
151 In_Time NVARCHAR(8),
152 Out_Time NVARCHAR(8),
153 Tot_hrs NVARCHAR(15),
154 Shift_Hrs NVARCHAR(15),
155 Extra_Hrs NVARCHAR(15),
156 Branch NVARCHAR(50),
157 Event_Year NVARCHAR(4),
158 Event_Month NVARCHAR(02),
159 Shift_code NVARCHAR(10)
160
161 )
162
163 CREATE table #Member_tbl
164 (
165 slno INT Identity(1,1),
166 CompanyID NVARCHAR(8)
167 )
168CREATE INDEX ind_TempMember_tbl ON #Member_tbl(slno)
169 SET @cnt=1
170 WHILE @Date <= @dtTo_Date
171 BEGIN
172 IF EXISTS (SELECT * FROM dbo.Holiday_Mstr WHERE Holiday_Date=@Date)
173 SET @IsHoliday=1
174 ELSE
175 SET @IsHoliday=0
176
177 if @inLoginType <> 'M'
178 BEGIN
179 if @inDepartment = '--ALL--'
180 SET @sqlqry =' INSERT #Member_tbl SELECT distinct(CompanyID) FROM dbo.Member where department in ( SELECT DEPARTMENT FROM dbo.GetAllDepartments('+ cast(@inUserCode as nvarchar) +') )'
181 else if @inDepartment <> ''
182 SET @sqlqry =' INSERT #Member_tbl SELECT distinct(CompanyID) FROM dbo.Member where department in ( '''+ @inDepartment +''' )'
183 END
184 ELSE
185 BEGIN
186 SET @sqlqry =' INSERT #Member_tbl SELECT distinct(CompanyID) FROM dbo.Member where CompanyID in ( '''+ @inEmpID +''' )'
187 END
188 EXEC sp_executesql @sqlqry
189
190 SET @max=(SELECT MAX(slno) FROM #Member_tbl)
191
192 WHILE @cnt <= @max
193 BEGIN
194 SELECT @CompanyID=CompanyID FROM #Member_tbl WHERE slno = @cnt
195
196 SET @In_Time=NULL
197 SET @Out_Time=NULL
198 SET @Shift_In_Time=NULL
199 SET @Shift_Out_Time=NULL
200 SET @Grace_Time=NULL
201 SET @Func_IN=NULL
202 SET @Func_Out=NULL
203 SET @Remarks=null
204 set @Weekly_Off=null
205 set @ShiftCode=''
206-- set @ShiftName=''
207 set @IsWeeklyOff=null
208 set @Tot_hrs =0
209 set @Shift_Hrs ='00'
210 set @Extra_Hrs ='00'
211 set @Branch =''
212 set @Event_Year =''
213 set @Event_Month ='00'
214 set @Shift_code ='00'
215 set @Tot_hrs1 ='00:00'
216 set @Shift_Hrs1 ='00:00'
217 set @Extra_Hrs1 ='00:00'
218 set @In_Time1=''
219 set @Out_Time1 =''
220set @time_Tot =0
221set @Tot_Hrs_work =0
222 set @time_hh =0
223set @time_mm =0
224set @time_Tot1 =0
225 set @time_hh1 =0
226set @time_mm1 =0
227
228
229 SELECT @Name=[Name],@Department=Department,@userid=userid,@Branch=Department
230 FROM dbo.Member
231 WHERE CompanyID=@CompanyID
232
233
234
235 SELECT @Shift_In_Time=a.Shift_InTime,@Shift_Out_Time=a.Shift_OutTime,@Grace_Time=Shift_Grace,@ShiftCode=a.Shift_Code
236 FROM dbo.Shift_Mstr a,dbo.Emp_ShiftDtls b
237 WHERE b.EMP_ID=@CompanyID
238 AND a.Shift_Code=b.Shift_Code
239 AND @Date =Shift_Date
240
241 set @Shift_code=@ShiftCode
242
243 IF @Shift_In_Time IS NULL AND @Shift_Out_Time IS NULL
244 BEGIN
245 SET @Shift_In_Time=@Dflt_Shft_In_Time
246 SET @Shift_Out_Time=@Dflt_Shft_Out_Time
247 SET @Grace_Time=@DfltGraceTime
248
249 END
250
251---------------------------Shift Hrs----------
252 if DATEDIFF(SS,@Shift_In_Time,@Shift_Out_Time) < 0
253 begin
254 set @Shift_hrs=datediff(ss,@Shift_In_Time,dateadd(dd,1,@Shift_Out_Time))
255 end
256 else
257 Begin
258 set @Shift_hrs=datediff(ss,@Shift_In_Time,@Shift_Out_Time)
259 end
260--------------------------------
261 IF DATEDIFF(SS,@Shift_In_Time,@Shift_Out_Time) < 0
262 SET @Tmp_Out_Time=DATEADD(DD,1,@Date)
263 ELSE
264 SET @Tmp_Out_Time=@Date
265
266
267 SET @Tmp_In_Time=DATEADD(Mi,-(@Shft_Tolerance_InTime),DATEADD(Mi,DATEPART(Mi,@Shift_In_Time),DATEADD(HH,DATEPART(HH,@Shift_In_Time),@Date)))
268 SET @Tmp_Out_Time=DATEADD(Mi,@Shft_Tolerance_OutTime,DATEADD(Mi,DATEPART(Mi,@Shift_Out_Time),DATEADD(HH,DATEPART(HH,@Shift_Out_Time),@Tmp_Out_Time)))
269
270 SELECT TOP 1 @In_Time=EventDateTime, @Func_IN = FuncCode
271 FROM #History_Tbl
272 WHERE UserID=@UserId
273 AND EventDateTime BETWEEN @Tmp_In_Time AND @Tmp_Out_Time
274 AND FuncCode IN ('0','30') ORDER BY EventDateTime ASC
275
276 SELECT TOP 1 @Out_Time=EventDateTime, @Func_Out = FuncCode
277 FROM #History_Tbl
278 WHERE UserID=@UserId
279 AND EventDateTime BETWEEN @Tmp_In_Time AND @Tmp_Out_Time
280 AND FuncCode IN ('10','20') ORDER BY EventDateTime DESC
281 IF @In_Time IS NULL OR @Out_Time IS NULL
282 SET @Remarks='Odd Punch'
283 SELECT @Weekly_Off=Shift_Code FROM dbo.Emp_ShiftDtls WHERE EMP_ID=@CompanyID AND Shift_Date=@Date
284 IF @Weekly_Off ='W'
285 begin
286 SET @Remarks='Weekly Off'
287 set @ShiftCode='W'
288 end
289 IF @Weekly_Off IS NULL
290 begin
291 SET @Weekly_Off=@Dflt_Weekly_Off
292
293 SET @IsWeeklyOff=dbo.GetWeeklyOffDays(@Date,@Weekly_Off)
294 end
295 IF @IsWeeklyOff=1 and @ShiftCode= ''
296 begin
297 SET @Remarks='Weekly Off'
298 end
299 else IF @IsHoliday=1
300 Begin
301 SET @Remarks='Holiday'
302 set @ShiftCode='H'
303 End
304
305 ELSE IF EXISTS (SELECT * FROM dbo.Emp_Leave_Dtls WHERE EMP_ID=@CompanyID AND @Date BETWEEN Start_Date AND End_Date AND IsPermission=0)
306 begin
307 SET @Remarks='Leave'
308 set @ShiftCode='L'
309 end
310 ELSE IF EXISTS (SELECT * FROM dbo.Emp_Leave_Dtls WHERE EMP_ID=@CompanyID AND @Date BETWEEN Start_Date AND End_Date AND IsPermission=1)
311
312 begin
313 set @permiss=(Select PermDesc from dbo.Emp_Leave_Dtls WHERE EMP_ID=@CompanyID AND @Date BETWEEN Start_Date AND End_Date AND IsPermission=1)
314 SET @Remarks=@permiss
315 end
316
317 IF @In_Time IS NULL AND @Out_Time IS NULL AND (@Remarks IS NULL or @Remarks ='Odd Punch')
318 SET @Remarks='Absent'
319 ELSE IF @In_Time IS NOT NULL AND @Out_Time IS NOT NULL AND @Remarks IS NULL
320 SET @Remarks='Present'
321
322
323 if @In_Time is not null AND @Out_Time is not null and @In_Time < @Out_Time
324 begin
325 set @Tot_hrs=DATEDIFF(SS,@In_Time,@Out_Time)
326 end
327 else
328 begin
329 set @Tot_hrs=''
330 end
331 if @Tot_hrs is not null and @Tot_hrs > @Shift_Hrs
332 begin
333 set @Tot_hrs=DATEDIFF(SS,@In_Time,dateadd(hh,12,@Out_Time))
334 set @Extra_Hrs=(@Tot_hrs - @Shift_Hrs)
335 set @Extra_Hrs1=dbo.GetTimeFromSecs1(@Extra_Hrs)
336
337 end
338 else
339 begin
340 set @Extra_Hrs=0
341 set @Extra_Hrs1='00:00'
342 end
343
344 set @Tot_hrs1=dbo.GetTimeFromSecs1(@Tot_hrs)
345 if @Remarks='Absent'
346 begin
347 set @Tot_hrs1='00:00'
348 set @Extra_Hrs1='00:00'
349
350 end
351
352 set @Shift_hrs1=dbo.GetTimeFromSecs1(@Shift_hrs)
353
354 SET @Shift_Out_Time=DATEADD(Mi,-(@Shft_Tolerance_OutTime),@Tmp_Out_Time)
355
356
357 if @Remarks='Leave' or @Remarks='Weekly Off'
358 begin
359 set @Shift_Hrs1='00:00'
360 end
361
362 set @Event_Month=( select datepart(MM,@Date))
363 set @Event_Year=( select datepart(YY,@Date))
364--SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
365 set @Date1 =(select convert (varchar(10),@Date,103)as [dd/MM/yyyy] )
366 set @In_Time1 =(select convert (varchar(8),@In_Time,114))
367 set @Out_Time1 =(select convert (varchar(8),@Out_Time,114))
368 set @time_hh=(select datepart(HH,@Tot_hrs1))
369 set @time_mm=(select datepart(mi,@Tot_hrs1))
370
371 set @time_Tot =round(((@time_mm)/60+@time_hh),2)
372
373 set @time_hh1=(select datepart(HH,@Extra_Hrs1))
374 set @time_mm1=(select datepart(mi,@Extra_Hrs1))
375
376 set @time_Tot1 =round(((@time_mm1)/60+@time_hh1),2)
377
378 set @Tot_Hrs_work=round(@time_Tot-@time_Tot1,1)
379
380
381 INSERT INTO #Daily_Rpt_temp_tbl VALUES( @CompanyID,@Date1,@Remarks,@In_Time1,@Out_Time1,@time_Tot,@Tot_Hrs_work,@time_Tot1,@Branch,@Event_Year,@Event_Month,@Shift_code)
382
383 IF @Out_Time1 IS NULL
384 SET @Out_Time1 = 0
385
386 IF @In_Time1 IS NULL
387 SET @In_Time1 = 0
388
389 if @Remarks<>'Absent' and @Remarks<>'Leave'
390 begin
391
392 IF @Branch = 'PMI' OR @Branch = 'PMI-III'OR @Branch = 'SMS'OR @Branch = 'SSU'OR @Branch = 'PAPL'OR @Branch = 'PAC'
393 BEGIN
394
395 USE [PARASON PAYROLL]
396
397 INSERT INTO [AttandanceDetails]([Emp AttandAance ID],[Attand Date],[In Time],[Out Time],[Branch],
398 [Status],[Shift Code])
399 VALUES (@CompanyID,@Date1,@In_Time1,@Out_Time1,@Branch,@Remarks,@Shift_code);
400 END
401 end
402
403
404
405 --INSERT INTO #Daily_Rpt_temp_tbl VALUES( @CompanyID,@Date1,@Remarks,@In_Time1,@Out_Time1,@Tot_hrs1,@Shift_Hrs1,@Extra_Hrs1,@Branch,@Event_Year,@Event_Month,@Shift_code)
406
407 --INSERT INTO #Daily_Rpt_temp_tbl VALUES(@Date,@CompanyID,@Name,@Department,@Shift_In_Time,@In_Time,@Shift_Out_Time,@Out_Time,@Grace_Time,@Remarks,@Func_IN,@Func_Out,@Tot_hrs1,@Shift_Hrs1,@Extra_Hrs1,@Branch,@Event_Year,@Event_Month,@Shift_code)
408
409 USE [PARASON]
410 SET @Cnt =@Cnt+1
411 END
412
413 SET @Date=DATEADD(D,1,@Date)
414 END
415
416 SELECT * FROM #Daily_Rpt_temp_tbl ORDER BY EventDate,CompanyID
417 DROP TABLE #Daily_Rpt_temp_tbl
418 DROP TABLE #Member_tbl