二开案例.表达式函数.获取分组全名
【应用场景】获取分组的全路径名称。
【案例演示】物料列表,新增文本字段,获取物料分组的全路径名称。
因涉及到存储过程,本案例仅支持SQL Server账套。
【实现步骤】
<1>编写存储过程实现根据分组内码获取分组全名。
/*
--获取分组全名
CREATE PROCEDURE [P_GetGroupFullName]
(
@TableName NVARCHAR(255),
@Id INT,
@SplitChar NVARCHAR(10)='.',
@LocaleId INT=2052
)
AS
BEGIN
DECLARE @LoopMaxTimes INT=20
DECLARE @TreeNodeFullName NVARCHAR(4000)=''
DECLARE @NodeName NVARCHAR(255)=''
DECLARE @ParentId INT=0
DECLARE @Sql NVARCHAR(4000)=N'
SELECT @ParentId=t.FPARENTID,@NodeName=ISNULL(l.FNAME,'''') FROM '+@TableName+' t
LEFT JOIN '+@TableName+'_L l ON t.FID=l.FID AND l.FLOCALEID='+CAST(@LocaleId AS NVARCHAR(20))+'
WHERE t.FID='+CAST(@Id AS NVARCHAR(20))
DECLARE @Parameters NVARCHAR(255)=N'@ParentId INT OUTPUT,@NodeName NVARCHAR(255) OUTPUT'
PRINT @Sql
EXEC sp_executesql @Sql,@Parameters,@ParentId=@ParentId OUTPUT,@NodeName=@NodeName OUTPUT
WHILE @@ROWCOUNT>0 AND @LoopMaxTimes>0
BEGIN
IF @TreeNodeFullName=''
SET @TreeNodeFullName=@NodeName
ELSE
SET @TreeNodeFullName=@NodeName+@SplitChar+@TreeNodeFullName
IF @ParentId=0 BREAK
SET @LoopMaxTimes=@LoopMaxTimes-1
SET @Sql=N'
SELECT @ParentId=t.FPARENTID,@NodeName=ISNULL(l.FNAME,'''') FROM '+@TableName+' t
LEFT JOIN '+@TableName+'_L l ON t.FID=l.FID AND l.FLOCALEID='+CAST(@LocaleId AS NVARCHAR(20))+'
WHERE t.FID='+CAST(@ParentId AS NVARCHAR(20))
PRINT @Sql
EXEC sp_executesql @Sql,@Parameters,@ParentId=@ParentId OUTPUT,@NodeName=@NodeName OUTPUT
END
SELECT @TreeNodeFullName
END
*/
<2>编写函数,代码如下。
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.Metadata.Expression.FuncDefine;
using System;
namespace Jac.XkDemo.BOS.Core.Functions
{
/// <summary>
/// 【函数】获取分组全名
/// </summary>
[Serializable]
public class GetGroupFullNameFuncDefine : AbstractFuncDefine
{
public GetGroupFullNameFuncDefine()
{
}
public GetGroupFullNameFuncDefine(Context ctx, dynamic obj)
{
m_ctx = ctx;
m_obj = obj;
}
public override IFuncDefine GetFunctionDefine(Context ctx, dynamic obj)
{
return new GetGroupFullNameFuncDefine(ctx, obj);
}
public override object GetFuncDefine()
{
return new Func<string, string, int, string>(FuncDefine);
}
/// <summary>
/// 获取分组全名
/// </summary>
/// <param name="groupTableName">分组表名</param>
/// <param name="splitChar">连接上下级分组名的分隔符</param>
/// <param name="id">分组当前节点内码</param>
/// <returns>返回分组全名</returns>
string FuncDefine(string groupTableName, string splitChar, int id)
{
if (string.IsNullOrWhiteSpace(groupTableName))
{
return "";
}
if (string.IsNullOrEmpty(splitChar))
{
splitChar = ".";
}
var sql = string.Format("EXEC P_GetGroupFullName '{0}','{1}',{2},{3}", groupTableName, id, splitChar, m_ctx.UserLocale.LCID);
var rval = DBUtils.ExecuteScalar(m_ctx, sql, "");
return rval;
}
}
}
<3>注册函数(可参考:https://vip.kingdee.com/article/74091717745675520)。
-- 获取分组全名函数
DELETE T_MDL_ELEMENTTYPE WHERE FID=888010;
INSERT INTO T_MDL_ELEMENTTYPE (FID,FIMGKEY,FTYPEID,FISVISIBLE,FELEMENTCLASS,FAPPEARANCECLASS)
VALUES (888010, '', 1, 0, 'Jac.XkDemo.BOS.Core.Functions.GetGroupFullNameFuncDefine,Jac.XkDemo.BOS.Core', 'BOS_GETGROUPFULLNAME');
DELETE T_MDL_DOMAINMODELELEMENTMAP WHERE FID=1200 and FELEMENTGROUPID=24 and FELEMENTTYPEID=888010;
INSERT INTO T_MDL_DOMAINMODELELEMENTMAP (FID, FENTRYID, FELEMENTTYPEID, FELEMENTGROUPID, FSEQ)
VALUES (1200, 888010, 888010, 24, 81);
DELETE T_MDL_ELEMENTTYPE_L WHERE FID=888010 AND FLOCALEID=2052;
INSERT INTO T_MDL_ELEMENTTYPE_L(FID,FNAME,FDESCRIPTION,FLOCALEID,FPKID)
VALUES (888010,N'获取分组全名',N'获取分组当前节点在分组树中的路径全名。
使用示例:
获取分组全名:GETGROUPFULLNAME(''T_BD_MATERIALGROUP'',''.'',FMaterialGroup)
',2052,(SELECT ISNULL(MAX(FPKID), 0) + 1 FROM T_MDL_ELEMENTTYPE_L WHERE FPKID > 100000));
<4>拷贝插件组件到应用站点的WebSite\Bin目录下,重启IIS。
<5>BOSIDE扩展物料,单据头新增文本字段,物料分组字段新增值更新事件,单据头新增实体服务规则,如下图所示,保存元数据,开发完毕。
注意:实际使用时,使用值更新事件或者实体服务规则两种方案中的任意一种实现即可。
新增文件字段存储分组全名:
物料分组设置值更新事件:
前置条件:FMaterialGroup <> null
计算公式:F_Jac_Text = GETGROUPFULLNAME('T_BD_MATERIALGROUP','>',FMaterialGroup.Id) if FMaterialGroup <> null else ''
单据头配置实体服务规则:
前置条件:FMaterialGroup <> null
为真计算公式:F_Jac_Text = GETGROUPFULLNAME('T_BD_MATERIALGROUP','>',FMaterialGroup.Id)
为假计算公式:F_Jac_Text = ''
现在可以登录业务站点,打开物料列表新增界面,选择物料分组,就能联动获取当前分组的全名啦。
---------------------------------------------------------------------------------------------------------
【参考资料】
分组节点数据可能会因为某些未知的原因,产生死循环数据,这种异常数据一旦出现,可能会把系统搞奔溃掉,以下方法可快速定位到有死循环的分组节点数据。
/*
-- 获取分组全名(另一种实现方案)
CREATE PROCEDURE [P_GetGroupFullName2]
(
@TableName NVARCHAR(255),
@Id INT,
@SplitChar NVARCHAR(10)='.',
@LocaleId INT=2052
)
AS
BEGIN
DECLARE @TreeNodeFullName NVARCHAR(4000)=''
DECLARE @Sql NVARCHAR(4000)=N'
WITH t AS
(
SELECT * FROM '+@TableName+' WHERE FID='+CAST(@Id AS NVARCHAR(20))+'
union all
SELECT x.* FROM '+@TableName+' x JOIN t ON x.FID=t.FPARENTID
)
SELECT IDENTITY(INT,1,1)AS RowId, l.FNAME,t.* INTO #TMPTreeNodes FROM t
LEFT JOIN '+@TableName+'_L l ON t.FID=l.FID AND l.FLOCALEID='+CAST(@LocaleId AS NVARCHAR(20))+'
SELECT @TreeNodeFullName=@TreeNodeFullName+(CASE WHEN @TreeNodeFullName=''''THEN '''' ELSE '''+@SplitChar+''' END)+ISNULL(FNAME,'''')
FROM #TMPTreeNodes ORDER BY RowId DESC'
PRINT @Sql
DECLARE @Parameters NVARCHAR(4000)=N'@TreeNodeFullName NVARCHAR(4000) OUTPUT'
PRINT @Parameters
EXEC sp_executesql @Sql,@Parameters,@TreeNodeFullName=@TreeNodeFullName OUTPUT
SELECT @TreeNodeFullName
END
*/
/*
-- 获取物料分组名称全路径
CREATE FUNCTION F_GetMaterialGroupFullName
(
@Id INT,
@SplitChar NVARCHAR(10)='.',
@LocaleId INT=2052
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @LoopMaxTimes INT=20
DECLARE @TreeNodeFullName NVARCHAR(4000)=''
DECLARE @NodeName NVARCHAR(255)=''
DECLARE @ParentId INT=0
SELECT @ParentId=t.FPARENTID,@NodeName=ISNULL(l.FNAME,'') FROM T_BD_MATERIALGROUP t
LEFT JOIN T_BD_MATERIALGROUP_L l ON t.FID=l.FID AND l.FLOCALEID=@LocaleId
WHERE t.FID=@Id
WHILE @@ROWCOUNT>0 AND @LoopMaxTimes>0
BEGIN
IF @TreeNodeFullName=''
SET @TreeNodeFullName=@NodeName
ELSE
SET @TreeNodeFullName=@NodeName+@SplitChar+@TreeNodeFullName
IF @ParentId=0 BREAK
SET @LoopMaxTimes=@LoopMaxTimes-1
SELECT @ParentId=t.FPARENTID,@NodeName=ISNULL(l.FNAME,'') FROM T_BD_MATERIALGROUP t
LEFT JOIN T_BD_MATERIALGROUP_L l ON t.FID=l.FID AND l.FLOCALEID=@LocaleId
WHERE t.FID=@ParentId
END
IF @LoopMaxTimes<=0
-- 加问号用于识别死循环数据
SET @TreeNodeFullName='???'+@TreeNodeFullName
RETURN @TreeNodeFullName
END
*/
/*
-- 获取物料分组内码全路径
CREATE FUNCTION F_GetMaterialGroupFullId
(
@Id INT,
@SplitChar NVARCHAR(10)='.'
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @LoopMaxTimes INT=20
DECLARE @TreeNodeFullName NVARCHAR(4000)=''
DECLARE @NodeName NVARCHAR(255)=''
DECLARE @ParentId INT=0
SELECT @ParentId=t.FPARENTID,@NodeName=CAST(t.FID AS NVARCHAR(20)) FROM T_BD_MATERIALGROUP t
WHERE t.FID=@Id
WHILE @@ROWCOUNT>0 AND @LoopMaxTimes>0
BEGIN
IF @TreeNodeFullName=''
SET @TreeNodeFullName=@NodeName
ELSE
SET @TreeNodeFullName=@NodeName+@SplitChar+@TreeNodeFullName
IF @ParentId=0 BREAK
SET @LoopMaxTimes=@LoopMaxTimes-1
SELECT @ParentId=t.FPARENTID,@NodeName=CAST(t.FID AS NVARCHAR(20)) FROM T_BD_MATERIALGROUP t
WHERE t.FID=@ParentId
END
IF @LoopMaxTimes<=0
-- 加问号用于识别死循环数据
SET @TreeNodeFullName='???'+@TreeNodeFullName
RETURN @TreeNodeFullName
END
*/
---------------------------------------------------------------------------------------------------------
【金蝶云星空BOS二次开发案例演示】https://vip.kingdee.com/article/94751030918525696
二开案例.表达式函数.获取分组全名
本文2024-09-23 04:18:47发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-164503.html