log-server/service/log/log_ip.go

package log

import (
	"fmt"
	"github.com/xuri/excelize/v2"
	"go.uber.org/zap"
	"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
}

//根据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.IpLog{})

	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("ip_log.create_date >= ? and ip_log.create_date <= ?", startDate, endDate)
	if api.GameId != 0 {
		db = db.Where("ip_log.game_id = ?", api.GameId)
		//查找总ip上报次数
		db = db.Joins("left join" + "(select count(*) as count, game_id, create_date from ip_log where game_id = ? and create_date >= ? and 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", api.GameId, startDate, endDate)
	} else {
		//查找总ip上报次数
		db = db.Joins("left join" + "(select count(*) as count, game_id, create_date from ip_log where create_date >= ? and 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",  startDate, endDate)
	}

	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")
	//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, 5)
			//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
			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("ip_log.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.IpLog{})
	startDate := time.Now().Format("2006-01-02")
	endDate := time.Now().Format("2006-01-02")
	var apiList []log.GameIpResponse

	//筛选日期
	db = db.Where("ip_log.create_date >= ? and ip_log.create_date <= ?", startDate, endDate)
	//查找总ip上报次数
	db = db.Joins("left join" + "(select count(*) as count, game_id, create_date from ip_log where create_date >= ? and 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",  startDate, endDate)

	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.Order("ip_log.id").Find(&apiList).Error

	var abnormalIpList = map[string]string{}
	//遍历更改日期格式
	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)
		apiList[i].AverageIpRepetitionRate, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", (countDistinctIp / taskCount) *100), 64)
		if apiList[i].IpRepetitionRate < 30 && apiList[i].SuccessIp > 10 {
			abnormalIpList[apiList[i].User] = apiList[i].TaskName
		}
	}
	return abnormalIpList, err
}