Разработка базы данных и прикладного программного обеспечения для автобусного парка

/>

ShowMessage('Connection error!');

StatusBar.Panels[0].Text :=
'Disconnected';

end;

end;

procedure TfmMain.FormCreate(Sender:
TObject);

begin

fmMain.DBHost := 'ERETIKSQLEXPRESS';

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.