log-server/service/dataStatistics/oldDataShow.go

package dataStatistics

import (
	"context"
	"fmt"
	"github.com/xuri/excelize/v2"
	"go.uber.org/zap"
	"log-server/global"
	"log-server/model/dataStatistics"
	"log-server/model/dataStatistics/request"
)

type ServiceOldDataShow struct {
}

func (s *ServiceOldDataShow) OldDataShowList(ctx context.Context, api request.OldDataShow2, info request.PageInfo, order string, desc bool) ([]*dataStatistics.OldDataShow, int64, error) {
	db := global.GVA_DB.Table("shuyou_game_target")
	db = db.Select("shuyou_game_target.id,shuyou_game_target.task_id,shuyou_game_target.task_name,shuyou_game_target.new_date," +
		"shuyou_game_target.target_new,shuyou_game_target.target_pay,shuyou_game_target.target_active,c.complete_new,c.complete_pay,c.complete_active," +
		"c.is_complete,c.amount,c.remark,h.complete_new_hand,h.complete_lc_hand,h.complete_pay_hand,h.amount_hand")
	db = db.Joins("left join shuyou_game_complete c on c.task_id = shuyou_game_target.task_id and c.new_date = shuyou_game_target.new_date")
	db = db.Joins("left join shuyou_game_complete_hand h on h.task_id = shuyou_game_target.task_id and h.new_date = shuyou_game_target.new_date")
	if api.TaskId != 0 {
		db = db.Where("shuyou_game_target.task_id = ?", api.TaskId)
	}
	if api.TaskName != "" {
		db = db.Where("shuyou_game_target.task_name like ?", "%"+api.TaskName+"%")
	}
	if len(api.NewDate) == 0 {
		api.NewDate = append(api.NewDate, "2023-02-22")
		api.NewDate = append(api.NewDate, "2023-02-22")
	}
	db = db.Where("shuyou_game_target.new_date >= ? and shuyou_game_target.new_date <= ?", api.NewDate[0], api.NewDate[1])

	var total int64
	err := db.Count(&total).Error
	//if err != nil {
	//	return nil, 0, err
	//}
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	//var statisticsLogs []*log.LogComputer
	var dateList []*dataStatistics.OldDataShow
	db = db.Limit(limit).Offset(offset)
	if order != "" {
		var OrderStr string
		// 设置有效排序key 防止sql注入
		// 感谢 Tom4t0 提交漏洞信息
		orderMap := make(map[string]bool, 3)
		orderMap["id"] = true
		//orderMap["game_id"] = true
		//orderMap["operator"] = true
		if orderMap[order] {
			if desc {
				OrderStr = order + " desc"
			} else {
				OrderStr = order + " desc"
			}
		} else { // didn't matched any order key in `orderMap`
			global.GVA_LOG.Error("获取失败!", zap.Error(err))
			return dateList, total, err
		}
		err = db.Order(OrderStr).Find(&dateList).Error
	} else {
		err = db.Order("id").Find(&dateList).Error
	}
	if err != nil {
		return nil, 0, err
	}
	return dateList, total, err
}

func (s *ServiceOldDataShow) ParseInfoList2Excel(infoList []*dataStatistics.OldDataShow, filePath string) (err error) {
	excel := excelize.NewFile()
	err = excel.SetSheetRow("Sheet1", "A1", &[]string{"日期", "任务ID", "任务名称", "目标新增", "目标留存", "目标付费", "完成新增", "完成留存", "完成付费", "未完成新增", "未完成留存", "未完成付费", "手动付费数量", "手动付费金额", "异常记录", "总付费金额"})
	if err != nil {
		return err
	}
	for i, one := range infoList {
		axis := fmt.Sprintf("A%d", i+2)
		targetLc := one.TargetActive - one.TargetNew
		if targetLc < 0 {
			targetLc = 0
		}
		completeLc := one.CompleteActive - one.CompleteNew
		if completeLc < 0 {
			completeLc = 0
		}
		imcompleteNew := one.TargetNew - one.CompleteNew
		if targetLc < 0 {
			targetLc = 0
		}
		imcompleteLc := targetLc - (one.CompleteActive - one.CompleteNew)
		if targetLc < 0 {
			targetLc = 0
		}
		imcompletePay := one.TargetPay - one.CompletePay
		if targetLc < 0 {
			targetLc = 0
		}
		err = excel.SetSheetRow("Sheet1", axis, &[]interface{}{
			one.NewDate,
			one.TaskId,
			one.TaskName,
			one.TargetNew,
			targetLc,
			one.TargetPay,
			one.CompleteNew,
			completeLc,
			one.CompletePay,
			imcompleteNew,
			imcompleteLc,
			imcompletePay,
			one.CompletePayHand,
			one.AmountHand,
			one.Remark,
			one.Amount,
		})
		if err != nil {
			return err
		}
	}
	err = excel.SaveAs(filePath)
	return err
}