简单帐表，过滤条件怎么写？
金蝶云·星空-BOS平台
金蝶云社区-凤凰初生
凤凰初生
1人赞赏了该文章 1,508次浏览 未经作者许可，禁止转载编辑于2017年07月11日 15:23:06

过滤条件怎么做，我下面写的过滤条件不管用 ，好像没有和sql语句联系上，哪位大神看看 帮修改一下，不知道怎么写了

[code]using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using Kingdee.BOS;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Core.Report.PlugIn;
using Kingdee.BOS.Core.Report.PlugIn.Args;
using Kingdee.BOS.Core.List;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Orm.DataEntity;

namespace sql
{

public class DemoSysReport : SysReportBaseService
{
public override void Initialize()
{
// 支持分组汇总
this.ReportProperty.IsGroupSummary = true;
this.ReportProperty.BillKeyFieldName = "名字";
this.ReportProperty.ReportName = new LocaleValue(Kingdee.BOS.Resource.ResManager.LoadKDString("销售提成明细表", "002460030014686", Kingdee.BOS.Resource.SubSystemType.BOS), this.Context.UserLocale.LCID);
//this.ReportProperty.GroupSummaryInfoData.DefaultGroupbyString = "fwname";
//this.ReportProperty.DecimalControlFieldList = new List();
// 标示famount用famount_decimal做精度格式化
//this.ReportProperty.DecimalControlFieldList.Add(new DecimalControlField() { ByDecimalControlFieldName = "famount", DecimalControlFieldName = "famount_decimal" });
}

public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
// 拼接sql
// string sSQL = @"select b.fname as fwname,c.fcustid,d.fname as fcname,a.* ,f.famount,3 as famount_decimal,f.fprice, {0} into {1} from T_SAL_ORDERENTRY a
//left join T_BD_MATERIAL_L b on a.fmaterialid=b.fmaterialid and b.FLOCALEID=2052
//left join T_SAL_ORDER c on a.fid=c.fid
//left join T_BD_CUSTOMER_L d on c.fcustid=d.fcustid and d.FLOCALEID=2052
//left join T_SAL_ORDERENTRY_F f on a.fentryid=f.fentryid";

string sSQL = @"
select e5.FName as 名字,e3.FName as 部门,a1.FNAME as 物料编码,e2.FName as 客户,
(case when e1.提成类型='593deb7716d001' then '正常整单'
when e1.提成类型='593deb9216d003' then '云产品'
when e1.提成类型='593deba316d005' then '云服务'
when e1.提成类型='593debb316d007' then '项目外保费'
when e1.提成类型='59472067bd85d7' then '服务费'
when e1.提成类型='59478edebd864b' then '分公司给予的'
else '其他' end) as 提成类型,
e0.本次收款,e0.收款单号,e0.销售订单号,e1.销售订单价税合计,e1.标准报价,(case e1.销售费用 when 0 then 0 else e0.本次收款/e1.销售订单价税合计 end) as 销售费用比例,
(e0.本次收款/e1.销售订单价税合计)*(case e0.本次收款 when 0 then 0 else e1.销售费用 end) as 销售费用比例金额,
(case e0.本次收款 when 0 then 0 else e1.销售费用 end) as 销售费用,e1.折扣率,

(case when e1.提成类型='59472067bd85d7' and 折扣率>=1 then 0.14
when e1.提成类型='59472067bd85d7' and 折扣率>0.25 and 折扣率<0.31 then 0.06
when e1.提成类型='59472067bd85d7' and 折扣率>=0.31 and 折扣率<0.4 then 0.07
when e1.提成类型='59472067bd85d7' and 折扣率>=0.4 and 折扣率<0.45 then 0.08
when e1.提成类型='59472067bd85d7' and 折扣率>=0.45 and 折扣率<0.5 then 0.085
when e1.提成类型='59472067bd85d7' and 折扣率>=0.5 and 折扣率<0.55 then 0.09
when e1.提成类型='59472067bd85d7' and 折扣率>=0.55 and 折扣率<0.6 then 0.095
when e1.提成类型='59472067bd85d7' and 折扣率>=0.6 and 折扣率<0.65 then 0.1
when e1.提成类型='59472067bd85d7' and 折扣率>=0.65 and 折扣率<0.7 then 0.105
when e1.提成类型='59472067bd85d7' and 折扣率>=0.7 and 折扣率<0.75 then 0.11
when e1.提成类型='59472067bd85d7' and 折扣率>=0.75 and 折扣率<0.8 then 0.115
when e1.提成类型='59472067bd85d7' and 折扣率>=0.8 and 折扣率<0.85 then 0.12
when e1.提成类型='59472067bd85d7' and 折扣率>=0.85 and 折扣率<0.9 then 0.125
when e1.提成类型='59472067bd85d7' and 折扣率>=0.9 and 折扣率<0.95 then 0.13
when e1.提成类型='59472067bd85d7' and 折扣率>=0.95 and 折扣率<1 then 0.135
--整单
when e1.提成类型='593deb7716d001' and 折扣率>0.25 and 折扣率<0.31 then 0.01
when e1.提成类型='593deb7716d001' and 折扣率>=0.31 and 折扣率<0.4 then 0.02
when e1.提成类型='593deb7716d001' and 折扣率>=0.4 and 折扣率<0.45 then 0.03
when e1.提成类型='593deb7716d001' and 折扣率>=0.45 and 折扣率<0.5 then 0.035
when e1.提成类型='593deb7716d001' and 折扣率>=0.5 and 折扣率<0.55 then 0.04
when e1.提成类型='593deb7716d001' and 折扣率>=0.55 and 折扣率<0.6 then 0.045
when e1.提成类型='593deb7716d001' and 折扣率>=0.6 and 折扣率<0.65 then 0.05
when e1.提成类型='593deb7716d001' and 折扣率>=0.65 and 折扣率<0.7 then 0.055
when e1.提成类型='593deb7716d001' and 折扣率>=0.7 and 折扣率<0.75 then 0.06
when e1.提成类型='593deb7716d001' and 折扣率>=0.75 and 折扣率<0.8 then 0.065
when e1.提成类型='593deb7716d001' and 折扣率>=0.8 and 折扣率<0.85 then 0.07
when e1.提成类型='593deb7716d001' and 折扣率>=0.85 and 折扣率<0.9 then 0.075
when e1.提成类型='593deb7716d001' and 折扣率>=0.9 and 折扣率<0.95 then 0.08
when e1.提成类型='593deb7716d001' and 折扣率>=0.95 and 折扣率<1 then 0.085
when e1.提成类型='593deb7716d001' and 折扣率>=1 then 0.09
when e1.提成类型='593deb9216d003' then 0.2
when e1.提成类型='593debb316d007' then 0.2
when e1.提成类型='593deba316d005' then 0.06
when e1.提成类型='59478edebd864b' then 0
else 0 end) as 提成率,
((e0.本次收款-(isnull(e1.销售费用,0)*(e0.本次收款/e1.销售订单价税合计)))*(case
when e1.提成类型='59472067bd85d7' and 折扣率>=1 then 0.14
when e1.提成类型='59472067bd85d7' and 折扣率>0.25 and 折扣率<0.31 then 0.06
when e1.提成类型='59472067bd85d7' and 折扣率>=0.31 and 折扣率<0.4 then 0.07
when e1.提成类型='59472067bd85d7' and 折扣率>=0.4 and 折扣率<0.45 then 0.08
when e1.提成类型='59472067bd85d7' and 折扣率>=0.45 and 折扣率<0.5 then 0.085
when e1.提成类型='59472067bd85d7' and 折扣率>=0.5 and 折扣率<0.55 then 0.09
when e1.提成类型='59472067bd85d7' and 折扣率>=0.55 and 折扣率<0.6 then 0.095
when e1.提成类型='59472067bd85d7' and 折扣率>=0.6 and 折扣率<0.65 then 0.1
when e1.提成类型='59472067bd85d7' and 折扣率>=0.65 and 折扣率<0.7 then 0.105
when e1.提成类型='59472067bd85d7' and 折扣率>=0.7 and 折扣率<0.75 then 0.11
when e1.提成类型='59472067bd85d7' and 折扣率>=0.75 and 折扣率<0.8 then 0.115
when e1.提成类型='59472067bd85d7' and 折扣率>=0.8 and 折扣率<0.85 then 0.12
when e1.提成类型='59472067bd85d7' and 折扣率>=0.85 and 折扣率<0.9 then 0.125
when e1.提成类型='59472067bd85d7' and 折扣率>=0.9 and 折扣率<0.95 then 0.13
when e1.提成类型='59472067bd85d7' and 折扣率>=0.95 and 折扣率<1 then 0.135
--整单
when e1.提成类型='593deb7716d001' and 折扣率>0.25 and 折扣率<0.31 then 0.01
when e1.提成类型='593deb7716d001' and 折扣率>=0.31 and 折扣率<0.4 then 0.02
when e1.提成类型='593deb7716d001' and 折扣率>=0.4 and 折扣率<0.45 then 0.03
when e1.提成类型='593deb7716d001' and 折扣率>=0.45 and 折扣率<0.5 then 0.035
when e1.提成类型='593deb7716d001' and 折扣率>=0.5 and 折扣率<0.55 then 0.04
when e1.提成类型='593deb7716d001' and 折扣率>=0.55 and 折扣率<0.6 then 0.045
when e1.提成类型='593deb7716d001' and 折扣率>=0.6 and 折扣率<0.65 then 0.05
when e1.提成类型='593deb7716d001' and 折扣率>=0.65 and 折扣率<0.7 then 0.055
when e1.提成类型='593deb7716d001' and 折扣率>=0.7 and 折扣率<0.75 then 0.06
when e1.提成类型='593deb7716d001' and 折扣率>=0.75 and 折扣率<0.8 then 0.065
when e1.提成类型='593deb7716d001' and 折扣率>=0.8 and 折扣率<0.85 then 0.07
when e1.提成类型='593deb7716d001' and 折扣率>=0.85 and 折扣率<0.9 then 0.075
when e1.提成类型='593deb7716d001' and 折扣率>=0.9 and 折扣率<0.95 then 0.08
when e1.提成类型='593deb7716d001' and 折扣率>=0.95 and 折扣率<1 then 0.085
when e1.提成类型='593deb7716d001' and 折扣率>=1 then 0.09
when e1.提成类型='593deb9216d003' then 0.2
when e1.提成类型='593debb316d007' then 0.2
when e1.提成类型='593deba316d005' then 0.06
when e1.提成类型='59478edebd864b' then 0
else 0 end)) as 销售提成 ,e0.FDATE as 日期, {0} into {1} from (
-- 提成类型 593deb7716d001 是 正常整单 593deb9216d003 是云产品 593deba316d005 云服务 593debb316d007项目外保费
-- 593e3d5b71d6ab 无销售提成 59472067bd85d7服务费 59478edebd864b 是分公司给予的 0%
--业务员分录
SELECT T1.FEntryId AS FEntryId,t1.FSTAFFID AS FStaffId FROM T_BD_OPERATORENTRY t1
)e4
INNER JOIN(
--员工 临时
SELECT t0.FID AS FID,t1.FSTAFFID AS FStaffDetails,t1.FENTRYID FROM T_HR_EMPINFO t0 LEFT JOIN T_BD_STAFFTEMP t1
ON (t0.FID=t1.FID)
)e6 ON e4.FStaffId=e6.FStaffDetails
INNER JOIN(
--员工姓名
SELECT t0.FID AS FID,t0_L.FNAME AS FName ,t0.fnumber FROM T_HR_EMPINFO t0 LEFT JOIN
T_HR_EMPINFO_L t0_L ON (t0.FID=t0_L.FID AND t0_L.FLocaleId=2052)
)e5 ON e6.FID=e5.FID
left join(
select f_abc_name as 员工代码,f_abc_buzhu as 补助,F_abc_baoxian as 保险代扣,F_abc_jichu as 基础工资 from abc_t_jcgzb
)e8 on e8.员工代码=e6.FID
left join (
select t1.FREALRECAMOUNTFOR as 本单实收金额,t1.FSALEERID as 销售员,t3.FAFTTAXTOTALAMOUNT as 应收款,t3.FREALRECAMOUNT as 本次收款,t1.FID As FID,t1.FBILLNO AS 收款单号,
t1.FDATE,t3.FSRCBILLNO as 销售订单号,t1.FSALEDEPTID ,t3.FMATERIALID,t1.FCONTACTUNIT from T_AR_RECEIVEBILL t1 inner join T_AR_RECEIVEBILLSRCENTRY t3 on t1.FID=t3.FID

)e0 ON e0.销售员=e4.FEntryId

left join (
select t3.FMATERIALID as 物料编码,FBILLNO as 销售订单编号,t3.F_ABC_AMOUNT as 标准报价,t2.FBILLALLAMOUNT as 销售订单价税合计,FBILLAMOUNT as 不含税合计,t1.F_ABC_AMOUNT4 as 销售费用,F_ABC_DISCOUNTRATE1 as 折扣率,t1.F_ABC_TCLX as 提成类型 from T_SAL_ORDER t1 inner join T_SAL_ORDERFIN t2 on t1.fid=t2.fid inner join T_SAL_ORDERENTRY t3 on t1.FID=t3.FID

--select FALLAMOUNT,FMATERIALID as 物料编码, t2.F_ABC_AMOUNT1 as 销售费用,t1.FBILLNO as FBILLNO ,t1.F_ABC_DISCOUNTRATE1 as 折扣率,t2.fseq,FALLAMOUNT as 销售订单价税合计,t1.F_ABC_TCLX as 提成类型 from T_SAL_ORDER t1 inner join T_SAL_ORDERENTRY t2 on t1.FID=t2.FID inner join T_SAL_ORDERENTRY_F t3 on t1.FID=t3.FID and t2.FENTRYID=t3.FENTRYID where FALLAMOUNT!=0
)e1 on e1.销售订单编号=e0.销售订单号 and e1.物料编码=e0.FMATERIALID
--关联不对 应该关联物料代码 现在关联的是应收款金额， 一个订单多次收款的情况下就不准了
left join (
select t1.FMATERIALID ,FNUMBER,t2.FNAME from t_bd_material t1 inner join t_bd_material_l t2 on t1.FMATERIALID=t2.FMATERIALID
)a1 on e1.物料编码=a1.FMATERIALID
left JOIN(
--客户，客户多语言
SELECT t0.FCUSTID AS FCUSTID,t0.FSETTLETYPEID AS FSETTLETYPEID,t0.FCUSTTYPEID AS FCustTypeid,t0_L.FNAME AS FName FROM T_BD_CUSTOMER t0 LEFT JOIN
T_BD_CUSTOMER_L t0_L ON(t0.FCUSTID=t0_L.FCUSTID AND t0_L.FLocaleid=2052)
) e2 ON e0.FCONTACTUNIT=e2.FCUSTID

INNER JOIN(
--部门表，部门多语言
SELECT t0.FDEPTID AS FDEPTID,t0_L.FNAME AS FName FROM T_BD_DEPARTMENT t0 LEFT JOIN
T_BD_DEPARTMENT_L t0_L ON(t0.FDEPTID=t0_L.FDEPTID AND t0_L.FLocaleid=2052)
) e3 ON e0.FSALEDEPTID=e3.FDEPTID
left join(
SELECT month(F_abc_date) as 任务月份,year(F_abc_date) as 任务年份,F_ABC_AMOUNT AS 任务收款金额,F_ABC_INSIDEAMOUNT AS 任务内部结算金额,F_ABC_EMP,F_ABC_AMOUNT2 as 奖金,F_ABC_AMOUNT3 as 罚扣,F_ABC_AMOUNT4 as 其他金额 FROM abc_t_TaskBill t1 inner join abc_t_Cust_TaskBillEntry t2 on t1.fid=t2.fid
) e9 on e5.FID=e9.F_ABC_EMP and e9.任务月份=(select month(e0.FDATE)) and e9.任务年份 =(select year(e0.FDATE))
--where year(e0.fdate)=#FY# and month(e0.fdate)=#FM#
";

//base.BuilderReportSqlAndTempTable(filter, tableName);

//DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
////获取过滤条件里面的物料编码
//string FCUSTMATNO = Convert.ToString(((DynamicObject)dyFilter["F_abc_Name"])["FName"]);

// 设置分页排序
string name = this.Context.UserName;
KSQL_SEQ = string.Format(KSQL_SEQ, "e5.FName asc");//e5.FName
// KSQL_SEQ = string.Format(KSQL_SEQ, "a.FID asc");
sSQL = string.Format(sSQL, this.KSQL_SEQ, tableName);

DBUtils.Execute(this.Context, sSQL);
}

public override ReportTitles GetReportTitles(IRptParams filter)
{
ReportTitles titles = new ReportTitles();
// TODO：从filter中获取具体的固定过滤条件，设置到tilte中，用于报表条件展示
//titles.AddTitle("FText", "55555");F_abc_Name
//titles.AddTitle("FLabel", "66666");

titles.AddTitle("F_abc_Name", this.Context.UserName);
titles.AddTitle("F_abc_Year", "");
titles.AddTitle("F_abc_Month", "");
return titles;
}
///


/// 设置汇总列信息
///
///
///
public override List GetSummaryColumnInfo(IRptParams filter)
{
List summarys = new List();
// summarys.Add(new Kingdee.BOS.Core.Report.SummaryField("FQty", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
summarys.Add(new Kingdee.BOS.Core.Report.SummaryField("本次收款", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
summarys.Add(new Kingdee.BOS.Core.Report.SummaryField("销售费用", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
summarys.Add(new Kingdee.BOS.Core.Report.SummaryField("销售提成", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));

// 单价求平均
//summarys.Add(new Kingdee.BOS.Core.Report.SummaryField("fprice", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.AVERAGE));
// 金额汇总
// summarys.Add(new Kingdee.BOS.Core.Report.SummaryField("famount", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
return summarys;
}
///


/// 构建动态列
///
///
///
public override ReportHeader GetReportHeaders(IRptParams filter)
{
// TODO:fentryid,fid,fbaseunitqty,fmaterialid,fbomid,fqty
ReportHeader header = new ReportHeader();
header.AddChild("部门", new LocaleValue(Kingdee.BOS.Resource.ResManager.LoadKDString("部门", "002460030014689", Kingdee.BOS.Resource.SubSystemType.BOS), this.Context.UserLocale.LCID));
header.AddChild("名字", new LocaleValue(Kingdee.BOS.Resource.ResManager.LoadKDString("名字", "002460030014690", Kingdee.BOS.Resource.SubSystemType.BOS), this.Context.UserLocale.LCID));
header.AddChild("物料编码", new LocaleValue(Kingdee.BOS.Resource.ResManager.LoadKDString("物料编码", "002460030014695", Kingdee.BOS.Resource.SubSystemType.BOS), this.Context.UserLocale.LCID));
header.AddChild("客户", new LocaleValue(Kingdee.BOS.Resource.ResManager.LoadKDString("客户", "002460030014698", Kingdee.BOS.Resource.SubSystemType.BOS), this.Context.UserLocale.LCID));
header.AddChild("提成类型", new LocaleValue(Kingdee.BOS.Resource.ResManager.LoadKDString("提成类型", "002460030014701", Kingdee.BOS.Resource.SubSystemType.BOS), this.Context.UserLocale.LCID));
header.AddChild("本次收款", new LocaleValue(Kingdee.BOS.Resource.ResManager.LoadKDString("本次收款", "002460030014704", Kingdee.BOS.Resource.SubSystemType.BOS), this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);//数字类型 需加SqlStorageType.SqlDecimal
header.AddChild("收款单号", new LocaleValue(Kingdee.BOS.Resource.ResManager.LoadKDString("收款单号", "002460030014690", Kingdee.BOS.Resource.SubSystemType.BOS), this.Context.UserLocale.LCID));
header.AddChild("销售订单号", new LocaleValue(Kingdee.BOS.Resource.ResManager.LoadKDString("销售订单号", "002460030014690", Kingdee.BOS.Resource.SubSystemType.BOS), this.Context.UserLocale.LCID));
return header;
}
}
}
[/code]

推荐阅读

简单帐表的过滤条件怎么显示出来

[图片] 显示影藏列怎么放出来，求解，好像是插件里面某个属性还是可以在bosIDE里面设置。。。。。 这个页签中设置完数据，它就自动显示出来了 ,选中页签里边的 …

    文章

    1,709

简单帐表如何设置过滤条件？

是通过插件设置还是前台bos设置，如果是插件，有没有类似的dome可供参考？ 不太明白楼主意思，在简单帐表的过滤窗口-快捷页签有一些过滤条件的字段，在条件页签也 …

    文章

    2,586

简单帐表的“条件”过滤如何实现？

之前参考 丁老师的帖子[链接]https://vip.kingdee.com/article/37120 做了一个简单帐表，实现了 “快捷”页签 的过滤，但是 …

    文章

    1,793