package models import ( "eta/eta_mobile/models/data_manage" "eta/eta_mobile/utils" "fmt" "github.com/beego/beego/v2/client/orm" "github.com/rdlucklib/rdluck_tools/paging" "sort" "strconv" "strings" "time" ) 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 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 } 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 `description:"频度"` ClassifyId int `description:"分类id"` ClassifyName string `description:"分类名称"` CreateDate string `description:"创建时间"` UserId int `description:"录入用户id"` UserName string `description:"录入用户名称"` NoticeTime string `description:"通知时间"` Mobile string `description:"录入者手机号"` } 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 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 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 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:"状态：未完成/完成"` 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 } // 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 }