ESAP达摩院 思想有多远,我们就能走多远

ESAP第十三弹 视图盛宴


ES也能实现类似编程中的MVC架构,基本原理就是通过ES维护数据库基表数据,基表数据生成数据库视图,ES读取视图完成UI显示。在这种循环结构中,存储和显示是分离的,从而使数据库能实现3NF(第三范式)。

本文将对sql视图所能实现的功能做一些演示,希望能启发读者进一步拓展ES后台功能。

系统表视图

  • 系统连接状态
SELECT * FROM ESSystem..ES_seslog --可在工作台显示用户连接状态

  • 自动任务清单,显示所有用户设置的自动任务。
SELECT B1.RtIds ASRtIds, B1.RtNames AS RtNames, B1.TaskId AS TaskId,
      B1.UserId ASUserId, A1.UserName AS UserName
FROM ES_User A1
INNER JOIN  ESSystem..ES_AFPlan B1 ON A1.UserId = B1.UserId

  • 工作流状态,此略(详见ES工作流扫描)

数据整理视图

  • 物料进出明细(第六弹有介绍),即多表结果集合并
SELECT B1.日期,B1.仓库,B1.单位部门,B2.描述,B1.批号,B1.入库,B1.出库,B2.单位,B1.备注,B2.物料编码,B2.物料分类
FROM (  SELECT 日期,仓库,交货方 AS 单位部门,物料编码,批号,数量 AS 入库,NULL AS 出库,备注
    FROM 入库单 A1,入库单_D A2 WHERE A1.EXCELSERVERRCID=A2.EXCELSERVERRCID
    UNION ALL
    SELECT 日期,仓库,发交,物料编码,批号,NULL,数量,备注
    FROM 出库单 A3,出库单_D A4 WHERE A3.EXCELSERVERRCID=A4.EXCELSERVERRCID) B1
INNER JOIN 物料表 B2 ON B1.物料编码=B2.物料编码

  • 同表多列合并(一般来说要避免这种特殊设计)
SELECT 姓名1,工号1,部门1 FROM 培训记录明细
UNION ALL
SELECT 姓名2,工号2,部门2 FROM 培训记录明细

数据汇总视图

  • 总库存/批次库存,此略(详见第六弹)

  • 在途库存(单据未经确认的数据视为“在途”)

SELECT B2.描述,B1.数量 AS 在途数量,B2.单位,B2.物料编码,B2.物料分类
FROM (  SELECT 物料编码,SUM(数量) AS 数量
		FROM(	SELECT  物料编码,数量 FROM 入库单 A1,入库单_D A2 WHERE A1.EXCELSERVERRCID=A2.EXCELSERVERRCIDAND ISNULL(A1.确认人,’’)=’’
				UNION ALL
				SELECT 物料编码,-数量 FROM 出库单 A3,出库单_D A4 WHERE A3.EXCELSERVERRCID=A4.EXCELSERVERRCIDAND ISNULL(A3.确认人,’’)=’’
		) N
        GROUP BY  物料编码
) B1
RIGHT JOIN  物料表 B2 ON B1.物料编码=B2.物料编码
  • 分组小计/合计
SELECT 仓库,物料分类,描述,数量,单位 FROM v_PCKC
UNION ALL
SELECT 仓库,物料分类 + '-小计' AS 物料分类,''AS描述,SUM(数量),单位
FROM V_PCKC GROUPBY 仓库,物料分类,单位
UNION ALL
SELECT 仓库+ '-合计','' AS 物料分类,''AS 描述,SUM(数量),单位
FROM V_PCKC GROUP BY 仓库,单位
ORDER BY 仓库,物料分类

  • 移动平均价
 --视图1,带单价进出明细
SELECT 日期,物料编码,单价,数量 AS 收入,0 AS 发出
FROM 入库单 A1,入库单_D A2
WHERE A1.EXCELSERVERRCID= A2.EXCELSERVERRCID
UNION ALL
SELECT 日期,物料编码,0,0 AS 收入,数量 AS 发出
FROM 出库单 A1,出库单_D A2
WHERE A1.EXCELSERVERRCID= A2.EXCELSERVERRCID
--视图2,物料价格历史
SELECT 日期,
    物料编码,
    结存=ISNULL((SELECTSUM(收入)-SUM(发出) FROM VPRICEDWHERE 物料编码=A.物料编码 AND 日期<MIN(A.日期)),0),
    结存价=ISNULL((SELECT SUM(收入*单价)/(SUM(收入)) FROM VPRICED WHERE 物料编码=A.物料编码 AND 日期<MIN(A.日期)),0),
    SUM(收入)AS 收入,
    AVG(单价)AS 单价,
    SUM(发出)AS 发出
FROM VPRICED A
GROUP BY 日期,物料编码
--视图3,物料均价
select 日期,物料编码,
    均价=(结存*结存价+收入*单价)/(结存+收入)
FROM vPriceHis

  • 无级BOM,此略(详见第九弹)。

  • 订单状态切换*(该部分仅做演示)

SELECT DNO,T2S.S,CDATE FROM S,T2S WHERE S.V=T2S.CTL ANDS.T=T2S.TID
UNION ALL
SELECT ONO,T2S.S,ODATE FROM ODA,T2S WHERE ODA.S=0 ANDODA.V=T2S.CTL AND ODA.T=T2S.TID
UNION ALL
SELECT ONO,STAT,CDATE FROM ODB WHERE ODB.S=0   --获取所有订单状态
SELECT DNO,MAX(S) AS S FROM VORDSTAT GROUP BY DNO –获取订单当前状态

小结

  1. ES视图是友好的,可以直接写SQL语句,所以不需要借助VBA就能在后台完成大量运算。

  2. 当多个模板中使用同样的多个数据源表定义提数公式时,可以考虑定义视图简化提数。

  3. 当大量使用回写公式时,可以考虑换一下思路,用视图完成相同的功能往往比回写更稳定。

  4. 视图可以减轻数据库存储压力,并且所能实现的功能远超以上罗列项……


近似文章

下一篇 ES一句话VBA