edb_data_ys.go 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560
  1. /**
  2. * @Author: jgl
  3. * @Date: 2021/9/14 13:33
  4. */
  5. package data_manage
  6. import (
  7. "crypto/md5"
  8. "encoding/hex"
  9. "encoding/json"
  10. "errors"
  11. "eta/eta_api/global"
  12. "eta/eta_api/utils"
  13. "fmt"
  14. "github.com/beego/beego/v2/client/orm"
  15. "io/ioutil"
  16. "net/http"
  17. "net/url"
  18. "strconv"
  19. "strings"
  20. "time"
  21. )
  22. type BaseFromSmmDataSimple struct {
  23. //SmmDataId int `orm:"column(smm_data_id);pk"`
  24. SmmDataId int `gorm:"column:smm_data_id;primaryKey"`
  25. BaseFromSmmIndexId int
  26. IndexCode string
  27. DataTime string
  28. Value string
  29. }
  30. func GetEdbDataYsMaxAndMinDate(edbCode string) (min_date, max_date string, err error) {
  31. //o := orm.NewOrmUsingDB("data")
  32. //sql := ` SELECT MIN(data_time) AS min_date,MAX(data_time) AS max_date FROM edb_data_ys WHERE edb_code=? `
  33. //err = o.Raw(sql, edbCode).QueryRow(&min_date, &max_date)
  34. sql := ` SELECT MIN(data_time) AS minDate,MAX(data_time) AS maxDate FROM edb_data_ys WHERE edb_code=? `
  35. var maxAndMinDate MaxAndMinDate
  36. err = global.DbMap[utils.DbNameIndex].Raw(sql, edbCode).First(&maxAndMinDate).Error
  37. if err != nil {
  38. return
  39. }
  40. min_date = maxAndMinDate.MinDate.Format(utils.FormatDate)
  41. max_date = maxAndMinDate.MaxDate.Format(utils.FormatDate)
  42. return
  43. }
  44. // 有色
  45. func GetEdbDataByYs(edbCode, startDate, endDate string) (searchItem *EdbInfoSearch, err error) {
  46. o := orm.NewOrmUsingDB("data")
  47. to, err := o.Begin()
  48. if err != nil {
  49. return
  50. }
  51. searchItem = new(EdbInfoSearch)
  52. searchItem.EdbCode = edbCode
  53. smmBaseDataAll, err := GetBaseFromSmmDataAllByIndexCode(edbCode)
  54. if err != nil && !utils.IsErrNoRow(err) {
  55. return
  56. }
  57. var isAdd bool
  58. addSql := ` INSERT INTO edb_data_ys(edb_info_id,edb_code,data_time,value,create_time,modify_time,status,data_timestamp) values `
  59. dataList := make([]*EdbInfoSearchData, 0)
  60. existMap := make(map[string]string)
  61. for _, sv := range smmBaseDataAll {
  62. eDate := sv.DataTime
  63. dataTime, err := time.Parse(utils.FormatDate, eDate)
  64. if err != nil {
  65. fmt.Println("time.Parse Err:" + eDate)
  66. return nil, err
  67. }
  68. timestamp := dataTime.UnixNano() / 1e6
  69. timeStr := fmt.Sprintf("%d", timestamp)
  70. if _, ok := existMap[eDate]; !ok {
  71. addSql += GetAddSql("0", edbCode, eDate, timeStr, sv.Value)
  72. isAdd = true
  73. }
  74. existMap[eDate] = sv.Value
  75. }
  76. if isAdd {
  77. addSql = strings.TrimRight(addSql, ",")
  78. utils.FileLog.Info("addSql:" + addSql)
  79. _, err = to.Raw(addSql).Exec()
  80. if err != nil {
  81. return searchItem, err
  82. }
  83. }
  84. if err != nil {
  85. _ = to.Rollback()
  86. } else {
  87. _ = to.Commit()
  88. }
  89. size := utils.EDB_DATA_LIMIT
  90. dataList, err = GetEdbDataAllByEdbCode(edbCode, utils.DATA_SOURCE_YS, utils.DATA_SUB_SOURCE_EDB, size)
  91. if err != nil {
  92. utils.FileLogData.Info("GetEdbDataThsByCode Err:%s", err.Error())
  93. return searchItem, err
  94. }
  95. minDate, maxDate, err := GetEdbDataYsMaxAndMinDate(edbCode)
  96. if err != nil {
  97. return searchItem, err
  98. }
  99. searchItem.DataList = dataList
  100. searchItem.StartDate = minDate
  101. searchItem.EndDate = maxDate
  102. if searchItem.DataList == nil {
  103. searchItem.DataList = make([]*EdbInfoSearchData, 0)
  104. }
  105. return
  106. }
  107. // 全部刷新有色数据
  108. func RefreshAllEdbDataByYs(edbInfoId, source, subSource int, edbCode, startDate, endDate string) (err error) {
  109. o := orm.NewOrmUsingDB("data")
  110. to, err := o.Begin()
  111. if err != nil {
  112. return
  113. }
  114. defer func() {
  115. if err != nil {
  116. _ = to.Rollback()
  117. } else {
  118. _ = to.Commit()
  119. }
  120. }()
  121. if err != nil {
  122. return
  123. }
  124. edbInfoIdStr := strconv.Itoa(edbInfoId)
  125. //获取数据
  126. err = SyncSmmIndexDataBase(edbCode, startDate, endDate)
  127. if err != nil {
  128. err = errors.New("SyncSmmIndexDataBase Err:" + err.Error())
  129. return err
  130. }
  131. //获取已存在指标所有数据
  132. existDataList := make([]*EdbDataBase, 0)
  133. dataTableName := GetEdbDataTableName(source, subSource)
  134. sql := `SELECT * FROM %s WHERE edb_info_id=? `
  135. sql = fmt.Sprintf(sql, dataTableName)
  136. _, err = to.Raw(sql, edbInfoId).QueryRows(&existDataList)
  137. if err != nil {
  138. return err
  139. }
  140. existDataMap := make(map[string]string)
  141. for _, v := range existDataList {
  142. existDataMap[v.DataTime] = v.Value
  143. }
  144. smmDateList := make([]*BaseFromSmmDataSimple, 0)
  145. smmSql := ` SELECT * FROM base_from_smm_data WHERE index_code=? AND data_time>=? `
  146. _, err = to.Raw(smmSql, edbCode, startDate).QueryRows(&smmDateList)
  147. if err != nil {
  148. return err
  149. }
  150. addSql := ` INSERT INTO edb_data_ys(edb_info_id,edb_code,data_time,value,create_time,modify_time,status,data_timestamp) values `
  151. var isAdd bool
  152. for _, sv := range smmDateList {
  153. if existVal, ok := existDataMap[sv.DataTime]; !ok {
  154. dataTime, err := time.Parse(utils.FormatDate, sv.DataTime)
  155. if err != nil {
  156. return err
  157. }
  158. timestamp := dataTime.UnixNano() / 1e6
  159. timeStr := fmt.Sprintf("%d", timestamp)
  160. addSql += GetAddSql(edbInfoIdStr, edbCode, sv.DataTime, timeStr, sv.Value)
  161. isAdd = true
  162. } else {
  163. if existVal != sv.Value {
  164. sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? `
  165. sql = fmt.Sprintf(sql, dataTableName)
  166. _, err = to.Raw(sql, sv.Value, edbInfoId, sv.DataTime).Exec()
  167. if err != nil {
  168. return err
  169. }
  170. }
  171. }
  172. }
  173. if isAdd {
  174. addSql = strings.TrimRight(addSql, ",")
  175. _, err = to.Raw(addSql).Exec()
  176. if err != nil {
  177. return err
  178. }
  179. }
  180. return
  181. }
  182. func SyncSmmIndexDataBase(edbCode, startDate, endDate string) (err error) {
  183. utils.FileLog.Info("start:" + time.Now().Format(utils.FormatDateTime))
  184. var smmCode string
  185. if strings.Contains(edbCode, "#") {
  186. smmCode = strings.Split(edbCode, "#")[0]
  187. } else {
  188. smmCode = edbCode
  189. }
  190. token, err := getToken("pqian@hzinsights.com", "hz123456")
  191. if err != nil {
  192. fmt.Println(err)
  193. return
  194. }
  195. baseSmmItem, err := GetBaseFromSmmBySmmCode(smmCode)
  196. if err != nil {
  197. fmt.Println(err)
  198. return
  199. }
  200. if baseSmmItem == nil {
  201. err = errors.New("GetBaseFromSmmBySmmCode Err:" + err.Error())
  202. return
  203. }
  204. smmIndexAll, err := GetBaseFromSmmIndexBySmmCode(smmCode)
  205. if err != nil {
  206. fmt.Println("GetBaseFromSmmIndex Err:" + err.Error())
  207. return
  208. }
  209. existIndexMap := make(map[string]*BaseFromSmmIndex)
  210. for _, item := range smmIndexAll {
  211. existIndexMap[item.IndexCode] = item
  212. }
  213. ysItem, err := getApiData(token, edbCode, startDate, endDate)
  214. if err != nil {
  215. fmt.Println(err)
  216. return
  217. }
  218. if ysItem != nil && ysItem.Code == 200 {
  219. frequency := ysItem.Data.Frequency
  220. indexMap := make(map[string]int)
  221. smmIndexIdMap := make(map[int]int)
  222. indexCodeMap := make(map[int]string)
  223. indexKey := 0
  224. var isDateIndex int
  225. for fk, fv := range ysItem.Data.Field {
  226. if fv.IsDate == "1" {
  227. isDateIndex = fk
  228. } else {
  229. if !strings.Contains(fv.Name, "产品名称") &&
  230. !strings.Contains(fv.Name, "单位") &&
  231. !strings.Contains(fv.Name, "时间") &&
  232. !strings.Contains(fv.Name, "备注") {
  233. indexMap[fv.DBColName] = fk
  234. indexKey += 1
  235. indexCode := smmCode + "#" + strconv.Itoa(indexKey)
  236. if findItem, ok := existIndexMap[indexCode]; !ok {
  237. ssmIndex := new(BaseFromSmmIndex)
  238. ssmIndex.Interface = smmCode
  239. ssmIndex.Name = baseSmmItem.Name
  240. ssmIndex.IndexCode = indexCode
  241. ssmIndex.IndexName = baseSmmItem.Name + "_" + fv.Name
  242. ssmIndex.Type1 = baseSmmItem.Type1
  243. ssmIndex.Type2 = baseSmmItem.Type2
  244. ssmIndex.Type3 = baseSmmItem.Type3
  245. ssmIndex.Frequency = frequency
  246. ssmIndex.Unit = fv.Unit
  247. ssmIndex.ApiStartTime = baseSmmItem.ApiStartTime
  248. ssmIndex.ApiUpdateTime = baseSmmItem.ApiUpdateTime
  249. ssmIndex.StartTime = baseSmmItem.StartTime
  250. ssmIndex.FinishTime = baseSmmItem.FinishTime
  251. ssmIndex.CreateTime = time.Now()
  252. ssmIndex.ModifyTime = time.Now()
  253. lastIndexId, err := AddBaseFromSmmIndex(ssmIndex)
  254. if err != nil {
  255. err = errors.New("AddBaseFromSmmIndex Err:" + err.Error())
  256. return err
  257. }
  258. smmIndexIdMap[fk] = int(lastIndexId)
  259. indexCodeMap[fk] = indexCode
  260. } else {
  261. smmIndexIdMap[fk] = findItem.BaseFromSmmIndexId
  262. indexCodeMap[fk] = findItem.IndexCode
  263. }
  264. }
  265. }
  266. }
  267. existDataMap := make(map[string]*BaseFromSmmData)
  268. for _, mv := range indexCodeMap {
  269. indexCode := mv
  270. dataAllList, err := GetBaseFromSmmDataAllByIndexCode(indexCode)
  271. if err != nil {
  272. err = errors.New("GetBaseFromSmmData Err:" + err.Error())
  273. return err
  274. }
  275. for _, item := range dataAllList {
  276. key := item.IndexCode + item.DataTime
  277. existDataMap[key] = item
  278. }
  279. }
  280. addExistDataMap := make(map[string]string)
  281. for _, dv := range ysItem.Data.Content {
  282. var dataTime string
  283. dataTime = dv[isDateIndex]
  284. if strings.Contains(dataTime, "Q1") {
  285. dataTime = strings.Replace(dataTime, "Q1", "-01", -1)
  286. dataTime += "-31"
  287. }
  288. if strings.Contains(dataTime, "Q2") {
  289. dataTime = strings.Replace(dataTime, "Q2", "-06", -1)
  290. dataTime += "-30"
  291. }
  292. if strings.Contains(dataTime, "Q3") {
  293. dataTime = strings.Replace(dataTime, "Q3", "-09", -1)
  294. dataTime += "-30"
  295. }
  296. if strings.Contains(dataTime, "Q4") {
  297. dataTime = strings.Replace(dataTime, "Q4", "-12", -1)
  298. dataTime += "-31"
  299. }
  300. if strings.Contains(dataTime, "H1") {
  301. dataTime = strings.Replace(dataTime, "H1", "-06", -1)
  302. dataTime += "-30"
  303. }
  304. if strings.Contains(dataTime, "H2") {
  305. dataTime = strings.Replace(dataTime, "H2", "-12", -1)
  306. dataTime += "-31"
  307. }
  308. if frequency == "月" {
  309. monthDate, err := time.Parse("2006-01", dataTime)
  310. if err != nil {
  311. fmt.Println("time.Parse:" + err.Error())
  312. }
  313. lastTime := monthDate.AddDate(0, 1, -1)
  314. lastYear, lastMonth, lastDay := lastTime.Date()
  315. var lastDate string
  316. if int(lastMonth) < 10 {
  317. lastDate = strconv.Itoa(lastYear) + "-" + "0" + strconv.Itoa(int(lastMonth)) + "-" + strconv.Itoa(lastDay)
  318. } else {
  319. lastDate = strconv.Itoa(lastYear) + "-" + strconv.Itoa(int(lastMonth)) + "-" + strconv.Itoa(lastDay)
  320. }
  321. dataTime = lastDate
  322. } else if frequency == "年" {
  323. dataTime = dataTime + "-12-31"
  324. }
  325. saveDataTime, err := time.Parse(utils.FormatDate, dataTime)
  326. if err != nil {
  327. err = errors.New("time.Parse Err:" + err.Error())
  328. return err
  329. }
  330. timestamp := saveDataTime.UnixNano() / 1e6
  331. //循环指标
  332. for _, v := range indexMap {
  333. indexCode := indexCodeMap[v]
  334. smmIndexId := smmIndexIdMap[v]
  335. dataVal := dv[v]
  336. if indexCode != "" {
  337. key := indexCode + dataTime
  338. val := strings.Replace(dataVal, ",", "", -1)
  339. if findData, dataOk := existDataMap[key]; !dataOk {
  340. if _, addOK := addExistDataMap[key]; !addOK {
  341. if val != "" && val != "-" {
  342. dataItem := new(BaseFromSmmData)
  343. dataItem.BaseFromSmmIndexId = smmIndexId
  344. dataItem.IndexCode = indexCode
  345. dataItem.DataTime = dataTime
  346. dataItem.Value = val
  347. dataItem.CreateTime = time.Now()
  348. dataItem.ModifyTime = time.Now()
  349. dataItem.DataTimestamp = timestamp
  350. _, err = AddBaseFromSmmData(dataItem)
  351. if err != nil && !strings.Contains(err.Error(), "idx_index_code_date") {
  352. fmt.Println("AddBaseFromSmmData Err:" + err.Error())
  353. err = errors.New("AddBaseFromSmmData Err:" + err.Error())
  354. return err
  355. }
  356. }
  357. }
  358. } else {
  359. if findData != nil && findData.Value != val { //修改
  360. if _, addOK := addExistDataMap[key]; !addOK {
  361. if val != "" && val != "-" {
  362. err = ModifyBaseFromSmmData(findData.SmmDataId, val)
  363. if err != nil {
  364. err = errors.New("ModifyBaseFromSmmData Err:" + err.Error())
  365. return err
  366. }
  367. }
  368. }
  369. }
  370. }
  371. addExistDataMap[key] = key
  372. }
  373. }
  374. }
  375. //修改数据开始,结束日期
  376. {
  377. indexList, err := GetBaseFromSmmIndexBySmmCode(smmCode)
  378. if err != nil {
  379. fmt.Println("GetBaseFromSmmIndexBySmmCode Err:" + err.Error())
  380. }
  381. for _, sv := range indexList {
  382. minDate, maxDate, err := GetBaseFromSmmMaxOrMinDate(sv.IndexCode)
  383. if err != nil {
  384. fmt.Println("GetEdbDataSmmMaxOrMinDate Err:" + err.Error())
  385. } else {
  386. err = ModifyBaseFromSmmMinDateAndMaxDate(sv.BaseFromSmmIndexId, minDate, maxDate)
  387. if err != nil {
  388. fmt.Println("ModifyBaseFromSmmMinDateAndMaxDate Err:" + err.Error())
  389. }
  390. }
  391. }
  392. }
  393. }
  394. return
  395. }
  396. const (
  397. dataUrl = "https://dataapi.smm.cn/GetData/" //data url (中文)
  398. //dataUrl = "https://dataapi.smm.cn/GetDataEn/" //data url (english edition)
  399. authUrl = "https://platform.smm.cn/usercenter/auth" // auth url (for all)
  400. )
  401. type TokenResp struct {
  402. Code int `json:"Code"`
  403. Msg string `json:"Msg"`
  404. Data TokenData `json:"Data"`
  405. }
  406. type TokenData struct {
  407. Token string `json:"Token"`
  408. }
  409. // 获取token
  410. func getToken(userName string, password string) (string, error) {
  411. encryptAuth := md5.New()
  412. encryptAuth.Write([]byte(password)) //encrypt password with md5
  413. newPassword := hex.EncodeToString(encryptAuth.Sum(nil))
  414. resp, err := http.PostForm(authUrl, url.Values{"user_name": {userName}, "password": {newPassword}})
  415. if err != nil {
  416. return "", err
  417. }
  418. defer resp.Body.Close()
  419. body, err := ioutil.ReadAll(resp.Body)
  420. if err != nil {
  421. fmt.Println("reponse error", err)
  422. return "", err
  423. }
  424. var bodyJsonContent TokenResp
  425. if err = json.Unmarshal([]byte(body), &bodyJsonContent); err != nil {
  426. fmt.Println(err, "unmarsal failure")
  427. return "", err
  428. }
  429. var token string
  430. if bodyJsonContent.Code == 0 {
  431. token = bodyJsonContent.Data.Token
  432. }
  433. //print(token)
  434. return token, nil
  435. }
  436. // request response
  437. type DataResp struct {
  438. Code int `json:"Code"`
  439. Msg string `json:"Msg"`
  440. Data *ApiData `json:"Data"`
  441. }
  442. // api data response
  443. type ApiData struct {
  444. Status int `json:"Status"` //0 no permission,1 ok
  445. Field []ApiField `json:"Field"`
  446. Content [][]string `json:"Content"`
  447. }
  448. // api title
  449. type ApiField struct {
  450. Unit string `json:"Unit"`
  451. Info string `json:"Info"`
  452. Name string `json:"Name"`
  453. ColumnType string `json:"ColumnType"`
  454. ColIndex uint `json:"ColIndex"`
  455. IsDate string `json:"IsDate"`
  456. }
  457. type YsResult struct {
  458. Code int64 `json:"Code"`
  459. Data struct {
  460. CompanyList []interface{} `json:"CompanyList"`
  461. Content [][]string `json:"Content"`
  462. Field []struct {
  463. ColIndex int64 `json:"ColIndex"`
  464. ColumnType string `json:"ColumnType"`
  465. Info string `json:"Info"`
  466. IsDate string `json:"IsDate"`
  467. Name string `json:"Name"`
  468. Unit string `json:"Unit"`
  469. DBColName string `json:"db_col_name"`
  470. } `json:"Field"`
  471. CountPage int64 `json:"count_page"`
  472. CurrentPage int64 `json:"current_page"`
  473. Frequency string `json:"frequency"`
  474. Mindate string `json:"mindate"`
  475. PageNum int64 `json:"page_num"`
  476. Status int64 `json:"status"`
  477. TotalNum int64 `json:"total_num"`
  478. } `json:"Data"`
  479. Msg string `json:"Msg"`
  480. }
  481. /*
  482. * request data
  483. * sdatetime,edatetime ==>format:yyyy-mm-dd,
  484. * apiName ==> data.metal.com(for english)/data.smm.cn (for chinese)
  485. */
  486. func getApiData(token string, apiName string, sdatetime string, edatetime string) (item *YsResult, err error) {
  487. reqUrl := dataUrl + apiName
  488. resp, err := http.PostForm(reqUrl, url.Values{"token": {token}, "sdatetime": {sdatetime}, "edatetime": {edatetime}})
  489. if err != nil {
  490. return nil, err
  491. }
  492. defer resp.Body.Close()
  493. body, err := ioutil.ReadAll(resp.Body)
  494. if err != nil {
  495. fmt.Println("response error")
  496. return nil, err
  497. }
  498. utils.FileLog.Info("ys result:" + string(body))
  499. dataJsonContent := new(YsResult)
  500. if err = json.Unmarshal([]byte(body), &dataJsonContent); err != nil {
  501. fmt.Println(err, "data unmarshal failure")
  502. return nil, err
  503. }
  504. if dataJsonContent.Code == 200 && len(dataJsonContent.Data.Content) > 0 {
  505. return dataJsonContent, nil
  506. } else {
  507. err = errors.New("code:" + strconv.Itoa(int(dataJsonContent.Code)) + "msg:" + dataJsonContent.Msg)
  508. }
  509. return nil, nil
  510. }