eta_api/models/data_manage/base_from_oilchem.go

package data_manage

import (
	"eta/eta_api/global"
	"eta/eta_api/utils"
	"github.com/shopspring/decimal"
	"gorm.io/gorm"

	"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 // 修改时间
}

func (baseFromOilchemIndex *BaseFromOilchemIndex) AfterFind(db *gorm.DB) (err error) {
	if utils.NeedDateOrTimeFormat(utils.DbDriverName) {
		if baseFromOilchemIndex.ModifyTime != "" {
			baseFromOilchemIndex.ModifyTime = utils.GormDateStrToDateTimeStr(baseFromOilchemIndex.ModifyTime)
		}
		if baseFromOilchemIndex.CreateTime != "" {
			baseFromOilchemIndex.CreateTime = utils.GormDateStrToDateTimeStr(baseFromOilchemIndex.CreateTime)
		}
	}
	return
}

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

func (baseFromOilchemData *BaseFromOilchemData) AfterFind(db *gorm.DB) (err error) {
	if utils.NeedDateOrTimeFormat(utils.DbDriverName) {
		if baseFromOilchemData.ModifyTime != "" {
			baseFromOilchemData.ModifyTime = utils.GormDateStrToDateTimeStr(baseFromOilchemData.ModifyTime)
		}
		if baseFromOilchemData.CreateTime != "" {
			baseFromOilchemData.CreateTime = utils.GormDateStrToDateTimeStr(baseFromOilchemData.CreateTime)
		}
		if baseFromOilchemData.DataTime != "" {
			baseFromOilchemData.DataTime = utils.GormDateStrToDateStr(baseFromOilchemData.DataTime)
		}
	}
	return
}
func GetOilchemClassifyList() (list []*BaseFromOilchemClassify, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := "SELECT * FROM base_from_oilchem_classify ORDER BY sort ASC"
	err = o.Raw(sql).Find(&list).Error
	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 `gorm:"-"`
	Paging                 *paging.PagingItem     `description:"分页数据" gorm:"-"`
}

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

func GetOilchemIndexById(indexId int) (item *BaseFromOilchemIndex, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).First(&item).Error
	return
}
func GetOilchemIndexByCode(indexCode string) (item *BaseFromOilchemIndexView, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).First(&item).Error
	return
}

func GetOilchemIndexList(condition string, pars []interface{}, startSize, pageSize int) (items []*BaseFromOilchemIndex, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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 ?,? `
	pars = append(pars, startSize, pageSize)
	err = o.Raw(sql, pars...).Find(&items).Error
	return
}

func GetOilchemIndexListCount(condition string, pars []interface{}) (count int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := ` SELECT COUNT(1) AS count FROM base_from_oilchem_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars...).Scan(&count).Error
	return
}

func GetOilchemDataListCount(condition string, pars []interface{}) (count int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := ` SELECT COUNT(1) AS count FROM base_from_oilchem_data WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars...).Scan(&count).Error
	return
}

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

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

// GetOilchemItemList 模糊查询隆众资讯数据库指标列表
func GetOilchemItemList(keyword string) (items []*BaseFromOilchemIndex, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := "SELECT * FROM base_from_oilchem_index WHERE CONCAT(index_name,index_code) LIKE ? "
	err = o.Raw(sql, utils.GetLikeKeyword(keyword)).Find(&items).Error
	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 := global.DbMap[utils.DbNameIndex]
	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...).Find(&items).Error
	return
}

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

// GetOilchemIndexAndEdbInfoByCondition 根据条件获取隆众资讯index和指标库的信息
func GetOilchemIndexAndEdbInfoByCondition(condition string, pars []interface{}) (items []*BaseFromOilchemIndexView, err error) {
	wherePars := make([]interface{}, 0)
	wherePars = append(wherePars, utils.DATA_SOURCE_OILCHEM)
	wherePars = append(wherePars, pars...)

	o := global.DbMap[utils.DbNameIndex]
	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, wherePars...).Find(&items).Error
	return
}

type BaseFromOilchemIndexView struct {
	BaseFromOilchemIndexId int    `orm:"pk" gorm:"primaryKey"`
	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:"值"`
}

func (baseFromOilchemIndexView *BaseFromOilchemIndexView) AfterFind(db *gorm.DB) (err error) {
	if utils.NeedDateOrTimeFormat(utils.DbDriverName) {
		if baseFromOilchemIndexView.DataTime != "" {
			baseFromOilchemIndexView.DataTime = utils.GormDateStrToDateStr(baseFromOilchemIndexView.DataTime)
		}
		if baseFromOilchemIndexView.StartDate != "" {
			baseFromOilchemIndexView.StartDate = utils.GormDateStrToDateStr(baseFromOilchemIndexView.StartDate)
		}
		if baseFromOilchemIndexView.EndDate != "" {
			baseFromOilchemIndexView.EndDate = utils.GormDateStrToDateStr(baseFromOilchemIndexView.EndDate)
		}
		if baseFromOilchemIndexView.ModifyTime != "" {
			baseFromOilchemIndexView.ModifyTime = utils.GormDateStrToDateTimeStr(baseFromOilchemIndexView.ModifyTime)
		}
	}
	return
}

// 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 := global.DbMap[utils.DbNameIndex]
	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`
	pars = append(pars, frequency)
	err = o.Raw(sql, pars...).Find(&items).Error
	return
}

func GetOilchemDataMaxCount(classifyId int) (count int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := `SELECT COALESCE(MAX(t.num), 0) 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).Scan(&count).Error
	return
}

func GetOilchemIndexDataByCode(indexCode string) (items []*BaseFromOilchemData, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := ` SELECT *  FROM base_from_oilchem_data WHERE index_code=? ORDER BY data_time DESC  `
	err = o.Raw(sql, indexCode).Find(&items).Error
	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 := global.DbMap[utils.DbNameIndex]
	err = o.Raw(sql, pars...).Find(&items).Error
	return
}

// GetOilchemIndexViewList 根据分类id获取隆众资讯指标列表
func GetOilchemIndexViewList(condition string, pars []interface{}, startSize, pageSize int) (items []*BaseFromOilchemIndexView, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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 ?,? `
	pars = append(pars, startSize, pageSize)
	err = o.Raw(sql, pars...).Find(&items).Error
	return
}

// GetOilchemIndexViewListCount 根据分类id获取隆众资讯指标列表
func GetOilchemIndexViewListCount(condition string, pars []interface{}) (count int, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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...).Scan(&count).Error
	return
}

// GetOilchemDataViewList 根据指标id获取隆众资讯指标列表
func GetOilchemDataViewList(indexIds []int) (items []*BaseFromOilchemData, err error) {
	o := global.DbMap[utils.DbNameIndex]
	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).Find(&items).Error
	return
}

// GetOilchemDataDataTimeByIndexId 根据指标id获取指标数据的日期列表
func GetOilchemDataDataTimeByIndexId(indexIdList []int) (items []string, err error) {
	if len(indexIdList) == 0 {
		return
	}
	o := global.DbMap[utils.DbNameIndex]
	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).Find(&items).Error
	if err != nil {
		return
	}
	if utils.NeedDateOrTimeFormat(utils.DbDriverName) {
		for i := 0; i < len(items); i++ {
			items[i] = utils.GormDateStrToDateStr(items[i])
		}
	}
	return
}