eta_mobile/models/data_manage/chart_info.go

package data_manage

import (
	"errors"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
	"hongze/hongze_ETA_mobile_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(source int) (items []*ChartClassifyItems, err error) {
	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 = ?  ORDER BY sort asc,create_time ASC `
	_, err = o.Raw(sql, source).QueryRows(&items)
	return
}

// GetChartInfoByAdminId 获取所有我创建的图表，用于分类展示
func GetChartInfoByAdminId(source, adminId int) (items []*ChartClassifyItems, err error) {
	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 = ? AND sys_user_id = ? ORDER BY sort asc,create_time ASC `
	_, err = o.Raw(sql, source, 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:"数据最新值"`
	UniqueCode          string  `description:"指标唯一编码"`
	MinValue            float64 `json:"-" description:"最小值"`
	MaxValue            float64 `json:"-" description:"最大值"`
	DataList            interface{}
	IsNullData          bool `json:"-" description:"是否空数据"`
	MappingSource       int  `description:"1：ETA图库；2：商品价格曲线"`
	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:"数据日期"`
	Color          string    `description:"数据颜色"`
	Name           string    `description:"别名"`
	NameEn         string    `description:"英文别名"`
	Value          []float64 `description:"每个指标的值"`
	NoDataEdbList  []int     `description:"没有数据的指标列表"`
	XEdbInfoIdList []int     `description:"对应X轴的指标id列表"`
}


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字符串"`
}

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, source int, noPermissionChartIdList []int) (total int64, searchList []*ChartInfo, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM chart_info WHERE 1=1 AND source = ? `

	var pars []interface{}
	pars = append(pars, source)

	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, updateSort string) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` update chart_info set sort = ` + updateSort + ` WHERE chart_classify_id=? AND `
	if prevChartInfoId > 0 {
		sql += ` (sort > ? or (chart_info_id > ` + fmt.Sprint(prevChartInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `))`
	}
	_, err = o.Raw(sql, classifyId, 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数据"`
}

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:"频度"`
	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：商品价格"`
}


// 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
}