eta_chart_lib/models/data_manage/excel/excel_chart_edb.go

package excel

import (
	"eta/eta_chart_lib/global"
	"eta/eta_chart_lib/models"
	"eta/eta_chart_lib/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"gorm.io/gorm"
	"strconv"
	"strings"
	"time"
)

type ExcelChartEdb struct {
	//ExcelChartEdbId int       `orm:"column(excel_chart_edb_id);pk"`
	ExcelChartEdbId int       `gorm:"column:excel_chart_edb_id;primaryKey"`
	ExcelInfoId     int       `description:"表格id"`
	ChartInfoId     int       `description:"图表id"`
	EdbCode         string    `description:"指标编码"`
	EdbName         string    `description:"指标名称"`
	EdbNameEn       string    `description:"指标英文名称"`
	Unit            string    `description:"指标单位"`
	UnitEn          string    `description:"指标单位"`
	DateSequence    string    `description:"日期序列选区"`
	DataSequence    string    `description:"数据序列选区"`
	SysUserId       int       `description:"创建人"`
	SysUserRealName string    `description:"创建人姓名"`
	MaxData         float64   `description:"上限"`
	MinData         float64   `description:"下限"`
	IsOrder         bool      `description:"true:正序,false:逆序"`
	IsAxis          int       `description:"true:左轴,false:右轴"`
	EdbInfoType     int       `description:"true:标准指标,false:领先指标"`
	LeadValue       int       `description:"领先值"`
	LeadUnit        string    `description:"领先单位"`
	FromTag         string    `description:"标签"`
	ModifyTime      time.Time `description:"修改时间"`
	CreateTime      time.Time `description:"创建时间"`
	ChartWidth      float64   `description:"线条大小"`
}

type ExcelChartEdbView struct {
	ExcelChartEdbId int
	ExcelInfoId     int    `description:"表格id"`
	ChartInfoId     int    `description:"图表id"`
	EdbCode         string `description:"指标编码"`
	EdbName         string `description:"指标名称"`
	DateSequenceStr string `description:"日期序列选区"`
	DataSequenceStr string `description:"数据序列选区"`
	/*MaxData         float64 `description:"上限"`
	MinData         float64 `description:"下限"`
	IsOrder         bool    `description:"true:正序,false:逆序"`
	IsAxis          int     `description:"true:左轴,false:右轴"`
	EdbInfoType     int     `description:"true:标准指标,false:领先指标"`
	LeadValue       int     `description:"领先值"`
	LeadUnit        string  `description:"领先单位"`*/
	FromTag string `description:"标签"`
}

type BalanceTableChart struct {
	ChartInfoId       int    `description:"图表id,新增时传0"`
	ChartName         string `description:"图表名称"`
	ChartType         int    `description:"生成样式:1:曲线图,2:季节性图,3:面积图，4：柱状图，5：散点图，6：组合图，7：柱方图"`
	Calendar          string `description:"公历/农历"`
	LeftMin           string `description:"图表左侧最小值"`
	LeftMax           string `description:"图表左侧最大值"`
	RightMin          string `description:"图表右侧最小值"`
	RightMax          string `description:"图表右侧最大值"`
	Right2Min         string `description:"图表右侧2最小值"`
	Right2Max         string `description:"图表右侧2最大值"`
	MinMaxSave        int    `description:"是否手动保存过上下限：0-否；1-是"`
	ExtraConfig       string `description:"图表额外配置信息,json字符串"`
	ChartImage        string `description:"封面图" json:"-"`
	SeasonExtraConfig string `description:"季节性图表中的配置,json数据"`
	SourcesFrom       string `description:"图表来源"`
	//	ChartEdbInfoList  []ExcelChartEdbView
}

func (e *ExcelChartEdb) TableName() string {
	return "excel_chart_edb"
}

// 新增
func (e *ExcelChartEdb) Add() (err error) {
	//o := orm.NewOrmUsingDB("data")
	//_, err = o.Insert(e)
	err = global.DbMap[utils.DbNameIndex].Create(&e).Error
	return
}

// 修改
func (e *ExcelChartEdb) Update(cols []string) (err error) {
	//o := orm.NewOrmUsingDB("data")
	//_, err = o.Update(e, cols...)
	err = global.DbMap[utils.DbNameIndex].Model(&e).Select(cols).Updates(&e).Error
	return
}

// 删除
func (e *ExcelChartEdb) Delete() (err error) {
	//o := orm.NewOrmUsingDB("data")
	//_, err = o.Delete(e)
	err = global.DbMap[utils.DbNameIndex].Delete(&e).Error
	return
}

type AddChartEdbAndDataItem struct {
	ChartEdb *ExcelChartEdb
	DataList []*ExcelChartData `description:"数据列表"`
}

// 同时添加指标和指标数据
func (e *ExcelChartEdb) AddChartEdbAndData(list []*AddChartEdbAndDataItem, chartInfo *models.ChartInfo, deleteEdbIds []int) (err error) {
	//o, err := orm.NewOrmUsingDB("data").Begin()
	o := global.DbMap[utils.DbNameIndex].Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = o.Rollback()
		} else {
			_ = o.Commit()
		}
	}()

	// 先删除原先的绑定的指标
	if len(deleteEdbIds) > 0 && chartInfo.ChartInfoId > 0 {
		//sql := `DELETE FROM excel_chart_edb WHERE chart_info_id = ? AND excel_chart_edb_id in (` + utils.GetOrmInReplace(len(deleteEdbIds)) + `)`
		sql := `DELETE FROM excel_chart_edb WHERE chart_info_id = ? AND excel_chart_edb_id in ?`
		//_, err = o.Raw(sql, chartInfo.ChartInfoId, deleteEdbIds).Exec()
		err = o.Exec(sql, chartInfo.ChartInfoId, deleteEdbIds).Error
		if err != nil {
			err = fmt.Errorf("删除原先的指标失败：%v", err)
			return
		}
	}
	// 图表指标信息入库
	updateIds := make([]int, 0)
	var edbInfoIdArrStr []string
	for _, item := range list {
		err = addChartEdbAndData(o, item.ChartEdb, item.DataList)
		if err != nil {
			return
		}
		updateIds = append(updateIds, item.ChartEdb.ExcelChartEdbId)
		edbInfoIdArrStr = append(edbInfoIdArrStr, strconv.Itoa(item.ChartEdb.ExcelChartEdbId))
	}

	//新增图表
	chartInfoId := chartInfo.ChartInfoId
	if chartInfo.ChartInfoId <= 0 {
		//lastId, e := o.Insert(chartInfo)
		err = o.Create(&chartInfo).Error
		if err != nil {
			err = fmt.Errorf("新增图表失败，AddChartEdbAndData: %v", e)
			return
		}
		//chartInfoId = int(lastId)
		chartInfoId = chartInfo.ChartInfoId
	} else {
		//_, err = o.Update(chartInfo)
		err = o.Save(&chartInfo).Error
		if err != nil {
			err = fmt.Errorf("更新图表失败，AddChartEdbAndData: %v", e)
			return
		}
	}

	//更新图表id
	//sql := `update excel_chart_edb set chart_info_id = ? where excel_chart_edb_id in (` + utils.GetOrmInReplace(len(updateIds)) + `) and chart_info_id=0`
	sql := `update excel_chart_edb set chart_info_id = ? where excel_chart_edb_id in ? and chart_info_id=0`
	//_, err = o.Raw(sql, chartInfoId, updateIds).Exec()
	err = o.Exec(sql, chartInfoId, updateIds).Error
	if err != nil {
		err = fmt.Errorf("更新图表id失败，AddChartEdbAndData: %v", err)
		return
	}

	if len(edbInfoIdArrStr) > 0 {
		edbInfoIdStr := strings.Join(edbInfoIdArrStr, ",")
		//更新图表关联的指标id
		sql = `update chart_info set edb_info_ids = ? where chart_info_id = ?`
		//_, err = o.Raw(sql, edbInfoIdStr, chartInfoId).Exec()
		err = o.Exec(sql, edbInfoIdStr, chartInfoId).Error
	}
	return
}

// func addChartEdbAndData(o orm.TxOrmer, chartEdb *ExcelChartEdb, dataList []*ExcelChartData) (err error) {
func addChartEdbAndData(o *gorm.DB, chartEdb *ExcelChartEdb, dataList []*ExcelChartData) (err error) {
	// 图表指标信息入库
	excelChartEdbId := chartEdb.ExcelChartEdbId
	if chartEdb.ExcelChartEdbId <= 0 {
		//lastId, e := o.Insert(chartEdb)
		e := o.Create(&chartEdb).Error
		if e != nil {
			err = fmt.Errorf("新增指标失败，addChartEdbAndData: %v", e)
			return
		}
		//excelChartEdbId = int(lastId)
		excelChartEdbId = chartEdb.ExcelChartEdbId
	} else {
		//_, e := o.Update(chartEdb)
		e := o.Save(&chartEdb).Error
		if e != nil {
			err = fmt.Errorf("更新指标失败，addChartEdbAndData: %v", e)
			return
		}
		//如果有数据则删除所有的数据
		sql := `delete from excel_chart_data where excel_chart_edb_id = ?`
		//_, err = o.Raw(sql, excelChartEdbId).Exec()
		err = o.Exec(sql, excelChartEdbId).Error
		if err != nil {
			return
		}
	}

	chartEdb.ExcelChartEdbId = excelChartEdbId

	// 图表指标数据入库
	addList := make([]*ExcelChartData, 0)
	if len(dataList) > 0 {
		for _, v := range dataList {
			chartData := &ExcelChartData{
				ExcelInfoId:     chartEdb.ExcelInfoId,
				ExcelChartEdbId: chartEdb.ExcelChartEdbId,
				ChartInfoId:     chartEdb.ChartInfoId,
				DataTime:        v.DataTime,
				Value:           v.Value,
				DataTimestamp:   v.DataTimestamp,
				ModifyTime:      time.Now(),
				CreateTime:      time.Now(),
			}
			addList = append(addList, chartData)
			// data信息入库
			if len(addList) > 1000 {
				//_, err = o.InsertMulti(len(addList), addList)
				err = o.CreateInBatches(addList, len(addList)).Error
				if err != nil {
					return
				}
				addList = addList[:0]
			}
		}
	}

	// data信息入库
	if len(addList) > 0 {
		//_, err = o.InsertMulti(len(addList), addList)
		err = o.CreateInBatches(addList, len(addList)).Error
		if err != nil {
			return
		}
	}
	return
}

func GetExcelChartEdbMappingByExcelInfoId(excelInfoId int) (list []*ExcelChartEdb, err error) {
	//o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *
             FROM excel_chart_edb 
			 WHERE excel_info_id=? 
             ORDER BY chart_info_id asc, excel_chart_edb_id ASC `
	//_, err = o.Raw(sql, excelInfoId).QueryRows(&list)
	err = global.DbMap[utils.DbNameIndex].Raw(sql, excelInfoId).Find(&list).Error
	return
}

func GetExcelChartEdbMappingByExcelInfoIds(excelInfoIds []int) (list []*ExcelChartEdb, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := ` SELECT *
	//         FROM excel_chart_edb
	//		 WHERE excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `)`
	sql := ` SELECT *
             FROM excel_chart_edb 
			 WHERE excel_info_id in ?`
	//_, err = o.Raw(sql, excelInfoIds).QueryRows(&list)
	err = global.DbMap[utils.DbNameIndex].Raw(sql, excelInfoIds).Find(&list).Error
	return
}

func GetExcelChartEdbById(id int) (item *ExcelChartEdb, err error) {
	//o := orm.NewOrmUsingDB("data")
	sql := ` SELECT * FROM excel_chart_edb WHERE excel_chart_edb_id=? `
	//err = o.Raw(sql, id).QueryRow(&item)
	err = global.DbMap[utils.DbNameIndex].Raw(sql, id).First(&item).Error
	return
}

func GetExcelChartEdbMappingByChartInfoId(chartInfoId int) (list []*ExcelChartEdb, err error) {
	//o := orm.NewOrmUsingDB("data")
	sql := ` SELECT *
             FROM excel_chart_edb 
			 WHERE chart_info_id=? 
             ORDER BY excel_chart_edb_id ASC `
	//_, err = o.Raw(sql, chartInfoId).QueryRows(&list)
	err = global.DbMap[utils.DbNameIndex].Raw(sql, chartInfoId).Find(&list).Error
	return
}

func GetExcelInfoByChartInfoId(chartInfoId int) (item *ExcelInfo, err error) {
	//o := orm.NewOrmUsingDB("data")
	sql := ` SELECT i.*
             FROM excel_chart_edb e left join excel_info i on e.excel_info_id=i.excel_info_id
			 WHERE e.chart_info_id=? limit 1`
	//err = o.Raw(sql, chartInfoId).QueryRow(&item)
	err = global.DbMap[utils.DbNameIndex].Raw(sql, chartInfoId).First(&item).Error
	return
}

// 同时删除指标和指标数据
func DeleteExcelChartEdbAndData(excelInfoIds []int, chartInfoIds []int) (err error) {
	//o, err := orm.NewOrmUsingDB("data").Begin()
	o := global.DbMap[utils.DbNameIndex].Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = o.Rollback()
		} else {
			_ = o.Commit()
		}
	}()
	// 把对应的表格状态改成删除状态
	//更新图表id
	//sql := `update excel_info set is_delete = 1, modify_time = ? where excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `)`
	sql := `update excel_info set is_delete = 1, modify_time = ? where excel_info_id in ?`
	//_, err = o.Raw(sql, time.Now(), excelInfoIds).Exec()
	err = o.Exec(sql, time.Now(), excelInfoIds).Error
	if err != nil {
		err = fmt.Errorf("更新图表id失败，AddChartEdbAndData: %v", err)
		return
	}
	// 把删除图表状态
	if len(chartInfoIds) > 0 {
		//sql := `DELETE FROM chart_info WHERE  chart_info_id in (` + utils.GetOrmInReplace(len(chartInfoIds)) + `)`
		sql := `DELETE FROM chart_info WHERE  chart_info_id in ?`
		//_, err = o.Raw(sql, chartInfoIds).Exec()
		err = o.Exec(sql, chartInfoIds).Error
		if err != nil {
			err = fmt.Errorf("删除原先的指标失败：%v", err)
			return
		}

		// todo 如果加入到我的图库中，则删除我的图库中的数据
	}
	// 删除原先的绑定的指标
	//sql = `DELETE FROM excel_chart_edb WHERE  excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `)`
	//_, err = o.Raw(sql, excelInfoIds).Exec()
	sql = `DELETE FROM excel_chart_edb WHERE  excel_info_id in ?`
	err = o.Exec(sql, excelInfoIds).Error
	if err != nil {
		err = fmt.Errorf("删除原先的指标失败：%v", err)
		return
	}
	// 删除指标数据
	//sql = `DELETE FROM excel_chart_data WHERE  excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `)`
	//_, err = o.Raw(sql, excelInfoIds).Exec()
	sql = `DELETE FROM excel_chart_data WHERE  excel_info_id in ?`
	err = o.Exec(sql, excelInfoIds).Error
	if err != nil {
		err = fmt.Errorf("删除原先的指标失败：%v", err)
		return
	}
	return
}

// 删除平衡表中的指标和数据
func DeleteBalanceExcelChartInfoAndData(chartInfoId int) (err error) {
	o := orm.NewOrmUsingDB("data")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	sql := ` DELETE FROM chart_info WHERE chart_info_id = ? `
	_, err = to.Raw(sql, chartInfoId).Exec()
	if err != nil {
		err = fmt.Errorf("删除平衡表图表失败 %s", err.Error())
		return
	}
	sql = ` DELETE FROM  excel_chart_edb WHERE chart_info_id = ? `
	_, err = to.Raw(sql, chartInfoId).Exec()
	if err != nil {
		err = fmt.Errorf("删除平衡表图表指标失败 %s", err.Error())
		return
	}
	// 删除表格里的数据
	sql = ` DELETE FROM  excel_chart_data WHERE chart_info_id = ? `
	_, err = to.Raw(sql, chartInfoId).Exec()
	if err != nil {
		err = fmt.Errorf("删除平衡表图表指标失败 %s", err.Error())
		return
	}
	return
}