hz_crm_api/models/data_manage/base_from_national_statistics_index.go

package data_manage

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/utils"
	"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, limitNum int) (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
	}
	limit := ``
	if limitNum > 0 {
		limit += fmt.Sprintf(` LIMIT %d`, limitNum)
	}
	sql := fmt.Sprintf(`SELECT %s FROM %s WHERE 1=1 %s %s %s`, fields, m.TableName(), condition, order, limit)
	_, 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
}

type BaseFromNationalStatisticsIndexItem struct {
	IndexId          int    `description:"指标ID"`
	ClassifyId       int    `description:"指标分类ID"`
	Dbcode           string `description:"dbcode"`
	IndexCode        string `description:"指标编码"`
	IndexName        string `description:"指标名称"`
	Unit             string `description:"单位"`
	Frequency        string `description:"频度"`
	Reg              string `description:"地区"`
	StartDate        string `description:"开始日期"`
	EndDate          string `description:"结束日期"`
	CreateTime       string `description:"创建时间"`
	ModifyTime       string `description:"更新时间"`
	ChildrenIndexIds []int  `description:"有地区维度的指标-子指标ID集合-用于前端定位"`
}

type BaseFromNationalStatisticsIndexListResp struct {
	List    []*BaseFromNationalStatisticsIndexItem `description:"指标列表"`
	RegList []string                               `description:"地区列表"`
}

func FormatBaseFromNationalStatisticsIndex2Item(v *BaseFromNationalStatisticsIndex) (item *BaseFromNationalStatisticsIndexItem) {
	item = new(BaseFromNationalStatisticsIndexItem)
	item.IndexId = v.BaseFromNationalStatisticsIndexId
	item.ClassifyId = v.BaseFromNationalStatisticsClassifyId
	item.Dbcode = v.Dbcode
	item.IndexCode = v.IndexCode
	item.IndexName = v.IndexName
	item.Unit = v.Unit
	item.Frequency = v.Frequency
	item.Reg = v.Reg
	item.StartDate = v.StartDate.Format(utils.FormatDate)
	item.EndDate = v.EndDate.Format(utils.FormatDate)
	item.CreateTime = v.CreateTime.Format(utils.FormatDateTime)
	item.ModifyTime = v.ModifyTime.Format(utils.FormatDateTime)
	return
}

type BaseFromNationalStatisticsIndexDetailItem struct {
	BaseFromNationalStatisticsIndexItem
	DataList []*BaseFromNationalStatisticsIndexDetailData `description:"数据列表"`
}

type BaseFromNationalStatisticsIndexDetailData struct {
	DataTime string  `description:"数据日期"`
	Value    float64 `description:"数据值"`
}

func GetBaseFromNationalStatisticsIndexByIndexCode(indexCode string) (item *BaseFromNationalStatisticsIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT * FROM base_from_national_statistics_index WHERE index_code = ? LIMIT 1`
	err = o.Raw(sql, indexCode).QueryRow(&item)
	return
}