Информационная система библиотеки

/*==============================================================*/

create index "saved_FK" on "saved" (

   "sCode" ASC

);

 

/*==============================================================*/

/* Index: "saved2_FK"                                           */

/*==============================================================*/

create index "saved2_FK" on "saved" (

   ISBN ASC

);

 

alter table "Copy"

   add constraint FK_COPY_LOCATED_PLACE foreign key ("plCode")

      references "Place" ("plCode");

 

alter table "Copy"

   add constraint FK_COPY_REPLACED_REPLACEM foreign key ("repCode")

      references "Replacement" ("repCode");

 

alter table "Copy"

   add constraint FK_COPY_THERE_IS_BOOK foreign key (ISBN)

      references "Book" (ISBN);

alter table "Copy"

   add constraint FK_COPY_WRITEOFF_WRITEOFF foreign key ("woCode")

      references "Writeoff" ("woCode");

 

alter table "Issuance"

   add constraint FK_ISSUANCE_ISSUED_COPY foreign key ("cNumber")

      references "Copy" ("cNumber");

 

alter table "Issuance"

   add constraint FK_ISSUANCE_TAKES_READER foreign key ("rNumber")

      references "Reader" ("rNumber");

 

alter table "saved"

   add constraint FK_SAVED_SAVED_SECTION foreign key ("sCode")

      references "Section" ("sCode");

 

alter table "saved"

   add constraint FK_SAVED_SAVED2_BOOK foreign key (ISBN)

      references "Book" (ISBN);

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  Приложение 2

SQL-скрипт хранимых процедур

CREATE OR REPLACE PROCEDURE PROCEDURE_INS_READER

(

  RNAME IN VARCHAR2 

, ADDRESS IN VARCHAR2 

, PHONE IN NUMBER 

, DATEB IN DATE 

) AS

BEGIN

  INSERT INTO "Reader" ("rNumber", "rFullName", "rAddress", "rPhone", "rData")

  VALUES(0, RNAME,ADDRESS,PHONE,DATEB);

END PROCEDURE_INS_READER;

 

CREATE OR REPLACE PROCEDURE PROCEDURE_INS_BOOK

(

  ISBN_ IN CHAR 

, BNAME IN VARCHAR2 

, AUTHOR IN VARCHAR2 

, PLACE IN VARCHAR2 

, PUBLISH IN VARCHAR2 

, BYEAR IN NUMBER 

, PAGES IN NUMBER

, SCODE IN NUMBER

) AS

BEGIN

  insert into "Book"(ISBN, "bName", "bAuthor", "bPlace", "bPublish", "bYear", "bPages")

  values(ISBN_, BNAME, AUTHOR, PLACE,  PUBLISH, BYEAR, PAGES);

  insert into "saved" ("sCode", ISBN)

  values(SCODE,ISBN_);

END PROCEDURE_INS_BOOK;

 

 

 

 

CREATE OR REPLACE PROCEDURE PROCEDURE_INS_COPY

(

  ISBN_ IN CHAR 

, PRICE IN NUMBER 

, DATEC IN DATE   

, PLCODE IN NUMBER

) AS

BEGIN

    insert into "Copy"("cNumber", ISBN, "plCode", "cCost", "cDate", "cLost")

    values(0, ISBN_, PLCODE, PRICE, DATEC, 0);

END PROCEDURE_INS_COPY;

 

CREATE OR REPLACE PROCEDURE PROCEDURE_ADD_SECTION

(

  ISBN_ IN CHAR 

, SECTION IN NUMBER 

) AS

BEGIN

  insert into "saved" ("sCode", ISBN)

  values(SECTION, ISBN_);

END PROCEDURE_ADD_SECTION;

 

CREATE OR REPLACE PROCEDURE PROCEDURE_EDIT_COPY

(

  ISBN_ IN CHAR 

, PRICE IN NUMBER 

, CDATE IN DATE 

, SECTION IN VARCHAR2 

, RACK IN NUMBER 

, SHELF IN NUMBER 

, SECTOR IN VARCHAR2 

) AS

num number(38,0);

BEGIN

 select P."plCode" into num from "Place" P

  where P."plSection" = SECTION AND P."plRack" = RACK AND P."plShelf" = SHELF AND P."plSector" = SECTOR;

  if(num IS NOT NULL) then

  begin

    update "Copy" SET  "plCode" = num, "cCost" = PRICE, "cDate" = CDATE

     where isbn = ISBN_;

  end;

  else

  begin

    insert into "Place"("plCode", "plSection", "plRack", "plShelf", "plSector")

    values(0, SECTION, RACK, SHELF, SECTOR);

     update "Copy" SET  "plCode" = (select max("plCode") from "Place") , "cCost" = PRICE, "cDate" = CDATE

     where isbn = ISBN_;

  end; end if;

END PROCEDURE_EDIT_COPY;

 

CREATE OR REPLACE PROCEDURE PROCEDURE_EDIT_BOOK

(

  ISBN_ IN CHAR 

, BNAME IN VARCHAR2 

, AUTHOR IN VARCHAR2 

, PLACE IN VARCHAR2 

, PUBLISH IN VARCHAR2 

, BYEAR IN NUMBER 

, PAGES IN NUMBER 

) AS

BEGIN

 update "Book" SET "bName" = BNAME, "bAuthor" =  AUTHOR, "bPlace" = PLACE, "bPublish" = PUBLISH, "bYear" = BYEAR, "bPages" = PAGES

  where isbn = isbn_;

END PROCEDURE_EDIT_BOOK;

 

 

 

 

 

 

CREATE OR REPLACE PROCEDURE PROCEDURE_EDIT_READER

(

  RID IN NUMBER 

, RNAME IN VARCHAR2 

, ADDRESS IN VARCHAR2 

, PHONE IN NUMBER 

, BDATE IN DATE 

) AS

BEGIN

  update "Reader" SET "rFullName" = RNAME, "rAddress" = ADDRESS, "rPhone" = PHONE, "rData" = BDATE

  where "rNumber" = rid;

END PROCEDURE_EDIT_READER;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Приложение 3

Тексты  основных модулей программы

 

Класс Program

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.OracleClient;

 

namespace DBLibrary

{

    static class Program

    {

        /// <summary>

        /// Главная  точка входа для приложения.

        /// </summary>

        [STAThread]

        static void Main()

        {

            Application.EnableVisualStyles();

            Application.SetCompatibleTextRenderingDefault(false);

 

            OracleConnection OC = new OracleConnection();

            OC.ConnectionString = "Data source=XE; User id=system; Password=2310823";

            try

            {

                OC.Open();

            }

            catch (Exception ex)

            {

                MessageBox.Show("Failed! " + ex.Message);

                goto final;

            }

 

            try

            {

                while (true)

                {

                    SelectMode SM = new SelectMode();

                    SM.ShowDialog();

                    if (SM.DialogResult == DialogResult.Yes)

                        Application.Run(new AReader(OC));

                    else if (SM.DialogResult == DialogResult.No)

                        Application.Run(new Librarian(OC));

                    else if (SM.DialogResult == DialogResult.Ignore)

                        Application.Run(new Administration(OC));

                    else

                        break;

                }

            }

            catch(Exception ex)

            {

                MessageBox.Show(ex.Message);

                goto final;

            }

 

final:

            OC.Close();

            OC.Dispose();

        }

    }

}

 

Класс SelectMode

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

 

namespace DBLibrary

{

    public partial class SelectMode : Form

    {

        public SelectMode()

        {

            InitializeComponent();

            radioButton1.Checked = false;

            radioButton2.Checked = false;

            radioButton3.Checked = false;

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            if(radioButton1.Checked == true)

                this.DialogResult = System.Windows.Forms.DialogResult.Yes;

            else if(radioButton2.Checked == true)

                this.DialogResult = System.Windows.Forms.DialogResult.No;

            else if(radioButton3.Checked == true)

                 this.DialogResult = System.Windows.Forms.DialogResult.Ignore;

            this.Close();

        }

 

        private void radioButton1_MouseDown(object sender, MouseEventArgs e)

        {

            radioButton1.Checked = true;

            radioButton2.Checked = false;

            radioButton3.Checked = false;

        }

 

        private void radioButton2_MouseDown(object sender, MouseEventArgs e)

        {

            radioButton1.Checked = false;

            radioButton2.Checked = true;

            radioButton3.Checked = false;

        }

 

        private void radioButton3_MouseDown(object sender, MouseEventArgs e)

        {

            radioButton1.Checked = false;

            radioButton2.Checked = false;

            radioButton3.Checked = true;

        }

       

    }

}

 

Класс Reader

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.OracleClient;

 

namespace DBLibrary

{

    public partial class Reader : Form

    {

        private AReader AR;

        private int cNumber;

        private string isbn;

        private bool flag;

        private bool clc;

 

        public Reader(AReader A)

        {

            InitializeComponent();

            AR = A;

            this.Text = this.Text +" "+ AR.rName;

            flag = false;

            clc = false;

        }

 

        private void Reader_FormClosed(object sender, FormClosedEventArgs e)

        {

            AR.Close();

        }

 

        private void Reader_Load(object sender, EventArgs e)

        {

            DataTable tab;

            OracleCommand Comand = AR.OC.CreateCommand();

            Comand.CommandText = "SELECT B.* FROM \"Book\" B INNER JOIN \"Copy\" C ON B.ISBN = C.ISBN AND C.\"cLost\" = 0 AND  C.\"woCode\" IS NULL " +

                "INNER JOIN \"Issuance\" I ON I.\"cNumber\" = C.\"cNumber\" AND I.\"iFactReturn\" IS NULL "+

                "INNER JOIN \"Reader\" R ON R.\"rNumber\" = I.\"rNumber\" AND R.\"rNumber\" = (select to_number('"+AR.rNumber+"')from dual)";

            tab = new DataTable();

            tab.Load(Comand.ExecuteReader());

            if (tab.Rows.Count == 5)

            {

                MessageBox.Show("Вы взяли уже пять  книг и не можете выбирать  новые");

                return;

            }

 

            Comand.CommandText = "SELECT \"sCode\", \"sName\" FROM \"Section\"";

            tab = new DataTable();

            tab.Load(Comand.ExecuteReader());

            comboBox1.DataSource = tab;

            comboBox1.ValueMember = "sCode";

            comboBox1.DisplayMember = "sName";

 

            Comand.CommandText = "SELECT B.* FROM \"Book\" B, \"saved\" s WHERE B.ISBN = s.ISBN AND s.\"sCode\" = " +

                comboBox1.SelectedValue.ToString();

            tab = new DataTable();

            tab.Load(Comand.ExecuteReader());

            dataGridView1.DataSource = tab.DefaultView;

 

        }

        private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)

        {

            OracleCommand Comand = AR.OC.CreateCommand();

            DataTable tab = new DataTable();

            Comand.CommandText = "SELECT B.* FROM \"Book\" B INNER JOIN \"Copy\" C ON B.ISBN = C.ISBN AND B.ISBN = (select to_char('" + dataGridView1[0, e.RowIndex].Value + "') from dual) AND C.\"cLost\" = 0 AND  C.\"woCode\" IS NULL " +

                "INNER JOIN \"Issuance\" I ON I.\"cNumber\" = C.\"cNumber\" AND I.\"iFactReturn\" IS NULL "+

                "INNER JOIN \"Reader\" R ON R.\"rNumber\" = I.\"rNumber\" AND R.\"rNumber\" = (select to_number('"+AR.rNumber+"')from dual)";

            tab.Load(Comand.ExecuteReader());

            if (tab.Rows.Count != 0)

            {

                MessageBox.Show("Вы уже выбрали эту  книгу");

            }

 

            tab = new DataTable();

            Comand.CommandText = "SELECT C.\"cNumber\" AS Номер, P.\"plSection\" AS Отдел,P.\"plRack\" AS Стеллаж,P.\"plShelf\" AS Полка,P.\"plSector\" AS Сектор " +

                "FROM \"Book\" B INNER JOIN \"Copy\" C ON B.ISBN = C.ISBN AND B.ISBN = (select to_char('" + dataGridView1[0, e.RowIndex].Value + "') from dual) AND  C.\"cLost\" = 0  AND  C.\"woCode\" IS NULL " +

                "INNER JOIN \"Place\" P ON C.\"plCode\" = P.\"plCode\" "+

                "where (select count(I.\"iNumber\") from \"Issuance\" I where I.\"cNumber\" = C.\"cNumber\" GROUP by C.\"cNumber\") IS NULL";

            tab.Load(Comand.ExecuteReader());

            if (tab.Rows.Count == 0)

            {

                tab = new DataTable();

                Comand.CommandText = "SELECT C.\"cNumber\" AS Номер, P.\"plSection\" AS Отдел,P.\"plRack\" AS Стеллаж,P.\"plShelf\" AS Полка,P.\"plSector\" AS Сектор " +

                    "FROM \"Book\" B INNER JOIN \"Copy\" C ON B.ISBN = C.ISBN AND B.ISBN = (select to_char('" + dataGridView1[0, e.RowIndex].Value + "') from dual) AND  C.\"cLost\" = 0  AND  C.\"woCode\" IS NULL " +

                    "INNER JOIN \"Place\" P ON C.\"plCode\" = P.\"plCode\" " +

                    "INNER JOIN \"Issuance\" I ON I.\"cNumber\" = C.\"cNumber\" AND I.\"iFactReturn\" IS NOT NULL " +

                    "AND I.\"iIssuance\"=(select max(I2.\"iIssuance\") from \"Book\" B2 INNER JOIN \"Copy\" C2 ON B2.ISBN = C2.ISBN AND B2.ISBN = (select to_char('" + dataGridView1[0, e.RowIndex].Value + "') from dual) AND  C2.\"cLost\" = 0  AND  C2.\"woCode\" IS NULL " +

                    "INNER JOIN \"Place\" P2 ON C2.\"plCode\" = P2.\"plCode\" " +

                    "INNER JOIN \"Issuance\" I2 ON I2.\"cNumber\" = C2.\"cNumber\" " +

                    "WHERE C2.\"cNumber\"=C.\"cNumber\"" +

                    "GROUP BY C2.\"cNumber\")";

                tab.Load(Comand.ExecuteReader());

                if (tab.Rows.Count == 0)

                {

                    Comand.CommandText = "SELECT C.\"cNumber\" AS Номер, I.\"iExpReturn\" AS Дата_возврата " +

                   "FROM \"Book\" B INNER JOIN \"Copy\" C ON B.ISBN = C.ISBN AND B.ISBN = (select to_char('" + dataGridView1[0, e.RowIndex].Value.ToString() + "') from dual) AND  C.\"cLost\" = 0  AND  C.\"woCode\" IS NULL " +

                   "INNER JOIN \"Place\" P ON C.\"plCode\" = P.\"plCode\" " +

                   "INNER JOIN \"Issuance\" I ON I.\"cNumber\" = C.\"cNumber\" AND I.\"iFactReturn\" IS NULL";

                    tab = new DataTable();

                    tab.Load(Comand.ExecuteReader());

                    bool f = false;

                    for (int i = 0; i < tab.Rows.Count; ++i)

                    {

                        if (DateTime.Compare(Convert.ToDateTime(tab.Rows[i][1].ToString()), DateTime.Today) > 0)

                            f = true;

                    }

                    if (!f)

                    {

                        MessageBox.Show("Книга не была возвращена  в библиотеку");

                        return;

                    }

                    clc = true;

                }

                else

                    clc = false;

            }

            dataGridView2.DataSource = tab.DefaultView;

            isbn = dataGridView1[0, e.RowIndex].Value.ToString(); ;

        }

 

        private void dataGridView2_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)

        {

            OracleCommand Comand = AR.OC.CreateCommand();

            Comand.CommandText = "SELECT B.* FROM \"Book\" B INNER JOIN \"Copy\" C ON B.ISBN = C.ISBN AND B.ISBN = (select to_char('" + isbn + "') from dual) AND C.\"cLost\" = 0 AND  C.\"woCode\" IS NULL " +

               "INNER JOIN \"Issuance\" I ON I.\"cNumber\" = C.\"cNumber\" AND I.\"iFactReturn\" IS NULL " +

               "INNER JOIN \"Reader\" R ON R.\"rNumber\" = I.\"rNumber\" AND R.\"rNumber\" = (select to_number('" + AR.rNumber + "')from dual)";

            DataTable tab = new DataTable();

            tab.Load(Comand.ExecuteReader());

            if (tab.Rows.Count != 0)

            {

                MessageBox.Show("Вы уже выбрали эту книгу");

                return;

            }

            if (!clc)

            {

                cNumber = Convert.ToInt32(dataGridView2[0, e.RowIndex].Value);

                label2.Visible = true;

                dateTimePicker1.Visible = true;

                button2.Enabled = true;

            }

        }

 

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)

        {