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 }