分页账表显示临时表数据问题
金蝶云·星空
金蝶云社区-黄会兴_yellocomet
黄会兴_yellocomet
0人赞赏了该文章 964次浏览 未经作者许可，禁止转载编辑于2016年04月18日 16:49:42

总部大神
分页账表，跟踪代码不执行BuilderReportSqlAndTempTable（）方法，在这个方法里面我创建了临时表，并且灌入了数据，但是getlist()方法里取不到我临时表里面的数据，导致数据出不来。

代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS;
using Kingdee.BOS.Core.List;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Contracts;
using System.Collections;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.SqlBuilder;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.Util;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Permission;
using Kingdee.BOS.App.Core.BusinessFlow;
using Kingdee.K3.Core;
using Kingdee.BOS.Core.BusinessFlow.ServiceArgs;
using Kingdee.BOS.BusinessEntity.BusinessFlow;
using System.Data;
using Kingdee.BOS.Core.Permission.Objects;
//using Kingdee.BOS.Core.CommonFilter;

namespace MyPlugin.ServicePlugIn
{
public class MySysReportServicePlugin : SysReportBaseService
{
public string StrtableName = "PIFRptDetail";
public override void Initialize()
{
string RptName = "息费台账";
this.ReportProperty.ReportType = ReportType.REPORTTYPE_MOVE;
this.ReportProperty.ReportName = new LocaleValue(RptName.ToString());
//this.ReportProperty.DetailReportId = "Rpt_PIFDetail";
//this.ReportProperty.PrimaryKeyFieldName = "FPAWNNO";
this.ReportProperty.IsGroupSummary =true;
this.ReportProperty.IsUIDesignerColumns = false;
this.IsCreateTempTableByPlugin = true;
this.ReportProperty.IdentityFieldName = "FSort";
}
///


/// 构造取数Sql，取数据填充到临时表：tableName
///
///
///
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
// DataRow dr = this.CacheDataList[filter.CurrentPosition];
// Int64 FFINAID =Convert.ToInt64( dr["FFINAID"]);
//金蝶标准插入临时表
//StringBuilder sb0 = new StringBuilder();
//sb0.AppendFormat("/*dialect*/Create table ");
//sb0.AppendFormat(tableName);
//sb0.AppendFormat("(FSort number(19),");
//sb0.AppendFormat(" FFINAID number(19))");
//string StrSql0 = sb0.ToString();
//DBUtils.Execute(this.Context,StrSql0);

//StringBuilder sb = new StringBuilder();
//sb.AppendFormat(" Insert into ");
//sb.AppendFormat(tableName);
//sb.AppendFormat("(FSort, FFINAID)");
//sb.AppendFormat(" Select row_number() over (order by i.FFINAID) as FSort,");
//sb.AppendFormat(" i.FFINAID From tc_t_interest i ");
//sb.AppendFormat(" Where i.FFINAID=");
//sb.AppendFormat(FFINAID.ToString());
//string StrSql = sb.ToString();
//this.KSQL_SEQ = string.Format(this.KSQL_SEQ, "FSort");
//StrSql = string.Format(StrSql, this.KSQL_SEQ, tableName);
//DBUtils.Execute(this.Context, StrSql);
//StrtableName = tableName;
//金蝶标准插入临时表
//取得过滤条件
//string StrFilter = filter.FilterParameter.FilterString.ToString() ;
//string Str = filter.FilterParameter.FilterRows.ToString();
//DataRow dr = this.CacheDataList[filter.CurrentPosition];
////查询条件字段FNumber： FNumberV 对应 V_BD_SALESMAN_l V
////FNumberO T_ORG_Organizations O FNumberC TC_T_Customer C
//StrFilter = StrFilter.Replace("FNumberV", "V.FNumber");
//StrFilter = StrFilter.Replace("FNumberO", "O.FNumber");
//StrFilter = StrFilter.Replace("FNumberC", "C.FNumber");
//StrFilter = StrFilter.Replace("FYearP", "P.FYear");
//StrFilter = StrFilter.Replace("FPeriodP", "P.FPeriod");
//StrFilter = StrFilter.Replace("N'", "'");
//建表
StringBuilder tb = new StringBuilder();
tb.AppendFormat("/*dialect*/Create table ");
tb.AppendFormat(tableName );
tb.AppendFormat(@"(
FSort number(19) default(0),
FTran number(19) default(0),
FORGID number(19) default(0),
FCUSTID number(19) default(0),
FPAWNNO nvarchar2(20),
FFINAID number(19),
FREQUID number(19),
FDATE date,
FCONSTAR　 date,
FCONEND date,
FIFRA number(28,6) default(0),
FDIFRA number(28,6) default(0),
FPBEGIN number(28,2) default(0),
FPDEB number(28,2) default(0),
FPBAL number(28,2) default(0),
FBUDGETIF number(28,2) default(0),
FIFBEGIN number(28,2) default(0),
FIFDEBIT number(28,2) default(0),
FIFCREDIT number(28,2) default(0),
FIFBAL number(28,2) default(0),
FDIFDAYS number(10) default(0),
FDIFBEGIN number(28,2) default(0),
FDIDEBIT number(28,2) default(0),
FDICREDIT number(28,2) default(0),
FDIBAL number(28,2) default(0)
) ");
string StrSql1 = tb.ToString();
DBUtils.Execute(this.Context, StrSql1.Replace("\r\n", "") );
//建立映射关系表 在ORCAL中建立物理表
////StringBuilder tb1 = new StringBuilder();
////tb1.AppendFormat("/*dialect*/Create table ");
////tb1.AppendFormat(StrtableName);
////tb1.AppendFormat(@"(
//// FSort number(19) default(0),
//// FTran number(19) default(0),
//// FORGID number(19) default(0),
//// FCUSTID number(19) default(0),
//// FPAWNNO nvarchar2(20),
//// FFINAID number(19),
//// FREQUID number(19),
//// FDATE date,
//// FCONSTAR　 date,
//// FCONEND date,
//// FIFRA number(28,6) default(0),
//// FDIFRA number(28,6) default(0),
//// FPBEGIN number(28,2) default(0),
//// FPDEB number(28,2) default(0),
//// FPBAL number(28,2) default(0),
//// FBUDGETIF number(28,2) default(0),
//// FIFBEGIN number(28,2) default(0),
//// FIFDEBIT number(28,2) default(0),
//// FIFCREDIT number(28,2) default(0),
//// FIFBAL number(28,2) default(0),
//// FDIFDAYS number(10) default(0),
//// FDIFBEGIN number(28,2) default(0),
//// FDIDEBIT number(28,2) default(0),
//// FDICREDIT number(28,2) default(0),
//// FDIBAL number(28,2) default(0)
//// ) ");
////string StrSql11 = tb1.ToString();
////DBUtils.Execute(this.Context, StrSql11.Replace("\r\n", ""));
//插入本金变动记录数据
StringBuilder Insb01 = new StringBuilder();
Insb01.AppendFormat("Insert into ");
Insb01.AppendFormat(StrtableName);
Insb01.AppendFormat(@" (Forgid,Fcustid,Fpawnno,Ffinaid,Frequid,Fdate,
Fconstar,Fconend,Fifra,Fdifra,Fbudgetif,
Fpbegin,Fpdeb,Fpbal)
Select Forgid,Fcustid,Fpawnno,Ffinaid,Frequid,FPdate,
Fconstar,Fconend, (Fira+Ffra) as Fifra, (Fdira) as Fdifra,(FBUDGETI+FBUDGETF) as FBUDGETIF,
Fpbegin,Fpdeb,Fpbal
From Tc_t_Principal;
");
string StrSql2 = Insb01.ToString();
DBUtils.Execute(this.Context,StrSql2.Replace("\r\n", ""));
//插入息费变动记录
//建立物理表在SQL中建立好
StringBuilder Insb02 = new StringBuilder();
Insb02.AppendFormat("Insert into ");
Insb02.AppendFormat(StrtableName);
Insb02.AppendFormat(@" (FTran,Forgid,Ffinaid,Fdate,FPBAL,
FIFBEGIN,FIFDEBIT,FIFCREDIT,FIFBAL,
FDIFDAYS,FDIFBEGIN,FDIDEBIT,FDICREDIT,FDIBAL
)
Select 1,i.Forgid,i.Ffinaid,i.FIRdate,i.Fpaggr,
(FIBEGIN+FFBEGIN) as FIFBEGIN,
(FIDEBIT+FFDEB) as FIFDEBIT,
(FICREDIT+ FFCRE) as FIFCREDI,
(FIBAL+FFBAL) as FIFBAL,
i.FDIDAYS ,
(FDIBEGIN+FDFBEGIN) as FDIFBEGIN,
(FDIDEBIT+FDFDEB) as FDIFDEBIT,
(FDICREDIT+ FDFCRE) as FDIFCREDIT,
(FDIBAL+FDFBAL) as FDIFBAL
From Tc_t_Interest i
Inner join Tc_t_FEE f
on i.ffinaid=f.ffinaid;
");
string StrSql3 = Insb02.ToString();
DBUtils.Execute(this.Context, StrSql3.Replace("\r\n", ""));
//修改记录--更新记录 更新计息罚息为1的客户内码 合同开始日期 结束日期(扩号中必须为唯一值
StringBuilder Upsb01 = new StringBuilder();
Upsb01.AppendFormat("/*dialect*/update ");
Upsb01.AppendFormat(StrtableName);
Upsb01.AppendFormat(@" D Set FCUSTID=(select distinct P.FCUSTID from Tc_t_Principal p where P.FFINAID=D.FFINAID)
,FPAWNNO=(select distinct P.FPAWNNO from Tc_t_Principal p where P.FFINAID=D.FFINAID)
,FCONSTAR=(select distinct P.FCONSTAR from Tc_t_Principal p where P.FFINAID=D.FFINAID)
,FCONEND=(select distinct P.FCONEND from Tc_t_Principal p where P.FFINAID=D.FFINAID)
where D.FTRAN=1 and D.FCUSTID=0
");
string StrSql4 = Upsb01.ToString();
DBUtils.Execute(this.Context, StrSql4.Replace("\r\n", ""));
//插入显示记录 插入到金蝶自建临时表
StringBuilder Insb03 = new StringBuilder();
this.KSQL_SEQ = string.Format(this.KSQL_SEQ, "FFINAID");
// insb03.AppendFormat(" Select " + this.KSQL_SEQ + ",FFINAID");
Insb03.AppendFormat( "Insert into ");
Insb03.AppendFormat(tableName);
Insb03.AppendFormat("(");
Insb03.AppendFormat(" FSort,FTran,FORGID,");
Insb03.AppendFormat(" FCUSTID,FPAWNNO,FFINAID,FREQUID,FDATE,FCONSTAR,FCONEND, FIFRA,");
Insb03.AppendFormat(" FDIFRA,FPBEGIN ,FPDEB,FPBAL,FBUDGETIF,FIFBEGIN,FIFCREDIT, FIFBAL,");
Insb03.AppendFormat(" FDIFDAYS,FDIFBEGIN ,FDIDEBIT,FDICREDIT,FDIBAL");
Insb03.AppendFormat(")");
Insb03.AppendFormat(" Select" + this.KSQL_SEQ + " ,FTran ,FORGID ,");
Insb03.AppendFormat(" FCUSTID,FPAWNNO,FFINAID,FREQUID,FDATE,FCONSTAR,FCONEND, FIFRA,");
Insb03.AppendFormat(" FDIFRA,FPBEGIN ,FPDEB,FPBAL,FBUDGETIF,FIFBEGIN,FIFCREDIT, FIFBAL,");
Insb03.AppendFormat(" FDIFDAYS,FDIFBEGIN ,FDIDEBIT,FDICREDIT,FDIBAL");
Insb03.AppendFormat(" From ");
Insb03.AppendFormat(StrtableName);
string StrSql03 = Insb03.ToString();
//StrSql03 = string.Format(StrSql03,this.i,tableName);
DBUtils.Execute(this.Context, StrSql03);
//StringBuilder sb0 = new StringBuilder();
//sb0.AppendFormat("/*dialect*/Create table ");
//sb0.AppendFormat(tableName);
//sb0.AppendFormat(@"(
// FSort number(19) default(0),
// FTran number(19) default(0),
// FORGID number(19) default(0),
// FCUSTID number(19) default(0),
// FPAWNNO nvarchar2(20),
// FFINAID number(19),
// FREQUID number(19),
// FDATE date,
// FCONSTAR　 date,
// FCONEND date,
// FIFRA number(28,6) default(0),
// FDIFRA number(28,6) default(0),
// FPBEGIN number(28,2) default(0),
// FPDEB number(28,2) default(0),
// FPBAL number(28,2) default(0),
// FBUDGETIF number(28,2) default(0),
// FIFBEGIN number(28,2) default(0),
// FIFDEBIT number(28,2) default(0),
// FIFCREDIT number(28,2) default(0),
// FIFBAL number(28,2) default(0),
// FDIFDAYS number(10) default(0),
// FDIFBEGIN number(28,2) default(0),
// FDIDEBIT number(28,2) default(0),
// FDICREDIT number(28,2) default(0),
// FDIBAL number(28,2) default(0)
// ) ");
//string StrSql0 = sb0.ToString();
//DBUtils.Execute(this.Context, StrSql0);

// StringBuilder sb = new StringBuilder();
// //sb.AppendFormat("(FSort, FFINAID)");
// sb.AppendFormat(" create table ");
// sb.AppendFormat(tableName);
// sb.AppendFormat(" as Select row_number() over (order by i.FFINAID) as FSort, FTran ");
// sb.AppendFormat(" From PIFRptDetail i ");
//// sb.AppendFormat(" Where i.FFINAID=");
//// sb.AppendFormat(FFINAID.ToString());
// string StrSql = sb.ToString();
// this.KSQL_SEQ = string.Format(this.KSQL_SEQ, "FSort");
// StrSql = string.Format(StrSql, this.KSQL_SEQ, tableName);
// DBUtils.Execute(this.Context, StrSql);
//
if (CacheDataList == null)
{
DataTable dt = GetList(filter);
if (dt != null && dt.Rows.Count > 0)
{
//titles.AddTitle("FCondition", dt.Rows[0]["flocaleid"].ToString());
// return titles;
}
// return null;
}
//DataRow dr = this.CacheDataList[filter.CurrentPosition];
//titles.AddTitle("FCondition", dr["flocaleid"].ToString());
// DataTable dt = GetList(filter);
// return titles;
DataTable dt1;
string sSQL = "select FFINAID from " + tableName + " group by FFINAID order by FFINAID";
dt1 = DBUtils.ExecuteDataSet(this.Context, sSQL).Tables[0];
// return dt1;
//清理数据库记录与表
//DBUtils.Execute(this.Context, "Truncate Table PIFRptDetail");
//DBUtils.Execute(this.Context, "Drop table PIFRptDetail");
}
//public override List GetSummaryColumnInfo(IRptParams filter)
//{
// List fls = new List();
// Kingdee.BOS.Core.Report.SummaryField fs = new Kingdee.BOS.Core.Report.SummaryField("FFINAID", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM);
// fls.Add(fs);
// return fls;
//}

///


/// 分页报表必须实现的方法，此方法用于为报表提供分页依据。
/// 比如以下示例：分别按语言来对部门分类，也就是说每种语言一个报表，中文的是一个报表、英文的一个报表，繁体的一个
///
///
///
public override DataTable GetList(IRptParams filter)
{
IRptParams ss = null;
// BuilderReportSqlAndTempTable(ss, "AA");
// StrtableName = "AA";
DataTable dt;
string sSQL = "select FFINAID from " + StrtableName + " group by FFINAID order by FFINAID";
dt = DBUtils.ExecuteDataSet(this.Context, sSQL).Tables[0];
// DBUtils.Execute(this.Context, "Drop Table AA");
return dt;
}
///
/// 动态构造列
///
///
///
public override ReportHeader GetReportHeaders(IRptParams filter)
{
ReportHeader header = new ReportHeader();
// FID, FEntryId, 客户编号、当票号、本金、已还本金、应还本金
// 编号
var FFINAID = header.AddChild("FFINAID", new LocaleValue("金蝶序号"));
//var FSORTS = header.AddChild("FSORTS", new LocaleValue("金蝶序号"));
//FSORTS.ColIndex = 0;
//var FNumberV = header.AddChild("FNumberV", new LocaleValue("客户编号"));
//FNumberV.ColIndex = 1;
//var FNameV = header.AddChild("FNameV ", new LocaleValue("客户名称"));
//FNameV.ColIndex = 2;
//var FNumberO = header.AddChild("FNumberO ", new LocaleValue("公司代码"));
//FNumberO.ColIndex = 3;
//var FNameO = header.AddChild("FNameO", new LocaleValue("公司名称"));
//FNameO.ColIndex = 4;
//var FNumberC = header.AddChild("FNumberC", new LocaleValue("客户编号"));
//FNumberC.ColIndex = 5;
//var FNameC = header.AddChild("FNameC", new LocaleValue("客户名称"));
//FNameC.ColIndex = 6;
//var FPAWNNO = header.AddChild("FPAWNNO", new LocaleValue("当票编号"));
//FPAWNNO.ColIndex = 7;
//var FREQNO = header.AddChild("FREQNO", new LocaleValue("申请单号"));
//FREQNO.ColIndex = 8;
//var FCONSTAR = header.AddChild("FCONSTAR", new LocaleValue("合同开始日"));
//FCONSTAR.ColIndex = 9;
//var FCONEND = header.AddChild("FCONEND", new LocaleValue("合同结束日"));
//FCONEND.ColIndex = 10;
//var FCONENDE = header.AddChild("FCONENDE", new LocaleValue("合同执行日"));
//FCONENDE.ColIndex = 11;
//var FPDeb = header.AddChild("FPDeb", new LocaleValue("本金总额"));
//FPDeb.ColIndex = 12;
//var FPDEBYTD = header.AddChild("FPDEBYTD", new LocaleValue("累计偿还"));
//FPDEBYTD.ColIndex = 13;
//var FPBAL = header.AddChild("FPBAL", new LocaleValue("本金余额"));
//FPBAL.ColIndex = 14;
//var FIbegin = header.AddChild("FIbegin", new LocaleValue("月初息费"));
//FIbegin.ColIndex = 15;
//var fiytd = header.AddChild("fiytd ", new LocaleValue("累计应收息费"));
//fiytd.ColIndex = 16;
//var fidebit = header.AddChild("fidebit", new LocaleValue("本次应收息费"));
//fidebit.ColIndex = 17;
//var ficytd = header.AddChild("ficytd", new LocaleValue("累计实付息费"));
//ficytd.ColIndex = 18;
//var ficredit = header.AddChild("ficredit", new LocaleValue("本次实付息费"));
//ficredit.ColIndex = 19;
//var fibal = header.AddChild("fibal", new LocaleValue("息费余额"));
//fibal.ColIndex = 20;
return header;
}
///
/// 设置报表头
///
///
///
public override ReportTitles GetReportTitles(IRptParams filter)
{
ReportTitles titles = new ReportTitles();
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
//取得过滤条件
//string StrFilter1 = filter.FilterParameter.FilterString.ToString();
////string Str = filter.FilterParameter.FilterRows.ToString();
////string StrColumns = filter.FilterParameter.ColumnInfo.ToString();
////DynamicObject customFilter = filter.FilterParameter.CustomFilter;
////string dataByKey = string.Empty;
//this.ReportTitles.AddTitle("FWH", StrFilter1.ToString());
//this.View.ShowMessage(msg, MessageBoxOptions.OK, "", MessageBoxType.Notice)
//this.ReportTitles.AddTitle(StrFilter, StrFilter);
//if (CacheDataList == null)
//{
// DataTable dt = GetList(filter);
// if (dt != null && dt.Rows.Count > 0)
// {
// //titles.AddTitle("FCondition", dt.Rows[0]["flocaleid"].ToString());
// return titles;
// }
// return null;
//}
//DataRow dr = this.CacheDataList[filter.CurrentPosition];
////titles.AddTitle("FCondition", dr["flocaleid"].ToString());
//// DataTable dt = GetList(filter);
return titles;
}
///
/// 设置汇总行，只有显示财务信息时才需要汇总
///
///
///
//public override List GetSummaryColumnInfo(IRptParams filter)
//{
// List summaryList = new List();
// return summaryList;
//}
//取得关键值
protected string GetDataByKey(DynamicObject dy, string key)
{
string str = string.Empty;
if (((dy != null) && (dy[key] != null)) && !string.IsNullOrWhiteSpace(dy[key].ToString()))
{
str = dy[key].ToString();
}
return str;
}
}
}
物理表结构
Create table PIFRptDetail(
FSort number(19) default(0),
FTran number(19) default(0),--0 放贷本金
FORGID number(19) default(0),
FCUSTID number(19) default(0), --客户内码
FPAWNNO nvarchar2(20),--当票号
FFINAID number(19),--资金放贷单内码
FREQUID number(19),--业务申请单内码
FDATE date, --操作日期
FCONSTAR　 date,--合同开始日期
FCONEND date,--合同结束日期
FIFRA number(28,6) default(0),--利率
FDIFRA number(28,6) default(0),--罚息率 FPIR
FPBEGIN number(28,2) default(0),--放贷本金
FPDEB number(28,2) default(0),--本次归还
FPBAL number(28,2) default(0),--本金余额
FBUDGETIF number(28,2) default(0),--预扣利息 --预扣费用
FIFBEGIN number(28,2) default(0),--期初利息
FIFDEBIT number(28,2) default(0),--本次利息
FIFCREDIT number(28,2) default(0),--本次还息
FIFBAL number(28,2) default(0),--利息余额
FDIFDAYS number(10) default(0),--逾期天数
FDIFBEGIN number(28,2) default(0),--罚息期初
FDIDEBIT number(28,2) default(0),--本次罚息
FDICREDIT number(28,2) default(0),--偿还罚息
FDIBAL number(28,2) default(0)--罚息余额
);

推荐阅读

关于单据体显示数据分页问题

请问一下总部老师，单据体显示的数据如何进行分页显示，因为我这边用动态表单显示sql查询出来的数据，但是数据量比较大，不进行分页处理的话展示时速度非常慢。 这么好 …

    文章

    1,118

临时表数据监控：临时表已超过20000张

产品： K3Cloud 7.2 临时表数据监控：临时表已超过20000张 疑问：为什么会产生这么多 临时表？ 没有启动删除临时表服务？

    文章

    2,055

临时表问题

5.0环境，6月补丁 系统产生很多的临时表，系统中的服务正常，而且计划任务也正常，为何不会自动清理？？？ [图片] 看是否是类似情况，如果是 可按照6楼的回复解 …

    文章

    715