kpt-pasture/module/backend/calendar.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"
)

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

	todoList, err := s.CalendarToDoHistoryList(ctx, userModel.AppPasture.Id, "")
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	return &pasturePb.TodoCountResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.TodoCountData{Count: int32(len(todoList))},
	}, nil
}

func (s *StoreEntry) CalendarToDoHistoryList(ctx context.Context, pastureId int64, earNumber string) ([]*pasturePb.CalendarToDoList, error) {
	whereSql := ""
	if earNumber != "" {
		whereSql += fmt.Sprintf(` AND ear_number = '%s' `, earNumber)
	}
	whereSql += fmt.Sprintf(" AND pasture_id = %d ", pastureId)
	whereSql1 := whereSql + fmt.Sprintf(` AND end_day <= %d `, util.TimeParseLocalEndUnix(time.Now().Format(model.LayoutDate2)))

	calendarToDoList := make([]*pasturePb.CalendarToDoList, 0)
	sql := `SELECT a.cow_id,b.pen_name,a.calendar_type_name,a.calendar_type_kind as calendar_type,DATE_FORMAT(FROM_UNIXTIME(a.plan_day), '%Y-%m-%d') AS plan_day,
		a.remaining_days,b.lact,b.ear_number FROM (
		SELECT cow_id,plan_day,'免疫' as calendar_type_name,1 as calendar_type_kind,TIMESTAMPDIFF(DAY, NOW(), FROM_UNIXTIME(end_day)) AS remaining_days 
			FROM event_immunization_plan WHERE status = 2` + whereSql1 + `
		UNION ALL
		SELECT cow_id,plan_day,'同期' as calendar_type_name,2 as calendar_type_kind,TIMESTAMPDIFF(DAY, NOW(), FROM_UNIXTIME(end_day)) AS remaining_days 
			FROM event_cow_same_time WHERE status = 2` + whereSql1 + `
		UNION ALL
		SELECT cow_id,plan_day,'孕检' as calendar_type_name,4 as calendar_type_kind,TIMESTAMPDIFF(DAY, NOW(), FROM_UNIXTIME(end_day)) AS remaining_days 
			FROM event_pregnant_check WHERE status = 2` + whereSql1 + `
		UNION ALL
		SELECT cow_id,plan_day,'断奶' as calendar_type_name,6 as calendar_type_kind,TIMESTAMPDIFF(DAY, NOW(), FROM_UNIXTIME(end_day)) AS remaining_days 
			FROM event_weaning WHERE status = 2` + whereSql1 + `
		UNION ALL
		SELECT cow_id,plan_day,'配种' as calendar_type_name,8 as calendar_type_kind,TIMESTAMPDIFF(DAY, NOW(), FROM_UNIXTIME(end_day)) AS remaining_days 
			FROM event_mating WHERE status = 2` + whereSql1 + `
		UNION ALL
		SELECT cow_id,plan_day,'产犊' as calendar_type_name,9 as calendar_type_kind,TIMESTAMPDIFF(DAY, NOW(), FROM_UNIXTIME(end_day)) AS remaining_days 
			FROM event_calving WHERE status = 2` + whereSql1 + `
		UNION ALL
		SELECT cow_id,plan_day,'干奶' as calendar_type_name,9 as calendar_type_kind,TIMESTAMPDIFF(DAY, NOW(), FROM_UNIXTIME(end_day)) AS remaining_days 
			FROM event_dry_milk WHERE status = 2` + whereSql1 + `
		UNION ALL
		SELECT cow_id,disease_at as plan_day,'疾病' as calendar_type_name,7 as calendar_type_kind,0 AS remaining_days 
			FROM event_cow_disease WHERE health_status IN (2,3) ` + whereSql + `
	) as a JOIN cow b ON a.cow_id = b.id WHERE 1 = 1 `

	completeSql := fmt.Sprintf("%s ORDER BY a.plan_day DESC", sql)
	if err := s.DB.Raw(completeSql).Find(&calendarToDoList).Error; err != nil {
		return nil, err
	}
	return calendarToDoList, nil

}

// CalendarToDoList 获取日历待办列表
func (s *StoreEntry) CalendarToDoList(ctx context.Context, req *pasturePb.CalendarToDoRequest, pagination *pasturePb.PaginationModel) (*pasturePb.CalendarToDoResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	pastureId := userModel.AppPasture.Id
	calendarToDoList, err := s.CalendarToDoHistoryList(ctx, pastureId, req.EarNumber)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	nowTime := time.Now().Format(model.LayoutDate2)
	todayCompletedSql := `SELECT a.count as count,a.calendar_type_name as calendar_type_name,a.calendar_type_kind as calendar_type_kind FROM (
		SELECT count('cow_id') as count,'免疫' as calendar_type_name,1 as calendar_type_kind FROM event_immunization_plan WHERE status = 1 AND DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m-%d') = ? AND pasture_id = ?
		UNION ALL
		SELECT count('cow_id') as count,'同期' as calendar_type_name,2 as calendar_type_kind FROM event_cow_same_time WHERE status = 1 AND DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m-%d') = ? AND pasture_id = ?
		UNION ALL
		SELECT count('cow_id') as count,'孕检' as calendar_type_name,4 as calendar_type_kind FROM event_pregnant_check WHERE status = 1 AND DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m-%d') = ? AND pasture_id = ?
		UNION ALL
		SELECT count('cow_id') as count,'断奶' as calendar_type_name,6 as calendar_type_kind FROM event_weaning WHERE status = 1 AND DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m-%d') = ? AND pasture_id = ?
		UNION ALL
		SELECT count('cow_id') as count,'配种' as calendar_type_name,8 as calendar_type_kind FROM event_mating WHERE status = 1 AND DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m-%d') = ? AND pasture_id = ?
		UNION ALL
		SELECT count('cow_id') as count,'产犊' as calendar_type_name,9 as calendar_type_kind FROM event_calving WHERE status = 1 AND DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m-%d') = ? AND pasture_id = ?
		UNION ALL
		SELECT count('cow_id') as count,'干奶' as calendar_type_name,10 as calendar_type_kind FROM event_dry_milk WHERE status = 1 AND DATE_FORMAT(FROM_UNIXTIME(reality_day), '%Y-%m-%d') = ? AND pasture_id = ?
		UNION ALL
		SELECT count('cow_id') as count,'疾病' as calendar_type_name,7 as calendar_type_kind FROM event_cow_disease WHERE diagnosed_result = 4 AND DATE_FORMAT(FROM_UNIXTIME(curable_at), '%Y-%m-%d') = ? AND pasture_id = ?
	) as a `

	toDayCompletedList := make([]*model.TodayCompletedData, 0)
	if err = s.DB.Raw(todayCompletedSql, nowTime, pastureId, nowTime, pastureId, nowTime, pastureId, nowTime, pastureId, nowTime, pastureId, nowTime, pastureId, nowTime, pastureId, nowTime, pastureId).
		Find(&toDayCompletedList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	toDayCompletedCountMap := make(map[pasturePb.CalendarType_Kind]*pasturePb.ProgressList)
	for _, v := range toDayCompletedList {
		toDayCompletedCountMap[v.CalendarTypeKind] = &pasturePb.ProgressList{
			CalendarTypeKind: v.CalendarTypeKind,
			CalendarName:     v.CalendarTypeName,
			CompletedCount:   v.Count,
		}
	}

	list, total := Paginate(calendarToDoList, req, pagination)
	return &pasturePb.CalendarToDoResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.CalendarToDoData{
			List:     list,
			Progress: ProgressList(calendarToDoList, toDayCompletedCountMap),
			Total:    total,
			PageSize: pagination.PageSize,
			Page:     pagination.Page,
		},
	}, nil
}
func (s *StoreEntry) CalendarList(ctx context.Context, req *pasturePb.CalendarRequest) (*pasturePb.CalendarResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}

	calendarList := make([]*model.Calendar, 0)
	if err = s.DB.Model(new(model.Calendar)).
		Where("start_day BETWEEN ? AND ?", req.ShowStartDay, req.ShowEndDay).
		Where("pasture_id = ?", userModel.AppPasture.Id).
		Where("is_show = ?", pasturePb.IsShow_Ok).
		Find(&calendarList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}
	return &pasturePb.CalendarResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: model.CalendarSlice(calendarList).ToPB(),
	}, nil
}

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

	newCalendar := &model.Calendar{}
	if err = s.DB.Model(&model.Calendar{}).
		Where("calendar_type = ?", req.CalendarType).
		Where("start_day = ?", req.Start).
		Where("is_show = ?", pasturePb.IsShow_Ok).
		Where("pasture_id = ?", userModel.AppPasture.Id).
		First(newCalendar).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	if newCalendar.Id <= 0 {
		return nil, xerr.New("不存在该日历数据")
	}
	return s.getCalendarCowList(ctx, req.CalendarType, req.Start, pagination, userModel.AppPasture.Id)
}

func (s *StoreEntry) getCalendarCowList(
	ctx context.Context,
	calendarType pasturePb.CalendarType_Kind,
	showDay string,
	pagination *pasturePb.PaginationModel,
	pastureId int64,
) (interface{}, error) {
	req := &pasturePb.ItemsRequest{EndDay: showDay, CalendarType: calendarType, PastureId: int32(pastureId)}
	switch calendarType {
	case pasturePb.CalendarType_Immunisation: // 免疫
		return s.ImmunisationCowList(ctx, req, pagination)
	case pasturePb.CalendarType_PG, pasturePb.CalendarType_RnGH: // 同期
		return s.SameTimeCowList(ctx, req, pagination)
	case pasturePb.CalendarType_Pregnancy_Check: // 孕检
		return s.PregnancyCheckCowList(ctx, req, pagination)
	case pasturePb.CalendarType_WorkOrder: // 工作单
		return s.WorkOrderCowList(ctx, req, pagination)
	case pasturePb.CalendarType_Weaning: // 断奶
		return s.WeaningCowList(ctx, req, pagination)
	case pasturePb.CalendarType_Disease: // 治疗
		return s.TreatmentCowList(ctx, req, pagination)
	case pasturePb.CalendarType_Mating: // 配种
		return s.MatingCowList(ctx, req, pagination)
	case pasturePb.CalendarType_Calving: // 产犊
		return s.CalvingCowList(ctx, req, pagination)
	case pasturePb.CalendarType_DryMilk: // 干奶
		return s.DryMilkCowList(ctx, req, pagination)
	default:
		return nil, xerr.New("不支持的日历类型")
	}
}
func (s *StoreEntry) ImmunisationCowList(ctx context.Context, req *pasturePb.ItemsRequest, pagination *pasturePb.PaginationModel) (*pasturePb.ImmunizationItemsResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	eventImmunizationPlanList := make([]*model.EventImmunizationPlan, 0)
	count := int64(0)
	pref := s.DB.Table(fmt.Sprintf("%s as a", new(model.EventImmunizationPlan).TableName())).
		Select("a.id,a.cow_id,a.plan_day,a.plan_name,b.pen_name,b.day_age,b.ear_number,a.plan_id").
		Joins(fmt.Sprintf("JOIN %s AS b on a.cow_id = b.id", new(model.Cow).TableName())).
		Where("a.status = ?", pasturePb.IsShow_No).
		Where("a.pasture_id = ?", userModel.AppPasture.Id)
	if req.StartDay != "" && req.EndDay != "" {
		startTime := util.TimeParseLocalUnix(req.StartDay)
		endTime := util.TimeParseLocalEndUnix(req.EndDay)
		pref.Where("a.plan_day between ? and  ?", startTime, endTime)
	}

	if req.CowId > 0 {
		pref.Where("a.cow_id = ?", req.CowId)
	}

	if req.PlanId > 0 {
		pref.Where("a.plan_id = ?", req.PlanId)
	}

	if req.PenId > 0 {
		pref.Where("b.pen_id = ?", req.PenId)
	}

	if err = pref.Count(&count).
		Limit(int(pagination.PageSize)).
		Offset(int(pagination.PageOffset)).
		Order("a.plan_day DESC").
		Find(&eventImmunizationPlanList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return &pasturePb.ImmunizationItemsResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.ImmunizationItemsData{
			Total:      int32(count),
			Page:       pagination.Page,
			PageSize:   pagination.PageSize,
			HeaderSort: []string{"id", "cowId", "planDay", "planName", "penName", "dayAge", "earNumber", "planId"},
			Header: map[string]string{
				"id":        "编号",
				"cowId":     "牛号",
				"earNumber": "耳标号",
				"penName":   "栏舍",
				"dayAge":    "日龄",
				"planDay":   "免疫时间",
				"planName":  "免疫名称",
				"planId":    "免疫Id",
			},
			List: model.EventImmunizationPlanSlice(eventImmunizationPlanList).ToPB(),
		},
	}, nil

}

func (s *StoreEntry) SameTimeCowList(ctx context.Context, req *pasturePb.ItemsRequest, pagination *pasturePb.PaginationModel) (*pasturePb.SameTimeItemResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	sameTimeBodyList := make([]*model.SameTimeItemBody, 0)
	count := int64(0)
	pref := s.DB.Table(fmt.Sprintf("%s as a", new(model.EventCowSameTime).TableName())).
		Select(`a.id,a.cow_id,a.ear_number,a.pen_name,a.status,a.same_time_type,b.breed_status,a.same_time_name,a.plan_day,
		b.cow_type,b.day_age,b.calving_age,b.abortion_age,b.last_calving_at,b.last_abortion_at,b.lact,b.pen_name,b.mating_times`).
		Joins("left join cow as b on a.cow_id = b.id").
		Where("b.admission_status = ?", pasturePb.AdmissionStatus_Admission).
		Where("a.pasture_id = ?", userModel.AppPasture.Id).
		Where("a.status = ?", pasturePb.IsShow_No)

	if req.EndDay != "" {
		dateTime := util.TimeParseLocalEndUnix(req.EndDay)
		pref.Where("a.plan_day <= ?", dateTime)
	}

	if req.CowType > 0 {
		pref.Where("b.cow_type = ?", req.CowType)
	}

	if req.SameTimeId > 0 {
		pref.Where("a.same_time_id = ?", req.SameTimeId)
	}

	if req.SameTimeType > 0 {
		pref.Where("a.same_time_type = ?", req.SameTimeType)
	}

	if err = pref.Order("a.plan_day DESC").Count(&count).
		Limit(int(pagination.PageSize)).
		Offset(int(pagination.PageOffset)).
		Find(&sameTimeBodyList).Error; err != nil {
		return nil, xerr.WithStack(err)
	}
	breedStatusMap := s.CowBreedStatusMap()
	penMap := s.PenMap(ctx, int64(req.PastureId))
	sameTimeTypeMap := s.SameTimeTypeMap()
	return &pasturePb.SameTimeItemResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.SameTimeItemsData{
			Total:    int32(count),
			Page:     pagination.Page,
			PageSize: pagination.PageSize,
			HeaderSort: []string{"id", "cowId", "earNumber", "breedStatusName", "cowTypeName", "planDayAtFormat", "penName",
				"lact", "calvingAge", "abortionAge", "dayAge", "status", "sameTimeTypeName", "matingTimes", "calvingAtFormat",
				"abortionAtFormat", "sameTimeName"},
			Header: map[string]string{
				"id":               "编号",
				"cowId":            "牛号",
				"earNumber":        "耳标号",
				"breedStatusName":  "繁殖状态",
				"cowTypeName":      "牛只类型",
				"planDayAtFormat":  "执行日期",
				"penName":          "栏舍",
				"lact":             "胎次",
				"calvingAge":       "产后天数",
				"abortionAge":      "流产天数",
				"dayAge":           "日龄",
				"status":           "状态",
				"sameTimeTypeName": "处理方式",
				"matingTimes":      "本胎次配次",
				"calvingAtFormat":  "产犊日期",
				"abortionAtFormat": "流产日期",
				"sameTimeName":     "同期名称",
			},
			List: model.SameTimeBodySlice(sameTimeBodyList).ToPB(breedStatusMap, penMap, sameTimeTypeMap),
		},
	}, nil
}

func (s *StoreEntry) PregnancyCheckCowList(ctx context.Context, req *pasturePb.ItemsRequest, pagination *pasturePb.PaginationModel) (*pasturePb.PregnancyCheckItemsResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	newPregnancyCheckItems := make([]*pasturePb.PregnancyCheckItems, 0)
	var count int64
	pref := s.DB.Table(fmt.Sprintf("%s as a", new(model.EventPregnantCheck).TableName())).
		Select(`a.id,a.cow_id,a.ear_number,a.pen_id,a.status,b.breed_status,b.pen_name,b.cow_type,
		DATE_FORMAT(FROM_UNIXTIME(b.last_mating_at),'%Y-%m-%d')as mating_at_format,pregnancy_age,DATEDIFF(CURDATE(), FROM_UNIXTIME(last_mating_at)) AS mating_age,
		CASE a.pregnant_check_name
        WHEN 'pregnant_check_for_first' THEN '初检'
        WHEN 'pregnant_check_for_second' THEN '复检'
        ELSE '其他'
		END AS check_type_name,b.day_age,b.calving_age,b.abortion_age,a.bull_id`).
		Joins("left join cow as b on a.cow_id = b.id").
		Where("b.admission_status = ?", pasturePb.AdmissionStatus_Admission).
		Where("a.pasture_id = ?", userModel.AppPasture.Id).
		Where("a.status = ?", pasturePb.IsShow_No)

	if req.EndDay != "" {
		dateTime := util.TimeParseLocalEndUnix(req.EndDay)
		pref.Where("a.plan_day <= ?", dateTime)
	}

	if req.PenId > 0 {
		pref.Where("b.pen_id = ?", req.PenId)
	}

	if req.CowType > 0 {
		pref.Where("a.cow_type = ?", req.CowType)
	}

	if req.PregnantCheckType > 0 {
		pref.Where("a.pregnant_check_name = ?", model.PregnantCheckNameValueMap[req.PregnantCheckType])
	}

	if err = pref.Order("a.plan_day DESC").
		Count(&count).
		Limit(int(pagination.PageSize)).
		Offset(int(pagination.PageOffset)).
		Find(&newPregnancyCheckItems).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return &pasturePb.PregnancyCheckItemsResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.PregnancyCheckItemsData{
			Total:    int32(count),
			Page:     pagination.Page,
			PageSize: pagination.PageSize,
			HeaderSort: []string{"id", "cowId", "earNumber", "cowTypeName", "penName", "lact", "dayAge", "planDay",
				"checkTypeName", "status", "matingTimes", "calvingAtFormat", "matingAtFormat", "matingAge", "bullId", "pregnancyAge"},
			Header: map[string]string{
				"id":              "编号",
				"cowId":           "牛号",
				"earNumber":       "耳标号",
				"cowTypeName":     "牛只类型",
				"penName":         "栏舍",
				"lact":            "胎次",
				"dayAge":          "日龄",
				"planDay":         "孕检日期",
				"checkTypeName":   "孕检名称",
				"status":          "状态",
				"matingTimes":     "配次",
				"calvingAtFormat": "产检日期",
				"matingAtFormat":  "配种日期",
				"matingAge":       "配后天数",
				"bullId":          "配种公牛",
				"pregnancyAge":    "怀孕天数",
			},
			List: newPregnancyCheckItems,
		},
	}, nil
}

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

	weaningItems := make([]*pasturePb.WeaningItems, 0)
	count := int64(0)
	pref := s.DB.Table(fmt.Sprintf("%s as a", new(model.EventWeaning).TableName())).
		Select(`a.id,a.cow_id,ROUND(b.current_weight / 1000,2) as current_weight,
		DATE_FORMAT(FROM_UNIXTIME(a.plan_day), '%Y-%m-%d') AS plan_day_format,b.day_age,b.pen_name,
		b.ear_number,DATE_FORMAT(FROM_UNIXTIME(b.birth_at), '%Y-%m-%d') AS birth_at_format`).
		Joins("left join cow as b on a.cow_id = b.id").
		Where("b.admission_status = ?", pasturePb.AdmissionStatus_Admission).
		Where("a.status = ?", pasturePb.IsShow_No).
		Where("a.pasture_id = ?", userModel.AppPasture.Id)

	if req.EndDay != "" {
		dateTime := util.TimeParseLocalEndUnix(req.EndDay)
		pref.Where("a.plan_day <= ?", dateTime)
	}

	if err = pref.Order("a.plan_day DESC").Count(&count).
		Limit(int(pagination.PageSize)).
		Offset(int(pagination.PageOffset)).
		Find(&weaningItems).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return &pasturePb.WeaningItemsResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.WeaningItemsData{
			Total:      int32(count),
			Page:       pagination.Page,
			PageSize:   pagination.PageSize,
			HeaderSort: []string{"id", "cowId", "earNumber", "penName", "dayAge", "planDayFormat", "birthAtFormat", "currentWeight"},
			Header: map[string]string{
				"id":            "编号",
				"cowId":         "牛号",
				"earNumber":     "耳标号",
				"penName":       "栏舍",
				"dayAge":        "日龄",
				"planDayFormat": "断奶日期",
				"birthAtFormat": "出生日期",
				"currentWeight": "体重",
			},
			List: weaningItems,
		},
	}, nil
}

func (s *StoreEntry) MatingCowList(ctx context.Context, req *pasturePb.ItemsRequest, pagination *pasturePb.PaginationModel) (*pasturePb.MatingItemsResponse, error) {
	userModel, err := s.GetUserModel(ctx)
	if err != nil {
		return nil, xerr.WithStack(err)
	}
	matingItems := make([]*pasturePb.MatingItems, 0)
	count := int64(0)
	pref := s.DB.Table(fmt.Sprintf("%s as a", new(model.EventMating).TableName())).
		Select(`a.id,a.cow_id,a.status,a.ear_number,
		CASE a.expose_estrus_type
        	WHEN 1 THEN '脖环揭发'
        	WHEN 2 THEN '脚环/计步器'
        	WHEN 3 THEN '自然发情'
        	WHEN 4 THEN '同期'
        ELSE '其他'
		END AS expose_estrus_type_name,
		CASE 
        	WHEN last_calving_at = 0 THEN ""
        	ELSE DATE_FORMAT(FROM_UNIXTIME(last_calving_at), '%Y-%m-%d %H:%i:%s')
    	END AS last_calving_at_format,
		b.breed_status,b.cow_type,b.pen_id,b.day_age,b.calving_age,b.abortion_age,b.pen_name`).
		Joins("left join cow as b on a.cow_id = b.id").
		Where("a.pasture_id = ?", userModel.AppPasture.Id).
		Where("a.status = ?", pasturePb.IsShow_No)

	if req.EndDay != "" {
		dateTime := util.TimeParseLocalEndUnix(req.EndDay)
		pref.Where("a.plan_day <= ?", dateTime)
	}

	if req.PenId > 0 {
		pref.Where("b.pen_id = ?", req.PenId)
	}

	if req.EarNumber != "" {
		pref.Where("a.ear_number = ?", req.EarNumber)
	}

	if err = pref.Order("a.plan_day DESC").
		Count(&count).
		Limit(int(pagination.PageSize)).
		Offset(int(pagination.PageOffset)).
		Find(&matingItems).Error; err != nil {
		return nil, xerr.WithStack(err)
	}

	return &pasturePb.MatingItemsResponse{
		Code: http.StatusOK,
		Msg:  "ok",
		Data: &pasturePb.MatingItemsData{
			Total:      int32(count),
			Page:       pagination.Page,
			PageSize:   pagination.PageSize,
			HeaderSort: []string{"id", "cowId", "earNumber", "dayAge", "lact", "penName", "breedStatusName", "cowTypeName", "calvingAge", "abortionAge", "exposeEstrusTypeName", "lastCalvingAtFormat"},
			Header: map[string]string{
				"id":                   "编号",
				"cowId":                "牛号",
				"earNumber":            "耳标号",
				"breedStatusName":      "繁殖状态",
				"cowTypeName":          "牛只类型",
				"penName":              "栏舍",
				"lact":                 "胎次",
				"calvingAge":           "产后天数",
				"abortionAge":          "流产天数",
				"dayAge":               "日龄",
				"status":               "状态",
				"exposeEstrusTypeName": "发情揭发方式",
				"lastCalvingAtFormat":  "产犊日期",
			},
			List: matingItems,
		},
	}, nil
}