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

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

        }


    }
}