lc_veterans/app/data/dao/environmentDao.go

package dao

import "time"

// EnvironmentData 环境监测器数据
type EnvironmentData struct {
	ID            int       `gorm:"primary_key" json:"id"`                 //编号
	DeviceId      int       `gorm:"type:int" json:"deviceId"`              //设备名称
	Pm25          float32   `gorm:"type:float(10, 2)" json:"pm25"`         //PM2.5
	Pm10          float32   `gorm:"type:float(10, 2)" json:"pm10"`         //PM10
	WindSpeed     float32   `gorm:"type:float(10, 2)" json:"windSpeed"`    //风速
	WindDirection string    `gorm:"type:varchar(10)" json:"windDirection"` //风向
	Hpa           float32   `gorm:"type:float(10, 2)" json:"hpa"`          //气压
	Rainfall      float32   `gorm:"type:float(10, 2)" json:"rainfall"`     //降雨量
	Temperature   float32   `gorm:"type:float(10, 2)" json:"temperature"`  //温度
	Humidity      float32   `gorm:"type:float(10, 2)" json:"humidity"`     //湿度
	Noise         float32   `gorm:"type:float(10, 2)" json:"noise"`        //噪音
	Ultraviolet   float32   `gorm:"type:float(10, 2)" json:"ultraviolet"`  //紫外线
	Illuminance   float32   `gorm:"type:float(10, 2)" json:"illuminance"`  //光照强度
	Air           float32   `gorm:"type:float(10, 2)" json:"air"`          //空气质量指数
	IsDay         int       `gorm:"type:int" json:"isDay"`                 //是否用于天数据 7点的采集数据用于天数据，0否 1是
	PostTime      time.Time `gorm:"type:datetime" json:"postTime"`         //采集时间
	CreateTime    time.Time `gorm:"type:datetime" json:"createTime"`       //新增时间
}

func (EnvironmentData) TableName() string {
	return "data_environment"
}

func (c *EnvironmentData) Save() error {
	return Db.Debug().Model(&c).Create(&c).Error
}

func (c *EnvironmentData) BatchSave(data []EnvironmentData) error {
	return Db.Debug().Model(&c).Create(&data).Error
}

func (c *EnvironmentData) Get() error {
	return Db.Debug().Model(&c).Where("device_id = ?", c.DeviceId).Order("create_time desc").First(&c).Error
}

func (c *EnvironmentData) GetByTime(start, end time.Time) ([]EnvironmentData, error) {
	var data []EnvironmentData
	err := Db.Debug().Model(&c).Where("device_id = ? and is_day = ? and create_time >= ? and create_time <= ?",
		c.DeviceId, c.IsDay, start, end).Order("create_time").Find(&data).Error
	return data, err
}

// EnvironmentDataDay 环境监测器天数据统计
type EnvironmentDataSummary struct {
	DeviceId int `gorm:"type:int" json:"deviceId"` //设备名称
	//1=空气质量 2=PM2.5 3=pm10 4=温度 5=湿度 6=风速 7=气压 8=噪音 9=光照强度 10=风向 11=降雨量 12=紫外线
	DataType   int       `gorm:"type:int" json:"dataType"`
	ValAvg     float32   `gorm:"type:float(10, 2)" json:"valAvg"`       //天均值
	ValMax     float32   `gorm:"type:float(10, 2)" json:"valMax"`       //天最大
	ValMin     float32   `gorm:"type:float(10, 2)" json:"valMin"`       //天最小
	CreateTime time.Time `gorm:"type:datetime;index" json:"createTime"` //新增时间
}

func (EnvironmentDataSummary) TableName() string {
	return "data_environment_summary"
}

func (c *EnvironmentDataSummary) DaySummary(start, end time.Time) error {
	err := Db.Debug().Model(&c).Exec(`INSERT INTO data_environment_summary
    select device_id,
       data_type,
       round(avg(value), 2)    val_avg,
       max(value)           val_max,
       min(value)           val_min,
       adddate(current_date, -1) create_time
from (
         SELECT device_id, 2 AS data_type, pm25 AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 3 AS data_type, pm10 AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 4 AS data_type, wind_speed AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 5 AS data_type, wind_direction AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 6 AS data_type, hpa AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 7 AS data_type, rainfall AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 8 AS data_type, temperature AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 9 AS data_type, humidity AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 10 AS data_type, noise AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 11 AS data_type, ultraviolet AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 12 AS data_type, illuminance AS value, create_time
         FROM data_environment
         UNION ALL
         SELECT device_id, 1 AS data_type, air AS value, create_time
         FROM data_environment
     ) a
where create_time > ? and create_time <= ?
group by device_id, data_type`, start, end).Error
	return err
}

func (c *EnvironmentDataSummary) Get() error {
	return Db.Debug().Model(&c).Where("device_id = ?", c.DeviceId).Order("create_time desc").First(&c).Error
}