package models

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


type ExcelInfo struct {
	ExcelInfoId     int       `gorm:"column:excel_info_id;primaryKey" description:"表格ID"`
	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:"创建日期"`
}

func (e *ExcelInfo) TableName() string {
	return "excel_info"
}

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       `gorm:"column:excel_info_id;primaryKey" description:"表格ID" orm:"column(excel_info_id);pk"`
	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:"创建日期"`
}

func AddExcelInfo(excelInfo *ExcelInfo) (err error) {
	o := global.DmSQL["data"]
	err = o.Create(excelInfo).Error
	return
}

func EditExcelInfo(excelInfo *ExcelInfo, updateExcelInfoParams []string) (err error) {
	o := global.DmSQL["data"]
	err = o.Model(excelInfo).Select(updateExcelInfoParams).Updates(excelInfo).Error
	return
}

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
}

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

func GetNextExcelInfo(classifyId 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.excel_classify_id>? AND is_delete=0
			ORDER BY a.excel_classify_id ASC
			LIMIT 1 `
	err = o.Raw(sql, classifyId).First(&item).Error
	return
}

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
}

func GetExcelInfoByUniqueCode(uniqueCode string) (item *ExcelInfoView, 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
}

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 asc limit 1`
	err = o.Raw(sql, classifyId).First(&item).Error
	return
}

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














type ExcelInfoView struct {
	ExcelInfoId     int       `gorm:"column:excel_info_id;primaryKey" description:"表格ID" 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：已删除" json:"-"`
	ModifyTime      time.Time `gorm:"column:modify_time" description:"最近修改日期"`
	CreateTime      time.Time `gorm:"column:create_time" description:"创建日期"`
	SourcesFrom     string    `gorm:"column:sources_from" description:"图表来源"`
}

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
}

func GetExcelInfoListByUniqueCodeSlice(uniqueCodeSlice []string) (total int64, items []*ExcelInfo, err error) {
	num := len(uniqueCodeSlice)
	if num <= 0 {
		return
	}
	o := global.DmSQL["data"]
	sql := ` SELECT * FROM excel_info WHERE unique_code in ("` + utils.GetGormInReplace(num) + `") AND is_delete=0 `
	err = o.Raw(sql, uniqueCodeSlice).Scan(&items).Error
	total = int64(len(items))
	return
}

func GetExcelListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyExcelInfoList, 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 create_time DESC LIMIT ?,? "
	pars = append(pars, startSize, pageSize)
	err = o.Raw(sql, pars...).Scan(&item).Error
	return
}

func GetNoContentExcelListByCondition(condition string, pars []interface{}, startSize, pageSize int) (item []*MyExcelInfoList, err error) {
	o := global.DmSQL["data"]
	sql := ` SELECT excel_info_id,excel_name,unique_code,excel_classify_id,sys_user_id,sys_user_real_name,excel_image,file_url,sort,create_time,modify_time
FROM excel_info WHERE 1=1 AND is_delete=0 `
	if condition != "" {
		sql += condition
	}
	sql += " ORDER BY create_time DESC LIMIT ?,? "
	pars = append(pars, startSize, pageSize)
	err = o.Raw(sql, pars...).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
}

func GetExcelViewInfoByExcelInfoId(excelInfoId int) (item *MyExcelInfoList, 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 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
}

func GetExcelInfoListByCondition(condition string, pars []interface{}) (items []*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, excel_info_id asc`
	err = o.Raw(sql, pars...).Scan(&items).Error
	return
}