Добавление кода вставки записей в таблицы

2 files changed, 81 insertions, 13 deletions

diff --git a/interface/database.go b/interface/database.go
index c911990..1d77827 100644
--- a/interface/database.go
+++ b/interface/database.go
@@ -4,6 +4,7 @@ import (
 	"database/sql"
 	"fmt"
 	_ "github.com/denisenkom/go-mssqldb"
+	"strings"
 )
 
 type SQLConnection struct {
@@ -72,6 +73,30 @@ func (conn *SQLConnection) getColumns(table string) ([]string, error) {
 	return result.Columns()
 }
 
+func (conn *SQLConnection) getIdentityColumns(table string) ([]string, error) {
+	result, err := conn.db.Query(
+		fmt.Sprintf(
+			`SELECT c.name FROM shipping.sys.objects o inner join shipping.sys.columns c on o.object_id = c.object_id
+			where c.is_identity = 1 and o.is_ms_shipped = 0 and o.name = N'%s'`, table))
+	if err != nil {
+		return nil, nil
+	}
+
+	var msg []string
+	cols, _ := result.Columns()
+	for result.Next() {
+		boxes := make([]string, len(cols))
+		dest := make([]interface{}, len(cols))
+		for i, _ := range dest {
+			dest[i] = &boxes[i]
+		}
+		_ = result.Scan(dest...)
+		msg = append(msg, boxes[0])
+	}
+	_ = result.Close()
+	return msg, nil
+}
+
 func (conn *SQLConnection) getTable(table string) ([][]string, error) {
 	var err error
 	var msg [][]string
@@ -98,7 +123,7 @@ func (conn *SQLConnection) getTable(table string) ([][]string, error) {
 
 func (conn *SQLConnection) getOneToManyTable(manyTable string, manyColumn string, oneValue string) ([][]string, error) {
 	result, err := conn.db.Query(fmt.Sprintf(
-		"SELECT * FROM %s WHERE %s=%s", manyTable, manyColumn, oneValue))
+		"SELECT * FROM %s WHERE %s=N'%s'", manyTable, manyColumn, oneValue))
 	if err != nil {
 		return nil, err
 	}
@@ -122,3 +147,12 @@ func (conn *SQLConnection) deleteWhere(table string, constraint string) error {
 	_, err := conn.db.Query(fmt.Sprintf("DELETE FROM %s WHERE %s", table, constraint))
 	return err
 }
+
+func (conn *SQLConnection) insertIntoTable(table string, columns []string, values []string) error {
+	_, err := conn.db.Query(fmt.Sprintf(
+		"INSERT INTO %s (%s) VALUES (N'%s')",
+		table,
+		strings.Join(columns, ","),
+		strings.Join(values, "',N'")))
+	return err
+}
diff --git a/interface/main.go b/interface/main.go
index df54c14..21e2c35 100644
--- a/interface/main.go
+++ b/interface/main.go
@@ -47,24 +47,58 @@ func parseQuery(box *fyne.Container) (string, error) {
 func (app *DBInterface) insertAdminView(table string) {
 	app.window.SetTitle(fmt.Sprintf("Вставка в таблицу [%s] (Режим администратора)", table))
 	columns, _ := app.db.getColumns(table)
+	identityCols, _ := app.db.getIdentityColumns(strings.Split(table, ".")[2])
 
-	var elements []fyne.CanvasObject
+	form := container.New(layout.NewFormLayout())
 	for _, val := range columns {
-		label := widget.NewLabel(val)
-		input := widget.NewEntry()
-		elements = append(elements, container.New(
-			layout.NewBorderLayout(nil, nil, label, nil),
-			label, input))
+		form.Add(widget.NewLabel(val))
+		entry := widget.NewEntry()
+		form.Add(entry)
+		for _, identity := range identityCols {
+			if identity == val {
+				entry.PlaceHolder = "Данная колонка является автозаполняемой"
+				entry.Disable()
+			}
+		}
 	}
+	form.Refresh()
 
-	insertBtn := widget.NewButton("Вставить", func() {})
+	insertBtn := widget.NewButton("Вставить", func() {
+		var insertColumns, insertValues []string
+		for i := 0; i < len(form.Objects); i += 2 {
+			column := form.Objects[i].(*widget.Label).Text
+			isIdentity := false
+			for _, identity := range identityCols {
+				if identity == column {
+					isIdentity = true
+					break
+				}
+			}
+			if isIdentity {
+				continue
+			}
+			insertColumns = append(insertColumns, column)
+			insertValues = append(insertValues, form.Objects[i+1].(*widget.Entry).Text)
+		}
+		err := app.db.insertIntoTable(table, insertColumns, insertValues)
+		if err != nil {
+			dialog.ShowInformation(
+				"Вставка",
+				err.Error(),
+				app.window)
+			return
+		}
+		dialog.ShowInformation(
+			"Вставка",
+			"Вставка выполнена успешно",
+			app.window)
+	})
 	backBtn := widget.NewButton("Назад", func() {
 		app.showAdminTable(table)
 	})
-	mainContainer := container.NewVBox(elements...)
 	app.window.SetContent(
 		genMainView(
-			container.NewVScroll(mainContainer),
+			container.NewVScroll(form),
 			[]fyne.CanvasObject{insertBtn, backBtn}))
 }
 
@@ -76,7 +110,7 @@ func (app *DBInterface) deleteAdminView(table string) {
 
 	newConstraintBtn := widget.NewButton("Добавить новое ограничение", func() {
 		selectBtn := widget.NewSelect(columns, func(value string) {})
-		operatorBtn := widget.NewSelect([]string{"=", "<", "<=", ">=", "LIKE"}, func(value string) {})
+		operatorBtn := widget.NewSelect([]string{"=", "<", ">", "<=", ">=", "LIKE"}, func(value string) {})
 		compareField := widget.NewEntry()
 		fieldConstraint := container.NewGridWithColumns(3, selectBtn, operatorBtn, compareField)
 		queryBox.Add(fieldConstraint)
@@ -109,7 +143,7 @@ func (app *DBInterface) deleteAdminView(table string) {
 
 			dialog.ShowInformation(
 				"Удаление",
-				"Удаление прошло успешно",
+				"Удаление выполнено успешно",
 				app.window)
 		}
 	})
@@ -177,7 +211,7 @@ func (app *DBInterface) selectAdminView(table string) {
 		selectBtn := widget.NewSelect(columns, func(value string) {
 			fmt.Println("Select set to", value)
 		})
-		operatorBtn := widget.NewSelect([]string{"=", "<", "<=", ">=", "LIKE"}, func(value string) {
+		operatorBtn := widget.NewSelect([]string{"=", "<", ">", "<=", ">=", "LIKE"}, func(value string) {
 			fmt.Println("Select set to", value)
 		})
 		compareField := widget.NewEntry()