eta_crawler/models/base_from_national_statistics_index.go

package models

import (
	"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 {
	Index      *BaseFromNationalStatisticsIndex  `description:"指标信息"`
	IndexExist bool                              `description:"指标是否存在"`
	DataList   []*BaseFromNationalStatisticsData `description:"新增的指标数据"`
}

// SaveNationalStatisticsIndexAndData 保存指标和值
func SaveNationalStatisticsIndexAndData(req *SaveNationalStatisticsIndexAndDataReq) (err error) {
	if req.Index == nil {
		return
	}
	o := orm.NewOrmUsingDB("data")
	tx, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
			return
		}
		_ = tx.Commit()
	}()

	if !req.IndexExist {
		id, e := tx.Insert(req.Index)
		if e != nil {
			return e
		}
		req.Index.BaseFromNationalStatisticsIndexId = int(id)
	}
	indexId := req.Index.BaseFromNationalStatisticsIndexId
	if req.DataList != nil && len(req.DataList) > 0 {
		for _, d := range req.DataList {
			d.BaseFromNationalStatisticsIndexId = indexId
		}
		_, e := tx.InsertMulti(len(req.DataList), req.DataList)
		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
}