package com.cyksj.model.excel;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.cyksj.model.entity.GroupsTrips;
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 goods_don_sku gdk on gdk.id = gt.sku_id",
		where = ":extra_sql:")
public class ExcelManageAccountData {

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

	@ExcelIgnore
	@DbField("g.id")
	private Long goodsId;

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

	@ExcelIgnore
	@DbField("gdk.id")
	private Long skuId;

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

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

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

	@ExcelProperty("邮箱密码")
	@DbField("select password from sys_email where email = a.account limit 1")
	private String emailPassword;

	@ExcelProperty("APIkey")
	@DbField("a.api_key")
	private String apiKey;

	@ExcelProperty("校验码")
	@DbField("a.verify_codes")
	private String verifyCodes;

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

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

	@ExcelProperty("创建时间")
	@DbField("a.created_time")
	private Date createdTime;

	@ExcelProperty("首开时间")
	@DbField("date_format(a.start_time,'%Y/%c/%e')")
	private String startTimeExcel;

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

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

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

	@DbField("gt.status")
	@ExcelIgnore
	private GroupsTrips.Status gtStatus;

	/**
	 * 车位数量
	 */
	@DbField("gt.num")
	@ExcelIgnore
	private Integer num;

	/**
	 * 可用车位数
	 */
	@DbField("gt.available_num")
	@ExcelIgnore
	private Integer availableNum;

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

	/**
	 * 是否外部采购
	 */
	@DbField("a.outside_purchase")
	@ExcelIgnore
	private Boolean outsidePurchase;

	/**
	 * 是否外部采购
	 */
	@DbField("if(a.outside_purchase,'是','否')")
	@ExcelProperty("外部采购")
	private String outsidePurchaseStr;

	@DbField("a.verify_link")
	@ExcelProperty("同户更新装置")
	private String verifyLink;

	//@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
	@DbIgnore
	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)")
	@DbIgnore
	@ExcelIgnore
	private Boolean expiredAll;

	/**
	 * 账号类型 2 备用
	 */
	@DbField("a.type")
	@ExcelIgnore
	private Integer accountType;

	@DbField("a.email")
	@ExcelProperty("辅助邮箱")
	private String email;

	@DbField("a.relate_email")
	@ExcelProperty("关联邮箱")
	private String relateEmail;

	@DbField("a.remark")
	@ExcelProperty("备注")
	private String remark;
}