using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Runtime.Intrinsics.X86;
|
using System.Security.Claims;
|
using System.Security.Policy;
|
using System.Text;
|
using Model.InterFaceModel;
|
using Model.ModelDto;
|
using Model.ModelDto.DataDto;
|
using Model.ModelDto.LogDto;
|
using Model.ModelDto.SysDto;
|
using Model.ModelVm.DataVm;
|
using Newtonsoft.Json;
|
using SqlSugar;
|
using Utility.Tools;
|
using WMS.BLL.LogServer;
|
using WMS.DAL;
|
using WMS.Entity.BllAsnEntity;
|
using WMS.Entity.BllSoEntity;
|
using WMS.Entity.Context;
|
using WMS.Entity.DataEntity;
|
using WMS.Entity.LogEntity;
|
using WMS.Entity.SysEntity;
|
using WMS.IBLL.IDataServer;
|
|
namespace WMS.BLL.DataServer
|
{
|
public class StockServer : DbHelper<DataStock>, IStockServer
|
{
|
private static readonly SqlSugarScope Db = DataContext.Db;
|
public StockServer() : base(Db)
|
{
|
|
}
|
|
|
#region 库存明细
|
|
/// <summary>
|
/// 查询库存总量
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <returns></returns>
|
public List<MateDataStockDto> GetDataStockList(string skuNo, string skuName, string ownerNo, string ownerName, string lotNo)
|
{
|
string str = "select stock.SkuNo,stock.SkuName,stock.LotNo,stock.LotText,stock.Standard,stock.Qty," +
|
"stock.LockQty,stock.FrozenQty,stock.OwnerNo,stock.OwnerName,(mate.Weight * stock.Qty) WeightSum " +
|
"from DataStock stock " +
|
"left join SysMaterials mate on stock.SkuNo = mate.SkuNo " +
|
"Where stock.IsDel = @isdel and mate.IsDel = 0 ";
|
//判断物料编码是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and stock.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and stock.SkuName like @skuname";
|
}
|
//判断货主编码是否为空
|
if (!string.IsNullOrEmpty(ownerNo))
|
{
|
str += " and stock.OwnerNo like @ownerNo";
|
}
|
//判断货主名称是否为空
|
if (!string.IsNullOrEmpty(ownerName))
|
{
|
str += " and stock.OwnerName like @ownerName";
|
}
|
//判断货主名称是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and stock.LotNo like @lotNo";
|
}
|
//排序
|
str += " order by stock.SkuNo";
|
List<MateDataStockDto> StockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料编码
|
skuname = "%" + skuName + "%", //物料名称
|
ownerNo= "%" + ownerNo + "%", //货主编码
|
ownerName= "%" + ownerName + "%", //货主名称
|
lotNo= "%" + lotNo + "%", //批次号
|
});
|
|
//库存总量
|
List<MateDataStockDto> StockListDto = new List<MateDataStockDto>();
|
DataStockDetail detail = new DataStockDetail();
|
|
foreach (var item in StockList)
|
{
|
|
detail = Db.Queryable<DataStockDetail>().First(w => w.SkuNo == item.SkuNo && w.LotNo == item.LotNo && w.IsDel == "0");
|
if (detail != null)
|
{
|
if (!string.IsNullOrEmpty(detail.ProductionTime.ToString()))
|
{
|
item.ProductionTime = (DateTime)detail.ProductionTime;
|
}
|
if (!string.IsNullOrEmpty(detail.ExpirationTime.ToString()))
|
{
|
item.ExpirationTime = (DateTime)detail.ExpirationTime;
|
}
|
item.InspectStatus = detail.InspectStatus;
|
}
|
|
|
|
StockListDto.Add(item);
|
////判断库存总量是否拥有物料
|
//if (StockListDto.Count > 0)
|
//{
|
// int i = 0;
|
// //foreach循环库存总量
|
// foreach (var dto in StockListDto)
|
// {
|
// //判断物料和批次是否相同
|
// if (dto.SkuNo == item.SkuNo && dto.LotNo == item.LotNo)
|
// {
|
// dto.Qty = (Convert.ToDecimal(dto.Qty) + Convert.ToDecimal(item.Qty)).ToString();
|
// dto.FrozenQty = (Convert.ToDecimal(dto.FrozenQty) + Convert.ToDecimal(item.FrozenQty)).ToString();
|
// dto.LockQty = (Convert.ToDecimal(dto.LockQty) + Convert.ToDecimal(item.LockQty)).ToString();
|
// dto.ResidueQty= (Convert.ToDecimal(dto.ResidueQty) + Convert.ToDecimal(item.ResidueQty)).ToString();
|
// break;
|
// }
|
// i += 1;
|
// //判断已有相同物料
|
// if (i == StockListDto.Count)
|
// {
|
// StockListDto.Add(item);
|
// break;
|
// }
|
// }
|
//}
|
//else
|
//{
|
// StockListDto.Add(item);
|
// continue;
|
//}
|
}
|
|
return StockListDto;
|
}
|
|
/// <summary>
|
/// 获取库存明细
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="locatNo">储位地址</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="status">库存状态</param>
|
/// <param name="inspectStatus">质检状态</param>
|
/// <param name="houseNo">所属仓库</param>
|
/// <param name="areaNo">所属区域</param>
|
/// <param name="type">物料类型</param>
|
/// <param name="ownerNo">货主编号</param>
|
/// <param name="ownerName">货主名称</param>
|
/// <returns></returns>
|
public List<StockDetailDto> GetInventoryList1(string skuNo, string skuName, string lotNo, string locatNo, string palletNo,
|
string status, string inspectStatus, string ownerNo, string ownerName, string houseNo, string areaNo, string categoryNo, string type)
|
{
|
string str = "select detail.*,sku.Type,house.WareHouseNo + '-' + house.WareHouseName as WareHouseName," +
|
"roadway.RoadwayNo + '-' + roadway.RoadwayName as RoadwayName,area.AreaNo + '-' + area.AreaName as AreaName " +
|
"from DataStockDetail detail " +
|
"left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo " +
|
"left join SysWareHouse house on detail.WareHouseNo = house.WareHouseNo " +
|
"left join SysStorageArea area on detail.AreaNo = area.AreaNo " +
|
"left join SysMaterials as sku on detail.skuNo = sku.skuNo " +
|
"left join SysMaterialCategory as cat on sku.CategoryNo = cat.CategoryNo " +
|
"Where detail.IsDel = @isdel";
|
//判断物料编码是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and detail.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and detail.SkuName like @skuname";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and detail.LotNo like @lotno";
|
}
|
//判断储位地址是否为空
|
if (!string.IsNullOrEmpty(locatNo))
|
{
|
str += " and detail.LocatNo like @locatno";
|
}
|
//判断托盘条码是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
str += " and detail.PalletNo like @palletno";
|
}
|
//判断库存状态是否为空
|
if (!string.IsNullOrEmpty(status))
|
{
|
str += " and detail.Status = @status";
|
}
|
//判断质检状态是否为空
|
if (!string.IsNullOrEmpty(inspectStatus))
|
{
|
str += " and detail.InspectStatus = @inspectstatus";
|
}
|
//判断货主编码是否为空
|
if (!string.IsNullOrEmpty(ownerNo))
|
{
|
str += " and detail.OwnerNo like @ownerNo";
|
}
|
//判断货主名称是否为空
|
if (!string.IsNullOrEmpty(ownerName))
|
{
|
str += " and detail.OwnerName like @ownerName";
|
}
|
if (!string.IsNullOrEmpty(houseNo))
|
{
|
str += " and house.WareHouseNo = @wareHouseNo";
|
}
|
if (!string.IsNullOrEmpty(areaNo))
|
{
|
str += " and area.AreaNo = @areaNo";
|
}
|
if (!string.IsNullOrEmpty(categoryNo))
|
{
|
str += " and cat.CategoryNo = @categoryNo";
|
}
|
if (!string.IsNullOrEmpty(type))
|
{
|
str += " and sku.Type = @type";
|
}
|
|
//排序
|
str += " order by detail.SkuNo,detail.PalletNo,detail.LotNo";
|
List<StockDetailDto> stockDetailsList = Db.Ado.SqlQuery<StockDetailDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料编码
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次
|
locatno = "%" + locatNo + "%", //储位地址
|
palletno = "%" + palletNo + "%", //托盘条码
|
status = status, //库存状态
|
inspectstatus = inspectStatus, //质检状态
|
ownerNo = "%" + ownerNo + "%", //货主编码
|
ownerName = "%" + ownerName + "%", //货主名称
|
wareHouseNo = houseNo,
|
areaNo = areaNo, //所属区域
|
categoryNo = categoryNo, //逻辑库区
|
type = type
|
});
|
foreach (var item in stockDetailsList)
|
{
|
if (!string.IsNullOrEmpty(item.LocatNo))
|
{
|
if (item.WareHouseNo == "W01")
|
{
|
item.PLCLocatNo = GetDjAdress(item.LocatNo.Substring(1, 2), item.LocatNo.Substring(0, 2), item.LocatNo.Substring(6, 2)).ToString() + item.LocatNo.Substring(2, 2) + item.LocatNo.Substring(4, 2);
|
}
|
}
|
|
}
|
return stockDetailsList;
|
}
|
|
#endregion
|
|
/// <summary>
|
/// 根据储位排获取跺机对应的地址排03010302
|
/// </summary>
|
/// <param name="road">排02</param>
|
/// <param name="pai">排03</param>
|
/// <param name="dept">深度02</param>
|
/// <returns></returns>
|
public static int GetDjAdress(string road, string pai, string dept)
|
{
|
var roadNum = int.Parse(road);
|
var paiNum = int.Parse(pai);
|
var deptNum = int.Parse(dept);
|
|
var paiVal = 0;
|
|
// 判断排奇偶数
|
if (paiNum % 2 == 0)
|
{
|
if (deptNum == 1)
|
{
|
paiVal = 3;
|
}
|
else
|
{
|
paiVal = 4;
|
}
|
}
|
else
|
{
|
if (deptNum == 1)
|
{
|
paiVal = 2;
|
|
}
|
else
|
{
|
paiVal = 1;
|
|
}
|
}
|
|
if (paiVal == 0)
|
{
|
throw new Exception("排转换失败");
|
}
|
return paiVal;
|
|
}
|
|
#region 低库存预警
|
|
/// <summary>
|
/// 低库存预警
|
/// </summary>
|
/// <param name="skuNo">物料号</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="type">物料类型</param>
|
/// <returns></returns>
|
public List<MateDataStockDto> GetInventoryWarning(string skuNo, string skuName, string lotNo, string type)
|
{
|
string str = "select mate.Id,mate.SkuNo,mate.SkuName,mate.Standard,mate.Type,stock.Qty,stock.LockQty,stock.FrozenQty,stock.Qty - stock.LockQty - Stock.FrozenQty ResidueQty,stock.LotNo from SysMaterials mate right join DataStock stock on mate.SkuNo = stock.SkuNo Where stock.IsDel = @isdel and stock.Qty - stock.LockQty - Stock.FrozenQty <= mate.LowInventory";
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and stock.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and stock.SkuName like @skuname";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and stock.LotNo like @lotno";
|
}
|
//判断物料类型是否为空
|
if (!string.IsNullOrEmpty(type))
|
{
|
str += " and mate.Type = @type";
|
}
|
//排序
|
str += " order by stock.SkuNo";
|
List<MateDataStockDto> stockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次
|
type, //物料类型
|
});
|
return stockList;
|
}
|
|
#endregion
|
|
#region 出入库报表
|
|
/// <summary>
|
/// 获取出入库总量
|
/// </summary>
|
/// <param name="skuNo">物料号</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="startTime">开始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns></returns>
|
public List<TotalRecordDto> GetTotalRecord(string skuNo, string skuName, string lotNo, string startTime, string endTime)
|
{
|
//入库
|
string rstr = "select LotNo,SkuNo,SkuName,SUM(Qty) RQty,SUM(FactQty) RFactQty,SUM(CompleteQty) RCompleteQty " +
|
"from BllArrivalNoticeDetail where IsDel = '0' and Id in (select ASNDetailNo from DataStockDetail " +
|
"where IsDel = '0' group by ASNDetailNo) ";
|
//出库
|
string cstr = "select LotNo,SkuNo,SkuName,SUM(Qty) CQty,SUM(FactQty) CFactQty,SUM(CompleteQty) CompleteQty,SUM(AllotQty) CAllotQty " +
|
"from BllExportNoticeDetail " +
|
"where IsDel = '0' and LotNo in (select LotNo from DataStock where IsDel = '0') ";
|
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
rstr += " and SkuNo like @skuno";
|
cstr += " and SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
rstr += " and SkuName like @skuname";
|
cstr += " and SkuName like @skuname";
|
}
|
//判断批次号是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
rstr += " and LotNo like @lotno";
|
cstr += " and LotNo like @lotno";
|
}
|
// liudl 时间不准确 暂时注释。
|
//if (!string.IsNullOrWhiteSpace(startTime))
|
//{
|
// rstr += " and UpdateTime >= @startTime";
|
// cstr += " and UpdateTime >= @startTime";
|
//}
|
//if (!string.IsNullOrWhiteSpace(endTime))
|
//{
|
// rstr += " and UpdateTime <= @endTime";
|
// cstr += " and UpdateTime <= @endTime";
|
//}
|
|
rstr += " group by LotNo,SkuNo,SkuName";
|
cstr += " group by LotNo,SkuNo,SkuName";
|
//入库
|
List<TotalRecordDto> totalHListData = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
//startTime = startTime,
|
//endTime = endTime
|
|
});
|
List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
//startTime = startTime,
|
//endTime = endTime
|
});
|
DataStock stock = new DataStock();
|
stock = Db.Queryable<DataStock>().First();
|
|
//合并数据
|
int a = 0;
|
decimal qty = 0;
|
foreach (var h in totalHListData)
|
{
|
if (a >= totalCListData.Count)
|
{
|
continue;
|
}
|
foreach (var c in totalCListData)
|
{
|
|
//判断出库是否拥有相同批次 且物料相同
|
if (h.LotNo == c.LotNo)
|
{
|
if (h.SkuNo == c.SkuNo && h.SkuName == c.SkuName)
|
{
|
stock = Db.Queryable<DataStock>().First(w=>w.SkuNo == h.SkuNo && w.SkuName == h.SkuName && w.LotNo == h.LotNo && w.IsDel == "0");
|
if (stock != null)
|
{
|
qty = stock.Qty;
|
}
|
h.CQty = c.CQty; //出库数量
|
h.CAllotQty = c.CAllotQty; //分配数量
|
h.CFactQty = c.CFactQty; //下架数量
|
h.CompleteQty = c.CompleteQty; //拣货数量
|
h.Qty = qty; //当前库存
|
|
|
a += 1;
|
|
break;
|
}
|
}
|
}
|
}
|
|
return totalHListData;
|
}
|
|
/// <summary>
|
/// 获取出入库记录
|
/// </summary>
|
/// <param name="skuNo">物料号</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="palletNo">托盘</param>
|
/// <param name="startTime">开始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns></returns>
|
public List<TotalRecordDto> GetDetailedRecord(string skuNo, string skuName, string lotNo, string palletNo,
|
string startTime, string endTime)
|
{
|
//入库
|
string rstr = "select notice.ASNNo,bind.LotNo,notice.SkuNo,notice.SkuName,bind.PalletNo,bind.Qty RQty," +
|
"bind.Qty RFactQty,bind.Qty RCompleteQty,bind.CompleteTime,notice.LotText,notice.SupplierLot,bind.CompleteTime " +
|
"from BllPalletBind bind left join BllArrivalNoticeDetail notice on bind.ASNDetailNo = notice.Id " +
|
"where bind.IsDel = '0' and notice.IsDel = '0' and bind.LotNo in (select LotNo from DataStock where IsDel = '0') ";
|
//出库
|
string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo," +
|
"allot.UpdateTime CCreateTime,allot.Qty CQty,allot.Qty CAllotQty,Allot.Qty CFactQty,Allot.CompleteQty CompleteQty," +
|
"allot.LotText,allot.SupplierLot " +
|
"from BllExportAllot allot left join BllExportNoticeDetail notice on allot.SODetailNo = notice.Id " +
|
"where allot.IsDel = '0' and notice.IsDel = '0' " +
|
"and notice.LotNo in (select LotNo from DataStock where IsDel = '0') ";
|
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
rstr += " and notice.SkuNo like @skuno";
|
cstr += " and allot.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
rstr += " and notice.SkuName like @skuname";
|
cstr += " and allot.SkuName like @skuname";
|
}
|
//判断批次号是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
rstr += " and notice.LotNo like @lotno";
|
cstr += " and allot.LotNo like @lotno";
|
}
|
//判断托盘是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
rstr += " and bind.PalletNo like @palletno";
|
cstr += " and allot.PalletNo like @palletno";
|
}
|
if (!string.IsNullOrWhiteSpace(startTime))
|
{
|
rstr += " and bind.CompleteTime >= @startTime";
|
cstr += " and allot.UpdateTime >= @startTime";
|
}
|
if (!string.IsNullOrWhiteSpace(endTime))
|
{
|
rstr += " and bind.CompleteTime <= @endTime";
|
cstr += " and allot.UpdateTime <= @endTime";
|
}
|
|
//rstr += " group by bind.LotNo,bind.SkuNo,bind.SkuName";
|
//cstr += " group by LotNo,SkuNo,SkuName";
|
//入库
|
List<TotalRecordDto> totalRList = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
palletno = "%" + palletNo + "%", //托盘号
|
startTime = startTime,
|
endTime = endTime
|
});
|
//出库
|
List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
palletno = "%" + palletNo + "%", //托盘号
|
startTime = startTime,
|
endTime = endTime
|
});
|
|
//合并
|
foreach (var c in totalCList)
|
{
|
int i = 0;
|
foreach (var r in totalRList)
|
{
|
i += 1;
|
//判断入库中是否存在出库中的批次物料托盘
|
if (r.PalletNo == c.PalletNo && r.LotNo == c.LotNo && r.SkuNo == c.SkuNo && r.SkuName == c.SkuName)
|
{
|
if (r.SONo != null)
|
{
|
if (r.SONo != c.SONo)
|
{
|
totalRList.Add(c);
|
}
|
}
|
if (c.CTaskNo != "" && r.SONo == null || r.SONo == c.SONo)
|
{
|
r.SONo = c.SONo; //出库单号
|
r.CQty += c.CQty; //出库数量
|
r.CAllotQty += c.CAllotQty; //分配数量
|
r.CFactQty += c.CFactQty; //下架数量
|
r.CompleteQty += c.CompleteQty; //拣货数量
|
r.CCreateTime = c.CCreateTime; //拣货时间
|
}
|
if (r.CompleteTime == null)
|
{
|
r.RCompleteQty = 0;
|
}
|
break;
|
}
|
if (i == totalRList.Count)
|
{
|
totalRList.Add(c);
|
break;
|
}
|
}
|
}
|
if (totalCList.Count == 0)
|
{
|
foreach (var r in totalRList)
|
{
|
if (r.CompleteTime == null)
|
{
|
r.RCompleteQty = 0;
|
continue;
|
}
|
}
|
}
|
|
return totalRList;
|
}
|
|
#endregion
|
|
#region 年度报表
|
|
/// <summary>
|
/// 获取年报表总表
|
/// </summary>
|
/// <param name="StartTime"></param>
|
/// <param name="EndTime"></param>
|
/// <returns></returns>
|
public List<SysMaterials> GetAssSoInfo(string StartTime, string EndTime)
|
{
|
try
|
{
|
string str = "select* from SysMaterials where SkuNo in (select a.SkuNo from BllArrivalNoticeDetail a inner join BllExportNoticeDetail b on a.SkuNo = b.SkuNo";
|
|
//判断开始时间是否为空
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
str += $" and a.CompleteTime >= '{StartTime}' and b.UpdateTime >= '{StartTime}'";
|
}
|
//判断结束时间是否为空
|
if (!string.IsNullOrWhiteSpace(EndTime))
|
{
|
EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
|
str += $" and a.CompleteTime < '{EndTime}' and b.UpdateTime < '{EndTime}'";
|
}
|
|
str += " group by a.SkuNo,b.SkuNo)";
|
|
var sku = Db.Ado.SqlQuery<SysMaterials>(str);
|
return sku;
|
}
|
catch (Exception ex)
|
{
|
|
throw new Exception(ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 获取年报表明细
|
/// </summary>
|
/// <param name="skuNo"></param>
|
/// <param name="StartTime"></param>
|
/// <param name="EndTime"></param>
|
/// <returns></returns>
|
public List<AssSoDateStockInfoDto> GetAssSoInfoDetail(string skuNo, string StartTime, string EndTime)
|
{
|
try
|
{
|
//用于合并数据
|
List<AssSoDateStockInfoDto> infolist = new List<AssSoDateStockInfoDto>();
|
//获取开始时间当天的结存数量
|
//DataStockInfo stockInfo = new DataStockInfo();
|
//获取物料理论重量
|
string weightstr = $"select Weight from SysMaterials where SkuNo = '{skuNo}'";
|
var weight = Db.Ado.SqlQuerySingle<decimal>(weightstr);
|
|
//判断开始时间是否为空
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
//string str = $"select * from DataStockInfo where IsDel = '0' and CONVERT(date,CreateTime) = '{StartTime}'";
|
//获取当天库存信息
|
//stockInfo = Db.Ado.SqlQuerySingle<DataStockInfo>(str);
|
}
|
//判断结束时间是否为空
|
if (!string.IsNullOrWhiteSpace(EndTime))
|
{
|
EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
|
}
|
decimal countjie = 0; //统计结存数量
|
////判断库存信息是否为空
|
//if (stockInfo != null)
|
//{
|
// countjie = stockInfo.BalanceQty; //统计结存数量
|
//}
|
//else if (stockInfo == null)
|
//{
|
//判断开始时间是否为空
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
//string StartTimelin = (Convert.ToDateTime(StartTime).AddDays(1)).ToString();
|
//获取出库数量
|
string str = $"select SUM(CompleteQty) as CompleteQtyc from BllExportNoticeDetail where IsDel = '0' and SkuNo = '{skuNo}' and Status != '0' and CreateTime < '{StartTime}'";
|
string CompleteQtyc = Db.Ado.SqlQuerySingle<string>(str);
|
//获取入库数量
|
str = $"select SUM(CompleteQty) as CompleteQtyr from BllArrivalNoticeDetail where IsDel = '0' and SkuNo = '{skuNo}' and Status != '0' and CreateTime < '{StartTime}'";
|
string CompleteQtyr = Db.Ado.SqlQuerySingle<string>(str);
|
|
//计算结存数量
|
countjie = int.Parse(CompleteQtyr) - int.Parse(CompleteQtyc);
|
|
}
|
|
//}
|
decimal yeara = 0; //年入库
|
decimal years = 0; //年出库
|
decimal montha = 0; //月入库
|
decimal months = 0; //月出库
|
DateTime createTime = DateTime.Now.AddYears(-1000); //时间
|
|
//获取有多少年
|
string yearstr = "select DATEPART(YEAR, CreateTime) SkuName from BllArrivalNoticeDetail where IsDel = '0' and Status = '2' ";
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
yearstr += $" and CreateTime >= '{StartTime}'";
|
}
|
if (!string.IsNullOrWhiteSpace(EndTime))
|
{
|
|
yearstr += $" and CreateTime < '{EndTime}'";
|
}
|
yearstr += "group by DATEPART(YEAR, CreateTime)";
|
var yearlist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(yearstr);
|
if (!string.IsNullOrWhiteSpace(StartTime) && !string.IsNullOrWhiteSpace(EndTime))
|
{
|
//判断开始时间年与结束时间年是否相等
|
var startyear = StartTime.Split('-');
|
var endyear = EndTime.Split("-");
|
}
|
foreach (var yearcount in yearlist)
|
{
|
//获取当前物料每年入库单据明细
|
string ruyearstr = $"select ASNNo,SkuNo,SkuName,CompleteQty as ASNQty,0 as ASNSOQty,CONVERT(date,CreateTime) as NoticeDateTime,DATEPART(MONTH,CreateTime) as month,CreateTime from BllArrivalNoticeDetail where IsDel = '0' and Status != '0' and SkuNo = '{skuNo}' and DATEPART(YEAR, CreateTime) = '{yearcount.SkuName}'";
|
//获取当前物料每年出库单据明细
|
string chuyearstr = $"select SONo,SkuNo,SkuName,CompleteQty as SOQty,0 as ASNSOQty,CONVERT(date,CreateTime) as NoticeDateTime,DATEPART(MONTH,CreateTime) as month,CreateTime from BllExportNoticeDetail where IsDel = '0' and Status != '0' and SkuNo = '{skuNo}' and DATEPART(YEAR, CreateTime) = '{yearcount.SkuName}'";
|
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
ruyearstr += $" and CreateTime >= '{StartTime}'";
|
chuyearstr += $" and CreateTime >= '{StartTime}'";
|
}
|
if (!string.IsNullOrWhiteSpace(EndTime))
|
{
|
//EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
|
ruyearstr += $" and CreateTime < '{EndTime}'";
|
chuyearstr += $" and CreateTime < '{EndTime}'";
|
}
|
|
ruyearstr += " order by CreateTime";
|
chuyearstr += " order by CreateTime";
|
//每年入库
|
List<AssSoDateStockInfoDto> ruinfolist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(ruyearstr);
|
//每年出库
|
List<AssSoDateStockInfoDto> chuinfolist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(chuyearstr);
|
|
//循环12个月
|
for (int i = 1; i <= 12; i++)
|
{
|
//临时入
|
List<AssSoDateStockInfoDto> linshiru = new List<AssSoDateStockInfoDto>();
|
//临时出
|
List<AssSoDateStockInfoDto> linshichu = new List<AssSoDateStockInfoDto>();
|
|
if (ruinfolist.Count > 0)
|
{
|
linshiru = ruinfolist.Where(a => a.month == i.ToString()).ToList(); //获取当月入库信息
|
}
|
if (chuinfolist.Count > 0)
|
{
|
linshichu = chuinfolist.Where(a => a.month == i.ToString()).ToList(); //获取当月出库信息
|
}
|
if (linshichu.Count > 0)
|
{
|
foreach (var h in linshichu)
|
{
|
linshiru.Add(h);
|
}
|
}
|
var data = linshiru.OrderBy(a => a.CreateTime).ToList();
|
|
//判断是否存在当月入库信息
|
if (linshiru.Count > 0)
|
{
|
|
foreach (var item in data)
|
{
|
item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
|
//总计结存数量
|
if (item.ASNNo != null)
|
{
|
//入库
|
countjie += item.ASNQty;
|
montha += item.ASNQty; //月入库
|
yeara += item.ASNQty; //年入库
|
}
|
else if (item.SONo != null)
|
{
|
//出库
|
countjie -= item.SOQty;
|
months += item.SOQty; //月出库
|
years += item.SOQty; //年出库
|
}
|
item.ASNSOQty = countjie; //结存数量
|
item.Weight = weight; //重量
|
infolist.Add(item);
|
|
if (createTime < item.CreateTime)
|
{
|
createTime = item.CreateTime; //时间
|
}
|
}
|
}
|
//判断是否存在当月出库信息
|
//if (linshichu.Count > 0)
|
//{
|
// //出库
|
// foreach (var item in linshichu)
|
// {
|
// item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
|
// countjie -= item.SOQty; //总计结存数量
|
// item.ASNSOQty = countjie; //结存数量
|
// item.Weight = weight; //重量
|
// infolist.Add(item);
|
// months += item.SOQty; //月出库
|
// years += item.SOQty; //年出库
|
// if (createTime < item.CreateTime)
|
// {
|
// createTime = item.CreateTime; //时间
|
// }
|
// }
|
//}
|
|
//判断是否有月出入库
|
if (linshiru.Count > 0 || linshichu.Count > 0)
|
{
|
//月信息
|
var addmonthinfo = new AssSoDateStockInfoDto()
|
{
|
ASNNo = "", // 入库单据
|
SONo = "", // 出库单据
|
|
SkuNo = "", // 物料编码
|
SkuName = "", // 物料名称
|
|
NoticeType = "本月累计", //统计类别
|
|
ASNQty = montha, // 入库数量
|
SOQty = months, // 出库数量
|
ASNSOQty = countjie, // 结存数量
|
|
Weight = weight, // 理论重量
|
|
NoticeDateTime = yearcount.SkuName + "年" + i + "月", // 单据时间
|
CreateTime = createTime, //时间
|
};
|
infolist.Add(addmonthinfo);
|
}
|
montha = 0; //月入库
|
months = 0; //月出库
|
}
|
//判断是否有年出入库
|
if (ruinfolist.Count > 0 || chuinfolist.Count > 0)
|
{
|
//年信息
|
var addyearinfo = new AssSoDateStockInfoDto()
|
{
|
ASNNo = "", // 入库单据
|
SONo = "", // 出库单据
|
|
SkuNo = "", // 物料编码
|
SkuName = "", // 物料名称
|
|
NoticeType = "本年累计", //统计类别
|
|
ASNQty = yeara, // 入库数量
|
SOQty = years, // 出库数量
|
ASNSOQty = countjie, // 结存数量
|
|
Weight = weight, // 理论重量
|
|
NoticeDateTime = yearcount.SkuName + "年", // 单据时间
|
CreateTime = createTime, //时间
|
};
|
infolist.Add(addyearinfo);
|
}
|
yeara = 0; //年入库
|
years = 0; //年出库
|
}
|
|
infolist = infolist.OrderBy(x => x.CreateTime).ToList();
|
return infolist;
|
|
}
|
catch (Exception ex)
|
{
|
|
throw new Exception(ex.Message);
|
}
|
}
|
|
|
#endregion
|
|
#region 导出库存
|
/// <summary>
|
/// 导出库存总量
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <returns></returns>
|
public List<MateDataStockDto> GetDataStockListDaoChu(string skuNo, string skuName)
|
{
|
string str = "select stock.SkuNo,stock.SkuName,stock.Standard,stock.Qty,stock.LockQty,stock.FrozenQty,(mate.Weight * stock.Qty) WeightSum from DataStock stock left join SysMaterials mate on stock.SkuNo = mate.SkuNo Where stock.IsDel = @isdel";
|
//判断物料编码是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and stock.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and stock.SkuName like @skuname";
|
}
|
//排序
|
str += " order by stock.SkuNo";
|
List<MateDataStockDto> StockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料编码
|
skuname = "%" + skuName + "%" //物料名称
|
});
|
|
//库存总量
|
List<MateDataStockDto> StockListDto = new List<MateDataStockDto>();
|
|
foreach (var item in StockList)
|
{
|
//判断库存总量是否拥有物料
|
if (StockListDto.Count > 0)
|
{
|
int i = 0;
|
//foreach循环库存总量
|
foreach (var dto in StockListDto)
|
{
|
//判断物料是否相同
|
if (dto.SkuNo == item.SkuNo)
|
{
|
var s = Convert.ToDecimal(dto.Qty) ;
|
var ss = Convert.ToDecimal(item.Qty);
|
dto.Qty = (Convert.ToDecimal(dto.Qty) + Convert.ToDecimal(item.Qty)).ToString();
|
dto.FrozenQty = (Convert.ToDecimal(dto.FrozenQty) + Convert.ToDecimal(item.FrozenQty)).ToString();
|
dto.LockQty = (Convert.ToDecimal(dto.LockQty) + Convert.ToDecimal(item.LockQty)).ToString();
|
dto.ResidueQty = (Convert.ToDecimal(dto.ResidueQty) + Convert.ToDecimal(item.ResidueQty)).ToString();
|
break;
|
}
|
i += 1;
|
//判断已有相同物料
|
if (i == StockListDto.Count)
|
{
|
StockListDto.Add(item);
|
break;
|
}
|
}
|
}
|
else
|
{
|
StockListDto.Add(item);
|
continue;
|
}
|
}
|
foreach (var item in StockListDto)
|
{
|
//物料编码，加上单引号是防止导出到excel自动把前面的0给去掉
|
if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
|
{
|
item.SkuNo = $"'{item.SkuNo}";
|
}
|
}
|
|
return StockListDto;
|
}
|
|
/// <summary>
|
/// 导出库存明细
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="locatNo">储位地址</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="status">库存状态</param>
|
/// <param name="inspectStatus">质检状态</param>
|
/// <returns></returns>
|
public List<StockDetailDto> GetInventoryList1DaoChu(string skuNo, string skuName, string lotNo, string locatNo, string palletNo, string status, string inspectStatus)
|
{
|
string str = "select detail.*,house.WareHouseName as WareHouseName,roadway.RoadwayName as RoadwayName from DataStockDetail detail left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo left join SysWareHouse house on detail.WareHouseNo = house.WareHouseNo Where detail.IsDel = @isdel";
|
//判断物料编码是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and detail.SkuNo = @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and detail.SkuName = @skuname";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and detail.LotNo = @lotno";
|
}
|
//判断储位地址是否为空
|
if (!string.IsNullOrEmpty(locatNo))
|
{
|
str += " and detail.LocatNo = @locatno";
|
}
|
//判断托盘条码是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
str += " and detail.PalletNo = @palletno";
|
}
|
//判断库存状态是否为空
|
if (!string.IsNullOrEmpty(status))
|
{
|
str += " and detail.Status = @status";
|
}
|
//判断质检状态是否为空
|
if (!string.IsNullOrEmpty(inspectStatus))
|
{
|
str += " and detail.InspectStatus = @inspectstatus";
|
}
|
//排序
|
str += " order by detail.SkuNo,detail.PalletNo,detail.LotNo";
|
List<StockDetailDto> stockDetailsList = Db.Ado.SqlQuery<StockDetailDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = skuNo, //物料编码
|
skuname = skuName, //物料名称
|
lotno = lotNo, //批次
|
locatno = locatNo, //储位地址
|
palletno = palletNo, //托盘条码
|
status = status, //库存状态
|
inspectstatus = inspectStatus //质检状态
|
});
|
foreach (var item in stockDetailsList)
|
{
|
//储位地址，加上单引号是防止导出到excel自动把前面的0给去掉
|
if (!string.IsNullOrEmpty(item.LocatNo) && item.LocatNo.Substring(0, 1) == "0")
|
{
|
item.LocatNo = $"'{item.LocatNo}";
|
}
|
//物料编码
|
if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
|
{
|
item.SkuNo = $"'{item.SkuNo}";
|
}
|
//库存状态
|
switch (item.Status)
|
{
|
case "0":
|
item.Status = "待分配";
|
break;
|
case "1":
|
item.Status = "部分分配";
|
break;
|
case "2":
|
item.Status = "已分配";
|
break;
|
case "3":
|
item.Status = "盘点锁定";
|
break;
|
case "4":
|
item.Status = "移库锁定";
|
break;
|
default:
|
break;
|
}
|
//质检状态
|
switch (item.InspectStatus)
|
{
|
case "0":
|
item.InspectStatus = "待检验";
|
break;
|
case "1":
|
item.InspectStatus = "检验合格";
|
break;
|
case "2":
|
item.InspectStatus = "不合格";
|
break;
|
case "4":
|
item.InspectStatus = "放置期";
|
break;
|
default:
|
break;
|
}
|
|
}
|
return stockDetailsList;
|
}
|
#endregion
|
|
#region AGV小车任务完成更新库存
|
|
/// <summary>
|
/// AGV入库完成
|
/// </summary>
|
/// <param name="TaskCode">任务号</param>
|
/// <param name="wcsurl">下发WCS放货完成地址</param>
|
/// <returns></returns>
|
public void ArriveFinish(string TaskCode,string wcsurl)
|
{
|
try
|
{
|
var palletNo = Db.Queryable<LogTask>().First(m => m.TaskNo == TaskCode && m.OrderType == "0" && m.Status == "1");//获取正在执行的任务信息
|
if (palletNo == null)
|
{
|
throw new Exception("此任务已完成");
|
|
}
|
var storageLocat = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat); //获取库位信息
|
var stockDetail = Db.Queryable<DataStockDetail>().First(w => w.IsDel == "0" && w.PalletNo == palletNo.PalletNo);
|
//开启事务
|
Db.BeginTran();
|
|
//修改起始库位状态
|
storageLocat.Status = "0";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中
|
Db.Updateable(storageLocat).ExecuteCommand();
|
|
//修改任务状态
|
palletNo.Status = "2"; //已完成
|
Db.Updateable(palletNo).ExecuteCommand();
|
|
|
if (stockDetail !=null)
|
{
|
//修改库存明细
|
stockDetail.Status = "0";//待分配
|
stockDetail.LocatNo = "";
|
stockDetail.AreaNo = "";
|
stockDetail.RoadwayNo = "";
|
stockDetail.WareHouseNo = "";
|
Db.Updateable(stockDetail).ExecuteCommand();
|
}
|
|
|
#region 下发WCS放货完成
|
var data = new
|
{
|
Port = palletNo.EndLocat
|
};
|
var jsonData = JsonConvert.SerializeObject(data);
|
string response = "";
|
try
|
{
|
var time1 = DateTime.Now;//发送时间 .ToString("yyyy-MM-dd HH:mm:ss")
|
response = HttpHelper.DoPost(wcsurl, jsonData, "下发给WCS放货完成命令", "WCS");
|
var time2 = DateTime.Now;//返回时间 .ToString("yyyy-MM-dd HH:mm:ss")
|
|
//////解析返回数据
|
var res = JsonConvert.DeserializeObject<WcsModel>(response);
|
if (res.StatusCode == -1)
|
{
|
throw new Exception("放货失败，WCS返回信息错误");
|
}
|
}
|
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
|
#endregion
|
palletNo.IsCancel = 0;
|
palletNo.IsSend = 0;
|
palletNo.IsFinish = 0;
|
Db.Updateable(palletNo).ExecuteCommand();
|
|
Db.CommitTran();
|
|
}
|
catch (Exception ex)
|
{
|
Db.RollbackTran();
|
throw new Exception("AGV返回入库完成信号处理错误，错误信息：" + ex);
|
}
|
}
|
|
/// <summary>
|
/// AGV出库完成
|
/// </summary>
|
/// <param name="TaskCode">任务号</param>
|
/// <param name="url">反馈MES备料完成地址</param>
|
/// <returns></returns>
|
public void SoFinish(string TaskCode,string url)
|
{
|
try
|
{
|
var palletNo = Db.Queryable<LogTask>().First(m => m.TaskNo == TaskCode && m.OrderType == "1" && m.Status == "1");//获取正在执行的任务信息
|
if (palletNo == null)
|
{
|
throw new Exception("此任务已完成");
|
}
|
var storageLocat = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.EndLocat); //获取库位信息
|
var storageStart = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat); //获取库位信息
|
var stockDetail = Db.Queryable<DataStockDetail>().First(w => w.IsDel == "0" && w.PalletNo == palletNo.PalletNo); //获取库存信息
|
|
//开启事务
|
Db.BeginTran();
|
//修改目的库位状态
|
storageLocat.Status = "1";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中
|
Db.Updateable(storageLocat).ExecuteCommand();
|
|
//修改任务状态
|
palletNo.Status = "2"; //已完成
|
Db.Updateable(palletNo).ExecuteCommand();
|
|
//修改库存明细
|
stockDetail.Status = "2";// 0:待分配 1:部分分配 2：已分配 3：盘点锁定： 4移库锁定
|
stockDetail.LocatNo = storageLocat.LocatNo;
|
stockDetail.AreaNo = storageLocat.AreaNo;
|
stockDetail.RoadwayNo = storageLocat.RoadwayNo;
|
stockDetail.WareHouseNo = storageLocat.WareHouseNo;
|
Db.Updateable(stockDetail).ExecuteCommand();
|
|
|
Db.CommitTran();
|
|
}
|
catch (Exception ex )
|
{
|
|
throw new Exception("AGV返回出库完成信号处理错误，错误信息：" + ex);
|
}
|
}
|
|
|
//完成接口修改（未完成）
|
//try
|
//{
|
// var palletNo = Db.Queryable<LogTask>().First(m => m.TaskNo == TaskCode && m.Status == "1");//获取正在执行的任务信息
|
// if (palletNo == null)
|
// {
|
// throw new Exception("此任务已完成");
|
// }
|
// var storageLocat = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.EndLocat); //获取库位信息
|
// var storageStart = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat); //获取库位信息
|
// var stockDetail = Db.Queryable<DataStockDetail>().Where(w => w.IsDel == "0" && w.PalletNo == palletNo.PalletNo).ToList();
|
// if (stockDetail.Count == 0)
|
// {
|
// throw new Exception("托盘上物料库存明细信息不存在,请检查!");
|
// }
|
// //开启事务
|
// Db.BeginTran();
|
// if (storageLocat != null)
|
// {
|
// if (storageLocat.AreaNo == "B12")//车间缓存位需要通知MES
|
// {
|
// var data = new List<RequertBeiliaoInfoModel>();
|
// //更改库存明细
|
// foreach (var item in stockDetail)
|
// {
|
// item.LocatNo = item.LocatNo;//储位更改
|
// item.WareHouseNo = item.WareHouseNo;//所属仓库更改
|
// item.RoadwayNo = item.RoadwayNo;//所属巷道更改
|
// item.AreaNo = item.AreaNo;//所属区域更改
|
|
// Db.Updateable(item).ExecuteCommand();
|
|
|
// if (string.IsNullOrWhiteSpace(item.SONo))
|
// {
|
// throw new Exception("当前托盘不是拼托出库托盘");
|
// }
|
// var boxInfo = Db.Queryable<DataBoxInfo>().Where(m => m.IsDel == "0" && m.StockDetailId == item.Id).ToList();
|
// if (boxInfo.Count == 0)
|
// {
|
// throw new Exception("托盘上物料箱码信息不存在,请检查!");
|
// }
|
// //var boxno = boxInfo.GroupBy(w => w.BoxNo).ToList();
|
// var boxno = Db.Queryable<DataBoxInfo>().Where(m => m.IsDel == "0" && m.StockDetailId == item.Id).GroupBy(m => new
|
// {
|
// m.BoxNo
|
// }).Select(it => new DataBoxInfo()
|
// {
|
// BoxNo = it.BoxNo
|
// }).ToList();
|
|
// //记录托盘上信息给MES
|
// foreach (var item2 in boxno)
|
// {
|
// var a = item.ProductionTime.ToString();
|
// data.Add(new RequertBeiliaoInfoModel()
|
// {
|
// no = item2.BoxNo,
|
// materiel_no = item.SkuNo,
|
// materiel_name = item.SkuName,
|
// qty = item.Qty,
|
// batch = item.LotNo,
|
// producttime = item.ProductionTime.ToString().Substring(0, 10),
|
// expiry = item.ExpirationTime.ToString().Substring(0, 10)
|
|
// });
|
// }
|
// //库存箱码明细删除
|
// Db.Deleteable(boxInfo).ExecuteCommand();
|
// //删除库存托盘信息
|
// Db.Deleteable(item).ExecuteCommand();
|
// //更改库存总表
|
// var stock = Db.Queryable<DataStock>().First(w => w.IsDel == "0" && w.SkuNo == item.SkuNo && w.LotNo == item.LotNo);
|
// stock.LockQty -= (decimal)item.Qty;
|
// stock.Qty -= (decimal)item.Qty;
|
// Db.Updateable(stock).ExecuteCommand();
|
// //更改托盘状态
|
// var pallet = Db.Queryable<SysPallets>().First(m => m.IsDel == "0" && m.PalletNo == palletNo);
|
// if (pallet != null)
|
// {
|
// pallet.Status = "0";
|
// Db.Updateable(pallet).ExecuteCommand();
|
// }
|
|
// }
|
|
// //获取令牌
|
// //var token = new Token().GetMesToken(mesTokenUrl);
|
// var token = "";
|
// Dictionary<string, string> mesDic = new Dictionary<string, string>()
|
// {
|
// {"Authorization",token }
|
// };
|
// var endlono = palletNo.EndLocat;
|
// //缓存库位转换
|
// switch (palletNo.EndLocat.ToString().Substring(4, 1))
|
// {
|
// case "1":
|
// endlono = "Y003_00" + palletNo.EndLocat.ToString().Substring(6, 1);
|
// break;
|
// case "2":
|
// endlono = "Y138_0" + palletNo.EndLocat.ToString().Substring(5, 2);
|
// break;
|
// case "3":
|
// endlono = "Y128_0" + palletNo.EndLocat.ToString().Substring(5, 2);
|
// break;
|
// default:
|
// break;
|
// }
|
// var mescode = Db.Queryable<BllExportNotice>().Where(w => w.SONo == stockDetail.First().SONo).First();
|
// var mesData = new RequertBeiliaoModel()
|
// {
|
// morder_no = mescode.OrderCode,
|
// pallet = stockDetail.First().PalletNo,
|
// layer_no = endlono,
|
// items = data
|
// };
|
// var jsonData = JsonConvert.SerializeObject(mesData);
|
// //调用接口
|
// var response = HttpHelper.DoPost(url, jsonData, "备料完成运至缓存区反馈至MES", "MES", mesDic);
|
|
// var obj = JsonConvert.DeserializeObject<MesModel>(response);//解析返回数据
|
// if (obj.status != "success")
|
// {
|
// throw new Exception("备料同步MES失败：" + obj.message);
|
// }
|
|
|
// }
|
// //修改目的库位状态
|
// storageLocat.Status = "1";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中
|
// Db.Updateable(storageLocat).ExecuteCommand();
|
|
// //修改任务状态
|
// palletNo.Status = "2"; //已完成
|
// Db.Updateable(palletNo).ExecuteCommand();
|
|
// //修改库存明细
|
// item.Status = "2";// 0:待分配 1:部分分配 2：已分配 3：盘点锁定： 4移库锁定
|
// stockDetail.LocatNo = storageLocat.LocatNo;
|
// stockDetail.AreaNo = storageLocat.AreaNo;
|
// stockDetail.RoadwayNo = storageLocat.RoadwayNo;
|
// stockDetail.WareHouseNo = storageLocat.WareHouseNo;
|
// Db.Updateable(stockDetail).ExecuteCommand();
|
// }
|
// Db.CommitTran();
|
|
//}
|
/// <summary>
|
/// AGV移库完成
|
/// </summary>
|
/// <param name="TaskCode">任务号</param>
|
/// <param name="url">反馈MES备料完成地址</param>
|
/// <returns></returns>
|
public void MoveFinish(string TaskCode,string url)
|
{
|
try
|
{
|
var palletNo = Db.Queryable<LogTask>().First(m => m.TaskNo == TaskCode && m.OrderType == "3" && m.Status == "1");//获取正在执行的任务信息
|
if (palletNo == null)
|
{
|
throw new Exception("此任务已完成");
|
}
|
var startLocat = new SysStorageLocat();
|
|
var endLocat = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.EndLocat); //获取目的库位信息
|
if (endLocat == null)
|
{
|
throw new Exception("未找到相应的目的库位");
|
}
|
var storageStart = new SysStorageLocat();
|
|
int isstock = 1;
|
var bindDetail = new BllPalletBind();
|
var sysPanlno = new SysPallets();
|
|
var stockDetail = Db.Queryable<DataStockDetail>().Where(w => w.IsDel == "0" && w.PalletNo == palletNo.PalletNo).ToList(); //获取库存信息
|
if (stockDetail.Count == 0)
|
{
|
//未找到库存后需要判断是否是空托盘移库
|
isstock = 0;
|
bindDetail = Db.Queryable<BllPalletBind>().OrderByDescending(w=>w.Id).First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat);//获取组托盘信息
|
if (bindDetail == null)
|
{
|
bindDetail = Db.Queryable<BllPalletBind>().OrderByDescending(w => w.Id).First(w => w.IsDel == "0" && w.LocatNo == palletNo.EndLocat);//获取组托盘信息
|
if (bindDetail == null)
|
{
|
throw new Exception("未找到空托盘组托信息");
|
}
|
}
|
}
|
else
|
{
|
storageStart = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat); //获取起始库位信息
|
if (storageStart == null)
|
{
|
isstock = 2; //库口到缓存位任务
|
}
|
}
|
//var soAllot = Db.Queryable<BllExportAllot>().First(m => m.IsDel == "0" && m.PalletNo == palletNo.PalletNo);//获取分配信息
|
|
|
//开启事务
|
Db.BeginTran();
|
|
//修改目的库位状态
|
endLocat.Status = "1";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中
|
Db.Updateable(endLocat).ExecuteCommand();
|
|
//修改任务状态
|
palletNo.Status = "2"; //已完成
|
Db.Updateable(palletNo).ExecuteCommand();
|
|
if (isstock == 1)
|
{
|
startLocat = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat);
|
//修改起始库位状态
|
startLocat.Status = "0";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中
|
Db.Updateable(startLocat).ExecuteCommand();
|
|
|
#region
|
var data = new List<RequertBeiliaoInfoModel>();
|
|
//更改库存明细
|
foreach (var item in stockDetail)
|
{
|
item.LocatNo = endLocat.LocatNo;//储位更改
|
item.WareHouseNo = endLocat.WareHouseNo;//所属仓库更改
|
item.RoadwayNo = endLocat.RoadwayNo;//所属巷道更改
|
item.AreaNo = endLocat.AreaNo;//所属区域更改
|
|
Db.Updateable(item).ExecuteCommand();
|
|
if (endLocat != null && endLocat.AreaNo == "B12") //是否是3楼缓存区 是：删除库存
|
{
|
if (string.IsNullOrWhiteSpace(item.SONo))
|
{
|
throw new Exception("当前托盘不是拼托出库托盘");
|
}
|
var boxInfo = Db.Queryable<DataBoxInfo>().Where(m => m.IsDel == "0" && m.StockDetailId == item.Id).ToList();
|
if (boxInfo.Count == 0)
|
{
|
throw new Exception("托盘上物料箱码信息不存在,请检查!");
|
}
|
//var boxno = boxInfo.GroupBy(w => w.BoxNo).ToList();
|
var boxno = Db.Queryable<DataBoxInfo>().Where(m => m.IsDel == "0" && m.StockDetailId == item.Id).GroupBy(m => new
|
{
|
m.BoxNo,
|
m.ProductionTime,
|
m.ExpirationTime,
|
}).Select(it => new DataBoxInfo()
|
{
|
BoxNo = it.BoxNo,
|
ProductionTime = it.ProductionTime,
|
ExpirationTime = it.ExpirationTime,
|
}).ToList();
|
|
//记录托盘上信息给MES
|
foreach (var item2 in boxno)
|
{
|
//var a = item.ProductionTime.ToString();
|
data.Add(new RequertBeiliaoInfoModel()
|
{
|
no = item2.BoxNo,
|
materiel_no = item.SkuNo,
|
materiel_name = item.SkuName,
|
qty = item.Qty,
|
batch = item.LotNo,
|
producttime = item2.ProductionTime.ToString().Substring(0, 10),
|
expiry = item2.ExpirationTime.ToString().Substring(0, 10)
|
|
});
|
}
|
//库存箱码明细删除
|
Db.Deleteable(boxInfo).ExecuteCommand();
|
//删除库存托盘信息
|
Db.Deleteable(item).ExecuteCommand();
|
//更改库存总表
|
var stock = Db.Queryable<DataStock>().First(w => w.IsDel == "0" && w.SkuNo == item.SkuNo && w.LotNo == item.LotNo);
|
stock.LockQty -= (decimal)item.Qty;
|
stock.Qty -= (decimal)item.Qty;
|
Db.Updateable(stock).ExecuteCommand();
|
//更改托盘状态
|
var pallet = Db.Queryable<SysPallets>().First(m => m.IsDel == "0" && m.PalletNo == palletNo.PalletNo);
|
if (pallet != null)
|
{
|
pallet.Status = "0";
|
Db.Updateable(pallet).ExecuteCommand();
|
}
|
}
|
}
|
|
|
if (endLocat != null && endLocat.AreaNo == "B12")
|
{
|
//获取令牌
|
//var token = new Token().GetMesToken(mesTokenUrl);
|
var token = "";
|
Dictionary<string, string> mesDic = new Dictionary<string, string>()
|
{
|
{"Authorization",token }
|
};
|
var endlono = endLocat.LocatNo;
|
//缓存库位转换
|
switch (endLocat.LocatNo.ToString().Substring(4, 1))
|
{
|
case "1":
|
endlono = "Y003_0" + endLocat.LocatNo.ToString().Substring(5, 2);
|
break;
|
|
}
|
var mescode = Db.Queryable<BllExportNotice>().Where(w => w.SONo == stockDetail.First().SONo).First();
|
var mesData = new RequertBeiliaoModel()
|
{
|
morder_no = mescode.OrderCode,
|
pallet = stockDetail.First().PalletNo,
|
layer_no = endlono,
|
items = data
|
};
|
var jsonData = JsonConvert.SerializeObject(mesData);
|
//调用接口
|
var response = HttpHelper.DoPost(url, jsonData, "备料完成运至缓存区反馈至MES", "MES", mesDic);
|
|
var obj = JsonConvert.DeserializeObject<MesModel>(response);//解析返回数据
|
if (obj.status != "success")
|
{
|
throw new Exception("备料同步MES失败：" + obj.message);
|
}
|
|
}
|
#endregion
|
}
|
else if (isstock == 0)
|
{
|
//修改组托信息
|
if (bindDetail.WareHouseNo == "W01") //1、空托盘垛到空托盘收集器；
|
{
|
bindDetail.WareHouseNo = "W02";
|
bindDetail.LocatNo = endLocat.LocatNo;
|
bindDetail.RoadwayNo = "";
|
//修改起始库位状态
|
startLocat.Status = "0";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中
|
Db.Updateable(startLocat).ExecuteCommand();
|
}
|
else//2、拣货叫空托盘，小车完成一次，扣减一个空托盘垛上数量
|
{
|
bindDetail.Qty = bindDetail.Qty - 1;
|
if (bindDetail.Qty == 0)//数量为0后更改组托状态和托盘使用状态
|
{
|
bindDetail.Status = "2";
|
bindDetail.IsDel = "1";
|
//修改起始库位状态
|
startLocat.Status = "0";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中
|
Db.Updateable(startLocat).ExecuteCommand();
|
}
|
else
|
{
|
bindDetail.LocatNo = startLocat.LocatNo;//?????拣货位托盘如何处理，拣货位是否需要更改状态，是否需要增加库存；
|
}
|
}
|
Db.Updateable(bindDetail).ExecuteCommand();
|
|
}
|
else //库口移库到缓存位
|
{
|
|
foreach (var item in stockDetail)
|
{
|
item.LocatNo = endLocat.LocatNo;//储位更改
|
item.WareHouseNo = endLocat.WareHouseNo;//所属仓库更改
|
item.RoadwayNo = endLocat.RoadwayNo;//所属巷道更改
|
item.AreaNo = endLocat.AreaNo;//所属区域更改
|
|
Db.Updateable(item).ExecuteCommand();
|
}
|
}
|
palletNo.IsCancel = 0;
|
palletNo.IsSend = 0;
|
palletNo.IsFinish = 0;
|
Db.Updateable(palletNo).ExecuteCommand();
|
Db.CommitTran();
|
}
|
catch (Exception ex)
|
{
|
Db.RollbackTran();
|
throw new Exception("AGV返回移库完成信号处理错误，错误信息："+ex);
|
}
|
}
|
#endregion
|
|
#region 数字孪生系统反馈信息
|
|
/// <summary>
|
/// 反馈数字孪生系统库存信息
|
/// </summary>
|
/// <param name=""></param>
|
/// <returns></returns>
|
public List<ReLocateDataModel> GetLocateList()
|
{
|
try
|
{
|
|
//库存信息
|
var stockDetailsList = Db.Queryable<DataStockDetail>().ToList();
|
var sql = "select LocatNo,PalletNo,SkuName,Standard,LotNo,InspectStatus,Qty,ExpirationTime as Warranty from DataStockDetail ";
|
|
List<ReLocateDataModel> list = Db.Ado.SqlQuery<ReLocateDataModel>(sql);
|
|
//if (stockDetailsList.Count == 0)
|
//{
|
// return list;
|
//}
|
//foreach (var item in stockDetailsList)
|
//{
|
// list.Add(item);
|
|
//}
|
//for (int i = 0; i < stockDetailsList.Count; i++)
|
//{
|
|
// list.Add(stockDetailsList);
|
// list[i].LocatNo = stockDetailsList[i].LocatNo; //库位
|
// list[i].PalletNo = stockDetailsList[i].PalletNo; //托盘号
|
// list[i].SkuName = stockDetailsList[i].SkuName; //物料名称
|
// list[i].Stadard = stockDetailsList[i].Standard; //规格
|
// list[i].LotNo = stockDetailsList[i].LotNo; //批次
|
// list[i].InspectStatus = stockDetailsList[i].InspectStatus; //质量状态
|
// list[i].Qty = stockDetailsList[i].Qty.ToString(); //库存数量
|
// list[i].Warranty = stockDetailsList[i].ExpirationTime.ToString(); //有效期 ??过期时间，是否要更改
|
//}
|
return list;
|
}
|
catch (Exception ex)
|
{
|
|
throw new Exception("返回库存信息有误，错误信息：" + ex);
|
}
|
}
|
|
|
|
/// <summary>
|
/// 反馈数字孪生系统操作信息
|
/// </summary>
|
/// <param name=""></param>
|
/// <returns></returns>
|
public List<ReLogDataModel> GetLogDataList()
|
{
|
try
|
{
|
|
//库存信息
|
var stockDetailsList = Db.Queryable<DataStockDetail>().ToList();
|
var sql = "select LocatNo,PalletNo,SkuName,Standard,LotNo,InspectStatus,Qty,ExpirationTime as Warranty from DataStockDetail ";
|
|
|
var item2 = Expressionable.Create<LogOperationSO>()
|
.And(it => it.IsDel == "0")
|
.ToExpression();//注意 这一句 不能少
|
|
var list2 = Db.Queryable<LogOperationSO>().Where(item2)
|
.LeftJoin<SysDictionary>((it, dic) => it.Type == dic.Id.ToString())
|
.LeftJoin<SysUserInfor>((it, dic, users) => it.CreateUser == users.Id)
|
.Select((it, dic, users) => new OperationDto()
|
{
|
Id = it.Id,
|
ParentNo = it.ParentNo,
|
MenuNo = it.MenuNo,
|
MenuName = it.MenuName,
|
FkNo = it.FkNo,
|
Type = dic.DictName,
|
Msg = it.Msg,
|
CreateTime = it.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
|
CreateUserName = users.RealName,
|
});
|
|
List<ReLogDataModel> list = Db.Ado.SqlQuery<ReLogDataModel>(sql);
|
|
return list;
|
}
|
catch (Exception ex)
|
{
|
|
throw new Exception("返回库存信息有误，错误信息：" + ex);
|
}
|
}
|
|
|
/// <summary>
|
/// 查询操作日志
|
/// </summary>
|
/// <param name="menuName">菜单名称</param>
|
/// <param name="type">类型</param>
|
/// <returns></returns>
|
public List<OperationDto> ReLogData()
|
{
|
try
|
{
|
var total = 0;
|
#region asn
|
|
var item = Expressionable.Create<LogOperationASN>()
|
.And(it => it.IsDel == "0")
|
.ToExpression();//注意 这一句 不能少
|
|
var list = Db.Queryable<LogOperationASN>().Where(item)
|
.LeftJoin<SysDictionary>((it, dic) => it.Type == dic.Id.ToString())
|
.LeftJoin<SysUserInfor>((it, dic, users) => it.CreateUser == users.Id)
|
.Select((it, dic, users) => new OperationDto()
|
{
|
Id = it.Id,
|
ParentNo = it.ParentNo,
|
MenuNo = it.MenuNo,
|
MenuName = it.MenuName,
|
FkNo = it.FkNo,
|
Type = dic.DictName,
|
Msg = it.Msg,
|
CreateTime = it.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
|
CreateUserName = users.RealName,
|
});
|
|
#endregion
|
|
#region so
|
|
var item2 = Expressionable.Create<LogOperationSO>()
|
.And(it => it.IsDel == "0")
|
.ToExpression();//注意 这一句 不能少
|
|
var list2 = Db.Queryable<LogOperationSO>().Where(item2)
|
.LeftJoin<SysDictionary>((it, dic) => it.Type == dic.Id.ToString())
|
.LeftJoin<SysUserInfor>((it, dic, users) => it.CreateUser == users.Id)
|
.Select((it, dic, users) => new OperationDto()
|
{
|
Id = it.Id,
|
ParentNo = it.ParentNo,
|
MenuNo = it.MenuNo,
|
MenuName = it.MenuName,
|
FkNo = it.FkNo,
|
Type = dic.DictName,
|
Msg = it.Msg,
|
CreateTime = it.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
|
CreateUserName = users.RealName,
|
});
|
|
#endregion
|
|
#region cr
|
|
var item3 = Expressionable.Create<LogOperationCR>()
|
.And(it => it.IsDel == "0")
|
.ToExpression();//注意 这一句 不能少
|
|
var list3 = Db.Queryable<LogOperationCR>().Where(item3)
|
.LeftJoin<SysDictionary>((it, dic) => it.Type == dic.Id.ToString())
|
.LeftJoin<SysUserInfor>((it, dic, users) => it.CreateUser == users.Id)
|
.Select((it, dic, users) => new OperationDto()
|
{
|
Id = it.Id,
|
ParentNo = it.ParentNo,
|
MenuNo = it.MenuNo,
|
MenuName = it.MenuName,
|
FkNo = it.FkNo,
|
Type = dic.DictName,
|
Msg = it.Msg,
|
CreateTime = it.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
|
CreateUserName = users.RealName,
|
});
|
|
#endregion
|
|
#region sys
|
|
var item4 = Expressionable.Create<LogOperationSys>()
|
.ToExpression();//注意 这一句 不能少
|
|
var list4 = Db.Queryable<LogOperationSys>().Where(item4)
|
.LeftJoin<SysDictionary>((it, dic) => it.Type == dic.Id.ToString())
|
.LeftJoin<SysUserInfor>((it, dic, users) => it.CreateUser == users.Id)
|
.Select((it, dic, users) => new OperationDto()
|
{
|
Id = it.Id,
|
ParentNo = it.ParentNo,
|
MenuNo = it.MenuNo,
|
MenuName = it.MenuName,
|
FkNo = it.FkNo,
|
Type = dic.DictName,
|
Msg = it.Msg,
|
CreateTime = it.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
|
CreateUserName = users.RealName,
|
});
|
|
#endregion
|
|
var data = Db.UnionAll(list, list2, list3, list4).OrderByDescending(it => it.CreateTime);
|
return data.OrderByDescending(m => m.CreateTime).ToList();
|
}
|
catch (Exception e)
|
{
|
throw new Exception(e.Message);
|
}
|
}
|
#endregion
|
}
|
}
|
