python树形账表
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.Contracts")
clr.AddReference("Kingdee.BOS.App")
from Kingdee.BOS import *
from Kingdee.BOS.JSON import *
from Kingdee.BOS.Util import *
from Kingdee.BOS.Core import *
from Kingdee.BOS.Core.Report import *
from Kingdee.BOS.Core.Metadata import *
from Kingdee.BOS.App.Data import *
from System import *
from System.Collections.Generic import *
from System.Linq import *
def Initialize():
this.ReportProperty.ReportType = ReportType.REPORTTYPE_TREE;##树形报表
this.ReportProperty.IsGroupSummary = True;
this.ReportProperty.PrimaryKeyFieldName = "FBILLNO";
this.ReportProperty.ReportName = LocaleValue("树形报表");
this.IsCreateTempTableByPlugin = True;##临时表取数
def GetReportHeaders(filters):
header = ReportHeader();
header.AddChild("FBillNo", LocaleValue("单据编号"));
header.AddChild("FNumber", LocaleValue("物料代码"));
header.AddChild("FName", LocaleValue("物料名称"));
header.AddChild("FQty", LocaleValue("采购数量"));
return header;
def BuilderReportSqlAndTempTable(filters,tableName):
sql="""/*dialect*/ select Tab.fid as fid,Tab.FBillNo as FBillNo ,Tab.FPrice as FPrice,Tab.FQty as FQty,Tab.FNumber as FNumber,Tab.FName as Fname,Tab.FNumber_e3 as FNumber_e3,Tab.FName_e3 as FName_e3,Tab.FName_e5 as FName_e5
,Tab.FPrice*Tab.FQty as amount,Tab.FDATE as FDATE
,Tab.bmlfname as bmlfname,Tab.F_TXBE_AREA as F_TXBE_AREA
,Tab.FSTOCKORGID as FStockOrgId
,row_number() over(order by Tab.fid) as FIDENTITYID,(SELECT STUFF((
SELECT top 10 ',' + FBillNo
FROM T_STK_MISDELIVERY
FOR XML PATH('')
), 1, 1, '') AS concatenated_strings) as a INTO {} FROM ({}) Tab""";
strFrom= GetSql(filters);
sqlAll=sql.format(tableName,strFrom);
DBUtils.Execute(this.Context, sqlAll);
def GetTreeNodes(filters):
sql="""/*dialect*/ SELECT FID,FBillNo FROM ({}) Tab group by FID,FBillNo""".format(GetSql(filters))
lst=DBUtils.ExecuteDynamicObject(this.Context, sql);
nodes=List[TreeNode]();
for item in lst:
node=TreeNode();
node.id=item["FBillNo"];
node.text=str(item["FBillNo"]);
nodes.Add(node);
return nodes;
def GetSql(filters):
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
,(SELECT STUFF((
SELECT top 10 ',' + FBillNo
FROM T_STK_MISDELIVERY
FOR XML PATH('')
), 1, 1, '') AS concatenated_strings) as a
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
""";
#if (this.CurrentGroupID!=None and this.CurrentGroupID.Trim()!='' and this.CurrentGroupID !="0"):
if (filters.CurrentGroupID!=None and filters.CurrentGroupID.Trim()!='' and filters.CurrentGroupID !="0" and filters.CurrentGroupID !="-1"):
sql=sql + """ AND fbillno='{}'""".format(str(filters.CurrentGroupID));
#raise Exception(sql)
return sql;
