public static void WriteErrorOLEDB(ProblemEmployee emp)
{
using (OleDbConnection connection = new OleDbConnection(ConnStr))
{
connection.Open();
string mysql = "INSERT INTO [Sheet3$] VALUES (";
StringBuilder sb = new StringBuilder(mysql);
SetValueOnError(emp.Name, sb);
SetValueOnError(emp.Number, sb);
SetValueOnError(emp.OldDeptName, sb);
SetValueOnError(emp.OldDeptId, sb);
SetValueOnError(emp.OldGroupName, sb);
SetValueOnError(emp.OldGroupId, sb);
SetValueOnError(emp.DeptName, sb);
SetValueOnError(emp.DeptId, sb);
SetValueOnError(emp.GroupName,sb);
SetValueOnError(emp.GroupId,sb);
sb.AppendFormat("'{0}')", "新旧部门组别不一");
OleDbCommand commande = new OleDbCommand(sb.ToString(), connection);
commande.ExecuteNonQuery();
connection.Close();
connection.Dispose();
}
}
public bool CheckEmployee(string employeeName, string employeeNumber, string groupName, string deptName)
{
ProblemEmployee pe = new ProblemEmployee();
pe.Name = employeeName;
pe.Number = employeeNumber;
pe.OldDeptName = deptName;
pe.OldGroupName = groupName;
int deptNumber = 0, groupNumber = 0;
ExcelTool.GetExcelDeptGroupID(deptName, groupName, ref deptNumber, ref groupNumber, ref this.DeptName, ref this.GroupName);
pe.OldDeptId = deptNumber.ToString();
pe.OldGroupId = groupNumber.ToString();
pe.DeptName = this.DeptName;
pe.GroupName = this.GroupName;
OT_POSITION position = null; ;
bool matchMappingRecord = true;
string msg = String.Format("Excel中员工{0},员工号码{1},于{2},{3}", employeeName, employeeNumber, deptName, groupName); ;
if (groupNumber>0)
{
var q1 = from p in context.OT_POSITION where p.OT_EMP.EMP_NUMBER == employeeNumber && p.OT_ORG.ORG_NUM == groupNumber select p;
position = q1.FirstOrDefault();
if (position != null)
{
pe.GroupId = position.OT_ORG.ORG_NUM.ToString(); ;
pe.GroupName = position.OT_ORG.ORG_NAME;
}
//else
// Comm.Logger.Info("员工信息与文件一致");
}
if (position == null)
{
matchMappingRecord = false;
var q1 = from p in context.OT_POSITION where p.OT_EMP.EMP_NUMBER == employeeNumber select p;
position = q1.FirstOrDefault();
if (position == null)
return false;
}
this.GroupId = position.OT_ORG.ORG_ID.ToString();
pe.GroupId = position.OT_ORG.ORG_NUM.ToString();
pe.GroupName = position.OT_ORG.ORG_NAME;
this.GroupName = position.OT_ORG.ORG_NAME.ToString();
this.EmpId = position.EMP_ID;
var q2 = from p in context.OT_ORG where p.ORG_ID == position.OT_ORG.PARANT_ORG select p;
OT_ORG department = q2.FirstOrDefault();
if (department == null)
{
Comm.Logger.Error(string.Format("该员工没有分配部门{0},员工号码{1}", employeeName, employeeNumber));
return false;
}
this.DeptId = department.ORG_ID;
pe.DeptName=this.DeptName = department.ORG_NAME;
pe.DeptId = department.ORG_NUM.ToString();
if ((!department.ORG_NUM.Equals(deptNumber) && deptNumber != 0)
||(!pe.OldGroupId.Equals(pe.GroupId)))
{
msg = string.Format("Excel文件员工{0},员工号码{1},部门{2}-{3}不同其在库中的部门{4}-{5}", employeeName, employeeNumber,
deptNumber, deptName, department.ORG_NUM, department.ORG_NAME);
Comm.Logger.Warn(msg);
if (!EmpAbnormal.ContainsKey(employeeNumber))
{
EmpAbnormal.Add(employeeNumber, employeeName);
ExcelTool.WriteErrorOLEDB(pe);
}
}
return true;
}
public static void WriteError(ProblemEmployee emp)
{
ExcelTool.ErrSheet = (Worksheet)ErrBook.Sheets[3];
int lastRow = ErrSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
int index = lastRow + 1;
ErrSheet.Cells[index, 1] = emp.Name;
ErrSheet.Cells[index, 2] = emp.Number;
ErrSheet.Cells[index, 3] = emp.OldDeptName;
ErrSheet.Cells[index, 4] = emp.OldDeptId;
ErrSheet.Cells[index, 5] = emp.OldGroupName;
ErrSheet.Cells[index, 6] = emp.OldGroupId;
ErrSheet.Cells[index, 7] = emp.DeptName;
ErrSheet.Cells[index, 8] = emp.DeptId;
ErrSheet.Cells[index, 9] = emp.GroupName;
ErrSheet.Cells[index, 10] = emp.GroupId;
ErrSheet.Cells[index, 11] = "新旧部门组别不一";
ErrBook.Save();
}