package data_manage

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"time"
)

// ExcelClassify excel表格分类
type ExcelClassify struct {
	ExcelClassifyId   int       `orm:"column(excel_classify_id);pk"`
	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:"修改时间"`
}

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

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

func GetExcelClassifyByParentId(parentId int) (items []*ExcelClassifyItems, 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`
	_, err = o.Raw(sql, parentId).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
}

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) (err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` update excel_classify set sort = ` + updateSort + ` WHERE parent_id=? 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, 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) (sort int, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := `SELECT Max(sort) AS sort FROM excel_classify WHERE parent_id=? AND is_delete=0 `
	err = o.Raw(sql, parentId).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
}