예제 #1
0
 // 获取查询条件下的总数目
 public int getTotalSize(SearchVo vo) {
   StringBuffer sb_sql = new StringBuffer("");
   sb_sql.append("select count(*) from analyse_storage where 1=1");
   // 系统ID
   if (vo.getMarketId() != null && vo.getMarketId() != 0) {
     sb_sql.append(" and mkid=").append(vo.getMarketId());
   }
   // 门店ID
   if (vo.getSfId() != null && vo.getSfId() != 0) {
     sb_sql.append(" and sfid=").append(vo.getSfId());
   }
   // 品牌
   if (vo.getBrand() != null && !vo.getBrand().equals("")) {
     sb_sql.append(" and brand='").append(vo.getBrand()).append("'");
   }
   // 产品
   if (vo.getProductId() != null && vo.getProductId() != 0) {
     sb_sql.append(" and proid=").append(vo.getProductId());
   }
   // 开始日期
   if (vo.getBeginDate() != null && !vo.getBeginDate().equals("")) {
     sb_sql.append(" and record_date >='").append(vo.getBeginDate()).append("'");
   }
   // 结束日期
   if (vo.getEndDate() != null && !vo.getEndDate().equals("")) {
     sb_sql.append(" and record_date <='").append(vo.getEndDate()).append("'");
   }
   return this.getJdbcTemplate().queryForInt(sb_sql.toString());
 }
예제 #2
0
  // 统计库存集合
  public List repStorages(SearchVo vo, int page) {

    int totalSize = this.getTotalSize(vo);
    int count = 15 * page;
    StringBuffer sb_sql = new StringBuffer("");
    sb_sql.append("select * from (");
    // 分页获取数据
    if (totalSize >= count) {
      sb_sql.append(" select TOP 15 * FROM (");
    } else {
      sb_sql.append(" select TOP ").append(totalSize + 15 - count).append(" *FROM (");
    }
    sb_sql
        .append("	SELECT TOP ")
        .append(count)
        .append(" * from (")
        .append(" 	select stoid,mkid,mkname,sfid,sfname,brand,proid,product,record_date,")
        .append(
            "      round(cp_boxnum,4) cp_boxnum,isnull(round(now_boxnum,4),-1) now_boxnum,isnull(round(cp_boxnum-now_boxnum,4),-1) vt_boxnum")
        .append("	from analyse_storage")
        .append(" 	where 1=1 ");
    // 系统ID
    if (vo.getMarketId() != null && vo.getMarketId() != 0) {
      sb_sql.append(" and mkid=").append(vo.getMarketId());
    }
    // 门店ID
    if (vo.getSfId() != null && vo.getSfId() != 0) {
      sb_sql.append(" and sfid=").append(vo.getSfId());
    }
    // 品牌
    if (vo.getBrand() != null && !vo.getBrand().equals("")) {
      sb_sql.append(" and brand='").append(vo.getBrand()).append("'");
    }
    // 产品
    if (vo.getProductId() != null && vo.getProductId() != 0) {
      sb_sql.append(" and proid=").append(vo.getProductId());
    }
    // 日期
    if (vo.getBeginDate() != null && !vo.getBeginDate().equals("")) {
      sb_sql.append(" and record_date ='").append(vo.getBeginDate()).append("'");
    }

    sb_sql
        .append("		) AS AA order by AA.stoid asc")
        .append(" 	) as BB order by BB.stoid desc")
        .append("   )as CC order by CC.stoid asc");
    // 查询数据库
    List list =
        getJdbcTemplate()
            .query(
                sb_sql.toString(),
                new RowMapper() {
                  public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    StorageVo vo = new StorageVo();
                    vo.setStoId(rs.getInt("stoid"));
                    vo.setMkid(rs.getInt("mkid"));
                    vo.setMkname(rs.getString("mkname"));
                    vo.setSfid(rs.getInt("sfid"));
                    vo.setSfname(rs.getString("sfname"));
                    vo.setBrand(rs.getString("brand"));
                    vo.setProid(rs.getInt("proid"));
                    vo.setProduct(rs.getString("product"));
                    vo.setRecorddate(rs.getString("record_date"));
                    vo.setCpboxnum(rs.getDouble("cp_boxnum"));
                    vo.setNowboxnum(rs.getDouble("now_boxnum"));
                    vo.setVtboxnum(rs.getDouble("vt_boxnum"));
                    return vo;
                  }
                });

    return list;
  }
예제 #3
0
  // 统计库存集合
  public List repStorages(SearchVo vo) {

    StringBuffer sb_sql = new StringBuffer("");
    sb_sql
        .append(" 	select stoid,mkid,mkname,sfid,sfname,brand,proid,product,record_date,")
        .append(
            "      round(cp_boxnum,4) cp_boxnum,isnull(round(now_boxnum,4),-1) now_boxnum,isnull(round(cp_boxnum-now_boxnum,4),-1) vt_boxnum")
        .append("	from analyse_storage")
        .append(" 	where 1=1 ");
    // 系统ID
    if (vo.getMarketId() != null && vo.getMarketId() != 0) {
      sb_sql.append(" and mkid=").append(vo.getMarketId());
    }
    // 门店ID
    if (vo.getSfId() != null && vo.getSfId() != 0) {
      sb_sql.append(" and sfid=").append(vo.getSfId());
    }
    // 品牌
    if (vo.getBrand() != null && !vo.getBrand().equals("")) {
      sb_sql.append(" and brand='").append(vo.getBrand()).append("'");
    }
    // 产品
    if (vo.getProductId() != null && vo.getProductId() != 0) {
      sb_sql.append(" and proid=").append(vo.getProductId());
    }
    // 日期
    if (vo.getBeginDate() != null && !vo.getBeginDate().equals("")) {
      sb_sql.append(" and record_date ='").append(vo.getBeginDate()).append("'");
    }

    // 查询数据库
    List list =
        getJdbcTemplate()
            .query(
                sb_sql.toString(),
                new RowMapper() {
                  public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    StorageVo vo = new StorageVo();
                    vo.setStoId(rs.getInt("stoid"));
                    vo.setMkid(rs.getInt("mkid"));
                    vo.setMkname(rs.getString("mkname"));
                    vo.setSfid(rs.getInt("sfid"));
                    vo.setSfname(rs.getString("sfname"));
                    vo.setBrand(rs.getString("brand"));
                    vo.setProid(rs.getInt("proid"));
                    vo.setProduct(rs.getString("product"));
                    vo.setRecorddate(rs.getString("record_date"));
                    vo.setCpboxnum(rs.getDouble("cp_boxnum"));
                    vo.setNowboxnum(rs.getDouble("now_boxnum"));
                    vo.setVtboxnum(rs.getDouble("vt_boxnum"));
                    return vo;
                  }
                });

    return list;
  }
예제 #4
0
  // 统计分析集合
  public StorageVo repRatioSum(SearchVo vo) {

    StringBuffer sb_sql = new StringBuffer("");
    sb_sql
        .append("select round(sum(cp_state)*1.0/count(cp_state),4) cp_ratio,")
        .append("	round( sum(now_state)*1.0/count(now_state),4) now_ratio")
        .append(" from analyse_storage where 1=1");
    // 系统ID
    if (vo.getMarketId() != null && vo.getMarketId() != 0) {
      sb_sql.append(" and mkid=").append(vo.getMarketId());
    }
    // 门店ID
    if (vo.getSfId() != null && vo.getSfId() != 0) {
      sb_sql.append(" and sfid=").append(vo.getSfId());
    }
    // 品牌
    if (vo.getBrand() != null && !vo.getBrand().equals("")) {
      sb_sql.append(" and brand='").append(vo.getBrand()).append("'");
    }
    // 产品
    if (vo.getProductId() != null && vo.getProductId() != 0) {
      sb_sql.append(" and proid=").append(vo.getProductId());
    }
    // 开始日期
    if (vo.getBeginDate() != null && !vo.getBeginDate().equals("")) {
      sb_sql.append(" and record_date >='").append(vo.getBeginDate()).append("'");
    }
    // 结束日期
    if (vo.getEndDate() != null && !vo.getEndDate().equals("")) {
      sb_sql.append(" and record_date <='").append(vo.getEndDate()).append("'");
    }
    // 查询数据库
    List list =
        getJdbcTemplate()
            .query(
                sb_sql.toString(),
                new RowMapper() {
                  public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    StorageVo vo = new StorageVo();
                    vo.setCpRatio(rs.getDouble("cp_ratio"));
                    vo.setNowRatio(rs.getDouble("now_ratio"));
                    return vo;
                  }
                });
    return (StorageVo) list.get(0);
  }