From f0dc98b93c12e6428d84533bc4cc42210522548f Mon Sep 17 00:00:00 2001 From: Andrew Guschin Date: Tue, 31 May 2022 11:58:02 +0400 Subject: =?UTF-8?q?=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD=D0=B8?= =?UTF-8?q?=D0=B5=20=D0=BD=D0=B5=D1=81=D0=BA=D0=BE=D0=BB=D1=8C=D0=BA=D0=B8?= =?UTF-8?q?=D1=85=20=D0=BE=D1=82=D1=87=D1=91=D1=82=D0=BE=D0=B2,=20=D0=BF?= =?UTF-8?q?=D1=80=D0=B5=D0=B4=D1=81=D1=82=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD?= =?UTF-8?q?=D0=B8=D0=B9=20=D0=B4=D0=BB=D1=8F=20UPDATE=20=D0=B8=20SELECT?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- interface/database.go | 76 ++++++++++- interface/main.go | 340 ++++++++++++++++++++++++++++++++++++++++---------- 2 files changed, 344 insertions(+), 72 deletions(-) (limited to 'interface') 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() { -- cgit v1.2.3