hongze_api/models/user_report_view_record.go

package models

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

// UserReportViewRecord 用户报告阅读记录表（汇总表）
type UserReportViewRecord struct {
	Id              int    `orm:"column(id);pk"`
	Source          int8   `description:"来源，1：rddp的报告；2：weekly_report的PHP报告；3：weekly_report商品的报告（应该是作废了）；4：察研观向的报告"`
	UserId          int    `description:"用户id"`
	ReportId        int    `description:"报告id"`
	ReportChapterId int    `description:"报告章节id"`
	Mobile          string `description:"手机号"`
	Email           string `description:"邮箱"`
	RealName        string `description:"用户实际姓名"`
	CompanyName     string `description:"公司名称"`
	StopTime        int    `description:"停留时间"`
	OutId           string `description:"外部关联id"`
	CreateTime      string `description:"创建时间"`
}

// TableName 表名
func (obj UserReportViewRecord) TableName() string {
	return `user_report_view_record`
}

// GetViewReportList 单条记录插入
func (obj UserReportViewRecord) GetViewReportList(year int, condition string, pars []interface{}, startSize, pageSize int) (total int64, items []*UserReportViewRecord, err error) {
	tableName := obj.GetTableName(year)

	o := orm.NewOrm()
	sql := fmt.Sprintf(`SELECT *  FROM %s WHERE 1 = 1 `, tableName)
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY create_time DESC,id DESC LIMIT ?,? `
	total, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)

	return

}

// Insert 单条记录插入
func (obj UserReportViewRecord) Insert(year int, item *UserReportViewRecord) (err error) {
	tableName := obj.GetTableName(year)

	sql := "INSERT INTO " + tableName + " (`source`, `user_id`, `report_id`, `report_chapter_id`, `mobile`, `email`, `real_name`, `company_name`, `stop_time`, `out_id`, `create_time`) VALUES "

	valStr := fmt.Sprintf(`( %d, %d, %d, %d, "%s", "%s","%s", "%s", %d, "%s", "%s")`, item.Source, item.UserId, item.ReportId, item.ReportChapterId, item.Mobile, item.Email, item.RealName, item.CompanyName, item.StopTime, item.OutId, item.CreateTime)

	sql = sql + valStr + ";"

	o := orm.NewOrm()

	// 先校验表是否存在，不存在的话，需要创建表
	err = obj.CheckAndCreateTable(o, tableName)
	if err != nil {
		return
	}

	// 插入数据
	_, err = o.Raw(sql).Exec()

	return

}

// InsertMul 单条记录插入
func (obj UserReportViewRecord) InsertMul(year int, items []*UserReportViewRecord) (err error) {
	tableName := obj.GetTableName(year)

	sql := "INSERT INTO " + tableName + " (`source`, `user_id`, `report_id`, `report_chapter_id`, `mobile`, `email`, `real_name`, `company_name`, `stop_time`, `out_id`, `create_time`) VALUES "

	valStrList := make([]string, 0)
	for _, item := range items {
		valStrList = append(valStrList, fmt.Sprintf(`( %d, %d, %d, %d, "%s", "%s","%s", "%s", %d, "%s", "%s")`, item.Source, item.UserId, item.ReportId, item.ReportChapterId, item.Mobile, item.Email, item.RealName, item.CompanyName, item.StopTime, item.OutId, item.CreateTime))
	}

	if len(valStrList) <= 0 {
		return
	}

	valStr := strings.Join(valStrList, ",")
	sql = sql + valStr + ";"

	o := orm.NewOrm()

	// 先校验表是否存在，不存在的话，需要创建表
	err = obj.CheckAndCreateTable(o, tableName)
	if err != nil {
		return
	}

	// 插入数据
	_, err = o.Raw(sql).Exec()

	return

}

// GetTableName 获取表名
func (obj UserReportViewRecord) GetTableName(year int) string {
	return fmt.Sprintf(`user_report_view_record_%d`, year)
}

// tableNameMap 已创建表的map
var tableNameMap = map[string]string{}

// createTableMutex 创建表的时候的锁
var createTableMutex = &sync.Mutex{}

// CheckAndCreateTable 通过表名校验表是否存在，不存在的话，需要创建表
func (obj UserReportViewRecord) CheckAndCreateTable(o orm.Ormer, tableName string) (err error) {
	if _, ok := tableNameMap[tableName]; ok {
		return
	}

	// 表判断的时候需要加锁，避免多个协程调用时，表还没有创建成功，又被另一个协程发现表不存在，然后再次创建表
	createTableMutex.Lock()
	defer func() {
		createTableMutex.Unlock()
	}()

	db := o.Raw(fmt.Sprintf(`SHOW TABLES LIKE "%s"`, tableName))
	var result []orm.Params
	_, err = db.Values(&result)
	if err != nil {
		fmt.Println("查询表失败:", err)
		return
	}

	// 不存在该表，那么就去创建表
	if len(result) <= 0 {
		baseSql := fmt.Sprintf("CREATE TABLE `%s` (\n  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',\n  `source` tinyint(20) NOT NULL DEFAULT '0' COMMENT '来源，1：rddp的报告；2：weekly_report的PHP报告；3：weekly_report商品的报告（应该是作废了）；4：察研观向的报告',\n  `user_id` int(11) DEFAULT '0' COMMENT '用户id',\n  `report_id` int(11) DEFAULT '0' COMMENT '报告id',\n  `report_chapter_id` int(10) DEFAULT NULL COMMENT '报告章节id',\n  `mobile` varchar(20) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '手机号',\n  `email` varchar(100) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '邮箱',\n  `real_name` varchar(30) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '用户时间名称',\n  `company_name` varchar(100) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '公司名称',\n  `stop_time` int(5) NOT NULL DEFAULT '0' COMMENT '停留时间',\n  `out_id` varchar(20) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '外部关联id',\n  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '阅读时间',\n  PRIMARY KEY (`id`),\n  KEY `idx_user_id` (`user_id`),\n  KEY `idx_report_id` (`report_id`),\n  KEY `idx_mobile` (`mobile`),\n  KEY `idx_email` (`email`),\n  KEY `idx_ctime` (`create_time`)\n) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;", tableName)
		_, err = o.Raw(baseSql).Exec()
		if err != nil {
			return
		}
	}

	tableNameMap[tableName] = tableName

	return
}