精简修正代码

啊鑫

8 天以前 0aa54059b26e6641196e9953490dd18616e916e3

 service/JJGZ/OaToMesRecordManager.cs

@@ -1,215 +1,271 @@
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient;
using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using System.Data;
using NewPdaSqlServer.util;

namespace NewPdaSqlServer.service.JJGZ
namespace NewPdaSqlServer.service.JJGZ;

public class OaToMesRecordManager : Repository<OaToMesRecord>
{
    public class OaToMesRecordManager : Repository<OaToMesRecord>
    public string ApproveRecord(OaToMesRecord record)
    {
        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))
        {
            var _strMsg = "";
            if (record == null || record.dataJson == null || record.dataJson.Count == 0)
                throw new Exception("dataJson不能为空且必须包含至少一条记录");

            int successCount = 0;
            using (var conn = new SqlConnection(DbHelperSQL.strConn))
            conn.Open();
            using (var tran = conn.BeginTransaction())
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                try
                {
                    try
                    foreach (var item in record.dataJson)
                    {
                        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("打卡日期不能为空");
                        // 参数校验增强
                        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);
                                int rows = cmd.ExecuteNonQuery();
                                if (rows > 0) successCount++;
                            }
                        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();
                    }

                    tran.Commit();
                    return $"批量插入MES_JJGZ_RECORD成功！共插入{successCount}条记录。";
                }
            }
        }

        public string ApproveUser(OaToMesUser user)
        {
            var _strMsg = "";
            if (user == null || user.dataJson == null || user.dataJson.Count == 0)
                throw new Exception("dataJson不能为空且必须包含至少一条记录");

            int insertCount = 0;
            int updateCount = 0;
            using (var conn = new SqlConnection(DbHelperSQL.strConn))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                catch (Exception ex)
                {
                    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);
                                        int 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);
                                        int 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();
                    }
                    tran.Rollback();
                    _strMsg = $"批量插入MES_JJGZ_RECORD异常：{ex.Message}";
                    throw new Exception(_strMsg);
                }
            }
        }

        public string ApproveDepartment(OaToMesDepartment department)
        {
            var _strMsg = "";
            if (department == null || department.dataJson == null || department.dataJson.Count == 0)
                throw new Exception("dataJson不能为空且必须包含至少一条记录");

            int insertCount = 0;
            int updateCount = 0;
            using (var conn = new SqlConnection(DbHelperSQL.strConn))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                finally
                {
                    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);
                                        int 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);
                                        int 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();
                    }
                    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();
                }
            }
        }
    }
}