广告平台(站长使用)
25'ten fazla konu seçemezsiniz Konular bir harf veya rakamla başlamalı, kısa çizgiler ('-') içerebilir ve en fazla 35 karakter uzunluğunda olabilir.
 
 
 
 
 

246 satır
9.9 KiB

  1. package svc
  2. import (
  3. "applet/app/e"
  4. "applet/app/md"
  5. "applet/app/utils"
  6. db "code.fnuoos.com/zhimeng/model.git/src"
  7. "fmt"
  8. "github.com/gin-gonic/gin"
  9. "strings"
  10. "time"
  11. )
  12. func IndexTotal(c *gin.Context) {
  13. //昨天
  14. yesterday := utils.GetTimeRange("yesterday")
  15. yesterdayTotal := commTotal(c, time.Unix(yesterday["start"], 0).Format("2006-01-02"), time.Unix(yesterday["end"]-3600, 0).Format("2006-01-02"))
  16. //前天
  17. beforeYesterDayTotal := commTotal(c, time.Unix(yesterday["start"]-86400, 0).Format("2006-01-02"), time.Unix(yesterday["start"]-3600, 0).Format("2006-01-02"))
  18. //7天
  19. withinSevenDays := utils.GetTimeRange("new_within_seven_days")
  20. withinSevenDaysTotal := commTotal(c, time.Unix(withinSevenDays["start"], 0).Format("2006-01-02"), time.Unix(withinSevenDays["end"]-3600, 0).Format("2006-01-02"))
  21. //7天前的 7天
  22. 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"))
  23. //本月
  24. currentMonth := utils.GetTimeRange("current_month")
  25. currentMonthTotal := commTotal(c, time.Unix(currentMonth["start"], 0).Format("2006-01-02"), time.Unix(currentMonth["end"]-3600, 0).Format("2006-01-02"))
  26. //上月
  27. lastMonth := utils.GetTimeRange("last_month")
  28. lastMonthTotal := commTotal(c, time.Unix(lastMonth["start"], 0).Format("2006-01-02"), time.Unix(lastMonth["end"]-3600, 0).Format("2006-01-02"))
  29. //上上月
  30. beforeLastMonth := time.Unix(lastMonth["start"]-86400, 0)
  31. beforeLastMonthStr := time.Date(beforeLastMonth.Year(), beforeLastMonth.Month(), 1, 0, 0, 0, 0, beforeLastMonth.Location())
  32. beforeLastMonthTotal := commTotal(c, beforeLastMonthStr.Format("2006-01-02"), time.Unix(lastMonth["start"]-3600, 0).Format("2006-01-02"))
  33. res := []md.IndexAppListDataList{
  34. {Name: "昨日收益(媒体)", Type: "yesterday", Bili: commBili(c, beforeYesterDayTotal, yesterdayTotal, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(yesterdayTotal[0]["media_revenue"]) / 100)},
  35. {Name: "七日收益(媒体)", Type: "within_seven_days", Bili: commBili(c, beforeWithinSevenDaysTotal, withinSevenDaysTotal, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(withinSevenDaysTotal[0]["media_revenue"]) / 100)},
  36. {Name: "本月收益(媒体)", Type: "current_month", Bili: commBili(c, lastMonthTotal, currentMonthTotal, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(currentMonthTotal[0]["media_revenue"]) / 100)},
  37. {Name: "上月收益(媒体)", Type: "last_month", Bili: commBili(c, beforeLastMonthTotal, lastMonthTotal, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(lastMonthTotal[0]["media_revenue"]) / 100)},
  38. {Name: "昨日收益(曝光量)", Type: "yesterday", Bili: commBili(c, beforeYesterDayTotal, yesterdayTotal, "exposure_count"), Value: yesterdayTotal[0]["exposure_count"]},
  39. {Name: "七日收益(曝光量)", Type: "within_seven_days", Bili: commBili(c, beforeWithinSevenDaysTotal, withinSevenDaysTotal, "exposure_count"), Value: withinSevenDaysTotal[0]["exposure_count"]},
  40. {Name: "本月收益(曝光量)", Type: "current_month", Bili: commBili(c, lastMonthTotal, currentMonthTotal, "exposure_count"), Value: currentMonthTotal[0]["exposure_count"]},
  41. {Name: "上月收益(曝光量)", Type: "last_month", Bili: commBili(c, beforeLastMonthTotal, lastMonthTotal, "exposure_count"), Value: lastMonthTotal[0]["exposure_count"]},
  42. }
  43. e.OutSuc(c, res, nil)
  44. return
  45. }
  46. func commBili(c *gin.Context, before, after []map[string]string, types string) string {
  47. bili := "-%"
  48. if utils.StrToFloat64(before[0][types]) > 0 {
  49. tmpDiff := utils.StrToFloat64(after[0][types]) - utils.StrToFloat64(before[0][types])
  50. types1 := "+"
  51. if tmpDiff < 0 {
  52. types1 = ""
  53. }
  54. bili = types1 + utils.Float64ToStr(tmpDiff/utils.StrToFloat64(before[0][types])*100) + "%"
  55. }
  56. return bili
  57. }
  58. func BeforeSevenPoint(c *gin.Context, req md.IndexAppListTableReq) map[string][]string {
  59. start := utils.TimeStdParseUnix(req.StartDate + " 00:00:00")
  60. day := (utils.TimeStdParseUnix(req.EndDate+" 00:00:00")-start)/86400 + 1
  61. first := start - day*86400*6
  62. dayAll := (utils.TimeStdParseUnix(req.EndDate+" 00:00:00")-first)/86400 + 1
  63. firstTime := time.Unix(first, 0).Format("2006-01-02")
  64. appData := make(map[string][]string)
  65. for _, v := range req.AppId {
  66. appData[v] = make([]string, dayAll/day+1, dayAll/day+1)
  67. }
  68. dateList := make(map[int]string)
  69. j := 1
  70. for i := 0; i < int(dayAll); i++ {
  71. if i >= (j-1)*int(day) && i < j*int(day) {
  72. dateList[j-1] += "," + time.Unix(first+int64(i)*86400, 0).Format("2006-01-02")
  73. continue
  74. }
  75. j++
  76. dateList[j-1] += "," + time.Unix(first+int64(i)*86400, 0).Format("2006-01-02")
  77. }
  78. if len(req.AppId) > 0 {
  79. date := commTotalByDate(c, firstTime, req.EndDate, req.AppId)
  80. for _, v := range date {
  81. for k1, v1 := range dateList {
  82. if strings.Contains(v1, v["date"]) {
  83. appData[v["app_id"]][k1] = utils.Float64ToStr(utils.StrToFloat64(appData[v["app_id"]][k1]) + utils.StrToFloat64(v["media_revenue"]))
  84. }
  85. }
  86. }
  87. }
  88. for k, v := range appData {
  89. for k1, v1 := range v {
  90. if v1 == "" {
  91. appData[k][k1] = "0"
  92. }
  93. }
  94. }
  95. return appData
  96. }
  97. func IndexAppList(c *gin.Context, req md.IndexAppListReq) md.IndexAppListRes {
  98. appId := make([]string, 0)
  99. data := commTotalByApp(c, req, appId)
  100. for _, v := range data {
  101. appId = append(appId, v["app_id"])
  102. }
  103. start := utils.TimeStdParseUnix(req.StartDate + " 00:00:00")
  104. day := (utils.TimeStdParseUnix(req.EndDate+" 00:00:00") - start) / 86400
  105. req.EndDate = time.Unix(start-1, 0).Format("2006-01-02")
  106. req.StartDate = time.Unix(start-1-day*86400, 0).Format("2006-01-02")
  107. dataLast := commTotalByApp(c, req, appId)
  108. dataLastMap := make(map[string][]map[string]string)
  109. for _, v := range dataLast {
  110. dataLastMap[v["app_id"]] = make([]map[string]string, 0)
  111. dataLastMap[v["app_id"]] = append(dataLastMap[v["app_id"]], v)
  112. }
  113. list := make([]md.IndexAppListData, 0)
  114. for _, v := range data {
  115. _, ok := dataLastMap[v["app_id"]]
  116. if ok == false {
  117. dataLastMap[v["app_id"]] = []map[string]string{
  118. {"media_revenue": "0", "ecpm": "0", "exposure_count": "0", "click_count": "0", "click_rate": "0"},
  119. }
  120. }
  121. tmpList := []map[string]string{
  122. v,
  123. }
  124. dataList := []md.IndexAppListDataList{
  125. {Name: "广告预估收益", Type: "media_revenue", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "media_revenue"), Value: utils.Float64ToStr(utils.StrToFloat64(v["media_revenue"]) / 100)},
  126. {Name: "ECPM", Type: "ecpm", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "ecpm"), Value: utils.Float64ToStr(utils.StrToFloat64(v["ecpm"]) / float64(day))},
  127. {Name: "曝光量", Type: "exposure_count", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "exposure_count"), Value: v["exposure_count"]},
  128. {Name: "点击量", Type: "click_count", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "click_count"), Value: v["click_count"]},
  129. {Name: "点击率", Type: "click_rate", Bili: commBili(c, dataLastMap[v["app_id"]], tmpList, "click_rate"), Value: utils.GetPrec(v["click_rate"], "2") + "%"},
  130. }
  131. applet := GetAppletInfo(c, v["app_id"])
  132. tmp := md.IndexAppListData{
  133. Name: applet["name"],
  134. Logo: applet["logo"],
  135. List: dataList,
  136. TableList: []string{},
  137. }
  138. list = append(list, tmp)
  139. }
  140. res := md.IndexAppListRes{
  141. List: list,
  142. AppId: appId,
  143. Sort: []md.SelectData{
  144. {Name: "按收益最高", Value: "media_revenue desc"},
  145. {Name: "按ECPM最高", Value: "ecpm desc"},
  146. {Name: "按曝光量最高", Value: "exposure_count desc"},
  147. },
  148. }
  149. return res
  150. }
  151. func commTotal(c *gin.Context, startDate, endDate string) []map[string]string {
  152. sql := `
  153. SELECT
  154. SUM(exposure_count) as exposure_count,
  155. SUM(click_count) as click_count,
  156. SUM(click_rate) as click_rate,
  157. SUM(ecpm) as ecpm,
  158. SUM(media_revenue) as media_revenue
  159. FROM generate_wx_ad_data
  160. where %s
  161. `
  162. where := "uuid=" + c.GetString("mid")
  163. if startDate != "" {
  164. where += " and date>='" + startDate + "'"
  165. }
  166. if endDate != "" {
  167. where += " and date<='" + endDate + "'"
  168. }
  169. sql = fmt.Sprintf(sql, where)
  170. nativeString, _ := db.QueryNativeString(db.Db, sql)
  171. if len(nativeString) == 0 {
  172. nativeString = []map[string]string{
  173. {
  174. "media_revenue": "0",
  175. },
  176. }
  177. }
  178. return nativeString
  179. }
  180. func commTotalByApp(c *gin.Context, req md.IndexAppListReq, appId []string) []map[string]string {
  181. sql := `
  182. SELECT
  183. app_id as app_id,
  184. SUM(exposure_count) as exposure_count,
  185. SUM(click_count) as click_count,
  186. SUM(click_count)/SUM(exposure_count)*100 as click_rate,
  187. SUM(ecpm) as ecpm,
  188. SUM(media_revenue) as media_revenue
  189. FROM generate_wx_ad_data
  190. where %s group by app_id order by %s,id asc limit 50
  191. `
  192. where := "uuid=" + c.GetString("mid")
  193. if req.StartDate != "" {
  194. where += " and date>='" + req.StartDate + "'"
  195. }
  196. if req.EndDate != "" {
  197. where += " and date<='" + req.EndDate + "'"
  198. }
  199. if req.Name != "" {
  200. mediumId := GetMediumId(c, req.Name)
  201. where += " and medium_id in(" + mediumId + ")"
  202. }
  203. if len(appId) > 0 {
  204. where += " and app_id in(‘" + strings.Join(appId, ",") + "’)"
  205. }
  206. if req.Sort == "" {
  207. req.Sort = "media_revenue desc"
  208. }
  209. sql = fmt.Sprintf(sql, where, req.Sort)
  210. nativeString, _ := db.QueryNativeString(db.Db, sql)
  211. return nativeString
  212. }
  213. func commTotalByDate(c *gin.Context, startDate, endDate string, appId []string) []map[string]string {
  214. sql := `
  215. SELECT
  216. app_id as app_id,
  217. date as date,
  218. SUM(exposure_count) as exposure_count,
  219. SUM(click_count) as click_count,
  220. SUM(click_count)/SUM(exposure_count)*100 as click_rate,
  221. SUM(ecpm) as ecpm,
  222. SUM(media_revenue) as media_revenue
  223. FROM generate_wx_ad_data
  224. where %s group by date,app_id
  225. `
  226. where := "uuid=" + c.GetString("mid")
  227. if startDate != "" {
  228. where += " and date>='" + startDate + "'"
  229. }
  230. if endDate != "" {
  231. where += " and date<='" + endDate + "'"
  232. }
  233. if len(appId) > 0 {
  234. where += " and app_id in('" + strings.Join(appId, ",") + "')"
  235. }
  236. sql = fmt.Sprintf(sql, where)
  237. nativeString, err := db.QueryNativeString(db.Db, sql)
  238. fmt.Println(err)
  239. return nativeString
  240. }