edb_data_base.go 8.9 KB

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