Разработка программного продукта для реализации информационной работы автовокзала с использованием базы данных на основе SQL Server

select @loginlang = @@language

exec sp_addlogin N'Kassir01', null, @logindb, @loginlang

END

GO

 

/****** Object:  Login Manager    Script Date: 22.01.2008 2:22:39 ******/

if not exists (select * from master.dbo.syslogins where loginname = N'Manager')

BEGIN

declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'Autovokzaly', @loginlang = N'us_english'

if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)

select @logindb = N'master'

if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')

select @loginlang = @@language

exec sp_addlogin N'Manager', null, @logindb, @loginlang

END

GO

 

/****** Object:  Login Mechanic    Script Date: 22.01.2008 2:22:39 ******/

if not exists (select * from master.dbo.syslogins where loginname = N'Mechanic')

BEGIN

declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'Autovokzaly', @loginlang = N'us_english'

if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)

select @logindb = N'master'

if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')

select @loginlang = @@language

exec sp_addlogin N'Mechanic', null, @logindb, @loginlang

END

GO

 

/****** Object:  Login BUILTIN\Администраторы    Script Date: 22.01.2008 2:22:39 ******/

exec sp_addsrvrolemember N'BUILTIN\Администраторы', sysadmin

GO

 

/****** Object:  User Administrator    Script Date: 22.01.2008 2:22:39 ******/

if not exists (select * from dbo.sysusers where name = N'Administrator')

EXEC sp_grantdbaccess N'Administrator', N'Administrator'

GO

 

/****** Object:  User guest    Script Date: 22.01.2008 2:22:39 ******/

if not exists (select * from dbo.sysusers where name = N'guest' and hasdbaccess = 1)

EXEC sp_grantdbaccess N'guest'

GO

 

/****** Object:  User Kadry01    Script Date: 22.01.2008 2:22:39 ******/

if not exists (select * from dbo.sysusers where name = N'Kadry01')

EXEC sp_grantdbaccess N'Kadry01', N'Kadry01'

GO

 

/****** Object:  User Kassir01    Script Date: 22.01.2008 2:22:39 ******/

if not exists (select * from dbo.sysusers where name = N'Kassir01')

EXEC sp_grantdbaccess N'Kassir01', N'Kassir01'

GO

 

/****** Object:  User Manager    Script Date: 22.01.2008 2:22:39 ******/

if not exists (select * from dbo.sysusers where name = N'Manager')

EXEC sp_grantdbaccess N'Manager', N'Manager'

GO

 

/****** Object:  User Mechanic    Script Date: 22.01.2008 2:22:39 ******/

if not exists (select * from dbo.sysusers where name = N'Mechanic')

EXEC sp_grantdbaccess N'Mechanic', N'Mechanic'

GO

 

/****** Object:  User Administrator    Script Date: 22.01.2008 2:22:39 ******/

exec sp_addrolemember N'db_owner', N'Administrator'

GO

 

/****** Object:  Table [dbo].[AutoPark]    Script Date: 22.01.2008 2:22:40 ******/

CREATE TABLE [dbo].[AutoPark] (

[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,

[AutoVokzal] [int] NULL ,

[AutoNumber] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[Kol_voMest] [int] NULL ,

[Model] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL

) ON [PRIMARY]

GO

 

/****** Object:  Table [dbo].[AutoVokzal]    Script Date: 22.01.2008 2:22:40 ******/

CREATE TABLE [dbo].[AutoVokzal] (

[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,

[Region] [nchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,

[Sity] [nchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,

[Street] [nchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,

[Flat] [int] NULL ,

[Phone] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[Director] [nchar] (20) COLLATE Cyrillic_General_CI_AS NULL ,

[NAME] [nchar] (50) COLLATE Cyrillic_General_CI_AS NULL

) ON [PRIMARY]

GO

 

/****** Object:  Table [dbo].[Personal]    Script Date: 22.01.2008 2:22:40 ******/

CREATE TABLE [dbo].[Personal] (

[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,

[FirstName] [nchar] (20) COLLATE Cyrillic_General_CI_AS NULL ,

[SurName] [nchar] (20) COLLATE Cyrillic_General_CI_AS NULL ,

[Dolgnost] [nchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,

[Pol] [bit] NULL ,

[BirhtDay] [datetime] NULL ,

[INN] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[NumberPension] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[Region] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[Sity] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[Street] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[Flat] [int] NULL ,

[Phone] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[AvtoVokzal] [int] NULL ,

[LogonName] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[Password] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL

) ON [PRIMARY]

GO

 

/****** Object:  Table [dbo].[Reysi]    Script Date: 22.01.2008 2:22:40 ******/

CREATE TABLE [dbo].[Reysi] (

[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,

[Code] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[Data] [datetime] NULL ,

[Driver] [int] NULL ,

[SecondDriver] [int] NULL ,

[PunktOtpravlenia] [int] NULL ,

[PunktNaznachenia] [int] NULL ,

[Kontroler] [int] NULL ,

[NamberPlatformi] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,

[AutoVokzal] [int] NULL ,

[Avto] [int] NULL

) ON [PRIMARY]

GO

 

/****** Object:  Table [dbo].[Sity]    Script Date: 22.01.2008 2:22:40 ******/

CREATE TABLE [dbo].[Sity] (

[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,

[Name] [nchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,

[Region] [nchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,

[Map] [image] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

 

/****** Object:  Table [dbo].[Tikets]    Script Date: 22.01.2008 2:22:40 ******/

CREATE TABLE [dbo].[Tikets] (

[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,

[Plase] [int] NULL ,

[Tsena] [real] NULL ,

[Bagage] [bit] NULL ,

[Reys] [int] NOT NULL ,

[Zakaz] [int] NULL

) ON [PRIMARY]

GO

 

/****** Object:  Table [dbo].[Zakaz]    Script Date: 22.01.2008 2:22:40 ******/

CREATE TABLE [dbo].[Zakaz] (

[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,

[Date] [datetime] NULL ,

[Reys] [int] NULL ,

[FIO] [nchar] (50) COLLATE Cyrillic_General_CI_AS NULL ,

[TelephoneNumber] [nchar] (10) COLLATE Cyrillic_General_CI_AS NULL

) ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[AutoPark] WITH NOCHECK ADD

CONSTRAINT [PK_AutoPark] PRIMARY KEY  CLUSTERED

(

[ID]

)  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[AutoVokzal] WITH NOCHECK ADD

CONSTRAINT [PK_AutoVokzal] PRIMARY KEY  CLUSTERED

(

[ID]

)  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Personal] WITH NOCHECK ADD

CONSTRAINT [PK_Personal] PRIMARY KEY  CLUSTERED

(

[Id]

)  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Reysi] WITH NOCHECK ADD

CONSTRAINT [PK_Reysi] PRIMARY KEY  CLUSTERED

(

[ID]

)  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Sity] WITH NOCHECK ADD

CONSTRAINT [PK_Sity] PRIMARY KEY  CLUSTERED

(

[ID]

)  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Tikets] WITH NOCHECK ADD

CONSTRAINT [PK_Tikets] PRIMARY KEY  CLUSTERED

(

[ID]

)  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Zakaz] WITH NOCHECK ADD

CONSTRAINT [PK_Zakaz] PRIMARY KEY  CLUSTERED

(

[ID]

)  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[AutoPark] ADD

CONSTRAINT [FK_AutoPark_AutoVokzal] FOREIGN KEY

(

[AutoVokzal]

) REFERENCES [dbo].[AutoVokzal] (

[ID]

)

GO

 

ALTER TABLE [dbo].[Personal] ADD

CONSTRAINT [FK_Personal_AutoVokzal] FOREIGN KEY

(

[AvtoVokzal]

) REFERENCES [dbo].[AutoVokzal] (

[ID]

)

GO

 

ALTER TABLE [dbo].[Reysi] ADD

CONSTRAINT [FK_Reysi_AutoPark] FOREIGN KEY

(

[Avto]

) REFERENCES [dbo].[AutoPark] (

[ID]

),

CONSTRAINT [FK_Reysi_AutoVokzal] FOREIGN KEY

(

[AutoVokzal]

) REFERENCES [dbo].[AutoVokzal] (

[ID]

),

CONSTRAINT [FK_Reysi_Personal] FOREIGN KEY

(

[Driver]

) REFERENCES [dbo].[Personal] (

[Id]

),

CONSTRAINT [FK_Reysi_Personal1] FOREIGN KEY

(

[SecondDriver]

) REFERENCES [dbo].[Personal] (

[Id]

),

CONSTRAINT [FK_Reysi_Personal2] FOREIGN KEY

(

[Kontroler]

) REFERENCES [dbo].[Personal] (

[Id]

),

CONSTRAINT [FK_Reysi_Sity] FOREIGN KEY

(

[PunktOtpravlenia]

) REFERENCES [dbo].[Sity] (

[ID]

),

CONSTRAINT [FK_Reysi_Sity1] FOREIGN KEY

(

[PunktNaznachenia]

) REFERENCES [dbo].[Sity] (

[ID]

)

GO

 

ALTER TABLE [dbo].[Tikets] ADD

CONSTRAINT [FK_Tikets_Reysi] FOREIGN KEY

(

[Reys]

) REFERENCES [dbo].[Reysi] (

[ID]

),

CONSTRAINT [FK_Tikets_Zakaz] FOREIGN KEY

(

[Zakaz]

) REFERENCES [dbo].[Zakaz] (

[ID]

)

GO

 

ALTER TABLE [dbo].[Zakaz] ADD

CONSTRAINT [FK_Zakaz_Reysi] FOREIGN KEY

(

[Reys]

) REFERENCES [dbo].[Reysi] (

[ID]

)

GO

 

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

 

/****** Object:  Trigger dbo.DEL_reys    Script Date: 22.01.2008 2:22:40 ******/

CREATE trigger DEL_reys on dbo.Reysi

instead of delete

as

IF @@ROWCOUNT=1

BEGIN

  DECLARE @y INT

  SELECT @y=ID

  FROM deleted

  delete from Tikets where Reys=@y

  delete from Zakaz where Reys=@y

END

 

 

 

 

GO

 

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

 

 

Приложение 2 – Листинг  программы

 

program Mdiapp;

 

uses

  Forms,

  MAIN in 'MAIN.PAS' {MainForm},

  about in 'about.pas' {AboutBox},

  DataModule in 'DataModule.pas' {DataModule1: TDataModule},

  USity in 'USity.pas' {FSity},

  UAutovokzaly in 'UAutovokzaly.pas' {FAutovokzaly},

  UEditAutovokzal in 'UEditAutovokzal.pas' {FEditAutovokzal},

  UAutoPark in 'UAutoPark.pas' {FAutoPark},

  UEditAutopark in 'UEditAutopark.pas' {FEditAutopark},

  UPersonal in 'UPersonal.pas' {FPersonal},

  UEditPersonal in 'UEditPersonal.pas' {FEditPersonal},

  UReisy in 'UReisy.pas' {Freisy},

  UEditReys in 'UEditReys.pas' {FEditReis},

  UEditSity in 'UEditSity.pas' {FEditSity};

 

{$R *.RES}

 

begin

  Application.Initialize;

  Application.Title := 'Курсовая работа "Автовокзалы"';

  Application.CreateForm(TDataModule1, DataModule1);

  Application.CreateForm(TMainForm, MainForm);

  Application.CreateForm(TAboutBox, AboutBox);

  Application.Run;

end.

 

unit MAIN;

 

interface

 

uses Windows, SysUtils, Classes, Graphics, Forms, Controls, Menus,

  StdCtrls, Dialogs, Buttons, Messages, ExtCtrls, ComCtrls, StdActns,

  ActnList, ToolWin, ImgList, DBActns, DBClientActns, dxExEdtr, dxCntner,

  dxTL, dxDBCtrl, dxDBGrid, dxDBTL, ShellApi;

 

type

  TMainForm = class(TForm)

    procedure HelpAbout1Execute(Sender: TObject);

    procedure FileExit1Execute(Sender: TObject);

    procedure SityExecute(Sender: TObject);

    procedure AutovokzalysExecute(Sender: TObject);

    procedure AutoparkExecute(Sender: TObject);

    procedure PersonalExecute(Sender: TObject);

    procedure ReisyExecute(Sender: TObject);

    procedure SaveExcelExecute(Sender: TObject);

    procedure FormCreate(Sender: TObject);

  private

    { Private declarations }

 

  public

    { Public declarations }

  end;

 

var

  MainForm: TMainForm;

 

implementation

 

{$R *.dfm}

 

uses about, USity, UAutovokzaly, UAutoPark, UPersonal, UReisy, DataModule;

 

 

procedure TMainForm.HelpAbout1Execute(Sender: TObject);

begin

  AboutBox.ShowModal;

end;

 

procedure TMainForm.FileExit1Execute(Sender: TObject);

begin

  Close;

end;

 

procedure TMainForm.SityExecute(Sender: TObject);

begin

Application.CreateForm(TFSity,FSity);

FSity.Show

end;

 

procedure TMainForm.AutovokzalysExecute(Sender: TObject);

begin

Application.CreateForm(TFAutovokzaly,FAutovokzaly);

FAutovokzaly.Show

end;

 

procedure TMainForm.AutoparkExecute(Sender: TObject);

begin

Application.CreateForm(TFAutoPark,FAutoPark);

FAutoPark.Show

end;

 

procedure TMainForm.PersonalExecute(Sender: TObject);

begin

Application.CreateForm(TFPersonal,FPersonal);

FPersonal.Show

end;

 

procedure TMainForm.ReisyExecute(Sender: TObject);

begin

Application.CreateForm(TFreisy,Freisy);

Freisy.Show

end;

 

procedure TMainForm.SaveExcelExecute(Sender: TObject);

  var FName:String;

    AObject:String;

begin

//  with Sender as TdxDBGrid, TdxDBTreeList do begin

//        MessageDlg(FName, mtInformation, [mbOk], 0);

 

  if Screen.ActiveForm.ActiveControl <> nil then begin

    AObject := Screen.ActiveForm.ActiveControl.ClassName;

    if (AObject = 'TdxDBTreeList') or (AObject = 'TdxDBGrid') then begin

      if SaveDialog1.Execute then begin

        FName := SaveDialog1.FileName;

        if AObject = 'TdxDBGrid' then

          with Screen.ActiveForm.ActiveControl as TdxDBGrid do

            SaveToXLS(FName, False)

        else

          with Screen.ActiveForm.ActiveControl as TdxDBTreeList do

            SaveAllToTextFile(FName);

 

        if ShellExecute

        (Handle, 'open', @FName[1], nil, nil, SW_SHOWNORMAL)<32 then

          MessageDlg('Не буду !', mtInformation, [mbOk], 0)

        else

          MessageDlg('Создан файл '+FName, mtInformation, [mbOk], 0);

      end;

// WinExec()  CreateProcess()

    end;

  end;

 

end;

 

procedure TMainForm.FormCreate(Sender: TObject);

begin

DataModule1.MSConnection1.Connect

end;

 

end.

 

unit DataModule;

 

interface

 

uses

  SysUtils, Classes, DBAccess, SdacVcl, DB, MSAccess, MemDS;

 

type

    procedure AutovokzalyAfterInsert(DataSet: TDataSet);

    procedure AutoParkAfterInsert(DataSet: TDataSet);

    procedure PersonalAfterInsert(DataSet: TDataSet);

    procedure ReisyAfterInsert(DataSet: TDataSet);

  private

    { Private declarations }

  public

    { Public declarations }

  end;

 

var

  DataModule1: TDataModule1;

 

implementation

 

uses UEditAutovokzal, UEditAutopark, UEditPersonal, UEditReys, UReisy;

 

{$R *.dfm}

 

procedure TDataModule1.AutovokzalyAfterInsert(DataSet: TDataSet);

begin

FEditAutovokzal.ShowModal

end;

 

procedure TDataModule1.AutoParkAfterInsert(DataSet: TDataSet);

begin

FEditAutopark.ShowModal

end;

 

procedure TDataModule1.PersonalAfterInsert(DataSet: TDataSet);

begin

FEditPersonal.ShowModal

end;

 

procedure TDataModule1.ReisyAfterInsert(DataSet: TDataSet);

begin

FEditReis.ShowModal;

end;

 

end.