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

using AntDesign;
using DocumentFormat.OpenXml.InkML;
using DocumentFormat.OpenXml.Presentation;
using DynamicExpresso;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using NPOI.OpenXmlFormats.Vml.Spreadsheet;
using NPOI.OpenXmlFormats.Wordprocessing;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Dynamic;
using System.Linq;
using System.Linq.Expressions;
using System.Text.RegularExpressions;
using wispro.sp.api.AppealHandler;
using wispro.sp.api.Job;
using wispro.sp.api.Services;
using wispro.sp.entity;
using wispro.sp.entity.CompareCase;
using wispro.sp.share;
using static OneOf.Types.TrueFalseOrNull;

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


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

        }
        [Authorize]
        public ApiSaveResponse Save(CaseInfo caseInfo)
        {
            ApiSaveResponse ret = new ApiSaveResponse();
            ret.Success = true;

            using (Context.Database.BeginTransaction())
            {
                try
                {
                    #region 客户处理
                    if (caseInfo.Customer != null && !string.IsNullOrEmpty(caseInfo.Customer.Name))
                    {
                        var temCustomer = Context.Customers.Where<Customer>(c => c.Name == caseInfo.Customer.Name).FirstOrDefault();

                        if (temCustomer == null)
                        {
                            temCustomer = new Customer() { Name = caseInfo.Customer.Name };
                            //item.Customer.Id = 0;

                            Context.Customers.Add(temCustomer);
                            Context.SaveChanges();
                            caseInfo.Customer = temCustomer;
                            //item.CustomerId = item.Customer.Id;
                        }
                        else
                        {
                            caseInfo.Customer = temCustomer;
                        }

                        caseInfo.CustomerId = caseInfo.Customer.Id;
                        caseInfo.Customer = null;
                    }
                    else
                    {
                        caseInfo.Customer = null;
                    }
                    #endregion

                    #region 审核人
                    if (caseInfo.Reviewer != null && !string.IsNullOrEmpty(caseInfo.Reviewer.Name))
                    {
                        var temReviewer = Context.Staffs.Where<Staff>(c => c.Name == caseInfo.Reviewer.Name).FirstOrDefault();

                        if (temReviewer == null)
                        {
                            temReviewer = new Staff() { Name = caseInfo.Reviewer.Name };
                            //item.Customer.Id = 0;

                            Context.Staffs.Add(temReviewer);
                            Context.SaveChanges();
                            caseInfo.Reviewer = temReviewer;
                            //item.CustomerId = item.Customer.Id;
                        }
                        else
                        {
                            caseInfo.Reviewer = temReviewer;
                        }

                        caseInfo.ReviewerId = caseInfo.Reviewer.Id;
                        caseInfo.Reviewer = null;
                    }
                    else
                    {
                        caseInfo.Reviewer = null;
                    }
                    #endregion

                    if (caseInfo.DRRAbstract != null) {
                        Context.CaseCompareResults.Add(caseInfo.DRRAbstract);
                        Context.SaveChanges();
                        caseInfo.DRRAbstractId = caseInfo.DRRAbstract.Id;
                        caseInfo.DRRAbstract = null;
                    }
                    if (caseInfo.DRRCalim != null)
                    {
                        Context.CaseCompareResults.Add(caseInfo.DRRCalim);
                        Context.SaveChanges();
                        caseInfo.DRRCalimId = caseInfo.DRRCalim.Id;
                        caseInfo.DRRCalim = null;
                    }
                    if (caseInfo.DRRFulltext != null)
                    {
                        Context.CaseCompareResults.Add(caseInfo.DRRFulltext);
                        Context.SaveChanges();
                        caseInfo.DRRFulltextId = caseInfo.DRRFulltext.Id;
                        caseInfo.DRRFulltext = null;
                    }
                    if (caseInfo.DRRAll != null)
                    {
                        Context.CaseCompareResults.Add(caseInfo.DRRAll);
                        Context.SaveChanges();
                        caseInfo.DRRAllId = caseInfo.DRRAll.Id;
                        caseInfo.DRRAll = null;

                    }
                    if (caseInfo.RFRAbstract != null)
                    {
                        Context.CaseCompareResults.Add(caseInfo.RFRAbstract);
                        Context.SaveChanges();
                        caseInfo.RFRAbstractId = caseInfo.RFRAbstract.Id;
                        caseInfo.RFRAbstract = null;
                    }
                    if (caseInfo.RFRCalim != null)
                    {
                        Context.CaseCompareResults.Add(caseInfo.RFRCalim);
                        Context.SaveChanges();
                        caseInfo.RFRCalimId = caseInfo.RFRCalim.Id;
                        caseInfo.RFRCalim = null;
                    }
                    if (caseInfo.RFRFulltext != null)
                    {
                        Context.CaseCompareResults.Add(caseInfo.RFRFulltext);
                        Context.SaveChanges();
                        caseInfo.RFRFulltextId = caseInfo.RFRFulltext.Id;
                        caseInfo.RFRFulltext = null;
                    }
                    if (caseInfo.RFRAll != null)
                    {
                        Context.CaseCompareResults.Add(caseInfo.RFRAll);
                        Context.SaveChanges();
                        caseInfo.RFRAllId = caseInfo.RFRAll.Id;
                        caseInfo.RFRAll = null;
                    }

                    Context.CaseInfos.Add(caseInfo);
                    Context.SaveChanges();
                    Context.Database.CommitTransaction();

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

            return ret;
        }

        public ListApiResponse<CaseInfo> QueryFilter(QueryFilter queryFilter)
        {
            ListApiResponse<CaseInfo> ret = new ListApiResponse<CaseInfo>();

            IQueryable<CaseInfo> response = NewMethod(queryFilter);

            int totals = response.Count();

            if (totals > 0 && totals < (queryFilter.PageIndex) * queryFilter.PageSize)
            {
                response = response
                     .Include(pi => pi.DRRCalim)
                    .Include(pi => pi.DRRAbstract)
                    .Include(pi => pi.DRRFulltext)
                    .Include(pi => pi.DRRAll)
                    .Include(pi => pi.RFRCalim)
                    .Include(pi => pi.RFRAbstract)
                    .Include(pi => pi.RFRFulltext)
                    .Include(pi => pi.RFRAll)
                    .Include(pi => pi.Customer)
                    .Include(pi => pi.Reviewer)
                    .OrderConditions<CaseInfo>(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.DRRCalim)
                    .Include(pi => pi.DRRAbstract)
                    .Include(pi => pi.DRRFulltext)
                    .Include(pi=>pi.DRRAll)
                    .Include(pi => pi.RFRCalim)
                    .Include(pi => pi.RFRAbstract)
                    .Include(pi => pi.RFRFulltext)
                    .Include(pi => pi.RFRAll)
                    .Include(pi => pi.Customer)
                    .Include(pi => pi.Reviewer)
                    .OrderConditions<CaseInfo>(queryFilter.Sorts)
                    .Skip((queryFilter.PageIndex - 1) * queryFilter.PageSize)
                    .Take(queryFilter.PageSize);
            }
            ret.TotalCount = totals;

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


            #region 将某些属性设为null，避免循环取值造成返回json过大
            foreach (CaseInfo item in retList)
            {
                if (item.Customer != null)
                {
                    item.Customer.PerformanceItems = null;
                    item.Customer.ResponseMan = null;
                }
                if (item.Reviewer != null)
                {
                    item.Reviewer.Customers = null;

                    item.Reviewer.ExternalHandlerItems = null;
                    item.Reviewer.ItemStaffs = null;
                    item.Reviewer.AllocationRatios = null;
                    item.Reviewer.ReviewerItems = null;
                }
            }
            #endregion

            ret.Results = retList.ToList();

            return ret;
        }

        public bool CaseExist(string caseNo)
        {
            return Context.CaseInfos.Where(p => p.CaseNo == caseNo.Trim()).Count() > 0;
        }

        public static (double, double) UpdateStatistics(int n, double mean, double variance, double newValue)
        {
            double newMean = (n * mean + newValue) / (n + 1);
            
            double newVariance = Math.Sqrt((n * (mean - newMean) * (mean - newMean) + (newValue - newMean) * (newValue - newMean) + n * variance * variance) / (n + 1));

            return (newMean, newVariance);
        }

        /// <summary>
        /// 计算指定日期之前一年的指定客户的平均权利要求差异度和标准方差
        /// </summary>
        /// <param name="endDate">结束日期</param>
        /// /// <param name="customerName">客户名称，默认不指定，获取所有客户的平均权利要求差异度和标准方差</param>
        /// <returns></returns>
        private List<CustomerCompareStatistics> getLaseYearMean_Variance(DateTime endDate,List<CaseInfo> caseList,string customerName=null)
        {
            if(caseList == null)
            {
                caseList = Context.CaseInfos.Where<CaseInfo>(
                        p => p.CreateTime >= endDate.AddYears(-1) &&
                        p.CreateTime <= endDate &&
                        p.Customer.Name == customerName
                        )
                    .Include(p => p.Customer)
                    .Include(p => p.Reviewer)
                    .Include(p => p.DRRAbstract)
                    .Include(p => p.DRRAbstract)
                    .Include(p => p.DRRCalim)
                    .Include(p => p.DRRFulltext)
                    .Include(p => p.DRRAll)
                    .Include(p => p.RFRAbstract)
                    .Include(p => p.RFRCalim)
                    .Include(p => p.RFRFulltext)
                    .Include(p => p.RFRAll)
                    .ToList();
            }

            //IIncludableQueryable<CaseInfo, CompareResult> caseList;
            var temcaseList = caseList;
            if (customerName != null) {
                temcaseList = caseList.Where(p=> p.Customer.Name == customerName)
                .ToList();
            }
            

            List < CustomerCompareStatistics > retList = new List < CustomerCompareStatistics >();

            foreach ( var item in temcaseList)
            {
                var temObj = retList.Where(p=>p.CustomerName == item.Customer.Name).FirstOrDefault<CustomerCompareStatistics>();
                if(temObj == null) {
                    temObj = new CustomerCompareStatistics();
                    temObj.CustomerName = item.Customer.Name;
                    retList.Add(temObj);
                }

                temObj.CaseCount += 1;

                if (item.DRRCalim != null)
                {
                    if (temObj.Diff_FinalDrafted_Claims_Count > 0) {
                        var retStatistics = UpdateStatistics(temObj.Diff_Drafted_Claims_Count, temObj.Diff_Drafted_Claims_Avg, temObj.Diff_Drafted_Claims_Std, item.DRRCalim.diffRate);
                        temObj.Diff_Drafted_Claims_Std = retStatistics.Item2;
                        temObj.Diff_Drafted_Claims_Avg = retStatistics.Item1;
                        temObj.Diff_Drafted_Claims_Count += 1;
                    }
                    else
                    {
                        temObj.Diff_Drafted_Claims_Std = 0.0;
                        temObj.Diff_Drafted_Claims_Avg = item.DRRCalim.diffRate;
                        temObj.Diff_Drafted_Claims_Count = 1;
                    }
                }

                if (item.RFRCalim != null)
                {
                    if (temObj.Diff_FinalDrafted_Claims_Count > 0)
                    {
                        var retStatistics = UpdateStatistics(temObj.Diff_FinalDrafted_Claims_Count, temObj.Diff_FinalDrafted_Claims_Avg, temObj.Diff_FinalDrafted_Claims_Std, item.RFRCalim.diffRate);
                        temObj.Diff_FinalDrafted_Claims_Std = retStatistics.Item2;
                        temObj.Diff_FinalDrafted_Claims_Avg = retStatistics.Item1;
                        temObj.Diff_FinalDrafted_Claims_Count += 1;
                    }
                    else
                    {
                        temObj.Diff_FinalDrafted_Claims_Std = 0.0;
                        temObj.Diff_FinalDrafted_Claims_Avg = item.RFRCalim.diffRate;
                        temObj.Diff_FinalDrafted_Claims_Count = 1;
                    }
                }
            }

            return retList;
        }

        /// <summary>
        /// 获取指定时间内客户的权要修改差异率最高的10各案件
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="customerName">客户名称</param>
        /// <param name="type">类型：
        ///     0：外部核稿差异率，默认值，
        ///     1：内部核稿差异率
        /// </param>
        /// <returns></returns>
        private List<CaseInfo> getTop10DiffRateCases(DateTime start, DateTime end,string customerName,int type=0,List<CaseInfo> caseList=null)
        {
            if (caseList == null) {
                
                caseList = Context.CaseInfos.Where<CaseInfo>(
                    p => p.CreateTime >= end.AddYears(-1) &&
                    p.CreateTime <= end &&
                    p.Customer.Name == customerName
                    )
                .Include(p => p.Customer)
                .Include(p => p.Reviewer)
                .Include(p => p.DRRAbstract)
                .Include(p => p.DRRAbstract)
                .Include(p => p.DRRCalim)
                .Include(p => p.DRRFulltext)
                .Include(p => p.DRRAll)
                .Include(p => p.RFRAbstract)
                .Include(p => p.RFRCalim)
                .Include(p => p.RFRFulltext)
                .Include(p => p.RFRAll)
                .ToList();
                
            }
            if (type == 0) {

                var top10Case = caseList.Where<CaseInfo>(p=>p.Customer.Name == customerName)
                .OrderByDescending(p => p.RFRCalim?.diffRate).Take(10);

                return top10Case.Where<CaseInfo>(c=>c.CreateTime>=start).ToList();
            }
            else
            {
                var top10Case = caseList.Where<CaseInfo>(p => p.Customer.Name == customerName)
                .OrderByDescending(p => p.DRRCalim?.diffRate).Take(10);

                return top10Case.Where<CaseInfo>(c => c.CreateTime >= start).ToList();
            }
        }

        /// <summary>
        /// 获取指定时间内客户权要修改差异度计算出的异常案件
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="customerName">客户名称</param>
        /// <returns></returns>
        private List<CaseInfo> GetAbnormalCases(DateTime start, DateTime end, string customerName,List<CaseInfo> caseList)
        {
            var customerStatics = getLaseYearMean_Variance(end, caseList,customerName);
            var cStatics = customerStatics.Where(s => s.CustomerName == customerName).FirstOrDefault();
            return _GetAbnormalCases(start, end, customerName, cStatics,caseList);

        }

        private  List<CaseInfo> _GetAbnormalCases(DateTime start, DateTime end, string customerName, CustomerCompareStatistics cStatics,List<CaseInfo> caseList)
        {
            if (cStatics != null)
            {
                if (caseList == null) {
                    caseList = Context.CaseInfos.Where<CaseInfo>(
                        p => p.CreateTime >= end.AddYears(-1) &&
                        p.CreateTime <= end &&
                        p.Customer.Name == customerName
                        )
                    .Include(p => p.Customer)
                    .Include(p => p.Reviewer)
                    .Include(p => p.DRRAbstract)
                    .Include(p => p.DRRAbstract)
                    .Include(p => p.DRRCalim)
                    .Include(p => p.DRRFulltext)
                    .Include(p => p.DRRAll)
                    .Include(p => p.RFRAbstract)
                    .Include(p => p.RFRCalim)
                    .Include(p => p.RFRFulltext)
                    .Include(p => p.RFRAll)
                    .ToList();

                }

                var retList = new List<CaseInfo>();

                var temCaseList = caseList.Where(p=>p.Customer.Name== customerName).ToList();

                foreach (var item in temCaseList)
                {
                    if (item.CreateTime >= start)
                    {
                        double first = item.DRRCalim == null ? 0.0 : item.DRRCalim.diffRate;
                        double second = item.RFRCalim == null ? 0.0 : item.RFRCalim.diffRate;

                        double score1 = (first - cStatics.Diff_Drafted_Claims_Avg) / cStatics.Diff_Drafted_Claims_Std;
                        double score2 = (second - cStatics.Diff_FinalDrafted_Claims_Avg) / cStatics.Diff_FinalDrafted_Claims_Std;
                        double score = Math.Sqrt(score1 * score1 + score2 * score2);

                        if (score > 3.0)
                        {
                            if (score2 > 0)
                            {
                                if (score1 < 0)
                                {
                                    item.AbnormalMessage = $"内部核稿修改相对少，外部修改相对很多!【核稿人不给力/沟通有问题?】，【分数为：{score.ToString("0.00")}】";
                                }
                                else
                                {
                                    item.AbnormalMessage = $"内部核稿相对多，外部修改也相对多!【沟通有问题?】，【分数为：{score.ToString("0.00")}】";
                                }
                            }
                            else
                            {
                                if (score1 < 0)
                                {
                                    item.AbnormalMessage = $"内部核稿修改相对少，外部修改相对少!【撰稿人给力】，【分数为：{score.ToString("0.00")}】";
                                }
                                else
                                {
                                    item.AbnormalMessage = $"内部核稿相对多，外部修改也相对少!【核稿人给力】，【分数为：{score.ToString("0.00")}】";
                                }
                            }


                            retList.Add(item);
                        }
                    }
                }

                return retList;
            }
            else
            {
                return new List<CaseInfo>();
            }


        }

        internal class mailCaseInfo
        {
            public CustomerCompareStatistics CustomerCompareStatistics { get; set; }

            public List<CaseInfo> Top10Cases { get; set; } = new List<CaseInfo>();

            public List<CaseInfo> AbnormalCases { get; set; }= new List<CaseInfo>();
        }

        public void getMailCaseInfo(DateTime start, DateTime end)
        {
            var caseList = Context.CaseInfos.Where<CaseInfo>(
                    p => p.CreateTime >= end.AddYears(-1) &&
                    p.CreateTime <= end
                    )
                .Include(p => p.Customer)
                .Include(p => p.Reviewer)
                .Include(p => p.DRRAbstract)
                .Include(p => p.DRRAbstract)
                .Include(p => p.DRRCalim)
                .Include(p => p.DRRFulltext)
                .Include(p => p.DRRAll)
                .Include(p => p.RFRAbstract)
                .Include(p => p.RFRCalim)
                .Include(p => p.RFRFulltext)
                .Include(p => p.RFRAll)
                .ToList();

            List<string> defaultStaffNames = new List<string>() { "罗才洋","李庆波","钟子敏"};
            var DefaultEmailAccounts = Context.Staffs.Where(p => defaultStaffNames.Contains(p.Name)).ToList();
            
            var customerStatics = getLaseYearMean_Variance(end,caseList);
            Hashtable staffMailInfo = new Hashtable();
            foreach (var cStatics in customerStatics)
            {
                if(cStatics.CaseCount < 20)
                {
                    continue;
                }

                //var cStatics = customerStatics.Where(s => s.CustomerName == item.Customer.Name).FirstOrDefault();
                var Top10Cases =getTop10DiffRateCases(start, end, cStatics.CustomerName,0,caseList);
                foreach (var caseInfo in Top10Cases)
                {
                    foreach (Staff staff in DefaultEmailAccounts)
                    {
                        AddtoHashTable(caseInfo, 0, staff, staffMailInfo, cStatics);
                    }

                    if (caseInfo.Reviewer != null)
                    {
                        AddtoHashTable(caseInfo,0, caseInfo.Reviewer,staffMailInfo,cStatics);
                    }

                    if (caseInfo.Customer.ResponseManId != null)
                    {
                        Staff respMan = Context.Staffs.FirstOrDefault<Staff>(p => p.Id == caseInfo.Customer.ResponseManId);
                        if (respMan != null)
                        {
                            AddtoHashTable(caseInfo, 0, respMan, staffMailInfo, cStatics);
                        }
                    }
                }

                var AbnormalCases = _GetAbnormalCases(start,end, cStatics.CustomerName, cStatics,caseList);
                foreach (var caseInfo in AbnormalCases)
                {
                    foreach(Staff staff in DefaultEmailAccounts)
                    {
                        AddtoHashTable(caseInfo, 1, staff, staffMailInfo, cStatics);
                    }
                    if (caseInfo.Reviewer != null)
                    {
                        AddtoHashTable(caseInfo, 1, caseInfo.Reviewer, staffMailInfo, cStatics);
                    }

                    if (caseInfo.Customer.ResponseManId != null)
                    {
                        Staff respMan = Context.Staffs.FirstOrDefault<Staff>(p => p.Id == caseInfo.Customer.ResponseManId);
                        if (respMan != null)
                        {
                            AddtoHashTable(caseInfo, 1, respMan, staffMailInfo, cStatics);
                        }
                    }
                }

            }

            if (staffMailInfo.Count > 0) { 
                foreach (var key in staffMailInfo.Keys)
                {
                    Staff staff = key as Staff;
                    List<mailCaseInfo> mailCases = (List<mailCaseInfo>)staffMailInfo[key];

                    
                    string strMessage = string.Empty ;
                    foreach(var m in mailCases)
                    {
                        if ((m.Top10Cases != null && m.Top10Cases.Count > 0) || (m.AbnormalCases != null && m.AbnormalCases.Count > 0))
                        {
                            strMessage += $"<br/><div><b>{m.CustomerCompareStatistics.CustomerName}:</b>";
                            strMessage += $"<br/>一年内案件数量：{m.CustomerCompareStatistics.CaseCount}";
                            strMessage += $"<br/>内部核稿平均修改率：{m.CustomerCompareStatistics.Diff_Drafted_Claims_Avg}&nbsp;&nbsp;内部核稿修改率标准方差：{m.CustomerCompareStatistics.Diff_Drafted_Claims_Std}";
                            strMessage += $"<br/>外部部核稿平均修改率：{m.CustomerCompareStatistics.Diff_FinalDrafted_Claims_Avg}&nbsp;&nbsp;外部核稿修改率标准方差：{m.CustomerCompareStatistics.Diff_FinalDrafted_Claims_Std}</div>";
                            if (m.Top10Cases != null && m.Top10Cases.Count > 0)
                            {
                                strMessage += $"<div>外部核稿修改率前10名案件清单：</div>";
                                strMessage += "<table border='1'><thead><td>案号</td><td>案件名称</td><td>处理人</td><td>核稿人</td><td>内部核稿修改率(%)</td><td>外部核稿修改率(%)</td></thead><body>";
                                foreach (var c in m.Top10Cases)
                                {
                                    strMessage += $"<tr><td><a href=\"{($"http://1.116.113.26/CompareFile/detail/{c.CaseNo}")}\">{c.CaseNo}</a></td><td>{c.CaseName}</td><td>{c.Handlers}</td><td>{(c.Reviewer == null ? "" : c.Reviewer.Name)}</td><td>{(c.DRRCalim == null ? "" : (c.DRRCalim.diffRate*100).ToString("0.0000"))}</td><td>{(c.RFRCalim == null ? "" : (c.RFRCalim.diffRate * 100).ToString("0.0000"))}</td></tr>";
                                }
                                strMessage += "</body></table>";
                            }

                            if (m.AbnormalCases != null && m.AbnormalCases.Count > 0)
                            {
                                strMessage += $"<br/><div>疑似异常案件清单：</div>";
                                strMessage += "<table border='1'><thead><td>案号</td><td>案件名称</td><td>处理人</td><td>核稿人</td><td>内部核稿修改率</td><td>外部核稿修改率</td><td>异常说明</td></thead><body>";
                                foreach (var c in m.AbnormalCases)
                                {
                                    strMessage += $"<tr><td><a href=\"{($"http://1.116.113.26/CompareFile/detail/{c.CaseNo}")}\">{c.CaseNo}</a></td><td>{c.CaseName}</td><td>{c.Handlers}</td><td>{(c.Reviewer == null ? "" : c.Reviewer.Name)}</td><td>{(c.DRRCalim == null ? "" : (c.DRRCalim.diffRate*100).ToString("0.0000"))}</td><td>{(c.RFRCalim == null ? "" : (c.RFRCalim.diffRate*100).ToString("0.0000"))}</td><td>{c.AbnormalMessage}</td></tr>";
                                }
                                strMessage += "</body></table><br/>";
                            }

                            strMessage += "</div>";
                        }
                        
                    }

                    string strTem = $"如下是{start.ToString("yyyy-MM-dd")}到{end.ToString("yyyy-MM-dd")}国内专利申请案件在客户核稿处于年内修改率前10名的案件和疑似异常案件清单，请关注！";

                    string strBody = $"<div><div>{staff.Name},你好！</div><br/><div>{strTem}</div>{strMessage}<br/><div >小美集团绩效管理系统</div></div>";
                    staff.Mail = "luocaiyang@china-wispro.com";
                    _ = QuartzUtil.AddMailJob($"{start.ToString("yyyyMMdd")}-{end.ToString("yyyyMMdd")}申请文件比较邮件", strBody, staff.Name, staff.Mail);
                }
            }
        }

        private void AddtoHashTable(CaseInfo caseInfo, int v, Staff staff, Hashtable staffMailInfo,CustomerCompareStatistics cStatics)
        {
            var staffCaseMailInfo = new mailCaseInfo()
            {
                CustomerCompareStatistics = cStatics,
            };

            List<mailCaseInfo> staffCaseMailInfos = new List<mailCaseInfo>();
            if (staffMailInfo.ContainsKey(staff))
            {
                staffCaseMailInfos = (List<mailCaseInfo>)staffMailInfo[staff];
            }
            else
            {
                staffMailInfo.Add(staff,staffCaseMailInfos);
            }

            var temObj = staffCaseMailInfos.Where(p => p.CustomerCompareStatistics.CustomerName == caseInfo.Customer.Name).FirstOrDefault();
            if (temObj != null)
            {
                staffCaseMailInfo = temObj;
            }
            else
            {
                staffCaseMailInfos.Add(staffCaseMailInfo);
            }

            if (v == 0)
            {
                staffCaseMailInfo.Top10Cases.Add(caseInfo);
            }
            else
            {
                staffCaseMailInfo.AbnormalCases.Add(caseInfo);
            }
        }

        private class retObject
        {
            public string CaseNo { get; set; }
            public string CaseName { get; set; }

            public string Customer { get; set; }
            public string Handers { get; set; }


            public string Reviewer { get; set; }

            public double? InternalSim { get; set; }

            public double? InternalAvg { get; set; }

            public double? InternalStd { get; set; }

            public double? InternalScore { get; set; }

            public double? CustomerSim { get; set; }

            public double? CustomerAvg { get; set; }

            public double? CustomerStd { get; set; }

            public double? CustomerScore { get; set; }

            public string Memo { get; set; }

            public DateTime? FinishedDate { get; set; }

        }

        private DataTable  CoverttoDatable(List<retObject> ObjList)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("我方文号");
            dt.Columns.Add("案件名称");
            dt.Columns.Add("客户");
            dt.Columns.Add("处理人");
            dt.Columns.Add("核稿人");
            dt.Columns.Add("内部核稿权要差异度");
            dt.Columns.Add("内部客户平均值");
            dt.Columns.Add("内部客户标准偏差");
            dt.Columns.Add("内部核稿分数");
            dt.Columns.Add("外部核稿权要差异度");
            dt.Columns.Add("外部客户平均值");
            dt.Columns.Add("外部客户标准偏差");
            dt.Columns.Add("外部核稿分数");
            dt.Columns.Add("备注");

            foreach (var temObj in ObjList)
            {
                DataRow row = dt.NewRow();

                row["我方文号"] = temObj.CaseNo;
                row["案件名称"] = temObj.CaseName;
                row["客户"] = temObj.Customer;
                row["核稿人"] = temObj.Reviewer;
                row["处理人"] = temObj.Handers;

                row["内部核稿分数"] = temObj.InternalScore;

                row["内部核稿权要差异度"] = temObj.InternalSim;
                row["内部客户平均值"] = temObj.InternalAvg;
                row["内部客户标准偏差"] = temObj.InternalStd;
                row["内部核稿分数"] = temObj.InternalScore;

                row["外部核稿权要差异度"] = temObj.CustomerSim;
                row["外部客户平均值"] = temObj.CustomerAvg;
                row["外部客户标准偏差"] = temObj.CustomerStd;
                row["外部核稿分数"] = temObj.CustomerScore;
                row["备注"] = temObj.Memo;

                dt.Rows.Add(row);
            }

            return dt;
        }
        /// <summary>
        /// 计算案件的zScore
        /// </summary>
        /// <param name="start">定稿日开始时间</param>
        /// <param name="end">定稿日结束时间</param>
        /// <param name="type">
        /// 0：基于文本相似度计算，
        /// 1：基于文本修改差异度计算
        /// 2：基于权要权重70说明书30计算
        /// 3：基于权要文本相似度计算
        /// 4: 基于权要文字修改差异度计算
        /// </param>
        /// <returns></returns>
        private IList<retObject> CalCustomer_mean(DateTime start,DateTime end,int type=0)
        {

            var caseList = Context.CaseInfos.Where<CaseInfo>(
                p => p.CreateTime >= start && p.CreateTime < end)
                .Include(p=>p.Customer)
                .Include(p=>p.Reviewer)
                .Include(p=>p.DRRAbstract)
                .Include(p => p.DRRAbstract)
                .Include(p => p.DRRCalim)
                .Include(p => p.DRRFulltext)
                .Include(p => p.DRRAll)
                .Include(p => p.RFRAbstract)
                .Include(p => p.RFRCalim)
                .Include(p => p.RFRFulltext)
                .Include(p => p.RFRAll);

            int iTotals = caseList.Count();
            int iIndex = 0;

            List<retObject> retList = new List<retObject>();

            #region 计算客户相似度平均值和标准方差
            List<CustomerAvg_Std> avg_std1 = new List<CustomerAvg_Std>();
            List<CustomerAvg_Std> avg_std2 = new List<CustomerAvg_Std>();
            foreach (var caseInfo in caseList)
            {
                iIndex++;
                Debug.WriteLine($"{iIndex}/{iTotals}\t{caseInfo.CaseNo}\t{caseInfo.DRRAbstractId},{caseInfo.DRRCalimId},{caseInfo.DRRFulltextId},{caseInfo.DRRAllId},{caseInfo.RFRAbstractId},{caseInfo.RFRCalimId},{caseInfo.RFRFulltextId},{caseInfo.RFRAllId}");
                string tmpCustomerName = "未知";
                if(caseInfo.Customer!= null)
                {
                    tmpCustomerName = caseInfo.Customer.Name;
                }
                
                var one = avg_std1.Where<CustomerAvg_Std>(p => p.Name == tmpCustomerName).FirstOrDefault();
                if (one == null)
                {
                    one = new CustomerAvg_Std() { Name = tmpCustomerName };
                    avg_std1.Add(one);
                }
                var two = avg_std2.Where<CustomerAvg_Std>(p => p.Name == tmpCustomerName).FirstOrDefault();
                if (two == null)
                {
                    two = new CustomerAvg_Std() { Name = tmpCustomerName };
                    avg_std2.Add(two);
                }

                double? oneTmp = getCalValue(caseInfo, type, 0);
                if(oneTmp != null)
                {
                    one.Sum += oneTmp.Value;
                    one.SquareSum += oneTmp.Value * oneTmp.Value;
                    one.count += 1;
                }
                double? twoTmp = getCalValue(caseInfo, type, 1);
                if (twoTmp != null)
                {
                    two.Sum += twoTmp.Value;
                    two.SquareSum += twoTmp.Value * twoTmp.Value;
                    two.count += 1;
                }
            }
            #endregion

            foreach (var item in caseList)
            {
                retObject temObj = new retObject();
                string tmpCustomerName = "未知";
                if (item.Customer != null)
                {
                    tmpCustomerName = item.Customer.Name;
                }
                var one = avg_std1.Where<CustomerAvg_Std>(p => p.Name == tmpCustomerName).FirstOrDefault();
                var two = avg_std2.Where<CustomerAvg_Std>(p => p.Name == tmpCustomerName).FirstOrDefault();

                double? oneSim = getCalValue(item,type,0);
                double? twoSim = getCalValue(item, type, 1);
                temObj.FinishedDate = item.CreateTime;

                temObj.CaseNo = item.CaseNo;
                temObj.CaseName = item.CaseName;
                temObj.Customer = item.Customer?.Name;
                temObj.Reviewer = item.Reviewer?.Name;
                temObj.Handers = item.Handlers;

                double? zScoreA = null;
                if (oneSim != null)
                {
                    zScoreA = (oneSim - one.Average) / one.Std_Deviation;
                    
                }

                temObj.InternalScore = zScoreA;
                temObj.InternalSim = oneSim;
                temObj.InternalAvg = one.Average;
                temObj.InternalStd = one.Std_Deviation;
                
                double? zScoreB = null;
                if (twoSim != null)
                {
                    zScoreB = (twoSim - two.Average) / two.Std_Deviation;
                    
                }
                temObj.CustomerSim = twoSim;
                temObj.CustomerAvg = two.Average;
                temObj.CustomerStd = two.Std_Deviation;
                temObj.CustomerScore = zScoreB;

                string strMemo = "";

                zScoreA = (zScoreA == null) ? 0 : zScoreA;
                zScoreB = (zScoreB == null) ? 0 : zScoreB;
                if (zScoreA != null && zScoreB != null)
                {
                    var distince = Math.Sqrt(zScoreB.Value * zScoreB.Value + zScoreA.Value * zScoreA.Value);

                    if (type == 0 || type == 2 || type==3)
                    {
                        if (distince > 3)
                        {
                            if (zScoreA.Value > 0 && zScoreB.Value > 0)
                            {
                                strMemo = "内部核稿、外部核稿修改都较少！[撰稿人给力或客户友好]";
                            }

                            if (zScoreA.Value > 0 && zScoreB.Value < 0)
                            {
                                strMemo = "内部核稿较少、外部核稿修改较多！[核稿人没有尽责！]";
                            }

                            if (zScoreA.Value < 0 && zScoreB.Value > 0)
                            {
                                strMemo = "内部核稿较多、外部核稿修改都较少！[核稿人给力]";
                            }

                            if (zScoreA.Value < 0 && zScoreB.Value < 0)
                            {
                                strMemo = "内部核稿和外部核稿修改都较多！[案件沟通问题？]";
                            }
                        }
                    }
                    else
                    {
                        if (distince > 3)
                        {
                            if (zScoreA.Value > 0 && zScoreB.Value > 0)
                            {
                                strMemo = "内部核稿和外部核稿修改都较多！[案件沟通问题？]";

                            }

                            if (zScoreA.Value > 0 && zScoreB.Value < 0)
                            {
                                strMemo = "内部核稿较多、外部核稿修改都较少！[核稿人给力]";
                            }

                            if (zScoreA.Value < 0 && zScoreB.Value > 0)
                            {
                                strMemo = "内部核稿较少、外部核稿修改较多！[核稿人没有尽责！]";
                            }

                            if (zScoreA.Value < 0 && zScoreB.Value < 0)
                            {
                                strMemo = "内部核稿、外部核稿修改都较少！[撰稿人给力或客户友好]";
                            }
                        }
                    }
                }
                temObj.Memo = strMemo;

                retList.Add(temObj);
            }

            
            //wispro.sp.utility.NPOIExcel.DataTableToExcel(dt,$"c:\\temp\\{DateTime.Now.ToString("yyyyMMdd")}-内部核稿外部核稿情况案件清单_{typeName(type)}.xlsx");
            return retList;
        }

        public void MonthlyReport(int year,int month)
        {
            #region 获取一年的案件清单,并计算个案件的清单
            DateTime endDate = new DateTime(year, month + 1, 1);
            DateTime yStartDate = endDate.AddYears(-1);
            var retList = CalCustomer_mean(yStartDate, endDate, 4);
            #endregion

            DateTime mStartDate = new DateTime(year, month, 1);

            #region 获取前客户修改前10名的案件清单
            var monthItemList = retList.Where<retObject>(
                r => r.FinishedDate >= mStartDate && r.FinishedDate < endDate);

            var top10List = monthItemList.OrderByDescending(r => r.CustomerSim)
                .Take(10).ToList();

            
            Hashtable reviewerHash = new Hashtable(); //核稿人
            Hashtable crHash = new Hashtable(); //客户对接人
            Hashtable dphash = new Hashtable(); //部长
            foreach (var item in top10List)
            {
                #region 核稿人
                if (!string.IsNullOrEmpty(item.Reviewer)) {
                    Staff reviewer = Context.Staffs.Where(s => s.Name == item.Reviewer).FirstOrDefault();
                    List<retObject> rets = new List<retObject>();
                    foreach (Staff k in reviewerHash.Keys)
                    {
                        if (k.Id == reviewer.Id)
                        {
                            rets = (List<retObject>)reviewerHash[k];
                            break;
                        }
                    }
                    if (rets.Count == 0)
                    {
                        reviewerHash.Add(reviewer, rets);
                    }

                    rets.Add(item);
                }
                #endregion

                #region 客户对接人
                
                if (!string.IsNullOrEmpty(item.Customer)) {
                    Customer c = Context.Customers.Where(s => s.Name == item.Customer)
                        .Include(s=>s.ResponseMan).FirstOrDefault();
                    if (c != null && c.ResponseMan != null)
                    {
                        List<retObject> rets = new List<retObject>();
                        foreach (Staff k in crHash.Keys)
                        {
                            if (k.Id == c.ResponseManId)
                            {
                                rets = (List<retObject>)crHash[k];
                                break;
                            }
                        }
                        if (rets.Count == 0)
                        {
                            crHash.Add(c.ResponseMan, rets);
                        }
                        rets.Add(item);
                    }
                }
                #endregion

                #region 部长
                
                string[] Handlers = item.Handers.Split(",");
                foreach(string n in Handlers)
                {
                    var dpList = Context.DepartmentPositions.Where(d => d.Staff.Name == n).Include(d => d.department).ToList();
                    foreach(var dp in dpList)
                    {
                        var dpObj = Context.DepartmentPositions.Where(d => d.departmentId == dp.departmentId && d.Position.Name == "部长").Include(d => d.Staff).FirstOrDefault();
                        if(dpObj!= null)
                        {
                            List<retObject> rets = new List<retObject>();
                            foreach(Staff k in dphash.Keys)
                            {
                                if(k.Id == dpObj.StaffId)
                                {
                                    rets = (List<retObject>)dphash[k];
                                    break;
                                }
                            }
                            if (rets.Count ==0)
                            { 
                                dphash.Add(dpObj.Staff, rets);
                            }

                            if(!rets.Contains(item))
                                rets.Add(item);
                        }

                    }
                    
                }
                #endregion
            }

            foreach(Staff sf in reviewerHash.Keys)
            {
                List<retObject> temList = (List<retObject>)reviewerHash[sf];
                DataTable temdt  = CoverttoDatable(temList);
                string strPath = $"{year}年{month}月客户修改权要排名前10的案件清单(审核人).xlsx";
                utility.NPOIExcel.DataTableToExcel(temdt, strPath, true, true, 0, "http://1.116.113.26/CompareFile/detail/{0}");
                string strBody = $"{sf.Name},你好！<br>附件中的案件是{year}年{month}月您核稿的案件，客户权要修改率在前10的案件，请注意核稿质量！";
                _ = QuartzUtil.AddMailJob($"{year}年{month}月申请文件比较邮件", strBody, sf.Name, sf.Mail, $"{strPath}");
            }

            foreach (Staff sf in crHash.Keys)
            {
                List<retObject> temList = (List<retObject>)crHash[sf];
                DataTable temdt = CoverttoDatable(temList);
                string strPath = $"{year}年{month}月客户修改权要排名前10的案件清单(客户对接人).xlsx";
                utility.NPOIExcel.DataTableToExcel(temdt, strPath, true, true, 0, "http://1.116.113.26/CompareFile/detail/{0}");
                string strBody = $"{sf.Name},你好！<br>附件中的案件是{year}年{month}月你对接的客户的案件，客户权要修改率在前10的案件，请注意！";
                _ = QuartzUtil.AddMailJob($"{year}年{month}月申请文件比较邮件", strBody, sf.Name, sf.Mail, $"{strPath}");
            }

            foreach (Staff sf in dphash.Keys)
            {
                List<retObject> temList = (List<retObject>)dphash[sf];
                DataTable temdt = CoverttoDatable(temList);
                string strPath = $"{year}年{month}月客户修改权要排名前10的案件清单(部长).xlsx";
                utility.NPOIExcel.DataTableToExcel(temdt, strPath, true, true, 0, "http://1.116.113.26/CompareFile/detail/{0}");
                string strBody = $"{sf.Name},你好！<br>附件中的案件是{year}年{month}月贵部人员处理的，客户权要修改率在前10的案件，请注意！";
                _ = QuartzUtil.AddMailJob($"{year}年{month}月申请文件比较邮件", strBody, sf.Name, sf.Mail, $"{strPath}");
            }

            DataTable dt = CoverttoDatable(top10List);
            string top10_filePath = $"{year}年{month}月客户修改权要排名前10的案件清单.xlsx";
            utility.NPOIExcel.DataTableToExcel(dt, top10_filePath, true, true, 0, "http://1.116.113.26/CompareFile/detail/{0}");
            #endregion

            #region 获取疑似问题清单
            var excItemList = monthItemList.Where<retObject>(r => string.IsNullOrEmpty(r.Memo) == false)
                .OrderBy(r => r.Customer).ThenByDescending(r => r.CustomerSim)
                .ToList();


            DataTable excdt = CoverttoDatable(excItemList);

            string except_filePath = $"{year}年{month}月疑似有问题的案件清单.xlsx";
            utility.NPOIExcel.DataTableToExcel(excdt, except_filePath, true, true, 0, "http://1.116.113.26/CompareFile/detail/{0}");
            #endregion

            #region 所有案件清单
            var allItemList = monthItemList
                .OrderBy(r => r.Customer).ThenByDescending(r=>r.CustomerSim)
                .ToList();
            DataTable alldt = CoverttoDatable(allItemList);

            string all_filePath = $"{year}年{month}月案件清单.xlsx";
            utility.NPOIExcel.DataTableToExcel(alldt, all_filePath, true, true, 0, "http://1.116.113.26/CompareFile/detail/{0}");

            #endregion

            #region 发邮件
            _ = QuartzUtil.AddMailJob($"{year}年{month}月申请文件比较邮件", "FYI!", "罗才洋", "luocaiyang@china-wispro.com", $"{all_filePath};{except_filePath};{top10_filePath}");
            _ = QuartzUtil.AddMailJob($"{year}年{month}月申请文件比较邮件", "FYI!", "李庆波", "liqingbo@china-wispro.com", $"{all_filePath};{except_filePath};{top10_filePath}");
            _ = QuartzUtil.AddMailJob($"{year}年{month}月申请文件比较邮件", "FYI!", "钟子敏", "zhongzimin@china-wispro.com", $"{all_filePath};{except_filePath};{top10_filePath}");
            _ = QuartzUtil.AddMailJob($"{year}年{month}月申请文件比较邮件", "FYI!", "王姝然", "zhongzimin@china-wispro.com", $"{all_filePath};{except_filePath};{top10_filePath}");
            #endregion
        }

        private double? getCalValue(CaseInfo caseInfo,int type,int stage)
        {
            double? calValue = null;
            switch (type)
            {
                case 0:
                    if(stage == 0)
                    {
                        if(caseInfo.DRRFulltext != null && caseInfo.DRRAbstract != null)
                        {
                            calValue = caseInfo.DRRAll.TextSimilarity;
                        }
                        else
                        {
                            if(caseInfo.DRRCalim != null)
                            {
                                calValue = caseInfo.DRRCalim.TextSimilarity;
                            }
                        }
                    }
                    else
                    {
                        if (caseInfo.RFRFulltext != null && caseInfo.RFRAbstract != null)
                        {
                            calValue = caseInfo.RFRAll.TextSimilarity;
                        }
                        else
                        {
                            if (caseInfo.RFRCalim != null)
                            {
                                calValue = caseInfo.RFRCalim.TextSimilarity;
                            }
                        }
                    }
                    break;
                case 1:
                    if (stage == 0)
                    {
                        if (caseInfo.DRRFulltext != null && caseInfo.DRRAbstract != null)
                        {
                            calValue = caseInfo.DRRAll.diffRate;
                        }
                        else
                        {
                            if (caseInfo.DRRCalim != null)
                            {
                                calValue = caseInfo.DRRCalim.diffRate;
                            }
                        }
                    }
                    else
                    {
                        if (caseInfo.RFRFulltext != null && caseInfo.RFRAbstract != null)
                        {
                            calValue = caseInfo.RFRAll.diffRate;
                        }
                        else
                        {
                            if (caseInfo.RFRCalim != null)
                            {
                                calValue = caseInfo.RFRCalim.diffRate;
                            }
                        }
                    }
                    break;
                case 2:
                    if (stage == 0)
                    {
                        if(caseInfo.DRRFulltext != null && caseInfo.DRRCalim!= null)
                        {
                            calValue = caseInfo.DRRCalim.TextSimilarity * 0.7 + caseInfo.DRRFulltext.TextSimilarity * 0.3;
                        }
                        else
                        {
                            if( caseInfo.DRRCalim != null)
                            {
                                calValue = caseInfo.DRRCalim.TextSimilarity;
                            }
                            else
                            {
                                if(caseInfo.DRRAll != null)
                                {
                                    calValue = caseInfo.DRRAll.TextSimilarity;
                                }
                            }
                        }
                    }
                    else
                    {
                        if (caseInfo.RFRFulltext != null && caseInfo.RFRCalim != null)
                        {
                            calValue = caseInfo.RFRCalim.TextSimilarity * 0.7 + caseInfo.RFRFulltext.TextSimilarity * 0.3;
                        }
                        else
                        {
                            if (caseInfo.RFRCalim != null)
                            {
                                calValue = caseInfo.RFRCalim.TextSimilarity;
                            }
                            else
                            {
                                if (caseInfo.RFRAll != null)
                                {
                                    calValue = caseInfo.RFRAll.TextSimilarity;
                                }
                            }
                        }
                    }
                    break;
                case 3:
                    if (stage == 0)
                    {
                        
                        if (caseInfo.DRRCalim != null)
                        {
                            calValue = caseInfo.DRRCalim.TextSimilarity;
                        }
                    }
                    else
                    {
                        if (caseInfo.RFRCalim != null)
                        {
                            calValue = caseInfo.RFRCalim.TextSimilarity;
                        }
                    }
                    break;
                case 4:
                    if (stage == 0)
                    {

                        if (caseInfo.DRRCalim != null)
                        {
                            calValue = caseInfo.DRRCalim.diffRate;
                        }
                    }
                    else
                    {
                        if (caseInfo.RFRCalim != null)
                        {
                            calValue = caseInfo.RFRCalim.diffRate;
                        }
                    }
                    break;
            }

            return calValue;
        }

        private string typeName(int type)
        {
            switch (type)
            {
                case 0:
                    return "文本相似度计算";
                case 1:
                    return "字符修改计算";
                case 2:
                    return "权要权重70说明书30";
                case 3:
                    return "权要相似度计算";

            }

            return "";
        }


        public IList<Object> CalMean_Std(DateTime start,DateTime end)
        {
            double AverageA = Context.CaseInfos.Where<CaseInfo>(
                p => p.FinalVersionDate >= start && p.FinalVersionDate <= end && p.DRRCalim!= null)
                .Average(x=>x.DRRCalim.TextSimilarity);
               
            double AverageB = Context.CaseInfos.Where<CaseInfo>(
                p => p.FinalVersionDate >= start && p.FinalVersionDate <= end && p.RFRAll != null)
                .Average(x => x.RFRAll.TextSimilarity);

            double stdA = Math.Sqrt( Context.CaseInfos.Where<CaseInfo>(
                p => p.FinalVersionDate >= start && p.FinalVersionDate <= end)
                .Average((x => (x.DRRCalim.TextSimilarity - AverageA) * (x.DRRCalim.TextSimilarity - AverageA))));
                

            double stdB = Math.Sqrt(Context.CaseInfos.Where<CaseInfo>(
                p => p.FinalVersionDate >= start && p.FinalVersionDate <= end)
                .Average((x => (x.RFRAll.TextSimilarity - AverageB) * (x.RFRAll.TextSimilarity - AverageB))));

            var response2 = Context.CaseInfos.Where<CaseInfo>(
                p => p.FinalVersionDate >= start && p.FinalVersionDate <= end)
                .Include(p=>p.DRRCalim)
                .Include(p=>p.RFRAll);
            
            IList<Object> results = new List<Object>();
            foreach(var item in response2.ToList())
            {
                results.Add(
                    new { 
                        Id = item.Id,
                        CaseNo = item.CaseNo,
                        CaseName = item.CaseName,
                        Customer = item.Customer,
                        Reviewer = item.Reviewer,
                        Handlers = item.Handlers,
                        zScoreA = (item.DRRCalim?.TextSimilarity -AverageA)/stdA,
                        zScoreB = (item.RFRAll?.TextSimilarity - AverageB) /stdB
                    }    
                );
            }

            return results;

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


        private IQueryable<CaseInfo> NewMethod(QueryFilter queryFilter)
        {
            string strExpress = "";
            
            if (queryFilter.ConditionTree != null)
            {
                strExpress = GetExpress(queryFilter.ConditionTree);
            }

            var interpreter = new Interpreter();
            if (string.IsNullOrEmpty(strExpress))
            {
                return new spDbContext().CaseInfos.Where<CaseInfo>(p=>(p.Id>0));
            }
            else
            {
                Expression<Func<CaseInfo, bool>> dynamicWhere = interpreter.ParseAsExpression<Func<CaseInfo, bool>>(strExpress, "s");

                IQueryable<CaseInfo> response = new spDbContext().CaseInfos.Where<CaseInfo>(dynamicWhere);
                return response;
            }

        }


    }
}