package models

import (
	"LAPP_SJA_ME/db"
	"LAPP_SJA_ME/utils"
	"github.com/360EntSecGroup-Skylar/excelize"
	"time"
	"xorm.io/core"
)

type Tabcolname struct {
	Tabname   string `xorm:"pk comment('表名') VARCHAR(40)" json:"tabcolname-tabname"`
	Colname   string `xorm:"pk comment('字段名') VARCHAR(40)" json:"tabcolname-colname"`
	Textlabel string `xorm:"comment('文本') VARCHAR(30)" json:"tabcolname-textlabel"`
	Longlabel string `xorm:"comment('超文本') VARCHAR(60)" json:"tabcolname-longlabel"`
	Valformat string `xorm:"comment('值类型') VARCHAR(60)" json:"tabcolname-valformat"`
	Lastmodif string `xorm:"comment('更新时间') VARCHAR(14)" json:"tabcolname-lastmodif"`
	Lastuser  string `xorm:"comment('更新人员') VARCHAR(20)" json:"tabcolname-lastuser"`
	Credatuz  string `xorm:"comment('创建时间') VARCHAR(14)" json:"tabcolname-credatuz"`
}
func (t *Tabcolname) TableName() string {
	return "tabcolname"
}
// 清除string字段的右侧空格
func (t *Tabcolname) Clipped() {
	utils.TrimStruct(t, *t)
}

//excel表格导入数据库
func (t *Tabcolname)ReadXlsx(path string) bool {
	e := db.Eloquent.Master()

	xlsx, err := excelize.OpenFile(path)
	if err != nil {
		return false
	}
	rows := xlsx.GetRows("Sheet1")
	for k, row := range rows {
		if k == 0{
			continue
		}
		data := Tabcolname{}
		data.Tabname = row[0]
		data.Colname = row[1]
		data.Textlabel = row[2]
		data.Longlabel = row[3]
		data.Valformat = row[4]
		data.Lastuser = t.Lastuser
		data.Credatuz = utils.TimeFormat(time.Now(),"yyyyMMddHHmmss")
		data.Lastmodif = utils.TimeFormat(time.Now(),"yyyyMMddHHmmss")
		has, _ := e.Where("tabname = ? & colname = ?", data.Tabname,data.Colname).Get(t)
		if !has{
			_, err := e.Insert(&data)
			if err != nil{
				continue
			}
		}else{
			_, err := e.Id(core.PK{data.Tabname,data.Colname}).Update(&data)
			if err != nil{
				continue
			}
		}
	}
	return true
}
//分页查询
func (t *Tabcolname) DataListPage(pageSize int, pageIndex int)([]Tabcolname, int, error){
	doc := make([]Tabcolname, 0)
	e := db.Eloquent.Master()
	query := e.Where("1=1")
	where := "where 1=1 "
	if !utils.ValueIsEmpty(t.Colname) {
		query = query.And("colname = ?", t.Colname)
		where += " and colname like " + "'%" + t.Colname + "%'"
	}
	Offset := (pageIndex - 1) * pageSize
	err := e.SQL("SELECT TOP " + utils.ValueToString(pageSize, "") + " tabcolname.* FROM tabcolname " + where + " AND (convert(varchar(10),tabname)+convert(varchar(40),colname) NOT IN (SELECT TOP " + utils.ValueToString(Offset, "") + " convert(varchar(10),tabname)+convert(varchar(40),colname) FROM tabcolname " + where + " ORDER BY credatuz DESC)) ORDER BY credatuz DESC").Find(&doc)
	if err != nil {
		return nil, 0, err
	}
	for k, _ := range doc {
		doc[k].Clipped()
	}

	tcount := new(Tabcolname)

	count, _ := query.Count(tcount)
	return doc, int(count), nil
}
//查询数据
func(t * Tabcolname) DataList()([]Tabcolname,error){
	e := db.Eloquent.Master()
	data := make([]Tabcolname,0)
	err := e.Find(&data)
	for k, _ := range data {
		data[k].Clipped()
	}
	if err != nil{
		return nil,err
	}
	return data,nil
}
//删除数据
func(t * Tabcolname) DelData()(success bool, err error){
	e := db.Eloquent.Master()
	if _,err = e.Table(t.TableName()).ID(core.PK{t.Tabname,t.Colname}).Delete(&Tabcolname{}); err != nil {
		success = false
		return
	}
	success = true
	return
}