chunhao_admin/controller/v1/test.go

package v1

import (
	"context"
	"designs/config"
	"designs/crons"
	"designs/global"
	"designs/model"
	"designs/response"
	"designs/service"
	"fmt"
	"github.com/gin-gonic/gin"
	"go.mongodb.org/mongo-driver/v2/bson"
	"gorm.io/gorm/clause"
	"os"
	"strconv"
	"time"
)

type logData struct {
	LogTime time.Time `json:"logTime" gorm:"column:logTime;"`
	Type    int       `json:"Type"`
}

func calculateUserOnlineTime(logData []logData) int64 {

	var lastLog int64
	var isStart bool
	var onlineTimeTotal int64
	for k, v := range logData {
		logTime := v.LogTime.Unix()
		//如果跟上一次的记录隔的时间超过6分钟，就认为是之前断线了，属于无效数据
		if k > 0 && logData[k-1].LogTime.Unix()+60*6 < logTime {
			isStart = false
			continue
		}

		if v.Type == 1 && isStart == false {
			isStart = true
			lastLog = v.LogTime.Unix()
			continue
		}

		if v.Type == 1 && isStart == true {
			//logTime := v.LogTime.Unix()
			onlineTimeTotal = onlineTimeTotal + (logTime - lastLog)
			lastLog = logTime
			//如果下一次的心跳，间隔时间大于6分钟了，就可以认为，这次就算是结束了
			if k+1 == len(logData) || logData[k+1].LogTime.Unix() > logTime+60*6 {
				isStart = false
			}
			continue
		}

		if v.Type == 2 && isStart == true {
			//logTime := v.LogTime.Unix()
			onlineTimeTotal = onlineTimeTotal + (logTime - lastLog)

			isStart = false
			continue
		}

	}

	return onlineTimeTotal
}

func SetUserBehaviorBak(c *gin.Context) {
	//读取出来user数据
	var users []model.User
	global.App.DB.Table("user").
		Where("pf", "=", "tt").
		Where("gid", "=", "linkup").
		Where("createdAt", ">=", "2024-11-12").
		Where("createdAt", "<=", "2024-11-13").Scan(&users)

	collection := global.App.MongoDB.Database("chunhao").Collection("adRelated")
	for _, user := range users {
		//查询出reids中的openId
		userIdKeys := fmt.Sprintf("%s:%s:%s:%v", user.Gid, user.Pf, config.Get("app.user_table_id"), strconv.Itoa(user.UserId))

		userIdData, _ := global.App.Redis.HGetAll(context.Background(), userIdKeys).Result()

		openId := userIdData["openId"]
		//openId = strconv.Itoa(user.UserId)
		//查询出在线时长数据
		var LogData []logData
		global.App.DB.Table("user_online").
			Where("pf", user.Pf).
			Where("gid", user.Gid).
			Where("userId", user.UserId).Scan(&LogData)

		duration := calculateUserOnlineTime(LogData)
		//查询出看广告数据
		var req_count int64
		var exp_count int64
		global.App.DB.Table("user_see_ads").
			Where("pf", user.Pf).
			Where("gid", user.Gid).
			Where("userId", user.UserId).Count(&req_count)
		global.App.DB.Table("user_see_ads").
			Where("pf", user.Pf).
			Where("gid", user.Gid).
			Where("adState", 2).
			Where("userId", user.UserId).Count(&exp_count)
		//查询出启动次数
		var start_num int64
		global.App.DB.Table("user_login").
			Where("pf", user.Pf).
			Where("gid", user.Gid).
			Where("userId", user.UserId).Count(&start_num)

		//存入mongo
		adData := struct {
			Id         string `bson:"_id" json:"_id"`
			UserId     string `bson:"userId" json:"userId"`
			Aid        int64  `json:"aid" bson:"aid"` //广告的推广计划id,即广告ID，广告平台配置落地页参数
			Cid        string `json:"cid" bson:"cid"` //openid的用户点击aid广告进入游戏时的唯一标识,广告平台提供
			Pid        int64  `json:"pid" bson:"pid"` //广告的项目id(仅巨量引擎存在,腾讯广告时不存在该值),广告平台配置落地页参数
			CreateTime int64  `bson:"create_time"`    //当前计划的创建时间

			StartNum int     `bson:"startNum" json:"startNum"`   //启动次数
			Revenue  float32 `bson:"revenue" json:"revenue"`     //当日预估收益
			Duration int64   `bson:"duration" json:"duration"`   //当日在线时长
			ReqCount int     `bson:"req_count" json:"req_count"` //当日的激励视频广告请求次数
			ExpCount int     `bson:"exp_count" json:"exp_count"` //当日的激励视频广告曝光次数

		}{
			UserId:     user.Gid + "|" + user.Pf + "|" + openId,
			Id:         user.Gid + "|" + user.Pf + "|" + openId + "|" + "7436301890621997000",
			Aid:        7436301890621997000,
			Pid:        0,
			Cid:        "",
			Duration:   duration,
			StartNum:   int(start_num),
			Revenue:    0,
			ReqCount:   int(req_count),
			ExpCount:   int(exp_count),
			CreateTime: user.CreatedAt.Unix(),
		}
		collection.InsertOne(context.Background(), adData)
		fmt.Println(user.Gid+"|"+user.Pf+"|"+openId, "数据写入完成\n")
	}

}

func DeleteUserBehaviorBak(c *gin.Context) {
	collection := global.App.MongoDB.Database("chunhao").Collection("userBehavior")
	filter := bson.M{"create_time": bson.M{"$gt": 1}}
	collection.DeleteMany(context.Background(), filter)

	response.Success(c, gin.H{})
}

func GetUserNickName(c *gin.Context) {
	//读取user表
	var count int64
	err := global.App.DB.Table("user").Count(&count).Error
	if err != nil {
		response.Fail(c, 1001, err.Error())
		return
	}

	var i int64
	for i = 1; i <= count; i++ {
		var user model.User
		err = global.App.DB.Table("user").Where("id", i).Find(&user).Error
		if err != nil {
			response.Fail(c, 1001, err.Error())
			return
		}
		if user.Pf == "web" {
			//web跳过
			continue
		}

		userKey := user.Gid + ":" + user.Pf + ":" + config.Get("app.user_table_key") + user.OpenId
		userData, err := global.App.Redis.HGetAll(context.Background(), userKey).Result()
		if err != nil {
			global.App.Log.Error("GetUserData err")
			response.Fail(c, 1003, "GetUserData err"+err.Error())
			return
		}

		if userData["head"] != "" {
			//有意义的数据
			//data := struct {
			//	ID       int    `json:"id" gorm:"not null;"`
			//	Head     string `json:"head" gorm:"not null;"`
			//	NickName string `json:"nickName" gorm:"not null;column:nickName;"`
			//}{
			//	Head:     userData["head"],
			//	NickName: userData["nickName"],
			//}
			//err = global.App.DB.Table("nickname").Create(&data).Error
			//if err != nil {
			//	response.Fail(c, 1003, "nickname err"+err.Error())
			//	return
			//}

		}
	}

	response.Success(c, gin.H{})
}

// 定义一个函数来执行任务
func WriteDBDataToFile(c *gin.Context) {
	// 打开文件准备写入
	file, err := os.Create("./nickname.txt")
	if err != nil {
		response.Fail(c, 1001, fmt.Errorf("无法创建文件: %v", err))
	}
	defer file.Close()

	// 查询数据库
	var data []struct {
		ID       int    `json:"id" gorm:"not null;"`
		Head     string `json:"head" gorm:"not null;"`
		NickName string `json:"nickName" gorm:"not null;column:nickName;"`
	}

	global.App.DB.Table("nickname").Scan(&data)

	// 写入列名到文件（可选）
	for _, v := range data {
		fmt.Println(v)
		fmt.Fprintf(file, "%s\t%s\n", v.NickName, v.Head)
	}

	response.Success(c, gin.H{})
}

// 对打点数据进行汇算
func ActionDataSummary(c *gin.Context) {

	now := time.Now()
	for i := 1; i <= 30; i++ {
		lastDay := now.AddDate(0, 0, -i).Format("20060102")

		service.SeeAdsSummary(lastDay)

		fmt.Println("用时", time.Since(now))
	}

	response.Success(c, gin.H{})
}

func BehaviorTimeSummary(c *gin.Context) {
	//先查出所有gid
	ActiveGid := service.GetActiveGid()

	//现在直接汇总所有的数据（直到前一天的）
	now := time.Now()
	end := now.AddDate(0, 0, -1)
	start, _ := time.Parse("2006-01-02 15:04:05", "2025-05-17 15:04:05")

	crons.BehaviorSummary("wx", ActiveGid, start, end)

	global.App.Log.Info("重新汇总用户在线时长数据完成")

	response.Success(c, gin.H{})
}

func BehaviorSummary(c *gin.Context) {

	//先查出所有gid
	ActiveGid := service.GetActiveGid()

	now := time.Now().AddDate(0, 0, -1)
	start := now.AddDate(0, 0, -45)

	pf := "wx"
	//循环一下，查出userId 跟相关的在线数据
	for _, gid := range ActiveGid {

		start1 := time.Now()
		fmt.Println("处理数据开始,gid :", gid)

		//查询 30天内的在线数据

		SummaryData, err := service.UserOnlineSummary(gid, pf, "", start.Format("2006-01-02 15:04:05"), now.Format("2006-01-02 15:04:05"))
		if err != nil {
			response.Fail(c, 1001, err.Error())
			return
		}

		//aaa, _ := json.Marshal(SummaryData)
		//global.App.Log.Error(string(aaa))
		//
		//return

		fmt.Println("查询在线数据完成：", time.Since(start1))
		//查询用户的登录次数
		var loginData []struct {
			UserId     int `json:"userId" gorm:"not null;column:userId;"`
			LoginCount int `json:"loginCount" gorm:"not null;column:loginCount;"`
		}
		loginDataMap := make(map[int]int)

		err = global.App.DB.Table("user_login").
			Where("gid", gid).
			Where("pf", pf).
			Group("userId").
			Select("count(*) as loginCount", "userId").
			Scan(&loginData).Error
		if err != nil {
			response.Fail(c, 1002, err.Error())
			return
		}

		for _, login := range loginData {
			loginDataMap[login.UserId] = login.LoginCount
		}

		fmt.Println("查询登录数据完成：", time.Since(start1))

		//查询用户的看广告次数，广告看完次数
		var seeAdsData []struct {
			UserId      int `json:"userId" gorm:"not null;column:userId;"`
			SeeAdsCount int `json:"seeAdsCount" gorm:"not null;column:seeAdsCount;"`
			State2Count int `json:"state2Count" gorm:"not null;column:state2Count;"`
		}

		seeAdsDataMap := make(map[int]int)
		seeAdsDataMap2 := make(map[int]int)
		err = global.App.DB.Table("user_see_ads").
			Where("gid", gid).
			Where("pf", pf).
			Group("userId").
			Select("count(*) as seeAdsCount", "SUM(CASE WHEN adsState = 2 THEN 1 ELSE 0 END) AS state2Count", "userId").
			Scan(&seeAdsData).Error
		if err != nil {
			response.Fail(c, 1003, err.Error())
			return
		}

		for _, ads := range seeAdsData {
			seeAdsDataMap[ads.UserId] = ads.SeeAdsCount
			seeAdsDataMap2[ads.UserId] = ads.State2Count
		}

		fmt.Println("查询广告数据完成：", time.Since(start1))

		//格式化，存入数据库
		var userIdList []model.User
		err = global.App.DB.Table("user").Where("gid", gid).
			Select("id", "userId").
			Where("pf", pf).Scan(&userIdList).Error
		if err != nil {
			response.Fail(c, 1004, err.Error())
			return
		}

		//fmt.Println(len(userIdList))

		userBehavior := make(map[int]model.UserBehavior)
		for _, users := range userIdList {
			userBehavior[users.UserId] = model.UserBehavior{
				ID:         users.ID,
				Duration:   int(SummaryData[users.UserId]),
				StartNum:   loginDataMap[users.UserId],
				AdCount:    seeAdsDataMap[users.UserId],
				AdExpCount: seeAdsDataMap2[users.UserId],
			}
		}

		var userBehaviorList []model.UserBehavior
		for _, Behavior := range userBehavior {
			userBehaviorList = append(userBehaviorList, Behavior)
		}

		err = global.App.DB.Table("user_behavior").Clauses(clause.OnConflict{
			Columns:   []clause.Column{{Name: "id"}},                                                       // 冲突列（主键）
			DoUpdates: clause.AssignmentColumns([]string{"duration", "startNum", "adCount", "adExpCount"}), // 需要更新的列
		}).CreateInBatches(&userBehaviorList, 1000).Error
		if err != nil {
			//global.App.Log.Error(userBehavior)
			response.Fail(c, 1005, err.Error())
			return
		}

		fmt.Println("存入汇总完成：", time.Since(start1))

	}

}

func TestClickHouse(c *gin.Context) {

	sql := "CREATE TABLE user_see_ads\n(\n    `pf` LowCardinality(String) COMMENT '登录路径',\n    `gid` LowCardinality(String) COMMENT '游戏ID',\n    `userId` UInt32 COMMENT '用户ID',\n    `date` Date COMMENT '日期',\n    `createdAt` DateTime DEFAULT now() COMMENT '时间',\n    `adsId` Nullable(String) COMMENT '广告ID',\n    \n    -- 修复字段声明顺序：Nullable 应包裹 LowCardinality\n    `adsType` LowCardinality(Nullable(String)) COMMENT '类型',\n    `adsScene` LowCardinality(Nullable(String)) COMMENT '广告场景',\n    \n    `adsState` Nullable(Int8) COMMENT '广告状态: 0失败 1成功 2完整观看',\n    `startTime` Nullable(DateTime) COMMENT '广告开始时间',\n    `cost` UInt32 DEFAULT 0 COMMENT '广告收益'\n)\nENGINE = MergeTree()\nPARTITION BY (toYear(date), toMonth(date))\nORDER BY (date, pf, gid, userId)\nPRIMARY KEY (date, pf, gid)\nSETTINGS \n    index_granularity = 8192,\n    index_granularity_bytes = 10485760;\n"
	err := global.App.Clickhouse.Exec(sql).Error

	response.Success(c, gin.H{
		"res": err,
	})
}