hongze_open_api/models/tables/company_user.go

package tables

import (
	"fmt"
	"hongze/hongze_open_api/utils"
	"rdluck_tools/orm"
	"time"
)

type ViewReportList struct {
	CompanyName        string `description:"公司名称" json:"company_name"`
	RealName           string `description:"用户名称" json:"user_name"`
	Mobile             string `description:"手机号" json:"mobile"`
	Email              string `description:"邮箱" json:"email"`
	ResearchReportName string `description:"报告标题" json:"report_name"`
	CreatedTime        string `description:"报告阅读时间" json:"created_time"`
	ReportType         string `description:"报告类型 'day 晨报'、'week 周报'、'twoweek 双周报'、'month 月报'、'rddp 日度点评'、'cygx 查研观向'、'advisory 每日商品聚焦'" json:"-"`
	TxtType            string `description:"类型 ficc:ficc  、 rights：权益"  json:"-"`
	MatchTypeName      string `description:"匹配类型"  json:"-"`
	StopTime           string `description:"停留时间" json:"-"`
}

type ViewReportListResp struct {
	Total int `description:"数量"`
	List  []*ViewReportList
}

func GetViewReportListByMobile(mobile string, txtType int) (items []*ViewReportList, err error) {
	dataName := ""
	sql := ``
	if utils.RunMode == "debug" {
		dataName = "test_v2_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}

	ficcSql := `SELECT
	rr.research_report_name,
		rr.type AS report_type,
		'ficc' AS txt_type,
		'--' AS match_type_name,
		'--' AS stop_time,
	uvh.created_time AS created_time
	FROM
	user_view_history uvh
	LEFT JOIN research_report rr ON rr.research_report_id = uvh.research_report_id
	WHERE
	uvh.mobile = ?
	UNION ALL
	SELECT
	r.title AS research_report_name,
		'rddp' AS report_type,
		'ficc' AS txt_type,
		r.classify_name_first AS match_type_name,
		'--' AS stop_time,
		rvr.create_time AS created_time
	FROM %s.report_view_record rvr
	LEFT JOIN %s.report r ON r.id = rvr.report_id
	WHERE
	rvr.mobile=?
	UNION ALL
	SELECT
	cha.permission_name AS research_report_name,
		'advisory' AS report_type,
		'ficc' AS txt_type,
		cha.classify_name AS match_type_name,
		'--' AS stop_time,
		auc.create_time AS created_time
	FROM
	advisory_user_chart_article_record auc
	LEFT JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id
	WHERE
	auc.mobile = ?`

	rightsSql := `SELECT
		art.title AS research_report_name,
		'cygx' AS report_type,
		'rights' AS txt_type,
		art.match_type_name,
		h.stop_time,
		h.create_time AS created_time 
	FROM
		cygx_article_history_record h
		LEFT JOIN cygx_article art ON art.article_id = h.article_id
	WHERE
		h.mobile = ? `

	if txtType == 1 {
		sql = ` SELECT * FROM ( ` + rightsSql + `
	      )AS t ORDER BY t.created_time DESC`
	} else if txtType == 2 {
		sql = ` SELECT * FROM ( ` + ficcSql + `
	      )AS t ORDER BY t.created_time DESC`
	} else {
		sql = ` SELECT * FROM ( ` + ficcSql + " UNION ALL " + rightsSql + `
	      )AS t ORDER BY t.created_time DESC`
	}

	//报告统计删除晨报部分统计加入每日资讯 2021-4-9
	//sql := ` SELECT * FROM (
	//					SELECT
	//							r.title AS research_report_name,
	//							'rddp' AS report_type,
	//							rvr.create_time AS created_time
	//						FROM %s.report_view_record rvr
	//						INNER JOIN %s.report r ON r.id = rvr.report_id
	//						WHERE
	//							rvr.mobile=?
	//					UNION ALL
	//					SELECT
	//					cha.permission_name AS research_report_name,
	//					'advisory' AS report_type,
	//					auc.create_time AS created_time
	//				FROM
	//					advisory_user_chart_article_record auc
	//					INNER JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id
	//				WHERE
	//					auc.mobile = ?
	//       )AS t ORDER BY t.created_time DESC
	//    `

	o := orm.NewOrm()

	if txtType == 1 {
		_, err = o.Raw(sql, mobile).QueryRows(&items)
	} else if txtType == 2 {
		sql = fmt.Sprintf(sql, dataName, dataName)
		_, err = o.Raw(sql, mobile, mobile, mobile).QueryRows(&items)
	} else {
		sql = fmt.Sprintf(sql, dataName, dataName)
		_, err = o.Raw(sql, mobile, mobile, mobile, mobile).QueryRows(&items)
	}

	return
}

func GetViewReportListByEmail2(email string, txtType int) (items []*ViewReportList, err error) {
	dataName := ""
	sql := ``
	if utils.RunMode == "debug" {
		dataName = "test_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}

	ficcSql := `SELECT
	rr.research_report_name,
		rr.type AS report_type,
		'ficc' AS txt_type,
		'--' AS match_type_name,
		'--' AS stop_time,
	uvh.created_time AS created_time
	FROM
	user_view_history uvh
	INNER JOIN research_report rr ON rr.research_report_id = uvh.research_report_id
	WHERE
	uvh.email = ?
	UNION ALL
	SELECT
	r.title AS research_report_name,
		'rddp' AS report_type,
		'ficc' AS txt_type,
		r.classify_name_first AS match_type_name,
		'--' AS stop_time,
		rvr.create_time AS created_time
	FROM %s.report_view_record rvr
	INNER JOIN %s.report r ON r.id = rvr.report_id
	WHERE
	rvr.email=?
	UNION ALL
	SELECT
	cha.permission_name AS research_report_name,
		'advisory' AS report_type,
		'ficc' AS txt_type,
		cha.classify_name AS match_type_name,
		'--' AS stop_time,
		auc.create_time AS created_time
	FROM
	advisory_user_chart_article_record auc
	INNER JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id
	WHERE
	auc.email = ?`

	rightsSql := `SELECT
		art.title AS research_report_name,
		'cygx' AS report_type,
		'rights' AS txt_type,
		art.match_type_name,
		h.stop_time,
		h.create_time AS created_time 
	FROM
		cygx_article_history_record h
		INNER JOIN cygx_article art ON art.article_id = h.article_id
	WHERE
		h.email = ? `

	if txtType == 1 {
		sql = ` SELECT * FROM ( ` + rightsSql + `
	      )AS t ORDER BY t.created_time DESC`
	} else if txtType == 2 {
		sql = ` SELECT * FROM ( ` + ficcSql + `
	      )AS t ORDER BY t.created_time DESC`
	} else {
		sql = ` SELECT * FROM ( ` + ficcSql + " UNION ALL " + rightsSql + `
	      )AS t ORDER BY t.created_time DESC`
	}

	//报告统计删除晨报部分统计加入每日资讯 2021-4-9
	//sql := ` SELECT * FROM (
	//					SELECT
	//							r.title AS research_report_name,
	//							'rddp' AS report_type,
	//							rvr.create_time AS created_time
	//						FROM %s.report_view_record rvr
	//						INNER JOIN %s.report r ON r.id = rvr.report_id
	//						WHERE
	//							rvr.mobile=?
	//					UNION ALL
	//					SELECT
	//					cha.permission_name AS research_report_name,
	//					'advisory' AS report_type,
	//					auc.create_time AS created_time
	//				FROM
	//					advisory_user_chart_article_record auc
	//					INNER JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id
	//				WHERE
	//					auc.mobile = ?
	//       )AS t ORDER BY t.created_time DESC
	//    `

	o := orm.NewOrm()
	if txtType == 1 {
		_, err = o.Raw(sql, email).QueryRows(&items)
	} else if txtType == 2 {
		sql = fmt.Sprintf(sql, dataName, dataName)
		_, err = o.Raw(sql, email, email, email).QueryRows(&items)
	} else {
		sql = fmt.Sprintf(sql, dataName, dataName)
		_, err = o.Raw(sql, email, email, email, email).QueryRows(&items)
	}

	return
}

// GetViewReportCount 获取报告列表
func GetViewReportList(mobile, email, startDate, endDate string, startSize, pageSize int) (total int, items []*ViewReportList, err error) {
	dataName := ""
	sql := ``

	var sql1, sql2, sql3 string
	var pars []interface{}
	var pars1, pars2, pars3 []interface{}
	if mobile != "" && email != "" {
		sql1 += ` and ( uvh.mobile = ? or uvh.email = ? ) `
		sql2 += ` and ( rvr.mobile = ? or rvr.email = ? `
		sql3 += ` and ( auc.mobile = ? or auc.email = ? `
		pars1 = append(pars1, mobile, email)
		pars2 = append(pars2, mobile, email)
		pars3 = append(pars3, mobile, email)
	} else if mobile != "" {
		sql1 += ` and ( uvh.mobile = ? ) `
		sql2 += ` and ( rvr.mobile = ? ) `
		sql3 += ` and ( auc.mobile = ? ) `
		pars1 = append(pars1, mobile)
		pars2 = append(pars2, mobile)
		pars3 = append(pars3, mobile)
	} else if email != "" {
		sql1 += ` and ( uvh.email = ? ) `
		sql2 += ` and ( rvr.email = ? ) `
		sql3 += ` and ( auc.email = ? ) `
		pars1 = append(pars1, email)
		pars2 = append(pars2, email)
		pars3 = append(pars3, email)
	}

	//时间筛选
	if startDate != "" && endDate != "" {
		sql1 += ` and uvh.created_time >= ? and uvh.created_time <= ?  `
		pars1 = append(pars1, startDate, endDate)
		sql2 += ` and rvr.create_time >= ? and rvr.create_time <= ?  `
		pars2 = append(pars2, startDate, endDate)
		sql3 += ` and auc.create_time >= ? and auc.create_time <= ?  `
		pars3 = append(pars3, startDate, endDate)
	}

	pars = append(pars, pars1, pars2, pars3)
	if utils.RunMode == "debug" {
		dataName = "test_v2_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}

	ficcSql := `SELECT
	rr.research_report_name,
		rr.type AS report_type,
		'ficc' AS txt_type,
		'--' AS match_type_name,
		'--' AS stop_time,
	uvh.created_time AS created_time,uvh.mobile,uvh.email,uvh.real_name,uvh.company_name
	FROM
	user_view_history uvh
	LEFT JOIN research_report rr ON rr.research_report_id = uvh.research_report_id
	WHERE 1=1 
	` + sql1 + `
	UNION ALL
	SELECT
	r.title AS research_report_name,
		'rddp' AS report_type,
		'ficc' AS txt_type,
		r.classify_name_first AS match_type_name,
		'--' AS stop_time,
		rvr.create_time AS created_time,rvr.mobile,rvr.email,rvr.real_name,rvr.company_name
	FROM %s.report_view_record rvr
	LEFT JOIN %s.report r ON r.id = rvr.report_id
	WHERE 1=1 ` + sql2 + `
	UNION ALL
	SELECT
	cha.permission_name AS research_report_name,
		'advisory' AS report_type,
		'ficc' AS txt_type,
		cha.classify_name AS match_type_name,
		'--' AS stop_time,
		auc.create_time AS created_time,auc.mobile,auc.email,auc.real_name,auc.company_name
	FROM
	advisory_user_chart_article_record auc
	LEFT JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id
	WHERE 1=1 ` + sql3

	sql = ` SELECT * FROM ( ` + ficcSql + ` )AS t ORDER BY t.created_time DESC`

	o := orm.NewOrm()

	sql = fmt.Sprintf(sql, dataName, dataName)
	totalSql := `SELECT count(*) total FROM ( ` + sql + ` )AS z `
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}

	sql += ` limit ?,?`
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)

	return
}

// GetViewReportCount 获取报告列表总数
func GetViewReportCount(mobile, email string) (total int, err error) {
	dataName := ""
	sql := ``

	var sql1, sql2, sql3 string
	var pars []interface{}
	if mobile != "" && email != "" {
		sql1 = `uvh.mobile = ? or uvh.email = ? `
		sql2 = `rvr.mobile = ? or rvr.email = ? `
		sql3 = `auc.mobile = ? or auc.email = ? `
		pars = append(pars, mobile, email, mobile, email, mobile, email)
	} else if mobile != "" {
		sql1 = `uvh.mobile = ? `
		sql2 = `rvr.mobile = ? `
		sql3 = `auc.mobile = ? `
		pars = append(pars, mobile, mobile, mobile)
	} else if email != "" {
		sql1 = `uvh.email = ? `
		sql2 = `rvr.email = ? `
		sql3 = `auc.email = ? `
		pars = append(pars, email, email, email)
	}

	if utils.RunMode == "debug" {
		dataName = "test_v2_hongze_rddp"
	} else {
		dataName = "hongze_rddp"
	}

	ficcSql := `SELECT
	rr.research_report_name,
		rr.type AS report_type,
		'ficc' AS txt_type,
		'--' AS match_type_name,
		'--' AS stop_time,
	uvh.created_time AS created_time
	FROM
	user_view_history uvh
	LEFT JOIN research_report rr ON rr.research_report_id = uvh.research_report_id
	WHERE
	` + sql1 + `
	UNION ALL
	SELECT
	r.title AS research_report_name,
		'rddp' AS report_type,
		'ficc' AS txt_type,
		r.classify_name_first AS match_type_name,
		'--' AS stop_time,
		rvr.create_time AS created_time
	FROM %s.report_view_record rvr
	LEFT JOIN %s.report r ON r.id = rvr.report_id
	WHERE ` + sql2 + `
	UNION ALL
	SELECT
	cha.permission_name AS research_report_name,
		'advisory' AS report_type,
		'ficc' AS txt_type,
		cha.classify_name AS match_type_name,
		'--' AS stop_time,
		auc.create_time AS created_time
	FROM
	advisory_user_chart_article_record auc
	LEFT JOIN chart_permission cha ON cha.chart_permission_id = auc.chart_permission_id
	WHERE ` + sql3

	sql = ` SELECT count(*) total FROM ( ` + ficcSql + `
	      )AS t ORDER BY t.created_time DESC`

	o := orm.NewOrm()

	sql = fmt.Sprintf(sql, dataName, dataName)
	err = o.Raw(sql, pars).QueryRow(&total)

	return
}

// UserViewMobileTotalSlice 根据用户手机号字符串获取用户的浏览数
type UserViewMobileTotalSlice struct {
	Mobile      string    `description:"用户手机号"`
	Total       int       `description:"总阅读数"`
	CreatedTime time.Time `description:"用户浏览时间"`
}

// UserViewEmailTotalSlice 根据用户邮箱字符串获取用户的浏览数
type UserViewEmailTotalSlice struct {
	Email       string    `description:"用户邮箱"`
	Total       int       `description:"总阅读数"`
	CreatedTime time.Time `description:"用户浏览时间"`
}

func GetCountUserViewHistoryByMobile(dayStr string) (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,mobile,max(created_time) as created_time FROM user_view_history WHERE created_time >= ? and mobile !="" group by mobile `
	_, err = o.Raw(sql, dayStr+" 00:00:00").QueryRows(&items)
	return
}

func GetCountUserViewHistoryByEmails(emails string) (items []*UserViewEmailTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,email,max(created_time) as created_time FROM user_view_history WHERE email in (` + emails + `) group by email`
	_, err = o.Raw(sql).QueryRows(&items)
	return
	//return items2,err
}

func GetReportViewMaxTimeByMobile(dayStr string) (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrm()
	o.Using("rddp")
	rddpSql := `SELECT mobile,MAX(create_time) AS created_time,COUNT(1) AS total FROM report_view_record WHERE create_time >= ? and mobile !="" group by mobile`
	_, err = o.Raw(rddpSql, dayStr+" 00:00:00").QueryRows(&items)
	return
}

func GetReportViewMaxTimeByEmails(emails string) (items []*UserViewEmailTotalSlice, err error) {
	o := orm.NewOrm()
	o.Using("rddp")
	rddpSql := `SELECT mobile,MAX(create_time) AS created_time,COUNT(1) AS total FROM report_view_record WHERE email in (` + emails + `) group by email`
	_, err = o.Raw(rddpSql).QueryRows(&items)
	return
}

// GetAdvisoryCountUserViewHistoryByMobile 每日资讯
func GetAdvisoryCountUserViewHistoryByMobile(dayStr string) (items []*UserViewMobileTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,mobile,max(create_time) as created_time FROM advisory_user_chart_article_record WHERE  create_time >= ? and mobile !="" group by mobile`
	_, err = o.Raw(sql, dayStr+" 00:00:00").QueryRows(&items)
	return
}

// GetAdvisoryCountUserViewHistoryByEmails 每日资讯
func GetAdvisoryCountUserViewHistoryByEmails(emails string) (items []*UserViewEmailTotalSlice, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(1) total,email,max(create_time) as created_time FROM advisory_user_chart_article_record WHERE email in (` + emails + `) group by email`
	_, err = o.Raw(sql).QueryRows(&items)
	return
	//return items2,err
}