yhlxj/netflix-dao/src/main/java/com/cyksj/model/views/UserCommonDistributeView.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;

/**
 * 项目名: yhlxj2
 * 文件名: UserCommonDistributeView
 * 创建者: JavaZou
 * 创建时间:2024/1/31 17:35
 */
@Getter
@Setter
@SearchBean(tables = "(select id,nickname from user where 1 = 1 :condition:) u left join user_distribute_shared us on u.id = us.shared_id" +
		" left join user_distribute ud on ud.user_id = u.id and ud.deleted is true" +
		" left join order_don od on od.user_id = us.user_id and od.is_distribute = 1 and od.status not in ('noPayment', 'close') :time:"
		, where = "ud.id is null",
		groupBy = "u.id,u.nickname")
public class UserCommonDistributeView {

	@DbField("u.id")
	private Long userId;

	@DbField("u.nickname")
	private String nickname;

	@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("(ifnull(sum(if(od.is_dp and od.ori_money != 0,od.ori_money,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;
}