using System; using System.Collections.Generic; using System.Linq; 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.Collections.ObjectModel; using System.Data; using System.ComponentModel; using System.Collections.Specialized; using System.Reflection; // ICollectionView namespace Database_AllInOne2 { /// /// Interaktionslogik für MainWindow.xaml /// public partial class MainWindow : Window { ObservableCollection empl_items = new ObservableCollection(); ObservableCollection dept_items = new ObservableCollection(); ObservableCollection dept_deleteItems = new ObservableCollection(); ObservableCollection employee_deleteItems = new ObservableCollection(); ICollectionView collViewDept = null; ICollectionView collViewEmployee = null; public MainWindow() { InitializeComponent(); } private void Window_Loaded(object sender, RoutedEventArgs e) { Title = "Department-Employee-Dialog"; LEmployee.Content = "Employee: "; readFromDatabase(); setDatabase_Work_Dept(); setDatabase_Work_Employee(); } private void readFromDatabase() { empl_items.Add(new ModelEmployee(1, "Andreas", "Meier", 1000,1)); empl_items.Add(new ModelEmployee(2, "Ute", "Schulze", 2000,2)); empl_items.Add(new ModelEmployee(3, "Hans", "Bischoff", 1500,3)); empl_items.Add(new ModelEmployee(4, "Bernd", "Wolff", 5000,2)); empl_items.Add(new ModelEmployee(5, "Ralf", "Heinmann", 3000,2)); empl_items.Add(new ModelEmployee(6, "Rolf", "Gemein", 4000,6)); empl_items.Add(new ModelEmployee(7, "Volker", "Brandt", 2000,3)); empl_items.Add(new ModelEmployee(8, "Martin", "Hubert", 1000,1)); empl_items.Add(new ModelEmployee(9, "Uwe", "Schulz", 700,5)); empl_items.Add(new ModelEmployee(10, "Hans Hugo", "Schlundt", 2500,7)); empl_items.Add(new ModelEmployee(11, "Ingo", "Meier", 800,11)); empl_items.Add(new ModelEmployee(12, "Frank", "Schulz", 1000,15)); empl_items.Add(new ModelEmployee(13, "Lothar", "Hinkelstein", 10000,11)); empl_items.Add(new ModelEmployee(14, "Klaus", "Brandt", 1000,1)); empl_items.Add(new ModelEmployee(15, "Peter", "Brand", 1000,4)); empl_items.Add(new ModelEmployee(16, "Rolf", "Ludwig", 1000,6)); empl_items.Add(new ModelEmployee(17, "Susanne", "Vega", 3000,8)); empl_items.Add(new ModelEmployee(18, "Dorethea", "Otmann", 4000,4)); empl_items.Add(new ModelEmployee(19, "Anton", "Brand", 1000,6)); empl_items.Add(new ModelEmployee(20, "Ute", "Müller", 2000,3)); empl_items.Add(new ModelEmployee(21, "Anna", "Brandt", 3000,5)); empl_items.Add(new ModelEmployee(22, "Hans Georg", "Büchner", 1500,5)); empl_items.Add(new ModelEmployee(23, "Kevin", "Schulze", 400,7)); empl_items.Add(new ModelEmployee(24, "Chanthal", "Wissmann", 1000,5)); foreach (ModelEmployee empl in empl_items) { empl.newAttrib = false; empl.modifyAttrib = false; } dept_items.Add(new ModelDept(1, "FINZ", "Finanzen")); dept_items.Add(new ModelDept(2, "MARK", "Marketing")); dept_items.Add(new ModelDept(3, "VERT", "Vertrieb")); dept_items.Add(new ModelDept(4, "WARTG", "Wartung")); dept_items.Add(new ModelDept(5, "FPK", "Fuhrpark")); dept_items.Add(new ModelDept(6, "EDV", "Computer-Abteilung")); dept_items.Add(new ModelDept(7, "TEL", "Telefon-Handy")); dept_items.Add(new ModelDept(8, "RS", "Reisen und Übernachtung")); dept_items.Add(new ModelDept(9, "BFR", "Betriebsfeiern")); dept_items.Add(new ModelDept(10, "PERS", "Personal")); dept_items.Add(new ModelDept(11, "BER", "Betriebsfeiern")); dept_items.Add(new ModelDept(12, "FIBER", "Betriebsfeiern")); dept_items.Add(new ModelDept(13, "BIN", "Betriebsfeiern")); foreach (ModelDept dept in dept_items) { dept.newAttrib = false; dept.modifyAttrib = false; } } private void setDatabase_Work_Dept() { this.datagridDept.AutoGenerateColumns = false; Type myType = typeof(ModelDept); PropertyInfo[] propertyInfos = myType.GetProperties(); // BindingFlags.Public foreach (PropertyInfo propertyInfo in propertyInfos) { System.Diagnostics.Debug.WriteLine(propertyInfo.Name); DataGridTextColumn dataColumn = new DataGridTextColumn(); dataColumn.Header = propertyInfo.Name; Binding binding = new Binding(propertyInfo.Name); binding.Mode = BindingMode.TwoWay; binding.UpdateSourceTrigger = UpdateSourceTrigger.PropertyChanged; // neu dataColumn.Binding = binding; datagridDept.Columns.Add(dataColumn); } datagridDept.ItemsSource = dept_items; setCountDept(dept_items.Count); collViewDept = CollectionViewSource.GetDefaultView(datagridDept.ItemsSource); collViewDept.SortDescriptions.Add(new SortDescription("ShortName", ListSortDirection.Ascending)); datagridDept.Columns[0].Width = 100; datagridDept.Columns[1].Width = 200; // datagridDept.Width - 200; datagridDept.Columns[2].Width = 50; datagridDept.CanUserAddRows = false; datagridDept.DataContext = dept_items[0]; } private void setDatabase_Work_Employee() { this.datagridDept.AutoGenerateColumns = false; Type myType = typeof(ModelEmployee); PropertyInfo[] propertyInfos = myType.GetProperties(); // BindingFlags.Public foreach (PropertyInfo propertyInfo in propertyInfos) { System.Diagnostics.Debug.WriteLine(propertyInfo.Name); DataGridTextColumn dataColumn = new DataGridTextColumn(); dataColumn.Header = propertyInfo.Name; Binding binding = new Binding(propertyInfo.Name); binding.Mode = BindingMode.TwoWay; binding.UpdateSourceTrigger = UpdateSourceTrigger.PropertyChanged; // neu dataColumn.Binding = binding; datagridEmployee.Columns.Add(dataColumn); } datagridEmployee.ItemsSource = empl_items; setCountEmployee(empl_items.Count); collViewEmployee = CollectionViewSource.GetDefaultView(datagridEmployee.ItemsSource); collViewEmployee.SortDescriptions.Add(new SortDescription("Lastname", ListSortDirection.Ascending)); collViewEmployee.SortDescriptions.Add(new SortDescription("Firstname", ListSortDirection.Ascending)); collViewEmployee.Filter = EmployeeFilter; datagridEmployee.Columns[0].Width = 100; datagridEmployee.Columns[1].Width = 200; // datagridDept.Width - 200; datagridEmployee.Columns[2].Width = 50; datagridEmployee.CanUserAddRows = false; datagridEmployee.DataContext = empl_items[0]; } private bool getModify() { bool modify = false; foreach (ModelDept dept in dept_items) { if (dept.modifyAttrib) modify = true; if (dept.newAttrib) modify = true; } if (dept_deleteItems.Count > 0) modify = true; foreach (ModelEmployee empl in empl_items) { if (empl.modifyAttrib) modify = true; if (empl.newAttrib) modify = true; } if (employee_deleteItems.Count > 0) modify = true; return modify; } public MessageBoxResult readYesNoCancel(string message, string title) { return MessageBox.Show(message, title, MessageBoxButton.YesNoCancel, MessageBoxImage.Question); // Exclamation } private void Window_Closing(object sender, CancelEventArgs e) { e.Cancel = false; if (getModify()) { MessageBoxResult res = readYesNoCancel("Change the modification?", "Close Dialog"); switch (res) { case MessageBoxResult.Yes: save2Database(); break; case MessageBoxResult.No: break; case MessageBoxResult.Cancel: e.Cancel = false; return; } } } private void bnOk_Click(object sender, RoutedEventArgs e) { if (getModify()) { save2Database(); foreach (ModelDept dept in dept_items) { dept.modifyAttrib = false; dept.newAttrib = false; } foreach (ModelEmployee empl in empl_items) { empl.modifyAttrib = false; empl.newAttrib = false; } dept_deleteItems.Clear(); employee_deleteItems.Clear(); } Close(); } private void bnEsc_Click(object sender, RoutedEventArgs e) { Close(); } // Spalten, die nicht editierbar sein sollen private void datagrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e) { DataGridColumn col = e.Column; if (col.Header.Equals("PIndex") || col.Header.Equals("Yearsalary")) { e.Cancel = true; } } private void datagrid_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e) { DataGridColumn col = e.Column; e.Cancel = false; if (col.Header.Equals("Lastname")) { TextBox t = e.EditingElement as TextBox; String item = t.Text.Trim(); if (item.Length == 0) e.Cancel = true; } if (col.Header.Equals("Firstname")) { TextBox t = e.EditingElement as TextBox; String item = t.Text.Trim(); if (item.Length == 0) e.Cancel = true; } if (col.Header.Equals("Monthsalary")) { DataGridRow row = e.Row; ModelEmployee empl = (ModelEmployee)row.Item; empl.updateYearsalary(); // important } System.Diagnostics.Debug.WriteLine("datagrid_CellEditEnding"); } bool tRowAction = false; private void tRow_TextChanged(object sender, TextChangedEventArgs e) { if (!tRowAction) { int row; if (int.TryParse(tRow1.Text.Trim(), out row)) { if (row >= 0 && row < empl_items.Count) { datagridDept.SelectedIndex = row; datagrid_SelectionChanged(null, null); datagridDept.ScrollIntoView(datagridDept.SelectedItem); datagridDept.Focus(); } } } // tRow } private void datagrid_SelectionChanged(object sender, SelectionChangedEventArgs e) { if (datagridDept.SelectedIndex >= 0) { ModelDept dept = (ModelDept)datagridDept.SelectedItem; LDepartment.Content = dept.ShortName + ", " + dept.LongName; tRowAction = true; tRow1.Text = datagridDept.SelectedIndex.ToString(); tRowAction = false; department_employee_aindex = dept.PIndex; tFilter2_TextChanged(null, null); } } private void setCountDept(long count) { lRow1.Content = "von {" + count + "}"; } private void setCountEmployee(long count) { lRow2.Content = "von {" + count + "}"; } private void save2Database() { // Dbs dbs = Dbs.getDbsInstance(); String sql; foreach (ModelEmployee mit in employee_deleteItems) { sql = "DELETE FROM Employee WHERE pindex=" + mit.PIndex + ";"; System.Diagnostics.Debug.WriteLine("sql: " + sql); //dbs.changeDBS(sql); } foreach (ModelEmployee empl in empl_items) { if (empl.newAttrib) { sql = "INSERT INTO Employee(pindex, firstname, lastname, monthsalary) " + "Values (" + empl.PIndex + ", " + empl.getFirstnameQuote() + ", " + empl.getLastnameQuote() + ", " + empl.Monthsalary + ");"; System.Diagnostics.Debug.WriteLine("sql: " + sql); //dbs.changeDBS(sql); } else { if (empl.modifyAttrib) { sql = "UPDATE Employee " + " SET firstname=" + empl.getFirstnameQuote() + ", lastname=" + empl.getLastnameQuote() + ", monthsalary=" + empl.Monthsalary + " " + " WHERE pindex=" + empl.PIndex + ";"; System.Diagnostics.Debug.WriteLine("sql: " + sql); // empl.changeDBS(sql); } } } } private void tFilter1_TextChanged(object sender, TextChangedEventArgs e) { String f = tFilter1.Text.Trim(); if (f.Length == 0) { collViewDept.Filter = null; } else { collViewDept.Filter = mit => (mit as ModelDept).ShortName.StartsWith(f); } } private void bnFirst1_Click(object sender, RoutedEventArgs e) { datagridDept.SelectedIndex = 0; datagridDept.ScrollIntoView(datagridDept.SelectedItem); datagridDept.Focus(); } private void bnPrev1_Click(object sender, RoutedEventArgs e) { if (datagridDept.SelectedIndex > 0) { datagridDept.SelectedIndex = datagridDept.SelectedIndex - 1; datagridDept.ScrollIntoView(datagridDept.SelectedItem); datagridDept.Focus(); } } private void bnNext1_Click(object sender, RoutedEventArgs e) { if (datagridDept.SelectedIndex < (dept_items.Count - 1)) { datagridDept.SelectedIndex = datagridDept.SelectedIndex + 1; datagridDept.ScrollIntoView(datagridDept.SelectedItem); datagridDept.Focus(); } } private void bnLast1_Click(object sender, RoutedEventArgs e) { datagridDept.SelectedIndex = dept_items.Count - 1; datagridDept.ScrollIntoView(datagridDept.SelectedItem); datagridDept.Focus(); } private long sequence = 0; private long getSequenz(String seqname) { return ++sequence; } private void bnPlus1_Click(object sender, RoutedEventArgs e) { ModelDept dept = new ModelDept(); dept.PIndex = getSequenz("seq"); dept.ShortName = "ShortName" + dept.PIndex; dept.ShortName = "ShortName" + dept.PIndex; dept_items.Add(dept); //} } // simulate a edit dialog private void bnEdit1_Click(object sender, RoutedEventArgs e) { ModelDept dept = (ModelDept)datagridDept.SelectedItem; if (dept != null) { dept.ShortName = dept.ShortName + "a"; dept.LongName = dept.LongName + "b"; } } private void bnDelete1_Click(object sender, RoutedEventArgs e) { ModelDept dept = (ModelDept)datagridDept.SelectedItem; if (dept != null) { dept_items.Remove(dept); setCountDept(dept_items.Count); dept_deleteItems.Add(dept); } } // ------------------------------------------------------------------ String filterEmployeeString = ""; private long department_employee_aindex = 0; private bool EmployeeFilter(object item) { ModelEmployee empl = item as ModelEmployee; if (filterEmployeeString.Length == 0 && department_employee_aindex == 0) return true; else if (filterEmployeeString.Length>0 && department_employee_aindex > 0) return empl.Lastname.StartsWith(filterEmployeeString) && empl.DeptIindex == department_employee_aindex; else if (filterEmployeeString.Length > 0) return empl.Lastname.StartsWith(filterEmployeeString); else if (department_employee_aindex > 0) return empl.DeptIindex == department_employee_aindex; else return true; } // EmployeeFilter private void tFilter2_TextChanged(object sender, TextChangedEventArgs e) { filterEmployeeString = tFilter2.Text.Trim(); collViewEmployee.Refresh(); } private void bnFirst2_Click(object sender, RoutedEventArgs e) { datagridEmployee.SelectedIndex = 0; datagridEmployee.ScrollIntoView(datagridEmployee.SelectedItem); datagridEmployee.Focus(); } private void bnPrev2_Click(object sender, RoutedEventArgs e) { if (datagridEmployee.SelectedIndex > 0) { datagridEmployee.SelectedIndex = datagridEmployee.SelectedIndex - 1; datagridEmployee.ScrollIntoView(datagridEmployee.SelectedItem); datagridEmployee.Focus(); } } private void bnNext2_Click(object sender, RoutedEventArgs e) { if (datagridEmployee.SelectedIndex < (empl_items.Count - 1)) { datagridEmployee.SelectedIndex = datagridEmployee.SelectedIndex + 1; datagridEmployee.ScrollIntoView(datagridEmployee.SelectedItem); datagridEmployee.Focus(); } } private void bnLast2_Click(object sender, RoutedEventArgs e) { datagridEmployee.SelectedIndex = empl_items.Count - 1; datagridEmployee.ScrollIntoView(datagridEmployee.SelectedItem); datagridEmployee.Focus(); } private void bnPlus2_Click(object sender, RoutedEventArgs e) { ModelEmployee empl = new ModelEmployee(); // FReadInputMitarbeiter dialog = new FReadInputMitarbeiter(this, mit); // dialog.ShowDialog(); //if (dialog.Retcode) { empl.PIndex = getSequenz("seq"); empl.Firstname = "Firstname_" + empl.PIndex; empl.Lastname = empl.PIndex + "Lastname"; // filter ! empl.Monthsalary = empl.PIndex * 10; empl_items.Add(empl); //} } // simulate a edit dialog private void bnEdit2_Click(object sender, RoutedEventArgs e) { ModelEmployee empl = (ModelEmployee)datagridEmployee.SelectedItem; if (empl != null) { empl.Firstname = empl.Firstname + "a"; empl.Lastname = empl.Lastname + "b"; empl.Monthsalary = empl.Monthsalary * 1.1; } } private void bnDelete2_Click(object sender, RoutedEventArgs e) { ModelEmployee empl = (ModelEmployee)datagridEmployee.SelectedItem; if (empl != null) { empl_items.Remove(empl); setCountEmployee(empl_items.Count); employee_deleteItems.Add(empl); } } // ------------------------------------------------------------------ public abstract class ModelBase : IComparable, INotifyPropertyChanged { public bool newAttrib = true; public bool modifyAttrib = false; public abstract int CompareTo(object obj); public void NotifyPropertyChanged(string name) { if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(name)); } } public event PropertyChangedEventHandler PropertyChanged; } // ModelBase public class ModelDept : ModelBase { private String shortName; private String longName; private long pindex; public ModelDept() : this(-1, "", "") { } public ModelDept(long pindex, String shortName, String longName) { PIndex = pindex; ShortName = shortName; LongName = longName; } public override int CompareTo(object obj) { ModelDept dept = (ModelDept)obj; return shortName.CompareTo(dept.shortName); } public String getShortNameQuote() { return "'" + shortName + "'"; } public String getLongNameQuote() { return "'" + longName + "'"; } public String ShortName { get { return shortName; } set { if (value != shortName) { NotifyPropertyChanged("ShortName"); modifyAttrib = true; shortName = value; System.Diagnostics.Debug.WriteLine("Change ShortName: " + shortName); } } } public String LongName { get { return longName; } set { if (value != longName) { NotifyPropertyChanged("LongName"); modifyAttrib = true; longName = value; System.Diagnostics.Debug.WriteLine("Change LongName: " + longName); } } } public long PIndex { get { return pindex; } set { if (value != pindex) { NotifyPropertyChanged("PIndex"); modifyAttrib = true; pindex = value; } } } public override String ToString() { return shortName + " " + longName; } } // ModelDept private class ModelEmployee : ModelBase { private long pindex; private String firstname; private String lastname; private double monthsalary; private double yearsalary; private long deptIindex; public ModelEmployee() : this(-1, "", "", 0,0) { } public ModelEmployee(long pindex, String firstname, String lastname, double monthsalary, long deptIindex) { Firstname = firstname; Lastname = lastname; Monthsalary = monthsalary; DeptIindex = deptIindex; } public override int CompareTo(object obj) { ModelEmployee empl = (ModelEmployee)obj; int i = lastname.CompareTo(empl.lastname); if (i != 0) return i; else return firstname.CompareTo(empl.firstname); } public String getFirstnameQuote() { return "'" + firstname + "'"; } public String getLastnameQuote() { return "'" + lastname + "'"; } public String Firstname { get { return firstname; } set { if (value != firstname) { NotifyPropertyChanged("Firstname"); modifyAttrib = true; firstname = value; System.Diagnostics.Debug.WriteLine("Change firstname: " + firstname); } } } public String Lastname { get { return lastname; } set { if (value != lastname) { NotifyPropertyChanged("Lastname"); modifyAttrib = true; lastname = value; System.Diagnostics.Debug.WriteLine("Change lastname: " + lastname); } } } public bool CheckFilter(String prefix, long aindex) { if (aindex > 0) return Lastname.StartsWith(prefix) && this.deptIindex == aindex; else return Lastname.StartsWith(prefix); } public double Monthsalary { get { return monthsalary; } set { if (value != monthsalary) { NotifyPropertyChanged("Monthsalary"); modifyAttrib = true; monthsalary = value; Yearsalary = monthsalary * 12.0; System.Diagnostics.Debug.WriteLine("Change Monthsalary: " + monthsalary); } } } // muss vorhanden sein, für die Datagrid public double Yearsalary { get { return yearsalary; } set { if (value != yearsalary) { NotifyPropertyChanged("Yearsalary"); modifyAttrib = true; yearsalary = value; System.Diagnostics.Debug.WriteLine("Change Yearsalary: " + yearsalary); } } } public void updateYearsalary() { NotifyPropertyChanged("Yearsalary"); System.Diagnostics.Debug.WriteLine("updateYearsalary: " + yearsalary); } public long PIndex { get { return pindex; } set { if (value != pindex) { NotifyPropertyChanged("PIndex"); modifyAttrib = true; pindex = value; } } } public long DeptIindex { get { return deptIindex; } set { if (value != deptIindex) { NotifyPropertyChanged("AIndex"); modifyAttrib = true; deptIindex = value; } } } public override String ToString() { return lastname + " " + firstname; } } // ModelEmployee } }