video_product_material.lua 5.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182
  1. --下载队列
  2. local M = {}
  3. local mysqldbx = require "mysqldbx"
  4. local tools = require "tools"
  5. local skynet = require "skynet"
  6. local cjson = require "cjson"
  7. local mysql = require "skynet.db.mysql"
  8. local config = require "run_config"
  9. local db
  10. local mysqldtaskbx = {}
  11. function M.set_status(msg_body)
  12. local isok ,key = tools.checkData({"id_list","is_download"},msg_body)
  13. if not isok then
  14. return false,string.format("缺少字段: %s.", key)
  15. end
  16. local idString = table.concat(msg_body.id_list, ",")
  17. local sql = string.format("SELECT * FROM video_product_material WHERE id IN (%s)",idString)
  18. local isok,res;
  19. res = mysqldtaskbx.Singleton().query(sql)
  20. for i = 1, #res, 1 do
  21. local id = res[i].id
  22. sql = string.format("UPDATE video_product_material SET is_download = %d WHERE id =%d ",msg_body.is_download,id)
  23. mysqldtaskbx.Singleton().query(sql)
  24. end
  25. return true,{}
  26. end
  27. function M.set_d_z_number(msg_body)
  28. local isok ,key = tools.checkData({"id_list","d_z_number"},msg_body)
  29. if not isok then
  30. return false,string.format("缺少字段: %s.", key)
  31. end
  32. local idString = table.concat(msg_body.id_list, ",")
  33. local sql = string.format("SELECT * FROM video_product_material WHERE id IN (%s)",idString)
  34. local isok,res;
  35. res = mysqldtaskbx.Singleton().query(sql)
  36. for i = 1, #res, 1 do
  37. local id = res[i].id
  38. sql = string.format("UPDATE video_product_material SET d_z_number = %d WHERE id =%d ",msg_body.d_z_number,id)
  39. mysqldtaskbx.Singleton().query(sql)
  40. end
  41. return true,{}
  42. end
  43. function M.search_video_product_material(msg_body)
  44. local isok ,key = tools.checkData({"page_size",
  45. "page_number",
  46. "start_create_time",
  47. "end_create_time",
  48. "dy_id",
  49. "product_id",
  50. "product_name",
  51. "book_platform",
  52. "title",
  53. "is_download",
  54. "signature",
  55. "cleaning_status"},msg_body)
  56. if not isok then
  57. return false,string.format("缺少字段: %s.", key)
  58. end
  59. local page_size = msg_body.page_size
  60. local page_number = msg_body.page_number
  61. local offset = (page_number - 1) * page_size
  62. local dy_id_param = ""
  63. if msg_body.dy_id~="" then
  64. dy_id_param = " AND dy_id = "..msg_body.dy_id
  65. end
  66. local product_id_param = ""
  67. if msg_body.product_id~="" then
  68. product_id_param = " AND product_id = "..msg_body.product_id
  69. end
  70. local product_name_param = ""
  71. if msg_body.product_name~="" then
  72. product_name_param = string.format(" AND ( product_name LIKE CONCAT( '%%%s%%')) ",msg_body.product_name)
  73. end
  74. local book_platform_param = ""
  75. if msg_body.book_platform~="" then
  76. book_platform_param = " AND book_platform = "..msg_body.book_platform
  77. end
  78. local title_param = ""
  79. if msg_body.title~="" then
  80. title_param = string.format(" AND ( product_name LIKE CONCAT( '%%%s%%')) ",msg_body.title)
  81. end
  82. local is_download_param = ""
  83. if msg_body.is_download~="" then
  84. is_download_param = " AND is_download = "..msg_body.is_download
  85. end
  86. local status_param = ""
  87. -- if msg_body.status~="" then
  88. -- status_param = " AND status = "..msg_body.status
  89. -- end
  90. local signature_param = ""
  91. if msg_body.signature~="" then
  92. signature_param = string.format(" AND signature = '%s' ",msg_body.signature)
  93. end
  94. local cleaning_status_param = ""
  95. if msg_body.cleaning_status~="" then
  96. cleaning_status_param = " AND cleaning_status = "..msg_body.cleaning_status
  97. end
  98. local create_date_param = ""
  99. if msg_body.start_create_time~="" and msg_body.end_create_time~="" then
  100. create_date_param = " AND create_day >= DATE(FROM_UNIXTIME(" .. (msg_body.start_create_time / 1000) .. ")) AND create_day <= DATE(FROM_UNIXTIME(" .. (msg_body.end_create_time / 1000) .. ")) "
  101. end
  102. local param = dy_id_param..product_id_param..product_name_param..book_platform_param..title_param..is_download_param..status_param..create_date_param..cleaning_status_param
  103. local sql = "SELECT * FROM video_product_material WHERE dy_id!=0 "..param.. " ORDER BY update_time DESC "..string.format(" LIMIT %d OFFSET %d ",page_size, offset)
  104. skynet.error("sql:",sql)
  105. local list = mysqldtaskbx.Singleton().query(sql)
  106. sql = "SELECT COUNT(*) AS total FROM video_product_material WHERE dy_id!=0 "..param
  107. local total = mysqldtaskbx.Singleton().query(sql)
  108. return true,list,total[1].total
  109. end
  110. function M.delete_video_product_material(msg_body)
  111. local isok ,key = tools.checkData({"id_list"},msg_body)
  112. if not isok then
  113. return false,string.format("缺少字段: %s.", key)
  114. end
  115. for i = 1, #msg_body.id_list, 1 do
  116. local id = msg_body.id_list[i]
  117. local sql = string.format("DELETE FROM video_product_material WHERE id = %d ",id)
  118. mysqldtaskbx.Singleton().query(sql)
  119. end
  120. return true, {}
  121. end
  122. function mysqldtaskbx.start()
  123. local function on_connect(db)
  124. db:query("set charset utf8mb4");
  125. end
  126. local conf = config.db_cnf.book_server.mysqldb_task_cnf
  127. db = mysql.connect{
  128. host=conf.ip,
  129. port=conf.port,
  130. database=conf.db,
  131. user=conf.user,
  132. password=conf.password,
  133. charset="utf8mb4",
  134. max_packet_size = 1024 * 1024,
  135. on_connect = on_connect
  136. }
  137. if not db then
  138. skynet.error("mysql connect fail")
  139. end
  140. end
  141. function mysqldtaskbx.Singleton()
  142. if db == nil then
  143. mysqldtaskbx.start()
  144. end
  145. return mysqldtaskbx
  146. end
  147. function mysqldtaskbx.query(sql)
  148. return db:query(sql)
  149. end
  150. return M