package chart import ( "crypto/md5" "encoding/hex" "encoding/json" "errors" "fmt" "github.com/shopspring/decimal" "github.com/yidane/formula" "hongze/hongze_yb/global" edbDataModel "hongze/hongze_yb/models/tables/edb_data" edbInfoModel "hongze/hongze_yb/models/tables/edb_info" "hongze/hongze_yb/utils" "io/ioutil" "net/http" "net/url" "reflect" "sort" "strconv" "strings" "time" ) type EdbDataFromThs struct { DataVol int64 `json:"dataVol"` Errmsg string `json:"errmsg"` Errorcode int64 `json:"errorcode"` Perf interface{} `json:"perf"` Tables []struct { ID []string `json:"id"` Time []string `json:"time"` Value []float64 `json:"value"` } `json:"tables"` } func RefreshEdbDataByThs(edbInfoId int, edbCode, startDate, endDate string) (err error) { HzServerDataUrl := "" if global.CONFIG.Serve.RunMode == "release" { HzServerDataUrl = "http://172.19.173.231:7000/" } else { HzServerDataUrl = "http://139.196.136.213:7000/" } thsUrl := HzServerDataUrl + `edbInfo/ths?EdbCode=%s&StartDate=%s&EndDate=%s` thsUrl = fmt.Sprintf(thsUrl, edbCode, startDate, endDate) //utils.FileLog.Info("thsUrl:%s", thsUrl) reqRes, err := http.Get(thsUrl) if err != nil { return } defer reqRes.Body.Close() body, err := ioutil.ReadAll(reqRes.Body) if err != nil { return } fmt.Println("GetEdbDataByThs body:") fmt.Println(string(body)) item := new(EdbDataFromThs) err = json.Unmarshal(body, &item) if err != nil { return } if item.Errorcode != 0 { err = errors.New(string(body)) return } if len(item.Tables) > 0 { table := item.Tables[0] dataLen := len(table.Time) addSql := ` INSERT INTO edb_data_ths(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` nowStr := time.Now().Format(utils.FormatDateTime) var isAdd bool for i := 0; i < dataLen; i++ { eDate := table.Time[i] sValue := table.Value[i] count, err := edbDataModel.GetEdbDataThsByCodeAndDate(edbCode, eDate) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += "(" addSql += strconv.Itoa(edbInfoId) + "," + "'" + edbCode + "'" + "," + "'" + eDate + "'" + "," + utils.SubFloatToString(sValue, 30) + "," + "'" + nowStr + "'" + "," + "'" + nowStr + "'" addSql += "," + "'" + timeStr + "'" addSql += ")," isAdd = true } else { err = edbDataModel.ModifyEdbDataThs(int64(edbInfoId), eDate, sValue) if err != nil { return err } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = edbDataModel.AddEdbDataThsBySql(addSql) if err != nil { fmt.Println("AddEdbDataThs Err", err.Error()) return } } } return } type EdbDataFromWind struct { Close map[string]float64 `json:"CLOSE"` Dt map[string]int64 `json:"DT"` ErrMsg string } func RefreshEdbDataByWind(edbInfoId int, edbCode, startDate, endDate string) (err error) { defer func() { if err != nil { fmt.Println("GetEdbDataByWind Err:" + err.Error()) } }() fmt.Println("startDate,endDate:",startDate,endDate) thsUrl := utils.Hz_Data_Url + `edbInfo/wind?EdbCode=%s&StartDate=%s&EndDate=%s` thsUrl = fmt.Sprintf(thsUrl, edbCode, startDate, endDate) //utils.FileLog.Info("thsUrl:%s", thsUrl) reqRes, err := http.Get(thsUrl) if err != nil { return } defer reqRes.Body.Close() body, err := ioutil.ReadAll(reqRes.Body) if err != nil { return } fmt.Println("GetEdbDataByThs body:") fmt.Println(string(body)) //utils.FileLog.Info("wind result:"+string(body)) item := new(EdbDataFromWind) err = json.Unmarshal(body, &item) if err != nil { return } addSql := ` INSERT INTO edb_data_wind(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` nowStr := time.Now().Format(utils.FormatDateTime) var isAdd bool for k, v := range item.Dt { timeStr := fmt.Sprintf("%d", v) v = v / 1000 t := time.Unix(v, 0) dateTime := t.Format(utils.FormatDate) val := item.Close[k] count, err := edbDataModel.GetEdbDataWindByCodeAndDate(edbCode, dateTime) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { isAdd = true fmt.Println(dateTime) addSql += "(" addSql += strconv.Itoa(edbInfoId) + "," + "'" + edbCode + "'" + "," + "'" + dateTime + "'" + "," + utils.SubFloatToString(val, 30) + "," + "'" + nowStr + "'" + "," + "'" + nowStr + "'" addSql += "," + "'" + timeStr + "'" addSql += ")," } else { err = edbDataModel.ModifyEdbDataWind(int64(edbInfoId), dateTime, val) if err != nil { return err } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = edbDataModel.AddEdbDataWindBySql(addSql) if err != nil { fmt.Println("AddEdbDataWind Err", err.Error()) return } } return } type EdbDataFromPb struct { Date map[string]int64 `json:"date"` Ticker map[string]string `json:"ticker"` Field map[string]string `json:"field"` Value map[string]float64 `json:"value"` } func RefreshEdbDataByPb(edbInfoId int, edbCode, startDate, endDate string) (err error) { refreshEdbCode := edbCode edbCode = url.QueryEscape(edbCode) bpUrl := utils.Hz_Data_PB_Url + `edbInfo/pb?EdbCode=%s&StartDate=%s&EndDate=%s` bpUrl = fmt.Sprintf(bpUrl, edbCode, startDate, endDate) //utils.FileLog.Info("bpUrl+" + bpUrl) reqRes, err := http.Get(bpUrl) if err != nil { return } defer reqRes.Body.Close() body, err := ioutil.ReadAll(reqRes.Body) if err != nil { return } fmt.Println("RefreshEdbDataByPb body:") fmt.Println(string(body)) //utils.FileLog.Info("RefreshEdbDataByPb:" + string(body)) item := new(EdbDataFromPb) err = json.Unmarshal(body, &item) if err != nil { return } if len(item.Date) > 0 { dateMap := item.Date pbList := make([]*edbDataModel.EdbDataPb, 0) for k, v := range dateMap { timestamp := v v = v / 1000 t := time.Unix(v, 0) dateTime := t.Format(utils.FormatDate) val := item.Value[k] field := item.Field[k] ticker := item.Ticker[k] if field == "PX_LAST" { //判断数据是否已经存在 count, err := edbDataModel.GetEdbDataPbByCodeAndDate(refreshEdbCode, dateTime) if err != nil && err != utils.ErrNoRow { return err } fmt.Println("field:", field) if count <= 0 { item := new(edbDataModel.EdbDataPb) item.EdbCode, _ = url.QueryUnescape(edbCode) item.EdbInfoId = edbInfoId item.CreateTime = time.Now() item.ModifyTime = time.Now() item.DataTime = dateTime item.Value = val item.Status = 1 item.Field = field item.Ticker = ticker item.DataTimestamp = timestamp pbList = append(pbList, item) } else { err = edbDataModel.ModifyEdbDataPb(edbInfoId, dateTime, val) if err != nil { return err } } } } if len(pbList) > 0 { err = edbDataModel.AddEdbDataPb(pbList) if err != nil { fmt.Println("AddEdbDataPb Err", err.Error()) return } } } return } //刷新手工指标数据 func RefreshAllEdbDataByManual(edbInfoId, source int, edbCode string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} if edbCode != "" { condition += " AND TRADE_CODE=? " pars = append(pars, edbCode) } manualDataList, err := edbDataModel.GetEdbdataManualByTradeCode(condition, pars) //获取指标所有数据 existDataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = global.MYSQL["data"].Raw(sql, edbInfoId).Scan(&existDataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList) if err != nil { return err } existDataMap := make(map[string]*edbDataModel.EdbDataBase) for _, v := range existDataList { existDataMap[v.DataTime] = v } addSql := ` INSERT INTO edb_data_manual(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool manualMap := make(map[string]*edbDataModel.ManualEdbdata) for _, v := range manualDataList { item := v if findItem, ok := existDataMap[v.Dt]; !ok { eDate := item.Dt sValue := item.Close dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue) isAdd = true } else { if findItem.Value != item.Close { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, item.Close, edbInfoId, item.Dt).Error //err = global.MYSQL["data"].Exec(sql, item.Close, edbInfoId, item.Dt).Error //_, err = o.Raw(sql, item.Close, edbInfoId, item.Dt).Exec() if err != nil { return err } } } manualMap[v.Dt] = v } for _, v := range existDataList { if _, ok := manualMap[v.DataTime]; !ok { go func() { sql := ` DELETE FROM %s WHERE edb_data_id=? ` tableName := edbDataModel.GetEdbDataTableName(utils.DATA_SOURCE_MANUAL) sql = fmt.Sprintf(sql, tableName) err = tx.Exec(sql, v.EdbInfoId).Error //err = global.MYSQL["data"].Exec(sql, edbDataId).Error }() //go DeleteEdbDataByIdAndSource(v.EdbDataId, utils.DATA_SOURCE_MANUAL) } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { fmt.Println("RefreshAllEdbDataByManual add Err", err.Error()) return } } return } func DeleteEdbDataByIdAndSource(edbDataId, source int) (err error) { sql := ` DELETE FROM %s WHERE edb_data_id=? ` tableName := edbDataModel.GetEdbDataTableName(source) sql = fmt.Sprintf(sql, tableName) err = global.MYSQL["data"].Exec(sql, edbDataId).Error return } func GetAddSql(edbInfoId, edbCode, dataTime, timestampStr string, value string) (addSql string) { nowStr := time.Now().Format(utils.FormatDateTime) addSql += "(" addSql += edbInfoId + "," + "'" + edbCode + "'" + "," + "'" + dataTime + "'" + "," + value + "," + "'" + nowStr + "'" + "," + "'" + nowStr + "'" addSql += "," + "'" + timestampStr + "'" addSql += ")," return } //刷新隆众指标数据 func RefreshEdbDataByLz(edbInfoId int, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} if edbCode != "" { condition += " AND b.lz_code=? " pars = append(pars, edbCode) } if startDate != "" { condition += " AND a.data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND a.data_time<=? " pars = append(pars, endDate) } lzDataList, err := edbDataModel.GetLzSurveyDataByTradeCode(condition, pars) addSql := ` INSERT INTO edb_data_lz(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool for _, v := range lzDataList { item := v count, err := edbDataModel.GetEdbDataLzByCodeAndDate(edbCode, v.DataTime) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { nowStr := time.Now().Format(utils.FormatDateTime) eDate := item.DataTime sValue := item.InputValue dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += "(" addSql += edbInfoIdStr + "," + "'" + edbCode + "'" + "," + "'" + eDate + "'" + "," + sValue + "," + "'" + nowStr + "'" + "," + "'" + nowStr + "'" + "," + "'" + timeStr + "'" addSql += ")," isAdd = true } else { //edbInfoId int64, dataTime, value string //o := orm.NewOrm() //o.Using("data") sql := ` UPDATE edb_data_lz SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` err = tx.Exec(sql, v.InputValue, int64(edbInfoId), v.DataTime).Error //_, err = o.Raw(sql, value, edbInfoId, dataTime).Exec() //err = ModifyEdbDataLz(int64(edbInfoId), v.DataTime, v.InputValue) if err != nil { return err } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } //全部刷新有色数据 func RefreshAllEdbDataByYs(edbInfoId, source int, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) //获取数据 err = SyncSmmIndexDataBase(edbCode, startDate, endDate) if err != nil { err = errors.New("SyncSmmIndexDataBase Err:" + err.Error()) return err } //获取已存在指标所有数据 existDataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = global.MYSQL["data"].Raw(sql, edbInfoId).Scan(&existDataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList) if err != nil { return err } existDataMap := make(map[string]string) for _, v := range existDataList { existDataMap[v.DataTime] = v.Value } smmDateList := make([]*edbDataModel.BaseFromSmmDataSimple, 0) smmSql := ` SELECT * FROM base_from_smm_data WHERE index_code=? AND data_time>=? ` err = global.MYSQL["data"].Raw(smmSql, edbCode, startDate).Scan(&existDataList).Error //_, err = o.Raw(smmSql, edbCode, startDate).QueryRows(&smmDateList) if err != nil { return err } addSql := ` INSERT INTO edb_data_ys(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool for _, sv := range smmDateList { if existVal, ok := existDataMap[sv.DataTime]; !ok { dataTime, err := time.Parse(utils.FormatDate, sv.DataTime) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, sv.DataTime, timeStr, sv.Value) isAdd = true } else { if existVal != sv.Value { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, sv.Value, edbInfoId, sv.DataTime).Error //_, err = o.Raw(sql, sv.Value, edbInfoId, sv.DataTime).Exec() if err != nil { return err } } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } const ( dataUrl = "https://dataapi.smm.cn/GetData/" //data url (中文) //dataUrl = "https://dataapi.smm.cn/GetDataEn/" //data url (english edition) authUrl = "https://platform.smm.cn/usercenter/auth" // auth url (for all) ) type TokenResp struct { Code int `json:"Code"` Msg string `json:"Msg"` Data TokenData `json:"Data"` } type TokenData struct { Token string `json:"Token"` } //获取token func getToken(userName string, password string) (string, error) { encryptAuth := md5.New() encryptAuth.Write([]byte(password)) //encrypt password with md5 newPassword := hex.EncodeToString(encryptAuth.Sum(nil)) resp, err := http.PostForm(authUrl, url.Values{"user_name": {userName}, "password": {newPassword}}) if err != nil { return "", err } defer resp.Body.Close() body, err := ioutil.ReadAll(resp.Body) if err != nil { fmt.Println("reponse error", err) return "", err } var bodyJsonContent TokenResp if err = json.Unmarshal([]byte(body), &bodyJsonContent); err != nil { fmt.Println(err, "unmarsal failure") return "", err } var token string if bodyJsonContent.Code == 0 { token = bodyJsonContent.Data.Token } //print(token) return token, nil } func SyncSmmIndexDataBase(edbCode, startDate, endDate string) (err error) { //utils.FileLog.Info("start:" + time.Now().Format(utils.FormatDateTime)) var smmCode string if strings.Contains(edbCode, "#") { smmCode = strings.Split(edbCode, "#")[0] } else { smmCode = edbCode } token, err := getToken("pqian@hzinsights.com", "hz123456") if err != nil { fmt.Println(err) return } baseSmmItem, err := edbDataModel.GetBaseFromSmmBySmmCode(smmCode) if err != nil { fmt.Println(err) return } if baseSmmItem == nil { err = errors.New("GetBaseFromSmmBySmmCode Err:" + err.Error()) return } smmIndexAll, err := edbDataModel.GetBaseFromSmmIndexBySmmCode(smmCode) if err != nil { fmt.Println("GetBaseFromSmmIndex Err:" + err.Error()) return } existIndexMap := make(map[string]*edbDataModel.BaseFromSmmIndex) for _, item := range smmIndexAll { existIndexMap[item.IndexCode] = item } ysItem, err := getApiData(token, edbCode, startDate, endDate) if err != nil { fmt.Println(err) return } if ysItem != nil && ysItem.Code == 200 { frequency := ysItem.Data.Frequency indexMap := make(map[string]int) smmIndexIdMap := make(map[int]int) indexCodeMap := make(map[int]string) indexKey := 0 var isDateIndex int for fk, fv := range ysItem.Data.Field { if fv.IsDate == "1" { isDateIndex = fk } else { if !strings.Contains(fv.Name, "产品名称") && !strings.Contains(fv.Name, "单位") && !strings.Contains(fv.Name, "时间") && !strings.Contains(fv.Name, "备注") { indexMap[fv.DBColName] = fk indexKey += 1 indexCode := smmCode + "#" + strconv.Itoa(indexKey) if findItem, ok := existIndexMap[indexCode]; !ok { ssmIndex := new(edbDataModel.BaseFromSmmIndex) ssmIndex.Interface = smmCode ssmIndex.Name = baseSmmItem.Name ssmIndex.IndexCode = indexCode ssmIndex.IndexName = baseSmmItem.Name + "_" + fv.Name ssmIndex.Type1 = baseSmmItem.Type1 ssmIndex.Type2 = baseSmmItem.Type2 ssmIndex.Type3 = baseSmmItem.Type3 ssmIndex.Frequency = frequency ssmIndex.Unit = fv.Unit ssmIndex.ApiStartTime = baseSmmItem.ApiStartTime ssmIndex.ApiUpdateTime = baseSmmItem.ApiUpdateTime ssmIndex.StartTime = baseSmmItem.StartTime ssmIndex.FinishTime = baseSmmItem.FinishTime ssmIndex.CreateTime = time.Now() ssmIndex.ModifyTime = time.Now() lastIndexId, err := edbDataModel.AddBaseFromSmmIndex(ssmIndex) if err != nil { err = errors.New("AddBaseFromSmmIndex Err:" + err.Error()) return err } smmIndexIdMap[fk] = int(lastIndexId) indexCodeMap[fk] = indexCode } else { smmIndexIdMap[fk] = findItem.BaseFromSmmIndexId indexCodeMap[fk] = findItem.IndexCode } } } } existDataMap := make(map[string]*edbDataModel.BaseFromSmmData) for _, mv := range indexCodeMap { indexCode := mv dataAllList, err := edbDataModel.GetBaseFromSmmDataAllByIndexCode(indexCode) if err != nil { err = errors.New("GetBaseFromSmmData Err:" + err.Error()) return err } for _, item := range dataAllList { key := item.IndexCode + item.DataTime existDataMap[key] = item } } addExistDataMap := make(map[string]string) for _, dv := range ysItem.Data.Content { var dataTime string dataTime = dv[isDateIndex] if strings.Contains(dataTime, "Q1") { dataTime = strings.Replace(dataTime, "Q1", "-01", -1) dataTime += "-31" } if strings.Contains(dataTime, "Q2") { dataTime = strings.Replace(dataTime, "Q2", "-06", -1) dataTime += "-30" } if strings.Contains(dataTime, "Q3") { dataTime = strings.Replace(dataTime, "Q3", "-09", -1) dataTime += "-30" } if strings.Contains(dataTime, "Q4") { dataTime = strings.Replace(dataTime, "Q4", "-12", -1) dataTime += "-31" } if strings.Contains(dataTime, "H1") { dataTime = strings.Replace(dataTime, "H1", "-06", -1) dataTime += "-30" } if strings.Contains(dataTime, "H2") { dataTime = strings.Replace(dataTime, "H2", "-12", -1) dataTime += "-31" } if frequency == "月" { monthDate, err := time.Parse("2006-01", dataTime) if err != nil { fmt.Println("time.Parse:" + err.Error()) } lastTime := monthDate.AddDate(0, 1, -1) lastYear, lastMonth, lastDay := lastTime.Date() var lastDate string if int(lastMonth) < 10 { lastDate = strconv.Itoa(lastYear) + "-" + "0" + strconv.Itoa(int(lastMonth)) + "-" + strconv.Itoa(lastDay) } else { lastDate = strconv.Itoa(lastYear) + "-" + strconv.Itoa(int(lastMonth)) + "-" + strconv.Itoa(lastDay) } dataTime = lastDate } else if frequency == "年" { dataTime = dataTime + "-12-31" } saveDataTime, err := time.Parse(utils.FormatDate, dataTime) if err != nil { err = errors.New("time.Parse Err:" + err.Error()) return err } timestamp := saveDataTime.UnixNano() / 1e6 //循环指标 for _, v := range indexMap { indexCode := indexCodeMap[v] smmIndexId := smmIndexIdMap[v] dataVal := dv[v] if indexCode != "" { key := indexCode + dataTime val := strings.Replace(dataVal, ",", "", -1) if findData, dataOk := existDataMap[key]; !dataOk { if _, addOK := addExistDataMap[key]; !addOK { if val != "" && val != "-" { dataItem := new(edbDataModel.BaseFromSmmData) dataItem.BaseFromSmmIndexId = smmIndexId dataItem.IndexCode = indexCode dataItem.DataTime = dataTime dataItem.Value = val dataItem.CreateTime = time.Now() dataItem.ModifyTime = time.Now() dataItem.DataTimestamp = timestamp _, err = edbDataModel.AddBaseFromSmmData(dataItem) if err != nil && !strings.Contains(err.Error(), "idx_index_code_date") { fmt.Println("AddBaseFromSmmData Err:" + err.Error()) err = errors.New("AddBaseFromSmmData Err:" + err.Error()) return err } } } } else { if findData != nil && findData.Value != val { //修改 if _, addOK := addExistDataMap[key]; !addOK { if val != "" && val != "-" { err = edbDataModel.ModifyBaseFromSmmData(findData.SmmDataId, val) if err != nil { err = errors.New("ModifyBaseFromSmmData Err:" + err.Error()) return err } } } } } addExistDataMap[key] = key } } } //修改数据开始，结束日期 { indexList, err := edbDataModel.GetBaseFromSmmIndexBySmmCode(smmCode) if err != nil { fmt.Println("GetBaseFromSmmIndexBySmmCode Err:" + err.Error()) } for _, sv := range indexList { minDate, maxDate, err := edbDataModel.GetBaseFromSmmMaxOrMinDate(sv.IndexCode) if err != nil { fmt.Println("GetEdbDataSmmMaxOrMinDate Err:" + err.Error()) } else { err = edbDataModel.ModifyBaseFromSmmMinDateAndMaxDate(sv.BaseFromSmmIndexId, minDate, maxDate) if err != nil { fmt.Println("ModifyBaseFromSmmMinDateAndMaxDate Err:" + err.Error()) } } } } } return } /* * request data * sdatetime,edatetime ==>format:yyyy-mm-dd, * apiName ==> data.metal.com(for english)/data.smm.cn (for chinese) */ func getApiData(token string, apiName string, sdatetime string, edatetime string) (item *edbDataModel.YsResult, err error) { reqUrl := dataUrl + apiName resp, err := http.PostForm(reqUrl, url.Values{"token": {token}, "sdatetime": {sdatetime}, "edatetime": {edatetime}}) if err != nil { return nil, err } defer resp.Body.Close() body, err := ioutil.ReadAll(resp.Body) if err != nil { fmt.Println("response error") return nil, err } //utils.FileLog.Info("ys result:" + string(body)) dataJsonContent := new(edbDataModel.YsResult) if err = json.Unmarshal([]byte(body), &dataJsonContent); err != nil { fmt.Println(err, "data unmarshal failure") return nil, err } if dataJsonContent.Code == 200 && len(dataJsonContent.Data.Content) > 0 { return dataJsonContent, nil } else { err = errors.New("code:" + strconv.Itoa(int(dataJsonContent.Code)) + "msg:" + dataJsonContent.Msg) } return nil, nil } func RefreshEdbDataByGl(edbInfoId int, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} if edbCode != "" { condition += " AND INDEX_CODE=? " pars = append(pars, edbCode) } if startDate != "" { condition += " AND DATA_DATE>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND DATA_DATE<=? " pars = append(pars, endDate) } glDataList, err := edbDataModel.GetGlDataByTradeCode(condition, pars) addSql := ` INSERT INTO edb_data_gl(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) for _, v := range glDataList { item := v if _, ok := existMap[v.DataTime]; !ok { count, err := edbDataModel.GetEdbDataGlByCodeAndDate(edbCode, v.DataTime) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { eDate := item.DataTime sValue := item.InputValue if sValue != "" { dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue) isAdd = true } } else { updateSql := ` UPDATE edb_data_gl SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` //err = global.MYSQL["data"].Exec(sql, value, edbInfoId, dataTime).Error err = tx.Exec(updateSql, v.InputValue, edbInfoId, v.DataTime).Error //err = edbDataModel.ModifyEdbDataGl(int64(edbInfoId), v.DataTime, v.InputValue) if err != nil { return err } } } existMap[v.DataTime] = v.InputValue } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } // RefreshEdbDataByZz 刷新路透社数据 func RefreshAllEdbDataByLt(edbInfoId, source int, edbCode, startDate, endDate string) (err error) { // 获取路透社数据 ltDataList, err := QueryEdbDataByLt(edbCode, startDate, endDate) if err != nil { return } tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() //获取指标所有数据 dataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = global.MYSQL["data"].Raw(sql, dataTableName).Scan(&dataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&dataList) if err != nil { return err } dataMap := make(map[string]string) for _, v := range dataList { dataMap[v.DataTime] = v.Value } edbInfoIdStr := strconv.Itoa(edbInfoId) addSql := ` INSERT INTO edb_data_lt(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool for timestampInt, edbValue := range ltDataList { dataTime := time.Unix(timestampInt/1000, 0) //校验数据类型对不对 valType := reflect.TypeOf(edbValue) if valType == nil { continue } if valType.String() != "float64" { continue } sValue := edbValue.(float64) eDate := dataTime.Format(utils.FormatDate) if err != nil { return err } saveValue := utils.SubFloatToString(sValue, 30) if existVal, ok := dataMap[eDate]; !ok { timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, saveValue) isAdd = true } else { if existVal != saveValue { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, sValue, edbInfoId, eDate).Error //_, err = o.Raw(sql, sValue, edbInfoId, eDate).Exec() if err != nil { return err } } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { fmt.Println("RefreshAllEdbDataByLt add Err", err.Error()) return } } return } // QueryEdbDataByLt 获取路透社数据 func QueryEdbDataByLt(edbCode, startDate, endDate string) (dataList map[int64]interface{}, err error) { dataList = make(map[int64]interface{}) ltUrl := utils.Hz_Data_LT_Url + `edbInfo/ek?EdbCode=%s&StartDate=%s&EndDate=%s` ltUrl = fmt.Sprintf(ltUrl, edbCode, startDate, endDate) //utils.FileLogData.Info("ltUrl:%s", ltUrl) //body, err := http.Get(ltUrl) reqRes, err := http.Get(ltUrl) if err != nil { return } defer reqRes.Body.Close() body, err := ioutil.ReadAll(reqRes.Body) if err != nil { return } //utils.FileLogData.Info("lt result:%s", string(body)) //if err != nil { // return //} //fmt.Println(string(body)) item := new(edbDataModel.EdbDataFromLt) err = json.Unmarshal(body, &item) if err != nil { return } dataList = item.Close return } // RefreshEdbDataByZz 刷新郑商所指标数据 func RefreshEdbDataByZz(edbInfoId int, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } var suffix string if strings.Contains(edbCode, "deal") { suffix = "deal" } else if strings.Contains(edbCode, "buy") { suffix = "buy" } else if strings.Contains(edbCode, "sold") { suffix = "sold" } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} if edbCode != "" { if suffix == "deal" { condition += " AND deal_code=? " } else if suffix == "buy" { condition += " AND buy_code=? " } else { condition += " AND sold_code=? " } pars = append(pars, edbCode) } if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } glDataList, err := edbDataModel.GetZzDataByTradeCode(condition, pars) addSql := ` INSERT INTO edb_data_zz(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) for _, v := range glDataList { var value string if suffix == "deal" { value = v.DealValue } else if suffix == "buy" { value = v.BuyValue } else { value = v.SoldValue } item := v itemValue := value if _, ok := existMap[v.DataTime]; !ok { count, err := edbDataModel.GetEdbDataZzByCodeAndDate(edbCode, v.DataTime) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { eDate := item.DataTime sValue := itemValue if sValue != "" { dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue) isAdd = true } } else { updateSql := ` UPDATE edb_data_zz SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` err = tx.Exec(updateSql, value, edbInfoId, v.DataTime).Error if err != nil { return err } } } existMap[v.DataTime] = value } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } func RefreshEdbDataByDl(edbInfoId int, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } var suffix string if strings.Contains(edbCode, "deal") { suffix = "deal" } else if strings.Contains(edbCode, "buy") { suffix = "buy" } else if strings.Contains(edbCode, "sold") { suffix = "sold" } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} if edbCode != "" { if suffix == "deal" { condition += " AND deal_code=? " } else if suffix == "buy" { condition += " AND buy_code=? " } else { condition += " AND sold_code=? " } pars = append(pars, edbCode) } if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } glDataList, err := edbDataModel.GetDlDataByTradeCode(condition, pars) addSql := ` INSERT INTO edb_data_dl(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) for _, v := range glDataList { var value string if suffix == "deal" { value = v.DealValue } else if suffix == "buy" { value = v.BuyValue } else { value = v.SoldValue } item := v itemValue := value if _, ok := existMap[v.DataTime]; !ok { count, err := edbDataModel.GetEdbDataDlByCodeAndDate(edbCode, v.DataTime) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { eDate := item.DataTime sValue := itemValue if sValue != "" { dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue) isAdd = true } } else { updataSql := ` UPDATE edb_data_dl SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` err = tx.Exec(updataSql, value, edbInfoId, v.DataTime).Error if err != nil { return err } } } existMap[v.DataTime] = value } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error if err != nil { return err } } return } // RefreshEdbDataBySh 刷新上期所指标数据 func RefreshEdbDataBySh(edbInfoId int, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } var suffix string if strings.Contains(edbCode, "deal") { suffix = "deal" } else if strings.Contains(edbCode, "buy") { suffix = "buy" } else if strings.Contains(edbCode, "sold") { suffix = "sold" } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} if edbCode != "" { if suffix == "deal" { condition += " AND deal_code=? " } else if suffix == "buy" { condition += " AND buy_code=? " } else { condition += " AND sold_code=? " } pars = append(pars, edbCode) } if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } glDataList, err := edbDataModel.GetShDataByTradeCode(condition, pars) addSql := ` INSERT INTO edb_data_sh(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) for _, v := range glDataList { var value string if suffix == "deal" { value = v.DealValue } else if suffix == "buy" { value = v.BuyValue } else { value = v.SoldValue } item := v itemValue := value if _, ok := existMap[v.DataTime]; !ok { count, err := edbDataModel.GetEdbDataShByCodeAndDate(edbCode, v.DataTime) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { eDate := item.DataTime sValue := itemValue if sValue != "" { dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue) isAdd = true } } else { updataSql := ` UPDATE edb_data_sh SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` err = tx.Exec(updataSql, value, edbInfoId, v.DataTime).Error if err != nil { return err } } } existMap[v.DataTime] = value } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } // RefreshEdbDataByCffex 刷新中金所指标数据 func RefreshEdbDataByCffex(edbInfoId int, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } var suffix string if strings.Contains(edbCode, "deal") { suffix = "deal" } else if strings.Contains(edbCode, "buy") { suffix = "buy" } else if strings.Contains(edbCode, "sold") { suffix = "sold" } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} if edbCode != "" { if suffix == "deal" { condition += " AND deal_code=? " } else if suffix == "buy" { condition += " AND buy_code=? " } else { condition += " AND sold_code=? " } pars = append(pars, edbCode) } if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } glDataList, err := edbDataModel.GetCffexDataByTradeCode(condition, pars) addSql := ` INSERT INTO edb_data_cffex(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) for _, v := range glDataList { var value string if suffix == "deal" { value = v.DealValue } else if suffix == "buy" { value = v.BuyValue } else { value = v.SoldValue } item := v itemValue := value if _, ok := existMap[v.DataTime]; !ok { count, err := edbDataModel.GetEdbDataCffexByCodeAndDate(edbCode, v.DataTime) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { eDate := item.DataTime sValue := itemValue if sValue != "" { dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue) isAdd = true } } else { updateSql := ` UPDATE edb_data_cffex SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` err = tx.Exec(updateSql, value, edbInfoId, v.DataTime).Error if err != nil { return err } } } existMap[v.DataTime] = value } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } // RefreshEdbDataByShfe 刷新上期能源指标数据 func RefreshEdbDataByShfe(edbInfoId int, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } var suffix string if strings.Contains(edbCode, "deal") { suffix = "deal" } else if strings.Contains(edbCode, "buy") { suffix = "buy" } else if strings.Contains(edbCode, "sold") { suffix = "sold" } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} if edbCode != "" { if suffix == "deal" { condition += " AND deal_code=? " } else if suffix == "buy" { condition += " AND buy_code=? " } else { condition += " AND sold_code=? " } pars = append(pars, edbCode) } if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } glDataList, err := edbDataModel.GetShfeDataByTradeCode(condition, pars) addSql := ` INSERT INTO edb_data_ine(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) for _, v := range glDataList { var value string if suffix == "deal" { value = v.DealValue } else if suffix == "buy" { value = v.BuyValue } else { value = v.SoldValue } item := v itemValue := value if _, ok := existMap[v.DataTime]; !ok { count, err := edbDataModel.GetEdbDataShfeByCodeAndDate(edbCode, v.DataTime) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { eDate := item.DataTime sValue := itemValue if sValue != "" { dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue) isAdd = true } } else { updateSql := ` UPDATE edb_data_ine SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` err = tx.Exec(updateSql, value, edbInfoId, v.DataTime).Error if err != nil { return err } } } existMap[v.DataTime] = value } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error if err != nil { return err } } return } // RefreshEdbDataByGie 刷新欧洲天然气指标数据 func RefreshEdbDataByGie(edbInfoId int, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { fmt.Println("refresh err:", err) return } var suffix string l := len(edbCode) if strings.Contains(edbCode[l-2:], "GS") { suffix = "GS" } else if strings.Contains(edbCode[l-1:], "F") { suffix = "F" } else if strings.Contains(edbCode[l-1:], "T") { suffix = "T" } else if strings.Contains(edbCode[l-2:], "In") { suffix = "In" } else if strings.Contains(edbCode[l-3:], "Out") { suffix = "Out" } else if strings.Contains(edbCode[l-3:], "WGV") { suffix = "WGV" } else if strings.Contains(edbCode[l-2:], "IC") { suffix = "IC" } else if strings.Contains(edbCode[l-2:], "WC") { suffix = "WC" } else { suffix = "" } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} if edbCode != "" { condition += " AND eic_code=? " pars = append(pars, edbCode[:l-len(suffix)]) } if startDate != "" { condition += " AND gas_day_started_on>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND gas_day_started_on<=? " pars = append(pars, endDate) } eicDataList, err := edbDataModel.GetGieDataByTradeCode(condition, pars) fmt.Println("eicDataList", len(eicDataList)) addSql := ` INSERT INTO edb_data_gie(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) for _, v := range eicDataList { var value string if suffix == "GS" { value = v.GasInStorage } else if suffix == "F" { value = v.Full } else if suffix == "T" { value = v.Trend } else if suffix == "In" { value = v.Injection } else if suffix == "Out" { value = v.Withdrawal } else if suffix == "WGV" { value = v.WorkingGasVolume } else if suffix == "IC" { value = v.InjectionCapacity } else if suffix == "WC" { value = v.WithdrawalCapacity } item := v itemValue := value if _, ok := existMap[v.GasDayStartedOn]; !ok { count, err := edbDataModel.GetEdbDataGieByCodeAndDate(edbCode, v.GasDayStartedOn) if err != nil && err != utils.ErrNoRow { return err } if count <= 0 { eDate := item.GasDayStartedOn sValue := itemValue if sValue != "" { dataTime, err := time.Parse(utils.FormatDate, eDate) if err != nil { return err } timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) addSql += GetAddSql(edbInfoIdStr, edbCode, eDate, timeStr, sValue) isAdd = true } } else { updateSql := ` UPDATE edb_data_gie SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` err = tx.Exec(updateSql, value, edbInfoId, v.GasDayStartedOn).Error if err != nil { return err } } } existMap[v.GasDayStartedOn] = value } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error if err != nil { return err } } return } //刷新全部数据 func RefreshAllCalculate(edbInfoIdArr []*edbInfoModel.EdbInfo, edbInfoId, source int, edbCode, formulaStr, startDate, endDate string, edbInfoIdBytes []string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() fmt.Println(startDate, endDate) saveDataMap := make(map[string]map[int]float64) for _, v := range edbInfoIdArr { var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, v.EdbInfoId) if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } fmt.Println("v.Source:", v.Source) dataList, err := edbDataModel.GetEdbDataListAll(condition, pars, v.Source, 1) if err != nil { return err } dataMap := make(map[string]float64) for _, dv := range dataList { if val, ok := saveDataMap[dv.DataTime]; ok { if _, ok := val[v.EdbInfoId]; !ok { val[v.EdbInfoId] = dv.Value } } else { temp := make(map[int]float64) temp[v.EdbInfoId] = dv.Value saveDataMap[dv.DataTime] = temp } } item := new(edbDataModel.CalculateItems) item.EdbInfoId = v.EdbInfoId item.DataMap = dataMap } formulaMap := CheckFormula(formulaStr) addSql := ` INSERT INTO edb_data_calculate(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool //获取指标所有数据 dataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = global.MYSQL["data"].Raw(sql, edbInfoId).Scan(&dataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&dataList) if err != nil { return err } dataMap := make(map[string]string) for _, v := range dataList { dataMap[v.DataTime] = v.Value } edbInfoIdStr := strconv.Itoa(edbInfoId) existDataMap := make(map[string]string) for sk, sv := range saveDataMap { fmt.Println(sk, sv) formulaStr = strings.ToUpper(formulaStr) formulaFormStr := ReplaceFormula(edbInfoIdArr, sv, formulaMap, formulaStr, edbInfoIdBytes) if formulaFormStr != "" { //utils.FileLog.Info("formulaFormStr:%s", formulaFormStr) expression := formula.NewExpression(formulaFormStr) calResult, err := expression.Evaluate() if err != nil { err = errors.New("计算失败：Err:" + err.Error() + ";formulaStr:" + formulaFormStr) fmt.Println(err) return err } calVal, err := calResult.Float64() if err != nil { err = errors.New("计算失败：获取计算值失败 Err:" + err.Error() + ";formulaStr:" + formulaFormStr) fmt.Println(err) return err } saveValue := utils.SubFloatToString(calVal, 4) if existVal, ok := dataMap[sk]; !ok { dataTime, _ := time.Parse(utils.FormatDate, sk) timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) if _, existOk := existDataMap[sk]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, sk, timeStr, saveValue) isAdd = true } existDataMap[sk] = sk } else { if existVal != saveValue { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, saveValue, edbInfoId, sk).Error //_, err = o.Raw(sql, saveValue, edbInfoId, sk).Exec() if err != nil { return err } } } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { fmt.Println("RefreshAllCalculate add Err", err.Error()) return } } return } func CheckFormula(formula string) map[string]string { mathFormula := []string{"MAX", "MIN", "ABS", "ACOS", "ASIN", "CEIL", "MOD", "POW", "ROUND", "SIGN", "SIN", "TAN", "LOG10", "LOG2", "LOG"} str := strings.ToUpper(formula) for _, v := range mathFormula { str = strings.Replace(str, v, "", -1) } str = strings.Replace(str, "(", "", -1) str = strings.Replace(str, ")", "", -1) byteMap := make(map[string]string) for i := 0; i < len(str); i++ { byteInt := str[i] if byteInt >= 65 && byteInt <= 90 { byteStr := string(byteInt) if _, ok := byteMap[byteStr]; !ok { byteMap[byteStr] = byteStr } } } return byteMap } func GetFormulaMap() map[string]string { funMap := make(map[string]string) funMap["MAX"] = "[@@]" funMap["MIN"] = "[@!]" funMap["ABS"] = "[@#]" funMap["CEIL"] = "[@$]" funMap["COS"] = "[@%]" funMap["FLOOR"] = "[@^]" funMap["MOD"] = "[@&]" funMap["POW"] = "[@*]" funMap["ROUND"] = "[@(]" return funMap } func ReplaceFormula(edbInfoIdArr []*edbInfoModel.EdbInfo, valArr map[int]float64, formulaMap map[string]string, formulaStr string, edbInfoIdBytes []string) string { funMap := GetFormulaMap() for k, v := range funMap { formulaStr = strings.Replace(formulaStr, k, v, -1) } replaceCount := 0 for dk, dv := range edbInfoIdArr { if dk == 0 { dKey := edbInfoIdBytes[dk] if _, ok := formulaMap[dKey]; ok { //公式中存在 if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在 dvStr := fmt.Sprintf("%v", val) formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1) replaceCount++ } } } if dk == 1 { dKey := edbInfoIdBytes[dk] if _, ok := formulaMap[dKey]; ok { //公式中存在 if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在 dvStr := fmt.Sprintf("%v", val) formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1) replaceCount++ } } } if dk == 2 { dKey := edbInfoIdBytes[dk] if _, ok := formulaMap[dKey]; ok { //公式中存在 if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在 dvStr := fmt.Sprintf("%v", val) formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1) replaceCount++ } } } if dk == 3 { dKey := edbInfoIdBytes[dk] if _, ok := formulaMap[dKey]; ok { //公式中存在 if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在 dvStr := fmt.Sprintf("%v", val) formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1) replaceCount++ } } } if dk == 4 { dKey := edbInfoIdBytes[dk] if _, ok := formulaMap[dKey]; ok { //公式中存在 if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在 dvStr := fmt.Sprintf("%v", val) formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1) replaceCount++ } } } if dk == 5 { dKey := edbInfoIdBytes[dk] if _, ok := formulaMap[dKey]; ok { //公式中存在 if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在 dvStr := fmt.Sprintf("%v", val) formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1) replaceCount++ } } } if dk == 6 { dKey := edbInfoIdBytes[dk] if _, ok := formulaMap[dKey]; ok { //公式中存在 if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在 dvStr := fmt.Sprintf("%v", val) formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1) replaceCount++ } } } if dk == 7 { dKey := edbInfoIdBytes[dk] if _, ok := formulaMap[dKey]; ok { //公式中存在 if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在 dvStr := fmt.Sprintf("%v", val) formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1) replaceCount++ } } } if dk == 8 { dKey := edbInfoIdBytes[dk] if _, ok := formulaMap[dKey]; ok { //公式中存在 if val, valOk := valArr[dv.EdbInfoId]; valOk { //值存在 dvStr := fmt.Sprintf("%v", val) formulaStr = strings.Replace(formulaStr, dKey, dvStr, -1) replaceCount++ } } } } for k, v := range funMap { formulaStr = strings.Replace(formulaStr, v, k, -1) } if replaceCount == len(formulaMap) { return formulaStr } else { return "" } } func RefreshAllCalculateLjzzy(edbInfoId, source int, fromEdbInfo *edbInfoModel.EdbInfo, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) fmt.Println(edbInfoIdStr) //计算数据 var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, fromEdbInfo.EdbInfoId) if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } dataList, err := edbDataModel.GetEdbDataListAll(condition, pars, fromEdbInfo.Source, 1) if err != nil { return err } yearMap := make(map[int]map[int]*edbDataModel.EdbInfoSearchData) dataLen := len(dataList) for i := 0; i < dataLen; i++ { item := dataList[i] //日其中获取年 itemDate, err := time.Parse(utils.FormatDate, item.DataTime) if err != nil { return err } year := itemDate.Year() month := int(itemDate.Month()) if monthMap, yok := yearMap[year]; yok { monthMap[month] = item yearMap[year] = monthMap } else { monthMap = make(map[int]*edbDataModel.EdbInfoSearchData) monthMap[month] = item yearMap[year] = monthMap } } addSql := ` INSERT INTO edb_data_calculate_ljzzy(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool //获取指标所有数据 existDataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = global.MYSQL["data"].Raw(sql, edbInfoId).Scan(&existDataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList) if err != nil { return err } dataMap := make(map[string]string) for _, v := range existDataList { dataMap[v.DataTime] = v.Value } existDataMap := make(map[string]string) for yk, yv := range yearMap { _, oneMonthOk := yv[1] _, twoMonthOk := yv[2] if !oneMonthOk && !twoMonthOk { continue } for i := 1; i <= 12; i++ { //fmt.Println(yk, i, yv[i]) dataCurrentItem := yv[i] var date string var val float64 if i == 1 || i == 2 { if _, mok := yv[1]; mok { //1月有值 if i == 1 { date = dataCurrentItem.DataTime val, _ = decimal.NewFromFloat(dataCurrentItem.Value).Float64() //a.Div(b).Float64() } if i == 2 { dataOneItem := yv[1] if dataCurrentItem != nil && dataOneItem != nil { date = dataCurrentItem.DataTime twoMonth := decimal.NewFromFloat(dataCurrentItem.Value) oneMonth := decimal.NewFromFloat(dataOneItem.Value) val, _ = twoMonth.Sub(oneMonth).Float64() } } } else { //1月无值 dataTwoItem := yv[2] if i == 1 { date = strconv.Itoa(yk) + "-01-31" a := decimal.NewFromFloat(dataTwoItem.Value) b := decimal.NewFromFloat(2.0) val, _ = a.Div(b).Float64() } if i == 2 { date = dataCurrentItem.DataTime a := decimal.NewFromFloat(dataTwoItem.Value) b := decimal.NewFromFloat(2.0) val, _ = a.Div(b).Float64() } } } else { dataPreItem := yv[i-1] if dataCurrentItem != nil && dataPreItem != nil { date = dataCurrentItem.DataTime //val = dataCurrentItem.Value - dataPreItem.Value a := decimal.NewFromFloat(dataCurrentItem.Value) b := decimal.NewFromFloat(dataPreItem.Value) val, _ = a.Sub(b).Float64() } } if date != "" { saveValue := utils.SubFloatToString(val, 4) //判断数据是否存在 if existVal, ok := dataMap[date]; !ok { dataTime, _ := time.Parse(utils.FormatDate, date) timestamp := dataTime.UnixNano() / 1e6 timeStr := fmt.Sprintf("%d", timestamp) if _, existOk := existDataMap[date]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, date, timeStr, saveValue) isAdd = true } existDataMap[date] = date } else { if existVal != saveValue { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, saveValue, edbInfoId, date).Error //_, err = o.Raw(sql, saveValue, edbInfoId, date).Exec() if err != nil { return err } } } } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { fmt.Println("RefreshAllCalculateLjzzy add Err", err.Error()) return } } return } func RefreshAllCalculateTbz(edbInfoId, source int, fromEdbInfo *edbInfoModel.EdbInfo, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, fromEdbInfo.EdbInfoId) if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } fmt.Println("GetEdbDataListAll--start") dataList, err := edbDataModel.GetEdbDataListAll(condition, pars, fromEdbInfo.Source, 0) if err != nil { return err } fmt.Println("GetEdbDataListAll--end") var dateArr []string dataMap := make(map[string]*edbDataModel.EdbInfoSearchData) for _, v := range dataList { dateArr = append(dateArr, v.DataTime) dataMap[v.DataTime] = v } fmt.Println("source:", source) //获取指标所有数据 existDataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) fmt.Println("dataTableName:", dataTableName) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = global.MYSQL["data"].Raw(sql, edbInfoId).Scan(&existDataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList) if err != nil { return err } existDataMap := make(map[string]string) for _, v := range existDataList { existDataMap[v.DataTime] = v.Value } //fmt.Println("existDataMap:", existDataMap) addSql := ` INSERT INTO edb_data_calculate_tbz(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existAddDataMap := make(map[string]string) for _, av := range dateArr { currentItem := dataMap[av] if currentItem != nil { //当前日期 currentDate, err := time.Parse(utils.FormatDate, av) if err != nil { return err } //上一年的日期 preDate := currentDate.AddDate(-1, 0, 0) preDateStr := preDate.Format(utils.FormatDate) if findItem, ok := dataMap[preDateStr]; ok { //上一年同期找到 //dataTime, _ := time.Parse(utils.FormatDate, date) timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TbzDiv(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } continue } else { if fromEdbInfo.Frequency == "月度" { //向上和向下，各找一个月 for i := 0; i <= 35; i++ { nextDateDay := preDate.AddDate(0, 0, i) nextDateDayStr := nextDateDay.Format(utils.FormatDate) if findItem, ok := dataMap[nextDateDayStr]; ok { //上一年同期->下一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TbzDiv(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } else { preDateDay := preDate.AddDate(0, 0, -i) preDateDayStr := preDateDay.Format(utils.FormatDate) if findItem, ok := dataMap[preDateDayStr]; ok { //上一年同期->上一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TbzDiv(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } } } } else if fromEdbInfo.Frequency == "季度" || fromEdbInfo.Frequency == "年度" { if findItem, ok := dataMap[preDateStr]; ok { //上一年同期->下一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TbzDiv(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } } else { nextDateDay := preDate.AddDate(0, 0, 1) nextDateDayStr := nextDateDay.Format(utils.FormatDate) preDateDay := preDate.AddDate(0, 0, -1) preDateDayStr := preDateDay.Format(utils.FormatDate) for i := 0; i < 35; i++ { if i >= 1 { nextDateDay = nextDateDay.AddDate(0, 0, i) nextDateDayStr = nextDateDay.Format(utils.FormatDate) } if findItem, ok := dataMap[nextDateDayStr]; ok { //上一年同期->下一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TbzDiv(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } else { if i >= 1 { preDateDay = preDate.AddDate(0, 0, -i) preDateDayStr = nextDateDay.Format(utils.FormatDate) } if findItem, ok := dataMap[preDateDayStr]; ok { //上一年同期->上一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TbzDiv(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } } } } } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } func TbzDiv(a, b float64) string { var valStr string if b != 0 { af := decimal.NewFromFloat(float64(a)) bf := decimal.NewFromFloat(float64(b)) val, _ := af.Div(bf).Float64() val = val - 1 valStr = utils.SubFloatToString(val, 4) } else { valStr = "0" } return valStr } //刷新全部同差值数据 func RefreshAllCalculateTcz(edbInfoId, source int, fromEdbInfo *edbInfoModel.EdbInfo, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, fromEdbInfo.EdbInfoId) if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } dataList, err := edbDataModel.GetEdbDataListAll(condition, pars, fromEdbInfo.Source, 0) if err != nil { return err } var dateArr []string dataMap := make(map[string]*edbDataModel.EdbInfoSearchData) for _, v := range dataList { dateArr = append(dateArr, v.DataTime) dataMap[v.DataTime] = v } //获取指标所有数据 existDataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = global.MYSQL["data"].Raw(sql, edbInfoId).Scan(&existDataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList) if err != nil { return err } existDataMap := make(map[string]string) for _, v := range existDataList { existDataMap[v.DataTime] = v.Value } addSql := ` INSERT INTO edb_data_calculate_tcz(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existAddDataMap := make(map[string]string) for _, av := range dateArr { currentItem := dataMap[av] if currentItem != nil { //当前日期 currentDate, err := time.Parse(utils.FormatDate, av) if err != nil { return err } //上一年的日期 preDate := currentDate.AddDate(-1, 0, 0) preDateStr := preDate.Format(utils.FormatDate) if findItem, ok := dataMap[preDateStr]; ok { //上一年同期找到 //dataTime, _ := time.Parse(utils.FormatDate, date) timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TczSub(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } //utils.FileLog.Info("同期找到:" + av + ";" + preDateStr) continue } else { if fromEdbInfo.Frequency == "月度" { //向上和向下，各找一个月 for i := 0; i <= 35; i++ { nextDateDay := preDate.AddDate(0, 0, 1) nextDateDayStr := nextDateDay.Format(utils.FormatDate) if findItem, ok := dataMap[nextDateDayStr]; ok { //上一年同期->下一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TczSub(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } else { preDateDay := preDate.AddDate(0, 0, -1) preDateDayStr := preDateDay.Format(utils.FormatDate) if findItem, ok := dataMap[preDateDayStr]; ok { //上一年同期->上一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TczSub(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } } } } else if fromEdbInfo.Frequency == "季度" || fromEdbInfo.Frequency == "年度" { if findItem, ok := dataMap[preDateStr]; ok { //上一年同期->下一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TczSub(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } } else { for i := 0; i < 35; i++ { nextDateDay := preDate.AddDate(0, 0, 1) nextDateDayStr := nextDateDay.Format(utils.FormatDate) if findItem, ok := dataMap[nextDateDayStr]; ok { //上一年同期->下一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TczSub(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } else { preDateDay := preDate.AddDate(0, 0, -1) preDateDayStr := preDateDay.Format(utils.FormatDate) if findItem, ok := dataMap[preDateDayStr]; ok { //上一年同期->上一个月找到 timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := TczSub(currentItem.Value, findItem.Value) if existVal, ok := existDataMap[av]; !ok { if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, val) isAdd = true } existAddDataMap[av] = av } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } break } } } } } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } func TczSub(a, b float64) string { af := decimal.NewFromFloat(float64(a)) fmt.Println(af) bf := decimal.NewFromFloat(float64(b)) val, _ := af.Sub(bf).Float64() valStr := utils.SubFloatToString(val, 4) return valStr } //刷新全部N数值移动平均计算 func RefreshAllCalculateNszydpjjs(edbInfoId, source, formulaInt int, fromEdbInfo *edbInfoModel.EdbInfo, edbCode, startDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { fmt.Println("RefreshCalculateNszydpjjs Err:" + err.Error()) //utils.FileLog.Info("RefreshCalculateNszydpjjs Err:" + err.Error()) tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) fmt.Println(edbInfoIdStr) //计算数据 var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, edbInfoId) existDataList, err := edbDataModel.GetEdbDataListAll(condition, pars, source, 0) if err != nil { fmt.Println("existDataList GetEdbDataListAll Err:" + err.Error()) return err } existDataMap := make(map[string]*edbDataModel.EdbInfoSearchData) for _, v := range existDataList { existDataMap[v.DataTime] = v } //计算来源数据 var fromCondition string var fromPars []interface{} fromCondition += " AND edb_info_id=? " fromPars = append(fromPars, fromEdbInfo.EdbInfoId) fromCondition += " AND data_time>=? " fromPars = append(fromPars, startDate) fmt.Println("fromPars:", fromPars) fromDataList, err := edbDataModel.GetEdbDataListAll(fromCondition, fromPars, fromEdbInfo.Source, 0) if err != nil { fmt.Println("from GetEdbDataListAll Err:" + err.Error()) return err } var fromDateArr []string fromDataMap := make(map[string]*edbDataModel.EdbInfoSearchData) for _, v := range fromDataList { fromDateArr = append(fromDateArr, v.DataTime) fromDataMap[v.DataTime] = v } addSql := ` INSERT INTO edb_data_calculate_nszydpjjs(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool dataTableName := edbDataModel.GetEdbDataTableName(source) arrLen := len(fromDateArr) existAddDataMap := make(map[string]string) for ak, av := range fromDateArr { //处理第一个值 var valArr []float64 if findItem, ok := fromDataMap[av]; ok { valArr = append(valArr, findItem.Value) } else { continue } if ak+1 != arrLen { //处理除第一个值之外的N-1个值 for i := 1; i < formulaInt; i++ { arrIndex := ak + i if arrIndex >= arrLen { break } arrVal := fromDateArr[arrIndex] if findItem, ok := fromDataMap[arrVal]; ok { valArr = append(valArr, findItem.Value) } else { continue } } } valArrLen := len(valArr) //var totalVal float64 totalVal := decimal.NewFromFloat(0.00) for _, v := range valArr { newDecimal := decimal.NewFromFloat(v) totalVal = totalVal.Add(newDecimal) } af := totalVal //decimal.NewFromFloat(totalVal) bf := decimal.NewFromFloat(float64(valArrLen)) val, _ := af.Div(bf).Float64() valStr := utils.SubFloatToString(val, 4) if existVal, existOk := existDataMap[av]; !existOk { currentDate, err := time.Parse(utils.FormatDate, av) if err != nil { return err } timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) if _, existOk := existAddDataMap[av]; !existOk { addSql += GetAddSql(edbInfoIdStr, edbCode, av, timestampStr, valStr) isAdd = true } existAddDataMap[av] = av } else { saveValue := utils.SubFloatToString(existVal.Value, 30) if saveValue != valStr { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, av).Error //_, err = o.Raw(sql, val, edbInfoId, av).Exec() if err != nil { return err } } } } if isAdd { addSql = strings.TrimRight(addSql, ",") //utils.FileLog.Info("addSql:" + addSql) //_, err = o.Raw(addSql).Exec() err = tx.Exec(addSql).Error if err != nil { return err } } return } //刷新所有环比值数据 func RefreshAllCalculateHbz(edbInfoId, source int, fromEdbInfo *edbInfoModel.EdbInfo, edbCode, startDate, endDate string, formulaInt int) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, fromEdbInfo.EdbInfoId) if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } dataList, err := edbDataModel.GetEdbDataListAll(condition, pars, fromEdbInfo.Source, 0) if err != nil { return err } var dateArr []string dataMap := make(map[string]*edbDataModel.EdbInfoSearchData) for _, v := range dataList { dateArr = append(dateArr, v.DataTime) dataMap[v.DataTime] = v } fmt.Println("source:", source) //获取指标所有数据 existDataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) fmt.Println("dataTableName:", dataTableName) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = global.MYSQL["data"].Raw(sql, edbInfoId).Scan(&existDataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList) if err != nil { return err } existDataMap := make(map[string]string) for _, v := range existDataList { existDataMap[v.DataTime] = v.Value } addSql := ` INSERT INTO edb_data_calculate_hbz(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) dataLen := len(dataList) fmt.Println("dataLen:", dataLen) for i := 0; i < dataLen; i++ { j := i + formulaInt if j < dataLen { //当期 currentItem := dataList[i] preItem := dataList[j] if currentItem != nil && preItem != nil { existKey := edbCode + currentItem.DataTime if _, ok := existMap[existKey]; !ok { currentDate, _ := time.Parse(utils.FormatDate, currentItem.DataTime) timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := HbzDiv(currentItem.Value, preItem.Value) if val != "" { if existVal, findOk := existDataMap[currentItem.DataTime]; !findOk { addSql += GetAddSql(edbInfoIdStr, edbCode, currentItem.DataTime, timestampStr, val) isAdd = true } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, currentItem.DataTime).Error //_, err = o.Raw(sql, val, edbInfoId, currentItem.DataTime).Exec() if err != nil { return err } } } } } existMap[existKey] = currentItem.DataTime } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } //环比值，current:当期,pre:上期 公式: (当期-上期）/上期 func HbzDiv(current, pre float64) string { if pre == 0 { return "" } currentVal := decimal.NewFromFloat(float64(current)) preVal := decimal.NewFromFloat(float64(pre)) val, _ := currentVal.Sub(preVal).Div(preVal).Float64() valStr := utils.SubFloatToString(val, 4) return valStr } //刷新所有环差值数据 func RefreshAllCalculateHcz(edbInfoId, source int, fromEdbInfo *edbInfoModel.EdbInfo, edbCode, startDate, endDate string, formulaInt int) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, fromEdbInfo.EdbInfoId) if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } dataList, err := edbDataModel.GetEdbDataListAll(condition, pars, fromEdbInfo.Source, 0) if err != nil { return err } var dateArr []string dataMap := make(map[string]*edbDataModel.EdbInfoSearchData) for _, v := range dataList { dateArr = append(dateArr, v.DataTime) dataMap[v.DataTime] = v } fmt.Println("source:", source) //获取指标所有数据 existDataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) fmt.Println("dataTableName:", dataTableName) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = global.MYSQL["data"].Raw(sql, edbInfoId).Scan(&existDataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList) if err != nil { return err } existDataMap := make(map[string]string) for _, v := range existDataList { existDataMap[v.DataTime] = v.Value } //fmt.Println("existDataMap:", existDataMap) addSql := ` INSERT INTO edb_data_calculate_hcz(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) dataLen := len(dataList) fmt.Println("dataLen:", dataLen) for i := 0; i < dataLen; i++ { j := i + formulaInt if j < dataLen { //当期 currentItem := dataList[i] preItem := dataList[j] if currentItem != nil && preItem != nil { existKey := edbCode + currentItem.DataTime if _, ok := existMap[existKey]; !ok { currentDate, _ := time.Parse(utils.FormatDate, currentItem.DataTime) timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) val := HczDiv(currentItem.Value, preItem.Value) if val != "" { if existVal, findOk := existDataMap[currentItem.DataTime]; !findOk { addSql += GetAddSql(edbInfoIdStr, edbCode, currentItem.DataTime, timestampStr, val) isAdd = true } else { if existVal != val { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, val, edbInfoId, currentItem.DataTime).Error //_, err = o.Raw(sql, val, edbInfoId, currentItem.DataTime).Exec() if err != nil { return err } } } } } existMap[existKey] = currentItem.DataTime } } } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } //环差值，current:当期,pre:上期 公式:当期-上期 func HczDiv(current, pre float64) string { if pre == 0 { return "" } currentVal := decimal.NewFromFloat(current) preVal := decimal.NewFromFloat(pre) val, _ := currentVal.Sub(preVal).Float64() valStr := utils.SubFloatToString(val, 4) return valStr } //刷新所有变频数据 func RefreshAllCalculateBp(edbInfoId, source int, fromEdbInfo *edbInfoModel.EdbInfo, edbCode, startDate, endDate string) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() if err != nil { return } edbInfoIdStr := strconv.Itoa(edbInfoId) //计算数据 //计算数据 var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, fromEdbInfo.EdbInfoId) if startDate != "" { condition += " AND data_time>=? " pars = append(pars, startDate) } if endDate != "" { condition += " AND data_time<=? " pars = append(pars, endDate) } dataList, err := edbDataModel.GetEdbDataListAll(condition, pars, fromEdbInfo.Source, 0) if err != nil { return err } var dateArr []string dataMap := make(map[string]*edbDataModel.EdbInfoSearchData) for _, v := range dataList { dateArr = append(dateArr, v.DataTime) dataMap[v.DataTime] = v } fmt.Println("source:", source) //获取指标所有数据 existDataList := make([]*edbDataModel.EdbDataBase, 0) dataTableName := edbDataModel.GetEdbDataTableName(source) fmt.Println("dataTableName:", dataTableName) sql := `SELECT * FROM %s WHERE edb_info_id=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Raw(sql, edbInfoId).Scan(&existDataList).Error //_, err = o.Raw(sql, edbInfoId).QueryRows(&existDataList) if err != nil { return err } existDataMap := make(map[string]string) for _, v := range existDataList { existDataMap[v.DataTime] = v.Value } //fmt.Println("existDataMap:", existDataMap) addSql := ` INSERT INTO edb_data_calculate_bp(edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) values ` var isAdd bool existMap := make(map[string]string) dataLen := len(dataList) for i := 0; i < dataLen; i++ { //当期 currentItem := dataList[i] currentDate, _ := time.Parse(utils.FormatDate, currentItem.DataTime) var day int var preItem *edbDataModel.EdbInfoSearchData var preDate time.Time if i == 0 { day = int(time.Now().Sub(currentDate).Hours() / float64(24)) preDate = time.Now() } else { j := i + 1 if j < dataLen { preItem = dataList[j] preDate, _ = time.Parse(utils.FormatDate, preItem.DataTime) day = int(currentDate.Sub(preDate).Hours() / float64(24)) } } for k := 0; k <= day; k++ { needDay := preDate.AddDate(0, 0, -k) needDayStr := needDay.Format(utils.FormatDate) existKey := edbCode + needDayStr if _, ok := existMap[existKey]; !ok { timestamp := needDay.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) valStr := decimal.NewFromFloat(currentItem.Value).String() if existVal, ok := existDataMap[needDayStr]; !ok { addSql += GetAddSql(edbInfoIdStr, edbCode, needDayStr, timestampStr, valStr) } else { if existVal != valStr { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, valStr, edbInfoId, needDay).Error //_, err = o.Raw(sql, valStr, edbInfoId, needDay).Exec() if err != nil { return err } } } } existMap[existKey] = needDayStr } existKey := edbCode + currentItem.DataTime if _, ok := existMap[existKey]; !ok { currentDate, _ := time.Parse(utils.FormatDate, currentItem.DataTime) timestamp := currentDate.UnixNano() / 1e6 timestampStr := fmt.Sprintf("%d", timestamp) valStr := decimal.NewFromFloat(currentItem.Value).String() if existVal, ok := existDataMap[currentItem.DataTime]; !ok { addSql += GetAddSql(edbInfoIdStr, edbCode, currentItem.DataTime, timestampStr, valStr) } else { if existVal != valStr { sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? ` sql = fmt.Sprintf(sql, dataTableName) err = tx.Exec(sql, valStr, edbInfoId, currentItem.DataTime).Error //_, err = o.Raw(sql, valStr, edbInfoId, currentItem.DataTime).Exec() if err != nil { return err } } } } existMap[existKey] = currentItem.DataTime } if isAdd { addSql = strings.TrimRight(addSql, ",") err = tx.Exec(addSql).Error //_, err = o.Raw(addSql).Exec() if err != nil { return err } } return } // RefreshAllCalculateZjpj 刷新所有 直接拼接 数据 func RefreshAllCalculateZjpj(edbInfo *edbInfoModel.EdbInfo) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() //查询当前指标现有的数据 var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, edbInfo.EdbInfoId) dataList, err := edbDataModel.GetAllEdbDataCalculateZjpjByEdbInfoId(edbInfo.EdbInfoId) if err != nil { return err } var dateArr []string dataMap := make(map[string]*edbDataModel.EdbDataCalculateZjpj) removeDataTimeMap := make(map[string]int) //需要移除的日期数据 for _, v := range dataList { dateArr = append(dateArr, v.DataTime) dataMap[v.DataTime] = v removeDataTimeMap[v.DataTime] = 1 } //查询关联指标信息 var existCondition string var existPars []interface{} existCondition += " AND edb_info_id=? " existPars = append(existPars, edbInfo.EdbInfoId) existList, err := edbDataModel.GetEdbInfoCalculateListByCondition(existCondition, existPars) if err != nil { err = errors.New("判断指标是否改变失败,Err:" + err.Error()) return } var existItemA, existItemB *edbDataModel.EdbInfoCalculateMapping for _, existItem := range existList { if existItem.FromTag == "A" { existItemA = existItem } else if existItem.FromTag == "B" { existItemB = existItem } } addDataList := make([]*edbDataModel.EdbDataCalculateZjpj, 0) //第一个指标 { var condition string var pars []interface{} condition += " AND data_time < ? AND edb_info_id=? " pars = append(pars, edbInfo.CalculateFormula, existItemA.FromEdbInfoId) //第一个指标的数据列表 firstDataList, tmpErr := edbDataModel.GetEdbDataListAll(condition, pars, existItemA.FromSource, 0) if tmpErr != nil { return tmpErr } for _, v := range firstDataList { //校验待删除日期数据里面是否存在该元素，如果存在的话，那么移除该元素 if _, ok := removeDataTimeMap[v.DataTime]; ok { delete(removeDataTimeMap, v.DataTime) } //时间戳 if edbData, ok := dataMap[v.DataTime]; ok { if edbData.Value != v.Value { //更新指标数据 edbData.Value = v.Value tempErr := tx.Model(edbDataModel.EdbDataCalculateZjpj{}).Where("edb_data_id = ?", edbData.EdbDataId).Updates(edbDataModel.EdbDataCalculateZjpj{Value: v.Value}).Error if tempErr != nil { fmt.Println("tmpErr:", tmpErr) } //o.Update(edbData, "Value") } } else { //时间戳 currentDate, _ := time.Parse(utils.FormatDate, v.DataTime) timestamp := currentDate.UnixNano() / 1e6 edbDataZjpj := &edbDataModel.EdbDataCalculateZjpj{ EdbInfoId: edbInfo.EdbInfoId, EdbCode: edbInfo.EdbCode, DataTime: v.DataTime, Value: v.Value, Status: 1, CreateTime: time.Now(), ModifyTime: time.Now(), DataTimestamp: timestamp, } addDataList = append(addDataList, edbDataZjpj) } } } //第二个指标 { condition = `` pars = make([]interface{}, 0) condition += " AND data_time >= ? AND edb_info_id = ? " pars = append(pars, edbInfo.CalculateFormula, existItemB.FromEdbInfoId) //第二个指标的数据列表 secondDataList, tmpErr := edbDataModel.GetEdbDataListAll(condition, pars, existItemB.FromSource, 0) if tmpErr != nil { return tmpErr } for _, v := range secondDataList { //校验待删除日期数据里面是否存在该元素，如果存在的话，那么移除该元素 if _, ok := removeDataTimeMap[v.DataTime]; ok { delete(removeDataTimeMap, v.DataTime) } if edbData, ok := dataMap[v.DataTime]; ok { if edbData.Value != v.Value { //更新指标数据 edbData.Value = v.Value edbData.ModifyTime = time.Now() tempErr := tx.Model(edbDataModel.EdbDataCalculateZjpj{}).Where("edb_data_id = ?", edbData.EdbDataId).Updates(edbDataModel.EdbDataCalculateZjpj{Value: v.Value, ModifyTime: time.Now()}).Error if tempErr != nil { fmt.Println("tmpErr:", tmpErr) } //_, tmpErr := o.Update(edbData, "Value", "ModifyTime") //if tmpErr != nil { // fmt.Println("tmpErr:", tmpErr) //} } } else { //时间戳 currentDate, _ := time.Parse(utils.FormatDate, v.DataTime) timestamp := currentDate.UnixNano() / 1e6 edbDataZjpj := &edbDataModel.EdbDataCalculateZjpj{ EdbInfoId: edbInfo.EdbInfoId, EdbCode: edbInfo.EdbCode, DataTime: v.DataTime, Value: v.Value, Status: 1, CreateTime: time.Now(), ModifyTime: time.Now(), DataTimestamp: timestamp, } addDataList = append(addDataList, edbDataZjpj) } } } //删除已经不存在的累计同比拼接指标数据（由于同比值当日的数据删除了） { removeDateList := make([]string, 0) for dateTime := range removeDataTimeMap { removeDateList = append(removeDateList, dateTime) } if len(removeDateList) > 0 { removeDateStr := strings.Join(removeDateList, `","`) removeDateStr = `"` + removeDateStr + `"` //如果拼接指标变更了，那么需要删除所有的指标数据 tableName := edbDataModel.GetEdbDataTableName(edbInfo.Source) sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? and data_time in (%s) `, tableName, removeDateStr) err = tx.Exec(sql, edbInfo.EdbInfoId).Error //_, err = o.Raw(sql, edbInfo.EdbInfoId).Exec() if err != nil { err = errors.New("删除不存在的直接拼接指标数据失败,Err:" + err.Error()) return } } } //数据入库 tmpAddDataList := make([]*edbDataModel.EdbDataCalculateZjpj, 0) for _, v := range addDataList { tmpAddDataList = append(tmpAddDataList, v) if len(tmpAddDataList) >= 200 { tmpErr := tx.Create(tmpAddDataList).Error //_, tmpErr := o.InsertMulti(len(tmpAddDataList), tmpAddDataList) if tmpErr != nil { err = tmpErr return } //重新初始化需要加入的数据切片 tmpAddDataList = make([]*edbDataModel.EdbDataCalculateZjpj, 0) } } //最后如果还有需要新增的数据，那么就统一入库 if len(tmpAddDataList) > 0 { tmpErr := tx.Create(tmpAddDataList).Error //_, tmpErr := o.InsertMulti(len(tmpAddDataList), tmpAddDataList) if tmpErr != nil { err = tmpErr return } } return } // RefreshAllCalculateLjztbpj 刷新所有 累计值同比拼接 数据 func RefreshAllCalculateLjztbpj(edbInfo *edbInfoModel.EdbInfo) (err error) { tx := global.MYSQL["data"].Begin() defer func() { if err != nil { tx.Rollback() } else { tx.Commit() } }() //查询当前指标现有的数据 var condition string var pars []interface{} condition += " AND edb_info_id=? " pars = append(pars, edbInfo.EdbInfoId) //所有的数据 dataList, err := edbDataModel.GetAllEdbDataCalculateLjztbpjByEdbInfoId(edbInfo.EdbInfoId) if err != nil { return err } //待拼接指标map pjDataMap := make(map[string]float64) //需要入库的数据 nowEdbDataMap := make(map[string]float64) //当前指标的数据（已经在库里了，不需要重新） //拼接指标的日期切片数据 pjEdbDataTimeList := make([]string, 0) dataMap := make(map[string]*edbDataModel.EdbDataCalculateLjztbpj) for _, v := range dataList { pjEdbDataTimeList = append(pjEdbDataTimeList, v.DataTime) dataMap[v.DataTime] = v nowEdbDataMap[v.DataTime] = v.Value } //查询关联指标信息 var existCondition string var existPars []interface{} existCondition += " AND edb_info_id=? " existPars = append(existPars, edbInfo.EdbInfoId) existList, err := edbDataModel.GetEdbInfoCalculateListByCondition(existCondition, existPars) if err != nil { err = errors.New("判断指标是否改变失败,Err:" + err.Error()) return } var existItemA, existItemB *edbDataModel.EdbInfoCalculateMapping for _, existItem := range existList { if existItem.FromTag == "A" { existItemA = existItem } else if existItem.FromTag == "B" { existItemB = existItem } } // 原数据开始计算日期 startCalculationDate, _ := time.ParseInLocation(utils.FormatDate, edbInfo.CalculateFormula, time.Local) //待拼接指标 { var condition string var pars []interface{} condition += " AND data_time <= ? AND edb_info_id=? " pars = append(pars, startCalculationDate, existItemA.FromEdbInfoId) //第一个指标的数据列表 firstDataList, tmpErr := edbDataModel.GetEdbDataListAll(condition, pars, existItemA.FromSource, 0) if tmpErr != nil { return tmpErr } for _, v := range firstDataList { //时间戳 if edbData, ok := dataMap[v.DataTime]; ok { if edbData.Value != v.Value { //更新指标数据 edbData.Value = v.Value //tempErr := tx.Model() //o.Update(edbData, "Value") tempErr := tx.Model(edbDataModel.EdbDataCalculateLjztbpj{}).Where("edb_data_id = ?", edbData.EdbDataId).Updates(edbDataModel.EdbDataCalculateLjztbpj{Value: v.Value}).Error if tempErr != nil { fmt.Println("tmpErr:", tmpErr) } //将新的数据存入已入库指标map里面，以便后续计算 nowEdbDataMap[edbData.DataTime] = v.Value } } } } //同比值指标map tbzEdbDataMap := make(map[string]float64) //同比值日期切片列表 tbzEdbDataTimeList := make([]string, 0) //同比值指标 { var condition string var pars []interface{} condition += " AND data_time > ? AND edb_info_id = ? " pars = append(pars, startCalculationDate, existItemB.FromEdbInfoId) //第二个指标的数据列表 secondDataList, tmpErr := edbDataModel.GetEdbDataListAll(condition, pars, existItemB.FromSource, 0) if tmpErr != nil { return tmpErr } for _, v := range secondDataList { tbzEdbDataMap[v.DataTime] = v.Value tbzEdbDataTimeList = append(tbzEdbDataTimeList, v.DataTime) } } sort.Strings(tbzEdbDataTimeList) // 遍历现有的数据，判断拼接指标中是否存在该日期数据，如果拼接指标无此数据，那么需要删除该日期数据（日期的判断：需要在开始计算日期之后） removeDateList := make([]string, 0) for nowEdbDate := range nowEdbDataMap { nowEdbDateTime, _ := time.ParseInLocation(utils.FormatDate, nowEdbDate, time.Local) //校验日期 需要 大于 拼接前日期 if startCalculationDate.Before(nowEdbDateTime) { if _, ok := tbzEdbDataMap[nowEdbDate]; !ok { // 同比指标中，不存在该日期数据，那么需要移除 现有数据 中该日期的数据 removeDateList = append(removeDateList, nowEdbDate) } } } //待修改的指标数据map（index：日期，value：值） updateEdbDataMap := make(map[string]float64) for _, v := range tbzEdbDataTimeList { tbzDataTime, _ := time.ParseInLocation(utils.FormatDate, v, time.Local) //获取拼接指标上一年同一天的数据 var pjDataTime time.Time if tbzDataTime.Month() == 2 { pjDataTime = tbzDataTime.AddDate(0, -11, 0) pjDataTime = time.Date(pjDataTime.Year(), pjDataTime.Month(), 1, 0, 0, 0, 0, time.Now().Location()).AddDate(0, 0, -1) } else { pjDataTime = tbzDataTime.AddDate(-1, 0, 0) } //校验现有数据中，是否存在该日期的数据，如果存在的话，那么就要去校验 最新计算数据 与 现有数据 是否一致 if nowEdbDataValue, isHas := nowEdbDataMap[v]; isHas { //获取去年今日的数据，获取到后，然后是去修改该日期的数据 if lastYearEdbDataValue, ok := nowEdbDataMap[pjDataTime.Format(utils.FormatDate)]; ok { if v == "2021-08-31" { fmt.Println("进来了") } tbzDataValue := tbzEdbDataMap[v] //同比值 currValue := lastYearEdbDataValue * (1 + tbzDataValue/100) currValue, _ = decimal.NewFromFloat(currValue).Truncate(4).Float64() //保留4位小数 //如果计算出来的值与库里面的值不匹配，那么就去修改该值 if nowEdbDataValue != currValue { //将计算后的数据存入待拼接指标map里面，以便后续计算 updateEdbDataMap[v] = currValue } } } else { //因为 现有数据中 不存在该日期数据，那么需要做新增数据处理 //如果去年今日存在该数据，那么就去计算当前的数据 if pjDataValue, ok := nowEdbDataMap[pjDataTime.Format(utils.FormatDate)]; ok { tbzDataValue := tbzEdbDataMap[v] //同比值 currValue := pjDataValue * (1 + tbzDataValue/100) currValue, _ = decimal.NewFromFloat(currValue).Truncate(4).Float64() //将计算后的数据存入已入库指标map里面，以便后续计算 nowEdbDataMap[v] = currValue //将计算后的数据存入待拼接指标map里面，以便后续入库 pjDataMap[v] = currValue pjEdbDataTimeList = append(pjEdbDataTimeList, v) } } } //新增的数据入库 { addDataList := make([]*edbDataModel.EdbDataCalculateLjztbpj, 0) for dataTime, dataValue := range pjDataMap { //时间戳 currentDate, _ := time.Parse(utils.FormatDate, dataTime) timestamp := currentDate.UnixNano() / 1e6 edbDataLjztbpj := &edbDataModel.EdbDataCalculateLjztbpj{ EdbInfoId: edbInfo.EdbInfoId, EdbCode: edbInfo.EdbCode, DataTime: dataTime, Value: dataValue, Status: 1, CreateTime: time.Now(), ModifyTime: time.Now(), DataTimestamp: timestamp, } addDataList = append(addDataList, edbDataLjztbpj) } tmpAddDataList := make([]*edbDataModel.EdbDataCalculateLjztbpj, 0) for _, v := range addDataList { tmpAddDataList = append(tmpAddDataList, v) if len(tmpAddDataList) >= 200 { tmpErr := tx.Create(tmpAddDataList).Error //_, tmpErr := o.InsertMulti(len(tmpAddDataList), tmpAddDataList) if tmpErr != nil { err = tmpErr return } //重新初始化需要加入的数据切片 tmpAddDataList = make([]*edbDataModel.EdbDataCalculateLjztbpj, 0) } } //最后如果还有需要新增的数据，那么就统一入库 if len(tmpAddDataList) > 0 { tmpErr := tx.Create(tmpAddDataList).Error //_, tmpErr := o.InsertMulti(len(tmpAddDataList), tmpAddDataList) if tmpErr != nil { err = tmpErr return } } } //删除已经不存在的累计同比拼接指标数据（由于同比值当日的数据删除了） { if len(removeDateList) > 0 { removeDateStr := strings.Join(removeDateList, `","`) removeDateStr = `"` + removeDateStr + `"` //如果拼接指标变更了，那么需要删除所有的指标数据 tableName := edbDataModel.GetEdbDataTableName(edbInfo.Source) sql := fmt.Sprintf(` DELETE FROM %s WHERE edb_info_id = ? and data_time in (%s) `, tableName, removeDateStr) err = tx.Exec(sql, edbInfo.EdbInfoId).Error //_, err = o.Raw(sql, edbInfo.EdbInfoId).Exec() if err != nil { err = errors.New("删除不存在的累计值同比拼接指标数据失败,Err:" + err.Error()) return } } } //修改现有的数据中对应的值 { tableName := edbDataModel.GetEdbDataTableName(edbInfo.Source) for edbDate, edbDataValue := range updateEdbDataMap { sql := fmt.Sprintf(` UPDATE %s set value = ?,modify_time=now() WHERE edb_info_id = ? and data_time = ? `, tableName) err = tx.Exec(sql, edbDataValue, edbInfo.EdbInfoId, edbDate).Error //_, err = o.Raw(sql, edbDataValue, edbInfo.EdbInfoId, edbDate).Exec() if err != nil { err = errors.New("更新现有的累计值同比拼接指标数据失败,Err:" + err.Error()) return } } } return }