首页 > 编程语言 >python简单账表（包括联查）

python简单账表（包括联查）

时间：2024-06-18 16:00:10浏览次数：21  
标签：FNumber python BOS Kingdee t0 FName 账表 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 *
#初始化
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);


 

标签：FNumber,python,BOS,Kingdee,t0,FName,账表,import,联查
From： https://www.cnblogs.com/woshinige/p/18254525

相关文章

  • 关于几种语言(c#,php,python,javascript)字符串的gzip压缩与解压的整理
    背景介绍因为一直在处理restfulAPI，给移动端提供的数据需要考虑流量问题，优先考虑就是压缩现有的字符串，然后再考虑业务逻辑方面的减少流量。鉴于找这些资料也花了不少时间，所以整理了这篇文章，留作纪念。参考网址PHP与C#的压缩与解压http://www.oschina.net/question/2265205_181......
  • python函数声明（参数/返回值注释）和三个双引号用法
     1#python的"""三个双引号两种用法：（1）多行注释（2）定义多行字符串2deff1(ham:42,eggs:int='spam')->"Nothingtoseehere":3print("函数注释",f1.__annotations__)#函数注释{'ham':42,'eggs':<cl......
  • （slam工具）6 python四元数转化旋转矩阵
       importnumpyasnpfromscipy.spatial.transformimportRotationasRimportpyprojfrompyprojimportProj,transform#0.0169380355232107080.58455146147157355-0.488705791564092830.64744060819180593-129342.747563395343469822.8668770161534369......
  • IPython 使用技巧整理
    IPython使用技巧整理IPython是一种强大的交互式Pythonshell，提供了许多增强功能，适合数据科学、机器学习和科学计算等多个领域。以下是一些常用的IPython使用技巧。目录基础功能魔法命令扩展和插件与JupyterNotebook的集成调试与错误处理性能优化基础功能1.自动......
  • mybatis关联查询
    packagecom.xin.pojo;importlombok.AllArgsConstructor;importlombok.Data;importlombok.NoArgsConstructor;importjava.util.List;@Data@AllArgsConstructor@NoArgsConstructorpublicclassUserLogin{privateintid;privateStringusername;......
  • 小于n的最大数 - 贪心算法及证明 - 附python实现
    一、问题描述？    给定一个整数n，并从1~9中给定若干个可以使用的数字，根据上述两个条件，得到每一位都为给定可使用数字的、最大的小于整数n的数。    例如，给定可以使用的数字为{2,3,8}三个数：    给定n=3589，输出3388；给定n=8234，输出8233；…… 二、解......
  • Python - Meta Class
    Aspartofmetaprogramming,ametaclassisoneofthemostimportantconceptsinPython.AClassinPythondefinesthefunctionalityofitsobjectsusingattributesandmethods.Incontrast,ametaclassdefinesthefunctionalityoftheclasses,whereast......
  • python 开发工具IDE 之 thonny
    一、thonny简介    thonny是一款开源免费的pythonIDE(集成开发环境)，其内置python解释器，无需安装python解释器和配置环境变量。下载thonny，安装即可使用，轻量简便，省去python环境安装及配置的烦恼。二、thonny优缺点   优点：简单轻便，免费开源，支持中文且功能不复杂，适......
  • 补充第一天的python学习笔记
    昨天晚上学习到10点左右太困了，没有完成既定目标，迁延一日。补充下昨天的学习内容，算是对第一天学习时的回顾。1.字符集编码(1)utf-8全球通用，一个字节等于8个二进制位，utf-8用于中文占3个字节(2)unicode全球通用，16位二进制以上(3)gbk专为中国人设计的编码，一个文字占2个字节......
  • Fatal error in launcher: Unable to create process using ‘“python.exe“ “\pyt
    1.设置环境变量将pip和python的路径加入环境变量中2.在cmd中，查看是否存在python，pip等3.把应用安装程序中的python.exe和python3.exe关闭4.正常使用详情请看微软的常见问题，链接如下：关于在Windows上使用Python的FAQ|MicrosoftLearn......

赞助商

阅读排行

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

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

Copyright © 2020-2023 IPS99 版权所有 IPS99