金蝶云星空锁库时同时锁定序列号
金蝶云星空锁库时同时锁定序列号
## 业务背景
公司业务要求，如果检查发现序列号有问题，先锁库不允许出库。
## 系统现状
即时库存锁库，锁定的是数量，库存-锁库数=可用数，当可用量小于等于0就不可以再出库了。
如果想要控制锁到序列号，系统就不支持了。
## 方案设计
锁库时同时锁定库存和根据输入的序列号锁定序列号
## 详细设计
序列号主档增加复选框-锁定
### 即时库存锁库扩展
添加页签，页签里添加单据体-序列号，增加基础资料控件，绑定《序列号主档》
菜单项，添加按钮，按序列号锁库
创建表单插件
新建类LockOperateBySerial
继承继承AbstractDynamicFormPlugIn
重写BeforeSetItemValueByNumber，设置序列号主档，可以选到未审核的资料
输入序列号前，判断当前锁库行只有一行，且物料资料正常，仓库正常，因为需要即时库存内码+物料+仓库+仓位过滤序列号主档数据
public override void BeforeF7Select(BeforeF7SelectEventArgs e) { base.BeforeF7Select(e); switch (e.FieldKey.ToUpperInvariant()) { case "F_XXXX_SERIALID": string DelFilter = ""; Entity subEntity = View.BillBusinessInfo.GetEntity("FEntity"); var getEntity = View.Model.GetEntityDataObject(subEntity); if (getEntity == null || getEntity.Count <= 0 || getEntity.Count > 1) { this.View.ShowErrMessage("只能处理一行物料"); return; } var em = getEntity.FirstOrDefault(); //物料不为空 var mater = em["MaterialId"] as DynamicObject; if (mater == null) { this.View.ShowErrMessage("物料为空"); return; } long materialId = Convert.ToInt64(mater["Id"]); //仓库不为空 var stock = em["StockId"] as DynamicObject; if (stock == null) { this.View.ShowErrMessage("仓库为空"); return; } long stockId = Convert.ToInt64(stock["Id"]); DelFilter = string.Format(@" t0.FMATERIALID ={0} and t2.FSTOCKID={1} ", materialId, stockId); //仓位不为空 var stockLoc = em["StockLocId"] as DynamicObject; if (stockLoc != null) { long stockLocId = Convert.ToInt64(stockLoc["Id"]); DelFilter += string.Format(@" and t2.FSTOCKLOCID={0}", stockLocId); } // 未审核的基础资料也显示出来 e.IsShowApproved = false; // 已禁用的基础资料也显示出来 //e.IsShowUsed = false; break; } }
重写按钮点击事件，校验序列号的个数=锁库数，序列号在当前即时库存里，且锁定状态为否，通过校验则调用锁库方法，同时对这些序列号进行锁定。
创建存储过程，获取即时库存下的未锁定的序列号XXXX_PR_STK_SerialInfoByInvId
/**根据即时库存内码查询在库的序列号 @IsLock=1锁定 @IsLock=0未锁定 @IsLock=-1返回在库存的所有 Krystal 2024-08-08 09:01:00 **/ CREATE PROCEDURE [dbo].[XXXX_PR_STK_SerialInfoByInvId] @InvId VARCHAR(100), @IsLock INT AS BEGIN DECLARE @LockSql VARCHAR(100) IF(@IsLock=1 OR @IsLock=0) SET @LockSql=' AND sm.F_XXXX_ISLOCK= '+CAST(@IsLock AS CHAR(1) ) ELSE SET @LockSql=' ' --最后输出到临时表 DECLARE @cmdtext VARCHAR(MAX); BEGIN SET @cmdtext='SELECT ''BD_SerialMainFile'' FFORMID , SM.FSERIALID, INV.FID , SM.FNUMBER FSERIALNO , ORGL.FNAME FSTOCKORGNAME , MA.FMaterialId FMATERIALID , MA.FNUMBER FMATERIALNUMBER , MAL.FNAME FMATERIALNAME , MAL.FSpecification FMATERIALMODEL , INV.FAuxPropID FAUXPROPCOMID , '' '' FAUXPROP , BDSTL.FNAME FSTOCKNAME , ROW_NUMBER() OVER( ORDER BY INV.FID ASC, SM.FNUMBER ASC) FIDENTITYID FROM T_STK_INVENTORY INV INNER JOIN T_BD_SERIALBILLTRACE TRA ON INV.FID = TRA.FINVID INNER JOIN ( SELECT MAX(T3.FBILLTRACEID) fbilltraceid FROM T_BD_SERIALBILLTRACE T3 INNER JOIN ( SELECT DISTINCT FSERIALID FROM T_BD_SERIALBILLTRACE WHERE FINVID ='''+@InvId+''' ) t4 ON T4.FSERIALID = T3.FSERIALID WHERE (T3.FINVID <> '' '') GROUP BY T3.FSERIALID ) tra2 ON TRA2.FBILLTRACEID = TRA.FBILLTRACEID INNER JOIN T_BD_SERIALMASTER SM ON SM.FSERIALID = TRA.FSERIALID LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L ORGL ON (ORGL.FORGID = INV.FSTOCKORGID AND ORGL.FLOCALEID = 2052) INNER JOIN T_BD_MATERIAL MA ON (MA.FMASTERID = INV.FMATERIALID AND MA.FUSEORGID = INV.FSTOCKORGID) LEFT OUTER JOIN T_BD_MATERIAL_L MAL ON (MAL.FMATERIALID = MA.FMATERIALID AND MAL.FLOCALEID = 2052) LEFT OUTER JOIN T_BD_STOCK_L BDSTL ON (BDSTL.FSTOCKID = INV.FSTOCKID AND BDSTL.FLOCALEID = 2052) WHERE (SM.FFORBIDSTATUS = ''A'' AND (INV.FID ='''+@InvId+''' AND TRA.FSTATE = ''1'') ' + @LockSql + ' ) ORDER BY INV.FID ASC, SM.FNUMBER ASC' END; EXEC(@cmdtext); END GO
完整代码
#region << 版 本 注 释 >> /*---------------------------------------------------------------- * 版权所有 (c) 2024 NJRN 保留所有权利。 * CLR版本：4.0.30319.42000 * 机器名称：INC1507245 * 公司名称：Increase * 命名空间：Krystal.K3.SCM.Business.PlugIn.STK.DynamicForm * 唯一标识：fe1b3de0-b8c8-41a9-aba6-535ccaa3c90e * 文件名：LockOperateBySerial * 当前用户域：INC1507245 * * 创建者：Krystal * 电子邮箱：543375940@qq.com * 创建时间：2024/8/8 17:45:31 * 版本：V1.0.0 * 描述： * * ---------------------------------------------------------------- * 修改人： * 时间： * 修改说明： * * 版本：V1.0.1 *----------------------------------------------------------------*/ #endregion << 版 本 注 释 >> using Kingdee.BOS; using Kingdee.BOS.App.Data; using Kingdee.BOS.Core; using Kingdee.BOS.Core.DynamicForm.PlugIn; using Kingdee.BOS.Core.DynamicForm.PlugIn.Args; using Kingdee.BOS.Core.Metadata.EntityElement; using Kingdee.BOS.Orm.DataEntity; using Kingdee.BOS.Resource; using Kingdee.BOS.Util; using Kingdee.K3.Core.SCM.STK; using Kingdee.K3.SCM.ServiceHelper; using Krystal.K3Cloud.Core.Const; using Krystal.K3Cloud.Core.Model.STK; using Krystal.K3Cloud.Core.Util; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Linq; using System.Text; using System.Transactions; namespace Krystal.K3.SCM.Business.PlugIn.STK.DynamicForm { /// <summary> /// 功能描述 ：即时库存锁库操作插件--二开 /// 创 建 者 ：Administrator /// 创建日期 ：2024/8/8 17:45:31 /// 最后修改者 ：Krystal /// 最后修改日期：2024/8/8 17:45:31 /// </summary> [Description("即时库存锁库操作插件--二开"), HotUpdate] public class LockOperateBySerial: AbstractDynamicFormPlugIn { #region <常量> /// <summary> /// 操作类型:Inv-即时库存 /// </summary> private string opType = "Inv"; #endregion <常量> #region <事件> public override void BeforeSetItemValueByNumber(BeforeSetItemValueByNumberArgs e) { base.BeforeSetItemValueByNumber(e); switch (e.BaseDataField.Key.ToUpperInvariant()) { case "F_XXXX_SERIALID": // 未审核的基础资料也显示出来 e.IsShowApproved = false; break; } } /// <summary> /// 输入序列号前，判断当前锁库行只有一行，且物料资料正常，仓库正常，因为需要即时库存内码+物料+仓库+仓位过滤序列号主档数据 /// </summary> /// <param name="e"></param> public override void BeforeF7Select(BeforeF7SelectEventArgs e) { base.BeforeF7Select(e); switch (e.FieldKey.ToUpperInvariant()) { case "F_XXXX_SERIALID": string DelFilter = ""; Entity subEntity = View.BillBusinessInfo.GetEntity("FEntity"); var getEntity = View.Model.GetEntityDataObject(subEntity); if (getEntity == null || getEntity.Count <= 0 || getEntity.Count > 1) { this.View.ShowErrMessage("只能处理一行物料"); return; } var em = getEntity.FirstOrDefault(); //物料不为空 var mater = em["MaterialId"] as DynamicObject; if (mater == null) { this.View.ShowErrMessage("物料为空"); return; } long materialId = Convert.ToInt64(mater["Id"]); //仓库不为空 var stock = em["StockId"] as DynamicObject; if (stock == null) { this.View.ShowErrMessage("仓库为空"); return; } long stockId = Convert.ToInt64(stock["Id"]); DelFilter = string.Format(@" t0.FMATERIALID ={0} and t2.FSTOCKID={1} ", materialId, stockId); //仓位不为空 var stockLoc = em["StockLocId"] as DynamicObject; if (stockLoc != null) { long stockLocId = Convert.ToInt64(stockLoc["Id"]); DelFilter += string.Format(@" and t2.FSTOCKLOCID={0}", stockLocId); } // 未审核的基础资料也显示出来 e.IsShowApproved = false; // 已禁用的基础资料也显示出来 //e.IsShowUsed = false; break; } } public override void BarItemClick(BarItemClickEventArgs e) { base.BarItemClick(e); switch (e.BarItemKey.ToUpperInvariant()) { case "XXXX_TBLOCKSERIAL": string text; if ((text = opType) == null || !(text == "Inv")) { return; } Entity subEntity = View.BillBusinessInfo.GetEntity("FEntity"); var getEntity = View.Model.GetEntityDataObject(subEntity); if (getEntity == null || getEntity.Count <= 0 || getEntity.Count > 1) { this.View.ShowErrMessage("只能处理一行物料"); return; } var em = getEntity.FirstOrDefault(); //物料不为空 var mater = em["MaterialId"] as DynamicObject; if (mater == null) { this.View.ShowErrMessage("物料为空"); return; } //锁库数>0 decimal lockQty = Convert.ToDecimal(em["LockQty"]); if (lockQty <= 0) { this.View.ShowErrMessage("锁库数必须大于0"); return; } //序列号获取 SaveLockBySerial(em); break; } } /// <summary> /// 校验 /// 锁库 /// 锁序列号 /// </summary> /// <param name="obj"></param> private void SaveLockBySerial(DynamicObject obj) { List<LockStockArgs> argsList = new List<LockStockArgs>(); if (CheckSubData(obj)) { string invId = obj["InvDetailID"] + ""; Entity serialEntity = View.BillBusinessInfo.GetEntity("F_XXXX_SerialEntity"); List<DynamicObject> subEntityList = (from p in View.Model.GetEntityDataObject(serialEntity) where p["F_XXXX_SerialId"] != null select p).ToList(); if (subEntityList == null || subEntityList.Count <= 0) { this.View.ShowErrMessage("序列号必须录入。"); return; } //序列号个数=锁库数 decimal serialCount = subEntityList.Count; decimal lockQty = Convert.ToDecimal(obj["LockQty"]); //C#判断锁库数是否为整数 if (serialCount != lockQty) { this.View.ShowErrMessage("序列号个数必须与锁库数一致。"); return; } if (CheckSerial(subEntityList, invId)) { argsList.Add(GetSubData(obj)); if (argsList.Count > 0) { using (KDTransactionScope scope = new KDTransactionScope(TransactionScopeOption.RequiresNew)) { List<string> updateSql = new List<string>(); var lockInfo = StockServiceHelper.SaveLockInfo(base.Context, argsList); foreach (var ss in subEntityList) { updateSql.Add(string.Format(@"{0}UPDATE T_BD_SERIALMASTER SET F_XXXX_ISLOCK=1 WHERE FSERIALID={1};", OtherConst.DIALECT, ss["F_XXXX_SerialId_Id"])); } if (updateSql != null && updateSql.Count > 0) { int updateCount = DBUtils.ExecuteBatch(this.Context, updateSql, updateSql.Count); } scope.Complete(); } View.ShowMessage(ResManager.LoadKDString("锁库成功", "004023030002176", SubSystemType.SCM)); View.Refresh(); View.ReturnToParentWindow(true); } } } } /// <summary> /// 校验锁库信息数据 /// </summary> /// <param name="obj"></param> /// <returns></returns> private bool CheckSubData(DynamicObject obj) { bool isSucceed = true; int seq = Convert.ToInt32(obj["Seq"]); decimal lockQty = Convert.ToDecimal(obj["LockQty"]); decimal validQty = Convert.ToDecimal(obj["ValidQty"]); DynamicObject secUnitID = (DynamicObject)obj["SecUnitId"]; decimal secLockQty = Convert.ToDecimal(obj["SecLockQty"]); decimal secValidQty = Convert.ToDecimal(obj["SecValidQty"]); DynamicObject subMaterial = (DynamicObject)obj["MaterialId"]; StringBuilder errMes = new StringBuilder(); if (validQty <= 0m) { errMes.AppendLine(string.Format(ResManager.LoadKDString("第{0}行物料【{1}】的可锁数量≤0", "004023030000289", SubSystemType.SCM), seq, subMaterial["Name"])); isSucceed = false; } if (lockQty > validQty) { errMes.AppendLine(string.Format(ResManager.LoadKDString("第{0}行物料【{1}】的锁库量＞可锁量", "004023030000292", SubSystemType.SCM), seq, subMaterial["Name"])); isSucceed = false; } if (secUnitID != null && secLockQty > 0m && secLockQty > secValidQty) { errMes.AppendLine(string.Format(ResManager.LoadKDString("第{0}行物料【{1}】的锁库量(辅助)大于可锁量(辅助)", "004023030000298", SubSystemType.SCM), seq, subMaterial["Name"])); isSucceed = false; } if (!isSucceed) { View.ShowErrMessage(errMes.ToString()); } return isSucceed; } /// <summary> /// 校验序列号是否在库，且未锁定 /// </summary> /// <param name="objList"></param> /// <param name="invId"></param> /// <returns></returns> private bool CheckSerial(List<DynamicObject> objList, string invId) { bool isSucceed = true; //序列号物料编码，在库状态 List<SqlParam> para = new List<SqlParam>() { //new SqlParam("@STOCKID", KDDbType.Int32,493513), new SqlParam("@InvId", KDDbType.String,invId), new SqlParam("@IsLock", KDDbType.Int32,0), //new SqlParam("@ENDTIME",KDDbType.String,_filterArgs.EndTime.ToString("yyyy-MM-dd")), //new SqlParam("@t",KDDbType.String,temp_detail) }; //执行sql语句并将结果集填充至DataSet XHWT_PR_STK_SerialInfoByInvId未使用 DataSet getSerial = DBUtils.ExecuteDataSet(this.Context, System.Data.CommandType.StoredProcedure , string.Format(@"{0}XXXX_PR_STK_SerialInfoByInvId", OtherConst.DIALECT), para); //调用上面的将DataSet转换为List<Menu>实体集合 List<SerialMainFile> serialListInStock = (List<SerialMainFile>)KrystalCommonUtil .DataSetToList<SerialMainFile>(getSerial, 0); if (serialListInStock == null || serialListInStock.Count <= 0) { isSucceed = false; } //LINQ两个集合关联，左集合在右边找不到，则为空，找出为空的数据提示出来 //使用查询语句 var list = from left in objList join right in serialListInStock on left["F_XHWT_SerialId_Id"] + "" equals right.FSERIALID + "" into temp select new { serialId = left["F_XHWT_SerialId_Id"] + "", serialNumber = (left["F_XHWT_SerialId"] as DynamicObject)["Number"] + "", serialNo = temp.Select(t => t.FSERIALNO).FirstOrDefault() }; var isEmpty = list.Where(s => s.serialNo.IsNullOrEmptyOrWhiteSpace()).ToList(); if (isEmpty != null && isEmpty.Count > 0) { isSucceed = false; List<string> empNumber = isEmpty.Select(s => s.serialNumber).ToList(); this.View.ShowErrMessage(string.Format(@"序列号不在当前库存里，可以查询序列号报表。在库则可能已经锁定【{0}】", string.Join(",", empNumber))); return isSucceed; } return isSucceed; } /// <summary> /// 获取锁库信息数据（保存数据需要数据） /// </summary> /// <param name="obj"></param> /// <returns></returns> private LockStockArgs GetSubData(DynamicObject materialObj) { LockStockArgs stockArg = new LockStockArgs(); stockArg.ObjectId = "STK_Inventory"; stockArg.BillId = materialObj["InvDetailID"].ToString(); stockArg.BillDetailID = ""; stockArg.FInvDetailID = materialObj["InvDetailID"].ToString(); stockArg.StockOrgID = Convert.ToInt64(((DynamicObject)materialObj["StockOrgId"])["Id"]); stockArg.DemandOrgId = stockArg.StockOrgID; stockArg.MaterialID = GetDynamicValue(materialObj["MaterialId"] as DynamicObject); stockArg.DemandMaterialId = stockArg.MaterialID; stockArg.DemandDateTime = null; stockArg.DemandPriority = ""; object planMode = ((DynamicObjectCollection)(materialObj["MaterialID"] as DynamicObject)["MaterialPlan"])[0]["PlanMode"]; if (planMode != null && planMode.ToString() == "1") { stockArg.IsMto = "1"; } stockArg.BOMID = GetDynamicValue(materialObj["BomId"] as DynamicObject); stockArg.AuxPropId = GetDynamicValue(materialObj["AuxPropId"] as DynamicObject); if (materialObj["Lot"] is DynamicObject dyLot && Convert.ToInt64(dyLot["Id"]) > 0) { stockArg.Lot = GetDynamicValue(dyLot); stockArg.LotNo = dyLot["Number"].ToString(); } stockArg.MtoNo = materialObj["MtoNo"].ToString(); stockArg.ProjectNo = materialObj["ProjectNo"].ToString(); if (materialObj["ProduceDate"] != null) { stockArg.ProduceDate = DateTime.Parse(materialObj["ProduceDate"].ToString()); } if (materialObj["ExpiryDate"] != null) { stockArg.ExpiryDate = DateTime.Parse(materialObj["ExpiryDate"].ToString()); } stockArg.STOCKID = GetDynamicValue(materialObj["StockId"] as DynamicObject); stockArg.StockLocID = GetDynamicValue(materialObj["StockLocId"] as DynamicObject); stockArg.StockStatusID = GetDynamicValue(materialObj["StockStatusId"] as DynamicObject); stockArg.OwnerTypeID = materialObj["OwnerTypeId"].ToString(); stockArg.OwnerID = GetDynamicValue(materialObj["OwnerId"] as DynamicObject); stockArg.KeeperTypeID = materialObj["KeeperTypeId"].ToString(); stockArg.KeeperID = GetDynamicValue(materialObj["KeeperId"] as DynamicObject); stockArg.UnitID = GetDynamicValue(materialObj["UnitID"] as DynamicObject); stockArg.BaseUnitID = GetDynamicValue(materialObj["BaseUnitId"] as DynamicObject); stockArg.SecUnitID = GetDynamicValue(materialObj["SecUnitId"] as DynamicObject); stockArg.LockQty = decimal.Parse(materialObj["LockQty"].ToString()); stockArg.LockBaseQty = decimal.Parse(materialObj["BaseLockQty"].ToString()); stockArg.LockSecQty = decimal.Parse(materialObj["SecLockQty"].ToString()); object oDate = materialObj["ReserveDate"]; if (oDate != null && !string.IsNullOrWhiteSpace(oDate.ToString())) { stockArg.ReserveDate = DateTime.Parse(oDate.ToString()); } stockArg.ReserveDays = Convert.ToInt32(materialObj["ReserveDays"]); oDate = materialObj["ReleaseDate"]; if (oDate != null && !string.IsNullOrWhiteSpace(oDate.ToString())) { stockArg.ReLeaseDate = DateTime.Parse(oDate.ToString()); } stockArg.SupplyNote = Convert.ToString(materialObj["SupplyNote"]); stockArg.RequestNote = stockArg.SupplyNote; return stockArg; } /// <summary> /// 获得动态对象的值，优先返回MasterId /// </summary> /// <param name="obj"></param> /// <returns></returns> private long GetDynamicValue(DynamicObject obj) { if (obj == null) { return 0L; } if (obj.DynamicObjectType.Properties.ContainsKey(FormConst.MASTER_ID)) { return Convert.ToInt64(obj[FormConst.MASTER_ID]); } if (obj.DynamicObjectType.Properties.ContainsKey("Id")) { return Convert.ToInt64(obj["Id"]); } return 0L; } #endregion <事件> } }
配置表单插件
登录业务站点测试
