datatools/doFreeClueSign/job/job.go

package job

import (
	"context"
	"doFreeClueSign/db"
	"doFreeClueSign/public"
	"github.com/gogf/gf/v2/encoding/gjson"
	"github.com/gogf/gf/v2/frame/g"
	"github.com/gogf/gf/v2/util/gconv"
	"time"
)

// LoadActivityUser 加载新活跃用户
func (jm *JobManager) LoadActivityUser() {
	var (
		ctx = context.TODO()
	)
	start, _ := time.ParseInLocation(time.DateTime, jm.lastRun.NewActivity, time.Local)
	val, ed := public.GetNewActiveUser(start)
	if len(val) > 0 {
		for i, msg := range val {
			if i%10 == 0 {
				g.Log().Infof(ctx, "JobManager.LoadActivityUser %d/%d", i, len(val))
			}
			if err := jm.FilterPayUserAndSaveDb(ctx, msg); err != nil {
				g.Log().Errorf(ctx, "JobManager.LoadActivityUser.FilterPayUserAndSaveDb %v", gconv.String(msg))
			}
		}
	}
	//更新
	jm.lastRun.NewActivity = ed.Format(time.DateTime)
	if err := jm.SaveLastRun(); err != nil {
		g.Log().Errorf(ctx, "LoadActivityUser error %v", err)
	}
}

// LoadBindPhoneUser 加载绑定手机号用户
func (jm *JobManager) LoadBindPhoneUser() {
	var (
		runNow = time.Now()
	)
	start, _ := time.ParseInLocation(time.DateTime, jm.lastRun.BindPhone, time.Local)
	val := public.GetBidPhoneUser(start, runNow)
	if len(val) > 0 {
		for i, msg := range val {
			if i%10 == 0 {
				g.Log().Infof(ctx, "JobManager.LoadBindPhoneUser %d/%d", i, len(val))
			}
			if err := jm.FilterPayUserAndSaveDb(ctx, msg); err != nil {
				g.Log().Errorf(ctx, "JobManager.LoadBindPhoneUser.FilterPayUserAndSaveDb %v", gconv.String(msg))
			}
		}
	}
	//更新
	jm.lastRun.BindPhone = runNow.Format(time.DateTime)
	if err := jm.SaveLastRun(); err != nil {
		g.Log().Errorf(ctx, "LoadBindPhoneUser error %v", err)
	}
}

// LoadAgainSubUser 加载再次关注用户
func (jm *JobManager) LoadAgainSubUser() {
	var (
		runNow = time.Now()
		ctx    = context.TODO()
	)
	start, _ := time.ParseInLocation(time.DateTime, jm.lastRun.AgainSub, time.Local)
	val := public.GetAgainSubUser(start, runNow)
	if len(val) > 0 {
		for i, msg := range val {
			if i%10 == 0 {
				g.Log().Infof(ctx, "JobManager.LoadAgainSubUser %d/%d", i, len(val))
			}
			if err := jm.FilterPayUserAndSaveDb(ctx, msg); err != nil {
				g.Log().Errorf(ctx, "JobManager.LoadAgainSubUser.FilterPayUserAndSaveDb %v", gconv.String(msg))
			}
		}
	}
	//更新
	jm.lastRun.AgainSub = runNow.Format(time.DateTime)
	if err := jm.SaveLastRun(); err != nil {
		g.Log().Errorf(ctx, "LoadAgainSubUser error %v", err)
	}
}

// @Author jianghan
// @Description 过滤订单 筛选活动注册用户
// @Date 2024/11/18
func loadOrder() {
	sql := `SELECT id, filter, order_code, product_type, user_id, user_phone, vip_starttime, vip_endtime FROM dataexport_order WHERE id > ? AND order_status = 1 ORDER BY id ASC`
	res, err := g.DB("jianyu").Query(ctx, sql, LastId)
	if err == nil && !res.IsEmpty() {
		for _, m := range res.List() {
			id := gconv.Int(m["id"])
			if id > LastId {
				LastId = id
			}
			jsonObj, err := gjson.DecodeToJson([]byte(gconv.String(m["filter"])))
			if err != nil {
				g.Log().Errorf(ctx, "loadOrder error %v", err)
				continue
			}
			filter := jsonObj.Map()
			userid := gconv.String(m["user_id"])
			now := time.Now().Format(time.DateTime)
			//vipEndTime := gconv.Time(m["vip_endtime"]).Unix()
			if remark := gconv.String(filter["remark"]); remark != "" {
				switch remark {
				case "邀请好友成功":
					g.DB("bi_service").Exec(ctx, `INSERT INTO user_statistics (userId, createTime, event) SELECT ?, ?, 1 WHERE NOT EXISTS ( SELECT 1 FROM user_statistics WHERE userId = ? AND event = 1)`, userid, now, userid)
				case "被邀请注册成功":
					g.DB("bi_service").Exec(ctx, `INSERT INTO user_statistics (userId, createTime, event) SELECT ?, ?, 2 WHERE NOT EXISTS ( SELECT 1 FROM user_statistics WHERE userId = ? AND event = 2)`, userid, now, userid)

					// 被邀请成功注册 送7天超级订阅订单
					//res, _ := db.MG.DB().FindById("user", userid, `{"l_vip_endtime":1}`)
					//userVEndtime := gconv.Int64((*res)["l_vip_endtime"])
					//if userVEndtime == vipEndTime && verifyDate(vipEndTime) {
					//	g.DB("bi_service").Exec(ctx, `INSERT INTO user_statistics (userId, createTime, event) SELECT ?, ?, 3 WHERE NOT EXISTS ( SELECT 1 FROM user_statistics WHERE userId = ? AND event = 3)`, userid, now, userid)
					//}
				case "2024年新用户注册赠送7天超级订阅":
					//res, _ := db.MG.DB().FindById("user", userid, `{"l_vip_endtime":1}`)
					//userVEndtime := gconv.Int64((*res)["l_vip_endtime"])
					//if userVEndtime == vipEndTime && verifyDate(vipEndTime) {
					//	g.DB("bi_service").Exec(ctx, `INSERT INTO user_statistics (userId, createTime, event) SELECT ?, ?, 3 WHERE NOT EXISTS ( SELECT 1 FROM user_statistics WHERE userId = ? AND event = 3)`, userid, now, userid)
					//}
				}
			}
		}
	}
	g.Log().Infof(ctx, "loadOrder end: %d", LastId)
}

func LoadOrderOther() {
	sql := `SELECT id, filter, order_code, product_type, user_id, user_phone, vip_starttime, vip_endtime FROM dataexport_order WHERE vip_endtime > ? AND vip_endtime < ? AND order_status = 1 AND (filter LIKE '%邀请好友成功%' OR filter LIKE '%被邀请注册成功%' OR filter LIKE '%2024年新用户注册赠送7天超级订阅%') ORDER BY id ASC`
	sql1 := `SELECT count(1) FROM dataexport_order WHERE vip_endtime > ? AND vip_endtime < ? AND order_status = 1 AND (filter LIKE '%邀请好友成功%' OR filter LIKE '%被邀请注册成功%' OR filter LIKE '%2024年新用户注册赠送7天超级订阅%')`
	sql2 := `SELECT id, vip_endtime FROM jianyu.dataexport_order WHERE (product_type = 'VIP订阅' OR product_type = '大会员') AND id > ? AND user_id = ? AND order_status = 1`
	now := time.Now().Format(time.DateTime)
	end := time.Now().AddDate(0, 0, 1).Format(time.DateTime)
	c, _ := g.DB("jianyu").GetCount(ctx, sql1, now, end)
	g.Log().Info(ctx, "LoadOrderOther count: ", c)
	res, err := g.DB("jianyu").Query(ctx, sql, now, end)
	if err == nil && !res.IsEmpty() {
		for _, m := range res.List() {
			id := gconv.Int64(m["id"])
			userid := gconv.String(m["user_id"])
			res1, err := g.DB("jianyu").Query(ctx, sql2, id, userid)
			if err == nil && res1.IsEmpty() {
				g.DB("bi_service").Exec(ctx, `INSERT INTO user_statistics (userId, createTime, event) SELECT ?, ?, 3 WHERE NOT EXISTS ( SELECT 1 FROM user_statistics WHERE userId = ? AND event = 3)`, userid, now, userid)
			}
		}
	}
}

// 验证vip到期时间一天内到期
func verifyDate(v int64) bool {
	if (v - time.Now().Unix()) < 24*60*60 {
		return true
	}
	return false
}

// @Author jianghan
// @Description 历史数据
// @Date 2024/11/19
func LoadOrderHis() {
	g.Log().Infof(ctx, "LoadOrderHis start")
	sql := `SELECT id, filter, order_code, product_type, user_id, user_phone, vip_starttime, vip_endtime FROM dataexport_order WHERE id <= ? AND order_status = 1 AND filter LIKE '%邀请好友成功%' ORDER BY id DESC`
	res, err := g.DB("jianyu").Query(ctx, sql, LastId)
	if err == nil && !res.IsEmpty() {
		for _, m := range res.List() {
			id := gconv.Int(m["id"])
			if id > LastId {
				LastId = id
			}
			userid := gconv.String(m["user_id"])
			now := time.Now().Format(time.DateTime)
			user, _ := db.MG.DB().FindById("user", userid, `{"i_vip_status":1, "i_member_status": 1}`)
			if user != nil && len(*user) > 0 {
				if gconv.Int((*user)["i_vip_status"]) <= 0 && gconv.Int((*user)["i_member_status"]) <= 0 {
					g.DB("bi_service").Exec(ctx, `INSERT INTO user_statistics (userId, createTime, event) SELECT ?, ?, 1 WHERE NOT EXISTS ( SELECT 1 FROM user_statistics WHERE userId = ? AND event = 1)`, userid, now, userid)
				}
			}
		}
	}
	g.Log().Info(ctx, "LoadOrderHis end")
}

// @Author jianghan
// @Description 11月9日注册成功
// @Date 2024/11/20
func LoadOrderHisMore() {
	g.Log().Info(ctx, "LoadOrderHisMore start")
	now := time.Now().Format(time.DateTime)
	sql := `SELECT id, filter, order_code, product_type, user_id, user_phone, vip_starttime, vip_endtime FROM dataexport_order WHERE order_status = 1 AND vip_endtime > '2024-11-16 00:00:00' AND vip_endtime < ? AND filter LIKE '%2024年新用户注册赠送7天超级订阅%' ORDER BY id DESC`
	sql1 := `SELECT id, vip_endtime FROM jianyu.dataexport_order WHERE (product_type = 'VIP订阅' OR product_type = '大会员') AND id > ? AND user_id = ? AND order_status = 1`
	res, err := g.DB("jianyu").Query(ctx, sql, now)
	if err == nil && !res.IsEmpty() {
		for _, m := range res.List() {
			id := gconv.Int64(m["id"])
			userid := gconv.String(m["user_id"])
			res1, err := g.DB("jianyu").Query(ctx, sql1, id, userid)
			if err == nil && res1.IsEmpty() {
				g.DB("bi_service").Exec(ctx, `INSERT INTO user_statistics (userId, createTime, event) SELECT ?, ?, 3 WHERE NOT EXISTS ( SELECT 1 FROM user_statistics WHERE userId = ? AND event = 3)`, userid, now, userid)
			}
		}
	}
	g.Log().Info(ctx, "LoadOrderHisMore end")
}