eta_data_init/services/init_calculate_index.go

package services

import (
	"bytes"
	"crypto/md5"
	"encoding/json"
	"errors"
	"fmt"
	"github.com/shopspring/decimal"
	"github.com/xuri/excelize/v2"
	"hongze/hz_eta_data/models"
	"hongze/hz_eta_data/utils"
	"io/ioutil"
	"net/http"
	"os"
	"path/filepath"
	"reflect"
	"sort"
	"strconv"
	"strings"
	"time"
)

//初始化计算指标数据

//classifyId:=685

const (
	HZ_DATA_API = "https://hzdataapi.hzinsights.com/hzdataapi/"
	ClassifyId  = 685
)

func InitCalculateIndex() {
	//读取基础指标
	var err error

	defer func() {
		if err != nil {
			fmt.Println("InitBaseIndexData Err:" + err.Error())
		}
	}()

	//读取excel
	path, err := filepath.Abs(os.Args[0])
	if err != nil {
		fmt.Println(err)
	}

	dir := filepath.Dir(path)
	fmt.Println("dir:" + dir)
	dataPath := dir + "/docs/逸诺指标库（基础）.xlsx"
	fmt.Println("dataPath:" + dataPath)
	f, err := excelize.OpenFile(dataPath)
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		// Close the spreadsheet.
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	rows, err := f.GetRows("edb_info")
	if err != nil {
		fmt.Println(err)
		return
	}

	hzMethod := "edb_index/detail"
	edbLibIndexMethod := "/edb_info/detail"
	var needAddIndexArr []string

	for rk, row := range rows {
		if rk <= 0 {
			continue
		}
		var indexCode string
		for ck, colCell := range row {
			switch ck {
			case 0:
				indexCode = colCell
			}
		}

		fmt.Println(rk, indexCode)
		if indexCode == "" {
			fmt.Println("指标编码不能为空")
			return
		}

		params := make(map[string]interface{})
		params["EdbCode"] = indexCode
		result, err := hzDataHttpPost(hzMethod, params)
		if err != nil {
			fmt.Println("hzDataHttpPost Err:" + err.Error())
			return
		}
		utils.FileLog.Info("hzDataHttpPost:" + string(result))
		resp := new(models.EdbInfoResp)
		err = json.Unmarshal([]byte(result), &resp)
		if err != nil {
			fmt.Println("json.Unmarshal Err:" + err.Error())
			return
		}
		if resp.Ret != 200 && !strings.Contains(resp.ErrMsg, "QuerySeter") {
			fmt.Println("resp Err:" + resp.ErrMsg)
			return
		}
		//指标信息不存在
		if resp.Data.EdbInfoId <= 0 {
			fmt.Println("指标:" + indexCode + ";不存在")
			f.SetCellStr("edb_info", "H"+strconv.Itoa(rk+1), "不存在")
			continue
		}
		//判断是否存在计算指标
		if len(resp.Data.CalculateList) <= 0 {
			f.SetCellStr("edb_info", "H"+strconv.Itoa(rk+1), "不存在")
			continue
		}
		//存在计算指标
		for _, cv := range resp.Data.CalculateList {
			//计算指标所依赖的基础指标
			var total int
			for _, bv := range cv.BaseEdbInfoCode {
				//判断基础指标是否存在
				indexMap := make(map[string]interface{})
				indexMap["EdbCode"] = bv.FromEdbCode
				result, err := PostEdbLib(indexMap, edbLibIndexMethod)
				if err != nil {
					utils.FileLog.Info("获取计算指标所依赖的基础指标失败:" + err.Error() + " result:" + string(result))
					return
				}

				indexResp := new(models.EdbInfoResp)
				err = json.Unmarshal(result, &indexResp)
				if err != nil {
					utils.FileLog.Info("json.Unmarshal 获取计算指标所依赖的基础指标失败:" + err.Error())
					return
				}
				if indexResp.Ret != 200 {
					fmt.Println("获取计算指标所依赖的基础指标失败 Err:" + indexResp.Msg)
					return
				}
				if resp.Data.EdbInfoId > 0 {
					total++
				}
			}

			if total == len(cv.BaseEdbInfoCode) {
				needAddIndexArr = append(needAddIndexArr, cv.EdbCode)
			}
		}
	}
	f.Save()

	hzIndexMethod := "edb_index/item"
	hzCalculateIndexMethod := "edb_info_calculate/item"

	edbLibIndexMethod = "edb_info/copy/from/hz"
	edbLibCalculateIndexMethod := "edb_info/copy/from/hz"

	for k, v := range needAddIndexArr { //需要同步的计算指标
		fmt.Println(k, v)

		params := make(map[string]interface{})
		params["EdbCode"] = v
		hzResult, err := hzDataHttpPost(hzIndexMethod, params)
		if err != nil {
			fmt.Println("hzDataHttpPost Err:" + err.Error())
			return
		}
		hzResp := new(models.EdbInfoItemResp)
		err = json.Unmarshal([]byte(hzResult), &hzResp)
		if err != nil {
			fmt.Println("json.Unmarshal Err:" + err.Error())
			return
		}
		if hzResp.Ret != 200 {
			fmt.Println("resp Err:" + hzResp.ErrMsg)
			return
		}
		edbInfo := hzResp.Data
		//新增指标信息
		indexMap := make(map[string]interface{})
		indexMap["EdbInfoType"] = edbInfo.EdbInfoType
		indexMap["SourceName"] = edbInfo.SourceName
		indexMap["Source"] = edbInfo.Source
		indexMap["EdbCode"] = edbInfo.EdbCode
		indexMap["EdbName"] = edbInfo.EdbName
		indexMap["EdbNameEn"] = edbInfo.EdbNameEn
		indexMap["EdbNameSource"] = edbInfo.EdbNameSource
		indexMap["Frequency"] = edbInfo.Frequency
		indexMap["Unit"] = edbInfo.Unit
		indexMap["UnitEn"] = edbInfo.UnitEn
		indexMap["StartDate"] = edbInfo.StartDate
		indexMap["EndDate"] = edbInfo.EndDate
		indexMap["ClassifyId"] = ClassifyId
		indexMap["SysUserId"] = 0
		indexMap["SysUserRealName"] = ""
		indexMap["UniqueCode"] = edbInfo.UniqueCode
		indexMap["CreateTime"] = time.Now()
		indexMap["ModifyTime"] = time.Now()
		indexMap["MinValue"] = edbInfo.MinValue
		indexMap["MaxValue"] = edbInfo.MaxValue
		indexMap["CalculateFormula"] = edbInfo.CalculateFormula
		indexMap["EdbType"] = edbInfo.EdbType
		indexMap["IsUpdate"] = 1
		indexMap["Sort"] = edbInfo.Sort
		indexMap["LatestDate"] = edbInfo.LatestDate
		indexMap["LatestValue"] = edbInfo.LatestValue
		indexMap["MoveType"] = edbInfo.MoveType
		indexMap["MoveFrequency"] = edbInfo.MoveFrequency
		indexMap["NoUpdate"] = edbInfo.NoUpdate
		indexMap["ServerUrl"] = edbInfo.ServerUrl
		indexMap["ChartImage"] = edbInfo.ChartImage
		indexMap["Calendar"] = edbInfo.Calendar
		indexMap["DataDateType"] = edbInfo.DataDateType

		edbLibResult, err := PostEdbLib(indexMap, edbLibIndexMethod)
		if err != nil {
			utils.FileLog.Info("新增计算指标失败:" + err.Error() + " result:" + string(edbLibResult))
			return
		}
		baseResp := new(models.BaseResponse)
		err = json.Unmarshal([]byte(edbLibResult), &baseResp)
		if err != nil {
			fmt.Println("edbLibResult json.Unmarshal Err:" + err.Error())
			return
		}
		if baseResp.Ret != 200 {
			fmt.Println("PostEdbLib resp Err:" + hzResp.ErrMsg)
			return
		}

		//新增计算指标映射关系
		calculateParams := make(map[string]interface{})
		calculateParams["EdbCode"] = v
		hzResult, err = hzDataHttpPost(hzCalculateIndexMethod, calculateParams)
		if err != nil {
			fmt.Println("hzDataHttpPost calculate Err:" + err.Error())
			return
		}
		hzCalculateResp := new(models.EdbInfoCalculateMappingResp)
		err = json.Unmarshal([]byte(hzResult), &hzCalculateResp)
		if err != nil {
			fmt.Println("calculate json.Unmarshal Err:" + err.Error())
			return
		}
		if hzResp.Ret != 200 {
			fmt.Println("calculate resp Err:" + hzResp.ErrMsg)
			return
		}
		for _, cv := range hzCalculateResp.Data {
			//新增指标信息
			indexCalculateMap := make(map[string]interface{})
			indexCalculateMap["EdbInfoId"] = cv.EdbInfoId
			indexCalculateMap["Source"] = cv.Source
			indexCalculateMap["SourceName"] = cv.SourceName
			indexCalculateMap["EdbCode"] = cv.EdbCode
			indexCalculateMap["FromEdbInfoId"] = cv.FromEdbInfoId
			indexCalculateMap["FromEdbCode"] = cv.FromEdbCode
			indexCalculateMap["FromEdbName"] = cv.FromEdbName
			indexCalculateMap["FromSource"] = cv.FromSource
			indexCalculateMap["FromSourceName"] = cv.FromSourceName
			indexCalculateMap["MoveValue"] = cv.MoveValue
			indexCalculateMap["Sort"] = cv.Sort
			indexCalculateMap["CreateTime"] = time.Now()
			indexCalculateMap["ModifyTime"] = time.Now()
			indexCalculateMap["FromTag"] = cv.EdbInfoId

			edbLibResult, err := PostEdbLib(indexCalculateMap, edbLibCalculateIndexMethod)
			if err != nil {
				utils.FileLog.Info("新增计算指标映射关系失败:" + err.Error() + " result:" + string(edbLibResult))
				return
			}
			baseResp := new(models.BaseResponse)
			err = json.Unmarshal([]byte(edbLibResult), &baseResp)
			if err != nil {
				fmt.Println("edbLibResult mapping json.Unmarshal Err:" + err.Error())
				return
			}
			if baseResp.Ret != 200 {
				fmt.Println("PostEdbLib mapping resp Err:" + hzResp.ErrMsg)
				return
			}
		}
		time.Sleep(1 * time.Second)
	}
}

func hzDataHttpPost(method string, postDataMap map[string]interface{}) (result string, err error) {
	bytesData, err := handlePostData(postDataMap)
	if err != nil {
		return
	}

	client := &http.Client{}
	req, err := http.NewRequest("POST", HZ_DATA_API+method, bytes.NewReader(bytesData))
	if err != nil {
		return "", err
	}
	req.Header.Set("content-type", "application/json")
	resp, err := client.Do(req)
	if err != nil {
		return "", err
	}
	body, err := ioutil.ReadAll(resp.Body)
	if err != nil {
		return "", err
	}
	result = string(body)
	return
}

// handlePostData 处理post请求数据
func handlePostData(postDataMap map[string]interface{}) (bytesData []byte, err error) {
	postDataMap["nonce_str"] = utils.GetRandString(16) // 随机字符串
	postDataMap["timestamp"] = time.Now().Unix()       //当前格林威治时间，int64类型
	postDataMap["appid"] = utils.APPID                 //当前格林威治时间，int64类型
	// 待签名数据
	signData := convertParamInterface(postDataMap)
	sign, err := getSignData(signData)
	if err != nil {
		return
	}
	postDataMap["sign"] = sign //签名

	bytesData, err = json.Marshal(postDataMap)

	return
}

// 将请求传入的数据格式转换成签名需要的格式（目前只能处理简单的类型，数组、对象暂不支持）
func convertParamInterface(params map[string]interface{}) (signData map[string]string) {
	signData = make(map[string]string)
	for key := range params {
		val := ``
		//fmt.Println("key", key, ";val:", params[key], ";type:", reflect.TypeOf(params[key]))
		//signData[key] = params[key][0]
		tmpVal := params[key]
		switch reflect.TypeOf(tmpVal).Kind() {
		case reflect.String:
			val = fmt.Sprint(tmpVal)
		case reflect.Int, reflect.Int16, reflect.Int64, reflect.Int32, reflect.Int8:
			val = fmt.Sprint(tmpVal)
		case reflect.Uint, reflect.Uint32, reflect.Uint16, reflect.Uint8, reflect.Uint64:
			val = fmt.Sprint(tmpVal)
		case reflect.Bool:
			val = fmt.Sprint(tmpVal)
		case reflect.Float64:
			decimalNum := decimal.NewFromFloat(tmpVal.(float64))
			val = decimalNum.String()
			//val = strconv.FormatFloat(tmpVal.(float64), 'E', -1, 64) //float64
		case reflect.Float32:
			decimalNum := decimal.NewFromFloat32(tmpVal.(float32))
			val = decimalNum.String()
		}
		signData[key] = val
	}
	return signData
}

// getSignData 获取参数签名
func getSignData(postData map[string]string) (sign string, err error) {
	appid := utils.APPID
	if appid == "" {
		err = errors.New("参数异常，缺少appid")
		return
	}

	secret := utils.SECRET
	if secret == "" {
		err = errors.New("参数异常，缺少secret")
		return
	}

	if postData["nonce_str"] == "" {
		err = errors.New("参数异常，缺少随机字符串")
		return
	}
	if postData["timestamp"] == "" {
		err = errors.New("参数异常，缺少时间戳")
		return
	}

	//先取出除sign外的所有的提交的参数key
	var keys []string
	for k := range postData {
		if k != "sign" {
			keys = append(keys, k)
		}
	}

	//1,根据参数名称的ASCII码表的顺序排序
	sort.Strings(keys)

	//2 根据排序后的参数名称，取出对应的值，并拼接字符串
	var signStr string
	for _, v := range keys {
		signStr += v + "=" + postData[v] + "&"
	}
	//3,全转小写(md5(拼装的字符串后+分配给你的app_secret))
	//sign := strings.ToLower(fmt.Sprintf("%x", md5.Sum([]byte(strings.Trim(signStr, "&")+key))))

	//md5.Sum([]byte(signStr+"key="+key))  这是md5加密出来后的每个字符的ascall码，需要再转换成对应的字符
	//3,全转大写(md5(拼装的字符串后+分配给你的app_secret))
	sign = strings.ToUpper(fmt.Sprintf("%x", md5.Sum([]byte(signStr+"secret="+secret))))

	return
}