tmrgo/module/group.go

package module

import (
	"encoding/json"
	"errors"
	"fmt"
	"strconv"
	"tmr-watch/http/handle/restful"
	"tmr-watch/models"
)

const (
	IsDefault = 0
	IsOk      = 1
	IsNo      = 2
)

// DistributeFeedFormula 集团饲料配方下发
func DistributeFeedFormula(res []*models.FeedTemplate) error {
	if _, err := restful.Engine.Table(new(models.FeedTemplate)).Insert(&res); 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)
	newSql := restful.Engine.NewSession().Table("downloadplandtl1").Alias("dp1").
		Select(" dp1.actualweightminus,dp1.lweight,dp1.date ").
		Join("LEFT", []string{"feedtemplet", "ft"}, "dp1.fid = ft.id").
		Where("dp1.pastureid = ?", req.PastureId)

	if len(req.StartDate) > 0 && len(req.EndDate) > 0 {
		newSql.And("dp1.date >= ? and  dp1.date <= ?", req.StartDate, req.EndDate)
	}

	if req.CattleParentCategoryId > 0 {
		newSql.And("ft.ccid = ?", req.CattleParentCategoryId)
	}

	if req.FeedFormulaId > 0 {
		newSql.And("dp1.fid = ?", req.FeedFormulaId)
	}
	if err := newSql.GroupBy("dp1.date").OrderBy("dp1.date").Find(&res); err != nil {
		return nil, err
	}

	return res, nil
}

// SprinkleFodderData 撒料准确率
func SprinkleFodderData(req *models.AnalysisAccuracyRequest) ([]*models.MixedFodderDataList, error) {
	res := make([]*models.MixedFodderDataList, 0)
	newSql := restful.Engine.NewSession().Table("downloadplandtl2").Alias("dp2").
		Select("dp2.actualweightminus , dp2.lweight,  dp2.date").
		Join("LEFT", []string{"feedtemplet", "ft"}, "dp2.feedtempletid = ft.id").
		Where("dp2.pastureid = ?", req.PastureId)

	if len(req.StartDate) > 0 && len(req.EndDate) > 0 {
		newSql.And("dp2.date >= ? and  dp2.date <= ?", req.StartDate, req.EndDate)
	}

	if req.CattleParentCategoryId > 0 {
		newSql.And("ft.ccid = ?", req.CattleParentCategoryId)
	}

	if req.FeedFormulaId > 0 {
		newSql.And("dp2.feedtempletid = ?", req.FeedFormulaId)
	}
	if err := newSql.GroupBy("dp2.date").OrderBy("dp2.date").Find(&res); err != nil {
		return nil, err
	}

	return res, nil
}

// MixedFodderCorrectData 混料正确率
// SELECT
//SUM(de.havebuttom) AS "已混料操作数",de.date,
//IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.feedallowratio AND de.actualweightminus<>0,1,0)),0) AS "混料正确数"
//FROM downloadplandtl1 de LEFT JOIN feedtemplet ft ON ft.id = de.fid
//WHERE de.pastureid = 1653271339
//AND ft.id =20 AND ft.ccid = ?
//GROUP BY de.date ORDER BY de.date
func MixedFodderCorrectData(req *models.AnalysisAccuracyRequest) ([]*models.MixedFodderCorrectDataList, error) {
	res := make([]*models.MixedFodderCorrectDataList, 0)
	newSql := restful.Engine.NewSession().Table("downloadplandtl1").Alias("de").
		Select("SUM(de.havebuttom) AS use_mixed_fodder_option_number,de.date,IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.feedallowratio AND de.actualweightminus<>0,1,0)),0) AS mixed_correct_number").
		Join("LEFT", []string{"feedtemplet", "ft"}, "ft.id = de.fid").
		Where("de.pastureid = ?", req.PastureId)

	if len(req.StartDate) > 0 && len(req.EndDate) > 0 {
		newSql.And("de.date >= ? and  de.date <= ?", req.StartDate, req.EndDate)
	}

	if req.CattleParentCategoryId > 0 {
		newSql.And("ft.ccid = ?", req.CattleParentCategoryId)
	}

	if req.FeedFormulaId > 0 {
		newSql.And("de.tempid = ?", req.FeedFormulaId)
	}
	if err := newSql.GroupBy("de.date").OrderBy("de.date").Find(&res); err != nil {
		return nil, err
	}
	return res, nil
}

func SprinkleFodderCorrectData(req *models.AnalysisAccuracyRequest) ([]*models.SprinkleFodderCorrectDataList, error) {
	res := make([]*models.SprinkleFodderCorrectDataList, 0)
	newSql := restful.Engine.NewSession().Table("downloadplandtl2").Alias("de").
		Select("SUM(de.havebuttom) AS use_sprinkle_option_number,de.date,IFNULL(SUM(IF(ABS(de.actualweightminus-de.lweight)<=de.allowratio AND de.actualweightminus<>0,1,0)),0) AS sprinkle_correct_number").
		Join("LEFT", []string{"feedtemplet", "ft"}, "ft.id = de.feedtempletid").
		Where("de.pastureid = ?", req.PastureId)

	if len(req.StartDate) > 0 && len(req.EndDate) > 0 {
		newSql.And("de.date >= ? and  de.date <= ?", req.StartDate, req.EndDate)
	}

	if req.CattleParentCategoryId > 0 {
		newSql.And("ft.ccid = ?", req.CattleParentCategoryId)
	}

	if req.FeedFormulaId > 0 {
		newSql.And("de.tempid = ?", req.FeedFormulaId)
	}
	if err := newSql.GroupBy("de.date").OrderBy("de.date").Find(&res); err != nil {
		return nil, err
	}
	return res, nil
}

func ProcessAnalysisData(req *models.AnalysisAccuracyRequest) ([]*models.ProcessData, error) {
	res := make([]*models.ProcessData, 0)
	newSql := restful.Engine.Table("downloadplandtl1_exec").Alias("e").
		Select("e.id,e.begintime as exec_begin_time,e.intime as exec_end_time,e.processtime as exec_process_time,e.stirdelay as exec_stir_delay ,l2.begintime as l2_begin_time,l2.intime as l2_end_time,l2.processtime as l2_process_time").
		Join("LEFT", []string{"downloadplandtl2", "l2"}, "e.pastureid = l2.pastureid and e.pid = l2.pid").
		Where("e.pastureid = ?", req.PastureId)

	if len(req.StartDate) > 0 && len(req.EndDate) > 0 {
		newSql.And("e.date >= ? and  e.date <= ?", req.StartDate, req.EndDate)
	}

	if req.CattleParentCategoryId > 0 {
		newSql.And("l2.cowclassid = ?", req.CattleParentCategoryId)
	}

	if req.FeedFormulaId > 0 {
		newSql.And("l2.feedtempletid = ?", req.FeedFormulaId)
	}
	if err := newSql.GroupBy("e.date").OrderBy("e.date").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
	}

	b, _ := json.Marshal(res)
	fmt.Println("======SprinkleStatistics======", string(b))

	return res, nil
}

func AccountDistribution(req *models.AccountDistributionRequest) error {
	res := &models.User{}
	_, err := restful.Engine.Select("pastureid,username,empname,password,phone").
		Where("enable = ?", IsOk).And("isdelete = ?", IsNo).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.FeedFormulaListRequest) ([]*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 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 HAVING tmp.feed_formula_id = %d`, pastureId, startTime, endTime, 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 HAVING  feed_formula_id = %d`,
		pastureId, startTime, endTime, feedFormulaId)
	dataList := &models.SprinkleDetail{}
	if _, err := restful.Engine.NewSession().SQL(sql).Get(dataList); err != nil {
		return nil, err
	}
	return dataList, nil
}