smart_streetlights/common/models/devicedata.go

package models

import (
	"bytes"
	"fmt"
	"lc/common/util"
	"time"
)

type DeviceHourData struct {
	ID        string    `gorm:"type:varchar(32);primary_key"`
	Sid       uint16    `gorm:"type:smallint;primary_key"`
	Val       float32   `gorm:"type:float(10,2)"`
	Time      time.Time `gorm:"type:datetime;primary_key"`
	CreatedAt time.Time
}

func (DeviceHourData) TableName() string {
	return "t_device_hour_data"
}

type DeviceDayData struct {
	ID        string    `gorm:"type:varchar(32);primary_key"`
	Sid       uint16    `gorm:"type:smallint;primary_key"`
	ValAvg    float32   `gorm:"type:float(10,2)"`
	ValMin    float32   `gorm:"type:float(10,2)"`
	ValMax    float32   `gorm:"type:float(10,2)"`
	Time      time.Time `gorm:"type:datetime;primary_key"`
	CreatedAt time.Time
}

func (DeviceDayData) TableName() string {
	return "t_device_day_data"
}

type DeviceEnergyDayData struct {
	ID         string    `gorm:"type:varchar(32);primary_key"` //设备编码
	Date       time.Time `gorm:"type:date;primary_key"`        //日期
	Beginning  float32   `gorm:"type:float(10,2)"`             //上次抄表数
	Ending     float32   `gorm:"type:float(10,2)"`             //本次抄表数
	Difference float32   `gorm:"type:float(10,2)"`             //增量值，不允许为负数
	CreatedAt  time.Time
}

func (DeviceEnergyDayData) TableName() string {
	return "t_device_energy_day_data"
}

type DeviceEnergyMonthData struct {
	ID         string    `gorm:"type:varchar(32);primary_key"` //设备编码
	Date       time.Time `gorm:"type:date;primary_key"`        //日期
	Beginning  float32   `gorm:"type:float(10,2)"`             //上次抄表数
	Ending     float32   `gorm:"type:float(10,2)"`             //本次抄表数
	Difference float32   `gorm:"type:float(10,2)"`             //增量值，不允许为负数
	CreatedAt  time.Time
}

func (DeviceEnergyMonthData) TableName() string {
	return "t_device_energy_month_data"
}

func MultiInsertDeviceHourData(datas []DeviceHourData) error {
	if len(datas) == 0 {
		return nil
	}
	var buffer bytes.Buffer
	sql := "insert into t_device_hour_data (id,sid,val,time,created_at) values"
	if _, err := buffer.WriteString(sql); err != nil {
		return err
	}
	for i, e := range datas {
		if i == len(datas)-1 {
			buffer.WriteString(fmt.Sprintf("('%s',%d,%f,'%s','%s');", e.ID, e.Sid, e.Val, e.Time.Format("2006-01-02 15:04:05"), e.CreatedAt.Format("2006-01-02 15:04:05")))
		} else {
			buffer.WriteString(fmt.Sprintf("('%s',%d,%f,'%s','%s'),", e.ID, e.Sid, e.Val, e.Time.Format("2006-01-02 15:04:05"), e.CreatedAt.Format("2006-01-02 15:04:05")))
		}
	}
	return G_db.Exec(buffer.String()).Error
}

func GetDeviceHourData(Code, Start, End string, Sids []uint16) ([]DeviceHourData, error) {
	var array []DeviceHourData
	err := G_db.Model(&DeviceHourData{}).Where("id = ? and sid in (?) and time >= ? and time <= ?",
		Code, Sids, Start, End).Order("sid asc,time asc").Find(&array).Error
	return array, err
}
func GetBDeviceHourData(Codes []string, Start, End string) ([]DeviceHourData, error) {
	var array []DeviceHourData
	err := G_db.Model(&DeviceHourData{}).Where("id in (?) and  time >= ? and time <= ?",
		Codes, Start, End).Order("sid asc,time asc").Find(&array).Error
	return array, err
}

func GetDeviceDayData(Code, Start, End string, Sids []uint16) ([]DeviceDayData, error) {
	var array []DeviceDayData
	err := G_db.Model(&DeviceDayData{}).Where("id = ? and sid in (?) and time >= ? and time <= ?",
		Code, Sids, Start, End).Order("sid asc,time asc").Find(&array).Error
	return array, err
}

func GetBDeviceDayData(Codes []string, Start, End string) ([]DeviceDayData, error) {
	var array []DeviceDayData
	err := G_db.Model(&DeviceDayData{}).Where("id  in (?)  and time >= ? and time <= ?",
		Codes, Start, End).Order("id asc,time asc").Find(&array).Error
	return array, err
}

func GetDeviceEnergyMonthData(Codes []string, Start, End string) ([]DeviceEnergyMonthData, error) {
	var array []DeviceEnergyMonthData
	err := G_db.Model(&DeviceEnergyMonthData{}).Where("id IN (?) and date >= ? and date <= ?",
		Codes, Start, End).Order("id asc,date asc").Find(&array).Error
	return array, err
}

func GetDeviceEnergyDayData(Codes []string, Start, End string) ([]DeviceEnergyDayData, error) {
	var array []DeviceEnergyDayData
	err := G_db.Model(&DeviceEnergyDayData{}).Where("id IN (?) and date >= ? and date <= ?",
		Codes, Start, End).Order("id asc,date asc").Find(&array).Error
	return array, err
}

func GetRadarDataList(Codes []string, Start, End string, model uint8) ([]RadarData, error) {
	var array []RadarData
	//0点目标 1车目标 所有目标2
	var err error
	if model == 2 {
		err = G_db.Model(&RadarData{}).Where("d_id IN (?) and time >= ? and time <= ?",
			Codes, Start, End).Order("id asc,time asc").Find(&array).Error
	} else {
		err = G_db.Model(&RadarData{}).Where("d_id IN (?) and time >= ? and time <= ? and type = ?",
			Codes, Start, End, model).Order("id asc,time asc").Find(&array).Error
	}
	return array, err
}
func GetRadarSummaryHourData(Codes []string, Start, End string, Model uint8) ([]RadarHourData, error) {
	var array []RadarHourData
	//0点目标 1车目标 所有目标2
	var err error
	if Model == 2 {
		err = G_db.Model(&RadarHourData{}).Where("d_id IN (?) and time >= ? and time <= ?",
			Codes, Start, End).Order("time asc").Find(&array).Error
	} else {
		err = G_db.Model(&RadarHourData{}).Where("d_id IN (?) and time >= ? and time <= ? and type = ?",
			Codes, Start, End, Model).Order("time asc").Find(&array).Error
	}
	return array, err
}
func GetRadarSummaryDayData(Codes []string, Start, End string, Model uint8) ([]RadarDayData, error) {
	var array []RadarDayData
	//0点目标 1车目标 所有目标2
	var err error
	if Model == 2 {
		err = G_db.Model(&RadarDayData{}).Where("d_id IN (?) and date >= ? and date <= ?",
			Codes, Start, End).Order("date asc").Find(&array).Error
	} else {
		err = G_db.Model(&RadarDayData{}).Where("d_id IN (?) and date >= ? and date <= ? and type = ?",
			Codes, Start, End, Model).Order("date asc").Find(&array).Error
	}
	return array, err
}
func StatDay(t time.Time) error {
	return G_db.Exec("CALL CalcDayData(?)", t).Error
}

func StatDayEnergy(currhour time.Time, flag int) error {
	return G_db.Exec("CALL CalcEnergyDay(?,?)", currhour, flag).Error
}

func StatMonthEnergy(currhour time.Time, flag int) error {
	return G_db.Exec("CALL CalcEnergyMonth(?,?)", currhour, flag).Error
}

func StatHourRadarData(hour time.Time) error {
	end := util.New(hour).BeginningOfHour()
	start := end.Add(-1 * time.Hour)
	err := G_db.Where("time = ?", start).Delete(&RadarHourData{}).Error
	err = G_db.Model(&RadarHourData{}).Exec(` INSERT INTO t_radar_hour_data
    select id,
       g_id,
       type,
       count(*)                                    total,
       sum(if(Speed > suggest, 1, 0)) as           over_speed_number,
       sum(if(Speed < 0, 1, 0))                    speed_min_number,
       sum(if(Speed > 0 and speed <= 20, 1, 0))    speed20_number,
       sum(if(Speed > 20 and speed <= 40, 1, 0))   speed40_number,
       sum(if(Speed > 40 and speed <= 60, 1, 0))   speed60_number,
       sum(if(Speed > 60 and speed <= 80, 1, 0))   speed80_number,
       sum(if(Speed > 80 and speed <= 100, 1, 0))  speed100_number,
       sum(if(Speed > 100 and speed <= 120, 1, 0)) speed120_number,
       sum(if(Speed > 120, 1, 0))                  speed_max_number,
       ?                              as           time
from (
         select trd.g_id,
                trd.time,
                trd.speed,
                trd.type,
                if(tdr.id is null, trd.d_id, tdr.id)         id,
                if(tdr.id is null, 10000, tdr.suggest_speed) suggest
         from t_radar_data trd
                  left join t_device_radar tdr
                            on trd.g_id = tdr.g_id and trd.ip = tdr.ip
         where time > ?
           and time <= ?) a
group by id,
         g_id,
         type`, start, start, end).Error
	return err
}

func StatDayRadarData(day time.Time) error {
	end := util.New(day).BeginningOfDay()
	start := end.Add(-24 * time.Hour)
	err := G_db.Where("date = ?", start).Delete(&RadarDayData{}).Error
	err = G_db.Model(&RadarDayData{}).Exec(`INSERT INTO t_radar_day_data
    select id,
       g_id,
       type,
       count(*)                                    total,
       sum(if(Speed > suggest, 1, 0)) as           over_speed_number,
       sum(if(Speed < 0, 1, 0))                    speed_min_number,
       sum(if(Speed > 0 and speed <= 20, 1, 0))    speed20_number,
       sum(if(Speed > 20 and speed <= 40, 1, 0))   speed40_number,
       sum(if(Speed > 40 and speed <= 60, 1, 0))   speed60_number,
       sum(if(Speed > 60 and speed <= 80, 1, 0))   speed80_number,
       sum(if(Speed > 80 and speed <= 100, 1, 0))  speed100_number,
       sum(if(Speed > 100 and speed <= 120, 1, 0)) speed120_number,
       sum(if(Speed > 120, 1, 0))                  speed_max_number,
       ?                             as            date
from (
         select trd.g_id,
                trd.time,
                trd.speed,
                trd.type,
                if(tdr.id is null, trd.d_id, tdr.id)         id,
                if(tdr.id is null, 10000, tdr.suggest_speed) suggest
         from t_radar_data trd
                  left join t_device_radar tdr
                            on trd.g_id = tdr.g_id and trd.ip = tdr.ip
         where time > ?
           and time <= ?) a
group by id,
         g_id,
         type`, start, start, end).Error
	return err
}