package v1 import ( "designs/app/common/request" "designs/app/common/response" "designs/global" "designs/model" "designs/utils" "github.com/gin-gonic/gin" "math" "time" ) // 用户广告数据总览 func UserAdsOverview(c *gin.Context) { form := request.Check(c, &struct { Gid string `json:"gid" binding:"required"` Pf []string `form:"pf" binding:"required"` }{}) //近七日广告总数 //查询近七日活跃总数 now := time.Now() sevenDayAgo := now.AddDate(0, 0, -7) thirtyDayAgo := now.AddDate(0, 0, -30) var adsCount7 int64 err := global.App.DB.Table("user_see_ads"). Where("gid", form.Gid). WhereIn("pf", form.Pf). Where("adsState", 2). //只统计看完的 Where("createdAt", ">=", sevenDayAgo). Where("createdAt", "<=", now). Count(&adsCount7).Error if err != nil { response.Fail(c, 1001, err.Error()) return } //近30天广告总数 var adsCount30 int64 err = global.App.DB.Table("user_see_ads"). Where("gid", form.Gid). WhereIn("pf", form.Pf). Where("adsState", 2). //只统计看完的 Where("createdAt", ">=", thirtyDayAgo). Where("createdAt", "<=", now). Count(&adsCount30).Error if err != nil { response.Fail(c, 1001, err.Error()) return } //近七日活跃用户人均看广告数 var activeUserCount7 int64 err = global.App.DB.Table("user_login"). Where("gid", form.Gid). WhereIn("pf", form.Pf). Where("loginTime", ">=", sevenDayAgo). Where("loginTime", "<=", now). Distinct("userId"). Count(&activeUserCount7).Error if err != nil { response.Fail(c, 1001, err.Error()) return } //用户人均看广告数 var userTotal int64 err = global.App.DB.Table("user"). Where("gid", form.Gid). WhereIn("pf", form.Pf).Count(&userTotal).Error if err != nil { response.Fail(c, 1001, err.Error()) return } var adsTotal int64 err = global.App.DB.Table("user_see_ads"). Where("gid", form.Gid). Where("adsState", 2). //只统计看完的 WhereIn("pf", form.Pf).Count(&adsTotal).Error if err != nil { response.Fail(c, 1001, err.Error()) return } var avgUserAds float64 var avgUserAds7 float64 if activeUserCount7 == 0 { avgUserAds7 = 0 } else { avgUserAds7 = float64(adsCount7) / float64(activeUserCount7) } if userTotal == 0 { avgUserAds = 0 } else { avgUserAds = float64(adsTotal) / float64(userTotal) } response.Success(c, gin.H{ "data": map[string]interface{}{ "adsCount7": adsCount7, "adsCount30": adsCount30, "avgUserAds": math.Round(avgUserAds*100) / 100, "avgUserAds7": math.Round(avgUserAds7*100) / 100, }, }) } // 每日看广告总数 func UserAdsDaily(c *gin.Context) { form := request.Check(c, &struct { Gid string `json:"gid" binding:"required"` Pf []string `form:"pf" binding:""` StartTime string `form:"startTime" json:"startTime" binding:"required"` EndTime string `form:"endTime" json:"endTime" binding:"required"` }{}) dates := utils.GetTimeDayDate(form.StartTime, form.EndTime) //response.Success(c, gin.H{ // "dates": dates, //}) //return form.EndTime = form.EndTime + " 23:59:59" //近七日广告总数 var userAdsDaily []struct { Date string `json:"date" gorm:"not null;column:date;"` Count int64 `json:"count" gorm:"not null;column:count;"` } err := global.App.DB.Table("user_see_ads"). Where("gid", form.Gid). WhereIn("pf", form.Pf). Where("adsState", 2). //只统计看完的 Where("createdAt", ">=", form.StartTime). Where("createdAt", "<=", form.EndTime). Group("date"). Select("count(*) as count", "date"). Scan(&userAdsDaily).Error if err != nil { response.Fail(c, 1001, err.Error()) return } res := make(map[string]interface{}) for k := range dates { res[k] = 0 } for _, v := range userAdsDaily { date := v.Date[0:4] + "-" + v.Date[4:6] + "-" + v.Date[6:] res[date] = v.Count } var count int64 for _, v := range userAdsDaily { count = count + v.Count } response.Success(c, gin.H{ "data": map[string]interface{}{ "userAdsDaily": res, "count": count, "avg": count / int64(len(dates)), }, }) } // 广告详细数据() func UserAdsDetail(c *gin.Context) { form := request.Check(c, &struct { Gid string `json:"gid" binding:"required"` Pf []string `form:"pf" binding:""` StartTime string `form:"startTime" json:"startTime" binding:""` EndTime string `form:"endTime" json:"endTime" binding:""` AdsState []int `json:"adsState" binding:""` AdsType string `json:"adsType" binding:""` OpenId string `json:"openId" binding:""` Offset int `json:"offset" binding:""` Limit int `json:"limit" binding:""` Order string `json:"order" binding:""` }{}) form.EndTime = form.EndTime + " 23:59:59" query := global.App.DB.Table("user_see_ads"). LeftJoin("user", "user.userId = user_see_ads.userId and user.pf = user_see_ads.pf and user.gid = user_see_ads.gid") query = query.Where("user_see_ads.gid", form.Gid).WhereIn("user_see_ads.pf", form.Pf) if form.StartTime != "" && form.EndTime != "" { query = query.Where("user_see_ads.createdAt", ">=", form.StartTime).Where("user_see_ads.createdAt", "<=", form.EndTime) } if len(form.AdsState) > 0 { query = query.WhereIn("adsState", form.AdsState) } if form.OpenId != "" { query = query.Where("openId", form.OpenId) } if form.AdsType != "" { query = query.Where("adsType", form.AdsType) } var count int64 err := query.Count(&count).Error if err != nil { response.Fail(c, 1001, err.Error()) return } var resData []struct { model.UserSeeAds OpenId string `json:"openId" gorm:"not null;column:openId;"` } err = query.Select("user_see_ads.*", "user.openId"). Offset(form.Offset).Limit(form.Limit). Order("user_see_ads.id desc").Scan(&resData).Error if err != nil { response.Fail(c, 1001, err.Error()) return } response.Success(c, gin.H{ "data": resData, "count": count, }) }