eta_chart_lib/models/data_manage/excel/excel_info.go

package excel

import (
	"eta_gn/eta_chart_lib/global"
	"eta_gn/eta_chart_lib/utils"
	"fmt"
	"time"
)

// ExcelInfo excel表格详情表
//
//	type ExcelInfo struct {
//		ExcelInfoId        int       `orm:"column(excel_info_id);pk"`
//		Source             int       `description:"表格来源，1：excel插件的表格，2：自定义表格，3：混合表格，4：自定义分析，默认：1"`
//		ExcelType          int       `description:"表格类型，1：指标列，2：日期列，默认：1"`
//		ExcelName          string    `description:"表格名称"`
//		UniqueCode         string    `description:"表格唯一编码"`
//		ExcelClassifyId    int       `description:"表格分类id"`
//		SysUserId          int       `description:"操作人id"`
//		SysUserRealName    string    `description:"操作人真实姓名"`
//		Content            string    `description:"表格内容"`
//		ExcelImage         string    `description:"表格图片"`
//		FileUrl            string    `description:"表格下载地址"`
//		Sort               int       `description:"排序字段，数字越小越排前面"`
//		IsDelete           int       `description:"是否删除，0：未删除，1：已删除"`
//		ModifyTime         time.Time `description:"最近修改日期"`
//		CreateTime         time.Time `description:"创建日期"`
//		IsJoinPermission   int       `description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
//		BalanceType        int       `description:"平衡表类型：0 动态表，1静态表"`
//		UpdateUserId       int       `description:"更新人id"`
//		UpdateUserRealName string    `description:"更新人真实姓名"`
//		RelExcelInfoId     int       `description:"平衡表里静态表关联的动态表excel id"`
//		VersionName        string    `description:"静态表版本名称"`
//	}
type ExcelInfo struct {
	ExcelInfoId        int       `gorm:"column:excel_info_id;primaryKey" orm:"column(excel_info_id);pk"`
	Source             int       `gorm:"column:source" description:"表格来源，1：excel插件的表格，2：自定义表格，3：混合表格，4：自定义分析，默认：1"`
	ExcelType          int       `gorm:"column:excel_type" description:"表格类型，1：指标列，2：日期列，默认：1"`
	ExcelName          string    `gorm:"column:excel_name" description:"表格名称"`
	UniqueCode         string    `gorm:"column:unique_code" description:"表格唯一编码"`
	ExcelClassifyId    int       `gorm:"column:excel_classify_id" description:"表格分类id"`
	SysUserId          int       `gorm:"column:sys_user_id" description:"操作人id"`
	SysUserRealName    string    `gorm:"column:sys_user_real_name" description:"操作人真实姓名"`
	Content            string    `gorm:"column:content" description:"表格内容"`
	ExcelImage         string    `gorm:"column:excel_image" description:"表格图片"`
	FileUrl            string    `gorm:"column:file_url" description:"表格下载地址"`
	Sort               int       `gorm:"column:sort" description:"排序字段，数字越小越排前面"`
	IsDelete           int       `gorm:"column:is_delete" description:"是否删除，0：未删除，1：已删除"`
	ModifyTime         time.Time `gorm:"column:modify_time" description:"最近修改日期"`
	CreateTime         time.Time `gorm:"column:create_time" description:"创建日期"`
	IsJoinPermission   int       `gorm:"column:is_join_permission" description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
	BalanceType        int       `gorm:"column:balance_type" description:"平衡表类型：0 动态表，1静态表"`
	UpdateUserId       int       `gorm:"column:update_user_id" description:"更新人id"`
	UpdateUserRealName string    `gorm:"column:update_user_real_name" description:"更新人真实姓名"`
	RelExcelInfoId     int       `gorm:"column:rel_excel_info_id" description:"平衡表里静态表关联的动态表excel id"`
	VersionName        string    `gorm:"column:version_name" description:"静态表版本名称"`
}

// Update 更新 excel表格基础信息
func (excelInfo *ExcelInfo) Update(cols []string) (err error) {
	o := global.DmSQL["data"]
	err = o.Model(excelInfo).Select(cols).Updates(excelInfo).Error
	return
}

//	type MyExcelInfoList struct {
//		ExcelInfoId      int       `orm:"column(excel_info_id);pk"`
//		Source           int       `description:"表格来源，1：excel插件的表格，2：自定义表格，默认：1"`
//		ExcelType        int       `description:"表格类型，1：指标列，2：日期列，默认：1"`
//		ExcelName        string    `description:"表格名称"`
//		UniqueCode       string    `description:"表格唯一编码"`
//		ExcelClassifyId  int       `description:"表格分类id"`
//		SysUserId        int       `description:"操作人id"`
//		SysUserRealName  string    `description:"操作人真实姓名"`
//		ExcelImage       string    `description:"表格图片"`
//		FileUrl          string    `description:"表格下载地址"`
//		Sort             int       `description:"排序字段，数字越小越排前面"`
//		ModifyTime       time.Time `description:"最近修改日期"`
//		CreateTime       time.Time `description:"创建日期"`
//		IsJoinPermission int       `description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
//		HaveOperaAuth    bool      `description:"是否有数据权限"`
//	}
type MyExcelInfoList struct {
	ExcelInfoId      int       `gorm:"column:excel_info_id;primaryKey" orm:"column(excel_info_id);pk"`
	Source           int       `gorm:"column:source" description:"表格来源，1：excel插件的表格，2：自定义表格，默认：1"`
	ExcelType        int       `gorm:"column:excel_type" description:"表格类型，1：指标列，2：日期列，默认：1"`
	ExcelName        string    `gorm:"column:excel_name" description:"表格名称"`
	UniqueCode       string    `gorm:"column:unique_code" description:"表格唯一编码"`
	ExcelClassifyId  int       `gorm:"column:excel_classify_id" description:"表格分类id"`
	SysUserId        int       `gorm:"column:sys_user_id" description:"操作人id"`
	SysUserRealName  string    `gorm:"column:sys_user_real_name" description:"操作人真实姓名"`
	ExcelImage       string    `gorm:"column:excel_image" description:"表格图片"`
	FileUrl          string    `gorm:"column:file_url" description:"表格下载地址"`
	Sort             int       `gorm:"column:sort" description:"排序字段，数字越小越排前面"`
	ModifyTime       time.Time `gorm:"column:modify_time" description:"最近修改日期"`
	CreateTime       time.Time `gorm:"column:create_time" description:"创建日期"`
	IsJoinPermission int       `gorm:"column:is_join_permission" description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
	HaveOperaAuth    bool      `gorm:"column:have_opera_auth" description:"是否有数据权限"`
}

// AddExcelInfo 新增表格
func AddExcelInfo(excelInfo *ExcelInfo, excelEdbMappingList []*ExcelEdbMapping) (err error) {
	o := global.DmSQL["data"].Begin()
	defer func() {
		if err != nil {
			_ = o.Rollback()
		} else {
			_ = o.Commit()
		}
	}()
	// 表格信息入库
	err = o.Create(excelInfo).Error
	if err != nil {
		return
	}

	// excel与指标的关联关系
	dataNum := len(excelEdbMappingList)
	if dataNum > 0 {
		for k, v := range excelEdbMappingList {
			v.ExcelInfoId = excelInfo.ExcelInfoId
			excelEdbMappingList[k] = v
		}
		err = o.CreateInBatches(excelEdbMappingList, utils.MultiAddNum).Error
	}

	return
}

// EditExcelInfo 编辑表格
func EditExcelInfo(excelInfo *ExcelInfo, updateExcelInfoParams []string, excelEdbMappingList []*ExcelEdbMapping) (err error) {
	o := global.DmSQL["data"].Begin()
	defer func() {
		if err != nil {
			_ = o.Rollback()
		} else {
			_ = o.Commit()
		}
	}()

	// ETA表格信息变更
	err = o.Model(excelInfo).Select(updateExcelInfoParams).Updates(excelInfo).Error
	if err != nil {
		return
	}

	// 删除关系表
	sql := `DELETE FROM excel_edb_mapping WHERE excel_info_id=? `
	err = o.Exec(sql, excelInfo.ExcelInfoId).Error
	if err != nil {
		return
	}

	// excel与指标的关联关系
	dataNum := len(excelEdbMappingList)
	if dataNum > 0 {
		for k, v := range excelEdbMappingList {
			v.ExcelInfoId = excelInfo.ExcelInfoId
			excelEdbMappingList[k] = v
		}
		err = o.CreateInBatches(excelEdbMappingList, utils.MultiAddNum).Error
	}

	return
}

// GetExcelInfoAll 获取所有表格列表，用于分类展示
func GetExcelInfoAll() (items []*ExcelClassifyItems, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name,
             unique_code,sys_user_id,sys_user_real_name,is_join_permission
            FROM excel_info where is_delete=0 ORDER BY sort asc,create_time ASC `
	err = o.Raw(sql).Scan(&items).Error
	return
}

// GetNoContentExcelInfoAll 获取不含content的表格列表 用于分类展示
func GetNoContentExcelInfoAll(source, userId int) (items []*ExcelClassifyItems, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name,
             unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission
            FROM excel_info where is_delete=0 AND source = ?  `

	pars := []interface{}{source}

	if userId > 0 {
		sql += ` AND sys_user_id = ? `
		pars = append(pars, userId)
	}
	sql += `  ORDER BY sort asc,excel_info_id desc `
	err = o.Raw(sql, pars...).Scan(&items).Error
	return
}

// GetAllExcelInfoBySource 根据来源获取包含content的表格列表
func GetAllExcelInfoBySource(source int) (items []*ExcelInfo, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info where is_delete=0  AND source = ?  ORDER BY sort asc,create_time desc `
	err = o.Raw(sql, source).Scan(&items).Error
	return
}

// GetExcelInfoById 根据id 获取eta表格详情
func GetExcelInfoById(excelInfoId int) (item *ExcelInfo, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
	err = o.Raw(sql, excelInfoId).First(&item).Error
	return
}

// GetExcelInfoByUnicode 编码获取表格
func GetExcelInfoByUnicode(unicode string) (item *ExcelInfo, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE unique_code = ? AND is_delete = 0 `
	err = o.Raw(sql, unicode).First(&item).Error
	return
}

func GetExcelInfoViewById(excelInfoId int) (item *ExcelInfoView, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
	err = o.Raw(sql, excelInfoId).First(&item).Error
	return
}

func GetExcelInfoCountByCondition(condition string, pars []interface{}) (count int, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars...).Scan(&count).Error
	return
}

func GetNoContentExcelInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (items []*ExcelClassifyItems, err error) {
	o := global.DmSQL["data"]
	sql := `SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name,
             unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission FROM excel_info WHERE 1=1 `
	if condition != "" {
		sql += condition
	}

	sql += ` AND is_delete=0 ORDER BY excel_info_id DESC LIMIT ?,? `
	pars = append(pars, startSize, pageSize)
	err = o.Raw(sql, pars...).Scan(&items).Error
	return
}

func GetExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars...).First(&item).Error
	return
}

// GetNextExcelInfoByCondition 根据条件获取下一个表格
func GetNextExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
	if condition != "" {
		sql += condition
	}
	sql += " ORDER BY sort asc , create_time desc LIMIT 1 "
	err = o.Raw(sql, pars...).First(&item).Error
	return
}

// GetNextExcelInfo 根据分类id获取下一个excel表格
func GetNextExcelInfo(classifyId, classifySort, source int) (item *ExcelInfo, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT b.* FROM excel_classify AS a
			INNER JOIN excel_info AS b ON a.excel_classify_id=b.excel_classify_id
			WHERE (a.sort>? OR (a.sort=? and a.excel_classify_id>?) ) AND a.is_delete=0 AND b.is_delete=0
			AND a.source = ? AND b.source = ? 
			ORDER BY a.sort ASC,b.sort asc,b.create_time desc
			LIMIT 1 `
	err = o.Raw(sql, classifySort, classifySort, classifyId, source, source).First(&item).Error
	return
}

// EditExcelInfoImage 修改excel表格的图片
func EditExcelInfoImage(excelInfoId int, imageUrl string) (err error) {
	o := global.DmSQL["data"]

	sql := ` UPDATE  excel_info SET excel_image=?, modify_time = NOW() WHERE excel_info_id = ? AND is_delete=0 `
	err = o.Exec(sql, imageUrl, excelInfoId).Error
	if err != nil {
		fmt.Println("EditExcelInfoImage Err:", err.Error())
		return err
	}
	return
}

// GetExcelInfoByUniqueCode 根据unique_code来获取excel表格详情
func GetExcelInfoByUniqueCode(uniqueCode string) (item *ExcelInfo, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE unique_code=? AND is_delete=0 `
	err = o.Raw(sql, uniqueCode).First(&item).Error
	return
}

// GetFirstExcelInfoByClassifyId 获取当前分类下，且排序数相同 的排序第一条的数据
func GetFirstExcelInfoByClassifyId(classifyId int) (item *ExcelInfo, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE excel_classify_id=? AND is_delete=0 order by sort asc,excel_info_id desc limit 1`
	err = o.Raw(sql, classifyId).First(&item).Error
	return
}

// UpdateExcelInfoSortByClassifyId 根据表格id更新排序
func UpdateExcelInfoSortByClassifyId(classifyId, nowSort, prevExcelInfoId int, updateSort string, source int) (err error) {
	o := global.DmSQL["data"]
	sql := ` update excel_info set sort = ` + updateSort + ` WHERE excel_classify_id=? AND source=? AND is_delete=0 AND ( sort > ? `
	// todo 前一个兄弟节点后移
	if prevExcelInfoId > 0 {
		sql += ` or (excel_info_id < ` + fmt.Sprint(prevExcelInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `)`
	}
	sql += `)`
	err = o.Exec(sql, classifyId, source, nowSort).Error
	return
}

// AddExcelInfo 新增表格
// func AddExcelInfo(excelInfo *ExcelInfo, excelEdbMappingList []*ExcelEdbMapping) (err error) {
// 	o, err := orm.NewOrmUsingDB("data").Begin()
// 	if err != nil {
// 		return
// 	}
// 	defer func() {
// 		if err != nil {
// 			_ = o.Rollback()
// 		} else {
// 			_ = o.Commit()
// 		}
// 	}()
// 	// 表格信息入库
// 	lastId, err := o.Insert(excelInfo)
// 	if err != nil {
// 		return
// 	}
// 	excelInfo.ExcelInfoId = int(lastId)

// 	// excel与指标的关联关系
// 	dataNum := len(excelEdbMappingList)
// 	if dataNum > 0 {
// 		for k, v := range excelEdbMappingList {
// 			v.ExcelInfoId = excelInfo.ExcelInfoId
// 			excelEdbMappingList[k] = v
// 		}
// 		_, err = o.InsertMulti(dataNum, excelEdbMappingList)
// 	}

// 	return
// }

// // EditExcelInfo 编辑表格
// func EditExcelInfo(excelInfo *ExcelInfo, updateExcelInfoParams []string, excelEdbMappingList []*ExcelEdbMapping) (err error) {
// 	o, err := orm.NewOrmUsingDB("data").Begin()
// 	if err != nil {
// 		return
// 	}
// 	defer func() {
// 		if err != nil {
// 			_ = o.Rollback()
// 		} else {
// 			_ = o.Commit()
// 		}
// 	}()

// 	// ETA表格信息变更
// 	_, err = o.Update(excelInfo, updateExcelInfoParams...)
// 	if err != nil {
// 		return
// 	}

// 	// 删除关系表
// 	sql := `DELETE FROM excel_edb_mapping WHERE excel_info_id=? `
// 	_, err = o.Raw(sql, excelInfo.ExcelInfoId).Exec()

// 	// excel与指标的关联关系
// 	dataNum := len(excelEdbMappingList)
// 	if dataNum > 0 {
// 		for k, v := range excelEdbMappingList {
// 			v.ExcelInfoId = excelInfo.ExcelInfoId
// 			excelEdbMappingList[k] = v
// 		}
// 		_, err = o.InsertMulti(dataNum, excelEdbMappingList)
// 	}

// 	return
// }

// GetExcelInfoAll 获取所有表格列表，用于分类展示
// func GetExcelInfoAll() (items []*ExcelClassifyItems, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name,
//              unique_code,sys_user_id,sys_user_real_name,is_join_permission
//             FROM excel_info where is_delete=0 ORDER BY sort asc,create_time ASC `
// 	_, err = o.Raw(sql).QueryRows(&items)
// 	return
// }

// // GetNoContentExcelInfoAll 获取不含content的表格列表 用于分类展示
// func GetNoContentExcelInfoAll(source, userId int) (items []*ExcelClassifyItems, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name,
//              unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission
//             FROM excel_info where is_delete=0 AND source = ?  `

// 	pars := []interface{}{source}

// 	if userId > 0 {
// 		sql += ` AND sys_user_id = ? `
// 		pars = append(pars, userId)
// 	}
// 	sql += `  ORDER BY sort asc,excel_info_id desc `
// 	_, err = o.Raw(sql, pars...).QueryRows(&items)
// 	return
// }

// // GetAllExcelInfoBySource 根据来源获取包含content的表格列表
// func GetAllExcelInfoBySource(source int) (items []*ExcelInfo, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info where is_delete=0  AND source = ?  ORDER BY sort asc,create_time desc `
// 	_, err = o.Raw(sql, source).QueryRows(&items)
// 	return
// }

// // GetExcelInfoById 根据id 获取eta表格详情
// func GetExcelInfoById(excelInfoId int) (item *ExcelInfo, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
// 	err = o.Raw(sql, excelInfoId).QueryRow(&item)
// 	return
// }

// // GetExcelInfoByUnicode 编码获取表格
// func GetExcelInfoByUnicode(unicode string) (item *ExcelInfo, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE unique_code = ? AND is_delete = 0 `
// 	err = o.Raw(sql, unicode).QueryRow(&item)
// 	return
// }

// func GetExcelInfoViewById(excelInfoId int) (item *ExcelInfoView, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
// 	err = o.Raw(sql, excelInfoId).QueryRow(&item)
// 	return
// }

// func GetExcelInfoCountByCondition(condition string, pars []interface{}) (count int, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 `
// 	if condition != "" {
// 		sql += condition
// 	}
// 	err = o.Raw(sql, pars).QueryRow(&count)
// 	return
// }

// func GetNoContentExcelInfoListByCondition(condition string, pars []interface{}, startSize, pageSize int) (items []*ExcelClassifyItems, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := `SELECT excel_info_id,excel_classify_id,excel_name AS excel_classify_name,
//              unique_code,sys_user_id,sys_user_real_name,sort,is_join_permission FROM excel_info WHERE 1=1 `
// 	if condition != "" {
// 		sql += condition
// 	}

// 	sql += ` AND is_delete=0 ORDER BY excel_info_id DESC LIMIT ?,? `
// 	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
// 	return
// }

// func GetExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
// 	if condition != "" {
// 		sql += condition
// 	}
// 	err = o.Raw(sql, pars).QueryRow(&item)
// 	return
// }

// // GetNextExcelInfoByCondition 根据条件获取下一个表格
// func GetNextExcelInfoByCondition(condition string, pars []interface{}) (item *ExcelInfo, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE 1=1 AND is_delete=0 `
// 	if condition != "" {
// 		sql += condition
// 	}
// 	sql += " ORDER BY sort asc , create_time desc LIMIT 1 "
// 	err = o.Raw(sql, pars).QueryRow(&item)
// 	return
// }

// // GetNextExcelInfo 根据分类id获取下一个excel表格
// func GetNextExcelInfo(classifyId, classifySort, source int) (item *ExcelInfo, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT b.* FROM excel_classify AS a
// 			INNER JOIN excel_info AS b ON a.excel_classify_id=b.excel_classify_id
// 			WHERE (a.sort>? OR (a.sort=? and a.excel_classify_id>?) ) AND a.is_delete=0 AND b.is_delete=0
// 			AND a.source = ? AND b.source = ?
// 			ORDER BY a.sort ASC,b.sort asc,b.create_time desc
// 			LIMIT 1 `
// 	err = o.Raw(sql, classifySort, classifySort, classifyId, source, source).QueryRow(&item)
// 	return
// }

// // EditExcelInfoImage 修改excel表格的图片
// func EditExcelInfoImage(excelInfoId int, imageUrl string) (err error) {
// 	o := orm.NewOrmUsingDB("data")

// 	sql := ` UPDATE  excel_info SET excel_image=?, modify_time = NOW() WHERE excel_info_id = ? AND is_delete=0 `
// 	_, err = o.Raw(sql, imageUrl, excelInfoId).Exec()
// 	if err != nil {
// 		fmt.Println("EditExcelInfoImage Err:", err.Error())
// 		return err
// 	}

// 	return
// }

// // GetExcelInfoByUniqueCode 根据unique_code来获取excel表格详情
// func GetExcelInfoByUniqueCode(uniqueCode string) (item *ExcelInfo, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE unique_code=? AND is_delete=0 `
// 	err = o.Raw(sql, uniqueCode).QueryRow(&item)
// 	return
// }

// // GetFirstExcelInfoByClassifyId 获取当前分类下，且排序数相同 的排序第一条的数据
// func GetFirstExcelInfoByClassifyId(classifyId int) (item *ExcelInfo, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE excel_classify_id=? AND is_delete=0 order by sort asc,excel_info_id desc limit 1`
// 	err = o.Raw(sql, classifyId).QueryRow(&item)
// 	return
// }

// // UpdateExcelInfoSortByClassifyId 根据表格id更新排序
// func UpdateExcelInfoSortByClassifyId(classifyId, nowSort, prevExcelInfoId int, updateSort string, source int) (err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` update excel_info set sort = ` + updateSort + ` WHERE excel_classify_id=? AND source=? AND is_delete=0 AND ( sort > ? `
// 	// todo 前一个兄弟节点后移
// 	if prevExcelInfoId > 0 {
// 		sql += ` or (excel_info_id < ` + fmt.Sprint(prevExcelInfoId) + ` and sort = ` + fmt.Sprint(nowSort) + `)`
// 	}
// 	sql += `)`
// 	_, err = o.Raw(sql, classifyId, source, nowSort).Exec()
// 	return
// }

//	type ExcelInfoView struct {
//		ExcelInfoId       int    `orm:"column(excel_info_id);pk"`
//		ExcelName         string `description:"来源名称"`
//		ExcelClassifyId   int    `description:"表格分类id"`
//		ExcelClassifyName string `description:"表格名称"`
//		SysUserId         int
//		SysUserRealName   string
//		UniqueCode        string `description:"表格唯一编码"`
//		CreateTime        time.Time
//		ModifyTime        time.Time
//		DateType          int    `description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间"`
//		StartDate         string `description:"自定义开始日期"`
//		EndDate           string `description:"自定义结束日期"`
//		IsSetName         int    `description:"设置名称"`
//		EdbInfoIds        string `description:"指标id"`
//		ExcelType         int    `description:"生成样式:1:曲线图,2:季节性图"`
//		Calendar          string `description:"公历/农历"`
//		SeasonStartDate   string `description:"季节性图开始日期"`
//		SeasonEndDate     string `description:"季节性图开始日期"`
//		ExcelImage        string `description:"表格图片"`
//		Sort              int    `description:"排序字段，数字越小越排前面"`
//		IsAdd             bool   `description:"true:已加入我的图库,false:未加入我的图库"`
//		MyExcelId         int
//		MyExcelClassifyId string `description:"我的表格分类，多个用逗号隔开"`
//		ExcelClassify     []*ExcelClassifyView
//		EdbEndDate        string `description:"指标最新更新日期"`
//		LeftMin           string `description:"表格左侧最小值"`
//		LeftMax           string `description:"表格左侧最大值"`
//		RightMin          string `description:"表格右侧最小值"`
//		RightMax          string `description:"表格右侧最大值"`
//	}

type ExcelInfoView struct {
	ExcelInfoId       int       `gorm:"column:excel_info_id;primaryKey" orm:"column(excel_info_id);pk"`
	ExcelName         string    `gorm:"column:excel_name" description:"来源名称"`
	ExcelClassifyId   int       `gorm:"column:excel_classify_id" description:"表格分类id"`
	ExcelClassifyName string    `gorm:"column:excel_classify_name" description:"表格名称"`
	SysUserId         int       `gorm:"column:sys_user_id"`
	SysUserRealName   string    `gorm:"column:sys_user_real_name"`
	UniqueCode        string    `gorm:"column:unique_code" description:"表格唯一编码"`
	CreateTime        time.Time `gorm:"column:create_time"`
	ModifyTime        time.Time `gorm:"column:modify_time"`
	DateType          int       `gorm:"column:date_type"  description:"日期类型:1:00年至今,2:10年至今,3:15年至今，4：年初至今，5:自定义时间"`
	StartDate         string    `gorm:"column:start_date"  description:"自定义开始日期"`
	EndDate           string    `gorm:"column:end_date"  description:"自定义结束日期"`
	IsSetName         int       `gorm:"column:is_set_name"  description:"设置名称"`
	EdbInfoIds        string    `gorm:"column:edb_info_ids"  description:"指标id"`
	ExcelType         int       `gorm:"column:excel_type"  description:"生成样式:1:曲线图,2:季节性图"`
	Calendar          string    `gorm:"column:calendar"  description:"公历/农历"`
	SeasonStartDate   string    `gorm:"column:season_start_date" description:"季节性图开始日期"`
	SeasonEndDate     string    `gorm:"column:season_end_date" description:"季节性图结束日期"`
	ExcelImage        string    `gorm:"column:excel_image" description:"表格图片"`
	Sort              int       `gorm:"column:sort" description:"排序字段，数字越小越排前面"`
	IsAdd             bool      `gorm:"column:is_add" description:"true:已加入我的图库,false:未加入我的图库"`
	MyExcelId         int       `gorm:"column:my_excel_id"`
	MyExcelClassifyId string    `gorm:"column:my_excel_classify_id" description:"我的表格分类，多个用逗号隔开"`
	ExcelClassify     []*ExcelClassifyView
	EdbEndDate        string `gorm:"column:edb_end_date" description:"指标最新更新日期"`
	LeftMin           string `gorm:"column:left_min" description:"表格左侧最小值"`
	LeftMax           string `gorm:"column:left_max" description:"表格左侧最大值"`
	RightMin          string `gorm:"column:right_min" description:"表格右侧最小值"`
	RightMax          string `gorm:"column:right_max" description:"表格右侧最大值"`
}

// GetExcelInfoByClassifyIdAndName 根据分类id和表格名获取表格信息
func GetExcelInfoByClassifyIdAndName(classifyId int, excelName string) (item *ExcelInfo, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE excel_classify_id = ? and excel_name=? AND is_delete=0 `
	err = o.Raw(sql, classifyId, excelName).First(&item).Error
	return
}

// GetNoContentExcelListByCondition 获取没有content的excel表格列表数据
func GetNoContentExcelListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyExcelInfoList, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission
FROM excel_info WHERE 1=1 AND is_delete=0 `
	if condition != "" {
		sql += condition
	}
	//sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
	sql += " ORDER BY create_time DESC LIMIT ?,? "
	err = o.Raw(sql, pars, startSize, pageSize).Scan(&item).Error
	return
}

func GetExcelListCountByCondition(condition string, pars []interface{}) (count int, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 `
	if condition != "" {
		sql += condition
	}
	err = o.Raw(sql, pars).Scan(&count).Error
	return
}

// GetExcelViewInfoByExcelInfoId 根据excelInfoId 获取ETA表格详情
func GetExcelViewInfoByExcelInfoId(excelInfoId int) (item *MyExcelInfoList, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission FROM excel_info WHERE excel_info_id = ? AND is_delete=0 `
	err = o.Raw(sql, excelInfoId).First(&item).Error
	return
}

// GetExcelInfoCountByClassifyId 根据分类id获取名下表格数量
func GetExcelInfoCountByClassifyId(classifyId int) (total int64, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT count(1) total FROM excel_info WHERE excel_classify_id = ? AND is_delete=0 `
	err = o.Raw(sql, classifyId).Scan(&total).Error
	return
}

// UpdateExcelInfoClassifyId 更改表格分类
func UpdateExcelInfoClassifyId(classifyId, excelInfoId int) (err error) {
	o := global.DmSQL["data"]
	sql := ` update excel_info set excel_classify_id = ? WHERE excel_info_id=? `
	err = o.Exec(sql, classifyId, excelInfoId).Error

	return
}

// GetNoContentExcelInfoByName 根据名称 获取eta表格详情
func GetNoContentExcelInfoByName(excelName string, source int) (item *MyExcelInfoList, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission 
 FROM excel_info WHERE excel_name = ? AND source = ? AND is_delete=0 `
	err = o.Raw(sql, excelName, source).First(&item).Error

	return
}

// GetNoContentExcelInfoByUniqueCode 根据unique_code来获取excel表格详情
func GetNoContentExcelInfoByUniqueCode(uniqueCode string) (item *MyExcelInfoList, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission 
 FROM excel_info WHERE unique_code=? AND is_delete=0 `
	err = o.Raw(sql, uniqueCode).First(&item).Error
	return
}

// GetNoContentExcelInfoByExcelId 根据表格id来获取excel表格详情
func GetNoContentExcelInfoByExcelId(excelInfoId int) (item *MyExcelInfoList, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission 
 FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
	err = o.Raw(sql, excelInfoId).First(&item).Error
	return
}

// AddExcelInfoAndSheet 新增excel
func AddExcelInfoAndSheet(excelInfo *ExcelInfo, sheetParamsList []AddExcelSheetParams) (err error) {
	o := global.DmSQL["data"].Begin()
	defer func() {
		if err != nil {
			_ = o.Rollback()
		} else {
			_ = o.Commit()
		}
	}()

	// 表格信息入库
	err = o.Create(excelInfo).Error
	if err != nil {
		return
	}

	// sheet信息入库
	for _, sheetInfo := range sheetParamsList {
		dataNum := len(sheetInfo.DataList)

		//sheet信息入库
		excelSheetInfo := &ExcelSheet{
			ExcelSheetId: 0,
			ExcelInfoId:  excelInfo.ExcelInfoId,
			SheetName:    sheetInfo.SheetName,
			PageNum:      dataNum,
			Index:        sheetInfo.Index,
			Sort:         sheetInfo.Sort,
			Config:       sheetInfo.Config,
			CalcChain:    sheetInfo.CalcChain,
			ModifyTime:   time.Now(),
			CreateTime:   time.Now(),
		}
		tmpErr := o.Create(excelSheetInfo).Error
		if tmpErr != nil {
			err = tmpErr
			return
		}

		// data信息入库
		if dataNum > 0 {
			for k, _ := range sheetInfo.DataList {
				sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId
				sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId
			}
			tmpErr = o.CreateInBatches(sheetInfo.DataList, utils.MultiAddNum).Error
			if tmpErr != nil {
				err = tmpErr
				return
			}
		}
	}

	return
}

// SaveExcelInfoAndSheet 编辑保存
func SaveExcelInfoAndSheet(excelInfo *ExcelInfo, updateExcelInfoParam []string, sheetParamsList []AddExcelSheetParams) (err error) {
	o := global.DmSQL["data"].Begin()
	defer func() {
		if err != nil {
			_ = o.Rollback()
		} else {
			_ = o.Commit()
		}
	}()

	// 表格信息入库
	err = o.Model(excelInfo).Select(updateExcelInfoParam).Updates(excelInfo).Error
	if err != nil {
		return
	}

	// 先删除历史的sheet信息
	sql := `DELETE FROM excel_sheet WHERE excel_info_id = ?`
	err = o.Exec(sql, excelInfo.ExcelInfoId).Error
	if err != nil {
		return
	}

	// 再删除历史sheet中的cell data信息
	sql = `DELETE FROM excel_sheet_data WHERE excel_info_id = ?`
	err = o.Exec(sql, excelInfo.ExcelInfoId).Error
	if err != nil {
		return
	}

	// sheet信息入库
	for _, sheetInfo := range sheetParamsList {
		dataNum := len(sheetInfo.DataList)

		//sheet信息入库
		excelSheetInfo := &ExcelSheet{
			ExcelSheetId: 0,
			ExcelInfoId:  excelInfo.ExcelInfoId,
			SheetName:    sheetInfo.SheetName,
			PageNum:      dataNum,
			Index:        sheetInfo.Index,
			Sort:         sheetInfo.Sort,
			Config:       sheetInfo.Config,
			CalcChain:    sheetInfo.CalcChain,
			ModifyTime:   time.Now(),
			CreateTime:   time.Now(),
		}
		tmpErr := o.Create(excelSheetInfo).Error
		if tmpErr != nil {
			err = tmpErr
			return
		}

		// data信息入库
		if dataNum > 0 {
			for k, _ := range sheetInfo.DataList {
				sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId
				sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId
			}
			tmpErr = o.CreateInBatches(sheetInfo.DataList, utils.MultiAddNum).Error
			if tmpErr != nil {
				err = tmpErr
				return
			}
		}
	}

	return
}

// // GetExcelInfoByClassifyIdAndName 根据分类id和表格名获取表格信息
// func GetExcelInfoByClassifyIdAndName(classifyId int, excelName string) (item *ExcelInfo, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE excel_classify_id = ? and excel_name=? AND is_delete=0 `
// 	err = o.Raw(sql, classifyId, excelName).QueryRow(&item)
// 	return
// }

// // GetNoContentExcelListByCondition 获取没有content的excel表格列表数据
// func GetNoContentExcelListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyExcelInfoList, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission
// FROM excel_info WHERE 1=1 AND is_delete=0 `
// 	if condition != "" {
// 		sql += condition
// 	}
// 	//sql += " ORDER BY sort ASC,chart_info_id DESC LIMIT ?,? "
// 	sql += " ORDER BY create_time DESC LIMIT ?,? "
// 	_, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&item)
// 	return
// }

// func GetExcelListCountByCondition(condition string, pars []interface{}) (count int, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT COUNT(1) AS count FROM excel_info WHERE 1=1 AND is_delete=0 `
// 	if condition != "" {
// 		sql += condition
// 	}
// 	err = o.Raw(sql, pars).QueryRow(&count)
// 	return
// }

// // GetExcelViewInfoByExcelInfoId 根据excelInfoId 获取ETA表格详情
// func GetExcelViewInfoByExcelInfoId(excelInfoId int) (item *MyExcelInfoList, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission FROM excel_info WHERE excel_info_id = ? AND is_delete=0 `
// 	err = o.Raw(sql, excelInfoId).QueryRow(&item)
// 	return
// }

// // GetExcelInfoCountByClassifyId 根据分类id获取名下表格数量
// func GetExcelInfoCountByClassifyId(classifyId int) (total int64, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT count(1) total FROM excel_info WHERE excel_classify_id = ? AND is_delete=0 `
// 	err = o.Raw(sql, classifyId).QueryRow(&total)
// 	return
// }

// // UpdateExcelInfoClassifyId 更改表格分类
// func UpdateExcelInfoClassifyId(classifyId, excelInfoId int) (err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` update excel_info set excel_classify_id = ? WHERE excel_info_id=? `
// 	_, err = o.Raw(sql, classifyId, excelInfoId).Exec()

// 	return
// }

// // GetNoContentExcelInfoByName 根据名称 获取eta表格详情
// func GetNoContentExcelInfoByName(excelName string, source int) (item *MyExcelInfoList, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission
//  FROM excel_info WHERE excel_name = ? AND source = ? AND is_delete=0 `
// 	err = o.Raw(sql, excelName, source).QueryRow(&item)

// 	return
// }

// // GetNoContentExcelInfoByUniqueCode 根据unique_code来获取excel表格详情
// func GetNoContentExcelInfoByUniqueCode(uniqueCode string) (item *MyExcelInfoList, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission
//  FROM excel_info WHERE unique_code=? AND is_delete=0 `
// 	err = o.Raw(sql, uniqueCode).QueryRow(&item)
// 	return
// }

// // GetNoContentExcelInfoByExcelId 根据表格id来获取excel表格详情
// func GetNoContentExcelInfoByExcelId(excelInfoId int) (item *MyExcelInfoList, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT excel_info_id,source,excel_type,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time,is_join_permission
//  FROM excel_info WHERE excel_info_id=? AND is_delete=0 `
// 	err = o.Raw(sql, excelInfoId).QueryRow(&item)
// 	return
// }

// // AddExcelInfoAndSheet 新增excel
// func AddExcelInfoAndSheet(excelInfo *ExcelInfo, sheetParamsList []AddExcelSheetParams) (err error) {
// 	o, err := orm.NewOrmUsingDB("data").Begin()
// 	if err != nil {
// 		return
// 	}
// 	defer func() {
// 		if err != nil {
// 			_ = o.Rollback()
// 		} else {
// 			_ = o.Commit()
// 		}
// 	}()

// 	// 表格信息入库
// 	lastId, err := o.Insert(excelInfo)
// 	if err != nil {
// 		return
// 	}
// 	excelInfo.ExcelInfoId = int(lastId)

// 	// sheet信息入库
// 	for _, sheetInfo := range sheetParamsList {
// 		dataNum := len(sheetInfo.DataList)

// 		//sheet信息入库
// 		excelSheetInfo := &ExcelSheet{
// 			ExcelSheetId: 0,
// 			ExcelInfoId:  excelInfo.ExcelInfoId,
// 			SheetName:    sheetInfo.SheetName,
// 			PageNum:      dataNum,
// 			Index:        sheetInfo.Index,
// 			Sort:         sheetInfo.Sort,
// 			Config:       sheetInfo.Config,
// 			CalcChain:    sheetInfo.CalcChain,
// 			ModifyTime:   time.Now(),
// 			CreateTime:   time.Now(),
// 		}
// 		sheetId, tmpErr := o.Insert(excelSheetInfo)
// 		if tmpErr != nil {
// 			err = tmpErr
// 			return
// 		}
// 		excelSheetInfo.ExcelSheetId = int(sheetId)

// 		// data信息入库
// 		if dataNum > 0 {
// 			for k, _ := range sheetInfo.DataList {
// 				sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId
// 				sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId
// 			}
// 			_, tmpErr = o.InsertMulti(dataNum, sheetInfo.DataList)
// 			if tmpErr != nil {
// 				err = tmpErr
// 				return
// 			}
// 		}
// 	}

// 	return
// }

// // SaveExcelInfoAndSheet 编辑保存
// func SaveExcelInfoAndSheet(excelInfo *ExcelInfo, updateExcelInfoParam []string, sheetParamsList []AddExcelSheetParams) (err error) {
// 	o, err := orm.NewOrmUsingDB("data").Begin()
// 	if err != nil {
// 		return
// 	}
// 	defer func() {
// 		if err != nil {
// 			_ = o.Rollback()
// 		} else {
// 			_ = o.Commit()
// 		}
// 	}()

// 	// 表格信息入库
// 	_, err = o.Update(excelInfo, updateExcelInfoParam...)
// 	if err != nil {
// 		return
// 	}

// 	// 先删除历史的sheet信息
// 	sql := `DELETE FROM excel_sheet WHERE excel_info_id = ?`
// 	_, err = o.Raw(sql, excelInfo.ExcelInfoId).Exec()
// 	if err != nil {
// 		return
// 	}

// 	// 再删除历史sheet中的cell data信息
// 	sql = `DELETE FROM excel_sheet_data WHERE excel_info_id = ?`
// 	_, err = o.Raw(sql, excelInfo.ExcelInfoId).Exec()
// 	if err != nil {
// 		return
// 	}

// 	// sheet信息入库
// 	for _, sheetInfo := range sheetParamsList {
// 		dataNum := len(sheetInfo.DataList)

// 		//sheet信息入库
// 		excelSheetInfo := &ExcelSheet{
// 			ExcelSheetId: 0,
// 			ExcelInfoId:  excelInfo.ExcelInfoId,
// 			SheetName:    sheetInfo.SheetName,
// 			PageNum:      dataNum,
// 			Index:        sheetInfo.Index,
// 			Sort:         sheetInfo.Sort,
// 			Config:       sheetInfo.Config,
// 			CalcChain:    sheetInfo.CalcChain,
// 			ModifyTime:   time.Now(),
// 			CreateTime:   time.Now(),
// 		}
// 		sheetId, tmpErr := o.Insert(excelSheetInfo)
// 		if tmpErr != nil {
// 			err = tmpErr
// 			return
// 		}
// 		excelSheetInfo.ExcelSheetId = int(sheetId)

// 		// data信息入库
// 		if dataNum > 0 {
// 			for k, _ := range sheetInfo.DataList {
// 				sheetInfo.DataList[k].ExcelSheetId = excelSheetInfo.ExcelSheetId
// 				sheetInfo.DataList[k].ExcelInfoId = excelSheetInfo.ExcelInfoId
// 			}
// 			_, tmpErr = o.InsertMulti(dataNum, sheetInfo.DataList)
// 			if tmpErr != nil {
// 				err = tmpErr
// 				return
// 			}
// 		}
// 	}

// 	return
// }

//	type BatchRefreshExcelReq struct {
//		ExcelCodes      []string `description:"表格编码"`
//		ReportId        int      `description:"报告ID"`
//		ReportChapterId int      `description:"报告章节ID"`
//		Source          string   `description:"来源，枚举值：report、english_report、smart_report"`
//	}
//
// BatchRefreshExcelReq 批量刷新表格请求
type BatchRefreshExcelReq struct {
	ExcelCodes      []string `gorm:"column:excel_codes" description:"表格编码"`
	ReportId        int      `gorm:"column:report_id" description:"报告ID"`
	ReportChapterId int      `gorm:"column:report_chapter_id" description:"报告章节ID"`
	Source          string   `gorm:"column:source" description:"来源，枚举值：report、english_report、smart_report"`
}

// GetExcelMaxSortByClassifyId 获取当前分类下，且排序数最大的excel
func GetExcelMaxSortByClassifyId(classifyId int, source int) (sort int, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT Max(sort) AS sort FROM excel_info WHERE excel_classify_id=? AND source = ? AND is_delete=0 order by sort desc,excel_info_id desc limit 1`
	err = o.Raw(sql, classifyId, source).Scan(&sort).Error
	return
}

// GetNoContentExcelListByExcelInfoIdList
// @Description: 根据ETA表格ID列表获取列表信息
// @param excelInfoIdList []string
// @return items
// @return err
func GetNoContentExcelListByExcelInfoIdList(excelInfoIdList []string) (items []*MyExcelInfoList, err error) {
	num := len(excelInfoIdList)
	if num <= 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC `
	err = o.Raw(sql, excelInfoIdList).Scan(&items).Error

	return
}

// GetNoContentExcelListByUserId
// @Description: 根据ETA表格ID列表获取列表信息
// @param userIdList []int
// @return items
// @return err
func GetNoContentExcelListByUserId(userIdList []int) (items []*MyExcelInfoList, err error) {
	num := len(userIdList)
	if num <= 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC `
	err = o.Raw(sql, userIdList).Scan(&items).Error

	return
}

// // ModifyExcelInfoUserIdByCodeList
// // @Description: 根据编码列表修改表格信息
// // @author: Roc
// // @datetime 2024-03-26 14:16:30
// // @param excelIdList []string
// // @param userId int
// // @param userName string
// // @return err error
func ModifyExcelInfoUserIdByCodeList(excelIdList []string, userId int, userName string) (err error) {
	num := len(excelIdList)
	if num <= 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) `
	err = o.Exec(sql, userId, userName, excelIdList).Error
	return
}

// // ModifyExcelInfoUserIdByOldUserId
// // @Description:  根据旧的用户id修改创建人
// // @author: Roc
// // @datetime 2024-03-25 19:14:59
// // @param oldUserId int
// // @param userId int
// // @param userName string
// // @return err error
func ModifyExcelInfoUserIdByOldUserId(oldUserIdList []int, userId int, userName string) (err error) {
	num := len(oldUserIdList)
	if num <= 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE is_delete=0 AND sys_user_id in (` + utils.GetOrmInReplace(num) + `) `
	err = o.Exec(sql, userId, userName, oldUserIdList).Error
	return
}

// GetExcelMaxSortByClassifyId 获取当前分类下，且排序数最大的excel
// func GetExcelMaxSortByClassifyId(classifyId int, source int) (sort int, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT Max(sort) AS sort FROM excel_info WHERE excel_classify_id=? AND source = ? AND is_delete=0 order by sort desc,excel_info_id desc limit 1`
// 	err = o.Raw(sql, classifyId, source).QueryRow(&sort)
// 	return
// }

// // GetNoContentExcelListByExcelInfoIdList
// // @Description: 根据ETA表格ID列表获取列表信息
// // @param excelInfoIdList []string
// // @return items
// // @return err
// func GetNoContentExcelListByExcelInfoIdList(excelInfoIdList []string) (items []*MyExcelInfoList, err error) {
// 	num := len(excelInfoIdList)
// 	if num <= 0 {
// 		return
// 	}
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC `
// 	_, err = o.Raw(sql, excelInfoIdList).QueryRows(&items)

// 	return
// }

// // GetNoContentExcelListByUserId
// // @Description: 根据ETA表格ID列表获取列表信息
// // @param userIdList []int
// // @return items
// // @return err
// func GetNoContentExcelListByUserId(userIdList []int) (items []*MyExcelInfoList, err error) {
// 	num := len(userIdList)
// 	if num <= 0 {
// 		return
// 	}
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_info WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) order by excel_info_id DESC `
// 	_, err = o.Raw(sql, userIdList).QueryRows(&items)

// 	return
// }

// // ModifyExcelInfoUserIdByCodeList
// // @Description: 根据编码列表修改表格信息
// // @author: Roc
// // @datetime 2024-03-26 14:16:30
// // @param excelIdList []string
// // @param userId int
// // @param userName string
// // @return err error
// func ModifyExcelInfoUserIdByCodeList(excelIdList []string, userId int, userName string) (err error) {
// 	num := len(excelIdList)
// 	if num <= 0 {
// 		return
// 	}
// 	o := orm.NewOrmUsingDB("data")
// 	sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE excel_info_id in (` + utils.GetOrmInReplace(num) + `) `
// 	_, err = o.Raw(sql, userId, userName, excelIdList).Exec()
// 	return
// }

// // ModifyExcelInfoUserIdByOldUserId
// // @Description:  根据旧的用户id修改创建人
// // @author: Roc
// // @datetime 2024-03-25 19:14:59
// // @param oldUserId int
// // @param userId int
// // @param userName string
// // @return err error
// func ModifyExcelInfoUserIdByOldUserId(oldUserIdList []int, userId int, userName string) (err error) {
// 	num := len(oldUserIdList)
// 	if num <= 0 {
// 		return
// 	}
// 	o := orm.NewOrmUsingDB("data")
// 	sql := `UPDATE excel_info SET sys_user_id=?,sys_user_real_name=? WHERE is_delete=0 AND sys_user_id in (` + utils.GetOrmInReplace(num) + `) `
// 	_, err = o.Raw(sql, userId, userName, oldUserIdList).Exec()
// 	return
// }

//	type ExcelInfoDetail struct {
//		ExcelInfoId     int         `orm:"column(excel_info_id);pk"`
//		Source          int         `description:"表格来源，1：excel插件的表格，2：自定义表格，默认：1"`
//		ExcelType       int         `description:"表格类型，1：指标列，2：日期列，默认：1"`
//		ExcelName       string      `description:"表格名称"`
//		UniqueCode      string      `description:"表格唯一编码"`
//		ExcelClassifyId int         `description:"表格分类id"`
//		SysUserId       int         `description:"操作人id"`
//		SysUserRealName string      `description:"操作人真实姓名"`
//		Content         string      `description:"表格内容"`
//		ExcelImage      string      `description:"表格图片"`
//		FileUrl         string      `description:"表格下载地址"`
//		Sort            int         `description:"排序字段，数字越小越排前面"`
//		IsDelete        int         `description:"是否删除，0：未删除，1：已删除"`
//		ModifyTime      time.Time   `description:"最近修改日期"`
//		CreateTime      time.Time   `description:"创建日期"`
//		TableData       interface{} `description:"表格内容"`
//		//Button           ExcelInfoDetailButton `description:"操作权限"`
//		CanEdit          bool   `description:"是否可编辑"`
//		Editor           string `description:"编辑人"`
//		IsJoinPermission int    `description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
//		HaveOperaAuth    bool   `description:"是否有数据权限"`
//	}
//
// ExcelInfoDetail excel表格详情(前端使用)
type ExcelInfoDetail struct {
	ExcelInfoId      int         `gorm:"column:excel_info_id;primaryKey" orm:"column(excel_info_id);pk"`
	Source           int         `gorm:"column:source" description:"表格来源，1：excel插件的表格，2：自定义表格，默认：1"`
	ExcelType        int         `gorm:"column:excel_type" description:"表格类型，1：指标列，2：日期列，默认：1"`
	ExcelName        string      `gorm:"column:excel_name" description:"表格名称"`
	UniqueCode       string      `gorm:"column:unique_code" description:"表格唯一编码"`
	ExcelClassifyId  int         `gorm:"column:excel_classify_id" description:"表格分类id"`
	SysUserId        int         `gorm:"column:sys_user_id" description:"操作人id"`
	SysUserRealName  string      `gorm:"column:sys_user_real_name" description:"操作人真实姓名"`
	Content          string      `gorm:"column:content" description:"表格内容"`
	ExcelImage       string      `gorm:"column:excel_image" description:"表格图片"`
	FileUrl          string      `gorm:"column:file_url" description:"表格下载地址"`
	Sort             int         `gorm:"column:sort" description:"排序字段，数字越小越排前面"`
	IsDelete         int         `gorm:"column:is_delete" description:"是否删除，0：未删除，1：已删除"`
	ModifyTime       time.Time   `gorm:"column:modify_time" description:"最近修改日期"`
	CreateTime       time.Time   `gorm:"column:create_time" description:"创建日期"`
	TableData        interface{} `gorm:"column:table_data" description:"表格内容"`
	CanEdit          bool        `gorm:"column:can_edit" description:"是否可编辑"`
	Editor           string      `gorm:"column:editor" description:"编辑人"`
	IsJoinPermission int         `gorm:"column:is_join_permission" description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
	HaveOperaAuth    bool        `gorm:"column:have_opera_auth" description:"是否有数据权限"`
}