78 lines
3.3 KiB
C#
78 lines
3.3 KiB
C#
using MySql.Data.MySqlClient;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace ZktecoAttendenceService
|
|
{
|
|
class AttendanceDAO
|
|
{
|
|
public AttendanceDAO()
|
|
{
|
|
}
|
|
public void Add(Attendance attendance, MySqlConnection connection)
|
|
{
|
|
string query = "INSERT INTO `hrms`.`attendance_log` (`ac_no`, `checktime`, `processed`, `machine_id`, `in_out_type_id`, `machine_ip`, `date`) VALUES (@AcNo, @CHECKTIME, @isProcessed, @MachineId, @InOutTypeId, @MachineIP, @Getdate)";
|
|
|
|
MySqlCommand cmd = new MySqlCommand(query, connection);
|
|
cmd.Parameters.AddWithValue("@AcNo", attendance.AcNo);
|
|
cmd.Parameters.AddWithValue("@CHECKTIME", attendance.CheckTime);
|
|
cmd.Parameters.AddWithValue("@isProcessed", attendance.IsProcessed);
|
|
cmd.Parameters.AddWithValue("@machineId", attendance.MachineId);
|
|
cmd.Parameters.AddWithValue("@InOutTypeId", attendance.InOutTypeId);
|
|
cmd.Parameters.AddWithValue("@MachineIP", attendance.MachineIp);
|
|
cmd.Parameters.AddWithValue("@Getdate", attendance.Date);
|
|
|
|
cmd.ExecuteNonQuery();
|
|
|
|
}
|
|
public void AddColony(Attendance attendance, MySqlConnection connection)
|
|
{
|
|
string query = "REPLACE INTO `hrms`.`colony_attendance_log` (`ac_no`, `checktime`, `processed`, `machine_id`, `in_out_type_id`, `machine_ip`, `date`) VALUES (@AcNo, @CheckTime, @IsProcessed, @MachineId, @InOutTypeId, @MachineIp, @GetDate)";
|
|
|
|
MySqlCommand cmd = new MySqlCommand(query, connection);
|
|
cmd.Parameters.AddWithValue("@AcNo", attendance.AcNo);
|
|
cmd.Parameters.AddWithValue("@CheckTime", attendance.CheckTime);
|
|
cmd.Parameters.AddWithValue("@IsProcessed", attendance.IsProcessed);
|
|
cmd.Parameters.AddWithValue("@MachineId", attendance.MachineId);
|
|
cmd.Parameters.AddWithValue("@InOutTypeId", attendance.InOutTypeId);
|
|
cmd.Parameters.AddWithValue("@MachineIp", attendance.MachineIp);
|
|
cmd.Parameters.AddWithValue("@GetDate", attendance.Date);
|
|
|
|
cmd.ExecuteNonQuery();
|
|
|
|
}
|
|
|
|
public void BulkInsert(List<Attendance> attendanceLogs, MySqlConnection connection)
|
|
{
|
|
if (attendanceLogs == null || attendanceLogs.Count == 0) return;
|
|
|
|
|
|
|
|
// Start building the INSERT statement
|
|
StringBuilder queryBuilder = new StringBuilder("INSERT INTO attendance_log (ac_no, checktime, processed, machine_id, in_out_type_id, machine_ip, date) VALUES ");
|
|
|
|
// Add each record as a value
|
|
List<string> rows = new List<string>();
|
|
foreach (var log in attendanceLogs)
|
|
{
|
|
rows.Add($"('{log.AcNo}', '{log.CheckTime:yyyy-MM-dd HH:mm:ss}', {log.IsProcessed}, {log.MachineId}, {log.InOutTypeId}, '{log.MachineIp}', '{log.Date:yyyy-MM-dd HH:mm:ss}')");
|
|
}
|
|
|
|
// Concatenate rows with commas
|
|
queryBuilder.Append(string.Join(", ", rows));
|
|
|
|
// Execute the query
|
|
using (MySqlCommand cmd = new MySqlCommand(queryBuilder.ToString(), connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|