BaseService
/
biService


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213
							package service

import (
	"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"
	"os"
	"path/filepath"
	"strings"
	"time"
)

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

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")
	pathArr := export("customer_data_ttzl_project", tableColumn, exportKey, timeStr)
	if len(pathArr) > 0 {
		pathstr := compressFiles(pathArr, 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(time.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")
	pathArr := export("customer_data_ttzl", tableColumn, exportKey, timeStr)
	if len(pathArr) > 0 {
		pathstr := compressFiles(pathArr, 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(time.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%2000 == 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 != 2000 {
		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)
	}
}
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("static/excel/%s", 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
}

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(target_mail string, path string) bool {
	mailStr := "<html lang=\"en\">\n<head>\n    <meta charset=\"UTF-8\">\n    <title>铁塔全量标讯数据导出</title>\n</head>\n<body>\n<div>\n    <p>铁塔全量标讯数据已导出成功，请查收附件，导出时间：%s,请点击链接下载数据：<a href=\"%s\">%s</a></p>\n  </div>\n</body>"
	html := fmt.Sprintf(mailStr, time.Now().Format(time.DateTime), path, path)
	fool := false
	for k, v := range GmailAuth {
		fool := mail.GSendMail("剑鱼标讯", target_mail, "", "", "铁塔全量标讯数据导出", 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, timeStr string) string {
	folderPath := fmt.Sprintf("static/zip/%s", 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/output%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)
		}
		// 创建 ZIP 内部文件
		zipFile, err := zipWriter.Create(file)
		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 zipFilename
}