using Microsoft.Data.SqlClient;
|
using NewPdaSqlServer.DB;
|
using NewPdaSqlServer.Dto.service;
|
using NewPdaSqlServer.util;
|
|
namespace NewPdaSqlServer.service.JJGZ;
|
|
public class OaToMesRecordManager : Repository<OaToMesRecord>
|
{
|
public string ApproveRecord(OaToMesRecord record)
|
{
|
var _strMsg = "";
|
if (record == null || record.dataJson == null ||
|
record.dataJson.Count == 0)
|
throw new Exception("dataJson不能为空且必须包含至少一条记录");
|
|
var successCount = 0;
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
conn.Open();
|
using (var tran = conn.BeginTransaction())
|
{
|
try
|
{
|
foreach (var item in record.dataJson)
|
{
|
// 参数校验增强
|
if (string.IsNullOrEmpty(item.StaffId))
|
throw new Exception("员工编号不能为空");
|
if (string.IsNullOrEmpty(item.LineNo))
|
throw new Exception("产线不能为空");
|
if (string.IsNullOrEmpty(item.Classes))
|
throw new Exception("班次不能为空");
|
if (item.CheckTime == null)
|
throw new Exception("打卡日期不能为空");
|
|
var sql =
|
@"INSERT INTO MES_JJGZ_RECORD (GUID, Staff_Id, Staff_No, Staff_Name, Line_No, Line_Name, CheckTime, ScheNo,RecordDate,OrgId) VALUES (NEWID(), @StaffId, @StaffNo, @StaffName, @LineNo, @LineName, @CheckTime, @Classes, getdate(),@OrgId)";
|
using (var cmd = new SqlCommand(sql, conn, tran))
|
{
|
cmd.Parameters.AddWithValue("@StaffId",
|
item.StaffId ?? (object)DBNull.Value);
|
cmd.Parameters.AddWithValue("@StaffNo",
|
item.StaffNo ?? (object)DBNull.Value);
|
cmd.Parameters.AddWithValue("@StaffName",
|
item.StaffName ?? (object)DBNull.Value);
|
cmd.Parameters.AddWithValue("@LineNo",
|
item.LineNo ?? (object)DBNull.Value);
|
cmd.Parameters.AddWithValue("@LineName",
|
item.LineName ?? (object)DBNull.Value);
|
cmd.Parameters.AddWithValue("@CheckTime",
|
item.CheckTime ?? (object)DBNull.Value);
|
cmd.Parameters.AddWithValue("@Classes",
|
item.Classes ?? (object)DBNull.Value);
|
cmd.Parameters.AddWithValue("@OrgId",
|
item.OrgId ?? (object)DBNull.Value);
|
var rows = cmd.ExecuteNonQuery();
|
if (rows > 0) successCount++;
|
}
|
}
|
|
tran.Commit();
|
return $"批量插入MES_JJGZ_RECORD成功！共插入{successCount}条记录。";
|
}
|
catch (Exception ex)
|
{
|
tran.Rollback();
|
_strMsg = $"批量插入MES_JJGZ_RECORD异常：{ex.Message}";
|
throw new Exception(_strMsg);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
public string ApproveUser(OaToMesUser user)
|
{
|
var _strMsg = "";
|
if (user == null || user.dataJson == null || user.dataJson.Count == 0)
|
throw new Exception("dataJson不能为空且必须包含至少一条记录");
|
|
var insertCount = 0;
|
var updateCount = 0;
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
conn.Open();
|
using (var tran = conn.BeginTransaction())
|
{
|
try
|
{
|
foreach (var item in user.dataJson)
|
{
|
// 参数校验增强
|
if (string.IsNullOrEmpty(item.Id))
|
throw new Exception("用户ID不能为空");
|
if (string.IsNullOrEmpty(item.Name))
|
throw new Exception("用户姓名不能为空");
|
|
// 检查是否存在
|
var checkSql =
|
@"SELECT COUNT(1) FROM OA_USER WHERE Id = @Id";
|
using (var checkCmd =
|
new SqlCommand(checkSql, conn, tran))
|
{
|
checkCmd.Parameters.AddWithValue("@Id", item.Id);
|
var exists = (int)checkCmd.ExecuteScalar() > 0;
|
|
if (exists)
|
{
|
// 更新
|
var updateSql =
|
@"UPDATE OA_USER SET NAME = @Name, JOBNUMBER = @Jobnumber, DEPARTMENT = @Department WHERE Id = @Id";
|
using (var updateCmd =
|
new SqlCommand(updateSql, conn, tran))
|
{
|
updateCmd.Parameters.AddWithValue("@Id",
|
item.Id);
|
updateCmd.Parameters.AddWithValue("@Name",
|
item.Name ?? (object)DBNull.Value);
|
updateCmd.Parameters.AddWithValue(
|
"@Jobnumber",
|
item.Jobnumber ?? (object)DBNull.Value);
|
updateCmd.Parameters.AddWithValue(
|
"@Department",
|
item.Department ??
|
(object)DBNull.Value);
|
var rows = updateCmd.ExecuteNonQuery();
|
if (rows > 0) updateCount++;
|
}
|
}
|
else
|
{
|
// 插入
|
var insertSql =
|
@"INSERT INTO OA_USER (Id, NAME, JOBNUMBER, DEPARTMENT) VALUES (@Id, @Name, @Jobnumber, @Department)";
|
using (var insertCmd =
|
new SqlCommand(insertSql, conn, tran))
|
{
|
insertCmd.Parameters.AddWithValue("@Id",
|
item.Id);
|
insertCmd.Parameters.AddWithValue("@Name",
|
item.Name ?? (object)DBNull.Value);
|
insertCmd.Parameters.AddWithValue(
|
"@Jobnumber",
|
item.Jobnumber ?? (object)DBNull.Value);
|
insertCmd.Parameters.AddWithValue(
|
"@Department",
|
item.Department ??
|
(object)DBNull.Value);
|
var rows = insertCmd.ExecuteNonQuery();
|
if (rows > 0) insertCount++;
|
}
|
}
|
}
|
}
|
|
tran.Commit();
|
return $"用户数据同步成功！插入{insertCount}条，更新{updateCount}条记录。";
|
}
|
catch (Exception ex)
|
{
|
tran.Rollback();
|
_strMsg = $"用户数据同步异常：{ex.Message}";
|
throw new Exception(_strMsg);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
public string ApproveDepartment(OaToMesDepartment department)
|
{
|
var _strMsg = "";
|
if (department == null || department.dataJson == null ||
|
department.dataJson.Count == 0)
|
throw new Exception("dataJson不能为空且必须包含至少一条记录");
|
|
var insertCount = 0;
|
var updateCount = 0;
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
conn.Open();
|
using (var tran = conn.BeginTransaction())
|
{
|
try
|
{
|
foreach (var item in department.dataJson)
|
{
|
// 参数校验增强
|
if (string.IsNullOrEmpty(item.Id))
|
throw new Exception("部门ID不能为空");
|
if (string.IsNullOrEmpty(item.Name))
|
throw new Exception("部门名称不能为空");
|
|
// 检查是否存在
|
var checkSql =
|
@"SELECT COUNT(1) FROM OA_DEPARTMENT WHERE id = @Id";
|
using (var checkCmd =
|
new SqlCommand(checkSql, conn, tran))
|
{
|
checkCmd.Parameters.AddWithValue("@Id", item.Id);
|
var exists = (int)checkCmd.ExecuteScalar() > 0;
|
|
if (exists)
|
{
|
// 更新
|
var updateSql =
|
@"UPDATE OA_DEPARTMENT SET name = @Name, Parentid = @Parentid, OrgId = @OrgId WHERE Id = @Id";
|
using (var updateCmd =
|
new SqlCommand(updateSql, conn, tran))
|
{
|
updateCmd.Parameters.AddWithValue("@Id",
|
item.Id);
|
updateCmd.Parameters.AddWithValue("@Name",
|
item.Name ?? (object)DBNull.Value);
|
updateCmd.Parameters.AddWithValue(
|
"@Parentid",
|
item.Parentid ?? (object)DBNull.Value);
|
updateCmd.Parameters.AddWithValue("@OrgId",
|
item.OrgId ?? (object)DBNull.Value);
|
var rows = updateCmd.ExecuteNonQuery();
|
if (rows > 0) updateCount++;
|
}
|
}
|
else
|
{
|
// 插入
|
var insertSql =
|
@"INSERT INTO OA_DEPARTMENT (id, name, Parentid, OrgId) VALUES (@Id, @Name, @Parentid, @OrgId)";
|
using (var insertCmd =
|
new SqlCommand(insertSql, conn, tran))
|
{
|
insertCmd.Parameters.AddWithValue("@Id",
|
item.Id);
|
insertCmd.Parameters.AddWithValue("@Name",
|
item.Name ?? (object)DBNull.Value);
|
insertCmd.Parameters.AddWithValue(
|
"@Parentid",
|
item.Parentid ?? (object)DBNull.Value);
|
insertCmd.Parameters.AddWithValue("@OrgId",
|
item.OrgId ?? (object)DBNull.Value);
|
var rows = insertCmd.ExecuteNonQuery();
|
if (rows > 0) insertCount++;
|
}
|
}
|
}
|
}
|
|
tran.Commit();
|
return $"部门数据同步成功！插入{insertCount}条，更新{updateCount}条记录。";
|
}
|
catch (Exception ex)
|
{
|
tran.Rollback();
|
_strMsg = $"部门数据同步异常：{ex.Message}";
|
throw new Exception(_strMsg);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
}
|
