chunhao_admin/controller/v1/userAds.go

package v1

import (
	"designs/app/common/request"
	"designs/app/common/response"
	"designs/global"
	"designs/model"
	"github.com/gin-gonic/gin"
	"math"
	"strconv"
	"strings"
	"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).Format("20060102")
	thirtyDayAgo := now.AddDate(0, 0, -30).Format("20060102")

	var adsCount7 int64
	err := global.App.DB.Table("summary_user_see_ads").
		Where("gid", form.Gid).
		Where("pf", form.Pf).
		Where("date", ">=", sevenDayAgo).Select("SUM(count) as total").Scan(&adsCount7).Error
	if err != nil {
		response.Fail(c, 1001, err.Error())
		return
	}

	//近30天广告总数
	var adsCount30 int64
	err = global.App.DB.Table("summary_user_see_ads").
		Where("gid", form.Gid).
		Where("pf", form.Pf).
		Where("date", ">=", thirtyDayAgo).Select("SUM(count) as total").Scan(&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("summary_user_see_ads").
		Where("gid", form.Gid).
		Where("pf", form.Pf).
		Select("SUM(count) as total").Scan(&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)
	}

	//当日变现人数
	var todayCashCount int64

	query := global.App.DB.Table("user").
		Where("gid", form.Gid).
		Where("pf", form.Pf).
		WhereRaw("DATE_FORMAT(createdAt, '%Y%m%d') = ?", now.Format("20060102")).Select("userId").SubQuery()
	err = global.App.DB.Table("user_see_ads").
		Where("gid", form.Gid).
		Where("pf", form.Pf).
		Where("date", "=", now.Format("20060102")).
		WhereRaw("userId in (?)", query).
		Distinct("userId").
		Count(&todayCashCount).Error
	if err != nil {
		response.Fail(c, 1001, err.Error())
		return
	}

	response.Success(c, gin.H{
		"data": map[string]interface{}{
			"adsCount7":      adsCount7,
			"adsCount30":     adsCount30,
			"todayCashCount": todayCashCount,
			"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"`
	}{})

	startDate := strings.Replace(form.StartTime, "-", "", -1)
	endDate := strings.Replace(form.EndTime, "-", "", -1)

	var summary []model.SummaryUserSeeAds
	err := global.App.DB.Table("summary_user_see_ads").
		Where("gid", form.Gid).
		Where("pf", form.Pf).
		Where("date", ">=", startDate).
		Where("date", "<=", endDate).Scan(&summary).Error

	if err != nil {
		response.Fail(c, 1001, err.Error())
		return
	}

	res := make(map[string]int)
	resToday := make(map[string]int)
	for _, v := range summary {
		newDate := v.Date[:4] + "-" + v.Date[4:6] + "-" + v.Date[6:]

		res[newDate] = v.Count
		resToday[newDate] = v.TodayCount
	}

	response.Success(c, gin.H{
		"data": map[string]interface{}{
			"userAdsDaily":      res,
			"userAdsDailyToday": resToday,
			"count":             0,
			"avg":               0,
		},
	})

}

// 广告详细数据()
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,
	})

}

// 广告详细数据 --- 饼状图
func UserAdsCake(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:""`
		CreateTime string   `form:"createTime" json:"createTime" binding:""`
		AdsState   []int    `json:"adsState" binding:""`
		AdsType    string   `json:"adsType" binding:""`
	}{})

	form.EndTime = form.EndTime + " 23:59:59"

	query := global.App.DB.Table("user_see_ads").Where("gid", form.Gid).Group("adsScene")

	if len(form.Pf) > 0 {
		query = query.WhereIn("pf", form.Pf)
	}

	if form.CreateTime != "" {
		createTime := strings.Split(form.CreateTime, ",")
		startTime, _ := strconv.Atoi(createTime[0])
		endTime, _ := strconv.Atoi(createTime[1])

		t := time.Unix(int64(startTime)/1000, 0)
		s := time.Unix(int64(endTime)/1000, 0)

		query = query.Where("user_see_ads.createdAt", ">=", t).Where("user_see_ads.createdAt", "<=", s)
	}

	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.AdsType != "" {
		query = query.Where("adsType", form.AdsType)
	}

	var data []struct {
		Count    int64  `json:"count" gorm:"column:count;"`
		Name     string `json:"name" gorm:"not null;column:name;"`
		SumType1 int    `json:"sumType1" gorm:"not null;column:sumType1;"`
		SumType2 int    `json:"sumType2" gorm:"not null;column:sumType2;"`
		SumType0 int    `json:"sumType0" gorm:"not null;column:sumType0;"`
	}

	err := query.Select(
		"adsScene as name",
		"adsState",
		"count(*) as count",
		"SUM(adsState = 1) AS sumType1",
		"SUM(adsState = 2) AS sumType2",
		"SUM(adsState = 0) AS sumType0",
	).Scan(&data).Error
	if err != nil {
		response.Fail(c, 1001, err.Error())
		return
	}

	response.Success(c, gin.H{
		"data": data,
	})
}