hz_crm_api/models/cygx/industrial_activity_group_subject.go

package cygx

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/utils"
	"time"
)

type CygxIndustrialActivityGroupSubject struct {
	Id                  int       `orm:"column(id);pk" description:"主键ID"`
	ActivityId          int       `description:"活动ID"`
	IndustrialSubjectId int       `description:"cygx_industrial_subject表的文章ID"`
	Source              int       `description:"来源，1 活动，2专项调研"`
	CreateTime          time.Time `description:"创建时间"`
}

type SubjectActivityGroupManagementRep struct {
	IndustrialSubjectId int    `description:"产业id"`
	SubjectName         string `description:"标的名称"`
	ActivityId          int    `description:"活动ID"`
}

// 列表
func GetSubjectActivityGroupManagementList(activityId, source int) (items []*SubjectActivityGroupManagementRep, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			s.subject_name,
			s.industrial_subject_id 
			FROM
			cygx_industrial_activity_group_subject AS ag
			INNER JOIN cygx_industrial_subject AS s ON s.industrial_subject_id = ag.industrial_subject_id 
			WHERE
			ag.activity_id = ? AND ag.source = ?`
	_, err = o.Raw(sql, activityId, source).QueryRows(&items)
	return
}

// 列表
func GetSubjectActivityGroupManagementListByActivityIdsArr(activityIds []int, source int) (items []*SubjectActivityGroupManagementRep, err error) {
	lenArr := len(activityIds)
	if lenArr == 0 {
		return
	}
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
    	    ag.activity_id,
			s.subject_name,
			s.industrial_subject_id 
			FROM
			cygx_industrial_activity_group_subject AS ag
			INNER JOIN cygx_industrial_subject AS s ON s.industrial_subject_id = ag.industrial_subject_id 
			WHERE
				 ag.activity_id IN (` + utils.GetOrmInReplace(lenArr) + `) AND ag.source = ?`
	_, err = o.Raw(sql, activityIds, source).QueryRows(&items)
	return
}

// 列表
func GetSubjectActivityGroupManagementListBySubjectId(subjectId, source int) (items []*SubjectActivityGroupManagementRep, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
    	    ag.activity_id,
			s.subject_name,
			s.industrial_subject_id 
			FROM
			cygx_industrial_activity_group_subject AS ag
			INNER JOIN cygx_industrial_subject AS s ON s.industrial_subject_id = ag.industrial_subject_id 
			WHERE
			ag.industrial_subject_id = ? AND ag.source = ?`
	_, err = o.Raw(sql, subjectId, source).QueryRows(&items)
	return
}

func GetActivityIdsByIndustrialSubjectId(industrialSubjectId string) (activityId string, err error) {
	sql := ` SELECT
			GROUP_CONCAT( DISTINCT activity_id ORDER BY id ASC SEPARATOR ',' ) AS activity_id 
			FROM
			cygx_industrial_activity_group_subject WHERE industrial_subject_id IN (` + industrialSubjectId + `)`
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sql).QueryRow(&activityId)
	return
}

type SubjectlActivityGroupResp struct {
	ActivityId             int    `description:"活动ID"`
	IndustrialManagementId int    `description:"cygx_industrial_management表的主键ID"`
	IndustrialSubjectId    int    `description:"标的id"`
	SubjectName            string `description:"标的名称"`
}

// 列表
func GetSubjectActivityGroupListByactivityIds(activityIds string) (items []*SubjectlActivityGroupResp, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
			sg.*,
			s.subject_name,
			s.industrial_management_id 
		FROM
			cygx_industrial_activity_group_subject AS sg
			INNER JOIN cygx_industrial_subject AS s ON s.industrial_subject_id = sg.industrial_subject_id 
		WHERE
			1 = 1 
			AND sg.activity_id IN (` + activityIds + `)  GROUP BY sg.activity_id,sg.industrial_subject_id`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type IndustrySubjectActCountGroupByType struct {
	ActivityType  string `json:"activity_type" description:"活动类型名称"`
	ActivityCount int    `json:"activity_count" description:"关联的活动数"`
}

// GetIndustrySubjectActCountGroupByType 获取标的关联的活动数-根据活动类型分组
func GetIndustrySubjectActCountGroupByType(subjectId int) (list []*IndustrySubjectActCountGroupByType, err error) {
	sqlCount := `SELECT
					COUNT(1) AS activity_count,
					CASE
						WHEN a.chart_permission_id = 31 THEN
							"研选活动"
						ELSE
							"弘则活动"
						END AS activity_type
				FROM
					cygx_industrial_activity_group_subject AS gsub
				INNER JOIN cygx_activity AS a ON a.activity_id = gsub.activity_id
				INNER JOIN cygx_industrial_subject AS sub ON sub.industrial_subject_id = gsub.industrial_subject_id
				WHERE
					gsub.industrial_subject_id = ? AND a.publish_status = 1
				GROUP BY
					activity_type`
	o := orm.NewOrmUsingDB("hz_cygx")
	_, err = o.Raw(sqlCount, subjectId).QueryRows(&list)
	return
}