english_report_email.go 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292
  1. package models
  2. import (
  3. "github.com/beego/beego/v2/client/orm"
  4. "github.com/rdlucklib/rdluck_tools/paging"
  5. "time"
  6. )
  7. // EnglishReportEmail 英文研报-邮箱/客户联系人
  8. type EnglishReportEmail struct {
  9. Id int `orm:"column(id);pk" description:"邮箱ID"`
  10. CompanyId int `description:"客户ID"`
  11. Name string `description:"联系人名称"`
  12. Email string `description:"邮箱地址"`
  13. Mobile string `description:"手机号"`
  14. CountryCode string `description:"区号,86、852、886等"`
  15. BusinessCardUrl string `description:"名片"`
  16. ViewTotal int `description:"累计点击量/阅读量"`
  17. LastViewTime time.Time `description:"最后阅读时间"`
  18. IsDeleted int `description:"删除状态:0-正常;1-已删除"`
  19. Enabled int `description:"邮箱状态:1:有效,0:禁用"`
  20. AdminId int `description:"创建人ID"`
  21. AdminName string `description:"创建人姓名"`
  22. Status int `description:"1:正式,2:临时,3:终止"`
  23. CompanyName string `description:"公司名称"`
  24. CreateTime time.Time `description:"创建时间"`
  25. ModifyTime time.Time `description:"更新时间"`
  26. RegisterTime time.Time `description:"注册时间"`
  27. }
  28. func (item *EnglishReportEmail) TableName() string {
  29. return "english_report_email"
  30. }
  31. // EnglishReportEmailSaveReq 保存邮箱请求体
  32. type EnglishReportEmailSaveReq struct {
  33. Id int `description:"邮箱ID, 大于0为编辑"`
  34. CompanyId int `description:"客户ID"`
  35. Name string `description:"客户名称"`
  36. Email string `description:"邮箱地址"`
  37. Mobile string `description:"手机号"`
  38. CountryCode string `description:"区号,86、852、886等"`
  39. BusinessCardUrl string `description:"名片"`
  40. Enabled int `description:"邮箱状态:1:有效,0:禁用"`
  41. }
  42. func (item *EnglishReportEmail) Create() (err error) {
  43. o := orm.NewOrmUsingDB("rddp")
  44. id, err := o.Insert(item)
  45. if err != nil {
  46. return
  47. }
  48. item.Id = int(id)
  49. return
  50. }
  51. func (item *EnglishReportEmail) Update(cols []string) (err error) {
  52. o := orm.NewOrmUsingDB("rddp")
  53. _, err = o.Update(item, cols...)
  54. return
  55. }
  56. // GetEnglishReportEmailById 主键获取邮箱
  57. func GetEnglishReportEmailById(id int) (item *EnglishReportEmail, err error) {
  58. o := orm.NewOrmUsingDB("rddp")
  59. sql := `SELECT * FROM english_report_email WHERE is_deleted = 0 AND id = ? LIMIT 1`
  60. err = o.Raw(sql, id).QueryRow(&item)
  61. return
  62. }
  63. // EnglishReportEmailPageListResp 分页列表响应体
  64. type EnglishReportEmailPageListResp struct {
  65. List []*EnglishReportEmailResp
  66. Paging *paging.PagingItem `description:"分页数据"`
  67. }
  68. // EnglishReportEmailResp 邮箱响应体
  69. type EnglishReportEmailResp struct {
  70. Id int `description:"邮箱ID"`
  71. CompanyId int `description:"客户ID"`
  72. Name string `description:"客户名称"`
  73. Email string `description:"邮箱地址"`
  74. Mobile string `description:"手机号"`
  75. CountryCode string `description:"区号,86、852、886等"`
  76. BusinessCardUrl string `description:"名片"`
  77. AdminName string `description:"创建人姓名"`
  78. CreateTime string `description:"创建时间"`
  79. ViewTotal int `description:"累计点击量"`
  80. Enabled int `description:"邮箱状态:1:有效,0:禁用"`
  81. CompanyName string `description:"公司名称"`
  82. RegisterCompanyName string `description:"注册公司名称"`
  83. Status int `description:"1:正式,2:临时,3:终止"`
  84. LastViewTime time.Time `description:"最后阅读时间"`
  85. IsDeleted int `description:"删除状态:0-正常;1-已删除"`
  86. AdminId int `description:"创建人ID"`
  87. ModifyTime string `description:"更新时间"`
  88. RegisterTime string `description:"注册时间"`
  89. }
  90. // EnglishReportEmailRespItem 邮箱响应体
  91. type EnglishReportEmailRespItem struct {
  92. Id int `description:"邮箱ID"`
  93. Name string `description:"客户名称"`
  94. Email string `description:"邮箱地址"`
  95. Mobile string `description:"手机号"`
  96. CountryCode string `description:"区号,86、852、886等"`
  97. BusinessCardUrl string `description:"名片"`
  98. AdminName string `description:"创建人姓名"`
  99. CreateTime time.Time `description:"创建时间"`
  100. ViewTotal int `description:"累计点击量"`
  101. Enabled int `description:"邮箱状态:1:有效,0:禁用"`
  102. CompanyName string `description:"公司名称"`
  103. RegisterCompanyName string `description:"注册公司名称"`
  104. RegisterTime time.Time `description:"注册时间"`
  105. }
  106. // GetEnglishReportEmailPageList 获取邮箱列表-分页
  107. func GetEnglishReportEmailPageList(condition string, pars []interface{}, order string, startSize, pageSize int) (total int, list []*EnglishReportEmailRespItem, err error) {
  108. o := orm.NewOrmUsingDB("rddp")
  109. sql := `SELECT a.id,a.name,a.email,a.mobile,a.country_code,a.business_card_url,a.view_total,a.company_id,
  110. 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,
  111. b.company_name AS company_name,a.company_name AS register_company_name FROM english_report_email AS a LEFT JOIN
  112. english_company AS b ON a.company_id = b.company_id
  113. WHERE a.is_deleted = 0 `
  114. sql += condition
  115. if order != "" {
  116. sql += order
  117. } else {
  118. sql += ` ORDER BY a.create_time DESC`
  119. }
  120. totalSQl := `SELECT COUNT(1) total FROM (` + sql + `) z`
  121. if err = o.Raw(totalSQl, pars).QueryRow(&total); err != nil {
  122. return
  123. }
  124. sql += ` LIMIT ?,?`
  125. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
  126. return
  127. }
  128. // GetEnglishReportEmailList 获取邮箱列表
  129. func GetEnglishReportEmailList(condition string, pars []interface{}, order string) (list []*EnglishReportEmail, err error) {
  130. o := orm.NewOrmUsingDB("rddp")
  131. sql := `SELECT * FROM english_report_email WHERE is_deleted = 0 `
  132. sql += condition
  133. if order != "" {
  134. sql += order
  135. } else {
  136. sql += ` ORDER BY create_time DESC`
  137. }
  138. _, err = o.Raw(sql, pars).QueryRows(&list)
  139. return
  140. }
  141. // GetEnglishReportEmailByEmail 地址获取邮箱
  142. func GetEnglishReportEmailByEmail(email string) (item *EnglishReportEmailResp, err error) {
  143. o := orm.NewOrmUsingDB("rddp")
  144. sql := `SELECT a.id,a.name,a.email,a.mobile,a.country_code,a.business_card_url,a.view_total,
  145. a.last_view_time,a.admin_id,a.admin_name,a.create_time,a.modify_time,a.enabled,a.status,
  146. b.company_name AS company_name,a.company_name AS register_company_name FROM english_report_email AS a LEFT JOIN
  147. english_company AS b ON a.company_id = b.company_id WHERE a.is_deleted = 0 AND a.email = ? LIMIT 1`
  148. err = o.Raw(sql, email).QueryRow(&item)
  149. return
  150. }
  151. // EnglishReportEmailDelReq 删除邮箱请求体
  152. type EnglishReportEmailDelReq struct {
  153. EmailId int `description:"邮箱ID"`
  154. }
  155. // EnglishReportEmailDelReq 删除邮箱请求体
  156. type EnglishReportEditEnabledReq struct {
  157. EmailId int `description:"邮箱ID"`
  158. Enabled int `description:"1:有效,0:禁用"`
  159. }
  160. // DelEnglishReportEmail 删除邮箱
  161. func DelEnglishReportEmail(id int) (err error) {
  162. o := orm.NewOrmUsingDB("rddp")
  163. sql := `DELETE FROM english_report_email WHERE id = ? LIMIT 1`
  164. _, err = o.Raw(sql, id).Exec()
  165. return
  166. }
  167. // EnglishReportEmailSendReq 群发邮件请求体
  168. type EnglishReportEmailSendReq struct {
  169. ReportId int `description:"报告ID"`
  170. EmailIds string `description:"邮箱IDs, 为空则表示全部"`
  171. Theme string `description:"邮件主题"`
  172. }
  173. // EnglishReportEmailConf 英文研报邮件配置
  174. type EnglishReportEmailConf struct {
  175. FromAlias string `description:"发信人昵称" json:"from_alias"`
  176. SendAuthGroup string `description:"群发邮件权限组, 英文逗号分隔" json:"send_auth_group"`
  177. }
  178. // GetEnglishCompanyViewPageList 获取联系人点击量列表-分页
  179. func GetEnglishCompanyViewPageList(condition string, pars []interface{}, order string, startSize, pageSize int) (total int, list []*EnglishReportEmail, err error) {
  180. o := orm.NewOrmUsingDB("rddp")
  181. sql := `SELECT * FROM english_report_email WHERE view_total > 0 `
  182. sql += condition
  183. if order != "" {
  184. sql += order
  185. } else {
  186. sql += ` ORDER BY create_time DESC`
  187. }
  188. totalSQl := `SELECT COUNT(1) total FROM (` + sql + `) z`
  189. if err = o.Raw(totalSQl, pars).QueryRow(&total); err != nil {
  190. return
  191. }
  192. sql += ` LIMIT ?,?`
  193. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
  194. return
  195. }
  196. // MultiCreateEnglishEmail 批量新增英文邮箱/联系人
  197. //func MultiCreateEnglishEmail(items []*EnglishReportEmail, logs []*EnglishReportEmailOpLog) (err error) {
  198. // o := orm.NewOrmUsingDB("rddp")
  199. // tx, err := o.Begin()
  200. // if err != nil {
  201. // return
  202. // }
  203. // defer func() {
  204. // if err != nil {
  205. // _ = tx.Rollback()
  206. // } else {
  207. // _ = tx.Commit()
  208. // }
  209. // }()
  210. //
  211. // // 新增联系人
  212. // _, err = tx.InsertMulti(len(items), items)
  213. // if err != nil {
  214. // return
  215. // }
  216. //
  217. // // 新增日志
  218. // _, err = tx.InsertMulti(len(logs), logs)
  219. // return
  220. //}
  221. // EnglishReportEmailResendReq 邮件重发请求体
  222. type EnglishReportEmailResendReq struct {
  223. ReportId int `description:"报告ID"`
  224. SendId int `description:"发送ID, 0表示批量发送全部失败邮件"`
  225. }
  226. // UpdateEnglishEmailEnabledByCompanyId 更新客户下所有联系人状态
  227. func UpdateEnglishEmailEnabledByCompanyId(companyId, enabled int) (err error) {
  228. o := orm.NewOrmUsingDB("rddp")
  229. sql := `UPDATE english_report_email SET enabled = ? WHERE company_id = ?`
  230. _, err = o.Raw(sql, enabled, companyId).Exec()
  231. return
  232. }
  233. // EnglishReportMoveReq 移动至当前客户请求体
  234. type EnglishReportMoveReq struct {
  235. EmailId int `description:"邮箱ID"`
  236. CompanyId int `description:"公司id"`
  237. }
  238. // GetEnglishReportEmailListWithCompany 获取邮箱列表
  239. func GetEnglishReportEmailListWithCompany(condition string, pars []interface{}, order string) (list []*EnglishReportEmailRespItem, err error) {
  240. o := orm.NewOrmUsingDB("rddp")
  241. sql := `SELECT a.id,a.name,a.email,a.mobile,a.country_code,a.business_card_url,a.view_total,
  242. a.last_view_time,a.admin_id,a.admin_name,a.create_time,a.modify_time,a.enabled,a.status,
  243. b.company_name AS company_name,a.company_name AS register_company_name FROM english_report_email AS a LEFT JOIN
  244. english_company AS b ON a.company_id = b.company_id WHERE is_deleted = 0 `
  245. sql += condition
  246. if order != "" {
  247. sql += order
  248. } else {
  249. sql += ` ORDER BY create_time DESC`
  250. }
  251. _, err = o.Raw(sql, pars).QueryRows(&list)
  252. return
  253. }
  254. // GetEnCompanyIdsByKeyword 关键词获取英文客户IDs
  255. func GetEnCompanyIdsByKeyword(keyword string) (companyIds []int, err error) {
  256. o := orm.NewOrmUsingDB("rddp")
  257. sql := `SELECT DISTINCT
  258. a.company_id
  259. FROM
  260. english_report_email AS a
  261. JOIN english_company AS b ON a.company_id = b.company_id AND b.is_deleted = 0 AND b.enabled = 1
  262. WHERE
  263. a.is_deleted = 0 AND a.status = 1 AND (a.email LIKE ? OR a.mobile LIKE ? OR b.company_name LIKE ?)`
  264. _, err = o.Raw(sql, keyword, keyword, keyword).QueryRows(&companyIds)
  265. return
  266. }