smart-enterprise-management/model/device.go

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

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

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"`
	ProjectApproveTime time.Time `json:"project_approve_time"`
}

const (
	DeviceTypeVedio = 9
	DeviceTypeDust = 5
)

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, where map[string]interface{}) (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...).Find(&list)

		return list, result.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
	Key string
	ProviderName string
	SocialCode string
	Name string
	Ip string
	Port int
	MediaTransport string
	ChannelCount int

}



func listRawSqlForVedio(req ListRawRequest)(string, string, []interface{}) {
	sql := fmt.Sprintf("select t1.id as id, t1.key, t1.name as name, t1.sn as sn, t1.device_code as type_code, t1.project_id as project_id, t1.project_approve_time, t4.MediaTransport as media_transport, t4.IP as ip, t4.Port as port, t4.channel_count, "+
		"t1.created_at as created_time, t4.Alive as state, t1.verify_status as status, t2.safety_record_no, t2.name as project_name, t3.name as provider_name, t3.social_code from t_device as t1 left join t_project as t2 on t1.project_id=t2.id and t2.cid=%d left join t_provider as t3 on t1.provider_id=t3.id left join VSSDevTbl as t4 on t4.DevPubID=t1.sn", req.Cid)
	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  and t2.cid=%d left join t_provider as t3 on t1.provider_id=t3.id left join VSSDevTbl as t4 on t4.DevPubID=t1.sn", req.Cid)
	args := []interface{}{}
	whereArray := []string{}
	if req.ProviderId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.provider_id=%d", req.ProviderId))
	}
	if req.Cid > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t2.cid=%d", req.Cid))
	}

	whereArray = append(whereArray, fmt.Sprintf("t1.device_code = %d", DeviceTypeVedio))

	if req.Filter != "" {
		whereArray = append(whereArray, fmt.Sprintf("(t1.sn like '%%%s%%' or t2.safety_record_no like '%%%s%%' or t2.name like '%%%s%%')", req.Filter, req.Filter, req.Filter))
	}

	if req.ProjectId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.project_id = %d", req.ProjectId))
	}
	if req.CanDel {
		req.IsAll = true
		req.StatusFilter = []int32{1}
		whereArray = append(whereArray, fmt.Sprintf("(select count(1) from t_project_job where device_id=t1.id and type = 1 and (status = 0 or status = 1)) = 0"))
	}
	if len(req.StatusFilter) > 0 {
		args = append(args, req.StatusFilter)
		whereArray = append(whereArray, fmt.Sprintf("t1.verify_status in(?)"))
	}
	where := ""

	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) *int32(PageSize)
	if req.IsAll {
		sql = fmt.Sprintf("%s %s order by t1.created_at desc", sql, where)
		countSql = fmt.Sprintf("%s %s", countSql, where)
	} else {
		sql = fmt.Sprintf("%s %s order by t1.created_at desc limit %d offset %d", sql, where, PageSize, offset)
		countSql = fmt.Sprintf("%s %s", countSql, where)
	}
	return sql, countSql, args
}


func listRawSql(req ListRawRequest)(string, string, []interface{}) {
	if req.IsVedio {
		return listRawSqlForVedio(req)
	}
	sql := fmt.Sprintf("select t1.id as id, t1.key, t1.name, t1.sn as sn, t1.device_code as type_code, t1.project_id as project_id, t1.project_approve_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, t3.name as provider_name, t3.social_code from t_device as t1 left join t_project as t2 on t1.project_id=t2.id and t2.cid=%d left join t_provider as t3 on t1.provider_id=t3.id", req.Cid)
	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 and t2.cid=%d left join t_provider as t3 on t1.provider_id=t3.id", req.Cid)
	args := []interface{}{}
	whereArray := []string{}
	if req.ProviderId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.provider_id=%d", req.ProviderId))
	}
	if req.Cid > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t2.cid=%d", req.Cid))
	}
	if !req.CanDel {
		whereArray = append(whereArray, fmt.Sprintf("t1.device_code <> %d", DeviceTypeVedio))
	}
	if req.TypeCode > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.device_code = %d", req.TypeCode))
	}
	if req.Filter != "" {
		whereArray = append(whereArray, fmt.Sprintf("(t1.sn like '%%%s%%' or t2.safety_record_no like '%%%s%%' or t2.name like '%%%s%%')", req.Filter, req.Filter, req.Filter))
	}

	if req.ProjectId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.project_id = %d", req.ProjectId))
	}
	if req.CanDel {
		req.IsAll = true
		req.StatusFilter = []int32{1}
		whereArray = append(whereArray, fmt.Sprintf("(select count(1) from t_project_job where device_id=t1.id and type = 1 and (status = 0 or status = 1)) = 0"))
	}
	if len(req.StatusFilter) > 0 {
		args = append(args, req.StatusFilter)
		whereArray = append(whereArray, fmt.Sprintf("t1.verify_status in(?)"))
	}
	where := ""

	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) *int32(PageSize)
	if req.IsAll {
		sql = fmt.Sprintf("%s %s order by t1.created_at desc", sql, where)
		countSql = fmt.Sprintf("%s %s", countSql, where)
	} else {
		sql = fmt.Sprintf("%s %s order by t1.created_at desc limit %d offset %d", sql, where, PageSize, offset)
		countSql = fmt.Sprintf("%s %s", countSql, where)
	}
	return sql, countSql, args
}

type ListRawRequest struct {
	ProviderId int64
	Filter string
	StatusFilter []int32
	IsAll bool
	CanDel bool
	Page int32
	TypeCode int32
	ProjectId int64
	IsVedio bool
	Cid int64
}

func (p *TDevice) ListRaw(db *gorm.DB, req ListRawRequest) ([]DeviceItem, int64, error) {
	type ResultCount struct {
		Count int64
	}
	array := []ResultCount{}
	ret := []DeviceItem{}
	var err error

	sql, countSql, args := listRawSql(req)

	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
}

type DeviceDelJobItem struct {
	Id                   int64
	Sn                   string
	TypeCode             int32
	TypeName             string
	ProjectName          string
	ProviderName string
	SocialCode string
	SafetyRecordNo       string
	CreatedTime          time.Time
	Status               int32
	ApproveTime time.Time
	Origin string
	Reason string
	Feedback string
}


func listDelJobSql(req ListRawRequest)(string, string, []interface{}) {
	sql := "select t1.origin, t1.reason, t1.feedback, t1.id, t1.status, t1.updated_at as approve_time, t1.created_at as created_time, t2.name as provider_name, t2.social_code, t3.safety_record_no, t3.name as project_name " +
		"from t_project_job as t1 left join t_provider as t2 on t1.provider_id=t2.id left join t_project as t3 on t3.id=t1.project_id "
	countSql := "select count(1) as count " +
		"from t_project_job as t1 left join t_provider as t2 on t1.provider_id=t2.id left join t_project as t3 on t3.id=t1.project_id "
	args := []interface{}{}
	whereArray := []string{"t1.type = 1"}
	if req.ProviderId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.provider_id=%d", req.ProviderId))
	}
	if req.Filter != "" {
		whereArray = append(whereArray, fmt.Sprintf("(t1.content like '%%%s%%' or t2.social_code like '%%%s%%' or t2.name like '%%%s%%')", req.Filter, req.Filter, req.Filter))
	}
	if len(req.StatusFilter) > 0 {
		args = append(args, req.StatusFilter)
		whereArray = append(whereArray, fmt.Sprintf("t1.status in(?)"))
	}
	if req.ProjectId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.project_id = %d", req.ProjectId))
	}

	where := ""
	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) *int32(PageSize)
	if req.IsAll {
		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) ListDelJob(db *gorm.DB, req ListRawRequest) ([]DeviceDelJobItem, int64, error) {
	type ResultCount struct {
		Count int64
	}
	array := []ResultCount{}
	ret := []DeviceDelJobItem{}
	var err error

	sql, countSql, args := listDelJobSql(req)

	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
	}

	for i, v := range ret {
		ret[i].Sn = gjson.GetBytes([]byte(v.Origin), "sn").String()
		ret[i].TypeCode = int32(gjson.GetBytes([]byte(v.Origin), "device_code").Int())
	}

	return ret, array[0].Count, nil
}