Files
GX.O3.WMS/GuangFu.StringTracing/.svn/pristine/4a/4aca42d89f6429e7a4153139dfa6e4cfb6c846b7.svn-base

334 lines
12 KiB
Plaintext
Executable File
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
using GuangFu.StringTracing.Models;
namespace GuangFu.StringTracing.Common
{
/// <summary>
/// 部门同步统计信息
/// </summary>
public class DepartmentSyncStats
{
public int Total { get; set; }
public int Updated { get; set; }
public int Inserted { get; set; }
public List<string> Errors { get; set; }
public DepartmentSyncStats()
{
Errors = new List<string>();
}
}
/// <summary>
/// OA部门服务类
/// </summary>
public class OADepartmentService
{
private readonly string _connectionString;
public OADepartmentService()
{
_connectionString = ConfigurationManager.ConnectionStrings["OAConnection"]?.ConnectionString;
if (string.IsNullOrEmpty(_connectionString))
{
throw new Exception("未找到OAConnection数据库连接字符串配置");
}
}
/// <summary>
/// 根据部门名称查询OA部门ID
/// </summary>
/// <param name="departmentName">部门名称</param>
/// <returns>部门ID,如果未找到则返回0</returns>
public int GetDepartmentIdByName(string departmentName)
{
if (string.IsNullOrWhiteSpace(departmentName))
{
return 0;
}
string sql = "SELECT id FROM hrmdepartment WHERE CANCELED IS NULL AND DEPARTMENTNAME = @DepartmentName";
using (var mysqlUtils = new MySqlUtils())
{
mysqlUtils.ConnectionString = _connectionString;
using (var connection = mysqlUtils.GetConnection())
using (var command = new MySqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@DepartmentName", departmentName);
try
{
var result = command.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
return Convert.ToInt32(result);
}
return 0;
}
catch (Exception ex)
{
LogHelper.WriteLog($"查询部门 '{departmentName}' 的ID时发生异常: {ex.Message}");
return 0;
}
}
}
}
/// <summary>
/// 检查hrmdepartmentdefined表中是否存在指定DEPTID的记录
/// </summary>
/// <param name="deptId">OA部门ID</param>
/// <returns>是否存在</returns>
public bool CheckDepartmentDefinedExists(int deptId)
{
if (deptId <= 0)
{
return false;
}
string sql = "SELECT COUNT(1) FROM hrmdepartmentdefined WHERE DEPTID = @DeptId";
using (var mysqlUtils = new MySqlUtils())
{
mysqlUtils.ConnectionString = _connectionString;
using (var connection = mysqlUtils.GetConnection())
using (var command = new MySqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@DeptId", deptId);
try
{
var count = Convert.ToInt32(command.ExecuteScalar());
return count > 0;
}
catch (Exception ex)
{
LogHelper.WriteLog($"检查DEPTID={deptId}在hrmdepartmentdefined表中是否存在时发生异常: {ex.Message}");
return false;
}
}
}
}
/// <summary>
/// 更新hrmdepartmentdefined表中的ddbmid字段
/// </summary>
/// <param name="deptId">OA部门ID</param>
/// <param name="dingDeptId">钉钉部门ID</param>
/// <returns>是否更新成功</returns>
public bool UpdateDepartmentDefined(int deptId, long dingDeptId)
{
if (deptId <= 0)
{
return false;
}
string sql = "UPDATE hrmdepartmentdefined SET ddbmid = @Ddbmid WHERE DEPTID = @DeptId";
using (var mysqlUtils = new MySqlUtils())
{
mysqlUtils.ConnectionString = _connectionString;
try
{
var parameters = new[]
{
new MySqlParameter("@DeptId", deptId),
new MySqlParameter("@Ddbmid", dingDeptId.ToString()) // ddbmid是varchar50
};
int rowsAffected = mysqlUtils.ExecNonQuery(sql, parameters);
bool success = rowsAffected > 0;
if (success)
{
LogHelper.WriteLog($"更新成功: DEPTID={deptId}, ddbmid={dingDeptId}");
}
else
{
LogHelper.WriteLog($"更新失败: DEPTID={deptId} 的记录不存在");
}
return success;
}
catch (Exception ex)
{
LogHelper.WriteLog($"更新DEPTID={deptId}的ddbmid字段时发生异常: {ex.Message}");
return false;
}
}
}
/// <summary>
/// 插入hrmdepartmentdefined表记录
/// </summary>
/// <param name="deptId">OA部门ID</param>
/// <param name="dingDeptId">钉钉部门ID</param>
/// <returns>是否插入成功</returns>
public bool InsertDepartmentDefined(int deptId, long dingDeptId)
{
if (deptId <= 0)
{
return false;
}
string sql = "INSERT INTO hrmdepartmentdefined (DEPTID, ddbmid) VALUES (@DeptId, @Ddbmid)";
using (var mysqlUtils = new MySqlUtils())
{
mysqlUtils.ConnectionString = _connectionString;
try
{
var parameters = new[]
{
new MySqlParameter("@DeptId", deptId),
new MySqlParameter("@Ddbmid", dingDeptId.ToString()) // ddbmid是varchar50
};
int rowsAffected = mysqlUtils.ExecNonQuery(sql, parameters);
bool success = rowsAffected > 0;
if (success)
{
LogHelper.WriteLog($"插入成功: DEPTID={deptId}, ddbmid={dingDeptId}");
}
return success;
}
catch (Exception ex)
{
LogHelper.WriteLog($"插入hrmdepartmentdefined表记录时发生异常: DEPTID={deptId}, ddbmid={dingDeptId}, 异常: {ex.Message}");
return false;
}
}
}
/// <summary>
/// 同步单个钉钉部门到OA数据库
/// </summary>
/// <param name="dingDept">钉钉部门信息</param>
/// <returns>同步结果(1:更新成功, 2:插入成功, 0:失败或跳过)</returns>
public int SyncSingleDepartment(Models.DepartmentInfo dingDept)
{
if (dingDept == null || string.IsNullOrWhiteSpace(dingDept.name))
{
LogHelper.WriteLog($"钉钉部门信息无效,跳过同步");
return 0;
}
// 1. 根据部门名称查询OA部门ID
int deptId = GetDepartmentIdByName(dingDept.name);
if (deptId <= 0)
{
LogHelper.WriteLog($"OA中未找到部门名称 '{dingDept.name}',DEPTID赋值为0,跳过同步");
return 0;
}
// 2. 检查hrmdepartmentdefined表中是否存在该DEPTID的记录
bool exists = CheckDepartmentDefinedExists(deptId);
// 3. 根据存在情况执行更新或插入
bool success;
if (exists)
{
success = UpdateDepartmentDefined(deptId, dingDept.dept_id);
return success ? 1 : 0; // 1表示更新成功
}
else
{
success = InsertDepartmentDefined(deptId, dingDept.dept_id);
return success ? 2 : 0; // 2表示插入成功
}
}
/// <summary>
/// 批量同步钉钉部门到OA数据库
/// </summary>
/// <param name="dingDepartments">钉钉部门列表</param>
/// <returns>同步结果统计</returns>
public DepartmentSyncStats SyncDepartments(List<Models.DepartmentInfo> dingDepartments)
{
int total = dingDepartments?.Count ?? 0;
int updated = 0;
int inserted = 0;
var errors = new List<string>();
if (dingDepartments == null || total == 0)
{
errors.Add("钉钉部门列表为空");
return new DepartmentSyncStats
{
Total = total,
Updated = updated,
Inserted = inserted,
Errors = errors
};
}
LogHelper.WriteLog($"开始同步 {total} 个钉钉部门到OA数据库");
foreach (var dept in dingDepartments)
{
try
{
int result = SyncSingleDepartment(dept);
switch (result)
{
case 1:
updated++;
break;
case 2:
inserted++;
break;
case 0:
// 记录跳过原因
errors.Add($"部门 '{dept.name}' (钉钉ID: {dept.dept_id}) 同步失败或跳过");
break;
}
}
catch (Exception ex)
{
string errorMsg = $"同步部门 '{dept.name}' (钉钉ID: {dept.dept_id}) 时发生异常: {ex.Message}";
errors.Add(errorMsg);
LogHelper.WriteLog(errorMsg);
}
}
LogHelper.WriteLog($"同步完成: 总数={total}, 更新={updated}, 插入={inserted}, 失败/跳过={errors.Count}");
return new DepartmentSyncStats
{
Total = total,
Updated = updated,
Inserted = inserted,
Errors = errors
};
}
#region 插入OA日志
public void InsOALog(MySqlUtils mySqlUtils, string djbh, string jkxyjg, string tbdjmc)
{
try
{
string InsSQL = $@"INSERT INTO uf_clrzrs (djbh, tsrq, jkxyjg, tbdjmc) VALUES
('{djbh}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{jkxyjg}','{tbdjmc}') ";
mySqlUtils.ExecNonQuery(InsSQL);
}
catch (Exception ex)
{
return;
}
}
#endregion
}
}