lc_veterans/app/operation/dao/viewsAllCodeDeviceDao.go

package dao

import "iot_manager_service/app/operation/model"

type ViewsAllCodeDevice struct {
	TenantId       int    `json:"tenantId"`
	DeviceType     int    `json:"deviceType"`     //类型 1-14
	DeviceTypeName string `json:"deviceTypeName"` //类型中文名
	MonthTime      string `json:"monthTime,omitempty"`
	YearTime       string `json:"yearTime,omitempty"`
	OffLineCountT  int    `json:"offLine"`    //掉线数
	AlarmCountT    int    `json:"countAlarm"` //报警数

	DeviceType1CountT  int `json:"deviceType1CountT"`
	DeviceType2CountT  int `json:"deviceType2CountT"`
	DeviceType3CountT  int `json:"deviceType3CountT"`
	DeviceType4CountT  int `json:"deviceType4CountT"`
	DeviceType5CountT  int `json:"deviceType5CountT"`
	DeviceType6CountT  int `json:"deviceType6CountT"`
	DeviceType7CountT  int `json:"deviceType7CountT"`
	DeviceType8CountT  int `json:"deviceType8CountT"`
	DeviceType9CountT  int `json:"deviceType9CountT"`
	DeviceType10CountT int `json:"deviceType10CountT"`
	DeviceType11CountT int `json:"deviceType11CountT"`
	DeviceType12CountT int `json:"deviceType12CountT"`
	DeviceType13CountT int `json:"deviceType13CountT"`
	DeviceType14CountT int `json:"deviceType14CountT"`
	DeviceType15CountT int `json:"deviceType15CountT"`
	CountT             int `json:"countT"` //设备数
}

func (ViewsAllCodeDevice) TableName() string {
	return "view_all_code" //此处不是真实表，是使用视图查询的
}

// 总设备数
func (v ViewsAllCodeDevice) GetDeviceCount() ([]ViewsAllCodeDevice, error) {
	var list []ViewsAllCodeDevice
	err := Db.Debug().Model(&v).Raw("SELECT device_type,device_type_name,count(*) as count_t FROM view_all_code where tenant_id=? GROUP BY device_type,device_type_name", v.TenantId).Scan(&list).Error
	return list, err
}

// 按月统计
func (v ViewsAllCodeDevice) GetMonthDeviceCount(req model.RequestDeviceCountFilter) ([]ViewsAllCodeDevice, error) {
	var list []ViewsAllCodeDevice
	sql := `
SELECT
	month_time,
	sum(count_t) AS count_t,
	SUM(CASE device_type WHEN 1 THEN count_t ELSE 0 END) as device_type1_count_t,
	SUM(CASE device_type WHEN 2 THEN count_t ELSE 0 END) as device_type2_count_t,
	SUM(CASE device_type WHEN 3 THEN count_t ELSE 0 END) as device_type3_count_t,
	SUM(CASE device_type WHEN 4 THEN count_t ELSE 0 END) as device_type4_count_t,
	SUM(CASE device_type WHEN 5 THEN count_t ELSE 0 END) as device_type5_count_t,
	SUM(CASE device_type WHEN 6 THEN count_t ELSE 0 END) as device_type6_count_t,
	SUM(CASE device_type WHEN 7 THEN count_t ELSE 0 END) as device_type7_count_t,
	SUM(CASE device_type WHEN 8 THEN count_t ELSE 0 END) as device_type8_count_t,
	SUM(CASE device_type WHEN 9 THEN count_t ELSE 0 END) as device_type9_count_t,
	SUM(CASE device_type WHEN 10 THEN count_t ELSE 0 END) as device_type10_count_t,
	SUM(CASE device_type WHEN 11 THEN count_t ELSE 0 END) as device_type11_count_t,
	SUM(CASE device_type WHEN 12 THEN count_t ELSE 0 END) as device_type12_count_t,
	SUM(CASE device_type WHEN 13 THEN count_t ELSE 0 END) as device_type13_count_t,
	SUM(CASE device_type WHEN 14 THEN count_t ELSE 0 END) as device_type14_count_t,
	SUM(CASE device_type WHEN 15 THEN count_t ELSE 0 END) as device_type15_count_t
FROM
	(
	SELECT
		device_type,
		count(*) AS count_t,
		date_format( create_time, '%Y-%m' ) AS month_time 
	FROM
		view_all_code 
	where tenant_id=? and create_time >= ? and create_time<=?
	GROUP BY
		device_type,
		month_time 
	ORDER BY
		month_time DESC 
	) a 
GROUP BY
	month_time
`
	err := Db.Debug().Model(&v).Raw(sql, v.TenantId, req.StartTime, req.EndTime).Scan(&list).Error
	return list, err
}

// 按年统计
func (v ViewsAllCodeDevice) GetYearDeviceCount(req model.RequestDeviceCountFilter) ([]ViewsAllCodeDevice, error) {
	var list []ViewsAllCodeDevice
	sql := `
SELECT
	year_time,
	sum(count_t) AS count_t,
	SUM(CASE device_type WHEN 1 THEN count_t ELSE 0 END) as device_type1_count_t,
	SUM(CASE device_type WHEN 2 THEN count_t ELSE 0 END) as device_type2_count_t,
	SUM(CASE device_type WHEN 3 THEN count_t ELSE 0 END) as device_type3_count_t,
	SUM(CASE device_type WHEN 4 THEN count_t ELSE 0 END) as device_type4_count_t,
	SUM(CASE device_type WHEN 5 THEN count_t ELSE 0 END) as device_type5_count_t,
	SUM(CASE device_type WHEN 6 THEN count_t ELSE 0 END) as device_type6_count_t,
	SUM(CASE device_type WHEN 7 THEN count_t ELSE 0 END) as device_type7_count_t,
	SUM(CASE device_type WHEN 8 THEN count_t ELSE 0 END) as device_type8_count_t,
	SUM(CASE device_type WHEN 9 THEN count_t ELSE 0 END) as device_type9_count_t,
	SUM(CASE device_type WHEN 10 THEN count_t ELSE 0 END) as device_type10_count_t,
	SUM(CASE device_type WHEN 11 THEN count_t ELSE 0 END) as device_type11_count_t,
	SUM(CASE device_type WHEN 12 THEN count_t ELSE 0 END) as device_type12_count_t,
	SUM(CASE device_type WHEN 13 THEN count_t ELSE 0 END) as device_type13_count_t,
	SUM(CASE device_type WHEN 14 THEN count_t ELSE 0 END) as device_type14_count_t,
	SUM(CASE device_type WHEN 15 THEN count_t ELSE 0 END) as device_type15_count_t
FROM
	(
	SELECT
		device_type,
		count(*) AS count_t,
		date_format( create_time, '%Y' ) AS year_time 
	FROM
		view_all_code 
	where tenant_id=? 
	GROUP BY
		device_type,
		year_time 
	ORDER BY
		year_time DESC 
	) a 
GROUP BY
	year_time
`
	err := Db.Debug().Model(&v).Raw(sql, v.TenantId).Scan(&list).Error
	return list, err
}

func (v ViewsAllCodeDevice) GetDayDeviceCount(req model.RequestDeviceCountFilter) ([]ViewsAllCodeDevice, error) {
	var list []ViewsAllCodeDevice
	sql := `
SELECT
	month_time,
	sum(count_t) AS count_t,
	SUM(CASE device_type WHEN 1 THEN count_t ELSE 0 END) as device_type1_count_t,
	SUM(CASE device_type WHEN 2 THEN count_t ELSE 0 END) as device_type2_count_t,
	SUM(CASE device_type WHEN 3 THEN count_t ELSE 0 END) as device_type3_count_t,
	SUM(CASE device_type WHEN 4 THEN count_t ELSE 0 END) as device_type4_count_t,
	SUM(CASE device_type WHEN 5 THEN count_t ELSE 0 END) as device_type5_count_t,
	SUM(CASE device_type WHEN 6 THEN count_t ELSE 0 END) as device_type6_count_t,
	SUM(CASE device_type WHEN 7 THEN count_t ELSE 0 END) as device_type7_count_t,
	SUM(CASE device_type WHEN 8 THEN count_t ELSE 0 END) as device_type8_count_t,
	SUM(CASE device_type WHEN 9 THEN count_t ELSE 0 END) as device_type9_count_t,
	SUM(CASE device_type WHEN 10 THEN count_t ELSE 0 END) as device_type10_count_t,
	SUM(CASE device_type WHEN 11 THEN count_t ELSE 0 END) as device_type11_count_t,
	SUM(CASE device_type WHEN 12 THEN count_t ELSE 0 END) as device_type12_count_t,
	SUM(CASE device_type WHEN 13 THEN count_t ELSE 0 END) as device_type13_count_t,
	SUM(CASE device_type WHEN 14 THEN count_t ELSE 0 END) as device_type14_count_t,
	SUM(CASE device_type WHEN 15 THEN count_t ELSE 0 END) as device_type15_count_t
FROM
	(
	SELECT
		device_type,
		count(*) AS count_t,
		date_format( create_time, '%Y-%m-%d' ) AS month_time 
	FROM
		view_all_code 
	where tenant_id=? and create_time >= ? and create_time<=?
	GROUP BY
		device_type,
		month_time 
	ORDER BY
		month_time DESC 
	) a 
GROUP BY
	month_time
`
	err := Db.Debug().Model(&v).Raw(sql, v.TenantId, req.StartTime, req.EndTime).Scan(&list).Error
	return list, err
}