smart_streetlights/common/models/itsstatic.go

package models

import (
	"bytes"
	"fmt"
	"time"
)

type ItsVehicleSpeed struct {
	ID    int64     `gorm:"primary_key"`      //主键
	DID   string    `gorm:"type:varchar(32)"` //设备编码
	Time  time.Time `gorm:"type:datetime"`    //日期
	Plate string    `gorm:"type:varchar(16)"` //车牌
	Vtype int       `gorm:"type:smallint"`    //车辆类型
	Speed int       `gorm:"type:int"`         //车辆速度
}

func (ItsVehicleSpeed) TableName() string {
	return "t_its_vehicle_speed"
}

func MultiItsVehicleSpeed(datas []ItsVehicleSpeed) error {
	if len(datas) == 0 {
		return nil
	}
	var buffer bytes.Buffer
	sql := "insert into t_its_vehicle_speed (d_id,time,plate,vtype,speed) 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','%s','%s',%d,%d);", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.Plate, e.Vtype, e.Speed))
		} else {
			buffer.WriteString(fmt.Sprintf("('%s','%s','%s',%d,%d),", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.Plate, e.Vtype, e.Speed))
		}
	}
	return G_db.Exec(buffer.String()).Error
}

type ItsVehicleDirection struct {
	ID        int64     `gorm:"primary_key"`      //主键
	DID       string    `gorm:"type:varchar(32)"` //设备编码
	Time      time.Time `gorm:"type:datetime"`    //日期
	Direction int       `gorm:"type:tinyint"`     //方向
	Total     int       `gorm:"type:int"`         //总数
}

func (ItsVehicleDirection) TableName() string {
	return "t_its_vehicle_direction"
}

func MultiItsVehicleDirection(datas []ItsVehicleDirection) error {
	if len(datas) == 0 {
		return nil
	}
	var buffer bytes.Buffer
	sql := "insert into t_its_vehicle_direction (d_id,time,direction,total) 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','%s',%d,%d);", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.Direction, e.Total))
		} else {
			buffer.WriteString(fmt.Sprintf("('%s','%s',%d,%d),", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.Direction, e.Total))
		}
	}
	return G_db.Exec(buffer.String()).Error
}

type ItsVehicleProvince struct {
	ID       int64     `gorm:"primary_key"`      //主键
	DID      string    `gorm:"type:varchar(32)"` //设备编码
	Time     time.Time `gorm:"type:datetime"`    //日期
	Province string    `gorm:"type:varchar(12)"` //省份
	Total    int       `gorm:"type:int"`         //车辆数
}

func (ItsVehicleProvince) TableName() string {
	return "t_its_vehicle_province"
}
func MultiItsVehicleProvince(datas []ItsVehicleProvince) error {
	if len(datas) == 0 {
		return nil
	}
	var buffer bytes.Buffer
	sql := "insert into t_its_vehicle_province (d_id,time,province,total) 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','%s','%s',%d);", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.Province, e.Total))
		} else {
			buffer.WriteString(fmt.Sprintf("('%s','%s','%s',%d),", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.Province, e.Total))
		}
	}
	return G_db.Exec(buffer.String()).Error
}

type VehicleProvinceTag struct {
	DID      string `json:"code"`     //设备ID
	Time     string `json:"time"`     //数据时间
	Province string `json:"province"` //省,市
	Total    int    `json:"total"`    //车辆数
	Flag     int    `json:"flag"`     //类型标识，0:日数据;1:月数据
}

func GetVehicleProvince(Codes []string, start, end string, flag int) ([]VehicleProvinceTag, error) {
	var (
		err error
		sql string
		arr []VehicleProvinceTag
	)
	if flag == 0 {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d') as time,province,SUM(total) as total, 0 as flag FROM t_its_vehicle_province
          WHERE time >= ? AND time <= ? AND d_id IN (?)
          GROUP BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),province
          ORDER BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),province`
	} else {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m') as time,province,SUM(total) as total, 1 as flag FROM t_its_vehicle_province
          WHERE time >= ? AND time <= ? AND d_id IN (?)
          GROUP BY d_id,DATE_FORMAT(time,'%Y-%m'),province
          ORDER BY d_id,DATE_FORMAT(time,'%Y-%m'),province`
	}
	err = G_db.Raw(sql, start, end, Codes).Scan(&arr).Error
	return arr, err
}

type ItsVehicleProvinceCity struct {
	ID           int64     `gorm:"primary_key"`      //主键
	DID          string    `gorm:"type:varchar(32)"` //设备编码
	Time         time.Time `gorm:"type:datetime"`    //日期
	ProvinceCity string    `gorm:"type:varchar(12)"` //城市
	Total        int       `gorm:"type:int"`         //车辆数
}

func (ItsVehicleProvinceCity) TableName() string {
	return "t_its_vehicle_province_city"
}

func MultiItsVehicleProvinceCity(datas []ItsVehicleProvinceCity) error {
	if len(datas) == 0 {
		return nil
	}
	var buffer bytes.Buffer
	sql := "insert into t_its_vehicle_province_city (d_id,time,province_city,total) 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','%s','%s',%d);", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.ProvinceCity, e.Total))
		} else {
			buffer.WriteString(fmt.Sprintf("('%s','%s','%s',%d),", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.ProvinceCity, e.Total))
		}
	}
	return G_db.Exec(buffer.String()).Error
}

type VehicleProvinceCityTag struct {
	DID          string `json:"code"`  //设备ID
	Time         string `json:"time"`  //数据时间
	ProvinceCity string `json:"city"`  //省,市
	Total        int    `json:"total"` //车辆数
	Flag         int    `json:"flag"`  //类型标识，0:日数据;1:月数据
}

func GetVehicleProvinceCity(Codes []string, start, end string, flag int) ([]VehicleProvinceCityTag, error) {
	var (
		err error
		sql string
		arr []VehicleProvinceCityTag
	)
	if flag == 0 {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d') as time,province_city,SUM(total) as total, 0 as flag FROM t_its_vehicle_province_city
          WHERE time >= ? AND time <= ? AND d_id IN (?)
          GROUP BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),province_city
          ORDER BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),province_city`
	} else {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m') as time,province_city,SUM(total) as total, 1 as flag FROM t_its_vehicle_province_city
          WHERE time >= ? AND time <= ? AND d_id IN (?) 
          GROUP BY d_id,DATE_FORMAT(time,'%Y-%m'),province_city
          ORDER BY d_id,DATE_FORMAT(time,'%Y-%m'),province_city`
	}
	err = G_db.Raw(sql, start, end, Codes).Scan(&arr).Error
	return arr, err
}

type ItsVehicleType struct {
	ID    int64     `gorm:"primary_key"`      //主键
	DID   string    `gorm:"type:varchar(32)"` //设备编码
	Time  time.Time `gorm:"type:datetime"`    //日期
	Vtype int       `gorm:"type:int"`         //类型
	Total int       `gorm:"type:int"`         //车辆数
}

func (ItsVehicleType) TableName() string {
	return "t_its_vehicle_type"
}

func MultiItsVehicleType(datas []ItsVehicleType) error {
	if len(datas) == 0 {
		return nil
	}
	var buffer bytes.Buffer
	sql := "insert into t_its_vehicle_type (d_id,time,vtype,total) 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','%s',%d,%d);", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.Vtype, e.Total))
		} else {
			buffer.WriteString(fmt.Sprintf("('%s','%s',%d,%d),", e.DID, e.Time.Format("2006-01-02 15:04:05"), e.Vtype, e.Total))
		}
	}
	return G_db.Exec(buffer.String()).Error
}

type VehicleTypeTag struct {
	DID   string `json:"code"`  //设备ID
	Time  string `json:"time"`  //数据时间
	Vtype int    `json:"vtype"` //车辆类型
	Total int    `json:"total"` //车辆数
	Flag  int    `json:"flag"`  //类型标识，0:日数据;1:月数据;2小时数据
}

func GetVehicleType(Codes []string, start, end string, flag int) ([]VehicleTypeTag, error) {
	var (
		err error
		sql string
		arr []VehicleTypeTag
	)
	if flag == 0 {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d') as time,vtype,SUM(total) as total, 0 as flag FROM t_its_vehicle_type
          WHERE time >= ? AND time <= ? AND d_id IN (?) 
          GROUP BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),vtype
          ORDER BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),vtype`
	} else {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m') as time,vtype,SUM(total) as total, 1 as flag FROM t_its_vehicle_type
          WHERE time >= ? AND time <= ? AND d_id IN (?) 
          GROUP BY d_id,DATE_FORMAT(time,'%Y-%m'),vtype
          ORDER BY d_id,DATE_FORMAT(time,'%Y-%m'),vtype`
	}
	err = G_db.Raw(sql, start, end, Codes).Scan(&arr).Error
	return arr, err
}

func GetVehicleTypeEx(Codes []string, start, end string, Type []int, flag int) ([]VehicleTypeTag, error) {
	var (
		err error
		sql string
		arr []VehicleTypeTag
	)

	if flag == 0 {
		if len(Type) > 0 {
			sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d') as time,vtype,SUM(total) as total, 0 as flag FROM t_its_vehicle_type
                   WHERE time >= ? AND time <= ? AND d_id IN (?) AND vtype IN (?)
                   GROUP BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),vtype
                   ORDER BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),vtype`
		} else {
			sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d') as time,vtype,SUM(total) as total, 0 as flag FROM t_its_vehicle_type
                   WHERE time >= ? AND time <= ? AND d_id IN (?) 
                   GROUP BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),vtype
                   ORDER BY d_id,DATE_FORMAT(time,'%Y-%m-%d'),vtype`
		}
	} else if flag == 1 {
		if len(Type) > 0 {
			sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m') as time,vtype,SUM(total) as total, 1 as flag FROM t_its_vehicle_type
                   WHERE time >= ? AND time <= ? AND d_id IN (?) AND vtype IN (?)
                   GROUP BY d_id,DATE_FORMAT(time,'%Y-%m'),vtype
                   ORDER BY d_id,DATE_FORMAT(time,'%Y-%m'),vtype`
		} else {
			sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m') as time,vtype,SUM(total) as total, 1 as flag FROM t_its_vehicle_type
                   WHERE time >= ? AND time <= ? AND d_id IN (?) 
                   GROUP BY d_id,DATE_FORMAT(time,'%Y-%m'),vtype
                   ORDER BY d_id,DATE_FORMAT(time,'%Y-%m'),vtype`
		}
	} else if flag == 2 {
		if len(Type) > 0 {
			sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d %H') as time,vtype,SUM(total) as total, 2 as flag FROM t_its_vehicle_type
                   WHERE time >= ? AND time <= ? AND d_id IN (?) AND vtype IN (?)
				   GROUP BY d_id,DATE_FORMAT(time,'%Y-%m-%d %H'),vtype
				   ORDER BY d_id,DATE_FORMAT(time,'%Y-%m-%d %H'),vtype`
		} else {
			sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d %H') as time,vtype,SUM(total) as total, 2 as flag FROM t_its_vehicle_type
                   WHERE time >= ? AND time <= ? AND d_id IN (?)
                   GROUP BY d_id,DATE_FORMAT(time,'%Y-%m-%d %H'),vtype
                   ORDER BY d_id,DATE_FORMAT(time,'%Y-%m-%d %H'),vtype`
		}
	}
	if len(Type) > 0 {
		err = G_db.Raw(sql, start, end, Codes, Type).Scan(&arr).Error
	} else {
		err = G_db.Raw(sql, start, end, Codes).Scan(&arr).Error
	}
	return arr, err
}

//车流
type VehicleStreamsTotalTag struct {
	DID   string `json:"code"`  //设备ID
	Time  string `json:"time"`  //数据时间
	Total int    `json:"total"` //车辆数
	Flag  int    `json:"flag"`  //类型标识，0:日数据;1:月数据
}

func GetVehicleStreamsTag(Codes []string, start, end string, flag int) ([]VehicleStreamsTotalTag, error) {
	var (
		err error
		sql string
		arr []VehicleStreamsTotalTag
	)
	if flag == 0 {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d') as time,SUM(total) as total, 0 as flag FROM t_its_vehicle_direction
          WHERE time >= ? AND time <= ? AND d_id IN (?) 
          GROUP BY d_id,DATE_FORMAT(time,'%Y-%m-%d')
          ORDER BY d_id,DATE_FORMAT(time,'%Y-%m-%d')`
	} else {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m') as time,SUM(total) as total, 1 as flag FROM t_its_vehicle_direction
          WHERE time >= ? AND time <= ? AND d_id IN (?) 
          GROUP BY d_id,DATE_FORMAT(time,'%Y-%m')
          ORDER BY d_id,DATE_FORMAT(time,'%Y-%m')`
	}
	err = G_db.Raw(sql, start, end, Codes).Scan(&arr).Error
	return arr, err
}

//车流分小时统计
type VehicleHourTotalTag struct {
	DID   string `json:"code"`  //设备ID
	Time  string `json:"time"`  //数据时间
	Total int    `json:"total"` //车辆数
}

func GetVehicleHourTotalTag(Codes []string, start, end string) ([]VehicleHourTotalTag, error) {
	var (
		err error
		sql string
		arr []VehicleHourTotalTag
	)
	sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d %H:00:00') as time,SUM(total) as total FROM t_its_vehicle_direction
          WHERE time >= ? AND time <= ? AND d_id IN (?) 
          GROUP BY d_id,DATE_FORMAT(time,'%Y-%m-%d %H:00:00')
          ORDER BY d_id,DATE_FORMAT(time,'%Y-%m-%d %H:00:00')`
	err = G_db.Raw(sql, start, end, Codes).Scan(&arr).Error
	return arr, err
}

//速度
type VehicleSpeedTag struct {
	DID   string `json:"code"`  //设备ID
	Time  string `json:"time"`  //数据时间
	Flag  int    `json:"flag"`  //类型标识，0:日数据;1:月数据
	Sflag int    `json:"sflag"` //速度标识
	Total int    `json:"total"` //总数
}

func GetVehicleSpeedRange(Codes []string, start, end string, flag int) ([]VehicleSpeedTag, error) {
	var (
		err error
		sql string
		arr []VehicleSpeedTag
	)
	if flag == 0 {
		sql = `SELECT a.d_id as d_id,DATE_FORMAT(a.t,'%Y-%m-%d') as time ,0 as flag, a.sp as sflag,COUNT(*) as total
               FROM (SELECT  d_id,time AS t,FLOOR(speed/20) AS sp FROM t_its_vehicle_speed  
               WHERE  d_id IN (?) AND time >= ? AND time <= ?  ) a
               GROUP BY a.d_id,DATE_FORMAT(a.t,'%Y-%m-%d'),a.sp`
	} else {
		sql = `SELECT a.d_id as d_id,DATE_FORMAT(a.t,'%Y-%m') as time ,1 as flag, a.sp as sflag,COUNT(*) as total
               FROM (SELECT  d_id,time AS t,FLOOR(speed/20) AS sp FROM t_its_vehicle_speed  
               WHERE  d_id IN (?) AND time >= ? AND time <= ?  ) a
               GROUP BY a.d_id,DATE_FORMAT(a.t,'%Y-%m'),a.sp`
	}
	err = G_db.Raw(sql, Codes, start, end).Scan(&arr).Error
	return arr, err
}

func GetVehicleOverSpeed(Codes []string, start, end string, flag int) ([]VehicleSpeedTag, error) {
	var (
		err error
		sql string
		arr []VehicleSpeedTag
	)
	if flag == 0 {
		sql = `SELECT t.d_id AS d_id,DATE_FORMAT(t.time,'%Y-%m-%d') AS time ,0 AS flag, t.flag AS sflag,COUNT(*) AS total
				FROM (SELECT  a.d_id ,a.time,
				CASE  WHEN ROUND(100*a.speed/b.suggest_speed)<= 100 THEN 100 
				WHEN ROUND(100*a.speed/b.suggest_speed) > 100 AND ROUND(100*a.speed/b.suggest_speed) < 110 THEN  101
				WHEN ROUND(100*a.speed/b.suggest_speed) >=110 AND ROUND(100*a.speed/b.suggest_speed) <120 THEN 102 
				WHEN  ROUND(100*a.speed/b.suggest_speed) >=120 AND ROUND(100*a.speed/b.suggest_speed) <150 THEN 103 
				ELSE 104 END AS flag FROM t_its_vehicle_speed a,t_device_its b 
				WHERE  a.d_id IN (?) AND a.time >= ? AND a.time <= ? AND a.d_id = b.id) t
				GROUP BY t.d_id,DATE_FORMAT(t.time,'%Y-%m-%d'),t.flag  
				ORDER BY t.d_id ,DATE_FORMAT(t.time,'%Y-%m-%d'),t.flag`
	} else {
		sql = `SELECT t.d_id AS d_id,DATE_FORMAT(t.time,'%Y-%m') AS time ,1 AS flag, t.flag AS sflag,COUNT(*) AS total
				FROM (SELECT  a.d_id ,a.time,
				CASE  WHEN ROUND(100*a.speed/b.suggest_speed)<= 100 THEN 100 
				WHEN ROUND(100*a.speed/b.suggest_speed) > 100 AND ROUND(100*a.speed/b.suggest_speed) < 110 THEN  101
				WHEN ROUND(100*a.speed/b.suggest_speed) >=110 AND ROUND(100*a.speed/b.suggest_speed) <120 THEN 102 
				WHEN  ROUND(100*a.speed/b.suggest_speed) >=120 AND ROUND(100*a.speed/b.suggest_speed) <150 THEN 103 
				ELSE 104 END AS flag FROM t_its_vehicle_speed a,t_device_its b 
				WHERE  a.d_id IN (?) AND a.time >= ? AND a.time <= ? AND a.d_id = b.id) t
				GROUP BY t.d_id,DATE_FORMAT(t.time,'%Y-%m'),t.flag  
				ORDER BY t.d_id ,DATE_FORMAT(t.time,'%Y-%m'),t.flag`
	}
	err = G_db.Raw(sql, Codes, start, end).Scan(&arr).Error
	return arr, err
}

//速度
type VehiclePlateTag struct {
	DID   string `json:"code"`  //设备ID
	Time  string `json:"time"`  //数据时间
	Vtype int    `json:"vtype"` //车辆类型
	Plate string `json:"plate"` //车牌
	Speed int    `json:"speed"` //车速
}

func GetVehiclePlate(Codes []string, start, end string, Types []int) ([]VehiclePlateTag, error) {
	var (
		err error
		sql string
		arr []VehiclePlateTag
	)
	if len(Types) > 0 {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') AS time, vtype,plate,speed FROM t_its_vehicle_speed 
           WHERE d_id IN (?) AND time >= ?  AND time  <= ? AND vtype IN (?)`
	} else {
		sql = `SELECT d_id,DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') AS time, vtype,plate,speed FROM t_its_vehicle_speed 
           WHERE d_id IN (?) AND time >= ?  AND time  <= ? `
	}

	err = G_db.Raw(sql, Codes, start, end, Types).Scan(&arr).Error
	return arr, err
}

//速度
type OverSpeedPlate struct {
	ID    int64  `json:"id"`
	DID   string `json:"code"`  //设备ID
	Time  string `json:"time"`  //数据时间
	Vtype int    `json:"vtype"` //车辆类型
	Plate string `json:"plate"` //车牌
	Speed int    `json:"speed"` //车速
	Flag  int    `json:"flag"`  //超速范围
}

func GetOverSpeedPlate(id int64, start, end string) ([]OverSpeedPlate, error) {
	var (
		err error
		sql string
		arr []OverSpeedPlate
	)
	sql = `SELECT a.id,a.d_id,DATE_FORMAT(a.time,'%Y-%m-%d %H:%i:%s') AS time,a.vtype,a.plate,a.speed,
			CASE  WHEN ROUND(100*a.speed/b.suggest_speed)<= 100 THEN 100 
			WHEN ROUND(100*a.speed/b.suggest_speed) > 100 AND ROUND(100*a.speed/b.suggest_speed) < 110 THEN  101
			WHEN ROUND(100*a.speed/b.suggest_speed) >=110 AND ROUND(100*a.speed/b.suggest_speed) <120 THEN 102 
			WHEN  ROUND(100*a.speed/b.suggest_speed) >=120 AND ROUND(100*a.speed/b.suggest_speed) <150 THEN 103 
			ELSE 104 END AS flag FROM t_its_vehicle_speed a,t_device_its b 
			WHERE  a.id >= ? and a.time >= ? AND a.time < ? AND a.d_id = b.id AND a.speed > b.suggest_speed*1.1`
	err = G_db.Raw(sql, id, start, end).Scan(&arr).Error
	return arr, err
}