eta_api/models/english_report_email.go

package models

import (
	sql2 "database/sql"
	"eta/eta_api/global"
	"eta/eta_api/utils"
	"github.com/rdlucklib/rdluck_tools/paging"
	"time"
)

// EnglishReportEmail 英文研报-邮箱/客户联系人
type EnglishReportEmail struct {
	Id              int       `gorm:"column:id;primaryKey;autoIncrement" description:"邮箱ID"`
	CompanyId       int       `description:"客户ID"`
	Name            string    `description:"联系人名称"`
	Email           string    `description:"邮箱地址"`
	Mobile          string    `description:"手机号"`
	CountryCode     string    `description:"区号，86、852、886等"`
	BusinessCardUrl string    `description:"名片"`
	ViewTotal       int       `description:"累计点击量/阅读量"`
	LastViewTime    time.Time `description:"最后阅读时间"`
	IsDeleted       int       `description:"删除状态：0-正常；1-已删除"`
	Enabled         int       `description:"邮箱状态：1:有效，0:禁用"`
	AdminId         int       `description:"创建人ID"`
	AdminName       string    `description:"创建人姓名"`
	Status          int       `description:"1:正式,2:临时,3:终止"`
	CompanyName     string    `description:"公司名称"`
	CreateTime      time.Time `description:"创建时间"`
	ModifyTime      time.Time `description:"更新时间"`
	RegisterTime    time.Time `description:"注册时间"`
}

func (item *EnglishReportEmail) TableName() string {
	return "english_report_email"
}

// EnglishReportEmailSaveReq 保存邮箱请求体
type EnglishReportEmailSaveReq struct {
	Id              int    `description:"邮箱ID, 大于0为编辑"`
	CompanyId       int    `description:"客户ID"`
	Name            string `description:"客户名称"`
	Email           string `description:"邮箱地址"`
	Mobile          string `description:"手机号"`
	CountryCode     string `description:"区号，86、852、886等"`
	BusinessCardUrl string `description:"名片"`
	Enabled         int    `description:"邮箱状态：1:有效，0:禁用"`
}

func (item *EnglishReportEmail) Create() (err error) {
	err = global.DbMap[utils.DbNameReport].Create(item).Error
	return
}

func (item *EnglishReportEmail) Update(cols []string) (err error) {
	err = global.DbMap[utils.DbNameReport].Select(cols).Updates(item).Error
	return
}

// GetEnglishReportEmailById 主键获取邮箱
func GetEnglishReportEmailById(id int) (item *EnglishReportEmail, err error) {
	o := global.DbMap[utils.DbNameReport]
	sql := `SELECT * FROM english_report_email WHERE is_deleted = 0 AND id = ? LIMIT 1`
	err = o.Raw(sql, id).First(&item).Error
	return
}

// EnglishReportEmailPageListResp 分页列表响应体
type EnglishReportEmailPageListResp struct {
	List   []*EnglishReportEmailResp
	Paging *paging.PagingItem `description:"分页数据"`
}

// EnglishReportEmailResp 邮箱响应体
type EnglishReportEmailResp struct {
	Id                  int       `description:"邮箱ID"`
	CompanyId           int       `description:"客户ID"`
	Name                string    `description:"客户名称"`
	Email               string    `description:"邮箱地址"`
	Mobile              string    `description:"手机号"`
	CountryCode         string    `description:"区号，86、852、886等"`
	BusinessCardUrl     string    `description:"名片"`
	AdminName           string    `description:"创建人姓名"`
	CreateTime          string    `description:"创建时间"`
	ViewTotal           int       `description:"累计点击量"`
	Enabled             int       `description:"邮箱状态：1:有效，0:禁用"`
	CompanyName         string    `description:"公司名称"`
	RegisterCompanyName string    `description:"注册公司名称"`
	Status              int       `description:"1:正式,2:临时,3:终止"`
	LastViewTime        time.Time `description:"最后阅读时间"`
	IsDeleted           int       `description:"删除状态：0-正常；1-已删除"`
	AdminId             int       `description:"创建人ID"`
	ModifyTime          string    `description:"更新时间"`
	RegisterTime        string    `description:"注册时间"`
}

// EnglishReportEmailRespItem 邮箱响应体
type EnglishReportEmailRespItem struct {
	Id                  int       `description:"邮箱ID"`
	Name                string    `description:"客户名称"`
	Email               string    `description:"邮箱地址"`
	Mobile              string    `description:"手机号"`
	CountryCode         string    `description:"区号，86、852、886等"`
	BusinessCardUrl     string    `description:"名片"`
	AdminName           string    `description:"创建人姓名"`
	CreateTime          time.Time `description:"创建时间"`
	ViewTotal           int       `description:"累计点击量"`
	Enabled             int       `description:"邮箱状态：1:有效，0:禁用"`
	CompanyName         string    `description:"公司名称"`
	RegisterCompanyName string    `description:"注册公司名称"`
	RegisterTime        time.Time `description:"注册时间"`
}

// GetEnglishReportEmailPageList 获取邮箱列表-分页
func GetEnglishReportEmailPageList(condition string, pars []interface{}, order string, startSize, pageSize int) (total int, list []*EnglishReportEmailRespItem, err error) {
	o := global.DbMap[utils.DbNameReport]
	sql := `SELECT a.id,a.name,a.email,a.mobile,a.country_code,a.business_card_url,a.view_total,a.company_id,
a.last_view_time,a.admin_id,a.admin_name,a.create_time,a.modify_time,a.enabled,a.status,a.is_deleted,a.register_time,
b.company_name AS company_name,a.company_name AS register_company_name FROM english_report_email AS a LEFT JOIN 
	english_company AS b ON a.company_id = b.company_id
WHERE a.is_deleted = 0 `
	sql += condition
	if order != "" {
		sql += order
	} else {
		sql += ` ORDER BY a.create_time DESC`
	}
	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z`
	var totalNull sql2.NullInt64
	err = o.Raw(totalSql, pars...).Scan(&totalNull).Error
	if err != nil {
		return
	}
	if totalNull.Valid {
		total = int(totalNull.Int64)
	}
	sql += ` LIMIT ?,?`
	err = o.Raw(sql, pars, startSize, pageSize).Find(&list).Error
	return
}

// GetEnglishReportEmailList 获取邮箱列表
func GetEnglishReportEmailList(condition string, pars []interface{}, order string) (list []*EnglishReportEmail, err error) {
	o := global.DbMap[utils.DbNameReport]
	sql := `SELECT * FROM english_report_email WHERE is_deleted = 0`
	sql += condition
	if order != "" {
		sql += order
	} else {
		sql += ` ORDER BY create_time DESC`
	}
	err = o.Raw(sql, pars...).Find(&list).Error
	return
}

// GetEnglishReportEmailByEmail 地址获取邮箱
func GetEnglishReportEmailByEmail(email string) (item *EnglishReportEmailResp, err error) {
	o := global.DbMap[utils.DbNameReport]
	sql := `SELECT a.id,a.name,a.email,a.mobile,a.country_code,a.business_card_url,a.view_total,
a.last_view_time,a.admin_id,a.admin_name,a.create_time,a.modify_time,a.enabled,a.status,
b.company_name AS company_name,a.company_name AS register_company_name FROM english_report_email AS a LEFT JOIN 
	english_company AS b ON a.company_id = b.company_id WHERE a.is_deleted = 0 AND a.email = ? LIMIT 1`
	err = o.Raw(sql, email).First(&item).Error
	return
}

// EnglishReportEmailDelReq 删除邮箱请求体
type EnglishReportEmailDelReq struct {
	EmailId int `description:"邮箱ID"`
}

// EnglishReportEmailDelReq 删除邮箱请求体
type EnglishReportEditEnabledReq struct {
	EmailId int `description:"邮箱ID"`
	Enabled int `description:"1:有效，0:禁用"`
}

// DelEnglishReportEmail 删除邮箱
func DelEnglishReportEmail(id int) (err error) {
	o := global.DbMap[utils.DbNameReport]
	sql := `DELETE FROM english_report_email WHERE id = ? LIMIT 1`
	err = o.Exec(sql, id).Error
	return
}

// EnglishReportEmailSendReq 群发邮件请求体
type EnglishReportEmailSendReq struct {
	ReportId      int    `description:"报告ID"`
	EmailIds      string `description:"邮箱IDs"`
	Theme         string `description:"邮件主题"`
	EnPermissions []int  `description:"品种权限IDs"`
	NoCompanyIds  []int  `description:"禁止接收邮件的英文客户IDs"`
}

// EnglishReportEmailConf 英文研报邮件配置
type EnglishReportEmailConf struct {
	FromAlias     string `description:"发信人昵称" json:"from_alias"`
	SendAuthGroup string `description:"群发邮件权限组, 英文逗号分隔" json:"send_auth_group"`
}

// GetEnglishCompanyViewPageList 获取联系人点击量列表-分页
func GetEnglishCompanyViewPageList(condition string, pars []interface{}, order string, startSize, pageSize int) (total int, list []*EnglishReportEmail, err error) {
	o := global.DbMap[utils.DbNameReport]
	sql := `SELECT * FROM english_report_email WHERE view_total > 0 `
	sql += condition
	if order != "" {
		sql += order
	} else {
		sql += ` ORDER BY create_time DESC`
	}

	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z`
	var totalNull sql2.NullInt64
	err = o.Raw(totalSql, pars...).Scan(&totalNull).Error
	if err != nil {
		return
	}
	if totalNull.Valid {
		total = int(totalNull.Int64)
	}
	sql += ` LIMIT ?,?`
	err = o.Raw(sql, pars, startSize, pageSize).Find(&list).Error
	return
}

// MultiCreateEnglishEmail 批量新增英文邮箱/联系人
func MultiCreateEnglishEmail(items []*EnglishReportEmail, logs []*EnglishReportEmailOpLog) (err error) {
	tx := global.DbMap[utils.DbNameReport].Begin()
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		} else {
			_ = tx.Commit()
		}
	}()

	// 新增联系人
	err = tx.CreateInBatches(items, utils.MultiAddNum).Error
	if err != nil {
		return
	}

	// 新增日志
	err = tx.CreateInBatches(logs, utils.MultiAddNum).Error
	return
}

// EnglishReportEmailResendReq 邮件重发请求体
type EnglishReportEmailResendReq struct {
	ReportId int `description:"报告ID"`
	SendId   int `description:"发送ID, 0表示批量发送全部失败邮件"`
}

// UpdateEnglishEmailEnabledByCompanyId 更新客户下所有联系人状态
func UpdateEnglishEmailEnabledByCompanyId(companyId, enabled int) (err error) {
	o := global.DbMap[utils.DbNameReport]
	sql := `UPDATE english_report_email SET enabled = ? WHERE company_id = ?`
	err = o.Exec(sql, enabled, companyId).Error
	return
}

// EnglishReportMoveReq 移动至当前客户请求体
type EnglishReportMoveReq struct {
	EmailId   int `description:"邮箱ID"`
	CompanyId int `description:"公司id"`
}

// GetEnglishReportEmailListWithCompany 获取邮箱列表
//func GetEnglishReportEmailListWithCompany(condition string, pars []interface{}, order string) (list []*EnglishReportEmailRespItem, err error) {
//	o := global.DbMap[utils.DbNameReport]
//	sql := `SELECT a.id,a.name,a.email,a.mobile,a.country_code,a.business_card_url,a.view_total,
//a.last_view_time,a.admin_id,a.admin_name,a.create_time,a.modify_time,a.enabled,a.status,
//b.company_name AS company_name,a.company_name AS register_company_name FROM english_report_email AS a LEFT JOIN
//	english_company AS b ON a.company_id = b.company_id WHERE is_deleted = 0 `
//	sql += condition
//	if order != "" {
//		sql += order
//	} else {
//		sql += ` ORDER BY create_time DESC`
//	}
//	err = o.Raw(sql, pars...).Find(&list).Error
//	return
//}

// GetEnCompanyIdsByKeyword 关键词获取英文客户IDs
func GetEnCompanyIdsByKeyword(keyword string) (companyIds []int, err error) {
	o := global.DbMap[utils.DbNameReport]
	sql := `SELECT DISTINCT
				a.company_id
			FROM
				english_report_email AS a
			JOIN english_company AS b ON a.company_id = b.company_id AND b.is_deleted = 0
			WHERE
				a.is_deleted = 0 AND a.status = 1 AND (a.email LIKE ? OR a.mobile LIKE ? OR b.company_name LIKE ?)`
	err = o.Raw(sql, utils.GetLikeKeyword(keyword), utils.GetLikeKeyword(keyword), utils.GetLikeKeyword(keyword)).Find(&companyIds).Error
	return
}