using System.Data;
|
using System.Data.SqlClient;
|
using System.Text;
|
using Masuit.Tools;
|
using Microsoft.SqlServer.Server;
|
using NewPdaSqlServer.DB;
|
using NewPdaSqlServer.Dto.service;
|
using NewPdaSqlServer.entity;
|
using NewPdaSqlServer.entity.Base;
|
using SqlSugar;
|
|
namespace NewPdaSqlServer.service.Wom;
|
|
public class WomdaaManager : Repository<Womdaa>
|
{
|
//当前类已经继承了 Repository 增、删、查、改的方法
|
//这里面写的代码不会给覆盖,如果要重新生成请删除 WomdaaManager.cs
|
|
//获取工单号
|
public List<string> GetProductionPickDaa001(WarehouseQuery query)
|
{
|
var sql =
|
"SELECT DAA001 FROM WOMDAA A LEFT JOIN (SELECT COUNT(1) UN_NUM, PID FROM WOMDAB WHERE DAB006 > DAB007 GROUP BY DAB002) B ON A.GUID = B.daaGuid WHERE DAA001 = " +
|
query.daa001 +
|
" and DAA018 != '完工' AND DAA022 = 1 AND UN_NUM > 0 AND ROWNUM <= 10 order by a.id desc";
|
|
return Db.Ado.SqlQuery<string>(sql);
|
}
|
|
//根据工单号返回产品型号和待领物料
|
public ProductionPickDto GetItemsByDaa001(WarehouseQuery query)
|
{
|
return getDaa001(query);
|
}
|
|
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_SCLL]", 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,
|
strMsg = _strMsg,
|
result = _intSum
|
};
|
|
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 = "";
|
var _cfBar = "";//拆分后条码
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (var cmd = new SqlCommand("[prc_pda_SCLL_CF]", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters =
|
{
|
new("@outMsg", SqlDbType.NVarChar, 2000),
|
new("@outSum", SqlDbType.NVarChar, 300),
|
new("@outCfBar", 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;
|
parameters[2].Direction = ParameterDirection.Output;
|
foreach (var parameter in parameters)
|
cmd.Parameters.Add(parameter);
|
cmd.ExecuteNonQuery();
|
_strMsg = parameters[0].Value.ToString();
|
_intSum = parameters[1].Value.ToString();
|
_cfBar = parameters[2].Value.ToString();
|
|
|
var result = Convert.ToInt32(_intSum);
|
if (result <= 0) throw new Exception(_strMsg);
|
|
var dto = new ProductionPickDto
|
{
|
daa001 = query.daa001,
|
barcode = query.barcode,//原条码
|
cfBarcode = _cfBar//拆分后条码
|
};
|
|
return dto;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
private ProductionPickDto getDaa001(WarehouseQuery query)
|
{
|
if (string.IsNullOrEmpty(query.daa001)) throw new Exception("工单号为空");
|
|
var womdaa = Db.Queryable<Womdaa, MesItems>((a, i) =>
|
new JoinQueryInfos(JoinType.Left,
|
a.Daa002 == i.ItemId.ToString()))
|
.Where((a, i) => a.Daa001 == query.daa001
|
&& a.Fstatus == 1)
|
.Select((a, i) => new
|
{
|
a.Daa001, a.CaaGuid
|
}).First();
|
|
if (womdaa?.Daa001 == null) throw new Exception("工单号不存在");
|
|
//var womdabs = Db.Queryable<Womdaa, Womdab, MesItems, Womcab>(
|
// (a, b, c, d) =>
|
// new JoinQueryInfos(
|
// JoinType.Left,
|
// a.Guid == b.DaaGuid,
|
// JoinType.Inner,
|
// c.Id.ToString() ==
|
// b.Dab003,
|
// JoinType.Inner, b.Erpid == d.Erpid
|
// ))
|
// .Where((a, b, c, d) =>
|
// a.Daa001 == query.daa001 && d.Iss1ueType == "1")
|
// .Select((a, b, c, d) => new ItemDetailModel
|
// {
|
// ItemNo = c.ItemNo,
|
// ItemName = c.ItemName,
|
// ItemModel = c.ItemModel,
|
// FQty = b.Dab006, // 申请数量
|
// SQty = b.Dab007, // 已扫数量
|
// DSQty = b.Dab006 - b.Dab007, // 已扫数量
|
// })
|
// .ToList();
|
|
var sql =string.Format(@"SELECT c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel,b.dab006 FQty,b.dab007 SQty,b.dab006 - b.dab007 DSQty ,
|
dbo.F_QX_GETRECODEPOTSE(B.dab003,'','','') as RecoKw
|
FROM WOMDAB B
|
LEFT JOIN WOMDAA A ON A.guid = B.daaGuid
|
LEFT JOIN MES_ITEMS C ON B.dab003 = C.item_id
|
LEFT JOIN WOMCAB D ON B.erpid = D.ERPID
|
WHERE daa001 = '{0}' AND ISSUE_TYPE = 1 ORDER BY DAB002", query.daa001);
|
|
var womdabs = Db.Ado.SqlQuery<ItemDetailModel>(sql);
|
|
|
var DS_list = womdabs.Where(s => s.DSQty > 0).ToList();
|
|
var YS_list = womdabs.Where(s => s.SQty > 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<Womcaa>().Where(s => s.Guid == womdaa.CaaGuid)
|
.First();
|
|
if (womcaa == null)
|
{
|
throw new Exception("生产任务单不存在");
|
}
|
|
var dto = new ProductionPickDto
|
{
|
daa001 = womdaa.Daa001,
|
PlanNo = womcaa.Caa020,
|
items = DS_list,
|
Ysitems = YS_list
|
// yisao = mesInvItemOutCDetailsList
|
};
|
|
return dto;
|
}
|
|
/// <summary>
|
/// 获取工单条码信息
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public dynamic getZsBarInfo(dynamic query)
|
{
|
|
var sql = string.Format(@"SELECT TOP 1 A.barCode AS zsBarcode,
|
D.name AS lineName,
|
D.line_no,
|
B.daa001,
|
C.item_no,
|
C.item_name,
|
C.item_model,
|
A.quantity as barQty,
|
B.daa008 as sumQty,
|
isnull((SELECT count(1) FROM WORK_COLLECT WHERE processNo = '{0}' AND ABOUT_GUID = A.ABOUT_GUID),0) AS finQty,
|
isnull((SELECT TOP 1 processNo FROM WORK_COLLECT WHERE WORK_COLLECT.barCode = '{1}' AND checkResult = '√' ORDER BY WORK_COLLECT.createDate DESC ),'') AS lastGx
|
FROM WORK_TRAC_CODE A
|
LEFT JOIN WOMDAA B ON A.ABOUT_GUID = B.guid
|
LEFT JOIN MES_ITEMS C ON B.daa002 = C.item_id
|
LEFT JOIN MES_WORKSHOP_LINE D ON A.lineId = D.id
|
WHERE A.barCode = '{1}'", query.GX, query.Zsbarcode);
|
|
var ZsBarInfo = Db.Ado.SqlQuery<dynamic>(sql);
|
|
if (ZsBarInfo.Count < 1)
|
{
|
throw new Exception($"该追溯码{query.Zsbarcode}不存在不存在");
|
}
|
|
return ZsBarInfo;
|
}
|
|
//获取生产
|
public dynamic getTraceability(dynamic query)
|
{
|
var sql = string.Format(@"SELECT processNo, A.barCode, B.lineId, line_no, name AS lineName, checkResult, C.USER_NAME
|
FROM WORK_COLLECT A
|
LEFT JOIN WORK_TRAC_CODE B ON A.barCode = B.barCode
|
LEFT JOIN SYS_USER C ON C.ACCOUNT = A.createBy
|
LEFT JOIN MES_WORKSHOP_LINE D ON B.lineId = D.id
|
WHERE A.barCode = '{0}'", query.Zsbarcode);
|
|
var Traceability = Db.Ado.SqlQuery<dynamic>(sql);
|
|
return Traceability;
|
}
|
|
public dynamic ProductBinding(dynamic query)
|
{
|
if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");
|
|
// 2. 使用 string.IsNullOrEmpty 直接判断字符串属性（避免 NullReferenceException）
|
if (string.IsNullOrEmpty(query.userName?.ToString()))
|
throw new ArgumentException("用户名不允许为空", nameof(query.userName));
|
|
if (string.IsNullOrEmpty(query.ZsBar?.ToString()))
|
throw new ArgumentException("追溯码不允许为空", nameof(query.ZsBar));
|
|
if (string.IsNullOrEmpty(query.LsBar?.ToString()))
|
throw new ArgumentException("后盖码不允许为空", nameof(query.LsBar));
|
|
var _strMsg = "";
|
var _intSum = "";
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (var cmd = new SqlCommand("prc_pda_product_binding", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters =
|
{
|
new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
|
new("@pi_trac_barcode", SqlDbType.NVarChar, 100) { Value = query.ZsBar },
|
new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = query.LsBar },
|
new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
|
new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
|
};
|
|
foreach (var parameter in parameters)
|
cmd.Parameters.Add(parameter);
|
|
cmd.ExecuteNonQuery();
|
|
_strMsg = parameters[3].Value?.ToString() ?? "";
|
_intSum = parameters[4].Value?.ToString() ?? "-1";
|
|
var result = Convert.ToInt32(_intSum);
|
if (result <= 0) throw new Exception(_strMsg);
|
|
var dto = new
|
{
|
message = _strMsg,
|
status = result,
|
tracBarcode = query.ZsBar
|
};
|
|
return dto;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
|
/// <summary>
|
/// 获取卡板信息
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public dynamic getKbBarInfo(dynamic query)
|
{
|
if (string.IsNullOrEmpty(query.Kbbarcode?.ToString()))
|
throw new ArgumentException("卡板条码不允许为空", nameof(query.userName));
|
|
var sql = string.Format(@"SELECT OLDQTY,
|
ISNULL((SELECT COUNT(1) FROM MES_INV_ITEM_BARCODES_TBMX WHERE ABOUT_TB_BARCODE = '{0}'), 0) AS YSum,
|
DAA001
|
FROM MES_INV_ITEM_BARCODES A LEFT JOIN WOMDAA B ON A.ABOUT_GUID = B.guid
|
WHERE ITEM_BARCODE = '{0}' ", query.Kbbarcode);
|
|
var ZsBarInfo = Db.Ado.SqlQuery<dynamic>(sql);
|
|
if (ZsBarInfo.Count < 1)
|
{
|
throw new Exception($"该卡板码{query.Zsbarcode}不存在！");
|
}
|
|
return ZsBarInfo;
|
}
|
|
/// <summary>
|
/// 获取卡板明细信息
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public dynamic getKbBarMxInfo(dynamic query)
|
{
|
|
var sql = string.Format(@"SELECT ITEM_BARCODE, QUANTITY,item_no,item_name,item_model,weight
|
FROM MES_INV_ITEM_BARCODES_TBMX A
|
LEFT JOIN WOMDAA B ON B.daa001 = A.DAA_001
|
LEFT JOIN MES_ITEMS C ON C.item_id = B.daa002
|
WHERE ABOUT_TB_BARCODE = '{0}' ", query.Kbbarcode);
|
|
var ZsBarInfo = Db.Ado.SqlQuery<dynamic>(sql);
|
|
|
return ZsBarInfo;
|
}
|
|
/// <summary>
|
/// 获取箱明细信息
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public dynamic getXBarInfo(dynamic query)
|
{
|
//var sql1 = string.Format(@"SELECT 1 FROM MES_INV_ITEM_BARCODES_TBMX WHERE ITEM_BARCODE = '{0}' ", query.Xbarcode);
|
|
//var IS_CZ = Db.Ado.SqlQuery<dynamic>(sql1);
|
|
//if (IS_CZ.Count < 1)
|
//{
|
// throw new Exception($"该箱条码[{query.Xbarcode}]不存在！");
|
//}
|
|
|
// 校验存储过程
|
if (string.IsNullOrEmpty(query.KbBar?.ToString()))
|
throw new ArgumentException("卡板条码不允许为空", nameof(query.KbBar));
|
// 校验存储过程
|
if (string.IsNullOrEmpty(query.Xbarcode?.ToString()))
|
throw new ArgumentException("箱条码不允许为空", nameof(query.Xbarcode));
|
|
var successRecords = new List<dynamic>();
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
conn.Open();
|
using (var transaction = conn.BeginTransaction()) // 开启事务
|
{
|
try
|
{
|
|
using (var cmd = new SqlCommand("prc_pda_Xbar_binding_JY", conn, transaction))
|
{
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters =
|
{
|
new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
|
new("@pi_kb_barcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
|
new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = query.Xbarcode },
|
new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
|
new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
|
};
|
|
foreach (var parameter in parameters)
|
cmd.Parameters.Add(parameter);
|
|
cmd.ExecuteNonQuery();
|
|
var _strMsg = parameters[3].Value?.ToString() ?? "";
|
var _intSum = parameters[4].Value?.ToString() ?? "-1";
|
|
var result = Convert.ToInt32(_intSum);
|
if (result <= 0)
|
{
|
//transaction.Rollback();
|
throw new Exception(_strMsg);
|
//return new {
|
// status = result,
|
// message = $"操作回滚：{_strMsg}",
|
// failedBarcode = xBar.iteM_BARCODE,
|
// successCount = successRecords.Count
|
//};
|
}
|
|
}
|
|
transaction.Commit(); // 全部成功提交事务
|
}
|
catch (Exception ex)
|
{
|
transaction.Rollback();
|
throw new Exception($"异常信息 ：{ex.Message}");
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
|
var sql2 = string.Format(@"SELECT ITEM_BARCODE, QUANTITY,item_no,item_name,item_model,'0' as is_hedui
|
FROM MES_INV_ITEM_BARCODES_TBMX A
|
LEFT JOIN WOMDAA B ON B.daa001 = A.DAA_001
|
LEFT JOIN MES_ITEMS C ON C.item_id = B.daa002
|
WHERE DAA_001 = '{0}'
|
AND ITEM_BARCODE = '{1}'
|
AND ISNULL(ABOUT_TB_BARCODE, '') = '' ", query.DAA001, query.Xbarcode);
|
|
var ZsBarInfo = Db.Ado.SqlQuery<dynamic>(sql2);
|
|
if (ZsBarInfo.Count < 1)
|
{
|
throw new Exception($"该箱条码[{query.Xbarcode}]对应工单与托码工单[{query.DAA001}]不匹配或已绑定托码！");
|
}
|
|
return ZsBarInfo;
|
}
|
|
public dynamic submitAllChecked(dynamic query)
|
{
|
if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");
|
|
// 参数校验强化
|
if (string.IsNullOrEmpty(query.userName?.ToString()))
|
throw new ArgumentException("用户名不允许为空", nameof(query.userName));
|
|
if (string.IsNullOrEmpty(query.KbBar?.ToString()))
|
throw new ArgumentException("卡板条码不允许为空", nameof(query.KbBar));
|
|
if (query.XbarInfo == null || !((IEnumerable<dynamic>)query.XbarInfo).GetEnumerator().MoveNext())
|
throw new ArgumentException("箱码列表不能为空", nameof(query.XbarInfo));
|
|
var successRecords = new List<dynamic>();
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
conn.Open();
|
using (var transaction = conn.BeginTransaction()) // 开启事务
|
{
|
try
|
{
|
foreach (var xBar in query.XbarInfo) // 遍历箱码集合
|
{
|
using (var cmd = new SqlCommand("prc_pda_Xbar_binding", conn, transaction))
|
{
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters =
|
{
|
new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
|
new("@pi_kb_barcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
|
new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = xBar.iteM_BARCODE },
|
new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
|
new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
|
};
|
|
foreach (var parameter in parameters)
|
cmd.Parameters.Add(parameter);
|
|
cmd.ExecuteNonQuery();
|
|
var _strMsg = parameters[3].Value?.ToString() ?? "";
|
var _intSum = parameters[4].Value?.ToString() ?? "-1";
|
|
var result = Convert.ToInt32(_intSum);
|
if (result <= 0)
|
{
|
//transaction.Rollback();
|
throw new Exception(_strMsg);
|
//return new {
|
// status = result,
|
// message = $"操作回滚：{_strMsg}",
|
// failedBarcode = xBar.iteM_BARCODE,
|
// successCount = successRecords.Count
|
//};
|
}
|
|
successRecords.Add(new {
|
kbBarcode = query.KbBar,
|
xBarcode = xBar.iteM_BARCODE,
|
processTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
|
});
|
}
|
}
|
transaction.Commit(); // 全部成功提交事务
|
return new {
|
status = 1,
|
message = "批量绑定成功",
|
successCount = successRecords.Count,
|
details = successRecords
|
};
|
}
|
catch (Exception ex)
|
{
|
transaction.Rollback();
|
throw new Exception($"异常信息 ：{ex.Message}");
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
public dynamic SubmitKbInspection(dynamic query)
|
{
|
if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");
|
|
// 参数校验
|
if (string.IsNullOrEmpty(query.userAccount?.ToString()))
|
throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));
|
|
if (string.IsNullOrEmpty(query.KbBar?.ToString()))
|
throw new ArgumentException("卡板条码不允许为空", nameof(query.KbBar));
|
|
var _strMsg = "";
|
var _status = -1;
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (var cmd = new SqlCommand("prc_pda_KBbar_submitInspection", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
SqlParameter[] parameters =
|
{
|
new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
|
new("@pi_kb_barcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
|
new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
|
new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
|
};
|
|
foreach (var parameter in parameters)
|
cmd.Parameters.Add(parameter);
|
|
cmd.ExecuteNonQuery();
|
|
_strMsg = parameters[2].Value?.ToString() ?? "";
|
_status = Convert.ToInt32(parameters[3].Value ?? -1);
|
|
if (_status <= 0) throw new Exception(_strMsg);
|
|
return new {
|
message = _strMsg,
|
status = _status,
|
kbBarcode = query.KbBar
|
};
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"卡板送检失败：{ex.Message}");
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
|
|
//根据工单号返回产品型号和待领物料
|
public XcslResultDto GetItemsXcsl(WarehouseQuery query)
|
{
|
if (string.IsNullOrEmpty(query.daa001)) throw new Exception("工单号为空");
|
|
var womdaa = Db.Queryable<Womdaa, MesItems>((a, i) =>
|
new JoinQueryInfos(JoinType.Left,
|
a.Daa002 == i.ItemId.ToString()))
|
.Where((a, i) => a.Daa001 == query.daa001
|
&& a.Fstatus == 1)
|
.Select((a, i) => new
|
{
|
a.Daa001,
|
a.CaaGuid,
|
a.Daa008,
|
a.Daa021
|
}).First();
|
|
if (womdaa?.Daa001 == null) throw new Exception("工单号不存在");
|
|
// 使用参数化查询防止SQL注入
|
var sqlParams = new List<SugarParameter> { new("@daa001", query.daa001) };
|
|
var sql1 = @"SELECT A.item_out_no,
|
SUM(B.QUANTITY) AS BL_Num,
|
ISNULL(Sub.JS_Sum, 0) AS JS_Num, -- 使用LEFT JOIN子查询的结果
|
C.item_no,
|
C.item_name,
|
C.item_model,
|
D.USER_NAME
|
FROM MES_INV_ITEM_OUTS A
|
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
|
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
|
LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
|
-- 新增LEFT JOIN聚合子查询
|
LEFT JOIN (SELECT B1.ITEM_ID,
|
SUM(B1.QUANTITY) AS JS_Sum
|
FROM MES_INV_ITEM_OUTS A1
|
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B1 ON A1.GUID = B1.ITEM_OUT_ID
|
WHERE A1.out_type = '生产领料'
|
AND A1.task_no = @daa001
|
AND ISNULL(B1.IS_XCSL, '0') = 1
|
GROUP BY B1.ITEM_ID -- 按ITEM_ID提前聚合
|
) AS Sub ON Sub.ITEM_ID = C.item_id
|
WHERE A.out_type = '生产领料'
|
AND A.task_no = @daa001
|
GROUP BY A.item_out_no,
|
C.item_no,
|
C.item_name,
|
C.item_model,
|
D.USER_NAME,
|
C.item_id,
|
ISNULL(Sub.JS_Sum, 0);";
|
|
var XcslItem = Db.Ado.SqlQuery<dynamic>(sql1, sqlParams);
|
|
var sql2 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,A.create_date
|
FROM MES_INV_ITEM_OUTS A
|
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
|
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
|
LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
|
WHERE out_type = '生产领料'
|
AND A.task_no = @daa001
|
AND ISNULL(IS_XCSL,'0') = 0";
|
|
var XcslWjsBar = Db.Ado.SqlQuery<dynamic>(sql2, sqlParams);
|
|
var sql3 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,B.XCSL_CREATE_DATE
|
FROM MES_INV_ITEM_OUTS A
|
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
|
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
|
LEFT JOIN SYS_USER D ON B.XCSL_CREATE_BY = D.ACCOUNT
|
WHERE out_type = '生产领料'
|
AND A.task_no = @daa001
|
AND ISNULL(IS_XCSL,'0') = 1";
|
|
var XcslYjsBar = Db.Ado.SqlQuery<dynamic>(sql3, sqlParams);
|
|
|
var dto = new XcslResultDto
|
{
|
GD_Num = womdaa.Daa008,
|
workNo = womdaa.Daa021,
|
XcslItemList = XcslItem,
|
XcslWjsBarList = XcslWjsBar,
|
XcslYjsBarList = XcslYjsBar
|
|
};
|
|
return dto;
|
}
|
|
// 现场收料展示列表
|
public class XcslResultDto
|
{
|
public int? GD_Num { get; set; }
|
public string? workNo { get; set; }
|
public List<dynamic> XcslItemList { get; set; }
|
public List<dynamic> XcslWjsBarList { get; set; }
|
public List<dynamic> XcslYjsBarList { get; set; }
|
}
|
|
|
public dynamic ScanXcsl(dynamic query)
|
{
|
if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");
|
|
// 参数校验（根据存储过程新增参数）
|
if (string.IsNullOrEmpty(query.userAccount?.ToString()))
|
throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));
|
|
if (string.IsNullOrEmpty(query.Bar?.ToString()))
|
throw new ArgumentException("物料条码不允许为空", nameof(query.Bar));
|
|
if (string.IsNullOrEmpty(query.DAA001?.ToString()))
|
throw new ArgumentException("工单单号不允许为空", nameof(query.DAA001));
|
|
var _strMsg = "";
|
var _status = -1;
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (var cmd = new SqlCommand("prc_pda_ScanXcsl", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
// 根据存储过程调整参数顺序和命名
|
SqlParameter[] parameters =
|
{
|
new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
|
new("@pi_barcode", SqlDbType.NVarChar, 100) { Value = query.Bar },
|
new("@pi_daa001", SqlDbType.NVarChar, 100) { Value = query.DAA001 },
|
new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
|
new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
|
};
|
|
foreach (var parameter in parameters)
|
cmd.Parameters.Add(parameter);
|
|
cmd.ExecuteNonQuery();
|
|
_strMsg = parameters[3].Value?.ToString() ?? "";
|
_status = Convert.ToInt32(parameters[4].Value ?? -1);
|
|
if (_status <= 0) throw new Exception(_strMsg);
|
|
return new
|
{
|
message = _strMsg,
|
status = _status,
|
daa001 = query.DAA001,
|
barCode = query.Bar
|
};
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"产线收料失败：{ex.Message}");
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
|
public dynamic GetWeightByXt(dynamic query)
|
{
|
if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为null");
|
|
// 参数校验
|
if (string.IsNullOrEmpty(query.userName?.ToString()))
|
throw new ArgumentException("用户名不允许为空", nameof(query.userName));
|
|
if (string.IsNullOrEmpty(query.LsBar?.ToString()))
|
throw new ArgumentException("卡板条码不允许为空", nameof(query.LsBar));
|
|
var _strMsg = "";
|
var _status = -1;
|
var _weight = "0";
|
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (var cmd = new SqlCommand("prc_pda_Xbar_chenzhong", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
SqlParameter[] parameters =
|
{
|
new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
|
new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = query.LsBar },
|
new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
|
new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output },
|
new("@po_outWeight", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output }
|
};
|
|
cmd.Parameters.AddRange(parameters);
|
cmd.ExecuteNonQuery();
|
|
_strMsg = parameters[2].Value?.ToString() ?? "";
|
_status = Convert.ToInt32(parameters[3].Value ?? -1);
|
_weight = parameters[4].Value?.ToString() ?? "0";
|
|
if (_status <= 0) throw new Exception(_strMsg);
|
|
return new
|
{
|
message = _strMsg,
|
status = _status,
|
weight = _weight,
|
barCode = query.Bar
|
};
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"产品称重失败：{ex.Message}");
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
public dynamic GetXcslDaa(dynamic unity)
|
{
|
|
var sqlParams = new List<SugarParameter> { };
|
|
var sql2 = new StringBuilder(@"
|
SELECT '['+DAA001+']['+DAA021+']' AS daaInfo,DAA001,DAA021
|
FROM WOMDAA
|
WHERE daa018 <>'W:完工'");
|
|
if (!string.IsNullOrWhiteSpace(unity.selectKey?.ToString()))
|
{
|
sqlParams.Add(new("@selectKey", unity.selectKey));
|
sql2.Append(@"
|
AND (DAA001 LIKE '%' + @selectKey + '%'
|
OR DAA021 LIKE '%' + @selectKey + '%')");
|
}
|
|
var XcslItem = Db.Ado.SqlQuery<dynamic>(sql2.ToString(), sqlParams);
|
|
if (XcslItem == null)
|
{
|
throw new Exception("该条件下无对应工单信息，请重新输入！");
|
}
|
|
return XcslItem;
|
}
|
|
|
public XcslResultDto GetItemsXctl(WarehouseQuery query)
|
{
|
if (string.IsNullOrEmpty(query.daa001)) throw new Exception("工单号为空");
|
|
var womdaa = Db.Queryable<Womdaa, MesItems>((a, i) =>
|
new JoinQueryInfos(JoinType.Left,
|
a.Daa002 == i.ItemId.ToString()))
|
.Where((a, i) => a.Daa001 == query.daa001
|
&& a.Fstatus == 1)
|
.Select((a, i) => new
|
{
|
a.Daa001,
|
a.CaaGuid,
|
a.Daa008,
|
a.Daa021
|
}).First();
|
|
if (womdaa?.Daa001 == null) throw new Exception("工单号不存在");
|
|
// 使用参数化查询防止SQL注入
|
var sqlParams = new List<SugarParameter> { new("@daa001", query.daa001) };
|
|
var sql1 = @"SELECT A.item_out_no,
|
SUM(B.QUANTITY) AS BL_Num,
|
ISNULL(SL_SUM.JS_Sum, 0) AS JS_Num,
|
ISNULL(TL_SUM.JS_Sum, 0) AS TR_Num,
|
C.item_no,
|
C.item_name,
|
C.item_model
|
FROM MES_INV_ITEM_OUTS A
|
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
|
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
|
LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
|
LEFT JOIN (SELECT B1.ITEM_ID,
|
SUM(B1.QUANTITY) AS JS_Sum
|
FROM MES_INV_ITEM_OUTS A1
|
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B1 ON A1.GUID = B1.ITEM_OUT_ID
|
WHERE A1.out_type = '生产领料'
|
AND A1.task_no = @daa001
|
AND ISNULL(B1.IS_XCSL, '0') = 1
|
GROUP BY B1.ITEM_ID
|
) AS SL_SUM ON SL_SUM.ITEM_ID = C.item_id
|
LEFT JOIN (SELECT B1.ITEM_ID,
|
SUM(B1.QUANTITY) AS JS_Sum
|
FROM MES_INV_ITEM_OUTS A1
|
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B1 ON A1.GUID = B1.ITEM_OUT_ID
|
WHERE A1.out_type = '生产领料'
|
AND A1.task_no = @daa001
|
AND ISNULL(B1.IS_CXTR, '0') = 1
|
GROUP BY B1.ITEM_ID
|
) AS TL_SUM ON TL_SUM.ITEM_ID = C.item_id
|
WHERE A.out_type = '生产领料'
|
AND A.task_no = @daa001
|
GROUP BY A.item_out_no,
|
C.item_no,
|
C.item_name,
|
C.item_model,
|
D.USER_NAME,
|
C.item_id,
|
ISNULL(SL_SUM.JS_Sum, 0),
|
ISNULL(TL_SUM.JS_Sum, 0);";
|
|
var XcslItem = Db.Ado.SqlQuery<dynamic>(sql1, sqlParams);
|
|
var sql2 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,A.create_date
|
FROM MES_INV_ITEM_OUTS A
|
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
|
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
|
LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
|
WHERE out_type = '生产领料'
|
AND A.task_no = @daa001
|
AND ISNULL(IS_CXTR,'0') = 0";
|
|
var XcslWjsBar = Db.Ado.SqlQuery<dynamic>(sql2, sqlParams);
|
|
var sql3 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,B.CXTR_CREATE_DATE
|
FROM MES_INV_ITEM_OUTS A
|
LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
|
LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
|
LEFT JOIN SYS_USER D ON B.CXTR_CREATE_BY = D.ACCOUNT
|
WHERE out_type = '生产领料'
|
AND A.task_no = @daa001
|
AND ISNULL(IS_CXTR,'0') = 1";
|
|
var XcslYjsBar = Db.Ado.SqlQuery<dynamic>(sql3, sqlParams);
|
|
|
var dto = new XcslResultDto
|
{
|
GD_Num = womdaa.Daa008,
|
workNo = womdaa.Daa021,
|
XcslItemList = XcslItem,
|
XcslWjsBarList = XcslWjsBar,
|
XcslYjsBarList = XcslYjsBar
|
|
};
|
|
return dto;
|
}
|
|
public dynamic ScanXctl(dynamic query)
|
{
|
if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");
|
|
// 参数校验（根据存储过程新增参数）
|
if (string.IsNullOrEmpty(query.userAccount?.ToString()))
|
throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));
|
|
if (string.IsNullOrEmpty(query.Bar?.ToString()))
|
throw new ArgumentException("物料条码不允许为空", nameof(query.Bar));
|
|
if (string.IsNullOrEmpty(query.DAA001?.ToString()))
|
throw new ArgumentException("工单单号不允许为空", nameof(query.DAA001));
|
|
var _strMsg = "";
|
var _status = -1;
|
using (var conn = new SqlConnection(DbHelperSQL.strConn))
|
{
|
using (var cmd = new SqlCommand("prc_pda_ScanXctl", conn))
|
{
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
// 根据存储过程调整参数顺序和命名
|
SqlParameter[] parameters =
|
{
|
new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
|
new("@pi_barcode", SqlDbType.NVarChar, 100) { Value = query.Bar },
|
new("@pi_daa001", SqlDbType.NVarChar, 100) { Value = query.DAA001 },
|
new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
|
new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
|
};
|
|
foreach (var parameter in parameters)
|
cmd.Parameters.Add(parameter);
|
|
cmd.ExecuteNonQuery();
|
|
_strMsg = parameters[3].Value?.ToString() ?? "";
|
_status = Convert.ToInt32(parameters[4].Value ?? -1);
|
|
if (_status <= 0) throw new Exception(_strMsg);
|
|
return new
|
{
|
message = _strMsg,
|
status = _status,
|
daa001 = query.DAA001,
|
barCode = query.Bar
|
};
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"产线收料失败：{ex.Message}");
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
}
|
|
}
|
