goiot-cron/services/box.go

package services

import (
	"github.com/jinzhu/gorm"
	"goiot-cronapi/models"
)

// GetBoxIdList 获取所有在线的盒子列表
func GetBoxIdList(db *gorm.DB) []string {
	var boxList []models.BoxBase
	sql := "SELECT box_id FROM btk_box WHERE box_is_delete = 0 AND box_state = 1"
	db.Raw(sql).Find(&boxList)
	var boxIds = make([]string, 0)
	for _, box := range boxList {
		if len(box.BoxId) > 0 {
			boxIds = append(boxIds, box.BoxId)
		}
	}
	return boxIds
}

// GetAreaList 获取所有在线的盒子对应的地区
func GetAreaList(db *gorm.DB) models.AreaList {
	var list models.AreaList
	sql := `SELECT p.province, p.city, p.area, p.district, p.city_id FROM btk_box b
			JOIN btk_project p ON p.project_auto_id = b.project_auto_id 
			WHERE b.box_is_delete = 0 AND b.box_state = 1 AND p.city_id > 0
			GROUP BY p.city_id`
	db.Raw(sql).Find(&list)
	return list
}

// 获取每个盒子对应的运行总时长
func GetBoxRunTime(db *gorm.DB) map[string]int64 {
	var (
		list    []models.BoxTime
		timeMap = make(map[string]int64)
	)
	sql := "SELECT box_id, SUM(count) as time FROM btk_run_time GROUP BY box_id"
	db.Raw(sql).Find(&list)
	for _, value := range list {
		timeMap[value.BoxId] = value.Time
	}
	return timeMap
}