库存查询返回实现二开字段返回赋值的实现方案

栏目：云星空知识作者：金蝶来源：金蝶云社区发布：2024-09-16浏览：1

一、【业务需求】

1、即时库存明细添加自定义字段，将单据中的字段保存到即时库存表

2、库存查询按钮-》点击事件-》从即时库存中返回自定义的数据到当前单据上

二、【功能分析】

单据审核即时库存更新，核心是即时库存维度匹配即时库存表字段的过程，相当于在SQL中Update ... Where ...

二开服务插件，在AfterExecuteOperationTransaction(AfterExecuteOperationTransaction e)事件中取得单据中库存维度字段，匹配即时库存行，更新二开字段

库存查询返回，依赖于库存查询返回操作服务配置中字段映射关系进行赋值。库存查询返回操作服务无法扩展二开字段的映射。库存查询，是一种表单操作，其配置界面以及功能实现，都是代码写死的，没有插件干预的地方

关于即时库存二开字段返回填写到单的问题，可考虑变通的二开方案，具体思路参考：

1、单据体上新增个文本字段并配置到服务上下文字段，字段把物理字段名清空，设置为虚拟字段，这样不会保存到数据库，默认锁定且不可见

2、库存查询返回更新某一行数据前会先把这个字段赋值为QueryStockUpdate（this.View.Model.SetValue("服务上下文字段标识", "QueryStockUpdate", rowIndex);），选中即时库存数据返回时，服务插件会根据字段的映射配置对表单字段进行赋值，本行返回值回填完毕再清空 this.View.Model.SetValue("服务上下文字段标识", "", rowIndex)，根据这样的处理机制，发现是在做库存查询返回更新的话，二开表单插件，在DataChanged事件中在清空服务上下文字段触发时（e.OldValue == "QueryStockUpdate" && e.NewValue == ""），获取到单据行上各库存维度数据，在即时库存表中匹配对应即时库存行，从而拿到扩展字段的值，进行回填

即时库存明细二开字段"入库类型", 新增其他出库单查询库存返回时，将对应即时库存记录中的"入库类型"回填赋值到单据的"入库类型"字段中

using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core.Bill.PlugIn;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.Interaction;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Orm;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Util;
using Kingdee.K3.Core.SCM;
using System;

namespace Demo
{
    public class MisDeliveryEditEx : AbstractBillPlugIn
    {
        public override void DataChanged(BOS.Core.DynamicForm.PlugIn.Args.DataChangedEventArgs e)
        {
            switch (e.Field.Key)
            {
                case "FServiceContext":
                    if(e.OldValue != null)
                    {
                        if (e.OldValue.ToString() == "QueryStockUpdate" && e.NewValue.ToString() == "")
                        {
                             var entry = this.View.BusinessInfo.GetEntity("FEntity");
                            var entryRow = this.Model.GetEntityDataObject(entry, e.Row);
                            var materilId = Convert.ToInt64(entryRow["MaterialId_Id"]);
                            var stockId = Convert.ToInt64(entryRow["StockId_Id"]);
                            var lotText = Convert.ToString(entryRow["Lot_Text"]);
                            DynamicObject orgObj = this.View.Model.GetValue("FStockOrgId") as DynamicObject;
                            DynamicObject lotObj = this.View.Model.GetValue("FLot") as DynamicObject;
                            var orgId = Convert.ToInt64(orgObj["Id"]);

                            // 批号不为空，加入批号匹配
                            if (!string.IsNullOrEmpty(lotText))
                            {
                                string sqlText = string.Format(@"SELECT T1.FMATERIALID, T1.FSTOCKID,T1.FLOT, T1.FTEXT1 FROM T_STK_INVENTORY T1 JOIN T_BD_LOTMASTER Lot ON T1.FLOT = Lot.FLOTID
WHERE T1.FMATERIALID = {0} AND T1.FSTOCKORGID = {1} AND FSTOCKID = {2} AND Lot.FNUMBER = '{3}'", materilId, orgId, stockId, lotText);
                                var dyObjs = DBServiceHelper.ExecuteDynamicObject(this.Context, sqlText);
                                if (dyObjs.Count > 0)
                                {
                                    string result = Convert.ToString(dyObjs[0]["FTEXT1"]);
                                    this.View.Model.SetValue("FText1", result, e.Row);  // 单据扩展的"入库类型"字段
                                }
                            }
                       else
                            {
                                string sqlText = string.Format(@"SELECT T1.FMATERIALID, T1.FSTOCKID,T1.FLOT, T1.FTEXT1 FROM T_STK_INVENTORY T1 WHERE T1.FMATERIALID = {0} AND T1.FSTOCKORGID = {1} AND FSTOCKID = {2}", materilId, orgId, stockId);
                                var dyObjs = DBServiceHelper.ExecuteDynamicObject(this.Context, sqlText);
                                if (dyObjs.Count > 0)
                                {
                                    string result = Convert.ToString(dyObjs[0]["FTEXT1"]);
                                    this.View.Model.SetValue("FText1", result, e.Row); // 单据扩展的"入库类型"字段
                                }
                            }
                        }
                    }

                    break;
            }
        }
    }
}

import clr
clr.AddReference("System")
clr.AddReference("System.Core")
clr.AddReference("Kingdee.BOS")
clr.AddReference("Kingdee.BOS.Core")
clr.AddReference('Kingdee.BOS.ServiceHelper')

from System import *
from Kingdee.BOS.ServiceHelper import * 
from System import StringComparison

def DataChanged(e):
    if e.Field.Key.Equals("FServiceContext", StringComparison.OrdinalIgnoreCase):
        if e.OldValue == None:
            pass
        if Convert.ToString(e.OldValue) == "QueryStockUpdate" and Convert.ToString(e.NewValue) == "":
            entry = this.View.BusinessInfo.GetEntity("FEntity");
            entryRow = this.Model.GetEntityDataObject(entry, e.Row)
            materilId = Convert.ToInt64(entryRow["MaterialId_Id"])
            stockId = Convert.ToInt64(entryRow["StockId_Id"])
            lotText = Convert.ToString(entryRow["Lot_Text"])
            orgObj = this.View.Model.GetValue("FStockOrgId")
            orgId = Convert.ToInt64(orgObj["Id"])
            
            if lotText != "": # 批号不为空，加入批号匹配
                sqlText = '''SELECT T1.FMATERIALID, T1.FSTOCKID,T1.FLOT, T1.FTEXT1 FROM T_STK_INVENTORY T1 JOIN T_BD_LOTMASTER Lot ON T1.FLOT = Lot.FLOTID
WHERE T1.FMATERIALID = {0} AND T1.FSTOCKORGID = {1} AND FSTOCKID = {2} AND Lot.FNUMBER = '{3}' '''.format(str(materilId), str(orgId), str(stockId), str(lotText))
                dyObjs = DBServiceHelper.ExecuteDynamicObject(this.Context, sqlText)
                if dyObjs.Count > 0:
                    this.View.Model.SetValue("FText1", Convert.ToString(dyObjs[0]["FTEXT1"]), e.Row)  # 单据扩展的"入库类型"字段
            else:
                sqlText2 = '''SELECT T1.FMATERIALID, T1.FSTOCKID,T1.FLOT, T1.FTEXT1 FROM T_STK_INVENTORY T1 WHERE T1.FMATERIALID = {0} AND T1.FSTOCKORGID = {1} AND FSTOCKID = {2}'''.format(str(materilId), str(orgId), str(stockId))
                dyObjs2 = DBServiceHelper.ExecuteDynamicObject(this.Context, sqlText2)
                if dyObjs2.Count > 0:
                    this.View.Model.SetValue("FText1", Convert.ToString(dyObjs2[0]["FTEXT1"]), e.Row) # 单据扩展的"入库类型"字段

无批号物料：