/**
* 当天是否签到
*
* @param uid
* @return
*/
public boolean isQianDao(String uid) {
String curDate = DateUtils.getDate();
String sql =
"select count(0) as cnt from bi_base_points_log where DATE_FORMAT(createtime, '%Y-%m-%d') = ? and remark = '每日签到送积分' and uid = ?";
Map<String, Object> map = sqlUtil.search(sql, curDate, uid);
int cnt = StringUtils.toInteger(map.get("cnt"));
return cnt > 0;
}
/**
* 获取属性规模数据
*
* @param catNo
* @param startMonth
* @param endMonth
* @param shopType
* @param pageParam
* @param propName
* @return
* @throws Exception
*/
public List<CatData> getPropScale(
String catNo,
String startMonth,
String endMonth,
String shopType,
PageParam pageParam,
String propName,
String chartWay)
throws Exception {
StringBuffer sb = new StringBuffer();
List<Object> params = new ArrayList<Object>();
params.add(propName);
params.add(catNo);
if (StringUtils.isNotBlank(startMonth)) {
params.add(startMonth);
} else {
params.add(DateUtils.getCurMonth());
}
if (StringUtils.isNotBlank(endMonth)) {
params.add(endMonth);
} else {
params.add(DateUtils.getCurMonth());
}
sb.append(
"select t1.prop_value as cat_name,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_prop t1")
.append(
" join tbbase.tb_base_cat_prop t2 on t1.cat_no = t2.cat_no and t1.prop_name = t2.prop_name")
.append(" where t1.prop_name = ? and t1.cat_no = ?")
.append(
" and str_to_date(t1.tran_month,'%Y-%m') between str_to_date(?, '%Y-%m') and str_to_date(?, '%Y-%m')");
if (StringUtils.isNotBlank(shopType)) {
if (!"ALL".equals(shopType)) {
sb.append(" and t1.shop_type = ?");
params.add(shopType);
}
} else {
sb.append(" and t1.shop_type = ?");
params.add(FinalConstants.DEFAULT_SHOP_TYPE);
}
sb.append(" group by t1.prop_value");
String pageSql = "";
if (pageParam != null) {
if (StringUtils.isNotBlank(chartWay)) {
if ("volume".equals(chartWay)) {
pageParam.setOrderColumn("sum(t1.sales_volume)");
} else if ("amount".equals(chartWay)) {
pageParam.setOrderColumn("sum(t1.sales_amount)");
} else if ("count".equals(chartWay)) {
pageParam.setOrderColumn("sum(t1.tran_count)");
}
pageParam.setOrderDir("desc");
}
pageSql = pageParam.buildSql(sb.toString());
} else {
if (StringUtils.isNotBlank(chartWay)) {
if ("volume".equals(chartWay)) {
pageSql = sb.append(" order by sum(t1.sales_volume) desc").toString();
} else if ("amount".equals(chartWay)) {
pageSql = sb.append(" order by sum(t1.sales_amount) desc").toString();
} else if ("count".equals(chartWay)) {
pageSql = sb.append(" order by sum(t1.tran_count) desc").toString();
}
}
pageSql = sb.toString();
}
pageSql += " limit 0,20";
List<CatData> list = sqlUtil.searchList(CatData.class, pageSql, params.toArray());
// 统计占比
double salesAmountTotal = 0, salesVolumeTotal = 0, tranCountTotal = 0;
for (CatData catData : list) {
salesAmountTotal += StringUtils.toDouble(catData.getSales_amount());
salesVolumeTotal += StringUtils.toDouble(catData.getSales_volume());
tranCountTotal += StringUtils.toDouble(catData.getTran_count());
}
for (CatData catData : list) {
catData.setAmountWeight(
StringUtils.formatPercent(
StringUtils.toDouble(catData.getSales_amount()) / salesAmountTotal));
catData.setCountWeight(
StringUtils.formatPercent(
StringUtils.toDouble(catData.getTran_count()) / tranCountTotal));
catData.setVolumeWeight(
StringUtils.formatPercent(
StringUtils.toDouble(catData.getSales_volume()) / salesVolumeTotal));
}
return list;
}
/**
* 统计类目的销量、销售额、成交次数
*
* @param leafList
* @param startMonth
* @param endMonth
* @param shopType
* @param pageParam
* @return
*/
private List<CatData> getCatDataByMonths(
List<Map<String, Object>> leafList,
String startMonth,
String endMonth,
String shopType,
PageParam pageParam,
String chartWay) {
StringBuffer sb = new StringBuffer();
sb.append(
"select t2.cat_no,t3.cat_name,t2.sales_volume, round(t2.sales_volume/sum(t2.sales_volume)*100,2) as volumeWeight,t2.sales_amount, round(t2.sales_amount/sum(t2.sales_amount)*100,2) as amountWeight,t2.tran_count, round(t2.tran_count/sum(t2.tran_count)*100,2) as countWeight from (");
if (StringUtils.isBlank(startMonth)) {
startMonth = DateUtils.getCurMonth();
}
if (StringUtils.isBlank(endMonth)) {
endMonth = DateUtils.getCurMonth();
}
if (StringUtils.isBlank(shopType)) {
shopType = FinalConstants.DEFAULT_SHOP_TYPE;
}
for (int i = 0; i < leafList.size(); i++) {
Map<String, Object> leaf = leafList.get(i);
sb.append(" select '")
.append(leaf.get("cat_no"))
.append(
"' as cat_no, ifnull(sum(t1.sales_volume),0) as sales_volume,ifnull(sum(t1.sales_amount),0) as sales_amount,ifnull(sum(t1.tran_count),0) as tran_count from tbdaily.tb_tran_month_cat t1")
.append(" where t1.cat_no in (")
.append(StringUtils.strIn(leaf.get("leafNo").toString()))
.append(")")
.append(
" and str_to_date(t1.tran_month,'%Y-%m') between str_to_date('"
+ startMonth
+ "', '%Y-%m') and str_to_date('"
+ endMonth
+ "', '%Y-%m') ");
if (!"ALL".equals(shopType)) {
sb.append(" and t1.shop_type = '" + shopType + "'");
}
if (i != leafList.size() - 1) {
sb.append(" union all ");
}
}
sb.append(
" ) t2 left join tbbase.tb_base_cat_api t3 on t2.cat_no = t3.cat_no group by t2.cat_no");
String pageSql = "";
if (pageParam != null) {
if (StringUtils.isNotBlank(chartWay)) {
pageParam.setoTag("t2");
if ("volume".equals(chartWay)) {
pageParam.setOrderColumn("sales_volume");
} else if ("amount".equals(chartWay)) {
pageParam.setOrderColumn("sales_amount");
} else if ("count".equals(chartWay)) {
pageParam.setOrderColumn("tran_count");
}
pageParam.setOrderDir("desc");
}
pageSql = pageParam.buildSql(sb.toString());
} else {
if (StringUtils.isNotBlank(chartWay)) {
if ("volume".equals(chartWay)) {
pageSql = sb.append(" order by t2.sales_volume desc").toString();
} else if ("amount".equals(chartWay)) {
pageSql = sb.append(" order by t2.sales_amount desc").toString();
} else if ("count".equals(chartWay)) {
pageSql = sb.append(" order by t2.tran_count desc").toString();
}
} else {
pageSql = sb.append(" order by t3.cat_name_single desc").toString();
}
}
List<CatData> list = sqlUtil.searchList(CatData.class, pageSql);
return list;
}
/**
* 封装获取行业趋势的数据业务逻辑
*
* @param leafList
* @param startMonth
* @param endMonth
* @param shopType
* @return
*/
private List<Map<String, Object>> getIndTrendDatas(
List<Map<String, Object>> leafList, String startMonth, String endMonth, String shopType) {
List<String> monthList = DateUtils.getMonthListBetweenDates(startMonth, endMonth);
StringBuffer colTags = new StringBuffer();
StringBuffer colTags2 = new StringBuffer();
StringBuffer colTags3 = new StringBuffer();
StringBuffer cols = new StringBuffer();
StringBuffer cols2 = new StringBuffer();
StringBuffer cols3 = new StringBuffer();
for (int i = 0; i < monthList.size(); i++) {
String month = monthList.get(i);
String cTag = "a" + month.replace("-", "");
colTags.append(cTag);
String cTag2 = "b" + month.replace("-", "");
colTags2.append(cTag2);
String cTag3 = "c" + month.replace("-", "");
colTags3.append(cTag3);
cols.append("ifnull(sum(if(t1.tran_month='")
.append(month)
.append("',t1.sales_volume,0)),0) as ")
.append(cTag);
cols2
.append("ifnull(sum(if(t1.tran_month='")
.append(month)
.append("',t1.sales_amount,0)),0) as ")
.append(cTag2);
cols3
.append("ifnull(sum(if(t1.tran_month='")
.append(month)
.append("',t1.tran_count,0)),0) as ")
.append(cTag3);
if (i != monthList.size() - 1) {
colTags.append(",");
cols.append(",");
colTags2.append(",");
cols2.append(",");
colTags3.append(",");
cols3.append(",");
}
}
StringBuffer sb = new StringBuffer();
sb.append("select t2.cat_no,t3.cat_name,")
.append(colTags)
.append(",")
.append(colTags2)
.append(",")
.append(colTags3)
.append(" from (");
for (int i = 0; i < leafList.size(); i++) {
Map<String, Object> leaf = leafList.get(i);
sb.append("select '")
.append(leaf.get("cat_no"))
.append("' as cat_no,")
.append(cols)
.append(",")
.append(cols2)
.append(",")
.append(cols3)
.append(" from tbdaily.tb_tran_month_cat t1")
.append(" where t1.cat_no in (")
.append(StringUtils.strIn(leaf.get("leafNo").toString()))
.append(")")
.append(
" and str_to_date(t1.tran_month,'%Y-%m') between str_to_date('"
+ startMonth
+ "', '%Y-%m') and str_to_date('"
+ endMonth
+ "', '%Y-%m') ");
if (!"ALL".equals(shopType)) {
sb.append(" and t1.shop_type = '" + shopType + "'");
}
if (i != leafList.size() - 1) {
sb.append(" union all ");
}
}
sb.append(
" ) t2 left join tbbase.tb_base_cat_api t3 on t2.cat_no = t3.cat_no order by t3.cat_name_single");
return sqlUtil.searchList(sb.toString());
}
/**
* 属性趋势（TOP20）
*
* @param catNo
* @param startMonth
* @param endMonth
* @param shopType
* @param propName
* @return
*/
public List<Map<String, Object>> getPropTrend(
String catNo, String startMonth, String endMonth, String shopType, String propName) {
List<String> monthList = DateUtils.getMonthListBetweenDates(startMonth, endMonth);
StringBuffer colTags = new StringBuffer();
StringBuffer colTags2 = new StringBuffer();
StringBuffer colTags3 = new StringBuffer();
StringBuffer cols = new StringBuffer();
StringBuffer cols2 = new StringBuffer();
StringBuffer cols3 = new StringBuffer();
for (int i = 0; i < monthList.size(); i++) {
String month = monthList.get(i);
String cTag = "a" + month.replace("-", "");
colTags.append(cTag);
String cTag2 = "b" + month.replace("-", "");
colTags2.append(cTag2);
String cTag3 = "c" + month.replace("-", "");
colTags3.append(cTag3);
cols.append("ifnull(sum(if(t1.tran_month='")
.append(month)
.append("',t1.sales_volume,0)),0) as ")
.append(cTag);
cols2
.append("ifnull(sum(if(t1.tran_month='")
.append(month)
.append("',t1.sales_amount,0)),0) as ")
.append(cTag2);
cols3
.append("ifnull(sum(if(t1.tran_month='")
.append(month)
.append("',t1.tran_count,0)),0) as ")
.append(cTag3);
if (i != monthList.size() - 1) {
colTags.append(",");
cols.append(",");
colTags2.append(",");
cols2.append(",");
colTags3.append(",");
cols3.append(",");
}
}
StringBuffer sb = new StringBuffer();
List<Object> params = new ArrayList<Object>();
params.add(propName);
params.add(catNo);
params.add(startMonth);
params.add(endMonth);
sb.append("select * from (select t1.prop_value as cat_name,")
.append(cols)
.append(",")
.append(cols2)
.append(",")
.append(cols3)
.append(" from tbdaily.tb_tran_month_prop t1")
.append(
" join tbbase.tb_base_cat_prop t2 on t1.cat_no = t2.cat_no and t1.prop_name = t2.prop_name")
.append(" where t1.prop_name = ? and t1.cat_no = ?")
.append(
" and 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(" group by t1.prop_value limit 0,20) t");
return sqlUtil.searchList(sb.toString(), params.toArray());
}