eta_api/models/data_manage/chart_edb_mapping.go

package data_manage

import (
	sql2 "database/sql"
	"eta_gn/eta_api/global"
	"eta_gn/eta_api/utils"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/rdlucklib/rdluck_tools/paging"
)

type ChartEdbMapping struct {
	ChartEdbMappingId int       `orm:"column(chart_edb_mapping_id);pk" gorm:"primaryKey" `
	ChartInfoId       int       `description:"图表id"`
	EdbInfoId         int       `description:"指标id"`
	CreateTime        time.Time `description:"创建时间"`
	ModifyTime        time.Time `description:"修改时间"`
	UniqueCode        string    `description:"唯一编码"`
	MaxData           float64   `description:"上限"`
	MinData           float64   `description:"下限"`
	IsOrder           bool      `description:"true:正序,false:逆序"`
	IsAxis            int       `description:"true:左轴,false:右轴"`
	EdbInfoType       int       `description:"true:标准指标,false:领先指标"`
	LeadValue         int       `description:"领先值"`
	LeadUnit          string    `description:"领先单位"`
	ChartStyle        string    `description:"图表类型"`
	ChartColor        string    `description:"颜色"`
	PredictChartColor string    `description:"预测数据的颜色"`
	ChartWidth        float64   `description:"线条大小"`
	Source            int       `description:"1：ETA图库；2：商品价格曲线"`
	EdbAliasName      string    `description:"中文别名"`
	IsConvert         int       `description:"是否数据转换 0不转 1转"`
	ConvertType       int       `description:"数据转换类型 1乘 2除 3对数"`
	ConvertValue      float64   `description:"数据转换值"`
	ConvertUnit       string    `description:"数据转换单位"`
	ConvertEnUnit     string    `description:"数据转换单位"`
}

func AddChartEdbMapping(items []*ChartEdbMapping) (err error) {
	err = global.DmSQL["data"].CreateInBatches(items, utils.MultiAddNum).Error

	return
}

func GetChartEdbMappingList(chartInfoId int) (list []*ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT a.*,b.source_name,b.source,b.sub_source,b.classify_id,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,
b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type AS edb_info_category_type,b.edb_type,b.is_join_permission,
a.is_convert, a.convert_type, a.convert_value, a.convert_unit, a.convert_en_unit 
             FROM chart_edb_mapping AS a
			 INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
			 WHERE chart_info_id=? 
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoId).Scan(&list).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range list {
		v.ConvertToResp()
	}

	return
}

func GetChartMappingList(chartInfoId int) (list []*ChartEdbMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT a.*
             FROM chart_edb_mapping AS a
			 WHERE chart_info_id=? 
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoId).Scan(&list).Error

	return
}

func GetChartEdbMappingListByChartInfoIds(chartInfoIds string) (list []*ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT a.*,b.source_name,b.source,b.sub_source,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type AS edb_info_category_type,b.edb_type,b.classify_id,b.is_join_permission
             FROM chart_edb_mapping AS a
			 INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
			 WHERE chart_info_id in (` + chartInfoIds + `) 
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql).Scan(&list).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range list {
		v.ConvertToResp()
	}

	return
}

func GetChartEdbMappingListByChartInfoIdList(chartInfoIdList []int) (list []*ChartEdbInfoMapping, err error) {
	if len(chartInfoIdList) <= 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := ` SELECT a.*,b.source_name,b.source,b.sub_source,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type AS edb_info_category_type,b.edb_type,b.classify_id,b.is_join_permission
             FROM chart_edb_mapping AS a
			 INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
			 WHERE chart_info_id in (?) 
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoIdList).Scan(&list).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range list {
		v.ConvertToResp()
	}

	return
}

func GetChartEdbMappingListByEdbInfoId(edbInfoStr string) (list []*ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT edb_info_id,source_name,source,sub_source,edb_code,edb_name,edb_name_en,frequency,unit,unit_en,start_date,end_date,modify_time,latest_date,latest_value,unique_code,edb_info_type AS edb_info_category_type,max_value,min_value,edb_type,classify_id,is_join_permission
             FROM edb_info
			 WHERE edb_info_id IN(` + edbInfoStr + `)
			ORDER BY FIELD(edb_info_id,` + edbInfoStr + `)
              `
	err = o.Raw(sql).Scan(&list).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range list {
		v.ConvertToResp()
	}

	return
}

// GetChartEdbMappingListByEdbInfoIdList 根据指标id列表获取关联关系
func GetChartEdbMappingListByEdbInfoIdList(edbIdList []int) (list []*ChartEdbInfoMapping, err error) {
	num := len(edbIdList)
	if num <= 0 {
		return
	}
	edbIdArr := make([]string, 0)
	for _, v := range edbIdList {
		edbIdArr = append(edbIdArr, strconv.Itoa(v))
	}
	edbIdStr := strings.Join(edbIdArr, ",")
	o := global.DmSQL["data"]
	sql := ` SELECT edb_info_id,source_name,source,sub_source,edb_code,edb_name,edb_name_en,frequency,unit,unit_en,start_date,end_date,modify_time,latest_date,latest_value,unique_code,edb_info_type AS edb_info_category_type,max_value,min_value,edb_type,classify_id,is_join_permission
	        FROM edb_info
			 WHERE edb_info_id IN(` + edbIdStr + `)
			ORDER BY FIELD(edb_info_id,` + edbIdStr + `) `
	err = o.Raw(sql).Find(&list).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range list {
		v.ConvertToResp()
	}

	return
}

// GetChartEdbMappingListByIdList 通过图表id列表获取
func GetChartEdbMappingListByIdList(chartInfoIdList []int) (list []*ChartEdbInfoMapping, err error) {
	num := len(chartInfoIdList)
	if num <= 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := ` SELECT a.*,b.source_name,b.source,b.sub_source,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type,b.edb_type AS edb_info_category_type,b.classify_id,b.is_join_permission
             FROM chart_edb_mapping AS a
			 INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
			 WHERE chart_info_id in (` + utils.GetOrmInReplace(num) + `) 
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoIdList).Scan(&list).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range list {
		v.ConvertToResp()
	}

	return
}

// RelationEdbInfoListResp 关联指标列表数据返回
type RelationEdbInfoListResp struct {
	Paging *paging.PagingItem
	List   []*ChartEdbInfoMapping
}

func GetRelationEdbInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT a.* FROM edb_info AS a 
	JOIN edb_info_calculate_mapping AS b on a.edb_info_id = b.edb_info_id WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	//sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
	sql += " ORDER BY a.create_time DESC LIMIT ?,? "
	pars = append(pars, startSize)
	pars = append(pars, pageSize)
	err = o.Raw(sql, pars...).Scan(&item).Error

	return
}

func GetRelationEdbInfoListCountByCondition(condition string, pars []interface{}) (count int, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT COUNT(1) AS count FROM edb_info AS a 
	JOIN edb_info_calculate_mapping AS b on a.edb_info_id = b.edb_info_id WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars...).Scan(&count).Error

	return
}

// GetEtaEdbChartEdbMapping       商品曲线图查询对应的普通指标
func GetEtaEdbChartEdbMapping(chartInfoId int) (item *ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	aField := `a.chart_edb_mapping_id,a.chart_info_id,a.edb_info_id,a.create_time,a.modify_time,a.unique_code,a.max_data,a.min_data,a.is_order,a.is_axis,a.edb_info_type,a.lead_value,a.lead_unit,a.chart_style,a.chart_color,a.predict_chart_color,a.chart_width,a.source as mapping_source`

	sql := ` SELECT ` + aField + `,b.source_name,b.source,b.sub_source,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type AS edb_info_category_type,b.classify_id,b.is_join_permission
             FROM chart_edb_mapping AS a
			 INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
			 WHERE a.chart_info_id=? AND a.source = ?
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoId, utils.CHART_SOURCE_DEFAULT).Scan(&item).Error
	if err != nil {
		return
	}

	// 日期格式转换
	item.ConvertToResp()

	return
}

// GetEtaEdbChartEdbMappingList       商品曲线图查询对应的普通指标
func GetEtaEdbChartEdbMappingList(chartInfoId int) (items []*ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	aField := `a.chart_edb_mapping_id,a.chart_info_id,a.edb_info_id,a.create_time,a.modify_time,a.unique_code,a.max_data,a.min_data,a.is_order,a.is_axis,a.edb_info_type,a.lead_value,a.lead_unit,a.chart_style,a.chart_color,a.predict_chart_color,a.chart_width,a.source as mapping_source`

	sql := ` SELECT ` + aField + `,b.source_name,b.source,b.sub_source,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type AS edb_info_category_type,b.classify_id,b.is_join_permission
             FROM chart_edb_mapping AS a
			 INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
			 WHERE a.chart_info_id=? AND a.source = ?
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoId, utils.CHART_SOURCE_DEFAULT).Scan(&items).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range items {
		v.ConvertToResp()
	}

	return
}

// GetFutureGoodEdbChartEdbMapping       商品曲线图查询对应的商品指标
func GetFutureGoodEdbChartEdbMapping(chartInfoId int) (item *ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	aField := `a.chart_edb_mapping_id,a.chart_info_id,a.edb_info_id,a.create_time,a.modify_time,a.unique_code,a.max_data,a.min_data,a.is_order,a.is_axis,a.edb_info_type,a.lead_value,a.lead_unit,a.chart_style,a.chart_color,a.predict_chart_color,a.chart_width,a.source as mapping_source`
	sql := ` SELECT ` + aField + `,b.future_good_edb_info_id,b.future_good_edb_code as edb_code,b.future_good_edb_name as edb_name,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value
             FROM chart_edb_mapping AS a
			 INNER JOIN future_good_edb_info AS b ON a.edb_info_id=b.future_good_edb_info_id
			 WHERE a.chart_info_id=? AND a.source = ?
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoId, utils.CHART_SOURCE_FUTURE_GOOD).Scan(&item).Error
	if err != nil {
		return
	}

	// 日期格式转换
	item.ConvertToResp()

	return
}

// GetFutureGoodEdbChartEdbMappingList       商品曲线图查询对应的商品指标
func GetFutureGoodEdbChartEdbMappingList(chartInfoId int) (items []*ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	aField := `a.chart_edb_mapping_id,a.chart_info_id,a.edb_info_id,a.create_time,a.modify_time,a.unique_code,a.max_data,a.min_data,a.is_order,a.is_axis,a.edb_info_type,a.lead_value,a.lead_unit,a.chart_style,a.chart_color,a.predict_chart_color,a.chart_width,a.source as mapping_source`
	sql := ` SELECT ` + aField + `,b.future_good_edb_info_id,b.future_good_edb_code as edb_code,b.future_good_edb_name as edb_name,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value
             FROM chart_edb_mapping AS a
			 INNER JOIN future_good_edb_info AS b ON a.edb_info_id=b.future_good_edb_info_id
			 WHERE a.chart_info_id=? AND a.source = ?
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoId, utils.CHART_SOURCE_FUTURE_GOOD).Scan(&items).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range items {
		v.ConvertToResp()
	}

	return
}

// GetChartEdbMappingListV2 根据图表id获取指标信息,不连表查询指标表
func GetChartEdbMappingListV2(chartInfoId int) (list []*ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT a.* FROM chart_edb_mapping AS a
			 WHERE chart_info_id=? 
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoId).Scan(&list).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range list {
		v.ConvertToResp()
	}

	return
}

// GetChartEdbMappingByEdbInfoId 根据指标id获取edb_mapping
func GetChartEdbMappingByEdbInfoId(edbInfoId int) (item *ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT edb_info_id,source_name,classify_id,source,sub_source,edb_code,edb_name,edb_name_en,frequency,unit,unit_en,start_date,end_date,modify_time,latest_date,latest_value,unique_code,edb_info_type AS edb_info_category_type,edb_type,max_value,min_value,is_join_permission
             FROM edb_info
			 WHERE edb_info_id = ? `
	err = o.Raw(sql, edbInfoId).First(&item).Error
	if err != nil {
		return
	}

	// 日期格式转换
	item.ConvertToResp()

	return
}

// GetEdbMappingListByEdbInfoId 根据指标id获取edb_mapping
func GetEdbMappingListByEdbInfoId(edbInfoId int) (list []*ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM chart_edb_mapping
			 WHERE edb_info_id = ? `
	err = o.Raw(sql, edbInfoId).Scan(&list).Error
	if err != nil {
		return
	}

	// 日期格式转换
	for _, v := range list {
		v.ConvertToResp()
	}

	return
}

// GetChartEdbMappingByFutureGoodEdbInfoId 根据指标id获取edb_mapping
func GetChartEdbMappingByFutureGoodEdbInfoId(edbInfoId int) (item *ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT future_good_edb_info_id as edb_info_id,future_good_edb_code as edb_code,b.future_good_edb_name as edb_name,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.region_type
             FROM future_good_edb_info b
			 WHERE future_good_edb_info_id = ?  limit 1 `
	err = o.Raw(sql, edbInfoId).Scan(&item).Error
	if err != nil {
		return
	}

	// 日期格式转换
	item.ConvertToResp()

	return
}

// ModifyChartEdbMapping
// @Description: 修改图表的关系表
// @author: Roc
// @datetime 2023-12-11 17:23:32
// @param chartInfoId int
// @param edbInfoList []*EdbInfo
// @return err error
func ModifyChartEdbMapping(chartInfoId int, edbInfoList []*EdbInfo) (err error) {
	o := global.DmSQL["data"].Begin()
	defer func() {
		if err != nil {
			fmt.Println("AddCalculateHcz,Err:" + err.Error())
			_ = o.Rollback()
		} else {
			_ = o.Commit()
		}
	}()
	list := make([]*ChartEdbMapping, 0)
	sql := ` SELECT a.*
             FROM chart_edb_mapping AS a
			 WHERE chart_info_id=? 
             ORDER BY chart_edb_mapping_id ASC `
	err = o.Raw(sql, chartInfoId).Scan(&list).Error
	if err != nil {
		return
	}

	mappingIdMap := make(map[int]*ChartEdbMapping)
	removeMapping := make(map[int]int)
	for _, v := range list {
		mappingIdMap[v.EdbInfoId] = v
		removeMapping[v.EdbInfoId] = v.ChartEdbMappingId
	}

	addList := make([]*ChartEdbMapping, 0)
	for _, v := range edbInfoList {
		_, ok := mappingIdMap[v.EdbInfoId]
		// 存在该指标关系就不处理了
		if ok {
			delete(removeMapping, v.EdbInfoId)
			continue
		}
		timestamp := strconv.FormatInt(time.Now().UnixNano(), 10)
		// 不存在就添加
		addList = append(addList, &ChartEdbMapping{
			//ChartEdbMappingId: 0,
			ChartInfoId: chartInfoId,
			EdbInfoId:   v.EdbInfoId,
			CreateTime:  time.Now(),
			ModifyTime:  time.Now(),
			UniqueCode:  utils.MD5(utils.CHART_PREFIX + "_" + fmt.Sprint(chartInfoId) + "_" + fmt.Sprint(v.EdbInfoId) + "_" + timestamp),
			MaxData:     v.MaxValue,
			MinData:     v.MinValue,
			EdbInfoType: v.EdbInfoType,
			Source:      v.Source,
		})
	}

	// 需要添加的话，那就添加吧
	if len(addList) > 0 {
		err = o.CreateInBatches(addList, utils.MultiAddNum).Error
		if err != nil {
			return
		}
	}

	// 移除不必要的mapping
	if len(removeMapping) > 0 {
		removeIdList := make([]string, 0) //需要移除的日期
		for _, v := range removeMapping {
			removeIdList = append(removeIdList, fmt.Sprint(v))
		}
		removeIdStr := strings.Join(removeIdList, `","`)
		removeIdStr = `"` + removeIdStr + `"`
		//如果拼接指标变更了，那么需要删除所有的指标数据
		sql := fmt.Sprintf(` DELETE FROM chart_edb_mapping WHERE chart_edb_mapping_id in (%s) `, removeIdStr)

		err = o.Exec(sql).Error
		if err != nil {
			err = fmt.Errorf("移除不必要的mapping失败,Err:" + err.Error())
			return
		}
	}

	return
}

func GetRelationEdbInfoListMappingByCondition(condition string, pars []interface{}) (item []*ChartEdbInfoMapping, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT a.* FROM edb_info AS a 
	JOIN edb_info_calculate_mapping AS b on a.edb_info_id = b.edb_info_id WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars...).Scan(&item).Error

	return
}

// GetCountRelationChartEdbInfoListMappingByCondition
// @Description: 根据条件获取关联图数量
// @author: Roc
// @datetime 2024-12-26 10:13:47
// @param condition string
// @param pars []interface{}
// @return total int
// @return err error
func GetCountRelationChartEdbInfoListMappingByCondition(condition string, pars []interface{}) (total int, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT COUNT(1) total FROM chart_edb_mapping AS a 
	JOIN chart_info AS b on a.chart_info_id = b.chart_info_id WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	var totalNull sql2.NullInt64
	err = o.Raw(sql, pars...).Scan(&totalNull).Error
	if err != nil {
		return
	}
	total = int(totalNull.Int64)

	return
}

// GetUserIdListRelationChartEdbInfoListMappingByCondition
// @Description: 根据条件获取关联图的用户id列表
// @author: Roc
// @datetime 2024-12-26 10:13:47
// @param condition string
// @param pars []interface{}
// @return total int
// @return err error
func GetUserIdListRelationChartEdbInfoListMappingByCondition(condition string, pars []interface{}) (userIdList []int, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT b.sys_user_id FROM chart_edb_mapping AS a 
	JOIN chart_info AS b on a.chart_info_id = b.chart_info_id WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` GROUP BY b.sys_user_id `

	err = o.Raw(sql, pars...).Scan(&userIdList).Error

	return
}