代码:
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 Kingdee.K3.SCM.Sal.Report.PlugIn;
using Kingdee.K3.CRM.App.Core;
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.Resource;
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 MarketingCostSummaryRpt
{
public class Class1 : SysReportBaseService
{
private string m_strSourceDataTable;
public string filterOrgList;
//初始化
public override void Initialize()
{
base.Initialize();
base.ReportProperty.IdentityFieldName = "FID";
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
base.ReportProperty.ReportName = new LocaleValue("营销费用汇总表", this.Context.UserLocale.LCID);
this.ReportProperty.PrimaryKeyFieldName = "FID";
this.ReportProperty.IsGroupSummary = true;
base.ReportProperty.IsUIDesignerColumns = true;//列头是否BOSIDE设置
base.ReportProperty.SimpleAllCols = false;//是否锁定帐表表格列
this.InitTables();
base.Initialize();
}
//主方法
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//base.BuilderReportSqlAndTempTable(filter, tableName);
this.CreateTempTable();//创建临时表;
this.InsertSourceDate(filter);//从数据库取数,放到创建的临时表中;
this.SetRptBosTable(filter,tableName);//将临时表中的数据放到tablename中用于展示;
//不包含DeleteTable的定义,先注释掉
//base.DeleteTable();//删除临时表
}
//如果账表模型在设计时,没有配置账表单据体字段信息,则通过此方法设置账表列头字段信息
public override Kingdee.BOS.Core.Report.ReportHeader GetReportHeaders(Kingdee.BOS.Core.Report.IRptParams filter)
{
return base.GetReportHeaders(filter);
}
//准备账表表头信息
public override ReportTitles GetReportTitles(IRptParams filter)
{
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
base.GetReportTitles(filter);
return this.BuilderTitle(filter);
}
private ReportTitles BuilderTitle(IRptParams filter)
{
ReportTitles titles = new ReportTitles();
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
if (customFilter != null)
{
string blank = string.Empty;
//部门范围
string DeptFromInfo = string.Empty;
string DeptToInfo = string.Empty;
DynamicObject DeptFrom = customFilter["F_xxq_DeptFrom"] as DynamicObject;
string DeptFromNumber = string.Empty;
string DeptFromName = string.Empty;
if (DeptFrom != null)
{
DeptFromNumber = GetDataByKey(DeptFrom, "NUMBER");
DeptFromName = GetDataByKey(DeptFrom, "NAME");
}
DynamicObject DeptTo = customFilter["F_xxq_DeptTo"] as DynamicObject;
string DeptToNumber = string.Empty;
string DeptToName = string.Empty;
if (DeptTo != null)
{
DeptToNumber = GetDataByKey(DeptTo, "NUMBER");
DeptToName = GetDataByKey(DeptTo, "NAME");
}
if (DeptFrom != null || DeptTo != null)
{ blank = "--"; }
else
{
blank = "全部";
}
if (DeptFrom != null)
{
DeptFromInfo = string.Format("{0}({1})", DeptFromNumber, DeptFromName);
}
if (DeptTo != null)
{
DeptToInfo = string.Format("{0}({1})", DeptToNumber, DeptToName);
}
titles.AddTitle("F_xxq_DeptTitle", string.Format("{0}{1}{2}", DeptFromInfo, blank, DeptToInfo));
blank = string.Empty;
}
return titles;
}
//创建临时表
public void CreateTempTable()
{
string createSql = "(\r\nF_xxq_Text3 bigint not null default 0,F_xxq_Text4 nvarchar(30) not null default '',F_xxq_Text5 nvarchar(30) not null default ''\r\n)";
this.m_strSourceDataTable = DBUtils.CreateSessionTemplateTable(base.Context, "TM_CRM_MarketingCostSumSOURCE", createSql);
//不包含deleteTables的定义,先注释掉
//base.deleteTables.Add(this.m_strSourceDataTable);
}
//从数据库取数,放到创建的临时表中;
private void InsertSourceDate(IRptParams filter)
{
int lCID = base.Context.UserLocale.LCID;
StringBuilder builder = new StringBuilder();
builder.AppendFormat("\r\nINSERT INTO {0}(F_xxq_Text3,F_xxq_Text4,F_xxq_Text5)\r\nselect \r\na.FID F_xxq_Text3,\r\nISNULL(e.FNAME,' ') F_xxq_Text4,\r\nISNULL(g.FNAME,' ') F_xxq_Text5\r\n from T_CRM_Opportunity a \r\nleft join V_BD_SALESMAN b on a.FBEMPID=b.fid \r\nleft join T_BD_STAFF c on b.FSTAFFID=c.FSTAFFID \r\nleft join T_HR_EMPINFO d on c.FPERSONID=d.FPERSONID \r\nleft join T_HR_EMPINFO_L e on d.FID=e.FID \r\nleft join T_ORG_POST f on c.FPOSTID=f.FPOSTID \r\nleft join T_ORG_POST_L g on f.FPOSTID=g.FPOSTID \r\n\r\n", this.m_strSourceDataTable);
}
//将临时表中的数据放到tablename中用于展示;
private void SetRptBosTable(IRptParams filter, string tableName)
{
string str = "F_xxq_Text3";
if (!string.IsNullOrEmpty(filter.FilterParameter.SortString))
{
str = str + filter.FilterParameter.SortString;
}
if (string.IsNullOrWhiteSpace(str))
{
base.KSQL_SEQ = string.Format(base.KSQL_SEQ, "F_xxq_Text3");
}
else
{
base.KSQL_SEQ = string.Format(base.KSQL_SEQ, string.Format("{0}", str));
}
string str2 = string.Format("SELECT F_xxq_Text3,F_xxq_Text4,F_xxq_Text5\r\n ,{0} INTO {1}\r\n from {2}", base.KSQL_SEQ, tableName, this.m_strSourceDataTable);
List
str2
};
DBUtils.ExecuteBatch(base.Context, sqlArray, 1);
}
protected string GetDataByKey(DynamicObject dy, string key)
{
if (((dy != null) && (dy[key] != null)) && !string.IsNullOrWhiteSpace(dy[key].ToString()))
{
return dy[key].ToString();
}
else
{
return string.Empty;
}
}
}
}
推荐阅读