kpt-pasture/module/backend/analysis.go

package backend

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

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

// WeightScatterPlot 体重散点图 获取图表数据
func (s *StoreEntry) WeightScatterPlot(ctx context.Context, req *pasturePb.SearchGrowthCurvesRequest, pagination *pasturePb.PaginationModel) (*pasturePb.GrowthCurvesResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.Custom("当前用户信息错误,请退出重新登录")
	}

	// 查询数据
	cowList := make([]*model.Cow, 0)
	pref := s.DB.Model(new(model.Cow)).
		Where("admission_status = ?", pasturePb.AdmissionStatus_Admission).
		Where("pasture_id = ?", userModel.AppPasture.Id)
	if req.EarNumber != "" {
		pref.Where("ear_number = ?", req.EarNumber)
	}

	if len(req.PenIds) > 0 {
		pref.Where("pen_id IN (?)", req.PenIds)
	}

	if len(req.AdmissionDate) == 2 && len(req.AdmissionDate[0]) > 0 && len(req.AdmissionDate[1]) > 0 {
		t0, _ := util.TimeParseLocal(model.LayoutDate2, req.AdmissionDate[0])
		t1, _ := util.TimeParseLocal(model.LayoutDate2, req.AdmissionDate[1])
		pref.Where("admission_at BETWEEN ? AND ?", t0.Unix(), t1.Unix()+86399)
	}

	if err = pref.Find(&cowList).Error; err != nil {
		return nil, err
	}
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	// 计算图表数据
	chartsList := &pasturePb.Charts{
		CowId:        make([]int32, 0),
		Weight:       make([]float32, 0),
		AdmissionAge: make([]int32, 0),
	}
	cowData := make([]*pasturePb.CowList, 0)
	for _, cow := range cowList {
		currentWeight := float32(cow.CurrentWeight) / 1000
		admissionAtFormat := ""
		if cow.AdmissionAt > 0 {
			admissionAtFormat = time.Unix(cow.AdmissionAt, 0).Local().Format(model.LayoutDate2)
		}
		cowData = append(cowData, &pasturePb.CowList{
			CowId:                    int32(cow.Id),
			EarNumber:                cow.EarNumber,
			DayAge:                   cow.GetDayAge(),
			PenName:                  cow.PenName,
			CurrentWeight:            currentWeight,
			BirthAt:                  int32(cow.BirthAt),
			BirthWeight:              float32(cow.BirthWeight) / 1000,
			LastWeightAt:             int32(cow.LastWeightAt),
			AverageDailyWeightGain:   float32(cow.GetAverageDailyWeight()),
			PreviousStageDailyWeight: float32(cow.GetPreviousStageDailyWeight()),
			AdmissionAge:             cow.GetAdmissionAge(),
			AdmissionAtFormat:        admissionAtFormat,
		})

		chartsList.CowId = append(chartsList.CowId, int32(cow.Id))
		chartsList.Weight = append(chartsList.Weight, currentWeight)
		chartsList.AdmissionAge = append(chartsList.AdmissionAge, cow.GetAdmissionAge())
	}

	// 返回数据
	return &pasturePb.GrowthCurvesResponse{
		Code: http.StatusOK,
		Msg:  "success",
		Data: &pasturePb.GrowthCurveData{
			Table:  cowData,
			Charts: chartsList,
		},
	}, nil

}

func (s *StoreEntry) WeightRange(ctx context.Context, req *pasturePb.WeightRangeRequest, pagination *pasturePb.PaginationModel) (*pasturePb.WeightRangeResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	cowWeightRange := make([]*model.CowWeightRange, 0)
	prefix := s.DB.Model(new(model.Cow)).
		Where("admission_status = ?", pasturePb.AdmissionStatus_Admission).
		Where("pasture_id = ?", userModel.AppPasture.Id)
	if req.CowKind > 0 {
		prefix.Where("cow_kind = ?", req.CowKind)
	}
	if err = prefix.Select(`
		CASE 
			WHEN current_weight BETWEEN 0 AND 50000 THEN '0-50'  
			WHEN current_weight BETWEEN 50001 AND 100000 THEN '51-100' 
			WHEN current_weight BETWEEN 100001 AND 150000 THEN '101-150' 
			WHEN current_weight BETWEEN 150001 AND 200000 THEN '151-200' 
			WHEN current_weight BETWEEN 200001 AND 250000 THEN '201-250' 
			WHEN current_weight BETWEEN 250001 AND 300000 THEN '251-300' 
			WHEN current_weight BETWEEN 300001 AND 350000 THEN '301-350' 
			WHEN current_weight BETWEEN 350001 AND 400000 THEN '351-400' 
			WHEN current_weight BETWEEN 400001 AND 450000 THEN '401-450' 
			WHEN current_weight BETWEEN 450001 AND 500000 THEN '451-500' 
			WHEN current_weight BETWEEN 500001 AND 550000 THEN '500-550' 
			WHEN current_weight BETWEEN 550001 AND 600000 THEN '551-600' 
			WHEN current_weight BETWEEN 600001 AND 650000 THEN '601-650' 
			WHEN current_weight BETWEEN 650001 AND 700000 THEN '651-700' 
			WHEN current_weight BETWEEN 700001 AND 750000 THEN '701-750' 
			ELSE '750+'  
		END AS weight_range, 
		COUNT(*) AS count `,
	).Group("weight_range").
		Order("MIN(current_weight)").
		Find(&cowWeightRange).Error; err != nil {
		return nil, err
	}

	if len(cowWeightRange) == 0 {
		return &pasturePb.WeightRangeResponse{
			Code: http.StatusOK,
			Msg:  "ok",
			Data: &pasturePb.WeightRangeData{
				CowList: make([]*pasturePb.CowList, 0),
				WeightBarChart: &pasturePb.WeightBarChart{
					Header: make([]string, 0),
					Data:   make([]int32, 0),
				},
				Total:    0,
				PageSize: pagination.PageSize,
				Page:     pagination.Page,
			},
		}, nil
	}
	header := make([]string, 0)
	data := make([]int32, 0)
	for _, v := range cowWeightRange {
		header = append(header, v.WeightRange)
		data = append(data, v.Count)
	}
	// 牛只详情列表
	pref := s.DB.Model(new(model.Cow)).
		Where("admission_status = ?", pasturePb.AdmissionStatus_Admission).
		Where("pasture_id = ?", userModel.AppPasture.Id)
	if req.CowKind > 0 {
		pref.Where("cow_kind = ?", req.CowKind)
	}
	cowList := make([]*model.Cow, 0)
	if req.MinWeight >= 0 && req.MaxWeight >= 0 && req.MinWeight < req.MaxWeight {
		pref.Where("current_weight BETWEEN  ? AND ? ", req.MinWeight*1000, req.MaxWeight*1000)
	}

	var count int64
	if err = pref.Order("id desc").
		Count(&count).
		Limit(int(pagination.PageSize)).
		Offset(int(pagination.PageOffset)).
		Find(&cowList).Error; err != nil {
		return nil, err
	}

	penMap := s.PenMap(ctx, userModel.AppPasture.Id)
	return &pasturePb.WeightRangeResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.WeightRangeData{
			CowList: model.CowSlice(cowList).WeightRangeToPB(penMap),
			WeightBarChart: &pasturePb.WeightBarChart{
				Header: header,
				Data:   data,
			},
			Total:    int32(count),
			PageSize: pagination.PageSize,
			Page:     pagination.Page,
		},
	}, nil
}

func (s *StoreEntry) MatingTimely(ctx context.Context, req *pasturePb.MatingTimelyRequest) (*model.MatingTimelyResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	matingTimelyChart := make([]*model.MatingTimelyChart, 0)
	pastureWhereSql := fmt.Sprintf(" AND pasture_id = %d", userModel.AppPasture.Id)
	sql := `SELECT calving_age,cow_type, DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m-%d') AS reality_day, lact_group
		FROM (
			SELECT calving_age, cow_type,reality_day, '0' AS lact_group
			FROM event_mating
			WHERE lact = 0 AND status = 1 ` + pastureWhereSql + `
			UNION ALL
			SELECT calving_age,cow_type, reality_day, '1' AS lact_group
			FROM event_mating
			WHERE lact = 1 AND status = 1 ` + pastureWhereSql + `
			UNION ALL
			SELECT calving_age,cow_type,  reality_day, '2' AS lact_group
			FROM event_mating
			WHERE lact = 2 AND status = 1 ` + pastureWhereSql + `
			UNION ALL
			SELECT calving_age, cow_type, reality_day, '3+' AS lact_group
			FROM event_mating
			WHERE lact >= 3 AND status = 1 ` + pastureWhereSql + `
		) AS subquery WHERE 1 = 1 `

	whereSql := ""
	if req.CowType > 0 {
		whereSql += fmt.Sprintf("AND cow_type = %d ", req.CowType)
	}

	if req.StartDayAt > 0 && req.EndDayAt > 0 {
		whereSql += fmt.Sprintf("AND reality_day BETWEEN %d AND %d", req.StartDayAt, req.EndDayAt)
	}
	if err = s.DB.Raw(fmt.Sprintf("%s %s", sql, whereSql)).Find(&matingTimelyChart).Error; err != nil {
		return nil, err
	}

	chart := &model.CowMatingChart{
		Lact0: make([][]string, 0),
		Lact1: make([][]string, 0),
		Lact2: make([][]string, 0),
		Lact3: make([][]string, 0),
	}
	if len(matingTimelyChart) == 0 {
		return &model.MatingTimelyResponse{
			Code: http.StatusOK,
			Msg:  "ok",
			Data: &model.MatingTimelyData{
				CowList: make([]*pasturePb.CowList, 0),
				Chart:   chart,
			},
		}, nil
	}
	for _, v := range matingTimelyChart {
		t, _ := util.TimeParseLocal(model.LayoutDate2, v.RealityDay)
		switch v.LactGroup {
		case "0":
			chart.Lact0 = append(chart.Lact0, []string{fmt.Sprintf("%d", t.Day()), fmt.Sprintf("%d", v.CalvingAge), v.RealityDay})
		case "1":
			chart.Lact1 = append(chart.Lact1, []string{fmt.Sprintf("%d", t.Day()), fmt.Sprintf("%d", v.CalvingAge), v.RealityDay})
		case "2":
			chart.Lact2 = append(chart.Lact2, []string{fmt.Sprintf("%d", t.Day()), fmt.Sprintf("%d", v.CalvingAge), v.RealityDay})
		case "3+":
			chart.Lact3 = append(chart.Lact3, []string{fmt.Sprintf("%d", t.Day()), fmt.Sprintf("%d", v.CalvingAge), v.RealityDay})
		}
	}

	// 牛只详情列表
	eventMatingList := make([]*model.EventMating, 0)
	pref := s.DB.Model(new(model.EventMating)).
		Where("status = ?", pasturePb.IsShow_Ok)
	if req.CowType > 0 {
		pref.Where("cow_type = ?", req.CowType)
	}

	if req.StartDayAt > 0 && req.EndDayAt > 0 {
		pref.Where("reality_day BETWEEN ? AND ?", req.StartDayAt, req.EndDayAt)
	}

	if err = pref.Find(&eventMatingList).Error; err != nil {
		return nil, err
	}

	return &model.MatingTimelyResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &model.MatingTimelyData{
			CowList: model.EventMatingSlice(eventMatingList).ToPB2(),
			Chart:   chart,
		},
	}, nil
}

func (s *StoreEntry) PenWeight(ctx context.Context, req *pasturePb.PenWeightRequest, pagination *pasturePb.PaginationModel) (*pasturePb.PenWeightResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	penWeightList := make([]*model.PenWeight, 0)
	pref := s.DB.Model(new(model.Cow)).
		Select(`
			pen_id,
			CEILING(AVG(current_weight) / 1000 ) AS avg_weight,
			CEILING(SUM(current_weight) / 1000 ) AS all_weight,
			COUNT(*) AS cow_count`,
		).
		Where("pasture_id = ?", userModel.AppPasture.Id).
		Where("admission_status = ?", pasturePb.AdmissionStatus_Admission)
	if len(req.PenId) > 0 && req.BarId <= 0 {
		pref.Where("pen_id IN ?", req.PenId)
	}
	if err = pref.Group("pen_id").
		Order("pen_id").
		Find(&penWeightList).Error; err != nil {
		return nil, err
	}

	chart := &pasturePb.PenWeightChart{
		Header:    make([]string, 0),
		AllWeight: make([]int32, 0),
		AvgWeight: make([]int32, 0),
		CowCount:  make([]int32, 0),
	}
	if len(penWeightList) <= 0 {
		return &pasturePb.PenWeightResponse{
			Code: http.StatusOK,
			Msg:  "ok",
			Data: &pasturePb.PenWeightData{
				CowList: make([]*pasturePb.CowList, 0),
				Chart:   chart,
			},
		}, nil
	}
	cowList := make([]*model.Cow, 0)
	var count int64 = 0
	prefList := s.DB.Model(new(model.Cow)).
		Where("admission_status = ?", pasturePb.AdmissionStatus_Admission)

	if len(req.PenId) > 0 {
		prefList.Where("pen_id IN (?)", req.PenId)
	} else if req.BarId > 0 {
		prefList.Where("pen_id = ?", []int32{req.BarId})
	}

	if err = prefList.Count(&count).Limit(int(pagination.PageSize)).
		Offset(int(pagination.PageOffset)).Order("pen_id").
		Find(&cowList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	penMap := s.PenMap(ctx, userModel.AppPasture.Id)
	return &pasturePb.PenWeightResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.PenWeightData{
			CowList:  model.CowSlice(cowList).ToPB2(penWeightList),
			Total:    int32(count),
			Page:     pagination.Page,
			PageSize: pagination.PageSize,
			Chart:    model.PenWeightSlice(penWeightList).ToPB(penMap),
		},
	}, nil
}

func (s *StoreEntry) AbortionRate(ctx context.Context, req *pasturePb.AbortionRateRequest) (*pasturePb.AbortionRateResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	dayTimeList, err := util.GetMonthsInRange(req.StartDayTime, req.EndDayTime)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	// 2. 创建月份映射表用于快速查找
	monthMap := make(map[string]*pasturePb.AbortionRateTable)
	for _, month := range dayTimeList {
		monthMap[month] = &pasturePb.AbortionRateTable{
			MonthName:     month,
			AbortionCount: 0,
			PregnantCount: 0,
			AbortionRate:  0,
		}
	}

	// 历史每月怀孕牛头数量
	cowPregnantMonthList := make([]*model.CowPregnantMonth, 0)
	pref := s.DB.Model(new(model.CowPregnant)).
		Select(`COUNT(cow_id) AS cow_count,date_time as month`).
		Where("cow_type = ?", req.CowType).
		Where("pasture_id = ?", userModel.AppPasture.Id)

	if req.CowType == pasturePb.CowType_Breeding_Calf && req.Lact >= 0 && req.Lact <= 3 {
		pref.Where("lact = ?", req.Lact)
	} else {
		pref.Where("lact > ?", req.Lact)
	}

	if err = pref.Group("month").
		Find(&cowPregnantMonthList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	// 更新月份映射表中的怀孕数量
	for _, v := range cowPregnantMonthList {
		if table, exists := monthMap[v.Month]; exists {
			table.PregnantCount = v.CowCount
		}
	}

	// 历史每月流产牛头数量
	cowAbortionMonthList := make([]*model.CowPregnantMonth, 0)
	pref2 := s.DB.Model(new(model.EventAbortion)).
		Select(`COUNT(cow_id) AS cow_count,DATE_FORMAT(FROM_UNIXTIME(abortion_at),'%Y-%m') as month`).
		Where("cow_type = ?", req.CowType).
		Where("DATE_FORMAT(FROM_UNIXTIME(abortion_at),'%Y-%m') IN ?", dayTimeList)

	if req.Lact >= 0 && req.CowType == pasturePb.CowType_Breeding_Calf {
		pref2.Where("lact = ?", req.Lact)
	}

	if err = pref2.Group("month").
		Find(&cowAbortionMonthList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	// 更新月份映射表中的流产数量和流产率
	for _, v := range cowAbortionMonthList {
		if table, exists := monthMap[v.Month]; exists {
			table.AbortionCount = v.CowCount
			if table.PregnantCount > 0 {
				table.AbortionRate = float32(util.RoundToTwoDecimals(float64(v.CowCount) / float64(table.PregnantCount) * 100))
			}
		}
	}

	chart := &pasturePb.AbortionRateChart{
		Header:             make([]string, 0),
		AbortionCountMonth: make([]int32, 0),
		PregnantCountMonth: make([]int32, 0),
		AbortionRateMonth:  make([]float32, 0),
	}

	table := make([]*pasturePb.AbortionRateTable, 0)
	for _, month := range dayTimeList {
		data := monthMap[month]
		chart.Header = append(chart.Header, month) // 添加月份到Header
		chart.AbortionCountMonth = append(chart.AbortionCountMonth, data.AbortionCount)
		chart.PregnantCountMonth = append(chart.PregnantCountMonth, data.PregnantCount)
		chart.AbortionRateMonth = append(chart.AbortionRateMonth, data.AbortionRate)
		table = append(table, data)
	}

	return &pasturePb.AbortionRateResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.AbortionRateData{
			Chart: chart,
			Table: table,
		},
	}, nil
}