How to use timer control to compare between last time from table MachineSettingsData And last time from table acc_monitor_log

[engahmedbarbary]

Problem

How to use timer control to detect last attendance of employee and then show message welcome name as example welcome gorage as example ?

I have two tables

table MachineSettingsData store last timepunch for machineid=1

this table have only one row per every machine

Table acc_monitor_log store last attendance time for device_id =1

when last record time stored on table acc_monitor_log for deviceid=1 is bigger than from last time exist on table machinesettingsdata to machineid=1


then update last time exist on table machinesettingsdata and show message welcome username

and display this message for 30 second and then display null in name

meaning :

every second detect

if(accmonitorlog time for deviceid=1 > lastrecordattendance machinesettingsdata for machineid=1)

{

show welcome for 30 second then hide it .

if any punch happen done withine 30 second then show message welcome again

}


CREATE TABLE [dbo].[MachineSettingsData](
[MachineId] [int] NOT NULL,
[LastRecordAttendance] [bigint] NOT NULL,
[UserId] [nvarchar](50) NOT NULL,
[FlagExceedXsecondOrNot] [bit] NULL,
[LastDatePunch] [datetime] NULL,
[BranchCode] [int] NULL,
CONSTRAINT [PK_MachineSettingsData] PRIMARY KEY CLUSTERED
(
[MachineId] ASC,
[LastRecordAttendance] ASC,
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



GO
INSERT [dbo].[MachineSettingsData] ([MachineId], [LastRecordAttendance], [UserId], [FlagExceedXsecondOrNot], [LastDatePunch], [BranchCode]) VALUES (1, 20190409015059, N'Admin', 1, CAST(N'2019-04-01 00:00:00.000' AS DateTime), 1)
INSERT [dbo].[MachineSettingsData] ([MachineId], [LastRecordAttendance], [UserId], [FlagExceedXsecondOrNot], [LastDatePunch], [BranchCode]) VALUES (2, 20190407151959, N'Admin', 1, CAST(N'2019-04-01 00:00:00.000' AS DateTime), 1)



CREATE TABLE [dbo].[acc_monitor_log](
[id] [int] IDENTITY(1,1) NOT NULL,
[time] [datetime] NULL,
[pin] [nvarchar](50) NULL,
[card_no] [nvarchar](50) NULL,
[device_id] [int] NULL,

PRIMARY KEY CLUSTERED
(
[id] ASC ,
[time]ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
INSERT [dbo].[acc_monitor_log] ([id], [time], [pin], [card_no], [device_id], [device_sn], [device_name], [verified], [state], [event_type], [description], [event_point_type], [event_point_id], [event_point_name]) VALUES (72, CAST(N'2019-04-01 15:11:28.000' AS DateTime), N'97', N'', 1, NULL, N'Gate1', 0, 10, 0, N'', 0, 1, N'Gate1-1')
INSERT [dbo].[acc_monitor_log] ([id], [time], [pin], [card_no], [device_id], [device_sn], [device_name], [verified], [state], [event_type], [description], [event_point_type], [event_point_id], [event_point_name]) VALUES (73, CAST(N'2019-04-01 15:13:32.000' AS DateTime), N'97', N'', 1, NULL, N'Gate1', 0, 10, 0, N'', 0, 1, N'Gate1-1')

CREATE VIEW [dbo].[ViewGetPunchEmployeeM1]
AS
SELECT ZKAccess.dbo.acc_monitor_log.id, ZKAccess.dbo.acc_monitor_log.device_id, CONVERT(VARCHAR(30), ZKAccess.dbo.acc_monitor_log.time, 112)
+ REPLACE(CONVERT(VARCHAR(30), ZKAccess.dbo.acc_monitor_log.time, 108), ':', '') AS time, ZKAccess.dbo.USERINFO.Badgenumber,
ZKAccess.dbo.USERINFO.CardNo
FROM ZKAccess.dbo.acc_monitor_log LEFT OUTER JOIN
ZKAccess.dbo.USERINFO ON ZKAccess.dbo.USERINFO.USERID = ZKAccess.dbo.acc_monitor_log.pin
WHERE (ZKAccess.dbo.acc_monitor_log.time =
(SELECT TOP (1) time
FROM ZKAccess.dbo.acc_monitor_log AS acc_monitor_log_1
WHERE (device_id = 1) AND (ZKAccess.dbo.USERINFO.CardNo IS NOT NULL OR
ZKAccess.dbo.USERINFO.CardNo <> '') AND (ZKAccess.dbo.USERINFO.Badgenumber IS NOT NULL OR
ZKAccess.dbo.USERINFO.Badgenumber <> '')
ORDER BY time DESC))

public long GetLastrecordpuncheformchine1(int MachineId)
{
long PunchTime = 0;
string sqlGetLast = @"select LastRecordAttendance from MachineSettingsData where BranchCode= " + GlobalVariables.GlobalCriteria["BranchCode"] + " AND MachineId = " + MachineId + "";
PunchTime = Utilities.ObjectConverter.ConvertToLongInteger(DataAccess.ExecuteScalar(sqlGetLast));
return PunchTime;
}
public DataTable GetLastPunchEmployee(int MachineId)
{
string sql = "";

sql = "select * from ViewGetPunchEmployeeM1 ";


DataTable dtgetpunch = DataAccess.ExecuteDataTable(sql);
return dtgetpunch;
}

Continue reading...