/**
* 获取热销宝贝数据
*
* @param catNo
* @param startMonth
* @param endMonth
* @param shopType
* @param pageParam
* @return
* @throws Exception
*/
public PageEntity<HotGoods> getHotGoods(
String uid,
String catNo,
String startMonth,
String endMonth,
String shopType,
PageParam pageParam,
String flag,
String catPath)
throws Exception {
String catNoIns = "";
if ("ind".equals(flag)) {
Map<String, Object> leaf = this.getLeafListByCatPath2(catPath);
catNoIns = StringUtils.strIn(leaf.get("leafNo").toString());
} else if ("brand".equals(flag)) {
catNoIns = catNo;
}
StringBuffer sb = new StringBuffer();
List<Object> params = new ArrayList<Object>();
params.add(startMonth);
params.add(endMonth);
sb.append(
" select (@rowNum:=@rowNum+1) as rowNum,concat_ws(',',tt2.shop_id, tt2.item_id) as asid, tt1.* from (")
.append(
" select t2.prd_img,t2.prd_name,t1.avg_price, round(avg(t1.avg_price_tran),2) as avg_price_tran,sum(t1.sales_volume) as sales_volume,sum(t1.sales_amount) as sales_amount,")
.append(
" sum(t1.tran_count) as tran_count,t1.shop_name,t1.shop_id,t1.shop_type,t2.region,t2.item_id from tbdaily.tb_tran_month t1 ")
.append(
" join tbbase.tb_base_product t2 on t1.shop_id = t2.shop_id and t1.item_id = t2.item_id")
.append(
" where str_to_date(t1.tran_month,'%Y-%m') between str_to_date(?, '%Y-%m') and str_to_date(?, '%Y-%m')");
if (!"ALL".equals(shopType)) {
sb.append(" and t1.shop_type = ?");
params.add(shopType);
}
sb.append(" and t2.cat_no in (" + catNoIns + ") GROUP BY t2.item_id");
String pageSql = pageParam.buildSql(sb.toString());
pageSql += " limit 0, 100";
pageSql +=
") tt1"
+ " left join (select a1.shop_id, a1.item_id from tbweb.tb_attn_dir_detail a1 join tbweb.tb_attn_dir a2 on a2.adid = a1.adid"
+ " where a2.uid = ? group by a1.shop_id, a1.item_id) tt2 on tt1.shop_id = tt2.shop_id and tt1.item_id = tt2.item_id ,(Select (@rowNum :=0) ) tt ";
params.add(uid);
List<HotGoods> list = sqlUtil.searchList(HotGoods.class, pageSql, params.toArray());
return PageEntity.getPageEntity(pageParam, list);
}
/**
* 热销店铺
*
* @param catNo
* @param startMonth
* @param endMonth
* @param shopType
* @param pageParam
* @return
* @throws Exception
*/
public PageEntity<HotShop> getHotShops(
String uid,
String catNo,
String startMonth,
String endMonth,
String shopType,
PageParam pageParam,
String flag)
throws Exception {
String catNoIns = "";
if ("ind".equals(flag)) {
Map<String, Object> leaf = this.getLeafListByCatNo2(catNo);
catNoIns = StringUtils.strIn(leaf.get("leafNo").toString());
} else if ("brand".equals(flag)) {
catNoIns = catNo;
}
StringBuffer sb = new StringBuffer();
List<Object> params = new ArrayList<Object>();
params.add(startMonth);
params.add(endMonth);
sb.append("select (@rowNum:=@rowNum+1) as rowNum, tt2.asid, tt1.* from (")
.append(
" select t4.shop_name,t4.shop_img,t4.region,t3.sales_volume,t3.sales_amount,t3.tran_count,t3.shop_id,t4.shop_type from (")
.append(
" select t1.shop_id,sum(t1.sales_volume) as sales_volume,sum(t1.sales_amount) as sales_amount,")
.append(" sum(t1.tran_count) as tran_count from tbdaily.tb_tran_month t1")
.append(
" join tbbase.tb_base_product t2 on t1.shop_id = t2.shop_id and t1.item_id = t2.item_id")
.append(
" where str_to_date(t1.tran_month,'%Y-%m') between str_to_date(?, '%Y-%m') and str_to_date(?, '%Y-%m')");
if (!"ALL".equals(shopType)) {
sb.append(" and t1.shop_type = ?");
params.add(shopType);
}
sb.append(" and t1.cat_no in (" + catNoIns + ") group by t1.shop_id)")
.append(" t3 join tbbase.tb_base_shop t4 on t3.shop_id = t4.shop_id GROUP BY t3.shop_id");
String pageSql = pageParam.buildSql(sb.toString());
pageSql += " limit 0, 100";
pageSql +=
") tt1"
+ " left join tbweb.tb_attn_shop tt2 on tt1.shop_id = tt2.shop_id and tt2.uid = ? and tt2.att_type = 1,(Select (@rowNum :=0) ) tt";
params.add(uid);
List<HotShop> list = sqlUtil.searchList(HotShop.class, pageSql, params.toArray());
return PageEntity.getPageEntity(pageParam, list);
}
/**
* 品牌分析-行业分析
*
* @param catNos
* @param startMonth
* @param endMonth
* @param shopType
* @param pageParam
* @return
* @throws Exception
*/
public PageEntity<CatData> getCateDatasByBrand(
String catNos, String startMonth, String endMonth, String shopType, PageParam pageParam)
throws Exception {
List<Map<String, Object>> leafList = getCatNosLeafList(catNos);
List<CatData> list =
getCatDataByMonths(leafList, startMonth, endMonth, shopType, pageParam, null);
return PageEntity.getPageEntity(pageParam, list);
}
/**
* 检索各品牌规模（TOP20）
*
* @param catNo
* @param startMonth
* @param endMonth
* @param shopType
* @param pageParam
* @return
* @throws Exception
*/
public PageEntity<CatData> getBrandScaleByCatNo(
String catNo,
String startMonth,
String endMonth,
String shopType,
PageParam pageParam,
String chartWay)
throws Exception {
List<CatData> list =
this.getBrandScale(catNo, startMonth, endMonth, shopType, pageParam, chartWay);
PageEntity<CatData> pageEntity = PageEntity.getPageEntity(pageParam, list);
return pageEntity;
}
/**
* 检索行业模块时返回的数据
*
* @param iid
* @param uid
* @param pageParam
* @return
* @throws Exception
*/
public PageEntity<CatData> getCateDatasByIid2(
String iid,
String uid,
String startMonth,
String endMonth,
String shopType,
PageParam pageParam)
throws Exception {
List<CatData> list =
this.getCateDatasByIid(iid, uid, startMonth, endMonth, shopType, pageParam);
PageEntity<CatData> pageEntity = PageEntity.getPageEntity(pageParam, list);
return pageEntity;
}