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

            if (flag)

            {

                DataTable tab;

                OracleCommand Comand = AR.OC.CreateCommand();

                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;

            }

            flag = false;

 

        }

 

        private void comboBox1_MouseClick(object sender, MouseEventArgs e)

        {

            flag = true;

        }

 

        private void button2_Click(object sender, EventArgs e)

        {

            if (DateTime.Compare(dateTimePicker1.Value, DateTime.Today) <= 0)

            {

                MessageBox.Show("Дата возврата должна  быть больше текущей даты");

                return;

            }

            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 = "INSERT INTO \"Issuance\" (\"iNumber\",\"cNumber\",\"rNumber\",\"iIssuance\",\"iExpReturn\",\"iFactReturn\",ICHOICE) "+

                "VALUES (0, "+

                "(select to_number('"+cNumber+"') from dual), "+

                "(select to_number('" + AR.rNumber + "') from dual), "+

                "(select to_date(SYSDATE,'dd-mm-rr') from dual), "+

                "(select to_date('" + dateTimePicker1.Value .ToShortDateString()+ "','dd-mm-rr') from dual), "+

                "null, 1)";

            tab = new DataTable();

            tab.Load(Comand.ExecuteReader());

            label2.Visible = false;

            dateTimePicker1.Visible = false;

            button2.Enabled = false;

        }

 

    }

}

 

 

Класс Administration

 

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 Administration : Form

    {

        private OracleConnection OC;

        private int lbl;

 

        public Administration(OracleConnection oc)

        {

            InitializeComponent();

            OC = oc;

            lbl = 0;

        }

 

        private void Administration_Load(object sender, EventArgs e)

        {

            dateTimePicker1.Enabled = false;

            dateTimePicker2.Enabled = false;

            button1.Enabled = false;

        }

 

        private void недобросовестныеЧитателиToolStripMenuItem_Click(object sender, EventArgs e)

        {

            dateTimePicker1.Enabled = true;

            dateTimePicker2.Enabled = true;

            button1.Enabled = true;

            lbl = 1;

        }

 

        private void label1_Click(object sender, EventArgs e)

        { }

 

        private void популярныеКнигиToolStripMenuItem_Click(object sender, EventArgs e)

        {

            dateTimePicker1.Enabled = true;

            dateTimePicker2.Enabled = true;

            button1.Enabled = true;

            lbl = 2;

        }

 

        private void стоимостьУтерянныхКнигToolStripMenuItem_Click(object sender, EventArgs e)

        {

            lbl = 3;

            button1_Click(sender, e);

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            OracleCommand Comand = OC.CreateCommand();

            DataTable table = new DataTable();

 

            if (lbl == 1)

            {

                string dateBegin = dateTimePicker1.Value.ToShortDateString();

                string dateEnd = dateTimePicker2.Value.ToShortDateString();

                Comand.CommandText = "SELECT R.\"rFullName\" AS ФИО, R.\"rAddress\" AS Адрес, R.\"rPhone\" AS Телефон, " +

                    "R.\"rData\" AS Дата_рождения, COUNT(I.\"iNumber\") AS Опоздания_Долги " +

                    "FROM \"Reader\" R, \"Issuance\" I " +

                    "WHERE R.\"rNumber\" = I.\"rNumber\" AND " +

                    "(I.\"iExpReturn\" < I.\"iFactReturn\" OR I.\"iFactReturn\" IS NULL) AND " +

                    "(I.\"iFactReturn\" BETWEEN  (select to_date('"+dateBegin+ "','dd-mm-rr') from dual) AND " +

                    "(select to_date('" + dateEnd + "','dd-mm-rr') from dual) OR I.\"iFactReturn\" IS NULL) " +

                    "GROUP BY R.\"rFullName\",R.\"rAddress\",R.\"rPhone\",R.\"rData\" " +

                    "ORDER BY Опоздания_Долги DESC";

                table.Load(Comand.ExecuteReader());

                dataGridView1.DataSource = table.DefaultView;

 

            }

            else if (lbl == 2)

            {

                string dateBegin = dateTimePicker1.Value.ToShortDateString();

                string dateEnd = dateTimePicker2.Value.ToShortDateString();

                Comand.CommandText = "SELECT B.\"bName\" AS Книга, B.\"bAuthor\" AS Автор,B.\"bPublish\" AS Издательство, "+

                    "B.\"bYear\" AS Год_издания, COUNT(I.\"iNumber\") AS Выдач "+

                    "FROM \"Book\" B, \"Issuance\" I, \"Copy\" C "+

                    "WHERE B.\"ISBN\" = C.\"ISBN\" AND C.\"cNumber\" = I.\"cNumber\" AND "+

                    "I.\"iIssuance\" BETWEEN (select to_date('"+dateBegin+ "','dd-mm-rr') from dual) AND "+

                    "(select to_date('" + dateEnd + "','dd-mm-rr') from dual)"+

                    "GROUP BY  B.\"bName\",B.\"bAuthor\",B.\"bPublish\",B.\"bYear\""+

                    "ORDER BY Выдач DESC";

                table.Load(Comand.ExecuteReader());

                dataGridView1.DataSource = table.DefaultView;

            }

            else  if (lbl == 3)

            {

                Comand.CommandText = "SELECT B.\"bName\" AS Книга, C.\"cNumber\" AS Номер, C.\"cCost\" AS \"Цена\",R.\"rFullName\" AS ФИО_читателя "+

                        "FROM \"Book\" B, \"Issuance\" I, \"Copy\" C, \"Reader\" R "+

                        "WHERE B.\"ISBN\" = C.\"ISBN\" AND C.\"cNumber\" = I.\"cNumber\" AND I.\"rNumber\"=R.\"rNumber\" AND C.\"cLost\"=1 AND C.\"repCode\" IS NULL";

                table.Load(Comand.ExecuteReader());

                dataGridView1.DataSource = table.DefaultView;

            }

 

            dateTimePicker1.Enabled = false;

            dateTimePicker2.Enabled = false;

            button1.Enabled = false;

        }

    }

}

 

 

Класс Librarian

 

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 Librarian : Form

    {

        public OracleConnection OC;

        private bool flag;

        private bool D;

        private string isbn;

        public int rNumber;

        public int mode;

        public int cNumber;

 

        public Librarian(OracleConnection oc)

        {

            InitializeComponent();

            OC = oc;

            flag = false;

        }

 

        public void Librarian_Load(object sender, EventArgs e)

        {

            экземплярToolStripMenuItem.Visible = false;

            this.Enabled = true;

            выдатьToolStripMenuItem.Visible = false;

            списатьToolStripMenuItem.Visible = false;

            заменToolStripMenuItem.Visible = false;

            DataTable tab;

            OracleCommand Comand = OC.CreateCommand();

 

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

            tab = new DataTable();

            tab.Load(Comand.ExecuteReader());

            comboBox1.DataSource = tab;

            comboBox1.ValueMember = "sCode";

            comboBox1.DisplayMember = "sName";

 

            FLoad();           

        }

 

        public void FLoad()

        {

            DataTable tab;

            OracleCommand Comand = OC.CreateCommand();

            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;

 

            Comand.CommandText = "SELECT R.\"rNumber\" AS Номер_билета, R.\"rFullName\" AS ФИО, R.\"rAddress\" AS Адрес, R.\"rPhone\" AS Телефон, R.\"rData\" AS Дата_рождения, " +

              "case when (select count(I.\"iIssuance\") from \"Issuance\" I, \"Reader\" R2 where I.\"rNumber\" = R2.\"rNumber\" AND R2.\"rNumber\" =  R.\"rNumber\" AND  I.ICHOICE IS NOT NULL group by R2.\"rNumber\")IS NULL then 'нет' else 'да' end AS Выбрал_книгу " +

              "FROM \"Reader\" R";

            tab = new DataTable();

            tab.Load(Comand.ExecuteReader());

            dataGridView3.DataSource = tab.DefaultView;

        }

 

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)

        {

            if (flag)

            {

                DataTable tab;

                OracleCommand Comand = OC.CreateCommand();

                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;

            }

            flag = false;

        }

 

        private void comboBox1_MouseClick(object sender, MouseEventArgs e)

        {

            flag = true;

        }

 

        private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)

        {

            D = false;

            mode = 1;

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

            экземплярToolStripMenuItem.Visible = true;

            OracleCommand Comand = OC.CreateCommand();

            DataTable tab = new DataTable();

            Comand.CommandText = "SELECT C.\"cNumber\" AS Номер, C.\"cCost\" AS Цена, C.\"cDate\" AS Дата_поставки, case when C.\"cLost\" = 0 then 'нет' else 'да' end AS Утерян, "+

                "case when C.\"cLost\" = 1 AND C.\"repCode\" IS NOT NULL then 'да' when C.\"cLost\" = 1 AND C.\"repCode\" IS NULL then 'нет' else ' ' end AS Заменен, " +

                "case when C.\"woCode\" IS NULL then 'нет' else 'да' end AS Списан, " +

                "P.\"plSection\" AS Отдел, P.\"plRack\" AS Стеллаж, P.\"plShelf\" AS Полка, P.\"plSector\" AS Сектор, "+

                "I.\"iIssuance\" AS Дата_выдачи, I.\"iExpReturn\" AS Ожид_возврат, I.\"iFactReturn\" AS Факт_возврат "+

                "FROM \"Book\" B INNER JOIN \"Copy\" C ON B.ISBN = C.ISBN AND B.ISBN = (select to_char('" + isbn + "') from dual) " +

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

                "INNER JOIN \"Issuance\" I ON I.\"cNumber\" = C.\"cNumber\" " +

                "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('" + isbn + "') from dual) " +

                "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());

            dataGridView2.DataSource = tab.DefaultView;

        }

 

        private void dataGridView3_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)

        {

            mode = 3;

            rNumber = Convert.ToInt32(dataGridView3[0, e.RowIndex].Value);

            OracleCommand Comand = OC.CreateCommand();

            DataTable tab = new DataTable();

            Comand.CommandText = "SELECT B.ISBN, B.\"bName\" AS Название, C.\"cNumber\" AS Номер, I.\"iExpReturn\" AS Ожид_возврат "+

                "FROM \"Book\" B, \"Copy\" C, \"Issuance\" I, \"Reader\" R "+

                "WHERE  I.\"cNumber\" = C.\"cNumber\" AND C.ISBN = B.ISBN AND I.\"rNumber\" =  R.\"rNumber\"  AND R.\"rNumber\" = (select to_number('" + rNumber + "') from dual)  AND I.\"iFactReturn\" IS NULL AND I.ICHOICE IS NULL";

            tab.Load(Comand.ExecuteReader());

            dataGridView4.DataSource = tab.DefaultView;

 

            if("да".CompareTo(dataGridView3[5,e.RowIndex].Value.ToString()) == 0)

                выдатьToolStripMenuItem.Visible = true;

            else

                выдатьToolStripMenuItem.Visible = false;

        }

 

        private void выходToolStripMenuItem_Click(object sender, EventArgs e)

        {

            OracleCommand Comand = OC.CreateCommand();

            DataTable tab = new DataTable();

            Comand.CommandText = "UPDATE \"Copy\" SET \"cLost\" = 1 WHERE \"Copy\".\"cNumber\" = (select to_number('"+cNumber+"') from dual)";

            tab.Load(Comand.ExecuteReader());

            FLoad();

        }

 

        private void выдатьToolStripMenuItem_Click(object sender, EventArgs e)

        {

            Issuance I = new Issuance(this);

            this.Enabled = false;

            I.Show();      

        }

 

        private void выходToolStripMenuItem1_Click(object sender, EventArgs e)

        {

            this.Close();

        }

 

        private void dataGridView2_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)

        {

            mode = 2;

            if ("да".CompareTo(dataGridView2[3, e.RowIndex].Value.ToString()) == 0)

            {

                заменToolStripMenuItem.Visible = true;

                списатьToolStripMenuItem.Visible = true;

                выходToolStripMenuItem.Visible = false;

            }

            if ("да".CompareTo(dataGridView2[4, e.RowIndex].Value.ToString()) == 0)

            {

                списатьToolStripMenuItem.Visible = true;

                заменToolStripMenuItem.Visible = false;

                выходToolStripMenuItem.Visible = false;

            }

            if ("да".CompareTo(dataGridView2[5, e.RowIndex].Value.ToString()) == 0)

            {

                заменToolStripMenuItem.Visible = false;

                списатьToolStripMenuItem.Visible = false;

                выходToolStripMenuItem.Visible = false;

            }

            if (D)

            {

                заменToolStripMenuItem.Visible = false;

                списатьToolStripMenuItem.Visible = true;

                выходToolStripMenuItem.Visible = false;

            }

            else if ("да".CompareTo(dataGridView2[5, e.RowIndex].Value.ToString()) != 0 && "".CompareTo(dataGridView2[12, e.RowIndex].Value.ToString()) != 0)

            {

                списатьToolStripMenuItem.Visible = true;

                выходToolStripMenuItem.Visible = false;

            }