edb_data_ys.go 15 KB

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