eta_api/models/data_manage/gl_data.go

package data_manage

import (
	"eta_gn/eta_api/global"
	"eta_gn/eta_api/utils"
	"github.com/rdlucklib/rdluck_tools/paging"
)

type GlClassify struct {
	BreedShortName string `orm:"column(BREED_SHORT_NAME)" description:"分类名称"`
}

func GetGlSurveyClassify() (items []*GlClassify, err error) {
	//sql := ` SELECT CASE WHEN BREED_SHORT_NAME IS NULL THEN '无' ELSE BREED_SHORT_NAME END BREED_SHORT_NAME  FROM mb_index_main_info GROUP BY BREED_SHORT_NAME ORDER BY BREED_SHORT_NAME ASC `
	//o := orm.NewOrmUsingDB("gl")
	//o.Raw(sql).QueryRows(&items)

	sql := ` SELECT CASE WHEN BREED_SHORT_NAME IS NULL THEN '无' ELSE BREED_SHORT_NAME END BREED_SHORT_NAME  FROM mb_index_main_info GROUP BY BREED_SHORT_NAME ORDER BY BREED_SHORT_NAME ASC `
	err = global.DmSQL["gl"].Raw(sql).Find(&items).Error
	return
}

type GlFrequency struct {
	Frequency string `description:"频度:1-日度 2-周度 3-月度 4-季度 5-年度 99-无固定频率"`
}

func GetGlFrequencyByClassifyId(breedShortName string) (items []*GlFrequency, err error) {
	//o := orm.NewOrmUsingDB("gl")
	//if breedShortName == "无" {
	//	sql := ` SELECT FREQUENCY_NAME AS frequency FROM mb_index_main_info WHERE BREED_SHORT_NAME IS NULL GROUP BY FREQUENCY_CODE ORDER BY FREQUENCY_CODE ASC `
	//	_, err = o.Raw(sql).QueryRows(&items)
	//	return
	//} else {
	//	sql := ` SELECT FREQUENCY_NAME AS frequency FROM mb_index_main_info WHERE BREED_SHORT_NAME=? GROUP BY FREQUENCY_CODE ORDER BY FREQUENCY_CODE ASC `
	//	_, err = o.Raw(sql, breedShortName).QueryRows(&items)
	//	return
	//}

	if breedShortName == "无" {
		sql := ` SELECT FREQUENCY_NAME AS frequency FROM mb_index_main_info WHERE BREED_SHORT_NAME IS NULL GROUP BY FREQUENCY_CODE ORDER BY FREQUENCY_CODE ASC `
		err = global.DmSQL["gl"].Raw(sql).Find(&items).Error
		return
	} else {
		//sql := ` SELECT FREQUENCY_NAME AS frequency FROM mb_index_main_info WHERE BREED_SHORT_NAME=? GROUP BY FREQUENCY_CODE ORDER BY FREQUENCY_CODE ASC `
		//_, err = o.Raw(sql, breedShortName).QueryRows(&items)

		sql := ` SELECT FREQUENCY_NAME AS frequency FROM mb_index_main_info WHERE BREED_SHORT_NAME=? GROUP BY FREQUENCY_CODE ORDER BY FREQUENCY_CODE ASC `
		err = global.DmSQL["gl"].Raw(sql, breedShortName).Find(&items).Error
		return
	}
}

type GlIndex struct {
	Id            int    `orm:"column(ID)"`
	IndexCode     string `orm:"column(INDEX_CODE)"`
	IndexName     string `orm:"column(INDEX_NAME)"`
	UnitName      string `orm:"column(UNIT_NAME)"`
	FrequencyName string `orm:"column(FREQUENCY_NAME)"`
	UpdateTime    string `orm:"column(UPDATE_TIME)"`
}

func GetGlIndex(breedShortName, frequency string) (items []*GlIndex, err error) {
	//o := orm.NewOrmUsingDB("gl")
	//if breedShortName == "无" {
	//	sql := ` SELECT * FROM mb_index_main_info WHERE BREED_SHORT_NAME IS NULL AND FREQUENCY_NAME=? ORDER BY INDEX_CODE ASC `
	//	_, err = o.Raw(sql, frequency).QueryRows(&items)
	//	return
	//} else {
	//	sql := ` SELECT * FROM mb_index_main_info WHERE BREED_SHORT_NAME=? AND FREQUENCY_NAME=? ORDER BY INDEX_CODE ASC `
	//	_, err = o.Raw(sql, breedShortName, frequency).QueryRows(&items)
	//	return
	//}

	if breedShortName == "无" {
		sql := ` SELECT * FROM mb_index_main_info WHERE BREED_SHORT_NAME IS NULL AND FREQUENCY_NAME=? ORDER BY INDEX_CODE ASC `
		err = global.DmSQL["gl"].Raw(sql, frequency).Find(&items).Error
		return
	} else {
		sql := ` SELECT * FROM mb_index_main_info WHERE BREED_SHORT_NAME=? AND FREQUENCY_NAME=? ORDER BY INDEX_CODE ASC `
		err = global.DmSQL["gl"].Raw(sql, breedShortName, frequency).Find(&items).Error
		return
	}
}

func GetGlFrequency(productName string) (items []*string, err error) {
	//sql := `SELECT DISTINCT FREQUENCY_NAME FROM mb_index_main_info WHERE BREED_SHORT_NAME=? ORDER BY FIELD(FREQUENCY_NAME,'日度','周度','旬度','月度','季度','半年','年度') `
	//o := orm.NewOrmUsingDB("gl")
	//_, err = o.Raw(sql, productName).QueryRows(&items)

	sql := `SELECT DISTINCT FREQUENCY_NAME FROM mb_index_main_info WHERE BREED_SHORT_NAME=? ORDER BY FIELD(FREQUENCY_NAME,'日度','周度','旬度','月度','季度','半年','年度') `
	err = global.DmSQL["gl"].Raw(sql, productName).Find(&items).Error
	return
}

type GlIndexList struct {
	Id            int                `orm:"column(ID)"`
	IndexCode     string             `orm:"column(INDEX_CODE)"`
	IndexName     string             `orm:"column(INDEX_NAME)"`
	UnitName      string             `orm:"column(UNIT_NAME)"`
	FrequencyName string             `orm:"column(FREQUENCY_NAME)"`
	UpdateTime    string             `orm:"column(UPDATE_TIME)"`
	Paging        *paging.PagingItem `description:"分页数据"`
	DataList      []*GlIndexData
}

type GlIndexData struct {
	InputValue string `orm:"column(DATA_VALUE)" description:"日期"`
	DataTime   string `orm:"column(DATA_DATE)" description:"值"`
}

func GetGlIndexData(indexCode string, startSize, pageSize int) (items []*GlIndexData, err error) {
	//sql := ` SELECT *  FROM mb_index_main_data WHERE INDEX_CODE=? ORDER BY DATA_DATE DESC,PUBLISH_TIME DESC LIMIT ?,?`
	//sql := ` SELECT * FROM (
	//SELECT DISTINCT a.INDEX_CODE,a.DATA_VALUE,a.DATA_DATE FROM mb_index_main_data AS a WHERE INDEX_CODE=? AND IS_DELETE=0
	//ORDER BY DATA_DATE DESC,PUBLISH_TIME DESC
	//)AS t
	//GROUP BY t.DATA_DATE
	//ORDER BY t.DATA_DATE DESC LIMIT ?,? `
	//o := orm.NewOrmUsingDB("gl")
	//_, err = o.Raw(sql, indexCode, startSize, pageSize).QueryRows(&items)

	sql := ` SELECT * FROM (
	SELECT DISTINCT a.INDEX_CODE,a.DATA_VALUE,a.DATA_DATE FROM mb_index_main_data AS a WHERE INDEX_CODE=? AND IS_DELETE=0
	ORDER BY DATA_DATE DESC,PUBLISH_TIME DESC 
	)AS t
	GROUP BY t.DATA_DATE
	ORDER BY t.DATA_DATE DESC LIMIT ?,? `
	err = global.DmSQL["gl"].Raw(sql, indexCode, startSize, pageSize).Find(&items).Error
	return
}

func GetGlIndexByCode(indexCode string) (items *GlIndex, err error) {
	//sql := ` SELECT *  FROM mb_index_main_info WHERE INDEX_CODE=? `
	//o := orm.NewOrmUsingDB("gl")
	//err = o.Raw(sql, indexCode).QueryRow(&items)

	sql := ` SELECT *  FROM mb_index_main_info WHERE INDEX_CODE=? `
	err = global.DmSQL["gl"].Raw(sql, indexCode).First(&items).Error
	return
}

func GetGlIndexDataCount(indexCode string) (count int, err error) {
	//o := orm.NewOrmUsingDB("gl")
	//sql := `SELECT COUNT(1) AS count FROM (
	//		SELECT * FROM (
	//			SELECT DISTINCT a.INDEX_CODE,a.DATA_VALUE,a.DATA_DATE FROM mb_index_main_data AS a WHERE INDEX_CODE=? AND IS_DELETE=0
	//			ORDER BY DATA_DATE DESC,PUBLISH_TIME DESC
	//			)AS t
	//			GROUP BY t.DATA_DATE
	//			ORDER BY t.DATA_DATE DESC
	//		)AS n `
	//err = o.Raw(sql, indexCode).QueryRow(&count)

	sql := `SELECT COUNT(1) AS count FROM (
			SELECT * FROM (
				SELECT DISTINCT a.INDEX_CODE,a.DATA_VALUE,a.DATA_DATE FROM mb_index_main_data AS a WHERE INDEX_CODE=? AND IS_DELETE=0
				ORDER BY DATA_DATE DESC,PUBLISH_TIME DESC
				)AS t
				GROUP BY t.DATA_DATE
				ORDER BY t.DATA_DATE DESC
			)AS n `
	err = global.DmSQL["gl"].Raw(sql, indexCode).Scan(&count).Error
	return
}

type GlSearchIndex struct {
	Id             int    `orm:"column(ID)"`
	IndexCode      string `orm:"column(INDEX_CODE)"`
	IndexName      string `orm:"column(INDEX_NAME)"`
	UnitName       string `orm:"column(UNIT_NAME)"`
	FrequencyName  string `orm:"column(FREQUENCY_NAME)"`
	UpdateTime     string `orm:"column(UPDATE_TIME)"`
	BreedShortName string `orm:"column(BREED_SHORT_NAME)"`
}

// GetGlItemList 模糊查询隆众数据库指标列表
func GetGlItemList(keyword string) (items []*GlSearchIndex, err error) {
	//o := orm.NewOrmUsingDB("gl")
	//sql := "SELECT * FROM mb_index_main_info WHERE CONCAT(INDEX_NAME,INDEX_CODE) LIKE ? "
	//_, err = o.Raw(sql, utils.GetLikeKeyword(keyword)).QueryRows(&items)

	sql := "SELECT * FROM mb_index_main_info WHERE CONCAT(INDEX_NAME,INDEX_CODE) LIKE ? "
	err = global.DmSQL["gl"].Raw(sql, utils.GetLikeKeyword(keyword)).Find(&items).Error
	return

}

func GetGlDataMaxCount(classifyName string) (count int, err error) {
	//o := orm.NewOrmUsingDB("gl")
	//sql := `SELECT MAX(t.num) AS count FROM (
	//			SELECT COUNT(1) AS num  FROM mb_index_main_info AS a
	//			INNER JOIN mb_index_main_data AS b ON a.INDEX_CODE=b.INDEX_CODE
	//			WHERE a.BREED_SHORT_NAME=?
	//			GROUP BY a.INDEX_CODE
	//		)AS t `
	//err = o.Raw(sql, classifyName).QueryRow(&count)

	sql := `SELECT MAX(t.num) AS count FROM (
				SELECT COUNT(1) AS num  FROM mb_index_main_info AS a
				INNER JOIN mb_index_main_data AS b ON a.INDEX_CODE=b.INDEX_CODE
				WHERE a.BREED_SHORT_NAME=?
				GROUP BY a.INDEX_CODE
			)AS t `
	err = global.DmSQL["gl"].Raw(sql, classifyName).Scan(&count).Error
	return
}

func GetGlDataByCode(indexCode string) (items []*GlIndexData, err error) {
	//	o := orm.NewOrmUsingDB("gl")
	//	sql := `SELECT
	//	*
	//FROM
	//	( SELECT * FROM mb_index_main_data WHERE INDEX_CODE = ? GROUP BY UPDATE_TIME DESC, DATA_DATE DESC ) a
	//GROUP BY
	//	a.DATA_DATE DESC `
	//	_, err = o.Raw(sql, indexCode).QueryRows(&items)

	sql := `SELECT
		* 
	FROM
		( SELECT * FROM mb_index_main_data WHERE INDEX_CODE = ? GROUP BY UPDATE_TIME DESC, DATA_DATE DESC ) a 
	GROUP BY
		a.DATA_DATE DESC `
	err = global.DmSQL["gl"].Raw(sql, indexCode).Find(&items).Error
	return
}