chart_edb_mapping.go 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309
  1. package data_manage
  2. import (
  3. "eta/eta_api/utils"
  4. "fmt"
  5. "github.com/beego/beego/v2/client/orm"
  6. "github.com/rdlucklib/rdluck_tools/paging"
  7. "strconv"
  8. "strings"
  9. "time"
  10. )
  11. type ChartEdbMapping struct {
  12. ChartEdbMappingId int `orm:"column(chart_edb_mapping_id);pk"`
  13. ChartInfoId int `description:"图表id"`
  14. EdbInfoId int `description:"指标id"`
  15. CreateTime time.Time `description:"创建时间"`
  16. ModifyTime time.Time `description:"修改时间"`
  17. UniqueCode string `description:"唯一编码"`
  18. MaxData float64 `description:"上限"`
  19. MinData float64 `description:"下限"`
  20. IsOrder bool `description:"true:正序,false:逆序"`
  21. IsAxis int `description:"true:左轴,false:右轴"`
  22. EdbInfoType int `description:"true:标准指标,false:领先指标"`
  23. LeadValue int `description:"领先值"`
  24. LeadUnit string `description:"领先单位"`
  25. ChartStyle string `description:"图表类型"`
  26. ChartColor string `description:"颜色"`
  27. PredictChartColor string `description:"预测数据的颜色"`
  28. ChartWidth float64 `description:"线条大小"`
  29. Source int `description:"1:ETA图库;2:商品价格曲线"`
  30. EdbAliasName string `description:"中文别名"`
  31. IsConvert int `description:"是否数据转换 0不转 1转"`
  32. ConvertType int `description:"数据转换类型 1乘 2除 3对数"`
  33. ConvertValue float64 `description:"数据转换值"`
  34. ConvertUnit string `description:"数据转换单位"`
  35. ConvertEnUnit string `description:"数据转换单位"`
  36. }
  37. func AddChartEdbMapping(items []*ChartEdbMapping) (err error) {
  38. o := orm.NewOrmUsingDB("data")
  39. _, err = o.InsertMulti(1, items)
  40. return
  41. }
  42. func GetChartEdbMappingList(chartInfoId int) (list []*ChartEdbInfoMapping, err error) {
  43. o := orm.NewOrmUsingDB("data")
  44. sql := ` SELECT a.*,b.source_name,b.source,b.sub_source,b.classify_id,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,
  45. b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type AS edb_info_category_type,b.edb_type,
  46. a.is_convert, a.convert_type, a.convert_value, a.convert_unit, a.convert_en_unit
  47. FROM chart_edb_mapping AS a
  48. INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
  49. WHERE chart_info_id=?
  50. ORDER BY chart_edb_mapping_id ASC `
  51. _, err = o.Raw(sql, chartInfoId).QueryRows(&list)
  52. return
  53. }
  54. func GetChartEdbMappingListByChartInfoIds(chartInfoIds string) (list []*ChartEdbInfoMapping, err error) {
  55. o := orm.NewOrmUsingDB("data")
  56. sql := ` SELECT a.*,b.source_name,b.source,b.sub_source,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type AS edb_info_category_type,b.edb_type
  57. FROM chart_edb_mapping AS a
  58. INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
  59. WHERE chart_info_id in (` + chartInfoIds + `)
  60. ORDER BY chart_edb_mapping_id ASC `
  61. _, err = o.Raw(sql).QueryRows(&list)
  62. return
  63. }
  64. func GetChartEdbMappingListByEdbInfoId(edbInfoStr string) (list []*ChartEdbInfoMapping, err error) {
  65. o := orm.NewOrmUsingDB("data")
  66. sql := ` SELECT edb_info_id,source_name,source,sub_source,edb_code,edb_name,edb_name_en,frequency,unit,unit_en,start_date,end_date,modify_time,latest_date,latest_value,unique_code,edb_info_type AS edb_info_category_type,max_value,min_value,edb_type
  67. FROM edb_info
  68. WHERE edb_info_id IN(` + edbInfoStr + `)
  69. ORDER BY FIELD(edb_info_id,` + edbInfoStr + `)
  70. `
  71. _, err = o.Raw(sql).QueryRows(&list)
  72. return
  73. }
  74. // GetChartEdbMappingListByEdbInfoIdList 根据指标id列表获取关联关系
  75. func GetChartEdbMappingListByEdbInfoIdList(edbIdList []int) (list []*ChartEdbInfoMapping, err error) {
  76. num := len(edbIdList)
  77. if num <= 0 {
  78. return
  79. }
  80. o := orm.NewOrmUsingDB("data")
  81. sql := ` SELECT edb_info_id,source_name,source,sub_source,edb_code,edb_name,edb_name_en,frequency,unit,unit_en,start_date,end_date,modify_time,latest_date,latest_value,unique_code,edb_info_type AS edb_info_category_type,max_value,min_value,edb_type
  82. FROM edb_info
  83. WHERE edb_info_id IN(` + utils.GetOrmInReplace(num) + `)
  84. ORDER BY FIELD(edb_info_id,` + utils.GetOrmInReplace(num) + `)
  85. `
  86. _, err = o.Raw(sql, edbIdList, edbIdList).QueryRows(&list)
  87. return
  88. }
  89. // GetChartEdbMappingListByIdList 通过图表id列表获取
  90. func GetChartEdbMappingListByIdList(chartInfoIdList []int) (list []*ChartEdbInfoMapping, err error) {
  91. num := len(chartInfoIdList)
  92. if num <= 0 {
  93. return
  94. }
  95. o := orm.NewOrmUsingDB("data")
  96. sql := ` SELECT a.*,b.source_name,b.source,b.sub_source,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type,b.edb_type AS edb_info_category_type
  97. FROM chart_edb_mapping AS a
  98. INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
  99. WHERE chart_info_id in (` + utils.GetOrmInReplace(num) + `)
  100. ORDER BY chart_edb_mapping_id ASC `
  101. _, err = o.Raw(sql, chartInfoIdList).QueryRows(&list)
  102. return
  103. }
  104. // RelationEdbInfoListResp 关联指标列表数据返回
  105. type RelationEdbInfoListResp struct {
  106. Paging *paging.PagingItem
  107. List []*ChartEdbInfoMapping
  108. }
  109. func GetRelationEdbInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*ChartEdbInfoMapping, err error) {
  110. o := orm.NewOrmUsingDB("data")
  111. sql := ` SELECT a.* FROM edb_info AS a
  112. JOIN edb_info_calculate_mapping AS b on a.edb_info_id = b.edb_info_id WHERE 1=1 `
  113. if condition != "" {
  114. sql += condition
  115. }
  116. //sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
  117. sql += " ORDER BY a.create_time DESC LIMIT ?,? "
  118. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
  119. return
  120. }
  121. func GetRelationEdbInfoListCountByCondition(condition string, pars []interface{}) (count int, err error) {
  122. o := orm.NewOrmUsingDB("data")
  123. sql := ` SELECT COUNT(1) AS count FROM edb_info AS a
  124. JOIN edb_info_calculate_mapping AS b on a.edb_info_id = b.edb_info_id WHERE 1=1 `
  125. if condition != "" {
  126. sql += condition
  127. }
  128. err = o.Raw(sql, pars).QueryRow(&count)
  129. return
  130. }
  131. // GetEtaEdbChartEdbMapping 商品曲线图查询对应的普通指标
  132. func GetEtaEdbChartEdbMapping(chartInfoId int) (item *ChartEdbInfoMapping, err error) {
  133. o := orm.NewOrmUsingDB("data")
  134. aField := `a.chart_edb_mapping_id,a.chart_info_id,a.edb_info_id,a.create_time,a.modify_time,a.unique_code,a.max_data,a.min_data,a.is_order,a.is_axis,a.edb_info_type,a.lead_value,a.lead_unit,a.chart_style,a.chart_color,a.predict_chart_color,a.chart_width,a.source as mapping_source`
  135. sql := ` SELECT ` + aField + `,b.source_name,b.source,b.sub_source,b.edb_code,b.edb_name,b.edb_name_en,b.frequency,b.unit,b.unit_en,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.unique_code,b.edb_info_type AS edb_info_category_type
  136. FROM chart_edb_mapping AS a
  137. INNER JOIN edb_info AS b ON a.edb_info_id=b.edb_info_id
  138. WHERE a.chart_info_id=? AND a.source = ?
  139. ORDER BY chart_edb_mapping_id ASC `
  140. err = o.Raw(sql, chartInfoId, utils.CHART_SOURCE_DEFAULT).QueryRow(&item)
  141. return
  142. }
  143. // GetFutureGoodEdbChartEdbMapping 商品曲线图查询对应的商品指标
  144. func GetFutureGoodEdbChartEdbMapping(chartInfoId int) (item *ChartEdbInfoMapping, err error) {
  145. o := orm.NewOrmUsingDB("data")
  146. aField := `a.chart_edb_mapping_id,a.chart_info_id,a.edb_info_id,a.create_time,a.modify_time,a.unique_code,a.max_data,a.min_data,a.is_order,a.is_axis,a.edb_info_type,a.lead_value,a.lead_unit,a.chart_style,a.chart_color,a.predict_chart_color,a.chart_width,a.source as mapping_source`
  147. sql := ` SELECT ` + aField + `,b.future_good_edb_info_id,b.future_good_edb_code as edb_code,b.future_good_edb_name as edb_name,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value
  148. FROM chart_edb_mapping AS a
  149. INNER JOIN future_good_edb_info AS b ON a.edb_info_id=b.future_good_edb_info_id
  150. WHERE a.chart_info_id=? AND a.source = ?
  151. ORDER BY chart_edb_mapping_id ASC `
  152. err = o.Raw(sql, chartInfoId, utils.CHART_SOURCE_FUTURE_GOOD).QueryRow(&item)
  153. return
  154. }
  155. // GetFutureGoodEdbChartEdbMappingList 商品曲线图查询对应的商品指标
  156. func GetFutureGoodEdbChartEdbMappingList(chartInfoId int) (items []*ChartEdbInfoMapping, err error) {
  157. o := orm.NewOrmUsingDB("data")
  158. aField := `a.chart_edb_mapping_id,a.chart_info_id,a.edb_info_id,a.create_time,a.modify_time,a.unique_code,a.max_data,a.min_data,a.is_order,a.is_axis,a.edb_info_type,a.lead_value,a.lead_unit,a.chart_style,a.chart_color,a.predict_chart_color,a.chart_width,a.source as mapping_source`
  159. sql := ` SELECT ` + aField + `,b.future_good_edb_info_id,b.future_good_edb_code as edb_code,b.future_good_edb_name as edb_name,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value
  160. FROM chart_edb_mapping AS a
  161. INNER JOIN future_good_edb_info AS b ON a.edb_info_id=b.future_good_edb_info_id
  162. WHERE a.chart_info_id=? AND a.source = ?
  163. ORDER BY chart_edb_mapping_id ASC `
  164. _, err = o.Raw(sql, chartInfoId, utils.CHART_SOURCE_FUTURE_GOOD).QueryRows(&items)
  165. return
  166. }
  167. // GetChartEdbMappingListV2 根据图表id获取指标信息,不连表查询指标表
  168. func GetChartEdbMappingListV2(chartInfoId int) (list []*ChartEdbInfoMapping, err error) {
  169. o := orm.NewOrmUsingDB("data")
  170. sql := ` SELECT a.* FROM chart_edb_mapping AS a
  171. WHERE chart_info_id=?
  172. ORDER BY chart_edb_mapping_id ASC `
  173. _, err = o.Raw(sql, chartInfoId).QueryRows(&list)
  174. return
  175. }
  176. // GetChartEdbMappingByEdbInfoId 根据指标id获取edb_mapping
  177. func GetChartEdbMappingByEdbInfoId(edbInfoId int) (item *ChartEdbInfoMapping, err error) {
  178. o := orm.NewOrmUsingDB("data")
  179. sql := ` SELECT edb_info_id,source_name,classify_id,source,sub_source,edb_code,edb_name,edb_name_en,frequency,unit,unit_en,start_date,end_date,modify_time,latest_date,latest_value,unique_code,edb_info_type AS edb_info_category_type,edb_type,max_value,min_value
  180. FROM edb_info
  181. WHERE edb_info_id = ? limit 1`
  182. err = o.Raw(sql, edbInfoId).QueryRow(&item)
  183. return
  184. }
  185. // GetChartEdbMappingByFutureGoodEdbInfoId 根据指标id获取edb_mapping
  186. func GetChartEdbMappingByFutureGoodEdbInfoId(edbInfoId int) (item *ChartEdbInfoMapping, err error) {
  187. o := orm.NewOrmUsingDB("data")
  188. sql := ` SELECT future_good_edb_info_id as edb_info_id,future_good_edb_code as edb_code,b.future_good_edb_name as edb_name,b.start_date,b.end_date,b.modify_time,b.latest_date,b.latest_value,b.region_type
  189. FROM future_good_edb_info b
  190. WHERE future_good_edb_info_id = ? limit 1 `
  191. err = o.Raw(sql, edbInfoId).QueryRow(&item)
  192. return
  193. }
  194. // ModifyChartEdbMapping
  195. // @Description: 修改图表的关系表
  196. // @author: Roc
  197. // @datetime 2023-12-11 17:23:32
  198. // @param chartInfoId int
  199. // @param edbInfoList []*EdbInfo
  200. // @return err error
  201. func ModifyChartEdbMapping(chartInfoId int, edbInfoList []*EdbInfo) (err error) {
  202. o, err := orm.NewOrmUsingDB("data").Begin()
  203. if err != nil {
  204. return
  205. }
  206. defer func() {
  207. if err != nil {
  208. fmt.Println("AddCalculateHcz,Err:" + err.Error())
  209. _ = o.Rollback()
  210. } else {
  211. _ = o.Commit()
  212. }
  213. }()
  214. list := make([]*ChartEdbMapping, 0)
  215. sql := ` SELECT a.*
  216. FROM chart_edb_mapping AS a
  217. WHERE chart_info_id=?
  218. ORDER BY chart_edb_mapping_id ASC `
  219. _, err = o.Raw(sql, chartInfoId).QueryRows(&list)
  220. if err != nil {
  221. return
  222. }
  223. mappingIdMap := make(map[int]*ChartEdbMapping)
  224. removeMapping := make(map[int]int)
  225. for _, v := range list {
  226. mappingIdMap[v.EdbInfoId] = v
  227. removeMapping[v.EdbInfoId] = v.ChartEdbMappingId
  228. }
  229. addList := make([]*ChartEdbMapping, 0)
  230. for _, v := range edbInfoList {
  231. _, ok := mappingIdMap[v.EdbInfoId]
  232. // 存在该指标关系就不处理了
  233. if ok {
  234. delete(removeMapping, v.EdbInfoId)
  235. continue
  236. }
  237. timestamp := strconv.FormatInt(time.Now().UnixNano(), 10)
  238. // 不存在就添加
  239. addList = append(addList, &ChartEdbMapping{
  240. //ChartEdbMappingId: 0,
  241. ChartInfoId: chartInfoId,
  242. EdbInfoId: v.EdbInfoId,
  243. CreateTime: time.Now(),
  244. ModifyTime: time.Now(),
  245. UniqueCode: utils.MD5(utils.CHART_PREFIX + "_" + fmt.Sprint(chartInfoId) + "_" + fmt.Sprint(v.EdbInfoId) + "_" + timestamp),
  246. MaxData: v.MaxValue,
  247. MinData: v.MinValue,
  248. //IsOrder: v.IsOrder,
  249. //IsAxis: v.IsAxis,
  250. EdbInfoType: v.EdbInfoType,
  251. //LeadValue: v.LeadValue,
  252. //LeadUnit: v.LeadUnit,
  253. //ChartStyle: v.ChartStyle,
  254. //ChartColor: v.ChartColor,
  255. //ChartWidth: v.ChartWidth,
  256. Source: v.Source,
  257. })
  258. }
  259. // 需要添加的话,那就添加吧
  260. if len(addList) > 0 {
  261. _, err = o.InsertMulti(len(addList), addList)
  262. if err != nil {
  263. return
  264. }
  265. }
  266. // 移除不必要的mapping
  267. if len(removeMapping) > 0 {
  268. removeIdList := make([]string, 0) //需要移除的日期
  269. for _, v := range removeMapping {
  270. removeIdList = append(removeIdList, fmt.Sprint(v))
  271. }
  272. removeIdStr := strings.Join(removeIdList, `","`)
  273. removeIdStr = `"` + removeIdStr + `"`
  274. //如果拼接指标变更了,那么需要删除所有的指标数据
  275. sql := fmt.Sprintf(` DELETE FROM chart_edb_mapping WHERE chart_edb_mapping_id in (%s) `, removeIdStr)
  276. _, err = o.Raw(sql).Exec()
  277. if err != nil {
  278. err = fmt.Errorf("移除不必要的mapping失败,Err:" + err.Error())
  279. return
  280. }
  281. }
  282. return
  283. }