123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368 |
- 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)
- }
|