smart-government-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"
)

const (
	DeviceTypeVedio = 9
	DeviceTypeDust = 5
	DeviceTypeTower = 1
	DeviceTypeHighFramework = 7
	DeviceTypeUnloading = 6
	DeviceTypeUpDown = 4
	DeviceTypeWireless = 8
	DeviceTypeVehicle = 2
	DeviceTypeStaff = 3
)

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"`
}

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
	Key string
	ProviderName string
	SocialCode string
	Name string
	Ip string
	Port int
	MediaTransport string
	ChannelCount int
	Lon float64
	Lat float64

}
const (
	DeviceTypeVideo = 9
)
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 left join t_provider as t3 on t1.provider_id=t3.id left join VSSDevTbl as t4 on t4.DevPubID=t1.sn")
	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 req.ProviderId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.provider_id=%d", req.ProviderId))
	}

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

	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, t2.lon, t2.lat, 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 left join t_provider as t3 on t1.provider_id=t3.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 req.ProviderId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.provider_id=%d", req.ProviderId))
	}
	if req.TypeCode > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.device_code = %d", req.TypeCode))
	}
	if !req.CanDel {
		whereArray = append(whereArray, fmt.Sprintf("t1.device_code <> %d", DeviceTypeVideo))
	}
	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
}

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
	Name 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 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 (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())
		ret[i].Name = gjson.GetBytes([]byte(v.Origin), "name").String()
	}

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

type DeviceStatisticData struct {
	Total int64
	Online int64
	Offline int64
	Increase int64
}

func GetIncreaseStart(month bool) string {
	now := time.Now()
	var ret time.Time
	if month {
		ret = time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, now.Location())
		return ret.Format("2006-01-02 15:04:05")
	}

	switch  {
	case int(now.Month()) < 4 :
		ret = time.Date(now.Year(), 1, 1, 0, 0, 0, 0, now.Location())
	case int(now.Month()) < 7 :
		ret = time.Date(now.Year(), 4, 1, 0, 0, 0, 0, now.Location())
	case int(now.Month()) < 10 :
		ret = time.Date(now.Year(), 7, 1, 0, 0, 0, 0, now.Location())
	default:
		ret = time.Date(now.Year(), 10, 1, 0, 0, 0, 0, now.Location())
	}
	return ret.Format("2006-01-02 15:04:05")
}

func DeviceStatistic(db *gorm.DB) (DeviceStatisticData, error) {
	type Result struct {
		Count int64
		Online int64
		Increase int64
	}
	//
	ret := DeviceStatisticData{}
	start := GetIncreaseStart(false)
	sql := fmt.Sprintf("select count(1) as count, sum(status=1) as online, sum(created_at >= '%s') as increase from t_device where verify_status=1 and device_code <> 9", start)
	array := []Result{}
	err := db.Raw(sql).Scan(&array).Error
	if err != nil {
		return ret, err
	}
	if len(array) > 0 {
		ret.Total = array[0].Count
		ret.Online = array[0].Online
		ret.Increase = array[0].Increase
	}
	//
	sql = fmt.Sprintf("select count(1) as count, sum(t1.Alive=1) as online, sum(t3.created_at >= '%s') as increase from VSSChannelTbl as t1 left join VSSDevTbl as t2 on t1.DevPubID=t2.DevPubID left join t_device as t3 on t3.sn=t1.DevPubID where t3.device_code=9 and t3.verify_status=1")
	array = []Result{}
	err = db.Raw(sql).Scan(&array).Error
	if err != nil {
		return ret, err
	}
	if len(array) > 0 {
		ret.Total += array[0].Count
		ret.Online += array[0].Online
		ret.Increase += array[0].Increase
	}
	ret.Offline = ret.Total - ret.Online

	return ret, nil
}


type DeviceStatisticsItem struct {
	TypeCode int32
	Total int64
	Online int64
}

func (p *TDevice) DeviceTypeStatistic(db *gorm.DB) ([]DeviceStatisticsItem, error) {
	sql := fmt.Sprintf("select device_code as type_code, sum(1) as total, sum(status=1) as online from t_device where verify_status=1 and device_code <> 9 group by device_code")
	array := []DeviceStatisticsItem{}
	err := db.Raw(sql).Scan(&array).Error
	if err != nil {
		return nil, err
	}

	vedioArray := []DeviceStatisticsItem{}
	sql = fmt.Sprintf("select t3.device_code as type_code, sum(1) as total, sum(t1.Alive=1) as online from VSSChannelTbl as t1 left join VSSDevTbl as t2 on t1.DevPubID=t2.DevPubID left join t_device as t3 on t3.sn=t1.DevPubID where t3.device_code=9 and t3.verify_status=1 group by t3.device_code")
	err = db.Raw(sql).Scan(&vedioArray).Error
	if err != nil {
		return nil, err
	}
	if len(vedioArray) > 0 {
		return append(array, vedioArray...), nil
	}

	return array, nil
}