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_AllInOne3 { /// /// Interaktionslogik für MainWindow.xaml /// public partial class MainWindow : Window { ObservableCollection empl_items = new ObservableCollection(); ObservableCollection deleteItems = new ObservableCollection(); ICollectionView collView = null; // ObservableCollection dept_items = new ObservableCollection(); public MainWindow() { InitializeComponent(); } private void Window_Loaded(object sender, RoutedEventArgs e) { Title = "Employee-Dialog"; LEmployee.Content = "Employee: "; readFromDatabase(); setDatabase_Work(); } private void readFromDatabase() { ModelEmployee.dept_items.Add(new ModelDept(1, "FINZ", "Finanzen")); ModelEmployee.dept_items.Add(new ModelDept(2, "MARK", "Marketing")); ModelEmployee.dept_items.Add(new ModelDept(3, "VERT", "Vertrieb")); ModelEmployee.dept_items.Add(new ModelDept(4, "WARTG", "Wartung")); ModelEmployee.dept_items.Add(new ModelDept(5, "FPK", "Fuhrpark")); ModelEmployee.dept_items.Add(new ModelDept(6, "EDV", "Computer-Abteilung")); ModelEmployee.dept_items.Add(new ModelDept(7, "TEL", "Telefon-Handy")); ModelEmployee.dept_items.Add(new ModelDept(8, "RS", "Reisen und Übernachtung")); ModelEmployee.dept_items.Add(new ModelDept(9, "BFR", "Betriebsfeiern")); ModelEmployee.dept_items.Add(new ModelDept(10, "PERS", "Personal")); ModelEmployee.dept_items.Add(new ModelDept(11, "BER", "Betriebsfeiern")); ModelEmployee.dept_items.Add(new ModelDept(12, "FIBER", "Betriebsfeiern")); ModelEmployee.dept_items.Add(new ModelDept(13, "BIN", "Betriebsfeiern")); foreach (ModelDept dept in ModelEmployee.dept_items) { dept.newAttrib = false; dept.modifyAttrib = false; } empl_items.Add(new ModelEmployee(1, "Andreas", "Meier", 1000, true, 1)); empl_items.Add(new ModelEmployee(2, "Ute", "Schulze", 2000, false, 2)); empl_items.Add(new ModelEmployee(3, "Hans", "Bischoff", 1500, true, 3)); empl_items.Add(new ModelEmployee(4, "Bernd", "Wolff", 5000, false, 2)); empl_items.Add(new ModelEmployee(5, "Ralf", "Heinmann", 3000, true, 2)); empl_items.Add(new ModelEmployee(6, "Rolf", "Gemein", 4000, true, 6)); empl_items.Add(new ModelEmployee(7, "Volker", "Brandt", 2000, false, 3)); empl_items.Add(new ModelEmployee(8, "Martin", "Hubert", 1000, true, 1)); empl_items.Add(new ModelEmployee(9, "Uwe", "Schulz", 700, false, 5)); empl_items.Add(new ModelEmployee(10, "Hans Hugo", "Schlundt", 2500, true, 7)); empl_items.Add(new ModelEmployee(11, "Ingo", "Meier", 800, false, 11)); empl_items.Add(new ModelEmployee(12, "Frank", "Schulz", 1000, false,10)); empl_items.Add(new ModelEmployee(13, "Lothar", "Hinkelstein", 10000, true, 11)); empl_items.Add(new ModelEmployee(14, "Klaus", "Brandt", 1000, false, 1)); empl_items.Add(new ModelEmployee(15, "Peter", "Brand", 1000, false, 4)); empl_items.Add(new ModelEmployee(16, "Rolf", "Ludwig", 1000, false, 6)); empl_items.Add(new ModelEmployee(17, "Susanne", "Vega", 3000, true, 8)); empl_items.Add(new ModelEmployee(18, "Dorethea", "Otmann", 4000, false, 4)); empl_items.Add(new ModelEmployee(19, "Anton", "Brand", 1000, false, 6)); empl_items.Add(new ModelEmployee(20, "Ute", "Müller", 2000, true,3)); empl_items.Add(new ModelEmployee(21, "Anna", "Brandt", 3000, true, 5)); empl_items.Add(new ModelEmployee(22, "Hans Georg", "Büchner", 1500, true, 5)); empl_items.Add(new ModelEmployee(23, "Kevin", "Schulze", 400, true, 7)); empl_items.Add(new ModelEmployee(24, "Chanthal", "Wissmann", 1000, false, 5)); foreach (ModelEmployee empl in empl_items) { empl.newAttrib = false; empl.modifyAttrib = false; } } private void setDatabase_Work() { datagrid.AutoGenerateColumns = false; // DataGridComboBoxColumn cbDept.ItemsSource = ModelEmployee.dept_items; datagrid.ItemsSource = empl_items; setCount(empl_items.Count); collView = CollectionViewSource.GetDefaultView(datagrid.ItemsSource); collView.SortDescriptions.Add(new SortDescription("Lastname", ListSortDirection.Ascending)); collView.SortDescriptions.Add(new SortDescription("Firstname", ListSortDirection.Ascending)); datagrid.Columns[0].Width = 100; datagrid.Columns[1].Width = 200; // datagrid.Width - 200; datagrid.Columns[2].Width = 50; datagrid.CanUserAddRows = false; gridDetails.DataContext = empl_items[0]; } private bool getModify() { bool modify = false; foreach (ModelEmployee empl in empl_items) { if (empl.modifyAttrib) modify = true; if (empl.newAttrib) modify = true; } if (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 (ModelEmployee empl in empl_items) { empl.modifyAttrib = false; empl.newAttrib = false; } 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")) { //TextBox t = e.EditingElement as TextBox; //double monthsalary; //if (double.TryParse(t.Text.Trim(), System.Globalization.NumberStyles.AllowDecimalPoint, // System.Globalization.CultureInfo.InvariantCulture, out monthsalary)) { // DataGridRow row = e.Row; // ModelEmployee empl = (ModelEmployee)row.Item; // empl.Yearsalary = monthsalary * 12.0; // System.Diagnostics.Debug.WriteLine("datagrid_CellEditEnding: Yearsalary: " + empl.Yearsalary); // empl.updateYearsalary(); // important //} 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(tRow.Text.Trim(), out row)) { if (row >= 0 && row < empl_items.Count) { datagrid.SelectedIndex = row; datagrid_SelectionChanged(null, null); datagrid.ScrollIntoView(datagrid.SelectedItem); datagrid.Focus(); } } } // tRow } private void datagrid_SelectionChanged(object sender, SelectionChangedEventArgs e) { if (datagrid.SelectedIndex >= 0) { ModelEmployee empl = (ModelEmployee)datagrid.SelectedItem; LEmployee.Content = empl.Lastname + ", " + empl.Firstname; gridDetails.DataContext = empl; tRowAction = true; tRow.Text = datagrid.SelectedIndex.ToString(); tRowAction = false; } } // change the data with an extern event private void bnTest_Click(object sender, RoutedEventArgs e) { ModelEmployee empl = empl_items[2]; // that's NOT the second item in the datagrid !!! SORT !!! empl.Monthsalary = 2000; LEmployee.Content = "Test: " + empl.Lastname + ", " + empl.Firstname; collView.Refresh(); // important } private void setCount(long count) { lRow.Content = "von {" + empl_items.Count + "}"; } private void save2Database() { // Dbs dbs = Dbs.getDbsInstance(); String sql; foreach (ModelEmployee empl in empl_items) { System.Diagnostics.Debug.WriteLine("empl: " + empl + " dept: " + empl.Dept); System.Diagnostics.Debug.WriteLine(" aindex: " + empl.Dept.PIndex); } foreach (ModelEmployee mit in 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,aindex) " + "Values (" + empl.PIndex + ", " + empl.getFirstnameQuote() + ", " + empl.getLastnameQuote() + ", " + empl.Monthsalary + ", " + empl.Dept.PIndex + ");"; 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 + ", aindex=" + empl.Dept.PIndex + " " + " WHERE pindex=" + empl.PIndex + ";"; System.Diagnostics.Debug.WriteLine("sql: " + sql); // empl.changeDBS(sql); } } } } private void tFilter_TextChanged(object sender, TextChangedEventArgs e) { String f = tFilter.Text.Trim(); if (f.Length == 0) { collView.Filter = null; } else { collView.Filter = mit => (mit as ModelEmployee).Lastname.StartsWith(f); } } private void bnFirst_Click(object sender, RoutedEventArgs e) { datagrid.SelectedIndex = 0; datagrid.ScrollIntoView(datagrid.SelectedItem); datagrid.Focus(); } private void bnPrev_Click(object sender, RoutedEventArgs e) { if (datagrid.SelectedIndex > 0) { datagrid.SelectedIndex = datagrid.SelectedIndex - 1; datagrid.ScrollIntoView(datagrid.SelectedItem); datagrid.Focus(); } } private void bnNext_Click(object sender, RoutedEventArgs e) { if (datagrid.SelectedIndex < (empl_items.Count - 1)) { datagrid.SelectedIndex = datagrid.SelectedIndex + 1; datagrid.ScrollIntoView(datagrid.SelectedItem); datagrid.Focus(); } } private void bnLast_Click(object sender, RoutedEventArgs e) { datagrid.SelectedIndex = empl_items.Count - 1; datagrid.ScrollIntoView(datagrid.SelectedItem); datagrid.Focus(); } private long sequence = 0; private long getSequenz(String seqname) { return ++sequence; } private void bnPlus_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 bnEdit_Click(object sender, RoutedEventArgs e) { ModelEmployee empl = (ModelEmployee)datagrid.SelectedItem; if (empl != null) { empl.Firstname = empl.Firstname + "a"; empl.Lastname = empl.Lastname + "b"; empl.Monthsalary = empl.Monthsalary * 1.1; } } private void bnDelete_Click(object sender, RoutedEventArgs e) { ModelEmployee empl = (ModelEmployee)datagrid.SelectedItem; if (empl != null) { empl_items.Remove(empl); setCount(empl_items.Count); 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 ModelDept(ModelDept dept) { PIndex = dept.pindex; ShortName = dept.shortName; LongName = dept.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; return shortName; } public override bool Equals(object obj) { if (obj is ModelDept) { ModelDept dept = (ModelDept)obj; return shortName.Equals(dept.shortName); } else { return false; } } public override int GetHashCode() { return shortName.GetHashCode(); } } // ModelDept private class ModelEmployee : ModelBase { public static ObservableCollection dept_items = new ObservableCollection(); private long pindex; private String firstname; private String lastname; private double monthsalary; private double yearsalary; private ModelDept dept; private bool isGoodWorker; public ModelEmployee() : this(-1, "", "", 0, false, 0) { } public ModelEmployee(long pindex, String firstname, String lastname, double monthsalary, bool isGoodWorker, long aindex) { Firstname = firstname; Lastname = lastname; Monthsalary = monthsalary; dept = null; IsGoodWorker = isGoodWorker; foreach (ModelDept dept_for in ModelEmployee.dept_items) { if (dept_for.PIndex == aindex) { this.dept = new ModelDept(dept_for); break; } } } 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 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 ModelDept Dept { get { return dept; } set { NotifyPropertyChanged("Dept"); modifyAttrib = true; dept = value; } } public bool IsGoodWorker { get { return isGoodWorker; } set { NotifyPropertyChanged("IsGoodSeller"); modifyAttrib = true; isGoodWorker = value; } } public override String ToString() { return lastname + " " + firstname; } } // ModelEmployee } }