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.SqlExpressions;
using System;
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;
        }

        /// <summary>
        /// 计算案件的zScore
        /// </summary>
        /// <param name="start">定稿日开始时间</param>
        /// <param name="end">定稿日结束时间</param>
        /// <param name="type">
        /// 0：基于文本相似度计算，
        /// 1：基于文本修改差异度计算
        /// 2：权要权重70说明书30
        /// </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); ;

                if(oneSim == null)
                {
                    oneSim = 1;
                }

                if (twoSim == null)
                {
                    twoSim = 1; 
                }

                DataRow row = dt.NewRow();
                row["我方文号"] = item.CaseNo;
                row["案件名称"] = item.CaseName;
                row["客户"] = item.Customer?.Name;
                row["核稿人"] = item.Reviewer?.Name;
                row["处理人"] = item.Handlers;
                var zScoreA = (oneSim -one.Average)/one.Std_Deviation;
                row["内部核稿相似度或差异度"] = oneSim;
                row["内部客户平均值"] = one.Average;
                row["内部客户标准偏差"] = one.Std_Deviation;
                row["内部核稿分数"] = zScoreA;

                var zScoreB = (twoSim -two.Average)/two.Std_Deviation;
                row["外部核稿相似度或差异度"] = twoSim;
                row["外部客户平均值"] = two.Average;
                row["外部客户标准偏差"] = two.Std_Deviation;
                row["外部核稿分数"] = zScoreB;

                var distince = Math.Sqrt(zScoreB.Value * zScoreB.Value + zScoreA.Value * zScoreA.Value);

                if (type == 0 || type==2)
                {
                    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;
            }

            return calValue;
        }

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

            }

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

        }


    }
}