package data_manage import ( "eta/eta_api/utils" "fmt" "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "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:"数据转换单位"` } func AddChartEdbMapping(items []*ChartEdbMapping) (err error) { o := orm.NewOrmUsingDB("data") _, err = o.InsertMulti(1, 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 } func GetChartEdbMappingListByEdbInfoId(edbInfoStr string) (list []*ChartEdbInfoMapping, err error) { 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(` + edbInfoStr + `) ORDER BY FIELD(edb_info_id,` + edbInfoStr + `) ` _, 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 } // RelationEdbInfoListResp 关联指标列表数据返回 type RelationEdbInfoListResp struct { Paging *paging.PagingItem List []*ChartEdbInfoMapping } 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 []*ChartEdbInfoMapping, 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 }