I'm working on an attendance sheet project for a store with 11 employees. Employees will clock in and clock out in their pre-determined times. I have a problem with shifts. I want to find a way for the system to figure out if the last line recorded was in the morning shift, the subsequent line should be in evening shift and vice versa.
The structure of my program is this: I have a button on my program for employees to determine the morning shift or the evening shift with a boolean variable. I have a text box, which when an employeeId/memberId is entered in it and pressed enter, it will process the eligibility of that memberId with a stored procedure with this logic: memberIds who 1. have clockedIn but have not ClockedOut (union with) 2.members who are generally in our tbl_attendanceMembers but have not entered in the attendance sheet are eligible to be entered in the textbox. Store these two in a medium table called tbl_availableMembers.
CREATE PROCEDURE duplicateCheck3
@inputdate date
AS
BEGIN
DELETE FROM tbl_availableMembers
Insert into tbl_availableMembers SELECT memberId
FROM tbl_attendancemembers
WHERE memberId NOT IN
(SELECT memberId
FROM tbl_attendanceSheet
WHERE DATE = @InputDate)
UNION
SELECT memberId from tbl_attendancesheet
WHERE [date] = @InputDate
AND [clockin] IS NOT NULL
AND [clockout] IS NULL
END
Then in C# I will read the values of tbl_availableMembers everytime a memberId has entered the textbox like this:
SqlCommand sqlcmdread_availablemembers = new SqlCommand("select * from tbl_availablemembers", sqlcon);
SqlDataReader sqldr_availablemembers = sqlcmdread_availablemembers.ExecuteReader();
while (sqldr_availablemembers.Read())
{
if (sqldr_availablemembers[1].ToString() == textBoxX1.Text)
{
member_available = true;
break;
}
}
And if the entered id in the textbox is equal to the ones that are read, it goes through a stored procedure like this:
CREATE PROCEDURE InputClockStacks
@QmemberId nvarchar(20),
@InputName nvarchar(20),
@DateString nvarchar(100),
@InputDate date,
@InputShift Bit,
@InputTime time(7),
@EnterDist nvarchar(50),
@EnterDelay time(7),
@ExitDist nvarchar(50),
@ExitDelay time(7)
AS
UPDATE [dbo].[tbl_attendanceSheet]
SET [clockout] = @InputTime, [exitdelay]=@ExitDelay, ExitDist=@ExitDist
WHERE MemberId = @QmemberId
AND [date] = @InputDate
AND [clockin] IS NOT NULL
AND [clockout] IS NULL
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO tbl_attendanceSheet(MemberId,name,datestring,date,morning_shift,clockIn,EnterDist,EnterDelay)
VALUES (@QmemberId, @InputName,@DateString, @InputDate, @InputShift, @InputTime,@EnterDist,@EnterDelay)
END
It basically, has the following logic: if the ClockOut column is null and clockIn column is not null it means the employee has to only exit one's workplace so the system should insert the time into ClockOut column. Otherwise, it means that the employee has not even come to work so it should insert a new row with clockIn and ClockOut.
The logic of tbl_availableMembers has a problem. The problem is that if a member has clockedIn and out in the morning shift, the duplicateCheck3 stored procedure will not let him/her to clockIn in the evening shift.
I want to implement a logic like this:
SELECT * FROM tbl_attendanceSheet
WHERE date = @inputDate // to get all users who have clocked in and(or) out
in the given date or current date.
IF morning_shif is true, only allow him/ her to ClockIn in the evening shift
or this logic: allow every employee to occupy only two rows in the attendance sheet table. one for clocking in and out in the morning shift and one for clocking in and out in the evening shift.
or this logic: find a way for the system to figure out if the last line recorded was in the morning shift, the subsequent line should be in evening shift and vice versa.
At last, I want to thank you all for your remindings of SQL-injection, I will fix it later. and Thank You for your time
My tables:
CREATE TABLE [dbo].[tbl_attendanceSheet] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[MemberId] INT NULL,
[name] NVARCHAR (20) NULL,
[date] DATE NULL,
[datestring] NVARCHAR (100) NULL,
[morning_shift] BIT NOT NULL,
[clockin] TIME (7) NULL,
[clockout] TIME (7) NULL,
[HouresWorked] AS (CONVERT([time],dateadd(millisecond,datediff(second,[clockin],[clockout])*(1000),(0)))),
[EnterDist] NVARCHAR (50) NULL,
[EnterDelay] TIME (7) NULL,
[ExitDist] NVARCHAR (50) NULL,
[ExitDelay] TIME (7) NULL,
[desc] NVARCHAR (150) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
FOREIGN KEY ([MemberId]) REFERENCES [dbo].[tbl_attendanceMembers] ([memberId])
);
and
CREATE TABLE [dbo].[tbl_attendanceMembers] (
[memberId] INT IDENTITY (101000, 10) NOT NULL,
[name] NVARCHAR (20) NULL,
[branchId] INT NULL,
[isOpener] BIT NULL,
[cellularphone] NVARCHAR (20) NULL,
[exp] NVARCHAR (100) NULL,
[address] NVARCHAR (100) NULL,
CONSTRAINT [PK_tbl_attendanceMembers] PRIMARY KEY CLUSTERED ([memberId] ASC)
);
and
CREATE TABLE [dbo].[tbl_availableMembers] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[memberId] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
What I have searched, myself:
Change value in a 2-value column to its opposite only if a condition in another column is met Its different but has a similar Idea But I can't understand the answer he marked as solution.
Is there an elegant way to Invert a Bit value in an SQL insert Statement? It is really good, But I don't know how to implement it.
Thank you for your time.
