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