eta_api/controllers/data_manage/national_statistics.go

package data_manage

import (
	"encoding/json"
	"fmt"
	"github.com/tealeg/xlsx"
	"hongze/hz_eta_api/controllers"
	"hongze/hz_eta_api/models"
	"hongze/hz_eta_api/models/data_manage"
	"hongze/hz_eta_api/utils"
	"os"
	"path/filepath"
	"time"
)

// BaseFromNationalStatisticsController 数据源-国家统计局
type BaseFromNationalStatisticsController struct {
	controllers.BaseAuthController
}

// DbList
// @Title 数据库列表
// @Description 数据库列表
// @Success 200 Ret=200 获取成功
// @router /base_from_national_statistics/db_list [get]
func (this *BaseFromNationalStatisticsController) DbList() {
	br := new(models.BaseResponse).Init()
	defer func() {
		if br.ErrMsg == "" {
			br.IsSendEmail = false
		}
		this.Data["json"] = br
		this.ServeJSON()
	}()
	sysUser := this.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录, SysUser Is Empty"
		br.Ret = 408
		return
	}

	type DbInfo struct {
		Dbcode   string   `description:"数据库编码"`
		Dbname   string   `description:"数据库名称"`
		Sort     int      `description:"排序"`
		Children []DbInfo `description:"数据库列表"`
	}
	// 基本不会变动
	data := `[{"dbcode":"hgyd","dbname":"月度数据","sort":1,"children":null},{"dbcode":"hgjd","dbname":"季度数据","sort":2,"children":null},{"dbcode":"hgnd","dbname":"年度数据","sort":3,"children":null},{"dbcode":"","dbname":"地区数据","sort":4,"children":[{"dbcode":"fsyd","dbname":"分省月度数据","sort":1,"children":null},{"dbcode":"fsjd","dbname":"分省季度数据","sort":2,"children":null},{"dbcode":"fsnd","dbname":"分省年度数据","sort":3,"children":null},{"dbcode":"csyd","dbname":"主要城市月度价格","sort":4,"children":null},{"dbcode":"csnd","dbname":"主要城市年度数据","sort":5,"children":null},{"dbcode":"gatyd","dbname":"港澳台月度数据","sort":6,"children":null},{"dbcode":"gatnd","dbname":"港澳台年度数据","sort":7,"children":null}]},{"dbcode":"","dbname":"国际数据","sort":5,"children":[{"dbcode":"gjyd","dbname":"主要国家(地区)月度数据","sort":1,"children":null},{"dbcode":"gjydsdj","dbname":"三大经济体月度数据","sort":2,"children":null},{"dbcode":"gjydsc","dbname":"国际市场月度商品价格","sort":3,"children":null},{"dbcode":"gjnd","dbname":"主要国家(地区)年度数据","sort":4,"children":null}]}]`
	var list []DbInfo
	if e := json.Unmarshal([]byte(data), &list); e != nil {
		br.Msg = "获取失败"
		br.ErrMsg = "解析数据库列表信息失败, Err: " + e.Error()
		return
	}

	br.Data = list
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
}

// ClassifyList
// @Title 指标分类列表
// @Description 指标分类列表
// @Param   Dbcode  query  string  true  "数据库编码"
// @Success 200 Ret=200 获取成功
// @router /base_from_national_statistics/classify_list [get]
func (this *BaseFromNationalStatisticsController) ClassifyList() {
	br := new(models.BaseResponse).Init()
	defer func() {
		if br.ErrMsg == "" {
			br.IsSendEmail = false
		}
		this.Data["json"] = br
		this.ServeJSON()
	}()
	sysUser := this.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录, SysUser Is Empty"
		br.Ret = 408
		return
	}
	dbcode := this.GetString("Dbcode", "")
	if dbcode == "" {
		br.Msg = "参数有误"
		return
	}

	classifyOB := new(data_manage.BaseFromNationalStatisticsClassify)
	classifyCond := ` AND dbcode = ? AND wdcode = ? `
	classifyPars := make([]interface{}, 0)
	classifyPars = append(classifyPars, dbcode, "zb")
	classifies, e := classifyOB.GetItemsByCondition(classifyCond, classifyPars, []string{}, "base_from_national_statistics_classify_id ASC")
	if e != nil {
		br.Msg = "获取失败"
		br.ErrMsg = "获取统计局指标分类列表失败, Err: " + e.Error()
		return
	}
	classifyMap := make(map[string]*data_manage.BaseFromNationalStatisticsClassify)
	for _, c := range classifies {
		classifyMap[c.Id] = c
	}
	topClassify := make([]*data_manage.BaseFromNationalStatisticsClassify, 0)
	childMap := make(map[string][]*data_manage.BaseFromNationalStatisticsClassify)
	//parentMap := make(map[string]*data_manage.BaseFromNationalStatisticsClassify)
	for _, c := range classifies {
		if c.Pid != "" {
			if childMap[c.Pid] == nil {
				childMap[c.Pid] = make([]*data_manage.BaseFromNationalStatisticsClassify, 0)
			}
			childMap[c.Pid] = append(childMap[c.Pid], c)
			//parentMap[c.Id] = classifyMap[c.Pid]
			continue
		}
		topClassify = append(topClassify, c)
	}

	list := make([]*data_manage.BaseFromNationalStatisticsClassifyItem, 0)
	for _, t := range topClassify {
		v := new(data_manage.BaseFromNationalStatisticsClassifyItem)
		v.ClassifyId = t.BaseFromNationalStatisticsClassifyId
		v.ClassifyName = t.ClassifyName
		v.Dbcode = dbcode
		v.IsParent = t.IsParent
		// 二级分类
		v.Children = make([]*data_manage.BaseFromNationalStatisticsClassifyItem, 0)
		children := childMap[t.Id]
		if children != nil && len(children) > 0 {
			for _, l2 := range children {
				v2 := new(data_manage.BaseFromNationalStatisticsClassifyItem)
				v2.ClassifyId = l2.BaseFromNationalStatisticsClassifyId
				v2.ClassifyName = l2.ClassifyName
				v2.Dbcode = dbcode
				v2.IsParent = l2.IsParent
				v2.ParentId = t.BaseFromNationalStatisticsClassifyId
				v2.ParentIds = append(v2.ParentIds, t.BaseFromNationalStatisticsClassifyId)
				v2.Children = make([]*data_manage.BaseFromNationalStatisticsClassifyItem, 0)
				// 三级分类
				childLevel3 := childMap[l2.Id]
				if childLevel3 != nil && len(childLevel3) > 0 {
					for _, l3 := range childLevel3 {
						v3 := new(data_manage.BaseFromNationalStatisticsClassifyItem)
						v3.ClassifyId = l3.BaseFromNationalStatisticsClassifyId
						v3.ClassifyName = l3.ClassifyName
						v3.Dbcode = dbcode
						v3.IsParent = l3.IsParent
						v3.ParentId = l2.BaseFromNationalStatisticsClassifyId
						v3.ParentIds = append(v3.ParentIds, t.BaseFromNationalStatisticsClassifyId, l2.BaseFromNationalStatisticsClassifyId)
						v3.Children = make([]*data_manage.BaseFromNationalStatisticsClassifyItem, 0)
						// 目前应该最多就三级了, 以防万一多写一层
						childLevel4 := childMap[l3.Id]
						if childLevel4 != nil && len(childLevel4) > 0 {
							for _, l4 := range childLevel4 {
								v4 := new(data_manage.BaseFromNationalStatisticsClassifyItem)
								v4.ClassifyId = l4.BaseFromNationalStatisticsClassifyId
								v4.ClassifyName = l4.ClassifyName
								v4.Dbcode = dbcode
								v4.IsParent = l4.IsParent
								v4.ParentId = l3.BaseFromNationalStatisticsClassifyId
								v4.ParentIds = append(v4.ParentIds, t.BaseFromNationalStatisticsClassifyId, l2.BaseFromNationalStatisticsClassifyId, l3.BaseFromNationalStatisticsClassifyId)
								v4.Children = make([]*data_manage.BaseFromNationalStatisticsClassifyItem, 0)
								v3.Children = append(v3.Children, v4)
							}
						}
						v2.Children = append(v2.Children, v3)
					}
				}
				v.Children = append(v.Children, v2)
			}
		}
		list = append(list, v)
	}

	br.Data = list
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
}

// IndexList
// @Title 指标列表
// @Description 指标列表
// @Param   Dbcode  query  string  true  "数据库编码"
// @Param   ClassifyId  query  int  false  "指标分类ID"
// @Param   Keywords  query  string  false  "关键词-指标名称/指标ID"
// @Success 200 Ret=200 获取成功
// @router /base_from_national_statistics/index_list [get]
func (this *BaseFromNationalStatisticsController) IndexList() {
	br := new(models.BaseResponse).Init()
	defer func() {
		if br.ErrMsg == "" {
			br.IsSendEmail = false
		}
		this.Data["json"] = br
		this.ServeJSON()
	}()
	sysUser := this.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录, SysUser Is Empty"
		br.Ret = 408
		return
	}
	dbcode := this.GetString("Dbcode", "")
	if dbcode == "" {
		br.Msg = "请选择指标库"
		return
	}
	classifyId, _ := this.GetInt("ClassifyId", 0)
	keywords := this.GetString("Keywords", "")

	indexOB := new(data_manage.BaseFromNationalStatisticsIndex)
	indexCond := ` AND dbcode = ?`
	indexPars := make([]interface{}, 0)
	indexPars = append(indexPars, dbcode)
	limitNum := 0
	if keywords != "" {
		k := fmt.Sprint("%", keywords, "%")
		indexCond += ` AND (index_code LIKE ? OR CONCAT(reg, index_name) LIKE ?)`
		indexPars = append(indexPars, k, k)
		limitNum = 100 // 有关键词时限制100条不然前端数据量可能会很大
	}
	if classifyId > 0 {
		indexCond += ` AND base_from_national_statistics_classify_id = ?`
		indexPars = append(indexPars, classifyId)
	}
	indexes, e := indexOB.GetItemsByCondition(indexCond, indexPars, []string{}, "base_from_national_statistics_index_id ASC", limitNum)
	if e != nil {
		br.Msg = "获取失败"
		br.ErrMsg = "获取统计局指标列表失败, Err: " + e.Error()
		return
	}

	resp := new(data_manage.BaseFromNationalStatisticsIndexListResp)
	resp.List = make([]*data_manage.BaseFromNationalStatisticsIndexItem, 0)
	repeatMap := make(map[string]int)
	indexMap := make(map[int]*data_manage.BaseFromNationalStatisticsIndexItem)
	for _, v := range indexes {
		item := data_manage.FormatBaseFromNationalStatisticsIndex2Item(v)
		// 因为存在第三维度-地区, 在分类大于0时, 根据indexName分组
		if classifyId > 0 && v.Reg != "" {
			// 取出地区信息作为详情接口的参数
			if !utils.InArrayByStr(resp.RegList, v.Reg) {
				resp.RegList = append(resp.RegList, v.Reg)
			}
			if indexMap[repeatMap[v.IndexName]] != nil {
				indexMap[repeatMap[v.IndexName]].ChildrenIndexIds = append(indexMap[repeatMap[v.IndexName]].ChildrenIndexIds, v.BaseFromNationalStatisticsIndexId)
			}
			if repeatMap[v.IndexName] > 0 {
				continue
			}
			repeatMap[v.IndexName] = v.BaseFromNationalStatisticsIndexId
			indexMap[v.BaseFromNationalStatisticsIndexId] = item
		}
		// 非分类下的查询时, 指标全名为地区+指标名
		if classifyId == 0 && v.Reg != "" {
			item.IndexName = fmt.Sprintf("%s%s", item.Reg, item.IndexName)
		}
		resp.List = append(resp.List, item)
	}

	br.Data = resp
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
}

// IndexDetail
// @Title 指标详情
// @Description 指标详情
// @Param   ClassifyId  query  int  false  "指标分类ID"
// @Param   IndexId  query  int  false  "指标ID"
// @Param   Reg  query  string  false  "地区"
// @Param   IsExport  query  bool  false  "是否导出"
// @Success 200 Ret=200 获取成功
// @router /base_from_national_statistics/index_detail [get]
func (this *BaseFromNationalStatisticsController) IndexDetail() {
	br := new(models.BaseResponse).Init()
	defer func() {
		if br.ErrMsg == "" {
			br.IsSendEmail = false
		}
		this.Data["json"] = br
		this.ServeJSON()
	}()
	sysUser := this.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录, SysUser Is Empty"
		br.Ret = 408
		return
	}
	classifyId, _ := this.GetInt("ClassifyId", 0)
	indexId, _ := this.GetInt("IndexId", 0)
	reg := this.GetString("Reg", "")
	isExport, _ := this.GetBool("IsExport", false)

	indexOB := new(data_manage.BaseFromNationalStatisticsIndex)
	indexCond := ``
	indexPars := make([]interface{}, 0)
	if reg != "" {
		indexCond += ` AND reg = ?`
		indexPars = append(indexPars, reg)
	}
	// 针对此种情况-数据库含地区维度, 选择指标时
	if indexId > 0 && reg != "" {
		index := new(data_manage.BaseFromNationalStatisticsIndex)
		if e := index.GetItemById(indexId); e != nil {
			br.Msg = "获取失败"
			br.ErrMsg = "获取指标失败, Err: " + e.Error()
			return
		}
		classifyId = index.BaseFromNationalStatisticsClassifyId
		indexCond += ` AND index_name = ?`
		indexPars = append(indexPars, index.IndexName)
	}
	if classifyId > 0 {
		indexCond += ` AND base_from_national_statistics_classify_id = ?`
		indexPars = append(indexPars, classifyId)
	}
	if indexId > 0 && reg == "" {
		indexCond += ` AND base_from_national_statistics_index_id = ?`
		indexPars = append(indexPars, indexId)
	}
	indexes, e := indexOB.GetItemsByCondition(indexCond, indexPars, []string{}, "base_from_national_statistics_index_id ASC", 0)
	if e != nil {
		br.Msg = "获取失败"
		br.ErrMsg = "获取指标列表失败, Err: " + e.Error()
		return
	}
	if len(indexes) == 0 {
		br.Ret = 200
		br.Success = true
		br.Msg = "获取成功"
		return
	}
	indexIds := make([]int, 0)
	dbCode := ``
	for _, i := range indexes {
		if dbCode == `` {
			dbCode = i.Dbcode
		}
		indexIds = append(indexIds, i.BaseFromNationalStatisticsIndexId)
	}
	dbNameMap := map[string]string{
		"hgyd":    "月度数据",
		"hgjd":    "季度数据",
		"hgnd":    "年度数据",
		"fsyd":    "分省月度数据",
		"fsjd":    "分省季度数据",
		"fsnd":    "分省年度数据",
		"csyd":    "主要城市月度价格",
		"csnd":    "主要城市年度数据",
		"gatyd":   "港澳台月度数据",
		"gatnd":   "港澳台年度数据",
		"gjyd":    "主要国家(地区)月度数据",
		"gjydsdj": "三大经济体月度数据",
		"gjydsc":  "国际市场月度商品价格",
		"gjnd":    "主要国家(地区)年度数据",
	}

	// 获取指标数据
	dataOB := new(data_manage.BaseFromNationalStatisticsData)
	dataCond := fmt.Sprintf(` AND base_from_national_statistics_index_id IN (%s)`, utils.GetOrmInReplace(len(indexIds)))
	dataPars := make([]interface{}, 0)
	dataPars = append(dataPars, indexIds)
	dataItems, e := dataOB.GetItemsByCondition(dataCond, dataPars, []string{"base_from_national_statistics_data_id", "base_from_national_statistics_index_id", "data_time", "value"}, "base_from_national_statistics_index_id ASC, data_time DESC")
	if e != nil {
		br.Msg = "获取失败"
		br.ErrMsg = "获取统计局指标数据失败, Err: " + e.Error()
		return
	}
	indexDataMap := make(map[int][]*data_manage.BaseFromNationalStatisticsIndexDetailData)
	for _, d := range dataItems {
		if indexDataMap[d.BaseFromNationalStatisticsIndexId] == nil {
			indexDataMap[d.BaseFromNationalStatisticsIndexId] = make([]*data_manage.BaseFromNationalStatisticsIndexDetailData, 0)
		}
		indexDataMap[d.BaseFromNationalStatisticsIndexId] = append(indexDataMap[d.BaseFromNationalStatisticsIndexId], &data_manage.BaseFromNationalStatisticsIndexDetailData{
			DataTime: d.DataTime.Format(utils.FormatDate),
			Value:    d.Value,
		})
	}
	resp := make([]*data_manage.BaseFromNationalStatisticsIndexDetailItem, 0)
	for _, i := range indexes {
		v := new(data_manage.BaseFromNationalStatisticsIndexDetailItem)
		item := data_manage.FormatBaseFromNationalStatisticsIndex2Item(i)
		item.IndexName = fmt.Sprintf("%s%s", item.Reg, item.IndexName)
		v.BaseFromNationalStatisticsIndexItem = *item
		v.DataList = indexDataMap[i.BaseFromNationalStatisticsIndexId]
		resp = append(resp, v)
	}

	// 导出
	if isExport {
		NationalStatisticsExport(this, br, resp, dbNameMap[dbCode])
		return
	}
	br.Data = resp
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
}

// NationalStatisticsExport 导出Excel
func NationalStatisticsExport(this *BaseFromNationalStatisticsController, br *models.BaseResponse, list []*data_manage.BaseFromNationalStatisticsIndexDetailItem, dbName string) {
	sysUser := this.SysUser
	if sysUser == nil {
		br.Msg = "请重新登录"
		return
	}

	dir, _ := os.Executable()
	exPath := filepath.Dir(dir)
	downloadPath := exPath + "/" + time.Now().Format(utils.FormatDateTimeUnSpace) + ".xlsx"
	xlsxFile := xlsx.NewFile()
	sheet, e := xlsxFile.AddSheet("数据报表")
	if e != nil {
		br.Msg = "新增Sheet失败"
		br.ErrMsg = "新增Sheet失败, Err: " + e.Error()
		return
	}

	nameRow := sheet.AddRow()
	codeRow := sheet.AddRow()
	frequencyRow := sheet.AddRow()
	unitRow := sheet.AddRow()
	updateTimeRow := sheet.AddRow()
	setRowIndex := 6 // 数据行开始
	for _, index := range list {
		nameRow.AddCell().SetString("指标名称")
		nameRow.AddCell().SetString(index.IndexName)
		nameRow.AddCell().SetString("")

		codeRow.AddCell().SetString("指标ID")
		codeRow.AddCell().SetString(index.IndexCode)
		codeRow.AddCell().SetString("")

		frequencyRow.AddCell().SetString("频率")
		frequencyRow.AddCell().SetString(index.Frequency)
		frequencyRow.AddCell().SetString("")

		unitRow.AddCell().SetString("单位")
		unitRow.AddCell().SetString(index.Unit)
		unitRow.AddCell().SetString("")

		updateTimeRow.AddCell().SetString("更新时间")
		updateTimeRow.AddCell().SetString(index.ModifyTime)
		updateTimeRow.AddCell().SetString("")

		for k, v := range index.DataList {
			row := sheet.Row(setRowIndex + k)
			row.AddCell().SetString(v.DataTime)
			row.AddCell().SetString(fmt.Sprint(v.Value))
			row.AddCell().SetString("")
		}
	}

	if e = xlsxFile.Save(downloadPath); e != nil {
		br.Msg = "导出失败"
		br.ErrMsg = "保存文件失败"
		return
	}
	fileName := fmt.Sprint("国家统计局", dbName, time.Now().Format("2006.01.02"), ".xlsx")
	this.Ctx.Output.Download(downloadPath, fileName)
	defer func() {
		_ = os.Remove(downloadPath)
	}()
	br.Ret = 200
	br.Success = true
	br.Msg = "success"
}