hongze_yb/models/tables/rddp/report_chapter/query.go

package report_chapter

import (
	"fmt"
	"hongze/hongze_yb/global"
	"hongze/hongze_yb/utils"
)

func GetLatestChaptersByTypeIdsAndClass(typeIds []int, classifyNameFirst string) (list []*ReportChapter, err error) {
	sql := `SELECT
	DISTINCT report_id
FROM
	report_chapter
WHERE
	type_id IN (?) 
	AND publish_state = 2 
	AND is_edit = 1
	AND report_type = ?
ORDER BY
	publish_time desc, report_chapter_id desc limit 30
`
	err = global.MYSQL["rddp"].Model(ReportChapter{}).Raw(sql, typeIds, classifyNameFirst).Scan(&list).Error
	return
}

// GetListByReportId 根据报告ID获取章节列表
func GetListByReportId(reportId int, classifyNameFirst string) (list []*ReportChapter, err error) {
	var where string
	if classifyNameFirst == "周报" {
		where = "report_id = ? AND is_edit = 1 AND publish_state = 2"
	} else {
		where = "report_id = ? AND publish_state = 2"
	}

	err = global.MYSQL["rddp"].Model(ReportChapter{}).
		Select("report_chapter_id, report_id, type_id, type_name, abstract, title, author, publish_time, trend,video_url,video_name,video_play_seconds,video_size").
		Where(where, reportId).
		Order("sort asc, report_chapter_id asc").
		Scan(&list).Error
	return
}

// GetListByReportIdTypeIds 根据报告ID、章节类型ID获取章节列表
func GetListByReportIdTypeIds(reportId int, typeIds []int, classifyNameFirst string) (list []*ReportChapter, err error) {
	var where string
	if classifyNameFirst == "周报" {
		where = "report_id = ? AND type_id in (?) AND is_edit = 1 AND publish_state = 2 "
	} else {
		where = "report_id = ? AND type_id in (?) AND publish_state = 2 "
	}

	err = global.MYSQL["rddp"].Model(ReportChapter{}).
		Select("report_chapter_id, report_id, type_id, type_name, abstract, title, author, publish_time").
		Where(where, reportId, typeIds).
		Order("sort asc, report_chapter_id asc").
		Scan(&list).Error
	return
}

// GetContentById 根据ID获取章节详情
func GetContentById(id int, typeIds []int) (info *ReportChapter, err error) {
	err = global.MYSQL["rddp"].Model(ReportChapter{}).Select("report_chapter_id, report_id,  is_edit, classify_name_first, classify_id_first, content, trend, type_id, type_name, abstract, title, author, stage, publish_time, content, content_sub, video_url, video_name, video_play_seconds, video_size, report_type, video_size,report_layout").
		Where("report_chapter_id = ? and type_id in (?) AND publish_state = 2 ", id, typeIds).
		First(&info).Error
	if err == utils.ErrNoRow {
		err = nil
	}
	return
}

// GetReportIdsByTypeIdsAndClass 根据章节ID获取报告ID
func GetReportIdsByTypeIdsAndClass(typeIds []int, classifyNameFirst string) (reportIds []int, err error) {
	var list []*ReportChapter
	err = global.MYSQL["rddp"].Model(ReportChapter{}).Select("DISTINCT report_id").Where("type_id in (?) and publish_state = 2 AND is_edit = 1 AND classify_name_first= ? ", typeIds, classifyNameFirst).Scan(&list).Error
	if err != nil {
		return
	}
	for _, v := range list {
		reportIds = append(reportIds, v.ReportId)
	}
	return
}

// GetTypeIdById 根据ID获取章节类型
func GetTypeIdById(id int) (info *ReportChapter, err error) {
	err = global.MYSQL["rddp"].Model(ReportChapter{}).Select("report_chapter_id, report_id, type_id").
		Where("report_chapter_id = ? AND publish_state = 2 ", id).
		First(&info).Error
	if err == utils.ErrNoRow {
		err = nil
	}
	return
}

// GetByTypeIdsAndReportIds 根据章节ID和ReportIds查询报告
func GetByTypeIdsAndReportIds(typeIds []int, reportIds []int, classifyNameFirst string) (list []*ReportChapter, err error) {
	var where string
	if classifyNameFirst == "周报" {
		where = "report_id in (?) AND type_id in (?) AND is_edit = 1 AND publish_state = 2 "
	} else {
		where = "report_id in (?) AND type_id in (?) AND publish_state = 2 "
	}

	err = global.MYSQL["rddp"].Model(ReportChapter{}).
		Select("report_id, type_id, report_chapter_id, classify_name_first, classify_id_first, video_url, video_name, video_play_seconds, video_size, sort").
		Where(where, reportIds, typeIds).
		Order("sort asc, report_chapter_id asc").
		Scan(&list).Error
	if err != nil {
		return
	}
	for _, v := range list {
		reportIds = append(reportIds, v.ReportId)
	}
	return
}

// GetChapterByReportIdTypeId 根据报告ID和章节类型ID查找章节
func GetChapterByReportIdTypeId(reportId, typeId int) (chapter *ReportChapter, err error) {
	err = global.MYSQL["rddp"].Model(ReportChapter{}).
		Select("report_id, type_id, report_chapter_id").
		Where("report_id = ? and type_id = ?", reportId, typeId).
		First(&chapter).Error
	if err == utils.ErrNoRow {
		err = nil
	}
	return
}

func GetWeekRecommendList(reportId int, firstName string) (items []*ReportChapter, err error) {
	sql := `SELECT * FROM (SELECT
	a.report_id,
	a.report_chapter_id,
	a.classify_name_first,
	a.title,
	a.stage,
	a.publish_time 
FROM
	report_chapter AS a,
	report AS b
WHERE
	a.publish_state = 2 
	AND a.report_id <> %v
	AND a.classify_name_first = "%v"
	AND a.report_id=b.id
	AND b.state IN (2, 6)
ORDER BY
	publish_time DESC) t
	GROUP BY report_id 
	ORDER BY publish_time desc LIMIT 3 `
	sql = fmt.Sprintf(sql, reportId, firstName)
	err = global.MYSQL["rddp"].Raw(sql).Scan(&items).Error
	return
}

func GetLatestChapterByClassifyName(firstName string) (items *ReportChapter, err error) {
	sql := `SELECT
	report_id,
	classify_name_first,
	classify_id_first,
	stage 
FROM
	report_chapter 
WHERE
	publish_state = 2 
	AND classify_name_first = "%v" 
ORDER BY
	publish_time DESC `
	sql = fmt.Sprintf(sql, firstName)
	err = global.MYSQL["rddp"].Raw(sql).First(&items).Error
	return
}

// GetItemById 主键获取章节
func GetItemById(chapterId int) (item *ReportChapter, err error) {
	err = global.MYSQL["rddp"].Model(ReportChapter{}).
		Where("report_chapter_id = ? AND publish_state = 2", chapterId).
		First(&item).Error
	return
}

// GetListByChapterIds 根据章节IDs获取列表
func GetListByChapterIds(chapterIds []int) (list []*ReportChapter, err error) {
	var where string
	where = `publish_state = 2`
	if len(chapterIds) > 0 {
		where += ` AND report_chapter_id IN (?)`
	}

	err = global.MYSQL["rddp"].Model(ReportChapter{}).
		//Select("report_id, type_id, report_chapter_id, classify_name_first, classify_id_first, video_url, video_name, video_play_seconds, video_size, sort").
		Where(where, chapterIds).
		Order("sort asc, report_chapter_id asc").
		Scan(&list).Error
	return
}

// GetLatestChapterByMinClassifyId
// @Description: 根据分类id获取最新的章节信息
// @author: Roc
// @datetime 2024-06-24 14:00:46
// @param minClassifyId int
// @return items *ReportChapter
// @return err error
func GetLatestChapterByMinClassifyId(minClassifyId int) (items *ReportChapter, err error) {
	sql := `SELECT
	report_id,
	classify_name_first,
	classify_id_first,
	stage 
FROM
	report_chapter 
WHERE
	publish_state = 2 
	AND classify_id_first = ?
ORDER BY
	publish_time DESC `
	err = global.MYSQL["rddp"].Raw(sql, minClassifyId).First(&items).Error

	return
}

// GetWeekRecommendListV2
// @Description: 获取推荐列表
// @author: Roc
// @datetime 2024-06-24 14:20:09
// @param reportId int
// @param firstId int
// @param secondId int
// @param thirdId int
// @return items []*ReportChapter
// @return err error
func GetWeekRecommendListV2(reportId, minClassifyId int) (items []*ReportChapter, err error) {
	sql := `SELECT * FROM (SELECT
	a.report_id,
	a.report_chapter_id,
	a.classify_name_first,
	a.title,
	a.stage,
	a.publish_time 
FROM
	report_chapter AS a,
	report AS b
WHERE
	a.publish_state = 2 
	AND a.report_id <> ?
	AND a.classify_id_first = ?
	AND a.report_id=b.id
	AND b.state IN (2, 6)
ORDER BY
	publish_time DESC) t
	GROUP BY report_id 
	ORDER BY publish_time desc LIMIT 3 `
	err = global.MYSQL["rddp"].Raw(sql, reportId, minClassifyId).Scan(&items).Error
	return
}

// GetByPermissionIdListAndReportList
// @Description: 根据报告id和品种权限获取所有的章节
// @param permissionIdList
// @param reportIds
// @return list
// @return err
func GetByPermissionIdListAndReportList(permissionIdList, reportIds []int) (list []*ReportChapter, err error) {
	sql := ` SELECT a.report_id, a.type_id, a.report_chapter_id, a.classify_name_first, a.classify_id_first, a.video_url, a.video_name, a.video_play_seconds, a.video_size, a.sort FROM report_chapter a
            INNER JOIN  report_chapter_permission_mapping b ON a.report_chapter_id = b.report_chapter_id
            WHERE a.report_id in (?) AND b.chart_permission_id IN (?) AND a.is_edit = 1 AND a.publish_state = 2 `

	sql += ` order by a.sort asc,a.report_chapter_id asc `
	err = global.MYSQL["rddp"].Raw(sql, reportIds, permissionIdList).Scan(&list).Error

	for _, v := range list {
		reportIds = append(reportIds, v.ReportId)
	}
	return
}