@Override
public void findOrderCount(OrderQuery orderQuery) {
String sqlCount = getSql(orderQuery, 2);
logger.info("count_sql:{}", sqlCount);
int count = helper.queryForInt(sqlCount, new HashMap<String, Object>(0));
orderQuery.setTotalItem(count);
}
/**
* 拼接sql
*
* @param orderQuery
* @param type 1--表示获取值， 2--表示获取总记录数
* @return
*/
private String getSql(OrderQuery orderQuery, int type) {
Integer urpayQueryType = orderQuery.getUrpayStatus();
String from =
"select o.* from plt_taobao_order_tc o force index(idx_created_dpid_status_customerno)";
String fromCount =
"select count(DISTINCT(o.tid)) as num from plt_taobao_order_tc o force index(idx_created_dpid_status_customerno)";
// 连接
String join = "";
if (!StringUtils.isEmpty(orderQuery.getTitle())
|| !StringUtils.isEmpty(orderQuery.getOuterIid())) {
join += " inner join plt_taobao_order_item_tc oi on oi.tid = o.tid";
}
if (!StringUtils.isEmpty(orderQuery.getServiceStaffName()) || orderQuery.getIsHide() != null) {
join += " inner join tb_tc_customer_orders_ship os on os.tid = o.tid";
}
if (urpayQueryType != null) {
// 已催付
if (urpayQueryType.equals(UrpayQueryType.HAS_URPAY.getType())) {
join +=
" inner join tb_tc_urpay_status us on us.tid = o.tid and (us.auto_urpay_status = 1 or us.close_urpay_status=1 or us.cheap_urpay_status=1 or us.manual_urpay_status=1)";
} else if (urpayQueryType.equals(UrpayQueryType.NO_URPAY.getType())) {
join += " inner join tb_tc_urpay_status us on us.tid = o.tid";
} else {
join +=
" inner join tb_tc_urpay_status us on us.tid = o.tid inner join tb_tc_buyer_interaction_statistic ins on ins.dp_id=o.dp_id and ins.customerno = o.customerno";
}
}
// where条件
String conditions = " where o.dp_id = " + orderQuery.getDpId();
conditions += " and o.status = '" + OrderStatus.WAIT_BUYER_PAY.getStatus() + "'";
// 如果商品名不为空
if (!StringUtils.isEmpty(orderQuery.getTitle())) {
String[] titles = orderQuery.getTitle().split("\\s");
for (String title : titles) {
conditions += " and oi.title like '%" + title + "%'";
}
}
// 如果商品外部编码不会空
if (!StringUtils.isEmpty(orderQuery.getOuterIid())) {
conditions += " and oi.outer_iid like '%" + orderQuery.getOuterIid() + "%'";
}
// 如果引导客服不为空
if (!StringUtils.isEmpty(orderQuery.getServiceStaffName())) {
conditions += " and os.service_staff_id = '" + orderQuery.getServiceStaffName() + "'";
}
// 是否隐藏
if (orderQuery.getIsHide() != null) {
if (orderQuery.getIsHide()) {
conditions += " and os.is_hide = " + orderQuery.getIsHide();
} else {
conditions += " and (os.is_hide is null || os.is_hide = " + orderQuery.getIsHide() + ")";
}
}
// 客户名不为空
if (!StringUtils.isEmpty(orderQuery.getCustomerno())) {
conditions += " and o.customerno = '" + orderQuery.getCustomerno() + "'";
}
// 客户手机不为空
if (!StringUtils.isEmpty(orderQuery.getMobile())) {
conditions += " and o.receiver_mobile = " + orderQuery.getMobile();
}
// 如果开始时间不为空
if (!StringUtils.isEmpty(orderQuery.getCreatedStartTime())) {
conditions += " and o.created >= '" + orderQuery.getCreatedStartTime() + "'";
}
// 如果结束时间不为空
if (!StringUtils.isEmpty(orderQuery.getCreatedEndTime())) {
conditions += " and o.created <= '" + orderQuery.getCreatedEndTime() + "'";
}
if (urpayQueryType != null) {
// 未催付
if (urpayQueryType.equals(UrpayQueryType.NO_URPAY.getType())) {
conditions +=
" and us.auto_urpay_status != 1 and us.close_urpay_status!=1 and us.cheap_urpay_status!=1 and us.manual_urpay_status!=1";
}
// 建议催付
else if (urpayQueryType.equals(UrpayQueryType.ADVICE_URPAY.getType())) {
conditions +=
" and us.auto_urpay_status != 1 and us.close_urpay_status!=1 and us.cheap_urpay_status!=1 and us.manual_urpay_status!=1 and ins.trade_payed_count=0 and ins.urpay_count=0";
}
// 不建议催付
else if (urpayQueryType.equals(UrpayQueryType.DEPRECATED_URPAY.getType())) {
conditions +=
" and us.auto_urpay_status != 1 and us.close_urpay_status!=1 and us.cheap_urpay_status!=1 and us.manual_urpay_status!=1 and (ins.trade_payed_count>0 || ins.urpay_count>0)";
}
}
// 分组
String group = "";
if (!StringUtils.isEmpty(orderQuery.getTitle())
|| !StringUtils.isEmpty(orderQuery.getOuterIid())) {
group = " group by o.tid";
}
// 排序
String order = "";
if (orderQuery.getFirstOrder() != null && orderQuery.getFirstOrderSort() != null) {
order = " order by o." + orderQuery.getFirstOrder() + " " + orderQuery.getFirstOrderSort();
}
// 限制
String limit = " limit " + orderQuery.getStartRow() + "," + orderQuery.getPageSize();
String sqlCount = fromCount + join + conditions;
String sql = from + join + conditions + group + order + limit;
if (type == 1) {
return sql;
} else {
return sqlCount;
}
}