hongze_open_api/models/tables/company_user.go

package tables

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

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{}
	if mobile != "" && email !=""{
		sql1 = ` and ( uvh.mobile = ? or uvh.email = ? ) `
		sql2 = ` and ( rvr.mobile = ? or rvr.email = ? `
		sql3 = ` and ( auc.mobile = ? or auc.email = ? `
		pars = append(pars,mobile,email,mobile,email,mobile,email)
	}else if mobile != ""{
		sql1 = ` and ( uvh.mobile = ? ) `
		sql2 = ` and ( rvr.mobile = ? ) `
		sql3 = ` and ( auc.mobile = ? ) `
		pars = append(pars,mobile,mobile,mobile)
	}else if email != ""{
		sql1 = ` and ( uvh.email = ? ) `
		sql2 = ` and ( rvr.email = ? ) `
		sql3 = ` and ( auc.email = ? ) `
		pars = append(pars,email,email,email)
	}

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

	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
}