using Dapper; using Estsh.Core.Base; using Estsh.Core.Dapper; using Estsh.Core.Wms.IRepositories; using Estsh.Core.Repositories; using System.Data; using System.Text; using Estsh.Core.Models; using Estsh.Core.Model.EnumUtil; namespace Estsh.Core.Wms.Repositories { public class InpdlineRepository : BaseRepository, IInpdlineRepository { public InpdlineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext) { } //领料 public List CheckMoveHeader(string onLine, string cartonno) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder getqcSQL = new StringBuilder(1024); DynamicParameters parameters = new DynamicParameters(); List WmsQcDetail = new List(); if (cartonno.Trim() != "") { List stocks = GetCartonInfoByRK(cartonno); if (stocks.Count > 0) { //是箱条码 用简码查一次 getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join (select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' "); getqcSQL.Append(" and part_spec=@part_spec "); getqcSQL.Append(@" group by order_no,dest_zone_name ) as b on a.order_no=b.order_no where a.order_status in (@CREATE, @PROCESSING) and a.order_type = @order_type and a.enabled = 'Y' and ref_order_no != '成品下线大件拉动' and a.wrong_sign in ('0', '2')) as ccc where qty > pick_qty "); getqcSQL.Append(" order by case when order_status='30' then 0 else 1 end, create_time "); parameters = new DynamicParameters(); parameters.Add("@part_spec", stocks[0].PartSpec); parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK); parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE); parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING); WmsQcDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); if (WmsQcDetail.Count > 0) return WmsQcDetail; //是箱条码 用零件号查一次 getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join (select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' "); getqcSQL.Append(" and part_no=@part_no "); getqcSQL.Append(@" group by order_no,dest_zone_name ) as b on a.order_no=b.order_no where a.order_status in (@CREATE, @PROCESSING) and a.order_type = @order_type and a.enabled = 'Y' and ref_order_no != '成品下线大件拉动' and a.wrong_sign in ('0', '2')) as ccc where qty > pick_qty "); getqcSQL.Append(" order by case when order_status='30' then 0 else 1 end, create_time "); parameters = new DynamicParameters(); parameters.Add("@part_no", stocks[0].PartNo); parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK); parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE); parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING); WmsQcDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsQcDetail; } else { //不是箱条码 当简码查一次 getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join (select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' "); getqcSQL.Append(" and part_spec=@part_no "); getqcSQL.Append(@" group by order_no,dest_zone_name ) as b on a.order_no=b.order_no where a.order_status in (@CREATE, @PROCESSING) and a.order_type = @order_type and a.enabled = 'Y' and ref_order_no != '成品下线大件拉动' and a.wrong_sign in ('0', '2')) as ccc where qty > pick_qty "); getqcSQL.Append(" order by case when order_status='30' then 0 else 1 end, create_time "); parameters = new DynamicParameters(); parameters.Add("@part_no", cartonno); parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK); parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE); parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING); WmsQcDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsQcDetail; } } else { getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join (select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' "); getqcSQL.Append(""); getqcSQL.Append(@" group by order_no,dest_zone_name ) as b on a.order_no=b.order_no where a.order_status in (@CREATE, @PROCESSING) and a.order_type = @order_type and a.enabled = 'Y' and ref_order_no != '成品下线大件拉动' and a.wrong_sign in ('0', '2')) as ccc where qty > pick_qty "); getqcSQL.Append(" order by case when order_status='30' then 0 else 1 end, create_time "); parameters = new DynamicParameters(); parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK); parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE); parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING); WmsQcDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsQcDetail; } } } //领料上线 public List CheckOnlineMoveHeader(string onLine, string carton_no) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select * from ( select a.*,b.out_qty,b.dest_zone_name from wms_move_header as a (nolock) left join (select order_no,sum(out_qty) out_qty,dest_zone_name from wms_move_detail group by order_no,dest_zone_name) as b on a.order_no=b.order_no "); getqcSQL.Append(" where a.order_status in (@CREATE,@PROCESSING,@BATCHOVER) and a.order_type=@order_type and ref_order_no!='成品下线大件拉动' and a.wrong_sign in ('0','2') and a.enabled='Y' ) as ccc "); if (carton_no != "") { getqcSQL.Append("where order_no = (select order_no from wms_move_sn where carton_no = '" + carton_no + "' )"); } getqcSQL.Append(" order by case when out_qty>0 then 0 else 1 end, create_time "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK); parameters.Add("@CREATE", 0); parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING); parameters.Add("@BATCHOVER", (int)WmsEnumUtil.MoveOrderStatus.BATCHOVER); List WmsQcDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsQcDetail; } } //大件配料 public List CheckBulkyMoveHeader(string onLine) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select * from ( select a.*,b.qty,b.pick_qty,b.dest_zone_name from wms_move_header as a (nolock) left join (select order_no,sum(qty) qty,sum(pick_qty) pick_qty,dest_zone_name from wms_move_detail where enabled='Y' group by order_no,dest_zone_name ) as b on a.order_no=b.order_no where a.order_status in (@CREATE,@PROCESSING) and a.order_type=@order_type and a.enabled='Y' and ref_order_no='成品下线大件拉动' and a.wrong_sign in ('0','2') ) as ccc where qty > pick_qty order by case when order_status='30' then 0 else 1 end, create_time "); // getqcSQL.Append(@"select * from ( select distinct a.*,b.dest_zone_name from wms_move_header as a (nolock) left join wms_move_detail as b (nolock) on a.order_no=b.order_no //where a.order_status in (@CREATE,@PROCESSING) and a.order_type=@order_type and a.enabled='Y' and ref_order_no='成品下线大件拉动' ) as ccc order by case when order_status='30' then 0 else 1 end, update_time "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK); parameters.Add("@CREATE", (int)WmsEnumUtil.MoveOrderStatus.CREATE); parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING); List WmsQcDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsQcDetail; } } //大件领料上线 public List CheckBulkyOnlineMoveHeader(string onLine, string carton_no) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select * from ( select a.*,b.out_qty,b.dest_zone_name from wms_move_header as a (nolock) left join (select order_no,sum(out_qty) out_qty,dest_zone_name from wms_move_detail group by order_no,dest_zone_name) as b on a.order_no=b.order_no "); getqcSQL.Append(" where a.order_status in (@CREATE,@PROCESSING,@BATCHOVER) and a.order_type=@order_type and ref_order_no='成品下线大件拉动' and a.enabled='Y' ) as ccc "); if (carton_no != "") { getqcSQL.Append("where order_no = (select order_no from wms_move_sn where carton_no in (select carton_no from wms_rack_package where enabled='Y' and rack_no=@carton_no) )"); } getqcSQL.Append(" order by case when out_qty>0 then 0 else 1 end, create_time "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_type", (int)WmsEnumUtil.MoveOrderType.PICK); parameters.Add("@CREATE", 0); parameters.Add("@carton_no", carton_no); parameters.Add("@PROCESSING", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING); parameters.Add("@BATCHOVER", (int)WmsEnumUtil.MoveOrderStatus.BATCHOVER); List WmsQcDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsQcDetail; } } public List CheckMoveDetail(string orderNo, int status) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select * from ( select a.*,t.part_spec2,(select top 1 locate_name from sys_stock (nolock) where part_id=a.part_id and status=@status and enabled='Y' and locate_name != '' and locate_name is not null order by create_time) as recommend ,(select top 1 vendor_name from sys_stock as b left join sys_vendor as c on b.vendor_id = c.vendor_id where b.part_id = a.part_id and vendor_name is not null and vendor_name !='' ) as vendor_name from wms_move_detail as a (nolock) left join sys_part as t (nolock) on a.part_id=t.part_id where a.order_no=@order_no and a.enabled='Y' ) as ccc order by recommend"); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_no", orderNo); parameters.Add("@status", status); List WmsMoveDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsMoveDetail; } } public SysStock GetCartonInfo(string cartonNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); string result = string.Empty; StringBuilder stringBuilder = new StringBuilder(1024); stringBuilder.Append("SELECT a.*,b.enum_name as stock_status FROM sys_stock a (nolock) left join sys_enum b (nolock) on a.status=b.enum_value and b.enum_type = 'sys_stock_status' WHERE carton_no=@carton_no"); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@carton_no", cartonNo); return dbConn.QueryFirstOrDefault(stringBuilder.ToString(), parameters); } } public List GetCarrierByName(string rackNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("select a.* from wms_rack_package a (nolock) left join sys_stock s (nolock) on a.carton_no=s.carton_no where a.rack_no= '" + rackNo + "' and a.enabled='Y' and s.enabled='Y'"); List sysLocates = dbConn.Query(stringBuilder.ToString()).ToList(); return sysLocates; } } public WmsRack GetWmsRackInfo(string cartonNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); string result = string.Empty; StringBuilder sql = new StringBuilder(1024); sql.Append("select * from [dbo].[wms_rack] where rack_no=@carton_no "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@carton_no", cartonNo); return dbConn.QueryFirstOrDefault(sql.ToString(), parameters); } } public List GetMoveSN(string order_no) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); string result = string.Empty; StringBuilder sql = new StringBuilder(1024); sql.Append("select * from wms_move_sn (nolock) where order_no=@order_no and enabled='Y' "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_no", order_no); return dbConn.Query(sql.ToString(), parameters).ToList(); } } public List GetPartByFIFO(string part_id) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); string result = string.Empty; StringBuilder sql = new StringBuilder(1024); sql.Append(@"select * from sys_stock (nolock) where part_id=@part_id and status=@status and enabled='Y' and create_time=(select min(create_time) from sys_stock (nolock) where part_id=@part_id and status=@status and enabled='Y' )"); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@part_id", part_id); parameters.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED); //List dt = dbConn.Query(sql.ToString(), parameters).ToList(); return dbConn.Query(sql.ToString(), parameters).ToList(); } } public string BatchingCompleted(string loginId, string orderNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder updateString = new StringBuilder(1024); updateString.Append(@"update wms_move_header set batching_status=1,update_userid=@update_userid,update_time=@update_time where order_no=@order_no"); DynamicParameters updateStringparameters = new DynamicParameters(); updateStringparameters.Add("@order_no", orderNo); updateStringparameters.Add("@update_userid", loginId); updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); int rec = dbConn.Execute(updateString.ToString(), updateStringparameters); if (rec > 0) { return "OK"; } else { return "操作失败，请重新尝试！"; } } } //上料 public List GetLocateInfo(string locate_name) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder LocateInfoSQL = new StringBuilder(1024); LocateInfoSQL.Append(@"select B.erp_warehouse,A.* from sys_locate AS A (nolock) LEFT JOIN sys_zone AS B (nolock) ON A.zone_id=B.zone_id where A.locate_name=@locate_name and A.enabled='Y' and B.enabled='Y' "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@locate_name", locate_name); List LocateInfo = dbConn.Query(LocateInfoSQL.ToString(), parameters).ToList(); return LocateInfo; } } public List GetLocatePart(string locate_name) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder LocateInfoSQL = new StringBuilder(1024); LocateInfoSQL.Append(@" select * from sys_locate_part where locate_name=@locate_name "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@locate_name", locate_name); List LocateInfo = dbConn.Query(LocateInfoSQL.ToString(), parameters).ToList(); return LocateInfo; } } public List CheckMoveDetail2(string orderNo, int status, string carton_no) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select * from ( select a.*,t.part_spec2,(select top 1 locate_name from sys_locate_part (nolock) where part_id=a.part_id and enabled='Y' and locate_name != '' and locate_name is not null order by create_time) as recommend ,(select top 1 vendor_name from sys_stock as b (nolock) left join sys_vendor as c (nolock) on b.vendor_id = c.vendor_id where b.part_no = a.part_no and vendor_name is not null and vendor_name !='') as vendor_name from wms_move_detail as a (nolock) left join sys_part as t (nolock) on a.part_no=t.part_no where a.order_no=@order_no and a.enabled='Y' ) as ccc "); if (carton_no != "") { StringBuilder GetLocateSQL = new StringBuilder(1024); GetLocateSQL.Append(@" select top 1 * from sys_stock (nolock) where carton_no = @carton_no and enabled='Y' "); DynamicParameters parameters2 = new DynamicParameters(); parameters2.Add("@carton_no", carton_no); List getStocks = dbConn.Query(GetLocateSQL.ToString(), parameters2).ToList(); getqcSQL.Append("order by case when part_no='" + getStocks[0].PartNo + "' then 0 else 1 end, recommend "); } else { getqcSQL.Append("order by recommend "); } DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_no", orderNo); List WmsMoveDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsMoveDetail; } } public List GetLocateCapacity(string locate_id) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder GetLocateSQL = new StringBuilder(1024); GetLocateSQL.Append(@" select isnull(sum(qty),0) qty from sys_stock (nolock) where locate_id=@locate_id and status = @status and enabled='Y' "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@locate_id", locate_id); parameters.Add("@status", (int)WmsEnumUtil.StockStatus.ONLINED); List LocateInfo = dbConn.Query(GetLocateSQL.ToString(), parameters).ToList(); return LocateInfo; } } public string UpdteStatusOnLine(string loginId, string orderNo, SysStock Stock, List locate) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); List sqlStrings = new List(); List parameterList = new List(); //修改移库明细表 已收数, StringBuilder updateString2 = new StringBuilder(1024); updateString2.Append(@"update wms_move_detail set out_qty = out_qty + @out_qty,update_userid=@update_userid,update_time=@update_time,item_status = case when pick_qty = out_qty + @out_qty then @item_status else item_status end where order_no=@order_no and part_id=@part_id "); DynamicParameters updateStringparameters2 = new DynamicParameters(); updateStringparameters2.Add("@order_no", orderNo); updateStringparameters2.Add("@part_id", Stock.PartId); updateStringparameters2.Add("@out_qty", Stock.Qty); updateStringparameters2.Add("@item_status", (int)WmsEnumUtil.MoveOrderDetailStatus.COMPLETED); updateStringparameters2.Add("@update_userid", loginId); updateStringparameters2.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); sqlStrings.Add(updateString2.ToString()); parameterList.Add(updateStringparameters2); //修改移库条码表 40 已上线 StringBuilder updateString = new StringBuilder(1024); updateString.Append(" update wms_move_sn set status=@status,update_userid=@update_userid,update_time=@update_time where carton_no=@carton_no "); DynamicParameters updateStringparameters = new DynamicParameters(); updateStringparameters.Add("@status", (int)WmsEnumUtil.MoveOrderSnStatus.ONLINED); updateStringparameters.Add("@carton_no", Stock.CartonNo); updateStringparameters.Add("@update_userid", loginId); updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); sqlStrings.Add(updateString.ToString()); parameterList.Add(updateStringparameters); //修改条码表 70 已上线 StringBuilder updateString5 = new StringBuilder(1024); updateString5.Append("update sys_stock set status=@status,zone_id=@zone_id,zone_name=@zone_name,erp_warehouse=@erp_warehouse,update_userid=@update_userid,update_time=@update_time,locate_id=@locate_id,locate_name=@locate_name where carton_no= @carton_no "); DynamicParameters updateStringparameters5 = new DynamicParameters(); updateStringparameters5.Add("@carton_no", Stock.CartonNo); updateStringparameters5.Add("@status", (int)WmsEnumUtil.StockStatus.ONLINED); updateStringparameters5.Add("@locate_id", locate[0].LocateId); updateStringparameters5.Add("@locate_name", locate[0].LocateName); updateStringparameters5.Add("@zone_id", locate[0].ZoneId); updateStringparameters5.Add("@zone_name", locate[0].ZoneName); updateStringparameters5.Add("@erp_warehouse", locate[0].ErpWarehouse); updateStringparameters5.Add("@update_userid", loginId); updateStringparameters5.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); sqlStrings.Add(updateString5.ToString()); parameterList.Add(updateStringparameters5); //新增 sys_stock_trans 事务表 StringBuilder updateString4 = new StringBuilder(1024); updateString4.Append(@" INSERT INTO[dbo].[sys_stock_trans] ([trans_code] ,[carton_no] ,[part_id] ,[part_no] ,[part_spec] ,[src_locate_id] ,[src_locate_name] ,[dest_locate_id] ,[dest_locate_name] ,[old_qty] ,[new_qty] ,[trans_qty] ,[old_status] ,[new_status] ,[old_qms_status] ,[new_qms_status] ,[unit] ,[factory_id] ,[factory_code] ,[src_erp_warehouse] ,[dest_erp_warehouse] ,[src_warehouse_id] ,[src_warehouse_name] ,[dest_warehouse_id] ,[dest_warehouse_name] ,[src_zone_id] ,[src_zone_name] ,[dest_zone_id] ,[dest_zone_name] ,[enabled] ,[update_userid] ,[update_time] ,[create_userid] ,[create_time] ,[guid],ref_order_no) VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty ,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid,@ref_order_no )"); DynamicParameters updateStringparameters4 = new DynamicParameters(); updateStringparameters4.Add("@trans_code", (int)WmsEnumUtil.TransType.PICK_ONLINE); updateStringparameters4.Add("@carton_no", Stock.CartonNo); updateStringparameters4.Add("@part_id", Stock.PartId); updateStringparameters4.Add("@part_no", Stock.PartNo); updateStringparameters4.Add("@part_spec", Stock.PartSpec); updateStringparameters4.Add("@src_locate_id", Stock.LocateId); updateStringparameters4.Add("@src_locate_name", Stock.LocateName); updateStringparameters4.Add("@dest_locate_id", locate[0].LocateId); updateStringparameters4.Add("@dest_locate_name", locate[0].LocateName); updateStringparameters4.Add("@old_qty", Stock.Qty); updateStringparameters4.Add("@new_qty", Stock.Qty); updateStringparameters4.Add("@trans_qty", Stock.Qty); updateStringparameters4.Add("@old_status", Stock.Status); updateStringparameters4.Add("@new_status", (int)WmsEnumUtil.StockStatus.ONLINED); updateStringparameters4.Add("@old_qms_status", Stock.QmsStatus); updateStringparameters4.Add("@new_qms_status", Stock.QmsStatus); updateStringparameters4.Add("@unit", Stock.Unit); updateStringparameters4.Add("@factory_id", Stock.FactoryId); updateStringparameters4.Add("@factory_code", Stock.FactoryCode); updateStringparameters4.Add("@src_erp_warehouse", Stock.ErpWarehouse); updateStringparameters4.Add("@dest_erp_warehouse", Stock.ErpWarehouse); updateStringparameters4.Add("@src_warehouse_id", Stock.WarehouseId); updateStringparameters4.Add("@src_warehouse_name", Stock.WarehouseName); updateStringparameters4.Add("@dest_warehouse_id", Stock.WarehouseId); updateStringparameters4.Add("@dest_warehouse_name", Stock.WarehouseName); updateStringparameters4.Add("@src_zone_id", Stock.ZoneId); updateStringparameters4.Add("@src_zone_name", Stock.ZoneName); updateStringparameters4.Add("@dest_zone_id", Stock.ZoneId); updateStringparameters4.Add("@dest_zone_name", Stock.ZoneName); updateStringparameters4.Add("@enabled", Stock.Enabled); updateStringparameters4.Add("@update_userid", loginId); updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); updateStringparameters4.Add("@create_userid", loginId); updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); updateStringparameters4.Add("@guid", Guid.NewGuid()); updateStringparameters4.Add("@ref_order_no", orderNo); sqlStrings.Add(updateString4.ToString()); parameterList.Add(updateStringparameters4); //执行事务 IDbTransaction transaction = dbConn.BeginTransaction(); bool successCount = true; for (int i = 0; i < sqlStrings.Count; i++) { if (dbConn.Execute(sqlStrings[i], parameterList[i], transaction) < 1) { successCount = false; } } if (successCount) { transaction.Commit(); StringBuilder getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select a.ref_order_no,b.*,t.part_spec2 from wms_move_header as a (nolock) left join wms_move_detail as b (nolock) on a.order_no=b.order_no left join sys_part as t (nolock) on b.part_no=t.part_no where a.order_no=@order_no and b.enabled='Y'"); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_no", orderNo); List WmsMoveDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); if (WmsMoveDetail.Sum(a => a.ItemStatus) == (int)WmsEnumUtil.MoveOrderDetailStatus.COMPLETED * WmsMoveDetail.Count) { List sqlStrings2 = new List(); List parameterList2 = new List(); //修改移库条码表 40 已上线 StringBuilder updateString6 = new StringBuilder(1024); updateString6.Append(" update wms_move_header set order_status =@order_status ,update_userid=@update_userid,update_time=@update_time where order_no=@order_no ; "); DynamicParameters updateStringparameters6 = new DynamicParameters(); updateStringparameters6.Add("@order_status", (int)WmsEnumUtil.MoveOrderStatus.COMPLETED); updateStringparameters6.Add("@order_no", orderNo); updateStringparameters6.Add("@update_userid", loginId); updateStringparameters6.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); sqlStrings2.Add(updateString6.ToString()); parameterList2.Add(updateStringparameters6); IDbTransaction transaction2 = dbConn.BeginTransaction(); bool successCount2 = true; for (int i = 0; i < sqlStrings2.Count; i++) { if (dbConn.Execute(sqlStrings2[i], parameterList2[i], transaction2) < 0) { successCount2 = false; } } if (successCount2) { transaction2.Commit(); } else { transaction.Rollback(); } } return "OK"; } else { transaction.Rollback(); return "操作失败，请重新尝试！"; } } } public List GetRkInfos(string RKNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"SELECT * FROM [wms_rack] (nolock) where rack_no=@rack_no "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@rack_no", RKNo); List WmsRack = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsRack; } } public List GetRackPackage(string RKNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder RackPackageSQL = new StringBuilder(1024); RackPackageSQL.Append(@"SELECT b.* FROM wms_rack_package as a (nolock) left join sys_stock as b (nolock) on a.carton_no=b.carton_no and a.enabled='Y' where a.rack_no=@rack_no and a.enabled = 'Y' and b.enabled = 'Y' order by qty "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@rack_no", RKNo); List WmsRackPackage = dbConn.Query(RackPackageSQL.ToString(), parameters).ToList(); return WmsRackPackage; } } public List GetCartonInfoByRK(string cartonNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); string result = string.Empty; StringBuilder sql = new StringBuilder(1024); sql.Append("select * from sys_stock (nolock) where carton_no=@carton_no and enabled='Y' "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@carton_no", cartonNo); //List dt = dbConn.Query(sql.ToString(), parameters).ToList(); return dbConn.Query(sql.ToString(), parameters).ToList(); } } public List GetMaterialInfo(string cartonNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); string result = string.Empty; StringBuilder sql = new StringBuilder(1024); sql.Append("select * from sys_stock (nolock) where carton_no= @carton_no "); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@carton_no", cartonNo); return dbConn.Query(sql.ToString(), parameters).ToList(); } } public List CheckMaterialSplit(List oldStock, int splitNum, string loginId, int splitCount) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); List sqlStrings = new List(); List parameterList = new List(); decimal oldQty = oldStock[0].Qty; decimal newQty = oldStock[0].Qty - splitNum; //更新条码表，数量 StringBuilder updateString = new StringBuilder(1024); updateString.Append("update sys_stock set qty=@qty where carton_no=@carton_no "); DynamicParameters updateStringparameters = new DynamicParameters(); updateStringparameters.Add("@qty", newQty); updateStringparameters.Add("@carton_no", oldStock[0].CartonNo); updateStringparameters.Add("@update_userid", loginId); updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); sqlStrings.Add(updateString.ToString()); parameterList.Add(updateStringparameters); //新增 sys_stock_trans 事务表 #region sys_stock_trans 事务表 StringBuilder updateString4 = new StringBuilder(1024); updateString4.Append(@" INSERT INTO[dbo].[sys_stock_trans] ([trans_code] ,[carton_no] ,[part_id] ,[part_no] ,[part_spec] ,[src_locate_id] ,[src_locate_name] ,[dest_locate_id] ,[dest_locate_name] ,[old_qty] ,[new_qty] ,[trans_qty] ,[old_status] ,[new_status] ,[old_qms_status] ,[new_qms_status] ,[unit] ,[factory_id] ,[factory_code] ,[src_erp_warehouse] ,[dest_erp_warehouse] ,[src_warehouse_id] ,[src_warehouse_name] ,[dest_warehouse_id] ,[dest_warehouse_name] ,[src_zone_id] ,[src_zone_name] ,[dest_zone_id] ,[dest_zone_name] ,[enabled] ,[update_userid] ,[update_time] ,[create_userid] ,[create_time] ,[guid]) VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty ,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid )"); DynamicParameters updateStringparameters4 = new DynamicParameters(); updateStringparameters4.Add("@trans_code", (int)WmsEnumUtil.TransType.PART_SPLIT); updateStringparameters4.Add("@carton_no", oldStock[0].CartonNo); updateStringparameters4.Add("@part_id", oldStock[0].PartId); updateStringparameters4.Add("@part_no", oldStock[0].PartNo); updateStringparameters4.Add("@part_spec", oldStock[0].PartSpec); updateStringparameters4.Add("@src_locate_id", oldStock[0].LocateId); updateStringparameters4.Add("@src_locate_name", oldStock[0].LocateName); updateStringparameters4.Add("@dest_locate_id", oldStock[0].LocateId); updateStringparameters4.Add("@dest_locate_name", oldStock[0].LocateName); updateStringparameters4.Add("@old_qty", oldQty); updateStringparameters4.Add("@new_qty", newQty); updateStringparameters4.Add("@trans_qty", oldStock[0].Qty); updateStringparameters4.Add("@old_status", oldStock[0].Status); updateStringparameters4.Add("@new_status", oldStock[0].Status); updateStringparameters4.Add("@old_qms_status", oldStock[0].QmsStatus); updateStringparameters4.Add("@new_qms_status", oldStock[0].QmsStatus); updateStringparameters4.Add("@unit", oldStock[0].Unit); updateStringparameters4.Add("@factory_id", oldStock[0].FactoryId); updateStringparameters4.Add("@factory_code", oldStock[0].FactoryCode); updateStringparameters4.Add("@src_erp_warehouse", oldStock[0].ErpWarehouse); updateStringparameters4.Add("@dest_erp_warehouse", oldStock[0].ErpWarehouse); updateStringparameters4.Add("@src_warehouse_id", oldStock[0].WarehouseId); updateStringparameters4.Add("@src_warehouse_name", oldStock[0].WarehouseName); updateStringparameters4.Add("@dest_warehouse_id", oldStock[0].WarehouseId); updateStringparameters4.Add("@dest_warehouse_name", oldStock[0].WarehouseName); updateStringparameters4.Add("@src_zone_id", oldStock[0].ZoneId); updateStringparameters4.Add("@src_zone_name", oldStock[0].ZoneName); updateStringparameters4.Add("@dest_zone_id", oldStock[0].ZoneId); updateStringparameters4.Add("@dest_zone_name", oldStock[0].ZoneName); updateStringparameters4.Add("@enabled", oldStock[0].Enabled); updateStringparameters4.Add("@update_userid", loginId); updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); updateStringparameters4.Add("@create_userid", loginId); updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); updateStringparameters4.Add("@guid", Guid.NewGuid()); sqlStrings.Add(updateString4.ToString()); parameterList.Add(updateStringparameters4); #endregion oldStock[0].CartonNo = checkCartonNo(oldStock[0].CartonNo, splitCount); oldStock[0].Qty = splitNum; //插入拆分出的箱条码 #region 插入拆分出的箱条码 StringBuilder updateString3 = new StringBuilder(1024); updateString3.Append(@" INSERT INTO [dbo].[sys_stock] ([vendor_id] ,[vendor_code] ,[carton_no] ,[part_id] ,[part_no] ,[part_spec] ,[lot_no] ,[fix_lot_no] ,[status] ,[qty] ,[snp_qty] ,[locate_id] ,[locate_name] ,[group_no] ,[erp_warehouse] ,[date_code] ,[qms_status] ,[ref_order_no] ,[unit] ,[dock] ,[warehouse_id] ,[warehouse_name] ,[zone_id] ,[zone_name] ,[printed] ,[print_time] ,[remark] ,[factory_id] ,[factory_code] ,[enabled] ,[create_userid] ,[create_time] ,[update_userid] ,[update_time] ,[guid]) VALUES(@vendor_id,@vendor_code,@carton_no,@part_id,@part_no,@part_spec,@lot_no,@fix_lot_no,@status,@qty,@snp_qty ,@locate_id,@locate_name,@group_no,@erp_warehouse,@date_code,@qms_status,@ref_order_no,@unit,@dock,@warehouse_id,@warehouse_name,@zone_id,@zone_name ,@printed,@print_time,@remark,@factory_id,@factory_code,@enabled,@create_userid,@create_time,@update_userid,@update_time,@guid )"); DynamicParameters updateStringparameters3 = new DynamicParameters(); updateStringparameters3.Add("@vendor_id", oldStock[0].VendorId); updateStringparameters3.Add("@vendor_code", oldStock[0].VendorName); updateStringparameters3.Add("@carton_no", oldStock[0].CartonNo); updateStringparameters3.Add("@part_id", oldStock[0].PartId); updateStringparameters3.Add("@part_no", oldStock[0].PartNo); updateStringparameters3.Add("@part_spec", oldStock[0].PartSpec); updateStringparameters3.Add("@lot_no", oldStock[0].LotNo); updateStringparameters3.Add("@fix_lot_no", oldStock[0].FixLotNo); updateStringparameters3.Add("@status", oldStock[0].Status); updateStringparameters3.Add("@qty", splitNum); updateStringparameters3.Add("@snp_qty", oldStock[0].SnpQty); updateStringparameters3.Add("@locate_id", oldStock[0].LocateId); updateStringparameters3.Add("@locate_name", oldStock[0].LocateName); updateStringparameters3.Add("@group_no", oldStock[0].GroupNo); updateStringparameters3.Add("@erp_warehouse", oldStock[0].ErpWarehouse); updateStringparameters3.Add("@date_code", oldStock[0].DateCode); updateStringparameters3.Add("@qms_status", oldStock[0].QmsStatus); updateStringparameters3.Add("@ref_order_no", oldStock[0].RefOrderNo); updateStringparameters3.Add("@unit", oldStock[0].Unit); updateStringparameters3.Add("@dock", oldStock[0].Dock); updateStringparameters3.Add("@warehouse_id", oldStock[0].WarehouseId); updateStringparameters3.Add("@warehouse_name", oldStock[0].WarehouseName); updateStringparameters3.Add("@zone_id", oldStock[0].ZoneId); updateStringparameters3.Add("@zone_name", oldStock[0].ZoneName); updateStringparameters3.Add("@printed", oldStock[0].Printed); updateStringparameters3.Add("@print_time", oldStock[0].PrintTime); updateStringparameters3.Add("@remark", oldStock[0].Remark); updateStringparameters3.Add("@factory_id", oldStock[0].FactoryId); updateStringparameters3.Add("@factory_code", oldStock[0].FactoryCode); updateStringparameters3.Add("@enabled", oldStock[0].Enabled); updateStringparameters3.Add("@update_userid", loginId); updateStringparameters3.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); updateStringparameters3.Add("@create_userid", oldStock[0].CreateUserId); updateStringparameters3.Add("@create_time", oldStock[0].CreateTime); updateStringparameters3.Add("@guid", Guid.NewGuid()); sqlStrings.Add(updateString3.ToString()); parameterList.Add(updateStringparameters3); #endregion //新增 sys_stock_trans 事务表 #region sys_stock_trans 事务表 StringBuilder updateString5 = new StringBuilder(1024); updateString5.Append(@" INSERT INTO[dbo].[sys_stock_trans] ([trans_code] ,[carton_no] ,[part_id] ,[part_no] ,[part_spec] ,[src_locate_id] ,[src_locate_name] ,[dest_locate_id] ,[dest_locate_name] ,[old_qty] ,[new_qty] ,[trans_qty] ,[old_status] ,[new_status] ,[old_qms_status] ,[new_qms_status] ,[unit] ,[factory_id] ,[factory_code] ,[src_erp_warehouse] ,[dest_erp_warehouse] ,[src_warehouse_id] ,[src_warehouse_name] ,[dest_warehouse_id] ,[dest_warehouse_name] ,[src_zone_id] ,[src_zone_name] ,[dest_zone_id] ,[dest_zone_name] ,[enabled] ,[update_userid] ,[update_time] ,[create_userid] ,[create_time] ,[guid]) VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty ,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid )"); DynamicParameters updateStringparameters5 = new DynamicParameters(); updateStringparameters5.Add("@trans_code", (int)WmsEnumUtil.TransType.PART_SPLIT); updateStringparameters5.Add("@carton_no", oldStock[0].CartonNo); updateStringparameters5.Add("@part_id", oldStock[0].PartId); updateStringparameters5.Add("@part_no", oldStock[0].PartNo); updateStringparameters5.Add("@part_spec", oldStock[0].PartSpec); updateStringparameters5.Add("@src_locate_id", oldStock[0].LocateId); updateStringparameters5.Add("@src_locate_name", oldStock[0].LocateName); updateStringparameters5.Add("@dest_locate_id", oldStock[0].LocateId); updateStringparameters5.Add("@dest_locate_name", oldStock[0].LocateName); updateStringparameters5.Add("@old_qty", 0); updateStringparameters5.Add("@new_qty", splitNum); updateStringparameters5.Add("@trans_qty", oldStock[0].Qty); updateStringparameters5.Add("@old_status", oldStock[0].Status); updateStringparameters5.Add("@new_status", oldStock[0].Status); updateStringparameters5.Add("@old_qms_status", oldStock[0].QmsStatus); updateStringparameters5.Add("@new_qms_status", oldStock[0].QmsStatus); updateStringparameters5.Add("@unit", oldStock[0].Unit); updateStringparameters5.Add("@factory_id", oldStock[0].FactoryId); updateStringparameters5.Add("@factory_code", oldStock[0].FactoryCode); updateStringparameters5.Add("@src_erp_warehouse", oldStock[0].ErpWarehouse); updateStringparameters5.Add("@dest_erp_warehouse", oldStock[0].ErpWarehouse); updateStringparameters5.Add("@src_warehouse_id", oldStock[0].WarehouseId); updateStringparameters5.Add("@src_warehouse_name", oldStock[0].WarehouseName); updateStringparameters5.Add("@dest_warehouse_id", oldStock[0].WarehouseId); updateStringparameters5.Add("@dest_warehouse_name", oldStock[0].WarehouseName); updateStringparameters5.Add("@src_zone_id", oldStock[0].ZoneId); updateStringparameters5.Add("@src_zone_name", oldStock[0].ZoneName); updateStringparameters5.Add("@dest_zone_id", oldStock[0].ZoneId); updateStringparameters5.Add("@dest_zone_name", oldStock[0].ZoneName); updateStringparameters5.Add("@enabled", oldStock[0].Enabled); updateStringparameters5.Add("@update_userid", loginId); updateStringparameters5.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); updateStringparameters5.Add("@create_userid", loginId); updateStringparameters5.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); updateStringparameters5.Add("@guid", Guid.NewGuid()); sqlStrings.Add(updateString5.ToString()); parameterList.Add(updateStringparameters5); #endregion //执行事务 IDbTransaction transaction = dbConn.BeginTransaction(); bool successCount = true; for (int i = 0; i < sqlStrings.Count; i++) { if (dbConn.Execute(sqlStrings[i], parameterList[i], transaction) < 1) { successCount = false; } } if (successCount) { transaction.Commit(); return oldStock; } else { transaction.Rollback(); return null; } } } public string checkCartonNo(string carton, int splitCount) { List MaterialInfo = GetMaterialInfo(carton + "-" + splitCount); if (MaterialInfo.Count > 0) { //有重复 return checkCartonNo(carton, splitCount + 1); } else { return carton + "-" + splitCount; } } public string SetMoveOrderClose(string orderNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); string result = string.Empty; List sqlLists = new List(); List parameters = new List(); DynamicParameters dynamic = new DynamicParameters(); StringBuilder SqlStringBuilder = new StringBuilder(1024); string sqldetailcheck = "SELECT * FROM wms_move_detail (NOLOCK) WHERE order_no ='" + orderNo + "' and pick_qty!=out_qty"; List wmsMoves = dbConn.Query(sqldetailcheck).ToList(); if (wmsMoves.Count > 0) { return "操作数小于配料数不能关闭单据，请检查！"; } SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append(@"update sys_locate_part set is_safety_pull= 0 where safety_pull_order=@order_no "); sqlLists.Add(SqlStringBuilder.ToString()); dynamic = new DynamicParameters(); dynamic.Add("@order_no", orderNo); parameters.Add(dynamic); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update wms_move_header set order_status='60',update_userid=@updateUserid,update_time = CONVERT(varchar(50), GETDATE(), 21) WHERE order_no=@order_no"); sqlLists.Add(SqlStringBuilder.ToString()); dynamic = new DynamicParameters(); dynamic.Add("@updateUserid", 0); dynamic.Add("@order_no", orderNo); parameters.Add(dynamic); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("update wms_move_detail set item_status='100',update_userid=@updateUserid,update_time = CONVERT(varchar(50), GETDATE(), 21) WHERE order_no=@order_no"); sqlLists.Add(SqlStringBuilder.ToString()); dynamic = new DynamicParameters(); dynamic.Add("@updateUserid", 0); dynamic.Add("@order_no", orderNo); parameters.Add(dynamic); bool flag = ExecuteTransaction(sqlLists, parameters); if (flag) { return "OK"; } else { return "操作失败，请重新尝试！"; } } } //根据单据号更新单据主表 public int UpdateMoveDetailByOrderNo(string orderNo, string loginId) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { if (dbConn.State == ConnectionState.Closed) { dbConn.Open(); } StringBuilder updateString6 = new StringBuilder(1024); updateString6.Append(" update wms_move_header set order_status =@order_status ,update_userid=@update_userid,update_time=@update_time where order_no=@order_no ; "); DynamicParameters updateStringparameters6 = new DynamicParameters(); updateStringparameters6.Add("@order_status", (int)WmsEnumUtil.MoveOrderStatus.COMPLETED); updateStringparameters6.Add("@order_no", orderNo); updateStringparameters6.Add("@update_userid", loginId); updateStringparameters6.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); int retNum = dbConn.Execute(updateString6.ToString(), updateStringparameters6); return retNum; } } //根据单据号获取明细数据 public List GetWmsMoveDetailByOrderNo(string orderNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { if (dbConn.State == ConnectionState.Closed) { dbConn.Open(); } StringBuilder getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select a.ref_order_no,b.*,t.part_spec2 from wms_move_header as a (nolock) left join wms_move_detail as b (nolock) on a.order_no=b.order_no left join sys_part as t (nolock) on b.part_no=t.part_no where a.order_no=@order_no and b.enabled='Y'"); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_no", orderNo); List WmsMoveDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsMoveDetail; } } //事务批量执行添加、修改 public int SQLTransaction(List sqlStrings, List parameterList) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { if (dbConn.State == ConnectionState.Closed) { dbConn.Open(); } //执行事务 IDbTransaction transaction = dbConn.BeginTransaction(); if (parameterList == null || parameterList.Count == 0) { try { for (int i = 0; i < sqlStrings.Count; i++) { dbConn.Execute(sqlStrings[i], null, transaction); } transaction.Commit(); return 1; } catch (Exception exception) { transaction.Rollback(); return 0; } } else { try { for (int i = 0; i < sqlStrings.Count; i++) { dbConn.Execute(sqlStrings[i], parameterList[i], transaction); } transaction.Commit(); return 1; } catch (Exception exception) { transaction.Rollback(); return 0; } } } } } }