OASystemAPI/OASystem/OASystem.Infrastructure/Repositories/PersonnelModule/GoodsRepository.cs

using Aspose.Cells;
using Aspose.Cells.Drawing;
using Aspose.Words.Drawing;
using AutoMapper;
using EyeSoft.IO;
using EyeSoft.Reflection;
using Newtonsoft.Json;
using NPOI.OpenXmlFormats.Vml;
using NPOI.SS.UserModel;
using NPOI.Util;
using OASystem.Domain.Dtos.PersonnelModule;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.PersonnelModule;
using OASystem.Domain.ViewModels.PersonnelModule;
using OASystem.Infrastructure.Tools;
using System.Drawing;
using System.IO;
using System.Security.Policy;

namespace OASystem.Infrastructure.Repositories.PersonnelModule
{
    /// <summary>
    /// 物品进销存 
    /// 仓储
    /// </summary>
    public class GoodsRepository : BaseRepository<Pm_GoodsInfo, GoodsInfoView>
    {
        private readonly IMapper _mapper;
        private JsonView _jv;
        private string _url;
        private string _excelPath;
        public GoodsRepository(SqlSugarClient sqlSugar, IMapper mapper) : base(sqlSugar)
        {
            _mapper = mapper;
            _jv = new JsonView() { Code = StatusCodes.Status400BadRequest, Msg = "操作失败!" };
            _url = AppSettingsHelper.Get("ExcelBaseUrl");
            _excelPath = $"{AppSettingsHelper.Get("ExcelBasePath")}";
            if (!Directory.Exists(_excelPath))
            {
                Directory.CreateDirectory(_excelPath);
            }
        }

        /// <summary>
        /// 基础数据
        /// </summary>
        /// <returns></returns>
        public async Task<JsonView> InitDataSource()
        {

            var typeData = await _sqlSugar.Queryable<GoodsTypeView>()
                                          .Includes(x => x.SubTypeItems.Where(z => z.IsDel == 0)
                                                                       //.Select(z => new {
                                                                       //     z.Id,
                                                                       //     z.STid,
                                                                       //     z.Name
                                                                       //})
                                                                       .ToList())
                                          .Where(x => x.IsDel == 0 &&
                                                      x.Remark.Equals("GoodsType"))
                                          //.Select(x => new { 
                                          //        x.Id,
                                          //        x.Name,
                                          //        x.SubTypeItems
                                          //})
                                          .ToListAsync();

            var groupData = await _sqlSugar.Queryable<Grp_DelegationInfo>()
                                           .Where(x => x.IsDel == 0)
                                           .Select(x => new
                                           {
                                               id = x.Id,
                                               groupName = x.TeamName
                                           })
                                           .OrderByDescending(x => x.id)
                                           .ToListAsync();

            groupData.Insert(0, new { id = 0, groupName = "其他物资(公司内部物资)" });
            groupData.Insert(0, new { id = -1, groupName = "拜访客户所使用的物资" });

            var userData = await _sqlSugar.Queryable<Sys_Users>()
                .Where(x => x.IsDel == 0)
                .Select(x => new
                {
                    x.Id,
                    UserName = x.CnName,
                })
                .ToListAsync();

            _jv.Code = StatusCodes.Status200OK;
            _jv.Data = new { goodsTypeData = typeData, groupNameData = groupData, userNameData = userData };
            _jv.Msg = $"操作成功";
            return _jv;
        }

        /// <summary>
        /// 物品列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsList(GoodsListDto dto)
        {
            var ids = new List<int>();
            if (!string.IsNullOrEmpty(dto.TypeIds))
            {
                var strArray = dto.TypeIds.Split(',');
                foreach (var str in strArray)
                {
                    if (int.TryParse(str, out int id))
                    {
                        ids.Add(id);
                    }
                }
            }

            RefAsync<int> total = 0;

            var data = await _sqlSugar.Queryable<GoodsListView>()
                .Includes(glv => glv.Receives.Where(z1 => z1.IsDel == 0 && z1.AuditStatus == GoodsAuditEnum.Pending).ToList())
                .Includes(glv => glv.TypeData)
                .Includes(glv => glv.UserData)
                .LeftJoin<Sys_SetData>((glv, sd) => glv.Type == sd.Id)
                .LeftJoin<Sys_Users>((glv, sd, u) => glv.LastUpdateUserId == u.Id)
                .Where(glv => glv.IsDel == 0)
                .WhereIF(ids.Count > 0, glv => ids.Contains(glv.Type))
                .WhereIF(!string.IsNullOrEmpty(dto.GoodsName), glv => glv.Name.Contains(dto.GoodsName))
                .OrderByDescending(glv => glv.LastUpdateTime)
                .ToPageListAsync(dto.PageIndex, dto.PageSize, total);


            var view = data.Select(x => new
            {
                x.Id,
                x.Name,
                x.Type,
                TypeName = x.TypeData?.Name ?? string.Empty,
                LastUpdateUserName = x.UserData?.CnName ?? string.Empty,
                x.LastUpdateTime,
                StockQuantity = x.StockQuantity - x.WaitAuditQuantity,
                x.Unit,
                x.StockQuantityLabel,
                x.Remark
            }).ToList();

            _jv.Code = StatusCodes.Status200OK;
            _jv.Data = view;
            _jv.Count = total;
            _jv.Msg = $"操作成功";
            return _jv;
        }

        /// <summary>
        /// 物品Info
        /// </summary>
        /// <param name="portType"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsInfo(int portType, int id)
        {
            var data = await _sqlSugar.Queryable<Pm_GoodsInfo>()
                                      .LeftJoin<Sys_SetData>((gi, sd) => gi.Type == sd.Id)
                                      .LeftJoin<Sys_Users>((gi, sd, u1) => gi.LastUpdateUserId == u1.Id)
                                      .LeftJoin<Sys_Users>((gi, sd, u1, u2) => gi.CreateUserId == u2.Id)
                                      .Where((gi, sd, u1, u2) => gi.IsDel == 0 && gi.Id == id)
                                      .Select((gi, sd, u1, u2) => new
                                      {
                                          gi.Id,
                                          gi.Name,
                                          ParentType = sd.STid,
                                          gi.Type,
                                          TypeName = sd.Name,
                                          gi.SQ_Total,
                                          gi.OQ_Total,
                                          gi.PriceTotal,
                                          gi.StockQuantity,
                                          gi.Unit,
                                          gi.Remark,
                                          LastUpdateUserName = u1.CnName,
                                          gi.LastUpdateTime,
                                          CreateUserName = u2.CnName,
                                          gi.CreateTime,
                                      })
                                      .FirstAsync();

            _jv.Code = StatusCodes.Status200OK;
            _jv.Data = data;
            _jv.Msg = $"操作成功";
            return _jv;
        }

        /// <summary>
        /// 物品 OP(Create Or Edit)
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="currUserId"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsOp(GoodsOpDto dto, int currUserId)
        {
            var info = new Pm_GoodsInfo()
            {
                Id = dto.Id,
                Name = dto.Name,
                Type = dto.Type,
                SQ_Total = 0,
                OQ_Total = 0,
                PriceTotal = 0,
                StockQuantity = 0,
                Unit = dto.Unit,
                Remark = dto.Remark,
                LastUpdateUserId = currUserId,
                LastUpdateTime = DateTime.Now,
                CreateUserId = currUserId
            };

            if (dto.Id > 0) //Edit
            {
                var upd = await _sqlSugar.Updateable(info)
                                         .UpdateColumns(x => new
                                         {
                                             x.Name,
                                             x.Type,
                                             x.Unit,
                                             x.Remark,
                                             x.LastUpdateUserId,
                                             x.LastUpdateTime,
                                         })
                                         .ExecuteCommandAsync();
                if (upd > 0)
                {
                    _jv.Msg = $"修改成功!";
                    _jv.Code = StatusCodes.Status200OK;
                    return _jv;
                }
            }
            else if (dto.Id < 1) //添加
            {
                var selectInfo = await _sqlSugar.Queryable<Pm_GoodsInfo>().FirstAsync(x => x.Name.Equals(info.Name));
                if (selectInfo != null)
                {
                    _jv.Msg = $"“{info.Name}”该物品已存在,请勿重新添加!";
                    return _jv;
                }

                var add = await _sqlSugar.Insertable(info).ExecuteCommandAsync();
                if (add > 0)
                {
                    _jv.Msg = $"添加成功!";
                    _jv.Code = StatusCodes.Status200OK;
                    return _jv;
                }
            }
            return _jv;
        }

        /// <summary>
        /// 物品 Del
        /// </summary>
        /// <param name="id"></param>
        /// <param name="currUserId"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsDel(int id, int currUserId)
        {
            _sqlSugar.BeginTran();

            var goods = await _sqlSugar.Updateable<Pm_GoodsInfo>()
                                       .SetColumns(x => new Pm_GoodsInfo()
                                       {
                                           IsDel = 1,
                                           DeleteUserId = currUserId,
                                           DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                                       })
                                       .Where(x => x.Id == id)
                                       .ExecuteCommandAsync();
            if (goods < 1)
            {
                _sqlSugar.RollbackTran();
                _jv.Msg = $"操作失败";
                return _jv;
            }

            var goodsStorage = await _sqlSugar.Updateable<Pm_GoodsStorage>()
                                              .SetColumns(x => new Pm_GoodsStorage()
                                              {
                                                  IsDel = 1,
                                                  DeleteUserId = currUserId,
                                                  DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                                              })
                                              .Where(x => x.Id == id)
                                              .ExecuteCommandAsync();

            var goodsReceive = await _sqlSugar.Updateable<Pm_GoodsReceive>()
                                              .SetColumns(x => new Pm_GoodsReceive()
                                              {
                                                  IsDel = 1,
                                                  DeleteUserId = currUserId,
                                                  DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                                              })
                                              .Where(x => x.Id == id)
                                              .ExecuteCommandAsync();
            _sqlSugar.CommitTran();
            _jv.Code = StatusCodes.Status200OK;
            _jv.Msg = $"操作成功!";
            return _jv;
        }

        /// <summary>
        /// 物品入库列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsStorageList(GoodsStorageListDto dto)
        {
            RefAsync<int> total = 0;
            var data = await _sqlSugar.Queryable<Pm_GoodsStorage>()
                                      .LeftJoin<Pm_GoodsInfo>((gs, gi) => gs.GoodsId == gi.Id)
                                      .LeftJoin<Sys_Users>((gs, gi, u) => gs.CreateUserId == u.Id)
                                      .LeftJoin<Sys_Users>((gs, gi, u, u1) => gs.StorageUserId == u1.Id)
                                      .Where((gs, gi, u, u1) => gs.IsDel == 0)
                                      .WhereIF(dto.GoodsId > 0, (gs, gi, u, u1) => gs.GoodsId == dto.GoodsId)
                                      .WhereIF(!string.IsNullOrEmpty(dto.BatchNo), (gs, gi, u, u1) => gs.BatchNo.Contains(dto.BatchNo))
                                      .Select((gs, gi, u, u1) => new
                                      {
                                          gs.Id,
                                          gs.GoodsId,
                                          gs.BatchNo,
                                          GoodsName = gi.Name,
                                          gs.Quantity,
                                          gs.UnitPrice,
                                          gs.TotalPrice,
                                          gs.SupplierName,
                                          gs.SupplierTel,
                                          gs.SupplierAddress,
                                          gs.SupplierSource,
                                          StorageUserName = u1.CnName,
                                          gs.StorageTime,
                                          CreateUserName = u.CnName,
                                          gs.CreateTime,
                                      })
                                      .OrderByDescending(gs => gs.CreateTime)
                                      .ToPageListAsync(dto.PageIndex, dto.PageSize, total);

            _jv.Code = StatusCodes.Status200OK;
            _jv.Data = data;
            _jv.Count = total;
            _jv.Msg = $"操作成功";
            return _jv;
        }

        /// <summary>
        /// 物品入库详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsStorageInfo(int portType, int id)
        {
            var data = await _sqlSugar.Queryable<Pm_GoodsStorage>()
                                      .LeftJoin<Pm_GoodsInfo>((gs, gi) => gs.GoodsId == gi.Id)
                                      .LeftJoin<Sys_Users>((gs, gi, u) => gs.CreateUserId == u.Id)
                                      .LeftJoin<Sys_Users>((gs, gi, u, u1) => gs.StorageUserId == u1.Id)
                                      .Where((gs, gi, u) => gs.IsDel == 0)
                                      .WhereIF(id > 0, (gs, gi, u) => gs.Id == id)
                                      .Select((gs, gi, u, u1) => new
                                      {
                                          gs.Id,
                                          gs.GoodsId,
                                          GoodsName = gi.Name,
                                          gs.Quantity,
                                          gs.UnitPrice,
                                          gs.TotalPrice,
                                          gs.SupplierName,
                                          gs.SupplierTel,
                                          gs.SupplierAddress,
                                          gs.SupplierSource,
                                          gs.ReceiveQuantity,
                                          gs.StorageUserId,
                                          StorageUser = u1.CnName,
                                          gs.StorageTime,
                                          CreateUserName = u.CnName,
                                          gs.CreateUserId,
                                          gs.CreateTime,
                                          gs.Remark
                                      })
                                      .FirstAsync();

            _jv.Msg = $"操作成功!";
            _jv.Code = StatusCodes.Status200OK;
            _jv.Data = data;
            return _jv;
        }

        /// <summary>
        /// 物品入库 操作(Create Or Edit)
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="currUserId"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsStorageOp(GoodsStorageOpDto dto, int currUserId)
        {
            var info = _mapper.Map<Pm_GoodsStorage>(dto);
            info.CreateUserId = currUserId;
            info.BatchNo = DateTime.Now.ToString("yyyyMMddHHmmssfff");

            decimal editAgoQuantity = 0.00M,
                    editAgoTotalPrice = 0.00M;

            _sqlSugar.BeginTran();
            if (info.Id > 0) //修改
            {
                var selectInfo = await _sqlSugar.Queryable<Pm_GoodsStorage>()
                                                .Where(x => x.Id == dto.Id)
                                                .FirstAsync();
                editAgoQuantity = selectInfo.Quantity;
                editAgoTotalPrice = selectInfo.TotalPrice;

                var storageEdit = await _sqlSugar.Updateable(info)
                                                .UpdateColumns(x => new
                                                {
                                                    x.Quantity,
                                                    x.UnitPrice,
                                                    x.TotalPrice,
                                                    x.SupplierName,
                                                    x.SupplierTel,
                                                    x.SupplierAddress,
                                                    x.SupplierSource,
                                                    x.StorageUserId,
                                                    x.StorageTime
                                                })
                                                .Where(x => x.Id == dto.Id)
                                                .ExecuteCommandAsync();
                if (storageEdit < 1)
                {
                    _sqlSugar.RollbackTran();
                    _jv.Msg = $"修改失败!";
                    return _jv;
                }
            }
            else if (info.Id < 1) //添加
            {
                var storageAdd = await _sqlSugar.Insertable(info).ExecuteCommandAsync();
                if (storageAdd < 1)
                {
                    _sqlSugar.RollbackTran();
                    _jv.Msg = $"添加失败!";
                    return _jv;
                }
            }

            var goodsInfo = await _sqlSugar.Queryable<Pm_GoodsInfo>().FirstAsync(x => x.Id == info.GoodsId);
            goodsInfo.SQ_Total = goodsInfo.SQ_Total - editAgoQuantity + info.Quantity;
            goodsInfo.StockQuantity = goodsInfo.StockQuantity - editAgoQuantity + info.Quantity;
            goodsInfo.PriceTotal = goodsInfo.PriceTotal - editAgoTotalPrice + info.TotalPrice;
            goodsInfo.LastUpdateUserId = currUserId;
            goodsInfo.LastUpdateTime = DateTime.Now;

            var goodsEdit = await _sqlSugar.Updateable(goodsInfo)
                                           .UpdateColumns(x => new
                                           {
                                               x.SQ_Total,
                                               x.StockQuantity,
                                               x.PriceTotal,
                                               x.LastUpdateUserId,
                                               x.LastUpdateTime,
                                           })
                                           .Where(x => x.Id == info.GoodsId)
                                           .ExecuteCommandAsync();
            if (goodsEdit > 0)
            {
                _sqlSugar.CommitTran();
                _jv.Msg = $"操作成功!";
                _jv.Code = StatusCodes.Status200OK;
                return _jv;
            }

            _sqlSugar.RollbackTran();
            _jv.Msg = $"操作失败!";
            return _jv;
        }

        /// <summary>
        /// 物品入库 Del
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsStorageDel(int id, int userId)
        {
            var storageInfo = await _sqlSugar.Queryable<Pm_GoodsStorage>()
                                             .Where(x => x.Id == id)
                                             .FirstAsync();
            if (storageInfo == null) return _jv;

            decimal delAgoQuantity = storageInfo.Quantity,
                    delAgoTotalPrice = storageInfo.TotalPrice;
            var goodsId = storageInfo.GoodsId;

            _sqlSugar.BeginTran();

            var storageDel = await _sqlSugar.Updateable<Pm_GoodsStorage>()
                                            .SetColumns(x => new Pm_GoodsStorage
                                            {
                                                DeleteUserId = userId,
                                                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                                IsDel = 1
                                            })
                                            .Where(x => x.Id == id)
                                            .ExecuteCommandAsync();
            if (storageDel < 1)
            {
                _sqlSugar.RollbackTran();
                _jv.Msg = $"操作失败!";
                return _jv;
            }

            var goodsInfo = await _sqlSugar.Queryable<Pm_GoodsInfo>().FirstAsync(x => x.Id == goodsId);
            goodsInfo.SQ_Total = goodsInfo.SQ_Total - delAgoQuantity;
            goodsInfo.StockQuantity = goodsInfo.StockQuantity - delAgoQuantity;
            goodsInfo.PriceTotal = goodsInfo.PriceTotal - delAgoTotalPrice;
            goodsInfo.LastUpdateUserId = userId;
            goodsInfo.LastUpdateTime = DateTime.Now;

            var goodsEdit = await _sqlSugar.Updateable(goodsInfo)
                                           .UpdateColumns(x => new
                                           {
                                               x.SQ_Total,
                                               x.StockQuantity,
                                               x.PriceTotal,
                                               x.LastUpdateUserId,
                                               x.LastUpdateTime,
                                           })
                                           .Where(x => x.Id == goodsId)
                                           .ExecuteCommandAsync();
            if (goodsEdit > 0)
            {
                _sqlSugar.CommitTran();
                _jv.Msg = $"操作成功!";
                _jv.Code = StatusCodes.Status200OK;
                return _jv;
            }

            _sqlSugar.RollbackTran();
            _jv.Msg = $"操作失败!";
            return _jv;
        }

        /// <summary>
        /// 物品入库
        /// excelDownload
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsStorageExcelDownload()
        {
            var fileName = $"物资入库{Guid.NewGuid()}.xlsx";

            var excelTempPath = $"{_excelPath}Template/物资入库Temp.xlsx";
            if (!File.Exists(excelTempPath))
            {
                _jv.Code = StatusCodes.Status204NoContent;
                _jv.Msg = $"该模板文件不存在!";
                return _jv;
            }

            _url = $"{_url}Office/Excel/GoodsFiles/";
            _excelPath = $"{_excelPath}GoodsFiles";
            if (!Directory.Exists(_excelPath))
            {
                Directory.CreateDirectory(_excelPath);
            }

            //入库记录
            var storageData = await _sqlSugar.Queryable<Pm_GoodsStorage>()
                .LeftJoin<Sys_Users>((gs, su) => gs.StorageUserId == su.Id)
                .Where((gs, su) => gs.IsDel == 0)
                .Select((gs, su) => new
                {
                    gs.GoodsId,
                    gs.Quantity,
                    su.CnName,
                    gs.StorageTime
                })
                .ToListAsync();

            //出库记录
            var receiveData = await _sqlSugar.Queryable<Pm_GoodsReceive>()
                .LeftJoin<Sys_Users>((gr, su) => gr.CreateUserId == su.Id)
                .Where((gr, su) => gr.IsDel == 0)
                .Select((gr, su) => new
                {
                    gr.GoodsId,
                    gr.Quantity,
                    su.CnName,
                    gr.CreateTime,
                    gr.Reason
                })
                .ToListAsync();

            var data = await _sqlSugar.Queryable<Pm_GoodsInfo>()
                .Where(gi => gi.IsDel == 0)
                .Select(gi => new GoodsStorageExcelDownloadView
                {
                    Id = gi.Id,
                    Name = gi.Name,
                    SQ_Total = gi.SQ_Total,
                    OQ_Total = gi.OQ_Total,
                    StockQuantity = gi.StockQuantity,
                    Unit = gi.Unit
                })
                .ToListAsync();

            foreach (var item in data)
            {
                var storageData1 = storageData.Where(x => x.GoodsId == item.Id).ToList();
                if (storageData1.Any())
                {
                    item.SQ_Total1 = storageData1.Sum(x => x.Quantity);
                    item.SQ_Total -= item.SQ_Total1;
                    item.StorageLabel = string.Join("\r\n", storageData1.Select(x => "数量：【" + x.Quantity.ToString("#0.00") + "】  入库人：【" + x.CnName + "】  入库时间：【" + x.StorageTime + "】").ToList());
                }

                var receiveData1 = receiveData.Where(x => x.GoodsId == item.Id).ToList();
                if (receiveData1.Any())
                {
                    item.ReceiveLabel = string.Join("\r\n", receiveData1.Select(x => "数量：【" + x.Quantity.ToString("#0.00") + "】  申请人：【" + x.CnName + "】  申请时间：【" + x.CreateTime.ToString("yyyy-MM-dd HH:mm:ss") + "】  原因：【" + x.Reason + "】").ToList());
                }
                
            }

            //载入模板
            WorkbookDesigner designer = new WorkbookDesigner();
            designer.Workbook = new Workbook(excelTempPath);

            designer.SetDataSource("Export", data);

            designer.Process();

            #region 渲染Cell批注
            var sheet = designer.Workbook.Worksheets[0];
            for (int i = 0; i < data.Count; i++)
            {
                string storageComment = $"C{i + 2}",
                       receiveComment = $"D{i + 2}",
                       storageCommentText = data[i].StorageLabel,
                       receiveCommentText = data[i].ReceiveLabel;
                if (!string.IsNullOrEmpty(storageCommentText))
                {
                    int storageIndex = sheet.Comments.Add(storageComment);
                    Aspose.Cells.Comment comment = sheet.Comments[storageIndex];
                    comment.Note = storageCommentText;
                    comment.Width = 500;
                    comment.Height = 200;
                    //comment.Font.Color = Color.Red;
                }
                if (!string.IsNullOrEmpty(receiveCommentText))
                {
                    int receiveIndex = sheet.Comments.Add(receiveComment);
                    Aspose.Cells.Comment comment = sheet.Comments[receiveIndex];
                    comment.Note = receiveCommentText;
                    comment.Width = 800;
                    comment.Height = 200;
                    //comment.Font.Color = Color.Red;
                }
            }

            #endregion


            string serverPath = $"{_url}{fileName}";
            designer.Workbook.Save($"{_excelPath}/{fileName}");

            _jv.Code = StatusCodes.Status200OK;
            _jv.Data = new { url = serverPath };
            _jv.Msg = $"操作成功";
            return _jv;
        }

        /// <summary>
        /// 物品领用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsReceiveList(GoodsReceiveListDTO dto)
        {
            //参数处理
            int[] typeLabel = Array.Empty<int>(),
                  userLabel = Array.Empty<int>(),
                  auditLabel = Array.Empty<int>(),
                  groupLabel = Array.Empty<int>();
            if (!string.IsNullOrEmpty(dto.TypeLabel))
            {
                typeLabel = dto.TypeLabel
                    .Split(',')
                    .Select(x =>
                    {
                        if (int.TryParse(x, out var id)) return id;
                        return id;
                    })
                    .ToArray();
            }
            if (!string.IsNullOrEmpty(dto.UserLabel))
            {
                userLabel = dto.UserLabel
                    .Split(',')
                    .Select(x =>
                    {
                        if (int.TryParse(x, out var id)) return id;
                        return id;
                    })
                    .ToArray();
            }
            if (!string.IsNullOrEmpty(dto.AuditLabel))
            {
                auditLabel = dto.AuditLabel
                    .Split(',')
                    .Select(x =>
                    {
                        if (int.TryParse(x, out var id)) return id;
                        return id;
                    })
                    .ToArray();
            }
            if (!string.IsNullOrEmpty(dto.GroupLabel))
            {
                groupLabel = dto.GroupLabel
                    .Split(',')
                    .Select(x =>
                    {
                        if (int.TryParse(x, out var id)) return id;
                        return id;
                    })
                    .ToArray();
            }

            //物品ID和物品名称只能传一个
            if (dto.GoodsId > 0) dto.GoodsName = string.Empty;
            if (!string.IsNullOrEmpty(dto.GoodsName)) dto.GoodsId = 0;

            var beginBool = DateTime.TryParse(!string.IsNullOrEmpty(dto.BeginDt) ? $"{dto.BeginDt} 00:00:00" : string.Empty, out var begin);
            var endBool = DateTime.TryParse(!string.IsNullOrEmpty(dto.EndDt) ? $"{dto.EndDt} 00:00:00" : string.Empty, out var end);

            RefAsync<int> total = 0;
            var data = _sqlSugar.Queryable<Pm_GoodsReceive>()
                .LeftJoin<Pm_GoodsInfo>((gr, gi) => gr.GoodsId == gi.Id)
                .LeftJoin<Sys_SetData>((gr, gi, sd) => gi.Type == sd.Id)
                .LeftJoin<Sys_Users>((gr, gi, sd, u1) => gr.AuditUserId == u1.Id)
                .LeftJoin<Sys_Users>((gr, gi, sd, u1, u2) => gr.CreateUserId == u2.Id)
                .LeftJoin<Grp_DelegationInfo>((gr, gi, sd, u1, u2, di) => gr.GroupId == di.Id)
                .Where((gr, gi, sd, u1, u2, di) => gr.IsDel == 0)
                .WhereIF(dto.GoodsId > 0, (gr, gi, sd, u1, u2, di) => gr.GoodsId == dto.GoodsId)
                .WhereIF(!string.IsNullOrEmpty(dto.GoodsName), (gr, gi, sd, u1, u2, di) => gi.Name.Contains(dto.GoodsName))
                .WhereIF(auditLabel.Length > 0, (gr, gi, sd, u1, u2, di) => auditLabel.Contains((int)gr.AuditStatus))
                .WhereIF(typeLabel.Length > 0, (gr, gi, sd, u1, u2, di) => typeLabel.Contains(gi.Type))
                .WhereIF(userLabel.Length > 0, (gr, gi, sd, u1, u2, di) => userLabel.Contains(gr.CreateUserId))
                .WhereIF(groupLabel.Length > 0, (gr, gi, sd, u1, u2, di) => groupLabel.Contains(gr.GroupId))
                .WhereIF(beginBool && endBool, (gr, gi, sd, u1, u2, di) => gr.CreateTime >= begin && gr.CreateTime <= end)
                .Select((gr, gi, sd, u1, u2, di) => new GoodsReceiveListMobileView
                {
                    Id = gr.Id,
                    GroupId = gr.GroupId,
                    GroupName = di.TeamName,
                    GoodsId = gr.GoodsId,
                    GoodsName = gi.Name,
                    GoodsType = sd.Name,
                    Quantity = gr.Quantity,
                    Unit = gi.Unit,
                    Reason = gr.Reason,
                    Remark = gr.Remark,
                    AuditStatus = gr.AuditStatus,
                    //AuditStatusText = gr.AuditStatus.GetEnumDescription(),
                    AuditUserId = gr.AuditUserId,
                    AuditUserName = u1.CnName,
                    AuditTime = gr.AuditTime,
                    CreateUserName = u2.CnName,
                    CreateTime = gr.CreateTime
                })
                .OrderByDescending(gr => gr.CreateTime);
                                     

            //excel导出
            if (dto.IsExcelDownload)
            {
                var fileName = $"物资领用{Guid.NewGuid()}.xlsx";

                var excelTempPath = $"{_excelPath}Template/物资领用Temp.xlsx";
                if (!File.Exists(excelTempPath))
                {
                    _jv.Code = StatusCodes.Status204NoContent;
                    _jv.Msg = $"该模板文件不存在!";
                    return _jv;
                }

                _url = $"{_url}Office/Excel/GoodsFiles/";
                _excelPath = $"{_excelPath}GoodsFiles";
                if (!Directory.Exists(_excelPath))
                {
                    Directory.CreateDirectory(_excelPath);
                }

                //载入模板
                WorkbookDesigner designer = new WorkbookDesigner();
                designer.Workbook = new Workbook(excelTempPath);

                var tableData = await data.ToListAsync();
                designer.SetDataSource("Export", tableData);

                designer.Process();

                string serverPath = $"{_url}{fileName}";
                designer.Workbook.Save($"{_excelPath}/{fileName}");

                _jv.Code = StatusCodes.Status200OK;
                _jv.Data = new { url = serverPath };
                _jv.Msg = $"操作成功";
                return _jv;
            }
            
            //返回分页数据
            var view = await data.ToPageListAsync(dto.PageIndex, dto.PageSize, total);
            if (dto.PortType == 2 || dto.PortType == 3)
            {
                _jv.Data = view;
            }
            else if (dto.PortType == 1)
            {
                _jv.Data = _mapper.Map<List<GoodsReceiveListView>>(view);
            }

            _jv.Code = StatusCodes.Status200OK;
            _jv.Count = total;
            _jv.Msg = $"操作成功";
            return _jv;
        }

        /// <summary>
        /// 物品领用详情
        /// </summary>
        /// <param name="portType"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsReceiveInfo(int portType, int id)
        {
            var data = await _sqlSugar.Queryable<Pm_GoodsReceive>()
                                      .LeftJoin<Pm_GoodsInfo>((gr, gi) => gr.GoodsId == gi.Id)
                                      .LeftJoin<Sys_Users>((gr, gi, u1) => gr.AuditUserId == u1.Id)
                                      .LeftJoin<Sys_Users>((gr, gi, u1, u2) => gr.CreateUserId == u2.Id)
                                      .LeftJoin<Grp_DelegationInfo>((gr, gi, u1, u2,di) => gr.GroupId == di.Id)
                                      .Where((gr, gi, u1, u2, di) => gr.IsDel == 0)
                                      .WhereIF(id > 0, (gr, gi, u1, u2, di) => gr.Id == id)
                                      .Select((gr, gi, u1, u2, di) => new GoodsReceiveInfoMobileView
                                      {
                                          Id = gr.Id,
                                          GroupId = gr.GroupId,
                                          GroupName = di.TeamName,
                                          GoodsId = gr.GoodsId,
                                          GoodsName = gi.Name,
                                          Quantity = gr.Quantity,
                                          Reason = gr.Reason,
                                          Remark = gr.Remark,
                                          GoodsStorageInfo = gr.GoodsStorageInfo,
                                          AuditStatus = gr.AuditStatus,
                                          AuditUserId = gr.AuditUserId,
                                          AuditUserName = u1.CnName,
                                          AuditTime = gr.AuditTime,
                                          CreateUserName = u2.CnName,
                                          CreateTime = gr.CreateTime
                                      })
                                      .FirstAsync();

            if (!string.IsNullOrEmpty(data.GoodsStorageInfo))
            {
                var subData = new List<dynamic>();
                try
                {
                    var subData1 = JsonConvert.DeserializeObject<List<GoodsReceiveLinkStorageView>>(data.GoodsStorageInfo);
                    if (subData1.Count > 0)
                    {
                        string goodsStorageInfoStr = string.Empty;
                        var storageIds = subData1.Select(x => x.StorageId).ToList();
                        var storages = await _sqlSugar.Queryable<Pm_GoodsStorage>().Where(x => x.IsDel == 0 && storageIds.Contains(x.Id)).ToListAsync();
                        foreach (var item in subData1)
                        {
                            var storageInfo = storages.Find(x => x.Id == item.StorageId);
                            if (storageInfo != null)
                            {
                                subData.Add(new
                                {
                                    StorageId = item.StorageId,
                                    BatchNo = storageInfo.BatchNo,
                                    RecsiveQuantity = item.Quantity
                                });
                                goodsStorageInfoStr += $"物品名称：{data.GoodsName} 批次号：{storageInfo.BatchNo} 领用数量：{item.Quantity} \r\n";
                            }
                        }
                        data.QuantityInfos = subData;
                        data.GoodsStorageInfoStr = goodsStorageInfoStr;
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
            }
            
            _jv.Code = StatusCodes.Status200OK;
            _jv.Msg = $"操作成功";
            if (portType == 2 || portType == 3) //移动端
            {
                _jv.Data = data;
            }
            else if (portType == 1) //pc端
            {
                _jv.Data = _mapper.Map<GoodsReceiveInfoView>(data);
            }

            return _jv;
        }

        /// <summary>
        /// 物品领用 OP(Add Or Edit)
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="currUserId"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsReceiveOp(GoodsReceiveOpDto dto, int currUserId)
        {
            var info = _mapper.Map<Pm_GoodsReceive>(dto);
            info.CreateUserId = currUserId;
            _sqlSugar.BeginTran();

            //物品现有库存
            var stockQuantity = _sqlSugar.Queryable<Pm_GoodsInfo>()
                                               .First(x => x.Id == info.GoodsId)
                                               ?.StockQuantity;

            //待审核 该物品数量
            var waitAuditQuantity = await _sqlSugar.Queryable<Pm_GoodsReceive>()
                                                   .Where(x => x.IsDel == 0 &&
                                                               x.GoodsId == dto.GoodsId &&
                                                               x.AuditStatus == GoodsAuditEnum.Pending
                                                    )
                                                   .SumAsync(x => x.Quantity);
            if (info.Id > 0) //修改
            {
                //审核验证
                var selectInfo = await _sqlSugar.Queryable<Pm_GoodsReceive>().FirstAsync(x => x.Id == info.Id);
                if (selectInfo.AuditStatus == GoodsAuditEnum.Approved)
                {
                    _sqlSugar.RollbackTran();
                    _jv.Msg = $"该条数据已通过审核,不可更改!";
                    return _jv;
                }

                //物品数量验证
                var editAfterQuantity = waitAuditQuantity - selectInfo.Quantity + info.Quantity;
                if (editAfterQuantity > stockQuantity)
                {
                    _sqlSugar.RollbackTran();
                    _jv.Msg = $"该物品现有库存不足,不可更改!请联系采购人员购买!";
                    return _jv;
                }

                var edit = await _sqlSugar.Updateable(info)
                                          .UpdateColumns(x => new
                                          {
                                              x.GroupId,
                                              x.Quantity,
                                              x.Reason,
                                              x.Remark,
                                          })
                                          .Where(x => x.Id == info.Id)
                                          .ExecuteCommandAsync();
                if (edit > 0)
                {
                    _sqlSugar.CommitTran();
                    _jv.Msg = $"操作成功!";
                    _jv.Code = StatusCodes.Status200OK;
                    return _jv;
                }
            }
            else if (info.Id < 1) //添加
            {
                //物品数量验证
                decimal addAgoQuantity = waitAuditQuantity + info.Quantity;
                if (addAgoQuantity > stockQuantity)
                {
                    _sqlSugar.RollbackTran();
                    _jv.Msg = $"该物品现有库存不足,不可更改!请联系采购人员购买!";
                    return _jv;
                }

                var add = await _sqlSugar.Insertable(info).ExecuteCommandAsync();
                if (add > 0)
                {
                    _sqlSugar.CommitTran();
                    _jv.Msg = $"操作成功!";
                    _jv.Code = StatusCodes.Status200OK;
                    return _jv;
                }
            }
            _sqlSugar.RollbackTran();
            return _jv;
        }

        /// <summary>
        /// 物品领用 Audit
        /// </summary>
        /// <param name="idArray"></param>
        /// <param name="userId"></param>
        /// <param name="auditEnum"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsReceiveAudit(int[] idArray, int userId, GoodsAuditEnum auditEnum)
        {
            if (idArray.Length < 1) return _jv;

            //TODO: 审核权限验证

            _sqlSugar.BeginTran();
            var receiveInfos = await _sqlSugar.Queryable<Pm_GoodsReceive>()
                                              .Where(x => x.IsDel == 0 && idArray.Contains(x.Id))
                                              .ToListAsync();

            var status = true;
            foreach (var id in idArray)
            {
                //1.更改审核状态
                var currInfo = receiveInfos.Find(x => x.Id == id);
                if (currInfo == null) continue;
                var edit = await _sqlSugar.Updateable<Pm_GoodsReceive>()
                                             .SetColumns(x => new Pm_GoodsReceive()
                                             {
                                                 AuditStatus = auditEnum,
                                                 AuditUserId = userId,
                                                 AuditTime = DateTime.Now,
                                             })
                                             .Where(x => x.Id == id)
                                             .ExecuteCommandAsync();
                if (edit < 1) status = false;

                //if (auditEnum != GoodsAuditEnum.Approved) continue;
                //2.更改库存
                var goodsInfo = await _sqlSugar.Queryable<Pm_GoodsInfo>().Where(x => x.Id == currInfo.GoodsId).FirstAsync();
                if (auditEnum == GoodsAuditEnum.Pending) 
                {
                    goodsInfo.StockQuantity += currInfo.Quantity;
                    goodsInfo.OQ_Total -= currInfo.Quantity;
                }
                else if (auditEnum == GoodsAuditEnum.Approved)
                {
                    goodsInfo.StockQuantity -= currInfo.Quantity;
                    goodsInfo.OQ_Total += currInfo.Quantity;
                }else if(auditEnum == GoodsAuditEnum.UnApproved) continue;
                goodsInfo.LastUpdateTime = DateTime.Now;
                goodsInfo.LastUpdateUserId = userId;

                var editGoods = await _sqlSugar.Updateable<Pm_GoodsInfo>(goodsInfo)
                                                   .UpdateColumns(x => new
                                                   {
                                                       x.StockQuantity,
                                                       x.OQ_Total,
                                                       x.LastUpdateUserId,
                                                       x.LastUpdateTime,
                                                   })
                                                   .Where(x => x.Id == currInfo.GoodsId)
                                                   .ExecuteCommandAsync();

                if (editGoods < 1) status = false;

                //3.入库批次关联领用人 更改批次库存
                var goodsStorages = await _sqlSugar.Queryable<Pm_GoodsStorage>()
                                                                    .Where(x => x.IsDel == 0 &&
                                                                                             x.GoodsId == currInfo.GoodsId &&
                                                                                             (x.Quantity - x.ReceiveQuantity) > 0
                                                                     )
                                                                    .OrderBy(x => x.CreateTime)
                                                                    .ToListAsync();
                var goodsReceiveInfos = new List<GoodsReceiveLinkStorageView>();
                var batchStorageInfos = new List<Pm_GoodsStorage>();
                var receiveQuantity = 0.00M; //领用总数量
                if (auditEnum == GoodsAuditEnum.Approved)
                {
                    foreach (var storage in goodsStorages)
                    {
                        if (currInfo.Quantity == receiveQuantity) break;

                        var thisBatchSurplusQuantity = storage.Quantity - storage.ReceiveQuantity;
                        if (thisBatchSurplusQuantity <= 0.00M) continue;

                        var thisBatchReceiveQuantity = 0.00M; //此批次领用数量
                        const decimal unit = 0.50M;
                        while (receiveQuantity < currInfo.Quantity)
                        {
                            if (thisBatchSurplusQuantity == thisBatchReceiveQuantity) break;

                            thisBatchReceiveQuantity += unit;
                            receiveQuantity += unit;
                        }
                        goodsReceiveInfos.Add(new GoodsReceiveLinkStorageView
                        {
                            StorageId = storage.Id,
                            Quantity = thisBatchReceiveQuantity
                        });
                        storage.ReceiveQuantity += thisBatchReceiveQuantity;
                        var storageUpd = storage;
                        //storageUpd.ReceiveQuantity += thisBatchReceiveQuantity;
                        batchStorageInfos.Add(storageUpd);
                    }
                    //3.1 更改批次库存
                    if (goodsReceiveInfos.Count > 0)
                    {
                        var edit1 = await _sqlSugar.Updateable(batchStorageInfos)
                            .UpdateColumns(x => x.ReceiveQuantity)
                            .WhereColumns(x => x.Id)
                            .ExecuteCommandAsync();
                        if (edit1 < 1) status = false;
                    }

                    //3.2 添加入库批次关联领用人
                    if (goodsReceiveInfos.Count > 0)
                    {
                        var edit1 = await _sqlSugar.Updateable<Pm_GoodsReceive>()
                            .SetColumns(x => new Pm_GoodsReceive()
                            {
                                GoodsStorageInfo = JsonConvert.SerializeObject(goodsReceiveInfos)
                            })
                            .Where(x => x.Id == id)
                            .ExecuteCommandAsync();
                        if (edit1 < 1) status = false;
                    }
                }
                else if (auditEnum == GoodsAuditEnum.Pending)
                {
                    var goodsStorageInfo = currInfo.GoodsStorageInfo;
                    if (!string.IsNullOrEmpty(goodsStorageInfo))
                    {
                        var goodsStorageInfos = JsonConvert.DeserializeObject<List<GoodsReceiveLinkStorageView>>(goodsStorageInfo);
                        if (goodsStorageInfos.Count > 0)
                        {
                            foreach (var item in goodsStorageInfos)
                            {
                                var newStorageInfo = await _sqlSugar.Queryable<Pm_GoodsStorage>()
                                    .Where(x => x.IsDel == 0 && x.Id == item.StorageId)
                                    .FirstAsync();
                                if (newStorageInfo != null)
                                {
                                    var newEdit = await _sqlSugar.Updateable(newStorageInfo)
                                        .ReSetValue(x => x.ReceiveQuantity = x.ReceiveQuantity - item.Quantity)
                                        .ExecuteCommandAsync();
                                    if (newEdit < 1) status = false;
                                }
                            }
                        }
                    }
                }
            }

            if (status)
            {
                _sqlSugar.CommitTran();
                _jv.Msg = $"操作成功!";
                _jv.Code = StatusCodes.Status200OK;
                return _jv;
            }

            _sqlSugar.RollbackTran();
            return _jv;
        }

        /// <summary>
        /// 物品领用 Del
        /// </summary>
        /// <param name="id"></param>
        /// <param name="currUserId"></param>
        /// <returns></returns>
        public async Task<JsonView> GoodsReceiveDel(int id, int currUserId)
        {
            var receiveInfo = await _sqlSugar.Queryable<Pm_GoodsReceive>()
                                             .Where(x => x.IsDel == 0 && x.Id == id)
                                             .FirstAsync();
            if (receiveInfo.AuditStatus == GoodsAuditEnum.Approved)
            {
                _jv.Msg = $"该条数据已通过审核,不可删除!";
                return _jv;
            }

            var edit = await _sqlSugar.Updateable<Pm_GoodsReceive>()
                                        .SetColumns(x => new Pm_GoodsReceive()
                                        {
                                            IsDel = 1,
                                            DeleteUserId = currUserId,
                                            DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                        })
                                        .Where(x => x.Id == id)
                                        .ExecuteCommandAsync();
            if (edit > 0)
            {
                _jv.Msg = $"操作成功!";
                _jv.Code = StatusCodes.Status200OK;
                return _jv;
            }

            return _jv;
        }
    }
}