6c8594633a
All checks were successful
构建并部署到测试环境（无 SSH） / build-and-deploy (push) Successful in 7m33s
450 lines
13 KiB
Go
450 lines
13 KiB
Go
package exporter
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
"strconv"
|
|
"time"
|
|
|
|
"gorm.io/gorm"
|
|
|
|
"github.com/break/junhong_cmp_fiber/pkg/constants"
|
|
)
|
|
|
|
const (
|
|
deviceExportBaseHeaderCount = 6
|
|
deviceExportCardGroupSize = 5
|
|
deviceExportTailHeaderCount = 5
|
|
deviceExportMinCardGroups = 1
|
|
)
|
|
|
|
// DeviceDataSource 设备导出数据源。
|
|
type DeviceDataSource struct {
|
|
db *gorm.DB
|
|
}
|
|
|
|
// NewDeviceDataSource 创建设备导出数据源。
|
|
func NewDeviceDataSource(db *gorm.DB) *DeviceDataSource {
|
|
return &DeviceDataSource{db: db}
|
|
}
|
|
|
|
// Scene 返回导出场景编码。
|
|
func (s *DeviceDataSource) Scene() string {
|
|
return constants.ExportTaskSceneDevice
|
|
}
|
|
|
|
// Count 统计设备导出行数。
|
|
func (s *DeviceDataSource) Count(ctx context.Context, params ExportParams) (int, error) {
|
|
var total int64
|
|
query := s.applyFilters(s.baseQuery(ctx), params)
|
|
if err := query.Count(&total).Error; err != nil {
|
|
return 0, err
|
|
}
|
|
return int(total), nil
|
|
}
|
|
|
|
// Headers 返回设备导出表头。
|
|
func (s *DeviceDataSource) Headers(ctx context.Context, params ExportParams) ([]string, error) {
|
|
cardGroups, err := s.resolveCardGroupCount(ctx, params)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return buildDeviceExportHeaders(cardGroups), nil
|
|
}
|
|
|
|
// Fetch 按 offset/limit 查询设备导出数据。
|
|
func (s *DeviceDataSource) Fetch(ctx context.Context, params ExportParams, offset, limit int) ([][]string, error) {
|
|
if limit <= 0 {
|
|
return [][]string{}, nil
|
|
}
|
|
|
|
cardGroups := cardGroupCountFromHeaders(params.ResolvedHeaders)
|
|
if cardGroups <= 0 {
|
|
resolved, err := s.resolveCardGroupCount(ctx, params)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
cardGroups = resolved
|
|
}
|
|
|
|
devices, err := s.fetchDevices(ctx, params, offset, limit)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
if len(devices) == 0 {
|
|
return [][]string{}, nil
|
|
}
|
|
|
|
deviceIDs := make([]uint, 0, len(devices))
|
|
for _, item := range devices {
|
|
deviceIDs = append(deviceIDs, item.ID)
|
|
}
|
|
|
|
cardMap, err := s.fetchBoundCards(ctx, deviceIDs)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
packageMap, err := s.fetchActivePackages(ctx, deviceIDs)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
rows := make([][]string, 0, len(devices))
|
|
for _, item := range devices {
|
|
rows = append(rows, buildDeviceExportRow(item, cardMap[item.ID], packageMap[item.ID], cardGroups))
|
|
}
|
|
return rows, nil
|
|
}
|
|
|
|
func (s *DeviceDataSource) baseQuery(ctx context.Context) *gorm.DB {
|
|
return s.db.WithContext(ctx).
|
|
Table("tb_device AS d").
|
|
Where("d.deleted_at IS NULL")
|
|
}
|
|
|
|
// applyFilters 应用与 /api/admin/devices 一致的筛选条件。
|
|
// 导出任务运行在 Worker 中，权限只能使用任务创建时保存的店铺范围快照。
|
|
func (s *DeviceDataSource) applyFilters(query *gorm.DB, params ExportParams) *gorm.DB {
|
|
query = applyExportShopScope(query, params, "d.shop_id")
|
|
|
|
if virtualNo, ok := filterString(params.Filters, "virtual_no"); ok {
|
|
query = query.Where("d.virtual_no LIKE ?", "%"+virtualNo+"%")
|
|
}
|
|
if imei, ok := filterString(params.Filters, "imei"); ok {
|
|
query = query.Where("d.imei LIKE ?", "%"+imei+"%")
|
|
}
|
|
if keyword, ok := filterString(params.Filters, "keyword"); ok {
|
|
like := "%" + keyword + "%"
|
|
query = query.Where("d.virtual_no LIKE ? OR d.imei LIKE ?", like, like)
|
|
}
|
|
if deviceName, ok := filterString(params.Filters, "device_name"); ok {
|
|
query = query.Where("d.device_name LIKE ?", "%"+deviceName+"%")
|
|
}
|
|
if status, ok := filterInt(params.Filters, "status"); ok && status > 0 {
|
|
query = query.Where("d.status = ?", status)
|
|
}
|
|
if activationStatus, ok := filterInt(params.Filters, "activation_status"); ok {
|
|
query = s.applyActivationStatusFilter(query, activationStatus)
|
|
}
|
|
if shopIDs, ok := filterUintSlice(params.Filters, "shop_ids"); ok {
|
|
if len(shopIDs) == 0 {
|
|
query = query.Where("1 = 0")
|
|
} else {
|
|
query = query.Where("d.shop_id IN ?", shopIDs)
|
|
}
|
|
} else if shopID, ok := filterInt(params.Filters, "shop_id"); ok {
|
|
if shopID == 0 {
|
|
query = query.Where("1 = 0")
|
|
} else if shopID > 0 {
|
|
query = query.Where("d.shop_id = ?", shopID)
|
|
}
|
|
}
|
|
if batchNo, ok := filterString(params.Filters, "batch_no"); ok {
|
|
query = query.Where("d.batch_no = ?", batchNo)
|
|
}
|
|
if deviceType, ok := filterString(params.Filters, "device_type"); ok {
|
|
query = query.Where("d.device_type = ?", deviceType)
|
|
}
|
|
if manufacturer, ok := filterString(params.Filters, "manufacturer"); ok {
|
|
query = query.Where("d.manufacturer LIKE ?", "%"+manufacturer+"%")
|
|
}
|
|
if start, ok := filterTime(params.Filters, "created_at_start"); ok {
|
|
query = query.Where("d.created_at >= ?", start)
|
|
}
|
|
if end, ok := filterTime(params.Filters, "created_at_end"); ok {
|
|
query = query.Where("d.created_at <= ?", end)
|
|
}
|
|
if seriesID, ok := filterUint(params.Filters, "series_id"); ok {
|
|
query = query.Where("d.series_id = ?", seriesID)
|
|
}
|
|
if hasActivePackage, ok := filterOptionalBool(params.Filters, "has_active_package"); ok {
|
|
query = s.applyHasActivePackageFilter(query, hasActivePackage)
|
|
}
|
|
return query
|
|
}
|
|
|
|
// applyHasActivePackageFilter 按设备是否有生效中的主套餐过滤。
|
|
func (s *DeviceDataSource) applyHasActivePackageFilter(query *gorm.DB, hasActive bool) *gorm.DB {
|
|
subQuery := `SELECT 1 FROM tb_package_usage AS pu
|
|
WHERE pu.device_id = d.id
|
|
AND pu.status = ?
|
|
AND pu.master_usage_id IS NULL
|
|
AND pu.deleted_at IS NULL`
|
|
if hasActive {
|
|
return query.Where("EXISTS ("+subQuery+")", constants.PackageUsageStatusActive)
|
|
}
|
|
return query.Where("NOT EXISTS ("+subQuery+")", constants.PackageUsageStatusActive)
|
|
}
|
|
|
|
func (s *DeviceDataSource) applyActivationStatusFilter(query *gorm.DB, activationStatus int) *gorm.DB {
|
|
activeCondition := `EXISTS (
|
|
SELECT 1
|
|
FROM tb_package_usage AS pu
|
|
JOIN tb_device_sim_binding AS b
|
|
ON b.device_id = pu.device_id
|
|
AND b.bind_status = ?
|
|
AND b.deleted_at IS NULL
|
|
JOIN tb_iot_card AS c
|
|
ON c.id = b.iot_card_id
|
|
AND c.real_name_status = ?
|
|
AND c.deleted_at IS NULL
|
|
WHERE pu.device_id = d.id
|
|
AND pu.status = ?
|
|
AND pu.master_usage_id IS NULL
|
|
AND pu.deleted_at IS NULL
|
|
)`
|
|
args := []any{
|
|
constants.BindStatusBound,
|
|
constants.RealNameStatusVerified,
|
|
constants.PackageUsageStatusActive,
|
|
}
|
|
if activationStatus == constants.ActivationStatusActive {
|
|
return query.Where(activeCondition, args...)
|
|
}
|
|
if activationStatus == constants.ActivationStatusInactive {
|
|
return query.Where("NOT "+activeCondition, args...)
|
|
}
|
|
return query
|
|
}
|
|
|
|
func (s *DeviceDataSource) resolveCardGroupCount(ctx context.Context, params ExportParams) (int, error) {
|
|
var count int64
|
|
query := s.applyFilters(s.baseQuery(ctx), params).
|
|
Select("COALESCE(MAX(bound_cards.card_count), 0)").
|
|
Joins(`
|
|
LEFT JOIN LATERAL (
|
|
SELECT COUNT(*) AS card_count
|
|
FROM tb_device_sim_binding AS b
|
|
WHERE b.device_id = d.id
|
|
AND b.bind_status = ?
|
|
AND b.deleted_at IS NULL
|
|
) AS bound_cards ON TRUE
|
|
`, constants.BindStatusBound)
|
|
if err := query.Scan(&count).Error; err != nil {
|
|
return 0, err
|
|
}
|
|
if count < deviceExportMinCardGroups {
|
|
return deviceExportMinCardGroups, nil
|
|
}
|
|
return int(count), nil
|
|
}
|
|
|
|
func (s *DeviceDataSource) fetchDevices(ctx context.Context, params ExportParams, offset, limit int) ([]deviceExportRow, error) {
|
|
var devices []deviceExportRow
|
|
query := s.applyFilters(s.baseQuery(ctx), params).
|
|
Joins("LEFT JOIN tb_shop AS sh ON sh.id = d.shop_id").
|
|
Joins("LEFT JOIN tb_package_series AS ps ON ps.id = d.series_id AND ps.deleted_at IS NULL").
|
|
Joins(`
|
|
LEFT JOIN tb_asset_wallet AS aw
|
|
ON aw.resource_type = ?
|
|
AND aw.resource_id = d.id
|
|
AND aw.deleted_at IS NULL
|
|
`, constants.AssetWalletResourceTypeDevice).
|
|
Select(`
|
|
d.id,
|
|
d.virtual_no,
|
|
d.imei,
|
|
d.device_name,
|
|
d.device_model,
|
|
COALESCE(sh.shop_name, '') AS shop_name,
|
|
COALESCE(ps.series_name, '') AS series_name,
|
|
COALESCE(aw.balance, 0) AS wallet_balance
|
|
`).
|
|
Order("d.id ASC").
|
|
Limit(limit).
|
|
Offset(offset)
|
|
if err := query.Scan(&devices).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
return devices, nil
|
|
}
|
|
|
|
func (s *DeviceDataSource) fetchBoundCards(ctx context.Context, deviceIDs []uint) (map[uint][]deviceExportCardRow, error) {
|
|
result := make(map[uint][]deviceExportCardRow, len(deviceIDs))
|
|
if len(deviceIDs) == 0 {
|
|
return result, nil
|
|
}
|
|
|
|
var cards []deviceExportCardRow
|
|
if err := s.db.WithContext(ctx).
|
|
Table("tb_device_sim_binding AS b").
|
|
Select(`
|
|
b.device_id,
|
|
b.slot_position,
|
|
c.iccid,
|
|
c.msisdn,
|
|
c.carrier_name,
|
|
c.current_month_usage_mb,
|
|
c.network_status
|
|
`).
|
|
Joins("JOIN tb_iot_card AS c ON c.id = b.iot_card_id AND c.deleted_at IS NULL").
|
|
Where("b.device_id IN ?", deviceIDs).
|
|
Where("b.bind_status = ?", constants.BindStatusBound).
|
|
Where("b.deleted_at IS NULL").
|
|
Order("b.device_id ASC, b.slot_position ASC, b.id ASC").
|
|
Scan(&cards).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
for _, card := range cards {
|
|
result[card.DeviceID] = append(result[card.DeviceID], card)
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
func (s *DeviceDataSource) fetchActivePackages(ctx context.Context, deviceIDs []uint) (map[uint]deviceExportPackageRow, error) {
|
|
result := make(map[uint]deviceExportPackageRow, len(deviceIDs))
|
|
if len(deviceIDs) == 0 {
|
|
return result, nil
|
|
}
|
|
|
|
var packages []deviceExportPackageRow
|
|
if err := s.db.WithContext(ctx).
|
|
Table("tb_package_usage AS pu").
|
|
Select(`
|
|
pu.device_id,
|
|
COALESCE(NULLIF(pu.package_name, ''), p.package_name, '') AS package_name,
|
|
pu.activated_at,
|
|
pu.expires_at
|
|
`).
|
|
Joins("LEFT JOIN tb_package AS p ON p.id = pu.package_id AND p.deleted_at IS NULL").
|
|
Where("pu.device_id IN ?", deviceIDs).
|
|
Where("pu.status = ?", constants.PackageUsageStatusActive).
|
|
Where("pu.master_usage_id IS NULL").
|
|
Where("pu.deleted_at IS NULL").
|
|
Order("pu.device_id ASC, pu.priority ASC, pu.activated_at ASC, pu.id ASC").
|
|
Scan(&packages).Error; err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
for _, item := range packages {
|
|
if _, exists := result[item.DeviceID]; exists {
|
|
continue
|
|
}
|
|
result[item.DeviceID] = item
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
type deviceExportRow struct {
|
|
ID uint `gorm:"column:id"`
|
|
VirtualNo string `gorm:"column:virtual_no"`
|
|
IMEI string `gorm:"column:imei"`
|
|
DeviceName string `gorm:"column:device_name"`
|
|
DeviceModel string `gorm:"column:device_model"`
|
|
ShopName string `gorm:"column:shop_name"`
|
|
SeriesName string `gorm:"column:series_name"`
|
|
WalletBalance int64 `gorm:"column:wallet_balance"`
|
|
}
|
|
|
|
type deviceExportCardRow struct {
|
|
DeviceID uint `gorm:"column:device_id"`
|
|
SlotPosition int `gorm:"column:slot_position"`
|
|
ICCID string `gorm:"column:iccid"`
|
|
MSISDN string `gorm:"column:msisdn"`
|
|
CarrierName string `gorm:"column:carrier_name"`
|
|
CurrentMonthUsageMB float64 `gorm:"column:current_month_usage_mb"`
|
|
NetworkStatus int `gorm:"column:network_status"`
|
|
}
|
|
|
|
type deviceExportPackageRow struct {
|
|
DeviceID uint `gorm:"column:device_id"`
|
|
PackageName string `gorm:"column:package_name"`
|
|
ActivatedAt *time.Time `gorm:"column:activated_at"`
|
|
ExpiresAt *time.Time `gorm:"column:expires_at"`
|
|
}
|
|
|
|
func buildDeviceExportHeaders(cardGroups int) []string {
|
|
if cardGroups < deviceExportMinCardGroups {
|
|
cardGroups = deviceExportMinCardGroups
|
|
}
|
|
|
|
headers := []string{"设备号", "IMEI", "设备名称", "店铺名称", "套餐系列", "设备型号"}
|
|
for i := 1; i <= cardGroups; i++ {
|
|
headers = append(headers,
|
|
fmt.Sprintf("ICCID%d", i),
|
|
fmt.Sprintf("接入号%d", i),
|
|
fmt.Sprintf("ICCID%d对应的运营商", i),
|
|
fmt.Sprintf("ICCID%d使用的流量", i),
|
|
fmt.Sprintf("卡状态%d", i),
|
|
)
|
|
}
|
|
headers = append(headers,
|
|
"设备合计",
|
|
"套餐的生效时间",
|
|
"套餐的到期时间",
|
|
"当前套餐",
|
|
"钱包余额",
|
|
)
|
|
return headers
|
|
}
|
|
|
|
func buildDeviceExportRow(item deviceExportRow, cards []deviceExportCardRow, pkg deviceExportPackageRow, cardGroups int) []string {
|
|
if cardGroups < deviceExportMinCardGroups {
|
|
cardGroups = deviceExportMinCardGroups
|
|
}
|
|
|
|
row := []string{
|
|
item.VirtualNo,
|
|
item.IMEI,
|
|
item.DeviceName,
|
|
item.ShopName,
|
|
item.SeriesName,
|
|
item.DeviceModel,
|
|
}
|
|
|
|
totalUsage := 0.0
|
|
for i := 0; i < cardGroups; i++ {
|
|
if i >= len(cards) {
|
|
row = append(row, "", "", "", "", "")
|
|
continue
|
|
}
|
|
card := cards[i]
|
|
totalUsage += card.CurrentMonthUsageMB
|
|
row = append(row,
|
|
card.ICCID,
|
|
card.MSISDN,
|
|
card.CarrierName,
|
|
formatMB(card.CurrentMonthUsageMB),
|
|
constants.GetNetworkStatusName(card.NetworkStatus),
|
|
)
|
|
}
|
|
// 如果表头列数被历史任务固定得比当前绑定卡少，仍保证合计覆盖当前查到的全部卡。
|
|
for i := cardGroups; i < len(cards); i++ {
|
|
totalUsage += cards[i].CurrentMonthUsageMB
|
|
}
|
|
|
|
row = append(row,
|
|
formatMB(totalUsage),
|
|
formatOptionalTime(pkg.ActivatedAt),
|
|
formatOptionalTime(pkg.ExpiresAt),
|
|
pkg.PackageName,
|
|
formatMoneyYuan(item.WalletBalance),
|
|
)
|
|
return row
|
|
}
|
|
|
|
func cardGroupCountFromHeaders(headers []string) int {
|
|
if len(headers) < deviceExportBaseHeaderCount+deviceExportTailHeaderCount {
|
|
return 0
|
|
}
|
|
cardColumnCount := len(headers) - deviceExportBaseHeaderCount - deviceExportTailHeaderCount
|
|
if cardColumnCount <= 0 || cardColumnCount%deviceExportCardGroupSize != 0 {
|
|
return 0
|
|
}
|
|
return cardColumnCount / deviceExportCardGroupSize
|
|
}
|
|
|
|
func formatMB(value float64) string {
|
|
return strconv.FormatFloat(value, 'f', 2, 64)
|
|
}
|
|
|
|
func formatMoneyYuan(cents int64) string {
|
|
sign := ""
|
|
if cents < 0 {
|
|
sign = "-"
|
|
cents = -cents
|
|
}
|
|
return fmt.Sprintf("%s%d.%02d", sign, cents/100, cents%100)
|
|
}
|