hongze_cygx/models/report.go

package models

import (
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"strconv"
	//"github.com/rdlucklib/rdluck_tools/paging"
)

type IndustrialManagementList struct {
	Paging *paging.PagingItem
	List   []*IndustrialManagement
}

type IndustrialManagement struct {
	IndustrialManagementId int                  `orm:"column(industrial_management_id);pk" description:"产业id"`
	IndustryName           string               `description:"产业名称"`
	RecommendedIndex       int                  `description:"推荐指数"`
	CategoryId             int                  `description:"文章分类ID"`
	LayoutTime             string               `description:"布局时间"`
	UpdateTime             string               `description:"更新时间"`
	IsRed                  bool                 `description:"是否标记红点"`
	IsTop                  bool                 `description:"是否置顶"`
	IsHot                  bool                 `description:"是否是热门"`
	IsFollow               int                  `description:"是否关注"`
	Analyst                string               `description:"分析师"`
	ArticleReadNum         int                  `description:"文章阅读数量"`
	AnalystList            []*IndustrialAnalyst `description:"分析师列表"`
	IndustrialSubjectList  []*IndustrialSubject `description:"标的列表"`
}

type IndustrialAnalyst struct {
	AnalystName            string `description:"分析师名称"`
	IndustrialManagementId int    `description:"产业id"`
}

type IndustrialSubject struct {
	IndustrialSubjectId    int    `orm:"column(industrial_subject_id);pk" description:"标的id"`
	IndustrialManagementId int    `description:"产业id"`
	SubjectName            string `description:"标的名称"`
}

//获取产业报告数量
func GetReportIndustrialCount(categoryId, industrialManagementId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) count 
FROM
	cygx_article AS a 
	INNER JOIN cygx_industrial_article_group_management as man_g ON man_g.article_id = a.article_id
WHERE
	a.publish_status = 1 
	AND category_id IN (SELECT
	category_id 
FROM
	cygx_report_mapping 
WHERE
	chart_permission_id = any( SELECT chart_permission_id FROM cygx_report_mapping WHERE category_id = ` + strconv.Itoa(categoryId) + ` ) 
	AND match_type_name = any( SELECT match_type_name FROM cygx_report_mapping WHERE category_id = ` + strconv.Itoa(categoryId) + ` ) )
	AND a.is_class = 1
	AND man_g.industrial_management_id = ?`
	err = o.Raw(sql, industrialManagementId).QueryRow(&count)
	return
}

//获取产业报告列表
func GetReportIndustrialList(pars []interface{}, categoryId, industrialManagementId, userId, startSize, pageSize int) (items []*ReportArticle, err error) {
	o := orm.NewOrm()
	sql := `SELECT *,( SELECT COUNT( 1 ) FROM cygx_article_history_record AS rec WHERE rec.user_id = ` + strconv.Itoa(userId) + ` AND rec.article_id = a.article_id ) AS readnum 
FROM
	cygx_article AS a 
	INNER JOIN cygx_industrial_article_group_management as man_g ON man_g.article_id = a.article_id
WHERE
	a.publish_status = 1 
	AND category_id  IN (SELECT
	category_id 
FROM
	cygx_report_mapping 
WHERE
	chart_permission_id = any( SELECT chart_permission_id FROM cygx_report_mapping WHERE category_id = ` + strconv.Itoa(categoryId) + ` ) 
	AND match_type_name = any( SELECT match_type_name FROM cygx_report_mapping WHERE category_id = ` + strconv.Itoa(categoryId) + ` ) )
    AND a.is_class = 1
	AND man_g.industrial_management_id = ?`
	sql += ` ORDER BY publish_date DESC LIMIT ?,? `
	_, err = o.Raw(sql, pars, industrialManagementId, startSize, pageSize).QueryRows(&items)
	return
}

//产业下所关联的文章分类列表
func IndustrialToArticleCategory(industrialManagementId, chartPermissionId int) (items []*IndustrialToArticleCategoryRep, err error) {
	o := orm.NewOrm()
	sql := `SELECT map.match_type_name,map.category_id
    FROM cygx_report_mapping AS map
	INNER JOIN cygx_article AS art ON art.category_id = map.category_id
	INNER JOIN cygx_industrial_article_group_management AS man_g ON man_g.article_id = art.article_id
	WHERE map.report_type = 2
	AND map.is_report = 1
    AND art.is_report = 1
    AND art.publish_status = 1
	AND man_g.industrial_management_id =?
	AND map.chart_permission_id = ?
	GROUP BY map.match_type_name`
	_, err = o.Raw(sql, industrialManagementId, chartPermissionId).QueryRows(&items)
	return
}

//判断用户是否阅读该产业下，某一分类的文章
func IndustrialUserRecordArticleCount(userId, industrialManagementId, categoryId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT
	COUNT(1) count
FROM
	cygx_article_history_record 
WHERE
	article_id = ( SELECT article_id FROM cygx_article WHERE article_id IN (SELECT article_id FROM cygx_industrial_article_group_management WHERE industrial_management_id = ? ) AND category_id = ? ORDER BY publish_date DESC LIMIT 0, 1 ) 
	AND user_id = ? `
	err = o.Raw(sql, industrialManagementId, categoryId, userId).QueryRow(&count)
	return
}

//获取最新文章
func GetNewIndustrialUserRecordArticle(industrialManagementId, categoryId int) (item *ArticleDetail, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM cygx_article WHERE article_id IN (SELECT article_id FROM cygx_industrial_article_group_management WHERE industrial_management_id = ? ) AND category_id = ? ORDER BY publish_date DESC LIMIT 0, 1`
	err = o.Raw(sql, industrialManagementId, categoryId).QueryRow(&item)
	return
}

//获取最新文章
func GetNewArticleByCategoryId(categoryId int) (item *ArticleDetail, err error) {
	o := orm.NewOrm()
	sql := ` SELECT * FROM cygx_article WHERE  category_id = ? ORDER BY publish_date DESC LIMIT 0, 1`
	err = o.Raw(sql, categoryId).QueryRow(&item)
	return
}

func GetIndustrialManagementIdsBykeyWord(condition string) (industrialManagementIds string, err error) {
	sql := `SELECT GROUP_CONCAT(DISTINCT industrial_management_id  SEPARATOR ',') AS industrial_management_ids FROM cygx_industrial_management WHERE` + condition
	o := orm.NewOrm()
	err = o.Raw(sql).QueryRow(&industrialManagementIds)
	return
}

type ReportArticleWhichIndustrial struct {
	ArticleId    int    `description:"文章id"`
	Title        string `description:"标题"`
	PublishDate  string `description:"发布时间"`
	IndustryName string `description:"产业名称"`
	SubjectName  string `description:"标的名称"`
	NickName     string `description:"作者昵称"`
	IsRed        bool   `description:"是否标记红点"`
	Readnum      int    `description:"阅读数量"`
	IsResearch   bool   `description:"是否属于研选"`
	Pv           int    `description:"PV"`
	ImgUrlPc     string `description:"图片链接"`
}

type ReportArticleWhichIndustrialRepList struct {
	HaveResearch bool               `description:"是否有研选权限"`
	Paging       *paging.PagingItem `description:"分页数据"`
	NickName     string             `description:"作者昵称"`
	IndustryName string             `description:"产业名称"`
	List         []*ReportArticleWhichIndustrial
}

//列表
func IndustrialToArticleWhichDepartment(condition string, pars []interface{}, uid, startSize, pageSize int) (items []*ReportArticleWhichIndustrial, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			art.* ,m.industry_name,d.nick_name,
			(SELECT count(1) FROM cygx_article_history_record_newpv as h WHERE h.article_id = art.article_id ) as pv,
			( SELECT COUNT( 1 ) FROM cygx_article_history_record_newpv AS rec WHERE rec.user_id =  ` + strconv.Itoa(uid) + ` AND rec.article_id = art.article_id ) AS readnum 
			FROM
			cygx_article AS art
			INNER JOIN cygx_industrial_article_group_management as mg ON mg.article_id = art.article_id 
			INNER JOIN cygx_industrial_management as m ON m.industrial_management_id = mg.industrial_management_id
			INNER JOIN cygx_article_department as d ON d.department_id = art.department_id
			WHERE 1 = 1 
			AND art.publish_status = 1`
	if condition != "" {
		sql += condition
	}
	sql += ` LIMIT ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

func GetWhichDepartmentCount(condition string) (count int, err error) {
	o := orm.NewOrm()
	//sql := `SELECT COUNT(*) count
	//		FROM(
	//		SELECT COUNT(1) count
	//		FROM
	//		cygx_article AS art
	//		INNER JOIN cygx_industrial_article_group_management as mg ON mg.article_id = art.article_id
	//		INNER JOIN cygx_industrial_management as m ON m.industrial_management_id = mg.industrial_management_id
	//		INNER JOIN cygx_article_department as d ON d.department_id = art.department_id
	//		WHERE 1 = 1
	//		AND art.publish_status = 1`
	//if condition != "" {
	//	sql += condition
	//}
	//sql += ` ) AS num`

	sql := `SELECT COUNT(1) count 
			FROM
			cygx_article AS art
			INNER JOIN cygx_industrial_article_group_management as mg ON mg.article_id = art.article_id 
			INNER JOIN cygx_industrial_management as m ON m.industrial_management_id = mg.industrial_management_id
			INNER JOIN cygx_article_department as d ON d.department_id = art.department_id
			WHERE 1 = 1 
			AND art.publish_status = 1`
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql).QueryRow(&count)
	return
}

type IsShow struct {
	IsShow bool `description:"是否展示"`
}

//获取用户是否有查看权限
func GetUserIsAdminCount(mobile string) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) count  FROM admin 
			WHERE
 			mobile = ?
			AND (group_id IN (11, 13, 14, 15, 16, 17) OR department_id = 3 )   AND  enabled = 1`
	err = o.Raw(sql, mobile).QueryRow(&count)
	return
}

type ReportDetailRoadshow struct {
	ArticleId        int    `description:"报告Id"`
	Title            string `description:"标题"`
	Department       string `orm:"column(seller_and_mobile)"description:"作者"`
	PublishDate      string `description:"发布时间"`
	VideoUrl         string `description:"链接"`
	VideoPlaySeconds string `description:"时长"`
	VideoName        string `description:"音频名称"`
	Abstract         string `description:"摘要"`
	Body             string `description:"内容"`
	CategoryName     string `description:"行业名称"`
	ReportLink       string `orm:"column(link_article_id)"description:"报告链接"`
}

type RoadshowDetailResp struct {
	Detail        *ReportDetailRoadshow
	HasPermission int `description:"1:有该行业权限,正常展示，2：无该行业权限，不存在权益客户下，3:无该品类权限,已提交过申请，4：无该行业权限,未提交过申请，5：潜在客户，未提交过申请，6：潜在客户,已提交过申请"`
	HasFree       int `description:"1:已付费(至少包含一个品类的权限)，2:未付费(没有任何品类权限)"`
}

func GetReportRoadshowDetailById(articleId int) (item *ReportDetailRoadshow, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM cygx_article WHERE article_id = ? AND publish_status = 1  `
	err = o.Raw(sql, articleId).QueryRow(&item)
	return
}

type CygxIndustryAndArticleList struct {
	ArticleId    int    `orm:"column(article_id);pk"description:"报告id"`
	Title        string `description:"标题"`
	PublishDate  string `description:"发布时间"`
	IndustryName string `description:"产业名称"`
	SubjectName  string `description:"标的名称"`
}

type CygxIndustrySearchList struct {
	ArtList []*CygxIndustryAndArticleList `description:"文章列表"`
	IndList []*IndustrialManagement       `description:"产业列表"`
}

type CygxIndustrySearchListPc struct {
	DepartmentList []*CygxArticleDepartmentRepPc
	IndList        []*IndustrialManagement `description:"产业列表"`
}

//列表
func GetCygxIndustryAndArticleList(keyWord string) (items []*CygxIndustryAndArticleList, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			art.title,
			art.article_id,
			art.publish_date
		FROM
			cygx_article AS art
			LEFT JOIN cygx_industrial_article_group_subject AS sg ON sg.article_id = art.article_id
			LEFT JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = mg.article_id
			LEFT JOIN cygx_industrial_management AS m ON m.industrial_management_id = mg.industrial_management_id 
		WHERE
		category_name LIKE '%研选%'
		AND art.article_id IN ( SELECT article_id FROM cygx_industrial_article_group_subject AS sg WHERE sg.industrial_subject_id IN ( SELECT industrial_subject_id FROM cygx_industrial_subject WHERE subject_name LIKE '%` + keyWord + `%' ) GROUP BY sg.article_id ) 
		OR ( art.article_id IN ( SELECT article_id FROM cygx_industrial_article_group_management AS mg WHERE mg.industrial_management_id IN ( SELECT industrial_management_id FROM cygx_industrial_management WHERE industry_name LIKE '%` + keyWord + `%' ) GROUP BY mg.article_id ) AND category_name LIKE '%研选%' )
		GROUP BY
			art.article_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetArticleIdsBySubId(subjectId string) (articleIds string, err error) {
	o := orm.NewOrm()
	sql := `SELECT	GROUP_CONCAT( DISTINCT a.article_id  SEPARATOR ',' ) AS articleIds 
            FROM cygx_article AS a 
			WHERE subject_ids  LIKE '%` + subjectId + `%'`
	err = o.Raw(sql).QueryRow(&articleIds)
	return
}

//end

//用户收藏榜start
type ArticleCollectionResp struct {
	ArticleId              int    `description:"文章id"`
	Title                  string `description:"标题"`
	PublishDate            string `description:"发布时间"`
	IndustrialManagementId int    `description:"产业Id"`
	IndustryName           string `description:"产业名称"`
	DepartmentId           int    `description:"作者Id"`
	NickName               string `description:"作者昵称"`
	Pv                     int    `description:"PV"`
	CollectNum             int    `description:"收藏人数"`
}

type ArticleCollectionLIstResp struct {
	List []*ArticleCollectionResp
}

//列表
func GetArticleCollectionList(permissionName string) (items []*ArticleCollectionResp, err error) {
	o := orm.NewOrm()
	sql := `SELECT
			a.article_id,
			a.title,
			date_format( a.publish_date, '%Y-%m-%d' ) AS publish_date,
			m.industry_name,
			m.industrial_management_id,
			d.nick_name,
			d.department_id,
			( SELECT count( 1 ) FROM cygx_article_history_record_newpv AS h WHERE h.article_id = a.article_id ) AS pv,
			( SELECT count( 1 ) FROM cygx_article_collect AS ac WHERE ac.article_id = a.article_id ) AS collect_num 
		FROM
			cygx_article AS a
			INNER JOIN cygx_industrial_article_group_management AS mg ON mg.article_id = a.article_id
			INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id = mg.industrial_management_id
			INNER JOIN cygx_article_department AS d ON d.department_id = a.department_id 
		WHERE
			1 = 1 
			AND a.is_report = 1 
			AND a.category_name LIKE '%` + permissionName + `%' 
			AND publish_status = 1 
		GROUP BY
			m.industrial_management_id 
		ORDER BY
			collect_num DESC,
			publish_date DESC 
			LIMIT 15`
	_, err = o.Raw(sql).QueryRows(&items)
	return
} //end