Company: Link Technologies
Case No: L12789. Project: 14.20: LinkSOFT Version 14.20 - November 2022
Logged By: Vineshwar Prasad (Edge Business Solutions) on behalf of Sanjay (Link Technologies) on 27 Sep 2022 01:54PM
Priority: Medium
Product: Payroll & HR
Group: Enhancement
Time Taken: 92.50 (Weight: 120.00)
Assigned To: Development
Circulation: Development, Sanjay, Sitla Sharma, Vineshwar Prasad
Resolve By: Monday, 31 October 2022 03:15 PM [569 days since logged date]
Status: Closed
Subject: Changes to Table Names in ZKTeco Software version 8.0.7
Summary:    The Table Names in ZKTeco Software version 8.0.7 has changed. LinkSOFT TNA Import process was using a different Table Name in prior versions.

Version 5.0
  1. Table Name to store Check In and Out - CHECKINOUT
Version 8.0.7
  1. Table Name to store Check In and Out - iclock_transaction
Audit Notes:Edited by sanjay on 28/10/22 11:24. Edited by sanjay on 18/10/22 11:37. Edited by sanjay on 13/10/22 16:08. Edited by sanjay on 13/10/22 10:52. Edited by sanjay on 11/10/22 11:46. 
26 Aug 202202:50PM Comment 1 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 30-08-2022 02:09 PM Time Taken: 3.00

Hi Vineshwar,

Can you give me the following information from the vendors:

  1. V8 Table name for USERINFO
  2. Which field is the same as CHECKINOUT.Processed?
  3. In table "CHECKINOUT", is UNPROCESSED records still marked as "1" and Processed Marked as "2"?
  4. What are the values that define: IN, Break1IN, Break2IN, OvertimeIN, OUT? In version 5, it was: ClockType. IN = 1, Break1In = 2, Break2IN = 5, OvertimeIN = 7, OUT = 3
  5. Here is the query we used to get data from Version 5. Can you translate this to Version 8


    SELECT

      u.BADGENUMBER

     ,c.CHECKTIME

     ,'' --ClockType. IN = 1, Break1In = 2, Break2IN = 5, OvertimeIN = 7, OUT = 3

     ,CONVERT(VARCHAR(50),c.USERID) +  'BadgeNumber: ' +  ISNULL(u.BADGENUMBER,'')  + 'CheckTime: ' +  CONVERT(VARCHAR(50),c.CHECKTIME,102)  +   CONVERT(VARCHAR(50),c.CHECKTIME,108)   

     ,0

     ,CONVERT(VARCHAR(50),c.USERID)  + ' ' + CONVERT(VARCHAR(50),c.CHECKTIME,102) + ' ' +  CONVERT(VARCHAR(50),c.CHECKTIME,108)  

     from   dbo.CHECKINOUT c

     JOIN   dbo.USERINFO u ON c.USERID = u.USERID



26 Aug 202202:57PM Comment 2 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 30-08-2022 02:50 PM Time Taken: 2.00

We have installed V8 on QA2 and setup data on QA4. DB Name: ZKTecoSoftware807.

Can you create sample data for:

  1. Time IN, OUT
  2. Time IN, Break1IN, OUT
  3. Time IN, Break2IN, OUT
  4. Break1IN, Break2IN
  5. and some other variations for us to test

06 Sep 202205:41PM Comment 3 by Vineshwar Prasad (Edge Business Solutions) Assigned To: Development Followup Date: 07-09-2022 05:35 PM Time Taken: 2.00
---- Email from Vineshwar Prasad - Tue, Aug 30, 10:55 AM (7 days ago) to edison.wang [edison.wang@zkteco.com] -----

Hi Kenny,

Greetings from Fiji Islands. Hope you are doing well.

As per our Skype conversation, we would like to request the following information for our developers to do integrations changes to support ZKBiotime8 from ZKTime5;
  1. V8 Table name for USERINFO
  2. Which field is the same as CHECKINOUT.Processed?
  3. In table "CHECKINOUT", is UNPROCESSED records still marked as "1" and Processed Marked as "2"?
  4. What are the values that define: IN, Break1IN, Break2IN, OvertimeIN, OUT? In version 5, it was: ClockType. IN = 1, Break1In = 2, Break2IN = 5, OvertimeIN = 7, OUT = 3
  5. Here is the query we used to get data from Version 5. Can you translate this to Version 8
    SELECT
    u.BADGENUMBER
    ,c.CHECKTIME
    ,'' --ClockType. IN = 1, Break1In = 2, Break2IN = 5, OvertimeIN = 7, OUT = 3
    ,CONVERT(VARCHAR(50),c.USERID) + 'BadgeNumber: ' + ISNULL(u.BADGENUMBER,'') + 'CheckTime: ' + CONVERT(VARCHAR(50),c.CHECKTIME,102) + CONVERT(VARCHAR(50),c.CHECKTIME,108)
    ,0
    ,CONVERT(VARCHAR(50),c.USERID) + ' ' + CONVERT(VARCHAR(50),c.CHECKTIME,102) + ' ' + CONVERT(VARCHAR(50),c.CHECKTIME,108)
    from dbo.CHECKINOUT c
    JOIN dbo.USERINFO u ON c.USERID = u.USERID
Let us know if you need any clarifications.

Regards
Vineshwar Prasad

06 Sep 202205:43PM Comment 4 by Vineshwar Prasad (Edge Business Solutions) Assigned To: Vineshwar Prasad (Edge Business Solutions) Followup Date: 10-09-2022 05:41 PM Notes: ETC extended from: 18/08/2022 to 10/09/2022
No response received from ZKTeco to date

10 Sep 202205:13PM Comment 5 by Vineshwar Prasad (Edge Business Solutions) Assigned To: Vineshwar Prasad (Edge Business Solutions) Followup Date: 16-09-2022 05:04 PM Time Taken: 1.00 Notes: ETC extended from: 10/09/2022 to 16/09/2022
----- Response from Zack [zack.li@zkteco.com] via Skype -----

1. In Biotime8, the table name for USERINFO is personnel_employee
2&3; The value of PROCESSED doesn't have any actual meaning, it is just a default value, all the attendance transaction is stored at iclock_transaction in Biotime8
4. You can edit the value in the software BioTime8

5. I can't help you to develop the code

If you have any problems, you could ask me, thanks

10 Sep 202205:28PM Comment 6 by Vineshwar Prasad (Edge Business Solutions) Assigned To: Vineshwar Prasad (Edge Business Solutions) Followup Date: 16-09-2022 05:13 PM Time Taken: 1.00 Notes: ETC extended from: 16/09/2022 to 16/09/2022
Sample Data has been Uploaded for testing;
  1. ZKTeco Software installed on QA2
  2. Database Setup in QA4 [ZKTecoSoftware805]
  3. User Details on Table [personnel_employee]
  4. TNA Transactions in Table [iclock_transaction]
  5. Sample uploaded for User [emp_code = '2425']

13 Sep 202202:06PM Comment 7 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 14-09-2022 02:02 PM Time Taken: 1.00

Meeting with Sanjay and Vineshwar:

  1. Add 10 entries. for Day 1, 2 transactions per "Punch Stage"
  2. Add an example of an employee over a 1 week period.
  3. Transfer case to development.

27 Sep 202201:53PM Comment 8 by Vineshwar Prasad (Edge Business Solutions) Assigned To: Vineshwar Prasad (Edge Business Solutions) Followup Date: 28-09-2022 01:40 PM Time Taken: 1.00 Notes: ETC extended from: 16/09/2022 to 28/09/2022
Sample Data has been Uploaded for testing;
  1. ZKTeco Software installed on QA2
  2. Database Setup in QA4 [ZKTecoSoftware805]
  3. User Details on Table [personnel_employee]
  4. TNA Transactions in Table [iclock_transaction]
  5. Sample uploaded for User [emp_code = '2425']
  6. Transaction Dates: 05/09/2022 to 09/09/2022

27 Sep 202201:56PM Comment 9 by Vineshwar Prasad (Edge Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 30-09-2022 01:55 PM Notes: ETC extended from: 28/09/2022 to 30/09/2022
Case transferred to development

06 Oct 202201:23PM Comment 10 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 13-10-2022 01:21 PM Time Taken: 0.50 Notes: ETC extended from: 30/09/2022 to 13/10/2022
Thanks Vineshwar, can we dioscuss this when you have a moment so we can finalise the setup and I can add this to 14.20?. Send a meeting request for any day 10 am aest next week.

11 Oct 202211:46AM Comment 11 by Sanjay (Link Technologies) Case L12789 added to project 14.20
13 Oct 202210:52AM Comment 12 by Sanjay (Link Technologies) Case L12789 added to project Review
13 Oct 202204:08PM Comment 13 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 31-10-2022 03:15 PM Time Taken: 2.00 Notes: ETC extended from: 13/10/2022 to 31/10/2022

Meeting Vineshwar and Sanjay

Notes:

  1. Agreed that the states in ZKTeco will be defined as INBreak1INBreak2INOvertimeINOUT? In version 5, it was: ClockType. IN = 1, Break1In = 2, Break2IN = 5, OvertimeIN = 7, OUT = 3. We will use the LinkSOFT INTEGRATION Configuration for TNA devices to allocate the states.
  2. The two tables to use are as follows:
    1. personnel_employee - Agreed to use Primary Key as "Employee Code". Department ID is not used and is "system assigned"
    2. iclock_transaction - Transaction table linking to 1. above using Employee Code.
  3. Multi-company is not supported, however, we can use "iclock_transaction.TerminalAlias" as the Company code from LinkSOFT.
  4. What does Area Code/Department mean?
  5. When extracting data, we used to mark transactions as "Processed" in the external system. For this version, we will use the "iclock_transaction.ID" (Identity Column) field to mark the last transaction imported. This value can be stored in the "Integration" settings.

Next step:

  1. Vineshwar to get a trial licence extended so we can perform more tests
  2. Vineshwar to get an explanation on how to set up Multi-Company. I.e. Where can we place a company code from Linksoft? Propose using "iclock_transaction.TerminalAlias"
  3. Development can start work while waiting for the Licence

Figure 1 - SQL script to align with comment 1

SELECT e.emp_code, t.punch_time

 ,t.punch_state

 ,'Employee Number: ' + ISNULL(e.emp_code,'') + 'CheckTime: ' + CONVERT(VARCHAR(50),t.punch_time,102) + CONVERT(VARCHAR(50),t.punch_time,108)

FROM personnel_employee e

JOIN iclock_transaction t ON t.emp_code = e.emp_code


13 Oct 202204:08PM Comment 14 by Sanjay (Link Technologies) Case L12789 added to project 14.20
20 Oct 202203:54PM Comment 15 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 27-10-2022 02:48 PM Time Taken: 68.00
PART A - Development work for this case has been completed.

1. The change will be available in version:14.20

2. The following changes were made(Include Database object names, Program classes, and any other relevant information):

  1. New configuration in menu "Integration ~> TI004 ~> Timesheet.SoftwareVersion". Valid options are 5 and 8
  2. Changed structure to handle version 8 data import

3. Affected Areas:

  1. Timesheet Import for version 8 of ZKTeco software

4. The issue was caused by:

  1. New version of API for ZKTeck Time and Attendance

5. Other Relevant Notes
6. Next Step
(Review and System Test (Developer) -> UAT (Quality) -> Documentation): UAT


20 Oct 202204:25PM Comment 16 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 27-10-2022 04:25 PM Time Taken: 0.50
Hi Vineshwar, I have completed changes to Case L12789 - ZKTeco Software version 8.0.7

Can we allocate some time to test this tomorrow? let me know when you are available.

20 Oct 202205:26PM Comment 17 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 27-10-2022 05:25 PM Time Taken: 0.50
*** Skype discussion
Vineshwar, 4:57 PM
Yes, I have a training finishing at 12 PM FJT tomorrow. We can test after that.

5:24 PM
ok great. You can access 14.20 on http://10.0.0.14/linksoft username admin. Set this up to work with your ZKTeco database and we can connect at 1 pm aest after your lunch

24 Oct 202202:52PM Comment 18 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 27-10-2022 02:51 PM Time Taken: 4.00
Added an APIStartDate configuration to the TNADevice Integration to allow users to define a start date for TNA transactions. Default value '2022/12/31'

24 Oct 202202:57PM Comment 19 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 27-10-2022 02:52 PM Time Taken: 2.00

Hi Vineshwar, please complete the following tests on TNA for the ZKTech device changes you have requested

  1. Import data into LinkSOFT
  2. DELETE TNA data and reimport
  3. Enable Rules for TNA processing
  4. Submit Processed data

Note that we need to complete this before COB Wednesday 26th October as the release date for 14.2 is 1st November. Please let me know if this cannot be achieved.


24 Oct 202202:59PM Comment 20 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 27-10-2022 02:57 PM Time Taken: 1.00 Notes: Edited by sanjay on 24/10/22 15:02. 
*** Skype message ***
Hi Sitla, case L12789 was a change requested by EDGE. I know you guys are very busy, but I need this change to be verified as my guys have spent a fair bit of time on this and we are approaching the 14.2 release date of 1st Nov. Can you discuss this case in your next meeting? thanks.

31 Oct 202204:31PM Comment 21 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 03-11-2022 04:29 PM Time Taken: 3.00
The following tests were performed:

Test Setup Instructions:
  1. Setup ZKTECO V5 and perform tests from Table 1
  2. Setup ZKTECO V8 and perform tests from Table 1
Table 1 - Test Results
NoTest CaseExpected ResultPass/FailComments
1Create entries in TNA Device for V5
Import should pull data into LinkSOFT based on V 5 configuration
Pass

2Create entries in TNA Device for V8
Import should pull data into LinkSOFT based on V 5 configuration
Pass

Environment Details

  1. OS version: Win11
  2. Application version: 14.20
  3. Setup on:
    1. Server: LinkQA4
    2. Database: LinkSOFT
    3. LinkSOFT URL: HTTP://LinkQA4/LinkSOFT
  4. Login Details: Standard username and password for user "admin"

Next Step: Closure


If you have any queries regarding this support incident, please email admin@linktechnologies.com.au and include the Case No: L12789 in the subject line of all emails regarding this issue.

Document size: 26.1 KB
For call complaints, please contact the Managing Director of the company using this form