edb_data_base.go 9.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275
  1. package models
  2. import (
  3. "eta/eta_index_lib/global"
  4. "eta/eta_index_lib/utils"
  5. "fmt"
  6. "gorm.io/gorm"
  7. "strings"
  8. "time"
  9. )
  10. // 指标检索数据
  11. type EdbDataItem struct {
  12. EdbCode string `description:"指标编码"`
  13. StartDate string `description:"起始日期"`
  14. EndDate string `description:"终止日期"`
  15. EdbName string `description:"指标名称"`
  16. Unit string `description:"单位"`
  17. Frequency string `description:"频率"`
  18. DataList []*EdbData
  19. }
  20. type EdbData struct {
  21. EdbDataId int `gorm:"primaryKey;autoIncrement;column:edb_data_id"`
  22. EdbInfoId int `gorm:"column:edb_info_id"`
  23. EdbCode string `gorm:"column:edb_code"`
  24. DataTime string `gorm:"column:data_time"`
  25. Value string `gorm:"column:value"`
  26. Status int `gorm:"column:status"`
  27. CreateTime time.Time `gorm:"column:create_time"`
  28. ModifyTime time.Time `gorm:"column:modify_time"`
  29. DataTimestamp int64 `gorm:"column:data_timestamp"`
  30. }
  31. // AfterFind 在该模型上设置钩子函数,把日期转成正确的string,所以查询函数只能用Find函数,First或者Scan是不会触发该函数的来获取数据
  32. func (m *EdbData) AfterFind(db *gorm.DB) (err error) {
  33. if m.DataTime == "" {
  34. return
  35. }
  36. minDateTmp, err := time.ParseInLocation(utils.FormatDateWallWithLoc, m.DataTime, time.Local)
  37. if err != nil {
  38. return
  39. }
  40. m.DataTime = minDateTmp.Format(utils.FormatDate)
  41. return
  42. }
  43. func GetAddSql(edbInfoId, edbCode, dataTime, timestampStr string, value string) (addSql string) {
  44. nowStr := time.Now().Format(utils.FormatDateTime)
  45. addSql += "("
  46. addSql += edbInfoId + "," + "'" + edbCode + "'" + "," + "'" + dataTime + "'" + "," + value + "," + "'" + nowStr + "'" +
  47. "," + "'" + nowStr + "'"
  48. addSql += "," + "'" + timestampStr + "'"
  49. addSql += "),"
  50. return
  51. }
  52. func GetAddSqlV2(edbInfoId, edbCode, dataTime, timestampStr string, value, nowStr string) (addSql string) {
  53. addSql += "("
  54. addSql += edbInfoId + "," + "'" + edbCode + "'" + "," + "'" + dataTime + "'" + "," + value + "," + "'" + nowStr + "'" +
  55. "," + "'" + nowStr + "'"
  56. addSql += "," + "'" + timestampStr + "'"
  57. addSql += "),"
  58. return
  59. }
  60. type AddEdbInfoReq struct {
  61. EdbCode string `description:"指标编码"`
  62. Source int `description:"指标来源ID"`
  63. StockCode string `description:"证券代码"`
  64. Frequency string `description:"频度"`
  65. ExtraPars string `description:"额外参数(如同花顺日期序列)"`
  66. }
  67. // GetEdbInfoCountByCondition 获取指标数量
  68. func GetEdbInfoCountByCondition(condition string, pars []interface{}) (count int, err error) {
  69. sql := ` SELECT COUNT(1) AS count FROM edb_info WHERE 1=1 `
  70. if condition != "" {
  71. sql += condition
  72. }
  73. err = global.DEFAULT_DB.Raw(sql, pars...).Scan(&count).Error
  74. return
  75. }
  76. // GetEdbDataAllByEdbCode 根据指标编码获取指标数据列表
  77. func GetEdbDataAllByEdbCode(edbCode string, source, subSource, limit int) (items []*EdbInfoSearchData, err error) {
  78. var pars []interface{}
  79. pars = append(pars, edbCode)
  80. tableName := GetEdbDataTableName(source, subSource)
  81. sql := ` SELECT * FROM %s WHERE edb_code=? ORDER BY data_time DESC`
  82. if limit > 0 {
  83. sql += ` LIMIT ? `
  84. pars = append(pars, limit)
  85. }
  86. sql = fmt.Sprintf(sql, tableName)
  87. err = global.DEFAULT_DB.Raw(sql, pars...).Scan(&items).Error
  88. return
  89. }
  90. func GetEdbDataByCondition(source, subSource int, condition string, pars []interface{}) (items []*EdbInfoSearchData, err error) {
  91. tableName := GetEdbDataTableName(source, subSource)
  92. sql := ` SELECT * FROM %s WHERE 1=1 `
  93. if condition != "" {
  94. sql += condition
  95. }
  96. sql += ` ORDER BY data_time DESC `
  97. sql = fmt.Sprintf(sql, tableName)
  98. err = global.DEFAULT_DB.Raw(sql, pars...).Find(&items).Error
  99. if err != nil {
  100. return
  101. }
  102. for _, v := range items {
  103. v.ConvertTimeStr()
  104. }
  105. return
  106. }
  107. // GetEdbDataByDate 根据数据日期获取指标数据
  108. func GetEdbDataByDate(source, subSource int, edbCode, dataTime string) (item *EdbInfoSearchData, err error) {
  109. tableName := GetEdbDataTableName(source, subSource)
  110. sql := ` SELECT * FROM %s WHERE 1=1 AND edb_code = ? AND data_time =? ORDER BY data_time DESC `
  111. sql = fmt.Sprintf(sql, tableName)
  112. err = global.DEFAULT_DB.Raw(sql, edbCode, dataTime).First(&item).Error
  113. return
  114. }
  115. func ModifyEdbDataById(source, subSource, edbDataId int, value string) (err error) {
  116. tableName := GetEdbDataTableName(source, subSource)
  117. sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_data_id=? `
  118. sql = fmt.Sprintf(sql, tableName)
  119. err = global.DEFAULT_DB.Exec(sql, value, edbDataId).Error
  120. return
  121. }
  122. func DeleteEdbDataById(source, subSource, edbDataId int) (err error) {
  123. sql := ` DELETE FROM %s WHERE edb_data_id=? `
  124. tableName := GetEdbDataTableName(source, subSource)
  125. sql = fmt.Sprintf(sql, tableName)
  126. err = global.DEFAULT_DB.Exec(sql, edbDataId).Error
  127. return
  128. }
  129. type RefreshEdbInfoReq struct {
  130. EdbInfoId int `description:"指标ID"`
  131. EdbCode string `description:"指标编码"`
  132. StartDate string `description:"开始日期"`
  133. Source int `description:"指标来源ID"`
  134. }
  135. // GetAllEdbDataListByTo 获取所有的指标数据列表
  136. func GetAllEdbDataListByTo(to *gorm.DB, edbInfoId, source, subSource int) (existDataList []*EdbData, err error) {
  137. dataTableName := GetEdbDataTableName(source, subSource)
  138. sql := `SELECT * FROM %s WHERE edb_info_id=? order by data_time asc`
  139. sql = fmt.Sprintf(sql, dataTableName)
  140. err = to.Raw(sql, edbInfoId).Find(&existDataList).Error
  141. return
  142. }
  143. // GetFinalLastByTo 获取所有的指标数据列表
  144. func GetFinalLastByTo(to *gorm.DB, edbInfoId, source, subSource int, latestDate string) (finalLast EdbInfoSearchData, err error) {
  145. dataTableName := GetEdbDataTableName(source, subSource)
  146. sql := fmt.Sprintf(` SELECT data_time , value FROM %s WHERE edb_info_id=? and data_time<=? ORDER BY data_time DESC `, dataTableName)
  147. err = to.Raw(sql, edbInfoId, latestDate).First(&finalLast).Error
  148. if err != nil && err.Error() != utils.ErrNoRow() {
  149. return
  150. }
  151. return
  152. }
  153. // 新版本
  154. type EdbDataV1 struct {
  155. EdbDataId int `gorm:"primaryKey;autoIncrement;column:edb_data_id"`
  156. EdbInfoId int `gorm:"column:edb_info_id"`
  157. EdbCode string `gorm:"column:edb_code"`
  158. DataTime string `gorm:"column:data_time"`
  159. Value string `gorm:"column:value"`
  160. Status int `gorm:"column:status"`
  161. CreateTime time.Time `gorm:"column:create_time"`
  162. ModifyTime time.Time `gorm:"column:modify_time"`
  163. DataTimestamp int64 `gorm:"column:data_timestamp"`
  164. }
  165. type EdbDataList struct {
  166. EdbDataId int `description:" 指标数据ID"`
  167. EdbInfoId int `description:"指标ID"`
  168. DataTime string //`json:"-" description:"数据日期"`
  169. DataTimestamp int64 `description:"数据日期"`
  170. Value float64 `description:"数据值"`
  171. }
  172. func DelEdbDataByMysql(to *gorm.DB, edbInfoId int, tableName string, removeDateList []string) (err error) {
  173. if len(removeDateList) <= 0 {
  174. return
  175. }
  176. // 移除不存在的日期数据
  177. removeDateStr := strings.Join(removeDateList, `','`)
  178. removeDateStr = `'` + removeDateStr + `'`
  179. sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? and data_time in (%s) `, tableName, removeDateStr)
  180. err = to.Exec(sql, edbInfoId).Error
  181. return
  182. }
  183. // GetEdbDataList
  184. // @Description:
  185. // @author: Roc
  186. // @datetime 2024-05-08 16:10:49
  187. // @param source int
  188. // @param subSource int
  189. // @param endInfoId int
  190. // @param startDate string
  191. // @param endDate string
  192. // @return list []*EdbDataList
  193. // @return err error
  194. func GetEdbDataList(source, subSource, endInfoId int, startDate, endDate string) (list []*EdbDataList, err error) {
  195. list = make([]*EdbDataList, 0)
  196. tmpDataList, err := GetEdbDataListAll(source, subSource, FindEdbDataListAllCond{
  197. EdbInfoId: endInfoId,
  198. StartDataTime: startDate,
  199. StartDataTimeCond: ">=",
  200. EndDataTime: endDate,
  201. EndDataTimeCond: "<=",
  202. }, 1)
  203. if err != nil {
  204. return
  205. }
  206. // 数据拼接
  207. for _, v := range tmpDataList {
  208. list = append(list, &EdbDataList{
  209. EdbDataId: v.EdbDataId,
  210. EdbInfoId: v.EdbInfoId,
  211. DataTime: v.DataTime,
  212. DataTimestamp: v.DataTimestamp,
  213. Value: v.Value,
  214. })
  215. }
  216. return
  217. }
  218. type AddEdbBaseInfoReq struct {
  219. EdbCode string `description:"指标编码"`
  220. EdbName string `description:"指标名称"`
  221. Unit string `description:"单位"`
  222. ClassifyId int `description:"所属分类"`
  223. SysUserId int `description:"用户id"`
  224. SysUserRealName string `description:"用户真实名称"`
  225. }
  226. // EditEdbBaseInfoReq 编辑基础指标请求参数
  227. type EditEdbBaseInfoReq struct {
  228. SysUserId int `description:"用户id"`
  229. SysUserRealName string `description:"用户真实名称"`
  230. EdbInfoId int `description:"指标id"`
  231. EdbName string `description:"指标名称"`
  232. EdbNameEn string `description:"英文指标名称"`
  233. Frequency string `description:"频率"`
  234. Unit string `description:"单位"`
  235. UnitEn string `description:"英文单位"`
  236. ClassifyId int `description:"分类id"`
  237. }
  238. // ConvertDate
  239. // @Description: 格式化EdbData的日期
  240. // @receiver e
  241. func (m *EdbData) ConvertDate() {
  242. m.DataTime = utils.GormDateStrToDateStr(m.DataTime)
  243. return
  244. }