edb_data_calculate_cjjx.go 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467
  1. package models
  2. import (
  3. "errors"
  4. "eta/eta_index_lib/utils"
  5. "fmt"
  6. "github.com/beego/beego/v2/client/orm"
  7. "github.com/nosixtools/solarlunar"
  8. "github.com/shopspring/decimal"
  9. "strconv"
  10. "strings"
  11. "time"
  12. )
  13. // AddCalculateCjjx 超季节性
  14. func AddCalculateCjjx(req *EdbInfoCalculateBatchSaveReq, fromEdbInfo *EdbInfo, edbCode, uniqueCode string, sysUserId int, sysUserRealName string, formulaInt int) (edbInfo *EdbInfo, err error) {
  15. o := orm.NewOrm()
  16. to, err := o.Begin()
  17. if err != nil {
  18. return
  19. }
  20. defer func() {
  21. if err != nil {
  22. fmt.Println("AddCalculateCjjx,Err:" + err.Error())
  23. _ = to.Rollback()
  24. } else {
  25. _ = to.Commit()
  26. }
  27. }()
  28. fmt.Println("req.EdbInfoId:", req.EdbInfoId)
  29. if req.EdbInfoId <= 0 {
  30. edbInfo = new(EdbInfo)
  31. edbInfo.Source = utils.DATA_SOURCE_CALCULATE_CJJX
  32. edbInfo.SourceName = "超季节性"
  33. edbInfo.EdbCode = edbCode
  34. edbInfo.EdbName = req.EdbName
  35. edbInfo.EdbNameSource = req.EdbName
  36. edbInfo.Frequency = req.Frequency
  37. edbInfo.Unit = req.Unit
  38. edbInfo.ClassifyId = req.ClassifyId
  39. edbInfo.SysUserId = sysUserId
  40. edbInfo.SysUserRealName = sysUserRealName
  41. edbInfo.CreateTime = time.Now()
  42. edbInfo.ModifyTime = time.Now()
  43. edbInfo.UniqueCode = uniqueCode
  44. edbInfo.CalculateFormula = req.Formula
  45. edbInfo.Calendar = req.Calendar
  46. edbInfo.EdbNameEn = req.EdbName
  47. edbInfo.UnitEn = req.Unit
  48. edbInfo.EdbType = 2
  49. edbInfo.Sort = GetAddEdbMaxSortByClassifyId(req.ClassifyId, utils.EDB_INFO_TYPE)
  50. newEdbInfoId, tmpErr := to.Insert(edbInfo)
  51. if tmpErr != nil {
  52. err = tmpErr
  53. return
  54. }
  55. edbInfo.EdbInfoId = int(newEdbInfoId)
  56. //关联关系
  57. {
  58. calculateMappingItem := new(EdbInfoCalculateMapping)
  59. calculateMappingItem.CreateTime = time.Now()
  60. calculateMappingItem.ModifyTime = time.Now()
  61. calculateMappingItem.Sort = 1
  62. calculateMappingItem.EdbCode = edbCode
  63. calculateMappingItem.EdbInfoId = edbInfo.EdbInfoId
  64. calculateMappingItem.FromEdbInfoId = fromEdbInfo.EdbInfoId
  65. calculateMappingItem.FromEdbCode = fromEdbInfo.EdbCode
  66. calculateMappingItem.FromEdbName = fromEdbInfo.EdbName
  67. calculateMappingItem.FromSource = fromEdbInfo.Source
  68. calculateMappingItem.FromSourceName = fromEdbInfo.SourceName
  69. calculateMappingItem.FromTag = ""
  70. calculateMappingItem.Source = edbInfo.Source
  71. calculateMappingItem.SourceName = edbInfo.SourceName
  72. calculateMappingItem.FromSubSource = edbInfo.SubSource
  73. _, err = to.Insert(calculateMappingItem)
  74. if err != nil {
  75. return
  76. }
  77. }
  78. } else {
  79. edbInfo, err = GetEdbInfoById(req.EdbInfoId)
  80. if err != nil {
  81. return
  82. }
  83. dataTableName := GetEdbDataTableName(utils.DATA_SOURCE_CALCULATE_CJJX, utils.DATA_SUB_SOURCE_EDB)
  84. fmt.Println("dataTableName:", dataTableName)
  85. deleteSql := ` DELETE FROM %s WHERE edb_info_id=? `
  86. deleteSql = fmt.Sprintf(deleteSql, dataTableName)
  87. _, err = to.Raw(deleteSql, req.EdbInfoId).Exec()
  88. if err != nil {
  89. return
  90. }
  91. }
  92. //计算数据
  93. err = refreshAllCalculateCjjx(to, edbInfo.EdbInfoId, edbInfo.Source, edbInfo.SubSource, fromEdbInfo, edbInfo.EdbCode, "", "", edbInfo.Calendar, formulaInt)
  94. return
  95. }
  96. // EditCalculateCjjx 超季节性
  97. func EditCalculateCjjx(req *EdbInfoCalculateBatchEditReq, edbInfo, fromEdbInfo *EdbInfo, formulaInt int) (err error) {
  98. o := orm.NewOrm()
  99. to, err := o.Begin()
  100. if err != nil {
  101. return
  102. }
  103. defer func() {
  104. if err != nil {
  105. fmt.Println("EditCalculateCjjx,Err:" + err.Error())
  106. _ = to.Rollback()
  107. } else {
  108. _ = to.Commit()
  109. }
  110. }()
  111. oldCalculateFormula := edbInfo.CalculateFormula //原先的n值
  112. oldCalendar := edbInfo.Calendar //原先的公历、农历
  113. edbInfo, err = GetEdbInfoById(req.EdbInfoId)
  114. if err != nil {
  115. return
  116. }
  117. //修改指标信息
  118. edbInfo.EdbName = req.EdbName
  119. edbInfo.EdbNameSource = req.EdbName
  120. edbInfo.Frequency = req.Frequency
  121. edbInfo.Unit = req.Unit
  122. edbInfo.ClassifyId = req.ClassifyId
  123. edbInfo.CalculateFormula = req.Formula
  124. edbInfo.Calendar = req.Calendar
  125. edbInfo.EdbNameEn = req.EdbNameEn
  126. edbInfo.UnitEn = req.UnitEn
  127. edbInfo.ModifyTime = time.Now()
  128. _, err = to.Update(edbInfo, "EdbName", "EdbNameSource", "Frequency", "Unit", "ClassifyId", "CalculateFormula", "Calendar", "ModifyTime", "EdbNameEn", "UnitEn")
  129. if err != nil {
  130. return
  131. }
  132. //判断计算指标是否被更换
  133. var existCondition string
  134. var existPars []interface{}
  135. existCondition += " AND edb_info_id=? AND from_edb_info_id=? "
  136. existPars = append(existPars, edbInfo.EdbInfoId, req.FromEdbInfoId)
  137. count, err := GetEdbInfoCalculateCountByCondition(existCondition, existPars)
  138. if err != nil {
  139. err = errors.New("判断指标是否改变失败,Err:" + err.Error())
  140. return
  141. }
  142. if count > 0 && oldCalculateFormula == req.Formula && oldCalendar == req.Calendar { // 指标未被替换,同时N值未修改,同时公历/农历未变更,无需重新计算
  143. return
  144. }
  145. // 指标被替换,或者N值未修改,那么需要重新计算数据
  146. //基础指标被替换了,需要删除原先的 计算指标关联的,基础指标的关联关系
  147. if count <= 0 {
  148. // 需要删除原先的 计算指标关联的,基础指标的关联关系
  149. sql := ` DELETE FROM edb_info_calculate_mapping WHERE edb_info_id = ? `
  150. _, err = to.Raw(sql, edbInfo.EdbInfoId).Exec()
  151. if err != nil {
  152. return
  153. }
  154. // 添加新的关联关系
  155. {
  156. calculateMappingItem := &EdbInfoCalculateMapping{
  157. EdbInfoCalculateMappingId: 0,
  158. EdbInfoId: edbInfo.EdbInfoId,
  159. Source: utils.DATA_SOURCE_CALCULATE_CJJX,
  160. SourceName: "超季节性",
  161. EdbCode: edbInfo.EdbCode,
  162. FromEdbInfoId: fromEdbInfo.EdbInfoId,
  163. FromEdbCode: fromEdbInfo.EdbCode,
  164. FromEdbName: fromEdbInfo.EdbName,
  165. FromSource: fromEdbInfo.Source,
  166. FromSourceName: fromEdbInfo.SourceName,
  167. FromTag: "",
  168. Sort: 1,
  169. CreateTime: time.Now(),
  170. ModifyTime: time.Now(),
  171. FromSubSource: fromEdbInfo.SubSource,
  172. }
  173. _, err = to.Insert(calculateMappingItem)
  174. if err != nil {
  175. return
  176. }
  177. }
  178. }
  179. //清空原有数据
  180. tableName := GetEdbDataTableName(edbInfo.Source, edbInfo.SubSource)
  181. sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? `, tableName)
  182. _, err = to.Raw(sql, edbInfo.EdbInfoId).Exec()
  183. if err != nil {
  184. return
  185. }
  186. //计算数据
  187. err = refreshAllCalculateCjjx(to, edbInfo.EdbInfoId, edbInfo.Source, edbInfo.SubSource, fromEdbInfo, edbInfo.EdbCode, "", "", edbInfo.Calendar, formulaInt)
  188. return
  189. }
  190. // RefreshAllCalculateCjjx 刷新全部超季节性数据
  191. func RefreshAllCalculateCjjx(edbInfoId, source, subSource int, fromEdbInfo *EdbInfo, edbCode, startDate, endDate, calendar string, formulaInt int) (err error) {
  192. o := orm.NewOrm()
  193. to, err := o.Begin()
  194. if err != nil {
  195. return
  196. }
  197. defer func() {
  198. if err != nil {
  199. fmt.Println("RefreshAllCalculateCjjx,Err:" + err.Error())
  200. _ = to.Rollback()
  201. } else {
  202. _ = to.Commit()
  203. }
  204. }()
  205. // 重新计算
  206. err = refreshAllCalculateCjjx(to, edbInfoId, source, subSource, fromEdbInfo, edbCode, startDate, endDate, calendar, formulaInt)
  207. return
  208. }
  209. // refreshAllCalculateCjjx 刷新全部超季节性数据
  210. func refreshAllCalculateCjjx(to orm.TxOrmer, edbInfoId, source, subSource int, fromEdbInfo *EdbInfo, edbCode, startDate, endDate, calendar string, formulaInt int) (err error) {
  211. if err != nil {
  212. return
  213. }
  214. edbInfoIdStr := strconv.Itoa(edbInfoId)
  215. //计算数据
  216. dataList, err := GetEdbDataListAllByTo(to, fromEdbInfo.Source, fromEdbInfo.SubSource, FindEdbDataListAllCond{
  217. EdbInfoId: fromEdbInfo.EdbInfoId,
  218. }, 1)
  219. if err != nil {
  220. return err
  221. }
  222. var dateArr []string
  223. dataMap := make(map[string]*EdbInfoSearchData)
  224. for _, v := range dataList {
  225. dateArr = append(dateArr, v.DataTime)
  226. dataMap[v.DataTime] = v
  227. }
  228. // 通过插值法补全所有数据(包含周末)
  229. handleDataMap := make(map[string]float64)
  230. _, err = HandleDataByLinearRegression(dataList, handleDataMap)
  231. if err != nil {
  232. return
  233. }
  234. //获取指标所有数据
  235. existDataList := make([]*EdbData, 0)
  236. dataTableName := GetEdbDataTableName(source, subSource)
  237. sql := `SELECT * FROM %s WHERE edb_info_id=? `
  238. sql = fmt.Sprintf(sql, dataTableName)
  239. _, err = to.Raw(sql, edbInfoId).QueryRows(&existDataList)
  240. if err != nil {
  241. return err
  242. }
  243. existDataMap := make(map[string]string)
  244. removeDataTimeMap := make(map[string]int) //需要移除的日期数据
  245. for _, v := range existDataList {
  246. existDataMap[edbCode+v.DataTime] = v.Value
  247. removeDataTimeMap[v.DataTime] = 1
  248. }
  249. addSql := ` INSERT INTO edb_data_calculate_cjjx(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values `
  250. var isAdd bool
  251. //日度/周度/季度/月度
  252. //isCompatibility := false //是否向上下兼容35天
  253. //if utils.InArrayByStr([]string{"日度", "周度", "季度", "月度"}, fromEdbInfo.Frequency) {
  254. // isCompatibility = true
  255. //}
  256. // 每个年份的日期数据需要平移的天数
  257. moveDayMap := make(map[int]int, 0) // 每个年份的春节公历
  258. var lastDataDay time.Time
  259. if len(dataList) > 0 {
  260. lastDataDay, _ = time.ParseInLocation(utils.FormatDate, dataList[0].DataTime, time.Local)
  261. }
  262. for _, av := range dateArr {
  263. // 如果遇到闰二月,如2.29,去掉该天数据
  264. if strings.Contains(av, "02-29") {
  265. continue
  266. }
  267. currentDate, tmpErr := time.ParseInLocation(utils.FormatDate, av, time.Local)
  268. if tmpErr != nil {
  269. err = tmpErr
  270. return
  271. }
  272. //农历的超季节性运算,只计算11月--次年5月,分段计算,与数据区间和N数值有关
  273. if calendar == "农历" && currentDate.Month() > 5 && currentDate.Month() < 11 {
  274. continue
  275. }
  276. currentItem, ok := dataMap[av]
  277. // 找不到数据就退出当前循环,进入下一循环
  278. if !ok {
  279. continue
  280. }
  281. pastValueList := make([]float64, 0) // 过去几期的数据
  282. //当前日期
  283. pastValueList = append(pastValueList, currentItem.Value)
  284. for i := 1; i < formulaInt; i++ {
  285. //前几年当天公历的日期
  286. historyPreDate := currentDate.AddDate(-i, 0, 0)
  287. moveDay := 0
  288. if calendar == "农历" {
  289. if tmpMoveDay, ok := moveDayMap[historyPreDate.Year()]; !ok {
  290. moveDay, err = getMoveDay(lastDataDay, historyPreDate)
  291. if err != nil {
  292. return
  293. }
  294. } else {
  295. moveDay = tmpMoveDay
  296. }
  297. // 移动天数到对应农历 的 公历 日期
  298. historyPreDate = historyPreDate.AddDate(0, 0, moveDay)
  299. }
  300. historyPreDateStr := historyPreDate.Format(utils.FormatDate)
  301. if tmpValue, ok := handleDataMap[historyPreDateStr]; ok { //上一年同期找到
  302. pastValueList = append(pastValueList, tmpValue)
  303. }
  304. //if findItem, ok := dataMap[hisoryPreDateStr]; ok { //上一年同期找到
  305. // pastValueList = append(pastValueList, findItem.Value)
  306. //} else if isCompatibility { // 如果需要兼容上下35天
  307. // nextDateDay := hisoryPreDate
  308. // preDateDay := hisoryPreDate
  309. // for i := 0; i < 35; i++ {
  310. // nextDateDayStr := nextDateDay.Format(utils.FormatDate)
  311. // if findItem, ok := dataMap[nextDateDayStr]; ok { //上一年同期->下一个月找到
  312. // pastValueList = append(pastValueList, findItem.Value)
  313. // break
  314. // } else {
  315. // preDateDayStr := preDateDay.Format(utils.FormatDate)
  316. // if findItem, ok := dataMap[preDateDayStr]; ok { //上一年同期->上一个月找到
  317. // pastValueList = append(pastValueList, findItem.Value)
  318. // break
  319. // }
  320. // }
  321. // nextDateDay = nextDateDay.AddDate(0, 0, 1)
  322. // preDateDay = preDateDay.AddDate(0, 0, -1)
  323. // }
  324. //}
  325. }
  326. if len(pastValueList) == formulaInt {
  327. delete(removeDataTimeMap, av) //将待删除的日期给移除
  328. val := CjjxSub(currentItem.Value, pastValueList)
  329. if existVal, ok := existDataMap[edbCode+av]; !ok {
  330. timestamp := currentDate.UnixNano() / 1e6
  331. timestampStr := fmt.Sprintf("%d", timestamp)
  332. addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val)
  333. isAdd = true
  334. } else {
  335. existValDecimal, err := decimal.NewFromString(existVal)
  336. existStr := existValDecimal.String()
  337. if existStr != val {
  338. sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? `
  339. sql = fmt.Sprintf(sql, dataTableName)
  340. _, err = to.Raw(sql, val, edbInfoId, av).Exec()
  341. if err != nil {
  342. return err
  343. }
  344. }
  345. }
  346. }
  347. existDataMap[edbCode+av] = av
  348. }
  349. //删除已经不存在的超季节性指标数据(由于该指标当日的数据删除了)
  350. {
  351. removeDateList := make([]string, 0)
  352. for dateTime := range removeDataTimeMap {
  353. removeDateList = append(removeDateList, dateTime)
  354. }
  355. removeNum := len(removeDateList)
  356. if removeNum > 0 {
  357. //如果拼接指标变更了,那么需要删除所有的指标数据
  358. tableName := GetEdbDataTableName(source, subSource)
  359. sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? and data_time in (`+utils.GetOrmInReplace(removeNum)+`) `, tableName)
  360. _, err = to.Raw(sql, edbInfoId, removeDateList).Exec()
  361. if err != nil {
  362. err = fmt.Errorf("删除不存在的超季节性指标数据失败,Err:" + err.Error())
  363. return
  364. }
  365. }
  366. }
  367. if isAdd {
  368. addSql = strings.TrimRight(addSql, ",")
  369. _, err = to.Raw(addSql).Exec()
  370. if err != nil {
  371. return err
  372. }
  373. }
  374. return
  375. }
  376. // getMoveDay 获取两个日期的平移天数
  377. func getMoveDay(lastDataDay, currentDataDay time.Time) (moveDay int, err error) {
  378. if lastDataDay.Month() >= 11 { //最新数据的日期如果大于等于11月份,那么用的是下一年的春节
  379. lastDataDay = lastDataDay.AddDate(1, 0, 0)
  380. }
  381. currentYear := lastDataDay.Year()
  382. currentYearCjnl := fmt.Sprintf("%d-01-01", currentYear) //当年的春节农历
  383. currentYearCjgl := solarlunar.LunarToSolar(currentYearCjnl, false) //当年的春节公历
  384. currentYearCjglTime, tmpErr := time.ParseInLocation(utils.FormatDate, currentYearCjgl, time.Local)
  385. if tmpErr != nil {
  386. err = errors.New("当前春节公历日期转换失败:" + tmpErr.Error())
  387. return
  388. }
  389. //指定年的春节农历
  390. tmpYearCjnl := fmt.Sprintf("%d-01-01", currentDataDay.Year())
  391. //指定年的春节公历
  392. tmpYearCjgl := solarlunar.LunarToSolar(tmpYearCjnl, false)
  393. tmpYearCjglTime, tmpErr := time.ParseInLocation(utils.FormatDate, tmpYearCjgl, time.Local)
  394. if tmpErr != nil {
  395. err = errors.New(fmt.Sprintf("%d公历日期转换失败:%s", currentDataDay.Year(), tmpErr.Error()))
  396. return
  397. }
  398. // 将两个日期同步到同一年,然后计算两个日期相差的天数
  399. tmpCurrentYearCjglTime := currentYearCjglTime.AddDate(currentDataDay.Year()-currentYear, 0, 0)
  400. moveDay = utils.GetTimeSubDay(tmpYearCjglTime, tmpCurrentYearCjglTime)
  401. return
  402. }
  403. // CjjxSub 计算超季节性值
  404. // 计算公式=现值-过去n年(包括今年)均值,n为取数个数,需大于等于1;
  405. // 举例:A指标 2022-10-13值100,2021-10-13值120,2020-10-13值110,设置n=3,则“超季节性”指标计算值为100-(100+120+110)/3=-10。
  406. func CjjxSub(currValue float64, pastValue []float64) (value string) {
  407. num := len(pastValue)
  408. if num == 0 {
  409. return
  410. }
  411. numDecimal := decimal.NewFromInt(int64(num))
  412. af := decimal.NewFromFloat(currValue)
  413. //fmt.Println(af)
  414. bf := decimal.NewFromFloat(pastValue[0])
  415. for k := 1; k < num; k++ {
  416. tmpVal := decimal.NewFromFloat(pastValue[k])
  417. bf = bf.Add(tmpVal)
  418. }
  419. val, _ := af.Sub(bf.Div(numDecimal)).Float64()
  420. //valStr := utils.SubFloatToString(val, 4)
  421. valStr := decimal.NewFromFloat(val).Round(4).String()
  422. return valStr
  423. }