smart-log/model/device.go

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

import (
	"time"
	"fmt"
	"github.com/jinzhu/gorm"
)

type TDevice struct {
	Id int64 `gorm:"column:id" json:"id" form:"id"`
	ProjectId int64 `gorm:"column:project_id" json:"project_id" form:"project_id"`
	ProviderId int64 `gorm:"column:provider_id" json:"provider_id" form:"provider_id"`
	Name string `gorm:"column:name" json:"name" form:"name"`
	Sn string `gorm:"column:sn" json:"sn" form:"sn"`
	Addr string `gorm:"column:addr" json:"addr" form:"addr"`
	DeviceCode int32 `gorm:"column:device_code" json:"device_code" form:"device_code"`
	Lon float64 `gorm:"column:lon" json:"lon" form:"lon"`
	Lat float64 `gorm:"column:lat" json:"lat" form:"lat"`
	XCoord float64 `gorm:"column:x_coord" json:"x_coord" form:"x_coord"`
	YCoord float64 `gorm:"column:y_coord" json:"y_coord" form:"y_coord"`
	Status int64 `gorm:"column:status" json:"status" form:"status"`
	CreatedAt time.Time `gorm:"column:created_at" json:"created_at" form:"created_at"`
	UpdatedAt time.Time `gorm:"column:updated_at" json:"updated_at" form:"updated_at"`
	VerifyStatus int64 `gorm:"column:verify_status" json:"verify_status" form:"verify_status"`
	Key string `gorm:"column:key" json:"key" form:"key"`
	JobId int64 `gorm:"column:job_id" json:"job_id" form:"job_id"`
	ProjectApproveTime time.Time `json:"project_approve_time"`
	DelApplyTime time.Time `json:"del_apply_time"`
}

func (TDevice) TableName() string {
	return "t_device"
}

func (p *TDevice) Insert(db *gorm.DB) error {
	return db.Create(p).Error
}

func (p *TDevice) Statistic(db *gorm.DB, projectId int64, deviceType int32, result interface{}) error {

	sql := fmt.Sprintf("select device_code as device_type, sum(status=0) as offline, sum(status=1) as online from t_device where project_id=%d group by type", projectId)
	if deviceType > 0 {
		sql = fmt.Sprintf("select device_code as device_type, sum(status=0) as offline, sum(status=1) as online from t_device where project_id=%d and type = %d group by type", projectId, deviceType)
	}
	err := db.Raw(sql).Scan(result).Error
	return err
}

func (p *TDevice) Del(db *gorm.DB, where map[string]interface{}) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		return err
	}
	return db.Table(p.TableName()).Where(cond, val...).Delete(p).Error
}

func (p *TDevice) Find(db *gorm.DB, where map[string]interface{}) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		return err
	}
	return db.Table(p.TableName()).Where(cond, val...).First(p).Error
}

func (p *TDevice) 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
}

func (p *TDevice) FindSort(db *gorm.DB, where map[string]interface{}, sort string) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		return err
	}
	ps := []TDevice{}
	err = db.Table(p.TableName()).Where(cond, val...).Order(sort).Limit(1).Find(&ps).Error
	if err != nil {
		return err
	}
	if len(ps) > 0 {
		*p = ps[0]
	}
	return nil
}

func (p *TDevice) Save(db *gorm.DB) error {
	return db.Save(p).Error
}

func (p *TDevice) 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

		return ret, err
	}
	ret := int64(0)
	err := db.Table(p.TableName()).Count(&ret).Error
	return ret, err
}

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

		result := db.Table(p.TableName()).Where(cond, val...).Limit(PageSize).Offset(page).Find(&list)

		return list, result.Error
	}
	result := db.Table(p.TableName()).Limit(10).Offset(page).Find(&list)

	return list, result.Error

}

func (p *TDevice) All(db *gorm.DB) (list []TDevice, err error) {

	result := db.Table(p.TableName()).Find(&list)

	return list, result.Error
}

type DeviceItem struct {
	Id                   int64
	Sn                   string
	TypeCode             int32
	TypeName             string
	ProjectName          string
	ProjectId            int64
	SafetyRecordNo       string
	CreatedTime          time.Time
	Status               int32
	State int32
	ProjectApproveTime time.Time
	DelApplyTime time.Time
	DelReason string
	Key string
}

func listWithProjectSql(providerId int64, filter string, statusFilter []int32, projectId int64, page int32, typeCode int32)(string, string, []interface{}) {
	sql := fmt.Sprintf("select t1.id as id, t1.key, t1.sn as sn, t1.device_code as type_code, t1.project_id as project_id, t1.project_approve_time, t1.del_apply_time,"+
		"t1.created_at as created_time, t1.status as state, t1.verify_status as status, t2.safety_record_no, t2.name as project_name from t_device as t1 left join t_project as t2 on t1.project_id=t2.id ")
	countSql := fmt.Sprintf("select count(1) as count from t_device as t1 left join t_project as t2 on t1.project_id=t2.id ")
	args := []interface{}{}
	whereArray := []string{}
	if projectId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.provider_id=%d", providerId))
	}
	if typeCode > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.device_code = %d", typeCode))
	}
	if filter != "" {
		whereArray = append(whereArray, fmt.Sprintf("(t1.sn like '%%%s%%' or t2.safety_record_no like '%%%s%%' or t2.name like '%%%s%%')", filter, filter, filter))
	}
	if len(statusFilter) > 0 {
		args = append(args, statusFilter)
		whereArray = append(whereArray, fmt.Sprintf("t1.verify_status in(?)"))
	}
	if projectId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.project_id = %d", projectId))
	}
	where := ""

	for _, v := range whereArray {
		if where == "" {
			where = fmt.Sprintf(" where %s", v)
			continue
		}
		where = fmt.Sprintf("%s and %s", where, v)
	}
	offset := (page - 1) *int32(page)
	if providerId > 0 {
		sql = fmt.Sprintf("%s %s", sql, where)
		countSql = fmt.Sprintf("%s %s", countSql, where)
	} else {
		sql = fmt.Sprintf("%s %s limit %d offset %d", sql, where, PageSize, offset)
		countSql = fmt.Sprintf("%s %s", countSql, where)
	}
	return sql, countSql, args
}

func (p *TDevice) ListWithProject(db *gorm.DB, providerId int64, filter string, statusFilter []int32, projectId int64, page int32, typeCode int32) ([]DeviceItem, int64, error) {
	type ResultCount struct {
		Count int64
	}
	array := []ResultCount{}
	ret := []DeviceItem{}
	var err error

	sql, countSql, args := listWithProjectSql(providerId, filter, statusFilter, projectId, page, typeCode)

	err = db.Raw(countSql, args...).Scan(&array).Error
	if err != nil {
		return nil, 0, err
	}
	if len(array) == 0 {
		return nil, 0, nil
	}
	if array[0].Count == 0 {
		return nil, 0, nil
	}

	err = db.Raw(sql, args...).Scan(&ret).Error
	if err != nil {
		return nil, array[0].Count, err
	}
	return ret, array[0].Count, nil

}