i want to PunchOut my attendance for night shift on next day.

Question

User-508818466 posted

Hi,

I have two buttons Punch In and Punch Out. I am able to punch In today at 10 PM but not able to punch out tomorrow at 7 AM. Its automatically saving null value in Punch Out column.

This is my Procedure

ALTER proc [dbo].[InsertEmpAttendance]

@EmpID int,
@Flag varchar(50)
as
begin
declare @count int,@Id bigint
if(@Flag='PunchIn')
begin
select @count=count(*) from Emp_Attendance where EmpID=@EmpID and convert(date, PunchIn) = convert(date,getdate())

if(@count=0)
begin
insert into Emp_Attendance(PunchIn,EmpID) values(GETDATE(),@EmpID)
end
end
else if(@Flag='PunchOut')
begin
select @count=count(ID),@Id=ID from Emp_Attendance where EmpID=@EmpID and convert(date, PunchIn) = convert(date,getdate()) group by id

if(@count=1)
begin
update Emp_Attendance set PunchOut=GETDATE() where ID=@Id
end
end
else
begin
select PunchIn,PunchOut from Emp_Attendance where EmpID=@EmpID and convert(date, PunchIn) = convert(Date, getdate())
end
end

And this is my code for two buttons

protected void btnPunchIn_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("InsertEmpAttendance", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpID", Session["EmpID"]);
cmd.Parameters.AddWithValue("@Flag", "PunchIn");
cmd.ExecuteNonQuery();
con.Close();
}

protected void Button1_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("InsertEmpAttendance", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpID", Session["EmpID"]);
cmd.Parameters.AddWithValue("@Flag", "PunchOut");
cmd.ExecuteNonQuery();
con.Close(); }

Kindly help with this

Answer 1

User475983607 posted

Simply the design.  Create a PunchIn procedure and a PuchOut procedure rather than one procedure that tries to do both.   This will reduce the conditional logic in the procedure.

Then just update the click events.

protected void btnPunchIn_Click(object sender, EventArgs e)
{
    con.Open();
    SqlCommand cmd = new SqlCommand("PunchIn", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@EmpID", Session["EmpID"]);
    cmd.ExecuteNonQuery();
    con.Close();
}

protected void Button1_Click(object sender, EventArgs e)
{
    con.Open();
    SqlCommand cmd = new SqlCommand("PunchOut", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@EmpID", Session["EmpID"]);
    cmd.ExecuteNonQuery();
    con.Close(); 
}

Answer 2

User-508818466 posted

Actually its not able to punch out for the same date. If i am doing night shift then today i will punch in at 10PM but it will not able to punch out at 7AM on the next day for the yesterday's date. so how to do it for night shift. or how do i check wheather i have punch out or not.

Thanks

Answer 3

User475983607 posted

Actually its not able to punch out for the same date. If i am doing night shift then today i will punch in at 10PM but it will not able to punch out at 7AM on the next day for the yesterday's date. so how to do it for night shift. or how do i check wheather i have punch out or not.

IMHO, you've made the design far to complicated.  One table can hold the IN and OUT times; TimeAndAttendance.  This table has at least three columns the employee Id, IN/OUT, and DateTime.  The primary key DateTime-UserId or all three.  

CREATE TABLE dbo.InOutType 
(
	InOutTypeId		INT	PRIMARY KEY IDENTITY(1,1),
	[Description]	VARCHAR(64)
)

CREATE TABLE dbo.TimeAndAttendance(
	UserId		INT NOT NULL,
	InOutTime	DATETIME NOT NULL,
	InOutTypeId	INT,
	CONSTRAINT PK_TimeAndAttendance PRIMARY KEY (InOutTime, UserId, InOutTypeId),
	FOREIGN KEY (InOutTypeId) REFERENCES InOutType(InOutTypeId)		
)

INSERT INTO dbo.InOutType ([Description])
VALUES ('In'), ('OUT')

This basic design allows you to easily query the TimeAndAttendance table to get the user state.  There's nothing to figure out just get the the last record where the data is max for the user.  This will tell what button to light up; In or OUT.  

DECLARE @userId INT = 3
SELECT a.UserId, a.InOuttime, io.Description
FROM dbo.TimeAndAttendance AS a
	JOIN [dbo].[InOutType] AS io ON a.InOutTypeId = io.InOutTypeId
	JOIN (	SELECT
			MAX(InOutTime) AS InOutTime
			FROM dbo.TimeAndAttendance 
			WHERE UserId = @userId) AS t ON a.InOutTime = t.InOutTime
WHERE UserId = @userId