kpt-pasture/module/backend/analysis.go

package backend

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

	"gitee.com/xuyiping_admin/pkg/xerr"

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

// GrowthCurve 生长曲线 获取图表数据
func (s *StoreEntry) GrowthCurve(ctx context.Context, req *pasturePb.SearchGrowthCurvesRequest) (*pasturePb.GrowthCurvesResponse, error) {
	// 查询数据
	cowList := make([]*model.Cow, 0)
	pref := s.DB.Model(new(model.Cow)).Where("admission_status = ?", pasturePb.AdmissionStatus_Admission)
	if req.GetCowId() != "" {
		pref.Where("id IN ?", strings.Split(req.CowId, ","))
	}

	if len(req.BirthDate) == 2 && req.BirthDate[0] != "" && req.BirthDate[1] != "" {
		t0, _ := time.Parse(time.RFC3339, req.BirthDate[0])
		t1, _ := time.Parse(time.RFC3339, req.BirthDate[1])

		pref.Where("birth_at BETWEEN ? AND ?", t0.Unix(), t1.Unix()+86399)
	}

	if err := pref.Find(&cowList).Error; err != nil {
		return nil, err
	}
	penList, err := s.GetPenList(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	// 计算图表数据
	chartsList := &pasturePb.Charts{
		CowId:  make([]int32, 0),
		Weight: make([]float32, 0),
		DayAge: make([]int32, 0),
	}
	cowData := make([]*pasturePb.CowList, 0)
	for _, cow := range cowList {
		currentWeight := float32(cow.CurrentWeight) / 100
		penName := ""
		for _, v := range penList {
			if int64(cow.PenId) != v.Id {
				continue
			}
			penName = v.Name
		}
		cowData = append(cowData, &pasturePb.CowList{
			CowId:                  int32(cow.Id),
			EarNumber:              cow.EarNumber,
			DayAge:                 cow.GetDayAge(),
			PenName:                penName,
			CurrentWeight:          currentWeight,
			BirthAt:                int32(cow.BirthAt),
			BirthWeight:            float32(cow.BirthWeight) / 100,
			LastWeightAt:           int32(cow.LastWeightAt),
			DailyWeightGain:        float32(cow.GetDayWeight() / 100),
			AverageDailyWeightGain: float32(cow.GetAverageDailyWeight() / 100),
		})

		chartsList.CowId = append(chartsList.CowId, int32(cow.Id))
		chartsList.Weight = append(chartsList.Weight, currentWeight)
		chartsList.DayAge = append(chartsList.DayAge, cow.GetDayAge())
	}

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

}

func (s *StoreEntry) WeightRange(ctx context.Context, req *pasturePb.WeightRangeRequest) (*pasturePb.WeightRangeResponse, error) {
	cowWeightRange := make([]*model.CowWeightRange, 0)

	prefix := s.DB.Model(new(model.Cow)).Where("admission_status = ?", pasturePb.AdmissionStatus_Admission)
	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,
			Message: "ok",
			Data: &pasturePb.WeightRangeData{
				CowList: make([]*pasturePb.CowList, 0),
				WeightBarChart: &pasturePb.WeightBarChart{
					Header: make([]string, 0),
					Data:   make([]int32, 0),
				},
			},
		}, 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)
	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)
	}

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

	penMap := s.PenMap(ctx)
	return &pasturePb.WeightRangeResponse{
		Code:    http.StatusOK,
		Message: "ok",
		Data: &pasturePb.WeightRangeData{
			CowList: model.CowSlice(cowList).WeightRangeToPB(penMap),
			WeightBarChart: &pasturePb.WeightBarChart{
				Header: header,
				Data:   data,
			},
		},
	}, nil
}

func (s *StoreEntry) MatingTimely(ctx context.Context, req *pasturePb.MatingTimelyRequest) (*model.MatingTimelyResponse, error) {
	matingTimelyChart := make([]*model.MatingTimelyChart, 0)
	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
			UNION ALL
			SELECT calving_age,cow_type, reality_day, '1' AS lact_group
			FROM event_mating
			WHERE lact = 1 AND status = 1
			UNION ALL
			SELECT calving_age,cow_type,  reality_day, '2' AS lact_group
			FROM event_mating
			WHERE lact = 2 AND status = 1
			UNION ALL
			SELECT calving_age, cow_type, reality_day, '3+' AS lact_group
			FROM event_mating
			WHERE lact >= 3 AND status = 1
		) 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,
			Message: "ok",
			Data: &model.MatingTimelyData{
				CowList: make([]*pasturePb.CowList, 0),
				Chart:   chart,
			},
		}, nil
	}
	for _, v := range matingTimelyChart {
		switch v.LactGroup {
		case "0":
			chart.Lact0 = append(chart.Lact0, []string{fmt.Sprintf("%d", v.CalvingAge), v.RealityDay})
		case "1":
			chart.Lact1 = append(chart.Lact1, []string{fmt.Sprintf("%d", v.CalvingAge), v.RealityDay})
		case "2":
			chart.Lact2 = append(chart.Lact2, []string{fmt.Sprintf("%d", v.CalvingAge), v.RealityDay})
		case "3+":
			chart.Lact3 = append(chart.Lact3, []string{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,
		Message: "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) {
	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("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,
			Message: "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 && req.BarId > 0 {
		prefList.Where("pen_id IN ?", []int32{req.BarId})
	} else {
		prefList.Where("pen_id IN ?", req.PenId)
	}

	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)
	return &pasturePb.PenWeightResponse{
		Code:    http.StatusOK,
		Message: "ok",
		Data: &pasturePb.PenWeightData{
			CowList:  model.CowSlice(cowList).ToPB2(penMap, 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) {

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

	lastDayForMonth := make([]string, 0)
	for _, v := range dayTimeList {
		lastDayTime, _ := util.GetLastDayOfMonth(v)
		lastDayForMonth = append(lastDayForMonth, lastDayTime)
	}
	// 历史每月怀孕牛头数量
	cowPregnantMonthList := make([]*model.CowPregnantMonth, 0)
	if err = s.DB.Model(new(model.Cow)).
		Select(`
			COUNT(cow_id) AS cow_count,
			DATE_FORMAT(FROM_UNIXTIME(updated_at),'%Y-%m-%d') as month`,
		).Where("status = ?", pasturePb.IsShow_Ok).
		Where("cow_type = ?", req.CowType).
		Where("DATE_FORMAT(FROM_UNIXTIME(`created_at`),'%Y-%m-%d') IN ?", lastDayForMonth).
		Group("month").Find(&cowPregnantMonthList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}
	// 历史每月流产牛头数量
	cowAbortionMonthList := make([]*model.CowPregnantMonth, 0)
	if err = s.DB.Model(new(model.EventAbortion)).
		Select(`
			COUNT(cow_id) AS cow_count,
			DATE_FORMAT(FROM_UNIXTIME(abortion_at),'%Y-%m') as month`,
		).Where("status = ?", pasturePb.IsShow_Ok).
		Where("cow_type = ?", req.CowType).
		Where("DATE_FORMAT(FROM_UNIXTIME(`created_at`),'%Y-%m') IN = ?", dayTimeList).
		Group("month").Find(&cowAbortionMonthList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	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 _, v2 := range cowAbortionMonthList {
		pregnantCountMonth := int32(0)
		for _, v := range cowPregnantMonthList {
			if v.Month == v2.Month {
				v.CowCount = v2.CowCount
				pregnantCountMonth = v.CowCount
			}
		}

		abortionRateMonth := float64(0)
		if pregnantCountMonth > 0 && v2.CowCount > 0 {
			abortionRateMonth = util.Ceil(float64(pregnantCountMonth) / float64(v2.CowCount) * 100)
		}

		chart.Header = append(chart.Header, v2.Month)
		chart.AbortionCountMonth = append(chart.AbortionCountMonth, v2.CowCount)
		chart.PregnantCountMonth = append(chart.PregnantCountMonth, pregnantCountMonth)
		chart.AbortionRateMonth = append(chart.AbortionRateMonth, float32(abortionRateMonth))

		table = append(table, &pasturePb.AbortionRateTable{
			AbortionCount: v2.CowCount,
			MonthName:     v2.Month,
			PregnantCount: pregnantCountMonth,
			AbortionRate:  float32(abortionRateMonth),
		})
	}

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