eta_mobile/models/report.go

package models

import (
	"eta/eta_mobile/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"strings"
	"time"
)

// 报告状态
const (
	ReportStateUnpublished = 1 // 未发布
	ReportStatePublished   = 2 // 已发布
	ReportStateWaitSubmit  = 3 // 待提交
	ReportStateWaitApprove = 4 // 审批中
	ReportStateRefused     = 5 // 已驳回
	ReportStatePass        = 6 // 已通过
)

// 报告操作
const (
	ReportOperateAdd           = 1 // 新增报告
	ReportOperateEdit          = 2 // 编辑报告
	ReportOperatePublish       = 3 // 发布报告
	ReportOperateCancelPublish = 4 // 取消发布报告
	ReportOperateSubmitApprove = 5 // 提交审批
	ReportOperateCancelApprove = 6 // 撤回审批
)

type Report struct {
	Id                 int       `orm:"column(id)" description:"报告Id"`
	AddType            int       `description:"新增方式：1:新增报告，2:继承报告"`
	ClassifyIdFirst    int       `description:"一级分类id"`
	ClassifyNameFirst  string    `description:"一级分类名称"`
	ClassifyIdSecond   int       `description:"二级分类id"`
	ClassifyNameSecond string    `description:"二级分类名称"`
	Title              string    `description:"标题"`
	Abstract           string    `description:"摘要"`
	Author             string    `description:"作者"`
	Frequency          string    `description:"频度"`
	CreateTime         string    `description:"创建时间"`
	ModifyTime         time.Time `description:"修改时间"`
	State              int       `description:"1:未发布；2:已发布；3-待提交；4-待审批；5-已驳回；6-已通过"`
	PublishTime        time.Time `description:"发布时间"`
	Stage              int       `description:"期数"`
	MsgIsSend          int       `description:"消息是否已发送，0:否，1:是"`
	ThsMsgIsSend       int       `description:"客户群消息是否已发送，0:否，1:是"`
	Content            string    `description:"内容"`
	VideoUrl           string    `description:"音频文件URL"`
	VideoName          string    `description:"音频文件名称"`
	VideoPlaySeconds   string    `description:"音频播放时长"`
	VideoSize          string    `description:"音频文件大小，单位M"`
	ContentSub         string    `description:"内容前两个章节"`
	ReportCode         string    `description:"报告唯一编码"`
	ReportVersion      int       `description:"1:旧版,2:新版"`
	HasChapter         int       `description:"是否有章节 0-否 1-是"`
	ChapterType        string    `description:"章节类型 day-晨报 week-周报"`
	OldReportId        int       `description:"research_report表ID, 大于0则表示该报告为老后台同步过来的"`
	MsgSendTime        time.Time `description:"模版消息发送时间"`
	AdminId            int       `description:"创建者账号"`
	AdminRealName      string    `description:"创建者姓名"`
	ApproveTime        time.Time `description:"审批时间"`
	ApproveId          int       `description:"审批ID"`
}

type ReportList struct {
	Id                 int                       `description:"报告Id"`
	AddType            int                       `description:"新增方式：1:新增报告，2:继承报告"`
	ClassifyIdFirst    int                       `description:"一级分类id"`
	ClassifyNameFirst  string                    `description:"一级分类名称"`
	ClassifyIdSecond   int                       `description:"二级分类id"`
	ClassifyNameSecond string                    `description:"二级分类名称"`
	Title              string                    `description:"标题"`
	Abstract           string                    `description:"摘要"`
	Author             string                    `description:"作者"`
	Frequency          string                    `description:"频度"`
	CreateTime         string                    `description:"创建时间"`
	ModifyTime         time.Time                 `description:"修改时间"`
	State              int                       `description:"1:未发布；2:已发布；3-待提交；4-待审批；5-已驳回；6-已通过"`
	PublishTime        string                    `description:"发布时间"`
	PrePublishTime     string                    `description:"预发布时间"`
	Stage              int                       `description:"期数"`
	MsgIsSend          int                       `description:"模板消息是否已发送，0:否，1:是"`
	Content            string                    `description:"内容"`
	VideoUrl           string                    `description:"音频文件URL"`
	VideoName          string                    `description:"音频文件名称"`
	VideoPlaySeconds   string                    `description:"音频播放时长"`
	ContentSub         string                    `description:"内容前两个章节"`
	Pv                 int                       `description:"Pv"`
	Uv                 int                       `description:"Uv"`
	ReportCode         string                    `description:"报告唯一编码"`
	ReportVersion      int                       `description:"1:旧版,2:新版"`
	ThsMsgIsSend       int                       `description:"客户群消息是否已发送，0:否，1:是"`
	NeedThsMsg         int                       `description:"是否需要推送客群消息 0-否 1-是"`
	HasChapter         int                       `description:"是否有章节 0-否 1-是"`
	ChapterType        string                    `description:"章节类型 day-晨报 week-周报"`
	ChapterVideoList   []*ReportChapterVideoList `description:"章节音频列表"`
	OldReportId        int                       `description:"research_report表ID, 大于0则表示该报告为老后台同步过来的"`
	MsgSendTime        string                    `description:"模版消息发送时间"`
	CanEdit            bool                      `description:"是否可编辑"`
	Editor             string                    `description:"编辑人"`
	AdminId            int                       `description:"创建者账号"`
	AdminRealName      string                    `description:"创建者姓名"`
	ApproveTime        string                    `description:"审批时间"`
	DetailImgUrl       string                    `description:"报告详情长图地址"`
	DetailPdfUrl       string                    `description:"报告详情PDF地址"`
}

type ReportListResp struct {
	List   []*ReportList
	Paging *paging.PagingItem `description:"分页数据"`
}

func GetReportListCount(condition string, pars []interface{}, companyType string) (count int, err error) {
	//产品权限
	companyTypeSqlStr := ``
	if companyType == "ficc" {
		companyTypeSqlStr = " AND classify_id_first != 40 "
	} else if companyType == "权益" {
		companyTypeSqlStr = " AND classify_id_first = 40 "
	}

	oRddp := orm.NewOrmUsingDB("rddp")
	sql := `SELECT COUNT(1) AS count  FROM report WHERE 1=1 ` + companyTypeSqlStr
	if condition != "" {
		sql += condition
	}
	err = oRddp.Raw(sql, pars).QueryRow(&count)
	return
}

func GetReportList(condition string, pars []interface{}, companyType string, startSize, pageSize int) (items []*ReportList, err error) {
	o := orm.NewOrmUsingDB("rddp")
	//产品权限
	companyTypeSqlStr := ``
	if companyType == "ficc" {
		companyTypeSqlStr = " AND classify_id_first != 40 "
	} else if companyType == "权益" {
		companyTypeSqlStr = " AND classify_id_first = 40 "
	}

	sql := `SELECT *,
        (SELECT COUNT(1) FROM report_view_record AS rvr WHERE rvr.report_id=report.id) AS pv,
        (SELECT COUNT(DISTINCT user_id) FROM report_view_record AS rvr WHERE rvr.report_id=report.id) AS uv
        FROM report WHERE 1=1  ` + companyTypeSqlStr
	if condition != "" {
		sql += condition
	}
	// 排序：1:未发布；2:已发布；3-待提交；4-待审批；5-已驳回；6-已通过
	sql += `ORDER BY FIELD(state,3,1,4,5,6,2), modify_time DESC LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// PublishReport 发布报告
func PublishReport(reportIds []int) (err error) {
	if len(reportIds) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET state=2,publish_time=now(),modify_time=NOW() WHERE id IN (` + utils.GetOrmInReplace(len(reportIds)) + `)`
	_, err = o.Raw(sql).Exec()
	return
}

// PublishCancelReport 取消发布报告
func PublishCancelReport(reportId, state int, publishTimeNullFlag bool) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	var sql string
	if publishTimeNullFlag {
		sql = ` UPDATE report SET state=?, publish_time=null, pre_publish_time=null, pre_msg_send=0 WHERE id =?`
	} else {
		sql = ` UPDATE report SET state=?, pre_publish_time=null, pre_msg_send=0 WHERE id =?`
	}
	_, err = o.Raw(sql, state, reportId).Exec()
	return
}

// 取消发布报告
func PublishCancleReport(reportIds int, publishTimeNullFlag bool) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	var sql string
	if publishTimeNullFlag {
		sql = ` UPDATE report SET state=1, publish_time=null, pre_publish_time=null, pre_msg_send=0 WHERE id =?`
	} else {
		sql = ` UPDATE report SET state=1, pre_publish_time=null, pre_msg_send=0 WHERE id =?`
	}
	_, err = o.Raw(sql, reportIds).Exec()
	return
}

// 删除报告
func DeleteReport(reportIds int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` DELETE FROM report WHERE id =? `
	_, err = o.Raw(sql, reportIds).Exec()
	return
}

type ReportDetail struct {
	Id                 int    `orm:"column(id)" description:"报告Id"`
	AddType            int    `description:"新增方式：1:新增报告，2:继承报告"`
	ClassifyIdFirst    int    `description:"一级分类id"`
	ClassifyNameFirst  string `description:"一级分类名称"`
	ClassifyIdSecond   int    `description:"二级分类id"`
	ClassifyNameSecond string `description:"二级分类名称"`
	Title              string `description:"标题"`
	Abstract           string `description:"摘要"`
	Author             string `description:"作者"`
	Frequency          string `description:"频度"`
	CreateTime         string `description:"创建时间"`
	ModifyTime         string `description:"修改时间"`
	State              int    `description:"1:未发布，2:已发布"`
	PublishTime        string `description:"发布时间"`
	PrePublishTime     string `description:"预发布时间"`
	Stage              int    `description:"期数"`
	MsgIsSend          int    `description:"消息是否已发送，0:否，1:是"`
	PreMsgSend         int    `description:"定时发布成功后是否立即推送模版消息：0否，1是"`
	Content            string `description:"内容"`
	VideoUrl           string `description:"音频文件URL"`
	VideoName          string `description:"音频文件名称"`
	VideoPlaySeconds   string `description:"音频播放时长"`
	ContentSub         string `description:"内容前两个章节"`
	ThsMsgIsSend       int    `description:"客户群消息是否已发送，0:否，1:是"`
	HasChapter         int    `description:"是否有章节 0-否 1-是"`
	ChapterType        string `description:"章节类型 day-晨报 week-周报"`
	ReportCode         string `description:"报告code"`
}

func GetReportById(reportId int) (item *ReportDetail, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM report WHERE id=?`
	err = o.Raw(sql, reportId).QueryRow(&item)
	return
}

func GetReportByIds(reportIds string) (list []*ReportDetail, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM report WHERE id in ` + reportIds
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetSimpleReportByIds 根据报告ID查询报告基本信息
func GetSimpleReportByIds(reportIds []int) (list []*ReportDetail, err error) {
	if len(reportIds) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT id, title FROM report WHERE id IN (` + utils.GetOrmInReplace(len(reportIds)) + `)`
	_, err = o.Raw(sql, reportIds).QueryRows(&list)
	return
}

func GetReportStage(classifyIdFirst, classifyIdSecond int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ``
	if classifyIdSecond > 0 {
		sql = "SELECT MAX(stage) AS max_stage FROM report WHERE classify_id_second=? "
		o.Raw(sql, classifyIdSecond).QueryRow(&count)
	} else {
		sql = "SELECT MAX(stage) AS max_stage FROM report WHERE classify_id_first=? "
		o.Raw(sql, classifyIdFirst).QueryRow(&count)
	}
	return
}

func GetReportStageEdit(classifyIdFirst, classifyIdSecond, reportId int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ``
	if classifyIdSecond > 0 {
		sql = "SELECT MAX(stage) AS max_stage FROM report WHERE classify_id_second=? AND id<>? "
		o.Raw(sql, classifyIdSecond, reportId).QueryRow(&count)
	} else {
		sql = "SELECT MAX(stage) AS max_stage FROM report WHERE classify_id_first=? AND id<>? "
		o.Raw(sql, classifyIdFirst, reportId).QueryRow(&count)
	}
	return
}

type PublishReq struct {
	ReportIds string `description:"报告id,多个用英文逗号隔开"`
	ReportUrl string `description:"报告Url"`
}

type PublishCancelReq struct {
	ReportIds int `description:"报告id"`
}

type DeleteReq struct {
	ReportIds int `description:"报告id"`
}

type AddReq struct {
	AddType            int    `description:"新增方式：1:新增报告，2:继承报告"`
	ClassifyIdFirst    int    `description:"一级分类id"`
	ClassifyNameFirst  string `description:"一级分类名称"`
	ClassifyIdSecond   int    `description:"二级分类id"`
	ClassifyNameSecond string `description:"二级分类名称"`
	Title              string `description:"标题"`
	Abstract           string `description:"摘要"`
	Author             string `description:"作者"`
	Frequency          string `description:"频度"`
	State              int    `description:"状态：1:未发布，2:已发布"`
	Content            string `description:"内容"`
	CreateTime         string `description:"创建时间"`
	ReportVersion      int    `description:"1:旧版,2:新版"`
}

type PrePublishReq struct {
	ReportId       int    `description:"报告id"`
	PrePublishTime string `description:"预发布时间"`
	PreMsgSend     int    `description:"定时发布成功后是否立即推送模版消息：0否，1是"`
	ReportUrl      string `description:"报告Url"`
}

type AddResp struct {
	ReportId   int64  `description:"报告id"`
	ReportCode string `description:"报告code"`
}

func AddReport(item *Report) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	lastId, err = o.Insert(item)
	return
}

type EditReq struct {
	ReportId           int64  `description:"报告id"`
	ClassifyIdFirst    int    `description:"一级分类id"`
	ClassifyNameFirst  string `description:"一级分类名称"`
	ClassifyIdSecond   int    `description:"二级分类id"`
	ClassifyNameSecond string `description:"二级分类名称"`
	Title              string `description:"标题"`
	Abstract           string `description:"摘要"`
	Author             string `description:"作者"`
	Frequency          string `description:"频度"`
	State              int    `description:"状态：1:未发布，2:已发布"`
	Content            string `description:"内容"`
	CreateTime         string `description:"创建时间"`
}

type EditResp struct {
	ReportId   int64  `description:"报告id"`
	ReportCode string `description:"报告code"`
}

func EditReport(item *Report, reportId int64) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report
			SET
			  classify_id_first =?,
			  classify_name_first = ?,
			  classify_id_second = ?,
			  classify_name_second = ?,
			  title = ?,
			  abstract = ?,
			  author = ?,
			  frequency = ?,
			  state = ?,
			  content = ?,
			  content_sub = ?,
			  stage =?,
			  create_time = ?,
			  modify_time = ?
			WHERE id = ? `
	_, err = o.Raw(sql, item.ClassifyIdFirst, item.ClassifyNameFirst, item.ClassifyIdSecond, item.ClassifyNameSecond, item.Title,
		item.Abstract, item.Author, item.Frequency, item.State, item.Content, item.ContentSub, item.Stage, item.CreateTime, time.Now(), reportId).Exec()
	return
}

type ReportDetailReq struct {
	ReportId int `description:"报告id"`
}

type ClassifyIdDetailReq struct {
	ClassifyIdFirst  int `description:"报告一级分类id"`
	ClassifyIdSecond int `description:"报告二级分类id"`
}

func GetReportDetailByClassifyId(classifyIdFirst, classifyIdSecond int) (item *Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT * FROM report WHERE 1=1 `
	if classifyIdSecond > 0 {
		sql = sql + ` AND classify_id_second=?   ORDER BY stage DESC LIMIT 1`
		err = o.Raw(sql, classifyIdSecond).QueryRow(&item)
	} else {
		sql = sql + ` AND classify_id_first=?   ORDER BY stage DESC LIMIT 1`
		err = o.Raw(sql, classifyIdFirst).QueryRow(&item)
	}
	return
}

type SendTemplateMsgReq struct {
	ReportId int `description:"报告id"`
}

func ModifyReportMsgIsSend(reportId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	report, err := GetReportById(reportId)
	if err != nil {
		return
	}
	if report.MsgIsSend == 0 {
		sql := `UPDATE report SET msg_is_send = 1, msg_send_time=NOW()  WHERE id = ? `
		_, err = o.Raw(sql, reportId).Exec()
	}
	return
}

func ModifyReportVideo(reportId int, videoUrl, videoName, videoSize string, playSeconds float64) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET video_url=?,video_name=?,video_play_seconds=?,video_size=? WHERE id=? `
	_, err = o.Raw(sql, videoUrl, videoName, playSeconds, videoSize, reportId).Exec()
	return
}

type ReportItem struct {
	Id                 int       `orm:"column(id)" description:"报告Id"`
	AddType            int       `description:"新增方式：1:新增报告，2:继承报告"`
	ClassifyIdFirst    int       `description:"一级分类id"`
	ClassifyNameFirst  string    `description:"一级分类名称"`
	ClassifyIdSecond   int       `description:"二级分类id"`
	ClassifyNameSecond string    `description:"二级分类名称"`
	Title              string    `description:"标题"`
	Abstract           string    `description:"摘要"`
	Author             string    `description:"作者"`
	Frequency          string    `description:"频度"`
	CreateTime         time.Time `description:"创建时间"`
	ModifyTime         time.Time `description:"修改时间"`
	State              int       `description:"1:未发布，2:已发布"`
	PublishTime        time.Time `description:"发布时间"`
	Stage              int       `description:"期数"`
	MsgIsSend          int       `description:"消息是否已发送，0:否，1:是"`
	Content            string    `description:"内容"`
	VideoUrl           string    `description:"音频文件URL"`
	VideoName          string    `description:"音频文件名称"`
	VideoPlaySeconds   string    `description:"音频播放时长"`
	ContentSub         string    `description:"内容前两个章节"`
}

func GetReportItemById(reportId int) (item *ReportItem, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM report WHERE id=?`
	err = o.Raw(sql, reportId).QueryRow(&item)
	return
}

type SaveReportContent struct {
	Content  string `description:"内容"`
	ReportId int    `description:"报告id"`
	NoChange int    `description:"内容是否未改变：1：内容未改变"`
}

func EditReportContent(reportId int, content, contentSub string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` UPDATE report SET content=?,content_sub=?,modify_time=NOW() WHERE id=? `
	_, err = o.Raw(sql, content, contentSub, reportId).Exec()
	return
}

func AddReportSaveLog(reportId, adminId int, content, contentSub, adminName string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` INSERT INTO report_save_log(report_id, content,content_sub,admin_id,admin_name) VALUES (?,?,?,?,?) `
	_, err = o.Raw(sql, reportId, content, contentSub, adminId, adminName).Exec()
	return
}

func MultiAddReportChaptersSaveLog(items []*ReportChapter, adminId int, adminRealName string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	p, err := o.Raw(`INSERT INTO report_save_log(report_id, report_chapter_id, content, content_sub, admin_id, admin_name) VALUES (?,?,?,?,?,?)`).Prepare()
	if err != nil {
		return
	}
	defer func() {
		_ = p.Close()
	}()
	for _, v := range items {
		_, err = p.Exec(v.ReportId, v.ReportChapterId, v.Content, v.ContentSub, adminId, adminRealName)
		if err != nil {
			return
		}
	}
	return
}

type SaveReportContentResp struct {
	ReportId int `description:"报告id"`
}

func ModifyReportCode(reportId int64, reportCode string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET report_code=? WHERE id=? `
	_, err = o.Raw(sql, reportCode, reportId).Exec()
	return
}

func ModifyReportThsMsgIsSend(item *ReportDetail) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	if item.ThsMsgIsSend == 0 {
		sql := `UPDATE report SET ths_msg_is_send = 1 WHERE id = ? `
		_, err = o.Raw(sql, item.Id).Exec()
	}
	return
}

type ThsSendTemplateMsgReq struct {
	ReportId []int `description:"报告id"`
}

type PublishDayWeekReportReq struct {
	ReportId int `description:"报告ID"`
}

// SaveDayWeekReportReq 新增晨报周报请求体
type SaveDayWeekReportReq struct {
	ReportId   int    `description:"报告ID"`
	Title      string `description:"标题"`
	ReportType string `description:"一级分类ID"`
	Author     string `description:"作者"`
	CreateTime string `description:"创建时间"`
}

// GetDayWeekReportStage 获取晨报周报期数
func GetDayWeekReportStage(classifyIdFirst int, yearStart time.Time) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := " SELECT MAX(stage) AS max_stage FROM report WHERE classify_id_first = ? AND create_time > ? "
	o.Raw(sql, classifyIdFirst, yearStart).QueryRow(&count)

	return
}

// AddReportAndChapter 新增报告及章节
func AddReportAndChapter(reportItem *Report, chapterItemList []*ReportChapter) (reportId int64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	if reportId, err = to.Insert(reportItem); err != nil {
		return
	}
	if len(chapterItemList) > 0 {
		for _, chapterItem := range chapterItemList {
			chapterItem.ReportId = int(reportId)
			cpId, tmpErr := to.Insert(chapterItem)
			if tmpErr != nil {
				return
			}
			chapterItem.ReportChapterId = int(cpId)
		}
	}

	return
}

// GetReportByReportId 主键获取报告
func GetReportByReportId(reportId int) (item *Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM report WHERE id = ?`
	err = o.Raw(sql, reportId).QueryRow(&item)
	return
}

// GetReportByOldReportId 根据老报告id主键获取报告
func GetReportByOldReportId(reportId int) (item *Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM report WHERE old_report_id = ?`
	err = o.Raw(sql, reportId).QueryRow(&item)
	return
}

// DeleteDayWeekReportAndChapter 删除晨周报及章节
func DeleteDayWeekReportAndChapter(reportId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	sql := ` DELETE FROM report WHERE id = ? LIMIT 1 `
	if _, err = to.Raw(sql, reportId).Exec(); err != nil {
		return
	}
	sql = ` DELETE FROM report_chapter WHERE report_id = ? `
	if _, err = to.Raw(sql, reportId).Exec(); err != nil {
		return
	}

	return
}

// UpdateReport 更新报告
func (reportInfo *Report) UpdateReport(cols []string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Update(reportInfo, cols...)

	return
}

// 晨周报详情
type ReportDetailView struct {
	*ReportDetail
	ChapterList []*ReportChapter
}

func GetUnPublishDayReport(startTime time.Time, endTime time.Time) (item *Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT
				*
			FROM
				report AS a
			WHERE
				a.has_chapter = 1
			AND a.chapter_type = "day"
			AND a.state = 1
			AND (
				a.create_time BETWEEN ? AND ?
			)
			ORDER BY
				a.create_time DESC
			LIMIT 1 `
	err = o.Raw(sql, startTime, endTime).QueryRow(&item)
	return
}

type ElasticReportDetail struct {
	ReportId           int    `description:"报告ID"`
	ReportChapterId    int    `description:"报告章节ID"`
	Title              string `description:"标题"`
	Abstract           string `description:"摘要"`
	BodyContent        string `description:"内容"`
	PublishTime        string `description:"发布时间"`
	PublishState       int    `description:"发布状态 1-未发布 2-已发布"`
	Author             string `description:"作者"`
	ClassifyIdFirst    int    `description:"一级分类ID"`
	ClassifyNameFirst  string `description:"一级分类名称"`
	ClassifyIdSecond   int    `description:"二级分类ID"`
	ClassifyNameSecond string `description:"二级分类名称"`
	Categories         string `description:"关联的品种名称(包括品种别名)"`
	StageStr           string `description:"报告期数"`
}

// GetLastPublishedDayWeekReport 获取上一篇已发布的晨周报
func GetLastPublishDayWeekReport(chapterType string) (item *Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT * FROM report WHERE has_chapter = 1 AND chapter_type = ? AND state = 2 ORDER BY publish_time DESC LIMIT 1 `
	err = o.Raw(sql, chapterType).QueryRow(&item)

	return
}

// GetNewReportExist
func GetNewReportExist(oldReportId int) (item *Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT id FROM report WHERE old_report_id = ? LIMIT 1 `
	err = o.Raw(sql, oldReportId).QueryRow(&item)

	return
}

// PublishReportAndChapter 发布报告及章节
func PublishReportAndChapter(reportInfo *Report, publishIds, unPublishIds []int, isPublishReport bool, cols []string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	// 更新报告
	if isPublishReport {
		if _, err = to.Update(reportInfo, cols...); err != nil {
			return
		}
	}
	// 发布章节
	if len(publishIds) > 0 {
		sql := ` UPDATE report_chapter SET publish_state = 2, publish_time = ? WHERE report_id = ? AND report_chapter_id IN (` + utils.GetOrmInReplace(len(publishIds)) + `) `
		_, err = to.Raw(sql, reportInfo.PublishTime, reportInfo.Id, publishIds).Exec()
	}
	if len(unPublishIds) > 0 {
		sql := ` UPDATE report_chapter SET publish_state = 1, publish_time = NULL, is_edit = 0 WHERE report_id = ? AND report_chapter_id IN (` + utils.GetOrmInReplace(len(unPublishIds)) + `) `
		_, err = to.Raw(sql, reportInfo.Id, unPublishIds).Exec()
	}
	return
}

func GetSyncEmptyVideoReport() (list []*Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT id FROM report WHERE old_report_id > 0 AND state = 2 AND chapter_type = "" AND (video_size = "" OR video_play_seconds = "")
UNION ALL
SELECT DISTINCT report_id FROM report_chapter WHERE publish_state = 2 AND (video_size = "" OR video_play_seconds = "") `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// PublishReportById 发布报告
func PublishReportById(reportId int, publishTime time.Time) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET state = 2, publish_time = ?, pre_publish_time=null, pre_msg_send=0, modify_time = NOW() WHERE id = ? `
	_, err = o.Raw(sql, publishTime, reportId).Exec()
	return
}

// ResetReportById 重置报告状态
func ResetReportById(reportId, state int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET state = ?, pre_publish_time = null, pre_msg_send = 0, modify_time = NOW() WHERE id = ?`
	_, err = o.Raw(sql, state, reportId).Exec()
	return
}

// GetCommentReportByReportId 查询有留言的报告列表
func GetCommentReportByReportId(condition string, pars []interface{}, startSize, pageSize int) (list []*Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT
	id,
	create_time,
	title,
	classify_name_first,
	classify_id_first,
	classify_name_second,
	classify_id_second,
	state,
IF
	( publish_time, publish_time, create_time ) AS publish_time 
FROM
	report 
WHERE
	1=1
  `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY  publish_time DESC , title ASC LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// GetCommentReportByReportIdOrderBy 查询有留言的报告列表（指定排序）
func GetCommentReportByReportIdOrderBy(condition string, pars []interface{}, startSize, pageSize int, orderBy string) (list []*Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT
	id,
	create_time,
	title,
	classify_name_first,
	classify_id_first,
	classify_name_second,
	classify_id_second,
	state,
IF
	( publish_time, publish_time, create_time ) AS publish_time 
FROM
	report 
WHERE
	1=1
  `
	if condition != "" {
		sql += condition
	}
	if orderBy == `` {
		sql += ` ORDER BY  publish_time DESC , title ASC `
	} else {
		sql += orderBy
	}
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// GetCommentReportTotalByReportId 查询有留言的报告列表总数
func GetCommentReportTotalByReportId(condition string, pars []interface{}) (total int64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT count(*)
        FROM report WHERE 1=1`
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&total)
	return
}

// 点赞相关的报告列表
type LikeReportItem struct {
	ReportId              int       `description:"报告Id"`
	ReportChapterId       int       `description:"报告章节Id"`
	ClassifyIdFirst       int       `description:"一级分类id"`
	ClassifyNameFirst     string    `description:"一级分类名称"`
	ClassifyIdSecond      int       `description:"二级分类id"`
	ClassifyNameSecond    string    `description:"二级分类名称"`
	ReportChapterTypeId   int       `description:"章节类型"`
	ReportChapterTypeName string    `description:"品种名称"`
	PublishTime           time.Time `description:"发布时间" `
	Title                 string    `description:"标题"`
}

// GetLikeReportByReportIdReportChapterId 获取有被点赞的报告列表
func GetLikeReportByReportIdReportChapterId(reportIds string, chapterIds string, orderStr string, startSize, pageSize int) (list []*LikeReportItem, err error) {
	o := orm.NewOrmUsingDB("rddp")
	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,
"" as report_chapter_type_name,
publish_time,
title
FROM
	report
WHERE
	classify_name_first != "晨报" 
	AND classify_name_first != "周报"
	AND id in (` + reportIds + `)
	)
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,
type_name as report_chapter_type_name,
publish_time,
title
FROM
	report_chapter
WHERE
	 report_chapter_id in (` + chapterIds + `)
	)`
	if orderStr != "" {
		sql += ` ORDER BY FIELD(CONCAT(report_id, "-",report_chapter_id),` + orderStr + `)`
	} else {
		sql += ` ORDER BY  publish_time DESC, report_id Desc`
	}
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, startSize, pageSize).QueryRows(&list)
	return
}

// GetLikeReportTotalByReportIdReportChapterId 获取有被点赞的报告列表总数
func GetLikeReportTotalByReportIdReportChapterId(reportIds string, chapterIds string) (total int64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `select count(*) from (( 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,
publish_time,
title
FROM
	report
WHERE
	classify_name_first != "晨报" 
	AND classify_name_first != "周报"
	AND id in (` + reportIds + `)
	)
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,
publish_time,
title
FROM
	report_chapter
WHERE
report_chapter_id in (` + chapterIds + `)
	)) r`
	err = o.Raw(sql).QueryRow(&total)
	return
}

// GetPageReportList 分页获取报告列表
func GetPageReportList(condition string, pars []interface{}, startSize, pageSize int) (total int, items []*ReportList, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM report WHERE 1=1 `
	sql += condition
	sql += ` ORDER BY modify_time DESC`
	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// SunCodeReq 获取太阳码请求体
type SunCodeReq struct {
	CodePage  string `json:"CodePage" description:"太阳码page"`
	CodeScene string `json:"CodeScene" description:"太阳码scene"`
}

// YbPcSuncode 活动海报表
type YbPcSuncode struct {
	SuncodeID  uint32    `orm:"column(suncode_id);pk" `
	Scene      string    `gorm:"column:scene;type:varchar(255);not null;default:0" json:"scene"` // 微信scene
	SceneMd5   string    `gorm:"column:scene_md5;type:varchar(255);not null" json:"sceneMd5"`
	CodePage   string    `gorm:"column:code_page;type:varchar(255);not null;default:''" json:"codePage"`     // 路径
	SuncodeUrl string    `gorm:"column:suncode_url;type:varchar(255);not null;default:''" json:"suncodeUrl"` // 太阳码储存地址
	CreateTime time.Time `gorm:"column:create_time;type:timestamp;default:CURRENT_TIMESTAMP" json:"createTime"`
}

// GetYbPcSunCode 获取太阳码
func GetYbPcSunCode(scene, page string) (item *YbPcSuncode, err error) {
	o := orm.NewOrmUsingDB("weekly")
	sql := `SELECT * FROM yb_pc_suncode WHERE scene = ? AND code_page = ? `
	err = o.Raw(sql, scene, page).QueryRow(&item)
	return
}

func AddYbPcSunCode(item *YbPcSuncode) (err error) {
	o := orm.NewOrmUsingDB("weekly")
	_, err = o.Insert(item)
	return
}

// YbSuncodePars 小程序太阳码scene参数
type YbSuncodePars struct {
	ID         uint32    `orm:"column(id);pk" `
	Scene      string    `gorm:"column:scene;type:varchar(255);not null;default:''" json:"scene"`        // scene参数
	SceneKey   string    `gorm:"column:scene_key;type:varchar(32);not null;default:''" json:"scene_key"` // MD5值
	CreateTime time.Time `gorm:"column:create_time;type:datetime;default:CURRENT_TIMESTAMP" json:"createTime"`
}

func AddYbSuncodePars(item *YbSuncodePars) (err error) {
	o := orm.NewOrmUsingDB("weekly")
	_, err = o.Insert(item)
	return
}

// UpdateReportSecondClassifyNameByClassifyId 更新报告分类名称字段
func UpdateReportSecondClassifyNameByClassifyId(classifyId int, classifyName string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := " UPDATE report SET classify_name_second = ? WHERE classify_id_second = ? "
	_, err = o.Raw(sql, classifyName, classifyId).Exec()
	return
}

// UpdateReportFirstClassifyNameByClassifyId 更新报告分类一级名称字段
func UpdateReportFirstClassifyNameByClassifyId(classifyId int, classifyName string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := " UPDATE report SET classify_name_first = ? WHERE classify_id_first = ? "
	_, err = o.Raw(sql, classifyName, classifyId).Exec()
	return
}

// UpdateReportSecondClassifyFirstNameByClassifyId 更新报告二级分类的一级分类名称和id
func UpdateReportSecondClassifyFirstNameByClassifyId(classifyId, newClassifyId int, classifyName string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := " UPDATE report SET classify_name_first = ?, classify_id_first = ? WHERE classify_id_second = ? "
	_, err = o.Raw(sql, classifyName, newClassifyId, classifyId).Exec()
	return
}

// GetEmptyContentSubPPTReport 获取前两章为空的PPT报告
func GetEmptyContentSubPPTReport() (list []*Report, err error) {
	sql := `SELECT
				r.id,
				r.content,
				r.content_sub
			FROM
				report AS r
			JOIN ppt_v2 AS p ON r.id = p.report_id
			WHERE
				p.report_id > 0 AND r.content_sub = ""`
	_, err = orm.NewOrmUsingDB("rddp").Raw(sql).QueryRows(&list)
	return
}

// ModifyReportAuthor 更改报告作者
func ModifyReportAuthor(condition string, pars []interface{}, authorName string) (count int, err error) {
	//产品权限
	oRddp := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE english_report set author = ? WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = oRddp.Raw(sql, authorName, pars).QueryRow(&count)
	return
}

func UpdateReportPublishTime(reportId int, videoNameDate string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql1 := ` UPDATE report SET publish_time = NOW() WHERE id = ?  `
	_, err = o.Raw(sql1, reportId).Exec()
	if err != nil {
		return
	}
	//修改音频标题
	sql2 := ` UPDATE report SET video_name=CONCAT(SUBSTRING_INDEX(video_name,"(",1),"` + videoNameDate + `") WHERE id = ? and (video_name !="" and video_name is not null)`
	_, err = o.Raw(sql2, reportId).Exec()
	return
}

func UpdateReportChapterPublishTime(reportId int, videoNameDate string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql1 := ` UPDATE report_chapter SET publish_time = NOW() WHERE report_id = ? `
	_, err = o.Raw(sql1, reportId).Exec()
	if err != nil {
		return
	}
	//修改音频标题
	sql2 := ` UPDATE report_chapter SET video_name=CONCAT(SUBSTRING_INDEX(video_name,"(",1),"` + videoNameDate + `") WHERE report_id = ? and (video_name !="" and video_name is not null)`
	_, err = o.Raw(sql2, reportId).Exec()
	return
}

// MarkEditReport 标记编辑英文研报的请求数据
type MarkEditReport struct {
	ReportId int `description:"研报id"`
	Status   int `description:"标记状态，1：编辑中，2：编辑完成"`
}

type MarkReportResp struct {
	Status int    `description:"状态：0:无人编辑, 1：当前有人在编辑"`
	Msg    string `description:"提示信息"`
	Editor string `description:"编辑者姓名"`
}

type MarkReportItem struct {
	AdminId                 int    `description:"编辑者ID"`
	Editor                  string `description:"编辑者姓名"`
	ReportClassifyNameFirst string
}

// GetReportByCondition 获取报告
func GetReportByCondition(condition string, pars []interface{}, fieldArr []string, orderRule string, isPage bool, startSize, pageSize int) (items []*Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	fields := `*`
	if len(fieldArr) > 0 {
		fields = strings.Join(fieldArr, ",")
	}
	sql := `SELECT ` + fields + ` FROM report WHERE 1=1 `
	sql += condition
	order := ` ORDER BY modify_time DESC`
	if orderRule != `` {
		order = orderRule
	}
	sql += order
	if isPage {
		sql += ` LIMIT ?,?`
		_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	} else {
		_, err = o.Raw(sql, pars).QueryRows(&items)
	}
	return
}

// ModifyReportMsgIsSendV2 更新报告消息状态
func ModifyReportMsgIsSendV2(reportId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET msg_is_send = 1, ths_msg_is_send = 1, msg_send_time = NOW() WHERE id = ? LIMIT 1`
	_, err = o.Raw(sql, reportId).Exec()
	return
}

// SetPrePublishReportById 设置定时发布
func SetPrePublishReportById(reportId int, prePublishTime string, preMsgSend int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET pre_publish_time=?, pre_msg_send=? WHERE id = ? and state = 1 `
	_, err = o.Raw(sql, prePublishTime, preMsgSend, reportId).Exec()
	return
}

// ReportSubmitApproveReq 提交审批请求体
type ReportSubmitApproveReq struct {
	ReportId int `description:"报告ID"`
}

// ReportCancelApproveReq 撤回审批请求体
type ReportCancelApproveReq struct {
	ReportId int `description:"报告ID"`
}

func (m *Report) GetItemById(id int) (item *Report, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM report WHERE id = ? LIMIT 1`
	err = o.Raw(sql, id).QueryRow(&item)
	return
}

// GetReportStateCount 获取指定状态的报告数量
func GetReportStateCount(state int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT COUNT(1) AS count FROM report WHERE state = ?`
	err = o.Raw(sql, state).QueryRow(&count)
	return
}

// UpdateReportsStateByCond 批量更新报告状态
func UpdateReportsStateByCond(classifyFirstId, classifySecondId, oldState, newState int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	cond := ``
	if classifyFirstId > 0 {
		cond += fmt.Sprintf(` AND classify_id_first = %d`, classifyFirstId)
	}
	if classifySecondId > 0 {
		cond += fmt.Sprintf(` AND classify_id_second = %d`, classifySecondId)
	}
	sql := fmt.Sprintf(`UPDATE report SET state = ?, pre_publish_time = NULL WHERE state = ? %s`, cond)
	_, err = o.Raw(sql, newState, oldState).Exec()
	return
}

// UpdateReportsStateBySecondIds 批量更新二级分类报告状态
func UpdateReportsStateBySecondIds(oldState, newState int, secondIds []int) (err error) {
	if len(secondIds) <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("rddp")
	// (有审批流的)未发布->待提交
	sql := fmt.Sprintf(`UPDATE report SET state = ?, pre_publish_time = NULL WHERE state = ? AND classify_id_second IN (%s)`, utils.GetOrmInReplace(len(secondIds)))
	_, err = o.Raw(sql, newState, oldState, secondIds).Exec()
	if err != nil {
		return
	}
	// (无审批流的)待提交->未发布
	sql = fmt.Sprintf(`UPDATE report SET state = ?, pre_publish_time = NULL WHERE state = ? AND classify_id_second NOT IN (%s)`, utils.GetOrmInReplace(len(secondIds)))
	_, err = o.Raw(sql, oldState, newState, secondIds).Exec()
	return
}

func ModifyReportPdfUrl(reportId int, detailPdfUrl string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET detail_pdf_url=? WHERE id=? `
	_, err = o.Raw(sql, detailPdfUrl, reportId).Exec()
	return
}

func ModifyReportImgUrl(reportId int, detailImgUrl string) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET detail_img_url=? WHERE id=? `
	_, err = o.Raw(sql, detailImgUrl, reportId).Exec()
	return
}

// UpdatePdfUrlReportById 清空pdf相关字段
func UpdatePdfUrlReportById(reportId int) (err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `UPDATE report SET detail_img_url = '',detail_pdf_url='',modify_time=NOW() WHERE id = ? `
	_, err = o.Raw(sql, reportId).Exec()
	return
}

func GetReportListByCollectCount(classifyIdFirst, classifyIdSecond, classifyIdThird []string, keyword, author string, state int) (count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	var params []interface{}

	// SQL 主体
	sql := `
		SELECT COUNT(DISTINCT t.id) AS totalCount
		FROM (
			SELECT b.id
			FROM report AS b
			LEFT JOIN user_collect_classify AS a ON a.classify_id = b.classify_id_first
			WHERE 1 = 1
	`

	// 处理 classifyIdFirst
	if len(classifyIdFirst) > 0 {
		sql += " AND a.classify_id IN ( " + utils.GetOrmInReplace(len(classifyIdFirst)) + " ) "
		for _, id := range classifyIdFirst {
			params = append(params, id)
		}
	}

	// 处理关键词
	if keyword != "" {
		sql += " AND (b.title LIKE ? OR b.admin_real_name LIKE ?)"
		params = append(params, "%"+keyword+"%", "%"+keyword+"%")
	}

	if author != "" {
		sql += " AND b.author = ? "
		params = append(params, author)
	}

	if state > 0 {
		sql += " AND b.state = ? "
		params = append(params, state)
	}

	// 第二段 SQL
	sql += `
		UNION ALL
		SELECT b.id
		FROM report AS b
		LEFT JOIN user_collect_classify AS a ON a.classify_id = b.classify_id_second
		WHERE 1 = 1
	`

	// 处理 classifyIdSecond
	if len(classifyIdSecond) > 0 {
		sql += " AND a.classify_id IN ( " + utils.GetOrmInReplace(len(classifyIdSecond)) + " ) "
		for _, id := range classifyIdSecond {
			params = append(params, id)
		}
	}

	// 处理关键词
	if keyword != "" {
		sql += " AND (b.title LIKE ? OR b.admin_real_name LIKE ?)"
		params = append(params, "%"+keyword+"%", "%"+keyword+"%")
	}

	if author != "" {
		sql += " AND b.author = ? "
		params = append(params, author)
	}

	if state > 0 {
		sql += " AND b.state = ? "
		params = append(params, state)
	}

	// 第三段 SQL
	sql += `
		UNION ALL
		SELECT b.id
		FROM report AS b
		LEFT JOIN user_collect_classify AS a ON a.classify_id = b.classify_id_third
		WHERE 1 = 1
	`

	// 处理 classifyIdThird
	if len(classifyIdThird) > 0 {
		sql += " AND a.classify_id IN ( " + utils.GetOrmInReplace(len(classifyIdThird)) + " ) "
		for _, id := range classifyIdThird {
			params = append(params, id)
		}
	}

	// 处理关键词
	if keyword != "" {
		sql += " AND (b.title LIKE ? OR b.admin_real_name LIKE ?)"
		params = append(params, "%"+keyword+"%", "%"+keyword+"%")
	}

	if author != "" {
		sql += " AND b.author = ? "
		params = append(params, author)
	}

	if state > 0 {
		sql += " AND b.state = ? "
		params = append(params, state)
	}

	sql += ") AS t"

	// 执行 SQL 查询获取总数
	err = o.Raw(sql, params...).QueryRow(&count)
	return count, err
}

func GetReportListByCollectList(classifyIdFirst, classifyIdSecond, classifyIdThird []string, keyword, orderField, orderType string, startSize, pageSize int, author string, state int) (items []*ReportList, err error) {
	o := orm.NewOrmUsingDB("rddp")
	var params []interface{}

	// SQL 主体
	sql := `
		SELECT DISTINCT t.id, t.title, t.author, t.modify_time, t.publish_time
		FROM (
			SELECT b.id, b.title, b.author, b.modify_time, b.publish_time
			FROM report AS b
			LEFT JOIN user_collect_classify AS a ON a.classify_id = b.classify_id_first
			WHERE 1 = 1
	`

	// 处理 classifyIdFirst
	if len(classifyIdFirst) > 0 {
		sql += " AND a.classify_id IN ( " + utils.GetOrmInReplace(len(classifyIdFirst)) + " ) "
		for _, id := range classifyIdFirst {
			params = append(params, id)
		}
	}

	// 处理关键词
	if keyword != "" {
		sql += " AND (b.title LIKE ? OR b.admin_real_name LIKE ?)"
		params = append(params, "%"+keyword+"%", "%"+keyword+"%")
	}

	if author != "" {
		sql += " AND b.author = ? "
		params = append(params, author)
	}

	if state > 0 {
		sql += " AND b.state = ? "
		params = append(params, state)
	}

	// 第二段 SQL
	sql += `
		UNION ALL
		SELECT b.id, b.title, b.author, b.modify_time, b.publish_time
		FROM report AS b
		LEFT JOIN user_collect_classify AS a ON a.classify_id = b.classify_id_second
		WHERE 1 = 1
	`

	// 处理 classifyIdSecond
	if len(classifyIdSecond) > 0 {
		sql += " AND a.classify_id IN ( " + utils.GetOrmInReplace(len(classifyIdSecond)) + " ) "
		for _, id := range classifyIdSecond {
			params = append(params, id)
		}
	}

	// 处理关键词
	if keyword != "" {
		sql += " AND (b.title LIKE ? OR b.admin_real_name LIKE ?)"
		params = append(params, "%"+keyword+"%", "%"+keyword+"%")
	}

	if author != "" {
		sql += " AND b.author = ? "
		params = append(params, author)
	}

	if state > 0 {
		sql += " AND b.state = ? "
		params = append(params, state)
	}

	// 第三段 SQL
	sql += `
		UNION ALL
		SELECT b.id, b.title, b.author, b.modify_time, b.publish_time
		FROM report AS b
		LEFT JOIN user_collect_classify AS a ON a.classify_id = b.classify_id_third
		WHERE 1 = 1
	`

	// 处理 classifyIdThird
	if len(classifyIdThird) > 0 {
		sql += " AND a.classify_id IN ( " + utils.GetOrmInReplace(len(classifyIdThird)) + " ) "
		for _, id := range classifyIdThird {
			params = append(params, id)
		}
	}

	// 处理关键词
	if keyword != "" {
		sql += " AND (b.title LIKE ? OR b.admin_real_name LIKE ?)"
		params = append(params, "%"+keyword+"%", "%"+keyword+"%")
	}

	if author != "" {
		sql += " AND b.author = ? "
		params = append(params, author)
	}

	if state > 0 {
		sql += " AND b.state = ? "
		params = append(params, state)
	}

	sql += ") AS t "

	// 排序处理
	if orderField != "" && orderType != "" {
		sql += " ORDER BY " + orderField + " " + orderType
	} else {
		sql += " ORDER BY t.modify_time DESC, t.publish_time DESC "
	}

	// 分页
	sql += " LIMIT ?, ?"
	params = append(params, (startSize-1)*pageSize, pageSize)

	// 执行 SQL 查询
	_, err = o.Raw(sql, params...).QueryRows(&items)
	return items, err
}