using Dapper; using Estsh.Core.Base; using Estsh.Core.Dapper; using Estsh.Core.Model.EnumUtil; using Estsh.Core.Models; using Estsh.Core.Repositories; using Estsh.Core.Wms.IRepositories; using System.Data; using System.Text; namespace Estsh.Core.Wms.Repositories { /// /// 销售配料 /// public class SaleBatchRepository : BaseRepository, ISaleBatchRepository { public SaleBatchRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext) { } public List CheckMoveDetail(string orderNo, int status) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { StringBuilder getqcSQL = new StringBuilder(1024); getqcSQL.Append(@"select *,(select top 1 locate_name from sys_stock (nolock) where part_id=a.part_id and status=@status order by create_time) as recommend_locate from wms_outstock_detail as a (nolock) where order_no=@order_no and enabled='Y'"); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@order_no", orderNo); parameters.Add("@status", status); List WmsOutstockDetail = dbConn.Query(getqcSQL.ToString(), parameters).ToList(); return WmsOutstockDetail; } } 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 SysCustomerPart GetCustomerPart(string partNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder stringBuilder = new StringBuilder(1024); stringBuilder.Append("select * from sys_customer_part scp where scp.part_no = @partNo and enabled = 'Y'"); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@partNo", partNo); return dbConn.QueryFirstOrDefault(stringBuilder.ToString(), parameters); } } 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 SetObjectDetail UpdteStatus(string orderNo, List detail, SysStock Stock, string groupNo, string loginId, string customBarcode) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); SetObjectDetail rfInfo = new SetObjectDetail();//返回消息 List sqlStrings = new List(); List parameterList = new List(); //修改移库主表 30 处理中 StringBuilder updateString = new StringBuilder(1024); updateString.Append(" update wms_outstock set order_status=@order_status,update_userid=@update_userid,update_time=@update_time where order_no=@order_no "); DynamicParameters updateStringparameters = new DynamicParameters(); updateStringparameters.Add("@order_status", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING); updateStringparameters.Add("@order_no", detail[0].OrderNo); 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); //修改移库明细表 30 处理中 StringBuilder updateString2 = new StringBuilder(1024); updateString2.Append(@"update wms_outstock_detail set pick_qty = pick_qty + @pick_qty, item_status=@item_status,update_userid=@update_userid,update_time=@update_time where ruid=@ruid "); DynamicParameters updateStringparameters2 = new DynamicParameters(); updateStringparameters2.Add("@item_status", (int)WmsEnumUtil.MoveOrderDetailStatus.BATCHING); updateStringparameters2.Add("@ruid", detail[0].Ruid); updateStringparameters2.Add("@pick_qty", Stock.Qty); 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); //修改条码表 60 配料中 StringBuilder updateString5 = new StringBuilder(1024); updateString5.Append("update sys_stock set status=@status,group_no=@group_no,update_userid=@update_userid,carton_no_customer=@carton_no_customer,update_time=@update_time where carton_no= @carton_no "); DynamicParameters updateStringparameters5 = new DynamicParameters(); updateStringparameters5.Add("@carton_no", Stock.CartonNo); updateStringparameters5.Add("@carton_no_customer", customBarcode); updateStringparameters5.Add("@group_no", groupNo); updateStringparameters5.Add("@status", (int)WmsEnumUtil.StockStatus.BATCHED); 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); //插入 wms_outstock_sn 条码表 StringBuilder updateString3 = new StringBuilder(1024); updateString3.Append(@"INSERT INTO [dbo].[wms_outstock_sn] ([order_no] ,[item_no] ,[part_id] ,[part_no] ,[part_spec] ,[carton_no] ,[qty] ,[unit] ,[status] ,[factory_id] ,[factory_code] ,[enabled] ,[create_userid] ,[create_time] ,[update_userid] ,[update_time] ,[guid]) VALUES (@order_no,@item_no,@part_id,@part_no,@part_spec,@carton_no,@qty,@unit,@status,@factory_id,@factory_code,@enabled,@create_userid,@create_time,@update_userid,@update_time,@guid)"); DynamicParameters updateStringparameters3 = new DynamicParameters(); updateStringparameters3.Add("@order_no", detail[0].OrderNo); updateStringparameters3.Add("@item_no", detail[0].ItemNo); updateStringparameters3.Add("@part_id", detail[0].PartId); updateStringparameters3.Add("@part_no", detail[0].PartNo); updateStringparameters3.Add("@part_spec", detail[0].PartSpec); updateStringparameters3.Add("@carton_no", Stock.CartonNo); updateStringparameters3.Add("@qty", Stock.Qty); updateStringparameters3.Add("@unit", Stock.Unit); updateStringparameters3.Add("@status", (int)WmsEnumUtil.MoveOrderSnStatus.BATCHED); updateStringparameters3.Add("@factory_id", detail[0].FactoryId); updateStringparameters3.Add("@factory_code", detail[0].FactoryCode); updateStringparameters3.Add("@enabled", "Y"); updateStringparameters3.Add("@update_userid", loginId); updateStringparameters3.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); updateStringparameters3.Add("@create_userid", loginId); updateStringparameters3.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); updateStringparameters3.Add("@guid", Guid.NewGuid()); sqlStrings.Add(updateString3.ToString()); parameterList.Add(updateStringparameters3); //新增 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.SALE_PICK); 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", Stock.LocateId); updateStringparameters4.Add("@dest_locate_name", Stock.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.BATCHED); 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", detail[0].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 stringBuilder = new StringBuilder(1024); stringBuilder.Append("SELECT SUM( CONVERT(INT, c.qty)) qty,SUM(CONVERT(INT, c.pick_qty)) pick_qty,(select count(1) from wms_outstock_sn where order_no = c.order_no and part_no=c.part_no) as carton_num,c.part_no,c.part_spec,pt.part_spec2,(select top 1 locate_name from sys_stock (nolock) where part_id=c.part_id and status=50 order by create_time) as recommend_locate,(select top 1 vendor_name from sys_stock as sb left join sys_vendor as sc on sb.vendor_id = sc.vendor_id where sb.part_no = c.part_no and vendor_name is not null and vendor_name !='' ) as vendor_name FROM dbo.wms_outstock a (nolock) "); stringBuilder.Append("LEFT JOIN dbo.wms_outstock_detail c (nolock) ON a.order_no=c.order_no "); stringBuilder.Append("LEFT JOIN dbo.sys_part pt (nolock) ON c.part_id=pt.part_id "); stringBuilder.Append(" WHERE a.order_no = '" + orderNo + "' AND order_status in ('30') AND (order_type=" + (int)WmsEnumUtil.OutStockType.SALE_SHIPPING + " OR order_type=" + (int)WmsEnumUtil.OutStockType.SALE_SHIPPING_NJIS + ") and a.enabled='Y' group by c.part_id,c.part_no,c.part_spec,pt.part_spec2,c.order_no"); List wmsMoves1 = dbConn.Query(stringBuilder.ToString()).ToList(); for (int i = 0; i < wmsMoves1.Count; i++) { if (wmsMoves1[i].Qty != wmsMoves1[i].PickQty) { rfInfo.type = "PASS"; rfInfo.message = "操作成功!请继续扫描箱条码"; rfInfo.wmsObjectDetail = wmsMoves1.ConvertAll(s => (object)s); return rfInfo; } } rfInfo.type = "PASS"; rfInfo.message = "订单操作完成!请切换订单或移至销售发运"; rfInfo.wmsObjectDetail = wmsMoves1.ConvertAll(s => (object)s); return rfInfo; } else { transaction.Rollback(); rfInfo.type = "FAIL"; rfInfo.message = "未查询到数据，请检查！"; return rfInfo; } } } 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 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 GetSaleBatchOrderList(string orderNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("SELECT a.order_no,b.enum_desc AS order_type_desc,a.create_time,d.emp_no,a.factory_code,a.enabled,a.order_status FROM dbo.wms_outstock a (nolock) "); SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum b (nolock) ON a.order_type = b.enum_value AND b.enum_type = 'wms_outstock_order_type' "); SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d (nolock) ON a.create_userid = d.emp_id "); SqlStringBuilder.Append("WHERE a.order_no like '%" + orderNo + "%' AND order_status in ('10','30') AND (order_type=" + (int)WmsEnumUtil.OutStockType.SALE_SHIPPING + " OR order_type=" + (int)WmsEnumUtil.OutStockType.SALE_SHIPPING_NJIS + ") and a.enabled = 'Y' "); SqlStringBuilder.Append("AND order_no IN(SELECT t.order_no FROM wms_outstock_detail t WHERE a.order_no= t.order_no GROUP BY t.order_no HAVING SUM(t.qty)!=SUM(t.pick_qty)) "); SqlStringBuilder.Append(" order by case when order_status='30' then 0 else 1 end, create_time "); List wmsMoves = dbConn.Query(SqlStringBuilder.ToString()).ToList(); return wmsMoves; } } /// /// 获取销售配料单据信息通过订单号 /// /// /// public List GetSaleBatchOrderListByOrderNo(string orderNo) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); SetObjectDetail rfInfo = new SetObjectDetail(); StringBuilder stringBuilder = new StringBuilder(1024); stringBuilder.Append("SELECT SUM( CONVERT(INT, c.qty)) qty,SUM(CONVERT(INT, c.pick_qty)) pick_qty,(select count(1) from wms_outstock_sn where order_no = c.order_no and part_no=c.part_no) as carton_num,c.part_no,c.part_spec,pt.part_spec2,(select top 1 locate_name from sys_stock (nolock) where part_id=c.part_id and status=50 order by create_time) as recommend_locate,(select top 1 vendor_name from sys_stock as sb left join sys_vendor as sc on sb.vendor_id = sc.vendor_id where sb.part_no = c.part_no and vendor_name is not null and vendor_name !='' ) as vendor_name FROM dbo.wms_outstock a (nolock) "); stringBuilder.Append("LEFT JOIN dbo.wms_outstock_detail c (nolock) ON a.order_no=c.order_no "); stringBuilder.Append("LEFT JOIN dbo.sys_part pt (nolock) ON c.part_id=pt.part_id "); stringBuilder.Append(" WHERE a.order_no = '" + orderNo + "' AND (order_type=" + (int)WmsEnumUtil.OutStockType.SALE_SHIPPING + " OR order_type=" + (int)WmsEnumUtil.OutStockType.SALE_SHIPPING_NJIS + ") and a.enabled='Y' group by c.part_id,c.part_no,c.part_spec,pt.part_spec2,c.order_no"); List wmsMoves = dbConn.Query(stringBuilder.ToString()).ToList(); return wmsMoves; } } //事务批量执行添加、修改 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; } } } } } }