biService/service/exprot.go

package service

import (
	"archive/zip"
	"bp.jydev.jianyu360.cn/BaseService/biService/rpc/pb"
	"fmt"
	"io"
	"log"
	"math/rand"
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"time"

	"app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/mail"
	. "bp.jydev.jianyu360.cn/BaseService/biService/entity"
	"github.com/gogf/gf/v2/util/gconv"
	"github.com/xuri/excelize/v2"
	"github.com/zeromicro/go-zero/core/logx"
)

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
	FileName string
	Datas    []*pb.FilesData
}

var reg1 = regexp.MustCompile("(?i)(insert|delete|update|master|truncate|declare|exec|alter|use)\\s")
var reg2 = regexp.MustCompile("(?i)(select|from)\\s")

// 全量项目导出
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 {
		folderPath := fmt.Sprintf("%s/%s", ExportDirectory, timeStr)
		compressFiles(pathArr, folderPath, timeStr)
		pathstr := fmt.Sprintf("%s/%s/%s.zip", ExportUrl, timeStr, 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 {
		folderPath := fmt.Sprintf("%s/%s", ExportDirectory, timeStr)
		compressFiles(pathArr, folderPath, timeStr)
		pathstr := fmt.Sprintf("%s/%s/%s.zip", ExportUrl, timeStr, 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, ExportDirectory, timeStr)
			fileCounter++
			pathArr = append(pathArr, path)
		}
		dataHandle(writer, l, a+1, tableColumn)
		a++
		return true
	}, sqlStr)
	if a > 0 && a != ExportCount {
		writer, file, path = Warehousing(writer, file, fileCounter, key, ExportDirectory, 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{}, path, timeStr string) (*excelize.StreamWriter, *excelize.File, string) {
	writer.Flush()
	folderPath := fmt.Sprintf("%s/%s", path, timeStr)
	_, err := os.Stat(folderPath)
	if os.IsNotExist(err) {
		//创建文件夹
		err := os.MkdirAll(folderPath, os.ModePerm)
		if err != nil {
			logx.Info("创建文件夹失败:", err)
		}
	}
	fileName := fmt.Sprintf("%s/output%d.xlsx", folderPath, fileCounter)
	err = file.SaveAs(fileName)
	if err != nil {
		logx.Info(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 InirWrite1(i int, sheet string, key []interface{}, file *excelize.File) (*excelize.StreamWriter, *excelize.File) {
	//文件初始化
	if file == nil {
		file = excelize.NewFile()
	}
	if i == 1 {
		//设置表名
		file.SetSheetName(fmt.Sprintf("Sheet%d", i), sheet)
		//创建流式写入
		writer, err := file.NewStreamWriter(sheet)
		logx.Error(err)
		// 定义每个 Excel 文件的数据列数限制
		writer.SetRow("A1", key)
		return writer, file
	} else {
		file.NewSheet(sheet)
		//创建流式写入
		writer, err := file.NewStreamWriter(sheet)
		logx.Error(err)
		// 定义每个 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 {
			logx.Info(target_mail, fmt.Sprintf("使用%s发送邮件成功", v.User))
			break
		}
		if k < len(GmailAuth)-1 {
			logx.Info(target_mail, fmt.Sprintf("使用%s发送邮件失败！3s后使用其他邮箱尝试", v.User))
		} else {
			logx.Info(target_mail, fmt.Sprintf("使用%s发送邮件失败！", v.User))
		}
		time.Sleep(time.Second * 3)
	}
	return fool
}

// 文件压缩
func compressFiles(filePattern []string, path, timeStr string) {
	_, err := os.Stat(path)
	if os.IsNotExist(err) {
		//创建文件夹
		err := os.MkdirAll(path, os.ModePerm)
		if err != nil {
			logx.Info("创建文件夹失败:", err)
		}
	}
	zipFilename := fmt.Sprintf("%s/%s.zip", path, 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)
	//return fmt.Sprintf("%s/%s/%s.zip", url, timeStr, timeStr)
}

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

	stype := "public"

	var writer *excelize.StreamWriter
	var file *excelize.File

	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))
	path1 := fmt.Sprintf("%s/%s", ComFileDir, stype)

	var pathArr []string
	for i, data := range e.Datas {
		key := hName(data.Sql)
		sheet := data.SheetName
		writer, file = InirWrite1(i+1, sheet, key, file)

		exportA(data.Sql, key, writer)
	}
	fpath := ""
	writer, file, fpath = WarehousingA(writer, file, path1, timeStr, e.FileName)
	pathArr = append(pathArr, fpath)
	if len(pathArr) > 0 {
		var files []string
		for _, s := range pathArr {
			ss := strings.Split(s, "/")
			files = append(files, fmt.Sprintf("%s/%s/%s/%s.xlsx", ComFileUrl, stype, timeStr, ss[len(ss)-1]))
		}
		state := sendMailA(e.Title, e.Mails, e.Content, strings.Join(files, ","))
		BiService.Insert("export_record", map[string]interface{}{
			"positionId": "0",
			"type":       stype,
			"excelPath":  strings.Join(pathArr, ","),
			"zipPath":    "",
			"sendState":  state,
			"createTime": time.Now().Format(DateTime),
		})
		return gconv.Bytes(map[string]interface{}{"status": 1, "msg": "导出数据成功"})
	}
	return gconv.Bytes(map[string]interface{}{"status": -1, "msg": "导出数据失败，未查询到数据"})
}

// 表头
func hName(sql string) []interface{} {
	if reg1.MatchString(sql) && !reg2.MatchString(sql) {
		return []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") {
			a1 := strings.Split(s1, "as")
			l1 := len(a1)
			chiReg := regexp.MustCompile("[^\u4e00-\u9fa5]")
			if !chiReg.MatchString(a1[l1-1]) {
				key = append(key, a1[l1-1])
			}
		}
	}
	if key != nil {
		return key
	} else {
		return []interface{}{}
	}
}

// 导出开始
func exportA(sql string, key []interface{}, writer *excelize.StreamWriter) {
	a := 0
	JyBiTidb.SelectByBath(1, func(l *[]map[string]interface{}) bool {
		dataHandle(writer, l, a+1, common.ObjArrToStringArr(key))
		a++
		return true
	}, sql)
}

func WarehousingA(writer *excelize.StreamWriter, file *excelize.File, path, timeStr, fileName string) (*excelize.StreamWriter, *excelize.File, string) {
	writer.Flush()
	folderPath := fmt.Sprintf("%s/%s", path, timeStr)
	_, err := os.Stat(folderPath)
	if os.IsNotExist(err) {
		//创建文件夹
		err := os.MkdirAll(folderPath, os.ModePerm)
		if err != nil {
			logx.Info("创建文件夹失败:", err)
		}
	}
	f := fmt.Sprintf("%s/%s.xlsx", folderPath, fileName)
	err = file.SaveAs(f)
	if err != nil {
		logx.Info(err)
	}
	return writer, file, fileName
}

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 {
			logx.Info(target_mail, fmt.Sprintf("使用%s发送邮件成功", v.User))
			break
		}
		if k < len(GmailAuth)-1 {
			logx.Info(target_mail, fmt.Sprintf("使用%s发送邮件失败！3s后使用其他邮箱尝试", v.User))
		} else {
			logx.Info(target_mail, fmt.Sprintf("使用%s发送邮件失败！", v.User))
		}
		time.Sleep(time.Second * 3)
	}
	return fool
}