FranklinWH-Toolbox/service/test/getSiteVersion/main.go

package main

import (
	"context"
	"fmt"
	"os"
	"sort"
	"strings"
	"time"

	"github.com/xuri/excelize/v2"
	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
)

// SiteVersionInfo 用于存储站点版本相关字段
type SiteVersionInfo struct {
	Sn           string `bson:"_id"`
	Version      string `bson:"version"`
	ProtocolVer  string `bson:"protocolVer"`
	SysHdVersion int    `bson:"sysHdVersion"`
}

// VersionCombination 版本组合
type VersionCombination struct {
	Version      string
	ProtocolVer  string
	SysHdVersion int
	Count        int
	Sites        []string // 记录前几个站点SN作为示例
}

const (
	collectionName = "SiteInfoManageData"
	maxExampleSites = 5 // 每个组合最多记录的示例站点数
)

func main() {
	// 从环境变量获取 MongoDB DSN
	mongoDSN := os.Getenv("MONGO_DSN")
	if mongoDSN == "" {
		fmt.Println("请设置 MONGO_DSN 环境变量")
		fmt.Println("例如: export MONGO_DSN=mongodb://127.0.0.1:27017/FwhToolboxDb")
		os.Exit(1)
	}

	// 连接 MongoDB
	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	client, err := mongo.Connect(ctx, options.Client().ApplyURI(mongoDSN))
	if err != nil {
		fmt.Printf("MongoDB 连接失败: %v\n", err)
		os.Exit(1)
	}
	defer client.Disconnect(ctx)

	if err := client.Ping(ctx, nil); err != nil {
		fmt.Printf("MongoDB Ping 失败: %v\n", err)
		os.Exit(1)
	}
	fmt.Println("MongoDB 连接成功")

	// 从 URI 中解析数据库名
	parts := strings.Split(mongoDSN, "/")
	dbName := parts[len(parts)-1]
	// 移除可能的查询参数
	if idx := strings.Index(dbName, "?"); idx != -1 {
		dbName = dbName[:idx]
	}

	db := client.Database(dbName)
	collection := db.Collection(collectionName)

	// 只查询需要的字段以提高性能
	projection := bson.M{
		"_id":          1,
		"version":      1,
		"protocolVer":  1,
		"sysHdVersion": 1,
	}

	findOpts := options.Find().SetProjection(projection)

	cursor, err := collection.Find(ctx, bson.M{}, findOpts)
	if err != nil {
		fmt.Printf("查询失败: %v\n", err)
		os.Exit(1)
	}
	defer cursor.Close(ctx)

	// 统计版本组合
	type comboKey struct {
		Version      string
		ProtocolVer  string
		SysHdVersion int
	}

	comboMap := make(map[comboKey]*VersionCombination)
	totalCount := 0

	for cursor.Next(ctx) {
		var site SiteVersionInfo
		if err := cursor.Decode(&site); err != nil {
			fmt.Printf("解码失败 (sn=%s): %v\n", site.Sn, err)
			continue
		}
		totalCount++

		key := comboKey{
			Version:      site.Version,
			ProtocolVer:  site.ProtocolVer,
			SysHdVersion: site.SysHdVersion,
		}

		if combo, exists := comboMap[key]; exists {
			combo.Count++
			if len(combo.Sites) < maxExampleSites {
				combo.Sites = append(combo.Sites, site.Sn)
			}
		} else {
			comboMap[key] = &VersionCombination{
				Version:      site.Version,
				ProtocolVer:  site.ProtocolVer,
				SysHdVersion: site.SysHdVersion,
				Count:        1,
				Sites:        []string{site.Sn},
			}
		}
	}

	if err := cursor.Err(); err != nil {
		fmt.Printf("游标遍历出错: %v\n", err)
		os.Exit(1)
	}

	// 将结果转为切片并排序（按数量降序）
	combos := make([]*VersionCombination, 0, len(comboMap))
	for _, combo := range comboMap {
		combos = append(combos, combo)
	}

	sort.Slice(combos, func(i, j int) bool {
		return combos[i].Count > combos[j].Count
	})

	// 输出结果
	fmt.Printf("\n========== 站点版本组合统计 ==========\n")
	fmt.Printf("总站点数: %d\n", totalCount)
	fmt.Printf("唯一版本组合数: %d\n", len(combos))
	fmt.Printf("======================================\n\n")

	fmt.Printf("%-6s | %-20s | %-15s | %-12s | %s\n",
		"序号", "Version", "ProtocolVer", "SysHdVersion", "站点数量")
	fmt.Println(strings.Repeat("-", 80))

	for i, combo := range combos {
		fmt.Printf("%-6d | %-20s | %-15s | %-12d | %d\n",
			i+1, combo.Version, combo.ProtocolVer, combo.SysHdVersion, combo.Count)
	}

	// 输出详细信息（包含示例站点）
	fmt.Printf("\n\n========== 详细信息（含示例站点）==========\n\n")
	for i, combo := range combos {
		fmt.Printf("[%d] Version=%s, ProtocolVer=%s, SysHdVersion=%d (共 %d 个站点)\n",
			i+1, combo.Version, combo.ProtocolVer, combo.SysHdVersion, combo.Count)
		fmt.Printf("    示例站点: %s\n", strings.Join(combo.Sites, ", "))
		fmt.Println()
	}

	// 生成 Excel 表格
	if err := exportToExcel(combos, totalCount); err != nil {
		fmt.Printf("生成 Excel 失败: %v\n", err)
		os.Exit(1)
	}
}

// exportToExcel 将版本组合统计结果导出为 Excel 文件
func exportToExcel(combos []*VersionCombination, totalCount int) error {
	f := excelize.NewFile()
	defer f.Close()

	sheetName := "版本组合统计"
	// 重命名默认 Sheet
	f.SetSheetName("Sheet1", sheetName)

	// 设置表头样式
	headerStyle, _ := f.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Size: 12},
		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"#DCE6F1"}},
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
		Border: []excelize.Border{
			{Type: "left", Color: "#000000", Style: 1},
			{Type: "top", Color: "#000000", Style: 1},
			{Type: "bottom", Color: "#000000", Style: 1},
			{Type: "right", Color: "#000000", Style: 1},
		},
	})

	// 设置列宽
	f.SetColWidth(sheetName, "A", "A", 8)
	f.SetColWidth(sheetName, "B", "B", 22)
	f.SetColWidth(sheetName, "C", "C", 18)
	f.SetColWidth(sheetName, "D", "D", 15)
	f.SetColWidth(sheetName, "E", "E", 12)
	f.SetColWidth(sheetName, "F", "F", 60)

	// 写入汇总信息
	f.SetCellValue(sheetName, "A1", "站点版本组合统计报告")
	f.SetCellValue(sheetName, "A2", fmt.Sprintf("总站点数: %d", totalCount))
	f.SetCellValue(sheetName, "A3", fmt.Sprintf("唯一版本组合数: %d", len(combos)))
	f.SetCellValue(sheetName, "A4", fmt.Sprintf("生成时间: %s", time.Now().Format("2006-01-02 15:04:05")))

	// 写入表头 (从第6行开始)
	headers := []string{"序号", "Version", "ProtocolVer", "SysHdVersion", "站点数量", "示例站点"}
	for i, header := range headers {
		cell := fmt.Sprintf("%s%d", columnName(i+1), 6)
		f.SetCellValue(sheetName, cell, header)
		f.SetCellStyle(sheetName, cell, cell, headerStyle)
	}

	// 写入数据
	for i, combo := range combos {
		row := i + 7 // 数据从第7行开始
		f.SetCellValue(sheetName, fmt.Sprintf("A%d", row), i+1)
		f.SetCellValue(sheetName, fmt.Sprintf("B%d", row), combo.Version)
		f.SetCellValue(sheetName, fmt.Sprintf("C%d", row), combo.ProtocolVer)
		f.SetCellValue(sheetName, fmt.Sprintf("D%d", row), combo.SysHdVersion)
		f.SetCellValue(sheetName, fmt.Sprintf("E%d", row), combo.Count)
		f.SetCellValue(sheetName, fmt.Sprintf("F%d", row), strings.Join(combo.Sites, ", "))
	}

	// 保存文件
	fileName := fmt.Sprintf("site_version_combinations_%s.xlsx", time.Now().Format("20060102_150405"))
	if err := f.SaveAs(fileName); err != nil {
		return fmt.Errorf("保存 Excel 文件失败: %w", err)
	}

	fmt.Printf("\n✅ Excel 文件已生成: %s\n", fileName)
	return nil
}

// columnName 将列号(1-based)转为Excel列名
func columnName(col int) string {
	name, _ := excelize.ColumnNumberToName(col)
	return name
}