bulletin_dao.go
3.84 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
package model
import (
"client/bulletin/db"
"client/bulletin/utils"
"database/sql"
"log"
"strconv"
"time"
)
const (
getLastTimeSQLUser = "SELECT last_show FROM ice_notice_control WHERE (user_id = ? OR device_id = ?)" +
" and notice_id =?" +
" and game_id=?"
getLastTimeSQL = "SELECT last_show FROM ice_notice_control WHERE device_id = ?" +
" and notice_id =?" +
" and game_id=?"
getContentSQL = "select notice_title,notice_content,daily_once,id from ice_notice where notice_game = ?" +
" and ? BETWEEN start_time and end_time"
insertControlSQL = "INSERT INTO ice_notice_control (notice_id,game_id,user_id,device_id,last_show) VALUES(?,?,?,?,?)"
updateControlSQL = "UPDATE ice_notice_control SET last_show =?" +
" WHERE notice_id=?" +
" AND game_id=?" +
" AND (user_id=?" +
" OR device_id=?)"
)
type sqlExecutor interface {
Exec(query string, args ...interface{}) (sql.Result, error)
Query(query string, args ...interface{}) (*sql.Rows, error)
QueryRow(query string, args ...interface{}) *sql.Row
}
type Bulletin struct {
NoticeTitle string `json:"title"`
NoticeContent string `json:"content"`
}
type BulletinParam struct {
DisplayInterval int64 `json:"daily_once"`
BulletinID int64 `json:"notice_id"` //数据表notice对应bulletin。
}
type Bulletins struct {
Notices []Bulletin `json:"notices"`
}
//返回 titile，content
func GetBulletinByIDChannelAndPlatform(dbOne sqlExecutor, gameID, userId, deviceId, channelName, platformName, forceShow string) (Bulletins, error) {
timeNow := time.Now()
bulletin := &Bulletin{}
bulletinParam := &BulletinParam{}
bulletins := new(Bulletins)
//目前默认是全平台，全渠道；未做判断。
rows, err := dbOne.Query(getContentSQL, gameID, timeNow)
if err == sql.ErrNoRows {
return *bulletins, nil
}
if err != nil {
return *bulletins, err
}
defer rows.Close()
for rows.Next() {
rows.Scan(&bulletin.NoticeTitle, &bulletin.NoticeContent, &bulletinParam.DisplayInterval, &bulletinParam.BulletinID)
//查出来，gameID，userID,bullletinID对应的lastTime。毫秒值解析。
var lastTime sql.NullString
err := dbOne.QueryRow(getLastTimeSQL, deviceId, bulletinParam.BulletinID, gameID).Scan(&lastTime)
if err != nil {
log.Println(err)
//return *bulletins, err
}
// 先判断，是不是为空，如果为空，直接置入结果集，DB插入完整数据。
if lastTime.String == "" {
log.Println("trying insert: ")
bulletins.Notices = append(bulletins.Notices, *bulletin)
insertNoticeControlTab(db.Mysql, gameID, userId, deviceId, bulletinParam.BulletinID)
continue
}
// 不为空，根据lastTime判断是不是一天前，是的话，置入结果集，DB更新lastTime。
timeed, err := strconv.ParseInt(lastTime.String, 10, 64)
if err != nil {
log.Println("时间戳解析int错误")
}
//一天内的判断。不能用时间差。
if forceShow != "1" && utils.CheckMinTiemToNow(timeed, bulletinParam.DisplayInterval) {
log.Println("limit time! 公告标题、校验及ID", bulletin.NoticeTitle, bulletinParam)
continue
}
log.Println("trying update")
bulletins.Notices = append(bulletins.Notices, *bulletin)
updateNoticeControlTab(db.Mysql, bulletinParam.BulletinID, gameID, userId, deviceId)
}
return *bulletins, nil
}
//后期如果效率不够，可以考虑go func 调。
func insertNoticeControlTab(db sqlExecutor, gameID, userID, deviceID string, bulletinID int64) {
log.Println("insert to notice_control Tab")
_, err := db.Exec(insertControlSQL, bulletinID, gameID, userID, deviceID, time.Now().Unix())
if err != nil {
log.Println(err)
}
}
func updateNoticeControlTab(db sqlExecutor, bulletinID int64, gameID, userID, deviceID string) {
log.Println("update notice_control Tab")
_, err := db.Exec(updateControlSQL, time.Now().Unix(), bulletinID, gameID, userID, deviceID)
if err != nil {
log.Println(err)
}
}