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
}