package com.cyksj.model.excel;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
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.util.Date;

/*
 *项目名: netflix
 *文件名: ExcelManageAccountData
 *创建者: JavaZou
 *创建时间:2022/12/27 13:59
 */
@Getter
@Setter
@SearchBean(tables = "account a left join goods_don g on a.goods_id = g.id " +
		"left join groups_trips gt on gt.account_id = a.id " +
		"left join groups_relation gr on gr.groups_id = gt.id " +
		"left join goods_don_sku gdk on gdk.id = gt.sku_id",
		groupBy = "a.id,g.id,a.bank_card,g.title,a.account,a.password,a.start_time,a.expiry_time,a.amount,a.cn_account,a.us_account,gt.id,a.remark,gdk.id,gdk.spec_val")
public class ExcelManageAccountData {

	@ExcelIgnore
	@DbField("a.id")
	private Long id;

	@ExcelProperty("平台")
	@DbField("g.title")
	private String title;

	@ExcelProperty("规格")
	@DbField("gdk.spec_val")
	private String specVal;

	@ExcelProperty("账号")
	@DbField("a.account")
	private String account;

	@ExcelProperty("密码")
	@DbField("a.password")
	private String password;

	@ExcelProperty("银行卡号")
	@DbField("a.bank_card")
	private String bankCard;

	@DbField("a.expiry_time")
	@ExcelIgnore
	private Date expiryTime;

	@ExcelProperty("到期时间")
	@DbField("date_format(a.expiry_time,'%Y/%c/%e')")
	private String expiryTimeExcel;

	@ExcelProperty("激活码")
	@DbIgnore
	private String activeCode;

	@ExcelProperty("密码更新状态")
	@DbIgnore
	private String passwordStatus;

	@ExcelProperty("激活更新状态")
	@DbIgnore
	private String activeCodeStatus;

	@DbField("select count(ur.id) from user_ticket_cleared_record ur where ur.account_id = a.id and deleted is true and source = 'self'")
	@ExcelIgnore
	private Integer expiryCount;

	@DbField("if((select s.account_id from (select s.account_id, count(*) num" +
			" from (select ur.account_id, ur.relation_id" +
			" from user_ticket_cleared_record ur where ur.deleted is true and source = 'self'" +
			" group by ur.account_id, ur.relation_id) s" +
			"  group by s.account_id) s left join groups_trips gt on gt.account_id = s.account_id where s.account_id = a.id and s.num >= gt.num)is null,false,true)")
	@ExcelIgnore
	private Boolean expiredAll;

	@DbField("max(gr.expiry_time)")
	@ExcelIgnore
	private Date lastTicketTime;

	/**
	 * 今日有过期人员
	 */
	@DbField("min(gr.expiry_time)")
	@ExcelIgnore
	private Date firstTicketTime;
}