public int ConfirmLockState(WMS_BN_LockMaterial info, out string msg) { msg = ""; db.UseTransaction(true); var rowsAffected = 0; rowsAffected = db.Update <WMS_BN_LockMaterial>("WMS_BN_LockMaterial", info).AutoMap(x => x.ID).Where(x => x.ID).Execute(); if (rowsAffected < 0) { db.Rollback(); return(rowsAffected); } var sql = String.Format(@"select InventoryCode from SYS_Part where InventoryCode = '{0}'", info.InventoryCode); sql = String.Format(@"select * from WMS_BN_RealStock where InventoryCode = '{0}' and WarehouseCode='{1}'", info.InventoryCode, info.WarehouseCode); WMS_BN_RealStock product = db.Sql(sql).QuerySingle <WMS_BN_RealStock>(); if (product == null) { msg = "该仓库物料不存在!"; return(0); } if (info.LockState == 1) { product.RealStock = product.RealStock - info.LockQuantity; product.LockStock = product.LockStock + info.LockQuantity; } else if (info.LockState == 2) { product.RealStock = product.RealStock + info.LockQuantity; product.LockStock = product.LockStock - info.LockQuantity; } product.ModifyTime = DateTime.Now; rowsAffected = db.Update <WMS_BN_RealStock>("WMS_BN_RealStock", product).AutoMap(x => x.ID).Where(x => x.ID).Execute(); if (rowsAffected < 0) { db.Rollback(); return(rowsAffected); } db.Commit(); return(rowsAffected); }
public int AuditBillCode(string BillCode, out string msg) { msg = string.Empty; //var rowsAffected = 0; string sql = String.Format(@" SELECT t1.*, t1.QualifiedQuntity, t3.WarehouseID, t3.WarehouseName, t2.CheckQuantity ,t4.QuantityUnit ,t4.Model , t4.InventoryCode ,t4.InventoryName ,t3.WorkshopID ,t3.WorkshopName FROM dbo.QMS_ProcessInspection t1 LEFT JOIN MES_ProcessInspectionRequest t2 ON t1.PBillCode = t2.BillCode AND t1.IsEnable = 1 AND t2.IsEnable = 1 AND t2.BillState = 2 LEFT JOIN dbo.MES_BN_ProductProcessRoute t3 ON t2.ProductProcessRouteID = t3.ID AND t3.IsEnable = 1 inner JOIN dbo.SYS_Part t4 ON t1.PartCode=t4.PartCode AND t4.IsEnable=1 where t1.BillCode='{0}'" , BillCode); dynamic stateList = db.Sql(sql).QueryMany <dynamic>(); if (stateList[0].BillState == 2) { msg = "单据已审核"; return(0); } else { sql = string.Format(@"update QMS_ProcessInspection set BillState=2 where BillCode='{0}'", BillCode); //string insertSql=string.Format(@"") using (db.UseTransaction(true)) { int countMain = db.Sql(sql).Execute(); string documentNo = ""; documentNo = MmsHelper.GetOrderNumber("WMS_BN_BillMain", "BillCode", "ZXRK", "", ""); //billstate = 2; #region WMS_BN_BillMain表 string sqlA = string.Format(@" INSERT INTO dbo.WMS_BN_BillMain ( ID, BillCode, BillType, ContractCode, ProjectName, SalesmanCode, Salesman, DepartmentID, DepartmentName, SupplierCode, SupplierName, WarehouseCode, WarehouseName, PickPersonCode, PickPerson, ApproveState, ApprovePerson, ApproveDate, ApproveRemark, Remark, CreatePerson, CreateTime, ModifyPerson, ModifyTime, IsEnable ) VALUES ( {0}, -- ID - int '{1}', -- BillCode - varchar(50) {2}, -- BillType - int '{3}', -- ContractCode - varchar(50) '{4}', -- ProjectName - varchar(50) '', -- SalesmanCode - varchar(50) '', -- Salesman - varchar(50) '', -- DepartmentID - varchar(50) '', -- DepartmentName - varchar(50) '', -- SupplierCode - varchar(50) '', -- SupplierName - varchar(50) '{5}', -- WarehouseCode - varchar(50) '{6}', -- WarehouseName - varchar(50) '', -- PickPersonCode - varchar(50) '', -- PickPerson - varchar(50) '1', -- ApproveState - varchar(50) '', -- ApprovePerson - varchar(50) GETDATE(), -- ApproveDate - datetime '', -- ApproveRemark - varchar(500) '', -- Remark - varchar(500) '{7}', -- CreatePerson - varchar(50) GETDATE(), -- CreateTime - datetime '{7}', -- ModifyPerson - varchar(50) GETDATE(), -- ModifyTime - datetime 1 -- IsEnable - int ) ", Convert.ToInt32(new WMS_BN_BillMainService().GetNewKey("ID", "Maxplus")), documentNo, 8, stateList[0].ContractCode, stateList[0].ProjectName, stateList[0].WarehouseID, stateList[0].WarehouseName, MmsHelper.GetUserName()); #endregion int countA = db.Sql(sqlA).Execute(); #region WMS_BN_BillDetail表 string sqlB = string.Format(@" INSERT INTO dbo.WMS_BN_BillDetail ( BillCode, OrderBillCode, InventoryCode, InventoryName, Specs, Unit, MateNum, ConfirmNum, UnitPrice, TotalPrice, PackageCode, BatchCode, PBillCode, AccountabilityCode, Remark, CreatePerson, CreateTime, ModifyPerson, ModifyTime, IsEnable ) VALUES ( '{0}', -- BillCode - varchar(50) '', -- OrderBillCode - varchar(50) N'{1}', -- InventoryCode - nvarchar(50) N'{2}', -- InventoryName - nvarchar(50) '{3}', -- Specs - varchar(50) N'{4}', -- Unit - nvarchar(10) {5}, -- MateNum - float {6}, -- ConfirmNum - float 0.0, -- UnitPrice - float 0.0, -- TotalPrice - float '', -- PackageCode - varchar(50) '', -- BatchCode - varchar(50) '', -- PBillCode - varchar(50) '', -- AccountabilityCode - varchar(50) N'', -- Remark - nvarchar(500) N'{7}', -- CreatePerson - nvarchar(50) GETDATE(), -- CreateTime - datetime N'{7}', -- ModifyPerson - nvarchar(50) GETDATE(), -- ModifyTime - datetime 1 -- IsEnable - int ) ", documentNo, stateList[0].InventoryCode, stateList[0].InventoryName, stateList[0].Model, stateList[0].Unit, stateList[0].QualifiedQuntity, stateList[0].QualifiedQuntity, MmsHelper.GetUserName()); #endregion int countB = db.Sql(sqlB).Execute(); int countC = 0; bool aaa = true; bool ccc = true; //更新库存表 //库存表 List <WMS_BN_RealStock> realStockData = db.Sql("SELECT * FROM WMS_BN_RealStock WHERE IsEnable = 1").QueryMany <WMS_BN_RealStock>(); var realStocks = realStockData.Where(s => { bool a = s.WarehouseCode == null ? false : s.WarehouseCode.Equals(stateList[0].WarehouseID); bool b = s.InventoryCode == null ? false : s.InventoryCode.Equals(stateList[0].InventoryCode); bool c = s.BatchCode == null ? false : s.BatchCode.Equals(stateList[0].BatchCode); return(a && b && c); }).ToList(); string updateStockSal = ""; if (stateList[0].WarehouseName == stateList[0].WorkshopName) { #region //没有库存则新增 if (realStocks.Count <= 0) { WMS_BN_RealStock realStockModel = new WMS_BN_RealStock() { InventoryCode = stateList[0].InventoryCode, InventoryName = stateList[0].InventoryName, Model = stateList[0].Model, RealStock = (double)stateList[0].QualifiedQuntity, TotalStock = (double)stateList[0].QualifiedQuntity, WarehouseCode = stateList[0].WarehouseID, WarehouseName = stateList[0].WarehouseName, BatchCode = stateList[0].BatchCode, Unit = stateList[0].Unit, CreatePerson = MmsHelper.GetUserName(), CreateTime = DateTime.Now, ModifyPerson = MmsHelper.GetUserName(), ModifyTime = DateTime.Now, IsEnable = 1 }; countC = db.Insert("WMS_BN_RealStock", realStockModel).AutoMap(x => x.ID).Execute(); updateStockSal += String.Format(@" update WMS_BN_BillMain set ApproveState=2 where BillCode = '{0}'", documentNo); } //有则更新数量 else if (realStocks.Count.Equals(1)) { updateStockSal = string.Format("UPDATE WMS_BN_RealStock SET RealStock = {0},TotalStock = {1},ModifyPerson = '{2}',ModifyTime = '{3}' WHERE ID = {4} ;\r\n", realStocks[0].RealStock + stateList[0].QualifiedQuntity, realStocks[0].TotalStock + stateList[0].QualifiedQuntity, MmsHelper.GetUserName(), DateTime.Now, realStocks[0].ID); updateStockSal += String.Format(@" update WMS_BN_BillMain set ApproveState=2 where BillCode = '{0}'", documentNo); } else { return(0); msg = @"仓库物料种类繁多,可惜不知取哪一种!"; } #endregion ccc = db.Sql(updateStockSal).Execute() > 0; } if (aaa && countMain > 0 && countA > 0 && countB > 0 && ccc && countC > 0) { db.Commit(); msg = "审核单据成功"; return(1); } else { db.Rollback(); msg = "审核单据失败,请确认数据"; return(1); } } } }
/// <summary> /// 物料扫码出库 /// </summary> /// <returns></returns> public ResultModel MaterialOutput(dynamic data) { try { DateTime newDT = DateTime.Now; string documentNo = MmsHelper.GetOrderNumber("WMS_BN_BillMain", "BillCode", "QTCK", "", ""); WMS_BN_BillMain bMainModel = new WMS_BN_BillMain() { ID = Convert.ToInt32(new WMS_BN_BillMainService().GetNewKey("ID", "Maxplus")), BillCode = documentNo, BillType = 7, IsEnable = 1, ContractCode = data["mainData"]["ContractCode"], ProjectName = data["mainData"]["ProjectName"], WarehouseCode = data["mainData"]["WarehouseCode"], WarehouseName = data["mainData"]["WarehouseName"], ApproveState = data["mainData"]["ApproveState"], ApprovePerson = data["mainData"]["UserName"], ApproveDate = newDT, CreatePerson = data["userData"]["UserName"], CreateTime = newDT, ModifyPerson = data["userData"]["UserName"], ModifyTime = newDT }; WMS_BN_BillDetail bDetailModel = new WMS_BN_BillDetail() { BillCode = documentNo, IsEnable = 1, InventoryCode = data["detailData"]["InventoryCode"], InventoryName = data["detailData"]["InventoryName"], Specs = data["detailData"]["Specs"], Unit = data["detailData"]["Unit"], MateNum = data["detailData"]["MateNum"] == null ? null : Convert.ToDouble(data["detailData"]["MateNum"]), ConfirmNum = data["detailData"]["ConfirmNum"] == null ? null : Convert.ToDouble(data["detailData"]["ConfirmNum"]), CreatePerson = data["userData"]["UserName"], CreateTime = newDT, ModifyPerson = data["userData"]["UserName"], ModifyTime = newDT, PBillCode = data["detailData"]["PBillCode"] }; db.UseTransaction(true); int tempA = db.Sql(GetInsertSQL(bMainModel)).Execute(); var realStocks = db.Sql(string.Format(@"SELECT * FROM dbo.WMS_BN_RealStock WHERE IsEnable = 1 AND WarehouseCode = '{0}' AND InventoryCode = '{1}' ORDER BY BatchCode", bMainModel.WarehouseCode, bDetailModel.InventoryCode)).QueryMany <WMS_BN_RealStock>(); WMS_BN_RealStock realStock = null; if (realStocks.Count <= 0) { throw new Exception(@"仓库没有物料!"); } else { var num = Convert.ToInt32(bDetailModel.ConfirmNum ?? 1); foreach (var item in realStocks) { if (item.RealStock == null) { throw new Exception(@"仓库物料数量异常!"); } else { if ((item.RealStock ?? 0) < num) { } else { realStock = item; bDetailModel.BatchCode = item.BatchCode; break; } } } if (string.IsNullOrEmpty(bDetailModel.BatchCode)) { throw new Exception(@"仓库物料数量不够,无法出库!"); } } int tempB = db.Sql(GetInsertSQL(bDetailModel)).Execute(); string sql = GetUpdateSQL(nameof(WMS_BN_RealStock), new KeyValuePair <string, object>("ID", realStock.ID), new { RealStock = realStock.RealStock - bDetailModel.ConfirmNum, TotalStock = realStock.TotalStock - bDetailModel.ConfirmNum, ModifyPerson = bDetailModel.ModifyPerson, ModifyTime = bDetailModel.ModifyTime, }); int tempC = db.Sql(sql).Execute(); bool result = tempA > 0 && tempB > 0 && tempC > 0; if (result) { db.Commit(); } else { db.Rollback(); } return(new ResultModel() { Result = result, Msg = result ? @"成功!" : "失败!" }); } catch (Exception ex) { db.Rollback(); return(new ResultModel() { Result = false, Msg = ex.Message }); } }
public dynamic NewAuditBillCode(string billCode) { try { if (string.IsNullOrEmpty(billCode)) { throw new Exception(@"参数错误!"); } string sqlA = string.Format(@"SELECT * FROM dbo.MES_MaterialReturnMain WHERE IsEnable = 1 AND BillCode = '{0}';", billCode); List <MES_MaterialReturnMain> listA = db.Sql(sqlA).QueryMany <MES_MaterialReturnMain>(); DateTime newDT = DateTime.Now; if (listA.Count <= 0) { throw new Exception(@"请保存数据后审核!"); } if (listA[0].BillState != null && listA[0].BillState.Equals(2)) { throw new Exception(@"单据已审核!"); } int billType = 0; using (var dbA = Db.Context("Sys")) { var sysCodeModel = dbA.Sql(@"SELECT * FROM HBHC_Sys.dbo.sys_code WHERE Text='退料入库';").QuerySingle <sys_code>(); if (sysCodeModel == null) { throw new Exception(@"字典表查不到【退料入库】数据信息!"); } else { billType = Convert.ToInt32(sysCodeModel.Value); } } using (var dbB = Db.Context("Mms")) { try { dbB.UseTransaction(true); string sqlB = string.Format(@"UPDATE dbo.MES_MaterialReturnMain SET BillState = 2 WHERE IsEnable = 1 AND BillCode = '{0}';", billCode); int tempA = dbB.Sql(sqlB).Execute(); var dno = MmsHelper.GetOrderNumber("WMS_BN_BillMain", "BillCode", "TLRK", "", ""); string sqlC = WinFormClientService.GetInsertSQL(new WMS_BN_BillMain() { ID = dbB.Sql("SELECT ISNULL(MAX(ID),1)+1 FROM dbo.WMS_BN_BillMain;").QuerySingle <int>(), BillCode = dno, BillType = billType, ContractCode = listA[0].ContractCode, ProjectName = listA[0].ProjectName, DepartmentID = listA[0].DepartmentID == null ? null : listA[0].DepartmentID.ToString(), DepartmentName = listA[0].DepartmentName, WarehouseCode = listA[0].WarehouseCode, WarehouseName = listA[0].WarehouseName, ApproveState = "2", Remark = "", CreatePerson = MmsHelper.GetUserName(), CreateTime = newDT, ModifyPerson = MmsHelper.GetUserName(), ModifyTime = newDT, IsEnable = 1 }); int tempB = dbB.Sql(sqlC).Execute(); var listB = dbB.Sql(string.Format(@"SELECT * FROM dbo.MES_MaterialReturnDetail WHERE IsEnable = 1 AND MainID = {0};", listA[0].ID)).QueryMany <MES_MaterialReturnDetail>(); StringBuilder sb = new StringBuilder(); foreach (var item in listB) { WMS_BN_BillDetail bDetailModel = new WMS_BN_BillDetail() { BillCode = dno, InventoryCode = item.InventoryCode, InventoryName = item.InventoryName, Specs = item.Model, Unit = item.Unit, MateNum = item.ReturnQuantity, ConfirmNum = item.ReturnQuantity, PBillCode = billCode, Remark = "", CreatePerson = MmsHelper.GetUserName(), CreateTime = newDT, ModifyPerson = MmsHelper.GetUserName(), ModifyTime = newDT, IsEnable = 1 }; var realStocks = db.Sql(string.Format(@"SELECT * FROM dbo.WMS_BN_RealStock WHERE IsEnable = 1 AND WarehouseCode = '{0}' AND InventoryCode = '{1}' ORDER BY BatchCode", listA[0].WarehouseCode, item.InventoryCode)).QueryMany <WMS_BN_RealStock>(); WMS_BN_RealStock realStock = null; if (realStocks.Count <= 0) { throw new Exception(@"仓库没有物料!"); } else { var num = Convert.ToInt32(bDetailModel.ConfirmNum); foreach (var i in realStocks) { if (i.RealStock == null) { throw new Exception(@"仓库物料数量异常!"); } else { if ((i.RealStock ?? 0) < num) { } else { realStock = i; bDetailModel.BatchCode = i.BatchCode; break; } } } if (string.IsNullOrEmpty(bDetailModel.BatchCode)) { throw new Exception(@"仓库物料数量不够,无法出库!"); } } sb.Append(WinFormClientService.GetInsertSQL(bDetailModel)); sb.Append(WinFormClientService.GetUpdateSQL(nameof(WMS_BN_RealStock), new KeyValuePair <string, object>("ID", realStock.ID), new { RealStock = realStock.RealStock - bDetailModel.ConfirmNum, TotalStock = realStock.TotalStock - bDetailModel.ConfirmNum, ModifyPerson = bDetailModel.ModifyPerson, ModifyTime = bDetailModel.ModifyTime, })); } string sqlD = sb.ToString(); int tempC = dbB.Sql(sqlD).Execute(); bool result = tempA > 0 && tempB > 0 && tempC > 0; if (result) { dbB.Commit(); } else { dbB.Rollback(); } return(new ResultModel() { Result = result, Msg = result ? @"审核成功!" : "审核失败!" }); } catch (Exception ex) { dbB.Rollback(); throw new Exception(ex.Message); } } } catch (Exception ex) { return(new ResultModel() { Result = false, Msg = ex.Message }); } }