package backend

import (
	"context"
	"fmt"
	"kpt-pasture/model"
	"kpt-pasture/util"
	"net/http"

	pasturePb "gitee.com/xuyiping_admin/go_proto/proto/go/backend/cow"
	"gitee.com/xuyiping_admin/pkg/xerr"
)

// SingleFactorInfantSurvivalRateAnalysis 单因素受胎率分析
func (s *StoreEntry) SingleFactorInfantSurvivalRateAnalysis(ctx context.Context, req *pasturePb.SingleFactorPregnancyRateRequest) (*pasturePb.SingleFactorPregnancyRateResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	startTimeUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endTimeUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	if startTimeUnix == 0 || endTimeUnix == 0 || endTimeUnix <= startTimeUnix {
		return nil, xerr.Custom("开始时间不能大于结束时间")
	}
	list := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	chart := &pasturePb.SingleFactorPregnancyRateChart{
		Headers:             make([]string, 0),
		PregnantRate:        make([]float32, 0),
		MaxValue:            make([]int32, 0),
		MinValue:            make([]int32, 0),
		AveragePregnantRate: 0,
	}
	switch req.AnalysisMethod {
	case pasturePb.SingleFactorAnalysisMethod_Cycle:
		list, err = s.SingleFactorAnalysisMethodCycle(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Months:
		list, err = s.SingleFactorAnalysisMethodMonths(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Mating_Times:
		list, err = s.SingleFactorAnalysisMethodMatingTimes(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Breeding_Method:
		list, err = s.SingleFactorAnalysisMethodBreeding(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Breeding_Company:
		list, err = s.SingleFactorAnalysisMethodBreedingCompany(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Operation:
		list, err = s.SingleFactorAnalysisMethodOperation(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Mating_Interval:
		list, err = s.SingleFactorAnalysisMethodMatingInterval(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Bull:
		list, err = s.SingleFactorAnalysisMethodBull(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Breeding_Cycle:
		list, err = s.SingleFactorAnalysisMethodBreedingCycle(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Week:
		list, err = s.SingleFactorAnalysisMethodWeek(userModel.AppPasture.Id, req)
	case pasturePb.SingleFactorAnalysisMethod_Lact:
		list, err = s.SingleFactorAnalysisMethodLact(userModel.AppPasture.Id, req)
	default:
		return nil, xerr.Custom("错误的统计方式")
	}

	if err != nil {
		return nil, xerr.WithStack(err)
	}
	totalCountMap := make(map[int]int32, len(list))
	allTotalCount := int32(0)     // 总头数
	allPregnantRate := float64(0) // 总受胎率
	for i, v := range list {
		chart.Headers = append(chart.Headers, v.StatisticMethod)
		pregnantRate := float64(0)
		if v.EmptyPregnantCount+v.PregnantCount > 0 {
			pregnantRate = float64(v.PregnantCount) / float64(v.EmptyPregnantCount+v.PregnantCount)
		}
		chart.PregnantRate = append(chart.PregnantRate, float32(util.RoundToTwoDecimals(pregnantRate*100)))
		v.TotalCount = v.PregnantCount + v.EmptyPregnantCount + v.OtherCount
		v.PregnantRate = float32(util.RoundToTwoDecimals(pregnantRate * 100))
		spcRate := float32(0)
		if v.PregnantRate > 0 {
			spcRate = float32(util.RoundToTwoDecimals((float64(v.PregnantCount) / float64(v.TotalCount)) * 100))
		}
		v.SpceRate = spcRate
		ci95Min, ci95Max := util.ConfidenceInterval2(pregnantRate, float64(v.TotalCount))
		v.Ci95 = fmt.Sprintf("%d ~ %d", int32(ci95Min), int32(ci95Max))
		chart.MaxValue = append(chart.MaxValue, int32(ci95Max))
		chart.MinValue = append(chart.MinValue, int32(ci95Min))
		totalCountMap[i] = v.TotalCount
		allTotalCount += v.TotalCount
		allPregnantRate += pregnantRate
	}
	for i, v := range totalCountMap {
		if allTotalCount <= 0 || list[i].TotalCount <= 0 {
			continue
		}
		list[i].TotalRate = float32(util.RoundToTwoDecimals(float64(v) / float64(allTotalCount) * 100))
	}

	if len(list) > 0 && allPregnantRate > 0 {
		chart.AveragePregnantRate = float32(util.RoundToTwoDecimals(allPregnantRate/float64(len(list))) * 100)
	}

	return &pasturePb.SingleFactorPregnancyRateResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.SingleFactorPregnancyRateData{
			Total: int32(len(list)),
			List:  list,
			Chart: chart,
		},
	}, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodCycle(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	dateTimeRange, err := util.GetRangeDayByDays(req.StartDayTime, req.EndDayTime, req.Value)
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)

	selectSql := ""
	for _, v := range dateTimeRange {
		if len(v) != 2 {
			continue
		}
		startDayTimeUnix := util.TimeParseLocalUnix(v[0])
		endDayTimeUnix := util.TimeParseLocalEndUnix(v[1])

		selectSql += fmt.Sprintf(`
			SELECT
			'%s ~ %s' as  statistic_method,   
			COUNT(DISTINCT CASE WHEN mating_result = %d AND mating_result_at > 0 THEN cow_id END) AS pregnant_count, -- 怀孕头数
			COUNT(DISTINCT CASE WHEN mating_result = %d AND mating_result_at > 0 THEN cow_id END) AS empty_pregnant_count, -- 空怀头数
			COUNT(DISTINCT CASE WHEN mating_result = %d AND mating_result_at > 0 THEN cow_id END) AS abortion_count, -- 流产头数
			COUNT(DISTINCT CASE WHEN mating_result IN (%d, %d) THEN cow_id END) AS other_count, -- 其他数
			(
				COUNT(DISTINCT CASE WHEN mating_result = %d AND mating_result_at > 0 THEN cow_id END) +
				COUNT(DISTINCT CASE WHEN mating_result = %d AND mating_result_at > 0 THEN cow_id END) +
				COUNT(DISTINCT CASE WHEN mating_result IN (%d, %d) THEN cow_id END)
			) AS total_count -- 总数
			FROM event_mating WHERE pasture_id = %d AND status = %d AND reality_day BETWEEN %d AND %d
		UNION ALL `, v[0], v[1], pasturePb.MatingResult_Pregnant, pasturePb.MatingResult_Empty, pasturePb.MatingResult_Abort,
			pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch, pasturePb.MatingResult_Pregnant,
			pasturePb.MatingResult_Empty, pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch,
			pastureId, pasturePb.IsShow_Ok, startDayTimeUnix, endDayTimeUnix)
	}

	if len(selectSql) > 0 {
		selectSql = selectSql[:len(selectSql)-len("UNION ALL")-3] + ";"
	}

	if err = s.DB.Raw(selectSql).Scan(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodMonths(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	startDayTimeUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endDayTimeUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	if startDayTimeUnix == 0 || endDayTimeUnix == 0 || endDayTimeUnix <= startDayTimeUnix {
		return nil, xerr.Custom("开始时间不能大于结束时间")
	}

	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	pref := s.DB.Model(new(model.EventMating)).
		Select(`
		DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m') AS months,
		DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m') AS statistic_method,
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS pregnant_count, -- 怀孕头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS empty_pregnant_count, -- 空怀头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS abortion_count, -- 流产头数  
		COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END) AS other_count, -- 其他数
		(  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END)  
		) AS total_count -- 总数   
		`, pasturePb.MatingResult_Pregnant, pasturePb.MatingResult_Empty, pasturePb.MatingResult_Abort,
			pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch, pasturePb.MatingResult_Pregnant,
			pasturePb.MatingResult_Empty, pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch).
		Where("status = ?", pasturePb.IsShow_Ok).
		Where("pasture_id = ?", pastureId).
		Where("reality_day BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix)
	if req.CowType > 0 {
		pref.Where("cow_type = ?", req.CowType)
	}

	if req.CowKind > 0 {
		pref.Where("cow_kind = ?", req.CowKind)
	}

	if req.LactInterval > 0 {
		switch req.LactInterval {
		case pasturePb.CompareSymbol_Less_Than:
			pref.Where("lact < ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Less_Than_Or_Equal_To:
			pref.Where("lact <= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than:
			pref.Where("lact > ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than_Or_Equal_To:
			pref.Where("lact >= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Equal_To:
			pref.Where("lact = ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Not_Equal_To:
			pref.Where("lact != ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Between:
			pref.Where("lact BETWEEN ? AND ? ", req.LactIntervalStartValue, req.LactIntervalEndValue)
		default:
			return nil, xerr.Custom("错误的胎次区间符号")
		}
	}

	if err := pref.Group("months").Order("months").Find(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}
	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodMatingTimes(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodBreeding(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	startDayTimeUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endDayTimeUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)

	if err := s.DB.Model(new(model.EventMating)).
		Select(`
		CASE  
			WHEN expose_estrus_type = ? THEN '脖环揭发'  
			WHEN expose_estrus_type = ? THEN '脚环/计步器'  
			WHEN expose_estrus_type = ? THEN '自然发情'  
			WHEN expose_estrus_type = ? THEN '同期'  
        ELSE '未知'  
    	END AS statistic_method,
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS pregnant_count, -- 怀孕头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS empty_pregnant_count, -- 空怀头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS abortion_count, -- 流产头数  
		COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END) AS other_count, -- 其他数
		(  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END)  
		) AS total_count -- 总数
		`, pasturePb.ExposeEstrusType_Neck_Ring, pasturePb.ExposeEstrusType_Foot_Ring,
			pasturePb.ExposeEstrusType_Natural_Estrus, pasturePb.ExposeEstrusType_Same_Time,
			pasturePb.MatingResult_Pregnant, pasturePb.MatingResult_Empty, pasturePb.MatingResult_Abort,
			pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch, pasturePb.MatingResult_Pregnant,
			pasturePb.MatingResult_Empty, pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch,
		).Where("status = ?", pasturePb.IsShow_Ok).
		Where("pasture_id = ?", pastureId).
		Where("reality_day BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix).
		Where("cow_type = ?", req.CowType).
		Group("expose_estrus_type").
		Order("expose_estrus_type").
		Find(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}
	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodBreedingCompany(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	startDayTimeUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endDayTimeUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	pref := s.DB.Table(fmt.Sprintf("%s as a", new(model.EventMating).TableName())).
		Select(`
		b.producer AS statistic_method,
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS pregnant_count, -- 怀孕头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS empty_pregnant_count, -- 空怀头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS abortion_count, -- 流产头数  
		COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END) AS other_count, -- 其他数
		(  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END)  
		) AS total_count -- 总数
	`, pasturePb.MatingResult_Pregnant, pasturePb.MatingResult_Empty, pasturePb.MatingResult_Abort,
			pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch, pasturePb.MatingResult_Pregnant,
			pasturePb.MatingResult_Empty, pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch).
		Joins(fmt.Sprintf("left join %s as b on a.frozen_semen_number = b.bull_id ", new(model.FrozenSemen).TableName()))
	if req.CowType > 0 {
		pref.Where("a.cow_type = ?", req.CowType)
	}
	if req.CowKind > 0 {
		pref.Where("a.cow_kind = ?", req.CowKind)
	}

	if req.LactInterval > 0 {
		switch req.LactInterval {
		case pasturePb.CompareSymbol_Less_Than:
			pref.Where("a.lact < ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Less_Than_Or_Equal_To:
			pref.Where("a.lact <= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than:
			pref.Where("a.lact > ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than_Or_Equal_To:
			pref.Where("a.lact >= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Equal_To:
			pref.Where("a.lact = ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Not_Equal_To:
			pref.Where("a.lact != ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Between:
			pref.Where("a.lact BETWEEN ? AND ? ", req.LactIntervalStartValue, req.LactIntervalEndValue)
		default:
			return nil, xerr.Custom("错误的胎次区间符号")
		}
	}

	if err := pref.Where("a.status = ?", pasturePb.IsShow_Ok).Where("pasture_id = ?", pastureId).
		Where("a.reality_day BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix).
		Group("b.producer").
		Find(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodOperation(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	startDayTimeUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endDayTimeUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	pref := s.DB.Model(new(model.EventMating)).Select(`
		operation_name AS statistic_method,
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS pregnant_count, -- 怀孕头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS empty_pregnant_count, -- 空怀头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS abortion_count, -- 流产头数  
		COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END) AS other_count, -- 其他数
		(  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END)  
		) AS total_count -- 总数
	`, pasturePb.MatingResult_Pregnant, pasturePb.MatingResult_Empty, pasturePb.MatingResult_Abort,
		pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch, pasturePb.MatingResult_Pregnant,
		pasturePb.MatingResult_Empty, pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch)
	if req.CowType > 0 {
		pref.Where("cow_type = ?", req.CowType)
	}
	if req.CowKind > 0 {
		pref.Where("cow_kind = ?", req.CowKind)
	}

	if req.LactInterval > 0 {
		switch req.LactInterval {
		case pasturePb.CompareSymbol_Less_Than:
			pref.Where("lact < ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Less_Than_Or_Equal_To:
			pref.Where("lact <= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than:
			pref.Where("lact > ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than_Or_Equal_To:
			pref.Where("lact >= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Equal_To:
			pref.Where("lact = ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Not_Equal_To:
			pref.Where("lact != ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Between:
			pref.Where("lact BETWEEN ? AND ? ", req.LactIntervalStartValue, req.LactIntervalEndValue)
		default:
			return nil, xerr.Custom("错误的胎次区间符号")
		}
	}

	if err := pref.Where("status = ?", pasturePb.IsShow_Ok).
		Where("pasture_id = ?", pastureId).
		Where("reality_day BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix).
		Group("operation_id").
		Find(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodMatingInterval(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodBull(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	startDayTimeUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endDayTimeUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	pref := s.DB.Model(new(model.EventMating)).
		Select(`
		frozen_semen_number AS statistic_method,
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS pregnant_count, -- 怀孕头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS empty_pregnant_count, -- 空怀头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS abortion_count, -- 流产头数  
		COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END) AS other_count, -- 其他数
		(  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END)  
		) AS total_count -- 总数
	`, pasturePb.MatingResult_Pregnant, pasturePb.MatingResult_Empty, pasturePb.MatingResult_Abort,
			pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch, pasturePb.MatingResult_Pregnant,
			pasturePb.MatingResult_Empty, pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch)
	if req.CowType > 0 {
		pref.Where("cow_type = ?", req.CowType)
	}
	if req.CowKind > 0 {
		pref.Where("cow_kind = ?", req.CowKind)
	}

	if req.LactInterval > 0 {
		switch req.LactInterval {
		case pasturePb.CompareSymbol_Less_Than:
			pref.Where("lact < ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Less_Than_Or_Equal_To:
			pref.Where("lact <= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than:
			pref.Where("lact > ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than_Or_Equal_To:
			pref.Where("lact >= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Equal_To:
			pref.Where("lact = ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Not_Equal_To:
			pref.Where("lact != ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Between:
			pref.Where("lact BETWEEN ? AND ? ", req.LactIntervalStartValue, req.LactIntervalEndValue)
		default:
			return nil, xerr.Custom("错误的胎次区间符号")
		}
	}

	if err := pref.Where("status = ?", pasturePb.IsShow_Ok).
		Where("pasture_id = ?").
		Where("reality_day BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix).
		Group("frozen_semen_number").
		Find(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodBreedingCycle(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodWeek(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	startDayTimeUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endDayTimeUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	pref := s.DB.Model(new(model.EventMating)).
		Select(`
		CASE  
			DAYOFWEEK(DATE(FROM_UNIXTIME(reality_day)))
			WHEN 2 THEN '星期一'  
			WHEN 3 THEN '星期二'  
			WHEN 4 THEN '星期三'  
			WHEN 5 THEN '星期四'  
			WHEN 6 THEN '星期五'  
			WHEN 7 THEN '星期六'  
			WHEN 1 THEN '星期日'  
			ELSE '未知'  
    	END AS statistic_method,
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS pregnant_count, -- 怀孕头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS empty_pregnant_count, -- 空怀头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS abortion_count, -- 流产头数  
		COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END) AS other_count, -- 其他数
		(  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END)  
		) AS total_count -- 总数
	`, pasturePb.MatingResult_Pregnant, pasturePb.MatingResult_Empty, pasturePb.MatingResult_Abort,
			pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch, pasturePb.MatingResult_Pregnant,
			pasturePb.MatingResult_Empty, pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch)
	if req.CowType > 0 {
		pref.Where("cow_type = ?", req.CowType)
	}
	if req.CowKind > 0 {
		pref.Where("cow_kind = ?", req.CowKind)
	}

	if req.LactInterval > 0 {
		switch req.LactInterval {
		case pasturePb.CompareSymbol_Less_Than:
			pref.Where("lact < ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Less_Than_Or_Equal_To:
			pref.Where("lact <= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than:
			pref.Where("lact > ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Greater_Than_Or_Equal_To:
			pref.Where("lact >= ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Equal_To:
			pref.Where("lact = ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Not_Equal_To:
			pref.Where("lact != ?", req.LactIntervalStartValue)
		case pasturePb.CompareSymbol_Between:
			pref.Where("lact BETWEEN ? AND ? ", req.LactIntervalStartValue, req.LactIntervalEndValue)
		default:
			return nil, xerr.Custom("错误的胎次区间符号")
		}
	}

	if err := pref.Where("status = ?", pasturePb.IsShow_Ok).
		Where("pasture_id = ?", pastureId).
		Where("reality_day BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix).
		Group("statistic_method").
		Find(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return res, nil
}

func (s *StoreEntry) SingleFactorAnalysisMethodLact(pastureId int64, req *pasturePb.SingleFactorPregnancyRateRequest) ([]*pasturePb.SingleFactorPregnancyRateList, error) {
	res := make([]*pasturePb.SingleFactorPregnancyRateList, 0)
	startDayTimeUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endDayTimeUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	pref := s.DB.Model(new(model.EventMating)).
		Select(`
		lact AS statistic_method,
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS pregnant_count, -- 怀孕头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS empty_pregnant_count, -- 空怀头数  
		COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS abortion_count, -- 流产头数  
		COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END) AS other_count, -- 其他数
		(  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
        	COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END)  
		) AS total_count -- 总数
	`, pasturePb.MatingResult_Pregnant, pasturePb.MatingResult_Empty, pasturePb.MatingResult_Abort,
			pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch, pasturePb.MatingResult_Pregnant,
			pasturePb.MatingResult_Empty, pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch)
	if req.CowType > 0 {
		pref.Where("cow_type = ?", req.CowType)
	}
	if req.CowKind > 0 {
		pref.Where("cow_kind = ?", req.CowKind)
	}

	if err := pref.Where("status = ?", pasturePb.IsShow_Ok).
		Where("pasture_id = ?", pastureId).
		Where("reality_day BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix).
		Group("lact").
		Find(&res).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return res, nil
}

func (s *StoreEntry) MultipleFactorAnalysis(ctx context.Context, req *pasturePb.MultiFactorPregnancyRateRequest) (*model.MultiFactorPregnancyRateResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, err
	}
	startTimeUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endTimeUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	if startTimeUnix == 0 || endTimeUnix == 0 || endTimeUnix <= startTimeUnix {
		return nil, xerr.Custom("开始时间不能大于结束时间")
	}

	if req.XAxle == req.YAxle {
		return nil, xerr.Custom("X轴和Y轴不能相同")
	}

	if req.XAxle == 0 || req.YAxle == 0 {
		return nil, xerr.Custom("错误的XY轴数据")
	}

	pref := s.DB.Model(new(model.EventMating)).
		Select(`
			DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m') AS months,  
			operation_name,frozen_semen_number as bull,lact,mating_times,
			CASE expose_estrus_type
				WHEN 1 THEN '脖环揭发'  
				WHEN 2 THEN '脚环/计步器'  
				WHEN 3 THEN '自然发情'  
				WHEN 4 THEN '同期'  
				ELSE '未知'  
    		END AS expose_estrus_type,
			CASE DAYOFWEEK(DATE(FROM_UNIXTIME(reality_day)))
				WHEN 2 THEN '星期一'  
				WHEN 3 THEN '星期二'  
				WHEN 4 THEN '星期三'  
				WHEN 5 THEN '星期四'  
				WHEN 6 THEN '星期五'  
				WHEN 7 THEN '星期六'  
				WHEN 1 THEN '星期日'  
				ELSE '未知'  
			END AS week,
			COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS pregnant_count, -- 怀孕头数  
			COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS empty_pregnant_count, -- 空怀头数  
			COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) AS abortion_count, -- 流产头数  
			COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END) AS other_count, -- 其他数
			(  
				COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
				COUNT(DISTINCT CASE WHEN mating_result = ? AND mating_result_at > 0 THEN cow_id END) +  
				COUNT(DISTINCT CASE WHEN mating_result IN (?, ?) THEN cow_id END)  
			) AS total_count -- 总数
		`, pasturePb.MatingResult_Pregnant, pasturePb.MatingResult_Empty, pasturePb.MatingResult_Abort,
			pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch, pasturePb.MatingResult_Pregnant,
			pasturePb.MatingResult_Empty, pasturePb.MatingResult_Unknown, pasturePb.MatingResult_ReMatch).
		Where("pasture_id = ?", userModel.AppPasture.Id).
		Where("status = ?", pasturePb.IsShow_Ok).
		Where("cow_type = ?", req.CowType).
		Where("reality_day BETWEEN ? AND ?", startTimeUnix, endTimeUnix)

	if req.LactCompareSymbol > 0 {
		switch req.LactCompareSymbol {
		case pasturePb.CompareSymbol_Less_Than:
			pref.Where("lact < ?", req.LactStartValue)
		case pasturePb.CompareSymbol_Less_Than_Or_Equal_To:
			pref.Where("lact <= ?", req.LactStartValue)
		case pasturePb.CompareSymbol_Greater_Than:
			pref.Where("lact > ?", req.LactStartValue)
		case pasturePb.CompareSymbol_Greater_Than_Or_Equal_To:
			pref.Where("lact >= ?", req.LactStartValue)
		case pasturePb.CompareSymbol_Equal_To:
			pref.Where("lact = ?", req.LactStartValue)
		case pasturePb.CompareSymbol_Not_Equal_To:
			pref.Where("lact != ?", req.LactStartValue)
		case pasturePb.CompareSymbol_Between:
			pref.Where("lact BETWEEN ? AND ? ", req.LactStartValue, req.LactEndValue)
		}
	}

	multiFactorAnalysisMethod := s.MultiFactorAnalysisMethodMap()
	groupMap := make(map[string]string)
	for k1, v1 := range multiFactorAnalysisMethod {
		for k2, v2 := range multiFactorAnalysisMethod {
			groupMap[fmt.Sprintf("%d%d", k1, k2)] = fmt.Sprintf("%s,%s", v1, v2)
		}
	}

	list := make([]*model.MultiFactorPregnancyRateList, 0)
	if err = pref.Group(groupMap[fmt.Sprintf("%d%d", req.XAxle, req.YAxle)]).
		Order(fmt.Sprintf("%s", multiFactorAnalysisMethod[req.XAxle])).Find(&list).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	chart := &model.MultiFactorPregnancyRateChart{
		Header:          make([]string, 0),
		PregnantRateMap: make(map[string]map[string]string),
	}

	for _, v := range list {
		switch req.XAxle {
		case pasturePb.MultiFactorAnalysisMethod_Months:
			chart.Header = append(chart.Header, v.Months)
			v.StatisticMethod1 = v.Months
		case pasturePb.MultiFactorAnalysisMethod_Week:
			chart.Header = append(chart.Header, v.Week)
			v.StatisticMethod1 = v.Week
		case pasturePb.MultiFactorAnalysisMethod_Operation:
			chart.Header = append(chart.Header, v.OperationName)
			v.StatisticMethod1 = v.OperationName
		case pasturePb.MultiFactorAnalysisMethod_Bull:
			chart.Header = append(chart.Header, v.Bull)
			v.StatisticMethod1 = v.Bull
		case pasturePb.MultiFactorAnalysisMethod_Lact:
			chart.Header = append(chart.Header, v.Lact)
			v.StatisticMethod1 = v.Lact
		case pasturePb.MultiFactorAnalysisMethod_Mating_Times:
			chart.Header = append(chart.Header, v.MatingTimes)
			v.StatisticMethod1 = v.MatingTimes
		case pasturePb.MultiFactorAnalysisMethod_Breeding_Method:
			chart.Header = append(chart.Header, v.ExposeEstrusType)
			v.StatisticMethod1 = v.ExposeEstrusType
		}

		switch req.YAxle {
		case pasturePb.MultiFactorAnalysisMethod_Months:
			v.StatisticMethod2 = v.Months
		case pasturePb.MultiFactorAnalysisMethod_Week:
			v.StatisticMethod2 = v.Week
		case pasturePb.MultiFactorAnalysisMethod_Operation:
			v.StatisticMethod2 = v.OperationName
		case pasturePb.MultiFactorAnalysisMethod_Bull:
			v.StatisticMethod2 = v.Bull
		case pasturePb.MultiFactorAnalysisMethod_Lact:
			v.StatisticMethod2 = v.Lact
		case pasturePb.MultiFactorAnalysisMethod_Mating_Times:
			v.StatisticMethod2 = v.MatingTimes
		case pasturePb.MultiFactorAnalysisMethod_Breeding_Method:
			v.StatisticMethod2 = v.ExposeEstrusType
		}

		if chart.PregnantRateMap[v.StatisticMethod1] == nil {
			chart.PregnantRateMap[v.StatisticMethod1] = make(map[string]string)
		}

		if chart.KepMap == nil {
			chart.KepMap = make([]string, 0)
		}

		pregnantRate := float64(0)
		if v.EmptyPregnantCount+v.PregnantCount > 0 {
			pregnantRate = float64(v.PregnantCount) / float64(v.EmptyPregnantCount+v.PregnantCount)
		}
		v.PregnantRate = float32(util.RoundToTwoDecimals(pregnantRate * 100))
		spcRate := float32(0)
		if v.PregnantRate > 0 {
			spcRate = float32(util.RoundToTwoDecimals((float64(v.PregnantCount) / float64(v.TotalCount)) * 100))
		}
		v.SpcRate = spcRate
		chart.PregnantRateMap[v.StatisticMethod1][v.StatisticMethod2] = fmt.Sprintf("%.0f", v.PregnantRate)
		chart.KepMap = append(chart.KepMap, v.StatisticMethod2)
	}

	chart.Header = util.RemoveDuplicates(chart.Header)
	chart.KepMap = util.RemoveDuplicates(chart.KepMap)
	return &model.MultiFactorPregnancyRateResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &model.MultiFactorPregnancyRateData{
			Total: int32(len(list)),
			List:  list,
			Chart: chart,
		},
	}, nil
}