internal static Crime gtNewCrime(SqliteWorker sqlWorker, Crime _c = null) { AddCrime wndCrime = new AddCrime(sqlWorker, _c); wndCrime.ShowDialog(); return(wndCrime.c); }
private void crimesDataGrid_MouseDoubleClick(object sender, MouseButtonEventArgs e) { if (crimesDataGrid.SelectedItem != null) { Crime newC = AddCrime.gtNewCrime(sqlWorker, crimesDataGrid.SelectedItem as Crime); for (int i = 0; i < crimes.Count; i++) { if (newC.Id == crimes[i].Id) { crimes[i] = newC; } } crimes = DataWorker.getCrimes( sqlWorker, dpLeft.Text == "" ? "" : dpLeft.SelectedDate.Value.ToString("yyyy.MM.dd"), dpRight.Text == "" ? "9999.99.99" : dpRight.SelectedDate.Value.ToString("yyyy.MM.dd")); coll.Source = crimes; coll.Filter += coll_Filter; crimesDataGrid.ItemsSource = coll.View; coll.View.GroupDescriptions.Clear(); if (chkGroupMU.IsChecked.Value) { coll.View.GroupDescriptions.Add(new PropertyGroupDescription("MilitaryUnit")); } if (cbGroupOn.SelectedIndex == 1) { coll.View.GroupDescriptions.Add(new PropertyGroupDescription("Accomplice")); } if (cbGroupOn.SelectedIndex == 2) { coll.View.GroupDescriptions.Add(new PropertyGroupDescription("Clause")); } coll.View.Refresh(); } }
internal bool updateCrime(Crime c, AccompliceList accompliceList, DBList categoryList, int oldId) { if (addCrime(c, accompliceList, categoryList) && deleteCrime(oldId)) { return(true); } return(false); }
private void btnAddCrimeOrIncidents_Click_1(object sender, RoutedEventArgs e) { Crime c = AddCrime.gtNewCrime(sqlWorker); if (c != null) { crimes.Add(c); } }
internal bool addCrime(Crime newItem, AccompliceList accompliceList, DBList categoryList) { try { executeQuery("INSERT INTO Crime VALUES(" + newItem.Id + "," + (newItem.IdOrgan == 0 ? "NULL" : newItem.IdOrgan.ToString()) + "," + (newItem.IdClause == 0 ? "NULL" : newItem.IdClause.ToString()) + "," + (newItem.IdMilitaryUnit == 0 ? "NULL" : newItem.IdMilitaryUnit.ToString()) + "," + (newItem.DateRegistration == "" ? "NULL" : "'" + newItem.DateRegistration + "'") + "," + (newItem.DateInstitution == "" ? "NULL" : "'" + newItem.DateInstitution + "'") + "," + (newItem.DateCommit == "" ? "NULL" : "'" + newItem.DateCommit + "'") + "," + (newItem.Story == "" ? "NULL" : "'" + newItem.Story + "'") + "," + (newItem.Damage == "" ? "NULL" : "'" + newItem.Damage + "'") + "," + (newItem.DateVerdict == "" ? "NULL" : "'" + newItem.DateVerdict + "'") + "," + (newItem.Verdict == "" ? "NULL" : "'" + newItem.Verdict + "'") + "," + (newItem.NumberCase == "" ? "NULL" : "'" + newItem.NumberCase + "'") + "," + (newItem.IsRegistred == true ? "1" : "0") + ");"); if (accompliceList != null) { for (int i = 0; i < accompliceList.values.Count; i++) { executeQuery("INSERT INTO Portaking VALUES(" + accompliceList.values[i].Id + "," + newItem.Id + ");"); } } for (int i = 0; i < categoryList.values.Count; i++) { if (categoryList.values[i].IsCheked) { executeQuery("INSERT INTO InCategory VALUES(" + categoryList.values[i].Key + "," + newItem.Id + ");"); } } return(true); } catch (Exception ex) { return(false); } }
public AddCrime(SqliteWorker _sqlWorker, Crime _c = null) { InitializeComponent(); sqlWorker = _sqlWorker; organList = new DBList("Organ", DataWorker.getList(sqlWorker.selectData("SELECT * FROM Organ ORDER BY description"))); categoryList = new DBList("Category", DataWorker.getList(sqlWorker.selectData("SELECT * FROM Category ORDER BY description"))); militaryList = new MilitaryUnitList(DataWorker.getMilitaryUnitList(sqlWorker.selectData("SELECT * FROM MilitaryUnit"))); clauseList = new ClauseList(DataWorker.getClauseList(sqlWorker.selectData("SELECT * FROM Clause ORDER BY number, part, point"))); cbOrgan.ItemsSource = organList.values; cbMilitaryUnit.ItemsSource = militaryList.values; cbClause.ItemsSource = clauseList.values; lbCategoty.ItemsSource = categoryList.values; if (_c != null) { isEditing = true; c = _c; txDamage.Text = c.Damage; txDateCommit.Text = c.DateCommit; txDateInstitution.Text = c.DateInstitution; txDateRegistration.Text = c.DateRegistration; txDateVerdict.Text = c.DateVerdict; txnumberCase.Text = c.NumberCase; txStory.Text = c.Story; txVerdict.Text = c.Verdict; //если происшествие if (c.Clause == "") { rbIncident.IsChecked = true; } chkIsRegistred.IsChecked = c.IsRegistred; for (int i = 0; i < cbOrgan.Items.Count; i++) { if ((cbOrgan.Items[i] as KeyValue).Key == c.IdOrgan) { cbOrgan.SelectedIndex = i; } } for (int i = 0; i < cbClause.Items.Count; i++) { if ((cbClause.Items[i] as Clause).Id == c.IdClause) { cbClause.SelectedIndex = i; } } for (int i = 0; i < cbMilitaryUnit.Items.Count; i++) { if ((cbMilitaryUnit.Items[i] as MilitaryUnit).Id == c.IdMilitaryUnit) { cbMilitaryUnit.SelectedIndex = i; } } accompliceList = new AccompliceList( DataWorker.getAccompliceList( sqlWorker.selectData("SELECT R.shortName as rank, S.shortName as subUnit, SF.shortName as battalion, M.shortName as militaryUnit, A.*, R.priority " + "FROM Accomplice A " + "INNER JOIN SubUnit S ON S.idSubUnit = A.idSubUnit " + "INNER JOIN Portaking P ON P.idAccomplice = A.idAccomplice " + "LEFT JOIN Rank R ON R.idRank = A.idRank " + "LEFT JOIN SubUnit SF ON S.idFKSubUnit = SF.idSubUnit " + "LEFT JOIN MilitaryUnit M ON M.idMilitaryUnit = S.idMilitaryUnit OR M.idMilitaryUnit = SF.idMilitaryUnit " + "WHERE P.idCrime = " + c.Id))); lbAccomplice.ItemsSource = accompliceList.values; //categoryList DBList categoryUsed = new DBList("", DataWorker.getList(sqlWorker.selectData("SELECT * FROM InCategory WHERE idCrime = " + c.Id + ";"))); for (int i = 0; i < categoryUsed.values.Count; i++) { for (int j = 0; j < categoryList.values.Count; j++) { if (categoryUsed.values[i].Key == categoryList.values[j].Key) { categoryList.values[j].IsCheked = true; } } } } else { chkIsRegistred.IsChecked = true; } }
private void btnOk_Click(object sender, RoutedEventArgs e) { int newId = sqlWorker.getNewId("Crime"); int oldId = 0; if (rbCrime.IsChecked == true && cbClause.SelectedItem != null && cbMilitaryUnit.SelectedItem != null && txStory.Text != "" && txDateRegistration.Text != "") { //заполнен минимум для преступления string accomplices = "", postAccomplice = ""; if (accompliceList != null) { for (int i = 0; i < accompliceList.values.Count; i++) { accomplices += (i == 0 ? "" : "\n") + accompliceList.values[i].Rank + " " + accompliceList.values[i].ShortName; //в идеале надо хранить в экземпляре Accomplice, но "и так сойдет" postAccomplice += (i == 0 ? "" : "\n") + sqlWorker.selectData("SELECT description FROM Post WHERE idPost = " + accompliceList.values[i].IdPost).Rows[0][0].ToString(); } } if (c != null) { oldId = c.Id; } c = new Crime( cbOrgan.SelectedItem == null?0:(cbOrgan.SelectedItem as KeyValue).Key, cbClause.SelectedItem == null?0:(cbClause.SelectedItem as Clause).Id, cbMilitaryUnit.SelectedItem == null ? 0 : (cbMilitaryUnit.SelectedItem as MilitaryUnit).Id, txDateRegistration.Text, txDateInstitution.Text, txDateCommit.Text, txStory.Text, txDamage.Text, txDateVerdict.Text, txVerdict.Text, txnumberCase.Text, postAccomplice, accomplices, cbClause.SelectedItem == null ? "" : (cbClause.SelectedItem as Clause).ToString(), cbClause.SelectedItem == null ? "" : (cbClause.SelectedItem as Clause).Number, chkIsRegistred.IsChecked == true ? 1:0, cbMilitaryUnit.SelectedItem == null ? "" : (cbMilitaryUnit.SelectedItem as MilitaryUnit).ShortName); } else if (rbCrime.IsChecked == false && cbMilitaryUnit.SelectedItem != null && txStory.Text != "" && txDateRegistration.Text != "") { //введен минимум для происшествия string accomplices = "", postAccomplice = ""; for (int i = 0; i < accompliceList.values.Count; i++) { accomplices += (i == 0 ? "" : "\n") + accompliceList.values[i].Rank + " " + accompliceList.values[i].ShortName; //в идеале надо хранить в экземпляре Accomplice, но "и так сойдет" postAccomplice += (i == 0 ? "" : "\n") + sqlWorker.selectData("SELECT description FROM Post WHERE idPost = " + accompliceList.values[i].IdPost).Rows[0][0].ToString(); } if (c != null) { oldId = c.Id; } c = new Crime( cbOrgan.SelectedItem == null ? 0 : (cbOrgan.SelectedItem as KeyValue).Key, cbClause.SelectedItem == null ? 0 : (cbClause.SelectedItem as Clause).Id, cbMilitaryUnit.SelectedItem == null ? 0 : (cbMilitaryUnit.SelectedItem as MilitaryUnit).Id, txDateRegistration.Text, txDateInstitution.Text, txDateCommit.Text, txStory.Text, txDamage.Text, txDateVerdict.Text, txVerdict.Text, txnumberCase.Text, postAccomplice, accomplices, cbClause.SelectedItem == null ? "" : (cbClause.SelectedItem as Clause).ToString(), cbClause.SelectedItem == null ? "" : (cbClause.SelectedItem as Clause).Number, chkIsRegistred.IsChecked == true ? 1 : 0, cbMilitaryUnit.SelectedItem == null ? "" : (cbMilitaryUnit.SelectedItem as MilitaryUnit).ShortName); } else { MessageBox.Show("Не все обязательные поля заполнены!"); return; } //похоже единственный случай когда изменения в бд нужно производить не из главного окна //т.к. нужно вносить изменения в 2 дополнительные таблицы - Portaking и InCategory c.Id = newId; if (!isEditing && sqlWorker.addCrime(c, accompliceList, categoryList)) { this.Close(); } else if (isEditing && sqlWorker.updateCrime(c, accompliceList, categoryList, oldId)) { this.Close(); } else { MessageBox.Show("Ошибка при добавлении преступления в базу данных"); } }
internal static ObservableCollection <Crime> getCrimes(SqliteWorker sqlWorker, string leftDateRange = "", string rightDateRange = "9999.99.99", int idAccomplice = -1) { ObservableCollection <Crime> list = new ObservableCollection <Crime>(); DataTable tableCrimes = sqlWorker.selectData("SELECT Cl.point, Cl.part, Cl.number, Cl.description, C.*, M.shortName FROM Crime C " + "LEFT JOIN Clause Cl ON C.idClause = Cl.idClause " + "INNER JOIN MilitaryUnit M ON M.idMilitaryUnit = C.idMilitaryUnit " + (idAccomplice == -1?"":"INNER JOIN Portaking P ON P.idCrime = C.idCrime AND P.idAccomplice = " + idAccomplice + " ") + "WHERE C.dateRegistration BETWEEN '" + leftDateRange + "' AND '" + rightDateRange + "' " + "ORDER BY C.dateRegistration, C.dateInstitution, C.dateCommit, C.story;"); if (tableCrimes.Rows.Count > 0) { try { for (int i = 0; i < tableCrimes.Rows.Count; i++) { DataTable tableAccomplice = sqlWorker.selectData("SELECT R.shortName, A.shortName, A.isContrakt, P.description, R.priority FROM Accomplice A " + "INNER JOIN Portaking P ON P.idAccomplice = A.idAccomplice " + "INNER JOIN Rank R ON A.idRank = R.idRank " + "INNER JOIN Post P ON P.idPost = A.idPost " + "WHERE P.idCrime = " + Int32.Parse(tableCrimes.Rows[i][4].ToString()) + ";"); string accomplices = "", postAccomplice = ""; for (int j = 0; j < tableAccomplice.Rows.Count; j++) { accomplices += (j == 0 ? "" : "\n") + tableAccomplice.Rows[j][0] + (tableAccomplice.Rows[j][2].ToString() == "1" && Int32.Parse(tableAccomplice.Rows[j][4].ToString()) < 60 ? " к/с " : " ") + tableAccomplice.Rows[j][1]; postAccomplice += (j == 0 ? "" : "\n") + tableAccomplice.Rows[j][3]; } Crime c = new Crime( tableCrimes.Rows[i][5].ToString() == "" ? 0 : Int32.Parse(tableCrimes.Rows[i][5].ToString()), tableCrimes.Rows[i][6].ToString() == "" ? 0 : Int32.Parse(tableCrimes.Rows[i][6].ToString()), tableCrimes.Rows[i][7].ToString() == "" ? 0 : Int32.Parse(tableCrimes.Rows[i][7].ToString()), tableCrimes.Rows[i][8].ToString(), tableCrimes.Rows[i][9].ToString(), tableCrimes.Rows[i][10].ToString(), tableCrimes.Rows[i][11].ToString(), tableCrimes.Rows[i][12].ToString(), tableCrimes.Rows[i][13].ToString(), tableCrimes.Rows[i][14].ToString(), tableCrimes.Rows[i][15].ToString(), postAccomplice, accomplices, (tableCrimes.Rows[i][0].ToString() == "" ? "" : "п.'" + tableCrimes.Rows[i][0].ToString() + "' ") + (tableCrimes.Rows[i][1].ToString() == "" ? "" : "ч." + tableCrimes.Rows[i][1].ToString() + " ") + (tableCrimes.Rows[i][2].ToString() == "" ? "" : "ст." + tableCrimes.Rows[i][2].ToString() + " ") + (tableCrimes.Rows[i][3].ToString() == "" ? "" : " (" + tableCrimes.Rows[i][3].ToString() + ")"), (tableCrimes.Rows[i][2].ToString() == "" ? "" : tableCrimes.Rows[i][2].ToString()) + //статья УК РФ (tableCrimes.Rows[i][1].ToString() == "" ? "" : "," + tableCrimes.Rows[i][1].ToString()), Int32.Parse(tableCrimes.Rows[i][16].ToString()), tableCrimes.Rows[i][17].ToString()); //часть статьи c.Id = tableCrimes.Rows[i][4].ToString() == "" ? 0 : Int32.Parse(tableCrimes.Rows[i][4].ToString()); list.Add(c); } } catch (Exception ex) { throw new Exception("Некорректные данные в БД:\n" + ex.Message); } } return(list); }