edb_data_base.go 6.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203
  1. package models
  2. import (
  3. "errors"
  4. "eta/eta_index_lib/utils"
  5. "fmt"
  6. "github.com/beego/beego/v2/client/orm"
  7. "strconv"
  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 `orm:"column(edb_data_id);pk"`
  22. EdbInfoId int
  23. EdbCode string
  24. DataTime string
  25. Value string
  26. Status int
  27. CreateTime time.Time
  28. ModifyTime time.Time
  29. DataTimestamp int64
  30. }
  31. func GetAddSql(edbInfoId, edbCode, dataTime, timestampStr string, value string) (addSql string) {
  32. nowStr := time.Now().Format(utils.FormatDateTime)
  33. addSql += "("
  34. addSql += edbInfoId + "," + "'" + edbCode + "'" + "," + "'" + dataTime + "'" + "," + value + "," + "'" + nowStr + "'" +
  35. "," + "'" + nowStr + "'"
  36. addSql += "," + "'" + timestampStr + "'"
  37. addSql += "),"
  38. return
  39. }
  40. type AddEdbInfoReq struct {
  41. EdbCode string `description:"指标编码"`
  42. Source int `description:"指标来源ID"`
  43. }
  44. // GetEdbInfoCountByCondition 获取指标数量
  45. func GetEdbInfoCountByCondition(condition string, pars []interface{}) (count int, err error) {
  46. o := orm.NewOrm()
  47. sql := ` SELECT COUNT(1) AS count FROM edb_info WHERE 1=1 `
  48. if condition != "" {
  49. sql += condition
  50. }
  51. err = o.Raw(sql, pars).QueryRow(&count)
  52. return
  53. }
  54. // GetEdbDataAllByEdbCode 根据指标编码获取指标数据列表
  55. func GetEdbDataAllByEdbCode(edbCode string, source, limit int) (items []*EdbInfoSearchData, err error) {
  56. var pars []interface{}
  57. pars = append(pars, edbCode)
  58. o := orm.NewOrm()
  59. tableName := GetEdbDataTableName(source)
  60. sql := ` SELECT * FROM %s WHERE edb_code=? ORDER BY data_time DESC`
  61. if limit > 0 {
  62. sql += ` LIMIT ? `
  63. pars = append(pars, limit)
  64. }
  65. sql = fmt.Sprintf(sql, tableName)
  66. _, err = o.Raw(sql, pars).QueryRows(&items)
  67. return
  68. }
  69. func GetEdbDataByCondition(source int, condition string, pars []interface{}) (items []*EdbInfoSearchData, err error) {
  70. o := orm.NewOrm()
  71. tableName := GetEdbDataTableName(source)
  72. sql := ` SELECT * FROM %s WHERE 1=1 `
  73. if condition != "" {
  74. sql += condition
  75. }
  76. sql += ` ORDER BY data_time DESC `
  77. sql = fmt.Sprintf(sql, tableName)
  78. _, err = o.Raw(sql, pars).QueryRows(&items)
  79. return
  80. }
  81. // GetEdbDataByDate 根据数据日期获取指标数据
  82. func GetEdbDataByDate(source int, edbCode, dataTime string) (item *EdbInfoSearchData, err error) {
  83. o := orm.NewOrm()
  84. tableName := GetEdbDataTableName(source)
  85. sql := ` SELECT * FROM %s WHERE 1=1 AND edb_code = ? AND data_time =? ORDER BY data_time DESC `
  86. sql = fmt.Sprintf(sql, tableName)
  87. err = o.Raw(sql, edbCode, dataTime).QueryRow(&item)
  88. return
  89. }
  90. func ModifyEdbDataById(source, edbDataId int, value string) (err error) {
  91. o := orm.NewOrm()
  92. tableName := GetEdbDataTableName(source)
  93. sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_data_id=? `
  94. sql = fmt.Sprintf(sql, tableName)
  95. _, err = o.Raw(sql, value, edbDataId).Exec()
  96. return
  97. }
  98. func DeleteEdbDataById(source, edbDataId int) (err error) {
  99. sql := ` DELETE FROM %s WHERE edb_data_id=? `
  100. tableName := GetEdbDataTableName(source)
  101. sql = fmt.Sprintf(sql, tableName)
  102. o := orm.NewOrm()
  103. _, err = o.Raw(sql, edbDataId).Exec()
  104. return
  105. }
  106. type RefreshEdbInfoReq struct {
  107. EdbInfoId int `description:"指标ID"`
  108. EdbCode string `description:"指标编码"`
  109. StartDate string `description:"开始日期"`
  110. Source int `description:"指标来源ID"`
  111. }
  112. // GetAllEdbDataList 获取所有的指标数据列表
  113. func GetAllEdbDataList(edbInfoId, source int) (existDataList []*EdbData, err error) {
  114. o := orm.NewOrm()
  115. dataTableName := GetEdbDataTableName(source)
  116. fmt.Println("dataTableName:", dataTableName)
  117. sql := `SELECT * FROM %s WHERE edb_info_id=? order by data_time asc`
  118. sql = fmt.Sprintf(sql, dataTableName)
  119. _, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList)
  120. return
  121. }
  122. // GetAllEdbDataListByTo 获取所有的指标数据列表
  123. func GetAllEdbDataListByTo(to orm.TxOrmer, edbInfoId, source int) (existDataList []*EdbData, err error) {
  124. dataTableName := GetEdbDataTableName(source)
  125. fmt.Println("dataTableName:", dataTableName)
  126. sql := `SELECT * FROM %s WHERE edb_info_id=? order by data_time asc`
  127. sql = fmt.Sprintf(sql, dataTableName)
  128. _, err = to.Raw(sql, edbInfoId).QueryRows(&existDataList)
  129. return
  130. }
  131. // GetFinalLastByTo 获取所有的指标数据列表
  132. func GetFinalLastByTo(to orm.TxOrmer, edbInfoId, source int, latestDate string) (finalLast EdbInfoSearchData, err error) {
  133. dataTableName := GetEdbDataTableName(source)
  134. sql := fmt.Sprintf(` SELECT data_time , value FROM %s WHERE edb_info_id=? and data_time<=? ORDER BY data_time DESC `, dataTableName)
  135. err = to.Raw(sql, edbInfoId, latestDate).QueryRow(&finalLast)
  136. if err != nil && err.Error() != utils.ErrNoRow() {
  137. return
  138. }
  139. return
  140. }
  141. // 新版本
  142. type EdbDataV1 struct {
  143. EdbDataId int `orm:"column(edb_data_id);pk"`
  144. EdbInfoId int
  145. EdbCode string
  146. DataTime string
  147. Value string
  148. Status int
  149. CreateTime time.Time
  150. ModifyTime time.Time
  151. DataTimestamp int64
  152. }
  153. type EdbDataList struct {
  154. EdbDataId int `description:" 指标数据ID"`
  155. EdbInfoId int `description:"指标ID"`
  156. DataTime string //`json:"-" description:"数据日期"`
  157. DataTimestamp int64 `description:"数据日期"`
  158. Value float64 `description:"数据值"`
  159. }
  160. func GetEdbDataList(source, endInfoId int, startDate, endDate string) (list []*EdbDataList, err error) {
  161. tableName := GetEdbDataTableName(source)
  162. if tableName == "" {
  163. err = errors.New("无效的渠道:" + strconv.Itoa(source))
  164. list = make([]*EdbDataList, 0)
  165. return list, err
  166. }
  167. var pars []interface{}
  168. sql := `SELECT edb_data_id,edb_info_id,data_time,value,data_timestamp FROM %s WHERE edb_info_id=? `
  169. if startDate != "" {
  170. sql += ` AND data_time>=? `
  171. pars = append(pars, startDate)
  172. }
  173. if endDate != "" {
  174. sql += ` AND data_time<=? `
  175. pars = append(pars, endDate)
  176. }
  177. sql += ` ORDER BY data_time ASC `
  178. sql = fmt.Sprintf(sql, tableName)
  179. o := orm.NewOrm()
  180. _, err = o.Raw(sql, endInfoId, pars).QueryRows(&list)
  181. return
  182. }