commodity_coal.go 51 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913
  1. package controllers
  2. import (
  3. "encoding/json"
  4. "eta/eta_index_lib/models"
  5. "eta/eta_index_lib/utils"
  6. "fmt"
  7. "github.com/mozillazg/go-pinyin"
  8. "os"
  9. "strconv"
  10. "strings"
  11. "time"
  12. )
  13. // 煤炭网
  14. type CoalMineDataController struct {
  15. BaseAuthController
  16. }
  17. // @Title 刷新数据
  18. // @Description 刷新数据接口
  19. // @Param request body models.CoalMineDataReq true "type json string"
  20. // @Success 200 {object} models.EdbClassify
  21. // @router /jsm/history [post]
  22. func (this *CoalMineDataController) JsmHistory() {
  23. br := new(models.BaseResponse).Init()
  24. defer func() {
  25. this.Data["json"] = br
  26. this.ServeJSON()
  27. }()
  28. var req models.CoalMineDataReq
  29. err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
  30. if err != nil {
  31. br.Msg = "参数解析异常!"
  32. br.ErrMsg = "参数解析失败,Err:" + err.Error()
  33. return
  34. }
  35. //path := "/home/code/python/coal_mail/emailFile/沿海八省动力煤终端用户供耗存数据更新.xlsx"
  36. var mappingItems []*models.BaseFromCoalmineMapping
  37. var indexItems []*models.BaseFromCoalmineJsmIndex
  38. var codeMap = make(map[string]string)
  39. var indexMap = make(map[string]string)
  40. var nameMap = make(map[int]string)
  41. var coalInfoMap = make(map[string][]string)
  42. var dataTime string
  43. var unit string
  44. var province string
  45. var exchange string
  46. codeList, err := models.GetBaseFromCoalmineMapping()
  47. if err != nil && err.Error() != utils.ErrNoRow() {
  48. utils.FileLog.Info("获取煤炭指标失败:", err)
  49. return
  50. }
  51. if len(codeList) > 0 {
  52. for _, v := range codeList {
  53. codeMap[v.IndexName] = v.IndexCode
  54. }
  55. }
  56. indexCompanyList, err := models.GetBaseFromCoalmineIndex()
  57. if err != nil && err.Error() != utils.ErrNoRow() {
  58. utils.FileLog.Info("获取煤炭公司指标失败:", err)
  59. return
  60. }
  61. if len(indexCompanyList) > 0 {
  62. for _, v := range indexCompanyList {
  63. indexMap[v.IndexName+v.DataTime] = v.DealValue
  64. }
  65. }
  66. sheet := req.SheetData[0]
  67. //遍历行读取
  68. maxCol := sheet.MaxRow
  69. for i := 0; i < maxCol; i++ {
  70. if i == 0 {
  71. row := sheet.Rows[i]
  72. cells := row.Cells
  73. for k, cell := range cells {
  74. text := cell.Value
  75. if k > 0 {
  76. switch k {
  77. case 1:
  78. province = "山西"
  79. text = strings.TrimLeft(text, "山西")
  80. case 4:
  81. province = "内蒙古"
  82. text = strings.TrimLeft(text, "内蒙古")
  83. case 7:
  84. province = "陕西"
  85. text = strings.TrimLeft(text, "陕西")
  86. case 10:
  87. province = "442家"
  88. text = "产量"
  89. }
  90. if k != 1 && k != 4 && k != 7 && k != 10 {
  91. unit = "%"
  92. } else {
  93. unit = "万吨"
  94. }
  95. exchange = text
  96. text = province + text
  97. var item models.BaseFromCoalmineMapping
  98. item.IndexName = text
  99. nameMap[k] = item.IndexName
  100. coalInfoMap[item.IndexName] = append(coalInfoMap[item.IndexName], exchange, province, unit)
  101. //code
  102. exists := ContainsSpecialName(province)
  103. var code string
  104. if exists {
  105. abbr := trimProvinceName(text)
  106. provinceAbbr := trimProvinceName(province)
  107. //取处理后公司名首字母缩写
  108. a := pinyin.NewArgs()
  109. rows := pinyin.Pinyin(abbr, a)
  110. for i := 0; i < len(rows); i++ {
  111. if len(rows[i]) != 0 {
  112. str := rows[i][0]
  113. pi := str[0:1]
  114. code += pi
  115. }
  116. }
  117. item.IndexCode = provinceAbbr + code + "jsm"
  118. } else {
  119. a := pinyin.NewArgs()
  120. rows := pinyin.Pinyin(text, a)
  121. for i := 0; i < len(rows); i++ {
  122. if len(rows[i]) != 0 {
  123. str := rows[i][0]
  124. pi := str[0:1]
  125. code += pi
  126. }
  127. }
  128. item.IndexCode = code + "jsm"
  129. }
  130. item.CreateTime = time.Now()
  131. mappingItems = append(mappingItems, &item)
  132. }
  133. }
  134. }
  135. if i > 0 {
  136. row := sheet.Rows[i]
  137. cells := row.Cells
  138. for k, cell := range cells {
  139. var item models.BaseFromCoalmineJsmIndex
  140. if k == 0 {
  141. text := cell.Value
  142. parsedTime, err := time.Parse("01-02-06", text)
  143. if err != nil {
  144. fmt.Println("解析时间字符串出错:", err)
  145. return
  146. }
  147. dataTime = parsedTime.Format(utils.FormatDate)
  148. }
  149. if k > 0 {
  150. text := cell.Value
  151. item.IndexName = nameMap[k]
  152. item.IndexCode = codeMap[nameMap[k]]
  153. item.DealValue = text
  154. item.Source = "三省周度"
  155. item.DataTime = dataTime
  156. item.Frequency = "周度"
  157. item.ModifyTime = time.Now()
  158. item.CreateTime = time.Now()
  159. if len(coalInfoMap[item.IndexName]) == 3 {
  160. item.Exchange = coalInfoMap[item.IndexName][0]
  161. item.Province = coalInfoMap[item.IndexName][1]
  162. item.Unit = coalInfoMap[item.IndexName][2]
  163. }
  164. indexItems = append(indexItems, &item)
  165. }
  166. }
  167. }
  168. }
  169. ////添加数据到数据库
  170. for _, v := range mappingItems {
  171. if codeMap[v.IndexName] == "" {
  172. codeMap[v.IndexName] = v.IndexCode
  173. newId, err := models.AddBaseFromCoalmineMapping(v)
  174. if err != nil {
  175. for i := 0; i < 10; i++ {
  176. v.IndexCode = v.IndexCode + strconv.Itoa(i)
  177. codeMap[v.IndexName] = v.IndexCode
  178. newId, err := models.AddBaseFromCoalmineMapping(v)
  179. if err != nil {
  180. fmt.Println("再次添加公司指标名称错误", err)
  181. continue
  182. } else {
  183. fmt.Println("新增公司成功", newId)
  184. break
  185. }
  186. }
  187. } else {
  188. fmt.Println("新增公司成功", newId)
  189. }
  190. }
  191. }
  192. //fmt.Println("指标操作完成")
  193. //给indexItem中的code赋值并插入index表
  194. for _, v := range indexItems {
  195. v.IndexCode = codeMap[v.IndexName]
  196. if indexMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
  197. newId, err := models.AddBaseFromCoalmineIndex(v)
  198. if err != nil {
  199. fmt.Println("添加数据错误", err)
  200. } else {
  201. fmt.Println("新增成功", newId)
  202. }
  203. } else {
  204. if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
  205. err = models.UpdateBaseFromCoalmineIndex(v)
  206. if err != nil {
  207. fmt.Println("修改数据错误错误", err)
  208. return
  209. }
  210. }
  211. }
  212. }
  213. // 更新指标最新日期
  214. {
  215. go func() {
  216. obj := new(models.BaseFromCoalmineMapping)
  217. for _, v := range indexItems {
  218. indexCode := v.IndexCode
  219. var dateItem *models.EdbInfoMaxAndMinInfo
  220. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  221. if err != nil {
  222. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  223. return
  224. }
  225. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  226. }
  227. }()
  228. }
  229. br.Ret = 200
  230. br.Success = true
  231. br.Msg = "获取成功"
  232. return
  233. }
  234. // @Title 刷新数据
  235. // @Description 刷新数据接口
  236. // @Param request body models.CoalMineDataReq true "type json string"
  237. // @Success 200 {object} models.EdbClassify
  238. // @router /coastal/history [post]
  239. func (this *CoalMineDataController) CoastalHistory() {
  240. br := new(models.BaseResponse).Init()
  241. defer func() {
  242. this.Data["json"] = br
  243. this.ServeJSON()
  244. }()
  245. var req models.CoalMineDataReq
  246. err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
  247. if err != nil {
  248. br.Msg = "参数解析异常!"
  249. br.ErrMsg = "参数解析失败,Err:" + err.Error()
  250. return
  251. }
  252. var mappingItems []*models.BaseFromCoalmineMapping
  253. var indexItems []*models.BaseFromCoalmineCoastalIndex
  254. var codeMap = make(map[string]string)
  255. var indexMap = make(map[string]string)
  256. var nameMap = make(map[int]string)
  257. var groupMap = make(map[int]string)
  258. var dataTime string
  259. var unit string
  260. codeList, err := models.GetBaseFromCoalmineMapping()
  261. if err != nil && err.Error() != utils.ErrNoRow() {
  262. utils.FileLog.Info("获取煤炭指标失败:", err)
  263. return
  264. }
  265. if len(codeList) > 0 {
  266. for _, v := range codeList {
  267. codeMap[v.IndexName] = v.IndexCode
  268. }
  269. }
  270. indexCompanyList, err := models.GetBaseFromCoalmineCoastalIndex()
  271. if err != nil && err.Error() != utils.ErrNoRow() {
  272. utils.FileLog.Info("获取煤炭公司指标失败:", err)
  273. return
  274. }
  275. if len(indexCompanyList) > 0 {
  276. for _, v := range indexCompanyList {
  277. indexMap[v.IndexName+v.DataTime] = v.DealValue
  278. }
  279. }
  280. for ii, sheet := range req.SheetData {
  281. //遍历行读取
  282. maxCol := len(sheet.Rows)
  283. for i := 0; i < maxCol; i++ {
  284. if i == 0 {
  285. row := sheet.Rows[i]
  286. cells := row.Cells
  287. for k, cell := range cells {
  288. ignore := 1
  289. ignore2 := 10
  290. if ii == 3 {
  291. // 第四页供煤没有星期,所以只跳过第一行
  292. ignore = 0
  293. ignore2 = 9
  294. }
  295. if k > ignore && k < ignore2 {
  296. text := cell.Value
  297. text = strings.Replace(text, ":", "", -1)
  298. text = strings.Replace(text, ":", "", -1)
  299. if ii == 3 {
  300. text += "供煤"
  301. }
  302. if ii == 2 {
  303. text += "天数"
  304. }
  305. province := strings.Replace(text, "日耗", "", -1)
  306. province = strings.Replace(province, "库存", "", -1)
  307. province = strings.Replace(province, "可用天数", "", -1)
  308. province = strings.Replace(province, "供煤", "", -1)
  309. groupMap[k] = province
  310. var item models.BaseFromCoalmineMapping
  311. item.IndexName = text
  312. //合计命名
  313. nameMap[k] = item.IndexName
  314. //code
  315. exists := ContainsSpecialName(text)
  316. var code string
  317. if exists {
  318. abbr := trimProvinceName(text)
  319. //取处理后公司名首字母缩写
  320. a := pinyin.NewArgs()
  321. rows := pinyin.Pinyin(abbr, a)
  322. for i := 0; i < len(rows); i++ {
  323. if len(rows[i]) != 0 {
  324. str := rows[i][0]
  325. pi := str[0:1]
  326. code += pi
  327. }
  328. }
  329. item.IndexCode = abbr + "coastal"
  330. } else {
  331. a := pinyin.NewArgs()
  332. rows := pinyin.Pinyin(item.IndexName, a)
  333. for i := 0; i < len(rows); i++ {
  334. if len(rows[i]) != 0 {
  335. str := rows[i][0]
  336. pi := str[0:1]
  337. code += pi
  338. }
  339. }
  340. item.IndexCode = code + "coastal"
  341. }
  342. item.CreateTime = time.Now()
  343. mappingItems = append(mappingItems, &item)
  344. }
  345. if ii == 2 {
  346. unit = "天"
  347. } else {
  348. unit = "万吨"
  349. }
  350. }
  351. }
  352. if i > 0 {
  353. row := sheet.Rows[i]
  354. cells := row.Cells
  355. for k, cell := range cells {
  356. ingore := 10
  357. if ii == 3 {
  358. ingore = 9
  359. }
  360. if k < ingore {
  361. var item models.BaseFromCoalmineCoastalIndex
  362. if k == 0 {
  363. text := cell.Value
  364. if text == "" {
  365. continue
  366. }
  367. if len(text) < 6 {
  368. dataTime = ExcelDateToDate(text).Format(utils.FormatDate)
  369. } else {
  370. parsedTime, err := time.Parse("01-02-06", text)
  371. if err != nil {
  372. parsedTime, err = time.Parse("2006/01/02", text)
  373. if err != nil {
  374. fmt.Println("解析时间字符串出错:", err)
  375. return
  376. }
  377. }
  378. dataTime = parsedTime.Format(utils.FormatDate)
  379. }
  380. }
  381. ignore := 1
  382. if ii == 3 {
  383. // 第四页供煤没有星期,所以只跳过第一行
  384. ignore = 0
  385. }
  386. if k > ignore {
  387. text := cell.Value
  388. item.IndexName = nameMap[k]
  389. item.IndexCode = codeMap[nameMap[k]]
  390. item.DealValue = text
  391. item.GroupName = groupMap[k]
  392. item.Source = "沿海八省"
  393. item.DataTime = dataTime
  394. item.Frequency = "日度"
  395. item.ModifyTime = time.Now()
  396. item.CreateTime = time.Now()
  397. item.Unit = unit
  398. indexItems = append(indexItems, &item)
  399. }
  400. }
  401. }
  402. }
  403. }
  404. }
  405. //添加数据到数据库
  406. for _, v := range mappingItems {
  407. if codeMap[v.IndexName] == "" {
  408. codeMap[v.IndexName] = v.IndexCode
  409. newId, err := models.AddBaseFromCoalmineMapping(v)
  410. if err != nil {
  411. for i := 0; i < 10; i++ {
  412. v.IndexCode = v.IndexCode + strconv.Itoa(i)
  413. codeMap[v.IndexName] = v.IndexCode
  414. newId, err := models.AddBaseFromCoalmineMapping(v)
  415. if err != nil {
  416. fmt.Println("再次添加公司指标名称错误", err)
  417. continue
  418. } else {
  419. fmt.Println("新增公司成功", newId)
  420. break
  421. }
  422. }
  423. } else {
  424. fmt.Println("新增公司成功", newId)
  425. }
  426. }
  427. }
  428. fmt.Println("指标操作完成")
  429. //给indexItem中的code赋值并插入index表
  430. for _, v := range indexItems {
  431. v.IndexCode = codeMap[v.IndexName]
  432. if indexMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
  433. newId, err := models.AddBaseFromCoalCoastalIndex(v)
  434. if err != nil {
  435. fmt.Println("添加数据错误", err)
  436. } else {
  437. fmt.Println("新增成功", newId)
  438. }
  439. } else {
  440. if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
  441. err = models.UpdateBaseFromCoalCoastalIndex(v)
  442. if err != nil {
  443. fmt.Println("修改数据错误错误", err)
  444. return
  445. }
  446. }
  447. }
  448. }
  449. // 更新指标最新日期
  450. {
  451. go func() {
  452. obj := new(models.BaseFromCoalmineMapping)
  453. for _, v := range indexItems {
  454. indexCode := v.IndexCode
  455. var dateItem *models.EdbInfoMaxAndMinInfo
  456. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  457. if err != nil {
  458. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  459. return
  460. }
  461. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  462. }
  463. }()
  464. }
  465. br.Ret = 200
  466. br.Success = true
  467. br.Msg = "获取成功"
  468. return
  469. }
  470. // @Title 刷新数据
  471. // @Description 刷新数据接口
  472. // @Param request body models.CoalMineDataReq true "type json string"
  473. // @Success 200 {object} models.EdbClassify
  474. // @router /inland/history [post]
  475. func (this *CoalMineDataController) InlandHistory() {
  476. br := new(models.BaseResponse).Init()
  477. defer func() {
  478. this.Data["json"] = br
  479. this.ServeJSON()
  480. }()
  481. var req models.CoalMineDataReq
  482. err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
  483. if err != nil {
  484. br.Msg = "参数解析异常!"
  485. br.ErrMsg = "参数解析失败,Err:" + err.Error()
  486. return
  487. }
  488. var mappingItems []*models.BaseFromCoalmineMapping
  489. var indexItems []*models.BaseFromCoalmineInlandIndex
  490. var codeMap = make(map[string]string)
  491. var indexMap = make(map[string]string)
  492. var nameMap = make(map[int]string)
  493. var groupMap = make(map[int]string)
  494. var dataTime string
  495. var sheetName string
  496. codeList, err := models.GetBaseFromCoalmineMapping()
  497. if err != nil && err.Error() != utils.ErrNoRow() {
  498. utils.FileLog.Info("获取煤炭指标失败:", err)
  499. return
  500. }
  501. if len(codeList) > 0 {
  502. for _, v := range codeList {
  503. codeMap[v.IndexName] = v.IndexCode
  504. }
  505. }
  506. indexCompanyList, err := models.GetBaseFromCoalmineInlandIndex()
  507. if err != nil && err.Error() != utils.ErrNoRow() {
  508. utils.FileLog.Info("获取煤炭公司指标失败:", err)
  509. return
  510. }
  511. if len(indexCompanyList) > 0 {
  512. for _, v := range indexCompanyList {
  513. indexMap[v.IndexName+v.DataTime] = v.DealValue
  514. }
  515. }
  516. for _, sheet := range req.SheetData {
  517. sheetName = sheet.Name
  518. fmt.Println("sheetName:", sheetName)
  519. var unit string
  520. sheetName = strings.TrimLeft(sheetName, "内陆")
  521. if sheetName == "可用天数" {
  522. unit = "天"
  523. } else {
  524. unit = "万吨"
  525. }
  526. //遍历行读取
  527. maxCol := sheet.MaxRow
  528. for i := 0; i < maxCol; i++ {
  529. if i == 0 {
  530. row := sheet.Rows[i]
  531. cells := row.Cells
  532. for k, cell := range cells {
  533. if k > 0 && k < 18 {
  534. text := cell.Value
  535. groupMap[k] = text
  536. var item models.BaseFromCoalmineMapping
  537. //合计命名
  538. if k == 9 {
  539. item.IndexName = sheetName
  540. }
  541. item.IndexName = text + sheetName
  542. nameMap[k] = item.IndexName
  543. //code
  544. exists := ContainsSpecialName(text)
  545. var code string
  546. if exists {
  547. abbr := trimProvinceName(text)
  548. //取处理后公司名首字母缩写
  549. a := pinyin.NewArgs()
  550. rows := pinyin.Pinyin(sheetName, a)
  551. for i := 0; i < len(rows); i++ {
  552. if len(rows[i]) != 0 {
  553. str := rows[i][0]
  554. pi := str[0:1]
  555. code += pi
  556. }
  557. }
  558. item.IndexCode = abbr + code + "inland"
  559. } else {
  560. a := pinyin.NewArgs()
  561. rows := pinyin.Pinyin(text+sheetName, a)
  562. for i := 0; i < len(rows); i++ {
  563. if len(rows[i]) != 0 {
  564. str := rows[i][0]
  565. pi := str[0:1]
  566. code += pi
  567. }
  568. }
  569. item.IndexCode = code + "inland"
  570. }
  571. item.CreateTime = time.Now()
  572. mappingItems = append(mappingItems, &item)
  573. }
  574. }
  575. }
  576. if i > 0 {
  577. row := sheet.Rows[i]
  578. cells := row.Cells
  579. for k, cell := range cells {
  580. if k < 18 {
  581. var item models.BaseFromCoalmineInlandIndex
  582. if k == 0 {
  583. text := cell.Value
  584. if text == "" {
  585. continue
  586. }
  587. if len(text) < 6 {
  588. dataTime = ExcelDateToDate(text).Format(utils.FormatDate)
  589. } else {
  590. parsedTime, err := time.Parse("01-02-06", text)
  591. if err != nil {
  592. parsedTime, err = time.Parse("2006/01/02", text)
  593. if err != nil {
  594. fmt.Println("解析时间字符串出错:", err)
  595. return
  596. }
  597. }
  598. dataTime = parsedTime.Format(utils.FormatDate)
  599. }
  600. }
  601. if k > 0 {
  602. text := cell.Value
  603. item.IndexName = nameMap[k]
  604. item.IndexCode = codeMap[nameMap[k]]
  605. item.DealValue = text
  606. item.GroupName = groupMap[k]
  607. item.Source = "内陆十七省"
  608. item.DataTime = dataTime
  609. item.Unit = unit
  610. item.Frequency = "日度"
  611. item.ModifyTime = time.Now()
  612. item.CreateTime = time.Now()
  613. }
  614. indexItems = append(indexItems, &item)
  615. }
  616. }
  617. }
  618. }
  619. }
  620. //添加数据到数据库
  621. for _, v := range mappingItems {
  622. if codeMap[v.IndexName] == "" {
  623. codeMap[v.IndexName] = v.IndexCode
  624. newId, err := models.AddBaseFromCoalmineMapping(v)
  625. if err != nil {
  626. for i := 0; i < 10; i++ {
  627. v.IndexCode = v.IndexCode + strconv.Itoa(i)
  628. codeMap[v.IndexName] = v.IndexCode
  629. newId, err := models.AddBaseFromCoalmineMapping(v)
  630. if err != nil {
  631. fmt.Println("再次添加公司指标名称错误", err)
  632. continue
  633. } else {
  634. fmt.Println("新增公司成功", newId)
  635. break
  636. }
  637. }
  638. } else {
  639. fmt.Println("新增公司成功", newId)
  640. }
  641. }
  642. }
  643. fmt.Println("指标操作完成")
  644. //给indexItem中的code赋值并插入index表
  645. for _, v := range indexItems {
  646. v.IndexCode = codeMap[v.IndexName]
  647. if indexMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
  648. newId, err := models.AddBaseFromCoalInlandIndex(v)
  649. if err != nil {
  650. fmt.Println("添加数据错误", err)
  651. } else {
  652. fmt.Println("新增成功", newId)
  653. }
  654. } else {
  655. if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
  656. err = models.UpdateBaseFromCoalInlandIndex(v)
  657. if err != nil {
  658. fmt.Println("修改数据错误错误", err)
  659. return
  660. }
  661. }
  662. }
  663. }
  664. // 更新指标最新日期
  665. {
  666. go func() {
  667. obj := new(models.BaseFromCoalmineMapping)
  668. for _, v := range indexItems {
  669. indexCode := v.IndexCode
  670. var dateItem *models.EdbInfoMaxAndMinInfo
  671. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  672. if err != nil {
  673. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  674. return
  675. }
  676. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  677. }
  678. }()
  679. }
  680. br.Ret = 200
  681. br.Success = true
  682. br.Msg = "获取成功"
  683. return
  684. }
  685. // @Title 晋陕蒙数据
  686. // @Description 刷新晋陕蒙数据接口
  687. // @Param request body models.CoalMineDataReq true "type json string"
  688. // @Success 200 {object} models.EdbClassify
  689. // @router /jsm [post]
  690. func (this *CoalMineDataController) Jsm() {
  691. br := new(models.BaseResponse).Init()
  692. defer func() {
  693. this.Data["json"] = br
  694. this.ServeJSON()
  695. }()
  696. var req models.CoalMineDataReq
  697. err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
  698. if err != nil {
  699. br.Msg = "参数解析异常!"
  700. br.ErrMsg = "参数解析失败,Err:" + err.Error()
  701. return
  702. }
  703. //path := "/home/code/python/coal_mail/emailFile/沿海八省动力煤终端用户供耗存数据更新.xlsx"
  704. var province string
  705. var description string
  706. var exchange string
  707. var city string
  708. var companyName string
  709. var group string
  710. var dateMap = make(map[int]string)
  711. var codeMap = make(map[string]string)
  712. var indexMap = make(map[string]string)
  713. var codeCompanyMap = make(map[string]string)
  714. var indexCompanyMap = make(map[string]string)
  715. var items []*models.BaseFromCoalmineMapping
  716. var itemsCompany []*models.BaseFromCoalmineMapping
  717. var itemsIndex []*models.BaseFromCoalmineJsmIndex
  718. var itemsCompanyIndex []*models.BaseFromCoalmineCompanyIndex
  719. codeList, err := models.GetBaseFromCoalmineMapping()
  720. if err != nil && err.Error() != utils.ErrNoRow() {
  721. utils.FileLog.Info("获取煤炭指标失败:", err)
  722. return
  723. }
  724. if len(codeList) > 0 {
  725. for _, v := range codeList {
  726. codeMap[v.IndexName] = v.IndexCode
  727. }
  728. }
  729. codeCompanyList, err := models.GetBaseFromCoalmineMapping()
  730. if err != nil && err.Error() != utils.ErrNoRow() {
  731. utils.FileLog.Info("获取煤炭公司指标失败:", err)
  732. return
  733. }
  734. if len(codeCompanyList) > 0 {
  735. for _, v := range codeCompanyList {
  736. codeCompanyMap[v.IndexName] = v.IndexCode
  737. }
  738. }
  739. indexList, err := models.GetBaseFromCoalmineIndex()
  740. if err != nil && err.Error() != utils.ErrNoRow() {
  741. utils.FileLog.Info("获取煤炭公司指标失败:", err)
  742. return
  743. }
  744. if len(indexList) > 0 {
  745. for _, v := range indexList {
  746. indexMap[v.IndexName+v.DataTime] = v.DealValue
  747. }
  748. }
  749. indexCompanyList, err := models.GetBaseFromCoalmineCompanyIndex()
  750. if err != nil && err.Error() != utils.ErrNoRow() {
  751. utils.FileLog.Info("获取煤炭公司指标失败:", err)
  752. return
  753. }
  754. if len(indexCompanyList) > 0 {
  755. for _, v := range indexCompanyList {
  756. indexCompanyMap[v.IndexName+v.DataTime] = v.DealValue
  757. }
  758. }
  759. for _, sheet := range req.SheetData {
  760. //遍历行读取
  761. maxRow := sheet.MaxRow
  762. for i := 0; i < maxRow; i++ {
  763. //获取样本情况
  764. if i > 3 && i < 16 {
  765. row := sheet.Rows[i]
  766. cells := row.Cells
  767. Loop2:
  768. for k, cell := range cells {
  769. text := cell.Value
  770. if text != "" {
  771. if k == 1 {
  772. province = text
  773. }
  774. if k == 3 {
  775. description = text
  776. }
  777. if k == 4 {
  778. item := new(models.BaseFromCoalmineMapping)
  779. exchange = text
  780. exists := ContainsSpecialName(province)
  781. var strResult string
  782. if exists {
  783. abbr := TrimProvinceName(province)
  784. //取处理后公司名首字母缩写
  785. a := pinyin.NewArgs()
  786. rows := pinyin.Pinyin(exchange, a)
  787. for i := 0; i < len(rows); i++ {
  788. if len(rows[i]) != 0 {
  789. str := rows[i][0]
  790. pi := str[0:1]
  791. strResult += pi
  792. }
  793. }
  794. item.IndexCode = abbr + strResult + "jsm"
  795. } else {
  796. //取处理后公司名首字母缩写
  797. a := pinyin.NewArgs()
  798. rows := pinyin.Pinyin(province+exchange, a)
  799. for i := 0; i < len(rows); i++ {
  800. if len(rows[i]) != 0 {
  801. str := rows[i][0]
  802. pi := str[0:1]
  803. strResult += pi
  804. }
  805. }
  806. item.IndexCode = strResult + "jsm"
  807. }
  808. item.IndexName = province + exchange
  809. item.CreateTime = time.Now()
  810. items = append(items, item)
  811. break Loop2
  812. }
  813. }
  814. }
  815. }
  816. //获取日期
  817. if i == 3 {
  818. row := sheet.Rows[i]
  819. cells := row.Cells
  820. for k, cell := range cells {
  821. text := cell.Value
  822. stamp, _ := time.ParseInLocation("01-02-06", text, time.Now().Location())
  823. if k > 4 {
  824. dateMap[k] = stamp.Format(utils.FormatDate)
  825. }
  826. }
  827. }
  828. //获取具体产量
  829. if i > 3 && i < 16 {
  830. row := sheet.Rows[i]
  831. cells := row.Cells
  832. for k, cell := range cells {
  833. text := cell.Value
  834. if k > 4 {
  835. item := new(models.BaseFromCoalmineJsmIndex)
  836. item.IndexName = province + exchange
  837. item.IndexCode = codeMap[item.IndexName]
  838. item.Exchange = exchange
  839. item.DealValue = text
  840. item.DataTime = dateMap[k]
  841. item.Exchange = exchange
  842. item.Description = description
  843. item.Province = province
  844. item.Source = "三省周度"
  845. if exchange == "产量" {
  846. item.Unit = "万吨"
  847. } else {
  848. item.Unit = "百分比"
  849. }
  850. item.Frequency = "周度"
  851. item.ModifyTime = time.Now()
  852. item.CreateTime = time.Now()
  853. itemsIndex = append(itemsIndex, item)
  854. }
  855. }
  856. }
  857. //获取公司指标名称
  858. if i > 17 {
  859. row := sheet.Rows[i]
  860. cells := row.Cells
  861. Loop3:
  862. for k, cell := range cells {
  863. text := cell.Value
  864. if text != "" {
  865. if k == 1 {
  866. province = text
  867. }
  868. if k == 2 {
  869. city = text
  870. }
  871. if k == 3 {
  872. companyName = text
  873. }
  874. if k == 4 {
  875. item := new(models.BaseFromCoalmineMapping)
  876. group = text
  877. trimName := TrimCompanyName(companyName)
  878. item.IndexName = trimName
  879. //取处理后公司名首字母缩写
  880. strResult := ""
  881. a := pinyin.NewArgs()
  882. rows := pinyin.Pinyin(trimName, a)
  883. for i := 0; i < len(rows); i++ {
  884. if len(rows[i]) != 0 {
  885. str := rows[i][0]
  886. pi := str[0:1]
  887. strResult += pi
  888. }
  889. }
  890. item.IndexCode = strResult + "company"
  891. item.CreateTime = time.Now()
  892. itemsCompany = append(itemsCompany, item)
  893. break Loop3
  894. }
  895. }
  896. }
  897. }
  898. //获取公司具体产量
  899. if i > 18 {
  900. row := sheet.Rows[i]
  901. cells := row.Cells
  902. for k, cell := range cells {
  903. text := cell.Value
  904. if k > 4 {
  905. item := new(models.BaseFromCoalmineCompanyIndex)
  906. companyName = TrimCompanyName(companyName)
  907. item.IndexName = companyName
  908. item.IndexCode = codeCompanyMap[item.IndexName]
  909. item.DealValue = text
  910. item.DataTime = dateMap[k]
  911. item.Province = province
  912. item.City = city
  913. //处理无类别名时的情况
  914. if group == "无" {
  915. item.GroupName = companyName
  916. } else {
  917. item.GroupName = group
  918. }
  919. item.Source = "三省企业"
  920. item.Unit = "万吨"
  921. item.Frequency = "周度"
  922. item.ModifyTime = time.Now()
  923. item.CreateTime = time.Now()
  924. //fmt.Println(item)
  925. itemsCompanyIndex = append(itemsCompanyIndex, item)
  926. }
  927. }
  928. }
  929. }
  930. }
  931. //添加数据到数据库
  932. for _, v := range items {
  933. if codeMap[v.IndexName] == "" {
  934. codeMap[v.IndexName] = v.IndexCode
  935. newId, err := models.AddBaseFromCoalmineMapping(v)
  936. if err != nil {
  937. fmt.Println("添加指标名称错误")
  938. } else {
  939. fmt.Println("添加指标名称成功", newId)
  940. }
  941. }
  942. }
  943. fmt.Println("指标操作完成")
  944. for _, v := range itemsIndex {
  945. v.IndexCode = codeMap[v.IndexName]
  946. if indexMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
  947. newId, err := models.AddBaseFromCoalmineIndex(v)
  948. if err != nil {
  949. fmt.Println("添加数据错误", err)
  950. } else {
  951. fmt.Println("新增成功", newId)
  952. }
  953. } else {
  954. if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
  955. err = models.UpdateBaseFromCoalmineIndex(v)
  956. if err != nil {
  957. fmt.Println("修改数据错误错误", err)
  958. return
  959. }
  960. }
  961. }
  962. }
  963. fmt.Println("数据操作完成")
  964. for _, v := range itemsCompany {
  965. if codeCompanyMap[v.IndexName] == "" {
  966. codeCompanyMap[v.IndexName] = v.IndexCode
  967. newId, err := models.AddBaseFromCoalmineMapping(v)
  968. if err != nil {
  969. for i := 0; i < 10; i++ {
  970. v.IndexCode = v.IndexCode + strconv.Itoa(i)
  971. codeCompanyMap[v.IndexName] = v.IndexCode
  972. newId, err := models.AddBaseFromCoalmineMapping(v)
  973. if err != nil {
  974. fmt.Println("再次添加公司指标名称错误", err)
  975. fmt.Println(v.IndexName, v.IndexCode)
  976. continue
  977. } else {
  978. fmt.Println("新增公司成功", newId)
  979. break
  980. }
  981. }
  982. } else {
  983. fmt.Println("新增公司成功", newId)
  984. }
  985. }
  986. }
  987. fmt.Println("公司指标操作完成")
  988. for _, v := range itemsCompanyIndex {
  989. v.IndexCode = codeCompanyMap[v.IndexName]
  990. if indexCompanyMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
  991. newId, err := models.AddBaseFromCoalmineCompanyIndex(v)
  992. if err != nil {
  993. fmt.Println("添加公司数据错误", err)
  994. } else {
  995. fmt.Println("新增公司数据成功", newId)
  996. }
  997. } else {
  998. if indexCompanyMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
  999. err = models.UpdateBaseFromCoalmineCompanyIndex(v)
  1000. if err != nil {
  1001. fmt.Println("修改数据错误错误", err)
  1002. }
  1003. }
  1004. }
  1005. }
  1006. // 更新指标最新日期
  1007. {
  1008. go func() {
  1009. obj := new(models.BaseFromCoalmineMapping)
  1010. for _, v := range items {
  1011. indexCode := v.IndexCode
  1012. var dateItem *models.EdbInfoMaxAndMinInfo
  1013. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  1014. if err != nil {
  1015. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  1016. return
  1017. }
  1018. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  1019. }
  1020. for _, v := range itemsIndex {
  1021. indexCode := v.IndexCode
  1022. var dateItem *models.EdbInfoMaxAndMinInfo
  1023. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  1024. if err != nil {
  1025. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  1026. return
  1027. }
  1028. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  1029. }
  1030. for _, v := range itemsCompany {
  1031. indexCode := v.IndexCode
  1032. var dateItem *models.EdbInfoMaxAndMinInfo
  1033. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  1034. if err != nil {
  1035. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  1036. return
  1037. }
  1038. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  1039. }
  1040. for _, v := range itemsCompanyIndex {
  1041. indexCode := v.IndexCode
  1042. var dateItem *models.EdbInfoMaxAndMinInfo
  1043. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  1044. if err != nil {
  1045. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  1046. return
  1047. }
  1048. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  1049. }
  1050. }()
  1051. }
  1052. br.Ret = 200
  1053. br.Success = true
  1054. br.Msg = "获取成功"
  1055. return
  1056. }
  1057. // @Title 沿海数据
  1058. // @Description 刷新沿海数据接口
  1059. // @Param request body models.CoalMineDataReq true "type json string"
  1060. // @Success 200 {object} models.EdbClassify
  1061. // @router /coastal [post]
  1062. func (this *CoalMineDataController) Coastal() {
  1063. br := new(models.BaseResponse).Init()
  1064. defer func() {
  1065. this.Data["json"] = br
  1066. this.ServeJSON()
  1067. }()
  1068. var req models.CoalMineDataReq
  1069. err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
  1070. if err != nil {
  1071. br.Msg = "参数解析异常!"
  1072. br.ErrMsg = "参数解析失败,Err:" + err.Error()
  1073. return
  1074. }
  1075. var mappingItems []*models.BaseFromCoalmineMapping
  1076. var indexItems []*models.BaseFromCoalmineCoastalIndex
  1077. var codeMap = make(map[string]string)
  1078. var indexMap = make(map[string]string)
  1079. var nameMap = make(map[int]string)
  1080. var groupMap = make(map[int]string)
  1081. var dataTime string
  1082. var sheetName, unit string
  1083. codeList, err := models.GetBaseFromCoalmineMapping()
  1084. if err != nil && err.Error() != utils.ErrNoRow() {
  1085. utils.FileLog.Info("获取煤炭指标失败:", err)
  1086. return
  1087. }
  1088. if len(codeList) > 0 {
  1089. for _, v := range codeList {
  1090. codeMap[v.IndexName] = v.IndexCode
  1091. }
  1092. }
  1093. indexCompanyList, err := models.GetBaseFromCoalmineCoastalIndex()
  1094. if err != nil && err.Error() != utils.ErrNoRow() {
  1095. utils.FileLog.Info("获取煤炭公司指标失败:", err)
  1096. return
  1097. }
  1098. if len(indexCompanyList) > 0 {
  1099. for _, v := range indexCompanyList {
  1100. indexMap[v.IndexName+v.DataTime] = v.DealValue
  1101. }
  1102. }
  1103. for _, sheet := range req.SheetData {
  1104. sheetName = sheet.Name
  1105. //遍历行读取
  1106. maxRow := sheet.MaxRow
  1107. for i := 0; i < maxRow; i++ {
  1108. if i == 0 {
  1109. row := sheet.Rows[i]
  1110. cells := row.Cells
  1111. for k, cell := range cells {
  1112. if k > 0 && k < 9 {
  1113. text := cell.Value
  1114. if text == "" {
  1115. continue
  1116. }
  1117. groupMap[k] = text
  1118. var item models.BaseFromCoalmineMapping
  1119. //合计命名
  1120. if k == 9 {
  1121. item.IndexName = sheetName
  1122. }
  1123. item.IndexName = text + sheetName
  1124. nameMap[k] = item.IndexName
  1125. //code
  1126. exists := ContainsSpecialName(text)
  1127. var code string
  1128. if exists {
  1129. abbr := trimProvinceName(text)
  1130. //取处理后公司名首字母缩写
  1131. a := pinyin.NewArgs()
  1132. rows := pinyin.Pinyin(sheetName, a)
  1133. for i := 0; i < len(rows); i++ {
  1134. if len(rows[i]) != 0 {
  1135. str := rows[i][0]
  1136. pi := str[0:1]
  1137. code += pi
  1138. }
  1139. }
  1140. item.IndexCode = abbr + code + "coastal"
  1141. } else {
  1142. a := pinyin.NewArgs()
  1143. rows := pinyin.Pinyin(text+sheetName, a)
  1144. for i := 0; i < len(rows); i++ {
  1145. if len(rows[i]) != 0 {
  1146. str := rows[i][0]
  1147. pi := str[0:1]
  1148. code += pi
  1149. }
  1150. }
  1151. item.IndexCode = code + "coastal"
  1152. }
  1153. item.CreateTime = time.Now()
  1154. mappingItems = append(mappingItems, &item)
  1155. }
  1156. if k == 10 {
  1157. unit = strings.Replace(cell.Value, "单位:", "", -1)
  1158. }
  1159. }
  1160. }
  1161. if i > 0 {
  1162. var row models.Row
  1163. if i < len(sheet.Rows) {
  1164. row = sheet.Rows[i]
  1165. }
  1166. cells := row.Cells
  1167. for k, cell := range cells {
  1168. if k < 9 {
  1169. var item models.BaseFromCoalmineCoastalIndex
  1170. if k == 0 {
  1171. text := cell.Value
  1172. if text == "" {
  1173. continue
  1174. }
  1175. if len(text) < 6 {
  1176. dataTime = ExcelDateToDate(text).Format(utils.FormatDate)
  1177. } else {
  1178. parsedTime, err := time.Parse("01-02-06", text)
  1179. if err != nil {
  1180. parsedTime, err = time.Parse("2006/01/02", text)
  1181. if err != nil {
  1182. fmt.Println("解析时间字符串出错:", err)
  1183. return
  1184. }
  1185. }
  1186. dataTime = parsedTime.Format(utils.FormatDate)
  1187. }
  1188. }
  1189. if k > 0 {
  1190. text := cell.Value
  1191. item.IndexName = nameMap[k]
  1192. item.IndexCode = codeMap[nameMap[k]]
  1193. item.DealValue = text
  1194. item.GroupName = groupMap[k]
  1195. item.Source = "沿海八省"
  1196. item.DataTime = dataTime
  1197. item.Frequency = "日度"
  1198. item.ModifyTime = time.Now()
  1199. item.CreateTime = time.Now()
  1200. }
  1201. item.Unit = unit
  1202. indexItems = append(indexItems, &item)
  1203. }
  1204. }
  1205. }
  1206. }
  1207. }
  1208. //添加数据到数据库
  1209. for _, v := range mappingItems {
  1210. if codeMap[v.IndexName] == "" {
  1211. codeMap[v.IndexName] = v.IndexCode
  1212. newId, err := models.AddBaseFromCoalmineMapping(v)
  1213. if err != nil {
  1214. for i := 0; i < 10; i++ {
  1215. v.IndexCode = v.IndexCode + strconv.Itoa(i)
  1216. codeMap[v.IndexName] = v.IndexCode
  1217. newId, err := models.AddBaseFromCoalmineMapping(v)
  1218. if err != nil {
  1219. utils.FileLog.Info("再次添加公司指标名称错误:", err)
  1220. fmt.Println("再次添加公司指标名称错误", err)
  1221. continue
  1222. } else {
  1223. fmt.Println("新增公司成功", newId)
  1224. break
  1225. }
  1226. }
  1227. } else {
  1228. fmt.Println("新增公司成功", newId)
  1229. }
  1230. }
  1231. }
  1232. utils.FileLog.Info("指标操作完成")
  1233. fmt.Println("指标操作完成")
  1234. //给indexItem中的code赋值并插入index表
  1235. for _, v := range indexItems {
  1236. v.IndexCode = codeMap[v.IndexName]
  1237. if indexMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
  1238. newId, err := models.AddBaseFromCoalCoastalIndex(v)
  1239. if err != nil {
  1240. fmt.Println("添加数据错误", err)
  1241. utils.FileLog.Info("添加数据错误:", err)
  1242. } else {
  1243. fmt.Println("新增成功", newId)
  1244. }
  1245. } else {
  1246. if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
  1247. err = models.UpdateBaseFromCoalCoastalIndex(v)
  1248. if err != nil {
  1249. utils.FileLog.Info("修改数据错误错误:", err)
  1250. fmt.Println("修改数据错误错误", err)
  1251. return
  1252. }
  1253. }
  1254. }
  1255. }
  1256. // 更新指标最新日期
  1257. {
  1258. go func() {
  1259. obj := new(models.BaseFromCoalmineMapping)
  1260. for _, v := range indexItems {
  1261. indexCode := v.IndexCode
  1262. var dateItem *models.EdbInfoMaxAndMinInfo
  1263. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  1264. if err != nil {
  1265. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  1266. return
  1267. }
  1268. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  1269. }
  1270. }()
  1271. }
  1272. br.Ret = 200
  1273. br.Success = true
  1274. br.Msg = "获取成功"
  1275. return
  1276. }
  1277. // @Title 内陆数据
  1278. // @Description 刷新内陆数据接口
  1279. // @Param request body models.CoalMineDataReq true "type json string"
  1280. // @Success 200 {object} models.EdbClassify
  1281. // @router /inland [post]
  1282. func (this *CoalMineDataController) Inland() {
  1283. br := new(models.BaseResponse).Init()
  1284. defer func() {
  1285. this.Data["json"] = br
  1286. this.ServeJSON()
  1287. }()
  1288. var req models.CoalMineDataReq
  1289. err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
  1290. if err != nil {
  1291. br.Msg = "参数解析异常!"
  1292. br.ErrMsg = "参数解析失败,Err:" + err.Error()
  1293. return
  1294. }
  1295. var mappingItems []*models.BaseFromCoalmineMapping
  1296. var indexItems []*models.BaseFromCoalmineInlandIndex
  1297. var codeMap = make(map[string]string)
  1298. var indexMap = make(map[string]string)
  1299. var nameMap = make(map[int]string)
  1300. var groupMap = make(map[int]string)
  1301. var dataTime string
  1302. var sheetName string
  1303. codeList, err := models.GetBaseFromCoalmineMapping()
  1304. if err != nil && err.Error() != utils.ErrNoRow() {
  1305. utils.FileLog.Info("获取煤炭指标失败:", err)
  1306. return
  1307. }
  1308. if len(codeList) > 0 {
  1309. for _, v := range codeList {
  1310. codeMap[v.IndexName] = v.IndexCode
  1311. }
  1312. }
  1313. indexCompanyList, err := models.GetBaseFromCoalmineInlandIndex()
  1314. if err != nil && err.Error() != utils.ErrNoRow() {
  1315. utils.FileLog.Info("获取煤炭公司指标失败:", err)
  1316. return
  1317. }
  1318. if len(indexCompanyList) > 0 {
  1319. for _, v := range indexCompanyList {
  1320. indexMap[v.IndexName+v.DataTime] = v.DealValue
  1321. }
  1322. }
  1323. for _, sheet := range req.SheetData {
  1324. sheetName = sheet.Name
  1325. fmt.Println("sheetName:", sheetName)
  1326. var unit string
  1327. if sheetName == "可用天数" {
  1328. unit = "天"
  1329. } else {
  1330. unit = "万吨"
  1331. }
  1332. //遍历行读取
  1333. maxCol := sheet.MaxCol
  1334. for i := 0; i < maxCol; i++ {
  1335. if i == 0 {
  1336. row := sheet.Rows[i]
  1337. cells := row.Cells
  1338. for k, cell := range cells {
  1339. if k > 0 && k < 18 {
  1340. text := cell.Value
  1341. groupMap[k] = text
  1342. var item models.BaseFromCoalmineMapping
  1343. //合计命名
  1344. if k == 9 {
  1345. item.IndexName = sheetName
  1346. }
  1347. item.IndexName = text + sheetName
  1348. nameMap[k] = item.IndexName
  1349. //code
  1350. exists := ContainsSpecialName(text)
  1351. var code string
  1352. if exists {
  1353. abbr := trimProvinceName(text)
  1354. //取处理后公司名首字母缩写
  1355. a := pinyin.NewArgs()
  1356. rows := pinyin.Pinyin(sheetName, a)
  1357. for i := 0; i < len(rows); i++ {
  1358. if len(rows[i]) != 0 {
  1359. str := rows[i][0]
  1360. pi := str[0:1]
  1361. code += pi
  1362. }
  1363. }
  1364. item.IndexCode = abbr + code + "inland"
  1365. } else {
  1366. a := pinyin.NewArgs()
  1367. rows := pinyin.Pinyin(text+sheetName, a)
  1368. for i := 0; i < len(rows); i++ {
  1369. if len(rows[i]) != 0 {
  1370. str := rows[i][0]
  1371. pi := str[0:1]
  1372. code += pi
  1373. }
  1374. }
  1375. item.IndexCode = code + "inland"
  1376. }
  1377. item.CreateTime = time.Now()
  1378. mappingItems = append(mappingItems, &item)
  1379. }
  1380. }
  1381. }
  1382. if i > 0 {
  1383. var row models.Row
  1384. if i < len(sheet.Rows) {
  1385. row = sheet.Rows[i]
  1386. }
  1387. cells := row.Cells
  1388. for k, cell := range cells {
  1389. if k < 18 {
  1390. var item models.BaseFromCoalmineInlandIndex
  1391. if k == 0 {
  1392. text := cell.Value
  1393. if text == "" {
  1394. continue
  1395. }
  1396. if len(text) < 6 {
  1397. dataTime = ExcelDateToDate(text).Format(utils.FormatDate)
  1398. } else {
  1399. parsedTime, err := time.Parse("01-02-06", text)
  1400. if err != nil {
  1401. parsedTime, err = time.Parse("2006/01/02", text)
  1402. if err != nil {
  1403. utils.FileLog.Info("解析时间字符串出错:", err)
  1404. fmt.Println("解析时间字符串出错:", err)
  1405. return
  1406. }
  1407. }
  1408. dataTime = parsedTime.Format(utils.FormatDate)
  1409. }
  1410. }
  1411. if k > 0 {
  1412. text := cell.Value
  1413. item.IndexName = nameMap[k]
  1414. item.IndexCode = codeMap[nameMap[k]]
  1415. item.DealValue = text
  1416. item.GroupName = groupMap[k]
  1417. item.Source = "内陆十七省"
  1418. item.DataTime = dataTime
  1419. item.Unit = unit
  1420. item.Frequency = "日度"
  1421. item.ModifyTime = time.Now()
  1422. item.CreateTime = time.Now()
  1423. }
  1424. indexItems = append(indexItems, &item)
  1425. }
  1426. }
  1427. }
  1428. }
  1429. }
  1430. //添加数据到数据库
  1431. for _, v := range mappingItems {
  1432. if codeMap[v.IndexName] == "" {
  1433. codeMap[v.IndexName] = v.IndexCode
  1434. newId, err := models.AddBaseFromCoalmineMapping(v)
  1435. if err != nil {
  1436. for i := 0; i < 10; i++ {
  1437. v.IndexCode = v.IndexCode + strconv.Itoa(i)
  1438. codeMap[v.IndexName] = v.IndexCode
  1439. newId, err := models.AddBaseFromCoalmineMapping(v)
  1440. if err != nil {
  1441. utils.FileLog.Info("再次添加公司指标名称错误:", err)
  1442. fmt.Println("再次添加公司指标名称错误", err)
  1443. continue
  1444. } else {
  1445. fmt.Println("新增公司成功", newId)
  1446. break
  1447. }
  1448. }
  1449. } else {
  1450. fmt.Println("新增公司成功", newId)
  1451. }
  1452. }
  1453. }
  1454. utils.FileLog.Info("指标操作完成:")
  1455. fmt.Println("指标操作完成")
  1456. //给indexItem中的code赋值并插入index表
  1457. for _, v := range indexItems {
  1458. v.IndexCode = codeMap[v.IndexName]
  1459. if indexMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
  1460. newId, err := models.AddBaseFromCoalInlandIndex(v)
  1461. if err != nil {
  1462. utils.FileLog.Info("添加数据错误:", err)
  1463. fmt.Println("添加数据错误", err)
  1464. } else {
  1465. fmt.Println("新增成功", newId)
  1466. }
  1467. } else {
  1468. if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
  1469. err = models.UpdateBaseFromCoalInlandIndex(v)
  1470. if err != nil {
  1471. utils.FileLog.Info("修改数据错误错误:", err)
  1472. fmt.Println("修改数据错误错误", err)
  1473. return
  1474. }
  1475. }
  1476. }
  1477. }
  1478. // 更新指标最新日期
  1479. {
  1480. go func() {
  1481. obj := new(models.BaseFromCoalmineMapping)
  1482. for _, v := range indexItems {
  1483. indexCode := v.IndexCode
  1484. var dateItem *models.EdbInfoMaxAndMinInfo
  1485. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  1486. if err != nil {
  1487. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  1488. return
  1489. }
  1490. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  1491. }
  1492. }()
  1493. }
  1494. br.Ret = 200
  1495. br.Success = true
  1496. br.Msg = "获取成功"
  1497. return
  1498. }
  1499. // @Title 内陆数据
  1500. // @Description 刷新内陆数据接口
  1501. // @Param request body models.CoalMineDataReq true "type json string"
  1502. // @Success 200 {object} models.EdbClassify
  1503. // @router /firm [post]
  1504. func (this *CoalMineDataController) Firm() {
  1505. br := new(models.BaseResponse).Init()
  1506. defer func() {
  1507. this.Data["json"] = br
  1508. this.ServeJSON()
  1509. }()
  1510. var req models.CoalMineDataReq
  1511. err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
  1512. if err != nil {
  1513. br.Msg = "参数解析异常!"
  1514. br.ErrMsg = "参数解析失败,Err:" + err.Error()
  1515. return
  1516. }
  1517. var year, month, day string
  1518. var yearMap = make(map[int]string)
  1519. var monthMap = make(map[int]string)
  1520. var dateMap = make(map[int]string)
  1521. var mappingItems []*models.BaseFromCoalmineMapping
  1522. var indexItems []*models.BaseFromCoalmineFirmIndex
  1523. var codeMap = make(map[string]string)
  1524. var indexMap = make(map[string]string)
  1525. var groupName string
  1526. codeList, err := models.GetBaseFromCoalmineMapping()
  1527. if err != nil && err.Error() != utils.ErrNoRow() {
  1528. utils.FileLog.Info("获取煤炭指标失败:", err)
  1529. return
  1530. }
  1531. if len(codeList) > 0 {
  1532. for _, v := range codeList {
  1533. codeMap[v.IndexName] = v.IndexCode
  1534. }
  1535. }
  1536. indexCompanyList, err := models.GetBaseFromCoalmineFirmIndex()
  1537. if err != nil && err.Error() != utils.ErrNoRow() {
  1538. utils.FileLog.Info("获取煤炭公司指标失败:", err)
  1539. return
  1540. }
  1541. if len(indexCompanyList) > 0 {
  1542. for _, v := range indexCompanyList {
  1543. indexMap[v.IndexName+v.DataTime] = v.DealValue
  1544. }
  1545. }
  1546. for _, sheet := range req.SheetData {
  1547. //遍历行读取
  1548. maxRow := sheet.MaxRow
  1549. for i := 0; i < maxRow; i++ {
  1550. //获取年份
  1551. //if i == 2 {
  1552. // row := sheet.Row(i)
  1553. // cells := row.Cells
  1554. // for k, cell := range cells {
  1555. // text := cell.String()
  1556. // if k > 0 && text != "" {
  1557. // year = text
  1558. // fmt.Println("year:",year)
  1559. // fmt.Println("k:",k)
  1560. // yearMap[k] = year
  1561. // }
  1562. // }
  1563. //}
  1564. //获取月份和年度
  1565. if i == 3 {
  1566. row := sheet.Rows[i]
  1567. cells := row.Cells
  1568. for k, cell := range cells {
  1569. text := cell.Value
  1570. if k > 0 && text != "" {
  1571. month = text
  1572. month = utils.ConvertToFormatDay(month)
  1573. time, _ := time.Parse(utils.FormatDate, month)
  1574. yearInt := time.Year()
  1575. monthInt := int(time.Month())
  1576. year = strconv.Itoa(yearInt)
  1577. yearMap[k] = year
  1578. month = strconv.Itoa(monthInt)
  1579. monthMap[k] = month
  1580. }
  1581. }
  1582. }
  1583. //获取旬度
  1584. if i == 4 {
  1585. row := sheet.Rows[i]
  1586. cells := row.Cells
  1587. for k, cell := range cells {
  1588. text := cell.Value
  1589. if k > 0 && text != "" {
  1590. if yearMap[k] != "" {
  1591. year = yearMap[k]
  1592. }
  1593. if monthMap[k] != "" {
  1594. month = monthMap[k]
  1595. }
  1596. day = text
  1597. dateMap[k] = year + "年" + month + "月" + day
  1598. }
  1599. }
  1600. }
  1601. //获取企业或地区名及信息
  1602. if i > 4 {
  1603. row := sheet.Rows[i]
  1604. cells := row.Cells
  1605. var companyName string
  1606. for k, cell := range cells {
  1607. if k == 0 {
  1608. companyName = cell.Value
  1609. //省名
  1610. if !strings.Contains(companyName, " ") {
  1611. groupName = companyName
  1612. }
  1613. if groupName == "全国合计" {
  1614. groupName = "全国"
  1615. }
  1616. //若有下面四个名称要拼上省名不然会重复
  1617. if companyName == " 地方合计" || companyName == " 地方国有" ||
  1618. companyName == " 地方乡镇" || companyName == " 国有重点" {
  1619. companyName = groupName + companyName
  1620. }
  1621. companyName = strings.Replace(companyName, " ", "", -1)
  1622. var item models.BaseFromCoalmineMapping
  1623. //生成code
  1624. exists := ContainsSpecialName(companyName)
  1625. var code string
  1626. if exists {
  1627. abbr := trimProvinceName(companyName)
  1628. //取处理后公司名首字母缩写
  1629. a := pinyin.NewArgs()
  1630. rows := pinyin.Pinyin(companyName[9:], a)
  1631. for i := 0; i < len(rows); i++ {
  1632. if len(rows[i]) != 0 {
  1633. str := rows[i][0]
  1634. pi := str[0:1]
  1635. code += pi
  1636. }
  1637. }
  1638. item.IndexCode = abbr[:2] + code + "firm"
  1639. } else {
  1640. a := pinyin.NewArgs()
  1641. rows := pinyin.Pinyin(companyName, a)
  1642. for i := 0; i < len(rows); i++ {
  1643. if len(rows[i]) != 0 {
  1644. str := rows[i][0]
  1645. pi := str[0:1]
  1646. code += pi
  1647. }
  1648. }
  1649. item.IndexCode = code + "firm"
  1650. }
  1651. item.IndexName = companyName
  1652. item.CreateTime = time.Now()
  1653. mappingItems = append(mappingItems, &item)
  1654. } else {
  1655. dealValue := cell.Value
  1656. item := models.BaseFromCoalmineFirmIndex{
  1657. IndexName: companyName,
  1658. IndexCode: codeMap[companyName],
  1659. DataTime: dateMap[k],
  1660. DealValue: dealValue,
  1661. GroupName: groupName,
  1662. Source: "全国分企业",
  1663. Unit: "万吨",
  1664. Frequency: "旬度",
  1665. CreateTime: time.Now(),
  1666. ModifyTime: time.Now(),
  1667. }
  1668. indexItems = append(indexItems, &item)
  1669. }
  1670. }
  1671. }
  1672. }
  1673. }
  1674. //添加数据到数据库
  1675. for _, v := range mappingItems {
  1676. if codeMap[v.IndexName] == "" {
  1677. codeMap[v.IndexName] = v.IndexCode
  1678. newId, err := models.AddBaseFromCoalmineMapping(v)
  1679. if err != nil {
  1680. for i := 0; i < 10; i++ {
  1681. v.IndexCode = v.IndexCode + strconv.Itoa(i)
  1682. codeMap[v.IndexName] = v.IndexCode
  1683. newId, err := models.AddBaseFromCoalmineMapping(v)
  1684. if err != nil {
  1685. fmt.Println("再次添加公司指标名称错误", err)
  1686. continue
  1687. } else {
  1688. fmt.Println("新增公司成功", newId)
  1689. break
  1690. }
  1691. }
  1692. } else {
  1693. fmt.Println("新增公司成功", newId)
  1694. }
  1695. }
  1696. }
  1697. fmt.Println("指标操作完成")
  1698. //给indexItem中的code赋值并插入index表
  1699. for _, v := range indexItems {
  1700. v.IndexCode = codeMap[v.IndexName]
  1701. if indexMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
  1702. newId, err := models.AddBaseFromCoalFirmIndex(v)
  1703. if err != nil {
  1704. fmt.Println("添加数据错误", err)
  1705. } else {
  1706. fmt.Println("新增成功", newId)
  1707. }
  1708. } else {
  1709. if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
  1710. err = models.UpdateBaseFromCoalFirmIndex(v)
  1711. if err != nil {
  1712. fmt.Println("修改数据错误错误", err)
  1713. return
  1714. }
  1715. }
  1716. }
  1717. }
  1718. // 更新指标最新日期
  1719. {
  1720. go func() {
  1721. obj := new(models.BaseFromCoalmineMapping)
  1722. for _, v := range indexItems {
  1723. indexCode := v.IndexCode
  1724. var dateItem *models.EdbInfoMaxAndMinInfo
  1725. dateItem, err = obj.GetMaxAndMinDateByIndexCode(indexCode)
  1726. if err != nil {
  1727. err = fmt.Errorf("查询指标最新日期失败 Err:%s", err)
  1728. return
  1729. }
  1730. obj.ModifyIndexMaxAndMinDate(indexCode, dateItem)
  1731. }
  1732. }()
  1733. }
  1734. br.Ret = 200
  1735. br.Success = true
  1736. br.Msg = "获取成功"
  1737. return
  1738. }
  1739. func ttrimProvinceName(name string) string {
  1740. name = strings.Replace(name, "陕西省", "sn", -1)
  1741. name = strings.Replace(name, "陕西", "sn", -1)
  1742. name = strings.Replace(name, "海南市", "hi", -1)
  1743. name = strings.Replace(name, "海南", "hi", -1)
  1744. name = strings.Replace(name, "河南省", "ha", -1)
  1745. name = strings.Replace(name, "河南", "ha", -1)
  1746. name = strings.Replace(name, "河北省", "he", -1)
  1747. name = strings.Replace(name, "河北", "he", -1)
  1748. name = strings.Replace(name, "澳门", "mo", -1)
  1749. name = strings.Replace(name, "内蒙古自治区", "nm", -1)
  1750. name = strings.Replace(name, "内蒙古", "nm", -1)
  1751. name = strings.Replace(name, "黑龙江", "hl", -1)
  1752. name = strings.Replace(name, "(", "", -1)
  1753. name = strings.Replace(name, ")", "", -1)
  1754. name = strings.Replace(name, "+", "", -1)
  1755. return name
  1756. }
  1757. func ExcelDateToDate(excelDate string) time.Time {
  1758. excelTime := time.Date(1899, time.December, 30, 0, 0, 0, 0, time.UTC)
  1759. var days, _ = strconv.Atoi(excelDate)
  1760. return excelTime.Add(time.Second * time.Duration(days*86400))
  1761. }
  1762. func PathExists(path string) (bool, error) {
  1763. _, err := os.Stat(path)
  1764. if err == nil { //文件或者目录存在
  1765. return true, nil
  1766. }
  1767. if os.IsNotExist(err) {
  1768. return false, nil
  1769. }
  1770. return false, err
  1771. }
  1772. func TrimCompanyName(name string) string {
  1773. name = strings.Replace(name, "有限", "", -1)
  1774. name = strings.Replace(name, "股份", "", -1)
  1775. name = strings.Replace(name, "责任", "", -1)
  1776. name = strings.Replace(name, "公司", "", -1)
  1777. name = strings.Replace(name, "(", "", -1)
  1778. name = strings.Replace(name, ")", "", -1)
  1779. return name
  1780. }
  1781. func ContainsSpecialName(name string) bool {
  1782. return strings.Contains(name, "陕西") ||
  1783. strings.Contains(name, "海南") ||
  1784. strings.Contains(name, "河南") ||
  1785. strings.Contains(name, "河北") ||
  1786. strings.Contains(name, "澳门") ||
  1787. strings.Contains(name, "内蒙古") ||
  1788. strings.Contains(name, "黑龙江")
  1789. }
  1790. func TrimProvinceName(name string) string {
  1791. name = strings.Replace(name, "陕西省", "sns", -1)
  1792. name = strings.Replace(name, "陕西", "sn", -1)
  1793. name = strings.Replace(name, "海南市", "hi", -1)
  1794. name = strings.Replace(name, "海南", "hi", -1)
  1795. name = strings.Replace(name, "河南省", "has", -1)
  1796. name = strings.Replace(name, "河南", "ha", -1)
  1797. name = strings.Replace(name, "河北省", "hes", -1)
  1798. name = strings.Replace(name, "河北", "he", -1)
  1799. name = strings.Replace(name, "澳门", "mo", -1)
  1800. name = strings.Replace(name, "内蒙古自治区", "nm", -1)
  1801. name = strings.Replace(name, "内蒙古", "nm", -1)
  1802. name = strings.Replace(name, "黑龙江", "hl", -1)
  1803. name = strings.Replace(name, "(", "", -1)
  1804. name = strings.Replace(name, ")", "", -1)
  1805. name = strings.Replace(name, "+", "", -1)
  1806. return name
  1807. }
  1808. func trimProvinceName(name string) string {
  1809. name = strings.Replace(name, "陕西省", "sn", -1)
  1810. name = strings.Replace(name, "陕西", "sn", -1)
  1811. name = strings.Replace(name, "海南市", "hi", -1)
  1812. name = strings.Replace(name, "海南可用", "hiky", -1)
  1813. name = strings.Replace(name, "海南", "hi", -1)
  1814. name = strings.Replace(name, "河南省", "ha", -1)
  1815. name = strings.Replace(name, "河南", "ha", -1)
  1816. name = strings.Replace(name, "河北省", "he", -1)
  1817. name = strings.Replace(name, "河北", "he", -1)
  1818. name = strings.Replace(name, "澳门", "mo", -1)
  1819. name = strings.Replace(name, "内蒙古自治区", "nm", -1)
  1820. name = strings.Replace(name, "内蒙古", "nm", -1)
  1821. name = strings.Replace(name, "黑龙江", "hl", -1)
  1822. name = strings.Replace(name, "(", "", -1)
  1823. name = strings.Replace(name, ")", "", -1)
  1824. name = strings.Replace(name, "+", "", -1)
  1825. return name
  1826. }