Файл: АРМ библиотекаря МБОУ «Мичуринская средняя общеобразовательная школа».pdf

ВУЗ: Не указан

Категория: Курсовая работа

Дисциплина: Не указана

Добавлен: 28.03.2023

Просмотров: 232

Скачиваний: 2

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.

ON UPDATE CASCADE;

/* Триггеры */

/* Trigger: BOOKMOVE_BI */

CREATE TRIGGER BOOKMOVE_BI FOR BOOKMOVE

ACTIVE BEFORE INSERT POSITION 0

as

begin

if (new.bookmove_id is null) then

new.bookmove_id = gen_id(gen_bookmove_id,1);

end;

/* Trigger: BOOK_BI */

CREATE TRIGGER BOOK_BI FOR BOOK

ACTIVE BEFORE INSERT POSITION 0

as

begin

if (new.book_id is null) then

new.book_id = gen_id(gen_book_id,1);

end;

/* Trigger: PUBLISHER_BI */

CREATE TRIGGER PUBLISHER_BI FOR PUBLISHER

ACTIVE BEFORE INSERT POSITION 0

As begin

if (new.id_publisher is null) then

new.id_publisher = gen_id(gen_publisher_id,1);

end;

/* Trigger: READER_BI */

CREATE TRIGGER READER_BI FOR READER

ACTIVE BEFORE INSERT POSITION 0

as

begin

if (new.reader_id is null) then

new.reader_id = gen_id(gen_reader_id,1);

end;

/* Хранимые процедуры */

CREATE OR ALTER PROCEDURE BOOK_DEL (

BOOK_ID TYPE OF COLUMN BOOK.BOOK_ID)

AS

begin

delete from book

where (book_id = :book_id);

end;

CREATE OR ALTER PROCEDURE BOOK_INS (

NAME TYPE OF COLUMN BOOK.NAME,

AUTHOR TYPE OF COLUMN BOOK.AUTHOR,

ID_PUBLISHER TYPE OF COLUMN BOOK.ID_PUBLISHER,

YEAROF TYPE OF COLUMN BOOK.YEAROF,

KIND TYPE OF COLUMN BOOK.KIND,

ISBN TYPE OF COLUMN BOOK.ISBN,

AMOUNT TYPE OF COLUMN BOOK.AMOUNT,

NUMBERS TYPE OF COLUMN BOOK.NUMBERS)

RETURNS (

ID TYPE OF COLUMN BOOK.BOOK_ID)

AS

begin

insert into book (

name,

author,

id_publisher,

yearof,

kind,

isbn,

amount,

numbers)

values (

:name,

:author,

:id_publisher,

:yearof,

:kind,

:isbn,

:amount,

:numbers)

returning book.book_id into :id;

suspend;

end;

CREATE OR ALTER PROCEDURE BOOK_SEL

RETURNS (

BOOK_ID TYPE OF COLUMN BOOK.BOOK_ID,

NAME TYPE OF COLUMN BOOK.NAME,

AUTHOR TYPE OF COLUMN BOOK.AUTHOR,

ID_PUBLISHER TYPE OF COLUMN BOOK.ID_PUBLISHER,

YEAROF TYPE OF COLUMN BOOK.YEAROF,

KIND TYPE OF COLUMN BOOK.KIND,

ISBN TYPE OF COLUMN BOOK.ISBN,

AMOUNT TYPE OF COLUMN BOOK.AMOUNT,

NUMBERS TYPE OF COLUMN BOOK.NUMBERS)

AS

begin

for select book_id,

name,

author,

id_publisher,

yearof,

kind,

isbn,

amount,

numbers

from book

into :book_id,

:name,

:author,

:id_publisher,

:yearof,

:kind,

:isbn,

:amount,

:numbers

do

begin

suspend;

end

end;

CREATE OR ALTER PROCEDURE BOOK_UPD (

BOOK_ID TYPE OF COLUMN BOOK.BOOK_ID,

NAME TYPE OF COLUMN BOOK.NAME,

AUTHOR TYPE OF COLUMN BOOK.AUTHOR,

ID_PUBLISHER TYPE OF COLUMN BOOK.ID_PUBLISHER,

YEAROF TYPE OF COLUMN BOOK.YEAROF,

KIND TYPE OF COLUMN BOOK.KIND,

ISBN TYPE OF COLUMN BOOK.ISBN,

AMOUNT TYPE OF COLUMN BOOK.AMOUNT,

NUMBERS TYPE OF COLUMN BOOK.NUMBERS)

AS

begin

update book

set name = :name,

author = :author,

id_publisher = :id_publisher,

yearof = :yearof,

kind = :kind,

isbn = :isbn,

amount = :amount,

numbers = :numbers

where (book_id = :book_id);

end;

CREATE OR ALTER PROCEDURE BOOKMOVE_DEL (

BOOKMOVE_ID TYPE OF COLUMN BOOKMOVE.BOOKMOVE_ID)

AS

begin

delete from bookmove

where (bookmove_id = :bookmove_id);

end;

ALTER PROCEDURE BOOKMOVE_INS (

BOOK_ID TYPE OF COLUMN BOOKMOVE.BOOK_ID,

DAYOF TYPE OF COLUMN BOOKMOVE.DAYOF,

DOC TYPE OF COLUMN BOOKMOVE.DOC,

DIR TYPE OF COLUMN BOOKMOVE.DIR,

AMOUNT TYPE OF COLUMN BOOKMOVE.AMOUNT,

NUMBERS TYPE OF COLUMN BOOKMOVE.NUMBERS)

RETURNS (

BOOKMOVE_ID TYPE OF COLUMN BOOKMOVE.BOOKMOVE_ID)

AS

begin

insert into bookmove (

book_id,

dayof,

doc,

dir,

amount,

numbers)

values (

:book_id,

:dayof,

:doc,

:dir,

:amount,

:numbers)

returning bookmove.bookmove_id into :bookmove_id;


suspend;

end;

CREATE OR ALTER PROCEDURE BOOKMOVE_SEL

RETURNS (

BOOKMOVE_ID TYPE OF COLUMN BOOKMOVE.BOOKMOVE_ID,

BOOK_ID TYPE OF COLUMN BOOKMOVE.BOOK_ID,

DAYOF TYPE OF COLUMN BOOKMOVE.DAYOF,

DOC TYPE OF COLUMN BOOKMOVE.DOC,

DIR TYPE OF COLUMN BOOKMOVE.DIR,

AMOUNT TYPE OF COLUMN BOOKMOVE.AMOUNT,

NUMBERS TYPE OF COLUMN BOOKMOVE.NUMBERS)

AS

begin

for select bookmove_id,

book_id,

dayof,

doc,

dir,

amount,

numbers

from bookmove

into :bookmove_id,

:book_id,

:dayof,

:doc,

:dir,

:amount,

:numbers

do

begin

suspend;

end

end;

CREATE OR ALTER PROCEDURE BOOKMOVE_UPD (

BOOKMOVE_ID TYPE OF COLUMN BOOKMOVE.BOOKMOVE_ID,

BOOK_ID TYPE OF COLUMN BOOKMOVE.BOOK_ID,

DAYOF TYPE OF COLUMN BOOKMOVE.DAYOF,

DOC TYPE OF COLUMN BOOKMOVE.DOC,

DIR TYPE OF COLUMN BOOKMOVE.DIR,

AMOUNT TYPE OF COLUMN BOOKMOVE.AMOUNT,

NUMBERS TYPE OF COLUMN BOOKMOVE.NUMBERS)

AS

begin

update bookmove

set book_id = :book_id,

dayof = :dayof,

doc = :doc,

dir = :dir,

amount = :amount,

numbers = :numbers

where (bookmove_id = :bookmove_id);

end;

CREATE OR ALTER PROCEDURE PUBLISHER_DEL (

ID_PUBLISHER TYPE OF COLUMN PUBLISHER.ID_PUBLISHER)

AS

begin

delete from publisher

where (id_publisher = :id_publisher);

end;

CREATE OR ALTER PROCEDURE PUBLISHER_INS (

NAME_PUBLISHER TYPE OF COLUMN PUBLISHER.NAME_PUBLISHER)

RETURNS (

ID TYPE OF COLUMN PUBLISHER.ID_PUBLISHER)

AS

begin

insert into publisher (

name_publisher)

values (

:name_publisher)

returning id_publisher into :id;

suspend;

end;

CREATE OR ALTER PROCEDURE PUBLISHER_SEL

RETURNS (

ID_PUBLISHER TYPE OF COLUMN PUBLISHER.ID_PUBLISHER,

NAME_PUBLISHER TYPE OF COLUMN PUBLISHER.NAME_PUBLISHER)

AS

begin

for select id_publisher,

name_publisher

from publisher

into :id_publisher,

:name_publisher

do

begin

suspend;

end

end;

CREATE OR ALTER PROCEDURE PUBLISHER_UPD (

ID_PUBLISHER TYPE OF COLUMN PUBLISHER.ID_PUBLISHER,

NAME_PUBLISHER TYPE OF COLUMN PUBLISHER.NAME_PUBLISHER)

AS

begin

update publisher

set name_publisher = :name_publisher

where (id_publisher = :id_publisher);

end;

CREATE OR ALTER PROCEDURE READER_DEL (

READER_ID TYPE OF COLUMN READER.READER_ID)

AS

begin

delete from reader

where (reader_id = :reader_id);

end;

CREATE OR ALTER PROCEDURE READER_INS (

LASTNAME TYPE OF COLUMN READER.LASTNAME,

FIRSTNAME TYPE OF COLUMN READER.FIRSTNAME,

MIDNAME TYPE OF COLUMN READER.MIDNAME,

TICKET TYPE OF COLUMN READER.TICKET,

DAYOF TYPE OF COLUMN READER.DAYOF,

CLASS TYPE OF COLUMN READER.CLASS)

RETURNS (

ID TYPE OF COLUMN READER.READER_ID)

AS

begin

insert into reader (

lastname,

firstname,

midname,

ticket,

dayof,

class)

values (

:lastname,

:firstname,

:midname,

:ticket,

:dayof,

:class)

returning reader.reader_id into :id;

suspend;

end;

CREATE OR ALTER PROCEDURE READER_SEL

RETURNS (

READER_ID TYPE OF COLUMN READER.READER_ID,

LASTNAME TYPE OF COLUMN READER.LASTNAME,

FIRSTNAME TYPE OF COLUMN READER.FIRSTNAME,

MIDNAME TYPE OF COLUMN READER.MIDNAME,

TICKET TYPE OF COLUMN READER.TICKET,

DAYOF TYPE OF COLUMN READER.DAYOF,

CLASS TYPE OF COLUMN READER.CLASS)

AS

begin

for select reader_id,

lastname,

firstname,

midname,

ticket,

dayof,

class

from reader

into :reader_id,

:lastname,

:firstname,

:midname,

:ticket,

:dayof,

:class

do

begin

suspend;

end

end;

CREATE OR ALTER PROCEDURE READER_UPD (

READER_ID TYPE OF COLUMN READER.READER_ID,

LASTNAME TYPE OF COLUMN READER.LASTNAME,

FIRSTNAME TYPE OF COLUMN READER.FIRSTNAME,

MIDNAME TYPE OF COLUMN READER.MIDNAME,


TICKET TYPE OF COLUMN READER.TICKET,

DAYOF TYPE OF COLUMN READER.DAYOF,

CLASS TYPE OF COLUMN READER.CLASS)

AS

begin

update reader

set lastname = :lastname,

firstname = :firstname,

midname = :midname,

ticket = :ticket,

dayof = :dayof,

class = :class

where (reader_id = :reader_id);

end;

CREATE OR ALTER PROCEDURE SP_GEN_BOOKMOVE_ID

RETURNS (

ID INTEGER)

AS

begin

id = gen_id(gen_bookmove_id, 1);

suspend;

end;

/* Описания */

DESCRIBE TABLE BOOKMOVE 'Поступление/выбытие';

DESCRIBE FIELD ID_PUBLISHER TABLE PUBLISHER 'ID Издательства';

DESCRIBE FIELD NAME_PUBLISHER TABLE PUBLISHER 'Название издательства';

Приложение 6
Клиентская часть комплекса

Файл pLibrary.dpr

program pLibrary;

uses

Forms,

frmLoginUnit in 'frmLoginUnit.pas' {frmLogin},

uMain in 'uMain.pas' {fMain},

uDm in 'uDm.pas' {dm: TDataModule},

frmAboutUnit in 'frmAboutUnit.pas' {frmAbout},

uCat in 'uCat.pas' {fCat},

uReader in 'uReader.pas' {fReader},

uReaderEd in 'uReaderEd.pas' {fReaderEd},

uPublisher in 'uPublisher.pas' {fPublisher},

uPublisherEd in 'uPublisherEd.pas' {fPublisherEd},

uBook in 'uBook.pas' {fBook},

uBookEd in 'uBookEd.pas' {fBookEd};

{$R *.res}

begin

Application.Initialize;

Application.CreateForm(Tdm, dm);

if not dm.ReConnectToDB then

Application.Terminate

else begin

Application.CreateForm(TfMain, fMain);

Application.CreateForm(TfPublisher, fPublisher);

Application.CreateForm(TfReader, fReader);

Application.CreateForm(TfCat, fCat);

Application.CreateForm(TfPublisherEd, fPublisherEd);

Application.CreateForm(TfReaderEd, fReaderEd);

Application.CreateForm(TfBookEd, fBookEd);

Application.Run;

end;

end.

unit uReader;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, Grids, DBGrids, Buttons, StdCtrls, ExtCtrls, DB, DBTables, Menus,

IBCustomDataSet, IBStoredProc, Mask;

type

TfReader = class(TForm)

Panel1: TPanel;

btEdit: TButton;

btNew: TButton;

btDelete: TButton;

DBGrid1: TDBGrid;

ds: TDataSource;

sp: TIBStoredProc;

btFind: TButton;

Panel2: TPanel;

Label1: TLabel;

MaskEdit1: TMaskEdit;

q: TIBDataSet;

qREADER_ID: TIntegerField;

qLASTNAME: TIBStringField;

qFIRSTNAME: TIBStringField;

qMIDNAME: TIBStringField;

qFIO: TIBStringField;

qTICKET: TIBStringField;

qDAYOF: TDateField;

qCLASS: TIBStringField;

procedure FormActivate(Sender: TObject);

procedure btEditClick(Sender: TObject);

procedure btDeleteClick(Sender: TObject);

procedure btNewClick(Sender: TObject);

procedure FormClose(Sender: TObject; var Action: TCloseAction);

procedure btFindClick(Sender: TObject);

procedure FormCreate(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

fReader: TfReader;

implementation

uses uDm, uReaderEd;

{$R *.dfm}

procedure TfReader.FormActivate(Sender: TObject);

// При активации формы

begin

dbGrid1.ReadOnly := true;

if ds.DataSet.Active then ds.DataSet.Close;

ds.DataSet.Open;

end;

procedure TfReader.btEditClick(Sender: TObject);

// Вызов формы редактирования и корректировка строки

var

id: integer;

begin

id := ds.DataSet.FieldByName( 'Reader_ID' ).AsInteger;

fReaderEd.LastName := ds.DataSet.FieldByName( 'LastName').AsString;

fReaderEd.FirstName := ds.DataSet.FieldByName( 'FirstName' ).AsString;

fReaderEd.MidName := ds.DataSet.FieldByName( 'MidName' ).AsString;

fReaderEd.ticket := ds.DataSet.FieldByName( 'ticket' ).AsString;

fReaderEd.dayOf := ds.DataSet.FieldByName( 'dayOf' ).AsDateTime;


fReaderEd.xClass := ds.DataSet.FieldByName( 'Class' ).AsString;

if fReaderEd.ShowModal = mrOK then begin

try

with sp do begin

if Active then Close;

StoredProcName := UpperCase('Reader_UPD');

with Params do begin

Clear;

CreateParam( ftInteger,'Reader_ID',ptInput ).AsInteger := ds.DataSet['Reader_ID'];

CreateParam( ftString, 'LastName', ptInput ).AsString := fReaderEd.LastName;

CreateParam( ftString, 'FirstName', ptInput ).AsString := fReaderEd.FirstName;

CreateParam( ftString, 'MidName', ptInput ).AsString := fReaderEd.MidName;

CreateParam( ftString, 'ticket', ptInput ).AsString := fReaderEd.ticket;

CreateParam( ftString, 'dayof', ptInput ).AsDate := fReaderEd.dayOf;

CreateParam( ftString, 'class', ptInput ).AsString := fReaderEd.xClass;

end; // with Params

Prepare;

ExecProc;

dm.trans.Commit;

end; // with sp

if not ds.DataSet.Active then

ds.DataSet.Open;

ds.DataSet.Locate('Reader_ID', id, [] );

except

ShowMessage('Ошибка редактирования!');

end;

end;

end;

procedure TfReader.btDeleteClick(Sender: TObject);

// Удаление строки

begin

if MessageDlg('Удалить', mtConfirmation, mbYesNoCancel, 0) <> mrYes then

Abort

else

with sp do begin

if Active then Close;

StoredProcName:= UpperCase('Reader_DEL');

with Params do begin

Clear;

CreateParam(ftInteger, 'Reader_ID', ptInput).AsInteger := ds.DataSet['Reader_ID'];

end; // with Params

try

Prepare;

ExecProc;

ds.DataSet.Close;

ds.DataSet.Open;

except

ShowMessage('Удаление запрещено!');

end; // with sp

end; // if

end;

procedure TfReader.btNewClick(Sender: TObject);

// Вызов формы редактирования и вставка строки

var

id: integer;

begin

fReaderEd.name := '';

if fReaderEd.ShowModal = mrOK then begin

try

with sp do begin

if Active then Close;

StoredProcName := UpperCase('Reader_INS');

with Params do begin

Clear;

CreateParam( ftString, 'LastName', ptInput ).AsString := fReaderEd.LastName;

CreateParam( ftString, 'FirstName', ptInput ).AsString := fReaderEd.FirstName;

CreateParam( ftString, 'MidName', ptInput ).AsString := fReaderEd.MidName;

CreateParam( ftString, 'ticket', ptInput ).AsString := fReaderEd.ticket;

CreateParam( ftString, 'dayof', ptInput ).AsDate := fReaderEd.dayOf;

CreateParam( ftString, 'class', ptInput ).AsString := fReaderEd.xClass;

CreateParam( ftInteger, 'Reader_ID', ptOutput ).AsInteger;

end; // with Params

Prepare;

ExecProc;

dm.trans.Commit;

id := sp.ParamByName( 'Reader_ID' ).AsInteger;

end; // with sp

ds.DataSet.Close;

ds.DataSet.Open;

ds.DataSet.Locate('Reader_ID', id, [] );

except

ShowMessage('Ошибка добавления!');

end;

end;

end;

procedure TfReader.FormClose(Sender: TObject; var Action: TCloseAction);

// При закрытии формы

begin

ds.DataSet.Close;

end;

procedure TfReader.btFindClick(Sender: TObject);

// Поиск по названию

begin

if ( MaskEdit1.Text <> '' ) then

begin

if not ds.DataSet.Locate('Lastname', MaskEdit1.Text, [loPartialKey]) then

ShowMessage('Не найдено');

end;

end;

procedure TfReader.FormCreate(Sender: TObject);

// При создании формы

begin

with q do begin

FieldByName( 'FIO').DisplayLabel := 'ФИО';

FieldByName( 'ticket').DisplayLabel := 'Билет';

FieldByName( 'dayOf').DisplayLabel := 'Дата';

FieldByName( 'Class').DisplayLabel := 'Класс';

end;

end;

end.

unit uReaderEd;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls, Mask, Buttons, ExtCtrls, DBCtrls, DB, DBTables,

ComCtrls;

type

TfReaderEd = class(TForm)

bbOk: TBitBtn;

MaskEdit1: TMaskEdit;

BitBtn1: TBitBtn;

Label1: TLabel;