eta_api/models/data_manage/base_from_hisugar.go

package data_manage

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

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

type BaseFromHisugarIndex struct {
	BaseFromHisugarIndexId int       // 主键ID
	IndexCode              string    // 指标编码
	IndexName              string    // 指标名称
	ClassifyId             uint      // 分类ID
	Unit                   string    // 单位
	Frequency              string    // 频度
	Describe               string    // 指标描述
	Sort                   int       // 排序
	CreateTime             time.Time // 创建时间
	ModifyTime             time.Time // 修改时间
	StartDate              time.Time `orm:"column(start_date)"`
	EndDate                time.Time `orm:"column(end_date)"`
	LatestValue            float64   `orm:"column(latest_value)" description:"数据值"`
}

type BaseFromHisugarData struct {
	BaseFromHisugarDataId  int    // 数据表ID
	BaseFromHisugarIndexId int    // 指标ID
	IndexCode              string // 指标编码
	DataTime               time.Time
	Value                  string
	CreateTime             time.Time
	ModifyTime             time.Time
}

type BaseFromHisugarDataItem struct {
	BaseFromHisugarDataId  int    // 数据表ID
	BaseFromHisugarIndexId int    // 指标ID
	IndexCode              string // 指标编码
	DataTime               string
	Value                  string
	CreateTime             string
	ModifyTime             string
}

func (m *BaseFromHisugarData) ToItem() (item *BaseFromHisugarDataItem) {
	return &BaseFromHisugarDataItem{
		BaseFromHisugarDataId:  m.BaseFromHisugarDataId,
		BaseFromHisugarIndexId: m.BaseFromHisugarIndexId,
		IndexCode:              m.IndexCode,
		DataTime:               m.DataTime.Format(utils.FormatDate),
		Value:                  m.Value,
		CreateTime:             m.CreateTime.Format(utils.FormatDateTime),
		ModifyTime:             m.ModifyTime.Format(utils.FormatDateTime),
	}
}

func GetHisugarClassifyList() (list []*BaseFromHisugarClassify, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := "SELECT * FROM base_from_hisugar_classify ORDER BY sort ASC"
	_, err = o.Raw(sql).QueryRows(&list)
	return
}
func (m *BaseFromHisugarIndex) ToIndex() (item *BaseFromHisugarIndexList) {
	return &BaseFromHisugarIndexList{
		BaseFromHisugarIndexId: m.BaseFromHisugarIndexId,
		IndexCode:              m.IndexCode,
		IndexName:              m.IndexName,
		ClassifyId:             int(m.ClassifyId),
		Unit:                   m.Unit,
		Frequency:              m.Frequency,
		Describe:               m.Describe,
		Sort:                   m.Sort,
		CreateTime:             m.CreateTime.Format(utils.FormatDateTime),
		ModifyTime:             m.ModifyTime.Format(utils.FormatDateTime),
		//EdbExist:               0,
		//DataList:               nil,
		//Paging:                 nil,
		//EdbInfoId:              0,
	}
}

type BaseFromHisugarIndexList struct {
	BaseFromHisugarIndexId 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               []*BaseFromHisugarDataItem
	Paging                 *paging.PagingItem `description:"分页数据"`
	EdbInfoId              int                `description:"指标库主键id"`
}

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

func GetHisugarIndexById(indexId int) (item *BaseFromHisugarIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_hisugar_index WHERE 1=1 base_from_hisugar_index_id = ? `
	sql += `ORDER BY base_from_hisugar_index_id ASC `
	err = o.Raw(sql, indexId).QueryRow(&item)
	return
}
func GetHisugarIndexByCode(indexCode string) (item *BaseFromHisugarIndexView, 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,e.edb_info_id
	FROM base_from_hisugar_index as a 
LEFT JOIN edb_info AS e ON a.index_code=e.edb_code AND e.source=93 
	WHERE 1=1 and a.index_code = ? `
	sql += `ORDER BY a.base_from_hisugar_index_id ASC `
	err = o.Raw(sql, indexCode).QueryRow(&item)
	return
}

func GetHisugarIndexList(condition string, pars interface{}, startSize, pageSize int) (items []*BaseFromHisugarIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_hisugar_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 GetHisugarIndexListCount(condition string, pars interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM base_from_hisugar_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

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

func GetHisugarIndexData(condition string, pars interface{}, startSize, pageSize int) (items []*BaseFromHisugarDataItem, err error) {
	var ormList []*BaseFromHisugarData
	items = make([]*BaseFromHisugarDataItem, 0)

	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_hisugar_data WHERE 1=1  `
	if condition != "" {
		sql += condition
	}

	sql += ` order by data_time DESC LIMIT ?,? `
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&ormList)
	if err != nil {
		return
	}
	for _, ormItem := range ormList {
		items = append(items, ormItem.ToItem())
	}

	return
}

// GetHisugarItemList 模糊查询泛糖科技数据库指标列表
func GetHisugarItemList(keyword string) (items []*BaseFromHisugarIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := "SELECT * FROM base_from_hisugar_index WHERE CONCAT(index_name,index_code) LIKE ? "
	_, err = o.Raw(sql, utils.GetLikeKeyword(keyword)).QueryRows(&items)
	return
}

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

// GetHisugarIndexByCondition 根据条件获取泛糖科技指标列表
func GetHisugarIndexByCondition(condition string, pars []interface{}) (items []*BaseFromHisugarIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_hisugar_index WHERE 1=1 `

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

// HisugarDataBatchAddCheckReq 泛糖科技指标批量添加校验
type HisugarDataBatchAddCheckReq struct {
	IndexCodes []string `description:"指标编码"`
}

// GetHisugarIndexAndEdbInfoByCondition 根据条件获取泛糖科技index和指标库的信息
func GetHisugarIndexAndEdbInfoByCondition(condition string, pars []interface{}) (items []*BaseFromHisugarIndexView, 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_hisugar_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_HISUGAR, pars).QueryRows(&items)
	return
}

type BaseFromHisugarIndexView struct {
	BaseFromHisugarIndexId 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:"值"`
}

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

// GetHisugarIndexByConditionAndFrequency 根据条件获取泛糖科技指标列表
func GetHisugarIndexByConditionAndFrequency(condition, frequency string, pars []interface{}) (items []*BaseFromHisugarIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_hisugar_index WHERE 1=1 `

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

func GetHisugarDataMaxCount(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_hisugar_index AS a
				INNER JOIN base_from_hisugar_data AS b ON a.index_code=b.index_code
				WHERE a.classify_id=?
				GROUP BY a.base_from_hisugar_index_id
			)AS t `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

func GetHisugarIndexDataByCode(indexCode string) (items []*BaseFromHisugarDataItem, err error) {
	var ormList []*BaseFromHisugarData
	items = make([]*BaseFromHisugarDataItem, 0)

	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *  FROM base_from_hisugar_data WHERE index_code=? ORDER BY data_time DESC  `
	_, err = o.Raw(sql, indexCode).QueryRows(&ormList)
	if err != nil {
		return
	}
	for _, ormItem := range ormList {
		items = append(items, ormItem.ToItem())
	}

	return
}

func GetHisugarFrequencyByCondition(condition string, pars []interface{}) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM base_from_hisugar_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
}

// GetHisugarIndexViewList 根据分类id获取泛糖科技指标列表
func GetHisugarIndexViewList(condition string, pars []interface{}) (items []*BaseFromHisugarIndexView, 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_hisugar_index AS b
	LEFT JOIN edb_info AS e ON b.index_code=e.edb_code AND e.source=93
	WHERE 1=1   `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY b.modify_time ASC `
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

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

// GetHisugarDataViewList 根据指标id获取泛糖科技指标列表
func GetHisugarDataViewList(indexIds []int) (items []*BaseFromHisugarDataItem, err error) {
	num := len(indexIds)
	if num <= 0 {
		return
	}
	var ormList []*BaseFromHisugarData
	items = make([]*BaseFromHisugarDataItem, 0)

	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_hisugar_data WHERE base_from_hisugar_index_id IN (` + utils.GetOrmInReplace(num) + `) ORDER BY data_time desc  `
	_, err = o.Raw(sql, indexIds).QueryRows(&ormList)
	if err != nil {
		return
	}
	for _, ormItem := range ormList {
		items = append(items, ormItem.ToItem())
	}

	return
}

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

type BaseFromHisugarClassifyItem struct {
	BaseFromHisugarClassifyId int                            `orm:"column(base_from_hisugar_classify_id);pk"`
	ClassifyName              string                         `description:"分类名称"`
	ParentId                  int                            `description:"父级id"`
	Level                     int                            `description:"层级"`
	Sort                      int                            `description:"排序字段"`
	UniqueCode                string                         `description:"唯一code"`
	ModifyTime                time.Time                      `description:"修改时间"`
	CreateTime                time.Time                      `description:"创建时间"`
	ClassifyNameEn            string                         `description:"英文分类名称"`
	Children                  []*BaseFromHisugarClassifyItem `description:"子分类"`
}

// 获取所有分类
func GetHisugarClassifyAll() (items []*BaseFromHisugarClassifyItem, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_hisugar_classify ORDER BY sort ASC, base_from_hisugar_classify_id ASC`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

func GetHisugarIndex(condition string, pars interface{}) (items []*BaseFromHisugarIndexList, err error) {
	var ormList []*BaseFromHisugarIndex
	items = make([]*BaseFromHisugarIndexList, 0)

	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_hisugar_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY sort ASC, base_from_hisugar_index_id asc`
	_, err = o.Raw(sql, pars).QueryRows(&ormList)
	if err != nil {
		return
	}
	for _, ormItem := range ormList {
		items = append(items, ormItem.ToIndex())
	}

	return
}

type HisugarIndexDataCountGroup struct {
	IndexCode string
	Count     int
}

func GetHisugarIndexDataCountGroup(indexCodes []string) (items []*HisugarIndexDataCountGroup, err error) {
	if len(indexCodes) <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count, index_code FROM base_from_hisugar_data WHERE index_code IN (` + utils.GetOrmInReplace(len(indexCodes)) + `) GROUP BY index_code`
	_, err = o.Raw(sql, indexCodes).QueryRows(&items)
	return
}

func GetHisugarIndexDataV2(indexCode string, startSize, pageSize int) (items []*BaseFromHisugarDataItem, err error) {
	var ormList []*BaseFromHisugarData
	items = make([]*BaseFromHisugarDataItem, 0)

	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *  FROM base_from_hisugar_data WHERE index_code=? ORDER BY data_time DESC LIMIT ?,? `
	_, err = o.Raw(sql, indexCode, startSize, pageSize).QueryRows(&ormList)
	if err != nil {
		return
	}
	for _, ormItem := range ormList {
		items = append(items, ormItem.ToItem())
	}

	return
}

// GetHisugarIndexInfoCount 查询指标信息总数
func GetHisugarIndexInfoCount(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count FROM base_from_hisugar_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

type BaseFromHisugarIndexPage struct {
	List   []*BaseFromHisugarIndex `description:"指标列表"`
	Paging *paging.PagingItem      `description:"分页数据"`
}

// GetHisugarIndexInfoPage 分页查询指标信息
func GetHisugarIndexInfoPage(condition string, pars []interface{}, size int, pageSize int) (items []*BaseFromHisugarIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_hisugar_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	sql += ` ORDER BY base_from_hisugar_index_id asc LIMIT ?,?`
	_, err = o.Raw(sql, pars, size, pageSize).QueryRows(&items)
	return

}

// 获取所有分类
func GetHisugarClassifyById(classifyId int) (ClassifyIds string, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT GROUP_CONCAT(base_from_hisugar_classify_id) AS classify_ids FROM base_from_hisugar_classify WHERE base_from_hisugar_classify_id=? OR parent_id=?`
	err = o.Raw(sql, classifyId, classifyId).QueryRow(&ClassifyIds)
	return
}