lc_veterans/app/device/dao/bigScreen.go

package dao

import (
	"iot_manager_service/util/logger"
)

type Big interface {
	ListDevices() ([]Device, int64, error)
	CountFault() (int64, error)
}

func getLinkNum(sn string) int64 {
	var i int64
	//SELECT total FROM device_wisdom_gateway a LEFT JOIN device_gateway_relation b ON a.id = b.id
	//WHERE a.lamp_pole_sn='ZHDG4306268O408R' ORDER BY total DESC LIMIT 1;
	Db.Select("total").Table("device_wisdom_gateway a").
		Joins("LEFT JOIN device_gateway_relation b ON a.id = b.id").
		Where("a.lamp_pole_sn = ?", sn).
		Order("total DESC").Limit(1).Find(&i)
	return i
}

func GetEnergy(start, end string) []Energy {
	var rsp = make([]Energy, 0, 12)
	//select  SUBSTR(date FROM 1 FOR 7) AS date1,SUM(difference) AS difference
	//FROM `t_device_energy_day_data` t WHERE date BETWEEN "2023-04-01" AND "2023-06-01" GROUP BY date1;
	rows, err := Db.Select("SUBSTR(date FROM 1 FOR 7) AS dates,SUM(difference) AS difference").
		Table("`t_device_energy_day_data`").Where("date BETWEEN ? AND ?", start, end).Group("dates").Rows()
	defer rows.Close()
	if err != nil {
		logger.Logger.Errorf("GetEnergy error: %v", err)
	}
	for rows.Next() {
		var e Energy
		rows.Scan(&e.Date, &e.Difference)
		rsp = append(rsp, e)
	}
	return rsp
}

// GetLightRate
// SELECT DATE_FORMAT(NOW(),'%Y-%m') AS month, AVG(rate) AS rate FROM `t_lamp_lighting_rate_day_data` WHERE SUBSTR(date FROM 1 FOR 7) = DATE_FORMAT(NOW(),'%Y-%m') AND tenant = '000000';
// SELECT QUARTER(NOW()) AS quarter, AVG(rate) AS rate FROM `t_lamp_lighting_rate_day_data` WHERE QUARTER(date) = QUARTER(NOW()) AND tenant = '000000';
// SELECT YEAR(NOW()) AS year, AVG(rate) AS rate FROM `t_lamp_lighting_rate_day_data` WHERE YEAR(date) = YEAR(NOW()) AND tenant = '000000';
func GetLightRate(tenant string) []interface{} {
	var rsp = make([]interface{}, 0, 4)
	var lrm LightRateM
	var lrq LightRateQ
	var lry LightRateY
	Db.Select("DATE_FORMAT(NOW(),'%Y-%m') AS month,ROUND(AVG(rate),2) AS rate").Table("`t_lamp_lighting_rate_day_data`").
		Where("SUBSTR(date FROM 1 FOR 7) = DATE_FORMAT(NOW(),'%Y-%m') And tenant = ?", tenant).Find(&lrm)
	Db.Select("QUARTER(NOW()) AS quarter, ROUND(AVG(rate),2) AS rate").Table("`t_lamp_lighting_rate_day_data`").
		Where("quarter(date) = quarter(NOW()) AND tenant = ?", tenant).Find(&lrq)
	Db.Select("YEAR(NOW()) AS year, ROUND(AVG(rate),2) AS rate").Table("`t_lamp_lighting_rate_day_data`").
		Where("YEAR(date) = YEAR(NOW()) AND tenant = ?", tenant).Find(&lry)
	//lr[0].Month = ""
	rsp = append(rsp, []interface{}{})
	rsp = append(rsp, lrm)
	rsp = append(rsp, lrq)
	rsp = append(rsp, lry)
	return rsp
}

// GetAlarmInfo 1待处理2忽略3已处理4其他
// #待处理
// SELECT COUNT(id) FROM `warn_platform_alarm` WHERE arm_handle = 1;
// SELECT arm_content,arm_device_type_name,dict_value AS level_name,arm_time
// FROM `warn_platform_alarm` w JOIN `dict` d
// ON w.arm_level = d.dict_key
// WHERE w.arm_handle = 1 AND d.`code` = 'arm_level_type'
// LIMIT 10;
// #本月已处理
// SELECT COUNT(id) FROM `warn_platform_alarm` WHERE SUBSTR(arm_end_time FROM 1 FOR 7)=SUBSTR(NOW() FROM 1 FOR 7) AND arm_handle = 3;
// #本年已处理
// SELECT COUNT(id) FROM `warn_platform_alarm` WHERE YEAR(arm_end_time)=YEAR(NOW()) AND arm_handle = 3;
func GetAlarmInfo() AlarmList {
	var al AlarmList
	Db.Select("COUNT(id)").Table("`warn_platform_alarm`").
		Where("arm_handle = 1").Find(&al.Backlog)
	Db.Select("COUNT(id)").Table("`warn_platform_alarm`").
		Where("SUBSTR(arm_end_time FROM 1 FOR 7)=SUBSTR(NOW() FROM 1 FOR 7) AND arm_handle = 3").Find(&al.LastMonthAlarm)
	Db.Select("COUNT(id)").Table("`warn_platform_alarm`").
		Where("YEAR(arm_end_time)=YEAR(NOW()) AND arm_handle = 3").Find(&al.LastYearAlarm)
	Db.Select("arm_content,arm_device_type_name,dict_value AS arm_level_name,arm_time AS create_time").
		Table("`warn_platform_alarm` AS w JOIN `dict` AS d ON w.arm_level = d.dict_key").
		Where("w.arm_handle = 1 AND d.`code` = 'arm_level_type'").Order("create_time DESC").Limit(7).Find(&al.List)
	return al
}

type OverView struct {
	Id        int64  `gorm:"primary_key" json:"id"`
	TenantId  string `json:"tenantId"`
	PadScreen string `json:"padScreen"`
	Site      string `json:"site"`
	SiteValue int64  `json:"siteValue"`
}

func (o *OverView) TableName() string {
	return "big_screen_overview"
}

type OverViewInfo struct {
	Id        int64 `gorm:"primary_key" json:"id"`
	SiteValue int64 `json:"siteValue"`
}

func (o *OverView) Query(tenantId, padScreen string) []OverViewInfo {
	var rsp []OverViewInfo
	Db.Select("id, site_value").Table(o.TableName()).Where("tenant_id = ? AND pad_screen = ?", tenantId, padScreen).Find(&rsp)
	return rsp
}
func (o *OverView) Submit(data OverView) {
	Db.Model(&o).Select("pad_screen", "site_value").Where("id=?", data.Id).Updates(&data)
}

func GetEnvData(id string) Environment {
	var env Environment
	//"lampPoleLocation"
	Db.Select("air, create_date, temperature, humidity, pm25, pm10, noise, hpa, wind_direction, wind_speed").
		Table("data_environment").Where("device_id=?", id).
		Order("create_date DESC").Limit(1).Find(&env)
	return env
}

// GetLEDProgramList
// SELECT m.id,m.name,m.duration,d.dict_value, end_time
// FROM `media_publish_libraries` m JOIN `dict` d
// ON dict_key = m.resolution
// WHERE m.tenant_id = '000000' and m.sys_type=0 and m.is_deleted = 0 AND d.code = 'resolution_type'
// ORDER BY id desc LIMIT 10
func GetLEDProgramList(tenantId string) []InfoBoardProgram {
	var list []InfoBoardProgram
	Db.Select("m.id,m.name,m.duration,d.dict_value AS resolution_name, end_time ,(LENGTH(info_id) - LENGTH(REPLACE(info_id, ',', '')) + 1) AS count").
		Table("`media_publish_libraries` m JOIN `dict` d ON dict_key = m.resolution").
		Where("d.code='resolution_type' AND m.sys_type=0 AND m.is_deleted=0 AND m.tenant_id=?", tenantId).
		Order("id DESC").Limit(10).Find(&list)
	return list
}

func GetProgramUrl(id string) Program {
	var rsp Program
	//SELECT name FROM `media_publish_libraries` WHERE id = 45
	t1 := Db.Select("name").Table("media_publish_libraries").Where("id=(?)", id)
	//SELECT id FROM `media_program` WHERE name =?
	t2 := Db.Select("id").Table("media_program").Where("name=(?)", t1)
	//SELECT library_id FROM media_program_relation WHERE program_id = ?
	t3 := Db.Select("library_id").Table("media_program_relation").Where("program_id=(?)", t2)
	//SELECT material_address FROM `media_library` WHERE id =?
	Db.Select("material_address AS send_url").Table("media_library").Where("id =(?)", t3).Find(&rsp)
	return rsp
}

func GetBridgeSensors(tenantId string) []Sensor {
	var rsp []Sensor
	//SELECT id,name FROM `device_bridge_sensor` WHERE tenant_id = '000000' AND is_deleted = 0
	Db.Select("id,name").Table("device_bridge_sensor").Where("tenant_id = ? AND is_deleted = 0", tenantId).Find(&rsp)
	return rsp
}