using Admin.NET.Core.Service; using Admin.NET.Application.Entity; using Microsoft.AspNetCore.Http; using OfficeOpenXml.FormulaParsing.Excel.Functions.Text; using System.Web; using System.Data; using System.Text; using Furion.DatabaseAccessor; namespace Admin.NET.Application; /// /// 容器信息服务 /// [ApiDescriptionSettings(ApplicationConst.WmsBaseGroupName, Order = 100)] public class WmsContainerService : IDynamicApiController, ITransient { private readonly SqlSugarRepository _rep; private readonly SqlSugarRepository _repWmsStockQuan; private readonly SqlSugarRepository _repWmsContainerType; private readonly SqlSugarRepository _wmsPlaceRep; private readonly SqlSugarRepository _wmsContainerPlaceRep; private readonly SqlSugarRepository _wmsMaterialRep; public WmsContainerService( SqlSugarRepository rep , SqlSugarRepository repWmsContainerType, SqlSugarRepository wmsContainerPlaceRep, SqlSugarRepository wmsPlaceRep, SqlSugarRepository repWmsStockQuan , SqlSugarRepository wmsMaterialRep ) { _rep = rep; _repWmsContainerType = repWmsContainerType; _wmsContainerPlaceRep = wmsContainerPlaceRep; _wmsPlaceRep = wmsPlaceRep; _repWmsStockQuan = repWmsStockQuan; _wmsMaterialRep = wmsMaterialRep; } /// /// 分页查询容器信息 /// /// /// [HttpPost] [ApiDescriptionSettings(Name = "Page")] public async Task> Page(WmsContainerInput input) { var query = _rep.AsQueryable() .WhereIF(!string.IsNullOrWhiteSpace(input.SearchKey), u => u.ContainerCode.Contains(input.SearchKey.Trim()) || u.ContainerName.Contains(input.SearchKey.Trim()) ) .WhereIF(input.IsDisabled != null, u => u.IsDisabled == input.IsDisabled) .WhereIF(!string.IsNullOrWhiteSpace(input.ContainerCode), u => u.ContainerCode.Contains(input.ContainerCode.Trim())) .WhereIF(!string.IsNullOrWhiteSpace(input.ContainerName), u => u.ContainerName.Contains(input.ContainerName.Trim())) .WhereIF(input.ContainerTypeId>0, u => u.ContainerTypeId == input.ContainerTypeId) .WhereIF(input.IsVirtually!=null, u => u.IsVirtually == input.IsVirtually) .Select(); return await query.OrderBuilder(input).ToPagedListAsync(input.Page, input.PageSize); } /// /// 增加容器信息 /// /// /// [HttpPost] [ApiDescriptionSettings(Name = "Add")] public async Task Add(AddWmsContainerInput input) { var entity = input.Adapt(); //验重 await CheckExisit(entity); if (entity.IsVirtually == null) { entity.IsVirtually = false; } //ly - 容器类型 var Info = await _repWmsContainerType.GetFirstAsync(u => u.Id == input.ContainerTypeId); if (Info == null) { throw Oops.Oh(errorMessage: @$"容器类型不存在!"); } if (Info.IsDisabled == true) { throw Oops.Oh($"不能使用已禁用的容器类型"); } entity.ContainerTypeName = Info.TypeName; entity.ContainerTypeCode = Info.TypeCode; await _rep.InsertAsync(entity); return entity.Id; } /// /// 删除容器信息 /// /// /// [HttpPost] [ApiDescriptionSettings(Name = "Delete")] public async Task Delete(DeleteWmsContainerInput input) { // var exist = await _repWmsStockQuan.GetFirstAsync(u => u.ContainerId == input.Id) ?? throw Oops.Oh("该容器存在绑定关系不可删除!"); var entity = await _rep.GetFirstAsync(u => u.Id == input.Id) ?? throw Oops.Oh(ErrorCodeEnum.D1002); var entityWmsContainerPlace = await _wmsContainerPlaceRep.GetFirstAsync(u => u.ContainerId == entity.Id); if (entityWmsContainerPlace != null) { throw Oops.Oh("存在绑定关系,不可删除"); } //await _rep.FakeDeleteAsync(entity); //假删除 await _rep.DeleteAsync(entity); //真删除 } /// /// 更新容器信息 /// /// /// [HttpPost] [ApiDescriptionSettings(Name = "Update")] public async Task Update(UpdateWmsContainerInput input) { var entity = input.Adapt(); //ly - 容器类型 var Info = await _repWmsContainerType.GetFirstAsync(u => u.Id == input.ContainerTypeId); entity.ContainerTypeName = Info.TypeName; entity.ContainerTypeCode = Info.TypeCode; //entity.ContainerTypeC = Info.AreaCode; if (Info == null) { throw Oops.Oh(errorMessage: @$"容器类型不存在!"); } if (Info.IsDisabled == true) { throw Oops.Oh($"不能使用已禁用的容器类型"); } //验重 await CheckExisit(entity, true); try { await _rep.AsTenant().BeginTranAsync(); await _rep.UpdateAsync(entity); // await _rep.AsUpdateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync(); await _rep.AsTenant().CommitTranAsync(); } catch { await _rep.AsTenant().RollbackTranAsync(); throw; } } /// /// 获取容器信息 /// /// /// [HttpGet] [ApiDescriptionSettings(Name = "Detail")] public async Task Detail([FromQuery] QueryByIdWmsContainerInput input) { return await _rep.GetFirstAsync(u => u.Id == input.Id); } /// /// 获取容器信息列表 /// /// /// [HttpGet] [ApiDescriptionSettings(Name = "List")] public async Task> List([FromQuery] WmsContainerInput input) { return await _rep.AsQueryable().Select().ToListAsync(); } /// /// pda-容器下架-获取容器类型 /// /// /// [HttpGet] [ApiDescriptionSettings(Name = "ContainerTypeForPda")] [Description("WmsContainer/ContainerTypeForPda")] public async Task> ContainerTypeForPda([FromQuery] WmsContainerForpdaInput input) { /** ly0723 先查库存表 再查物料表 返回容器类型 */ if (String.IsNullOrEmpty(input.MaterialAndSncode)) { throw Oops.Oh("请传入物料编号或者跟踪码"); } WmsStockQuan WmsStockItemName = new WmsStockQuan(); //查库存 var WmsStockItem = await _repWmsStockQuan.GetFirstAsync(u => u.SNCode == input.MaterialAndSncode.Trim() && u.IsDelete == false); if (WmsStockItem != null) { WmsStockItemName = WmsStockItem; } //查物料 var WmsContainerTypeItem = await _wmsMaterialRep.AsQueryable() .LeftJoin((u, a) => u.MaterialCode == a.MaterialCode) .LeftJoin((u, a, b) => b.Id == a.ContainerTypeId) .Where((u, a) => u.IsDelete == false) .Where((u, a) => u.MaterialCode == input.MaterialAndSncode.Trim() || u.MaterialCode == WmsStockItemName.MaterialCode) .Select((u, a, b) => new WmsContainerForPdaOutput { TypeCode = b.TypeCode, TypeName = b.TypeName }, true).ToListAsync(); return WmsContainerTypeItem; } #region 导入 /// /// Excel模板导入容器信息表功能 /// /// Excel模板文件 /// 导入的记录数 [HttpPost] [ApiDescriptionSettings(Name = "ImportExcel")] [Description("WmsContainer/ImportExcel")] public async Task ImportExcelAsync(IFormFile file) { int _HeadStartLine = 2;//第1行是说明,第2行是列名 int _DataStartLine = 3;//第3行开始是数据 DataTable importDataTable = ExcelUtil.ImportExcelToDataTable(file, _HeadStartLine, _DataStartLine); var addList = await CommonImport(importDataTable, _DataStartLine); await _rep.InsertRangeAsync(addList); return addList.Count; } /// /// DataTable转换实体对象列表 /// /// /// 模版列名开始行 /// private async Task> CommonImport(DataTable dataTable, int dataStartLine) { var details = new List(); int index = dataStartLine;//模版列名开始行 foreach (System.Data.DataRow row in dataTable.Rows) { index++; //导入模版定制化代码(替换模版使用) var addItem = new WmsBaseContainer(); #region 定义变量 var _ContainerCode = "";//编号 var _ContainerName = "";//名称 //var _ContainerTypeId = "";//类型ID //var _ContainerTypeCode = "";//类型编号 var _ContainerTypeName = "";//容器类型名称 var _SpecLength = "";//长度 var _SpecWidth = "";//宽度 var _SpecHeight = "";//高度 var _LimitLength = "";//限长 var _LimitWidth = "";//限宽 var _LimitHeight = "";//限高 var _MaxWeight = "";//载重上限 var _ParentContainerName = "";//父容器名称 var _ParentContainerId = "";//父容器Id var _IsVirtually = "";//是否虚拟 var _IsDisabled = "";//是否禁用 var _UsageCount = "";//使用次数 #endregion #region 取值 _ContainerCode = row["编号"]?.ToString(); _ContainerName = row["名称"]?.ToString(); //_ContainerTypeId = row["类型ID"]?.ToString(); //_ContainerTypeCode = row["类型编号"]?.ToString(); _ContainerTypeName = row["容器类型名称"]?.ToString(); _SpecLength = row["长度"]?.ToString(); _SpecWidth = row["宽度"]?.ToString(); _SpecHeight = row["高度"]?.ToString(); _LimitLength = row["限长"]?.ToString(); _LimitWidth = row["限宽"]?.ToString(); _LimitHeight = row["限高"]?.ToString(); _MaxWeight = row["载重上限"]?.ToString(); _ParentContainerName = row["父容器名称"]?.ToString(); _ParentContainerId = row["父容器Id"]?.ToString(); _IsVirtually = row["是否虚拟"]?.ToString(); _IsDisabled = row["是否禁用"]?.ToString(); _UsageCount = row["使用次数"]?.ToString(); #endregion #region 验证 if (string.IsNullOrEmpty(_ContainerCode)) { throw Oops.Oh($"第{index}行[编号]{_ContainerCode}不能为空!"); } if (!string.IsNullOrEmpty(_ContainerCode)) { var isExist = await _rep.AsQueryable().FirstAsync(p => p.ContainerCode == _ContainerCode); if (isExist != null) throw Oops.Oh("该容器编号" + _ContainerCode + "已存在"); addItem.ContainerCode = (string)(_ContainerCode.Trim()); } if (string.IsNullOrEmpty(_ContainerName)) { throw Oops.Oh($"第{index}行[名称]{_ContainerName}不能为空!"); } if (!string.IsNullOrEmpty(_ContainerName)) { addItem.ContainerName = (string)(_ContainerName.Trim()); } //if (string.IsNullOrEmpty(_ContainerTypeId)) //{ // throw Oops.Oh($"第{index}行[类型ID]{_ContainerTypeId}不能为空!"); //} //if (!string.IsNullOrEmpty(_ContainerTypeId)) //{ // if (!long.TryParse(_ContainerTypeId, out long outContainerTypeId) && !string.IsNullOrEmpty(_ContainerTypeId)) // { // throw Oops.Oh($"第{index}行[类型ID]{_ContainerTypeId}值不正确!"); // } // if (outContainerTypeId <= 0 && !string.IsNullOrEmpty(_ContainerTypeId)) // { // throw Oops.Oh($"第{index}行[类型ID]{_ContainerTypeId}值不能小于等于0!"); // } // else // { // addItem.ContainerTypeId = outContainerTypeId; // } //} //if (!string.IsNullOrEmpty(_ContainerTypeCode)) //{ // addItem.ContainerTypeCode = (string)(_ContainerTypeCode.Trim()); //} //根据容器名称去查询容器类型编号和id if (string.IsNullOrEmpty(_ContainerTypeName)) { throw Oops.Oh($"第{index}行[容器类型名称]{_ContainerTypeName}不能为空!"); } if (!string.IsNullOrEmpty(_ContainerTypeName)) { var ContainerTypeModel = await _repWmsContainerType.AsQueryable().FirstAsync(p =>p.TypeName.Contains(_ContainerTypeName)); addItem.ContainerTypeName = (string)(_ContainerTypeName.Trim()); addItem.ContainerTypeId = ContainerTypeModel.Id; addItem.ContainerTypeCode = ContainerTypeModel.TypeCode; } if (!string.IsNullOrEmpty(_SpecLength)) { if (!decimal.TryParse(_SpecLength, out decimal outSpecLength) && !string.IsNullOrEmpty(_SpecLength)) { throw Oops.Oh($"第{index}行[长度]{_SpecLength}值不正确!"); } if (outSpecLength <= 0 && !string.IsNullOrEmpty(_SpecLength)) { throw Oops.Oh($"第{index}行[长度]{_SpecLength}值不能小于等于0!"); } else { addItem.SpecLength = outSpecLength; } } if (!string.IsNullOrEmpty(_SpecWidth)) { if (!decimal.TryParse(_SpecWidth, out decimal outSpecWidth) && !string.IsNullOrEmpty(_SpecWidth)) { throw Oops.Oh($"第{index}行[宽度]{_SpecWidth}值不正确!"); } if (outSpecWidth <= 0 && !string.IsNullOrEmpty(_SpecWidth)) { throw Oops.Oh($"第{index}行[宽度]{_SpecWidth}值不能小于等于0!"); } else { addItem.SpecWidth = outSpecWidth; } } if (!string.IsNullOrEmpty(_SpecHeight)) { if (!decimal.TryParse(_SpecHeight, out decimal outSpecHeight) && !string.IsNullOrEmpty(_SpecHeight)) { throw Oops.Oh($"第{index}行[高度]{_SpecHeight}值不正确!"); } if (outSpecHeight <= 0 && !string.IsNullOrEmpty(_SpecHeight)) { throw Oops.Oh($"第{index}行[高度]{_SpecHeight}值不能小于等于0!"); } else { addItem.SpecHeight = outSpecHeight; } } if (!string.IsNullOrEmpty(_LimitLength)) { if (!decimal.TryParse(_LimitLength, out decimal outLimitLength) && !string.IsNullOrEmpty(_LimitLength)) { throw Oops.Oh($"第{index}行[限长]{_LimitLength}值不正确!"); } if (outLimitLength <= 0 && !string.IsNullOrEmpty(_LimitLength)) { throw Oops.Oh($"第{index}行[限长]{_LimitLength}值不能小于等于0!"); } else { addItem.LimitLength = outLimitLength; } } if (!string.IsNullOrEmpty(_LimitWidth)) { if (!decimal.TryParse(_LimitWidth, out decimal outLimitWidth) && !string.IsNullOrEmpty(_LimitWidth)) { throw Oops.Oh($"第{index}行[限宽]{_LimitWidth}值不正确!"); } if (outLimitWidth <= 0 && !string.IsNullOrEmpty(_LimitWidth)) { throw Oops.Oh($"第{index}行[限宽]{_LimitWidth}值不能小于等于0!"); } else { addItem.LimitWidth = outLimitWidth; } } if (!string.IsNullOrEmpty(_LimitHeight)) { if (!decimal.TryParse(_LimitHeight, out decimal outLimitHeight) && !string.IsNullOrEmpty(_LimitHeight)) { throw Oops.Oh($"第{index}行[限高]{_LimitHeight}值不正确!"); } if (outLimitHeight <= 0 && !string.IsNullOrEmpty(_LimitHeight)) { throw Oops.Oh($"第{index}行[限高]{_LimitHeight}值不能小于等于0!"); } else { addItem.LimitHeight = outLimitHeight; } } if (!string.IsNullOrEmpty(_MaxWeight)) { if (!decimal.TryParse(_MaxWeight, out decimal outMaxWeight) && !string.IsNullOrEmpty(_MaxWeight)) { throw Oops.Oh($"第{index}行[载重上限]{_MaxWeight}值不正确!"); } if (outMaxWeight <= 0 && !string.IsNullOrEmpty(_MaxWeight)) { throw Oops.Oh($"第{index}行[载重上限]{_MaxWeight}值不能小于等于0!"); } else { addItem.MaxWeight = outMaxWeight; } } if (!string.IsNullOrEmpty(_ParentContainerName)) { addItem.ParentContainerName = (string)(_ParentContainerName.Trim()); } if (!string.IsNullOrEmpty(_ParentContainerId)) { if (!long.TryParse(_ParentContainerId, out long outParentContainerId) && !string.IsNullOrEmpty(_ParentContainerId)) { throw Oops.Oh($"第{index}行[父容器Id]{_ParentContainerId}值不正确!"); } if (outParentContainerId <= 0 && !string.IsNullOrEmpty(_ParentContainerId)) { throw Oops.Oh($"第{index}行[父容器Id]{_ParentContainerId}值不能小于等于0!"); } else { addItem.ParentContainerId = outParentContainerId; } } if (!string.IsNullOrEmpty(_IsVirtually)) { if (!_IsVirtually.Equals("是") && !_IsVirtually.Equals("否")) { throw Oops.Oh($"第{index}行[是否虚拟]{_IsVirtually}值不正确!"); } else { bool outIsVirtually = _IsVirtually.Equals("是") ? true : false; addItem.IsVirtually = outIsVirtually; } } if (string.IsNullOrEmpty(_IsDisabled)) { throw Oops.Oh($"第{index}行[是否禁用]{_IsDisabled}不能为空!"); } if (!string.IsNullOrEmpty(_IsDisabled)) { if (!_IsDisabled.Equals("是") && !_IsDisabled.Equals("否")) { throw Oops.Oh($"第{index}行[是否禁用]{_IsDisabled}值不正确!"); } else { bool outIsDisabled = _IsDisabled.Equals("是") ? true : false; addItem.IsDisabled = outIsDisabled; } } if (string.IsNullOrEmpty(_UsageCount)) { throw Oops.Oh($"第{index}行[使用次数]{_UsageCount}不能为空!"); } if (!string.IsNullOrEmpty(_UsageCount)) { if (!int.TryParse(_UsageCount, out int outUsageCount) && !string.IsNullOrEmpty(_UsageCount)) { throw Oops.Oh($"第{index}行[使用次数]{_UsageCount}值不正确!"); } if (outUsageCount <= 0 && !string.IsNullOrEmpty(_UsageCount)) { throw Oops.Oh($"第{index}行[使用次数]{_UsageCount}值不能小于等于0!"); } else { addItem.UsageCount = outUsageCount; } } #endregion details.Add(addItem); } //验重 await CheckExisitForImport(details); return details; } /// /// 根据版本下载容器信息表的Excel导入模板 /// /// 下载的模板文件 [HttpGet] [ApiDescriptionSettings(Name = "DownloadExcelTemplate")] [Description("WmsContainer/DownloadExcelTemplate")] public IActionResult DownloadExcelTemplate() { string _path = TemplateConst.EXCEL_TEMPLATEFILE_导入模版路径 + $"\\容器信息表{TemplateConst.EXCEL_TEMPLATEFILE_导入模版名称后缀}.xlsx"; var fileName = HttpUtility.UrlEncode($"导入模板(容器信息表).xlsx", Encoding.GetEncoding("UTF-8")); return new FileStreamResult(new FileStream(_path, FileMode.Open), "application/octet-stream") { FileDownloadName = fileName }; } #endregion /// /// 根据组合校验和单独校验验证数据是否已存在-导入时验证 /// /// /// private async Task CheckExisitForImport(List inputs) { if (inputs?.Count <= 0) { throw Oops.Oh($"导入数据不能为空"); } //根据组合校验验证表格中中是否已存在相同数据 //根据单独校验验证表格中中是否已存在相同数据 } #region 私有方法 /// /// 根据联合主键验证数据是否已存在-数据库 /// /// /// /// private async Task CheckExisit(WmsBaseContainer input, bool isEdit = false) { //输出数据已存在错误 ErrorCodeItemMetadataAttribute metadata = ErrorCodeEnum.D1006.GetErrorCodeItemMetadata(); WmsBaseContainer _existItem = null; if (!isEdit)//新增 { _existItem = await _rep.GetFirstAsync(u => u.ContainerCode.Equals(input.ContainerCode)); if (_existItem != null) throw Oops.Oh($"编号[{input.ContainerCode}]{metadata.ErrorMessage}"); _existItem = await _rep.GetFirstAsync(u => u.ContainerName.Equals(input.ContainerName)); if (_existItem != null) throw Oops.Oh($"名称[{input.ContainerName}]{metadata.ErrorMessage}"); } else//编辑 { //当前编辑数据以外是否存在重复 _existItem = await _rep.GetFirstAsync(u => u.Id != input.Id && u.ContainerCode.Equals(input.ContainerCode)); if (_existItem != null) throw Oops.Oh($"编号[{input.ContainerCode}]{metadata.ErrorMessage}"); _existItem = await _rep.GetFirstAsync(u => u.Id != input.Id && u.ContainerName.Equals(input.ContainerName)); if (_existItem != null) throw Oops.Oh($"名称[{input.ContainerName}]{metadata.ErrorMessage}"); } } #endregion }