labsop_mcs/backend/src/dashoo.cn/mcs_common/business/device/deviceService.go

package device

import (
	"fmt"
	"strconv"

	"xorm.io/xorm"

	"dashoo.cn/base_common/utils"
	. "dashoo.cn/base_common/utils/db"
	"dashoo.cn/mcs_common/business/equipment"
	"dashoo.cn/mcs_common/business/organize"
)

type DeviceService struct {
	ServiceBase
}

func GetDeviceService(xormEngine *xorm.Engine) *DeviceService {
	s := new(DeviceService)
	s.DBE = xormEngine
	return s
}

// 新增
func (s *DeviceService) GetChannelids(userid string) (ids []string) {
	var idList []Id_Str

	err := s.DBE.SQL(`select Id FROM Device where CreateUserId= ` + userid).Find(&idList)
	LogError(err)
	ids = s.GetIdsFromId_StrList(idList)
	if len(ids) == 0 {
		ids = append(ids, "-1")
	}
	return
}

func (s *DeviceService) GetEntitiesWithOrderSearch(order, searchstring string) []Device {

	sql := `select a.Id,a.Title,a.Serial,a.About,a.Local,a.Latitude,a.Longitude from Device a
		where ` + searchstring + ` order by ` + order

	List := make([]Device, 0)
	utils.DBE.SQL(sql).Find(&List)

	return List
}
func (s *DeviceService) GetEntitiesbyVisitLevel(index int) (devices []Device) {
	s.DBE.Where("Visitlevel=?", index).Find(&devices)
	return
}
func (s *DeviceService) GetEntitiesbyiddtype(id, dtype int) (devices []Device) {
	s.DBE.Where("Createuserid=? and Dtype=?", utils.ToStr(id), dtype).Find(&devices)
	return
}
func (s *DeviceService) GetEntitiesbyuid(uid string) (devices []Device) {
	s.DBE.Where("Createuserid=?", uid).Find(&devices)
	return
}

func (s *DeviceService) GetChannelsListOrder(pageIndex, itemsPerPage int64, order string, searchstring, Uid string) (int64, []DeviceChannels) {
	var err error
	var total int64
	if Uid == "" {
		Uid = "0"
	}
	//获取总记录数
	sqlCount := "    select  count(*)  from Channels a left join Device b on a.DId=b.Id left join Base_User d on a.CreateUserId=d.Id where " + searchstring + ""
	var sql, limitstr string = "", ""
	if pageIndex > 0 {
		limitstr = ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}
	sql = `select a.Id, a.EquipMentId, a.Code,a.Title,a.Tags,a.About,a.Local,a.Serial,a.TagCode,b.Code BCode,b.Title BTitle,a.DeviceState,a.DataItem,a.Description  from Channels a left join Device b on a.DId=b.Id
		left join ChannelsSort c on a.Id=c.ChannelId and c.UserId=` + Uid + `
		left join Base_User d on a.CreateUserId=d.Id
		where ` + searchstring + ` order by ` + order + limitstr
	List := make([]DeviceChannels, 0)
	utils.DBE.SQL(sql).Find(&List)
	resultsSlice, err := s.DBE.Query(sqlCount)
	LogError(err)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			LogError(err)
			break
		}
	}
	return total, List
}

func (s *DeviceService) GetChannelsList(pageIndex, itemsPerPage int64, searchstring, Uid string) (int64, []DeviceChannels) {
	var err error
	var total int64
	if Uid == "" {
		Uid = "0"
	}
	//获取总记录数
	sqlCount := "    select  count(*)  from Channels a  where " + searchstring + ""
	var sql, limitstr string = "", ""
	if pageIndex > 0 {
		limitstr = ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}
	sql = `select a.*  from Channels a 
		left join EquipMentSort b on a.EquipMentId = b.EquipMentlId and b.CreateUserId=` + Uid + `
		where ` + searchstring + ` order by b.SortCode,a.Id desc ` + limitstr
	List := make([]DeviceChannels, 0)
	utils.DBE.SQL(sql).Find(&List)
	resultsSlice, err := s.DBE.Query(sqlCount)
	LogError(err)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			LogError(err)
			break
		}
	}
	return total, List
}

func (s *DeviceService) GetIdByCode(code string) int {
	var channel Channels
	s.DBE.Where("Code=?", code).Get(&channel)
	return channel.Id
}
func (s *DeviceService) GetBlackBoxIdByCode(code string) int {
	var channel Channels
	s.DBE.Where("Code=?", code).Get(&channel) //都得到的是整条语句
	return channel.DId                        //取其中一个字段
}
func (s *DeviceService) GetDidByCode(code string) int {
	var channel Channels
	s.DBE.Where("Code=?", code).Get(&channel)
	return channel.DId
}
func (s *DeviceService) GetIdByDId(did int) int {
	var channel Channels
	s.DBE.Where("DId=?", did).Get(&channel)
	return channel.Id
}

func (s *DeviceService) GetWDidByDid(id int) int {
	var device Device
	s.DBE.Where("Id=?", id).Get(&device)
	return device.Wdid
}
func (s *DeviceService) GetTitleByCode(code string) string {
	var channel Channels
	s.DBE.Where("Code=?", code).Get(&channel)
	return channel.Title
}
func (s *DeviceService) GetDataItemByCode(code string) int {
	var channel Channels
	s.DBE.Where("Code=?", code).Get(&channel)
	return channel.DataItem
}

func (s *DeviceService) Delete(id string) (err error) {
	var entity Device
	err = s.DeleteEntityById(id, &entity)

	if err == nil {
		Idint, _ := utils.StrTo(id).Int()
		var detail Channels = Channels{DId: Idint}
		s.DeleteEntity(&detail)
	}
	return
}
func (s *DeviceService) GetDeviceChannel(did int, channels interface{}) {
	s.DBE.Where("DId=?", did).Find(channels)
}
func (s *DeviceService) GetDeviceTreeByRole(roleid, permissionitem string) (utree []DeviceShowr) {

	s.DBE.SQL(`select distinct a.TargetId  Id,c.Title FullName
			from Base_PermissionScope a
			left join Device c on a.TargetId=c.Id
			where a.ResourceCategory='Base_Role'
			and a.ResourceId =` + roleid + `
			and a.TargetCategory='Base_User' and a.PermissionId=` + permissionitem).Find(&utree)
	return
}

//验证设备是否存在
func (s *DeviceService) VerifyDevice(did int) bool {
	var has bool
	var devices Device
	s.DBE.Where(`WDid=?`, utils.ToStr(did)).Get(&devices)
	if devices.Id == 0 {
		has = false
	} else {
		has = true
	}
	return has
}

//验证传感器是否存在
func (s *DeviceService) VerifyChannel(code string) bool {
	var has bool
	var channel Channels
	s.DBE.Where(`Code=?`, code).Get(&channel)
	if channel.Id == 0 {
		has = false
	} else {
		has = true
	}
	return has
}

//根据序列号获取设备id
func (s *DeviceService) GetDIdBySerial(serial string) (did int) {
	var devices Device
	s.DBE.Where(`Serial=?`, serial).Get(&devices)
	return devices.Id
}

func (s *DeviceService) GetDTitleBySerial(serial string) string {
	var devices Device
	s.DBE.Where(`Serial=?`, serial).Get(&devices)
	return devices.Title
}

//根据Code获取WDid
func (s *DeviceService) GetWdidByCode(code string) int {
	var devices Device
	sql := `select a.WDid from Device a left join Channels b on a.Id=b.DId where b.Code='` + code + `'`
	s.DBE.SQL(sql).Get(&devices)
	return devices.Wdid
}

func (s *DeviceService) GetChannelsCount(searchstring string) int64 {
	channel := new(Channels)
	total, _ := s.DBE.Where(searchstring).Count(channel)
	return total
}
func (s *DeviceService) GetCIds(searchstring string) string {
	if searchstring == "" {
		searchstring = "1=1"
	}
	sql := `select Code from Channels
		where ` + searchstring
	var cids = "-1"
	List := make([]Channels, 0)
	s.DBE.SQL(sql).Find(&List)
	for i, j := 0, len(List); i < j; i++ {
		cids += "," + utils.ToStr(List[i].Code)
	}
	return cids
}

func (s *DeviceService) GetEntitieByCode(code string) *Channels {
	entity := new(Channels)
	s.DBE.Where("Code='" + code + "'").Get(entity)
	return entity
}

func (s *DeviceService) GetDeviceAlertor(code, alerttype, binddatatype string) *DeviceAlertor {
	entity := new(DeviceAlertor)
	sql := "select a.Id,a.Code,a.Title,a.Serial,a.`Local`,a.Latitude,a.Longitude,a.DataItem,c.Code Ccode,group_concat(b.DataItem) AlertItem" + `
		from Device a
		inner join Channels b on a.Id=b.DId and b.DataItem in (` + alerttype + `)
		inner join Channels c on a.Id=c.DId and c.DataItem=` + binddatatype + `
		where a.DataItem=4 and b.Code='` + code + `' 
		group by a.Id`
	s.DBE.SQL(sql).Get(entity)
	return entity
}

func (s *DeviceService) GetCode(searchstring, Uid string) string {
	if searchstring == "" {
		searchstring = "1=1"
	}
	sql := `select a.Code from Channels a left join Device b on a.DId=b.Id
		left join ChannelsSort c on a.Id=c.ChannelId and c.UserId=` + Uid + `
		left join Base_User d on a.CreateUserId=d.Id
		where ` + searchstring
	var cids = "-1"
	List := make([]DeviceChannels, 0)
	s.DBE.SQL(sql).Find(&List)
	for i, j := 0, len(List); i < j; i++ {
		cids += "," + utils.ToStr(List[i].Code)
	}
	return cids
}

//根据条件取列表（不分页）
func (s *DeviceService) Getlist(order string, where, Uid string) []DeviceChannels {
	sql := `select a.Id, a.Code,a.Title,a.Tags,a.About,b.Local,a.Serial,a.TagCode,b.Code BCode,b.Title BTitle,a.DeviceState,a.DataItem  from Channels a left join Device b on a.DId=b.Id
		left join ChannelsSort c on a.Id=c.ChannelId and c.UserId=` + Uid + `
		left join Base_User d on a.CreateUserId=d.Id
		where ` + where + ` order by ` + order

	List := make([]DeviceChannels, 0)
	utils.DBE.SQL(sql).Find(&List)

	return List
}

//查询异常数据（sensor查询工具也用这个方法）
func (s *DeviceService) GetAbnormal(pageIndex, itemsPerPage int64, order string, searchstring, Uid string) (int64, []Abnormal) {
	var err error
	var total int64
	if Uid == "" {
		Uid = "0"
	}
	//获取总记录数
	sqlCount := "    select  count(*)  from Channels a left join Base_User b on a.CreateUserId=b.Id	left join Device c on a.DId=c.Id left join Base_Company  d on d.Id=b.AccCode where " + searchstring + ""
	var sql, limitstr string = "", ""
	if pageIndex > 0 {
		limitstr = ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}
	sql = "select a.Title,a.Serial,a.Tags,a.ChannelState,a.DataItem,a.DeviceState,a.CJTime,a.CSTime,a.CreateOn BindingTime,b.Realname,b.UserName,b.Mobile,b.Telephone,c.`Local`  from Channels a left join Base_User b on a.CreateUserId=b.Id	left join Device c on a.DId=c.Id left join Base_Company  d on d.Id=b.AccCode where " + searchstring + " order by " + order + limitstr
	List := make([]Abnormal, 0)
	utils.DBE.SQL(sql).Find(&List)
	resultsSlice, err := s.DBE.Query(sqlCount)
	LogError(err)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			LogError(err)
			break
		}
	}
	return total, List
}

//BB查询工具
func (s *DeviceService) GetBlackboxquery(pageIndex, itemsPerPage int64, order string, searchstring, Uid string) (int64, []Blackboxquery) {
	var err error
	var total int64
	if Uid == "" {
		Uid = "0"
	}
	//获取总记录数
	sqlCount := "    select  count(*) from Channels a left join Base_User b on a.CreateUserId=b.Id left join Base_Company  d on d.Id=b.AccCode where " + searchstring + ""
	var sql, limitstr string = "", ""
	if pageIndex > 0 {
		limitstr = ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}
	sql = "select a.Title,a.Serial,a.ChannelState,a.CreateOn BindingTime,b.Realname,b.UserName from Channels a left join Base_User b on a.CreateUserId=b.Id left join Base_Company  d on d.Id=b.AccCode where " + searchstring + " order by " + order + limitstr
	List := make([]Blackboxquery, 0)
	utils.DBE.SQL(sql).Find(&List)
	resultsSlice, err := s.DBE.Query(sqlCount)
	LogError(err)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			LogError(err)
			break
		}
	}
	return total, List
}

func (s *DeviceService) GetDeviceModel(order, searchstring string) []DeviceModel {
	sql := `select Id,DItem,Brands,DModel,Structure,Photo from DeviceModel
		where ` + searchstring + ` order by ` + order

	List := make([]DeviceModel, 0)
	utils.DBE.SQL(sql).Find(&List)
	return List
}

func (s *DeviceService) GetDeviceModelDItem(searchstring string) []DeviceModel {
	sql := `select distinct DItem from DeviceModel
		where ` + searchstring + ` order by DItem`
	List := make([]DeviceModel, 0)
	utils.DBE.SQL(sql).Find(&List)
	return List
}

func (s *DeviceService) GetDeviceModelBrands(searchstring string) []DeviceModel {
	sql := `select distinct Brands from DeviceModel
		where ` + searchstring + ` order by Brands`
	List := make([]DeviceModel, 0)
	utils.DBE.SQL(sql).Find(&List)
	return List
}

func (s *DeviceService) GetDeviceModelDModel(searchstring string) []DeviceModel {
	sql := `select distinct DModel,Photo,Id from DeviceModel
		where ` + searchstring + ` order by DModel`
	List := make([]DeviceModel, 0)
	utils.DBE.SQL(sql).Find(&List)
	return List
}

func (s *DeviceService) GetDeviceModelStructure(searchstring string) []DeviceModel {
	sql := `select distinct Structure from DeviceModel
		where ` + searchstring + ` order by Structure`
	List := make([]DeviceModel, 0)
	utils.DBE.SQL(sql).Find(&List)
	return List
}

func (s *DeviceService) GetDeviceModelPhoto(searchstring string) []DeviceModel {
	sql := `select distinct Photo from DeviceModel
		where ` + searchstring + ` order by Photo`
	List := make([]DeviceModel, 0)
	utils.DBE.SQL(sql).Find(&List)
	return List
}
func (s *DeviceService) GetChannelsTitles(searchstring string) string {
	type TitleStr struct {
		Title string
	}
	var entity TitleStr
	sql := `select group_concat(Title) Title from Channels
		where Code in (` + searchstring + `)`
	utils.DBE.SQL(sql).Get(&entity)
	return entity.Title
}

func (s *DeviceService) GetTree_Org(whereorg string) (tree []organize.Base_Organizetree) {
	s.DBE.SQL(`select Id, ParentId,Fullname, '/static/img/1_open.png' Icon from Base_Organize
		where ` + whereorg).Find(&tree)
	return

}

func (s *DeviceService) GetTree_OrgAndDevice(whereorg, searchstring, uid string, isoprnorg bool) (tree []organize.Base_Organizetree) {
	sql := ""
	if isoprnorg {
		sql = `select Id, ParentId,Fullname, '' Icon,'-1' Fu from Base_Organize
		where ` + whereorg + ` union all
		(select a.Id Id, a.OrganizeId ParentId, a.Title Fullname, '' Icon,'-2' Fu from Channels a left join Device b on a.DId=b.Id
		left join ChannelsSort c on a.Id=c.ChannelId and c.UserId=` + uid + `
		left join Base_User d on a.CreateUserId=d.Id  
		where ` + searchstring + " order by c.SortCode,a.CreateOn desc)"
	} else {
		sql = `select a.Id Id, a.OrganizeId ParentId, a.Title Fullname, '' Icon,'-2' Fu from Channels a left join Device b on a.DId=b.Id
		left join ChannelsSort c on a.Id=c.ChannelId and c.UserId=` + uid + `
		left join Base_User d on a.CreateUserId=d.Id  
		where ` + searchstring + " order by c.SortCode,a.CreateOn desc"
	}
	s.DBE.SQL(sql).Find(&tree)
	return
}

func (s *DeviceService) GetDistinctLocal(uid int) (list []Channels) {
	sql := fmt.Sprintf("select distinct Local,Latitude,Longitude from Channels where CreateUserId=%v and Local<>'' and Local is not null order by CreateOn desc", uid)
	s.DBE.SQL(sql).Find(&list)
	return
}

func (s *DeviceService) GetChannelSerialStrS(searchstring string) string {
	var ids Id_Str
	sql := `select group_concat(Serial) Id from Channels a 
		left join Base_User b on a.CreateUserId=b.Id
		where ` + searchstring
	utils.DBE.SQL(sql).Get(&ids)
	return ids.Id
}

func (s *DeviceService) UpdateChannelBySet(did int) {
	if did == 0 {

	} else {
		sql := fmt.Sprintf(`update  Channels set DId=0 
		where DataItem=0 and Did=%v`, did)
		utils.DBE.Exec(sql)
	}

}

func (s *DeviceService) GetAllChannelsByOrgTopId(topids []string, uid, ditem string) (clist []Channels) {
	svc := organize.GetOrganizeService(s.DBE)
	orgidstrs := "-1"
	for i, v := range topids {
		if i != 0 { // 新增,略去不必要数据
			if v != "" {
				orgidstrstmp := svc.GetAllChildByTopId(v, uid)
				if orgidstrstmp != "" {
					orgidstrs += "," + orgidstrstmp
				}
			}
		}
	}
	svc.GetEntities(&clist, "OrganizeId in ("+orgidstrs+") or DataItem="+ditem+" and CreateUserId="+uid)
	// fmt.Println("----------------------------orgidstrsorgidstrs---", orgidstrs)
	return
}

// 根据设备ID获取传感器序列号
func (s *DeviceService) GetSerialsByEquipids(equipids string) string {
	var model Id_Str
	if equipids == "" {
		equipids = "-1"
	}
	sql := `select  group_concat(Serial) Id from Channels where EquipMentId in (` + equipids + `)`
	s.DBE.SQL(sql).Get(&model)
	return model.Id
}

// 根据设备ID获取传感器Code
func (s *DeviceService) GetCodesByEquipids(equipids string) string {
	var model Id_Str
	if equipids == "" {
		equipids = "-1"
	}
	sql := `select  group_concat(Code) Id from Channels where EquipMentId in (` + equipids + `)`
	s.DBE.SQL(sql).Get(&model)
	return model.Id
}

// 根据ID获取传感器序列号
func (s *DeviceService) GetSerialsByids(ids string) string {
	var model Id_Str
	if ids == "" {
		ids = "-1"
	}
	sql := `select  group_concat(Serial) Id from Channels where Id in (` + ids + `)`
	s.DBE.SQL(sql).Get(&model)
	return model.Id
}

// 根据ID获取传感器Code
func (s *DeviceService) GetCodeByids(ids string) string {
	var model Id_Str
	if ids == "" {
		ids = "-1"
	}
	sql := `select  group_concat(Code) Id from Channels where Id in (` + ids + `)`
	s.DBE.SQL(sql).Get(&model)
	return model.Id
}

// 根据组织id获取所有设备id
func (s *DeviceService) GetAllIdsByOrgTopId(topids []string, uid string) (ids []ChannelsIdtitle) {
	svc := organize.GetOrganizeService(s.DBE)
	orgidstrs := "-1"
	for _, v := range topids {
		if v != "" {
			orgidstrstmp := svc.GetAllChildByTopId(v, uid)
			if orgidstrstmp != "" {
				orgidstrs += "," + orgidstrstmp
			}
		}
	}
	svcequi := equipment.GetEquipmentService(s.DBE)
	eids := svcequi.GetEquipmentidsByUid(uid)
	where := " (CreateUserId = " + uid + " or Id in (" + eids + "))"

	sql := `select a.Id,a.Title from Channels a left join
		EquipMentSort b on a.EquipMentId = b.EquipMentlId and b.UserId=` + uid + `
		where a.EquipMentId in 
		(select Id from EquipMent where OrganizeId in (` + orgidstrs + ") and " + where + ") order by b.SortCode,a.Id desc"
	s.DBE.SQL(sql).Find(&ids)
	return
}

// 根据组织id和设备类型获取所有设备id
func (s *DeviceService) GetAllSerialsByOrgTopIdDitem(topids []string, item, uid string) (ids []ChannelsSerialtitle) {
	svc := organize.GetOrganizeService(s.DBE)
	svcequi := equipment.GetEquipmentService(s.DBE)
	eids := svcequi.GetEquipmentidsByUid(uid)
	where := " (a.CreateUserId = " + uid + " or a.Id in (" + eids + "))"
	if len(topids) > 0 {
		orgidstrs := "-1"
		for _, v := range topids {
			if v != "" {
				orgidstrstmp := svc.GetAllChildByTopId(v, uid)
				if orgidstrstmp != "" {
					orgidstrs += "," + orgidstrstmp
				}
			}
		}
		where += " and a.OrganizeId in (" + orgidstrs + ")"
	}
	if item != "" && item != "-1" && item != "0" {
		where += " and b.DItem =" + item
	}

	sql := `select a.Serial,a.Title from Channels a left join
		EquipMentSort b on a.EquipMentId = b.EquipMentlId and b.UserId=` + uid + `
		where a.EquipMentId in 
		(select a.Id from EquipMent a
		inner join EquipMentModel b on a.ModelId=b.Id where ` + where + ` ) 
		order by b.SortCode,a.Id desc`
	s.DBE.SQL(sql).Find(&ids)
	return
}