/**
* 绑定指定业务的日志
*
* @param bizCode
* @param bizID
*/
public void bindBizLog(Long bizCode, Long bizID) {
try {
this.showPage(false);
this.showQuery(false);
String sql = "select A.*,B.TRUENAME from OP_LOG A,OP B";
String where = "A.OPERATOR_TARGET = B.OP_ID ";
where =
where
+ " AND A.OPERATOR_TYPE= "
+ bizCode
+ " AND A.OPERATOR_TARGET like '%"
+ bizID
+ "%'";
sql = sql + " where " + where + " order by A.CREATE_TIME desc";
this.bindData(DbUtils.queryMapList(this.fieldList, sql));
} catch (BizException ex) {
throw ex;
} catch (Exception e) {
throw new BizException("查询业务日志异常");
}
}
public /*PageResult*/ List<Map<String, Object>> queryComm(
QueryPageData queryPagedata, List<DbField> dbFieldList) {
// 手动查询
List<String> storeN = queryPagedata.GetQueryValues("STORE_NAME");
String storeName = "";
if (storeN != null) {
storeName = storeN.get(0);
}
// 门店
String storeSql = GaUtil.isNullStr(storeName) ? "" : " and st.STORE_NAME='" + storeName + "' ";
List<String> time = queryPagedata.GetQueryValues("CREATETIME");
// 时间段查询
if (time != null && time.size() < 2) {
throw new BizException("请选择完整的时间段");
}
String begainTiem = time != null ? time.get(0) : "";
String endTime = time != null ? time.get(1) : "";
if (time != null && !GaUtil.compareTimeStr(begainTiem, endTime, "yyyy-MM-dd")) {
throw new BizException("请选择正确的时间段");
}
String timeSql =
GaUtil.isNullStr(begainTiem)
? " where 1 = 1 "
: "join STORE_SALES_ORDER sso on sso.STORE_SALES_ORDER_id =s.STORE_SALES_ORDER_id where sso.CREATE_TIME BETWEEN"
+ " '"
+ begainTiem
+ "' and '"
+ endTime
+ "' ";
// 折扣比
Integer ratio = this.userACL.getDiscRatio();
double dratio = ratio == 0 ? 1.00 : ratio * 1.00 / 100;
// 权限设置
String storeId = this.userACL.getStoreID();
String whereSql = GaUtil.isNullStr(storeId) ? "" : " s.STORE_ID IN(" + storeId + ") ";
String sql =
"SELECT *, ROUND(scnt*"
+ dratio
+ "/ 100 * 1.1, 2) CNT, ROUND(scnt / total * 100, 2) AS szb, ROUND(leiji / total * 100, 2) AS zb, "
+ "scnt / total SZHANBI, leiji / total ZHANBI , IF( leiji / total < 0.9, IF( leiji / total < 0.7, 'A', IF(scnt / total > 0.7, 'A', 'B') ), 'C' ) ABC"
+ " FROM (SELECT store_name, COMMODITY_NAME, scnt, (SELECT SUM(i.scnt) FROM (SELECT SUM(s.COMM_CNT * s.RETAIL_PRICE) scnt "
+ "FROM store_sales_detail s JOIN store st ON st.store_id = s.store_id "
+ timeSql
+ " "
+ whereSql
+ " "
+ storeSql
+ " GROUP BY s.COMMODITY_ID ORDER BY scnt DESC) i WHERE i.scnt >=o.scnt ) leiji, "
+ "(SELECT SUM(s.COMM_CNT * s.RETAIL_PRICE) scnt FROM store_sales_detail s JOIN store st ON st.store_id = s.store_id "
+ timeSql
+ " "
+ whereSql
+ " "
+ storeSql
+ ") total FROM (SELECT st.store_name, c.COMMODITY_ID, c.COMMODITY_NAME, SUM(s.COMM_CNT * s.RETAIL_PRICE) scnt "
+ "FROM store_sales_detail s JOIN commodity c ON c.COMMODITY_ID = s.COMMODITY_ID JOIN store st ON st.store_id = s.store_id "
+ timeSql
+ " "
+ whereSql
+ " "
+ storeSql
+ " GROUP BY s.COMMODITY_ID ORDER BY scnt DESC) o ORDER BY scnt DESC) t";
// PageResult result = BizUtil.queryListBySQL(sql, "", "", fields, queryPagedata, null);
List<Map<String, Object>> result = DbUtils.queryMapList(DbUtils.getConnection(), sql);
System.out.println(queryPagedata.GetPageParam());
return result;
// return BizUtil.queryListBySQL(sql, "", "", dbFieldList, queryPagedata, this.userACL);
}