eta_api/models/data_manage/edb_info_relation.go

package data_manage

import (
	"eta/eta_api/utils"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"time"
)

type EdbInfoRelation struct {
	EdbInfoRelationId  int       `orm:"column(edb_info_relation_id);pk"`
	EdbInfoId          int       `description:"指标id"`
	Source             int       `description:"来源:1:同花顺,2:wind,3:彭博,4:指标运算,5:累计值转月,6:同比值,7:同差值,8:N数值移动平均计算,9:手工指标,10:隆众"`
	EdbName            string    `description:"指标名称"`
	EdbCode            string    `description:"指标编码"`
	ReferObjectId      int       `description:"引用对象ID（图表ID，ETA逻辑ID等）"`
	ReferObjectType    int       `description:"引用对象ID类型（1.图表，2.ETA逻辑）"`
	ReferObjectSubType int       `description:"引用对象子类"`
	CreateTime         time.Time `description:"创建时间"`
	ModifyTime         time.Time `description:"修改时间"`
	RelationTime       time.Time `description:"引用时间"`
	RelationType       int       `description:"引用类型，0:直接饮用，1间接引用"`
	RootEdbInfoId      int       `description:"间接引用时，关联的直接引用的指标ID"`
	ChildEdbInfoId     int       `description:"间接引用时，关联的计算指标ID"`
	RelationCode       string    `description:"引用标识"`
	ParentRelationId   int       `description:"间接引用关联的直接引用的ID"`
}

func (e *EdbInfoRelation) TableName() string {
	return "edb_info_relation"
}

// GetEdbInfoRelationByRelationIds 查询引用的指标ID
func GetEdbInfoRelationByRelationIds(ids []int) (items []*EdbInfoRelation, err error) {
	o := orm.NewOrmUsingDB("data")
	msql := ` SELECT * FROM edb_info_relation WHERE edb_info_relation_id in (` + utils.GetOrmInReplace(len(ids)) + `) `
	_, err = o.Raw(msql, ids).QueryRows(&items)
	return
}

// GetEdbInfoRelationByReferObjectId 查询直接引用的指标ID
func GetEdbInfoRelationByReferObjectId(referObjectId int, referObjectType int) (items []*EdbInfoRelation, err error) {
	o := orm.NewOrmUsingDB("data")
	msql := ` SELECT * FROM edb_info_relation WHERE refer_object_id =? and relation_type=0 AND refer_object_type=?  GROUP BY edb_info_id `
	_, err = o.Raw(msql, referObjectId, referObjectType).QueryRows(&items)
	return
}

// GetEdbInfoRelationByReferObjectIds 查询引用的指标ID
func GetEdbInfoRelationByReferObjectIds(referObjectIds []int, referObjectType int) (items []*EdbInfoRelation, err error) {
	o := orm.NewOrmUsingDB("data")
	msql := ` SELECT * FROM edb_info_relation WHERE refer_object_id in (` + utils.GetOrmInReplace(len(referObjectIds)) + `) AND refer_object_type=? and relation_type=0`
	_, err = o.Raw(msql, referObjectIds, referObjectType).QueryRows(&items)
	return
}

// GetEdbInfoRelationAllByReferObjectIds 查询引用的指标ID
func GetEdbInfoRelationAllByReferObjectIds(referObjectIds []int, referObjectType int) (items []*EdbInfoRelation, err error) {
	o := orm.NewOrmUsingDB("data")
	msql := ` SELECT * FROM edb_info_relation WHERE refer_object_id in (` + utils.GetOrmInReplace(len(referObjectIds)) + `) AND refer_object_type=?`
	_, err = o.Raw(msql, referObjectIds, referObjectType).QueryRows(&items)
	return
}

// 新增记录
func AddOrUpdateEdbInfoRelation(objectId, objectType int, relationList []*EdbInfoRelation, deleteEdbInfoIds []int, refreshEdbInfoIds []int, indexCodeList []string) (err error) {
	o, err := orm.NewOrmUsingDB("data").Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = o.Rollback()
			return
		}
		_ = o.Commit()
	}()

	if len(deleteEdbInfoIds) > 0 {
		sql := ` DELETE FROM edb_info_relation WHERE refer_object_id = ? AND refer_object_type=? AND edb_info_id in (` + utils.GetOrmInReplace(len(deleteEdbInfoIds)) + `) AND relation_type=0`
		_, err = o.Raw(sql, objectId, objectType, deleteEdbInfoIds).Exec()
		if err != nil {
			return
		}
		// 同时删除相关连的间接引用的指标ID
		sql = ` DELETE FROM edb_info_relation WHERE refer_object_id = ? AND refer_object_type=? AND root_edb_info_id in (` + utils.GetOrmInReplace(len(deleteEdbInfoIds)) + `) AND relation_type=1 `
		_, err = o.Raw(sql, objectId, objectType, deleteEdbInfoIds).Exec()
		if err != nil {
			return
		}
	}
	relationCodesMap := make(map[string]struct{}, 0)
	if len(relationList) > 0 {
		for _, relation := range relationList {
			if relation.RelationType == 1 {
				relationCodesMap[relation.RelationCode] = struct{}{}
			}
		}
		_, err = o.InsertMulti(len(relationList), relationList)
		if err != nil {
			return
		}
	}

	if len(refreshEdbInfoIds) > 0 {
		//todo 是否需要所有指标的刷新状态
		sql := ` UPDATE edb_info SET no_update = 0, set_update_time=? WHERE edb_info_id IN (` + utils.GetOrmInReplace(len(refreshEdbInfoIds)) + `) `
		_, err = o.Raw(sql, time.Now(), refreshEdbInfoIds).Exec()
		if err != nil {
			return
		}
	}

	//更新数据源上海钢联指标
	if len(indexCodeList) > 0 {
		// 更改数据源的更新状态
		sql := ` UPDATE base_from_mysteel_chemical_index SET is_stop = 0 WHERE index_code IN (` + utils.GetOrmInReplace(len(indexCodeList)) + `) and is_stop=1`
		_, err = o.Raw(sql, indexCodeList).Exec()
		if err != nil {
			return
		}
	}

	if len(relationList) > 0 {
		// 更新间接引用指标的关联ID
		relationCodes := make([]string, 0)
		for relationCode := range relationCodesMap {
			relationCodes = append(relationCodes, relationCode)
		}
		if len(relationCodes) > 0 {
			sql := ` UPDATE edb_info_relation e1  
JOIN edb_info_relation e2 ON e1.relation_code = e2.relation_code   
SET e1.parent_relation_id = e2.edb_info_relation_id  
WHERE  
    e1.relation_type = 1   
    AND e2.relation_type = 0 AND e1.parent_relation_id !=e2.edb_info_relation_id AND e1.relation_code in (` + utils.GetOrmInReplace(len(relationCodes)) + `)`
			_, err = o.Raw(sql, relationCodes).Exec()
			if err != nil {
				return
			}
		}
	}
	return
}

// 新增记录
func AddOrUpdateEdbInfoRelationMulti(relationList []*EdbInfoRelation, refreshEdbInfoIds []int, indexCodeList []string) (err error) {
	o, err := orm.NewOrmUsingDB("data").Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = o.Rollback()
			return
		}
		_ = o.Commit()
	}()

	relationCodesMap := make(map[string]struct{}, 0)
	if len(relationList) > 0 {
		for _, relation := range relationList {
			if relation.RelationType == 1 {
				relationCodesMap[relation.RelationCode] = struct{}{}
			}
		}
		_, err = o.InsertMulti(len(relationList), relationList)
		if err != nil {
			return
		}
	}

	if len(refreshEdbInfoIds) > 0 {
		// todo 更新指标的刷新状态
		sql := ` UPDATE edb_info SET no_update = 0, set_update_time=? WHERE edb_info_id IN (` + utils.GetOrmInReplace(len(refreshEdbInfoIds)) + `) `
		_, err = o.Raw(sql, time.Now(), refreshEdbInfoIds).Exec()
		if err != nil {
			return
		}
	}

	//更新数据源上海钢联指标
	if len(indexCodeList) > 0 {
		// 更改数据源的更新状态
		sql := ` UPDATE base_from_mysteel_chemical_index SET is_stop = 0 WHERE index_code IN (` + utils.GetOrmInReplace(len(indexCodeList)) + `) and is_stop=1`
		_, err = o.Raw(sql, indexCodeList).Exec()
		if err != nil {
			return
		}
	}

	if len(relationList) > 0 {
		// 更新间接引用指标的关联ID
		relationCodes := make([]string, 0)
		for relationCode := range relationCodesMap {
			relationCodes = append(relationCodes, relationCode)
		}
		if len(relationCodes) > 0 {
			sql := ` UPDATE edb_info_relation e1  
JOIN edb_info_relation e2 ON e1.relation_code = e2.relation_code   
SET e1.parent_relation_id = e2.edb_info_relation_id  
WHERE  
    e1.relation_type = 1   
    AND e2.relation_type = 0 AND e1.parent_relation_id !=e2.edb_info_relation_id AND e1.relation_code in (` + utils.GetOrmInReplace(len(relationCodes)) + `)`
			_, err = o.Raw(sql, relationCodes).Exec()
			if err != nil {
				return
			}
		}
	}
	return
}

// 删除指标引用内容
func DeleteEdbRelationByObjectIds(referObjectIds []int, referObjectType int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` DELETE FROM edb_info_relation WHERE refer_object_id in (` + utils.GetOrmInReplace(len(referObjectIds)) + `) AND refer_object_type=?`
	_, err = o.Raw(sql, referObjectIds, referObjectType).Exec()
	return
}

// DeleteEdbRelationByObjectId 删除指标引用内容
func DeleteEdbRelationByObjectId(referObjectId int, referObjectType int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` DELETE FROM edb_info_relation WHERE refer_object_id =? AND refer_object_type=?`
	_, err = o.Raw(sql, referObjectId, referObjectType).Exec()
	return
}

type BaseRelationEdbInfo struct {
	EdbInfoId       int
	ClassifyId      int    `description:"指标分类id"`
	EdbName         string `description:"指标名称"`
	EdbCode         string `description:"指标编码"`
	SysUserId       int    `description:"创建人id"`
	SysUserRealName string `description:"创建人姓名"`
	Frequency       string `description:"频度"`
	IsStop          int    `description:"是否停更:1:停更,0:未停更"`
	IsSupplierStop  int    `description:"是否供应商停更:1:停更,0:未停更"`
	RelationNum     int    `description:"引用次数"`
	RelationTime    string `description:"引用时间"`
}

type BaseRelationEdbInfoResp struct {
	Paging *paging.PagingItem
	List   []*BaseRelationEdbInfo
}

type EdbInfoRelationDetail struct {
	EdbInfoRelationId   int    `orm:"column(edb_info_relation_id);pk"`
	EdbInfoId           int    `description:"指标id"`
	ReferObjectId       int    `description:"引用对象ID（图表ID，ETA逻辑ID等）"`
	ReferObjectTypeName string `description:"引用对象类型"`
	ReferObjectType     int    `description:"引用对象ID类型（1.图表，2.ETA逻辑）"`
	ReferObjectSubType  int    `description:"引用对象子类"`
	RelationTime        string `description:"引用时间"`
	ReferObjectName     string `description:"引用对象名称"`
}
type BaseRelationEdbInfoDetailResp struct {
	Paging *paging.PagingItem
	List   []*EdbInfoRelationDetail
}

// 查询指标引用列表
func GetEdbInfoRelationList(condition string, pars []interface{}, addFieldStr, joinTableStr, orderBy string, startSize, pageSize int) (total int, items []*BaseRelationEdbInfo, err error) {
	o := orm.NewOrmUsingDB("data")
	// 数量汇总
	totalSql := ` SELECT count(1) FROM edb_info e LEFT JOIN (
SELECT count(edb_info_id) as relation_num, edb_info_id, max(relation_time) as relation_time FROM edb_info_relation GROUP BY edb_info_id) r on e.edb_info_id=r.edb_info_id  `

	if joinTableStr != "" {
		totalSql += joinTableStr
	}
	totalSql += ` WHERE 1=1 `
	if condition != "" {
		totalSql += condition
	}
	err = o.Raw(totalSql, pars).QueryRow(&total)
	if err != nil {
		return
	}

	fieldStr := ` e.edb_info_id, e.classify_id,e.edb_code,e.edb_name,e.sys_user_id,e.sys_user_real_name,e.frequency,e.no_update as is_stop, r.relation_num, r.relation_time ` + addFieldStr
	// 列表数据
	sql := ` SELECT ` + fieldStr + ` from edb_info e LEFT JOIN (
SELECT count(edb_info_id) as relation_num, edb_info_id, max(relation_time) as relation_time FROM edb_info_relation GROUP BY edb_info_id) r on e.edb_info_id=r.edb_info_id  `
	if joinTableStr != "" {
		sql += joinTableStr
	}
	sql += ` WHERE 1=1 `

	if condition != "" {
		sql += condition
	}

	if orderBy != "" {
		sql += ` ORDER BY ` + orderBy
	} else {
		sql += ` ORDER BY edb_info_id ASC `
	}
	sql += `  LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)

	return
}

// GetEdbInfoRelationDetailList 查询指标引用详情列表
func GetEdbInfoRelationDetailList(edbInfoId int, startSize, pageSize int) (total int, items []*EdbInfoRelation, err error) {
	o := orm.NewOrmUsingDB("data")
	// 数量汇总
	totalSql := ` SELECT count(*) FROM edb_info_relation where edb_info_id=?`
	err = o.Raw(totalSql, edbInfoId).QueryRow(&total)
	if err != nil {
		return
	}

	// 列表数据
	sql := ` SELECT *FROM edb_info_relation where edb_info_id=? ORDER BY relation_time desc, edb_info_id ASC `
	sql += `  LIMIT ?,? `
	_, err = o.Raw(sql, edbInfoId, startSize, pageSize).QueryRows(&items)

	return
}

// 查询相关的指标记录总数
func GetReplaceChildEdbInfoRelationTotal(edbInfoId int) (total int, err error) {
	o := orm.NewOrmUsingDB("data")
	// 数量汇总
	totalSql := ` SELECT count(*) FROM edb_info_relation where  relation_type=1 and (child_edb_info_id=? or edb_info_id=? ) group by parent_relation_id`
	err = o.Raw(totalSql, edbInfoId, edbInfoId).QueryRow(&total)
	if err != nil {
		return
	}
	return
}

// 查询相关的指标记录列表
func GetReplaceChildEdbInfoRelationList(edbInfoId int, startSize, pageSize int) (items []*EdbInfoRelation, err error) {
	o := orm.NewOrmUsingDB("data")
	// 列表数据
	sql := ` SELECT * FROM edb_info_relation where  relation_type=1 and (child_edb_info_id=? or edb_info_id=? )  group by parent_relation_id ORDER BY edb_info_relation_id ASC  LIMIT ?,? `
	_, err = o.Raw(sql, edbInfoId, edbInfoId, startSize, pageSize).QueryRows(&items)
	return
}

// 查询相关的指标记录总数
func GetReplaceEdbInfoRelationTotal(edbInfoId int) (total int, err error) {
	o := orm.NewOrmUsingDB("data")
	// 数量汇总
	totalSql := ` SELECT count(*) FROM edb_info_relation where edb_info_id=? and relation_type = 0`
	err = o.Raw(totalSql, edbInfoId).QueryRow(&total)
	if err != nil {
		return
	}
	return
}

// 查询相关的指标记录列表
func GetReplaceEdbInfoRelationList(edbInfoId int, startSize, pageSize int) (items []*EdbInfoRelation, err error) {
	o := orm.NewOrmUsingDB("data")
	// 列表数据
	sql := ` SELECT * FROM edb_info_relation where edb_info_id=? and relation_type = 0 ORDER BY edb_info_relation_id ASC LIMIT ?,? `
	_, err = o.Raw(sql, edbInfoId, startSize, pageSize).QueryRows(&items)
	return
}

// 替换指标引用表中直接引用的指标
func ReplaceRelationEdbInfoId(oldEdbInfo, newEdbInfo *EdbInfo, edbRelationIds []int, relationList []*EdbInfoRelation, refreshEdbInfoIds []int, indexCodeList []string) (err error) {
	o, err := orm.NewOrmUsingDB("data").Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = o.Rollback()
			return
		}
		_ = o.Commit()
	}()

	now := time.Now()
	// 删除相关的间接引用
	sql := ` DELETE FROM edb_info_relation WHERE root_edb_info_id=?  and relation_type=1 and parent_relation_id in (` + utils.GetOrmInReplace(len(edbRelationIds)) + `)`
	_, err = o.Raw(sql, oldEdbInfo.EdbInfoId, edbRelationIds).Exec()
	if err != nil {
		return
	}

	sourceWhere := ` and (refer_object_type in (1,2,5) or (refer_object_type=4 and refer_object_sub_type !=5) )` //平衡表和事件日历中的直接引用无需替换，
	// 替换edb_info_id
	sql = ` UPDATE edb_info_relation SET edb_info_id=?, source=?, edb_name=?, edb_code=?, modify_time=?, relation_time=?  WHERE edb_info_id=? ` + sourceWhere + ` and relation_type=0 and edb_info_relation_id in (` + utils.GetOrmInReplace(len(edbRelationIds)) + `)`
	_, err = o.Raw(sql, newEdbInfo.EdbInfoId, newEdbInfo.Source, newEdbInfo.EdbName, newEdbInfo.EdbCode, now, now, oldEdbInfo.EdbInfoId, edbRelationIds).Exec()
	if err != nil {
		return
	}

	// 更新code值
	sql = ` UPDATE edb_info_relation SET relation_code=CONCAT_WS("_", edb_info_id,refer_object_id,refer_object_type,refer_object_sub_type)  WHERE relation_type=0 ` + sourceWhere + `  and edb_info_relation_id in (` + utils.GetOrmInReplace(len(edbRelationIds)) + `)`
	_, err = o.Raw(sql, edbRelationIds).Exec()
	if err != nil {
		return
	}
	// 新增间接引用
	relationCodesMap := make(map[string]struct{}, 0)
	if len(relationList) > 0 {
		for _, relation := range relationList {
			if relation.RelationType == 1 {
				relationCodesMap[relation.RelationCode] = struct{}{}
			}
		}
		_, err = o.InsertMulti(len(relationList), relationList)
		if err != nil {
			return
		}
	}

	if len(refreshEdbInfoIds) > 0 {
		// todo 更新指标的刷新状态
		sql := ` UPDATE edb_info SET no_update = 0, set_update_time=? WHERE  edb_info_id IN (` + utils.GetOrmInReplace(len(refreshEdbInfoIds)) + `) `
		_, err = o.Raw(sql, time.Now(), refreshEdbInfoIds).Exec()
		if err != nil {
			return
		}
	}

	//更新数据源上海钢联指标
	if len(indexCodeList) > 0 {
		// 更改数据源的更新状态
		sql := ` UPDATE base_from_mysteel_chemical_index SET is_stop = 0 WHERE index_code IN (` + utils.GetOrmInReplace(len(indexCodeList)) + `) and is_stop=1`
		_, err = o.Raw(sql, indexCodeList).Exec()
		if err != nil {
			return
		}
	}
	if len(relationList) > 0 {
		// 更新间接引用指标的关联ID
		relationCodes := make([]string, 0)
		for relationCode := range relationCodesMap {
			relationCodes = append(relationCodes, relationCode)
		}
		if len(relationCodes) > 0 {
			sql := ` UPDATE edb_info_relation e1  
JOIN edb_info_relation e2 ON e1.relation_code = e2.relation_code   
SET e1.parent_relation_id = e2.edb_info_relation_id  
WHERE  
    e1.relation_type = 1   
    AND e2.relation_type = 0 AND e1.parent_relation_id !=e2.edb_info_relation_id AND e1.relation_code in (` + utils.GetOrmInReplace(len(relationCodes)) + `)`
			_, err = o.Raw(sql, relationCodes).Exec()
			if err != nil {
				return
			}
		}
	}
	return
}

// UpdateSecondRelationEdbInfoId 更新指标替换后的间接引用记录
func UpdateSecondRelationEdbInfoId(edbRelationIds []int, relationList []*EdbInfoRelation, refreshEdbInfoIds []int, indexCodeList []string) (err error) {
	o, err := orm.NewOrmUsingDB("data").Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = o.Rollback()
			return
		}
		_ = o.Commit()
	}()
	// 删除相关的间接引用
	sql := ` DELETE FROM edb_info_relation WHERE relation_type=1 and parent_relation_id in (` + utils.GetOrmInReplace(len(edbRelationIds)) + `)`
	_, err = o.Raw(sql, edbRelationIds).Exec()
	if err != nil {
		return
	}

	// 新增间接引用
	relationCodesMap := make(map[string]struct{}, 0)
	if len(relationList) > 0 {
		for _, relation := range relationList {
			if relation.RelationType == 1 {
				relationCodesMap[relation.RelationCode] = struct{}{}
			}
		}
		_, err = o.InsertMulti(len(relationList), relationList)
		if err != nil {
			return
		}
	}

	if len(refreshEdbInfoIds) > 0 {
		// todo 更新指标的刷新状态
		sql = ` UPDATE edb_info SET no_update = 0, set_update_time=? WHERE  edb_info_id IN (` + utils.GetOrmInReplace(len(refreshEdbInfoIds)) + `) `
		_, err = o.Raw(sql, time.Now(), refreshEdbInfoIds).Exec()
		if err != nil {
			return
		}
	}

	//更新数据源上海钢联指标
	if len(indexCodeList) > 0 {
		// 更改数据源的更新状态
		sql = ` UPDATE base_from_mysteel_chemical_index SET is_stop = 0 WHERE index_code IN (` + utils.GetOrmInReplace(len(indexCodeList)) + `) and is_stop=1`
		_, err = o.Raw(sql, indexCodeList).Exec()
		if err != nil {
			return
		}
	}
	if len(relationList) > 0 {
		// 更新间接引用指标的关联ID
		relationCodes := make([]string, 0)
		for relationCode := range relationCodesMap {
			relationCodes = append(relationCodes, relationCode)
		}
		if len(relationCodes) > 0 {
			sql = ` UPDATE edb_info_relation e1  
JOIN edb_info_relation e2 ON e1.relation_code = e2.relation_code   
SET e1.parent_relation_id = e2.edb_info_relation_id  
WHERE  
    e1.relation_type = 1   
    AND e2.relation_type = 0 AND e1.parent_relation_id !=e2.edb_info_relation_id AND e1.relation_code in (` + utils.GetOrmInReplace(len(relationCodes)) + `)`
			_, err = o.Raw(sql, relationCodes).Exec()
			if err != nil {
				return
			}
		}
	}
	return
}