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

package report

import (
	"fmt"
	"hongze/hongze_yb/global"
	"hongze/hongze_yb/models/response"
	"hongze/hongze_yb/models/response/pc"
	"hongze/hongze_yb/utils"
	"strings"
)

// 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 IN (2, 6) 
	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 IN (2, 6) 
	 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 IN (2, 6) 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 IN (2, 6) ", 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 IN (2, 6) ", 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 IN (2, 6) ", 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 IN (2, 6) ", 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 IN (2, 6) ", 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 IN (2, 6) ", classifyIdFirst).
		Count(&total).Error
	return
}

// GetLatestByReportIds 根据ids获取报告
func GetLatestByReportIds(ids []int) (item *Report, err error) {
	err = global.MYSQL["rddp"].Where("id in ? and state IN (2, 6)", 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 IN (2, 6)", 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 IN (2, 6)", 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 IN (2, 6) 
	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 IN (2, 6) 
	 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 IN (2, 6) ", 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 IN (2, 6) ", 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,classify_id_third, classify_name_third, title, stage, publish_time, author, create_time, video_url, video_name, video_play_seconds, abstract,has_chapter,head_img,end_img,head_resource_id,end_resource_id").
		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 IN (2, 6)
	)
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 IN (2, 6)
        )
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
}

// GetReportByOldReportId 根据老后台的research_report_id查找新的报告ID
func GetReportByOldReportId(oldReportId uint64) (item *Report, err error) {
	err = global.MYSQL["rddp"].Model(Report{}).
		Where("old_report_id=?", oldReportId).First(&item).Error
	if err == utils.ErrNoRow {
		err = nil
	}
	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 IN (2, 6)
	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
r.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 AS r
	INNER JOIN
	classify AS c
WHERE
	r.classify_name_first = c.classify_name
	AND c.show_type = 2
	AND c.is_show = 1
    AND c.enabled = 1
	AND state IN (2, 6)
	ORDER BY publish_time DESC LIMIT 1
	`
	err = global.MYSQL["rddp"].Raw(sql).First(&list).Error
	return
}

func GetLatestReportByClassifyName(firstName string, secondId int) (items *pc.LatestReportBanner, err error) {
	sql := `SELECT
	a.id AS report_id,
	classify_name_second,
	classify_id_second,
	classify_name_first,
	classify_id_first,
	author,
	stage,
	report_author,
	vip_title 
FROM
	report as a
	JOIN classify as b
WHERE
	a.state IN (2, 6) 
	AND a.classify_name_first = "%v" 
	AND a.classify_id_second = %v 
	AND a.classify_id_second = b.id
ORDER BY
	publish_time DESC `
	sql = fmt.Sprintf(sql, firstName, secondId)
	err = global.MYSQL["rddp"].Raw(sql).First(&items).Error
	return
}

// GetCommoditiesReportCollectListByPermission 根据权限相关的分类查询大宗商品报告
func GetCommoditiesReportCollectListByPermission(classifyIdSeconds []int, offset, limit int) (list []*Report, err error) {
	sql := ` 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  
FROM
	report
WHERE
	classify_name_first != "晨报" 
	AND classify_name_first != "周报" 
	AND classify_id_second in ?
	AND state IN (2, 6)
	ORDER BY publish_time DESC, id desc LIMIT ? OFFSET ?
	`
	err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds, limit, offset).Scan(&list).Error
	return
}

// GetCommoditiesReportCollectCountByPermission 查询大宗商品报告总页数
func GetCommoditiesReportCollectCountByPermission(classifyIdSeconds []int) (total int64, err error) {
	sql := `select count(*) 
FROM
        report
WHERE
        classify_name_first != "晨报" 
        AND classify_name_first != "周报" 
        AND classify_id_second in ?
        AND state IN (2, 6)
        
	`
	err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds).Count(&total).Error
	return
}

// GetListByReportIds 根据IDs获取列表
func GetListByReportIds(reportIds []int) (list []*Report, err error) {
	var where string
	where = `state IN (2, 6)`
	if len(reportIds) > 0 {
		where += ` AND id IN (?)`
	}

	err = global.MYSQL["rddp"].Model(Report{}).
		Where(where, reportIds).
		Order("id asc").
		Scan(&list).Error
	return
}

// GetReportCollectListByPermissionV1
// @Description: 根据权限相关的分类查询报告和章节
// @author: Roc
// @datetime 2024-06-21 10:24:14
// @param chartPermissionId int
// @param classifyIdSeconds []int
// @param offset int
// @param limit int
// @return list []*response.ReportCollectListItem
// @return err error
func GetReportCollectListByPermissionV1(chartPermissionId int, firstClassifyIdList, secondClassifyIdList, thirdClassifyIdList []int, offset, limit int) (list []*response.ReportCollectListItem, err error) {
	conditionList := make([]string, 0)
	pars := make([]interface{}, 0)
	if len(firstClassifyIdList) > 0 {
		conditionList = append(conditionList, "classify_id_first in ?")
		pars = append(pars, firstClassifyIdList)
	}
	if len(secondClassifyIdList) > 0 {
		conditionList = append(conditionList, "classify_id_second in ?")
		pars = append(pars, secondClassifyIdList)
	}
	if len(thirdClassifyIdList) > 0 {
		conditionList = append(conditionList, "classify_id_third in ?")
		pars = append(pars, thirdClassifyIdList)
	}

	condition := " classify_id_first = 0 "
	if len(conditionList) > 0 {
		condition = strings.Join(conditionList, " OR ")
	}

	pars = append(pars, chartPermissionId, limit, offset)

	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
	has_chapter != 1 
	AND ( ` + condition + ` )
	AND state IN (2, 6)
	)
UNION
	
( SELECT
a.report_id,
a.report_chapter_id,
a.classify_id_first,
0 as classify_id_second,
a.classify_name_first,
null as classify_name_second,
a.type_id as report_chapter_type_id,
a.title,
a.content_sub,
a.publish_time 
FROM
	report_chapter AS a 
JOIN report_chapter_permission_mapping AS b on a.report_chapter_id=b.report_chapter_id
WHERE
	a.publish_state = 2
AND b.chart_permission_id = ?
	)
	ORDER BY publish_time DESC, report_id desc LIMIT ? OFFSET ?
	`
	err = global.MYSQL["rddp"].Raw(sql, pars...).Scan(&list).Error
	return
}

// GetReportCollectCountByPermission 查询汇总报告总页数
func GetReportCollectCountByPermissionV1(chartPermissionId int, classifyIdSeconds []int) (total int64, err error) {
	sql := `select count(*) from ( (( SELECT
id AS report_id,
0 AS report_chapter_id
FROM
	report
WHERE
	has_chapter != 1 
	AND classify_id_second in ?
	AND state IN (2, 6)
	)
UNION
	
( SELECT 
a.report_id,
a.report_chapter_id
FROM
	report_chapter AS a 
JOIN report_chapter_permission_mapping AS b on a.report_chapter_id=b.report_chapter_id
WHERE
	a.publish_state = 2
AND b.chart_permission_id = ?
	)
)
	
) as ru
	`
	err = global.MYSQL["rddp"].Raw(sql, classifyIdSeconds, chartPermissionId).Count(&total).Error
	return
}

// GetLatestReportByClassifyId
// @Description: 根据分类id获取最近一期报告
// @author: Roc
// @datetime 2024-06-24 14:10:05
// @param firstId int
// @param secondId int
// @param thirdId int
// @return items *pc.LatestReportBanner
// @return err error
func GetLatestReportByClassifyId(firstId, secondId, thirdId int) (items *pc.LatestReportBanner, err error) {
	sql := `SELECT
	a.id AS report_id,
	classify_name_second,
	classify_id_second,
	classify_name_first,
	classify_id_first,
	author,
	stage
FROM
	report as a
WHERE
	a.state IN (2, 6) 
	AND a.classify_id_first = ? 
	AND a.classify_id_second = ? 
	AND a.classify_id_third = ? 
ORDER BY
	publish_time DESC `
	err = global.MYSQL["rddp"].Raw(sql, firstId, secondId, thirdId).First(&items).Error
	return
}