--Exec Sp_HRMDailyAttendance_UI_Pending 1,'2019-10-26',824
CREATE PROCEDURE Sp_HRMDailyAttendance_UI_Pending
@LocationId numeric(2),
@AttDate Date,
@EmpId Numeric(10,0)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@CardNo Numeric(10,0),
@ShiftDate Date,
@ShiftStartTime DateTime,
@ShiftEndTime DateTime,
@ActualStartTime DateTime,
@MaxEmpId Numeric(10,0),
@MaxCardNo Numeric(10,0),
@MaxShiftStartTime DateTime,
@NextDay Char(1),
@PreviousDay Char(1),
@FHFlag Varchar(20),
@SHFlag Varchar(20),
@FullFlag Varchar(20),
@FHFlagOD Varchar(20),
@SHFlagOD Varchar(20),
@FullFlagOD Varchar(20),
@Reason Varchar(800),
@LeaveId Int,
@ODAndPermission Varchar(20)
insert into hr.dbo.HRM_DailyAttendance_Amend select EmpId,InTime,OutTime,substring(ReasonForLeave,1,100),AttDate,ShiftId,LeaveId,CatId,
DeptId,Attendance,SerialNo,OfficialOut,Flag,HalfDayLeave,OTRequiredForHoliday,MisPunch,Permisable,Notes,LeaveAppId,
ConsiderForSalary,LeaveId2,EntryEmpId,EntryComputer,ImportMachine,BillNo,CustId,LocationId,othrs,remarks,prodn,outtime1,
entrydate,ent_down_date,otentrydate,otentredby,otaprovedby,otaproveddate,otprocess,Latedet,LatePunch,earlypunch,F_S_half,
forceabsent,ShiftStartTime,ShiftEndTime,LunchStartTime,LunchEndTime,CalStartTime,CalEndTime,FHFlag,SHFlag,FullFlag,ChkShiftStartTime,GraceTime,
HalfDayOnduty,Onduty
from hr.dbo.HRM_DailyAttendance where AttDate = @AttDate and empid = @EmpId
Delete hr.dbo.HRM_DailyAttendance where AttDate = @AttDate and empid = @EmpId
Declare @Count As Integer
Select @Count = COUNT(*) From hr.dbo.HRM_DailyAttendance where AttDate = @AttDate and LocationId = @LocationId and empid = @empid;
If @Count > 0
begin
RAISERROR('This date Already Downloaded. Please Check...', 16, 10) with nowait;
RETURN;
End
Else
Declare cur_HrmDailyAttendanceTest Cursor for
select Distinct b.EmpId,b.CardNo,d.NextDay,Isnull(d.previousday,'N')previousday
from hr.dbo.emp_personal b with (nolock)
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
where b.locationid = @LocationId and b.resign = 'N'
and @AttDate between c.StartDate and c.EndDate
and b.empid = @EmpId
order by cardno
Open cur_HrmDailyAttendanceTest;
fetch next from cur_HrmDailyAttendanceTest into @EmpId, @CardNo,@NextDay,@PreviousDay;
While @@FETCH_STATUS = 0
Begin
BEGIN TRANSACTION INS;
If @NextDay = 'N' and @PreviousDay = 'N'
Insert into hr.dbo.HRM_DailyAttendance (EmpId,InTime,AttDate,Shiftid,CatId,DeptId,
EntryEmpId,EntryComputer,ImportMachine,LocationId,ent_down_date,ShiftStartTime,ShiftEndTime,
LunchStartTime,LunchEndTime,ChkShiftStartTime,GraceTime)
select Top 1 b.EmpId,LogDate as ActualStartTime, convert(varchar(10),LogDate,20) ShiftDate,
d.ShiftId,Isnull(ss.CatId,ed.catid)Catid,ISNULL(ss.deptid,ed.deptid)DeptId,
10,'Server','Y',@LocationId,GETDATE(),
convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.Stime,8) ShiftStartTime,
case d.NextDay when 'Y' then
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8)
else
convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8) end as shiftetime,
case when d.NextDay = 'Y' and CAST(d.LunchStart as time) < CAST('08:00' as time) then
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.LunchStart,8)
Else
convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.LunchStart,8) End as LunchStartTime,
case when d.NextDay = 'Y' and CAST(d.LunchEnd as time) < CAST('08:00' as time) then
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.LunchEnd,8)
Else
convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.LunchEnd,8) End As LunchEndTime,
ISNULL(ChkShiftStTime,30),
Isnull(hc.GraceTimeMin,5)
from sandfitsetime.dbo.DeviceLogs a with (nolock)
Inner join hr.dbo.emp_personal b with (nolock) on a.userId = b.cardno and b.locationid = @LocationId
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
where
Convert(date,LogDate) between c.StartDate and c.EndDate
and Convert(date,LogDate) = @AttDate
and LogDate between
DateAdd (MINUTE,-ISNULL(ChkShiftStTime,30),convert(varchar(10),LogDate,20)+' '+convert(varchar(8),d.Stime,8) )
and
convert(varchar(10),LogDate,20)+' '+convert(varchar(8),d.etime,8)
and UserId = @CardNo
order by LogDate Asc;
Else if @NextDay = 'N' and @PreviousDay = 'Y'
Insert into hr.dbo.HRM_DailyAttendance (EmpId,InTime,AttDate,Shiftid,CatId,DeptId,
EntryEmpId,EntryComputer,ImportMachine,LocationId,ent_down_date,ShiftStartTime,ShiftEndTime,
LunchStartTime,LunchEndTime,ChkShiftStartTime,GraceTime)
select Top 1 b.EmpId, LogDate as ActualStartTime,convert(varchar(10),@AttDate,20) ShiftDate,
d.ShiftId,Isnull(ss.CatId,ed.catid)Catid,ISNULL(ss.deptid,ed.deptid)DeptId,
10,'Server','Y',@LocationId,GETDATE(),
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.Stime,8) ShiftStartTime,
case d.NextDay when 'Y' then
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8)
else
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8) end as shiftetime,
case when d.NextDay = 'Y' and CAST(d.LunchStart as time) < CAST('08:00' as time) then
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.LunchStart,8)
Else
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.LunchStart,8) End as LunchStartTime,
case when d.NextDay = 'Y' and CAST(d.LunchEnd as time) < CAST('08:00' as time) then
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.LunchEnd,8)
Else
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.LunchEnd,8) End As LunchEndTime,
ISNULL(ChkShiftStTime,30),
Isnull(hc.GraceTimeMin,5)
from sandfitsetime.dbo.DeviceLogs a with (nolock)
Inner join hr.dbo.emp_personal b with (nolock) on a.userId = b.cardno and b.locationid = @LocationId
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
where @AttDate between c.StartDate and c.EndDate
and LogDate between
DateAdd (MINUTE,-ISNULL(ChkShiftStTime,30),convert(varchar(10),case When d.previousday = 'Y' Then DateAdd(dd,1,@AttDate) Else @AttDate End,20)+' '+convert(varchar(8),d.Stime,8) )
and
convert(varchar(10),case When d.previousday = 'Y' Then DateAdd(dd,1,@AttDate) Else @AttDate End,20)+' '+convert(varchar(8),d.etime,8)
and UserId = @CardNo
order by LogDate Asc;
Else
if @NextDay = 'Y'
Insert into hr.dbo.HRM_DailyAttendance (EmpId,InTime,AttDate,Shiftid,CatId,DeptId,
EntryEmpId,EntryComputer,ImportMachine,LocationId,ent_down_date,ShiftStartTime,ShiftEndTime,
LunchStartTime,LunchEndTime,ChkShiftStartTime,GraceTime)
select Top 1 b.EmpId, LogDate as ActualStartTime,convert(varchar(10),LogDate,20) ShiftDate,d.ShiftId,
Isnull(ss.CatId,ed.catid)Catid,ISNULL(ss.deptid,ed.deptid)DeptId,10,'Server','Y',@LocationId,GETDATE(),
convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.Stime,8) ShiftStartTime,
case d.NextDay when 'Y' then convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8)
else convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8) end as shiftetime,
case when d.NextDay = 'Y' and CAST(d.LunchStart as time) < CAST('08:00' as time) then
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.LunchStart,8)
Else
convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.LunchStart,8) End as LunchStartTime,
case when d.NextDay = 'Y' and CAST(d.LunchEnd as time) < CAST('08:00' as time) then
convert(varchar(10),dateadd(d,1,@AttDate),20)+' '+convert(varchar(8),d.LunchEnd,8)
Else
convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.LunchEnd,8) End As LunchEndTime,
ISNULL(ChkShiftStTime,30),
Isnull(hc.GraceTimeMin,5)
from sandfitsetime.dbo.DeviceLogs a with (nolock)
Inner join hr.dbo.emp_personal b with (nolock) on a.userId = b.cardno and b.locationid = @LocationId
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
where
Convert(date,LogDate) between c.StartDate and c.EndDate
and Convert(date,LogDate) = @AttDate
and LogDate between
DateAdd (MINUTE,-ISNULL(ChkShiftStTime,30),convert(varchar(10),LogDate,20)+' '+convert(varchar(8),d.Stime,8) )
and
convert(varchar(10),dateadd(d,1,LogDate),20)+' '+convert(varchar(8),d.etime,8)
and UserId = @CardNo
order by LogDate Asc;
COMMIT TRANSACTION INS;
BEGIN TRANSACTION UPD;
Declare @ActualEndTime DateTime
If @NextDay = 'N' and @PreviousDay = 'N'
Select Top 1 @ActualEndTime = LogDate From
(
select * from (
select Top 1 LogDate from sandfitsetime.dbo.DeviceLogs a with (nolock)
Inner join hr.dbo.emp_personal b with (nolock) on a.userId = b.cardno and b.locationid = @LocationId
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
Inner Join Hr_TwelveHrShiftCtrlHrs TH on th.LocationId = b.locationid
where
Convert(date,LogDate) between c.StartDate and c.EndDate
and UserId = @CardNo
and LogDate
between
DateAdd (MINUTE,-ISNULL(ChkShiftStTime,30),convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.Stime,8) )
and
case when DATEDIFF(MI,d.Stime,d.ETime)/60 < 10 Then
DATEADD(MINUTE,DATEDIFF(MINUTE,d.Stime,d.ETime)+30,convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8))
Else
DATEADD(MINUTE,th.ShiftEndTimeMaxMinutes,convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8))
End
And (Logdate >= convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8))
order by LogDate )T
Union All
select Top 1 LogDate from sandfitsetime.dbo.DeviceLogs a with (nolock)
Inner join hr.dbo.emp_personal b with (nolock) on a.userId = b.cardno and b.locationid = @LocationId
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
Inner Join Hr_TwelveHrShiftCtrlHrs TH on th.LocationId = b.locationid
where
Convert(date,LogDate) between c.StartDate and c.EndDate
and UserId = @CardNo
and LogDate
between
DateAdd (MINUTE,-ISNULL(ChkShiftStTime,30),convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.Stime,8) )
and
--DATEADD(MINUTE,DATEDIFF(MINUTE,d.Stime,d.ETime)+30,convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8))
case when DATEDIFF(MI,d.Stime,d.ETime)/60 < 10 Then
DATEADD(MINUTE,DATEDIFF(MINUTE,d.Stime,d.ETime)+30,convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8))
Else
DATEADD(MINUTE,th.ShiftEndTimeMaxMinutes,convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8))
End
And (Logdate <= convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8))
order by LogDate Desc)M
If @NextDay = 'N' and @PreviousDay = 'Y'
Select Top 1 @ActualEndTime = LogDate From
(
select * from (
select Top 1 LogDate
from sandfitsetime.dbo.DeviceLogs a with (nolock)
Inner join hr.dbo.emp_personal b with (nolock) on a.userId = b.cardno and b.locationid = @LocationId
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
Inner Join Hr_TwelveHrShiftCtrlHrs TH on th.LocationId = b.locationid
where @AttDate between c.StartDate and c.EndDate and
convert(date,LogDate) = (case When d.previousday = 'Y' Then DateAdd(dd,1,@AttDate) Else @AttDate End)
and LogDate between
DateAdd (MINUTE,-ISNULL(ChkShiftStTime,30),convert(varchar(10),LogDate,20)+' '+convert(varchar(8),d.Stime,8) )
and
case when DATEDIFF(MI,d.Stime,d.ETime)/60 < 10 Then
DATEADD(MINUTE,DATEDIFF(MINUTE,d.Stime,d.ETime)+30,convert(varchar(10),LogDate,20)+' '+convert(varchar(8),d.etime,8))
Else
DATEADD(MINUTE,th.ShiftEndTimeMaxMinutes,convert(varchar(10),LogDate,20)+' '+convert(varchar(8),d.etime,8))
End
And (Logdate >= convert(varchar(10),(case When d.previousday = 'Y' Then DateAdd(dd,1,@AttDate) Else @AttDate End) ,20)+' '+convert(varchar(8),d.etime,8))
and UserId = @CardNo
order by LogDate )T
Union All
select Top 1 LogDate
from sandfitsetime.dbo.DeviceLogs a with (nolock)
Inner join hr.dbo.emp_personal b with (nolock) on a.userId = b.cardno and b.locationid = @LocationId
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
Inner Join Hr_TwelveHrShiftCtrlHrs TH on th.LocationId = b.locationid
where @AttDate between c.StartDate and c.EndDate and
convert(date,LogDate) = (case When d.previousday = 'Y' Then DateAdd(dd,1,@AttDate) Else @AttDate End)
and LogDate between
DateAdd (MINUTE,-ISNULL(ChkShiftStTime,30),convert(varchar(10),LogDate,20)+' '+convert(varchar(8),d.Stime,8) )
and
case when DATEDIFF(MI,d.Stime,d.ETime)/60 < 10 Then
DATEADD(MINUTE,DATEDIFF(MINUTE,d.Stime,d.ETime)+30,convert(varchar(10),LogDate,20)+' '+convert(varchar(8),d.etime,8))
Else
DATEADD(MINUTE,th.ShiftEndTimeMaxMinutes,convert(varchar(10),LogDate,20)+' '+convert(varchar(8),d.etime,8))
End
And (Logdate <= convert(varchar(10),(case When d.previousday = 'Y' Then DateAdd(dd,1,@AttDate) Else @AttDate End) ,20)+' '+convert(varchar(8),d.etime,8))
and UserId = @CardNo
order by LogDate Desc)M
Else If @NextDay = 'Y' and @PreviousDay = 'N'
Select Top 1 @ActualEndTime = LogDate From
(
select * from (
select Top 1 LogDate from sandfitsetime.dbo.DeviceLogs a with (nolock)
Inner join hr.dbo.emp_personal b with (nolock) on a.userId = b.cardno and b.locationid = @LocationId
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
Inner Join Hr_TwelveHrShiftCtrlHrs TH on th.LocationId = b.locationid
where
Convert(date,LogDate) between c.StartDate and c.EndDate
and Convert(date,LogDate) between @AttDate and DATEADD(dd,1,@AttDate)
and UserId = @CardNo
and LogDate
between
DateAdd (MINUTE,-ISNULL(ChkShiftStTime,30),convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.Stime,8) )
and
case when DATEDIFF(MI,d.Stime,d.ETime)/60 < 10 Then
DATEADD(MINUTE,DATEDIFF(MINUTE,d.Stime,d.ETime)+30,convert(varchar(10),dateadd(dd,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8))
Else
DATEADD(MINUTE,th.ShiftEndTimeMaxMinutes,convert(varchar(10),dateadd(dd,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8))
End
--And (Logdate >= convert(varchar(10),DATEADD(dd,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8))
And (Logdate >= convert(varchar(10),DATEADD(dd,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8))
order by LogDate )T
Union All
select Top 1 LogDate from sandfitsetime.dbo.DeviceLogs a with (nolock)
Inner join hr.dbo.emp_personal b with (nolock) on a.userId = b.cardno and b.locationid = @LocationId
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
Left join hr.dbo.Designation hh with (nolock) on hh.desigid = ss.desigid
Left Join hr.dbo.Hrm_ShiftRotation c with (nolock) on c.EmpId = b.empid
Left Join hr.dbo.HRM_Shift d with (nolock) on d.ShiftId = c.ShiftId
Left Join production.dbo.HR_Controls hc with (nolock) on hc.DesigGroup = hh.DesigGroup and ss.catid = hc.CategoryId and b.locationid = @LocationId and hc.active = 'Y'
Inner Join Hr_TwelveHrShiftCtrlHrs TH on th.LocationId = b.locationid
where
Convert(date,LogDate) between c.StartDate and c.EndDate
and Convert(date,LogDate) between @AttDate and DATEADD(dd,1,@AttDate)
and UserId = @CardNo
and LogDate
between
DateAdd (MINUTE,-ISNULL(ChkShiftStTime,30),convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.Stime,8) )
and
case when DATEDIFF(MI,d.Stime,d.ETime)/60 < 10 Then
DATEADD(MINUTE,DATEDIFF(MINUTE,d.Stime,d.ETime)+30,convert(varchar(10),dateadd(dd,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8))
Else
DATEADD(MINUTE,th.ShiftEndTimeMaxMinutes,convert(varchar(10),dateadd(dd,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8))
End
--DATEADD(MINUTE,DATEDIFF(MINUTE,d.Stime,d.ETime)+30,convert(varchar(10),@AttDate,20)+' '+convert(varchar(8),d.etime,8))
And (Logdate <= convert(varchar(10),DATEADD(dd,1,@AttDate),20)+' '+convert(varchar(8),d.etime,8))
order by LogDate Desc)M
order by LogDate desc
Update hr.dbo.HRM_DailyAttendance set outtime = @ActualEndTime where EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId ;
COMMIT TRANSACTION UPD;
BEGIN TRANSACTION UPDAtt;
Update hr.dbo.HRM_DailyAttendance set Attendance =
(
Select
Case when
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between DATEADD(n,-ISNULL(hc.ChkShiftStTime,30),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8)) and
DATEADD(MINUTE,Isnull(hc.GraceTimeMin,5),a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
Then 'P'
When
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between DATEADD(n,-ISNULL(hc.ChkShiftStTime,30),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8)) and
DATEADD(MINUTE,Isnull(hc.GraceTimeMin,5),a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) < DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.LunchStartTime)
Then 'P'
When
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) > DATEADD(n,+Isnull(hc.GraceTimeMin,5),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8))
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) <= DATEADD(MINUTE,Isnull(hc.GraceTimeMin,5),a.LunchEndTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
Then 'P'
When
(DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between ShiftStartTime and DATEADD(n,+Isnull(hc.GraceTimeMin,5),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8))
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
)
Then 'P'
Else 'A'
End as Attendance
from hr.dbo.HRM_DailyAttendance a with (nolock)
Left Join hr.dbo.scalemaster b with (nolock) on b.empid = a.EmpId and id = (select MAX(id) from hr.dbo.scalemaster where empid =a.EmpId )
Left join hr.dbo.Designation d with (nolock) on d.desigid = b.desigid
Left Join HR_Controls hc with (nolock) on hc.DesigGroup = d.DesigGroup and hc.CategoryId = a.CatId and hc.Locationid = @LocationId and Active = 'Y'
where a.EmpId = @EmpId and AttDate = @AttDate and a.locationid = @LocationId
)
where EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId
COMMIT TRANSACTION UPDAtt;
BEGIN TRANSACTION UPDAttFlag;
Update hr.dbo.HRM_DailyAttendance set latedet =
(
Select
Case when
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between DATEADD(n,-ISNULL(hc.ChkShiftStTime,30),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8)) and
--DATEADD(MINUTE,Isnull(hc.GraceTimeMin,5),a.ShiftStartTime)
a.ShiftStartTime
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
Then 'P'
When
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between DATEADD(n,-ISNULL(hc.ChkShiftStTime,30),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8)) and
DATEADD(MINUTE,Isnull(hc.GraceTimeMin,5),a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) < DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.LunchStartTime)
Then 'FH'
When
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) > DATEADD(n,+Isnull(hc.GraceTimeMin,5),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8))
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) <= DATEADD(MINUTE,Isnull(hc.GraceTimeMin,5),a.LunchEndTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
Then 'SH'
When
(DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between ShiftStartTime and DATEADD(n,+Isnull(hc.GraceTimeMin,5),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8))
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
)
Then 'LP'
Else 'A'
End as AttFlag
from hr.dbo.HRM_DailyAttendance a with (nolock)
Left Join hr.dbo.scalemaster b with (nolock) on b.empid = a.EmpId and id = (select MAX(id) from hr.dbo.scalemaster where empid =a.EmpId )
Left join hr.dbo.Designation d with (nolock) on d.desigid = b.desigid
Left Join HR_Controls hc with (nolock) on hc.DesigGroup = d.DesigGroup and hc.CategoryId = a.CatId and hc.Locationid = @LocationId and Active = 'Y'
where a.empid = @EmpId and AttDate = @AttDate and a.locationid = @LocationId
)
where EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId
COMMIT TRANSACTION UPDAttFlag;
--General Permission
BEGIN TRANSACTION UPDGeneralPermission;
Update hr.dbo.HRM_DailyAttendance set Attendance = b.Attendance,Latedet = b.AttFlag
from hr.dbo.HRM_DailyAttendance mm
Inner join
(select a.empid,
case when c.PermissionLevel = 'SS' and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between ShiftStartTime and DATEADD(mi,PermissionHrsMin,a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= ShiftEndTime
then 'P'
When c.PermissionLevel = 'SE' and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) between DATEADD(mi,-PermissionHrsMin,a.ShiftEndTime) and ShiftEndTime
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) <= ShiftStartTime + Isnull(hc.GraceTimeMin,5)
Then 'P'
Else a.Attendance
End as Attendance,
case when c.PermissionLevel = 'SS' and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between ShiftStartTime and DATEADD(mi,PermissionHrsMin,a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= ShiftEndTime
then 'GP'
When c.PermissionLevel = 'SE' and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) between DATEADD(mi,-PermissionHrsMin,a.ShiftEndTime) and ShiftEndTime
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) <= ShiftStartTime + Isnull(hc.GraceTimeMin,5)
Then 'GP'
Else a.Latedet
End as AttFlag,FromTime,ToTime
from hr.dbo.HRM_DailyAttendance a with (nolock)
Inner join HR_Permission c with (nolock) on a.EmpId = c.EmpId and c.EntryDate = @AttDate
Left Join hr.dbo.scalemaster b with (nolock) on b.empid = a.EmpId and id = (select MAX(id) from hr.dbo.scalemaster where empid =a.EmpId )
Left join hr.dbo.Designation d with (nolock) on d.desigid = b.desigid
Left Join HR_Controls hc with (nolock) on hc.DesigGroup = d.DesigGroup and hc.CategoryId = a.CatId and hc.Locationid = @LocationId and Active = 'Y'
and c.Locationid = @LocationId
where a.EmpId = @EmpId and AttDate = @AttDate and a.LocationId = @LocationId and c.Approved = 'Y' and c.PermissionType = 'G') b on mm.empid = b.empid
where mm.EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId
Update HR_Permission set Download = 'Y' where EmpId = (select EmpId from hr.dbo.emp_personal where cardno = @CardNo ) and EntryDate = @AttDate
and Approved = 'Y'
COMMIT TRANSACTION UPDGeneralPermission;
--Special Permission
BEGIN TRANSACTION UPDSpecialPermission;
Update hr.dbo.HRM_DailyAttendance set Attendance = b.Attendance,Latedet = b.AttFlag
from hr.dbo.HRM_DailyAttendance mm
Inner join
(select a.empid,
case when c.PermissionLevel = 'SS' and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between ShiftStartTime and DATEADD(mi,PermissionHrsMin,a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= ShiftEndTime
then 'P'
When c.PermissionLevel = 'SE' and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) between DATEADD(mi,-PermissionHrsMin,a.ShiftEndTime) and ShiftEndTime
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) <= ShiftStartTime
Then 'P'
Else a.Attendance
End as Attendance,
case when c.PermissionLevel = 'SS' and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between ShiftStartTime and DATEADD(mi,PermissionHrsMin,a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= ShiftEndTime
then 'SP'
When c.PermissionLevel = 'SE' and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) between DATEADD(mi,-PermissionHrsMin,a.ShiftEndTime) and ShiftEndTime
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) <= ShiftStartTime
Then 'SP'
Else a.Latedet
End as AttFlag,FromTime,ToTime
from hr.dbo.HRM_DailyAttendance a with (nolock)
Inner join HR_Permission c with (nolock) on a.EmpId = c.EmpId and c.EntryDate = @AttDate
and c.Locationid = @LocationId
where a.EmpId = @EmpId and AttDate = @AttDate and a.LocationId = @LocationId and c.Approved = 'Y' and c.PermissionType = 'S') b on mm.empid = b.empid
where mm.EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId
Update HR_Permission set Download = 'Y' where EmpId = (select EmpId from hr.dbo.emp_personal where cardno = @CardNo ) and EntryDate = @AttDate
and Approved = 'Y'
COMMIT TRANSACTION UPDSpecialPermission;
--Late Permission
BEGIN TRANSACTION UPDLatePermission;
Update hr.dbo.HRM_DailyAttendance set Attendance = b.Attendance,Latedet = b.AttFlag
from hr.dbo.HRM_DailyAttendance mm
Inner join
(select a.empid,
case when DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between ShiftStartTime and DATEADD(mi,PermissionHrsMin,a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= ShiftEndTime
then 'P'
Else a.Attendance
End as Attendance,
case when DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between ShiftStartTime and DATEADD(mi,PermissionHrsMin,a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= ShiftEndTime
then 'LTP'
Else a.Latedet
End as AttFlag,FromTime,ToTime
from hr.dbo.HRM_DailyAttendance a with (nolock)
Inner join HR_Permission c with (nolock) on a.EmpId = c.EmpId and c.entryDate = @AttDate
and c.Locationid = @LocationId
where a.EmpId = @EmpId and AttDate = @AttDate and a.LocationId = @LocationId and c.Approved = 'Y' and c.PermissionType = 'L' ) b on mm.empid = b.empid
where mm.EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId
Update HR_Permission set Download = 'Y' where EmpId = (select EmpId from hr.dbo.emp_personal where cardno = @CardNo ) and EntryDate = @AttDate
and Approved = 'Y'
COMMIT TRANSACTION UPDLatePermission;
BEGIN TRANSACTION UPDMissPunch;
Update hr.dbo.HRM_DailyAttendance set
InTime = Case when InTime between DATEADD(minute,-Isnull(ChkShiftStartTime,30),shiftstarttime) and LunchStartTime then InTime else null end,
outtime = Case when outtime between DATEADD(MINUTE,1,LunchStartTime) and ShiftEndTime then outtime else null end,
Latedet = 'MP'
where AttDate = @AttDate and locationid = @LocationId and InTime = outtime and EmpId = @EmpId
COMMIT TRANSACTION UPDMissPunch;
BEGIN TRANSACTION UPDHalfMisPunch;
update hr.dbo.HRM_DailyAttendance
set HalfDayLeave = Isnull((case when Latedet = 'FH' Then 'Y' when Latedet = 'SH' Then 'Y' Else 'N' end),'N') ,
MisPunch = (case when Latedet = 'MP' Then 'Y' else 'N' end)
where EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId
COMMIT TRANSACTION UPDHalfMisPunch;
BEGIN TRANSACTION CalTimeUPD;
Update hr.dbo.HRM_DailyAttendance set CalStartTime =
(
Select Case when Attendance = 'P' and HalfDayLeave = 'N' Then
ShiftStartTime
When Attendance = 'P' and HalfDayLeave = 'Y' and Latedet = 'FH' Then
ShiftStartTime
when Attendance = 'P' and HalfDayLeave = 'Y' and Latedet = 'SH' Then
LunchEndTime
when Attendance = 'P' and Latedet = 'LP' Then
ShiftStartTime
End as CalStartTime
from hr.dbo.HRM_DailyAttendance a with (nolock)
Left Join hr.dbo.scalemaster b with (nolock) on b.empid = a.EmpId and id = (select MAX(id) from hr.dbo.scalemaster where empid =a.EmpId )
Left join hr.dbo.Designation d with (nolock) on d.desigid = b.desigid
Left Join HR_Controls hc with (nolock) on hc.DesigGroup = d.DesigGroup and hc.CategoryId = a.CatId and hc.Locationid = @LocationId and Active = 'Y'
where a.EmpId = @EmpId and AttDate = @AttDate and a.locationid = @LocationId
), CalEndTime =
(
Select Case when Attendance = 'P'and HalfDayLeave = 'N' Then
ShiftEndTime
When Attendance = 'P' and HalfDayLeave = 'Y' and Latedet = 'FH' Then
LunchStartTime
when Attendance = 'P' and HalfDayLeave = 'Y' and Latedet = 'SH' Then
ShiftEndTime
when Attendance = 'P' and Latedet = 'LP' Then
ShiftEndTime
End as CalEndTime
from hr.dbo.HRM_DailyAttendance a with (nolock)
Left Join hr.dbo.scalemaster b with (nolock) on b.empid = a.EmpId and id = (select MAX(id) from hr.dbo.scalemaster where empid =a.EmpId )
Left join hr.dbo.Designation d with (nolock) on d.desigid = b.desigid
Left Join HR_Controls hc with (nolock) on hc.DesigGroup = d.DesigGroup and hc.CategoryId = a.CatId and hc.Locationid = @LocationId and Active = 'Y'
where a.empid = @EmpId and AttDate = @AttDate and a.locationid = @LocationId
)
where EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId
COMMIT TRANSACTION CalTimeUPD;
--BEGIN TRANSACTION TotMinUPD;
--Update HR_DailyAttendance set TotalMin =
-- (
--Select DATEDIFF(MINUTE,CalStartTime,CalEndTime)
--from HR_DailyAttendance a with (nolock)
--Inner Join hr.dbo.scalemaster b with (nolock) on b.empid = a.EmpId and id = (select MAX(id) from hr.dbo.scalemaster where empid =a.EmpId )
--Inner join hr.dbo.Designation d with (nolock) on d.desigid = b.desigid
--Left Join HR_Controls hc with (nolock) on hc.DesigGroup = d.DesigGroup and hc.CategoryId = a.CatId and hc.Locationid = 1 and Active = 'Y'
--where CardNo = @CardNo and ShiftDate = @AttDate and a.locationid = 1
--)
--where CardNo = @CardNo and ShiftDate = @AttDate and locationid = 1
--COMMIT TRANSACTION TotMinUPD;
--BEGIN TRANSACTION ExtraMinUPD;
--Update HR_DailyAttendance set ExtraMin =
-- (
--Select DATEDIFF(MINUTE,CalEndTime,ActualEndTime)
--from HR_DailyAttendance a with (nolock)
--Inner Join hr.dbo.scalemaster b with (nolock) on b.empid = a.EmpId and id = (select MAX(id) from hr.dbo.scalemaster where empid =a.EmpId )
--Inner join hr.dbo.Designation d with (nolock) on d.desigid = b.desigid
--Left Join HR_Controls hc with (nolock) on hc.DesigGroup = d.DesigGroup and hc.CategoryId = a.CatId and hc.Locationid = @LocationId and Active = 'Y'
--where CardNo = @CardNo and ShiftDate = @AttDate and a.locationid = @LocationId
--)
--where CardNo = @CardNo and ShiftDate = @AttDate and locationid = @LocationId
--COMMIT TRANSACTION ExtraMinUPD;
BEGIN TRANSACTION LateMinUPD;
Update hr.dbo.HRM_DailyAttendance set LatePunch =
(
Select Case when CalStartTime < InTime and ShiftStartTime = CalStartTime then DATEDIFF(MINUTE,CalStartTime,InTime)
end
from hr.dbo.HRM_DailyAttendance a with (nolock)
Left Join hr.dbo.scalemaster b with (nolock) on b.empid = a.EmpId and id = (select MAX(id) from hr.dbo.scalemaster where empid =a.EmpId )
Left join hr.dbo.Designation d with (nolock) on d.desigid = b.desigid
Left Join HR_Controls hc with (nolock) on hc.DesigGroup = d.DesigGroup and hc.CategoryId = a.CatId and hc.Locationid = @LocationId and Active = 'Y'
where a.empid = @EmpId and AttDate = @AttDate and a.locationid = @LocationId
)
where EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId
COMMIT TRANSACTION LateMinUPD;
BEGIN TRANSACTION LateDetA;
Update hr.dbo.hrm_dailyattendance set Attendance = Latedet where Attendance = 'P' and Latedet = 'A' and LeaveId = 0 and
EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
COMMIT TRANSACTION LateDetA;
BEGIN TRANSACTION WorkerLateAtt;
Declare @LateCount Integer,
@GraceTimeCount Integer,
@LateDt varchar(10)
select @LateDt = Latedet from hr.dbo.hrm_dailyattendance where EmpId = @EmpId and AttDate = @AttDate and Latedet = 'LP'
select @LateCount = COUNT(*),@GraceTimeCount = GraceTimeCount from hr.dbo.hrm_dailyattendance a
Left join HR_Controls b on a.CatId = b.CategoryId and active = 'Y'
where a.AttDate between (select StartDate from YrMonDesc where Status = 'A' ) and
(select EndDate from YrMonDesc where Status = 'A') and EmpId = @EmpId and Latedet = 'LP'
and a.CatId not in (5,6,7,15,16,17,27,31)
group by GraceTimeCount
If @LateCount > @GraceTimeCount and @LateDt = 'LP'
Begin
Update hr.dbo.hrm_dailyattendance set HalfDayLeave = 'Y',Latedet = 'SH' where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
COMMIT TRANSACTION WorkerLateAtt;
--Download Flag
BEGIN TRANSACTION DownLoadFlag;
Declare @AStTime DateTime,
@AEndTime DateTime
select @AStTime = InTime,@AEndTime = outtime from hr.dbo.HRM_DailyAttendance where EmpId = @EmpId and AttDate = @AttDate and locationid = @LocationId
Update sandfitsetime.dbo.DeviceLogs set ChkData = 'Y' where LogDate = @AStTime and UserId = @CardNo
Update sandfitsetime.dbo.DeviceLogs set ChkData = 'Y' where LogDate = @AEndTime and UserId = @CardNo
Update sandfitsetime.dbo.DeviceLogs set ChkData1 = 'Y' where LogDate between DATEADD(SECOND,1,@AStTime) and DATEADD(SECOND,-1,@AEndTime) and UserId = @CardNo
COMMIT TRANSACTION DownLoadFlag;
Fetch next from cur_HrmDailyAttendanceTest into @EmpId, @CardNo,@NextDay,@PreviousDay;
End
Close cur_HrmDailyAttendanceTest
Deallocate cur_HrmDailyAttendanceTest
SET NOCOUNT OFF;
--ElOdComLeave
BEGIN TRANSACTION ElOdComLeave;
Insert Into hr.dbo.HRM_DailyAttendance
(EmpId,ReasonForLeave,AttDate,Leaveid,CatId,DeptId,Attendance,Latedet,
EntryEmpId,EntryComputer,ImportMachine,LocationId,entrydate)
select a.empid,a.reason,@AttDate,a.leavetype,isnull(ss.catid,e.catid),isnull(ss.deptid,e.deptid),
Case when c.LeaveType = 'absent' Then 'A'
Else 'A' End as LeaveTypeAttendance,
Case when c.LeaveType = 'absent' Then 'L'
Else c.LeaveType End as LeaveType,
10,'Server','N',@LocationId,GETDATE()
from hr.dbo.hrm_leavemaster a
Inner join hr.dbo.hrm_leavedet b on a.appno = b.appno
Inner join hr.dbo.HRM_LeaveType c on a.leavetype = c.leaveid
Inner Join hr.dbo.emp_personal d on a.empid = d.empid
Inner join hr.dbo.emp_employementdet e on d.empid = e.empid
Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
where
leavedate = @AttDate and b.approved = 'Y'
and d.locationid = @LocationId
and a.leavetype <> 3 and a.leavetype <> 6
and d.empid not in (select EmpId from hr.dbo.HRM_DailyAttendance where AttDate = @AttDate and locationid = @LocationId)
and d.empid = @EmpId
COMMIT TRANSACTION ElOdComLeave;
--Absenties
BEGIN TRANSACTION Absenties;
--Declare @Leave Integer = 0,
--@Desc Varchar(100)
----select COUNT(*), LDesc from hr.dbo.leave where LDate = '2017-03-05' and locationid = 1 group by ldesc
--select @Leave = COUNT(*),@Desc = LDesc from hr.dbo.leave where LDate = @AttDate and locationid = @LocationId
--group by LDesc
--If @Leave = 0
--Begin
Insert Into hr.dbo.HRM_DailyAttendance
(EmpId,AttDate,LeaveId,CatId,DeptId,Attendance,Latedet,
EntryEmpId,EntryComputer,ImportMachine,LocationId,ent_down_date)
select a.empid,@AttDate,0,isnull(ss.catid,b.catid),isnull(ss.deptid,b.deptid),
Case when Isnull(ss.catid,b.catid) = 27 Then 'P' Else 'A' End ,
Case when Isnull(ss.catid,b.catid) = 27 Then 'P' Else 'A' End ,
10,'Server','N',@LocationId,GETDATE()
from hr.dbo.emp_personal a
Inner join hr.dbo.emp_employementdet b on a.empid = b.empid and catid not in (21,22)
Inner join hr.dbo.department c on c.deptid = b.deptid
Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
where a.locationid = @LocationId and resign <> 'Y' and b.catid = 27
and a.empid not in (select empid from hr.dbo.HRM_DailyAttendance where locationid = @LocationId and AttDate = @AttDate and empid = @empid )
and a.empid = @empid
--and a.empid not in (select a.empid from hr.dbo.emp_personal a
-- Inner join hr.dbo.hrm_weekoff wh on a.empid = wh.empid
-- and @Attdate between wh.eff_from and wh.eff_to
-- and week_off = DATENAME(dw,@Attdate)
-- )
Insert Into hr.dbo.HRM_DailyAttendance
(EmpId,AttDate,LeaveId,CatId,DeptId,Attendance,Latedet,
EntryEmpId,EntryComputer,ImportMachine,LocationId,ent_down_date)
select a.empid,@AttDate,6,isnull(ss.catid,b.catid),isnull(ss.deptid,b.deptid),
Case when Isnull(ss.catid,b.catid) = 27 Then 'P' Else 'A' End ,
Case when Isnull(ss.catid,b.catid) = 27 Then 'P' Else 'A' End ,
10,'Server','N',@LocationId,GETDATE()
from hr.dbo.emp_personal a
Inner join hr.dbo.emp_employementdet b on a.empid = b.empid and catid not in (21,22)
Inner join hr.dbo.department c on c.deptid = b.deptid
Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
where a.locationid = @LocationId and resign <> 'Y' and b.catid <> 27
and a.empid not in (select empid from hr.dbo.HRM_DailyAttendance where locationid = @LocationId and AttDate = @AttDate and empid = @empid )
and a.empid = @empid
--End
--Weekly holiday-->Weekoff employee absent
--Desabled on 050517 instructed by Mr.sureshbabu
--If @Leave > 0 and @Desc = 'Weekly Holiday'
--Begin
--Insert Into hr.dbo.HRM_DailyAttendance
-- (EmpId,AttDate,LeaveId,CatId,DeptId,Attendance,Latedet,
--EntryEmpId,EntryComputer,ImportMachine,LocationId,ent_down_date)
--select t.empid,@AttDate,6,isnull(ss.catid,b.catid),isnull(ss.deptid,b.deptid),
--Case when Isnull(ss.catid,b.catid) = 27 Then 'P' Else 'A' End ,
--Case when Isnull(ss.catid,b.catid) = 27 Then 'P' Else 'A' End ,
--10,'Server','N',@LocationId,GETDATE()
--from hr.dbo.emp_Personal a
--Inner Join hr.dbo.emp_employementdet b on a.empid = b.empid
--Inner Join (select a.empid from hr.dbo.emp_personal a
--Inner join hr.dbo.hrm_weekoff wh on a.empid = wh.empid
--and @Attdate between wh.eff_from and wh.eff_to) t on a.empid = t.empid
--Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
--and a.locationid = @LocationId and resign <> 'Y'
-- where a.empid not in (select empid from hr.dbo.HRM_DailyAttendance where locationid = @LocationId and AttDate = @AttDate)
-- and a.empid = @EmpId
--End
COMMIT TRANSACTION Absenties;
--SundayAbsenties
BEGIN TRANSACTION SundayAbsenties;
Declare @SundayLeave Integer = 0
--select COUNT(*), LDesc from hr.dbo.leave where LDate = '2017-03-05' and locationid = 1 group by ldesc
select @SundayLeave = COUNT(*) from hr.dbo.leave where LDate = @AttDate and locationid = @LocationId and LDesc = 'Weekly Holiday'
group by LDesc
If @SundayLeave > 0
Begin
--Declare @Day varchar(20) = (select DATENAME(dw,convert(date,(@AttDate))))
--Declare @WeekDay varchar(20) = ''
--select @WeekDay = week_off from hr.dbo.hrm_weekoff where empid = @EmpId and @AttDate between eff_from and eff_to
--If @WeekDay = ''
--Begin
Delete hr.dbo.hrm_dailyattendance where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate and Attendance = 'A' and CatId in (5,6,7,15,16,17,40) and EmpId not in (select empid from hr.dbo.hrm_weekoff where @AttDate between eff_from and eff_to) and ISNULL(Onduty,'N') = 'N'
--End
End
COMMIT TRANSACTION SundayAbsenties;
--^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--NFHAbsenties
BEGIN TRANSACTION NFHAbsenties;
Declare @NFHLeave Integer = 0
--select COUNT(*), LDesc from hr.dbo.leave where LDate = '2017-03-05' and locationid = 1 group by ldesc
select @NFHLeave = COUNT(*) from hr.dbo.leave where LDate = @AttDate and locationid = @LocationId and LDesc <> 'Weekly Holiday'
group by LDesc
If @NFHLeave > 0
Begin
--Declare @Day varchar(20) = (select DATENAME(dw,convert(date,(@AttDate))))
--Declare @WeekDay varchar(20) = ''
--select @WeekDay = week_off from hr.dbo.hrm_weekoff where empid = @EmpId and @AttDate between eff_from and eff_to
--If @WeekDay = ''
--Begin
--and EmpId not in (select empid from hr.dbo.hrm_weekoff where @AttDate between eff_from and eff_to)
Delete hr.dbo.hrm_dailyattendance where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate and Attendance = 'A' and CatId in (5,6,7,15,16,17,40) and ISNULL(Onduty,'N') = 'N'
--End
End
COMMIT TRANSACTION NFHAbsenties;
--^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--***************************************
--WeekOffAbsenties
BEGIN TRANSACTION WeekOffAbsenties;
Declare @WeekOffLeave Integer = 0
select @WeekOffLeave = COUNT(*) from hr.dbo.hrm_weeklyoff where LDate = @AttDate and empid = @EmpId and locationid = @LocationId
If @WeekOffLeave > 0
Begin
Print @WeekOffLeave
Delete hr.dbo.hrm_dailyattendance where EmpId = @EmpId and EmpId in (select EmpId from hr.dbo.hrm_weeklyoff where LDate = @AttDate and locationid = @LocationId) and LocationId = @LocationId and AttDate = @AttDate and Attendance = 'A' and CatId in (5,6,7,15,16,17,40) and ISNULL(Onduty,'N') = 'N'
End
COMMIT TRANSACTION WeekOffAbsenties;
--***************************************
--ElComLeave Update
Declare cur_ElOdComLeave Cursor for
select d.empid,d.cardno,b.leavedate
from hr.dbo.hrm_leavemaster a
Inner join hr.dbo.hrm_leavedet b on a.appno = b.appno
Inner join hr.dbo.HRM_LeaveType c on a.leavetype = c.leaveid
Inner Join hr.dbo.emp_personal d on a.empid = d.empid
Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
where leavedate = @AttDate and b.approved = 'Y' and a.leavetype <> 3 and a.empid = @EmpId
Open cur_ElOdComLeave;
fetch next from cur_ElOdComLeave into @EmpId,@CardNo,@attDate;
While @@FETCH_STATUS = 0
Begin
BEGIN TRANSACTION ElOdComLeave;
select @FHFlag = Isnull(Case when b.halfday = 'FH' Then (Case when c.LeaveType = 'absent' Then 'L' Else c.LeaveType End) End,Null) ,
@SHFlag = IsNull(Case when b.halfday = 'SH' Then (Case when c.LeaveType = 'absent' Then 'L' Else c.LeaveType End) End,Null) ,
@FullFlag = IsNull(Case when b.halfday = 'F' Then (Case when c.LeaveType = 'absent' Then 'L' Else c.LeaveType End) End,Null) ,
@Reason = substring(reason,1,100) ,@LeaveId = a.leavetype
from hr.dbo.hrm_leavemaster a
Inner join hr.dbo.hrm_leavedet b on a.appno = b.appno
Inner join hr.dbo.HRM_LeaveType c on a.leavetype = c.leaveid
Inner Join hr.dbo.emp_personal d on a.empid = d.empid
Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
where d.cardno = @CardNo and leavedate = @AttDate and a.leavetype <> 3 and a.leavetype <> 6 and a.empid = @EmpId And b.approved = 'Y'
Update hr.dbo.HRM_DailyAttendance set FHFlag = @FHFlag,SHFlag = @SHFlag ,FullFlag = @FullFlag,
ReasonForLeave = @Reason,LeaveId = @LeaveId
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
Print @FHFlag
print @SHFlag
print @FullFlag
print @Reason
print @LeaveId
print @EmpId
print @LocationId
print @AttDate
If @SHFlag <> 'L'
Begin
--Update hr.dbo.hrm_dailyattendance set
--HalfDayLeave = IsNull(
--(select case when
--InTime between DATEADD(minute,-Isnull(chkshiftStartTime,30),intime)
--and DATEADD(minute,+Isnull(GraceTime,5),intime) then 'Y' else Isnull(HalfDayLeave,'N') end
--from hr.dbo.HRM_DailyAttendance
--where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
--and Attendance = 'P' and Isnull(SHFlag,'L') <> 'L'
--and InTime between DATEADD(minute,-Isnull(chkshiftStartTime,30),intime)
--and DATEADD(minute,+Isnull(GraceTime,5),intime)
--and outtime >= LunchStartTime),'N'),Latedet = @SHFlag
--where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
--and InTime Is Not Null and outTime Is Not Null
Update hr.dbo.hrm_dailyattendance set HalfDayLeave = 'Y' where EmpId = @EmpId and AttDate = @AttDate
End
If @FHFlag <> 'L'
Begin
--update hr.dbo.HRM_DailyAttendance set HalfDayLeave =
--Isnull((select case when
--InTime between DATEADD(minute,-Isnull(chkshiftStartTime,30),intime)
--and DATEADD(minute,+Isnull(GraceTime,5),intime) then 'Y' else Isnull(HalfDayLeave,'N') end
--from hr.dbo.HRM_DailyAttendance
--where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
--and Attendance = 'P' and Isnull(FHFlag,'L') <> 'L'
--and InTime between ShiftStartTime
--and DATEADD(minute,+Isnull(GraceTime,5),LunchEndTime)
--and outtime >= ShiftEndTime),'N'),Latedet = @FHFlag
--where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
--and InTime Is Not Null and outTime Is Not Null
Update hr.dbo.hrm_dailyattendance set HalfDayLeave = 'Y' where EmpId = @EmpId and AttDate = @AttDate
End
If @FullFlag is not null
Begin
update hr.dbo.HRM_DailyAttendance set Attendance = 'A',MisPunch = 'N',HalfDayLeave = 'N',Latedet = @FullFlag
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
--Update hr.dbo.hrm_dailyattendance set Attendance = 'A' where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
--and InTime Is Null and outTime Is Null
COMMIT TRANSACTION ElOdComLeave;
Fetch next from cur_ElOdComLeave into @empid,@CardNo,@attDate;
End
Close cur_ElOdComLeave;
Deallocate cur_ElOdComLeave;
--Od Update
Declare cur_OD Cursor for
select d.empid,d.cardno,b.leavedate
from hr.dbo.hrm_leavemaster a
Inner join hr.dbo.hrm_leavedet b on a.appno = b.appno
Inner join hr.dbo.HRM_LeaveType c on a.leavetype = c.leaveid
Inner Join hr.dbo.emp_personal d on a.empid = d.empid
Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id in (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
where leavedate = @AttDate and b.approved = 'Y' and a.leavetype = 3 and a.empid = @EmpId and d.locationid = @LocationId
Open cur_OD;
fetch next from cur_OD into @EmpId,@CardNo,@attDate;
While @@FETCH_STATUS = 0
Begin
BEGIN TRANSACTION OD;
select @FHFlagOD = Isnull(Case when b.halfday = 'FH' Then (Case when c.LeaveType = 'absent' Then 'L' Else c.LeaveType End) End,hd.FHflag) ,
@Reason = substring(a.reason,1,100) ,@LeaveId = a.leavetype,
@ODAndPermission = Case when hd.OutTime between hp.fromtime and hp.ToTime Then 'P'Else hd.Attendance end
from hr.dbo.hrm_leavemaster a
Inner join hr.dbo.hrm_leavedet b on a.appno = b.appno and b.approved = 'Y'
Inner join hr.dbo.HRM_LeaveType c on a.leavetype = c.leaveid
Inner Join hr.dbo.emp_personal d on a.empid = d.empid
Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id in (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
Left Join hr.dbo.hrm_dailyattendance hd on hd.EmpId = d.empid and hd.AttDate = @AttDate
Left Join HR_Permission hp on hd.EmpId = hp.EmpId and hp.EntryDate = hd.AttDate and hp.approved = 'Y'
where d.cardno = @CardNo and leavedate = @AttDate and a.empid = @EmpId and halfday = 'FH' and d.LocationId = @LocationId
select @SHFlagOD = IsNull(Case when b.halfday = 'SH' Then (Case when c.LeaveType = 'absent' Then 'L' Else c.LeaveType End) End,hd.SHFlag) ,
@Reason = substring(a.reason,1,100) ,@LeaveId = a.leavetype,
@ODAndPermission = Case when hd.InTime between hp.fromtime and hp.ToTime Then 'P' Else hd.Attendance end
from hr.dbo.hrm_leavemaster a
Inner join hr.dbo.hrm_leavedet b on a.appno = b.appno and b.approved = 'Y'
Inner join hr.dbo.HRM_LeaveType c on a.leavetype = c.leaveid
Inner Join hr.dbo.emp_personal d on a.empid = d.empid
Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id in (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
Left Join hr.dbo.hrm_dailyattendance hd on hd.EmpId = d.empid and hd.AttDate = @AttDate
Left Join HR_Permission hp on hd.EmpId = hp.EmpId and hp.EntryDate = hd.AttDate and hp.approved = 'Y'
where d.cardno = @CardNo and leavedate = @AttDate and a.empid = @EmpId and HalfDay = 'SH' and d.LocationId = @LocationId
select
@FullFlagOD = IsNull(Case when b.halfday = 'F' Then (Case when c.LeaveType = 'absent' Then 'L' Else c.LeaveType End) End,hd.FullFlag) ,
@Reason = substring(reason,1,100) ,@LeaveId = a.leavetype,@ODAndPermission = 'P'
from hr.dbo.hrm_leavemaster a
Inner join hr.dbo.hrm_leavedet b on a.appno = b.appno and b.approved = 'Y'
Inner join hr.dbo.HRM_LeaveType c on a.leavetype = c.leaveid
Inner Join hr.dbo.emp_personal d on a.empid = d.empid
Left Join hr.dbo.scalemaster ss with (nolock) on a.empid = ss.EmpId and ss.id in (select MAX(id) from hr.dbo.scalemaster where empid =a.empid )
Left Join hr.dbo.hrm_dailyattendance hd on hd.EmpId = d.empid and hd.AttDate = @AttDate
where d.cardno = @CardNo and leavedate = @AttDate and a.empid = @EmpId and halfday = 'F' and d.LocationId = @LocationId
Declare @Check1 Integer = 0
select @Check1 = COUNT(*) from hr.dbo.hrm_dailyattendance where EmpId = @EmpId and AttDate = @AttDate
If @Check1 >0
Begin
Print @ODAndPermission
Print 'ODAndPer'
print @ODAndPermission
If @ODAndPermission = 'P'
Begin
Update hr.dbo.HRM_DailyAttendance set FHFlag = @FHFlagOD,SHFlag = @SHFlagOD ,FullFlag = @FullFlagOD,
Onduty = 'Y',Attendance = 'P'
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
If @ODAndPermission <> 'P'
Begin
Update hr.dbo.HRM_DailyAttendance set FHFlag = @FHFlagOD,SHFlag = @SHFlagOD ,FullFlag = @FullFlagOD,
Onduty = 'Y'
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
Print @FHFlag
print @SHFlag
print @FullFlag
print @Reason
print @LeaveId
print @EmpId
Print @CardNo
print @LocationId
print @AttDate
End
If @Check1 = 0
Begin
Insert into hr.dbo.hrm_dailyattendance (EmpId,AttDate,CatId,DeptId,Attendance,EntryEmpId,EntryComputer,ImportMachine,LocationId,ent_down_date,Latedet,SHFlag,FHFlag,FullFlag,Onduty)
select @EmpId,@AttDate,Isnull(ss.CatId,ed.catid)Catid,ISNULL(ss.deptid,ed.deptid)DeptId,'A', 10,'Server','Y',@LocationId,GETDATE(),'A',@SHFlagOD,@FHFlagOD,@FullFlagOD,'Y'
from hr.dbo.emp_personal b
Inner join hr.dbo.emp_employementdet ed with (nolock) on b.empid = ed.empid
Left Join hr.dbo.scalemaster ss with (nolock) on b.empid = ss.EmpId and ss.id = (select MAX(id) from hr.dbo.scalemaster where empid =b.empid )
where b.empid = @EmpId and LocationId = @LocationId
End
Print @SHFlagOD
print 'SH'
Print @FHFlagOD
Print 'FH'
Print @FullFlagOD
Print 'FLL'
Print @ODAndPermission
If @SHFlagOD = 'OD'
Begin
If @ODAndPermission = 'P'
Begin
Update hr.dbo.hrm_dailyattendance set Onduty = 'Y', HalfDayOnduty = 'Y' , Latedet = 'OD',Attendance = 'P'
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
If @ODAndPermission <> 'P'
Begin
Update hr.dbo.hrm_dailyattendance set Onduty = 'Y', HalfDayOnduty = 'Y' , Latedet = 'OD'
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
End
If @FHFlagOD = 'OD'
Begin
If @ODAndPermission = 'P'
Begin
Update hr.dbo.hrm_dailyattendance set Onduty = 'Y', HalfDayOnduty = 'Y',Latedet = 'OD',Attendance = 'P'
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
If @ODAndPermission <> 'P'
Begin
Update hr.dbo.hrm_dailyattendance set Onduty = 'Y', HalfDayOnduty = 'Y',Latedet = 'OD'
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
End
If @FullFlagOD = 'OD'
Begin
Update hr.dbo.hrm_dailyattendance set HalfDayLeave = 'N', Onduty = 'Y',HalfDayOnduty = 'N',Latedet = 'OD',MisPunch = 'N',Attendance = 'A'
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
If @SHFlagOD = 'OD' and @FHFlagOD = 'OD'
Begin
Update hr.dbo.hrm_dailyattendance set HalfDayLeave = 'N', Onduty = 'Y', HalfDayOnduty = 'N',Latedet = 'OD',MisPunch = 'N'
where EmpId = @EmpId and LocationId = @LocationId and AttDate = @AttDate
End
Update hr.dbo.hrm_dailyattendance set HalfDayLeave = (
select distinct ISNULL( Case when
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) between DATEADD(n,-ISNULL(hc.ChkShiftStTime,30),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8)) and
DATEADD(MINUTE,Isnull(hc.GraceTimeMin,5),a.ShiftStartTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) < DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.LunchStartTime)
and halfday = 'SH' then 'N'
When
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) > DATEADD(n,+Isnull(hc.GraceTimeMin,5),convert(varchar(10),a.InTime,20)+' '+convert(varchar(8),a.ShiftStartTime,8))
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.InTime), 0) <= DATEADD(MINUTE,Isnull(hc.GraceTimeMin,5),a.LunchEndTime)
and DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a.OutTime), 0) >= DATEADD(n,-ISNULL(hc.EarlyOutPunch,0),a.ShiftEndTime)
and halfday = 'FH'
Then 'N'
Else HalfDayLeave end,HalfDayLeave) from hr.dbo.HRM_DailyAttendance a with (nolock)
Left Join hr.dbo.scalemaster b with (nolock) on b.empid = a.EmpId and id in (select MAX(id) from hr.dbo.scalemaster where empid =a.EmpId )
Left join hr.dbo.Designation d with (nolock) on d.desigid = b.desigid
Left Join HR_Controls hc with (nolock) on hc.DesigGroup = d.DesigGroup and hc.CategoryId = a.CatId and hc.Locationid = @LocationId and Active = 'Y'
Inner join hr.dbo.hrm_leavemaster lm on a.EmpId = lm.empid
Inner join hr.dbo.hrm_leavedet Ld on ld.appno = lm.appno and a.AttDate = ld.leavedate and ld.approved = 'Y'
where a.EmpId = @EmpId and AttDate = @AttDate and ld.approved = 'Y' and a.Onduty = 'Y' and lm.leavetype = 3 )
where EmpId = @EmpId and AttDate = @AttDate and Onduty = 'Y'
COMMIT TRANSACTION OD;
Fetch next from cur_OD into @empid,@CardNo,@attDate;
End
Close cur_OD;
Deallocate cur_OD;
End
No comments:
Post a Comment