exprot.go 6.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238
  1. package service
  2. import (
  3. "app.yhyue.com/moapp/jybase/mail"
  4. "archive/zip"
  5. . "bp.jydev.jianyu360.cn/BaseService/biService/entity"
  6. "fmt"
  7. "github.com/gogf/gf/v2/util/gconv"
  8. "github.com/xuri/excelize/v2"
  9. "io"
  10. "log"
  11. "math/rand"
  12. "os"
  13. "path/filepath"
  14. "strings"
  15. "time"
  16. )
  17. var (
  18. DateTime = "2006-01-02 15:04:05"
  19. )
  20. type ExportReq struct {
  21. Mail string
  22. Mapping []string
  23. PositionId int64
  24. }
  25. // 全量项目导出
  26. func (a *ExportReq) AllProjectExport() {
  27. tableColumn := []string{}
  28. exportKey := []interface{}{}
  29. for _, value := range a.Mapping {
  30. valueArr := strings.Split(value, ":")
  31. tableColumn = append(tableColumn, valueArr[0])
  32. exportKey = append(exportKey, valueArr[1])
  33. }
  34. timeStr := time.Now().Format("20060102150405")
  35. rand.Seed(time.Now().UnixNano())
  36. randomInt := rand.Intn(10000) // 生成0到9999之间的随机整数
  37. timeStr = fmt.Sprintf("%s%s", timeStr, gconv.String(randomInt))
  38. pathArr := export("customer_data_ttzl_project", tableColumn, exportKey, timeStr)
  39. if len(pathArr) > 0 {
  40. pathstr := compressFiles(pathArr, timeStr)
  41. state := sendMail("项目", a.Mail, pathstr)
  42. BiService.Insert("export_record", map[string]interface{}{
  43. "positionId": a.PositionId,
  44. "type": "project",
  45. "excelPath": strings.Join(pathArr, ","),
  46. "zipPath": pathstr,
  47. "sendState": state,
  48. "createTime": time.Now().Format(DateTime),
  49. })
  50. }
  51. }
  52. // 全量资讯导出
  53. func (a *ExportReq) AllInfoExport() {
  54. tableColumn := []string{}
  55. exportKey := []interface{}{}
  56. for _, value := range a.Mapping {
  57. valueArr := strings.Split(value, ":")
  58. tableColumn = append(tableColumn, valueArr[0])
  59. exportKey = append(exportKey, valueArr[1])
  60. }
  61. timeStr := time.Now().Format("20060102150405")
  62. rand.Seed(time.Now().UnixNano())
  63. randomInt := rand.Intn(10000) // 生成0到9999之间的随机整数
  64. timeStr = fmt.Sprintf("%s%s", timeStr, gconv.String(randomInt))
  65. pathArr := export("customer_data_ttzl", tableColumn, exportKey, timeStr)
  66. if len(pathArr) > 0 {
  67. pathstr := compressFiles(pathArr, timeStr)
  68. state := sendMail("标讯", a.Mail, pathstr)
  69. BiService.Insert("export_record", map[string]interface{}{
  70. "positionId": a.PositionId,
  71. "type": "project",
  72. "excelPath": strings.Join(pathArr, ","),
  73. "zipPath": pathstr,
  74. "sendState": state,
  75. "createTime": time.Now().Format(DateTime),
  76. })
  77. }
  78. }
  79. // 导出开始
  80. func export(table string, tableColumn []string, key []interface{}, timeStr string) []string {
  81. pathArr := []string{}
  82. sqlStr := fmt.Sprintf("SELECT %s FROM %s", strings.Join(tableColumn, ","), table)
  83. fileCounter := 1
  84. writer, file := InirWrite(key)
  85. a := 0
  86. path := ""
  87. BiService.SelectByBath(1, func(l *[]map[string]interface{}) bool {
  88. if a%ExportCount == 0 && a > 0 {
  89. a = 0
  90. //入excel处理
  91. writer, file, path = Warehousing(writer, file, fileCounter, key, timeStr)
  92. fileCounter++
  93. pathArr = append(pathArr, path)
  94. }
  95. dataHandle(writer, l, a, tableColumn)
  96. a++
  97. return true
  98. }, sqlStr)
  99. if a > 0 && a != ExportCount {
  100. writer, file, path = Warehousing(writer, file, fileCounter, key, timeStr)
  101. pathArr = append(pathArr, path)
  102. }
  103. return pathArr
  104. }
  105. // 数据处理
  106. func dataHandle(writer *excelize.StreamWriter, l *[]map[string]interface{}, a int, tableColumn []string) {
  107. for _, value := range *l {
  108. valueArr := []interface{}{}
  109. for _, column := range tableColumn {
  110. valueArr = append(valueArr, gconv.String(value[column]))
  111. }
  112. cell, _ := excelize.CoordinatesToCellName(1, a+1)
  113. writer.SetRow(cell, valueArr)
  114. }
  115. }
  116. // 入excel
  117. func Warehousing(writer *excelize.StreamWriter, file *excelize.File, fileCounter int, key []interface{}, timeStr string) (*excelize.StreamWriter, *excelize.File, string) {
  118. log.Println(fmt.Sprintf("开始第%d数据", fileCounter))
  119. writer.Flush()
  120. folderPath := fmt.Sprintf("%s/%s", ExportDirectory, timeStr)
  121. _, err := os.Stat(folderPath)
  122. if os.IsNotExist(err) {
  123. //创建文件夹
  124. err := os.MkdirAll(folderPath, os.ModePerm)
  125. if err != nil {
  126. log.Println("创建文件夹失败:", err)
  127. }
  128. }
  129. fileName := fmt.Sprintf("%s/output%d.xlsx", folderPath, fileCounter)
  130. err = file.SaveAs(fileName)
  131. if err != nil {
  132. log.Println(err)
  133. }
  134. writer, file = InirWrite(key)
  135. return writer, file, fileName
  136. }
  137. // excel初始化
  138. func InirWrite(key []interface{}) (*excelize.StreamWriter, *excelize.File) {
  139. //文件初始化
  140. file := excelize.NewFile()
  141. //设置表名
  142. file.SetSheetName("Sheet1", "表1")
  143. //创建流式写入
  144. writer, _ := file.NewStreamWriter("表1")
  145. // 定义每个 Excel 文件的数据行数限制
  146. writer.SetRow("A1", key)
  147. return writer, file
  148. }
  149. // 发送邮箱
  150. func sendMail(title, target_mail string, path string) bool {
  151. 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>"
  152. html := fmt.Sprintf(mailStr, title, title, time.Now().Format(DateTime), path, path)
  153. fool := false
  154. for k, v := range GmailAuth {
  155. fool := mail.GSendMail("剑鱼标讯", target_mail, "", "", fmt.Sprintf("铁塔全量%s数据导出", title), html, "", "", v)
  156. if fool {
  157. log.Println(target_mail, fmt.Sprintf("使用%s发送邮件成功", v.User))
  158. break
  159. }
  160. if k < len(GmailAuth)-1 {
  161. log.Println(target_mail, fmt.Sprintf("使用%s发送邮件失败!3s后使用其他邮箱尝试", v.User))
  162. } else {
  163. log.Println(target_mail, fmt.Sprintf("使用%s发送邮件失败!", v.User))
  164. }
  165. time.Sleep(time.Second * 3)
  166. }
  167. return fool
  168. }
  169. // 文件压缩
  170. func compressFiles(filePattern []string, timeStr string) string {
  171. folderPath := fmt.Sprintf("%s/%s", ExportDirectory, timeStr)
  172. _, err := os.Stat(folderPath)
  173. if os.IsNotExist(err) {
  174. //创建文件夹
  175. err := os.MkdirAll(folderPath, os.ModePerm)
  176. if err != nil {
  177. log.Println("创建文件夹失败:", err)
  178. }
  179. }
  180. zipFilename := fmt.Sprintf("%s/%s.zip", folderPath, timeStr)
  181. // 创建 ZIP 文件
  182. zipFile, err := os.Create(zipFilename)
  183. if err != nil {
  184. log.Fatal(err)
  185. }
  186. defer zipFile.Close()
  187. // 创建 ZIP Writer
  188. zipWriter := zip.NewWriter(zipFile)
  189. defer zipWriter.Close()
  190. // 遍历文件列表
  191. for _, file := range filePattern {
  192. // 打开文件
  193. f, err := os.Open(file)
  194. if err != nil {
  195. log.Fatal(err)
  196. }
  197. defer f.Close()
  198. // 获取文件信息
  199. _, err = f.Stat()
  200. if err != nil {
  201. log.Fatal(err)
  202. }
  203. //文件名切割
  204. nameArr := strings.Split(file, "/")
  205. name := nameArr[len(nameArr)-1]
  206. name = fmt.Sprintf("%s/%s", timeStr, name)
  207. // 创建 ZIP 内部文件
  208. zipFile, err := zipWriter.Create(name)
  209. if err != nil {
  210. log.Fatal(err)
  211. }
  212. // 将文件内容复制到 ZIP 文件中
  213. _, err = io.Copy(zipFile, f)
  214. if err != nil {
  215. log.Fatal(err)
  216. }
  217. }
  218. // 打印压缩文件路径
  219. absPath, err := filepath.Abs(zipFilename)
  220. if err != nil {
  221. log.Fatal(err)
  222. }
  223. log.Printf("Files compressed to: %s", absPath)
  224. return fmt.Sprintf("%s/%s/%s.zip", ExportUrl, timeStr, timeStr)
  225. }