query.go 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458
  1. package report
  2. import (
  3. "fmt"
  4. "hongze/hongze_yb/global"
  5. "hongze/hongze_yb/models/response"
  6. "hongze/hongze_yb/models/response/pc"
  7. "hongze/hongze_yb/utils"
  8. )
  9. // GetLatestClassReportsByClassifyIdSeconds 根据用户已购买的分类权限查询个分类最新的报告
  10. func GetLatestClassReportsByClassifyIdSeconds(classifyIdSeconds []int) (reportList []*Report, err error) {
  11. sql := `SELECT t1.id,
  12. t1.classify_id_first,
  13. t1.classify_id_second,
  14. t1.stage,
  15. t1.title,
  16. t1.classify_name_first,
  17. t1.classify_name_second,
  18. t1.publish_time
  19. FROM report t1
  20. INNER JOIN
  21. (
  22. SELECT classify_id_first, max( publish_time ) AS max_publish_time
  23. FROM report
  24. WHERE
  25. state = 2
  26. AND classify_name_first !="权益研报"
  27. AND classify_id_second IN ?
  28. GROUP BY
  29. classify_id_first
  30. ) t2
  31. ON t1.classify_id_first = t2.classify_id_first AND
  32. t1.publish_time = t2.max_publish_time
  33. WHERE t1.state = 2
  34. AND t1.classify_name_first !="权益研报"
  35. AND t1.classify_id_second IN ?`
  36. err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds, classifyIdSeconds).Scan(&reportList).Error
  37. return
  38. }
  39. // GetReportsByClassifyIdSecondsAndDate 根据时间和报告分类筛选出合适的记录
  40. func GetReportsByClassifyIdSecondsAndDate(classifyIdSeconds []int, publishTime string) (reportList []*Report, err error) {
  41. err = global.MYSQL["rddp"].Model(Report{}).
  42. Select("id, classify_name_first").
  43. Where("classify_id_second in (?) and state = 2 and publish_time > ? ", classifyIdSeconds, publishTime).Scan(&reportList).Error
  44. return
  45. }
  46. // GetListByIDsAndClassifyIdFirst 分页查询
  47. func GetListByIDsAndClassifyIdFirst(ids []int, classifyIdFirst int, offset, limit int) (reportList []*Report, err error) {
  48. err = global.MYSQL["rddp"].Model(Report{}).
  49. Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time").
  50. Where("id in (?) and classify_id_first=? and state = 2 ", ids, classifyIdFirst).
  51. Order("publish_time desc, id desc").
  52. Offset(offset).
  53. Limit(limit).
  54. Scan(&reportList).Error
  55. return
  56. }
  57. // GetListCountByIDsAndClassifyIdFirst 分页查询
  58. func GetListCountByIDsAndClassifyIdFirst(ids []int, classifyIdFirst int) (total int64, err error) {
  59. err = global.MYSQL["rddp"].Model(Report{}).
  60. Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time").
  61. Where("id in (?) and classify_id_first=? and state = 2 ", ids, classifyIdFirst).
  62. Count(&total).Error
  63. return
  64. }
  65. // GetListByClassifyIdSeconds 分页查询
  66. func GetListByClassifyIdSeconds(classifyIdSeconds []int, offset, limit int) (reportList []*Report, err error) {
  67. err = global.MYSQL["rddp"].Model(Report{}).
  68. Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time").
  69. Where("classify_id_second in (?) and state = 2 ", classifyIdSeconds).
  70. Order("publish_time desc, id desc").
  71. Offset(offset).
  72. Limit(limit).
  73. Scan(&reportList).Error
  74. return
  75. }
  76. // GetListCountByClassifyIdSeconds
  77. func GetListCountByClassifyIdSeconds(classifyIdSeconds []int) (total int64, err error) {
  78. err = global.MYSQL["rddp"].Model(Report{}).
  79. Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time").
  80. Where("classify_id_second in (?) and state = 2 ", classifyIdSeconds).
  81. Count(&total).Error
  82. return
  83. }
  84. // GetListByClassifyIdFirst 按照类型分页查询
  85. func GetListByClassifyIdFirst(classifyIdFirst int, offset, limit int) (reportList []*Report, err error) {
  86. err = global.MYSQL["rddp"].Model(Report{}).
  87. Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time").
  88. Where("classify_id_first=? and state = 2 ", classifyIdFirst).
  89. Order("publish_time desc, id desc").
  90. Offset(offset).
  91. Limit(limit).
  92. Scan(&reportList).Error
  93. return
  94. }
  95. // GetListCountByClassifyIdFirst 按照类型查询报告总数
  96. func GetListCountByClassifyIdFirst(classifyIdFirst int) (total int64, err error) {
  97. err = global.MYSQL["rddp"].Model(Report{}).
  98. Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time").
  99. Where("classify_id_first=? and state = 2 ", classifyIdFirst).
  100. Count(&total).Error
  101. return
  102. }
  103. // GetLatestByReportIds 根据ids获取报告
  104. func GetLatestByReportIds(ids []int) (item *Report, err error) {
  105. err = global.MYSQL["rddp"].Where("id in ? and state = 2", ids).Order("publish_time desc, id desc").First(&item).Error
  106. if err == utils.ErrNoRow {
  107. err = nil
  108. }
  109. return
  110. }
  111. // GetPublishByReportId 根据id获取报告
  112. func GetPublishByReportId(id int) (item *Report, err error) {
  113. err = global.MYSQL["rddp"].Where("id = ? and state = 2", id).First(&item).Error
  114. if err == utils.ErrNoRow {
  115. err = nil
  116. }
  117. return
  118. }
  119. // GetByReportId 根据id获取报告
  120. func GetByReportId(id int) (item *Report, err error) {
  121. err = global.MYSQL["rddp"].Where("id = ? ", id).First(&item).Error
  122. if err == utils.ErrNoRow {
  123. err = nil
  124. }
  125. return
  126. }
  127. // GetByReportIds 根据id获取报告
  128. func GetByReportIds(ids []int) (list []*Report, err error) {
  129. err = global.MYSQL["rddp"].Model(Report{}).
  130. Where("id in (?) and state = 2", ids).
  131. Select("id, create_time").
  132. Scan(&list).Error
  133. if err == utils.ErrNoRow {
  134. err = nil
  135. }
  136. return
  137. }
  138. // GetLatestDay 获取最新的晨报
  139. func GetLatestDay() (item *Report, err error) {
  140. err = global.MYSQL["rddp"].Where("state = 2 and classify_name_first= '晨报'").Order("publish_time desc, id desc").First(&item).Error
  141. if err == utils.ErrNoRow {
  142. err = nil
  143. }
  144. return
  145. }
  146. // GetLatestReportsByClassifyIdFirst 查询当前一级分类下,二级分类中,最新的报告
  147. func GetLatestReportsByClassifyIdFirst(classifyIdFirst int, classifyIdSeconds []int) (reportList []*Report, err error) {
  148. sql := `
  149. SELECT t1.id,
  150. t1.classify_id_first,
  151. t1.classify_id_second,
  152. t1.stage,
  153. t1.classify_name_first,
  154. t1.classify_name_second,
  155. t1.publish_time
  156. FROM report t1
  157. INNER JOIN
  158. (
  159. SELECT classify_id_first, classify_id_second, max( publish_time ) AS max_publish_time
  160. FROM report
  161. WHERE
  162. state = 2
  163. AND classify_id_first = ?
  164. AND classify_id_second IN ?
  165. GROUP BY
  166. classify_id_second
  167. ) t2
  168. ON t1.classify_id_second = t2.classify_id_second AND
  169. t1.classify_id_first = t2.classify_id_first AND
  170. t1.publish_time = t2.max_publish_time
  171. WHERE t1.state = 2
  172. AND t1.classify_id_first = ?
  173. AND t1.classify_id_second IN ?`
  174. err = global.MYSQL["rddp"].Raw(sql, classifyIdFirst, classifyIdSeconds, classifyIdFirst, classifyIdSeconds).Scan(&reportList).Error
  175. return
  176. }
  177. // GetReportListByCondition 获取报告列表
  178. func GetReportListByCondition(condition string, pars []interface{}) (list []*Report, err error) {
  179. err = global.MYSQL["rddp"].Select("id").Model(Report{}).Where(condition, pars...).
  180. Scan(&list).Error
  181. return
  182. }
  183. // GetListByClassifyIdSecond 按照二级类型分页查询
  184. func GetListByClassifyIdSecond(classifyIdSecond int, offset, limit int) (reportList []*Report, err error) {
  185. err = global.MYSQL["rddp"].Model(Report{}).
  186. Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time, author, video_name, video_url, video_play_seconds, abstract").
  187. Where("classify_id_second = ? and state = 2 ", classifyIdSecond).
  188. Order("publish_time desc, id desc").
  189. Offset(offset).
  190. Limit(limit).
  191. Scan(&reportList).Error
  192. if err == utils.ErrNoRow {
  193. err = nil
  194. }
  195. return
  196. }
  197. // GetListCountByClassifyIdSecond 按照二级分类总条数
  198. func GetListCountByClassifyIdSecond(classifyIdSecond int) (total int64, err error) {
  199. err = global.MYSQL["rddp"].Model(Report{}).
  200. Where("classify_id_second=? and state = 2 ", classifyIdSecond).Count(&total).Error
  201. if err == utils.ErrNoRow {
  202. err = nil
  203. }
  204. return
  205. }
  206. // GetReportList 获取报告列表
  207. func GetReportList(condition string, pars []interface{}, offset, limit int) (list []*Report, err error) {
  208. err = global.MYSQL["rddp"].Model(Report{}).
  209. Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time, author, create_time, video_url, video_name, video_play_seconds, abstract").
  210. Where(condition, pars...).
  211. Order("publish_time desc, id desc").
  212. Offset(offset).
  213. Limit(limit).
  214. Scan(&list).Error
  215. return
  216. }
  217. // GetReportListCount 获取报告总数
  218. func GetReportListCount(condition string, pars []interface{}) (total int64, err error) {
  219. err = global.MYSQL["rddp"].Model(Report{}).Where(condition, pars...).
  220. Count(&total).Error
  221. return
  222. }
  223. // GetReportCollectListByPermission 根据权限相关的分类查询报告和章节
  224. func GetReportCollectListByPermission(classifyIdSeconds []int, typeIds []int, offset, limit int) (list []*response.ReportCollectListItem, err error) {
  225. sql := `( SELECT
  226. id AS report_id,
  227. 0 AS report_chapter_id,
  228. classify_id_first,
  229. classify_id_second,
  230. classify_name_first,
  231. classify_name_second,
  232. 0 as report_chapter_type_id,
  233. title,
  234. content_sub,
  235. publish_time
  236. FROM
  237. report
  238. WHERE
  239. classify_name_first != "晨报"
  240. AND classify_name_first != "周报"
  241. AND classify_id_second in ?
  242. AND state = 2
  243. )
  244. UNION
  245. ( SELECT
  246. report_id,
  247. report_chapter_id,
  248. classify_id_first,
  249. 0 as classify_id_second,
  250. classify_name_first,
  251. null as classify_name_second,
  252. type_id as report_chapter_type_id,
  253. title,
  254. content_sub,
  255. publish_time
  256. FROM
  257. report_chapter
  258. WHERE
  259. publish_state = 2
  260. AND type_id in ?
  261. )
  262. ORDER BY publish_time DESC, report_id desc LIMIT ? OFFSET ?
  263. `
  264. err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds, typeIds, limit, offset).Scan(&list).Error
  265. return
  266. }
  267. // GetReportCollectCountByPermission 查询汇总报告总页数
  268. func GetReportCollectCountByPermission(classifyIdSeconds []int, typeIds []int) (total int64, err error) {
  269. sql := `select count(*) from ( ( SELECT
  270. id AS report_id,
  271. 0 AS report_chapter_id
  272. FROM
  273. report
  274. WHERE
  275. classify_name_first != "晨报"
  276. AND classify_name_first != "周报"
  277. AND classify_id_second in ?
  278. AND state = 2
  279. )
  280. UNION
  281. ( SELECT
  282. report_id,
  283. report_chapter_id
  284. FROM
  285. report_chapter
  286. WHERE
  287. publish_state = 2
  288. AND type_id in ?
  289. )
  290. ) as ru
  291. `
  292. err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds, typeIds).Count(&total).Error
  293. return
  294. }
  295. // GetReportByOldReportId 根据老后台的research_report_id查找新的报告ID
  296. func GetReportByOldReportId(oldReportId uint64) (item *Report, err error) {
  297. err = global.MYSQL["rddp"].Model(Report{}).
  298. Where("old_report_id=?", oldReportId).First(&item).Error
  299. if err == utils.ErrNoRow {
  300. err = nil
  301. }
  302. return
  303. }
  304. // GetLatestReportByPermission 根据权限相关的分类查询最新的三篇专栏报告
  305. func GetLatestReportByPermission(classifyIdSeconds []int) (list []*pc.LatestReport, err error) {
  306. sql := `SELECT
  307. id AS report_id,
  308. 0 AS report_chapter_id,
  309. classify_id_first,
  310. classify_id_second,
  311. classify_name_first,
  312. classify_name_second,
  313. 0 as report_chapter_type_id,
  314. title,
  315. content_sub,
  316. stage,
  317. publish_time
  318. FROM
  319. report
  320. WHERE
  321. classify_name_first != "晨报"
  322. AND classify_name_first != "周报"
  323. AND classify_id_second in ?
  324. AND state = 2
  325. ORDER BY publish_time DESC LIMIT 3
  326. `
  327. err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds).Scan(&list).Error
  328. return
  329. }
  330. // GetLatestReport 获取最新专栏信息
  331. func GetLatestReport() (list *pc.LatestReport, err error) {
  332. sql := `SELECT
  333. r.id AS report_id,
  334. 0 AS report_chapter_id,
  335. classify_id_first,
  336. classify_id_second,
  337. classify_name_first,
  338. classify_name_second,
  339. 0 as report_chapter_type_id,
  340. title,
  341. content_sub,
  342. stage,
  343. publish_time
  344. FROM
  345. report AS r
  346. INNER JOIN
  347. classify AS c
  348. WHERE
  349. r.classify_name_first = c.classify_name
  350. AND c.show_type = 2
  351. AND c.is_show = 1
  352. AND state = 2
  353. ORDER BY publish_time DESC LIMIT 1
  354. `
  355. err = global.MYSQL["rddp"].Raw(sql).First(&list).Error
  356. return
  357. }
  358. func GetLatestReportByClassifyName(firstName string, secondId int) (items *pc.LatestReportBanner, err error) {
  359. sql := `SELECT
  360. a.id AS report_id,
  361. classify_name_second,
  362. classify_id_second,
  363. classify_name_first,
  364. classify_id_first,
  365. author,
  366. stage,
  367. report_author,
  368. vip_title
  369. FROM
  370. report as a
  371. JOIN classify as b
  372. WHERE
  373. a.state = 2
  374. AND a.classify_name_first = "%v"
  375. AND a.classify_id_second = %v
  376. AND a.classify_id_second = b.id
  377. ORDER BY
  378. publish_time DESC `
  379. sql = fmt.Sprintf(sql, firstName, secondId)
  380. err = global.MYSQL["rddp"].Raw(sql).First(&items).Error
  381. return
  382. }
  383. // GetCommoditiesReportCollectListByPermission 根据权限相关的分类查询大宗商品报告
  384. func GetCommoditiesReportCollectListByPermission(classifyIdSeconds []int, offset, limit int) (list []*Report, err error) {
  385. sql := ` SELECT
  386. id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time, author, create_time, video_url, video_name, video_play_seconds, abstract
  387. FROM
  388. report
  389. WHERE
  390. classify_name_first != "晨报"
  391. AND classify_name_first != "周报"
  392. AND classify_id_second in ?
  393. AND state = 2
  394. ORDER BY publish_time DESC, id desc LIMIT ? OFFSET ?
  395. `
  396. err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds, limit, offset).Scan(&list).Error
  397. return
  398. }
  399. // GetCommoditiesReportCollectCountByPermission 查询大宗商品报告总页数
  400. func GetCommoditiesReportCollectCountByPermission(classifyIdSeconds []int) (total int64, err error) {
  401. sql := `select count(*)
  402. FROM
  403. report
  404. WHERE
  405. classify_name_first != "晨报"
  406. AND classify_name_first != "周报"
  407. AND classify_id_second in ?
  408. AND state = 2
  409. `
  410. err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds).Count(&total).Error
  411. return
  412. }
  413. // GetListByReportIds 根据IDs获取列表
  414. func GetListByReportIds(reportIds []int) (list []*Report, err error) {
  415. var where string
  416. where = `state = 2`
  417. if len(reportIds) > 0 {
  418. where += ` AND id IN (?)`
  419. }
  420. err = global.MYSQL["rddp"].Model(Report{}).
  421. Where(where, reportIds).
  422. Order("id asc").
  423. Scan(&list).Error
  424. return
  425. }