biService/service/exprot.go

package service

import (
	"app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/mail"
	"archive/zip"
	. "bp.jydev.jianyu360.cn/BaseService/biService/entity"
	"fmt"
	"github.com/gogf/gf/v2/util/gconv"
	"github.com/xuri/excelize/v2"
	"io"
	"log"
	"math/rand"
	"os"
	"path/filepath"
	"strings"
	"time"
)

var (
	DateTime = "2006-01-02 15:04:05"
)

type ExportReq struct {
	Mail       string
	Mapping    []string
	PositionId int64
}

type ExportByDbReq struct {
	Token   string
	Title   string
	Content string
	Mails   string
	Query   string
	Stype   string
}

// 全量项目导出
func (a *ExportReq) AllProjectExport() {
	tableColumn := []string{}
	exportKey := []interface{}{}
	for _, value := range a.Mapping {
		valueArr := strings.Split(value, ":")
		tableColumn = append(tableColumn, valueArr[0])
		exportKey = append(exportKey, valueArr[1])
	}
	timeStr := time.Now().Format("20060102150405")
	rand.Seed(time.Now().UnixNano())
	randomInt := rand.Intn(10000) // 生成0到9999之间的随机整数
	timeStr = fmt.Sprintf("%s%s", timeStr, gconv.String(randomInt))
	pathArr := export("customer_data_ttzl_project", tableColumn, exportKey, timeStr)
	if len(pathArr) > 0 {
		pathstr := compressFiles(pathArr, ExportUrl, timeStr)
		state := sendMail("项目", a.Mail, pathstr)
		BiService.Insert("export_record", map[string]interface{}{
			"positionId": a.PositionId,
			"type":       "project",
			"excelPath":  strings.Join(pathArr, ","),
			"zipPath":    pathstr,
			"sendState":  state,
			"createTime": time.Now().Format(DateTime),
		})
	}
}

// 全量资讯导出
func (a *ExportReq) AllInfoExport() {
	tableColumn := []string{}
	exportKey := []interface{}{}
	for _, value := range a.Mapping {
		valueArr := strings.Split(value, ":")
		tableColumn = append(tableColumn, valueArr[0])
		exportKey = append(exportKey, valueArr[1])
	}
	timeStr := time.Now().Format("20060102150405")
	rand.Seed(time.Now().UnixNano())
	randomInt := rand.Intn(10000) // 生成0到9999之间的随机整数
	timeStr = fmt.Sprintf("%s%s", timeStr, gconv.String(randomInt))
	pathArr := export("customer_data_ttzl", tableColumn, exportKey, timeStr)
	if len(pathArr) > 0 {
		pathstr := compressFiles(pathArr, ExportUrl, timeStr)
		state := sendMail("标讯", a.Mail, pathstr)
		BiService.Insert("export_record", map[string]interface{}{
			"positionId": a.PositionId,
			"type":       "project",
			"excelPath":  strings.Join(pathArr, ","),
			"zipPath":    pathstr,
			"sendState":  state,
			"createTime": time.Now().Format(DateTime),
		})
	}
}

// 导出开始
func export(table string, tableColumn []string, key []interface{}, timeStr string) []string {
	pathArr := []string{}
	sqlStr := fmt.Sprintf("SELECT %s FROM %s", strings.Join(tableColumn, ","), table)
	fileCounter := 1
	writer, file := InirWrite(key)
	a := 0
	path := ""
	BiService.SelectByBath(1, func(l *[]map[string]interface{}) bool {
		if a%ExportCount == 0 && a > 0 {
			a = 0
			//入excel处理
			writer, file, path = Warehousing(writer, file, fileCounter, key, timeStr)
			fileCounter++
			pathArr = append(pathArr, path)
		}
		dataHandle(writer, l, a, tableColumn)
		a++
		return true
	}, sqlStr)
	if a > 0 && a != ExportCount {
		writer, file, path = Warehousing(writer, file, fileCounter, key, timeStr)
		pathArr = append(pathArr, path)
	}
	return pathArr
}

// 数据处理
func dataHandle(writer *excelize.StreamWriter, l *[]map[string]interface{}, a int, tableColumn []string) {
	for _, value := range *l {
		valueArr := []interface{}{}
		for _, column := range tableColumn {
			valueArr = append(valueArr, gconv.String(value[column]))
		}
		cell, _ := excelize.CoordinatesToCellName(1, a+1)
		writer.SetRow(cell, valueArr)
	}
}

// 入excel
func Warehousing(writer *excelize.StreamWriter, file *excelize.File, fileCounter int, key []interface{}, timeStr string) (*excelize.StreamWriter, *excelize.File, string) {
	log.Println(fmt.Sprintf("开始第%d数据", fileCounter))
	writer.Flush()
	folderPath := fmt.Sprintf("%s/%s", ComFileDir, timeStr)
	_, err := os.Stat(folderPath)
	if os.IsNotExist(err) {
		//创建文件夹
		err := os.MkdirAll(folderPath, os.ModePerm)
		if err != nil {
			log.Println("创建文件夹失败:", err)
		}
	}
	fileName := fmt.Sprintf("%s/output%d.xlsx", folderPath, fileCounter)
	err = file.SaveAs(fileName)
	if err != nil {
		log.Println(err)
	}
	writer, file = InirWrite(key)
	return writer, file, fileName
}

// excel初始化
func InirWrite(key []interface{}) (*excelize.StreamWriter, *excelize.File) {
	//文件初始化
	file := excelize.NewFile()
	//设置表名
	file.SetSheetName("Sheet1", "表1")
	//创建流式写入
	writer, _ := file.NewStreamWriter("表1")
	// 定义每个 Excel 文件的数据列数限制
	writer.SetRow("A1", key)
	return writer, file
}

// 发送邮箱
func sendMail(title, target_mail string, path string) bool {
	mailStr := "<html lang=\"en\">\n<head>\n    <meta charset=\"UTF-8\">\n    <title>铁塔全量%s数据导出</title>\n</head>\n<body>\n<div>\n    <p>铁塔全量%s数据已导出成功,导出时间：%s,请点击链接下载数据：<a href=\"%s\">%s</a></p>\n  </div>\n</body>"
	html := fmt.Sprintf(mailStr, title, title, time.Now().Format(DateTime), path, path)
	fool := false
	for k, v := range GmailAuth {
		fool := mail.GSendMail("剑鱼标讯", target_mail, "", "", fmt.Sprintf("铁塔全量%s数据导出", title), html, "", "", v)
		if fool {
			log.Println(target_mail, fmt.Sprintf("使用%s发送邮件成功", v.User))
			break
		}
		if k < len(GmailAuth)-1 {
			log.Println(target_mail, fmt.Sprintf("使用%s发送邮件失败！3s后使用其他邮箱尝试", v.User))
		} else {
			log.Println(target_mail, fmt.Sprintf("使用%s发送邮件失败！", v.User))
		}
		time.Sleep(time.Second * 3)
	}
	return fool
}

// 文件压缩
func compressFiles(filePattern []string, url, timeStr string) string {
	folderPath := fmt.Sprintf("%s/%s", ComFileDir, timeStr)
	_, err := os.Stat(folderPath)
	if os.IsNotExist(err) {
		//创建文件夹
		err := os.MkdirAll(folderPath, os.ModePerm)
		if err != nil {
			log.Println("创建文件夹失败:", err)
		}
	}
	zipFilename := fmt.Sprintf("%s/%s.zip", folderPath, timeStr)
	// 创建 ZIP 文件
	zipFile, err := os.Create(zipFilename)
	if err != nil {
		log.Fatal(err)
	}
	defer zipFile.Close()

	// 创建 ZIP Writer
	zipWriter := zip.NewWriter(zipFile)
	defer zipWriter.Close()

	// 遍历文件列表
	for _, file := range filePattern {
		// 打开文件
		f, err := os.Open(file)
		if err != nil {
			log.Fatal(err)
		}
		defer f.Close()
		// 获取文件信息
		_, err = f.Stat()
		if err != nil {
			log.Fatal(err)
		}
		//文件名切割
		nameArr := strings.Split(file, "/")
		name := nameArr[len(nameArr)-1]
		name = fmt.Sprintf("%s/%s", timeStr, name)
		// 创建 ZIP 内部文件
		zipFile, err := zipWriter.Create(name)
		if err != nil {
			log.Fatal(err)
		}
		// 将文件内容复制到 ZIP 文件中
		_, err = io.Copy(zipFile, f)
		if err != nil {
			log.Fatal(err)
		}
	}
	// 打印压缩文件路径
	absPath, err := filepath.Abs(zipFilename)
	if err != nil {
		log.Fatal(err)
	}
	log.Printf("Files compressed to: %s", absPath)
	return fmt.Sprintf("%s/%s/%s.zip", url, timeStr, timeStr)
}

// 数据导出(通用)
func (e *ExportByDbReq) ExportDataByDb() []byte {

	key := hName(e.Query)
	timeStr := time.Now().Format("20060102150405")
	rand.Seed(time.Now().UnixNano())
	randomInt := rand.Intn(10000) // 生成0到9999之间的随机整数
	timeStr = fmt.Sprintf("%s%s", timeStr, gconv.String(randomInt))
	stype := e.Stype
	if stype == "" {
		stype = "public"
	}
	pathArr := exportA(e.Query, stype, key)
	if len(pathArr) > 0 {
		pathstr := compressFiles(pathArr, ComFileUrl, stype)
		state := sendMailA(e.Title, e.Mails, e.Content, pathstr)

		BiService.Insert("export_record", map[string]interface{}{
			"positionId": "0",
			"type":       stype,
			"excelPath":  strings.Join(pathArr, ","),
			"zipPath":    pathstr,
			"sendState":  state,
			"createTime": time.Now().Format(DateTime),
		})
	}
	return gconv.Bytes(map[string]interface{}{"status": 1})
}

// 表头
func hName(sql string) []interface{} {
	var key []interface{}
	s := strings.Split(strings.ToLower(sql), "from")
	keystr := strings.ReplaceAll(s[0], "select", "")
	keystr = strings.ReplaceAll(keystr, " ", "")
	for _, s1 := range strings.Split(keystr, ",") {
		if strings.Contains(s1, "as") {
			key = append(key, strings.Split(s1, "as")[1])
		}
	}
	return key
}

// 导出开始
func exportA(sql, timeStr string, key []interface{}) []string {
	pathArr := []string{}
	fileCounter := 1
	writer, file := InirWrite(key)
	a := 0
	path := ""
	BiService.SelectByBath(1, func(l *[]map[string]interface{}) bool {
		if a%ExportCount == 0 && a > 0 {
			a = 0
			//入excel处理
			writer, file, path = Warehousing(writer, file, fileCounter, key, timeStr)
			fileCounter++
			pathArr = append(pathArr, path)
		}
		dataHandle(writer, l, a, common.ObjArrToStringArr(key))
		a++
		return true
	}, sql)
	if a > 0 && a != ExportCount {
		writer, file, path = Warehousing(writer, file, fileCounter, key, timeStr)
		pathArr = append(pathArr, path)
	}
	return pathArr
}

func sendMailA(title, target_mail, content string, path string) bool {
	html := strings.ReplaceAll(content, "#downLoadUrl#", path)
	fool := false
	for k, v := range GmailAuth {
		fool := mail.GSendMail("剑鱼标讯", target_mail, "", "", fmt.Sprintf("%s数据导出", title), html, "", "", v)
		if fool {
			log.Println(target_mail, fmt.Sprintf("使用%s发送邮件成功", v.User))
			break
		}
		if k < len(GmailAuth)-1 {
			log.Println(target_mail, fmt.Sprintf("使用%s发送邮件失败！3s后使用其他邮箱尝试", v.User))
		} else {
			log.Println(target_mail, fmt.Sprintf("使用%s发送邮件失败！", v.User))
		}
		time.Sleep(time.Second * 3)
	}
	return fool
}