smart-supplier-management/model/vedio.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"
	"time"
	"smart-supplier-management/consts"
)

type Vssdevtbl struct {
	Id int64 `gorm:"column:ID;PRIMARY_KEY" json:"id" form:"id"`
	Devpubid string `gorm:"column:DevPubID" json:"devpubid" form:"devpubid"`
	Devnickname string `gorm:"column:DevNickname" json:"devnickname" form:"devnickname"`
	Username string `gorm:"column:UserName" json:"username" form:"username"`
	Passwd string `gorm:"column:Passwd" json:"passwd" form:"passwd"`
	Corpid string `gorm:"column:CorpID" json:"corpid" form:"corpid"`
	Ip string `gorm:"column:IP" json:"ip" form:"ip"`
	Port int64 `gorm:"column:Port" json:"port" form:"port"`
	Alive int64 `gorm:"column:Alive" json:"alive" form:"alive"`
	Subcatalogtime int64 `gorm:"column:SubCatalogTime" json:"subcatalogtime" form:"subcatalogtime"`
	Devmode string `gorm:"column:DevMode" json:"devmode" form:"devmode"`
	Mediatransport string `gorm:"column:MediaTransport" json:"mediatransport" form:"mediatransport"`
	Dmarker int64 `gorm:"column:DMarker" json:"dmarker" form:"dmarker"`
	UpdateTime time.Time `gorm:"column:updatetime" json:"updatetime" form:"updatetime"`
	ChannelCount int `gorm:"column:ChannelCount"`
	ProviderId int64 `gorm:"column:ProviderId"`
	ProjectId int64 `gorm:"column:ProjectId"`
	VerifyStatus int `gorm:"column:VerifyStatus"`
	VerifyTime time.Time `gorm:"column:VerifyTime"`
	CreatedAt time.Time `gorm:"column:CreatedAt"`
}

func (Vssdevtbl) TableName() string {
	return "VSSDevTbl"
}

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


func (p *Vssdevtbl) 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 *Vssdevtbl) 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 *Vssdevtbl) 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 *Vssdevtbl) FindSort(db *gorm.DB, where map[string]interface{}, sort string) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		return err
	}
	ps := []Vssdevtbl{}
	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 *Vssdevtbl) Save(db *gorm.DB) error {
	return db.Save(p).Error
}

func (p *Vssdevtbl) 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 *Vssdevtbl) List(db *gorm.DB, where map[string]interface{}, page int) (list []Vssdevtbl, err error) {
	offset := (page - 1) *PageSize
	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(offset).Find(&list)

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

	return list, result.Error

}

func (p *Vssdevtbl) UpdateChannelCount(db *gorm.DB, sn string, count int) error {
	sql := fmt.Sprintf("update VSSDevTbl set ChannelCount=ChannelCount+%d where DevPubID='%s'", count, sn)
	return db.Exec(sql).Error
}

type Vsschanneltbl struct {
	ID int64 `gorm:"column:ID;PRIMARY_KEY"`
	Devpubid string `gorm:"column:DevPubID"`
	Restype int64 `gorm:"column:ResType"`
	Nickname string `gorm:"column:Nickname"`
	Chanpubid string `gorm:"column:ChanPubID"`
	Alive int64 `gorm:"column:Alive"`
	Corpid string `gorm:"column:CorpID"`
	Model string `gorm:"column:Model"`
	Owner string `gorm:"column:Owner"`
	Civilcode string `gorm:"column:CivilCode"`
	Address string `gorm:"column:Address"`
	Parental int64 `gorm:"column:Parental"`
	Parentid string `gorm:"column:ParentId"`
	Ip string `gorm:"column:IP"`
	Port int64 `gorm:"column:Port"`
	Longitude float64 `gorm:"column:Longitude"`
	Latitude float64 `gorm:"column:Latitude"`
	Altitude float64 `gorm:"column:Altitude"`
	Ptztype int64 `gorm:"column:PTZType"`
	Roomtype int64 `gorm:"column:RoomType"`
	Directiontype int64 `gorm:"column:DirectionType"`
	Streamtype int64 `gorm:"column:StreamType"`
	Chanrtspurl string `gorm:"column:ChanRtspUrl"`
	Realrtspurl string `gorm:"column:RealRtspUrl"`
	Dmarker int64 `gorm:"column:DMarker"`
	Updatetime time.Time `gorm:"column:UpdateTime"`
}

func (Vsschanneltbl) TableName() string {
	return "VSSChannelTbl"
}

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


func (p *Vsschanneltbl) 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 *Vsschanneltbl) 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 *Vsschanneltbl) 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 *Vsschanneltbl) Save(db *gorm.DB) error {
	return db.Save(p).Error
}

func (p *Vsschanneltbl) 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 *Vsschanneltbl) List(db *gorm.DB, where map[string]interface{}, page int32) (list []Vsschanneltbl, err error) {
	offset := (int(page) - 1) *PageSize
	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(offset).Find(&list)

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

	return list, result.Error

}

func GetProjectIdByChannel(db *gorm.DB, id int64) (int64, error) {
	sql := fmt.Sprintf("select t2.project_id from VSSChannelTbl as t1 left join t_device as t2 on t1.DevPubID = t2.sn and t2.device_code=%d where t1.id=?", DeviceTypeVedio)
	type Result struct {
		ProjectId int64
	}
	array := []Result{}
	err := db.Raw(sql, id).Scan(&array).Error
	if err != nil {
		return 0, err
	}
	if len(array) == 0 {
		return 0, nil
	}
	return array[0].ProjectId, nil
}


type VedioDeviceItem 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 vedioDeviceListSql(req VedioDeviceListRequest)(string, string, []interface{}) {
	sql := fmt.Sprintf("select t1.ID as id, t1.DevNickName as name, t1.DevPubID as sn, t1.ProjectId as project_id, t1.VerifyTime as project_approve_time, t1.MediaTransport as media_transport, t1.IP as ip, t1.Port as port, t1.ChannelCount as channel_count, "+
		"t1.CreatedAt as created_time, t1.Alive as state, t1.VerifyStatus as status, t2.SafetyNo as safety_record_no, t2.Name as project_name, t3.Name as provider_name, t3.SocialCode as social_code from VSSDevTbl as t1 left join ProjectInfo as t2 on t1.ProjectId=t2.ID left join Provider as t3 on t1.ProviderId=t3.ID")
	countSql := fmt.Sprintf("select count(1) as count from VSSDevTbl as t1 left join ProjectInfo as t2 on t1.ProjectId=t2.ID ")
	args := []interface{}{}
	whereArray := []string{}
	if req.ProviderId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.ProviderId=%d", req.ProviderId))
	}

	if req.Filter != "" {
		whereArray = append(whereArray, fmt.Sprintf("(t1.DevPubID like '%%%s%%' or t2.SafetyNo like '%%%s%%' or t2.Name like '%%%s%%')", req.Filter, req.Filter, req.Filter))
	}

	if req.ProjectId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t1.ProjectId = %d", req.ProjectId))
	}
	if req.CanDel {
		req.IsAll = true
		req.StatusFilter = []int32{consts.DeviceStatusAddAuditted}
		whereArray = append(whereArray, fmt.Sprintf("(select count(1) from DeviceDelJob where DeviceId=t1.ID and (Status = 0 or Status = 1)) = 0"))
	}
	if len(req.StatusFilter) > 0 {
		args = append(args, req.StatusFilter)
		whereArray = append(whereArray, fmt.Sprintf("t1.VerifyStatus 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.CreatedAt desc", sql, where)
		countSql = fmt.Sprintf("%s %s", countSql, where)
	} else {
		sql = fmt.Sprintf("%s %s order by t1.CreatedAt desc limit %d offset %d", sql, where, PageSize, offset)
		countSql = fmt.Sprintf("%s %s", countSql, where)
	}
	return sql, countSql, args
}


type VedioDeviceListRequest struct {
	ProviderId int64
	Filter string
	StatusFilter []int32
	IsAll bool
	CanDel bool
	Page int32
	ProjectId int64
}

func  VedioDeviceList(db *gorm.DB, req VedioDeviceListRequest) ([]VedioDeviceItem, int64, error) {
	type ResultCount struct {
		Count int64
	}
	array := []ResultCount{}
	ret := []VedioDeviceItem{}
	var err error

	sql, countSql, args := vedioDeviceListSql(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
}