private void button2_Click(object sender, EventArgs e)
{
if (dataGridView1.CurrentRow != null)
{
int index = dataGridView1.CurrentRow.Index;
bool created = MakeApproval(dataGridView1[0, index].Value.ToString());
if (created)
{
string query = @"update Обучение
set coursePassed = 1
where id = {0};" ;
DbWorker dbWorker = new DbWorker();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = string.Format(query, dataGridView1[0, index].Value.ToString());
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
dataGridView1.Refresh();
this.courseNotPassedViewTableAdapter.Fill(this.coursesDataSet.CourseNotPassedView);
MessageBox.Show("Курс одобрен");
}
}
else
{
MessageBox.Show("Курс не выбран");
}
}
private void AvtorizationChangeEvent(object sender, DataGridViewCellEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" update Авторизация
set login=N'{0}', password = N'{1}'
where id = {2};";
try
{
if (e.RowIndex >= 0)
{
/* queryComand = string.Format(queryComand,
* dataGridView1[1, e.RowIndex].Value.ToString(),
* dataGridView1[2, e.RowIndex].Value.ToString(),
* dataGridView1[0, e.RowIndex].Value.ToString());*/
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception ex)
{
}
}
private void button2_Click(object sender, EventArgs e)
{
DbWorker dbWorker = new DbWorker();
int role = 0;
if (radioButton1.Checked)
{
role = (int)DbWorker.Roles.Metodist;
}
if (radioButton2.Checked)
{
role = (int)DbWorker.Roles.Kadrovik;
}
dbWorker.RegisterUser(textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, textBox5.Text, dateTimePicker1.Value, role);
textBox1.Clear();
textBox2.Clear();
textBox3.Clear();
textBox4.Clear();
textBox5.Clear();
MessageBox.Show("Запись добавлена");
}
private void UserChangeEvent(object sender, DataGridViewCellEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" update Пользователи
set firstName= N'{0}',
midleName = N'{1}',
lastName = N'{2}',
dateOfBirth= '{3}',
authId = {4},
roleId= {5}
where id = {6};";
if (e.RowIndex >= 0)
{
queryComand = string.Format(queryComand,
dataGridView2[1, e.RowIndex].Value.ToString(),
dataGridView2[2, e.RowIndex].Value.ToString(),
dataGridView2[3, e.RowIndex].Value.ToString(),
dataGridView2[4, e.RowIndex].Value.ToString(),
dataGridView2[5, e.RowIndex].Value.ToString(),
dataGridView2[6, e.RowIndex].Value.ToString(),
dataGridView2[0, e.RowIndex].Value.ToString());
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
private void RemoveUserEvent(object sender, DataGridViewRowsRemovedEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" delete from Пользователи
where id = {0};";
try
{
if (e.RowIndex >= 0)
{
/* queryComand = string.Format(queryComand,
* dataGridView2[0, e.RowIndex + 1].Value.ToString());*/
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception ex)
{
}
}
public Dictionary <int, List <object> > GetDBData(string query, int columnsCount)
{
DbWorker dbWorker = new DbWorker();
Dictionary <int, List <object> > data = new Dictionary <int, List <object> >();
using (SqlConnection connection = GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = query;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
int counter = 0;
while (reader.Read())
{
List <object> list = new List <object>();
for (int i = 0; i < columnsCount; i++)
{
list.Add(reader.GetValue(i));
}
data.Add(counter, list);
counter++;
}
connection.Close();
}
return(data);
}
private void button2_Click_1(object sender, EventArgs e)
{
if (dataGridView1.CurrentRow != null)
{
int index = dataGridView1.CurrentRow.Index;
string query = @"insert Обучение(teacherId,coursId,coursePassed)
values({0},{1},0);" ;
DbWorker dbWorker = new DbWorker();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = string.Format(query, _user.UserId, dataGridView1[0, index].Value.ToString());
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
dataGridView1.Rows.Clear();
FillDataGrid();
MessageBox.Show("Курс выбран");
}
else
{
MessageBox.Show("Курс не выбран");
}
}
private void button3_Click(object sender, EventArgs e)
{
int role = 0;
if (radioButton3.Checked)
{
role = (int)DbWorker.Roles.Metodist;
}
if (radioButton4.Checked)
{
role = (int)DbWorker.Roles.Kadrovik;
}
if (radioButton5.Checked)
{
role = (int)DbWorker.Roles.Teacher;
}
if (radioButton1.Checked)
{
role = (int)DbWorker.Roles.Admin;
}
if (string.IsNullOrEmpty(textBox6.Text) ||
string.IsNullOrEmpty(textBox7.Text) ||
string.IsNullOrEmpty(textBox8.Text) ||
string.IsNullOrEmpty(textBox9.Text) ||
string.IsNullOrEmpty(textBox10.Text) ||
dateTimePicker2.Value == new DateTime() ||
role == 0)
{
MessageBox.Show("Введите все обязательные поля");
return;
}
if (Helpers.GetDialogResult("Вы действительно хотите добавить запись?", "Добавление записи"))
{
DbWorker dbWorker = new DbWorker();
dbWorker.RegisterUser(textBox6.Text, textBox7.Text, textBox8.Text, textBox9.Text, textBox10.Text, dateTimePicker2.Value, role);
textBox6.Clear();
textBox7.Clear();
textBox8.Clear();
textBox9.Clear();
textBox10.Clear();
this.userDataTableAdapter.Fill(this.coursesDataSet.UserData1);
dataGridView1.Refresh();
MessageBox.Show("Запись добавлена");
}
else
{
MessageBox.Show("Запись не добавлена");
}
}
private void button2_Click(object sender, EventArgs e)
{
DbWorker dbWorker = new DbWorker();
object value = educationFormList.SelectedItem;
int coursTypeId = Convert.ToInt32(coursTypeList.SelectedValue.ToString());
int coursFormId = Convert.ToInt32(educationFormList.SelectedValue.ToString());
dbWorker.AddCours(textBox1.Text, coursTypeId, Convert.ToInt32(textBox2.Text), coursFormId);
this.курсыTableAdapter.Fill(this.silverHa.Курсы);
}
private void FillDataGrid()
{
string query = @"select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'CoursePassedView'; " ;
string coursesQuery = @"
select *
from CoursePassedView " ;
DbWorker dbWorker = new DbWorker();
List <string> columns = new List <string>();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = query;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
columns.Add(reader.GetString(0));
}
connection.Close();
}
dataGridView1.ColumnCount = columns.Count;
dataGridView1.ColumnHeadersVisible = true;
for (int i = 0; i < columns.Count; i++)
{
dataGridView1.Columns[i].Name = columns[i];
}
dataGridView1.Rows.Clear();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = coursesQuery;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetDateTime(5), reader.GetDateTime(6), reader.GetInt32(7));
}
connection.Close();
}
}
private void button4_Click(object sender, EventArgs e)
{
bool result = Helpers.GetDialogResult("Удаление записи", "Вы действительно хотите удалить запись?");
if (result)
{
DbWorker dbWorker = new DbWorker();
int index = dataGridView1.CurrentRow.Index;
dbWorker.ExecQuery(string.Format("Delete from Курсы where id={0}", dataGridView1[0, index].Value.ToString()));
//dataGridView1.Refresh();
this.coursesViewTableAdapter.Fill(this.coursesDataSet1.CoursesView1);
textBox1.Clear();
textBox2.Clear();
}
}
public void Maketemplate(string outPath)
{
DbWorker dbWorker = new DbWorker();
string appPath = Directory.GetCurrentDirectory();
if (File.Exists(appPath + outPath))
{
File.Delete(Path.GetFullPath(appPath + outPath));
}
File.Copy(Path.GetFullPath(appPath + "\\Template\\шаблон.docx"), appPath + outPath);
Dictionary <string, string> req = dbWorker.GetSertificateData();
if (req != null)
{
var valuesToFill = new Content(
new FieldContent("CoursTipe", req["CoursType"]),
new FieldContent("LastName", req["LastName"]),
new FieldContent("FirstName", req["FirstName"]),
new FieldContent("MiddleName", req["MiddleName"]),
new FieldContent("DateFrom", req["StartDate"]),
new FieldContent("DateEnd", req["EndDate"]),
new FieldContent("EducationType", req["CoursType"]),
new FieldContent("CoursName", req["CoursName"]),
new FieldContent("Volume", req["Volume"]),
new FieldContent("DateNow", DateTime.Now.ToString()),
new FieldContent("RegisterNumber", req["RegisterNumber"])
);
using (var outputDocument = new TemplateProcessor(appPath + outPath)
.SetRemoveContentControls(true))
{
outputDocument.FillContent(valuesToFill);
outputDocument.SaveChanges();
}
}
else
{
MessageBox.Show("Данных по обучающимся не найдено!");
}
}
private void AutDataDeleted(object sender, DataGridViewRowsRemovedEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" delete from Авторизация
where id = {0};";
if (e.RowIndex >= 0)
{
/*queryComand = string.Format(queryComand,
* dataGridView1[0, e.RowIndex + 1].Value.ToString());*/
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
private void RowClickEvent(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex <= dataGridView2.RowCount && e.RowIndex >= 0)
{
firstName.Text = dataGridView2[1, e.RowIndex].Value.ToString();
midleName.Text = dataGridView2[2, e.RowIndex].Value.ToString();
lastName.Text = dataGridView2[3, e.RowIndex].Value.ToString();
dateOfBirth.Text = dataGridView2[4, e.RowIndex].Value.ToString();
login.Text = dataGridView2[5, e.RowIndex].Value.ToString();
password.Text = dataGridView2[6, e.RowIndex].Value.ToString();
//role.SelectedItem = dataGridView2[7, e.RowIndex].Value.ToString();
role.SelectedIndex = role.FindString(dataGridView2[7, e.RowIndex].Value.ToString());
}
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader = null;
List <string> roles = new List <string>();
}
public bool ExecQuery(string queryComand)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
try
{
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
private void button3_Click(object sender, EventArgs e)
{
Templater templater = new Templater();
try
{
int index = dataGridView1.CurrentRow.Index;
if (Convert.ToDateTime(dataGridView1[6, index].Value.ToString()) < DateTime.Now)
{
DbWorker dbWorker = new DbWorker();
string id = dataGridView1[0, index].Value.ToString();
string educationId = dataGridView1[0, index].Value.ToString();
string selectedPath = null;
OpenFileDialog folderBrowser = new OpenFileDialog();
folderBrowser.ValidateNames = false;
folderBrowser.CheckFileExists = false;
folderBrowser.CheckPathExists = true;
folderBrowser.FileName = $"Справка_№-{educationId}.docx";
if (folderBrowser.ShowDialog() == DialogResult.OK)
{
selectedPath = folderBrowser.FileName;
}
templater.Maketemplate(selectedPath, educationId, "shablon.docx"); //"\\Documents\\шаблон.docx"
MessageBox.Show("Справка сформирована!");
}
else
{
MessageBox.Show("Не возможно сформировать справку для не оконченных курсов!");
}
}
catch (Exception ex)
{
MessageBox.Show("Справка не сформирована");
}
dataGridView1.Rows.Clear();
FillDataGridWithFilter();
}
public bool MakeTemplate(string courseIndex, string teacherIndex)
{
Templater templater = new Templater();
try
{
DbWorker dbWorker = new DbWorker();
var education = dbWorker.GetEducation(teacherIndex, courseIndex);
string selectedPath = null;
if (education != null)
{
OpenFileDialog folderBrowser = new OpenFileDialog();
// Set validate names and check file exists to false otherwise windows will
// not let you select "Folder Selection."
folderBrowser.ValidateNames = false;
folderBrowser.CheckFileExists = false;
folderBrowser.CheckPathExists = true;
// Always default to Folder Selection.
folderBrowser.FileName = $"Направление_№-{education.Id}.docx";
if (folderBrowser.ShowDialog() == DialogResult.OK)
{
selectedPath = folderBrowser.FileName;
}
templater.CourseReferral(selectedPath, education.Id, "Napravlenie_na_prokhozhdenie_kursa.docx"); //"\\Documents\\Направление на прохождение курса.docx"
MessageBox.Show("Справка сформирована!");
return(true);
}
else
{
MessageBox.Show("Данные не найдены");
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show("Справка не сформирована");
return(false);
}
}
public bool MakeApproval(string educationId)
{
Templater templater = new Templater();
try
{
DbWorker dbWorker = new DbWorker();
string selectedPath = null;
if (!string.IsNullOrEmpty(educationId) || !string.IsNullOrWhiteSpace(educationId))
{
OpenFileDialog folderBrowser = new OpenFileDialog();
// Set validate names and check file exists to false otherwise windows will
// not let you select "Folder Selection."
folderBrowser.ValidateNames = false;
folderBrowser.CheckFileExists = false;
folderBrowser.CheckPathExists = true;
// Always default to Folder Selection.
folderBrowser.FileName = $"Одобрение_курса_№-{educationId}.docx";
if (folderBrowser.ShowDialog() == DialogResult.OK)
{
selectedPath = folderBrowser.FileName;
}
templater.CourseReferral(selectedPath, educationId, "odobrenie_kursa.docx"); //"\\Documents\\Направление на прохождение курса.docx"
MessageBox.Show("Справка сформирована!");
return(true);
}
else
{
MessageBox.Show("Данные не найдены");
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show("Справка не сформирована");
return(false);
}
}
private void RowSelectedEvent(object sender, EventArgs e)
{
DataGridViewCellEventArgs arg = (DataGridViewCellEventArgs)e;
firstName.Text = dataGridView2[1, arg.RowIndex].Value.ToString();
midleName.Text = dataGridView2[2, arg.RowIndex].Value.ToString();
lastName.Text = dataGridView2[3, arg.RowIndex].Value.ToString();
dateOfBirth.Text = dataGridView2[4, arg.RowIndex].Value.ToString();
login.Text = dataGridView2[5, arg.RowIndex].Value.ToString();
password.Text = dataGridView2[5, arg.RowIndex].Value.ToString();
role.SelectedItem = dataGridView2[6, arg.RowIndex].Value.ToString();
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader = null;
List <string> roles = new List <string>();
try
{
command.CommandText = "select roleName from Роли;";
connection.Open();
command.Connection = connection;
reader = command.ExecuteReaderAsync().GetAwaiter().GetResult();
while (reader.HasRows)
{
reader.Read();
roles.Add(reader.GetString(1));
}
connection.Close();
role.Items.AddRange(roles.ToArray());
}
catch (Exception ex)
{
}
}
/// <summary>
/// Направление на прохождение курсов
/// </summary>
/// <param name="outPath"></param>
/// <param name="id"></param>
/// <param name="educationId"></param>
/// <param name="templateName"></param>
public void CourseReferral(string outPath, string educationId, string templateName)
{
DbWorker dbWorker = new DbWorker();
string appPath = Directory.GetCurrentDirectory();
File.Copy(Path.GetFullPath(appPath + "\\Template\\" + templateName), outPath);
Dictionary <string, object> req = dbWorker.GetSertificateData(educationId);
if (req != null)
{
Content valuesToFill = new Content(
new FieldContent("CoursTipe", req["CoursType"].ToString()),
new FieldContent("LastName", req["LastName"].ToString()),
new FieldContent("FirstName", req["FirstName"].ToString()),
new FieldContent("MiddleName", req["MiddleName"].ToString()),
new FieldContent("DateOfBirth", ((DateTime)req["DateOfBirth"]).ToString("dd.MM.yyyy")),
new FieldContent("DateFrom", req["StartDate"].ToString()),
new FieldContent("DateEnd", req["EndDate"].ToString()),
new FieldContent("EducationType", req["CoursType"].ToString()),
new FieldContent("CoursName", req["CoursName"].ToString()),
new FieldContent("Volume", req["Volume"].ToString()),
new FieldContent("DateNow", DateTime.Now.ToString("dd.MM.yyyy"))
//,new FieldContent("RegisterNumber", req["RegisterNumber"].ToString())
);
using (TemplateProcessor outputDocument = new TemplateProcessor(outPath)
.SetRemoveContentControls(true))
{
outputDocument.FillContent(valuesToFill);
outputDocument.SaveChanges();
}
}
else
{
MessageBox.Show("Данных по обучающимся не найдено!");
}
}
private void AuthDataRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
{
DbWorker dbWorker = new DbWorker();
SqlConnection connection = dbWorker.GetConnection();
SqlCommand command = new SqlCommand();
string queryComand = @" delete from Авторизация
where id = {0};";
try
{
if (e.RowIndex >= 0)
{
command.CommandText = queryComand;
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception ex)
{
}
}
private void FillDataGrid()
{
string query = @"select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TeacherCoursesView'; " ;
string coursesQuery = @"
select distinct k.id,k.coursName, k.courseVolume, f.educationType,v.coursName as 'courseTypeName' --,o.teacherId
from Курсы k
left join Обучение o
on o.coursId = k.id
left join ФормаОбучения f
on k.educationFormId = f.id
left join ВидКурса v
on k.coursTypeId = v.id
where (o.teacherId != {0}
or o.teacherId is null)
and k.id in (
select id from Курсы
except
select id from Курсы
where id in
(
select coursId
from Обучение
where teacherId = {0}
)
) and k.endDate > '{1}'" ;
DbWorker dbWorker = new DbWorker();
List <string> columns = new List <string>()
{
"Код",
"Название курса",
"Объем курса",
"Форма обучения",
"Вид курса"
};
/*using (SqlConnection connection = dbWorker.GetConnection())
* using (SqlCommand command = new SqlCommand())
* {
* command.Connection = connection;
* command.CommandText = query;
* connection.Open();
* SqlDataReader reader = command.ExecuteReader();
* while (reader.Read())
* {
* columns.Add(reader.GetString(0));
* }
* connection.Close();
* }*/
dataGridView1.ColumnCount = columns.Count;
dataGridView1.ColumnHeadersVisible = true;
for (int i = 0; i < columns.Count; i++)
{
dataGridView1.Columns[i].Name = columns[i];
}
dataGridView1.Rows.Clear();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = string.Format(coursesQuery, _user.UserId, DateTime.Now);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//dataGridView1.Rows.Add(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader["teacherId"] );
dataGridView1.Rows.Add(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4));
}
connection.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
DbWorker dbWorker = new DbWorker();
object value = educationFormList.SelectedItem;
int coursTypeId = 0;
int coursFormId = 0;
int courseVolume = 0;
var coursTypes = dbWorker.GetCoursTypes().ToList();
if (
!string.IsNullOrWhiteSpace(coursTypeList.SelectedValue.ToString()) &&
!string.IsNullOrWhiteSpace(educationFormList.SelectedValue.ToString()) &&
!string.IsNullOrWhiteSpace(textBox2.Text) &&
!string.IsNullOrEmpty(coursTypeList.SelectedValue.ToString()) &&
!string.IsNullOrEmpty(educationFormList.SelectedValue.ToString()) &&
!string.IsNullOrEmpty(textBox2.Text)
)
{
switch ((int)coursTypeList.SelectedValue)
{
case (int)Повышение_квалификации.Data.CoursTypes.QualificationUpgrade:
if (Convert.ToInt32(textBox2.Text) >= 72)
{
courseVolume = Convert.ToInt32(textBox2.Text);
coursTypeId = Convert.ToInt32(coursTypeList.SelectedValue.ToString());
}
else
{
MessageBox.Show("Курс повышения квалификации не может длиться менее 72 часов");
return;
}
break;
case (int)Повышение_квалификации.Data.CoursTypes.RefresherCourse:
if (Convert.ToInt32(textBox2.Text) >= 250)
{
courseVolume = Convert.ToInt32(textBox2.Text);
coursTypeId = Convert.ToInt32(coursTypeList.SelectedValue.ToString());
}
else
{
MessageBox.Show("Курс переподготовки не может длиться менее 250 часов");
return;
}
break;
case (int)Повышение_квалификации.Data.CoursTypes.InternshipCourse:
if (Convert.ToInt32(textBox2.Text) > 0)
{
courseVolume = Convert.ToInt32(textBox2.Text);
coursTypeId = Convert.ToInt32(coursTypeList.SelectedValue.ToString());
}
else
{
MessageBox.Show("Время проведения стажировк быть больше 0 часов");
return;
}
break;
default:
break;
}
coursFormId = Convert.ToInt32(educationFormList.SelectedValue.ToString());
//courseVolume = Convert.ToInt32(textBox2.Text);
//coursTypeList.SelectedValue.
//!string.IsNullOrWhiteSpace(educationFormList.SelectedValue.ToString()) &&
textBox2.Clear();
textBox2.Clear();
}
else
{
MessageBox.Show("Заполните все поля");
return;
}
dbWorker.AddCours(textBox1.Text, coursTypeId, courseVolume, coursFormId, dateTimePicker1.Value, dateTimePicker2.Value);
this.coursesViewTableAdapter.Fill(this.coursesDataSet1.CoursesView1);
textBox1.Clear();
textBox2.Clear();
}
private void button3_Click(object sender, EventArgs e)
{
DbWorker dbWorker = new DbWorker();
if (!string.IsNullOrEmpty(login.Text) &&
!string.IsNullOrWhiteSpace(login.Text) &&
!string.IsNullOrEmpty(password.Text) &&
!string.IsNullOrWhiteSpace(password.Text) &&
!string.IsNullOrEmpty(firstName.Text) &&
!string.IsNullOrWhiteSpace(firstName.Text) &&
!string.IsNullOrEmpty(lastName.Text) &&
!string.IsNullOrWhiteSpace(lastName.Text) &&
!string.IsNullOrEmpty(midleName.Text) &&
!string.IsNullOrWhiteSpace(midleName.Text)
)
{
if (Helpers.GetDialogResult("Изменить запись?", "Изменение записи"))
{
int index = dataGridView2.CurrentRow.Index;
string userQuery = @"update Пользователи
set firstName = N'{0}',
midleName = N'{1}',
lastName = N'{2}',
dateOfBirth = '{3}',
roleId = {4}
where id = '{5}';" ;
string authQuery = @"
begin
declare @authId int;
set @authId = (select authId from Пользователи where id = {0});
update Авторизация
set login = N'{1}',
password=N'{2}'
where id=@authId;
end;
" ;
string resultQuery = string.Format(userQuery,
firstName.Text,
midleName.Text,
lastName.Text,
dateOfBirth.Value,
role.SelectedIndex + 1,
dataGridView2[0, index].Value.ToString());
resultQuery = resultQuery + " " + string.Format(authQuery, dataGridView2[0, index].Value.ToString(), login.Text, password.Text);
if (!dbWorker.IsLoginExists(login.Text))
{
ExecQuery(resultQuery);
this.userDataTableAdapter.Fill(this.coursesDataSet.UserData1);
firstName.Clear();
midleName.Clear();
lastName.Clear();
login.Clear();
password.Clear();
MessageBox.Show("Запись изменена");
}
else
{
MessageBox.Show("Пользователь с таким логином уже существует!");
}
}
}
else
{
MessageBox.Show("Заполните все поля");
}
}
private void FillDataGridWithFilter()
{
dataGridView1.Rows.Clear();
string query = @"select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'CoursePassedView'; " ;
string coursesQuery = @"select * from CoursePassedView ";
int filterParamCount = 0;
if (!string.IsNullOrEmpty(firstName.Text) &&
!string.IsNullOrWhiteSpace(firstName.Text) ||
!string.IsNullOrEmpty(midleName.Text) &&
!string.IsNullOrWhiteSpace(midleName.Text) ||
!string.IsNullOrEmpty(lastName.Text) &&
!string.IsNullOrWhiteSpace(lastName.Text) ||
!string.IsNullOrEmpty(courseVolume.Text) &&
!string.IsNullOrWhiteSpace(courseVolume.Text) ||
!string.IsNullOrEmpty(courseType.Text) &&
!string.IsNullOrWhiteSpace(courseType.Text) ||
!string.IsNullOrEmpty(courseName.Text) &&
!string.IsNullOrWhiteSpace(courseName.Text)
)
{
coursesQuery += " where ";
filterParamCount++;
}
if (!string.IsNullOrEmpty(firstName.Text) &&
!string.IsNullOrWhiteSpace(firstName.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("firstName = N'{0}'", firstName.Text);
}
if (!string.IsNullOrEmpty(midleName.Text) &&
!string.IsNullOrWhiteSpace(midleName.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("midleName = N'{0}'", midleName.Text);
}
if (!string.IsNullOrEmpty(lastName.Text) &&
!string.IsNullOrWhiteSpace(lastName.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("lastName = N'{0}'", lastName.Text);
}
if (!string.IsNullOrEmpty(courseVolume.Text) &&
!string.IsNullOrWhiteSpace(courseVolume.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("courseVolume = '{0}'", courseVolume.Text);
}
if (!string.IsNullOrEmpty(courseName.Text) &&
!string.IsNullOrWhiteSpace(courseName.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("coursName = N'{0}'", courseName.Text);
}
if (!string.IsNullOrEmpty(courseType.Text) &&
!string.IsNullOrWhiteSpace(courseType.Text))
{
if (filterParamCount > 1)
{
coursesQuery += " and ";
}
filterParamCount++;
coursesQuery += string.Format("coursTypeName = N'{0}'", courseType.Text);
}
DbWorker dbWorker = new DbWorker();
List <string> columns = new List <string>()
{
"Код",
"Имя",
"Отчество",
"Фамилия",
"Название курса",
"Дата начала курса",
"Дата окончания курса",
"Объем курса",
"Вид курса",
"Форма обучения"
};
dataGridView1.ColumnCount = columns.Count;
dataGridView1.ColumnHeadersVisible = true;
for (int i = 0; i < columns.Count; i++)
{
dataGridView1.Columns[i].Name = columns[i];
}
dataGridView1.Rows.Clear();
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = coursesQuery;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetInt32(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6), reader.GetValue(7), reader.GetValue(8), reader.GetValue(9));
}
connection.Close();
}
}
private void button3_Click(object sender, EventArgs e)
{
bool result = Helpers.GetDialogResult("Изменение записи", "Вы действительно хотите изменить запись?");
if (result)
{
DbWorker dbWorker = new DbWorker();
object value = educationFormList.SelectedItem;
int coursTypeId = 0;
int coursFormId = 0;
int courseVolume = 0;
if (
!string.IsNullOrWhiteSpace(coursTypeList.SelectedValue?.ToString()) &&
!string.IsNullOrWhiteSpace(educationFormList.SelectedValue?.ToString()) &&
!string.IsNullOrWhiteSpace(textBox2?.Text) &&
!string.IsNullOrEmpty(coursTypeList.SelectedValue?.ToString()) &&
!string.IsNullOrEmpty(educationFormList.SelectedValue?.ToString()) &&
!string.IsNullOrEmpty(textBox2.Text)
)
{
switch ((int)coursTypeList.SelectedValue)
{
case (int)Повышение_квалификации.Data.CoursTypes.QualificationUpgrade:
if (Convert.ToInt32(textBox2.Text) >= 72)
{
courseVolume = Convert.ToInt32(textBox2.Text);
coursTypeId = Convert.ToInt32(coursTypeList.SelectedValue.ToString());
}
else
{
MessageBox.Show("Курс повышения квалификации не может длиться менее 72 часов");
return;
}
break;
case (int)Повышение_квалификации.Data.CoursTypes.RefresherCourse:
if (Convert.ToInt32(textBox2.Text) >= 250)
{
courseVolume = Convert.ToInt32(textBox2.Text);
coursTypeId = Convert.ToInt32(coursTypeList.SelectedValue.ToString());
}
else
{
MessageBox.Show("Курс переподготовки не может длиться менее 250 часов");
return;
}
break;
case (int)Повышение_квалификации.Data.CoursTypes.InternshipCourse:
if (Convert.ToInt32(textBox2.Text) > 0)
{
courseVolume = Convert.ToInt32(textBox2.Text);
coursTypeId = Convert.ToInt32(coursTypeList.SelectedValue.ToString());
}
else
{
MessageBox.Show("Время проведения стажировк быть больше 0 часов");
return;
}
break;
default:
break;
}
//coursTypeId = Convert.ToInt32(coursTypeList.SelectedValue.ToString());
coursFormId = Convert.ToInt32(educationFormList.SelectedValue.ToString());
//courseVolume = Convert.ToInt32(textBox2.Text);
}
else
{
MessageBox.Show("Не корректно заполнены поля");
return;
}
dbWorker.UpdateCourses(dataGridView1, textBox1.Text, coursTypeId, courseVolume, coursFormId, dateTimePicker1.Value, dateTimePicker2.Value);
this.coursesViewTableAdapter.Fill(this.coursesDataSet1.CoursesView1);
textBox1.Clear();
textBox2.Clear();
}
}
private void button3_Click(object sender, EventArgs e)
{
DbWorker dbWorker = new DbWorker();
int teacherIndex = dataGridView2.CurrentRow.Index;
int courseIndex = dataGridView3.CurrentRow.Index;
string query = @"Insert_Education";
/* @"insert Обучение(teacherId,coursId,coursePassed)
* values({0},{1},1);";*/
if (teacherIndex < 0)
{
MessageBox.Show("Не выбран преподаватель");
return;
}
if (courseIndex < 0)
{
MessageBox.Show("Не выбран курс");
return;
}
string id = null;
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = query;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@teacherId", dataGridView2[0, teacherIndex].Value.ToString());
command.Parameters.AddWithValue("@coursId", dataGridView3[0, courseIndex].Value.ToString());
command.Parameters.AddWithValue("@coursPassed", 1);
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
id = reader.GetValue(0).ToString();
}
connection.Close();
}
bool created = MakeTemplate(dataGridView3[0, courseIndex].Value.ToString(), dataGridView2[0, teacherIndex].Value.ToString());
if (!created)
{
using (SqlConnection connection = dbWorker.GetConnection())
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = $"delete from Обучение where id = {id}";
connection.Open();
command.ExecuteNonQuery();
}
}
}