compute_statistic.go 9.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290
  1. package crontask
  2. import (
  3. "strconv"
  4. "time"
  5. "wechat-api/ent"
  6. "wechat-api/ent/contact"
  7. "wechat-api/ent/messagerecords"
  8. "wechat-api/ent/usagedetail"
  9. "wechat-api/ent/usagestatisticday"
  10. "wechat-api/ent/usagestatistichour"
  11. "wechat-api/ent/usagestatisticmonth"
  12. "wechat-api/ent/wx"
  13. )
  14. func (l *CronTask) computeStatistic() {
  15. startTime := time.Now()
  16. // 获取所有机器人信息
  17. wxbots, err := l.svcCtx.DB.Wx.Query().Select(wx.FieldWxid, wx.FieldID, wx.FieldOrganizationID).All(l.ctx)
  18. if err != nil {
  19. l.Errorf("fetch wxids error:%v\n", err)
  20. return
  21. }
  22. /*
  23. 计算本小时的数据
  24. 1. 查询出上小时里所有 usagedetail 内容
  25. 2. 挨个遍历他的 bot_id ,再查询他的 bot_id 相关的参数
  26. 3. 遍历的时候可能有重复,所以要先检查是否生成了数据,如果有就忽略,没有再生成
  27. ----------------------------------------------------------------------------------------------------------
  28. */
  29. // 获取当前时间
  30. now := time.Now()
  31. // 获取本小时的第一分钟
  32. currentHour := time.Date(now.Year(), now.Month(), now.Day(), now.Hour(), 0, 0, 0, now.Location())
  33. currentHourInt, _ := strconv.Atoi(currentHour.Format("2006010215"))
  34. // 上一个小时的起始时间
  35. lastHour := currentHour.Add(-time.Hour * 1)
  36. lastHourInt, _ := strconv.Atoi(lastHour.Format("2006010215"))
  37. for _, wxinfo := range wxbots {
  38. l.Logger.Infof("开始计算小时数据:%d\n", currentHourInt)
  39. // 先判断该账号是否已经统计了小时数据,如果已经统计了,就不需要再统计了
  40. var aiResponseInt, sopRunInt, friendCountInt, groupCountInt, accountBalanceInt, consumeTokenInt, activeUserInt, newUserInt int
  41. hourDataCount, _ := l.svcCtx.DB.UsageStatisticHour.Query().Where(
  42. usagestatistichour.Type(1),
  43. usagestatistichour.BotID(wxinfo.Wxid),
  44. usagestatistichour.Addtime(uint64(currentHourInt)),
  45. ).Count(l.ctx)
  46. if hourDataCount > 0 {
  47. continue
  48. }
  49. // AI回复包括:SOP次数+AI次数
  50. // SOP次数:content 非空,source_type = 3 或 4,sub_source_id = 0
  51. // AI次数:app = 1 或 3
  52. sopresp, _ := l.svcCtx.DB.MessageRecords.Query().Where(
  53. messagerecords.SubSourceID(0),
  54. messagerecords.SourceTypeIn(3, 4),
  55. messagerecords.BotWxid(wxinfo.Wxid),
  56. messagerecords.CreatedAtGTE(lastHour),
  57. messagerecords.CreatedAtLT(currentHour),
  58. ).Count(l.ctx)
  59. airesp, _ := l.svcCtx.DB.UsageDetail.Query().Where(
  60. usagedetail.AppIn(1, 3),
  61. usagedetail.BotID(wxinfo.Wxid),
  62. usagedetail.CreatedAtGTE(lastHour),
  63. usagedetail.CreatedAtLT(currentHour),
  64. ).Count(l.ctx)
  65. aiResponseInt = sopresp + airesp
  66. // SOP执行次数:SOP阶段和节点的执行次数。
  67. sopRunInt, _ = l.svcCtx.DB.MessageRecords.Query().Where(
  68. messagerecords.BotWxid(wxinfo.Wxid),
  69. messagerecords.SubSourceIDEQ(0),
  70. messagerecords.SourceTypeIn(3, 4),
  71. messagerecords.BotWxid(wxinfo.Wxid),
  72. messagerecords.CreatedAtGTE(lastHour),
  73. messagerecords.CreatedAtLT(currentHour),
  74. ).Count(l.ctx)
  75. // 好友总数:contact 表中 type=1
  76. friendCountInt, _ = l.svcCtx.DB.Contact.Query().Where(
  77. contact.Type(1),
  78. contact.WxWxid(wxinfo.Wxid),
  79. ).Count(l.ctx)
  80. // 群总数:contact 表中 type=2
  81. groupCountInt, _ = l.svcCtx.DB.Contact.Query().Where(
  82. contact.Type(2),
  83. contact.WxWxid(wxinfo.Wxid),
  84. ).Count(l.ctx)
  85. // 消耗Token数:usage_detail 表
  86. consumeTokenInt, _ = l.svcCtx.DB.UsageDetail.Query().Where(
  87. usagedetail.TypeEQ(1),
  88. usagedetail.BotID(wxinfo.Wxid),
  89. usagedetail.CreatedAtGTE(lastHour),
  90. usagedetail.CreatedAtLT(currentHour),
  91. ).Aggregate(ent.Sum("total_tokens")).Int(l.ctx)
  92. // 账户余额
  93. accountBalanceInt = 0
  94. // 活跃好友:usage_detail 表 type = 1
  95. activeUserInt, _ = l.svcCtx.DB.UsageDetail.Query().Where(
  96. usagedetail.Type(1),
  97. usagedetail.BotID(wxinfo.Wxid),
  98. usagedetail.CreatedAtGTE(lastHour),
  99. usagedetail.CreatedAtLT(currentHour),
  100. ).GroupBy(usagedetail.FieldBotID).Int(l.ctx)
  101. lastHourData, _ := l.svcCtx.DB.UsageStatisticHour.Query().Where(
  102. usagestatistichour.AddtimeEQ(uint64(lastHourInt)),
  103. usagestatistichour.Type(1),
  104. usagestatistichour.BotID(wxinfo.Wxid),
  105. ).First(l.ctx)
  106. if lastHourData == nil {
  107. newUserInt = friendCountInt
  108. } else {
  109. newUserInt = int(lastHourData.TotalFriend) - friendCountInt
  110. }
  111. _, err := l.svcCtx.DB.UsageStatisticHour.Create().
  112. SetType(1).
  113. SetBotID(wxinfo.Wxid).
  114. SetOrganizationID(wxinfo.OrganizationID).
  115. SetAiResponse(uint64(aiResponseInt)).
  116. SetSopRun(uint64(sopRunInt)).
  117. SetTotalFriend(uint64(friendCountInt)).
  118. SetTotalGroup(uint64(groupCountInt)).
  119. SetAccountBalance(uint64(accountBalanceInt)).
  120. SetConsumeToken(uint64(consumeTokenInt)).
  121. SetActiveUser(uint64(activeUserInt)).
  122. SetNewUser(int64(newUserInt)).
  123. SetAddtime(uint64(currentHourInt)).
  124. Save(l.ctx)
  125. l.Errorf("save hour data error:%v \n", err)
  126. }
  127. /*
  128. 计算日数据
  129. ----------------------------------------------------------------------------------------------------------
  130. */
  131. dayStr := time.Now().Format("20060102")
  132. day, _ := strconv.Atoi(dayStr)
  133. // 获取今天的第一小时
  134. firstHour := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
  135. firstHourInt, _ := strconv.Atoi(firstHour.Format("20060102"))
  136. // 获取今天的最后一小时
  137. lastHour = time.Date(now.Year(), now.Month(), now.Day(), 23, 0, 0, 0, now.Location())
  138. lastHourInt, _ = strconv.Atoi(lastHour.Format("20060102"))
  139. for _, wxinfo := range wxbots {
  140. l.Logger.Infof("开始计算日数据:%d\n", day)
  141. // 先判断该账号是否已经统计了日数据,如果已经统计了,就不需要再统计了
  142. dayDataCount, _ := l.svcCtx.DB.UsageStatisticDay.Query().Where(
  143. usagestatisticday.Type(1),
  144. usagestatisticday.BotID(wxinfo.Wxid),
  145. usagestatisticday.Addtime(uint64(day)),
  146. ).Count(l.ctx)
  147. // 如果添加过了就略过
  148. if dayDataCount > 0 {
  149. continue
  150. }
  151. hourDataBatch, _ := l.svcCtx.DB.UsageStatisticHour.Query().Where(
  152. usagestatistichour.Type(1),
  153. usagestatistichour.BotID(wxinfo.Wxid),
  154. usagestatistichour.AddtimeGTE(uint64(firstHourInt)),
  155. usagestatistichour.AddtimeLTE(uint64(lastHourInt)),
  156. ).All(l.ctx)
  157. var aiResponse, sopRun, totalFriend, totalGroup, accountBalance, consumeToken, activeUser uint64
  158. var newUser int64
  159. for _, hourData := range hourDataBatch {
  160. aiResponse += hourData.AiResponse
  161. sopRun += hourData.SopRun
  162. totalFriend += hourData.TotalFriend
  163. totalGroup += hourData.TotalGroup
  164. accountBalance += hourData.AccountBalance
  165. consumeToken += hourData.ConsumeToken
  166. activeUser += hourData.ActiveUser
  167. newUser += hourData.NewUser
  168. }
  169. _, err := l.svcCtx.DB.UsageStatisticDay.Create().
  170. SetAddtime(uint64(day)).
  171. SetType(1).
  172. SetBotID(wxinfo.Wxid).
  173. SetOrganizationID(wxinfo.OrganizationID).
  174. SetAiResponse(aiResponse).
  175. SetSopRun(sopRun).
  176. SetTotalFriend(totalFriend).
  177. SetTotalGroup(totalGroup).
  178. SetAccountBalance(accountBalance).
  179. SetConsumeToken(consumeToken).
  180. SetActiveUser(activeUser).
  181. SetNewUser(newUser).
  182. Save(l.ctx)
  183. if err != nil {
  184. l.Errorf("create day data error:%v \n", err)
  185. continue
  186. }
  187. }
  188. /*
  189. 查看月表数据是否已经完成
  190. 1. 查询出上月里所有 usagedetail 内容
  191. 2. 挨个遍历他的 bot_id ,再查询他的 bot_id 相关的参数
  192. ----------------------------------------------------------------------------------------------------------
  193. */
  194. monthStr := time.Now().Format("200601")
  195. month, _ := strconv.Atoi(monthStr)
  196. for _, wxinfo := range wxbots {
  197. l.Logger.Infof("开始计算月数据:%d\n", month)
  198. // 获取本月的第一天
  199. firstDay := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, now.Location())
  200. firstDayInt, _ := strconv.Atoi(firstDay.Format("20060102"))
  201. // 获取本月的最后一天
  202. lastDay := firstDay.AddDate(0, 1, -1)
  203. lastDayInt, _ := strconv.Atoi(lastDay.Format("20060102"))
  204. // 先判断该账号是否已经统计了月数据,如果已经统计了,就不需要再统计了
  205. monthDataCount, _ := l.svcCtx.DB.UsageStatisticMonth.Query().Where(
  206. usagestatisticmonth.Type(1),
  207. usagestatisticmonth.BotID(wxinfo.Wxid),
  208. usagestatisticmonth.Addtime(uint64(month)),
  209. ).Count(l.ctx)
  210. // 如果添加过了就略过
  211. if monthDataCount > 0 {
  212. continue
  213. }
  214. dayDataBatch, _ := l.svcCtx.DB.UsageStatisticDay.Query().Where(
  215. usagestatisticday.Type(1),
  216. usagestatisticday.BotID(wxinfo.Wxid),
  217. usagestatisticday.AddtimeGTE(uint64(firstDayInt)),
  218. usagestatisticday.AddtimeLTE(uint64(lastDayInt)),
  219. ).All(l.ctx)
  220. var aiResponse, sopRun, totalFriend, totalGroup, accountBalance, consumeToken, activeUser uint64
  221. var newUser int64
  222. for _, dayData := range dayDataBatch {
  223. aiResponse += dayData.AiResponse
  224. sopRun += dayData.SopRun
  225. totalFriend += dayData.TotalFriend
  226. totalGroup += dayData.TotalGroup
  227. accountBalance += dayData.AccountBalance
  228. consumeToken += dayData.ConsumeToken
  229. activeUser += dayData.ActiveUser
  230. newUser += dayData.NewUser
  231. }
  232. _, err := l.svcCtx.DB.UsageStatisticMonth.Create().
  233. SetAddtime(uint64(month)).
  234. SetType(1).
  235. SetBotID(wxinfo.Wxid).
  236. SetOrganizationID(wxinfo.OrganizationID).
  237. SetAiResponse(aiResponse).
  238. SetSopRun(sopRun).
  239. SetTotalFriend(totalFriend).
  240. SetTotalGroup(totalGroup).
  241. SetAccountBalance(accountBalance).
  242. SetConsumeToken(consumeToken).
  243. SetActiveUser(activeUser).
  244. SetNewUser(newUser).
  245. Save(l.ctx)
  246. if err != nil {
  247. l.Errorf("create month data error:%v \n", err)
  248. continue
  249. }
  250. }
  251. finishTime := time.Now()
  252. l.Logger.Infof("This process cost %v", finishTime.Sub(startTime).String())
  253. return
  254. }