tmrgo/module/group.go

package module

import (
	"context"
	"errors"
	"fmt"
	"strconv"
	"time"
	"tmr-watch/http/handle/restful"
	"tmr-watch/models"

	operationPb "gitee.com/xuyiping_admin/go_proto/proto/go/backend/operation"
	pasturePb "gitee.com/xuyiping_admin/go_proto/proto/go/backend/pasture"
	"github.com/xormplus/xorm"
)

// DistributeFeedFormula 集团饲料配方下发
func DistributeFeedFormula(pastureId int64, feedTemplateList []*models.FeedTemplate) error {
	tx := restful.Engine.NewSession()
	defer tx.Close()

	for _, feedTemplate := range feedTemplateList {
		// 过滤掉没有饲料详情的配方
		if len(feedTemplate.FeedFormulaDetail) <= 0 {
			continue
		}
		if _, err := tx.Table(new(models.FeedTemplate)).Insert(feedTemplate); err != nil {
			return err
		}
		feedFormulaDetail := make([]*models.FeedTemplateDetail, 0)
		for _, f := range feedTemplate.FeedFormulaDetail {
			f.FtId = feedTemplate.Id
			feedFormulaDetail = append(feedFormulaDetail, f)
		}

		if _, err := tx.Table(new(models.FeedTemplateDetail)).Insert(feedFormulaDetail); err != nil {
			return err
		}
	}

	return tx.Commit()
}

// CancelDistributeFeedFormula 集团饲料配方取消下发
func CancelDistributeFeedFormula(pastureId int64, ids []int64) error {
	if _, err := restful.Engine.Table(new(models.FeedTemplate)).
		Where("id IN ?", ids).Where("pasture_id = ?", pastureId).Update(map[string]interface{}{"enable": 2, "is_modify": 2}); err != nil {
		return err
	}
	return nil
}

func FeedFormulaIsModify(req *models.PastureFeedFormulaIsModifyRequest) error {
	if _, err := restful.Engine.Table(new(models.FeedTemplate)).Cols("is_modify").
		Where("id = ?", req.FeedFormulaId).And("pastureid = ?", req.PastureId).
		Update(map[string]interface{}{"is_modify": req.IsModify}); err != nil {
		return err
	}
	return nil
}

// MixedFodderData 混料准确率
func MixedFodderData(req *models.AnalysisAccuracyRequest) ([]*models.MixedFodderDataList, error) {
	res := make([]*models.MixedFodderDataList, 0)
	whereFeedFormulaIdStr := ""
	if req.FeedFormulaId > 0 {
		whereFeedFormulaIdStr = fmt.Sprintf(" AND  f.ccid = %d", req.FeedFormulaId)
	}
	sql := fmt.Sprintf(`SELECT DATE(de.date) AS plan_time,
    CONCAT( IF (SUM(de.actualweightminus)>SUM(de.lweight),ROUND((SUM(de.lweight)/SUM(de.actualweightminus)*100),2) ,SUM(de.actualweightminus)/SUM(de.lweight)*100) ,2) AS mixed_fodder_accurate_ratio,
	ROUND(IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.feedallowratio AND de.actualweightminus<>0,1,0))/SUM(1),0)*100,2) AS mixed_fodder_correct_ratio
	FROM downloadplandtl1 de 
 	LEFT JOIN downloadedplan d ON d.id = de.pid
 	LEFT JOIN feedtemplet f ON f.id = d.tempid
	WHERE  de.pastureid= %d AND de.intime IS NOT NULL 
	AND (SELECT d.lpplantype FROM downloadedplan d WHERE d.pastureid = de.pastureid AND d.id = de.pid) IN (0,1,4) 
	AND de.date >= '%s' AND de.date <= '%s' %s GROUP BY de.date`, req.PastureId, req.StartDate, req.EndDate, whereFeedFormulaIdStr)

	if err := restful.Engine.SQL(sql).Find(&res); err != nil {
		return nil, err
	}
	return res, nil
}

// SprinkleFodderData 撒料准确率
func SprinkleFodderData(req *models.AnalysisAccuracyRequest) ([]*models.SprinkleFodderDataList, error) {
	res := make([]*models.SprinkleFodderDataList, 0)
	whereFeedFormulaIdStr := ""
	if req.FeedFormulaId > 0 {
		whereFeedFormulaIdStr = fmt.Sprintf(" AND  f.ccid = %d", req.FeedFormulaId)
	}
	sql := fmt.Sprintf(`SELECT DATE(d.mydate) AS plan_time,
		CONCAT( IF (SUM(de.actualweightminus)>SUM(de.lweight),ROUND((SUM(de.lweight)/SUM(de.actualweightminus)*100),2),SUM(de.actualweightminus)/SUM(de.lweight)*100) ,2) AS sprinkle_fodder_accurate_ratio,
		ROUND(IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.allowratio AND de.actualweightminus<>0,1,0))/SUM(1),0)*100,2) AS sprinkle_fodder_correct_ratio
		FROM downloadplandtl2 de 
		JOIN downloadedplan d ON d.id=de.pid AND d.pastureid = de.pastureid 
		LEFT JOIN feedtemplet f ON f.id = d.tempid
		WHERE  de.pastureid = %d AND DATE(de.date)  BETWEEN '%s' AND '%s' %s GROUP BY de.date`, req.PastureId, req.StartDate, req.EndDate, whereFeedFormulaIdStr)
	if err := restful.Engine.SQL(sql).Find(&res); err != nil {
		return nil, err
	}
	return res, nil
}

// TMRList tmr设备列表
func TMRList(req *models.AnalysisAccuracyRequest) ([]*models.ProcessData, error) {
	res := make([]*models.ProcessData, 0)
	sql := fmt.Sprintf(`SELECT d.lpplantype  as plan_type,
       	d.tmrtname as tmr_name,
       	f.ccid as c_cid,
       	TRIM(d.pastureid) as pasture_id,
       	TRIM(d.pid) as pid,
       	TRIM(d.id) as id
 		FROM downloadedplan d 
 		LEFT JOIN  feedtemplet f on  f.id = d.tempid 
 		WHERE d.pastureid= %d and d.intime IS NOT NULL
		AND d.mydate >= '%s' AND d.mydate <= '%s'`, req.PastureId, req.StartDate, req.EndDate)
	if req.CattleParentCategoryId > 0 {
		sql = fmt.Sprintf("%s AND f.ccid = %d", sql, req.CattleParentCategoryId)
	}
	if err := restful.Engine.SQL(sql).Find(&res); err != nil {
		return nil, err
	}

	return res, nil
}

// MixedProcessTimeList 混料时间列表
func MixedProcessTimeList(pastureId int32, id int64) ([]*models.ProcessTimeList, error) {
	res := make([]*models.ProcessTimeList, 0)
	sql := fmt.Sprintf(`SELECT IFNULL(TIMEDIFF (d1.intime,(SELECT MAX(intime) FROM downloadplandtl1_exec d2 
			WHERE  d1.pid=d2.pid AND d1.pastureid = d2.pastureid AND d2.intime<d1.intime)),TIMEDIFF(intime,(SELECT MAX(intime) FROM downloadedplan d2 
			WHERE  d1.date=d2.mydate AND d1.pastureid = d2.pastureid AND d2.id=d1.pid))) AS process_time
			FROM downloadplandtl1_exec d1 WHERE d1.pastureid = %d AND d1.pid = %d ORDER BY sort`, pastureId, id)
	if err := restful.Engine.SQL(sql).Find(&res); err != nil {
		return nil, err
	}
	return res, nil
}

// SprinkleProcessTimeList 混料时间列表
func SprinkleProcessTimeList(pastureId int32, id int64) ([]*models.ProcessTimeList, error) {
	res := make([]*models.ProcessTimeList, 0)
	sql := fmt.Sprintf(`SELECT IFNULL(TIMEDIFF (d1.intime,(SELECT MAX(intime) FROM downloadplandtl2 d2 
    WHERE  d1.pid=d2.pid AND d1.pastureid = d2.pastureid AND d2.intime<d1.intime)), TIMEDIFF(intime,(SELECT MAX(intime) FROM downloadplandtl1_exec d2 
	WHERE  d1.date=d2.date AND d1.pastureid = d2.pastureid AND d2.pid=d1.pid))) AS process_time FROM downloadplandtl2 d1
	WHERE d1.pastureid = %d AND pid =%d ORDER BY sort`, pastureId, id)
	if err := restful.Engine.SQL(sql).Find(&res); err != nil {
		return nil, err
	}
	return res, nil
}

func SprinkleStatistics(req *models.SprinkleStatisticsRequest) ([]*models.SprinkleStatisticsDataList, error) {
	res := make([]*models.SprinkleStatisticsDataList, 0)
	newSql := restful.Engine.Table("downloadedplan").Alias("b").
		Select("a.fbarid,a.fname,a.intime,a.processtime,b.times").
		Join("LEFT", []string{"downloadplandtl2", "a"}, "a.pastureid = b.pastureid AND b.pid = a.flpid").
		Where("a.pastureid = ?", req.PastureId).And("b.times > 0 ").
		And("a.fbarid > 0").And("a.intime IS NOT NULL").And("a.processtime IS NOT NULL")
	if len(req.StartDate) > 0 && len(req.EndDate) > 0 {
		newSql.And("a.date >= ? and  a.date <= ?", req.StartDate, req.EndDate)
	}

	if req.FeedFormulaId > 0 {
		newSql.And("a.feedtempletid = ?", req.FeedFormulaId)
	}

	if err := newSql.GroupBy("a.fbarid ,b.intime").Limit(100).Find(&res); err != nil {
		return nil, err
	}

	return res, nil
}

func AccountDistribution(req *models.AccountDistributionRequest) error {
	res := &models.User{}
	_, err := restful.Engine.Select("pastureid,username,empname,password,phone").
		Where("enable = ?", operationPb.IsShow_OK).And("isdelete = ?", operationPb.IsShow_NO).And("pastureid = ?", req.PastureId).
		And("username = ?", req.Account).Get(res)
	if err != nil {
		return err
	}
	if res.Username == req.Account {
		return errors.New("该账号已经存在")
	}
	session := restful.Engine.NewSession()
	defer session.Close()

	if err = session.Begin(); err != nil {
		return err
	}

	// 查看牧场是否存在
	pastureData := &models.Pasture{}
	if _, err = session.Table(new(models.Pasture).TableName()).Where("pastureid = ?", req.PastureId).Get(pastureData); err != nil {
		return err
	}

	if pastureData.PastureId <= 0 {
		newPasture := models.NewPasture(int64(req.PastureId), req.PastureName, req.Address, req.UserName, req.Phone)
		if _, err = session.Table(new(models.Pasture).TableName()).Insert(newPasture); err != nil {
			return err
		}
	}

	// 查询牧场对应菜单是否存在
	menuPasture := &models.MenuPasture{}
	if _, err = session.Table(new(models.MenuPasture).TableName()).Where("pastureid = ?", req.PastureId).Get(menuPasture); err != nil {
		return err
	}

	// 创建新的超级管理员角色
	newRole := models.NewDefaultRole(int64(req.PastureId))
	if _, err = session.Table(new(models.Role).TableName()).Insert(newRole); err != nil {
		return err
	}

	menuList := make([]*models.Menu, 0)
	if err = session.Table(new(models.Menu).TableName()).Find(&menuList); err != nil {
		return err
	}

	if menuPasture.PastureId <= 0 {
		newMenuPastureList := models.NewMenuPastureList(int64(req.PastureId), menuList)
		if _, err = session.Table(new(models.MenuPasture).TableName()).Insert(newMenuPastureList); err != nil {
			return err
		}
	}

	NewRoleMenu := models.NewRoleMenu(int64(req.PastureId), newRole.Id, menuList)
	if _, err = session.Table(new(models.RoleMenu).TableName()).Insert(NewRoleMenu); err != nil {
		return err
	}
	// 创建用户并绑定管理员权限
	newUser := models.NewUser(int64(req.PastureId), req.Account, req.UserName, req.Password, req.Phone, newRole.Id)
	if _, err = session.Table(new(models.User).TableName()).Insert(newUser); err != nil {
		return err
	}

	return session.Commit()
}

func CattleCategoryDistribute(req *models.CowClass) error {
	has, err := restful.Engine.Table(new(models.CowClass).TableName()).Exist(&models.CowClass{GroupId: req.GroupId})
	if err != nil {
		return err
	}
	if has {
		if _, err = restful.Engine.Table(new(models.CowClass).TableName()).
			Where("group_id = ?", req.GroupId).
			Update(map[string]interface{}{
				"pastureid":  req.PastureId,
				"classcode":  req.ClassCode,
				"classname":  req.ClassName,
				"enable":     req.Enable,
				"parentid":   req.ParentId,
				"parentname": req.ParentName,
			}); err != nil {
			return err
		}
	} else {
		if _, err = restful.Engine.Table(new(models.CowClass).TableName()).Insert(req); err != nil {
			return err
		}
	}

	return nil
}

func ForageCategoryDistribute(req *models.FeedClass) error {
	has, err := restful.Engine.Table(new(models.FeedClass).TableName()).Exist(&models.FeedClass{GroupId: req.GroupId})
	if err != nil {
		return err
	}

	if has {
		if _, err = restful.Engine.Table(new(models.FeedClass).TableName()).
			Where("group_id = ?", req.GroupId).
			Update(map[string]interface{}{
				"pastureid":        req.PastureId,
				"fccode":           req.FCCode,
				"fcname":           req.FCName,
				"enable":           req.Enable,
				"bigfeedclassid":   req.BigFeedClassId,
				"bigfeedclassname": req.BigFeedClassName,
			}); err != nil {
			return err
		}
	} else {
		if _, err = restful.Engine.Table(new(models.FeedClass).TableName()).Insert(req); err != nil {
			return err
		}
	}
	return nil
}

func ForageCategoryDelete(pastureId, groupId int64) error {
	feedClassHas := &models.FeedClass{GroupId: groupId, PastureId: pastureId}
	has, err := restful.Engine.Table(new(models.FeedClass).TableName()).Exist(feedClassHas)
	if err != nil {
		return err
	}
	if has {
		if _, err = restful.Engine.Table(new(models.FeedClass).TableName()).ID(feedClassHas.Id).Delete(feedClassHas); err != nil {
			return err
		}
	}
	return nil
}

func CowCategoryDelete(pastureId, groupId int64) error {
	cowClassHas := &models.CowClass{GroupId: groupId, PastureId: pastureId}
	has, err := restful.Engine.Table(new(models.CowClass).TableName()).Exist(cowClassHas)
	if err != nil {
		return err
	}
	if has {
		if _, err = restful.Engine.Table(new(models.CowClass).TableName()).ID(cowClassHas.Id).Delete(cowClassHas); err != nil {
			return err
		}
	}
	return nil
}

func FeedTemplateList(req *models.FeedListRequest) ([]*models.FeedTemplate, int64, error) {
	var (
		res   []*models.FeedTemplate
		total int64
		err   error
	)
	newSession := restful.Engine.NewSession()
	total, err = newSession.Table(new(models.FeedTemplate).TableName()).
		Where("pastureid = ?", req.PastureId).Count(&res)
	if err != nil {
		return nil, 0, err
	}

	if err = newSession.Table(new(models.FeedTemplate).TableName()).Limit(int(req.PageSize), int(req.Page-1)*int(req.PageSize)).Find(&res); err != nil {
		return nil, 0, err
	}

	return res, total, nil
}

func FeedList(req *models.FeedListRequest) ([]*models.Feed, int64, error) {
	var (
		res   []*models.Feed
		total int64
		err   error
	)
	newSession := restful.Engine.NewSession()
	total, err = newSession.Table(new(models.Feed).TableName()).
		Where("pastureid = ?", req.PastureId).Count(&res)
	if err != nil {
		return nil, 0, err
	}

	if err = newSession.Table(new(models.Feed).TableName()).Limit(int(req.PageSize), int(req.Page-1)*int(req.PageSize)).Find(&res); err != nil {
		return nil, 0, err
	}
	return res, total, nil
}

func FeedTemplateDetailList(req *models.FeedListRequest) ([]*models.FeedTemplateDetail, int64, error) {
	var (
		res   []*models.FeedTemplateDetail
		total int64
		err   error
	)
	newSession := restful.Engine.NewSession()
	total, err = newSession.Table(new(models.FeedTemplateDetail).TableName()).
		Where("pastureid = ?", req.PastureId).Count(&res)
	if err != nil {
		return nil, 0, err
	}

	if err = newSession.Table(new(models.FeedTemplateDetail).TableName()).Limit(int(req.PageSize), int(req.Page-1)*int(req.PageSize)).Find(&res); err != nil {
		return nil, 0, err
	}
	return res, total, nil
}

func FeedTemplateUsageDetail(req *models.FeedFormulaUsageRequest) (*models.FeedFormulaUsageResponse, error) {
	mixedDetail, err := getMixedDetail(req.PastureId, req.FeedFormulaId, req.StartTime, req.EndTime)
	if err != nil {
		return nil, err
	}
	sprinkleDetail, err := getSprinkleDetail(req.PastureId, req.FeedFormulaId, req.StartTime, req.EndTime)
	if err != nil {
		return nil, err
	}

	response := &models.FeedFormulaUsageResponse{
		MixedFodderAccurateRatio:    strconv.FormatFloat(mixedDetail.MixedFodderAccurateRatio, 'f', 2, 64) + "%",
		MixedFodderCorrectRatio:     strconv.FormatFloat(mixedDetail.MixedFodderCorrectRatio, 'f', 3, 64) + "%",
		SprinkleFodderAccurateRatio: strconv.FormatFloat(sprinkleDetail.SprinkleFodderAccurateRatio, 'f', 3, 64) + "%",
		SprinkleFodderCorrectRatio:  strconv.FormatFloat(sprinkleDetail.SprinkleFodderCorrectRatio, 'f', 3, 64) + "%",
		AddFeedTime:                 "",
		SprinkleTime:                "",
		StirTime:                    "",
		LastEditTime:                "",
	}
	return response, nil
}

func getMixedDetail(pastureId, feedFormulaId int32, startTime, endTime string) (*models.MixedDetail, error) {
	sql := fmt.Sprintf(`SELECT * FROM ( 
		SELECT DATE(de.date) AS plan_time,
		IFNULL((SELECT d.templetname FROM downloadedplan d WHERE d.pastureid = de.pastureid AND d.id = de.pid),de.fname) AS feed_formula_name,
		IFNULL((SELECT d.tempid FROM downloadedplan d WHERE d.pastureid = de.pastureid AND d.id = de.pid),0) AS feed_formula_id,  
		ROUND(SUM(de.lweight),2) AS l_weight,ROUND(SUM(de.actualweightminus),2) AS reality_weight,
		SUM(1) AS plan_mixed_opts,SUM(de.havebuttom) AS reality_mixed_opts,
		ROUND(SUM(de.havebuttom)/SUM(1)*100,2)  AS mixed_ops_ratio,
		SUM(IF(de.buttontype =1,1,0)) AS mixed_auto_jump_number , SUM(IF(de.buttontype>1,1,0)) AS mixed_manual_jump_number ,
		ROUND(ABS(SUM(de.lweight)-SUM(de.actualweightminus)),2) AS mix_error_number,
		CONCAT( IF (SUM(de.actualweightminus)>SUM(de.lweight),ROUND((SUM(de.lweight)/SUM(de.actualweightminus)*100),2) ,
		SUM(de.actualweightminus)/SUM(de.lweight)*100) ,2) AS mixed_fodder_accurate_ratio,
		IF (SUM(de.actualweightminus)>SUM(de.lweight),
		ROUND((SUM(de.lweight)/SUM(de.actualweightminus)*100),2) ,
		ROUND((SUM(de.actualweightminus)/SUM(de.lweight)*100) ,2)) AS hlzq,
		SUM(CASE WHEN (ABS(de.actualweightminus-de.lweight)/de.lweight)>3 AND  de.lweight >30 AND de.actualweightminus >30 THEN 1 ELSE 0 END) AS cancel_number,
		ROUND(STD(ABS(de.actualweightminus-de.lweight)) ,2) AS variance_ratio,
		IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.feedallowratio AND de.actualweightminus<>0,1,0)),0) AS mixed_fodder_correct_number,
		ROUND(IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.feedallowratio AND de.actualweightminus<>0,1,0))/SUM(1),0)*100,2) AS mixed_fodder_correct_ratio,
		ROUND(IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.feedallowratio AND de.actualweightminus<>0,1,0))/SUM(1),0)*100,2) hlzql,
		TRIM(de.pid) pid,
		ROUND(IFNULL(SUM(IF(((ABS(de.actualweightminus-de.lweight)/de.lweight)<=3 OR (de.lweight <30 AND de.actualweightminus <30 )),
		IF(ABS(de.actualweightminus-de.lweight)<=de.feedallowratio AND de.actualweightminus<>0,1,0),0))/SUM(IF(((ABS(de.actualweightminus-de.lweight)/de.lweight)<=3 OR (de.lweight <30 AND de.actualweightminus <30 )),1,0 )),0)*100,2) AS remove_cancel_correct_ratio
		FROM downloadplandtl1 de WHERE  de.pastureid= %d AND de.intime IS NOT NULL AND (SELECT d.lpplantype FROM downloadedplan d WHERE d.pastureid = de.pastureid AND d.id = de.pid) IN (0,1,4) AND de.date >= '%s' AND de.date <= '%s'
		GROUP BY de.date ) tmp `, pastureId, startTime, endTime)

	if feedFormulaId > 0 {
		sql = fmt.Sprintf("%s HAVING tmp.feed_formula_id = %d", sql, feedFormulaId)
	}
	dataList := &models.MixedDetail{}
	if _, err := restful.Engine.NewSession().SQL(sql).Get(dataList); err != nil {
		return nil, err
	}
	return dataList, nil
}

func getSprinkleDetail(pastureId, feedFormulaId int32, startTime, endTime string) (*models.SprinkleDetail, error) {
	sql := fmt.Sprintf(`SELECT * FROM (SELECT DATE(d.mydate) AS plan_time,
        IFNULL((SELECT d.templetname FROM downloadedplan d WHERE d.pastureid = de.pastureid AND d.id = de.pid),de.fname) AS feed_formula_name,
		IFNULL((SELECT d.tempid FROM downloadedplan d WHERE d.pastureid = de.pastureid AND d.id = de.pid),0) AS feed_formula_id,
		d.tmrtname AS tmr_name,
		IFNULL(TIMEDIFF ((SELECT MAX(intime) FROM downloadplandtl2 d2 WHERE  d2.pid=de.pid AND d2.pastureid = de.pastureid ),(SELECT MAX(intime) FROM downloadplandtl1_exec d2 WHERE  d2.pid=de.pid AND d2.pastureid = de.pastureid )),'00:00:00')  AS mixed_time,
		IFNULL(TIMEDIFF ((SELECT MIN(intime) FROM downloadplandtl2 d2 WHERE  d2.pid=de.pid AND d2.pastureid = de.pastureid ),(SELECT MAX(intime) FROM downloadplandtl1_exec d2 WHERE  d2.pid=de.pid AND d2.pastureid = de.pastureid )),'00:00:00')  AS wait_time,
		SUM(de.lweight) AS l_weight,ROUND(SUM(de.actualweightminus)) AS reality_weight,
		SUM(1) AS plan_sprinkle_opts,
		SUM(de.havebuttom) AS reality_sprinkle_opts, 
		ROUND(SUM(de.havebuttom)/SUM(1)*100,2) AS sprinkle_ops_ratio, 
		SUM(IF(de.buttontype =1,1,0)) AS sprinkle_auto_jump_number ,  
		SUM(IF(de.buttontype>1,1,0)) AS sprinkle_manual_jump_number ,
		ABS(SUM(de.actualweightminus)-SUM(de.lweight)) AS sprinkle_error_number,
		CONCAT( IF (SUM(de.actualweightminus)>SUM(de.lweight),ROUND((SUM(de.lweight)/SUM(de.actualweightminus)*100),2) ,
		SUM(de.actualweightminus)/SUM(de.lweight)*100) ,2) AS sprinkle_fodder_accurate_ratio,
		IF (SUM(de.actualweightminus)>SUM(de.lweight),ROUND((SUM(de.lweight)/SUM(de.actualweightminus)*100),2) ,
		ROUND((SUM(de.actualweightminus)/SUM(de.lweight)*100) ,2)) slzq,
		SUM(CASE WHEN (ABS(de.actualweightminus-de.lweight)/de.lweight)>3 AND  de.lweight >30 AND de.actualweightminus >30 THEN 1 ELSE 0 END) AS cancel_number,
		IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.allowratio AND de.actualweightminus<>0,1,0)),0) AS sprinkle_fodder_correct_number,
		ROUND(IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.allowratio AND de.actualweightminus<>0,1,0))/SUM(1),0)*100,2) AS sprinkle_fodder_correct_ratio,
		ROUND(IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.allowratio AND de.actualweightminus<>0,1,0))/SUM(1),0)*100,2) as slzql,
		TRIM(de.pid) pid,
		ROUND(IFNULL(SUM(IF(((ABS(de.actualweightminus-de.lweight)/de.lweight)<=3 OR (de.lweight <30 AND de.actualweightminus <30 )),
		IF(ABS(de.actualweightminus-de.lweight)<=de.allowratio AND de.actualweightminus<>0,1,0),0))/SUM(IF(((ABS(de.actualweightminus-de.lweight)/de.lweight)<=3 OR (de.lweight <30 AND de.actualweightminus <30 )),1,0 )),0)*100,2) AS remove_cancel_correct_ratio 
		FROM downloadplandtl2 de JOIN downloadedplan d ON d.id=de.pid AND d.pastureid = de.pastureid 
		WHERE  d.pastureid = %d AND DATE(d.mydate)  BETWEEN '%s' AND '%s'   AND d.lpplantype IN (0,2)) tmr `, pastureId, startTime, endTime)

	if feedFormulaId > 0 {
		sql = fmt.Sprintf("%s HAVING  feed_formula_id = %d", sql, feedFormulaId)
	}
	dataList := &models.SprinkleDetail{}
	if _, err := restful.Engine.NewSession().SQL(sql).Get(dataList); err != nil {
		return nil, err
	}
	return dataList, nil
}

func FeedFormulaVersion(pastureId int64, req *models.FeedFormulaUpdateVersionRequest) error {
	if req.Belong == 0 {
		return nil
	}
	DbSession := restful.Engine.NewSession()
	defer DbSession.Close()

	// 开启事务
	if err := DbSession.Begin(); err != nil {
		return err
	}

	feedDetailVersion := &models.FeedDetailVersion{
		FeedTemplateId: req.FeedTemplateId,
		PastureId:      pastureId,
		GroupVersion:   req.Version,
		Belong:         req.Belong,
		CreatedAt:      time.Now().Unix(),
		UpdatedAt:      time.Now().Unix(),
	}
	if _, err := DbSession.Insert(feedDetailVersion); err != nil {
		DbSession.Rollback()
		return err
	}

	// FeedDetailVersionLog 更新
	versionLogs := make([]*models.FeedDetailVersionLog, 0)
	for _, v := range req.Data {
		versionLogs = append(versionLogs, &models.FeedDetailVersionLog{
			VersionId:           feedDetailVersion.Id,
			PastureId:           pastureId,
			FeedTemplateId:      req.FeedTemplateId,
			ForageId:            int64(v.ForageId),
			ForageName:          v.ForageName,
			ForageGroupName:     v.ForageGroupName,
			Weight:              int32(v.Weight * 100),
			StirDelay:           v.StirDelay,
			AllowError:          v.AllowError,
			IsModify:            v.IsModify,
			IsLockCowCountRatio: operationPb.IsShow_Kind(v.IsLockCowCountRatio),
			Sort:                v.Sort,
		})
	}
	if _, err := DbSession.Table(new(models.FeedDetailVersionLog).TableName()).Insert(versionLogs); err != nil {
		DbSession.Rollback()
		return err
	}

	return DbSession.Commit()
}

func NewSearchFeedTemplateList(ctx context.Context, req *pasturePb.SearchFeedTemplateRequest) ([]*models.FeedTemplate, int64, error) {
	DBSession := restful.Engine.NewSession()
	defer DBSession.Close()

	res := make([]*models.FeedTemplate, 0)

	if req.Name != "" {
		DBSession.Where("name like '%?%'", req.Name)
	}

	if req.CattleCategoryId > 0 {
		DBSession.Where("ccid = ?", req.CattleCategoryId)
	}

	if req.FormulaTypeId > 0 {
		DBSession.Where("fttypeid = ?", req.FormulaTypeId)
	}

	switch req.SourceId {
	case 0:
		DBSession.Where("source = '自定义'")
	case 1:
		DBSession.Where("source = '集团下发未调整'")
	case 2:
		DBSession.Where("source = '集团下发有调整'")
	case 3:
		DBSession.Where("source = '导入'")
	}

	if req.Enable > 0 {
		DBSession.Where("enable = ?", req.Enable)
	}
	if req.Remark != "" {
		DBSession.Where("remark = ?", req.Remark)
	}

	DBSession.Desc("id")
	if total, err := DBSession.Count(&res); err != nil {
		return nil, 0, err
	} else {
		return res, total, nil
	}
}

func FeedDetailVersionLogList(ctx context.Context) ([]*models.FeedDetailVersion, error) {
	res := make([]*models.FeedDetailVersion, 0)
	if err := restful.Engine.NewSession().Find(&res); err != nil {
		if !errors.Is(err, xorm.ErrNotExist) {
			return nil, err
		}
	}
	return res, nil
}

// SearchFeedTemplateDetailListByFeedTemplateId 获取配方当前饲料列表详情
func SearchFeedTemplateDetailListByFeedTemplateId(ctx context.Context, pastureId, feedTemplateId int64) ([]*models.FeedTemplateDetail, error) {
	res := make([]*models.FeedTemplateDetail, 0)
	if err := restful.Engine.Table(new(models.FeedTemplateDetail).TableName()).Where("pastureid = ?", pastureId).And("ftid = ?", feedTemplateId).Find(&res); err != nil {
		return nil, err
	}
	return res, nil
}

// GetFeedDetailVersionByDesc 获取配方最新版本数据
func GetFeedDetailVersionByDesc(ctx context.Context, pastureId, feedTemplateId int64) (*models.FeedDetailVersion, error) {
	res := &models.FeedDetailVersion{}
	if _, err := restful.Engine.Table(new(models.FeedDetailVersion).TableName()).
		Where("pasture_id = ?", pastureId).
		And("feed_template_id = ?", feedTemplateId).OrderBy("id desc").Get(res); err != nil {
		return nil, err
	}
	return res, nil
}

// SearchFeedDetailVersionLogById 获取配方关联最新饲料信息
func SearchFeedDetailVersionLogById(ctx context.Context, pastureId, feedTemplateId, versionId int64) ([]*models.FeedDetailVersionLog, error) {
	res := make([]*models.FeedDetailVersionLog, 0)
	if err := restful.Engine.NewSession().Table(new(models.FeedDetailVersionLog).TableName()).Where("pasture_id = ?", pastureId).
		And("feed_formula_id = ?", feedTemplateId).And("version = ?", versionId).Find(&res); err != nil {
		return nil, err
	}
	return res, nil
}

// FeetTemplateUpgradeNewVersion 配方更新至最新版本
func FeetTemplateUpgradeNewVersion(ctx context.Context, pastureId, feedTemplateId int64) error {
	newFeedDetailVersion, err := GetFeedDetailVersionByDesc(ctx, pastureId, feedTemplateId)
	if err != nil {
		return err
	}

	DBSession := restful.Engine.NewSession()
	defer DBSession.Close()

	if err = DBSession.Begin(); err != nil {
		return err
	}

	if _, err = DBSession.Table(new(models.FeedTemplateDetail).TableName()).
		Where("pasture_id = ?", pastureId).
		And("feed_template_id = ?", feedTemplateId).Update("is_show", operationPb.IsShow_NO); err != nil {
		DBSession.Rollback()
		return err
	}

	res, err := SearchFeedDetailVersionLogById(ctx, pastureId, feedTemplateId, newFeedDetailVersion.Id)
	if err != nil {
		DBSession.Rollback()
		return err
	}

	if len(res) <= 0 {
		return DBSession.Commit()
	}

	newFtDetailList := make([]*models.FeedTemplateDetail, 0)
	for _, v := range res {
		newFtDetailList = append(newFtDetailList, &models.FeedTemplateDetail{
			PastureId:      pastureId,
			FtId:           feedTemplateId,
			FId:            v.ForageId,
			FName:          v.ForageName,
			FWeight:        float64(v.Weight / 100),
			IsLockCount:    int32(v.IsLockCowCountRatio),
			IsFg:           0,
			Sort:           v.Sort,
			FeedGroup:      v.ForageGroupName,
			PreFtId:        0,
			AutoSecond:     v.StirDelay,
			AutoSecondName: "",
			SplitFtPreId:   0,
			Deviation:      v.AllowError,
			IsModify:       int32(v.IsModify),
			IsShow:         int32(operationPb.IsShow_OK),
		})
	}

	if _, err = DBSession.Table(new(models.FeedTemplateDetail).TableName()).Insert(newFtDetailList); err != nil {
		DBSession.Rollback()
		return err
	}

	return DBSession.Commit()
}