package module

import (
	"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)
	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 proess_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)), IMEDIFF(intime,(SELECT MAX(intime) FROM downloadplandtl1_exec d2 
	WHERE  d1.date=d2.date AND d1.pastureid = d2.pastureid AND d2.pid=d1.pid))) AS proess_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 = ?", 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 `, 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
}