chart_edb_mapping.go 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333
  1. package data_manage
  2. import (
  3. "eta/eta_mobile/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,b.is_join_permission,
  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,b.classify_id,b.is_join_permission
  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,classify_id,is_join_permission
  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,classify_id,is_join_permission
  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,b.classify_id,b.is_join_permission
  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,b.classify_id,b.is_join_permission
  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. // GetEtaEdbChartEdbMappingList 商品曲线图查询对应的普通指标
  144. func GetEtaEdbChartEdbMappingList(chartInfoId int) (items []*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.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.classify_id,b.is_join_permission
  148. FROM chart_edb_mapping AS a
  149. INNER JOIN edb_info AS b ON a.edb_info_id=b.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_DEFAULT).QueryRows(&items)
  153. return
  154. }
  155. // GetFutureGoodEdbChartEdbMapping 商品曲线图查询对应的商品指标
  156. func GetFutureGoodEdbChartEdbMapping(chartInfoId int) (item *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).QueryRow(&item)
  165. return
  166. }
  167. // GetFutureGoodEdbChartEdbMappingList 商品曲线图查询对应的商品指标
  168. func GetFutureGoodEdbChartEdbMappingList(chartInfoId int) (items []*ChartEdbInfoMapping, err error) {
  169. o := orm.NewOrmUsingDB("data")
  170. 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`
  171. 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
  172. FROM chart_edb_mapping AS a
  173. INNER JOIN future_good_edb_info AS b ON a.edb_info_id=b.future_good_edb_info_id
  174. WHERE a.chart_info_id=? AND a.source = ?
  175. ORDER BY chart_edb_mapping_id ASC `
  176. _, err = o.Raw(sql, chartInfoId, utils.CHART_SOURCE_FUTURE_GOOD).QueryRows(&items)
  177. return
  178. }
  179. // GetChartEdbMappingListV2 根据图表id获取指标信息,不连表查询指标表
  180. func GetChartEdbMappingListV2(chartInfoId int) (list []*ChartEdbInfoMapping, err error) {
  181. o := orm.NewOrmUsingDB("data")
  182. sql := ` SELECT a.* FROM chart_edb_mapping AS a
  183. WHERE chart_info_id=?
  184. ORDER BY chart_edb_mapping_id ASC `
  185. _, err = o.Raw(sql, chartInfoId).QueryRows(&list)
  186. return
  187. }
  188. // GetChartEdbMappingByEdbInfoId 根据指标id获取edb_mapping
  189. func GetChartEdbMappingByEdbInfoId(edbInfoId int) (item *ChartEdbInfoMapping, err error) {
  190. o := orm.NewOrmUsingDB("data")
  191. 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,is_join_permission
  192. FROM edb_info
  193. WHERE edb_info_id = ? limit 1`
  194. err = o.Raw(sql, edbInfoId).QueryRow(&item)
  195. return
  196. }
  197. // GetChartEdbMappingByFutureGoodEdbInfoId 根据指标id获取edb_mapping
  198. func GetChartEdbMappingByFutureGoodEdbInfoId(edbInfoId int) (item *ChartEdbInfoMapping, err error) {
  199. o := orm.NewOrmUsingDB("data")
  200. 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
  201. FROM future_good_edb_info b
  202. WHERE future_good_edb_info_id = ? limit 1 `
  203. err = o.Raw(sql, edbInfoId).QueryRow(&item)
  204. return
  205. }
  206. // ModifyChartEdbMapping
  207. // @Description: 修改图表的关系表
  208. // @author: Roc
  209. // @datetime 2023-12-11 17:23:32
  210. // @param chartInfoId int
  211. // @param edbInfoList []*EdbInfo
  212. // @return err error
  213. func ModifyChartEdbMapping(chartInfoId int, edbInfoList []*EdbInfo) (err error) {
  214. o, err := orm.NewOrmUsingDB("data").Begin()
  215. if err != nil {
  216. return
  217. }
  218. defer func() {
  219. if err != nil {
  220. fmt.Println("AddCalculateHcz,Err:" + err.Error())
  221. _ = o.Rollback()
  222. } else {
  223. _ = o.Commit()
  224. }
  225. }()
  226. list := make([]*ChartEdbMapping, 0)
  227. sql := ` SELECT a.*
  228. FROM chart_edb_mapping AS a
  229. WHERE chart_info_id=?
  230. ORDER BY chart_edb_mapping_id ASC `
  231. _, err = o.Raw(sql, chartInfoId).QueryRows(&list)
  232. if err != nil {
  233. return
  234. }
  235. mappingIdMap := make(map[int]*ChartEdbMapping)
  236. removeMapping := make(map[int]int)
  237. for _, v := range list {
  238. mappingIdMap[v.EdbInfoId] = v
  239. removeMapping[v.EdbInfoId] = v.ChartEdbMappingId
  240. }
  241. addList := make([]*ChartEdbMapping, 0)
  242. for _, v := range edbInfoList {
  243. _, ok := mappingIdMap[v.EdbInfoId]
  244. // 存在该指标关系就不处理了
  245. if ok {
  246. delete(removeMapping, v.EdbInfoId)
  247. continue
  248. }
  249. timestamp := strconv.FormatInt(time.Now().UnixNano(), 10)
  250. // 不存在就添加
  251. addList = append(addList, &ChartEdbMapping{
  252. //ChartEdbMappingId: 0,
  253. ChartInfoId: chartInfoId,
  254. EdbInfoId: v.EdbInfoId,
  255. CreateTime: time.Now(),
  256. ModifyTime: time.Now(),
  257. UniqueCode: utils.MD5(utils.CHART_PREFIX + "_" + fmt.Sprint(chartInfoId) + "_" + fmt.Sprint(v.EdbInfoId) + "_" + timestamp),
  258. MaxData: v.MaxValue,
  259. MinData: v.MinValue,
  260. //IsOrder: v.IsOrder,
  261. //IsAxis: v.IsAxis,
  262. EdbInfoType: v.EdbInfoType,
  263. //LeadValue: v.LeadValue,
  264. //LeadUnit: v.LeadUnit,
  265. //ChartStyle: v.ChartStyle,
  266. //ChartColor: v.ChartColor,
  267. //ChartWidth: v.ChartWidth,
  268. Source: v.Source,
  269. })
  270. }
  271. // 需要添加的话,那就添加吧
  272. if len(addList) > 0 {
  273. _, err = o.InsertMulti(len(addList), addList)
  274. if err != nil {
  275. return
  276. }
  277. }
  278. // 移除不必要的mapping
  279. if len(removeMapping) > 0 {
  280. removeIdList := make([]string, 0) //需要移除的日期
  281. for _, v := range removeMapping {
  282. removeIdList = append(removeIdList, fmt.Sprint(v))
  283. }
  284. removeIdStr := strings.Join(removeIdList, `","`)
  285. removeIdStr = `"` + removeIdStr + `"`
  286. //如果拼接指标变更了,那么需要删除所有的指标数据
  287. sql := fmt.Sprintf(` DELETE FROM chart_edb_mapping WHERE chart_edb_mapping_id in (%s) `, removeIdStr)
  288. _, err = o.Raw(sql).Exec()
  289. if err != nil {
  290. err = fmt.Errorf("移除不必要的mapping失败,Err:" + err.Error())
  291. return
  292. }
  293. }
  294. return
  295. }
  296. func GetChartMappingList(chartInfoId int) (list []*ChartEdbMapping, err error) {
  297. o := orm.NewOrmUsingDB("data")
  298. sql := ` SELECT a.*
  299. FROM chart_edb_mapping AS a
  300. WHERE chart_info_id=?
  301. ORDER BY chart_edb_mapping_id ASC `
  302. _, err = o.Raw(sql, chartInfoId).QueryRows(&list)
  303. return
  304. }