Добавление нескольких отчётов, представлений для UPDATE и SELECT

2 files changed, 344 insertions, 72 deletions

diff --git a/interface/database.go b/interface/database.go
index 1d77827..71ee211 100644
--- a/interface/database.go
+++ b/interface/database.go
@@ -87,7 +87,7 @@ func (conn *SQLConnection) getIdentityColumns(table string) ([]string, error) {
 	for result.Next() {
 		boxes := make([]string, len(cols))
 		dest := make([]interface{}, len(cols))
-		for i, _ := range dest {
+		for i := range dest {
 			dest[i] = &boxes[i]
 		}
 		_ = result.Scan(dest...)
@@ -110,7 +110,7 @@ func (conn *SQLConnection) getTable(table string) ([][]string, error) {
 	for result.Next() {
 		boxes := make([]string, len(cols))
 		dest := make([]interface{}, len(cols))
-		for i, _ := range dest {
+		for i := range dest {
 			dest[i] = &boxes[i]
 		}
 		_ = result.Scan(dest...)
@@ -133,7 +133,7 @@ func (conn *SQLConnection) getOneToManyTable(manyTable string, manyColumn string
 	for result.Next() {
 		boxes := make([]string, len(cols))
 		dest := make([]interface{}, len(cols))
-		for i, _ := range dest {
+		for i := range dest {
 			dest[i] = &boxes[i]
 		}
 		_ = result.Scan(dest...)
@@ -143,11 +143,25 @@ func (conn *SQLConnection) getOneToManyTable(manyTable string, manyColumn string
 	return msg, nil
 }
 
-func (conn *SQLConnection) deleteWhere(table string, constraint string) error {
+func (conn *SQLConnection) deleteWhere(table string, constraints []string) error {
+	constraint := strings.Join(constraints, " AND ")
 	_, err := conn.db.Query(fmt.Sprintf("DELETE FROM %s WHERE %s", table, constraint))
 	return err
 }
 
+func (conn *SQLConnection) updateRow(table string, updateColumns []string, updateValues []string, id string) error {
+	var updates []string
+	for i := 0; i < len(updateColumns); i += 1 {
+		updates = append(updates, fmt.Sprintf("%s = N'%s'", updateColumns[i], updateValues[i]))
+	}
+	_, err := conn.db.Query(fmt.Sprintf(
+		"UPDATE %s SET %s WHERE id = %s",
+		table,
+		strings.Join(updates, ", "),
+		id))
+	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')",
@@ -156,3 +170,57 @@ func (conn *SQLConnection) insertIntoTable(table string, columns []string, value
 		strings.Join(values, "',N'")))
 	return err
 }
+
+func (conn *SQLConnection) selectWhere(table string, fields []string, constraints []string) ([][]string, []string, error) {
+	var err error
+	var result *sql.Rows
+	field := strings.Join(fields, ", ")
+	if len(constraints) == 0 {
+		result, err = conn.db.Query(fmt.Sprintf("SELECT %s FROM %s", field, table))
+	} else {
+		constraint := strings.Join(constraints, " AND ")
+		result, err = conn.db.Query(fmt.Sprintf("SELECT %s FROM %s WHERE %s", field, table, constraint))
+	}
+
+	if err != nil {
+		return nil, nil, err
+	}
+
+	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)
+	}
+	_ = result.Close()
+
+	return msg, cols, nil
+}
+
+func (conn *SQLConnection) executeSelect(query string) ([][]string, []string, error) {
+	result, err := conn.db.Query(query)
+
+	if err != nil {
+		return nil, nil, err
+	}
+
+	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)
+	}
+	_ = result.Close()
+
+	return msg, cols, nil
+}
diff --git a/interface/main.go b/interface/main.go
index 21e2c35..c550d0c 100644
--- a/interface/main.go
+++ b/interface/main.go
@@ -1,5 +1,6 @@
 package main
 
+import "C"
 import (
 	"errors"
 	"fmt"
@@ -23,7 +24,22 @@ func genMainView(view fyne.CanvasObject, buttons []fyne.CanvasObject) fyne.Canva
 		nil, container.NewGridWithColumns(len(buttons), buttons...), nil, nil, view)
 }
 
-func parseQuery(box *fyne.Container) (string, error) {
+func parseFields(box *fyne.Container) ([]string, error) {
+	var query []string
+	for _, obj := range box.Objects {
+		constraint := obj.(*fyne.Container).Objects
+		column := constraint[0].(*widget.Select)
+
+		if column.SelectedIndex() == -1 {
+			return nil, errors.New("не выбрано одно из полей")
+		}
+
+		query = append(query, column.Selected)
+	}
+	return query, nil
+}
+
+func parseQuery(box *fyne.Container) ([]string, error) {
 	var query []string
 	for _, obj := range box.Objects {
 		constraint := obj.(*fyne.Container).Objects
@@ -32,16 +48,16 @@ func parseQuery(box *fyne.Container) (string, error) {
 		value := constraint[2].(*widget.Entry)
 
 		if column.SelectedIndex() == -1 {
-			return "", errors.New("В одном из ограничений не выбрана колонка")
+			return nil, errors.New("в одном из ограничений не выбрана колонка")
 		}
 		if operator.SelectedIndex() == -1 {
-			return "", errors.New("В одном из ограничений не выбран оператор")
+			return nil, errors.New("в одном из ограничений не выбран оператор")
 		}
 
 		query = append(query,
 			fmt.Sprintf("%s %s '%s'", column.Selected, operator.Selected, value.Text))
 	}
-	return strings.Join(query, " AND "), nil
+	return query, nil
 }
 
 func (app *DBInterface) insertAdminView(table string) {
@@ -107,7 +123,6 @@ func (app *DBInterface) deleteAdminView(table string) {
 	columns, _ := app.db.getColumns(table)
 
 	queryBox := container.NewVBox()
-
 	newConstraintBtn := widget.NewButton("Добавить новое ограничение", func() {
 		selectBtn := widget.NewSelect(columns, func(value string) {})
 		operatorBtn := widget.NewSelect([]string{"=", "<", ">", "<=", ">=", "LIKE"}, func(value string) {})
@@ -165,62 +180,152 @@ func (app *DBInterface) selectAdminView(table string) {
 
 	columns, _ := app.db.getColumns(table)
 
-	var selectFields []fyne.CanvasObject
-	newField := widget.NewButton("Добавить новое поле", func() {})
+	selectFields := container.NewVBox()
+	newField := widget.NewButton("Добавить новое поле", func() {
+		selectFields.Add(
+			container.NewHBox(
+				widget.NewSelect(columns, func(value string) {}),
+				widget.NewLabel(",")))
+		selectFields.Refresh()
+	})
 
-	var query []fyne.CanvasObject
-	newConstraintBtn := widget.NewButton("Добавить новое ограничение", func() {})
-	updateView := func() {
-		var widgets []fyne.CanvasObject
-		widgets = append(widgets, widget.NewLabel("SELECT"))
-		for _, val := range selectFields {
-			widgets = append(widgets, container.NewHBox(val, widget.NewLabel(",")))
-		}
-		widgets = append(widgets, newField)
-		widgets = append(widgets, widget.NewLabel(fmt.Sprintf("FROM [%s] WHERE", table)))
+	queryBox := container.NewVBox()
+	newConstraintBtn := widget.NewButton("Добавить новое ограничение", func() {
+		selectBtn := widget.NewSelect(columns, func(value string) {})
+		operatorBtn := widget.NewSelect([]string{"=", "<", ">", "<=", ">=", "LIKE"}, func(value string) {})
+		compareField := widget.NewEntry()
+		fieldConstraint := container.NewGridWithColumns(3, selectBtn, operatorBtn, compareField)
+		queryBox.Add(fieldConstraint)
+		queryBox.Refresh()
+	})
 
-		for _, val := range query {
-			widgets = append(widgets, val)
-		}
-		widgets = append(widgets, newConstraintBtn)
+	backBtn := widget.NewButton("Назад", func() {
+		app.showAdminTable(table)
+	})
 
-		selectBtn := widget.NewButton("Показать результаты выбора", func() {
-			w := app.application.NewWindow("hello")
+	selectBtn := widget.NewButton("Показать результаты выбора", func() {
+		if len(selectFields.Objects) == 0 {
+			dialog.ShowInformation(
+				"Выбор",
+				"Не выбрано ни одного поля для запроса",
+				app.window)
+		} else {
+			w := app.application.NewWindow("")
 			w.Resize(fyne.NewSize(100, 100))
-			//conn := DBInterface{app.application, w, app.db}
+			conn := DBInterface{app.application, w, app.db}
+			conn.selectedShowAdmin(table, selectFields, queryBox)
 			w.Show()
-		})
-		backBtn := widget.NewButton("Назад", func() {
-			app.showAdminTable(table)
-		})
+		}
+	})
 
-		app.window.SetContent(
-			genMainView(
-				container.NewVScroll(container.NewVBox(widgets...)),
-				[]fyne.CanvasObject{selectBtn, backBtn}))
+	app.window.SetContent(
+		genMainView(
+			container.NewVScroll(
+				container.NewVBox(
+					widget.NewLabel("SELECT"),
+					selectFields,
+					newField,
+					widget.NewLabel(fmt.Sprintf("FROM [%s] WHERE", table)),
+					queryBox,
+					newConstraintBtn)),
+			[]fyne.CanvasObject{selectBtn, backBtn}))
+}
+
+func (app *DBInterface) updateRowAdminView(table string, rowData []string) {
+	app.window.SetTitle(fmt.Sprintf("Обновшение ряда таблицы [%s] (Режим администратора)", table))
+
+	columns, _ := app.db.getColumns(table)
+	identityCols, _ := app.db.getIdentityColumns(strings.Split(table, ".")[2])
+
+	identityIdx := -1
+	form := container.New(layout.NewFormLayout())
+	for i, val := range columns {
+		form.Add(widget.NewLabel(val))
+		entry := widget.NewEntry()
+		entry.SetText(rowData[i])
+		form.Add(entry)
+		for j, identity := range identityCols {
+			if identity == val {
+				identityIdx = j
+				entry.PlaceHolder = "Данная колонка является автозаполняемой"
+				entry.Disable()
+			}
+		}
 	}
+	form.Refresh()
 
-	newField.OnTapped = func() {
-		selectBtn := widget.NewSelect(columns, func(value string) {
-			fmt.Println("Select set to", value)
-		})
-		selectFields = append(selectFields, selectBtn)
-		updateView()
+	insertBtn := widget.NewButton("Изменить", func() {
+		var updateColumns, updateValues []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
+			}
+			updateColumns = append(updateColumns, column)
+			updateValues = append(updateValues, form.Objects[i+1].(*widget.Entry).Text)
+		}
+		err := app.db.updateRow(table, updateColumns, updateValues, rowData[identityIdx])
+		if err != nil {
+			dialog.ShowInformation(
+				"Изменение записи",
+				err.Error(),
+				app.window)
+			return
+		}
+		dialog.ShowInformation(
+			"Изменение записи",
+			"Изменение записи выполнено успешно",
+			app.window)
+	})
+	backBtn := widget.NewButton("Назад", func() {
+		app.showAdminTable(table)
+	})
+	app.window.SetContent(
+		genMainView(
+			container.NewVScroll(form),
+			[]fyne.CanvasObject{insertBtn, backBtn}))
+
+}
+
+func (app *DBInterface) selectedShowAdmin(table string, fields *fyne.Container, constraints *fyne.Container) {
+	app.window.SetTitle("Результаты выбора (Режим администратора)")
+
+	fieldsStr, _ := parseFields(fields)
+	query, _ := parseQuery(constraints)
+
+	data, columns, err := app.db.selectWhere(table, fieldsStr, query)
+	if err != nil {
+		fmt.Println(err)
+		return
 	}
-	newConstraintBtn.OnTapped = func() {
-		selectBtn := widget.NewSelect(columns, func(value string) {
-			fmt.Println("Select set to", value)
-		})
-		operatorBtn := widget.NewSelect([]string{"=", "<", ">", "<=", ">=", "LIKE"}, func(value string) {
-			fmt.Println("Select set to", value)
+
+	tableWidget := widget.NewTable(
+		func() (int, int) {
+			return len(data) + 1, len(columns)
+		},
+		func() fyne.CanvasObject {
+			return widget.NewLabel("some very wide content")
+		},
+		func(i widget.TableCellID, o fyne.CanvasObject) {
+			if i.Row == 0 {
+				o.(*widget.Label).Alignment = fyne.TextAlignCenter
+				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: true}
+				o.(*widget.Label).SetText(fmt.Sprintf("%v", columns[i.Col]))
+			} else {
+				o.(*widget.Label).Alignment = fyne.TextAlignLeading
+				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: false}
+				o.(*widget.Label).SetText(fmt.Sprintf("%v", data[i.Row-1][i.Col]))
+			}
 		})
-		compareField := widget.NewEntry()
 
-		fieldConstraint := container.NewGridWithColumns(3, selectBtn, operatorBtn, compareField)
-		query = append(query, fieldConstraint)
-		updateView()
-	}
-	updateView()
+	app.window.SetContent(container.NewMax(tableWidget))
 }
 
 func (app *DBInterface) oneToManyShowAdmin(manyTable string, manyColumn string, oneValue string) {
@@ -256,7 +361,7 @@ func (app *DBInterface) oneToManyShowAdmin(manyTable string, manyColumn string,
 
 func (app *DBInterface) oneToManyParamsAdmin(table string, row int) {
 	app.window.SetTitle("Выбор параметров отношения один ко многим (Режим администратора)")
-	app.window.Resize(fyne.NewSize(720, 480))
+	app.window.Resize(fyne.NewSize(700, 480))
 	oneColumns, _ := app.db.getColumns(table)
 	tables, _ := app.db.getTables()
 	data, _ := app.db.getTable(table)
@@ -351,6 +456,17 @@ func (app *DBInterface) showAdminTable(table string) {
 		app.selectAdminView(table)
 	})
 
+	updateBtn := widget.NewButton("Изменить", func() {
+		if selectedRow == -1 {
+			dialog.ShowInformation(
+				"Изменение записи",
+				"Не выбрана ни одна строка в таблице",
+				app.window)
+		} else {
+			app.updateRowAdminView(table, data[selectedRow])
+		}
+	})
+
 	insertBtn := widget.NewButton("Вставить", func() {
 		app.insertAdminView(table)
 	})
@@ -381,19 +497,19 @@ func (app *DBInterface) showAdminTable(table string) {
 	app.window.SetContent(
 		genMainView(
 			tableWidget,
-			[]fyne.CanvasObject{selectBtn, insertBtn, deleteBtn, oneToMany, back}))
+			[]fyne.CanvasObject{selectBtn, updateBtn, insertBtn, deleteBtn, oneToMany, back}))
 }
 
 func (app *DBInterface) adminView() {
 	app.window.SetTitle("Режим администратора")
 	tables, _ := app.db.getTables()
 
-	var tableBtns []fyne.CanvasObject
-	tableBtns = append(tableBtns, container.NewCenter(widget.NewLabel("Таблицы")))
+	var tableButtons []fyne.CanvasObject
+	tableButtons = append(tableButtons, container.NewCenter(widget.NewLabel("Таблицы")))
 	for _, val := range tables {
 		e := val
-		tableBtns = append(
-			tableBtns, widget.NewButton(
+		tableButtons = append(
+			tableButtons, widget.NewButton(
 				fmt.Sprintf("Показать %s", val),
 				func() {
 					app.showAdminTable(e)
@@ -401,20 +517,20 @@ func (app *DBInterface) adminView() {
 	}
 
 	views, _ := app.db.getViews()
-	var viewsBtns []fyne.CanvasObject
-	viewsBtns = append(viewsBtns, container.NewCenter(widget.NewLabel("Представления")))
+	var viewsButtons []fyne.CanvasObject
+	viewsButtons = append(viewsButtons, container.NewCenter(widget.NewLabel("Представления")))
 	for _, val := range views {
 		e := val
-		viewsBtns = append(
-			viewsBtns, widget.NewButton(
+		viewsButtons = append(
+			viewsButtons, widget.NewButton(
 				fmt.Sprintf("Показать %s", val),
 				func() {
 					app.showAdminTable(e)
 				}))
 	}
 
-	tablesScroll := container.NewVScroll(container.NewVBox(tableBtns...))
-	viewsScroll := container.NewVScroll(container.NewVBox(viewsBtns...))
+	tablesScroll := container.NewVScroll(container.NewVBox(tableButtons...))
+	viewsScroll := container.NewVScroll(container.NewVBox(viewsButtons...))
 
 	dataBox := container.NewGridWithColumns(2, tablesScroll, viewsScroll)
 	menuBtn := widget.NewButton("В меню", func() {
@@ -450,26 +566,114 @@ func (app *DBInterface) showUserTable(table string) {
 	app.window.SetContent(genMainView(list, []fyne.CanvasObject{back}))
 }
 
+func (app *DBInterface) showDataTable(data [][]string, columns []string) {
+	app.window.SetTitle("Таблица")
+	tableWidget := widget.NewTable(
+		func() (int, int) {
+			return len(data) + 1, len(columns)
+		},
+		func() fyne.CanvasObject {
+			return widget.NewLabel("some very wide content")
+		},
+		func(i widget.TableCellID, o fyne.CanvasObject) {
+			if i.Row == 0 {
+				o.(*widget.Label).Alignment = fyne.TextAlignCenter
+				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: true}
+				o.(*widget.Label).SetText(fmt.Sprintf("%v", columns[i.Col]))
+			} else {
+				o.(*widget.Label).Alignment = fyne.TextAlignLeading
+				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: false}
+				o.(*widget.Label).SetText(fmt.Sprintf("%v", data[i.Row-1][i.Col]))
+			}
+		})
+
+	app.window.SetContent(container.NewMax(tableWidget))
+}
+
+func (app *DBInterface) reportUserBoxes() {
+	entry := widget.NewEntry()
+	app.window.SetContent(
+		container.NewVBox(
+			widget.NewLabel("E-Mail Клиента"),
+			entry,
+			widget.NewButton("Построить таблицу", func() {
+				query := fmt.Sprintf(
+					`select B.id, B.order_id, B.warehouse_id, B.wrapping_id, B.volume, B.weight
+					FROM shipping.dbo.Boxes B
+					JOIN shipping.dbo.Orders O on O.id = B.order_id
+					JOIN shipping.dbo.Customers C on C.id = O.customer_id
+					WHERE email = N'%s'`, entry.Text)
+				data, cols, _ := app.db.executeSelect(query)
+				app.showDataTable(data, cols)
+			})))
+}
+
+func (app *DBInterface) reportUserAmount() {
+	entryEmail := widget.NewEntry()
+	entryStart := widget.NewEntry()
+	entryEnd := widget.NewEntry()
+	app.window.SetContent(
+		container.NewVBox(
+			widget.NewLabel("E-Mail Клиента"),
+			entryEmail,
+			widget.NewLabel("Начало периода"),
+			entryStart,
+			widget.NewLabel("Конец периода"),
+			entryEnd,
+			widget.NewButton("Построить таблицу", func() {
+				query := fmt.Sprintf(
+					`SELECT SUM(amount) as [Размер выплат]
+					FROM shipping.dbo.Payments P
+					JOIN shipping.dbo.Orders O on O.id = P.order_id
+					JOIN shipping.dbo.Customers C on C.id = O.customer_id
+					WHERE O.date >= '%s' AND O.date <= '%s' AND email = N'%s'`,
+					entryStart.Text, entryEnd.Text, entryEmail.Text)
+				data, cols, _ := app.db.executeSelect(query)
+				app.showDataTable(data, cols)
+			})))
+}
+
 func (app *DBInterface) userView() {
 	views, _ := app.db.getViews()
-	var viewsBtns []fyne.CanvasObject
-	viewsBtns = append(viewsBtns, container.NewCenter(widget.NewLabel("Представления")))
+	var viewsButtons []fyne.CanvasObject
+	viewsButtons = append(viewsButtons, container.NewCenter(widget.NewLabel("Представления")))
 	for _, val := range views {
 		e := val
-		viewsBtns = append(
-			viewsBtns, widget.NewButton(
+		viewsButtons = append(
+			viewsButtons, widget.NewButton(
 				fmt.Sprintf("Показать %s", val),
 				func() {
 					app.showUserTable(e)
 				}))
 	}
 
-	viewsScroll := container.NewVScroll(container.NewVBox(viewsBtns...))
+	reportButtons := container.NewVBox(
+		container.NewCenter(widget.NewLabel("Отчёты")),
+		widget.NewButton("Все коробки пользователя", func() {
+			w := app.application.NewWindow("")
+			w.Resize(fyne.NewSize(700, 480))
+			conn := DBInterface{app.application, w, app.db}
+			conn.reportUserBoxes()
+			w.Show()
+		}),
+		widget.NewButton("Размер выплат клиента за период", func() {
+			w := app.application.NewWindow("")
+			w.Resize(fyne.NewSize(700, 480))
+			conn := DBInterface{app.application, w, app.db}
+			conn.reportUserAmount()
+			w.Show()
+		}),
+	)
+
+	viewsScroll := container.NewVScroll(container.NewVBox(viewsButtons...))
+	reportsScroll := container.NewVScroll(reportButtons)
+
 	menuBtn := widget.NewButton("В меню", func() {
 		app.menu()
 	})
 
-	app.window.SetContent(container.NewBorder(nil, menuBtn, nil, nil, viewsScroll))
+	dataBox := container.NewGridWithColumns(2, viewsScroll, reportsScroll)
+	app.window.SetContent(genMainView(dataBox, []fyne.CanvasObject{menuBtn}))
 }
 
 func (app *DBInterface) menu() {