hongze_cygx/models/micro_roadshow.go

package models

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

// MicroRoadShowListResp 微路演列表响应体
type MicroRoadShowListResp struct {
	Paging *paging.PagingItem
	List   []*MicroRoadShowPageList
}

type CygxMicroRoadshowVideo struct {
	VideoId             int       `orm:"column(video_id);pk"description:"微路演视频id"`
	VideoName           string    `description:"视频名称"`
	ChartPermissionId   int       `description:"行业Id"`
	ChartPermissionName string    `description:"行业名称"`
	IndustryId          int       `description:"产业id"`
	IndustryName        string    `description:"产业名称"`
	PublishStatus       int       `description:"发布状态 1发布 0没有"`
	ModifyDate          string    `description:"更新时间"`
	PublishDate         time.Time `description:"发布时间"`
	VideoDuration       string    `description:"视频时长"`
	VideoCounts         int       `description:"播放量"`
	VideoUrl            string    `description:"视频地址"`
	CreateTime          string    `description:"创建时间"`
	ImgUrl              string    `description:"视频封面图"`
	ShareImgUrl         string    `description:"视频分享图"`
	DetailImgUrl        string    `description:"产业详情页背景图"`
	CommentNum          int       `description:"留言总数"`
	IsSendWxMsg         int       `description:"是否推送过微信模板消息，1是，0：否"`
}

func GetMicroRoadshowVideoByVideoId(videoId int) (item *CygxMicroRoadshowVideo, err error) {
	o := orm.NewOrm()
	sql := `SELECT * from cygx_micro_roadshow_video where video_id = ? `
	err = o.Raw(sql, videoId).QueryRow(&item)
	return
}

// MicroRoadShowPageList 微路演列表
type MicroRoadShowPageList struct {
	Id                  int    `description:"音视频ID"`
	SourceId            int    `description:"资源ID"`
	Title               string `description:"标题"`
	ResourceUrl         string `description:"链接"`
	Type                int    `description:"类型: 1-活动音频; 2-活动视频； 3-产业视频; 4-问答系列"`
	LabelType           int    `description:"标签类型: 1-路演回放; 2-调研反馈； 3-产业视频; 4-问答系列"`
	PublishTime         string `description:"发布时间"`
	BackgroundImg       string `description:"背景图"`
	ShareImg            string `description:"分享封面图"`
	ChartPermissionId   int    `description:"行业ID"`
	ChartPermissionName string `description:"行业名称"`
	IndustryName        string `description:"产业名称"`
	IndustryId          int    `description:"产业id"`
	PlaySeconds         string `description:"音视频时长"`
	ActivityId          int    `description:"活动ID"`
	IsCollect           bool   `description:"是否收藏"`
	AuthInfo            *UserPermissionAuthInfo
}

// GetMicroRoadShowVideoPageList 获取微路演视频列表-分页
func GetMicroRoadShowVideoPageListV8(startSize, pageSize int, condition string, pars []interface{}, conditionAct string, parsAct []interface{}, conditionAudio string, parsAudio []interface{}) (total int, list []*MicroRoadShowPageList, err error) {
	o := orm.NewOrm()
	var sql string
	//if audioId+activityVideoId == 0 && filter != 2 {
	sql += `SELECT
			video_id AS id,
			video_name AS title,
			video_url AS resource_url,
			3 AS type,
			publish_date AS publish_time,
			chart_permission_id,
			chart_permission_name,
			video_duration AS play_seconds,
			img_url AS background_img,            
			industry_name,
			industry_id,
			share_img_url AS share_img,
			"" as  activity_id
		FROM
			cygx_micro_roadshow_video 
		WHERE
			publish_status = 1 `
	if condition != `` {
		sql += condition
	}
	//}
	//if audioId+videoId+activityVideoId == 0 && filter != 2 {
	//	sql += `  UNION ALL `
	//}
	sql += `  UNION ALL `
	//if audioId+videoId == 0 && filter != 2 {
	sql += `
		SELECT
			video_id AS id,
			video_name AS title,
			video_url AS resource_url,
			2 AS type,
	    	art.activity_time as publish_time,
			art.chart_permission_id,
			art.chart_permission_name,
			"" AS play_seconds,
			v.background_img,
			"" AS industry_name,
			"",
			v.share_img,
			v.activity_id
		FROM
			cygx_activity_video as v
			INNER JOIN cygx_activity as art on art.activity_id = v.activity_id WHERE 1= 1  `
	if conditionAct != `` {
		sql += conditionAct
	}
	//}
	//if audioId+videoId+activityVideoId == 0 && filter == 0 {
	//	sql += `  UNION ALL `
	//}
	sql += `  UNION ALL `
	//if videoId+activityVideoId == 0 && filter != 1 {
	sql += `
			SELECT
			a.activity_voice_id AS id,
			a.voice_name AS title,
			a.voice_url AS resource_url,
			1 AS type,
			b.activity_time AS publish_time,
			b.chart_permission_id,
			b.chart_permission_name,
			a.voice_play_seconds AS play_seconds,
			a.background_img,
			"" AS industry_name,
			"",
			a.share_img,
			a.activity_id 
		FROM
			cygx_activity_voice AS a
			JOIN cygx_activity AS b ON a.activity_id = b.activity_id  WHERE 1= 1  `
	if conditionAudio != `` {
		sql += conditionAudio
	}
	//}
	sql += ` ORDER BY publish_time DESC`
	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSql, pars, parsAct, parsAudio).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, pars, parsAct, parsAudio, startSize, pageSize).QueryRows(&list)
	return
}

// GetMicroRoadShowVideoPageList 获取微路演视频列表-分页
func GetMicroRoadShowVideoPageListV12(startSize, pageSize int, audioAct string, audioActPars []interface{}, videoAct string, videoActPars []interface{}, videoMico string, videoMicoPars []interface{}, conditionAskserie string, askseriePars []interface{}) (total int, list []*MicroRoadShowPageList, err error) {
	o := orm.NewOrm()
	var sql string

	//活动音频1
	sql += `
			SELECT
			a.activity_voice_id AS id,
			a.activity_id AS source_id,
			a.voice_name AS title,
			a.voice_url AS resource_url,
			1 AS type,
			b.activity_time AS publish_time,
			b.chart_permission_id,
			b.chart_permission_name,
			a.voice_play_seconds AS play_seconds,
			a.background_img,
			"" AS industry_name,
			0 AS  industry_id,
			a.share_img,
			a.activity_id 
		FROM
			cygx_activity_voice AS a
			JOIN cygx_activity AS b ON a.activity_id = b.activity_id  WHERE 1= 1  `
	if audioAct != `` {
		sql += audioAct
	}

	//活动视频2
	sql += `  UNION ALL `
	sql += `
		SELECT
			a.video_id AS id,
			a.activity_id AS source_id,
			a.video_name AS title,
			a.video_url AS resource_url,
			2 AS type,
	    	b.activity_time as publish_time,
			b.chart_permission_id,
			b.chart_permission_name,
			"" AS play_seconds,
			a.background_img,
			"" AS industry_name,
			0 AS  industry_id,
			a.share_img,
			a.activity_id
		FROM
			cygx_activity_video as a
			INNER JOIN cygx_activity as b on a.activity_id = b.activity_id WHERE 1= 1  `
	if videoAct != `` {
		sql += videoAct
	}
	sql += `  UNION ALL `

	// 产业视频3
	sql += `SELECT
			video_id AS id,
			video_id AS source_id,
			video_name AS title,
			video_url AS resource_url,
			3 AS type,
			publish_date AS publish_time,
			chart_permission_id,
			chart_permission_name,
			video_duration AS play_seconds,
			img_url AS background_img,            
			industry_name,
			industry_id,
			share_img_url AS share_img,
			"" as  activity_id
		FROM
			cygx_micro_roadshow_video  as a
		WHERE
			publish_status = 1 `
	if videoMico != `` {
		sql += videoMico
	}

	//系列问答
	sql += `  UNION ALL `
	sql += `
			SELECT
			a.askserie_video_id AS id,
			a.askserie_video_id AS source_id,
			a.video_name AS title,
			a.video_url AS resource_url,
			4 AS type,
			a.publish_date AS publish_time,
			a.chart_permission_id,
			a.chart_permission_name,
			a.video_duration AS play_seconds,
			a.background_img,
			"" AS industry_name,
			0 AS  industry_id,
			a.share_img,
			0  as activity_id 
		FROM
			cygx_askserie_video AS a WHERE 1= 1 `
	if conditionAskserie != `` {
		sql += conditionAskserie
	}

	sql += ` ORDER BY publish_time DESC`
	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSql, audioActPars, videoActPars, videoMicoPars, askseriePars).QueryRow(&total)
	if err != nil {
		return
	}
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, audioActPars, videoActPars, videoMicoPars, askseriePars, startSize, pageSize).QueryRows(&list)
	return
}

// CountMicroRoadShowVideoPageList 获取微路演视频数量
func CountMicroRoadShowVideoPageList(condition string, pars []interface{}, conditionAct string, parsAct []interface{}, conditionAudio string, parsAudio []interface{}) (total int, err error) {
	o := orm.NewOrm()
	var sql string
	sql += `SELECT
			video_id AS id,
			video_name AS title,
			video_url AS resource_url,
			3 AS type,
			publish_date AS publish_time,
			chart_permission_id,
			chart_permission_name,
			video_duration AS play_seconds,
			img_url AS background_img,            
			industry_name,
			share_img_url AS share_img,
			"" as  activity_id
		FROM
			cygx_micro_roadshow_video 
		WHERE
			publish_status = 1 `
	if condition != `` {
		sql += condition
	}
	sql += `  UNION ALL `
	sql += `
		SELECT
			video_id AS id,
			video_name AS title,
			video_url AS resource_url,
			2 AS type,
		art.activity_time as publish_time,
			art.chart_permission_id,
			art.chart_permission_name,
			"" AS play_seconds,
			v.background_img,
			"" AS industry_name,
			v.share_img,
			v.activity_id
		FROM
			cygx_activity_video as v
			INNER JOIN cygx_activity as art on art.activity_id = v.activity_id WHERE 1= 1  `
	if conditionAct != `` {
		sql += conditionAct
	}
	sql += `  UNION ALL `
	sql += `
			SELECT
			a.activity_voice_id AS id,
			a.voice_name AS title,
			a.voice_url AS resource_url,
			1 AS type,
			b.activity_time AS publish_time,
			b.chart_permission_id,
			b.chart_permission_name,
			a.voice_play_seconds AS play_seconds,
			a.background_img,
			"" AS industry_name,
			a.share_img,
			a.activity_id 
		FROM
			cygx_activity_voice AS a
			JOIN cygx_activity AS b ON a.activity_id = b.activity_id  WHERE 1= 1  `
	if conditionAudio != `` {
		sql += conditionAudio
	}

	sql += ` ORDER BY publish_time DESC`
	totalSql := `SELECT COUNT(1) total FROM (` + sql + `) z `
	err = o.Raw(totalSql, pars, parsAct, parsAudio).QueryRow(&total)
	return
}

// GetMicroRoadShowVideoPageList 获取微路演视频列表-分页
func GetMicroRoadShowVideoPageListIkWord(startSize, pageSize int, condition string, pars []interface{}, conditionAct string, parsAct []interface{}, conditionAudio string, parsAudio []interface{}) (list []*MicroRoadShowPageList, err error) {
	o := orm.NewOrm()
	var sql string
	//if audioId+activityVideoId == 0 && filter != 2 {
	sql += `SELECT
			video_id AS id,
			video_name AS title,
			video_url AS resource_url,
			3 AS type,
			publish_date AS publish_time,
			chart_permission_id,
			chart_permission_name,
			video_duration AS play_seconds,
			img_url AS background_img,            
			industry_name,
			share_img_url AS share_img,
			"" as  activity_id
		FROM
			cygx_micro_roadshow_video 
		WHERE
			publish_status = 1 `
	if condition != `` {
		sql += condition
	}
	//}
	//if audioId+videoId+activityVideoId == 0 && filter != 2 {
	//	sql += `  UNION ALL `
	//}
	sql += `  UNION ALL `
	//if audioId+videoId == 0 && filter != 2 {
	sql += `
		SELECT
			video_id AS id,
			video_name AS title,
			video_url AS resource_url,
			2 AS type,
			art.activity_time as publish_time,
			art.chart_permission_id,
			art.chart_permission_name,
			"" AS play_seconds,
			v.background_img,
			"" AS industry_name,
			v.share_img,
			v.activity_id
		FROM
			cygx_activity_video as v
			INNER JOIN cygx_activity as art on art.activity_id = v.activity_id WHERE 1= 1  `
	if conditionAct != `` {
		sql += conditionAct
	}
	//}
	//if audioId+videoId+activityVideoId == 0 && filter == 0 {
	//	sql += `  UNION ALL `
	//}
	sql += `  UNION ALL `
	//if videoId+activityVideoId == 0 && filter != 1 {
	sql += `
			SELECT
			a.activity_voice_id AS id,
			a.voice_name AS title,
			a.voice_url AS resource_url,
			1 AS type,
			b.activity_time AS publish_time,
			b.chart_permission_id,
			b.chart_permission_name,
			a.voice_play_seconds AS play_seconds,
			a.background_img,
			"" AS industry_name,
			a.share_img,
			a.activity_id 
		FROM
			cygx_activity_voice AS a
			JOIN cygx_activity AS b ON a.activity_id = b.activity_id  WHERE 1= 1  `
	if conditionAudio != `` {
		sql += conditionAudio
	}
	//}
	sql += ` ORDER BY publish_time DESC`
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, pars, parsAct, parsAudio, startSize, pageSize).QueryRows(&list)
	return
}

type AddVideoHistoryReq struct {
	SourceId int `description:"资源ID"`
	//VideoId     int `description:"视频ID"`
	PlaySeconds int `description:"播放时长"`
	//SourceType  int `description:"视频来源: 1-微路演; 2-活动 (不传默认为1)"`
	SourceType int `description:"音视频来源: 1-活动音频; 2-活动视频； 3-产业视频; 4-系列问答"`
}

func GetLastCygxMicroRoadshowVideoHistory(videoId, userId int) (item *CygxMicroRoadshowVideoHistory, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM cygx_micro_roadshow_video_history WHERE video_id=? AND user_id=? ORDER BY create_time DESC limit 1 `
	err = o.Raw(sql, videoId, userId).QueryRow(&item)
	return
}

func AddCygxMicroRoadshowVideoHistory(item *CygxMicroRoadshowVideoHistory) (err error) {
	o := orm.NewOrm()
	_, err = o.Insert(item)
	return
}

// MicroAudioUnionList 微路演音频联合列表
type MicroAudioUnionList struct {
	Id                       int    `description:"音视频ID"`
	AudioTitle               string `description:"标题"`
	AudioResourceUrl         string `description:"链接"`
	AudioType                int    `description:"类型: 1-音频; 2-视频"`
	AudioPublishTime         string `description:"发布时间"`
	AudioImgUrl              string `description:"背景图"`
	AudioShareImg            string `description:"分享图"`
	AudioChartPermissionId   int    `description:"行业ID"`
	AudioChartPermissionName string `description:"行业名称"`
	AudioPlaySeconds         string `description:"音视频时长"`
	AudioActivityId          int    `description:"活动ID"`
	AuthInfo                 *UserPermissionAuthInfo
}

// HomeNewestUnionList 首页最新纪要-音频联合查询结果
type HomeNewestUnionList struct {
	ArticleId        int    `description:"文章id"`
	Title            string `description:"标题"`
	TitleEn          string `description:"英文标题 "`
	UpdateFrequency  string `description:"更新周期"`
	CreateDate       string `description:"创建时间"`
	PublishDate      string `description:"发布时间"`
	Body             string `description:"内容"`
	BodyHtml         string `description:"内容带有HTML标签"`
	Abstract         string `description:"摘要"`
	CategoryName     string `description:"一级分类"`
	SubCategoryName  string `description:"二级分类"`
	ExpertBackground string `description:"专家背景"`
	IsResearch       bool   `description:"是否属于研选"`
	Pv               int    `description:"PV"`
	ImgUrlPc         string `description:"图片链接"`
	CategoryId       string `description:"文章分类"`
	HttpUrl          string `description:"文章链接跳转地址"`
	IsNeedJump       bool   `description:"是否需要跳转链接地址"`
	Source           int    `description:"来源  1:文章, 2:图表"`
	Annotation       string `description:"核心观点"`
	HomeType         int    `description:"数据类型：0-纪要(默认); 1-微路演音频"`
	MicroAudioUnionList
}

// GetHomeNewestListUnionList 首页最新纪要-音频联合查询
func GetHomeNewestListUnionList(condition string, pars []interface{}, startSize, pageSize int) (list []*HomeNewestUnionList, err error) {
	sql := `SELECT
				id, article_id, title, title_en, update_frequency, create_date, publish_date, body, abstract, category_name, sub_category_name, expert_background, category_id, source, annotation,
				(SELECT count(1) FROM cygx_article_history_record_newpv as h WHERE h.article_id = art.article_id ) as pv,
				0 AS home_type, "" AS audio_title, "" AS audio_resource_url, 0 AS audio_type, "" AS audio_publish_time, 0 AS audio_chart_permission_id, "" AS audio_chart_permission_name,
				"" AS audio_play_seconds, "" AS audio_img_url, 0 AS audio_activity_id
			FROM
				cygx_article AS art
			WHERE
				art.publish_status = 1 `
	if condition != `` {
		sql += condition
	}
	sql += ` UNION ALL
			
			SELECT
				a.activity_voice_id, 0, "", "", "", "", b.activity_time, "", "", "", "", "", 0, 0, "",
				0, 1, a.voice_name, a.voice_url, 1, b.activity_time,
				b.chart_permission_id, b.chart_permission_name, a.voice_play_seconds AS audio_play_seconds, a.img_url AS audio_img_url, a.activity_id AS audio_activity_id
			FROM
				cygx_activity_voice AS a
			JOIN cygx_activity AS b ON a.activity_id = b.activity_id`
	sql += ` ORDER BY publish_date DESC`
	sql += ` LIMIT ?,?`
	_, err = orm.NewOrm().Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// GetHomeNewestListUnionCount 首页最新纪要-音频联合查询总数
func GetHomeNewestListUnionCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				COUNT(1) AS count
			FROM
				(
					SELECT
						art.id
					FROM
						cygx_article AS art
					WHERE
						art.publish_status = 1 `
	if condition != `` {
		sql += condition
	}
	sql += ` UNION ALL
				SELECT
					a.activity_voice_id
				FROM
					cygx_activity_voice AS a
				JOIN cygx_activity AS b ON a.activity_id = b.activity_id
			) z `
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// MicroRoadshowVideo 微路演视频
type MicroRoadshowVideo struct {
	VideoId             int       `orm:"column(video_id);pk" description:"视频ID"`
	VideoName           string    `description:"视频标题"`
	ChartPermissionId   int       `description:"行业ID"`
	ChartPermissionName string    `description:"行业名称"`
	IndustryId          int       `description:"产业ID"`
	IndustryName        string    `description:"产业名称"`
	PublishStatus       int       `description:"发布状态：0-未发布；1-已发布"`
	ModifyDate          time.Time `description:"更新时间"`
	PublishDate         time.Time `description:"发布时间"`
	VideoCounts         int       `description:"视频播放量"`
	VideoDuration       int       `description:"视频时长"`
	VideoUrl            string    `description:"视频地址"`
	CreateTime          time.Time `description:"创建时间"`
	ImgUrl              string    `description:"背景图链接"`
	DetailImgUrl        string    `description:"产业详情页背景图"`
}

// GetMicroRoadshowVideoById 主键获取微路演视频
func GetMicroRoadshowVideoById(videoId int) (item *MicroRoadshowVideo, err error) {
	sql := `SELECT * FROM cygx_micro_roadshow_video WHERE video_id = ? LIMIT 1`
	err = orm.NewOrm().Raw(sql, videoId).QueryRow(&item)
	return
}

func UpdateCygxMicroRoadshowVideo(videoId int) (err error) {
	sql := `UPDATE cygx_micro_roadshow_video SET video_counts = video_counts+1 WHERE video_id = ?  `
	o := orm.NewOrm()
	_, err = o.Raw(sql, videoId).Exec()
	return
}

func UpdateLastCygxMicroRoadshowVideoHistory(playSeconds string, lastId int) (err error) {
	o := orm.NewOrm()
	sql := ` UPDATE cygx_micro_roadshow_video SET play_seconds = play_seconds +? WHERE id=? `
	_, err = o.Raw(sql, playSeconds, lastId).Exec()
	return
}

// GetMicroRoadshowVideoByIndustryIds 根据行业ID查询产业视频列表
func GetMicroRoadshowVideoByIndustryIds(industrialIdArr []int) (list []*MicroRoadshowVideo, err error) {
	sql := `SELECT * FROM cygx_micro_roadshow_video WHERE industry_id in (` + utils.GetOrmInReplace(len(industrialIdArr)) + `)  and publish_status = 1 `
	_, err = orm.NewOrm().Raw(sql, industrialIdArr).QueryRows(&list)
	return
}

// GetMicroRoadshowVideoByIndustryId 根据行业ID查询产业视频列表
func GetMicroRoadshowVideoByIndustryId(industryId int) (item *MicroRoadshowVideo, err error) {
	sql := `SELECT * FROM cygx_micro_roadshow_video WHERE industry_id = ? and publish_status = 1`
	err = orm.NewOrm().Raw(sql, industryId).QueryRow(&item)
	return
}

type AddVideoCommnetReq struct {
	//Id         int    `description:"活动或产业ID"`
	SourceId   int    `description:"资源ID"`
	SourceType int    `description:"视频来源: 1-音频; 2-活动视频; 3-微路演视频 (不传默认为1)"`
	Content    string `description:"内容"`
	Title      string `description:"标题"`
}

type MicroRoadshowCollectReq struct {
	SourceId   int `description:"音频或视频ID"`
	SourceType int `description:"视频来源: 1-音频; 2-活动视频; 3-微路演视频 (不传默认为1)"`
}

// GetMicroRoadshowVideoListBycondition 根据搜索条件获取搜索列表
func GetMicroRoadshowVideoListBycondition(condition string, pars []interface{}, startSize, pageSize int) (list []*MicroRoadshowVideo, err error) {
	sql := `SELECT * FROM cygx_micro_roadshow_video WHERE  1 =1 `
	if condition != "" {
		sql += condition
	}
	sql += `  LIMIT ?,? `
	_, err = orm.NewOrm().Raw(sql, pars, startSize, pageSize).QueryRows(&list)
	return
}

// GetMicroRoadshowVideoList 获取已经发布的微路演视频
func GetMicroRoadshowVideoList() (list []*MicroRoadshowVideo, err error) {
	sql := `SELECT * FROM cygx_micro_roadshow_video WHERE  publish_status = 1`
	_, err = orm.NewOrm().Raw(sql).QueryRows(&list)
	return
}

// GetMicroRoadshowVideoByIndustryIdCount  根据行业ID查询产业视频是否存在
func GetMicroRoadshowVideoByIndustryIdCount(industryId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) count
			FROM cygx_micro_roadshow_video WHERE industry_id = ? and publish_status = 1`
	err = o.Raw(sql, industryId).QueryRow(&count)
	return
}