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

package service

import (
	"context"
	"fmt"
	"gadmin/config"
	"gadmin/internal/admin/consts"
	"gadmin/internal/admin/forms"
	"gadmin/internal/gorm/model"
	"gadmin/internal/gorm/query"
	"gadmin/utility/serializer"
	"github.com/gin-gonic/gin"
	"github.com/sirupsen/logrus"
	"github.com/xuri/excelize/v2"
	"io/ioutil"
	"os"
	"strings"
	"sync"
	"time"
)

var Fighting = new(sFighting)

type sFighting struct{}

func (s *sFighting) unionModel(ctx *gin.Context, where string) (unionALL string, err error) {
	type Table struct {
		Value string
	}

	dbName := config.GetDBName(os.Getenv("MYSQL_DSN"))
	var sql = "SELECT TABLE_NAME as `value` FROM information_schema.`TABLES` WHERE TABLE_SCHEMA ='" + dbName + "' AND TABLE_NAME LIKE 'chapter_logs_user_details_%'"
	tx := config.DB.Raw(sql)
	var tables []string
	if err = tx.Scan(&tables).Error; err != nil {
		return
	}

	logrus.Warnf("tables:%+v", tables)
	field := "id,server_id,user_id,event_id,chapter_id,room_id,user_created_at,event_at,extra,token,difficulty,event_at_ns"
	for k, table := range tables {
		if k == 0 {
			unionALL = fmt.Sprintf("SELECT %s FROM %s %s", field, table, where)
			continue
		}
		unionALL += fmt.Sprintf(" UNION ALL SELECT %s FROM %s %s", field, table, where)
	}

	return unionALL, nil
}

func (s *sFighting) List(ctx *gin.Context, req forms.FightingListReq) serializer.Response {
	var (
		models      forms.ListRes
		lists       []model.ChapterLogsUserDetail
		offset      int64 = 0
		hasWhere    bool
		where       string
		count       int64
		masterWhere string
	)
	if req.UserId > 0 {
		if hasWhere {
			where += fmt.Sprintf(" and user_id = %v", req.UserId)
		} else {
			hasWhere = true
			where += fmt.Sprintf(" where user_id = %v", req.UserId)
		}
	}

	if req.Id > 0 {
		if hasWhere {
			where += fmt.Sprintf(" and id = %v", req.Id)
		} else {
			hasWhere = true
			where += fmt.Sprintf(" where id = %v", req.Id)
		}
	}

	if len(req.EventAt) == 2 {
		if hasWhere {
			where += fmt.Sprintf(" and event_at BETWEEN %v AND %v", int32(req.EventAt[0]), int32(req.EventAt[1]))
		} else {
			hasWhere = true
			where += fmt.Sprintf(" where event_at BETWEEN %v AND %v", int32(req.EventAt[0]), int32(req.EventAt[1]))
		}
	}

	if req.ChapterId > 0 {
		if hasWhere {
			where += fmt.Sprintf(" and chapter_id = %v", req.ChapterId)
		} else {
			hasWhere = true
			where += fmt.Sprintf(" where chapter_id = %v", req.ChapterId)
		}
	}

	if req.Difficulty > -1 {
		if hasWhere {
			where += fmt.Sprintf(" and difficulty = %v", req.Difficulty)
		} else {
			hasWhere = true
			where += fmt.Sprintf(" where difficulty = %v", req.Difficulty)
		}
	}

	unionALL, err := s.unionModel(ctx, where)
	if err != nil {
		return serializer.Err(consts.CodeParamErr, "查询出错 unionModel", err)
	}

	countSql := fmt.Sprintf("SELECT count(1) FROM(%s) as a", unionALL)
	getSql := fmt.Sprintf("SELECT * FROM(%s) as a", unionALL)

	// 排序
	masterWhere += " ORDER BY event_at DESC"

	// 分页
	req.Page, req.PerPage, offset = forms.CalculatePage(req.Page, req.PerPage)

	config.DB.Raw(countSql + masterWhere).Scan(&count)
	if count > 0 {
		masterWhere += fmt.Sprintf(" LIMIT %v", req.PerPage)
		if offset > 0 {
			masterWhere += fmt.Sprintf(" OFFSET %v", offset)
		}
		config.DB.Raw(getSql + masterWhere).Scan(&lists)
	}

	models.List = lists
	models.Page = req.Page
	models.PerPage = req.PerPage
	models.PageCount = (count + req.PerPage - 1) / req.PerPage
	return serializer.Suc(models)
}

func (s *sFighting) GetExtra(extraPath string) serializer.Response {
	// 处理从ocs获取extra逻辑
	if strings.Contains(extraPath, "{") {
		return serializer.Suc(extraPath)
	}
	if response, err := config.GetCOSClient().Object.Get(context.Background(), extraPath, nil); err != nil {
		return serializer.Err(consts.CodeParamErr, "从cos中获取extra发生错误", err)
	} else {
		extra, err := ioutil.ReadAll(response.Body)
		response.Body.Close()
		if err != nil {
			return serializer.Err(consts.CodeParamErr, "读取cos数据出错", err)
		} else {
			return serializer.Suc(string(extra))
		}
	}
}

func (s *sFighting) Export(ctx *gin.Context, req forms.FightingExportReq) {
	var (
		lists       []model.ChapterLogsUserDetail
		hasWhere    bool
		where       string
		masterWhere string
	)

	if req.UserId > 0 {
		if hasWhere {
			where += fmt.Sprintf(" and user_id = %v", req.UserId)
		} else {
			hasWhere = true
			where += fmt.Sprintf(" where user_id = %v", req.UserId)
		}
	}
	if req.Id > 0 {
		if hasWhere {
			where += fmt.Sprintf(" and id = %v", req.Id)
		} else {
			hasWhere = true
			where += fmt.Sprintf(" where id = %v", req.Id)
		}
	}
	// 导出必须选择一个时间段，且不能大于一天
	if hasWhere {
		where += fmt.Sprintf(" and event_at BETWEEN %v AND %v", int32(req.EventStamp[0]), int32(req.EventStamp[1]))
	} else {
		hasWhere = true
		where += fmt.Sprintf(" where event_at BETWEEN %v AND %v", int32(req.EventStamp[0]), int32(req.EventStamp[1]))
	}

	if req.ChapterId > 0 {
		if hasWhere {
			where += fmt.Sprintf(" and chapter_id = %v", req.ChapterId)
		} else {
			hasWhere = true
			where += fmt.Sprintf(" where chapter_id = %v", req.ChapterId)
		}
	}

	if req.Difficulty > -1 {
		if hasWhere {
			where += fmt.Sprintf(" and difficulty = %v", req.Difficulty)
		} else {
			hasWhere = true
			where += fmt.Sprintf(" where difficulty = %v", req.Difficulty)
		}
	}

	unionALL, err := s.unionModel(ctx, where)
	if err != nil {
		logrus.Warnf("Export 查询出错 unionModel: err:%+v", err)
		return
	}

	//countSql := fmt.Sprintf("SELECT count(1) FROM(%s) as a", unionALL)
	getSql := fmt.Sprintf("SELECT * FROM(%s) as a", unionALL)

	// 排序
	masterWhere += " ORDER BY event_at DESC"

	config.DB.Raw(getSql + masterWhere).Scan(&lists)
	// 多协程去cos请求数据
	numWorkers := 100
	var wg sync.WaitGroup
	if numWorkers > len(lists) {
		numWorkers = len(lists)
	}
	// 计算每个worker负责处理的元素范围
	itemsPerWorker := len(lists) / numWorkers
	extraItems := len(lists) % numWorkers
	// 分配任务给每个worker
	for i := 0; i < numWorkers; i++ {
		startIndex := i * itemsPerWorker
		endIndex := startIndex + itemsPerWorker
		// 处理最后一个worker的情况
		if i == numWorkers-1 {
			endIndex += extraItems
		}
		wg.Add(1)
		go func(start, end int) {
			defer wg.Done()
			for j := start; j < end; j++ {
				if strings.Contains(`lists[j].Extra`, "{") {
					continue
				}
				if response, err := config.GetCOSClient().Object.Get(context.Background(), lists[j].Extra, nil); err != nil {
					logrus.Warnf("从cos中获取extra发生错误 err:%+v", err)
					continue
				} else {
					extra, err := ioutil.ReadAll(response.Body)
					response.Body.Close()
					if err != nil {
						logrus.Warnf("读取cos数据出错 err:%+v", err)
						continue
					} else {
						lists[j].Extra = string(extra)
					}
				}
			}
		}(startIndex, endIndex)
	}
	// 等待所有goroutines完成
	wg.Wait()

	f := excelize.NewFile()
	f.SetColWidth("Sheet1", "G", "G", 150)
	f.SetColWidth("Sheet1", "H", "H", 25)
	// 创建一个工作表
	f.SetCellValue("Sheet1", "A1", "ID")
	f.SetCellValue("Sheet1", "B1", "玩家ID")
	f.SetCellValue("Sheet1", "C1", "埋点ID")
	f.SetCellValue("Sheet1", "D1", "关卡ID")
	f.SetCellValue("Sheet1", "E1", "房间ID")
	f.SetCellValue("Sheet1", "F1", "困难度")
	f.SetCellValue("Sheet1", "G1", "其他参数")
	f.SetCellValue("Sheet1", "H1", "埋点触发时间")

	for i, v := range lists {
		f.SetCellValue("Sheet1", fmt.Sprintf("A%d", i+2), v.ID)
		f.SetCellValue("Sheet1", fmt.Sprintf("B%d", i+2), v.UserID)
		f.SetCellValue("Sheet1", fmt.Sprintf("C%d", i+2), v.EventID)
		f.SetCellValue("Sheet1", fmt.Sprintf("D%d", i+2), v.ChapterID)
		f.SetCellValue("Sheet1", fmt.Sprintf("E%d", i+2), v.RoomID)
		if v.Difficulty == 0 {
			f.SetCellValue("Sheet1", fmt.Sprintf("F%d", i+2), "简单")
		} else {
			f.SetCellValue("Sheet1", fmt.Sprintf("F%d", i+2), "困难")
		}
		f.SetCellValue("Sheet1", fmt.Sprintf("G%d", i+2), v.Extra)
		f.SetCellValue("Sheet1", fmt.Sprintf("H%d", i+2), time.Unix(int64(v.EventAt), 0).Format("2006-01-02 15:04:05"))
	}

	// 设置工作簿的默认工作表
	f.SetActiveSheet(1)
	ctx.Header("Content-Type", "application/vnd.ms-excel")
	ctx.Header("Content-Disposition", fmt.Sprintf("attachment;filename=玩家(%v)战斗数据导出_%s.xlsx", req.UserId, time.Now().Format("20060102150405")))
	f.WriteTo(ctx.Writer)
}

func (s *sFighting) Export2(ctx *gin.Context, req forms.FightingExportReq) {

	var (
		q      = query.Use(config.DB).ChapterLogsUserDetail
		m      = q.WithContext(ctx)
		models forms.ListRes
		lists  []model.ChapterLogsUserDetail
	)
	if req.UserId > 0 {
		m = m.Where(q.UserID.Eq(req.UserId))
	}

	if req.Id > 0 {
		m = m.Where(q.ID.Eq(req.Id))
	}

	if len(req.EventStamp) == 2 {
		m = m.Where(q.EventAt.Between(int32(req.EventStamp[0]), int32(req.EventStamp[1])))
	}

	if req.ChapterId > 0 {
		m = m.Where(q.ChapterID.Eq(req.ChapterId))
	}

	if req.Difficulty > -1 {
		m = m.Where(q.Difficulty.Eq(req.Difficulty))
	}

	//m = m.Order(q.ID.Desc())
	if err := m.Scan(&lists); err != nil {
		return
	}

	models.List = lists

	f := excelize.NewFile()
	//f.SetColWidth("Sheet1", "C", "C", 12)
	//f.SetColWidth("Sheet1", "D", "D", 25)
	f.SetColWidth("Sheet1", "G", "G", 150)
	f.SetColWidth("Sheet1", "H", "H", 25)
	// 创建一个工作表
	f.SetCellValue("Sheet1", "A1", "ID")
	f.SetCellValue("Sheet1", "B1", "玩家ID")
	f.SetCellValue("Sheet1", "C1", "埋点ID")
	f.SetCellValue("Sheet1", "D1", "关卡ID")
	f.SetCellValue("Sheet1", "E1", "房间ID")
	f.SetCellValue("Sheet1", "F1", "困难度")
	f.SetCellValue("Sheet1", "G1", "其他参数")
	f.SetCellValue("Sheet1", "H1", "埋点触发时间")

	for i, v := range lists {
		f.SetCellValue("Sheet1", fmt.Sprintf("A%d", i+2), v.ID)
		f.SetCellValue("Sheet1", fmt.Sprintf("B%d", i+2), v.UserID)
		f.SetCellValue("Sheet1", fmt.Sprintf("C%d", i+2), v.EventID)
		f.SetCellValue("Sheet1", fmt.Sprintf("D%d", i+2), v.ChapterID)
		f.SetCellValue("Sheet1", fmt.Sprintf("E%d", i+2), v.RoomID)
		if v.Difficulty == 0 {
			f.SetCellValue("Sheet1", fmt.Sprintf("F%d", i+2), "简单")
		} else {
			f.SetCellValue("Sheet1", fmt.Sprintf("F%d", i+2), "困难")
		}
		f.SetCellValue("Sheet1", fmt.Sprintf("G%d", i+2), v.Extra)
		f.SetCellValue("Sheet1", fmt.Sprintf("H%d", i+2), time.Unix(int64(v.EventAt), 0).Format("2006-01-02 15:04:05"))
	}

	// 设置工作簿的默认工作表
	f.SetActiveSheet(1)
	// content, _ := gocsv.MarshalString(&data)
	ctx.Header("Content-Type", "application/vnd.ms-excel")
	ctx.Header("Content-Disposition", fmt.Sprintf("attachment;filename=玩家(%v)战斗数据导出_%s.xlsx", req.UserId, time.Now().Format("20060102150405")))
	f.WriteTo(ctx.Writer)

}