123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328 |
- package models
- import (
- "eta/eta_forum_task/utils"
- "fmt"
- "github.com/beego/beego/v2/client/orm"
- "strconv"
- "strings"
- "time"
- )
- type ChartEdbMapping struct {
- ChartEdbMappingId int `orm:"column(chart_edb_mapping_id);pk"`
- 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:"数据转换单位"`
- EdbCode string `description:"指标编码"`
- }
- func AddChartEdbMapping(items []*ChartEdbMapping) (err error) {
- o := orm.NewOrmUsingDB("data")
- _, err = o.InsertMulti(len(items), items)
- return
- }
- func GetChartEdbMappingList(chartInfoId int) (list []*ChartEdbInfoMapping, err error) {
- o := orm.NewOrmUsingDB("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).QueryRows(&list)
- return
- }
- func GetChartEdbMappingListByChartInfoIds(chartInfoIds string) (list []*ChartEdbInfoMapping, err error) {
- o := orm.NewOrmUsingDB("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).QueryRows(&list)
- return
- }
- // GetChartEdbMappingListByEdbInfoIdList 根据指标id列表获取关联关系
- func GetChartEdbMappingListByEdbInfoIdList(edbIdList []int) (list []*ChartEdbInfoMapping, err error) {
- num := len(edbIdList)
- if num <= 0 {
- return
- }
- o := orm.NewOrmUsingDB("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(` + utils.GetOrmInReplace(num) + `)
- ORDER BY FIELD(edb_info_id,` + utils.GetOrmInReplace(num) + `)
- `
- _, err = o.Raw(sql, edbIdList, edbIdList).QueryRows(&list)
- return
- }
- // GetChartEdbMappingListByIdList 通过图表id列表获取
- func GetChartEdbMappingListByIdList(chartInfoIdList []int) (list []*ChartEdbInfoMapping, err error) {
- num := len(chartInfoIdList)
- if num <= 0 {
- return
- }
- o := orm.NewOrmUsingDB("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).QueryRows(&list)
- return
- }
- func GetRelationEdbInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*ChartEdbInfoMapping, err error) {
- o := orm.NewOrmUsingDB("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 ?,? "
- _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
- return
- }
- func GetRelationEdbInfoListCountByCondition(condition string, pars []interface{}) (count int, err error) {
- o := orm.NewOrmUsingDB("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).QueryRow(&count)
- return
- }
- // GetEtaEdbChartEdbMapping 商品曲线图查询对应的普通指标
- func GetEtaEdbChartEdbMapping(chartInfoId int) (item *ChartEdbInfoMapping, err error) {
- o := orm.NewOrmUsingDB("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).QueryRow(&item)
- return
- }
- // GetFutureGoodEdbChartEdbMapping 商品曲线图查询对应的商品指标
- func GetFutureGoodEdbChartEdbMapping(chartInfoId int) (item *ChartEdbInfoMapping, err error) {
- o := orm.NewOrmUsingDB("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).QueryRow(&item)
- return
- }
- // GetFutureGoodEdbChartEdbMappingList 商品曲线图查询对应的商品指标
- func GetFutureGoodEdbChartEdbMappingList(chartInfoId int) (items []*ChartEdbInfoMapping, err error) {
- o := orm.NewOrmUsingDB("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).QueryRows(&items)
- return
- }
- // GetChartEdbMappingListV2 根据图表id获取指标信息,不连表查询指标表
- func GetChartEdbMappingListV2(chartInfoId int) (list []*ChartEdbMapping, err error) {
- o := orm.NewOrmUsingDB("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).QueryRows(&list)
- return
- }
- // GetChartEdbMappingByEdbInfoId 根据指标id获取edb_mapping
- func GetChartEdbMappingByEdbInfoId(edbInfoId int) (item *ChartEdbInfoMapping, err error) {
- o := orm.NewOrmUsingDB("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 = ? limit 1`
- err = o.Raw(sql, edbInfoId).QueryRow(&item)
- return
- }
- // GetChartEdbMappingByFutureGoodEdbInfoId 根据指标id获取edb_mapping
- func GetChartEdbMappingByFutureGoodEdbInfoId(edbInfoId int) (item *ChartEdbInfoMapping, err error) {
- o := orm.NewOrmUsingDB("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).QueryRow(&item)
- 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, err := orm.NewOrmUsingDB("data").Begin()
- if err != nil {
- return
- }
- 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).QueryRows(&list)
- 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,
- //IsOrder: v.IsOrder,
- //IsAxis: v.IsAxis,
- EdbInfoType: v.EdbInfoType,
- //LeadValue: v.LeadValue,
- //LeadUnit: v.LeadUnit,
- //ChartStyle: v.ChartStyle,
- //ChartColor: v.ChartColor,
- //ChartWidth: v.ChartWidth,
- Source: v.Source,
- })
- }
- // 需要添加的话,那就添加吧
- if len(addList) > 0 {
- _, err = o.InsertMulti(len(addList), addList)
- 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.Raw(sql).Exec()
- if err != nil {
- err = fmt.Errorf("移除不必要的mapping失败,Err:" + err.Error())
- return
- }
- }
- return
- }
- func DeleteChartEdbMappingByChartEdbMappingId(removeIdStr string) (err error) {
- // 删除映射关系
- o := orm.NewOrmUsingDB("data")
- sql := fmt.Sprintf(` DELETE FROM chart_edb_mapping WHERE chart_edb_mapping_id in (%s) `, removeIdStr)
- _, err = o.Raw(sql).Exec()
- if err != nil {
- err = fmt.Errorf("删除映射关系失败,Err:" + err.Error())
- return
- }
- return
- }
- func (c *ChartEdbMapping) Update(col []string) (err error) {
- // 删除映射关系
- o := orm.NewOrmUsingDB("data")
- _, err = o.Update(c, col...)
- return
- }
- func GetChartEdbMappingCount(edbInfoId int) (count int, err error) {
- o := orm.NewOrmUsingDB("data")
- sql := ` SELECT COUNT(1) AS count FROM chart_edb_mapping WHERE edb_info_id=? `
- err = o.Raw(sql, edbInfoId).QueryRow(&count)
- return
- }
- func GetChartMappingList(chartInfoId int) (list []*ChartEdbMapping, err error) {
- o := orm.NewOrmUsingDB("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).QueryRows(&list)
- return
- }
|