package service import ( "context" "encoding/json" "fmt" "gadmin/config" "gadmin/internal/admin/consts" "gadmin/internal/admin/forms" "gadmin/internal/gorm/model" "gadmin/internal/gorm/query" "gadmin/package/gmdata" "gadmin/utility" "gadmin/utility/player" "gadmin/utility/serializer" "github.com/gin-gonic/gin" "github.com/jinzhu/now" "github.com/sirupsen/logrus" "github.com/spf13/cast" "github.com/xuri/excelize/v2" "gorm.io/gorm" "math" "sort" "strconv" "strings" "time" ) // Dash Dash服务 var Dash = new(sDash) type sDash struct{} // QueryDisconnectList 断线重连统计列表 func (s *sDash) QueryDisconnectList(params forms.DisconnectListReq) (resp forms.DisconnectListRes, err error) { var ( rdb = query.Use(config.DB).ReportDayDisconnect m = rdb.Select(rdb.ALL, rdb.ID, rdb.Date, rdb.DisCount.Sum().As("dis_count"), rdb.DisUsers.Sum().As("dis_users"), rdb.ReCount.Sum().As("re_count"), rdb.ReUsers.Sum().As("re_users"), rdb.NewDisCount.Sum().As("new_dis_count"), rdb.NewDisUsers.Sum().As("new_dis_users"), rdb.NewReCount.Sum().As("new_re_count"), rdb.NewReUsers.Sum().As("new_re_users"), rdb.MobileDisCount.Sum().As("mobile_dis_count"), rdb.MobileDisUsers.Sum().As("mobile_dis_users"), rdb.MobileReCount.Sum().As("mobile_re_count"), rdb.MobileReUsers.Sum().As("mobile_re_users"), rdb.WifiDisCount.Sum().As("wifi_dis_count"), rdb.WifiDisUsers.Sum().As("wifi_dis_users"), rdb.WifiReCount.Sum().As("wifi_re_count"), rdb.WifiReUsers.Sum().As("wifi_re_users"), ) ) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } resp.Data, err = m. Where(rdb.Date.Between(params.Day, params.EndDay)). Group(rdb.Date). Order(rdb.Date.Desc()). Find() if err != nil { return resp, err } for _, v := range resp.Data { v.Date = utility.ParseDate(v.Date) } resp.Total = int64(len(resp.Data)) return } // 统计分组 const ( SevenLogGroupBasic = 1 // 七日登陆基本信息统计 SevenLogGroupTask = 2 // 任务完成度统计 SevenLogGroupAward = 3 // 活跃度奖励完成度统计 ) // QuerySevenAward 七日任务奖励统计 func (s *sDash) QuerySevenAward(params forms.SevenAwardReq) (resp forms.SevenAwardRespData, err error) { var ( rdb = query.Use(config.DB).ReportDaySeven days = 0 ) days, err = utility.GetDaysBetween2Date("2006-01-02", params.Day, params.EndDay) if err != nil { return resp, err } days += 1 rm := rdb. Where(rdb.Org.Eq(SevenLogGroupAward)). Where(rdb.Date.Between(params.Day, params.EndDay)) if params.ServerId > 0 { rm = rm.Where(rdb.ServerID.Eq(int32(params.ServerId))) } results, err := rm.Order(rdb.Date, rdb.TaskID).Find() if err != nil { return resp, err } findResult := func(taskID int64) *forms.SevenAwardItem { for _, v := range resp.Data { if v.TaskID == taskID { return v } } return nil } for _, result := range results { task := gmdata.GetNoviceAward(result.TaskID) if task == nil { continue } // 查询单天 if days == 1 { item := &forms.SevenAwardItem{ ID: result.ID, TaskID: task.ID, SucCount: result.TaskSucCount, FirstSucCount: result.TaskSucCount, AdvSucDays: result.AdvSucDay, } resp.Data = append(resp.Data, item) continue } vDate, _ := now.Parse(result.Date) resultDate := vDate.Format("2006-01-02") // 查询范围天 // 首天 if params.Day == resultDate { item := &forms.SevenAwardItem{ ID: result.ID, TaskID: task.ID, SucCount: result.TaskSucCount, FirstSucCount: result.TaskSucCount, AdvSucDays: result.AdvSucDay, } resp.Data = append(resp.Data, item) } else { item := findResult(task.ID) item.SucCount += result.TaskSucCount item.AdvSucDays += result.AdvSucDay // 末尾天 if params.EndDay == resultDate { item.AdvSucDays = item.AdvSucDays / float64(days) } } } var ( lists = make(map[int64]*forms.SevenAwardItem) baseMap = make(map[int64]int) ) for _, datum := range resp.Data { if _, ok := baseMap[datum.TaskID]; ok { baseMap[datum.TaskID] += 1 lists[datum.TaskID].AdvSucDays += datum.AdvSucDays lists[datum.TaskID].FirstSucCount += datum.FirstSucCount lists[datum.TaskID].SucCount += datum.SucCount } else { baseMap[datum.TaskID] = 1 lists[datum.TaskID] = datum } } resp.Data = nil for _, item := range lists { item.AdvSucDays /= float64(baseMap[item.TaskID]) resp.Data = append(resp.Data, item) } sort.Slice(resp.Data, func(i, j int) bool { return resp.Data[i].TaskID < resp.Data[j].TaskID }) resp.Total = int64(len(gmdata.NoviceActivityAward)) return } // QuerySevenTask 七日任务基础信息 func (s *sDash) QuerySevenTask(params forms.SevenTaskReq) (resp forms.SevenTaskRespData, err error) { var ( rdb = query.Use(config.DB).ReportDaySeven days = 0 offset = (params.Page - 1) * params.PerPage startTID = offset + 1 endTID = params.PerPage + offset ) days, err = utility.GetDaysBetween2Date("2006-01-02", params.Day, params.EndDay) if err != nil { return resp, err } days += 1 rm := rdb. Where(rdb.Org.Eq(SevenLogGroupTask)). Where(rdb.Date.Between(params.Day, params.EndDay)). Where(rdb.TaskID.Between(startTID, endTID)) if params.ServerId > 0 { rm = rm.Where(rdb.ServerID.Eq(int32(params.ServerId))) } results, err := rm.Order(rdb.Date, rdb.TaskID).Find() if err != nil { return resp, err } findResult := func(taskID int64) *forms.SevenTaskItem { for _, v := range resp.Data { if v.TaskID == taskID { return v } } return nil } for _, result := range results { task := gmdata.GetNoviceTask(result.TaskID) if task == nil { continue } // 查询单天 if days == 1 { item := &forms.SevenTaskItem{ ID: result.ID, TaskID: task.ID, Describe: strings.ReplaceAll(task.Describe, "[0]", strconv.FormatInt(task.TaskNum, 10)), DayValue: task.DayValue, SucCount: result.TaskSucCount, FirstSucCount: result.TaskSucCount, AdvSucDays: result.AdvSucDay, } resp.Data = append(resp.Data, item) continue } vDate, _ := now.Parse(result.Date) resultDate := vDate.Format("2006-01-02") // 查询范围天 // 首天 if params.Day == resultDate { item := &forms.SevenTaskItem{ ID: result.ID, TaskID: task.ID, Describe: strings.ReplaceAll(task.Describe, "[0]", strconv.FormatInt(task.TaskNum, 10)), DayValue: task.DayValue, SucCount: result.TaskSucCount, FirstSucCount: result.TaskSucCount, AdvSucDays: result.AdvSucDay, } resp.Data = append(resp.Data, item) } else { item := findResult(task.ID) item.SucCount += result.TaskSucCount item.AdvSucDays += result.AdvSucDay // 末尾天 if params.EndDay == resultDate { item.AdvSucDays = item.AdvSucDays / float64(days) } } } var ( lists = make(map[int64]*forms.SevenTaskItem) baseMap = make(map[int64]int) ) for _, datum := range resp.Data { if _, ok := baseMap[datum.TaskID]; ok { baseMap[datum.TaskID] += 1 lists[datum.TaskID].AdvSucDays += datum.AdvSucDays lists[datum.TaskID].FirstSucCount += datum.FirstSucCount lists[datum.TaskID].SucCount += datum.SucCount } else { baseMap[datum.TaskID] = 1 lists[datum.TaskID] = datum } } resp.Data = nil for _, item := range lists { item.AdvSucDays /= float64(baseMap[item.TaskID]) resp.Data = append(resp.Data, item) } sort.Slice(resp.Data, func(i, j int) bool { return resp.Data[i].TaskID < resp.Data[j].TaskID }) resp.Total = int64(len(gmdata.NoviceTaskList)) return } // QuerySevenBasic 七日任务基础信息 func (s *sDash) QuerySevenBasic(params forms.SevenBasicReq) (resp forms.SevenBasicRespData, err error) { var ( rdb = query.Use(config.DB).ReportDaySeven b = query.Use(config.DB).ReportDayBasic m = rdb. Select(rdb.ID, rdb.Date, rdb.PlayerCount.Sum().As("player_count"), rdb.NewPlayerCount.Sum().As("new_player_count"), rdb.OpenCount.Sum().As("open_count"), rdb.AllTaskSucCount.Sum().As("all_task_suc_count"), rdb.Unlock1.Sum().As("unlock1"), rdb.Unlock2.Sum().As("unlock2"), rdb.Unlock3.Sum().As("unlock3"), rdb.Unlock4.Sum().As("unlock4"), rdb.Unlock5.Sum().As("unlock5"), rdb.Unlock6.Sum().As("unlock6"), rdb.Unlock7.Sum().As("unlock7"), rdb.TaskID.Sum().As("task_id"), ). Where(rdb.Org.Eq(SevenLogGroupBasic)). Group(rdb.Date). Order(rdb.Date.Desc()). Limit(int(params.PerPage)). Offset(int((params.Page - 1) * params.PerPage)) ) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } results, err := m.Find() if err != nil { return resp, err } for _, result := range results { var validCount int64 mb := b. Select(b.ValidCount.Sum().As("valid_count")). Where(b.Date.Eq(utility.ParseDate(result.Date))) if params.ChannelId != "" { mb = mb.Where(b.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { mb = mb.Where(b.ServerID.Eq(int32(params.ServerId))) } basic, err := mb.Group(b.Date).Order(b.Date.Desc()).First() if err == nil && basic != nil { validCount = basic.ValidCount } resp.Data = append(resp.Data, forms.SevenBasicItem{ ID: result.ID, Date: utility.ParseDate(result.Date), PlayerCount: result.PlayerCount, ValidCount: validCount, NewPlayerCount: result.NewPlayerCount, OpenCount: result.OpenCount, AllTaskSucCount: result.AllTaskSucCount, Unlock1: result.Unlock1, Unlock2: result.Unlock2, Unlock3: result.Unlock3, Unlock4: result.Unlock4, Unlock5: result.Unlock5, Unlock6: result.Unlock6, Unlock7: result.Unlock7, }) } resp.Total, err = m.Count() return } type AdvEchartsInfo struct { Days []string `json:"days"` PositionID []int64 `json:"PositionID"` ShowTimes []int64 `json:"ShowTimes"` RewardTimes []int64 `json:"RewardTimes"` ShowUsers []int64 `json:"ShowUsers"` RewardUsers []int64 `json:"RewardUsers"` ClickTimes []int64 `json:"ClickTimes"` ClickUsers []int64 `json:"ClickUsers"` } type AdvEchartsData struct { Info AdvEchartsInfo `json:"info"` } // QueryAdvEcharts 广告点位统计折线 func (s *sDash) QueryAdvEcharts(params forms.QueryAdvEchartsReq) (respData AdvEchartsData, err error) { resp := AdvEchartsInfo{} rdb := query.Use(config.DB).ReportDayAdvBasic m := rdb. Select(rdb.ID, rdb.Date, rdb.PositionID, rdb.ShowTimes.Sum().As("show_times"), rdb.ClickTimes.Sum().As("click_times"), rdb.RewardTimes.Sum().As("reward_times"), rdb.ShowUsers.Sum().As("show_users"), rdb.ClickUsers.Sum().As("click_users"), rdb.RewardUsers.Sum().As("reward_users"), ). Where(rdb.PositionID.Eq(params.PositionId), rdb.Date.Gte(params.Day), rdb.Date.Lt(params.EndDay)) switch params.ChannelId { case "": // 不选择渠道 case "1": // 所有的广告渠道 m = m.Where(rdb.ChannelID.Neq("0")) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } result, err := m.Order(rdb.Date).Group(rdb.Date).Find() // .Order(rdb.Date.Desc()) if err != nil { return } for _, v := range result { if v != nil { resp.Days = append(resp.Days, strings.ReplaceAll(v.Date, "T00:00:00+08:00", "")) resp.PositionID = append(resp.PositionID, v.PositionID) resp.ShowTimes = append(resp.ShowTimes, v.ShowTimes) resp.RewardTimes = append(resp.RewardTimes, v.RewardTimes) resp.ShowUsers = append(resp.ShowUsers, v.ShowUsers) resp.RewardUsers = append(resp.RewardUsers, v.RewardUsers) resp.ClickTimes = append(resp.ClickTimes, v.ClickTimes) resp.ClickUsers = append(resp.ClickUsers, v.ClickUsers) } } respData.Info = resp return } type R struct { EventId int64 Count int64 } func (s *sDash) QueryAdvReport(params forms.AdvReportReq) (resp forms.AdvReportRespData, err error) { var ( positionIds []int64 p = query.Use(config.DB).ReportDayAdvBasic mp = p.Select(p.PositionID.Distinct()) ) if params.ServerId > 0 { mp = mp.Where(p.ServerID.Eq(int32(params.ServerId))) } if params.Flag > -1 { mp = mp.Where(p.Flag.Eq(params.Flag)) } err = mp.Where(p.Date.Eq(params.Day)).Pluck(p.PositionID, &positionIds) if err != nil { logrus.WithField("from", "QueryAdvReport Pluck").Error(err) return } for _, positionId := range positionIds { rdb := query.Use(config.DB).ReportDayAdvBasic m := rdb. Select(rdb.ID, rdb.Date, rdb.PositionID, rdb.ShowTimes.Sum().As("show_times"), rdb.ClickTimes.Sum().As("click_times"), rdb.RewardTimes.Sum().As("reward_times"), rdb.ShowUsers.Sum().As("show_users"), rdb.ClickUsers.Sum().As("click_users"), rdb.RewardUsers.Sum().As("reward_users"), ). Where(rdb.Date.Eq(params.Day), rdb.PositionID.Eq(positionId)) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } if params.Flag > -1 { m = m.Where(rdb.Flag.Eq(int32(params.Flag))) } results, err := m.Order(rdb.Date.Desc()). Group(rdb.Date). Limit(params.PerPage). Offset((params.Page - 1) * params.PerPage). Find() if err != nil { return resp, err } for _, result := range results { resp.Data = append(resp.Data, forms.AdvReportItem{ ID: result.ID, PositionId: result.PositionID, ShowTimes: result.ShowTimes, ClickTimes: result.ClickTimes, RewardTimes: result.RewardTimes, ShowUsers: result.ShowUsers, ClickUsers: result.ClickUsers, RewardUsers: result.RewardUsers, }) } } resp.Total = int64(len(positionIds)) if err != nil { return resp, err } return } func (s *sDash) QueryFirstAdvStat(ctx *gin.Context, params forms.FirstAdvReq) (resp *model.ReportDayFirstAdv, err error) { rdb := query.Use(config.DB).ReportDayFirstAdv m := rdb. Select(rdb.ALL, rdb.Goto.Sum().As("goto"), rdb.A.Sum().As("a"), rdb.B.Sum().As("b"), rdb.C.Sum().As("c"), rdb.Waive.Sum().As("waive"), rdb.EquipNow.Sum().As("equip_now"), ) switch params.ChannelId { case "": // 不选择渠道 case "1": // 所有的广告渠道 m = m.Where(rdb.ChannelID.Neq("0")) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } resp, err = m.First() if err != nil { return resp, err } return } func (s *sDash) QueryFirstAdv(ctx *gin.Context, params forms.FirstAdvReq) (resp forms.FirstAdvRespData, err error) { rdb := query.Use(config.DB).ReportDayFirstAdv m := rdb. Select(rdb.ALL, rdb.Goto.Sum().As("goto"), rdb.A.Sum().As("a"), rdb.B.Sum().As("b"), rdb.C.Sum().As("c"), rdb.Waive.Sum().As("waive"), rdb.EquipNow.Sum().As("equip_now"), ). WithContext(ctx) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } results, err := m.Order(rdb.Date.Desc()). Group(rdb.Date). Limit(int(params.PerPage)). Offset(int((params.Page - 1) * params.PerPage)). Find() if err != nil { return resp, err } for _, result := range results { date := utility.ParseDate(result.Date) resp.Data = append(resp.Data, forms.FirstAdvItem{ ID: result.ID, Date: date, Goto: result.Goto, A: result.A, B: result.B, C: result.C, Waive: result.Waive, EquipNow: result.EquipNow, }) } resp.Total, err = rdb.Order(rdb.Date.Desc()).Group(rdb.Date).Count() return } func (s *sDash) QueryAdvSumm(ctx *gin.Context, params forms.AdvSummReq) (resp forms.AdvSummRespData, err error) { rdb := query.Use(config.DB).ReportDayAdvSumm m := rdb. Select(rdb.ALL, rdb.RewardTimes.Sum().As("reward_times"), rdb.RewardUsers.Sum().As("reward_users"), rdb.NewTimes.Sum().As("new_times"), rdb.NewUsers.Sum().As("new_users"), rdb.OldTimes.Sum().As("old_times"), rdb.OldUsers.Sum().As("old_users"), ) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } if params.Flag > -1 { m = m.Where(rdb.Flag.Eq(params.Flag)) } // 导出 if params.IsExport == 1 { results, err := m.Order(rdb.Date.Desc()).Group(rdb.Date).Find() if err != nil { return resp, err } f := excelize.NewFile() f.SetColWidth("Sheet1", "A", "A", 15) f.SetColWidth("Sheet1", "B", "B", 12) f.SetColWidth("Sheet1", "C", "C", 12) f.SetColWidth("Sheet1", "D", "D", 12) f.SetColWidth("Sheet1", "E", "E", 12) f.SetColWidth("Sheet1", "F", "F", 12) f.SetColWidth("Sheet1", "G", "G", 12) f.SetColWidth("Sheet1", "H", "H", 12) f.SetColWidth("Sheet1", "I", "I", 12) f.SetColWidth("Sheet1", "J", "J", 12) f.SetColWidth("Sheet1", "K", "K", 12) f.SetColWidth("Sheet1", "L", "L", 12) f.SetColWidth("Sheet1", "M", "M", 12) f.SetColWidth("Sheet1", "N", "N", 12) f.SetColWidth("Sheet1", "O", "O", 12) f.SetColWidth("Sheet1", "P", "P", 12) // 创建一个工作表 f.SetCellValue("Sheet1", "A1", "日期") f.SetCellValue("Sheet1", "B1", "总曝光次数") f.SetCellValue("Sheet1", "C1", "总曝光人数") f.SetCellValue("Sheet1", "D1", "总平均频次") f.SetCellValue("Sheet1", "E1", "总活跃人数") f.SetCellValue("Sheet1", "F1", "总覆盖率") f.SetCellValue("Sheet1", "G1", "新注册广告次数") f.SetCellValue("Sheet1", "H1", "新注册广告人数") f.SetCellValue("Sheet1", "I1", "新注册平均频次") f.SetCellValue("Sheet1", "J1", "新注册人数") f.SetCellValue("Sheet1", "K1", "新注册覆盖率") f.SetCellValue("Sheet1", "L1", "老玩家广告次数") f.SetCellValue("Sheet1", "M1", "老玩家广告人数") f.SetCellValue("Sheet1", "N1", "老玩家平均频次") f.SetCellValue("Sheet1", "O1", "老玩家人数") f.SetCellValue("Sheet1", "P1", "老玩家覆盖率") for i, result := range results { newCount := DayBasic.GetPlayerCount(params.ServerId, result.Date, params.ChannelId, params.Flag, "new_count") //oldCount := DayBasic.GetPlayerCount(params.ServerId, result.Date, params.ChannelId, params.Flag, "old_count") activeCount := DayBasic.GetPlayerCount(params.ServerId, result.Date, params.ChannelId, params.Flag, "active_count") date, _ := now.ParseInLocation(time.Local, result.Date) // 日期 f.SetCellValue("Sheet1", fmt.Sprintf("A%d", i+2), date.Format("2006-01-02")) // 总曝光次数 f.SetCellValue("Sheet1", fmt.Sprintf("B%d", i+2), result.RewardTimes) // 总曝光人数 f.SetCellValue("Sheet1", fmt.Sprintf("C%d", i+2), result.RewardUsers) // 总平均频次 if result.RewardTimes == 0 || result.RewardUsers == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("D%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("D%d", i+2), utility.Round(float64(result.RewardTimes)/float64(result.RewardUsers))) } // 总活跃人数 f.SetCellValue("Sheet1", fmt.Sprintf("E%d", i+2), activeCount) // 总覆盖率 if result.RewardUsers == 0 || activeCount == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("F%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("F%d", i+2), fmt.Sprintf("%v%s", utility.Round((float64(result.RewardUsers)/float64(activeCount))*100), "%")) } // 新注册广告次数 f.SetCellValue("Sheet1", fmt.Sprintf("G%d", i+2), result.NewTimes) // 新注册广告人数 f.SetCellValue("Sheet1", fmt.Sprintf("H%d", i+2), result.NewUsers) // 新注册平均频次 if result.NewTimes == 0 || result.NewUsers == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("I%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("I%d", i+2), utility.Round(float64(result.NewTimes)/float64(result.NewUsers))) } // 新注册人数 f.SetCellValue("Sheet1", fmt.Sprintf("J%d", i+2), newCount) // 新注册覆盖率 if result.NewUsers == 0 || newCount == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("K%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("K%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.NewUsers)/float64(newCount)*100), "%")) } // 老玩家广告次数 f.SetCellValue("Sheet1", fmt.Sprintf("L%d", i+2), result.OldTimes) // 老玩家广告人数 if activeCount == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("M%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("M%d", i+2), activeCount-newCount) } // 老玩家平均频次 if result.OldTimes == 0 || result.OldUsers == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("N%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("N%d", i+2), utility.Round(float64(result.OldTimes)/float64(result.OldUsers))) } // 老玩家人数 if activeCount == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("O%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("O%d", i+2), activeCount-result.OldUsers) } // 老玩家覆盖率 if activeCount == 0 || result.OldUsers == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("P%d", i+2), "-") } else { f.SetCellValue("Sheet1", fmt.Sprintf("P%d", i+2), fmt.Sprintf("%v%s", utility.Round((float64(result.OldUsers)/float64(activeCount))*100), "%")) } } // 设置工作簿的默认工作表 f.SetActiveSheet(1) ctx.Header("Content-Type", "application/vnd.ms-excel") ctx.Header("Content-Disposition", fmt.Sprintf("attachment;filename=广告综合统计导出%s.xlsx", time.Now().Format("20060102150405"))) f.WriteTo(ctx.Writer) return resp, err } results, err := m.Order(rdb.Date.Desc()). Group(rdb.Date). Limit(int(params.PerPage)). Offset(int((params.Page - 1) * params.PerPage)). Find() if err != nil { return resp, err } for _, result := range results { date := utility.ParseDate(result.Date) resp.Data = append(resp.Data, forms.AdvSummItem{ ID: result.ID, Date: date, NewCount: DayBasic.GetPlayerCount(params.ServerId, date, params.ChannelId, params.Flag, "new_count"), OldCount: DayBasic.GetPlayerCount(params.ServerId, date, params.ChannelId, params.Flag, "old_count"), ActiveCount: DayBasic.GetPlayerCount(params.ServerId, date, params.ChannelId, params.Flag, "active_count"), RewardTimes: result.RewardTimes, RewardUsers: result.RewardUsers, NewTimes: result.NewTimes, NewUsers: result.NewUsers, OldTimes: result.OldTimes, OldUsers: result.OldUsers, }) } resp.Total, err = rdb.Order(rdb.Date.Desc()).Group(rdb.Date).Count() return } func (s *sDash) QueryAdvUserDetails(params forms.AdvUserDetailsReq) (resp forms.AdvUserReportRespData, err error) { type R2 struct { Count int64 PositionId int64 } var ( models []*forms.AdvUserReportItem eids = []int64{1, 2, 4} sql string ) push := func(eventId, positionId, count int64) { for _, v := range models { if v.PositionId == positionId { switch eventId { case 1: v.ShowTimes += count case 2: v.ClickTimes += count case 4: v.RewardTimes += count } return } } v2 := new(forms.AdvUserReportItem) v2.ID = int64(len(models)) + 1 v2.PositionId = positionId switch eventId { case 1: v2.ShowTimes += count case 2: v2.ClickTimes += count case 4: v2.RewardTimes += count } models = append(models, v2) } for _, eventId := range eids { var results []R2 sql = fmt.Sprintf("SELECT COUNT(*) AS `count`, `position_id` FROM `advertisement_logs` where user_id = %v and event_id = %v GROUP BY position_id ORDER BY `count` desc", params.UserId, eventId) if len(params.EventAt) == 2 { sql = fmt.Sprintf("SELECT COUNT(*) AS `count`, `position_id` FROM `advertisement_logs` where user_id = %v and event_id = %v and event_at >= %v and event_at <= %v GROUP BY position_id ORDER BY `count` desc", params.UserId, eventId, params.EventAt[0], params.EventAt[1]) } tx := config.DB.Model(&model.AdvertisementLog{}).Raw(sql).Scan(&results) if tx.Error != nil { logrus.Errorln(tx.Error) continue } for _, result := range results { push(eventId, result.PositionId, result.Count) } } resp.Data = models resp.Total = int64(len(models)) if err != nil { return resp, err } return } func (s *sDash) QueryAdvDetailsReq(params forms.AdvDetailsReq) (resp forms.AdvReportRespData, err error) { day, _ := now.Parse(params.Day) begin, end := now.With(day).BeginningOfDay().Unix(), now.With(day).EndOfDay().Unix() daysInfo := strings.Split(params.Days, "_") var ( uBegin int64 uEnd int64 ) if len(daysInfo) >= 2 { uBegin, uEnd = now.With(day).BeginningOfDay().AddDate(0, 0, 0-cast.ToInt(daysInfo[0])).Unix(), now.With(day).EndOfDay().AddDate(0, 0, 0-cast.ToInt(daysInfo[1])).Unix() } else { if strings.Contains(params.Days, "_") { // > 28 uEnd = now.With(day).EndOfDay().AddDate(0, 0, 0-cast.ToInt(daysInfo[0])).Unix() } else { uBegin, uEnd = now.With(day).BeginningOfDay().AddDate(0, 0, 0-cast.ToInt(daysInfo[0])).Unix(), now.With(day).EndOfDay().AddDate(0, 0, 0-cast.ToInt(daysInfo[0])).Unix() } } results := s.getDayAdvLogDetails(params.ServerId, uBegin, uEnd, begin, end, params.ChannelId) for i, result := range results { resp.Data = append(resp.Data, forms.AdvReportItem{ ID: int64(i), PositionId: result.PositionID, ShowTimes: result.ShowTimes, ClickTimes: result.ClickTimes, RewardTimes: result.RewardTimes, ShowUsers: result.ShowUsers, ClickUsers: result.ClickUsers, RewardUsers: result.RewardUsers, }) } resp.Total = int64(len(results)) if err != nil { return resp, err } return } func (s *sDash) QueryAllEventItems() (data interface{}, err error) { ec := query.Use(config.DB).ReportEvent data, err = ec.Find() return } func (s *sDash) getDayAdvLogDetails(serverId int, ubegin, uend, begin, end int64, channelId string) (data []*model.ReportDayAdvBasic) { advLog := query.Use(config.DB).AdvertisementLog advLogm := advLog.Select(advLog.PositionID.Distinct()) var positionIds []int64 if serverId > 0 { advLogm = advLogm.Where(advLog.ServerID.Eq(int32(serverId))) } err := advLogm.Where(advLog.EventAt.Between(int32(begin), int32(end))).Pluck(advLog.PositionID, &positionIds) if err != nil { logrus.Errorln(err) return } var cls = Channel.GetIdsByTypeToString(channelId) for _, pid := range positionIds { arl := &model.ReportDayAdvBasic{PositionID: pid} var results []R var sql string if ubegin > 0 { switch channelId { case consts.ChannelIdNone: // 不选择渠道 sql = fmt.Sprintf("select event_id, count(1) as count from advertisement_logs where event_at >= %d and event_at <= %d and position_id = %d and user_created_at>= %d and user_created_at <= %d ", begin, end, pid, ubegin, uend) case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 sql = fmt.Sprintf("select event_id, count(1) as count from advertisement_logs LEFT JOIN player_channel ON advertisement_logs.user_id = player_channel.playerid where event_at >= %d and event_at <= %d and position_id = %d and user_created_at>= %d and user_created_at <= %d and player_channel.channel_id in (%v)", begin, end, pid, ubegin, uend, cls) default: // 指定渠道 sql = fmt.Sprintf("select event_id, count(1) as count from advertisement_logs LEFT JOIN player_channel ON advertisement_logs.user_id = player_channel.playerid where event_at >= %d and event_at <= %d and position_id = %d and user_created_at>= %d and user_created_at <= %d and player_channel.channel_id = '%v'", begin, end, pid, ubegin, uend, channelId) } } else { switch channelId { case consts.ChannelIdNone: // 不选择渠道 sql = fmt.Sprintf("select event_id, count(1) as count from advertisement_logs where event_at >= %d and event_at <= %d and position_id = %d and user_created_at <= %d ", begin, end, pid, uend) case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 sql = fmt.Sprintf("select event_id, count(1) as count from advertisement_logs LEFT JOIN player_channel ON advertisement_logs.user_id = player_channel.playerid where event_at >= %d and event_at <= %d and position_id = %d and user_created_at <= %d and player_channel.channel_id in (%v)", begin, end, pid, uend, cls) default: // 指定渠道 sql = fmt.Sprintf("select event_id, count(1) as count from advertisement_logs LEFT JOIN player_channel ON advertisement_logs.user_id = player_channel.playerid where event_at >= %d and event_at <= %d and position_id = %d and user_created_at <= %d and player_channel.channel_id = '%v'", begin, end, pid, uend, channelId) } } if serverId > 0 { sql = fmt.Sprintf("%v and server_id = %v", sql, serverId) } sql = sql + " group by event_id" tx := config.DB.Model(&model.AdvertisementLog{}).Raw(sql).Scan(&results) if tx.Error != nil { logrus.Errorln(tx.Error) continue } hasVal := false for _, result := range results { switch result.EventId { case 1: arl.ShowTimes = result.Count hasVal = true case 2: arl.ClickTimes = result.Count hasVal = true case 4: arl.RewardTimes = result.Count hasVal = true } } var results2 []R if ubegin > 0 { switch channelId { case consts.ChannelIdNone: // 不选择渠道 sql = fmt.Sprintf("select event_id, count(distinct(user_id)) as count from advertisement_logs where event_at >= %d and event_at <=%d and position_id = %d and user_created_at>= %d and user_created_at <= %d ", begin, end, pid, ubegin, uend) case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 sql = fmt.Sprintf("select event_id, count(distinct(user_id)) as count from advertisement_logs LEFT JOIN player_channel ON advertisement_logs.user_id = player_channel.playerid where event_at >= %d and event_at <=%d and position_id = %d and user_created_at>= %d and user_created_at <= %d and player_channel.channel_id in (%v)", begin, end, pid, ubegin, uend, cls) default: // 指定渠道 sql = fmt.Sprintf("select event_id, count(distinct(user_id)) as count from advertisement_logs LEFT JOIN player_channel ON advertisement_logs.user_id = player_channel.playerid where event_at >= %d and event_at <=%d and position_id = %d and user_created_at>= %d and user_created_at <= %d and player_channel.channel_id = '%v' ", begin, end, pid, ubegin, uend, channelId) } } else { switch channelId { case consts.ChannelIdNone: // 不选择渠道 sql = fmt.Sprintf("select event_id, count(distinct(user_id)) as count from advertisement_logs where event_at >= %d and event_at <=%d and position_id = %d and user_created_at <= %d", begin, end, pid, uend) case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 sql = fmt.Sprintf("select event_id, count(distinct(user_id)) as count from advertisement_logs LEFT JOIN player_channel ON advertisement_logs.user_id = player_channel.playerid where event_at >= %d and event_at <=%d and position_id = %d and user_created_at <= %d and player_channel.channel_id in (%v)", begin, end, pid, uend, cls) default: // 指定渠道 sql = fmt.Sprintf("select event_id, count(distinct(user_id)) as count from advertisement_logs LEFT JOIN player_channel ON advertisement_logs.user_id = player_channel.playerid where event_at >= %d and event_at <=%d and position_id = %d and user_created_at <= %d and player_channel.channel_id = '%v'", begin, end, pid, uend, channelId) } } if serverId > 0 { sql = fmt.Sprintf("%v and server_id = %v", sql, serverId) } sql = sql + " group by event_id" tx2 := config.DB.Model(&model.AdvertisementLog{}).Raw(sql).Scan(&results2) if tx2.Error != nil { logrus.Errorln(tx.Error) continue } for _, result := range results2 { switch result.EventId { case 1: arl.ShowUsers = result.Count hasVal = true case 2: arl.ClickUsers = result.Count hasVal = true case 4: arl.RewardUsers = result.Count hasVal = true } } if hasVal { data = append(data, arl) } } return data } type BossReportInfo struct { Days []string `json:"days"` Players []int64 `json:"players"` MaxStage []int64 `json:"maxStage"` ChallengeCount []int64 `json:"challengeCount"` ReviveCount []int64 `json:"reviveCount"` RepelCount []int64 `json:"repelCount"` AwardCount []int64 `json:"awardCount"` MinuteMix1Count []int64 `json:"minuteMix1Count"` MinuteMix2Count []int64 `json:"minuteMix2Count"` MinuteMix3Count []int64 `json:"minuteMix3Count"` MinuteCount []int64 `json:"minuteCount"` ExchangeData []string `json:"exchangeData"` AvgChallengeCount []int64 `json:"avgChallengeCount"` AvgReviveCount []int64 `json:"avgReviveCount"` AvgRepelCount []int64 `json:"avgRepelCount"` AvgAwardCount []int64 `json:"avgAwardCount"` AvgMinuteMix1Count []float64 `json:"avgMinuteMix1Count"` AvgMinuteMix2Count []float64 `json:"avgMinuteMix2Count"` AvgMinuteMix3Count []float64 `json:"avgMinuteMix3Count"` AvgMinuteCount []int64 `json:"avgMinuteCount"` } type BossLogItem struct { Day string `json:"days"` Players int64 `json:"players"` MaxStage int64 `json:"maxStage"` ChallengeCount int64 `json:"challengeCount"` ReviveCount int64 `json:"reviveCount"` RepelCount int64 `json:"repelCount"` AwardCount int64 `json:"awardCount"` MinuteMix1Count int64 `json:"minuteMix1Count"` MinuteMix2Count int64 `json:"minuteMix2Count"` MinuteMix3Count int64 `json:"minuteMix3Count"` MinuteCount int64 `json:"minuteCount"` ExchangeData string `json:"exchangeData"` AvgChallengeCount float64 `json:"avgChallengeCount"` AvgReviveCount float64 `json:"avgReviveCount"` AvgRepelCount float64 `json:"avgRepelCount"` AvgAwardCount float64 `json:"avgAwardCount"` AvgMinuteMix1Count float64 `json:"avgMinuteMix1Count"` AvgMinuteMix2Count float64 `json:"avgMinuteMix2Count"` AvgMinuteMix3Count float64 `json:"avgMinuteMix3Count"` AvgMinuteCount int64 `json:"avgMinuteCount"` } type BossInfo struct { Info BossReportInfo `json:"info"` Rows []BossLogItem `json:"rows"` } // QueryBossLog 统计暗影突袭信息 func (s *sDash) QueryBossLog(params forms.BossReportReq) (respData BossInfo, err error) { DB, err := player.GetDBByServerID(params.ServerId) if err != nil { return } resp := BossReportInfo{} boss := query.Use(config.GDBGroup[DB]).Bosswar rdb := query.Use(config.DB).ReportDayBoss type ReportDayBoss struct { model.ReportDayBoss Exchange map[int]int } var result []*ReportDayBoss m := rdb.Where(rdb.Date.Gte(params.Day), rdb.Date.Lt(params.EndDay)).Order(rdb.Date.Desc()) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } add := func(row *model.ReportDayBoss) { var ( exist bool ) for _, v := range result { if v.Date == row.Date { exist = true v.PlayerCount += row.PlayerCount v.ChallengeCount += row.ChallengeCount v.MaxStage += row.MaxStage v.ReviveCount += row.ReviveCount v.RepelCount += row.RepelCount v.AwardCount += row.AwardCount v.MinuteMix1 += row.MinuteMix1 v.MinuteMix2 += row.MinuteMix2 v.MinuteMix3 += row.MinuteMix3 v.TotalMinute += row.TotalMinute var tempExchange = make(map[int]int) if row.ExchangeData != "" { if err = json.Unmarshal([]byte(row.ExchangeData), &tempExchange); err != nil { logrus.Warnf("ExchangeData Unmarshal err :%+v", err) return } } if v.Exchange == nil { v.Exchange = make(map[int]int) } for key, value := range tempExchange { if _, ok := v.Exchange[key]; ok { v.Exchange[key] += value } else { v.Exchange[key] = value } } } } if exist == false { v := new(ReportDayBoss) v.Date = row.Date v.ChannelID = row.ChannelID v.PlayerCount += row.PlayerCount v.ChallengeCount += row.ChallengeCount v.MaxStage += row.MaxStage v.ReviveCount += row.ReviveCount v.RepelCount += row.RepelCount v.AwardCount += row.AwardCount v.MinuteMix1 += row.MinuteMix1 v.MinuteMix2 += row.MinuteMix2 v.MinuteMix3 += row.MinuteMix3 v.TotalMinute += row.TotalMinute var tempExchange = make(map[int]int) if row.ExchangeData != "" { if err = json.Unmarshal([]byte(row.ExchangeData), &tempExchange); err != nil { logrus.Warnf("ExchangeData Unmarshal err :%+v", err) return } } if v.Exchange == nil { v.Exchange = make(map[int]int) } for key, value := range tempExchange { if _, ok := v.Exchange[key]; ok { v.Exchange[key] += value } else { v.Exchange[key] = value } } result = append(result, v) } } distributes, err := m.Find() if err != nil { return } for _, distribute := range distributes { add(distribute) } for _, v := range result { if v != nil { exchangeData, err := json.Marshal(v.Exchange) if err != nil { logrus.Warnf("exchangeData Marshal failed:%+v", err) return respData, err } v.ExchangeData = string(exchangeData) resp.Days = append(resp.Days, strings.ReplaceAll(v.Date, "T00:00:00+08:00", "")) resp.Players = append(resp.Players, v.PlayerCount) resp.MaxStage = append(resp.MaxStage, v.MaxStage) resp.ChallengeCount = append(resp.ChallengeCount, v.ChallengeCount) resp.ReviveCount = append(resp.ReviveCount, v.ReviveCount) resp.RepelCount = append(resp.RepelCount, int64(v.RepelCount)) resp.AwardCount = append(resp.AwardCount, int64(v.AwardCount)) resp.MinuteMix1Count = append(resp.MinuteMix1Count, v.MinuteMix1) resp.MinuteMix2Count = append(resp.MinuteMix2Count, v.MinuteMix2) resp.MinuteMix3Count = append(resp.MinuteMix3Count, v.MinuteMix3) resp.MinuteCount = append(resp.MinuteCount, v.TotalMinute) resp.ExchangeData = append(resp.ExchangeData, v.ExchangeData) var players int64 = 1 if v.PlayerCount > 1 { players = v.PlayerCount } resp.AvgChallengeCount = append(resp.AvgChallengeCount, v.ChallengeCount*100/players) resp.AvgReviveCount = append(resp.AvgReviveCount, v.ReviveCount*100/players) resp.AvgRepelCount = append(resp.AvgRepelCount, int64(v.RepelCount)*100/players) resp.AvgAwardCount = append(resp.AvgAwardCount, int64(v.AwardCount)*100/players) minutes := v.MinuteMix1 + v.MinuteMix2 + v.MinuteMix3 if minutes < 1 { minutes = 1 } resp.AvgMinuteMix1Count = append(resp.AvgMinuteMix1Count, float64(v.MinuteMix1*100/minutes)) resp.AvgMinuteMix2Count = append(resp.AvgMinuteMix2Count, float64(v.MinuteMix2*100/minutes)) resp.AvgMinuteMix3Count = append(resp.AvgMinuteMix3Count, float64(v.MinuteMix3*100/minutes)) resp.AvgMinuteCount = append(resp.AvgMinuteCount, v.TotalMinute/players) // 最大阶段 var maxStage int64 stamp, _ := now.ParseInLocation(time.Local, v.Date) first, err := boss.Where(boss.YearDay.Eq(int32(utility.GetYearDay2(&stamp)))).First() if err == nil && first != nil { maxStage = int64(first.Fighting) } item := BossLogItem{ Day: strings.ReplaceAll(v.Date, "T00:00:00+08:00", ""), Players: v.PlayerCount, MaxStage: maxStage, ChallengeCount: v.ChallengeCount, ReviveCount: v.ReviveCount, RepelCount: int64(v.RepelCount), AwardCount: int64(v.AwardCount), MinuteMix1Count: int64(v.MinuteMix1), MinuteMix2Count: int64(v.MinuteMix2), MinuteMix3Count: int64(v.MinuteMix3), MinuteCount: v.TotalMinute, } item.AvgChallengeCount = float64(v.ChallengeCount*100/players) / 100 item.AvgReviveCount = float64(v.ReviveCount*100/players) / 100 item.AvgRepelCount = float64(int64(v.RepelCount)*100/players) / 100 item.AvgAwardCount = float64(int64(v.AwardCount)*100/players) / 100 item.ExchangeData = v.ExchangeData item.AvgMinuteMix1Count = float64(v.MinuteMix1 * 100 / minutes) item.AvgMinuteMix2Count = float64(v.MinuteMix2 * 100 / minutes) item.AvgMinuteMix3Count = float64(v.MinuteMix3 * 100 / minutes) item.AvgMinuteCount = int64(v.TotalMinute*100/players) / 100 respData.Rows = append(respData.Rows, item) } } respData.Info = resp return } type IdiomReportInfo struct { Days []string `json:"days"` Players []int64 `json:"players"` BreakCount []int64 `json:"breakCount"` TipsCount []int64 `json:"tipsCount"` IntegralCount []int64 `json:"integralCount"` ReceiveCount []int64 `json:"receiveCount"` InkAdvCount []int64 `json:"inkAdvCount"` InkDrillCount []int64 `json:"inkDrillCount"` RankingCount []int64 `json:"rankingCount"` AwardCount []int64 `json:"awardCount"` GearData []string `json:"gearData"` AvgBreakCount []int64 `json:"avgBreakCount"` AvgInkAdvCount []int64 `json:"avgInkAdvCount"` AvgInkDrillCount []int64 `json:"avgInkDrillCount"` AvgTipsCount []int64 `json:"avgTipsCount"` AvgIntegralCount []int64 `json:"avgIntegralCount"` AvgReceiveCount []int64 `json:"avgReceiveCount"` AvgRankingCount []int64 `json:"avgRankingCount"` AvgAwardCount []int64 `json:"avgAwardCount"` } type IdiomLogItem struct { Day string `json:"days"` Players int64 `json:"players"` BreakCount int64 `json:"breakCount"` TipsCount int64 `json:"tipsCount"` IntegralCount int64 `json:"integralCount"` ReceiveCount int64 `json:"receiveCount"` InkAdvCount int64 `json:"inkAdvCount"` InkDrillCount int64 `json:"inkDrillCount"` RankingCount int64 `json:"rankingCount"` AwardCount int64 `json:"awardCount"` GearData string `json:"gearData"` AvgBreakCount float64 `json:"avgBreakCount"` AvgInkAdvCount float64 `json:"avgInkAdvCount"` AvgInkDrillCount float64 `json:"avgInkDrillCount"` AvgTipsCount float64 `json:"avgTipsCount"` AvgIntegralCount float64 `json:"avgIntegralCount"` AvgReceiveCount float64 `json:"avgReceiveCount"` AvgRankingCount float64 `json:"avgRankingCount"` AvgAwardCount float64 `json:"avgAwardCount"` } type IdiomInfo struct { Info IdiomReportInfo `json:"info"` Rows []IdiomLogItem `json:"rows"` } // QueryIdiomLog 统计金榜题名信息 func (s *sDash) QueryIdiomLog(params forms.IdiomReportReq) (respData IdiomInfo, err error) { resp := IdiomReportInfo{} rdb := query.Use(config.DB).ReportDayIdiom type Gear struct { GearId int `json:"GearId"` Count int `json:"Count"` } type ReportDayIdiom struct { model.ReportDayIdiom Gear []*Gear } findFail := func(id int, lst []*Gear) *Gear { for _, v := range lst { if v.GearId == id { return v } } return nil } var result []*ReportDayIdiom m := rdb.Where(rdb.Date.Gte(params.Day), rdb.Date.Lt(params.EndDay)).Order(rdb.Date.Desc()) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } add := func(row *model.ReportDayIdiom) { var exist bool for _, v := range result { if v.Date == row.Date { exist = true v.PlayerCount += row.PlayerCount v.BreakCount += row.BreakCount v.TipsCount += row.TipsCount v.IntegralCount += row.IntegralCount v.ReceiveCount += row.ReceiveCount v.InkAdvCount += row.InkAdvCount v.InkDrillCount += row.InkDrillCount v.RankingCount += row.RankingCount v.AwardCount += row.AwardCount var tempGear []*Gear if row.GearData != "" { if err = json.Unmarshal([]byte(row.GearData), &tempGear); err != nil { logrus.Warnf("GearData Unmarshal err :%+v", err) return } } for _, temp := range tempGear { cas := findFail(temp.GearId, v.Gear) if cas != nil { cas.Count += temp.Count } else { v.Gear = append(v.Gear, &Gear{ GearId: temp.GearId, Count: temp.Count, }) } } } } if exist == false { v := new(ReportDayIdiom) v.Date = row.Date v.ChannelID = row.ChannelID v.PlayerCount += row.PlayerCount v.BreakCount += row.BreakCount v.TipsCount += row.TipsCount v.IntegralCount += row.IntegralCount v.ReceiveCount += row.ReceiveCount v.InkAdvCount += row.InkAdvCount v.InkDrillCount += row.InkDrillCount v.RankingCount += row.RankingCount v.AwardCount += row.AwardCount var tempGear []*Gear if row.GearData != "" { if err = json.Unmarshal([]byte(row.GearData), &tempGear); err != nil { logrus.Warnf("GearData2 Unmarshal err :%+v", err) return } } for _, temp := range tempGear { cas := findFail(temp.GearId, v.Gear) if cas != nil { cas.Count += temp.Count } else { v.Gear = append(v.Gear, &Gear{ GearId: temp.GearId, Count: temp.Count, }) } } result = append(result, v) } } distributes, err := m.Find() if err != nil { return } for _, distribute := range distributes { add(distribute) } for _, v := range result { if v != nil { gearData, err := json.Marshal(v.Gear) if err != nil { logrus.Warnf("gearData Marshal failed:%+v", err) return respData, err } v.GearData = string(gearData) resp.Days = append(resp.Days, strings.ReplaceAll(v.Date, "T00:00:00+08:00", "")) resp.Players = append(resp.Players, v.PlayerCount) resp.BreakCount = append(resp.BreakCount, v.BreakCount) resp.TipsCount = append(resp.TipsCount, v.TipsCount) resp.IntegralCount = append(resp.IntegralCount, v.IntegralCount) resp.ReceiveCount = append(resp.ReceiveCount, v.ReceiveCount) resp.InkAdvCount = append(resp.InkAdvCount, v.InkAdvCount) resp.InkDrillCount = append(resp.InkDrillCount, v.InkDrillCount) resp.RankingCount = append(resp.RankingCount, v.RankingCount) resp.AwardCount = append(resp.AwardCount, v.AwardCount) resp.GearData = append(resp.GearData, v.GearData) var players int64 = 1 if v.PlayerCount > 1 { players = v.PlayerCount } resp.AvgBreakCount = append(resp.AvgBreakCount, v.BreakCount*100/players) resp.AvgInkAdvCount = append(resp.AvgInkAdvCount, v.InkAdvCount*100/players) resp.AvgInkDrillCount = append(resp.AvgInkDrillCount, v.InkDrillCount*100/players) resp.AvgTipsCount = append(resp.AvgTipsCount, v.TipsCount*100/players) resp.AvgIntegralCount = append(resp.AvgIntegralCount, v.IntegralCount*100/players) resp.AvgReceiveCount = append(resp.AvgReceiveCount, v.ReceiveCount*100/players) resp.AvgRankingCount = append(resp.AvgRankingCount, v.RankingCount*100/players) resp.AvgAwardCount = append(resp.AvgAwardCount, v.AwardCount*100/players) item := IdiomLogItem{ Day: strings.ReplaceAll(v.Date, "T00:00:00+08:00", ""), Players: v.PlayerCount, BreakCount: v.BreakCount, InkAdvCount: v.InkAdvCount, InkDrillCount: v.InkDrillCount, TipsCount: v.TipsCount, IntegralCount: v.IntegralCount, ReceiveCount: v.ReceiveCount, RankingCount: v.RankingCount, AwardCount: v.AwardCount, } item.AvgBreakCount = float64(v.BreakCount*100/players) / 100 item.AvgInkAdvCount = float64(v.InkAdvCount*100/players) / 100 item.AvgInkDrillCount = float64(v.InkDrillCount*100/players) / 100 item.AvgTipsCount = float64(v.TipsCount*100/players) / 100 item.AvgIntegralCount = float64(v.IntegralCount*100/players) / 100 item.AvgReceiveCount = float64(v.ReceiveCount*100/players) / 100 item.AvgRankingCount = float64(v.RankingCount*100/players) / 100 item.AvgAwardCount = float64(v.AwardCount*100/players) / 100 item.GearData = v.GearData respData.Rows = append(respData.Rows, item) } } respData.Info = resp return } type DuelReportInfo struct { Days []string `json:"days"` Players []int64 `json:"players"` RefreshCount []int64 `json:"refreshCount"` DuelCount []int64 `json:"duelCount"` DuelFreeCount []int64 `json:"duelFreeCount"` DuelBattleCount []int64 `json:"duelBattleCount"` DuelRevengeCount []int64 `json:"duelRevengeCount"` BattleAdvCount []int64 `json:"battleAdvCount"` BattleDrillCount []int64 `json:"battleDrillCount"` AdjustmentCount []int64 `json:"adjustmentCount"` RevengeCount []int64 `json:"revengeCount"` RankingCount []int64 `json:"rankingCount"` AwardCount []int64 `json:"awardCount"` AdjustDuel []string `json:"adjustDuel"` AvgRefreshCount []int64 `json:"avgRefreshCount"` AvgDuelCount []int64 `json:"avgDuelCount"` AvgDuelFreeCount []int64 `json:"avgDuelFreeCount"` AvgDuelBattleCount []int64 `json:"avgDuelBattleCount"` AvgDuelRevengeCount []int64 `json:"avgDuelRevengeCount"` AvgBattleAdvCount []int64 `json:"avgBattleAdvCount"` AvgBattleDrillCount []int64 `json:"avgBattleDrillCount"` AvgAdjustmentCount []int64 `json:"avgAdjustmentCount"` AvgRevengeCount []int64 `json:"avgRevengeCount"` AvgRankingCount []int64 `json:"avgRankingCount"` AvgAwardCount []int64 `json:"avgAwardCount"` } type DuelLogItem struct { Day string `json:"days"` Players int64 `json:"players"` RefreshCount int64 `json:"refreshCount"` DuelCount int64 `json:"duelCount"` DuelFreeCount int64 `json:"duelFreeCount"` DuelBattleCount int64 `json:"duelBattleCount"` DuelRevengeCount int64 `json:"duelRevengeCount"` BattleAdvCount int64 `json:"battleAdvCount"` BattleDrillCount int64 `json:"battleDrillCount"` AdjustmentCount int64 `json:"adjustmentCount"` RevengeCount int64 `json:"revengeCount"` RankingCount int64 `json:"rankingCount"` AwardCount int64 `json:"awardCount"` AdjustDuel string `json:"adjustDuel"` AvgRefreshCount float64 `json:"avgRefreshCount"` AvgDuelCount float64 `json:"avgDuelCount"` AvgDuelFreeCount float64 `json:"avgDuelFreeCount"` AvgDuelBattleCount float64 `json:"avgDuelBattleCount"` AvgDuelRevengeCount float64 `json:"avgDuelRevengeCount"` AvgBattleAdvCount float64 `json:"avgBattleAdvCount"` AvgBattleDrillCount float64 `json:"avgBattleDrillCount"` AvgAdjustmentCount float64 `json:"avgAdjustmentCount"` AvgRevengeCount float64 `json:"avgRevengeCount"` AvgRankingCount float64 `json:"avgRankingCount"` AvgAwardCount float64 `json:"avgAwardCount"` } type DuelInfo struct { Info DuelReportInfo `json:"info"` Rows []DuelLogItem `json:"rows"` } // QueryDuelLog 统计狭路对决信息 func (s *sDash) QueryDuelLog(params forms.DuelReportReq) (respData DuelInfo, err error) { resp := DuelReportInfo{} rdb := query.Use(config.DB).ReportDayDuel m := rdb.Select(rdb.ALL, rdb.ID, rdb.Date, rdb.PlayerCount.Sum().As("player_count"), rdb.RefreshCount.Sum().As("refresh_count"), rdb.DuelCount.Sum().As("duel_count"), rdb.DuelFreeCount.Sum().As("duel_free_count"), rdb.DuelBattleCount.Sum().As("duel_battle_count"), rdb.DuelRevengeCount.Sum().As("duel_revenge_count"), rdb.BattleAdvCount.Sum().As("battle_adv_count"), rdb.BattleDrillCount.Sum().As("battle_drill_count"), rdb.AdjustmentCount.Sum().As("adjustment_count"), rdb.RevengeCount.Sum().As("revenge_count"), rdb.RankingCount.Sum().As("ranking_count"), rdb.AwardCount.Sum().As("award_count"), ).Where(rdb.Date.Gte(params.Day), rdb.Date.Lt(params.EndDay)) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int64(params.ServerId))) } result, err := m.Group(rdb.Date).Order(rdb.Date.Desc()).Find() if err != nil { return respData, err } for _, v := range result { if v != nil { resp.Days = append(resp.Days, strings.ReplaceAll(v.Date, "T00:00:00+08:00", "")) resp.Players = append(resp.Players, v.PlayerCount) resp.RefreshCount = append(resp.RefreshCount, v.RefreshCount) resp.DuelCount = append(resp.DuelCount, v.DuelCount) resp.DuelFreeCount = append(resp.DuelFreeCount, v.DuelFreeCount) resp.DuelBattleCount = append(resp.DuelBattleCount, v.DuelBattleCount) resp.DuelRevengeCount = append(resp.DuelRevengeCount, v.DuelRevengeCount) resp.BattleAdvCount = append(resp.BattleAdvCount, v.BattleAdvCount) resp.BattleDrillCount = append(resp.BattleDrillCount, v.BattleDrillCount) resp.AdjustmentCount = append(resp.AdjustmentCount, v.AdjustmentCount) resp.RevengeCount = append(resp.RevengeCount, v.RevengeCount) resp.RankingCount = append(resp.RankingCount, v.RankingCount) resp.AwardCount = append(resp.AwardCount, v.AwardCount) resp.AdjustDuel = append(resp.AdjustDuel, fmt.Sprintf("%.2f", float64(v.AdjustmentCount)/float64(v.DuelCount)*100)+"%") var players int64 = 1 if v.PlayerCount > 1 { players = v.PlayerCount } resp.AvgRefreshCount = append(resp.AvgRefreshCount, v.RefreshCount*100/players) resp.AvgDuelCount = append(resp.AvgDuelCount, v.DuelCount*100/players) resp.AvgDuelFreeCount = append(resp.AvgDuelFreeCount, v.DuelFreeCount*100/players) resp.AvgDuelBattleCount = append(resp.AvgDuelBattleCount, v.DuelBattleCount*100/players) resp.AvgDuelRevengeCount = append(resp.AvgDuelRevengeCount, v.DuelRevengeCount*100/players) resp.AvgBattleAdvCount = append(resp.AvgBattleAdvCount, v.BattleAdvCount*100/players) resp.AvgBattleDrillCount = append(resp.AvgBattleDrillCount, v.BattleDrillCount*100/players) resp.AvgAdjustmentCount = append(resp.AvgAdjustmentCount, v.AdjustmentCount*100/players) resp.AvgRevengeCount = append(resp.AvgRevengeCount, v.RevengeCount*100/players) resp.AvgRankingCount = append(resp.AvgRankingCount, v.RankingCount*100/players) resp.AvgAwardCount = append(resp.AvgAwardCount, v.AwardCount*100/players) item := DuelLogItem{ Day: strings.ReplaceAll(v.Date, "T00:00:00+08:00", ""), Players: v.PlayerCount, RefreshCount: v.RefreshCount, DuelCount: v.DuelCount, DuelFreeCount: v.DuelFreeCount, DuelBattleCount: v.DuelBattleCount, DuelRevengeCount: v.DuelRevengeCount, BattleAdvCount: v.BattleAdvCount, BattleDrillCount: v.BattleDrillCount, AdjustmentCount: v.AdjustmentCount, RevengeCount: v.RevengeCount, RankingCount: v.RankingCount, AwardCount: v.AwardCount, AdjustDuel: fmt.Sprintf("%.2f", float64(v.AdjustmentCount)/float64(v.DuelCount)*100) + "%", } item.AvgRefreshCount = float64(v.RefreshCount*100/players) / 100 item.AvgDuelCount = float64(v.DuelCount*100/players) / 100 item.AvgDuelFreeCount = float64(v.DuelFreeCount*100/players) / 100 item.AvgDuelBattleCount = float64(v.DuelBattleCount*100/players) / 100 item.AvgDuelRevengeCount = float64(v.DuelRevengeCount*100/players) / 100 item.AvgBattleAdvCount = float64(v.BattleAdvCount*100/players) / 100 item.AvgBattleDrillCount = float64(v.BattleDrillCount*100/players) / 100 item.AvgAdjustmentCount = float64(v.AdjustmentCount*100/players) / 100 item.AvgRevengeCount = float64(v.RevengeCount*100/players) / 100 item.AvgRankingCount = float64(v.RankingCount*100/players) / 100 item.AvgAwardCount = float64(v.AwardCount*100/players) / 100 respData.Rows = append(respData.Rows, item) } } respData.Info = resp return } type ExpeditionReportInfo struct { Days []string `json:"days"` Players []int64 `json:"players"` BreakCount []int64 `json:"breakCount"` SweepCount []int64 `json:"sweepCount"` StarsAdvCount []int64 `json:"starsAdvCount"` StarsDrillCount []int64 `json:"starsDrillCount"` FailData []string `json:"failData"` AvgBreakCount []int64 `json:"avgBreakCount"` AvgSweepCount []int64 `json:"avgSweepCount"` AvgStarsAdvCount []int64 `json:"avgStarsAdvCount"` AvgStarsDrillCount []int64 `json:"avgStarsDrillCount"` } type ExpeditionLogItem struct { Day string `json:"days"` Players int64 `json:"players"` BreakCount int64 `json:"breakCount"` SweepCount int64 `json:"sweepCount"` StarsAdvCount int64 `json:"starsAdvCount"` StarsDrillCount int64 `json:"starsDrillCount"` OutputData string `json:"outputData"` AvgBreakCount float64 `json:"avgBreakCount"` AvgSweepCount float64 `json:"avgSweepCount"` AvgStarsAdvCount float64 `json:"avgStarsAdvCount"` AvgStarsDrillCount float64 `json:"avgStarsDrillCount"` } type ExpeditionInfo struct { Info ExpeditionReportInfo `json:"info"` Rows []ExpeditionLogItem `json:"rows"` } // QueryExpeditionLog 统计远征信息 func (s *sDash) QueryExpeditionLog(params forms.ExpeditionReportReq) (respData ExpeditionInfo, err error) { resp := ExpeditionReportInfo{} rdb := query.Use(config.DB).ReportDayExpedition type Fail struct { Floor int `json:"floor"` Count int `json:"count"` } type ReportDayExpedition struct { model.ReportDayExpedition Fail []*Fail Output map[int]int } findFail := func(id int, lst []*Fail) *Fail { for _, v := range lst { if v.Floor == id { return v } } return nil } var result []*ReportDayExpedition m := rdb.Where(rdb.Date.Gte(params.Day), rdb.Date.Lt(params.EndDay)).Order(rdb.Date.Desc()) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } add := func(row *model.ReportDayExpedition) { var ( exist bool ) for _, v := range result { if v.Date == row.Date { exist = true v.PlayerCount += row.PlayerCount v.BreakCount += row.BreakCount v.SweepCount += row.SweepCount v.StarsAdvCount += row.StarsAdvCount v.StarsDrillCount += row.StarsDrillCount var tempFail []*Fail if row.FailData != "" { if err = json.Unmarshal([]byte(row.FailData), &tempFail); err != nil { logrus.Warnf("FailData Unmarshal err :%+v", err) return } } for _, temp := range tempFail { cas := findFail(temp.Floor, v.Fail) if cas != nil { cas.Count += temp.Count } else { v.Fail = append(v.Fail, &Fail{ Floor: temp.Floor, Count: temp.Count, }) } } var tempOutput = make(map[int]int) if row.OutputData != "" { if err = json.Unmarshal([]byte(row.OutputData), &tempOutput); err != nil { logrus.Warnf("OutputData Unmarshal err :%+v", err) return } } if v.Output == nil { v.Output = make(map[int]int) } for key, value := range tempOutput { if _, ok := v.Output[key]; ok { v.Output[key] += value } else { v.Output[key] = value } } } } if exist == false { v := new(ReportDayExpedition) v.Date = row.Date v.ChannelID = row.ChannelID v.PlayerCount += row.PlayerCount v.BreakCount += row.BreakCount v.SweepCount += row.SweepCount v.StarsAdvCount += row.StarsAdvCount v.StarsDrillCount += row.StarsDrillCount var tempFail []*Fail if row.FailData != "" { if err = json.Unmarshal([]byte(row.FailData), &tempFail); err != nil { logrus.Warnf("FailData2 Unmarshal err :%+v", err) return } } for _, temp := range tempFail { cas := findFail(temp.Floor, v.Fail) if cas != nil { cas.Count += temp.Count } else { v.Fail = append(v.Fail, &Fail{ Floor: temp.Floor, Count: temp.Count, }) } } var tempOutput = make(map[int]int) if row.OutputData != "" { if err = json.Unmarshal([]byte(row.OutputData), &tempOutput); err != nil { logrus.Warnf("OutputData2 Unmarshal err :%+v", err) return } } if v.Output == nil { v.Output = make(map[int]int) } for key, value := range tempOutput { if _, ok := v.Output[key]; ok { v.Output[key] += value } else { v.Output[key] = value } } result = append(result, v) } } distributes, err := m.Find() if err != nil { return } for _, distribute := range distributes { add(distribute) } for _, v := range result { if v != nil { failData, err := json.Marshal(v.Fail) if err != nil { logrus.Warnf("failData Marshal failed:%+v", err) return respData, err } v.FailData = string(failData) outputData, err := json.Marshal(v.Output) if err != nil { logrus.Warnf("outputData Marshal failed:%+v", err) return respData, err } v.OutputData = string(outputData) resp.Days = append(resp.Days, strings.ReplaceAll(v.Date, "T00:00:00+08:00", "")) resp.Players = append(resp.Players, v.PlayerCount) resp.BreakCount = append(resp.BreakCount, v.BreakCount) resp.SweepCount = append(resp.SweepCount, v.SweepCount) resp.StarsAdvCount = append(resp.StarsAdvCount, v.StarsAdvCount) resp.StarsDrillCount = append(resp.StarsDrillCount, v.StarsDrillCount) resp.FailData = append(resp.FailData, v.FailData) var players int64 = 1 if v.PlayerCount > 1 { players = v.PlayerCount } resp.AvgBreakCount = append(resp.AvgBreakCount, v.BreakCount*100/players) resp.AvgSweepCount = append(resp.AvgSweepCount, v.SweepCount*100/players) resp.AvgStarsAdvCount = append(resp.AvgStarsAdvCount, v.StarsAdvCount*100/players) resp.AvgStarsDrillCount = append(resp.AvgStarsDrillCount, v.StarsDrillCount*100/players) item := ExpeditionLogItem{ Day: strings.ReplaceAll(v.Date, "T00:00:00+08:00", ""), Players: v.PlayerCount, BreakCount: v.BreakCount, SweepCount: v.SweepCount, StarsAdvCount: v.StarsAdvCount, StarsDrillCount: v.StarsDrillCount, OutputData: v.OutputData, } item.AvgBreakCount = float64(v.BreakCount*100/players) / 100 item.AvgSweepCount = float64(v.SweepCount*100/players) / 100 item.AvgStarsAdvCount = float64(v.StarsAdvCount*100/players) / 100 item.AvgStarsDrillCount = float64(v.StarsDrillCount*100/players) / 100 respData.Rows = append(respData.Rows, item) } } respData.Info = resp return } // QueryExpeditionFloor 获取所有材料 func (s *sDash) QueryExpeditionFloor(params forms.ExpeditionFloorReq) (resp *model.ReportDayExpedition, err error) { q := query.Use(config.DB).ReportDayExpedition m := q.Where(q.Date.Eq(params.Day)) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(q.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(q.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(q.ServerID.Eq(int32(params.ServerId))) } type Fail struct { Floor int `json:"floor"` Count int `json:"count"` } var fail []*Fail findFail := func(id int, lst []*Fail) *Fail { for _, v := range lst { if v.Floor == id { return v } } return nil } lists, err := m.Find() if err != nil { return nil, err } resp = new(model.ReportDayExpedition) for k, row := range lists { if k == 0 { resp = row } var tempFail []*Fail if row.FailData != "" { if err = json.Unmarshal([]byte(row.FailData), &tempFail); err != nil { logrus.Warnf("FailData2 Unmarshal err :%+v", err) return } } for _, temp := range tempFail { cas := findFail(temp.Floor, fail) if cas != nil { cas.Count += temp.Count } else { fail = append(fail, &Fail{ Floor: temp.Floor, Count: temp.Count, }) } } } failData, err := json.Marshal(fail) if err != nil { logrus.Warnf("failData Marshal failed:%+v", err) return resp, err } resp.FailData = string(failData) return } func (s *sDash) QueryGoodsReport(params forms.GoodsReportReq) (resp forms.GoodsReportRespData, err error) { var ( positionIds []int p = query.Use(config.DB).ReportDayGoodsBasic pm = p.Select(p.PositionID.Distinct()) ) if params.ServerId > 0 { pm = pm.Where(p.ServerID.Eq(int32(params.ServerId))) } err = pm.Where(p.Date.Eq(params.Day)).Pluck(p.PositionID, &positionIds) if err != nil { logrus.WithField("from", "QueryAdvReport Pluck").Error(err) return } for _, positionId := range positionIds { rdb := query.Use(config.DB).ReportDayGoodsBasic m := rdb. Select(rdb.ID, rdb.Date, rdb.PositionID, rdb.ShowTimes.Sum().As("show_times"), rdb.ClickTimes.Sum().As("click_times"), rdb.SuccessTimes.Sum().As("success_times"), rdb.ShowUsers.Sum().As("show_users"), rdb.ClickUsers.Sum().As("click_users"), rdb.SuccessUsers.Sum().As("success_users"), ). Where(rdb.PositionID.Eq(int64(positionId)), rdb.Date.Eq(params.Day)) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } results, err := m. Order(rdb.Date.Desc()). Group(rdb.Date). Limit(params.PerPage). Offset((params.Page - 1) * params.PerPage). Find() // .Order(rdb.Date.Desc()) if err != nil { return resp, err } for _, result := range results { var comment = "充值" if result.PositionID/10000 == 1 { comment = "礼包" } comment += fmt.Sprintf(",商品id(%d)", result.PositionID%1000) if result.PositionID%10000/1000 == 2 { comment += ",双倍获取" } resp.Data = append(resp.Data, forms.GoodsReportItem{ ID: result.ID, PositionId: result.PositionID, GoodsId: result.PositionID % 1000, ShowTimes: result.ShowTimes, ClickTimes: result.ClickTimes, SuccessTimes: result.SuccessTimes, ShowUsers: result.ShowUsers, ClickUsers: result.ClickUsers, SuccessUsers: result.SuccessUsers, Comment: comment, }) } resp.Total = int64(len(results)) if err != nil { return resp, err } } return } type GudongReportInfo struct { Days []string `json:"days"` Players []int64 `json:"players"` Jinglis []int64 `json:"jinglis"` AdJinglis []int64 `json:"adJinglis"` DiamondJinglis []int64 `json:"diamondJinglis"` AdDiamonds []int64 `json:"adDiamonds"` RmbDiamonds []int64 `json:"rmbDiamonds"` SpeedUps []int64 `json:"speedUps"` BoxDetails []string `json:"boxDetails"` IdentifyGoods []int64 `json:"identifyGoods"` UpgradeGoods []int64 `json:"upgradeGoods"` ShowRevenue []int64 `json:"showRevenues"` Ranking []int64 `json:"rankings"` RankingBenifit []int64 `json:"rankingBenifit"` ExchangeGoods []string `json:"exchangeGoods"` SellGoods []int64 `json:"sellGoods"` GetGoods []int64 `json:"getGoods"` AvgJinglis []int64 `json:"avgJinglis"` AvgAdJinglis []int64 `json:"avgAdJinglis"` AvgDiamondJinglis []int64 `json:"avgDiamondJinglis"` AvgRmbJinglis []int64 `json:"avgRmbJinglis"` AvgSpeedUps []int64 `json:"avgSpeedUps"` AvgBoxDetails []string `json:"avgBoxDetails"` AvgIdentifyGoods []int64 `json:"avgIdentifyGoods"` AvgUpgradeGoods []int64 `json:"avgUpgradeGoods"` AvgShowRevenue []int64 `json:"avgShowRevenues"` AvgRanking []int64 `json:"avgRankings"` AvgRankingBenifit []int64 `json:"avgRankingBenifit"` AvgSellGoods []int64 `json:"avgSellGoods"` } type GudongLogItem struct { Day string `json:"days"` Players int64 `json:"players"` Jinglis int64 `json:"jinglis"` AdJinglis int64 `json:"adJinglis"` DiamondJinglis int64 `json:"diamondJinglis"` AdDiamonds int64 `json:"adDiamonds"` RmbDiamonds int64 `json:"rmbDiamonds"` SpeedUps int64 `json:"speedUps"` BoxDetails string `json:"boxDetails"` IdentifyGoods int64 `json:"identifyGoods"` UpgradeGoods int64 `json:"upgradeGoods"` ShowRevenue int64 `json:"showRevenues"` Ranking int64 `json:"rankings"` RankingBenifit int64 `json:"rankingBenifit"` ExchangeGoods string `json:"exchangeGoods"` SellGoods int64 `json:"sellGoods"` GetGoods int64 `json:"getGoods"` GetGoodsDetail string `json:"getGoodsDetail"` AvgJinglis float64 `json:"avgJinglis"` AvgAdJinglis float64 `json:"avgAdJinglis"` AvgDiamondJinglis float64 `json:"avgDiamondJinglis"` AvgAdDiamonds float64 `json:"avgAdDiamonds"` AvgRmbDiamonds float64 `json:"avgRmbDiamonds"` AvgSpeedUps float64 `json:"avgSpeedUps"` AvgBoxDetails float64 `json:"avgBoxDetails"` AvgIdentifyGoods float64 `json:"avgIdentifyGoods"` AvgUpgradeGoods float64 `json:"avgUpgradeGoods"` AvgShowRevenue float64 `json:"avgShowRevenues"` AvgRanking float64 `json:"avgRankings"` AvgRankingBenifit float64 `json:"avgRankingBenifit"` AvgSellGoods float64 `json:"avgSellGoods"` AvgGetGoods float64 `json:"avgGetGoods"` } type GudongInfo struct { Info GudongReportInfo `json:"info"` Rows []GudongLogItem `json:"rows"` } // 查询古董的统计信息 func (s *sDash) QueryGudongLog(params forms.GoodsReportReq) (respData GudongInfo, err error) { resp := GudongReportInfo{} rdb := query.Use(config.DB).ReportDayGudong type Goods map[string]int type ReportDayGudong struct { model.ReportDayGudong Exchange Goods SpeedUp Goods Detail Goods } findGoods := func(id string, lst Goods) bool { for k, _ := range lst { if k == id { return true } } return false } var result []*ReportDayGudong m := rdb.Where(rdb.Date.Gte(params.Day), rdb.Date.Lt(params.EndDay)).Order(rdb.Date.Desc()) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } add := func(row *model.ReportDayGudong) { var ( exist bool ) for _, v := range result { if v.Date == row.Date { exist = true v.PlayerCount += row.PlayerCount v.JingliCount += row.JingliCount v.AdJingli += row.AdJingli v.DiamondJingli += row.DiamondJingli v.AdDiamond += row.AdDiamond v.RmbDiamond += row.RmbDiamond v.SpeedUpBoxDetail += row.SpeedUpBoxDetail v.IdentifyGoods += row.IdentifyGoods v.UpgradeGoods += row.UpgradeGoods v.ShowRevenue += row.ShowRevenue v.Ranking += row.Ranking v.RankingBenifit += row.RankingBenifit v.SpeedUpBox += row.SpeedUpBox v.SellGoods += row.SellGoods v.GetGoods += row.GetGoods var tempExchange Goods if row.ExchangeGoods != "" && row.ExchangeGoods != "null" { if err = json.Unmarshal([]byte(row.ExchangeGoods), &tempExchange); err != nil { logrus.Errorf("ExchangeGoods Unmarshal err :%+v", err) return } } for eventId, num := range tempExchange { if findGoods(eventId, v.Exchange) { v.Exchange[eventId] += num } else { v.Exchange = Goods{eventId: num} } } var tempSpeedUp Goods if row.SpeedUpBoxDetail != "" && row.SpeedUpBoxDetail != "null" { if err = json.Unmarshal([]byte(row.SpeedUpBoxDetail), &tempSpeedUp); err != nil { logrus.Warnf("SpeedUpBoxDetail Unmarshal err :%+v", err) return } } for eventId, num := range tempSpeedUp { if findGoods(eventId, v.SpeedUp) { v.SpeedUp[eventId] += num } else { v.SpeedUp = Goods{eventId: num} } } var tempDetail Goods if row.GetGoodsDetail != "" && row.GetGoodsDetail != "null" { if err = json.Unmarshal([]byte(row.GetGoodsDetail), &tempDetail); err != nil { logrus.Warnf("GetGoodsDetail Unmarshal err :%+v", err) return } } for eventId, num := range tempDetail { if findGoods(eventId, v.Detail) { v.Detail[eventId] += num } else { v.Detail = Goods{eventId: num} } } } } if exist == false { v := new(ReportDayGudong) v.Date = row.Date v.ChannelID = row.ChannelID v.PlayerCount += row.PlayerCount v.JingliCount += row.JingliCount v.AdJingli += row.AdJingli v.DiamondJingli += row.DiamondJingli v.AdDiamond += row.AdDiamond v.RmbDiamond += row.RmbDiamond v.SpeedUpBoxDetail += row.SpeedUpBoxDetail v.IdentifyGoods += row.IdentifyGoods v.UpgradeGoods += row.UpgradeGoods v.ShowRevenue += row.ShowRevenue v.Ranking += row.Ranking v.RankingBenifit += row.RankingBenifit v.SpeedUpBox += row.SpeedUpBox v.SellGoods += row.SellGoods v.GetGoods += row.GetGoods var tempExchange Goods if row.ExchangeGoods != "" && row.ExchangeGoods != "null" { if err = json.Unmarshal([]byte(row.ExchangeGoods), &tempExchange); err != nil { logrus.Warnf("ExchangeGoods Unmarshal err2 :%+v", err) return } } for eventId, num := range tempExchange { if findGoods(eventId, v.Exchange) { v.Exchange[eventId] += num } else { v.Exchange = Goods{eventId: num} } } var tempSpeedUp Goods if row.SpeedUpBoxDetail != "" && row.SpeedUpBoxDetail != "null" { if err = json.Unmarshal([]byte(row.SpeedUpBoxDetail), &tempSpeedUp); err != nil { logrus.Warnf("SpeedUpBoxDetail Unmarshal err :%+v", err) return } } for eventId, num := range tempSpeedUp { if findGoods(eventId, v.SpeedUp) { v.SpeedUp[eventId] += num } else { v.SpeedUp = Goods{eventId: num} } } var tempDetail Goods if row.GetGoodsDetail != "" && row.GetGoodsDetail != "null" { if err = json.Unmarshal([]byte(row.GetGoodsDetail), &tempDetail); err != nil { logrus.Warnf("GetGoodsDetail Unmarshal err :%+v", err) return } } for eventId, num := range tempDetail { if findGoods(eventId, v.Detail) { v.Detail[eventId] += num } else { v.Detail = Goods{eventId: num} } } result = append(result, v) } } distributes, err := m.Find() if err != nil { return } for _, distribute := range distributes { add(distribute) } for _, v := range result { if v != nil { resp.Days = append(resp.Days, strings.ReplaceAll(v.Date, "T00:00:00+08:00", "")) resp.Players = append(resp.Players, v.PlayerCount) resp.Jinglis = append(resp.Jinglis, v.JingliCount) resp.AdJinglis = append(resp.AdJinglis, v.AdJingli) resp.DiamondJinglis = append(resp.DiamondJinglis, v.DiamondJingli) resp.AdDiamonds = append(resp.AdDiamonds, v.AdDiamond) resp.RmbDiamonds = append(resp.RmbDiamonds, v.RmbDiamond) resp.SpeedUps = append(resp.SpeedUps, v.SpeedUpBox) // 加速宝箱 speedUpBoxDetail, err := json.Marshal(v.SpeedUp) if err != nil { logrus.Warnf("speedUpBoxDetail Marshal failed:%+v", err) return respData, err } v.SpeedUpBoxDetail = string(speedUpBoxDetail) resp.BoxDetails = append(resp.BoxDetails, v.SpeedUpBoxDetail) // 兑换 exchange, err := json.Marshal(v.Exchange) if err != nil { logrus.Warnf("Exchange Marshal failed:%+v", err) return respData, err } v.ExchangeGoods = string(exchange) resp.BoxDetails = append(resp.BoxDetails, v.ExchangeGoods) // 获取物品 detail, err := json.Marshal(v.Detail) if err != nil { logrus.Warnf("Detail Marshal failed:%+v", err) return respData, err } v.GetGoodsDetail = string(detail) resp.IdentifyGoods = append(resp.IdentifyGoods, v.IdentifyGoods) resp.UpgradeGoods = append(resp.UpgradeGoods, v.UpgradeGoods) resp.ShowRevenue = append(resp.ShowRevenue, v.ShowRevenue) resp.Ranking = append(resp.Ranking, v.Ranking) resp.RankingBenifit = append(resp.RankingBenifit, v.RankingBenifit) resp.SellGoods = append(resp.SellGoods, v.SellGoods) resp.GetGoods = append(resp.GetGoods, v.GetGoods) var players int64 = 1 if v.PlayerCount > 1 { players = v.PlayerCount } exchangeInfo := strings.ReplaceAll(v.ExchangeGoods, "Event", "") exchangeInfo = strings.ReplaceAll(exchangeInfo, `"`, "") item := GudongLogItem{ Day: strings.ReplaceAll(v.Date, "T00:00:00+08:00", ""), Players: v.PlayerCount, Jinglis: v.JingliCount, AdJinglis: v.AdJingli, DiamondJinglis: v.DiamondJingli, AdDiamonds: v.AdDiamond, RmbDiamonds: v.RmbDiamond, SpeedUps: v.SpeedUpBox, BoxDetails: v.SpeedUpBoxDetail, IdentifyGoods: v.IdentifyGoods, UpgradeGoods: v.UpgradeGoods, ShowRevenue: v.ShowRevenue, Ranking: v.Ranking, RankingBenifit: v.RankingBenifit, ExchangeGoods: exchangeInfo, SellGoods: v.SellGoods, GetGoods: v.GetGoods, } item.GetGoodsDetail = strings.ReplaceAll(v.GetGoodsDetail, "Event", "") item.GetGoodsDetail = strings.ReplaceAll(item.GetGoodsDetail, `"`, "") item.AvgJinglis = float64(v.JingliCount*100/players) / 100 item.AvgAdJinglis = float64(v.AdJingli*100/players) / 100 item.AvgDiamondJinglis = float64(v.DiamondJingli*100/players) / 100 item.AvgAdDiamonds = float64(v.AdDiamond*100/players) / 100 item.AvgRmbDiamonds = float64(v.RmbDiamond*100/players) / 100 item.AvgUpgradeGoods = float64(v.UpgradeGoods*100/players) / 100 item.AvgSpeedUps = float64(v.SpeedUpBox*100/players) / 100 item.AvgIdentifyGoods = float64(v.IdentifyGoods*100/players) / 100 item.AvgShowRevenue = float64(int64(v.ShowRevenue)*100/int64(players)) / 100 item.AvgRanking = float64(v.Ranking*100/players) / 100 item.AvgRankingBenifit = float64(v.RankingBenifit*100/players) / 100 item.AvgSellGoods = float64(v.SellGoods*100/players) / 100 item.AvgGetGoods = float64(v.GetGoods*100/players) / 100 respData.Rows = append(respData.Rows, item) } } respData.Info = resp return } var ( LoginSuccessEventId = consts.LoginEventEntryPack // 获取到entryPack // 4.22前是接收到http登录成功后 ) type CountResult struct { Count int64 } type UserIdResult struct { UserId int64 } func (s *sDash) GetNewUserCount(serverId int, begin, end int64) (count int64) { var result CountResult loginModel := &model.LoginLog{} logDB := model.TableOfYearMonth(loginModel.TableName(), time.Now())(config.DB) loginQuery := query.Use(logDB).LoginLog.Table(logDB.Statement.Table) loginQueryDo := loginQuery.Select(loginQuery.UserID.Distinct().Count().As("count")).Where(loginQuery.UserCreatedAt.Between(int32(begin), int32(end))) if serverId > 0 { loginQueryDo = loginQueryDo.Where(loginQuery.ServerID.Eq(int32(serverId))) } err := loginQueryDo.Scan(&result) if err != nil { logrus.Errorf("GetNewUserCount err: %v", err) return 0 } return result.Count } func (s *sDash) GetActiveUserCount(serverId int, ubegin, uend, begin, end int64) (count int64) { var result []UserIdResult loginModel := &model.LoginLog{} logDB := model.TableOfYearMonth(loginModel.TableName(), time.Now())(config.DB) loginQuery := query.Use(logDB).LoginLog.Table(logDB.Statement.Table) loginQueryDo := loginQuery.Select(loginQuery.UserID). Where(loginQuery.EventID.Eq(int32(LoginSuccessEventId)), loginQuery.EventAt.Between(int32(begin), int32(end))) if ubegin != 0 && uend != 0 { loginQueryDo = loginQueryDo.Where(loginQuery.UserCreatedAt.Between(int32(ubegin), int32(uend))) } if serverId != 0 { loginQueryDo = loginQueryDo.Where(loginQuery.ServerID.Eq(int32(serverId))) } err := loginQueryDo.Group(loginQuery.UserID).Having(loginQuery.ID.Sum().Gt(1)).Scan(&result) if err != nil { logrus.Errorf("GetNewUserCount err: %v", err) } //if ubegin == 0 && uend == 0 { // sql := fmt.Sprintf("select user_id from login_logs where event_id = %d and event_at >= %d and event_at <= %d", LoginSuccessEventId, begin, end) // if serverId > 0 { // sql = fmt.Sprintf("%v and server_id = %v", sql, serverId) // } // sql = sql + " group by user_id having sum(1)>=1" // tx = config.DB.Model(&model.LoginLog{}).Raw(sql).Scan(&result) //} else { // sql := fmt.Sprintf("select user_id from login_logs where user_created_at >= %d and user_created_at <= %d and event_id = %d and event_at >= %d and event_at <= %d", ubegin, uend, LoginSuccessEventId, begin, end) // if serverId > 0 { // sql = fmt.Sprintf("%v and server_id = %v", sql, serverId) // } // sql = sql + " group by user_id having sum(1)>=1" // tx = config.DB.Model(&model.LoginLog{}).Raw(sql).Scan(&result) //} //if tx.Error != nil { // logrus.Errorf("GetNewUserCount err: %v", tx.Error) //} return int64(len(result)) } func (s *sDash) GetGuideFinishedCount(serverId int, ubegin, uend, begin, end int64, channelId string) (count int64) { var result CountResult if channelId != "" { sql := fmt.Sprintf("select count(distinct(user_id)) as count from chapter_logs_0 LEFT JOIN player_channel ON chapter_logs_0.user_id = player_channel.playerid where event_at >= %d and event_at <= %d and user_created_at >= %d and user_created_at <= %d and event_id = 11 and player_channel.channel_id = '%v' ", ubegin, uend, begin, end, channelId) if serverId > 0 { sql = fmt.Sprintf("%v and server_id = %v", sql, serverId) } tx := config.DB.Model(&model.ChapterLog{}).Raw(sql).Scan(&result) if tx.Error != nil { logrus.Errorf("GetNewUserCount err: %v", tx.Error) return 0 } } else { sql := fmt.Sprintf("select count(distinct(user_id)) as count from chapter_logs_0 where event_at >= %d and event_at <= %d and user_created_at >= %d and user_created_at <= %d and event_id = 11", ubegin, uend, begin, end) if serverId > 0 { sql = fmt.Sprintf("%v and server_id = %v", sql, serverId) } tx := config.DB.Model(&model.ChapterLog{}).Raw(sql).Scan(&result) if tx.Error != nil { logrus.Errorf("GetNewUserCount2 err: %v", tx.Error) return 0 } } return result.Count } type DurationResult struct { Count int64 } func (s *sDash) GetUserOnlineDuration(serverId int, ubegin, uend, begin, end int64) int64 { var result DurationResult var tx *gorm.DB if ubegin == 0 && uend == 0 { sql := fmt.Sprintf("select sum(duration) as count from online_duration_logs where logout_at >= %d and logout_at <= %d", begin, end) if serverId > 0 { sql = fmt.Sprintf("%v and server_id = %v", sql, serverId) } tx = config.DB.Model(&model.LoginLog{}).Raw(sql).Scan(&result) } else { sql := fmt.Sprintf("select sum(duration) as count from online_duration_logs where user_created_at >= %d and user_created_at <= %d and logout_at >= %d and logout_at <= %d", ubegin, uend, begin, end) if serverId > 0 { sql = fmt.Sprintf("%v and server_id = %v", sql, serverId) } tx = config.DB.Model(&model.LoginLog{}).Raw(sql).Scan(&result) } if tx.Error != nil { logrus.Errorf("GetNewUserCount err: %v", tx.Error) } return result.Count } func (s *sDash) GetOnlineNums(serverId int) (int64, int64) { type stampResult struct { Stamp int64 Count int64 } var result stampResult tx := config.DB.Raw(fmt.Sprintf("select stamp, sum(nums) as count from online_nums_logs where server_id = %v group by stamp order by stamp desc limit 1", serverId)).Scan(&result) if tx.Error != nil { logrus.Errorf("GetOnlineNums err:%v", tx.Error) return result.Stamp, result.Count } return result.Stamp, result.Count } func (s *sDash) QueryBasicInfo(serverId int, day string) (resp []forms.BasicInfoItem, err error) { stamp, nums := s.GetOnlineNums(serverId) var onlineTips = "当前在线人数" if stamp > 0 { onlineTips = fmt.Sprintf("当前在线人数(%s)", utility.FormatSecond(time.Unix(stamp, 0))) } resp = append(resp, forms.BasicInfoItem{ ID: 0, Name: onlineTips, Count: int64(nums), }) begin, end := now.BeginningOfDay().Unix(), now.EndOfDay().Unix() newCount := s.GetNewUserCount(serverId, begin, end) newActive := s.GetActiveUserCount(serverId, 0, 0, begin, end) newActiveDuration := s.GetUserOnlineDuration(serverId, begin, end, begin, end) activeDuration := s.GetUserOnlineDuration(serverId, 0, 0, begin, end) if activeDuration < 1 { activeDuration = 1 } resp = append(resp, forms.BasicInfoItem{ID: 1, Name: "今日新增玩家", Count: int64(newCount)}) resp = append(resp, forms.BasicInfoItem{ID: 2, Name: "新增玩家时长", Count: newActiveDuration}) resp = append(resp, forms.BasicInfoItem{ID: 3, Name: "活跃玩家", Count: int64(newActive)}) resp = append(resp, forms.BasicInfoItem{ID: 4, Name: "活跃玩家时长(分钟)", Count: activeDuration / 60}) return } func (s *sDash) QueryBasicReport(ctx *gin.Context, params forms.BasicReportReq) (resp forms.BasicReportRespData, err error) { rdb := query.Use(config.DB).ReportDayBasic m := rdb. Select(rdb.ALL, rdb.OldCount.Sum().As("old_count"), rdb.NewCount.Sum().As("new_count"), rdb.ValidCount.Sum().As("valid_count"), rdb.ActiveCount.Sum().As("active_count"), rdb.NewDuration.Sum().As("new_duration"), rdb.ValidDuration.Sum().As("valid_duration"), rdb.ActiveDuration.Sum().As("active_duration"), rdb.Active1Day.Sum().As("active_1_day"), rdb.Active2Day.Sum().As("active_2_day"), rdb.Active3Day.Sum().As("active_3_day"), rdb.Active4Day.Sum().As("active_4_day"), rdb.Active5Day.Sum().As("active_5_day"), rdb.Active6Day.Sum().As("active_6_day"), rdb.Active7Day.Sum().As("active_7_day"), rdb.Active14Day.Sum().As("active_14_day"), rdb.Active30Day.Sum().As("active_30_day"), ) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } if params.Flag > -1 { m = m.Where(rdb.Flag.Eq(params.Flag)) } // 导出 if params.IsExport == 1 { results, err := m.Order(rdb.Date.Desc()).Group(rdb.Date).Find() if err != nil { return resp, err } f := excelize.NewFile() f.SetColWidth("Sheet1", "A", "A", 15) f.SetColWidth("Sheet1", "B", "B", 12) f.SetColWidth("Sheet1", "C", "C", 12) f.SetColWidth("Sheet1", "D", "D", 12) f.SetColWidth("Sheet1", "E", "E", 20) f.SetColWidth("Sheet1", "F", "F", 20) f.SetColWidth("Sheet1", "G", "G", 20) f.SetColWidth("Sheet1", "H", "H", 20) f.SetColWidth("Sheet1", "I", "I", 20) f.SetColWidth("Sheet1", "J", "J", 20) f.SetColWidth("Sheet1", "K", "K", 20) f.SetColWidth("Sheet1", "L", "L", 20) f.SetColWidth("Sheet1", "M", "M", 12) f.SetColWidth("Sheet1", "N", "N", 12) f.SetColWidth("Sheet1", "O", "O", 12) f.SetColWidth("Sheet1", "P", "P", 12) f.SetColWidth("Sheet1", "Q", "Q", 12) f.SetColWidth("Sheet1", "R", "R", 12) f.SetColWidth("Sheet1", "S", "S", 12) f.SetColWidth("Sheet1", "T", "T", 12) f.SetColWidth("Sheet1", "U", "U", 12) // 创建一个工作表 f.SetCellValue("Sheet1", "A1", "日期") f.SetCellValue("Sheet1", "B1", "新增") f.SetCellValue("Sheet1", "C1", "有效新增") f.SetCellValue("Sheet1", "D1", "活跃") f.SetCellValue("Sheet1", "E1", "新增时长(分钟) ") f.SetCellValue("Sheet1", "F1", "人均新增时长(分钟)") f.SetCellValue("Sheet1", "G1", "有效新增时长(分钟)") f.SetCellValue("Sheet1", "H1", "人均有效新增时长(分钟)") f.SetCellValue("Sheet1", "I1", "老玩家时长(分钟)") f.SetCellValue("Sheet1", "J1", "人均老玩家时长(分钟)") f.SetCellValue("Sheet1", "K1", "总时长(分钟) ") f.SetCellValue("Sheet1", "L1", "人均总时长(分钟) ") f.SetCellValue("Sheet1", "M1", "次留率") f.SetCellValue("Sheet1", "N1", "2日留") f.SetCellValue("Sheet1", "O1", "3日留") f.SetCellValue("Sheet1", "P1", "4日留") f.SetCellValue("Sheet1", "Q1", "5日留") f.SetCellValue("Sheet1", "R1", "6日留") f.SetCellValue("Sheet1", "S1", "7日留") f.SetCellValue("Sheet1", "T1", "14日留") f.SetCellValue("Sheet1", "U1", "30日留") for i, result := range results { date, _ := now.ParseInLocation(time.Local, result.Date) // 日期 f.SetCellValue("Sheet1", fmt.Sprintf("A%d", i+2), date.Format("2006-01-02")) // 新增 f.SetCellValue("Sheet1", fmt.Sprintf("B%d", i+2), result.NewCount) // 有效新增 f.SetCellValue("Sheet1", fmt.Sprintf("C%d", i+2), result.ValidCount) // 活跃 f.SetCellValue("Sheet1", fmt.Sprintf("D%d", i+2), result.ActiveCount) // 新增时长(分钟) f.SetCellValue("Sheet1", fmt.Sprintf("E%d", i+2), result.NewDuration/60) // 人均新增时长(分钟) if result.NewCount == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("F%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("F%d", i+2), utility.Round(float64(result.NewDuration)/float64(result.NewCount)/60)) } // 有效新增时长(分钟) f.SetCellValue("Sheet1", fmt.Sprintf("G%d", i+2), result.ValidDuration/60) // 人均有效新增时长(分钟) if result.ValidCount == 0 { f.SetCellValue("Sheet1", fmt.Sprintf("H%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("H%d", i+2), utility.Round(float64(result.ValidDuration)/float64(result.ValidCount)/60)) } // 老玩家时长(分钟) f.SetCellValue("Sheet1", fmt.Sprintf("I%d", i+2), (result.ActiveDuration-result.NewDuration)/60) // 人均老玩家时长(分钟) if result.ActiveDuration < 1 || result.NewDuration < 1 || result.ActiveCount-result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("J%d", i+2), "0") } else { logrus.Warnf("result:%+v", result) f.SetCellValue("Sheet1", fmt.Sprintf("J%d", i+2), utility.Round(float64(result.ActiveDuration-result.NewDuration)/float64(result.ActiveCount-result.NewCount)/60)) } // 总时长(分钟) f.SetCellValue("Sheet1", fmt.Sprintf("K%d", i+2), result.ActiveDuration/60) // 人均总时长(分钟) if result.ActiveDuration < 1 || result.ActiveCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("L%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("L%d", i+2), utility.Round(float64(result.ActiveDuration)/float64(result.ActiveCount)/60)) } // 次留率 if result.Active1Day < 1 || result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("M%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("M%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.Active1Day)/float64(result.NewCount)*100), "%")) } // 2日留 if result.Active2Day < 1 || result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("N%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("N%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.Active2Day)/float64(result.NewCount)*100), "%")) } // 3日留 if result.Active3Day < 1 || result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("O%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("O%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.Active3Day)/float64(result.NewCount)*100), "%")) } // 4日留 if result.Active4Day < 1 || result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("P%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("P%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.Active4Day)/float64(result.NewCount)*100), "%")) } // 5日留 if result.Active5Day < 1 || result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("Q%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("Q%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.Active5Day)/float64(result.NewCount)*100), "%")) } // 6日留 if result.Active6Day < 1 || result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("R%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("R%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.Active6Day)/float64(result.NewCount)*100), "%")) } // 7日留 if result.Active7Day < 1 || result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("S%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("S%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.Active7Day)/float64(result.NewCount)*100), "%")) } // 14日留 if result.Active14Day < 1 || result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("T%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("T%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.Active14Day)/float64(result.NewCount)*100), "%")) } // 30日留 if result.Active30Day < 1 || result.NewCount < 1 { f.SetCellValue("Sheet1", fmt.Sprintf("U%d", i+2), "0") } else { f.SetCellValue("Sheet1", fmt.Sprintf("U%d", i+2), fmt.Sprintf("%v%s", utility.Round(float64(result.Active30Day)/float64(result.NewCount)*100), "%")) } } // 设置工作簿的默认工作表 f.SetActiveSheet(1) ctx.Header("Content-Type", "application/vnd.ms-excel") ctx.Header("Content-Disposition", fmt.Sprintf("attachment;filename=日统计导出%s.xlsx", time.Now().Format("20060102150405"))) f.WriteTo(ctx.Writer) return resp, err } results, err := m.Order(rdb.Date.Desc()). Group(rdb.Date). Limit(int(params.PerPage)). Offset(int((params.Page - 1) * params.PerPage)). Find() if err != nil { return resp, err } for _, result := range results { resp.Data = append(resp.Data, forms.BasicReportItem{ ID: result.ID, Date: utility.ParseDate(result.Date), OldCount: result.OldCount, NewCount: result.NewCount, ValidCount: result.ValidCount, ActiveCount: result.ActiveCount, NewDuration: result.NewDuration, ValidDuration: result.ValidDuration, ActiveDuration: result.ActiveDuration, Day1Active: result.Active1Day, Day2Active: result.Active2Day, Day3Active: result.Active3Day, Day4Active: result.Active4Day, Day5Active: result.Active5Day, Day6Active: result.Active6Day, Day7Active: result.Active7Day, Day14Active: result.Active14Day, Day30Active: result.Active30Day, }) } resp.Total, err = rdb.Count() return } type QueryBasicRetentionInfo struct { Days []string `json:"days"` NewCount []int64 `json:"newCount"` OldCount []int64 `json:"old_count"` Active1Day []int64 `json:"Active1Day"` Active7Day []int64 `json:"Active7Day"` Active14Day []int64 `json:"Active14Day"` Active30Day []int64 `json:"Active30Day"` AvgActive1Day []string `json:"AvgActive1Day"` AvgActive7Day []string `json:"AvgActive7Day"` AvgActive14Day []string `json:"AvgActive14Day"` AvgActive30Day []string `json:"AvgActive30Day"` } type QueryBasicRetentionData struct { Info QueryBasicRetentionInfo `json:"info"` } // QueryBasicRetention 查询基础留存率 func (s *sDash) QueryBasicRetention(params forms.QueryBasicRetentionReq) (respData QueryBasicRetentionData, err error) { resp := QueryBasicRetentionInfo{} rdb := query.Use(config.DB).ReportDayBasic m := rdb. Select(rdb.ALL, rdb.NewCount.Sum().As("new_count"), rdb.ValidCount.Sum().As("valid_count"), rdb.ActiveCount.Sum().As("active_count"), rdb.NewDuration.Sum().As("new_duration"), rdb.ValidDuration.Sum().As("valid_duration"), rdb.OldCount.Sum().As("old_count"), rdb.ActiveDuration.Sum().As("active_duration"), rdb.Active1Day.Sum().As("active_1_day"), rdb.Active2Day.Sum().As("active_2_day"), rdb.Active3Day.Sum().As("active_3_day"), rdb.Active4Day.Sum().As("active_4_day"), rdb.Active5Day.Sum().As("active_5_day"), rdb.Active6Day.Sum().As("active_6_day"), rdb.Active7Day.Sum().As("active_7_day"), rdb.Active14Day.Sum().As("active_14_day"), rdb.Active30Day.Sum().As("active_30_day"), ).Where(rdb.Date.Gte(params.Day), rdb.Date.Lt(params.EndDay)) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } result, err := m.Order(rdb.Date).Group(rdb.Date).Find() // .Order(rdb.Date.Desc()) if err != nil { return } for _, v := range result { if v != nil { resp.Days = append(resp.Days, strings.ReplaceAll(v.Date, "T00:00:00+08:00", "")) resp.OldCount = append(resp.OldCount, v.OldCount) resp.NewCount = append(resp.NewCount, v.NewCount) resp.Active1Day = append(resp.Active1Day, v.Active1Day) resp.Active7Day = append(resp.Active7Day, v.Active7Day) resp.Active14Day = append(resp.Active14Day, v.Active14Day) resp.Active30Day = append(resp.Active30Day, v.Active30Day) var newCount int64 = 1 if v.NewCount > 1 { newCount = v.NewCount } else { resp.AvgActive1Day = append(resp.AvgActive1Day, "0") resp.AvgActive7Day = append(resp.AvgActive7Day, "0") resp.AvgActive14Day = append(resp.AvgActive14Day, "0") resp.AvgActive30Day = append(resp.AvgActive30Day, "0") continue } resp.AvgActive1Day = append(resp.AvgActive1Day, utility.Round((float64(v.Active1Day))*100/float64(newCount), int64(1))) resp.AvgActive7Day = append(resp.AvgActive7Day, utility.Round((float64(v.Active7Day))*100/float64(newCount), int64(1))) resp.AvgActive14Day = append(resp.AvgActive14Day, utility.Round((float64(v.Active14Day))*100/float64(newCount), int64(1))) resp.AvgActive30Day = append(resp.AvgActive30Day, utility.Round((float64(v.Active30Day))*100/float64(newCount), int64(1))) } } respData.Info = resp return } func (s *sDash) QueryConditionReport(params forms.ConditionReportReq) (resp forms.ConditionReportRespData, err error) { rdb := query.Use(config.DB).ReportDayEventBasic m := rdb. Select(rdb.ALL, rdb.Count_.Sum().As("count"), rdb.Active1Day.Sum().As("active_1_day"), rdb.Active2Day.Sum().As("active_2_day"), rdb.Active3Day.Sum().As("active_3_day"), rdb.Active4Day.Sum().As("active_4_day"), rdb.Active5Day.Sum().As("active_5_day"), rdb.Active6Day.Sum().As("active_6_day"), rdb.Active7Day.Sum().As("active_7_day"), rdb.Active14Day.Sum().As("active_14_day"), rdb.Active30Day.Sum().As("active_30_day"), ) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } if params.Flag > -1 { m = m.Where(rdb.Flag.Eq(params.Flag)) } resp.Total, err = m.Order(rdb.Date.Desc()).Group(rdb.Date).Count() results, err := m.Limit(int(params.PerPage)).Offset(int((params.Page - 1) * params.PerPage)).Find() if err != nil { return resp, err } for _, result := range results { date := utility.ParseDate(result.Date) begin, end, err := utility.GetBeginAndEndOfDay(date) if err != nil { return resp, err } lcount := s.GetGuideFinishedCount(params.ServerId, begin, end, begin, end, params.ChannelId) resp.Data = append(resp.Data, forms.ConditionReportItem{ ID: result.ID, ConditionId: result.ConditionID, Name: result.Name, Date: date, GuideFinishedCount: lcount, NewCount: result.Count_, Day1Active: result.Active1Day, Day2Active: result.Active2Day, Day3Active: result.Active3Day, Day4Active: result.Active4Day, Day5Active: result.Active5Day, Day6Active: result.Active6Day, Day7Active: result.Active7Day, Day14Active: result.Active14Day, Day30Active: result.Active30Day, }) } return } func (s *sDash) GetEventUserCount(uBegin, uEnd, evBegin, evEnd int64, chapter, roomId, event, difficulty int64, extra string) (int64, error) { var results []resultUserId sqlStr := "select a.user_id from chapter_logs a where a.user_created_at >= %d and a.user_created_at <=%d and a.chapter_id = %d and a.room_id = %d and a.event_id = %d " if extra != "" { sqlStr = sqlStr + " and a.extra='%s' " } sqlStr += " and a.event_at >= %d and a.event_at <=%d and a.difficulty = %d group by a.user_id" var tx *gorm.DB if extra != "" { tx = config.DB.Raw(fmt.Sprintf(sqlStr, uBegin, uEnd, chapter, roomId, event, extra, evBegin, evEnd, difficulty)). Scan(&results) } else { tx = config.DB.Raw(fmt.Sprintf(sqlStr, uBegin, uEnd, chapter, roomId, event, evBegin, evEnd, difficulty)). Scan(&results) } if tx.Error != nil { return 0, tx.Error } return int64(len(results)), nil } type resultUserId struct { UserId int64 } // 注册当天满足某个事件的用户,然后在某个日期内的活动数量 func (s *sDash) GetEventUserOnlineCount(uBengin, uEnd, evBegin, evEnd, begin, end int64, chapter, roomId, event int64, extra string) (int64, error) { var results []resultUserId sqlStr := "select a.user_id from chapter_logs_%d a left join online_duration_logs b on a.user_id = b.user_id where a.user_created_at >= %d and a.user_created_at <=%d and a.chapter_id = %d and a.room_id = %d and a.event_id = %d " if extra != "" { sqlStr = sqlStr + " and a.extra='%s' " } sqlStr += " and a.event_at >= %d and a.event_at <=%d and b.logout_at >= %d and b.logout_at <= %d group by a.user_id having max(b.id) > 0 " var tx *gorm.DB if extra != "" { tx = config.DB.Raw(fmt.Sprintf(sqlStr, chapter, uBengin, uEnd, chapter, roomId, event, extra, evBegin, evEnd, begin, end)). Scan(&results) } else { tx = config.DB.Raw(fmt.Sprintf(sqlStr, chapter, uBengin, uEnd, chapter, roomId, event, evBegin, evEnd, begin, end)). Scan(&results) } if tx.Error != nil { return 0, tx.Error } return int64(len(results)), nil } func (s *sDash) QueryChapterPassLog(params forms.GoodsReportReq) (resp interface{}, err error) { var ( rdb = query.Use(config.DB).ReportDayChapterPass m = rdb.Where(rdb.Date.Between(params.Day, params.EndDay)) ) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } if params.Flag > -1 { m = m.Where(rdb.Flag.Eq(int32(params.Flag))) } result, err := m.Find() if err != nil { return } type ChapterItem struct { ID int64 `json:"id"` Index int64 `json:"index"` Diff int64 `json:"diff"` Nums int64 `json:"nums"` Users int64 `json:"users"` NumsNew int64 `json:"numsNew"` PassNums int64 `json:"passNums"` UsersNew int64 `json:"usersNew"` PassUsers int64 `json:"passUsers"` PassNumsNew int64 `json:"passNumsNew"` PassUsersNew int64 `json:"passUsersNew"` SettleNums int64 `json:"settleNums"` SettleUsers int64 `json:"settleUsers"` SettleNumsNew int64 `json:"settleNumsNew"` SettleUsersNew int64 `json:"settleUsersNew"` } if result == nil || len(result) == 0 { resp = nil return } var allData []ChapterItem for i := 0; i < len(result); i++ { var chapterItems []ChapterItem if err = json.Unmarshal([]byte(result[i].Data), &chapterItems); err != nil { logrus.Infof("result json.Unmarshal err:%+v", err) return } for c := 0; c < len(chapterItems); c++ { chapterItems[c].Index = (chapterItems[c].ID * 1000) + chapterItems[c].Diff allData = append(allData, chapterItems[c]) } } transform := make(map[int64]ChapterItem, 0) for i := 0; i < len(allData); i++ { _, ok := transform[allData[i].Index] if ok { transform[allData[i].Index] = ChapterItem{ ID: allData[i].ID, Diff: allData[i].Diff, Index: allData[i].Index, Nums: allData[i].Nums + transform[allData[i].Index].Nums, Users: allData[i].Users + transform[allData[i].Index].Users, NumsNew: allData[i].NumsNew + transform[allData[i].Index].NumsNew, PassNums: allData[i].PassNums + transform[allData[i].Index].PassNums, UsersNew: allData[i].UsersNew + transform[allData[i].Index].UsersNew, PassUsers: allData[i].PassUsers + transform[allData[i].Index].PassUsers, PassNumsNew: allData[i].PassNumsNew + transform[allData[i].Index].PassNumsNew, PassUsersNew: allData[i].PassUsersNew + transform[allData[i].Index].PassUsersNew, SettleNums: allData[i].SettleNums + transform[allData[i].Index].SettleNums, SettleUsers: allData[i].SettleUsers + transform[allData[i].Index].SettleUsers, SettleNumsNew: allData[i].SettleNumsNew + transform[allData[i].Index].SettleNumsNew, SettleUsersNew: allData[i].SettleUsersNew + transform[allData[i].Index].SettleUsersNew, } } else { transform[allData[i].Index] = allData[i] } } passT := make([]ChapterItem, 0) for k, _ := range transform { passT = append(passT, transform[k]) } sort.Slice(passT, func(i, j int) bool { return passT[i].Index < passT[j].Index }) resp = passT return } type RoleMapInfo struct { Days []string `json:"days"` Info map[string][]int64 `json:"info"` } func (s *sDash) QueryUserRolesMap(params forms.GoodsReportReq) (RoleMapInfo, error) { resp := RoleMapInfo{Info: map[string][]int64{ "player1": {}, "player2": {}, "player3": {}, "player4": {}, "player5": {}, "player6": {}, "player7": {}, "player8": {}, "player9": {}, "player10": {}, "player11": {}, "player12": {}, "player13": {}, "player14": {}, }} rdb := query.Use(config.DB).ReportDayHaveRole m := rdb.Select(rdb.Date).Where(rdb.Date.Gte(params.Day), rdb.Date.Lt(params.EndDay)) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } result, err := m.Group(rdb.Date).Order(rdb.Date).Find() if err != nil { logrus.Warnf("QueryUserRolesMap Group err:%+v", err) return resp, err } for _, v := range result { date := strings.ReplaceAll(v.Date, "T00:00:00+08:00", "") resp.Days = append(resp.Days, date) m2 := rdb.Select(rdb.Data).Where(rdb.Date.Eq(date)) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m2 = m2.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m2 = m2.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m2 = m2.Where(rdb.ServerID.Eq(int32(params.ServerId))) } var tmpRoles = make(map[int64]int64) rows, err := m2.Find() if err != nil { logrus.Warnf("QueryUserRolesMap Data err:%+v", err) return resp, err } for _, row := range rows { var tmpRole = make(map[int64]int64) if err = json.Unmarshal([]byte(row.Data), &tmpRole); err != nil { logrus.Warnf("QueryUserRolesMap Unmarshal err:%+v", err) return resp, err } for roleId, num := range tmpRole { tmpRoles[roleId] += num } } for roleId, num := range tmpRoles { switch roleId { case 1: resp.Info["player1"] = append(resp.Info["player1"], num) case 2: resp.Info["player2"] = append(resp.Info["player2"], num) case 3: resp.Info["player3"] = append(resp.Info["player3"], num) case 4: resp.Info["player4"] = append(resp.Info["player4"], num) case 5: resp.Info["player5"] = append(resp.Info["player5"], num) case 6: resp.Info["player6"] = append(resp.Info["player6"], num) case 7: resp.Info["player7"] = append(resp.Info["player7"], num) case 8: resp.Info["player8"] = append(resp.Info["player8"], num) case 9: resp.Info["player9"] = append(resp.Info["player9"], num) case 10: resp.Info["player10"] = append(resp.Info["player10"], num) case 11: resp.Info["player11"] = append(resp.Info["player11"], num) case 12: resp.Info["player12"] = append(resp.Info["player12"], num) case 13: resp.Info["player13"] = append(resp.Info["player13"], num) case 14: resp.Info["player14"] = append(resp.Info["player14"], num) } } } return resp, err } var ( deadEvent = 6 ) type Result struct { EventId int64 `json:"event_id"` Cost float64 `json:"cost"` Count int64 `json:"count"` Times int64 `json:"times"` } type Result2 struct { RoomId int64 `json:"room_id"` Val int64 `json:"val"` } type Result3 struct { ChapterId int64 UserCount int64 DieNums int64 Difficulty int64 } func (s *sDash) QueryLoginLog(params forms.LoginlogReq) (resp forms.LoginEventRespData, err error) { var ( loginModel = &model.LoginLog{} q = config.DB.Scopes(model.TableOfYearMonth(loginModel.TableName(), time.Now())). Select("`user_id`,`event_id`,AVG(`cost_time`) AS `cost`,COUNT(distinct(`user_id`)) as count, count(1) as times") results []Result data []forms.LoginEventData ) begin, end, err := utility.GetBeginAndEndOfDay(params.Day) if err != nil { return resp, err } q = q.Where("event_at <= ? and event_at >=? ", end, begin) if params.EventId != 0 { q = q.Where("event_id = ?", params.EventId) } if params.UserType == 1 { q = q.Where("user_created_at >= ? and user_created_at <=?", begin, end) } else if params.UserType == 2 { q = q.Where("user_created_at <=?", begin) } if params.ServerId > 0 { q = q.Where("server_id =?", params.ServerId) } switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 q = q.Where("channel_id in(?)", Channel.GetIdsByType(params.ChannelId)) default: // 指定渠道 q = q.Where("channel_id =?", params.ChannelId) } tx := q.Group("event_id").Scan(&results) if tx.Error != nil { return resp, tx.Error } for i, result := range results { data = append(data, forms.LoginEventData{ Key: int64(i), EventId: result.EventId, EventName: s.GetLoginEventName(result.EventId), UserCount: result.Count, Count: result.Times, Cost: int64(result.Cost), }) } lcount := s.GetGuideFinishedCount(params.ServerId, begin, end, begin, end, params.ChannelId) if lcount > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 100, EventName: "引导关卡完成(新手)", UserCount: lcount, Count: lcount, Cost: 0, }) } n, f := s.getNewConvCount(params.ServerId, params.Day, params.ChannelId) if f.Goto > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 101, EventName: "到达新手武器界面", UserCount: f.Goto, Count: f.Goto, Cost: 0, }) } if n.Wear1Count > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 102, EventName: "引导装备佩戴(1)", UserCount: n.Wear1Users, Count: n.Wear1Count, Cost: 0, }) } if n.Wear2Count > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 103, EventName: "引导装备佩戴(2)", UserCount: n.Wear2Users, Count: n.Wear2Count, Cost: 0, }) } if n.Wear3Count > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 104, EventName: "引导装备佩戴(3)", UserCount: n.Wear3Users, Count: n.Wear3Count, Cost: 0, }) } if n.FirstChapterCount > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 105, EventName: "强制进入第一关卡", UserCount: n.FirstChapterUsers, Count: n.FirstChapterCount, Cost: 0, }) } if n.Intensify1Count > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 106, EventName: "引导装备强化(1)", UserCount: n.Intensify1Users, Count: n.Intensify1Count, Cost: 0, }) } if n.Intensify2Count > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 107, EventName: "引导装备强化(2)", UserCount: n.Intensify2Users, Count: n.Intensify2Count, Cost: 0, }) } if n.Intensify3Count > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 108, EventName: "引导装备强化(3)", UserCount: n.Intensify3Users, Count: n.Intensify3Count, Cost: 0, }) } if n.Intensify4Count > 0 { data = append(data, forms.LoginEventData{ Key: int64(len(data)), EventId: 109, EventName: "引导装备强化(4)", UserCount: n.Intensify4Users, Count: n.Intensify4Count, Cost: 0, }) } resp.Data = data return } func (s *sDash) getNewConvCount(serverId int, day, channelId string) (newConv model.ReportDayNewConv, firstAdv model.ReportDayFirstAdv) { n := config.DB.Table("report_day_new_conv").Select( "sum(wear1_users) as wear1_users", "sum(wear2_users) as wear2_users", "sum(wear3_users) as wear3_users", "sum(first_chapter_users) as first_chapter_users", "sum(intensify1_users) as intensify1_users", "sum(intensify2_users) as intensify2_users", "sum(intensify3_users) as intensify3_users", "sum(intensify4_users) as intensify4_users", "sum(wear1_count) as wear1_count", "sum(wear2_count) as wear2_count", "sum(wear3_count) as wear3_count", "sum(first_chapter_count) as first_chapter_count", "sum(intensify1_count) as intensify1_count", "sum(intensify2_count) as intensify2_count", "sum(intensify3_count) as intensify3_count", "sum(intensify4_count) as intensify4_count", ) f := config.DB.Table("report_day_first_adv").Select( "sum(goto) as goto", ) if serverId > 0 { n = n.Where("server_id = %v", serverId) f = f.Where("server_id = %v", serverId) } switch channelId { case "": n.Where("date = ?", day).Scan(&newConv) f.Where("date = ?", day).Scan(&firstAdv) case "1": // 所有的广告渠道 n.Where("date = ? and channel_id != ?", day, "0").Scan(&newConv) f.Where("date = ? and channel_id != ?", day, "0").Scan(&firstAdv) default: // 指定渠道 n.Where("date = ? and channel_id = ?", day, channelId).Scan(&newConv) f.Where("date = ? and channel_id = ?", day, channelId).Scan(&firstAdv) } return } func (s *sDash) QueryUserChapterLog(serverId int, day string, days, chapter_id, difficulty int64, channelId string, flag int32) (resp []forms.UserChapterItem, err error) { begin, end, err := utility.GetBeginAndEndOfDay(day) if err != nil { return resp, err } userBegin := begin - int64(days)*24*60*60 userEnd := end - int64(days)*24*60*60 // 获取进入房间信息 var results []Result2 clog := model.ChapterLog{ChapterID: int32(chapter_id)} query := config.DB.Scopes(model.ChapterLogTable(clog)).Select("room_id, count(distinct(user_id)) as val").Where("chapter_id = ? and difficulty= ? and room_id > 0", chapter_id, difficulty) if days >= 7 { // 超过7天,就是7日后 query = query.Where("user_created_at <= ? and event_at >= ?", userEnd, begin) } else { query = query.Where("user_created_at >= ? and user_created_at <= ? and event_at >=? and event_at <= ?", userBegin, userEnd, begin, end) } switch channelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: query = query.Where("`channel_id` IN (?)", Channel.GetIdsByType(channelId)) default: // 指定渠道 query = query.Where("`channel_id` = ? ", channelId) } if serverId > 0 { query = query.Where(fmt.Sprintf("server_id = %v", serverId)) } if flag > -1 { query = query.Where(fmt.Sprintf("flag = %v", flag)) } tx := query.Group("room_id").Order("room_id asc").Scan(&results) if tx.Error != nil { return resp, tx.Error } // 获取结算房间信息 var results2 []Result2 query2 := config.DB.Scopes(model.ChapterLogTable(clog)).Select("room_id, count(distinct(user_id)) as val").Where("chapter_id = ? and difficulty= ? and room_id >0 and event_id =11", chapter_id, difficulty) if days >= 7 { // 超过7天,就是7日后 query2 = query2.Where("user_created_at <= ? and event_at >= ?", userEnd, begin) } else { query2 = query2.Where("user_created_at >= ? and user_created_at <= ? and event_at >=? and event_at <= ?", userBegin, userEnd, begin, end) } switch channelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 query2 = query2.Where("`channel_id` IN (?)", Channel.GetIdsByType(channelId)) default: // 指定渠道 query2 = query2.Where("`channel_id` = ? ", channelId) } if serverId > 0 { query2 = query2.Where(fmt.Sprintf("server_id = %v", serverId)) } if flag > -1 { query2 = query2.Where(fmt.Sprintf("flag = %v", flag)) } tx2 := query2.Group("room_id").Order("room_id asc").Scan(&results2) if tx2.Error != nil { return resp, tx.Error } if len(results) == 0 && len(results2) == 0 { return } else if len(results) == 0 { for i, item := range results2 { resp = append(resp, forms.UserChapterItem{Id: i, RoomId: int32(item.RoomId), SettelCount: int32(item.Val)}) } return } else if len(results2) == 0 { for i, item := range results { resp = append(resp, forms.UserChapterItem{Id: i, RoomId: int32(item.RoomId), Count: int32(item.Val)}) } return } else { for i, j := 0, 0; i < len(results) && j < len(results2); { k := i + 1 + j + 1 tmpItem := forms.UserChapterItem{Id: k} if results[i].RoomId < results2[j].RoomId { tmpItem.RoomId = int32(results[i].RoomId) tmpItem.Count = int32(results[i].Val) i++ } else if results[i].RoomId > results2[j].RoomId { tmpItem.RoomId = int32(results2[j].RoomId) tmpItem.Count = int32(results2[j].Val) j++ } else { tmpItem.RoomId = int32(results2[j].RoomId) tmpItem.Count = int32(results[i].Val) tmpItem.SettelCount = int32(results2[j].Val) i++ j++ } resp = append(resp, tmpItem) } } return } // queryUserDieLog 查询每天的用户死亡信息 func (s *sDash) queryUserDieLog(serverId int, day, endDay string, chapterId, difficulty int, channelId string, flag int32) (nums, users, newNums, newUsers int, err error) { begin, end, err := utility.GetBeginAndEndOfDay2(day, endDay) if err != nil { return 0, 0, 0, 0, err } var results []Result3 clog := model.ChapterLog{ChapterID: int32(chapterId)} query := config.DB.Scopes(model.ChapterLogTable(clog)). Select("chapter_id,difficulty, count(distinct(user_id)) as user_count, count(1) as die_nums"). Where("event_at >= ? and event_at <= ? and event_id = ? and difficulty = ?", begin, end, deadEvent, difficulty) switch channelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 query = query.Where("`channel_id` IN (?)", Channel.GetIdsByType(channelId)) default: // 指定渠道 query = query.Where("channel_id = ? ", channelId) } if serverId > 0 { query = query.Where(fmt.Sprintf("server_id = %v", serverId)) } if flag > -1 { query = query.Where(fmt.Sprintf("flag = %v", flag)) } tx := query.Group("chapter_id,difficulty").Scan(&results) if tx.Error != nil { return 0, 0, 0, 0, tx.Error } if len(results) > 0 { newNums, newUsers = s.newUserDieLog(serverId, day, endDay, chapterId, difficulty, clog, channelId, flag) return int(results[0].DieNums), int(results[0].UserCount), newNums, newUsers, nil } return 0, 0, 0, 0, nil } func (s *sDash) newUserDieLog(serverId int, day, endDay string, chapterId, difficulty int, clog model.ChapterLog, channelId string, flag int32) (newNums, newUsers int) { begin, end, _ := utility.GetBeginAndEndOfDay2(day, endDay) end = end + 1 // 同一天 if begin+86400 == end { var results4 []Result3 query2 := config.DB.Scopes(model.ChapterLogTable(clog)). Select("chapter_id,difficulty, count(distinct(user_id)) as user_count, count(1) as die_nums"). Where("event_at >= ? and event_at <= ? and event_id = ? and difficulty = ? and user_created_at >= ? and user_created_at <= ? ", begin, end, deadEvent, difficulty, begin, end) if channelId != "" { query2 = query2.Where(fmt.Sprintf("`user_id` IN (SELECT `player_channel`.`playerid` FROM `player_channel` WHERE `player_channel`.`channel_id` = '%v')", channelId)) } if serverId > 0 { query2 = query2.Where(fmt.Sprintf("server_id = %v", serverId)) } if flag > -1 { query2 = query2.Where(fmt.Sprintf("flag = %v", flag)) } tx2 := query2.Group("chapter_id,difficulty").Scan(&results4) if tx2.Error != nil { return 0, 0 } if len(results4) == 0 { return 0, 0 } logrus.Warnf("results4:%+v", results4) return int(results4[0].DieNums), int(results4[0].UserCount) } var ( dieNums int64 userCount int64 ) for i := begin; i < end; i += 86400 { var results5 []Result3 query2 := config.DB.Scopes(model.ChapterLogTable(clog)). Select("chapter_id,difficulty, count(distinct(user_id)) as user_count, count(1) as die_nums"). Where("event_at >= ? and event_at <= ? and event_id = ? and difficulty = ? and user_created_at >= ? and user_created_at <= ? ", i, i+86400, deadEvent, difficulty, i, i+86400) if channelId != "" { query2 = query2.Where(fmt.Sprintf("`user_id` IN (SELECT `player_channel`.`playerid` FROM `player_channel` WHERE `player_channel`.`channel_id` = '%v')", channelId)) } if serverId > 0 { query2 = query2.Where(fmt.Sprintf("server_id = %v", serverId)) } if flag > -1 { query2 = query2.Where(fmt.Sprintf("flag = %v", flag)) } tx2 := query2.Group("chapter_id,difficulty").Scan(&results5) if tx2.Error != nil { return 0, 0 } if len(results5) > 0 { dieNums += results5[0].DieNums userCount += results5[0].UserCount } } return int(dieNums), int(userCount) } func (s *sDash) QueryUserDieLog(params forms.DieDataReq) (resp serializer.Response) { chapterMap := gmdata.GetChaptersMap() list := make([]forms.DieInfoItem, 0) for _, v := range chapterMap { dies, users, newDies, newUsers, err := s.queryUserDieLog(params.ServerId, params.Day, params.EndDay, int(v.ID), int(v.Difficulty), params.ChannelId, params.Flag) if err == nil { list = append(list, forms.DieInfoItem{ Id: len(list), ChapterId: int(v.ID), Chapter: s.GetChapterName(v.ID, v.Difficulty), DifficultyIndex: int(v.Difficulty), Difficulty: gmdata.GetDifficultName(v.Difficulty), DieCount: dies, DieUserCount: users, NewDieCount: newDies, NewDieUserCount: newUsers, }) } } for k, v := range list { list[k].Index = v.ChapterId*1000 + v.DifficultyIndex } sort.Sort(forms.DieInfoItemSlice(list)) pageCount := int64(len(list)) models := new(forms.ChapterReconnectRespData) models.Page = params.Page models.PerPage = params.PerPage models.PageCount = int64(math.Ceil(float64(pageCount) / float64(params.PerPage))) startInx := (models.Page - 1) * models.PerPage endInx := (models.Page) * models.PerPage if startInx < 0 { startInx = 0 } if endInx > pageCount { endInx = pageCount } subList := list[startInx:endInx] models.List = subList return serializer.Suc(models) } func (s *sDash) QueryConditionUsers(params forms.DieDataReq) (interface{}, error) { var ( conditionRow = make(map[int64]*model.ReportDayEventBasic) rdb = query.Use(config.DB).ReportDayEventBasic m = rdb.WithContext(context.TODO()).Select(rdb.ALL, rdb.Count_.Sum().As("count"), rdb.Active1Day.Sum().As("active_1_day"), rdb.Active2Day.Sum().As("active_2_day"), rdb.Active3Day.Sum().As("active_3_day"), rdb.Active4Day.Sum().As("active_4_day"), rdb.Active5Day.Sum().As("active_5_day"), rdb.Active6Day.Sum().As("active_6_day"), rdb.Active7Day.Sum().As("active_7_day"), rdb.Active14Day.Sum().As("active_14_day"), rdb.Active30Day.Sum().As("active_30_day"), ) ) switch params.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(params.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(params.ChannelId)) } if params.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(params.ServerId))) } res2, _ := m. Where(rdb.Date.Eq(params.Day)). Order(rdb.Date.Desc()). Group(rdb.Date).Find() for _, item := range res2 { conditionRow[item.ConditionID] = item } db3 := query.Use(config.DB).ReportEvent res, err := db3.Find() if err != nil { return nil, err } var resp []*forms.ConditionItem for _, item := range res { temp := forms.ConditionItem{ Id: int32(item.ID), ConditionId: int32(item.ID), Name: item.EventName, } if v, ok := conditionRow[item.ID]; ok { temp.Val = int(v.Count_) } resp = append(resp, &temp) } return resp, err } func (s *sDash) GetLoginEventName(eventId int64) string { for _, v := range config.LoginEvent { if int64(v.ID) == eventId { return v.Name } } return "" } func (s *sDash) wrapperChaperName(id, diff int64) string { c1 := "活动章节" if id == 0 { c1 = "新手引导章节" } else if id < 100 { c1 = "剧情章节" } return fmt.Sprintf("%s%d %s", c1, id, gmdata.GetDifficultName(diff)) } func (s *sDash) GetChapterName(id, diff int64) string { data := gmdata.GetChapterById(int32(id)) if data == nil || strings.Contains(data.Name, "未知") { return s.wrapperChaperName(id, diff) } return fmt.Sprintf("%s %s", data.Name, gmdata.GetDifficultName(diff)) } // QueryRolesLog // 角色Id/1亿 = 角色类型 角色后四位(不包含后四位的最后一位)= 角色等级 func (s *sDash) QueryRolesLog(queryDate forms.HeroLevelDistributedReq) (resp forms.HeroLevelDistributedRespData, err error) { rolesMap, allRoleType, allRoleName := getRoleInfos() resp.HeroName = allRoleName resp.Level = []string{} for i := queryDate.StartLevel; i <= queryDate.EndLevel; i++ { resp.Level = append(resp.Level, strconv.Itoa(i)) } resp.Data = []*forms.HeroLevelDistributedItem{} for _, roleType := range allRoleType { data := &forms.HeroLevelDistributedItem{} data.Name = rolesMap[roleType] data.Type = "bar" data.Data = []int{} resp.Data = append(resp.Data, data) } rdb := query.Use(config.DB).RolesLog m := rdb.Where(rdb.Date.Eq(queryDate.Day)) switch queryDate.ChannelId { case consts.ChannelIdNone: // 不选择渠道 case consts.ChannelIdAllAdv, consts.ChannelIdAllWx, consts.ChannelIdAllTT: // 所有的广告渠道 m = m.Where(rdb.ChannelID.In(Channel.GetIdsByType(queryDate.ChannelId)...)) default: // 指定渠道 m = m.Where(rdb.ChannelID.Eq(queryDate.ChannelId)) } if queryDate.ServerId > 0 { m = m.Where(rdb.ServerID.Eq(int32(queryDate.ServerId))) } results, err := m.Find() if err != nil { return resp, err } var roleLevelInfo = make(map[string]int) for _, result := range results { var tmpInfo = make(map[string]int) json.Unmarshal([]byte(result.Collect), &tmpInfo) for k, v := range tmpInfo { roleLevelInfo[k] += v } } for idx, roleType := range allRoleType { resp.Data[idx].Data = getRoleCount(roleLevelInfo, roleType, resp.Level) } return } func getRoleCountByType(roleLevelInfo map[string]int, roleType int, level int) int { var c int for k, v := range roleLevelInfo { idTemp, _ := strconv.Atoi(k) role := player.GetRoleData(idTemp) if role.Type == roleType && role.Level == level { c += v } } return c } func getRoleCount(roleLevelInfo map[string]int, roleType int, levelInfo []string) (result []int) { for _, level := range levelInfo { result = append(result, getRoleCountByType(roleLevelInfo, roleType, cast.ToInt(level))) } return } func getRoleInfos() (result map[int]string, allRoleType []int, allRoleName []string) { result = map[int]string{} for _, role := range gmdata.Roles { roleType := int(role.Type) if _, ok := result[roleType]; !ok { result[roleType] = role.Name allRoleType = append(allRoleType, roleType) allRoleName = append(allRoleName, role.Name) } } return }