package excel

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

// ExcelClassify excel表格分类
// type ExcelClassify struct {
// 	ExcelClassifyId   int       `orm:"column(excel_classify_id);pk"`
// 	Source            int       `description:"表格来源，1：excel插件的表格，2：自定义表格，3：混合表格，4：自定义分析，默认：1"`
// 	ExcelClassifyName string    `description:"分类名称"`
// 	ParentId          int       `description:"父级id"`
// 	SysUserId         int       `description:"创建人id"`
// 	SysUserRealName   string    `description:"创建人姓名"`
// 	Level             int       `description:"层级"`
// 	UniqueCode        string    `description:"唯一编码"`
// 	Sort              int       `description:"排序字段，越小越靠前，默认值：10"`
// 	IsDelete          int       `description:"排序字段，越小越靠前，默认值：10"`
// 	CreateTime        time.Time `description:"创建时间"`
// 	ModifyTime        time.Time `description:"修改时间"`
// 	IsJoinPermission  int       `description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
// }

type ExcelClassify struct {
	ExcelClassifyId   int       `gorm:"column:excel_classify_id;primaryKey" orm:"column(excel_classify_id);pk"` // 主键
	Source            int       `description:"表格来源，1：excel插件的表格，2：自定义表格，3：混合表格，4：自定义分析，默认：1" gorm:"column:source"`
	ExcelClassifyName string    `description:"分类名称" gorm:"column:excel_classify_name"`
	ParentId          int       `description:"父级id" gorm:"column:parent_id"`
	SysUserId         int       `description:"创建人id" gorm:"column:sys_user_id"`
	SysUserRealName   string    `description:"创建人姓名" gorm:"column:sys_user_real_name"`
	Level             int       `description:"层级" gorm:"column:level"`
	UniqueCode        string    `description:"唯一编码" gorm:"column:unique_code"`
	Sort              int       `description:"排序字段，越小越靠前，默认值：10" gorm:"column:sort"`
	IsDelete          int       `description:"是否删除，0：未删除；1：已删除，默认值：0" gorm:"column:is_delete"` // 修改了注释以避免混淆
	CreateTime        time.Time `description:"创建时间" gorm:"column:create_time"`
	ModifyTime        time.Time `description:"修改时间" gorm:"column:modify_time"`
	IsJoinPermission  int       `description:"是否加入权限管控，0：不加入；1：加入；默认：0" gorm:"column:is_join_permission"`
}

// TableName 设置模型对应的表名
func (ExcelClassify) TableName() string {
	return "excel_classify"
}

// AddExcelClassify 添加excel分类
// func AddExcelClassify(item *ExcelClassify) (lastId int64, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	lastId, err = o.Insert(item)
// 	if err != nil {
// 		return
// 	}
// 	item.ExcelClassifyId = int(lastId)

//		return
//	}
func AddExcelClassify(item *ExcelClassify) (lastId int64, err error) {
	o := global.DmSQL["data"]
	err = o.Create(item).Error
	if err != nil {
		return
	}
	lastId = int64(item.ExcelClassifyId)
	return
}

// GetExcelClassifyCount 获取同级分类下存在同名分类的数量
//
//	func GetExcelClassifyCount(ExcelClassifyName string, parentId, source int) (count int, err error) {
//		o := orm.NewOrmUsingDB("data")
//		sql := `SELECT COUNT(1) AS count FROM excel_classify WHERE parent_id=? AND source = ? AND excel_classify_name=? AND is_delete=0 `
//		err = o.Raw(sql, parentId, source, ExcelClassifyName).QueryRow(&count)
//		return
//	}

func GetExcelClassifyCount(ExcelClassifyName string, parentId, source int) (count int, err error) {
	o := global.DmSQL["data"]
	sql := `SELECT COUNT(1) AS count FROM excel_classify WHERE parent_id=? AND source = ? AND excel_classify_name=? AND is_delete=0 `
	err = o.Raw(sql, parentId, source, ExcelClassifyName).Scan(&count).Error
	return
}

//	func GetExcelClassifyById(classifyId int) (item *ExcelClassify, err error) {
//		o := orm.NewOrmUsingDB("data")
//		sql := `SELECT * FROM excel_classify WHERE excel_classify_id=? AND is_delete=0 `
//		err = o.Raw(sql, classifyId).QueryRow(&item)
//		return
//	}
func GetExcelClassifyById(classifyId int) (item *ExcelClassify, err error) {
	o := global.DmSQL["data"]
	sql := `SELECT * FROM excel_classify WHERE excel_classify_id=? AND is_delete=0 `
	err = o.Raw(sql, classifyId).First(&item).Error
	return
}

// GetExcelClassifyByIdList
// @Description: 根据分类id列表获取所有分类信息
// @author: Roc
// @datetime 2024-04-07 16:24:04
// @param classifyIdList []int
// @return items []*ExcelClassify
// @return err error
// func GetExcelClassifyByIdList(classifyIdList []int) (items []*ExcelClassify, err error) {
// 	num := len(classifyIdList)
// 	if num <= 0 {
// 		return
// 	}
// 	o := orm.NewOrmUsingDB("data")
// 	sql := `SELECT * FROM excel_classify WHERE excel_classify_id in (` + utils.GetOrmInReplace(num) + `) AND is_delete=0 `
// 	_, err = o.Raw(sql, classifyIdList).QueryRows(&items)
// 	return
// }

// GetExcelClassifyByIdList
// @Description: 根据分类id列表获取所有分类信息
// @author: zqbao
// @datetime 2024-04-07 16:24:04
// @param classifyIdList []int
// @return items []*ExcelClassify
// @return err error
func GetExcelClassifyByIdList(classifyIdList []int) (items []*ExcelClassify, err error) {
	num := len(classifyIdList)
	if num <= 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := `SELECT * FROM excel_classify WHERE excel_classify_id in (` + utils.GetGormInReplace(num) + `) AND is_delete=0 `
	err = o.Raw(sql, classifyIdList).Scan(&items).Error
	return
}

// GetExcelClassifyBySourceAndIsJoinPermission
// @Description: 根据分类id列表获取所有分类信息
// @author: Roc
// @datetime 2024-04-07 16:24:04
// @param classifyIdList []int
// @return items []*ExcelClassify
// @return err error
// func GetExcelClassifyBySourceAndIsJoinPermission(source, isJoinPermission int) (items []*ExcelClassify, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := `SELECT * FROM excel_classify WHERE source = ? AND is_join_permission = ? `
// 	_, err = o.Raw(sql, source, isJoinPermission).QueryRows(&items)

// 	return
// }

// GetExcelClassifyBySourceAndIsJoinPermission
// @Description: 根据分类id列表获取所有分类信息
// @author: zqbao
// @datetime 2024-04-07 16:24:04
// @param classifyIdList []int
// @return items []*ExcelClassify
// @return err error
func GetExcelClassifyBySourceAndIsJoinPermission(source, isJoinPermission int) (items []*ExcelClassify, err error) {
	o := global.DmSQL["data"]
	sql := `SELECT * FROM excel_classify WHERE source = ? AND is_join_permission = ? `
	err = o.Raw(sql, source, isJoinPermission).Scan(&items).Error
	return
}

//	func GetChildClassifyById(classifyId int) (items []*ExcelClassify, err error) {
//		o := orm.NewOrmUsingDB("data")
//		sql := `SELECT * FROM excel_classify WHERE parent_id=? AND is_delete=0 `
//		_, err = o.Raw(sql, classifyId).QueryRows(&items)
//		return
//	}

func GetChildClassifyById(classifyId int) (items []*ExcelClassify, err error) {
	o := global.DmSQL["data"]
	sql := `SELECT * FROM excel_classify WHERE parent_id=? AND is_delete=0 `
	err = o.Raw(sql, classifyId).Scan(&items).Error
	return
}

func GetExcelClassifyByParentId(parentId, source int) (items []*ExcelClassifyItems, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_classify WHERE parent_id=? AND source = ? AND is_delete=0 order by sort asc,excel_classify_id asc`
	err = o.Raw(sql, parentId, source).Scan(&items).Error
	return
}

//	func GetExcelClassifyBySource(source int) (items []*ExcelClassifyItems, err error) {
//		o := orm.NewOrmUsingDB("data")
//		sql := ` SELECT * FROM excel_classify WHERE  source = ? AND is_delete=0 order by sort asc,excel_classify_id asc`
//		_, err = o.Raw(sql, source).QueryRows(&items)
//		return
//	}
func GetExcelClassifyBySource(source int) (items []*ExcelClassifyItems, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_classify WHERE  source = ? AND is_delete=0 order by sort asc,excel_classify_id asc`
	err = o.Raw(sql, source).Scan(&items).Error
	return
}

//	func GetExcelClassifyBySourceOrderByLevel(source int) (items []*ExcelClassifyItems, err error) {
//		o := orm.NewOrmUsingDB("data")
//		sql := ` SELECT * FROM excel_classify WHERE  source = ? AND is_delete=0 order by level asc, sort asc,excel_classify_id asc`
//		_, err = o.Raw(sql, source).QueryRows(&items)
//		return
//	}

func GetExcelClassifyBySourceOrderByLevel(source int) (items []*ExcelClassifyItems, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_classify WHERE  source = ? AND is_delete=0 order by level asc, sort asc,excel_classify_id asc`
	err = o.Raw(sql, source).Scan(&items).Error
	return
}

func GetExcelClassifyAll() (items []*ExcelClassifyItems, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_classify WHERE parent_id<>0 AND is_delete=0 order by sort asc,excel_classify_id asc`
	err = o.Raw(sql).Scan(&items).Error
	return
}

//	type ExcelClassifyItems struct {
//		ExcelClassifyId   int `description:"分类id"`
//		ExcelInfoId       int `description:"表格id"`
//		ExcelClassifyName string
//		ParentId          int
//		Level             int    `description:"层级"`
//		Sort              int    `description:"排序字段，越小越靠前，默认值：10"`
//		UniqueCode        string `description:"唯一编码"`
//		SysUserId         int    `description:"创建人id"`
//		SysUserRealName   string `description:"创建人姓名"`
//		StartDate         string `description:"自定义开始日期"`
//		Children          []*ExcelClassifyItems
//		IsJoinPermission  int  `description:"是否加入权限管控，0：不加入；1：加入；默认：0"`
//		HaveOperaAuth     bool `description:"是否有数据权限"`
//	}
type ExcelClassifyItems struct {
	ExcelClassifyId   int                   `gorm:"column:excel_classify_id" description:"分类id"` // 分类id
	ExcelInfoId       int                   `gorm:"column:excel_info_id" description:"表格id"`     // 表格id
	ExcelClassifyName string                `gorm:"column:excel_classify_name" description:"分类名称"`
	ParentId          int                   `gorm:"column:parent_id" description:"父级id"`
	Level             int                   `gorm:"column:level" description:"层级"`                 // 层级
	Sort              int                   `gorm:"column:sort" description:"排序字段，越小越靠前，默认值：10"`   // 排序字段，越小越靠前，默认值：10
	UniqueCode        string                `gorm:"column:unique_code" description:"唯一编码"`         // 唯一编码
	SysUserId         int                   `gorm:"column:sys_user_id" description:"创建人id"`        // 创建人id
	SysUserRealName   string                `gorm:"column:sys_user_real_name" description:"创建人姓名"` // 创建人姓名
	StartDate         string                `gorm:"column:start_date" description:"自定义开始日期"`       // 自定义开始日期
	Children          []*ExcelClassifyItems `gorm:"-"`
	IsJoinPermission  int                   `gorm:"column:is_join_permission" description:"是否加入权限管控，0：不加入；1：加入；默认：0"` // 是否加入权限管控，0：不加入；1：加入；默认：0
	HaveOperaAuth     bool                  `gorm:"column:have_opener_auth" description:"是否有数据权限"`                    // 是否有数据权限
}

// TableName 设置模型对应的表名
func (ExcelClassifyItems) TableName() string {
	return "excel_classify_items"
}

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

// GetNextExcelClassifyByCondition 获取下一个分类
// func GetNextExcelClassifyByCondition(condition string, pars []interface{}) (item *ExcelClassify, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_classify WHERE 1=1 AND is_delete=0 `
// 	if condition != "" {
// 		sql += condition
// 	}
// 	sql += " ORDER BY sort asc , create_time ASC LIMIT 1 "
// 	err = o.Raw(sql, pars).QueryRow(&item)
// 	return
// }

func GetNextExcelClassifyByCondition(condition string, pars []interface{}) (item *ExcelClassify, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_classify WHERE 1=1 AND is_delete=0 `
	if condition != "" {
		sql += condition
	}
	sql += " ORDER BY sort asc , create_time ASC LIMIT 1 "
	err = o.Raw(sql, pars...).First(&item).Error
	return
}

// GetFirstExcelClassifyByParentId 获取当前父级图表分类下的排序第一条的数据
// func GetFirstExcelClassifyByParentId(parentId int) (item *ExcelClassify, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` SELECT * FROM excel_classify WHERE parent_id=? AND is_delete=0 order by sort asc,excel_classify_id asc limit 1`
// 	err = o.Raw(sql, parentId).QueryRow(&item)
// 	return
// }

func GetFirstExcelClassifyByParentId(parentId int) (item *ExcelClassify, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_classify WHERE parent_id=? AND is_delete=0 order by sort asc,excel_classify_id asc limit 1`
	err = o.Raw(sql, parentId).First(&item).Error
	return
}

// UpdateExcelClassifySortByParentId 根据图表父类id更新排序
// func UpdateExcelClassifySortByParentId(parentId, classifyId, nowSort int, updateSort string, source int) (err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := ` update excel_classify set sort = ` + updateSort + ` WHERE parent_id=? and source=? and sort > ? AND is_delete=0 `
// 	if classifyId > 0 {
// 		sql += ` or ( excel_classify_id > ` + fmt.Sprint(classifyId) + ` and sort= ` + fmt.Sprint(nowSort) + `)`
// 	}
// 	_, err = o.Raw(sql, parentId, source, nowSort).Exec()
// 	return
// }

func UpdateExcelClassifySortByParentId(parentId, classifyId, nowSort int, updateSort string, source int) (err error) {
	o := global.DmSQL["data"]
	sql := ` update excel_classify set sort = ` + updateSort + ` WHERE parent_id=? and source=? and sort > ? AND is_delete=0 `
	if classifyId > 0 {
		sql += ` or ( excel_classify_id > ` + fmt.Sprint(classifyId) + ` and sort= ` + fmt.Sprint(nowSort) + `)`
	}
	err = o.Exec(sql, parentId, source, nowSort).Error
	return
}

// Update 更新图表分类基础信息
// func (ExcelClassify *ExcelClassify) Update(cols []string) (err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	_, err = o.Update(ExcelClassify, cols...)
// 	return
// }

func (ExcelClassify *ExcelClassify) Update(cols []string) (err error) {
	o := global.DmSQL["data"]
	err = o.Model(ExcelClassify).Select(cols).Updates(ExcelClassify).Error
	return
}

// GetExcelClassifyMaxSort 获取图表分类下最大的排序数
// func GetExcelClassifyMaxSort(parentId int, source int) (sort int, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := `SELECT Max(sort) AS sort FROM excel_classify WHERE parent_id=? AND source = ? AND is_delete=0 `
// 	err = o.Raw(sql, parentId, source).QueryRow(&sort)
// 	return
// }

func GetExcelClassifyMaxSort(parentId int, source int) (sort int, err error) {
	o := global.DmSQL["data"]
	sql := `SELECT COALESCE(MAX(sort), 0) AS sort FROM excel_classify WHERE parent_id=? AND source = ? AND is_delete=0 `
	err = o.Raw(sql, parentId, source).Scan(&sort).Error
	return
}

//	type ExcelClassifyView struct {
//		ExcelClassifyId   int    `orm:"column(excel_classify_id);pk"`
//		ExcelClassifyName string `description:"分类名称"`
//		ParentId          int    `description:"父级id"`
//	}
type ExcelClassifyView struct {
	ExcelClassifyId   int    `orm:"column(excel_classify_id);pk" gorm:"column:excel_classify_id;primaryKey"` // 主键
	ExcelClassifyName string `description:"分类名称" gorm:"column:excel_classify_name"`                          // 分类名称
	ParentId          int    `description:"父级id" gorm:"column:parent_id"`                                    // 父级id
}

// func GetExcelClassifyViewById(classifyId int) (item *ExcelClassifyView, err error) {
// 	o := orm.NewOrmUsingDB("data")
// 	sql := `SELECT * FROM excel_classify WHERE excel_classify_id=? AND is_delete=0 `
// 	err = o.Raw(sql, classifyId).QueryRow(&item)
// 	return
// }

func GetExcelClassifyViewById(classifyId int) (item *ExcelClassifyView, err error) {
	o := global.DmSQL["data"]
	sql := `SELECT * FROM excel_classify WHERE excel_classify_id=? AND is_delete=0 `
	err = o.Raw(sql, classifyId).First(&item).Error
	return
}

// ExcelClassifyItemBySort 自定义比较函数，按年龄从小到大排序
func ExcelClassifyItemBySort(p1, p2 *ExcelClassifyItems) bool {
	return p1.Sort < p2.Sort
}

// GetClassifyByIdList
// @Description: 根据分类ID列表获取分类列表
// @author: Roc
// @datetime 2024-04-02 19:40:30
// @param classifyIdList []int
// @return items []*ExcelClassify
// @return err error
func GetClassifyByIdList(classifyIdList []int) (items []*ExcelClassify, err error) {
	num := len(classifyIdList)
	if num <= 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := `SELECT * FROM excel_classify WHERE excel_classify_id in (` + utils.GetGormInReplace(num) + `) AND is_delete=0 `
	err = o.Raw(sql, classifyIdList).Scan(&items).Error
	return
}

// func GetClassifyByIdList(classifyIdList []int) (items []*ExcelClassify, err error) {
// 	num := len(classifyIdList)
// 	if num <= 0 {
// 		return
// 	}
// 	o := orm.NewOrmUsingDB("data")
// 	sql := `SELECT * FROM excel_classify WHERE excel_classify_id in (` + utils.GetOrmInReplace(num) + `) AND is_delete=0 `
// 	_, err = o.Raw(sql, classifyIdList).QueryRows(&items)

// 	return
// }