opms_backend/opms_parent/app/service/proj/business_cron.go

package proj

import (
	"context"
	projDao "dashoo.cn/micro/app/dao/proj"
	model "dashoo.cn/micro/app/model/proj"
	"dashoo.cn/micro/app/service"
	"database/sql"
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize"
	"github.com/gogf/gf/container/gset"
	"github.com/gogf/gf/frame/g"
	"github.com/gogf/gf/os/gcron"
	"github.com/gogf/gf/os/glog"
	"github.com/gogf/gf/os/gtime"
	"github.com/gogf/gf/text/gstr"
	"github.com/gogf/gf/util/gconv"
	"github.com/smallnest/rpcx/share"
	"strings"
	"time"
)

// 初始化，创建每10分钟执行一次的定时任务
func init() {
	// 定时任务
	spec := "0 0 9 * * *" // 每天凌晨9点执行
	gcron.AddSingleton(spec, businessFollowRun)
	//定时任务
	specs := "0 0 0 * * 1 *" //每周一一点
	gcron.AddSingleton(specs, businesspeopleUpReminder)
}

// businessFollowRun 项目跟进超时任务逻辑
func businessFollowRun() {
	tenant := g.Config().GetString("micro_srv.tenant")
	if tenant == "" {
		glog.Error("定时任务租户码未设置，请前往配置")
		return
	}
	// 从配置中获取消息提醒设置
	configs, err := g.DB(tenant).Model("sys_config").Where("config_key IN ('SalesDirector','SalesAssociate', 'ProductLineManager')").FindAll()
	if err != nil && err != sql.ErrNoRows {
		glog.Error(err)
		return
	}
	// 销售总监用户Id，销售助理用户Id，产品线经理用户Id
	salesDirector, salesAssociate, productLineManager := []string{}, []string{}, []string{}

	for _, config := range configs {
		if config["config_key"].String() == "SalesDirector" {
			salesDirector = strings.Split(config["config_value"].String(), ",")
		} else if config["config_key"].String() == "SalesAssociate" {
			salesAssociate = strings.Split(config["config_value"].String(), ",")
		} else if config["config_key"].String() == "ProductLineManager" {
			productLineManager = strings.Split(config["config_value"].String(), ",")
		}
	}

	if len(salesDirector) > 0 {
		go businessFollowOverdueSalesDirector(tenant, salesDirector)
	}
	if len(salesAssociate) > 0 {
		go businessFollowOverdueSalesAssociate(tenant, salesAssociate)
	}
	if len(productLineManager) > 0 {
		businessFollowOverdueProductLineManager(tenant, productLineManager)
	}

	go businessFollowOverdueSalesEngineer(tenant)
}

type FollowOverdue struct {
	SaleId   int64  `json:"saleId"`
	Count    int64  `json:"count"`
	BusNames string `json:"busNames"`
}

// 超期3天提醒销售总监
func businessFollowOverdueSalesDirector(tenant string, userIds []string) {
	now := gtime.Now().StartOfDay()
	LastWeekDay := now.AddDate(0, 0, -10)
	LastTwoWeekDay := now.AddDate(0, 0, -17)
	LastMonthDay := now.AddDate(0, -1, -3)
	businessA, businessB, businessC := new(FollowOverdue), new(FollowOverdue), new(FollowOverdue)

	err := projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekDay).
		Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&businessA)
	if err != nil {
		g.Log().Error("获取A类超期3天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekDay).
		Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&businessB)
	if err != nil {
		g.Log().Error("获取B类超期3天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthDay).
		Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&businessC)
	if err != nil {
		g.Log().Error("获取C类超期3天未跟进项目", err)
	}
	var msg string
	if businessA.Count > 0 {
		msg += fmt.Sprintf("您有超期3天未跟进A类项目%v个，项目名称分别为 %v，", businessA.Count, businessA.BusNames)
	}
	if businessB.Count > 0 {
		msg += fmt.Sprintf("您有超期3天未跟进B类项目%v个，项目名称分别为 %v，", businessB.Count, businessB.BusNames)
	}
	if businessC.Count > 0 {
		msg += fmt.Sprintf("您有超期3天未跟进C类项目%v个，项目名称分别为 %v，", businessC.Count, businessC.BusNames)
	}
	if msg != "" {
		businessNotifyMessage(userIds, gstr.TrimRightStr(msg, "，"))
	}
}

// 超期1天后,超期3天 提醒销售助理
func businessFollowOverdueSalesAssociate(tenant string, userIds []string) {
	now := gtime.Now().StartOfDay()
	LastWeekDay := now.AddDate(0, 0, -8)
	LastTwoWeekDay := now.AddDate(0, 0, -15)
	LastMonthDay := now.AddDate(0, -1, -1)

	LastWeekTridDay := now.AddDate(0, 0, -10)
	LastTwoWeekTridDay := now.AddDate(0, 0, -17)
	LastMonthTridDay := now.AddDate(0, -1, -3)

	businessATrid, businessBTrid, businessCTrid := new(FollowOverdue), new(FollowOverdue), new(FollowOverdue)
	businessA, businessB, businessC := new(FollowOverdue), new(FollowOverdue), new(FollowOverdue)

	err := projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekDay).
		Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&businessATrid)
	if err != nil {
		g.Log().Error("获取A类超期3天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekDay).
		Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(businessBTrid)
	if err != nil {
		g.Log().Error("获取B类超期3天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthDay).
		Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(businessCTrid)
	if err != nil {
		g.Log().Error("获取C类超期3天未跟进项目", err)
	}

	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekDay).WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekTridDay).
		Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(businessA)
	if err != nil {
		g.Log().Error("获取A类超期1天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekDay).WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekTridDay).
		Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(businessB)
	if err != nil {
		g.Log().Error("获取B类超期1天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthDay).WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthTridDay).
		Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(businessC)
	if err != nil {
		g.Log().Error("获取C类超期1天未跟进项目", err)
	}
	var msg string
	if businessATrid.Count > 0 {
		msg += fmt.Sprintf("您有超期3天未跟进A类项目%v个，项目名称分别为 %v，", businessATrid.Count, businessATrid.BusNames)
	}
	if businessBTrid.Count > 0 {
		msg += fmt.Sprintf("您有超期3天未跟进B类项目%v个，项目名称分别为 %v，", businessBTrid.Count, businessBTrid.BusNames)
	}
	if businessCTrid.Count > 0 {
		msg += fmt.Sprintf("您有超期3天未跟进C类项目%v个，项目名称分别为 %v，", businessCTrid.Count, businessCTrid.BusNames)
	}
	if businessA.Count > 0 {
		msg += fmt.Sprintf("您有超期1天未跟进A类项目%v个，项目名称分别为 %v，", businessA.Count, businessA.BusNames)
	}
	if businessB.Count > 0 {
		msg += fmt.Sprintf("您有超期1天未跟进B类项目%v个，项目名称分别为 %v，", businessB.Count, businessB.BusNames)
	}
	if businessC.Count > 0 {
		msg += fmt.Sprintf("您有超期1天未跟进C类项目%v个，项目名称分别为 %v，", businessC.Count, businessC.BusNames)
	}
	if msg != "" {
		businessNotifyMessage(userIds, gstr.TrimRightStr(msg, "，"))
	}
}

// 在到达超期时间前3天进行提醒、当天进行提醒，这两次提醒只提醒销售工程师, 超期1天后提醒
func businessFollowOverdueProductLineManager(tenant string, userIds []string) {
	now := gtime.Now().StartOfDay()
	// 超期一天
	LastWeekOneDay := now.AddDate(0, 0, -8)
	LastTwoWeekOneDay := now.AddDate(0, 0, -15)
	LastMonthOneDay := now.AddDate(0, -1, -1)
	// 超期当天
	LastWeekCurrentDay := now.AddDate(0, 0, -7)
	LastTwoWeekCurrentDay := now.AddDate(0, 0, -14)
	LastMonthCurrentDay := now.AddDate(0, -1, 0)
	//  在到达超期时间前3天进行提醒
	LastWeekBeforeTridDay := now.AddDate(0, 0, -4)
	LastTwoWeekBeforeTridDay := now.AddDate(0, 0, -11)
	LastMonthBeforeTridDay := now.AddDate(0, -1, 3)

	for _, userId := range userIds {
		LastWeekOne, LastTwoWeekOne, LastMonthOne := new(FollowOverdue), new(FollowOverdue), new(FollowOverdue)
		LastWeekCurrent, LastTwoWeekCurrent, LastMonthCurrent := new(FollowOverdue), new(FollowOverdue), new(FollowOverdue)
		LastWeekBeforeTrid, LastTwoWeekBeforeTrid, LastMonthBeforeTrid := new(FollowOverdue), new(FollowOverdue), new(FollowOverdue)

		dataScope, err := service.GetUserDataScope(gconv.Int(userId))
		if err != nil {
			g.Log().Error(fmt.Sprintf("定时任务项目超期提醒用户数据权限查询失败：userId-%v,", userId), err)
			continue
		}
		ctx := context.WithValue(context.TODO(), "contextService", g.Map{"dataScope": dataScope})

		err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
			WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekOneDay).DataScope(ctx, "sale_id").
			Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastWeekOne)
		if err != nil {
			g.Log().Error("获取A类超期一天未跟进项目", err)
		}
		err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
			WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekOneDay).DataScope(ctx, "sale_id").
			Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastTwoWeekOne)
		if err != nil {
			g.Log().Error("获取B类超期一天未跟进项目", err)
		}
		err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
			WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthOneDay).DataScope(ctx, "sale_id").
			Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastMonthOne)
		if err != nil {
			g.Log().Error("获取C类超期一天未跟进项目", err)
		}

		err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
			WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekCurrentDay).
			WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekOneDay).DataScope(ctx, "sale_id").
			Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastWeekCurrent)
		if err != nil {
			g.Log().Error("获取A类超期当天未跟进项目", err)
		}
		err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
			WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekCurrentDay).
			WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekOneDay).DataScope(ctx, "sale_id").
			Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastTwoWeekCurrent)
		if err != nil {
			g.Log().Error("获取B类超期当天未跟进项目", err)
		}
		err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
			WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthCurrentDay).
			WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthOneDay).DataScope(ctx, "sale_id").
			Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastMonthCurrent)
		if err != nil {
			g.Log().Error("获取C类超期当天未跟进项目", err)
		}

		err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
			WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekBeforeTridDay).
			WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekCurrentDay).DataScope(ctx, "sale_id").
			Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastWeekBeforeTrid)
		if err != nil {
			g.Log().Error("获取A类超期当天未跟进项目", err)
		}
		err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
			WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekBeforeTridDay).
			WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekCurrentDay).DataScope(ctx, "sale_id").
			Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastTwoWeekBeforeTrid)
		if err != nil {
			g.Log().Error("获取B类超期当天未跟进项目", err)
		}
		err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
			WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthBeforeTridDay).
			WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthCurrentDay).DataScope(ctx, "sale_id").
			Fields("count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastMonthBeforeTrid)
		if err != nil {
			g.Log().Error("获取C类超期当天未跟进项目", err)
		}

		var msg string
		if LastWeekOne.Count != 0 {
			msg += fmt.Sprintf("您有超期1天未跟进A类项目%v个，项目名称分别为 %v，", LastWeekOne.Count, LastWeekOne.BusNames)
		}
		if LastTwoWeekOne.Count != 0 {
			msg += fmt.Sprintf("您有超期1天未跟进B类项目%v个，项目名称分别为 %v，", LastTwoWeekOne.Count, LastTwoWeekOne.BusNames)
		}
		if LastMonthOne.Count != 0 {
			msg += fmt.Sprintf("您有超期1天未跟进C类项目%v个，项目名称分别为 %v，", LastMonthOne.Count, LastMonthOne.BusNames)
		}
		if LastWeekCurrent.Count != 0 {
			msg += fmt.Sprintf("您今天有超期未跟进A类项目%v个，项目名称分别为 %v，", LastWeekCurrent.Count, LastWeekCurrent.BusNames)
		}
		if LastTwoWeekCurrent.Count != 0 {
			msg += fmt.Sprintf("您今天有超期未跟进B类项目%v个，项目名称分别为 %v，", LastTwoWeekCurrent.Count, LastTwoWeekCurrent.BusNames)
		}
		if LastMonthCurrent.Count != 0 {
			msg += fmt.Sprintf("您今天有超期未跟进C类项目%v个，项目名称分别为 %v，", LastMonthCurrent.Count, LastMonthCurrent.BusNames)
		}
		if LastWeekBeforeTrid.Count != 0 {
			msg += fmt.Sprintf("您3天后有超期未跟进A类项目%v个，项目名称分别为 %v，", LastWeekBeforeTrid.Count, LastWeekBeforeTrid.BusNames)
		}
		if LastTwoWeekBeforeTrid.Count != 0 {
			msg += fmt.Sprintf("您3天后有超期未跟进B类项目%v个，项目名称分别为 %v，", LastTwoWeekBeforeTrid.Count, LastTwoWeekBeforeTrid.BusNames)
		}
		if LastMonthBeforeTrid.Count != 0 {
			msg += fmt.Sprintf("您3天后有超期未跟进C类项目%v个，项目名称分别为 %v，", LastMonthBeforeTrid.Count, LastMonthBeforeTrid.BusNames)
		}
		if msg != "" {
			businessNotifyMessage([]string{userId}, gstr.TrimRightStr(msg, "，"))
		}
	}
}

// 在到达超期时间前3天进行提醒、当天进行提醒，这两次提醒只提醒销售工程师, 超期1天后提醒
func businessFollowOverdueSalesEngineer(tenant string) {
	now := gtime.Now().StartOfDay()
	// 超期一天
	LastWeekOneDay := now.AddDate(0, 0, -8)
	LastTwoWeekOneDay := now.AddDate(0, 0, -15)
	LastMonthOneDay := now.AddDate(0, -1, -1)
	// 超期当天
	LastWeekCurrentDay := now.AddDate(0, 0, -7)
	LastTwoWeekCurrentDay := now.AddDate(0, 0, -14)
	LastMonthCurrentDay := now.AddDate(0, -1, 0)
	//  在到达超期时间前3天进行提醒
	LastWeekBeforeTridDay := now.AddDate(0, 0, -4)
	LastTwoWeekBeforeTridDay := now.AddDate(0, 0, -11)
	LastMonthBeforeTridDay := now.AddDate(0, -1, 3)

	LastWeekOneCount, LastTwoWeekOneCount, LastMonthOneCount := make([]FollowOverdue, 0), make([]FollowOverdue, 0), make([]FollowOverdue, 0)
	LastWeekCurrentCount, LastTwoWeekCurrentCount, LastMonthCurrentCount := make([]FollowOverdue, 0), make([]FollowOverdue, 0), make([]FollowOverdue, 0)
	LastWeekBeforeTridCount, LastTwoWeekBeforeTridCount, LastMonthBeforeTridCount := make([]FollowOverdue, 0), make([]FollowOverdue, 0), make([]FollowOverdue, 0)
	err := projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekOneDay).Group("sale_id").OrderAsc("sale_id").
		Fields("sale_id, count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastWeekOneCount)
	if err != nil {
		g.Log().Error("获取A类超期一天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekOneDay).Group("sale_id").OrderAsc("sale_id").
		Fields("sale_id, count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastTwoWeekOneCount)
	if err != nil {
		g.Log().Error("获取B类超期一天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthOneDay).Group("sale_id").OrderAsc("sale_id").
		Fields("sale_id, count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastMonthOneCount)
	if err != nil {
		g.Log().Error("获取C类超期一天未跟进项目", err)
	}

	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekCurrentDay).
		WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekOneDay).Group("sale_id").OrderAsc("sale_id").
		Fields("sale_id, count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastWeekCurrentCount)
	if err != nil {
		g.Log().Error("获取A类超期当天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekCurrentDay).
		WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekOneDay).Group("sale_id").OrderAsc("sale_id").
		Fields("sale_id, count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastTwoWeekCurrentCount)
	if err != nil {
		g.Log().Error("获取B类超期当天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthCurrentDay).
		WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthOneDay).Group("sale_id").OrderAsc("sale_id").
		Fields("sale_id, count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastMonthCurrentCount)
	if err != nil {
		g.Log().Error("获取C类超期当天未跟进项目", err)
	}

	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekBeforeTridDay).
		WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekCurrentDay).Group("sale_id").OrderAsc("sale_id").
		Fields("sale_id, count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastWeekBeforeTridCount)
	if err != nil {
		g.Log().Error("获取A类超期当天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekBeforeTridDay).
		WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekCurrentDay).Group("sale_id").OrderAsc("sale_id").
		Fields("sale_id, count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastTwoWeekBeforeTridCount)
	if err != nil {
		g.Log().Error("获取B类超期当天未跟进项目", err)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthBeforeTridDay).
		WhereGTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthCurrentDay).Group("sale_id").OrderAsc("sale_id").
		Fields("sale_id, count(id) as count, group_concat(nbo_name) as busNames").Scan(&LastMonthBeforeTridCount)
	if err != nil {
		g.Log().Error("获取C类超期当天未跟进项目", err)
	}

	allSaleIds := gset.NewIntSet(true)
	saleIds, LastWeekOneCountMap := handleSalesEngineerData(LastWeekOneCount)
	allSaleIds = allSaleIds.Union(saleIds)
	saleIds, LastTwoWeekOneCountMap := handleSalesEngineerData(LastTwoWeekOneCount)
	allSaleIds = allSaleIds.Union(saleIds)
	saleIds, LastMonthOneCountMap := handleSalesEngineerData(LastMonthOneCount)
	allSaleIds = allSaleIds.Union(saleIds)
	saleIds, LastWeekCurrentCountMap := handleSalesEngineerData(LastWeekCurrentCount)
	allSaleIds = allSaleIds.Union(saleIds)
	saleIds, LastTwoWeekCurrentCountMap := handleSalesEngineerData(LastTwoWeekCurrentCount)
	allSaleIds = allSaleIds.Union(saleIds)
	saleIds, LastMonthCurrentCountMap := handleSalesEngineerData(LastMonthCurrentCount)
	allSaleIds = allSaleIds.Union(saleIds)
	saleIds, LastWeekBeforeTridCountMap := handleSalesEngineerData(LastWeekBeforeTridCount)
	allSaleIds = allSaleIds.Union(saleIds)
	saleIds, LastTwoWeekBeforeTridCountMap := handleSalesEngineerData(LastTwoWeekBeforeTridCount)
	allSaleIds = allSaleIds.Union(saleIds)
	saleIds, LastMonthBeforeTridCountMap := handleSalesEngineerData(LastMonthBeforeTridCount)
	allSaleIds = allSaleIds.Union(saleIds)
	for _, saleId := range allSaleIds.Slice() {
		var msg string
		if LastWeekOneCountMap[saleId].Count != 0 {
			msg += fmt.Sprintf("您有超期1天未跟进A类项目%v个，项目名称分别为 %v，", LastWeekOneCountMap[saleId].Count, LastWeekOneCountMap[saleId].BusNames)
		}
		if LastTwoWeekOneCountMap[saleId].Count != 0 {
			msg += fmt.Sprintf("您有超期1天未跟进B类项目%v个，项目名称分别为 %v，", LastTwoWeekOneCountMap[saleId].Count, LastTwoWeekOneCountMap[saleId].BusNames)
		}
		if LastMonthOneCountMap[saleId].Count != 0 {
			msg += fmt.Sprintf("您有超期1天未跟进C类项目%v个，项目名称分别为 %v，", LastMonthOneCountMap[saleId].Count, LastMonthOneCountMap[saleId].BusNames)
		}
		if LastWeekCurrentCountMap[saleId].Count != 0 {
			msg += fmt.Sprintf("您今天有超期未跟进A类项目%v个，项目名称分别为 %v，", LastWeekCurrentCountMap[saleId].Count, LastWeekCurrentCountMap[saleId].BusNames)
		}
		if LastTwoWeekCurrentCountMap[saleId].Count != 0 {
			msg += fmt.Sprintf("您今天有超期未跟进B类项目%v个，项目名称分别为 %v，", LastTwoWeekCurrentCountMap[saleId].Count, LastTwoWeekCurrentCountMap[saleId].BusNames)
		}
		if LastMonthCurrentCountMap[saleId].Count != 0 {
			msg += fmt.Sprintf("您今天有超期未跟进C类项目%v个，项目名称分别为 %v，", LastMonthCurrentCountMap[saleId].Count, LastMonthCurrentCountMap[saleId].BusNames)
		}
		if LastWeekBeforeTridCountMap[saleId].Count != 0 {
			msg += fmt.Sprintf("您3天后有超期未跟进A类项目%v个，项目名称分别为 %v，", LastWeekBeforeTridCountMap[saleId].Count, LastWeekBeforeTridCountMap[saleId].BusNames)
		}
		if LastTwoWeekBeforeTridCountMap[saleId].Count != 0 {
			msg += fmt.Sprintf("您3天后有超期未跟进B类项目%v个，项目名称分别为 %v，", LastTwoWeekBeforeTridCountMap[saleId].Count, LastTwoWeekBeforeTridCountMap[saleId].BusNames)
		}
		if LastMonthBeforeTridCountMap[saleId].Count != 0 {
			msg += fmt.Sprintf("您3天后有超期未跟进C类项目%v个，项目名称分别为 %v，", LastMonthBeforeTridCountMap[saleId].Count, LastMonthBeforeTridCountMap[saleId].BusNames)
		}
		if msg != "" {
			businessNotifyMessage([]string{gconv.String(saleId)}, gstr.TrimRightStr(msg, "，"))
		}
	}
}

func handleSalesEngineerData(countData []FollowOverdue) (saleIds *gset.IntSet, data map[int]FollowOverdue) {
	saleIds = gset.NewIntSet(true)
	data = make(map[int]FollowOverdue)
	for _, v := range countData {
		saleId := gconv.Int(v.SaleId)
		saleIds.Add(saleId)
		data[saleId] = v
	}
	return
}

// 项目跟进的消息通知
func businessNotifyMessage(userIds []string, message string) {
	ids := strings.Join(userIds, ",")
	// 调用统一的消息通知方式
	notifyMessage(ids, message)
}

// notifyMessage 发送消息通知
func notifyMessage(ids, message string) {
	msg := g.MapStrStr{
		"msgTitle":    "项目跟进超期提醒",
		"msgContent":  message,
		"msgType":     "20",
		"recvUserIds": ids,
		"msgStatus":   "10",
		"sendType":    "10,30",
	}
	if err := service.CreateSystemMessage(msg); err != nil {
		glog.Error("消息提醒异常：", err)
	}
}

// 项目每周进行一次超期三天未跟进统计表，每周给销售助理发一次邮件
func businesspeopleUpReminder() {

	tenant := g.Config().GetString("micro_srv.tenant")
	if tenant == "" {
		glog.Error("定时任务租户码未设置，请前往配置")
		return
	}
	// 从配置中获取消息提醒设置
	configs, err := g.DB(tenant).Model("sys_config").Where("config_key IN ('SalesDirector','SalesAssociate')").FindAll()
	if err != nil && err != sql.ErrNoRows {
		glog.Error(err)
		return
	}
	// 销售助理用户Id
	salesAssociate := []string{}
	for _, config := range configs {
		if config["config_key"].String() == "SalesAssociate" {
			salesAssociate = strings.Split(config["config_value"].String(), ",")
		}
	}
	var list []*model.ProjBusinessRes
	var listA []*model.ProjBusinessRes
	var listB []*model.ProjBusinessRes
	var listC []*model.ProjBusinessRes
	now := gtime.Now().StartOfDay()
	LastWeekDay := now.AddDate(0, 0, -8)
	LastTwoWeekDay := now.AddDate(0, 0, -15)
	LastMonthDay := now.AddDate(0, -1, -1)
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusA).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastWeekDay).OrderDesc("id").Scan(&listA)
	if err != nil {
		g.Log().Error("获取A类超期3天未跟进项目", err)
	}
	if len(listA) > 0 {
		list = append(list, listA...)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusB).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastTwoWeekDay).OrderDesc("id").Scan(&listB)
	if err != nil {
		g.Log().Error("获取B类超期3天未跟进项目", err)
	}
	if len(listB) > 0 {
		list = append(list, listB...)
	}
	err = projDao.NewProjBusinessDao(tenant).Where(projDao.ProjBusiness.C.NboType, StatusC).
		WhereLTE(projDao.ProjBusiness.C.FinalFollowTime, LastMonthDay).OrderDesc("id").Scan(&listC)
	if err != nil {
		g.Log().Error("获取C类超期3天未跟进项目", err)
	}
	if len(listC) > 0 {
		list = append(list, listC...)
	}
	if err != nil {
		g.Log().Error("获取未跟进项目", err)
	}
	ctx := context.WithValue(context.TODO(), share.ReqMetaDataKey, map[string]string{"tenant": tenant})
	rsp, err := service.GetDictDataByType(ctx, "proj_nbo_type")
	if err != nil {
		g.Log().Error("项目类别数据字典", err)
	}
	if err != nil {
		g.Log().Error("获取未跟进项目", err)
		return
	}
	var msg string
	if len(list) > 0 {
		msg += fmt.Sprintf("您有超期3天未跟进项目%v个,请登录系统查看", len(list))
	}
	f := excelize.NewFile()
	f.MergeCell("Sheet1", "A1", "D1")
	style, _ := f.NewStyle(`{"alignment":{"horizontal":"center"}}`)
	f.SetCellValue("Sheet1", "A1", "超期三天未跟进项目")
	f.SetCellStyle("sheet1", "A1", "D1", style)
	f.SetColWidth("Sheet1", "A", "K", 20)
	f.SetCellValue("Sheet1", "A2", "序号")
	f.SetCellValue("Sheet1", "B2", "项目名称")
	f.SetCellValue("Sheet1", "C2", "项目类别")
	f.SetCellValue("Sheet1", "D2", "最新跟进时间")
	line := 2
	if len(list) > 0 {
		for _, v := range list {
			line++
			f.SetCellValue("Sheet1", fmt.Sprintf("A%d", line), gconv.String(line-2))
			f.SetCellValue("Sheet1", fmt.Sprintf("B%d", line), v.NboName)
			if len(rsp) > 0 {
				f.SetCellValue("Sheet1", fmt.Sprintf("C%d", line), rsp[v.NboType])
			} else {
				f.SetCellValue("Sheet1", fmt.Sprintf("C%d", line), v.NboType)
			}
			f.SetCellValue("Sheet1", fmt.Sprintf("D%d", line), v.FinalFollowTime)

		}
	}
	dir := g.Config().GetString("file.cronstatic")
	filename := "项目跟进" + gconv.String(time.Now().UnixNano()) + ".xlsx"
	path := dir + filename
	if err = f.SaveAs(path); err != nil {
		g.Log().Error("Excel保存失败", err)
	}
	if len(salesAssociate) > 0 {
		for _, val := range salesAssociate {
			if val == "" {
				continue
			}
			msgs := g.MapStrStr{
				"msgTitle":    "超期3天未跟进项目提醒",
				"msgContent":  msg,
				"recvUserIds": "1",
				"recvUser":    "系统管理员",
				"opnUrl":      path,
			}
			if err = service.GSendMail(msgs); err != nil {
				g.Log().Error("SendMail() error = %v", err)
			}
		}

	}

}