p align="left">limit 5 Заключение В связи со стремительно развивающимися на данный момент технологиями хранение информации на бумажных носителях отходит в прошлое, и повсеместно вводятся средства электронного учета. В курсовой работе предложен метод оперативного обслуживания БД применительно к небольшому автопарку. Подобный метод может быть применен в любой (практически) организации. Дальнейшее совершенствование программы возможно в сторону нормализации базы данных, добавления неких графических представлений, учёта различных состояний транспорта и т.д. При их внедрении программа может стать коммерческим продуктом, ориентированным на предприятия, занимающиеся доставкой корреспонденции, товаров народного потребления, курьерские службы, таксомоторные парки, автобусные и троллейбусные парки и т.п. Список использованной литературы 1. Структуризированный язык запросов (SQL). Учебное пособие 2. А.М. Терлецкая БД. Лекции. 3. П. Дарахвелидзе, Е. Марков. Программирование в Delphi 7. СПб.: «БХВ-Петербург». 2003. 784 с: ил. 4. М. Фленов. Библия программистов в среде Delphi. 2002. 636 с. 5. С. Лукьяненко. Спектр. М.: ООО «Издательство АСТ», ОАО «Люкс». 2004. 490 с. Приложение А. Листинг программы главная форма var DBHost, DBLogin, DBPassword, DBDatabase: string; implementation uses uConnectOptions, uRoutes, uStations, uStopPoints, uBuses; {$R *.dfm} procedure TfmMain.mmDBOptionsClick(Sender: TObject); begin Application.CreateForm(TfmConnectOptions, fmConnectOptions); fmConnectOptions.ShowModal; end; procedure TfmMain.mmDBConnectClick(Sender: TObject); begin try fmMain.ADOConnection.Open; StatusBar.Panels[0].Text := 'Connected'; mmDBConnect.Enabled := false; mmDBDisconnect.Enabled := true; ADOTableRoutes.Active := true; ADOQueryStations.Active := true; ADOQueryStopPoints.Active := true; ADOQueryBuses.Active := true; btnRoutesAdd.Enabled := true; btnStationsAdd.Enabled := true; btnStopPointsAdd.Enabled := true; btnBusesAdd.Enabled := true; ppmnRoutesAdd.Enabled := true; ppmnStationsAdd.Enabled := true; ppmnStopPointsAdd.Enabled := true; ppmnBusesAdd.Enabled := true; except ShowMessage('Connection error!'); StatusBar.Panels[0].Text := 'Disconnected'; end; end; procedure TfmMain.FormCreate(Sender: TObject); begin fmMain.DBHost := 'ERETIK\SQLEXPRESS'; fmMain.DBLogin := 'sa'; fmMain.DBPassword := '1'; fmMain.DBDatabase := 'Buzzz'; end; procedure TfmMain.mmDBDisconnectClick(Sender: TObject); begin ADOConnection.Close; mmDBDisconnect.Enabled := false; mmDBConnect.Enabled := true; StatusBar.Panels[0].Text := 'Disconnected'; ADOTableRoutes.Active := false; ADOQueryStations.Active := false; ADOQueryStopPoints.Active := false; ADOQueryBuses.Active := false; btnRoutesAdd.Enabled := false; btnStationsAdd.Enabled := false; btnStopPointsAdd.Enabled := false; btnBusesAdd.Enabled := false; btnRoutesEdit.Enabled := false; btnStationsEdit.Enabled := false; btnStopPointsEdit.Enabled := false; btnBusesEdit.Enabled := false; btnRoutesDelete.Enabled := false; btnStationsDelete.Enabled := false; btnStopPointsDelete.Enabled := false; ppmnRoutesAdd.Enabled := false; ppmnStationsAdd.Enabled := false; ppmnStopPointsAdd.Enabled := false; ppmnBusesAdd.Enabled := false; ppmnRoutesEdit.Enabled := false; ppmnStationsEdit.Enabled := false; ppmnStopPointsEdit.Enabled := false; ppmnBusesEdit.Enabled := false; ppmnRoutesDelete.Enabled := false; ppmnStationsDelete.Enabled := false; ppmnStopPointsDelete.Enabled := false; end; procedure TfmMain.btnRoutesAddClick(Sender: TObject); begin Application.CreateForm(TfmRoutes, fmRoutes); fmRoutes.EditFlag := false; fmRoutes.ShowModal; end; procedure TfmMain.dtsrcRoutesDataChange(Sender: TObject; Field: TField); begin btnRoutesEdit.Enabled := ADOTableRoutes.RecordCount <> 0; btnRoutesDelete.Enabled := ADOTableRoutes.RecordCount <> 0; ppmnRoutesEdit.Enabled := ADOTableRoutes.RecordCount <> 0; ppmnRoutesDelete.Enabled := ADOTableRoutes.RecordCount <> 0; end; procedure TfmMain.dtsrcStationsDataChange(Sender: TObject; Field: TField); begin btnStationsEdit.Enabled := ADOQueryStations.RecordCount <> 0; btnStationsDelete.Enabled := ADOQueryStations.RecordCount <> 0; ppmnStationsEdit.Enabled := ADOQueryStations.RecordCount <> 0; ppmnStationsDelete.Enabled := ADOQueryStations.RecordCount <> 0; end; procedure TfmMain.dtsrcStopPointsDataChange(Sender: TObject; Field: TField); begin btnStopPointsEdit.Enabled := ADOQueryStopPoints.RecordCount <> 0; btnStopPointsDelete.Enabled := ADOQueryStopPoints.RecordCount <> 0; ppmnStopPointsEdit.Enabled := ADOQueryStopPoints.RecordCount <> 0; ppmnStopPointsDelete.Enabled := ADOQueryStopPoints.RecordCount <> 0; end; procedure TfmMain.dtsrcBusesDataChange(Sender: TObject; Field: TField); begin btnBusesEdit.Enabled := ADOQueryBuses.RecordCount <> 0; btnBusesDelete.Enabled := ADOQueryBuses.RecordCount <> 0; ppmnBusesEdit.Enabled := ADOQueryBuses.RecordCount <> 0; ppmnBusesDelete.Enabled := ADOQueryBuses.RecordCount <> 0; end; procedure TfmMain.btnRoutesDeleteClick(Sender: TObject); begin ADOCommand.CommandText := 'DELETE FROM routes WHERE id='+DBGridRoutes.DataSource.DataSet.Fields[0].AsString;; ADOCommand.Execute; ADOCommand.CommandText := 'DELETE FROM stations WHERE routeid='+DBGridRoutes.DataSource.DataSet.Fields[0].AsString;; ADOCommand.Execute; ADOCommand.CommandText := 'DELETE FROM stoppoints WHERE routeid='+DBGridRoutes.DataSource.DataSet.Fields[0].AsString;; ADOCommand.Execute; ADOTableRoutes.Requery(); ADOQueryStations.Requery; ADOQueryStopPoints.Requery; end; procedure TfmMain.btnStationsDeleteClick(Sender: TObject); begin ADOCommand.CommandText := 'DELETE FROM stations WHERE id='+DBGridStations.DataSource.DataSet.Fields[0].AsString;; ADOCommand.Execute; ADOCommand.CommandText := 'DELETE FROM stoppoints WHERE stationid='+DBGridStations.DataSource.DataSet.Fields[0].AsString;; ADOCommand.Execute; ADOQueryStations.Requery; ADOQueryStopPoints.Requery; end; procedure TfmMain.btnStopPointsDeleteClick(Sender: TObject); begin ADOCommand.CommandText := 'DELETE FROM stoppoints WHERE id='+DBGridStopPoints.DataSource.DataSet.Fields[0].AsString;; ADOCommand.Execute; ADOQueryStopPoints.Requery; end; procedure TfmMain.btnBusesDeleteClick(Sender: TObject); begin ADOCommand.CommandText := 'DELETE FROM buses WHERE id='+DBGridBuses.DataSource.DataSet.Fields[0].AsString;; ADOCommand.Execute; ADOQueryBuses.Requery; end; procedure TfmMain.btnRoutesEditClick(Sender: TObject); begin Application.CreateForm(TfmRoutes, fmRoutes); fmRoutes.EditFlag := true; fmRoutes.ShowModal; end; procedure TfmMain.btnStationsAddClick(Sender: TObject); begin Application.CreateForm(TfmStations, fmStations); fmStations.EditFlag := false; fmStations.ShowModal; end; procedure TfmMain.btnStationsEditClick(Sender: TObject); begin Application.CreateForm(TfmStations, fmStations); fmStations.EditFlag := true; fmStations.ShowModal; end; procedure TfmMain.btnStopPointsEditClick(Sender: TObject); begin Application.CreateForm(TfmStopPoints, fmStopPoints); fmStopPoints.EditFlag := true; fmStopPoints.ShowModal; end; procedure TfmMain.btnStopPointsAddClick(Sender: TObject); begin Application.CreateForm(TfmStopPoints, fmStopPoints); fmStopPoints.EditFlag := false; fmStopPoints.ShowModal; end; procedure TfmMain.btnBusesAddClick(Sender: TObject); begin Application.CreateForm(TfmBuses, fmBuses); fmBuses.EditFlag := false; fmBuses.ShowModal; end; procedure TfmMain.btnBusesEditClick(Sender: TObject); begin Application.CreateForm(TfmBuses, fmBuses); fmBuses.EditFlag := true; fmBuses.ShowModal; end; procedure TfmMain.FormClose(Sender: TObject; var Action: TCloseAction); begin ADOConnection.Connected := false; end; end. настройки подключения procedure TfmConnectOptions.btnOKClick(Sender: TObject); begin fmMain.ADOConnection.ConnectionString := 'Provider=SQLOLEDB.1;Password=' + medPassword.Text + ';Persist Security Info=False;User ID=' + edLogin.Text + ';Initial Catalog=' + edDatabase.Text + ';Data Source=' + edHost.Text; fmMain.DBHost := edHost.Text; fmMain.DBLogin := edLogin.Text; fmMain.DBPassword := medPassword.Text; fmMain.DBDatabase := edDatabase.Text; Close; end; procedure TfmConnectOptions.btnCancelClick(Sender: TObject); begin Close; end; procedure TfmConnectOptions.FormCreate(Sender: TObject); begin edHost.Text := fmMain.DBHost; edLogin.Text := fmMain.DBLogin; medPassword.Text := fmMain.DBPassword; edDatabase.Text := fmMain.DBDatabase; end; добавление/редактирование автобусов private { Private declarations } public { Public declarations } EditFlag: boolean; end; var fmBuses: TfmBuses; implementation uses uMain; {$R *.dfm} procedure TfmBuses.FormShow(Sender: TObject); begin if EditFlag then begin Caption := 'Edit bus'; ledModel.Text := fmMain.DBGridBuses.DataSource.DataSet.Fields[1].AsString; ledIssue.Text := fmMain.DBGridBuses.DataSource.DataSet.Fields[2].AsString; lcmbbxRoute.KeyValue := fmMain.DBGridBuses.DataSource.DataSet.Fields[3].AsInteger; end else begin Caption := 'Add bus'; ledModel.Clear; ledIssue.Clear; lcmbbxRoute.KeyValue := 1; end; end; procedure TfmBuses.btnCancelClick(Sender: TObject); begin Close; end; procedure TfmBuses.btnOKClick(Sender: TObject); begin if (ledModel.Text <> '') and (lcmbbxRoute.Text <> '') and (ledIssue.Text <> '') then begin if EditFlag then begin fmMain.ADOCommand.CommandText := 'UPDATE buses SET model=''' + ledModel.Text +''', issue=''' + ledIssue.Text + ''', routeid=' + IntToStr(lcmbbxRoute.KeyValue) + ' WHERE id=' + fmMain.DBGridBuses.DataSource.DataSet.Fields[0].AsString; fmMain.ADOQueryBuses.Requery; end else begin fmMain.ADOCommand.CommandText := 'INSERT INTO buses (model, issue, routeid) VALUES (''' + ledModel.Text + ''', ''' + ledIssue.Text + ''', ' + IntToStr(lcmbbxRoute.KeyValue) + ')'; end; fmMain.ADOCommand.Execute; fmMain.ADOQueryBuses.Requery; close; end else ShowMessage('Incorrect parameters!'); end; end. добавление/редактирование маршрутов private { Private declarations } public { Public declarations } EditFlag: boolean; end; var fmRoutes: TfmRoutes; implementation uses uMain; {$R *.dfm} procedure TfmRoutes.btnCancelClick(Sender: TObject); begin Close; end; procedure TfmRoutes.btnOKClick(Sender: TObject); begin if ledName.Text <> '' then begin if EditFlag then begin fmMain.ADOCommand.CommandText := 'UPDATE routes SET name=''' + ledName.Text + ''' WHERE id=' + fmMain.DBGridRoutes.DataSource.DataSet.Fields[0].AsString; fmMain.ADOQueryStations.Requery; fmMain.ADOQueryStopPoints.Requery; fmMain.ADOQueryBuses.Requery; end else begin fmMain.ADOCommand.CommandText := 'INSERT INTO routes (name) VALUES (''' + ledName.Text + ''')'; end; fmMain.ADOCommand.Execute; fmMain.ADOTableRoutes.Requery; close; end else ShowMessage('Incorrect parameters!'); end; procedure TfmRoutes.FormShow(Sender: TObject); begin if EditFlag then begin Caption := 'Edit route'; ledName.Text := fmMain.DBGridRoutes.DataSource.DataSet.Fields[1].AsString; end else begin Caption := 'Add route'; ledName.Clear; end; end; end. добавление/редактирование остановок private { Private declarations } public { Public declarations } EditFlag: boolean; end; var fmStations: TfmStations; implementation uses uMain, DB; {$R *.dfm} procedure TfmStations.FormShow(Sender: TObject); begin if EditFlag then begin Caption := 'Edit station'; ledName.Text := fmMain.DBGridStations.DataSource.DataSet.Fields[1].AsString; lcmbbxRoute.KeyValue := fmMain.DBGridStations.DataSource.DataSet.Fields[2].AsInteger; end else begin Caption := 'Add station'; ledName.Clear; lcmbbxRoute.KeyValue := 1; end; end; procedure TfmStations.btnCancelClick(Sender: TObject); begin close; end; procedure TfmStations.btnOKClick(Sender: TObject); begin if (ledName.Text <> '') and (lcmbbxRoute.Text <> '') then begin if EditFlag then begin fmMain.ADOCommand.CommandText := 'UPDATE stations SET name=''' + ledName.Text + ''', routeid=' + IntToStr(lcmbbxRoute.KeyValue) + ' WHERE id=' + fmMain.DBGridStations.DataSource.DataSet.Fields[0].AsString; fmMain.ADOQueryStopPoints.Requery; end else begin fmMain.ADOCommand.CommandText := 'INSERT INTO stations (name, routeid) VALUES (''' + ledName.Text + ''', ' + IntToStr(lcmbbxRoute.KeyValue) + ')'; end; fmMain.ADOCommand.Execute; fmMain.ADOQueryStations.Requery; close; end else ShowMessage('Incorrect parameters!'); end; end. добавление/редактирование остановочных пунктов private { Private declarations } public { Public declarations } EditFlag: boolean; end; var fmStopPoints: TfmStopPoints; implementation uses uMain; {$R *.dfm} procedure TfmStopPoints.btnCancelClick(Sender: TObject); begin Close; end; procedure TfmStopPoints.FormShow(Sender: TObject); begin if EditFlag then begin Caption := 'Edit stop point'; with fmMain.DBGridStopPoints.DataSource.DataSet do begin ADOQueryRoutes.SQL.Text := 'SELECT * FROM routes WHERE id = ' + 'ANY (SELECT routeid FROM stations WHERE id = ' + Fields[3].AsString + ')'; ADOQueryRoutes.Active := true; ADOQueryRoutes.Requery; ledName.Text := Fields[1].AsString; lcmbbxRoute.KeyValue := dtsrcRoutes.DataSet.Fields[0].AsInteger; lcmbbxStation.KeyValue := Fields[3].AsInteger; if Fields[2].AsString = 'forward' then cmbbxDirection.ItemIndex := 0 else cmbbxDirection.ItemIndex := 1; end; end else begin Caption := 'Add stop point'; ledName.Clear; lcmbbxStation.KeyValue := 1; ADOQueryRoutes.SQL.Text := 'SELECT * FROM routes WHERE id = ' + 'ANY (SELECT routeid FROM stations WHERE id = 1)'; ADOQueryRoutes.Active := true; ADOQueryRoutes.Requery; end; end; procedure TfmStopPoints.lcmbbxStationCloseUp(Sender: TObject); begin ADOQueryRoutes.SQL.Text := 'SELECT * FROM routes WHERE id = ' + 'ANY (SELECT routeid FROM stations WHERE stations.id = ' + IntToStr(lcmbbxStation.KeyValue) + ')'; ADOQueryRoutes.Open; ADOQueryRoutes.Requery; lcmbbxRoute.DropDown; end; procedure TfmStopPoints.btnOKClick(Sender: TObject); begin if (ledName.Text <> '') and (lcmbbxRoute.Text <> '') and (lcmbbxStation.Text <> '') then begin if EditFlag then begin fmMain.ADOCommand.CommandText := 'UPDATE stoppoints SET name=''' + ledName.Text + ''', routeid=' + IntToStr(lcmbbxRoute.KeyValue) + ', direction=''' + cmbbxDirection.Text + ''', stationid=' + IntToStr(lcmbbxStation.KeyValue) + ' WHERE id=' + fmMain.DBGridStopPoints.DataSource.DataSet.Fields[0].AsString; end else begin fmMain.ADOCommand.CommandText := 'INSERT INTO stoppoints (name, direction, stationid, routeid) VALUES (''' + ledName.Text + ''', ''' + cmbbxDirection.Text + ''', ' + IntToStr(lcmbbxStation.KeyValue) + ', ' + IntToStr(lcmbbxRoute.KeyValue) + ')'; end; fmMain.ADOCommand.Execute; fmMain.ADOQueryStopPoints.Requery; close; end else ShowMessage('Incorrect parameters!'); end; end.
Страницы: 1, 2
|