using Dapper; using Estsh.Core.Base; using Estsh.Core.Dapper; using Estsh.Core.Wms.IRepositories; using Estsh.Core.Model.Result; using Estsh.Core.Repositories; using System.Collections; using System.Data; using System.Text; using System.Security.Cryptography; using System.Text.Json; using Estsh.Core.Models; using Newtonsoft.Json.Linq; using Estsh.Core.Model.EnumUtil; using System.Diagnostics; using Estsh.Core.Util; using NPOI.SS.Formula.Functions; namespace Estsh.Core.Wms.Repositories { public class OutPdlineRepository : BaseRepository, IOutPdlineRepository { public OutPdlineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext) { } /// /// 查询成品和半成品下线 /// /// public List GetFinishedLineOffInfo(string pdlineCode) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { StringBuilder sql = new StringBuilder(1024); sql.Append(" SELECT top 1000 * FROM MES_WMS_OUT_PDLINE (readpast) "); sql.Append(" WHERE syn_flg = 10 AND pdline_code = @pdlineCode "); //未同步：10 已同步：20 sql.Append(" ORDER BY create_time "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@pdlineCode", pdlineCode); return dbConn.Query(sql.ToString(), parameters).ToList(); } } /// /// 成品下线 处理成品和半成品下线 /// /// /// public bool InsertFinishedLineOffInfo(List mesWmsOuts) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from sys_part (nolock) "); List sysPartsAll = dbConn.Query(stringBuilder.ToString()).ToList(); stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from sys_pdline (nolock) "); List sysPdlinesAll = dbConn.Query(stringBuilder.ToString()).ToList(); StringBuilder factoryStringBuilder = new StringBuilder(1024); factoryStringBuilder.Append("select * from sys_factory (nolock) ");//工厂代码获取工厂ID List sysFactoryAll = dbConn.Query(factoryStringBuilder.ToString()).ToList(); MesWmsOutPdline mesWmsOut = new MesWmsOutPdline(); for (int i = 0; i < mesWmsOuts.Count; i++) { List sqlList = new List(); List parametersList = new List(); mesWmsOut = mesWmsOuts[i]; StringBuilder SqlStringBuilder = new StringBuilder(1024); DynamicParameters parameters = new DynamicParameters(); List sysParts = sysPartsAll.Where(a => a.PartNo == mesWmsOut.PartNo).ToList();//零件号ID List sysPdlines = sysPdlinesAll.Where(a => a.PdlineCode == mesWmsOut.PdlineCode).ToList();//产线 List sysFactory = sysFactoryAll.Where(a => a.FactoryCode == mesWmsOut.FactoryCode).ToList();//工厂 stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from mes_out_pdline (nolock) WHERE work_order_no='" + mesWmsOut.WorkOrderNo + "' and pdline_code='" + mesWmsOut.PdlineCode + "' and part_no='" + mesWmsOut.PartNo + "' and serial_number='" + mesWmsOut.SerialNumber + "'"); List mesOutPdlines = dbConn.Query(stringBuilder.ToString()).ToList(); if (mesOutPdlines.Count > 0) { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("UPDATE dbo.mes_out_pdline "); SqlStringBuilder.Append(" SET work_order_no = @workOrderNo ,pdline_id = @pdlineId "); SqlStringBuilder.Append(" ,pdline_code = @pdlineCode ,part_id = @partId "); SqlStringBuilder.Append(" ,part_no = @partNo ,part_spec = @partSpec "); SqlStringBuilder.Append(" ,serial_number = @serialNumber ,qty += @qty "); SqlStringBuilder.Append(" ,lot_no = @lotNo ,product_type = @productType,product_version = @productVersion "); SqlStringBuilder.Append(" ,report_status = @reportStatus ,erp_status = @erpStatus "); SqlStringBuilder.Append(" ,tray_no = @tray_no ,mtoc = @mtoc "); SqlStringBuilder.Append(" ,mtoc_desc = @mtoc_desc ,cartype_name = @cartype_name "); SqlStringBuilder.Append(" ,factory_id = @factoryId ,factory_code = @factoryCode "); SqlStringBuilder.Append(" ,enabled = @enabled ,update_userid = @updateUserid "); SqlStringBuilder.Append(" ,update_time = @updateTime,isrejects=@isRejects "); SqlStringBuilder.Append(" WHERE ruid=@ruid "); sqlList.Add(SqlStringBuilder.ToString()); parameters.Add("@workOrderNo", mesWmsOut.WorkOrderNo);//工单号 if (sysPdlines.Count > 0) { parameters.Add("@pdlineId", sysPdlines[0].PdlineId);//生产线id parameters.Add("@pdlineCode", sysPdlines[0].PdlineCode);//生产线代码 } else { parameters.Add("@pdlineId", 0);//生产线id parameters.Add("@pdlineCode", mesWmsOut.PdlineCode);//生产线代码 } if (sysParts.Count > 0) { parameters.Add("@partId", sysParts[0].PartId);//零件号ID parameters.Add("@partNo", sysParts[0].PartNo);//零件号 parameters.Add("@partSpec", sysParts[0].PartSpec);//零件号简码 } else { parameters.Add("@partId", 0);//零件号ID parameters.Add("@partNo", mesWmsOut.PartNo);//零件号 parameters.Add("@partSpec", "");//零件号简码 } parameters.Add("@serialNumber", mesWmsOut.SerialNumber);//产品条码 parameters.Add("@qty", mesWmsOut.Qty);//数量 parameters.Add("@lotNo", mesWmsOut.LotNo);//生产批次 parameters.Add("@productType", mesWmsOut.ProductType);//生产类型 针对座椅总成下线，product_type=10，针对骨架和滑轨，product_type=20 parameters.Add("@productVersion", mesWmsOut.ProductVersion);//生产版本 parameters.Add("@reportStatus", "10");// parameters.Add("@erpStatus", "10");// parameters.Add("@isRejects", mesWmsOut.IsRejects);//是否不良 parameters.Add("@tray_no", mesWmsOut.TrayNo); parameters.Add("@mtoc", mesWmsOut.Mtoc); parameters.Add("@mtoc_desc", mesWmsOut.MtocDesc); parameters.Add("@cartype_name", mesWmsOut.CartypeName); if (sysFactory.Count == 0) { parameters.Add("@factoryId", 0);//工厂ID } else { parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID } parameters.Add("@factoryCode", mesWmsOut.FactoryCode);//工厂代码 parameters.Add("@enabled", 'Y');//启用 parameters.Add("@updateUserid", 0);//修改人 parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//修改日期 parameters.Add("@ruid", mesOutPdlines[0].Ruid);//ruid parametersList.Add(parameters); } else { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("INSERT INTO dbo.mes_out_pdline (work_order_no,pdline_id,pdline_code,part_id,part_no,part_spec "); SqlStringBuilder.Append(" ,serial_number,qty,lot_no,product_type,product_version,report_status,erp_status,err_msg "); SqlStringBuilder.Append(" ,factory_id,factory_code,enabled,create_userid,create_time,Guid,isrejects "); SqlStringBuilder.Append(" ,tray_no,mtoc,mtoc_desc,cartype_name) "); SqlStringBuilder.Append(" VALUES(@workOrderNo,@pdlineId,@pdlineCode,@partId,@partNo,@partSpec,@serialNumber "); SqlStringBuilder.Append(" ,@qty,@lotNo,@productType,@productVersion,@reportStatus,@erpStatus,@errMsg "); SqlStringBuilder.Append(" ,@factoryId,@factoryCode,@enabled,@createUserid,@createTime,@Guid,@isRejects "); SqlStringBuilder.Append(" ,@tray_no,@mtoc,@mtoc_desc,@cartype_name) "); sqlList.Add(SqlStringBuilder.ToString()); parameters.Add("@workOrderNo", mesWmsOut.WorkOrderNo);//工单号 if (sysPdlines.Count > 0) { parameters.Add("@pdlineId", sysPdlines[0].PdlineId);//生产线id parameters.Add("@pdlineCode", sysPdlines[0].PdlineCode);//生产线代码 } else { parameters.Add("@pdlineId", 0);//生产线id parameters.Add("@pdlineCode", mesWmsOut.PdlineCode);//生产线代码 } if (sysParts.Count > 0) { parameters.Add("@partId", sysParts[0].PartId);//零件号ID parameters.Add("@partNo", sysParts[0].PartNo);//零件号 parameters.Add("@partSpec", sysParts[0].PartSpec);//零件号简码 } else { parameters.Add("@partId", 0);//零件号ID parameters.Add("@partNo", mesWmsOut.PartNo);//零件号 parameters.Add("@partSpec", "");//零件号简码 } parameters.Add("@serialNumber", mesWmsOut.SerialNumber);//产品条码 parameters.Add("@qty", mesWmsOut.Qty);//数量 parameters.Add("@lotNo", mesWmsOut.LotNo);//生产批次 parameters.Add("@productType", mesWmsOut.ProductType);//生产类型 针对座椅总成下线，product_type=10，针对骨架和滑轨，product_type=20 parameters.Add("@productVersion", mesWmsOut.ProductVersion);//生产版本 parameters.Add("@reportStatus", "10");// parameters.Add("@erpStatus", "10");// parameters.Add("@errMsg", "");// parameters.Add("@isRejects", mesWmsOut.IsRejects);//是否不良 parameters.Add("@tray_no", mesWmsOut.TrayNo); parameters.Add("@mtoc", mesWmsOut.Mtoc); parameters.Add("@mtoc_desc", mesWmsOut.MtocDesc); parameters.Add("@cartype_name", mesWmsOut.CartypeName); if (sysFactory.Count == 0) { parameters.Add("@factoryId", 0);//工厂ID } else { parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID } parameters.Add("@factoryCode", mesWmsOut.FactoryCode);//工厂代码 parameters.Add("@enabled", 'Y');//启用 parameters.Add("@createUserid", 0);//创建人 parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建日期 parameters.Add("@GUID", Guid.NewGuid().ToString());//ID parametersList.Add(parameters); } SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_OUT_PDLINE set syn_flg=@synFlg,syn_time=@synTime,update_userid=@updateUserid,update_time=@updateTime where GUID=@GUID ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@GUID", mesWmsOut.Guid);//ID parameters.Add("@synFlg", "20");//已同步 parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间 parameters.Add("@updateUserid", 0);//更新人 parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//更新时间 parametersList.Add(parameters); bool staResult = ExecuteTransaction(sqlList, parametersList); if (staResult == false) { sqlList = new List(); parametersList = new List(); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_OUT_PDLINE set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where GUID=@GUID ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@GUID", mesWmsOut.Guid);//ID parameters.Add("@synFlg", "30");//同步异常 parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间 parameters.Add("@errMsg", "同步错误，请检查接口数据！");//错误 parameters.Add("@updateUserid", 0);//更新人 parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//更新时间 parametersList.Add(parameters); ExecuteTransaction(sqlList, parametersList); return false; } } return true; } } /// /// 获取待处理的下线报工数据 /// /// public List GetOutPdlineInfo(string pdlineCode) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { if (dbConn.State == ConnectionState.Closed) { dbConn.Open(); } StringBuilder sql = new StringBuilder(1024); sql.Append("SELECT top 100 a.*,b.part_id,b.part_spec,b.unit FROM mes_out_pdline a WITH(readpast) "); sql.Append("JOIN dbo.sys_part b (nolock) ON a.part_no = b.part_no "); sql.Append("WHERE a.enabled = 'Y' "); sql.Append(" AND a.report_status = @reportStatus "); sql.Append(" AND a.pdline_code = @pdlineCode "); sql.Append("ORDER BY a.create_time "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@reportStatus", MesEnumUtil.OutPdlineReportStatus.INIT); parameters.Add("@pdlineCode", pdlineCode); return dbConn.Query(sql.ToString(), parameters).ToList(); } } /// /// 获取库位信息 /// /// /// private SysLocate GetLocateInfo(int locateId) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { if (dbConn.State == ConnectionState.Closed) { dbConn.Open(); } StringBuilder sql = new StringBuilder(1024); sql.Append("SELECT a.*,b.erp_warehouse FROM dbo.sys_locate a (nolock) "); sql.Append("JOIN dbo.sys_zone b (nolock) ON a.zone_id = b.zone_id "); sql.Append("WHERE a.locate_id = @locateId AND a.enabled = 'Y' "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@locateId", locateId); return dbConn.QueryFirstOrDefault(sql.ToString(), parameters); } } private SysLocate GetNCLocateInfo() { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { if (dbConn.State == ConnectionState.Closed) { dbConn.Open(); } StringBuilder sql = new StringBuilder(1024); //sql.Append("select * from sys_locate (nolock) where locate_type='60' "); sql.Append("SELECT a.*,b.erp_warehouse FROM dbo.sys_locate a (nolock) "); sql.Append("JOIN dbo.sys_zone b (nolock) ON a.zone_id = b.zone_id "); sql.Append("WHERE a.enabled = 'Y' and a.locate_type='60'"); return dbConn.QueryFirstOrDefault(sql.ToString()); } } /// /// 更新下线数据 /// /// /// /// public int UpdateOutPdlineInfo(MesOutPdline outPdline, MesEnumUtil.OutPdlineReportStatus status, string msg = "") { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { if (dbConn.State == ConnectionState.Closed) { dbConn.Open(); } StringBuilder sql = new StringBuilder(1024); sql.Append("UPDATE dbo.mes_out_pdline "); sql.Append("SET report_status = @reportStatus, "); sql.Append(" err_msg = @errMsg, "); sql.Append(" update_userid = @updateUserid, "); sql.Append(" update_time = @updateTime "); sql.Append("WHERE serial_number = @serialNumber "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@reportStatus", status); parameters.Add("@errMsg", msg); parameters.Add("@updateUserid", 0); parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@serialNumber", outPdline.SerialNumber); return dbConn.Execute(sql.ToString(), parameters); } } /// /// 处理下线报工 /// /// /// /// /// public bool DoOutPdline(MesOutPdline outPdline, List bomDetails, SysPdline pdline, List bomDetailsList) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { if (dbConn.State == ConnectionState.Closed) { dbConn.Open(); } //LogHelper.Info("当前操作：开始执行" + outPdline.SerialNumber + "条码下线逻辑处理（条件判断），当前时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); List sqlList = new List(); List parametersList = new List(); StringBuilder sql = new StringBuilder(1024); StringBuilder SqlStringBuilder = new StringBuilder(1024); DynamicParameters parameters; // 1、处理自动生产入库，针对成品和半成品。 // 写入库存表 if (outPdline.ProductType == (int)MesEnumUtil.OutPdlineProductType.WIP || outPdline.ProductType == (int)MesEnumUtil.OutPdlineProductType.FINISHED_PRODUCT) { StringBuilder stringBuilder = new StringBuilder(1024); stringBuilder.Append("SELECT * from sys_stock WITH(NOLOCK) "); stringBuilder.Append("WHERE carton_no = @cartonNo "); DynamicParameters dynamic = new DynamicParameters(); dynamic.Add("@cartonNo", outPdline.SerialNumber); SysStock sysStocks = dbConn.Query(stringBuilder.ToString(), dynamic).FirstOrDefault(); if (sysStocks != null) { SysLocate locate = null; sql.Remove(0, sql.Length); sql.Append("UPDATE dbo.sys_stock "); sql.Append(" SET part_id = @part_id,part_no = @part_no "); sql.Append(" ,part_spec = @part_spec,lot_no = @lot_no,fix_lot_no = @fix_lot_no "); sql.Append(" ,status = @status,qty = @qty,snp_qty = @snp_qty "); sql.Append(" ,ref_order_no = @ref_order_no,unit = @unit"); if (sysStocks.LocateId != 0) { //不等于0 ，说明下线时已有库位，此情况目前为激光焊 ,不赋值库位信息 } else { sql.Append(" ,locate_id = @locate_id,locate_name = @locate_name,erp_warehouse = @erp_warehouse "); sql.Append(" ,warehouse_name = @warehouse_name,zone_id = @zone_id,zone_name = @zone_name ,warehouse_id = @warehouse_id "); } sql.Append(" ,factory_id = @factory_id,factory_code = @factory_code,enabled = @enabled "); sql.Append(" ,tray_no = @tray_no,mtoc = @mtoc,mtoc_desc = @mtoc_desc,cartype_name=@cartype_name "); sql.Append(" ,update_userid = @update_userid,update_time = @update_time,guid = @guid "); sql.Append(" WHERE carton_no = @carton_no "); parameters = new DynamicParameters(); parameters.Add("@carton_no", outPdline.SerialNumber); parameters.Add("@part_id", outPdline.PartId); parameters.Add("@part_no", outPdline.PartNo); parameters.Add("@part_spec", outPdline.PartSpec); parameters.Add("@lot_no", outPdline.LotNo); parameters.Add("@fix_lot_no", string.Empty); parameters.Add("@qty", outPdline.Qty); parameters.Add("@snp_qty", outPdline.Qty); string isRejectsVal = outPdline.IsRejects.Trim(); //if (isRejectsVal == "Y")//不是不良品 //{ // parameters.Add("@status", (int)WmsEnumUtil.StockStatus.NC_INSTOCK); // locate = this.GetNCLocateInfo(); //} //else //良品 //{ parameters.Add("@status", WmsEnumUtil.StockStatus.INSTOCKED); locate = this.GetLocateInfo(pdline.DestLocateId == null ? 0 : pdline.DestLocateId.Value); //} if (locate == null) { return false; } //第二次补 托盘号 parameters.Add("@tray_no", outPdline.TrayNo); parameters.Add("@mtoc", outPdline.Mtoc); parameters.Add("@mtoc_desc", outPdline.MtocDesc); parameters.Add("@cartype_name", outPdline.CartypeName); if (sysStocks.LocateId != 0) { //不等于0 ，说明下线时已有库位，此情况目前为激光焊 ,不赋值库位信息 } else { parameters.Add("@locate_id", locate.LocateId); parameters.Add("@locate_name", locate.LocateName); parameters.Add("@erp_warehouse", locate.ErpWarehouse); parameters.Add("@warehouse_id", locate.WarehouseId); parameters.Add("@warehouse_name", locate.WarehouseName); parameters.Add("@zone_id", locate.ZoneId); parameters.Add("@zone_name", locate.ZoneName); } parameters.Add("@ref_order_no", outPdline.WorkOrderNo); parameters.Add("@unit", outPdline.Unit); parameters.Add("@factory_id", outPdline.FactoryId); parameters.Add("@factory_code", outPdline.FactoryCode); parameters.Add("@enabled", WmsEnumUtil.Enabled.Y.ToString()); parameters.Add("@update_userid", 0); parameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@guid", Guid.NewGuid()); sqlList.Add(sql.ToString()); parametersList.Add(parameters); //新增 sys_stock_trans 事务表 sql.Remove(0, sql.Length); sql.Append("INSERT INTO [dbo].[sys_stock_trans] "); sql.Append("( "); sql.Append(" [trans_code],[carton_no],[part_id],[part_no],[part_spec],[src_locate_id],[src_locate_name],[dest_locate_id], "); sql.Append(" [dest_locate_name],[old_qty],[new_qty],[trans_qty],[old_status],[new_status],[unit], "); sql.Append(" [factory_id],[factory_code],[src_erp_warehouse],[dest_erp_warehouse],[src_warehouse_id],[src_warehouse_name], "); sql.Append(" [dest_warehouse_id],[dest_warehouse_name],[src_zone_id],[src_zone_name],[dest_zone_id],[dest_zone_name],[enabled], "); sql.Append(" [update_userid],[update_time], [create_userid],[create_time],[guid],ref_order_no "); sql.Append(") "); sql.Append("VALUES "); sql.Append("(@trans_code, @carton_no, @part_id, @part_no, @part_spec, @src_locate_id, @src_locate_name, @dest_locate_id, "); sql.Append(" @dest_locate_name, @old_qty, @new_qty, @trans_qty, @old_status, @new_status, @unit, "); sql.Append(" @factory_id, @factory_code, @src_erp_warehouse, @dest_erp_warehouse, @src_warehouse_id, @src_warehouse_name, "); sql.Append(" @dest_warehouse_id, @dest_warehouse_name, @src_zone_id, @src_zone_name, @dest_zone_id, @dest_zone_name, @enabled, "); sql.Append(" @update_userid, @update_time, @create_userid, @create_time, @guid, @ref_order_no) "); parameters = new DynamicParameters(); parameters.Add("@trans_code", (int)WmsEnumUtil.TransType.PRODUCTION_REPORT); parameters.Add("@carton_no", outPdline.SerialNumber); parameters.Add("@part_id", outPdline.PartId); parameters.Add("@part_no", outPdline.PartNo); parameters.Add("@part_spec", outPdline.PartSpec); parameters.Add("@old_qty", outPdline.Qty); parameters.Add("@new_qty", outPdline.Qty); parameters.Add("@trans_qty", outPdline.Qty); parameters.Add("@old_status", sysStocks.Status); //if (isRejectsVal == "Y") //是不良品 //{ // parameters.Add("@new_status", (int)WmsEnumUtil.StockStatus.NC_INSTOCK); //} //else //{ parameters.Add("@new_status", (int)WmsEnumUtil.StockStatus.INSTOCKED); //} parameters.Add("@unit", outPdline.Unit); parameters.Add("@factory_id", outPdline.FactoryId); parameters.Add("@factory_code", outPdline.FactoryCode); if (sysStocks.LocateId != 0) { //不等于0 ，说明下线时已有库位，此情况目前为激光焊 ,不赋值库位信息 ,使用已有库位写事务 parameters.Add("@src_locate_id", sysStocks.LocateId); parameters.Add("@src_locate_name", sysStocks.LocateName); parameters.Add("@dest_locate_id", sysStocks.LocateId); parameters.Add("@dest_locate_name", sysStocks.LocateName); parameters.Add("@src_erp_warehouse", sysStocks.ErpWarehouse); parameters.Add("@dest_erp_warehouse", sysStocks.ErpWarehouse); parameters.Add("@src_warehouse_id", sysStocks.WarehouseId); parameters.Add("@src_warehouse_name", sysStocks.WarehouseName); parameters.Add("@dest_warehouse_id", sysStocks.WarehouseId); parameters.Add("@dest_warehouse_name", sysStocks.WarehouseName); parameters.Add("@src_zone_id", sysStocks.ZoneId); parameters.Add("@src_zone_name", sysStocks.ZoneName); parameters.Add("@dest_zone_id", sysStocks.ZoneId); parameters.Add("@dest_zone_name", sysStocks.ZoneName); } else { parameters.Add("@src_locate_id", string.Empty); parameters.Add("@src_locate_name", string.Empty); parameters.Add("@dest_locate_id", locate == null ? 0 : locate.LocateId); parameters.Add("@dest_locate_name", locate == null ? "" : locate.LocateName); parameters.Add("@src_erp_warehouse", string.Empty); parameters.Add("@dest_erp_warehouse", locate == null ? "" : locate.ErpWarehouse); parameters.Add("@src_warehouse_id", 0); parameters.Add("@src_warehouse_name", string.Empty); parameters.Add("@dest_warehouse_id", locate == null ? 0 : locate.WarehouseId); parameters.Add("@dest_warehouse_name", locate == null ? "" : locate.WarehouseName); parameters.Add("@src_zone_id", 0); parameters.Add("@src_zone_name", string.Empty); parameters.Add("@dest_zone_id", locate == null ? 0 : locate.ZoneId); parameters.Add("@dest_zone_name", locate == null ? "" : locate.ZoneName); } parameters.Add("@enabled", WmsEnumUtil.Enabled.Y.ToString()); parameters.Add("@update_userid", 0); parameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@create_userid", 0); parameters.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@guid", Guid.NewGuid()); parameters.Add("@ref_order_no", outPdline.WorkOrderNo); sqlList.Add(sql.ToString()); parametersList.Add(parameters); } else { SysLocate locate = null; sql.Remove(0, sql.Length); sql.Append("INSERT INTO dbo.sys_stock "); sql.Append("( "); sql.Append(" carton_no, part_id, part_no, part_spec,lot_no, fix_lot_no, status, "); sql.Append(" qty, snp_qty, locate_id, locate_name,erp_warehouse, ref_order_no, "); sql.Append(" unit, warehouse_id, warehouse_name, zone_id, zone_name, "); sql.Append(" factory_id, factory_code, enabled,create_userid, create_time, guid "); sql.Append(") "); sql.Append("VALUES "); sql.Append("( @carton_no, @part_id, @part_no, @part_spec,@lot_no, @fix_lot_no, @status, "); sql.Append(" @qty, @snp_qty, @locate_id, @locate_name,@erp_warehouse, @ref_order_no, "); sql.Append(" @unit, @warehouse_id, @warehouse_name, @zone_id, @zone_name, "); sql.Append(" @factory_id, @factory_code, @enabled,@create_userid, @create_time, @guid "); sql.Append(") "); parameters = new DynamicParameters(); parameters.Add("@carton_no", outPdline.SerialNumber); parameters.Add("@part_id", outPdline.PartId); parameters.Add("@part_no", outPdline.PartNo); parameters.Add("@part_spec", outPdline.PartSpec); parameters.Add("@lot_no", outPdline.LotNo); parameters.Add("@fix_lot_no", string.Empty); parameters.Add("@qty", outPdline.Qty); parameters.Add("@snp_qty", outPdline.Qty); string isRejectsVal = outPdline.IsRejects.Trim(); //if (isRejectsVal == "Y")//是不良品 //{ // parameters.Add("@status", (int)WmsEnumUtil.StockStatus.NC_INSTOCK); // locate = this.GetNCLocateInfo(); //} //else //良品 //{ parameters.Add("@status", WmsEnumUtil.StockStatus.INSTOCKED); locate = this.GetLocateInfo(pdline.DestLocateId == null ? 0 : pdline.DestLocateId.Value); //} if (locate == null) { return false; } parameters.Add("@locate_id", locate.LocateId); parameters.Add("@locate_name", locate.LocateName); parameters.Add("@erp_warehouse", locate.ErpWarehouse); parameters.Add("@warehouse_id", locate.WarehouseId); parameters.Add("@warehouse_name", locate.WarehouseName); parameters.Add("@zone_id", locate.ZoneId); parameters.Add("@zone_name", locate.ZoneName); parameters.Add("@ref_order_no", outPdline.WorkOrderNo); parameters.Add("@unit", outPdline.Unit); parameters.Add("@factory_id", outPdline.FactoryId); parameters.Add("@factory_code", outPdline.FactoryCode); parameters.Add("@enabled", WmsEnumUtil.Enabled.Y.ToString()); parameters.Add("@create_userid", 0); parameters.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@guid", Guid.NewGuid()); sqlList.Add(sql.ToString()); parametersList.Add(parameters); //新增 sys_stock_trans 事务表 sql.Remove(0, sql.Length); sql.Append("INSERT INTO [dbo].[sys_stock_trans] "); sql.Append("( "); sql.Append(" [trans_code],[carton_no],[part_id],[part_no],[part_spec],[src_locate_id],[src_locate_name],[dest_locate_id], "); sql.Append(" [dest_locate_name],[old_qty],[new_qty],[trans_qty],[old_status],[new_status],[unit], "); sql.Append(" [factory_id],[factory_code],[src_erp_warehouse],[dest_erp_warehouse],[src_warehouse_id],[src_warehouse_name], "); sql.Append(" [dest_warehouse_id],[dest_warehouse_name],[src_zone_id],[src_zone_name],[dest_zone_id],[dest_zone_name],[enabled], "); sql.Append(" [update_userid],[update_time], [create_userid],[create_time],[guid],ref_order_no "); sql.Append(") "); sql.Append("VALUES "); sql.Append("(@trans_code, @carton_no, @part_id, @part_no, @part_spec, @src_locate_id, @src_locate_name, @dest_locate_id, "); sql.Append(" @dest_locate_name, @old_qty, @new_qty, @trans_qty, @old_status, @new_status, @unit, "); sql.Append(" @factory_id, @factory_code, @src_erp_warehouse, @dest_erp_warehouse, @src_warehouse_id, @src_warehouse_name, "); sql.Append(" @dest_warehouse_id, @dest_warehouse_name, @src_zone_id, @src_zone_name, @dest_zone_id, @dest_zone_name, @enabled, "); sql.Append(" @update_userid, @update_time, @create_userid, @create_time, @guid,@ref_order_no) "); parameters = new DynamicParameters(); parameters.Add("@trans_code", (int)WmsEnumUtil.TransType.PRODUCTION_REPORT); parameters.Add("@carton_no", outPdline.SerialNumber); parameters.Add("@part_id", outPdline.PartId); parameters.Add("@part_no", outPdline.PartNo); parameters.Add("@part_spec", outPdline.PartSpec); parameters.Add("@src_locate_id", string.Empty); parameters.Add("@src_locate_name", string.Empty); parameters.Add("@dest_locate_id", locate == null ? 0 : locate.LocateId); parameters.Add("@dest_locate_name", locate == null ? 0 : locate.LocateName); parameters.Add("@old_qty", outPdline.Qty); parameters.Add("@new_qty", outPdline.Qty); parameters.Add("@trans_qty", outPdline.Qty); parameters.Add("@old_status", 0); parameters.Add("@new_status", (int)WmsEnumUtil.StockStatus.INSTOCKED); parameters.Add("@unit", outPdline.Unit); parameters.Add("@factory_id", outPdline.FactoryId); parameters.Add("@factory_code", outPdline.FactoryCode); parameters.Add("@src_erp_warehouse", string.Empty); parameters.Add("@dest_erp_warehouse", locate == null ? 0 : locate.ErpWarehouse); parameters.Add("@src_warehouse_id", 0); parameters.Add("@src_warehouse_name", string.Empty); parameters.Add("@dest_warehouse_id", locate == null ? 0 : locate.WarehouseId); parameters.Add("@dest_warehouse_name", locate == null ? 0 : locate.WarehouseName); parameters.Add("@src_zone_id", 0); parameters.Add("@src_zone_name", string.Empty); parameters.Add("@dest_zone_id", locate == null ? 0 : locate.ZoneId); parameters.Add("@dest_zone_name", locate == null ? 0 : locate.ZoneName); parameters.Add("@enabled", WmsEnumUtil.Enabled.Y.ToString()); parameters.Add("@update_userid", 0); parameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@create_userid", 0); parameters.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@guid", Guid.NewGuid()); parameters.Add("@ref_order_no", outPdline.WorkOrderNo); sqlList.Add(sql.ToString()); parametersList.Add(parameters); } } SqlStringBuilder = new StringBuilder(1024);//拉动状态 SqlStringBuilder.Append("update mes_work_order set order_status='20' where order_no=@order_no and part_no=@part_no "); parameters = new DynamicParameters(); parameters.Add("@order_no", outPdline.WorkOrderNo);//工单号 parameters.Add("@part_no", outPdline.PartNo);//零件号 sqlList.Add(SqlStringBuilder.ToString()); parametersList.Add(parameters); sql.Remove(0, sql.Length); sql.Append("SELECT * FROM dbo.sys_zone (nolock) "); sql.Append("WHERE zone_name = @zoneName "); parameters = new DynamicParameters(); parameters.Add("@zoneName", pdline.DestZoneName); SysZone sysZones = dbConn.QueryFirstOrDefault(sql.ToString(), parameters); ////LogHelper.Info("当前操作：结束执行" + outPdline.SerialNumber + "条码下线逻辑处理（条件判断），当前时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); string sid = Guid.NewGuid().ToString().Replace("-", ""); ////LogHelper.Info("当前操作：开始执行" + outPdline.SerialNumber + "条码下线拆解BOM，WMS计数器生成拉动单、MES计数器生成JIT需求任务，当前时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); //WMS拉动组实体 string checkPullingGroupSql = @"select * from [dbo].[wms_pulling_group] where enabled='Y'"; List wmsPullings = dbConn.Query(checkPullingGroupSql).ToList(); //MES 拉动 string MEScheckPullingGroupSql = @"select * from [dbo].[wms_pulling_group_mes] where enabled='Y'"; List MesPullings = dbConn.Query(MEScheckPullingGroupSql).ToList(); // 2、处理拆解BOM，针对所有。 foreach (var bom in bomDetails) { #region 拉动组操作 string workOrderNoSql = @"select * from mes_out_pdline (nolock) where work_order_no='" + outPdline.WorkOrderNo + "' and pull_record=1"; MesOutPdline mesOutPdline = dbConn.Query(workOrderNoSql).FirstOrDefault(); if (mesOutPdline == null) { ////LogHelper.Info("当前操作：开始执行" + outPdline.SerialNumber + "条码下线逻辑（WMS计数器），当前时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); //WMS 拉动 List wmsPulling = wmsPullings.Where(a => a.PdlineCode == pdline.PdlineCode).ToList(); for (int j = 0; j < wmsPulling.Count; j++) { string pullingGroupCountSql = @"update wms_pulling_group set cumulativeValue = convert(int, isnull(cumulativeValue, 0)) + 1 where group_code='" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "'"; dbConn.Execute(pullingGroupCountSql); string getPullingGroupCumulativeValueSql = @"select * from [dbo].[wms_pulling_group] (nolock) where group_code='" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "'"; List wmsPullingGroup = dbConn.Query(getPullingGroupCumulativeValueSql).ToList(); if (Convert.ToInt32(wmsPullingGroup[0].CumulativeValue) >= Convert.ToInt32(wmsPullingGroup[0].TriggerValue)) { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append(" select * from mes_work_order_part k (nolock) where k.order_no in (select distinct top " + wmsPullingGroup[0].PullValue + " a.order_no from mes_work_order_part a (nolock) left join mes_work_order b (nolock) on a.order_no = b.order_no and a.parent_part_no = b.part_no "); SqlStringBuilder.Append(" where a.part_no in (select part_no from[dbo].[wms_pulling_group_part] a (nolock) left join[dbo].[wms_pulling_group] b (nolock) on a.group_code = b.group_code "); SqlStringBuilder.Append(" where b.group_code = '" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "' and a.enabled='Y' and b.enabled='Y') and a.pull_status = 10 and pdline_code = '" + wmsPulling[j].PdlineCode + "' order by a.order_no ) "); SqlStringBuilder.Append(" and k.part_no in (select part_no from[dbo].[wms_pulling_group_part] a (nolock) left join[dbo].[wms_pulling_group] b (nolock) on a.group_code = b.group_code "); SqlStringBuilder.Append(" where b.group_code = '" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "' and a.enabled='Y' and b.enabled='Y') "); List mesWorkOrderParts = dbConn.Query(SqlStringBuilder.ToString()).ToList(); if (mesWorkOrderParts.Count > 0) { List storeInfoList = mesWorkOrderParts.GroupBy(x => new { x.PartNo, x.FactoryId, x.FactoryCode }) .Select(g => new MesWorkOrderPart { PartNo = g.Key.PartNo, FactoryId = g.Key.FactoryId, FactoryCode = g.Key.FactoryCode, Qty = g.Sum(b => b.Qty) }).ToList(); var orderNo = GetOrderNo("ShiftProduction", "P");//生产领料 for (int i = 0; i < storeInfoList.Count; i++) { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("INSERT INTO dbo.wms_move_detail "); SqlStringBuilder.Append(" (order_no,item_no,part_id,part_no,part_spec,qty,pick_qty,out_qty,rec_qty "); SqlStringBuilder.Append(" ,unit,plan_date,plan_time,item_status,src_warehouse_id,src_warehouse_name,dest_warehouse_id "); SqlStringBuilder.Append(" ,dest_warehouse_name,src_zone_id,src_zone_name,dest_zone_id,dest_zone_name,src_erp_warehouse,dest_erp_warehouse,factory_id "); SqlStringBuilder.Append(" ,factory_code,enabled,create_userid,create_time,guid) "); SqlStringBuilder.Append(" VALUES(@orderNo,@itemNo,@partId,@partNo,@partSpec,@qty,@pickQty,@outQty,@recQty,@unit,@planDate "); SqlStringBuilder.Append(" ,@planTime,@itemStatus,@srcWarehouseId,@srcWarehouseName,@destWarehouseId,@destWarehouseName "); SqlStringBuilder.Append(" ,@srcZoneId,@srcZoneName,@destZoneId,@destZoneName,@srcErpWarehouse,@destErpWarehouse "); SqlStringBuilder.Append(" ,@factoryId,@factoryCode,@enabled,@createUserid,@createTime,@guid) "); sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@orderNo", orderNo);//生产领料单 parameters.Add("@itemNo", (i + 1) * 10);//序号 StringBuilder partNoStringBuilder = new StringBuilder(1024); partNoStringBuilder.Append("select * from sys_part (nolock) where part_no='" + storeInfoList[i].PartNo + "'");//零件号ID List sysParts = dbConn.Query(partNoStringBuilder.ToString()).ToList(); if (sysParts.Count > 0) { parameters.Add("@partId", sysParts[0].PartId);// parameters.Add("@partNo", sysParts[0].PartNo);//零件号 parameters.Add("@partSpec", sysParts[0].PartSpec);//简码 parameters.Add("@unit", sysParts[0].Unit);//单位 } else { parameters.Add("@partId", 0);//零件号ID parameters.Add("@partNo", storeInfoList[i].PartNo);//零件号 parameters.Add("@partSpec", "");//简码 parameters.Add("@unit", "");//单位 } parameters.Add("@qty", storeInfoList[i].Qty);//数量 parameters.Add("@pickQty", 0);// parameters.Add("@outQty", 0);// parameters.Add("@recQty", 0);// parameters.Add("@planDate", "");//计划日期 parameters.Add("@planTime", "");//计划时间 parameters.Add("@itemStatus", "10");//状态 parameters.Add("@srcWarehouseId", 0);//源仓库ID parameters.Add("@srcWarehouseName", "");//源仓库 parameters.Add("@srcZoneId", 0);//源库区ID parameters.Add("@srcZoneName", "");//源库区 parameters.Add("@srcErpWarehouse", "");//源ERP库存地 List pullingGroup = wmsPullings.Where(a => a.GroupCode == wmsPulling[j].GroupCode).ToList(); if (pullingGroup.Count > 0) { parameters.Add("@destWarehouseId", 0);//目标仓库ID parameters.Add("@destWarehouseName", "");//目标仓库 parameters.Add("@destZoneId", 0);//目标库区ID parameters.Add("@destZoneName", pullingGroup[0].GroupName);//目标库区 parameters.Add("@destErpWarehouse", "");//目标ERP库存地 } else { parameters.Add("@destWarehouseId", 0);//目标仓库ID parameters.Add("@destWarehouseName", "");//目标仓库 parameters.Add("@destZoneId", 0);//目标库区ID parameters.Add("@destZoneName", "");//目标库区 parameters.Add("@destErpWarehouse", "");//目标ERP库存地 } parameters.Add("@factoryId", storeInfoList[0].FactoryId);//工厂ID parameters.Add("@factoryCode", storeInfoList[0].FactoryCode);//工厂代码 parameters.Add("@enabled", "Y"); parameters.Add("@createUserid", 0); parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期 parameters.Add("@guid", Guid.NewGuid().ToString());// parametersList.Add(parameters); } StringBuilder stringBuilderSQL = new StringBuilder(1024); stringBuilderSQL.Append("INSERT INTO dbo.wms_move_header "); stringBuilderSQL.Append(" (order_no,order_type,order_status,ref_order_no,factory_id,factory_code "); stringBuilderSQL.Append(" ,enabled,create_userid,create_time,guid) "); stringBuilderSQL.Append(" VALUES(@orderNo,@orderType,@orderStatus,@refOrderNo "); stringBuilderSQL.Append(" ,@factoryId,@factoryCode,@enabled,@createUserid "); stringBuilderSQL.Append(" ,@createTime,@guid) "); sqlList.Add(stringBuilderSQL.ToString()); parameters = new DynamicParameters(); parameters.Add("@orderNo", orderNo);//生产领料单 parameters.Add("@orderType", (int)WmsEnumUtil.MoveOrderType.PICK);//生产领料单 parameters.Add("@orderStatus", (int)WmsEnumUtil.MoveOrderStatus.CREATE);//已创建 parameters.Add("@refOrderNo", "成品下线大件拉动");// parameters.Add("@factoryId", mesWorkOrderParts[0].FactoryId);// parameters.Add("@factoryCode", mesWorkOrderParts[0].FactoryCode);// parameters.Add("@enabled", "Y");// parameters.Add("@createUserid", 0);// parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期 parameters.Add("@guid", Guid.NewGuid().ToString());// parametersList.Add(parameters); for (int i = 0; i < mesWorkOrderParts.Count; i++) { for (int k = 0; k < storeInfoList.Count; k++) { stringBuilderSQL = new StringBuilder(1024); stringBuilderSQL.Append("update mes_work_order_part set pull_status =@pullStatus where order_no=@order_no and part_no=@part_no"); sqlList.Add(stringBuilderSQL.ToString()); parameters = new DynamicParameters(); parameters.Add("@order_no", mesWorkOrderParts[i].OrderNo);//id parameters.Add("@part_no", storeInfoList[k].PartNo);//id parameters.Add("@pullStatus", "20");// parametersList.Add(parameters); } } stringBuilderSQL = new StringBuilder(1024); //stringBuilderSQL.Append("update wms_pulling_group set cumulativeValue =convert(int, isnull(cumulativeValue, 0))-convert(int, isnull(pullValue, 0)) where group_code=@groupCode and pdline_code = @PdlineCode"); stringBuilderSQL.Append("update wms_pulling_group set cumulativeValue =0 where group_code=@groupCode and pdline_code = @PdlineCode"); sqlList.Add(stringBuilderSQL.ToString()); parameters = new DynamicParameters(); parameters.Add("@groupCode", wmsPulling[j].GroupCode);//id parameters.Add("@PdlineCode", wmsPulling[j].PdlineCode);// parametersList.Add(parameters); } } } ////LogHelper.Info("当前操作：结束执行" + outPdline.SerialNumber + "条码下线逻辑（WMS计数器），当前时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); //LogHelper.Info("当前操作：开始执行" + outPdline.SerialNumber + "条码下线逻辑（MES计数器），当前时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); //MES 拉动 List MesPulling = MesPullings.Where(a => a.PdlineCode == pdline.PdlineCode).ToList(); for (int j = 0; j < MesPulling.Count; j++) { string pullingGroupCountSql = @"update wms_pulling_group_mes set cumulative_value = convert(int, isnull(cumulative_value, 0)) + 1 where pdline_code = '" + MesPulling[j].PdlineCode + "'"; dbConn.Execute(pullingGroupCountSql); string getPullingGroupCumulativeValueSql = @"select * from [dbo].[wms_pulling_group_mes] (nolock) where pdline_code = '" + MesPulling[j].PdlineCode + "'"; List mesPullingGroup = dbConn.Query(getPullingGroupCumulativeValueSql).ToList(); if (Convert.ToInt32(mesPullingGroup[0].CumulativeValue) >= Convert.ToInt32(mesPullingGroup[0].TriggerValue)) { //给MES生成 拉动任务 StringBuilder stringBuilderSQL = new StringBuilder(1024); stringBuilderSQL.Append("insert into WMS_MES_PULL_ORDER(pdline_id,src_move_qty,syn_fig,create_time) values(@pdline_id,@src_move_qty,@syn_fig,@create_time) "); sqlList.Add(stringBuilderSQL.ToString()); parameters = new DynamicParameters(); parameters.Add("@pdline_id", MesPulling[j].PdlineCode);//产线 100001 parameters.Add("@src_move_qty", MesPulling[j].PullValue);// 需求数量 parameters.Add("@syn_fig", "0");// WMS生成为 0 parameters.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));// 创建时间 parametersList.Add(parameters); stringBuilderSQL = new StringBuilder(1024); stringBuilderSQL.Append("update wms_pulling_group_mes set cumulative_value =0 where pdline_code = @PdlineCode"); sqlList.Add(stringBuilderSQL.ToString()); parameters = new DynamicParameters(); parameters.Add("@PdlineCode", MesPulling[j].PdlineCode);// parametersList.Add(parameters); } } //LogHelper.Info("当前操作：结束执行" + outPdline.SerialNumber + "条码下线逻辑（MES计数器），当前时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); string updateWorkOrderNoSql = @"update mes_out_pdline set pull_record=1 where ruid=" + outPdline.Ruid + ""; dbConn.Execute(updateWorkOrderNoSql);//拉动值+1 } #endregion if (bom.VitualPart == "XXXX")//虚阶，向下再查一层 { List vitualBom = bomDetailsList.Where(a => a.PartId == bom.ItemPartId).ToList(); foreach (var item in vitualBom) { sql.Remove(0, sql.Length); sql.Append("INSERT INTO dbo.wms_product_deduct "); sql.Append("( "); sql.Append(" serial_number,part_id, part_no, unit, qty, item_part_id, item_part_no, item_unit, item_qty, "); sql.Append(" pdline_code, bom_id, factory_id, factory_code, enabled, create_userid, create_time, guid "); sql.Append(") "); sql.Append("VALUES "); sql.Append("( "); sql.Append(" @serial_number, @part_id, @part_no, @unit, @qty, @item_part_id, @item_part_no, @item_unit, @item_qty, "); sql.Append(" @pdline_code, @bom_id, @factory_id, @factory_code, @enabled, @create_userid, @create_time, @guid "); sql.Append(") "); parameters = new DynamicParameters(); parameters.Add("@serial_number", outPdline.SerialNumber); parameters.Add("@part_id", outPdline.PartId); parameters.Add("@part_no", outPdline.PartNo); parameters.Add("@unit", outPdline.Unit); parameters.Add("@qty", outPdline.Qty); parameters.Add("@item_part_id", item.ItemPartId); parameters.Add("@item_part_no", item.ItemPartNo); parameters.Add("@item_unit", item.ItemUnit); parameters.Add("@item_qty", item.ItemQty * outPdline.Qty); parameters.Add("@pdline_code", outPdline.PdlineCode); parameters.Add("@bom_id", item.BomId); parameters.Add("@factory_id", outPdline.FactoryId); parameters.Add("@factory_code", outPdline.FactoryCode); parameters.Add("@enabled", WmsEnumUtil.Enabled.Y.ToString()); parameters.Add("@create_userid", 0); parameters.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@guid", Guid.NewGuid()); sqlList.Add(sql.ToString()); parametersList.Add(parameters); } } else//正常操作 { //新增 wms_product_deduct 待扣减表 sql.Remove(0, sql.Length); sql.Append("INSERT INTO dbo.wms_product_deduct "); sql.Append("( "); sql.Append(" serial_number,part_id, part_no, unit, qty, item_part_id, item_part_no, item_unit, item_qty, "); sql.Append(" pdline_code, bom_id, factory_id, factory_code, enabled, create_userid, create_time, guid "); sql.Append(") "); sql.Append("VALUES "); sql.Append("( "); sql.Append(" @serial_number, @part_id, @part_no, @unit, @qty, @item_part_id, @item_part_no, @item_unit, @item_qty, "); sql.Append(" @pdline_code, @bom_id, @factory_id, @factory_code, @enabled, @create_userid, @create_time, @guid "); sql.Append(") "); parameters = new DynamicParameters(); parameters.Add("@serial_number", outPdline.SerialNumber); parameters.Add("@part_id", outPdline.PartId); parameters.Add("@part_no", outPdline.PartNo); parameters.Add("@unit", outPdline.Unit); parameters.Add("@qty", outPdline.Qty); parameters.Add("@item_part_id", bom.ItemPartId); parameters.Add("@item_part_no", bom.ItemPartNo); parameters.Add("@item_unit", bom.ItemUnit); parameters.Add("@item_qty", bom.ItemQty * outPdline.Qty); parameters.Add("@pdline_code", outPdline.PdlineCode); parameters.Add("@bom_id", bom.BomId); parameters.Add("@factory_id", outPdline.FactoryId); parameters.Add("@factory_code", outPdline.FactoryCode); parameters.Add("@enabled", WmsEnumUtil.Enabled.Y.ToString()); parameters.Add("@create_userid", 0); parameters.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@guid", Guid.NewGuid()); sqlList.Add(sql.ToString()); parametersList.Add(parameters); } ////新增 MES_MOVTRN 生产汇报信息给SAP 原定 主表 从表都传 //sql.Remove(0, sql.Length); //sql.Append("INSERT INTO dbo.MES_MOVTRN "); //sql.Append(" (WERKS,MATNRLNR,LMNGA,GMEIN,MDV01,ALORT,MATNR,ENTRY_QNT,ENTRY_UOM "); //sql.Append(" ,STGE_LOC,BOMUPDATE,POSTDATE,POSTTIME,SID,RECTIM,SYNFLG,SEQ,GUID) "); //sql.Append("VALUES "); //sql.Append(" (@WERKS,@MATNRLNR,@LMNGA,@GMEIN,@MDV01,@ALORT,@MATNR,@ENTRY_QNT "); //sql.Append(" ,@ENTRY_UOM,@STGE_LOC,@BOMUPDATE,@POSTDATE,@POSTTIME,@SID "); //sql.Append(" ,@RECTIM,@SYNFLG,@SEQ,@GUID) "); //parameters = new DynamicParameters(); //parameters.Add("@WERKS", outPdline.FactoryCode);//工厂 //parameters.Add("@MATNRLNR", outPdline.PartNo);//产成品物料编码 //parameters.Add("@LMNGA", outPdline.Qty);//完工数量 //parameters.Add("@GMEIN", outPdline.Unit);//产成品单位 //parameters.Add("@MDV01", string.IsNullOrEmpty(outPdline.ProductVersion) ? "" : outPdline.ProductVersion);//SAP生产版本对应信息 //parameters.Add("@ALORT", sysZones == null ? "" : sysZones.ErpWarehouse);//生产入库的SAP库位 ////parameters.Add("@MATNR", bom.ItemPartNo);//原材料编码 ////parameters.Add("@ENTRY_QNT", bom.ItemQty * outPdline.Qty);//原材料消耗的数量 ////parameters.Add("@ENTRY_UOM", bom.ItemUnit);//单位 ////parameters.Add("@STGE_LOC", "3001");//原材料消耗SAP库位 ////parameters.Add("@BOMUPDATE", bom.StartYmd);//BOM启用日期 + 时间 ////parameters.Add("@POSTDATE", DateTime.Now.ToString("yyyyMMdd"));//提交日期 ////parameters.Add("@POSTTIME", DateTime.Now.ToString("HHmmss"));//提交时间 //parameters.Add("@MATNR", "");//原材料编码 //parameters.Add("@ENTRY_QNT", "");//原材料消耗的数量 //parameters.Add("@ENTRY_UOM", "");//单位 //parameters.Add("@STGE_LOC", "");//原材料消耗SAP库位 //parameters.Add("@BOMUPDATE", "");//BOM启用日期 + 时间 //parameters.Add("@POSTDATE", "");//提交日期 //parameters.Add("@POSTTIME", "");//提交时间 //parameters.Add("@SID", sid);//SID //parameters.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建时间 //parameters.Add("@SYNFLG", "N");//同步状态 未同步：N 已同步：Y //parameters.Add("@SEQ", 0);//序号 //parameters.Add("@guid", Guid.NewGuid());//GUID //sqlList.Add(sql.ToString()); //parametersList.Add(parameters); } #region MyRegion //新增 MES_MOVTRN 生产汇报信息给SAP 只传成品 sql.Remove(0, sql.Length); sql.Append("INSERT INTO dbo.MES_MOVTRN "); sql.Append(" (WERKS,MATNRLNR,LMNGA,GMEIN,MDV01,ALORT,MATNR,ENTRY_QNT,ENTRY_UOM "); sql.Append(" ,STGE_LOC,BOMUPDATE,POSTDATE,POSTTIME,SID,RECTIM,SYNFLG,SEQ,GUID) "); sql.Append("VALUES "); sql.Append(" (@WERKS,@MATNRLNR,@LMNGA,@GMEIN,@MDV01,@ALORT,@MATNR,@ENTRY_QNT "); sql.Append(" ,@ENTRY_UOM,@STGE_LOC,@BOMUPDATE,@POSTDATE,@POSTTIME,@SID "); sql.Append(" ,@RECTIM,@SYNFLG,@SEQ,@GUID) "); parameters = new DynamicParameters(); parameters.Add("@WERKS", outPdline.FactoryCode);//工厂 parameters.Add("@MATNRLNR", outPdline.PartNo);//产成品物料编码 parameters.Add("@LMNGA", outPdline.Qty);//完工数量 parameters.Add("@GMEIN", outPdline.Unit);//产成品单位 parameters.Add("@MDV01", string.IsNullOrEmpty(outPdline.ProductVersion) ? "" : outPdline.ProductVersion);//SAP生产版本对应信息 parameters.Add("@ALORT", sysZones == null ? "" : sysZones.ErpWarehouse);//生产入库的SAP库位 //parameters.Add("@MATNR", bom.ItemPartNo);//原材料编码 //parameters.Add("@ENTRY_QNT", bom.ItemQty * outPdline.Qty);//原材料消耗的数量 //parameters.Add("@ENTRY_UOM", bom.ItemUnit);//单位 //parameters.Add("@STGE_LOC", "3001");//原材料消耗SAP库位 //parameters.Add("@BOMUPDATE", bom.StartYmd);//BOM启用日期 + 时间 //parameters.Add("@POSTDATE", DateTime.Now.ToString("yyyyMMdd"));//提交日期 //parameters.Add("@POSTTIME", DateTime.Now.ToString("HHmmss"));//提交时间 parameters.Add("@MATNR", "");//原材料编码 parameters.Add("@ENTRY_QNT", 0);//原材料消耗的数量 parameters.Add("@ENTRY_UOM", "");//单位 parameters.Add("@STGE_LOC", "");//原材料消耗SAP库位 parameters.Add("@BOMUPDATE", "");//BOM启用日期 + 时间 parameters.Add("@POSTDATE", DateTime.Now.ToString("yyyyMMdd"));//提交日期 parameters.Add("@POSTTIME", DateTime.Now.ToString("HHmmss"));//提交时间 parameters.Add("@SID", sid);//SID parameters.Add("@RECTIM", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建时间 parameters.Add("@SYNFLG", "N");//同步状态 未同步：N 已同步：Y parameters.Add("@SEQ", 0);//序号 parameters.Add("@guid", Guid.NewGuid());//GUID sqlList.Add(sql.ToString()); parametersList.Add(parameters); #endregion //LogHelper.Info("当前操作：结束执行" + outPdline.SerialNumber + "产线下线拆解BOM操作，WMS计数器生成拉动单、MES计数器生成JIT需求任务，当前时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); sql.Remove(0, sql.Length); sql.Append("UPDATE dbo.mes_out_pdline "); sql.Append("SET report_status = @reportStatus, "); sql.Append(" err_msg = '', "); sql.Append(" update_userid = @updateUserid, "); sql.Append(" update_time = @updateTime "); sql.Append("WHERE serial_number = @serialNumber "); parameters = new DynamicParameters(); parameters.Add("@reportStatus", MesEnumUtil.OutPdlineReportStatus.REPORTED); parameters.Add("@updateUserid", 0); parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); parameters.Add("@serialNumber", outPdline.SerialNumber); sqlList.Add(sql.ToString()); parametersList.Add(parameters); return this.ExecuteTransaction(sqlList, parametersList); } } /// /// 获取订单编号 /// /// public string GetOrderNo(string stockOrder, string p) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); DynamicParameters list = new DynamicParameters(); list.Add("@order_type", stockOrder); list.Add("@order_prefix", p); list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50); var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure); string result = list.Get("@order_no"); return result; } } } }