smart-government-management/model/alarm.go

// Copyright 2019 github.com. All rights reserved.
// Use of this source code is governed by github.com.

package model

import (
	"encoding/json"
	"fmt"
	"smart-government-management/consts"
	"smart-government-management/errors"
	"time"

	"github.com/jaryhe/gopkgs/logger"
	"github.com/jinzhu/gorm"
	"go.uber.org/zap"
)

type TAlarm struct {
	Id          int64  `gorm:"primary_key"`
	ProjectId   int64  `gorm:"column:project_id"`
	Sn          string `gorm:"column:sn"`
	AlarmCode   string `gorm:"column:alarm_code"`
	AlarmReason string `gorm:"column:alarm_reason"`
	IsHandle    bool   `gorm:"column:is_handle"`
	Time        time.Time `gorm:"column:time"`
	CreatedAt   string `gorm:"column:created_at"`
	UpdatedAt   string `json:"updated_at"`
	DeviceCode int
}

func (TAlarm) TableName() string {
	return "t_alarm"
}

func (p *TAlarm) Insert(db *gorm.DB) error {
	timeNow := time.Now().Format(consts.TimeSecondLayOut)
	p.CreatedAt = timeNow
	p.UpdatedAt = timeNow
	err := db.Create(p).Error
	if err != nil {
		fields, _ := json.Marshal(*p)
		logger.Error("mysql",
			zap.String("sql", "insert into t_alarm"),
			zap.String("fields", string(fields)),
			zap.String("error", err.Error()))
		return errors.DataBaseError
	}
	return nil
}

func (p *TAlarm) Delete(db *gorm.DB, filter map[string]interface{}) error {
	err := db.Where(filter).Delete(p).Error
	if err != nil {
		fields, _ := json.Marshal(filter)
		logger.Error("mysql",
			zap.String("sql", "delete from t_alarm"),
			zap.String("fields", string(fields)),
			zap.String("error", err.Error()))
		return errors.DataBaseError
	}
	return nil
}

func (p *TAlarm) Update(db *gorm.DB, where map[string]interface{}, values map[string]interface{}) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		return err
	}

	return db.Table(p.TableName()).Where(cond, val...).Updates(values).Error
}

// 通过结构体变量更新字段值, gorm库会忽略零值字段。就是字段值等于0, nil, "", false这些值会被忽略掉，不会更新。如果想更新零值，可以使用map类型替代结构体。
func (p *TAlarm) UpdateSome(db *gorm.DB, filed map[string]interface{}) error {
	if filed == nil {
		return errors.ParamsError
	}
	timeNow := time.Now().Format(consts.TimeSecondLayOut)
	filed["updated_at"] = timeNow
	err := db.Model(p).Updates(filed).Error
	if err != nil {
		fields, _ := json.Marshal(filed)
		logger.Error("mysql",
			zap.String("sql", "update t_alarm"),
			zap.String("fields", string(fields)),
			zap.String("error", err.Error()))
		return errors.DataBaseError
	}
	return nil
}

func (p *TAlarm) Query(db *gorm.DB, filter map[string]interface{}) error {
	err := db.Where(filter).Find(p).Error
	if err != nil {
		fields, _ := json.Marshal(filter)
		logger.Error("mysql",
			zap.String("sql", "select from t_alarm"),
			zap.String("fields", string(fields)),
			zap.String("error", err.Error()))
		return err
	}
	return nil
}

func (p *TAlarm) Count(db *gorm.DB, where map[string]interface{}) (int64, error) {
	if len(where) > 0 {
		cond, val, err := whereBuild(where)
		if err != nil {
			return 0, err
		}
		ret := int64(0)
		err = db.Table(p.TableName()).Where(cond, val...).Count(&ret).Error
		if err != nil {
			fields, _ := json.Marshal(where)
			logger.Error("mysql",
				zap.String("sql", "select count from t_alarm"),
				zap.String("fields", string(fields)),
				zap.String("error", err.Error()))
			return ret, errors.DataBaseError
		}
		return ret, err
	}
	ret := int64(0)
	err := db.Table(p.TableName()).Count(&ret).Error
	if err != nil {
		fields, _ := json.Marshal(where)
		logger.Error("mysql",
			zap.String("sql", "select count from t_alarm"),
			zap.String("fields", string(fields)),
			zap.String("error", err.Error()))
		return ret, errors.DataBaseError
	}
	return ret, err
}

type AlarmListRequest struct {
	Page int32
	PageSize int32
	IsHandle int32
	Start int64
	End int64
	Filter string
	ProjectId int64
	DeviceCode int32
}

type AlarmListItem struct {
	TAlarm
	DeviceCode int32
	Name string
	ProjectName string
	SafetyRecordNo string
}

func AlarmListMoreSql(req *AlarmListRequest)(string, string) {
	sql := fmt.Sprintf("select t1.*, t2.device_code, t2.name, t3.name as project_name, t3.safety_record_no from t_alarm as t1 left join t_device as t2 on t1.sn=t2.sn and t1.device_code=t2.device_code and t1.project_id=t2.project_id left join t_project as t3 on t3.id=t1.project_id")
	countSql := fmt.Sprintf("select count(1) as count from t_alarm as t1 left join t_device as t2 on t1.sn=t2.sn and t1.device_code=t2.device_code and t1.project_id=t2.project_id left join t_project as t3 on t3.id=t1.project_id")
	whereArray := []string{}
	where := ""
	whereArray = append(whereArray, fmt.Sprintf("t2.sn is not null"))
	whereArray = append(whereArray, fmt.Sprintf("t1.first = 1"))
	if req.Filter != "" {
		whereArray = append(whereArray, fmt.Sprintf("(t1.sn like '%%%s%%' or t2.name like '%%%s%%')", req.Filter, req.Filter))
	}
	if req.ProjectId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("(t1.project_id=%d)", req.ProjectId))
	}
	if req.DeviceCode > 0 {
		whereArray = append(whereArray, fmt.Sprintf("(t1.device_code=%d)", req.DeviceCode))
	}

	if req.IsHandle == 1 {
		whereArray = append(whereArray, fmt.Sprintf("t1.is_handle=1"))
	} else if req.IsHandle == 2 {
		whereArray = append(whereArray, fmt.Sprintf("t1.is_handle=0"))
	}
	if req.Start > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.time >= '%s'", time.Unix(req.Start, 0).Format("2006-01-02 15:04:05")))
	}
	if req.End > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.time < '%s'", time.Unix(req.End, 0).Format("2006-01-02 15:04:05")))
	}

	for _, v := range whereArray {
		if where == "" {
			where = fmt.Sprintf(" where %s", v)
			continue
		}
		where = fmt.Sprintf("%s and %s", where, v)
	}
	offset := (req.Page - 1) *req.PageSize
	limit := req.PageSize

	sql = fmt.Sprintf("%s %s order by t1.time desc limit %d offset %d", sql, where, limit, offset)
	countSql = fmt.Sprintf("%s %s", countSql, where)

	return sql, countSql
}

func (p *TAlarm) ListMore(db *gorm.DB, req *AlarmListRequest) (list []AlarmListItem, total int64, err error) {

	type ResultCount struct {
		Count int64
	}
	array := []ResultCount{}
	ret := []AlarmListItem{}

	sql, countSql := AlarmListMoreSql(req)

	err = db.Raw(countSql).Scan(&array).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", countSql),
			zap.String("error", err.Error()))
		return  nil, 0, errors.DataBaseError
	}
	if len(array) == 0 {
		return nil, 0, nil
	}
	if array[0].Count == 0 {
		return nil, 0, nil
	}

	err = db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return nil, array[0].Count, errors.DataBaseError
	}
	return ret, array[0].Count, nil
}

func (p *TAlarm) List(db *gorm.DB, where map[string]interface{}, page int32, pagesize int32) (list []TAlarm, err error) {
	offset := (page - 1) * pagesize
	if len(where) > 0 {
		cond, val, err := whereBuild(where)
		if err != nil {
			return list, err
		}

		err = db.Table(p.TableName()).Where(cond, val...).Limit(PageSize).Offset(offset).Find(&list).Error
		if err != nil {
			fields, _ := json.Marshal(where)
			logger.Error("mysql",
				zap.String("sql", "select * from t_alarm"),
				zap.String("fields", string(fields)),
				zap.String("error", err.Error()))
			return list, errors.DataBaseError
		}

		return list, err
	}
	err = db.Table(p.TableName()).Limit(10).Offset(page).Find(&list).Error
	if err != nil {
		fields, _ := json.Marshal(where)
		logger.Error("mysql",
			zap.String("sql", "select * from t_alarm"),
			zap.String("fields", string(fields)),
			zap.String("error", err.Error()))
		return list, errors.DataBaseError
	}

	return list, err

}

type AlarmProjectItem struct {
	ProjectId int64
	Total int64
	Handled int64
	ProjectName string
}

func AlarmProjectStatistic(db *gorm.DB) ([]AlarmProjectItem, error) {
	ret := []AlarmProjectItem{}
	sql := fmt.Sprintf("select sum(1) as total, sum(is_handle=1) as handled, t1.project_id, t2.name as project_name from t_alarm as t1 left join t_project as t2 on t1.project_id=t2.id where t1.first=1 group by t1.project_id")
	err := db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return nil, errors.DataBaseError
	}
	return ret, nil
}

type AlarmReasonItem struct {
	Total int64
	Reason string
	Handled int64
}

func AlarmReasonStatistic(db *gorm.DB)([]AlarmReasonItem, error) {
	sql := fmt.Sprintf("select sum(1) as total, sum(is_handle) as handled, alarm_reason as reason from t_alarm where first=1 group by alarm_code")
	ret := []AlarmReasonItem{}
	err := db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return nil, errors.DataBaseError
	}
	return ret, nil
}

type AlarmDeviceStatisticItem struct {
	Count int64
	DeviceCode uint32
}

func AlarmDeviceStatistic(db *gorm.DB)([]AlarmDeviceStatisticItem, error) {
	sql := fmt.Sprintf("select sum(1) as count, device_code from t_alarm where first=1 and is_handle<>1 group by device_code")
	ret := []AlarmDeviceStatisticItem{}
	err := db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return nil, errors.DataBaseError
	}
	return ret, nil
}
var timeLayOut = "2006-01-02 15:04:05"
func getDayTime(now time.Time)(start, end string) {
	nowStart := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
	nowEnd := nowStart.Add(24*time.Hour)
	return nowStart.Format(timeLayOut), nowEnd.Format(timeLayOut)
}

func getMonthTime(now time.Time)(start string, end string) {
	nowStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, now.Location())
	nowEnd := nowStart
	start = nowStart.Format(timeLayOut)
	for {
		nowEnd = nowEnd.Add(24*time.Hour)
		end = nowEnd.Format(timeLayOut)
		if nowEnd.Month() != nowStart.Month() {
			return start, end
		}
	}
	return start, end
}

func AlarmIncreaseStatistic(db *gorm.DB)(dayIncrease int64, monthIncrease int64, total int64, err error) {
	type Result struct {
		Count int64
	}
	ret := []Result{}
	now := time.Now()
	dayStart, dayEnd := getDayTime(now)
	monthStart, monthEnd := getMonthTime(now)
	// 当天新增
	sql := fmt.Sprintf("select count(1) as count from t_alarm where first = 1 and is_handle<>1 and time >= '%s' and time < '%s'", dayStart, dayEnd)
	err = db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return 0, 0, 0, errors.DataBaseError
	}
	if len(ret) > 0 {
		dayIncrease = ret[0].Count
	}
	ret = []Result{}
	// 当月新增
	sql = fmt.Sprintf("select count(1) as count from t_alarm where first = 1 and is_handle<>1 and time >= '%s' and time < '%s'", monthStart, monthEnd)
	err = db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return 0, 0, 0, errors.DataBaseError
	}
	if len(ret) > 0 {
		monthIncrease = ret[0].Count
	}

	ret = []Result{}
	// 总数
	sql = fmt.Sprintf("select count(1) as count from t_alarm where first = 1 and is_handle<>1")
	err = db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return 0, 0, 0, errors.DataBaseError
	}
	if len(ret) > 0 {
		total = ret[0].Count
	}
	return
}

type AlarmPercentStatisticItem struct {
	Count int64
	HandleCount int64
	Date time.Time
}

func AlarmPercentStatistic(db *gorm.DB, start string, end string) (list []AlarmPercentStatisticItem, err error) {

	sql := fmt.Sprintf("select sum(1) as count, sum(is_handle=1) as handle_count, date(time) as date  from t_alarm where first=1 and time >= '%s' and time < '%s' group by date(time)", start, end)

	ret := []AlarmPercentStatisticItem{}
	err = db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return nil, errors.DataBaseError
	}
	return  ret, nil
}