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

            if (D)

            {

                заменToolStripMenuItem.Visible = false;

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

                выходToolStripMenuItem.Visible = false;

            }

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

            {

                заменToolStripMenuItem.Visible = false;

                выходToolStripMenuItem.Visible = true;

            }

 

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

        }

 

        private void списатьToolStripMenuItem_Click(object sender, EventArgs e)

        {

            OracleCommand Comand = OC.CreateCommand();

            DataTable tab = new DataTable();

            Comand.CommandText = "INSERT INTO \"Writeoff\" (\"woCode\", \"woDate\") VALUES(0, (select to_date(SYSDATE,'dd-mm-rr') from dual))";

            tab.Load(Comand.ExecuteReader());

            tab = new DataTable();

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

            tab.Load(Comand.ExecuteReader());

 

            FLoad();

        }

 

        private void заменToolStripMenuItem_Click(object sender, EventArgs e)

        {

            OracleCommand Comand = OC.CreateCommand();

            DataTable tab = new DataTable();

            Comand.CommandText = "INSERT INTO \"Replacement\" (\"repCode\", \"repDate\") VALUES(0, (select to_date(SYSDATE,'dd-mm-rr') from dual))";

            tab.Load(Comand.ExecuteReader());

            tab = new DataTable();

            Comand.CommandText = "UPDATE \"Copy\" SET \"repCode\" = (select max(\"repCode\") from \"Replacement\") WHERE \"Copy\".\"cNumber\" = (select to_number('" + cNumber + "'))";

            tab.Load(Comand.ExecuteReader());

            книгуToolStripMenuItem_Click(sender, e);

        }

 

        private void книгуToolStripMenuItem_Click(object sender, EventArgs e)

        {

            AddBook AB = new AddBook(OC,null);

            AB.Show();

        }

 

        private void обновитьToolStripMenuItem_Click(object sender, EventArgs e)

        {

            FLoad();

        }

 

        private void экземплярToolStripMenuItem_Click(object sender, EventArgs e)

        {

            AddCopy AC = new AddCopy(OC, isbn, 0);

            AC.Show();

        }

 

        private void dataGridView1_RowHeaderMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)

        {

            D = true;

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

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

            OracleCommand Comand = OC.CreateCommand();

            DataTable tab = new 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 Сектор " +

            "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\" ";

            tab.Load(Comand.ExecuteReader());

 

            dataGridView2.DataSource = tab.DefaultView;

        }

 

        private void читателяToolStripMenuItem_Click(object sender, EventArgs e)

        {

            AddReader AR = new AddReader(OC,0);

            AR.Show();

        }

 

        private void редактироватьToolStripMenuItem_Click(object sender, EventArgs e)

        {

            if (mode == 1)

            {

                AddBook AB = new AddBook(OC, isbn);

                AB.Show();

            }

            else if (mode == 2)

            {

                AddCopy AC = new AddCopy(OC, isbn, cNumber);

                AC.Show();

            }

            else if (mode == 3)

            {

                AddReader AR = new AddReader(OC, rNumber);

                AR.Show();

            }

            else

                MessageBox.Show("Выделите объект для  редактирования");

        }

    }

}

 

 

Класс AddReader

 

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

    {

        private OracleConnection OC;

        private int rNumber;

        private bool rflag;

 

        public AddReader(OracleConnection oc, int r)

        {

            InitializeComponent();

            OC = oc;

            rflag = false;

            if (r != 0)

            {

                rNumber = r;

                rflag = true;

            }

        }

 

        private void button2_Click(object sender, EventArgs e)

        {

            if (textBox1.Text == null || textBox1.Text == "" ||

                textBox2.Text == null || textBox2.Text == "" ||

                textBox5.Text == null || textBox5.Text == "")

            {

                MessageBox.Show("Все поля должны быть  заполнены!");

                return;

            }

            TimeSpan ts = DateTime.Today - dateTimePicker1.Value;

            if (ts.Days < 6574)

            {

                MessageBox.Show("Читатель должен быть  совершеннолетним");

                return;

            }

            OracleCommand Comand = OC.CreateCommand();

            DataTable tab = new DataTable();

 

            if (!rflag)

            {

                Comand.CommandText = "select * from \"Reader\" where \"rFullName\" = '" + textBox1.Text + "' AND \"rData\" = (select to_date('" +

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

                tab.Load(Comand.ExecuteReader());

                if (tab.Rows.Count != 0)

                {

                    MessageBox.Show("Читатель уже зарегистрирован  в библиотеке");

                    return;

                }

                Comand.CommandText = "begin PROCEDURE_INS_READER('" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox5.Text + "', '" +

                    dateTimePicker1.Value.ToShortDateString() + "'); end;";

                Comand.ExecuteNonQuery();

                MessageBox.Show("Читатель успешно добавлен");

                this.Close();

            }

            else

            {

                Comand.CommandText = "begin PROCEDURE_EDIT_READER('" + rNumber + "', '" + textBox1.Text + "', '" + textBox2.Text + "', '" +

                    textBox5.Text + "', '" + dateTimePicker1.Value.ToShortDateString() + "'); end;";

                Comand.ExecuteNonQuery();

                MessageBox.Show("Изменения сохранены");

                this.Close();

            }

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            this.Close();

        }

 

        private void AddReader_Load(object sender, EventArgs e)

        {

            if (rflag)

            {

                OracleCommand Comand = OC.CreateCommand();

                DataTable tab = new DataTable();

                Comand.CommandText = "select * from \"Reader\" where \"rNumber\" = '" + rNumber + "'";

                tab.Load(Comand.ExecuteReader());

                textBox1.Text = tab.Rows[0][1].ToString();

                textBox2.Text = tab.Rows[0][2].ToString();

                textBox5.Text = tab.Rows[0][3].ToString();

                dateTimePicker1.Value = Convert.ToDateTime(tab.Rows[0][4].ToString());

            }

        }

    }

}

 

Класс AddCopy

 

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

    {

        private OracleConnection OC;

        private string isbn;

        private int cNumber;

        private bool bflag;

 

        public AddCopy(OracleConnection oc, string i, int c)

        {

            InitializeComponent();

            OC = oc;

            isbn = i;

            bflag = false;

            if (c != 0)

            {

                cNumber = c;

                bflag = true;

            }

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            this.Close();

        }

 

        private void button2_Click(object sender, EventArgs e)

        {

            if (!bflag)

            {

                OracleCommand Comand = OC.CreateCommand();

                DataTable tab = new DataTable();

                if (comboBox1.SelectedIndex == -1 || comboBox2.SelectedIndex == -1 ||

                    comboBox3.SelectedIndex == -1 || textBox2.Text == null || textBox2.Text == "")

                {

                    MessageBox.Show("Заполните все поля!");

                    return;

                }

                if (DateTime.Compare(dateTimePicker1.Value, DateTime.Now) > 0)

                {

                    MessageBox.Show("Дата поставки не  может превышать текущую дату");

                    return;

                }

 

                Comand.CommandText = "select P.\"plCode\" from \"Place\" P " +

                 "where P.\"plSection\" = '" + comboBox1.Items[comboBox1.SelectedIndex].ToString() + "' AND P.\"plRack\" = '" +

                comboBox2.Items[comboBox2.SelectedIndex].ToString() + "' AND P.\"plShelf\" = '" +

               comboBox3.Items[comboBox3.SelectedIndex].ToString() + "' AND P.\"plSector\" = '" + textBox2.Text + "'";

                tab.Load(Comand.ExecuteReader());

                int code = 0;

                if (tab.Rows.Count > 0)

                {

                    code = Convert.ToInt32(tab.Rows[0][0].ToString());

                }

 

                if (code > 0)

                {

                    Comand.CommandText = "begin PROCEDURE_INS_COPY('" + isbn + "', '" + numericUpDown1.Value + "', '" + dateTimePicker1.Value.ToShortDateString() + "', '" + code + "'); end;";

                    Comand.ExecuteNonQuery();

                }

                else

                {

                    tab = new DataTable();

                    Comand.CommandText = "begin PROCEDURE_INS_PLACE('" + comboBox1.Items[comboBox1.SelectedIndex].ToString() + "', '" +

                        comboBox2.Items[comboBox2.SelectedIndex].ToString() + "', '" +

                        comboBox3.Items[comboBox3.SelectedIndex].ToString() + "', '" + textBox2.Text + "'); end;";

                    Comand.ExecuteNonQuery();

                    Comand.CommandText = "select max(\"plCode\") from \"Place\"";

                    tab.Load(Comand.ExecuteReader());

                    code = Convert.ToInt32(tab.Rows[0][0]);

                    Comand.CommandText = "begin PROCEDURE_INS_COPY('" + isbn + "', '" + numericUpDown1.Value + "', '" + dateTimePicker1.Value.ToShortDateString() + "', '" + code + "'); end;";

                    Comand.ExecuteNonQuery();

                }

                MessageBox.Show("Экземпляр книги зарегистрирован");

            }

            else

            {

                OracleCommand Comand = OC.CreateCommand();

                DataTable tab = new DataTable();

                if (comboBox1.SelectedIndex == -1 || comboBox2.SelectedIndex == -1 ||

                    comboBox3.SelectedIndex == -1 || textBox2.Text == null || textBox2.Text == "")

                {

                    MessageBox.Show("Заполните все поля!");

                    return;

                }

                if (DateTime.Compare(dateTimePicker1.Value, DateTime.Now) > 0)

                {

                    MessageBox.Show("Дата поставки не  может превышать текущую дату");

                    return;

                }

                Comand.CommandText = "begin PROCEDURE_EDIT_COPY('"+cNumber+"', '"+numericUpDown1.Value+"', '"+dateTimePicker1.Value.ToShortDateString()+

                    "', '" + comboBox1.Items[comboBox1.SelectedIndex].ToString() + "', '" + comboBox2.Items[comboBox2.SelectedIndex].ToString() + "', '" + comboBox3.Items[comboBox3.SelectedIndex].ToString() +

                    "', '"+textBox2.Text+"'); end;";

                Comand.ExecuteNonQuery();

                MessageBox.Show("Изменения сохранены");

                this.Close();

            }

        }

 

        private void AddCopy_Load(object sender, EventArgs e)

        {

            textBox5.Text = isbn;

            OracleCommand Comand = OC.CreateCommand();

            DataTable tab = new DataTable();

            Comand.CommandText = "select \"bName\" from \"Book\" where ISBN = (select to_char('"+isbn+"') from dual)";

            tab.Load(Comand.ExecuteReader());

            textBox1.Text = tab.Rows[0][0].ToString();

            if (bflag)

            {

                tab = new DataTable();

                Comand.CommandText = "select C.\"cCost\", C.\"cDate\", P.\"plSection\", P.\"plRack\", P.\"plShelf\", P.\"plSector\" "+

                    "from \"Copy\" C inner join \"Place\" P on C.\"plCode\" = P.\"plCode\" where C.\"cNumber\" = (select to_number('"+cNumber+"') from dual)";

                tab.Load(Comand.ExecuteReader());

                numericUpDown1.Value = Convert.ToDecimal(tab.Rows[0][0]);

                dateTimePicker1.Value = Convert.ToDateTime(tab.Rows[0][1]);

                for (int i = 0; i < comboBox1.Items.Count; ++i)

                {

                    if (comboBox1.Items[i].ToString() == tab.Rows[0][2].ToString())

                    {

                        comboBox1.SelectedIndex = i;

                        break;

                    }

                }

                for (int i = 0; i < comboBox2.Items.Count; ++i)

                {

                    if (comboBox2.Items[i].ToString() == tab.Rows[0][3].ToString())

                    {

                        comboBox2.SelectedIndex = i;

                        break;

                    }

                }

                for (int i = 0; i < comboBox3.Items.Count; ++i)

                {

                    if (comboBox3.Items[i].ToString() == tab.Rows[0][4].ToString())

                    {

                        comboBox3.SelectedIndex = i;

                        break;

                    }

                }

                textBox2.Text = tab.Rows[0][5].ToString();

            }

        }

    }

}

 

 

Класс AddBook

 

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

    {

        public OracleConnection OC;

        private string isbn;

        private bool flag;