eta_api/models/target.go

package models

import (
	sql2 "database/sql"
	"eta/eta_api/global"
	"eta/eta_api/utils"
	"fmt"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/rdlucklib/rdluck_tools/paging"
)

type DataList struct {
	TradeCode    string  `orm:"column(TRADE_CODE)" description:"指标编码"`
	SecName      string  `orm:"column(SEC_NAME)" description:"指标名称"`
	Unit         string  `orm:"column(UNIT)" description:"单位"`
	Remark       string  `orm:"column(REMARK)" description:"备注"`
	Frequency    string  `description:"频度"`
	ClassifyId   int     `description:"分类id"`
	ClassifyName string  `description:"分类名称"`
	Dt           string  `orm:"column(DT)" description:"录入日期"`
	Close        float64 `orm:"column(CLOSE)" description:"录入值"`
	ModifyTime   string  `description:"修改时间"`
}

type DataListResp struct {
	List   []*DataList
	Paging *paging.PagingItem `description:"分页数据"`
}

func GetDataList(condition string, pars []interface{}, startSize, pageSize int) (items []*DataList, err error) {
	sql := `select a.TRADE_CODE,a.SEC_NAME,a.UNIT,a.frequency,a.classify_id,b.classify_name,c.DT,c.CLOSE,c.modify_time FROM edbdata AS c
                inner join edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
                left join edbdata_classify AS b ON a.classify_id=b.classify_id
                where a.classify_id>0`
	if condition != "" {
		sql += condition
	}
	sql += ` order by c.DT desc limit ?,? `
	o := global.DbMap[utils.DbNameManualIndex]
	pars = append(pars, startSize, pageSize)
	err = o.Raw(sql, pars...).Find(&items).Error
	return
}

func GetDataListCount(condition string, pars []interface{}) (count int, err error) {
	sql := ` select count(1) as count FROM edbdata AS c
                    inner join edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE    
                    left join edbdata_classify AS b ON a.classify_id=b.classify_id
                    where a.classify_id>0 `
	if condition != "" {
		sql += condition
	}
	o := global.DbMap[utils.DbNameManualIndex]
	var countNull sql2.NullInt64
	err = o.Raw(sql, pars...).Scan(&countNull).Error
	if err != nil {
		return
	}
	if countNull.Valid {
		count = int(countNull.Int64)
	}
	return
}

type DataAddReq struct {
	TradeCode  string `description:"指标唯一编码"`
	CreateDate string `description:"创建日期"`
	Close      string `description:"录入值"`
}

type Edbdata struct {
	TradeCode  string    `orm:"column(TRADE_CODE);pk" description:"指标编码"`
	Dt         string    `orm:"column(DT)" description:"日期"`
	Close      string    `orm:"column(CLOSE)" description:"值"`
	ModifyTime time.Time `orm:"column(modify_time)" description:"修改时间"`
}

type EdbDataNextDateTime struct {
	TradeCode  string    `orm:"column(TRADE_CODE);pk" description:"指标编码"`
	Dt         string    `orm:"column(DT)" description:"日期"`
	Close      string    `orm:"column(CLOSE)" description:"值"`
	ModifyTime time.Time `orm:"column(modify_time)" description:"修改时间"`

	NextDateTime string `description:"下期日期"`
}

func GetDataInfo(tradeCode, creteDate string) (item *Edbdata, err error) {
	sql := " SELECT * FROM edbdata WHERE TRADE_CODE=? AND DT=? "
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, tradeCode, creteDate).First(&item).Error
	return
}

func AddEdbdata(item *Edbdata) (lastId int64, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Create(item).Error
	return
}

type DataEditReq struct {
	TradeCode     string      `description:"指标唯一编码"`
	CreateDate    string      `description:"创建日期"`
	Close         interface{} `description:"录入值"`
	OldCreateDate string      `description:"旧的录入日期"`
}

// BatchDataEditReq 批量修改指标
type BatchDataEditReq struct {
	OldCreateDate string        `description:"旧的录入日期"`
	CreateDate    string        `description:"新的录入日期"`
	List          []DataEditReq `description:"需要修改的数据"`
}

// EditEdbdata 编辑数据
func EditEdbdata(item *Edbdata) (err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` UPDATE edbdata SET CLOSE = ?,modify_time=NOW() WHERE TRADE_CODE = ? AND DT = ? `
	err = o.Exec(sql, item.Close, item.TradeCode, item.Dt).Error
	return
}

type EdbdataDeleteRecord struct {
	Id         int       `gorm:"column:id;primaryKey;autoIncrement"`
	TradeCode  string    `orm:"column(TRADE_CODE)" description:"指标编码"`
	Dt         string    `orm:"column(DT)" description:"日期"`
	Close      string    `orm:"column(CLOSE)" description:"值"`
	ModifyTime time.Time `orm:"column(modify_time)" description:"修改时间"`
	CreateTime time.Time
	SysUserId  int
}

func AddEdbdataDeleteRecord(item *EdbdataDeleteRecord) (lastId int64, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Create(item).Error
	if err != nil {
		return
	}
	lastId = int64(item.Id)
	return
}

// DeleteEdbData 根据指标code和日期删除数据
func DeleteEdbData(tradeCode, dt string) (err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` DELETE FROM edbdata WHERE TRADE_CODE = ? AND DT = ? `
	err = o.Exec(sql, tradeCode, dt).Error
	return
}

// DeleteAllEdbData 根据指标code删除数据
func DeleteAllEdbData(tradeCode string) (err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` DELETE FROM edbdata WHERE TRADE_CODE = ? `
	err = o.Exec(sql, tradeCode).Error
	return
}

type Edbinfo struct {
	TradeCode    string  `orm:"column(TRADE_CODE);pk" description:"指标code"`
	SecName      string  `orm:"column(SEC_NAME);" description:"指标名称"`
	Unit         string  `orm:"column(UNIT);" description:"单位"`
	Remark       string  `orm:"column(REMARK);" description:"备注"`
	Frequency    string  `orm:"column(frequency)" description:"频度"`
	ClassifyId   int     `orm:"column(classify_id)" description:"分类id"`
	ClassifyName string  `orm:"-" description:"分类名称"`
	CreateDate   string  `orm:"column(create_date)" description:"创建时间"`
	UserId       int     `orm:"column(user_id)" description:"录入用户id"`
	UserName     string  `orm:"column(user_name)" description:"录入用户名称"`
	NoticeTime   string  `orm:"column(notice_time)" description:"通知时间"`
	Mobile       string  `orm:"column(mobile)" description:"录入者手机号"`
	Sort         int     `orm:"column(sort)" description:"排序"`
	ModifyTime   string  `description:"最近一次更新时间"`
	IsJoinEdb    int8    `description:"指标库是否已添加：0-否；1-是"`
	StartDate    string  `description:"数据开始日期"`
	EndDate      string  `description:"数据结束日期"`
	LatestValue  float64 `description:"指标最新值"`
}

//func DeleteEdbinfoByTraceCodeList(tradeCodeList []string) (err error) {
//	if len(tradeCodeList) == 0 {
//		return
//	}
//	o := global.DbMap[utils.DbNameManualIndex]
//	err = o.DoTx(func(ctx context.Context, txOrm orm.TxOrmer) error {
//		var holder []string
//		for range tradeCodeList {
//			holder = append(holder, "?")
//		}
//		sql := ` DELETE FROM edbdata WHERE TRADE_CODE in (` + strings.Join(holder, ",") + `) `
//		err := txOrm.Raw(sql, tradeCodeList).Error
//		if err != nil {
//			return err
//		}
//		sql = ` DELETE FROM edbinfo WHERE TRADE_CODE in (` + strings.Join(holder, ",") + `)`
//		err = txOrm.Raw(sql, tradeCodeList).Error
//		if err != nil {
//			return err
//		}
//		return nil
//	})
//	return
//}

func GetEdbinfoListCount(condition string, pars []interface{}, mobile string, roleType int) (count int, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ``
	var countNull sql2.NullInt64
	if mobile != "" && roleType == 1 {
		sql = `SELECT COUNT(1) AS count FROM edbinfo AS a 
             INNER JOIN edbinfo_user AS c ON a.TRADE_CODE=c.TRADE_CODE AND c.mobile=?
             WHERE a.classify_id>0`

		if condition != "" {
			sql += condition
		}
		err = o.Raw(sql, utils.ForwardPars(pars, mobile)...).Scan(&countNull).Error
		if err != nil {
			return
		}
		if countNull.Valid {
			count = int(countNull.Int64)
		}
	} else {
		sql := `SELECT COUNT(1) AS count FROM edbinfo AS a WHERE a.classify_id>0`

		if condition != "" {
			sql += condition
		}
		err = o.Raw(sql, pars...).Scan(&countNull).Error
		if err != nil {
			return
		}
		if countNull.Valid {
			count = int(countNull.Int64)
		}
	}
	return
}

type EdbinfoItem struct {
	TradeCode    string  `orm:"column(TRADE_CODE);pk" description:"指标code"`
	SecName      string  `orm:"column(SEC_NAME);" description:"指标名称"`
	Unit         string  `orm:"column(UNIT);" description:"单位"`
	Remark       string  `orm:"column(REMARK);" description:"备注"`
	Frequency    string  `description:"频度"`
	ClassifyId   int     `description:"分类id"`
	ClassifyName string  `description:"分类名称"`
	CreateDate   string  `description:"创建时间"`
	UserId       int     `description:"录入用户id"`
	UserName     string  `description:"录入用户名称"`
	NoticeTime   string  `description:"通知时间"`
	Mobile       string  `description:"录入者手机号"`
	ModifyTime   string  `description:"最近一次更新时间"`
	StartDate    string  `description:"数据开始日期"`
	EndDate      string  `description:"数据结束日期"`
	LatestValue  float64 `description:"指标最新值"`
}

func GetEdbinfoItemList(condition string, pars []interface{}, startSize, pageSize int, mobile string, roleType int) (items []*EdbinfoItem, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ``
	if mobile != "" && roleType == 1 {
		sql = ` SELECT DISTINCT a.*,b.classify_name FROM edbinfo AS a
                    LEFT JOIN edbdata_classify AS b ON a.classify_id=b.classify_id
                    INNER JOIN edbinfo_user AS c ON a.TRADE_CODE=c.TRADE_CODE AND c.mobile=?
                    WHERE a.classify_id>0`
		if condition != "" {
			sql += condition
		}
		sql += ` ORDER BY a.create_date DESC LIMIT ?,? `
		err = o.Raw(sql, mobile, pars, startSize, pageSize).Find(&items).Error
	} else {
		sql = `SELECT DISTINCT a.*,b.classify_name FROM edbinfo AS a
                     LEFT JOIN edbdata_classify AS b on a.classify_id=b.classify_id
                     WHERE a.classify_id>0`
		if condition != "" {
			sql += condition
		}
		sql += ` ORDER BY a.create_date DESC LIMIT ?,? `
		err = o.Raw(sql, pars, startSize, pageSize).Find(&items).Error
	}
	return
}

// EdbParamsInfo 指标数据结构体
type EdbParamsInfo struct {
	Unit      string `orm:"column(UNIT);" description:"单位"`
	Frequency string `orm:"column(frequency);" description:"单位"`
}

// GetEdbUnitList 获取指标单位
func GetEdbUnitList() (items []*EdbParamsInfo, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `SELECT UNIT from edbinfo group by UNIT`
	err = o.Raw(sql).Find(&items).Error
	return
}

// GetEdbFrequencyList 获取指标频度
func GetEdbFrequencyList(classifyId, userId int) (items []*EdbParamsInfo, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `SELECT frequency from edbinfo a 
join edbdata b on a.TRADE_CODE=b.TRADE_CODE
 where classify_id = ? `
	if userId > 0 {
		sql += ` and a.user_id = ` + fmt.Sprint(userId) + ` `
	}
	sql += ` group by a.frequency`
	err = o.Raw(sql, classifyId).Find(&items).Error
	return
}

type TargetListResp struct {
	List   []*EdbinfoItem
	Paging *paging.PagingItem `description:"分页数据"`
}

type EdbinfoAddReq struct {
	SecName    string `description:"指标名称"`
	Unit       string `description:"单位"`
	Frequency  string `description:"频度"`
	ClassifyId int    `description:"分类id"`
	NoticeTime string `description:"通知时间"`
}

// GetMaxTradeCode 获取指标最大trade_code
func GetMaxTradeCode() (max_trade_code string, err error) {
	sql := " SELECT MAX(TRADE_CODE) AS max_trade_code FROM edbinfo WHERE LEFT(TRADE_CODE,1)='W' "
	o := global.DbMap[utils.DbNameManualIndex]
	var codeNull sql2.NullString
	err = o.Raw(sql).Scan(&codeNull).Error
	max_trade_code = "W00"
	if codeNull.Valid {
		max_trade_code = codeNull.String
	}
	return
}

func GetEdbinfoBySecName(secName string) (item *Edbinfo, err error) {
	sql := `SELECT * FROM edbinfo WHERE SEC_NAME=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, secName).First(&item).Error
	return
}

func GetEdbinfoByTradeCode(tradeCode string) (item *Edbinfo, err error) {
	sql := `SELECT * FROM edbinfo WHERE TRADE_CODE=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, tradeCode).First(&item).Error
	return
}

func AddEdbinfo(tradeCode, secName, unit, remark, frequency, noticeTime string, classifyId int, userId int, userName string) (err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	currTime := time.Now().Format(utils.FormatDateTime)
	edbInfo := &Edbinfo{
		TradeCode:  tradeCode,
		SecName:    secName,
		Unit:       unit,
		Remark:     remark,
		Frequency:  frequency,
		ClassifyId: classifyId,
		CreateDate: currTime,
		UserId:     userId,
		UserName:   userName,
		NoticeTime: noticeTime,
		Mobile:     "",
		ModifyTime: currTime,
		IsJoinEdb:  0,
	}
	err = o.Create(edbInfo).Error
	return
}

func AddEdbinfoUser(tradeCode, mobile string) (err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `INSERT INTO edbinfo_user(TRADE_CODE, mobile) VALUES (?,?)`
	err = o.Exec(sql, tradeCode, mobile).Error
	return
}

type EdbinfoEditReq struct {
	TradeCode  string `description:"指标code"`
	SecName    string `description:"指标名称"`
	Unit       string `description:"单位"`
	Frequency  string `description:"频度"`
	ClassifyId int    `description:"分类id"`
	NoticeTime string `description:"通知时间"`
}

func EditEdbinfo(tradeCode, secName, unit, frequency, noticeTime string, classifyId int) (err error) {
	sql := `UPDATE edbinfo SET SEC_NAME= ?, UNIT = ?,classify_id=?,frequency=?,notice_time=?,create_date=NOW() WHERE TRADE_CODE=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Exec(sql, secName, unit, classifyId, frequency, noticeTime, tradeCode).Error
	return
}

func SearchTargetEntry(classifyId int, keyWord string) (items []*Edbinfo, err error) {
	where := ""
	pars := make([]interface{}, 0)
	sql := `SELECT * FROM edbinfo WHERE classify_id>0 AND classify_id=? `
	pars = append(pars, classifyId)
	if keyWord != "" {
		sql += `AND SEC_NAME LIKE ? `
		pars = utils.GetLikeKeywordPars(pars, keyWord, 1)
	}
	sql += where
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, pars...).Find(&items).Error
	return
}

type SearchTargetListResp struct {
	List []*Edbinfo
}

type EdbdataClassify struct {
	ClassifyId   int
	ClassifyName string
	ParentId     int
	EdbInfoTotal int
	TradeCode    string
	UniqueCode   string
}

//func GetEdbdataClassifyByClassifyName(classifyName string) (item *EdbdataClassify, err error) {
//	sql := `SELECT * FROM edbdata_classify WHERE classify_name=? `
//	o := global.DbMap[utils.DbNameManualIndex]
//	err = o.Raw(sql, classifyName).First(&item).Error
//	return
//}

type EdbdataClassifyList struct {
	ClassifyId   int
	ClassifyName string
	ParentId     int
	Child        []*EdbdataClassify
	TradeCode    string
	UniqueCode   string
}

func GetEdbdataClassify(userId int64) (items []*EdbdataClassifyList, err error) {
	var newItems []*EdbdataClassifyList
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` SELECT classify_id,classify_name,parent_id FROM edbdata_classify WHERE parent_id=0 `
	err = o.Raw(sql).Find(&newItems).Error
	if err != nil {
		return
	}
	classifyLen := len(newItems)

	// 获取子集分类
	var allChildItems []*EdbdataClassify
	if userId > 0 {
		userClassifyList, _ := GetManualUserClassify(int(userId))
		var userIdArr []int
		for _, v := range userClassifyList {
			userIdArr = append(userIdArr, v.ClassifyId)
		}
		num := len(userClassifyList)
		if num > 0 {
			childSql := "SELECT a.classify_id,a.classify_name,a.parent_id FROM edbdata_classify AS a WHERE a.is_show=1 and a.classify_id IN(" + utils.GetOrmInReplace(num) + ") ORDER BY a.create_time ASC "
			err = o.Raw(childSql, userIdArr).Find(&allChildItems).Error
		}
	} else {
		childSql := "SELECT classify_id,classify_name,parent_id FROM edbdata_classify WHERE is_show=1 ORDER BY create_time ASC "
		err = o.Raw(childSql).Find(&allChildItems).Error
	}
	if err != nil {
		return
	}

	for i := 0; i < classifyLen; i++ {
		var childItems []*EdbdataClassify
		parentId := newItems[i].ClassifyId
		for _, v := range allChildItems {
			if v.ParentId == parentId {
				childItems = append(childItems, v)
			}
		}
		newItems[i].Child = childItems
	}
	for _, v := range newItems {
		childLen := len(v.Child)
		if childLen > 0 {
			items = append(items, v)
		}
	}
	return
}

type ManualUserClassify struct {
	ManualUserClassifyId int `orm:"column(manual_user_classify_id);pk"`
	AdminId              int
	ClassifyId           int
	CreateTime           time.Time
}

func GetManualUserClassify(sysUserId int) (list []*ManualUserClassify, err error) {
	o := global.DbMap[utils.DbNameIndex]
	sql := `SELECT * FROM manual_user_classify WHERE admin_id=? `
	err = o.Raw(sql, sysUserId).Find(&list).Error
	return
}

type EdbdataClassifyResp struct {
	List []*EdbdataClassifyList
}

func GetTargetBySecName(secName string) (item *Edbinfo, err error) {
	sql := `SELECT * FROM edbinfo WHERE SEC_NAME=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, secName).First(&item).Error
	return
}

// 更新指标数据信息
func (edbinfo *Edbinfo) Update(cols []string) (err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Select(cols).Updates(edbinfo).Error
	return
}

//func ModifyTargetClassifyId(tradeCode string, classifyId int) (err error) {
//	sql := `UPDATE edbinfo SET classify_id=? WHERE TRADE_CODE=? `
//	o := global.DbMap[utils.DbNameManualIndex]
//	err = o.Exec(sql, classifyId, tradeCode).Error
//	return
//}

//func GetTargetsDataCount(tradeCode, dt string) (count int, err error) {
//	sql := `SELECT COUNT(1) AS count FROM edbdata WHERE TRADE_CODE=? AND DT=? `
//	o := global.DbMap[utils.DbNameManualIndex]
//	err = o.Raw(sql, tradeCode, dt).QueryRow(&count)
//	return
//}

// GetTargetsDataList 根据code获取指标数据列表
func GetTargetsDataList(tradeCode string) (items []*Edbdata, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `SELECT * FROM edbdata WHERE TRADE_CODE=? ORDER BY DT ASC `
	err = o.Raw(sql, tradeCode).Find(&items).Error
	return
}

//func GetTargetsData(tradeCode, dt string) (item *Edbdata, err error) {
//	sql := `SELECT * FROM edbdata WHERE TRADE_CODE=? AND DT=? `
//	o := global.DbMap[utils.DbNameManualIndex]
//	err = o.Raw(sql, tradeCode, dt).First(&item).Error
//	return
//}

func ModifyTargetsDataByImport(tradeCode, dt, close string) (err error) {
	sql := `UPDATE  edbdata SET CLOSE=?,modify_time=NOW() WHERE  TRADE_CODE=? AND DT=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Exec(sql, close, tradeCode, dt).Error
	return
}

func AddTargetsDataByImport(tradeCode, dt, close string) (err error) {
	sql := `INSERT INTO edbdata(TRADE_CODE, DT,CLOSE, modify_time)VALUES(?,?,?,NOW()) `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Exec(sql, tradeCode, dt, close).Error
	return
}

type EdbdataImportResp struct {
	Status       int
	Msg          string
	SuccessCount int
	FailCount    int
	IndexCount   int `description:"指标数"`
}

func GetFailList(sysUserId int) (items []*EdbdataImportFail, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` SELECT * FROM edbdata_import_fail WHERE sys_user_id=? `
	err = o.Raw(sql, sysUserId).Find(&items).Error
	return
}

type DataListForExport struct {
	TradeCode    string `orm:"column(TRADE_CODE)" description:"指标code"`
	SecName      string `orm:"column(SEC_NAME)" description:"指标名称"`
	Unit         string `orm:"column(UNIT)" description:"单位"`
	Frequency    string `description:"频度"`
	ClassifyId   int    `description:"分类id"`
	NoticeTime   string `description:"通知时间"`
	ClassifyName string
	Dt           string  `orm:"column(DT)" description:"日期"`
	Close        float64 `orm:"column(CLOSE)" description:"值"`
}

//func GetDataListForExport(startDate, endDate, frequency, keyWord string, classifyId int) (items []*DataListForExport, err error) {
//	where := ``
//	var pars []interface{}
//	if keyWord != "" {
//		where = ` AND SEC_NAME LIKE ? `
//		pars = utils.GetLikeKeywordPars(pars, keyWord, 1)
//	}
//	if startDate != "" {
//		where += ` AND create_date>=? `
//		pars = append(pars, startDate)
//	}
//	if endDate != "" {
//		where += ` AND create_date<=? `
//		pars = append(pars, endDate)
//	}
//	if frequency != "" {
//		where += ` AND frequency=? `
//		pars = append(pars, frequency)
//	}
//	if classifyId > 0 {
//		where += ` AND classify_id=? `
//		pars = append(pars, classifyId)
//	}
//
//	sql := ` SELECT a.TRADE_CODE,a.SEC_NAME,a.UNIT,a.frequency,a.classify_id,b.classify_name,c.DT,c.CLOSE FROM edbdata AS c
//                INNER JOIN edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
//                LEFT JOIN edbdata_classify AS b ON a.classify_id=b.classify_id
//                WHERE a.classify_id>0 `
//	if where != "" {
//		sql += where
//	}
//	sql = sql + " ORDER BY c.DT DESC "
//	o := global.DbMap[utils.DbNameManualIndex]
//	err = o.Raw(sql, pars...).Find(&items).Error
//	return
//}

type DataDeleteReq struct {
	TradeCode  string `description:"指标唯一编码"`
	CreateDate string `description:"数据录入日期"`
}

func DataDelete(tradeCode, createDate, close string, modifyTime time.Time, sysUserId int) (err error) {
	to := global.DbMap[utils.DbNameManualIndex].Begin()
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()
	recordSql := ` INSERT INTO edbdata_delete_record(TRADE_CODE,DT,CLOSE,modify_time,create_time,sys_user_id) 
                 VALUES(?,?,?,?,?,?)`
	err = to.Exec(recordSql, tradeCode, createDate, close, modifyTime, time.Now(), sysUserId).Error
	sql := ` DELETE FROM edbdata WHERE TRADE_CODE = ? AND DT = ? `
	err = to.Exec(sql, tradeCode, createDate).Error
	return
}

//func GetTargetInfoCount(tradeCode string) (count int, err error) {
//	sql := ` SELECT COUNT(1) AS count FROM edbdata AS c
//           INNER JOIN edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
//           WHERE a.TRADE_CODE=? `
//	o := global.DbMap[utils.DbNameManualIndex]
//	err = o.Raw(sql, tradeCode).QueryRow(&count)
//	return
//}

type TargetDeleteReq struct {
	TradeCode string `description:"指标唯一编码"`
}

func TargetDelete(tradeCode string) (err error) {
	to := global.DbMap[utils.DbNameManualIndex].Begin()
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	sql := " DELETE FROM edbinfo WHERE TRADE_CODE = ? "
	err = to.Exec(sql, tradeCode).Error

	sql = " DELETE FROM edbdata WHERE TRADE_CODE = ? "
	err = to.Exec(sql, tradeCode).Error
	return
}

type Researcher struct {
	AdminId     int    `description:"系统用户id"`
	AdminName   string `description:"系统用户名称"`
	RealName    string `description:"系统用户姓名"`
	Role        string `description:"系统用户角色"`
	Mobile      string `description:"手机号"`
	TargetCount int    `description:"指标数量"`
}

type ResearcherListResp struct {
	List []*Researcher
}

func GetResearcherEntry() (items []*Researcher, err error) {
	sql := ` SELECT admin_id,admin_name,real_name,mobile,0 as target_count FROM admin WHERE role_type=1 `
	o := global.DEFAULT_DB
	err = o.Raw(sql).Find(&items).Error
	researchLen := len(items)
	edbO := global.DbMap[utils.DbNameManualIndex]
	for i := 0; i < researchLen; i++ {
		var count int
		mobile := items[i].Mobile
		sqlCount := ` SELECT COUNT(DISTINCT a.TRADE_CODE) AS count FROM  edbinfo_user AS a
            INNER JOIN edbinfo AS b ON a.TRADE_CODE=b.TRADE_CODE
            WHERE a.mobile=? AND b.classify_id>0 `
		var countNull sql2.NullInt64
		err = edbO.Raw(sqlCount, mobile).Scan(&countNull).Error
		if err != nil {
			return
		}
		if countNull.Valid {
			count = int(countNull.Int64)
		}

		items[i].TargetCount = count
	}
	return
}

func GetResearcherEntryByMobile(mobile string) (items []*Researcher, err error) {
	sql := ` SELECT admin_id,admin_name,real_name,mobile,0 as target_count FROM admin WHERE  role_type=1 `
	if mobile != "" {
		sql += ` AND mobile IN(` + mobile + `)`
	}
	o := global.DEFAULT_DB
	err = o.Raw(sql).Find(&items).Error
	researchLen := len(items)
	edbO := global.DbMap[utils.DbNameManualIndex]
	for i := 0; i < researchLen; i++ {
		var count int
		mobile := items[i].Mobile
		sqlCount := ` SELECT COUNT(DISTINCT a.TRADE_CODE) AS count FROM  edbinfo_user AS a
            INNER JOIN edbinfo AS b ON a.TRADE_CODE=b.TRADE_CODE
            WHERE a.mobile=? AND b.classify_id>0 `
		var countNull sql2.NullInt64
		err = edbO.Raw(sqlCount, mobile).Scan(&countNull).Error
		if err != nil {
			return
		}
		if countNull.Valid {
			count = int(countNull.Int64)
		}

		items[i].TargetCount = count
	}
	return
}

type EdbinfoItems struct {
	TradeCode    string `orm:"column(TRADE_CODE);pk" description:"指标code"`
	SecName      string `orm:"column(SEC_NAME);" description:"指标名称"`
	Unit         string `orm:"column(UNIT);" description:"单位"`
	Remark       string `orm:"column(REMARK);" description:"备注"`
	Frequency    string `description:"频度"`
	ClassifyId   int    `description:"分类id"`
	ClassifyName string `description:"分类名称"`
	CreateDate   string `description:"创建时间"`
	UserId       int    `description:"录入用户id"`
	NoticeTime   string `description:"通知时间"`
	Mobile       string `description:"录入者手机号"`
	ModifyDate   string `description:"待更新日期"`
	Status       string `description:"状态：未完成/完成"`
}

type TargetItemsResp struct {
	List SortEdbInfo
}

type SortEdbInfo []EdbinfoItems

func GetTargetItems(mobile string, classifyId int) (lastItems SortEdbInfo, err error) {
	var items []*EdbinfoItems
	o := global.DbMap[utils.DbNameManualIndex]
	//sql := ` SELECT *,'' modify_date,'' status FROM edbinfo AS a WHERE a.classify_id>0 `

	sql := ` SELECT *,'' modify_date,'' STATUS FROM edbinfo AS a 
            WHERE a.classify_id>0
             `
	if classifyId > 0 {
		sql += ` AND a.classify_id=` + strconv.Itoa(classifyId) + ``
	}
	sql += ` GROUP BY a.TRADE_CODE `

	//if classifyId > 0 {
	//	sql = ` SELECT *,'' modify_date,'' status FROM edbinfo AS a
	//        WHERE a.classify_id=` + strconv.Itoa(classifyId) + ` AND a.classify_id>0
	//         GROUP BY a.TRADE_CODE `
	//}

	sql = sql + ` ORDER BY CONVERT(a.SEC_NAME USING gbk )  COLLATE gbk_chinese_ci ASC `
	err = o.Raw(sql).Find(&items).Error
	if err != nil {
		return
	}
	itemsLen := len(items)
	nowWeek := time.Now().Weekday().String()

	fmt.Println(nowWeek)
	finishEdbInfo := SortEdbInfo{}
	unFinishEdbInfo := SortEdbInfo{}

	for i := 0; i < itemsLen; i++ {
		noticeTime := items[i].NoticeTime
		frequency := items[i].Frequency
		tradeCode := items[i].TradeCode
		if noticeTime != "" {
			if frequency == "周度" {
				noticeArr := strings.Split(noticeTime, " ")
				noticeWeek := noticeArr[0]
				fmt.Println(noticeWeek)
				addDay := 0

				if nowWeek == "Sunday" {
					if noticeWeek == "周日" {
						addDay = 0
					} else if noticeWeek == "周一" {
						addDay = 1
					} else if noticeWeek == "周二" {
						addDay = 2
					} else if noticeWeek == "周三" {
						addDay = 3
					} else if noticeWeek == "周四" {
						addDay = 4
					} else if noticeWeek == "周五" {
						addDay = 5
					} else if noticeWeek == "周六" {
						addDay = 6
					} else {
						addDay = 0
					}
				} else if nowWeek == "Monday" {
					if noticeWeek == "周日" {
						addDay = 6
					} else if noticeWeek == "周一" {
						addDay = 0
					} else if noticeWeek == "周二" {
						addDay = 1
					} else if noticeWeek == "周三" {
						addDay = 2
					} else if noticeWeek == "周四" {
						addDay = 3
					} else if noticeWeek == "周五" {
						addDay = 4
					} else if noticeWeek == "周六" {
						addDay = 5
					} else {
						addDay = 0
					}
				} else if nowWeek == "Tuesday" {
					if noticeWeek == "周日" {
						addDay = 5
					} else if noticeWeek == "周一" {
						addDay = 6
					} else if noticeWeek == "周二" {
						addDay = 0
					} else if noticeWeek == "周三" {
						addDay = 1
					} else if noticeWeek == "周四" {
						addDay = 2
					} else if noticeWeek == "周五" {
						addDay = 3
					} else if noticeWeek == "周六" {
						addDay = 4
					} else {
						addDay = 0
					}
				} else if nowWeek == "Wednesday" {
					if noticeWeek == "周日" {
						addDay = 4
					} else if noticeWeek == "周一" {
						addDay = 5
					} else if noticeWeek == "周二" {
						addDay = 6
					} else if noticeWeek == "周三" {
						addDay = 0
					} else if noticeWeek == "周四" {
						addDay = 1
					} else if noticeWeek == "周五" {
						addDay = 2
					} else if noticeWeek == "周六" {
						addDay = 3
					} else {
						addDay = 0
					}
				} else if nowWeek == "Thursday" {
					if noticeWeek == "周日" {
						addDay = 3
					} else if noticeWeek == "周一" {
						addDay = 4
					} else if noticeWeek == "周二" {
						addDay = 5
					} else if noticeWeek == "周三" {
						addDay = 6
					} else if noticeWeek == "周四" {
						addDay = 0
					} else if noticeWeek == "周五" {
						addDay = 1
					} else if noticeWeek == "周六" {
						addDay = 2
					} else {
						addDay = 0
					}
				} else if nowWeek == "Friday" {
					if noticeWeek == "周日" {
						addDay = 2
					} else if noticeWeek == "周一" {
						addDay = 3
					} else if noticeWeek == "周二" {
						addDay = 4
					} else if noticeWeek == "周三" {
						addDay = 5
					} else if noticeWeek == "周四" {
						addDay = 6
					} else if noticeWeek == "周五" {
						addDay = 0
					} else if noticeWeek == "周六" {
						addDay = 1
					} else {
						addDay = 0
					}
				} else if nowWeek == "Saturday" {
					if noticeWeek == "周日" {
						addDay = 1
					} else if noticeWeek == "周一" {
						addDay = 2
					} else if noticeWeek == "周二" {
						addDay = 3
					} else if noticeWeek == "周三" {
						addDay = 4
					} else if noticeWeek == "周四" {
						addDay = 5
					} else if noticeWeek == "周五" {
						addDay = 6
					} else if noticeWeek == "周六" {
						addDay = 0
					} else {
						addDay = 0
					}
				}

				modifyDate := time.Now().AddDate(0, 0, addDay)
				modifyDateStr := modifyDate.Format(utils.FormatDate)
				items[i].ModifyDate = modifyDateStr

				modifyDateEndStr := modifyDate.AddDate(0, 0, -7).Format(utils.FormatDate)

				fmt.Println("addDay:", addDay)
				fmt.Println("modifyDateEndStr:", modifyDateEndStr)

				count := 0
				sqlCount := ` SELECT COUNT(1) AS num FROM edbdata WHERE TRADE_CODE=? AND DT >= ? AND DT <= ? `
				//err = o.Raw(sqlCount, tradeCode, modifyDateEndStr, modifyDateStr).QueryRow(&count)

				var countNull sql2.NullInt64
				err = o.Raw(sqlCount, tradeCode, modifyDateEndStr, modifyDateStr).Scan(&countNull).Error
				if err != nil {
					return nil, err
				}
				if countNull.Valid {
					count = int(countNull.Int64)
				}

				if count > 0 {
					items[i].Status = "完成"
					finishEdbInfo = append(finishEdbInfo, *items[i])
				} else {
					items[i].Status = "未完成"
					unFinishEdbInfo = append(unFinishEdbInfo, *items[i])
				}
			} else if frequency == "日度" {
				items[i].Status = "完成"
				finishEdbInfo = append(finishEdbInfo, *items[i])
			} else if frequency == "月度" {
				myYear := time.Now().Year()
				myMonth := time.Now().Format("01")
				startDate, endDate := utils.GetMonthStartAndEnd(strconv.Itoa(myYear), myMonth)
				count := 0
				sqlCount := ` SELECT COUNT(1) AS num FROM edbdata WHERE TRADE_CODE=? AND DT >= ? AND DT <= ? `
				//err = o.Raw(sqlCount, tradeCode, startDate, endDate).QueryRow(&count)

				var countNull sql2.NullInt64
				err = o.Raw(sqlCount, tradeCode, startDate, endDate).Scan(&countNull).Error
				if err != nil {
					return nil, err
				}
				if countNull.Valid {
					count = int(countNull.Int64)
				}

				if noticeTime != "" {
					var modifyDateStr string
					strArr := strings.Split(noticeTime, "日")
					myYear := time.Now().Year()
					myMonth := time.Now().Format("01")
					modifyDateStr = strconv.Itoa(myYear) + "-" + myMonth + "-" + strArr[0]
					items[i].ModifyDate = modifyDateStr
				}
				if count > 0 {
					items[i].Status = "完成"
					finishEdbInfo = append(finishEdbInfo, *items[i])
				} else {
					items[i].Status = "未完成"
					unFinishEdbInfo = append(unFinishEdbInfo, *items[i])
				}
			} else {
				items[i].Status = "完成"
				finishEdbInfo = append(finishEdbInfo, *items[i])
			}
		} else {
			if frequency == "月度" {
				myYear := time.Now().Year()
				myMonth := time.Now().Format("01")
				startDate, endDate := utils.GetMonthStartAndEnd(strconv.Itoa(myYear), myMonth)
				count := 0
				sqlCount := ` SELECT COUNT(1) AS num FROM edbdata WHERE TRADE_CODE=? AND DT >= ? AND DT <= ? `
				//err = o.Raw(sqlCount, tradeCode, startDate, endDate).QueryRow(&count)

				var countNull sql2.NullInt64
				err = o.Raw(sqlCount, tradeCode, startDate, endDate).Scan(&countNull).Error
				if err != nil {
					return nil, err
				}
				if countNull.Valid {
					count = int(countNull.Int64)
				}

				if count > 0 {
					items[i].Status = "完成"
					finishEdbInfo = append(finishEdbInfo, *items[i])
				} else {
					items[i].Status = "未完成"
					unFinishEdbInfo = append(unFinishEdbInfo, *items[i])
				}
			} else {
				items[i].Status = "完成"
				finishEdbInfo = append(finishEdbInfo, *items[i])
			}
		}
	}
	sort.Sort(SortByModifyDate{finishEdbInfo})
	sort.Sort(SortByModifyDate{unFinishEdbInfo})
	lastItems = append(lastItems, unFinishEdbInfo...)
	lastItems = append(lastItems, finishEdbInfo...)
	return
}

// 获取此 slice 的长度
func (p SortEdbInfo) Len() int { return len(p) }

// 根据元素的状态降序排序
func (p SortEdbInfo) Less(i, j int) bool {
	return p[i].Status > p[j].Status
}

// 交换数据
func (p SortEdbInfo) Swap(i, j int) { p[i], p[j] = p[j], p[i] }

// 嵌套结构体  将继承 SortEdbInfo 的所有属性和方法
// 所以相当于SortByName 也实现了 Len() 和 Swap() 方法
type SortByStatus struct{ SortEdbInfo }

// 根据元素的姓名长度降序排序 （此处按照自己的业务逻辑写）
func (p SortByStatus) Less(i, j int) bool {
	return len(p.SortEdbInfo[i].Status) > len(p.SortEdbInfo[j].Status)
}

type SortByModifyDate struct{ SortEdbInfo }

// 根据元素的年龄降序排序 （此处按照自己的业务逻辑写）
func (p SortByModifyDate) Less(i, j int) bool {
	return p.SortEdbInfo[i].ModifyDate > p.SortEdbInfo[j].ModifyDate
}

type DataCheckResp struct {
	Status int    `description:"状态：1：该日期已存在数据，是否确认修改？,0:数据不存在"`
	Close  string `description:"值"`
}

type TargetCheckResp struct {
	Status int `description:"状态：1：该指标有关联数据，请先删除数据,0:指标不存在关联数据，可直接删除"`
}

type EdbdataExportList struct {
	TradeCode    string `orm:"column(TRADE_CODE);" description:"指标code"`
	SecName      string `orm:"column(SEC_NAME);" description:"指标名称"`
	Unit         string `orm:"column(UNIT);" description:"单位"`
	Remark       string `orm:"column(REMARK);" description:"备注"`
	Frequency    string `description:"频度"`
	ClassifyId   int    `description:"分类id"`
	ClassifyName string `description:"分类名称"`
	CreateDate   string `description:"创建时间"`
	Dt           string `orm:"column(Dt);" description:"最新一次录入时间"`
}

func GetEdbdataSecName(condition string, pars []interface{}) (items []*EdbdataExportList, err error) {
	//sql := `SELECT a.TRADE_CODE,a.SEC_NAME,a.frequency,a.UNIT,MAX(c.DT) AS Dt
	//	        FROM edbdata AS c
	//	        INNER JOIN edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
	//	        INNER JOIN edbinfo_user AS d ON a.TRADE_CODE=d.TRADE_CODE
	//	        LEFT JOIN edbdata_classify AS b ON a.classify_id=b.classify_id
	//	        WHERE a.classify_id>0`
	sql := `SELECT a.TRADE_CODE,a.SEC_NAME,a.frequency,a.UNIT,MAX(c.DT) AS Dt,b.classify_name
		        FROM edbdata AS c
		        INNER JOIN edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
		        LEFT JOIN edbdata_classify AS b ON a.classify_id=b.classify_id
		        WHERE a.classify_id>0`
	if condition != "" {
		sql += condition
	}
	sql += " GROUP BY a.TRADE_CODE ORDER BY a.TRADE_CODE ASC "
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, pars...).Find(&items).Error
	return
}

func GetEdbDataFrequency(classifyId int) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM edbinfo where classify_id=? AND frequency IS NOT NULL ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年度','年度') `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, classifyId).Find(&items).Error
	return
}

// GetEdbDataFrequencyByClassifyIdList
// @Description: 根据分类列表获取所有的频度
// @author: Roc
// @datetime 2024-08-15 17:51:13
// @param classifyIdList []int
// @return items []*string
// @return err error
func GetEdbDataFrequencyByClassifyIdList(classifyIdList []int) (items []string, err error) {
	num := len(classifyIdList)
	if num <= 0 {
		return
	}
	sql := `SELECT DISTINCT frequency FROM edbinfo where classify_id in (` + utils.GetOrmInReplace(num) + `) AND frequency IS NOT NULL ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年度','年度') `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, classifyIdList).Find(&items).Error
	return
}

func GetEdbDataFrequencyByKeyord(keyword string) (items []string, err error) {
	sql := `SELECT DISTINCT frequency FROM edbinfo where SEC_NAME=? ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年度','年度') `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, keyword).Find(&items).Error
	return
}

type EdbdataList struct {
	Dt string `orm:"column(DT);" description:"录入时间"`
}

func GetEdbdataList(tradeCode string) (items []*EdbdataList, err error) {
	sql := ` SELECT  DT FROM edbdata WHERE TRADE_CODE IN(` + tradeCode + `)  GROUP BY DT ORDER BY DT DESC `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql).Find(&items).Error
	return
}

type EdbdataItem struct {
	TradeCode string  `orm:"column(TRADE_CODE);" description:"指标code"`
	Dt        string  `orm:"column(DT);" description:"最新一次录入时间"`
	Close     float64 `orm:"column(CLOSE);" description:"值"`
}

func GetEdbdataValueByTradeCode(tradeCode, dt string) (item *EdbdataItem, err error) {
	sql := ` SELECT  TRADE_CODE,DT,CLOSE FROM edbdata WHERE TRADE_CODE=? AND DT=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, tradeCode, dt).First(&item).Error
	return
}

func GetEdbdataAllByTradeCode(tradeCode string) (items []*EdbdataItem, err error) {
	sql := ` SELECT * FROM edbdata WHERE TRADE_CODE=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, tradeCode).Find(&items).Error
	return
}

func GetEdbdataClassifyByParentId(parentId int) (items []*EdbdataClassify, err error) {
	sql := ` SELECT * FROM edbdata_classify WHERE parent_id=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, parentId).Find(&items).Error
	return
}

type LzPriceClassify struct {
	ProductName string
}

func GetLzPriceClassify() (items []*LzPriceClassify, err error) {
	sql := ` SELECT product_name  FROM longzhongpriceinfo GROUP BY product_name ORDER BY product_name DESC `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql).Find(&items).Error
	return
}

type Longzhongpriceinfo struct {
	LongzhongpriceinfoId int `orm:"column(longzhongpriceinfo_id);pk"`
	Standard             string
	ModelName            string
	Unit                 string
	AreaName             string
	PriceType            string
	Memo                 string
	PriceId              string
	ProductName          string
	InfoType             string
	InfoTypeRemark       string
	MarketName           string
	ManufactureName      string
}

func GetLongzhongpriceinfoByClassifyName(productName string) (items []*Longzhongpriceinfo, err error) {
	sql := `SELECT * FROM longzhongpriceinfo WHERE product_name=? ORDER BY longzhongpriceinfo_id ASC `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, productName).Find(&items).Error
	return
}

func GetLongzhongPriceDataMaxCount(productName string) (count int, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `SELECT MAX(t.num) AS count FROM (
				SELECT COUNT(1) AS num  FROM longzhongpriceinfo AS a
				INNER JOIN longzhongpricedata AS b ON a.longzhongpriceinfo_id=b.longzhongpriceinfo_id
				WHERE a.product_name=?
				GROUP BY a.product_name
			)AS t `
	//err = o.Raw(sql, productName).QueryRow(&count)
	var countNull sql2.NullInt64
	err = o.Raw(sql, productName).Scan(&countNull).Error
	if err != nil {
		return
	}
	if countNull.Valid {
		count = int(countNull.Int64)
	}
	return
}

type LongzhongpricedataItems struct {
	LongzhongpricedataId int `orm:"column(longzhongpricedata_id);pk"`
	LongzhongpriceinfoId int
	PriceDate            string
	Memo                 string
	Price                float64
	CnyPrice             float64
	ZsyPrice             float64
	ZshPrice             float64
	LowPrice             float64
	HighPrice            float64
	RisePrice            float64
	TonPrice             float64
	PriceType            string
	UpdateDate           string
}

func GetLongzhongPriceDataById(lzPriceInfoId int) (items []*LongzhongpricedataItems, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` SELECT DISTINCT a.longzhongpriceinfo_id,a.price_date,a.memo,a.price,a.cny_price,a.zsy_price,a.zsh_price,a.low_price,a.high_price,a.rise_price,a.ton_price,a.price_type,a.update_date  
			 FROM longzhongpricedata AS a
			 WHERE longzhongpriceinfo_id=? ORDER BY price_date DESC `
	err = o.Raw(sql, lzPriceInfoId).Find(&items).Error
	return
}

func GetLzSurveyClassify() (items []*LzPriceClassify, err error) {
	sql := ` SELECT breed_name AS product_name  FROM longzhong_survey_product GROUP BY breed_name ORDER BY breed_name DESC `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql).Find(&items).Error
	return
}

type LongzhongSurveyProduct struct {
	SurveyProductId      int `orm:"column(survey_product_id);pk"`
	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           string
	TaskShouldFinishTime int
	CustomId             string
	CustomType           int64
	Custom               string
	QuotaSampleId        int64
	StartDate            string
	EndDate              string
	LzCode               string
}

func GetLongzhongSurveyProductByClassifyName(productName string) (items []*LongzhongSurveyProduct, err error) {
	sql := `SELECT * FROM longzhong_survey_product WHERE breed_name=? ORDER BY survey_product_id ASC `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, productName).Find(&items).Error
	return
}

func GetLzSurveyProductByNameAndFrequency(productName string, frequency int) (items []*LongzhongSurveyProduct, err error) {

	sql := `SELECT * FROM longzhong_survey_product WHERE breed_name=? AND frequency=? ORDER BY survey_product_id ASC `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, productName, frequency).Find(&items).Error
	return
}

func GetExportLzSurveyProductByBreedIds(breedIds []string) (items []*LongzhongSurveyProduct, err error) {
	if len(breedIds) == 0 {
		return
	}
	field := ` survey_product_id, breed_id, breed_name, sample_name, custom, quota_name, lz_code, frequency, unit_name, end_date, input_value `
	sql := `SELECT ` + field + ` FROM longzhong_survey_product WHERE breed_id IN (` + utils.GetOrmInReplace(len(breedIds)) + `) ORDER BY breed_id ASC, frequency ASC, survey_product_id ASC `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, breedIds).Find(&items).Error
	return
}

func GetLzFrequency(productName string) (items []*int, err error) {
	sql := `SELECT DISTINCT frequency FROM longzhong_survey_product WHERE breed_name=? ORDER BY frequency`
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql, productName).Find(&items).Error
	return
}

// EdbInfoItem
type EdbInfoItem struct {
	TradeCode        string                 `orm:"column(TRADE_CODE);pk" description:"指标code"`
	SecName          string                 `orm:"column(SEC_NAME);" description:"指标名称"`
	Unit             string                 `orm:"column(UNIT);" description:"单位"`
	Remark           string                 `orm:"column(REMARK);" description:"备注"`
	Frequency        string                 `description:"频度"`
	ClassifyId       int                    `description:"分类id"`
	ClassifyName     string                 `description:"分类名称"`
	CreateDate       string                 `description:"创建时间"`
	UserId           int                    `description:"录入用户id"`
	UserName         string                 `description:"录入用户名称"`
	NoticeTime       string                 `description:"通知时间"`
	Mobile           string                 `description:"录入者手机号"`
	ModifyDate       string                 `description:"待更新日期"`
	ModifyTime       string                 `description:"最近一次更新时间"`
	Status           string                 `description:"状态：未完成/完成"`
	IsJoinEdb        int8                   `description:"指标库是否已添加：0-否；1-是"`
	StartDate        string                 `description:"数据开始日期"`
	EndDate          string                 `description:"数据结束日期"`
	LatestValue      float64                `description:"指标最新值"`
	DataList         []*Edbdata             `description:"指标数据列表"`
	NextDataTimeList []*EdbDataNextDateTime `description:"下期数据时间列表"`
}

// GetTargetItemList 获取指标列表数据
func GetTargetItemList(classifyId, edbShowType int, frequency, keyword, tradeCode string, classifyIdStrList []string) (items []*EdbInfoItem, err error) {
	o := global.DbMap[utils.DbNameManualIndex]

	pars := make([]interface{}, 0)

	sql := ` SELECT a.*,'' modify_date,'' STATUS FROM edbinfo AS a `
	if edbShowType != 0 {
		sql = ` SELECT a.*,b.DT,'' modify_date,'' STATUS FROM edbinfo AS a 
left join edbdata b on a.TRADE_CODE=b.TRADE_CODE `
	}
	sql += ` WHERE a.classify_id>0 `

	//如果没有分类id集合列表，那么就没有数据了，不用往下执行了，直接返回好了
	if len(classifyIdStrList) <= 0 {
		return
	}
	if len(classifyIdStrList) > 0 {
		sql += ` AND a.classify_id in (` + strings.Join(classifyIdStrList, ",") + `)  `
	}
	if classifyId > 0 {
		sql += ` AND a.classify_id=` + strconv.Itoa(classifyId) + ` `
	}
	//频度
	if frequency != "" {
		sql += ` AND a.frequency="` + frequency + `" `
	}
	//关键字
	if keyword != "" {
		sql += ` AND (a.SEC_NAME like ?  or a.TRADE_CODE like ? )`
		pars = utils.GetLikeKeywordPars(pars, keyword, 2)
	}
	//指定指标
	if tradeCode != "" {
		sql += ` AND a.TRADE_CODE = "` + tradeCode + `" `
	}
	//指标里面是否有数据
	switch edbShowType {
	case 1:
		sql += ` AND b.CLOSE is not null `
	case 2:
		sql += ` AND b.CLOSE is null `
	}
	sql += ` GROUP BY a.TRADE_CODE `

	sql = sql + ` ORDER BY CONVERT(a.SEC_NAME USING gbk )  COLLATE gbk_chinese_ci ASC `
	err = o.Raw(sql, pars...).Find(&items).Error
	return

}

// GetLzItemList 模糊查询隆众数据库指标列表
//func GetLzItemList(keyword string) (items []*data_manage.LongzhongSurveyProduct, err error) {
//	o := global.DbMap[utils.DbNameManualIndex]
//	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)).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 := global.DbMap[utils.DbNameManualIndex]
//	sql := "SELECT * FROM longzhong_survey_data WHERE survey_product_id=? GROUP BY data_time DESC"
//	err = o.Raw(sql, lzCode).Find(&items).Error
//	return
//}

// GetEdbDataListByCodes 通过指标ID获取所有数据
func GetEdbDataListByCodes(tradeCode string) (items []*Edbdata, err error) {
	sql := ` SELECT  TRADE_CODE,DT,round(CLOSE,4) CLOSE,modify_time  FROM edbdata WHERE TRADE_CODE IN(` + tradeCode + `)  GROUP BY TRADE_CODE,DT ORDER BY DT DESC `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql).Find(&items).Error
	return
}

// TargetItemListResp 指标数据结构体
type TargetItemListResp struct {
	List          []*EdbInfoItem
	FrequencyList []string
}

// BatchDataDeleteReq 批量删除某日的指标数据请求结构体
type BatchDataDeleteReq struct {
	CreateDate    string   `description:"创建日期"`
	TradeCodeList []string `description:"指标唯一编码列表"`
}

// BatchDeleteEdbDataByDate 批量删除某日的指标数据
func BatchDeleteEdbDataByDate(tradeCodes, dt string, opUserId int) (err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	var list []*Edbdata
	sql := ` select * FROM edbdata WHERE TRADE_CODE in  (` + tradeCodes + `) AND DT = ? `
	err = o.Raw(sql, dt).Find(&list).Error
	if err != nil {
		return
	}
	deleteRecordList := make([]*EdbdataDeleteRecord, 0)
	for _, edbDataInfo := range list {
		deleteRecord := &EdbdataDeleteRecord{
			TradeCode:  edbDataInfo.TradeCode,
			Dt:         edbDataInfo.Dt,
			Close:      edbDataInfo.Close,
			ModifyTime: time.Now(),
			CreateTime: time.Now(),
			SysUserId:  opUserId,
		}
		deleteRecordList = append(deleteRecordList, deleteRecord)
	}
	if len(deleteRecordList) > 0 {
		tmpErr := o.CreateInBatches(deleteRecordList, utils.MultiAddNum).Error
		if tmpErr != nil {
			err = tmpErr
			return
		}
	}
	sql = ` DELETE FROM edbdata WHERE TRADE_CODE in  (` + tradeCodes + `) AND DT = ? `
	err = o.Exec(sql, dt).Error
	return
}

// BatchDeleteEdbData 批量删除指标数据
func BatchDeleteEdbData(tradeCode string, opUserId int) (err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	var list []*Edbdata
	sql := ` select * FROM edbdata WHERE TRADE_CODE =  ? `
	err = o.Raw(sql, tradeCode).Find(&list).Error
	if err != nil {
		return
	}
	deleteRecordList := make([]*EdbdataDeleteRecord, 0)
	for _, edbDataInfo := range list {
		deleteRecord := &EdbdataDeleteRecord{
			TradeCode:  edbDataInfo.TradeCode,
			Dt:         edbDataInfo.Dt,
			Close:      edbDataInfo.Close,
			ModifyTime: time.Now(),
			CreateTime: time.Now(),
			SysUserId:  opUserId,
		}
		deleteRecordList = append(deleteRecordList, deleteRecord)
	}
	err = o.CreateInBatches(deleteRecordList, utils.MultiAddNum).Error
	if err != nil {
		return
	}
	sql = ` DELETE FROM edbdata WHERE TRADE_CODE  = ? `
	err = o.Exec(sql, tradeCode).Error
	return
}

// GetEdbInfoCountByClassifyId 根据指标分类id获取当前分类下的指标数量
func GetEdbInfoCountByClassifyId(classifyId int) (count int, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `SELECT COUNT(1) AS count FROM ( SELECT a.*,b.CLOSE FROM edbinfo AS a 
             INNER JOIN edbdata AS b ON a.TRADE_CODE=b.TRADE_CODE
             WHERE a.classify_id=? group by a.TRADE_CODE) d `
	var countNull sql2.NullInt64
	err = o.Raw(sql, classifyId).Scan(&countNull).Error
	if err != nil {
		return
	}
	if countNull.Valid {
		count = int(countNull.Int64)
	}
	return
}

// EdbInfoGroupCount 指标分类id获取当前分类下的指标数量
type EdbInfoGroupCount struct {
	Count      int
	ClassifyId int
}

// GetEdbInfoGroupCountByClassifyIds 根据指标分类id获取当前分类下的指标数量
//func GetEdbInfoGroupCountByClassifyIds(classifyIdList []int) (list []*EdbInfoGroupCount, err error) {
//	num := len(classifyIdList)
//	if num <= 0 {
//		return
//	}
//	o := global.DbMap[utils.DbNameManualIndex]
//	sql := `SELECT COUNT(1) AS count,classify_id FROM ( SELECT a.*,b.CLOSE FROM edbinfo AS a
//             INNER JOIN edbdata AS b ON a.TRADE_CODE=b.TRADE_CODE
//             WHERE a.classify_id in (` + utils.GetOrmInReplace(num) + `) group by a.TRADE_CODE) d
//						 GROUP BY classify_id `
//	err = o.Raw(sql, classifyIdList).Find(&list).Error
//	return
//}

// GetExcelData 获取excel样式数据
//func GetExcelData() (list []*data_manage.ExcelStyle, err error) {
//	o := global.DbMap[utils.DbNameManualIndex]
//	sql := `SELECT * FROM excel_style `
//	err = o.Raw(sql).QueryRows(&list)
//	return
//}

// AddExcelData 添加excel样式数据
//func AddExcelData(item *data_manage.ExcelStyle) (id int64, err error) {
//	o := global.DbMap[utils.DbNameManualIndex]
//	id, err = o.Insert(item)
//	return
//}

type EdbdataFloat struct {
	TradeCode  string    `orm:"column(TRADE_CODE);pk" description:"指标编码"`
	Dt         string    `orm:"column(DT)" description:"日期"`
	Close      float64   `orm:"column(CLOSE)" description:"值"`
	ModifyTime time.Time `orm:"column(modify_time)" description:"修改时间"`
}

//func GetTargetsDataFloat(tradeCode, dt string) (item *EdbdataFloat, err error) {
//	sql := `SELECT * FROM edbdata WHERE TRADE_CODE=? AND DT=? `
//	o := global.DbMap[utils.DbNameManualIndex]
//	err = o.Raw(sql, tradeCode, dt).First(&item).Error
//	return
//}

func ModifyEdbinfo(tradeCode, unit, frequency string, classifyId int) (err error) {
	sql := `UPDATE edbinfo SET UNIT = ?,frequency=?, classify_id=?, create_date=NOW() WHERE TRADE_CODE=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Exec(sql, unit, frequency, classifyId, tradeCode).Error
	return
}

func DeleteTargetsDataByImport(tradeCode, dt string) (err error) {
	sql := `DELETE FROM edbdata WHERE TRADE_CODE=? AND DT=? `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Exec(sql, tradeCode, dt).Error
	return
}

// GetEdbinfoListByCodeListGroupByUserId 根据指标code列表、用户分组获取指标信息
//func GetEdbinfoListByCodeListGroupByUserId(edbCodeList []string) (items []*Edbinfo, err error) {
//	num := len(edbCodeList)
//	if num <= 0 {
//		return
//	}
//	o := global.DbMap[utils.DbNameManualIndex]
//	sql := `SELECT * FROM edbinfo WHERE TRADE_CODE in (` + utils.GetOrmInReplace(num) + `) GROUP BY user_id `
//	err = o.Raw(sql, edbCodeList).Find(&items).Error
//	return
//}

// GetEdbinfoListByCodeListByCodeIdList
// @Description: 根据指标code列表获取列表信息
// @param edbCodeList
// @return items
// @return err
func GetEdbinfoListByCodeListByCodeIdList(edbCodeList []string) (items []*Edbinfo, err error) {
	num := len(edbCodeList)
	if num <= 0 {
		return
	}
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `SELECT * FROM edbinfo WHERE TRADE_CODE in (` + utils.GetOrmInReplace(num) + `) `
	err = o.Raw(sql, edbCodeList).Find(&items).Error
	return
}

// GetEdbinfoListByCodeListByUserId
// @Description: 根据用户id列表获取指标列表信息
// @param userIdList
// @return items
// @return err
func GetEdbinfoListByCodeListByUserId(userIdList []int) (items []*Edbinfo, err error) {
	num := len(userIdList)
	if num <= 0 {
		return
	}
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `SELECT * FROM edbinfo WHERE user_id in (` + utils.GetOrmInReplace(num) + `) `
	err = o.Raw(sql, userIdList).Find(&items).Error
	return
}

// ModifyEdbinfoUserIdByCodeList 根据指标code列表修改创建人
func ModifyEdbinfoUserIdByCodeList(edbCodeList []string, userId int, userName string) (err error) {
	num := len(edbCodeList)
	if num <= 0 {
		return
	}
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `UPDATE edbinfo SET user_id = ?,user_name = ? WHERE TRADE_CODE in (` + utils.GetOrmInReplace(num) + `) `
	err = o.Exec(sql, userId, userName, edbCodeList).Error
	return
}

// ModifyEdbinfoUserIdByOldUserId
// @Description:  根据旧用户id修改新用户id
// @author: Roc
// @datetime 2024-03-25 17:59:32
// @param oldUserId int
// @param userId int
// @return err error
func ModifyEdbinfoUserIdByOldUserId(oldUserIdList []int, userId int) (err error) {
	num := len(oldUserIdList)
	if num <= 0 {
		return
	}
	o := global.DbMap[utils.DbNameManualIndex]
	sql := `UPDATE edbinfo SET user_id=? WHERE user_id in (` + utils.GetOrmInReplace(num) + `) `
	err = o.Exec(sql, userId, oldUserIdList).Error
	return
}

func GetEdbInfoAdminList() (list []int, err error) {
	sql := `SELECT user_id FROM edbinfo GROUP BY user_id `
	o := global.DbMap[utils.DbNameManualIndex]
	err = o.Raw(sql).Find(&list).Error
	return
}

// GetAllChildManualEdbClassify
// @Description: 获取手工数据中所有的子分类
// @author: Roc
// @datetime 2024-07-16 13:27:28
// @return items []*EdbdataClassify
// @return err error
func GetAllChildManualEdbClassify() (items []*EdbdataClassify, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` SELECT classify_id,classify_name,parent_id FROM edbdata_classify WHERE parent_id > 0 `
	err = o.Raw(sql).Find(&items).Error
	return
}

// GetChildManualEdbClassifyByIdList
// @Description: 获取手工数据中所有的子分类
// @author: Roc
// @datetime 2024-07-16 13:33:57
// @param idList []int
// @return items []*EdbdataClassify
// @return err error
func GetChildManualEdbClassifyByIdList(idList []int) (items []*EdbdataClassify, err error) {
	num := len(idList)
	if num <= 0 {
		return
	}
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` SELECT classify_id,classify_name,parent_id FROM edbdata_classify WHERE classify_id in (` + utils.GetOrmInReplace(num) + `) `
	err = o.Raw(sql, idList).Find(&items).Error
	return
}

// EdbinfoMaxMinDate
// @Description: 手工指标的最小最大日期
type EdbinfoMaxMinDate struct {
	MinDate string
	MaxDate string
}

// GetEdbdataMaxMinDate
// @Description: 获取手工指标的最小最大日期
// @author: Roc
// @datetime 2024-08-01 14:27:20
// @param tradeCode string
// @return item EdbinfoMaxMinDate
// @return err error
func GetEdbdataMaxMinDate(tradeCode string) (item EdbinfoMaxMinDate, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` SELECT MIN(DT) min_date,MAX(DT) max_date FROM edbdata WHERE TRADE_CODE = ? `
	err = o.Raw(sql, tradeCode).First(&item).Error
	return
}

// GetEdbdataLatestValue
// @Description: 获取手工数据的最新值
// @author: Roc
// @datetime 2024-08-02 10:33:22
// @param tradeCode string
// @return latestValue float64
// @return err error
func GetEdbdataLatestValue(tradeCode string) (latestValue float64, err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` SELECT CLOSE FROM edbdata WHERE TRADE_CODE = ? ORDER BY DT DESC LIMIT 1`
	//err = o.Raw(sql, tradeCode).QueryRow(&latestValue)
	var valueNull sql2.NullFloat64
	err = o.Raw(sql, tradeCode).Scan(&valueNull).Error
	if err != nil {
		return
	}
	if valueNull.Valid {
		latestValue = valueNull.Float64
	}
	return
}

// ModifyEdbinfoMaxMinDate
// @Description: 修改手工指标的最小最大日期
// @author: Roc
// @datetime 2024-08-01 15:33:45
// @param startDate string
// @param endDate string
// @param tradeCode string
// @return err error
func ModifyEdbinfoMaxMinDate(tradeCode, startDate, endDate string, latestValue float64) (err error) {
	o := global.DbMap[utils.DbNameManualIndex]
	sql := ` UPDATE edbinfo SET start_date = ?, end_date = ?, latest_value = ? , modify_time = now() WHERE TRADE_CODE = ? `
	err = o.Exec(sql, startDate, endDate, latestValue, tradeCode).Error
	return
}