package report import ( "hongze/hongze_yb/global" "hongze/hongze_yb/models/response" "hongze/hongze_yb/models/response/pc" "hongze/hongze_yb/utils" ) // GetLatestClassReportsByClassifyIdSeconds 根据用户已购买的分类权限查询个分类最新的报告 func GetLatestClassReportsByClassifyIdSeconds(classifyIdSeconds []int) (reportList []*Report, err error) { sql := `SELECT t1.id, t1.classify_id_first, t1.classify_id_second, t1.stage, t1.title, t1.classify_name_first, t1.classify_name_second, t1.publish_time FROM report t1 INNER JOIN ( SELECT classify_id_first, max( publish_time ) AS max_publish_time FROM report WHERE state = 2 AND classify_name_first !="权益研报" AND classify_id_second IN ? GROUP BY classify_id_first ) t2 ON t1.classify_id_first = t2.classify_id_first AND t1.publish_time = t2.max_publish_time WHERE t1.state = 2 AND t1.classify_name_first !="权益研报" AND t1.classify_id_second IN ?` err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds, classifyIdSeconds).Scan(&reportList).Error return } // GetReportsByClassifyIdSecondsAndDate 根据时间和报告分类筛选出合适的记录 func GetReportsByClassifyIdSecondsAndDate( classifyIdSeconds []int, publishTime string) (reportList []*Report, err error) { err = global.MYSQL["rddp"].Model(Report{}). Select("id, classify_name_first"). Where("classify_id_second in (?) and state = 2 and publish_time > ? ", classifyIdSeconds, publishTime).Scan(&reportList).Error return } // GetListByIDsAndClassifyIdFirst 分页查询 func GetListByIDsAndClassifyIdFirst( ids []int, classifyIdFirst int, offset , limit int) (reportList []*Report, err error) { err = global.MYSQL["rddp"].Model(Report{}). Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time"). Where("id in (?) and classify_id_first=? and state = 2 ", ids, classifyIdFirst). Order("publish_time desc, id desc"). Offset(offset). Limit(limit). Scan(&reportList).Error return } // GetListCountByIDsAndClassifyIdFirst 分页查询 func GetListCountByIDsAndClassifyIdFirst( ids []int, classifyIdFirst int) (total int64, err error) { err = global.MYSQL["rddp"].Model(Report{}). Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time"). Where("id in (?) and classify_id_first=? and state = 2 ", ids, classifyIdFirst). Count(&total).Error return } // GetListByClassifyIdSeconds 分页查询 func GetListByClassifyIdSeconds( classifyIdSeconds []int, offset , limit int) (reportList []*Report, err error) { err = global.MYSQL["rddp"].Model(Report{}). Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time"). Where("classify_id_second in (?) and state = 2 ", classifyIdSeconds). Order("publish_time desc, id desc"). Offset(offset). Limit(limit). Scan(&reportList).Error return } // GetListCountByClassifyIdSeconds func GetListCountByClassifyIdSeconds( classifyIdSeconds []int) (total int64, err error) { err = global.MYSQL["rddp"].Model(Report{}). Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time"). Where("classify_id_second in (?) and state = 2 ", classifyIdSeconds). Count(&total).Error return } // GetListByClassifyIdFirst 按照类型分页查询 func GetListByClassifyIdFirst(classifyIdFirst int, offset , limit int) (reportList []*Report, err error) { err = global.MYSQL["rddp"].Model(Report{}). Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time"). Where("classify_id_first=? and state = 2 ", classifyIdFirst). Order("publish_time desc, id desc"). Offset(offset). Limit(limit). Scan(&reportList).Error return } // GetListCountByClassifyIdFirst 按照类型查询报告总数 func GetListCountByClassifyIdFirst(classifyIdFirst int) (total int64, err error) { err = global.MYSQL["rddp"].Model(Report{}). Select("id, classify_id_first, classify_name_first, classify_id_second, classify_name_second, title, stage, publish_time"). Where("classify_id_first=? and state = 2 ", classifyIdFirst). Count(&total).Error return } // GetLatestByReportIds 根据ids获取报告 func GetLatestByReportIds(ids []int) (item *Report, err error) { err = global.MYSQL["rddp"].Where("id in ? and state = 2", ids).Order("publish_time desc, id desc").First(&item).Error if err == utils.ErrNoRow { err = nil } return } // GetPublishByReportId 根据id获取报告 func GetPublishByReportId(id int) (item *Report, err error) { err = global.MYSQL["rddp"].Where("id = ? and state = 2", id).First(&item).Error if err == utils.ErrNoRow { err = nil } return } // GetByReportId 根据id获取报告 func GetByReportId(id int) (item *Report, err error) { err = global.MYSQL["rddp"].Where("id = ? ", id).First(&item).Error if err == utils.ErrNoRow { err = nil } return } // GetByReportIds 根据id获取报告 func GetByReportIds(ids []int) (list []*Report, err error) { err = global.MYSQL["rddp"].Model(Report{}).Where("id in (?) and state = 2", ids).Select("id, create_time").Scan(&list).Error if err == utils.ErrNoRow { err = nil } return } // GetLatestDay 获取最新的晨报 func GetLatestDay() (item *Report, err error) { err = global.MYSQL["rddp"].Where("state = 2 and classify_name_first= '晨报'").Order("publish_time desc, id desc").First(&item).Error if err == utils.ErrNoRow { err = nil } return } // GetLatestReportsByClassifyIdFirst 查询当前一级分类下,二级分类中,最新的报告 func GetLatestReportsByClassifyIdFirst(classifyIdFirst int, classifyIdSeconds []int) (reportList []*Report, err error) { sql := ` SELECT t1.id, t1.classify_id_first, t1.classify_id_second, t1.stage, t1.classify_name_first, t1.classify_name_second, t1.publish_time FROM report t1 INNER JOIN ( SELECT classify_id_first, classify_id_second, max( publish_time ) AS max_publish_time FROM report WHERE state = 2 AND classify_id_first = ? AND classify_id_second IN ? GROUP BY classify_id_second ) t2 ON t1.classify_id_second = t2.classify_id_second AND t1.classify_id_first = t2.classify_id_first AND t1.publish_time = t2.max_publish_time WHERE t1.state = 2 AND t1.classify_id_first = ? AND t1.classify_id_second IN ?` err = global.MYSQL["rddp"].Raw(sql, classifyIdFirst, classifyIdSeconds, classifyIdFirst, classifyIdSeconds).Scan(&reportList).Error return } // GetReportListByCondition 获取报告列表 func GetReportListByCondition(condition string, pars []interface{}) (list []*Report, err error) { err = global.MYSQL["rddp"].Select("id").Model(Report{}).Where(condition, pars...). Scan(&list).Error return } // GetListByClassifyIdSecond 按照二级类型分页查询 func GetListByClassifyIdSecond(classifyIdSecond int, offset , limit int) (reportList []*Report, err error) { err = global.MYSQL["rddp"].Model(Report{}). 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"). Where("classify_id_second = ? and state = 2 ", classifyIdSecond). Order("publish_time desc, id desc"). Offset(offset). Limit(limit). Scan(&reportList).Error if err == utils.ErrNoRow { err = nil } return } // GetListCountByClassifyIdSecond 按照二级分类总条数 func GetListCountByClassifyIdSecond(classifyIdSecond int) (total int64, err error) { err = global.MYSQL["rddp"].Model(Report{}). Where("classify_id_second=? and state = 2 ", classifyIdSecond).Count(&total).Error if err == utils.ErrNoRow { err = nil } return } // GetReportList 获取报告列表 func GetReportList(condition string, pars []interface{}, offset , limit int) (list []*Report, err error) { err = global.MYSQL["rddp"].Model(Report{}). 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"). Where(condition, pars...). Order("publish_time desc, id desc"). Offset(offset). Limit(limit). Scan(&list).Error return } // GetReportListCount 获取报告总数 func GetReportListCount(condition string, pars []interface{}) (total int64, err error) { err = global.MYSQL["rddp"].Model(Report{}).Where(condition, pars...). Count(&total).Error return } // GetReportCollectListByPermission 根据权限相关的分类查询报告和章节 func GetReportCollectListByPermission(classifyIdSeconds []int, typeIds []int, offset , limit int) (list []*response.ReportCollectListItem, err error) { sql := `( SELECT id AS report_id, 0 AS report_chapter_id, classify_id_first, classify_id_second, classify_name_first, classify_name_second, 0 as report_chapter_type_id, title, content_sub, publish_time FROM report WHERE classify_name_first != "晨报" AND classify_name_first != "周报" AND classify_id_second in ? AND state = 2 ) UNION ( SELECT report_id, report_chapter_id, classify_id_first, 0 as classify_id_second, classify_name_first, null as classify_name_second, type_id as report_chapter_type_id, title, content_sub, publish_time FROM report_chapter WHERE publish_state = 2 AND type_id in ? ) ORDER BY publish_time DESC, report_id desc LIMIT ? OFFSET ? ` err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds, typeIds, limit, offset).Scan(&list).Error return } // GetReportCollectCountByPermission 查询汇总报告总页数 func GetReportCollectCountByPermission(classifyIdSeconds []int, typeIds []int) (total int64, err error) { sql := `select count(*) from ( ( SELECT id AS report_id, 0 AS report_chapter_id FROM report WHERE classify_name_first != "晨报" AND classify_name_first != "周报" AND classify_id_second in ? AND state = 2 ) UNION ( SELECT report_id, report_chapter_id FROM report_chapter WHERE publish_state = 2 AND type_id in ? ) ) as ru ` err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds, typeIds).Count(&total).Error return } // GetLatestReportByPermission 根据权限相关的分类查询最新的三篇专栏报告 func GetLatestReportByPermission(classifyIdSeconds []int) (list []*pc.LatestReport, err error) { sql := `SELECT id AS report_id, 0 AS report_chapter_id, classify_id_first, classify_id_second, classify_name_first, classify_name_second, 0 as report_chapter_type_id, title, content_sub, stage, publish_time FROM report WHERE classify_name_first != "晨报" AND classify_name_first != "周报" AND classify_id_second in ? AND state = 2 ORDER BY publish_time DESC LIMIT 3 ` err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds).Scan(&list).Error return } // GetLatestReport 获取最新专栏信息 func GetLatestReport() (list *pc.LatestReport, err error) { sql := `SELECT id AS report_id, 0 AS report_chapter_id, classify_id_first, classify_id_second, classify_name_first, classify_name_second, 0 as report_chapter_type_id, title, content_sub, stage, publish_time FROM report WHERE classify_name_first != "晨报" AND classify_name_first != "周报" AND state = 2 ORDER BY publish_time DESC LIMIT 1 ` err = global.MYSQL["rddp"].Raw(sql).First(&list).Error return }