edb_data_base.go 9.0 KB

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