医药采购之供货商药品目录添加查询

1      供货商药品目录添加查询

1.1     需求 ：

供货商查询药品目录(ypxx) ，从里边选择要供应的药品.

查询列表中将供货商药品目录中的药品过虑掉。

1.2     实现

1.2.1     dao

供货商药品添加目录查询，查询药品目录表，查询列表中将供货商药品目录已经存在药品过虑掉。

Sql：

主查询表：ypxx表

关联查询表：在where条件中，过虑掉供货商药品目录表的记录

 

select ypxx.id,

       ypxx.bm,

       ypxx.mc,

       ypxx.jx,

       ypxx.gg,

       ypxx.zhxs,

       ypxx.scqymc,

       ypxx.spmc,

       ypxx.zbjg,

       ypxx.jyzt,

      

       (select info

          from dictinfo

         where ypxx.jyzt = dictcode

           and typecode = '003') jyztmc

  --子查询，关联查询到说明此药品id在供货商药品目录存在

  --(select id from gysypml whereusergysid='5197cdd2-08cf-11e3-8a4f-60a44cea4388' and ypxx.id = gysypml.ypxxid)gysypmlid

 from ypxx

 --查询子查询不为空的值

 wherenotexists(selectidfrom gysypml where usergysid='5197cdd2-08cf-11e3-8a4f-60a44cea4388'and ypxx.id = gysypml.ypxxid)

   // 查询供应商药品添加目录

   publicList<GysypmlCustom> findAddGysypmlList(GysypmlQueryVo gysypmlQueryVo)

         throws Exception;

 

   // 查询供应商药品添加目录数量

   publicintfindAddGysypmlCount(GysypmlQueryVo gysypmlQueryVo)

         throws Exception;

 

1.2.2     service

 

接口功能：供货商药品添加目录查询

接口参数：查询条件，供货商id

 // 查询供应商药品添加目录

   publicList<GysypmlCustom> findAddGysypmlList(GysypmlQueryVo gysypmlQueryVo)

         throws Exception;

 

   // 查询供应商药品添加目录数量

   publicintfindAddGysypmlCount(GysypmlQueryVo gysypmlQueryVo)

         throws Exception;

 

实现方法：

// 查询供应商药品添加目录

   @Override

   publicList<GysypmlCustom> findAddGysypmlList(String usergysid,

         GysypmlQueryVogysypmlQueryVo)throwsException {

      //非空判断 gysypmlQueryVo不等于空否则就创建一个新的gysypmlQueryVo

      gysypmlQueryVo= gysypmlQueryVo !=null ? gysypmlQueryVo

            :newGysypmlQueryVo();

      GysypmlCustomgysypmlCustom = gysypmlQueryVo.getGysypmlCustom();

      if (gysypmlCustom ==null) {

         gysypmlCustom= newGysypmlCustom();

 

      }

 

      gysypmlCustom.setUsergysid(usergysid);

      gysypmlQueryVo.setGysypmlCustom(gysypmlCustom);

      returngysypmlMapperCustom.findAddGysypmlList(gysypmlQueryVo);

   }

 

   // 查询供应商药品添加目录数量

   @Override

   publicintfindAddGysypmlCount(String usergysid,

         GysypmlQueryVogysypmlQueryVo)throwsException {

 

      //因为在mapper中传递参数没有判断非空。所以当参数非空后就会报错。所以不能让参数非空因为查询需要

      //非空判断

      gysypmlQueryVo= gysypmlQueryVo !=null ? gysypmlQueryVo

            :newGysypmlQueryVo();

 

      GysypmlCustomgysypmlCustom = gysypmlQueryVo.getGysypmlCustom();

      if (gysypmlCustom ==null) {

         gysypmlCustom= newGysypmlCustom();

      }

 

      //设置供货商id

      gysypmlCustom.setUsergysid(usergysid);

 

      gysypmlQueryVo.setGysypmlCustom(gysypmlCustom);

      returngysypmlMapperCustom.findAddGysypmlCount(gysypmlQueryVo);

   }

1.2.3     action

   // 供应商添加药品目录总数

   @RequestMapping("/querygysypmladd_result")

   public@ResponseBody

   DataGridResultInfoquerygysypmladd_result(HttpSession session,int page,

         int rows, GysypmlQueryVogysypmlQueryVo)throwsException {

      //取出当前用户

      ActiveUseractiveUser = (ActiveUser) session

            .getAttribute(Config.ACTIVEUSER_KEY);

      Stringusergysid = activeUser.getSysid();

 

      //取总数

      int total =ypmlService.findAddGysypmlCount(usergysid,gysypmlQueryVo);

      //分页参数

      PageQuerypageQuery = newPageQuery();

      pageQuery.setPageParams(total,rows, page);

      gysypmlQueryVo.setPageQuery(pageQuery);

      //新建列表

      List<GysypmlCustom>list = ypmlService.findAddGysypmlList(usergysid,

            gysypmlQueryVo);

 

      DataGridResultInfodataGridResultInfo =new DataGridResultInfo();

      dataGridResultInfo.setTotal(total);

      dataGridResultInfo.setRows(list);

      return dataGridResultInfo;

   }

 

   

调试