5. Физическая реализация
Серверная часть
/********************************************************/
/** Generated by IBExpert 2004.01.22 23.05.2004 20:38:17 ****/
/********************************************************/
SET SQL DIALECT 3;
SET NAMES WIN1251;
CREATE DATABASE 'Document:C:\Program Files\Borland\InterBase\bin\ELECTRDOC.GDB'
USER 'SYSDBA' PASSWORD 'administrator'
PAGE_SIZE 1024
DEFAULT CHARACTER SET WIN1251;
/*********************************************************/
/**** Generators ****/
/*********************************************************/
CREATE GENERATOR ARHIVN;
SET GENERATOR ARHIVN TO 16;
CREATE GENERATOR DOCN;
SET GENERATOR DOCN TO 17;
CREATE GENERATOR PODRAZDN;
SET GENERATOR PODRAZDN TO 4;
CREATE GENERATOR PROTOCOLN;
SET GENERATOR PROTOCOLN TO 52;
CREATE GENERATOR PROVERKIN;
SET GENERATOR PROVERKIN TO 13;
CREATE GENERATOR RABN;
SET GENERATOR RABN TO 19;
/*************************************************************//**** Exceptions ****/
/******************************************************/
CREATE EXCEPTION NODELETE 'Нельзяудалитьданногоработника';
CREATE EXCEPTION NOLOGIN 'Имя пользователя должно быть уникальным';
SET TERM ^ ;
/************************************************************/
/**** Stored Procedures ****/
/************************************************************/
CREATE PROCEDURE ADD_DOCUMENT (
NKLASS VARCHAR(7),
TEMA VARCHAR(30),
DATA DATE,
VID VARCHAR(15),
NAME VARCHAR(70))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE ADDDIRECTOR (
DATA DATE,
LOGIN VARCHAR(20),
FAMILY VARCHAR(20))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE ADDPODRAZDELENIE (
NAZV VARCHAR(70),
FIO VARCHAR(20))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE ADDPROTOCOL (
NUMDOC INTEGER)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE ADDPROVERKI (
NAZVPODR VARCHAR(70),
FIO VARCHAR(20),
OPISANIE VARCHAR(1000),
VID VARCHAR(15),
DATA DATE)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE CLEARARHIV
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE CLEARPROTOCOL
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE DELETEDIRECTOR (
FIO VARCHAR(20))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE DELETEDOC (
NUM INTEGER)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE DELETEPODRAZD (
NOMER INTEGER)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE DELETEPROVERKA (
NPROVERKI INTEGER)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE DELETERABOTNIK (
NUM INTEGER)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE EDITPROVERKI (
OPISANIE VARCHAR(1000),
NPROV INTEGER,
VID VARCHAR(15),
NAZVPODR VARCHAR(70),
FIO VARCHAR(20),
DATA DATE)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE NEW_PROCEDURE (
NAZV VARCHAR(70),
LOGIN VARCHAR(20),
FIO VARCHAR(20))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE UPDATEDOCUMENT (
IZMEN VARCHAR(30),
TEMA VARCHAR(30),
NUM INTEGER)
AS
BEGIN
EXIT;
END^
SET TERM ; ^/****************************************************************/
/**** Tables ****/
/************************************************************/
CREATE TABLE ARHIVDOC (
NDOC INTEGER NOT NULL,
NKLASS VARCHAR(7) NOT NULL,
NAIMENDOC VARCHAR(70) NOT NULL,
VIDDOC VARCHAR(15) NOT NULL,
DATAPRINYATIYA DATE NOT NULL,
DATADELETE DATE NOT NULL,
TEMATIKA VARCHAR(30),
IZMENENIYA VARCHAR(30)
);
CREATE TABLE DIRECTOR (
FIO VARCHAR(20) NOT NULL,
LOGIN VARCHAR(20) NOT NULL,
DATAVSTUPLENIYA DATE NOT NULL
);
CREATE TABLE DOCUMENT (
NDOC INTEGER NOT NULL,
NKLASS VARCHAR(7) NOT NULL,
NAIMENDOC VARCHAR(70) NOT NULL,
VIDDOC VARCHAR(15) NOT NULL,
DATAPRINYATIYA DATE NOT NULL,
DATAIZMEN DATE,
STATUS VARCHAR(10),
TEMATIKA VARCHAR(30),
IZMENENIYA VARCHAR(30)
);
CREATE TABLE PODRAZD (
NPODR INTEGER NOT NULL,
FIOPODR VARCHAR(20) NOT NULL,
NAZVANIE VARCHAR(70) NOT NULL
);
CREATE TABLE PROTOCOL (
NUMBER INTEGER NOT NULL,
DATA DATE NOT NULL,
NRAB INTEGER,
NDOC INTEGER,
VREMYA TIME NOT NULL
);
CREATE TABLE PROVERKI (
NPROVERKI INTEGER NOT NULL,
DATA DATE NOT NULL,
FIO VARCHAR(20) NOT NULL,
NPODR INTEGER NOT NULL,
VID VARCHAR(15) ,
OPISANIE VARCHAR(1000)
);
CREATE TABLE RABOTNIK (
NUMBER INTEGER NOT NULL,
FIO VARCHAR(20) NOT NULL,
LOGIN VARCHAR(20) NOT NULL,
NPODR INTEGER NOT NULL);/**************************************************************/
/**** Unique Constraints ****/
/*********************************************************/
ALTER TABLE DOCUMENT ADD CONSTRAINT UNQ_DOCUMENT UNIQUE (NKLASS, NAIMENDOC);
ALTER TABLE RABOTNIK ADD CONSTRAINT UNQ_RAB UNIQUE (LOGIN);
/************************************************************/
/**** Primary Keys ****/
/************************************************************/
ALTER TABLE ARHIVDOC ADD CONSTRAINT PK_ARHIVDOC PRIMARY KEY (NDOC);
ALTER TABLE DIRECTOR ADD CONSTRAINT PK_DIRECTOR PRIMARY KEY (FIO);
ALTER TABLE DOCUMENT ADD CONSTRAINT PK_DOCUMENT PRIMARY KEY (NDOC);
ALTER TABLE PODRAZD ADD CONSTRAINT PK_PODRAZD PRIMARY KEY (NPODR);
ALTER TABLE PROTOCOL ADD CONSTRAINT PK_PROTOCOL PRIMARY KEY (NUMBER);
ALTER TABLE PROVERKI ADD CONSTRAINT PK_PROVERKI PRIMARY KEY (NPROVERKI);
ALTER TABLE RABOTNIK ADD CONSTRAINT PK_RABOTNIK PRIMARY KEY (NUMBER);
/************************************************************/
/**** Foreign Keys ****/
/************************************************************/
ALTER TABLE PROTOCOL ADD CONSTRAINT FK_PROTOCOL_DOC FOREIGN KEY (NDOC) REFERENCES DOCUMENT (NDOC) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE PROTOCOL ADD CONSTRAINT FK_PROTOCOL_RAB FOREIGN KEY (NRAB) REFERENCES RABOTNIK (NUMBER) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE PROVERKI ADD CONSTRAINT FK_PROV_DIR FOREIGN KEY (FIO) REFERENCES DIRECTOR (FIO) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE PROVERKI ADD CONSTRAINT FK_PROV_PODRAZD FOREIGN KEY (NPODR) REFERENCES PODRAZD (NPODR) ON DELETE NO ACTION ON UPDATE CASCADE;
ALTER TABLE RABOTNIK ADD CONSTRAINT FK_RAB_PODR FOREIGN KEY (NPODR) REFERENCES PODRAZD (NPODR) ON DELETE NO ACTION ON UPDATE CASCADE;
/************************************************************/
/**** Triggers ****/
/************************************************************/
SET TERM ^ ;
/* Trigger: BI_ARHIV */
CREATE TRIGGER BI_ARHIV FOR ARHIVDOC
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* Trigger text */
NEW.ndoc= GEN_ID(arhivn,1);
end
^
/* Trigger: BI_DOCUMRNT */
CREATE TRIGGER BI_DOCUMRNT FOR DOCUMENT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* Trigger text */
NEW.ndoc= GEN_ID(docn,1);
end
^
/* Trigger: BI_PODRAZD */
CREATE TRIGGER BI_PODRAZD FOR PODRAZD
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* Trigger text */
NEW.npodr= GEN_ID(podrazdn,1);
end
^
/* Trigger: BI_PROTOCOL */
CREATE TRIGGER BI_PROTOCOL FOR PROTOCOL
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* Trigger text */
NEW.number= GEN_ID(protocoln,1);
end
^
/* Trigger: BI_PROVERKI */
CREATE TRIGGER BI_PROVERKI FOR PROVERKI
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* Trigger text */
NEW.nproverki= GEN_ID(proverkin,1);
end
^
/* Trigger: BI_RABOTNIK */
CREATE TRIGGER BI_RABOTNIK FOR RABOTNIK
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* Trigger text */
NEW.number= GEN_ID(rabn,1);
end
^
SET TERM ; ^
/***********************************************************/
/**** Stored Procedures ****/
/***********************************************************/
SET TERM ^ ;
ALTER PROCEDURE ADD_DOCUMENT (
NKLASS VARCHAR(7),
TEMA VARCHAR(30),
DATA DATE,
VID VARCHAR(15),
NAME VARCHAR(70))
AS
begin
/* Procedure Text */
insert into document(nklass,naimendoc,viddoc,dataprinyatiya,tematika)
values (:nklass,:name,:vid,:data,:tema);
end
^
ALTER PROCEDURE ADDDIRECTOR (
DATA DATE,
LOGIN VARCHAR(20),
FAMILY VARCHAR(20))
AS
begin
insert into director(FIO,LOGIN,DATAVSTUPLENIYA)
values (:family,:login,:data);
/* Procedure Text */ /*$$IBE$$
suspend;
$$IBE$$*/
end
^
ALTER PROCEDURE ADDPODRAZDELENIE (
NAZV VARCHAR(70),
FIO VARCHAR(20))
AS
begin
insert INTO podrazd(FIOPODR,NAZVANIE)
VALUES (:fio, :nazv);
end
^
ALTER PROCEDURE ADDPROTOCOL (
NUMDOC INTEGER)
AS
DECLARE VARIABLE NRAB INTEGER;
DECLARE VARIABLE USE VARCHAR(20);
DECLARE VARIABLE VREMYA TIME;
DECLARE VARIABLE DATA DATE;
begin
/* Procedure Text */
use = user;
data = 'TODAY';
vremya = 'NOW';
select NUMBER
FROM rabotnik
WHERE LOGIN =: use
INTO :NRAB;
INSERT INTO protocol(DATA ,VREMYA,NRAB,NDOC )
VALUES (:DATA,:VREMYA,:NRAB,:numdoc);
end
^
ALTER PROCEDURE ADDPROVERKI (
NAZVPODR VARCHAR(70),
FIO VARCHAR(20),
OPISANIE VARCHAR(1000),
VID VARCHAR(15),
DATA DATE)
AS
DECLARE VARIABLE PODR INTEGER;
begin
SELECT NPODR
FROM podrazd WHERE NAZVANIE=:NAZVPODR
INTO PODR ;
INSERT INTO PROVERKI(DATA, FIO, NPODR,VID,OPISANIE)
VALUES (:DATA,:FIO,:PODR,:VID,:OPISANIE);
end
^
ALTER PROCEDURE CLEARARHIV
AS
begin
/* Procedure Text */
delete from arhivdoc;
end
^
ALTER PROCEDURE CLEARPROTOCOL
AS
begin
/* Procedure Text */
delete from protocol;
end
^
ALTER PROCEDURE DELETEDIRECTOR (
FIO VARCHAR(20))
AS
begin
/* Procedure Text */
DELETE FROM director
where FIO=:fio;
end
^
ALTER PROCEDURE DELETEDOC (
NUM INTEGER)
AS
DECLARE VARIABLE IZMENENIYA VARCHAR(30) CHARACTER SET WIN1251;
DECLARE VARIABLE TEMATIKA VARCHAR(30) CHARACTER SET WIN1251;
DECLARE VARIABLE DATA DATE;
DECLARE VARIABLE VIDDOC VARCHAR(15) CHARACTER SET WIN1251;
DECLARE VARIABLE NAIMENDOC VARCHAR(70) CHARACTER SET WIN1251;
DECLARE VARIABLE NKLASS VARCHAR(7) CHARACTER SET WIN1251;
begin
/* Procedure Text */
select nklass,naimendoc,viddoc,dataprinyatiya,tematika,izmeneniya
from document
where ndoc = :num
into :nklass,:naimendoc,:viddoc,:data,:tematika,:izmeneniya ;
delete from document
where ndoc =:num;
insert into arhivdoc(nklass,naimendoc,viddoc,dataprinyatiya,datadelete,tematika,izmeneniya)
values(:nklass,:naimendoc,:viddoc,:data,'TODAY',:tematika,:izmeneniya);
end
^
ALTER PROCEDURE DELETEPODRAZD (
NOMER INTEGER)
AS
begin
DELETE from podrazd
WHERE NPODR=:nomer;
end
^
ALTER PROCEDURE DELETEPROVERKA (
NPROVERKI INTEGER)
AS
begin
/* Procedure Text */
delete FROM proverki
where NPROVERKI=:nproverki;
end
^
ALTER PROCEDURE DELETERABOTNIK (
NUM INTEGER)
AS
begin
DELETE FROM RABOTNIK
WHERE NUMBER =:NUM;
/* Procedure Text */
WHEN ANY do exception NODELETE;
end
^
ALTER PROCEDURE EDITPROVERKI (
OPISANIE VARCHAR(1000),
NPROV INTEGER,
VID VARCHAR(15),
NAZVPODR VARCHAR(70),
FIO VARCHAR(20),
DATA DATE)
AS
DECLARE VARIABLE PODR INTEGER;
begin
SELECT NPODR
FROM podrazd WHERE NAZVANIE=:NAZVPODR
INTO PODR ;
UPDATE PROVERKI
SET DATA=:DATA, FIO=:FIO, NPODR=:PODR,VID=:VID,OPISANIE=:OPISANIE
WHERE NPROVERKI =:NPROV ;
/* Procedure Text */
end
^
ALTER PROCEDURE NEW_PROCEDURE (
NAZV VARCHAR(70),
LOGIN VARCHAR(20),
FIO VARCHAR(20))
AS
DECLARE VARIABLE NUM INTEGER;
begin
/* Procedure Text */
select NPODR
FROM PODRAZD
WHERE NAZVANIE =:nazv
INTO NUM;
insert INTO RABOTNIK(FIO,LOGIN,NPODR)
VALUES (:fio, :login, :NUM );
end
^
ALTER PROCEDURE UPDATEDOCUMENT (
IZMEN VARCHAR(30),
TEMA VARCHAR(30),
NUM INTEGER)
AS
begin
/* Procedure Text */
UPDATE document
set DATAIZMEN = 'TODAY', STATUS='изменен',TEMATIKA=:TEMA,IZMENENIYA=:izmen
WHERE NDOC=:NUM;
end
^
SET TERM ; ^
Клиентскаячасть
unit Unit1;
interface
uses
Windows,RzLabel,RzCommon,Unit3,Unit4, DBCtrls, StdCtrls, Controls, Mask,
Grids, DBGrids, ComCtrls,
Classes, Messages, SysUtils, Variants, Graphics, Forms,
Dialogs,Unit2,Unit5,Unit6,Unit7, ExtCtrls, QuickRpt ;
type
TForm1 = class(TForm)
PageControl1: TPageControl;
TabSheet1: TTabSheet;
TabSheet2: TTabSheet;
TabSheet3: TTabSheet;
TabSheet4: TTabSheet;
TabSheet5: TTabSheet;
TabSheet6: TTabSheet;
TabSheet7: TTabSheet;
DBGrid1: TDBGrid;
Edit1: TEdit;
Button1: TButton;
Edit2: TEdit;
Button2: TButton;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
Label5: TLabel;
DBEdit1: TDBEdit;
MaskEdit1: TMaskEdit;
Label6: TLabel;
Label7: TLabel;
Label8: TLabel;
DBGrid2: TDBGrid;
MaskEdit2: TMaskEdit;
ComboBox1: TComboBox;
ComboBox2: TComboBox;
ComboBox3: TComboBox;
Label9: TLabel;
Label10: TLabel;
Label11: TLabel;
Label12: TLabel;
Label13: TLabel;
Label14: TLabel;
Label15: TLabel;
Label16: TLabel;
Label17: TLabel;
Button3: TButton;
DBEdit3: TDBEdit;
Label18: TLabel;
Button5: TButton;
DBGrid3: TDBGrid;
Edit3: TEdit;
Edit4: TEdit;
Label19: TLabel;
Label20: TLabel;
Label21: TLabel;
Button6: TButton;
DBEdit4: TDBEdit;
Label22: TLabel;
Button7: TButton;
Memo1: TMemo;
DBMemo1: TDBMemo;
Label23: TLabel;
Label24: TLabel;
Label25: TLabel;
Label26: TLabel;
Edit5: TEdit;
Edit6: TEdit;
ComboBox4: TComboBox;
Label27: TLabel;
Label28: TLabel;
Label29: TLabel;
Label30: TLabel;
Label31: TLabel;
Button4: TButton;
DBEdit2: TDBEdit;
Button8: TButton;
DBGrid4: TDBGrid;
DBGrid5: TDBGrid;
Button9: TButton;
Button10: TButton;
Button11: TButton;
Button12: TButton;
DBMemo2: TDBMemo;
Label32: TLabel;
DBEdit5: TDBEdit;
DBGrid6: TDBGrid;
Label33: TLabel;
Button13: TButton;
TabSheet8: TTabSheet;
DBGrid7: TDBGrid;
Button14: TButton;
Panel1: TPanel;
RadioButton1: TRadioButton;
RadioButton2: TRadioButton;
Button15: TButton;
procedure Button1Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button6Click(Sender: TObject);
procedure Button7Click(Sender: TObject);
procedure MaskEdit2Change(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
procedure Edit5Change(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure Button8Click(Sender: TObject);
procedure Button9Click(Sender: TObject);
procedure Button11Click(Sender: TObject);
procedure Button10Click(Sender: TObject);
procedure Button12Click(Sender: TObject);