eta_api/models/target.go

package models

import (
	"context"
	"eta/eta_api/models/data_manage"
	"eta/eta_api/utils"
	"fmt"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/beego/beego/v2/client/orm"
	"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 left(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
	if condition != "" {
		sql += condition
	}
	sql += ` order by c.DT desc limit ?,? `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	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 left(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0 `
	if condition != "" {
		sql += condition
	}
	o := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, pars).QueryRow(&count)
	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:"修改时间"`
}

func MultiAddEdbdata(edbDataList []*Edbdata) (err error) {
	if len(edbDataList) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("edb")
	err = o.DoTx(func(ctx context.Context, txOrm orm.TxOrmer) error {
		holder := make([]string, 0)
		tradeCodeList := make([]string, 0)
		for _, v := range edbDataList {
			holder = append(holder, "?")
			tradeCodeList = append(tradeCodeList, v.TradeCode)
		}
		sql := "DELETE FROM edbdata WHERE TRADE_CODE in (" + strings.Join(holder, ",") + ")"
		_, err := txOrm.Raw(sql, tradeCodeList).Exec()
		if err != nil {
			return err
		}
		_, err = txOrm.InsertMulti(500, edbDataList)
		if err != nil {
			return err
		}
		return nil
	})
	return
}

func GetDataInfo(tradeCode, creteDate string) (item *Edbdata, err error) {
	sql := " SELECT * FROM edbdata WHERE TRADE_CODE=? AND DT=? "
	o := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, tradeCode, creteDate).QueryRow(&item)
	return
}

func AddEdbdata(item *Edbdata) (lastId int64, err error) {
	o := orm.NewOrmUsingDB("edb")
	lastId, err = o.Insert(item)
	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:"需要修改的数据"`
}

// 编辑数据
func EditEdbdata(item *Edbdata) (err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := ` UPDATE edbdata SET CLOSE = ?,modify_time=NOW() WHERE TRADE_CODE = ? AND DT = ? `
	_, err = o.Raw(sql, item.Close, item.TradeCode, item.Dt).Exec()
	return
}

type EdbdataDeleteRecord struct {
	Id         int       `orm:"column(id);pk"`
	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 := orm.NewOrmUsingDB("edb")
	lastId, err = o.Insert(item)
	return
}

// DeleteEdbData 根据指标code和日期删除数据
func DeleteEdbData(tradeCode, dt string) (err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := ` DELETE FROM edbdata WHERE TRADE_CODE = ? AND DT = ? `
	_, err = o.Raw(sql, tradeCode, dt).Exec()
	return
}

// DeleteAllEdbData 根据指标code删除数据
func DeleteAllEdbData(tradeCode string) (err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := ` DELETE FROM edbdata WHERE TRADE_CODE = ? `
	_, err = o.Raw(sql, tradeCode).Exec()
	return
}

// DeleteAllEdbDataByTradeCodeList 根据指标code删除数据
func DeleteAllEdbDataByTradeCodeList(tradeCodeList []string) (err error) {
	if len(tradeCodeList) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("edb")
	sql := ` DELETE FROM edbdata WHERE TRADE_CODE in (` + strings.Join(tradeCodeList, ",") + `) `
	_, err = o.Raw(sql).Exec()
	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:"-" description:"录入用户名称"`
	NoticeTime   string `orm:"column(notice_time)" description:"通知时间"`
	Mobile       string `orm:"column(mobile)" description:"录入者手机号"`
	Sort         int    `orm:"column(sort)" description:"排序"`
}

func DeleteEdbinfoByTraceCodeList(tradeCodeList []string) (err error) {
	if len(tradeCodeList) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("edb")
	err = o.DoTx(func(ctx context.Context, txOrm orm.TxOrmer) error {
		sql := ` DELETE FROM edbdata WHERE TRADE_CODE in (` + strings.Join(tradeCodeList, ",") + `) `
		_, err := txOrm.Raw(sql).Exec()
		if err != nil {
			return err
		}
		sql = ` DELETE FROM edbinfo WHERE TRADE_CODE in (` + strings.Join(tradeCodeList, ",") + `)`
		_, err = txOrm.Raw(sql).Exec()
		if err != nil {
			return err
		}
		return nil
	})
	return
}

func GetEdbinfoListCount(condition string, pars []interface{}, mobile string, roleType int) (count int, err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := ``
	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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`

		if condition != "" {
			sql += condition
		}
		err = o.Raw(sql, mobile, pars).QueryRow(&count)
	} else {
		sql := `SELECT COUNT(1) AS count FROM edbinfo AS a WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`

		if condition != "" {
			sql += condition
		}
		err = o.Raw(sql, pars).QueryRow(&count)
	}
	return
}

func GetEdbinfoList(condition string, pars []interface{}, startSize, pageSize int, mobile string, roleType int) (items []*Edbinfo, err error) {
	o := orm.NewOrmUsingDB("edb")
	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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
		if condition != "" {
			sql += condition
		}
		sql += ` ORDER BY a.create_date DESC LIMIT ?,? `
		_, err = o.Raw(sql, mobile, pars, startSize, pageSize).QueryRows(&items)
	} 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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
		if condition != "" {
			sql += condition
		}
		sql += ` ORDER BY a.create_date DESC LIMIT ?,? `
		_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
	}
	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 := orm.NewOrmUsingDB("edb")
	sql := `SELECT UNIT from edbinfo group by UNIT`
	_, err = o.Raw(sql).QueryRows(&items)
	return
}

// GetEdbFrequencyList 获取指标频度
func GetEdbFrequencyList(classifyId, userId int) (items []*EdbParamsInfo, err error) {
	o := orm.NewOrmUsingDB("edb")
	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).QueryRows(&items)
	return
}

type TargetListResp struct {
	List   []*Edbinfo
	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' AND TRADE_CODE not like '%index%' and TRADE_CODE NOT LIKE 'WDC%'"
	o := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql).QueryRow(&max_trade_code)
	if (err != nil && err.Error() == utils.ErrNoRow()) || max_trade_code == `` {
		max_trade_code = "W00"
	}
	return
}

func GetEdbinfoBySecName(secName string) (item *Edbinfo, err error) {
	sql := `SELECT * FROM edbinfo WHERE SEC_NAME=? AND left(TRADE_CODE,1)='W' AND REMARK='手动' `
	o := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, secName).QueryRow(&item)
	return
}

func GetEdbinfoBySecNameList(secNameList []string) (items []*Edbinfo, err error) {
	if len(secNameList) == 0 {
		return
	}
	sql := `SELECT * FROM edbinfo WHERE  left(TRADE_CODE,1)='W' AND REMARK='手动' `
	if len(secNameList) > 0 {
		var holder []string
		for range secNameList {
			holder = append(holder, "?")
		}

		sql += " AND SEC_NAME IN (" + strings.Join(holder, ",") + ") "
	}
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, secNameList).QueryRows(&items)
	return
}

func GetEdbinfoByTradeCode(tradeCode string) (item *Edbinfo, err error) {
	sql := `SELECT * FROM edbinfo WHERE TRADE_CODE=? `
	o := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, tradeCode).QueryRow(&item)
	return
}

func AddEdbinfo(tradeCode, secName, unit, remark, frequency, noticeTime string, classifyId int, userId int) (err error) {
	sql := `INSERT INTO edbinfo(TRADE_CODE, SEC_NAME,UNIT, REMARK,frequency, classify_id,notice_time,user_id,create_date) 
			VALUES(?,?,?,?,?,?,?,?,now()) `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, tradeCode, secName, unit, remark, frequency, classifyId, noticeTime, userId).Exec()
	return
}

func MultiAddEdbinfoV2(edbInfoList []*Edbinfo) (err error) {
	o := orm.NewOrmUsingDB("edb")
	_, err = o.InsertMulti(len(edbInfoList), edbInfoList)
	return
}

func AddEdbinfoUser(tradeCode, mobile string) (err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := `INSERT INTO edbinfo_user(TRADE_CODE, mobile) VALUES (?,?)`
	_, err = o.Raw(sql, tradeCode, mobile).Exec()
	return
}

type EdbinfoUser struct {
	EdbinfoUserId int    `orm:"pk" description:"主键"`
	TradeCode     string `orm:"column(TRADE_CODE);" description:"指标code"`
	Mobile        string `orm:"column(mobile);" description:"手机号"`
}

func MultiAddEdbinfoUser(edbInfoUserList []*EdbinfoUser) (err error) {
	o := orm.NewOrmUsingDB("edb")
	_, err = o.InsertMulti(len(edbInfoUserList), edbInfoUserList)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, secName, unit, classifyId, frequency, noticeTime, tradeCode).Exec()
	return
}

func SearchTargetEntry(classifyId int, keyWord string) (items []*Edbinfo, err error) {
	where := ""
	pars := make([]interface{}, 0)
	sql := `SELECT * FROM edbinfo WHERE LEFT(TRADE_CODE,1)='W' AND REMARK='手动' AND 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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, pars...).QueryRows(&items)
	return
}

type SearchTargetListResp struct {
	List []*Edbinfo
}

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

func GetEdbdataClassifyByClassifyName(classifyName string) (item *EdbdataClassify, err error) {
	sql := `SELECT * FROM edbdata_classify WHERE classify_name=? `
	o := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, classifyName).QueryRow(&item)
	return
}

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

func GetEdbdataClassify(userId int64) (items []*EdbdataClassifyList, err error) {
	var newItems []*EdbdataClassifyList
	o := orm.NewOrmUsingDB("edb")
	sql := ` SELECT classify_id,classify_name,parent_id FROM edbdata_classify WHERE parent_id=0 `
	_, err = o.Raw(sql).QueryRows(&newItems)
	if err != nil {
		return
	}
	classifyLen := len(newItems)

	for i := 0; i < classifyLen; i++ {
		var childItems []*EdbdataClassify
		parentId := newItems[i].ClassifyId
		childSql := ``
		if userId > 0 {
			userClassifyList, _ := GetManualUserClassify(int(userId))
			var userIdArr []string
			for _, v := range userClassifyList {
				userIdArr = append(userIdArr, strconv.Itoa(v.ClassifyId))
			}

			userIdStr := strings.Join(userIdArr, ",")
			if userIdStr != "" {
				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(" + userIdStr + ") AND parent_id=? ORDER BY a.create_time ASC "
				_, err = o.Raw(childSql, parentId).QueryRows(&childItems)
			}
		} else {
			childSql = "SELECT classify_id,classify_name,parent_id FROM edbdata_classify WHERE is_show=1 and parent_id=? ORDER BY create_time ASC "
			_, err = o.Raw(childSql, parentId).QueryRows(&childItems)
		}
		if err != nil {
			return
		}
		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 := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM manual_user_classify WHERE admin_id=? `
	_, err = o.Raw(sql, sysUserId).QueryRows(&list)
	return
}

type EdbdataClassifyResp struct {
	List []*EdbdataClassifyList
}

func GetTargetBySecName(secName string) (item *Edbinfo, err error) {
	sql := `SELECT * FROM edbinfo WHERE SEC_NAME=? AND left(TRADE_CODE,1)='W' AND REMARK='手动' `
	o := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, secName).QueryRow(&item)
	return
}

// 更新指标数据信息
func (edbinfo *Edbinfo) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Update(edbinfo, cols...)
	return
}

func ModifyTargetClassifyId(tradeCode string, classifyId int) (err error) {
	sql := `UPDATE edbinfo SET classify_id=? WHERE TRADE_CODE=? `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, classifyId, tradeCode).Exec()
	return
}

func GetTargetsDataCount(tradeCode, dt string) (count int, err error) {
	sql := `SELECT COUNT(1) AS count FROM edbdata WHERE TRADE_CODE=? AND DT=? `
	o := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, tradeCode, dt).QueryRow(&count)
	return
}

// GetTargetsDataList 根据code获取指标数据列表
func GetTargetsDataList(tradeCode string) (items []*Edbdata, err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := `SELECT * FROM edbdata WHERE TRADE_CODE=? ORDER BY DT ASC `
	_, err = o.Raw(sql, tradeCode).QueryRows(&items)
	return
}

func GetTargetsData(tradeCode, dt string) (item *Edbdata, err error) {
	sql := `SELECT * FROM edbdata WHERE TRADE_CODE=? AND DT=? `
	o := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, tradeCode, dt).QueryRow(&item)
	return
}

func ModifyTargetsDataByImport(tradeCode, dt, close string) (err error) {
	sql := `UPDATE  edbdata SET CLOSE=?,modify_time=NOW() WHERE  TRADE_CODE=? AND DT=? `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, close, tradeCode, dt).Exec()
	return
}

func AddTargetsDataByImport(tradeCode, dt, close string) (err error) {
	sql := `INSERT INTO edbdata(TRADE_CODE, DT,CLOSE, modify_time)VALUES(?,?,?,NOW()) `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, tradeCode, dt, close).Exec()
	return
}

type EdbdataImportResp struct {
	Status       int
	Msg          string
	SuccessCount int
	FailCount    int
}

func GetFailList(sysUserId int) (items []*EdbdataImportFail, err error) {
	o := orm.NewOrmUsingDB("edb")
	sql := ` SELECT * FROM edbdata_import_fail WHERE sys_user_id=? `
	_, err = o.Raw(sql, sysUserId).QueryRows(&items)
	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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0 `
	if where != "" {
		sql += where
	}
	sql = sql + " ORDER BY c.DT DESC "
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

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

func DataDelete(tradeCode, createDate, close string, modifyTime time.Time, sysUserId int) (err error) {
	o := orm.NewOrmUsingDB("edb")
	to, err := o.Begin()
	if err != nil {
		return
	}
	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.Raw(recordSql, tradeCode, createDate, close, modifyTime, time.Now(), sysUserId).Exec()
	sql := ` DELETE FROM edbdata WHERE TRADE_CODE = ? AND DT = ? `
	_, err = to.Raw(sql, tradeCode, createDate).Exec()
	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 := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, tradeCode).QueryRow(&count)
	return
}

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

func TargetDelete(tradeCode string) (err error) {
	o := orm.NewOrmUsingDB("edb")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

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

	sql = " DELETE FROM edbdata WHERE TRADE_CODE = ? "
	_, err = to.Raw(sql, tradeCode).Exec()

	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 := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)
	researchLen := len(items)
	edbO := orm.NewOrmUsingDB("edb")
	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 LEFT(b.TRADE_CODE,1)='W' AND b.REMARK='手动' AND b.classify_id>0 `
		err = edbO.Raw(sqlCount, mobile).QueryRow(&count)
		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 := orm.NewOrm()
	_, err = o.Raw(sql).QueryRows(&items)
	researchLen := len(items)
	edbO := orm.NewOrmUsingDB("edb")
	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 LEFT(b.TRADE_CODE,1)='W' AND b.REMARK='手动' AND b.classify_id>0 `
		err = edbO.Raw(sqlCount, mobile).QueryRow(&count)
		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 := orm.NewOrmUsingDB("edb")
	//sql := ` SELECT *,'' modify_date,'' status FROM edbinfo AS a WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0 `

	sql := ` SELECT *,'' modify_date,'' STATUS FROM edbinfo AS a 
            WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND 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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' 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).QueryRows(&items)
	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)
				if err != nil {
					return nil, err
				}
				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)
				if err != nil {
					return nil, err
				}
				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)
				if err != nil {
					return nil, err
				}
				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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND 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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
	if condition != "" {
		sql += condition
	}
	sql += " GROUP BY a.TRADE_CODE ORDER BY a.TRADE_CODE ASC "
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, pars).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, classifyId).QueryRows(&items)
	return
}

func GetEdbDataFrequencyByKeyord(keyword string) (items []*string, err error) {
	sql := `SELECT DISTINCT frequency FROM edbinfo where SEC_NAME=? ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年度','年度') `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, keyword).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, tradeCode, dt).QueryRow(&item)
	return
}

func GetEdbdataAllByTradeCode(tradeCode string) (items []*EdbdataItem, err error) {
	sql := ` SELECT * FROM edbdata WHERE TRADE_CODE=? `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, tradeCode).QueryRows(&items)
	return
}

func GetEdbdataClassifyByParentId(parentId int) (items []*EdbdataClassify, err error) {
	sql := ` SELECT * FROM edbdata_classify WHERE parent_id=? `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, parentId).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, productName).QueryRows(&items)
	return
}

func GetLongzhongPriceDataMaxCount(productName string) (count int, err error) {
	o := orm.NewOrmUsingDB("edb")
	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)
	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 := orm.NewOrmUsingDB("edb")
	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).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, productName).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, productName, frequency).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, breedIds).QueryRows(&items)
	return
}

func GetLzFrequency(productName string) (items []*int, err error) {
	sql := `SELECT DISTINCT frequency FROM longzhong_survey_product WHERE breed_name=? ORDER BY frequency`
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, productName).QueryRows(&items)
	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"`
	NoticeTime   string     `description:"通知时间"`
	Mobile       string     `description:"录入者手机号"`
	ModifyDate   string     `description:"待更新日期"`
	Status       string     `description:"状态：未完成/完成"`
	Sort         int        `description:"排序"`
	DataList     []*Edbdata `description:"指标数据列表"`
}

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

	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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND 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).QueryRows(&items)
	return

}

// GetTargetItemList 获取指标列表数据
func GetTargetItemListSort(classifyId, edbShowType int, frequency, keyword, tradeCode string, classifyIdStrList []string) (items []*EdbInfoItem, err error) {
	o := orm.NewOrmUsingDB("edb")

	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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND 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 sort ASC, CONVERT(a.SEC_NAME USING gbk )  COLLATE gbk_chinese_ci ASC `
	_, err = o.Raw(sql, pars).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 ?"
	_, err = o.Raw(sql, utils.GetLikeKeyword(keyword)).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
}

// 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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	var list []*Edbdata
	sql := ` select * FROM edbdata WHERE TRADE_CODE in  (` + tradeCodes + `) AND DT = ? `
	_, err = o.Raw(sql, dt).QueryRows(&list)
	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.InsertMulti(len(deleteRecordList), deleteRecordList)
		if tmpErr != nil {
			err = tmpErr
			return
		}
	}
	sql = ` DELETE FROM edbdata WHERE TRADE_CODE in  (` + tradeCodes + `) AND DT = ? `
	_, err = o.Raw(sql, dt).Exec()
	return
}

// BatchDeleteEdbData 批量删除指标数据
func BatchDeleteEdbData(tradeCode string, opUserId int) (err error) {
	o := orm.NewOrmUsingDB("edb")
	var list []*Edbdata
	sql := ` select * FROM edbdata WHERE TRADE_CODE =  ? `
	_, err = o.Raw(sql, tradeCode).QueryRows(&list)
	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.InsertMulti(len(deleteRecordList), deleteRecordList)
	if err != nil {
		return
	}
	sql = ` DELETE FROM edbdata WHERE TRADE_CODE  = ? `
	_, err = o.Raw(sql, tradeCode).Exec()
	return
}

// GetEdbInfoCountByClassifyId 根据指标分类id获取当前分类下的指标数量
func GetEdbInfoCountByClassifyId(classifyId int) (count int, err error) {
	o := orm.NewOrmUsingDB("edb")
	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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id=? group by a.TRADE_CODE) d `
	err = o.Raw(sql, classifyId).QueryRow(&count)
	return
}

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

// GetEdbInfoGroupCountByClassifyIds 根据指标分类id获取当前分类下的指标数量
func GetEdbInfoGroupCountByClassifyIds(classifyIds string) (list []*EdbInfoGroupCount, err error) {
	o := orm.NewOrmUsingDB("edb")
	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 LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' and a.classify_id in (` + classifyIds + `) group by a.TRADE_CODE) d 
						 GROUP BY classify_id `
	_, err = o.Raw(sql).QueryRows(&list)
	return
}

// GetExcelData 获取excel样式数据
func GetExcelData() (list []*data_manage.ExcelStyle, err error) {
	o := orm.NewOrmUsingDB("edb")
	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 := orm.NewOrmUsingDB("edb")
	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 := orm.NewOrmUsingDB("edb")
	err = o.Raw(sql, tradeCode, dt).QueryRow(&item)
	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 := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, unit, frequency, classifyId, tradeCode).Exec()
	return
}

func DeleteTargetsDataByImport(tradeCode, dt string) (err error) {
	sql := `DELETE FROM edbdata WHERE TRADE_CODE=? AND DT=? `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql, tradeCode, dt).Exec()
	return
}

// GetEdbinfoListByCodeListGroupByUserId 根据指标code列表、用户分组获取指标信息
func GetEdbinfoListByCodeListGroupByUserId(edbCodeList []string) (items []*Edbinfo, err error) {
	num := len(edbCodeList)
	if num <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("edb")
	sql := `SELECT * FROM edbinfo WHERE TRADE_CODE in (` + utils.GetOrmInReplace(num) + `) GROUP BY user_id `
	_, err = o.Raw(sql, edbCodeList).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	sql := `SELECT * FROM edbinfo WHERE TRADE_CODE in (` + utils.GetOrmInReplace(num) + `) `
	_, err = o.Raw(sql, edbCodeList).QueryRows(&items)
	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 := orm.NewOrmUsingDB("edb")
	sql := `SELECT * FROM edbinfo WHERE user_id in (` + utils.GetOrmInReplace(num) + `) `
	_, err = o.Raw(sql, userIdList).QueryRows(&items)
	return
}

// ModifyEdbinfoUserIdByCodeList 根据指标code列表修改创建人
func ModifyEdbinfoUserIdByCodeList(edbCodeList []string, userId int) (err error) {
	num := len(edbCodeList)
	if num <= 0 {
		return
	}
	o := orm.NewOrmUsingDB("edb")
	sql := `UPDATE edbinfo SET user_id=? WHERE TRADE_CODE in (` + utils.GetOrmInReplace(num) + `) `
	_, err = o.Raw(sql, userId, edbCodeList).Exec()
	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 := orm.NewOrmUsingDB("edb")
	sql := `UPDATE edbinfo SET user_id=? WHERE user_id in (` + utils.GetOrmInReplace(num) + `) `
	_, err = o.Raw(sql, userId, oldUserIdList).Exec()

	return
}

func GetEdbInfoAdminList() (list []int, err error) {
	sql := `SELECT user_id FROM edbinfo GROUP BY user_id `
	o := orm.NewOrmUsingDB("edb")
	_, err = o.Raw(sql).QueryRows(&list)
	return
}