cadmin/server/internal/admin/service/player.go

package service

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"gadmin/config"
	"gadmin/internal/admin/forms"
	"gadmin/internal/gorm/query"
	"gadmin/package/gmdata"
	"strconv"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/sirupsen/logrus"
	"github.com/xuri/excelize/v2"
)

type PlayerService struct {
	ctx *gin.Context
}

const (
	batchSize = 1000
)

func NewPlayerService(ctx *gin.Context) *PlayerService {
	return &PlayerService{
		ctx: ctx,
	}
}

func (p *PlayerService) ExportStatistics(req forms.StatisticsExportReq) {

	// 调用本函数会导致线上服务器重启，暂屏蔽
	return

	for i := 2; i >= 0; i-- {
		searchDay := time.Now().AddDate(0, -i, 0)
		//用户属性
		playerAttrList, err := p.getUserInfoByMonth(searchDay)
		if err != nil {
			logrus.WithField("method", "ExportStatistics").Errorf("获取用户属性失败: %v", err)
			return
		}
		if len(playerAttrList) == 0 {
			return
		}
		//用户充值数据
		playerRechargeMap, err := p.getUserRechargeData(searchDay)
		if err != nil {
			logrus.WithField("method", "ExportStatistics").Errorf("获取用户充值数据失败: %v", err)
			return
		}

		//用户属性数据清洗转换
		playerInfoList := make([]*forms.PlayerInfo, len(playerAttrList))
		// ====定义玩家参数map===
		var equipMap map[string]int64      //装备
		var roleArr []forms.PlayerRole     //角色
		var passRoom []forms.PlayerChapter //关卡记录
		var bodyArr []forms.PlayerBody     //部位
		var talentMap map[string]int       //天赋
		var treasuresArr []forms.PlayerTreasure
		// ====定义玩家参数map===
		for n, item := range playerAttrList {
			//处理数据
			playerInfo := &forms.PlayerInfo{
				PlayerId:       item.PlayerId,
				AccID:          item.AccID,
				NickName:       item.NickName,
				ServerID:       int(item.ServerID),
				Level:          item.Level,
				Exp:            item.Exp,
				Stamina:        item.Stamina,
				Coin:           item.Coin,
				Diamond:        int64(item.Diamond),
				Lastlogintick:  time.Unix(item.Lastlogintick, 0).Format("2006-01-02 15:04:05"),
				Lastlogouttick: time.Unix(item.Lastlogouttick, 0).Format("2006-01-02 15:04:05"),
				CreateTime:     item.CreateTime.Format("2006-01-02 15:04:05"),
			}
			//玩家武器
			if item.FastEquips != "" {
				err = json.Unmarshal([]byte(item.FastEquips), &equipMap)
				if err != nil {
					logrus.WithField("method", "getUserExtraData").Errorf("玩家武器数据json umarshal错误: %v", err)
					return
				}
				playerWear := make([]gmdata.PlayerWear, 0)
				for _, equipId := range equipMap {
					equip := gmdata.PlayerWears[equipId]
					playerWear = append(playerWear, equip)
				}
				marshal, err := json.Marshal(playerWear)
				if err != nil {
					return
				}
				playerInfo.PlayerWear = string(marshal)
			}

			//玩家角色
			if item.Roles != "" {
				err = json.Unmarshal([]byte(item.Roles), &roleArr)
				if err != nil {
					logrus.WithField("method", "getUserExtraData").Errorf("玩家角色数据json umarshal错误: %v", err)
					return
				}
				playerRole := make([]gmdata.PlayerRole, 0)
				for _, role := range roleArr {
					playerRole = append(playerRole, gmdata.PlayerRoles[role.Id])
				}
				marshal, err := json.Marshal(playerRole)
				if err != nil {
					return
				}
				playerInfo.PlayerRole = string(marshal)
			}

			//玩家关卡
			if item.PassRoom != "" {
				err = json.Unmarshal([]byte(item.PassRoom), &passRoom)
				if err != nil {
					logrus.WithField("method", "getUserExtraData").Errorf("玩家关卡数据json umarshal错误: %v", err)
					return

				}
				playerChapter := make([]gmdata.PlayerChapter, 0)
				for _, chapter := range passRoom {
					if chapter.Pass == true {
						playerChapter = append(playerChapter, gmdata.PlayerChapters[chapter.ID])
					}
				}
				marshal, err := json.Marshal(playerChapter)
				if err != nil {
					return
				}
				playerInfo.PlayerChapter = string(marshal)
			}

			//玩家部位
			if item.Body != "" {
				err = json.Unmarshal([]byte(item.Body), &bodyArr)
				if err != nil {
					logrus.WithField("method", "getUserExtraData").Errorf("玩家部位数据json umarshal错误: %v", err)
					return

				}
				playerBody := make([]gmdata.PlayerBody, 0)
				for _, body := range bodyArr {
					playerBody = append(playerBody, gmdata.PlayerBody{
						Part:  body.Part,
						Name:  gmdata.BodyMap[body.Part],
						Level: body.Level,
						Step:  body.Step,
					})
				}
				marshal, err := json.Marshal(playerBody)
				if err != nil {
					return
				}
				playerInfo.PlayerBody = string(marshal)
			}

			//玩家天赋
			if item.Talents != "" {
				err = json.Unmarshal([]byte(item.Talents), &talentMap)
				if err != nil {
					logrus.WithField("method", "getUserExtraData").Errorf("玩家天赋数据json umarshal错误: %v", err)
					return
				}
				playerTalent := make([]gmdata.PlayerTalent, 0)
				for tId, num := range talentMap {
					atoi, _ := strconv.Atoi(tId)
					talent := gmdata.PlayerTalents[atoi]
					talent.Number = num
					playerTalent = append(playerTalent, talent)
				}
				marshal, err := json.Marshal(playerTalent)
				if err != nil {
					return
				}
				playerInfo.PlayerTalent = string(marshal)
			}

			//玩家宝物
			if item.Treasures != "" {
				err = json.Unmarshal([]byte(item.Treasures), &treasuresArr)
				if err != nil {
					logrus.WithField("method", "getUserExtraData").Errorf("玩家宝物数据json umarshal错误: %v", err)
					return
				}
				playerTreasure := make([]gmdata.PlayerTreasure, 0)
				for _, treasure := range treasuresArr {
					playerTreasure = append(playerTreasure, gmdata.PlayerTreasures[treasure.TId])
				}
				marshal, err := json.Marshal(playerTreasure)
				if err != nil {
					return
				}
				playerInfo.PlayerTreasure = string(marshal)

			}
			if m, ok := playerRechargeMap[playerInfo.PlayerId]; ok {
				playerInfo.LastChargeAt = m.LastChargeAt
				playerInfo.FirstChargeAt = m.FirstChargeAt
				playerInfo.RechargeAmount = m.RechargeAmount
				playerInfo.RechargeCount = m.RechargeCount
				playerInfo.Total = m.Total
				playerInfo.Count = m.Count
			}
			//add
			playerInfoList[n] = playerInfo
		}

		p.exportToExcel(playerInfoList, searchDay, i)
	}
}

func (p *PlayerService) getUserInfoByMonth(searchDay time.Time) ([]forms.PlayerAttr, error) {
	searchTime := p.getSearchTime(searchDay)
	userInfos := make([]forms.PlayerAttr, 0)
	userInfoArr := make([]forms.PlayerAttr, 0)
	sql := "SELECT `player_attr`.`nickName` as nick_name,`player_attr`.`playerid` as player_id ,`player_attr`.`accId` as acc_id,`player_attr`.`serverId` as server_id,`player_attr`.`level`,`player_attr`.`exp`,`player_attr`.`stamina`,`player_attr`.`lastlogintick`, `player_attr`.`lastlogouttick`, `player_attr`.`create_time`, `player_material`.`coin`, `player_material`.`diamond`, `player_material`.`payCount` as pay_count, `player_material`.`fastEquips` as fast_equips, `player_material`.`roles`, `player_material`.`talents`, `player_material`.`treasures`, `player_material`.`body`, `player_material`.`passRoom` as pass_room FROM `player_attr` LEFT JOIN `player_material` ON `player_material`.`playerid` = `player_attr`.`playerid` WHERE `player_attr`.`create_time` >= ? AND `player_attr`.`create_time` <= ?"
	//sql2 := "SELECT `player_attr`.`playerid`,`player_attr`.`accId`,`player_attr`.`nickName`,`player_attr`.`serverId` FROM `player_attr` WHERE `player_attr`.`create_time` >= ?"
	var userInfo forms.PlayerAttr
	for _, db := range config.GDBGroup {
		rows, err := db.Raw(sql, searchTime.FirstDayTimeString, searchTime.LastDayTimesString).Rows()
		for rows.Next() {
			err = db.ScanRows(rows, &userInfo)
			if err != nil {
				return nil, err
			}
			userInfos = append(userInfos, userInfo)
		}
		if err != nil {
			return nil, err
		}
		userInfoArr = append(userInfoArr, userInfos...)
	}
	return userInfoArr, nil
}

func (p *PlayerService) getUserRechargeData(searchDay time.Time) (map[int64]*forms.PlayerRecharge, error) {
	searchTime := p.getSearchTime(searchDay)
	//充值数据 logDB.order 、 消费数据logDB.changedLog
	//1. 充值金额与次数
	playerRecharge := make([]*forms.PlayerRecharge, 0)
	orderQuery := query.Use(config.DB).Order
	orderQueryDo := orderQuery.WithContext(p.ctx)
	err := orderQueryDo.
		Select(orderQuery.Money.Sum().As("total"), orderQuery.ID.Count().As("count"), orderQuery.PlayerID).
		Where(orderQuery.CreatedAt.Gte(searchDay)).
		Where(orderQuery.Status.Eq(1)).
		Where(orderQuery.CreatedAt.Between(time.Unix(searchTime.FirstDayTimestamp, 0), time.Unix(searchTime.LastDayTimestamp, 0))).
		Group(orderQuery.PlayerID).
		Scan(&playerRecharge)
	if err != nil {
		logrus.WithField("method", "getUserRechargeData").Errorf("查询玩家充值金额与次数失败: %v", err)
		return nil, err
	}
	//playerRecharge转为map
	playerRechargeMap := make(map[int64]*forms.PlayerRecharge)
	for _, item := range playerRecharge {
		playerRechargeMap[item.PlayerId] = item
	}

	// 第一次和最后一次充值
	var RechargeSql = "SELECT\n    first_order.id AS first_id,\n    first_order.player_id AS player_id,\n    first_order.created_at AS first_created_at,\n    last_order.id AS last_id,\n    last_order.created_at AS last_created_at\nFROM\n    (SELECT\n        id,\n        player_id,\n        created_at\n    FROM\n        orders\n    WHERE\n        created_at between @start and @end\n    ORDER BY\n        created_at\n    LIMIT 1) AS first_order\nJOIN\n    (SELECT\n        id,\n        player_id,\n        created_at\n    FROM\n        orders\n    WHERE\n  created_at between @start and @end\n    ORDER BY\n        created_at DESC\n    LIMIT 1) AS last_order\nON\n    first_order.player_id = last_order.player_id"
	err = config.DB.Raw(RechargeSql, sql.Named("start", searchTime.FirstDayTimeString), sql.Named("end", searchTime.LastDayTimesString)).Scan(&playerRecharge).Error
	if err != nil {
		logrus.WithField("method", "getUserRechargeData").Errorf("查询玩家充值时间失败: %v", err)
		return nil, err
	}
	for _, item := range playerRecharge {
		if recharge, ok := playerRechargeMap[item.PlayerId]; ok {
			recharge.FirstChargeAt = item.FirstChargeAt
			recharge.LastChargeAt = item.LastChargeAt
			recharge.RechargeCount = item.RechargeCount
			recharge.RechargeAmount = item.RechargeAmount
		}
	}
	return playerRechargeMap, nil
}

func (p *PlayerService) getSearchTime(searchDay time.Time) forms.SearchTime {
	// 获取月份第一天 00:00:00 时间戳
	firstDayOfMonth := time.Date(searchDay.Year(), searchDay.Month(), 1, 0, 0, 0, 0, time.Local)
	firstDayTimestamp := firstDayOfMonth.Unix()
	firstDayTimeString := firstDayOfMonth.Format("2006-01-02 15:04:05")
	fmt.Println("月份第一天 00:00:00 时间戳:", firstDayTimestamp)

	// 获取月份最后一天 23:59:59 时间戳
	lastDayOfMonth := firstDayOfMonth.AddDate(0, 1, -1)
	lastDayTimestamp := lastDayOfMonth.Add(time.Duration(23*time.Hour + 59*time.Minute + 59*time.Second)).Unix()
	lastDayTimeString := lastDayOfMonth.Add(time.Duration(23*time.Hour + 59*time.Minute + 59*time.Second)).Format("2006-01-02 15:04:05")
	fmt.Println("月份最后一天 23:59:59 时间戳:", lastDayTimestamp)
	return forms.SearchTime{
		FirstDayTimestamp:  firstDayTimestamp,
		FirstDayTimeString: firstDayTimeString,
		LastDayTimestamp:   lastDayTimestamp,
		LastDayTimesString: lastDayTimeString,
	}
}

func (p *PlayerService) exportToExcel(dataList []*forms.PlayerInfo, searchDay time.Time, n int) {
	searchName := searchDay.Format("200601")
	f := excelize.NewFile()
	sheetName := fmt.Sprintf("sheet_%s_%d", searchName, n)
	f.NewSheet(sheetName)

	streamWriter, err := f.NewStreamWriter(sheetName)
	if err != nil {
		logrus.WithField("method", "writeToFile").Errorf("创建流式写入器失败: %v", err)
		return
	}
	// 写入表头
	row := []interface{}{"玩家ID", "账号ID", "服务器ID", "昵称", "等级", "经验", "体力", "金币", "钻石", "最后登录", "最后登出", "注册时间", "首充时间", "最后充值时间", "充值总额", "充值次数", "武器", "角色", "部位", "天赋", "宝物", "关卡"}
	if err := streamWriter.SetRow("A1", row); err != nil {
		logrus.WithField("method", "writeToFile").Errorf("写入表头失败: %v", err)
		return
	}
	// 写入数据
	for i, playerInfo := range dataList {
		row := []interface{}{
			playerInfo.PlayerId, playerInfo.AccID, playerInfo.ServerID, playerInfo.NickName,
			playerInfo.Level, playerInfo.Exp, playerInfo.Stamina, playerInfo.Coin, playerInfo.Diamond,
			playerInfo.Lastlogintick, playerInfo.Lastlogouttick, playerInfo.CreateTime,
			playerInfo.FirstChargeAt, playerInfo.LastChargeAt, playerInfo.RechargeAmount, playerInfo.RechargeCount,
			playerInfo.PlayerWear, playerInfo.PlayerRole, playerInfo.PlayerBody, playerInfo.PlayerTalent,
			playerInfo.PlayerTreasure, playerInfo.PlayerChapter,
		}
		if err := streamWriter.SetRow(fmt.Sprintf("A%d", i+2), row); err != nil {
			logrus.WithField("method", "writeToFile").Errorf("写入数据失败: %v", err)
			return
		}
	}

	if err := streamWriter.Flush(); err != nil {
		logrus.WithField("method", "writeToFile").Errorf("刷新流式写入器失败: %v", err)
		return
	}

	if err := f.SaveAs("./player_statistics.xlsx"); err != nil {
		logrus.WithField("method", "writeToFile").Errorf("保存文件失败: %v", err)
		return
	}
}