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.IC;
using Business.VSM;
using EFCore.BulkExtensions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using MongoDB.Driver.Linq;
using Newtonsoft.Json;
using NLog;
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 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 BusinessDbContext _businessDbContext;
        public VSMAppService(
         ISqlRepository<ItemMaster> itemMaster,
         ISqlRepository<InvTransHist> invTransHist,
         IRepository<srm_purchase> srm_purchase,
         IRepository<ic_item> ic_item,
         ISqlRepository<LocationDetail> locationDetail,
        BusinessDbContext businessDbContext
         )
        {
            _itemMaster = itemMaster;
            _invTransHist = invTransHist;
            _srm_purchase = srm_purchase;
            _ic_item = ic_item;
            _LocationDetail=locationDetail;
            _businessDbContext = businessDbContext;
        }
        public string ValueFrequency(string Loc, string BeginDate, string EndDate, string KanBan, string Frequency)
        {
            try
            {
                var itemLoc = _LocationDetail.Select(a => a.Location == Loc && a.Domain == "8010");
                var itemList = itemLoc.Select(a => a.ItemNum).Distinct().ToList();
                ValueFrequencyDto dto = new ValueFrequencyDto();
                DateTime beginDate = Convert.ToDateTime(BeginDate);
                DateTime endDate = Convert.ToDateTime(EndDate);
                var itemTranList=_invTransHist.Select(a => a.Loc == Loc && a.CreateTime >= beginDate && a.CreateTime < endDate && itemList.Contains(a.ItemNum));
                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();

                string sql = @"select a.Loc as Loc,BeginBalance,BeginBalance+QtyChange as BalanceNum,a.ItemNum,
                                             (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,
                                             a.LotSerial,a.createTime from InvTransHist a inner join (select itemnum,LotSerial,Max(CreateTime) AS CreateTime from 
                                             InvTransHist where  loc='1001' and domain='8010' group by itemnum,LotSerial) b on a.ItemNum=b.ItemNum 
                                             and a.CreateTime=b.CreateTime and a.Loc='1001' and a.Domain='8010' and a.LotSerial=b.LotSerial";
                var InvTransHistDto = _businessDbContext.InvTransHistDto.FromSqlRaw(sql).ToList();
                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;
                        }
                        //出库总数
                        var outstock = itemTranList.Where(a => a.ItemNum == item && a.QtyChange < 0).ToList();
                        itemKanBanDto.OutStockQty = Math.Abs(outstock.Sum(a => a.QtyChange).Value);

                        //库存数据取进出存期末数据
                        if(InvTransHistDto.Any(a=>a.ItemNum==item))
                        {
                            itemKanBanDto.Amount = InvTransHistDto.Where(a=>a.ItemNum==item).Sum(a=>a.BalanceNum)* itemKanBanDto.NETPR;
                        }
                        itemKanBanDto.MoveCount = itemTranList.Where(a => a.ItemNum == item && a.QtyChange < 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);
                        if(item== "1000304")
                        {
                            string a = "";
                        }
                    }
                    
                });
                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++)
                {
                    kanBanDtos[i].MovePencent = kanBanDtos[i].MoveCount * 100 / totalmoveCount;
                    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;
                }
                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
                {
                    var itemLoc = _LocationDetail.Select(a => a.Location == Loc && a.Domain == "8010");
                    var itemList = itemLoc.Select(a => a.ItemNum).Distinct().ToList();
                    ValueFrequencyDto dto = new ValueFrequencyDto();
                    DateTime beginDate = Convert.ToDateTime(BeginDate);
                    DateTime endDate = Convert.ToDateTime(EndDate);
                    var itemTranList = _invTransHist.Select(a => a.Loc == Loc && a.CreateTime >= beginDate && a.CreateTime < endDate && itemList.Contains(a.ItemNum));
                    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();

                    string sql = @"select a.Loc as Loc,BeginBalance,BeginBalance+QtyChange as BalanceNum,a.ItemNum,
                                             (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,
                                             a.LotSerial,a.createTime from InvTransHist a inner join (select itemnum,LotSerial,Max(CreateTime) AS CreateTime from 
                                             InvTransHist where  loc='1001' and domain='8010' group by itemnum,LotSerial) b on a.ItemNum=b.ItemNum 
                                             and a.CreateTime=b.CreateTime and a.Loc='1001' and a.Domain='8010' and a.LotSerial=b.LotSerial";
                    var InvTransHistDto = _businessDbContext.InvTransHistDto.FromSqlRaw(sql).ToList();
                    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;
                            }
                            //出库总数
                            var outstock = itemTranList.Where(a => a.ItemNum == item && a.QtyChange < 0).ToList();
                            itemKanBanDto.OutStockQty = Math.Abs(outstock.Sum(a => a.QtyChange).Value);

                            //库存数据取进出存期末数据
                            if (InvTransHistDto.Any(a => a.ItemNum == item))
                            {
                                itemKanBanDto.Amount = InvTransHistDto.Where(a => a.ItemNum == item).Sum(a => a.BalanceNum) * itemKanBanDto.NETPR;
                            }
                            itemKanBanDto.MoveCount = itemTranList.Where(a => a.ItemNum == item && a.QtyChange < 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++)
                    {
                        kanBanDtos[i].MovePencent = kanBanDtos[i].MoveCount * 100 / totalmoveCount;
                        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 = 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;
                }




                return JsonConvert.SerializeObject(dtos);
            }
            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 icitems = _ic_item.FindAsync(a => a.number== ItemNum).Result;
                var srm_purchases = _srm_purchase.GetListAsync(a => icitems.Id==a.icitem_id).Result;
                decimal chartPLT = 14;
                decimal chartKanBan = Convert.ToDecimal(KanBan);
                //供应提前期取货源清单最大值，没有默认14天
                if (srm_purchases.Any(a => a.icitem_id == icitems.Id && a.quota_rate.GetValueOrDefault() > 0))
                {
                    chartPLT = srm_purchases.Where(a => a.number == ItemNum && a.quota_rate.GetValueOrDefault() > 0).Max(a => a.lead_time).Value;
                }
                //string sqldzd = @"select MATNR AS ItemNum,MAx(NETPR)*10 AS NETPR from SupplierStatement group by  MATNR";
                //var dzdList = _businessDbContext.SupplierStatementDto.FromSqlRaw(sqldzd).ToList();

                string sql = string.Format(@"select a.ItemNum,a.Loc,a.LotSerial,BeginBalance,a.QtyChange,(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,
                                                    a.CreateTime from InvTransHist a where  loc='{0}' and domain='8010' and itemnum='{1}' order by CreateTime", Loc,ItemNum);
                var InvTransHistDto = _businessDbContext.InvTransHistDetailDto.FromSqlRaw(sql).ToList();
                DateTime minTime = InvTransHistDto.Min(a => a.CreateTime);
                DateTime maxTime = InvTransHistDto.Max(a => a.CreateTime);
                if(beginDate<minTime)
                {
                    minTime = beginDate;
                }
                if(endDate>maxTime)
                {
                    maxTime = endDate;
                }
                int tranDays = (maxTime - minTime).Days;
                List<ValueFrequencyChartDto> chartDtos = new List<ValueFrequencyChartDto>();
                for(int i = 0; i < tranDays; i++)
                {
                    ValueFrequencyChartDto dtoChart = new ValueFrequencyChartDto();
                    dtoChart.Date = minTime.AddDays(i).ToString("yyyy-MM-dd");
                    dtoChart.InStockQty = InvTransHistDto.Where(a => minTime.AddDays(i).Date==a.CreateTime.Date).Sum(a => a.QtyChangeAdvance);
                    dtoChart.OutStockQty = InvTransHistDto.Where(a => minTime.AddDays(i).Date == a.CreateTime.Date).Sum(a => a.QtyChangeOut);
                    if(i>0)
                    {
                        dtoChart.StockQty = chartDtos[i-1].StockQty + InvTransHistDto.Where(a => minTime.AddDays(i).Date == a.CreateTime.Date).Sum(a => a.QtyChange);
                    }else
                    {
                        dtoChart.StockQty = InvTransHistDto.Where(a => minTime.AddDays(i).Date == a.CreateTime.Date).Sum(a => a.QtyChange);
                    }
                    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;
            }
        }
    }
}