FranklinWH-Toolbox/service/services/siteInfoManageServices.go

package services

import (
	"context"
	"fmt"
	"fwh-toolbox-server/domain"
	"fwh-toolbox-server/dto"
	"fwh-toolbox-server/repositories"
	"fwh-toolbox-server/task/queue/fwhQueue"
	"fwh-toolbox-server/utils/global"
	"math"
	"regexp"
	"strconv"
	"time"

	"github.com/xuri/excelize/v2"
	"go.uber.org/zap"
)

var VERSION_REGEX = regexp.MustCompile(`V(\d+)R(\d+)B(\d+)D(\d+)`)

// 获取更新状态
func GetSiteInfoUpdateStatus() (*domain.SiteInfoUpdateRecord, error) {
	return repositories.GetSiteInfoUpdateNewRecord()
}

// 请求更新站点信息表
func UpdateSiteInfoDate() error {
	id, err := repositories.CreateSiteInfoUpdateInfo()
	if err != nil {
		return err
	}

	if err := fwhQueue.EnqueueSiteInfoUpdateTask(domain.TaskSiteInfoUpdate{
		Id: *id,
	}); err != nil {
		// 入队失败时标记记录为失败，避免一直停在 pending 被当成“同步中”
		if e := repositories.UpdateSiteInfoUpdateResult(*id, domain.ProgressError, 0); e != nil {
			zap.L().Error("UpdateSiteInfoDate mark error failed.", zap.String("id", id.Hex()), zap.String("err", e.Error()))
		}
		return err
	}
	return nil
}

// 获取站点信息表
func QuerySiteInfoDataList(para dto.SiteInfoRequest) (*dto.ResPage[dto.SiteInfoDTO], error) {
	res, err := repositories.GetSiteInfoManageList(para.PageNum, para.PageSize, para.Filter, para.Search)
	if err != nil {
		return nil, err
	}

	idList := []string{}
	for _, data := range res.Data {
		if data.ExtTags != nil {
			idList = append(idList, *data.ExtTags...)
		}
	}
	tagMap, err := repositories.GetSiteInfoTagsFromId(idList)
	if err != nil {
		zap.L().Error("QuerySiteInfoDataList GetSiteInfoTagsFromId failed.", zap.String("err", err.Error()), zap.Strings("idList", idList))
		return nil, err
	}

	result := dto.ResPage[dto.SiteInfoDTO]{
		Data:     dto.SiteInfoDomainListToSiteInfoDTOList(res.Data, &tagMap),
		PageSize: res.PageSize,
		PageNum:  res.PageNum,
		Total:    res.Total,
	}

	return &result, nil
}

// 导出站点信息表
func ExportSiteInfoDataList(filer *domain.SiteInfoFilter) (*excelize.File, error) {
	res, err := repositories.GetSiteInfoManageList(0, 0, filer, "")
	if err != nil {
		return nil, err
	}

	record, err := repositories.GetSiteInfoUpdateNewRecord()
	if err != nil {
		return nil, err
	}

	// 创建新的excel文件
	f := excelize.NewFile()

	// 写标题行
	f.SetCellValue("Sheet1", "A1", "SN")
	f.SetCellValue("Sheet1", "B1", "Installer")
	f.SetCellValue("Sheet1", "C1", "ActiveTime")
	f.SetCellValue("Sheet1", "D1", "Status")
	f.SetCellValue("Sheet1", "E1", "OnLineTime")
	f.SetCellValue("Sheet1", "F1", "OffLineTime")
	f.SetCellValue("Sheet1", "G1", "FhpSn")
	f.SetCellValue("Sheet1", "H1", "Version")
	f.SetCellValue("Sheet1", "I1", "SlVersion")
	f.SetCellValue("Sheet1", "J1", "SlVersionJson")
	f.SetCellValue("Sheet1", "K1", "MeterVersion")
	f.SetCellValue("Sheet1", "L1", "MbVersion")
	f.SetCellValue("Sheet1", "M1", "AwsVersion")
	f.SetCellValue("Sheet1", "N1", "AppVersion")
	f.SetCellValue("Sheet1", "O1", "ProtocolVer")
	f.SetCellValue("Sheet1", "P1", "SysHdVersion")
	f.SetCellValue("Sheet1", "Q1", "Remark")
	f.SetCellValue("Sheet1", "R1", "Country")
	f.SetCellValue("Sheet1", "S1", "Province")
	f.SetCellValue("Sheet1", "T1", "ManufactureDate")
	f.SetCellValue("Sheet1", "U1", "HardwareVersion")
	f.SetCellValue("Sheet1", "V1", "CveStatus")
	f.SetCellValue("Sheet1", "W1", "SecurityPatchLevel")
	f.SetCellValue("Sheet1", "X1", "TargetVersion")
	f.SetCellValue("Sheet1", "Y1", "LastUpgradeTime")
	f.SetCellValue("Sheet1", "Z1", "UpgradeStatus")
	f.SetCellValue("Sheet1", "AA1", "Owner/Maintainer")
	f.SetCellValue("Sheet1", "AB1", "DataSource")
	f.SetCellValue("Sheet1", "AC1", "LastUpdated")
	f.SetCellValue("Sheet1", "AD1", "ConnectType")
	f.SetCellValue("Sheet1", "AE1", "WifiSignal")
	f.SetCellValue("Sheet1", "AF1", "4GSignal")

	// 写入数据
	for i, d := range res.Data {
		f.SetCellValue("Sheet1", "A"+strconv.Itoa(i+2), d.Sn)
		f.SetCellValue("Sheet1", "B"+strconv.Itoa(i+2), global.Deref(d.InstallerName, ""))
		f.SetCellValue("Sheet1", "C"+strconv.Itoa(i+2), d.ActiveTime)
		f.SetCellValue("Sheet1", "D"+strconv.Itoa(i+2), func() string {
			switch d.Status {
			case 0:
				return "离线(0)"
			case 1:
				return "在线(1)"
			default:
				return fmt.Sprintf("未知(%d)", d.Status)
			}
		}())
		f.SetCellValue("Sheet1", "E"+strconv.Itoa(i+2), global.Deref(d.OnLineTime, ""))
		f.SetCellValue("Sheet1", "F"+strconv.Itoa(i+2), global.Deref(d.OffLineTime, ""))
		f.SetCellValue("Sheet1", "G"+strconv.Itoa(i+2), d.FhpSn)
		f.SetCellValue("Sheet1", "H"+strconv.Itoa(i+2), d.Version)
		f.SetCellValue("Sheet1", "I"+strconv.Itoa(i+2), d.SlVersion)
		f.SetCellValue("Sheet1", "J"+strconv.Itoa(i+2), d.SlVersionJson)
		f.SetCellValue("Sheet1", "K"+strconv.Itoa(i+2), global.Deref(d.MeterVersion, ""))
		f.SetCellValue("Sheet1", "L"+strconv.Itoa(i+2), global.Deref(d.MbVersion, ""))
		f.SetCellValue("Sheet1", "M"+strconv.Itoa(i+2), global.Deref(d.AwsVersion, ""))
		f.SetCellValue("Sheet1", "N"+strconv.Itoa(i+2), global.Deref(d.AppVersion, ""))
		f.SetCellValue("Sheet1", "O"+strconv.Itoa(i+2), d.ProtocolVer)
		f.SetCellValue("Sheet1", "P"+strconv.Itoa(i+2), d.SysHdVersion)
		f.SetCellValue("Sheet1", "Q"+strconv.Itoa(i+2), global.Deref(d.Remark, ""))
		f.SetCellValue("Sheet1", "R"+strconv.Itoa(i+2), d.Country)
		f.SetCellValue("Sheet1", "S"+strconv.Itoa(i+2), d.Province)
		var manufactureDate string = ""
		if len(d.Sn) > 9 {
			year := d.Sn[len(d.Sn)-8 : len(d.Sn)-6]
			week := d.Sn[len(d.Sn)-6 : len(d.Sn)-4]
			manufactureDate = fmt.Sprintf("%s年%s周", year, week)
		}
		f.SetCellValue("Sheet1", "T"+strconv.Itoa(i+2), manufactureDate)
		f.SetCellValue("Sheet1", "U"+strconv.Itoa(i+2), "V"+strconv.Itoa(d.SysHdVersion))
		f.SetCellValue("Sheet1", "V"+strconv.Itoa(i+2), "无")
		f.SetCellValue("Sheet1", "W"+strconv.Itoa(i+2), "安全")
		f.SetCellValue("Sheet1", "X"+strconv.Itoa(i+2), d.Version)
		f.SetCellValue("Sheet1", "Y"+strconv.Itoa(i+2), global.Deref(d.FirmUpgradeTime, ""))
		f.SetCellValue("Sheet1", "Z"+strconv.Itoa(i+2), "成功")
		f.SetCellValue("Sheet1", "AA"+strconv.Itoa(i+2), "Jerry Liang/LMT")
		f.SetCellValue("Sheet1", "AB"+strconv.Itoa(i+2), "Fleetview")
		f.SetCellValue("Sheet1", "AC"+strconv.Itoa(i+2), record.UpdateTime.Format(time.DateTime))
		f.SetCellValue("Sheet1", "AD"+strconv.Itoa(i+2), func() string {
			switch d.ConnType {
			case 0:
				return "未连接(0)"
			case 1:
				return "eth0(1)"
			case 2:
				return "eth1(2)"
			case 3:
				return "WIFI(3)"
			case 4:
				return "4G(4)"
			default:
				return fmt.Sprintf("未知(%d)", d.Status)
			}
		}())
		f.SetCellValue("Sheet1", "AE"+strconv.Itoa(i+2), func() string {
			s, err := strconv.ParseInt(d.WifiSignal, 10, 64)
			if err != nil {
				zap.L().Warn("WifiSignal ParseInt failed", zap.String("WifiSignal", d.WifiSignal), zap.String("err", err.Error()))
				return fmt.Sprintf("未知信号(%s)", d.WifiSignal)
			}
			if s < 0 {
				return fmt.Sprintf("Disable(%s)", d.WifiSignal)
			} else if s < 40 {
				return fmt.Sprintf("很弱/不可用(%s) %ddBm", d.WifiSignal, s/2-100)
			} else if s < 60 {
				return fmt.Sprintf("弱(%s) %ddBm", d.WifiSignal, s/2-100)
			} else if s < 80 {
				return fmt.Sprintf("中等/一般(%s) %ddBm", d.WifiSignal, s/2-100)
			} else {
				return fmt.Sprintf("强/良好(%s) > -50dBm", d.WifiSignal)
			}
		}())
		f.SetCellValue("Sheet1", "AF"+strconv.Itoa(i+2), func() string {
			if d.Signal == nil {
				return "Not Signal"
			}
			s, err := strconv.ParseInt(*d.Signal, 10, 64)
			if err != nil {
				zap.L().Warn("Signal ParseInt failed", zap.String("4GSignal", *d.Signal), zap.String("err", err.Error()))
				return fmt.Sprintf("未知信号(%s)", *d.Signal)
			}

			// 判断软件版本
			isNew := false
			if d.AppVersion != nil && *d.AppVersion != "" {
				match := VERSION_REGEX.FindStringSubmatch(*d.AppVersion)
				if match != nil {
					v := match[1]
					r := match[2]
					b := match[3]
					d := match[4]
					_ = d // 当前逻辑未使用
					if v == "10" {
						rInt, _ := strconv.Atoi(r)
						bInt, _ := strconv.Atoi(b)

						if rInt > 5 {
							isNew = true
						} else if rInt == 4 && bInt >= 14 {
							isNew = true
						}
					}
				}
			}

			if isNew {
				if s < 0 {
					return fmt.Sprintf("Disable(%s)", *d.Signal)
				} else if s <= 12 {
					return fmt.Sprintf("很差(%s) %ddBm", *d.Signal, int(math.Round(float64(s)/2.5-115)))
				} else if s <= 37 {
					return fmt.Sprintf("较差(%s) %ddBm", *d.Signal, int(math.Round(float64(s)/2.5-115)))
				} else if s <= 62 {
					return fmt.Sprintf("一般(%s) %ddBm", *d.Signal, int(math.Round(float64(s)/2.5-115)))
				} else if s <= 87 {
					return fmt.Sprintf("良好(%s) %ddBm", *d.Signal, int(math.Round(float64(s)/2.5-115)))
				} else {
					return fmt.Sprintf("极好(%s) %ddBm", *d.Signal, int(math.Round(float64(s)/2.5-115)))
				}
			} else {
				if s < 0 {
					return fmt.Sprintf("Disable(%s)", *d.Signal)
				} else if s <= 1 {
					return fmt.Sprintf("很差(%s) %ddBm", *d.Signal, s*2-113)
				} else if s <= 6 {
					return fmt.Sprintf("较差(%s) %ddBm", *d.Signal, s*2-113)
				} else if s <= 11 {
					return fmt.Sprintf("一般(%s) %ddBm", *d.Signal, s*2-113)
				} else if s <= 16 {
					return fmt.Sprintf("良好(%s) %ddBm", *d.Signal, s*2-113)
				} else {
					return fmt.Sprintf("极好(%s) %ddBm", *d.Signal, s*2-113)
				}
			}
		}())
	}

	return f, err
}

// 查询单个站点实时信息（同时更新单个站点数据）
func GetSiteInfoData(sn string) (*dto.SiteInfoDTO, error) {
	res, err := repositories.QuerySiteInfoList(dto.SiteInfoPara{
		SN:       &sn,
		PageSize: 10,
		PageNum:  1,
	})
	if err != nil {
		return nil, err
	}

	if res.Result == nil {
		return nil, fmt.Errorf("code: %s, msg: %s", strconv.Itoa(res.Code), res.Message)
	}

	if len(*res.Result) == 0 {
		return nil, fmt.Errorf("code: %s, msg: %s", strconv.Itoa(res.Code), res.Message)
	}

	domainData := domain.FwhSiteInfoToSiteInfoDomain(&(*res.Result)[0])

	// 数据插入数据库
	if err := repositories.InsertSiteInfoData(*domainData); err != nil {
		zap.L().Error("保存站点信息失败", zap.String("err", err.Error()), zap.String("sn", sn))
	}

	tagMap := make(map[string]*dto.SiteTagDTO)
	if domainData.ExtTags != nil {
		tagMap, err = repositories.GetSiteInfoTagsFromId(*domainData.ExtTags)
		if err != nil {
			zap.L().Error("GetSiteInfoData GetSiteInfoTagsFromId failed.", zap.String("err", err.Error()), zap.Strings("idList", *domainData.ExtTags))
			return nil, err
		}
	}

	dtoData := dto.SiteInfoDomainToSiteInfoDTO(domainData, &tagMap)

	return dtoData, nil
}

// 使能站点xlog
func OperateSiteInfoEnableXlog(ctx context.Context, sn string) (string, error) {
	return repositories.FwhMqttEnableXlog(ctx, sn)
}

// 获取站点信息标签列表
func GetSiteInfoTags(page uint, size uint, search string) (*dto.ResPage[dto.SiteTagDTO], error) {
	tags, err := repositories.GetSiteInfoTags(page, size, search)
	if err != nil {
		return nil, err
	}

	result := dto.ResPage[dto.SiteTagDTO]{
		Data:     []dto.SiteTagDTO{},
		PageSize: tags.PageSize,
		PageNum:  tags.PageNum,
		Total:    tags.Total,
	}
	for _, tag := range tags.Data {
		result.Data = append(result.Data, *dto.SiteTagDomainToSiteTagDTO(&tag))
	}

	return &result, nil
}

// 创建或更新站点信息标签
func UpdateSiteInfoTag(tag dto.SiteTagDTO) error {
	return repositories.UpdateSiteInfoTag(*dto.SiteSiteTagDTOToTagDomain(&tag))
}