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; namespace Estsh.Core.Wms.Repositories { public class OutPdlineReceiveRepository : BaseRepository, IOutPdlineReceiveRepository { public OutPdlineReceiveRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext) { } /// /// 查询成品和半成品下线 /// /// public List GetOutPdlineReceiveInfo() { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { StringBuilder sql = new StringBuilder(1024); sql.Append("SELECT top 3000 * FROM MES_WMS_PG_RECEIVE (nolock) "); sql.Append("WHERE syn_flg = 10 and create_time < '"+ DateTime.Now.AddMinutes(-3).ToString("yyyy-MM-dd HH:mm:ss")+"'"); //未同步：10 已同步：20 sql.Append("ORDER BY create_time "); return dbConn.Query(sql.ToString()).ToList(); } } /// /// 成品下线 处理成品和半成品下线 /// /// /// public bool InsertOutPdlineReceiveInfo(List mesWmsPgs) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { for (int i = 0; i < mesWmsPgs.Count; i++) { List sqlList = new List(); List parametersList = new List(); //回传到stock表 StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("select* from sys_stock (nolock) where carton_no = '" + mesWmsPgs[i].SerialNumber + "' ");// SysStock sysStock = dbConn.Query(SqlStringBuilder.ToString()).FirstOrDefault(); if (mesWmsPgs[i].isWings == 0)//原逻辑不变,为座椅总成 { if (sysStock != null) { DynamicParameters parameters = new DynamicParameters(); if (mesWmsPgs[i].IsRepaired == "N") { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update sys_stock set status=@status,tray_no=@trayNo,channel_number=@channelNumber,mtoc=@mtoc,mtoc_desc=@mtocDesc,cartype_name=@carTypeName,update_userid=@updateUserid,update_time=@updateTime, ");//更新同步状态 SqlStringBuilder.Append(" locate_id=@locate_id,locate_name=@locate_name,warehouse_id=@warehouse_id,warehouse_name=@warehouse_name,zone_id=@zone_id,zone_name=@zone_name,erp_warehouse=@erp_warehouse "); SqlStringBuilder.Append(" where ruid=@ruid "); sqlList.Add(SqlStringBuilder.ToString()); SysLocate locate = this.GetLocateInfo(mesWmsPgs[i].PdlineCode); if (locate == null) { sqlList = new List(); parametersList = new List(); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_PG_RECEIVE set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where serial_number=@serialNumber ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@serialNumber", mesWmsPgs[i].SerialNumber);//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; } parameters.Add("@locate_id", locate.LocateId); parameters.Add("@locate_name", locate.LocateName); 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("@erp_warehouse", locate.ErpWarehouse); } else { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update sys_stock set tray_no=@trayNo,channel_number=@channelNumber,mtoc=@mtoc,mtoc_desc=@mtocDesc,cartype_name=@carTypeName,update_userid=@updateUserid,update_time=@updateTime where ruid=@ruid ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); } parameters.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);//入库 parameters.Add("@ruid", sysStock.Ruid);//ID parameters.Add("@trayNo", mesWmsPgs[i].TrayNo);// parameters.Add("@channelNumber", mesWmsPgs[i].ChannelNumber);// parameters.Add("@mtoc", mesWmsPgs[i].Mtoc);// parameters.Add("@mtocDesc", mesWmsPgs[i].MtocDesc);// parameters.Add("@carTypeName", mesWmsPgs[i].CartypeName);// parameters.Add("@updateUserid", 0);//更新人 parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//更新时间 parametersList.Add(parameters); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_PG_RECEIVE set syn_flg=@synFlg,syn_time=@synTime,update_userid=@updateUserid,update_time=@updateTime where serial_number=@serialNumber ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@serialNumber", mesWmsPgs[i].SerialNumber);//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_PG_RECEIVE set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where serial_number=@serialNumber ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@serialNumber", mesWmsPgs[i].SerialNumber);//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; } } else { sqlList = new List(); parametersList = new List(); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_PG_RECEIVE set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where serial_number=@serialNumber ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@serialNumber", mesWmsPgs[i].SerialNumber);//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; } } else//等于 1 ，侧翼下线时没有，入库时有 ，同时做回冲 { if (sysStock != null) { sqlList = new List(); parametersList = new List(); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_PG_RECEIVE set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where serial_number=@serialNumber ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@serialNumber", mesWmsPgs[i].SerialNumber);//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; }//侧翼下线已同步，报错 else { StringBuilder sql = new StringBuilder(1024); DynamicParameters parameters = new DynamicParameters(); SysLocate locate = null; 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(") "); StringBuilder getPart = new StringBuilder(1024); getPart.Append("select* from sys_part (nolock) where part_no = '" + mesWmsPgs[i].PartNo + "' and enabled='Y' ");// SysPart getParts = dbConn.Query(getPart.ToString()).FirstOrDefault(); //判断是否在WMS中有维护 if (getPart == null) { sqlList = new List(); parametersList = new List(); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_PG_RECEIVE set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where serial_number=@serialNumber ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@serialNumber", mesWmsPgs[i].SerialNumber);//ID parameters.Add("@synFlg", "30");//同步异常 parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间 parameters.Add("@errMsg", "侧翼条码在WMS零件维护中不存在，请检查！");//错误 parameters.Add("@updateUserid", 0);//更新人 parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//更新时间 parametersList.Add(parameters); ExecuteTransaction(sqlList, parametersList); return false; } parameters = new DynamicParameters(); parameters.Add("@carton_no", mesWmsPgs[i].SerialNumber); parameters.Add("@part_id", getParts.PartId); parameters.Add("@part_no", getParts.PartNo); parameters.Add("@part_spec", getParts.PartSpec); parameters.Add("@lot_no", DateTime.Now.ToString("yyyy-MM-dd")); parameters.Add("@fix_lot_no", string.Empty); parameters.Add("@qty", 1); parameters.Add("@snp_qty", 1); parameters.Add("@status", WmsEnumUtil.StockStatus.INSTOCKED); //getPart.Append("select* from MES_WMS_PG_RECEIVE (nolock) where TRAY_NO = '" + mesWmsPgs[i].TrayNo + "' and is_wings='0' ");// //MesWmsPgReceive MesWmsPgReceives = mesWmsPgs.Where(a => a.TrayNo == mesWmsPgs[i].TrayNo && a.isWings == 0).FirstOrDefault(); //MesWmsPgReceive MesWmsPgReceives = dbConn.Query(mesWmsPgs[i].PdlineCode).FirstOrDefault(); locate = this.GetLocateInfo(mesWmsPgs[i].PdlineCode); if (locate == null) { sqlList = new List(); parametersList = new List(); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_PG_RECEIVE set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where serial_number=@serialNumber ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@serialNumber", mesWmsPgs[i].SerialNumber);//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; } parameters.Add("@locate_id", locate.DestLocateId); parameters.Add("@locate_name", locate.DestLocateName); 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", "侧翼入库"); parameters.Add("@unit", "件"); parameters.Add("@factory_id", getParts.FactoryId); parameters.Add("@factory_code", getParts.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); #region 新增 sys_stock_trans 事务表 //新增 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.IN_STOCK_CEYI); parameters.Add("@carton_no", mesWmsPgs[i].SerialNumber); parameters.Add("@part_id", getParts.PartId); parameters.Add("@part_no", getParts.PartNo); parameters.Add("@part_spec", getParts.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", 1); parameters.Add("@new_qty", 1); parameters.Add("@trans_qty", 1); parameters.Add("@old_status", 0); parameters.Add("@new_status", (int)WmsEnumUtil.StockStatus.INSTOCKED); parameters.Add("@unit", "件"); parameters.Add("@factory_id", getParts.FactoryId); parameters.Add("@factory_code", getParts.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", "侧翼入库"); sqlList.Add(sql.ToString()); parametersList.Add(parameters); #endregion #region 新增 wms_product_deduct 待扣减表 //新增 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", mesWmsPgs[i].SerialNumber); parameters.Add("@part_id", getParts.PartId); parameters.Add("@part_no", getParts.PartNo); parameters.Add("@unit", getParts.Unit); parameters.Add("@qty", 1); parameters.Add("@item_part_id", getParts.PartId); parameters.Add("@item_part_no", getParts.PartNo); parameters.Add("@item_unit", getParts.Unit); parameters.Add("@item_qty", 1); parameters.Add("@pdline_code", locate.PdlineCode); parameters.Add("@bom_id", 0); parameters.Add("@factory_id", getParts.FactoryId); parameters.Add("@factory_code", getParts.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); #endregion SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_PG_RECEIVE set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where serial_number=@serialNumber ");//更新同步状态 sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@serialNumber", mesWmsPgs[i].SerialNumber);//ID parameters.Add("@synFlg", "20");//同步异常 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 true; } } private SysLocate GetLocateInfo(string pdline_code) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { if (dbConn.State == ConnectionState.Closed) { dbConn.Open(); } //StringBuilder sql = new StringBuilder(1024); //sql.Append("select a.pdline_id,a.pdline_code,b.src_warehouse_id,b.src_warehouse_name,b.src_zone_id,b.src_zone_name,c.warehouse_id,c.warehouse_name,c.zone_id,c.zone_name ,c.locate_id,c.locate_name from mes_out_pdline as a "); //sql.Append(" left join sys_pdline as b on a.pdline_id=b.pdline_id "); //sql.Append(" left join sys_stock as c on a.serial_number=c.carton_no "); //sql.Append(" where a.serial_number=@serial_number "); //DynamicParameters parameters = new DynamicParameters(); //parameters.Add("@serial_number", serial_number); StringBuilder sql = new StringBuilder(1024); sql.Append(" select a.pdline_code,b.warehouse_id,b.warehouse_name,b.erp_warehouse,b.zone_id,b.zone_name,a.dest_locate_id,a.dest_locate_name from sys_pdline as a "); sql.Append(" left join sys_zone as b on a.dest_zone_id = b.zone_id "); sql.Append(" where a.pdline_code=@pdline_code "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@pdline_code", pdline_code); return dbConn.QueryFirstOrDefault(sql.ToString(), parameters); } } } }