**续上节**
[二开学习1-1简单账表](https://wenku.my7c.com/article/586141411205003776?productLineId=1&lang=zh-CN)
#### 1.设置字段颜色
模拟单据列表界面设置单据编号字段颜色，为后续点击编号做联查
##### 1.1新建表单插件，编写代码
```
base.OnFormatRowConditions(args);
FormatCondition No_FC = new FormatCondition()
{
//前景色.蓝色
ForeColor = "#0000FF",
//条件,只设置单据编号列的颜色，方便显示做点击联查
Key = "FBillNo"
};
//加载颜色FRow_FC
args.FormatConditions.Add(No_FC);
```
##### 1.2编译插件，注册在简单账表的表单插件后看效果
 
#### 2.实现点击联查
##### 2.1延用1.1的表单插件，继续编写代码
```
//重写表格单元格双击
public override void CellDbClick(CellEventArgs Args)
{
base.CellDbClick(Args);
//获取行FID GetCurrentRowValue
string FID = ((ISysReportViewService)this.View).GetCurrentRowValue("FID").ToString();
//传单据
BillShowParameter para = new BillShowParameter();
para.OpenStyle.ShowType = ShowType.Modal;
//单据唯一标识（我这边是采购订单）
para.FormId = "PUR_PurchaseOrder";
//样式 view 查看
para.Status = OperationStatus.VIEW;
//打开哪张单据,必须FID
para.PKey = FID;
para.ParentPageId = this.View.ParentFormView.PageId;
this.View.ShowForm(para);
}
//重写实体行双击事件
public override void EntityRowDoubleClick(Kingdee.BOS.Core.DynamicForm.PlugIn.Args.EntityRowClickEventArgs e)
{
base.EntityRowDoubleClick(e);
}
```
##### 2.2重新编译插件后看效果
#### 3.合并单据头数据
模拟系统列表的查询合并效果 
##### 3.1基于原插件修改
基于原来的服务插件继续修改即可，我是简便就改了如下红色部分，当然还有其他的方法可以，这个也是可以的：[通过修改临时表实现简单账表单据头数据合并](https://wenku.my7c.com/article/238689146502692864?productLineId=1&lang=zh-CN) 
##### 3.2重新编译插件后看效果，至此还算完善的一张简单账表已经完成了
#### 4.总结
自己捋了一下简单账表的几个步骤
##### 4.1新建一张空白的简单账表
##### 4.2设计简单报表
报表抬头区域拖入的字段可用于显示获取快捷过滤框的值（当然这个是由代码实现的）
下面的区域是用于显示报表的列，这个可以由用户自行选择手动拖入字段绑定标识，或者由代码动态构建生成报表列，个人建议是代码构建
##### 4.3新增一个公共过滤框，新建过滤窗口：新建->继承->BOS->应用框架-公共过滤
##### 4.4设计过滤框
用户自行拖入需要过滤的字段，对于条件高级排序显示隐藏列完全可自行按需配置，这里切记快捷过滤拖入的过滤字段绑定的标识必须和代码里保持一致，配置完成后将过滤框的唯一标识绑定到简单账表的过滤窗口
##### 4.5构建报表数据源
打开数据库工具，可自行创造逻辑关联取数，初学者建议不要把sql搞的太复杂，主要是为了找到学习账表开发的感觉
为了给自己找感觉，我也没有写的太复杂，简单的写了个嵌套查询模拟
##### 4.6编写服务插件
具体插件的话社区有很多模板，我是根据林大的修改的，各位大佬不嫌弃的话我可以附上我的代码
```
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Core.SqlBuilder;
using Kingdee.BOS.Orm.DataEntity;
using System.ComponentModel;
namespace K3.SCM.PUR.Report.Test
{
[Description("采购订单明细表练习表-赖赖")]
[Kingdee.BOS.Util.HotUpdate]
public class CGDDMXBLX : SysReportBaseService
{
public override void Initialize()
{ //初始化
base.Initialize();
// 简单账表类型：普通、树形、分页
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
//通过插件创建
this.IsCreateTempTableByPlugin = false;
//是否分组汇总
this.ReportProperty.IsGroupSummary = true;
// 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
this.ReportProperty.PrimaryKeyFieldName = "FID";
//
this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
}
//获取过滤条件信息(构造单据信息)
public override ReportTitles GetReportTitles(IRptParams filter)
{
ReportTitles reportTitles = new ReportTitles();
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
if (customFilter != null)
{
string multiOrgnNameValues = this.GetMultiOrgnNameValues(customFilter["F_VSZN_OrgId"].ToString());
string startValue = (customFilter["F_VSZN_OrderStartDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["F_VSZN_OrderStartDate"]).ToString("yyyy-MM-dd");
string endValue = (customFilter["F_VSZN_OrderEndDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["F_VSZN_OrderEndDate"]).ToString("yyyy-MM-dd");
string BillNo = (Convert.ToString(customFilter["F_VSZN_BillNo"]) == "") ? string.Empty : customFilter["F_VSZN_BillNo"].ToString();
string baseDataNameValue3 = this.GetBaseDataNameValue(customFilter["F_VSZN_SupplierId"] as DynamicObjectCollection);
string baseDataNameValue = this.GetBaseDataNameValue(customFilter["F_VSZN_FmaterialId"] as DynamicObjectCollection);
reportTitles.AddTitle("F_VSZN_OrgId", multiOrgnNameValues);
reportTitles.AddTitle("F_VSZN_Date", startValue + "-" + endValue);
reportTitles.AddTitle("F_VSZN_BillNo", BillNo);
reportTitles.AddTitle("F_VSZN_SupplierId", baseDataNameValue3);
reportTitles.AddTitle("F_VSZN_FmaterialId", baseDataNameValue);
}
return reportTitles;
}
//组织名称
private string GetMultiOrgnNameValues(string orgIdStrings)
{
List<string> list = new List<string>();
string result = string.Empty;
if (orgIdStrings.Trim().Length > 0)
{
IQueryService service = Kingdee.BOS.Contracts.ServiceFactory.GetService<IQueryService>(base.Context);
QueryBuilderParemeter para = new QueryBuilderParemeter
{
FormId = "ORG_Organizations",
SelectItems = SelectorItemInfo.CreateItems("FNAME"),
FilterClauseWihtKey = string.Format(" FORGID IN ({0}) AND FLOCALEID={1}", orgIdStrings, base.Context.UserLocale.LCID)
};
DynamicObjectCollection dynamicObjectCollection = service.GetDynamicObjectCollection(base.Context, para, null);
foreach (DynamicObject current in dynamicObjectCollection)
{
list.Add(current["FNAME"].ToString());
}
if (list.Count > 0)
{
result = string.Join(",", list.ToArray());
}
}
return result;
}
//基础资料名称
private string GetBaseDataNameValue(DynamicObjectCollection dyobj)
{
string name = "";
foreach (DynamicObject dynbj in dyobj)
{
if (dynbj != null || !dynbj.DynamicObjectType.Properties.Contains("Name"))
{
DynamicObject dynbj2 = (DynamicObject)dynbj[2];
name = name + ",'" + dynbj2["Name"].ToString() + "'";
}
}
if (name.Length > 0)
{
name = name.Substring(1, name.Length - 1);
}
return name;
}
//设置单据列
public override ReportHeader GetReportHeaders(IRptParams filter)
{
ReportHeader header = new ReportHeader();
ReportHeader headerch = new ReportHeader();
// 动态创建列标题
//ColIndex:索引从0开始显示列顺序
//Mergeable:合并
//IsHyperlink:超链接
this.ReportProperty.PrimaryKeyFieldName = "FBILLNO";
var FBILLNO = header.AddChild("FBILLNO", new LocaleValue("订单编号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
FBILLNO.ColIndex = 0;
FBILLNO.Mergeable = true;
FBILLNO.IsHyperlink = true;
var FORGFNAME = header.AddChild("FORGFNAME", new LocaleValue("采购组织", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
FORGFNAME.ColIndex = 1;
FORGFNAME.Mergeable = true;
var FSUPPLIERFNUMBER = header.AddChild("FSUPPLIERFNUMBER", new LocaleValue("供应商编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
FSUPPLIERFNUMBER.ColIndex = 2;
FSUPPLIERFNUMBER.Mergeable = true;
var FSUPPLIERFNAME = header.AddChild("FSUPPLIERFNAME", new LocaleValue("供应商名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
FSUPPLIERFNAME.ColIndex = 3;
FSUPPLIERFNAME.Mergeable = true;
var DEPARTMENT = header.AddChild("DEPARTMENT", new LocaleValue("采购部门", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
DEPARTMENT.ColIndex = 4;
DEPARTMENT.Mergeable = true;
var FBUYER = header.AddChild("FBUYER", new LocaleValue("采购员", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
FBUYER.ColIndex = 5;
FBUYER.Mergeable = true;
var FDATE = header.AddChild("FDATE", new LocaleValue("采购日期", this.Context.UserLocale.LCID), SqlStorageType.SqlDatetime);
FDATE.ColIndex = 6;
FDATE.Mergeable = true;
var FSEQ = header.AddChild("FSEQ", new LocaleValue("行号", this.Context.UserLocale.LCID), SqlStorageType.SqlInt);
FSEQ.ColIndex = 7;
var FMATERIALFNUMBER = header.AddChild("FMATERIALFNUMBER", new LocaleValue("物料编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
FMATERIALFNUMBER.ColIndex = 8;
var FMATERIALFNAME = header.AddChild("FMATERIALFNAME", new LocaleValue("物料名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
FMATERIALFNAME.ColIndex = 9;
var FQTY = header.AddChild("FQTY", new LocaleValue("订单&采购数量", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
FQTY.ColIndex = 10;
var FPRICE = header.AddChild("FPRICE", new LocaleValue("订单&含税单价", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
FPRICE.ColIndex = 11;
var FAMOUNT = header.AddChild("FAMOUNT", new LocaleValue("订单&价税合计", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
FAMOUNT.ColIndex = 12;
var FALLQTY = header.AddChild("FALLQTY", new LocaleValue("入库&累计入库数量", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
FALLQTY.ColIndex = 13;
var FLOSSQTY = header.AddChild("FLOSSQTY", new LocaleValue("入库&剩余入库数量", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
FLOSSQTY.ColIndex = 14;
var FRETURNQTY = header.AddChild("FRETURNQTY", new LocaleValue("入库&入库退料数量", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
FRETURNQTY.ColIndex = 15;
return header;
}
//创建临时报表
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
}
//设置汇总列信息
public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
{
var result = base.GetSummaryColumnInfo(filter);
result.Add(new SummaryField("FQTY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
result.Add(new SummaryField("FAMOUNT", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
result.Add(new SummaryField("FALLQTY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
result.Add(new SummaryField("FLOSSQTY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
result.Add(new SummaryField("FRETURNQTY", Kingdee.BOS.Core.Enums.BOSEnums.Enu_SummaryType.SUM));
return result;
}
//构建order by语句
protected override string BuilderTempTableOrderBySQL(IRptParams filter)
{
string OrderBy = "";
//排序
string datasort = Convert.ToString(filter.FilterParameter.SortString);
if (datasort != "")
{
OrderBy = datasort;
}
else
{
OrderBy = " FID ";
}
return OrderBy;
}
//构建单据select 和字段
protected override string BuilderSelectFieldSQL(IRptParams filter)
{
string sqlstr = @"select FID,
FPURCHASEORGID,
FORGFNAME,
FBILLNO,
FSUPPLIERFNUMBER,
FSUPPLIERFNAME,
FDATE,
DEPARTMENT,
FBUYER,
FENTRYID,
FSEQ,
FMATERIALFNUMBER,
FMATERIALFNAME,
FQTY,
FPRICE,
FAMOUNT,
FALLQTY,
FLOSSQTY,
FRETURNQTY";
return sqlstr;
}
//构建from where语句
protected override string BuilderFromWhereSQL(IRptParams filter)
{
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
StringBuilder strwhere = new StringBuilder();
strwhere.AppendLine(@" from
(
SELECT
DD.组织内码 FPURCHASEORGID,
DD.采购组织 FORGFNAME,
DD.单据内码 FID,
DD.订单编号 FBILLNO,
DD.供应商编码 FSUPPLIERFNUMBER,
DD.供应商 FSUPPLIERFNAME,
DD.采购日期 FDATE,
DD.采购部门 DEPARTMENT,
DD.采购员 FBUYER,
DD.分录内码 FENTRYID,
DD.行号 FSEQ,
DD.物料编码 FMATERIALFNUMBER,
DD.物料名称 FMATERIALFNAME,
DD.采购数量 FQTY,
DD.含税单价 FPRICE,
DD.价税合计 FAMOUNT,
RK.累计入库数量 FALLQTY,
RK.剩余入库数量 FLOSSQTY,
RK.入库退料数量 FRETURNQTY
FROM
(
SELECT
TPR.FPURCHASEORGID 组织内码,
TOOL.FNAME 采购组织,
TPR.FID 单据内码,
TPR.FBILLNO 订单编号,
TBS.FNUMBER 供应商编码,
TBSL.FNAME 供应商,
TPR.FDATE 采购日期,
TBDL.FNAME 采购部门,
VBBL.FNAME 采购员,
TPRE.FENTRYID 分录内码,
TPRE.FSEQ 行号,
TBM.FNUMBER 物料编码,
TBML.FNAME 物料名称,
TPRE.FQTY 采购数量,
TPRF.FTAXPRICE 含税单价,
TPRF.FALLAMOUNT 价税合计
FROM
T_PUR_POORDER TPR
LEFT JOIN T_PUR_POORDERENTRY TPRE ON TPR.FID = TPRE.FID
LEFT JOIN T_PUR_POORDERENTRY_F TPRF ON TPRE.FENTRYID = TPRF.FENTRYID
LEFT JOIN t_BD_Supplier TBS ON TPR.FSUPPLIERID = TBS.FSupplierId
LEFT JOIN t_BD_Supplier_L TBSL ON TBS.FSupplierId = TBSL.FSupplierId AND TBSL.FLOCALEID = '2052'
LEFT JOIN t_org_organizations_l TOOL ON TPR.FPURCHASEORGID = TOOL.FORGID AND TOOL.FLOCALEID = '2052'
LEFT JOIN T_BD_DEPARTMENT_L TBDL ON TPR.FPURCHASEDEPTID = TBDL.FDEPTID AND TBDL.FLOCALEID = '2052'
LEFT JOIN V_BD_BUYER VBB ON TPR.FPURCHASERID = VBB.FID
LEFT JOIN V_BD_BUYER_L VBBL ON VBB.FID = VBBL.FID AND VBBL.FLocaleId = '2052'
LEFT JOIN T_BD_MATERIAL TBM ON TPRE.FMATERIALID = TBM.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L TBML ON TBM.FMATERIALID = TBML.FMATERIALID AND TBML.FLOCALEID = '2052'
)DD
LEFT JOIN (
--采购入库单
SELECT
RKD.订单分录内码,
RKD.订单数量,
RKD.累计入库数量,
CASE
WHEN RKD.累计入库数量 > RKD.订单数量 THEN '0'
ELSE RKD.订单数量 - RKD.累计入库数量
END AS 剩余入库数量,
RKTLD.入库退料数量
FROM
(
SELECT
TSIE.FENTRYID 入库分录内码,
TSIE.FPOORDERENTRYID 订单分录内码,
SUM(TSIE.FREALQTY) 累计入库数量,
TPRE.FQTY 订单数量
FROM
T_STK_INSTOCKENTRY TSIE
LEFT JOIN T_PUR_POORDERENTRY TPRE ON TSIE.FPOORDERENTRYID = TPRE.FENTRYID
GROUP BY
TSIE.FENTRYID,
TSIE.FPOORDERENTRYID,
TPRE.FQTY
)RKD
LEFT JOIN (
SELECT
SUM(TPME.FRMREALQTY) 入库退料数量,
TPMLK.FSID 入库分录内码
FROM
T_PUR_MRBENTRY TPME
LEFT JOIN T_PUR_MRBENTRY_LK TPMLK ON TPME.FENTRYID = TPMLK.FENTRYID
GROUP BY
TPMLK.FSID
)RKTLD ON RKD.入库分录内码 = RKTLD.入库分录内码
)RK ON DD.分录内码 = RK.订单分录内码
) t1 Where 1=1 ");
//组织
string org = string.IsNullOrWhiteSpace(customFilter["F_VSZN_OrgId"].ToString())
? " " : string.Format(" AND FPURCHASEORGID IN ({0}) ", Convert.ToString(customFilter["F_VSZN_OrgId"]));
strwhere.AppendLine(org);
//日期
string startValue = (customFilter["F_VSZN_OrderStartDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["F_VSZN_OrderStartDate"]).ToString("yyyy-MM-dd");
string endValue = (customFilter["F_VSZN_OrderEndDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["F_VSZN_OrderEndDate"]).ToString("yyyy-MM-dd");
strwhere.AppendLine(string.Format(" AND FDATE>='{0}' and FDATE<='{1}' ", startValue, endValue));
//单据编号
string BillNo = (Convert.ToString(customFilter["F_VSZN_BillNo"]) == "") ? " " : " AND FBILLNO='" + customFilter["F_VSZN_BillNo"].ToString() + "' ";
strwhere.AppendLine(BillNo);
//供应商
string baseSuppName = this.GetBaseDataNameValue(customFilter["F_VSZN_SupplierId"] as DynamicObjectCollection);
string SuppName = string.IsNullOrWhiteSpace(baseSuppName)
? " " : string.Format(" AND FSUPPLIERFNAME IN ({0}) ", baseSuppName);
strwhere.AppendLine(SuppName);
//物料
string baseDataNameValue = this.GetBaseDataNameValue(customFilter["F_VSZN_FmaterialId"] as DynamicObjectCollection);
string MaterailName = string.IsNullOrWhiteSpace(baseDataNameValue)
? " " : string.Format(" AND FMATERIALFNAME IN ({0}) ", baseDataNameValue);
strwhere.AppendLine(MaterailName);
string text2 = string.IsNullOrWhiteSpace(filter.FilterParameter.FilterString) ? " " : " AND " + filter.FilterParameter.FilterString.Replace("{ts", "").Replace("}", "");
strwhere.AppendLine(text2);
return strwhere.ToString();
}
}
}
```
##### 4.7编译插件注册发布
又更新啦，期待大佬下一次的更新
