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:"temp"`         //温度
	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"`          //空气质量指数
	AirName       string    `gorm:"varchar(100)" json:"airName"`           //空气质量指数
	IsDay         int       `gorm:"type:int" json:"isDay"`                 //是否用于天数据 7点的采集数据用于天数据，0否 1是
	TenantId      string    `gorm:"type:varchar(12)" json:"tenantId"`
	PostTime      time.Time `gorm:"type:datetime" json:"postTime"`      //采集时间
	CreateTime    time.Time `gorm:"type:datetime" json:"createTime"`    //新增时间
	CreateDate    string    `gorm:"type:varchar(50)" json:"createDate"` //显示时间
}

type EnvironmentScopeData struct {
	Time      string `json:"time"`
	Val       string `json:"val"`
	Maxval    string `json:"maxval"`
	Minval    string `json:"minval"`
	ModelType int    `json:"modelType"`
}

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("post_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
}

func (c *EnvironmentData) GetEnvironmentScope(queryType int, s string) []EnvironmentScopeData {
	var environmentScope []EnvironmentScopeData
	var field string
	switch queryType {
	case 1:
		field = "air"
	case 2:
		field = "pm25"
	case 3:
		field = "pm10"
	case 4:
		field = "temperature"
	case 5:
		field = "humidity"
	case 6:
		field = "wind_speed"
	case 7:
		field = "hpa"
	case 8:
		field = "noise"
	}
	dateStr := "%Y-%m-%d %H:00:00"
	postTime := time.Now().Format("2006-01-02")
	if s == "history" {
		postTime = time.Now().AddDate(0, 0, -1).Format("2006-01-02")
	}
	if c.IsDay != 1 {
		dateStr = "%Y-%m-%d 00:00:00"
		postTime = time.Now().Format("2006-01-02")
		if s == "history" {
			postTime = time.Now().AddDate(0, -1, 0).Format("2006-01-02")
		}
	}
	sql := `
		SELECT
			DATE_FORMAT( ( post_time ), '` + dateStr + `' ) AS time,
			ROUND(min(` + field + `), 2) AS minval,
			ROUND(max(` + field + `),2) as maxval,
			ROUND(avg(` + field + `),2)as val
		FROM
			data_environment 
		WHERE
			device_id = ? and
			post_time LIKE "` + postTime + `%"
			GROUP BY DATE_FORMAT( ( post_time ), '` + dateStr + `' )
			`
	Db.Debug().Raw(sql, c.DeviceId).Find(&environmentScope)
	return environmentScope
}

// 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(if(value='',0,value))           val_max,
       min(if(value='',0,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 data_type!=5 and 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
}