hongze_task/models/report_view.go

package models

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

type ResearchReportViewers struct {
	ResearchReportName string
	ReportType         string
	Count              int //访问次数
	UserCount          int //访问人数
	CreateDate         string
}

func GetResearchReportViewers(reportType string) (items []*ResearchReportViewers, err error) {
	sql := `select rr.research_report_name,rr.type as report_type,count(*) AS count,count(DISTINCT user_id) AS user_count
		from user_view_history uvh
		inner join research_report rr on rr.research_report_id = uvh.research_report_id
		where rr.type = ?
		group by rr.research_report_id
		order by rr.research_report_date desc`
	o := orm.NewOrm()
	_, err = o.Raw(sql, reportType).QueryRows(&items)
	return
}

func GetRddpReportViewers() (items []*ResearchReportViewers, err error) {
	sql := `select b.title as research_report_name, replace(SUBSTRING(b.create_time,6,5),'-','') as create_date ,b.classify_name_second AS report_type,count(*) AS count,count(DISTINCT a.user_id) AS user_count 
			from report_view_record as a
			inner join report as b on a.report_id=b.id
			group by b.id
			order by b.create_time desc
			`
	o := orm.NewOrmUsingDB("rddp")
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type ReportType struct {
	TypeName  string
	TypeValue string
}

type ResearchReportViewersDetail struct {
	RealName           string
	CompanyName        string
	CreatedTime        string //访问时间
	ResearchReportName string
	ReportVariety      string
	ResearchReportType string
	ReportCreateDate   string
}

func GetResearchReportViewersDetail(startTime, endTime, reportType string) (items []*ResearchReportViewersDetail, err error) {
	sql := `
			select u.real_name,c.company_name,uvh.created_time,rr.research_report_name,ifnull(rct.report_chapter_type_name,rr.report_variety) as report_variety,rr.type as research_report_type
			from user_view_history uvh
			inner join wx_user u on u.user_id = uvh.user_id
			inner join company c on c.company_id = u.company_id
			inner join research_report rr on rr.research_report_id = uvh.research_report_id
			left join research_report_type rrt on rrt.research_report_type_id = uvh.research_report_type_id
			left join report_chapter_type rct on rct.report_chapter_type_id	= rrt.type_id
			where uvh.created_time > ?
            and uvh.created_time <= ?
			and rr.type =?
			and c.company_id not in (1)
`
	o := orm.NewOrm()
	_, err = o.Raw(sql, startTime, endTime, reportType).QueryRows(&items)
	return
}

func GetRddpReportViewersDetail(startTime, endTime string) (items []*ResearchReportViewersDetail, err error) {
	sql := `
			SELECT u.real_name,c.company_name,uvh.create_time as created_time,REPLACE(SUBSTRING(r.create_time,6,5),'-','') AS report_create_date,r.title as research_report_name,r.classify_name_second as research_report_type
			FROM hongze_rddp.report_view_record AS uvh
			INNER JOIN hongze_rddp.report AS r ON uvh.report_id=r.id
			INNER JOIN wx_user u ON u.user_id = uvh.user_id
			INNER JOIN company c ON c.company_id = u.company_id
			WHERE uvh.create_time >?
            AND uvh.create_time <=?
			AND c.company_id NOT IN (1)
			AND r.classify_name_first not in ("周报","大宗商品","数据点评","会议纪要","年报合集","需求报告")
			AND r.classify_id_second not in (113,114,127,134,141,149)
			ORDER BY uvh.create_time DESC
`
	o := orm.NewOrm()
	_, err = o.Raw(sql, startTime, endTime).QueryRows(&items)
	return
}

type RddpWeekReportViewersDetail struct {
	RealName           string
	CompanyName        string
	CreatedTime        string //访问时间
	ResearchReportName string
	ReportVariety      string
	ResearchReportType string
	ReportCreateDate   string
	ChapterTitle       string
	ClassifyNameFirst  string
	ClassifyNameSecond string
}

func GetRddpWeekReportViewersDetail(startTime, endTime string) (items []*RddpWeekReportViewersDetail, err error) {
	sql := `
			SELECT
	u.real_name,c.company_name,uvh.create_time AS created_time,REPLACE ( SUBSTRING( r.create_time, 6, 5 ), '-', '' ) AS report_create_date,
	r.title AS research_report_name,r.classify_name_second AS research_report_type ,rc.title chapter_title,rc.type_name as report_variety
FROM
	hongze_rddp.report_view_record AS uvh
	INNER JOIN hongze_rddp.report AS r ON uvh.report_id = r.id
	INNER JOIN hongze_rddp.report_chapter AS rc ON uvh.report_id = rc.report_id and uvh.report_chapter_id=rc.report_chapter_id
	INNER JOIN wx_user u ON u.user_id = uvh.user_id
	INNER JOIN company c ON c.company_id = u.company_id 
			WHERE uvh.create_time >?
            AND uvh.create_time <=?
			AND c.company_id NOT IN (1)
			AND r.classify_name_first = "周报"
			ORDER BY uvh.create_time DESC
`
	o := orm.NewOrm()
	_, err = o.Raw(sql, startTime, endTime).QueryRows(&items)
	return
}

func GetRddpTwoWeekReportViewersDetail(startTime, endTime string) (items []*RddpWeekReportViewersDetail, err error) {
	sql := `
			SELECT
	u.real_name,c.company_name,uvh.create_time AS created_time,REPLACE ( SUBSTRING( r.create_time, 6, 5 ), '-', '' ) AS report_create_date,
	r.title AS research_report_name,r.classify_name_second AS research_report_type,r.classify_name_second 
FROM
	hongze_rddp.report_view_record AS uvh
	INNER JOIN hongze_rddp.report AS r ON uvh.report_id = r.id
	INNER JOIN wx_user u ON u.user_id = uvh.user_id
	INNER JOIN company c ON c.company_id = u.company_id 
			WHERE uvh.create_time >?
            AND uvh.create_time <=?
			AND c.company_id NOT IN (1)
			AND r.classify_name_first = "大宗商品"
			ORDER BY uvh.create_time DESC
`
	o := orm.NewOrm()
	_, err = o.Raw(sql, startTime, endTime).QueryRows(&items)
	return
}

// GetRddpMonthReportViewersDetail 获取月度数据
func GetRddpMonthReportViewersDetail(startTime, endTime string) (items []*RddpWeekReportViewersDetail, err error) {
	sql := `
			SELECT
	u.real_name,c.company_name,uvh.create_time AS created_time,REPLACE ( SUBSTRING( r.create_time, 6, 5 ), '-', '' ) AS report_create_date,
	r.title AS research_report_name,r.classify_name_second AS research_report_type,r.classify_name_second 
FROM
	hongze_rddp.report_view_record AS uvh
	INNER JOIN hongze_rddp.report AS r ON uvh.report_id = r.id
	INNER JOIN wx_user u ON u.user_id = uvh.user_id
	INNER JOIN company c ON c.company_id = u.company_id 
			WHERE uvh.create_time >?
            AND uvh.create_time <=?
			AND c.company_id NOT IN (1)
			AND r.classify_id_second in (113,114,127,134,141,149)
			ORDER BY uvh.create_time DESC
`
	o := orm.NewOrm()
	_, err = o.Raw(sql, startTime, endTime).QueryRows(&items)
	return
}

// GetRddpDataReviewReportViewersDetail 获取数据点评数据
func GetRddpDataReviewReportViewersDetail(startTime, endTime string) (items []*RddpWeekReportViewersDetail, err error) {
	sql := `
			SELECT
	u.real_name,c.company_name,uvh.create_time AS created_time,REPLACE ( SUBSTRING( r.create_time, 6, 5 ), '-', '' ) AS report_create_date,
	r.title AS research_report_name,r.classify_name_second AS research_report_type,r.classify_name_second,r.classify_name_first 
FROM
	hongze_rddp.report_view_record AS uvh
	INNER JOIN hongze_rddp.report AS r ON uvh.report_id = r.id
	INNER JOIN wx_user u ON u.user_id = uvh.user_id
	INNER JOIN company c ON c.company_id = u.company_id 
			WHERE uvh.create_time >?
            AND uvh.create_time <=?
			AND c.company_id NOT IN (1)
			AND r.classify_name_first IN ("数据点评","会议纪要","年报合集","需求报告")
			ORDER BY uvh.create_time DESC
`
	o := orm.NewOrm()
	_, err = o.Raw(sql, startTime, endTime).QueryRows(&items)
	return
}

type HistoryViewTimes struct {
	RealName    string
	CompanyName string
	ViewCount   int
	CreateTime  string
	UserId      int
}

func GetHistoryViewTimes() (items []*HistoryViewTimes, err error) {
	sql := `select u.user_id,u.real_name,c.company_name,count(distinct(DATE_FORMAT(uvh.created_time,'%Y-%m-%d'))) AS view_count,max(uvh.created_time) AS create_time
	from user_view_history uvh
	inner join wx_user u on u.user_id = uvh.user_id
	inner join company c on c.company_id = u.company_id
	and c.company_id not in (1,16)
	group by uvh.user_id 
	order by max(uvh.created_time) desc `
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetRddpHistoryViewTimes() (items []*HistoryViewTimes, err error) {
	sql := `select u.user_id,u.real_name,c.company_name,count(distinct(DATE_FORMAT(rvr.create_time,'%Y-%m-%d'))) AS view_count ,max(rvr.create_time) AS create_time
	from hongze_rddp.report_view_record rvr
	inner join wx_user u on u.user_id = rvr.user_id
	inner join company c on c.company_id = u.company_id
	and c.company_id not in (1,16)
	group by rvr.user_id 
	order by max(rvr.create_time) desc `
	o := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

//每日资讯
//func GetAdvisoryViewersDetail(startTime, endTime string) (items []*ResearchReportViewersDetail, err error) {
//	sql := `
//		SELECT
//			u.real_name,
//			c.company_name,
//			aavr.create_time AS created_time,
//			art.title AS research_report_name,
//			art.chart_permission_name AS research_report_type
//		FROM
//			advisory_article_view_record AS aavr
//			INNER JOIN advisory_article AS art ON aavr.article_id = art.advisory_article_id
//			INNER JOIN wx_user u ON u.user_id = aavr.user_id
//			INNER JOIN company c ON c.company_id = u.company_id
//		WHERE
//			aavr.create_time > ?
//			AND aavr.create_time <= ?
//			AND c.company_id NOT IN ( 1 )
//		ORDER BY
//			aavr.create_time DESC`
//	o := orm.NewOrm()
//	_, err = o.Raw(sql, startTime, endTime).QueryRows(&items)
//	return
//}

func GetAdvisoryViewersDetail(startTime, endTime string) (items []*ResearchReportViewersDetail, err error) {
	sql := `
		SELECT
			 u.real_name,
			 c.company_name,
			 aua.create_time AS created_time,
			 cha.permission_name AS research_report_name
		FROM
			advisory_user_chart_article_record AS aua 
			INNER JOIN chart_permission AS cha ON aua.chart_permission_id = cha.chart_permission_id 
			INNER JOIN wx_user u ON u.user_id = aua.user_id 
			INNER JOIN company c ON c.company_id = u.company_id 
		WHERE
			aua.create_time > ?
			AND aua.create_time <= ?
			AND c.company_id NOT IN ( 1 ) 
		ORDER BY
			aua.create_time DESC
	`
	o := orm.NewOrm()
	_, err = o.Raw(sql, startTime, endTime).QueryRows(&items)
	return
}