123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238 |
- 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"
- "math/rand"
- "os"
- "path/filepath"
- "strings"
- "time"
- )
- var (
- DateTime = "2006-01-02 15:04:05"
- )
- 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")
- 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, 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, 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", ExportDirectory, 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, timeStr string) string {
- folderPath := fmt.Sprintf("%s/%s", ExportDirectory, 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", ExportUrl, timeStr, timeStr)
- }
|