kpt-pasture/module/crontab/sql.go

package crontab

import (
	"errors"
	"fmt"
	"kpt-pasture/model"
	"kpt-pasture/util"
	"time"

	pasturePb "gitee.com/xuyiping_admin/go_proto/proto/go/backend/cow"
	"gitee.com/xuyiping_admin/pkg/logger/zaplog"
	"gitee.com/xuyiping_admin/pkg/xerr"
	"go.uber.org/zap"
	"gorm.io/gorm"
)

func (e *Entry) FindPastureList() []*model.AppPastureList {
	res := make([]*model.AppPastureList, 0)
	if err := e.DB.Model(new(model.AppPastureList)).
		Where("is_show = ?", pasturePb.IsShow_Ok).
		Find(&res).Error; err != nil {
		zaplog.Error("FindPastureList error", zap.Any("err", err))
		return res
	}
	return res
}

func (e *Entry) GetCowById(cowId int64) (*model.Cow, error) {
	cowInfo := &model.Cow{}
	if err := e.DB.Model(new(model.Cow)).
		Where("id = ?", cowId).
		Where("admission_status = ?", pasturePb.AdmissionStatus_Admission).
		First(cowInfo).Error; err != nil {
		return nil, err
	}
	return cowInfo, nil
}
func (e *Entry) GetPenMapList(pastureId int64) (map[int32]*model.Pen, error) {
	penList := make([]*model.Pen, 0)
	if err := e.DB.Model(new(model.Pen)).
		Where("pasture_id = ?", pastureId).
		Where("is_delete = ?", pasturePb.IsShow_Ok).
		Find(&penList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}
	penMap := make(map[int32]*model.Pen)
	for _, v := range penList {
		penMap[v.Id] = v
	}
	return penMap, nil
}

// GetBeforeThreeDaysCowEstrus 获取值得时间之前三天内最大发情记录
func (e *Entry) GetBeforeThreeDaysCowEstrus(cowId int64, activeTime string) *model.NeckRingEstrus {
	neckRingEstrus := &model.NeckRingEstrus{}
	if err := e.DB.Model(new(model.NeckRingEstrus)).
		Select("MAX(max_high) as max_high,cow_id,MAX(day_high) as day_high").
		Select("MAX(IF(check_result=1,3,check_result)) AS check_result").
		Where("cow_id = ?", cowId).
		Where("active_time >= ?", activeTime).
		First(neckRingEstrus).Error; err != nil {
		return neckRingEstrus
	}
	return neckRingEstrus
}

// GetTwoEstrus 判断最近50天内是否存在发情记录（发情等级>=2）,如果18～25天@xadjust21，如果36～50天@xadjust42
func (e *Entry) GetTwoEstrus(pastureId, cowId int64, startActiveTime, endActiveTime string) *CowEstrus {
	newCowEstrus := &CowEstrus{}
	if err := e.DB.Model(new(model.NeckRingEstrus)).
		Select("cow_id,MAX(active_time) as active_date").
		Where("cow_id = ?", cowId).
		Where("pasture_id = ?", pastureId).
		Where("active_time BETWEEN ? AND ?", startActiveTime, endActiveTime).
		Where("active_level >= ?", pasturePb.EstrusLevel_Middle).
		First(newCowEstrus).Error; err != nil {
		return newCowEstrus
	}
	return newCowEstrus
}

func (e *Entry) FindCowInfoByCowId(cowId int64) *model.Cow {
	res := &model.Cow{}
	if err := e.DB.Model(new(model.Cow)).
		Where("id = ?", cowId).
		Where("admission_status = ?", pasturePb.AdmissionStatus_Admission).
		First(res).Error; err != nil {
		zaplog.Error("FindCowInfoByCowId", zap.Any("cowId", cowId), zap.Any("err", err))
		return nil
	}
	return res
}

func (e *Entry) IsExistEventEstrus(pastureId, cowId int64) *model.EventEstrus {
	res := &model.EventEstrus{}
	if err := e.DB.Model(new(model.EventEstrus)).
		Where("cow_id = ?", cowId).
		Where("pasture_id = ?", pastureId).
		Where("expose_estrus_type = ?", pasturePb.ExposeEstrusType_Neck_Ring).
		Where("is_show = ?", pasturePb.IsShow_Ok).
		Order("id DESC").
		First(res).Error; err != nil {
		return nil
	}
	return res
}

func (e *Entry) IsExistNeckActiveHabit(pastureId int64, neckRingNumber, heatDate string, frameId int32) (*model.NeckActiveHabit, int64) {
	count := int64(0)
	neckActiveHabit := &model.NeckActiveHabit{}
	if err := e.DB.Model(new(model.NeckActiveHabit)).
		Where("pasture_id = ?", pastureId).
		Where("neck_ring_number = ?", neckRingNumber).
		Where("heat_date = ?", heatDate).
		Where("frameid = ?", frameId).
		Count(&count).First(neckActiveHabit).Error; err != nil {
		return nil, 0
	}
	return neckActiveHabit, count
}

func (e *Entry) FindAppPastureReceiver() map[string]int64 {
	appPastureReceiverList := make([]*model.AppPastureReceiver, 0)
	if err := e.DB.Model(new(model.AppPastureReceiver)).
		Find(&appPastureReceiverList).Error; err != nil {
		zaplog.Error("FindAppPastureReceiver", zap.Any("err", err))
	}
	receiverMap := make(map[string]int64)
	for _, v := range appPastureReceiverList {
		receiverMap[v.ReceiverNumber] = v.PastureId
	}
	return receiverMap
}

func (e *Entry) FindSystemNeckRingConfigure(pastureId int64) ([]*model.NeckRingConfigure, error) {
	res := make([]*model.NeckRingConfigure, 0)
	if err := e.DB.Model(new(model.NeckRingConfigure)).
		Where("pasture_id = ?", pastureId).
		Where("is_show = ?", pasturePb.IsShow_Ok).
		Find(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}
	return res, nil
}

func (e *Entry) GetSystemNeckRingConfigure(pastureId int64, name string) (*model.NeckRingConfigure, error) {
	res := &model.NeckRingConfigure{}
	if err := e.DB.Model(new(model.NeckRingConfigure)).
		Where("pasture_id = ?", pastureId).
		Where("is_show = ?", pasturePb.IsShow_Ok).
		Where("name = ?", name).
		First(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}
	return res, nil
}

func (e *Entry) GetCowInfoByNeckRingNumber(pastureId int64, neckRingNumber string) *model.Cow {
	res := &model.Cow{}
	if err := e.DB.Model(new(model.Cow)).
		Where("pasture_id = ?", pastureId).
		Where("neck_ring_number = ?", neckRingNumber).
		Where("admission_status = ?", pasturePb.AdmissionStatus_Admission).
		First(res).Error; err != nil {
		return nil
	}
	return res
}

// GetMinIdByHeatDate 获取最小的id
func (e *Entry) GetMinIdByHeatDate(heatDate string, defaultId int64) (int64, error) {
	xMinId := struct {
		Id int64
	}{}
	if err := e.DB.Model(new(model.NeckActiveHabit)).
		Select("MIN(id) as id").
		Where("heat_date = ?", heatDate).
		First(&xMinId).Error; err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			xMinId.Id = defaultId
		} else {
			return 0, xerr.WithStack(err)
		}
	}
	return xMinId.Id, nil
}

func (e *Entry) FindFilterData(pastureId int64, neckRingNumber, heatDate string, frameId int32) *FilterData {
	firstFilterData := &FilterData{}
	if err := e.DB.Model(new(model.NeckActiveHabit)).
		Select("neck_ring_number", "filter_high", "filter_rumina", "filter_chew", "change_filter", "rumina_filter", "chew_filter").
		Where("neck_ring_number = ?", neckRingNumber).
		Where("heat_date = ?", heatDate).
		Where("frameid = ?", frameId).
		Where("pasture_id = ?", pastureId).
		First(firstFilterData).Error; err != nil {
		zaplog.Error("FirstFilterUpdate", zap.Any("err", err), zap.Any("NeckRingNumber", neckRingNumber), zap.Any("heatDate", heatDate), zap.Any("frameId", frameId))
	}
	return firstFilterData
}

func (e *Entry) FindWeekHabitData(pastureId int64, neckRingNumber, heatDate string, frameid int32, xToday *XToday) *WeekHabit {
	beginDayDate, _ := util.TimeParseLocal(model.LayoutDate2, heatDate)
	before7DayDate := beginDayDate.AddDate(0, 0, -7).Format(model.LayoutDate2)
	before1DayDate := beginDayDate.AddDate(0, 0, -1).Format(model.LayoutDate2)
	weekHabitData := &WeekHabit{}
	if err := e.DB.Model(new(model.NeckActiveHabit)).
		Select(
			"neck_ring_number",
			"IF(COUNT(1)>=3, ROUND((SUM(filter_high) -MIN(filter_high) -MAX(filter_high))/ABS(COUNT(1) -2),0), -1) as high_habit",
			"IF(COUNT(1)>=3, ROUND((SUM(filter_rumina) -MIN(filter_rumina) -MAX(filter_rumina))/ABS(COUNT(1) -2),0), -1) as rumina_habit",
			"IF(COUNT(1)>=3, ROUND((SUM(filter_chew) -MIN(filter_chew) -MAX(filter_chew))/ABS(COUNT(1) -2),0), -1) as chew_habit",
			"ROUND(AVG(intake),0) as intake_habit",
			"ROUND(AVG(inactive),0) as inactive_habit",
		).Where("pasture_id = ?", pastureId).
		Where("heat_date BETWEEN ? AND ?", before7DayDate, before1DayDate).
		Where("neck_ring_number = ? ", neckRingNumber).
		Where("frameid = ?", frameid).
		Where("cow_id > ?", 0).
		Where(e.DB.Where("high > ?", xToday.High).Or("rumina >= ?", xToday.Rumina)).
		Group("neck_ring_number").
		First(weekHabitData).Error; err != nil {
		zaplog.Error("WeeklyActiveAvgUpdate-1",
			zap.Any("error", err),
			zap.Any("neckRingNumber", neckRingNumber),
			zap.Any("frameId", frameid),
			zap.Any("heatDate", heatDate),
			zap.Any("xToday", xToday),
		)
	}
	return weekHabitData
}

func (e *Entry) FindSumHabitData(pastureId int64, neckRingNumber, heatDate string, frameid int32, xToday *XToday) *SumHabit {
	beginDayDate, _ := util.TimeParseLocal(model.LayoutDate2, heatDate)
	before1DayDate := beginDayDate.AddDate(0, 0, -1).Format(model.LayoutDate2)
	activeTime := fmt.Sprintf("%s %02d:00:00", heatDate, frameid*2+1)
	activeStartTimeParse, _ := util.TimeParseLocal(model.LayoutTime, activeTime)
	activeStartTime := activeStartTimeParse.Add(-23 * time.Hour).Format(model.LayoutTime)
	// 累计24小时数值
	sumHabitData := &SumHabit{}
	if err := e.DB.Model(new(model.NeckActiveHabit)).
		Select(
			"neck_ring_number",
			"IF(COUNT(1)>6, ROUND(AVG(filter_rumina)*12,0), 0) as sum_rumina",
			"IF(COUNT(1)>6, ROUND(AVG(intake)*12,0), 0) as sum_intake",
			"IF(COUNT(1)>6, ROUND(AVG(inactive)*12,0), 0) as sum_inactive",
			"IF(COUNT(1)>6, ROUND(AVG(active)*12,0), 0) as sum_active",
			"MAX(change_filter) as sum_max_high",
			fmt.Sprintf("MIN(IF(change_filter > %d, change_filter, 0)) as sum_min_high", model.DefaultChangeFilter),
			fmt.Sprintf("MIN( CASE WHEN filter_chew > %d THEN filter_chew WHEN filter_rumina >= %d THEN filter_rumina ELSE 0 END) as sum_min_chew", model.DefaultChangeFilter, model.DefaultRuminaFilter),
		).
		Where("pasture_id = ?", pastureId).
		Where("heat_date BETWEEN ? AND ?", before1DayDate, heatDate).
		Where("active_time BETWEEN ? AND ?", activeStartTime, activeTime).
		Where(e.DB.Where("high > ?", xToday.High).Or("rumina >= ?", xToday.Rumina)).
		Where("neck_ring_number = ?", neckRingNumber).
		Group("neck_ring_number").First(sumHabitData).Debug().Error; err != nil {
		zaplog.Error("WeeklyActiveAvgUpdate-2",
			zap.Any("error", err),
			zap.Any("neckRingNumber", neckRingNumber),
			zap.Any("frameId", frameid),
			zap.Any("heatDate", heatDate),
			zap.Any("xToday", xToday),
		)
	}
	return sumHabitData
}

func (e *Entry) FindBefore3DaysNeckActiveHabit(pastureId int64, neckRingNumber, heatDate string, frameid int32) *model.NeckActiveHabit {
	before3DaysNeckActiveHabit := &model.NeckActiveHabit{}
	beginDayDate, _ := util.TimeParseLocal(model.LayoutDate2, heatDate)
	before3DayDate := beginDayDate.AddDate(0, 0, -3).Format(model.LayoutDate2)
	if err := e.DB.Model(new(model.NeckActiveHabit)).
		Select("sum_rumina", "sum_intake").
		Where("pasture_id = ?", pastureId).
		Where("neck_ring_number = ?", neckRingNumber).
		Where("heat_date = ?", before3DayDate).
		Where("frameid = ? ", frameid).
		First(before3DaysNeckActiveHabit).Error; err != nil {
		zaplog.Error("WeeklyActiveAvgUpdate-3",
			zap.Any("error", err),
			zap.Any("neckRingNumber", neckRingNumber),
			zap.Any("frameId", frameid),
			zap.Any("heatDate", heatDate),
		)
	}
	return before3DaysNeckActiveHabit
}

// FindNeckRingEstrusByFirstTimeEmpty 查询firstTime为空的数据
func (e *Entry) FindNeckRingEstrusByFirstTimeEmpty(pastureId int64) []*model.NeckRingEstrus {
	neckRingEstrusList := make([]*model.NeckRingEstrus, 0)
	if err := e.DB.Model(new(model.NeckRingEstrus)).
		Where("first_time = ?", "").
		Where("is_show = ?", pasturePb.IsShow_Ok).
		Where("pasture_id = ?", pastureId).
		Find(&neckRingEstrusList).Error; err != nil {
		zaplog.Error("FindNeckRingEstrusFirstTime", zap.Any("err", err))
	}
	return neckRingEstrusList
}