python简单账表（包括联查）
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 *
#初始化
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("FQty", BOSEnums.Enu_SummaryType.SUM));
summaryList.Add(SummaryField("amount", BOSEnums.Enu_SummaryType.SUM));
return summaryList;
#设置报表头 账表表头字段信息，通常在GetReportTitles对表头字段进行传值 主要是把过滤框设置的字段值，显示到报表表头
def GetReportTitles(filter):
titles = ReportTitles();
return titles;
#动态构造列 表格列名
def GetReportHeaders(filter):
headers = ReportHeader();
localEid = this.Context.UserLocale.LCID;
fb=headers.AddChild("FBillNo", LocaleValue("单据编号", localEid));
headers.AddChild("FDATE", LocaleValue("日期", localEid));
headers.AddChild("FNumber", LocaleValue("物料编码", localEid));
headers.AddChild("FName", LocaleValue("物料名称", localEid));
headers.AddChild("FName_e3", LocaleValue("存货类别", localEid));
headers.AddChild("FName_e5", LocaleValue("单位", localEid));
headers.AddChild("FPrice", LocaleValue("单价", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("FQty", LocaleValue("数量", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("amount", LocaleValue("金额", localEid), SqlStorageType.SqlDecimal);
a=headers.AddChild("a", LocaleValue("详情", localEid));
a.ColIndex=100
headers.AddChild("bmlfname", LocaleValue("部门", localEid));
headers.AddChild("F_TXBE_AREA", LocaleValue("面积", localEid));
return headers;
#构造取数Sql，取数据填充到临时表：tableName 临时表构造
def BuilderReportSqlAndTempTable(rptfilter, tableName):
custFilter = rptfilter.FilterParameter.CustomFilter;
if custFilter==None :
return;
orgObj=custFilter["F_BPW_OrgId"];#获取组织
whereOrgs="";
if orgObj!=None:
orgId=("{0}").format(orgObj["Id"]);#组织ID
whereOrgs=(" and e8.FStockOrgId in ({0}) ").format(orgId);#选择了组织，拼接组织过滤
materials=custFilter["F_BPW_Materials"];#物料多选过滤
matList=[];
if materials!=None:
for m in materials:
materialNum="'"+str(m["F_BPW_Materials"]["Number"])+"'";#取出过滤框选择的多个物料编码
matList.Add(materialNum);
if len(matList)>0:
whereMat=(" and e8.FNumber in ({0})").format(str.join(",",matList)) #把matlist按照逗号的方式拼接在一起
else:
whereMat="";
#raise Exception(str(whereMat));
sql=("""/*dialect*/
select e8.fid as fid,e8.FBillNo as FBillNo ,e8.FPrice as FPrice,e8.FQty as FQty,e8.FNumber as FNumber,e8.FName as Fname,e8.FNumber_e3 as FNumber_e3,e8.FName_e3 as FName_e3,e8.FName_e5 as FName_e5
,e8.FPrice*e8.FQty as amount,e8.FDATE as FDATE
,e8.bmlfname as bmlfname,e8.F_TXBE_AREA as F_TXBE_AREA
,e8.FSTOCKORGID as FStockOrgId
,row_number() over(order by e8.fid) as FIDENTITYID,(SELECT STUFF((
SELECT top 10 ',' + FBillNo
FROM T_STK_MISDELIVERY
FOR XML PATH('')
), 1, 1, '') AS concatenated_strings) as a
into {0}
from
(select e7.fid as fid,e7.FBillNo as FBillNo ,e7.FPrice as FPrice,e7.FQty as FQty,e7.FNumber as FNumber,e7.FName as Fname,e7.FNumber_e3 as FNumber_e3,e7.FName_e3 as FName_e3,e7.FName_e5 as FName_e5
,e7.FDATE as FDATE,e7.bmlfname as bmlfname,e7.F_TXBE_AREA as F_TXBE_AREA,FSTOCKORGID
from
(select e6.fid as fid,e6.FBillNo as FBillNo ,e6.FPrice as FPrice,e6.FQty as FQty,e6.FNumber as FNumber,e6.FName as Fname,e6.FNumber_e3 as FNumber_e3,e6.FName_e3 as FName_e3,e6.FName_e5 as FName_e5
,e6.FDATE as FDATE,e6.bmlfname as bmlfname,e6.F_TXBE_AREA as F_TXBE_AREA,FSTOCKORGID
from (SELECT
e0.FID,e0.FStockOrgId,e0.FBillNo,e0.FDocumentStatus, e1.FPrice,e1.FQty,e1.FMaterialId, e2.FNumber,e2.FName, e3.FCATEGORYID,e3.FNumber As FNumber_e3,e3.FName As FName_e3, e5.FUNITID,e5.FName As FName_e5
,e0.FDATE as FDATE,e0.bmlfname as bmlfname,F_TXBE_AREA as F_TXBE_AREA
FROM (
SELECT t0.FID AS FID,t0.FSTOCKORGID AS FStockOrgId,t0.FBILLNO AS FBillNo,t0.FDOCUMENTSTATUS AS FDocumentStatus,FDATE,bml.FNAME as bmlfname,F_TXBE_AREA as F_TXBE_AREA
-- 其他出库单
FROM T_STK_MISDELIVERY t0
--部门表
left join T_BD_DEPARTMENT as bm on t0.FDEPTID=bm.FDEPTID
inner join T_BD_DEPARTMENT_L as bml on bm.FDEPTID=bml.FDEPTID
WHERE
t0.FOBJECTTYPEID = 'STK_MisDelivery' and t0.FSTOCKORGID IN (105887, 105891, 105900, 105901, 105902, 105903, 105904, 105906, 105908, 105909, 105910, 305795, 621474, 702911)
and t0.FDOCUMENTSTATUS='C'
) e0
INNER JOIN (
SELECT t0.FID AS FID,t1.FPRICE AS FPrice,t1.FQTY AS FQty,t1.FMATERIALID AS FMaterialId FROM T_STK_MISDELIVERY t0 LEFT JOIN T_STK_MISDELIVERYENTRY t1
ON (t0.FID = t1.FID ) WHERE
t0.FOBJECTTYPEID = 'STK_MisDelivery'
) e1 ON e0.FID = e1.FID
INNER JOIN (
SELECT t0.FMATERIALID AS FMATERIALID,t0.FNUMBER AS FNumber,t0_L.FNAME AS FName FROM T_BD_MATERIAL t0 LEFT JOIN
T_BD_MATERIAL_L t0_L ON (t0.FMATERIALID=t0_L.FMATERIALID AND t0_L.FLocaleId=2052)
) e2 ON e1.FMATERIALID = e2.FMATERIALID
INNER JOIN (
SELECT t0.FMATERIALID AS FMATERIALID,t4.FCATEGORYID AS FCategoryID,t4.FBASEUNITID AS FBaseUnitId FROM T_BD_MATERIAL t0 LEFT JOIN t_BD_MaterialBase t4
ON (t0.FMATERIALID = t4.FMATERIALID )
) e4 ON e2.FMATERIALID = e4.FMATERIALID
INNER JOIN (
SELECT t0.FCATEGORYID AS FCATEGORYID,t0.FNUMBER AS FNumber,t0_L.FNAME AS FName FROM T_BD_MATERIALCATEGORY t0 inner JOIN
T_BD_MATERIALCATEGORY_L t0_L ON (t0.FCATEGORYID=t0_L.FCATEGORYID AND t0_L.FLocaleId=2052) where t0_L.FNAME in('原材料-肥料'
,'原材料-农药'
,'原材料-五金辅料'
,'原材料-保温材料'
,'原材料-包材'
)
) e3 ON e4.FCATEGORYID = e3.FCATEGORYID
INNER JOIN (
SELECT t0.FUNITID AS FUNITID,t0_L.FNAME AS FName FROM T_BD_UNIT t0 LEFT JOIN
T_BD_UNIT_L t0_L ON (t0.FUNITID=t0_L.FUNITID AND t0_L.FLocaleId=2052)
) e5 ON e4.FBASEUNITID = e5.FUNITID
)e6
where e6.F_TXBE_AREA>0 and F_TXBE_AREA<1
union all
select e6.fid as fid,e6.FBillNo as FBillNo ,e6.FPrice as FPrice,e6.FAppQty as FQty,e6.FNumber as FNumber,e6.FName as Fname,e6.FNumber_e4 as FNumber_e3,e6.FName_e4 as FName_e3,e6.FName_e5 as FName_e5
,e6.FDATE as FDATE,bmlfname as bmlfname,e6.F_TXBE_AREA as F_TXBE_AREA,e6.FStockOrgId as FStockOrgId
from
(SELECT
e0.FID,e0.FStockOrgId,e0.FBillNo,e0.FDocumentStatus, e1.FPrice,e1.FAppQty,e1.FMaterialId, e2.FNumber,e2.FName, e3.FCategoryID, e4.FNumber As FNumber_e4,e4.FName As FName_e4, e5.FUNITID As FUNITID_e5,e5.FName As FName_e5
,e0.FDATE as FDATE,bml.FNAME as bmlfname,bm.F_TXBE_AREA as F_TXBE_AREA
FROM (
SELECT t0.FID AS FID,t0.FSTOCKORGID AS FStockOrgId,t0.FBILLNO AS FBillNo,t0.FDOCUMENTSTATUS AS FDocumentStatus,t0.FDATE as FDATE,FWORKSHOPID
-- 简单生产领料单
FROM T_SP_PICKMTRL t0
WHERE
t0.FFORMID = 'SP_PickMtrl' and t0.FSTOCKORGID IN (105887, 105891, 105900, 105901, 105902, 105903, 105904, 105906, 105908, 105909, 105910, 305795, 621474, 702911)
) e0
--部门表
left join T_BD_DEPARTMENT as bm on e0.FWORKSHOPID=bm.FDEPTID
left join T_BD_DEPARTMENT_L as bml on bm.FDEPTID=bml.FDEPTID
INNER JOIN (
SELECT t0.FID AS FID,t1.FPRICE AS FPrice,t1.FUNITID AS FUnitID,t1.FAPPQTY AS FAppQty,t1.FACTUALQTY AS FActualQty,t1.FMATERIALID AS FMaterialId FROM T_SP_PICKMTRL t0 LEFT JOIN T_SP_PICKMTRLDATA t1
ON (t0.FID = t1.FID ) WHERE
t0.FFORMID = 'SP_PickMtrl'
) e1 ON e0.FID = e1.FID
INNER JOIN (
SELECT t0.FMATERIALID AS FMATERIALID,t0.FNUMBER AS FNumber,t0_L.FNAME AS FName FROM T_BD_MATERIAL t0 LEFT JOIN
T_BD_MATERIAL_L t0_L ON (t0.FMATERIALID=t0_L.FMATERIALID AND t0_L.FLocaleId=2052)
) e2 ON e1.FMATERIALID = e2.FMATERIALID
INNER JOIN (
SELECT t0.FMATERIALID AS FMATERIALID,t4.FCATEGORYID AS FCategoryID,t4.FBASEUNITID AS FBaseUnitId FROM T_BD_MATERIAL t0 LEFT JOIN t_BD_MaterialBase t4
ON (t0.FMATERIALID = t4.FMATERIALID )
) e3 ON e2.FMATERIALID = e3.FMATERIALID
INNER JOIN (
SELECT t0.FCATEGORYID AS FCATEGORYID,t0.FNUMBER AS FNumber,t0_L.FNAME AS FName FROM T_BD_MATERIALCATEGORY t0 inner JOIN
T_BD_MATERIALCATEGORY_L t0_L ON (t0.FCATEGORYID=t0_L.FCATEGORYID AND t0_L.FLocaleId=2052) where t0_L.FName in('原材料-包材'
)
) e4 ON e3.FCATEGORYID = e4.FCATEGORYID
INNER JOIN (
SELECT t0.FUNITID AS FUNITID,t0.FNUMBER AS FNumber,t0_L.FNAME AS FName FROM T_BD_UNIT t0 LEFT JOIN
T_BD_UNIT_L t0_L ON (t0.FUNITID=t0_L.FUNITID AND t0_L.FLocaleId=2052)
) e5 ON e3.FBASEUNITID = e5.FUNITID
)e6
-- where e6.F_TXBE_AREA>0 and F_TXBE_AREA<1
)e7
)e8
where 1=1 {1} {2}
""").format(tableName,whereOrgs,whereMat);
#raise Exception(sql);#可以通过此方法弹出Sql语句进行调试验证
DBUtils.Execute(this.Context,sql);#执行SQL，将报表数据写入临时表
#报表关闭触发，通常在此处清理报表过程产生的临时表
def CloseReport():
this.DropTempTable();
双击进入相关单据(表单插件）：
import clr
clr.AddReference("System")
clr.AddReference("System.Core")
clr.AddReference("Kingdee.BOS")
clr.AddReference("Kingdee.BOS.Core")
clr.AddReference("Kingdee.BOS.DataEntity")
clr.AddReference("Kingdee.BOS.ServiceHelper")
from System import *
from System.Collections.Generic import *
from System.ComponentModel import *
from Kingdee.BOS.Core.Bill import *
from Kingdee.BOS.Core.DynamicForm import *
from Kingdee.BOS.Core.DynamicForm.PlugIn.Args import *
from Kingdee.BOS.Core.Metadata import *
from Kingdee.BOS.Core.Metadata.FormElement import *
from Kingdee.BOS.Core.Permission import *
from Kingdee.BOS.Core.Report import *
from Kingdee.BOS.Core.Report.PlugIn import *
from Kingdee.BOS.Core.Report.PlugIn.Args import *
from Kingdee.BOS.Core.SqlBuilder import *
from Kingdee.BOS.Orm.DataEntity import *
from Kingdee.BOS.ServiceHelper import *
from Kingdee.BOS.Resource import *
#单元格双击事件
#简单账表 表单不会触发EntityRowDoubleClick事件，用此事件代替
def CellDbClick(Args):
Args.Cancel=True;#取消事件，若二开标准报表，可以此取消标准功能自带的双击事件
row=Args.CellRowIndex;#双击序号，从1开始
fldKey=Args.Header.FieldName;#双击单元格的字段名
#获取当前单元格的值
reportModel=this.Model;
tab=reportModel.DataSource;
value=("{0}").format(tab.Rows[row-1][fldKey]);#也可以传其他字段名，即可获取其他字段值
msg=("点击了第{0}行的[{1}],{1}值={2}").format(row,fldKey,value);
#this.View.ShowMessage(msg);
fbillNo = str(this.View.GetCurrentRowValue("FBILLNO")) #获取点击行的单据编号
fid=str(this.View.GetCurrentRowValue("FID")) #获取点击行的FID
#formId = str(this.View.GetCurrentRowValue("TXBE_wuliaolingyong"))
#this.View.ShowMessage(formId);
# fm = MetaDataServiceHelper.GetFormMetaData(this.View.Context, "STK_MisDelivery")
# form = fm.BusinessInfo.GetForm() #获取单据信息（这里是其他出库单）
# queryParam = QueryBuilderParemeter()
# queryParam.FormId = "STK_MisDelivery" #其他出库单唯一标识
# queryParam.SelectItems.Add(SelectorItemInfo("FID"));
# queryParam.FilterClauseWihtKey ="FBillNo ='%s'" %fbillNo #. format("","SKD00000753" #, "SKD00000753");
# # using Kingdee.BOS.ServiceHelper;
# objs = QueryServiceHelper.GetDynamicObjectCollection(this.Context, queryParam);
#sql="select fid from T_STK_MISDELIVERY where fbillno="+fbillNo
parameter =BillShowParameter();
parameter.Status = OperationStatus.EDIT;
if "APP" in fbillNo:
parameter.FormId = "SP_PickMtrl";#简单生产领料单
parameter.PKey = str(fid);
parameter.OpenStyle.ShowType =ShowType.MainNewTabPage;#打开方式，到主界面一个新的页签
#param.ParentPageId = this.View.PageId;//指定ParentPageId，可以实现打开的界面直接拿到父界面的数据 如this.View.ParentFormView.Model.DataObject
this.View.ShowForm(parameter);
else :
parameter.FormId = "STK_MisDelivery";#其他出库单
parameter.PKey = str(fid);
parameter.OpenStyle.ShowType =ShowType.MainNewTabPage;#打开方式，到主界面一个新的页签
#param.ParentPageId = this.View.PageId;//指定ParentPageId，可以实现打开的界面直接拿到父界面的数据 如this.View.ParentFormView.Model.DataObject
this.View.ShowForm(parameter);
超链接联查：
按钮点击事件：
#按钮点击事件
def ButtonClick(e):
key=e.Key.ToUpperInvariant();#按钮标识大写
if(key=="F_TXBE_Button_re5".ToUpperInvariant()):
this.Model.SetItemValueByID("F_BPW_OrgId",0,0);
reportModel=this.Model;#SysReportModel类型，报表数据模型
customFilterObj=reportModel.FilterParameter.CustomFilter;#报表过滤框快捷过滤实体数据包
this.View.ShowMessage(str(customFilterObj))
customFilterObj["F_BPW_OrgId"]=None;#清空过滤数据包中的组织字段
customFilterObj["F_BPW_OrgId_Id"]=0;
this.View.Refresh();#清空组织后刷新，获取所有组织数据
