首页 > 其他分享 >k3cloud简单账表（组织和客户多选）

k3cloud简单账表（组织和客户多选）

时间：2024-07-03 15:30:28浏览次数：9  
标签：BOS 多选 k3cloud TXBE t0 ysd Kingdee 账表 import

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();

标签：BOS,多选,k3cloud,TXBE,t0,ysd,Kingdee,账表,import
From： https://www.cnblogs.com/woshinige/p/18281720

相关文章

  • 多选项卡的shiny
    下面是一个包含多个选项卡的Shiny应用程序示例代码。在这个例子中，我们创建了一个包含三个选项卡的Shiny应用程序，每个选项卡中都有不同的内容。library(shiny)#DefineUIui<-fluidPage(titlePanel("多选项卡Shiny应用"),tabsetPanel(tabPanel("选项......
  • el-table多选分页回显
    el-table多选分页回显1.多选项添加:reserve-selection="true"<el-table-columntype="selection"align="center"width="55":reserve-selection="true"></el-table-column>reserve-selectio......
  • 一千题，No.0087（多选题常见计分法）
    批改多选题是比较麻烦的事情，有很多不同的计分方法。有一种最常见的计分方法是：如果考生选择了部分正确选项，并且没有选择任何错误选项，则得到50%分数；如果考生选择了任何一个错误的选项，则不能得分。本题就请你写个程序帮助老师批改多选题，并且指出哪道题的哪个选项错的人最多。输......
  • 多选列表用法
    文章目录知识回顾使用方法示例代码我们在上一章回中介绍了扩展内容相关的知识，本章回中将介绍单选列表和复选列表.闲话休提，让我们一起TalkFlutter吧。知识回顾我们在本章回中将介绍单选和复选列表，它们是常用的组件，不只在移动平台中使用，在其它平台上也经常被使用，......
  • python简单账表（包括联查）
    import clrclr.AddReference("System")clr.AddReference("Kingdee.BOS")clr.AddReference("Kingdee.BOS.Core")clr.AddReference("Kingdee.BOS.DataEntity")clr.AddReference("Kingdee.BOS.App")clr.AddReference(&q......
  • C语言期末复习多选题50道（含答案）
    答案：1·BCD·2.ABC·3.A·4.AC·5.ABC·6.BCD·7.ACD·8.BD·9.BC·10.BCD·11.ABD·12.ABD·13.ABD·14.AB·15.BCD·16.BCD·17.CD·18.AB·19.BD·20.ABD·21.CD·22.ABD·23.CD·24.C·25.ABD......
  • 多选列表用法
    文章目录知识回顾使用方法示例代码我们在上一章回中介绍了扩展内容相关的知识，本章回中将介绍单选列表和复选列表.闲话休提，让我们一起TalkFlutter吧。知识回顾我们在本章回中将介绍单选和复选列表，它们是常用的组件，不只在移动平台中使用，在其它平台上也经常被使用，......
  • 金蝶云星空在原有简单账表的基础上加上自定义字段
    以采购订单执行明细表为例，在原有基础上加上下面的两个字段产业链备注：F_TXBE_CYL_Notes产业链物料明细ID：F_TXBE_CYL_MateriaID1、首先打开采购订单执行明细表过滤界面找到显示隐藏列中的字段合集 新增字段：产业链备注：F_TXBE_CYL_Notes产业链物料明细ID：F_TXBE_CYL_MateriaID2......
  • 前端菜鸡流水账日记 -- select下拉框单选改多选
    哈喽哇大家，感觉好久不见咯，儿童节过的还开心嘛，hhh，昨天我可没有偷懒，我是上班了的，只不过没有遇到什么问题，所以呢就没有分享，这不是今天就遇到了就来了！那让我们步入正题吧我今天要说的是ant下的select下拉框，正常的使用的话，配置好ant然后引用就是这样的<a-sel......
  • Avalonia下DataGrid多选MVVM绑定的功能
    安装Avalonia.Xaml.BehaviorsInstall-PackageAvalonia.Xaml.BehaviorsDataGridSelectedItemsBehavior.csusingAvalonia;usingAvalonia.Controls;usingAvalonia.Threading;usingAvalonia.Xaml.Interactivity;namespaceCgdataBase;publicclassDataGridSelected......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99