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 *
#初始化
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("FNUMBER", LocaleValue("单据编号", localEid));
# headers.AddChild("FDATE", LocaleValue("日期", localEid));
headers.AddChild("FNUMBER", LocaleValue("物料编码", localEid));
headers.AddChild("FNAME", LocaleValue("物料名称", localEid));
headers.AddChild("YearOfDate", LocaleValue("年", localEid));
headers.AddChild("Jan",LocaleValue("1月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Feb",LocaleValue("2月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Mar",LocaleValue("3月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Apr",LocaleValue("4月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("May",LocaleValue("5月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Jun",LocaleValue("6月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Jul",LocaleValue("7月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Aug",LocaleValue("8月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Sept",LocaleValue("9月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Oct", LocaleValue("10月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Nov", LocaleValue("11月", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("Dec", LocaleValue("12月", localEid), SqlStorageType.SqlDecimal);
# headers.AddChild("FName_e5", LocaleValue("FQTY", localEid));
#headers.AddChild("FQTY", LocaleValue("月数量", localEid), SqlStorageType.SqlDecimal);
headers.AddChild("yearFQTY", 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*/
DECLARE @current_date DATE = GETDATE();
DECLARE @one_year_ago DATE = DATEADD(YEAR, -1, @current_date);
SELECT *,row_number() over(order by FNUMBER) as FIDENTITYID
,[1] Jan
,[2] Feb
,[3] Mar
,[4] Apr
,[5] May
,[6] Jun
,[7] Jul
,[8] Aug
,[9] Sept
,[10] Oct
,[11] Nov
,[12] Dec
into {0}
FROM (
SELECT wlmonth.FMATERIALID,
wlmonth.FNUMBER,
wlmonth.FNAME,
wlmonth.YearOfDate,
wlmonth.MonthOfDate,
wlmonth.FQTY,
wlyear.yearFQTY
FROM (
SELECT wls.FMATERIALID,
SUM(ISNULL(wls.FQTY, 0)) AS FQTY,
--CONVERT(VARCHAR(6), CONVERT(DATE, wls.FDATE), 112) as YearOfDate,
YEAR(wls.FDATE) as YearOfDate,
MONTH(wls.FDATE) AS MonthOfDate,
wl.FNUMBER,
wll.FNAME
FROM (
SELECT FMATERIALID,
FQTY,
FDate
FROM T_STK_MISDELIVERY qtck
LEFT JOIN T_STK_MISDELIVERYENTRY qtckl ON qtck.FID = qtckl.FID
where FDOCUMENTSTATUS='C' and FDATE>=@one_year_ago
UNION ALL
SELECT FMATERIALID,
FACTUALQTY,
FDate
FROM T_SP_PICKMTRL scll
LEFT JOIN T_SP_PICKMTRLDATA sclll ON scll.FID = sclll.FID
where scll.FDOCUMENTSTATUS='C' and FDATE>=@one_year_ago
) AS wls
LEFT JOIN T_BD_MATERIAL wl ON wls.FMATERIALID = wl.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L wll ON wl.FMATERIALID = wll.FMATERIALID
GROUP BY wls.FMATERIALID, wl.FNUMBER, wll.FNAME,YEAR(wls.FDATE),MONTH(wls.FDATE) --CONVERT(VARCHAR(6), CONVERT(DATE, wls.FDATE), 112)
) AS wlmonth
LEFT JOIN (
SELECT wls.FMATERIALID,
SUM(ISNULL(wls.FQTY, 0)) AS yearFQTY,
YEAR(wls.FDATE) AS YearOfDate,
wl.FNUMBER,
wll.FNAME
FROM (
SELECT FMATERIALID,
FQTY,
FDate
FROM T_STK_MISDELIVERY qtck
LEFT JOIN T_STK_MISDELIVERYENTRY qtckl ON qtck.FID = qtckl.FID
UNION ALL
SELECT FMATERIALID,
FACTUALQTY,
FDate
FROM T_SP_PICKMTRL scll
LEFT JOIN T_SP_PICKMTRLDATA sclll ON scll.FID = sclll.FID
) AS wls
Left JOIN T_BD_MATERIAL wl ON wls.FMATERIALID = wl.FMATERIALID
Left JOIN T_BD_MATERIAL_L wll ON wl.FMATERIALID = wll.FMATERIALID
GROUP BY wls.FMATERIALID, wl.FNUMBER, wll.FNAME, YEAR(wls.FDATE)
) AS wlyear ON wlmonth.FMATERIALID = wlyear.FMATERIALID
) AS source
PIVOT (
MAX(FQTY) FOR MonthOfDate IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
)
) AS pivot_table where 1=1 order by YearOfDate;
;
""").format(tableName);
raise Exception(sql);#可以通过此方法弹出Sql语句进行调试验证
DBUtils.Execute(this.Context,sql);#执行SQL，将报表数据写入临时表
#报表关闭触发，通常在此处清理报表过程产生的临时表
def CloseReport():
this.DropTempTable();
