welfare.go 8.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283
  1. package dao
  2. import (
  3. "context"
  4. xsql "database/sql"
  5. "fmt"
  6. "strings"
  7. "go-common/app/service/main/vip/model"
  8. "go-common/library/database/sql"
  9. "github.com/pkg/errors"
  10. )
  11. const (
  12. _getWelfareListSQL = "SELECT id, welfare_name, homepage_uri, backdrop_uri, tid, rank FROM vip_welfare WHERE state = 0 AND tid = ? AND recommend in (0,?) AND stime < ? AND etime > ? ORDER BY rank LIMIT ?,?"
  13. _countWelfareSQL = "SELECT COUNT(1) FROM vip_welfare WHERE state = 0 AND tid = ? AND recommend in (0,?) AND stime < ? AND etime > ?"
  14. _getRecommendWelfareSQL = "SELECT id, welfare_name, homepage_uri, backdrop_uri, tid, rank FROM vip_welfare WHERE state = 0 AND recommend = ? AND stime < ? AND etime > ? ORDER BY rank LIMIT ?,?"
  15. _countRecommendWelfareSQL = "SELECT COUNT(1) FROM vip_welfare WHERE state = 0 AND recommend = ? AND stime < ? AND etime > ?"
  16. _getWelfareTypeListSQL = "SELECT id, name FROM vip_welfare_type WHERE state = 0"
  17. _getWelfareInfoSQL = "SELECT id, welfare_name, welfare_desc, receive_rate, homepage_uri, backdrop_uri, usage_form, vip_type, stime, etime FROM vip_welfare WHERE id = ?"
  18. _getWelfareBatchSQL = "SELECT id, received_count, count, vtime FROM vip_welfare_code_batch WHERE state = 0 AND wid = ?"
  19. _getReceivedCodeSQL = "SELECT id, mtime FROM vip_welfare_code WHERE state = 0 AND wid = ? AND mid = ?"
  20. _getWelfareCodeListSQL = "SELECT id, bid, code FROM vip_welfare_code WHERE mid = 0 AND state = 0 AND wid = ? AND bid IN (%s) ORDER BY id limit 10"
  21. _receiveWelfareSQL = "UPDATE vip_welfare_code SET mid = ? where id = ? and mid = 0"
  22. _updateStockSQL = "UPDATE vip_welfare_code_batch SET received_count = received_count+1 WHERE received_count < count AND id = ? "
  23. _getMyWelfareSQL = "SELECT a.id, a.welfare_name, a.welfare_desc, a.usage_form, a.receive_uri, a.stime, a.etime, b.code FROM vip_welfare a LEFT JOIN vip_welfare_code b ON a.id = b.wid WHERE b.mid = ? ORDER BY b.mtime desc"
  24. _addReceiveRedirectWelfareSQL = "INSERT INTO vip_welfare_code (wid, mid) VALUES (?, ?)"
  25. _countReceiveRedirectWelfareSQL = "SELECT COUNT(1) FROM vip_welfare_code WHERE wid = ? AND mid = ?"
  26. _insertReceiveRecordSQL = "INSERT INTO vip_welfare_record (mid, wid, month, count) VALUES (?, ?, ?, 1)"
  27. )
  28. // GetWelfareList get welfare list
  29. func (d *Dao) GetWelfareList(c context.Context, req *model.ArgWelfareList) (res []*model.WelfareListResp, err error) {
  30. var rows *sql.Rows
  31. if rows, err = d.db.Query(c, _getWelfareListSQL, req.Tid, req.Recommend, req.NowTime, req.NowTime, (req.Pn-1)*req.Ps, req.Ps); err != nil {
  32. err = errors.WithStack(err)
  33. return
  34. }
  35. defer rows.Close()
  36. for rows.Next() {
  37. r := &model.WelfareListResp{}
  38. if err = rows.Scan(&r.ID, &r.Name, &r.HomepageUri, &r.BackdropUri, &r.Tid, &r.Rank); err != nil {
  39. err = errors.WithStack(err)
  40. res = nil
  41. return
  42. }
  43. res = append(res, r)
  44. }
  45. return
  46. }
  47. // CountWelfare count welfare list
  48. func (d *Dao) CountWelfare(c context.Context, req *model.ArgWelfareList) (count int64, err error) {
  49. row := d.db.QueryRow(c, _countWelfareSQL, req.Tid, req.Recommend, req.NowTime, req.NowTime)
  50. if err = row.Scan(&count); err != nil {
  51. if sql.ErrNoRows == err {
  52. err = nil
  53. count = 0
  54. return
  55. }
  56. err = errors.WithStack(err)
  57. }
  58. return
  59. }
  60. // GetRecommendWelfare get recommend welfare
  61. func (d *Dao) GetRecommendWelfare(c context.Context, req *model.ArgWelfareList) (res []*model.WelfareListResp, err error) {
  62. var rows *sql.Rows
  63. if rows, err = d.db.Query(c, _getRecommendWelfareSQL, req.Recommend, req.NowTime, req.NowTime, (req.Pn-1)*req.Ps, req.Ps); err != nil {
  64. err = errors.WithStack(err)
  65. return
  66. }
  67. defer rows.Close()
  68. for rows.Next() {
  69. r := &model.WelfareListResp{}
  70. if err = rows.Scan(&r.ID, &r.Name, &r.HomepageUri, &r.BackdropUri, &r.Tid, &r.Rank); err != nil {
  71. err = errors.WithStack(err)
  72. res = nil
  73. return
  74. }
  75. res = append(res, r)
  76. }
  77. return
  78. }
  79. // CountRecommendWelfare count recommend welfare
  80. func (d *Dao) CountRecommendWelfare(c context.Context, req *model.ArgWelfareList) (count int64, err error) {
  81. row := d.db.QueryRow(c, _countRecommendWelfareSQL, req.Recommend, req.NowTime, req.NowTime)
  82. if err = row.Scan(&count); err != nil {
  83. if sql.ErrNoRows == err {
  84. err = nil
  85. count = 0
  86. return
  87. }
  88. err = errors.WithStack(err)
  89. }
  90. return
  91. }
  92. // GetWelfareTypeList get welfare type list
  93. func (d *Dao) GetWelfareTypeList(c context.Context) (res []*model.WelfareTypeListResp, err error) {
  94. var rows *sql.Rows
  95. if rows, err = d.db.Query(c, _getWelfareTypeListSQL); err != nil {
  96. err = errors.WithStack(err)
  97. return
  98. }
  99. defer rows.Close()
  100. for rows.Next() {
  101. r := &model.WelfareTypeListResp{}
  102. if err = rows.Scan(&r.ID, &r.Name); err != nil {
  103. err = errors.WithStack(err)
  104. res = nil
  105. return
  106. }
  107. res = append(res, r)
  108. }
  109. return
  110. }
  111. // GetWelfareInfo get welfare info
  112. func (d *Dao) GetWelfareInfo(c context.Context, id int64) (res *model.WelfareInfoResp, err error) {
  113. res = new(model.WelfareInfoResp)
  114. if err = d.db.QueryRow(c, _getWelfareInfoSQL, id).
  115. Scan(&res.ID, &res.Name, &res.Desc, &res.ReceiveRate, &res.HomepageUri, &res.BackdropUri, &res.UsageForm, &res.VipType, &res.Stime, &res.Etime); err != nil {
  116. if err == sql.ErrNoRows {
  117. res = nil
  118. err = nil
  119. return
  120. }
  121. err = errors.Wrapf(err, "dao GetWelfareInfo(%d)", id)
  122. }
  123. return
  124. }
  125. // GetWelfareBatch get welfare batch infos
  126. func (d *Dao) GetWelfareBatch(c context.Context, wid int64) (res []*model.WelfareBatchResp, err error) {
  127. var rows *sql.Rows
  128. if rows, err = d.db.Query(c, _getWelfareBatchSQL, wid); err != nil {
  129. err = errors.WithStack(err)
  130. return
  131. }
  132. defer rows.Close()
  133. for rows.Next() {
  134. r := &model.WelfareBatchResp{}
  135. if err = rows.Scan(&r.Id, &r.ReceivedCount, &r.Count, &r.Vtime); err != nil {
  136. err = errors.WithStack(err)
  137. res = nil
  138. return
  139. }
  140. res = append(res, r)
  141. }
  142. return
  143. }
  144. // GetReceivedCode get received code
  145. func (d *Dao) GetReceivedCode(c context.Context, wid, mid int64) (res []*model.ReceivedCodeResp, err error) {
  146. var rows *sql.Rows
  147. if rows, err = d.db.Query(c, _getReceivedCodeSQL, wid, mid); err != nil {
  148. err = errors.WithStack(err)
  149. return
  150. }
  151. defer rows.Close()
  152. for rows.Next() {
  153. r := &model.ReceivedCodeResp{}
  154. if err = rows.Scan(&r.ID, &r.Mtime); err != nil {
  155. err = errors.WithStack(err)
  156. res = nil
  157. return
  158. }
  159. res = append(res, r)
  160. }
  161. return
  162. }
  163. //UpdateWelfareCodeUser user receive welfare
  164. func (d *Dao) UpdateWelfareCodeUser(c context.Context, tx *sql.Tx, id int, mid int64) (affectedRows int64, err error) {
  165. var (
  166. res xsql.Result
  167. )
  168. if res, err = tx.Exec(_receiveWelfareSQL, mid, id); err != nil {
  169. err = errors.WithStack(err)
  170. return
  171. }
  172. affectedRows, err = res.RowsAffected()
  173. return
  174. }
  175. //UpdateWelfareBatch reduce count
  176. func (d *Dao) UpdateWelfareBatch(c context.Context, tx *sql.Tx, bid int) (err error) {
  177. if _, err = tx.Exec(_updateStockSQL, bid); err != nil {
  178. err = errors.WithStack(err)
  179. }
  180. return
  181. }
  182. // GetWelfareCodeUnReceived get unReceived welfare code
  183. func (d *Dao) GetWelfareCodeUnReceived(c context.Context, wid int64, bids []string) (res []*model.UnReceivedCodeResp, err error) {
  184. var rows *sql.Rows
  185. if rows, err = d.db.Query(c, fmt.Sprintf(_getWelfareCodeListSQL, strings.Join(bids, ",")), wid); err != nil {
  186. err = errors.WithStack(err)
  187. return
  188. }
  189. defer rows.Close()
  190. for rows.Next() {
  191. r := new(model.UnReceivedCodeResp)
  192. if err = rows.Scan(&r.Id, &r.Bid, &r.Code); err != nil {
  193. err = errors.WithStack(err)
  194. res = nil
  195. return
  196. }
  197. res = append(res, r)
  198. }
  199. return
  200. }
  201. // GetMyWelfare get my welfare infos
  202. func (d *Dao) GetMyWelfare(c context.Context, mid int64) (res []*model.MyWelfareResp, err error) {
  203. var rows *sql.Rows
  204. if rows, err = d.db.Query(c, _getMyWelfareSQL, mid); err != nil {
  205. err = errors.WithStack(err)
  206. return
  207. }
  208. defer rows.Close()
  209. for rows.Next() {
  210. r := &model.MyWelfareResp{}
  211. if err = rows.Scan(&r.Wid, &r.Name, &r.Desc, &r.UsageForm, &r.ReceiveUri, &r.Stime, &r.Etime, &r.Code); err != nil {
  212. err = errors.WithStack(err)
  213. res = nil
  214. return
  215. }
  216. res = append(res, r)
  217. }
  218. return
  219. }
  220. // AddReceiveRedirectWelfare add redirect url
  221. func (d *Dao) AddReceiveRedirectWelfare(c context.Context, wid, mid int64) (err error) {
  222. if _, err = d.db.Exec(c, _addReceiveRedirectWelfareSQL, wid, mid); err != nil {
  223. err = errors.WithStack(err)
  224. return
  225. }
  226. return
  227. }
  228. // CountReceiveRedirectWelfare count it
  229. func (d *Dao) CountReceiveRedirectWelfare(c context.Context, wid, mid int64) (count int64, err error) {
  230. row := d.db.QueryRow(c, _countReceiveRedirectWelfareSQL, wid, mid)
  231. if err = row.Scan(&count); err != nil {
  232. if sql.ErrNoRows == err {
  233. err = nil
  234. count = 0
  235. return
  236. }
  237. err = errors.WithStack(err)
  238. }
  239. return
  240. }
  241. // InsertReceiveRecord to prevent repeated receive
  242. func (d *Dao) InsertReceiveRecord(c context.Context, tx *sql.Tx, mid, wid, monthYear int64) (err error) {
  243. if _, err = tx.Exec(_insertReceiveRecordSQL, mid, wid, monthYear); err != nil {
  244. err = errors.WithStack(err)
  245. return
  246. }
  247. return
  248. }