package data_manage import ( "errors" "fmt" "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "hongze/hz_crm_api/utils" "strconv" "strings" "time" ) type ChartInfo struct { ChartInfoId int `orm:"column(chart_info_id);pk"` ChartName string `description:"来源名称"` ChartNameEn string `description:"英文图表名称"` ChartClassifyId int `description:"图表分类id"` SysUserId int SysUserRealName string UniqueCode string `description:"图表唯一编码"` CreateTime time.Time ModifyTime time.Time DateType int `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间"` StartDate string `description:"自定义开始日期"` EndDate string `description:"自定义结束日期"` IsSetName int `description:"设置名称"` EdbInfoIds string `description:"指标id"` ChartType int `description:"生成样式:1:曲线图,2:季节性图,3:面积图，4：柱状图，5：散点图，6：组合图，7：柱方图，8：商品价格曲线图，9：相关性图"` Calendar string `description:"公历/农历"` SeasonStartDate string `description:"季节性图开始日期"` SeasonEndDate string `description:"季节性图开始日期"` ChartImage string `description:"图表图片"` Sort int `description:"排序字段，数字越小越排前面"` LeftMin string `description:"图表左侧最小值"` LeftMax string `description:"图表左侧最大值"` RightMin string `description:"图表右侧最小值"` RightMax string `description:"图表右侧最大值"` Disabled int `description:"是否禁用，0：启用，1：禁用，默认：0"` BarConfig string `description:"柱方图的配置，json数据"` Source int `description:"1：ETA图库；2：商品价格曲线"` ExtraConfig string `description:"图表额外配置,json数据"` } type ChartInfoMore struct { ChartInfo IsEnChart bool `description:"是否展示英文标识"` } func AddChartInfo(item *ChartInfo) (lastId int64, err error) { o := orm.NewOrmUsingDB("data") lastId, err = o.Insert(item) return } type ChartInfoItem struct { ChartInfoId int `description:"图表id"` ChartName string `description:"图表名称"` ChartClassifyId int `description:"图表分类"` } // GetChartInfoAll 用于分类展示 // @param source int 1：ETA图库；2：商品价格曲线 func GetChartInfoAll(sourceList []int) (items []*ChartClassifyItems, err error) { num := len(sourceList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT chart_info_id,chart_classify_id,chart_name AS chart_classify_name, unique_code,sys_user_id,sys_user_real_name,date_type,start_date,end_date,chart_type,calendar,season_start_date,season_end_date,source FROM chart_info WHERE source in (` + utils.GetOrmInReplace(num) + `) ORDER BY sort asc,create_time ASC ` _, err = o.Raw(sql, sourceList).QueryRows(&items) return } // GetChartInfoByAdminId 获取所有我创建的图表，用于分类展示 func GetChartInfoByAdminId(sourceList []int, adminId int) (items []*ChartClassifyItems, err error) { num := len(sourceList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT chart_info_id,chart_classify_id,chart_name AS chart_classify_name, unique_code,sys_user_id,sys_user_real_name,date_type,start_date,end_date,chart_type,calendar,season_start_date,season_end_date FROM chart_info where source in (` + utils.GetOrmInReplace(num) + `) AND sys_user_id = ? ORDER BY sort asc,create_time ASC ` _, err = o.Raw(sql, sourceList, adminId).QueryRows(&items) return } // 用于分类展示 func GetChartInfoAllList() (items []*ChartInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info ORDER BY create_time ASC ` _, err = o.Raw(sql).QueryRows(&items) return } // 图表检索数据 type ChartInfoSearch struct { EdbCode string `description:"图表编码"` StartDate string `description:"起始日期"` EndDate string `description:"终止日期"` DataList []*EdbInfoSearchData } type ChartInfoSearchData struct { DataTime string `description:"数据日期"` Value float64 `description:"数据"` } type ChartInfoSearchResp struct { SearchItem *EdbInfoSearch `description:"图表分类"` Status int `description:"1:数据已存在于弘则数据库，2:新数据"` } func GetChartInfoById(chartInfoId int) (item *ChartInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE chart_info_id=? ` err = o.Raw(sql, chartInfoId).QueryRow(&item) return } func GetChartInfoViewById(chartInfoId int) (item *ChartInfoView, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE chart_info_id=? ` err = o.Raw(sql, chartInfoId).QueryRow(&item) return } func GetChartInfoViewByIds(chartInfoIds []string) (list []*ChartInfoView, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE chart_info_id in ("` + strings.Join(chartInfoIds, `","`) + `") ` _, err = o.Raw(sql).QueryRows(&list) return } type SaveChartInfoReq struct { ChartEdbInfoList []*ChartSaveItem `description:"指标及配置信息"` ChartInfoId int `description:"图表id,新增时传0"` DateType int `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间,6:起始日期至今"` StartDate string `description:"自定义开始日期"` EndDate string `description:"自定义结束日期"` Calendar string `description:"公历/农历"` SeasonStartDate string `description:"季节性图开始日期"` SeasonEndDate string `description:"季节性图开始日期"` LeftMin string `description:"图表左侧最小值"` LeftMax string `description:"图表左侧最大值"` RightMin string `description:"图表右侧最小值"` RightMax string `description:"图表右侧最大值"` ExtraConfig string `description:"图表额外配置,json数据"` } type ChartSaveItem struct { EdbInfoId 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:"颜色"` PredictChartColor string `description:"预测数据的颜色"` ChartWidth float64 `description:"线条大小"` Source int `description:"1：ETA图库；2：商品价格曲线"` } func DeleteChartInfoAndData(chartInfoId int) (err error) { o := orm.NewOrmUsingDB("data") to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() sql := ` DELETE FROM chart_info WHERE chart_info_id=? ` _, err = to.Raw(sql, chartInfoId).Exec() if err != nil { return } sql = ` DELETE FROM chart_edb_mapping WHERE chart_info_id=? ` _, err = to.Raw(sql, chartInfoId).Exec() return } func GetChartInfoCountByCondition(condition string, pars []interface{}) (count int, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT COUNT(1) AS count FROM chart_info WHERE 1=1 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&count) return } type EditChartInfoReq struct { ChartInfoId int `description:"图表ID"` ChartName string `description:"图表名称"` ChartClassifyId int `description:"分类id"` ChartEdbInfoList []*ChartSaveItem `description:"指标及配置信息"` ChartType int `description:"生成样式:1:曲线图,2:季节性图,3:面积图，4：柱状图，5：散点图，6：组合图，7：柱方图，8：商品价格曲线图，9：相关性图,10:截面散点图"` DateType int `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间,6:起始日期至今"` StartDate string `description:"自定义开始日期"` EndDate string `description:"自定义结束日期"` Calendar string `description:"公历/农历"` SeasonStartDate string `description:"季节性图开始日期"` SeasonEndDate string `description:"季节性图开始日期"` LeftMin string `description:"图表左侧最小值"` LeftMax string `description:"图表左侧最大值"` RightMin string `description:"图表右侧最小值"` RightMax string `description:"图表右侧最大值"` BarChartInfo BarChartInfoReq `description:"柱方图的配置"` CorrelationChartInfo CorrelationChartInfoReq `description:"相关性图表配置"` ExtraConfig string `description:"图表额外配置信息,json字符串"` } type EditChartEnInfoReq struct { ChartInfoId int `description:"图表ID"` ChartNameEn string `description:"英文图表名称"` ChartEdbInfoList []*EditChartEnInfoEdbItem `description:"指标及配置信息"` ExtraConfig string `description:"图表额外配置信息,json字符串"` } type EditChartEnInfoEdbItem struct { EdbInfoId int `description:"指标ID"` EdbNameEn string `description:"英文指标名称"` UnitEn string `description:"英文单位"` } func ModifyChartInfo(item *EditChartInfoReq) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE chart_info SET chart_name =?, chart_classify_id = ?, is_set_name=1, modify_time = NOW() WHERE chart_info_id = ?` _, err = o.Raw(sql, item.ChartName, item.ChartClassifyId, item.ChartInfoId).Exec() return } type ChartInfoList struct { ChartInfoId int `orm:"column(chart_info_id);pk"` ChartName string `description:"来源名称"` ChartChartClassifyId int `description:"图表分类id"` SysUserId int SysUserRealName string UniqueCode string `description:"图表唯一编码"` CreateTime time.Time ModifyTime time.Time EdbInfoList []*EdbInfoList } type ChartInfoListResp struct { Paging *paging.PagingItem Item *ChartInfoList } func GetChartInfoByCondition(condition string, pars []interface{}) (item *ChartInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE 1=1 ` if condition != "" { sql += condition } err = o.Raw(sql, pars).QueryRow(&item) return } func GetChartInfoByNewest(source int) (item *ChartInfoList, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE 1=1 AND source = ? ORDER BY modify_time DESC LIMIT 1 ` err = o.Raw(sql, source).QueryRow(&item) return } func ModifyChartInfoModifyTime(chartInfoId int64) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE chart_info SET modify_time = NOW() WHERE chart_info_id = ? ` _, err = o.Raw(sql, chartInfoId).Exec() return } type MoveChartInfoReq struct { ChartInfoId int `description:"图表ID"` PrevChartInfoId int `description:"上一个图表ID"` NextChartInfoId int `description:"下一个图表ID"` ChartClassifyId int `description:"分类id"` } func MoveChartInfo(chartInfoId, classifyId int) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE chart_info SET chart_classify_id = ? WHERE chart_info_id = ?` _, err = o.Raw(sql, classifyId, chartInfoId).Exec() return } type DeleteChartInfoReq struct { ChartInfoId int `description:"图表ID"` } type EdbDataList struct { EdbDataId int `description:" 指标数据ID"` EdbInfoId int `description:"指标ID"` DataTime string //`json:"-" description:"数据日期"` DataTimestamp int64 `description:"数据日期"` Value float64 `description:"数据值"` } func GetEdbDataList(source, endInfoId int, startDate, endDate string) (list []*EdbDataList, err error) { tableName := GetEdbDataTableName(source) if tableName == "" { err = errors.New("无效的渠道：" + strconv.Itoa(source)) list = make([]*EdbDataList, 0) return list, err } var pars []interface{} sql := `SELECT edb_data_id,edb_info_id,data_time,value,data_timestamp FROM %s WHERE edb_info_id=? ` if startDate != "" { sql += ` AND data_time>=? ` pars = append(pars, startDate) } if endDate != "" { sql += ` AND data_time<=? ` pars = append(pars, endDate) } sql += ` ORDER BY data_time ASC ` sql = fmt.Sprintf(sql, tableName) o := orm.NewOrmUsingDB("data") _, err = o.Raw(sql, endInfoId, pars).QueryRows(&list) return } type EdbDataListByUniqueCode struct { EdbDataId int `description:" 指标数据ID" json:"-"` EdbInfoId int `description:"指标ID" json:"-"` DataTime string //`json:"-" description:"数据日期"` DataTimestamp int64 `description:"数据日期"` Value float64 `description:"数据值"` } func GetEdbDataListByByUniqueCode(source, endInfoId int, startDate, endDate string) (list []*EdbDataListByUniqueCode, err error) { tableName := GetEdbDataTableName(source) if tableName == "" { err = errors.New("无效的渠道：" + strconv.Itoa(source)) list = make([]*EdbDataListByUniqueCode, 0) return list, err } var pars []interface{} sql := `SELECT edb_data_id,edb_info_id,data_time,value,data_timestamp FROM %s WHERE edb_info_id=? ` if startDate != "" { sql += ` AND data_time>=? ` pars = append(pars, startDate) } if endDate != "" { sql += ` AND data_time<=? ` pars = append(pars, endDate) } sql += ` ORDER BY data_time ASC ` sql = fmt.Sprintf(sql, tableName) o := orm.NewOrmUsingDB("data") _, err = o.Raw(sql, endInfoId, pars).QueryRows(&list) return } func GetEdbDataLunarList(endInfoId int, startDate, endDate string) (list []*EdbDataList, err error) { tableName := "edb_data_quarter" sql := `SELECT edb_data_id,edb_info_id,data_time,TRUNCATE(value,2) AS value,data_timestamp FROM %s WHERE edb_info_id=? AND data_time>=? AND data_time<=? ORDER BY data_time ASC ` sql = fmt.Sprintf(sql, tableName) o := orm.NewOrmUsingDB("data") _, err = o.Raw(sql, endInfoId, startDate, endDate).QueryRows(&list) return } func GetEdbDataListMinAndMax(source, endInfoId int, startDate, endDate string) (min_data, max_data float64, err error) { tableName := GetEdbDataTableName(source) sql := `SELECT min(value) AS min_data,max(value) AS max_data FROM %s WHERE edb_info_id=? ` var pars []interface{} var condition string if startDate != "" { condition += ` AND data_time>=? ` pars = append(pars, startDate) } if endDate != "" { condition += ` AND data_time<=? ` pars = append(pars, endDate) } sql = fmt.Sprintf(sql, tableName) o := orm.NewOrmUsingDB("data") if condition != "" { sql += condition } err = o.Raw(sql, endInfoId, pars).QueryRow(&min_data, &max_data) return } type ChartEdbInfoMapping struct { EdbInfoId int `description:"指标id"` SourceName string `description:"来源名称"` Source int `description:"来源id"` EdbCode string `description:"指标编码"` EdbName string `description:"指标名称"` EdbAliasName string `description:"指标名称（别名）"` EdbNameEn string `description:"英文指标名称"` EdbAliasNameEn string `description:"英文指标名称（别名）"` EdbType int `description:"指标类型:1:基础指标,2:计算指标"` Frequency string `description:"频率"` FrequencyEn string `description:"英文频率"` Unit string `description:"单位"` UnitEn 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:领先指标"` EdbInfoCategoryType int `description:"0:普通指标,1:预测指标"` LeadValue int `description:"领先值"` LeadUnit string `description:"领先单位"` LeadUnitEn string `description:"领先英文单位"` ChartStyle string `description:"图表类型"` ChartColor string `description:"颜色"` PredictChartColor string `description:"预测数据的颜色"` ChartWidth float64 `description:"线条大小"` ChartType int `description:"生成样式:1:曲线图,2:季节性图,3:面积图，4：柱状图，5：散点图，6：组合图，7：柱方图，8：商品价格曲线图，9：相关性图"` LatestDate string `description:"数据最新日期"` LatestValue float64 `description:"数据最新值"` MoveLatestDate string `description:"移动后的数据最新日期"` UniqueCode string `description:"指标唯一编码"` MinValue float64 `json:"-" description:"最小值"` MaxValue float64 `json:"-" description:"最大值"` DataList interface{} IsNullData bool `json:"-" description:"是否空数据"` MappingSource int `description:"1：ETA图库；2：商品价格曲线"` RegionType string `description:"交易所来源，海外还是国内" json:"-"` ClassifyId int `description:"分类id"` } type QuarterData struct { Year int DataList []*EdbDataList CuttingDataTimestamp int64 `description:"切割的时间戳"` } type QuarterDataByUniqueCode struct { Year int DataList []*EdbDataListByUniqueCode } type ChartEdbInfoDetailResp struct { EdbInfo *ChartEdbInfoMapping } type ChartInfoDetailResp struct { ChartInfo *ChartInfoView EdbInfoList []*ChartEdbInfoMapping XEdbIdValue []int `description:"柱方图的x轴数据，指标id"` YDataList []YData `description:"柱方图的y轴数据"` XDataList []XData `description:"商品价格曲线的X轴数据"` BarChartInfo BarChartInfoReq `description:"柱方图的配置"` CorrelationChartInfo *CorrelationInfo `description:"相关性图表信息"` DataResp interface{} `description:"图表数据,根据图的类型而定的,没有确定的数据格式"` } // XData 商品价格曲线的的x轴数据 type XData struct { Name string `description:"别名"` NameEn string `description:"英文别名"` } // YData 柱方图的y轴数据 type YData struct { Date string `description:"数据日期"` ConfigDate time.Time `description:"配置的日期" json:"-"` Color string `description:"数据颜色"` Name string `description:"别名"` NameEn string `description:"英文别名"` Value []float64 `description:"每个指标的值"` NoDataEdbList []int `description:"没有数据的指标列表"` XEdbInfoIdList []int `description:"对应X轴的指标id列表"` NameList []string `description:"每个值对应的名称"` EnNameList []string `description:"每个值对应的英文名称"` EdbValMap map[int]float64 `description:"指标与值的对应" json:"-"` M []int `description:"对应开始日期的间隔值" json:"-"` } func ModifyChartInfoAndMapping(edbInfoIdStr string, req *SaveChartInfoReq, chartType int) (err error) { o := orm.NewOrmUsingDB("data") to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() //非季节图 if chartType != 2 { updateStr := `edb_info_ids=?, modify_time = NOW(), date_type=?, start_date=?, end_date=?, left_min=?, left_max=?, right_min=?, right_max=?` pars := []interface{}{edbInfoIdStr, req.DateType, req.StartDate, req.EndDate, req.LeftMin, req.LeftMax, req.RightMin, req.RightMax} if req.ExtraConfig != `` { updateStr += `,extra_config=? ` pars = append(pars, req.ExtraConfig) } pars = append(pars, req.ChartInfoId) sql := ` UPDATE chart_info SET ` + updateStr + ` WHERE chart_info_id = ?` _, err = to.Raw(sql, pars).Exec() if err != nil { fmt.Println("UPDATE chart_info Err:", err.Error()) return err } } else { sql := ` UPDATE chart_info SET edb_info_ids=?, modify_time = NOW(), calendar=?, season_start_date=?, season_end_date=?, left_min=?, left_max=?, right_min=?, right_max=? WHERE chart_info_id = ?` _, err = to.Raw(sql, edbInfoIdStr, req.Calendar, req.SeasonStartDate, req.SeasonEndDate, req.LeftMin, req.LeftMax, req.RightMin, req.RightMax, req.ChartInfoId).Exec() if err != nil { fmt.Println("UPDATE chart_info Err:", err.Error()) return err } } var edbInfoIdArr []string mapList := make([]*ChartEdbMapping, 0) for _, v := range req.ChartEdbInfoList { edbInfoIdArr = append(edbInfoIdArr, strconv.Itoa(v.EdbInfoId)) var count int csql := `SELECT COUNT(1) AS count FROM chart_edb_mapping WHERE chart_info_id=? AND edb_info_id=? ` err = to.Raw(csql, req.ChartInfoId, v.EdbInfoId).QueryRow(&count) if err != nil { fmt.Println("QueryRow Err:", err.Error()) return err } if count > 0 { msql := `UPDATE chart_edb_mapping SET modify_time = NOW(), max_data = ?, min_data = ?, is_order = ?, is_axis = ?, edb_info_type = ?, lead_value = ?, lead_unit = ?, chart_style = ?, chart_color = ?, predict_chart_color = ?, chart_width = ? WHERE chart_info_id =? AND edb_info_id=? ` _, err = to.Raw(msql, v.MaxData, v.MinData, v.IsOrder, v.IsAxis, v.EdbInfoType, v.LeadValue, v.LeadUnit, v.ChartStyle, v.ChartColor, v.PredictChartColor, v.ChartWidth, req.ChartInfoId, v.EdbInfoId).Exec() if err != nil { fmt.Println("chart_edb_mapping Err:" + err.Error()) return err } } else { mapItem := new(ChartEdbMapping) mapItem.ChartInfoId = req.ChartInfoId mapItem.EdbInfoId = v.EdbInfoId mapItem.CreateTime = time.Now() mapItem.ModifyTime = time.Now() timestamp := strconv.FormatInt(time.Now().UnixNano(), 10) mapItem.UniqueCode = utils.MD5(utils.CHART_PREFIX + "_" + timestamp) mapItem.MaxData = v.MaxData mapItem.MinData = v.MinData mapItem.IsOrder = v.IsOrder mapItem.IsAxis = v.IsAxis mapItem.EdbInfoType = v.EdbInfoType mapItem.LeadValue = v.LeadValue mapItem.LeadUnit = v.LeadUnit mapItem.ChartStyle = v.ChartStyle mapItem.ChartColor = v.ChartColor mapItem.PredictChartColor = v.PredictChartColor mapItem.ChartWidth = v.ChartWidth mapList = append(mapList, mapItem) } } if len(mapList) > 0 { _, err = to.InsertMulti(1, mapList) if err != nil { fmt.Println("AddChartEdbMapping Err:" + err.Error()) return err } } if len(edbInfoIdArr) > 0 { edbInfoIdStr := strings.Join(edbInfoIdArr, ",") if edbInfoIdStr != "" { dsql := `DELETE FROM chart_edb_mapping WHERE chart_info_id=? AND edb_info_id NOT IN(` + edbInfoIdStr + `)` _, err = to.Raw(dsql, req.ChartInfoId).Exec() if err != nil { fmt.Println("delete err:" + err.Error()) return err } } } return } // EditChartInfoAndMapping 修改 ETA图库 的 图表与指标 的关系 func EditChartInfoAndMapping(req *EditChartInfoReq, edbInfoIdStr string, calendar string, dateType, disabled int, barChartConf string, chartEdbInfoList []*ChartSaveItem) (err error) { o := orm.NewOrmUsingDB("data") to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() var pars []interface{} pars = append(pars, req.ChartName) pars = append(pars, edbInfoIdStr) pars = append(pars, req.ChartType) pars = append(pars, req.ChartClassifyId) pars = append(pars, disabled) pars = append(pars, barChartConf) pars = append(pars, req.ExtraConfig) sql := ` UPDATE chart_info SET chart_name =?, edb_info_ids=?, chart_type=?, chart_classify_id = ?, modify_time = NOW(), disabled = ?, bar_config = ?,extra_config = ? ` if calendar != "" { sql += `,calendar = ? ` pars = append(pars, calendar) } if dateType > 0 { sql += `,date_type = ? ` pars = append(pars, dateType) } sql += `,start_date = ? ` pars = append(pars, req.StartDate) sql += `,end_date = ? ` pars = append(pars, req.EndDate) sql += `,season_start_date = ? ` pars = append(pars, req.SeasonStartDate) sql += `,season_end_date = ? ` pars = append(pars, req.SeasonEndDate) sql += `,left_min = ? ` pars = append(pars, req.LeftMin) sql += `,left_max = ? ` pars = append(pars, req.LeftMax) sql += `,right_min = ? ` pars = append(pars, req.RightMin) sql += `,right_max = ? ` pars = append(pars, req.RightMax) sql += `WHERE chart_info_id = ?` pars = append(pars, req.ChartInfoId) _, err = to.Raw(sql, pars).Exec() if err != nil { fmt.Println("UPDATE chart_info Err:", err.Error()) return err } chartEdbMappingIdList := make([]string, 0) for _, v := range chartEdbInfoList { // 查询该指标是否存在,如果存在的话,那么就去修改,否则新增 var tmpChartEdbMapping *ChartEdbMapping csql := `SELECT * FROM chart_edb_mapping WHERE chart_info_id=? AND edb_info_id=? AND source = ? ` err = to.Raw(csql, req.ChartInfoId, v.EdbInfoId, utils.CHART_SOURCE_DEFAULT).QueryRow(&tmpChartEdbMapping) if err != nil && err.Error() != utils.ErrNoRow() { fmt.Println("QueryRow Err:", err.Error()) return err } if tmpChartEdbMapping != nil { chartEdbMappingIdList = append(chartEdbMappingIdList, strconv.Itoa(tmpChartEdbMapping.ChartEdbMappingId)) tmpChartEdbMapping.ModifyTime = time.Now() tmpChartEdbMapping.MaxData = v.MaxData tmpChartEdbMapping.MinData = v.MinData tmpChartEdbMapping.IsOrder = v.IsOrder tmpChartEdbMapping.IsAxis = v.IsAxis tmpChartEdbMapping.EdbInfoType = v.EdbInfoType tmpChartEdbMapping.LeadValue = v.LeadValue tmpChartEdbMapping.LeadUnit = v.LeadUnit tmpChartEdbMapping.ChartStyle = v.ChartStyle tmpChartEdbMapping.ChartColor = v.ChartColor tmpChartEdbMapping.PredictChartColor = v.PredictChartColor tmpChartEdbMapping.ChartWidth = v.ChartWidth _, err = to.Update(tmpChartEdbMapping, "ModifyTime", "MaxData", "MinData", "IsOrder", "IsAxis", "EdbInfoType", "LeadValue", "LeadUnit", "ChartStyle", "ChartColor", "PredictChartColor", "ChartWidth") if err != nil { fmt.Println("chart_edb_mapping Err:" + err.Error()) return err } } else { mapItem := new(ChartEdbMapping) mapItem.ChartInfoId = req.ChartInfoId mapItem.EdbInfoId = v.EdbInfoId mapItem.CreateTime = time.Now() mapItem.ModifyTime = time.Now() timestamp := strconv.FormatInt(time.Now().UnixNano(), 10) mapItem.UniqueCode = utils.MD5(utils.CHART_PREFIX + "_" + timestamp + "_" + strconv.Itoa(v.EdbInfoId)) mapItem.MaxData = v.MaxData mapItem.MinData = v.MinData mapItem.IsOrder = v.IsOrder mapItem.IsAxis = v.IsAxis mapItem.EdbInfoType = v.EdbInfoType mapItem.LeadValue = v.LeadValue mapItem.LeadUnit = v.LeadUnit mapItem.ChartStyle = v.ChartStyle mapItem.ChartColor = v.ChartColor mapItem.PredictChartColor = v.PredictChartColor mapItem.ChartWidth = v.ChartWidth mapItem.Source = utils.CHART_SOURCE_DEFAULT tmpId, err := to.Insert(mapItem) if err != nil { fmt.Println("AddChartEdbMapping Err:" + err.Error()) return err } mapItem.ChartEdbMappingId = int(tmpId) chartEdbMappingIdList = append(chartEdbMappingIdList, strconv.Itoa(mapItem.ChartEdbMappingId)) } } if len(chartEdbMappingIdList) > 0 { chartEdbMappingIdStr := strings.Join(chartEdbMappingIdList, ",") dsql := `DELETE FROM chart_edb_mapping WHERE chart_info_id=? AND chart_edb_mapping_id NOT IN(` + chartEdbMappingIdStr + `)` _, err = to.Raw(dsql, req.ChartInfoId).Exec() if err != nil { fmt.Println("delete err:" + err.Error()) return err } } return } // EditFutureGoodChartInfoAndMapping 修改商品价格曲线的 图表与指标 的关系 func EditFutureGoodChartInfoAndMapping(req *EditChartInfoReq, edbInfoIdStr string, calendar string, dateType, disabled int, barChartConf string) (err error) { o := orm.NewOrmUsingDB("data") to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() var pars []interface{} pars = append(pars, req.ChartName) pars = append(pars, edbInfoIdStr) pars = append(pars, req.ChartType) pars = append(pars, req.ChartClassifyId) pars = append(pars, disabled) pars = append(pars, barChartConf) sql := ` UPDATE chart_info SET chart_name =?, edb_info_ids=?, chart_type=?, chart_classify_id = ?, modify_time = NOW(), disabled = ?, bar_config = ? ` if calendar != "" { sql += `,calendar = ? ` pars = append(pars, calendar) } if dateType > 0 { sql += `,date_type = ? ` pars = append(pars, dateType) } sql += `,start_date = ? ` pars = append(pars, req.StartDate) sql += `,end_date = ? ` pars = append(pars, req.EndDate) sql += `,season_start_date = ? ` pars = append(pars, req.SeasonStartDate) sql += `,season_end_date = ? ` pars = append(pars, req.SeasonEndDate) sql += `,left_min = ? ` pars = append(pars, req.LeftMin) sql += `,left_max = ? ` pars = append(pars, req.LeftMax) sql += `,right_min = ? ` pars = append(pars, req.RightMin) sql += `,right_max = ? ` pars = append(pars, req.RightMax) sql += `WHERE chart_info_id = ?` pars = append(pars, req.ChartInfoId) _, err = to.Raw(sql, pars).Exec() if err != nil { fmt.Println("UPDATE chart_info Err:", err.Error()) return err } chartEdbMappingIdList := make([]string, 0) for _, v := range req.BarChartInfo.EdbInfoIdList { // 查询该指标是否存在,如果存在的话,那么就去修改,否则新增 var tmpChartEdbMapping *ChartEdbMapping csql := `SELECT * FROM chart_edb_mapping WHERE chart_info_id=? AND edb_info_id=? AND source = ? ` err = to.Raw(csql, req.ChartInfoId, v.EdbInfoId, v.Source).QueryRow(&tmpChartEdbMapping) if err != nil && err.Error() != utils.ErrNoRow() { fmt.Println("QueryRow Err:", err.Error()) return err } if tmpChartEdbMapping != nil { chartEdbMappingIdList = append(chartEdbMappingIdList, strconv.Itoa(tmpChartEdbMapping.ChartEdbMappingId)) //tmpChartEdbMapping.ModifyTime = time.Now() //tmpChartEdbMapping.MaxData = v.MaxData //tmpChartEdbMapping.MinData = v.MinData //tmpChartEdbMapping.IsOrder = v.IsOrder //tmpChartEdbMapping.IsAxis = v.IsAxis //tmpChartEdbMapping.EdbInfoType = v.EdbInfoType //tmpChartEdbMapping.LeadValue = v.LeadValue //tmpChartEdbMapping.LeadUnit = v.LeadUnit //tmpChartEdbMapping.ChartStyle = v.ChartStyle //tmpChartEdbMapping.ChartColor = v.ChartColor //tmpChartEdbMapping.PredictChartColor = v.PredictChartColor //tmpChartEdbMapping.ChartWidth = v.ChartWidth //_, err = to.Update(tmpChartEdbMapping, "ModifyTime", "MaxData", "MinData", "IsOrder", "IsAxis", "EdbInfoType", "LeadValue", "LeadUnit", "ChartStyle", "ChartColor", "PredictChartColor", "ChartWidth") //if err != nil { // fmt.Println("chart_edb_mapping Err:" + err.Error()) // return err //} } else { mapItem := new(ChartEdbMapping) mapItem.ChartInfoId = req.ChartInfoId mapItem.EdbInfoId = v.EdbInfoId mapItem.CreateTime = time.Now() mapItem.ModifyTime = time.Now() timestamp := strconv.FormatInt(time.Now().UnixNano(), 10) mapItem.UniqueCode = utils.MD5(utils.CHART_PREFIX + "_" + timestamp + "_" + strconv.Itoa(v.EdbInfoId)) //mapItem.MaxData = v.MaxData //mapItem.MinData = v.MinData mapItem.IsOrder = true mapItem.IsAxis = 1 mapItem.EdbInfoType = 1 //mapItem.LeadValue = v.LeadValue //mapItem.LeadUnit = v.LeadUnit //mapItem.ChartStyle = v.ChartStyle //mapItem.ChartColor = v.ChartColor //mapItem.PredictChartColor = v.PredictChartColor //mapItem.ChartWidth = v.ChartWidth mapItem.Source = v.Source tmpId, err := to.Insert(mapItem) if err != nil { fmt.Println("AddChartEdbMapping Err:" + err.Error()) return err } mapItem.ChartEdbMappingId = int(tmpId) chartEdbMappingIdList = append(chartEdbMappingIdList, strconv.Itoa(mapItem.ChartEdbMappingId)) } } if len(chartEdbMappingIdList) > 0 { chartEdbMappingIdStr := strings.Join(chartEdbMappingIdList, ",") dsql := `DELETE FROM chart_edb_mapping WHERE chart_info_id=? AND chart_edb_mapping_id NOT IN(` + chartEdbMappingIdStr + `)` _, err = to.Raw(dsql, req.ChartInfoId).Exec() if err != nil { fmt.Println("delete err:" + err.Error()) return err } } return } func EditChartEnInfoAndEdbEnInfo(req *EditChartEnInfoReq) (err error) { o := orm.NewOrmUsingDB("data") to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() var pars []interface{} updateField := ` chart_name_en =?, ` pars = append(pars, req.ChartNameEn) if req.ExtraConfig != `` { updateField += ` extra_config =?, ` pars = append(pars, req.ExtraConfig) } sql := ` UPDATE chart_info SET ` + updateField + ` modify_time = NOW() WHERE chart_info_id = ?` pars = append(pars, req.ChartInfoId) _, err = to.Raw(sql, pars).Exec() if err != nil { fmt.Println("UPDATE chart_info Err:", err.Error()) return err } var edbInfoIdArr []string for _, v := range req.ChartEdbInfoList { edbInfoIdArr = append(edbInfoIdArr, strconv.Itoa(v.EdbInfoId)) var count int csql := `SELECT COUNT(1) AS count FROM chart_edb_mapping WHERE chart_info_id=? AND edb_info_id=? ` err = to.Raw(csql, req.ChartInfoId, v.EdbInfoId).QueryRow(&count) if err != nil { fmt.Println("QueryRow Err:", err.Error()) return err } if count > 0 { msql := ` UPDATE edb_info SET edb_name_en =?, unit_en = ?, modify_time = NOW() WHERE edb_info_id = ? ` _, err = to.Raw(msql, v.EdbNameEn, v.UnitEn, v.EdbInfoId).Exec() if err != nil { fmt.Println("edb_info Err:" + err.Error()) return err } } } return } func ModifyEdbDatadTimestamp(source, edbDataId int, dataTimestamp int64) (err error) { tableName := GetEdbDataTableName(source) sql := `UPDATE %s SET data_timestamp=? WHERE edb_data_id=? ` sql = fmt.Sprintf(sql, tableName) o := orm.NewOrmUsingDB("data") _, err = o.Raw(sql, dataTimestamp, edbDataId).Exec() return } type AddChartInfoReq struct { ChartEdbInfoList []*ChartSaveItem `description:"指标及配置信息"` ChartClassifyId int `description:"分类id"` ChartName string `description:"图表名称"` ChartType int `description:"生成样式:1:曲线图,2:季节性图,3:面积图，4：柱状图，5：散点图，6：组合图，7：柱方图"` DateType int `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间,6:起始日期至今"` StartDate string `description:"自定义开始日期"` EndDate string `description:"自定义结束日期"` Calendar string `description:"公历/农历"` SeasonStartDate string `description:"季节性图开始日期"` SeasonEndDate string `description:"季节性图开始日期"` LeftMin string `description:"图表左侧最小值"` LeftMax string `description:"图表左侧最大值"` RightMin string `description:"图表右侧最小值"` RightMax string `description:"图表右侧最大值"` BarChartInfo BarChartInfoReq `description:"柱方图的配置"` CorrelationChartInfo CorrelationChartInfoReq `description:"相关性图表配置"` ExtraConfig string `description:"图表额外配置信息,json字符串"` ChartImage string `description:"封面图" json:"-"` } type AddChartInfoResp struct { ChartInfoId int `description:"图表id"` UniqueCode string `description:"图表唯一编码"` ChartType int `description:"生成样式:1:曲线图,2:季节性图"` } // 判断图表指标是否已经存在 func ChartInfoExist(condition, edbInfoIdStr string) (count int, err error) { sql := `SELECT COUNT(1) AS count FROM ( SELECT GROUP_CONCAT(edb_info_id ORDER BY edb_info_id ASC SEPARATOR ',') AS edb_info_id_str FROM chart_edb_mapping AS a WHERE 1=1` if condition != "" { sql += condition } sql += ` GROUP BY chart_info_id )AS t WHERE t.edb_info_id_str=? GROUP BY t.edb_info_id_str ` o := orm.NewOrmUsingDB("data") err = o.Raw(sql, edbInfoIdStr).QueryRow(&count) return } func ChartInfoSearchByKeyWord(KeyWord string, showSysId int) (searchList []*ChartInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE 1=1 ` var pars []interface{} if KeyWord != "" { sql += `AND chart_name LIKE ? ` pars = append(pars, "%"+KeyWord+"%") } if showSysId > 0 { sql += ` AND sys_user_id = ? ` pars = append(pars, showSysId) } sql += ` ORDER BY create_time DESC ` if KeyWord == "" { sql += ` LIMIT 100 ` } _, err = o.Raw(sql, pars).QueryRows(&searchList) return } // ChartInfoSearchByEmptyKeyWord 没有关键字的时候获取默认100条数据 func ChartInfoSearchByEmptyKeyWord(showSysId int, sourceList []int, noPermissionChartIdList []int) (total int64, searchList []*ChartInfo, err error) { num := len(sourceList) o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE 1=1 AND source in (` + utils.GetOrmInReplace(num) + `)` var pars []interface{} pars = append(pars, sourceList) if showSysId > 0 { sql += ` AND sys_user_id = ? ` pars = append(pars, showSysId) } lenNoPermissionChartIdList := len(noPermissionChartIdList) if lenNoPermissionChartIdList > 0 { sql += ` AND chart_info_id not in (` + utils.GetOrmInReplace(lenNoPermissionChartIdList) + `) ` pars = append(pars, noPermissionChartIdList) } sql += ` ORDER BY create_time DESC LIMIT 100 ` total, err = o.Raw(sql, pars).QueryRows(&searchList) return } func GetNextChartInfo(classifyId int) (item *ChartInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT b.* FROM chart_classify AS a INNER JOIN chart_info AS b ON a.chart_classify_id=b.chart_classify_id WHERE a.chart_classify_id>? ORDER BY a.chart_classify_id ASC LIMIT 1 ` err = o.Raw(sql, classifyId).QueryRow(&item) return } // GetChartInfoRefreshData 获取图表关联的指标信息 func GetChartInfoRefreshData(chartInfoId int) (baseEdbInfoArr, calculateInfoArr []*EdbInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT b.* 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=? ` edbInfoList := make([]*EdbInfo, 0) _, err = o.Raw(sql, chartInfoId).QueryRows(&edbInfoList) if err != nil { return } for _, v := range edbInfoList { fmt.Println(v.EdbInfoId, v.EdbType) if v.EdbType == 1 { baseEdbInfoArr = append(baseEdbInfoArr, v) } else { calculateInfoArr = append(calculateInfoArr, v) newBaseEdbInfoArr, newCalculateInfoArr, err := GetChartRefreshEdbInfo(v.EdbInfoId, v.Source, 0) if err != nil { return baseEdbInfoArr, calculateInfoArr, err } baseEdbInfoArr = append(baseEdbInfoArr, newBaseEdbInfoArr...) calculateInfoArr = append(calculateInfoArr, newCalculateInfoArr...) } } return } func GetChartRefreshEdbInfo(edbInfoId, source, n int) (baseEdbInfoArr, calculateInfoArr []*EdbInfo, err error) { calculateList, err := GetEdbInfoCalculateMap(edbInfoId, source) if err != nil && err.Error() != utils.ErrNoRow() { return } n++ for _, item := range calculateList { fmt.Println(item.EdbInfoId) if item.EdbType == 1 { baseEdbInfoArr = append(baseEdbInfoArr, item) } else { calculateInfoArr = append(calculateInfoArr, item) if n > 10 { return } newBaseEdbInfoArr, newCalculateInfoArr, _ := GetChartRefreshEdbInfo(item.EdbInfoId, item.Source, n) baseEdbInfoArr = append(baseEdbInfoArr, newBaseEdbInfoArr...) calculateInfoArr = append(calculateInfoArr, newCalculateInfoArr...) } } return } type SetChartInfoImageReq struct { ChartInfoId int `description:"图表ID"` ImageUrl string `description:"图表图片地址"` } func EditChartInfoImage(req *SetChartInfoImageReq) (err error) { o := orm.NewOrmUsingDB("data") sql := ` UPDATE chart_info SET chart_image=?, modify_time = NOW() WHERE chart_info_id = ? ` _, err = o.Raw(sql, req.ImageUrl, req.ChartInfoId).Exec() if err != nil { fmt.Println("EditChartInfoImage Err:", err.Error()) return err } return } type ChartInfoDetailFromUniqueCodeResp struct { ChartInfo *ChartInfoView Status bool `description:"true:图表存在，false:图表不存在"` EdbInfoList []*ChartEdbInfoMapping XEdbIdValue []int `description:"柱方图的x轴数据，指标id"` YDataList []YData `description:"柱方图的y轴数据"` XDataList []XData `description:"商品价格曲线的X轴数据"` BarChartInfo BarChartInfoReq `description:"柱方图的配置"` CorrelationChartInfo *CorrelationInfo `description:"相关性图表信息"` DataResp interface{} `description:"图表数据,根据图的类型而定的,没有确定的数据格式"` } func GetChartInfoByUniqueCode(uniqueCode string) (item *ChartInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE unique_code=? ` err = o.Raw(sql, uniqueCode).QueryRow(&item) return } func GetChartInfoViewByUniqueCode(uniqueCode string) (item *ChartInfoView, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE unique_code=? ` err = o.Raw(sql, uniqueCode).QueryRow(&item) return } // GetFirstChartInfoByClassifyId 获取当前分类下，且排序数相同 的排序第一条的数据 func GetFirstChartInfoByClassifyId(classifyId int) (item *ChartInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE chart_classify_id=? order by sort asc,chart_info_id asc limit 1` err = o.Raw(sql, classifyId).QueryRow(&item) return } // UpdateChartInfoSortByClassifyId 根据图表id更新排序 func UpdateChartInfoSortByClassifyId(classifyId, nowSort, prevChartInfoId int, sourceList []int, updateSort string) (err error) { o := orm.NewOrmUsingDB("data") num := len(sourceList) if num <= 0 { return } sql := ` update chart_info set sort = ` + updateSort + ` WHERE chart_classify_id=? AND source in (` + utils.GetOrmInReplace(num) + `) AND ` if prevChartInfoId > 0 { sql += ` (sort > ? or (chart_info_id > ` + fmt.Sprint(prevChartInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `))` } _, err = o.Raw(sql, classifyId, sourceList, nowSort).Exec() return } // Update 更新图表基础信息 func (chartInfo *ChartInfo) Update(cols []string) (err error) { o := orm.NewOrmUsingDB("data") _, err = o.Update(chartInfo, cols...) return } type ChartInfoView struct { ChartInfoId int `orm:"column(chart_info_id);pk"` ChartName string `description:"来源名称"` ChartNameEn string `description:"英文图表名称"` UnitEn string `description:"英文单位名称"` ChartClassifyId int `description:"图表分类id"` ChartClassifyName string `description:"图表名称"` SysUserId int SysUserRealName string UniqueCode string `description:"图表唯一编码"` CreateTime time.Time ModifyTime time.Time DateType int `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间"` StartDate string `description:"自定义开始日期"` EndDate string `description:"自定义结束日期"` IsSetName int `description:"设置名称"` EdbInfoIds string `description:"指标id"` ChartType int `description:"生成样式:1:曲线图,2:季节性图"` Calendar string `description:"公历/农历"` SeasonStartDate string `description:"季节性图开始日期"` SeasonEndDate string `description:"季节性图开始日期"` ChartImage string `description:"图表图片"` Sort int `description:"排序字段，数字越小越排前面"` IsAdd bool `description:"true:已加入我的图库,false:未加入我的图库"` MyChartId int MyChartClassifyId string `description:"我的图表分类，多个用逗号隔开"` ChartClassify []*ChartClassifyView EdbEndDate string `description:"指标最新更新日期"` LeftMin string `description:"图表左侧最小值"` LeftMax string `description:"图表左侧最大值"` RightMin string `description:"图表右侧最小值"` RightMax string `description:"图表右侧最大值"` IsEdit bool `description:"是否有编辑权限"` IsEnChart bool `description:"是否展示英文标识"` WarnMsg string `description:"错误信息"` Disabled int `description:"是否禁用，0：启用，1：禁用，默认：0"` BarConfig string `description:"柱方图的配置，json数据" json:"-"` Source int `description:"1：ETA图库；2：商品价格曲线；3：相关性图表"` //CorrelationLeadUnit string `description:"相关性图表-领先单位"` ExtraConfig string `description:"图表额外配置,json数据"` Button ChartViewButton `description:"操作按钮"` } type ChartViewButton struct { IsEdit bool `description:"是否有编辑权限"` IsEnChart bool `description:"是否展示英文标识"` IsAdd bool `description:"true:已加入我的图库,false:未加入我的图库"` IsCopy bool `description:"是否有另存为按钮"` IsSetName int `description:"设置名称"` } type ImageSvgToPngResp struct { Data struct { ResourceURL string `json:"ResourceUrl"` } `json:"Data"` ErrCode string `json:"ErrCode"` ErrMsg string `json:"ErrMsg"` IsSendEmail bool `json:"IsSendEmail"` Msg string `json:"Msg"` Ret int64 `json:"Ret"` Success bool `json:"Success"` } // GetChartInfoByClassifyIdAndName 根据分类id和图表名获取图表信息 func GetChartInfoByClassifyIdAndName(classifyId int, chartName string) (item *ChartInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE chart_classify_id = ? and chart_name=? ` err = o.Raw(sql, classifyId, chartName).QueryRow(&item) return } // BatchChartRefreshReq 批量刷新图表请求 type BatchChartRefreshReq struct { ChartInfoCode []string `description:"图表编码数组"` } // GetChartInfoListByUniqueCodeSlice 根据图表编码获取图表列表数据 func GetChartInfoListByUniqueCodeSlice(uniqueCodeSlice []string) (total int64, items []*ChartInfo, err error) { if len(uniqueCodeSlice) <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE unique_code in ("` + strings.Join(uniqueCodeSlice, `","`) + `") ` total, err = o.Raw(sql).QueryRows(&items) return } // GetChartInfoRefreshDataByChartInfoIdSlice 根据图表id切片获取对应的指标数据 func GetChartInfoRefreshDataByChartInfoIdSlice(chartInfoIdSlice []string) (baseEdbInfoArr, calculateInfoArr []*EdbInfo, err error) { o := orm.NewOrmUsingDB("data") if len(chartInfoIdSlice) <= 0 { return } sql := ` SELECT b.* 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 in (` + strings.Join(chartInfoIdSlice, ",") + `) ` edbInfoList := make([]*EdbInfo, 0) _, err = o.Raw(sql).QueryRows(&edbInfoList) if err != nil { return } for _, v := range edbInfoList { if v.EdbType == 1 { baseEdbInfoArr = append(baseEdbInfoArr, v) } else { calculateInfoArr = append(calculateInfoArr, v) newBaseEdbInfoArr, newCalculateInfoArr, err := GetChartRefreshEdbInfo(v.EdbInfoId, v.Source, 0) if err != nil { return baseEdbInfoArr, calculateInfoArr, err } baseEdbInfoArr = append(baseEdbInfoArr, newBaseEdbInfoArr...) calculateInfoArr = append(calculateInfoArr, newCalculateInfoArr...) } } return } // CopyAddChartInfoReq 复制并新增图表 type CopyAddChartInfoReq struct { ChartInfoId int `description:"待复制的图表id"` ChartClassifyId int `description:"分类id"` ChartName string `description:"图表名称"` } // ChartInfoListByEsResp 图表数据Es搜索返回 type ChartInfoListByEsResp struct { Paging *paging.PagingItem List []*ChartInfoMore } // GetChartInfoListByCondition 根据条件获取图表数据了列表 func GetChartInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (items []*ChartInfo, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE 1=1 ` if condition != "" { sql += condition } sql += ` ORDER BY chart_info_id DESC LIMIT ?,? ` _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items) return } // GetChartInfoListGroupByUserId 根据指标id列表、用户分组获取指标信息 func GetChartInfoListGroupByUserId(edbIdList []string) (items []*ChartInfo, err error) { num := len(edbIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := ` SELECT * FROM chart_info WHERE chart_info_id in (` + utils.GetOrmInReplace(num) + `) GROUP BY sys_user_id ` _, err = o.Raw(sql, edbIdList).QueryRows(&items) return } // ModifyChartInfoUserIdByCodeList 根据指标code列表修改创建人 func ModifyChartInfoUserIdByCodeList(edbIdList []string, userId int, userName string) (err error) { num := len(edbIdList) if num <= 0 { return } o := orm.NewOrmUsingDB("data") sql := `UPDATE chart_info SET sys_user_id=?,sys_user_real_name=? WHERE chart_info_id in (` + utils.GetOrmInReplace(num) + `) ` _, err = o.Raw(sql, userId, userName, edbIdList).Exec() return } // BarChartInfoReq 柱方图预览请求数据 type BarChartInfoReq struct { EdbInfoIdList []BarChartInfoEdbItemReq `description:"指标信息"` DateList []BarChartInfoDateReq `description:"日期配置"` Sort BarChartInfoSortReq `description:"排序"` XEdbList []BarChartInfoEdbItemReq `description:"X轴选择的指标列表"` YEdbList []BarChartInfoEdbItemReq `description:"Y轴选择的指标列表"` } // BarChartInfoEdbItemReq 柱方图预览请求数据（指标相关） type BarChartInfoEdbItemReq struct { EdbInfoId int `description:"指标ID"` Name string `description:"别名"` NameEn string `description:"英文别名"` Source int `description:"1：ETA图库；2：商品价格"` } // BarChartInfoDateReq 柱方图预览请求数据（日期相关） type BarChartInfoDateReq struct { Type int `description:"配置类型"` Date string `description:"固定日期"` Value int `description:"N天的值"` Color string `description:"颜色"` Name string `description:"别名"` } // BarChartInfoSortReq 柱方图预览请求数据（排序相关） type BarChartInfoSortReq struct { Sort int `description:"排序类型，0：默认，1：升序，2：降序"` DateIndex int `description:"日期数据的下标，从0开始"` } // CorrelationChartInfoReq 相关性图表请求体 type CorrelationChartInfoReq struct { LeadValue int `description:"领先期数"` LeadUnit string `description:"频度"` CalculateValue int `description:"计算窗口"` CalculateUnit string `description:"计算频度"` BaseCalculateValue int `description:"基础计算窗口（滚动相关性的时候用到）"` BaseCalculateUnit string `description:"基础计算频度（滚动相关性的时候用到）"` DateType int `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间,6:起始日期至今"` StartDate string `description:"开始日期"` EndDate string `description:"结束日期"` EdbInfoIdList []CorrelationChartInfoEdbItemReq `description:"指标信息"` } // CorrelationChartInfoEdbItemReq 相关性图表请求指标 type CorrelationChartInfoEdbItemReq struct { EdbInfoId int `description:"指标ID"` Name string `description:"别名"` NameEn string `description:"英文别名"` //Source int `description:"1：ETA图库；2：商品价格"` } // EditCorrelationChartInfoAndMapping 修改相关性图表的 图表与指标 的关系 func EditCorrelationChartInfoAndMapping(req *EditChartInfoReq, edbInfoIdStr string, calendar string, dateType, disabled int, barChartConf string, correlationChart *ChartInfoCorrelation, correlationUpdateCols []string) (err error) { o := orm.NewOrmUsingDB("data") to, err := o.Begin() if err != nil { return } defer func() { if err != nil { _ = to.Rollback() } else { _ = to.Commit() } }() var pars []interface{} pars = append(pars, req.ChartName) pars = append(pars, edbInfoIdStr) pars = append(pars, req.ChartType) pars = append(pars, req.ChartClassifyId) pars = append(pars, disabled) pars = append(pars, barChartConf) sql := ` UPDATE chart_info SET chart_name =?, edb_info_ids=?, chart_type=?, chart_classify_id = ?, modify_time = NOW(), disabled = ?, bar_config = ? ` if calendar != "" { sql += `,calendar = ? ` pars = append(pars, calendar) } if dateType > 0 { sql += `,date_type = ? ` pars = append(pars, dateType) } sql += `,start_date = ? ` pars = append(pars, req.StartDate) sql += `,end_date = ? ` pars = append(pars, req.EndDate) sql += `,season_start_date = ? ` pars = append(pars, req.SeasonStartDate) sql += `,season_end_date = ? ` pars = append(pars, req.SeasonEndDate) sql += `,left_min = ? ` pars = append(pars, req.LeftMin) sql += `,left_max = ? ` pars = append(pars, req.LeftMax) sql += `,right_min = ? ` pars = append(pars, req.RightMin) sql += `,right_max = ? ` pars = append(pars, req.RightMax) sql += `WHERE chart_info_id = ?` pars = append(pars, req.ChartInfoId) _, err = to.Raw(sql, pars).Exec() if err != nil { fmt.Println("UPDATE chart_info Err:", err.Error()) return err } chartEdbMappingIdList := make([]string, 0) for _, v := range req.CorrelationChartInfo.EdbInfoIdList { // 查询该指标是否存在,如果存在的话,那么就去修改,否则新增 var tmpChartEdbMapping *ChartEdbMapping csql := `SELECT * FROM chart_edb_mapping WHERE chart_info_id=? AND edb_info_id=? AND source = ? ` err = to.Raw(csql, req.ChartInfoId, v.EdbInfoId, utils.CHART_SOURCE_CORRELATION).QueryRow(&tmpChartEdbMapping) if err != nil && err.Error() != utils.ErrNoRow() { fmt.Println("QueryRow Err:", err.Error()) return err } if tmpChartEdbMapping != nil { chartEdbMappingIdList = append(chartEdbMappingIdList, strconv.Itoa(tmpChartEdbMapping.ChartEdbMappingId)) //tmpChartEdbMapping.ModifyTime = time.Now() //tmpChartEdbMapping.MaxData = v.MaxData //tmpChartEdbMapping.MinData = v.MinData //tmpChartEdbMapping.IsOrder = v.IsOrder //tmpChartEdbMapping.IsAxis = v.IsAxis //tmpChartEdbMapping.EdbInfoType = v.EdbInfoType //tmpChartEdbMapping.LeadValue = v.LeadValue //tmpChartEdbMapping.LeadUnit = v.LeadUnit //tmpChartEdbMapping.ChartStyle = v.ChartStyle //tmpChartEdbMapping.ChartColor = v.ChartColor //tmpChartEdbMapping.PredictChartColor = v.PredictChartColor //tmpChartEdbMapping.ChartWidth = v.ChartWidth //_, err = to.Update(tmpChartEdbMapping, "ModifyTime", "MaxData", "MinData", "IsOrder", "IsAxis", "EdbInfoType", "LeadValue", "LeadUnit", "ChartStyle", "ChartColor", "PredictChartColor", "ChartWidth") //if err != nil { // fmt.Println("chart_edb_mapping Err:" + err.Error()) // return err //} } else { mapItem := new(ChartEdbMapping) mapItem.ChartInfoId = req.ChartInfoId mapItem.EdbInfoId = v.EdbInfoId mapItem.CreateTime = time.Now() mapItem.ModifyTime = time.Now() timestamp := strconv.FormatInt(time.Now().UnixNano(), 10) mapItem.UniqueCode = utils.MD5(utils.CHART_PREFIX + "_" + timestamp + "_" + strconv.Itoa(v.EdbInfoId)) //mapItem.MaxData = v.MaxData //mapItem.MinData = v.MinData mapItem.IsOrder = true mapItem.IsAxis = 1 mapItem.EdbInfoType = 1 //mapItem.LeadValue = v.LeadValue //mapItem.LeadUnit = v.LeadUnit //mapItem.ChartStyle = v.ChartStyle //mapItem.ChartColor = v.ChartColor //mapItem.PredictChartColor = v.PredictChartColor //mapItem.ChartWidth = v.ChartWidth mapItem.Source = utils.CHART_SOURCE_CORRELATION tmpId, err := to.Insert(mapItem) if err != nil { fmt.Println("AddChartEdbMapping Err:" + err.Error()) return err } mapItem.ChartEdbMappingId = int(tmpId) chartEdbMappingIdList = append(chartEdbMappingIdList, strconv.Itoa(mapItem.ChartEdbMappingId)) } } if len(chartEdbMappingIdList) > 0 { chartEdbMappingIdStr := strings.Join(chartEdbMappingIdList, ",") dsql := `DELETE FROM chart_edb_mapping WHERE chart_info_id=? AND chart_edb_mapping_id NOT IN(` + chartEdbMappingIdStr + `)` _, err = to.Raw(dsql, req.ChartInfoId).Exec() if err != nil { fmt.Println("delete err:" + err.Error()) return err } } // 相关性图表 _, err = to.Update(correlationChart, correlationUpdateCols...) return } // PreviewSectionScatterChartReq 预览截面散点图的请求 type PreviewSectionScatterChartReq struct { ChartName string `description:"图表名称"` ChartClassifyId int `description:"分类id"` ExtraConfig string `description:"图表额外配置信息,json字符串"` } // SectionScatterReq 截面散点请求 type SectionScatterReq struct { XName string `description:"x轴名称"` XNameEn string `description:"x轴名称(英文)"` XUnitName string `description:"x轴单位名称"` XUnitNameEn string `description:"x轴单位名称(英文)"` YName string `description:"y轴名称"` YNameEn string `description:"y轴名称(英文)"` YUnitName string `description:"y轴单位名称"` YUnitNameEn string `description:"y轴单位名称(英文)"` XMinValue string `description:"X轴的最小值"` XMaxValue string `description:"X轴的最大值"` YMinValue string `description:"Y轴的最小值"` YMaxValue string `description:"Y轴的最大值"` //EdbList []SectionScatterEdbItemReq `description:"指标数据"` SeriesList []SectionScatterSeriesItemReq `description:"系列数据"` } // SectionScatterSeriesItemReq 系列的请求 type SectionScatterSeriesItemReq struct { Name string `description:"系列名"` NameEn string `description:"系列名(英文名)"` Color string `description:"颜色"` EdbInfoList []SectionScatterEdbItemReq ShowTrendLine bool `description:"是否展示趋势线"` ShowFitEquation bool `description:"是否展示方程式"` ShowRSquare bool `description:"是否展示R平方"` } // SectionScatterEdbItemReq 截面散点请求的指标 type SectionScatterEdbItemReq struct { XEdbInfoId int `description:"X轴的指标ID"` YEdbInfoId int `description:"Y轴的指标ID"` Name string `description:"别名"` NameEn string `description:"英文别名"` XDateType int `description:"X轴的日期配置类型"` XDate string `description:"X轴的日期固定日期"` XDateValue int `description:"X轴的日期N天的值"` YDateType int `description:"Y轴的日期配置类型"` YDate string `description:"Y轴的日期固定日期"` YDateValue int `description:"Y轴的日期N天的值"` IsShow bool `description:"是否展示"` } // SectionScatterInfoResp 截面散点图数据 type SectionScatterInfoResp struct { XName string `description:"x轴名称"` XNameEn string `description:"x轴名称(英文)"` XUnitName string `description:"x轴单位名称"` XUnitNameEn string `description:"x轴单位名称(英文)"` YName string `description:"y轴名称"` YNameEn string `description:"y轴名称(英文)"` YUnitName string `description:"y轴单位名称"` YUnitNameEn string `description:"y轴单位名称(英文)"` XMinValue string `description:"X轴的最小值"` XMaxValue string `description:"X轴的最大值"` YMinValue string `description:"Y轴的最小值"` YMaxValue string `description:"Y轴的最大值"` DataList []SectionScatterSeriesItemResp `description:"数据列"` } // SectionScatterSeriesItemResp 系列的返回 type SectionScatterSeriesItemResp struct { Name string `description:"系列名"` NameEn string `description:"系列名(英文)"` Color string `description:"颜色"` EdbInfoList []SectionScatterEdbItemResp ShowTrendLine bool `description:"是否展示趋势线"` ShowFitEquation bool `description:"是否展示方程式"` ShowRSquare bool `description:"是否展示R平方"` TrendLine string `description:"方程式"` RSquare string `description:"R平方的值(决定系数R2)"` TrendLimitData []CoordinatePoint `description:"趋势线的前后坐标点"` } // SectionScatterEdbItemResp 截面散点的返回参数 type SectionScatterEdbItemResp struct { XEdbInfoId int `description:"X轴指标id"` XDate string `description:"X轴指标实际日期"` XName string `description:"X轴指标名称"` XNameEn string `description:"X轴指标英文名称"` XValue float64 `description:"X轴实际值"` YEdbInfoId int `description:"Y轴指标id"` YDate string `description:"Y轴指标实际日期"` YName string `description:"Y轴指标名称"` YNameEn string `description:"Y轴指标英文名称"` YValue float64 `description:"Y轴实际值"` IsShow bool `description:"是否展示"` Name string `description:"标签名称"` NameEn string `description:"英文标签名称"` } // CoordinatePoint 坐标点 type CoordinatePoint struct { X float64 Y float64 } // RollingCorrelationChartDataResp 滚动相关性图 type RollingCorrelationChartDataResp struct { XDateTimeValue []string `description:"滚动相关性图的x轴数据，日期数据"` YDataList []YData `description:"滚动相关性图的y轴数据"` } // GetChartInfoAll 用于分类展示 // @param source int 1：ETA图库；2：商品价格曲线 func GetChartInfoAllByClassifyId(source, classifyId int) (items []*ChartClassifyItems, err error) { o := orm.NewOrmUsingDB("data") sql := ` SELECT chart_info_id,chart_classify_id,chart_name AS chart_classify_name,chart_name_en AS chart_classify_name_en, unique_code,sys_user_id,sys_user_real_name,date_type,start_date,end_date,chart_type,calendar,season_start_date,season_end_date FROM chart_info WHERE chart_classify_id = ? AND source = ? ORDER BY sort asc,create_time DESC ` _, err = o.Raw(sql, classifyId, source).QueryRows(&items) return } // ProfitFutureGoodChartResp 商品利润图 type ProfitFutureGoodChartResp struct { XDataList []XData YDataList []YData ProfitName string `description:"利润的名称"` ProfitNameEn string `description:"利润的英文名称"` }