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  }