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

  • Question

  • 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;
            }


    Saturday, June 1, 2019 8:25 AM

Answers

  • Hi 

    Thank you for posting here.

    I want to know what your current project is. Because I note that you used Utilities.ObjectConverter method, it seems that something in entity framework.

    If your question is more related to entity framework, I suggest that you could post in the entity framework forum. If not, please provide more code with me so that we could solve your problem better.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, June 3, 2019 6:06 AM
    Moderator

All replies