金蝶云星空获取简单账表数据写入临时表中

#引入clr运行库
import clr
#添加对cloud插件开发的常用组件的引用
clr.AddReference('System')
clr.AddReference('System.Data')
clr.AddReference('Kingdee.BOS')
clr.AddReference('Kingdee.BOS.Core')
clr.AddReference('Kingdee.BOS.App')
clr.AddReference('Kingdee.BOS.App.Core')
clr.AddReference('Kingdee.BOS.ServiceHelper')
clr.AddReference('Kingdee.K3.FIN.ServiceHelper')
clr.AddReference('Kingdee.K3.FIN.HS.ServiceHelper')
clr.AddReference('Kingdee.BOS.Contracts')
clr.AddReference('Kingdee.BOS.Model')
#导入cloud基础库中的常用实体对象（分命名空间导入，不会递归导入）
from Kingdee.BOS import *
from Kingdee.BOS.JSON import *
from Kingdee.BOS.Util import *
from Kingdee.BOS.Core import *
from Kingdee.BOS.Core.Bill import *
from Kingdee.BOS.Core.DynamicForm.PlugIn import *
from Kingdee.BOS.Core.DynamicForm.PlugIn.ControlModel import *
from Kingdee.BOS.Core.List import*
from Kingdee.BOS.Core.List.PlugIn import *
from Kingdee.BOS.Core.SqlBuilder import *
from Kingdee.BOS.Core.Metadata import *
from System import *
from System.Data import *
from Kingdee.BOS.App.Data import *
from System.Collections.Generic import List
from Kingdee.BOS.ServiceHelper import *
from Kingdee.K3.FIN.HS.ServiceHelper import *
from Kingdee.K3.FIN.ServiceHelper import *
#下面是获取简单账表数据需要的引用
from Kingdee.BOS.Contracts import *
from Kingdee.BOS.ServiceHelper import *
from Kingdee.BOS.Core.Report import *
from Kingdee.BOS.Model.ReportFilter import *
from Kingdee.BOS.App.Core import *
from Kingdee.BOS.Core.SqlBuilder import *

tempTabs=None;
def getRptData(ctx,RptFormId,filterFormID,SchemeId,startDate,endDate,billIdList):
sysReporSservice=SysReportService();
#permissionService=ServiceFactory.GetPermissionService(ctx);
filterMetadata=FormMetaDataCache.GetCachedFilterMetaData(ctx);#加载字段比较条件元数据。
reportMetadata=FormMetaDataCache.GetCachedFormMetaData(ctx, RptFormId);#加载账表元数据。
reportFilterMetadata=FormMetaDataCache.GetCachedFormMetaData(ctx, filterFormID);#加载账表过滤条件元数据。
reportFilterServiceProvider=reportFilterMetadata.BusinessInfo.GetForm().GetFormServiceProvider();
filterModel=SysReportFilterModel();
filterModel.SetContext(ctx, reportFilterMetadata.BusinessInfo, reportFilterServiceProvider);
filterModel.FormId=reportFilterMetadata.BusinessInfo.GetForm().Id;
filterModel.FilterObject.FilterMetaData=filterMetadata;
filterModel.InitFieldList(reportMetadata,reportFilterMetadata);
SchemeList=list(s for s in filterModel.GetSchemeList() if(s.Id == SchemeId) );
if(len(SchemeList)<= 0):
error=("过滤方案[{0}]不存在，或者不存在该过滤方案的权限！").format(SchemeId);
raise Exception(error);
return None;
#过滤方案ID，可通过该SQL语句查询得到：SELECT * FROM T_BAS_FILTERSCHEME
entity=filterModel.Load(SchemeId);
filterPara=filterModel.GetFilterParameter();
p=RptParams();
p.FormId=reportFilterMetadata.BusinessInfo.GetForm().Id;
p.StartRow=1;
p.EndRow=Int32.MaxValue;#StartRow和EndRow是报表数据分页的起始行数和截至行数，一般取所有数据，所以EndRow取int最大值。
p.FilterParameter=filterPara;
p.FilterFieldInfo=filterModel.FilterFieldInfo;
filterStr=p.FilterParameter.FilterString;
p.FilterParameter.CustomFilter["StartDate"]=startDate;
p.FilterParameter.CustomFilter["EndDate"]=endDate;
p.FilterParameter.FilterString=(" FBillId in ({0}) ").format(str.Join(",",billIdList));
#error=("[{0}]{1}").format(p.FilterParameter.FilterString,filterStr);
#raise Exception(error);
#p.BaseDataTempTable.AddRange(permissionService.GetBaseDataTempTable(ctx, reportMetadata.BusinessInfo.GetForm().Id));
#修改过滤方案中的参数值
#acctStartFld=reportFilterMetadata.BusinessInfo.GetField("FSTARTBALANCE");
#acctId=LoadPKValue(acctStartFld.LookUpObject.FormId,"1602");
#acctObj=BusinessDataServiceHelper.LoadSingle(ctx, acctId, acctStartFld.RefFormDynamicObjectType);#基础资料字段数据包
#p.FilterParameter.CustomFilter["STARTBALANCE_Id"]=acctId;
#p.FilterParameter.CustomFilter["STARTBALANCE"]=acctObj;
#acctStartFld.DynamicProperty.SetValue(p.FilterParameter.CustomFilter,acctObj);
#acctStartFld.RefIDDynamicProperty.SetValue(p.FilterParameter.CustomFilter,acctId);
#raise Exception(JsonUtil.Serialize(p.FilterParameter.CustomFilter));
tab=sysReporSservice.GetData(ctx, reportMetadata.BusinessInfo, p);
ServiceFactory.CloseService(sysReporSservice);
#ServiceFactory.CloseService(permissionService);
return tab;
#列表菜单点击事件,列表菜单点击开始时触发
#此事件也是很常用的,可以在此事件中取消菜单的点击事件
#使用时一定要判断菜单标识!!!
def BarItemClick(e):
global tempTabs;
key=e.BarItemKey.ToUpperInvariant();
if(key=="TWKH_tbBGetExpenseAmt".ToUpperInvariant()):
#e.Cancel=True;#取消菜单的点击，可以阻止后续功能的触发,可完成一些简单校验
selectedRowsInfo=this.ListView.SelectedRowsInfo;#列表勾选的数据集
if(selectedRowsInfo.Count<=0):
this.View.ShowWarnningMessage("未选择任何行！");
return;
entityKey=selectedRowsInfo[0].EntryEntityKey;
billIDs=selectedRowsInfo.GetPrimaryKeyValues();
entryIDs=selectedRowsInfo.GetEntryPrimaryKeyValues();
msg=("[{0}]").format(entityKey);
whereBill=(" AND en.FID in ({0}) ").format(str.Join(",",billIDs));
if(entityKey=="FEntityDetail"):
whereBill=(" AND en.FENTRYID in ({0}) ").format(str.Join(",",entryIDs));
sql=("""/*dialect*/select *
from
(
select distinct h.FID,h.FBILLNO,h.FDATE
from t_AR_receivableEntry en
inner join t_AR_receivableEntry_LK lk on lk.FENTRYID=en.FENTRYID
inner join T_SAL_OUTSTOCK h on h.FID=lk.FSBILLID
where en.FSOURCETYPE='SAL_OUTSTOCK' and h.FDOCUMENTSTATUS='C' {0}
union all
select distinct h.FID,h.FBILLNO,h.FDATE
from t_AR_receivableEntry en
inner join t_AR_receivableEntry_LK lk on lk.FENTRYID=en.FENTRYID
inner join T_SAL_RETURNSTOCK h on h.FID=lk.FSBILLID
where en.FSOURCETYPE='SAL_RETURNSTOCK'and h.FDOCUMENTSTATUS='C' {0}
) ysd
order by ysd.FDATE """).format(whereBill);
ds=DBServiceHelper.ExecuteDataSet(this.Context,sql);
AllRows=ds.Tables[0].Rows;
if(AllRows.Count<=0):
this.View.ShowWarnningMessage("勾选的应收单上游单据不是[销售出库]或者[销售退货]，无法获取费用项目明细！");
return;
billIds=List[str]();
for dr in AllRows:
billID=str(dr["FID"]);
billIds.Add(billID);
beginDate=AllRows[0]["FDATE"];
endDate=AllRows[AllRows.Count-1]["FDATE"];
SchemeId="64b2c0e2a55200";
sysTab=getRptData(this.Context,"HS_SALESLIST","HS_SALESLISTFILTER",SchemeId,beginDate,endDate,billIds);
if(sysTab.Rows.Count<=0):
this.View.ShowWarnningMessage("勾选的应收单暂未获取到任何费用项目明细数据，可能还未进行该期间的存货核算！");
return;
flds=List[str]();
for fld in sysTab.Columns:
fldName=fld.ColumnName;
dataType=fld.DataType.Name.ToUpperInvariant();
sqlDataType="VARCHAR(MAX)";
if("INT" in dataType):
sqlDataType="int";
elif("DECIMAL" in dataType):
sqlDataType="decimal(23, 10)";
elif("DATETIME" in dataType):
sqlDataType="datetime";
fldInfo=("{0} {1} ").format(fldName,sqlDataType);
flds.Add(fldInfo);
#tempTabName=sysTab.TableName;
dbService=DBService();
tempTabs=dbService.CreateTemporaryTableName(this.Context, 1);
sysTab.TableName=tempTabs[0];
createTempTabSql=("/*dialect*/create table {0} ( {1} ) ").format(tempTabs[0],str.Join(",",flds));
DBUtils.Execute(this.Context,createTempTabSql);
try:
DBUtils.BulkInserts(this.Context, sysTab);
#msg=("[{0}]---{1}").format(tempTabs[0],JsonUtil.Serialize(sysTab));
#this.View.ShowMessage(msg);
subEnSql=("""/*dialect*/select
FSeq=cast(row_Number() Over(partition by en.FENTRYID Order by tab.FIDENTITYID) AS int),
FEntryID=Cast(en.FEntryID as int),
FDetailID=Cast(0 as int),
F_TWKH_EXPID=Cast(expen.FEXPID as int),
F_TWKH_EXPAMT=cast(tab.FAmount AS decimal(13,10))
from t_AR_receivableEntry en
inner join t_AR_receivableEntry_LK lk on lk.FENTRYID=en.FENTRYID
inner join {1} tab
inner join T_BD_EXPENSE expen on expen.FNumber=tab.FEXPENSEID
on tab.FBillFormId=en.FSOURCETYPE and tab.FBillId=lk.FSBILLID and tab.FBillEntryId=lk.FSID
where en.FSOURCETYPE in ('SAL_RETURNSTOCK','SAL_OUTSTOCK') {0} """).format(whereBill,tempTabs[0]);
ds=DBServiceHelper.ExecuteDataSet(this.Context,subEnSql);
subTab=ds.Tables[0];
dataCount=subTab.Rows.Count;
if(dataCount<=0):
this.View.ShowWarnningMessage("勾选的应收单暂未匹配到任何费用项目明细数据，可能还未进行该期间的存货核算！");
clearTempTable();
return;
subTab.TableName="TWKH_t_YSD_ExpenseSubEntry";
newFIDs = DBServiceHelper.GetSequenceInt64(this.Context, subTab.TableName,dataCount);
i=0;
for dr in subTab.Rows:
FDetailId=newFIDs[i];
dr["FDetailID"]=FDetailId;
i=i+1;
deleteOldDataSql=("""/*dialect*/delete from {0}
where FEntryID in
(
select distinct en.FEntryID
from t_AR_receivableEntry en
where en.FSOURCETYPE in ('SAL_RETURNSTOCK','SAL_OUTSTOCK') {1}
) """).format(subTab.TableName,whereBill);
DBUtils.Execute(this.Context,deleteOldDataSql);
DBUtils.BulkInserts(this.Context, subTab);
clearTempTable();
msg=("获取数据成功,共选中[{0}]条应收单数据，获取到费用项目明细数据共[{1}]条！[{2}]").format(selectedRowsInfo.Count,dataCount,tempTabs[0]);
this.View.ShowMessage(msg);
except Exception as e:
error=("发生异常了[{1}]:{0}").format(e,tempTabs[0]);
this.View.ShowWarnningMessage(error);
finally:
clearTempTable();
def clearTempTable():
global tempTabs;
if(tempTabs is not None and tempTabs.Length>0):
#dbService=DBService();
#该删除服务实际不会立即删除临时表，只是将可以删除的临时表打上标记，然后依赖一个清理临时表的执行计划，定期删除过期的临时表。
#dbService.DeleteTemporaryTableName(this.Context, tempTabs);
for temp in tempTabs:
deleteTempSql=("""/*dialect*/IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'{0}') AND type IN (N'U'))
drop table {0} """).format(temp);#立即删除临时表
DBUtils.Execute(this.Context,deleteTempSql);