public void CheckCorrectStudentData()
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlExist = "SELECT ID FROM STUDENT WHERE ID_group = @id_group AND FIO LIKE @fio";
SQLiteCommand command = new SQLiteCommand(sqlExist, connection);
command.Parameters.Add(new SQLiteParameter("@id_group", DbType.Int64));
command.Parameters[0].Value = id_group;
command.Parameters.Add(new SQLiteParameter("@fio", DbType.String));
for (int i = 0; i < dataGrid.Rows.Count; i++)
{
command.Parameters[1].Value = "%" + dataGrid[0, i].Value.ToString() + "%";
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
{
dataGrid.Rows[i].Tag = Convert.ToInt64(reader[0]);
}
else
{
dataGrid.Rows[i].Tag = -1;
}
reader.Close();
}
connection.Close();
}
private bool Save()
{
string[] columns;
object[] paramValue;
string name = textBoxName.Text.Trim();
string kod = textBoxKod.Text.Trim();
if (id_specialization == -1)
{
columns = new string[] { "Name", "KOD", "ID_SPECIALTY" };
paramValue = new object[] { name, kod, kodSpecialty };
}
else
{
columns = new string[] { "Name", "KOD", "ID_SPECIALTY", "ID_SPECIALIZATION" };
paramValue = new object[] { name, kod, kodSpecialty, id_specialization };
}
int rowid = SQLiteManager.InsertValue("COMPETENCE", columns, paramValue);
if (rowid == -1)
{
return(false);
}
parentForm.AddCompetenceInDataGrid(name, kod, rowid, kodSpecialty, id_specialization);
return(true);
}
private void InitialRowsDataGridAssessment()
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sql = "SELECT ID_competence, Assessment FROM Assessment_Competence WHERE ID_student = @id_stud";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64));
for (int i = 0; i < dataGridViewAssessment.Rows.Count; i++)
{
command.Parameters[0].Value = studentID[i];
dataGridViewAssessment.Rows[i].Tag = studentID[i];
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int index_column = GetIndexColumnForIdCompetence(Convert.ToInt64(reader[0]));
if (index_column == -1)
{
continue;
}
dataGridViewAssessment[index_column, i].Value = Convert.ToDouble(reader[1]);
}
reader.Close();
}
connection.Close();
}
/// <summary>
/// Считывает все страницы эксель файла с данными об оценках за семестер для учебных групп.
/// Возвращает массив с информацией о каждой группе.
/// </summary>
/// <returns></returns>
public RatingGroup[] ReadAllTable()
{
int countSheets = getCountSheets();
if (countSheets < 1)
{
return(null);
}
RatingGroup[] result = new RatingGroup[countSheets];
for (int i = 0; i < countSheets; i++)
{
//String.Equals(getNameSheet().Trim(), "7133", StringComparison.OrdinalIgnoreCase)
string nameGroup = getNameSheet().Trim();
if (!SQLiteManager.IsExistValue("StudyGroup", "NumGroup", nameGroup))
{ // указанная группа не существует в БД, поэтому ничего считывать не будем
result[i] = new RatingGroup(null, false, false, nameGroup);
}
else
{
DataTable table = ReadTable();
if (table == null)
{
result[i] = new RatingGroup(null, true, false, nameGroup);
}
else
{
result[i] = new RatingGroup(table, true, true, nameGroup);
}
}
NextSheet();
}
return(result);
}
private void CheckCorrectData()
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlExist = "SELECT ID, NAME FROM COMPETENCE WHERE ID_specialty = @id_spec AND KOD = @kod";
SQLiteCommand command = new SQLiteCommand(sqlExist, connection);
command.Parameters.Add(new SQLiteParameter("@kod", DbType.String));
command.Parameters.AddWithValue("@id_spec", kod_specialty);
SQLiteDataReader reader;
for (int i = 0; i < dataGrid.Rows.Count; i++)
{
command.Parameters[0].Value = dataGrid.Rows[i].Cells[0].Value.ToString();
reader = command.ExecuteReader();
if (reader.Read())
{ // если в БД есть такая компетенция
dataGrid.Rows[i].Tag = Convert.ToInt64(reader[0]);
dataGrid.Rows[i].Cells[1].Value = reader[1].ToString();
}
else
{ // компетенция не найдена в БД
dataGrid.Rows[i].Tag = -1;
dataGrid.Rows[i].DefaultCellStyle.BackColor = Color.Yellow;
}
reader.Close();
}
connection.Close();
}
/// <summary>
/// Загружает данные для команды. Команда должна быть без подключения.Данные, возвращаемые
/// командой, должны содержать столбцы, определённые при создании объекта.
/// </summary>
/// <param name="command"></param>
/// <returns>true - если данные загружены успешно, иначе false</returns>
public bool LoadData(SQLiteCommand command)
{
try
{
table.Rows.Clear();
SQLiteConnection connection = SQLiteManager.CreateConnection();
command.Connection = connection;
SQLiteDataReader reader = command.ExecuteReader();
int countColumn = table.Columns.Count;
while (reader.Read())
{
DataRow row = table.NewRow();
for (int i = 0; i < countColumn; i++)
{
row[columns[i]] = reader[columns[i]].ToString();
}
table.Rows.Add(row);
}
reader.Close();
connection.Close();
return(true);
}
catch
{
return(false);
}
}
private bool SaveData()
{
try
{
string sqlInsert = "INSERT INTO WorkProgramm(NameDiscipline, ID_specialization, Semester, FormReport) VALUES (@name, @id_spec, @sem, @report)";
SQLiteCommand command = new SQLiteCommand(sqlInsert);
command.Parameters.Add("@name", DbType.String);
command.Parameters.Add("@id_spec", DbType.Int64);
command.Parameters["@id_spec"].Value = managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID");
command.Parameters.Add("@sem", DbType.String);
command.Parameters.Add("@report", DbType.String);
SQLiteConnection connection = SQLiteManager.CreateConnection();
command.Connection = connection;
foreach (DataGridViewRow row in dataGridViewWorkPlan.Rows)
{
command.Parameters[0].Value = row.Cells[0].Value;
command.Parameters[2].Value = row.Cells[1].Value;
command.Parameters[3].Value = row.Cells[2].Value;
command.ExecuteNonQuery();
}
connection.Close();
return(true);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Ошибка");
return(false);
}
}
private void CheckCorrectDataForEdit(string value_kod, int index_row)
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlExist = "SELECT ID, NAME FROM COMPETENCE WHERE ID_specialty = @id_spec AND KOD = @kod";
SQLiteCommand command = new SQLiteCommand(sqlExist, connection);
command.Parameters.AddWithValue("@kod", value_kod);
command.Parameters.AddWithValue("@id_spec", kod_specialty);
SQLiteDataReader reader;
reader = command.ExecuteReader();
if (reader.Read())
{ // если в БД есть такая компетенция
dataGrid.Rows[index_row].Tag = Convert.ToInt64(reader[0]);
dataGrid.Rows[index_row].Cells[1].Value = reader[1].ToString();
dataGrid.Rows[index_row].DefaultCellStyle.BackColor = Color.White;
}
else
{ // компетенция не найдена в БД
dataGrid.Rows[index_row].Tag = -1;
dataGrid.Rows[index_row].DefaultCellStyle.BackColor = Color.Yellow;
}
reader.Close();
connection.Close();
}
public void CheckCorrectDisciplineData()
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlExist = "SELECT ID FROM WorkProgramm WHERE ID_specialization = @id_spec AND NameDiscipline LIKE @name";
SQLiteCommand command = new SQLiteCommand(sqlExist, connection);
command.Parameters.Add(new SQLiteParameter("@id_spec", DbType.Int64));
command.Parameters[0].Value = id_specialization;
command.Parameters.Add(new SQLiteParameter("@name", DbType.String));
for (int i = 1; i < dataGrid.Columns.Count; i++)
{
command.Parameters[1].Value = "%" + dataGrid.Columns[i].HeaderText + "%";
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
{
dataGrid.Columns[i].Tag = Convert.ToInt64(reader[0]);
}
else
{
dataGrid.Columns[i].Tag = -1;
}
reader.Close();
}
connection.Close();
}
private void buttonSave_Click(object sender, EventArgs e)
{
if (comboBoxSpecialty.SelectedIndex == -1)
{
MessageBox.Show("Сначала выберите специальность группы", "Уведомление");
return;
}
if (comboBoxSpecialization.SelectedIndex == -1)
{
MessageBox.Show("Сначала выберите специализацию группы", "Уведомление");
return;
}
if (comboBoxNumGroup.SelectedIndex == -1)
{
MessageBox.Show("Сначала выберите номер группы", "Уведомление");
return;
}
long id_specialization = Convert.ToInt64(managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID"));
string numGroup = comboBoxNumGroup.Items[comboBoxNumGroup.SelectedIndex].ToString();
int kurs = Program.GetNumKurs(numGroup);
if (SQLiteManager.IsExistValue("StudyGroup", "NumGroup", numGroup))
{
MessageBox.Show("В базе данных уже есть группа под таким номером.", "Ошибка");
return;
}
parentForm.AddNewGroup(id_specialization, numGroup, kurs);
Close();
}
private void CheckCorrectData()
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlExist = "SELECT EXISTS(SELECT ID FROM COMPETENCE WHERE ID_specialty = @id_spec AND KOD = @kod)";
SQLiteCommand command = new SQLiteCommand(sqlExist, connection);
command.Parameters.Add(new SQLiteParameter("@kod", DbType.String));
command.Parameters.AddWithValue("@id_spec", kod_specialty);
SQLiteDataReader reader;
for (int i = 0; i < dataGrid.Rows.Count; i++)
{
command.Parameters[0].Value = dataGrid.Rows[i].Cells[0].Value.ToString();
reader = command.ExecuteReader();
if (reader.Read() && reader[0].ToString() == "1")
{ // если в БД уже есть такая компетенция
dataGrid.Rows[i].DefaultCellStyle.BackColor = Color.Yellow;
// строка не готова к загрузке в базу
dataGrid.Rows[i].Tag = false;
}
else
{
dataGrid.Rows[i].Tag = true;
}
reader.Close();
}
connection.Close();
}
public void Save()
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sql;
if (IsSpecNeed) // если специализация должна быть указана
{
sql = "INSERT INTO COMPETENCE(NAME, KOD, ID_SPECIALTY, ID_SPECIALIZATION) VALUES(@name, @kod, @id_spec, @id_specialization)";
}
else
{
sql = "INSERT INTO COMPETENCE(NAME, KOD, ID_SPECIALTY) VALUES(@name, @kod, @id_spec)";
}
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.Parameters.Add(new SQLiteParameter("@name", DbType.String));
command.Parameters.Add(new SQLiteParameter("@kod", DbType.String));
command.Parameters.AddWithValue("@id_spec", kod_specialty);
if (IsSpecNeed)
{
command.Parameters.AddWithValue("@id_specialization", Convert.ToInt64(managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID")));
}
for (int i = 0; i < dataGrid.Rows.Count; i++)
{
command.Parameters[0].Value = dataGrid.Rows[i].Cells[1].Value.ToString();
command.Parameters[1].Value = dataGrid.Rows[i].Cells[0].Value.ToString();
command.ExecuteNonQuery();
}
connection.Close();
}
/// <summary>
/// Проверяет, есть ли для слушателей информация об их успеваемости за этот семестер.
/// Если есть, то помечает соответствующих слушателей.
/// </summary>
private void CheckExistDataInDB()
{
IsExistDataUpdate = false;
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlExist = "SELECT EXISTS(SELECT * FROM Assessment_Discipline WHERE ID_student = @id_stud AND Semester = @sem)";
SQLiteCommand command = new SQLiteCommand(sqlExist, connection);
command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64));
command.Parameters.Add(new SQLiteParameter("@sem", DbType.Int64));
command.Parameters[1].Value = semester;
SQLiteDataReader reader;
for (int i = 0; i < dataGrid.Rows.Count; i++)
{
long id_stud = Convert.ToInt64(dataGrid.Rows[i].Tag);
if (id_stud == -1)
{
continue;
}
command.Parameters[0].Value = id_stud;
reader = command.ExecuteReader();
if (reader.Read() && reader[0].ToString() == "1")
{ // если в БД уже есть инфа о слушателе за семестер, то нельзя грузить эти данные
dataGrid.Rows[i].Tag = 0;
IsExistDataUpdate = true;
}
reader.Close();
}
connection.Close();
}
/// <summary>
/// Функция сохранения всей информации в БД.
/// </summary>
private bool SaveData()
{
try
{
// Добавляем новых пользователей в БД и записываем их ID в Таг строки
for (int i = 0; i < dataGrid.Rows.Count; i++)
{
if (Convert.ToInt64(dataGrid.Rows[i].Tag) == -1)
{
int rowid = SQLiteManager.InsertValue("Student", new string[] { "FIO", "ID_group" }, new object[] { dataGrid[0, i].Value.ToString().Trim(), id_group });
dataGrid.Rows[i].Tag = rowid;
}
}
// Проходим по каждому пользователю и добавляем в БД новую запись об оценке за семестер по каждой дисциплине
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlInsert = "INSERT INTO Assessment_Discipline(Assessment, ID_student, ID_discipline, Semester) VALUES(@ass, @id_stud, @id_disp, @sem)";
SQLiteCommand command = new SQLiteCommand(sqlInsert, connection);
command.Parameters.Add(new SQLiteParameter("@ass", DbType.Int64));
command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64));
command.Parameters.Add(new SQLiteParameter("@id_disp", DbType.Int64));
command.Parameters.AddWithValue("@sem", semester);
for (int i = 0; i < dataGrid.Rows.Count; i++)
{
long id_student = Convert.ToInt64(dataGrid.Rows[i].Tag);
if (id_student == -1)
{
continue;
}
for (int j = 1; j < dataGrid.Columns.Count; j++)
{
long id_disp = Convert.ToInt64(dataGrid.Columns[j].Tag);
if (id_disp == -1)
{
continue;
}
int assessment;
if (dataGrid[j, i].Value == null)
{
assessment = 0;
}
else
{
assessment = getValueAssessment(dataGrid[j, i].Value.ToString());
}
command.Parameters[0].Value = assessment;
command.Parameters[1].Value = id_student;
command.Parameters[2].Value = id_disp;
command.ExecuteNonQuery();
}
}
connection.Close();
return(true);
}
catch (Exception e)
{
MessageBox.Show("В процессе сохранения произошла ошибка.\n" + e.Message, "Ошибка");
return(false);
}
}
private void buttonSave_Click(object sender, EventArgs e)
{
if (String.IsNullOrWhiteSpace(textBox1.Text))
{
MessageBox.Show("Сначала введите ФИО", "Уведомление");
return;
}
int rowid = SQLiteManager.InsertValue("Student", new string[] { "FIO", "ID_group" }, new object[] { textBox1.Text.Trim(), id_group });
SQLiteConnection connection = SQLiteManager.CreateConnection();
SQLiteCommand command = new SQLiteCommand("SELECT ID, Semester FROM WorkProgramm WHERE ID_specialization = @id_spec", connection);
command.Parameters.AddWithValue("@id_spec", id_specialization);
List <long> IDdiscipline = new List <long>();
ArrayList listSemesterDiscipline = new ArrayList();
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string[] sem = reader[1].ToString().Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
if (sem == null || sem.Length == 0)
{
continue;
}
IDdiscipline.Add(Convert.ToInt64(reader[0]));
int[] temp = new int[sem.Length];
for (int i = 0; i < sem.Length; i++)
{
temp[i] = Convert.ToInt32(sem[i]);
}
listSemesterDiscipline.Add(temp);
}
reader.Close();
if (listSemesterDiscipline.Count != 0)
{
command.Parameters.Clear();
command.CommandText = "INSERT INTO Assessment_Discipline(ID_student, ID_discipline, Semester) VALUES(@id_stud, @id_disp, @sem)";
command.Parameters.AddWithValue("@id_stud", rowid);
command.Parameters.Add(new SQLiteParameter("@id_disp", DbType.Int64));
command.Parameters.Add(new SQLiteParameter("@sem", DbType.Int32));
for (int i = 0; i < listSemesterDiscipline.Count; i++)
{
int[] sem = (int[])listSemesterDiscipline[i];
command.Parameters[1].Value = IDdiscipline[i];
for (int j = 0; j < sem.Length; j++)
{
if (sem[j] <= semester)
{
command.Parameters[2].Value = sem[j];
command.ExecuteNonQuery();
}
}
}
}
parentForm.AddNewStudent(rowid, id_group, textBox1.Text.Trim());
connection.Close();
Close();
}
/// <summary>
/// Обновляет значения в ячейках таблицы оценок компетенций
/// </summary>
private void UpdateValueAssessmentInDataGrid()
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlInsert = "INSERT INTO Assessment_Competence(ID_student, ID_competence) VALUES ";
//SQLiteCommand command = new SQLiteCommand("INSERT INTO Assessment_Competence(ID_student, ID_competence) VALUES(@id_stud, @id_comp)", connection);
//command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64));
//command.Parameters.Add(new SQLiteParameter("@id_comp", DbType.Int64));
SQLiteCommand command = new SQLiteCommand();
command.Connection = connection;
for (int i = 0; i < dataGridViewAssessment.Rows.Count; i++)
{
double mean_value = 0;
double count_notNull_value = 0;
//command.Parameters[0].Value = Convert.ToInt64(dataGridViewAssessment.Rows[i].Tag);
command.Parameters.Clear();
sqlInsert = "INSERT INTO Assessment_Competence(ID_student, ID_competence) VALUES ";
command.Parameters.Add(new SQLiteParameter("@id_stud", Convert.ToInt64(dataGridViewAssessment.Rows[i].Tag)));
int current_param = 0;
for (int j = 2; j < dataGridViewAssessment.Columns.Count; j++)
{
if (dataGridViewAssessment[j, i].Tag == null)
{
dataGridViewAssessment[j, i].Tag = false;
}
if (dataGridViewAssessment[j, i].Value == null)
{ // значит в базе нет записи слушатель_компетенция, создадим её
dataGridViewAssessment[j, i].Value = 0;
string param_name = "@param" + (++current_param).ToString();
command.Parameters.Add(new SQLiteParameter(param_name, competenceID[j - 2]));
sqlInsert += "(@id_stud, " + param_name + "),";
//command.Parameters[1].Value = competenceID[j - 2];
//command.ExecuteNonQuery();
}
else if (Convert.ToDouble(dataGridViewAssessment[j, i].Value) != 0)
{
mean_value += Convert.ToDouble(dataGridViewAssessment[j, i].Value);
count_notNull_value += 1.0d;
}
}
if (current_param != 0)
{
sqlInsert = sqlInsert.Remove(sqlInsert.Length - 1);
command.CommandText = sqlInsert;
command.ExecuteNonQuery();
}
if (count_notNull_value == 0)
{
dataGridViewAssessment[1, i].Value = 0;
}
else
{
dataGridViewAssessment[1, i].Value = string.Format("{0:N2}", mean_value / count_notNull_value);
}
}
connection.Close();
}
/// <summary>
/// Открывает соединение и загружает данные в дата грид. ВАЖНО - команда должна быть без соединения.
/// </summary>
/// <param name="command"></param>
public void LoadData(SQLiteCommand command)
{
ClearDataGrid();
connection = SQLiteManager.CreateConnection();
command.Connection = connection;
adapter = new SQLiteDataAdapter(command);
adapter.Fill(dataSet, NameTable);
comandBuilder = new SQLiteCommandBuilder(adapter);
BindingSource bSource = new BindingSource();
bSource.DataSource = dataSet.Tables[0];
dataGridView.DataSource = bSource;
}
private void InitialID()
{
string temp = SQLiteManager.GetValueFromDB("StudyGroup", "ID", "NumGroup", NameGroup);
if (!String.IsNullOrWhiteSpace(temp))
{
this.id_group = Convert.ToInt64(temp);
}
temp = SQLiteManager.GetValueFromDB("StudyGroup", "ID_specialization", "NumGroup", NameGroup);
if (!String.IsNullOrWhiteSpace(temp))
{
this.id_specialization = Convert.ToInt64(temp);
}
}
public void AddNewGroup(long id_specialization, string numGroup, long kurs)
{
int rowid = SQLiteManager.InsertValue("StudyGroup", new string[] { "ID_specialization", "NumGroup", "Kurs" },
new object[] { id_specialization, numGroup, kurs });
BindingSource bind = (BindingSource)dataGridViewGroup.DataSource;
DataTable table = (DataTable)bind.DataSource;
DataRow row = table.NewRow();
row["ID"] = Convert.ToInt64(rowid);
row["ID_specialization"] = id_specialization;
row["NumGroup"] = numGroup;
row["Kurs"] = kurs;
table.Rows.Add(row);
row.AcceptChanges();
dataGridViewGroup.Refresh();
}
private void dataGridViewGroup_SelectionChanged_1(object sender, EventArgs e)
{
if (dataGridViewGroup.SelectedRows.Count == 0 || dataGridViewGroup.SelectedRows[0] == null)
{
return;
}
currentNumGroup = dataGridViewGroup.SelectedRows[0].Cells["NumGroup"].Value.ToString();
id_group_current = Convert.ToInt64(dataGridViewGroup.SelectedRows[0].Cells["ID"].Value);
dataGridViewStudent.Tag = id_group_current;
labelGroup.Text = "Группа " + currentNumGroup;
string specName = SQLiteManager.GetValueFromDB("SPECIALIZATION", "Name", "ID", Convert.ToInt64(dataGridViewGroup.SelectedRows[0].Cells["ID_specialization"].Value));
id_specialization_current = Convert.ToInt64(dataGridViewGroup.SelectedRows[0].Cells["ID_specialization"].Value);
labelSpec.Text = "Специализация: " + specName;
LoadStudent(id_group_current);
}
public void Save()
{
long id_discipline = Convert.ToInt64(managerDiscipline.GetCellValue(comboBoxDiscipline.SelectedIndex, "ID"));
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sql = "INSERT INTO Competence_Discipline(ID_competence, ID_discipline) VALUES(@id_comp, @id_disp)";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.Parameters.Add(new SQLiteParameter("@id_comp", DbType.Int64));
command.Parameters.AddWithValue("@id_disp", id_discipline);
for (int i = 0; i < dataGrid.Rows.Count; i++)
{
command.Parameters[0].Value = Convert.ToInt64(dataGrid.Rows[i].Tag);
command.ExecuteNonQuery();
}
connection.Close();
}
private bool Save()
{
string semester = "";
foreach (int index in checkedListBox1.CheckedIndices)
{
semester += (index + 1).ToString() + ";";
}
int rowid = SQLiteManager.InsertValue("WorkProgramm", new string[] { "NameDiscipline", "ID_specialization", "Semester" },
new object[] { textBoxDiscipline.Text, id_specialization, semester });
if (rowid == -1)
{
return(false);
}
parentForm.AddDisciplineInDataGrid(textBoxDiscipline.Text, rowid, id_specialization, semester);
return(true);
}
private void UpdateState()
{
bool flag = false;
for (int i = 0; i < dataGridViewWorkPlan.Rows.Count; i++)
{
DataGridViewRow row = dataGridViewWorkPlan.Rows[i];
bool IsRowExistInDB = SQLiteManager.IsExistValue("WorkProgramm", new string[] { "NameDiscipline", "ID_specialization" },
new string[] { row.Cells[0].Value.ToString(), managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID") });
if (IsRowExistInDB)
{
flag = true;
row.DefaultCellStyle.BackColor = Color.Yellow;
}
}
if (flag)
{
MessageBox.Show("Среди загружаемых дисциплин найдены дисциплины, уже загруженые в базу. Они подсвечены жёлтым.", "Уведомление");
}
}
// Принимаем на вход значения айди и весов дисциплин в формировании компетенции и нормирует их, если это необходимо.
// Вовзрашает обратно лист значений и айди с ЗНАЧИМЫМИ дисциплинами.
private void CheckWeightCorrect(ref List <double> listWeight, ref List <long> listID, long id_competence)
{
double sum = 0;
for (int i = 0; i < listWeight.Count; i++)
{
sum += listWeight[i];
}
if (sum == 0)
{ // если сумма 0, значит нужно задать равные веса
for (int k = 0; k < listWeight.Count; k++)
{
listWeight[k] = 1.0 / listWeight.Count;
}
// Сохраним эти значения в БД
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlSelectDiscipline = "UPDATE Competence_Discipline SET Weight = @w WHERE ID_competence = @id_comp AND ID_discipline = @id_disp";
SQLiteCommand command = new SQLiteCommand(sqlSelectDiscipline, connection);
command.Parameters.AddWithValue("@w", listWeight[0]);
command.Parameters.AddWithValue("@id_comp", id_competence);
command.Parameters.Add(new SQLiteParameter("@id_disp", DbType.Int64));
for (int i = 0; i < listID.Count; i++)
{
command.Parameters[2].Value = listID[i];
command.ExecuteNonQuery();
}
connection.Close();
}
else
{ // иначе просто удалим из списков дисциплины с нулевым значением веса
for (int i = listWeight.Count - 1; i >= 0; i--)
{
if (listWeight[i] == 0)
{
listWeight.RemoveAt(i);
listID.RemoveAt(i);
}
}
}
}
private bool SaveSpecialization()
{
int rowid = SQLiteManager.InsertValue("Specialization", new string[] { "Number", "Name", "specialty_fk" },
new string[] { numericUpDown1.Value.ToString(), textBox2.Text, kodParentSpec });
if (rowid == -1)
{
return(false);
}
DataGridView d = (DataGridView)parentForm.Controls["dataGridViewSpecialization"];
BindingSource bind = (BindingSource)d.DataSource;
DataTable table = (DataTable)bind.DataSource;
DataRow row = table.NewRow();
row["Number"] = numericUpDown1.Value.ToString();
row["Name"] = textBox2.Text;
row["ID"] = rowid;
row["specialty_fk"] = kodParentSpec;
table.Rows.Add(row);
row.AcceptChanges();
d.Refresh();
return(true);
}
private bool ValidateStateWorkPlan()
{
for (int i = 0; i < dataGridViewWorkPlan.Rows.Count; i++)
{
DataGridViewRow row = dataGridViewWorkPlan.Rows[i];
foreach (DataGridViewCell cell in row.Cells)
{
if (String.IsNullOrWhiteSpace(cell.Value.ToString()))
{
MessageBox.Show("В строке " + (i + 1) + "пустая ячейка!", "Ошибка");
return(false);
}
}
bool IsRowExistInDB = SQLiteManager.IsExistValue("WorkProgramm", new string[] { "NameDiscipline", "ID_specialization" },
new string[] { row.Cells[0].Value.ToString(), managerSpecialization.GetCellValue(comboBoxSpecialization.SelectedIndex, "ID") });
if (IsRowExistInDB)
{
MessageBox.Show("Дисциплина " + row.Cells[0].Value.ToString() + " уже существует в базе!", "Ошибка");
return(false);
}
}
return(true);
}
public void AddCompetenceChosenDiscipline(System.Collections.ArrayList ListDataGridRow)
{
if (ListDataGridRow == null || ListDataGridRow.Count == 0)
{
return;
}
BindingSource bind = (BindingSource)dataGridCompChosenDiscip.DataSource;
DataTable table = (DataTable)bind.DataSource;
for (int i = 0; i < ListDataGridRow.Count; i++)
{
bool flag_add = true;
DataGridViewRow rowAdd = (DataGridViewRow)ListDataGridRow[i];
foreach (DataGridViewRow rowCurrent in dataGridCompChosenDiscip.Rows)
{
if (Convert.ToInt64(rowCurrent.Cells["ID"].Value) == Convert.ToInt64(rowAdd.Cells["ID"].Value))
{
flag_add = false;
break;
}
}
if (flag_add)
{
// добавляем компетенцию в дата грид
DataRow row = table.NewRow();
row["ID"] = rowAdd.Cells["ID"].Value;
row["KOD"] = rowAdd.Cells["KOD"].Value;
row["NAME"] = rowAdd.Cells["NAME"].Value;
table.Rows.Add(row);
row.AcceptChanges();
dataGridCompChosenDiscip.Refresh();
// теперь сохраним в базу новую связь дисциплина-компетенция
SQLiteManager.InsertValue("Competence_Discipline", new string[] { "ID_competence", "ID_discipline" },
new object[] { rowAdd.Cells["ID"].Value, dataGridCompChosenDiscip.Tag });
}
}
}
private void LoadTree()
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
string sqlSelect = "SELECT * FROM SPECIALTY";
SQLiteCommand command = new SQLiteCommand(sqlSelect);
command.Connection = connection;
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TreeNode node = new TreeNode(reader["Name"].ToString());
node.Tag = reader["KOD"];
treeView1.Nodes.Add(node);
}
reader.Close();
if (treeView1.Nodes.Count == 0)
{
connection.Close();
return;
}
// теперь загрузим все специализации
command.CommandText = "SELECT * FROM SPECIALIZATION WHERE specialty_fk = @id_spec ORDER BY Number";
command.Parameters.Add(new SQLiteParameter("@id_spec"));
foreach (TreeNode node in treeView1.Nodes)
{
command.Parameters[0].Value = node.Tag;
reader = command.ExecuteReader();
while (reader.Read())
{
TreeNode node_child = new TreeNode(reader["Number"].ToString() + " " + reader["Name"].ToString());
node_child.Tag = reader["ID"];
node.Nodes.Add(node_child);
}
reader.Close();
}
}
private void buttonSave_Click(object sender, EventArgs e)
{
SQLiteConnection connection = SQLiteManager.CreateConnection();
SQLiteCommand command = new SQLiteCommand("UPDATE Assessment_Competence SET Assessment = @ass WHERE ID_student = @id_stud AND ID_competence = @id_comp", connection);
command.Parameters.Add(new SQLiteParameter("@ass", DbType.Double));
command.Parameters.Add(new SQLiteParameter("@id_stud", DbType.Int64));
command.Parameters.Add(new SQLiteParameter("@id_comp", DbType.Int64));
for (int i = 2; i < dataGridViewAssessment.Columns.Count; i++)
{
command.Parameters[2].Value = competenceID[i - 2];
for (int j = 0; j < dataGridViewAssessment.Rows.Count; j++)
{
if (Convert.ToBoolean(dataGridViewAssessment[i, j].Tag))
{ // стоит флаг изменений, значит сохраняем в базу и снимает флаг
dataGridViewAssessment[i, j].Tag = false;
command.Parameters[0].Value = dataGridViewAssessment[i, j].Value;
command.Parameters[1].Value = Convert.ToInt64(dataGridViewAssessment.Rows[j].Tag);
command.ExecuteNonQuery();
}
}
}
MessageBox.Show("Данные успешно сохранены.", "Уведомление");
}
private bool SaveSpec()
{
if (SQLiteManager.IsExistValue("SPECIALTY", "KOD", textBox1.Text))
{
return(false);
}
int rowid = SQLiteManager.InsertValue("SPECIALTY", new string[] { "KOD", "Name" }, new string[] { textBox1.Text, textBox2.Text });
if (rowid == -1)
{
return(false);
}
DataGridView d = (DataGridView)parentForm.Controls["dataGridViewSpecialty"];
BindingSource bind = (BindingSource)d.DataSource;
DataTable table = (DataTable)bind.DataSource;
DataRow row = table.NewRow();
row[0] = textBox1.Text;
row[1] = textBox2.Text;
table.Rows.Add(row);
row.AcceptChanges();
d.Refresh();
return(true);
}