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

using DocumentFormat.OpenXml.Office2010.ExcelAc;
using DynamicExpresso;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.StaticFiles;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Hosting;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using wispro.sp.api.Job;
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]
    
    public class PerformanceItemController : ControllerBase
    {
        spDbContext Context;
        IFileTaskService fileTaskService;


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

        }

        [Authorize]
        public ApiSaveResponse RemoveDBNotFinishDate(int year, int month)
        {
            ApiSaveResponse ret = new ApiSaveResponse();
            ret.Success = true;

            var itemList = Context.PerformanceItems.Include(p => p.ItemStaffs).Where(p => p.CalMonth.Year == year &&
                                                      p.CalMonth.Month == month &&
                                                      p.DoItem == "处理审查意见" &&
                                                      p.FinishedDate == null);
            using var t = Context.Database.BeginTransaction();
            try
            {
                foreach (var item in itemList.ToList())
                {
                    if (item != null)
                    {
                        Context.ItemStaffs.RemoveRange(item.ItemStaffs);
                        Context.PerformanceItems.Remove(item);
                    }
                }

                Context.SaveChanges();
                t.Commit();
                return ret;
            }
            catch (Exception ex)
            {
                t.Rollback();
                ret.Success = false;
                ret.ErrorMessage = ex.Message;
                return ret;
            }
        }


        [Authorize]
        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;
            }

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

            using (Context.Database.BeginTransaction())
            {
                try
                {
                    #region 已算绩效的案号只匹配案号前边的数字 20230310
                    string[] strNos = item.CaseNo.Split(new char[] {'-'});
                    string strNo = item.CaseNo;
                    if(strNos.Length > 0 )
                    {
                        strNo = strNos[0];
                        if(strNos.Length > 1)
                        {
                            if (strNos[1].ToUpper() == "TS")
                            {
                                strNo = $"{strNos[0]}-{strNos[1]}";
                            }
                        }
                        
                    }

                    var results = Context.PerformanceItems.Where<PerformanceItem>(x =>
                       x.CaseNo.StartsWith(strNo) && x.DoItem == item.DoItem && x.DoItem != "提出报告" && x.CaseStage == item.CaseStage);
                   

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

                    #endregion

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

                    #region 计算绩效
                    if (item.BasePoint > 0)
                    {
                        _calItemJX(item, Context);
                    }
                    else
                    {
                        var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == item.Id);
                        Context.StaffStatistics.RemoveRange(oldJxList);
                    }
                    #endregion

                    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>
        [Authorize]
        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[] { '|' });
            }

            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
                            {
                                item.WordCount = null;
                                //ret.Success = false;
                                //ret.ErrorMessage = "所给的栏位值不能转换成数字！";
                                //return ret;
                            }
                            break;
                        case "ReturnCasseNo":
                            item.ReturnCasseNo = temValue;
                            break;
                    }
                }

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

                    if (item.BasePoint > 0)
                    {
                        _calItemJX(item, Context);
                    }
                    else
                    {
                        var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == item.Id);
                        Context.StaffStatistics.RemoveRange(oldJxList);
                    }

                    Context.SaveChanges();
                }
            }

            return ret;
        }
        [Authorize]
        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)
                .Include(pi => pi.ExternalHandler)
                .OrderByDescending(o => o.Id)
                .Skip<PerformanceItem>((pageIndex - 1) * pageSize).Take(pageSize).ToList<PerformanceItem>();

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

            ret.Results = retList;

            return ret;
        }

        [Authorize]
        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)
                .Include (pi => pi.ExternalHandler)
                .OrderByDescending(o => o.Id)
                .FirstOrDefault();

            #region 将某些属性设为null，避免循环取值造成返回json过大
            SetReturnItem(item);
            #endregion

            return item;
        }
        /// <summary>
        /// 获取给定用户的绩效清单
        /// </summary>
        /// <param name="userid">用户id</param>
        /// <param name="type">获取类型；0：处理中；1：所有；4：已归档</param>
        /// <returns></returns>
        [Authorize]
        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)
                .Include(pi => pi.ExternalHandler)
                .OrderByDescending(o => o.Id)
                .Skip<PerformanceItem>((pageIndex - 1) * pageSize).Take(pageSize)
                .ToList<PerformanceItem>();

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

            ret.Results = retList;

            return ret;
        }

        /// <summary>
        /// 获取个人难度系数
        /// </summary>
        /// <param name="calMonth">绩效月份</param>
        /// <param name="userId">用户Id</param>
        /// <param name="isReivewer">是否审核个人难度系数</param>
        /// <param name="HXqf">合写算0.5件</param>
        /// <returns></returns>
        private NanduStatics PersonNanduStatics(CalMonth calMonth, int userId, spDbContext spDb, bool isReivewer = false,bool HXqf=false)
        {
            NanduStatics retObj = new NanduStatics();

            IDictionary<string, double> CaseXiShu = new Dictionary<string, double>();
            var list = spDb.CaseCeoffcients;

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

            string jxType = isReivewer ? "新申请审核" : "新申请处理";

            var results = spDb.StaffStatistics.Include(p => p.Item).Where(s => s.CalMonthId == calMonth.Id && s.jxType == jxType && s.StaffId == userId);

            //if (!isReivewer)
            //{
            //    results = spDb.StaffStatistics.Include(p => p.Item).Where(s => s.CalMonthId == calMonth.Id && (s.jxType == jxType ) && s.StaffId == userId);
            //}

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

            foreach (var item in retList)
            {
                if (item.Item != null)
                {
                    //合写人数
                    decimal  CaseNumber = 1M;

                    if (item.Item.ItemStaffs.Count > 1 && !isReivewer )
                    {
                        if (item.Item .ItemStaffs == null)
                        {
                            item.Item .ItemStaffs = spDb.ItemStaffs.Include(p => p.DoPerson).Where(p => p.ItemId == item.ItemId).ToList();
                        }

                        try
                        {
                            CaseNumber = CaseNumber / (item.Item.ItemStaffs.Where(i => i.DoPerson.IsCalPerformsnce == true || (i.DoPerson.Status != "试用期" && i.DoPerson.IsCalPerformsnce == false)).Count());
                        }
                        catch { }
                    }

                    string strCaseCeoffcient = item.Item.CaseCoefficient;

                    if (string.IsNullOrEmpty(strCaseCeoffcient))
                    {
                        strCaseCeoffcient = "B";
                    }

                    #region 严重延期降系数
                    if (item.Item.isDanger() && string.IsNullOrEmpty(item.Item.OverDueMemo))
                    {
                        switch (item.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":
                            if (item.Item.Type == "专案")
                            {
                                retObj.S += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.S += CaseNumber;
                            }

                            break;
                        case "A":
                            if (item.Item.Type == "专案")
                            {
                                retObj.A += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.A += CaseNumber;
                            }
                            break;
                        case "B":
                            if (item.Item.Type == "专案")
                            {
                                retObj.B += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.B += CaseNumber;
                            }
                            break;
                        case "C":
                            if (item.Item.Type == "专案")
                            {
                                retObj.C += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.C += CaseNumber;
                            }
                            break;
                        case "D":
                            if (item.Item.Type == "专案")
                            {
                                retObj.D += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.D += CaseNumber;
                            }
                            break;
                        default:
                            if (item.Item.Type == "专案")
                            {
                                retObj.B += (decimal)item.totalBasePoint.Value;
                            }
                            else
                            {
                                retObj.B += CaseNumber;
                            }
                            break;

                    }

                    
                }

            }
            #endregion

            d = retObj.S * (decimal)CaseXiShu["S"] + retObj.A * (decimal)CaseXiShu["A"] + retObj.B * (decimal)CaseXiShu["B"] + retObj.C * (decimal)CaseXiShu["C"] + retObj.D * (decimal)CaseXiShu["D"];
            iCount = retObj.S + retObj.A + retObj.B + retObj.C + retObj.D;
            retObj.NanduXS =(double) (d / iCount);

            return retObj;

        }


        /// <summary>
        /// 获取总难度系数
        /// </summary>
        /// <param name="calMonth"></param>
        /// <returns></returns>
        private NanduStatics DegreeOfDifficulty(CalMonth calMonth, spDbContext spDb)
        {
            NanduStatics retObj = new NanduStatics();

            IDictionary<string, double> CaseXiShu = new Dictionary<string, double>();
            var list = spDb.CaseCeoffcients.ToList();

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

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

            var results = spDb.StaffStatistics
                .Where(p => 
                                    p.CalMonthId == calMonth.Id  && 
                                    (p.Item.Type == "新申请" )) //|| p.Item.Type == "专案"))
                .Select(p => p.Item).Distinct();

            #region 循环计算
            
            var retList = results.ToList();

            foreach (var item in retList)
            {
                decimal CaseNumber = 1;

                if(item.ItemStaffs == null)
                {
                    item.ItemStaffs = spDb.ItemStaffs.Include(p=>p.DoPerson).Where(p =>p.ItemId == item.Id).ToList();
                }
                if (item.ItemStaffs.Count > 1)
                {
                    var i = item.ItemStaffs.Where(i => (i.DoPerson.Status != "试用期" && i.DoPerson.IsCalPerformsnce == false)).Count();
                    if ( i> 0)
                    {
                        CaseNumber = CaseNumber *(item.ItemStaffs.Count-i)/item.ItemStaffs.Count;
                    }
                }

                string strCaseCeoffcient = item.CaseCoefficient;

                if (string.IsNullOrEmpty(strCaseCeoffcient))
                {
                    strCaseCeoffcient = "B";
                }

                #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":
                        if (item.Type == "专案")
                        {
                            retObj.S += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.S += CaseNumber;
                        }

                        break;
                    case "A":
                        if (item.Type == "专案")
                        {
                            retObj.A += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.A += CaseNumber;
                        }
                        break;
                    case "B":
                        if (item.Type == "专案")
                        {
                            retObj.B += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.B += CaseNumber;
                        }
                        break;
                    case "C":
                        if (item.Type == "专案")
                        {
                            retObj.C += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.C += CaseNumber;
                        }
                        break;
                    case "D":
                        if (item.Type == "专案")
                        {
                            retObj.D += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.D += CaseNumber;
                        }
                        break;
                    default:
                        if (item.Type == "专案")
                        {
                            retObj.B += (decimal)item.BasePoint.Value;
                        }
                        else
                        {
                            retObj.B += CaseNumber;
                        }
                        break;

                }


                //System.Diagnostics.Debug.WriteLine($"{item.CaseNo}\t{item.DoItem}\t{item.DoItemCoefficient}\t{item.WordCount}\t{strCaseCeoffcient}\t{item.CaseCoefficient}\t{item.BasePoint}");

            }
            #endregion

            var d = retObj.S * (decimal)CaseXiShu["S"] + retObj.A * (decimal)CaseXiShu["A"] + retObj.B * (decimal)CaseXiShu["B"] + retObj.C * (decimal)CaseXiShu["C"] + retObj.D * (decimal)CaseXiShu["D"];
            var iCount = retObj.S + retObj.A + retObj.B + retObj.C + retObj.D; 
            retObj.NanduXS = (double)(d /iCount);

            return retObj;

        }
        [Authorize]
        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>();
        }
        [Authorize]
        public FileProcessTask GetStaticsReport(int Year,int Month)
        {
            CalMonth calMonth = Context.CalMonths.FirstOrDefault(c => c.Year == Year && c.Month == Month);
            if (calMonth != null)
            {
                var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-{calMonth.Year}{calMonth.Month}代理人全部考评维度绩效点数加和.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);

                ExportDataResult result = new ExportDataResult()
                {
                    fileTask = fileTask,
                    calMonth = calMonth
                };

                System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(ExportStaticReport));
                t.Start(result);

                return fileTask;
            }

            return null;
        }

        [Authorize]
        public FileProcessTask GetStaticsPointReport(int Year, int Month)
        {
            CalMonth calMonth = Context.CalMonths.FirstOrDefault(c => c.Year == Year && c.Month == Month);
            if (calMonth != null)
            {
                var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-{calMonth.Year}{calMonth.Month}代理人案件绩效点数清单.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);

                ExportDataResult result = new ExportDataResult()
                {
                    fileTask = fileTask,
                    calMonth = calMonth
                };

                System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(ExportStaticPointReport));
                t.Start(result);

                return fileTask;
            }

            return null;
        }

        [Authorize]
        public FileProcessTask GetOtherNewCaseAllocationReport(int Year, int Month)
        {
            CalMonth calMonth = Context.CalMonths.FirstOrDefault(c => c.Year == Year && c.Month == Month);
            if (calMonth != null)
            {
                var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-{calMonth.Year}{calMonth.Month}其它新申请代理人案件绩效金额清单.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);

                ExportDataResult result = new ExportDataResult()
                {
                    fileTask = fileTask,
                    calMonth = calMonth
                };

                System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(_GetOtherNewCaseAllocationReport));
                t.Start(result);

                return fileTask;
            }

            return null;
        }

        private void _GetOtherNewCaseAllocationReport(object exportDataResult)
        {
            ExportDataResult result = (ExportDataResult)exportDataResult;
            FileProcessTask file = result.fileTask;
            CalMonth calMonth = result.calMonth;

            //var StatisticsResults = _CalMyStatistics(calMonth);
            string strSQL = @" SELECT Staff.Name as 姓名, CaseNo as 我方案号,CaseName as 案件名称,Type as 案件类型,CaseCoefficient as 案件系数,[Ratio] as 分配比率
                  ,[ActualAmount] as 绩效金额
                  FROM [spDB].[dbo].[AllocationRatio],
                  performanceItem,staff
                  where Staff.Id = AllocationRatio.PersonId and PerformanceItem.Id= AllocationRatio.ItemId
                  and ItemId in (select id from PerformanceItem where CalMonthId in (select id from CalMonth where year =@year and month=@month))
                  order by CaseNo";

            DataTable dt = new DataTable();
            spDbContext spDb = new spDbContext();
            using (var conn = spDb.Database.GetDbConnection())
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = strSQL;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add(new SqlParameter("year", calMonth.Year));
                cmd.Parameters.Add(new SqlParameter("month", calMonth.Month));
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
            }

            NPOIExcel.DataTableToExcel(dt, result.fileTask.FilePath);
            result.fileTask.Finished = true;
        }
        private void ExportStaticPointReport(object exportDataResult)
        {
            ExportDataResult result = (ExportDataResult)exportDataResult;
            FileProcessTask file = result.fileTask;
            CalMonth calMonth = result.calMonth;

            //var StatisticsResults = _CalMyStatistics(calMonth);
            string strSQL = @" SELECT Staff.Name as 姓名,PerformanceItem.CaseNo as 案号,
                  StaffStatistics.jxType as 点数类型,
                  StaffStatistics.totalBasePoint as 基础点数,
                  StaffStatistics.totalActuallyPoint as 调整后点数 
                  ,StaffStatistics.FinianlPoint as 最终点数,
                  StaffStatistics.nanduXS as 难度系数,
                  StaffStatistics.S as S案件数,
                  StaffStatistics.A as A案件数,
                  StaffStatistics.B as B案件数,
                  StaffStatistics.C as C案件数,
                  StaffStatistics.D as D案件数
                FROM StaffStatistics,Staff,PerformanceItem 
                WHERE  StaffStatistics.ItemId =PerformanceItem.Id
                  and StaffStatistics.StaffId = staff.Id
                  and PerformanceItem.CalMonthId =@CalMonthId
                  order by Staff.Name ";

            DataTable dt = new DataTable();
            spDbContext spDb = new spDbContext();
            using (var conn = spDb.Database.GetDbConnection())
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = strSQL;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add(new SqlParameter("CalMonthId", calMonth.Id));
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
            }

            NPOIExcel.DataTableToExcel(dt, result.fileTask.FilePath);

            #region 添加月度的难度系数和各等级案件数量
            /*
            // 检查文件是否是只读的
            if ((System.IO.File.GetAttributes(result.fileTask.FilePath) & FileAttributes.ReadOnly) == FileAttributes.ReadOnly)
            {
                // 取消只读属性
                System.IO.File.SetAttributes(result.fileTask.FilePath, System.IO.File.GetAttributes(result.fileTask.FilePath) & ~FileAttributes.ReadOnly);
            }

            using (FileStream f = new FileStream(result.fileTask.FilePath, FileMode.Open, FileAccess.ReadWrite))
            {
                XSSFWorkbook workbook = new XSSFWorkbook(f);
                
                ISheet sheet = workbook.GetSheetAt(0);

                ICellStyle style = workbook.CreateCellStyle();

                // 设置边框
                style.BorderTop = BorderStyle.Thin;
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;

                sheet.ShiftRows(0, sheet.LastRowNum, 1);
                sheet.ShiftRows(0, sheet.LastRowNum, 1);

                IRow newRow = sheet.CreateRow(0);
                ICell cell = newRow.CreateCell(0);
                cell.SetCellValue((calMonth.NanduXS != null) ? calMonth.NanduXS.Value : 0.0);
                cell.CellStyle = style;

                cell = newRow.CreateCell(1);
                cell.SetCellValue((calMonth.S != null) ? calMonth.S.Value : 0.0);
                cell.CellStyle = style;

                cell = newRow.CreateCell(2);
                cell.SetCellValue((calMonth.A != null) ? calMonth.A.Value : 0.0);
                cell.CellStyle = style;

                cell = newRow.CreateCell(3);
                cell.SetCellValue((calMonth.B != null) ? calMonth.B.Value : 0.0);
                cell.CellStyle = style;

                cell = newRow.CreateCell(4);
                cell.SetCellValue((calMonth.C != null) ? calMonth.C.Value : 0.0);
                cell.CellStyle = style;

                cell = newRow.CreateCell(5);
                cell.SetCellValue((calMonth.D != null) ? calMonth.D.Value : 0.0);
                cell.CellStyle = style;


                sheet.ShiftRows(0, sheet.LastRowNum, 1);
                newRow = sheet.CreateRow(0);
                cell = newRow.CreateCell(0);
                cell.SetCellValue("难度系数");
                cell.CellStyle = style;

                cell = newRow.CreateCell(1);
                cell.SetCellValue("S");
                cell.CellStyle = style;

                cell = newRow.CreateCell(2);
                cell.SetCellValue("A");
                cell.CellStyle = style;

                cell = newRow.CreateCell(3);
                cell.SetCellValue("B");
                cell.CellStyle = style;

                cell = newRow.CreateCell(4);
                cell.SetCellValue("C");
                cell.CellStyle = style;

                cell = newRow.CreateCell(5);
                cell.SetCellValue("D");
                cell.CellStyle = style;
                
                workbook.Write(f);

            }
            */
            
            #endregion

            result.fileTask.Finished = true;
        }


        private void ExportStaticReport(object exportDataResult)
        {
            ExportDataResult result = (ExportDataResult)exportDataResult;
            FileProcessTask file = result.fileTask;
            CalMonth calMonth = result.calMonth;

            //var StatisticsResults = _CalMyStatistics(calMonth);
            string strSQL = @"SELECT  Staff.Name as 代理人,StaffGrade.Grade as 代理人等级,
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '新申请处理' and CalMonthId = @CalMonthId ) as '新申请处理',
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '新申请审核' and CalMonthId = @CalMonthId) as '新申请审核',
	              (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '专案处理' and CalMonthId = @CalMonthId) as '专案处理',
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '专案审核' and CalMonthId = @CalMonthId) as '专案审核',
	              (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = 'OA处理' and CalMonthId = @CalMonthId) as OA处理,
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = 'OA审核' and CalMonthId = @CalMonthId) as OA审核,	  
	              (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '其它处理' and CalMonthId = @CalMonthId) as 其它处理,
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId] and b.jxType = '其它审核' and CalMonthId = @CalMonthId) as 其它审核,
                  (select sum([FinianlPoint]) from[StaffStatistics] B where B.StaffId = A.[StaffId]  and CalMonthId = @CalMonthId) as 总计
              FROM[spDB].[dbo].[StaffStatistics] as A
              inner join Staff on Staff.Id = A.StaffId
              Left join StaffGrade on Staff.StaffGradeId = StaffGrade.Id
              where CalMonthId = @CalMonthId
              group by A.StaffId, Staff.Name,StaffGrade.Grade";

            DataTable dt = new DataTable();
            spDbContext spDb = new spDbContext();
            using (var conn = spDb.Database.GetDbConnection())
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = strSQL;
                cmd.CommandType = CommandType.Text;
                    
                cmd.Parameters.Add(new SqlParameter("CalMonthId", calMonth.Id));
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
            }

            NPOIExcel.DataTableToExcel(dt, result.fileTask.FilePath);
            result.fileTask.Finished = true;
        }

        private List<StaffStatistics> _calMyOtherStatstics(CalMonth calMonth,int? userid)
        {
            var retList = Context.AllocationRatios.Where(s=>s.Item.CalMonth.Id == calMonth.Id);    

            if(userid != null)
            {
                retList = retList.Where(s=>s.PersonId == userid);
            }

            var retData = retList.GroupBy(s => new {  s.PersonId })
                            .Select(g => new
                            {
                                StaffId = g.Key.PersonId,
                                FinianlPoint = g.Sum(s => s.ActualAmount),
                            });

            List<StaffStatistics> temList = new List<StaffStatistics>();
            foreach (var key in retData)
            {
                temList.Add(new StaffStatistics()
                {
                    StaffId = key.StaffId,
                    CalMonthId = calMonth.Id,
                    jxType = "其他新申请",
                    FinianlPoint = key.FinianlPoint
                });
            }

            return temList;
        }

        private List<StaffStatistics> _CalMyStatistics(CalMonth calMonth, int? userid = null)
        {
            var retList = Context.StaffStatistics.Where(s => s.CalMonthId == calMonth.Id);

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

            var retData2 = retList.GroupBy(s => new { s.CalMonthId, s.StaffId, s.jxType })
                            .Select(g => new
                            {
                                StaffId = g.Key.StaffId,
                                CalMonthId = g.Key.CalMonthId,
                                jxType = g.Key.jxType,
                                FinianlPoint = g.Sum(s => s.FinianlPoint),
                                totalBasePoint = g.Sum(s => s.totalBasePoint),
                                totalActuallyPoint = g.Sum(s => s.totalActuallyPoint)
                            });

            List<StaffStatistics> temList = new List<StaffStatistics>();
            foreach (var key in retData2)
            {
                temList.Add(new StaffStatistics()
                {
                    StaffId = key.StaffId,
                    CalMonthId = key.CalMonthId,
                    jxType = key.jxType,
                    totalBasePoint = key.totalBasePoint,
                    totalActuallyPoint = key.totalActuallyPoint,
                    FinianlPoint = key.FinianlPoint

                });
            }

            return temList;

            #region old code
            ////未归档，从绩效记录中统计数据
            //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;
            //                temValue.totalActuallyPoint += retUserValue.totalActuallyPoint;
            //            }
            //            else
            //            {
            //                retList.Add(retUserValue);
            //            }
            //        }
            //    }

            //}

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

            //return retList;
            #endregion

        }

        private void _RefreshBasePoint()
        {
            spDbContext spDb = new spDbContext();

            var calMonth = spDb.CalMonths.FirstOrDefault(c => c.Status == 0);
            _RefreshBasePoint(calMonth, spDb);

            StatisticsLevelCount(calMonth.Year, calMonth.Month);

        }

        private void _RefreshBasePoint(CalMonth calMonth, spDbContext spDb)
        {
            if (calMonth != null)
            {
                var lstItem = spDb.PerformanceItems
                    .Include(p => p.Customer)
                    .Include(p => p.ItemStaffs)
                    .Include(p => p.Reviewer)
                    .Include(p => p.CalMonth)
                    .Where(p => p.CalMonthId == calMonth.Id).ToList();

                var rules = spDb.BasePointRules.ToList();

                foreach (var item in lstItem)
                {
                    try
                    {
                        Utility.Utility.CalBasePoint(item, rules);

                        if (item.BasePoint > 0)
                        {
                            _calItemJX(item, spDb);
                        }
                        else
                        {
                            var oldJxList = spDb.StaffStatistics.Where(p => p.ItemId == item.Id);
                            spDb.StaffStatistics.RemoveRange(oldJxList);
                        }

                        spDb.SaveChanges();
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debug.WriteLine(ex.Message);
                    }
                }

                _CalJXPoint(calMonth, spDb);
            }
        }
        [Authorize]
        public ApiSaveResponse RefreshBasePoint()
        {
            System.Threading.Thread t = new Thread(_RefreshBasePoint);
            t.Start();

            return new ApiSaveResponse() {
                Success = true
            };
        }
        [Authorize]
        public void RefreshPoint(PerformanceItem item)
        {
            //spDbContext spDb = new spDbContext();

            var rules = Context.BasePointRules.ToList();

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

                if (item.BasePoint > 0)
                {
                    _calItemJX(item, Context);
                }
                else
                {
                    var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == item.Id);
                    Context.StaffStatistics.RemoveRange(oldJxList);
                }

                Context.SaveChanges();
            }

        }

        private int? GetStaff(string v)
        {
            if (!string.IsNullOrEmpty(v))
            {
                string temName = v.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];

                if (!v.Contains("君龙"))
                {
                    temName = v;
                }
                var staff = Context.Staffs.Where<Staff>(s => s.Name == temName).FirstOrDefault();

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

            return null;
        }
        [Authorize]
        public ApiSaveResponse UpdateOAStaff()
        {
            string strExcelFile = @"c:\temp\每月绩效统计--中国一次OA授权表.xlsx";
            DataTable dt = NPOIExcel.ExcelToDataTable(strExcelFile, true, false);

            foreach(DataRow row in dt.Rows)
            {
                string strCaseNo = row["我方文号"].ToString().Trim();
                string strDoItem ="发明一次OA授权"; //row["处理事项"].ToString().Trim();

                PerformanceItem item = Context.PerformanceItems
                    .Where<PerformanceItem>(p => p.CaseNo == strCaseNo && 
                    p.DoItem == strDoItem 
                    && p.CalMonth.Status == 0)
                .Include(p => p.Reviewer)
                .Include(p => p.CalMonth)
                .Include(p => p.Customer)
                .Include(p => p.ItemStaffs).ThenInclude(p => p.DoPerson)
                .FirstOrDefault();

                if(item != null)
                {
                    string strHandler = strHandler = row["处理事项处理人"].ToString().Trim();

                    string[] temHandlers = strHandler.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    item.ItemStaffs = item.ItemStaffs;
                    if(item.ItemStaffs == null)
                    {
                        item.ItemStaffs = new List<ItemStaff>();
                    }

                    foreach (string name in temHandlers)
                    {
                        ItemStaff itemStaff = new ItemStaff();
                        string temName = name.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)[0];
                        if (!name.Contains("君龙"))
                        {
                            temName = name.Trim();
                        }
                        int? iTem = GetStaff(temName);
                        if ((iTem != null))
                        {
                            //itemStaff.Item = item;
                            itemStaff.DoPersonId = iTem.Value;

                            if (item.ItemStaffs.Where(itemS => itemS.DoPersonId == iTem.Value).Count() == 0)
                            {
                                item.ItemStaffs.Add(itemStaff);
                            }
                        }
                        else
                        {
                            itemStaff.DoPerson = new Staff()
                            {
                                Name = temName,
                                Account = temName,
                                Password = utility.MD5Utility.GetMD5("12345678"),
                                IsCalPerformsnce = false,
                                Status = "试用期",
                                StaffGradeId = 4
                            };
                            item.ItemStaffs.Add(itemStaff);
                        }
                    }

                }

                
                Context.SaveChanges();

            }



                

            return new ApiSaveResponse()
            {
                Success = true
            };
        }
        [Authorize]
        public ApiSaveResponse RefreshFromIPEasyById(int itemId)
        {
            var Item = Context.PerformanceItems.Include(p => p.Customer).Include(p => p.ItemStaffs).ThenInclude(p => p.DoPerson).FirstOrDefault(p => p.Id == itemId);
            if (Item != null)
            {
                new Job.UpdateJXDataFromIPEasyJob().UpdateFromIPEasy(Item, Context);
            }

            Utility.Utility.CalBasePoint(Item, Context.BasePointRules.ToList());

            if (Item.BasePoint > 0)
            {
                _calItemJX(Item, Context);
            }
            else
            {
                var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == Item.Id);
                Context.StaffStatistics.RemoveRange(oldJxList);
            }
            Context.SaveChanges();

            return new ApiSaveResponse()
            {
                Success = true
            };
        }
        [Authorize]

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

            return new ApiSaveResponse()
            {
                Success = true
            };
        }
        [Authorize]
        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
            };
        }
        [Authorize]
        public ApiSaveResponse CompareExcel2DB()
        {

            System.Threading.Thread t = new Thread(new ThreadStart(_CompareExcel2DB));
            t.Start();

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


        [Authorize]
        public FileProcessTask CurrentData2Excel(int Year,int Month)
        {
            CalMonth calMonth = Context.CalMonths.FirstOrDefault(c => c.Year == Year && c.Month == Month);
            if (calMonth != null)
            {
                var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-{calMonth.Year}{calMonth.Month}线下绩效核算数据.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);

                ExportDataResult result = new ExportDataResult() {
                    fileTask = fileTask,
                    calMonth = calMonth
                };

                System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(Export2ExcelThread));
                t.Start(result);

                return fileTask;
            }

            return null;

        }

        internal class ExportDataResult
        {
            public FileProcessTask fileTask { get; set; }
            public CalMonth calMonth { get; set; }
        }

        private void Export2ExcelThread(object attfile)
        {
            ExportDataResult result = (ExportDataResult)attfile;
            FileProcessTask file = result.fileTask;

            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.Id == result.calMonth.Id && !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));
            dt.Columns.Add("实际处理人", typeof(string));

            #endregion

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

            file.Size = items.Count;
            foreach (var p in items)
            {


                List<ItemStaff> temItemStaffs = p.ItemStaffs.ToList();

                string strCaseMemo = "";
                string strHandler = "";

                temItemStaffs = new List<ItemStaff>();
                foreach (var iStaff in p.ItemStaffs)
                {
                    strHandler = string.IsNullOrEmpty(strHandler) ? iStaff.DoPerson.Name : $"{strHandler},{iStaff.DoPerson.Name}";
                    if (iStaff.DoPerson.Status == "试用期")
                    {
                        strCaseMemo = string.IsNullOrEmpty(strCaseMemo) ? $"{iStaff.DoPerson.Name}在试用期" : $"{strCaseMemo},{iStaff.DoPerson.Name}在试用期";
                    }
                    else
                    {
                        if (!iStaff.DoPerson.IsCalPerformsnce)
                            strCaseMemo = string.IsNullOrEmpty(strCaseMemo) ? $"{iStaff.DoPerson.Name}不计算绩效" : $"{strCaseMemo},{iStaff.DoPerson.Name}不计算绩效";
                    }

                    if (iStaff.DoPersonId == p.ReviewerId)
                    {
                        strCaseMemo = String.IsNullOrEmpty(strCaseMemo) ? "核稿人与处理人相同" : $"{strCaseMemo},核稿人与处理人相同";
                    }
                }



                var staffStatics = spDb.StaffStatistics.Include(s => s.Staff).ThenInclude(s => s.StaffGrade).Where(s => s.ItemId == p.Id && s.jxType.Contains("处理"));
                var reviewStatic = spDb.StaffStatistics.Include(s => s.Staff).ThenInclude(s => s.StaffGrade).FirstOrDefault(s => s.ItemId == p.Id && s.jxType.Contains("审核"));

                foreach (var iStaff in staffStatics.ToList())
                {
                    DataRow row = dt.NewRow();
                    row["我方文号"] = p.CaseNo;
                    row["申请类型"] = p.ApplicationType;
                    row["实际处理人"] = strHandler;

                    if (p.ApplicationName != null && p.ApplicationName.Contains("OPPO") && p.ApplicationType == "实用新型" && p.Type == "新申请")
                    {
                        row["申请类型"] = "发明";
                        row["案件备注"] = $"{row["案件备注"]}\r\n【OPPO案件实用新型修改为发明】";
                    }
                    row["处理事项"] = p.DoItem;
                    row["业务类型"] = p.BusinessType;
                    row["备注（填表注意事项）"] = p.AgentFeedbackMemo;

                    if (p.Type == "专案")
                    {
                        row["备注（填表注意事项）"] = $"{row["备注（填表注意事项）"]}【主管给定点数】专案";
                        row["处理事项"] = "提出报告";

                    }

                    if (p.ItemStaffs.Count() > 1)
                    {
                        row["备注（填表注意事项）"] = $"{row["备注（填表注意事项）"]},{strHandler}合写";
                    }

                    row["案件阶段"] = p.CaseStage;

                    row["案件系数"] = p.CaseCoefficient;
                    if (p.isDanger() && string.IsNullOrEmpty(p.OverDueMemo) && p.Type == "新申请")
                    {
                        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;
                    row["处理人"] = iStaff.Staff.Name;
                    row["基本点数"] = iStaff.totalBasePoint;
                    row["处理人等级"] = iStaff.Staff.StaffGrade.Grade;
                    row["处理人绩效系数"] = iStaff.Staff.StaffGrade.Coefficient;

                    if (reviewStatic != null)
                    {
                        row["核稿人"] = reviewStatic.Staff.Name;

                        var v = verifyCeoffients.FirstOrDefault(p => p.CheckerId == reviewStatic.Staff.StaffGradeId && p.DoPersonId == iStaff.Staff.StaffGradeId);
                        if (v != null)
                        {
                            row["核稿绩效"] = ((decimal)iStaff.totalBasePoint * (decimal)v.Coefficient);
                            row["核稿系数"] = v.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.WordCount;
                    row["备注（发文严重超期是否属客观原因，若为否，请填写原因"] = p.OverDueMemo;
                    row["绩效类型"] = p.Type;
                    row["案件备注"] = $"{p.CaseMemo}\r\n{row["案件备注"]}";
                    if (!string.IsNullOrEmpty(strCaseMemo))
                    {
                        row["案件备注"] = $"{strCaseMemo}\r\n{row["案件备注"]}";
                    }

                    dt.Rows.Add(row);
                }

                file.Processed += 1;

            }

            NPOIExcel.DataTableToExcel(dt, file.FilePath);
            file.Finished = true;
        }

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


        }


        [Authorize]
        public List<StaffStatistics> CalItemJX(int itemid)
        {
            var Item = Context.PerformanceItems.Include(p => p.CalMonth)
                .Include(p => p.ItemStaffs).ThenInclude(p => p.DoPerson).ThenInclude(s => s.StaffGrade)
                .Include(p => p.Reviewer)
                .Include(p => p.Customer)
                .FirstOrDefault(p => p.Id == itemid);
            List<VerifyCoefficient> verifyCoefficients = Context.VerifyCoefficients.ToList<VerifyCoefficient>();

            return _calItemJX(verifyCoefficients, Item, Context);

        }

        public List<AllocationRatio> CalAllocationRatios(int itemId)
        {
            var retList = Context.AllocationRatios
                .Include(p=>p.Person)
                .Where<AllocationRatio>(p => p.ItemId == itemId).ToList();

            foreach(var ar in retList)
            {
                ar.Person.AllocationRatios = null;
                ar.Person.Customers = null;
                ar.Person.ExternalHandlerItems = null;
                ar.Person.ItemStaffs = null;
                ar.Person.ReviewerItems = null;
                ar.Person.Positions = null;
            }

            return retList;
            
        }

        public void SaveAllocationRatios(List<AllocationRatio> allocationRatios)
        {
            using (var t = Context.Database.BeginTransaction())
            {
                try
                {
                    var oldList = Context.AllocationRatios.Where<AllocationRatio>(a => a.ItemId == allocationRatios[0].ItemId).ToList();
                    Context.AllocationRatios.RemoveRange(oldList);

                    Context.AllocationRatios.AddRange(allocationRatios);

                    Context.SaveChanges();

                    var item = Context.PerformanceItems.Where(i => i.Id == allocationRatios[0].ItemId).FirstOrDefault<PerformanceItem>();
                    _calItemJX(item, Context);

                    t.Commit();
                }
                catch(Exception ex)
                {
                    t.Rollback();
                    throw new ApplicationException(ex.Message);
                }
            }
        }

        private void _calNDItemJx(PerformanceItem item,spDbContext spDb)
        {
            //宁德时代绩效计算
            var lstARs = spDb.AllocationRatios.Where<AllocationRatio>(i => i.ItemId == item.Id).ToList();
            double? baseMony = item.BasePoint;

            if (DateTime.Parse($"{item.CalMonth.Year}-{item.CalMonth.Month}-01") > DateTime.Parse("2024-11-01"))
            {
                if (item.isDanger() && item.OverDueMemo == null)
                {
                    #region 完成日期-委托日期>45天为延迟，每延迟一天绩效减去0.5个百分点
                    DateTime? dt1 = item.EntrustingDate;
                    DateTime? dt2 = item.FinishedDate;

                    if (dt2 == null)
                    {
                        dt2 = DateTime.Now;
                    }

                    baseMony = baseMony * (100 - ((dt2.Value - dt1.Value).TotalDays - 45) * 0.5) / 100.00;
                    #endregion
                }
            }

            
            if(lstARs == null || lstARs.Count()==0)
            {
                lstARs = new List<AllocationRatio>();
                if(item.ExternalHandlerId != null && item.ItemStaffs.Where<ItemStaff>(i=>i.DoPersonId== item.ExternalHandlerId).Count() > 0)
                {
                    foreach(ItemStaff iStaff in item.ItemStaffs)
                    {
                        lstARs.Add(new AllocationRatio() { 
                            ItemId = item.Id,
                            PersonId = iStaff.DoPersonId,
                            Ratio = 100.00/item.ItemStaffs.Count(),
                        });
                    }
                }
                else
                {

                    double temTotals = 100.00;
                    if (item.ExternalHandlerId != null)
                    {
                        lstARs.Add(new AllocationRatio()
                        {
                            ItemId = item.Id,
                            PersonId = item.ExternalHandlerId.Value,
                            Ratio = 50.00
                        });
                        temTotals = 50.00;
                    }

                    foreach (ItemStaff iStaff in item.ItemStaffs)
                    {
                        lstARs.Add(new AllocationRatio()
                        {
                            ItemId = item.Id,
                            PersonId = iStaff.DoPersonId,
                            Ratio = temTotals / item.ItemStaffs.Count(),
                        });
                    }
                }

                spDb.AllocationRatios.AddRange(lstARs);

            }

            var totals = lstARs.Sum<AllocationRatio>(a => a.Ratio);

            foreach(var ar in lstARs)
            {
                ar.ActualAmount = baseMony * ar.Ratio / totals;
            }

            spDb.SaveChanges();
        }
        private void _calItemJX(PerformanceItem Item, spDbContext spDb)
        {
            var oldJxList = spDb.StaffStatistics.Where(p => p.ItemId == Item.Id);
            spDb.StaffStatistics.RemoveRange(oldJxList);
            spDb.SaveChanges();

            if (Item.Type == "其他新申请")
            {
                _calNDItemJx(Item, spDb);
                return;
                
            }

            List<VerifyCoefficient> verifyCoefficients = spDb.VerifyCoefficients.ToList<VerifyCoefficient>();

            var jxList = _calItemJX(verifyCoefficients, Item, spDb);

            
            foreach (var jx in jxList)
            {
                if (jx.totalBasePoint == 0)
                {
                    continue;
                }

                jx.ItemId = Item.Id;
                spDb.StaffStatistics.Add(jx);

                spDb.SaveChanges();
            }
        }

        /// <summary>
        /// 序号	情况		                          处理逻辑
        ///  1	    处理人试用期算绩效 一人处理           正常算绩效给处理人
        ///  2	    处理人试用期不算绩效 一人处理         算给核稿人，如果没有核稿人，这条数据忽略
        ///  3	    处理人试用期算绩效 与人合写           合写人算绩效、与合写人一人一半
        ///  4	    处理人试用期不算绩效 与人合写         合写人算绩效、全部绩效算给合写人
        ///  5	    处理人试用期不算绩效 与人合写         合写人不算算绩效、这条数据忽略
        ///  6	    处理人试用期算绩效 与人合写           合写人不算绩效、试用期人员拿一半
        ///  7	    处理人不算绩效 一人处理               这条数据忽略
        ///  8	    处理人不算绩效 与人合写               合写人算绩效、合写人拿一半
        ///  9	    处理人不算绩效 与人合写               合写人不算绩效、这条数据忽略
        /// </summary>
        /// <param name="verifyCoefficients"></param>
        /// <param name="item"></param>
        /// <param name="spDb"></param>
        /// <returns></returns>
        private List<StaffStatistics> _calItemJX(List<VerifyCoefficient> verifyCoefficients, PerformanceItem item, spDbContext spDb)
        {
            
            System.Collections.Hashtable doPersonsBL = new System.Collections.Hashtable();

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

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

            List<ItemStaff> temIStaffs = new List<ItemStaff>();

            #region 将试用期且不算绩效的人员从处理人清单中剔除
            int syqUsers = 0;
            foreach(var itemStaff in item.ItemStaffs)
            {
                if (itemStaff.DoPerson == null)
                {
                    itemStaff.DoPerson = spDb.Staffs.Include(s => s.StaffGrade).FirstOrDefault(p => p.Id == itemStaff.DoPersonId);
                }

                if(itemStaff.DoPerson.Status == "试用期" && !itemStaff.DoPerson.IsCalPerformsnce )
                {
                    syqUsers += 1;
                }
                else
                {
                    temIStaffs.Add(itemStaff);
                }
            }
            #endregion

            #region 处理人全部是试用期人员，则将核稿人当成处理人（处理人为试用期人）
            if (syqUsers == item.ItemStaffs.Count)
            {
                if(item.Reviewer != null)
                {
                    temIStaffs.Add(new ItemStaff() { DoPersonId = item.ReviewerId.Value, ItemId = item.Id });
                }
                
            }
            #endregion

            bool isPJFP = true;
            double total = temIStaffs.Count();

            

            if (total >1 && temIStaffs.Where<ItemStaff>(p => p.PerformancePoint != null || p.PerformancePoint == 0).Count() > 0)
            {
                total = temIStaffs.Select(i => i.PerformancePoint.Value).Sum();
                isPJFP = false;
            }

            foreach (ItemStaff itemStaff in temIStaffs)
            {
                if(itemStaff.DoPerson == null)
                {
                    itemStaff.DoPerson = spDb.Staffs.Include(s=>s.StaffGrade).FirstOrDefault(p=>p.Id==itemStaff.DoPersonId);
                }

                if (itemStaff.DoPerson.IsCalPerformsnce)
                {
                    #region 计算各处理人的绩效点数
                    double handlerBasePoint;
                    if (item.Type != "专案")
                    {
                        if (isPJFP)
                        {
                            handlerBasePoint = item.BasePoint.Value * 1.0 / total;
                        }
                        else
                        {
                            handlerBasePoint = (double)((decimal)item.BasePoint.Value * (decimal)itemStaff.PerformancePoint.Value / (decimal)total);
                        }
                    }
                    else
                    {
                        if (itemStaff.PerformancePoint != null)
                        {
                            if (isPJFP)
                            {
                                handlerBasePoint = item.BasePoint.Value * 1.0 / total;
                            }
                            else
                            {
                                handlerBasePoint = (double)((decimal)item.BasePoint.Value * (decimal)itemStaff.PerformancePoint.Value / (decimal)total);
                            }
                        }
                        else
                        {
                            if (item.ItemStaffs.Count == 1 )
                            {
                                handlerBasePoint = item.BasePoint.Value;
                            }
                            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 == item.CalMonthId).FirstOrDefault();
                    if (temStatic != null)
                    {
                        temStatic.totalBasePoint += handlerBasePoint;

                        if (item.Type == "OA" || item.Type == "新申请") // || item.Type == "专案")
                        {
                            temStatic.totalActuallyPoint += (double)((decimal)handlerBasePoint * (decimal)itemStaff.DoPerson.StaffGrade.Coefficient);
                        }
                        else
                        {
                            temStatic.totalActuallyPoint += handlerBasePoint;
                        }
                    }
                    else
                    {
                        if (itemStaff.DoPerson.StaffGradeId != null && itemStaff.DoPerson.IsCalPerformsnce)
                        {
                            if (item.Type == "OA" || item.Type == "新申请") // || item.Type == "专案")
                            {
                                if (itemStaff.DoPerson.StaffGrade == null)
                                {
                                    itemStaff.DoPerson.StaffGrade = spDb.StaffGrades.FirstOrDefault(s => s.Id == itemStaff.DoPerson.StaffGradeId);
                                }

                                temStatic = new StaffStatistics()
                                {
                                    //CalMonth = calMonth,
                                    CalMonthId = item.CalMonthId,
                                    StaffId = itemStaff.DoPersonId,
                                    totalActuallyPoint = (double)((decimal)handlerBasePoint * (decimal)itemStaff.DoPerson.StaffGrade.Coefficient),
                                    totalBasePoint = handlerBasePoint,
                                    jxType = handlerJxType
                                };

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

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

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

                        if (item.Reviewer.IsCalPerformsnce)
                        {
                            #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 = (double)((decimal)handlerBasePoint * (decimal)vcoefficient.Coefficient);


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

                                var temReviewerStatic = itemStatistics.Where<StaffStatistics>(s => s.StaffId == item.ReviewerId && s.jxType == temJxType && s.CalMonthId == item.CalMonthId).FirstOrDefault();
                                if (temReviewerStatic != null)
                                {
                                    temReviewerStatic.totalBasePoint += reviewerBasePoint;
                                    temReviewerStatic.totalActuallyPoint += reviewerBasePoint;
                                }
                                else
                                {
                                    if (itemStaff.DoPerson.IsCalPerformsnce && item.Reviewer.IsCalPerformsnce)  //判断是否在职
                                    {
                                        temReviewerStatic = new StaffStatistics()
                                        {

                                            CalMonthId = item.CalMonthId,
                                            StaffId = item.ReviewerId.Value,
                                            totalBasePoint = reviewerBasePoint,
                                            totalActuallyPoint = reviewerBasePoint,
                                            jxType = temJxType
                                        };

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

            return itemStatistics;
        }

        private void _CalJXPoint(CalMonth calMonth,spDbContext spDb)
        {
            NanduStatics gspjXS = DegreeOfDifficulty(calMonth,spDb);
            calMonth.NanduXS = gspjXS.NanduXS;
            calMonth.S = (double)gspjXS.S;
            calMonth.A = (double)gspjXS.A;
            calMonth.B = (double)gspjXS.B;
            calMonth.C = (double)gspjXS.C;
            calMonth.D = (double)gspjXS.D;

            var retList = spDb.StaffStatistics.Where(s => s.CalMonthId == calMonth.Id).ToList();

            #region 新申请处理+专案处理 奖励点数
            double jlPoint = 0;
            if (calMonth.S.HasValue)
            {
                jlPoint = jlPoint + (calMonth.S.Value * 1.5);
            }

            if (calMonth.A.HasValue)
            {
                jlPoint = jlPoint + (calMonth.A.Value * 0.5);
            }
            #endregion

            #region 难度系数
            IDictionary<int, NanduStatics> staffXiShu = new Dictionary<int, NanduStatics>();
            IDictionary<int, NanduStatics> ReivewerXiShu = new Dictionary<int, NanduStatics>();
            foreach (StaffStatistics ss in retList)
            {
                if (ss.jxType == "新申请处理") // || ss.jxType == "专案处理")
                {
                    
                    #region 新申请处理+专案处理
                    if (!staffXiShu.ContainsKey(ss.StaffId))
                    {
                        NanduStatics nandu = PersonNanduStatics(calMonth, ss.StaffId,spDb,false,true);
                        staffXiShu.Add(ss.StaffId, nandu);
                    }

                    ss.FinianlPoint = ss.totalActuallyPoint * staffXiShu[ss.StaffId].NanduXS / gspjXS.NanduXS;
                    ss.NanduXS = staffXiShu[ss.StaffId].NanduXS;
                    ss.S = (double)staffXiShu[ss.StaffId].S;
                    ss.A = (double)staffXiShu[ss.StaffId].A;
                    ss.B = (double)staffXiShu[ss.StaffId].B;
                    ss.C = (double)staffXiShu[ss.StaffId].C;
                    ss.D = (double)staffXiShu[ss.StaffId].D;
                    #endregion
                }
                else
                {
                    if (ss.jxType == "新申请审核")
                    {
                        if (!ReivewerXiShu.ContainsKey(ss.StaffId))
                        {
                            NanduStatics nandu = PersonNanduStatics(calMonth, ss.StaffId,spDb, true);
                            ReivewerXiShu.Add(ss.StaffId, nandu);
                        }

                        ss.FinianlPoint = ss.totalBasePoint * ReivewerXiShu[ss.StaffId].NanduXS / gspjXS.NanduXS;
                        ss.NanduXS = ReivewerXiShu[ss.StaffId].NanduXS;
                        ss.S = (double)ReivewerXiShu[ss.StaffId].S;
                        ss.A = (double)ReivewerXiShu[ss.StaffId].A;
                        ss.B = (double)ReivewerXiShu[ss.StaffId].B;
                        ss.C = (double)ReivewerXiShu[ss.StaffId].C;
                        ss.D = (double)ReivewerXiShu[ss.StaffId].D;
                        spDb.SaveChanges();
                    }
                    else
                    {
                        if (ss.jxType == "专案处理")
                        {
                            switch (ss.Item.CaseCoefficient)
                            {
                                case "S":
                                    ss.FinianlPoint = ss.totalActuallyPoint * 2.5;
                                    break;
                                case "A":
                                    ss.FinianlPoint = ss.totalActuallyPoint * 1.5;
                                    break;
                                case "B":
                                    ss.FinianlPoint = ss.totalActuallyPoint;
                                    break;
                                case "C":
                                    ss.FinianlPoint = ss.totalActuallyPoint * 0.7;
                                    break;
                                case "D":
                                    ss.FinianlPoint = ss.totalActuallyPoint * 0.4;
                                    break;
                                default:
                                    ss.FinianlPoint = ss.totalActuallyPoint;
                                    break;
                            }
                            ss.NanduXS = null;
                            ss.S = null;
                            ss.A = null;
                            ss.B = null;
                            ss.C = null;
                            ss.D = null;
                            spDb.SaveChanges();

                        }
                        else
                        {
                            ss.FinianlPoint = ss.totalActuallyPoint;
                            ss.NanduXS = null;
                            ss.S = null;
                            ss.A = null;
                            ss.B = null;
                            ss.C = null;
                            ss.D = null;
                            spDb.SaveChanges();
                        }
                    }

                }
            }

            var xsqList = retList.Where(ss => ss.jxType == "新申请处理").ToList();// || ss.jxType == "专案处理").ToList();
            var totalFPBLPoint = xsqList.Sum(p=>p.FinianlPoint); //新申请处理+专案 分配比率总点数
            var totalActionPoint = xsqList.Sum(p=>p.totalActuallyPoint); //新申请处理+专案 原始总点数

            foreach (StaffStatistics ss in xsqList)
            {
                //if(totalFPBLPoint * (totalActionPoint + jlPoint) == 0)
                //{
                //    Console.WriteLine("");
                //}
                ss.FinianlPoint = ss.FinianlPoint / totalFPBLPoint * (totalActionPoint + jlPoint);
                spDb.SaveChanges();
                
            }
            #endregion
        }
        [Authorize]
        public void CalJXPoint(int year, int month)
        {
            CalMonth calMonth = Context.CalMonths.Where<CalMonth>(c => c.Month == month && c.Year == year).FirstOrDefault();

            if (calMonth != null)
            {
                using (var t = Context.Database.BeginTransaction())
                {
                    try
                    {
                        var result = Context.StaffStatistics.Where(s => s.CalMonthId == calMonth.Id);
                        Context.StaffStatistics.RemoveRange(result);

                        var itemList = Context.PerformanceItems
                            .Include(s => s.ItemStaffs)
                            .Include(p=>p.Customer)
                            .Include(p=>p.Reviewer)
                            .Where(s => s.CalMonthId == calMonth.Id).ToList();

                        var rules = Context.BasePointRules.ToList();

                        foreach (var item in itemList)
                        {
                            Utility.Utility.CalBasePoint(item, rules);
                            Context.SaveChanges();

                            if (item.BasePoint > 0)
                            {
                                _calItemJX(item,Context);
                            }
                            else
                            {
                                var oldJxList = Context.StaffStatistics.Where(p => p.ItemId == item.Id);
                                Context.StaffStatistics.RemoveRange(oldJxList);
                            }

                        }

                        _CalJXPoint(calMonth, Context);
                        t.Commit();
                    }
                    catch (Exception ex)
                    {
                        t.Rollback();
                    }
                }
            }
        }
        [Authorize]
        public ProcessTask FinishedCalMonth(int year,int month)
        {
            CalMonth calMonth = Context.CalMonths.FirstOrDefault(c => c.Year == year && c.Month == month);
            if (calMonth != null)
            {
                var filename = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}-{calMonth.Year}{calMonth.Month}线下绩效核算数据.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);

                ExportDataResult result = new ExportDataResult()
                {
                    fileTask = fileTask,
                    calMonth = calMonth
                };

                System.Threading.Thread t = new System.Threading.Thread(new ParameterizedThreadStart(_FinishedCalMonth));
                t.Start(result);

                return fileTask;
            }

            return null;
        }
        /// <summary>
        /// 新申请/专案： 
        ///     调整点数 = 分配比例点数/分配比例点数之和*总点数
        ///     等级调整点数+项目组点数之和 =  基础点数 * 代理人等级系数 + 项目组案件调整点数
        ///     分配比例点数 = 个人平均难度系数 / 总平均难度系数 *（等级调整点数+项目组点数之和）
        ///     总点数=等级调整点数+项目组点数之和+奖励点数
        ///     奖励点数= S案数量*1.5 + A案数量 * 0.5
        ///     难度系数=(S案数量*2.5 + A案数量* 1.5 + B案数量*1.0 + C案数量*0.7+ D案数量*0.4) / 总案件数量
        ///     总案件数量= S案数量 + A案数量 + B案数量 + C案数量+ D案数量
        /// OA ：  
        ///     调整点数=  基础点数 * 等级系数
        /// 其他：  
        ///     直接加和基础点数 不需要乘以系数
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        private void _FinishedCalMonth(object proecssTask)
        {
            ExportDataResult result = (ExportDataResult)proecssTask;
            CalMonth calMonth = result.calMonth;
            
            if (calMonth != null || calMonth.Status !=4)
            {
                spDbContext spDb = new spDbContext();

                #region 删除垃圾数据
                var delEntity = spDb.StaffStatistics.Where(x => x.Item == null && x.CalMonthId == calMonth.Id).ToArray();
                if(delEntity.Length > 0)
                {
                    spDb.StaffStatistics.RemoveRange(delEntity);
                }
                #endregion

                calMonth = spDb.CalMonths.FirstOrDefault(c=>c.Id == calMonth.Id);
                using (var t = spDb.Database.BeginTransaction())
                {
                    try
                    {
                        _RefreshBasePoint(calMonth, spDb);
                        //_CalJXPoint(calMonth, Context);

                        calMonth.Status = 4;
                        spDb.SaveChanges();
                        
                        //等级核算案件统计
                         _StatisticsLevelCount(calMonth.Year, calMonth.Month, spDb);

                        #region 将待审核的申诉全部修改为已审核
                        var aList = spDb.AppealRecords.Where(a => a.State == 0).ToList();
                        aList.ForEach(a =>a.State = 1);
                        spDb.SaveChanges();
                        #endregion

                        t.Commit();
                        result.fileTask.Finished = true;

                    }
                    catch (Exception ex)
                    {
                        t.Rollback();
                        result.fileTask.ErrorMessage = ex.Message;
                        result.fileTask.Finished = true;
                    }
                }
            }
            else
            {
                result.fileTask.ErrorMessage = "指定月份没有数据或者已归档!";
                result.fileTask.Finished = true;
            }

        }

        /// <summary>
        /// 计算指定用户，指定年月的绩效统计信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        [Authorize]
        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
                {
                    List<StaffStatistics> retList = _CalMyStatistics(calMonth, userid);
                    List<StaffStatistics> otherList = _calMyOtherStatstics(calMonth, userid);
                    if (otherList != null)
                    {
                        retList.AddRange(otherList);
                    }
                    return retList;

                    #region old code
                    //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;
                    #endregion
                }
            //}
            //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]
        [Authorize]
        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));
            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 = "";
                    string strISDeps = "";

                    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}";


                        var dp = new spDbContext().DepartmentPositions.Where(x => x.StaffId == istaff.DoPersonId).Include(d=>d.department).FirstOrDefault();
                        if(dp != null )
                        {
                            strISDeps = string.IsNullOrEmpty(strISDeps) ? dp.department.Name : $"{strISDeps},{dp.department.Name}";
                        }
                        
                    }

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

                    if (item.ReviewerId != null && item.BasePoint.HasValue)
                    {
                        
                        System.Diagnostics.Debug.WriteLine($"{item.ReviewerId}");
                        var jxList = new spDbContext().StaffStatistics.Where<StaffStatistics>(s => s.ItemId == item.Id).ToList();//_calItemJX( 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.ExternalHandler?.Name;

                    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]
        [Authorize]
        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) * queryFilter.PageSize)
            {
                response = response
                    .Include(pi => pi.ItemStaffs).ThenInclude(iStaff => iStaff.DoPerson)
                    .Include(pi => pi.Reviewer)
                    .Include(pi => pi.Customer)
                    .Include(pi => pi.CalMonth)
                    .Include(pi => pi.ExternalHandler)
                    .OrderConditions<PerformanceItem>(queryFilter.Sorts)
                    .Skip((queryFilter.PageIndex - 1) * queryFilter.PageSize)
                    .Take(totals- (queryFilter.PageIndex - 1) * queryFilter.PageSize);
                    //.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)
                    .Include(pi => pi.ExternalHandler)
                    .OrderConditions<PerformanceItem>(queryFilter.Sorts)
                    .Skip((queryFilter.PageIndex - 1) * queryFilter.PageSize)
                    .Take(queryFilter.PageSize);
                    //.Pager<PerformanceItem>(queryFilter.PageIndex, queryFilter.PageSize, out totals);
            }
            ret.TotalCount = totals;



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


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

            ret.Results = retList.ToList();

            //string str= System.Text.Json.JsonSerializer.Serialize(ret);

            return ret;
        }

        private static void SetReturnItem(PerformanceItem item)
        {
            foreach (ItemStaff itemStaff in item.ItemStaffs)
            {
                itemStaff.DoPerson.ItemStaffs = null;
                itemStaff.DoPerson.ReviewerItems = null;
                itemStaff.DoPerson.Customers = null;
                itemStaff.DoPerson.AllocationRatios = null;
                itemStaff.DoPerson.ExternalHandlerItems = null;
                itemStaff.Item = null;
            }

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

            }

            if (item.ExternalHandler != null)
            {
                item.ExternalHandler.ReviewerItems = null;
                item.ExternalHandler.Customers = null;
                item.ExternalHandler.ItemStaffs = null;
                item.ExternalHandler.AllocationRatios = null;
                item.ExternalHandler.ExternalHandlerItems = null;
            }

            if (item.PreOastaff != null)
            {
                item.PreOastaff.ReviewerItems = null;
                item.PreOastaff.Customers = null;
                item.PreOastaff.ItemStaffs = null;
                item.PreOastaff.AllocationRatios = null;
                item.PreOastaff.ExternalHandlerItems = null;
            }

            if (item.WorkflowUser != null)
            {
                item.WorkflowUser.ReviewerItems = null;
                item.WorkflowUser.Customers = null;
                item.WorkflowUser.ItemStaffs = null;
                item.WorkflowUser.AllocationRatios = null;
                item.WorkflowUser.ExternalHandlerItems = null;
            }

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

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

        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) || (s.ExternalHandlerId == queryFilter.userId && s.Type=="其他新申请"));
                }
                else
                {
                    response = new spDbContext().PerformanceItems.Where<PerformanceItem>(dynamicWhere).Where(s => s.ReviewerId == queryFilter.userId);
                }
            }
            else
            {
                response = new spDbContext().PerformanceItems.Where<PerformanceItem>(dynamicWhere);

            }

            return response;
        }
        [Authorize]
        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;
        }
        [Authorize]
        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;
            
        }
        [Authorize]
        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;
        }
        [Authorize]
        public PerformanceItem GetItemInfoByCaseStage(string CaseNo, string DoItem,string caseStage,bool UpdateFromIPEasy=true)
        {
            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 || UpdateFromIPEasy)
            {
                var temObj = new Job.UpdateJXDataFromIPEasyJob().GetItemFromIPEasyDB(
                    new PerformanceItem() {CaseNo =CaseNo,DoItem =DoItem,CaseStage = caseStage },
                    Context 
                    );// IPEasyUtility.GetPerformanceRecord(CaseNo, DoItem, caseStage);

                
                System.Dynamic.ExpandoObject temExpandoObject = (System.Dynamic.ExpandoObject)temObj;

                retObj = new PerformanceItem();
                retObj.CaseNo = temObj.CaseNo.ToString();
                retObj.CaseName = temObj.CaseName.ToString();

                try
                {
                    if (((IDictionary<String, Object>)temObj).Keys.Contains("ApplicationType") && temObj.ApplicationType != null)
                        retObj.ApplicationType = temObj.ApplicationType.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CaseMemo") && temObj.CaseMemo != null)
                        retObj.CaseMemo = temObj.CaseMemo.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("BusinessType") && temObj.BusinessType != null)
                        retObj.BusinessType = temObj.BusinessType.ToString();

                    if (temObj.DoItem != null)
                        retObj.DoItem = temObj.DoItem.ToString();

                    
                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CaseStage") && temObj.CaseStage != null)
                        retObj.CaseStage = temObj.CaseStage.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CaseType") && temObj.CaseType != null)
                        retObj.CaseType = temObj.CaseType.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("DoItemState") && temObj.DoItemState != null)
                        retObj.DoItemState = temObj.DoItemState.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("DoItemCoefficient") && temObj.DoItemCoefficient != null)
                        retObj.DoItemCoefficient = temObj.DoItemCoefficient.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CaseCoefficient") && temObj.CaseCoefficient != null)
                        retObj.CaseCoefficient = temObj.CaseCoefficient.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("EntrustingDate") && temObj.EntrustingDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.EntrustingDate.ToString()))
                            retObj.EntrustingDate = DateTime.Parse(temObj.EntrustingDate.ToString());
                    }

                    if(((IDictionary<String, Object>)temObj).Keys.Contains("Country") && temObj.Country != null)
                    {
                        retObj.Country = temObj.Country.ToString();
                    }
                        

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("InternalDate") && temObj.InternalDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.InternalDate.ToString()))
                            retObj.InternalDate = DateTime.Parse(temObj.InternalDate.ToString());
                    }

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CustomerLimitDate") && temObj.CustomerLimitDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.CustomerLimitDate.ToString()))
                            retObj.CustomerLimitDate = DateTime.Parse(temObj.CustomerLimitDate.ToString());
                    }


                    if (((IDictionary<String, Object>)temObj).Keys.Contains("ReturnDate") && temObj.ReturnDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.ReturnDate.ToString()))
                            retObj.ReturnDate = DateTime.Parse(temObj.ReturnDate.ToString());
                    }

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("FinalizationDate") && temObj.FinalizationDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.FinalizationDate.ToString()))
                            retObj.FinalizationDate = DateTime.Parse(temObj.FinalizationDate.ToString());
                    }
                    if (((IDictionary<String, Object>)temObj).Keys.Contains("FinishedDate") && temObj.FinishedDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.FinishedDate.ToString()))
                            retObj.FinishedDate = DateTime.Parse(temObj.FinishedDate.ToString());
                    }
                }
                catch { }

                int temWordCount;

                if (temObj.WordCount != null)
                {
                    if (int.TryParse(temObj.WordCount, out temWordCount))
                    {
                        retObj.WordCount = temWordCount;
                    }
                }

                try
                {
                    if (temObj.CustomerName != null)
                    {
                        string strCustomer = temObj.CustomerName.ToString();
                        retObj.Customer = new Customer() { Name = strCustomer };
                        var temCustomer = Context.Customers.Where(c => c.Name == strCustomer.Trim()).FirstOrDefault();
                        if (temCustomer != null)
                        {
                            retObj.CustomerId = temCustomer.Id;
                        }
                    }
                }
                catch { }

                try
                {
                    if (temObj.Reviewer != null)
                    {
                        string strReViewer = temObj.Reviewer.ToString().Replace("-君龙", ""); 
                        var temReviewer = Context.Staffs.Where(s => s.Name == strReViewer.Trim()).FirstOrDefault();
                        if (temReviewer != null)
                        {
                            retObj.ReviewerId = temReviewer.Id;
                        }
                    }
                }
                catch { }

                try
                {
                    if (temObj.WorkflowUser != null)
                    {
                        string strWorkflowUser = temObj.WorkflowUser.ToString().Replace("-君龙", "");
                        var temReviewer = Context.Staffs.Where(s => s.Name == strWorkflowUser.Trim()).FirstOrDefault();
                        if (temReviewer != null)
                        {
                            retObj.WorkflowUserId  = temReviewer.Id;
                        }
                    }
                }
                catch { }

                if (temObj.DoPersons != null)
                {
                    string DoPersons = temObj.DoPersons.ToString();

                    string[] Persons = DoPersons.Split(new char[] { ',' });
                    List<ItemStaff> itemStaffs = new List<ItemStaff>();
                    foreach (var doPerson in Persons)
                    {
                        string strName = doPerson.Replace("-君龙","");
                        itemStaffs.Add(new ItemStaff() { DoPerson = new Staff() { Name = strName } });
                    }
                    retObj.ItemStaffs = itemStaffs;
                }
                
            }

            return retObj;
        }

        [Authorize]
        public PerformanceItem GetItemInfoByCaseStage(string CaseNo, List<string> DoItems, string caseStage, bool UpdateFromIPEasy = true)
        {
            PerformanceItem retObj = null;
            
            foreach (var DoItem in DoItems)
            {
                retObj = Context.PerformanceItems.Include(p => p.Customer).FirstOrDefault<PerformanceItem>(p => p.CaseNo == CaseNo.Trim()
                    && p.DoItem == DoItem.Trim() && p.CaseStage == caseStage);
                if(retObj != null)
                {
                    break;
                }
            }
            

            if (retObj == null || UpdateFromIPEasy)
            {
                var temObj = wispro.sp.utility.IPEasyUtility.GetPerformanceRecord(CaseNo, DoItems);
                //var temObj = new Job.UpdateJXDataFromIPEasyJob().GetItemFromIPEasyDB(
                //    new PerformanceItem() { CaseNo = CaseNo, DoItem = DoItem, CaseStage = caseStage },
                //    Context
                //    );// IPEasyUtility.GetPerformanceRecord(CaseNo, DoItem, caseStage);


                System.Dynamic.ExpandoObject temExpandoObject = (System.Dynamic.ExpandoObject)temObj;

                retObj = new PerformanceItem();
                retObj.CaseNo = temObj.CaseNo.ToString();
                retObj.CaseName = temObj.CaseName.ToString();

                try
                {
                    if (((IDictionary<String, Object>)temObj).Keys.Contains("ApplicationType") && temObj.ApplicationType != null)
                        retObj.ApplicationType = temObj.ApplicationType.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CaseMemo") && temObj.CaseMemo != null)
                        retObj.CaseMemo = temObj.CaseMemo.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("BusinessType") && temObj.BusinessType != null)
                        retObj.BusinessType = temObj.BusinessType.ToString();

                    if (temObj.DoItem != null)
                        retObj.DoItem = temObj.DoItem.ToString();


                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CaseStage") && temObj.CaseStage != null)
                        retObj.CaseStage = temObj.CaseStage.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CaseType") && temObj.CaseType != null)
                        retObj.CaseType = temObj.CaseType.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("DoItemState") && temObj.DoItemState != null)
                        retObj.DoItemState = temObj.DoItemState.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("DoItemCoefficient") && temObj.DoItemCoefficient != null)
                        retObj.DoItemCoefficient = temObj.DoItemCoefficient.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CaseCoefficient") && temObj.CaseCoefficient != null)
                        retObj.CaseCoefficient = temObj.CaseCoefficient.ToString();

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("EntrustingDate") && temObj.EntrustingDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.EntrustingDate.ToString()))
                            retObj.EntrustingDate = DateTime.Parse(temObj.EntrustingDate.ToString());
                    }

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("Country") && temObj.Country != null)
                    {
                        retObj.Country = temObj.Country.ToString();
                    }


                    if (((IDictionary<String, Object>)temObj).Keys.Contains("InternalDate") && temObj.InternalDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.InternalDate.ToString()))
                            retObj.InternalDate = DateTime.Parse(temObj.InternalDate.ToString());
                    }

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("CustomerLimitDate") && temObj.CustomerLimitDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.CustomerLimitDate.ToString()))
                            retObj.CustomerLimitDate = DateTime.Parse(temObj.CustomerLimitDate.ToString());
                    }


                    if (((IDictionary<String, Object>)temObj).Keys.Contains("ReturnDate") && temObj.ReturnDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.ReturnDate.ToString()))
                            retObj.ReturnDate = DateTime.Parse(temObj.ReturnDate.ToString());
                    }

                    if (((IDictionary<String, Object>)temObj).Keys.Contains("FinalizationDate") && temObj.FinalizationDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.FinalizationDate.ToString()))
                            retObj.FinalizationDate = DateTime.Parse(temObj.FinalizationDate.ToString());
                    }
                    if (((IDictionary<String, Object>)temObj).Keys.Contains("FinishedDate") && temObj.FinishedDate != null)
                    {
                        if (!string.IsNullOrEmpty(temObj.FinishedDate.ToString()))
                            retObj.FinishedDate = DateTime.Parse(temObj.FinishedDate.ToString());
                    }
                }
                catch { }

                int temWordCount;

                if (temObj.WordCount != null)
                {
                    if (int.TryParse(temObj.WordCount, out temWordCount))
                    {
                        retObj.WordCount = temWordCount;
                    }
                }

                try
                {
                    if (temObj.CustomerName != null)
                    {
                        string strCustomer = temObj.CustomerName.ToString();
                        retObj.Customer = new Customer() { Name = strCustomer };
                        var temCustomer = Context.Customers.Where(c => c.Name == strCustomer.Trim()).FirstOrDefault();
                        if (temCustomer != null)
                        {
                            retObj.CustomerId = temCustomer.Id;
                        }
                    }
                }
                catch { }

                try
                {
                    if (temObj.Reviewer != null)
                    {
                        string strReViewer = temObj.Reviewer.ToString().Replace("-君龙", "");
                        var temReviewer = Context.Staffs.Where(s => s.Name == strReViewer.Trim()).FirstOrDefault();
                        if (temReviewer != null)
                        {
                            retObj.ReviewerId = temReviewer.Id;
                        }
                    }
                }
                catch { }

                try
                {
                    if (temObj.WorkflowUser != null)
                    {
                        string strWorkflowUser = temObj.WorkflowUser.ToString().Replace("-君龙", "");
                        var temReviewer = Context.Staffs.Where(s => s.Name == strWorkflowUser.Trim()).FirstOrDefault();
                        if (temReviewer != null)
                        {
                            retObj.WorkflowUserId = temReviewer.Id;
                        }
                    }
                }
                catch { }

                if (temObj.DoPersons != null)
                {
                    string DoPersons = temObj.DoPersons.ToString();

                    string[] Persons = DoPersons.Split(new char[] { ',' });
                    List<ItemStaff> itemStaffs = new List<ItemStaff>();
                    foreach (var doPerson in Persons)
                    {
                        string strName = doPerson.Replace("-君龙", "");
                        itemStaffs.Add(new ItemStaff() { DoPerson = new Staff() { Name = strName } });
                    }
                    retObj.ItemStaffs = itemStaffs;
                }

            }

            return retObj;
        }

        [Authorize]
        public PerformanceItem GetItemInfoByMonthId(string CaseNo, string DoItem, string caseStage,int calMonthId)
        {
            var retObj = Context.PerformanceItems.Include(p => p.Customer).FirstOrDefault<PerformanceItem>(p => p.CaseNo == CaseNo.Trim()
              && p.DoItem == DoItem.Trim()  && p.CalMonthId == calMonthId);

            if(retObj != null)
            {
                if(string.IsNullOrEmpty (retObj.CaseStage) && string.IsNullOrEmpty(caseStage))
                {
                    return retObj;
                }
                else
                {
                    if(retObj.CaseStage == caseStage)
                    {
                        return retObj;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [Authorize]
        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;
        }

        #region 等级考核计算
        /// <summary>
        /// 等级考核案件量统计
        /// </summary>
        /// <param name="calMonthId"></param>
        /// <returns></returns>
        private List<StaffStatisticsforLevel> StatisticsCount(int calMonthId,spDbContext spDb )
        {
            var retlist = new List<StaffStatisticsforLevel>();

            #region 新申请
            var newList = spDb.PerformanceItems.Include(p => p.ItemStaffs).ThenInclude(p=>p.DoPerson)
                .Include(p=>p.CalMonth)
                .Where(p=>p.CalMonthId == calMonthId && p.BasePoint >0
                    && (p.Type =="新申请" || p.Type == "其他新申请" || p.Type =="专案" || (p.Type == "其它" && p.WordCount >0 && (p.DoItem =="新申请" || p.DoItem == "翻译")))
                ).ToList();

            foreach(var p in newList)
            {
                #region 严重延期降系数  
                string strCaseCeoffcient = p.CaseCoefficient;

                if(p.Type == "其他新申请")
                {
                    //宁德时代的案件按照B统计
                    strCaseCeoffcient = "B";
                }

                if (string.IsNullOrEmpty(strCaseCeoffcient))
                {
                    strCaseCeoffcient = "B";
                }
                if (p.isDanger() && string.IsNullOrEmpty(p.OverDueMemo))
                {
                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            strCaseCeoffcient = "A";
                            break;
                        case "A":
                            strCaseCeoffcient = "B";
                            break;
                        case "B":
                            strCaseCeoffcient = "C";
                            break;
                        case "C":
                            strCaseCeoffcient = "D";
                            break;
                    }
                }
                #endregion
                var pointList = spDb.StaffStatistics.Include(i=>i.Staff).Where(i=>i.ItemId == p.Id).ToList();

                #region 新申请处理
                var doList = pointList.Where(i => i.jxType.Contains("处理"));

                int doPersons = doList.Count();
                if (p.ItemStaffs.Count() > doList.Count())
                {
                    doPersons = p.ItemStaffs.Count;
                    foreach (ItemStaff itemStaff in p.ItemStaffs)
                    {
                        Staff staff = itemStaff.DoPerson;
                        if (staff == null)
                        {
                            staff = spDb.Staffs.FirstOrDefault(s => s.Id == itemStaff.DoPersonId);
                        }

                        

                        if (staff.Status  == "试用期")
                        {
                            doPersons =doPersons- 1; 
                        }
                    }
                }

                if(doPersons >0)
                {
                    foreach(var h in doList)
                    {
                        var temObj = retlist.FirstOrDefault(s=>s.StaffId == h.StaffId && s.Type == "新申请" && s.isReview == false);
                        if(temObj == null)
                        {
                            temObj = new StaffStatisticsforLevel();
                            temObj.StaffId = h.StaffId;
                        
                            temObj.GradeId = h.Staff.StaffGradeId.Value ;

                            temObj.CalMonthId = p.CalMonthId;
                            temObj.Type = "新申请";
                            retlist.Add(temObj);
                        }

                        switch (strCaseCeoffcient)
                        {
                            case "S":
                                if(p.Type == "专案")
                                {
                                    temObj.S += h.totalBasePoint.Value;
                                }
                                else
                                {
                                    temObj.S+=(double)(1.0M/ (decimal)doPersons);
                                }
                                break;
                            case "A":
                                if (p.Type == "专案")
                                {
                                    temObj.A += h.totalBasePoint.Value;
                                }
                                else
                                {
                                    temObj.A += (double)(1.0M / (decimal)doPersons);
                                }
                                break;
                            case "B":
                                if (p.Type == "专案")
                                {
                                    temObj.B += h.totalBasePoint.Value;
                                }
                                else
                                {
                                    temObj.B += (double)(1.0M / (decimal)doPersons);
                                }
                                break;
                            case "C":
                                if (p.Type == "专案")
                                {
                                    temObj.C += h.totalBasePoint.Value;
                                }
                                else
                                {
                                    temObj.C += (double)(1.0M / (decimal)doPersons);
                                }
                                break;
                            case "D":
                                if (p.Type == "专案")
                                {
                                    temObj.D += h.totalBasePoint.Value;
                                }
                                else
                                {
                                    temObj.D += (double)(1.0M / (decimal)doPersons);
                                }
                                break;
                        }
                    
                    }
                }
                #endregion

                #region 新申请审核
                var reviewer = pointList.FirstOrDefault(i => i.jxType.Contains("审核"));
                if(reviewer != null)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == reviewer.StaffId && s.Type == "新申请" && s.isReview == true);

                    if(temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = reviewer.StaffId;
                        temObj.GradeId = reviewer.Staff.StaffGradeId.Value;

                       
                        temObj.CalMonthId = p.CalMonthId;
                        temObj.Type = "新申请";
                        temObj.isReview = true;
                        retlist.Add(temObj);
                    }

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

            #region 国内OA
            var cnOA = spDb.PerformanceItems.Include(x=>x.ItemStaffs)
                .Where(x => x.CalMonthId == calMonthId && x.Type == "OA" && x.CaseNo.Contains("CN") && x.BasePoint > 0.3
                ).ToList();

            //因为不是所有的OA案件都是答复审查意见，所以有些应该没有一通或首次转入信息，去掉这个条件：
            //&& (x.CaseStage == "一通" || x.DoItemCoefficient.Contains("首次转入"))

            foreach (var p in cnOA)
            {
                var pointList = spDb.StaffStatistics.Include(i => i.Staff).Where(i=> i.ItemId == p.Id).ToList();

                #region 国内OA处理
                var doList = pointList.Where(i => i.jxType.Contains("处理"));
                foreach (var h in doList)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == h.StaffId && s.Type == "国内OA" && s.isReview == false);
                    if (temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = h.StaffId;
                        temObj.GradeId = h.Staff.StaffGradeId.Value ;
                        
                        temObj.CalMonthId = p.CalMonthId;
                        temObj.Type = "国内OA";
                        retlist.Add(temObj);
                    }

                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            temObj.S += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "A":
                            temObj.A += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "B":
                            temObj.B += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "C":
                            temObj.C += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "D":
                            temObj.D += (double)(1.0M / (decimal)doList.Count());
                            break;
                        default:
                            temObj.B += (double)(1.0M / (decimal)doList.Count());
                            break;
                    }

                }
                #endregion

                #region 国内OA审核
                var reviewer = pointList.FirstOrDefault(i => i.jxType.Contains("审核"));
                if (reviewer != null)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == reviewer.StaffId && s.Type == "国内OA" && s.isReview == true);

                    if (temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = reviewer.StaffId;
                        temObj.GradeId = reviewer.Staff.StaffGradeId.Value;
                        
                        temObj.CalMonthId = p.CalMonthId;
                        temObj.Type = "国内OA";
                        temObj.isReview = true;

                        retlist.Add(temObj);
                    }

                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            temObj.S += 1;
                            break;
                        case "A":
                            temObj.A += 1;
                            break;
                        case "B":
                            temObj.B += 1;
                            break;
                        case "C":
                            temObj.C += 1;
                            break;
                        case "D":
                            temObj.D += 1;
                            break;
                    }
                }
                #endregion
            }
            #endregion

            #region 涉外OA
            var gwOA = spDb.PerformanceItems.Include(x => x.ItemStaffs)
                .Where(x => x.CalMonthId == calMonthId && x.Type == "OA" && !x.CaseNo.Contains("CN") && x.BasePoint>0.5
                && (x.DoItemCoefficient.Contains("实质"))).ToList();

            foreach (var p in gwOA)
            {
                var pointList = spDb.StaffStatistics.Include(i => i.Staff).Where(i => i.ItemId == p.Id).ToList();

                #region 涉外OA处理
                var doList = pointList.Where(i => i.jxType.Contains("处理"));
                foreach (var h in doList)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == h.StaffId && s.Type == "涉外OA" && s.isReview == false);
                    if (temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = h.StaffId;
                        temObj.GradeId = h.Staff.StaffGradeId.Value;
                        
                        temObj.CalMonthId = p.CalMonthId;
                        temObj.Type = "涉外OA";
                        retlist.Add(temObj);
                    }

                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            temObj.S += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "A":
                            temObj.A += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "B":
                            temObj.B += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "C":
                            temObj.C += (double)(1.0M / (decimal)doList.Count());
                            break;
                        case "D":
                            temObj.D += (double)(1.0M / (decimal)doList.Count());
                            break;
                        default:
                            temObj.B += (double)(1.0M / (decimal)doList.Count());
                            break;
                    }

                }
                #endregion

                #region 涉外OA审核
                var reviewer = pointList.FirstOrDefault(i => i.jxType.Contains("审核"));
                if (reviewer != null)
                {
                    var temObj = retlist.FirstOrDefault(s => s.StaffId == reviewer.StaffId && s.Type == "涉外OA" && s.isReview == true);

                    if (temObj == null)
                    {
                        temObj = new StaffStatisticsforLevel();
                        temObj.StaffId = reviewer.StaffId;
                        temObj.GradeId = reviewer.Staff.StaffGradeId.Value;
                        
                        temObj.CalMonthId = calMonthId;
                        temObj.Type = "涉外OA";
                        temObj.isReview = true;

                        retlist.Add(temObj);
                    }

                    switch (p.CaseCoefficient)
                    {
                        case "S":
                            temObj.S += 1;
                            break;
                        case "A":
                            temObj.A += 1;
                            break;
                        case "B":
                            temObj.B += 1;
                            break;
                        case "C":
                            temObj.C += 1;
                            break;
                        case "D":
                            temObj.D += 1;
                            break;
                        default:
                            temObj.C += 1;
                            break;

                    }
                }
                #endregion
            }
            #endregion

            return retlist;
        }

        /// <summary>
        /// 指定等级人员等级考核案件量统计汇总
        /// </summary>
        /// <param name="levelCaseCounts"></param>
        /// <param name="GradeCode"></param>
        /// <param name="calReviw"></param>
        /// <returns></returns>
        private LevelStatisticsResult CalLevelStatistics(List<StaffStatisticsforLevel> levelCaseCounts,string GradeCode,bool calReviw)
        {
            var retList = new List<LevelStatistics>();
            
            var temList = levelCaseCounts.Where(x=>x.Grade.Grade.StartsWith(GradeCode)).ToList();

            foreach (var s in temList)
            {
                var temObj = retList.FirstOrDefault(i=>i.StaffId == s.StaffId);

                if(temObj == null)
                {
                    temObj = new LevelStatistics();
                    temObj.StaffId = s.StaffId;
                    temObj.StaffName = s.Staff.Name;
                    temObj.Grade = s.Grade.Grade;
                    //var Department = Context.DepartmentPositions.FirstOrDefault(x => x.StaffId == s.StaffId);
                    temObj.Department = Context.DepartmentPositions.Include(x => x.department).FirstOrDefault(x => x.StaffId == s.StaffId)?.department.Name;
                    
                    retList.Add(temObj);
                }

                if (!s.isReview)
                {
                    if(s.Type == "国内OA")
                    {
                        temObj.dS += (double)((decimal)s.S / 2M);
                        temObj.dA += (double)((decimal)s.A / 2M);
                        temObj.dB += (double)((decimal)s.B / 2M);
                        temObj.dC += (double)((decimal)s.C / 2M);
                        temObj.dD += (double)((decimal)s.D / 2M);
                    }
                    else
                    {
                        temObj.dS += s.S;
                        temObj.dA += s.A;
                        temObj.dB += s.B;
                        temObj.dC += s.C;
                        temObj.dD += s.D;
                    }
                }
                else
                {
                    if (calReviw)
                    {
                        if (s.Type == "国内OA")
                        {
                            temObj.vS += (double)((decimal)s.S / 2M);
                            temObj.vA += (double)((decimal)s.A / 2M);
                            temObj.vB += (double)((decimal)s.B / 2M);
                            temObj.vC += (double)((decimal)s.C / 2M);
                            temObj.vD += (double)((decimal)s.D / 2M);
                        }
                        else
                        {
                            temObj.vS += s.S;
                            temObj.vA += s.A;
                            temObj.vB += s.B;
                            temObj.vC += s.C;
                            temObj.vD += s.D;
                        }
                    }
                }
            }

            return new LevelStatisticsResult() {Grade =GradeCode ,  Statistics = retList};

        }

        /// /// <summary>
        /// 获取季度等级考核结果
        /// </summary>
        /// <param name="year">考核年度</param>
        /// <param name="quarter">考核季度，取值为1、2、3、4</param>
        /// <param name="GradeCode">等级</param>
        /// <returns></returns>
        [Authorize]
        public LevelStatisticsResult CalAgentLevel(int year,int quarter,string GradeCode)
        {
            LevelStatisticsResult result = new LevelStatisticsResult();
            DateTime tempDate=DateTime.Parse($"{year}-01-01");
            var Months = new List<int>() { 1,2,3};
            switch (quarter)
            {
                case 1:
                    if(year == 2022)
                    {
                        Months = new List<int>() { 2, 3 };
                    }
                    else
                    {
                        Months = new List<int>() { 1, 2, 3 };
                    }
                    tempDate = DateTime.Parse($"{year}-01-01");
                    break;
                case 2:
                    Months = new List<int>() { 4,5,6 };
                    tempDate = DateTime.Parse($"{year}-04-01");
                    break;
                case 3:
                    Months = new List<int>() { 7,8,9};
                    tempDate = DateTime.Parse($"{year}-07-01");
                    break;
                case 4:
                    Months = new List<int>() { 10,11,12 };
                    tempDate = DateTime.Parse($"{year}-10-01");
                    break;
            }

            //var mList = Context.CalMonths.Where(c=>c.Year == year && Months.Contains(c.Month)).ToList();
            //foreach (var m in mList)
            //{
            //    StatisticsLevelCount(m.Id);
            //}
            var resp = Context.StaffStatisticsforLevels
                .Include(p => p.Staff)
                .Include(p => p.Grade)
                .Where(p => Months.Contains(p.CalMonth.Month) && p.CalMonth.Year == year && p.Staff.RegularDate.Value < tempDate && p.Staff.IsGradeAssess);
            var temtList = resp.ToList();

            bool isReview = (GradeCode == "A");
            result = CalLevelStatistics(temtList, GradeCode, isReview);

            if (!isReview)
            {
                string UpgradCode = "A";
                switch (GradeCode)
                {
                    case "B":
                        UpgradCode = "A";
                        break;
                    case "C":
                        UpgradCode = "B";
                        break;
                    case "D":
                        UpgradCode = "C";
                        break;
                }

                var temResult = CalLevelStatistics(temtList, UpgradCode, isReview);
                result.UpgradeBase = temResult.doAverage();
            }

            if (result.Statistics.Count > 0)
            {
                var temAverage = result.pAverage(result.Statistics[0].StaffId);
            }
            return result;
        }

        /// <summary>
        /// 重新统计指定月份的人员案件数量
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        [Authorize]
        public bool StatisticsLevelCount(int year,int month)
        {
            try
            {
                _StatisticsLevelCount(year,month,Context);

                return true;
            }
            catch { return false; }
        }

        private void _StatisticsLevelCount(int year, int month,spDbContext spDb)
        {
            var temObj = spDb.CalMonths.FirstOrDefault(l => l.Year == year && l.Month == month);

            if (temObj != null)
            {
                var temList = spDb.StaffStatisticsforLevels.Where(l => l.CalMonthId == temObj.Id);
                spDb.StaffStatisticsforLevels.RemoveRange(temList);

                var NewList = StatisticsCount(temObj.Id, spDb);

                spDb.StaffStatisticsforLevels.AddRange(NewList);
                spDb.SaveChanges();
            }
        }
        #endregion

        #region 导入绩效数据
        /// <summary>
        /// 从维德系统中下载报表，并导入到绩效数据库中
        /// </summary>
        /// <param name="dataType">
        /// 0：每月绩效统计--发客户超过一个月未完成案件
        /// 1：每月绩效统计--上个月递交完成案件
        /// 2：每月绩效统计--中国一次OA授权表
        /// </param>
        [HttpGet,HttpPost]
        public void ImportJXData(int dataType)
        {
            new ImportReportJob().ImportData(dataType);
        }

        [HttpGet,HttpPost]
        public void InportJXDataByName(string ReportName)
        {
            new ImportReportJob().ImportData(ReportName);
        }
        #endregion
    }
}