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

using DocumentFormat.OpenXml.InkML;
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 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.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,string customerName=null)
        {

            IIncludableQueryable<CaseInfo, CompareResult> caseList;

            if (customerName != 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);
            }
            else
            {
                caseList = Context.CaseInfos.Where<CaseInfo>(
                p => p.CreateTime >= endDate.AddYears(-1) && p.CreateTime <= endDate)
                .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);
            }

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

            foreach ( var item in caseList)
            {
                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)
        {
            if (type == 0) {

                var top10Case = Context.CaseInfos.Where<CaseInfo>(
                    p => p.CreateTime >= start &&
                    p.CreateTime <= end &&
                    p.Customer.Name == customerName
                    )
                .Include(p => p.Customer)
                .Include(p => p.Reviewer)
                .Include(p => p.DRRCalim)
                .Include(p => p.RFRCalim)
                .AsEnumerable()
                .OrderByDescending(p => p.RFRCalim?.diffRate);

                return top10Case.Take(10).ToList();
            }
            else
            {
                var top10Case = Context.CaseInfos.Where<CaseInfo>(
                    p => p.CreateTime >= start &&
                    p.CreateTime <= end &&
                    p.Customer.Name == customerName
                    )
                .Include(p => p.Customer)
                .Include(p => p.Reviewer)
                .Include(p => p.DRRCalim)
                .Include(p => p.RFRCalim)
                .AsEnumerable()
                .OrderByDescending(p => p.DRRCalim?.diffRate);

                return top10Case.Take(10).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)
        {
            var customerStatics = getLaseYearMean_Variance(end, customerName);
            var cStatics = customerStatics.Where(s => s.CustomerName == customerName).FirstOrDefault();
            return _GetAbnormalCases(start, end, customerName, cStatics);

        }

        private  List<CaseInfo> _GetAbnormalCases(DateTime start, DateTime end, string customerName, CustomerCompareStatistics cStatics)
        {
            if (cStatics != null)
            {
                var caseList = Context.CaseInfos.Where<CaseInfo>(
                        p => p.CreateTime >= start &&
                        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);

                var retList = new List<CaseInfo>();

                foreach (var item in caseList)
                {
                    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 = "内部核稿修改相对少，外部修改相对很多!【核稿人不给力/沟通有问题?】";
                            }
                            else
                            {
                                item.AbnormalMessage = "内部核稿相对多，外部修改也相对多!【沟通有问题?】";
                            }
                        }
                        else
                        {
                            //if (score1 < 0)
                            //{
                            //    item.AbnormalMessage = "内部核稿修改相对少，外部修改相对少!【撰稿人给力】";
                            //}
                            //else
                            //{
                            //    item.AbnormalMessage = "内部核稿相对多，外部修改也相对少!【核稿人给力】";
                            //}
                        }


                        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)
        {
            List<string> defaultStaffNames = new List<string>() { "罗才洋","李庆波","钟子敏"};
            var DefaultEmailAccounts = Context.Staffs.Where(p => defaultStaffNames.Contains(p.Name)).ToList();
            
            var customerStatics = getLaseYearMean_Variance(end);
            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);
                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);
                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);
                        }
                    }
                }

            }
        }

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

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

            var caseList = Context.CaseInfos.Where<CaseInfo>(
                p => p.FinalVersionDate >= start && p.FinalVersionDate <= 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<Object> retList = new List<Object>();

            #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

            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 item in caseList)
            {
                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); ;

                DataRow row = dt.NewRow();
                row["我方文号"] = item.CaseNo;
                row["案件名称"] = item.CaseName;
                row["客户"] = item.Customer?.Name;
                row["核稿人"] = item.Reviewer?.Name;
                row["处理人"] = item.Handlers;
                double? zScoreA = null;
                if (oneSim != null)
                {
                    zScoreA = (oneSim - one.Average) / one.Std_Deviation;
                    
                }
                row["内部核稿分数"] = zScoreA;

                row["内部核稿相似度或差异度"] = oneSim;
                row["内部客户平均值"] = one.Average;
                row["内部客户标准偏差"] = one.Std_Deviation;
                
                row["外部核稿相似度或差异度"] = twoSim;
                row["外部客户平均值"] = two.Average;
                row["外部客户标准偏差"] = two.Std_Deviation;

                double? zScoreB = null;
                if (twoSim != null)
                {
                    zScoreB = (twoSim - two.Average) / two.Std_Deviation;
                    
                }
                row["外部核稿分数"] = 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)
                            {
                                row["备注"] = "内部核稿、外部核稿修改都较少！[撰稿人给力或客户友好]";
                            }

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

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

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

                            }

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

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

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

                retList.Add(
                
                    new
                    {
                        Id = item.Id,
                        CaseNo = item.CaseNo,
                        CaseName = item.CaseName,
                        Customer = item.Customer,
                        Reviewer = item.Reviewer,
                        Handlers = item.Handlers,
                        zScoreA = (oneSim - one.Average) / one.Std_Deviation,
                        zScoreB = (twoSim - two.Average) / two.Std_Deviation
                    }
                 );
            }

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

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

        }


    }
}