python插件 - 动态 树形账表 通过SQL存储过程输出列实现动态展示

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

python插件 - 动态 树形账表 通过SQL存储过程输出列实现动态展示

# 部署操作 ## 第一步 新建一个树形账表 ![image.webp](/download/01001631856bc2f0457fa4be758a64b03b7e.webp) ## 第二步 新建一个过滤框 ![image.webp](/download/010081cb3d556bea41cba278ba2457919371.webp) ## 第三步 修改过滤窗口标识 ![image.webp](/download/0100d01598083894485aa7a1df6d8cda13b5.webp) ## 第四步 部署插件 ![image.webp](/download/010052a18686f45e4d7a93b2a49106b57cec.webp) # python 插件代码 ``` python #作者: 老李头(Dean) import clr clr.AddReference("System") clr.AddReference("System.Core") clr.AddReference("Kingdee.BOS") clr.AddReference("Kingdee.BOS.App") clr.AddReference("Kingdee.BOS.Core") clr.AddReference("Kingdee.BOS.Contracts") clr.AddReference("Kingdee.BOS.DataEntity") #引入命名空间 from Kingdee.BOS import * from Kingdee.BOS.App import * from Kingdee.BOS.App.Data import * from Kingdee.BOS.Core.Enums import * from Kingdee.BOS.Contracts import * from Kingdee.BOS.Contracts.Report import * from Kingdee.BOS.Core.Report.PlugIn import * from Kingdee.BOS.Core.Report import * from Kingdee.BOS.Orm.DataEntity import * from Kingdee.BOS.Util import * from System import * from System.Collections.Generic import * #import datetime #全局变量 global sBillDataTempTable; sBillDataTempTable = ""; global _dtDateFrom; #存放开始日期数据 global _dtDateTo;#存放结束日期数据 def Initialize(): #账表类型 this.ReportProperty.ReportType = ReportType.REPORTTYPE_TREE; this.ReportProperty.ReportName = LocaleValue("老李头的树形账表示例Python", this.Context.UserLocale.LCID); #支持分组汇总 this.ReportProperty.IsGroupSummary = True; this.ReportProperty.IsUIDesignerColumns = False; #默认分组依据,此依据不支持动态级次分组,如果没有设置过滤方案分组,就默认以此条件分组 this.ReportProperty.GroupSummaryInfoData.DefaultGroupbyString = "单据号"; #获取过滤条件 用于数据源过滤 def GetFilter(filter): dyFilter = filter.FilterParameter.CustomFilter; global _dtDateFrom; global _dtDateTo; #global _matter; #获取过滤框里的数据 _dtDateFrom = GetDataByKey(dyFilter, "FBeginDate").ToString() if GetDataByKey(dyFilter, "FBeginDate") != "" else ""; _dtDateTo = GetDataByKey(dyFilter, "FEndDate").ToString() if GetDataByKey(dyFilter, "FEndDate") != "" else ""; return dyFilter; #构造取数Sql,取数据填充到临时表:tableName def BuilderReportSqlAndTempTable(filter,tableName): dyFilter = GetFilter(filter); global sBillDataTempTable sBillDataTempTable = tableName; #创建【动态构建列存储过程】数据临时表 sql = ""; #数据源设置 使用存储过程后期可以不需要修改代码直接修改存储过程实现动态列展示 #使用SQL语句获取数据 sql =("""/*dialect*/SELECT T1.FBILLNO 单据号,T5.FNAME AS 单据类型 ,T2.FQTY 数量 ,T3.FNUMBER 物料代码 ,T4.FNAME 物料名称 ,T4.FDESCRIPTION 描述 ,ROW_NUMBER() OVER(ORDER BY T1.FID,T2.FENTRYID) FIDENTITYID into {0} FROM T_PUR_POORDER t1 INNER JOIN T_PUR_POORDERentry t2 ON T2.FID=T1.FID INNER JOIN T_BD_MATERIAL T3 ON T3.FMATERIALID=T2.FMATERIALID INNER JOIN T_BD_MATERIAL_L T4 ON T4.FMATERIALID=T3.FMASTERID INNER JOIN T_BAS_BILLTYPE_L T5 ON T5.FBILLTYPEID=t1.FBILLTYPEID AND T5.FLOCALEID={3} WHERE 1=1 AND 1 = CASE WHEN ISNULL('{1}','') = '' THEN 1 ELSE CASE WHEN ISNULL(T1.FDATE,'') >= '{1}' THEN 1 ELSE 0 END END AND 1 = CASE WHEN ISNULL('{2}','') = '' THEN 1 ELSE CASE WHEN ISNULL(T1.FDATE,'') <= '{2}' THEN 1 ELSE 0 END END """).format(sBillDataTempTable,_dtDateFrom,_dtDateTo, this.Context.LogLocale.LCID); #树形账表节点值 if filter.CurrentGroupID is not None and filter.CurrentGroupID.ToString() != "0" and filter.CurrentGroupID.ToString() != "-1": sql = sql + " AND T1.FBILLTYPEID ='{0}' ".format(filter.CurrentGroupID.ToString()); DBUtils.ExecuteDynamicObject(this.Context, sql); SetDataToRptTable(filter); #设置树形节点 def GetTreeNodes(e): nodeList = List[TreeNode](); SQL = """ SELECT t1.FBILLTYPEID,t2.FNAME FROM T_BAS_BILLTYPE t1 INNER JOIN T_BAS_BILLTYPE_L t2 ON t2.FBILLTYPEID=t1.FBILLTYPEID AND t2.FLOCALEID={0} WHERE FBILLFORMID='PUR_PurchaseOrder'""".format(this.Context.LogLocale.LCID); OBJs = DBUtils.ExecuteDynamicObject(this.Context, SQL); if OBJs is not None: for item in OBJs: node = TreeNode(); node.id = item["FBILLTYPEID"].ToString(); node.text = item["FNAME"].ToString(); nodeList.Add(node); return nodeList; #动态构建列 def GetReportHeaders(filter): header = ReportHeader(); localEid=this.Context.UserLocale.LCID; sql = ("""SELECT T1.system_type_id as FDateTypeId , t3.name AS FDateType,t1.name AS FName FROM sys.columns t1 INNER JOIN sys.objects t2 ON t2.object_id = t1.object_id INNER JOIN sys.types t3 ON t3.user_type_id=t1.user_type_id WHERE t2.name='{0}' AND t2.type='u' """).format(sBillDataTempTable); RecordData = DBUtils.ExecuteDynamicObject(this.Context, sql); for item in RecordData: #隐藏不显示这个字段 FIDENTITYID #不知为什么报表格式会显示格式是 FIDENTITYID,FIDENTITYID (求解答) #if item["FName"].ToString() == str("FIDENTITYID"): # continue header.AddChild(item["FName"].ToString(),LocaleValue(item["FName"].ToString(), localEid), GetEnumByValue(SqlStorageType,item["FDateTypeId"].ToString()), True); return header; #设置报表头 def GetReportTitles(filter): titles = ReportTitles(); #把过滤框的日期放到报表头 titles.AddTitle("FBeginDate", str(filter.FilterParameter.CustomFilter["FBeginDate"])); titles.AddTitle("FEndDate", str(filter.FilterParameter.CustomFilter["FEndDate"])); return titles; #展示数据表到前台 def SetDataToRptTable(filter): sqlstr=("SELECT * FROM {0}").format(sBillDataTempTable); sqlstr2=("SELECT COUNT(*) AS Frow FROM sys.objects WHERE name='{0}' AND type='u'").format(sBillDataTempTable); ROWS = DBUtils.ExecuteDynamicObject(this.Context,sqlstr2); if str(ROWS[0]["Frow"]) != "0": DBUtils.Execute(this.Context, sqlstr); #设置合计列 def GetSummaryColumnInfo(filter): summarys = List[SummaryField](); summarys.Add(SummaryField("数量", BOSEnums.Enu_SummaryType.SUM)); return summarys; #获取DynamicObject数据包中指定key的值 def GetDataByKey(doFilter, sKey): sReturnValue = ""; if doFilter is not None and doFilter[sKey] is not None and len(str(doFilter[sKey])) > 0: sReturnValue = doFilter[sKey].ToString(); return sReturnValue; #获取DynamicObject数据包中指定key的基础资料的指定属性的值 def GetBaseDataByKey(doFilter,sKey,sItem): sReturnValue=""; if doFilter is not None and doFilter[sKey] is not None and len(str(doFilter[sKey])) > 0: doTemp = doFilter[sKey]; sReturnValue = doTemp[sItem].ToString(); return sReturnValue; #枚举操作 def GetEnumByValue(enumType,value): return Enum.Parse(enumType, value); ``` # 代码解析 树形账表 跟简单账表最大不同就是需要调用多一个函数 【GetTreeNodes()】 以及增加一个过滤条件 ***GetTreeNodes 配置树形节点, 获取选中的树形节点标识 CurrentGroupID*** # 实现效果 ![image.webp](/download/0100dd28a852a0894df7b82babd25077e703.webp) [Python 【动态列】账表合集](https://wenku.my7c.com/article/456888394816161792?productLineId=1) **即时注册,即时实现!!!**

python插件 - 动态 树形账表 通过SQL存储过程输出列实现动态展示

# 部署操作## 第一步 新建一个树形账表![image.webp](/download/01001631856bc2f0457fa4be758a64b03b7e.webp)## 第二步 新建一个过滤...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息