#实践案例#巧妙利用自定义SQL视图解决问题的最佳实践

      继上一篇：#使用技巧#走进金蝶云星空后台表，给大家剖析了《单据的表结构与BOS单据的联系》，核心就是以下2点：

• 单据保存/修改写入数据时：可以把单据理解成数据录入的的界面，然后根据实体结构写入数据库表中。

• 单据查询读取数据时候：系统又根据实体表构成从对应的数据库表中读取数据。

       在Get了这上一篇的知识之后，我们就可以马上应用到实践过中。

     接着给大家分享一下利用这个关系，通过构建自定义SQL视图来实现我们遇到的实际问题一些实践案例，给大家提供一些参考思路。

一、业务背景

   整理一些在社区看到如下问题场景：

•     采购订单套打可以添加视图作为新的数据源关联采购订单吗?

•     怎样在费用应付单选单界面中添加采购订单号字段？

•     预算编制台，预算组织如何显示部门全名称
  

•     基础资料类型想引用一个单据的数据有办法吗?
  

•     基础资料开发建立关联关系
  

•     销售合同单据增加附件列表后，如何在销售合同列表可以显示附件的说明字段？或过滤里按附件说明过滤？

•     套打设置供应商联系人，如何只取默认并未禁用的联系人

•     如何实现在即时库存明细表显示预计出的数量

•     应收单上如何配置，能够显示客户的多级分组？

•     手工日记账如何显示凭证信息页签

•     即时库存列表过滤如何按仓位组合值进行过滤？

•     关联下游单据字段在列表显示

•     应付单列表怎么添加对应凭证的科目？

•     想用审核日期+7天，算出的日期成另一字段值，并显示在列表上

•     数据分组如何增加自定义字段，在基础资料物料的 物料分组时，引用自定义字段属性？

•      费用应付单选择销售出库单后，出库单的客户能否携带至费用应付单

•     ............

   针对以上问题，我都会给出解决思路：通过【构建自定义SQL视图+BOS配置】来解决。

   虽然这不是唯一的解决方案，也不一定是最好的解决方案，但这是没有插件开发条件的情况下，最合适的思路。

   那么简单分析一下问题场景，其实都是想做一个事情-----"自动生成数据、自动反写数据、自动同步数据"

• 对于这种只是想自动展示数据、携带数据、自动计算某个值、把单据变成基础资料作为字段选择...等等，

       都可以利用【构建自定义SQL视图+BOS配置】的方案来处理，而且掌握这个用法之后，非常方便。

• 最关键的技术门槛不高，是对于没有插件代码开发技能，但是自己又能熟练操作数据库的小伙伴来说就是一个福音，屡试不爽！

二、解决方案

• 我们设置BOS单据时，可以把表类型设置成视图，表名设置成视图名，那么系统就会自动从视图中取数了，只要视图里面的数据构成满足上一篇文章所讲的实体、字段构成逻辑，系统就能自动取数了。

• 下面就结合上面的实际问题案例，抛砖引玉，提炼出几种案例场景，给大家分享一下自定义SQL视图的妙用。

• 场景1:把单据数据构建成视图基资料

(示例：构建采购订单号，基础资料，供其他单据选择，或者凭证挂核算维度)

①打开BOS，通过继承模板的方式新建一个基础资料：此时切忌，先不要保存单据，否则系统会自动创建物理表！

②先写一个SQL，把需要取的数据构造成视图，视图名:V_CUST_PO。(视图命名规范:"V"开头)

此示例,需构建2个视图，因存在多语言文本字段，还需构建多语言视图，其名称构成必须是"视图名_L"，即：V_CUST_PO_L

注意！构建SQL视图时，应先把模板自带的字段，以及实体主键字段先构建好，再构建业务需求字段，防止忘记，导致功能报错或失败！

有几个实体，就需要构建几个实体的视图，如存在拆分表或者多语言表，也需要同时构建，视图命名规则也要注意！

我这里示例的基础资料只有一个单据头主实体，且存在多语言文本字段，所以构建2个视图，构建视图SQL如下：

--构建视图时,先测试好select查询语句之后，再执行Create View 
--构建视图字段的结构：select 源取数来源字段 AS 基础资料单据实体中对应的字段名

--构建基础资料主实体视图
--Create View V_CUST_PO AS 
select a.FID AS FID, --构建实体主键ID		
a.FBILLNO AS  FNumber,--基础资料编码,取单据编号
a.FDOCUMENTSTATUS AS FDOCUMENTSTATUS,--取采购订单的单据状态作为基础资料的数据状态
'A' AS FFORBIDSTATUS,--禁用状态，写死'A'，即未禁用，也可以根据自己的逻辑构造，例如取单据的作废状态等,状态值要和禁用状态保持一致
a.FSUPPLIERID AS F_PAEZ_SupplierId--把供应商也取过来，作为业务需求字段,基础资料中添加的供应商字段是F_PAEZ_SupplierId
from t_PUR_POOrder a

--构建基础资料主视图的多语言拆分视图，名称字段构建到这个视图中
--Create View V_CUST_PO_L AS
--多语言表也需要有一固定的后台字段，可以参考系统已有的多语言表看看(如物料T_BD_MATERIAL_L)，有哪些必须的后台字段
select a.FID AS FID, --构建实体主键ID，物料的主键是FMATERIALID,我们构建的基础资料主键是FID
a.FID AS FPKID,--这个是多语言表的主键，要保证在这个视图中唯一,我这里只构建中文的数据，所以直接取FID,如确实存在多种语言数据，需要另外构建唯一值
2052 AS FLOCALEID,--语言代码，必须要有，且字段标识不能改，系统会自动根据这个字段识别语言,这里固定中文2052
a.FBILLNO AS  FName--这个基础资料单据中只有一个多语言字段，即名称,字段名是FName,这里也取单据编号
from t_PUR_POOrder a

③把构造好的视图，绑定到单据上：这一步完成之后，才点击保存!

④权限配置，发布基础资料菜单，与二开单据发布一样，比较简单，省略步骤截图，下面看效果。

视图构建的数据应是只读的，应禁止修改、删除，把所有字段锁定，且禁用删除，防止通过视图更新了原始数据，造成系统数据出错！

• 场景2:在已有单据中增加视图实体自动关联显示另外的数据源

• 2.1(添加单据体示例：在采购订单中增加页签显示订单下游的所有入库明细记录)

①写SQL构建新加单据体的视图：V_PO_InstockInfo，由于没使用拆分表和多语言字段，只构建1个视图就可以了

--  一定要先测试好Select语句之后再执行Create View
--Create View V_PO_InstockInfo AS
SELECT   
en.FID,--构建的是单据体,需要构建单据ID,标识数据行属于哪个单据
inLK.FLINKID AS FEntryID,--单据体主键,整数类型,这里取的是入库单LK表的主键,需要唯一且相对固定
ROW_NUMBER() OVER (partition BY h.FID ORDER BY inEn.FSeq ASC) FSeq,--使用开窗函数构建行序号,对每个单据数据单独编号
 inH.FBILLNO AS F_PAEZ_InBillNo,--入库单号
 inEn.FREALQTY AS F_PAEZ_Qty, --入库数量
 inEn.FMATERIALID AS F_PAEZ_MatId--物料ID，物料字段使用的基础资料，所以取出ID就可以了
FROM  
t_PUR_POOrder h /*明细信息*/ INNER JOIN
t_PUR_POOrderEntry en ON h.FID = en.FID /*下游采购入库单数据*/ INNER JOIN
T_STK_INSTOCKENTRY_LK inLK ON inLK.FSID = en.FENTRYID AND inlk.FSBILLID = en.FID INNER JOIN
T_STK_INSTOCKENTRY inEn ON inEn.FENTRYID = inLK.FENTRYID AND 
inLK.FSTABLENAME = 't_PUR_POOrderEntry' INNER JOIN
t_STK_InStock inH ON inH.FID = inEn.FID
WHERE   inH.FDOCUMENTSTATUS = 'C'--只显示已审核的入库数据

②在BOS中打开-扩展采购订单，新增页签，拖入一个单据体，并添加需要显示的字段，字段名与视图中一致

③保存BOS单据，设置单据体字段锁定性、可见性等，前台打开单据看效果。

• 2.2(添加子单据体示例：在生产订单中增加页签显示用料清单数据，方便套打取数等)
  

①在BOS中打开-扩展生产订单，新增一个页签，拖入子单据体(每一行产品对应一个用料清单，所以用子单据体)

②构建SQL视图：V_MO_PPBOM

--  一定要先测试好Select语句之后再执行Create View
--Create View V_MO_PPBOM AS
select 
en.FMOENTRYID AS FENTRYID,--父分录实体主键,可查看父分录实体主键获取字段名,这里是生产订单明细主键
en.FENTRYID AS FDetailID,--子单据体主键,使用用料清单明细的主键
en.FSEQ AS FSeq,--行序号,这里可以直接取用料清单的明细行序号
h.FBILLNO AS FPPBomNum,--用料清单编号
en.FMATERIALID AS F_PAEZ_SubMaterialId,
en.FUNITID AS F_PAEZ_SubUnitID,
en.FMUSTQTY AS F_PAEZ_MustQty,
enQ.FPICKEDQTY AS F_PAEZ_PickQty,
enQ.FNOPICKEDQTY AS F_PAEZ_NotPickQty,
--领料比例,这里直接动态计算,这也是用视图的方便之处
Case when en.FMUSTQTY<>0 then Cast(enQ.FPICKEDQTY/en.FMUSTQTY AS decimal(18,2)) else 0 end AS F_PAEZ_PickRate
from T_PRD_PPBOM h
inner join T_PRD_PPBOMENTRY en on en.FID=h.FID
inner join T_PRD_PPBOMENTRY_Q enQ on enQ.FENTRYID=en.FENTRYID

③把构建好的SQL视图，设置到子单据头的表名上

④上述配置都做好无误之后，保存BOS单据，前台打开生产订单查看效果.

• 2.3(添加子单据头示例：即时库存自动构建仓位组合文本字段，方便过滤、查询)

这里演示即时库存汇总数据查询(STK_InvSumQuery),即时库存明细(STK_Inventory)方法类似就不过多介绍

①在BOS中打开即时库存汇总单据(STK_InvSumQuery)，扩展后，添加子单据头

②添加一个文本字段，命名：仓位组合文本，一定要修改字段所属实体！

③构建子单据体的SQL视图:V_STK_StockLocInfo

--  一定要先测试好Select语句之后再执行Create View
--Create View V_STK_StockLocInfo AS
select 
a.FID AS FID,--单据主键，标识这个子单据头数据属于哪个单据
a.FID AS FEntryId,--子单据头实体主键，唯一且相对固定,这里直接取单据主键
LocL.FNAME AS F_PAEZ_StockLocText--构建的仓位组合文本字段,这里演示环境只启用一个仓位值集
from T_STK_INVSUMQUERY a
--查询仓位等维度关联字段有点复杂,社区有相关资料，这里不做过多介绍
inner join T_BAS_FLEXVALUESDETAIL b on b.FID=a.FSTOCKLOCID
inner join T_BAS_FLEXVALUESENTRY Loc ON Loc.FENTRYID =b.FF100001
JOIN T_BAS_FLEXVALUESENTRY_L LocL ON Loc.FENTRYID = LocL.FENTRYID  and LocL.FLOCALEID=2052

④把构建好的SQL视图名，设置到子单据体的表名上

⑤以上步骤都配置好后，保存BOS单据，前台打开即时库存看效果

三、方案的可推广价值

场景1:把单据数据构建成视图基资料，借助此方案可应对如下需求场景：

• 把单据构建成基础资料，供其他单据选择，或者凭证挂核算维度，例如，采购合同号等。

• 标准的费用应付单选择的入库单号和出库单号也是用此方案实现，可以参考这个思路，修改标准视图基础资料，添加扩展字段携带。

• 标准的业务员，如销售员等，也是视图基础资料，扩展字段信息，也可以自行修改视图配置实现。

• 构建视图单据列表，代替直接SQL账表，权限设置，过滤字段更加方便灵活。

• 仅使用SQL视图，利用数据分组数据的表结构，可构建出树形账表，参考这个案例：部门树形结构的员工花名册构建

• ......

场景2:在已有单据单据中增加实体，自动关联显示另外的数据源，可适用如下一些场景：

• 早期版本单据列表不能同时显示凭证信息和单据体(新版本已支持配置)，可构建子单据头视图实现。

• 单据套打时，需要同时打印单据关联的其他单据的数据信息，可单独实体数据实现。

• 单据体不可做实体添加，可结合场景1，构建定义的视图基础资料，然后借助基础资料属性携带关联显示数据。

• 通过已有数据，自动构建出基础资料的数据分组。

• 即时库存查询添加关联的更多字段信息。

• 单据列表单独显示辅助属性组合、核算维度组合、仓位维度组合等维度关联字段。

• 单据中有些字段数据是需要自动同步计算的，可通过自定义视图实体构造，例如，前面问题中的"审核日期+7"天，自动显示。

• ...

更多实用场景，等你来发挥，期待你的分享...

四、注意事项

• 主键唯一：一定要具备唯一性，有时需要通过字符串拼接来构造唯一主键，记得修改实体的主键字段类型。

• 主键关联性：构建的视图实体数据一定要保持和原单据的主键关联性，该保持一致的主键值，要构造正确。

• 实体主键名：如非必要，不用修改，按照约定俗成的规范来就好。例如，单据主键FID，单据体FEntryId,子单据体FDetailID...
  

• 视图数据只读：视图构建的数据，应该是只读的，仅用于查询使用，禁止对视图构建的实体数据进行更新、删除。

• 视图性能考虑：使用自定义SQL视图时，应尽量过滤数据，并考虑SQL性能，视图确实没有物理表的读取效率高。

• 多语言字段：多语言字段一定要构建到"实体主表_L"的多语言视图中，这个是很容易忘记的一个细节。

• 视图中的字段值：视图构建的数据值一定要和BOS添加的字段类型匹配，例如下拉列表枚举、状态列表值等等。

• 新增的视图实体：需要根据视图数据与单据的关联逻辑来确定应该选择子单据头、单据体、还是子单据体。

• 操作顺序注意：业务字段先不加完都可以，一定要先根据实体主键以及一些后台关键字段构建好SQL视图，把视图名设置到[表名]，同时把[表类型]修改为[视图]之后，才可第1次保存BOS单据，否则有可能出现问题，需要重新做！

• 温馨提示：虽然比较简单，但毕竟是数据库操作，但是操作时一定要细心！细心！细心！

==============================正文结束=====================================

感谢大家的关注与评阅，希望能为大家的实际问题带来参考和启发。