zs_iot_mqtt/smartdevice_mqtt/business/trigger/triggerService.go

package trigger

import (
	"fmt"
	"strconv"
	"time"

	"dashoo.cn/utils"
	. "dashoo.cn/utils/db"
	"github.com/go-xorm/xorm"
)

type TriggerService struct {
	ServiceBase
}

//出库管理子表
type MaterialCKDetail struct {
	Id             int       `xorm:"not null pk autoincr INT(11)"`
	CKNo           string    `xorm:"VARCHAR(50)"`      //出库单号
	ProjectId      int       `xorm:"INT(11)"`          //项目Id
	MaterialId     int       `xorm:"INT(11)"`          //物料Id
	Unit           string    `xorm:"VARCHAR(50)"`      //单位Name
	BatchNo        string    `xorm:"VARCHAR(50)"`      //批次号
	CKCount        float32   `xorm:"FLOAT"`            //数量
	CKWeight       float32   `xorm:"FLOAT"`            //重量
	UnitPrice      float32   `xorm:"FLOAT"`            //单价
	TotalPrice     float32   `xorm:"FLOAT"`            //总价
	Remark         string    `xorm:"TEXT"`             //备注
	CreateOn       time.Time `xorm:"DATETIME created"` //创建时间
	CreateUserId   int       `xorm:"INT(11)"`
	CreateBy       string    `xorm:"VARCHAR(50)"` //创建人
	ModifiedOn     time.Time `xorm:"DATETIME updated"`
	ModifiedUserId int       `xorm:"INT(11)"`
	ModifiedBy     string    `xorm:"VARCHAR(50)"`
}

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

//时间查询
func (s *TriggerService) GetEntitiesWithSearch(searchstring string) []Triggers {
	sql := `select a.Id,a.AccCode,a.Feedback,a.AID,a.FeedbackTime,d.SurplusCount, a.ValidityDate,a.Id,a.Notification,a.TimeNotification,a.RemindTime,a.KCMin,a.KCMax, b.SPara3,c.KCCount,a.ContentMode,a.Switchalarm from Triggers   a  
	inner join Actions b on a.AId=b.Id
	inner join accountinfo d on d.Id=2925
	inner join materialbatchkc c on a.MaterialbatchkcId=c.Id ` + searchstring
	fmt.Println(sql)
	list := make([]Triggers, 0)
	s.DBE.Sql(sql).Find(&list)
	return list
}

//类型更新
func (s *TriggerService) Update_oilwell_type(scalar, vector, id string) error {
	var err error

	sql := "UPDATE channels_dg_oilwell SET Scalar='" + scalar + "', Vector ='" + vector + "' WHERE id= " + id
	fmt.Println("-----sql---", sql)
	_, err = s.DBE.Query(sql)

	LogError(err)

	return err
}

//油水井id
func (s *TriggerService) Getysj_ID(searchstring string) int64 {
	var err error
	var total int64
	sql := `select count(*) from channels_dg_oilwell  where id = ` + searchstring
	resultsSlice, err := s.DBE.Query(sql)

	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
}

//油水井分解类型
func (s *TriggerService) Getoilwell_type() []Channels_dg_oilwell {
	sql := `select * from channels_dg_oilwell `
	fmt.Println(sql)
	list := make([]Channels_dg_oilwell, 0)
	s.DBE.Sql(sql).Find(&list)
	return list
}

//查询跟新开锁请求
func (s *TriggerService) UpdateEntitybyId(id int, isSend float64, CreateOn int64) (err error) {
	// isSends := strconv.FormatFloat(isSend, 'E', -1, 64)
	difference := time.Now().Unix() - CreateOn
	SendErrorMsg := strconv.FormatInt(difference, 10)
	str_time := time.Now().Format("2006-01-02 15:04:05")
	_, err = s.DBE.Exec(`update record_History set IsSend=  2,SendErrorMsg = ` + SendErrorMsg + `, ModifiedOn = "` + str_time + `"  where 
		id=` + strconv.Itoa(id) + ``)
	LogError(err)
	if err != nil {
		return err
	}
	return
}

//查询10秒内开锁请求
func (s *TriggerService) GetHistoryByChannelCode(searchstring string, delay int) []Record_History {
	//延迟
	str_time := time.Unix(time.Now().Unix()-int64(delay), 0).Format("2006-01-02 15:04:05")
	sql := `select * from record_history WHERE ChannelCode =  ` + searchstring + ` and CreateOn > '` + str_time + `'`
	fmt.Println(sql)
	list := make([]Record_History, 0)
	s.DBE.Sql(sql).Find(&list)
	return list
}

//名称查询 存储到redis
func (s *TriggerService) GetEntitiesTRredis(searchstring string) []Channels {
	sql := `select Title from channels  where Serial = ` + searchstring
	fmt.Println(sql)
	list := make([]Channels, 0)
	s.DBE.Sql(sql).Find(&list)
	return list
}

//经纬度查询
func (s *TriggerService) GetEntitiesWithSearchlatlug(searchstring string) []Channelslat {
	sql := `select Id,Unit,UnitSign from channels where Serial = ` + searchstring
	fmt.Println(sql)
	list := make([]Channelslat, 0)
	s.DBE.Sql(sql).Find(&list)
	return list
}

//库存数量计算
func (s *TriggerService) Getmaterialckdetail(searchstring string) []MaterialCKDetail {
	sql := `select  CKCount from materialckdetail where ` + searchstring
	fmt.Println(sql)
	list := make([]MaterialCKDetail, 0)
	s.DBE.Sql(sql).Find(&list)
	return list
}

//设备有效期
func (s *TriggerService) GetEntitiesWithSearchdevice(searchstring string) []Triggers {
	sql := `select a.Id, a.AccCode,a.Category,a.AId,d.SurplusCount,a.RemindTime,a.ValidityDate,a.ProductDate, a.FeedbackTime,b.SPara3,a.Switchalarm from Triggers  a  
	inner join accountinfo d on d.Id=2925
	inner join Actions b on a.AId=b.Id where a.Category = 3 ` + searchstring
	fmt.Println(sql)
	list := make([]Triggers, 0)
	s.DBE.Sql(sql).Find(&list)
	return list
}

//添加历史记录
func (s *TriggerService) GetINSERTchannels_dg_oilwell(id, code, name string, welltype int64, orgCode string) error {
	sql := "insert into channels_dg_oilwell(Id,WellCode,Well_Name,WellType,OrgCode,CreateOn) values(" + id + ",'" + code + "','" + name + "','" + strconv.FormatInt(welltype, 10) + "','" + orgCode + "',NOW())"

	_, err := s.DBE.Exec(sql)
	return err
}

//添加历史记录
func (s *TriggerService) GetINSERTHIStr(SendTo, ChannelName, Message, ChannelCode, ProjectSourse, ProjectAccount, ProjectAccountName, ActionType string, AlarmOn, IsSend, EventFiled, Value, Remark string) (id int64, err error) {
	sql := "insert into trigger_history(Id,CreateUserId,CreateBy,CreateOn,ChannelName,ChannelCode,ProjectSourse,ProjectAccount,ProjectAccountName,SendTo,Message,ActionType,IsRead,AlarmOn,IsSend,EventFiled,Value,Remark) values(Null,138,'seed平台',NOW(),'" + ChannelName + "','" + ChannelCode + "','" + ProjectSourse + "','" + ProjectAccount + "','" + ProjectAccountName + "','" + SendTo + "','" + Message + "','" + ActionType + "',0,'" + AlarmOn + "','" + IsSend + "','" + EventFiled + "','" + Value + "','" + Remark + "')"
	// sql := "insert into trigger_history(Id,SendTo,Message,ActionType,IsRead,AlarmOn,IsSend) values(Null,'" + SendTo + "','" + Message + "','" + ActionType + "',0,NOW(),'" + IsSend + "')"
	fmt.Println("----sql----", sql)
	lastid, err := s.DBE.Exec(sql)
	ids, _ := lastid.LastInsertId()
	return ids, err
}

//库存为0删除查询
func (s *TriggerService) DeletetrforId(searchstring string) []Triggers {
	sql := `delete from Triggers where Id =` + searchstring
	fmt.Println(sql)
	list := make([]Triggers, 0)
	s.DBE.Sql(sql).Find(&list)
	return list
}

func (s *TriggerService) UpdateEntityBytbl(tablename string, id interface{}, entity interface{}, cols []string) (err error) {
	session := s.DBE.NewSession()
	defer session.Close()
	err = session.Begin()
	LogError(err)
	if err != nil {
		session.Rollback()
		return err
	}
	_, err = s.DBE.Table(tablename).Id(id).Cols(cols...).Update(entity) //执行更新
	LogError(err)
	if err != nil {
		session.Rollback()
		return err
	}
	err = session.Commit()
	if err != nil {
		return err
	}
	return
}

func (s *TriggerService) GetPswById(userid int) (userinfo UserInfo) {
	sql := `select a.UserName,a.UserPassword,a.PublicKey from Base_User a where a.Id=` + utils.ToStr(userid)
	s.DBE.Sql(sql).Get(&userinfo)
	return userinfo
}

//取报警记录中两小时内的最后一条数据的报警类型
func (s *TriggerService) GetTriggerTypelastbytime(systime time.Time, minute, triggerid int) int {
	var triggertype Id_Int
	sql := `select TriggerType Id from Trigger_History a where
		a.CreateOn>DATE_ADD('%v', INTERVAL %v MINUTE)
		and a.TriggerId =%v
		order by a.CreateOn desc limit 1`
	sql = fmt.Sprintf(sql, utils.TimeFormat(systime, "2006-01-02 15:04:05"), minute, triggerid)
	s.DBE.Sql(sql).Get(&triggertype)
	return triggertype.Id
}

//取报警记录中最后一条数据的报警类型
func (s *TriggerService) GetTriggerTypelast(triggerid string, createtime time.Time) int {
	var triggertype Id_Int
	sql := `select TriggerType Id from Trigger_History a where
		a.TriggerId =` + triggerid + ` and CreateOn>'` + createtime.Format("2006-01-02 15:04:05") + `'
		order by a.CreateOn desc limit 1`
	s.DBE.Sql(sql).Get(&triggertype)
	return triggertype.Id
}