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"
)

// 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
}

// 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 = 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
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 state = 2
	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 = 2 
	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 = 2
	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 = 2
        
	`
	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 = 2`
	if len(reportIds) > 0 {
		where += ` AND id IN (?)`
	}

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