eta_api/models/data_manage/base_from_oilchem.go

package data_manage

import (
	"eta/eta_api/utils"
	"github.com/beego/beego/v2/client/orm"
	"github.com/rdlucklib/rdluck_tools/paging"
)

type BaseFromOilchemClassify struct {
	BaseFromOilchemClassifyId int    // 分类ID
	ClassifyName              string // 分类名称
	ParentID                  int    // 上级ID
	Level                     int    // 层级
	Sort                      int    // 排序
	CreateTime                string // 创建时间
	ModifyTime                string // 修改时间
}

type BaseFromOilchemIndex struct {
	BaseFromOilchemIndexId int    // 主键ID
	IndexCode              string // 指标编码
	IndexName              string // 指标名称
	ClassifyId             uint   // 分类ID
	Unit                   string // 单位
	Frequency              string // 频度
	Describe               string // 指标描述
	Sort                   int    // 排序
	CreateTime             string // 创建时间
	ModifyTime             string // 修改时间
}

type BaseFromOilchemData struct {
	BaseFromOilchemDataId  int    // 数据表ID
	BaseFromOilchemIndexId int    // 指标ID
	IndexCode              string // 指标编码
	DataTime               string
	Value                  string
	CreateTime             string
	ModifyTime             string
}

func GetOilchemClassifyList() (list []*BaseFromOilchemClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := "SELECT * FROM base_from_oilchem_classify ORDER BY sort ASC"
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

type BaseFromOilchemIndexList struct {
	BaseFromOilchemIndexId int    // 主键ID
	IndexCode              string // 指标编码
	IndexName              string // 指标名称
	ClassifyId             int   // 分类ID
	Unit                   string // 单位
	Frequency              string // 频度
	Describe               string // 指标描述
	Sort                   int    // 排序
	CreateTime             string // 创建时间
	ModifyTime             string // 修改时间
	EdbExist               int    `description:"edb是否存在"`
	DataList               []*BaseFromOilchemData
	Paging                 *paging.PagingItem `description:"分页数据"`
}

type BaseFromOilchemIndexListResp struct {
	List   []*BaseFromOilchemIndexView
	Paging *paging.PagingItem `description:"分页数据"`
}

func GetOilchemIndexById(indexId int) (item *BaseFromOilchemIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_oilchem_index WHERE 1=1 base_from_oilchem_index_id = ? `
	sql += `ORDER BY base_from_oilchem_index_id ASC `
	err = o.Raw(sql, indexId).QueryRow(&item)
	return
}
func GetOilchemIndexByCode(indexCode string) (item *BaseFromOilchemIndexView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT a.*,CASE WHEN e.edb_info_id IS NULL THEN 0 ELSE 1 END AS edb_exist
	FROM base_from_oilchem_index as a 
LEFT JOIN edb_info AS e ON a.index_code=e.edb_code AND e.source=89 
	WHERE 1=1 and a.index_code = ? `
	sql += `ORDER BY a.base_from_oilchem_index_id ASC `
	err = o.Raw(sql, indexCode).QueryRow(&item)
	return
}

func GetOilchemIndexList(condition string, pars interface{}, startSize, pageSize int) (items []*BaseFromOilchemIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_oilchem_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}

	sql += `group BY index_code ASC order by create_time DESC LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

func GetOilchemIndexListCount(condition string, pars interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM base_from_oilchem_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func GetOilchemDataListCount(condition string, pars interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM base_from_oilchem_data WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func GetOilchemIndexData(condition string, pars interface{}, startSize, pageSize int) (items []*BaseFromOilchemData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_oilchem_data WHERE 1=1  `
	if condition != "" {
		sql += condition
	}

	sql += ` order by data_time DESC LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetOilchemItemList 模糊查询隆众资讯数据库指标列表
func GetOilchemItemList(keyword string) (items []*BaseFromOilchemIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := "SELECT * FROM base_from_oilchem_index WHERE CONCAT(index_name,index_code) LIKE ? "
	_, err = o.Raw(sql, utils.GetLikeKeyword(keyword)).QueryRows(&items)
	return
}

// OilchemDataBatchListReq 隆众资讯指标批量列表
type OilchemDataBatchListReq struct {
	ClassifyId  int    `description:"分类id"`
	KeyWord     string `description:"关键字"`
	SelectedId  []int  `description:"已选指标id, 为true时表示反选"`
	IsSelectAll bool   `description:"是否查询全部, 默认false, true:全选, false:查询已选"`
}

// GetOilchemIndexByCondition 根据条件获取隆众资讯指标列表
func GetOilchemIndexByCondition(condition string, pars []interface{}) (items []*BaseFromOilchemIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_oilchem_index WHERE 1=1 `

	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY sort ASC, base_from_oilchem_index_id ASC`
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// OilchemDataBatchAddCheckReq 隆众资讯指标批量添加校验
type OilchemDataBatchAddCheckReq struct {
	IndexCodes []string `description:"指标编码"`
}

// GetOilchemIndexAndEdbInfoByCondition 根据条件获取隆众资讯index和指标库的信息
func GetOilchemIndexAndEdbInfoByCondition(condition string, pars []interface{}) (items []*BaseFromOilchemIndexView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT b.*, e.edb_info_id, e.unique_code, e.classify_id AS edb_classify_id FROM base_from_oilchem_index AS b LEFT JOIN edb_info AS e ON b.index_code=e.edb_code AND e.source=? WHERE 1=1 `

	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY sort ASC `
	_, err = o.Raw(sql, utils.DATA_SOURCE_OILCHEM, pars).QueryRows(&items)
	return
}

type BaseFromOilchemIndexView struct {
	BaseFromOilchemIndexId int    `orm:"pk"`
	EdbInfoId              int    `description:"指标库id"`
	ClassifyId             int    `description:"指标分类id"`
	IndexCode              string `description:"指标编码"`
	IndexName              string `description:"指标名称"`
	UniqueCode             string `description:"唯一code"`
	Frequency              string `description:"频度"`
	Unit                   string `description:"单位"`
	StartDate              string `description:"开始日期"`
	EndDate                string `description:"结束日期"`
	Sort                   int    `description:"排序"`
	EdbExist               int    `description:"edb是否存在"`
	EdbClassifyId          int    `description:"edb分类id"`
	ModifyTime             string
	DataTime               string `description:"数据时间"`
	Value                  string `description:"值"`
}

// ExportOilchemExcelReq 导出隆众资讯excel指标
type ExportOilchemExcelReq struct {
	KeyWord       string   `description:"关键字, 指标编码或指标ID"`
	IndexCode     []string `description:"指标编码，全选时，表示反选"`
	IsSelectedAll bool     `description:"是否全选：true：全选|false: 无"`
	ClassifyId    int      `description:"指标id"`
}

// GetOilchemIndexByConditionAndFrequency 根据条件获取隆众资讯指标列表
func GetOilchemIndexByConditionAndFrequency(condition, frequency string, pars []interface{}) (items []*BaseFromOilchemIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_oilchem_index WHERE 1=1 `

	if condition != "" {
		sql += condition
	}
	sql += ` AND frequency=?`
	sql += ` ORDER BY sort ASC, base_from_oilchem_index_id ASC`
	_, err = o.Raw(sql, pars, frequency).QueryRows(&items)
	return
}

func GetOilchemDataMaxCount(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT MAX(t.num) AS count FROM (
				SELECT COUNT(1) AS num  FROM base_from_oilchem_index AS a
				INNER JOIN base_from_oilchem_data AS b ON a.index_code=b.index_code
				WHERE a.classify_id=?
				GROUP BY a.base_from_oilchem_index_id
			)AS t `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func GetOilchemIndexDataByCode(indexCode string) (items []*BaseFromOilchemData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *  FROM base_from_oilchem_data WHERE index_code=? ORDER BY data_time DESC  `
	_, err = o.Raw(sql, indexCode).QueryRows(&items)
	return
}

func GetOilchemFrequencyByCondition(condition string, pars []interface{}) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM base_from_oilchem_index WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年','年度') `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, pars...).QueryRows(&items)
	return
}

// GetOilchemIndexViewList 根据分类id获取隆众资讯指标列表
func GetOilchemIndexViewList(condition string, pars []interface{}, startSize, pageSize int) (items []*BaseFromOilchemIndexView, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT b.*, e.edb_info_id,
	CASE WHEN e.edb_info_id IS NULL THEN 0 ELSE 1 END AS edb_exist
	FROM base_from_oilchem_index AS b
	LEFT JOIN edb_info AS e ON b.index_code=e.edb_code AND e.source=89
	WHERE 1=1   `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY b.modify_time ASC LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	return
}

// GetOilchemIndexViewListCount 根据分类id获取隆众资讯指标列表
func GetOilchemIndexViewListCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count
	FROM base_from_oilchem_index AS b
	LEFT JOIN edb_info AS e ON b.index_code=e.edb_code AND e.source=89
	WHERE 1=1   `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY b.modify_time ASC `
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

// GetOilchemDataViewList 根据指标id获取隆众资讯指标列表
func GetOilchemDataViewList(indexIds []int) (items []*BaseFromOilchemData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_oilchem_data WHERE base_from_oilchem_index_id IN (` + utils.GetOrmInReplace(len(indexIds)) + `)ORDER BY data_time desc  `
	_, err = o.Raw(sql, indexIds).QueryRows(&items)
	return
}

// GetOilchemDataDataTimeByIndexId 根据指标id获取指标数据的日期列表
func GetOilchemDataDataTimeByIndexId(indexIdList []int) (items []string, err error) {
	if len(indexIdList) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT DISTINCT data_time FROM base_from_oilchem_data WHERE base_from_oilchem_index_id IN (` + utils.GetOrmInReplace(len(indexIdList)) + `) ORDER BY data_time DESC`
	_, err = o.Raw(sql, indexIdList).QueryRows(&items)
	return
}