Пример #1
0
  /**
   * 当天是否签到
   *
   * @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;
  }
Пример #2
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;
  }
Пример #3
0
  /**
   * 统计类目的销量、销售额、成交次数
   *
   * @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;
  }
Пример #4
0
  /**
   * 封装获取行业趋势的数据业务逻辑
   *
   * @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());
  }
Пример #5
0
  /**
   * 属性趋势(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());
  }