eta_api/models/data_manage/excel/excel_chart_edb.go

package excel

import (
	"eta_gn/eta_api/global"
	"eta_gn/eta_api/models/data_manage"
	"eta_gn/eta_api/utils"
	"fmt"
	"gorm.io/gorm"
	"strconv"
	"strings"
	"time"
)

type ExcelChartEdb struct {
	ExcelChartEdbId int       `orm:"column(excel_chart_edb_id);pk" gorm:"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.DmSQL["data"].Create(e).Error
	return
}

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

// 删除
func (e *ExcelChartEdb) Delete() (err error) {
	//o := orm.NewOrmUsingDB("data")
	//_, err = o.Delete(e)
	sql := ` DELETE FROM excel_chart_edb WHERE excel_chart_edb_id=? `
	err = global.DmSQL["data"].Exec(sql, e.ExcelChartEdbId).Error
	return
}

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

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

	to := global.DmSQL["data"].Begin()

	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.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)) + `)`
		//_, err = o.Raw(sql, chartInfo.ChartInfoId, deleteEdbIds).Exec()
		//if err != nil {
		//	err = fmt.Errorf("删除原先的指标失败：%v", err)
		//	return
		//}

		sql := `DELETE FROM excel_chart_edb WHERE chart_info_id = ? AND excel_chart_edb_id in (` + utils.GetOrmInReplace(len(deleteEdbIds)) + `)`
		err = to.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(to, 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)
		//if e != nil {
		//	err = fmt.Errorf("新增图表失败，AddChartEdbAndData: %v", e)
		//	return
		//}
		//chartInfoId = int(lastId)
		e := to.Create(chartInfo).Error
		if e != nil {
			err = fmt.Errorf("新增图表失败，AddChartEdbAndData: %v", e)
			return
		}
		chartInfoId = chartInfo.ChartInfoId
	} else {
		//_, err = o.Update(chartInfo)
		//if err != nil {
		//	err = fmt.Errorf("更新图表失败，AddChartEdbAndData: %v", e)
		//	return
		//}

		err = to.Updates(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`
	//_, err = o.Raw(sql, chartInfoId, updateIds).Exec()
	//if err != nil {
	//	err = fmt.Errorf("更新图表id失败，AddChartEdbAndData: %v", err)
	//	return
	//}

	sql := `update excel_chart_edb set chart_info_id = ? where excel_chart_edb_id in (` + utils.GetOrmInReplace(len(updateIds)) + `) and chart_info_id=0`
	err = to.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()

		sql = `update chart_info set edb_info_ids = ? where chart_info_id = ?`
		err = to.Exec(sql, edbInfoIdStr, chartInfoId).Error
	}

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

func addChartEdbAndData(o *gorm.DB, chartEdb *ExcelChartEdb, dataList []*ExcelChartData) (err error) {
	// 图表指标信息入库
	excelChartEdbId := chartEdb.ExcelChartEdbId
	if chartEdb.ExcelChartEdbId <= 0 {
		e := o.Create(chartEdb).Error
		if e != nil {
			err = fmt.Errorf("新增指标失败，addChartEdbAndData: %v", e)
			return
		}
		excelChartEdbId = chartEdb.ExcelChartEdbId
	} else {
		//_, e := o.Update(chartEdb)
		//if e != nil {
		//	err = fmt.Errorf("更新指标失败，addChartEdbAndData: %v", e)
		//	return
		//}

		e := o.Updates(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()
		//if err != nil {
		//	return
		//}

		sql := `delete from excel_chart_data where excel_chart_edb_id = ?`
		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)
				//if err != nil {
				//	return
				//}
				//addList = addList[:0]

				err = o.CreateInBatches(addList, utils.MultiAddNum).Error
				if err != nil {
					return
				}
				addList = addList[:0]
			}
		}
	}

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

		err = o.CreateInBatches(addList, utils.MultiAddNum).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)

	sql := ` SELECT *
	        FROM excel_chart_edb
			 WHERE excel_info_id=?
	        ORDER BY chart_info_id asc, excel_chart_edb_id ASC `
	err = global.DmSQL["data"].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)) + `)`
	//_, err = o.Raw(sql, excelInfoIds).QueryRows(&list)

	sql := ` SELECT *
	        FROM excel_chart_edb 
			 WHERE excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `)`
	err = global.DmSQL["data"].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)

	sql := ` SELECT * FROM excel_chart_edb WHERE excel_chart_edb_id=? `
	err = global.DmSQL["data"].Raw(sql, id).Find(&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)

	sql := ` SELECT *
	        FROM excel_chart_edb 
			 WHERE chart_info_id=? 
	        ORDER BY excel_chart_edb_id ASC `
	err = global.DmSQL["data"].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)

	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 = global.DmSQL["data"].Raw(sql, chartInfoId).First(&item).Error
	return
}

// 同时删除指标和指标数据
func DeleteExcelChartEdbAndData(excelInfoIds []int, chartInfoIds []int) (err error) {
	//o, err := orm.NewOrmUsingDB("data").Begin()
	//if err != nil {
	//	return
	//}
	//defer func() {
	//	if err != nil {
	//		_ = o.Rollback()
	//	} else {
	//		_ = o.Commit()
	//	}
	//}()

	to := global.DmSQL["data"].Begin()

	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	// 把对应的表格状态改成删除状态
	//更新图表id
	sql := `update excel_info set is_delete = 1, modify_time = ? where excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `)`
	err = to.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)) + `)`
		//_, err = o.Raw(sql, chartInfoIds).Exec()
		//if err != nil {
		//	err = fmt.Errorf("删除原先的指标失败：%v", err)
		//	return
		//}

		sql := `DELETE FROM chart_info WHERE  chart_info_id in (` + utils.GetOrmInReplace(len(chartInfoIds)) + `)`
		err = to.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()
	//if err != nil {
	//	err = fmt.Errorf("删除原先的指标失败：%v", err)
	//	return
	//}

	sql = `DELETE FROM excel_chart_edb WHERE  excel_info_id in (` + utils.GetOrmInReplace(len(excelInfoIds)) + `)`
	err = to.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()
	//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 = to.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()
	//	}
	//}()

	to := global.DmSQL["data"].Begin()

	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 chart_info WHERE chart_info_id=? `
	err = to.Exec(sql, chartInfoId).Error
	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_edb WHERE chart_info_id=? `
	err = to.Exec(sql, chartInfoId).Error
	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
	//}
	sql = ` DELETE FROM  excel_chart_data WHERE chart_info_id=? `
	err = to.Exec(sql, chartInfoId).Error
	if err != nil {
		err = fmt.Errorf("删除平衡表图表指标失败 %s", err.Error())
		return
	}

	return
}

func EditBalanceChartBaseInfoAndEdbEnInfo(req *data_manage.EditChartInfoBaseReq, chartItem *data_manage.ChartInfo, lang 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()
	//	}
	//}()

	to := global.DmSQL["data"].Begin()

	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	updateChartCols := make([]string, 0)
	switch lang {
	case utils.EnLangVersion:
		chartItem.ChartNameEn = req.ChartName
		updateChartCols = append(updateChartCols, "ChartNameEn")
	default:
		chartItem.ChartName = req.ChartName
		updateChartCols = append(updateChartCols, "ChartName")
	}

	if req.ExtraConfig != `` {
		chartItem.ExtraConfig = req.ExtraConfig
		updateChartCols = append(updateChartCols, "ExtraConfig")
	}
	chartItem.ModifyTime = time.Now()
	updateChartCols = append(updateChartCols, "ModifyTime")
	//_, err = to.Update(chartItem, updateChartCols...)
	//if err != nil {
	//	fmt.Println("UPDATE  chart_info Err:", err.Error())
	//	return err
	//}

	err = to.Select(updateChartCols).Updates(chartItem).Error
	if err != nil {
		fmt.Println("UPDATE  chart_info Err:", err.Error())
		return err
	}

	var edbInfoIdArr []string
	for _, v := range req.ChartEdbInfoList {
		edbInfoIdArr = append(edbInfoIdArr, strconv.Itoa(v.EdbInfoId))
		var count int
		//csql := `SELECT COUNT(1) AS count FROM excel_chart_edb WHERE chart_info_id=? AND excel_chart_edb_id=? `
		//err = to.Raw(csql, req.ChartInfoId, v.EdbInfoId).QueryRow(&count)
		//if err != nil {
		//	fmt.Println("QueryRow Err:", err.Error())
		//	return err
		//}

		csql := `SELECT COUNT(1) AS count FROM excel_chart_edb WHERE chart_info_id=? AND excel_chart_edb_id=? `
		err = to.Raw(csql, req.ChartInfoId, v.EdbInfoId).Scan(&count).Error
		if err != nil {
			fmt.Println("QueryRow Err:", err.Error())
			return err
		}

		if count > 0 {
			msql := ` UPDATE excel_chart_edb SET modify_time = NOW() `
			pars := make([]interface{}, 0)
			switch lang {
			case utils.EnLangVersion:
				msql += ` ,edb_name_en = ? `
				pars = append(pars, v.EdbName)
			default:
				msql += ` ,edb_name = ? `
				pars = append(pars, v.EdbName)
			}
			msql += ` WHERE excel_chart_edb_id = ? `
			pars = append(pars, v.EdbInfoId)
			//_, err = to.Raw(msql, pars...).Exec()
			//if err != nil {
			//	fmt.Println("edb_info Err:" + err.Error())
			//	return err
			//}

			err = to.Exec(msql, pars...).Error
			if err != nil {
				fmt.Println("edb_info Err:" + err.Error())
				return err
			}
		}
	}
	return
}

func GetBalanceChartEdbByCondition(condition string, pars []interface{}) (item *ExcelChartEdb, err error) {
	//o := orm.NewOrmUsingDB("data")
	//sql := ` SELECT * FROM excel_chart_edb WHERE 1=1 `
	//if condition != "" {
	//	sql += condition
	//}
	//err = o.Raw(sql, pars).QueryRow(&item)

	sql := ` SELECT * FROM excel_chart_edb WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	err = global.DmSQL["data"].Raw(sql, pars...).First(&item).Error
	return
}