yhlxj/netflix-dao/src/main/java/com/cyksj/model/views/UserDistributeViews.java

package com.cyksj.model.views;

import com.ejlchina.searcher.bean.DbField;
import com.ejlchina.searcher.bean.DbIgnore;
import com.ejlchina.searcher.bean.SearchBean;
import lombok.Getter;
import lombok.Setter;

import java.math.BigDecimal;
import java.util.Date;

/*
 *项目名: netflix
 *文件名: UserDistributeViews
 *创建者: JavaZou
 *创建时间:2022/11/14 13:49
 */
@Getter
@Setter
@SearchBean(tables = "user_distribute ud left join user_distribute_shared us on ud.user_id = us.shared_id" +
		" left join order_don od on od.user_id = us.user_id and od.is_fixed_distribute = 1 and od.status not in ('noPayment', 'close') :time:",
		where = "ud.deleted is true",
		groupBy = "ud.id,ud.user_id,ud.business_id,ud.remark,ud.created_time,ud.update_time,ud.business_bind_time")
public class UserDistributeViews {

	@DbField("ud.id")
	private Long id;

	@DbField("ud.user_id")
	private Long userId;

	@DbField("ud.business_id")
	private Long businessId;

	@DbIgnore
	private String businessName;

	@DbIgnore
	private String nickname;

	@DbField("ud.remark")
	private String remark;

	@DbField("count(od.id)")
	private Integer orders;

	/**
	 * 分销人数
	 * 其中拉新用户购买一单又退款，且无后续下单动作的不算分销用户，不统计在此字段内
	 */
	@DbField("count(DISTINCT CASE WHEN od.status != 'refund' THEN us.user_id ELSE null END)")
	private Integer distributeUserNum;

	/**
	 * 邀请人数
	 */
	@DbField("count(DISTINCT us.user_id)")
	private Integer inviteNum;

	@DbField("ud.business_bind_time")
	private Date businessBindTime;

	/**
	 * 分销订单金额(无需产生积分)
	 */
	@DbField("(ifnull(sum(od.money),0) - ifnull(sum(od.refund_money),0))")
	private BigDecimal distributeOrdersMoney;

	/**
	 * 分销消费占比
	 */
	@DbField("ifnull(cast(count(DISTINCT CASE WHEN od.status != 'refund' THEN us.user_id ELSE null END)/count(DISTINCT us.user_id) as decimal(10,2)),0)")
	private BigDecimal distributeConsumerRate;

	/**
	 * 销量最高的产品
	 */
	@DbIgnore
	private String goodsTitle;

	@DbIgnore
	private BigDecimal availableMoney;

	@DbIgnore
	private BigDecimal gotMoney;

	@DbIgnore
	private BigDecimal points;

	@DbField("ud.created_time")
	private Date createdTime;

	@DbField("ud.update_time")
	private Date updateTime;
}