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

type DustDeviceInfo struct {
	ID int64 `gorm:"column:ID;PRIMARY_KEY" json:"ID"`
	Code string `gorm:"column:Code" json:"code"`
	Name string `gorm:"column:Name" json:"name"`
	DeviceId string `gorm:"column:DeviceId" json:"deviceid"`
	DeviceModel string `gorm:"column:DeviceModel" json:"devicemodel"`
	DeviceName string `gorm:"column:DeviceName" json:"devicename"`
	Manufacturer string `gorm:"column:Manufacturer" json:"manufacturer"`
	Batch string `gorm:"column:Batch" json:"batch"`
	SN string `gorm:"column:SN" json:"sn"`
	SIM string `gorm:"column:SIM" json:"sim"`
	DeviceState int64 `gorm:"column:DeviceState" json:"devicestate"`
	Unit string `gorm:"column:Unit" json:"unit"`
	Person string `gorm:"column:Person" json:"person"`
	Phone string `gorm:"column:phone" json:"phone"`
	Remark string `gorm:"column:remark" json:"remark"`
	Longitude string `gorm:"column:Longitude" json:"longitude"`
	Latitude string `gorm:"column:Latitude" json:"latitude"`
	Url string `gorm:"column:Url" json:"url"`
	VerifyTime time.Time `gorm:"column:VerifyTime"`
	VerifyStatus int `gorm:"column:VerifyStatus"`
	Key string `gorm:"column:Key"`
	ProjectId int64 `gorm:"column:ProjectId"`
	ProviderId int64 `gorm:"column:ProviderId"`
	CreatedAt time.Time `gorm:"column:CreatedAt"`
}

const (
	DeviceTypeVedio = 9
	DeviceTypeDust = 5
	DeviceTypeVehicle = 2
	DeviceTypeAttendance = 3
	DeviceTypeTower = 1
	DeviceTypeLift = 4
)

func (DustDeviceInfo) TableName() string {
	return "db_smart_v2.DustDeviceInfo"
}

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

func (p *DustDeviceInfo) 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 *DustDeviceInfo) List(db *gorm.DB, where map[string]interface{}, page int) (list []DustDeviceInfo, 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 *DustDeviceInfo) All(db *gorm.DB) (list []DustDeviceInfo, err error) {

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

	return list, result.Error
}

type DustDeviceItem 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
	Manufacturer string
	Url string
	Batch string
	Person string
	Phone string
	Unit string
	Lon string
	Lat string
	Model string
	Comment string
}


func dustDeviceListSql(req DustDeviceListRequest)(string, string, []interface{}) {
	sql := fmt.Sprintf("select t1.ID as id, t1.DeviceModel as model, t1.Remark as comment, t1.Batch as batch, t1.Person as person, t1.Phone as phone, t1.Manufacturer as manufacturer, t1.Longitude as lon, t1.Latitude as lat, t1.Url as url, t1.Unit as unit, t1.Key as 'key', t1.DeviceName as name, t1.SN as sn, t1.VerifyTime as project_approve_time, "+
		"t1.CreatedAt as created_time, t1.DeviceState 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 DustDeviceInfo 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 DustDeviceInfo 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.SN like '%%%s%%' or t2.SafetyNo like '%%%s%%' or t2.Name like '%%%s%%')", req.Filter, req.Filter, req.Filter))
	}

	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 DustDeviceListRequest struct {
	ProviderId int64
	Filter string
	StatusFilter []int32
	IsAll bool
	CanDel bool
	Page int32
	ProjectId int64
}

func (p *DustDeviceInfo) DustDeviceList(db *gorm.DB, req DustDeviceListRequest) ([]DustDeviceItem, int64, error) {
	type ResultCount struct {
		Count int64
	}
	array := []ResultCount{}
	ret := []DustDeviceItem{}
	var err error

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


func GetNextNumberOfVedioNew(db *gorm.DB, prefix string) (int64, error) {
	sql1 := fmt.Sprintf("select right(DevPubID, 6)+1 as ret from VSSDevTbl t1 where not exists (select right(DevPubID, 6) from VSSDevTbl t2 where right(t2.DevPubID, 6)+0=right(t1.DevPubID, 6)+1 and  DevPubID like '%s%%') and DevPubID like '%s%%' order by ret limit 1 ;", prefix, prefix)

	sql2 := fmt.Sprintf("select right(DevPubID, 6)-1 as ret from VSSDevTbl t1 where not exists (select right(DevPubID, 6) from VSSDevTbl t2 where right(t2.DevPubID, 6)+0=right(t1.DevPubID, 6)-1 and  DevPubID like '%s%%') and DevPubID like '%s%%' order by ret desc limit 1 ;", prefix, prefix)
	type Result struct {
		Ret int64
	}
	array := []Result{}
	err := db.Raw(sql1).Scan(&array).Error
	if err != nil {
		return 0, err
	}
	if len(array) == 0 {
		return 1, nil
	}
	if array[0].Ret <= 999999 {
		return array[0].Ret, nil
	}
	array = []Result{}
	err = db.Raw(sql2).Scan(&array).Error
	if err != nil {
		return 0, err
	}
	if len(array) == 0 {
		return 0, nil
	}

	return array[0].Ret, nil
}


func GetNextNumberOfChannelNew(db *gorm.DB, prefix string) (int64, error) {
	sql1 := fmt.Sprintf("select right(ChanPubID, 6)+1 as ret from VSSChannelTbl t1 where not exists (select right(ChanPubID, 6) from VSSChannelTbl t2 where right(t2.ChanPubID, 6)+0=right(t1.ChanPubID, 6)+1 and  ChanPubID like '%s%%') and ChanPubID like '%s%%' order by ret limit 1 ;", prefix, prefix)

	sql2 := fmt.Sprintf("select right(ChanPubID, 6)-1 as ret from VSSChannelTbl t1 where not exists (select right(ChanPubID, 6) from VSSChannelTbl t2 where right(t2.ChanPubID, 6)+0=right(t1.ChanPubID, 6)-1 and  ChanPubID like '%s%%') and ChanPubID like '%s%%' order by ret desc limit 1 ;", prefix, prefix)
	type Result struct {
		Ret int64
	}
	array := []Result{}
	err := db.Raw(sql1).Scan(&array).Error
	if err != nil {
		return 0, errors.DataBaseError
	}
	if len(array) == 0 {
		return 1, nil
	}
	if array[0].Ret <= 999999 {
		return array[0].Ret, nil
	}
	array = []Result{}
	err = db.Raw(sql2).Scan(&array).Error
	if err != nil {
		return 0, err
	}
	if len(array) == 0 {
		return 0, nil
	}

	return array[0].Ret, nil

}

func GetNextNumberOfVedioOld(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 GetNextNumberOfChannelOld(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
}