MiFaZhan
125 lines
3.5 KiB
Go
125 lines
3.5 KiB
Go
package cardflow
|
|
|
|
import (
|
|
"fmt"
|
|
"strings"
|
|
|
|
"git.5iot.com/iotlcm-public/iotlcm-admin-svr/internal/consts"
|
|
"git.5iot.com/iotlcm-public/iotlcm-admin-svr/internal/dao"
|
|
)
|
|
|
|
// MainPackageFlowExpr returns the card's base package flow. When the current
|
|
// package table is already joined, pass its table alias to avoid another subquery.
|
|
func MainPackageFlowExpr(cardAlias, currentPackageAlias string) string {
|
|
cardColumns := dao.TSimCardInfo.Columns()
|
|
packageColumns := dao.TSimFlowPackageInfo.Columns()
|
|
cardTotalFlow := column(cardAlias, cardColumns.PackageTotalFlowMb)
|
|
|
|
currentPackageAlias = strings.TrimSpace(currentPackageAlias)
|
|
if currentPackageAlias != "" {
|
|
return fmt.Sprintf(
|
|
"COALESCE(NULLIF(%s, 0), %s, 0)",
|
|
column(currentPackageAlias, packageColumns.FlowMb),
|
|
cardTotalFlow,
|
|
)
|
|
}
|
|
|
|
packageTable := dao.TSimFlowPackageInfo.Table()
|
|
packageAlias := "card_base_pkg"
|
|
return fmt.Sprintf(
|
|
"COALESCE(NULLIF((SELECT %s.%s FROM %s AS %s WHERE %s.%s = %s AND %s.%s = %s LIMIT 1), 0), %s, 0)",
|
|
packageAlias,
|
|
packageColumns.FlowMb,
|
|
packageTable,
|
|
packageAlias,
|
|
packageAlias,
|
|
packageColumns.PackageCode,
|
|
column(cardAlias, cardColumns.CurrentPackageCode),
|
|
packageAlias,
|
|
packageColumns.TenantId,
|
|
column(cardAlias, cardColumns.TenantId),
|
|
cardTotalFlow,
|
|
)
|
|
}
|
|
|
|
// ActiveCardTopupFlowExpr returns active, unexpired single-card add-package flow.
|
|
func ActiveCardTopupFlowExpr(cardAlias string) string {
|
|
cardColumns := dao.TSimCardInfo.Columns()
|
|
bindTable := dao.TSimCardBindPkg.Table()
|
|
bindColumns := dao.TSimCardBindPkg.Columns()
|
|
packageTable := dao.TSimFlowPackageInfo.Table()
|
|
packageColumns := dao.TSimFlowPackageInfo.Columns()
|
|
bindAlias := "card_topup_bind"
|
|
packageAlias := "card_topup_pkg"
|
|
|
|
return fmt.Sprintf(
|
|
"IFNULL((SELECT SUM(IFNULL(%s.%s, 0)) FROM %s AS %s LEFT JOIN %s AS %s ON %s.%s = %s.%s AND %s.%s = %s.%s WHERE %s.%s = %s AND %s.%s = %s AND %s.%s = %d AND %s.%s = %d AND (%s.%s IS NULL OR %s.%s <= NOW()) AND (%s.%s IS NULL OR %s.%s > NOW())), 0)",
|
|
packageAlias,
|
|
packageColumns.FlowMb,
|
|
bindTable,
|
|
bindAlias,
|
|
packageTable,
|
|
packageAlias,
|
|
bindAlias,
|
|
bindColumns.PackageCode,
|
|
packageAlias,
|
|
packageColumns.PackageCode,
|
|
bindAlias,
|
|
bindColumns.TenantId,
|
|
packageAlias,
|
|
packageColumns.TenantId,
|
|
bindAlias,
|
|
bindColumns.Iccid,
|
|
column(cardAlias, cardColumns.Iccid),
|
|
bindAlias,
|
|
bindColumns.TenantId,
|
|
column(cardAlias, cardColumns.TenantId),
|
|
bindAlias,
|
|
bindColumns.PackageType,
|
|
consts.PackageTypeCardAddPackage,
|
|
bindAlias,
|
|
bindColumns.EffectiveStatus,
|
|
consts.EffectiveStatusEffective,
|
|
bindAlias,
|
|
bindColumns.EffectiveTime,
|
|
bindAlias,
|
|
bindColumns.EffectiveTime,
|
|
bindAlias,
|
|
bindColumns.ExpectedExpireTime,
|
|
bindAlias,
|
|
bindColumns.ExpectedExpireTime,
|
|
)
|
|
}
|
|
|
|
func TotalFlowExpr(cardAlias, currentPackageAlias string) string {
|
|
return fmt.Sprintf(
|
|
"(%s + %s)",
|
|
MainPackageFlowExpr(cardAlias, currentPackageAlias),
|
|
ActiveCardTopupFlowExpr(cardAlias),
|
|
)
|
|
}
|
|
|
|
func RemainFlowExpr(cardAlias, currentPackageAlias string) string {
|
|
return fmt.Sprintf(
|
|
"GREATEST(%s - IFNULL(%s, 0), 0)",
|
|
TotalFlowExpr(cardAlias, currentPackageAlias),
|
|
column(cardAlias, dao.TSimCardInfo.Columns().UsedFlowMb),
|
|
)
|
|
}
|
|
|
|
func ExhaustedCondition(cardAlias, currentPackageAlias string) string {
|
|
return fmt.Sprintf(
|
|
"%s <= IFNULL(%s, 0)",
|
|
TotalFlowExpr(cardAlias, currentPackageAlias),
|
|
column(cardAlias, dao.TSimCardInfo.Columns().UsedFlowMb),
|
|
)
|
|
}
|
|
|
|
func column(alias, name string) string {
|
|
alias = strings.TrimSpace(alias)
|
|
if alias == "" {
|
|
return name
|
|
}
|
|
return alias + "." + name
|
|
}
|