kpt-pasture/module/crontab/milk_original.go

package crontab

import (
	"fmt"
	"kpt-pasture/model"
	"kpt-pasture/util"
	"strconv"
	"strings"
	"time"

	"gitee.com/xuyiping_admin/pkg/logger/zaplog"
	"go.uber.org/zap"
)

func (e *Entry) UpdateMilkOriginal() error {
	pastureList := e.FindPastureList()
	if pastureList == nil || len(pastureList) == 0 {
		return nil
	}

	for _, pasture := range pastureList {
		e.ProcessMilkOriginal(pasture.Id)
	}

	return nil
}

func (e *Entry) ProcessMilkOriginal(pastureId int64) {
	milkConfigList, err := e.FindMilkConfigure(pastureId)
	if err != nil {
		zaplog.Error("MilkOriginal", zap.Any("pastureId", pastureId), zap.Any("err", err))
		return
	}
	milkClassConfig := &MilkClassConfig{}
	for _, v := range milkConfigList {
		switch v.Name {
		case model.FirstClassMilkTime:
			milkClassConfig.FirstClassMilkTime = v.Value
		case model.SecondClassMilkTime:
			milkClassConfig.SecondClassMilkTime = v.Value
		case model.ThirdClassMilkTime:
			milkClassConfig.ThirdClassMilkTime = v.Value
		case model.FourthClassMilkTime:
			milkClassConfig.FourthClassMilkTime = v.Value
		case model.UpdateMilkOriginalMaxId:
			maxId, _ := strconv.ParseInt(v.Value, 10, 64)
			milkClassConfig.OldUpdateMaxId = maxId
		}
	}
	xDBeg, xBeg1, xBeg2, xBeg3, xBeg4 := parseXBeg(milkClassConfig)
	e.UpdateShifts(pastureId, xBeg1, xBeg2, xBeg3, xBeg4)
	e.UpdateMilkDate(pastureId, xDBeg)

	// 获取当前最大Id
	var currentMaxId int64
	if err = e.DB.Model(new(model.MilkOriginal)).
		Select("MAX(id)").
		Where("pasture_id = ?", pastureId).
		Where("id > ?", milkClassConfig.OldUpdateMaxId).
		Scan(&currentMaxId).Error; err != nil {
		zaplog.Error("DeleteRepeatMilkData GetOriWid", zap.Any("pastureId", pastureId), zap.Any("err", err))
		return
	}
	milkClassConfig.CurrentMaxId = currentMaxId

	// 获取时间范围和ID范围
	deleteModel := &DeleteMilkOriginal{}
	selectSql := fmt.Sprintf(`MIN(DATE(TIMESTAMPADD(HOUR, -%d, attach_time))) as x_mind, 
	MAX(DATE(TIMESTAMPADD(HOUR, -%d, attach_time))) as x_max_d2, MIN(id) as x_min_wid, MAX(id) as x_max_wid`, xDBeg, xDBeg)
	if err = e.DB.Model(new(model.MilkOriginal)).
		Select(selectSql).
		Where("pasture_id = ?", pastureId).
		Where("id > ?", milkClassConfig.OldUpdateMaxId).
		First(deleteModel).Error; err != nil {
		zaplog.Error("DeleteRepeatMilkData", zap.Any("pastureId", pastureId), zap.Any("err", err))
		return
	}
	e.DeleteRepeatMilkData(pastureId, deleteModel, milkClassConfig)
}

// UpdateShifts 更新班次
func (e *Entry) UpdateShifts(pastureId int64, xBeg1, xBeg2, xBeg3, xBeg4 int) {
	milkOriginalList := make([]*model.MilkOriginal, 0)
	if err := e.DB.Model(new(model.MilkOriginal)).
		Where("pasture_id = ?", pastureId).
		Where("shifts = ?", 0).
		Find(&milkOriginalList).Error; err != nil {
		zaplog.Error("UpdateShifts", zap.Any("pastureId", pastureId), zap.Any("err", err))
	}

	for _, v := range milkOriginalList {
		subDetachTime1 := util.Substr(v.DetacherTime, 11, 2)
		subDetachTime2 := util.Substr(v.DetacherTime, 14, 2)

		subDetachTime1Int, _ := strconv.ParseInt(subDetachTime1, 10, 64)
		subDetachTime2Int, _ := strconv.ParseInt(subDetachTime2, 10, 64)
		allDetachTime := int(subDetachTime1Int*100 + subDetachTime2Int)

		// 更新第一班
		if xBeg2 > xBeg1 {
			if allDetachTime >= xBeg1 && allDetachTime <= xBeg2 {
				v.Shifts = 1
			}
		} else {
			if allDetachTime >= xBeg1 || allDetachTime <= xBeg2 {
				v.Shifts = 1
			}
		}

		// 更新第二班
		if xBeg3 > xBeg2 {
			if allDetachTime >= xBeg2 && allDetachTime <= xBeg3 {
				v.Shifts = 2
			}
		} else {
			if allDetachTime >= xBeg2 || allDetachTime <= xBeg3 {
				v.Shifts = 2
			}
		}

		// 更新第四班（如果有）
		if xBeg4 > 0 && xBeg4 != xBeg1 {
			if xBeg1 > xBeg4 {
				if allDetachTime >= xBeg4 && allDetachTime <= xBeg1 {
					v.Shifts = 4
				}
			} else {
				if allDetachTime >= xBeg4 || allDetachTime <= xBeg1 {
					v.Shifts = 4
				}
			}
		}

		// 如果还没有分配班次，则分配到第三班
		if v.Shifts == 0 {
			v.Shifts = 3
		}
		// 批量更新数据库
		if err := e.DB.Model(new(model.MilkOriginal)).
			Select("shifts").
			Where("id = ?", v.Id).
			Updates(v).Error; err != nil {
			zaplog.Error("UpdateShifts Save", zap.Any("pastureId", pastureId), zap.Any("err", err))
		}
	}
}

// UpdateMilkDate 更换挤奶时间
func (e *Entry) UpdateMilkDate(pastureId int64, xDBeg int) {
	milkOriginalList := make([]*model.MilkOriginal, 0)
	if err := e.DB.Model(new(model.MilkOriginal)).
		Where("pasture_id = ?", pastureId).
		Where("milk_date = ?", "").
		Find(&milkOriginalList).Error; err != nil {
		zaplog.Error("UpdateMilkDate", zap.Any("pastureId", pastureId), zap.Any("err", err))
		return
	}

	for _, v := range milkOriginalList {
		// 获取结束时间，如果为空则使用默认时间
		var endTime time.Time
		if v.EndTime == "" {
			endTime = time.Date(1999, 12, 31, 23, 0, 0, 0, time.Local)
		}

		// 比较挤奶时间和结束时间，取较晚的时间
		detacherTime, _ := util.TimeParseLocal(model.LayoutTime, v.DetacherTime)
		latestTime := detacherTime
		if endTime.After(detacherTime) {
			latestTime = endTime
		}

		// 减去xDBeg小时并获取日期
		milkDate := latestTime.Add(time.Duration(-xDBeg) * time.Hour).Format(model.LayoutDate2)

		// 更新数据库
		if err := e.DB.Model(new(model.MilkOriginal)).
			Select("milk_date").
			Where("id = ?", v.Id).
			Update("milk_date", milkDate).Error; err != nil {
			zaplog.Error("UpdateMilkDate", zap.Any("err", err), zap.Any("milkDate", milkDate))
		}
	}
}

// DeleteRepeatMilkData 删除重复数据
func (e *Entry) DeleteRepeatMilkData(pastureId int64, deleteModel *DeleteMilkOriginal, cfg *MilkClassConfig) {
	// 获取最小日期对应的最小wid
	var oriWid int64
	if err := e.DB.Model(new(model.MilkOriginal)).
		Select("MIN(id)").
		Where("pasture_id = ?", pastureId).
		Where("milk_date = ?", deleteModel.XMind).
		Scan(&oriWid).Error; err != nil {
		zaplog.Error("DeleteRepeatMilkData GetOriWid", zap.Any("pastureId", pastureId), zap.Any("err", err))
		return
	}

	milkOriginalList := make([]*model.MilkOriginal, 0)
	if err := e.DB.Model(new(model.MilkOriginal)).
		Where("pasture_id = ?", pastureId).
		Where("id BETWEEN ? AND ?", cfg.OldUpdateMaxId+1, cfg.CurrentMaxId).
		Find(&milkOriginalList).Error; err != nil {
		zaplog.Error("DeleteRepeatMilkData", zap.Any("pastureId", pastureId), zap.Any("err", err))
	}

	for _, v := range milkOriginalList {
		e.Delete1(v, deleteModel.XMind, cfg)
		e.Delete2(v, deleteModel.XMind, cfg)
		e.Delete3(v, deleteModel.XMind, cfg)
		e.Delete4(v, deleteModel.XMind, cfg)
	}

	/*// 2. 删除重复记录（除第一条外）
	if err := e.DB.Exec(`
		DELETE FROM milk_original
		WHERE wid IN (
			SELECT m.wid FROM (
				SELECT m1.wid, m1.milk_date, m1.shifts, m1.detacher_address, m1.attach_time, m1.milk_weight, m1.pasture_id
				FROM milk_original m1
				WHERE m1.wid BETWEEN ? AND ?
				AND m1.milk_date >= ?
				AND EXISTS (
					SELECT 1 FROM milk_original m2
					WHERE m2.wid BETWEEN ? AND ?
					AND m2.milk_date = m1.milk_date
					AND m2.shifts = m1.shifts
					AND m2.detacher_address = m1.detacher_address
					AND m2.attach_time = m1.attach_time
					AND m2.milk_weight = m1.milk_weight
					AND m2.pasture_id = m1.pasture_id
					AND m2.wid < m1.wid
				)
			) m
		)`,
		oriWid, maxWid, minDate, oriWid, maxWid).Error; err != nil {
		zaplog.Error("DeleteRepeatMilkData DeleteDuplicates", zap.Any("err", err))
	}

	// 4. 删除各班次开始前无奶量记录
	if err := e.DB.Exec(`
		DELETE FROM milk_original
		WHERE wid IN (
			SELECT m.wid FROM (
				SELECT m1.wid, m1.milk_date, m1.shifts, m1.milk_weight,
						@tot := IF(@tot + m1.milk_weight > 100, 100,
							IF(m1.shifts = @shifts, @tot, 0) + m1.milk_weight) m_tot,
						@shifts := m1.shifts
				FROM milk_original m1, (SELECT @tot := 0, @shifts := 0) aa
				WHERE m1.wid BETWEEN ? AND ?
				AND m1.milk_date >= ?
				AND m1.pasture_id = ?
				ORDER BY m1.milk_date, m1.shifts, STR_TO_DATE(m1.attach_time, '%Y-%m-%d %H:%i:%s')
			) m
			WHERE m.m_tot = 0
		)`,
		minWid, maxWid, minDate, pastureId).Error; err != nil {
		zaplog.Error("DeleteRepeatMilkData DeleteNoMilkAtShift", zap.Any("err", err))
	}

	// 5. 删除时间异常数据
	if err := e.DB.Exec(`
		DELETE FROM milk_original
		WHERE wid BETWEEN ? AND ?
		AND milk_date >= '2020-10-01'
		AND STR_TO_DATE(milk_date_time, '%Y-%m-%d %H:%i:%s') > STR_TO_DATE(detacher_time, '%Y-%m-%d %H:%i:%s')
		AND STR_TO_DATE(attach_time, '%Y-%m-%d %H:%i:%s') > STR_TO_DATE(detacher_time, '%Y-%m-%d %H:%i:%s')
		AND HOUR(STR_TO_DATE(attach_time, '%Y-%m-%d %H:%i:%s')) = 23
		AND pasture_id = ?`,
		minWid, maxWid, pastureId).Error; err != nil {
		zaplog.Error("DeleteRepeatMilkData DeleteAbnormalTime", zap.Any("err", err))
	}*/
}

func (e *Entry) Delete1(data *model.MilkOriginal, xMinD string, cfg *MilkClassConfig) {
	// 1. 删除attach_time为00:00:00的记录
	acctchStr := util.Substr(data.AttachTime, -1, 8)
	if data.MilkDate < xMinD || acctchStr != "00:00:00" {
		return
	}

	// 2. 检查是否存在符合条件的m2记录
	var count int64
	if err := e.DB.Model(new(model.MilkOriginal)).
		Where("wid BETWEEN ? AND ?", cfg.OldUpdateMaxId+1, cfg.CurrentMaxId).
		Where("milk_date = ?", data.MilkDate).
		Where("detacher_address = ?", data.DetacherAddress).
		Where("ABS(TIMESTAMPDIFF(SECOND, detach_time, ?)) < 10", data.DetacherTime).
		Where("milk_weight = ?", data.MilkWeight).
		Where("pasture_id = ?", data.PastureId).
		Where("RIGHT(attach_time, 8) != '00:00:00'").
		Count(&count).Error; err != nil {
		zaplog.Error("Delete1", zap.Any("err", err))
		return
	}

	if count > 0 {
		if err := e.DB.Model(new(model.MilkOriginal)).
			Where("id = ?", data.Id).
			Delete(data).Error; err != nil {
			zaplog.Error("Delete1", zap.Any("err", err), zap.Any("data", data))
		}
	}
}

func (e *Entry) Delete2(data *model.MilkOriginal, xMinD string, cfg *MilkClassConfig) {
	// 1. 检查记录是否在时间范围内
	if data.MilkDate < xMinD {
		return
	}

	// 2. 检查是否存在重复记录（除第一条外）
	var count int64
	if err := e.DB.Model(new(model.MilkOriginal)).
		Where("id BETWEEN ? AND ?", cfg.OldUpdateMaxId+1, cfg.CurrentMaxId).
		Where("milk_date = ?", data.MilkDate).
		Where("shifts = ?", data.Shifts).
		Where("detacher_address = ?", data.DetacherAddress).
		Where("attach_time = ?", data.AttachTime).
		Where("milk_weight = ?", data.MilkWeight).
		Where("pasture_id = ?", data.PastureId).
		Where("id < ?", data.Id). // 只查找比当前记录更早的记录
		Count(&count).Error; err != nil {
		zaplog.Error("Delete2", zap.Any("err", err))
		return
	}

	// 3. 如果存在重复记录，则删除当前记录
	if count > 0 {
		if err := e.DB.Model(new(model.MilkOriginal)).
			Where("id = ?", data.Id).
			Delete(data).Error; err != nil {
			zaplog.Error("Delete2", zap.Any("err", err), zap.Any("data", data))
		}
	}
}

func (e *Entry) Delete3(data *model.MilkOriginal, xMinD string, cfg *MilkClassConfig) {
	// 1. 检查记录是否在时间范围内
	if data.MilkDate < xMinD {
		return
	}

	// 2. 检查是否为班次开始且无奶量记录
	var isFirstInShift bool
	if err := e.DB.Raw(`
        SELECT 1 FROM (
            SELECT m.id, m.milk_date, m.shifts, m.milk_weight,
                @tot := IF(@tot + m.milk_weight > 100, 100, 
                    IF(m.shifts = @shifts, @tot, 0) + m.milk_weight) m_tot,
                @shifts := m.shifts
            FROM milk_original m, (SELECT @tot := 0, @shifts := 0) vars
            WHERE m.id BETWEEN ? AND ?
            AND m.milk_date >= ?
            AND m.pasture_id = ?
            ORDER BY m.milk_date, m.shifts, m.attach_time
        ) t 
        WHERE t.id = ? AND t.m_tot = 0`,
		cfg.OldUpdateMaxId+1, cfg.CurrentMaxId, xMinD, data.PastureId, data.Id).
		Scan(&isFirstInShift).Error; err != nil {
		zaplog.Error("Delete3", zap.Any("err", err))
		return
	}

	// 3. 如果是班次开始且无奶量记录，则删除
	if isFirstInShift {
		if err := e.DB.Model(new(model.MilkOriginal)).
			Where("id = ?", data.Id).
			Delete(data).Error; err != nil {
			zaplog.Error("Delete3", zap.Any("err", err), zap.Any("data", data))
		}
	}
}

func (e *Entry) Delete4(data *model.MilkOriginal, xMinD string, cfg *MilkClassConfig) {
	// 1. 检查记录是否在时间范围内
	if data.MilkDate < "2020-10-01" {
		return
	}

	// 2. 检查是否为时间异常记录
	var isAbnormal bool
	if err := e.DB.Model(new(model.MilkOriginal)).
		Where("id = ?", data.Id).
		Where("milk_date >= ?", "2020-10-01").
		Where("recognition_time > detacher_time").
		Where("attach_time > detacher_time").
		Where("SUBSTRING(attach_time, 12, 2) = ?", "23").
		Where("pasture_id = ?", data.PastureId).
		Select("1").
		Scan(&isAbnormal).Error; err != nil {
		zaplog.Error("Delete4", zap.Any("err", err))
		return
	}

	// 3. 如果是时间异常记录，则删除
	if isAbnormal {
		if err := e.DB.Model(new(model.MilkOriginal)).
			Where("id = ?", data.Id).
			Delete(data).Error; err != nil {
			zaplog.Error("Delete4", zap.Any("err", err), zap.Any("data", data))
		}
	}
}

func parseXBeg(cfg *MilkClassConfig) (xBeg, xBeg1, xBeg2, xBeg3, xBeg4 int) {
	xBeg1Parts := strings.Split(cfg.FirstClassMilkTime, ":")
	if len(xBeg1Parts) < 2 {
		return
	}
	// 提取最后一部分
	xBeg1LastPart, _ := strconv.Atoi(xBeg1Parts[len(xBeg1Parts)-1])
	xBeg, _ = strconv.Atoi(xBeg1Parts[0])
	xBeg1, _ = strconv.Atoi(xBeg1Parts[1])
	xBeg1 = xBeg1*100 + xBeg1LastPart

	xBeg2Parts := strings.Split(cfg.SecondClassMilkTime, ":")
	if len(xBeg2Parts) < 2 {
		return
	}
	xBeg2LastPart, _ := strconv.Atoi(xBeg2Parts[len(xBeg2Parts)-1])
	xBeg2, _ = strconv.Atoi(xBeg2Parts[0])
	xBeg2 = xBeg2*100 + xBeg2LastPart

	xBeg3Parts := strings.Split(cfg.ThirdClassMilkTime, ":")
	if len(xBeg3Parts) < 2 {
		return
	}
	xBeg3LastPart, _ := strconv.Atoi(xBeg3Parts[len(xBeg3Parts)-1])
	xBeg3, _ = strconv.Atoi(xBeg3Parts[0])
	xBeg3 = xBeg3*100 + xBeg3LastPart

	xBeg4Parts := strings.Split(cfg.FourthClassMilkTime, ":")
	if len(xBeg4Parts) < 2 {
		return
	}
	xBeg4LastPart, _ := strconv.Atoi(xBeg4Parts[len(xBeg4Parts)-1])
	xBeg4, _ = strconv.Atoi(xBeg4Parts[0])
	xBeg4 = xBeg4*100 + xBeg4LastPart
	return
}