package models
import (
sql2 "database/sql"
"eta/eta_api/global"
"eta/eta_api/utils"
"fmt"
"gorm.io/gorm"
"sort"
"strconv"
"strings"
"time"
"github.com/rdlucklib/rdluck_tools/paging"
)
type DataList struct {
TradeCode string `gorm:"column:TRADE_CODE" description:"指标编码"`
SecName string `gorm:"column:SEC_NAME" description:"指标名称"`
Unit string `gorm:"column:UNIT" description:"单位"`
Remark string `gorm:"column(REMARK)" description:"备注"`
Frequency string `description:"频度"`
ClassifyId int `description:"分类id"`
ClassifyName string `description:"分类名称"`
Dt string `gorm:"column:DT" description:"录入日期"`
Close float64 `gorm:"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 `gorm:"column:TRADE_CODE;pk" description:"指标编码"`
Dt string `gorm:"column:DT" description:"日期"`
Close string `gorm:"column:CLOSE" description:"值"`
ModifyTime time.Time `gorm:"column:modify_time" description:"修改时间"`
}
// AfterFind 在该模型上设置钩子函数，把日期转成正确的string，所以查询函数只能用Find函数，First或者Scan是不会触发该函数的来获取数据
func (m *Edbdata) AfterFind(db *gorm.DB) (err error) {
m.Dt = utils.GormDateStrToDateStr(m.Dt)
return
}
type EdbDataNextDateTime struct {
TradeCode string `gorm:"column:TRADE_CODE;pk" description:"指标编码"`
Dt string `gorm:"column:DT" description:"日期"`
Close string `gorm:"column:CLOSE" description:"值"`
ModifyTime time.Time `gorm:"column:modify_time" description:"修改时间"`
NextDateTime string `description:"下期日期"`
}
// AfterFind 在该模型上设置钩子函数，把日期转成正确的string，所以查询函数只能用Find函数，First或者Scan是不会触发该函数的来获取数据
func (m *EdbDataNextDateTime) AfterFind(db *gorm.DB) (err error) {
m.Dt = utils.GormDateStrToDateStr(m.Dt)
m.NextDateTime = utils.GormDateStrToDateStr(m.NextDateTime)
return
}
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 `gorm:"column:TRADE_CODE" description:"指标编码"`
Dt string `gorm:"column:DT" description:"日期"`
Close string `gorm:"column:CLOSE" description:"值"`
ModifyTime time.Time `gorm:"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 `gorm:"column:TRADE_CODE;autoIncrement:false;primaryKey" description:"指标code"`
SecName string `gorm:"column:SEC_NAME;" description:"指标名称"`
Unit string `gorm:"column:UNIT;" description:"单位"`
Remark string `gorm:"column:REMARK;" description:"备注"`
Frequency string `gorm:"column:frequency" description:"频度"`
ClassifyId int `gorm:"column:classify_id" description:"分类id"`
ClassifyName string `gorm:"-" description:"分类名称"`
CreateDate string `gorm:"column:create_date" description:"创建时间"`
UserId int `gorm:"column:user_id" description:"录入用户id"`
UserName string `gorm:"column:user_name" description:"录入用户名称"`
NoticeTime string `gorm:"column:notice_time" description:"通知时间"`
Mobile string `gorm:"column:mobile" description:"录入者手机号"`
Sort int `gorm:"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 `gorm:"column:TRADE_CODE;primaryKey;autoIncrement:false;" orm:"column(TRADE_CODE);pk" description:"指标code"`
SecName string `gorm:"column:SEC_NAME" orm:"column(SEC_NAME);" description:"指标名称"`
Unit string `gorm:"column:UNIT" orm:"column(UNIT);" description:"单位"`
Remark string `gorm:"column:REMARK" 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 ?,? `
pars = append(pars, startSize, pageSize)
err = o.Raw(sql, utils.ForwardPars(pars, mobile)...).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 ?,? `
pars = append(pars, startSize, pageSize)
err = o.Raw(sql, pars...).Find(&items).Error
}
return
}
// EdbParamsInfo 指标数据结构体
type EdbParamsInfo struct {
Unit string `description:"单位" gorm:"column:UNIT"`
Frequency string `gorm:"column:frequency;" description:"单位"`
}
// GetEdbUnitList 获取指标单位
func GetEdbUnitList() (items []*EdbParamsInfo, err error) {
o := global.DbMap[utils.DbNameManualIndex]
sql := `SELECT UNIT,Frequency 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 `gorm:"-"`
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 `gorm:"column:manual_user_classify_id;primaryKey"`
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 `gorm:"column:TRADE_CODE" description:"指标code"`
SecName string `gorm:"column:SEC_NAME" description:"指标名称"`
Unit string `gorm:"column:UNIT" description:"单位"`
Frequency string `description:"频度"`
ClassifyId int `description:"分类id"`
NoticeTime string `description:"通知时间"`
ClassifyName string
Dt string `gorm:"column:DT" description:"日期"`
Close float64 `gorm:"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
sql = utils.ReplaceDriverKeywords("", sql)
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 + `)`
}
sql = utils.ReplaceDriverKeywords("", sql)
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 `gorm:"column:TRADE_CODE;autoIncrement:false;primaryKey" description:"指标编码"`
SecName string `gorm:"column:SEC_NAME;" description:"指标名称"`
Unit string `gorm:"column:UNIT;" description:"单位"`
Remark string `gorm:"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 `
//}
//` ORDER BY CONVERT(a.SEC_NAME USING gbk ) COLLATE gbk_chinese_ci ASC `
sql = sql + fmt.Sprintf(` ORDER BY %s COLLATE gbk_chinese_ci ASC `, utils.GenerateQuerySql(utils.ConvertColumn, &utils.QueryParam{
ConvertColumn: "a.SEC_NAME",
}))
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 `gorm:"column:TRADE_CODE;" description:"指标code"`
SecName string `gorm:"column:SEC_NAME;" description:"指标名称"`
Unit string `gorm:"column:UNIT;" description:"单位"`
Remark string `gorm:"column:REMARK;" description:"备注"`
Frequency string `description:"频度"`
ClassifyId int `description:"分类id"`
ClassifyName string `description:"分类名称"`
CreateDate string `description:"创建时间"`
Dt string `gorm:"column:DT;" description:"最新一次录入时间"`
}
// AfterFind 在该模型上设置钩子函数，把日期转成正确的string，所以查询函数只能用Find函数，First或者Scan是不会触发该函数的来获取数据
func (m *EdbdataExportList) AfterFind(db *gorm.DB) (err error) {
m.Dt = utils.GormDateStrToDateStr(m.Dt)
return
}
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 `gorm:"column:DT;" description:"录入时间"`
}
// AfterFind 在该模型上设置钩子函数，把日期转成正确的string，所以查询函数只能用Find函数，First或者Scan是不会触发该函数的来获取数据
func (m *EdbdataList) AfterFind(db *gorm.DB) (err error) {
m.Dt = utils.GormDateStrToDateStr(m.Dt)
return
}
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 `gorm:"column:TRADE_CODE;" description:"指标code"`
Dt string `gorm:"column:DT;" description:"最新一次录入时间"`
Close float64 `gorm:"column:CLOSE;" description:"值"`
}
// AfterFind 在该模型上设置钩子函数，把日期转成正确的string，所以查询函数只能用Find函数，First或者Scan是不会触发该函数的来获取数据
func (m *EdbdataItem) AfterFind(db *gorm.DB) (err error) {
m.Dt = utils.GormDateStrToDateStr(m.Dt)
return
}
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 `gorm:"column:longzhongpriceinfo_id;primaryKey"`
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 `gorm:"column:longzhongpricedata_id;primaryKey"`
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 `gorm:"column:survey_product_id;primaryKey"`
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 (obj *LongzhongSurveyProduct) AfterFind(tx *gorm.DB) (err error) {
obj.StartDate = utils.GormDateStrToDateStr(obj.StartDate)
obj.EndDate = utils.GormDateStrToDateStr(obj.EndDate)
return
}
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 `gorm:"column:TRADE_CODE;autoIncrement:false;primaryKey" description:"指标编码"`
SecName string `gorm:"column:SEC_NAME;" description:"指标名称"`
Unit string `gorm:"column:UNIT;" description:"单位"`
Remark string `gorm:"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 `gorm:"-" description:"指标数据列表"`
NextDataTimeList []*EdbDataNextDateTime `gorm:"-" description:"下期数据时间列表"`
}
// AfterFind 在该模型上设置钩子函数，把日期转成正确的string，所以查询函数只能用Find函数，First或者Scan是不会触发该函数的来获取数据
func (m *EdbInfoItem) AfterFind(db *gorm.DB) (err error) {
m.CreateDate = utils.GormDateStrToDateTimeStr(m.CreateDate)
m.ModifyTime = utils.GormDateStrToDateTimeStr(m.ModifyTime)
m.StartDate = utils.GormDateStrToDateStr(m.StartDate)
m.EndDate = utils.GormDateStrToDateStr(m.EndDate)
return
}
// ConvDate
func (m *EdbInfoItem) ConvDate() {
m.CreateDate = utils.GormDateStrToDateTimeStr(m.CreateDate)
m.ModifyTime = utils.GormDateStrToDateTimeStr(m.ModifyTime)
m.StartDate = utils.GormDateStrToDateStr(m.StartDate)
m.EndDate = utils.GormDateStrToDateStr(m.EndDate)
return
}
// 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 `
sql = sql + fmt.Sprintf(` ORDER BY %s COLLATE gbk_chinese_ci ASC `, utils.GenerateQuerySql(utils.ConvertColumn, &utils.QueryParam{ConvertColumn: "a.SEC_NAME"}))
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 `gorm:"column:data_time" description:"日期"`
InputValue string `gorm:"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 `gorm:"column:TRADE_CODE;autoIncrement:false;primaryKey" description:"指标编码"`
Dt string `gorm:"column:DT" description:"日期"`
Close float64 `gorm:"column:CLOSE" description:"值"`
ModifyTime time.Time `gorm:"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
}
func (mmDate *EdbinfoMaxMinDate) AfterFind(tx *gorm.DB) (err error) {
mmDate.MinDate = utils.GormDateStrToDateStr(mmDate.MinDate)
mmDate.MaxDate = utils.GormDateStrToDateStr(mmDate.MaxDate)
return
}
// 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
}