Import Data: Difference between revisions

From eStream Software
 
(112 intermediate revisions by the same user not shown)
Line 6: Line 6:
*[[AutoCount Payroll]]
*[[AutoCount Payroll]]
===Standard Import===
===Standard Import===
*[[Others|ELID]]
{| class="wikitable" style="margin: 1em auto 1em auto;"
*[[Others|SynGlobal Time Attendance]]
|-
*[[Others|FingerTec]]
! System !! Contact Info
*[[Others|ANVIZ]]
|-
*[[Others|Click]]
| [[Others|ELID]] || [https://sites.google.com/site/sqlestream/sql-payroll/7-time-attendance/70-2-elid Elid Sdn Bhd]
|-
| [[Others|SynGlobal Time Attendance]] || [https://sites.google.com/site/sqlestream/sql-payroll/7-time-attendance/7-01-synglobal-attendance SynGlobal Technologies Sdn Bhd]
|-
| [[Others|FingerTec]] || [https://sites.google.com/site/sqlestream/sql-payroll/7-time-attendance/7-03-fingertec TimeTec Computing Sdn Bhd]
|-
| [[Others|ANVIZ]] || [https://sites.google.com/site/sqlestream/sql-payroll/7-time-attendance/7-04-anviz Hi-Profile Achievement (M) Sdn Bhd]
|-
| [[Others|Click Time Attendance]] || [https://sites.google.com/site/sqlestream/sql-payroll/7-time-attendance/7-05-click-tms EGroup Solution Sdn Bhd]
|-
| [[Others|Nexpro TimeTracX]] ||  [https://sites.google.com/site/sqlestream/sql-payroll/7-time-attendance/7-06-timetracx Nexus Professional Sdn Bhd]
|-
| [[Others|Check Time]] || [https://sites.google.com/site/sqlestream/sql-payroll/7-time-attendance/7-07-checktime MVIT Solution]
|-
| [[Others|ECOTimePrint]] || [http://www.ecotimeprint.com Eco Solution Technology]
|-
| [[Others|Invotime]] || [https://invocore.com.my/time-and-attendance Invocore Sdn Bhd]
|}


==SQL Payroll Program==
==SQL Payroll Program==
May get it from [http://www.estream.com.my/downloadfile/Fairy/sqlpay.x86.1.2017.133.123.exe here]<br />
May get it from [https://www.sql.com.my/download/demo/sqlpay-setup.exe here]<br />
MD5 : 9815E0A416F0BE80B1986B5689392A36


==Modules Require==
==Modules Require==
Line 21: Line 37:


==Guideline & Example==
==Guideline & Example==
:01. This guide is all import except [[UBS Payroll]] & [[AutoCount Payroll]]
: There are 3 type of import
:02. Just copy below example & paste to notepad & save it as CSV
:* Single File Import
:* <del>Batch File Import (Available in Version 1.2019.167.146 & above)</del>
:* Command Line Sync (Available in Version 1.2020.186.160 & above)


===Overtime & Wages===
===Single File Import===
:This guide is all import except [[UBS Payroll]] & [[AutoCount Payroll]]
:Just copy below example & paste to notepad & save it as CSV
Pros
# Column Field name can be free format
# No need to Hash File
Cons
# Need Manually Mapping the Column
# Manually Import individually
# Not secure cause user can change the file before import
 
====Overtime & Wages====
{| style="margin: 1em auto 1em auto;"
{| style="margin: 1em auto 1em auto;"
|-
|-
|  
|  
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 1 - OT  or Wages
! Example 1 - OT  or Wages (not applicable for version 192 & above)
|-
|-
|  
|  
Line 62: Line 91:
| align="center" |'''Remarks'''
| align="center" |'''Remarks'''
|-
|-
| Date || Date || align="center" |0 || In dd/mm/yyyy or dd-mm-yyyy format
| Date || Date || align="center" |0 || In dd/mm/yyyy format
|-
|-
| Employee || String || align="center" |30 || Employee Code/ID
| Employee || String || align="center" |30 || Employee Code/ID
Line 70: Line 99:
| Unit || FMTBcd|| align="center" |8 || Time in rate eg 3 hr 15 min = 3.25 (3 + (15/60))
| Unit || FMTBcd|| align="center" |8 || Time in rate eg 3 hr 15 min = 3.25 (3 + (15/60))
|-
|-
| UnitAsTime || Time || align="center" |0 || In HH:MM format
| UnitAsTime || Time || align="center" |0 ||  
* In HH:MM format  
* not applicable for version 192 & above
|}
|}


===Leave===
<div style="float: right;">  [[#top|[top]]]</div>
 
====Leave====
{| style="margin: 1em auto 1em auto;"
{| style="margin: 1em auto 1em auto;"
|-
|-
Line 112: Line 145:
| align="center" |'''Remarks'''
| align="center" |'''Remarks'''
|-
|-
| Date || Date || align="center" |0 || In dd/mm/yyyy or dd-mm-yyyy format
| Date || Date || align="center" |0 || In dd/mm/yyyy format
|-
|-
| Employee || String || align="center" |30 || Employee Code/ID
| Employee || String || align="center" |30 || Employee Code/ID
Line 136: Line 169:
|}
|}


===Employee===
<div style="float: right;">  [[#top|[top]]]</div>
 
====Employee====
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 1 - Employee   
! Example 1 - Employee   
|-
|-
|  
|  
"Code";"Name";"DOB";"Gender";"Salary"<br />
"Code";"Name";"DOB";"Gender";"Salary";"Resident";"EISTYPE";"EISCATEGORY"<br />
"0018";"Shiro Emiya";"14/06/1980";"M";"1200"<br />
"0018";"Shiro Emiya";"14/06/1980";"M";"1200";"F";"1";"0"<br />
"0019";"Nanoha Takamachi";"01/10/1995";"F";"2000"<br />
"0019";"Nanoha Takamachi";"01/10/1995";"F";"2000";"F";"0";"1"<br />
"0020";"Belldandy";"01/09/1977";"F";"2500"<br />
"0020";"Belldandy";"01/09/1977";"F";"2500";"T";"1";"0"<br />
|}
|}


Line 175: Line 210:
* 2 - Second Category
* 2 - Second Category
* 0 - No Socso
* 0 - No Socso
|-
| EIS Type || 
* 0 - No EIS
* 1 - Has EIS
|-
| EIS Category || 
* 1 - Foreign workers
* 2 - Public sector
* 3 - Domestic servant
* 4 - Age 57 to 59 (never contribute EIS before)
* 5 - Employer with no employee
* 6 - Self employed/sole proprietors
* 7 - Spouse of employer
|-
|-
| Bank Code||  Is Bank SWIFT Code
| Bank Code||  Is Bank SWIFT Code
Line 200: Line 248:
|}
|}


===Allowance & Deduction===
<div style="float: right;">  [[#top|[top]]]</div>
====Allowance & Deduction====
{| style="margin: 1em auto 1em auto;"
|-
|
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example - Allowance
|-
|
"Date","Employee","Code","Unit"<br />
26/6/2017,A0001,HP,4<br />
28/6/2017,A0001,FUEL,9.63<br />
26/6/2017,A0002,HP,6.30<br />
26/6/2017,A0003,FUEL,4.92<br />
26/6/2017,A0004,HP,1<br />
|}
 
||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example - Deduction   
! Example - Deduction   
Line 211: Line 276:
29/6/2017,A0002,LATE,3.37<br />
29/6/2017,A0002,LATE,3.37<br />
26/6/2017,A0003,LATE,4.92<br />
26/6/2017,A0003,LATE,4.92<br />
|}
|}
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Table Fields Properties
|-
| align="center" |'''Field Name'''
| align="center" |'''Field Type'''
| align="center" |'''Field Size'''
| align="center" |'''Remarks'''
|-
| Date || Date || align="center" |0 || In dd/mm/yyyy format
|-
| Employee || String || align="center" |30 || Employee Code/ID
|-
| Code || String || align="center" |20 || Allowance or Deduction Code
|-
| Unit || FMTBcd|| align="center" |8 || Qty or Unit
|}
<div style="float: right;">  [[#top|[top]]]</div>
====Claim====
{| style="margin: 1em auto 1em auto;"
|-
|
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 1 - Claim
|-
|
"TransDate","PostDate","Employee","Code","Amount"<br />
26/6/2017,26/6/2017,A0001,TOLL,4<br />
28/6/2017,28/6/2017,A0001,PARKING,9.63<br />
26/6/2017,26/6/2017,A0002,TOLL,6.30<br />
26/6/2017,26/6/2017,A0003,PARKING,4.92<br />
26/6/2017,26/6/2017,A0004,TOLL,1<br />
|}
||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 2 - Claim
|-
|
"TransDate","PostDate","Employee","Code","Description","Ref1","REF2","Amount"<br />
26/6/2017,26/6/2017,A0001,PARKING,"MID VALLEY - CUSTOMER AB","IV-01243","1235481377828",4<br />
28/6/2017,28/6/2017,A0001,PARKING,"MID VALLEY - CUSTOMER AB","IV-01243","2584986928248",9.65<br />
26/6/2017,26/6/2017,A0002,TOLL,"TOLL CLAIM","","",6.30<br />
|}
|}
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Table Fields Properties
|-
| align="center" |'''Field Name'''
| align="center" |'''Field Type'''
| align="center" |'''Field Size'''
| align="center" |'''Remarks'''
|-
| TransDate || Date || align="center" |0 || In dd/mm/yyyy format <br>
Date of transaction <br>
Default TransDate is same as PostDate
|-
| PostDate || Date || align="center" |0 || In dd/mm/yyyy format <br>
Date of Process Date<br>
Default PostDate is same as TransDate
|-
| Employee || String || align="center" |30 || Employee Code/ID
|-
| Code || String || align="center" |20 || Claim Code
|-
| Description || String || align="center" |100 || Claim Description (Optional)
|-
| Ref1 || String || align="center" |20 || Claim Reference 1 (Optional)
|-
| Ref2 || String || align="center" |20 || Claim Reference 2 (Optional)
|-
| Amount || FMTBcd|| align="center" |8 || Claim Amount
|}
<div style="float: right;">  [[#top|[top]]]</div>
===Batch File Import(Deprecated)===
: This function only Available in Version 1.2019.167.146 & above.
: All file should be in CSV also but need to be in 1 zip file.
: In Zip should had At least 2 file
:* Transaction File (eg pending.overtime.csv)
:* Hash File (eg pending.overtime.hash)
: Zip File Name can be any name you like
Pros
# 1 Button to import all the files
# No need to manually mapping the column
# Secure from changes by user cause file is Hash
Cons
# Need follow the column name given
====Overtime & Wages====
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Transaction Type !! File Name
|-
| Wages ||
* pending.wages.csv
* pending.wages.hash
|-
| Overtime ||
* pending.overtime.csv
* pending.overtime.hash
|}
{| style="margin: 1em auto 1em auto;"
|-
|
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 1 - OT  or Wages
|-
|
TransDate,PostDate,Employee,Code,WorkUnitAsTime<br />
26/6/2015,26/6/2015,A001,DR01,04:00<br />
28/6/2015,28/6/2015,A001,DR02,09:38<br />
26/6/2015,26/6/2015,A002,DR02,06:18<br />
29/6/2015,29/6/2015,A002,DR03,03:22<br />
26/6/2015,26/6/2015,A003,DR03,04:55<br />
|}
||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 2 - OT  or Wages
|-
|
"TransDate","PostDate","Employee","Code","WorkUnit"<br />
26/6/2015,26/6/2015,A001,DR01,4<br />
28/6/2015,28/6/2015,A001,DR02,9.63<br />
26/6/2015,26/6/2015,A002,DR02,6.30<br />
29/6/2015,29/6/2015,A002,DR03,3.37<br />
26/6/2015,26/6/2015,A003,DR03,4.92<br />
|}
|}
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Table Fields Properties
|-
| align="center" |'''Field Name'''
| align="center" |'''Field Type'''
| align="center" |'''Field Size'''
| align="center" |'''Remarks'''
|-
| TransDate || Date || align="center" |0 || In dd/mm/yyyy or dd-mm-yyyy format <br>
Date of transaction
|-
| PostDate || Date || align="center" |0 || In dd/mm/yyyy or dd-mm-yyyy format <br>
Date of Process Date
|-
| Employee || String || align="center" |30 || Employee Code/ID
|-
| Code || String || align="center" |20 || Overtime or Wages Code
|-
| WorkUnit || FMTBcd|| align="center" |8 || Time in rate eg 3 hr 15 min = 3.25 (3 + (15/60))
|-
| WorkUnitAsTime || Time || align="center" |0 || In HH:MM format
|}
<div style="float: right;">  [[#top|[top]]]</div>
====Leave====
File Name
* leave.application.csv
* leave.application.hash
{| style="margin: 1em auto 1em auto;"
|-
|
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 1 - Leave 
|-
|
LeaveDate,Code,LeaveType,Description,LeaveDayAsTime<br />
08/11/2015,001,UL,Sick,08:00<br />
19/11/2015,003,UL,Fever,09:20<br />
15/11/2015,005,UL,Sick,08:00<br />
01/11/2015,001,AL,Holiday To Melaka,08:00<br />
03/11/2015,003,AL,Holiday To Penang,08:00<br />
08/11/2015,005,MC,High Fever,08:00<br />
|}
||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 2 - Leave 
|-
|
LeaveDate,Code,LeaveType,Description,LeaveDay<br />
08/11/2015,001,UL,Sick,1<br />
19/11/2015,003,UL,Fever,1.17<br />
15/11/2015,005,UL,Sick,1<br />
01/11/2015,001,AL,Holiday To Melaka,1<br />
03/11/2015,003,AL,Holiday to Penang,1<br />
08/11/2015,005,MC,High Fever,1<br />
|}
|}
|}


Line 222: Line 485:
|-
|-
| Date || Date || align="center" |0 || In dd/mm/yyyy or dd-mm-yyyy format
| Date || Date || align="center" |0 || In dd/mm/yyyy or dd-mm-yyyy format
|-
| Code || String || align="center" |30 || Employee Code/ID
|-
| LeaveType|| String || align="center" |20 || Leave Code (Eg AL, MC, etc)
|-
| Description|| String || align="center" |80 || Leave Description
|-
| LeaveDay || FMTBcd|| align="center" |8 || Day in rate eg 9 hr 20 min for 8 hr per day = 1.17 (((9+(20/60))/24)*(24/8))
|-
| LeaveDayAsTime || Time || align="center" |0 || In HH:MM format
|}
{| style="margin: 1em auto 1em auto;"
|-
|
----------------------------------------------------------------------------------------------------------------------------------------------------
{|
|-
| [[File:Template.Tips-01.jpg|60px]]|| It adviceable to use '''LeaveDay''' instead of '''LeaveDayAsTime''' as
:* '''LeaveDayAsTime''' max value can enter is '''23:59''' only
:* if you Export as Grand Total
|}
----------------------------------------------------------------------------------------------------------------------------------------------------
|}
<div style="float: right;">  [[#top|[top]]]</div>
====Allowance & Deduction====
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Transaction Type !! File Name
|-
| Allowance ||
* pending.allowance.csv
* pending.allowance.hash
|-
| Deduction ||
* pending.deduction.csv
* pending.deduction.hash
|}
{| style="margin: 1em auto 1em auto;"
|-
|
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example - Allowance
|-
|
"TransDate","PostDate","Employee","Code","WorkUnit"<br />
26/6/2017,26/6/2017,A0001,HP,4<br />
28/6/2017,28/6/2017,A0001,FUEL,9.63<br />
26/6/2017,26/6/2017,A0002,HP,6.30<br />
26/6/2017,26/6/2017,A0003,FUEL,4.92<br />
26/6/2017,26/6/2017,A0004,HP,1<br />
|}
||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example - Deduction 
|-
|
"TransDate","PostDate","Employee","Code","WorkUnit"<br />
26/6/2017,26/6/2017,A0001,LATE,4<br />
28/6/2017,28/6/2017,A0001,LATE,9.63<br />
26/6/2017,26/6/2017,A0002,LATE,6.30<br />
29/6/2017,29/6/2017,A0002,LATE,3.37<br />
26/6/2017,26/6/2017,A0003,LATE,4.92<br />
|}
|}
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Table Fields Properties
|-
| align="center" |'''Field Name'''
| align="center" |'''Field Type'''
| align="center" |'''Field Size'''
| align="center" |'''Remarks'''
|-
| TransDate || Date || align="center" |0 || In dd/mm/yyyy or dd-mm-yyyy format <br>
Date of transaction <br>
Default TransDate is same as PostDate
|-
| PostDate || Date || align="center" |0 || In dd/mm/yyyy or dd-mm-yyyy format <br>
Date of Process Date<br>
Default PostDate is same as TransDate
|-
|-
| Employee || String || align="center" |30 || Employee Code/ID
| Employee || String || align="center" |30 || Employee Code/ID
|-
|-
| Code || String || align="center" |20 || Deduction Code
| Code || String || align="center" |20 || Allowance or Deduction Code
|-
| WorkUnit || FMTBcd|| align="center" |8 || Qty or Unit
|}
 
<div style="float: right;">  [[#top|[top]]]</div>
====File Hash====
: The Hash is using MD5
=====Steps=====
:01. File Hash : Hash MD5 the whole file
:02. File String Hash : Hash MD5 again using the result shown in MD5 in Step 1.
:03. Save the result in Step 2 in .hash file
Example Using the Allowance Information as above
:01. Result from File Hash : '''e83045758c13cb7c17ba8f2150919f36'''
:02. Result from String Hash from Step 1 : <span style="color:#0000ff">1b324dad30a84f322c23a1cdec481cc1</span>
:03. Save the Result in Step 2 in pending.allowance.hash file
 
<div style="float: right;">  [[#top|[top]]]</div>
 
===Command Line Sync===
: This function only Available in Version 1.2020.186.160 & above.
: This function can import and export file from/to SQL Payroll
 
====Setting In SQL Payroll====
:An access token is required when the third party application calls data provider and data import commands.
[[File:SQLPayImp-02.jpg|center]]
:01. Click Tools | Maintain User
:02. Double Click ADMIN user
:03. Click More | Generate Access Token
:04. Enter the ADMIN password
:05. Save the Token File
 
====Data Provider (Export)====
: This command is to export data from SQL Payroll to Json file
: [https://www.estream.com.my/downloadfile/Fairy/Example-SQLPay-Json.zip Sample Output - 20200914]
[[File:SQLPayImp-01.jpg|center]]
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "<id>" [-<param name> "<param value>"] -out "<json file>"
</pre>
 
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Command !! Description
|-
| [SQL Payroll bin folder Path] ||
* Folder path where SQL Payroll Install
* Eg C:\estream\SQL Payroll\bin
|-
| -token || A Code where generate
|-
| -dataprovider || Indicator To export Data from SQL Payroll
|-
| -id || Indicator type of Data to export
|-
| [-<param name> "<param value>"] || Additional parameter for some id type
|-
| -out || Json file folder & name to save to
|}
<div style="float: right;">  [[#top|[top]]]</div>
=====Maintain Employee=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "employee" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "employee" -out "c:\Users\Star\Desktop\employee.json"
</pre>
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Branch=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "branch" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "branch" -out "c:\Users\Star\Desktop\branch.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Department=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "department" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "department" -out "c:\Users\Star\Desktop\department.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain HR Group=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "hrgroup" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "hrgroup" -out "c:\Users\Star\Desktop\hrgroup.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Category=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "category" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "category" -out "c:\Users\Star\Desktop\category.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Project=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "project" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "project" -out "c:\Users\Star\Desktop\project.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Job=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "job" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "job" -out "c:\Users\Star\Desktop\job.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Task=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "task" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "task" -out "c:\Users\Star\Desktop\task.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Leave Type=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "leavetype" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "leavetype" -out "c:\Users\Star\Desktop\leavetype.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Leave Group=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "leavegroup" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "leavegroup" -out "c:\Users\Star\Desktop\leavegroup.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Leave Application=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "leave" -datefrom "<yyyy-mm-dd>" -dateto "<yyyy-mm-dd>" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "leave" -datefrom "2020-01-01" -dateto "2020-12-31" -out "c:\Users\Star\Desktop\leave.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Wages=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "wages" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "wages" -out "c:\Users\Star\Desktop\wages.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Allowance=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "allowance" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "allowance" -out "c:\Users\Star\Desktop\allowance.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Overtime=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "overtime" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "overtime" -out "c:\Users\Star\Desktop\overtime.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Commission=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "commission" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "commission" -out "c:\Users\Star\Desktop\commission.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Claim=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "claim" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "claim" -out "c:\Users\Star\Desktop\claim.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Maintain Deduction=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "deduction" -out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "deduction" -out "c:\Users\Star\Desktop\deduction.json"
</pre>
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====PaySheet=====
Command
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "paysheet" -year "<ProcessYear>" -month "<ProcessMonth>"-out "<json file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "paysheet" -year "2019" -month "1" -out "c:\Users\Star\Desktop\paysheet.json"
</pre>
 
 
<div style="float: right;">  [[#top|[top]]]</div>
 
====Data Import====
: This command is to import data to SQL Payroll
<pre>
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataimport -in "<zip file>"
</pre>
Example
<pre>
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataimport -in "c:\Users\Star\Desktop\output.zip"
</pre>
:Each ZIP file can contains one or more data file, each data file is a JSON object that has id and data fields.
<pre>
{
  "id":"",
  "data":[
  ]
}
</pre>
 
=====Leave Application=====
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
!Fields Properties
|-
! Field Name !! Type !! Size !! (M)andatory/(O)ptional !! Description
|-
| code || String || 30 || align="center" | M || Employee Code
|-
| leavedate || date || || align="center" | M ||
* ISO 8601 Date Format (yyyy-mm-dd).
* Example: "2020-09-08"
|-
| description || String || 160 || align="center" | O || Leave Description
|-
| leavetype || String || 20 || align="center" | M || Leave Type Code
|-
| leaveday || decimal || 18,8 || align="center" | O || Decimal in string. Example: "0.16666667"
|-
| leavedayastime || time || || align="center" | O || Time Format (hh:nn). Example: "01:20"
|}
 
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 
|-
|
<pre>
{
  "id":"leave.application",
  "data":[
      {
        "code":"001",
        "leavedate":"2020-09-09",
        "description":"Attent Shopping event",
        "leavetype":"AL",
        "leaveday":"1"
      },
      {
        "code":"002",
        "leavedate":"2020-09-08",
        "leavetype":"AL",
        "leavedayastime":"01:20"
      }
  ]
}
</pre>
|}
 
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Pending Wages, Overtime, Allowance & Deduction=====
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
!Fields Properties
|-
! Field Name !! Type !! Size !! (M)andatory/(O)ptional !! Description
|-
| postdate || date || || align="center" | M ||
* ISO 8601 Date Format (yyyy-mm-dd).
* Example: "2020-09-08"
|-
| employee || String || 30 || align="center" | M || Employee Code
|-
| project || String || 20 || align="center" | O || Project Code
|-
| job || String || 20 || align="center" | O || Job Code
|-
| task || String || 20 || align="center" | O || Task Code
|-
| code || String || 20 || align="center" | M ||
* pending.wages => Wages Type Code
* pending.overtime => Overtime Type Code
* pending.allowance => Allowance Type Code
* pending.deduction => Deduction Type Code
|-
| description || String || 160 || align="center" | O || Transaction Description
|-
| workunit || decimal || 18,8 || align="center" | O || Decimal in string. Example: "0.16666667"
|-
| workunitastime || time || || align="center" | O || Time Format (hh:nn). Example: "01:20"
|}
 
{| style="margin: 1em auto 1em auto;"
|-
|
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example Pending Wages
|-
|
<pre>
{
  "id":"pending.wages",
  "data":[
      {
        "postdate":"2020-09-08",
        "employee":"001",
        "code":"DR01",
        "description":"aaa",
        "workunit":"1"
      },
      {
        "postdate":"2020-09-08",
        "employee":"002",
        "code":"DR01",
        "workunitastime":"02:10"
      }
  ]
}
</pre>
|}
 
||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example Pending Overtime
|-
|
<pre>
{
  "id":"pending.overtime",
  "data":[
      {
        "postdate":"2020-09-08",
        "employee":"001",
        "code":"DR01",
        "description":"aaa",
        "workunit":"1"
      },
      {
        "postdate":"2020-09-08",
        "employee":"002",
        "code":"DR01",
        "workunitastime":"02:10"
      }
  ]
}
</pre>
|}
|}
<div style="float: right;">  [[#top|[top]]]</div>
 
=====Pending Claim=====
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
!Fields Properties
|-
! Field Name !! Type !! Size !! (M)andatory/(O)ptional !! Description
|-
| postdate || date || || align="center" | M ||
* ISO 8601 Date Format (yyyy-mm-dd).
* Example: "2020-09-08"
|-
| employee || String || 30 || align="center" | M || Employee Code
|-
| project || String || 20 || align="center" | O || Project Code
|-
| job || String || 20 || align="center" | O || Job Code
|-
| task || String || 20 || align="center" | O || Task Code
|-
| code || String || 20 || align="center" | M || Claim Type Code
|-
| description || String || 160 || align="center" | O || Transaction Description
|-
| ref1 || string || 20 || align="center" | O || Claim Reference 1
|-
| ref2 || string || 20 || align="center" | O || Claim Reference 2
|-
| amount|| 18,2 || || align="center" | M || Claim Amount
|}
 
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 
|-
|
<pre>
{
  "id":"pending.claim",
  "data":[
      {
        "postdate":"2020-09-08",
        "employee":"001",
        "description":"aaa",
        "amount":"1.12"
      },
      {
        "postdate":"2020-09-08",
        "employee":"001",
        "amount":"1.12"
      }
  ]
}
</pre>
|}
 
<div style="float: right;">  [[#top|[top]]]</div>
 
====Appendix====
:Below are constants used in SQL Payroll
 
=====Gender=====
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Value !! Description
|-
| align="center" | M || Male
|-
| align="center" | F || Female
|}
 
=====Married Status=====
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Value !! Description
|-
| align="center" | S || Single
|-
| align="center" | M || Married
|-
| align="center" | D || Divorced
|-
| align="center" | W || Widow
|}
 
=====SOCSO Type=====
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Value !! Description
|-
|-
| Unit || FMTBcd|| align="center" |8 || Qty or Unit
| align="center" | 0 || No SOCSO
|-
| align="center" | 1 || First Category
|-
| align="center" | 2 || Second Category
|}
 
=====EIS Type=====
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Value !! Description
|-
| align="center" | 0 || No EIS
|-
| align="center" | 1 || With EIS
|}
 
=====Leave Application Status=====
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
! Value !! Description
|-
| align="center" | 0 || Pending
|-
| align="center" | 1 || Approved
|-
| align="center" | 2 || Rejected
|-
| align="center" | 3 || Cancelled
|-
| align="center" | 4 || Commiteed
|}
|}
<div style="float: right;">  [[#top|[top]]]</div>


==Things To Consider==
==Things To Consider==

Latest revision as of 03:23, 10 August 2023

Introduction

This function is to import Information/Data from other Software Data
Below is the list Data that we can Import from

Special Import

Standard Import

System Contact Info
ELID Elid Sdn Bhd
SynGlobal Time Attendance SynGlobal Technologies Sdn Bhd
FingerTec TimeTec Computing Sdn Bhd
ANVIZ Hi-Profile Achievement (M) Sdn Bhd
Click Time Attendance EGroup Solution Sdn Bhd
Nexpro TimeTracX Nexus Professional Sdn Bhd
Check Time MVIT Solution
ECOTimePrint Eco Solution Technology
Invotime Invocore Sdn Bhd

SQL Payroll Program

May get it from here

Modules Require

For all import except UBS Payroll & AutoCount Payroll
  • Data Import

Guideline & Example

There are 3 type of import
  • Single File Import
  • Batch File Import (Available in Version 1.2019.167.146 & above)
  • Command Line Sync (Available in Version 1.2020.186.160 & above)

Single File Import

This guide is all import except UBS Payroll & AutoCount Payroll
Just copy below example & paste to notepad & save it as CSV

Pros

  1. Column Field name can be free format
  2. No need to Hash File

Cons

  1. Need Manually Mapping the Column
  2. Manually Import individually
  3. Not secure cause user can change the file before import

Overtime & Wages

Example 1 - OT or Wages (not applicable for version 192 & above)

Date,Employee,Code,UnitAsTime
26/6/2015,A001,DR01,04:00
28/6/2015,A001,DR02,09:38
26/6/2015,A002,DR02,06:18
29/6/2015,A002,DR03,03:22
26/6/2015,A003,DR03,04:55

Example 2 - OT or Wages

"Date","Employee","Code","Unit"
26/6/2015,A001,DR01,4
28/6/2015,A001,DR02,9.63
26/6/2015,A002,DR02,6.30
29/6/2015,A002,DR03,3.37
26/6/2015,A003,DR03,4.92

Table Fields Properties
Field Name Field Type Field Size Remarks
Date Date 0 In dd/mm/yyyy format
Employee String 30 Employee Code/ID
Code String 20 Overtime or Wages Code
Unit FMTBcd 8 Time in rate eg 3 hr 15 min = 3.25 (3 + (15/60))
UnitAsTime Time 0
  • In HH:MM format
  • not applicable for version 192 & above

Leave

Example 1 - Leave

TransDate,Employee,LeaveCode,UnitAsTime
08/11/2015,001,UL,08:00
19/11/2015,003,UL,09:20
15/11/2015,005,UL,08:00
01/11/2015,001,AL,08:00
03/11/2015,003,AL,08:00
08/11/2015,005,MC,08:00

Example 2 - Leave

TransDate,Employee,LeaveCode,Unit
08/11/2015,001,UL,1
19/11/2015,003,UL,1.17
15/11/2015,005,UL,1
01/11/2015,001,AL,1
03/11/2015,003,AL,1
08/11/2015,005,MC,1

Table Fields Properties
Field Name Field Type Field Size Remarks
Date Date 0 In dd/mm/yyyy format
Employee String 30 Employee Code/ID
LeaveCode String 20 Leave Code (Eg AL, MC, etc)
Unit FMTBcd 8 Day in rate eg 9 hr 20 min for 8 hr per day = 1.17 (((9+(20/60))/24)*(24/8))
UnitAsTime Time 0 In HH:MM format

Template.Tips-01.jpg It adviceable to use Unit instead of UnitAsTime as
  • UnitAsTime max value can enter is 23:59 only
  • if you Export as Grand Total

Employee

Example 1 - Employee

"Code";"Name";"DOB";"Gender";"Salary";"Resident";"EISTYPE";"EISCATEGORY"
"0018";"Shiro Emiya";"14/06/1980";"M";"1200";"F";"1";"0"
"0019";"Nanoha Takamachi";"01/10/1995";"F";"2000";"F";"0";"1"
"0020";"Belldandy";"01/09/1977";"F";"2500";"T";"1";"0"

Example 2 - Employee

CODE,NAME,NAME2,GENDER,DOB,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,PHONE1,PHONE2,MOBILE,EMAIL,NEWIC,OLDIC,PASSPORT,IMMIGRATIONNO,BRANCH,HRGROUP,DEPARTMENT,CATEGORY,PROJECT,JOB,TASK,COUNTRY,RACE,CALENDAR,JOBTITLE,JOINDATE,CONFIRMDATE,RESIGNDATE,MARITALSTATUS,ISACTIVE,WAGESTYPE,WAGES,CONTRIB,FREQUENCY,PAYMENTMETHOD,BANK,BANKACCNO,EPFNO,NK,INITIAL,SOCSONO,SOCSOTYPE,TAXBRANCH,TAXNO,DISABLED,RESIDENT,EASERIALNO,SPOUSENAME,SPOUSEADDRESS1,SPOUSEADDRESS2,SPOUSEADDRESS3,SPOUSEADDRESS4,SPOUSEPHONE1,SPOUSEPHONE2,SPOUSEMOBILE,SPOUSETAXBRANCH,SPOUSETAXNO,SPOUSEICNO,SPOUSEWORKING,SPOUSEDISABLED,LEAVEGROUP
001,LEE CHONG WAI,,M,12/12/1990,,,,,,,,lcw@gmail.com,901212014321,,,,----,----,----,----,----,----,----,MY,----,----,,02/01/2012,01/04/2012,,M,1,----,3000,----,----,MBB,CIBBMY,111265660,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----
002,KOO KIAN KEAT,,M,01/01/1992,,,,,,,,,920101101001,,,,----,----,----,----,----,----,----,MY,----,----,,01/08/2012,01/11/2012,,S,1,----,2000,----,HALF,MBB,RHBBMY,56706890,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----
003,NICOLE,,F,31/08/1994,,,,,,,,,940831105000,,,,----,----,----,----,----,----,----,MY,----,----,,02/01/2012,01/04/2012,,S,1,----,2800,----,----,MBB,MBBEMY,9048540654,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----
004,WONG CHUN HAN,,M,21/06/1984,,,,,,,,,840621083003,,,,----,----,----,----,----,----,----,MY,----,----,,01/02/2012,02/05/2012,,M,1,----,3200,----,----,MBB,HLBBMY,5646506160,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----
005,YAP KIM HOCK,,M,28/06/1980,,,,,,,,,800628063333,,,,----,----,----,----,----,----,----,MY,----,----,,02/01/2012,01/04/2012,,M,1,----,5000,----,----,MBB,OCBCMY,58946514315,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----

Table Fields Properties
Field Name Remarks
Marital Status
  • S - Single
  • M - Married
  • D - Divorced
  • W - Widow
Socso Type
  • 1 - First Category
  • 2 - Second Category
  • 0 - No Socso
EIS Type
  • 0 - No EIS
  • 1 - Has EIS
EIS Category
  • 1 - Foreign workers
  • 2 - Public sector
  • 3 - Domestic servant
  • 4 - Age 57 to 59 (never contribute EIS before)
  • 5 - Employer with no employee
  • 6 - Self employed/sole proprietors
  • 7 - Spouse of employer
Bank Code Is Bank SWIFT Code
  • PHBMMY - Affin Bank Berhad
  • RJHIMY - Al Rajhi Banking & Investment Corporation (Malaysia) Berhad
  • MFBBMY - Alliance Bank Malaysia Berhad
  • ARBKMY - AmBank (M) Berhad
  • BIMBMY - Bank Islam Malaysia Berhad
  • BKRMMY - Bank Kerjasama Rakyat Malaysia Berhad
  • BMMBMY - Bank Muamalat Malaysia Berhad
  • BSNAMY - Bank Simpanan Nasional Berhad
  • CIBBMY - CIMB Bank Berhad
  • CITIMY - Citibank Berhad
  • DEUTMY - Deutsche Bank (Malaysia) Berhad
  • HLBBMY - Hong Leong Bank Berhad
  • HBMBMY - HSBC Bank Malaysia Berhad
  • ICBKMY - Industrial and Commercial Bank of China (Malaysia) Berhad
  • MBBEMY - Malayan Banking Berhad
  • OCBCMY - OCBC Bank (Malaysia) Berhad
  • PBBEMY - Public Bank Berhad
  • RHBBMY - RHB Bank Berhad
  • SCBLMY - Standard Chartered Bank Malaysia Berhad
  • SMBCMY - Sumitomo Mitsui Banking Corporation Malaysia Berhad
  • UOVBMY - United Overseas Bank (Malaysia) Berhad

Allowance & Deduction

Example - Allowance

"Date","Employee","Code","Unit"
26/6/2017,A0001,HP,4
28/6/2017,A0001,FUEL,9.63
26/6/2017,A0002,HP,6.30
26/6/2017,A0003,FUEL,4.92
26/6/2017,A0004,HP,1

Example - Deduction

"Date","Employee","Code","Unit"
26/6/2017,A0001,LATE,4
28/6/2017,A0001,LATE,9.63
26/6/2017,A0002,LATE,6.30
29/6/2017,A0002,LATE,3.37
26/6/2017,A0003,LATE,4.92

Table Fields Properties
Field Name Field Type Field Size Remarks
Date Date 0 In dd/mm/yyyy format
Employee String 30 Employee Code/ID
Code String 20 Allowance or Deduction Code
Unit FMTBcd 8 Qty or Unit

Claim

Example 1 - Claim

"TransDate","PostDate","Employee","Code","Amount"
26/6/2017,26/6/2017,A0001,TOLL,4
28/6/2017,28/6/2017,A0001,PARKING,9.63
26/6/2017,26/6/2017,A0002,TOLL,6.30
26/6/2017,26/6/2017,A0003,PARKING,4.92
26/6/2017,26/6/2017,A0004,TOLL,1

Example 2 - Claim

"TransDate","PostDate","Employee","Code","Description","Ref1","REF2","Amount"
26/6/2017,26/6/2017,A0001,PARKING,"MID VALLEY - CUSTOMER AB","IV-01243","1235481377828",4
28/6/2017,28/6/2017,A0001,PARKING,"MID VALLEY - CUSTOMER AB","IV-01243","2584986928248",9.65
26/6/2017,26/6/2017,A0002,TOLL,"TOLL CLAIM","","",6.30

Table Fields Properties
Field Name Field Type Field Size Remarks
TransDate Date 0 In dd/mm/yyyy format

Date of transaction
Default TransDate is same as PostDate

PostDate Date 0 In dd/mm/yyyy format

Date of Process Date
Default PostDate is same as TransDate

Employee String 30 Employee Code/ID
Code String 20 Claim Code
Description String 100 Claim Description (Optional)
Ref1 String 20 Claim Reference 1 (Optional)
Ref2 String 20 Claim Reference 2 (Optional)
Amount FMTBcd 8 Claim Amount

Batch File Import(Deprecated)

This function only Available in Version 1.2019.167.146 & above.
All file should be in CSV also but need to be in 1 zip file.
In Zip should had At least 2 file
  • Transaction File (eg pending.overtime.csv)
  • Hash File (eg pending.overtime.hash)
Zip File Name can be any name you like

Pros

  1. 1 Button to import all the files
  2. No need to manually mapping the column
  3. Secure from changes by user cause file is Hash

Cons

  1. Need follow the column name given


Overtime & Wages

Transaction Type File Name
Wages
  • pending.wages.csv
  • pending.wages.hash
Overtime
  • pending.overtime.csv
  • pending.overtime.hash
Example 1 - OT or Wages

TransDate,PostDate,Employee,Code,WorkUnitAsTime
26/6/2015,26/6/2015,A001,DR01,04:00
28/6/2015,28/6/2015,A001,DR02,09:38
26/6/2015,26/6/2015,A002,DR02,06:18
29/6/2015,29/6/2015,A002,DR03,03:22
26/6/2015,26/6/2015,A003,DR03,04:55

Example 2 - OT or Wages

"TransDate","PostDate","Employee","Code","WorkUnit"
26/6/2015,26/6/2015,A001,DR01,4
28/6/2015,28/6/2015,A001,DR02,9.63
26/6/2015,26/6/2015,A002,DR02,6.30
29/6/2015,29/6/2015,A002,DR03,3.37
26/6/2015,26/6/2015,A003,DR03,4.92

Table Fields Properties
Field Name Field Type Field Size Remarks
TransDate Date 0 In dd/mm/yyyy or dd-mm-yyyy format

Date of transaction

PostDate Date 0 In dd/mm/yyyy or dd-mm-yyyy format

Date of Process Date

Employee String 30 Employee Code/ID
Code String 20 Overtime or Wages Code
WorkUnit FMTBcd 8 Time in rate eg 3 hr 15 min = 3.25 (3 + (15/60))
WorkUnitAsTime Time 0 In HH:MM format

Leave

File Name

  • leave.application.csv
  • leave.application.hash
Example 1 - Leave

LeaveDate,Code,LeaveType,Description,LeaveDayAsTime
08/11/2015,001,UL,Sick,08:00
19/11/2015,003,UL,Fever,09:20
15/11/2015,005,UL,Sick,08:00
01/11/2015,001,AL,Holiday To Melaka,08:00
03/11/2015,003,AL,Holiday To Penang,08:00
08/11/2015,005,MC,High Fever,08:00

Example 2 - Leave

LeaveDate,Code,LeaveType,Description,LeaveDay
08/11/2015,001,UL,Sick,1
19/11/2015,003,UL,Fever,1.17
15/11/2015,005,UL,Sick,1
01/11/2015,001,AL,Holiday To Melaka,1
03/11/2015,003,AL,Holiday to Penang,1
08/11/2015,005,MC,High Fever,1

Table Fields Properties
Field Name Field Type Field Size Remarks
Date Date 0 In dd/mm/yyyy or dd-mm-yyyy format
Code String 30 Employee Code/ID
LeaveType String 20 Leave Code (Eg AL, MC, etc)
Description String 80 Leave Description
LeaveDay FMTBcd 8 Day in rate eg 9 hr 20 min for 8 hr per day = 1.17 (((9+(20/60))/24)*(24/8))
LeaveDayAsTime Time 0 In HH:MM format

Template.Tips-01.jpg It adviceable to use LeaveDay instead of LeaveDayAsTime as
  • LeaveDayAsTime max value can enter is 23:59 only
  • if you Export as Grand Total

Allowance & Deduction

Transaction Type File Name
Allowance
  • pending.allowance.csv
  • pending.allowance.hash
Deduction
  • pending.deduction.csv
  • pending.deduction.hash
Example - Allowance

"TransDate","PostDate","Employee","Code","WorkUnit"
26/6/2017,26/6/2017,A0001,HP,4
28/6/2017,28/6/2017,A0001,FUEL,9.63
26/6/2017,26/6/2017,A0002,HP,6.30
26/6/2017,26/6/2017,A0003,FUEL,4.92
26/6/2017,26/6/2017,A0004,HP,1

Example - Deduction

"TransDate","PostDate","Employee","Code","WorkUnit"
26/6/2017,26/6/2017,A0001,LATE,4
28/6/2017,28/6/2017,A0001,LATE,9.63
26/6/2017,26/6/2017,A0002,LATE,6.30
29/6/2017,29/6/2017,A0002,LATE,3.37
26/6/2017,26/6/2017,A0003,LATE,4.92

Table Fields Properties
Field Name Field Type Field Size Remarks
TransDate Date 0 In dd/mm/yyyy or dd-mm-yyyy format

Date of transaction
Default TransDate is same as PostDate

PostDate Date 0 In dd/mm/yyyy or dd-mm-yyyy format

Date of Process Date
Default PostDate is same as TransDate

Employee String 30 Employee Code/ID
Code String 20 Allowance or Deduction Code
WorkUnit FMTBcd 8 Qty or Unit

File Hash

The Hash is using MD5
Steps
01. File Hash : Hash MD5 the whole file
02. File String Hash : Hash MD5 again using the result shown in MD5 in Step 1.
03. Save the result in Step 2 in .hash file

Example Using the Allowance Information as above

01. Result from File Hash : e83045758c13cb7c17ba8f2150919f36
02. Result from String Hash from Step 1 : 1b324dad30a84f322c23a1cdec481cc1
03. Save the Result in Step 2 in pending.allowance.hash file

Command Line Sync

This function only Available in Version 1.2020.186.160 & above.
This function can import and export file from/to SQL Payroll

Setting In SQL Payroll

An access token is required when the third party application calls data provider and data import commands.
SQLPayImp-02.jpg
01. Click Tools | Maintain User
02. Double Click ADMIN user
03. Click More | Generate Access Token
04. Enter the ADMIN password
05. Save the Token File

Data Provider (Export)

This command is to export data from SQL Payroll to Json file
Sample Output - 20200914
SQLPayImp-01.jpg
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "<id>" [-<param name> "<param value>"] -out "<json file>"
Command Description
[SQL Payroll bin folder Path]
  • Folder path where SQL Payroll Install
  • Eg C:\estream\SQL Payroll\bin
-token A Code where generate
-dataprovider Indicator To export Data from SQL Payroll
-id Indicator type of Data to export
[-<param name> "<param value>"] Additional parameter for some id type
-out Json file folder & name to save to
Maintain Employee

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "employee" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "employee" -out "c:\Users\Star\Desktop\employee.json" 
Maintain Branch

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "branch" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "branch" -out "c:\Users\Star\Desktop\branch.json" 
Maintain Department

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "department" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "department" -out "c:\Users\Star\Desktop\department.json" 
Maintain HR Group

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "hrgroup" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "hrgroup" -out "c:\Users\Star\Desktop\hrgroup.json" 
Maintain Category

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "category" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "category" -out "c:\Users\Star\Desktop\category.json" 
Maintain Project

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "project" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "project" -out "c:\Users\Star\Desktop\project.json" 
Maintain Job

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "job" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "job" -out "c:\Users\Star\Desktop\job.json" 
Maintain Task

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "task" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "task" -out "c:\Users\Star\Desktop\task.json" 
Maintain Leave Type

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "leavetype" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "leavetype" -out "c:\Users\Star\Desktop\leavetype.json" 
Maintain Leave Group

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "leavegroup" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "leavegroup" -out "c:\Users\Star\Desktop\leavegroup.json" 
Leave Application

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "leave" -datefrom "<yyyy-mm-dd>" -dateto "<yyyy-mm-dd>" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "leave" -datefrom "2020-01-01" -dateto "2020-12-31" -out "c:\Users\Star\Desktop\leave.json" 
Maintain Wages

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "wages" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "wages" -out "c:\Users\Star\Desktop\wages.json" 
Maintain Allowance

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "allowance" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "allowance" -out "c:\Users\Star\Desktop\allowance.json" 
Maintain Overtime

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "overtime" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "overtime" -out "c:\Users\Star\Desktop\overtime.json" 
Maintain Commission

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "commission" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "commission" -out "c:\Users\Star\Desktop\commission.json" 
Maintain Claim

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "claim" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "claim" -out "c:\Users\Star\Desktop\claim.json" 
Maintain Deduction

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "deduction" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "deduction" -out "c:\Users\Star\Desktop\deduction.json" 
PaySheet

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "paysheet" -year "<ProcessYear>" -month "<ProcessMonth>"-out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "paysheet" -year "2019" -month "1" -out "c:\Users\Star\Desktop\paysheet.json" 


Data Import

This command is to import data to SQL Payroll
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataimport -in "<zip file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataimport -in "c:\Users\Star\Desktop\output.zip" 
Each ZIP file can contains one or more data file, each data file is a JSON object that has id and data fields.
{
  "id":"",
  "data":[
  ]
}
Leave Application
Fields Properties
Field Name Type Size (M)andatory/(O)ptional Description
code String 30 M Employee Code
leavedate date M
  • ISO 8601 Date Format (yyyy-mm-dd).
  • Example: "2020-09-08"
description String 160 O Leave Description
leavetype String 20 M Leave Type Code
leaveday decimal 18,8 O Decimal in string. Example: "0.16666667"
leavedayastime time O Time Format (hh:nn). Example: "01:20"
Example
{
   "id":"leave.application",
   "data":[
      {
         "code":"001",
         "leavedate":"2020-09-09",
         "description":"Attent Shopping event",
         "leavetype":"AL",
         "leaveday":"1"
      },
      {
         "code":"002",
         "leavedate":"2020-09-08",
         "leavetype":"AL",
         "leavedayastime":"01:20"
      }
   ]
}
Pending Wages, Overtime, Allowance & Deduction
Fields Properties
Field Name Type Size (M)andatory/(O)ptional Description
postdate date M
  • ISO 8601 Date Format (yyyy-mm-dd).
  • Example: "2020-09-08"
employee String 30 M Employee Code
project String 20 O Project Code
job String 20 O Job Code
task String 20 O Task Code
code String 20 M
  • pending.wages => Wages Type Code
  • pending.overtime => Overtime Type Code
  • pending.allowance => Allowance Type Code
  • pending.deduction => Deduction Type Code
description String 160 O Transaction Description
workunit decimal 18,8 O Decimal in string. Example: "0.16666667"
workunitastime time O Time Format (hh:nn). Example: "01:20"
Example Pending Wages
{
   "id":"pending.wages",
   "data":[
      {
         "postdate":"2020-09-08",
         "employee":"001",
         "code":"DR01",
         "description":"aaa",
         "workunit":"1"
      },
      {
         "postdate":"2020-09-08",
         "employee":"002",
         "code":"DR01",
         "workunitastime":"02:10"
      }
   ]
}
Example Pending Overtime
{
   "id":"pending.overtime",
   "data":[
      {
         "postdate":"2020-09-08",
         "employee":"001",
         "code":"DR01",
         "description":"aaa",
         "workunit":"1"
      },
      {
         "postdate":"2020-09-08",
         "employee":"002",
         "code":"DR01",
         "workunitastime":"02:10"
      }
   ]
}
Pending Claim
Fields Properties
Field Name Type Size (M)andatory/(O)ptional Description
postdate date M
  • ISO 8601 Date Format (yyyy-mm-dd).
  • Example: "2020-09-08"
employee String 30 M Employee Code
project String 20 O Project Code
job String 20 O Job Code
task String 20 O Task Code
code String 20 M Claim Type Code
description String 160 O Transaction Description
ref1 string 20 O Claim Reference 1
ref2 string 20 O Claim Reference 2
amount 18,2 M Claim Amount
Example
{
   "id":"pending.claim",
   "data":[
      {
         "postdate":"2020-09-08",
         "employee":"001",
         "description":"aaa",
         "amount":"1.12"
      },
      {
         "postdate":"2020-09-08",
         "employee":"001",
         "amount":"1.12"
      }
   ]
}

Appendix

Below are constants used in SQL Payroll
Gender
Value Description
M Male
F Female
Married Status
Value Description
S Single
M Married
D Divorced
W Widow
SOCSO Type
Value Description
0 No SOCSO
1 First Category
2 Second Category
EIS Type
Value Description
0 No EIS
1 With EIS
Leave Application Status
Value Description
0 Pending
1 Approved
2 Rejected
3 Cancelled
4 Commiteed

Things To Consider

01. The target database must had Valid
- Employee Code
- OT Code
- Leave Code
- Wages Code
02. File extention must be in csv (e.g. OT.csv)
03. The data in "Net Figure" (eg. OT WorkUnitAsTime := Logout Time - Login Time)
04. There is no display format for Amount/Qty (eg 1600.12 not 1,600.12)
05. There is no extra spacing at the Begin & End of data (eg Tes1,Test2 not Tes1 , Tes2 ,)
06. The data type which is Boolean (0 = False & 1 = True)
07. Must 1 Transaction type 1 file (i.e. Overtime 1 file & Leave 1 file)
08. The output can be export out in Day or Time but not both in same time
09. Make sure only export out relevant data (eg if OT = 0 or No leave taken don't export out)


----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg Notes

1. Output Can be in Tab, Semicolon or Comma.
2. Field can be added by just look in the field chooser in Pending Payroll.

----------------------------------------------------------------------------------------------------------------------------------------------------


  • Need assistance? May email to support@sql.com.my

See also