edb_data_manual.go 8.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323
  1. package data_manage
  2. import (
  3. "eta/eta_mobile/utils"
  4. "fmt"
  5. "github.com/beego/beego/v2/client/orm"
  6. "strconv"
  7. "strings"
  8. "time"
  9. )
  10. func AddEdbDataManualBySql(sqlStr string) (err error) {
  11. o := orm.NewOrmUsingDB("data")
  12. _, err = o.Raw(sqlStr).Exec()
  13. return
  14. }
  15. func GetEdbDataManualMaxOrMinDate(edbCode string) (min_date, max_date string, err error) {
  16. o := orm.NewOrmUsingDB("data")
  17. sql := ` SELECT MIN(data_time) AS min_date,MAX(data_time) AS max_date FROM edb_data_manual WHERE edb_code=? `
  18. err = o.Raw(sql, edbCode).QueryRow(&min_date, &max_date)
  19. return
  20. }
  21. type ManualEdbdata struct {
  22. TradeCode string `orm:"column(TRADE_CODE);pk" description:"指标编码"`
  23. Dt string `orm:"column(DT)" description:"日期"`
  24. Close string `orm:"column(CLOSE)" description:"值"`
  25. ModifyTime time.Time `orm:"column(modify_time)" description:"修改时间"`
  26. }
  27. func GetEdbdataManualByTradeCode(condition string, pars []interface{}) (item []*ManualEdbdata, err error) {
  28. sql := ` SELECT * FROM edbdata WHERE 1=1 `
  29. o := orm.NewOrmUsingDB("edb")
  30. if condition != "" {
  31. sql += condition
  32. }
  33. sql += ` ORDER BY DT DESC `
  34. _, err = o.Raw(sql, pars).QueryRows(&item)
  35. return
  36. }
  37. func GetEdbDataManualByCode(edbCode string, size int) (items []*EdbInfoSearchData, err error) {
  38. o := orm.NewOrmUsingDB("data")
  39. sql := ` SELECT * FROM edb_data_manual WHERE edb_code=? ORDER BY data_time DESC LIMIT ? `
  40. _, err = o.Raw(sql, edbCode, size).QueryRows(&items)
  41. return
  42. }
  43. func GetEdbDataByManual(edbCode, startDate, endDate string) (searchItem *EdbInfoSearch, err error) {
  44. o := orm.NewOrmUsingDB("data")
  45. to, err := o.Begin()
  46. if err != nil {
  47. return
  48. }
  49. defer func() {
  50. if err != nil {
  51. _ = to.Rollback()
  52. } else {
  53. _ = to.Commit()
  54. }
  55. }()
  56. var condition string
  57. var pars []interface{}
  58. if edbCode != "" {
  59. condition += " AND TRADE_CODE=? "
  60. pars = append(pars, edbCode)
  61. }
  62. manualDataList, err := GetEdbdataManualByTradeCode(condition, pars)
  63. if err != nil {
  64. return
  65. }
  66. searchItem = new(EdbInfoSearch)
  67. searchItem.EdbCode = edbCode
  68. dataLen := len(manualDataList)
  69. if dataLen > 0 {
  70. var isAdd bool
  71. addSql := ` INSERT INTO edb_data_manual(edb_info_id,edb_code,data_time,value,create_time,modify_time,status,data_timestamp) values `
  72. nowStr := time.Now().Format(utils.FormatDateTime)
  73. dataList := make([]*EdbInfoSearchData, 0)
  74. for i := 0; i < dataLen; i++ {
  75. item := manualDataList[i]
  76. eDate := item.Dt
  77. sValue := item.Close
  78. dataTime, err := time.Parse(utils.FormatDate, eDate)
  79. if err != nil {
  80. return nil, err
  81. }
  82. timestamp := dataTime.UnixNano() / 1e6
  83. timeStr := fmt.Sprintf("%d", timestamp)
  84. addSql += "("
  85. addSql += "0," + "'" + edbCode + "'" + "," + "'" + eDate + "'" + "," + sValue + "," + "'" + nowStr + "'" +
  86. "," + "'" + nowStr + "'" + "," + "0" + "," + "'" + timeStr + "'"
  87. addSql += "),"
  88. isAdd = true
  89. }
  90. if isAdd {
  91. addSql = strings.TrimRight(addSql, ",")
  92. err = AddEdbDataManualBySql(addSql)
  93. if err != nil {
  94. utils.FileLogData.Info("AddEdbDataThsBySql Err:%s", err.Error())
  95. return
  96. }
  97. }
  98. size := utils.EDB_DATA_LIMIT
  99. dataList, err := GetEdbDataManualByCode(edbCode, size)
  100. if err != nil {
  101. utils.FileLogData.Info("GetEdbDataThsByCode Err:%s", err.Error())
  102. return searchItem, err
  103. }
  104. minDate, maxDate, err := GetEdbDataManualMaxOrMinDate(edbCode)
  105. if err != nil {
  106. return searchItem, err
  107. }
  108. searchItem.DataList = dataList
  109. searchItem.StartDate = minDate
  110. searchItem.EndDate = maxDate
  111. }
  112. if searchItem.DataList == nil {
  113. searchItem.DataList = make([]*EdbInfoSearchData, 0)
  114. }
  115. return
  116. }
  117. func GetEdbDataManualByCodeAndDate(edbCode string, startDate string) (count int, err error) {
  118. o := orm.NewOrmUsingDB("data")
  119. sql := ` SELECT COUNT(1) AS count FROM edb_data_manual WHERE edb_code=? AND data_time=? `
  120. err = o.Raw(sql, edbCode, startDate).QueryRow(&count)
  121. return
  122. }
  123. func ModifyEdbDataManual(edbInfoId int64, dataTime, value string) (err error) {
  124. o := orm.NewOrmUsingDB("data")
  125. sql := ` UPDATE edb_data_manual SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? `
  126. _, err = o.Raw(sql, value, edbInfoId, dataTime).Exec()
  127. return
  128. }
  129. // 刷新手工指标数据
  130. func RefreshEdbDataByManual(edbInfoId int, edbCode, startDate, endDate string) (err error) {
  131. o := orm.NewOrmUsingDB("data")
  132. to, err := o.Begin()
  133. if err != nil {
  134. return
  135. }
  136. defer func() {
  137. if err != nil {
  138. _ = to.Rollback()
  139. } else {
  140. _ = to.Commit()
  141. }
  142. }()
  143. if err != nil {
  144. return
  145. }
  146. edbInfoIdStr := strconv.Itoa(edbInfoId)
  147. //计算数据
  148. var condition string
  149. var pars []interface{}
  150. if edbCode != "" {
  151. condition += " AND TRADE_CODE=? "
  152. pars = append(pars, edbCode)
  153. }
  154. if startDate != "" {
  155. condition += " AND DT>=? "
  156. pars = append(pars, startDate)
  157. }
  158. if endDate != "" {
  159. condition += " AND DT<=? "
  160. pars = append(pars, endDate)
  161. }
  162. manualDataList, err := GetEdbdataManualByTradeCode(condition, pars)
  163. addSql := ` INSERT INTO edb_data_manual(edb_info_id,edb_code,data_time,value,create_time,modify_time,status,data_timestamp) values `
  164. var isAdd bool
  165. for _, v := range manualDataList {
  166. item := v
  167. count, err := GetEdbDataManualByCodeAndDate(v.TradeCode, v.Dt)
  168. if err != nil && err.Error() != utils.ErrNoRow() {
  169. return err
  170. }
  171. if count <= 0 {
  172. nowStr := time.Now().Format(utils.FormatDateTime)
  173. eDate := item.Dt
  174. sValue := item.Close
  175. dataTime, err := time.Parse(utils.FormatDate, eDate)
  176. if err != nil {
  177. return err
  178. }
  179. timestamp := dataTime.UnixNano() / 1e6
  180. timeStr := fmt.Sprintf("%d", timestamp)
  181. addSql += "("
  182. addSql += edbInfoIdStr + "," + "'" + edbCode + "'" + "," + "'" + eDate + "'" + "," + sValue + "," + "'" + nowStr + "'" +
  183. "," + "'" + nowStr + "'" + "," + "1" + "," + "'" + timeStr + "'"
  184. addSql += "),"
  185. isAdd = true
  186. } else {
  187. err = ModifyEdbDataManual(int64(edbInfoId), v.Dt, v.Close)
  188. if err != nil {
  189. return err
  190. }
  191. }
  192. }
  193. if isAdd {
  194. addSql = strings.TrimRight(addSql, ",")
  195. _, err = to.Raw(addSql).Exec()
  196. if err != nil {
  197. return err
  198. }
  199. }
  200. return
  201. }
  202. // 刷新手工指标数据
  203. func RefreshAllEdbDataByManual(edbInfoId, source, subSource int, edbCode string) (err error) {
  204. o := orm.NewOrmUsingDB("data")
  205. to, err := o.Begin()
  206. if err != nil {
  207. return
  208. }
  209. defer func() {
  210. if err != nil {
  211. _ = to.Rollback()
  212. } else {
  213. _ = to.Commit()
  214. }
  215. }()
  216. if err != nil {
  217. return
  218. }
  219. edbInfoIdStr := strconv.Itoa(edbInfoId)
  220. //计算数据
  221. var condition string
  222. var pars []interface{}
  223. if edbCode != "" {
  224. condition += " AND TRADE_CODE=? "
  225. pars = append(pars, edbCode)
  226. }
  227. manualDataList, err := GetEdbdataManualByTradeCode(condition, pars)
  228. //获取指标所有数据
  229. existDataList := make([]*EdbDataBase, 0)
  230. dataTableName := GetEdbDataTableName(source, subSource)
  231. sql := `SELECT * FROM %s WHERE edb_info_id=? `
  232. sql = fmt.Sprintf(sql, dataTableName)
  233. _, err = to.Raw(sql, edbInfoId).QueryRows(&existDataList)
  234. if err != nil {
  235. return err
  236. }
  237. existDataMap := make(map[string]*EdbDataBase)
  238. for _, v := range existDataList {
  239. existDataMap[v.DataTime] = v
  240. }
  241. addSql := ` INSERT INTO edb_data_manual(edb_info_id,edb_code,data_time,value,create_time,modify_time,status,data_timestamp) values `
  242. var isAdd bool
  243. manualMap := make(map[string]*ManualEdbdata)
  244. fmt.Println("manualDataList:", len(manualDataList))
  245. for _, v := range manualDataList {
  246. item := v
  247. fmt.Println("Item:", item.Dt, item.Close, item.TradeCode, item.ModifyTime)
  248. if findItem, ok := existDataMap[v.Dt]; !ok {
  249. eDate := item.Dt
  250. sValue := item.Close
  251. dataTime, err := time.Parse(utils.FormatDate, eDate)
  252. if err != nil {
  253. return err
  254. }
  255. timestamp := dataTime.UnixNano() / 1e6
  256. timeStr := fmt.Sprintf("%d", timestamp)
  257. addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue)
  258. isAdd = true
  259. } else {
  260. if findItem.Value != item.Close {
  261. sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? `
  262. sql = fmt.Sprintf(sql, dataTableName)
  263. _, err = to.Raw(sql, item.Close, edbInfoId, item.Dt).Exec()
  264. if err != nil {
  265. return err
  266. }
  267. }
  268. }
  269. manualMap[v.Dt] = v
  270. }
  271. for _, v := range existDataList {
  272. if _, ok := manualMap[v.DataTime]; !ok {
  273. go DeleteEdbDataByIdAndSource(v.EdbDataId, utils.DATA_SOURCE_MANUAL, utils.DATA_SUB_SOURCE_EDB)
  274. }
  275. }
  276. fmt.Println("isAdd:", isAdd)
  277. if isAdd {
  278. addSql = strings.TrimRight(addSql, ",")
  279. _, err = to.Raw(addSql).Exec()
  280. if err != nil {
  281. fmt.Println("RefreshAllEdbDataByManual add Err", err.Error())
  282. return
  283. }
  284. }
  285. return
  286. }
  287. func DeleteEdbDataByIdAndSource(edbDataId, source, subSource int) (err error) {
  288. sql := ` DELETE FROM %s WHERE edb_data_id=? `
  289. tableName := GetEdbDataTableName(source, subSource)
  290. sql = fmt.Sprintf(sql, tableName)
  291. o := orm.NewOrmUsingDB("data")
  292. _, err = o.Raw(sql, edbDataId).Exec()
  293. return
  294. }