289 lines
12 KiB
C#
289 lines
12 KiB
C#
|
using Microsoft.Win32;
|
|||
|
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Collections.ObjectModel;
|
|||
|
|
using System.Data.Entity;
|
|||
|
|
using System.Data.Entity.Infrastructure;
|
|||
|
|
using System.Data.Entity.Migrations;
|
|||
|
|
using System.Data.SqlClient;
|
|||
|
|
using System.Data.SqlTypes;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Runtime.Remoting.Contexts;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Windows;
|
|||
|
|
using System.Windows.Controls;
|
|||
|
|
using System.Windows.Data;
|
|||
|
|
using System.Windows.Documents;
|
|||
|
|
using System.Windows.Input;
|
|||
|
|
using System.Windows.Media;
|
|||
|
|
using System.Windows.Media.Imaging;
|
|||
|
|
using System.Windows.Navigation;
|
|||
|
|
using System.Windows.Shapes;
|
|||
|
|
using System.IO;
|
|||
|
|
using System.Data.Entity.Validation;
|
|||
|
|
using System.Data.OleDb;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Reflection;
|
|||
|
|
|
|||
|
|
namespace musicschoolapp.Pages
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// Логика взаимодействия для StudentEditingPage.xaml
|
|||
|
|
/// </summary>
|
|||
|
|
public partial class EnrollmentEditingPage : Page
|
|||
|
|
{
|
|||
|
|
musicschoolEntities1 mse = new musicschoolEntities1();
|
|||
|
|
DbSet<Enrollment> dbContext_;
|
|||
|
|
bool isadmin_;
|
|||
|
|
List<Enrollment> students1;
|
|||
|
|
bool adding = false;
|
|||
|
|
public EnrollmentEditingPage(bool isadmin)
|
|||
|
|
{
|
|||
|
|
InitializeComponent();
|
|||
|
|
this.isadmin_ = isadmin;
|
|||
|
|
if(isadmin)
|
|||
|
|
{
|
|||
|
|
dGridStudent.IsReadOnly = false;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
isadmin_ = false;
|
|||
|
|
dGridStudent.IsReadOnly = true;
|
|||
|
|
}
|
|||
|
|
combobox_courses.ItemsSource = mse.Course.ToList();
|
|||
|
|
students1 = mse.Enrollment.ToList();
|
|||
|
|
dGridStudent.ItemsSource = students1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public int updateDB()
|
|||
|
|
{
|
|||
|
|
combobox_courses.SelectedIndex = -1;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
foreach (var student in students1)
|
|||
|
|
{
|
|||
|
|
var original = mse.Enrollment.Find(student.EnrollmentID);
|
|||
|
|
|
|||
|
|
if (original != null)
|
|||
|
|
{
|
|||
|
|
mse.Entry(original).CurrentValues.SetValues(student);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
mse.Enrollment.Add(student);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
int saveresult = mse.SaveChanges();
|
|||
|
|
students1 = mse.Enrollment.ToList();
|
|||
|
|
dGridStudent.ItemsSource = students1;
|
|||
|
|
return saveresult;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show(ex.InnerException.ToString());
|
|||
|
|
return -1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
private void dGridStudent_MouseDoubleClick(object sender, MouseButtonEventArgs e)
|
|||
|
|
{
|
|||
|
|
var grid = (DataGrid)sender;
|
|||
|
|
var cellInfo = grid.SelectedCells[grid.CurrentCell.Column.DisplayIndex];
|
|||
|
|
|
|||
|
|
if (cellInfo.Column.Header.ToString() == "Фото")
|
|||
|
|
{
|
|||
|
|
if (MessageBox.Show("Вы дважды кликнули по столбцу 'Фото'. Изменить?", "Вопрос", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
|
|||
|
|
{
|
|||
|
|
OpenFileDialog openFileDialog = new OpenFileDialog();
|
|||
|
|
openFileDialog.ShowDialog();
|
|||
|
|
if (mse.Student.Find((grid.SelectedItem as Enrollment).EnrollmentID) == null)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show("Объект не создан.");
|
|||
|
|
grid.SelectedItem = -1;
|
|||
|
|
mse.Enrollment.Add(dGridStudent.SelectedItem as Enrollment);
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
mse.SaveChanges();
|
|||
|
|
}
|
|||
|
|
catch (DbEntityValidationException ex)
|
|||
|
|
{
|
|||
|
|
foreach (var entityValidationErrors in ex.EntityValidationErrors)
|
|||
|
|
{
|
|||
|
|
foreach (var validationError in entityValidationErrors.ValidationErrors)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show("Свойство: " + validationError.PropertyName + " Ошибка: " + validationError.ErrorMessage);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
MessageBox.Show("добавил");
|
|||
|
|
int index = grid.SelectedIndex;
|
|||
|
|
grid.SelectedIndex = -1;
|
|||
|
|
grid.SelectedIndex = index;
|
|||
|
|
dGridStudent_SelectionChanged(sender, new SelectionChangedEventArgs(DataGrid.SelectionChangedEvent, new Collection<object> { }, new Collection<object> { dGridStudent.SelectedItem }));
|
|||
|
|
}
|
|||
|
|
mse.SaveChanges();
|
|||
|
|
dGridStudent.ItemsSource = mse.Enrollment.ToList();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
private void dGridStudent_SelectionChanged(object sender, SelectionChangedEventArgs e)
|
|||
|
|
{
|
|||
|
|
if (e.AddedItems != null && e.AddedItems.Count > 0)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
else if (e.RemovedItems != null && e.RemovedItems.Count > 0)
|
|||
|
|
{
|
|||
|
|
Enrollment selectedStudent = (e.RemovedItems[0] as Enrollment);
|
|||
|
|
if (selectedStudent != null)
|
|||
|
|
{
|
|||
|
|
Enrollment studentInDb = mse.Enrollment.Find(selectedStudent.EnrollmentID);
|
|||
|
|
if (studentInDb != null && (e.RemovedItems[0] as Enrollment).Student != null && (e.RemovedItems[0] as Enrollment).Course != null && isadmin_)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
if (MessageBox.Show("Удалить " + (e.RemovedItems[0] as Enrollment).Student.LastName + " (" + (e.RemovedItems[0] as Enrollment).Course.Name + ")", "Внимание",MessageBoxButton.YesNo) == MessageBoxResult.Yes)
|
|||
|
|
{
|
|||
|
|
mse.Enrollment.Remove(studentInDb);
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
mse.SaveChanges();
|
|||
|
|
}
|
|||
|
|
catch (DbUpdateException ex)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
MessageBox.Show("Запись, похоже, удалить нельзя.\n\n" + ex.InnerException);
|
|||
|
|
MessageBox.Show("запись была удалена только из таблицы, но сохранена в базе данных, перезапустите окно и продолжайте работать.");
|
|||
|
|
return;
|
|||
|
|
}
|
|||
|
|
students1.Remove(dGridStudent.SelectedItem as Enrollment);
|
|||
|
|
dGridStudent.SelectedIndex = -1;
|
|||
|
|
if ((e.RemovedItems[0] as Enrollment).Student != null)
|
|||
|
|
{
|
|||
|
|
MessageBox.Show("Удалено: " + (e.RemovedItems[0] as Enrollment).Student.LastName);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
MessageBox.Show("Удалена 1 запись.");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void BtnEdit_Click(object sender, RoutedEventArgs e)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
private void ToExcelButton_OnClick(object sender, RoutedEventArgs e)
|
|||
|
|
{
|
|||
|
|
var d = dGridStudent.ItemsSource.Cast<Enrollment>();
|
|||
|
|
var data = ToDataTable(d.ToList());
|
|||
|
|
var dialog = new SaveFileDialog();
|
|||
|
|
dialog.DefaultExt = ".xlsx";
|
|||
|
|
dialog.Filter = "Excel файл |*.xlsx";
|
|||
|
|
dialog.ShowDialog();
|
|||
|
|
if (dialog.FileName != String.Empty)
|
|||
|
|
{
|
|||
|
|
ToExcelFile(data, dialog.FileName);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static DataTable ToDataTable<T>(List<T> items)
|
|||
|
|
{
|
|||
|
|
var dataTable = new DataTable(typeof(T).Name);
|
|||
|
|
|
|||
|
|
var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
|
|||
|
|
foreach (var prop in properties)
|
|||
|
|
{
|
|||
|
|
var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
|
|||
|
|
dataTable.Columns.Add(prop.Name, type);
|
|||
|
|
}
|
|||
|
|
foreach (var item in items)
|
|||
|
|
{
|
|||
|
|
var values = new object[properties.Length];
|
|||
|
|
for (var i = 0; i < properties.Length; i++)
|
|||
|
|
{
|
|||
|
|
values[i] = properties[i].GetValue(item, null);
|
|||
|
|
}
|
|||
|
|
dataTable.Rows.Add(values);
|
|||
|
|
}
|
|||
|
|
return dataTable;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void ToExcelFile(DataTable dataTable, string filePath, bool overwriteFile = true)
|
|||
|
|
{
|
|||
|
|
if (File.Exists(filePath) && overwriteFile)
|
|||
|
|
File.Delete(filePath);
|
|||
|
|
|
|||
|
|
using (var connection = new OleDbConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};" +
|
|||
|
|
"Extended Properties='Excel 12.0 Xml;HDR=YES;'";
|
|||
|
|
connection.Open();
|
|||
|
|
using (var command = new OleDbCommand())
|
|||
|
|
{
|
|||
|
|
command.Connection = connection;
|
|||
|
|
var columnNames = (from DataColumn dataColumn in dataTable.Columns select dataColumn.ColumnName).ToList();
|
|||
|
|
var tableName = !string.IsNullOrWhiteSpace(dataTable.TableName) ? dataTable.TableName : Guid.NewGuid().ToString();
|
|||
|
|
command.CommandText = $"CREATE TABLE [{tableName}] ({string.Join(",", columnNames.Select(c => $"[{c}] VARCHAR").ToArray())});";
|
|||
|
|
command.ExecuteNonQuery();
|
|||
|
|
foreach (DataRow row in dataTable.Rows)
|
|||
|
|
{
|
|||
|
|
var rowValues = (from DataColumn column in dataTable.Columns select (row[column] != null && row[column] != DBNull.Value) ? row[column].ToString() : string.Empty).ToList();
|
|||
|
|
command.CommandText = $"INSERT INTO [{tableName}]({string.Join(",", columnNames.Select(c => $"[{c}]"))}) VALUES ({string.Join(",", rowValues.Select(r => $"'{r}'").ToArray())});";
|
|||
|
|
command.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
connection.Close();
|
|||
|
|
}
|
|||
|
|
MessageBox.Show("Файл успешно сохранён в "+filePath);
|
|||
|
|
}
|
|||
|
|
private void Button_Click(object sender, RoutedEventArgs e)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void combobox_courses_SelectionChanged(object sender, SelectionChangedEventArgs e)
|
|||
|
|
{
|
|||
|
|
dGridStudent.SelectedIndex = 0;
|
|||
|
|
|
|||
|
|
if (combobox_courses.SelectedItem != null)
|
|||
|
|
{
|
|||
|
|
dGridStudent.ItemsSource = students1.Where(x => x.CourseID == (combobox_courses.SelectedItem as Course).CourseID).ToList();
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
dGridStudent.ItemsSource = students1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void TextBox_TextChanged(object sender, TextChangedEventArgs e)
|
|||
|
|
{
|
|||
|
|
dGridStudent.SelectedIndex = -1;
|
|||
|
|
if (textbox_search.Text != String.Empty && dGridStudent.SelectedItem == null)
|
|||
|
|
{
|
|||
|
|
if(combobox_courses.SelectedItem != null)
|
|||
|
|
{
|
|||
|
|
dGridStudent.ItemsSource = students1.Where(x => x.Student.LastName.Contains(textbox_search.Text) && x.Course == combobox_courses.SelectedItem as Course).ToList();
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
dGridStudent.ItemsSource = students1.Where(x => x.Student.LastName.Contains(textbox_search.Text)).ToList();
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
dGridStudent.ItemsSource = students1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|