using System.Data;
|
using System.Data.SqlClient;
|
using Masuit.Tools;
|
using NewPdaSqlServer.DB;
|
using NewPdaSqlServer.Dto.service;
|
using NewPdaSqlServer.entity;
|
using NewPdaSqlServer.util;
|
using SqlSugar;
|
|
namespace NewPdaSqlServer.service.Wom;
|
|
public class WwGdManager : Repository<WwGd>
|
{
|
public ProductionPickDto ScanCode(WarehouseQuery query)
|
{
|
var _strMsg = "";
|
var _intSum = "";
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
if (query.userName.IsNullOrEmpty()) throw new Exception("用户名不允许为空");
|
if (query.daa001.IsNullOrEmpty()) throw new Exception("领料单号不允许为空");
|
if (query.barcode.IsNullOrEmpty()) throw new Exception("条码不允许为空");
|
|
using (var cmd = new SqlCommand("[prc_pda_WWLL]", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters =
|
{
|
new("@outMsg", SqlDbType.NVarChar, 300),
|
new("@outSum", SqlDbType.NVarChar, 300),
|
new("@barcode_num", SqlDbType.NVarChar, 300),
|
new("@split_num", SqlDbType.NVarChar, 300),
|
new("@c_User", query.userName),
|
new("@p_biLL_no", query.daa001),
|
new("@p_item_barcode", query.barcode)
|
};
|
parameters[0].Direction = ParameterDirection.Output;
|
parameters[1].Direction = ParameterDirection.Output;
|
parameters[2].Direction = ParameterDirection.Output;
|
parameters[3].Direction = ParameterDirection.Output;
|
foreach (var parameter in parameters)
|
cmd.Parameters.Add(parameter);
|
cmd.ExecuteNonQuery();
|
_strMsg = parameters[0].Value.ToString();
|
_intSum = parameters[1].Value.ToString();
|
|
var barcodeNum = parameters[2].Value.ToString();
|
var splitNum = parameters[3].Value.ToString();
|
|
var result = Convert.ToInt32(_intSum);
|
if (result <= 0) throw new Exception(_strMsg);
|
|
var dto = new ProductionPickDto
|
{
|
daa001 = query.daa001,
|
barcodeNum = barcodeNum,
|
splitNum = splitNum,
|
barcode = query.barcode
|
};
|
|
return dto;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
//prC_pda_SCLL_CF
|
public ProductionPickDto ScanCodeCF(WarehouseQuery query)
|
{
|
if (query.userName.IsNullOrEmpty()) throw new Exception("用户名不允许为空");
|
if (query.daa001.IsNullOrEmpty()) throw new Exception("领料单号不允许为空");
|
if (query.barcode.IsNullOrEmpty()) throw new Exception("条码不允许为空");
|
|
if (query.Num is null or 0) throw new Exception("条码拆分数不允许为空或者为0");
|
|
var _strMsg = "";
|
var _intSum = "";
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (var cmd = new SqlCommand("[prc_pda_WWLL_CF]", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters =
|
{
|
new("@outMsg", SqlDbType.NVarChar, 300),
|
new("@outSum", SqlDbType.NVarChar, 300),
|
new("@c_User", query.userName),
|
new("@p_biLL_no", query.daa001),
|
new("@p_item_barcode", query.barcode),
|
new("@num", query.Num)
|
};
|
parameters[0].Direction = ParameterDirection.Output;
|
parameters[1].Direction = ParameterDirection.Output;
|
foreach (var parameter in parameters)
|
cmd.Parameters.Add(parameter);
|
cmd.ExecuteNonQuery();
|
_strMsg = parameters[0].Value.ToString();
|
_intSum = parameters[1].Value.ToString();
|
|
|
var result = Convert.ToInt32(_intSum);
|
if (result <= 0) throw new Exception(_strMsg);
|
|
var dto = new ProductionPickDto
|
{
|
daa001 = query.daa001,
|
barcode = query.barcode
|
};
|
|
return dto;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
public ProductionPickDto GetItemsByDaa001(WarehouseQuery query)
|
{
|
return getDaa001(query);
|
}
|
|
private ProductionPickDto getDaa001(WarehouseQuery query)
|
{
|
if (string.IsNullOrEmpty(query.daa001)) throw new Exception("工单号为空");
|
|
var wwgd = Db.Queryable<WwGd, MesItems>((a, i) =>
|
new JoinQueryInfos(JoinType.Left,
|
a.Daa003 == i.ItemId))
|
.Where((a, i) => a.Daa001 == query.daa001)
|
.Select((a, i) => new
|
{
|
a.Daa001, a.RwdGuid
|
}).First();
|
|
if (wwgd?.Daa001 == null) throw new Exception("工单号不存在");
|
|
var womdabs = Db
|
.Queryable<WwGd, WwGdDetail, MesItems, ProductionOrderSub>(
|
(a, b, c, d) =>
|
new JoinQueryInfos(
|
JoinType.Left,
|
a.Id == b.Pid,
|
JoinType.Inner,
|
c.Id ==
|
b.Dab003,
|
JoinType.Inner, b.Erpid.ToString() == d.ErpId
|
))
|
.Where((a, b, c, d) =>
|
a.Daa001 == query.daa001 && d.IssuingMethod == "1")
|
.Select((a, b, c, d) => new WwGdDetail
|
{
|
Pid = b.Pid,
|
Dab003 = b.Dab003,
|
Dab006 = b.Dab006,
|
Dab007 = b.Dab007,
|
wNum = b.Dab006 - b.Dab007, // 计算字段 W_NUM
|
ItemName = c.ItemName, // 动态字段 ITEM_NAME
|
ItemNo = c.ItemNo // 动态字段 ITEM_NO
|
})
|
.ToList();
|
|
var list = womdabs.Where(s => s.wNum > 0).ToList();
|
|
var mesInvItemOutCDetailsList = Db
|
.Queryable<MesInvItemOutCDetails, MesItems, MesDepots>
|
((a, b, c) =>
|
new JoinQueryInfos(
|
JoinType.Inner, a.ItemId == b.Id,
|
JoinType.Inner, c.DepotId == a.DepotId
|
))
|
.Where((a, b, c) => a.WorkNo == query.daa001)
|
.Select((a, b, c) => new MesInvItemOutCDetails
|
{
|
ItemName = b.ItemName,
|
ItemNo = b.ItemNo,
|
ItemId = a.ItemId,
|
DepotId = a.DepotId,
|
WorkNo = a.WorkNo,
|
DepotName = c.DepotName,
|
Quantity = a.Quantity
|
})
|
.ToList();
|
|
var womcaa = Db.Queryable<ProductionOrder>()
|
.Where(s => s.Guid == wwgd.RwdGuid)
|
.First();
|
|
var dto = new ProductionPickDto
|
{
|
daa001 = wwgd.Daa001,
|
PlanNo = womcaa.ErpProductionOrderNo,
|
totals1 = womdabs,
|
daisao1 = list,
|
yisao = mesInvItemOutCDetailsList
|
};
|
|
return dto;
|
}
|
|
|
#region 委外补料
|
|
/// <summary>
|
/// 委外工单退料扫码
|
/// 扫描条码 prc_rf_pda_scan_zout_barcode3
|
/// </summary>
|
/// <param name="query">查询参数</param>
|
/// <returns>扫描结果</returns>
|
/// <remarks>
|
/// 参数说明:
|
/// - billNo: 单据号(必填)
|
/// - barcode: 条码(必填)
|
/// - userName: 用户名
|
/// - blNo: 补料单号(必填)
|
/// </remarks>
|
public (WarehouseQuery item, List<MesItemBlDetail> pendingList)
|
WwblScanBarcode(WarehouseQuery query)
|
{
|
if (string.IsNullOrEmpty(query.billNo))
|
throw new Exception("请选取单据号！");
|
|
if (string.IsNullOrEmpty(query.barcode))
|
throw new Exception("请扫描条码！");
|
|
if (string.IsNullOrEmpty(query.userName))
|
throw new Exception("用户名不能为空！");
|
|
if (string.IsNullOrEmpty(query.blNo))
|
throw new Exception("补料单号不能为空！");
|
|
|
// 检验是否重复扫描
|
var exists = Db.Queryable<MesInvItemOutCDetails>()
|
.Where(b => b.ItemBarcode == query.barcode)
|
.Any();
|
|
if (exists)
|
throw new Exception("此条码已扫描,勿重复扫码！");
|
|
// 查询条码库存信息
|
var stockBarcode = Db.Queryable<MesInvItemStocks>()
|
.Where(t => t.ItemBarcode == query.barcode && t.Quantity > 0)
|
.First();
|
|
if (stockBarcode == null)
|
throw new Exception($"库存中无此条码,请核对！{query.barcode}");
|
|
// 检查补料单状态
|
var mesItemBl = Db.Queryable<MesItemBl>()
|
.Where(a => a.BlNo == query.blNo)
|
.First();
|
|
if (mesItemBl == null)
|
throw new Exception($"申请单 {query.blNo} 已撤回！");
|
|
if (mesItemBl.Bl018 != true)
|
throw new Exception($"申请单 {query.blNo} 未审核！");
|
|
if (mesItemBl.Bl019 == true)
|
throw new Exception($"申请单 {query.blNo} 已完结！");
|
|
// 获取补料单明细并校验
|
var blDetail = Db.Queryable<MesItemBlDetail>()
|
.Where(b =>
|
b.Mid == mesItemBl.Id && b.Bld012 == stockBarcode.ItemId)
|
.First();
|
|
if (blDetail == null)
|
throw new Exception($"申请单不存在此物料 {stockBarcode.ItemNo} 请确认！");
|
|
// 检查待补数量
|
var quantity = (blDetail.Bld007 ?? 0) - (blDetail.Bld008 ?? 0);
|
if (quantity == 0)
|
throw new Exception("物料已扫码完成，请核对！");
|
|
// 检查工单信息
|
var wwgd = Db.Queryable<WwGd>()
|
.Where(a => a.Daa001 == query.billNo)
|
.First();
|
|
if (wwgd == null)
|
throw new Exception($"工单 {query.billNo} 不存在，请确认！");
|
|
// 检查备料明细
|
var womdab = Db.Queryable<WwGdDetail>()
|
.Where(b => b.Dab001 == query.billNo && b.Erpid == blDetail.Bld014)
|
.First();
|
|
if (womdab == null)
|
throw new Exception($"备料明细不存在此物料 {stockBarcode.ItemNo} 请确认！");
|
|
if (stockBarcode.Quantity > quantity)
|
{
|
// 获取待发料明细列表
|
var pendingList = Db.Queryable<MesItemBl, MesItemBlDetail>((a, b) =>
|
new JoinQueryInfos(JoinType.Left, a.Id == b.Mid))
|
.Where((a, b) => a.BlNo == query.blNo
|
&& (b.Bld007 ?? 0) - (b.Bld008 ?? 0) > 0)
|
.Select((a, b) => new MesItemBlDetail
|
{
|
Bld012 = b.Bld012,
|
Bld002 = b.Bld002,
|
Bld003 = b.Bld003,
|
Bld004 = b.Bld004,
|
Bld007 = b.Bld007,
|
Bld008 = b.Bld008
|
})
|
.ToList();
|
|
|
query.Num = stockBarcode.Quantity;
|
query.Fum = quantity;
|
|
return (query, pendingList);
|
}
|
|
// 开启事务处理
|
var success = UseTransaction(db =>
|
{
|
// 获取或创建出库单
|
var outId = Guid.NewGuid();
|
var outNo = BillNo.GetBillNo("BL(工单补料)");
|
|
var existingOut = db.Queryable<MesInvItemOuts>()
|
.Where(a => a.BbillNo == query.blNo
|
&& a.DepotCode == womdab.Dab017
|
&& a.OutDate.Value.Date.ToString("yyyy-MM-dd") ==
|
DateTime.Now.Date.ToString("yyyy-MM-dd")
|
&& a.BillTypeId == 200
|
&& a.TransactionNo == 209
|
&& a.Status == 0)
|
.First();
|
|
if (existingOut != null)
|
{
|
outId = existingOut.Guid;
|
outNo = existingOut.ItemOutNo;
|
}
|
else
|
{
|
// 插入出库单主表
|
db.Insertable(new MesInvItemOuts
|
{
|
Guid = outId,
|
ItemOutNo = outNo,
|
TaskNo = query.blNo,
|
CreateBy = query.userName,
|
CreateDate = DateTime.Now,
|
LastupdateBy = query.userName,
|
LastupdateDate = DateTime.Now,
|
BillTypeId = 200,
|
TransactionNo = 209,
|
Remark = mesItemBl.Bl007,
|
DepotCode = womdab.Dab017,
|
OutPart = wwgd.Daa013.ToString(),
|
FType = 0,
|
Factory = stockBarcode.Factory,
|
Company = stockBarcode.Company,
|
// WorkNo = wwgd.Daa021,
|
// BoardItem = wwgd.Daa002,
|
PbillNo = wwgd.Daa001,
|
OutDate = DateTime.Now,
|
Status = 0,
|
BbillNo = query.blNo
|
}).IgnoreColumns(true).ExecuteCommand();
|
}
|
|
// 检查并更新出库单物料明细
|
var itemCount = db.Queryable<MesInvItemOutItems>()
|
.Where(i =>
|
i.ItemOutId == outId && i.ItemId == stockBarcode.ItemId)
|
.Count();
|
|
if (itemCount > 0)
|
// 更新已存在的物料明细数量
|
db.Updateable<MesInvItemOutItems>()
|
.SetColumns(i =>
|
i.Quantity == i.Quantity + stockBarcode.Quantity)
|
.Where(i =>
|
i.ItemOutId == outId && i.ItemId == stockBarcode.ItemId)
|
.ExecuteCommand();
|
else
|
// 插入新的物料明细记录
|
db.Insertable(new MesInvItemOutItems
|
{
|
Guid = Guid.NewGuid(),
|
ItemOutId = outId,
|
ItemNo = blDetail.Bld002,
|
Quantity = stockBarcode.Quantity,
|
CreateBy = query.userName,
|
CreateDate = DateTime.Now,
|
LastupdateBy = query.userName,
|
LastupdateDate = DateTime.Now,
|
Factory = stockBarcode.Factory,
|
Company = stockBarcode.Company,
|
DepotCode = womdab.Dab017,
|
TaskNo = query.blNo,
|
// WorkNo = wwgd.Daa021,
|
WorkLine = blDetail.Bld013,
|
ErpItemNo = womdab.Dab003.ToString(),
|
ErpId = womdab.Eid,
|
ErpAutoid = womdab.Erpid,
|
PbillNo = query.billNo,
|
ItemId = blDetail.Bld012
|
// Unit = blDetail.Bld009,
|
// DepotId = (int)stockBarcode.DepotsId
|
}).IgnoreColumns(true).ExecuteCommand();
|
|
// 插入出库单条码明细
|
db.Insertable(new MesInvItemOutCDetails
|
{
|
Guid = Guid.NewGuid(),
|
ItemOutId = outId,
|
ItemBarcode = stockBarcode.ItemBarcode,
|
ItemNo = stockBarcode.ItemNo,
|
LotNo = stockBarcode.LotNo,
|
Quantity = stockBarcode.Quantity,
|
ForceOutFlag = 0,
|
CreateBy = query.userName,
|
CreateDate = DateTime.Now,
|
LastupdateBy = query.userName,
|
LastupdateDate = DateTime.Now,
|
DepotCode = stockBarcode.DepotsCode,
|
DepotSectionCode = stockBarcode.DepotSectionsCode,
|
Remark = blDetail.Bld010,
|
Factory = stockBarcode.Factory,
|
Company = stockBarcode.Company,
|
TaskNoy = mesItemBl.Bl013,
|
BoardStyle = mesItemBl.Bl002,
|
TaskNo = query.blNo,
|
WorkNo = blDetail.Bld001,
|
WorkLine = blDetail.Bld013,
|
SuppNo = stockBarcode.SuppNo,
|
PbillNo = query.billNo,
|
ItemId = blDetail.Bld012,
|
Unit = blDetail.Bld009,
|
DepotId = (int)stockBarcode.DepotsId,
|
EbelnK3id = womdab.Eid,
|
LineK3id = womdab.Erpid
|
}).IgnoreColumns(true).ExecuteCommand();
|
|
// 插入业务交易记录
|
db.Insertable(new MesInvBusiness2
|
{
|
Guid = Guid.NewGuid(),
|
Status = 1,
|
BillTypeId = 200, // p_bill_type_id
|
TransactionCode = "210", // p_transaction_no
|
BusinessType = -1,
|
ItemBarcode = stockBarcode.ItemBarcode,
|
ItemNo = stockBarcode.ItemNo,
|
LotNo = stockBarcode.LotNo,
|
EpFlag = true,
|
Quantity = stockBarcode.Quantity,
|
FromInvDepotsCode = stockBarcode.DepotsCode,
|
FromInvDepotSectionsCode = stockBarcode.DepotSectionsCode,
|
CreateBy = query.userName,
|
CreateDate = DateTime.Now,
|
LastupdateBy = query.userName,
|
LastupdateDate = DateTime.Now,
|
Factory = stockBarcode.Factory,
|
Company = stockBarcode.Company,
|
TaskNo = mesItemBl.Bl012, // Matches C_QTCK.Bl012
|
BillNo = query.blNo,
|
WorkNo = blDetail.Bld001, // Matches C_QTCK_D.Bld001
|
WorkLine = blDetail.Bld013, // Matches C_QTCK_D.Bld013
|
SuppNo = stockBarcode.SuppNo,
|
ItemId = stockBarcode.ItemId
|
// CkDepot = stockBarcode.DepotsId
|
}).IgnoreColumns(true).ExecuteCommand();
|
|
|
// 更新工单表数量
|
db.Updateable<WwGdDetail>()
|
.SetColumns(it => new WwGdDetail
|
{
|
Dab007 = (it.Dab007 ?? 0) + (int)stockBarcode.Quantity,
|
Dab020 = (it.Dab020 ?? 0) + (int)stockBarcode.Quantity,
|
Dab021 = (it.Dab021 ?? 0) + (int)stockBarcode.Quantity
|
})
|
.Where(it => it.Id == womdab.Id && it.Dab003 == womdab.Dab003)
|
.ExecuteCommand();
|
|
// 更新补料单明细已补数量
|
db.Updateable<MesItemBlDetail>()
|
.SetColumns(it => new MesItemBlDetail
|
{
|
Bld008 = (it.Bld008 ?? 0) + (int)stockBarcode.Quantity
|
})
|
.Where(it => it.Id == blDetail.Id)
|
.ExecuteCommand();
|
|
// 检查补料单明细是否完成
|
var blDetail1 = db.Queryable<MesItemBlDetail>()
|
.Where(it => it.Id == blDetail.Id)
|
.First();
|
|
if ((blDetail1.Bld007 ?? 0) <= (blDetail1.Bld008 ?? 0))
|
// 更新明细完成状态
|
db.Updateable<MesItemBlDetail>()
|
.SetColumns(it => it.Bld011 == 1)
|
.Where(it => it.Id == blDetail1.Id)
|
.ExecuteCommand();
|
|
// 更新库存数量为0
|
db.Updateable<MesInvItemStocks>()
|
.SetColumns(it => it.Quantity == 0)
|
.Where(it => it.Guid == stockBarcode.Guid)
|
.ExecuteCommand();
|
|
|
// 检查是否所有明细都已完成
|
var unfinishedDetail = db.Queryable<MesItemBlDetail>()
|
.LeftJoin<MesItemBl>((b, a) => a.Id == b.Mid)
|
.Where((b, a) => a.BlNo == query.blNo && (b.Bld011 ?? 0) == 0)
|
.Select((b, a) => b)
|
.First();
|
|
if (unfinishedDetail == null)
|
// 如果没有未完成的明细，更新补料单状态为已完成
|
db.Updateable<MesItemBl>()
|
.SetColumns(it => new MesItemBl
|
{
|
Bl019 = true,
|
WcUser = query.userName,
|
WcTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
|
})
|
.Where(it => it.Id == mesItemBl.Id)
|
.ExecuteCommand();
|
|
return 1;
|
});
|
|
// 获取最终的待发料明细列表
|
var finalPendingList = Db.Queryable<MesItemBl, MesItemBlDetail>(
|
(a, b) =>
|
new JoinQueryInfos(JoinType.Left, a.Id == b.Mid))
|
.Where((a, b) => a.BlNo == query.blNo
|
&& (b.Bld007 ?? 0) - (b.Bld008 ?? 0) > 0)
|
.Select((a, b) => new MesItemBlDetail
|
{
|
Bld012 = b.Bld012,
|
Bld002 = b.Bld002,
|
Bld003 = b.Bld003,
|
Bld004 = b.Bld004,
|
Bld007 = b.Bld007,
|
Bld008 = b.Bld008
|
})
|
.ToList();
|
|
return (query, finalPendingList);
|
}
|
|
#endregion
|
}
|
