log-server/service/levelMonitor/image_record.go

package levelMonitor

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"github.com/xuri/excelize/v2"
	"go.uber.org/zap"
	"gorm.io/gorm"
	"io/ioutil"
	"log-server/global"
	"log-server/model/common/request"
	"log-server/model/levelMonitor"
	levelRequest "log-server/model/levelMonitor/request"
	"log-server/model/log"
	"log-server/service/cache"
	"net/http"
	"net/url"
	"regexp"
	"strconv"
	"strings"
	"time"
)

type ImageRecordService struct {
	cache cache.Cache
}

//创建图片记录
func (s *ImageRecordService) CreateImageRecord(record levelMonitor.ImageRecord) (err error) {
	//获取今日日期
	date := time.Now().Format("2006-01-02")
	key := fmt.Sprintf(cache.UploadImageNum, date, record.TaskId)
	ctx := context.Background()
	num, _ := s.cache.GetCacheNum(ctx, key)
	if num > 50 {
		return errors.New("数据上传已到上限")
	}
	var entity levelMonitor.ImageRecord
	//创建日期赋值
	record.CreateDate = date
	record.Status = 2
	//查找数据库是否存在此账号此游戏今日记录
	err = global.GVA_DB.Model(&levelMonitor.ImageRecord{}).Where("task_id = ? and account = ? and create_date = ?", record.TaskId, record.Account, date).First(&entity).Error
	if !errors.Is(err, gorm.ErrRecordNotFound) {
		return errors.New("此记录已存在，请勿重复添加")
	}
	err = global.GVA_DB.Model(&levelMonitor.ImageRecord{}).Omit("create_time", "update_time").Create(&record).Error
	if err != nil {
		return err
	}
	go s.ImageIdentify(record, key)
	return
}

func (s *ImageRecordService) UploadOrNot(record levelMonitor.ImageRecord) (err error) {
	date := time.Now().Format("2006-01-02")
	key := fmt.Sprintf(cache.UploadImageNum, date, record.TaskId)
	ctx := context.Background()
	num, _ := s.cache.GetCacheNum(ctx, key)
	if num > 50 {
		return errors.New("数据上传已到上限")
	}
	return nil
}

//删除三日前的图片记录
func (s *ImageRecordService) DeleteExpireImageRecord() {
	markTime := time.Now().Add(-time.Hour * 48).Format("2006-01-02")
	err := global.GVA_DB.Where("create_date < ?", markTime).Delete(&levelMonitor.ImageRecord{}).Error
	if err != nil {
		global.GVA_LOG.Info("删除图片信息失败:" + err.Error() + time.Now().Format("2006-01-02 15:04:05"))
	}
	return
}

//获取图片记录列表
func (s *ImageRecordService) GetImageRecordList(record levelRequest.ImageRecordRequest, info request.PageInfo, order string, desc bool) (recordList []levelMonitor.ImageRecordResponse, total int64, err error) {
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	db := global.GVA_DB.Model(&levelMonitor.ImageRecord{})
	startDate := time.Now().Format("2006-01-02")
	endDate := time.Now().Format("2006-01-02")
	if len(record.Date) == 2 {
		startDate = record.Date[0]
		endDate = record.Date[1]
	}
	db = db.Select("image_record.*, game_task.user, game_task.task_name")
	db = db.Joins("left join game_task on image_record.task_id = game_task.task_id")
	if record.User != "" {
		db = db.Where("user", record.User)
	}
	//筛选日期
	db = db.Where("create_date >= ? and create_date <= ?", startDate, endDate)
	if record.TaskId != 0 {
		db = db.Where("image_record.task_id = ?", record.TaskId)
	}
	err = db.Count(&total).Error
	if err != nil {
		return recordList, total, err
	} else {
		db = db.Limit(limit).Offset(offset)
		if order != "" {
			var OrderStr string
			// 设置有效排序key 防止sql注入
			// 感谢 Tom4t0 提交漏洞信息
			orderMap := make(map[string]bool, 7)
			orderMap["task_id"] = true
			orderMap["use_num"] = true
			orderMap["identify"] = true
			orderMap["create_date"] = true
			orderMap["create_time"] = true
			orderMap["update_time"] = true
			orderMap["user"] = true
			if orderMap[order] {
				if desc {
					OrderStr = order + " desc"
				} else {
					OrderStr = order
				}
			} else { // didn't matched any order key in `orderMap`
				global.GVA_LOG.Error("获取失败!", zap.Error(err))
				return recordList, total, err
			}
			err = db.Order(OrderStr).Find(&recordList).Error
		} else {
			err = db.Order("id desc").Find(&recordList).Error
		}
	}
	//遍历更改日期格式
	for i, _ := range recordList {
		recordList[i].CreateDate = recordList[i].CreateDate[:10]
		recordList[i].ImageBase64 = "data:image/png;base64," + recordList[i].ImageBase64
	}
	return recordList, total, err
}

const API_KEY = "z9GNcyrC7VeV3g1xXEj3YL1s"
const SECRET_KEY = "VASRBsEzeVsyKduSkkflfL87r5yqoqvj"
const BAIDU_IDENTIFY = "baiduIdentifyToken"

func (s *ImageRecordService) ImageIdentify(record levelMonitor.ImageRecord, key string) {
	token, err := GetAccessToken()
	if err != nil {
		global.GVA_LOG.Error("get token fail", zap.Error(err))
		s.UpdateImageRecordStatus(record.Id, "get token fail", -1, 0, 0)
		return
	}
	body, err := Request(token, record.ImageBase64)
	if err != nil {
		global.GVA_LOG.Error("Read Body Fail", zap.Error(err))
		s.UpdateImageRecordStatus(record.Id, err.Error(), -1, 0, 0)
		return
	}
	type Data struct {
		WordsResult []struct {
			Words string `json:"words"`
		} `json:"words_result"`
		WordsResultNum int `json:"words_result_num"`
		LogId          int `json:"log_id"`
	}
	var data Data
	err = json.Unmarshal(body, &data)
	if err != nil {
		global.GVA_LOG.Error("Read Body Fail", zap.Error(err))
		s.UpdateImageRecordStatus(record.Id, "Read Body Fail", -1, 0, 0)
		return
	}

	identify := 0.00
	if data.WordsResultNum == 0 {
		s.UpdateImageRecordStatus(record.Id, string(body), -1, 0, 0)
		return
	}
	i, err := strconv.ParseFloat(data.WordsResult[0].Words, 64)
	if err != nil {
		// 定义价格提取的正则表达式模式
		identifyPattern := `([0-9]+(?:\.[0-9]{1,2})?)`
		// 编译正则表达式
		re := regexp.MustCompile(identifyPattern)
		// 使用正则表达式查找价格信息
		level := re.FindAllString(data.WordsResult[0].Words, -1)
		l, _ := strconv.ParseFloat(level[0], 64)
		identify = l
	} else {
		identify = i
	}

	if identify == 0 {
		s.UpdateImageRecordStatus(record.Id, string(body), -1, 0, 0)
		return
	}
	var gameAccount log.GameAccount
	global.GVA_DB.Where("game_id", record.TaskId).Where("account", record.Account).First(&gameAccount)
	s.UpdateImageRecordStatus(record.Id, string(body), 1, gameAccount.UseNum, identify)
	UpdateGameAccountIdentify(record.Account, record.TaskId, identify)
	ctx := context.Background()
	s.cache.SetCacheNum(ctx, key)
	return
}

func (s *ImageRecordService) UpdateImageRecordStatus(id int, result string, status int, useNum int, identify float64) {
	update := map[string]interface{}{
		"result":   result,
		"status":   status,
		"use_num":  useNum,
		"identify": identify,
	}
	global.GVA_DB.Model(&levelMonitor.ImageRecord{}).Where("id", id).Updates(update)
}

func Request(baiduToken, content string) (body []byte, err error) {
	reqUrl := "https://aip.baidubce.com/rest/2.0/ocr/v1/general_basic?access_token=" + baiduToken
	var (
		params = url.Values{}
	)
	params.Set("image", content)
	requestData := params.Encode()
	payload := strings.NewReader(requestData)
	client := &http.Client{}
	req, err := http.NewRequest("POST", reqUrl, payload)

	if err != nil {
		fmt.Println(err)
		return
	}
	req.Header.Add("Content-Type", "application/x-www-form-urlencoded")
	req.Header.Add("Accept", "application/json")

	res, err := client.Do(req)
	if err != nil {
		global.GVA_LOG.Error("request Identify fail", zap.Error(err))
		return
	}
	defer res.Body.Close()

	body, err = ioutil.ReadAll(res.Body)
	if err != nil {
		global.GVA_LOG.Error("Read Body Fail", zap.Error(err))
		return
	}
	fmt.Println(string(body))
	return
}

// 更新game_acount
func UpdateGameAccountIdentify(account string, gameId int, identify float64) {
	global.GVA_DB.Table("game_account").Where("game_id", gameId).Where("account", account).Update("identify", identify)
}

/**
 * 使用 AK，SK 生成鉴权签名（Access Token）
 * @return string 鉴权签名信息（Access Token）
 */
func GetAccessToken() (string, error) {
	ctx := context.Background()
	token, err := global.GVA_REDIS.Get(ctx, BAIDU_IDENTIFY).Result()
	if err != nil || token == "" {
		reqUrl := "https://aip.baidubce.com/oauth/2.0/token"
		postData := fmt.Sprintf("grant_type=client_credentials&client_id=%s&client_secret=%s", API_KEY, SECRET_KEY)
		resp, err := http.Post(reqUrl, "application/x-www-form-urlencoded", strings.NewReader(postData))
		if err != nil {
			fmt.Println(err)
			return "", err
		}
		defer resp.Body.Close()
		body, err := ioutil.ReadAll(resp.Body)
		if err != nil {
			fmt.Println(err)
			return "", err
		}
		accessTokenObj := map[string]string{}
		json.Unmarshal(body, &accessTokenObj)
		global.GVA_REDIS.Set(ctx, BAIDU_IDENTIFY, accessTokenObj["access_token"], time.Hour*24*29)
		return accessTokenObj["access_token"], err
	}
	return token, err
}

func (s *ImageRecordService) YesterdayStatistics() {
	date := time.Now().Add(-time.Hour * 24).Format("2006-01-02")
	s.ImageRecordStatistics(date)
}

func (s *ImageRecordService) TodayStatistics() {
	date := time.Now().Format("2006-01-02")
	s.ImageRecordStatistics(date)
}

func (s *ImageRecordService) ImageRecordStatistics(date string) {
	type Data struct {
		TaskId int `json:"task_id"`
	}

	var taskIds []Data
	global.GVA_DB.Model(&levelMonitor.ImageRecord{}).Where("create_date = ?", date).Distinct("task_id").Find(&taskIds)
	if len(taskIds) == 0 {
		global.GVA_LOG.Warn("没有等级上报的数据")
		return
	}
	type StatisticsData struct {
		UseNum     int `json:"use_num"`
		AccountNum int `json:"account_num"`
		LevelNum   int `json:"level_num"`
	}
	for _, taskId := range taskIds {
		var statisticsData []StatisticsData
		global.GVA_DB.Model(&levelMonitor.ImageRecord{}).
			Select("use_num,COUNT(*) as account_num,SUM(identify) as level_num").
			Where("create_date = ?", date).
			Where("task_id = ?", taskId.TaskId).
			Where("status = ?", 1).
			Group("use_num").
			Order("use_num").
			Find(&statisticsData)
		if len(statisticsData) == 0 {
			continue
		}
		var imageRecordStatistics = make(map[string]interface{})
		b := false
		for _, data := range statisticsData {
			if _, ok := levelMonitor.R[data.UseNum]; ok {
				imageRecordStatistics[levelMonitor.R[data.UseNum]] = float64(data.LevelNum) / float64(data.AccountNum)
				b = true
			}
		}
		if b {
			s.CreateImageRecordStatistics(taskId.TaskId, imageRecordStatistics, date)
		}
	}
}

func (s *ImageRecordService) CreateImageRecordStatistics(taskId int, imageRecordStatistics map[string]interface{}, date string) {
	err := global.GVA_DB.Where("create_date = ?", date).Where("task_id = ?", taskId).First(&levelMonitor.ImageRecordStatistics{}).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		imageRecordStatistics["task_id"] = taskId
		imageRecordStatistics["create_date"] = time.Now().Format("2006-01-02")
		global.GVA_DB.Model(&levelMonitor.ImageRecordStatistics{}).Create(imageRecordStatistics)
		return
	}
	global.GVA_DB.Model(&levelMonitor.ImageRecordStatistics{}).
		Where("create_date = ?", date).
		Where("task_id = ?", taskId).
		Updates(imageRecordStatistics)
}

//获取7日统计数据
func (s *ImageRecordService) GetWeekImageRecordStatistics(taskId int, createDate string) (dataList []levelMonitor.ImageRecordStatisticsResponse, err error){
	db := global.GVA_DB.Model(&levelMonitor.ImageRecordStatistics{})
	db = db.Select("image_record_statistics.*, game_task.user, game_task.task_name")
	db = db.Joins("left join game_task on image_record_statistics.task_id = game_task.task_id")
	err = db.Where("image_record_statistics.task_id = ? and image_record_statistics.create_date < ?", taskId, createDate).Order("create_date desc").Limit(7).Find(&dataList).Error
	for i, _ := range dataList {
		dataList[i].CreateDate = dataList[i].CreateDate[:10]
	}
	return
}

//获取等级统计列表
func (s *ImageRecordService) GetImageRecordStatisticsList(record levelRequest.ImageRecordStatisticsRequest, info request.PageInfo, order string, desc bool) (dataList []levelMonitor.ImageRecordStatisticsResponse, total int64, err error) {
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	db := global.GVA_DB.Model(&levelMonitor.ImageRecordStatistics{})
	startDate := time.Now().Format("2006-01-02")
	endDate := time.Now().Format("2006-01-02")
	if len(record.Date) == 2 {
		startDate = record.Date[0]
		endDate = record.Date[1]
	}
	db = db.Select("image_record_statistics.*, game_task.user, game_task.task_name")
	db = db.Joins("left join game_task on image_record_statistics.task_id = game_task.task_id")
	if record.User != "" {
		db = db.Where("user", record.User)
	}
	//筛选日期
	db = db.Where("create_date >= ? and create_date <= ?", startDate, endDate)
	if record.TaskId != 0 {
		db = db.Where("image_record_statistics.task_id = ?", record.TaskId)
	}
	err = db.Count(&total).Error
	if err != nil {
		return dataList, total, err
	} else {
		db = db.Limit(limit).Offset(offset)
		if order != "" {
			var OrderStr string
			// 设置有效排序key 防止sql注入
			// 感谢 Tom4t0 提交漏洞信息
			orderMap := make(map[string]bool, 3)
			orderMap["task_id"] = true
			orderMap["create_date"] = true
			orderMap["user"] = true
			if orderMap[order] {
				if desc {
					OrderStr = order + " desc"
				} else {
					OrderStr = order
				}
			} else { // didn't matched any order key in `orderMap`
				global.GVA_LOG.Error("获取失败!", zap.Error(err))
				return dataList, total, err
			}
			err = db.Order(OrderStr).Find(&dataList).Error
		} else {
			err = db.Order("id desc").Find(&dataList).Error
		}
	}
	//遍历更改日期格式
	for i, _ := range dataList {
		dataList[i].CreateDate = dataList[i].CreateDate[:10]
	}
	return dataList, total, err
}

//导出等级统计列表
func (s *ImageRecordService) ImageRecordStatisticsListExcel(infoList []levelMonitor.ImageRecordStatisticsResponse, filePath string) error {
	excel := excelize.NewFile()
	excel.SetSheetRow("Sheet1", "A1", &[]string{
		"游戏id",
		"任务名称",
		"负责人",
		"日期",
		"次留",
		"3留",
		"4留",
		"5留",
		"6留",
		"7留",
		"8留",
		"9留",
		"10留",
		"11留",
		"12留",
		"13留",
		"14留",
		"15留",
		"25留",
		"30留"})
	for i, statisticsLog := range infoList {
		axis := fmt.Sprintf("A%d", i+2)
		excel.SetSheetRow("Sheet1", axis, &[]interface{}{
			statisticsLog.TaskId,
			statisticsLog.TaskName,
			statisticsLog.User,
			statisticsLog.CreateDate[:10],
			statisticsLog.Two,
			statisticsLog.Three,
			statisticsLog.Four,
			statisticsLog.Five,
			statisticsLog.Six,
			statisticsLog.Seven,
			statisticsLog.Eight,
			statisticsLog.Nine,
			statisticsLog.Ten,
			statisticsLog.Eleven,
			statisticsLog.Twelve,
			statisticsLog.Thirteen,
			statisticsLog.Fourteen,
			statisticsLog.Fifteen,
			statisticsLog.TwentyFive,
			statisticsLog.Thirty,
		})
	}
	err := excel.SaveAs(filePath)
	return err
}