StaffPerformance/wispro.sp.api/Job/ImportReportJob.cs

using DynamicExpresso;
using Microsoft.Extensions.DependencyInjection;
using Quartz;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using wispro.sp.entity;
using wispro.sp.utility;

namespace wispro.sp.api.Job
{
    public class ImportReportJob : Quartz.IJob
    {
        
        /// <summary>
        /// 不需要计算绩效的处理事项
        /// </summary>
        private string[] InValidDoItem = new string[]
        {
            "案件异常-催缴年费",
            "案件异常-视为放弃取得专利权",
            "办理登记手续",
            "办理登记手续-确认客户是否委托",
            "代理所变更",
            "绘图",
            "技术确认",
            "缴年费",
            "请求保密审查",
            "请求费减",
            "请求实审",
            "取得申请号",
            "取得证书",
            "取得专利权评价报告",
            "确认官方审查状况",
            "询问放弃或复审",
            "知识点总结",
            "专利权人发明人申请人信息变更",
            "专利挖掘与布局",
            "我方文号前缀带J",
            "开卷",
            "请求提前公开",
            "取得国际检索报告",
            "委外检索",
            "中止程序",
            "终止",
            "案件异常-视为撤回",
            "进入国家阶段提醒",
            "请求恢复权利",
            "请求优先权",
            "取得【无效宣告请求审查决定】",
            "撤回",
            "请求退款",
            "确认是否委托申请与类型",
            "专利交易",
            "专利权评价报告",
            "专利权人发明人申请人信息变更+代理所变更"
        };

        
        spDbContext spDb = new spDbContext();
        
        
        public Task Execute(IJobExecutionContext context)
        {
            CalMonth calMonth = new CalMonth()
            {
                Year = DateTime.Now.AddMonths(-1).Year,
                Month = DateTime.Now.AddMonths(-1).Month,
                Status = 0
            };


            var temCalMonth = spDb.CalMonths.Where<CalMonth>(x => x.Year == calMonth.Year && x.Month == calMonth.Month).FirstOrDefault();

            if (temCalMonth != null)
            {
                
                var iCount = spDb.PerformanceItems.Where<PerformanceItem>(p => p.CalMonthId == temCalMonth.Id).Count<PerformanceItem>();

                if (iCount > 0)
                {
                    return Task.CompletedTask;
                }
                calMonth = temCalMonth;
            }
            else
            {

                spDb.CalMonths.Add(calMonth);
                spDb.SaveChanges();

            }

            //每月绩效统计--发客户超过一个月未完成案件
            DownloadReport( "每月绩效统计--发客户超过一个月未完成案件", calMonth,false);
            
            //每月绩效统计--上个月递交完成案件
            DownloadReport("每月绩效统计--上个月递交完成案件", calMonth, true);
            
            //每月绩效统计--中国一次OA授权表
            DownloadReport("每月绩效统计--中国一次OA授权表", calMonth, true,true);

            //专案数据
            DownloadProject(calMonth);
            
            var lstStaff =   spDb.Staffs.Where<Staff>(s=>s.ItemStaffs.Where<ItemStaff>(p=>p.Item.CalMonthId == calMonth.Id).Count<ItemStaff>()>0);

            foreach (var staff in lstStaff.ToList<Staff>())
            {
                string strSubject = $"{calMonth.Year}年{calMonth.Month}月绩效数据确认通知";
                string strBody = $"<div style=\"position:absolute;width:800;height:300;margin-top:50px;margin-left:200px;margin-right:200px;box-shadow:0px 0px 3px 3px #ccc \"><div style= \"margin-top: 20px; margin-left:20px;;font-size:14px; \">{staff.Name},您好！</div><div style= \"margin-top: 25px; margin-left:20px;font-size:14px; \"><div>{calMonth.Year}年{calMonth.Month}月绩效已开始计算，请<a href= \"https://47.106.221.167:8080/ \">登录绩效系统</a>查看，如有任何问题，请使用绩效系统在<B>{utility.ConfigHelper.GetSectionValue("Lastest_feedback_date")}日</B>前反馈！</div><div style= \"margin-top: 100px;margin-right:15px; padding-bottom: 20px; font-size:14px;color:#888888;float:right; \">小美集团绩效管理系统</div></div>";
                string strTo = staff.Mail;

                if (!string.IsNullOrEmpty(strTo))
                {
                    _ = 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");
            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}");

            int tryCount = 0;

            tryAgain:
            try
            {
                wispro.sp.utility.IPEasyUtility.DownloadReport(ReportName, isModifyDate);

                if (!System.IO.File.Exists(strFilePath))
                {
                    throw new ApplicationException("超时！");
                }
            }
            catch {
                tryCount++;

                if (tryCount < 5)
                {
                    goto tryAgain;
                }
            }

            System.Threading.Thread.Sleep(5000);
            
            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<PerformanceItem> pfItems = new List<PerformanceItem>();
            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<PerformanceItem>(s => s.CaseNo == item.CaseNo);
                    if(temObj == null)
                    {
                        item.Type = "专案";
                        pfItems.Add(item);
                    }
                }

                System.IO.File.Move(strFilePath, strFinalPath);
            }

            //List<BasePointRule> rules = spDb.BasePointRules.ToList<BasePointRule>();
            foreach (var item in pfItems)
            {
                SaveProjectItem(item);
            }

        }

        private void SaveProjectItem(PerformanceItem item)
        {
            var temObj = spDb.ProjectInfos.FirstOrDefault(p=>p.CaseNo == item.CaseNo);

            if (temObj == null)
            {
                ProjectInfo project = new ProjectInfo();
                project.CaseNo = item.CaseNo;
                project.CaseName = item.CaseName;
                project.BusinessType = item.BusinessType;
                
                
                if(item.Customer != null)
                {
                    var temCustomer = spDb.Customers.FirstOrDefault(c => c.Name == item.Customer.Name);
                    if(temCustomer != null)
                    {
                        project.CustomerId = temCustomer.Id;
                    }
                }

                project.CaseState = 0;
                project.CaseType = item.CaseType;
                
                project.ReviewerId = item.ReviewerId;
                project.WorkflowUserId = item.WorkflowUserId;

                spDb.ProjectInfos.Add(project);
            }

            spDb.SaveChanges();
        }

        private List<PerformanceItem> 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<PerformanceItem> Items = new List<PerformanceItem>();
            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)
                {
                    item.CalMonth = calMonth;
                    item.CalMonthId = calMonth.Id;
                    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<BasePointRule> rules = spDb.BasePointRules.ToList<BasePointRule>();

            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<BasePointRule> rules)
        {
            try
            {
                Utility.Utility.CalBasePoint(item, rules);
                var ret= new Controllers.PerformanceItemController(spDb,new Services.FileTaskCacheService()).New(item);
                if (ret.Success == false)
                {
                    System.Diagnostics.Debug.WriteLine(ret.ErrorMessage);
                }
            }
            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<ItemStaff>();

            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());
                }
            }

            if (row.Table.Columns.Contains("流程负责人"))
            {
                item.WorkflowUserId  = 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<ItemStaff>();

            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());
                }
            }

            if (row.Table.Columns.Contains("流程负责人"))
            {
                item.WorkflowUserId = 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;
            }
            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();
            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<ItemStaff>();

            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()}";
            
            if (row.Table.Columns.Contains("翻译字数"))
            {
                //item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());

                var strWordCount = row["翻译字数"].ToString().Trim();
                if (string.IsNullOrEmpty(strWordCount))
                {
                    try
                    {
                        item.WordCount = int.Parse(strWordCount);
                    }
                    catch { }
                    
                }
            }

            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<ItemStaff>();

            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();

            if (row.Table.Columns.Contains("翻译字数"))
            {
                //item.ReviewerId = GetStaff(row["案件核稿人"].ToString().Trim());

                var strWordCount = row["翻译字数"].ToString().Trim();
                if (string.IsNullOrEmpty(strWordCount))
                {
                    try
                    {
                        item.WordCount = int.Parse(strWordCount);
                    }
                    catch { }

                }
            }

            return item;

        }

        private int? GetStaff(string v)
        {
            var staff = spDb.Staffs.Where<Staff>(s => s.Name == v).FirstOrDefault();

            if(staff != null)
            {
                return staff.Id;
            }

            return null;
        }
    }
}