hz_crm_api/models/data_manage/sci_data.go

package data_manage

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

type SciClassify struct {
	TypeName string `orm:"column(type_name)" description:"分类名称"`
	TypeCode string `orm:"column(type_code)" description:"分类名称编码"`
}

func GetSciClassify() (items []*SciClassify, err error) {
	sql := `SELECT CONCAT(type_2,type_3) AS type_name,CONCAT(type_2,'#',type_3) type_code  FROM base_from_sci_index GROUP BY CONCAT(type_2,type_3) ORDER BY CONCAT(type_2,type_3) ASC `
	o := orm.NewOrmUsingDB("data")
	o.Raw(sql).QueryRows(&items)
	return
}

type SciFrequency struct {
	Frequency string `description:"频度"`
}

func GetSciFrequencyByClassifyId(classifyId int) (items []*GlFrequency, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT frequency FROM base_from_sci_index WHERE classify_id = ? `
	sql += ` GROUP BY frequency ORDER BY frequency ASC `
	_, err = o.Raw(sql, classifyId).QueryRows(&items)
	return
}

type SciIndex struct {
	BaseFromSciIndexId int `orm:"column(base_from_sci_index_id);pk"`
	Interface          string
	Name               string
	IndexCode          string
	IndexName          string
	Type1              string `orm:"column(type_1)"`
	Type2              string `orm:"column(type_2)"`
	Type3              string `orm:"column(type_3)"`
	Frequency          string
	Unit               string
	ApiStartTime       string
	ApiUpdateTime      string
	StartTime          string
	FinishTime         string
	CreateTime         string
	ModifyTime         string
}

func GetSciIndex(condition string, pars interface{}) (items []*SciIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM base_from_sci_index WHERE 1=1  `
	if condition != "" {
		sql += condition
	}
	sql += `ORDER BY sort ASC, base_from_sci_index_id asc`
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

type SciExportIndex struct {
	TypeName	string
	IndexCode	string
	IndexName   string
	Type1		string	`orm:"column(type_1)"`
	Type2		string	`orm:"column(type_2)"`
	Type3		string	`orm:"column(type_3)"`
	Frequency	string
	Unit		string
	ModifyTime	string
}

func GetExportSciIndex(typeCodes string) (items []*SciExportIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *,CONCAT(type_2, "#", type_3) AS type_name FROM base_from_sci_index WHERE CONCAT(type_2, "#", type_3) IN (` + typeCodes + `) ORDER BY frequency ASC,index_code ASC`
	_, err = o.Raw(sql).QueryRows(&items)

	return
}

func GetSciFrequency(classifyId int ) (items []*string,err error){
	sql := `SELECT DISTINCT frequency FROM base_from_sci_index WHERE classify_id=? ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年','年度') `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, classifyId).QueryRows(&items)
	return
}

func GetSciFrequencyByCode(code string) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM base_from_sci_index WHERE index_code=? ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年','年度') `
	o := orm.NewOrmUsingDB("data")
	_, err = o.Raw(sql, code).QueryRows(&items)
	return
}

type SciIndexList struct {
	BaseFromSciIndexId int `orm:"column(base_from_sci_index_id);pk"`
	Interface          string
	Name               string
	IndexCode          string
	IndexName          string
	Type1              string `orm:"column(type_1)"`
	Type2              string `orm:"column(type_2)"`
	Type3              string `orm:"column(type_3)"`
	Frequency          string
	Unit               string
	ApiStartTime       string
	ApiUpdateTime      string
	StartTime          string
	FinishTime         string
	ModifyTime         string
	DataList           []*SciIndexData
	Paging             *paging.PagingItem `description:"分页数据"`
}

type SciIndexData struct {
	Value    string `orm:"column(value)" description:"日期"`
	DataTime string `orm:"column(data_time)" description:"值"`
}

func GetSciIndexData(indexCode string, startSize, pageSize int) (items []*SciIndexData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *  FROM base_from_sci_data WHERE index_code=? ORDER BY data_time DESC LIMIT ?,? `
	_, err = o.Raw(sql, indexCode, startSize, pageSize).QueryRows(&items)
	return
}

func GetSciIndexDataCount(indexCode string) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT COUNT(1) AS count  FROM base_from_sci_data WHERE index_code=? `
	err = o.Raw(sql, indexCode).QueryRow(&count)
	return
}

// GetSciItemList 模糊查询Sci数据库指标列表
func GetSciItemList(keyword string) (items []*SciIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := "SELECT * FROM base_from_sci_index WHERE CONCAT(index_name,index_code) LIKE '%" + keyword + "%'"
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

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

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

type ExportSciDataMaxCount struct {
	TypeName	string
	Count		int
}

func GetExportSciDataMaxCount(typeCodes string) (items []*ExportSciDataMaxCount, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT
				MAX(t.num) AS count,
				t.type_name
			FROM
				(
					SELECT
						COUNT(1) AS num,
						CONCAT(a.type_2, "#", a.type_3) AS type_name
					FROM
						base_from_sci_index AS a
					INNER JOIN base_from_sci_data AS b ON a.index_code = b.index_code
					WHERE
						CONCAT(a.type_2, "#", a.type_3) IN (` + typeCodes + `)
					GROUP BY
						a.base_from_sci_index_id
				) AS t
			GROUP BY
				type_name `
	_, err = o.Raw(sql).QueryRows(&items)

	return
}

type ExportSciIndexData struct {
	Value    	string 	`orm:"column(value)" description:"日期"`
	DataTime 	string 	`orm:"column(data_time)" description:"值"`
	IndexCode	string	`orm:"column(index_code)" description:"指标编码"`
}

func GetExportSciIndexDataByCodes(indexCodes string) (items []*ExportSciIndexData, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT index_code,data_time,value FROM base_from_sci_data WHERE index_code IN (` + indexCodes + `) ORDER BY data_time DESC  `
	_, err = o.Raw(sql).QueryRows(&items)
	return
}