hz_crm_api/models/report.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"hongze/hz_crm_api/utils"
	"strings"
	"time"
)

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:已发布"`
	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:"创建者姓名"`
}

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:已发布"`
	PublishTime        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:"创建者姓名"`
}

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

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:"发布时间"`
	Stage              int    `description:"期数"`
	MsgIsSend          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-周报"`
}

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
}

// GetReportByIdList 根据报告id列表获取报告列表
func GetReportByIdList(reportIdList []int) (list []*ReportDetail, err error) {
	num := len(reportIdList)
	if num <= 0 {
		return
	}

	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT * FROM report WHERE id in  (` + utils.GetOrmInReplace(num) + `)`
	_, err = o.Raw(sql, reportIdList).QueryRows(&list)

	return
}

// GetSimpleReportByIds 根据报告ID查询报告基本信息
func GetSimpleReportByIds(reportIds string) (list []*ReportDetail, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT id, title FROM report WHERE id IN (` + reportIds + `)`
	_, err = o.Raw(sql).QueryRows(&list)
	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
}

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

// 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("default")
	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("default")
	_, 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("default")
	_, err = o.Insert(item)
	return
}

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