smart_streetlights/common/models/lampdata.go

package models

import "time"

type LightingData struct {
	ID        string    `gorm:"type:varchar(32);primary_key"` //设备编码
	Date      time.Time `gorm:"type:date;primary_key"`        //日期
	Duration  int       `gorm:"type:int"`                     //时长
	Tenant    string    `gorm:"type:varchar(8)"`              //租户ID
	CreatedAt time.Time
}

func (LightingData) TableName() string {
	return "t_lamp_lighting_day_data"
}

type LightingRateData struct {
	Tenant    string    `gorm:"type:varchar(8);primary_key"` //租户ID
	Date      time.Time `gorm:"type:date;primary_key"`       //日期
	Total     int       `gorm:"type:int"`                    //总灯数
	Number    int       `gorm:"type:int"`                    //亮灯数
	Rate      float32   `gorm:"type:float(10,2)"`            //亮灯率
	CreatedAt time.Time //统计时间
}

func (LightingRateData) TableName() string {
	return "t_lamp_lighting_rate_day_data"
}

func GetDayLightingRateData(tenant, start, end string) ([]LightingRateData, error) {
	var arr []LightingRateData
	err := G_db.Model(LightingRateData{}).Where("tenant = ? and date between ? and ?", tenant, start, end).Find(&arr).Error
	return arr, err
}

type LightingRate struct {
	Time string  //周期
	Rate float32 //亮灯率
}

func GetMonthLightingRateData(tenant, start, end string) ([]LightingRate, error) {
	var arr []LightingRate
	sql := `SELECT DATE_FORMAT(a.date,'%Y-%m') AS time,ROUND(SUM(number)*100/SUM(total),1) AS rate 
           FROM t_lamp_lighting_rate_day_data a
           WHERE a.tenant = ? AND DATE_FORMAT(a.date,'%Y-%m') BETWEEN ? AND ?
	       GROUP BY DATE_FORMAT(a.date,'%Y-%m') 
           ORDER BY DATE_FORMAT(a.date,'%Y-%m')`
	err := G_db.Raw(sql, tenant, start, end).Scan(&arr).Error
	return arr, err
}

func GetQuarterLightingRateData(tenant, start, end string, quarter int) ([]LightingRate, error) {
	var err error
	var arr []LightingRate
	if quarter == 0 {
		sql := `SELECT QUARTER(a.date) AS time,ROUND(SUM(a.number)*100/SUM(a.total),1) AS rate
	          FROM t_lamp_lighting_rate_day_data a
	          WHERE a.tenant = ? AND DATE_FORMAT(a.date,'%Y') BETWEEN ? AND ?
	          GROUP BY QUARTER(a.date)
              ORDER BY QUARTER(a.date)`
		err = G_db.Raw(sql, tenant, start, end).Scan(&arr).Error
	} else {
		sql := `SELECT QUARTER(a.date) AS time,ROUND(SUM(a.number)*100/SUM(a.total),1) AS rate
	          FROM t_lamp_lighting_rate_day_data a
	          WHERE a.tenant = ? AND QUARTER(a.date) = ? AND DATE_FORMAT(a.date,'%Y') BETWEEN ? AND ? 
	          GROUP BY QUARTER(a.date)
              ORDER BY QUARTER(a.date)`
		err = G_db.Raw(sql, tenant, quarter, start, end).Scan(&arr).Error
	}
	return arr, err
}

func GetYearLightingRateData(tenant, start, end string) ([]LightingRate, error) {
	var err error
	var arr []LightingRate
	sql := `SELECT DATE_FORMAT(a.date,'%Y') AS time,ROUND(SUM(a.number)*100/SUM(a.total),1) AS rate
	          FROM t_lamp_lighting_rate_day_data a
	          WHERE a.tenant = ? AND DATE_FORMAT(a.date,'%Y') BETWEEN ? AND ?
	          GROUP BY DATE_FORMAT(a.date,'%Y')
              ORDER BY DATE_FORMAT(a.date,'%Y')`
	err = G_db.Raw(sql, tenant, start, end).Scan(&arr).Error
	return arr, err
}

type LightingRateMiniteData struct {
	Tenant    string    `gorm:"type:varchar(8);primary_key"` //租户ID
	Time      time.Time `gorm:"type:datetime;primary_key"`   //时间
	Total     int       `gorm:"type:int"`                    //总灯数
	Number    int       `gorm:"type:int"`                    //亮灯数
	Rate      float32   `gorm:"type:float(10,2)"`            //亮灯率
	CreatedAt time.Time //统计时间
}

func (LightingRateMiniteData) TableName() string {
	return "t_lamp_lighting_rate_minite_data"
}

//获取某时间段内的15分钟亮灯率数据
func GetMiniteLightingRateData(tenant, start, end string) ([]LightingRateMiniteData, error) {
	var err error
	var arr []LightingRateMiniteData
	if end != "" {
		G_db.Model(LightingRateMiniteData{}).Where("tenant = ? and time BETWEEN ? AND ? ",
			tenant, start, end)
	} else {
		G_db.Model(LightingRateMiniteData{}).Where("tenant = ? and time >= ? ",
			tenant, start)
	}
	return arr, err
}

type LastestLightingRate struct {
	Tenant string  //租户ID
	Total  int     //总灯数
	Number int     //亮灯数
	Rate   float32 //亮灯率
}

//获取实时亮灯率数据
func GetLastestLightingRate(tenant string) (LastestLightingRate, error) {
	var err error
	var ret LastestLightingRate
	sql := `SELECT a.tenant,COUNT(a.id) AS total,SUM(IF(b.d_id IS NULL,0,1)) AS number,
            ROUND(SUM(IF(b.d_id IS NULL,0,1))*100/COUNT(a.id),2) AS rate
            FROM t_device_lampcontroller a
            LEFT JOIN (SELECT DISTINCT d_id FROM device_lamp_events WHERE t_end IS NULL) b 
            ON a.id = b.d_id WHERE a.state = 1 AND a.tenant = ?`
	err = G_db.Raw(sql, tenant).Find(&ret).Error
	return ret, err
}