github.com/easysoft/zendata@v0.0.0-20240513203326-705bd5a7fd67/cmd/test/others/func/export/export-words.go (about) 1 package main 2 3 import ( 4 "fmt" 5 "path/filepath" 6 7 "github.com/360EntSecGroup-Skylar/excelize/v2" 8 "github.com/easysoft/zendata/cmd/test/others/func/comm" 9 "github.com/easysoft/zendata/cmd/test/others/func/model" 10 fileUtils "github.com/easysoft/zendata/pkg/utils/file" 11 "gorm.io/gorm" 12 ) 13 14 func main() { 15 db := comm.GetDB() 16 db.AutoMigrate( 17 &model.DataWord{}, 18 ) 19 20 // load tag groups 21 groups := make([]model.DataWordTagGroup, 0) 22 db.Order("id ASC").Find(&groups) 23 24 // gen sheet by tag group 25 for _, group := range groups { 26 sheetName := group.Name 27 filePath := fmt.Sprintf("data/words/v1/%s.xlsx", sheetName) 28 29 fileUtils.MkDirIfNeeded(filepath.Dir(filePath)) 30 31 f := excelize.NewFile() 32 index := f.NewSheet(sheetName) 33 f.SetActiveSheet(index) 34 sheet1 := f.GetSheetName(0) 35 f.DeleteSheet(sheet1) 36 37 allTags := loadTagsByGroup(group.Id, db) 38 words := loadWordsByGroup(group.Id, db) 39 40 // gen headers 41 excelColIndex := 'A' 42 excelColId := fmt.Sprintf("%c%d", excelColIndex, 1) 43 f.SetCellValue(sheetName, excelColId, "词") 44 excelColIndex++ 45 46 for _, tag := range allTags { 47 excelColId := fmt.Sprintf("%c%d", excelColIndex, 1) 48 f.SetCellValue(sheetName, excelColId, tag.Name) 49 50 excelColIndex++ 51 } 52 53 // gen word row 54 wordIndex := 2 55 for _, word := range words { 56 tags := loadTagsByWord(word.Id, group.Id, db) 57 58 // gen word's tag data 59 wordTagMap := map[string]bool{} 60 for _, tag := range tags { 61 wordTagMap[tag.Name] = true 62 } 63 64 // gen rows 65 excelColIndex := 'A' 66 excelColId := fmt.Sprintf("%c%d", excelColIndex, wordIndex) 67 f.SetCellValue(sheetName, excelColId, word.Word) 68 excelColIndex++ 69 70 for _, tag := range allTags { 71 excelColId := fmt.Sprintf("%c%d", excelColIndex, wordIndex) 72 73 val := "" 74 if wordTagMap[tag.Name] { 75 val = "Y" 76 } 77 f.SetCellValue(sheetName, excelColId, val) 78 79 excelColIndex++ 80 } 81 82 wordIndex++ 83 } 84 85 f.SaveAs(filePath) 86 } 87 } 88 89 func loadTagsByWord(wordId uint, groupId uint, db *gorm.DB) (tags []model.DataWordTag) { 90 tagIds := make([]int, 0) 91 92 sqlTags := fmt.Sprintf("SELECT r.data_word_tag_id "+ 93 "FROM biz_data_word_biz_data_word_tag r "+ 94 "WHERE r.data_word_id = %d AND r.data_word_tag_id IN "+ 95 " (SELECT data_word_tag_id FROM zendata.biz_data_word_tag_group_biz_data_word_tag "+ 96 " WHERE data_word_tag_group_id = %d) ", 97 wordId, groupId) 98 99 db.Raw(sqlTags).Scan(&tagIds) 100 101 db.Where("id IN (?) AND NOT deleted", tagIds).Find(&tags) 102 103 return 104 } 105 106 func loadTagsByGroup(groupId uint, db *gorm.DB) (tags []model.DataWordTag) { 107 sqlTags := fmt.Sprintf("SELECT t.* "+ 108 "FROM biz_data_word_tag t "+ 109 "WHERE t.id IN "+ 110 " (SELECT data_word_tag_id FROM zendata.biz_data_word_tag_group_biz_data_word_tag "+ 111 " WHERE data_word_tag_group_id = %d) "+ 112 "AND NOT t.deleted "+ 113 "ORDER BY t.id ", 114 groupId) 115 116 db.Raw(sqlTags).Scan(&tags) 117 118 return 119 } 120 121 func loadWordsByGroup(groupId uint, db *gorm.DB) (words []model.DataWord) { 122 sqlWords := fmt.Sprintf("SELECT w.* FROM biz_data_word w "+ 123 "WHERE w.tag_group_id = %d AND NOT w.deleted "+ 124 "ORDER BY w.id", 125 groupId) 126 db.Raw(sqlWords).Scan(&words) 127 128 return 129 }