aiforge/routers/repo/user_invitation.go

package repo

import (
	"fmt"
	"net/http"
	"net/url"
	"sort"
	"time"

	"code.gitea.io/gitea/models"
	"code.gitea.io/gitea/modules/context"
	"code.gitea.io/gitea/modules/log"
	"code.gitea.io/gitea/modules/setting"
	"github.com/360EntSecGroup-Skylar/excelize/v2"
)

func QueryInvitationCurrentMonth(ctx *context.Context) {
	// userName := ctx.Query("userName")
	// currentTimeNow := time.Now()
	// pageEndTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 0, 0, 0, 0, currentTimeNow.Location())
	// pageStartTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), 1, 0, 0, 0, 0, currentTimeNow.Location())

	//queryUserDataPage(ctx, "public.user_business_analysis_current_month", new(models.UserBusinessAnalysisCurrentMonth))
	//_, count := models.QueryUserStaticDataByTableName(1, 1, "public.user_business_analysis_current_month", new(models.UserBusinessAnalysisCurrentMonth), userName, 1)

	queryDataFromStaticTable(ctx, "public.user_business_analysis_current_month", new(models.UserBusinessAnalysisCurrentMonth))
}

func getInvitationExcelHeader(ctx *context.Context) map[string]string {
	excelHeader := make([]string, 0)
	excelHeader = append(excelHeader, ctx.Tr("user.static.id"))
	excelHeader = append(excelHeader, ctx.Tr("user.static.name"))
	excelHeader = append(excelHeader, ctx.Tr("user.static.invitationNum"))
	excelHeader = append(excelHeader, ctx.Tr("user.static.phone"))
	excelHeader = append(excelHeader, ctx.Tr("user.static.registdate"))

	excelHeaderMap := make(map[string]string, 0)
	var i byte
	i = 0
	for _, value := range excelHeader {
		excelColumn := getColumn(i) + fmt.Sprint(1)
		excelHeaderMap[excelColumn] = value
		i++
	}
	return excelHeaderMap
}

func getInvitationDetailExcelHeader(ctx *context.Context) map[string]string {
	excelHeader := make([]string, 0)
	excelHeader = append(excelHeader, ctx.Tr("user.static.id"))
	excelHeader = append(excelHeader, ctx.Tr("user.static.name"))
	excelHeader = append(excelHeader, ctx.Tr("user.static.email"))
	excelHeader = append(excelHeader, ctx.Tr("user.static.phone"))
	excelHeader = append(excelHeader, ctx.Tr("user.static.registdate"))
	excelHeader = append(excelHeader, ctx.Tr("user.static.srcUserId"))

	excelHeaderMap := make(map[string]string, 0)
	var i byte
	i = 0
	for _, value := range excelHeader {
		excelColumn := getColumn(i) + fmt.Sprint(1)
		excelHeaderMap[excelColumn] = value
		i++
	}
	return excelHeaderMap
}

func writeInvitationExcel(row int, xlsx *excelize.File, sheetName string, userRecord *models.UserBusinessAnalysisAll) {
	rows := fmt.Sprint(row)
	var tmp byte
	tmp = 0
	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.ID)
	tmp = tmp + 1
	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.Name)
	tmp = tmp + 1

	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.InvitationUserNum)
	tmp = tmp + 1

	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.Phone)
	tmp = tmp + 1

	formatTime := userRecord.RegistDate.Format("2006-01-02 15:04:05")
	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, formatTime[0:len(formatTime)-3])

}

func writeInvitationDetailExcel(row int, xlsx *excelize.File, sheetName string, userRecord *models.Invitation) {
	rows := fmt.Sprint(row)
	var tmp byte
	tmp = 0
	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.UserID)
	tmp = tmp + 1
	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.Name)
	tmp = tmp + 1
	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.Email)
	tmp = tmp + 1

	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.Phone)
	tmp = tmp + 1

	formatTime := userRecord.CreatedUnix.Format("2006-01-02 15:04:05")
	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, formatTime[0:len(formatTime)-3])
	tmp = tmp + 1

	xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.SrcUserID)
}

func DownloadInvitationDetail(ctx *context.Context) {
	xlsx := excelize.NewFile()
	sheetName := ctx.Tr("user.static.invitationdetailsheetname")
	index := xlsx.NewSheet(sheetName)
	xlsx.DeleteSheet("Sheet1")
	excelHeader := getInvitationDetailExcelHeader(ctx)
	for k, v := range excelHeader {
		//设置单元格的值
		xlsx.SetCellValue(sheetName, k, v)
	}
	userNameMap := models.GetAllUserName()
	_, count := models.QueryInvitaionPage(1, 1)
	var indexTotal int64
	indexTotal = 0
	row := 1
	for {
		re, _ := models.QueryInvitaionPage(int(indexTotal), PAGE_SIZE)
		log.Info("return count=" + fmt.Sprint(count))
		for _, userRecord := range re {
			row++
			userRecord.Name = userNameMap[userRecord.UserID]
			if userRecord.Name == "" {
				userRecord.Name = "已注销"
			}
			writeInvitationDetailExcel(row, xlsx, sheetName, userRecord)
		}
		indexTotal += PAGE_SIZE
		if indexTotal >= count {
			break
		}
	}
	//设置默认打开的表单
	xlsx.SetActiveSheet(index)
	filename := sheetName + ".xlsx"
	ctx.Resp.Header().Set("Content-Disposition", "attachment; filename="+url.QueryEscape(filename))
	ctx.Resp.Header().Set("Content-Type", "application/octet-stream")
	if _, err := xlsx.WriteTo(ctx.Resp); err != nil {
		log.Info("writer exel error." + err.Error())
	}
}

func queryDataFromStaticTable(ctx *context.Context, tableName string, queryObj interface{}) {
	page, pageSize := getPageInfo(ctx)
	userName := ctx.Query("userName")
	IsReturnFile := ctx.QueryBool("IsReturnFile")

	if IsReturnFile {
		//writer exec file.
		xlsx := excelize.NewFile()
		sheetName := ctx.Tr("user.static.invitationsheetname")
		index := xlsx.NewSheet(sheetName)
		xlsx.DeleteSheet("Sheet1")
		excelHeader := getInvitationExcelHeader(ctx)
		for k, v := range excelHeader {
			//设置单元格的值
			xlsx.SetCellValue(sheetName, k, v)
		}
		_, count := models.QueryUserInvitationDataByTableName(1, 1, tableName, queryObj, "", 1)
		var indexTotal int64
		indexTotal = 0
		row := 1
		for {
			re, _ := models.QueryUserInvitationDataByTableName(int(indexTotal), PAGE_SIZE, tableName, queryObj, "", 1)
			log.Info("return count=" + fmt.Sprint(count))
			for _, userRecord := range re {
				row++
				writeInvitationExcel(row, xlsx, sheetName, userRecord)
			}
			indexTotal += PAGE_SIZE
			if indexTotal >= count {
				break
			}
		}
		//设置默认打开的表单
		xlsx.SetActiveSheet(index)
		filename := sheetName + "_" + ctx.Tr("user.static."+tableName) + ".xlsx"
		ctx.Resp.Header().Set("Content-Disposition", "attachment; filename="+url.QueryEscape(filename))
		ctx.Resp.Header().Set("Content-Type", "application/octet-stream")
		if _, err := xlsx.WriteTo(ctx.Resp); err != nil {
			log.Info("writer exel error." + err.Error())
		}
	} else {
		resultRecord, count := models.QueryUserInvitationDataByTableName((page-1)*pageSize, pageSize, tableName, queryObj, userName, 1)
		result := make([]models.Invitation, 0)
		for _, record := range resultRecord {
			invi := models.Invitation{
				SrcUserID:         record.ID,
				Name:              record.Name,
				InvitationUserNum: record.InvitationUserNum,
				Phone:             record.Phone,
				CreatedUnix:       record.RegistDate,
			}
			result = append(result, invi)
		}
		mapInterface := make(map[string]interface{})
		mapInterface["data"] = result
		mapInterface["count"] = count
		ctx.JSON(http.StatusOK, mapInterface)
	}
}

func QueryInvitationCurrentWeek(ctx *context.Context) {
	// currentTimeNow := time.Now()
	// offset := int(time.Monday - currentTimeNow.Weekday())
	// if offset > 0 {
	// 	offset = -6
	// }
	// pageStartTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 0, 0, 0, 0, time.Local).AddDate(0, 0, offset)
	// pageEndTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 0, 0, 0, 0, currentTimeNow.Location())
	// queryData(ctx, pageStartTime.Unix(), pageEndTime.Unix())
	queryDataFromStaticTable(ctx, "public.user_business_analysis_current_week", new(models.UserBusinessAnalysisCurrentWeek))
}

func QueryInvitationLastWeek(ctx *context.Context) {
	// currentTimeNow := time.Now()
	// offset := int(time.Monday - currentTimeNow.Weekday())
	// if offset > 0 {
	// 	offset = -6
	// }
	// pageEndTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 0, 0, 0, 0, time.Local).AddDate(0, 0, offset)
	// pageStartTime := pageEndTime.AddDate(0, 0, -7)
	// queryData(ctx, pageStartTime.Unix(), pageEndTime.Unix())

	queryDataFromStaticTable(ctx, "public.user_business_analysis_last_week", new(models.UserBusinessAnalysisLastWeek))
}

func QueryInvitationCurrentYear(ctx *context.Context) {
	// currentTimeNow := time.Now()
	// pageStartTime := time.Date(currentTimeNow.Year(), 1, 1, 0, 0, 0, 0, currentTimeNow.Location())
	// pageEndTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 0, 0, 0, 0, currentTimeNow.Location())
	// queryData(ctx, pageStartTime.Unix(), pageEndTime.Unix())

	queryDataFromStaticTable(ctx, "public.user_business_analysis_current_year", new(models.UserBusinessAnalysisCurrentYear))
}

func QueryInvitationLast30Day(ctx *context.Context) {
	// currentTimeNow := time.Now()
	// pageStartTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 0, 0, 0, 0, time.Local).AddDate(0, 0, -30)
	// pageEndTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 0, 0, 0, 0, currentTimeNow.Location())
	// queryData(ctx, pageStartTime.Unix(), pageEndTime.Unix())

	queryDataFromStaticTable(ctx, "public.user_business_analysis_last30_day", new(models.UserBusinessAnalysisLast30Day))
}

func QueryInvitationLastMonth(ctx *context.Context) {
	// currentTimeNow := time.Now()
	// thisMonth := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), 1, 0, 0, 0, 0, currentTimeNow.Location())
	// pageStartTime := thisMonth.AddDate(0, -1, 0)
	// pageEndTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), 1, 23, 59, 59, 0, currentTimeNow.Location()).AddDate(0, 0, -1)
	// queryData(ctx, pageStartTime.Unix(), pageEndTime.Unix())

	queryDataFromStaticTable(ctx, "public.user_business_analysis_last_month", new(models.UserBusinessAnalysisLastMonth))
}

func QueryInvitationYesterday(ctx *context.Context) {
	// currentTimeNow := time.Now().AddDate(0, 0, -1)
	// pageStartTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 0, 0, 0, 0, time.Local)
	// pageEndTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 23, 59, 59, 0, currentTimeNow.Location())
	// queryData(ctx, pageStartTime.Unix(), pageEndTime.Unix())

	queryDataFromStaticTable(ctx, "public.user_business_analysis_yesterday", new(models.UserBusinessAnalysisYesterday))
}

func QueryInvitationAll(ctx *context.Context) {
	// currentTimeNow := time.Now()
	// pageStartTime := time.Date(2022, 8, 5, 0, 0, 0, 0, currentTimeNow.Location())
	// pageEndTime := time.Date(currentTimeNow.Year(), currentTimeNow.Month(), currentTimeNow.Day(), 0, 0, 0, 0, currentTimeNow.Location())
	// queryData(ctx, pageStartTime.Unix(), pageEndTime.Unix())

	queryDataFromStaticTable(ctx, "public.user_business_analysis_all", new(models.UserBusinessAnalysisAll))
}

func QueryUserDefineInvitationPage(ctx *context.Context) {
	startDate := ctx.Query("startDate")
	endDate := ctx.Query("endDate")
	startTime, _ := time.ParseInLocation("2006-01-02", startDate, time.Local)
	//startTime = startTime.UTC()
	endTime, _ := time.ParseInLocation("2006-01-02", endDate, time.Local)

	queryData(ctx, startTime, endTime)
}

func queryData(ctx *context.Context, startTime time.Time, endTime time.Time) {
	page, pageSize := getPageInfo(ctx)
	IsReturnFile := ctx.QueryBool("IsReturnFile")

	dbResult := models.QueryInvitaionByTime(startTime.Unix(), endTime.Unix())

	invitaionNumMap := make(map[int64]int, 0)
	allUserIds := make([]int64, 0)
	for _, record := range dbResult {
		if _, ok := invitaionNumMap[record.SrcUserID]; !ok {
			invitaionNumMap[record.SrcUserID] = 1
		} else {
			invitaionNumMap[record.SrcUserID] = invitaionNumMap[record.SrcUserID] + 1
		}
	}
	invitaionNumList := make([]models.Invitation, 0)
	for key, value := range invitaionNumMap {
		invi := models.Invitation{
			SrcUserID:         key,
			InvitationUserNum: value,
		}
		invitaionNumList = append(invitaionNumList, invi)
		allUserIds = append(allUserIds, key)
	}
	sort.Slice(invitaionNumList, func(i, j int) bool {
		return invitaionNumList[i].InvitationUserNum > invitaionNumList[j].InvitationUserNum
	})
	if IsReturnFile {
		xlsx := excelize.NewFile()
		sheetName := ctx.Tr("user.static.invitationsheetname")
		index := xlsx.NewSheet(sheetName)
		xlsx.DeleteSheet("Sheet1")
		excelHeader := getInvitationExcelHeader(ctx)
		for k, v := range excelHeader {
			//设置单元格的值
			xlsx.SetCellValue(sheetName, k, v)
		}
		end := 100
		userMap := make(map[int64]*models.User, 0)
		log.Info("len(allUserIds)=" + fmt.Sprint(len(allUserIds)))
		for i := 0; i < len(allUserIds); i += 100 {
			if end >= len(allUserIds) {
				end = len(allUserIds)
			}
			log.Info("i=" + fmt.Sprint(i) + " end=" + fmt.Sprint(end))
			if i == end {
				break
			}
			userList, err := models.GetUsersByIDs(allUserIds[i:end])
			if err == nil {
				for _, tmp := range userList {
					userMap[tmp.ID] = tmp
				}
			} else {

			}
			end = end + 100
		}
		row := 1
		log.Info("len(userMap)=" + fmt.Sprint(len(userMap)))
		for _, userRecord := range invitaionNumList {
			row++
			rows := fmt.Sprint(row)
			var tmp byte
			tmp = 0

			xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.SrcUserID)
			tmp = tmp + 1
			name := "已注销"
			if userMap[userRecord.SrcUserID] != nil {
				name = userMap[userRecord.SrcUserID].Name
			}
			xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, name)
			tmp = tmp + 1

			xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, userRecord.InvitationUserNum)
			tmp = tmp + 1
			Phone := ""
			if userMap[userRecord.SrcUserID] != nil {
				Phone = userMap[userRecord.SrcUserID].PhoneNumber
			}
			xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, Phone)
			tmp = tmp + 1

			formatTime := ""
			if userMap[userRecord.SrcUserID] != nil {
				formatTime = userMap[userRecord.SrcUserID].CreatedUnix.Format("2006-01-02 15:04:05")
				formatTime = formatTime[0 : len(formatTime)-3]
			}
			xlsx.SetCellValue(sheetName, getColumn(tmp)+rows, formatTime)
		}
		//设置默认打开的表单
		xlsx.SetActiveSheet(index)
		filename := sheetName + "_" + getTimeFileName(startTime) + "_" + getTimeFileName(endTime) + ".xlsx"
		//filename := sheetName + "_" + ctx.Tr("user.static."+tableName) + ".xlsx"
		ctx.Resp.Header().Set("Content-Disposition", "attachment; filename="+url.QueryEscape(filename))
		ctx.Resp.Header().Set("Content-Type", "application/octet-stream")
		if _, err := xlsx.WriteTo(ctx.Resp); err != nil {
			log.Info("writer exel error." + err.Error())
		}
	} else {
		result := make([]*models.Invitation, 0)
		userIds := make([]int64, 0)
		end := len(invitaionNumList) - 1
		for start := (page - 1) * pageSize; start <= end; start++ {
			invi := invitaionNumList[start]
			//todo name phone,createunix
			result = append(result, &invi)
			userIds = append(userIds, invi.SrcUserID)
			if len(result) == pageSize {
				break
			}
		}
		userList, err := models.GetUsersByIDs(userIds)
		if err == nil {
			for _, invi := range result {
				tmpUser := userList[0]
				for _, tmp := range userList {
					if tmp.ID == invi.SrcUserID {
						tmpUser = tmp
						break
					}
				}
				if invi.SrcUserID == tmpUser.ID {
					invi.Name = tmpUser.Name
					invi.Phone = tmpUser.PhoneNumber
					invi.CreatedUnix = tmpUser.CreatedUnix
					invi.Email = tmpUser.Email
				} else {
					invi.Name = "已注销"
				}
			}
		} else {
			log.Info("query user error." + err.Error())
		}
		mapInterface := make(map[string]interface{})
		mapInterface["data"] = result
		mapInterface["count"] = len(invitaionNumList)
		ctx.JSON(http.StatusOK, mapInterface)
	}
}

func getPageInfo(ctx *context.Context) (int, int) {
	page := ctx.QueryInt("page")
	if page <= 0 {
		page = 1
	}
	pageSize := ctx.QueryInt("pageSize")
	if pageSize <= 0 {
		pageSize = setting.UI.IssuePagingNum
	}
	return page, pageSize
}