package excel

import (
	"eta/eta_mobile/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"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"`
}

// 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
}

// 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 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
}

// 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
}

// 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
}

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 GetExcelClassifyByParentId(parentId, source int) (items []*ExcelClassifyItems, err error) {
	o := orm.NewOrmUsingDB("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).QueryRows(&items)
	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 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 GetExcelClassifyAll() (items []*ExcelClassifyItems, err error) {
	o := orm.NewOrmUsingDB("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).QueryRows(&items)
	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:"是否有数据权限"`
}

func GetExcelClassifyByCondition(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
	}
	err = o.Raw(sql, pars).QueryRow(&item)
	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
}

// 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
}

// 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
}

// Update 更新图表分类基础信息
func (ExcelClassify *ExcelClassify) Update(cols []string) (err error) {
	o := orm.NewOrmUsingDB("data")
	_, err = o.Update(ExcelClassify, cols...)
	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
}

type ExcelClassifyView struct {
	ExcelClassifyId   int    `orm:"column(excel_classify_id);pk"`
	ExcelClassifyName string `description:"分类名称"`
	ParentId          int    `description:"父级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
}

// 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 := 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
}