You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
324 lines
9.3 KiB
324 lines
9.3 KiB
package db
|
|
|
|
import (
|
|
"errors"
|
|
"github.com/go-xorm/xorm"
|
|
"leit.com/leit_seat_aps/common"
|
|
"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() {
|
|
common.TrimStruct(t, *t)
|
|
}
|
|
|
|
//增
|
|
func (t *Etltab) Add() error {
|
|
es := 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 = common.TimeFormat(time.Now(), "yyyyMMddHHmmss")
|
|
onedata.Lastuser = t.Lastuser
|
|
onedata.Credatuz = common.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 := 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 := 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 = common.TimeFormat(time.Now(), "yyyyMMddHHmmss")
|
|
onedata.Lastuser = t.Lastuser
|
|
onedata.Credatuz = common.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 := 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 := Eloquent.Slaves()
|
|
e := es[0]
|
|
table := e.Table("etltab").Where("finr = ? ", t.Finr)
|
|
where := "where finr = " + "'" + common.ValueToString(t.Finr, "") + "'"
|
|
if t.Eid > 0 {
|
|
table = table.And("eid = ?", t.Eid)
|
|
where += " and eid = " + "'" + common.ValueToString(t.Eid, "") + "'"
|
|
}
|
|
Offset := (pageIndex - 1) * pageSize
|
|
err := e.SQL("SELECT TOP " + common.ValueToString(pageSize, "") + " etltab.* FROM etltab " + where + " AND (convert(varchar(10),finr)+convert(varchar(40),eid) NOT IN (SELECT TOP " + common.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 := 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 := 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 := 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 := 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 := 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 := Eloquent.Master()
|
|
return e
|
|
case "Slave":
|
|
es := 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 := Eloquent.Master()
|
|
return e
|
|
}
|
|
|
|
}
|