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.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 Newtonsoft.Json;
using NLog;
using Org.BouncyCastle.Asn1.X509;
using System;
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;

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 ISqlRepository<SAPInv> _SAPInv;
        private readonly BusinessDbContext _businessDbContext;
        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,
         ISqlRepository<SAPInv> SAPInv,
        BusinessDbContext businessDbContext
         )
        {
            _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;
            _SAPInv=SAPInv;
            _businessDbContext = businessDbContext;
        }
        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 > 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)
        {
            //if(KanBan=="3")
            //{
            //    string sql = @"select t.[Date] AS [Date],t.DateStockQty AS StockQty,t.DateOutStockQty AS OutStockQty,t.DateInStockQty AS InStockQty,[SuggestStockQty] AS SuggestQty  from [dbo].[3KanBan] 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();
            //    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:[");
            //    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));
            //        sbDaysSuggest.Append(string.Format("{0},", a.SuggestQty));
            //    });
            //    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("]},{name:'推荐日库存数量',type:'line',data:[").
            //       Append(sbDaysSuggest.ToString().TrimEnd(',')).
            //       Append("]}]}");
            //    return sb.ToString();
            //}
            //else if(KanBan=="2")
            //{
            //    string sql = @"select t.[Date] AS [Date],t.DateStockQty AS StockQty,t.DateOutStockQty AS OutStockQty,t.DateInStockQty AS InStockQty,[SuggestStockQty] AS SuggestQty  from [dbo].[2KanBan] 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();
            //    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:[");
            //    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));
            //        sbDaysSuggest.Append(string.Format("{0},", a.SuggestQty));
            //    });
            //    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("]},{name:'推荐日库存数量',type:'line',data:[").
            //       Append(sbDaysSuggest.ToString().TrimEnd(',')).
            //       Append("]}]}");
            //    return sb.ToString();
            //}
            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");
                    dtoChart.InStockQty = InvTransHistDayDto.Where(a => beginDate.AddDays(i).Date== a.HistDayTime.Date).First().QtyChangeAdvance;
                    dtoChart.OutStockQty = InvTransHistDayDto.Where(a => beginDate.AddDays(i).Date == a.HistDayTime.Date).First().QtyChangeOut;
                    dtoChart.StockQty = InvTransHistDayDto.Where(a => beginDate.AddDays(i).Date == a.HistDayTime.Date).First().BalanceNum;
                    dtoChart.SuggestQty =chartKanBan*chartPLT;
                    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("{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: '0',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 CalcInvDayHist(string domain)
        {
            string sql = $"select distinct ItemNum,Min(CreateTime) AS HistMinTime from InvTransHist where Domain='{domain}' group by ItemNum";
            var InvTransHistDto = _businessDbContext.InvTransHistDayDto.FromSqlRaw(sql).ToList();

            string invHistTime = $"select A.ItemNum,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,CONVERT(varchar,a.createTime, 23) AS createTime from InvTransHist A where Domain='{domain}'  group by ItemNum,CONVERT(varchar,a.createTime, 23)";
            var InvTransHistDetailDto = _businessDbContext.InvTransHistDetailDto.FromSqlRaw(invHistTime).ToList();

            string sqlMaxTime = $"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 InvTransHistMaxTimeDayDto = _businessDbContext.InvTransHistDay.FromSqlRaw(sqlMaxTime).ToList();

            string sqlQtyChange = $"select ItemNum,Sum(QtyChange) AS QtyChange from InvTransHist where Domain='{domain}' group by ItemNum";
            var QtyChangeDto = _businessDbContext.QtyChangeDto.FromSqlRaw(sqlQtyChange).ToList();

            string sqlBalanceNum = $"select a.ItemNum,Sum(BeginBalance)+Sum(QtyChange) AS BalanceNum from InvTransHist a inner join (select ItemNum,LotSerial,Max(CreateTime) AS CreateTime from InvTransHist group by ItemNum,LotSerial ) b on a.ItemNum=b.ItemNum and a.LotSerial=b.LotSerial and a.CreateTime=b.CreateTime and Domain='{domain}' group by a.ItemNum";
            var BalanceNumDto = _businessDbContext.BalanceNumDto.FromSqlRaw(sqlBalanceNum).ToList();

            List<InvTransHistDay> daysHist = new List<InvTransHistDay>();
            InvTransHistDto?.ForEach(x => {
                if(InvTransHistMaxTimeDayDto.Any(a=>a.ItemNum==x.ItemNum))
                {
                    var xMaxDay = InvTransHistMaxTimeDayDto.Find(a => a.ItemNum == x.ItemNum).HistDayTime;
                    int day = 1;
                    while(true)
                    {
                        if(xMaxDay.AddDays(day).Date<=DateTime.Now.Date)
                        {
                            InvTransHistDay invTransHistDay = new InvTransHistDay();
                            var daydetail = InvTransHistDetailDto.Find(a => a.ItemNum == x.ItemNum && a.CreateTime== xMaxDay.AddDays(day).Date.ToString("yyyy-MM-dd"));
                            invTransHistDay.Domain = domain;
                            invTransHistDay.ItemNum = x.ItemNum;
                            if(day==1)
                            {
                                invTransHistDay.BeginBalance = InvTransHistMaxTimeDayDto.Find(a => a.ItemNum == x.ItemNum).BalanceNum;
                            }else
                            {
                                invTransHistDay.BeginBalance = daysHist.Find(a => a.ItemNum == x.ItemNum && a.HistDayTime.Date == xMaxDay.AddDays(day - 1).Date).BalanceNum;
                            }
                            invTransHistDay.QtyChangeAdvance = daydetail == null ? 0 : daydetail.QtyChangeAdvance;
                            invTransHistDay.QtyChangeOut = daydetail == null ? 0 : daydetail.QtyChangeOut;
                            invTransHistDay.BalanceNum = invTransHistDay.BeginBalance + invTransHistDay.QtyChangeAdvance - invTransHistDay.QtyChangeOut;
                            invTransHistDay.HistDayTime = xMaxDay.AddDays(day).Date;
                            daysHist.Add(invTransHistDay);
                            day++;
                        }else
                        {
                            break;
                        }
                    }
                }else
                {
                    var xMaxDay = InvTransHistDto.Find(a => a.ItemNum == x.ItemNum).HistMinTime.Value;
                    int day = 0;
                    while (true)
                    {
                        if (xMaxDay.AddDays(day).Date <= DateTime.Now.Date)
                        {
                            InvTransHistDay invTransHistDay = new InvTransHistDay();
                            var daydetail = InvTransHistDetailDto.Find(a => a.ItemNum == x.ItemNum && a.CreateTime == xMaxDay.AddDays(day).Date.ToString("yyyy-MM-dd"));
                            invTransHistDay.Domain = domain;
                            invTransHistDay.ItemNum = x.ItemNum;
                            if (day == 0)
                            {
                                var xBalanceNum = BalanceNumDto.Find(b => b.ItemNum == x.ItemNum);
                                var xQtyChangeDto = QtyChangeDto.Find(b => b.ItemNum == x.ItemNum);
                                if (xBalanceNum != null && xQtyChangeDto != null)
                                {
                                    invTransHistDay.BeginBalance = xBalanceNum.BalanceNum + xQtyChangeDto.QtyChange;
                                }
                                else
                                {
                                    invTransHistDay.BeginBalance = 0;
                                }
                            }
                            else
                            {
                                var dayPredetail = daysHist.Find(a => a.ItemNum == x.ItemNum && a.HistDayTime.Date == xMaxDay.AddDays(day - 1).Date);
                                invTransHistDay.BeginBalance = dayPredetail == null ? 0 : dayPredetail.BalanceNum;
                            }
                            invTransHistDay.QtyChangeAdvance = daydetail == null ? 0 : daydetail.QtyChangeAdvance;
                            invTransHistDay.QtyChangeOut = daydetail == null ? 0 : daydetail.QtyChangeOut;
                            invTransHistDay.BalanceNum = invTransHistDay.BeginBalance + invTransHistDay.QtyChangeAdvance - invTransHistDay.QtyChangeOut;
                            invTransHistDay.HistDayTime = xMaxDay.AddDays(day).Date;
                            daysHist.Add(invTransHistDay);
                            day++;
                        }
                        else
                        {
                            break;
                        }
                    }
                }
            });
            _businessDbContext.BulkInsert(daysHist);
            return "ok";
        }
    }
}