StaffPerformance/wispro.sp.api/Controllers/PerformanceItemController.cs

using DynamicExpresso;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.StaticFiles;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using wispro.sp.api.Services;
using wispro.sp.api.Utility;
using wispro.sp.entity;
using wispro.sp.share;
using wispro.sp.utility;

namespace wispro.sp.api.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]
    public class PerformanceItemController : ControllerBase
    {
        spDbContext Context;
        IFileTaskService fileTaskService;
        

        public PerformanceItemController(spDbContext context, IFileTaskService _fileTaskService)
        {
            Context = context;
            fileTaskService = _fileTaskService;

        }

        public bool IsExist(PerformanceItem item)
        {
            var results = Context.PerformanceItems.Where<PerformanceItem>(x =>
                       x.CaseNo == item.CaseNo && 
                       x.DoItem == item.DoItem &&                        
                       x.CaseStage == item.CaseStage &&
                       x.CalMonth.Year == item.CalMonth.Year &&
                       x.CalMonth.Month == item.CalMonth.Month);

            if(results.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        public ApiSaveResponse New(PerformanceItem item)
        {
            ApiSaveResponse ret = new ApiSaveResponse();
            ret.Success = true;

            using (Context.Database.BeginTransaction())
            {
                try
                {
                    var results = Context.PerformanceItems.Where<PerformanceItem>(x =>
                       x.CaseNo == item.CaseNo && x.DoItem == item.DoItem && x.DoItem != "提出报告" && x.CaseStage == item.CaseStage);

                    if (item.DoItem.ToUpper() == "提交IDS")
                    {
                        //提交IDS 添加完成日期做完判断是否已算绩效的条件
                        results = Context.PerformanceItems.Where<PerformanceItem>(x=>x.CaseNo == item.CaseNo && x.DoItem == item.DoItem && x.FinishedDate == item.FinishedDate);
                    }

                    var items = results.Include(pi => pi.CalMonth).FirstOrDefault<PerformanceItem>();

                    if (items != null)
                    {

                        item.AgentFeedbackMemo = "已算绩效";
                        item.DoItemMemo = $"{items.DoItemMemo}\r\n{items.CalMonth.Year}-{items.CalMonth.Month}已计算！";
                        item.BasePoint = 0;
                    }

                    if (item.CalMonth != null)
                    {
                        var calMonth = Context.CalMonths.Where<CalMonth>(c => c.Year  == item.CalMonth.Year && c.Month == item.CalMonth.Month).FirstOrDefault();
                        
                        if(calMonth == null)
                        {
                            Context.CalMonths.Add(item.CalMonth);
                            Context.SaveChanges();
                        }
                        else
                        {
                            item.CalMonth = calMonth;
                        }

                        item.CalMonthId = item.CalMonth.Id;
                        item.CalMonth = null;
                    }

                    if (!string.IsNullOrEmpty(item.Customer.Name))
                    {
                        var temCustomer = Context.Customers.Where<Customer>(c => c.Name == item.Customer.Name).FirstOrDefault();

                        if (temCustomer == null)
                        {
                            temCustomer = new Customer() { Name = item.Customer.Name };
                            //item.Customer.Id = 0;
                            
                            Context.Customers.Add(temCustomer);
                            Context.SaveChanges();
                            item.Customer = temCustomer;
                            //item.CustomerId = item.Customer.Id;
                        }
                        else
                        {
                            item.Customer = temCustomer;
                        }

                        item.CustomerId = item.Customer.Id;
                        item.Customer = null;
                    }
                    else
                    {
                        item.Customer = null;
                    }

                    
                    var ItemStaffs = item.ItemStaffs;
                    item.ItemStaffs = null;

                    Context.PerformanceItems.Add(item);
                    Context.SaveChanges();

                    foreach (ItemStaff itemStaff in ItemStaffs)
                    {
                        itemStaff.ItemId = item.Id;
                        itemStaff.Item = null;

                        if (itemStaff.DoPersonId == 0 && itemStaff.DoPerson != null)
                        {
                            var temStaff = Context.Staffs.FirstOrDefault<Staff>(s => s.Name == itemStaff.DoPerson.Name);

                            if (temStaff != null)
                            {
                                itemStaff.DoPersonId = temStaff.Id;
                                itemStaff.DoPerson = null;
                            }
                            else
                            {
                                Context.Staffs.Add(itemStaff.DoPerson);
                                Context.SaveChanges();
                                itemStaff.DoPersonId = itemStaff.DoPerson.Id;
                                itemStaff.DoPerson = null;
                            }
                        }
                    }

                    Context.ItemStaffs.AddRange(ItemStaffs);
                    Context.SaveChanges();

                    Context.Database.CommitTransaction();
                }
                catch (Exception ex)
                {
                    ret.Success = false;
                    ret.ErrorMessage = ex.Message;
                    Context.Database.RollbackTransaction();
                }
            }
            

            return ret;
        }

        /// <summary>
        /// 更新绩效记录信息
        /// </summary>
        /// <param name="id">绩效记录编号</param>
        /// <param name="field">栏位，多个位以|杠隔开</param>
        /// <param name="value">栏位值，多个以|杠隔开</param>
        /// <returns></returns>
        public ApiSaveResponse UpdateFieldValue(int id,string field,string value)
        {
            ApiSaveResponse ret = new ApiSaveResponse();
            ret.Success = true;

            var item = Context.PerformanceItems.Include(p=>p.Customer).FirstOrDefault<PerformanceItem>(p => p.Id == id);

            if (item == null)
            {
                ret.Success = false;
                ret.ErrorMessage = $"不存在的{id}";

                return ret;
            }

            if(string.IsNullOrEmpty(field) )
            {
                ret.Success = false;
                ret.ErrorMessage = $"参数不对！";

                return ret;
            }

            string[] fields = field.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);

            string[] values = new string[] { null };
            if (!string.IsNullOrEmpty(value))
            {
                values = value.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
            }

            if (fields.Length != values.Length) {
                ret.Success = false;
                ret.ErrorMessage = "栏位和值对不匹配";
            }
            else 
            {
                for(int i = 0; i < fields.Length; i++)
                {
                    string temField = fields[i];
                    string temValue = values[i];
                    switch (temField)
                    {
                        case "AgentFeedbackMemo":
                            item.AgentFeedbackMemo = temValue;

                            break;
                        case "CaseCoefficient":
                            item.CaseCoefficient = temValue;

                            //此处添加保存到流程系统的代码
                            break;
                        case "DoItemCoefficient":
                            item.DoItemCoefficient = temValue;

                            //此处添加保存到流程系统的代码
                            break;
                        case "WordCount":
                            int wordCount;
                            if (int.TryParse(temValue, out wordCount))
                            {
                                item.WordCount = wordCount;
                            }
                            else
                            {
                                ret.Success = false;
                                ret.ErrorMessage = "所给的栏位值不能转换成数字！";
                                return ret;
                            }
                            break;
                        case "ReturnCasseNo":
                            item.ReturnCasseNo = temValue;
                            break;
                    }
                }

                if (item.AgentFeedbackMemo != "特殊点数申诉")
                {
                    Utility.Utility.CalBasePoint(item, Context.BasePointRules.ToList());

                    Context.SaveChanges();
                }
            }
            
            return ret;
        }
        public ListApiResponse<PerformanceItem> Query(int pageIndex,int pageSize)
        {
            ListApiResponse<PerformanceItem> ret = new ListApiResponse<PerformanceItem>();
            var results = Context.PerformanceItems
                .Where<PerformanceItem>(s => 
                    (s.ItemStaffs.Where<ItemStaff>(iStaff => iStaff.DoPerson.Name == User.Identity.Name).Count() > 0 || s.Reviewer.Name == User.Identity.Name) 
                    && s.CalMonth.Status != 4);

            ret.TotalCount = results.Count();

            List<PerformanceItem> retList = results
                .Include(pi=>pi.ItemStaffs).ThenInclude(iStaff=>iStaff.DoPerson)
                .Include(pi=>pi.Reviewer)
                .Include(pi=>pi.Customer)
                .Include(pi=>pi.CalMonth)
                .OrderByDescending(o=>o.Id)
                .Skip<PerformanceItem>((pageIndex - 1) * pageSize).Take(pageSize).ToList<PerformanceItem>();

            #region 将某些属性设为null，避免循环取值造成返回json过大
            foreach (PerformanceItem item in retList)
            {
                foreach (ItemStaff itemStaff in item.ItemStaffs)
                {
                    itemStaff.DoPerson.ItemStaffs = null;
                    itemStaff.DoPerson.ReviewerItems = null;
                    itemStaff.Item = null;
                }

                item.Reviewer.ReviewerItems = null;
                item.Reviewer.Customers = null;
                item.Reviewer.ItemStaffs = null;

                item.Customer.PerformanceItems = null;
                item.CalMonth.PerformanceItems = null;
            }
            #endregion

            ret.Results = retList;

            return ret;
        }

        public PerformanceItem Get(int Id)
        {
            var results = Context.PerformanceItems
                .Where<PerformanceItem>(s =>s.Id == Id);

            PerformanceItem item = results
                .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                .Include(pi => pi.Reviewer)
                .Include(pi => pi.Customer)
                .Include(pi => pi.CalMonth)
                .OrderByDescending(o => o.Id)
                .FirstOrDefault();

            #region 将某些属性设为null，避免循环取值造成返回json过大
            foreach (ItemStaff itemStaff in item.ItemStaffs)
            {
                itemStaff.DoPerson.ItemStaffs = null;
                itemStaff.DoPerson.ReviewerItems = null;
                itemStaff.Item = null;
            }

            if (item.Reviewer != null)
            {
                item.Reviewer.ReviewerItems = null;
                item.Reviewer.Customers = null;
                item.Reviewer.ItemStaffs = null;
            }

            item.Customer.PerformanceItems = null;
            item.CalMonth.PerformanceItems = null;
            #endregion

            return item;
        }
        /// <summary>
        /// 获取给定用户的绩效清单
        /// </summary>
        /// <param name="userid">用户id</param>
        /// <param name="type">获取类型；0：处理中；1：所有；4：已归档</param>
        /// <returns></returns>
        public ListApiResponse<PerformanceItem> GetMyList(int userid, int type,int pageIndex=1,int pageSize = 10)
        {
            
            ListApiResponse<PerformanceItem> ret = new ListApiResponse<PerformanceItem>();
            var results = Context.PerformanceItems
                .Where<PerformanceItem>(s =>
                    (s.ItemStaffs.Where<ItemStaff>(iStaff => iStaff.DoPerson.Id  == userid ).Count() > 0 || s.Reviewer.Id  == userid )
                    && s.CalMonth.Status == type);

            
            ret.TotalCount = results.Count();

            List<PerformanceItem> retList = results
                .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                .Include(pi => pi.Reviewer)
                .Include(pi => pi.Customer)
                .Include(pi => pi.CalMonth)                
                .OrderByDescending(o => o.Id)
                .Skip<PerformanceItem>((pageIndex - 1) * pageSize).Take(pageSize)
                .ToList<PerformanceItem>();

            #region 将某些属性设为null，避免循环取值造成返回json过大
            foreach (PerformanceItem item in retList)
            {
                foreach(ItemStaff itemStaff in item.ItemStaffs)
                {
                    itemStaff.DoPerson.ItemStaffs = null;
                    itemStaff.DoPerson.ReviewerItems = null;
                    itemStaff.Item = null;
                }

                item.Reviewer.ReviewerItems = null;
                item.Reviewer.Customers = null;
                item.Reviewer.ItemStaffs = null;

                item.Customer.PerformanceItems = null;
                item.CalMonth.PerformanceItems = null;
            }
            #endregion

            ret.Results = retList;

            return ret;
        }

        
        public NanduStatics DegreeOfDifficulty(CalMonth calMonth,int? userId = null)
        {
            NanduStatics retObj = new NanduStatics();

            string strKey = $"DegreeOfDifficulty:{calMonth.Year}-{calMonth.Month}-{userId}";
            object cacheEntry;
            if (!MyMemoryCache.TryGetValue(strKey, out cacheEntry))
            {
                IDictionary<string, double> CaseXiShu = new Dictionary<string, double>();
                var list = Context.CaseCeoffcients;

                foreach (var cx in list.ToList<CaseCeoffcient>())
                {
                    CaseXiShu.Add(cx.Ceoffcient, cx.Value);
                }

                var results = Context.PerformanceItems.Where<PerformanceItem>(p => p.CalMonthId == calMonth.Id  && ((p.Type == "新申请" && p.BasePoint > 0) || p.Type == "专案") && p.BasePoint > 0.0);

                if (userId != null)
                {
                    results = Context.PerformanceItems.Where<PerformanceItem>(p =>
                    p.CalMonthId == calMonth.Id && ((p.Type == "新申请" && p.BasePoint > 0) || p.Type == "专案") &&
                    (p.ItemStaffs.Where<ItemStaff>(s => s.DoPerson.Id == userId).Count() > 0 || p.ReviewerId == userId) && p.BasePoint > 0.0);
                }

                #region 循环计算
                int iCount = 0;
                double d = 0.0;
                var retList = results.ToList();

                foreach (var item in retList)
                {
                    string strCaseCeoffcient = item.CaseCoefficient;

                    #region 严重延期降系数
                    if (item.isDanger() && string.IsNullOrEmpty(item.OverDueMemo))
                    {
                        switch (item.CaseCoefficient)
                        {
                            case "S":
                                strCaseCeoffcient = "A";
                                break;
                            case "A":
                                strCaseCeoffcient = "B";
                                break;
                            case "B":
                                strCaseCeoffcient = "C";
                                break;
                            case "C":
                                strCaseCeoffcient = "D";
                                break;
                        }
                    }
                    #endregion

                    switch (strCaseCeoffcient)
                    {
                        case "S":
                            retObj.S += 1;
                            break;
                        case "A":
                            retObj.A += 1;
                            break;
                        case "B":
                            retObj.B += 1;
                            break;
                        case "C":
                            retObj.C += 1;
                            break;
                        case "D":
                            retObj.D += 1;
                            break;
                    }

                    if (CaseXiShu.ContainsKey(strCaseCeoffcient))
                    {
                        d += CaseXiShu[strCaseCeoffcient];
                        iCount += 1;
                    }

                }
                #endregion

                

                retObj.NanduXS = d / (double)iCount;
                
                // Save data in cache.
                MyMemoryCache.SetValue(strKey, retObj);
                
                return retObj;
            }
            else
            {
                return (NanduStatics)cacheEntry;
            }
        }

        public List<string> GetFeedbackString(int itemId)
        {
            PerformanceItem item = Context.PerformanceItems.FirstOrDefault<PerformanceItem>(p => p.Id == itemId);

            if(item != null)
            {
                return Utility.Utility.GetFeedbackMemos(item, Context.BasePointRules.ToList());
            }

            return new List<string>();
        }

        private List<StaffStatistics> _CalMyStatistics(CalMonth calMonth, int? userid = null)
        {
            NanduStatics gspjXS = DegreeOfDifficulty(calMonth);
            calMonth.NanduXS = gspjXS.NanduXS;
            calMonth.S = gspjXS.S;
            calMonth.A = gspjXS.A;
            calMonth.B = gspjXS.B;
            calMonth.C = gspjXS.C;
            calMonth.D = gspjXS.D;

            //未归档，从绩效记录中统计数据
            var results = Context.PerformanceItems.Where<PerformanceItem>(s => s.CalMonth.Id == calMonth.Id);

            if (userid != null)
            {
                results = Context.PerformanceItems.Where<PerformanceItem>(s =>
                    (s.ItemStaffs.Where<ItemStaff>(iStaff => iStaff.DoPerson.Id == userid).Count() > 0 || s.Reviewer.Id == userid)
                    && s.CalMonth.Id == calMonth.Id);
            }

            List<PerformanceItem> ItemList = results
                .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                .Include(pi => pi.Reviewer).ThenInclude(p=>p.StaffGrade)
                .Include(pi => pi.Customer)
                .OrderByDescending(o => o.Id)
                .ToList<PerformanceItem>();

            List<StaffStatistics> retList = new List<StaffStatistics>();
            List<VerifyCoefficient> verifyCoefficients = Context.VerifyCoefficients.ToList<VerifyCoefficient>();

            var Rules = Context.BasePointRules.ToList();

            foreach (PerformanceItem item in ItemList)
            {

                //if (item.BasePoint == null)
                //{

                if (item.AgentFeedbackMemo != "特殊点数申诉")
                {
                    Utility.Utility.CalBasePoint(item, Rules);
 
                    Context.SaveChanges();
                }
                //}

                if (item.BasePoint != null && item.BasePoint.Value > 0)
                {
                    double doPersonBasePoint = item.BasePoint.Value;

                    List<StaffStatistics> itemStatistics = _calItemJX(calMonth, verifyCoefficients, item,Context);

                    List<StaffStatistics> temItemStatics;

                    if (userid != null)
                    {
                        temItemStatics = itemStatistics.Where<StaffStatistics>(s => s.StaffId == userid).ToList();
                    }
                    else
                    {
                        temItemStatics = itemStatistics;
                    }

                    foreach (StaffStatistics retUserValue in temItemStatics)
                    {
                        var temValue = retList.Where<StaffStatistics>(s => s.StaffId == retUserValue.StaffId && s.jxType == retUserValue.jxType && s.CalMonthId == calMonth.Id).FirstOrDefault();
                        if (temValue != null)
                        {
                            temValue.totalBasePoint += retUserValue.totalBasePoint;
                        }
                        else
                        {
                            retList.Add(retUserValue);
                        }
                    }
                }

            }

            if (userid != null)
            {
                retList = retList.Where<StaffStatistics>(s => s.StaffId == userid.Value).ToList();
            }

            IDictionary<int, NanduStatics> staffXiShu = new Dictionary<int, NanduStatics>();
            foreach (StaffStatistics ss in retList)
            {
                if (ss.jxType.Contains("新申请") || ss.jxType.Contains("专案"))
                {
                    if (!staffXiShu.ContainsKey(ss.StaffId))
                    {
                        NanduStatics nandu = DegreeOfDifficulty(calMonth, ss.StaffId);
                        staffXiShu.Add(ss.StaffId, nandu);
                    }

                    ss.totalActuallyPoint = ss.totalBasePoint * staffXiShu[ss.StaffId].NanduXS / gspjXS.NanduXS;
                    ss.NanduXS = staffXiShu[ss.StaffId].NanduXS;
                    ss.S = staffXiShu[ss.StaffId].S;
                    ss.A = staffXiShu[ss.StaffId].A;
                    ss.B = staffXiShu[ss.StaffId].B;
                    ss.C = staffXiShu[ss.StaffId].C;
                    ss.D = staffXiShu[ss.StaffId].D;
                }
                else
                {
                    ss.totalActuallyPoint = ss.totalBasePoint;
                }

                //ss.CalMonth.PerformanceItems = null;
            }


            return retList;

        }

        private void _RefreshBasePoint()
        {
            spDbContext spDb = new spDbContext();
            var lstItem = spDb.PerformanceItems.Include(p => p.Customer).Where(p => p.CalMonth.Status != 4).ToList();
            var rules = spDb.BasePointRules.ToList();
            foreach (var item in lstItem)
            {
                if (item.AgentFeedbackMemo != "特殊点数申诉")
                {
                    Utility.Utility.CalBasePoint(item, rules);
                    spDb.SaveChanges();
                }
            }
        }
        public ApiSaveResponse RefreshBasePoint()
        {
            System.Threading.Thread t = new Thread(_RefreshBasePoint);
            t.Start();

            return new ApiSaveResponse() {
                Success = true
            };
        }

        public ApiSaveResponse RefreshFromIPEasyById(int itemId)
        {
            var Item = Context.PerformanceItems.Include(p=>p.Customer).FirstOrDefault(p => p.Id == itemId);
            if (Item != null)
            {
                new Job.UpdateJXDataFromIPEasyJob().UpdateFromIPEasy(Item, Context);
            }

            return new ApiSaveResponse()
            {
                Success = true
            };
        }

        public ApiSaveResponse RefreshFromIPEasy_Batch(int type)
        {
            new Job.UpdateJXDataFromIPEasyJob().RefreshFromIPEasy(type);

            return new ApiSaveResponse()
            {
                Success = true
            };
        }

        public ApiSaveResponse RefreshFromIPEasy(string CaseNo,string DoItem,string caseStage)
        {
            var Item = Context.PerformanceItems.Include(p=>p.Customer).FirstOrDefault(p=>p.CaseNo == CaseNo && p.DoItem == DoItem && p.CaseStage == caseStage);
            if(Item != null)
            {
                new Job.UpdateJXDataFromIPEasyJob().UpdateFromIPEasy(Item,Context);
            }

            return new ApiSaveResponse()
            {
                Success = true
            };
        }

        public ApiSaveResponse CompareExcel2DB()
        {

            System.Threading.Thread t = new Thread(new ThreadStart(_CompareExcel2DB));
            t.Start();
            
            return new ApiSaveResponse()
            {
                Success = true
            };
        }

        private void Export2ExcelThread()
        {
            spDbContext spDb = new spDbContext();
            List<PerformanceItem> items = spDb.PerformanceItems
                .Include(p => p.Reviewer).ThenInclude(p => p.StaffGrade)
                .Include(p=>p.PreOastaff)
                .Include(p => p.Customer)
                .Include(p => p.CalMonth)
                .Include(p => p.ItemStaffs).ThenInclude(s => s.DoPerson).ThenInclude(s => s.StaffGrade)
                .Where(p => p.CalMonth.Status == 0 && !p.CaseNo.StartsWith("J")).OrderBy(p => p.CaseNo).ThenBy(p => p.DoItem).ToList();

            DataTable dt = new DataTable();
            #region 栏位名称
            dt.Columns.Add("我方文号",typeof(string));
            dt.Columns.Add("申请类型", typeof(string));
            dt.Columns.Add("业务类型", typeof(string));
            dt.Columns.Add("备注（填表注意事项）", typeof(string));
            dt.Columns.Add("处理事项", typeof(string));
            dt.Columns.Add("案件阶段", typeof(string));
            dt.Columns.Add("案件系数", typeof(string));
            dt.Columns.Add("处理事项系数", typeof(string));
            dt.Columns.Add("前一次OA处理事项系数", typeof(string));
            dt.Columns.Add("前一次OA处理人", typeof(string));
            dt.Columns.Add("处理人等级", typeof(string));
            dt.Columns.Add("基本点数", typeof(string));
            dt.Columns.Add("核稿系数", typeof(string));
            dt.Columns.Add("核稿绩效", typeof(string));
            dt.Columns.Add("处理人", typeof(string));
            dt.Columns.Add("核稿人", typeof(string));
            dt.Columns.Add("客户名称", typeof(string));
            dt.Columns.Add("申请人", typeof(string));
            dt.Columns.Add("处理事项完成日", typeof(string));
            dt.Columns.Add("定稿日", typeof(string));
            dt.Columns.Add("返稿日", typeof(string));
            dt.Columns.Add("案件类型", typeof(string));
            dt.Columns.Add("案件状态", typeof(string));
            dt.Columns.Add("处理事项备注", typeof(string));
            dt.Columns.Add("处理状态", typeof(string));
            dt.Columns.Add("案件名称", typeof(string));
            dt.Columns.Add("委案日期", typeof(string));
            dt.Columns.Add("客户期限", typeof(string));
            dt.Columns.Add("内部期限", typeof(string));
            dt.Columns.Add("初稿日", typeof(string));
            dt.Columns.Add("备注（发文严重超期是否属客观原因，若为否，请填写原因", typeof(string));
            dt.Columns.Add("绩效类型", typeof(string));
            dt.Columns.Add("案件备注", typeof(string));
            dt.Columns.Add("处理人绩效系数", typeof(string));
            dt.Columns.Add("系统核算绩效", typeof(string));
            dt.Columns.Add("实际处理人", typeof(string));

            #endregion

            var verifyCeoffients = spDb.VerifyCoefficients.ToList();
            var Rules = spDb.BasePointRules.ToList();

            foreach(var p in items)
            {
                Utility.Utility.CalBasePoint(p, Rules);
                var jxList = _calItemJX(p.CalMonth, verifyCeoffients, p, spDb);

                bool isPJFP = true;
                bool isReviewerInDopersons = false;
                if (p.ReviewerId.HasValue)
                {
                    isReviewerInDopersons = (p.ItemStaffs.Where(i => i.DoPersonId == p.ReviewerId).Count() > 0);
                }
                
                double total = p.ItemStaffs.Count();
                if (p.ItemStaffs.Where<ItemStaff>(p => p.PerformancePoint != null || p.PerformancePoint == 0).Count() > 0)
                {
                    total = p.ItemStaffs.Select(i => i.PerformancePoint.Value).Sum();
                    isPJFP = false;
                }

                foreach (var iStaff in p.ItemStaffs)
                {
                    DataRow row = dt.NewRow();
                    row["我方文号"] = p.CaseNo;
                    row["申请类型"] = p.ApplicationType;

                    if(p.Customer.Name.Contains("OPPO") && p.ApplicationType == "实用新型")
                    {
                        row["申请类型"] = "发明";
                        row["案件备注"] = $"{row["案件备注"]}\r\nOPPO案件实用新型修改为发明】";
                    }

                    row["业务类型"] = p.BusinessType;
                    row["备注（填表注意事项）"] = p.AgentFeedbackMemo;
                    row["处理事项"] = p.DoItem;
                    row["案件阶段"] = p.CaseStage;
                    
                    row["案件系数"] = p.CaseCoefficient;
                    if(p.isDanger() && string.IsNullOrEmpty(p.OverDueMemo))
                    {
                        switch (p.CaseCoefficient)
                        {
                            case "S":
                                row["案件系数"] = "A";
                                break;
                            case "A":
                                row["案件系数"] = "B";
                                break;
                            case "B":
                                row["案件系数"] = "C";
                                break;
                            case "C":
                                row["案件系数"] = "D";
                                break;
                        }

                        row["案件备注"] = $"{row["案件备注"]}\r\n严重延期降系数【{p.CaseCoefficient}->{row["案件系数"]}】";
                    }

                    row["处理事项系数"] = p.DoItemCoefficient;
                    row["前一次OA处理事项系数"] = "";
                    row["前一次OA处理人"] = p.PreOastaff.Name;

                    if(iStaff.DoPerson.Status == "试用期")
                    {
                        row["实际处理人"] = iStaff.DoPerson.Name;
                        row["案件备注"] = $"{row["案件备注"]}\r\n处理人{iStaff.DoPerson.Name}未转正";
                        row["处理人"] = p.Reviewer.Name;
                        row["处理人等级"] = p.Reviewer.StaffGrade.Grade;
                        row["基本点数"] = (p.BasePoint / total).ToString();
                       
                        row["核稿人"] = "";
                        row["核稿系数"] = "";
                        row["核稿绩效"] = "";
                        row["处理人绩效系数"] = p.Reviewer.StaffGrade.Coefficient;
                        row["系统核算绩效"] = "";
                    }
                    else
                    {
                        row["处理人"] = iStaff.DoPerson.Name;
                        row["处理人等级"] = iStaff.DoPerson.StaffGrade.Grade;
                        row["基本点数"] = (p.BasePoint / total).ToString();
                        row["处理人绩效系数"] = iStaff.DoPerson.StaffGrade.Coefficient;
                        row["系统核算绩效"] = "";

                        if (isReviewerInDopersons)
                        {
                            row["核稿人"] = "";
                            row["核稿系数"] = "";
                            row["核稿绩效"] = "";
                            row["处理人绩效系数"] = "";
                            row["系统核算绩效"] = "";
                            row["案件备注"] = $"{row["案件备注"]}\r\n核稿人与处理人一致，置空核稿人栏位";
                        }
                        else
                        {
                            row["核稿人"] = p.Reviewer?.Name;
                            var vCoefficient = Context.VerifyCoefficients.FirstOrDefault(c => c.CheckerId == p.ReviewerId && c.DoPersonId == iStaff.DoPersonId);
                            if (vCoefficient != null)
                            {
                                row["核稿系数"] = vCoefficient.Coefficient;
                                row["核稿绩效"] = (p.BasePoint / total) * vCoefficient.Coefficient;
                            }
                        }
                        
                    }

                    row["客户名称"] = p.Customer.Name;
                    row["申请人"] = p.ApplicationName;
                    row["处理事项完成日"] = p.FinishedDate?.ToString("yyyy-MM-dd");
                    row["定稿日"] = p.FinalizationDate?.ToString("yyyy-MM-dd");
                    row["返稿日"] = p.ReturnDate?.ToString("yyyy-MM-dd");
                    row["案件类型"] = p.CaseType;
                    row["案件状态"] = p.CaseState;
                    row["处理事项备注"] = p.DoItemMemo;
                    row["处理状态"] = p.DoItemState;
                    row["案件名称"] = p.CaseName;
                    row["委案日期"] = p.EntrustingDate?.ToString("yyyy-MM-dd");
                    row["客户期限"] = p.CustomerLimitDate?.ToString("yyyy-MM-dd");
                    row["内部期限"] = p.InternalDate?.ToString("yyyy-MM-dd");
                    row["初稿日"] = p.FirstDraftDate?.ToString("yyyy-MM-dd");
                    row["备注（发文严重超期是否属客观原因，若为否，请填写原因"] = p.OverDueMemo;
                    row["绩效类型"] = p.Type;
                    row["案件备注"] = p.CaseMemo;
                    
                    dt.Rows.Add(row);
                }
            }

            NPOIExcel.DataTableToExcel(dt,$"c:\\temp\\{DateTime.Now.ToString("yyyyMMddhhmmss")}-线下绩效核算数据.xlsx");
        }

        private void _CompareExcel2DB()
        {
           
            DataTable excelDT = NPOIExcel.ExcelToDataTable("c:\\temp\\220112-工程师绩效总表-12月-v2F.xlsx", true, true, 0,2);
            DataTable retTable = new DataTable();
            retTable.Columns.Add("我方文号");
            retTable.Columns.Add("申请类型");
            retTable.Columns.Add("业务类型");
            retTable.Columns.Add("备注（填表注意事项）");
            retTable.Columns.Add("备注（填表注意事项）【系统】");
            retTable.Columns.Add("处理事项");
            retTable.Columns.Add("处理事项【系统】");
            retTable.Columns.Add("案件阶段");
            retTable.Columns.Add("案件阶段【系统】");
            retTable.Columns.Add("案件系数");
            retTable.Columns.Add("案件系数【系统】");
            retTable.Columns.Add("处理事项系数");
            retTable.Columns.Add("处理事项系数【系统】");
            retTable.Columns.Add("处理人等级");
            retTable.Columns.Add("处理人等级【系统】");
            retTable.Columns.Add("处理人系数");
            retTable.Columns.Add("处理人系数【系统】");            
            retTable.Columns.Add("基本点数");
            retTable.Columns.Add("基本点数【系统】");
            retTable.Columns.Add("核稿系数");
            retTable.Columns.Add("核稿系数【系统】");
            retTable.Columns.Add("核稿绩效");
            retTable.Columns.Add("核稿绩效【系统】");
            retTable.Columns.Add("处理人");
            retTable.Columns.Add("处理人【系统】");
            retTable.Columns.Add("核稿人");
            retTable.Columns.Add("核稿人【系统】");
            retTable.Columns.Add("严重超期备注原因");
            retTable.Columns.Add("严重超期备注原因【系统】");

            retTable.Columns.Add("是否一致");
            retTable.Columns.Add("不一致原因");

            spDbContext spDb = new spDbContext();
            List<PerformanceItem> items = spDb.PerformanceItems
                .Include(p=>p.Reviewer).ThenInclude(p=>p.StaffGrade)
                .Include(p=>p.Customer)
                .Include(p=>p.ItemStaffs).ThenInclude(s=>s.DoPerson).ThenInclude(s=>s.StaffGrade)
                .Where(p=>p.CalMonth.Status ==0 && !p.CaseNo.StartsWith("J")).OrderBy(p=>p.CaseNo).ThenBy(p=>p.DoItem).ToList();

            excelDT.DefaultView.Sort = "我方文号,处理事项";
            DataTable temDt = excelDT.DefaultView.ToTable();

            int iTable = 0;
            int iList = 0;

            CalMonth calMonth = spDb.CalMonths.FirstOrDefault(p=>p.Status ==0);
            var verifyCoefficients = spDb.VerifyCoefficients.ToList();
            var Rules = spDb.BasePointRules.ToList();
            while (iTable < temDt.Rows.Count && iList < items.Count)
            {
                System.Diagnostics.Debug.WriteLine($"Excel：{iTable}/{temDt.Rows.Count}\t{iList}/{items.Count}");

                DataRow row = temDt.Rows[iTable];
                PerformanceItem item = items[iList];

                if(row["我方文号"].ToString() == item.CaseNo && (row["处理事项"].ToString() == item.DoItem || (row["备注（填表注意事项）"].ToString()== "发明一次OA授权" && item.DoItem == "发明一次OA授权")))
                {
                    var temRow = retTable.NewRow();

                    int iBegin = iTable;
                    int iEnd = iTable;

                    #region 判断是否有重复记录，并将Excel表中的记录生成临时表中的一行记录
                    if (iEnd < temDt.Rows.Count - 1)
                    {
                        while (temDt.Rows[iEnd]["我方文号"].ToString() == temDt.Rows[iEnd + 1]["我方文号"].ToString() &&
                            temDt.Rows[iEnd]["处理事项"].ToString() == temDt.Rows[iEnd + 1]["处理事项"].ToString())
                        {
                            iEnd++;
                        }
                    }

                    temRow["我方文号"] = temDt.Rows[iTable]["我方文号"];
                    temRow["处理事项"] = temDt.Rows[iTable]["处理事项"];
                    temRow["申请类型"] = temDt.Rows[iTable]["申请类型"];
                    temRow["业务类型"] = temDt.Rows[iTable]["业务类型"];
                    temRow["案件阶段"] = temDt.Rows[iTable]["案件阶段"];
                    temRow["案件系数"] = temDt.Rows[iTable]["案件系数"];
                    temRow["处理事项系数"] = temDt.Rows[iTable]["处理事项系数"];
                    temRow["核稿人"] = temDt.Rows[iTable]["核稿人"];
                    temRow["备注（填表注意事项）"] = temDt.Rows[iTable]["备注（填表注意事项）"];

                    temRow["处理人等级"] = temDt.Rows[iTable]["处理人等级"];
                    temRow["基本点数"] = temDt.Rows[iTable]["基本点数"];
                    temRow["核稿系数"] = temDt.Rows[iTable]["核稿系数"];
                    temRow["处理人"] = temDt.Rows[iTable]["处理人"];
                    
                    temRow["严重超期备注原因"] = temDt.Rows[iTable]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"];

                    for (int i = iBegin; i <= iEnd; i++)
                    {
                        temRow["备注（填表注意事项）"] =string.IsNullOrEmpty(temRow["备注（填表注意事项）"].ToString()) ?temDt.Rows[i]["备注（填表注意事项）"] :$"{temRow["备注（填表注意事项）"]}{temDt.Rows[i]["备注（填表注意事项）"]}";

                        //temRow["基本点数"] = string.IsNullOrEmpty(temRow["基本点数"].ToString()) ? temDt.Rows[i]["基本点数"] : $"{temRow["基本点数"]},{temDt.Rows[i]["基本点数"]}";
                        temRow["核稿系数"] = string.IsNullOrEmpty(temRow["核稿系数"].ToString()) ? temDt.Rows[i]["核稿系数"] : $"{temRow["核稿系数"]},{temDt.Rows[i]["核稿系数"]}";
                        if (!temRow["处理人"].ToString().Contains(temDt.Rows[i]["处理人"].ToString()))
                        {
                            temRow["处理人"] = string.IsNullOrEmpty(temRow["处理人"].ToString()) ? temDt.Rows[i]["处理人"] : $"{temRow["处理人"]},{temDt.Rows[i]["处理人"]}";
                            temRow["处理人等级"] = string.IsNullOrEmpty(temRow["处理人等级"].ToString()) ? temDt.Rows[i]["处理人等级"] : $"{temRow["处理人等级"]},{temDt.Rows[i]["处理人等级"]}";
                        }
                        temRow["严重超期备注原因"] = string.IsNullOrEmpty(temRow["严重超期备注原因"].ToString()) ? temDt.Rows[i]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"] : $"{temRow["严重超期备注原因"]}{temDt.Rows[i]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"]}";
                    }

                    iTable = iEnd;
                    #endregion

                    Utility.Utility.CalBasePoint(item, Rules);

                    List<StaffStatistics> retPoints = new List<StaffStatistics>();
                    try
                    {
                        retPoints = _calItemJX(calMonth, verifyCoefficients, item, spDb);
                    }
                    catch { }
                     
                    
                    temRow["案件阶段【系统】"] = item.CaseStage;
                    temRow["案件系数【系统】"] = item.CaseCoefficient;
                    temRow["处理事项系数【系统】"] = item.DoItemCoefficient;
                    temRow["核稿人【系统】"] = item.Reviewer?.Name;
                    temRow["备注（填表注意事项）【系统】"] = item.AgentFeedbackMemo;
                    temRow["基本点数【系统】"] = item.BasePoint?.ToString();
                    temRow["严重超期备注原因"] = item.OverDueMemo;

                    if (retPoints != null && retPoints.Count > 0)
                    {
                        temRow["核稿绩效【系统】"] = retPoints.FirstOrDefault(p => p.StaffId == item.ReviewerId && p.jxType.Contains("审核"))?.totalBasePoint;

                        foreach (var itemStaff in item.ItemStaffs)
                        {
                            //temRow["基本点数【系统】"] = string.IsNullOrEmpty(temRow["基本点数【系统】"].ToString()) ? retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint.ToString() : $"{temRow["基本点数【系统】"]},{retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint}";

                            temRow["处理人等级【系统】"] = string.IsNullOrEmpty(temRow["处理人等级【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Grade : $"{temRow["处理人等级【系统】"]},{itemStaff.DoPerson.StaffGrade?.Grade }";
                            temRow["处理人【系统】"] = string.IsNullOrEmpty(temRow["处理人【系统】"].ToString()) ? itemStaff.DoPerson.Name : $"{temRow["处理人【系统】"]},{itemStaff.DoPerson.Name}";

                            if (item.ReviewerId != null)
                            {
                                #region 取审核人等级审核等级系数
                                VerifyCoefficient vcoefficient
                                    = verifyCoefficients.Where<VerifyCoefficient>(v =>
                                        v.CheckerId == item.Reviewer.StaffGradeId
                                        && v.DoPersonId == itemStaff.DoPerson.StaffGradeId)
                                .FirstOrDefault<VerifyCoefficient>();
                                #endregion

                                if (vcoefficient != null)
                                {
                                    temRow["核稿系数【系统】"] = string.IsNullOrEmpty(temRow["核稿系数【系统】"].ToString()) ? vcoefficient.Coefficient.ToString() : $"{temRow["核稿系数【系统】"]},{vcoefficient.Coefficient}";
                                }
                            }

                        }
                    }

                    temRow["是否一致"] = "";
                    temRow["不一致原因"] = "";

                    if(temRow["案件阶段【系统】"].ToString().Trim() != temRow["案件阶段"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "案件阶段" : $"{temRow["不一致原因"]},案件阶段";
                    }
                    if (temRow["案件系数【系统】"].ToString().Trim() != temRow["案件系数"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "案件系数" : $"{temRow["不一致原因"]},案件系数";
                    }
                    if (temRow["处理事项系数【系统】"].ToString().Trim() != temRow["处理事项系数"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "处理事项系数" : $"{temRow["不一致原因"]},处理事项系数";
                    }
                    if (temRow["核稿人【系统】"].ToString().Trim() != temRow["核稿人"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "核稿人" : $"{temRow["不一致原因"]},核稿人";
                    }

                    if (temRow["基本点数【系统】"].ToString().Trim() != temRow["基本点数"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "基本点数" : $"{temRow["不一致原因"]},基本点数";
                    }
                    if (temRow["严重超期备注原因【系统】"].ToString().Trim() != temRow["严重超期备注原因"].ToString().Trim())
                    {
                        temRow["不一致原因"] = string.IsNullOrEmpty(temRow["不一致原因"].ToString()) ? "严重超期备注原因" : $"{temRow["不一致原因"]},严重超期备注原因";
                    }

                    if (!string.IsNullOrEmpty(temRow["不一致原因"].ToString()))
                    {
                        temRow["是否一致"] = "不一致";
                    }

                    retTable.Rows.Add(temRow);

                    iTable++;
                    iList++;
                }
                else
                {
                    string strDT = $"{row["我方文号"]}-{row["处理事项"]}";
                    string strList = $"{item.CaseNo}-{item.DoItem}";

                    if (strDT.CompareTo(strList) > 0)
                    {
                        var temRow = retTable.NewRow();

                        Utility.Utility.CalBasePoint(item, Rules);

                        List<StaffStatistics> retPoints = new List<StaffStatistics>();
                        try
                        {
                            retPoints = _calItemJX(calMonth, verifyCoefficients, item, spDb);
                        }
                        catch { }

                        temRow["我方文号"] = item.CaseNo;
                        temRow["处理事项"] = item.DoItem;
                        temRow["申请类型"] = item.ApplicationType;
                        temRow["业务类型"] = item.BusinessType;
                        temRow["案件阶段【系统】"] = item.CaseStage;
                        temRow["案件系数【系统】"] = item.CaseCoefficient;
                        temRow["处理事项系数【系统】"] = item.DoItemCoefficient;
                        temRow["核稿人【系统】"] = item.Reviewer?.Name;
                        temRow["备注（填表注意事项）【系统】"] = item.AgentFeedbackMemo;
                        temRow["基本点数【系统】"] = item.BasePoint?.ToString();
                        temRow["严重超期备注原因"] = item.OverDueMemo;

                        if (item.ReviewerId != null)
                        {
                            temRow["核稿绩效【系统】"] = retPoints.FirstOrDefault(p => p.StaffId == item.ReviewerId)?.totalBasePoint;
                        }

                        foreach (var itemStaff in item.ItemStaffs)
                        {
                            //if (itemStaff.DoPerson.Status != "试用期")
                            //{
                            //    if (retPoints != null && retPoints.Count > 0)
                            //    {
                            //        temRow["基本点数【系统】"] = string.IsNullOrEmpty(temRow["基本点数【系统】"].ToString()) ? retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint.ToString() : $"{temRow["基本点数【系统】"]},{retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint}";
                            //    }
                            //}


                            temRow["处理人等级【系统】"] = string.IsNullOrEmpty(temRow["处理人等级【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Grade : $"{temRow["处理人等级【系统】"]},{itemStaff.DoPerson.StaffGrade?.Grade }";
                            temRow["处理人系数【系统】"] = string.IsNullOrEmpty(temRow["处理人系数【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Coefficient : $"{temRow["处理人系数【系统】"]},{itemStaff.DoPerson.StaffGrade?.Coefficient}";
                            temRow["处理人【系统】"] = string.IsNullOrEmpty(temRow["处理人【系统】"].ToString()) ? itemStaff.DoPerson.Name : $"{temRow["处理人【系统】"]},{itemStaff.DoPerson.Name}";

                            if (item.ReviewerId != null)
                            {
                                #region 取审核人等级审核等级系数
                                VerifyCoefficient vcoefficient
                                    = verifyCoefficients.Where<VerifyCoefficient>(v =>
                                        v.CheckerId == item.Reviewer.StaffGradeId
                                        && v.DoPersonId == itemStaff.DoPerson.StaffGradeId)
                                .FirstOrDefault<VerifyCoefficient>();
                                #endregion

                                if (vcoefficient != null)
                                {
                                    temRow["核稿系数【系统】"] = string.IsNullOrEmpty(temRow["核稿系数【系统】"].ToString()) ? vcoefficient.Coefficient.ToString() : $"{temRow["核稿系数【系统】"]},{vcoefficient.Coefficient}";
                                }
                            }

                        }

                        temRow["是否一致"] = "不一致";
                        temRow["不一致原因"] = "Excel中没有，系统中有";

                        retTable.Rows.Add(temRow);

                        iList++;
                    }
                    else
                    {
                        var temRow = retTable.NewRow();

                        int iBegin = iTable;
                        int iEnd = iTable;

                        #region 判断是否有重复记录，并将Excel表中的记录生成临时表中的一行记录
                        if (iEnd < temDt.Rows.Count - 1)
                        {
                            while (temDt.Rows[iEnd]["我方文号"].ToString() == temDt.Rows[iEnd + 1]["我方文号"].ToString() &&
                            temDt.Rows[iEnd]["处理事项"].ToString() == temDt.Rows[iEnd + 1]["处理事项"].ToString())
                            {
                                iEnd++;
                            }
                        }

                        temRow["我方文号"] = temDt.Rows[iTable]["我方文号"];
                        temRow["处理事项"] = temDt.Rows[iTable]["处理事项"];
                        temRow["申请类型"] = temDt.Rows[iTable]["申请类型"];
                        temRow["业务类型"] = temDt.Rows[iTable]["业务类型"];
                        temRow["案件阶段"] = temDt.Rows[iTable]["案件阶段"];
                        temRow["案件系数"] = temDt.Rows[iTable]["案件系数"];
                        temRow["处理事项系数"] = temDt.Rows[iTable]["处理事项系数"];
                        temRow["核稿人"] = temDt.Rows[iTable]["核稿人"];
                        temRow["备注（填表注意事项）"] = temDt.Rows[iTable]["备注（填表注意事项）"];

                        temRow["处理人等级"] = temDt.Rows[iTable]["处理人等级"];
                        temRow["基本点数"] = temDt.Rows[iTable]["基本点数"];
                        temRow["核稿系数"] = temDt.Rows[iTable]["核稿系数"];
                        temRow["核稿绩效"] = temDt.Rows[iTable]["核稿绩效"];
                        temRow["处理人"] = temDt.Rows[iTable]["处理人"];
                        
                        temRow["严重超期备注原因"] = temDt.Rows[iTable]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"];

                        for (int i = iBegin+1; i <= iEnd; i++)
                        {
                            temRow["备注（填表注意事项）"] = string.IsNullOrEmpty(temRow["备注（填表注意事项）"].ToString()) ? temDt.Rows[i]["备注（填表注意事项）"] : $"{temRow["备注（填表注意事项）"]}{temDt.Rows[i]["备注（填表注意事项）"]}";

                            //temRow["基本点数"] = string.IsNullOrEmpty(temRow["基本点数"].ToString()) ? temDt.Rows[i]["基本点数"] : $"{temRow["基本点数"]},{temDt.Rows[i]["基本点数"]}";

                            temRow["核稿绩效"] = string.IsNullOrEmpty(temRow["核稿绩效"].ToString()) ? temDt.Rows[i]["核稿绩效"] : $"{temRow["核稿绩效"]},{temDt.Rows[i]["核稿绩效"]}";
                            if (!temRow["处理人"].ToString().Contains(temDt.Rows[i]["处理人"].ToString()))
                            {
                                temRow["处理人"] = string.IsNullOrEmpty(temRow["处理人"].ToString()) ? temDt.Rows[i]["处理人"] : $"{temRow["处理人"]},{temDt.Rows[i]["处理人"]}";
                                temRow["处理人等级"] = string.IsNullOrEmpty(temRow["处理人等级"].ToString()) ? temDt.Rows[i]["处理人等级"] : $"{temRow["处理人等级"]},{temDt.Rows[i]["处理人等级"]}";
                            }
                            temRow["严重超期备注原因"] = string.IsNullOrEmpty(temRow["严重超期备注原因"].ToString()) ? temDt.Rows[i]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"] : $"{temRow["严重超期备注原因"]}{temDt.Rows[i]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"]}";
                        }

                        iTable = iEnd;
                        #endregion

                        temRow["是否一致"] = "不一致";
                        temRow["不一致原因"] = "系统中没有，Excel中有";
                        retTable.Rows.Add(temRow);
                        iTable++;
                    }

                }
            }

            if (iList <=items.Count)
            {
                while(iList <items.Count){
                    var item = items[iList];
                    var temRow = retTable.NewRow();

                    Utility.Utility.CalBasePoint(item, Rules);

                    List<StaffStatistics> retPoints = new List<StaffStatistics>();
                    try
                    {
                        retPoints = _calItemJX(calMonth, verifyCoefficients, item, spDb);
                    }
                    catch { }

                    temRow["我方文号"] = item.CaseNo;
                    temRow["处理事项"] = item.DoItem;
                    temRow["申请类型"] = item.ApplicationType;
                    temRow["业务类型"] = item.BusinessType;
                    temRow["案件阶段【系统】"] = item.CaseStage;
                    temRow["案件系数【系统】"] = item.CaseCoefficient;
                    temRow["处理事项系数【系统】"] = item.DoItemCoefficient;
                    temRow["核稿人【系统】"] = item.Reviewer?.Name;
                    temRow["备注（填表注意事项）【系统】"] = item.AgentFeedbackMemo;
                    temRow["基本点数【系统】"] = "";// item.BasePoint?.ToString();
                    temRow["严重超期备注原因"] = item.OverDueMemo;

                    if (item.ReviewerId != null)
                    {
                        temRow["核稿绩效【系统】"] = retPoints.FirstOrDefault(p => p.StaffId == item.ReviewerId)?.totalBasePoint;
                    }

                    foreach (var itemStaff in item.ItemStaffs)
                    {
                        //if (itemStaff.DoPerson.Status != "试用期")
                        //{
                        //    if (retPoints != null && retPoints.Count > 0)
                        //    {
                        //        temRow["基本点数【系统】"] = string.IsNullOrEmpty(temRow["基本点数【系统】"].ToString()) ? retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint.ToString() : $"{temRow["基本点数【系统】"]},{retPoints.FirstOrDefault(p => p.StaffId == itemStaff.DoPersonId)?.totalBasePoint}";
                        //    }
                        //}


                        temRow["处理人等级【系统】"] = string.IsNullOrEmpty(temRow["处理人等级【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Grade : $"{temRow["处理人等级【系统】"]},{itemStaff.DoPerson.StaffGrade?.Grade }";
                        temRow["处理人系数【系统】"] = string.IsNullOrEmpty(temRow["处理人系数【系统】"].ToString()) ? itemStaff.DoPerson.StaffGrade?.Coefficient : $"{temRow["处理人系数【系统】"]},{itemStaff.DoPerson.StaffGrade?.Coefficient}";
                        temRow["处理人【系统】"] = string.IsNullOrEmpty(temRow["处理人【系统】"].ToString()) ? itemStaff.DoPerson.Name : $"{temRow["处理人【系统】"]},{itemStaff.DoPerson.Name}";

                        if (item.ReviewerId != null)
                        {
                            #region 取审核人等级审核等级系数
                            VerifyCoefficient vcoefficient
                                = verifyCoefficients.Where<VerifyCoefficient>(v =>
                                    v.CheckerId == item.Reviewer.StaffGradeId
                                    && v.DoPersonId == itemStaff.DoPerson.StaffGradeId)
                            .FirstOrDefault<VerifyCoefficient>();
                            #endregion

                            if (vcoefficient != null)
                            {
                                temRow["核稿系数【系统】"] = string.IsNullOrEmpty(temRow["核稿系数【系统】"].ToString()) ? vcoefficient.Coefficient.ToString() : $"{temRow["核稿系数【系统】"]},{vcoefficient.Coefficient}";
                            }
                        }

                    }

                    temRow["是否一致"] = "不一致";
                    temRow["不一致原因"] = "Excel中没有，系统中有";

                    iList++;
                    retTable.Rows.Add(temRow);

                }
            }
            else
            {
                while (iTable  < temDt.Rows.Count)
                {
                    var temRow = retTable.NewRow();

                    int iBegin = iTable;
                    int iEnd = iTable;

                    #region 判断是否有重复记录，并将Excel表中的记录生成临时表中的一行记录
                    while (temDt.Rows[iTable]["我方文号"].ToString() == temDt.Rows[iTable + 1]["我方文号"].ToString() &&
                        temDt.Rows[iTable]["处理事项"].ToString() == temDt.Rows[iTable + 1]["处理事项"].ToString())
                    {
                        iEnd++;
                    }

                    temRow["我方文号"] = temDt.Rows[iTable]["我方文号"];
                    temRow["处理事项"] = temDt.Rows[iTable]["处理事项"];
                    temRow["申请类型"] = temDt.Rows[iTable]["申请类型"];
                    temRow["业务类型"] = temDt.Rows[iTable]["业务类型"];
                    temRow["案件阶段"] = temDt.Rows[iTable]["案件阶段"];
                    temRow["案件系数"] = temDt.Rows[iTable]["案件系数"];
                    temRow["处理事项系数"] = temDt.Rows[iTable]["处理事项系数"];
                    temRow["核稿人"] = temDt.Rows[iTable]["核稿人"];
                    temRow["备注（填表注意事项）"] = temDt.Rows[iTable]["备注（填表注意事项）"];

                    temRow["处理人等级"] = temDt.Rows[iTable]["处理人等级"];
                    temRow["基本点数"] = temDt.Rows[iTable]["基本点数"];
                    temRow["核稿系数"] = temDt.Rows[iTable]["核稿系数"];
                    temRow["核稿绩效"] = temDt.Rows[iTable]["核稿绩效"];
                    temRow["处理人"] = temDt.Rows[iTable]["处理人"];

                    temRow["严重超期备注原因"] = temDt.Rows[iTable]["备注（发文严重超期是否属客观原因，若为否，请填写原因）"];

                    for (int i = iBegin + 1; i <= iEnd; i++)
                    {
                        temRow["备注（填表注意事项）"] = string.IsNullOrEmpty(temRow["备注（填表注意事项）"].ToString()) ? temDt.Rows[i]["备注（填表注意事项）"] : $"{temRow["备注（填表注意事项）"]}{temDt.Rows[i]["备注（填表注意事项）"]}";

                        //temRow["基本点数"] = string.IsNullOrEmpty(temRow["基本点数"].ToString()) ? temDt.Rows[i]["基本点数"] : $"{temRow["基本点数"]},{temDt.Rows[i]["基本点数"]}";

                        temRow["核稿绩效"] = string.IsNullOrEmpty(temRow["核稿绩效"].ToString()) ? temDt.Rows[i]["核稿绩效"] : $"{temRow["核稿绩效"]},{temDt.Rows[i]["核稿绩效"]}";

                        if (!temRow["处理人"].ToString().Contains(temDt.Rows[i]["处理人"].ToString()))
                        {
                            temRow["处理人"] = string.IsNullOrEmpty(temRow["处理人"].ToString()) ? temDt.Rows[i]["处理人"] : $"{temRow["处理人"]},{temDt.Rows[i]["处理人"]}";
                            temRow["处理人等级"] = string.IsNullOrEmpty(temRow["处理人等级"].ToString()) ? temDt.Rows[i]["处理人等级"] : $"{temRow["处理人等级"]},{temDt.Rows[i]["处理人等级"]}";
                        }

                        temRow["严重超期备注原因"] = string.IsNullOrEmpty(temRow["严重超期备注原因"].ToString()) ? temDt.Rows[i]["严重超期备注原因"] : $"{temRow["严重超期备注原因"]}{temDt.Rows[i]["严重超期备注原因"]}";
                    }

                    iTable = iEnd;
                    #endregion

                    temRow["是否一致"] = "不一致";
                    temRow["不一致原因"] = "系统中没有，Excel中有";
                    retTable.Rows.Add(temRow);
                    iTable++;
                }
            }


            NPOIExcel.DataTableToExcel(retTable, "c:\\temp\\202112-系统线下绩效记录对比.xlsx");

            
        }

        public List<StaffStatistics> CalItemJX(int itemid)
        {
            var Item = Context.PerformanceItems.Include(p=>p.CalMonth).FirstOrDefault(p=>p.Id == itemid);
            List<VerifyCoefficient> verifyCoefficients = Context.VerifyCoefficients.ToList<VerifyCoefficient>();
            
            return _calItemJX(Item.CalMonth,verifyCoefficients,Item,Context);

        }

        private List<StaffStatistics> _calItemJX(CalMonth calMonth, List<VerifyCoefficient> verifyCoefficients, PerformanceItem item,spDbContext spDb)
        {
            
            System.Collections.Hashtable doPersonsBL = new System.Collections.Hashtable();

            bool isPJFP = true;
            double total = item.ItemStaffs.Count();
            if (item.ItemStaffs.Where<ItemStaff>(p => p.PerformancePoint != null || p.PerformancePoint == 0).Count() > 0)
            {
                total = item.ItemStaffs.Select(i => i.PerformancePoint.Value).Sum();
                isPJFP = false;
            }

            List<StaffStatistics> itemStatistics = new List<StaffStatistics>();

            if (item.ReviewerId  != null)
            {
                item.Reviewer  = spDb.Staffs.Include(s => s.StaffGrade).FirstOrDefault(p => p.Id == item.ReviewerId);
                //spDb.Entry(item.Reviewer).Reference(b => b.StaffGrade).Load();
            }

            foreach (ItemStaff itemStaff in item.ItemStaffs)
            {
                if(itemStaff.DoPerson == null)
                {
                    itemStaff.DoPerson = spDb.Staffs.Include(s=>s.StaffGrade).FirstOrDefault(p=>p.Id==itemStaff.DoPersonId);
                }
                //spDb.Entry(itemStaff).Reference(b => b.DoPerson).Load();
                //spDb.Entry(itemStaff.DoPerson).Reference(b => b.StaffGrade).Load();

                #region 计算审核人绩效点数，核稿人绩效点数按照核稿人与个处理人的核稿系数计算后加总，没有找到核稿系数（比如同级别），核稿系数为0
                if (item.ReviewerId != null && item.Type != "专案" && 
                    item.ItemStaffs.FirstOrDefault(s=>s.DoPersonId == item.ReviewerId) == null)
                {

                    #region 取审核人等级审核等级系数
                    VerifyCoefficient vcoefficient
                        = verifyCoefficients.Where<VerifyCoefficient>(v =>
                            v.CheckerId == item.Reviewer.StaffGradeId
                            && v.DoPersonId == itemStaff.DoPerson.StaffGradeId)
                    .FirstOrDefault<VerifyCoefficient>();
                    #endregion

                    if (vcoefficient != null)
                    {
                        double reviewerBasePoint = item.BasePoint.Value * vcoefficient.Coefficient;


                        string temJxType = $"{item.Type}审核";

                        var temReviewerStatic = itemStatistics.Where<StaffStatistics>(s => s.StaffId == item.ReviewerId && s.jxType == temJxType && s.CalMonth.Id == calMonth.Id).FirstOrDefault();
                        if (temReviewerStatic != null)
                        {
                            temReviewerStatic.totalBasePoint += reviewerBasePoint;
                        }
                        else
                        {
                            if (item.Reviewer.IsOnJob && itemStaff.DoPerson.Status != "试用期")  //判断是否在职
                            {
                                temReviewerStatic = new StaffStatistics()
                                {
                                    CalMonth = calMonth,
                                    CalMonthId = calMonth.Id,
                                    StaffId = item.ReviewerId.Value,
                                    totalBasePoint = reviewerBasePoint,
                                    jxType = temJxType
                                };

                                itemStatistics.Add(temReviewerStatic);
                            }
                        }
                    }
                }
                #endregion

                #region 计算各处理人的绩效点数
                double handlerBasePoint;
                if (item.Type != "专案")
                {
                    if (isPJFP)
                    {
                        handlerBasePoint = item.BasePoint.Value * 1.0 / total;
                    }
                    else
                    {
                        handlerBasePoint = item.BasePoint.Value * itemStaff.PerformancePoint.Value / total;
                    }
                }
                else
                {
                    if (itemStaff.PerformancePoint == null)
                    {
                        if (isPJFP)
                        {
                            handlerBasePoint = item.BasePoint.Value * 1.0 / total;
                        }
                        else
                        {
                            handlerBasePoint = item.BasePoint.Value * itemStaff.PerformancePoint.Value / total;
                        }
                    }
                    else
                    {
                        handlerBasePoint = itemStaff.PerformancePoint.Value;
                    }
                }

                string handlerJxType = $"{item.Type}处理";
                var temStatic = itemStatistics.Where<StaffStatistics>(s => s.StaffId == itemStaff.DoPersonId && s.jxType == handlerJxType && s.CalMonth.Id == calMonth.Id).FirstOrDefault();
                if (temStatic != null)
                {
                    if (item.Type != "专案")
                    {
                        temStatic.totalBasePoint += handlerBasePoint * itemStaff.DoPerson.StaffGrade.Coefficient;
                    }
                    else
                    {
                        temStatic.totalBasePoint += handlerBasePoint;
                    }
                }
                else
                {
                    if (itemStaff.DoPerson.StaffGradeId  != null && itemStaff.DoPerson.IsOnJob)
                    {
                        if (item.Type != "专案")
                        {
                            if (itemStaff.DoPerson.Status == "试用期" && item.Reviewer != null)
                            {
                                temStatic = new StaffStatistics()
                                {
                                    CalMonth = calMonth,
                                    CalMonthId = calMonth.Id,
                                    StaffId = item.Reviewer.Id,
                                    totalBasePoint = handlerBasePoint * item.Reviewer.StaffGrade.Coefficient,
                                    jxType = handlerJxType
                                };


                                itemStatistics.Add(temStatic);
                            }
                            else
                            {
                                itemStaff.DoPerson.StaffGrade = spDb.StaffGrades.FirstOrDefault(s=>s.Id == itemStaff.DoPerson.StaffGradeId);
                                temStatic = new StaffStatistics()
                                {
                                    CalMonth = calMonth,
                                    CalMonthId = calMonth.Id,
                                    StaffId = itemStaff.DoPersonId,
                                    totalBasePoint = handlerBasePoint * itemStaff.DoPerson.StaffGrade.Coefficient,
                                    jxType = handlerJxType
                                };


                                itemStatistics.Add(temStatic);
                            }
                        }
                        else
                        {
                            temStatic = new StaffStatistics()
                            {
                                CalMonth = calMonth,
                                CalMonthId = calMonth.Id,
                                StaffId = itemStaff.DoPersonId,
                                totalBasePoint = handlerBasePoint,
                                jxType = handlerJxType
                            };


                            itemStatistics.Add(temStatic);
                        }
                    }
                }
                #endregion
            }

            return itemStatistics;
        }

        public ApiSaveResponse FinishedCalMonth(int year,int month)
        {
            CalMonth calMonth = Context.CalMonths.Where<CalMonth>(c => c.Month == month && c.Year == year).FirstOrDefault();

            if (calMonth != null || calMonth.Status !=4)
            {
                var retList = _CalMyStatistics(calMonth);

                using (var t = Context.Database.BeginTransaction())
                {
                    try
                    {
                        Context.SaveChanges();
                        foreach (var obj in retList)
                        {
                            obj.CalMonthId = calMonth.Id;
                            obj.CalMonth = null;
                            Context.StaffStatistics.Add(obj);
                            Context.SaveChanges();
                        }

                        calMonth.Status = 4;
                        Context.SaveChanges();
                        t.Commit();
                        return new ApiSaveResponse()
                        {
                            Success = true
                        };
                        
                    }
                    catch(Exception ex)
                    {
                        t.Rollback();
                        return new ApiSaveResponse()
                        {
                            Success = false,
                            ErrorMessage = ex.Message
                        };
                    }
                }
                    
            }
            else
            {
                return new ApiSaveResponse()
                {
                    Success = false,
                    ErrorMessage ="指定月份没有数据或者已归档!"
                };
            }

        }

        /// <summary>
        /// 计算指定用户，指定年月的绩效统计信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public List<StaffStatistics> CalMyStatistics(int year,int month, int? userid=null)
        {
            object retList;
            string strKey = $"CalMyStatistics:{year}-{month}-{userid}";
            
            if(!MyMemoryCache.TryGetValue(strKey,out retList))
            {
                CalMonth calMonth = Context.CalMonths.Where<CalMonth>(c => c.Month == month && c.Year == year).FirstOrDefault();

                if (calMonth == null)
                {
                    return null;
                }
                else
                {
                    if (calMonth.Status == 4)
                    {
                        //已归档，归档数据库中直接取出记录
                        if (userid == null)
                        {
                            retList = Context.StaffStatistics.Where<StaffStatistics>(s => s.CalMonthId == calMonth.Id).ToList<StaffStatistics>();
                        }
                        else
                        {
                            retList = Context.StaffStatistics.Where<StaffStatistics>(s => s.CalMonthId == calMonth.Id && s.StaffId == userid).ToList<StaffStatistics>();
                        }
                    }
                    else
                    {
                        try
                        {
                            retList = _CalMyStatistics(calMonth, userid);
                        }
                        catch(Exception ex)
                        {
                            StreamWriter sw = System.IO.File.AppendText("c:\\temp\\log.txt");
                            sw.WriteLine($"{ex.Message}");
                            sw.Flush();
                            sw.Close();
                            sw.Dispose();
                        }
                    }

                    // Set cache options.
                    var cacheEntryOptions = new MemoryCacheEntryOptions()
                        // Keep in cache for this time, reset time if accessed.
                        .SetSlidingExpiration(TimeSpan.FromHours(1));

                    foreach(var temObj in (List<StaffStatistics>)retList)
                    {
                        temObj.CalMonth.PerformanceItems = null;
                    }

                    // Save data in cache.
                    MyMemoryCache.SetValue(strKey, retList);



                    return (List<StaffStatistics>)retList;
                }
            }
            else
            {
                var temList = (List<StaffStatistics>)retList;
                foreach (var temObj in temList)
                {
                    temObj.CalMonth.PerformanceItems = null;
                }

                return temList;
            }
           
        }
        
        private string GetExpress(IList<FieldCondition> conditions)
        {
            string str = "";

            foreach(var c in conditions)
            {
                if (string.IsNullOrEmpty(str))
                {
                    str = c.ToExpressString("s");
                }
                else
                {
                    if(c.LogicOperate == LogicEnum.And)
                    {
                        str = $"({str}) && {c.ToExpressString("s")}";
                    }
                    else
                    {
                        str = $"({str}) || {c.ToExpressString("s")}";
                    }
                }
            }

            return str;
        }

        [HttpGet,HttpPost]
        public FileProcessTask ExportData(QueryFilter queryFilter)
        {
            var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-绩效数据下载.xlsx";
            var attachfileSavePath = utility.ConfigHelper.GetSectionValue("AttachFileSavePath");
            var filePath = Path.Combine(attachfileSavePath, filename);

            var fileTask = new FileProcessTask()
            {
                Id = Guid.NewGuid().ToString(),
                FileName = filename,
                FilePath = filePath,
                Processed = 0
            };

            fileTaskService.Add(fileTask);
            ThreadObject threadObject = new ThreadObject()
            {
                queryFilter = queryFilter,
                fileTask = fileTask
            };

            System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(ExportDataThread));
            t.Start(threadObject);

            return fileTask;
        }

        internal class ThreadObject
        {
            public QueryFilter queryFilter { get; set; }
            public FileProcessTask fileTask { get; set; }
        }
        
        private void ExportDataThread(object  tObj)
        {
            QueryFilter queryFilter = ((ThreadObject)tObj).queryFilter;
            FileProcessTask fileTask = ((ThreadObject)tObj).fileTask;

            IQueryable<PerformanceItem> response = NewMethod(queryFilter);

            var retList = response
                .Include(p=>p.Customer)
                .Include(p=>p.ItemStaffs).ThenInclude(p=>p.DoPerson).ThenInclude(p=>p.StaffGrade)
                .Include(p=>p.Reviewer).ThenInclude(p=>p.StaffGrade)
                .Include(p=>p.PreOastaff)
                .Include(p=>p.CalMonth)
                .ToList<PerformanceItem>();

            

            DataTable dt = new DataTable();
            #region 添加栏位
            dt.Columns.Add("我方文号",typeof(string));
            dt.Columns.Add("申请类型", typeof(string));
            dt.Columns.Add("业务类型", typeof(string));
            dt.Columns.Add("备注（填表注意事项）", typeof(string));
            dt.Columns.Add("处理事项", typeof(string));
            dt.Columns.Add("案件阶段", typeof(string));
            dt.Columns.Add("案件系数", typeof(string));
            dt.Columns.Add("处理事项系数", typeof(string));
            dt.Columns.Add("前一次OA处理事项系数", typeof(string));
            dt.Columns.Add("前一次OA处理人", typeof(string));
            dt.Columns.Add("处理人等级", typeof(string));
            dt.Columns.Add("基本点数", typeof(string));
            dt.Columns.Add("核稿系数", typeof(string));
            dt.Columns.Add("核稿绩效", typeof(string));
            dt.Columns.Add("处理人", typeof(string));
            dt.Columns.Add("核稿人", typeof(string));
            dt.Columns.Add("客户名称", typeof(string));
            dt.Columns.Add("申请人", typeof(string));
            dt.Columns.Add("处理事项完成日", typeof(string));
            dt.Columns.Add("定稿日", typeof(string));
            dt.Columns.Add("返稿日", typeof(string));
            dt.Columns.Add("案件类型", typeof(string));
            dt.Columns.Add("案件状态", typeof(string));
            dt.Columns.Add("处理事项备注", typeof(string));
            dt.Columns.Add("处理状态", typeof(string));
            dt.Columns.Add("案件名称", typeof(string));
            dt.Columns.Add("委案日期", typeof(string));
            dt.Columns.Add("客户期限", typeof(string));
            dt.Columns.Add("内部期限", typeof(string));
            dt.Columns.Add("初稿日", typeof(string));
            dt.Columns.Add("备注（发文严重超期是否属客观原因，若为否，请填写原因）", typeof(string));
            dt.Columns.Add("备注", typeof(string));
            #endregion

            List<VerifyCoefficient> verifyCoefficients = new spDbContext().VerifyCoefficients.ToList();
            fileTask.Size = retList.Count;

            foreach (var item in retList)
            {
                fileTask.Processed += 1;

                if (item.CaseNo.StartsWith("J"))
                {
                    continue;
                }

                try
                {
                    var row = dt.NewRow();
                    row["我方文号"] = item.CaseNo;
                    row["申请类型"] = item.ApplicationType;
                    row["业务类型"] = item.BusinessType;
                    row["备注（填表注意事项）"] = item.AgentFeedbackMemo;
                    row["处理事项"] = item.DoItem;
                    row["案件阶段"] = item.CaseStage;
                    row["案件系数"] = item.CaseCoefficient;
                    row["处理事项系数"] = item.DoItemCoefficient;
                    row["前一次OA处理事项系数"] = "";

                    if (item.PreOastaffId.HasValue)
                    {
                        row["前一次OA处理人"] = item.PreOastaff?.Name;
                    }

                    string strISLevels = "";
                    string strISNames = "";

                    foreach (var istaff in item.ItemStaffs)
                    {
                        strISLevels = string.IsNullOrEmpty(strISLevels)?istaff.DoPerson.StaffGrade?.Grade : $"{strISLevels},{istaff.DoPerson.StaffGrade?.Grade}";
                        strISNames = string.IsNullOrEmpty(strISNames)?istaff.DoPerson.Name : $"{strISNames},{istaff.DoPerson.Name}";
                    }

                    row["处理人等级"] = strISLevels;
                    row["基本点数"] = item.BasePoint;
                    row["处理人"] = strISNames;
                    row["核稿人"] = item.Reviewer?.Name;

                    if (item.ReviewerId != null && item.BasePoint.HasValue)
                    {
                        var jxList = _calItemJX(item.CalMonth, verifyCoefficients, item, new spDbContext());

                        row["核稿系数"] = "";
                        var temJx = jxList.FirstOrDefault<StaffStatistics>(s => s.jxType.Contains("审核") && s.StaffId == item.ReviewerId);
                        if (temJx != null)
                        {
                            row["核稿绩效"] = temJx.totalBasePoint;
                        }
                    }

                    row["客户名称"] = item.Customer?.Name;
                    row["申请人"] = item.ApplicationName;
                    row["处理事项完成日"] = item.FinishedDate?.ToString("yyyy-MM-dd");
                    row["定稿日"] = item.FinalizationDate?.ToString("yyyy-MM-dd");
                    row["返稿日"] = item.ReturnDate?.ToString("yyyy-MM-dd");
                    row["案件类型"] = item.CaseType;
                    row["案件状态"] = item.CaseState;
                    row["处理事项备注"] = item.DoItemState;
                    row["处理状态"] = item.DoItemState;
                    row["案件名称"] = item.CaseName;
                    row["委案日期"] = item.EntrustingDate?.ToString("yyyy-MM-dd");
                    row["客户期限"] = item.CustomerLimitDate?.ToString("yyyy-MM-dd");
                    row["内部期限"] = item.InternalDate?.ToString("yyyy-MM-dd"); ;
                    row["初稿日"] = item.FirstDraftDate?.ToString("yyyy-MM-dd");
                    row["备注（发文严重超期是否属客观原因，若为否，请填写原因）"] = item.OverDueMemo;
                    row["备注"] = item.DoItemMemo;

                    dt.Rows.Add(row);
                }
                catch(Exception ex)
                {
                    throw ex;
                }
            }
           
            utility.NPOIExcel.DataTableToExcel(dt,fileTask.FilePath);


            fileTask.Finished = true;
        }

        [HttpPost]
        public ListApiResponse<PerformanceItem> QueryFilter(QueryFilter queryFilter)
        {

            ListApiResponse<PerformanceItem> ret = new ListApiResponse<PerformanceItem>();

            IQueryable<PerformanceItem> response = NewMethod(queryFilter);

            int totals = response.ToList<PerformanceItem>().Count;

            if (totals > 0 && totals < (queryFilter.PageIndex - 1) * queryFilter.PageSize)
            {
                response = response
                    .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                    .Include(pi => pi.Reviewer)
                    .Include(pi => pi.Customer)
                    .Include(pi => pi.CalMonth)
                    .OrderConditions<PerformanceItem>(queryFilter.Sorts);
                    //.Pager<PerformanceItem>(1, queryFilter.PageSize, out totals);
            }
            else
            {
                response = response
                    .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                    .Include(pi => pi.Reviewer)
                    .Include(pi => pi.Customer)
                    .Include(pi => pi.CalMonth)
                    .OrderConditions<PerformanceItem>(queryFilter.Sorts);
                    //.Pager<PerformanceItem>(queryFilter.PageIndex, queryFilter.PageSize, out totals);
            }
            ret.TotalCount = totals;



            var retList = response.ToList<PerformanceItem>().Skip((queryFilter.PageIndex-1) *queryFilter.PageSize).Take(queryFilter.PageSize);


            #region 将某些属性设为null，避免循环取值造成返回json过大
            foreach (PerformanceItem item in retList)
            {

                foreach (ItemStaff itemStaff in item.ItemStaffs)
                {
                    itemStaff.DoPerson.ItemStaffs = null;
                    itemStaff.DoPerson.ReviewerItems = null;
                    itemStaff.Item = null;
                }

                if (item.Reviewer != null)
                {
                    item.Reviewer.ReviewerItems = null;
                    item.Reviewer.Customers = null;
                    item.Reviewer.ItemStaffs = null;
                }

                if (item.Customer != null)
                {
                    item.Customer.PerformanceItems = null;
                }

                if (item.CalMonth != null)
                {
                    item.CalMonth.PerformanceItems = null;
                }
            }
            #endregion

            ret.Results = retList.ToList();

            return ret;
        }

        private IQueryable<PerformanceItem> NewMethod(QueryFilter queryFilter)
        {
            string strExpress = "";

            string strCalMonth = "";
            if (queryFilter.CalMonthId.HasValue)
            {
                strCalMonth = $"s.CalMonthId == {queryFilter.CalMonthId}";
            }
            else
            {
                if(queryFilter.jxType == jxType.finished)
                {
                    strCalMonth = $"s.CalMonth.Status == 4";
                }
                else
                {
                    strCalMonth = $"s.CalMonth.Status != 4";
                }

                
                //strCalMonth = $"s.CalMonth.Status == {Convert.ToInt32(queryFilter.jxType)}";
            }

            if (!string.IsNullOrEmpty(strExpress))
            {
                strExpress = $"{strExpress} && {strCalMonth}";
            }
            else
            {
                strExpress = strCalMonth;
            }

            if (queryFilter.ConditionTree != null)
            {
                string strTem = GetExpress(queryFilter.ConditionTree);

                if (!string.IsNullOrEmpty(strTem))
                {
                    strExpress = $"{strExpress} && ({strTem})";
                }
            }

            var interpreter = new Interpreter();
            Expression<Func<PerformanceItem, bool>> dynamicWhere = interpreter.ParseAsExpression<Func<PerformanceItem, bool>>(strExpress, "s");

            IQueryable<PerformanceItem> response;
            if (queryFilter.userId > 0)
            {
                if (queryFilter.DoingOrReview == 0)
                {
                    response = new spDbContext().PerformanceItems.Where<PerformanceItem>(dynamicWhere).Where(s => (s.ItemStaffs.Where<ItemStaff>(iStaff => iStaff.DoPerson.Id == queryFilter.userId).Count() > 0));
                }
                else
                {
                    response = new spDbContext().PerformanceItems.Where<PerformanceItem>(dynamicWhere).Where(s => s.ReviewerId == queryFilter.userId);
                }
            }
            else
            {
                response = new spDbContext().PerformanceItems.Where<PerformanceItem>(dynamicWhere);

            }

            return response;
        }

        public ApiSaveResponse AddProjectContents(ProjectContents projectContents)
        {
            ApiSaveResponse retResponse = new ApiSaveResponse();
            retResponse.Success = true;

            if (projectContents != null && projectContents.ProjectWorkContents != null && projectContents.ProjectWorkContents.Count > 0)
            {
                using (var t = Context.Database.BeginTransaction())
                {
                    try
                    {
                        CalMonth calMonth = Context.CalMonths.FirstOrDefault<CalMonth>(c => c.Status == 0);

                        if (calMonth == null)
                        {
                            retResponse.Success = false;
                            retResponse.ErrorMessage = "不存在正在处理的绩效月度！";
                            return retResponse;
                        }
                        else
                        {
                            projectContents.ProjectContentRecord.CalMonthId = calMonth.Id;
                            projectContents.ProjectContentRecord.CalMonth = null;

                            var staff = Context.Staffs.FirstOrDefault(s=>s.Name == User.Identity.Name);

                            projectContents.ProjectContentRecord.StaffId = staff.Id;
                            projectContents.ProjectContentRecord.State = 0;
                        }

                        var project = Context.ProjectInfos.FirstOrDefault(p => p.CaseNo == projectContents.ProjectContentRecord.ProjectNo && p.CaseState ==0);

                        if (project != null)
                        {
                            var pRecord = Context.ProjectContentRecords.FirstOrDefault(p=>p.ProjectNo == projectContents.ProjectContentRecord.ProjectNo
                                && p.StaffId == projectContents.ProjectContentRecord.StaffId
                                && p.CalMonthId == projectContents.ProjectContentRecord.CalMonthId);


                            if(pRecord != null)
                            {
                                retResponse.Success = false;
                                retResponse.ErrorMessage = $"您已提交专案【{projectContents.ProjectContentRecord.ProjectNo}】{pRecord.CalMonth.Year}年{pRecord.CalMonth.Month}月的工作内容！";
                                return retResponse;
                            }

                            Context.ProjectContentRecords.Add(projectContents.ProjectContentRecord);

                            foreach (var doItem in projectContents.ProjectWorkContents)
                            {
                                doItem.ContentRecordId = projectContents.ProjectContentRecord.Id;
                                Context.ProjectWorkContents.Add(doItem);
                            }

                            t.Commit();
                        }
                        else
                        {
                            retResponse.Success = false;
                            retResponse.ErrorMessage = "专案不存在或专案已完成！";
                            return retResponse;
                        }
                        
                    }
                    catch (Exception ex)
                    {
                        retResponse.Success = false;
                        retResponse.ErrorMessage = ex.Message;
                        t.Rollback();
                        return retResponse;
                    }
                }
            }

            return retResponse;
        }

        public PerformanceItem GetCaseInfo(string CaseNo)
        {
            var retObj = Context.PerformanceItems.OrderByDescending(p=>p.CalMonthId).FirstOrDefault<PerformanceItem>(p=>p.CaseNo == CaseNo.Trim());

            if(retObj == null)
            {
                retObj = new IPEasyController(Context).GetCaseInfo(CaseNo);
            }

            return retObj;
            
        }

        public PerformanceItem GetItemInfo(string CaseNo, string DoItem)
        {
            var retObj = Context.PerformanceItems.FirstOrDefault<PerformanceItem>(p => p.CaseNo == CaseNo.Trim() && p.DoItem == DoItem.Trim());

            if (retObj == null)
            {
                retObj = new IPEasyController(Context).GetItemInfo(CaseNo,DoItem);
            }

            return retObj;
        }

        public PerformanceItem GetItemInfoByCaseStage(string CaseNo, string DoItem,string caseStage)
        {
            var retObj = Context.PerformanceItems.Include(p=>p.Customer).FirstOrDefault<PerformanceItem>(p => p.CaseNo == CaseNo.Trim() 
            && p.DoItem == DoItem.Trim() && p.CaseStage == caseStage);

            if (retObj == null)
            {
                retObj = IPEasyUtility.GetPerformanceRecord(CaseNo, DoItem, caseStage);
            }

            return retObj;
        }

        public bool MovePerformance2ProjectInfo()
        {
            var response = Context.PerformanceItems.Include(p=>p.ItemStaffs)
                .Where<PerformanceItem>(p => p.CaseNo.StartsWith("S") && p.CalMonth.Status == 0);

            
            var pList = response.ToList<PerformanceItem>();

            foreach(var p in pList)
            {
                ProjectInfo project = new ProjectInfo();
                project.CaseNo = p.CaseNo;
                project.CaseName = p.CaseName;
                project.CaseState = 0;
                project.CaseType = p.CaseType;
                project.CustomerId = p.CustomerId;
                project.ReviewerId = p.ReviewerId;

                Context.ProjectInfos.Add(project);

                
                foreach(ItemStaff iStaff in p.ItemStaffs)
                {
                    Context.ItemStaffs.Remove(iStaff);
                }

                Context.PerformanceItems.Remove(p);

                Context.SaveChanges();
                
            }

            return true;
        }
    }
}