eta_mini_crm/models/user_read_record.go

package models

import (
	"eta/eta_mini_crm/utils"
	"fmt"
	"time"

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

type UserReadRecord struct {
	UserReadRecordId int    `orm:"pk" description:"id"`
	UserId           int    `description:"用户id"`
	ReportId         int    `description:"报告id"`
	ReportTitle      string `description:"报告标题"`
	// ChartPermissionId1  string    `description:"一级品种id"`
	// ChartPermissionId2  string    `description:"二级品种id"`
	ChartPermissionName string    `description:"二级品种名称"`
	ClassifyId1         int       `description:"一级级分类id"`
	ClassifyName1       string    `description:"一级分类名称"`
	ClassifyId2         int       `description:"二级分类id"`
	ClassifyName2       string    `description:"二级分类名称"`
	ClassifyId3         int       `description:"三级分类id"`
	ClassifyName3       string    `description:"三级分类名称"`
	Timestamp           int       `description:"阅读开始时间戳"`
	EndTimestamp        int       `description:"阅读结束时间戳"`
	CreateTime          time.Time `description:"创建时间"`
	CreateDate          string    `description:"创建日期"`
	StayTime            string    `description:"停留时间"`
	StayTimestamp       string    `description:"停留时间戳"`
	ReportType          int       `description:"报告类型:1-普通研报;2-pdf研报"`
}

type UserReadRecordView struct {
	UserReadRecordId int    `orm:"pk" description:"id"`
	UserId           int    `description:"用户id"`
	ReportId         int    `description:"报告id"`
	ReportTitle      string `description:"报告标题"`
	// ChartPermissionId1  string    `description:"一级品种id"`
	// ChartPermissionId2  string    `description:"二级品种id"`
	ChartPermissionName string `description:"二级品种名称"`
	ClassifyId1         int    `description:"一级级分类id"`
	ClassifyName1       string `description:"一级分类名称"`
	ClassifyId2         int    `description:"二级分类id"`
	ClassifyName2       string `description:"二级分类名称"`
	ClassifyId3         int    `description:"三级分类id"`
	ClassifyName3       string `description:"三级分类名称"`
	Timestamp           int    `description:"阅读开始时间戳"`
	EndTimestamp        int    `description:"阅读结束时间戳"`
	CreateTime          string `description:"创建时间"`
	CreateDate          string `description:"创建日期"`
	StayTime            string `description:"停留时间"`
	StayTimestamp       string `description:"停留时间戳"`
	ReportType          int    `description:"报告类型:1-普通研报;2-pdf研报"`
}

type ReadCntStaitc struct {
	CreateDate string
	Count      int
}
type PermissionCntStaitc struct {
	ChartPermissionId int
	PermissionName    string
	Count             int
	Percent           float64
}

func GetUserReadRecordByUserId(firstClassifyIds, secondClassifyIds, thirdClassifyIds []int, userId int, condition string, pars []interface{}, startSize, pageSize int) (items []*UserReadRecordView, err error) {
	o := orm.NewOrm()
	sql := `SELECT DISTINCT ur.user_read_record_id, ur.report_id, ur.report_title, ur.chart_permission_name, ur.classify_name2, 
		ur.create_time, ur.stay_time, ur.classify_id2, ur.classify_id3, ur.classify_name3, ur.classify_id1, ur.classify_name1
	  	FROM user_read_record AS ur
		LEFT JOIN user_read_permission2 AS urp2
		ON ur.user_read_record_id = urp2.user_read_record_id
		WHERE user_id = ? `
	if condition != "" {
		sql += condition
	}
	if len(firstClassifyIds) != 0 || len(secondClassifyIds) != 0 || len(thirdClassifyIds) != 0 {
		sql += ` AND (1=2 `
		if len(firstClassifyIds) > 0 {
			sql += fmt.Sprintf(" OR ur.classify_id1 IN (%s) ", utils.GetOrmReplaceHolder(len(firstClassifyIds)))
		}
		if len(secondClassifyIds) > 0 {
			sql += fmt.Sprintf(" OR ur.classify_id2 IN (%s) ", utils.GetOrmReplaceHolder(len(secondClassifyIds)))
		}
		if len(thirdClassifyIds) > 0 {
			sql += fmt.Sprintf(" OR ur.classify_id3 IN (%s) ", utils.GetOrmReplaceHolder(len(thirdClassifyIds)))
		}
		sql += ` ) `
	}
	sql += ` ORDER BY create_time DESC LIMIT ?, ?`
	_, err = o.Raw(sql, userId, pars, firstClassifyIds, secondClassifyIds, thirdClassifyIds, startSize, pageSize).QueryRows(&items)
	return
}

func GetUserReadRecordCountByUserId(firstClassifyIds, secondClassifyIds, thirdClassifyIds []int, userId int, condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(DISTINCT ur.user_read_record_id) AS count
	  	FROM user_read_record AS ur
		LEFT JOIN user_read_permission2 AS urp2
		ON ur.user_read_record_id = urp2.user_read_record_id
		WHERE user_id = ? `
	if condition != "" {
		sql += condition
	}
	if len(firstClassifyIds) != 0 || len(secondClassifyIds) != 0 || len(thirdClassifyIds) != 0 {
		sql += ` AND (1=2 `
		if len(firstClassifyIds) > 0 {
			sql += fmt.Sprintf(" OR ur.classify_id1 IN (%s) ", utils.GetOrmReplaceHolder(len(firstClassifyIds)))
		}
		if len(secondClassifyIds) > 0 {
			sql += fmt.Sprintf(" OR ur.classify_id2 IN (%s) ", utils.GetOrmReplaceHolder(len(secondClassifyIds)))
		}
		if len(thirdClassifyIds) > 0 {
			sql += fmt.Sprintf(" OR ur.classify_id3 IN (%s) ", utils.GetOrmReplaceHolder(len(thirdClassifyIds)))
		}
		sql += ` ) `
	}
	err = o.Raw(sql, userId, pars, firstClassifyIds, secondClassifyIds, thirdClassifyIds).QueryRow(&count)
	return
}

func GetStaticReadCnt(condition string, pars []interface{}, startDate, endDate string) (items []*ReadCntStaitc, err error) {
	o := orm.NewOrm()
	sql := `SELECT create_date, COUNT(*) AS count
	FROM user_read_record AS ur
	LEFT JOIN user_read_permission2 AS urp
	ON ur.user_read_record_id = urp.user_read_record_id
	WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	sql += ` AND (ur.create_date BETWEEN ? AND ?)  GROUP BY ur.create_date`
	_, err = o.Raw(sql, pars, startDate, endDate).QueryRows(&items)
	return
}

func GetStaticPermissionCnt(condition string, pars []interface{}) (items []*PermissionCntStaitc, err error) {
	o := orm.NewOrm()
	sql := `SELECT urp.chart_permission_id, urp.permission_name,COUNT(*) AS count
	FROM user_read_permission1 AS urp
	LEFT JOIN user_read_record AS ur
	ON urp.user_read_record_id = ur.user_read_record_id
	WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY urp.chart_permission_id`
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}