())
{
string strSubject = $"{calMonth.Year}年{calMonth.Month}月绩效数据确认通知";
string strBody = $"{staff.Name},您好！
{calMonth.Year}年{calMonth.Month}月绩效数据已生成，请在{DateTime.Now.AddDays(4).ToString("yyyy年MM月dd日")}之前确认完成！
";
string strTo = "luocaiyang@wispro-china.com";
_ = QuartzUtil.AddMailJob(strSubject, strBody, staff.Name, strTo);
}
return Task.CompletedTask;
}
private void DownloadReport(string ReportName,CalMonth calMonth,bool isModifyDate,bool isFirstOA=false)
{
string strFileSavePath = utility.ConfigHelper.GetSectionValue("IPEasySetting:DownloadFileSavePath");
int tryCount = 0;
tryAgain:
try
{
wispro.sp.utility.IPEasyUtility.DownloadReport(ReportName, isModifyDate);
}
catch {
tryCount++;
if (tryCount < 5)
{
goto tryAgain;
}
}
System.Threading.Thread.Sleep(5000);
string filename = $"{ReportName.Trim()}.xlsx";
string strFilePath = System.IO.Path.Combine(strFileSavePath, filename);
string strFinalPath = System.IO.Path.Combine(strFileSavePath, $"{calMonth.Year}{calMonth.Month}-{filename}");
InputPerformanceItem(strFilePath, true, false, 1, calMonth, isFirstOA);
System.IO.File.Move(strFilePath, strFinalPath);
}
private void DownloadProject(CalMonth calMonth)
{
string strFileSavePath = utility.ConfigHelper.GetSectionValue("IPEasySetting:DownloadFileSavePath");
int tryCount = 0;
string[] ReportNames =new string[] { "每月绩效统计--专案进度跟踪~S卷", "每月绩效统计--专案开卷报表~S卷" };
List pfItems = new List();
foreach (var ReportName in ReportNames)
{
tryAgain:
try
{
wispro.sp.utility.IPEasyUtility.DownloadReport(ReportName, false);
}
catch
{
tryCount++;
if (tryCount < 5)
{
goto tryAgain;
}
}
System.Threading.Thread.Sleep(5000);
string filename = $"{ReportName.Trim().Replace("~","_")}.xlsx";
string strFilePath = System.IO.Path.Combine(strFileSavePath, filename);
string strFinalPath = System.IO.Path.Combine(strFileSavePath, $"{calMonth.Year}{calMonth.Month}-{filename}");
var Items = GetProjectItem(strFilePath, calMonth, ReportName.Contains("专案开卷报表"));
foreach(var item in Items)
{
var temObj = pfItems.FirstOrDefault(s => s.CaseNo == item.CaseNo);
if(temObj == null)
{
item.Type = "专案";
pfItems.Add(item);
}
}
System.IO.File.Move(strFilePath, strFinalPath);
}
List rules = spDb.BasePointRules.ToList();
foreach (var item in pfItems)
{
SavePerformanceItem(item, rules);
}
}
private List GetProjectItem(string strPath,CalMonth calMonth,bool isOpenSheet)
{
DataTable dt = NPOIExcel.ExcelToDataTable(strPath, true, true, 1);
#region 删除重复行
DataTable temdt = new DataTable();
foreach (DataColumn col in dt.Columns)
{
DataColumn temCol = new DataColumn();
temCol.ColumnName = col.ColumnName;
temCol.DataType = col.DataType;
temCol.Caption = col.Caption;
temdt.Columns.Add(temCol);
}
new ExcelHelper().MerageDataTable(temdt, dt);
#endregion
List Items = new List();
int iRow = 0;
foreach (DataRow row in temdt.Rows)
{
string strDebug = $"{++iRow}\t{row["我方文号"]}";
PerformanceItem item = null;
if (!isOpenSheet)
{
item = Row2Item_2(row, calMonth);
}
else
{
item = Row2Item_3(row, calMonth);
}
if (item != null)
{
Items.Add(item);
}
}
return Items;
}
private Task InputPerformanceItem(string strExcelFile, bool isColumnName, bool ignorHideRows = false, int ColumnNameRow = 0, CalMonth calMonth = null, bool isFirstOAFile = false)
{
DataTable dt = NPOIExcel.ExcelToDataTable(strExcelFile, isColumnName, ignorHideRows, ColumnNameRow);
#region 删除重复行
DataTable temdt = new DataTable();
foreach (DataColumn col in dt.Columns)
{
DataColumn temCol = new DataColumn();
temCol.ColumnName = col.ColumnName;
temCol.DataType = col.DataType;
temCol.Caption = col.Caption;
temdt.Columns.Add(temCol);
}
new ExcelHelper().MerageDataTable(temdt, dt);
#endregion
List rules = spDb.BasePointRules.ToList();
int iRow = 0;
foreach (DataRow row in temdt.Rows)
{
string strDebug = $"{++iRow}\t{row["我方文号"]}";
PerformanceItem item = null;
if (isFirstOAFile)
{
item = Row2Item_1(row, calMonth);
}
else
{
item = Row2Item(row, calMonth);
}
if (item != null)
{
if (!InValidDoItem.Contains(item.DoItem))
{
SavePerformanceItem(item,rules);
strDebug = $"{strDebug}\t保存成功";
}
else
{
strDebug = $"{strDebug}\t无效处理事项";
}
}
else
{
strDebug = $"{strDebug}\t转换Item为Null";
}
System.Diagnostics.Debug.WriteLine(strDebug);
}
return Task.CompletedTask;
}
private Task SavePerformanceItem(PerformanceItem item,List rules)
{
try
{
Utility.Utility.CalBasePoint(item, rules);
new Controllers.PerformanceItemController(spDb).New(item);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
return Task.CompletedTask;
}
private PerformanceItem Row2Item_3(DataRow row, CalMonth calMonth)
{
PerformanceItem item = new PerformanceItem();
item.CaseNo = row["我方文号"].ToString().Trim();
item.CaseName = row["案件名称"].ToString().Trim();
item.CaseType = row["案件类型"].ToString().Trim();
item.BusinessType = row["业务类型"].ToString().Trim();
item.Customer = new Customer();
item.Customer.Name = row["客户名称"].ToString().Trim();
item.CaseState = row["案件状态"].ToString().Trim();
DateTime temDate = new DateTime();
if (DateTime.TryParse(row["委案日期"].ToString().Trim(), out temDate))
{
item.EntrustingDate = temDate;
}
string strHandler = "";
if (row.Table.Columns.Contains("处理人"))
{
strHandler = row["处理人"].ToString().Trim();
}
else
{
if (row.Table.Columns.Contains("案件处理人"))
{
strHandler = row["案件处理人"].ToString().Trim();
}
}
string[] temHandlers = strHandler.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
item.ItemStaffs = new List();
foreach (string name in temHandlers)
{
ItemStaff itemStaff = new ItemStaff();
int? iTem = GetStaff(name);
if ((iTem != null))
{
//itemStaff.Item = item;
itemStaff.DoPersonId = iTem.Value;
item.ItemStaffs.Add(itemStaff);
}
else
{
itemStaff.DoPerson = new Staff()
{
Name = name,
Account = name,
Password = "12345678",
IsCalPerformsnce = false,
Status = "正式员工",
StaffGradeId = 4
};
item.ItemStaffs.Add(itemStaff);
}
}
if (item.ItemStaffs.Count == 0)
{
System.Diagnostics.Debug.WriteLine($"没有处理人: {item.CaseNo}\t{item.DoItem}");
}
if (row.Table.Columns.Contains("核稿人"))
{
item.ReviewerId = GetStaff(row["核稿人"].ToString().Trim());
}
else
{
if (row.Table.Columns.Contains("案件核稿人"))
{
item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());
}
}
return item;
}
private PerformanceItem Row2Item_2(DataRow row, CalMonth calMonth)
{
PerformanceItem item = new PerformanceItem();
item.CaseNo = row["我方文号"].ToString().Trim();
item.CaseName= row["案件名称"].ToString().Trim();
item.CaseType = row["案件类型"].ToString().Trim();
item.BusinessType= row["业务类型"].ToString().Trim();
item.Customer = new Customer();
item.Customer.Name = row["客户名称"].ToString().Trim();
item.CaseState = row["案件状态"].ToString().Trim();
item.DoItem = row["处理事项"].ToString().Trim();
DateTime temDate = new DateTime();
if (DateTime.TryParse(row["内部期限"].ToString().Trim(), out temDate))
{
item.InternalDate = temDate;
}
if (DateTime.TryParse(row["客户期限"].ToString().Trim(), out temDate))
{
item.CustomerLimitDate = temDate;
}
if (DateTime.TryParse(row["初稿日"].ToString().Trim(), out temDate))
{
item.FirstDraftDate = temDate;
}
if (DateTime.TryParse(row["定稿日"].ToString().Trim(), out temDate))
{
item.FinalizationDate = temDate;
}
if (DateTime.TryParse(row["委案日期"].ToString().Trim(), out temDate))
{
item.EntrustingDate = temDate;
}
if (DateTime.TryParse(row["处理事项完成日"].ToString().Trim(), out temDate))
{
item.FinishedDate = temDate;
}
item.DoItemState = row["处理状态"].ToString().Trim();
string strHandler = "";
if (row.Table.Columns.Contains("处理人"))
{
strHandler = row["处理人"].ToString().Trim();
}
else
{
if (row.Table.Columns.Contains("案件处理人"))
{
strHandler = row["案件处理人"].ToString().Trim();
}
}
string[] temHandlers = strHandler.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
item.ItemStaffs = new List();
foreach (string name in temHandlers)
{
ItemStaff itemStaff = new ItemStaff();
int? iTem = GetStaff(name);
if ((iTem != null))
{
//itemStaff.Item = item;
itemStaff.DoPersonId = iTem.Value;
item.ItemStaffs.Add(itemStaff);
}
else
{
itemStaff.DoPerson = new Staff()
{
Name = name,
Account = name,
Password = "12345678",
IsCalPerformsnce = false,
Status = "正式员工",
StaffGradeId = 4
};
item.ItemStaffs.Add(itemStaff);
}
}
if (item.ItemStaffs.Count == 0)
{
System.Diagnostics.Debug.WriteLine($"没有处理人: {item.CaseNo}\t{item.DoItem}");
}
if (row.Table.Columns.Contains("核稿人"))
{
item.ReviewerId = GetStaff(row["核稿人"].ToString().Trim());
}
else
{
if (row.Table.Columns.Contains("案件核稿人"))
{
item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());
}
}
return item;
}
private PerformanceItem Row2Item_1(DataRow row, CalMonth calMonth)
{
PerformanceItem item = new PerformanceItem();
item.ApplicationType = row["申请类型"].ToString().Trim();
if (item.ApplicationType != "发明")
{
return null;
}
if (item.CaseNo.StartsWith("S"))
{
return null;
}
item.CaseNo = row["我方文号"].ToString().Trim();
if (calMonth != null)
{
item.CalMonth = calMonth;
}
else
{
if (row.Table.Columns.Contains("绩效核算月份"))
{
string strjxyf = row["绩效核算月份"].ToString().Trim();
string[] temYFs = strjxyf.Split(new char[] { '.' });
item.CalMonth = new CalMonth();
item.CalMonth.Year = int.Parse(temYFs[0]);
item.CalMonth.Month = int.Parse(temYFs[1]);
item.CalMonth.Status = 4;
}
else
{
item.CalMonth = new CalMonth();
item.Status = 0;
item.CalMonth.Year = DateTime.Now.AddMonths(-1).Year;
item.CalMonth.Month = DateTime.Now.AddMonths(-1).Month;
}
}
item.ApplicationType = row["申请类型"].ToString().Trim();
item.BusinessType = "普通新申请"; // row["业务类型"].ToString().Trim();
item.AgentFeedbackMemo = "发明一次OA授权"; //row["备注（填表注意事项）"].ToString().Trim();
item.DoItem = "发明一次OA授权"; //row["处理事项"].ToString().Trim();
string strHandler = "";
if (row.Table.Columns.Contains("处理人"))
{
strHandler = row["处理人"].ToString().Trim();
}
else
{
if (row.Table.Columns.Contains("案件处理人"))
{
strHandler = row["案件处理人"].ToString().Trim();
}
}
string[] temHandlers = strHandler.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
item.ItemStaffs = new List();
foreach (string name in temHandlers)
{
ItemStaff itemStaff = new ItemStaff();
int? iTem = GetStaff(name);
if ((iTem != null))
{
//itemStaff.Item = item;
itemStaff.DoPersonId = iTem.Value;
item.ItemStaffs.Add(itemStaff);
}
else
{
itemStaff.DoPerson = new Staff()
{
Name = name,
Account = name,
Password = "12345678",
IsCalPerformsnce = false,
Status = "正式员工",
StaffGradeId = 4
};
item.ItemStaffs.Add(itemStaff);
}
}
if (item.ItemStaffs.Count == 0)
{
System.Diagnostics.Debug.WriteLine($"没有处理人: {item.CaseNo}\t{item.DoItem}");
}
if (row.Table.Columns.Contains("核稿人"))
{
item.ReviewerId = GetStaff(row["核稿人"].ToString().Trim());
}
else
{
if (row.Table.Columns.Contains("案件核稿人"))
{
item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());
}
}
item.Customer = new Customer() { Name = row["客户名称"].ToString().Trim() };
item.ApplicationName = row["申请人"].ToString().Trim();
item.CaseName = row["案件名称"].ToString().Trim();
//案件备注
item.CaseMemo = $"发文日期:{row["发文日期"].ToString().Trim()}\r\n客户文号:{row["客户文号"].ToString().Trim()}\r\n上传日期:{row["上传日期"].ToString().Trim()}\r\n文件描述:{row["文件描述"].ToString().Trim()}";
return item;
}
private PerformanceItem Row2Item(DataRow row, CalMonth calMonth)
{
PerformanceItem item = new PerformanceItem();
item.CaseNo = row["我方文号"].ToString().Trim();
if (item.CaseNo.StartsWith("S"))
{
return null;
}
if (calMonth != null)
{
item.CalMonth = calMonth;
}
else
{
if (row.Table.Columns.Contains("绩效核算月份"))
{
string strjxyf = row["绩效核算月份"].ToString().Trim();
string[] temYFs = strjxyf.Split(new char[] { '.' });
item.CalMonth = new CalMonth();
item.CalMonth.Year = int.Parse(temYFs[0]);
item.CalMonth.Month = int.Parse(temYFs[1]);
item.CalMonth.Status = 4;
}
else
{
item.CalMonth = new CalMonth();
item.Status = 0;
item.CalMonth.Year = DateTime.Now.AddMonths(-1).Year;
item.CalMonth.Month = DateTime.Now.AddMonths(-1).Month;
}
}
item.ApplicationType = row["申请类型"].ToString().Trim();
item.BusinessType = row["业务类型"].ToString().Trim();
if (row.Table.Columns.Contains("备注（填表注意事项）"))
item.AgentFeedbackMemo = row["备注（填表注意事项）"].ToString().Trim();
item.DoItem = row["处理事项"].ToString().Trim();
item.CaseStage = row["案件阶段"].ToString().Trim();
item.CaseCoefficient = row["案件系数"].ToString().Trim();
item.DoItemCoefficient = row["处理事项系数"].ToString().Trim();
item.PreOastaffId = GetStaff(row["前一次OA处理人"].ToString().Trim());
string strHandler = "";
if (row.Table.Columns.Contains("处理人"))
{
strHandler = row["处理人"].ToString().Trim();
}
else
{
if (row.Table.Columns.Contains("案件处理人"))
{
strHandler = row["案件处理人"].ToString().Trim();
}
}
string[] temHandlers = strHandler.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
item.ItemStaffs = new List();
foreach (string name in temHandlers)
{
ItemStaff itemStaff = new ItemStaff();
string temName = name.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
int? iTem = GetStaff(temName);
if ((iTem != null))
{
//itemStaff.Item = item;
itemStaff.DoPersonId = iTem.Value;
item.ItemStaffs.Add(itemStaff);
}
else
{
itemStaff.DoPerson = new Staff()
{
Name = temName,
Account = temName,
Password = "12345678",
IsCalPerformsnce = false,
Status = "已离职",
StaffGradeId = 4
};
item.ItemStaffs.Add(itemStaff);
}
}
if (item.ItemStaffs.Count == 0)
{
System.Diagnostics.Debug.WriteLine($"没有处理人: {item.CaseNo}\t{item.DoItem}");
}
if (row.Table.Columns.Contains("核稿人"))
{
if (!string.IsNullOrEmpty(row["核稿人"].ToString().Trim()))
{
string temName = row["核稿人"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
item.ReviewerId = GetStaff(temName);
}
}
else
{
if (row.Table.Columns.Contains("案件核稿人"))
{
if (!string.IsNullOrEmpty(row["案件核稿人"].ToString().Trim()))
{
string temName = row["案件核稿人"].ToString().Trim().Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
item.ReviewerId = GetStaff(temName);
}
}
}
item.Customer = new Customer() { Name = row["客户名称"].ToString().Trim() };
item.ApplicationName = row["申请人"].ToString().Trim();
DateTime temDate = new DateTime();
if (DateTime.TryParse(row["处理事项完成日"].ToString().Trim(), out temDate))
{
item.FinishedDate = temDate;
}
//定稿日
if (DateTime.TryParse(row["定稿日"].ToString().Trim(), out temDate))
{
item.FinalizationDate = temDate;
}
//返稿日
if (DateTime.TryParse(row["返稿日"].ToString().Trim(), out temDate))
{
item.ReturnDate = temDate;
}
//案件类型
item.CaseType = row["案件类型"].ToString().Trim();
//案件状态
item.CaseState = row["案件状态"].ToString().Trim();
//处理事项备注
item.DoItemMemo = row["处理事项备注"].ToString().Trim();
//处理状态
item.DoItemState = row["处理状态"].ToString().Trim();
//案件名称
item.CaseName = row["案件名称"].ToString().Trim();
//委案日期
if (DateTime.TryParse(row["委案日期"].ToString().Trim(), out temDate))
{
item.EntrustingDate = temDate;
}
//客户期限
if (DateTime.TryParse(row["客户期限"].ToString().Trim(), out temDate))
{
item.CustomerLimitDate = temDate;
}
//内部期限
if (DateTime.TryParse(row["内部期限"].ToString().Trim(), out temDate))
{
item.InternalDate = temDate;
}
//初稿日
if (DateTime.TryParse(row["初稿日"].ToString().Trim(), out temDate))
{
item.FirstDraftDate = temDate;
}
//备注（发文严重超期是否属客观原因，若为否，请填写原因）
if (row.Table.Columns.Contains("备注（发文严重超期是否属客观原因，若为否，请填写原因）"))
{
item.OverDueMemo = row["备注（发文严重超期是否属客观原因，若为否，请填写原因）"].ToString().Trim();
}
//案件备注
item.CaseMemo = row["案件备注"].ToString().Trim();
return item;
}
private int? GetStaff(string v)
{
var staff = spDb.Staffs.Where(s => s.Name == v).FirstOrDefault();
if(staff != null)
{
return staff.Id;
}
return null;
}
}
}