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

Интернет ресурсы:

1. http://notepad-plus-plus.org/
2. http://firebirdsql.org/
3. http://www.codeblocks.org/

ПРИЛОЖЕНИЕ 1
Книга суммарного учета библиотечного фонда

ПРИЛОЖЕНИЕ 2
Карточка учета

ПРИЛОЖЕНИЕ 3
Журнал регистрации карточек

ПРИЛОЖЕНИЕ 4
Инвентарная книга

Приложение 5
Серверная часть комплекса

/* Частичный сценарий создания базы данных AutoStore */

SET SQL DIALECT 3;

SET NAMES WIN1251;

CREATE DATABASE 'C:\Db\Library.FDB'

USER 'SYSDBA' PASSWORD 'masterkey'

PAGE_SIZE 16384

DEFAULT CHARACTER SET WIN1251 COLLATION WIN1251;

/* Домены */

CREATE DOMAIN T_AMOUNT AS INTEGER;

CREATE DOMAIN T_AUTHOR AS VARCHAR(150);

CREATE DOMAIN T_BOOKKIND AS VARCHAR(30);

CREATE DOMAIN T_BOOKNAME AS VARCHAR(100);

CREATE DOMAIN T_CLASS AS VARCHAR(5);

CREATE DOMAIN T_DATE AS DATE;

CREATE DOMAIN T_DAY AS DATE;

CREATE DOMAIN T_DIR AS CHAR(1);

CREATE DOMAIN T_DOC AS VARCHAR(50);

CREATE DOMAIN T_FIRSTNAME AS VARCHAR(50);

CREATE DOMAIN T_ID AS INTEGER;

CREATE DOMAIN T_ISBN AS VARCHAR(20);

CREATE DOMAIN T_LASTNAME AS VARCHAR(50);

CREATE DOMAIN T_MIDNAME AS VARCHAR(50);

CREATE DOMAIN T_NAME_PUBLISHER AS VARCHAR(100);

CREATE DOMAIN T_NUMBERS AS VARCHAR(200);

CREATE DOMAIN T_TICKET AS VARCHAR(10);

CREATE DOMAIN T_YEAR AS INTEGER;

/* Генераторы */

CREATE GENERATOR GEN_BOOKMOVE_ID;

CREATE GENERATOR GEN_BOOK_ID;

CREATE GENERATOR GEN_PUBLISHER_ID;

CREATE GENERATOR GEN_READER_ID;

/* Таблицы */

CREATE TABLE BOOK (

BOOK_ID T_ID NOT NULL,

NAME T_BOOKNAME,

AUTHOR T_AUTHOR,

ID_PUBLISHER T_ID,

YEAROF T_YEAR,

KIND T_BOOKKIND,

ISBN T_ISBN,

AMOUNT T_AMOUNT,

NUMBERS T_NUMBERS

);

CREATE TABLE BOOKMOVE (

BOOKMOVE_ID T_ID NOT NULL,

BOOK_ID T_ID,

DAYOF T_DATE,

DOC T_DOC,

DIR T_DIR,

AMOUNT T_AMOUNT,

NUMBERS T_NUMBERS

);

CREATE TABLE PUBLISHER (

ID_PUBLISHER T_ID NOT NULL,

NAME_PUBLISHER T_NAME_PUBLISHER

);

CREATE TABLE READER (

READER_ID T_ID NOT NULL,

LASTNAME T_LASTNAME,

FIRSTNAME T_FIRSTNAME,

MIDNAME T_MIDNAME,

TICKET T_TICKET,

DAYOF T_DATE,

CLASS T_CLASS

);

/* Первичные ключи */

ALTER TABLE BOOK ADD CONSTRAINT PK_BOOK

PRIMARY KEY (BOOK_ID);

ALTER TABLE BOOKMOVE ADD CONSTRAINT PK_BOOKMOVE

PRIMARY KEY (BOOKMOVE_ID);

ALTER TABLE PUBLISHER ADD CONSTRAINT PK_PUBLISHER

PRIMARY KEY (ID_PUBLISHER);

ALTER TABLE READER ADD CONSTRAINT PK_READER

PRIMARY KEY (READER_ID);

/* Внешние ключи */

ALTER TABLE BOOKMOVE ADD CONSTRAINT FK_BOOKMOVE_1 FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID)

ON UPDATE CASCADE;

ALTER TABLE READING ADD CONSTRAINT FK_READING_1 FOREIGN KEY (READER_ID) REFERENCES READER (READER_ID)

ON UPDATE CASCADE;

ALTER TABLE READING ADD CONSTRAINT FK_READING_2 FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID)

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;