hongze_yb/models/tables/chart_edb_mapping/query.go

package chart_edb_mapping

import (
	"fmt"
	"hongze/hongze_yb/global"
	"hongze/hongze_yb/utils"
	"strings"
	"time"
)

type ChartEdbInfoMapping struct {
	EdbInfoId         int       `description:"指标id"`
	SourceName        string    `description:"来源名称"`
	Source            int       `description:"来源id"`
	SubSource         int       `description:"来源id"`
	EdbCode           string    `description:"指标编码"`
	EdbName           string    `description:"指标名称"`
	EdbAliasName      string    `description:"指标名称（别名）"`
	EdbAliasNameEn    string    `description:"英文指标名称（别名）"`
	EdbType           int       `description:"指标类型:1:基础指标,2:计算指标"`
	Frequency         string    `description:"频率"`
	Unit              string    `description:"单位"`
	StartDate         string    `description:"起始日期"`
	EndDate           string    `description:"终止日期"`
	ModifyTime        string    `description:"指标最后更新时间"`
	ChartEdbMappingId int       `description:"图表指标id"`
	ChartInfoId       int       `description:"图表id"`
	MaxData           float64   `description:"上限"`
	MinData           float64   `description:"下限"`
	IsOrder           bool      `description:"true:正序,false:逆序"`
	IsAxis            int       `description:"1:左轴,0:右轴"`
	EdbInfoType       int       `description:"1:标准指标,0:领先指标"`
	LeadValue         int       `description:"领先值"`
	LeadUnit          string    `description:"领先单位"`
	ChartStyle        string    `description:"图表类型"`
	ChartColor        string    `description:"颜色"`
	ChartWidth        float64   `description:"线条大小"`
	LatestDate        time.Time `description:"数据最新日期"`
	LatestValue       float64   `description:"数据最新值"`
	UniqueCode        string    `description:"指标唯一编码"`
	MinValue          float64   `json:"-" description:"最小值"`
	MaxValue          float64   `json:"-" description:"最大值"`

	MoveLatestDate      time.Time `description:"移动后的数据最新日期"`
	EdbNameEn           string    `description:"英文指标名称"`
	FrequencyEn         string    `description:"英文频率"`
	UnitEn              string    `description:"英文单位"`
	EdbInfoCategoryType int       `description:"0:普通指标,1:预测指标"`
	LeadUnitEn          string    `description:"领先英文单位"`
	PredictChartColor   string    `description:"预测数据的颜色"`
	ChartType           int       `description:"生成样式:1:曲线图,2:季节性图,3:面积图，4：柱状图，5：散点图，6：组合图"`
	MappingSource       int       `description:"1：ETA图库；2：商品价格曲线"`
	IsConvert           int       `description:"是否数据转换 0不转 1转"`
	ConvertType         int       `description:"数据转换类型 1乘 2除 3对数"`
	ConvertValue        float64   `description:"数据转换值"`
	ConvertUnit         string    `description:"数据转换单位"`
	ConvertEnUnit       string    `description:"数据转换单位"`
}

type ChartEdbInfoMappingList struct {
	ChartEdbInfoMapping
	DataList   interface{}
	IsNullData bool `json:"-" description:"是否空数据"`
}

type ChartEdbInfoMappingTimeString struct {
	ChartEdbInfoMapping
	DataList       interface{}
	IsNullData     bool                       `json:"-" description:"是否空数据"`
	LatestDate     ChartLibEdbInfoMappingTime `description:"数据最新日期"`
	MoveLatestDate ChartLibEdbInfoMappingTime `description:"移动后的数据最新日期"`
}

type ChartLibEdbInfoMappingTime struct {
	time.Time
}

// 自定义时间解析规则
func (ct *ChartLibEdbInfoMappingTime) UnmarshalJSON(data []byte) error {
	s := string(data)
	if s[0] == '"' {
		s = s[1 : len(s)-1]
	}
	t, err := time.ParseInLocation(utils.FormatDate, s, time.Local)
	if err != nil {
		return err
	}
	ct.Time = t
	return nil
}

// GetMappingListByChartInfoId 根据图表ID获取指标映射列表
func GetMappingListByChartInfoId(chartInfoId int) (list []*ChartEdbInfoMapping, err error) {
	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.edb_type,
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 = global.MYSQL["data"].Raw(sql, chartInfoId).Scan(&list).Error

	return
}

// GetMappingListByEdbInfoId 根据指标ID获取指标映射列表
func GetMappingListByEdbInfoId(edbInfoStr string) (list []*ChartEdbInfoMapping, err error) {
	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 FROM edb_info WHERE edb_info_id IN(` + edbInfoStr + `) ORDER BY FIELD(edb_info_id,` + edbInfoStr + `) `
	err = global.MYSQL["data"].Raw(sql).Scan(&list).Error
	return
}

// GetEtaEdbChartEdbMapping       商品曲线图查询对应的普通指标
func GetEtaEdbChartEdbMapping(chartInfoId int) (item *ChartEdbInfoMappingList, err error) {
	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
             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 = global.MYSQL["data"].Raw(sql, chartInfoId, utils.CHART_SOURCE_DEFAULT).First(&item).Error
	return
}

// GetEtaEdbChartEdbMappingList       商品曲线图查询对应的普通指标
func GetEtaEdbChartEdbMappingList(chartInfoId int) (items []*ChartEdbInfoMappingList, err error) {
	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 = global.MYSQL["data"].Raw(sql, chartInfoId, utils.CHART_SOURCE_DEFAULT).Scan(&items).Error
	return
}

// GetFutureGoodEdbChartEdbMapping       商品曲线图查询对应的商品指标
func GetFutureGoodEdbChartEdbMapping(chartInfoId int) (item *ChartEdbInfoMappingList, err error) {
	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 = global.MYSQL["data"].Raw(sql, chartInfoId, utils.CHART_SOURCE_FUTURE_GOOD).First(&item).Error
	return
}

// GetFutureGoodEdbChartEdbMappingList       商品曲线图查询对应的商品指标列表
func GetFutureGoodEdbChartEdbMappingList(chartInfoId int) (items []*ChartEdbInfoMappingList, err error) {
	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 = global.MYSQL["data"].Raw(sql, chartInfoId, utils.CHART_SOURCE_FUTURE_GOOD).Find(&items).Error
	return
}

// GetChartEdbMappingByEdbInfoId 根据指标id获取edb_mapping
func GetChartEdbMappingByEdbInfoId(edbInfoId int) (item *ChartEdbInfoMapping, err error) {
	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
             FROM edb_info
			 WHERE edb_info_id = ? limit 1`
	err = global.MYSQL["data"].Raw(sql, edbInfoId).First(&item).Error
	return
}

// GetChartEdbMappingListByEdbInfoIdList 根据指标id列表获取关联关系
func GetChartEdbMappingListByEdbInfoIdList(edbIdList []int) (list []*ChartEdbInfoMapping, err error) {
	num := len(edbIdList)
	if num <= 0 {
		return
	}
	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
             FROM edb_info
			 WHERE edb_info_id IN(?)
			ORDER BY FIELD(edb_info_id,?)
              `
	edbIdStrList := make([]string, 0)
	for _, v := range edbIdList {
		edbIdStrList = append(edbIdStrList, fmt.Sprint(v))
	}
	err = global.MYSQL["data"].Raw(sql, edbIdList, strings.Join(edbIdStrList, ",")).Find(&list).Error

	return
}