// 获取查询条件下的总数目
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());
}
// 统计分析集合
public List repRatioSign(SearchVo vo) {
StringBuffer sb_sql = new StringBuffer("");
sb_sql
.append(
"select mkname,sfname,brand,product,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("'");
}
sb_sql
.append(" group by mkname,sfname,brand,product")
.append(" order by mkname,sfname,brand,product");
// 查询数据库
List list =
getJdbcTemplate()
.query(
sb_sql.toString(),
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
StorageVo vo = new StorageVo();
vo.setMkname(rs.getString("mkname"));
vo.setSfname(rs.getString("sfname"));
vo.setBrand(rs.getString("brand"));
vo.setProduct(rs.getString("product"));
vo.setCpRatio(rs.getDouble("cp_ratio"));
vo.setNowRatio(rs.getDouble("now_ratio"));
return vo;
}
});
return list;
}
// 分页获取卖场陈列集合
public List getListStorages(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,cp_units,cp_store,now_units,now_store")
.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("'");
}
// 结束日期
if (vo.getEndDate() != null && !vo.getEndDate().equals("")) {
sb_sql.append(" and record_date <='").append(vo.getEndDate()).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.setCpunits(rs.getString("cp_units"));
vo.setCpstore(rs.getInt("cp_store"));
vo.setNowunits(rs.getString("now_units"));
vo.setNowstore(rs.getInt("now_store"));
return vo;
}
});
return list;
}