hz_crm_api/models/data_manage/edb_data_sci.go

package data_manage

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/utils"
	"strconv"
	"strings"
	"time"
)

type BaseFromSciDataSimple struct {
	SciDataId          int `orm:"column(sci_data_id);pk"`
	BaseFromSciIndexId int
	IndexCode          string
	DataTime           string
	Value              string
}

func GetEdbDataSciMaxAndMinDate(edbCode string) (min_date, max_date string, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT MIN(data_time) AS min_date,MAX(data_time) AS max_date FROM edb_data_sci WHERE edb_code=? `
	err = o.Raw(sql, edbCode).QueryRow(&min_date, &max_date)
	return
}

// 全部刷新有色数据
func RefreshAllEdbDataBySci(edbInfoId, source int, edbCode, startDate, endDate string) (err error) {
	o := orm.NewOrmUsingDB("data")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	if err != nil {
		return
	}
	edbInfoIdStr := strconv.Itoa(edbInfoId)

	//获取已存在指标所有数据
	existDataList := make([]*EdbDataBase, 0)
	dataTableName := GetEdbDataTableName(source)
	sql := `SELECT * FROM %s WHERE edb_info_id=? `
	sql = fmt.Sprintf(sql, dataTableName)
	_, err = to.Raw(sql, edbInfoId).QueryRows(&existDataList)
	if err != nil {
		return err
	}
	existDataMap := make(map[string]string)
	for _, v := range existDataList {
		existDataMap[v.DataTime] = v.Value
	}

	sciDateList := make([]*BaseFromSciDataSimple, 0)
	sciSql := ` SELECT * FROM base_from_sci_data WHERE index_code=? AND data_time>=? `
	_, err = to.Raw(sciSql, edbCode, startDate).QueryRows(&sciDateList)
	if err != nil {
		return err
	}

	addSql := ` INSERT INTO edb_data_sci(edb_info_id,edb_code,data_time,value,create_time,modify_time,status,data_timestamp) values `
	var isAdd bool
	for _, sv := range sciDateList {
		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 = to.Raw(sql, sv.Value, edbInfoId, sv.DataTime).Exec()
				if err != nil {
					return err
				}
			}
		}
	}
	if isAdd {
		addSql = strings.TrimRight(addSql, ",")
		_, err = to.Raw(addSql).Exec()
		if err != nil {
			return err
		}
	}
	return
}