hongze_cygx/models/report_mapping.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
)

type CygxReportMapping struct {
	Id                  int    `description:"id"`
	ChartPermissionId   int    `description:"行业ID"`
	CategoryId          int    `description:"分类ID"`
	SubCategoryId       int    `description:"主题"`
	CategoryName        int    `description:"主题"`
	ChartPermissionName string `description:"行业名称"`
	SubCategoryName     int    `description:"主题"`
	MatchTypeName       string `description:"分类名称"`
	ReportType          int    `description:"报告类型，2产业报告，1行业报告"`
	Sort                int    `description:"排序"`
	IsCustom            int    `description:"是否属于自定义的匹配类型 ，1是，0否"`
	IsSummary           int    `description:"是否是纪要库，1是，0否"`
	IsReport            int    `description:"是否是报告，1是，0否"`
	PermissionType      int    `description:"1主观，2客观"`
	CategoryIdSet       int    `description:"分类ID手动设置的分类"`
	PolymerizationId    string `description:"分类聚合ID"`
	CeLueFieldId        string `description:"策略平台领域ID"`
}

type ReportMapping struct {
	CategoryId          int    `description:"分类ID"`
	CategoryIdSet       int    `description:"分类ID手动设置的分类"`
	SubCategoryName     string `description:"主题"`
	MatchTypeName       string `description:"分类名称"`
	ChartPermissionName string `description:"行业名称"`
	ChartPermissionId   int    `description:"行业ID"`
	IsRed               bool   `description:"是否标红"`
	PermissionType      int    `description:"1主观，2客观"`
	PolymerizationId    string `description:"分类聚合ID"`
	CeLueFieldId        string `description:"策略平台领域ID"`
	IsFllow             bool   `description:"是否关注"`
}

type ReportMappingHome struct {
	CategoryId      int    `description:"分类ID"`
	SubCategoryName string `description:"主题名称"`
	MatchTypeName   string `description:"匹配类型"`
	IsRed           bool   `description:"是否标红"`
	ListGroup       int    `description:"第几组"`
}

type TradeReportMapping struct {
	CategoryId       int    `description:"分类ID"`
	CategoryIdSet    int    `description:"分类映射id"`
	PolymerizationId string `description:"分类聚合ID"`
	SubCategoryName  string `description:"主题名称"`
	MatchTypeName    string `description:"匹配类型"`
	IsRed            bool   `description:"是否标红"`
	UpdateTime       string `description:"更新时间"`
	Readnum          int    `description:"阅读数量"`
}

type ReportMappingResp struct {
	List []*ReportMapping
}

type ReportMappingHomeResp struct {
	List       []*ReportMappingHome
	ListGroup1 []*ReportMappingHome
	ListGroup2 []*ReportMappingHome
	ListGroup3 []*ReportMappingHome
	ListGroup4 []*ReportMappingHome
}

type TradeReportMappingResp struct {
	List []*TradeReportMapping
}

// 获取策略下面的所有分类
func GetReportMappingStrategyAll() (items []*ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_report_mapping WHERE report_type=1 AND chart_permission_id=23 ORDER BY sort ASC `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取策略下面的所有分类
func GetReportMappingStrategyAllXzs() (items []*IndustrialManagementRep, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			match_type_name AS industry_name,
			category_id AS industrial_management_id,
			chart_permission_id
			FROM
				cygx_report_mapping 
			WHERE
				report_type = 1 
				AND chart_permission_name IN ('策略','固收')
			ORDER BY
				sort ASC `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取策略下面的所有分类
func GetReportMappingStrategyHomeAll(chartPermissionId int) (items []*ReportMappingHome, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	re.category_id,re.sub_category_name,re.match_type_name
FROM
	cygx_report_mapping AS re
	INNER JOIN cygx_article AS art ON art.category_id = re.category_id 
WHERE
	re.report_type = 1 
	AND re.chart_permission_id = ? 
GROUP  BY
	re.match_type_name
ORDER BY
	sort DESC , art.publish_date DESC`
	_, err = o.Raw(sql, chartPermissionId).QueryRows(&items)
	return
}

// 获取策略下面的所有分类
func GetReportMappingStrategyHomeAllByCygx(chartPermissionId int) (items []*ReportMappingHome, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	re.id AS category_id,
	re.list_group,
	re.match_type_name 
FROM
	cygx_report_mapping_cygx AS re
	INNER JOIN cygx_report_mapping_group AS mg ON mg.id_cygx = re.id
	INNER JOIN cygx_article AS art ON art.category_id = mg.category_id_celue 
WHERE
	1 = 1 
	AND re.chart_permission_id = ? 
	AND re.report_type = 1 
	AND re.list_group > 0 
GROUP BY
	re.match_type_name 
ORDER BY
	sort DESC,
	art.publish_date DESC`
	_, err = o.Raw(sql, chartPermissionId).QueryRows(&items)
	return
}

// 行业列表
func GetTradeAll(ChartPermissionId int) (items []*TradeReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			re.category_id,
			re.category_id_set,
			re.polymerization_id,
			re.sub_category_name,
			re.match_type_name 
		FROM
			cygx_report_mapping AS re 
		WHERE
			re.chart_permission_id = ? 
			AND re.report_type = 1 
			AND re.category_id NOT IN ( 67 ) 
		ORDER BY
			re.sort DESC `
	_, err = o.Raw(sql, ChartPermissionId).QueryRows(&items)
	return
}

// 行业列表
func GetTradeArticleAndProductInterior(ChartPermissionId int) (items []*TradeReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			map.sort,
			map.match_type_name,
			map.id AS category_id 
		FROM
			cygx_report_mapping_cygx AS map
			INNER JOIN cygx_report_mapping_group AS g ON g.id_cygx = map.id
			INNER JOIN cygx_report_mapping_celue AS cl ON cl.category_id = g.category_id_celue
			INNER JOIN cygx_article AS art ON art.category_id = cl.category_id 
		WHERE
			1 = 1 
			AND map.report_type = 1 
			AND map.is_report = 1 
			AND art.is_report = 1 
			AND art.publish_status = 1 
			AND map.chart_permission_id = ? 
		GROUP BY
			match_type_name UNION ALL
		SELECT
			map.sort,
			map.match_type_name,
			map.id AS category_id 
		FROM
			cygx_report_mapping_cygx AS map
			INNER JOIN cygx_industrial_management AS im ON im.chart_permission_id = map.chart_permission_id
			INNER JOIN cygx_product_interior AS art ON art.match_type_id = map.id 
		WHERE
			1 = 1 
			AND art.STATUS = 1 
			AND art.visible_range = 1 
			AND map.report_type = 1 
			AND map.chart_permission_id = ? 
		GROUP BY
			match_type_name 
		ORDER BY
			sort DESC `
	_, err = o.Raw(sql, ChartPermissionId, ChartPermissionId).QueryRows(&items)
	return
}

type IndustrialToArticleCategoryRep struct {
	CategoryId    int    `description:"分类ID"`
	MatchTypeName string `description:"匹配类型"`
	IsRed         bool   `description:"是否标红"`
}

type IndustrialToArticleCategoryListRep struct {
	AuthInfo               *UserPermissionAuthInfo
	LayoutTime             string `description:"布局时间"`
	IndustryName           string `description:"产业名称"`
	IndustrialManagementId int    `description:"产业D"`
	IsShowFollowButton     bool   `description:"是否展示关注取关按钮"`
	IsFollowButton         bool   `description:"是否关注"`
	IndustryVideo          *MicroVideoSimpleInfo
	List                   []*IndustrialToArticleCategoryRep
}

// 通过分类ID获取详情
func GetdetailByCategoryId(categoryId int) (item *ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_report_mapping WHERE report_type=1 AND category_id=? `
	err = o.Raw(sql, categoryId).QueryRow(&item)
	return
}

// 通过分类ID获取详情
func GetdetailByCategoryIdSet(categoryId int) (ids string, err error) {
	o := orm.NewOrm()
	sql := `SELECT
		GROUP_CONCAT( DISTINCT category_id SEPARATOR ',' ) AS ids 
	FROM
		cygx_report_mapping 
	WHERE
		category_id_set = ? `
	err = o.Raw(sql, categoryId).QueryRow(&ids)
	return
}

// 通过分类ID获取详情
func GetdetailByCategoryIdPush(categoryId int) (item *ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_report_mapping WHERE category_id=?  LIMIT 1 `
	err = o.Raw(sql, categoryId).QueryRow(&item)
	return
}

// 通过分类ID获取详情主观客观
func GetdetailByCategoryIdSando(categoryId int) (item *ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_report_mapping WHERE permission_type>0 AND category_id=?  LIMIT 1`
	err = o.Raw(sql, categoryId).QueryRow(&item)
	return
}

// 通过分类ID获取详情
func GetdetailByCategoryIdOne(categoryId int) (item *ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_report_mapping WHERE category_id=?   AND report_type = 2 LIMIT 1`
	err = o.Raw(sql, categoryId).QueryRow(&item)
	return
}

// 通过分类ID获取详情
func GetdetailByCategoryIdOneByHangye(categoryId int) (item *ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_report_mapping WHERE  category_id=?   AND report_type = 1 LIMIT 1`
	err = o.Raw(sql, categoryId).QueryRow(&item)
	return
}

// 判断该分类下最新的文章用户是否阅读
func CheckThisCategoryNewArticleIsRead(uid, categoryId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) count
			FROM cygx_article_history_record 
			WHERE user_id = ? 
			AND article_id = ( SELECT article_id FROM	cygx_article WHERE	category_id = ? 	AND is_class = 1 	AND is_report = 1 	AND is_filter = 0 ORDER BY publish_date DESC LIMIT 1)`
	err = o.Raw(sql, uid, categoryId).QueryRow(&count)
	return
}

// 获取策略下面的所有分类
func GetMatchTypeNamenNotNull() (items []*ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT category_id,category_id_set,match_type_name FROM cygx_report_mapping WHERE match_type_name <> ''`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type ReportMappingStatistical struct {
	CategoryId      int    `description:"分类ID"`
	SubCategoryName string `description:"主题名称"`
	MatchTypeName   string `description:"匹配类型"`
	IsSummary       string `description:"是否属于纪要，1 是，0否"`
	IsClass         string `description:"是否归类，1 是，0否"`
	Pv              int    `description:"Pv"`
	Uv              int    `description:"Uv"`
}

// 获取报表
func GetStatisticalReportArtilce(chartPermissionId int) (items []*ReportMappingStatistical, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	m.category_id,
	m.chart_permission_name,
	a.article_id,
	a.title,
	a.is_summary,
	a.is_class,
	(SELECT count( 1 ) FROM cygx_article_history_record AS h  WHERE	h.article_id = a.article_id 	) AS pv,
	(SELECT COUNT( DISTINCT h.user_id ) FROM cygx_article_history_record AS h   WHERE h.article_id = a.article_id ) AS uv
FROM
	cygx_report_mapping AS m
	INNER JOIN cygx_article AS a ON a.category_id = m.category_id 
WHERE
	m.chart_permission_id = ?
	and a.is_class= 1
	and a.is_report= 1
	and m.id !=28
	and a.is_filter= 0
	GROUP BY a.article_id`
	_, err = o.Raw(sql, chartPermissionId).QueryRows(&items)
	return
}

// 获取报表
func GetStatisticalReportArtilceExpert() (items []*ReportMappingStatistical, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	a.article_id,
	a.title,
	a.is_summary,
	a.is_class,
	(SELECT count( 1 ) FROM cygx_article_history_record AS h  WHERE	h.article_id = a.article_id ) AS pv,
	(SELECT COUNT( DISTINCT h.user_id ) FROM cygx_article_history_record AS h   WHERE h.article_id = a.article_id ) AS uv
	FROM
		cygx_article AS a
	WHERE
		a.category_name LIKE '%研选%'`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 通过分类ID获取详情 处理路演精华的映射
func GetdetailByCategoryIdLyjh(categoryId int) (item *ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	* 
FROM
	cygx_report_mapping 
WHERE
	sub_category_name = '路演精华' 
	AND chart_permission_id = ( SELECT chart_permission_id FROM cygx_report_mapping WHERE category_id = ? ) 
	LIMIT 1 `
	err = o.Raw(sql, categoryId).QueryRow(&item)
	return
}

// 获取分类
func GetReportMappingList(condition string, pars []interface{}) (items []*ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_report_mapping WHERE  1=1 `
	if condition != `` {
		sql += condition
	}
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// 获取所有的报告分类
func GetReportMappingByPermissionName(chartPermissionName string) (items []*ReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				* 
			FROM
				cygx_report_mapping
			WHERE
				1 = 1 
				AND chart_permission_name = ? `
	_, err = o.Raw(sql, chartPermissionName).QueryRows(&items)
	return
}

// 获取所有的报告分类
func GetReportMapping() (items []*CygxReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT
				* 
			FROM
				cygx_report_mapping `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 通过分类ID对应的系列名称
func GetdetailByCategoryIdLabel(categoryId int) (item *CygxReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			c.id,c.match_type_name
			FROM
			cygx_report_mapping_group as g
			INNER JOIN cygx_report_mapping_cygx as c ON c.id = g.id_cygx
			WHERE
			category_id_celue = ? LIMIT 1`
	err = o.Raw(sql, categoryId).QueryRow(&item)
	return
}

func GetCygxReportMappingById(id int) (items *CygxReportMapping, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_report_mapping_cygx WHERE id = ?`
	err = o.Raw(sql, id).QueryRow(&items)
	return
}