eta_api/models/data_source/longzhong.go

package data_source

import (
	"eta_gn/eta_api/global"
	"eta_gn/eta_api/models/data_manage"
	"eta_gn/eta_api/utils"
	"time"
)

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

type Longzhonginfo struct {
	LongzhonginfoId int    `gorm:"primaryKey;column:longzhonginfo_id;type:int(11);not null"`
	TradeCode       string `gorm:"unique;column:trade_code;type:varchar(32);default:''"` // 指标唯一编码
	SecName         string `gorm:"unique;column:sec_name;type:varchar(180);default:''"`  // 指标名称
	Unit            string `gorm:"column:unit;type:varchar(100);default:''"`             // 单位
	Frequency       string `gorm:"column:frequency;type:varchar(20);default:''"`         // 频度
	//ClassifyId      int       `gorm:"index:idx_classify_id;column:classify_id;type:int(11);default:0"` // 分类id
	//ClassifyName    string    `gorm:"column:classify_name;type:varchar(20);default:''"`
	//CreateTime      time.Time `gorm:"column:create_time;type:datetime;default:CURRENT_TIMESTAMP"`
	//Remark          string    `gorm:"column:remark;type:varchar(100);default:''"`
	LastModifyDate string `gorm:"column:last_modify_date;type:date"` // 最后更新时间
	//Unitid          int       `gorm:"index:idx_unitid;column:unitid;type:bigint(20)"` // 隆众id
	//TempID          int       `gorm:"column:temp_id;type:bigint(20)"`                 // 隆众数据单元组id
	//TempName        string    `gorm:"column:temp_name;type:varchar(255)"`             // 隆众数据单元组id
	//IsNormal        string    `gorm:"column:is_normal;type:varchar(10)"`              // 是否正常更新，0：停更，1：更新
	//ModifyTime      time.Time `gorm:"column:modify_time;type:datetime;default:CURRENT_TIMESTAMP"`
}

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)
	err = global.DmSQL["edb"].Raw(sql, classifyId).Find(&items).Error
	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)
	err = global.DmSQL["edb"].Raw(sql, classifyId).Scan(&count).Error
	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
//}

type Longzhongdata struct {
	LongzhongdataId int       `gorm:"primaryKey;column:longzhongdata_id;type:int(11);not null"`
	LongzhonginfoId int       `gorm:"uniqueIndex:idx_longzhonginfo_id;column:longzhonginfo_id;type:int(11);default:0"`
	TradeCode       string    `gorm:"uniqueIndex:idx_trade_code_dt;column:trade_code;type:varchar(32);default:''"`
	Dt              string    `gorm:"uniqueIndex:idx_trade_code_dt;uniqueIndex:idx_longzhonginfo_id;column:dt;type:date"`
	Close           float64   `gorm:"column:close;type:double"`
	CreateTime      time.Time `gorm:"column:create_time;type:datetime;default:CURRENT_TIMESTAMP"`
	ModifyTime      time.Time `gorm:"column:modify_time;type:datetime;default:CURRENT_TIMESTAMP"`
	UnitDesc        string    `gorm:"column:unit_desc;type:varchar(255);default:''"` // 描述
	UpdTime         string    `gorm:"column:upd_time;type:varchar(20);default:''"`   // 更新时间
	//AddTime         string    `gorm:"column:add_time;type:varchar(30);default:''"`
	DisplayTime string `gorm:"column:display_time;type:varchar(30);default:''"`
}

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)
	err = global.DmSQL["edb"].Raw(sql, lzInfoId).Find(&items).Error
	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)
	err = global.DmSQL["edb"].Raw(sql, classifyName).Scan(&count).Error
	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)
	err = global.DmSQL["edb"].Raw(sql, classifyName, frequency).Scan(&count).Error
	return
}

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

func GetExportLzSurveyDataMaxCount(breedIds []string) (items []ExportLzSurveyDataMaxCount, err error) {
	if len(breedIds) == 0 {
		return
	}
	//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 (` + utils.GetOrmInReplace(len(breedIds)) + `)
					GROUP BY
						a.survey_product_id
				) AS t
			GROUP BY
				t.breed_id `
	//_, err = o.Raw(sql, breedIds).QueryRows(&items)
	err = global.DmSQL["edb"].Raw(sql, breedIds).Find(&items).Error
	return
}

func GetExportLzSurveyDataByBreedIds(breedIds []string) (items []*LongzhongSurveyData, err error) {
	if len(breedIds) == 0 {
		return
	}
	//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 (` + utils.GetOrmInReplace(len(breedIds)) + `) ORDER BY breed_id ASC, survey_product_id ASC, data_time DESC `
	//_, err = o.Raw(sql, breedIds).QueryRows(&items)
	err = global.DmSQL["edb"].Raw(sql, breedIds).Find(&items).Error
	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
//}

type LongzhongSurveyData struct {
	SurveyDataId         int     `gorm:"primaryKey;column:survey_data_id;type:int(11);not null"`
	SurveyProductId      int     `gorm:"column:survey_product_id;type:int(11)"`                    // 本地指标id
	ProjectQuotaId       int64   `gorm:"column:project_quota_id;type:int(11)"`                     // 调研指标id
	BreedId              string  `gorm:"column:breed_id;type:varchar(20);default:''"`              // 品种id
	BreedName            string  `gorm:"column:breed_name;type:varchar(100);default:''"`           // 品种名称
	QuotaId              string  `gorm:"column:quota_id;type:varchar(20);default:''"`              // 指标id
	QuotaName            string  `gorm:"column:quota_name;type:varchar(255);default:''"`           // 指标名称
	UnitId               string  `gorm:"column:unit_id;type:varchar(20);default:''"`               // 单位id
	UnitName             string  `gorm:"column:unit_name;type:varchar(100);default:''"`            // 单位(例如：吨)
	SampleType           int64   `gorm:"column:sample_type;type:int(11);default:0"`                // 样本类型 0-空 1-企业 2-港口 3-运距 4-区域/国家 99-不确定
	SampleId             string  `gorm:"column:sample_id;type:varchar(50);default:''"`             // 样本id
	SampleName           string  `gorm:"column:sample_name;type:varchar(100);default:''"`          // 样本名称
	DeviceId             string  `gorm:"column:device_id;type:varchar(50);default:''"`             // 设备id
	Device               string  `gorm:"column:device;type:varchar(100);default:''"`               // 设备
	ProductCraftId       string  `gorm:"column:product_craft_id;type:varchar(50);default:''"`      // 工艺id
	ProductCraft         string  `gorm:"column:product_craft;type:varchar(100);default:''"`        // 工艺
	ProductLine          string  `gorm:"column:product_line;type:varchar(100);default:''"`         // 产线
	InputMode            int64   `gorm:"column:input_mode;type:int(11);default:0"`                 // 录入方式 1-采集 2-计算 99-行业求和
	Frequency            int64   `gorm:"column:frequency;type:int(11);default:0"`                  // 频率1-日度 2-周度 3-月度 4-季度 5-年度 99-无固定频率
	InputValue           float64 `gorm:"column:input_value;type:double"`                           // 调研结果
	TaskShouldFinishTime int64   `gorm:"column:task_should_finish_time;type:bigint(11);default:0"` // 调研数据完成时间
	CustomId             int64   `gorm:"column:custom_id;type:int(11);default:0"`                  // 扩展属性id
	CustomType           int64   `gorm:"column:custom_type;type:int(11);default:0"`                // 扩展属性类型0未设置，1规格，2标准，3用户，4包装，5生产工艺6装置，8行业，9企业分类，10企业隶属关系
	Custom               string  `gorm:"column:custom;type:varchar(200);default:''"`               // 扩展属性内容
	QuotaSampleId        int64   `gorm:"column:quota_sample_id;type:int(11)"`                      // 调研指标配置样本id
	TaskActualFinishTime int64   `gorm:"column:task_actual_finish_time;type:bigint(11)"`           // 调研数据实际完成时间
	AreaName             string  `gorm:"column:area_name;type:varchar(100);default:''"`            // 区域名称
	ProvinceName         string  `gorm:"column:province_name;type:varchar(100);default:''"`        // 省份名称
	ResearchStartData    int64   `gorm:"column:research_start_data;type:int(11)"`                  // 调研开始日期
	ResearchStopData     int64   `gorm:"column:research_stop_data;type:int(11)"`                   // 调研截止日期
	DataTime             string  `gorm:"column:data_time;type:date"`                               // 调研数据实际完成日期
}

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)
	err = global.DmSQL["edb"].Raw(sql, lzInfoId).Find(&items).Error
	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 ? "
	//_, err = o.Raw(sql, utils.GetLikeKeyword(keyword)).QueryRows(&items)
	err = global.DmSQL["edb"].Raw(sql, utils.GetLikeKeyword(keyword)).Find(&items).Error
	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)
	err = global.DmSQL["edb"].Raw(sql, lzCode).Scan(&productId).Error
	return
}

// GetLzSurveyDataMaxCountByProductId 根据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)
	err = global.DmSQL["edb"].Raw(sql, surveyProductId).Scan(&productId).Error
	return
}