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);
}
