医药采购系统平台第10天01:采购单药品明细的采购金额和总计&datagrid总计行的展示&交易明细表的数据聚合&使用触发器进行数据同步&交易明细的数据聚合分析及其实现&交易明细的统计实现-优快云博客

本文链接：https://blog.youkuaiyun.com/elastic_solr/article/details/147349812

如果想要获取相关的源码，笔记，和相关工具，对项目需求的二次开发，可以关注我并私信！！！

课程安排

1. 采购单药品明细的采购总金额的总计.

2. 本系统统计分析的需求：

2.1 交易明细的查询：昨天的课程中，入库完成以后页面中的记录就没有了，其实可以

通过交易明细的查询功能来查看以往的交易记录！

例如：可以查看发货记录和入库记录..................等等信息！

2.2 按药品、医院、供货商等进行分类的统计。

一采购单药品明细的采购金额和采购金额的总计

1 需求

以医院（卫生室）身份来登录原型系统，如下

用户（医院）在创建采购单时，页面上会统计出采购单中所有药品的采购总量、采购金额的总量。从而方便用户的操作!

如下图红框内的内容所示

总计的范围是指：符合查询条件的所有记录的采购量、采购金额。

例如：当页面中输入框中输入“流水号”后点击查询按钮，那么就会按照“流水号”进行查询时，总计也只会显示出符合该条件的采购量和采购金额，如下图：

注意：当上图中的“采购药品列表”是分页显示时，那么总计就会显示出所有采购的药品的采购量和采购金额，而不是某一页的采购量和采购金额!

2 dao

2.1 编写sql语句

统计出符合查询条件的采购单药品明细的所有采购量和所有的采购金额。（注意：这里的符合查询条件指的是页面中的输入框中的条件的总汇）!

实现思路：对符合查询条件的采购单明细列表的采购量、采购金额求总和！！

注意：不是只针对一页的数据来求总计。

该sql语句的编写需要参考YycgdMapperCustom.xml文件中的id="findYycgdmxList"的statement中所写的sql语句，如下图：

因此，可以在此基础之上进行扩展！因为都是采购单模块的功能扩展！

最终的Sql语句如下：

--计算采购量和采购金额。如果为空值，那么值就是0.

select sum(nvl(yycgdmx.cgl, 0)) cgl, sum(nvl(yycgdmx.cgje, 0)) cgje

from yycgdmx2018 yycgdmx, yycgd2018 yycgd, useryy, ypxx, usergys

where yycgdmx.yycgdid = yycgd.id

and yycgd.useryyid = useryy.id

and yycgdmx.ypxxid = ypxx.id

and yycgdmx.usergysid = usergys.id

---数据范围约束：指定某个采购单的id.因为只统计一个采购单下的采购量和采购金额。

and yycgdmx.yycgdid='2018100016'

2.2 自定义mapper.xml

参考YycgdMapperCustom.xml文件中的id="findYycgdmxList"的statement中的“采购单药品明细查询列表”的编写！

如下：

<select id="findYycgdmxListSum" parameterType="yycg.business.pojo.vo.YycgdQueryVo"

resultType="yycg.business.pojo.vo.YycgdmxCustom">

select sum(nvl(yycgdmx.cgl, 0)) cgl, sum(nvl(yycgdmx.cgje, 0)) cgje

from yycgdmx${businessyear} yycgdmx, yycgd${businessyear} yycgd,

useryy, ypxx, usergys

where yycgdmx.yycgdid = yycgd.id

and yycgd.useryyid = useryy.id

and yycgdmx.ypxxid = ypxx.id

and yycgdmx.usergysid = usergys.id

<include refid="query_yycgdmx_where" />

<include refid="query_yycgd_where" />

<include refid="yycg.business.dao.mapper.YpxxMapperCustom.query_ypxx_where" />

</select>

2.3 自定义mapper接口

在YycgdMapperCustom.java接口文件中添加如下方法：

3 service

接口功能：采购单明细总计.

接口参数：采购单id、查询条件

CgdService接口中添加如下方法：

CgdServiceImpl实现类的方法如下：

4 页面中的datagrid总计行的展示的分析

需求如图

可以参考easyui的demo

在该目录下的datagrid5.html文件就是用来展示总计行，打开该文件的源码！可以看到

然后，再打开datagrid_data3.json文件，查看源码，如下图：

可以看到：Datagird中所加载的json文件中定义了一个footer，这个footer就是用来在页面列表的最下面所显示一块总计行的区域！！

红框内定义的footer是key，其值是一个数组，数组中的listprice属性分别表示average平均值和Total总数（自己分析吧）！

而对应于Java中，表示数组的就只有List集合了!

因此，可以说：只要footer属性（下面的红框）中的listprice的值和rows属性中（上面的红框）的listprice的标识保持一致，那么就可以正确的求average和total值，那么，页面中就可以正确的显示出来了！

同时，再查看datagrid5.html文件，如下图红框内的定义：

可知：showFotter属性用来指定在加载 datagrid时所显示的footer！

5 action

打开之前写好的CgdAction类中的queryYycgdmx_result（）方法，即如下图所示：

该方法实现的是：返回采购单药品明细的JSON查询结果集!!该方法的返回值类型是DataGridResultInfo类，打开该类，可知该类已经添加了footer属性（DataGridResultInfo类由老师提供，不需要自己写了），如下图：

因此，在CgdAction.java中queryYycgdmx_result()方法中除了原先就返回的total属性和rows属性外，还需要添加返回的footer属性，修改后的代码如下：

6 editcgd.jsp页面

在editcgd.jsp采购单修改页面中，在加载明细列表的方法中，添加showFotter属性，如下：

然后，启动项目进行调试，打开浏览器的调试窗口来查看返回的JSON结果集，如下

分析红框内的数据，可知：在datagrid中对总计行的显示样式和普通行（普通行是指返回total和rows的结果集）的显示样式是不一样的!!!

例如：拿key为id这个值为例，在普通行中所有的id都是有值的，而在总计行（footer）中所有的id的值都是没有值的！！！其他的例如key是useryymc的，也可以照此来判断！

因此，就可以使用关键数据是否有值来作为区别普通行还是总计行的标记！！

因此，需要修改editcgd.jsp页面如下：

同时，还需要把总计这两个字添加到页面中的医院这一栏中，所以还需要修改如下所示：

7 调试

以医院的身份登录系统，如下：

然后，点击“采购单维护”链接，再点击修改，如下：

会显示如下所示的：

二交易明细表的数据聚合分析

该功能属于“统计分析模块”中的开篇内容！

1 需求

交易明细的查询：昨天的课程中，入库完成以后页面中的记录就没有了!

该节内容实现的功能是：

无论是供货商、医院、监督单位，其实都可以通过交易明细的查询功能来查看以往的交易记录！

医院、供货商、监管单位通过交易明细查询功能，查询出指定时间段内的历史交易清单。

监管单位查询交易明细的目的：是为了监督。

医院、供货商查询交易明细的目的：查询与自己相关的交易信息。

以卫生室（医院）身份来登录原型系统（功能已经都开发完成的系统），

用户名：cmzcmc 崔庙镇崔庙村卫生室密码：111111

页面如下：

在“统计分析”模块下，点击“交易明细查询”！在上图的页面中，红框内需要显示的内容包含几大块：

1.医院信息（医院名称）、

2.采购单信息（采购单编号）、

3.供货商信息（供货商名称）、

4.药品信息（流水号、通用名、剂型、规格、转换、交易价）、

5.采购信息（采购量和采购金额）、

6.入库信息（入库量和入库金额，这说明医院还没有执行入库操作，所以显示为空）、

7.退货信息（退货量和退货金额，之所以显示为空，理由同上）、

8.结算信息（结算量和结束金额）。

其中，退货和结算需要自己去实现（参考昨天的文档）！

其实，这里的交易明细可以添加“导出的功能”，包括后面的“统计分析”模块中其他的统计，例如：按药品统计、按医院统计、按供货商统计，都也可以添加“导出功能”，

在交易明细列表的上面自己添加一个导出工具栏！！

导出功能需要自己去实现！

2 分析

2.1交易明细查询的多表关联sql查询

注意：交易明细查询的多表关联的sql语句的编写过程并不能最终应用到系统中，它只是为了引出下节所讲的“数据聚合”的内容！

主查询表：yycgdmx（采购单明细表）。因为，需求中的交易明细中，在同一个采购单下有多个采购的药品信息，所以，采购单明细表作为主查询表。

关联查询表：useryy(内连接)、yycgd(内连接)、usergys(内连接)、ypxx(内连接)、入库信息表yycgdrk（必须使用外连接）、退货单信息表YYTHDMX（必须使用外连接）、结算单信息表yyjsdmx（必须使用外连接）.

注意：入库信息表yycgdrk必须要使用外连接。比如，医院采购了10个药品但是入库的只有4个（说明剩余的6个医院没有执行入库操作），如果使用内连接就会导致主查询表yycgdmx中的记录只有4条记录，而没有入库的6条记录的药品信息丢失了（即查询结果不会显示出来）！因此，必须使用外连接！！

而我们的需求是：只统计药品入库的数量，而没有入库数量的就不统计（虽然不统计但是必须要查询出来，因为页面中需要把不统计的也要显示出来）！

同样的，对于退货表（假如采购了不退货的话，你也要统计出来）和结算的信息表都需要使用外连接！

Sql语句如下：

注意：下面的sql语句的编写过程并不能最终应用到系统中，它只是为了引出下节所讲的“数据聚合”的内容！

select

yycgdmx.*,

yycgdrk.rkl,

yycgdrk.rkje,

yythdmx.thl,

yythdmx.thje,

yyjsdmx.jsl,

yyjsdmx.jsje

from (

select

useryy.mc useryymc,

yycgd.bm yycgdbm,

yycgd.id yycgdid,

usergys.mc usergysmc,

yycgdmx.ypxxid,

ypxx.*,

yycgdmx.cgl,

yycgdmx.cgje

from yycgdmx2018 yycgdmx, yycgd2018 yycgd, useryy,usergys,ypxx

where yycgdmx.yycgdid = yycgd.id

and yycgd.useryyid = useryy.id

and yycgdmx.usergysid = usergys.id

and yycgdmx.ypxxid = ypxx.id

)yycgdmx

--外连接入库信息

left join yycgdrk2018 yycgdrk on yycgdrk.yycgdid = yycgdmx.yycgdid and yycgdrk.ypxxid = yycgdmx.ypxxid

--外连接退货信息

left join yythdmx2018 yythdmx on yythdmx.yycgdid = yycgdmx.yycgdid and yythdmx.ypxxid = yycgdmx.ypxxid

--外连接结算信息

left join yyjsdmx2018 yyjsdmx on yyjsdmx.yycgdid = yycgdmx.yycgdid and yyjsdmx.ypxxid = yycgdmx.ypxxid

分析上面的sql语句查询出来的数据量：

采购明细表：一年有上千万。

入库信息表：一年有上千万。

退货信息表：少。因为数据量小，所以可以忽略不计。

结算信息表：一年有上千万。

4张大数据量的表关联查询，如果用作统计，肯定是在4张表关联的基础上实现，速度很慢，虽然oracle查询几千万的数据没有问题的，但是我们还是需要优化的。

设想：如果将4张表的数据全部放在一张表中，那么，单表查询速度提高很大--->因为不需要关联查询了。这就是接下来要讲的“数据聚合的意义了”！！

2.2 数据聚合意义（了解）

案例：统计网站中的每一年中的每一个页面（交易明细的查询页面）的访问量。

实现方法：对原始数据（统计的来源数据）采集。

当用户点击页面时，需要记录用户点击的信息，信息包括：用户客户端ip、访问时间、访问页面的地址.........等等。

用户客户端ip：可以用来统计出用户来源于哪个地区。

访问时间：统计出网站的访问时段（比如哪些时间段访问量大）。

访问页面的地址：统计出用户的喜好，哪些页面用户访问多。

如果对原始数据直接进行统计，速度很慢，因为原始数据量很大，所以说要对原始进行数据分析，分析后进行数据聚合，数据的聚合其实就是数据的挖掘过程

数据聚合结果：

按分钟聚合：

将原始数据按分钟聚合后存储到单独的分钟聚合表：

聚合时执行一次小范围的统计。

时间（年、月、日、时、分）、XXX页面、访问量（这一分钟的访问总量）

时间（年、月、日、时、分）、yyy页面、访问量（这一分钟的访问总量）.

如果按分钟聚合后，数据量还是很多怎么办？那就按小时集合。

按小时聚合：

将原始数据按小时聚合后存储到单独的小时聚合表：

从分钟聚合表中执行一次小范围的统计。

时间（年、月、日、时）、XXX页面、访问量（这一小时的访问总量）

….

按天聚合：

近年聚合：

通常情况下，单独开发统计系统，后台开启定时任务进行专门数据采集、数据分析、数据聚合。

所以说，统计分析不是通过执行一条select语句就可以完成的事那么简单！

3使用触发器进行数据同步（数据聚合）

数据聚合也可以称为数据同步！

数据同步的目的：因为目标表中需要原始表中的数据。

如果原始表和目标表在一个数据库中，建议在原始表中创建触发器来进行数据的同步，触发器是如何数据同步的呢？

在向原始表中插入记录的同时，向目标表中插入记录.

在原始表中修改记录的同时，也向目标表中修改记录.

在原始表中删除记录的同时，也向目标表中删除记录.

也就是说，需要在原始表中创建3个触发器！

注意：如果创建触发器的目的仅仅是为了数据的同步，那么就建议使用触发器，因为比较简单；

但是，如果创建的触发器内容中包含有业务逻辑，不建议使用触发器，因为会导致触发器滥用了，不易进行维护！

创建触发器的过程可以通过PL/SQL客户端来进行图形化的操作，如下图

上面的triggers中所有的触发器都没有业务逻辑！！

例如：查看 yycgdrk2018_insert触发器的源码（该触发器在下节的“本系统的数据聚合方法”一节中会用到），如下图：

该触发器实现的功能是：在执行插入insert采购单入库表之后执行update更新采购单入库表的操作！

其中：

:new: 是触发器中的一个关键字，表示新记录。在insert和update触发器中使用。

:new.rkl表示取出yycgdrk2018表中的rkl字段！

set t.rkl = :new.rlk表示：将插入的采购单入库表中的rkl入库量字段update更新到交易明细表 yybusiness 表中的rkl字段.

:old：表示旧数据。在delete触发器中使用。

自己可以练习：创建表A和表B，在表A中创建3个触发器，然后同步到表B中。

4 本系统中交易明细的数据聚合的方法

4.1 创建交易明细动态表YYBUSINESS

我们的目标是：将采购单明细信息、入库信息、退货信息表、结算信息表这4张表聚合到一张表中，提高查询统计的速度。

我们设想：

在采购单明细表yycgdmx中是否可以添加入库信息、退货信息...................等字段呢？

这种方法不合理，将统计分析业务功能和采购业务功能融合到一张表中来存储，会导致业务冲突，耦合性较强，可扩展性较差。

在昨天的day09.doc文档中的“采购药品的入库实现”一节中的“创建采购单入库表YYCGDRK”一节中已经对这种

方式的合理性做出更详细的解释了！这里就无需多言！

最终采用的方案：

新创建一张交易明细动态表YYBUSINESS+4位年份：该表记录了采购明细、入库信息、退货信息、结算信息..........等等。该表的唯一约束：采购单id和药品id，如下：

红框内包含4个部分，从上依次往下，包括：采购单的信息、入库的信息、退货的信息、结算的信息。

注意：交易明细表也是动态表，动态表的创建以及命名规范在前面已经讲过了！

4.2 本系统的数据聚合（数据同步）的方式

聚合方法如下：

因为退货和结算的功能没有实现，但是触发器已经创建了，所以退货表和结算表的聚合都使用触发器！

4.3 总结

对于本系统的数据聚合，主要需要完成几个功能：

第一个：在采购单的“提交审核”的方法中的service层中，需要手动编码实现数据聚合！

第二个：入库，退货和结算时，因为使用了事先已经创建好了的触发器，所以不需要进行干预，会自动实现数据聚合的功能！

三交易明细表的数据聚合实现

经过上面的分析，可知：采购单明细表yycgdmx的数据聚合需要在servie（）方法中通过编码来实现！

而入库、结算、退货的操作都是使用触发器来自动进行数据的聚合（同步）的，因此，不需要我们改动！！！

1 采购明细的聚合步骤

将采购单明细动态表yycgdmx聚合到交易明细动态表yybusiness中！

第1步：使用逆向工程生成交易明细动态表YYBUSINESS

注意：在逆向工程的配置文件中，首先需要把其他表的相关配置注释掉后，再使用如下的配置来生成交易明细表。

配置文件中的交易明细表的定义如下：

第2步：把生成的文件复制到项目中

因为交易明细表也是动态表，所以需要修改两个PO类都继承自BusinessBasePo类，如下图：

第3步：修改YybusinessMapper.xml文件

因为是动态表，所以需要把表名都添加上成动态变量，如下图：

这部分可以参考“动态表的生成规则”一节！！

第4步：业务层实现（重点）

修改CgdServiceImpl实现类中的saveYycgdCheckStatus（）方法，该方法实现的是审核提交结果！

经过上面的数据聚合的分析可知：在审核通过时，需要将采购单明细记录插入到交易明细表中。

因此，需要在saveYycgdCheckStatus（）方法中添加如下代码：

具体的源码可以参考项目中的代码：

* 采购明细数据聚合,将采购单明细记录插入到交易明细表中

// 如果审核通过(3表示审核通过)就进行数据聚合

if (yycgdCustom.getZt().equals("3")) {

// 获取采购单明细记录

List<YycgdmxCustom> yycgdmxList = this.findYycgdmxListByYycgdid(yycgdid, null);

// 将采购单明细记录插入到交易明细表中

for (YycgdmxCustom yycgdmxCustom : yycgdmxList) {

// 创建一个交易明细对象

Yybusiness yybusiness = new Yybusiness();

yybusiness.setBusinessyear(businessyear);

yybusiness.setId(UUIDBuild.getUUID());

yybusiness.setYycgdid(yycgdid);// 采购单id

yybusiness.setYpxxid(yycgdmxCustom.getId());// 药品id

yybusiness.setUseryyid(yycgdmxCustom.getUseryyid());// 医院id

yybusiness.setZbjg(yycgdmxCustom.getZbjg());// 中标价格

yybusiness.setJyjg(yycgdmxCustom.getJyjg());// 交易价格

yybusiness.setCgl(yycgdmxCustom.getCgl());// 采购量

yybusiness.setCgje(yycgdmxCustom.getCgje());// 采购金额

yybusiness.setCgzt(yycgdmxCustom.getCgzt());// 采购状态

yybusiness.setUsergysid(yycgdmxCustom.getUsergysid());// 供货商id

yybusinessMapper.insert(yybusiness);

}

2 测试采购明细聚合的结果

需要医院重新创建一个采购单（原因如上：service方法中审核的代码改动了），然后，监管单位审核采购单通过---->供货商受理（发货）----->医院再入库，流程结束后，查看交易明细表中的记录是否聚合成功。

第1步：先要删除交易明细动态表中YYBUSINESS2018（因为是动态表，所以表名会随时间的变化而变化）中的所有记录（如果有记录的话）！

第2步：医院（崔庙镇崔庙村卫生室）身份登录系统，如下：

创建采购单！

然后，进行药品的采购，如下图：

采购完成后，点击查询按钮，会在“采购药品列表”下显示采购的3条药品信息。

然后，输入采购量。然后，再点击“保存采购药品信息”。最后，点击“提交”按钮！

注意：此时查看交易明细动态表yybusinuss中的数据仍然是空的。因为还没有审核！

第3步：以卫生院（崔庙镇卫生院）身份登录系统，执行审核，如下图：

然后进行审核通过，如下图：

然后，查看数据库中的交易明细动态表yybusiness中的记录，如下图：

三条药品信息的记录已经插入到了交易明细表中了！

但是对于入库的信息还暂时没有，这是因为还未执行入库的操作！

第4步：以供货商身份（河南九州通医药公司）登录系统，受理该采购单，来执行发货的操作！

执行发货，如下：

第5步：再以卫生室来登录系统，执行入库操作

执行入库，如下：

然后，查看交易明细表yybusiness，如下图：

红框内的数据就是入库的触发器自动执行的！

因为退货和结算功能在本文档中没有实现，如果实现的话，那么触发器也会自动的在交易明细表中来插入记录。

四交易明细的统计实现

1 需求

需求在“交易明细的数据聚合分析”一节中，页面如下：

2 dao

2.1 交易明细查询的SQL语句

结合上面的交易明细的查询的sql语句，以及数据聚合的相关内容后，我们现在要做的：在交易明细的查询的sql语句中，把有关交易明细表yybusiness的字段添加到最终的查询sql语句中！

简单说，上面的内容只是一个铺垫，最终是要引出下面的sql语句！

主查询表：交易明细表yybusiness.

关联查询表：医院信息表useryy、供货商信息表usergys、采购单基本表yycgd、药品信息表ypxx。

添加3个约束条件，因为交易明细的查询功能的使用者在需求中说的很清楚了：

（1）监管单位查询：包括卫生局和卫生院，卫生局要能查询出所有的交易明细的信息，卫生院要能查询出本区域下的所有的交易明细的信息。那么，统一起来就是：查询出管理地区内所有医院的采购明细信息。

（2）医院查询：查询自己所下的采购明细信息。

（3）供货商查询：与本供货商有交易往来的相关的采购明细信息。

最终的Sql语句如下：

select useryy.id      useryyid,
       useryy.mc      useryymc,
       yycgd.bm       yycgdbm,
       yycgd.id       yycgdid,
       usergys.id     usergysid,
       usergys.mc     usergysmc,
       yycgdmx.ypxxid,

       ypxx.id,
       ypxx.bm,
       ypxx.mc,
       ypxx.jx,
       ypxx.gg,
       ypxx.zhxs,
       ypxx.scqymc,
       ypxx.spmc,     
       ypxx.jyzt,
       
       (select info
          from dictinfo
         where ypxx.jyzt = dictcode
           and typecode = '003') jyztmc,   --交易状态名称

       (select info
          from dictinfo
         where typecode = '011'
           and dictcode = yycgdmx.cgzt) cgztmc,  --采购状态名称

       yycgdmx.cgl,
       yycgdmx.cgje,
       yycgdmx.rkl,
       yycgdmx.rkje,
       yycgdmx.thl,
       yycgdmx.thje,
       yycgdmx.jsl,
       yycgdmx.jsje


  --因为使用数据聚合所生成的表交易明细表yybusiness，所以这里只需要把交易明细表yycgdmx表改成yybusiness表即可。
  from yybusiness2018 yycgdmx, yycgd2018 yycgd, useryy, usergys, ypxx
 where yycgdmx.yycgdid = yycgd.id
   and yycgd.useryyid = useryy.id
   and yycgdmx.usergysid = usergys.id
   and yycgdmx.ypxxid = ypxx.id
      
      --约束条件1：监管单位要查询管理地区内的所有医院的采购明细信息
      
   and useryy.id in (
                     --查询某个卫生院下的管理地区内的所有医院
                     select id from useryy where dq like '1.1.%')
      
      --约束条件2：医院查询自己的采购明细信息
   and useryy.id = '1f8b098b-067e-11e3-8a3c-0019d2ce5116'
      
      --约束条件3：供货商查询： 与本供货商相关的采购明细信息
   and usergys.id = '5197cdd2-08cf-11e3-8a4f-60a44cea4388'

注意：这3个约束条件是不可能同时存在的，只能选其一。