eta_api/models/data_manage/edb_data_insert_config.go

package data_manage

import (
	"errors"
	"eta/eta_api/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"strconv"
	"time"
)

// EdbDataInsertConfig 指标数据插入配置表
type EdbDataInsertConfig struct {
	EdbInfoId  int       `orm:"column(edb_info_id);pk" description:"指标id"`
	Date       time.Time `description:"插入的日期"`
	Value      string    `description:"插入的值"`
	RealDate   time.Time `description:"实际数据的值日期"`
	ModifyTime time.Time `description:"数据更新时间"`
	CreateTime time.Time `description:"数据插入的时间"`
}

// GetEdbDataInsertConfigByEdbId 根据指标id 获取数据插入配置详情
func GetEdbDataInsertConfigByEdbId(edbInfoId int) (item *EdbDataInsertConfig, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_data_insert_config WHERE edb_info_id=? `
	err = o.Raw(sql, edbInfoId).QueryRow(&item)
	return
}

// GetEdbDataInsertConfigByEdbIdList 根据指标id列表 获取数据插入配置详情
func GetEdbDataInsertConfigByEdbIdList(edbInfoIdList []int) (items []*EdbDataInsertConfig, err error) {
	num := len(edbInfoIdList)
	if num <= 0 {
		return
	}

	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM edb_data_insert_config WHERE edb_info_id in (` + utils.GetOrmInReplace(num) + `) `
	_, err = o.Raw(sql, edbInfoIdList).QueryRows(&items)
	return
}

// CreateEdbDataInsertConfigAndData 创建数据插入配置规则,及插入数据
func CreateEdbDataInsertConfigAndData(edbInfo *EdbInfo, date time.Time, value string) (err error, errMsg string, isSendEmail bool) {
	isSendEmail = true

	tableName := GetEdbDataTableName(edbInfo.Source, edbInfo.SubSource)
	if tableName == `` {
		err = errors.New("找不到该指标的数据表")
		return
	}

	dateStr := date.Format(utils.FormatDate)
	timestamp := date.UnixNano() / 1e6
	timeStr := fmt.Sprintf("%d", timestamp)

	var saveValue string
	if value != "" {
		floatValue, e := strconv.ParseFloat(value, 64)
		if e != nil {
			err = e
			fmt.Println("转换失败:", e.Error())
			return
		}
		saveValue = utils.SubFloatToString(floatValue, 30)
	}


	to, err := orm.NewOrmUsingDB("data").Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	var oldConfigDate time.Time //之前配置的日期
	// 添加/变更配置
	{
		var item *EdbDataInsertConfig
		sql := ` SELECT * FROM edb_data_insert_config WHERE edb_info_id=? `
		err = to.Raw(sql, edbInfo.EdbInfoId).QueryRow(&item)
		if err != nil && err.Error() != utils.ErrNoRow() {
			return
		}
		err = nil

		// 如果是没有配置，那么就需要添加配置
		if item == nil {
			var currLatestDate time.Time
			currLatestDateStr := edbInfo.LatestDate // 实际日期
			if currLatestDateStr != `` && currLatestDateStr != `0000-00-00` {
				currLatestDate, _ = time.ParseInLocation(utils.FormatDate, currLatestDateStr, time.Local)
			}
			if !currLatestDate.IsZero() && (currLatestDate.After(date) || currLatestDate.Equal(date)) {
				errMsg = `选择日期不能早于/等于数据最新日期`
				err = errors.New("选择日期不能早于/等于数据最新日期,数据最新日期：" + edbInfo.EndDate + ";填写日期：" + dateStr)
				isSendEmail = false
				return
			}

			realDate, _ := time.ParseInLocation(utils.FormatDate, edbInfo.LatestDate, time.Local)
			item = &EdbDataInsertConfig{
				EdbInfoId:  edbInfo.EdbInfoId,
				Date:       date,
				Value:      saveValue,
				RealDate:   realDate,
				ModifyTime: time.Now(),
				CreateTime: time.Now(),
			}
			_, err = to.Insert(item)
		} else {
			if date.Equal(item.RealDate) || date.Before(item.RealDate) {
				errMsg = `选择日期不能早于/等于数据最新日期`
				err = errors.New("选择日期不能早于/等于数据最新日期,数据最新日期：" + edbInfo.EndDate + ";填写日期：" + dateStr)
				isSendEmail = false
				return
			}
			oldConfigDate = item.Date // 之前配置的日期

			item.Date = date
			item.Value = saveValue
			item.ModifyTime = time.Now()
			_, err = to.Update(item, "Date", "Value", "ModifyTime")
		}
		if err != nil {
			return
		}
	}

	var offsetDate string
	// 更改数据
	{
		var edbDateData *EdbDataList
		if !oldConfigDate.IsZero() {
			sql := `SELECT edb_data_id,edb_info_id,data_time,value,data_timestamp FROM %s WHERE edb_info_id=? AND data_time = ?`
			sql = fmt.Sprintf(sql, tableName)
			err = to.Raw(sql, edbInfo.EdbInfoId, oldConfigDate.Format(utils.FormatDate)).QueryRow(&edbDateData)
			if err != nil && err.Error() != utils.ErrNoRow() {
				return
			}
			err = nil
		}

		// 如果是没有历史数据，那么就需要增加数据
		if edbDateData == nil {
			addSql := ` INSERT INTO %s (edb_info_id,edb_code,data_time,value,create_time,modify_time,data_timestamp) VALUES ( %d, "%s", "%s", "%s", now(), now(), %s) `
			addSql = fmt.Sprintf(addSql, tableName, edbInfo.EdbInfoId, edbInfo.EdbCode, dateStr, saveValue, timeStr)
			_, err = to.Raw(addSql).Exec()
		} else if value == "" {
			// 传空值时删除
			sql := `SELECT data_time FROM %s WHERE edb_info_id=? ORDER BY data_time DESC LIMIT 1 OFFSET 1`
			sql = fmt.Sprintf(sql, tableName)
			err = to.Raw(sql, edbInfo.EdbInfoId).QueryRow(&offsetDate)
			if err != nil && err.Error() != utils.ErrNoRow() {
				return
			}

			dateStr = offsetDate

			deleteSql := `DELETE FROM %s WHERE edb_data_id = %d;`
			deleteSql = fmt.Sprintf(deleteSql, tableName, edbDateData.EdbDataId)
			_, err = to.Raw(deleteSql).Exec()
		} else {
			updateSql := `UPDATE %s SET data_time = "%s", value = "%s", modify_time= now(), data_timestamp= %s WHERE edb_data_id = %d;`
			updateSql = fmt.Sprintf(updateSql, tableName, dateStr, saveValue, timeStr, edbDateData.EdbDataId)
			_, err = to.Raw(updateSql).Exec()
		}
		if err != nil {
			return
		}
	}

	edbInfo.EndDate = dateStr
	_, err = to.Update(edbInfo, "EndDate")

	return
}