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

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

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

        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            return ret;
        }

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

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

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

                return ret;
            }

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

                return ret;
            }

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

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

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

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

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

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

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

            ret.TotalCount = results.Count();

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

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

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

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

            ret.Results = retList;

            return ret;
        }

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

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

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

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

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

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

            
            ret.TotalCount = results.Count();

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

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

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

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

            ret.Results = retList;

            return ret;
        }

        public double DegreeOfDifficulty(int year,int month, int? userId = null)
        {
            IDictionary<string, double> CaseXiShu = new Dictionary<string, double>();
            var list = Context.CaseCeoffcients;

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

            var results = Context.PerformanceItems.Where<PerformanceItem>(p => p.CalMonth.Year == year && p.CalMonth.Month == month && ((p.Type == "新申请" && p.BasePoint > 0) || p.Type == "专案") && p.BasePoint > 0.0);

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

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

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

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

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

            }
            #endregion

            #region 统计计算
            //var groupResult = results.GroupBy(x => x.CaseCoefficient).Select(g=> new { 
            //    CaseCeoffcient = g.Key,
            //    count = g.Count()
            //});

            //int iCount = 0;
            //double d = 0.0;
            //foreach(var g in groupResult)
            //{
            //    if (!string.IsNullOrEmpty(g.CaseCeoffcient))
            //    {


            //        if (CaseXiShu.ContainsKey(g.CaseCeoffcient))
            //        {
            //            d += g.count * CaseXiShu[g.CaseCeoffcient];
            //            iCount += g.count;
            //        }
            //    }
            //}
            #endregion

            return d /(double)iCount;
        }

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

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

            return new List<string>();
        }
        private List<StaffStatistics> _CalMyStatistics(CalMonth calMonth, int? userid = null)
        {

            double gspjXS = DegreeOfDifficulty(calMonth.Year, calMonth.Month);

            //未归档，从绩效记录中统计数据
            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)
                //{

                    Utility.Utility.CalBasePoint(item,Rules);

                    Context.SaveChanges();
                //}

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

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

                    List<StaffStatistics> temItemStatics;

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

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

            }

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

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

                    ss.totalActuallyPoint = ss.totalBasePoint * staffXiShu[ss.StaffId] / gspjXS;
                }
                else
                {
                    ss.totalActuallyPoint = ss.totalBasePoint;
                }

                ss.CalMonth.PerformanceItems = null;
            }


            return retList;

        }

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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


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


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


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

            return itemStatistics;
        }

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

            if (calMonth != null || calMonth.Status !=4)
            {
                var retList = CalMyStatistics(year, month);

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

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

        }
        /// <summary>
        /// 计算指定用户，指定年月的绩效统计信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public List<StaffStatistics> CalMyStatistics(int year,int month, int? userid=null)
        {
            CalMonth calMonth = Context.CalMonths.Where<CalMonth>(c => c.Month == month && c.Year == year).FirstOrDefault();
            
            if(calMonth == null)
            {
                return null;
            }
            else
            {
                if(calMonth.Status  == 4)
                {
                    //已归档，归档数据库中直接取出记录
                    if (userid == null)
                    {
                        return Context.StaffStatistics.Where<StaffStatistics>(s => s.CalMonthId == calMonth.Id).ToList<StaffStatistics>();
                    }
                    else
                    {
                        return Context.StaffStatistics.Where<StaffStatistics>(s => s.CalMonthId == calMonth.Id && s.StaffId == userid).ToList<StaffStatistics>();
                    }
                }
                else
                {
                    return _CalMyStatistics(calMonth, userid);
                }
            }
        }
        
        private string GetExpress(IList<FieldCondition> conditions)
        {
            string str = "";

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

            return str;
        }

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

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

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

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

            return fileTask;
        }

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

            IQueryable<PerformanceItem> response = NewMethod(queryFilter);

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

            

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

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

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

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

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

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

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

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

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

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

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

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

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


            fileTask.Finished = true;
        }

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

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

            IQueryable<PerformanceItem> response = NewMethod(queryFilter);

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

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



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


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

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

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

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

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

            ret.Results = retList.ToList();

            return ret;
        }

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

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

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

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

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

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

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

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

            }

            return response;
        }

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

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

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

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

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

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

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


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

                            Context.ProjectContentRecords.Add(projectContents.ProjectContentRecord);

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

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

            return retResponse;
        }

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

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

            return retObj;
            
        }

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

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

            return retObj;
        }

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

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

            return retObj;
        }

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

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

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

                Context.ProjectInfos.Add(project);

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

                Context.PerformanceItems.Remove(p);

                Context.SaveChanges();
                
            }

            return true;
        }
    }
}