python插件 - 动态 分页账表 通过SQL存储过程输出列实现动态展示
# 部署操作
## 第一步 新建一个分页账表
![1686818137767.webp](/download/01007367f6a3c0964c389f97281fa6603ed3.webp)
## 第二步 新建一个过滤框
![image.webp](/download/0100d6262a8c6e87479fae2e14fbf6b2cb17.webp)
## 第三步 修改账表过滤窗口标识
- 把第二步的标识赋值到图片处
![image.webp](/download/01000a5a7d4f1ac44570bce8be1498a10014.webp)
## 第四步 挂载服务插件
![image.webp](/download/01002b39bf795d004da9819918e54b7acb15.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():
#分页账表 REPORTTYPE_MOVE
this.ReportProperty.ReportType = ReportType.REPORTTYPE_MOVE;
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;
#获取过滤框里的数据
_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);
#获取分页标识
dr = this.CacheDataList[filter.CurrentPosition];
global sBillDataTempTable
sBillDataTempTable = tableName;
#创建【动态构建列存储过程】数据临时表
sql = "";
#数据源设置 使用存储过程后期可以不需要修改代码直接修改存储过程实现动态列展示
#使用存储过程获取数据参数:临时表名称,开始时间,结束时间,物料标识
#sql =("/*dialect*/ EXEC MindeeSimpleTable '{0}','{1}','{2}','{3}'").format(sBillDataTempTable,_dtDateFrom,_dtDateTo,_matter);
#使用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={4}
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
AND T5.FNAME='{3}'
""").format(sBillDataTempTable,_dtDateFrom,_dtDateTo, dr["FNAME"],this.Context.LogLocale.LCID);
DBUtils.ExecuteDynamicObject(this.Context, sql);
SetDataToRptTable(filter);
#分页报表必须实现的方法,此方法用于为报表提供分页依据
def GetList(filter):
customFilter = filter.FilterParameter.CustomFilter
SQL = """ /*dialect*/SELECT FNAME from T_BAS_BILLTYPE t1
INNER JOIN T_BAS_BILLTYPE_L T2 ON T2.FBILLTYPEID=t1.FBILLTYPEID
WHERE FBILLFORMID='PUR_PurchaseOrder' AND T2.FLOCALEID=2052 """.format(this.Context.LogLocale.LCID);
dt = DBUtils.ExecuteDataSet(this.Context, SQL).Tables[0];
return dt;
#动态构建列
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:
header.AddChild(item["FName"].ToString(),LocaleValue(item["FName"].ToString(), localEid), GetEnumByValue(SqlStorageType,item["FDateTypeId"].ToString()), True);
return header;
#设置报表头
def GetReportTitles(filter):
dr = this.CacheDataList[filter.CurrentPosition];
titles = ReportTitles();
#把过滤框的日期放到报表头
#titles.AddTitle("FBeginDate", str(filter.FilterParameter.CustomFilter["FBeginDate"]));
#titles.AddTitle("FEndDate", str(filter.FilterParameter.CustomFilter["FEndDate"]));
titles.AddTitle("FBILLTYPEID", dr["FNAME"].ToString());
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;
#枚举操作
def GetEnumByValue(enumType,value):
return Enum.Parse(enumType, value);
```
# 代码解析
- 分页账表 跟简单账表最大不同就是需要调用多一个函数 【GetList()】 以及增加一个过滤条件
**GetList() 配置分页依据**
- BuilderReportSqlAndTempTable() 函数增加啊 this.CacheDataList[filter.CurrentPosition]; 获取当前分页位置
# 实现效果
![image.webp](/download/01006839bbee153348caa06c3aac8f438bbd.webp)
[Python 【动态列】账表合集](https://wenku.my7c.com/article/456888394816161792?productLineId=1)
**即时注册,即时实现!!!**
复制,一直报错,求大侠指导;
python插件 - 动态 分页账表 通过SQL存储过程输出列实现动态展示
# 部署操作## 第一步 新建一个分页账表![1686818137767.webp](/download/01007367f6a3c0964c389f97281fa6603ed3.webp)## 第二步 新建...
点击下载文档
本文2024-09-16 17:14:24发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-14428.html
您需要登录后才可以发表评论, 登录登录 或者 注册
最新文档
热门文章