南骏 池
5 天以前 7bd3f5d3a986a45095728e0a9745bdf0d099d77a
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using System.Data;
 
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不能为空且必须包含至少一条记录");
 
            int 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);
                                int 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不能为空且必须包含至少一条记录");
 
            int insertCount = 0;
            int 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);
                                        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();
                    }
                }
            }
        }
 
        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())
                {
                    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 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);
                                        int rows = updateCmd.ExecuteNonQuery();
                                        if (rows > 0) updateCount++;
                                    }
                                }
                                else
                                {
                                    // 插入
                                    var insertSql = @"INSERT INTO OA_DEPARTMENT (id, name, Parentid) VALUES (@Id, @Name, @Parentid)";
                                    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);
                                        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();
                    }
                }
            }
        }
    }
}