用插件创建一个简单账表，插件代码如下，将此插件绑定在简单账表服务器插件中，最后运行效果如下截图，请问如何制作过滤条件框，实现年月过滤此报表
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Kingdee.BOS.Core.Report.PlugIn;
using Kingdee.BOS.Core.Report;
using System.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.CommonFilter;
using Kingdee.BOS.Util;
using Kingdee.BOS;
using Kingdee.BOS.Core.List;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.Msg;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.DataEntity;
namespace ET.K3.BOS.RPT.BIZ.ZYJ.Plugin
{
public class ETReportCommonPARM
{
public string ThisYear = DateTime.Now.Year.ToString(); //本年currentyear
public string LastYear = Convert.ToString(Convert.ToInt32(DateTime.Now.Year.ToString()) - 1); //上年
public string ThisMonth = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString(); //本月currentmonth
public string LastMonth = DateTime.Now.Year.ToString() + "-" + Convert.ToString(Convert.ToInt32(DateTime.Now.Month.ToString()) - 1); //上月
public string[] ReportsCOL = new string[] { "本年计划", "本年累计完成额", "上年累计完成额", "年计划完成比例", "同比增减", "下月计划", "分析" };//定义数组(总业绩报表行)
}
public class ZYJ : SysReportBaseService
{
public override void Initialize()
{
base.Initialize();
base.ReportProperty.IdentityFieldName = "FIDENTITYID"; //顺序字段名
this.ReportProperty.ReportName = new LocaleValue("市场部报表-总业绩", this.Context.UserLocale.LCID);
}
private void CreatingTempTable(string strTempTable)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("/*dialect*/CREATE TABLE {0}(", strTempTable);
sb.AppendFormat("[fidentityid] [bigint] IDENTITY(1,1) NOT NULL,");
sb.AppendFormat(" F总业绩 NVARCHAR(50) DEFAULT '',");
sb.AppendFormat(" F总计 DECIMAL(23,10) DEFAULT 0,");
sb.AppendFormat(" F成套产 DECIMAL(23,10) DEFAULT 0,");
sb.AppendFormat(" F零售配 DECIMAL(23,10) DEFAULT 0,");
sb.AppendFormat(" F备注 NVARCHAR(200) DEFAULT ''");
sb.AppendFormat(" )");
DBUtils.Execute(this.Context, sb.ToString());
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
this.CreatingTempTable(tableName); //创建临时表
ETReportCommonPARM ETComm = new ETReportCommonPARM();
string sSQL = "";
try
{
for (int i = 0; i < ETComm.ReportsCOL.Length - 1; i++) //获取数组定义值
{
sSQL = string.Format(@"insert into {0} (F总业绩) values ('{1}')", tableName, ETComm.ReportsCOL[i]);
sSQL = string.Format(sSQL, this.KSQL_SEQ, tableName);
DBUtils.Execute(this.Context, sSQL);
switch (Convert.ToString(i).ToString())
{
case "0"://本年计划
{
break;
}
case "1"://本年累计完成额
{
sSQL.IsEmpty();
sSQL = string.Format(@"Update {0} set F总计={1},F成套产={2},F零售配={3} where fidentityid = {4} ",
tableName, ZJE(ETComm.ThisYear), CTCPJE(ETComm.ThisYear), LSPJE(ETComm.ThisYear), i + 1);
sSQL = string.Format(sSQL, this.KSQL_SEQ, tableName);
DBUtils.Execute(this.Context, sSQL);
break;
}
case "2"://上年累计完成额
{
sSQL.IsEmpty();
sSQL = string.Format(@"Update {0} set F总计={1},F成套产={2},F零售配={3} where fidentityid = {4} ",
tableName, ZJE(ETComm.LastYear), CTCPJE(ETComm.LastYear), LSPJE(ETComm.LastYear), i + 1);
sSQL = string.Format(sSQL, this.KSQL_SEQ, tableName);
DBUtils.Execute(this.Context, sSQL);
break;
}
case "3"://年计划完成比例
{
break;
}
case "4"://同比增减 （由于上年无数据，故无需计算）
{
if ((ZJE(ETComm.LastYear) != 0) && (CTCPJE(ETComm.LastYear) != 0) && (LSPJE(ETComm.LastYear) != 0))
{
sSQL.IsEmpty();
sSQL = string.Format(@"Update {0} set F总计={1},F成套产={2},F零售配={3} where fidentityid = {4}",
tableName, (ZJE(ETComm.ThisYear) - ZJE(ETComm.LastYear)) / ZJE(ETComm.LastYear), (CTCPJE(ETComm.ThisYear) - CTCPJE(ETComm.LastYear)) / CTCPJE(ETComm.LastYear),
(LSPJE(ETComm.ThisYear) - LSPJE(ETComm.LastYear)) / LSPJE(ETComm.LastYear), i + 1);
sSQL = string.Format(sSQL, this.KSQL_SEQ, tableName);
DBUtils.Execute(this.Context, sSQL);
}
break;
}
case "5"://下月计划
{
break;
}
case "6"://分析
{
break;
}
}
}
}
finally
{
sSQL.IsEmpty();
sSQL = @"select F总业绩,F总计,F成套产,F零售配,F备注 from " + tableName; //获得临时表数据
sSQL = string.Format(sSQL, this.KSQL_SEQ, tableName);
DBUtils.Execute(this.Context, sSQL);
}
}
//获取总金额
public double ZJE(string TheYear)
{
string sSQL = string.Format(@"select sum(TSOEF.FAMOUNT) as JE from t_sal_orderentry_F TSOEF " +
"left join T_SAL_ORDER TSO on TSO.fid=TSOEF.fid " +
"where year(TSO.FDATE) = '{0}'", TheYear);
DynamicObjectCollection dobj = Kingdee.BOS.ServiceHelper.DBServiceHelper.ExecuteDynamicObject(this.Context, sSQL);
string strJE = dobj[0]["JE"].ToString();
double JE = Convert.ToDouble(strJE.ToString());
return JE;
}
//获取成套产品金额
public double CTCPJE(string TheYear)
{
string sSQL = string.Format("select sum(TSOEF.FAMOUNT) as JE from t_sal_orderentry_F TSOEF " +
"left JOIN T_SAL_ORDERENTRY TSOE on TSOEF.FENTRYID =TSOE.FENTRYID " +
"left join T_BD_MATERIAL TBM on TSOE.FMATERIALID=TBM.FMATERIALID " +
"left join T_BD_MATERIALGROUP TBMG on TBM.FMATERIALGROUP=TBMG.FID " +
"left join T_SAL_ORDER TSO on TSO.fid=TSOEF.fid " +
"where left(TBMG.FNUMBER,2) = '01' and year(TSO.FDATE) = '{0}'", TheYear);
DynamicObjectCollection dobj = Kingdee.BOS.ServiceHelper.DBServiceHelper.ExecuteDynamicObject(this.Context, sSQL);
string strJE = dobj[0]["JE"].ToString();
double JE = Convert.ToDouble(strJE.ToString());
return JE;
}
//获取零售产品金额
public double LSPJE(string TheYear)
{
string sSQL = string.Format("select sum(TSOEF.FAMOUNT) as JE from t_sal_orderentry_F TSOEF " +
"left JOIN T_SAL_ORDERENTRY TSOE on TSOEF.FENTRYID =TSOE.FENTRYID " +
"left join T_BD_MATERIAL TBM on TSOE.FMATERIALID=TBM.FMATERIALID " +
"left join T_BD_MATERIALGROUP TBMG on TBM.FMATERIALGROUP=TBMG.FID " +
"left join T_SAL_ORDER TSO on TSO.fid=TSOEF.fid " +
"where (left(TBMG.FNUMBER,2) <> '01' ) and year(TSO.FDATE) = '{0}'", TheYear);
DynamicObjectCollection dobj = Kingdee.BOS.ServiceHelper.DBServiceHelper.ExecuteDynamicObject(this.Context, sSQL);
string strJE = dobj[0]["JE"].ToString();
double JE = Convert.ToDouble(strJE.ToString());
return JE;
}
public override void CloseReport()
{
base.CloseReport();
this.DropTempTable();
}
public override ReportHeader GetReportHeaders(IRptParams filter)
{
ReportHeader header = new ReportHeader();
header.AddChild("fidentityid", new LocaleValue("", this.Context.UserLocale.LCID));
header.AddChild("F总业绩", new LocaleValue("", this.Context.UserLocale.LCID));
header.AddChild("F总计", new LocaleValue("总计", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
header.AddChild("F成套产", new LocaleValue("成套产品", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
header.AddChild("F零售配", new LocaleValue("零售配件", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
header.AddChild("F备注", new LocaleValue("备注", this.Context.UserLocale.LCID));
return header;
}
}
}
[/i]
推荐阅读