医药采购之分类统计

按药品分类统计
需求：
按药品统计指定时间段时采购量、采购金额、入库量、入库金额。。。。

分类统计：统计数据来源？原始数据是什么？
统计数据来源：交易明细列表，在交易明细的基础进行分类统计。

1.2 按药品分类统计实现
1.2.1 dao
select * 时 虚表中的字段名万万不可重复
select count（*）时虚表中的字段名可以重复
sql:

select  business.id, business.bm, business.mc, sum(nvl(business.cgl,0))cgl, sum(nvl(business.cgje,0))cgje, sum(nvl(business.rkl,0))rkl, sum(nvl(business.rkje,0))rkje, sum(nvl(business.thl,0))thl, sum(nvl(business.thje,0))thje, sum(nvl(business.jsl,0))jsl, sum(nvl(business.jsje,0))jsje from (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  from yybusiness2014 yycgdmx, yycgd2014 yycgd, useryy, usergys, ypxx where yycgdmx.yycgdid = yycgd.id   and yycgd.useryyid = useryy.id   and yycgdmx.usergysid = usergys.id   and yycgdmx.ypxxid = ypxx.id      --监管单位查询管理地区内医院采购明细信息   and useryy.id in (                     --管理地区内医院                     select id from useryy where dq like '1.1.%')      --医院查询自己的采购明细信息   and useryy.id = '1f8b098b-067e-11e3-8a3c-0019d2ce5116'      --供货商查询： 与本供货商相关的采购明细信息   and usergys.id = '5197cdd2-08cf-11e3-8a4f-60a44cea4388'   )business   --分类统计   --按药品统计   group by business.id,business.bm,business.mc

由于统计这里明细查询使用较多采用抽取sql 代码块

<sql id="businessList">        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        from yybusiness${businessyear}        yycgdmx,        yycgd${businessyear} yycgd, useryy, usergys, ypxx        where        yycgdmx.yycgdid = yycgd.id        and yycgd.useryyid = useryy.id        and        yycgdmx.usergysid = usergys.id        and        yycgdmx.ypxxid = ypxx.id        <!-- 只查询某个采购单下药品明细 -->        <include            refid="yycg.business.dao.mapper.YycgdMapperCustom.query_yycgdmx_where" />        <!-- 采购单查询条件 -->        <include            refid="yycg.business.dao.mapper.YycgdMapperCustom.query_yycgd_where" />        <!-- 药品查询条件 -->        <include refid="yycg.business.dao.mapper.YpxxMapperCustom.query_ypxx_where" />        <!-- 医院查询条件 -->        <include refid="yycg.base.dao.mapper.SysuserMapperCustom.query_useryy_where" />        <!-- 供应商条件 -->        <include            refid="yycg.base.dao.mapper.SysuserMapperCustom.query_usergys_where" />    </sql>

所以mapper 为

<!--药品交易明细统计查询列表 -->    <select id="findYybusinessGroupByYpxxList" parameterType="yycg.business.pojo.vo.YycgdQueryVo"        resultType="yycg.business.pojo.vo.YycgdmxCustom">        <!-- 引入分页头 -->        <include refid="yycg.base.commonSql.page_start" />        select        business.id,        business.bm,        business.mc,        sum(nvl(business.cgl,0))cgl,        sum(nvl(business.cgje,0))cgje,        sum(nvl(business.rkl,0))rkl,        sum(nvl(business.rkje,0))rkje,        sum(nvl(business.thl,0))thl,        sum(nvl(business.thje,0))thje,        sum(nvl(business.jsl,0))jsl,        sum(nvl(business.jsje,0))jsje        from (        <!--交易明细查询 -->        <include refid="businessList"></include>        )business        group by business.id,business.bm,business.mc        <!-- 分页尾部 -->        <include refid="yycg.base.commonSql.page_end" />    </select>    <!--药品交易明细统计查询总数 -->    <select id="findYybusinessGroupByYpxxCount" parameterType="yycg.business.pojo.vo.YycgdQueryVo"        resultType="int">        select count(*)        from(        select        business.id        from (        <!--交易明细查询 -->        <include refid="businessList"></include>        )business        group by business.id        )    </select>

1.2.2 service

接口功能：医院、监管单位、供货商按药品统计
统计内容如下：
监管单位：对管理地区内医院采购明细进行统计。
医院：统计自己的采购明细统计
供货商：与本供货商相关的采购明细统计

接口参数：年份、查询条件，单位id、用户类型（1：卫生局 2:卫生院 3：卫生室 4:供货商）

// 药品交易明细统计查询总数    @Override    public int findYybusinessGroupByYpxxCount(String year, String sysid,            String groupid, YycgdQueryVo yycgdQueryVo) throws Exception {        YycgdQueryVo yycgdQueryVo_query = query_findYybusiness(year, sysid,                groupid, yycgdQueryVo);        return yybusinessMapperCustom                .findYybusinessGroupByYpxxCount(yycgdQueryVo_query);    }    // 药品交易明细统计查询列表    @Override    public List<YycgdmxCustom> findYybusinessGroupByYpxxList(String year,            String sysid, String groupid, YycgdQueryVo yycgdQueryVo)            throws Exception {        YycgdQueryVo yycgdQueryVo_query = query_findYybusiness(year, sysid,                groupid, yycgdQueryVo);        return yybusinessMapperCustom                .findYybusinessGroupByYpxxList(yycgdQueryVo_query);    }

1.2.3 action

参考查询页面模版代码实现。

// 药品交易明细统计查询界面    @RequestMapping("/groupbyypxx")    public String groupbyypxx(Model model) throws Exception {        List<Dictinfo> cgztlist = systemConfigService.findDictinfoByType("011");        model.addAttribute("cgztlist", cgztlist);        // 默认当前年份        model.addAttribute("year", MyUtil.get_YYYY(MyUtil.getDate()));        return "/business/tj/groupbyypxx";    }    // 药品交易明细统计查询参数    @RequestMapping("/groupbyypxx_result")    public @ResponseBody    DataGridResultInfo groupbyypxx_result(String year, ActiveUser activeUser,            YycgdQueryVo yycgdQueryVo, int page, int rows) throws Exception {        // 单位id        String sysid = activeUser.getSysid();        // 用户类型        String groupid = activeUser.getGroupid();        // 列表总数        int total = businessService.findYybusinessGroupByYpxxCount(year, sysid,                groupid, yycgdQueryVo);        // 分页参数        PageQuery pageQuery = new PageQuery();        pageQuery.setPageParams(total, rows, page);        // 设置分页参数        yycgdQueryVo.setPageQuery(pageQuery);        List<YycgdmxCustom> list = businessService                .findYybusinessGroupByYpxxList(year, sysid, groupid,                        yycgdQueryVo);        DataGridResultInfo dataGridResultInfo = new DataGridResultInfo();        dataGridResultInfo.setTotal(total);        dataGridResultInfo.setRows(list);        return dataGridResultInfo;    }

1.1 按供货商统计（参考药品分类统计）
需求：
按供货商统计指定时间段时采购量、采购金额、入库量、入库金额。。。。

Dao

<!-- 按供应商统计 -->    <select id="findYybusinessGroupByGysList" parameterType="yycg.business.pojo.vo.YycgdQueryVo"        resultType="yycg.business.pojo.vo.YycgdmxCustom">        <!-- 引入分页头 -->        <include refid="yycg.base.commonSql.page_start" />        select        business.usergysmc,        sum(nvl(business.cgl,0))cgl,        sum(nvl(business.cgje,0))cgje,        sum(nvl(business.rkl,0))rkl,        sum(nvl(business.rkje,0))rkje,        sum(nvl(business.thl,0))thl,        sum(nvl(business.thje,0))thje,        sum(nvl(business.jsl,0))jsl,        sum(nvl(business.jsje,0))jsje        from (        <!--交易明细查询 -->        <include refid="businessList"></include>        )business        group by business.usergysmc        <!-- 分页尾部 -->        <include refid="yycg.base.commonSql.page_end" />    </select>    <!-- 按供应商统计 -->    <select id="findYybusinessGroupByGysCount" parameterType="yycg.business.pojo.vo.YycgdQueryVo"        resultType="int">        select count(*) from(        select        business.usergysmc        from (        <!--交易明细查询 -->        <include refid="businessList"></include>        )business        group by business.usergysmc        )    </select>

Service

// 供应商交易明细统计查询列表总数    @Override    public int findYybusinessGroupByGysCount(String year, String sysid,            String groupid, YycgdQueryVo yycgdQueryVo) throws Exception {        YycgdQueryVo yycgdQueryVo_query = query_findYybusiness(year, sysid,                groupid, yycgdQueryVo);        return yybusinessMapperCustom                .findYybusinessGroupByGysCount(yycgdQueryVo_query);    }    // 供应商交易明细统计查询列表    @Override    public List<YycgdmxCustom> findYybusinessGroupByGysList(String year,            String sysid, String groupid, YycgdQueryVo yycgdQueryVo)            throws Exception {        YycgdQueryVo yycgdQueryVo_query = query_findYybusiness(year, sysid,                groupid, yycgdQueryVo);        return yybusinessMapperCustom                .findYybusinessGroupByGysList(yycgdQueryVo_query);    }

Action
修改总数he 列表方法

// 列表总数        int total = businessService.findYybusinessGroupByGysCount(year, sysid,                groupid, yycgdQueryVo);        List<YycgdmxCustom> list = businessService                .findYybusinessGroupByGysList(year, sysid, groupid,                        yycgdQueryVo);

页面 修改列名字段 和接收参数方法

var columns = [ [ {    field : 'usergysmc',    title : '供应商',    width : 100url : '${baseurl}/tj/groupbyusergys_result.action',

1.2 按医院统计（参考药品分类统计）
需求：
按医院统计指定时间段时采购量、采购金额、入库量、入库金额。。。。