edb_data_calculate_ljzzy.go 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398
  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/shopspring/decimal"
  8. "strconv"
  9. "strings"
  10. "time"
  11. )
  12. // AddCalculateLjzzy 累计值转月
  13. func AddCalculateLjzzy(req *EdbInfoCalculateBatchSaveReq, fromEdbInfo *EdbInfo, edbCode, uniqueCode string, sysUserId int, sysUserRealName string) (edbInfo *EdbInfo, err error) {
  14. o := orm.NewOrm()
  15. to, err := o.Begin()
  16. if err != nil {
  17. return
  18. }
  19. defer func() {
  20. if err != nil {
  21. fmt.Println("AddCalculateLjzzy,Err:" + err.Error())
  22. _ = to.Rollback()
  23. } else {
  24. _ = to.Commit()
  25. }
  26. }()
  27. if req.EdbInfoId <= 0 {
  28. edbInfo = new(EdbInfo)
  29. edbInfo.Source = utils.DATA_SOURCE_CALCULATE_LJZZY
  30. edbInfo.SourceName = "累计值转月值"
  31. edbInfo.EdbCode = edbCode
  32. edbInfo.EdbName = req.EdbName
  33. edbInfo.EdbNameSource = req.EdbName
  34. edbInfo.Frequency = req.Frequency
  35. edbInfo.Unit = req.Unit
  36. edbInfo.ClassifyId = req.ClassifyId
  37. edbInfo.SysUserId = sysUserId
  38. edbInfo.SysUserRealName = sysUserRealName
  39. edbInfo.CreateTime = time.Now()
  40. edbInfo.ModifyTime = time.Now()
  41. edbInfo.UniqueCode = uniqueCode
  42. edbInfo.CalculateFormula = req.Formula
  43. edbInfo.EdbNameEn = req.EdbName
  44. edbInfo.UnitEn = req.Unit
  45. edbInfo.EdbType = 2
  46. edbInfo.Sort = GetAddEdbMaxSortByClassifyId(req.ClassifyId, utils.EDB_INFO_TYPE)
  47. newEdbInfoId, tmpErr := to.Insert(edbInfo)
  48. if tmpErr != nil {
  49. err = tmpErr
  50. return
  51. }
  52. edbInfo.EdbInfoId = int(newEdbInfoId)
  53. //关联关系
  54. {
  55. calculateMappingItem := new(EdbInfoCalculateMapping)
  56. calculateMappingItem.CreateTime = time.Now()
  57. calculateMappingItem.ModifyTime = time.Now()
  58. calculateMappingItem.Sort = 1
  59. calculateMappingItem.EdbCode = edbCode
  60. calculateMappingItem.EdbInfoId = edbInfo.EdbInfoId
  61. calculateMappingItem.FromEdbInfoId = fromEdbInfo.EdbInfoId
  62. calculateMappingItem.FromEdbCode = fromEdbInfo.EdbCode
  63. calculateMappingItem.FromEdbName = fromEdbInfo.EdbName
  64. calculateMappingItem.FromSource = fromEdbInfo.Source
  65. calculateMappingItem.FromSourceName = fromEdbInfo.SourceName
  66. calculateMappingItem.FromTag = ""
  67. calculateMappingItem.Source = edbInfo.Source
  68. calculateMappingItem.SourceName = edbInfo.SourceName
  69. calculateMappingItem.FromSubSource = edbInfo.SubSource
  70. _, err = to.Insert(calculateMappingItem)
  71. if err != nil {
  72. return
  73. }
  74. }
  75. } else {
  76. edbInfo, err = GetEdbInfoById(req.EdbInfoId)
  77. if err != nil {
  78. return
  79. }
  80. dataTableName := GetEdbDataTableName(utils.DATA_SOURCE_CALCULATE_LJZZY, utils.DATA_SUB_SOURCE_EDB)
  81. deleteSql := ` DELETE FROM %s WHERE edb_info_id=? `
  82. deleteSql = fmt.Sprintf(deleteSql, dataTableName)
  83. _, err = to.Raw(deleteSql, req.EdbInfoId).Exec()
  84. if err != nil {
  85. return
  86. }
  87. }
  88. //计算数据
  89. err = refreshAllCalculateLjzzy(to, edbInfo.EdbInfoId, edbInfo.Source, edbInfo.SubSource, fromEdbInfo, edbInfo.EdbCode, "", "")
  90. return
  91. }
  92. // EditCalculateLjzzy 编辑累计值转月数据
  93. func EditCalculateLjzzy(edbInfo *EdbInfo, req *EdbInfoCalculateBatchEditReq, fromEdbInfo *EdbInfo) (err error) {
  94. o := orm.NewOrm()
  95. to, err := o.Begin()
  96. if err != nil {
  97. return
  98. }
  99. defer func() {
  100. if err != nil {
  101. fmt.Println("EditCalculateLjzzy,Err:" + err.Error())
  102. _ = to.Rollback()
  103. } else {
  104. _ = to.Commit()
  105. }
  106. }()
  107. //修改指标信息
  108. edbInfo.EdbName = req.EdbName
  109. edbInfo.EdbNameEn = req.EdbNameEn
  110. edbInfo.UnitEn = req.UnitEn
  111. edbInfo.EdbNameSource = req.EdbName
  112. edbInfo.Frequency = req.Frequency
  113. edbInfo.Unit = req.Unit
  114. edbInfo.ClassifyId = req.ClassifyId
  115. edbInfo.EdbNameEn = req.EdbNameEn
  116. edbInfo.UnitEn = req.UnitEn
  117. edbInfo.ModifyTime = time.Now()
  118. _, err = to.Update(edbInfo, "EdbName", "EdbNameSource", "Frequency", "Unit", "ClassifyId", "ModifyTime", "EdbNameEn", "UnitEn")
  119. if err != nil {
  120. return
  121. }
  122. var existCondition string
  123. var existPars []interface{}
  124. existCondition += " AND edb_info_id=? AND from_edb_info_id=? "
  125. existPars = append(existPars, edbInfo.EdbInfoId, req.FromEdbInfoId)
  126. //判断计算指标是否被更换
  127. count, err := GetEdbInfoCalculateCountByCondition(existCondition, existPars)
  128. if err != nil {
  129. err = errors.New("判断指标是否改变失败,Err:" + err.Error())
  130. return
  131. }
  132. if count > 0 { // 指标未被替换,无需重新计算
  133. return
  134. }
  135. //删除,计算指标关联的,基础指标的关联关系
  136. sql := ` DELETE FROM edb_info_calculate_mapping WHERE edb_info_id = ? `
  137. _, err = to.Raw(sql, edbInfo.EdbInfoId).Exec()
  138. if err != nil {
  139. return
  140. }
  141. //清空原有数据
  142. sql = ` DELETE FROM edb_data_calculate_ljzzy WHERE edb_info_id = ? `
  143. _, err = to.Raw(sql, edbInfo.EdbInfoId).Exec()
  144. if err != nil {
  145. return
  146. }
  147. //关联关系
  148. {
  149. calculateMappingItem := &EdbInfoCalculateMapping{
  150. EdbInfoCalculateMappingId: 0,
  151. EdbInfoId: edbInfo.EdbInfoId,
  152. Source: utils.DATA_SOURCE_CALCULATE_LJZZY,
  153. SourceName: "累计值转月",
  154. EdbCode: edbInfo.EdbCode,
  155. FromEdbInfoId: fromEdbInfo.EdbInfoId,
  156. FromEdbCode: fromEdbInfo.EdbCode,
  157. FromEdbName: fromEdbInfo.EdbName,
  158. FromSource: fromEdbInfo.Source,
  159. FromSourceName: fromEdbInfo.SourceName,
  160. FromTag: "",
  161. Sort: 1,
  162. CreateTime: time.Now(),
  163. ModifyTime: time.Now(),
  164. FromSubSource: fromEdbInfo.SubSource,
  165. }
  166. _, err = to.Insert(calculateMappingItem)
  167. if err != nil {
  168. return
  169. }
  170. }
  171. //计算数据
  172. err = refreshAllCalculateLjzzy(to, edbInfo.EdbInfoId, edbInfo.Source, edbInfo.SubSource, fromEdbInfo, edbInfo.EdbCode, "", "")
  173. return
  174. }
  175. // RefreshAllCalculateLjzzy 刷新全部累计值转月数据
  176. func RefreshAllCalculateLjzzy(edbInfoId, source, subSource int, fromEdbInfo *EdbInfo, edbCode, startDate, endDate string) (err error) {
  177. o := orm.NewOrm()
  178. to, err := o.Begin()
  179. if err != nil {
  180. return
  181. }
  182. defer func() {
  183. if err != nil {
  184. fmt.Println("RefreshAllCalculateLjzzy,Err:" + err.Error())
  185. _ = to.Rollback()
  186. } else {
  187. _ = to.Commit()
  188. }
  189. }()
  190. // 计算数据
  191. err = refreshAllCalculateLjzzy(to, edbInfoId, source, subSource, fromEdbInfo, edbCode, startDate, endDate)
  192. return
  193. }
  194. func refreshAllCalculateLjzzy(to orm.TxOrmer, edbInfoId, source, subSource int, fromEdbInfo *EdbInfo, edbCode, startDate, endDate string) (err error) {
  195. edbInfoIdStr := strconv.Itoa(edbInfoId)
  196. //计算数据
  197. dataList, err := GetEdbDataListAllByTo(to, fromEdbInfo.Source, fromEdbInfo.SubSource,
  198. FindEdbDataListAllCond{
  199. EdbInfoId: fromEdbInfo.EdbInfoId,
  200. StartDataTime: startDate,
  201. StartDataTimeCond: ">=",
  202. }, 1)
  203. if err != nil {
  204. return err
  205. }
  206. yearMap := make(map[int]map[int]*EdbInfoSearchData)
  207. dataLen := len(dataList)
  208. for i := 0; i < dataLen; i++ {
  209. item := dataList[i]
  210. //日其中获取年
  211. itemDate, err := time.ParseInLocation(utils.FormatDate, item.DataTime, time.Local)
  212. if err != nil {
  213. return err
  214. }
  215. year := itemDate.Year()
  216. month := int(itemDate.Month())
  217. if monthMap, yok := yearMap[year]; yok {
  218. monthMap[month] = item
  219. yearMap[year] = monthMap
  220. } else {
  221. monthMap = make(map[int]*EdbInfoSearchData)
  222. monthMap[month] = item
  223. yearMap[year] = monthMap
  224. }
  225. }
  226. addSql := ` INSERT INTO edb_data_calculate_ljzzy(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values `
  227. var isAdd bool
  228. //获取指标所有数据
  229. existDataList := make([]*EdbData, 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. dataMap := make(map[string]string)
  238. for _, v := range existDataList {
  239. dataMap[v.DataTime] = v.Value
  240. }
  241. existDataMap := make(map[string]string)
  242. for yk, yv := range yearMap {
  243. _, oneMonthOk := yv[1]
  244. _, twoMonthOk := yv[2]
  245. if !oneMonthOk && !twoMonthOk {
  246. continue
  247. }
  248. for i := 1; i <= 12; i++ {
  249. fmt.Println(yk, i, yv[i])
  250. dataCurrentItem := yv[i]
  251. var date string
  252. var val float64
  253. if dataCurrentItem != nil {
  254. if i == 1 || i == 2 {
  255. if _, mok := yv[1]; mok { //1月有值
  256. if i == 1 {
  257. date = dataCurrentItem.DataTime
  258. val, _ = decimal.NewFromFloat(dataCurrentItem.Value).Float64() //a.Div(b).Float64()
  259. }
  260. if i == 2 {
  261. dataOneItem := yv[1]
  262. date = dataCurrentItem.DataTime
  263. twoMonth := decimal.NewFromFloat(dataCurrentItem.Value)
  264. oneMonth := decimal.NewFromFloat(dataOneItem.Value)
  265. val, _ = twoMonth.Sub(oneMonth).Float64()
  266. }
  267. } else { //1月无值
  268. dataTwoItem := yv[2]
  269. if i == 1 {
  270. date = strconv.Itoa(yk) + "-01-31"
  271. a := decimal.NewFromFloat(dataTwoItem.Value)
  272. b := decimal.NewFromFloat(2.0)
  273. val, _ = a.Div(b).Float64()
  274. }
  275. if i == 2 {
  276. //1月无值:1月=2月/2
  277. {
  278. date = strconv.Itoa(yk) + "-01-31"
  279. a := decimal.NewFromFloat(dataTwoItem.Value)
  280. b := decimal.NewFromFloat(2.0)
  281. val, _ = a.Div(b).Float64()
  282. tmpSql, newAdd, tmpErr := calculateLjzzy(edbInfoId, date, edbInfoIdStr, edbCode, dataTableName, addSql, val, dataMap, existDataMap, to)
  283. if !isAdd {
  284. isAdd = newAdd
  285. }
  286. addSql = tmpSql
  287. if tmpErr != nil {
  288. return tmpErr
  289. }
  290. }
  291. //end 1月无值
  292. date = dataCurrentItem.DataTime
  293. a := decimal.NewFromFloat(dataTwoItem.Value)
  294. b := decimal.NewFromFloat(2.0)
  295. val, _ = a.Div(b).Float64()
  296. }
  297. }
  298. } else {
  299. dataPreItem := yv[i-1]
  300. if dataCurrentItem != nil && dataPreItem != nil {
  301. date = dataCurrentItem.DataTime
  302. //val = dataCurrentItem.Value - dataPreItem.Value
  303. a := decimal.NewFromFloat(dataCurrentItem.Value)
  304. b := decimal.NewFromFloat(dataPreItem.Value)
  305. val, _ = a.Sub(b).Float64()
  306. }
  307. }
  308. }
  309. if date != "" {
  310. //saveValue := utils.SubFloatToString(val, 4)
  311. ////判断数据是否存在
  312. //if existVal, ok := dataMap[date]; !ok {
  313. // dataTime, _ := time.ParseInLocation(utils.FormatDate, date, time.Local)
  314. // timestamp := dataTime.UnixNano() / 1e6
  315. // timeStr := fmt.Sprintf("%d", timestamp)
  316. // if _, existOk := existDataMap[date]; !existOk {
  317. // addSql += GetAddSql(edbInfoIdStr, edbCode, date, timeStr, saveValue)
  318. // isAdd = true
  319. // }
  320. // existDataMap[date] = date
  321. //} else {
  322. // if existVal != saveValue {
  323. // sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? `
  324. // sql = fmt.Sprintf(sql, dataTableName)
  325. // _, err = to.Raw(sql, saveValue, edbInfoId, date).Exec()
  326. // if err != nil {
  327. // return err
  328. // }
  329. // }
  330. //}
  331. tmpSql, newAdd, tmpErr := calculateLjzzy(edbInfoId, date, edbInfoIdStr, edbCode, dataTableName, addSql, val, dataMap, existDataMap, to)
  332. if !isAdd {
  333. isAdd = newAdd
  334. }
  335. addSql = tmpSql
  336. if tmpErr != nil {
  337. return tmpErr
  338. }
  339. }
  340. }
  341. }
  342. if isAdd {
  343. addSql = strings.TrimRight(addSql, ",")
  344. _, err = to.Raw(addSql).Exec()
  345. if err != nil {
  346. fmt.Println("RefreshAllCalculateLjzzy add Err", err.Error())
  347. return
  348. }
  349. }
  350. return
  351. }
  352. func calculateLjzzy(edbInfoId int, date, edbInfoIdStr, edbCode, dataTableName, addSql string, val float64, dataMap, existDataMap map[string]string, to orm.TxOrmer) (newSql string, isAdd bool, err error) {
  353. newSql = addSql
  354. saveValue := utils.SubFloatToString(val, 4)
  355. //判断数据是否存在
  356. if existVal, ok := dataMap[date]; !ok {
  357. dataTime, _ := time.ParseInLocation(utils.FormatDate, date, time.Local)
  358. timestamp := dataTime.UnixNano() / 1e6
  359. timeStr := fmt.Sprintf("%d", timestamp)
  360. if _, existOk := existDataMap[date]; !existOk {
  361. newSql += GetAddSql(edbInfoIdStr, edbCode, date, timeStr, saveValue)
  362. isAdd = true
  363. }
  364. existDataMap[date] = date
  365. } else {
  366. if existVal != saveValue {
  367. sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? `
  368. sql = fmt.Sprintf(sql, dataTableName)
  369. _, err = to.Raw(sql, saveValue, edbInfoId, date).Exec()
  370. }
  371. }
  372. return
  373. }