Информационная система составления расписания

Для того чтобы правильно понимать логику приложения было создано визуальное представление с помощью диаграммы Usecases.

Диаграмма показывает, что в приложении существует несколько страниц, которыми может воспользоваться администратор: страница «Редактирования, удаления и добавления информации», страница «Тарификации», страница «Составление расписания». Администратор изначально входит на страницу с редактированием, а далее может свободно перемещаться по этим трем блокам. С блоком редактирования связаны еще несколько страниц, такие как: добавление групп, предметов, преподавателей и кабинетов, а так же редактирование групп, предметов, преподавателей и кабинетов. С помощью блока с тарификацией администратор может выгрузить необходимую информацию в таблицы Excel. Страница «Составление расписания» помогает не только в правильной расстановке занятий, но и оптимизирует расписание.

Рис. 32. Usecase приложения по составлению расписания

3.5 Оптимизация

Заключительным этапом создания приложения для автоматизированного составления расписания для учебной части образовательного учрежденияявляется процесс оптимизации ручного составления расписания. Для создания функции оптимизации к программе VisualStudio была подключена программа IBMiLogCPLEXOptimizationStudio, которая автоматически может оптимизировать любой процесс, используя некоторые, вводимые пользователем ограничения. В данном случае математические ограничения описаны в части 2.3.3. С помощью языка, близкого по синтаксису с С#, ограничения были введены в программу IBMiLogCPLEXOptimizationStudio.

С помощью складки «Редактор/ Просмотр» пользователь может оптимизировать процесс ручного составления расписания. Программа автоматически сгенерирует расписание, опираясь на заданные ранее ограничения. Пример расписания для групп будет сохранен в Excel файле«KR-Tekhnikum», расписание, для каждой группы будет сохранено в отдельную вкладку. Каждое занятие описано, с помощью предмета, преподавателя, который ведет данный предмет, а также номером аудитории.

Аналогичным образом создано расписание для преподавателей, которое хранится в Excelфайле «Kartochki_prepodavatelya».

Выводы

В главе была описана база данных, на основе которой было создано приложение. Программа была основана на функциональных требованиях, полностью удовлетворяет условиям администратора учебной части. Также была построена логика приложения, с помощью которого пользователь сможет ориентироваться в приложении.

Заключение

В результате работы получилось создать базу данных для составления расписания Кировского авиационного техникума, которое оптимизирует работу учебной части техникума. База собирает информацию о преподавателях, группах, кабинетах, специальностях. Также данная база является отличным помощником для учебной части, которая может внести новых преподавателей, новые группы и т.д.

Однако основное внимание было уделено приложению, с помощью которого можно не только редактировать, удалять и добавлять данные, но и составлять само расписание, рассчитывать нагрузку преподавателей и групп. Такая система составления расписания подойдет не только Кировскому авиационному техникума, но и другим учебным заведениям с похожей структурой образования.

Также была создана система оптимизации расписания, чтобы администратор мог нажатием одной кнопки составить оптимальное расписание.

В марте 2016 года программа была внедрена в систему Кировского авиационного техникума для апробации. (Прил.13)На данном этапе сбоев в программе не обнаружено, система удовлетворяет всем требования технического задания.

В ходе работы были решены все задачи, а также цель была достигнута.

Список литературы

1) БутузовВ.Ф., КрутицкаяН.Ч., МедведевГ.Н., Шишкин А.А. Математический анализ в вопросах и задачах -- М.: ФИЗМАТЛИТ, 2002. - 480 с.

2) Дейт К. Введение в системы баз данных 8-е изд. -- М.: Вильямс, 2006

3) Карпова Т.С. Базы данных: модели, разработка, реализация -- СПб.: Питер, 2001. --304 с.;

4) Лазарев А.А., Гафаров Е.Р. Теория расписаний. Задачи и алгоритмы-- М.: Московский государственный университет им. М.В. Ломоносова (МГУ), 2011. -- 222 с.

5) Хансен Г., Хансен Д. Базы данных: разработка и управление -- М. : БИНОМ, 2000. - 210 с.

6) Ban Vu Thien. A finite Algorithm for Globally Minimizing a Concave Function under Linear Constraints and its Applications. Preprint Series. №4. Hannoy: Institute of Mathematics, 1983.

7) Falk I. E. and Soland R. M. An algorithm for separable nonconvex programming problems // Management Sci. - 1969. - Vol. 15. - P. 550-569.

8) Frieze A. M. A bilinear programrningjormulation of the 1-dimensional assignment problem // Math. Progr. - 1974. - Vol. 7. - P. 376-379.

Приложения

Приложение 1

Информация о типе преподавателя

Приложение 2

Информация о преподавателе

Приложение 3

Информация о группе

Приложение 4

Информация о специальности

Приложение 5

Информация о типе образования

Приложение 6

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

Приложение 7

Информация о номере предмета

Приложение 8

Информация о номере предмета в неделе

Приложение 9

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

Приложение 10

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

Приложение 11

Информация о предмете

Приложение 12

Информация о связи между преподавателем и предметом

Приложение 13

Пример оформления сохраненного расписания для групп

Приложение 14

Пример оформления сохраненного расписания для преподавателей

Приложение 15

Часть кода программы Form1

publicForm1()

{

InitializeComponent();

SqlDataAdapter dataAdapter = newSqlDataAdapter("SELECT Name_Subject from Subject", connectionString);

DataSet ds = newDataSet();

dataAdapter.Fill(ds, "Названиедисциплины");

table_subject.DataSource = ds.Tables["Названиедисциплины"].DefaultView;

SqlDataAdapter dataAdapter1 = newSqlDataAdapter("SELECT Number_campus, Number from Cabinet", connectionString);

DataSet ds1 = newDataSet();

dataAdapter1.Fill(ds1, "Кабинет");

table_cabinet.DataSource = ds1.Tables["Кабинет"].DefaultView;

SqlDataAdapter dataAdapter2 = newSqlDataAdapter("Select Grup.Group_Name, Specialty.Name_Specialty from Grup inner join Specialty on Grup.ID_Specialty=Specialty.ID_Specialty", connectionString);

DataSet ds2 = newDataSet();

dataAdapter2.Fill(ds2, "Группа");

table_Group.DataSource = ds2.Tables["Группа"].DefaultView;

int awidth = table_cabinet.Width / table_cabinet.ColumnCount;

for (int i = 0; i < table_cabinet.ColumnCount; i++)

table_cabinet.Columns[i].Width = awidth;

int awidth1 = table_subject.Width / table_subject.ColumnCount;

for (int i = 0; i < table_subject.ColumnCount; i++)

table_subject.Columns[i].Width = awidth1;

int awidth2 = table_Group.Width / table_Group.ColumnCount;

for (int i = 0; i < table_Group.ColumnCount; i++)

table_Group.Columns[i].Width = awidth2;

}

privatevoid Administrtion_button_Click(object sender, EventArgs e)

{

SqlDataAdapter dataAdapter = newSqlDataAdapter("SELECT Surname, Name, Second_Name from Teacher WHERE ID_type_teacher=1", connectionString);

DataSet ds = newDataSet();

dataAdapter.Fill(ds, "Фамилия");

table_teacher.DataSource = ds.Tables["Фамилия"].DefaultView;

int awidth3 = table_teacher.Width / table_teacher.ColumnCount;

for (int i = 0; i < table_teacher.ColumnCount; i++)

table_teacher.Columns[i].Width = awidth3;

a = 1;

}

privatevoid Teacher_button_Click(object sender, EventArgs e)

{

SqlDataAdapter dataAdapter = newSqlDataAdapter("SELECT Surname, Name, Second_Name from Teacher WHERE ID_type_teacher=2", connectionString);

DataSet ds = newDataSet();

dataAdapter.Fill(ds, "Фамилия");

table_teacher.DataSource = ds.Tables["Фамилия"].DefaultView;

int awidth3 = table_teacher.Width / table_teacher.ColumnCount;

for (int i = 0; i < table_teacher.ColumnCount; i++)

table_teacher.Columns[i].Width = awidth3;

a = 2;

}

privatevoid inside_button_Click(object sender, EventArgs e)

{

SqlDataAdapter dataAdapter = newSqlDataAdapter("SELECT Surname, Name, Second_Name from Teacher WHERE ID_type_teacher=3", connectionString);

DataSet ds = newDataSet();

dataAdapter.Fill(ds, "Фамилия");

table_teacher.DataSource = ds.Tables["Фамилия"].DefaultView;

int awidth3 = table_teacher.Width / table_teacher.ColumnCount;

for (int i = 0; i < table_teacher.ColumnCount; i++)

table_teacher.Columns[i].Width = awidth3;

a = 3;

}

privatevoid outside_button_Click(object sender, EventArgs e)

{

SqlDataAdapter dataAdapter = newSqlDataAdapter("SELECT Surname, Name, Second_Name from Teacher WHERE ID_type_teacher=4", connectionString);

DataSet ds = newDataSet();

dataAdapter.Fill(ds, "Фамилия");

table_teacher.DataSource = ds.Tables["Фамилия"].DefaultView;

int awidth3 = table_teacher.Width / table_teacher.ColumnCount;

for (int i = 0; i < table_teacher.ColumnCount; i++)

table_teacher.Columns[i].Width = awidth3;

a = 4;

}

privatevoid delete_subject_Click(object sender, EventArgs e)

{

if (MessageBox.Show("Выдействительнохотитеудалить?", "Удалениепредмета", MessageBoxButtons.YesNo) == DialogResult.Yes)

{

con.Open();

string ch = table_subject[0, table_subject.SelectedRows[0].Index].Value.ToString();

SqlCommand com = newSqlCommand("DELETE FROM [Subject] WHERE [Name_subject] LIKE '" + ch + "';", con);

com.ExecuteNonQuery();

SqlDataAdapter dataAdapter = newSqlDataAdapter("SELECT Name_subject from Subject", connectionString);

DataSet ds = newDataSet();

dataAdapter.Fill(ds, "Названиедисциплины");

table_subject.DataSource = ds.Tables["Названиедисциплины"].DefaultView;

con.Close();

}

}

privatevoid delete_cabinet_Click(object sender, EventArgs e)

{

if (MessageBox.Show("Выдействительнохотитеудалить?", "Удалениекабинета", MessageBoxButtons.YesNo) == DialogResult.Yes)

{

con.Open();

string ch = table_subject[0, table_cabinet.SelectedRows[0].Index].Value.ToString();

SqlCommand com = newSqlCommand("DELETE FROM [Cabinet] WHERE [Number] LIKE '" + ch + "';", con);

com.ExecuteNonQuery();

SqlDataAdapter dataAdapter1 = newSqlDataAdapter("SELECT Number_campus, Number from Cabinet", connectionString);

DataSet ds1 = newDataSet();

dataAdapter1.Fill(ds1, "Кабинет");

table_cabinet.DataSource = ds1.Tables["Кабинет"].DefaultView;

con.Close();

}

}

privatevoid delete_group_Click(object sender, EventArgs e)

{

if (MessageBox.Show("Выдействительнохотитеудалить?", "Удалениегруппы", MessageBoxButtons.YesNo) == DialogResult.Yes)

{

con.Open();

string ch = table_Group[0, table_Group.SelectedRows[0].Index].Value.ToString();

SqlCommand com = newSqlCommand("DELETE FROM [Grup] WHERE [Grup_Name] LIKE '" + ch + "';", con);

com.ExecuteNonQuery();

SqlDataAdapter dataAdapter = newSqlDataAdapter("Select Grup_Name, Name_specialty from Grup inner join Specialty on Grup.ID_Specialty=Specialty.ID_code_specialty where ID_Type_education=2", connectionString);

DataSet ds = newDataSet();

dataAdapter.Fill(ds, "Группа");

table_Group.DataSource = ds.Tables["Группа"].DefaultView;

con.Close();

}

}

privatevoid delete_teacher_Click(object sender, EventArgs e)

{

if (MessageBox.Show("Выдействительнохотитеудалить?", "Удалениепреподавателя", MessageBoxButtons.YesNo) == DialogResult.Yes)

{

con.Open();

string ch = table_teacher[0, table_teacher.SelectedRows[0].Index].Value.ToString();

SqlCommand com = newSqlCommand("DELETE FROM [Teacher] WHERE [Surname] LIKE '" + ch + "';", con);

com.ExecuteNonQuery();

SqlDataAdapter dataAdapter = newSqlDataAdapter("SELECT Surname from Teacher", connectionString);

DataSet ds = newDataSet();

dataAdapter.Fill(ds, "Названиепреподавателя");

table_teacher.DataSource = ds.Tables["Названиепреподавателя"].DefaultView;

con.Close();

}

}

Приложение 16

Часть кода программы Tarif

public Tarif(string constrc, Form1 DIL)

{

InitializeComponent();

diL = DIL;

this.constrc = constrc;

con = newSqlConnection(constrc);

con.Open();

com = newSqlCommand("SELECT [Name_subject] FROM [Subject]", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_subject.Items.Add((string)r[0]);

}

}

com = newSqlCommand("SELECT [Group_Name] FROM [Grup]", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_group.Items.Add((string)r[0]);

}

}

com = newSqlCommand("SELECT [Name_teacher] FROM [Type_teacher]", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

comboBox_sp.Items.Add((string)r[0]);

comboBox_pod1.Items.Add((string)r[0]);

}

}

com = newSqlCommand("SELECT [Name_teacher] FROM [Type_teacher]",con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

comboBox_stp.Items.Add((string)r[0]);

}

}

com = newSqlCommand("SELECT [Surname] FROM [Teacher] WHERE [ID_Type_teacher] = 1", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

comboBox_t.Items.Add((string)r[0]);

}

}

com = newSqlCommand("SELECT [Group_Name] FROM [Grup]", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

comboBox_group.Items.Add((string)r[0]);

}

}

con.Close();

comboBox_sp.SelectedIndex = 0;

comboBox_pod1.SelectedIndex = 0;

comboBox_stp.SelectedIndex = 0;

comboBox_group.SelectedIndex = 0;

}

int IDt=0;

privatevoid comboBox_sp_SelectedIndexChanged_1(object sender, EventArgs e)

{

listBox_teacher.Items.Clear();

con.Open();

com = newSqlCommand("SELECT [ID_Type_teacher] FROM [Type_teacher] WHERE [Name_teacher] LIKE '" + comboBox_sp.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt = (int)r[0];

}

}

com = newSqlCommand("SELECT [Surname] FROM [Teacher] WHERE [ID_Type_teacher] = '" + IDt.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_teacher.Items.Add((string)r[0]);

}

}

con.Close();

}

privatevoid listBox_teacher_SelectedIndexChanged_1(object sender, EventArgs e)

{

string str = listBox_teacher.SelectedItem.ToString();

TB_Teacher.Text = str;

}

privatevoid listBox_group_SelectedIndexChanged_1(object sender, EventArgs e)

{

string str = listBox_group.SelectedItem.ToString();

TB_group.Text = str;

}

privatevoid listBox_subject_SelectedIndexChanged_1(object sender, EventArgs e)

{

string str = listBox_subject.SelectedItem.ToString();

TB_Subject.Text = str;

}

privatevoid comboBox_stp_SelectedIndexChanged_1(object sender, EventArgs e)

{

con.Open();

listBox_teach1.Items.Clear();

com = newSqlCommand("SELECT [ID_Type_teacher] FROM [Type_teacher] WHERE [Name_teacher] LIKE '" + comboBox_stp.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt = (int)r[0];

}

}

com = newSqlCommand("SELECT [Surname] FROM [Teacher] WHERE [ID_Type_teacher] = '" + IDt.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_teach1.Items.Add((string)r[0]);

}

}

con.Close();

}

privatevoid comboBox_teacher_Click(object sender, EventArgs e)

{

con.Open();

int IDt = 0;

string ch = TB_Teacher.ToString();

string ch1 = TB_group.ToString();

string ch2 = TB_Subject.ToString();

com = newSqlCommand("SELECT [ID_Teacher] FROM [Teacher] WHERE [Surname] LIKE'" + ch + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt = (int)r[0];

}

}

int IDg = 0;

com = newSqlCommand("SELECT ID_Group FROM [Grup] WHERE [Group_Name] LIKE'" + ch1 + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDg = (int)r[0];

}

}

int IDs = 0;

com = newSqlCommand("SELECT ID_Subject FROM [Subject] WHERE [Name_Subject] LIKE'" + ch2 + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDs = (int)r[0];

}

}

int IDst = 0;

com = newSqlCommand("SELECT [ID_Teacher_Subject] FROM [Teacher_Subject] WHERE [ID_Teacher] = '" + IDt.ToString() + "'AND [ID_Subject]='" + IDs.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDst = (int)r[0];

}

}

if (IDst == 0)

{

if (MessageBox.Show("Связи преподаватель предмет не существовало. Создатьее?", "Добавлениесвязи", MessageBoxButtons.YesNo) == DialogResult.Yes)

{

com = newSqlCommand("INSERT INTO [Teacher_Subject] ([ID_Teacher], [ID_Subject]) VALUES (" + IDt.ToString() + "," + IDs.ToString() + ")", con);

com.ExecuteNonQuery();

com = newSqlCommand("SELECT ID_Teacher_Subject FROM [Teacher_Subject] WHERE [ID_Teacher] =" + IDt.ToString() + "AND [ID_Subject]=" + IDs.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDst = (int)r[0];

}

}

}

}

com = newSqlCommand("INSERT INTO [Connect] ([ID_Group, ID_Teacher_Subject]) VALUES ('" + IDg.ToString() + "," + IDst.ToString() + "')", con);

com.ExecuteNonQuery();

com = newSqlCommand("SELECT * FROM [Grup]", con);

MessageBox.Show("Связьдобавлена!");

con.Close();

}

privatebool AlI(string s)

{

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

if (ListBoxTar.Items[i].ToString() == s)

returntrue;

returnfalse;

}

privatevoid button15_Click_1(object sender, EventArgs e)

{

for (int i = 0; i < listBox_teach1.SelectedItems.Count; i++)

if (!AlI(listBox_teach1.SelectedItems[i].ToString()))

ListBoxTar.Items.Add(listBox_teach1.SelectedItems[i]);

}

privatevoid MoreAll_Click(object sender, EventArgs e)

{

ListBoxTar.Items.Clear();

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

ListBoxTar.Items.Add(listBox_teach1.Items[i]);

}

privatevoid RemoveAll_Click_1(object sender, EventArgs e)

{

ListBoxTar.Items.Clear();

}

privatevoid RemoveOne_Click_1(object sender, EventArgs e)

{

int per = ListBoxTar.SelectedItems.Count;

for (int i = 0; i < per; i++)

ListBoxTar.Items.Remove(ListBoxTar.SelectedItems[0]);

}

privatevoid comboBox_pod1_SelectedIndexChanged(object sender, EventArgs e)

{

con.Open();

comboBox_t.Items.Clear();

com = newSqlCommand("SELECT [ID_Type_teacher] FROM [Type_teacher] WHERE [Name_teacher] LIKE '" + comboBox_pod1.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt = (int)r[0];

}

}

com = newSqlCommand("SELECT [Surname] FROM [Teacher] WHERE [ID_Type_teacher] = '" + IDt.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

comboBox_t.Items.Add((string)r[0]);

}

con.Close();

}

privatevoid comboBox_t_SelectedIndexChanged(object sender, EventArgs e)

{

con.Open();

ConnectingPeople.Rows.Clear();

int TID = 0;

com = newSqlCommand("SELECT [ID_Teacher] FROM [Teacher] WHERE [Surname] LIKE '" + comboBox_t.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

TID = (int)r[0];

}

}

List<TC> ConL = newList<TC>();

com = newSqlCommand("SELECT [ID_Subject], [ID_Teacher_Subject] FROM [Teacher_Subject] WHERE [ID_Teacher] = " + TID.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

int SID = (int)r[0];

ConL.Add(newTC((int)r[0], (int)r[1]));

}

}

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

{

com = newSqlCommand("SELECT [Name_Subject] FROM [Subject] WHERE [ID_Subject] = " + ConL[i].SID.ToString(), con);

using (SqlDataReader rt = com.ExecuteReader())

{

while (rt.Read())

{

ConL[i].NameS = (string)rt[0];

}

}

}

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

{

com = newSqlCommand("SELECT [ID_Group], [Teo], [Lab], [Exam], [KKP], [PKP], [RPr], [DP], [GAK], [DZ], [One], [Two] FROM [Connect] WHERE [ID_Teacher_Subject] = " + ConL[i].TSID.ToString(), con);

using (SqlDataReader rt = com.ExecuteReader())

{

while (rt.Read())

{

ConL[i].GID = (int)rt[0];

ConL[i].teo = (int)rt[1];

ConL[i].lab = (int)rt[2];

ConL[i].exam = (int)rt[3];

ConL[i].kkp = (int)rt[4];

ConL[i].pkp = (int)rt[5];

ConL[i].rpr = (int)rt[6];

ConL[i].dp = (int)rt[7];

ConL[i].gak = (int)rt[8];

ConL[i].dz = (int)rt[9];

ConL[i].one = (int)rt[10];

ConL[i].two = (int)rt[11];

}

}

}

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

{

com = newSqlCommand("SELECT [Group_Name] FROM [Grup] WHERE [ID_Group] = " + ConL[i].GID.ToString(), con);

using (SqlDataReader rt = com.ExecuteReader())

{

while (rt.Read())

{

ConL[i].NameG = (string)rt[0];

}

}

}

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

ConnectingPeople.Rows.Add(ConL[i].NameG, ConL[i].NameS, ConL[i].teo.ToString(), ConL[i].lab.ToString(), ConL[i].exam.ToString(), ConL[i].kkp.ToString(), ConL[i].pkp.ToString(), ConL[i].rpr.ToString(), ConL[i].dp.ToString(), ConL[i].gak.ToString(), ConL[i].dz.ToString(), ConL[i].one.ToString(), ConL[i].two.ToString());

con.Close();

}

privatevoid comboBox_group_SelectedIndexChanged(object sender, EventArgs e)

{

con.Open();

dataGridView_Group.Rows.Clear();

com = newSqlCommand("SELECT [ID_Group] FROM [Grup] WHERE [Group_Name] LIKE '" + comboBox_group.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

GID = (int)r[0];

}

}

List<CC> ConL = newList<CC>();

com = newSqlCommand("SELECT [ID_Connect], [ID_Teacher_Subject], [Teo], [Lab], [Exam], [KKP], [PKP], [RPr], [DP], [GAK], [DZ], [One], [Two] FROM [Connect] WHERE [ID_Group] = " + GID.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

Cons.Add(newCon_DGV((int)r[0]));

int IDT = (int)r[1];

int teo = (int)r[2];

int lab = (int)r[3];

int exam = (int)r[4];

int kkp = (int)r[5];

int pkp = (int)r[6];

int rpr = (int)r[7];

int dp = (int)r[8];

int gak = (int)r[9];

int dz = (int)r[10];

int one = (int)r[11];

int two = (int)r[12];

ConL.Add(newCC((int)r[0], (int)r[1], (int)r[2], (int)r[3], (int)r[4], (int)r[5], (int)r[6], (int)r[7], (int)r[8], (int)r[9], (int)r[10], (int)r[11]));

}

}

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

{

com = newSqlCommand("SELECT [ID_Teacher], [ID_Subject] FROM [Teacher_Subject] WHERE [ID_Teacher_Subject] = " + ConL[i].IDT.ToString(), con);

using (SqlDataReader rt = com.ExecuteReader())

{

while (rt.Read())

{

ConL[i].IDt = (int)rt[0];

ConL[i].IDs = (int)rt[1];

}

}

}

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

{

com = newSqlCommand("SELECT [Name_Subject] FROM [Subject] WHERE [ID_Subject] = " + ConL[i].IDs.ToString(), con);

using (SqlDataReader rt = com.ExecuteReader())

{

while (rt.Read())

{

ConL[i].NameS = (string)rt[0];

}

}

}

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

{

com = newSqlCommand("SELECT [Surname] FROM [Teacher] WHERE [ID_Teacher] = " + ConL[i].IDt.ToString(), con);

using (SqlDataReader rt = com.ExecuteReader())

{

while (rt.Read())

{

ConL[i].NameT = (string)rt[0];

}

}

}

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

{

dataGridView_Group.Rows.Add(ConL[i].NameS, ConL[i].NameT, ConL[i].teo.ToString(), ConL[i].lab.ToString(), ConL[i].exam.ToString(), ConL[i].kkp.ToString(), ConL[i].pkp.ToString(), ConL[i].rpr.ToString(), ConL[i].dp.ToString(), ConL[i].gak.ToString(), ConL[i].dz.ToString(), ConL[i].one.ToString(), ConL[i].two.ToString());

Cons[i].Row_Num = i;

}

con.Close();

}

int ID_g;

privatevoid button_delete_all_Click(object sender, EventArgs e)

{

con.Open();

com = newSqlCommand("SELECT [ID_Group] FROM [Grup] WHERE [Name_Group] LIKE'" + comboBox_group.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

ID_g = (int)r[0];

}

}

com = newSqlCommand("DELETE FROM [Connect] WHERE [ID_Group] =" + ID_g.ToString(), con);

com.ExecuteNonQuery();

Cons.RemoveAt(ConnectingPeople.SelectedRows[0].Index);

comboBox_group.SelectedIndex = 0;

con.Close();

}

privatevoid button_del_Click(object sender, EventArgs e)

{

con.Open();

for (int y = 0; y < ConnectingPeople.SelectedRows.Count; y++)

{

for (int i = ConnectingPeople.SelectedRows[y].Index; i < Cons.Count; i++)

Cons[i].Row_Num--;

com = newSqlCommand("DELETE FROM [Connect] WHERE [ID_Connect] = " + Cons[ConnectingPeople.SelectedRows[y].Index].ID_Connecct.ToString(), con);

com.ExecuteNonQuery();

Cons.RemoveAt(ConnectingPeople.SelectedRows[0].Index);

}

comboBox_group.SelectedIndex = 0;

con.Close();

}

privatevoid button_Otchot_Click(object sender, EventArgs e)

{

ExportToExcel();

}

privatevoid ExportToExcel()

{

Excel.Application exApp = new Excel.Application();

exApp.Visible = true;

exApp.Workbooks.Add();

Excel._Worksheet workSheet = (Excel.Worksheet)exApp.ActiveSheet;

workSheet.Cells[1, 1] = "Распределение нагрузки на";

workSheet.Cells[1, 2] = "2014-2015";

workSheet.Cells[1, 3] = "учебныйгод";

workSheet.Cells[2, 1] = "Группа";

workSheet.Cells[3, 1] = "№ п/п";

workSheet.Cells[3, 2] = "Дисциплина";

workSheet.Cells[3, 3] = "Преподаватель";

workSheet.Cells[3, 4] = "Количествочасов";

workSheet.Cells[4, 4] = "Теория";

workSheet.Cells[4, 5] = "Лабораторные и практические работы";

workSheet.Cells[4, 6] = "Экзамены";

workSheet.Cells[4, 7] = "Курсовое проектирование";

workSheet.Cells[4, 8] = "Прием курсовых проектов";

workSheet.Cells[4, 9] = "Практика";

workSheet.Cells[4, 10] = "Дипломное проектирование";

workSheet.Cells[4, 11] = "ГАК, ГЭК";

workSheet.Cells[4, 12] = "Дополнительные занятия";

workSheet.Cells[4, 13] = "Всего";

int rowExcel = 5;

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

{

workSheet.Cells[rowExcel, "B"] = ConnectingPeople.Rows[i].Cells["Предмет"].Value;

workSheet.Cells[rowExcel, "C"] = ConnectingPeople.Rows[i].Cells["Фамилия"].Value;

workSheet.Cells[rowExcel, "D"] = ConnectingPeople.Rows[i].Cells["Теория"].Value;

workSheet.Cells[rowExcel, "E"] = ConnectingPeople.Rows[i].Cells["Лабораторные"].Value;

workSheet.Cells[rowExcel, "F"] = ConnectingPeople.Rows[i].Cells["Экзаменационные"].Value;

workSheet.Cells[rowExcel, "G"] = ConnectingPeople.Rows[i].Cells["ККП"].Value;

workSheet.Cells[rowExcel, "H"] = ConnectingPeople.Rows[i].Cells["ПКП"].Value;

workSheet.Cells[rowExcel, "I"] = ConnectingPeople.Rows[i].Cells["РПз"].Value;

workSheet.Cells[rowExcel, "J"] = ConnectingPeople.Rows[i].Cells["ДП"].Value;

workSheet.Cells[rowExcel, "K"] = ConnectingPeople.Rows[i].Cells["ГАК"].Value;

workSheet.Cells[rowExcel, "L"] = ConnectingPeople.Rows[i].Cells["ДЗ"].Value;

workSheet.Cells[rowExcel, "M"] = ConnectingPeople.Rows[i].Cells["Первое"].Value;

workSheet.Cells[rowExcel, "N"] = ConnectingPeople.Rows[i].Cells["Второе"].Value;

++rowExcel;

}

string pathToXmlFile;

pathToXmlFile = Environment.CurrentDirectory + "\\" + "Н-Техникум.xls";

workSheet.SaveAs(pathToXmlFile);

exApp.Quit();

}

privatevoid ExportToExcel1()

{

Excel.Application exApp = new Excel.Application();

exApp.Visible = true;

exApp.Workbooks.Add();

Excel._Worksheet workSheet = (Excel.Worksheet)exApp.ActiveSheet;

workSheet.Cells[1, 1] = "Распределение нагрузки на";

workSheet.Cells[1, 2] = "2014-2015";

workSheet.Cells[1, 3] = "учебныйгод";

workSheet.Cells[2, 1] = "Преподаватель";

workSheet.Cells[3, 1] = "Предмет";

workSheet.Cells[3, 2] = "Группа";

workSheet.Cells[3, 3] = "Количествочасов";

workSheet.Cells[4, 3] = "Теория";

workSheet.Cells[4, 4] = "Лабораторные и практические работы";

workSheet.Cells[4, 5] = "Экзамены";

workSheet.Cells[4, 6] = "Курсовое проектирование";

workSheet.Cells[4, 7] = "Прием курсовых проектов";

workSheet.Cells[4, 8] = "Практика";

workSheet.Cells[4, 9] = "Дипломное проектирование";

workSheet.Cells[4, 10] = "ГАК, ГЭК";

workSheet.Cells[4, 11] = "Дополнительные занятия";

workSheet.Cells[4, 12] = "Всего";

int rowExcel = 5; Otchot.

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

{

workSheet.Cells[rowExcel, "A"] = Otchot.Rows[i].Cells["Предмет"].Value;

workSheet.Cells[rowExcel, "B"] = Otchot.Rows[i].Cells["Группа"].Value;

workSheet.Cells[rowExcel, "C"] = Otchot.Rows[i].Cells["Теория"].Value;

workSheet.Cells[rowExcel, "D"] = Otchot.Rows[i].Cells["Лабораторные"].Value;

workSheet.Cells[rowExcel, "E"] = Otchot.Rows[i].Cells["Экзамен"].Value;

workSheet.Cells[rowExcel, "F"] = Otchot.Rows[i].Cells["ККП"].Value;

workSheet.Cells[rowExcel, "G"] = Otchot.Rows[i].Cells["ПКП"].Value;

workSheet.Cells[rowExcel, "H"] = Otchot.Rows[i].Cells["РПр"].Value;

workSheet.Cells[rowExcel, "I"] = Otchot.Rows[i].Cells["ДП"].Value;

workSheet.Cells[rowExcel, "J"] = Otchot.Rows[i].Cells["ГАК"].Value;

workSheet.Cells[rowExcel, "K"] = Otchot.Rows[i].Cells["ДЗ"].Value;

workSheet.Cells[rowExcel, "L"] = Otchot.Rows[i].Cells["Первое"].Value;

workSheet.Cells[rowExcel, "M"] = Otchot.Rows[i].Cells["Второе"].Value;

++rowExcel;

}

string pathToXmlFile;

pathToXmlFile = Environment.CurrentDirectory + "\\" + "Н-Преподаватьели.xls";

workSheet.SaveAs(pathToXmlFile);

exApp.Quit();

}

}

Приложение 17

ЧастькодапрограммыRaspis

public Raspis(string constrc, Form1 DIL)

{

InitializeComponent();

this.constrc = constrc;

dil = DIL;

Butts.Add(button1);Butts.Add(button2); Butts.Add(button3); Butts.Add(button4); Butts.Add(button5); Butts.Add(button6); Butts.Add(button7); Butts.Add(button8); Butts.Add(button9); Butts.Add(button10); Butts.Add(button11); Butts.Add(button12); Butts.Add(button13); Butts.Add(button14); Butts.Add(button15); Butts.Add(button16); Butts.Add(button17); Butts.Add(button18); Butts.Add(button19); Butts.Add(button20); Butts.Add(button21); Butts.Add(button22); Butts.Add(button23); Butts.Add(button24); Butts.Add(button25); Butts.Add(button26); Butts.Add(button27); Butts.Add(button28); Butts.Add(button29); Butts.Add(button30); Butts.Add(button31); Butts.Add(button32); Butts.Add(button33); Butts.Add(button34); Butts.Add(button35); Butts.Add(button36); Butts.Add(button37); Butts.Add(button38); Butts.Add(button39); Butts.Add(button40); Butts.Add(button41); Butts.Add(button42);

con = newSqlConnection(constrc);

con.Open();

comboBox_corpus.Items.Add(1);

comboBox_corpus.Items.Add(2);

com = newSqlCommand("SELECT [Surname] FROM [Teacher]", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_teacher.Items.Add((string)r[0]);

}

}

con.Close();

listBox_teacher.SelectedIndex = 0;

comboBox_corpus.SelectedIndex = 0;

}

privatevoid comboBox_corpus_SelectedIndexChanged(object sender, EventArgs e)

{

con.Open();

listBox_cabinet.Items.Clear();

com = newSqlCommand("SELECT [Number] FROM [Cabinet] WHERE [Number_campus]=" + comboBox_corpus.SelectedItem.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_cabinet.Items.Add((int)r[0]);

}

}

con.Close();

}

privatevoid listBox_teacher_SelectedIndexChanged(object sender, EventArgs e)

{

teach = listBox_teacher.SelectedItem.ToString();

con.Open();

List<TCab> ConL = newList<TCab>();

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

{

com = newSqlCommand("SELECT [ID_Teacher] FROM [Teacher] WHERE [Surname] LIKE'" + teach.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

ConL[i].IDT = (int)r[0];

}

}

}

con.Close();

}

privatevoid Teacher_Cabinet_button_Click(object sender, EventArgs e)

{

this.Visible = false;

Teacher_Cabinet f1 = newTeacher_Cabinet(constrc, this);

f1.Show();

}

int IDteacher;

int IDcab;

List<TS> Cons = newList<TS>();

privatevoid Add_Teacher_Cabinet_button_Click(object sender, EventArgs e)

{

listBox_subject.Items.Clear();

con.Open();

com = newSqlCommand("SELECT [ID_Teacher] FROM [Teacher] WHERE [Surname] LIKE '" + listBox_teacher.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDteacher = (int)r[0];

}

}

com = newSqlCommand("SELECT [ID_cabinet] FROM [Cabinet] WHERE [Number]=" + listBox_cabinet.SelectedItem.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDcab = (int)r[0];

}

}

com = newSqlCommand("SELECT [ID_teacher], [ID_Subject] FROM [Teacher_Subject] WHERE [ID_Teacher]=" + IDteacher.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

Cons.Add(newTS((int)r[0], (int)r[1]));

}

}

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

{

com = newSqlCommand("SELECT [Name_subject] FROM [Subject] WHERE [ID_subject]=" + Cons[i].IDS.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_subject.Items.Add((string)r[0]);

}

}

}

con.Close();

}

int IDsubj;

int IDTS;

privatevoid Connect_button_Click(object sender, EventArgs e)

{

try

{

con.Open();

com = newSqlCommand("SELECT [ID_subject] FROM [Subject] WHERE [Name_subject] LIKE '" + listBox_subject.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDsubj = (int)r[0];

}

}

com = newSqlCommand("SELECT [ID_Teacher_Subject] FROM [Teacher_Subject] WHERE [ID_Subject]=" + IDsubj.ToString() + "AND [ID_Teacher]=" + IDteacher.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDTS = (int)r[0];

}

}

com = newSqlCommand("INSERT INTO [Teacher_Cabinet] ([ID_Teacher_Subject], [ID_Cabinet]) VALUES ('" + IDTS.ToString() + "','" + IDcab.ToString() + "')", con);

com.ExecuteNonQuery();

MessageBox.Show("Связьпреподаватель - кабинет - предметдобавлена!");

con.Close();

}

catch

{

MessageBox.Show("Возниклаошибка, этасвязьужесуществовала");

}

}

privatevoid Exit_button_Click(object sender, EventArgs e)

{

Application.Exit();

}

int IDt1;

int IDt2;

List<ConectTeachSubject> Rt = newList<ConectTeachSubject>();

List<Connect> Conn = newList<Connect>();

privatevoid listBox_Select_SelectedIndexChanged(object sender, EventArgs e)

{

dataGridView_Info.Rows.Clear();

con.Open();

if (rb_Teacher.Checked == true)

{

com = newSqlCommand("SELECT [ID_Teacher] FROM [Teacher] WHERE [Surname] LIKE'" + listBox_Select.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt1 = (int)r[0];

}

}

com = newSqlCommand("SELECT [ID_Teacher_Subject], [ID_Teacher], [ID_Subject] FROM [Teacher_Subject] WHERE [ID_Teacher]=" + IDt1.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

Rt.Add(newConectTeachSubject((int)r[0], (int)r[1], (int)r[2]));//былпиздец

}

}

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

{

com = newSqlCommand(@"SELECT [Number], [Itog], [Surname],[Name_subject], [Grup_Name]

FROM [Teacher] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [Name_subject], [Grup_Name]

FROM [Subject] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [ID_Subject], [Grup_Name]

FROM [Cabinet] INNER Join(SELECT [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject], [Grup_Name]

FROM (SELECT [ID_Grup], [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject]

FROM [Connect] INNER JOIN [Teacher_Subject] on

Connect.ID_Teacher_Subject=Teacher_Subject.ID_Teacher_Subject

WHERE Teacher_Subject.ID_Teacher_Subject =" + Rt[i].ID_teacher_subject + @") AS t1 INNER JOIN [Grup]

ON t1.ID_Grup=Grup.ID_Grup) AS t2 ON t2.ID_Cabinet=Cabinet.ID_cabinet) as t3

ON Subject.ID_subject=t3.ID_Subject) AS t4 ON Teacher.ID_Teacher=t4.ID_Teacher", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

var a = r[0]; var b = r[1] isint ? r[1] : 0; var c = r[2]; var d = r[3]; var e1 = r[4];

Conn.Add(newConnect((string)c, (string)d, (string)e1, (int)a, (int)b));

}

}

}

dataGridView_Info.Rows.Clear();

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

{

dataGridView_Info.Rows.Add();

dataGridView_Info.Rows[i].Cells[0].Value = Conn[i].grup;

dataGridView_Info.Rows[i].Cells[1].Value = Conn[i].subject;

dataGridView_Info.Rows[i].Cells[2].Value = Conn[i].Itog.ToString();

dataGridView_Info.Rows[i].Cells[3].Value = Conn[i].cabinet.ToString();

}

int awidth3 = dataGridView_Info.Width / dataGridView_Info.ColumnCount;

for (int i = 0; i < dataGridView_Info.ColumnCount; i++)

dataGridView_Info.Columns[i].Width = awidth3;

}

else

{

if (rb_Group.Checked == true)

{

com = newSqlCommand("SELECT [ID_Grup] FROM [Grup] WHERE [Grup_Name] LIKE'" + listBox_Select.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt2 = (int)r[0];

}

}

com = newSqlCommand(@"SELECT [Number], [Itog], [Surname], [Name_subject], [Grup_Name]

FROM [Teacher] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [Name_subject], [Grup_Name]

FROM [Subject] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [ID_Subject], [Grup_Name]

FROM [Cabinet] INNER Join(SELECT [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject], [Grup_Name]

FROM (SELECT [ID_Grup], [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject]

FROM [Connect]INNER JOIN [Teacher_Subject] on [Connect].ID_Teacher_Subject=Teacher_Subject.ID_Teacher_Subject

WHERE [Connect].[ID_Grup]=" + IDt2.ToString() + @") AS t1 INNER JOIN [Grup]

ON t1.ID_Grup=Grup.ID_Grup) AS t2 ON t2.ID_Cabinet=Cabinet.ID_cabinet) as t3 ON Subject.ID_subject=t3.ID_Subject) AS t4 ON Teacher.ID_Teacher=t4.ID_Teacher", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

Conn.Add(newConnect((string)r[2], (string)r[3], (string)r[4], (int)r[0], (int)r[1]));

}

}

dataGridView_Info.Rows.Clear();

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

{

dataGridView_Info.Rows.Add();

dataGridView_Info.Rows[i].Cells[0].Value = Conn[i].teach;

dataGridView_Info.Rows[i].Cells[1].Value = Conn[i].subject;

dataGridView_Info.Rows[i].Cells[2].Value = Conn[i].Itog.ToString();

dataGridView_Info.Rows[i].Cells[3].Value = Conn[i].cabinet.ToString();

}

int awidth3 = dataGridView_Info.Width / dataGridView_Info.ColumnCount;

for (int i = 0; i < dataGridView_Info.ColumnCount; i++)

dataGridView_Info.Columns[i].Width = awidth3;

}

else

{

com = newSqlCommand("SELECT [ID_cabinet] FROM [Cabinet] WHERE [Number] =" + listBox_Select.SelectedItem.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt1 = (int)r[0];

}

}

com = newSqlCommand(@"SELECT [Number], [Itog], [Surname], [Name_subject], [Grup_Name]

FROM [Teacher] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [Name_subject], [Grup_Name]

FROM [Subject] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [ID_Subject], [Grup_Name]

FROM [Cabinet] INNER Join(SELECT [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject], [Grup_Name]

FROM (SELECT [ID_Grup], [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject]

FROM [Connect]INNER JOIN [Teacher_Subject] on Connect.ID_Teacher_Subject=Teacher_Subject.ID_Teacher_Subject

WHERE [Connect].[ID_Teacher_Subject]=" + IDt1.ToString() + @") AS t1 INNER JOIN [Grup] ON t1.ID_Grup=Grup.ID_Grup) AS t2

ON t2.ID_Cabinet=Cabinet.ID_cabinet) as t3 ON Subject.ID_subject=t3.ID_Subject) AS t4 ON Teacher.ID_Teacher=t4.ID_Teacher", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

Conn.Add(newConnect((string)r[2], (string)r[3], (string)r[4], (int)r[0], (int)r[1]));

}

}

dataGridView_Info.Rows.Clear();

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

{

dataGridView_Info.Rows.Add();

dataGridView_Info.Rows[i].Cells[0].Value = Conn[i].grup;

dataGridView_Info.Rows[i].Cells[1].Value = Conn[i].subject;

dataGridView_Info.Rows[i].Cells[2].Value = Conn[i].Itog.ToString();

dataGridView_Info.Rows[i].Cells[3].Value = Conn[i].teach;

}

int awidth3 = dataGridView_Info.Width / dataGridView_Info.ColumnCount;

for (int i = 0; i < dataGridView_Info.ColumnCount; i++)

dataGridView_Info.Columns[i].Width = awidth3;

}

}

con.Close();

}

List<TSS> Arg = newList<TSS>();

privatevoid dataGridView_Info_SelectionChanged(object sender, EventArgs e)

{

if (dataGridView_Info.SelectedRows.Count > 0)

{

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

{

Butts[i].BackColor = Color.LightGray;

Butts[i].Text = "Выбрать";

}

try

{

con.Open();

}

catch

{

con.Close();

con.Open();

}

int IDC = 0;

int IDL = 0;

int IDG = 0;

bool TUTU = true;

if (rb_Teacher.Checked == true)

{

for (int i = 0; i < 42; i++)

{

TUTU = true;

var q = dataGridView_Info.SelectedRows[0].Cells[2].Value.ToString();

com = newSqlCommand("SELECT [ID_cabinet] FROM [Cabinet] WHERE [Number] = " + dataGridView_Info.SelectedRows[0].Cells[3].Value.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

IDC = (int)r[0];

}

com = newSqlCommand("SELECT [ID_Lesson] FROM [Connect] WHERE [ID_cabinet] = " + IDC.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDL = (int)r[0];

if (IDL != 43)

{

if (((IDL - 1) / 7 == i / 7) && ((IDL % 7) == i % 7))

{

TUTU = false;

break;

}

}

}

}

if (TUTU)

{

com = newSqlCommand("SELECT [ID_Grup] FROM [Grup] WHERE [Grup_Name] LIKE '" + dataGridView_Info.SelectedRows[0].Cells[0].Value.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

IDG = (int)r[0];

}

com = newSqlCommand("SELECT [ID_Lesson] FROM [Connect] WHERE [ID_Grup] = " + IDG.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDL = (int)r[0];

if (((IDL - 1) / 7 == i / 7) && ((IDL % 7) == i % 7))

{

TUTU = false;

break;

}

}

}

}

if (TUTU)

{

Butts[i].BackColor = Color.Green;

Butts[i].Text = "Свободнаяжопка";

}

else

{

Butts[i].BackColor = Color.IndianRed;

Butts[i].Text = "Reserved";

}

}

}

else

{

if (rb_Group.Checked == true)

{

for (int i = 0; i < 42; i++)

{

var q = dataGridView_Info.SelectedRows[0].Cells[2].Value.ToString();

com = newSqlCommand("SELECT [ID_cabinet] FROM [Cabinet] WHERE [Number] = " + dataGridView_Info.SelectedRows[0].Cells[3].Value.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

IDC = (int)r[0];

}

com = newSqlCommand("SELECT [ID_Lesson] FROM [Connect] WHERE [ID_cabinet] = " + IDC.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDL = (int)r[0];

if (((IDL - 1) / 7 == i / 7) && ((IDL % 7) == i % 7))

{

TUTU = false;

break;

}

}

}

if (TUTU)

{

int ID_Teacher = 0;

com = newSqlCommand("SELECT [ID_Teacher] FROM [Teacher] WHERE [Surname] LIKE '" + dataGridView_Info.SelectedRows[0].Cells[0].Value.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

ID_Teacher = (int)r[0];

}

com = newSqlCommand("SELECT [ID_Teacher_Subject] FROM [Teacher_Subject] WHERE [ID_Teacher] =" + ID_Teacher.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

Arg.Add(newTSS((int)r[0]));

}

for (int j = 0; j < Arg.Count; j++)

{

com = newSqlCommand("SELECT [ID_Lesson] FROM [Connect] WHERE [ID_Grup] = " + Arg[i].IDTS.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDL = (int)r[0];

if (((IDL - 1) / 7 == i / 7) && ((IDL % 7) == i % 7))

{

TUTU = false;

break;

}

}

}

}

}

if (TUTU)

{

Butts[i].BackColor = Color.Green;

Butts[i].Text = "Свободнаяжопка";

}

else

{

Butts[i].BackColor = Color.IndianRed;

Butts[i].Text = "Reserved";

}

}

}

else

{

for (int i = 0; i < 42; i++)

{

com = newSqlCommand("SELECT [ID_Grup] FROM [Grup] WHERE [Grup_Name] LIKE '" + dataGridView_Info.SelectedRows[0].Cells[0].Value.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

IDC = (int)r[0];

}

com = newSqlCommand("SELECT [ID_Lesson] FROM [Connect] WHERE [ID_Grup] = " + IDC.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDL = (int)r[0];

if (((IDL - 1) / 7 == i / 7) && ((IDL % 7) == i % 7))

{

TUTU = false;

break;

}

}

}

if (TUTU)

{

com = newSqlCommand("SELECT [ID_Teacher] FROM [Teacher] WHERE [Surname] LIKE '" + dataGridView_Info.SelectedRows[0].Cells[3].Value.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

IDG = (int)r[0];

}

com = newSqlCommand("SELECT [ID_Teacher_Subject] FROM [Teacher_Subject] WHERE [ID_Teacher] =" + IDG.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

Arg.Add(newTSS((int)r[0]));

}

for (int j = 0; j < Arg.Count; j++)

{

com = newSqlCommand("SELECT [ID_Lesson] FROM [Connect] WHERE [ID_Grup] = " + Arg[i].IDTS.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDL = (int)r[0];

if (((IDL - 1) / 7 == i / 7) && ((IDL % 7) == i % 7))

{

TUTU = false;

break;

}

}

}

}

}

if (TUTU)

{

Butts[i].BackColor = Color.Green;

Butts[i].Text = "Свободнаяжопка";

}

else

{

Butts[i].BackColor = Color.IndianRed;

Butts[i].Text = "Reserved";

}

}

}

}

con.Close();

}

}

privatevoid rb_Group_CheckedChanged(object sender, EventArgs e)

{

con.Open();

listBox_Select.Items.Clear();

com = newSqlCommand("SELECT [Grup_Name] FROM [Grup]", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_Select.Items.Add((string)r[0]);

}

}

con.Close();

}

privatevoid rb_Teacher_CheckedChanged(object sender, EventArgs e)

{

con.Open();

listBox_Select.Items.Clear();

com = newSqlCommand("SELECT [Surname] FROM [Teacher]", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_Select.Items.Add((string)r[0]);

}

}

con.Close();

}

privatevoid rb_cabinet_CheckedChanged(object sender, EventArgs e)

{

con.Open();

listBox_Select.Items.Clear();

com = newSqlCommand("SELECT [Number] FROM [Cabinet]", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_Select.Items.Add((int)r[0]);

}

}

con.Close();

}

List<CONLes> CL = newList<CONLes>();

privatevoid button1_Click(object sender, EventArgs e)

{

if (button1.BackColor == Color.Green)

{

con.Open();

int IDCon = 0;

com = newSqlCommand(@"SELECT [ID_Connect] FROM

(SELECT [ID_Connect], [ID_Teacher_Subject] FROM

(Select [ID_Connect], [ID_Teacher_Subject], [ID_Grup]

FROM [Connect] INNER Join

(select [ID_Cabinet] FROM [Cabinet] WHERE [Number] =" + dataGridView_Info.SelectedRows[0].Cells[3].Value.ToString() + @")as t1

on [Connect].ID_Cabinet = t1.ID_cabinet) as t2 INNER JOIN

(SELECT [ID_Grup] FROM [Grup] WHERE [Grup_Name] Like '" + dataGridView_Info.SelectedRows[0].Cells[0].Value.ToString() + @"' )as t3

on t2.ID_Grup=t3.ID_Grup) as t4 INNER JOIN

(SELECT [ID_Teacher_Subject] FROM (SELECT [ID_Teacher_Subject], [ID_Subject] FROM Teacher_Subject INNER JOIN

(SELECT [ID_Teacher] from Teacher where [Surname] LIKE '" + listBox_Select.SelectedItem.ToString() + @"' )as t6 on

Teacher_Subject.ID_Teacher=t6.ID_Teacher) as t8 INNER JOIN

(SELECT [ID_Subject] FROM [Subject] WHERE [Name_subject] LIKE '" + dataGridView_Info.SelectedRows[0].Cells[1].ToString() + @"' )as t7 on t8.ID_Subject=t7.ID_subject)as t5

on t5.ID_Teacher_Subject=t4.ID_Teacher_Subject", con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

IDCon = (int)r[0];

}

com = newSqlCommand(@"SELECT [ID_Lesson], [ID_Connect], [Itog], [ID_Cabinet], [ID_Teacher_Subject], [ID_Grup] FROM [Connect] WHERE [ID_Connect]=" + IDCon.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

r.Read();

CL.Add(newCONLes((int)r[0], (int)r[1], (int)r[2], (int)r[3], (int)r[4], (int)r[5]));

}

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

{

if ((CL[i].itog != 0) && (CL[i].IDl == 43))

{

com = newSqlCommand(@"UPDATE [dbo].[Connect]

SET [Itog] = [Itog] - 1

WHERE [dbo].[Connect].[ID_Connect] = " + IDCon.ToString(), con);

com.ExecuteNonQuery();

int a = 1;

com = newSqlCommand("INSERT INTO [Connect] ([ID_Lesson], [ID_Cabinet], [ID_Grup], [Itog]) VALUES ('" + a.ToString() + "','" + CL[i].IDcab.ToString() + "','" + CL[i].IDg.ToString() + "','" + a.ToString() + "')", con);

com.ExecuteNonQuery();

}

else

{

if ((CL[i].itog != 0) && (CL[i].IDl != 43))

{

com = newSqlCommand(@"UPDATE [dbo].[Connect]

SET [Itog] = [Itog] + 1

WHERE [dbo].[Connect].[ID_Connect] = " + IDCon.ToString(), con);

com.ExecuteNonQuery();

}

else

{

MessageBox.Show("Ошибка");

}

}

}

con.Close();

}

}

privatevoid comboBox_corpus_SelectedIndexChanged_1(object sender, EventArgs e)

{

con.Open();

listBox_cabinet.Items.Clear();

com = newSqlCommand("SELECT [Number] FROM [Cabinet] WHERE [Number_campus]=" + comboBox_corpus.SelectedItem.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

listBox_cabinet.Items.Add((int)r[0]);

}

}

con.Close();

}

privatevoid listBox_Select_SelectedIndexChanged_1(object sender, EventArgs e)

{

dataGridView_Info.Rows.Clear();

con.Open();

if (rb_Teacher.Checked == true)

{

com = newSqlCommand("SELECT [ID_Teacher] FROM [Teacher] WHERE [Surname] LIKE'" + listBox_Select.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt1 = (int)r[0];

}

}

com = newSqlCommand("SELECT [ID_Teacher_Subject], [ID_Teacher], [ID_Subject] FROM [Teacher_Subject] WHERE [ID_Teacher]=" + IDt1.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

Rt.Add(newConectTeachSubject((int)r[0], (int)r[1], (int)r[2]));//былпиздец

}

}

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

{

com = newSqlCommand(@"SELECT [Number], [Itog], [Surname],[Name_subject], [Group_Name]

FROM [Teacher] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [Name_subject], [Group_Name]

FROM [Subject] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [ID_Subject], [Group_Name]

FROM [Cabinet] INNER Join(SELECT [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject], [Group_Name]

FROM (SELECT [ID_Group], [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject]

FROM [Connect] INNER JOIN [Teacher_Subject] on

Connect.ID_Teacher_Subject=Teacher_Subject.ID_Teacher_Subject

WHERE Teacher_Subject.ID_Teacher_Subject " + Rt[i].ID_teacher_subject + @") AS t1 INNER JOIN[Grup]

ON t1.ID_Group = Grup.ID_Group) AS t2 ON t2.ID_Cabinet = Cabinet.ID_cabinet) as t3

ON Subject.ID_subject = t3.ID_Subject) AS t4 ON Teacher.ID_Teacher = t4.ID_Teacher", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

var a = r[0]; var b = r[1] isint ? r[1] : 0; var c = r[2]; var d = r[3]; var e1 = r[4];

Conn.Add(newConnect((string)c, (string)d, (string)e1, (int)a, (int)b));

}

}

}

dataGridView_Info.Rows.Clear();

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

{

dataGridView_Info.Rows.Add();

dataGridView_Info.Rows[i].Cells[0].Value = Conn[i].grup;

dataGridView_Info.Rows[i].Cells[1].Value = Conn[i].subject;

dataGridView_Info.Rows[i].Cells[2].Value = Conn[i].Itog.ToString();

dataGridView_Info.Rows[i].Cells[3].Value = Conn[i].cabinet.ToString();

}

int awidth3 = dataGridView_Info.Width / dataGridView_Info.ColumnCount;

for (int i = 0; i < dataGridView_Info.ColumnCount; i++)

dataGridView_Info.Columns[i].Width = awidth3;

}

else

{

if (rb_Group.Checked == true)

{

com = newSqlCommand("SELECT [ID_Grup] FROM [Grup] WHERE [Grup_Name] LIKE'" + listBox_Select.SelectedItem.ToString() + "'", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt2 = (int)r[0];

}

}

com = newSqlCommand(@"SELECT [Number], [Itog], [Surname], [Name_subject], [Group_Name]

FROM [Teacher] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [Name_subject], [Group_Name]

FROM [Subject] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [ID_Subject], [Group_Name]

FROM [Cabinet] INNER Join(SELECT [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject], [Group_Name]

FROM (SELECT [ID_Group], [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject]

FROM [Connect]INNER JOIN [Teacher_Subject] on [Connect].ID_Teacher_Subject=Teacher_Subject.ID_Teacher_Subject

WHERE [Connect].[ID_Group]=" + IDt2.ToString() + @") AS t1 INNER JOIN [Grup]

ON t1.ID_Group=Grup.ID_Group) AS t2 ON t2.ID_Cabinet=Cabinet.ID_cabinet) as t3 ON Subject.ID_subject=t3.ID_Subject) AS t4 ON Teacher.ID_Teacher=t4.ID_Teacher", con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

Conn.Add(newConnect((string)r[2], (string)r[3], (string)r[4], (int)r[0], (int)r[1]));

}

}

dataGridView_Info.Rows.Clear();

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

{

dataGridView_Info.Rows.Add();

dataGridView_Info.Rows[i].Cells[0].Value = Conn[i].teach;

dataGridView_Info.Rows[i].Cells[1].Value = Conn[i].subject;

dataGridView_Info.Rows[i].Cells[2].Value = Conn[i].Itog.ToString();

dataGridView_Info.Rows[i].Cells[3].Value = Conn[i].cabinet.ToString();

}

int awidth3 = dataGridView_Info.Width / dataGridView_Info.ColumnCount;

for (int i = 0; i < dataGridView_Info.ColumnCount; i++)

dataGridView_Info.Columns[i].Width = awidth3;

}

else

{

com = newSqlCommand("SELECT [ID_cabinet] FROM [Cabinet] WHERE [Number] =" + listBox_Select.SelectedItem.ToString(), con);

using (SqlDataReader r = com.ExecuteReader())

{

while (r.Read())

{

IDt1 = (int)r[0];

}

}

com = newSqlCommand(@"SELECT [Number], [Itog], [Surname], [Name_subject], [Group_Name]

FROM [Teacher] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [Name_subject], [Group_Name]

FROM [Subject] INNER JOIN (SELECT [Number], [Itog], [ID_Teacher], [ID_Subject], [Group_Name]

FROM [Cabinet] INNER Join(SELECT [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject], [Group_Name]

FROM (SELECT [ID_Group], [ID_Cabinet], [Itog], [ID_Teacher], [ID_Subject]

FROM [Connect]INNER JOIN [Teacher_Subject] on Connect.ID_Teacher_Subject=Teacher_Subject.ID_Teacher_Subject