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 PullingRepository : BaseRepository, IPullingRepository { public PullingRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext) { } /// /// 查询JIT生产队列接口数据 /// /// public List GetProductionQueueInfo() { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { StringBuilder sql = new StringBuilder(1024); sql.Append("SELECT top 3000 * FROM MES_WMS_WORK_ORDER(nolock) "); sql.Append("WHERE syn_flg = 10 and order_type= 20 "); //未同步：10 已同步：20 sql.Append("ORDER BY create_time "); return dbConn.Query(sql.ToString()).ToList(); } } /// /// 处理JIT生产队列接口数据 /// /// /// public bool InsertProductionQueueInfo(List mesWmsWorks) { 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(); MesWmsWorkOrder mesWmsWork = new MesWmsWorkOrder(); for (int i = 0; i < mesWmsWorks.Count; i++) { List sqlList = new List(); List parametersList = new List(); mesWmsWork = mesWmsWorks[i]; StringBuilder SqlStringBuilder = new StringBuilder(1024); DynamicParameters parameters = new DynamicParameters(); List sysParts = sysPartsAll.Where(a => a.PartNo == mesWmsWork.PartNo).ToList();//零件号ID List sysPdlines = sysPdlinesAll.Where(a => a.PdlineCode == mesWmsWork.PdlineCode).ToList();//产线 List sysFactory = sysFactoryAll.Where(a => a.FactoryCode == mesWmsWork.FactoryCode).ToList();//工厂 StringBuilder customerStringBuilder = new StringBuilder(1024); customerStringBuilder.Append("select * from sys_customer(nolock) where customer_code='" + mesWmsWork.CustomerCode + "'");//客户信息 List sysCustomers = dbConn.Query(customerStringBuilder.ToString()).ToList(); if (mesWmsWork.IsWings == 1)//如果是1 侧翼 { //string checkPullingGroupPartSql = @"select a.* from [dbo].[wms_pulling_group_part] a (nolock) left join [dbo].[wms_pulling_group] b (nolock) on a.group_code=b.group_code // where a.part_no = '" + mesWmsWork.PartNo + "' and a.enabled='Y' "; //WmsPullingGroupPart wmsPullingGroupParts = dbConn.Query(checkPullingGroupPartSql).FirstOrDefault(); //if (wmsPullingGroupParts != null) //{ //明细 stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from mes_work_order_part(nolock) WHERE order_no='" + mesWmsWork.WorkOrderNo + "' and parent_part_no='" + mesWmsWork.PartNo + "' and part_no='" + mesWmsWork.PartNo + "' "); List mesWorkOrderParts = dbConn.Query(stringBuilder.ToString()).ToList(); if (mesWorkOrderParts.Count > 0) { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("UPDATE dbo.mes_work_order_part "); SqlStringBuilder.Append(" SET order_no = @orderNo,part_id = @partId "); SqlStringBuilder.Append(" ,part_no = @partNo,part_spec = @partSpec "); SqlStringBuilder.Append(" ,qty = @qty,unit = @unit "); SqlStringBuilder.Append(" ,pull_status = @pullStatus,parent_part_id = @parentPartId "); SqlStringBuilder.Append(" ,parent_part_no = @parentPartNo,parent_part_spec = @parentPartSpec "); SqlStringBuilder.Append(" ,factory_id = @factoryId "); SqlStringBuilder.Append(" ,factory_code = @factoryCode,enabled = @enabled "); SqlStringBuilder.Append(" ,update_userid = @updateUserid,update_time = @updateTime "); SqlStringBuilder.Append(" ,car_no = @car_no,move_group_no = @move_group_no ");//新增 车架号 下发时分组号 SqlStringBuilder.Append(" WHERE ruid = @ruid "); sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@orderNo", mesWmsWork.WorkOrderNo);//JIT单号 parameters.Add("@car_no", mesWmsWork.CarNo);//车架号 parameters.Add("@move_group_no", mesWmsWork.MoveGroupNo);//下发时分组号 stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from sys_part(nolock) WHERE part_no='" + mesWmsWork.PartNo + "'"); List itemSysParts = dbConn.Query(stringBuilder.ToString()).ToList(); if (itemSysParts.Count > 0) { parameters.Add("@partId", itemSysParts[0].PartId);//子零件号ID parameters.Add("@partSpec", itemSysParts[0].PartSpec);//子简码 parameters.Add("@unit", itemSysParts[0].Unit);//子单位 } else { parameters.Add("@partId", 0);//子零件号ID parameters.Add("@partSpec", "");//子简码 parameters.Add("@unit", "");//子单位 } parameters.Add("@partNo", mesWmsWork.PartNo);//子零件号 parameters.Add("@qty", mesWmsWork.Qty);//成品个数*零件号数量 parameters.Add("@pullStatus", "10");//状态 if (sysParts.Count > 0) { parameters.Add("@parentPartId", sysParts[0].PartId);//父零件号ID parameters.Add("@parentPartNo", sysParts[0].PartNo);//父零件号 parameters.Add("@parentPartSpec", sysParts[0].PartSpec);//父简码 } else { parameters.Add("@parentPartId", "0");//父零件号ID parameters.Add("@parentPartNo", "");//父零件号 parameters.Add("@parentPartSpec", "");//父简码 } if (sysFactory.Count == 0) { parameters.Add("@factoryId", 0);//工厂ID } else { parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID } parameters.Add("@factoryCode", mesWmsWork.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", mesWorkOrderParts[0].Ruid);//ruid parametersList.Add(parameters); } else { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("INSERT INTO dbo.mes_work_order_part(order_no,part_id,part_no,part_spec,qty,unit,pull_status "); SqlStringBuilder.Append(" ,parent_part_id,parent_part_no,parent_part_spec,factory_id,factory_code,enabled "); SqlStringBuilder.Append(" ,create_userid,create_time,car_no,move_group_no) "); SqlStringBuilder.Append(" VALUES(@orderNo,@partId,@partNo,@partSpec,@qty,@unit,@pullStatus,@parentPartId,@parentPartNo, "); SqlStringBuilder.Append(" @parentPartSpec,@factoryId,@factoryCode,@enabled,@createUserid,@createTime,@car_no,@move_group_no) "); sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@orderNo", mesWmsWork.WorkOrderNo);//JIT单号 parameters.Add("@car_no", mesWmsWork.CarNo);//车架号 parameters.Add("@move_group_no", mesWmsWork.MoveGroupNo);//下发时分组号 stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from sys_part(nolock) WHERE part_no='" + mesWmsWork.PartNo + "'"); List itemSysParts = dbConn.Query(stringBuilder.ToString()).ToList(); if (itemSysParts.Count > 0) { parameters.Add("@partId", itemSysParts[0].PartId);//子零件号ID parameters.Add("@partSpec", itemSysParts[0].PartSpec);//子简码 parameters.Add("@unit", itemSysParts[0].Unit);//子单位 } else { parameters.Add("@partId", 0);//子零件号ID parameters.Add("@partSpec", "");//子简码 parameters.Add("@unit", "");//子单位 } parameters.Add("@partNo", mesWmsWork.PartNo);//子零件号 parameters.Add("@qty", mesWmsWork.Qty);//成品个数*零件号数量 parameters.Add("@pullStatus", "10");//状态 if (sysParts.Count > 0) { parameters.Add("@parentPartId", sysParts[0].PartId);//父零件号ID parameters.Add("@parentPartNo", sysParts[0].PartNo);//父零件号 parameters.Add("@parentPartSpec", sysParts[0].PartSpec);//父简码 } else { parameters.Add("@parentPartId", "0");//父零件号ID parameters.Add("@parentPartNo", "");//父零件号 parameters.Add("@parentPartSpec", "");//父简码 } if (sysFactory.Count == 0) { parameters.Add("@factoryId", 0);//工厂ID } else { parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID } parameters.Add("@factoryCode", mesWmsWork.FactoryCode);//工厂代码 parameters.Add("@enabled", 'Y');//启用 parameters.Add("@createUserid", 0);//创建人 parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建日期 parametersList.Add(parameters); } //} } else //其他 正常拆bom { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("select * from sys_bom_detail(nolock) where part_no='" + mesWmsWork.PartNo + "' "); List sysBomDetails = dbConn.Query(SqlStringBuilder.ToString()).ToList(); foreach (SysBomDetail sysBom in sysBomDetails) { //string checkPullingGroupPartSql = @"select a.* from [dbo].[wms_pulling_group_part] a (nolock) left join [dbo].[wms_pulling_group] b (nolock) on a.group_code=b.group_code // where a.part_no = '" + sysBom.ItemPartNo + "' and a.enabled='Y' "; //WmsPullingGroupPart wmsPullingGroupParts = dbConn.Query(checkPullingGroupPartSql).FirstOrDefault(); //if (wmsPullingGroupParts != null) //{ //明细 stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from mes_work_order_part(nolock) WHERE order_no='" + mesWmsWork.WorkOrderNo + "' and parent_part_no='" + mesWmsWork.PartNo + "' and part_no='" + sysBom.ItemPartNo + "' "); List mesWorkOrderParts = dbConn.Query(stringBuilder.ToString()).ToList(); if (mesWorkOrderParts.Count > 0) { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("UPDATE dbo.mes_work_order_part "); SqlStringBuilder.Append(" SET order_no = @orderNo,part_id = @partId "); SqlStringBuilder.Append(" ,part_no = @partNo,part_spec = @partSpec "); SqlStringBuilder.Append(" ,qty = @qty,unit = @unit "); SqlStringBuilder.Append(" ,pull_status = @pullStatus,parent_part_id = @parentPartId "); SqlStringBuilder.Append(" ,parent_part_no = @parentPartNo,parent_part_spec = @parentPartSpec "); SqlStringBuilder.Append(" ,factory_id = @factoryId "); SqlStringBuilder.Append(" ,factory_code = @factoryCode,enabled = @enabled "); SqlStringBuilder.Append(" ,update_userid = @updateUserid,update_time = @updateTime "); SqlStringBuilder.Append(" ,car_no = @car_no,move_group_no = @move_group_no ");//新增 车架号 下发时分组号 SqlStringBuilder.Append(" WHERE ruid = @ruid "); sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@orderNo", mesWmsWork.WorkOrderNo);//JIT单号 parameters.Add("@car_no", mesWmsWork.CarNo);//车架号 parameters.Add("@move_group_no", mesWmsWork.MoveGroupNo);//下发时分组号 stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from sys_part(nolock) WHERE part_no='" + sysBom.ItemPartNo + "'"); List itemSysParts = dbConn.Query(stringBuilder.ToString()).ToList(); if (itemSysParts.Count > 0) { parameters.Add("@partId", itemSysParts[0].PartId);//子零件号ID parameters.Add("@partSpec", itemSysParts[0].PartSpec);//子简码 parameters.Add("@unit", itemSysParts[0].Unit);//子单位 } else { parameters.Add("@partId", 0);//子零件号ID parameters.Add("@partSpec", "");//子简码 parameters.Add("@unit", "");//子单位 } parameters.Add("@partNo", sysBom.ItemPartNo);//子零件号 parameters.Add("@qty", mesWmsWork.Qty * sysBom.Qty);//成品个数*零件号数量 parameters.Add("@pullStatus", "10");//状态 if (sysParts.Count > 0) { parameters.Add("@parentPartId", sysParts[0].PartId);//父零件号ID parameters.Add("@parentPartNo", sysParts[0].PartNo);//父零件号 parameters.Add("@parentPartSpec", sysParts[0].PartSpec);//父简码 } else { parameters.Add("@parentPartId", "0");//父零件号ID parameters.Add("@parentPartNo", "");//父零件号 parameters.Add("@parentPartSpec", "");//父简码 } if (sysFactory.Count == 0) { parameters.Add("@factoryId", 0);//工厂ID } else { parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID } parameters.Add("@factoryCode", mesWmsWork.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", mesWorkOrderParts[0].Ruid);//ruid parametersList.Add(parameters); } else { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("INSERT INTO dbo.mes_work_order_part(order_no,part_id,part_no,part_spec,qty,unit,pull_status "); SqlStringBuilder.Append(" ,parent_part_id,parent_part_no,parent_part_spec,factory_id,factory_code,enabled "); SqlStringBuilder.Append(" ,create_userid,create_time,car_no,move_group_no) "); SqlStringBuilder.Append(" VALUES(@orderNo,@partId,@partNo,@partSpec,@qty,@unit,@pullStatus,@parentPartId,@parentPartNo, "); SqlStringBuilder.Append(" @parentPartSpec,@factoryId,@factoryCode,@enabled,@createUserid,@createTime,@car_no,@move_group_no) "); sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@orderNo", mesWmsWork.WorkOrderNo);//JIT单号 parameters.Add("@car_no", mesWmsWork.CarNo);//车架号 parameters.Add("@move_group_no", mesWmsWork.MoveGroupNo);//下发时分组号 stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from sys_part(nolock) WHERE part_no='" + sysBom.ItemPartNo + "'"); List itemSysParts = dbConn.Query(stringBuilder.ToString()).ToList(); if (itemSysParts.Count > 0) { parameters.Add("@partId", itemSysParts[0].PartId);//子零件号ID parameters.Add("@partSpec", itemSysParts[0].PartSpec);//子简码 parameters.Add("@unit", itemSysParts[0].Unit);//子单位 } else { parameters.Add("@partId", 0);//子零件号ID parameters.Add("@partSpec", "");//子简码 parameters.Add("@unit", "");//子单位 } parameters.Add("@partNo", sysBom.ItemPartNo);//子零件号 parameters.Add("@qty", mesWmsWork.Qty * sysBom.Qty);//成品个数*零件号数量 parameters.Add("@pullStatus", "10");//状态 if (sysParts.Count > 0) { parameters.Add("@parentPartId", sysParts[0].PartId);//父零件号ID parameters.Add("@parentPartNo", sysParts[0].PartNo);//父零件号 parameters.Add("@parentPartSpec", sysParts[0].PartSpec);//父简码 } else { parameters.Add("@parentPartId", "0");//父零件号ID parameters.Add("@parentPartNo", "");//父零件号 parameters.Add("@parentPartSpec", "");//父简码 } if (sysFactory.Count == 0) { parameters.Add("@factoryId", 0);//工厂ID } else { parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID } parameters.Add("@factoryCode", mesWmsWork.FactoryCode);//工厂代码 parameters.Add("@enabled", 'Y');//启用 parameters.Add("@createUserid", 0);//创建人 parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建日期 parametersList.Add(parameters); } //} } } //主表 stringBuilder = new StringBuilder(); stringBuilder.Append(" SELECT * from mes_work_order(nolock) WHERE order_no='" + mesWmsWork.WorkOrderNo + "' and part_no='" + mesWmsWork.PartNo + "'"); List mesWorkOrders = dbConn.Query(stringBuilder.ToString()).ToList(); if (mesWorkOrders.Count > 0) { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("UPDATE dbo.mes_work_order "); SqlStringBuilder.Append(" SET order_type = @orderType,order_status = @orderStatus "); SqlStringBuilder.Append(" ,order_seq = @orderSeq,part_id = @partId "); SqlStringBuilder.Append(" ,part_no = @partNo,part_spec = @partSpec "); SqlStringBuilder.Append(" ,qty = @qty,complete_qty = @completeQty "); SqlStringBuilder.Append(" ,order_source = @orderSource,pdline_id = @pdlineId "); SqlStringBuilder.Append(" ,pdline_code = @pdlineCode "); SqlStringBuilder.Append(" ,customer_id = @customerId "); SqlStringBuilder.Append(" ,customer_code = @customerCode,cust_order_no = @custOrderNo "); SqlStringBuilder.Append(" ,vin_code = @vinCode "); SqlStringBuilder.Append(" ,vehicle_no = @vehicleNo,vehicle_name = @vehicleName "); SqlStringBuilder.Append(" ,factory_id = @factoryId,factory_code = @factoryCode "); SqlStringBuilder.Append(" ,enabled = @enabled,update_userid = @updateUserid "); SqlStringBuilder.Append(" ,update_time = @updateTime "); SqlStringBuilder.Append(" ,car_no = @car_no,move_group_no = @move_group_no ");//新增 车架号 下发时分组号 SqlStringBuilder.Append(" WHERE ruid = @ruid "); sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@orderType", mesWmsWork.OrderType);//JIT类型 parameters.Add("@orderStatus", "10");//状态 parameters.Add("@orderSeq", mesWmsWork.OrderSeq);//JIT序号 parameters.Add("@car_no", mesWmsWork.CarNo);//车架号 parameters.Add("@move_group_no", mesWmsWork.MoveGroupNo);//下发时分组号 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", mesWmsWork.PartNo);//零件号 parameters.Add("@partSpec", "");//简码 } parameters.Add("@qty", mesWmsWork.Qty);//数量 parameters.Add("@completeQty", 0);//数量 parameters.Add("@orderSource", "mes");//单据来源 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", mesWmsWork.PdlineCode);//生产线代码 } if (sysCustomers.Count == 0) { parameters.Add("@customerId", 0);//客户ID } else { parameters.Add("@customerId", sysCustomers[0].CustomerId);//客户ID } parameters.Add("@customerCode", mesWmsWork.CustomerCode);//客户代码 parameters.Add("@custOrderNo", mesWmsWork.CustOrderNo);//客户代码 parameters.Add("@vinCode", mesWmsWork.VinCode);//Vin代码 parameters.Add("@vehicleNo", mesWmsWork.VehicleNo);//配置代码 parameters.Add("@vehicleName", mesWmsWork.VehicleName);//配置描述 if (sysFactory.Count == 0) { parameters.Add("@factoryId", 0);//工厂ID } else { parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID } parameters.Add("@factoryCode", mesWmsWork.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", mesWorkOrders[0].Ruid);//ruid parametersList.Add(parameters); } else { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("INSERT INTO dbo.mes_work_order(order_no,order_type,order_status,order_seq,part_id,part_no,part_spec "); SqlStringBuilder.Append(" ,qty,complete_qty,order_source,pdline_id,pdline_code "); SqlStringBuilder.Append(" ,customer_id,customer_code,cust_order_no,cust_part_no,vin_code,vehicle_no,vehicle_name,pull_status"); SqlStringBuilder.Append(" ,factory_id,factory_code,enabled,create_userid,create_time,car_no,move_group_no ) "); SqlStringBuilder.Append(" VALUES(@orderNo,@orderType,@orderStatus,@orderSeq,@partId,@partNo,@partSpec "); SqlStringBuilder.Append(" ,@qty,@completeQty,@orderSource,@pdlineId,@pdlineCode,@customerId,@customerCode "); SqlStringBuilder.Append(" ,@custOrderNo,@custPartNo,@vinCode,@vehicleNo,@vehicleName,@pullStatus,@factoryId,@factoryCode "); SqlStringBuilder.Append(" ,@enabled,@createUserid,@createTime,@car_no,@move_group_no ) "); sqlList.Add(SqlStringBuilder.ToString()); parameters = new DynamicParameters(); parameters.Add("@orderNo", mesWmsWork.WorkOrderNo);//JIT单号 parameters.Add("@orderType", mesWmsWork.OrderType);//JIT类型 parameters.Add("@orderStatus", "10");//状态 parameters.Add("@orderSeq", mesWmsWork.OrderSeq);//JIT序号 parameters.Add("@car_no", mesWmsWork.CarNo);//车架号 parameters.Add("@move_group_no", mesWmsWork.MoveGroupNo);//下发时分组号 if (sysParts.Count > 0) { parameters.Add("@partId", sysParts[0].PartId);//零件号ID parameters.Add("@partSpec", sysParts[0].PartSpec);//简码 } else { parameters.Add("@partId", 0);//零件号ID parameters.Add("@partSpec", "");//简码 } parameters.Add("@partNo", mesWmsWork.PartNo);//零件号 parameters.Add("@qty", mesWmsWork.Qty);//数量 parameters.Add("@completeQty", 0);//数量 parameters.Add("@orderSource", "mes");//单据来源 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", mesWmsWork.PdlineCode);//生产线代码 } if (sysCustomers.Count == 0) { parameters.Add("@customerId", 0);//客户ID } else { parameters.Add("@customerId", sysCustomers[0].CustomerId);//客户ID } parameters.Add("@customerCode", mesWmsWork.CustomerCode);//客户代码 parameters.Add("@custOrderNo", mesWmsWork.CustOrderNo);//客户代码 parameters.Add("@custPartNo", "");//客户代码 parameters.Add("@vinCode", mesWmsWork.VinCode);//Vin代码 parameters.Add("@vehicleNo", mesWmsWork.VehicleNo);//配置代码 parameters.Add("@vehicleName", mesWmsWork.VehicleName);//配置描述 parameters.Add("@pullStatus", 10);//拉动状态 if (sysFactory.Count == 0) { parameters.Add("@factoryId", 0);//工厂ID } else { parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID } parameters.Add("@factoryCode", mesWmsWork.FactoryCode);//工厂代码 parameters.Add("@enabled", 'Y');//启用 parameters.Add("@createUserid", 0);//创建人 parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建日期 parametersList.Add(parameters); } SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update MES_WMS_WORK_ORDER 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", mesWmsWork.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_WORK_ORDER 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", mesWmsWork.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 GetPullingGroupList(int pullType) { IDbConnection dbConn = dapperDbContext.GetDbConnection(); //using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) //{ //dbConn.Open(); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("SELECT * FROM [wms_pulling_group] (nolock) WHERE enabled = 'Y' "); SqlStringBuilder.Append(" AND pulling_type = @pullingType "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@pullingType", pullType); List resultList = dbConn.Query(SqlStringBuilder.ToString(), parameters).ToList(); return resultList; //} } /// /// 需要生成安全库存零件的列表 /// /// public List SafetyPullList() { IDbConnection dbConn = dapperDbContext.GetDbConnection(); //using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) //{ //dbConn.Open(); StringBuilder sql = new StringBuilder(1024); sql.Append(@"select a.guid,c.warehouse_id,c.warehouse_name,c.zone_id,c.zone_name,d.erp_warehouse,a.locate_id,c.locate_name,c.locate_desc,a.factory_id,a.factory_code, a.part_id,a.part_no,e.part_spec,a.max_qty,a.min_qty,a.safety_qty, isnull(b.qty,0) Total,isnull(f.Total2,0) Total2 from sys_locate_part as a (nolock) left join (select locate_name,part_id, sum(qty) qty from sys_stock (nolock) where status = @status and enabled = 'Y' group by locate_name,part_id) as b on a.part_id = b.part_id and a.locate_name = b.locate_name left join sys_locate as c (nolock) on a.locate_id = c.locate_id left join sys_zone as d (nolock) on c.zone_id = d.zone_id left join sys_part as e (nolock) on a.part_no = e.part_no left join (select part_id, sum(qty) Total2 from sys_stock (nolock) where status = 50 and enabled = 'Y' group by part_id) as f on a.part_id = f.part_id where a.safety_qty > isnull(b.qty,0) and is_safety_pull = '0' and c.locate_type=@locate_type and a.enabled='Y' "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@status", (int)WmsEnumUtil.StockStatus.ONLINED); parameters.Add("@locate_type", (int)WmsEnumUtil.LocateType.LINE_LOCATE); List resultList = dbConn.Query(sql.ToString(), parameters).ToList(); return resultList; //} } public List GetSmallPiecePullRecovery() { IDbConnection dbConn = dapperDbContext.GetDbConnection(); StringBuilder sql = new StringBuilder(1024); sql.Append(@" select pdline_code,isnull(max(part_prepare),0) part_prepare from sys_pdline group by pdline_code "); List part_prepare = dbConn.Query(sql.ToString()).ToList(); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("select * from wms_move_header a left join wms_move_detail b on a.order_no=b.order_no where ref_order_no='安全库存拉动' and wrong_sign=0 and order_status in(10,20,30) "); SqlStringBuilder.Append("and part_no in ( "); for (int i = 0; i < part_prepare.Count; i++) { SqlStringBuilder.Append(@" select distinct b.part_no from (select distinct top "+ part_prepare[i].PartPrepare + " order_no,order_seq from mes_work_order where order_status = @order_status and pdline_code='" + part_prepare[i].PdlineCode + "' order by order_seq) a left join mes_work_order_part b on a.order_no = b.order_no "); if (part_prepare.Count-1 != i) { SqlStringBuilder.Append(@" union "); } } SqlStringBuilder.Append(" ) "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_status", 10);//JIT状态 10未处理 List resultList = dbConn.Query(SqlStringBuilder.ToString(), parameters).ToList(); return resultList; } public int UpdateSmallPiecePullRecovery(List wmsMoves) { IDbConnection dbConn = dapperDbContext.GetDbConnection(); StringBuilder SqlStringBuilder = new StringBuilder(1024); DynamicParameters parameters = new DynamicParameters(); int retNum = 0; for (int i = 0; i < wmsMoves.Count; i++) { SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update wms_move_header set wrong_sign=2 where ruid=@ruid "); parameters = new DynamicParameters(); parameters.Add("@ruid", wmsMoves[i].Ruid); retNum = dbConn.Execute(SqlStringBuilder.ToString(), parameters); } return retNum; } public List GetMoveHeaderWrong0() { IDbConnection dbConn = dapperDbContext.GetDbConnection(); //using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) //{ //dbConn.Open(); StringBuilder sql = new StringBuilder(1024); sql.Append(@" select a.guid,a.order_no,b.part_id,b.part_no,isnull(f.qty,0) qty from wms_move_header as a (nolock) left join wms_move_detail as b (nolock) on a.order_no = b.order_no left join (select part_id, sum(qty) qty from sys_stock (nolock) where status = 50 and enabled = 'Y' group by part_id) as f on b.part_id = f.part_id where a.wrong_sign in ('1','2') and a.ref_order_no='安全库存拉动' "); List resultList = dbConn.Query(sql.ToString()).ToList(); return resultList; //} } /// /// 大件拣选任务零件选择 /// /// /// /// public List GetPullingPickPartList(string pullGroupCode, int workOrderType) { IDbConnection dbConn = dapperDbContext.GetDbConnection(); //using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) //{ //dbConn.Open(); StringBuilder sql = new StringBuilder(1024); sql.Append("SELECT a.order_no,a.ruid,b.order_seq work_order_seq,a.part_id,a.part_no,a.part_spec,b.part_id parent_part_id,b.part_no parent_part_no,b.part_spec parent_part_spec,a.unit, a.qty * c.pull_qty qty, "); sql.Append(" a.factory_id,a.factory_code,b.pdline_id,b.pdline_code "); sql.Append("FROM mes_work_order_part a (nolock) "); sql.Append("JOIN dbo.mes_work_order b (nolock) ON a.order_no = b.order_no AND a.parent_part_id = b.part_id "); sql.Append("JOIN dbo.wms_pulling_group_part c (nolock) ON a.part_id = c.part_id "); sql.Append("WHERE a.pull_status = 10 "); sql.Append(" AND b.order_type = @orderType "); sql.Append(" AND a.enabled = 'Y' "); sql.Append(" AND c.enabled = 'Y' "); sql.Append(" AND c.group_code = @groupCode "); //sql.Append("GROUP BY a.order_no,a.part_id,a.part_no,a.part_spec,a.unit, "); //sql.Append(" a.factory_id,a.factory_code,b.pdline_id,b.pdline_code "); //sql.Append("ORDER BY a.order_no "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@orderType", workOrderType); parameters.Add("@groupCode", pullGroupCode); List resultList = dbConn.Query(sql.ToString(), parameters).ToList(); return resultList; //} } /// /// 根据产线ID获取产线库区关系信息 /// /// /// public SysPdlineZone GetPdlineZone(int pdlineId) { IDbConnection dbConn = dapperDbContext.GetDbConnection(); //using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) //{ //dbConn.Open(); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("SELECT top 1 *,z.erp_warehouse FROM [dbo].[sys_pdline_zone] pz (nolock) "); SqlStringBuilder.Append("JOIN dbo.sys_zone z (nolock) ON pz.zone_id = z.zone_id "); SqlStringBuilder.Append("WHERE pz.pdline_id = @pdlineId "); SqlStringBuilder.Append(" AND pz.busi_type = @busiType "); SqlStringBuilder.Append(" AND pz.enabled = 'Y' "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@pdlineId", pdlineId); parameters.Add("@busiType", (int)WmsEnumUtil.PdlineZoneBusiType.BIG_PART_PULL); var result = dbConn.QueryFirstOrDefault(SqlStringBuilder.ToString(), parameters); return result; //} } /// /// 获取待拉动JIT订单数据 /// /// public List GetPullingJitOrderList() { IDbConnection dbConn = dapperDbContext.GetDbConnection(); //using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) //{ //dbConn.Open(); StringBuilder sql = new StringBuilder(1024); sql.Append("SELECT TOP 1000 * FROM mes_work_order (nolock) "); sql.Append("WHERE order_type = @orderType "); sql.Append("ORDER BY order_seq "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@orderType", (int)MesEnumUtil.WorkOrderType.PRODUCT_JIT); List resultList = dbConn.Query(sql.ToString(), parameters).ToList(); return resultList; //} } /// /// 获取待拉动JIT订单原材料数据 /// /// public List GetPullingJitOrderPartList() { IDbConnection dbConn = dapperDbContext.GetDbConnection(); //using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) //{ //dbConn.Open(); StringBuilder sql = new StringBuilder(1024); sql.Append("SELECT TOP 1000 * FROM mes_work_order (nolock) "); sql.Append("WHERE order_type = @orderType "); sql.Append("ORDER BY order_seq "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@orderType", (int)MesEnumUtil.WorkOrderType.PRODUCT_JIT); List resultList = dbConn.Query(sql.ToString(), parameters).ToList(); return resultList; //} } } }