DEMOMySql/MicroServices/Business/Business.Application/VSM/VSMAppService.cs

using Azure;
using Business.Core.Enum;
using Business.Core.Utilities;
using Business.Domain;
using Business.Dto;
using Business.EntityFrameworkCore;
using Business.EntityFrameworkCore.SqlRepositories;
using Business.ResourceExamineManagement;
using Business.ResourceExamineManagement.Dto;
using Business.StructuredDB.MES;
using Business.StructuredDB.MES.IC;
using Business.StructuredDB.SaleFcst;
using Business.StructuredDB.WMS;
using Business.VSM;
using EFCore.BulkExtensions;
using MathNet.Numerics.LinearAlgebra.Factorization;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Extensions.Configuration;
using MongoDB.Driver.Linq;
using NetTopologySuite.Mathematics;
using Newtonsoft.Json;
using NLog;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.Metrics;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Xml.Linq;
using Volo.Abp.Application.Services;
using Volo.Abp.Domain.Repositories;
using Volo.Abp.MultiTenancy;
using WkHtmlToPdfDotNet;
using ZstdSharp.Unsafe;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace Business.VSMManagement
{
    public class VSMAppService : ApplicationService,IVSMAppService
    {
        private readonly ISqlRepository<ItemMaster> _itemMaster;
        private readonly ISqlRepository<InvTransHist> _invTransHist;
        private readonly IRepository<srm_purchase> _srm_purchase;
        private readonly IRepository<ic_item> _ic_item;
        private readonly ISqlRepository<LocationDetail> _LocationDetail;
        private readonly ISqlRepository<InvTransHistDay> _InvTransHistDay;
        private readonly IExtRASqlRepository<DMS_IN_PODETAIL> _DMS_IN_PODETAIL;
        private readonly IExtRASqlRepository<DMS_IN_LOCDETAIL> _DMS_IN_LOCDETAIL;
        private readonly IExtRASqlRepository<DMS_IN_ITEMMAPPING> _DMS_IN_ITEMMAPPING;
        private readonly IExtRASqlRepository<DMS_IN_SHIPPINGDETAIL> _DMS_IN_SHIPPINGDETAIL;
        private readonly ISqlRepository<SAPInv> _SAPInv;
        private readonly IRepository<MonthlyShipmentPlan> _monthlyShipmentPlan;
        private readonly ISqlRepository<ASNBOLShipperDetail> _aSNBOLShipperDetail;
        private readonly BusinessDbContext _businessDbContext;
        private readonly BusinessExtRADbContext _businessExtRADbContext;
        public VSMAppService(
         ISqlRepository<ItemMaster> itemMaster,
         ISqlRepository<InvTransHist> invTransHist,
         IRepository<srm_purchase> srm_purchase,
         IRepository<ic_item> ic_item,
         ISqlRepository<LocationDetail> locationDetail,
         ISqlRepository<InvTransHistDay> invTransHistDay,
         IExtRASqlRepository<DMS_IN_PODETAIL> dms_IN_PODETAIL,
         IExtRASqlRepository<DMS_IN_LOCDETAIL> dms_IN_LOCDETAIL,
         IExtRASqlRepository<DMS_IN_ITEMMAPPING> dms_IN_ITEMMAPPING,
         IExtRASqlRepository<DMS_IN_SHIPPINGDETAIL> dms_IN_SHIPPINGDETAIL,
         ISqlRepository<SAPInv> SAPInv,
         IRepository<MonthlyShipmentPlan> monthlyShipmentPlan,
         ISqlRepository<ASNBOLShipperDetail> aSNBOLShipperDetail,
        BusinessDbContext businessDbContext,
        BusinessExtRADbContext businessExtRADbContext
         )
        {
            _itemMaster = itemMaster;
            _invTransHist = invTransHist;
            _srm_purchase = srm_purchase;
            _ic_item = ic_item;
            _LocationDetail=locationDetail;
            _InvTransHistDay = invTransHistDay;
            _DMS_IN_PODETAIL = dms_IN_PODETAIL;
            _DMS_IN_LOCDETAIL= dms_IN_LOCDETAIL;
            _DMS_IN_ITEMMAPPING= dms_IN_ITEMMAPPING;
            _DMS_IN_SHIPPINGDETAIL = dms_IN_SHIPPINGDETAIL;
            _SAPInv =SAPInv;
            _monthlyShipmentPlan=monthlyShipmentPlan;
            _aSNBOLShipperDetail = aSNBOLShipperDetail;
            _businessDbContext = businessDbContext;
            _businessExtRADbContext = businessExtRADbContext;
        }
        public string ValueFrequency(string Loc, string BeginDate, string EndDate, string KanBan, string Frequency)
        {
            try
            {
                DateTime beginDate = Convert.ToDateTime(BeginDate);
                DateTime endDate = Convert.ToDateTime(EndDate);
                string sql = $"select distinct ItemNum,Min(CreateTime) AS HistMinTime from InvTransHist where Domain='8010' and createTime>='{beginDate}' and createTime<='{endDate}' group by ItemNum";
                var InvTransHistDto = _businessDbContext.InvTransHistDayDto.FromSqlRaw(sql).ToList();
                var itemList = InvTransHistDto.Select(a => a.ItemNum).Distinct().ToList();
                ValueFrequencyDto dto = new ValueFrequencyDto();
                List<ValueFrequencyKanBanDto> kanBanDtos = new List<ValueFrequencyKanBanDto>();
                var icitems = _ic_item.GetListAsync(a => itemList.Contains(a.number)).Result;
                var srm_purchases = _srm_purchase.GetListAsync(a => icitems.Select(b=>b.Id).ToList().Contains(a.icitem_id)).Result;
                
                string sqldzd = @"select MATNR AS ItemNum,MAx(NETPR)*10 AS NETPR from SupplierStatement group by  MATNR";
                var dzdList = _businessDbContext.SupplierStatementDto.FromSqlRaw(sqldzd).ToList();

                var InvTransHistDayDto = _InvTransHistDay.Select(a => a.HistDayTime >= beginDate.Date && a.HistDayTime <= endDate);
                itemList.ForEach(item =>
                {
                    if(icitems.Any(a=>a.number==item))
                    {
                        ValueFrequencyKanBanDto itemKanBanDto = new ValueFrequencyKanBanDto();
                        itemKanBanDto.ItemNumber = item;
                        itemKanBanDto.ItemId = icitems.Find(a => a.number == item).Id;
                        itemKanBanDto.Model = icitems.Find(a => a.number == item).model;
                        itemKanBanDto.ItemName = icitems.Find(a => a.number == item).name;
                        itemKanBanDto.PLT = 14;
                        //供应提前期取货源清单最大值，没有默认14天
                        if (srm_purchases.Any(a => a.icitem_id == itemKanBanDto.ItemId && a.quota_rate.GetValueOrDefault() > 0))
                        {
                            itemKanBanDto.PLT = srm_purchases.Where(a => a.number == item && a.quota_rate.GetValueOrDefault() > 0).Max(a => a.lead_time).Value;
                        }
                        var entry = dzdList.FirstOrDefault(p => p.ItemNum == item);
                        if (entry != null)
                        {
                            itemKanBanDto.NETPR = entry.NETPR;
                        }
                        else
                        {
                            itemKanBanDto.NETPR = 1 * 10;
                        }
                        //出库总数
                        itemKanBanDto.OutStockQty = InvTransHistDayDto.Where(a => a.ItemNum == item).Sum(b => b.QtyChangeOut);

                        //库存数据取进出存期末数据
                        itemKanBanDto.Amount = InvTransHistDayDto.Where(a => a.ItemNum == item).OrderByDescending(a => a.HistDayTime).First().BalanceNum * itemKanBanDto.NETPR;

                        itemKanBanDto.MoveCount = InvTransHistDayDto.Where(a => a.ItemNum == item && a.QtyChangeOut > 0).Count();
                        int days = endDate.Subtract(beginDate).Days;
                        //同一天算1天
                        if (days == 0)
                            days = 1;
                        itemKanBanDto.ADU = itemKanBanDto.OutStockQty / days;
                        itemKanBanDto.Kanban = itemKanBanDto.ADU * itemKanBanDto.PLT;
                        kanBanDtos.Add(itemKanBanDto);
                    }
                    
                });
                decimal totalmoveCount = kanBanDtos.Sum(a=>a.MoveCount);
                decimal totalAmount = kanBanDtos.Sum(a => a.Amount);
                decimal avgmoveCount = kanBanDtos.Average(a => a.MoveCount);
                kanBanDtos=kanBanDtos.OrderByDescending(a => a.Amount).ToList();
                for (int i=0;i< kanBanDtos.Count;i++)
                {
                    if (kanBanDtos[i].MoveCount > avgmoveCount)
                    {
                        kanBanDtos[i].FMR = "F";
                    } 
                    else if(kanBanDtos[i].MoveCount < avgmoveCount / 2)
                    {
                        kanBanDtos[i].FMR = "R";
                    }else
                    {
                        kanBanDtos[i].FMR = "M";
                    }

                    if(i<=kanBanDtos.Count*0.7)
                    {
                        kanBanDtos[i].ABC = "A";
                    }else if(i>=kanBanDtos.Count*0.9)
                    {
                        kanBanDtos[i].ABC = "C";
                    }else
                    {
                        kanBanDtos[i].ABC = "B";
                    }
                    kanBanDtos[i].AmountTotal = totalAmount;
                    kanBanDtos[i].MovePencent = totalmoveCount == 0 ? 0 : kanBanDtos[i].MoveCount * 100 / totalmoveCount;
                }
                int totalCount = kanBanDtos.Count();
                dto.jzpc1 = $"{kanBanDtos.Where(a => a.ABC == "A" && a.FMR == "R").Count()}/{kanBanDtos.Count}";
                dto.jzpc2 = $"{kanBanDtos.Where(a => a.ABC == "A" && a.FMR == "M").Count()}/{kanBanDtos.Count}";
                dto.jzpc3 = $"{kanBanDtos.Where(a => a.ABC == "A" && a.FMR == "F").Count()}/{kanBanDtos.Count}";
                dto.jzpc4 = $"{kanBanDtos.Where(a => a.ABC == "B" && a.FMR == "R").Count()}/{kanBanDtos.Count}";
                dto.jzpc5 = $"{kanBanDtos.Where(a => a.ABC == "B" && a.FMR == "M").Count()}/{kanBanDtos.Count}";
                dto.jzpc6 = $"{kanBanDtos.Where(a => a.ABC == "B" && a.FMR == "F").Count()}/{kanBanDtos.Count}";
                dto.jzpc7 = $"{kanBanDtos.Where(a => a.ABC == "C" && a.FMR == "R").Count()}/{kanBanDtos.Count}";
                dto.jzpc8 = $"{kanBanDtos.Where(a => a.ABC == "C" && a.FMR == "M").Count()}/{kanBanDtos.Count}";
                dto.jzpc9 = $"{kanBanDtos.Where(a => a.ABC == "C" && a.FMR == "F").Count()}/{kanBanDtos.Count}";
                return JsonConvert.SerializeObject(dto);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public string ValueFrequencyDetail(string Loc, string BeginDate, string EndDate, string KanBan, string Frequency, string JZ, string PC)
        {
            try
            {
                List<ValueFrequencyDetailDto> dtos = new List<ValueFrequencyDetailDto>();
                try
                {
                    DateTime beginDate = Convert.ToDateTime(BeginDate);
                    DateTime endDate = Convert.ToDateTime(EndDate);
                    string sql = $"select distinct ItemNum,Min(CreateTime) AS HistMinTime from InvTransHist where Domain='8010' and createTime>='{beginDate}' and createTime<='{endDate}' group by ItemNum";
                    var InvTransHistDto = _businessDbContext.InvTransHistDayDto.FromSqlRaw(sql).ToList();
                    var itemList = InvTransHistDto.Select(a => a.ItemNum).Distinct().ToList();
                    ValueFrequencyDto dto = new ValueFrequencyDto();
                    

                    List<ValueFrequencyKanBanDto> kanBanDtos = new List<ValueFrequencyKanBanDto>();
                    var icitems = _ic_item.GetListAsync(a => itemList.Contains(a.number)).Result;
                    var srm_purchases = _srm_purchase.GetListAsync(a => icitems.Select(b => b.Id).ToList().Contains(a.icitem_id)).Result;

                    string sqldzd = @"select MATNR AS ItemNum,Max(NETPR)*10 AS NETPR from SupplierStatement group by  MATNR";
                    var dzdList = _businessDbContext.SupplierStatementDto.FromSqlRaw(sqldzd).ToList();

                    var InvTransHistDayDto = _InvTransHistDay.Select(a=>a.HistDayTime>= beginDate.Date && a.HistDayTime<= endDate);
                    itemList.ForEach(item =>
                    {
                        if (icitems.Any(a => a.number == item))
                        {
                            ValueFrequencyKanBanDto itemKanBanDto = new ValueFrequencyKanBanDto();
                            itemKanBanDto.ItemNumber = item;
                            itemKanBanDto.ItemId = icitems.Find(a => a.number == item).Id;
                            itemKanBanDto.Model = icitems.Find(a => a.number == item).model;
                            itemKanBanDto.ItemName = icitems.Find(a => a.number == item).name;
                            itemKanBanDto.PLT = 14;
                            //供应提前期取货源清单最大值，没有默认14天
                            if (srm_purchases.Any(a => a.icitem_id == itemKanBanDto.ItemId && a.quota_rate.GetValueOrDefault() > 0))
                            {
                                itemKanBanDto.PLT = srm_purchases.Where(a => a.number == item && a.quota_rate.GetValueOrDefault() > 0).Max(a => a.lead_time).Value;
                            }
                            var entry = dzdList.FirstOrDefault(p => p.ItemNum == item);
                            if (entry != null)
                            {
                                itemKanBanDto.NETPR = entry.NETPR;
                            }
                            else
                            {
                                itemKanBanDto.NETPR = 1 * 10;
                            }
                            //出库总数
                            itemKanBanDto.OutStockQty = InvTransHistDayDto.Where(a=>a.ItemNum==item).Sum(b=>b.QtyChangeOut);

                            //库存数据取进出存期末数据
                            itemKanBanDto.Amount = InvTransHistDayDto.Where(a => a.ItemNum == item).OrderByDescending(a=>a.HistDayTime).First().BalanceNum * itemKanBanDto.NETPR;
               
                            itemKanBanDto.MoveCount = InvTransHistDayDto.Where(a => a.ItemNum == item && a.QtyChangeOut>0).Count();
                            int days = endDate.Subtract(beginDate).Days;
                            //同一天算1天
                            if (days == 0)
                                days = 1;
                            itemKanBanDto.ADU = itemKanBanDto.OutStockQty / days;
                            itemKanBanDto.Kanban = itemKanBanDto.ADU * itemKanBanDto.PLT;
                            kanBanDtos.Add(itemKanBanDto);
                        }

                    });
                    decimal totalmoveCount = kanBanDtos.Sum(a => a.MoveCount);
                    decimal totalAmount = kanBanDtos.Sum(a => a.Amount);
                    decimal avgmoveCount = kanBanDtos.Average(a => a.MoveCount);
                    kanBanDtos = kanBanDtos.OrderByDescending(a => a.Amount).ToList();
                    for (int i = 0; i < kanBanDtos.Count; i++)
                    {
                        if (kanBanDtos[i].MoveCount > avgmoveCount)
                        {
                            kanBanDtos[i].FMR = "F";
                        }
                        else if (kanBanDtos[i].MoveCount < avgmoveCount / 2)
                        {
                            kanBanDtos[i].FMR = "R";
                        }
                        else
                        {
                            kanBanDtos[i].FMR = "M";
                        }

                        if (i <= kanBanDtos.Count * 0.7)
                        {
                            kanBanDtos[i].ABC = "A";
                        }
                        else if (i >= kanBanDtos.Count * 0.9)
                        {
                            kanBanDtos[i].ABC = "C";
                        }
                        else
                        {
                            kanBanDtos[i].ABC = "B";
                        }
                        kanBanDtos[i].AmountTotal = totalAmount;
                        kanBanDtos[i].MovePencent = totalmoveCount == 0 ? 0 : kanBanDtos[i].MoveCount * 100 / totalmoveCount;
                    }
                    kanBanDtos=kanBanDtos.Where(a=>a.ABC==JZ&&a.FMR==PC && a.Kanban > Convert.ToDecimal(KanBan) && a.MovePencent*100 > Convert.ToDecimal(Frequency)).OrderByDescending(a=>a.Amount).ToList();
                    for (int i=0;i< kanBanDtos.Count(); i++)
                    {
                        ValueFrequencyDetailDto detailDto = new ValueFrequencyDetailDto();
                        detailDto.RowSeq =i+1;
                        detailDto.ItemNum = kanBanDtos[i].ItemNumber;
                        detailDto.ItemName = kanBanDtos[i].ItemName;
                        detailDto.Model = kanBanDtos[i].Model;
                        detailDto.ItemABC = kanBanDtos[i].ABC;
                        detailDto.ItemFMR = kanBanDtos[i].FMR;
                        detailDto.Amount = kanBanDtos[i].Amount;
                        detailDto.AmountFix = Convert.ToDecimal(KanBan) * kanBanDtos[i].NETPR;
                        detailDto.AmountDiff = detailDto.Amount- detailDto.AmountFix;
                        dtos.Add(detailDto);
                    }
                    return JsonConvert.SerializeObject(dtos);
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public string ChartLineOption(string Loc, string BeginDate, string EndDate, string ItemNum,string TurnOver,string KanBan)
        {
            try
            {
                DateTime beginDate = Convert.ToDateTime(BeginDate);
                DateTime endDate = Convert.ToDateTime(EndDate);
                var srm_purchases = _srm_purchase.GetListAsync(a =>a.number==ItemNum).Result;
                decimal chartPLT = 14;
                decimal chartKanBan = Convert.ToDecimal(KanBan);
                //供应提前期取货源清单最大值，没有默认14天
                if (srm_purchases.Any(a => a.number == ItemNum && a.quota_rate.GetValueOrDefault() > 0))
                {
                    chartPLT = srm_purchases.Where(a => a.number == ItemNum && a.quota_rate.GetValueOrDefault() > 0).Max(a => a.lead_time).Value;
                }

                var InvTransHistDayDto = _InvTransHistDay.Select(a => a.ItemNum== ItemNum && a.HistDayTime >= beginDate.Date && a.HistDayTime <= endDate);
                int tranDays = (endDate-beginDate).Days;
                List<ValueFrequencyChartDto> chartDtos = new List<ValueFrequencyChartDto>();
                for(int i = 0; i < tranDays; i++)
                {
                    ValueFrequencyChartDto dtoChart = new ValueFrequencyChartDto();
                    dtoChart.Date = beginDate.AddDays(i).ToString("yyyy-MM-dd");
                    var itemDays = InvTransHistDayDto.Where(a => beginDate.AddDays(i).Date == a.HistDayTime.Date).ToList();
                    if(itemDays.Count>0)
                    {
                        dtoChart.InStockQty = itemDays[0].QtyChangeAdvance;
                        dtoChart.OutStockQty = itemDays[0].QtyChangeOut;
                        dtoChart.StockQty = itemDays[0].BalanceNum;
                        dtoChart.SuggestQty = chartKanBan * chartPLT;
                    }else
                    {
                        dtoChart.InStockQty = 0;
                        dtoChart.OutStockQty = 0;
                        dtoChart.StockQty = 0;
                        dtoChart.SuggestQty = 0;
                    }
                    chartDtos.Add(dtoChart);
                }
                chartDtos = chartDtos.Where(a => string.Compare(a.Date, BeginDate) >= 0 && string.Compare(a.Date, EndDate) <= 0).ToList();

                StringBuilder sb = new StringBuilder();
                StringBuilder sbDays = new StringBuilder();
                StringBuilder sbDaysStock = new StringBuilder();
                StringBuilder sbDaysInStock = new StringBuilder();
                StringBuilder sbDaysOutStock = new StringBuilder();
                StringBuilder sbDaysSuggest = new StringBuilder();
                sb.Append("{tooltip:{trigger:'axis'},legend:{data: ['日库存数量','日出库数量','日入库数量','推荐日库存数量']},grid:{left:'3%',right:'4%',bottom:'3%',containLabel:true},toolbox:{feature:{saveAsImage: {}}},xAxis: {type:'category',boundaryGap: false,data:[");
                chartDtos.ForEach(a => 
                {
                    sbDays.Append(string.Format("'{0}',",a.Date));
                    sbDaysStock.Append(string.Format("{0},", a.StockQty));
                    sbDaysOutStock.Append(string.Format("{0},", a.OutStockQty));
                    sbDaysInStock.Append(string.Format("{0},", a.InStockQty));
                    sbDaysSuggest.Append(string.Format("{0},", a.SuggestQty));
                });
                sb.Append(sbDays.ToString().TrimEnd(',')).
                   Append("]},yAxis:{type:'value',},series:[{name:'日库存数量',type:'line',stack: 'Total',data:[").
                   Append(sbDaysStock.ToString().TrimEnd(',')).
                   Append("]},{name:'日出库数量',type:'line',stack:'Total',data:[").
                   Append(sbDaysOutStock.ToString().TrimEnd(',')).
                   Append("]},{name:'日入库数量',type:'line',stack:'Total',data:[").
                   Append(sbDaysInStock.ToString().TrimEnd(',')).
                   Append("]},{name:'推荐日库存数量',type:'line',stack:'Total',data:[").
                   Append(sbDaysSuggest.ToString().TrimEnd(',')).
                   Append("]}]}");
                return sb.ToString();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public string ChartLineSupplierPLTOption()
        {
            return "{title:{text: '供应商PLT占比(%)',left:'center',},tooltip:{trigger: 'axis'},toolbox: {show: true,feature:{saveAsImage: {}}},xAxis: {type: 'category',data: ['1', '2', '3', '4', '5', '6', '7']},yAxis: {type: 'value',axisLabel: {formatter: '{value} %'},axisPointer: {snap: true} },series: [{data: [2, 5, 18, 55, 12, 5, 3],type: 'line',smooth: true}]}";
        }

        public string ChartLinePLTOption(string PLT)
        {
            if(PLT=="25")
            {
                string sql = @"select t.[Date] AS [Date],t.DateStockQty AS StockQty,t.DateOutStockQty AS OutStockQty,t.DateInStockQty AS InStockQty,0.0 AS SuggestQty  from [dbo].[25PLT] t";
                var chartDtosTest = _businessDbContext.ValueFrequencyChartDto.FromSqlRaw(sql).ToList();
                StringBuilder sb = new StringBuilder();
                StringBuilder sbDays = new StringBuilder();
                StringBuilder sbDaysStock = new StringBuilder();
                StringBuilder sbDaysInStock = new StringBuilder();
                StringBuilder sbDaysOutStock = new StringBuilder();
                sb.Append("{tooltip:{trigger:'axis'},legend:{data: ['日库存数量','日出库数量','日入库数量']},grid:{left:'3%',right:'4%',bottom:'3%',containLabel:true},toolbox:{feature:{saveAsImage: {}}},xAxis: {type:'category',boundaryGap: false,data:[");
                chartDtosTest.ForEach(a =>
                {
                    sbDays.Append(string.Format("'{0}',", a.Date));
                    sbDaysStock.Append(string.Format("{0},", a.StockQty));
                    sbDaysOutStock.Append(string.Format("{0},", a.OutStockQty));
                    sbDaysInStock.Append(string.Format("{0},", a.InStockQty));
                });
                sb.Append(sbDays.ToString().TrimEnd(',')).
                   Append("]},yAxis:{type:'value',},series:[{name:'日库存数量',type:'line',data:[").
                   Append(sbDaysStock.ToString().TrimEnd(',')).
                   Append("]},{name:'日出库数量',type:'line',data:[").
                   Append(sbDaysOutStock.ToString().TrimEnd(',')).
                   Append("]},{name:'日入库数量',type:'line',data:[").
                   Append(sbDaysInStock.ToString().TrimEnd(',')).
                   Append("]}").Append("]}");
                return sb.ToString();
            }else
            {
                string sql = @"select t.[Date] AS [Date],t.DateStockQty AS StockQty,t.DateOutStockQty AS OutStockQty,t.DateInStockQty AS InStockQty,0.0 AS SuggestQty  from [dbo].[15PLT] t";
                var chartDtosTest = _businessDbContext.ValueFrequencyChartDto.FromSqlRaw(sql).ToList();
                StringBuilder sb = new StringBuilder();
                StringBuilder sbDays = new StringBuilder();
                StringBuilder sbDaysStock = new StringBuilder();
                StringBuilder sbDaysInStock = new StringBuilder();
                StringBuilder sbDaysOutStock = new StringBuilder();
                sb.Append("{tooltip:{trigger:'axis'},legend:{data: ['日库存数量','日出库数量','日入库数量']},grid:{left:'3%',right:'4%',bottom:'3%',containLabel:true},toolbox:{feature:{saveAsImage: {}}},xAxis: {type:'category',boundaryGap: false,data:[");
                chartDtosTest.ForEach(a =>
                {
                    sbDays.Append(string.Format("'{0}',", a.Date));
                    sbDaysStock.Append(string.Format("{0},", a.StockQty));
                    sbDaysOutStock.Append(string.Format("{0},", a.OutStockQty));
                    sbDaysInStock.Append(string.Format("{0},", a.InStockQty));
                });
                sb.Append(sbDays.ToString().TrimEnd(',')).
                   Append("]},yAxis:{type:'value',},series:[{name:'日库存数量',type:'line',data:[").
                   Append(sbDaysStock.ToString().TrimEnd(',')).
                   Append("]},{name:'日出库数量',type:'line',data:[").
                   Append(sbDaysOutStock.ToString().TrimEnd(',')).
                   Append("]},{name:'日入库数量',type:'line',data:[").
                   Append(sbDaysInStock.ToString().TrimEnd(',')).
                   Append("]}").Append("]}");
                return sb.ToString();
            }
        }


        public string ChartLineModelOption(string factory_id, string model,string avgHW, string avgGK, string avgT1, string avgTotal)
        {
            var loclist = _DMS_IN_LOCDETAIL.Select(a => a.UPN == model);
            var lotList=loclist.Select(a => a.LOT).Distinct().ToList();
            decimal? hw = 0;
            decimal? gk = 0;
            decimal? t1 = 0;
            decimal? total = 0;
            lotList.ForEach(a =>
            {
                var itemHW = loclist.Where(x => x.LOT == a && x.DealerLevel == "LP" && x.DealerCode == "RQ000002").ToList();
                if(itemHW.Count>0)
                {
                    hw += itemHW.OrderByDescending(y => y.InventoryDate).First().Qty;
                }

                var itemGK = loclist.Where(x => x.LOT == a && x.DealerLevel == "LP" && x.DealerCode == "RQ000005").ToList();
                if (itemGK.Count > 0)
                {
                    gk += itemGK.OrderByDescending(y => y.InventoryDate).First().Qty;
                }

                var itemT1 = loclist.Where(x => x.LOT == a && (x.DealerLevel == "T1" || x.DealerLevel == "LS")).ToList();
                if (itemT1.Count > 0)
                {
                    t1 += itemT1.OrderByDescending(y => y.InventoryDate).First().Qty;
                }
            });
            total=hw+gk+t1;
            if(avgHW!="0")
            {
                string numDecimalStr = (hw.Value / decimal.Parse(avgHW)).ToString("#0.00");
                hw = decimal.Parse(numDecimalStr);
            }else
            {
                hw = 0;
            }
            if (avgGK != "0")
            {
                string numDecimalStr = (hw.Value / decimal.Parse(avgGK)).ToString("#0.00");
                gk = decimal.Parse(numDecimalStr);
            }
            else
            {
                gk = 0;
            }
            if (avgT1 != "0")
            {
                string numDecimalStr = (hw.Value / decimal.Parse(avgT1)).ToString("#0.00");
                t1 = decimal.Parse(numDecimalStr);
            }
            else
            {
                t1 = 0;
            }
            if (avgTotal != "0")
            {
                string numDecimalStr = (hw.Value / decimal.Parse(avgTotal)).ToString("#0.00");
                total = decimal.Parse(numDecimalStr);
            }
            else
            {
                total = 0;
            }
            StringBuilder sb = new StringBuilder();
            sb.Append("{title: {text:'库存可供应周期(月)',left: 'center'},xAxis:{type:'category',data:['海王库存','国科库存','T1','Total']},yAxis:{type:'value'},series:[{data:[" + hw+","+gk+","+t1+","+total+"],type:'bar',label:{show:true,position:'top'},itemStyle:{normal:{color:function(params){if(params.value<4){return 'red';}else{return '#5470c6';}}}}}]}");
            return sb.ToString();
        }

        public string ChartLineOutStockOption(string factory_id, string model)
        {
            DateOnly dateOnly = DateOnly.FromDateTime(DateTime.Now.AddMonths(-14));
            var polist=_DMS_IN_PODETAIL.Select(a => a.UPN == model && a.OrderStatus != "Revoked" && a.OrderStatus != "Rejected" && a.SubmitDate>=dateOnly);
            StringBuilder sbDate = new StringBuilder();
            StringBuilder sbHWOutStock = new StringBuilder();
            StringBuilder sbGKOutStock = new StringBuilder();
            StringBuilder sbT1OutStock = new StringBuilder();
            StringBuilder sbTotalOutStock = new StringBuilder();
            for (int i=-14;i<1;i++)
            {
                sbDate.Append(string.Format("'{0}',",DateTime.Now.AddMonths(i).Date.ToString("yyyy-MM-dd")));
                var hw = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(i)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(i + 1)) && a.DealerCode== "RQ000002" && a.DealerLevel=="LP").Sum(a => a.RequiredQty);
                var gk = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(i)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(i + 1)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty);
                var t1 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(i)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(i + 1)) &&(a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty);
                var total = hw + gk + t1;
                sbHWOutStock.Append(string.Format("{0},", hw));
                sbGKOutStock.Append(string.Format("{0},", gk));
                sbT1OutStock.Append(string.Format("{0},", t1));
                sbTotalOutStock.Append(string.Format("{0},", total));
            }
            StringBuilder sb = new StringBuilder();
            sb.Append("{title: {text:'");
            sb.Append(model+"出货趋势',");
            sb.Append("left: 'center',},tooltip: {trigger: 'axis'},legend: {data:['海王', '国科', 'T1','Total'],bottom:-5,orient: 'horizontal'},grid: {left: '3%',right: '4%',bottom: '3%',containLabel: true},toolbox: {feature: {saveAsImage: {}}},xAxis:{type: 'category',boundaryGap: false,data:[");
            sb.Append(sbDate.ToString().TrimEnd(',')).
                  Append("]},yAxis:{type:'value',},series:[{name:'海王',type:'line',stack: 'Total',data:[").
                  Append(sbHWOutStock.ToString().TrimEnd(',')).
                  Append("]},{name:'国科',type:'line',stack:'Total',data:[").
                  Append(sbGKOutStock.ToString().TrimEnd(',')).
                  Append("]},{name:'T1',type:'line',stack:'Total',data:[").
                  Append(sbT1OutStock.ToString().TrimEnd(',')).
                  Append("]},{name:'Total',type:'line',stack:'Total',data:[").
                  Append(sbTotalOutStock.ToString().TrimEnd(',')).
                  Append("]}]}");
            return sb.ToString();
        }

        public string ChartLineOutStock(string factory_id, string model)
        {
            DateOnly dateOnly = DateOnly.FromDateTime(DateTime.Now.AddMonths(-14));
            var polist = _DMS_IN_PODETAIL.Select(a => a.UPN == model && a.OrderStatus != "Revoked" && a.OrderStatus != "Rejected" && a.SubmitDate >= dateOnly);
            List<ModelMonthOutkDto> modelMonthOuts=new List<ModelMonthOutkDto>();
            ModelMonthOutkDto stockDto1 = new ModelMonthOutkDto();
            stockDto1.Month0 = "海王";
            stockDto1.Month1 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-14)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-13)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value);
            stockDto1.Month2 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-13)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-12)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month3 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-12)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-11)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value);
            stockDto1.Month4 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-11)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-10)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month5 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-10)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-9)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month6 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-9)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-8)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month7 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-8)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-7)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month8 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-7)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-6)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month9 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-6)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-5)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month10 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-5)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-4)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month11 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-4)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-3)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month12 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-3)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-2)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month13 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-2)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-1)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); 
            stockDto1.Month14 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-1)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(0)) && a.DealerCode == "RQ000002" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value);  
            modelMonthOuts.Add(stockDto1);

            ModelMonthOutkDto stockDto2 = new ModelMonthOutkDto();
            stockDto2.Month0 = "国科";
            stockDto2.Month1 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-14)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-13)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value);
            stockDto2.Month2 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-13)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-12)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month3 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-12)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-11)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month4 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-11)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-10)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month5 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-10)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-9)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month6 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-9)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-8)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month7 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-8)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-7)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month8 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-7)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-6)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month9 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-6)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-5)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month10 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-5)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-4)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month11 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-4)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-3)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month12 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-3)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-2)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month13 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-2)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-1)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            stockDto2.Month14 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-1)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(0)) && a.DealerCode == "RQ000005" && a.DealerLevel == "LP").Sum(a => a.RequiredQty.Value); ;
            modelMonthOuts.Add(stockDto2);

            ModelMonthOutkDto stockDto3 = new ModelMonthOutkDto();
            stockDto3.Month0 = "T1";
            stockDto3.Month1 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-14)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-13)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value);
            stockDto3.Month2 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-13)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-12)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month3 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-12)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-11)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month4 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-11)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-10)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month5 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-10)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-9)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month6 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-9)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-8)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month7 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-8)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-7)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month8 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-7)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-6)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month9 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-6)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-5)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month10 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-5)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-4)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month11 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-4)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-3)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month12 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-3)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-2)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month13 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-2)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(-1)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            stockDto3.Month14 = polist.Where(a => a.SubmitDate >= DateOnly.FromDateTime(DateTime.Now.AddMonths(-1)) && a.SubmitDate < DateOnly.FromDateTime(DateTime.Now.AddMonths(0)) && (a.DealerLevel == "LP" || a.DealerLevel == "LS")).Sum(a => a.RequiredQty.Value); ;
            modelMonthOuts.Add(stockDto3);

            ModelMonthOutkDto stockDto4 = new ModelMonthOutkDto();
            stockDto4.Month0 = "Total";
            stockDto4.Month1 =stockDto1.Month1+stockDto2.Month1+stockDto3.Month1;
            stockDto4.Month2 = stockDto1.Month2 + stockDto2.Month2 + stockDto3.Month2; 
            stockDto4.Month3 = stockDto1.Month3 + stockDto2.Month3 + stockDto3.Month3;
            stockDto4.Month4 = stockDto1.Month4 + stockDto2.Month4 + stockDto3.Month4;
            stockDto4.Month5 = stockDto1.Month5 + stockDto2.Month5 + stockDto3.Month5;
            stockDto4.Month6 = stockDto1.Month6 + stockDto2.Month6 + stockDto3.Month6;
            stockDto4.Month7 = stockDto1.Month7 + stockDto2.Month7 + stockDto3.Month7;
            stockDto4.Month8 = stockDto1.Month8 + stockDto2.Month8 + stockDto3.Month8;
            stockDto4.Month9 = stockDto1.Month9 + stockDto2.Month9 + stockDto3.Month9;
            stockDto4.Month10 = stockDto1.Month10 + stockDto2.Month10 + stockDto3.Month10;
            stockDto4.Month11 = stockDto1.Month11 + stockDto2.Month11 + stockDto3.Month11;
            stockDto4.Month12 = stockDto1.Month12 + stockDto2.Month12 + stockDto3.Month12;
            stockDto4.Month13 = stockDto1.Month13 + stockDto2.Month13 + stockDto3.Month13;
            stockDto4.Month14 = stockDto1.Month14 + stockDto2.Month14 + stockDto3.Month14;
            modelMonthOuts.Add(stockDto4);

            ModelMonthOutkDto stockDto5 = new ModelMonthOutkDto();
            stockDto5.Month0 = "海王均值";
            stockDto5.Month1 = decimal.Ceiling((stockDto1.Month12+stockDto1.Month13+stockDto1.Month14)/3);
            stockDto5.Month2 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month3 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month4 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month5 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month6 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month7 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month8 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month9 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month10 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month11 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month12 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month13 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            stockDto5.Month14 = decimal.Ceiling((stockDto1.Month12 + stockDto1.Month13 + stockDto1.Month14) / 3);
            modelMonthOuts.Add(stockDto5);

            ModelMonthOutkDto stockDto6 = new ModelMonthOutkDto();
            stockDto6.Month0 = "国科均值";
            stockDto6.Month1 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month2 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month3 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month4 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month5 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month6 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month7 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month8 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month9 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month10 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month11 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month12 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month13 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            stockDto6.Month14 = decimal.Ceiling((stockDto2.Month12 + stockDto2.Month13 + stockDto2.Month14) / 3);
            modelMonthOuts.Add(stockDto6);

            ModelMonthOutkDto stockDto7 = new ModelMonthOutkDto();
            stockDto7.Month0 = "T1均值";
            stockDto7.Month1 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month2 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month3 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month4 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month5 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month6 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month7 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month8 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month9 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month10 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month11 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month12 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month13 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            stockDto7.Month14 = decimal.Ceiling((stockDto3.Month12 + stockDto3.Month13 + stockDto3.Month14) / 3);
            modelMonthOuts.Add(stockDto7);

            ModelMonthOutkDto stockDto8 = new ModelMonthOutkDto();
            stockDto8.Month0 = "Total均值";
            stockDto8.Month1 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month2 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month3 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month4 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month5 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month6 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month7 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month8 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month9 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month10 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month11 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month12 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month13 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            stockDto8.Month14 = decimal.Ceiling((stockDto4.Month12 + stockDto4.Month13 + stockDto4.Month14) / 3);
            modelMonthOuts.Add(stockDto8);
            return JsonConvert.SerializeObject(modelMonthOuts);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="factory_id"></param>
        /// <param name="isItemNum">统计维度:M物料、S系列</param>
        /// <param name="itemNumRange">统计范围:F工厂、G国科、H海王、A工厂+平台</param>
        /// <param name="itemNum">物料编码</param>
        /// <param name="groupBy">系列</param>
        /// <returns></returns>
        public string ForecastActual(string factory_id, string isItemNum, string itemNumRange, string itemNum, string groupBy)
        {
            string sql = $"exec [dbo].[pr_DOP_InventoryMonitoring]";
            var InventoryDto = _businessDbContext.InventoryDto.FromSqlRaw(sql).ToList();
            DateOnly dateOnly = DateOnly.FromDateTime(DateTime.Now.AddMonths(-2));
            var polist = _DMS_IN_PODETAIL.Select(a =>a.SubmitDate >= dateOnly && a.OrderStatus != "Revoked" && a.OrderStatus != "Rejected");
            var rqShip = _aSNBOLShipperDetail.Select(a => a.CreateTime >= DateTime.Now.AddMonths(-2) && a.Domain == factory_id);
            List<ForecastActualDto> modelMonthOuts = new List<ForecastActualDto>();
            string strMonth1 = DateTime.Now.AddMonths(-1).ToString("yyyy-MM");
            string strMonth2 = DateTime.Now.ToString("yyyy-MM");
            //按照物料统计
            if (isItemNum == "M")
            {
                switch (itemNumRange)
                {
                    case "F":
                        if(!string.IsNullOrEmpty(itemNum))
                        {
                            var shipPlan=_monthlyShipmentPlan.GetListAsync(a => a.SAPItemNumber == itemNum && (a.PlanMonth== strMonth1 || a.PlanMonth == strMonth2)
                            && a.DistributionChannel == "瑞奇").Result;
                            var itemDto = InventoryDto.Find(a => a.ItemNumber == itemNum);
                            if(itemDto!=null)
                            {
                                ForecastActualDto forecastActualDto = new ForecastActualDto();
                                forecastActualDto.ItemNum = itemNum;
                                forecastActualDto.GroupBy = itemDto.Series;
                                forecastActualDto.StockTurnOver = itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand==0?0:decimal.Ceiling((itemDto.GKInv+ itemDto.HWInv)/(itemDto.GKMonthlyDemand+ itemDto.HWMonthlyDemand));
                                //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                forecastActualDto.M1Plan = 0;
                                forecastActualDto.M2Plan = 0;
                                if (shipPlan.Any(a=> a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                {
                                    forecastActualDto.M1Plan = shipPlan.Find(a=>a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Qty;
                                }
                                if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                {
                                    forecastActualDto.M2Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Qty;
                                }
                                forecastActualDto.M1Real = rqShip.Where(a=>a.ContainerItem==itemNum && a.CreateTime>=DateTime.Now.AddMonths(-2) && a.CreateTime < DateTime.Now.AddMonths(-1)).Sum(b=>b.QtyToShip.Value);
                                forecastActualDto.M2Real = rqShip.Where(a => a.ContainerItem == itemNum && a.CreateTime >= DateTime.Now.AddMonths(-1) && a.CreateTime < DateTime.Now).Sum(b => b.QtyToShip.Value);
                                forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                modelMonthOuts.Add(forecastActualDto);
                            }
                        }else
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2)
                            && a.DistributionChannel == "瑞奇").Result;
                            var itemList = _itemMaster.Select(a =>a.ItemType == "RS50" && a.Domain==factory_id).Select(a=>a.ItemNum).Distinct().ToList();
                            var shipItemList = shipPlan.Select(a => a.SAPItemNumber).Distinct().ToList();
                            var intersection = itemList.Intersect(shipItemList).ToList();
                            intersection.ForEach(x=>
                            {
                                var itemDto = InventoryDto.Find(a => a.ItemNumber == x);
                                if (itemDto != null)
                                {
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.ItemNum = x;
                                    forecastActualDto.GroupBy = itemDto.Series;
                                    forecastActualDto.StockTurnOver = itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand == 0 ? 0 : decimal.Ceiling((itemDto.GKInv + itemDto.HWInv) / (itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand));
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Qty;
                                    }
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Qty;
                                    }
                                    forecastActualDto.M1Real = rqShip.Where(a => a.ContainerItem == itemNum && a.CreateTime >= DateTime.Now.AddMonths(-2) && a.CreateTime < DateTime.Now.AddMonths(-1)).Sum(b => b.QtyToShip.Value);
                                    forecastActualDto.M2Real = rqShip.Where(a => a.ContainerItem == itemNum && a.CreateTime >= DateTime.Now.AddMonths(-1) && a.CreateTime < DateTime.Now).Sum(b => b.QtyToShip.Value);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            });
                        }
                        break;
                    case "G":
                    case "H":
                        string gkhw = itemNumRange == "G" ? "国科" : "海王";
                        string gkhwcode = itemNumRange == "G" ? "RQ000005" : "RQ000002";
                        if (!string.IsNullOrEmpty(itemNum))
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => a.SAPItemNumber == itemNum && (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2) && a.DistributionChannel == gkhw).Result;
                            var item = _DMS_IN_ITEMMAPPING.Select(a => a.CfnERPCode == itemNum);
                            if (item != null && item.Count>0)
                            {
                                var itemDto = InventoryDto.Find(a => a.ItemNumber == itemNum);
                                if (itemDto != null)
                                {
                                    //表没有主键索引，用仓储查询很慢，改用这种方式
                                    string month1 = $"select '{itemNum}' AS ItemNum,UPN,SUM(Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] where UPN='{item[0].CfnCode}' and DealerLevel='T2' and ParentDealerCode='{gkhwcode}' and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}'  group by UPN";
                                    var month1Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month1).ToList();
                                    string month2 = $"select '{itemNum}' AS ItemNum,UPN,SUM(Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] where UPN='{item[0].CfnCode}' and DealerLevel='T2' and ParentDealerCode='{gkhwcode}' and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.ToString("yyyy-MM-dd")}'  group by UPN";
                                    var month2Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month2).ToList();
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.ItemNum = itemNum;
                                    forecastActualDto.GroupBy = itemDto.Series;
                                    forecastActualDto.StockTurnOver = itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand == 0 ? 0 : decimal.Ceiling((itemDto.GKInv + itemDto.HWInv) / (itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand));
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Qty;
                                    }
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Qty;
                                    }
                                    forecastActualDto.M1Real = month1Qty.Sum(a => a.Qty);
                                    forecastActualDto.M2Real = month2Qty.Sum(a => a.Qty);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            }
                        }
                        else
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2) && a.DistributionChannel == gkhw).Result;
                            var itemList = _itemMaster.Select(a => a.ItemType == "RS50" && a.Domain == factory_id).Select(a => a.ItemNum).Distinct().ToList();
                            string month1 = $"Select B.CfnERPCode AS ItemNum,A.UPN,SUM(A.Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode where DealerLevel='T2' and ParentDealerCode='{gkhwcode}' and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                            var month1Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month1).ToList();
                            string month2 = $"Select B.CfnERPCode AS ItemNum,A.UPN,SUM(A.Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode where DealerLevel='T2' and ParentDealerCode='{gkhwcode}' and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                            var month2Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month2).ToList();
                            var shipItemList = shipPlan.Select(a => a.SAPItemNumber).Distinct().ToList();
                            var intersection = itemList.Intersect(shipItemList).ToList();
                            intersection.ForEach(x =>
                            {
                                var itemDto = InventoryDto.Find(a => a.ItemNumber == x);
                                if (itemDto != null)
                                {
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.ItemNum = x;
                                    forecastActualDto.GroupBy = itemDto.Series;
                                    forecastActualDto.StockTurnOver = itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand == 0 ? 0 : decimal.Ceiling((itemDto.GKInv + itemDto.HWInv) / (itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand));
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => a.SAPItemNumber==x && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Find(a => a.SAPItemNumber == x && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Qty;
                                    }
                                    if (shipPlan.Any(a => a.SAPItemNumber == x && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Find(a => a.SAPItemNumber == x && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Qty;
                                    }
                                    forecastActualDto.M1Real = month1Qty.Where(a => a.ItemNum == x).Sum(a => a.Qty);
                                    forecastActualDto.M2Real = month2Qty.Where(a => a.ItemNum == x).Sum(a => a.Qty);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            });
                        }
                        break;
                    case "A":
                        if (!string.IsNullOrEmpty(itemNum))
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => a.SAPItemNumber == itemNum && (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2)).Result;

                            var item = _DMS_IN_ITEMMAPPING.Select(a => a.CfnERPCode == itemNum);
                            if (item != null && item.Count>0)
                            {
                                var itemDto = InventoryDto.Find(a => a.ItemNumber == itemNum);
                                if (itemDto != null)
                                {
                                    //表没有主键索引，用仓储查询很慢，改用这种方式
                                    string month1 = $"select '{itemNum}' AS ItemNum,UPN,SUM(Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] where UPN='{item[0].CfnCode}' and ((DealerLevel='T2' and (ParentDealerCode='RQ000005' or ParentDealerCode='RQ000002')) or (DealerLevel='T1'  or DealerLevel='LS')) and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' group by UPN";
                                    var month1Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month1).ToList();
                                    string month2 = $"select '{itemNum}' AS ItemNum,UPN,SUM(Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] where UPN='{item[0].CfnCode}' and ((DealerLevel='T2' and (ParentDealerCode='RQ000005' or ParentDealerCode='RQ000002')) or (DealerLevel='T1'  or DealerLevel='LS')) and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.ToString("yyyy-MM-dd")}' group by UPN";
                                    var month2Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month2).ToList();
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.ItemNum = itemNum;
                                    forecastActualDto.GroupBy = itemDto.Series;
                                    forecastActualDto.StockTurnOver = itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand == 0 ? 0 : decimal.Ceiling((itemDto.GKInv + itemDto.HWInv) / (itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand));
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Qty;
                                    }
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Qty;
                                    }
                                    forecastActualDto.M1Real = month1Qty.Sum(a => a.Qty);
                                    forecastActualDto.M2Real = month2Qty.Sum(a => a.Qty);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            }
                        }
                        else
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2)).Result;
                            var itemList = _itemMaster.Select(a => a.ItemType == "RS50" && a.Domain == factory_id).Select(a => a.ItemNum).Distinct().ToList();
                            string month1 = $"Select B.CfnERPCode AS ItemNum,A.UPN,SUM(A.Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode where ((DealerLevel='T2' and (ParentDealerCode='RQ000005' or ParentDealerCode='RQ000002')) or (DealerLevel='T1'  or DealerLevel='LS')) and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                            var month1Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month1).ToList();
                            string month2 = $"Select B.CfnERPCode AS ItemNum,A.UPN,SUM(A.Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode where ((DealerLevel='T2' and (ParentDealerCode='RQ000005' or ParentDealerCode='RQ000002')) or (DealerLevel='T1'  or DealerLevel='LS')) and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                            var month2Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month2).ToList();
                            var shipItemList = shipPlan.Select(a => a.SAPItemNumber).Distinct().ToList();
                            var intersection = itemList.Intersect(shipItemList).ToList();
                            intersection.ForEach(x =>
                            {
                                var itemDto = InventoryDto.Find(a => a.ItemNumber == x);
                                if (itemDto != null)
                                {
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.ItemNum = x;
                                    forecastActualDto.GroupBy = itemDto.Series;
                                    forecastActualDto.StockTurnOver = itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand == 0 ? 0 : decimal.Ceiling((itemDto.GKInv + itemDto.HWInv) / (itemDto.GKMonthlyDemand + itemDto.HWMonthlyDemand));
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => a.SAPItemNumber == x && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Find(a => a.SAPItemNumber == x && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Qty;
                                    }
                                    if (shipPlan.Any(a => a.SAPItemNumber == x && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Find(a => a.SAPItemNumber == x && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Qty;
                                    }
                                    forecastActualDto.M1Real = month1Qty.Where(a => a.ItemNum == x).Sum(a => a.Qty);
                                    forecastActualDto.M2Real = month2Qty.Where(a => a.ItemNum == x).Sum(a => a.Qty);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            });
                        }
                        break;
                    default:
                        break;        
                }
            }else
            {
                switch (itemNumRange)
                {
                    case "F":
                        if (!string.IsNullOrEmpty(groupBy))
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => a.ProdRange == groupBy && (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2) && a.DistributionChannel == "瑞奇").Result;
                            var itemList=_itemMaster.Select(a => a.Series == groupBy && a.ItemType == "RS50").Select(b => b.ItemNum).Distinct().ToList();
                            var itemDto = InventoryDto.Where(a => a.Series == groupBy && itemList.Contains(a.ItemNumber)).ToList();
                            if (itemDto != null && itemDto.Count>0)
                            {
                                ForecastActualDto forecastActualDto = new ForecastActualDto();
                                forecastActualDto.GroupBy =groupBy;
                                decimal sumDemand = itemDto.Sum(a => a.GKMonthlyDemand + a.HWMonthlyDemand);
                                decimal sumInv = itemDto.Sum(a => a.GKInv + a.HWInv);
                                forecastActualDto.StockTurnOver = sumDemand == 0 ? 0 : decimal.Ceiling(sumInv / sumDemand);
                                //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                forecastActualDto.M1Plan = 0;
                                forecastActualDto.M2Plan = 0;
                                if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM") && itemList.Contains(a.SAPItemNumber)))
                                {
                                    forecastActualDto.M1Plan = shipPlan.Where(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM") && itemList.Contains(a.SAPItemNumber)).Sum(x=>x.Qty);
                                }
                                if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                {
                                    forecastActualDto.M2Plan = shipPlan.Where(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM") && itemList.Contains(a.SAPItemNumber)).Sum(x=>x.Qty);
                                }
                                forecastActualDto.M1Real = rqShip.Where(a => itemList.Contains(a.ContainerItem) && a.CreateTime >= DateTime.Now.AddMonths(-2) && a.CreateTime < DateTime.Now.AddMonths(-1)).Sum(b => b.QtyToShip.Value);
                                forecastActualDto.M2Real = rqShip.Where(a => itemList.ToList().Contains(a.ContainerItem) && a.CreateTime >= DateTime.Now.AddMonths(-1) && a.CreateTime < DateTime.Now).Sum(b => b.QtyToShip.Value);
                                forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                modelMonthOuts.Add(forecastActualDto);
                            }
                        }
                        else
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2)&& a.DistributionChannel == "瑞奇").Result;
                            var itemSeriesList = _itemMaster.Select(a =>a.ItemType == "RS50");
                            var shipSeriesList = shipPlan.Select(a => a.ProdRange).Distinct().ToList();
                            var ItemSeriesList = itemSeriesList.Select(a => a.Series).Distinct().ToList();
                            var intersection = ItemSeriesList.Intersect(shipSeriesList).ToList();
                            intersection.ForEach(x =>
                            {
                                var itemDto = InventoryDto.Where(a => a.Series == x).ToList();
                                var itemNumList= itemSeriesList.Where(a=>a.Series==x).Select(a=>a.ItemNum).Distinct().ToList();
                                if (itemDto != null && itemDto.Count > 0)
                                {
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.GroupBy =x;
                                    decimal sumDemand = itemDto.Sum(a => a.GKMonthlyDemand + a.HWMonthlyDemand);
                                    decimal sumInv = itemDto.Sum(a => a.GKInv + a.HWInv);
                                    forecastActualDto.StockTurnOver = sumDemand == 0 ? 0 : decimal.Ceiling(sumInv / sumDemand);
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => itemNumList.Contains(a.SAPItemNumber) && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Find(a => itemNumList.Contains(a.SAPItemNumber) && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Qty;
                                    }
                                    if (shipPlan.Any(a => itemNumList.Contains(a.SAPItemNumber) && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Find(a => itemNumList.Contains(a.SAPItemNumber) && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Qty;
                                    }
                                    forecastActualDto.M1Real = rqShip.Where(a => itemNumList.Contains(a.ContainerItem) && a.CreateTime >= DateTime.Now.AddMonths(-2) && a.CreateTime < DateTime.Now.AddMonths(-1)).Sum(b => b.QtyToShip.Value);
                                    forecastActualDto.M2Real = rqShip.Where(a => itemNumList.Contains(a.ContainerItem) && a.CreateTime >= DateTime.Now.AddMonths(-1) && a.CreateTime < DateTime.Now).Sum(b => b.QtyToShip.Value);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            });
                        }
                        break;
                    case "G":
                    case "H":
                        string gkhw = itemNumRange == "G" ? "国科" : "海王";
                        string gkhwcode = itemNumRange == "G" ? "RQ000005" : "RQ000002";
                        if (!string.IsNullOrEmpty(groupBy))
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => a.ProdRange == groupBy && (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2) && a.DistributionChannel == gkhw).Result;
                            var itemList = _itemMaster.Select(a => a.Series == groupBy && a.ItemType == "RS50").Distinct().ToList();
                            var item = _DMS_IN_ITEMMAPPING.Select(a => itemList.Select(a => a.ItemNum).ToList().Contains(a.CfnERPCode));
                            string result = string.Join("','", item.Select(a=>a.CfnCode).Distinct().ToList());
                            if (item != null)
                            {
                                var itemDto = InventoryDto.Where(a =>a.Series==groupBy).ToList();
                                if (itemDto != null && itemDto.Count > 0)
                                {
                                    //表没有主键索引，用仓储查询很慢，改用这种方式
                                    string month1 = $"select B.CfnERPCode AS ItemNum,UPN,SUM(Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode and UPN in('{result}') and DealerLevel='T2' and ParentDealerCode='{gkhwcode}' and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                                    var month1Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month1).ToList();
                                    string month2 = $"select B.CfnERPCode AS ItemNum,UPN,SUM(Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode and UPN in('{result}') and DealerLevel='T2' and ParentDealerCode='{gkhwcode}' and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                                    var month2Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month2).ToList();
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.GroupBy = groupBy;
                                    decimal sumDemand = itemDto.Sum(a => a.GKMonthlyDemand + a.HWMonthlyDemand);
                                    decimal sumInv = itemDto.Sum(a => a.GKInv + a.HWInv);
                                    forecastActualDto.StockTurnOver = sumDemand == 0 ? 0 : decimal.Ceiling(sumInv / sumDemand);
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Qty;
                                    }
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Qty;
                                    }
                                    forecastActualDto.M1Real = month1Qty.Sum(a => a.Qty);
                                    forecastActualDto.M2Real = month2Qty.Sum(a => a.Qty);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            }
                        }
                        else
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2)&& a.DistributionChannel == gkhw).Result;
                            string month1 = $"select B.CfnERPCode AS ItemNum,A.UPN,SUM(A.Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode where DealerLevel='T2' and ParentDealerCode='{gkhwcode}' and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                            var month1Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month1).ToList();
                            string month2 = $"select B.CfnERPCode AS ItemNum,A.UPN,SUM(A.Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode where DealerLevel='T2' and ParentDealerCode='{gkhwcode}' and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.ToString("yyyy-MM-dd")}' group by  B.CfnERPCode,UPN";
                            var month2Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month2).ToList();
                            var itemList = _itemMaster.Select(a =>a.ItemType == "RS50").Distinct().ToList();
                            var item = _DMS_IN_ITEMMAPPING.Select(a => itemList.Select(a => a.ItemNum).ToList().Contains(a.CfnERPCode));
                            string result = string.Join("','", item.Select(a => a.CfnCode).Distinct().ToList());
                            var seriesList=itemList.Select(a => a.Series).Distinct().ToList();
                            seriesList.ForEach(x =>
                            {
                                var itemDto = InventoryDto.Where(a => a.Series == x).ToList();
                                if (itemDto != null && itemDto.Count > 0)
                                {
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.GroupBy = x;
                                    decimal sumDemand = itemDto.Sum(a => a.GKMonthlyDemand + a.HWMonthlyDemand);
                                    decimal sumInv = itemDto.Sum(a => a.GKInv + a.HWInv);
                                    forecastActualDto.StockTurnOver = sumDemand == 0 ? 0 : decimal.Ceiling(sumInv / sumDemand);
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => a.ProdRange == x && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Where(a => a.ProdRange == x && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Sum(q=>q.Qty);
                                    }
                                    if (shipPlan.Any(a => a.SAPItemNumber == x && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Where(a => a.ProdRange == x && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Sum(q => q.Qty);
                                    }
                                    List<string> seriesItemnum = new List<string>();
                                    seriesItemnum.AddRange(itemList.Where(a => a.Series == x).Select(a => a.ItemNum).Distinct());
                                    forecastActualDto.M1Real = month1Qty.Where(a => seriesItemnum.Contains(a.ItemNum)).Sum(a => a.Qty);
                                    forecastActualDto.M2Real = month2Qty.Where(a => seriesItemnum.Contains(a.ItemNum)).Sum(a => a.Qty);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            });
                        }
                        break;
                    case "A":
                        if (!string.IsNullOrEmpty(groupBy))
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => a.ProdRange == groupBy && (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2)).Result;
                            var itemList = _itemMaster.Select(a => a.Series == groupBy && a.ItemType == "RS50").Distinct().ToList();
                            var item = _DMS_IN_ITEMMAPPING.Select(a => itemList.Select(a => a.ItemNum).ToList().Contains(a.CfnERPCode));
                            string result = string.Join("','", item.Select(a => a.CfnCode).Distinct().ToList());
                            if (item != null)
                            {
                                var itemDto = InventoryDto.Where(a => a.Series == groupBy).ToList();
                                if (itemDto != null && itemDto.Count > 0)
                                {
                                    //表没有主键索引，用仓储查询很慢，改用这种方式
                                    string month1 = $"select B.CfnERPCode AS ItemNum,UPN,SUM(Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode and UPN='{item[0].CfnCode}' and ((DealerLevel='T2' and (ParentDealerCode='RQ000005' or ParentDealerCode='RQ000002')) or (DealerLevel='T1'  or DealerLevel='LS')) and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                                    var month1Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month1).ToList();
                                    string month2 = $"select B.CfnERPCode AS ItemNum,UPN,SUM(Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode and UPN='{item[0].CfnCode}' and ((DealerLevel='T2' and (ParentDealerCode='RQ000005' or ParentDealerCode='RQ000002')) or (DealerLevel='T1'  or DealerLevel='LS')) and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                                    var month2Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month2).ToList();
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.GroupBy = groupBy;
                                    decimal sumDemand = itemDto.Sum(a => a.GKMonthlyDemand + a.HWMonthlyDemand);
                                    decimal sumInv = itemDto.Sum(a => a.GKInv + a.HWInv);
                                    forecastActualDto.StockTurnOver = sumDemand == 0 ? 0 : decimal.Ceiling(sumInv / sumDemand);
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Qty;
                                    }
                                    if (shipPlan.Any(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Find(a => a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Qty;
                                    }
                                    forecastActualDto.M1Real = month1Qty.Sum(a => a.Qty);
                                    forecastActualDto.M2Real = month2Qty.Sum(a => a.Qty);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            }
                        }
                        else
                        {
                            var shipPlan = _monthlyShipmentPlan.GetListAsync(a => (a.PlanMonth == strMonth1 || a.PlanMonth == strMonth2)).Result;
                            string month1 = $"select B.CfnERPCode AS ItemNum,A.UPN,SUM(A.Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode where ((DealerLevel='T2' and (ParentDealerCode='RQ000005' or ParentDealerCode='RQ000002')) or (DealerLevel='T1'  or DealerLevel='LS')) and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' group by B.CfnERPCode,UPN";
                            var month1Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month1).ToList();
                            string month2 = $"select B.CfnERPCode AS ItemNum,A.UPN,SUM(A.Qty) AS Qty from [dbo].[DMS_IN_SHIPPINGDETAIL] A Inner join [dbo].[DMS_IN_ITEMMAPPING] B on A.UPN=B.CfnCode where ((DealerLevel='T2' and (ParentDealerCode='RQ000005' or ParentDealerCode='RQ000002')) or (DealerLevel='T1'  or DealerLevel='LS')) and Status !='Revoked' and Status !='Rejected' and ShipmentDate>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd")}' and ShipmentDate<'{DateTime.Now.ToString("yyyy-MM-dd")}' group by  B.CfnERPCode,UPN";
                            var month2Qty = _businessExtRADbContext.DMSShippingDetailDto.FromSqlRaw(month2).ToList();
                            var itemList = _itemMaster.Select(a =>a.ItemType == "RS50").Distinct().ToList();
                            var item = _DMS_IN_ITEMMAPPING.Select(a => itemList.Select(a => a.ItemNum).ToList().Contains(a.CfnERPCode));
                            string result = string.Join("','", item.Select(a => a.CfnCode).Distinct().ToList());
                            var seriesList = itemList.Select(a => a.Series).Distinct().ToList();
                            seriesList.ForEach(x =>
                            {
                                var itemDto = InventoryDto.Where(a => a.Series == x).ToList();
                                if (itemDto != null && itemDto.Count > 0)
                                {
                                    ForecastActualDto forecastActualDto = new ForecastActualDto();
                                    forecastActualDto.GroupBy = x;
                                    decimal sumDemand = itemDto.Sum(a => a.GKMonthlyDemand + a.HWMonthlyDemand);
                                    decimal sumInv = itemDto.Sum(a => a.GKInv + a.HWInv);
                                    forecastActualDto.StockTurnOver = sumDemand == 0 ? 0 : decimal.Ceiling(sumInv / sumDemand);
                                    //月度发货计划找不到可以去月度发货计划历史表按照版本号找，暂不处理
                                    forecastActualDto.M1Plan = 0;
                                    forecastActualDto.M2Plan = 0;
                                    if (shipPlan.Any(a => a.ProdRange == x && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M1Plan = shipPlan.Where(a => a.ProdRange == x && a.PlanMonth == DateTime.Now.AddMonths(-1).ToString("yyyy-MM")).Sum(q => q.Qty);
                                    }
                                    if (shipPlan.Any(a => a.SAPItemNumber == x && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")))
                                    {
                                        forecastActualDto.M2Plan = shipPlan.Where(a => a.ProdRange == x && a.PlanMonth == DateTime.Now.ToString("yyyy-MM")).Sum(q => q.Qty);
                                    }
                                    List<string> seriesItemnum = new List<string>();
                                    seriesItemnum.AddRange(itemList.Where(a => a.Series == x).Select(a => a.ItemNum).Distinct());
                                    forecastActualDto.M1Real = month1Qty.Where(a => seriesItemnum.Contains(a.ItemNum)).Sum(a => a.Qty);
                                    forecastActualDto.M2Real = month2Qty.Where(a => seriesItemnum.Contains(a.ItemNum)).Sum(a => a.Qty);
                                    forecastActualDto.M1Diff = (forecastActualDto.M1Plan == 0 || forecastActualDto.M1Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M1Real / forecastActualDto.M1Plan)}%";
                                    forecastActualDto.M2Diff = (forecastActualDto.M2Plan == 0 || forecastActualDto.M2Real == 0) ? "100%" : $"{decimal.Ceiling(forecastActualDto.M2Real / forecastActualDto.M2Plan)}%";
                                    modelMonthOuts.Add(forecastActualDto);
                                }
                            });
                        }
                        break;
                    default:
                        break;
                }
            }
            return JsonConvert.SerializeObject(modelMonthOuts);
        }

        /// <summary>
        /// 从当前日期往前倒推进出存，最小默认取到2024-01-01，定时任务建议选择每天晚上9点-12点之间
        /// </summary>
        /// <param name="domain"></param>
        /// <returns></returns>
        public string CalcInvDayHist(string domain)
        {
            //取物料已计算进出存的最新日期
            string sqlMaxHistDayTime = $"select t1.* from InvTransHistDay t1 inner join (SELECT max(HistDayTime) as HistDayTime, ItemNum FROM InvTransHistDay where Domain='{domain}' group by ItemNum) t2 on t1.HistDayTime=t2.HistDayTime and t1.ItemNum=t2.ItemNum and t1.Domain='{domain}'";
            var HistMaxTimeDayDto = _businessDbContext.InvTransHistDay.FromSqlRaw(sqlMaxHistDayTime).ToList();

            string sqlHistDetail = $"select A.ItemNum,Loc,LotSerial,BeginBalance,case when a.QtyChange>0 then a.QtyChange else 0 end as QtyChangeAdvance,case when a.QtyChange<0 then abs(a.QtyChange) else 0 end as QtyChangeOut,BeginBalance+QtyChange as BalanceNum,createTime from InvTransHist A where Domain='{domain}' and CreateTime>='2024-01-01'";
            if (HistMaxTimeDayDto.Count>0)
            {
                sqlHistDetail = $"select A.ItemNum,Loc,LotSerial,BeginBalance,case when a.QtyChange>0 then a.QtyChange else 0 end as QtyChangeAdvance,case when a.QtyChange<0 then abs(a.QtyChange) else 0 end as QtyChangeOut,BeginBalance+QtyChange as BalanceNum,createTime from InvTransHist A where Domain='{domain}' and CreateTime>='{HistMaxTimeDayDto[0].HistDayTime}'";
            }
            //取物料进出存明细
            var HistDetailDto = _businessDbContext.InvTransHistDetailDto.FromSqlRaw(sqlHistDetail).ToList();

            //取物料实时库存
            string sql = $"exec [dbo].[pr_WMS_GetMissedLocationQty]";
            var LocationQtyDto = _businessDbContext.LocationQtyDto.FromSqlRaw(sql).ToList();

            ////取出物料库位批次的最新一条记录用于计算期末库存(由于可能出现最大进出存时间一模一样的两笔业务，需要汇总)
            //string lastDetail = $"select a.ItemNum,a.Loc,a.LotSerial,0.00000 AS BeginBalance,Sum(case when a.QtyChange>0 then a.QtyChange else 0 end) as QtyChangeAdvance,Sum(case when a.QtyChange<0 then abs(a.QtyChange) else 0 end) as QtyChangeOut,Sum(BeginBalance+QtyChange) as BalanceNum,GETDATE() AS CreateTime from InvTransHist a inner join(select ItemNum,Loc,LotSerial,MAX(CreateTime) AS CreateTime from InvTransHist  group by ItemNum,Loc,LotSerial) b on a.ItemNum=b.ItemNum and a.Loc=b.Loc and a.LotSerial=b.LotSerial and a.CreateTime=b.CreateTime and a.Domain='{domain}' group by a.ItemNum,a.Loc,a.LotSerial order by a.ItemNum,a.Loc,a.LotSerial";
            //var lastDetailDto = _businessDbContext.InvTransHistDetailDto.FromSqlRaw(lastDetail).ToList();

            //取出所有有进出存记录的物料
            string sqlItemList = $"select distinct ItemNum from InvTransHist where Domain='{domain}' and CreateTime>='2024-01-01' group by ItemNum";
            var QtyChangeDto = _businessDbContext.QtyChangeDto.FromSqlRaw(sqlItemList).ToList();
            List<InvTransHistDay> daysHist = new List<InvTransHistDay>();
            QtyChangeDto?.ForEach(x =>
            {
                DateTime dtMin = new DateTime(2024, 1, 1);
                if (HistMaxTimeDayDto.Any(a => a.ItemNum == x.ItemNum))
                {
                    dtMin = HistMaxTimeDayDto.Find(a => a.ItemNum == x.ItemNum).HistDayTime;
                }
                //物料期末总库存
                var xBalanceNum = LocationQtyDto.Where(a => a.ItemNum == x.ItemNum).Sum(a => a.AvailStatusQty);
                var xMaxDay = DateTime.Now;
                int day = 0;
                while (true)
                {
                    if (xMaxDay.AddDays(day).Date < dtMin)
                        break;

                    InvTransHistDay invTransHistDay = new InvTransHistDay();
                    invTransHistDay.Domain = domain;
                    invTransHistDay.ItemNum = x.ItemNum;
                    invTransHistDay.HistDayTime = xMaxDay.AddDays(day).Date;
                    var xQtyChangeAdvance = HistDetailDto.Where(a => a.ItemNum == x.ItemNum && a.CreateTime.Date == xMaxDay.AddDays(day).Date).Sum(a => a.QtyChangeAdvance);
                    var xQtyChangeOut = HistDetailDto.Where(a => a.ItemNum == x.ItemNum && a.CreateTime.Date == xMaxDay.AddDays(day).Date).Sum(a => a.QtyChangeOut);


                    invTransHistDay.QtyChangeAdvance = xQtyChangeAdvance;
                    invTransHistDay.QtyChangeOut = xQtyChangeOut;
                    if (day == 0)
                    {
                        invTransHistDay.BalanceNum = xBalanceNum;
                        invTransHistDay.BeginBalance = xBalanceNum + xQtyChangeOut - xQtyChangeAdvance;
                    }
                    else
                    {
                        var nextDayQty = daysHist.Find(a => a.HistDayTime == xMaxDay.AddDays(day + 1).Date && a.ItemNum == x.ItemNum);
                        //倒推期初数
                        invTransHistDay.BalanceNum = nextDayQty.BeginBalance;
                        invTransHistDay.BeginBalance = nextDayQty.BeginBalance + xQtyChangeOut - xQtyChangeAdvance;
                    }
                    daysHist.Add(invTransHistDay);
                    day--;
                }
            });
            _businessDbContext.BulkInsert(daysHist);
            return "ok";
        }
    }
}