datatools/clueSync/sendMail.go

package main

import (
	"app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/mail"
	"fmt"
	"log"
	"strings"
)

var (
	selfMail = `SELECT a.position_id, a.name, c.mail FROM dwd_d_crm_department_level_succbi a LEFT JOIN jianyu.entniche_user c ON a.ent_user_id = c.id WHERE a.position_id = ?`
	infoSelf = `SELECT a.name, a.SZ_PID1, a.SZ_PID2, a.dept_name, b.role_id FROM dwd_d_crm_department_level_succbi a INNER JOIN dwd_f_crm_personnel_management b ON b.position_id = a.position_id WHERE a.position_id = ? AND a.resign = 0`
	topMail  = `SELECT a.name, a.ent_user_id, b.mail FROM dwd_d_crm_department_level_succbi a LEFT JOIN jianyu.entniche_user b ON a.ent_user_id = b.id LEFT JOIN dwd_f_crm_personnel_management c ON a.position_id = c.position_id AND c.resign = 0 
                	WHERE a.SZ_PID3 IN (SELECT a.SZ_PID3 FROM dwd_d_crm_department_level_succbi a WHERE a.position_id = ?) AND c.role_id = 3`
	topMail3 = `SELECT a.name, a.ent_user_id, b.mail FROM dwd_d_crm_department_level_succbi a LEFT JOIN jianyu.entniche_user b ON a.ent_user_id = b.id LEFT JOIN dwd_f_crm_personnel_management c ON a.position_id = c.position_id 
                    WHERE a.dept_name = '销售三部' AND a.resign = 0 AND c.role_id = 3`
	dmInfo = `SELECT b.dept_name FROM dwd_d_crm_department_level_succbi b WHERE b.position_id IN (SELECT a.position_id FROM dwd_f_crm_personnel_management a
		WHERE assign_type = 1) GROUP BY b.dept_name`
	dmInfoCount = `SELECT b.position_id, COUNT(IF(a.trailstatus <> '08', 1, NULL)) as num FROM Jianyu_subjectdb_test.dwd_f_crm_clue_info a RIGHT JOIN Jianyu_subjectdb_test.dwd_d_crm_department_level_succbi b ON a.position_id = b.position_id LEFT JOIN Jianyu_subjectdb_test.dwd_f_crm_personnel_management c ON b.position_id = c.position_id WHERE b.dept_name = ? AND c.resign = 0 AND c.assign_type = 1 GROUP BY a.position_id HAVING num < ?`
	dmInfoMail  = `SELECT a.name, a.position_id, (b.send_mail >> 2) & 1 AS flag_1, (b.send_mail >> 3) & 1 AS flag_2, c.mail FROM dwd_d_crm_department_level_succbi a INNER JOIN dwd_f_crm_personnel_management b ON a.position_id = b.position_id LEFT JOIN jianyu.entniche_user c ON a.ent_user_id = c.id WHERE a.dept_name = ? AND a.resign = 0 AND b.role_id = 3`
	xgFlag      = `SELECT a.name, (b.send_mail >> 4) & 1 AS flag FROM dwd_d_crm_department_level_succbi a INNER JOIN jianyu_subjectdb_test.dwd_f_crm_personnel_management b ON a.position_id = b.position_id WHERE b.role_id = 5 AND b.resign = 0`
	xgMail      = `SELECT a.name, c.mail FROM dwd_d_crm_department_level_succbi a INNER JOIN dwd_f_crm_personnel_management b ON a.position_id = b.position_id LEFT JOIN jianyu.entniche_user c ON a.ent_user_id = c.id WHERE b.role_id = 5 AND b.resign = 0`
	// 所有参与线索分配人员的邮箱
	allMail = `SELECT a.name, c.mail FROM dwd_f_crm_personnel_management a INNER JOIN dwd_d_crm_department_level_succbi b ON a.position_id  = b.position_id LEFT JOIN jianyu.entniche_user c ON b.ent_user_id  = c.id  WHERE a.assign_type = 1 AND a.resign = 0`
	// 超管
	cgMail = `SELECT a.name, c.mail FROM dwd_d_crm_department_level_succbi a INNER JOIN dwd_f_crm_personnel_management b ON a.position_id = b.position_id LEFT JOIN jianyu.entniche_user c ON a.ent_user_id = c.id WHERE b.role_id = 4 AND b.resign = 0`
)

// @Author jianghan
// @Description 个人预警/上限
// @Date 2024/4/11
func WarningPerl() {
	sql := `SELECT count(*) FROM dwd_f_crm_clue_info where position_id = ? and trailstatus <> '08'`
	sql1 := `UPDATE dwd_f_crm_personnel_management SET send_mail = (%s) where position_id = ?`
	pp := TiDb.SelectBySql(`SELECT name, position_id, send_mail & 1 AS flag_1, (send_mail >> 1) & 1 AS flag_2 FROM dwd_f_crm_personnel_management WHERE resign = 0`)
	if pp != nil && len(*pp) > 0 {
		for _, m := range *pp {
			sendFlag1 := common.IntAll(m["flag_1"]) //预警邮件发送
			sendFlag2 := common.IntAll(m["flag_2"]) //上限邮件发送
			posid := common.Int64All(m["position_id"])
			count := TiDb.CountBySql(sql, posid)
			if count >= cfg.AllocationCap && sendFlag2 == 0 {
				ExceedLimitByPp(posid) // 发上限邮件
				_, _ = TiDb.ExecBySql(fmt.Sprintf(sql1, "send_mail | (1 << 1)"), posid)
			} else if count >= cfg.WarningValue && sendFlag1 == 0 && sendFlag2 == 0 {
				WillWarningByPp(posid) // 发预警邮件
				_, _ = TiDb.ExecBySql(fmt.Sprintf(sql1, "send_mail | (1 << 0)"), posid)
			} else if sendFlag1 == 1 && count < cfg.WarningValue { // 已发邮件
				_, _ = TiDb.ExecBySql(fmt.Sprintf(sql1, "send_mail & ~(1 << 0)"), posid)
			} else if sendFlag2 == 1 && count < cfg.AllocationCap { // 已发邮件
				_, _ = TiDb.ExecBySql(fmt.Sprintf(sql1, "send_mail & ~(1 << 1)"), posid)
			}
		}
	}
}

// @Author jianghan
// @Description 部门预警/上限 所有人
// @Date 2024/5/9
func WarningDm() {
	sql1 := `UPDATE dwd_f_crm_personnel_management SET send_mail = (%s) where position_id = ?`
	dm := TiDb.SelectBySql(dmInfo)
	send := true // 所有人
	for _, m := range *dm {
		d1 := common.ObjToString(m["dept_name"])
		send1, send2 := true, true // 预警，上限 发送标记
		info := TiDb.SelectBySql(dmInfoCount, d1, cfg.WarningValue)
		if info != nil && len(*info) > 0 {
			send = false
			send1 = false
		}
		info1 := TiDb.SelectBySql(dmInfoCount, d1, cfg.AllocationCap)
		if info1 != nil && len(*info1) > 0 {
			send2 = false
		}
		//for _, m1 := range *info {
		//	count := common.IntAll(m1["num"])
		//	if count < WarningSize {
		//		send1 = false
		//	}
		//	if count < LimitSize {
		//		send2 = false
		//	}
		//}
		info2 := TiDb.SelectBySql(dmInfoMail, d1)
		if info2 == nil || len(*info2) == 0 {
			log.Println("未查询到部门领导邮箱", d1)
			continue
		}
		sendFlag1 := common.IntAll((*info2)[0]["flag_1"]) //预警邮件发送
		sendFlag2 := common.IntAll((*info2)[0]["flag_2"]) //上限邮件发送
		to := getMailAds(common.ObjToString((*info2)[0]["mail"]), common.ObjToString((*info2)[0]["name"]))
		posid := common.Int64All((*info2)[0]["position_id"])
		var toCc []string // 销管
		for _, m2 := range *TiDb.SelectBySql(xgMail) {
			if m3 := getMailAds(common.ObjToString(m2["mail"]), common.ObjToString(m2["name"])); m3 != "" {
				toCc = append(toCc, m3)
			}
		}
		if d1 != "销售三部" {
			toCc = append(toCc, getMailAds("shenbingyi@topnet.net.cn", "沈炳毅"))
		}
		if send2 && sendFlag2 == 0 { // 上限
			ExceedLimitByDm(d1, to, toCc)
			_, _ = TiDb.ExecBySql(fmt.Sprintf(sql1, "send_mail | (1 << 3)"), posid) // 倒数第四位修改成1
			sendFlag2 = 1
		} else {
			if !send2 && sendFlag2 == 1 {
				_, _ = TiDb.ExecBySql(fmt.Sprintf(sql1, "send_mail & ~(1 << 3)"), posid) // 倒数第四位修改成0
			}
		}
		if send1 && sendFlag1 == 0 && sendFlag2 == 0 { // 预警
			WillWarningByDm(d1, to, toCc)
			_, _ = TiDb.ExecBySql(fmt.Sprintf(sql1, "send_mail | (1 << 2)"), posid) // 倒数第三位修改成1
		} else {
			if !send1 && sendFlag1 == 1 {
				_, _ = TiDb.ExecBySql(fmt.Sprintf(sql1, "send_mail & ~(1 << 2)"), posid) // 倒数第三位修改成0
			}
		}
	}
	sql := `UPDATE dwd_f_crm_personnel_management SET send_mail = (%s) where position_id IN (SELECT a.position_id FROM dwd_f_crm_personnel_management a WHERE a.role_id = 5 AND a.resign = 0)`
	if send {
		info3 := TiDb.SelectBySql(xgFlag)
		if info3 != nil && len(*info3) > 0 {
			sendFlag := common.IntAll((*info3)[0]["flag"])
			if sendFlag == 0 {
				WillWarningByAll()
				TiDb.ExecBySql(fmt.Sprintf(sql, "send_mail | (1 << 4)"))
			}
		}
	} else {
		info3 := TiDb.SelectBySql(xgFlag)
		if info3 != nil && len(*info3) > 0 {
			sendFlag := common.IntAll((*info3)[0]["flag"])
			if sendFlag == 1 {
				TiDb.ExecBySql(fmt.Sprintf(sql, "send_mail & ~(1 << 4)")) // 修改销管 发送邮件标志 0
			}
		}
	}
}

// @Author jianghan
// @Description 个人私海线索即将达到预警提醒
// @Date 2024/4/10
func WillWarningByPp(posid int64) {
	title := "您的私海线索即将达到上限通知"
	content := "您的私海线索已超过%d条（不包含成交客户），即将达到私海线索上限%d条（不包含成交客户），请及时将无需跟进的线索退回公海，以避免无法接收新线索。"
	toMail := ""
	toCc := ""

	info := TiDb.SelectBySql(selfMail, posid)
	if info != nil && len(*info) > 0 {
		toMail = getMailAds(common.ObjToString((*info)[0]["mail"]), common.ObjToString((*info)[0]["name"]))
	}
	if toMail == "" {
		log.Println("未查询到邮箱地址, position id: ", posid)
		return
	}
	toCc = getCc(posid)
	content = fmt.Sprintf(content, cfg.WarningValue, cfg.AllocationCap)
	sendInfo(toMail, toCc, title, content)
}

// @Author jianghan
// @Description 部门人员私海线索即将达到预警提醒
// @Date 2024/4/10
func WillWarningByDm(dname, to string, toCc []string) {
	title := "“%s”私海线索即将达到上限通知"
	content := "“%s”的所有参与线索分配的电销人员，私海线索都已达到%d条（不包含成交客户），即将达到私海线索上限%d条（不包含成交客户），请及时通知电销人员将无需跟进的线索退回公海，以避免无法接收新线索。"
	title = fmt.Sprintf(title, dname)
	content = fmt.Sprintf(content, dname, cfg.WarningValue, cfg.AllocationCap)
	sendInfo(to, strings.Join(toCc, ","), title, content)
}

// @Author jianghan
// @Description 所有人私海线索即将达到预警提醒
// @Date 2024/4/10
func WillWarningByAll() {
	title := "所有电销人员私海线索即将达到上限通知"
	content := "所有参与线上线索自动分配的电销人员，私海线索都已达到%d条，请及时提醒销售部门将无需跟进的销售线索退回公海"
	var to []string // 销管
	for _, m2 := range *TiDb.SelectBySql(xgMail) {
		if m3 := getMailAds(common.ObjToString(m2["mail"]), common.ObjToString(m2["name"])); m3 != "" {
			to = append(to, m3)
		}
	}
	content = fmt.Sprintf(content, cfg.WarningValue)
	sendInfo(strings.Join(to, ","), "", title, content)
}

// @Author jianghan
// @Description 个人私海线索达到上限提醒
// @Date 2024/4/10
func ExceedLimitByPp(posid int64) {
	title := "您的私海线索已达上限通知"
	content := "您的私海线索已达到私海线索上限%d条（不包含成交客户），当前无法接收新线索，请及时将无需跟进的线索退回公海"

	toMail := ""
	toCc := ""
	content = fmt.Sprintf(content, cfg.AllocationCap)
	info := TiDb.SelectBySql(selfMail, posid)
	if info != nil && len(*info) > 0 {
		toMail = getMailAds(common.ObjToString((*info)[0]["mail"]), common.ObjToString((*info)[0]["name"]))
	}
	if toMail == "" {
		log.Println("未查询到邮箱地址, position id: ", posid)
		return
	}
	toCc = getCc(posid)
	sendInfo(toMail, toCc, title, content)
}

// @Author jianghan
// @Description  部门所有人私海线索达到上限提醒
// @Date 2024/4/10
func ExceedLimitByDm(dname, to string, toCc []string) {
	title := "“%s”私海线索已达到上限通知"
	content := "“%s”的所有参与线索分配的电销人员，私海线索都已达到私海线索上限%d条（不包含成交客户），当前无法接收新线索，请及时通知电销人员将无需跟进的线索退回公海"
	title = fmt.Sprintf(title, dname)
	content = fmt.Sprintf(content, dname, cfg.AllocationCap)
	sendInfo(to, strings.Join(toCc, ","), title, content)
}

// @Author jianghan
// @Description	自动释放私海线索通知
// @Date 2024/4/10
func AutoReleaseNots() {
	title := "自动释放私海线索通知"
	content := "所有参与线上线索自动分配的电销人员，私海线索都已达到%d条，为避免线上产生的新线索无法分配，系统已自动将线索状态为“商机线索（已建联，包含已拨打未接通）”、“潜在客户”、“沉睡客户”的线索退回公海，如有异议请联系销管处理；"

	var to []string
	for _, m := range *TiDb.SelectBySql(allMail) {
		if m1 := getMailAds(common.ObjToString(m["mail"]), common.ObjToString(m["name"])); m1 != "" {
			to = append(to, m1)
		}
	}
	var toCc []string // 销管+高级电销经理+超管
	for _, m2 := range *TiDb.SelectBySql(xgMail) {
		if m3 := getMailAds(common.ObjToString(m2["mail"]), common.ObjToString(m2["name"])); m3 != "" {
			toCc = append(toCc, m3)
		}
	}
	for _, m2 := range *TiDb.SelectBySql(cgMail) {
		if m3 := getMailAds(common.ObjToString(m2["mail"]), common.ObjToString(m2["name"])); m3 != "" {
			toCc = append(toCc, m3)
		}
	}
	toCc = append(toCc, getMailAds("shenbingyi@topnet.net.cn", "沈炳毅"))
	sendInfo(strings.Join(to, ","), strings.Join(toCc, ","), title, fmt.Sprintf(content, cfg.AllocationCap))
}

// @Author jianghan
// @Description  线上线索无法分配通知
// @Date 2024/4/10
func CantBeAssignedNots() {
	title := "【紧急】线上线索无法分配通知"
	content := "所有参与线上线索自动分配的电销人员，私海线索都已达到%d条，且私海已无“潜在客户”、“沉睡客户”、“商机线索（已建联）”可释放，当前线上线索无法分配，请尽快处理！"

	var to []string
	for _, m := range *TiDb.SelectBySql(allMail) {
		if m1 := getMailAds(common.ObjToString(m["mail"]), common.ObjToString(m["name"])); m1 != "" {
			to = append(to, m1)
		}
	}
	var toCc []string // 销管+高级电销经理+超管
	for _, m2 := range *TiDb.SelectBySql(xgMail) {
		if m3 := getMailAds(common.ObjToString(m2["mail"]), common.ObjToString(m2["name"])); m3 != "" {
			toCc = append(toCc, m3)
		}
	}
	for _, m2 := range *TiDb.SelectBySql(cgMail) {
		if m3 := getMailAds(common.ObjToString(m2["mail"]), common.ObjToString(m2["name"])); m3 != "" {
			toCc = append(toCc, m3)
		}
	}
	toCc = append(toCc, getMailAds("shenbingyi@topnet.net.cn", "沈炳毅"))
	sendInfo(strings.Join(to, ","), strings.Join(toCc, ","), title, fmt.Sprintf(content, cfg.AllocationCap))
}

// @Author jianghan
// @Description  客成移交线索失败提醒
// @Date 2024/4/10
func HandOverFail(posid int64, ent string) {
	title := "客成线索移交失败通知"
	content := "您的私海线索已超过%d条（不包含成交客户），已达到私海线索上限%d条（不包含成交客户），导致“%s”无法从客成到期自动移交至私海，请及时将无需跟进的线索退回公海，超过%d个工作日不处理，该线索将自动退回至公海"

	toMail := ""
	toCc := ""
	info := TiDb.SelectBySql(selfMail, posid)
	content = fmt.Sprintf(content, cfg.AllocationCap, cfg.AllocationCap, ent, cfg.ThawDay)
	if info != nil && len(*info) > 0 {
		toMail = getMailAds(common.ObjToString((*info)[0]["mail"]), common.ObjToString((*info)[0]["name"]))
	}
	if toMail == "" {
		log.Println("未查询到邮箱地址, position id: ", posid)
		return
	}
	toCc = getCc(posid)
	sendInfo(toMail, toCc, title, content)
}

// @Author jianghan
// @Description 订单用户创建线索失败提醒
// @Date 2024/4/10
func OrderCreateFail(posid int64, bname string) {
	title := "订单用户创建线索失败通知"
	content := "您的私海线索已超过%d条（不包含成交客户），已达到私海线索上限%d条（不包含成交客户），导致订单系统的用户“%s”无法进入您的私海，请及时将无需跟进的线索退回公海，超过%d个工作日不处理，该线索将自动进入至公海。"

	toMail := ""
	toCc := ""
	content = fmt.Sprintf(content, cfg.AllocationCap, cfg.AllocationCap, bname, cfg.ThawDay)
	info := TiDb.SelectBySql(selfMail, posid)
	if info != nil && len(*info) > 0 {
		toMail = getMailAds(common.ObjToString((*info)[0]["mail"]), common.ObjToString((*info)[0]["name"]))
	}
	if toMail == "" {
		log.Println("未查询到邮箱地址, position id: ", posid)
		return
	}
	toCc = getCc(posid)
	sendInfo(toMail, toCc, title, content)
}

// @Author jianghan
// @Description 非集团公司线索分配失败
// @Date 2024/5/11
func AssFail(posid int64, ent, iname string) {
	title := "您跟进的非集团公司线上线索分配失败"
	content := "您的私海线索已达到私海线索上限%d条（不包含成交客户），导致您正在跟进的非集团公司“%s”产生的新线索“%s”无法进入您的私海，请及时将无需跟进的线索退回公海，退回后联系您的电销经理将此线索再划转至您的私海。"

	toMail := ""
	toCc := ""
	content = fmt.Sprintf(content, cfg.AllocationCap, ent, iname)
	info := TiDb.SelectBySql(selfMail, posid)
	if info != nil && len(*info) > 0 {
		toMail = getMailAds(common.ObjToString((*info)[0]["mail"]), common.ObjToString((*info)[0]["name"]))
	}
	if toMail == "" {
		log.Println("未查询到邮箱地址, position id: ", posid)
		return
	}
	toCc = getCc(posid)
	sendInfo(toMail, toCc, title, content)
}

// @Author jianghan
// @Description 发邮件
// @Date 2024/5/8
func sendInfo(to, cs, title, content string) {
	if to == "" {
		log.Println("send mail fail", title, to)
		return
	}
	log.Println("发送标题", title)
	log.Println("发送人：", to)
	log.Println("抄送：", cs)
	gmail := &mail.GmailAuth{
		SmtpHost: "smtp.exmail.qq.com",
		SmtpPort: 465,
		User:     "public03@topnet.net.cn",
		Pwd:      "ue9Rg9Sf4CVtdm5a",
	}
	status := mail.GSendMail_dx("剑鱼标讯", to, cs, "", title, content, "", "", gmail)
	if status {
		log.Println("send mail success", title, to)
	}
}

// @Author jianghan
// @Description 获取抄送人员邮箱
//
//	销售三部 (SZ_PID1=5432)、销售一部（分组）
//
// @Date 2024/5/8
func getCc(posid int64) (to string) {
	info := TiDb.SelectBySql(infoSelf, posid)
	if info == nil && len(*info) == 0 {
		log.Println("未查询到个人信息, positionId: ", posid)
		return
	}
	if common.ObjToString((*info)[0]["dept_name"]) == "销售三部" {
		if common.IntAll((*info)[0]["role_id"]) == 1 || common.IntAll((*info)[0]["role_id"]) == 2 {
			m := TiDb.SelectBySql(topMail3)
			if m != nil && len(*m) > 0 {
				to = getMailAds(common.ObjToString((*m)[0]["mail"]), common.ObjToString((*m)[0]["name"]))
			}
		}
	} else {
		if common.IntAll((*info)[0]["role_id"]) == 1 || common.IntAll((*info)[0]["role_id"]) == 2 {
			m := TiDb.SelectBySql(topMail, posid)
			if m != nil && len(*m) > 0 {
				to = getMailAds(common.ObjToString((*m)[0]["mail"]), common.ObjToString((*m)[0]["name"]))
			}
			//to = getMailAds("shenbingyi@topnet.net.cn", "沈炳毅")
		} else if common.IntAll((*info)[0]["role_id"]) == 3 {
			to = getMailAds("shenbingyi@topnet.net.cn", "沈炳毅")
		}
	}
	return
}

// @Author jianghan
// @Description 获取配置文件邮箱地址
// @Date 2024/5/13
func getMailAds(mail, name string) string {
	log.Println("getMailAds", name, mail)
	if cfg.NameToMail != nil && len(cfg.NameToMail) > 0 {
		return cfg.NameToMail[name]
	} else {
		return mail
	}
}