// 获取查询条件下的总数目
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 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;
}
// 统计分析集合
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 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;
}