LAPP_GAAS_GFrame_BACKEND/web/models/etltab_model.go

package models

import (
	"LAPP_GAAS_GFrame_BACKEND/db"
	"LAPP_GAAS_GFrame_BACKEND/utils"
	"errors"
	"github.com/go-xorm/xorm"
	"strings"
	"time"
	"xorm.io/core"
)

type Etltab struct {
	Finr           int         `xorm:"pk comment('工厂号') Int" json:"etltab-finr"`
	Eid            int         `xorm:"pk comment('Id') Int" json:"etltab-eid"`
	Fromdbtype     string      `xorm:"comment('数据库类型') VARCHAR(32)" json:"etltab-fromdbtype"`
	Fromdb         string      `xorm:"comment('来源数据库') VARCHAR(32)" json:"etltab-fromdb"`
	Fromdrivername string      `xorm:"comment('来源数据库引擎') VARCHAR(32)" json:"etltab-fromdrivername"`
	Fromtable      string      `xorm:"comment('来源数据表') VARCHAR(32)" json:"etltab-fromtable"`
	Todb           string      `xorm:"comment('导入数据库') VARCHAR(32)" json:"etltab-todb"`
	Todbtype       string      `xorm:"comment('导入数据库') VARCHAR(32)" json:"etltab-todbtype"`
	Todrivername   string      `xorm:"comment('导入数据库引擎') VARCHAR(32)" json:"etltab-todrivername"`
	Totable        string      `xorm:"comment('导入数据表') VARCHAR(30)" json:"etltab-totable"`
	ChooseType     string      `xorm:"comment('选择方式') VARCHAR(30)" json:"etltab-choose_type"`
	Sqlshow        string      `xorm:"comment('sql语句') VARCHAR(255)" json:"etltab-sqlshow"`
	Status         string      `xorm:"comment('状态') VARCHAR(10)" json:"etltab-status"`
	SearchType     int         `xorm:"comment('查询类型') int(1)" json:"etltab-search_type"`
	SearchTime     string      `xorm:"comment('上次导入节点') VARCHAR(20)" json:"etltab-search_time"`
	SearchFiled    string      `xorm:"comment('时间查询字段') VARCHAR(20)" json:"etltab-search_filed"`
	Funcspec       string      `xorm:"comment('特殊方法') VARCHAR(64)" json:"etltab-funcspec"`
	Lastmodif      string      `xorm:"comment('最近一次更改时间') VARCHAR(14)" json:"etltab-lastmodif"`
	Lastuser       string      `xorm:"comment('最近一次更改人') VARCHAR(20)" json:"etltab-lastuser"`
	Credatuz       string      `xorm:"comment('创建时间') VARCHAR(14)" json:"etltab-credatuz"`
	Valst          []Etltablst `json:"valst" xorm:"-"`
}

func (t *Etltab) TableName() string {
	return "etltab"
}

// 清除string字段的右侧空格
func (t *Etltab) Clipped() {
	utils.TrimStruct(t, *t)
}

//增
func (t *Etltab) Add() error {
	es := db.Eloquent.Slaves()
	e := es[0]
	countrole := new(Etltab)
	affw, err := e.Table("etltab").ID(core.PK{t.Finr, t.Eid}).Count(countrole)
	if err != nil {
		return err
	}
	if affw > 0 {
		return errors.New("数据已经存在！")
	}
	_, err = e.Table("etltab").Insert(t)
	if err != nil {
		return err
	}
	//批量删除
	_, err = e.Table("etltablst").Where("finr = ? and eid = ?", t.Finr, t.Eid).Delete(&Etltablst{})
	if err != nil {
		return err
	}

	//批量插入
	alldata := make([]Etltablst, 0)
	for k, v := range t.Valst {
		onedata := Etltablst{}
		onedata.Finr = t.Finr
		onedata.Eid = t.Eid
		onedata.Id = (k + 1)
		onedata.Table = t.Totable
		onedata.Field = v.Field
		onedata.Fieldname = v.Fieldname
		onedata.Fieldtype = v.Fieldtype
		onedata.ToField = v.ToField
		onedata.Funcspec = v.Funcspec
		onedata.Lastmodif = utils.TimeFormat(time.Now(), "yyyyMMddHHmmss")
		onedata.Lastuser = t.Lastuser
		onedata.Credatuz = utils.TimeFormat(time.Now(), "yyyyMMddHHmmss")
		alldata = append(alldata, onedata)
	}
	_, err = e.Table("etltablst").Insert(&alldata)
	if err != nil {
		return err
	}
	return nil
}

//删
func (t *Etltab) Del() bool {
	es := db.Eloquent.Slaves()
	e := es[0]
	_, err := e.ID(core.PK{t.Finr, t.Eid}).Delete(&Etltab{})
	if err != nil {
		return false
	}
	countrole := new(Etltablst)
	affw, _ := e.Table("etltablst").Where("finr = ? and eid = ?", t.Finr, t.Eid).Count(countrole)
	if affw > 0 {
		_, err = e.Where("finr = ? and eid = ?", t.Finr, t.Eid).Delete(&Etltablst{})
		if err != nil {
			return false
		}
	}
	return true
}

//改
func (t *Etltab) Update() bool {
	es := db.Eloquent.Slaves()
	e := es[0]
	columns := []string{"fromdb", "fromtable", "todb", "todrivername", "totable", "sqlshow", "status", "search_type", "search_time", "choose_type", "to_type", "search_filed", "lastmodif", "lastuser", "credatuz"}
	_, err := e.ID(core.PK{t.Finr, t.Eid}).MustCols(columns...).Update(t)
	if err != nil {
		return false
	}
	//批量删除
	_, err = e.Table("etltablst").Where("finr = ? and eid = ?", t.Finr, t.Eid).Delete(&Etltablst{})
	if err != nil {
		return false
	}
	//批量插入
	alldata := make([]Etltablst, 0)
	for k, v := range t.Valst {
		onedata := Etltablst{}
		onedata.Finr = t.Finr
		onedata.Eid = t.Eid
		onedata.Id = (k + 1)
		onedata.Table = t.Totable
		onedata.Field = v.Field
		onedata.Fieldname = v.Fieldname
		onedata.Fieldtype = v.Fieldtype
		onedata.ToField = v.ToField
		onedata.Funcspec = v.Funcspec
		onedata.Lastmodif = utils.TimeFormat(time.Now(), "yyyyMMddHHmmss")
		onedata.Lastuser = t.Lastuser
		onedata.Credatuz = utils.TimeFormat(time.Now(), "yyyyMMddHHmmss")
		alldata = append(alldata, onedata)
	}
	_, err = e.Table("etltablst").Insert(&alldata)
	if err != nil {
		return false
	}
	return true
}

//查
func (t *Etltab) SelectOne() (Etltab, error) {
	es := db.Eloquent.Slaves()
	e := es[0]
	var data Etltab
	_, err := e.ID(core.PK{t.Finr, t.Eid}).Get(&data)
	if err != nil {
		return data, err
	}
	var datalist []Etltablst
	err = e.Where("finr = ? and eid = ?", t.Finr, t.Eid).Find(&datalist)
	if err != nil {
		return data, err
	}
	data.Valst = datalist
	return data, nil
}

//分页
func (t *Etltab) GetPage(pageSize int, pageIndex int) ([]Etltab, int, error) {
	data := make([]Etltab, 0)
	es := db.Eloquent.Slaves()
	e := es[0]
	table := e.Table("etltab").Where("finr = ? ", t.Finr)
	where := "where finr = " + "'" + utils.ValueToString(t.Finr, "") + "'"
	if t.Eid > 0 {
		table = table.And("eid = ?", t.Eid)
		where += " and eid = " + "'" + utils.ValueToString(t.Eid, "") + "'"
	}
	Offset := (pageIndex - 1) * pageSize
	err := e.SQL("SELECT TOP " + utils.ValueToString(pageSize, "") + " etltab.* FROM etltab " + where + " AND (convert(varchar(10),finr)+convert(varchar(40),eid) NOT IN (SELECT TOP " + utils.ValueToString(Offset, "") + " convert(varchar(10),finr)+convert(varchar(40),eid) FROM Etltab " + where + " ORDER BY eid DESC)) ORDER BY eid DESC").Find(&data)
	pcount := new(Etltab)
	count, err := table.Count(pcount)
	if err != nil {
		return data, 0, err
	}
	return data, int(count), nil
}

type EtlTabData struct {
	Tabmapcol string
	Coltype   string
}

//新增数据（根据表明查询）
func (t *Etltab) SelectArr() ([]Etltablst, error) {
	e := db.Eloquent.Master()
	datalist := make([]EtlTabData, 0)
	data := make([]Etltablst, 0)
	err := e.SQL("SELECT COLUMN_NAME as tabmapcol,DATA_TYPE as coltype FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=?", t.Fromtable).Find(&datalist)
	if err != nil {
		return nil, err
	}

	for _, v := range datalist {
		dataone := Etltablst{}
		dataone.Field = v.Tabmapcol
		if v.Coltype == "int" {
			dataone.Fieldtype = "1"
		} else if v.Coltype == "decimal" {
			dataone.Fieldtype = "2"
		} else {
			dataone.Fieldtype = "8"
		}
		data = append(data, dataone)
	}
	return data, err
}

//查询出所有得ETL服务
func (t *Etltab) SelectAll() ([]Etltab, error) {
	es := db.Eloquent.Slaves()
	e := es[0]
	data := make([]Etltab, 0)
	err := e.Table("etltab").Where("finr = ? and status != 1", t.Finr).Find(&data)
	if err != nil {
		return data, err
	}
	for k, v := range data {
		var datalist []Etltablst
		err = e.Where("finr = ? and eid = ?", v.Finr, v.Eid).Find(&datalist)
		if err != nil {
			continue
		}
		data[k].Valst = datalist
	}

	return data, nil
}

//改
func (t *Etltab) UpdateTime(search_time string) error {
	es := db.Eloquent.Slaves()
	e := es[0]
	_, err := e.Table("etltab").Where("finr = ? and eid = ?", t.Finr, t.Eid).Update(map[string]interface{}{"search_time": search_time})
	if err != nil {
		return err
	}
	return nil
}

//导入从库数据表
func (t *Etltab) ToLeadSlave(sql string) error {
	id := searchSlave(t.Todb, t.Todrivername)
	es := db.Eloquent.Slaves()
	e := es[id]
	_, err := e.Exec(sql)
	if err != nil {
		return err
	}
	return nil
}

//判断sql语句是否正确
func (t *Etltab) TestSql(sql string) ([]map[string]interface{}, error) {
	es := db.Eloquent.Slaves()
	e := es[0]
	data := make([]map[string]interface{}, 0)
	err := e.SQL(sql).Limit(5).Find(&data)
	if err != nil {
		return nil, err
	}
	return data, nil
}

//根据引擎和数据名称查询出对应的从库
func searchSlave(dbname string, drivername string) int {
	database := new(Database)
	data := database.FindData()
	tem := make(map[string]int)
	for _, v := range data {
		if v.Sourcetype == "Slave" {
			val := v.ID
			key := v.DbName + "_" + v.DriverName
			tem[key] = val
		}
	}
	key := dbname + "_" + drivername
	val, ok := tem[key]
	if ok {
		return val
	}
	return 0
}

//根据引擎和数据名称查询出对应的从库
func SearchDb(dbname string, drivername string, dbtype string) *xorm.Engine {
	switch dbtype {
	case "Master":
		e := db.Eloquent.Master()
		return e
	case "Slave":
		es := db.Eloquent.Slaves()
		database := new(Database)
		data := database.FindData()
		tem := make(map[string]int)
		for _, v := range data {
			if v.Sourcetype == "Slave" {
				val := v.ID
				key := v.DbName + "_" + v.DriverName
				tem[key] = val
			}
		}
		key := strings.TrimSpace(dbname) + "_" + strings.TrimSpace(drivername)
		val, ok := tem[key]
		if ok {
			return es[val]
		}
		return es[0]
	default:
		e := db.Eloquent.Master()
		return e
	}

}