base_from_usda_fas.go 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400
  1. package models
  2. import (
  3. "eta/eta_index_lib/global"
  4. "eta/eta_index_lib/utils"
  5. "fmt"
  6. "gorm.io/gorm"
  7. "strconv"
  8. "strings"
  9. "time"
  10. )
  11. // BaseFromUsdaFas 美国农业部
  12. type BaseFromUsdaFas struct{}
  13. type BaseFromUsdaFasData struct {
  14. BaseFromUsdaFasDataId int `gorm:"column:base_from_usda_fas_data_id;primaryKey"`
  15. //BaseFromUsdaFasDataId int `orm:"column(base_from_usda_fas_data_id);pk"`
  16. BaseFromUsdaFasIndexId int
  17. IndexCode string
  18. DataTime string
  19. Value string
  20. CreateTime time.Time
  21. ModifyTime time.Time
  22. DataTimestamp int64
  23. }
  24. func (e *BaseFromUsdaFasData) AfterFind(db *gorm.DB) (err error) {
  25. e.DataTime = utils.GormDateStrToDateStr(e.DataTime)
  26. return
  27. }
  28. func GetBaseFromUsdaFasDataByCondition(condition string, pars []interface{}) (list []*BaseFromUsdaFasData, err error) {
  29. //o := orm.NewOrm()
  30. sql := `SELECT * FROM base_from_usda_fas_data WHERE 1=1 `
  31. if condition != "" {
  32. sql += condition
  33. }
  34. //_, err = o.Raw(sql, pars).QueryRows(&list)
  35. err = global.DEFAULT_DB.Raw(sql, pars...).Find(&list).Error
  36. return
  37. }
  38. // Add 添加
  39. func (obj BaseFromUsdaFas) Add(edbCode string) (err error) {
  40. //o := orm.NewOrm()
  41. var condition string
  42. var pars []interface{}
  43. if edbCode != "" {
  44. condition += " AND index_code=? "
  45. pars = append(pars, edbCode)
  46. }
  47. UsdaFasBaseDataAll, err := GetBaseFromUsdaFasDataByCondition(condition, pars)
  48. if err != nil && !utils.IsErrNoRow(err) {
  49. return
  50. }
  51. var isAdd bool
  52. addSql := ` INSERT INTO edb_data_usda_fas(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values `
  53. existMap := make(map[string]string)
  54. for _, sv := range UsdaFasBaseDataAll {
  55. eDate := sv.DataTime
  56. dataTime, err := time.Parse(utils.FormatDate, eDate)
  57. if err != nil {
  58. return err
  59. }
  60. timestamp := dataTime.UnixNano() / 1e6
  61. timeStr := fmt.Sprintf("%d", timestamp)
  62. if _, ok := existMap[eDate]; !ok {
  63. addSql += GetAddSql("0", edbCode, eDate, timeStr, sv.Value)
  64. isAdd = true
  65. }
  66. existMap[eDate] = sv.Value
  67. }
  68. if isAdd {
  69. addSql = strings.TrimRight(addSql, ",")
  70. utils.FileLog.Info("addSql:" + addSql)
  71. //_,err=o.Raw(addSql).Exec()
  72. err = global.DEFAULT_DB.Exec(addSql).Error
  73. if err != nil {
  74. return err
  75. }
  76. }
  77. return
  78. }
  79. // Refresh 刷新涌益咨询指标数据
  80. func (obj BaseFromUsdaFas) Refresh(edbInfoId int, edbCode, startDate string) (err error) {
  81. source := obj.GetSource()
  82. //o := orm.NewOrm()
  83. if err != nil {
  84. return
  85. }
  86. edbInfoIdStr := strconv.Itoa(edbInfoId)
  87. //计算数据
  88. var condition string
  89. var pars []interface{}
  90. if edbCode != "" {
  91. condition += " AND index_code=? "
  92. pars = append(pars, edbCode)
  93. }
  94. if startDate != "" {
  95. condition += " AND data_time>=? "
  96. pars = append(pars, startDate)
  97. }
  98. UsdaFasDataList, err := GetBaseFromUsdaFasDataByCondition(condition, pars)
  99. if err != nil {
  100. return
  101. }
  102. // 真实数据的最大日期 , 插入规则配置的日期
  103. var realDataMaxDate, edbDataInsertConfigDate time.Time
  104. var edbDataInsertConfig *EdbDataInsertConfig
  105. var isFindConfigDateRealData bool //是否找到配置日期的实际数据的值
  106. {
  107. edbDataInsertConfig, err = GetEdbDataInsertConfigByEdbId(edbInfoId)
  108. if err != nil && !utils.IsErrNoRow(err) {
  109. return
  110. }
  111. if edbDataInsertConfig != nil {
  112. edbDataInsertConfigDate = edbDataInsertConfig.Date
  113. }
  114. }
  115. var existCondition string
  116. var existPars []interface{}
  117. existCondition += " AND edb_info_id=? "
  118. existPars = append(existPars, edbInfoId)
  119. if startDate != "" {
  120. existCondition += " AND data_time>=? "
  121. existPars = append(existPars, startDate)
  122. }
  123. existList, err := GetEdbDataByCondition(source, 0, existCondition, existPars)
  124. if err != nil {
  125. return err
  126. }
  127. existMap := make(map[string]*EdbInfoSearchData)
  128. for _, v := range existList {
  129. existMap[v.DataTime] = v
  130. }
  131. addSql := ` INSERT INTO edb_data_usda_fas(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values `
  132. var isAdd bool
  133. for _, v := range UsdaFasDataList {
  134. item := v
  135. eDate := item.DataTime
  136. dataTime, err := time.ParseInLocation(utils.FormatDate, eDate, time.Local)
  137. if err != nil {
  138. return err
  139. }
  140. if findItem, ok := existMap[v.DataTime]; !ok {
  141. sValue := item.Value
  142. timestamp := dataTime.UnixNano() / 1e6
  143. timeStr := fmt.Sprintf("%d", timestamp)
  144. addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue)
  145. isAdd = true
  146. } else {
  147. if findItem != nil && utils.SubFloatToString(findItem.Value, 30) != item.Value {
  148. err = ModifyEdbDataById(source, 0, findItem.EdbDataId, item.Value)
  149. if err != nil {
  150. return err
  151. }
  152. }
  153. }
  154. // 下面代码主要目的是处理掉手动插入的数据判断
  155. {
  156. if realDataMaxDate.IsZero() || dataTime.After(realDataMaxDate) {
  157. realDataMaxDate = dataTime
  158. }
  159. if edbDataInsertConfigDate.IsZero() || dataTime.Equal(edbDataInsertConfigDate) {
  160. isFindConfigDateRealData = true
  161. }
  162. }
  163. }
  164. // 处理手工数据补充的配置
  165. HandleConfigInsertEdbData(realDataMaxDate, edbDataInsertConfig, edbInfoId, source, 0, existMap, isFindConfigDateRealData)
  166. if isAdd {
  167. addSql = strings.TrimRight(addSql, ",")
  168. //_,err = o.Raw(assSql).Exec()
  169. err = global.DEFAULT_DB.Exec(addSql).Error
  170. if err != nil {
  171. fmt.Println("RefreshEdbDataFromBaiinfo add Err", err.Error())
  172. return
  173. }
  174. }
  175. return
  176. }
  177. // GetSource 获取来源编码id
  178. func (obj BaseFromUsdaFas) GetSource() int {
  179. return utils.DATA_SOURCE_USDA_FAS
  180. }
  181. // GetSourceName 获取来源名称
  182. func (obj BaseFromUsdaFas) GetSourceName() string {
  183. return utils.DATA_SOURCE_NAME_USDA_FAS
  184. }
  185. type BaseFromUsdaFasIndex struct {
  186. BaseFromUsdaFasIndexId int64 `gorm:"column:base_from_usda_fas_index_id;primaryKey"`
  187. //BaseFromUsdaFasIndexId int64 `orm:"column(base_from_usda_fas_index_id);pk"`
  188. IndexCode string
  189. IndexName string
  190. Frequency string
  191. Unit string
  192. StartDate string
  193. EndDate string
  194. ClassifyId int64
  195. Sort int
  196. BaseFileName string
  197. RenameFileName string
  198. TerminalCode string
  199. Country string `description:"国家"`
  200. Commodity string `description:"属性"`
  201. CreateTime time.Time
  202. ModifyTime time.Time
  203. }
  204. func (e *BaseFromUsdaFasIndex) AfterFind(db *gorm.DB) (err error) {
  205. e.StartDate = utils.GormDateStrToDateStr(e.StartDate)
  206. e.EndDate = utils.GormDateStrToDateStr(e.EndDate)
  207. return
  208. }
  209. type BaseFromUsdaFasIndexList struct {
  210. BaseFromUsdaFasIndexId int64 `gorm:"column:base_from_usda_fas_index_id;primaryKey"`
  211. //BaseFromUsdaFasIndexId int64 `orm:"column(base_from_usda_fas_index_id);pk"`
  212. IndexCode string
  213. IndexName string
  214. Frequency string
  215. Unit string
  216. Sort int
  217. ClassifyId int64
  218. StartDate string
  219. EndDate string
  220. TerminalCode string
  221. CreateTime string
  222. ModifyTime string
  223. }
  224. // AfterFind 在该模型上设置钩子函数,把日期转成正确的string,所以查询函数只能用Find函数,First或者Scan是不会触发该函数的来获取数据
  225. func (m *BaseFromUsdaFasIndexList) AfterFind(db *gorm.DB) (err error) {
  226. m.StartDate = utils.GormDateStrToDateStr(m.StartDate)
  227. m.EndDate = utils.GormDateStrToDateStr(m.EndDate)
  228. return
  229. }
  230. func (y *BaseFromUsdaFasData) GetByIndexCode(indexCode string) (list []*BaseFromUsdaFasData, err error) {
  231. //o := orm.NewOrm()
  232. sql := ` SELECT * FROM base_from_usda_fas_data WHERE index_code=? `
  233. //_, err = o.Raw(sql, indexCode).QueryRows(&list)
  234. err = global.DEFAULT_DB.Raw(sql, indexCode).Find(&list).Error
  235. return
  236. }
  237. func (y *BaseFromUsdaFasData) AddMulti(item []*BaseFromUsdaFasData) (err error) {
  238. //o := orm.NewOrm()
  239. //_, err = o.InsertMulti(1, item)
  240. err = global.DEFAULT_DB.CreateInBatches(&item, 1).Error
  241. return
  242. }
  243. // Update 修改
  244. func (y *BaseFromUsdaFasData) Update(updateCols []string) (err error) {
  245. //o := orm.NewOrm()
  246. //_, err = o.Update(y, updateCols...)
  247. err = global.DEFAULT_DB.Model(&y).Select(updateCols).Updates(&y).Error
  248. return
  249. }
  250. // HandleUsdaFasExcelData 涌益咨询的excel数据
  251. type HandleUsdaFasExcelData struct {
  252. ClassifyName string `description:"指标目录"`
  253. ParentClassifyName string `description:"父级指标目录"`
  254. ClassifySort int `description:"指标目录排序号"`
  255. IndexName string `description:"指标名称"`
  256. IndexCode string `description:"指标编码"`
  257. Unit string `description:"单位"`
  258. Sort int `description:"排序号"`
  259. Frequency string `description:"频度"`
  260. Country string `description:"国家"`
  261. Commodity string `description:"属性"`
  262. ExcelDataMap map[string]string
  263. }
  264. type HandleUsdaFasExcelDataReq struct {
  265. List []*HandleUsdaFasExcelData
  266. TerminalCode string `description:"编码"`
  267. }
  268. func (y *BaseFromUsdaFasData) GetMaxAndMinDateByIndexCode(indexCode string) (item *EdbInfoMaxAndMinInfo, err error) {
  269. //o := orm.NewOrm()
  270. sql := ` SELECT MIN(data_time) AS min_date,MAX(data_time) AS max_date,MIN(value) AS min_value,MAX(value) AS max_value FROM base_from_usda_fas_data WHERE index_code=? `
  271. //err = o.Raw(sql, indexCode).QueryRow(&item)
  272. err = global.DEFAULT_DB.Raw(sql, indexCode).First(&item).Error
  273. var latest_value float64
  274. sql = ` SELECT value AS latest_value FROM %s WHERE index_code=? ORDER BY data_time DESC LIMIT 1 `
  275. sql = fmt.Sprintf(sql, "base_from_usda_fas_data")
  276. //err = o.Raw(sql, indexCode).QueryRow(&latest_value)
  277. err = global.DEFAULT_DB.Raw(sql, indexCode).Scan(&latest_value).Error
  278. item.LatestValue = latest_value
  279. return
  280. }
  281. func (y *BaseFromUsdaFasIndex) ModifyIndexMaxAndMinDate(indexCode string, item *EdbInfoMaxAndMinInfo) (err error) {
  282. //o := orm.NewOrm()
  283. sql := ` UPDATE base_from_usda_fas_index SET start_date=?,end_date=?, end_value=?, modify_time=NOW() WHERE index_code=? `
  284. //_, err = o.Raw(sql, item.MinDate, item.MaxDate, item.LatestValue, indexCode).Exec()
  285. err = global.DEFAULT_DB.Exec(sql, item.MinDate, item.MaxDate, item.LatestValue, indexCode).Error
  286. return
  287. }
  288. func (y *BaseFromUsdaFasIndex) GetByIndexCode(indexCode string) (item *BaseFromUsdaFasIndex, err error) {
  289. //o := orm.NewOrm()
  290. sql := ` SELECT * FROM base_from_usda_fas_index WHERE index_code=? `
  291. //err = o.Raw(sql, indexCode).QueryRow(&item)
  292. err = global.DEFAULT_DB.Raw(sql, indexCode).First(&item).Error
  293. return
  294. }
  295. func (y *BaseFromUsdaFasIndex) Add() (lastId int64, err error) {
  296. //o := orm.NewOrm()
  297. //lastId, err = o.Insert(y)
  298. err = global.DEFAULT_DB.Create(&y).Error
  299. if err != nil {
  300. return
  301. }
  302. lastId = y.BaseFromUsdaFasIndexId
  303. return
  304. }
  305. // Update 修改
  306. func (y *BaseFromUsdaFasIndex) Update(updateCols []string) (err error) {
  307. //o := orm.NewOrm()
  308. //_, err = o.Update(y, updateCols...)
  309. err = global.DEFAULT_DB.Model(&y).Select(updateCols).Updates(&y).Error
  310. return
  311. }
  312. // BaseFromUsdaFasClassify UsdaFas原始数据分类表
  313. type BaseFromUsdaFasClassify struct {
  314. ClassifyId int64 `gorm:"column:classify_id;primaryKey"`
  315. //ClassifyId int64 `orm:"column(classify_id);pk"`
  316. ClassifyName string `description:"分类名称"`
  317. ClassifyNameEn string `description:"分类名称"`
  318. ParentId int `description:"父级id"`
  319. SysUserId int `description:"创建人id"`
  320. SysUserRealName string `description:"创建人姓名"`
  321. Level int `description:"层级"`
  322. Sort int `description:"排序字段,越小越靠前,默认值:10"`
  323. ModifyTime time.Time `description:"修改时间"`
  324. CreateTime time.Time `description:"创建时间"`
  325. }
  326. func (y *BaseFromUsdaFasClassify) Add() (lastId int64, err error) {
  327. //o := orm.NewOrm()
  328. //lastId, err = o.Insert(y)
  329. err = global.DEFAULT_DB.Create(&y).Error
  330. if err != nil {
  331. return
  332. }
  333. lastId = int64(y.ClassifyId)
  334. return
  335. }
  336. // Update 修改
  337. func (y *BaseFromUsdaFasClassify) Update(updateCols []string) (err error) {
  338. //o := orm.NewOrm()
  339. //_, err = o.Update(y, updateCols...)
  340. err = global.DEFAULT_DB.Model(&y).Select(updateCols).Updates(&y).Error
  341. return
  342. }
  343. func (y *BaseFromUsdaFasClassify) GetByClassifyName(classifyName string) (item *BaseFromUsdaFasClassify, err error) {
  344. //o := orm.NewOrm()
  345. sql := ` SELECT * FROM base_from_usda_fas_classify WHERE classify_name=? `
  346. //err = o.Raw(sql, classifyName).QueryRow(&item)
  347. err = global.DEFAULT_DB.Raw(sql, classifyName).First(&item).Error
  348. return
  349. }
  350. func (y *BaseFromUsdaFasClassify) GetParentClassify() (items []*BaseFromUsdaFasClassify, err error) {
  351. //o := orm.NewOrm()
  352. sql := ` SELECT * FROM base_from_usda_fas_classify WHERE parent_id=0 `
  353. //_, err = o.Raw(sql).QueryRows(&items)
  354. err = global.DEFAULT_DB.Raw(sql).Find(&items).Error
  355. return
  356. }