hz_crm_api/models/data_source/longzhong.go

package data_source

import (
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/models/data_manage"
	"time"
)

type Longzhonginfo struct {
	LongzhonginfoId int
	TradeCode       string
	SecName         string
	Unit            string
	Frequency       string
	LastModifyDate  string
}

func GetLongzhonginfoByClassifyId(classifyId int) (items []*Longzhonginfo, err error) {
	sql := `SELECT * FROM longzhonginfo WHERE classify_id=? ORDER BY longzhonginfo_id ASC `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, classifyId).QueryRows(&items)
	return
}

func GetLongzhongDataMaxCount(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := `SELECT MAX(t.num) AS count FROM (
				SELECT COUNT(1) AS num  FROM longzhonginfo AS a
				INNER JOIN longzhongdata AS b ON a.longzhonginfo_id=b.longzhonginfo_id
				WHERE a.classify_id=?
				GROUP BY a.longzhonginfo_id
			)AS t `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

type Longzhongdata struct {
	LongzhongdataId int `orm:"column(longzhongdata_id);pk"`
	LongzhonginfoId int
	TradeCode       string
	Dt              string
	Close           float64
	CreateTime      time.Time
	ModifyTime      time.Time
	UnitDesc        string
	UpdTime         string
	DisplayTime     string
}

func GetLongzhongDataById(lzInfoId int) (items []*Longzhongdata, err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := `SELECT * FROM longzhongdata WHERE longzhonginfo_id=? ORDER BY dt DESC `
	_, err = o.Raw(sql, lzInfoId).QueryRows(&items)
	return
}

func GetLongzhongSurveyDataMaxCount(classifyName string) (count int, err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := `SELECT MAX(t.num) AS count FROM (
				SELECT COUNT(1) AS num  FROM longzhong_survey_product AS a
				INNER JOIN longzhong_survey_data AS b ON a.survey_product_id=b.survey_product_id
				WHERE a.breed_name=?
				GROUP BY a.survey_product_id
			)AS t `
	err = o.Raw(sql, classifyName).QueryRow(&count)
	return
}

func GetLongzhongSurveyDataMaxCountByFrequency(classifyName string, frequency int) (count int, err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := `SELECT MAX(t.num) AS count FROM (
				SELECT COUNT(1) AS num  FROM longzhong_survey_product AS a
				INNER JOIN longzhong_survey_data AS b ON a.survey_product_id=b.survey_product_id
				WHERE a.breed_name=? AND a.frequency=?
				GROUP BY a.survey_product_id
			)AS t `
	err = o.Raw(sql, classifyName, frequency).QueryRow(&count)
	return
}

type ExportLzSurveyDataMaxCount struct {
	Count   int `description:"最大数据量"`
	BreedId int `description:"品种ID"`
}

func GetExportLzSurveyDataMaxCount(breedIds string) (items []ExportLzSurveyDataMaxCount, err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := ` SELECT
				MAX(t.num) AS count,
				t.breed_id
			FROM
				(
					SELECT
						COUNT(1) AS num,
						a.breed_id AS breed_id
					FROM
						longzhong_survey_product AS a
					INNER JOIN longzhong_survey_data AS b ON a.survey_product_id = b.survey_product_id
					WHERE
						a.breed_id IN (` + breedIds + `)
					GROUP BY
						a.survey_product_id
				) AS t
			GROUP BY
				t.breed_id `
	_, err = o.Raw(sql).QueryRows(&items)

	return
}

func GetExportLzSurveyDataByBreedIds(breedIds string) (items []*LongzhongSurveyData, err error) {
	o := orm.NewOrmUsingDB("edb")
	fields := ` survey_product_id, breed_id, input_value, data_time `
	sql := `SELECT ` + fields + ` FROM longzhong_survey_data WHERE breed_id IN (` + breedIds + `) ORDER BY breed_id ASC, survey_product_id ASC, data_time DESC `
	_, err = o.Raw(sql).QueryRows(&items)

	return
}

type LongzhongSurveyData struct {
	SurveyDataId         int `orm:"column(survey_data_id);pk"`
	SurveyProductId      int
	ProjectQuotaId       int64
	BreedId              string
	BreedName            string
	QuotaId              string
	QuotaName            string
	UnitId               string
	UnitName             string
	SampleType           int64
	SampleId             string
	SampleName           string
	DeviceId             string
	Device               string
	ProductCraftId       string
	ProductCraft         string
	ProductLine          string
	InputMode            int64
	Frequency            int64
	InputValue           float64
	TaskShouldFinishTime int64
	CustomId             string
	CustomType           int64
	Custom               string
	QuotaSampleId        int64
	TaskActualFinishTime int64
	AreaName             string
	ProvinceName         string
	ResearchStartData    int64
	ResearchStopData     int64
	DataTime             string
}

func GetLongzhongSurveyDataById(lzInfoId int) (items []*LongzhongSurveyData, err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := `SELECT * FROM longzhong_survey_data WHERE survey_product_id=? ORDER BY data_time DESC `
	_, err = o.Raw(sql, lzInfoId).QueryRows(&items)
	return
}

// GetLzItemList 模糊查询隆众数据库指标列表
func GetLzItemList(keyword string) (items []*data_manage.LongzhongSurveyProduct, err error) {
	o := orm.NewOrmUsingDB("edb")

	sql := "SELECT * FROM longzhong_survey_product WHERE CONCAT(sample_name,breed_name,custom,quota_name,lz_code) LIKE '%" + keyword + "%'"
	_, err = o.Raw(sql).QueryRows(&items)
	return

}

type lzSurveyData struct {
	DataTime   string `orm:"column(data_time)" description:"日期"`
	InputValue string `orm:"column(input_value)" description:"值"`
}

// GetLzItemListByCode 根据code查询隆众数据列表
func GetLzItemListByCode(lzCode string) (items []*lzSurveyData, err error) {
	o := orm.NewOrmUsingDB("edb")

	sql := "SELECT * FROM longzhong_survey_data WHERE survey_product_id=? GROUP BY data_time DESC"
	_, err = o.Raw(sql, lzCode).QueryRows(&items)
	return
}

// GetLzProductIdByCode 根据code查询隆众数据列表
func GetLzProductIdByCode(lzCode string) (productId int, err error) {
	o := orm.NewOrmUsingDB("edb")

	sql := "SELECT survey_product_id FROM longzhong_survey_product WHERE lz_code=?"
	err = o.Raw(sql, lzCode).QueryRow(&productId)
	return
}

// GetLzProductIdByCode 根据code查询隆众数据列表
func GetLzSurveyDataMaxCountByProductId(surveyProductId int) (productId int, err error) {
	o := orm.NewOrmUsingDB("edb")

	sql := "SELECT COUNT(1) FROM longzhong_survey_data WHERE survey_product_id=?"
	err = o.Raw(sql, surveyProductId).QueryRow(&productId)
	return
}