kpt-pasture/module/backend/analysis_other.go

package backend

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

	"gorm.io/gorm"

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

// PregnancyReport 孕检报告
func (s *StoreEntry) PregnancyReport(ctx context.Context, req *pasturePb.PregnancyReportRequest, pagination *pasturePb.PaginationModel) (*pasturePb.PregnancyReportResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	startDayUnix := util.TimeParseLocalUnix(req.StartDayTime)
	endDayUnix := util.TimeParseLocalEndUnix(req.EndDayTime)
	if startDayUnix > endDayUnix || startDayUnix == 0 || endDayUnix == 0 {
		return nil, xerr.Custom("开始时间不能大于结束时间")
	}

	eventPregnantCheckList := make([]*model.EventPregnantCheck, 0)
	pref := s.DB.Model(new(model.EventPregnantCheck)).
		Where("status = ?", pasturePb.IsShow_Ok).
		Where("cow_type = ?", req.CowType).
		Where("pasture_id = ?", userModel.AppPasture.Id)

	if startDayUnix > 0 && endDayUnix > 0 && endDayUnix >= startDayUnix {
		pref = pref.Where("create_time BETWEEN  ? AND ?", startDayUnix, endDayUnix)
	}

	if req.PregnantCheckResult > 0 {
		pref = pref.Where("pregnant_check_result = ?", req.PregnantCheckResult)
	}

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

	pregnantCheckResultMap := s.PregnantCheckResultMap()
	pregnantCheckMethodMap := s.PregnantCheckMethodMap()
	return &pasturePb.PregnancyReportResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.PregnancyReportData{
			List:     model.EventPregnantCheckSlice(eventPregnantCheckList).ToPB3(pregnantCheckResultMap, pregnantCheckMethodMap),
			Total:    int32(count),
			PageSize: pagination.PageSize,
			Page:     pagination.Page,
		},
	}, nil
}

// CalvingReport 产犊报告
func (s *StoreEntry) CalvingReport(ctx context.Context, req *pasturePb.CalvingReportRequest) (*pasturePb.CalvingReportResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	lastDayOfMonth, err := util.GetLastDayOfMonth(req.EndDayTime)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	endDayTimeUnix := util.TimeParseLocalEndUnix(lastDayOfMonth)
	startDayTimeUnix := util.TimeParseLocalUnix(fmt.Sprintf("%s-01", req.StartDayTime))
	if startDayTimeUnix == 0 || endDayTimeUnix == 0 || endDayTimeUnix <= startDayTimeUnix {
		return nil, xerr.Custom("开始时间不能大于结束时间")
	}

	eventCalving1 := make([]*pasturePb.CalvingReportTable, 0)
	pref1 := s.DB.Model(new(model.EventCalving)).
		Select(fmt.Sprint(`DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m') AS statistic_method,cow_kind,`)+fmt.Sprintf(
			`SUM(child_number) AS total_count,
			SUM(CASE WHEN child_number > 1 THEN child_number ELSE 0 END) AS twins,
			SUM(CASE WHEN pregnancy_age <= %d THEN 1 ELSE 0 END) AS premature_labor_count,
			SUM(CASE WHEN pregnancy_age >= %d THEN 1 ELSE 0 END) AS late_labor_count,
			SUM(CASE WHEN child_number > 1 THEN 1 ELSE 0 END) AS multiple_birth_count,
			COUNT(DISTINCT CASE WHEN child_number > 1 THEN cow_id END) AS cow_multiple_birth_count,
			count(*) as cow_total_count`, req.PrematureLabor, req.LateLabor),
		).Where("status = ?", pasturePb.IsShow_Ok).Where("pasture_id = ?", userModel.AppPasture.Id).
		Where("reality_day BETWEEN  ? AND ? ", startDayTimeUnix, endDayTimeUnix)

	if req.AnalysisMethod > 0 {
		switch req.AnalysisMethod {
		case pasturePb.CalvingAnalysisMethod_Months:
			pref1.Group("statistic_method").Order("statistic_method")
		case pasturePb.CalvingAnalysisMethod_CowKind:
			pref1.Group("cow_kind").Order("cow_kind")
		}
	}

	if err = pref1.Find(&eventCalving1).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	eventCalving2 := make([]*pasturePb.CalvingReportTable, 0)
	pref2 := s.DB.Model(new(model.CalvingCalf)).
		Select(fmt.Sprint(`DATE_FORMAT(FROM_UNIXTIME(birth_at), '%Y-%m') AS statistic_method, cow_kind,
			SUM(CASE WHEN sex = 1 THEN 1 ELSE 0 END) AS bulls,
    		SUM(CASE WHEN sex = 2 THEN 1 ELSE 0 END) AS cows,
    		SUM(CASE WHEN is_live = 1 THEN 1 ELSE 0 END) AS survive_count,
    		SUM(CASE WHEN is_live = 2 THEN 1 ELSE 0 END) AS die_count,
    		SUM(CASE WHEN is_live = 2 AND sex = 1 THEN 1 ELSE 0 END) AS bulls_die_count,
			SUM(CASE WHEN is_live = 2 AND sex = 2 THEN 1 ELSE 0 END) AS cows_die_count,
    		SUM(CASE WHEN is_adoption = 1 THEN 1 ELSE 0 END) AS adopt_count`)).
		Where("birth_at BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix).
		Where("pasture_id = ?", userModel.AppPasture.Id)

	if req.AnalysisMethod > 0 {
		switch req.AnalysisMethod {
		case pasturePb.CalvingAnalysisMethod_Months:
			pref2.Group("statistic_method").Order("statistic_method")
		case pasturePb.CalvingAnalysisMethod_CowKind:
			pref2.Group("cow_kind").Order("cow_kind")
		}
	}

	if err = pref2.Find(&eventCalving2).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	// 合并数据集
	for _, v1 := range eventCalving1 {
		for _, v2 := range eventCalving2 {
			if v1.StatisticMethod != v2.StatisticMethod && v1.CowKind != v2.CowKind {
				continue
			}
			v1.Bulls = v2.Bulls
			v1.Cows = v2.Cows
			v1.SurviveCount = v2.SurviveCount
			v1.DieCount = v2.DieCount
			v1.BullsDieCount = v2.BullsDieCount
			v1.CowsDieCount = v2.CowsDieCount
			v1.AdoptCount = v2.AdoptCount
		}
	}

	cowKindMap := s.CowKindMap()
	for _, v := range eventCalving1 {
		if req.AnalysisMethod == pasturePb.CalvingAnalysisMethod_CowKind {
			v.StatisticMethod = cowKindMap[v.CowKind]
		}
		if v.TotalCount > 0 {
			v.CowsRate = float32(util.RoundToTwoDecimals(float64(v.Cows) / float64(v.TotalCount) * 100))
			v.DieRate = float32(util.RoundToTwoDecimals(float64(v.DieCount) / float64(v.TotalCount) * 100))
			v.BullsRate = float32(util.RoundToTwoDecimals(float64(v.Bulls) / float64(v.TotalCount) * 100))
			v.SurviveRate = float32(util.RoundToTwoDecimals(float64(v.SurviveCount) / float64(v.TotalCount) * 100))
		}

		if v.CowTotalCount > 0 {
			v.TwinsRate = float32(util.RoundToTwoDecimals(float64(v.MultipleBirthCount) / float64(v.CowTotalCount) * 100))
		}

		if v.Bulls > 0 {
			v.BullsDieRate = float32(util.RoundToTwoDecimals(float64(v.BullsDieCount) / float64(v.Bulls) * 100))
		}

		if v.Cows > 0 {
			v.CowsDieRate = float32(util.RoundToTwoDecimals(float64(v.CowsDieCount) / float64(v.Cows) * 100))
		}
		v.NormalLaborCount = v.TotalCount - (v.PrematureLaborCount + v.LateLaborCount)
		if v.NormalLaborCount <= 0 {
			v.NormalLaborCount = 0
		}
	}

	return &pasturePb.CalvingReportResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.CalvingReportData{
			List:  eventCalving1,
			Total: int32(len(eventCalving1)),
		},
	}, nil
}

// DiseaseCureReport 疾病治愈率报告
func (s *StoreEntry) DiseaseCureReport(ctx context.Context, req *pasturePb.DiseaseCureRateRequest) (*pasturePb.DiseaseCureRateResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	lastDayOfMonth, err := util.GetLastDayOfMonth(req.EndDayTime)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	endDayTimeUnix := util.TimeParseLocalEndUnix(lastDayOfMonth)
	startDayTimeUnix := util.TimeParseLocalUnix(fmt.Sprintf("%s-01", req.StartDayTime))
	if startDayTimeUnix == 0 || endDayTimeUnix == 0 || endDayTimeUnix <= startDayTimeUnix {
		return nil, xerr.Custom("开始时间不能大于结束时间")
	}

	diseaseCureRateList1 := make([]*pasturePb.DiseaseCureRateList, 0)
	diseaseCureRateList2 := make([]*pasturePb.DiseaseCureRateList, 0)
	pref1, err := s.query1(userModel.AppPasture.Id, req.CowType, startDayTimeUnix, endDayTimeUnix)
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	pref2, err := s.query2(userModel.AppPasture.Id, startDayTimeUnix, endDayTimeUnix)
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	switch req.AnalysisMethod {
	case pasturePb.DiseaseAnalysisMethod_Months:
		pref1.Where("diagnosed_at > 0").Group("months").Order("months")
		pref2.Where("treatment_at > 0").Group("months").Order("months")
	case pasturePb.DiseaseAnalysisMethod_Disease_Category:
		pref1.Where("disease_type > 0").Group("disease_type").Order("disease_type")
		pref2.Where("disease_type > 0").Group("disease_type").Order("disease_type")
	case pasturePb.DiseaseAnalysisMethod_Disease:
		pref1.Where("diagnose_id > 0").Group("diagnose_id").Order("diagnose_id")
		pref2.Where("disease_id > 0").Group("disease_id").Order("disease_id")
	case pasturePb.DiseaseAnalysisMethod_Operator:
		pref1.Where("diagnose_operation_id > 0").Group("diagnose_operation_id").Order("diagnose_operation_id")
		pref2.Where("operation_id > 0").Group("operation_id").Order("operation_id")
	default:
		return nil, xerr.Custom("错误的统计方式")
	}

	if err = pref1.Find(&diseaseCureRateList1).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	if err = pref2.Find(&diseaseCureRateList2).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	// 处理数据
	processDiseaseCureRateList(diseaseCureRateList1, diseaseCureRateList2, req.AnalysisMethod)
	chart := &pasturePb.DiseaseCureRateChart{
		Headers:          make([]string, 0),
		DiseaseCureCount: make([]int32, 0),
		DiseaseCureRate:  make([]float32, 0),
		DiseaseCount:     make([]int32, 0),
	}

	for _, v := range diseaseCureRateList1 {
		switch req.AnalysisMethod {
		case pasturePb.DiseaseAnalysisMethod_Months:
			v.StatisticMethod = v.Months
		case pasturePb.DiseaseAnalysisMethod_Disease_Category:
			diseaseTypeMap := s.DiseaseTypeMap()
			v.StatisticMethod = diseaseTypeMap[v.DiseaseType]
		case pasturePb.DiseaseAnalysisMethod_Disease:
			v.StatisticMethod = v.DiseaseName
		case pasturePb.DiseaseAnalysisMethod_Operator:
			v.StatisticMethod = v.OperationName
		case pasturePb.DiseaseAnalysisMethod_Prescription:
			v.StatisticMethod = v.PrescriptionName
		}

		chart.Headers = append(chart.Headers, v.StatisticMethod)
		chart.DiseaseCureCount = append(chart.DiseaseCureCount, v.DiseaseTreatmentCount)
		diseaseCureRate := float32(0)
		if v.DiseaseTreatmentCount > 0 {
			diseaseCureRate = float32(util.RoundToTwoDecimals(float64(v.DiseaseCureCount) / float64(v.DiseaseCount) * 100))
		}
		chart.DiseaseCureRate = append(chart.DiseaseCureRate, diseaseCureRate)
		chart.DiseaseCount = append(chart.DiseaseCount, v.DiseaseCount)
	}

	return &pasturePb.DiseaseCureRateResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.DiseaseCureRateData{
			List:  diseaseCureRateList1,
			Chart: chart,
		},
	}, nil
}

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

	lastDayOfMonth, err := util.GetLastDayOfMonth(req.EndDay)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	endDayTimeUnix := util.TimeParseLocalEndUnix(lastDayOfMonth)
	startDayTimeUnix := util.TimeParseLocalUnix(fmt.Sprintf("%s-01", req.StartDay))
	if startDayTimeUnix == 0 || endDayTimeUnix == 0 || endDayTimeUnix <= startDayTimeUnix {
		return nil, xerr.Custom("开始时间不能大于结束时间")
	}

	list := make([]*pasturePb.SaleCowReportList, 0)
	pref := s.DB.Model(new(model.EventSale)).
		Select(
			`SUM(sale_cow_count) AS sale_all_count,
			SUM(sale_all_amount) AS sale_all_amount,
			SUM(sale_cow_count) AS sale_all_count,
			SUM(sale_all_weight) AS sale_all_weight`,
		).Where("sale_at BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix).
		Where("pasture_id = ?", userModel.AppPasture.Id)
	if req.AnalysisMethod == pasturePb.SaleCowAnalysisMethod_Months {
		pref.Select(`DATE_FORMAT(FROM_UNIXTIME(sale_at), '%Y-%m') AS statisticMethod`)
	}

	if req.AnalysisMethod == pasturePb.SaleCowAnalysisMethod_Dealer {
		pref.Select(`dealer_name as statisticMethod`)
	}

	if err = pref.Group("statisticMethod").Order("statisticMethod ASC").Find(&list).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	chart := &pasturePb.SaleCowReportChart{
		Headers:           make([]string, 0),
		SaleMount:         make([]float32, 0),
		SaleWeight:        make([]float32, 0),
		SaleCount:         make([]int32, 0),
		AverageSaleWeight: make([]float32, 0),
	}
	for i, v := range list {
		if v.SaleAllCount > 0 && v.SaleAllWeight > 0 {
			v.SaleAvgWeight = float32(util.RoundToTwoDecimals(float64(v.SaleAllWeight / float32(v.SaleAllCount))))
		}
		chart.Headers = append(chart.Headers, v.StatisticMethod)
		chart.SaleMount = append(chart.SaleMount, v.SaleAllMount)
		chart.SaleWeight = append(chart.SaleWeight, v.SaleAllWeight)
		chart.SaleCount = append(chart.SaleCount, v.SaleAllCount)
		chart.AverageSaleWeight = append(chart.AverageSaleWeight, v.SaleAvgWeight)
		v.Id = int32(i)
	}

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

func (s *StoreEntry) query1(pastureId int64, cowType pasturePb.CowType_Kind, startDayTimeUnix, endDayTimeUnix int64) (*gorm.DB, error) {
	pref := s.DB.Model(new(model.EventCowDisease)).
		Select(
			`DATE_FORMAT(FROM_UNIXTIME(diagnosed_at), '%Y-%m') AS months,
				diagnose_id as disease_id,
				diagnose_name as disease_name,
				diagnose_operation_id as operation_id,
				diagnose_operation_name as operation_name,
				disease_type,
				COUNT(DISTINCT CASE WHEN health_status = ? THEN cow_id END) AS disease_treatment_count, -- 治疗头数  
				COUNT(DISTINCT CASE WHEN health_status = ? AND curable_at > 0 THEN cow_id END) AS disease_cure_count, -- 治愈头数  
				COUNT(DISTINCT CASE WHEN diagnosed_result = ? THEN cow_id END) AS disease_count, -- 发病数  
				COUNT(DISTINCT CASE WHEN health_status IN (?, ?) THEN cow_id END) AS die_out_count, -- 死淘数  
				COUNT(DISTINCT CASE   
						   WHEN health_status = ?  
						   AND TIMESTAMPDIFF(DAY, FROM_UNIXTIME(disease_at), FROM_UNIXTIME(curable_at)) <= 7   
						   THEN cow_id   
					   END) AS seven_cure_count, -- 7天内治愈头数  
				COUNT(DISTINCT CASE   
						   WHEN health_status = ?  
						   AND TIMESTAMPDIFF(DAY, FROM_UNIXTIME(disease_at), FROM_UNIXTIME(curable_at)) <= 14   
						   THEN cow_id   
					   END) AS seventeen_cure_count, -- 14天内治愈头数 
				COUNT(DISTINCT CASE   
						   WHEN health_status = ?  
						   AND TIMESTAMPDIFF(DAY, FROM_UNIXTIME(disease_at), FROM_UNIXTIME(curable_at)) <= 30  
						   THEN cow_id   
					   END) AS thirty_cure_count -- 30天内治愈头数
				`,
			pasturePb.HealthStatus_Treatment, pasturePb.HealthStatus_Curable, pasturePb.IsShow_Ok, pasturePb.HealthStatus_Out,
			pasturePb.HealthStatus_Dead, pasturePb.HealthStatus_Curable, pasturePb.HealthStatus_Curable, pasturePb.HealthStatus_Curable,
		).
		Where("diagnosed_result = ?", pasturePb.IsShow_Ok).
		Where("pasture_id = ?", pastureId).
		Where("diagnosed_at BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix)
	if cowType > 0 {
		pref.Where("cow_type = ?", cowType)
	}

	return pref, nil
}

func (s *StoreEntry) query2(pastureId int64, startDayTimeUnix, endDayTimeUnix int64) (*gorm.DB, error) {
	pref := s.DB.Model(new(model.EventCowTreatment)).
		Select(`
		DATE_FORMAT(FROM_UNIXTIME(treatment_at), '%Y-%m') AS months,
		disease_id,
		disease_name,
		disease_type,
		prescription_id,
		prescription_name,
		operation_id,
		operation_name,
		COUNT(DISTINCT cow_id) AS disease_treatment_count`,
		).Where("treatment_at BETWEEN ? AND ?", startDayTimeUnix, endDayTimeUnix).
		Where("pasture_id = ?", pastureId)
	return pref, nil
}

// 构建哈希表
func buildMap(list []*pasturePb.DiseaseCureRateList, method pasturePb.DiseaseAnalysisMethod_Kind) map[interface{}]*pasturePb.DiseaseCureRateList {
	m := make(map[interface{}]*pasturePb.DiseaseCureRateList)
	for _, v := range list {
		var key interface{}
		switch method {
		case pasturePb.DiseaseAnalysisMethod_Months:
			key = v.Months
		case pasturePb.DiseaseAnalysisMethod_Disease_Category:
			key = v.DiseaseType
		case pasturePb.DiseaseAnalysisMethod_Disease:
			key = v.DiseaseId
		case pasturePb.DiseaseAnalysisMethod_Operator:
			key = v.OperationId
		case pasturePb.DiseaseAnalysisMethod_Prescription:
			key = v.PrescriptionId
		}
		m[key] = v
	}
	return m
}

// 更新 DiseaseTreatmentCount
func updateDiseaseTreatmentCount(v1 *pasturePb.DiseaseCureRateList, v2 *pasturePb.DiseaseCureRateList) {
	v1.DiseaseTreatmentCount = v2.DiseaseTreatmentCount
}

// 主逻辑
func processDiseaseCureRateList(diseaseCureRateList1, diseaseCureRateList2 []*pasturePb.DiseaseCureRateList, analysisMethod pasturePb.DiseaseAnalysisMethod_Kind) {
	if len(diseaseCureRateList1) == 0 || len(diseaseCureRateList2) == 0 {
		return
	}

	map2 := buildMap(diseaseCureRateList2, analysisMethod)
	for i := range diseaseCureRateList1 {
		v1 := diseaseCureRateList1[i]
		var key interface{}
		switch analysisMethod {
		case pasturePb.DiseaseAnalysisMethod_Months:
			key = v1.Months
		case pasturePb.DiseaseAnalysisMethod_Disease_Category:
			key = v1.DiseaseType
		case pasturePb.DiseaseAnalysisMethod_Disease:
			key = v1.DiseaseId
		case pasturePb.DiseaseAnalysisMethod_Operator:
			key = v1.OperationId
		case pasturePb.DiseaseAnalysisMethod_Prescription:
			key = v1.PrescriptionId
		default:
			continue // 处理无效的 AnalysisMethod
		}
		if v2, ok := map2[key]; ok {
			updateDiseaseTreatmentCount(v1, v2)
		}
	}
}