k3cloud简单账表（组织和客户多选）
import clr
clr.AddReference("System")
clr.AddReference("Kingdee.BOS")
clr.AddReference("Kingdee.BOS.Core")
clr.AddReference("Kingdee.BOS.DataEntity")
clr.AddReference("Kingdee.BOS.App")
clr.AddReference("Kingdee.BOS.Contracts")
clr.AddReference("Kingdee.BOS.ServiceHelper")
from Kingdee.BOS import*
from Kingdee.BOS.Contracts import*
from Kingdee.BOS.Contracts.Report import*
from Kingdee.BOS.Core import *
from Kingdee.BOS.Core.Metadata import *
from Kingdee.BOS.Core.Report import*
from Kingdee.BOS.Core.SqlBuilder import*
from Kingdee.BOS.Core.Enums import *
from Kingdee.BOS.App.Data import*
from Kingdee.BOS.Orm.DataEntity import*
from System import*
from System.ComponentModel import*
from System.Collections.Generic import*
from System.Text import*
from System.Threading.Tasks import*
from Kingdee.BOS.ServiceHelper import *
from System import DateTime
#初始化
def Initialize():
#是否由插件创建临时表 true 即调用BuilderReportSqlAndTempTable构建临时表 把账表取数结果放到创建的临时表中
# 否则调用以下3个接口，完成账表取数逻辑的sql指令即：BuilderSelectFieldSQL、BuilderTempTableOrderBySQL、BuilderFormWhereSQL
this.IsCreateTempTableByPlugin = True;
#是否分组汇总 在GetSummaryColumnInfo方法中添加汇总字段
this.ReportProperty.IsGroupSummary = True;
#是否由ui设置 = False表示报表的列通过插件控制 在GetReportHeaders中构建列头
this.ReportProperty.IsUIDesignerColumns = False;
# 设置账表的类型
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
#设置精度？
listControlField = List[DecimalControlField]();
#显示的字段名 用于控制精度的字段名,要精度控制起作用，head中的 SqlStorageType.SqlDecimal参数必须要有
#listControlField.Add(DecimalControlField("FPrice", "jindu"));
this.ReportProperty.DecimalControlFieldList = listControlField;
#设置汇总行 最下面的合计值 报表合计列(可选)
def GetSummaryColumnInfo(filter):
summaryList = List[SummaryField]();
summaryList.Add(SummaryField("FALLAMOUNTFOR", BOSEnums.Enu_SummaryType.SUM));
summaryList.Add(SummaryField("FREALRECAMOUNT", BOSEnums.Enu_SummaryType.SUM));
summaryList.Add(SummaryField("arrears", BOSEnums.Enu_SummaryType.SUM));
return summaryList;
#设置报表头 账表表头字段信息，通常在GetReportTitles对表头字段进行传值 主要是把过滤框设置的字段值，显示到报表表头
def GetReportTitles(filter):
reportTitles = ReportTitles();
custFilter=filter.FilterParameter.CustomFilter;#获取过滤框的数据包
beginDate=str(custFilter["F_TXBE_BeginDate"]);#获取开始日期
EndDate=str(custFilter["F_TXBE_End_Date"]);#获取结束日期
#orgFname=str(custFilter["F_BPW_OrgId"]["name"]);#获取组织名称
#FcustName=str(custFilter["F_TXBE_Base_kehu"]["name"]);
#orgFname=("{0}").format(orgObj["name"]);#组织名称
reportTitles.AddTitle("F_TXBE_BeginDate", beginDate);
reportTitles.AddTitle("F_TXBE_End_Date", EndDate);
#reportTitles.AddTitle("F_BPW_OrgId", orgFname);
#reportTitles.AddTitle("F_TXBE_Base_kehu", FcustName);
return reportTitles;
#动态构造列 表格列名
def GetReportHeaders(filter):
headers = ReportHeader();
localEid = this.Context.UserLocale.LCID;
fb=headers.AddChild("zzNumber", LocaleValue("组织编码", localEid));
headers.AddChild("zzFname", LocaleValue("组织名称", localEid));
headers.AddChild("FNumber_e2", LocaleValue("客户编码", localEid));
headers.AddChild("FName_e2", LocaleValue("客户名称", localEid));
headers.AddChild("FALLAMOUNTFOR", LocaleValue("本期应收", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("FREALRECAMOUNT", LocaleValue("本期收款", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("FREALREFUNDAMOUNT", LocaleValue("本期退款", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("arrears", LocaleValue("剩余欠款", localEid), SqlStorageType.SqlDecimal);
return headers;
#构造取数Sql，取数据填充到临时表：tableName 临时表构造
def BuilderReportSqlAndTempTable(rptfilter, tableName):
custFilter = rptfilter.FilterParameter.CustomFilter; #快捷框过滤条件
if custFilter==None :
return;
else:
F_TXBE_BeginDate=str(custFilter["F_TXBE_BeginDate"]);#获取开始日期
F_TXBE_End_Date=str(custFilter["F_TXBE_End_Date"]);#获取结束日期
orgObj=custFilter["F_BPW_OrgId"];#获取组织
# whereOrgs="";
# if orgObj!=None:
# orgId=("{0}").format(orgObj["Id"]);#组织ID
# whereOrgs=(" and ysd.FSETTLEORGID in ({0}) ").format(orgId);#选择了组织，拼接组织过滤
# FCUST=custFilter["F_TXBE_Base_kehu"];#获取客户
# wheremat="";
# if FCUST!=None:
# FCUSTId=("{0}").format(FCUST["Id"]);#组织ID
# wheremat=(" and ysd.FCUSTID in ({0}) ").format(FCUSTId);#拼接客户过滤
F_BPW_OrgIds=custFilter["F_BPW_OrgIds"];#组织多选过滤
F_BPW_OrgIdslist=List[str]();
if(F_BPW_OrgIds !=None): #多选组织不为空
for m in F_BPW_OrgIds:
F_BPW_OrgIdNum="'"+str(m["F_BPW_OrgIds"]["Id"])+"'";#取出过滤框选择的多个组织编码
F_BPW_OrgIdslist.Add(F_BPW_OrgIdNum);
whereMatlist=("and ysd.FSETTLEORGID in ({0})").format(str.Join(",",F_BPW_OrgIdslist)) if(F_BPW_OrgIdslist.Count>0) else "";#拼接组织多选过滤
F_TXBE_Base_kehus=custFilter["F_TXBE_Base_kehus"];#客户多选过滤
F_TXBE_Base_kehuslist=List[str]();
if(F_TXBE_Base_kehus !=None): #多选组织不为空
for m in F_TXBE_Base_kehus:
F_TXBE_Base_kehusNum="'"+str(m["F_TXBE_Base_kehus"]["Id"])+"'";#取出过滤框选择的多个组织编码
F_TXBE_Base_kehuslist.Add(F_TXBE_Base_kehusNum);
custlist=("and ysd.FCUSTID in ({0})").format(str.Join(",",F_TXBE_Base_kehuslist)) if(F_TXBE_Base_kehuslist.Count>0) else "";#拼接组织多选过滤
sql=("""/*dialect*/
-- 组织id,组织编码,组织名称，客户id,客户编码，客户名称
select ysd.FSETTLEORGID as FSETTLEORGID,ysd.zzNumber as zzNumber,ysd.zzFname as zzFname,ysd.FCUSTID,ysd.FNumber_e2,ysd.FName_e2,
ysd.FALLAMOUNTFOR,skd.FREALRECAMOUNT as FREALRECAMOUNT,skd.FREALREFUNDAMOUNT as FREALREFUNDAMOUNT,(ysd.FALLAMOUNTFOR-(isnull(skd.FREALRECAMOUNT,0)-isnull(skd.FREALREFUNDAMOUNT,0))) as arrears
,row_number() over(order by ysd.FSETTLEORGID) as FIDENTITYID
into {0}
from (select ys.FSETTLEORGID,zzNumber,zzFname,ys.FCUSTID,FNumber_e2,FName_e2,sum(isnull(FALLAMOUNTFOR,0)) as FALLAMOUNTFOR
from
(SELECT
e0.FSETTLEORGID as FSETTLEORGID,e0.FBillTypeID,e0.FDocumentStatus,e0.FCUSTOMERID,e0.FDATE, e1.FOrgID,e1.FNumber as zzNumber,e1.FName as zzFname, e2.FCUSTID,e2.FNumber As FNumber_e2,e2.FName As FName_e2,
e0.FALLAMOUNTFOR as FALLAMOUNTFOR
FROM (
SELECT t0.fid as fid,t0.FSETTLEORGID AS FSETTLEORGID,t0.FBILLTYPEID AS FBillTypeID,t0.FDOCUMENTSTATUS AS FDocumentStatus,t0.FCUSTOMERID AS FCUSTOMERID,t0.FDATE AS FDATE,FALLAMOUNTFOR
FROM t_AR_receivable t0
where FDocumentStatus='C' and FBILLTYPEID in('180ecd4afd5d44b5be78a6efe4a7e041','659e4c0a0359d3','6386ba7ac1df48')
and FDATE>='{1}' and FDATE<='{2}' --单据类型为659e4c0a0359d3、联合批发折让单
) e0
INNER JOIN (
-- 组织信息
SELECT t0.FORGID AS FOrgID,t0.FNUMBER AS FNumber,t0_L.FNAME AS FName FROM T_ORG_Organizations t0 LEFT JOIN
T_ORG_Organizations_L t0_L ON (t0.FORGID=t0_L.FORGID AND t0_L.FLocaleId=2052)
) e1 ON e0.FSETTLEORGID = e1.FORGID
INNER JOIN (
-- 客户信息
SELECT t0.FCUSTID AS FCUSTID,t0.FNUMBER AS FNumber,t0_L.FNAME AS FName FROM T_BD_CUSTOMER t0 LEFT JOIN
T_BD_CUSTOMER_L t0_L ON (t0.FCUSTID=t0_L.FCUSTID AND t0_L.FLocaleId=2052) where t0.FPRIMARYGROUP<>148870
) e2 ON e0.FCUSTOMERID = e2.FCUSTID
)ys
group by ys.FSETTLEORGID,zzNumber,zzFname,ys.FCUSTID,FNumber_e2,FName_e2
)ysd
-- 收款单
left join
--收款组织，往来单位
(select sk.FPAYORGID,sk.F_ORA_BASE, sum(isnull(sk.FREALRECAMOUNT,0)) as FREALRECAMOUNT,sum(isnull(FREALREFUNDAMOUNT,0)) as FREALREFUNDAMOUNT --sum(isnull(sk.FREALRECAMOUNT,0)) as FREALRECAMOUNT
from T_AR_RECEIVEBILL sk
left join T_AR_REFUNDBILLSRCENTRY tkl
on sk.FBILLNO=tkl.FSRCBILLNO
where FDocumentStatus='C' and FDATE>='{1}' and FDATE<='{2}'
group by FPAYORGID,F_ORA_BASE
)skd
on ysd.FSETTLEORGID=skd.FPAYORGID and ysd.FCUSTID=skd.F_ORA_BASE
where 1=1 {3} {4}
""").format(tableName,F_TXBE_BeginDate,F_TXBE_End_Date,whereMatlist,custlist); #
#raise Exception(sql);#可以通过此方法弹出Sql语句进行调试验证
DBUtils.Execute(this.Context,sql);#执行SQL，将报表数据写入临时表
#报表关闭触发，通常在此处清理报表过程产生的临时表
def CloseReport():
this.DropTempTable();
clr.AddReference("System")
clr.AddReference("Kingdee.BOS")
clr.AddReference("Kingdee.BOS.Core")
clr.AddReference("Kingdee.BOS.DataEntity")
clr.AddReference("Kingdee.BOS.App")
clr.AddReference("Kingdee.BOS.Contracts")
clr.AddReference("Kingdee.BOS.ServiceHelper")
from Kingdee.BOS import*
from Kingdee.BOS.Contracts import*
from Kingdee.BOS.Contracts.Report import*
from Kingdee.BOS.Core import *
from Kingdee.BOS.Core.Metadata import *
from Kingdee.BOS.Core.Report import*
from Kingdee.BOS.Core.SqlBuilder import*
from Kingdee.BOS.Core.Enums import *
from Kingdee.BOS.App.Data import*
from Kingdee.BOS.Orm.DataEntity import*
from System import*
from System.ComponentModel import*
from System.Collections.Generic import*
from System.Text import*
from System.Threading.Tasks import*
from Kingdee.BOS.ServiceHelper import *
from System import DateTime
#初始化
def Initialize():
#是否由插件创建临时表 true 即调用BuilderReportSqlAndTempTable构建临时表 把账表取数结果放到创建的临时表中
# 否则调用以下3个接口，完成账表取数逻辑的sql指令即：BuilderSelectFieldSQL、BuilderTempTableOrderBySQL、BuilderFormWhereSQL
this.IsCreateTempTableByPlugin = True;
#是否分组汇总 在GetSummaryColumnInfo方法中添加汇总字段
this.ReportProperty.IsGroupSummary = True;
#是否由ui设置 = False表示报表的列通过插件控制 在GetReportHeaders中构建列头
this.ReportProperty.IsUIDesignerColumns = False;
# 设置账表的类型
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
#设置精度？
listControlField = List[DecimalControlField]();
#显示的字段名 用于控制精度的字段名,要精度控制起作用，head中的 SqlStorageType.SqlDecimal参数必须要有
#listControlField.Add(DecimalControlField("FPrice", "jindu"));
this.ReportProperty.DecimalControlFieldList = listControlField;
#设置汇总行 最下面的合计值 报表合计列(可选)
def GetSummaryColumnInfo(filter):
summaryList = List[SummaryField]();
summaryList.Add(SummaryField("FALLAMOUNTFOR", BOSEnums.Enu_SummaryType.SUM));
summaryList.Add(SummaryField("FREALRECAMOUNT", BOSEnums.Enu_SummaryType.SUM));
summaryList.Add(SummaryField("arrears", BOSEnums.Enu_SummaryType.SUM));
return summaryList;
#设置报表头 账表表头字段信息，通常在GetReportTitles对表头字段进行传值 主要是把过滤框设置的字段值，显示到报表表头
def GetReportTitles(filter):
reportTitles = ReportTitles();
custFilter=filter.FilterParameter.CustomFilter;#获取过滤框的数据包
beginDate=str(custFilter["F_TXBE_BeginDate"]);#获取开始日期
EndDate=str(custFilter["F_TXBE_End_Date"]);#获取结束日期
#orgFname=str(custFilter["F_BPW_OrgId"]["name"]);#获取组织名称
#FcustName=str(custFilter["F_TXBE_Base_kehu"]["name"]);
#orgFname=("{0}").format(orgObj["name"]);#组织名称
reportTitles.AddTitle("F_TXBE_BeginDate", beginDate);
reportTitles.AddTitle("F_TXBE_End_Date", EndDate);
#reportTitles.AddTitle("F_BPW_OrgId", orgFname);
#reportTitles.AddTitle("F_TXBE_Base_kehu", FcustName);
return reportTitles;
#动态构造列 表格列名
def GetReportHeaders(filter):
headers = ReportHeader();
localEid = this.Context.UserLocale.LCID;
fb=headers.AddChild("zzNumber", LocaleValue("组织编码", localEid));
headers.AddChild("zzFname", LocaleValue("组织名称", localEid));
headers.AddChild("FNumber_e2", LocaleValue("客户编码", localEid));
headers.AddChild("FName_e2", LocaleValue("客户名称", localEid));
headers.AddChild("FALLAMOUNTFOR", LocaleValue("本期应收", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("FREALRECAMOUNT", LocaleValue("本期收款", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("FREALREFUNDAMOUNT", LocaleValue("本期退款", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("arrears", LocaleValue("剩余欠款", localEid), SqlStorageType.SqlDecimal);
return headers;
#构造取数Sql，取数据填充到临时表：tableName 临时表构造
def BuilderReportSqlAndTempTable(rptfilter, tableName):
custFilter = rptfilter.FilterParameter.CustomFilter; #快捷框过滤条件
if custFilter==None :
return;
else:
F_TXBE_BeginDate=str(custFilter["F_TXBE_BeginDate"]);#获取开始日期
F_TXBE_End_Date=str(custFilter["F_TXBE_End_Date"]);#获取结束日期
orgObj=custFilter["F_BPW_OrgId"];#获取组织
# whereOrgs="";
# if orgObj!=None:
# orgId=("{0}").format(orgObj["Id"]);#组织ID
# whereOrgs=(" and ysd.FSETTLEORGID in ({0}) ").format(orgId);#选择了组织，拼接组织过滤
# FCUST=custFilter["F_TXBE_Base_kehu"];#获取客户
# wheremat="";
# if FCUST!=None:
# FCUSTId=("{0}").format(FCUST["Id"]);#组织ID
# wheremat=(" and ysd.FCUSTID in ({0}) ").format(FCUSTId);#拼接客户过滤
F_BPW_OrgIds=custFilter["F_BPW_OrgIds"];#组织多选过滤
F_BPW_OrgIdslist=List[str]();
if(F_BPW_OrgIds !=None): #多选组织不为空
for m in F_BPW_OrgIds:
F_BPW_OrgIdNum="'"+str(m["F_BPW_OrgIds"]["Id"])+"'";#取出过滤框选择的多个组织编码
F_BPW_OrgIdslist.Add(F_BPW_OrgIdNum);
whereMatlist=("and ysd.FSETTLEORGID in ({0})").format(str.Join(",",F_BPW_OrgIdslist)) if(F_BPW_OrgIdslist.Count>0) else "";#拼接组织多选过滤
F_TXBE_Base_kehus=custFilter["F_TXBE_Base_kehus"];#客户多选过滤
F_TXBE_Base_kehuslist=List[str]();
if(F_TXBE_Base_kehus !=None): #多选组织不为空
for m in F_TXBE_Base_kehus:
F_TXBE_Base_kehusNum="'"+str(m["F_TXBE_Base_kehus"]["Id"])+"'";#取出过滤框选择的多个组织编码
F_TXBE_Base_kehuslist.Add(F_TXBE_Base_kehusNum);
custlist=("and ysd.FCUSTID in ({0})").format(str.Join(",",F_TXBE_Base_kehuslist)) if(F_TXBE_Base_kehuslist.Count>0) else "";#拼接组织多选过滤
sql=("""/*dialect*/
-- 组织id,组织编码,组织名称，客户id,客户编码，客户名称
select ysd.FSETTLEORGID as FSETTLEORGID,ysd.zzNumber as zzNumber,ysd.zzFname as zzFname,ysd.FCUSTID,ysd.FNumber_e2,ysd.FName_e2,
ysd.FALLAMOUNTFOR,skd.FREALRECAMOUNT as FREALRECAMOUNT,skd.FREALREFUNDAMOUNT as FREALREFUNDAMOUNT,(ysd.FALLAMOUNTFOR-(isnull(skd.FREALRECAMOUNT,0)-isnull(skd.FREALREFUNDAMOUNT,0))) as arrears
,row_number() over(order by ysd.FSETTLEORGID) as FIDENTITYID
into {0}
from (select ys.FSETTLEORGID,zzNumber,zzFname,ys.FCUSTID,FNumber_e2,FName_e2,sum(isnull(FALLAMOUNTFOR,0)) as FALLAMOUNTFOR
from
(SELECT
e0.FSETTLEORGID as FSETTLEORGID,e0.FBillTypeID,e0.FDocumentStatus,e0.FCUSTOMERID,e0.FDATE, e1.FOrgID,e1.FNumber as zzNumber,e1.FName as zzFname, e2.FCUSTID,e2.FNumber As FNumber_e2,e2.FName As FName_e2,
e0.FALLAMOUNTFOR as FALLAMOUNTFOR
FROM (
SELECT t0.fid as fid,t0.FSETTLEORGID AS FSETTLEORGID,t0.FBILLTYPEID AS FBillTypeID,t0.FDOCUMENTSTATUS AS FDocumentStatus,t0.FCUSTOMERID AS FCUSTOMERID,t0.FDATE AS FDATE,FALLAMOUNTFOR
FROM t_AR_receivable t0
where FDocumentStatus='C' and FBILLTYPEID in('180ecd4afd5d44b5be78a6efe4a7e041','659e4c0a0359d3','6386ba7ac1df48')
and FDATE>='{1}' and FDATE<='{2}' --单据类型为659e4c0a0359d3、联合批发折让单
) e0
INNER JOIN (
-- 组织信息
SELECT t0.FORGID AS FOrgID,t0.FNUMBER AS FNumber,t0_L.FNAME AS FName FROM T_ORG_Organizations t0 LEFT JOIN
T_ORG_Organizations_L t0_L ON (t0.FORGID=t0_L.FORGID AND t0_L.FLocaleId=2052)
) e1 ON e0.FSETTLEORGID = e1.FORGID
INNER JOIN (
-- 客户信息
SELECT t0.FCUSTID AS FCUSTID,t0.FNUMBER AS FNumber,t0_L.FNAME AS FName FROM T_BD_CUSTOMER t0 LEFT JOIN
T_BD_CUSTOMER_L t0_L ON (t0.FCUSTID=t0_L.FCUSTID AND t0_L.FLocaleId=2052) where t0.FPRIMARYGROUP<>148870
) e2 ON e0.FCUSTOMERID = e2.FCUSTID
)ys
group by ys.FSETTLEORGID,zzNumber,zzFname,ys.FCUSTID,FNumber_e2,FName_e2
)ysd
-- 收款单
left join
--收款组织，往来单位
(select sk.FPAYORGID,sk.F_ORA_BASE, sum(isnull(sk.FREALRECAMOUNT,0)) as FREALRECAMOUNT,sum(isnull(FREALREFUNDAMOUNT,0)) as FREALREFUNDAMOUNT --sum(isnull(sk.FREALRECAMOUNT,0)) as FREALRECAMOUNT
from T_AR_RECEIVEBILL sk
left join T_AR_REFUNDBILLSRCENTRY tkl
on sk.FBILLNO=tkl.FSRCBILLNO
where FDocumentStatus='C' and FDATE>='{1}' and FDATE<='{2}'
group by FPAYORGID,F_ORA_BASE
)skd
on ysd.FSETTLEORGID=skd.FPAYORGID and ysd.FCUSTID=skd.F_ORA_BASE
where 1=1 {3} {4}
""").format(tableName,F_TXBE_BeginDate,F_TXBE_End_Date,whereMatlist,custlist); #
#raise Exception(sql);#可以通过此方法弹出Sql语句进行调试验证
DBUtils.Execute(this.Context,sql);#执行SQL，将报表数据写入临时表
#报表关闭触发，通常在此处清理报表过程产生的临时表
def CloseReport():
this.DropTempTable();
