package svc import ( "applet/app/e" "applet/app/md" "applet/app/utils" db "code.fnuoos.com/zhimeng/model.git/src" "fmt" "github.com/gin-gonic/gin" "strings" "time" ) func IndexTotal(c *gin.Context) { //昨天 yesterday := utils.GetTimeRange("yesterday") yesterdayTotal := commTotal(c, time.Unix(yesterday["start"], 0).Format("2006-01-02"), time.Unix(yesterday["end"]-3600, 0).Format("2006-01-02")) //前天 beforeYesterDayTotal := commTotal(c, time.Unix(yesterday["start"]-86400, 0).Format("2006-01-02"), time.Unix(yesterday["start"]-3600, 0).Format("2006-01-02")) //7天 withinSevenDays := utils.GetTimeRange("new_within_seven_days") withinSevenDaysTotal := commTotal(c, time.Unix(withinSevenDays["start"], 0).Format("2006-01-02"), time.Unix(withinSevenDays["end"]-3600, 0).Format("2006-01-02")) //7天前的 7天 beforeWithinSevenDaysTotal := commTotal(c, time.Unix(withinSevenDays["start"]-3600-7*86400, 0).Format("2006-01-02"), time.Unix(withinSevenDays["start"]-3600, 0).Format("2006-01-02")) //本月 currentMonth := utils.GetTimeRange("current_month") currentMonthTotal := commTotal(c, time.Unix(currentMonth["start"], 0).Format("2006-01-02"), time.Unix(currentMonth["end"]-3600, 0).Format("2006-01-02")) //上月 lastMonth := utils.GetTimeRange("last_month") lastMonthTotal := commTotal(c, time.Unix(lastMonth["start"], 0).Format("2006-01-02"), time.Unix(lastMonth["end"]-3600, 0).Format("2006-01-02")) //上上月 beforeLastMonth := time.Unix(lastMonth["start"]-86400, 0) beforeLastMonthStr := time.Date(beforeLastMonth.Year(), beforeLastMonth.Month(), 1, 0, 0, 0, 0, beforeLastMonth.Location()) beforeLastMonthTotal := commTotal(c, beforeLastMonthStr.Format("2006-01-02"), time.Unix(lastMonth["start"]-3600, 0).Format("2006-01-02")) res := []md.IndexAppListDataList{ {Name: "昨日收益(媒体)", Type: "yesterday", Bili: commBili(c, beforeYesterDayTotal, yesterdayTotal, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(yesterdayTotal[0]["media_revenue"]) / 100)}, {Name: "七日收益(媒体)", Type: "within_seven_days", Bili: commBili(c, beforeWithinSevenDaysTotal, withinSevenDaysTotal, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(withinSevenDaysTotal[0]["media_revenue"]) / 100)}, {Name: "本月收益(媒体)", Type: "current_month", Bili: commBili(c, lastMonthTotal, currentMonthTotal, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(currentMonthTotal[0]["media_revenue"]) / 100)}, {Name: "上月收益(媒体)", Type: "last_month", Bili: commBili(c, beforeLastMonthTotal, lastMonthTotal, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(lastMonthTotal[0]["media_revenue"]) / 100)}, {Name: "昨日收益(曝光量)", Type: "yesterday", Bili: commBili(c, beforeYesterDayTotal, yesterdayTotal, "exposure_count"), Value: yesterdayTotal[0]["exposure_count"]}, {Name: "七日收益(曝光量)", Type: "within_seven_days", Bili: commBili(c, beforeWithinSevenDaysTotal, withinSevenDaysTotal, "exposure_count"), Value: withinSevenDaysTotal[0]["exposure_count"]}, {Name: "本月收益(曝光量)", Type: "current_month", Bili: commBili(c, lastMonthTotal, currentMonthTotal, "exposure_count"), Value: currentMonthTotal[0]["exposure_count"]}, {Name: "上月收益(曝光量)", Type: "last_month", Bili: commBili(c, beforeLastMonthTotal, lastMonthTotal, "exposure_count"), Value: lastMonthTotal[0]["exposure_count"]}, } e.OutSuc(c, res, nil) return } func commBili(c *gin.Context, before, after []map[string]string, types string) string { bili := "-%" if utils.StrToFloat64(before[0][types]) > 0 { tmpDiff := utils.StrToFloat64(after[0][types]) - utils.StrToFloat64(before[0][types]) types1 := "+" if tmpDiff < 0 { types1 = "" } bili = types1 + utils.Float64ToStr(tmpDiff/utils.StrToFloat64(before[0][types])*100) + "%" } return bili } func BeforeSevenPoint(c *gin.Context, req md.IndexAppListTableReq) map[string][]string { start := utils.TimeStdParseUnix(req.StartDate + " 00:00:00") day := (utils.TimeStdParseUnix(req.EndDate+" 00:00:00")-start)/86400 + 1 first := start - day*86400*6 dayAll := (utils.TimeStdParseUnix(req.EndDate+" 00:00:00")-first)/86400 + 1 firstTime := time.Unix(first, 0).Format("2006-01-02") appData := make(map[string][]string) for _, v := range req.AppId { appData[v] = make([]string, dayAll/day+1, dayAll/day+1) } dateList := make(map[int]string) j := 1 for i := 0; i < int(dayAll); i++ { if i >= (j-1)*int(day) && i < j*int(day) { dateList[j-1] += "," + time.Unix(first+int64(i)*86400, 0).Format("2006-01-02") continue } j++ dateList[j-1] += "," + time.Unix(first+int64(i)*86400, 0).Format("2006-01-02") } if len(req.AppId) > 0 { date := commTotalByDate(c, firstTime, req.EndDate, req.AppId) for _, v := range date { for k1, v1 := range dateList { if strings.Contains(v1, v["date"]) { appData[v["app_id"]][k1] = utils.Float64ToStr(utils.StrToFloat64(appData[v["app_id"]][k1]) + utils.StrToFloat64(v["media_revenue"])) } } } } for k, v := range appData { for k1, v1 := range v { if v1 == "" { appData[k][k1] = "0" } } } return appData } func IndexAppList(c *gin.Context, req md.IndexAppListReq) md.IndexAppListRes { appId := make([]string, 0) data := commTotalByApp(c, req, appId) for _, v := range data { appId = append(appId, v["app_id"]) } start := utils.TimeStdParseUnix(req.StartDate + " 00:00:00") day := (utils.TimeStdParseUnix(req.EndDate+" 00:00:00") - start) / 86400 req.EndDate = time.Unix(start-1, 0).Format("2006-01-02") req.StartDate = time.Unix(start-1-day*86400, 0).Format("2006-01-02") dataLast := commTotalByApp(c, req, appId) dataLastMap := make(map[string][]map[string]string) for _, v := range dataLast { dataLastMap[v["app_id"]] = make([]map[string]string, 0) dataLastMap[v["app_id"]] = append(dataLastMap[v["app_id"]], v) } list := make([]md.IndexAppListData, 0) for _, v := range data { _, ok := dataLastMap[v["app_id"]] if ok == false { dataLastMap[v["app_id"]] = []map[string]string{ {"media_revenue": "0", "ecpm": "0", "exposure_count": "0", "click_count": "0", "click_rate": "0"}, } } tmpList := []map[string]string{ v, } dataList := []md.IndexAppListDataList{ {Name: "广告预估收益", Type: "media_revenue", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(v["media_revenue"]) / 100)}, {Name: "ECPM", Type: "ecpm", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "ecpm"), Value: utils.Float64ToStr(utils.StrToFloat64(v["ecpm"]) / float64(day))}, {Name: "曝光量", Type: "exposure_count", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "exposure_count"), Value: v["exposure_count"]}, {Name: "点击量", Type: "click_count", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "click_count"), Value: v["click_count"]}, {Name: "点击率", Type: "click_rate", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "click_rate"), Value: utils.GetPrec(v["click_rate"], "2") + "%"}, } applet := GetAppletInfo(c, v["app_id"]) tmp := md.IndexAppListData{ Name: applet["name"], Logo: applet["logo"], List: dataList, TableList: []string{}, } list = append(list, tmp) } res := md.IndexAppListRes{ List: list, AppId: appId, Sort: []md.SelectData{ {Name: "按收益最高", Value: "media_revenue desc"}, {Name: "按ECPM最高", Value: "ecpm desc"}, {Name: "按曝光量最高", Value: "exposure_count desc"}, }, } return res } func commTotal(c *gin.Context, startDate, endDate string) []map[string]string { sql := ` SELECT SUM(exposure_count) as exposure_count, SUM(click_count) as click_count, SUM(click_rate) as click_rate, SUM(ecpm) as ecpm, SUM(media_revenue) as media_revenue FROM generate_wx_ad_data where %s ` where := "uuid=" + c.GetString("mid") if startDate != "" { where += " and date>='" + startDate + "'" } if endDate != "" { where += " and date<='" + endDate + "'" } sql = fmt.Sprintf(sql, where) nativeString, _ := db.QueryNativeString(db.Db, sql) if len(nativeString) == 0 { nativeString = []map[string]string{ { "media_revenue": "0", }, } } return nativeString } func commTotalByApp(c *gin.Context, req md.IndexAppListReq, appId []string) []map[string]string { sql := ` SELECT app_id as app_id, SUM(exposure_count) as exposure_count, SUM(click_count) as click_count, SUM(click_count)/SUM(exposure_count)*100 as click_rate, SUM(ecpm) as ecpm, SUM(media_revenue) as media_revenue FROM generate_wx_ad_data where %s group by app_id order by %s,id asc limit 50 ` where := "uuid=" + c.GetString("mid") if req.StartDate != "" { where += " and date>='" + req.StartDate + "'" } if req.EndDate != "" { where += " and date<='" + req.EndDate + "'" } if req.Name != "" { mediumId := GetMediumId(c, req.Name) where += " and medium_id in(" + mediumId + ")" } if len(appId) > 0 { where += " and app_id in(‘" + strings.Join(appId, ",") + "’)" } if req.Sort == "" { req.Sort = "media_revenue desc" } sql = fmt.Sprintf(sql, where, req.Sort) nativeString, _ := db.QueryNativeString(db.Db, sql) return nativeString } func commTotalByDate(c *gin.Context, startDate, endDate string, appId []string) []map[string]string { sql := ` SELECT app_id as app_id, date as date, SUM(exposure_count) as exposure_count, SUM(click_count) as click_count, SUM(click_count)/SUM(exposure_count)*100 as click_rate, SUM(ecpm) as ecpm, SUM(media_revenue) as media_revenue FROM generate_wx_ad_data where %s group by date,app_id ` where := "uuid=" + c.GetString("mid") if startDate != "" { where += " and date>='" + startDate + "'" } if endDate != "" { where += " and date<='" + endDate + "'" } if len(appId) > 0 { where += " and app_id in('" + strings.Join(appId, ",") + "')" } sql = fmt.Sprintf(sql, where) nativeString, err := db.QueryNativeString(db.Db, sql) fmt.Println(err) return nativeString }