hz_crm_api/models/cygx/industrial_subject.go

package cygx

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

type IndustrialSubjectAdd struct {
	SubjectName            string `description:"标的名称"`
	IndustrialManagementId int    `description:"产业id"`
	Source                 int    `description:"来源,1正常添加，2：通过文章添加，3通过活动添加(默认为1)"`
}

type IndustrialSubjectDelete struct {
	IndustrialSubjectId int `orm:"column(industrial_subject_id);pk" description:"标的id"`
}

type CygxIndustrialSubject struct {
	IndustrialSubjectId    int       `orm:"column(industrial_subject_id);pk" description:"标的id"`
	IndustrialManagementId int       `description:"产业id"`
	SubjectName            string    `description:"标的名称"`
	CreateTime             time.Time `description:"创建时间"`
	Source                 int       `description:"来源,1正常添加，2：通过文章添加，3通过活动添加(默认为1)"`
}

type CygxIndustrialSubjectNum struct {
	IndustrialSubjectId    int       `orm:"column(industrial_subject_id);pk" description:"标的id"`
	IndustrialManagementId int       `description:"产业id"`
	SubjectName            string    `description:"标的名称"`
	CreateTime             time.Time `description:"创建时间"`
	ArtNum                 int       `description:"文章数量"`
	IsRelevance            bool      `description:"是否存在关联文章"`
}

type GetIndustrialSubjectList struct {
	List []*CygxIndustrialSubject
}

type GetIndustrialSubjectNumList struct {
	List []*CygxIndustrialSubjectNum
}

func GetcygxIndustrialSubject(industrialManagementId int) (items []*CygxIndustrialSubject, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT subject_name FROM cygx_industrial_subject  WHERE industrial_management_id = ?`
	_, err = o.Raw(sql, industrialManagementId).QueryRows(&items)
	return
}

func UpdateIndustrialManagementSubjectNames(industrialManagementId int, industryName string) (err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	var nameSub string
	listSub, err := GetcygxIndustrialSubject(industrialManagementId)
	if err != nil {
		return
	}
	for _, v := range listSub {
		nameSub += v.SubjectName + ","
	}
	nameSub += industryName
	sql := `UPDATE cygx_industrial_management SET subject_names = ? WHERE industrial_management_id = ?`
	_, err = o.Raw(sql, nameSub, industrialManagementId).Exec()
	return
}

// 新增
func AddIndustrialSubject(item *CygxIndustrialSubject) (newId int64, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	newId, err = o.Insert(item)
	if err != nil {
		return
	}
	return
}

// 批量新增
func AddIndustrialSubjectList(items []*CygxIndustrialSubject) (newIdStr string, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	for _, v := range items {
		newId, errSub := to.Insert(v)
		newIdStr += strconv.Itoa(int(newId)) + ","
		if errSub != nil {
			return
		}
	}
	newIdStr = strings.Trim(newIdStr, ",")
	return
}

// 列表
func GetIndustrialSubjectAll(IndustrialManagementId int) (items []*CygxIndustrialSubjectNum, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT *,
		( SELECT COUNT( 1 ) FROM cygx_industrial_article_group_subject AS sub_g WHERE sub_g.industrial_subject_id = sub.industrial_subject_id ) AS art_num
		FROM
		cygx_industrial_subject AS sub 
		WHERE
		sub.industrial_management_id = ? `
	_, err = o.Raw(sql, IndustrialManagementId).QueryRows(&items)
	return
}

// 获取数量
func GetIndustrialSubjectCount(condition string, pars []interface{}) (count int, err error) {
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_industrial_subject WHERE 1=1 `
	if condition != "" {
		sqlCount += condition
	}
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sqlCount, pars).QueryRow(&count)
	return
}

// 修改
func EditIndustrialSubject(item *CygxIndustrialSubject) (err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `UPDATE cygx_industrial_subject SET  subject_name=? WHERE industrial_subject_id=? `
	_, err = o.Raw(sql, item.SubjectName, item.IndustrialSubjectId).Exec()
	return
}

// 删除数据
func DeleteIndustrialSubject(industrialSubjectId int) (err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := ` DELETE FROM cygx_industrial_subject WHERE industrial_Subject_id = ?`
	_, err = o.Raw(sql, industrialSubjectId).Exec()
	return
}

type ArtGroupIndustrialSubjectRep struct {
	SubjectName string `description:"标的名称"`
}

type ArtGroupIndustrialSubjectRepList struct {
	List []*ArtGroupIndustrialSubjectRep
}

// 获取文章关联的标的数量
func GetArtGroupIndustrialSubject(reportId int) (item []*ArtGroupIndustrialSubjectRep, err error) {
	sql := ` SELECT sub.subject_name FROM cygx_industrial_article_group_subject AS sub_g
		INNER JOIN cygx_industrial_subject AS sub ON sub.industrial_subject_id = sub_g.industrial_subject_id
		WHERE
		sub_g.cygx_article_id = ?`
	o := orm.NewOrmUsingDB("hz_cygx")
	_, err = o.Raw(sql, reportId).QueryRows(&item)
	return
}

// 获取标的关联的文章数量
func GetIndustrialSubjectGroupArtCount(industrialSubjectId int) (count int, err error) {
	sqlCount := ` SELECT COUNT(1) AS count  FROM cygx_industrial_article_group_subject WHERE industrial_subject_id=? `
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sqlCount, industrialSubjectId).QueryRow(&count)
	return
}

// 列表
func GetIndustrialSubjectAllByIds(condition string) (items []*CygxIndustrialSubjectNum, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT *,
		( SELECT COUNT( 1 ) FROM cygx_industrial_article_group_subject AS sub_g WHERE sub_g.industrial_subject_id = sub.industrial_subject_id ) AS art_num
		FROM
		cygx_industrial_subject AS sub 
		WHERE 1=1 ` + condition
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetIndustrialSubjectDetailById(industrialSubjectId int) (item *CygxIndustrialSubject, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT * FROM cygx_industrial_subject WHERE industrial_subject_id = ? `
	err = o.Raw(sql, industrialSubjectId).QueryRow(&item)
	return
}

func GetIndustrialSubjectDetailByIds(industrialSubjectIds []int) (list []*CygxIndustrialSubject, err error) {
	lenArr := len(industrialSubjectIds)
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT * FROM cygx_industrial_subject WHERE  industrial_subject_id IN (` + utils.GetOrmInReplace(lenArr) + `) `
	_, err = o.Raw(sql, industrialSubjectIds).QueryRows(&list)
	return
}

// 通过名称获取相同标的
func GetListIndustrialSubjectDetailByName(IndustrialSubjectName string) (list []*CygxIndustrialSubject, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT * FROM cygx_industrial_subject WHERE  subject_name = ? `
	_, err = o.Raw(sql, IndustrialSubjectName).QueryRows(&list)
	return
}

func GetIndustrialSubjectDetailByName(IndustrialSubjectName string) (item *CygxIndustrialSubject, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT * FROM cygx_industrial_subject WHERE subject_name = ? `
	err = o.Raw(sql, IndustrialSubjectName).QueryRow(&item)
	return
}

// 列表
func GetIndustrialSubjectListName(condition string) (items []*ArtGroupIndustrialSubjectRep, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT s.*
			FROM
			cygx_industrial_subject AS s
			INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id =s.industrial_management_id
			WHERE m.chart_permission_id = 31 `
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

type CygxIndustrialSubjectListRep struct {
	IndustrialSubjectId    int    `description:"标的id"`
	IndustrialManagementId int    `description:"产业id"`
	SubjectName            string `description:"标的名称"`
	IndustryName           string `description:"分析师名称"`
}

type CygxIndustrialSubjectList struct {
	List []*CygxIndustrialSubjectListRep
}

type IndustrialNameListRep struct {
	List []*SubjectNameListRep
}

type SubjectNameListRep struct {
	Name string `description:"产业名称"`
	List []*SubjectNameRep
}

type SubjectNameRep struct {
	Name string `description:"标的名称"`
}

// 列表
func GetIndustrialSubjectListNameByChartId(condition string) (items []*CygxIndustrialSubjectListRep, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT 	s.*,m.industry_name
			FROM
			cygx_industrial_subject AS s
			INNER JOIN cygx_industrial_management AS m ON m.industrial_management_id =s.industrial_management_id
			WHERE 1= 1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY s.subject_name`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 通过多个标的ID获取产业名称
func GetindustrialSubjectNames(subjectIds string) (names string, err error) {
	sql := ` SELECT
			GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR '/' ) AS names 
			FROM
			cygx_industrial_subject AS s
			WHERE
			industrial_subject_id IN ( ` + subjectIds + `) `
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sql).QueryRow(&names)
	return
}

// 列表
func GetIndustrialSubjectListNameByName(condition string) (items []*CygxIndustrialSubject, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT * FROM cygx_industrial_subject WHERE 1= 1`
	if condition != "" {
		sql += condition
	}
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 通过标的名称获取标的关联的ID
func GetIndustrialSubjectIdsByName(subjectName string) (industrialSubjectIds string, err error) {
	sql := ` SELECT
			GROUP_CONCAT( DISTINCT s.industrial_subject_id SEPARATOR ',' ) AS industrial_subject_ids 
			FROM
			cygx_industrial_subject AS s
			WHERE
			subject_name =? `
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sql, subjectName).QueryRow(&industrialSubjectIds)
	return
}

// 通过标的名称获取标的关联的ID
func GetIndustrialSubjectIdsByNameLike(subjectName string) (industrialSubjectIds string, err error) {
	sql := ` SELECT
			GROUP_CONCAT( DISTINCT s.industrial_subject_id SEPARATOR ',' ) AS industrial_subject_ids 
			FROM
			cygx_industrial_subject AS s
			WHERE
			subject_name  LIKE '%` + subjectName + `%'`
	o := orm.NewOrmUsingDB("hz_cygx")
	err = o.Raw(sql).QueryRow(&industrialSubjectIds)
	return
}

type IndustrySubjectCountDetail struct {
	ArtTotalNum int `description:"文章总数量"`
	List        []*IndustrySubjectArtTypeCountList
}

type IndustrySubjectArtTypeCountList struct {
	MatchTypeName string `description:"匹配类型"`
	ArtNum        int    `description:"文章数量"`
}

// GetIndustrySubjectArtTypeCountList 获取标的关联的报告数量列表-根据类型
func GetIndustrySubjectArtTypeCountList(subjectId int) (list []*IndustrySubjectArtTypeCountList, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT
				re.match_type_name,
				COUNT(*) AS art_num
			FROM
				cygx_industrial_subject AS sub
			INNER JOIN cygx_industrial_article_group_subject AS gsub ON gsub.industrial_subject_id = sub.industrial_subject_id
			INNER JOIN cygx_article AS art ON art.article_id = gsub.article_id
			INNER JOIN cygx_report_mapping AS re ON re.category_id = art.category_id
			WHERE
				sub.industrial_subject_id = ? AND re.match_type_name <> '' AND re.report_type = 2 AND art.publish_status = 1
			GROUP BY
				re.match_type_name `
	sql += `UNION ALL
			SELECT
				"研选报告",
				COUNT(*)
			FROM
				cygx_industrial_subject AS sub
			INNER JOIN cygx_industrial_article_group_subject AS gsub ON gsub.industrial_subject_id = sub.industrial_subject_id
			INNER JOIN cygx_article AS art ON art.article_id = gsub.article_id
			WHERE
				art.article_id > 1000000 AND art.article_type_id != 0 AND sub.industrial_subject_id = ? AND art.publish_status = 1`
	_, err = o.Raw(sql, subjectId, subjectId).QueryRows(&list)
	return
}

// 列表
func GetIndustrySubjectListByIds(IndustrialSubjectIds string) (items []*CygxIndustrialSubject, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT * FROM cygx_industrial_subject WHERE industrial_subject_id IN (` + IndustrialSubjectIds + `) `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// 获取标的列表
func GetCygxIndustrialSubjectListCondition(condition string, pars []interface{}) (items []*CygxIndustrialSubject, err error) {
	o := orm.NewOrmUsingDB("hz_cygx")
	sql := `SELECT * 
		FROM
			cygx_industrial_subject WHERE 1 = 1 ` + condition
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}