eta_crawler/models/base_from_national_statistics_index.go

package models

import (
	"eta/eta_crawler/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"strings"
	"time"
)

// BaseFromNationalStatisticsIndex 国统局-指标
type BaseFromNationalStatisticsIndex struct {
	BaseFromNationalStatisticsIndexId    int       `orm:"column(base_from_national_statistics_index_id);pk"`
	BaseFromNationalStatisticsClassifyId int       `description:"指标分类ID"`
	Dbcode                               string    `description:"dbcode"`
	IndexCode                            string    `description:"指标编码"`
	IndexName                            string    `description:"指标名称"`
	Unit                                 string    `description:"单位"`
	Frequency                            string    `description:"频度"`
	Reg                                  string    `description:"地区"`
	StartDate                            time.Time `description:"开始日期"`
	EndDate                              time.Time `description:"结束日期"`
	CreateTime                           time.Time `description:"创建时间"`
	ModifyTime                           time.Time `description:"更新时间"`
}

func (m *BaseFromNationalStatisticsIndex) TableName() string {
	return "base_from_national_statistics_index"
}

func (m *BaseFromNationalStatisticsIndex) Create() (err error) {
	o := orm.NewOrmUsingDB("data")
	id, err := o.Insert(m)
	if err != nil {
		return
	}
	m.BaseFromNationalStatisticsIndexId = int(id)
	return
}

func (m *BaseFromNationalStatisticsIndex) CreateMulti(items []*BaseFromNationalStatisticsIndex) (err error) {
	if len(items) == 0 {
		return
	}
	o := orm.NewOrmUsingDB("data")
	_, err = o.InsertMulti(len(items), items)
	return
}

func (m *BaseFromNationalStatisticsIndex) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Update(m, cols...)
	return
}

func (m *BaseFromNationalStatisticsIndex) Del() (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `DELETE FROM base_from_national_statistics_index WHERE base_from_national_statistics_index_id = ? LIMIT 1`
	_, err = o.Raw(sql, m.BaseFromNationalStatisticsIndexId).Exec()
	return
}

func (m *BaseFromNationalStatisticsIndex) GetItemById(id int) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_national_statistics_index WHERE base_from_national_statistics_index_id = ? LIMIT 1`
	err = o.Raw(sql, id).QueryRow(&m)
	return
}

func (m *BaseFromNationalStatisticsIndex) GetItemByCondition(condition string, pars []interface{}) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_national_statistics_index WHERE 1=1 `
	sql += condition
	sql += ` LIMIT 1`
	err = o.Raw(sql, pars).QueryRow(&m)
	return
}

func (m *BaseFromNationalStatisticsIndex) GetCountByCondition(condition string, pars []interface{}) (count int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := fmt.Sprintf(`SELECT COUNT(1) FROM %s WHERE 1=1 %s`, m.TableName(), condition)
	err = o.Raw(sql, pars).QueryRow(&count)
	return
}

func (m *BaseFromNationalStatisticsIndex) GetItemsByCondition(condition string, pars []interface{}, fieldArr []string, orderRule string) (items []*BaseFromNationalStatisticsIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	fields := strings.Join(fieldArr, ",")
	if len(fieldArr) == 0 {
		fields = `*`
	}
	order := `ORDER BY create_time DESC`
	if orderRule != "" {
		order = ` ORDER BY ` + orderRule
	}
	sql := fmt.Sprintf(`SELECT %s FROM %s WHERE 1=1 %s %s`, fields, m.TableName(), condition, order)
	_, err = o.Raw(sql, pars).QueryRows(&items)
	return
}

// SaveNationalStatisticsIndexAndDataReq 保存指标和数据请求体
type SaveNationalStatisticsIndexAndDataReq struct {
	//IndexExist bool                              `description:"指标是否存在"`
	Index    *BaseFromNationalStatisticsIndex  `description:"指标信息"`
	DataList []*BaseFromNationalStatisticsData `description:"新增的指标数据"`
}

// SaveNationalStatisticsIndexAndData 保存指标和值
func SaveNationalStatisticsIndexAndData(index *BaseFromNationalStatisticsIndex, data []*BaseFromNationalStatisticsData) (err error) {
	if index == nil {
		return
	}
	indexExist := new(BaseFromNationalStatisticsIndex)
	indexCond := ` AND index_code = ?`
	indexPars := make([]interface{}, 0)
	indexPars = append(indexPars, index.IndexCode)
	if e := indexExist.GetItemByCondition(indexCond, indexPars); e != nil && e.Error() != utils.ErrNoRow() {
		return e
	}
	dataOB := new(BaseFromNationalStatisticsData)
	dataCond := ` AND index_code = ?`
	dataPars := make([]interface{}, 0)
	dataPars = append(dataPars, index.IndexCode)
	dataList, e := dataOB.GetItemsByCondition(dataCond, dataPars, []string{}, "data_time ASC")
	if e != nil {
		return e
	}
	dataMap := make(map[string]*BaseFromNationalStatisticsData)
	for _, d := range dataList {
		dataMap[d.DataTime.Format(utils.FormatDate)] = d
	}

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

	indexId := indexExist.BaseFromNationalStatisticsIndexId
	if indexExist.BaseFromNationalStatisticsIndexId == 0 {
		id, e := tx.Insert(index)
		if e != nil {
			return e
		}
		indexId = int(id)
	}
	if indexExist.BaseFromNationalStatisticsIndexId > 0 {
		indexExist.ModifyTime = time.Now().Local()
		if e = indexExist.Update([]string{"ModifyTime"}); e != nil {
			return e
		}
	}
	inserts := make([]*BaseFromNationalStatisticsData, 0)
	updateCols := []string{"Value", "ModifyTime"}
	for _, d := range data {
		k := d.DataTime.Format(utils.FormatDate)
		// 更新数据
		v := dataMap[k]
		if v != nil {
			v.Value = d.Value
			v.ModifyTime = time.Now().Local()
			if _, e = tx.Update(v, updateCols...); e != nil {
				err = e
				return
			}
			continue
		}
		d.BaseFromNationalStatisticsIndexId = indexId
		inserts = append(inserts, d)
	}
	// 批量插入新数据
	if len(inserts) > 0 {
		_, e = tx.InsertMulti(len(inserts), inserts)
		if e != nil {
			return e
		}
	}
	return
}

// BatchSaveNationalStatisticsIndexAndData 批量保存指标和值
func BatchSaveNationalStatisticsIndexAndData(indexArr []*BaseFromNationalStatisticsIndex, indexDataMap map[string][]*BaseFromNationalStatisticsData) (err error) {
	o := orm.NewOrmUsingDB("data")
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
			return
		}
		_ = tx.Commit()
	}()

	// 指标
	for _, v := range indexArr {
		id, e := tx.Insert(v)
		if e != nil {
			return e
		}
		indexId := int(id)

		// 数据
		dataList := indexDataMap[v.IndexCode]
		if dataList != nil && len(dataList) > 0 {
			for _, d := range dataList {
				d.BaseFromNationalStatisticsIndexId = indexId
			}
			_, e = tx.InsertMulti(len(dataList), dataList)
			if e != nil {
				return e
			}
		}
	}
	return
}

// UpdateNationalStatisticsIndexStartEndDate 更新指标开始结束日期
func UpdateNationalStatisticsIndexStartEndDate() (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `UPDATE base_from_national_statistics_index AS a
			JOIN (
				SELECT
					index_code,
					MIN(data_time) AS min_time,
					MAX(data_time) AS max_time
				FROM
					base_from_national_statistics_data
				GROUP BY
					index_code
			) AS b ON a.index_code = b.index_code
			SET a.start_date = b.min_time, a.end_date = b.max_time`
	_, err = o.Raw(sql).Exec()
	return
}