log-server/service/log/log_ip.go

package log

import (
	"errors"
	"fmt"
	"github.com/xuri/excelize/v2"
	"go.uber.org/zap"
	"gorm.io/gorm"
	"log-server/global"
	"log-server/model/log"
	"log-server/model/log/request"
	"strconv"
	"time"
)

type ServiceIpLog struct {
}

//根据gameId、机器编号分组获取ip
func (s *ServiceIpLog) GetIpLogList(api log.IpLogRequest, info request.PageInfo, order string, desc bool) (list interface{}, total int64, err error) {
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	db := global.GVA_DB.Model(&log.IpLog{})
	var apiList []log.IpLogResponse
	startDate := time.Now().Format("2006-01-02")
	endDate := time.Now().Format("2006-01-02")
	if len(api.Date) == 2 {
		startDate = api.Date[0]
		endDate = api.Date[1]
	}
	db = db.Where("create_date >= ? and create_date <= ?", startDate, endDate)
	if api.GameId != 0 {
		db = db.Where("game_id = ?", api.GameId)
	}
	if api.PcCode != "" {
		db = db.Where("pc_code = ?", api.PcCode)
	}
	if api.Ip != "" {
		db = db.Where("ip = ?", api.Ip)
	}

	db = db.Select("create_date,pc_code,game_id,count(*) as count_total,count(distinct(ip)) as count_distinct_ip")
	db = db.Group("pc_code, game_id, create_date")
	//db.Select("count(*)").Count(&countTotal)
	//db.Select("count(distinct(ip))").Count(&countDistinctIp)
	err = db.Count(&total).Error
	if err != nil {
		return apiList, total, err
	} else {
		db = db.Limit(limit).Offset(offset)
		if order != "" {
			var OrderStr string
			// 设置有效排序key 防止sql注入
			// 感谢 Tom4t0 提交漏洞信息
			orderMap := make(map[string]bool, 4)
			orderMap["game_id"] = true
			orderMap["count_distinct_ip"] = true
			orderMap["count_total"] = true
			orderMap["create_date"] = 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 apiList, total, err
			}

			err = db.Order(OrderStr).Find(&apiList).Error
		} else {
			err = db.Order("id").Find(&apiList).Error
		}
	}
	//遍历更改日期格式
	for i, _ := range apiList {
		apiList[i].CreateDate = apiList[i].CreateDate[:10]
	}
	return apiList, total, err
}

//获取异常租机ip列表
func (s *ServiceIpLog) GetAbnormalIpLogList(api log.AbnormalIpLogRequest, info request.PageInfo, order string, desc bool) (list interface{}, total int64, err error) {
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	db := global.GVA_DB.Model(&log.AbnormalMachineIp{})
	var apiList []log.AbnormalMachineIp
	startDate := time.Now().Format("2006-01-02")
	endDate := time.Now().Format("2006-01-02")
	if len(api.Date) == 2 {
		startDate = api.Date[0]
		endDate = api.Date[1]
	}
	db = db.Select("abnormal_machine_ip.*, game_task.task_name, game_task.user")
	db = db.Joins("left join game_task on game_task.task_id = abnormal_machine_ip.game_id")
	db = db.Where("create_date >= ? and create_date <= ?", startDate, endDate)
	if api.GameId != 0 {
		db = db.Where("game_id = ?", api.GameId)
	}
	if api.PcCode != "" {
		db = db.Where("pc_code = ?", api.PcCode)
	}
	if api.Ip != "" {
		db = db.Where("ip = ?", api.Ip)
	}

	err = db.Count(&total).Error
	if err != nil {
		return apiList, total, err
	} else {
		db = db.Limit(limit).Offset(offset)
		if order != "" {
			var OrderStr string
			// 设置有效排序key 防止sql注入
			// 感谢 Tom4t0 提交漏洞信息
			orderMap := make(map[string]bool, 5)
			orderMap["game_id"] = true
			orderMap["count"] = true
			orderMap["create_date"] = true
			orderMap["user"] = true
			orderMap["task_name"] = 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 apiList, total, err
			}

			err = db.Order(OrderStr).Find(&apiList).Error
		} else {
			err = db.Order("id desc").Find(&apiList).Error
		}
	}
	//遍历更改日期格式
	for i, _ := range apiList {
		apiList[i].CreateDate = apiList[i].CreateDate[:10]
	}
	return apiList, total, err
}

//获取今日异常租机ip（ip播报使用）
func (s *ServiceIpLog) GetTodayAbnormalIpLogList() (list map[string]string, err error) {
	var total int64
	var apiList []log.TodayAbnormalMachineIp
	pcCodeList := make(map[string]string)
	usePcCode := make(map[string]string)
	date := time.Now().Format("2006-01-02")
	//1个半小时前的时间
	effectiveTime := time.Now().Add(-time.Minute * 90).Format("2006-01-02 15:04:05")

	db := global.GVA_DB.Table("abnormal_machine_ip as ami")
	db = db.Select("ami.*, gt.task_name, gt.user")
	db = db.Joins("left join game_task as gt on gt.task_id = ami.game_id")
	db = db.Where("create_date", date)
	err = db.Count(&total).Error
	if total == 0 {
		return usePcCode, err
	}
	if err != nil {
		return usePcCode, err
	} else {
		err = db.Order("id").Find(&apiList).Error
	}
	//遍历更改日期格式
	for i, _ := range apiList {
		apiList[i].CreateDate = apiList[i].CreateDate[:10]
		//得到{编号【负责人名称类似结构】}
		pcCodeList[apiList[i].PcCode] = apiList[i].User
	}

	//遍历列表中的租机，查询数据库（租机编号，和本日时间），得到记录集合，通过ip作为map的key，判断是否需要播报
	//遍历租机编号列表
	for k, _ := range pcCodeList {
		var ipList []log.IpLogBroadcast
		ipSet := make(map[string]int)
		db1 := global.GVA_DB.Model(&log.IpLog{}).Select("ip_log.*, game_task.user")
		db1 = db1.Joins("left join game_task on game_task.task_id = ip_log.game_id")
		db1.Where("create_date = ? and pc_code = ? and ip_log.create_time > ? ", date, k, effectiveTime).Limit(5).Order("id desc").Find(&ipList)
		//fmt.Println(ipList)
		//fmt.Println(len(ipList))
		//样本太少，不做处理
		if len(ipList) <= 1 {
			return usePcCode, err
		}
		//遍历ipList，查找
		for _, v := range ipList {
			ipSet[v.Ip] = 1
			//fmt.Println(ipSet)
			//fmt.Println(len(ipSet))
		}
		if len(ipSet) == 1 {
			usePcCode[ipList[0].PcCode] = ipList[0].User
		}
	}
	return usePcCode, err
}

//根据gameId获取ip
func (s *ServiceIpLog) GetGameIpList(api log.GameIpRequest, info request.PageInfo, order string, desc bool) (apiList []log.GameIpResponse, total int64, err error) {
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	db := global.GVA_DB.Model(&log.GameIpResponse{})
	startDate := time.Now().Format("2006-01-02")
	endDate := time.Now().Format("2006-01-02")
	if len(api.Date) == 2 {
		startDate = api.Date[0]
		endDate = api.Date[1]
	}
	//筛选负责人
	if api.User != "" {
		db = db.Where("user = ?", api.User)
	}
	//筛选日期
	db = db.Where("create_date >= ? and create_date <= ?", startDate, endDate)
	if api.GameId != 0 {
		db = db.Where("game_id = ?", api.GameId)
	}
	err = db.Count(&total).Error
	if err != nil {
		return apiList, total, err
	} else {
		db = db.Limit(limit).Offset(offset)
		if order != "" {
			var OrderStr string
			// 设置有效排序key 防止sql注入
			// 感谢 Tom4t0 提交漏洞信息
			orderMap := make(map[string]bool, 8)
			//orderMap["game_id"] = true
			orderMap["count_distinct_ip"] = true
			orderMap["count_total"] = true
			orderMap["success_ip"] = true
			orderMap["task_count"] = true
			orderMap["create_date"] = true
			orderMap["exceed_three"] = true
			orderMap["ip_repetition_rate"] = true
			orderMap["average_ip_repetition_rate"] = 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 apiList, total, err
			}

			err = db.Order(OrderStr).Find(&apiList).Error
		} else {
			err = db.Order("id").Find(&apiList).Error
		}
	}
	//遍历更改日期格式
	for i, _ := range apiList {
		apiList[i].CreateDate = apiList[i].CreateDate[:10]
		//err = global.GVA_DB.Model(&log.IpLog{}).Select("COUNT(1) as max_count").Where("game_id = ? and create_date = ? and status = 2", apiList[i].GameId, apiList[i].CreateDate).Group("ip").Order("max_count desc").Limit(1).Find(&apiList[i]).Error
		//global.GVA_DB.Model(&log.IpLog{}).Select("id,ip,count(*) ").Where("game_id = ? and create_date = ? and status = 2", apiList[i].GameId, apiList[i].CreateDate).Group("ip").Having("count(*) > 4").Count(&apiList[i].ExceedThree)
		////获取平均重复率和ip重复率 并保留小数点后两位
		//countDistinctIp := float64(apiList[i].CountDistinctIp)
		//successIp := float64(apiList[i].SuccessIp)
		//taskCount := float64(apiList[i].TaskCount)
		//apiList[i].IpRepetitionRate, _ =  strconv.ParseFloat(fmt.Sprintf("%.2f", (countDistinctIp/ successIp) *100), 64)
		//if taskCount != 0 {
		//	apiList[i].AverageIpRepetitionRate, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", (countDistinctIp / taskCount) *100), 64)
		//}
	}
	return apiList, total, err
}

//展示ip
func (s *ServiceIpLog) GetIp(iplog log.IpLogResponse) (list interface{}, total int64, err error) {
	var ipList []log.QueryIpList
	db := global.GVA_DB.Model(&log.IpLog{}).Select("ip, COUNT(1) as count").Where("game_id = ? and pc_code = ? and create_date = ?", iplog.GameId, iplog.PcCode, iplog.CreateDate).Group("ip")
	db.Count(&total)
	err = db.Order("count desc").Find(&ipList).Error
	return ipList, total, err
}

//gameID分组展示ip
func (s *ServiceIpLog) GetGameIp(ip log.GameIpResponse) (list interface{}, total int64, err error) {
	var ipList []log.QueryIpList
	db := global.GVA_DB.Model(&log.IpLog{}).Select("ip, COUNT(1) as count").Where("game_id = ? and status = 2 and create_date = ?", ip.GameId, ip.CreateDate).Group("ip")
	db.Count(&total)
	err = db.Order("count desc").Find(&ipList).Error
	return ipList, total, err
}

//获取gameIP列表
func (s *ServiceIpLog) GameIpListExcel(infoList []log.GameIpResponse, filePath string) error {
	excel := excelize.NewFile()
	excel.SetSheetRow("Sheet1", "A1", &[]string{
		"游戏id",
		"负责人",
		"任务名称",
		"上报ip次数",
		"任务完成",
		"最大ip数",
		"异常ip个数",
		"成功ip次数",
		"ip个数",
		"ip重复率",
		"平均重复率",
		"日期"})
	for i, statisticsLog := range infoList {
		axis := fmt.Sprintf("A%d", i+2)
		excel.SetSheetRow("Sheet1", axis, &[]interface{}{
			statisticsLog.GameId,
			statisticsLog.User,
			statisticsLog.TaskName,
			statisticsLog.CountTotal,
			statisticsLog.TaskCount,
			statisticsLog.MaxCount,
			statisticsLog.ExceedThree,
			statisticsLog.SuccessIp,
			statisticsLog.CountDistinctIp,
			statisticsLog.IpRepetitionRate,
			statisticsLog.AverageIpRepetitionRate,
			statisticsLog.CreateDate[:10],
		})
	}
	err := excel.SaveAs(filePath)
	return err
}

//获取异常ip数据
func (s *ServiceIpLog) GetAbnormalIp() (list map[string]string, err error) {
	db := global.GVA_DB.Model(&log.GameIpResponse{})
	date := time.Now().Format("2006-01-02")
	var apiList []log.GameIpResponse
	//筛选日期
	db = db.Where("create_date = ?", date)
	err = db.Order("id").Find(&apiList).Error
	var abnormalIpList = map[string]string{}
	//遍历更改日期格式
	for i, _ := range apiList {
		//apiList[i].CreateDate = apiList[i].CreateDate[:10]
		if apiList[i].IpRepetitionRate < 30 && apiList[i].CountTotal > 10 {
			abnormalIpList[apiList[i].User] = apiList[i].TaskName
		}
	}
	return abnormalIpList, err
}

//更新租机异常ip数据
func (s *ServiceIpLog) UpdateAbnormalMachineIp() (err error) {
	//首先获取今天的时间
	var abnormalIpList []log.AbnormalMachineIp
	date := time.Now().Format("2006-01-02")
	//然后查找租机异常ip记录，并且添加至数据库中
	db := global.GVA_DB.Table("ip_log").Select("ip, create_date, game_id, pc_code, count(ip) as count")
	db = db.Where("create_date", date)
	db = db.Group("pc_code, ip, game_id")
	err = db.Find(&abnormalIpList).Error
	//fmt.Println(abnormalIpList)
	if err != nil {
		return err
	}
	//将异常ip存储至abnormal_machine_ip数据库中
	for i, _ := range abnormalIpList {
		var entity log.AbnormalMachineIp
		ip := abnormalIpList[i].Ip
		gameId := abnormalIpList[i].GameId
		pcCode := abnormalIpList[i].PcCode
		createDate := abnormalIpList[i].CreateDate[:10]
		count := abnormalIpList[i].Count

		//根据count判断，如果count < 9, continue继续做下一条记录的处理
		if count < 6 {
			continue
		}

		if !errors.Is(global.GVA_DB.Model(&log.AbnormalMachineIp{}).Where("ip = ? and game_id = ? and pc_code = ? and create_date = ?", ip, gameId, pcCode, createDate).First(&entity).Error, gorm.ErrRecordNotFound) {
			//存在相同的ip、租机、gameid、创建日期记录，那么更新数量即可
			err = global.GVA_DB.Model(&log.AbnormalMachineIp{}).Where("ip = ? and game_id = ? and pc_code = ? and create_date = ?", ip, gameId, pcCode, createDate).Update("count", count).Error
			if err != nil {
				return errors.New("更新ip数量失败")
			}
			continue
		}
		//如果数据库中没有相同的记录，则创建相对应的记录
		err = global.GVA_DB.Model(&log.AbnormalMachineIp{}).Omit("user", "task_name").Create(&abnormalIpList[i]).Error
		if err != nil {
			return err
		}
	}
	return err
}

//定时更新ip列表数据
func (s *ServiceIpLog) UpdateGameIpList() (err error) {
	var total int64
	var apiList []log.GameIpResponse
	db := global.GVA_DB.Model(&log.IpLog{})
	date := time.Now().Format("2006-01-02")
	//筛选日期
	db = db.Where("ip_log.create_date = ?", date)
	//查找总ip上报次数
	db = db.Joins("left join"+"(select count(*) as count, game_id, create_date from ip_log where create_date = ? group by game_id, create_date)"+" as late on late.game_id = ip_log.game_id and late.create_date = ip_log.create_date", date)

	db = db.Where("ip_log.status = 2")

	db = db.Select("ip_log.create_date,ip_log.game_id,late.count as count_total, task_name, user,  count(*) as success_ip , retained_complete as task_count, count(distinct(ip)) as count_distinct_ip")
	//拼接任务完成表
	db = db.Joins("left join game_target_complete as gtc on gtc.task_id = ip_log.game_id and gtc.create_date = ip_log.create_date")
	//拼接game_task表获取任务名称和负责人名称
	db = db.Joins("left join game_task as gt on gt.task_id = ip_log.game_id")
	db = db.Group("ip_log.game_id, ip_log.create_date")
	err = db.Count(&total).Error
	if err != nil {
		return err
	}
	err = db.Order("ip_log.id").Find(&apiList).Error
	//遍历更改日期格式
	for i, _ := range apiList {
		var entity log.GameIpResponse
		apiList[i].CreateDate = apiList[i].CreateDate[:10]
		err = global.GVA_DB.Model(&log.IpLog{}).Select("COUNT(1) as max_count").Where("game_id = ? and create_date = ? and status = 2", apiList[i].GameId, apiList[i].CreateDate).Group("ip").Order("max_count desc").Limit(1).Find(&apiList[i]).Error
		global.GVA_DB.Model(&log.IpLog{}).Select("id,ip,count(*) ").Where("game_id = ? and create_date = ? and status = 2", apiList[i].GameId, apiList[i].CreateDate).Group("ip").Having("count(*) > 4").Count(&apiList[i].ExceedThree)
		//获取平均重复率和ip重复率 并保留小数点后两位
		countDistinctIp := float64(apiList[i].CountDistinctIp)
		successIp := float64(apiList[i].SuccessIp)
		taskCount := float64(apiList[i].TaskCount)
		apiList[i].IpRepetitionRate, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", (countDistinctIp/successIp)*100), 64)
		if taskCount != 0 {
			apiList[i].AverageIpRepetitionRate, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", (countDistinctIp/taskCount)*100), 64)
		}
		//查找数据库中是否有这条记录（game_id, create_date）
		if !errors.Is(global.GVA_DB.Model(&log.GameIpResponse{}).Where("game_id = ? and create_date = ?", apiList[i].GameId, apiList[i].CreateDate).First(&entity).Error, gorm.ErrRecordNotFound) {
			//如果有，更新
			err = global.GVA_DB.Model(&log.GameIpResponse{}).Where("game_id = ? and create_date = ?", apiList[i].GameId, apiList[i].CreateDate).Save(&apiList[i]).Error
			if err != nil {
				return errors.New("更新ip列表失败")
			}
			continue
		}
		//如果数据库中没有相同的记录，则创建相对应的记录
		err = global.GVA_DB.Model(&log.GameIpResponse{}).Create(&apiList[i]).Error
		if err != nil {
			return err
		}
	}
	return err
}