smart-supplier-management/model/device.go

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

import (
	"github.com/tidwall/gjson"
	"smart-supplier-management/consts"
	"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"`
	ProjectApproveTime time.Time `json:"project_approve_time"`
}

const (
	DeviceTypeVedio = 9
)

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

}

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", 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{consts.DeviceStatusAddAuditted}
		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 as 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 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.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{consts.DeviceStatusAddAuditted}
		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
	DeviceName string
}


func listDelJobSql(req ListRawRequest)(string, string, []interface{}) {
	sql := "select t1.origin, t1.id, t1.status, t1.reason, t1.feedback, 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())
		ret[i].DeviceName = gjson.GetBytes([]byte(v.Origin), "name").String()
	}

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

func GetNextNumberOfVedio(db *gorm.DB, prefix string) (int64, error) {
	sql := fmt.Sprintf("select max(right(DevPubID, 6)+0) as max from VSSDevTbl where DevPubID like '%s%%'", prefix)
	type Result struct {
		Max int64
	}
	array := []Result{}
	err := db.Raw(sql).Scan(&array).Error
	if err != nil {
		return 0, err
	}
	if len(array) == 0 {
		return 1, nil
	}
	return array[0].Max + 1, nil
}

func GetNextNumberOfChannel(db *gorm.DB, prefix string) (int64, error) {
	sql := fmt.Sprintf("select max(right(ChanPubID, 6)+0) as max from VSSChannelTbl where ChanPubID like '%s%%'", prefix)
	type Result struct {
		Max int64
	}
	array := []Result{}
	err := db.Raw(sql).Scan(&array).Error
	if err != nil {
		return 0, err
	}
	if len(array) == 0 {
		return 1, nil
	}
	return array[0].Max + 1, nil
}