private void ButtonAdd_Click(object sender, RoutedEventArgs e)
{
MsAccess acs = new MsAccess();
string result = "0";
string sql = $@"
Select
nrm.nrm_id
From
nrm
Where
nrm.nrm_dt = {DatePickerNorm.SelectedDate:#M-d-yyyy#}
";
result = acs.GetValueSql(sql);
if (result != "0")
{
MessageBox.Show($@"Данные на {DatePickerNorm.SelectedDate:#M-d-yyyy#} уже есть в системе");
return;
}
else
{
sql =
$@"INSERT INTO nrm (nrm_dt, stf_tn, nrm_hr, nrm_scr)
SELECT {DatePickerNorm.SelectedDate:#M-d-yyyy#}, stf_tn, nrm_hr, nrm_scr
FROM nrm WHERE nrm_dt = (SELECT MAX(nrm_dt) FROM nrm WHERE nrm_dt <= {
DatePickerNorm.SelectedDate
:#M-d-yyyy#})";
acs.GetValueSql(sql);
GenerateNormsDep();
//Запись лога
acs.Log(DatePickerNorm.SelectedDate.Value.ToShortDateString(), "Создание");
}
}
//Подсчет кол-ва
private string Сounting(DateTime data, string mp, string sql)
{
int param1 = 0;
DateTime param2 = DateTime.Now;
try
{
if (mp != null)
{
param1 = int.Parse(mp);
}
param2 = data;
}
catch (Exception e)
{
MessageBox.Show("Ошибка преобразования параметров при подсчете заявок");
Environment.Exit(0);
}
MsAccess acs = new MsAccess();
OleDbCommand cmd = new OleDbCommand(sql);
if (mp != null)
{
cmd.Parameters.Add("tn", OleDbType.Integer, 10, "tn");
cmd.Parameters["tn"].Value = param1;
}
cmd.Parameters.Add("Data", OleDbType.Date, 4, "Data");
cmd.Parameters["Data"].Value = param2;
return(acs.GetValueCommand(cmd));
}
//Генерация перечня дат, по которым есть нормы
private void GenerateComboBoxDate(string sql)
{
MsAccess acs = new MsAccess();
ComboBoxDate.ItemsSource = acs.CreateDataTable(sql).DefaultView;
ComboBoxDate.SelectedIndex = 0;
ComboBoxDate.ItemStringFormat = "dd.MM.yyyy";
}
private void GenerateNormsMp()
{
//Удаляю предыдущую генерацию StackPanel. Все кроме шапки
while (StackPanelMp.Children.Count != 1)
{
StackPanelMp.Children.RemoveAt(StackPanelMp.Children.Count - 1);
}
MsAccess acs = new MsAccess();
DataTable dt = new DataTable();
string sql = $@"
Select
mng.[mng_fln],
(Select
Sum(cor.[cor_scr]) As Итого
From
((flw Inner Join
ord On ord.[ord_id] = flw.[ord_id]) Inner Join
app On app.[ord_id] = ord.[ord_id]) Inner Join
cor On cor.[cor_id] = app.[cor_id]
Where
(ord.[ord_dt]) = {DatePickerNorm.SelectedDate:#M-d-yyyy#} And
flw.[mng_tn] = mng.[mng_tn]) As Итого,
(Select
Count(ord.[ord_id]) As Итого
From
flw Inner Join
ord On ord.[ord_id] = flw.[ord_id]
Where
(ord.[ord_dt]) = {DatePickerNorm.SelectedDate:#M-d-yyyy#} And
flw.[mng_tn] = mng.[mng_tn]) As [Кол-во]
From
mng";
dt = acs.CreateDataTable(sql);
//Перебираю строки в DataTable
int i = 0;
int[] arraySum = new int[3];
foreach (DataRow row in dt.Rows)
{
CreatePivotTable(StackPanelMp, arraySum, row);
}
CreatePivotTable(StackPanelMp, arraySum);
}
private void ButtonCopy_Click(object sender, RoutedEventArgs e)
{
if (ComboBoxMp.SelectedIndex == -1)
{
MessageBox.Show("Выберите менедежара потока");
return;
}
DataRowView dataRow = (DataRowView)DataGridRequests.SelectedItem;
string ordNum;
try
{
ordNum = dataRow.Row.ItemArray[1].ToString();
}
catch (NullReferenceException)
{
return;
}
string sql = $@"SELECT cor.cor_id FROM ord INNER JOIN (cor INNER JOIN app ON cor.cor_id = app.cor_id) ON ord.ord_id = app.ord_id WHERE (((ord.ord_num)='{ordNum}'));";
MsAccess acs = new MsAccess();
DataTable dt = acs.CreateDataTable(sql);
//Преобразую DataTable в IList<int>
IList <int> list = dt.AsEnumerable()
.Select(r => r.Field <int>("cor_id"))
.ToList();
Order order = new Order
{
DateOrder = DateTime.Parse(ComboBoxDate.SelectedValue.ToString()),
MngTn = ComboBoxMp.SelectedValue.ToString(),
//Для расстановки true в checkboxes содержания заявки
ContentOrders = list
};
order.ShowDialog();
DataGridReqReload();
CountRequestsMp();
//Запись лога
acs.Log(ordNum, "Копирование");
}
private void editButton_Click(object sender, RoutedEventArgs e)
{
string dep = ((TextBlock)((StackPanel)((Button)sender).Parent).Children[0]).Text;
MsAccess acs = new MsAccess();
string normDbPath = Environment.CurrentDirectory + @"\Resources\Norm.accdb";
//Uri uri = new Uri(@"pack://*****:*****@"INSERT INTO nrm (nrm_dt, stf_tn, stf_fln, pos_nm, nrm_hr, nrm_scr) IN '{normDbPath}'
SELECT nrm.nrm_dt, nrm.stf_tn, stf.stf_fln, pos.pos_nm, nrm.nrm_hr, nrm.nrm_scr
FROM pos INNER JOIN ((dep INNER JOIN stf ON dep.dep_id = stf.dep_id) INNER JOIN nrm ON stf.stf_tn = nrm.stf_tn) ON pos.pos_id = stf.pos_id
WHERE dep.dep_mn = ""{dep}"" AND nrm.nrm_dt = {DatePickerNorm.SelectedDate:#M-d-yyyy#} ; ";
acs.GetValueSql(sql);
acs.PathToBase = normDbPath;
acs.AccessFormsOpen("nrm");
//Подсвечиваю отдел в котором были изменения
((TextBlock)((StackPanel)((Button)sender).Parent).Children[0]).Background = Brushes.OrangeRed;
ButtonUpdate.Visibility = Visibility.Visible;
ButtonReset.Visibility = Visibility.Visible;
_dep = dep;
}
//Генерация списка менеджеров для комбобокс
private void GenerateComboBoxMp(string sql)
{
MsAccess acs = new MsAccess();
ComboBoxMp.ItemsSource = acs.CreateDataTable(sql).DefaultView;
}
private void ButtonDel_Click(object sender, RoutedEventArgs e)
{
//Удаление обращения
MsAccess acs = new MsAccess();
DataRowView dataRow = (DataRowView)DataGridRequests.SelectedItem;
//Получаю номер заявки
string ordNum = dataRow.Row.ItemArray[1].ToString();
//Получаю дату заявки
DateTime ordDt = (DateTime)dataRow.Row.ItemArray[2];
//Формирую предупреждающее сообщение
MessageBoxResult msg = MessageBox.Show(
messageBoxText: $"Будет удалено обращение {ordNum} от {ordDt:MM.dd.yyyy}\nПродолжить?"
, caption: "Внимание"
, button: MessageBoxButton.YesNo);
if (msg == MessageBoxResult.No)
{
return;
}
//Удаляю заявку из трех таблиц
OleDbConnection connection = acs.CreateConnection();
OleDbTransaction transaction = connection.BeginTransaction();
OleDbCommand command = connection.CreateCommand();
command.CommandText =
$@"DELETE flw.* FROM ord INNER JOIN flw ON ord.ord_id = flw.ord_id WHERE ord.ord_num=""{ordNum}"";";
command.Transaction = transaction;
int ordDelRows = 0;
//Удаляю из flw
try
{
ordDelRows = command.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show("Не могу удалить обращение из таблицы flw");
transaction.Rollback();
return;
}
command.CommandText =
$@"DELETE app.* FROM ord INNER JOIN app ON ord.ord_id = app.ord_id WHERE ord.ord_num=""{ordNum}"";";
command.Transaction = transaction;
//Удаляю из app
try
{
ordDelRows = command.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show("Не могу удалить содержание обращения из таблицы app");
transaction.Rollback();
return;
}
command.CommandText =
$@"DELETE ord.* FROM ord WHERE ord.ord_num=""{ordNum}"";";
command.Transaction = transaction;
//Удаляю из ord
try
{
ordDelRows = command.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show("Не могу удалить обращение из таблицы ord");
transaction.Rollback();
return;
}
//Подтверждаю транзакцию если все в порядке
transaction.Commit();
connection.Close();
//Обновление DataGrid
DataGridReqReload();
//Подсчет кол-ва обращений
CountRequestsMp();
//Запись лога
acs.Log(ordNum, "Удаление");
}
//Обновление DataGrid
private void GenerateDataGridReq(string sql)
{
MsAccess acs = new MsAccess();
DataGridRequests.DataContext = acs.CreateDataTable(sql);
}
private void ButtonUpdate_Click(object sender, RoutedEventArgs e)
{
MsAccess acs = new MsAccess();
string normDbPath = Environment.CurrentDirectory + @"\Resources\Norm.accdb";
OleDbConnection connection = acs.CreateConnection();
OleDbTransaction transaction = connection.BeginTransaction();
OleDbCommand command = connection.CreateCommand();
//Удаление норм из родительской базы
command.CommandText =
$@"DELETE FROM nrm WHERE nrm_dt = {DatePickerNorm.SelectedDate:#M-d-yyyy#} AND stf_tn IN (SELECT stf_tn FROM nrm IN '{normDbPath}')";
command.Transaction = transaction;
int deteleRows = 0;
try
{
deteleRows = command.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show("Ошибка удаления старых данных. Проверьте данные и повторите попытку.");
connection.Close();
return;
}
//Вставка норм в родительскую базу
if (deteleRows > 0)
{
command.CommandText =
$@"INSERT INTO nrm (nrm_dt, stf_tn, nrm_hr, nrm_scr) SELECT nrm.nrm_dt, nrm.stf_tn, nrm.nrm_hr, nrm.nrm_scr FROM nrm IN '{normDbPath}'";
command.Transaction = transaction;
}
int insertRows = 0;
try
{
insertRows = command.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show("Ошибка вставки. Проверьте данные и повторите попытку.");
connection.Close();
return;
}
//Удаление норм в локальной базе данных
if (insertRows > 0)
{
command.CommandText =
$@"DELETE FROM nrm IN '{normDbPath}'";
command.Transaction = transaction;
}
int deleteLocalRows = 0;
try
{
deleteLocalRows = command.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show("Ошибка удаления данных из локальной базы. Проверьте данные и повторите попытку.");
connection.Close();
return;
}
if (deleteLocalRows == 0)
{
transaction.Rollback();
connection.Close();
MessageBox.Show("Ошибка редактирования данных");
return;
}
transaction.Commit();
connection.Close();
MessageBox.Show("Данные изменены");
ButtonUpdate.Visibility = Visibility.Collapsed;
ButtonReset.Visibility = Visibility.Collapsed;
GenerateNormsDep();
acs.Log(DatePickerNorm.SelectedDate.Value.ToShortDateString() + "_" + _dep, "Изменение");
}
private void GenerateNormsDep()
{
//Удаляю предыдущую генерацию StackPanel. Все кроме шапки
while (StackPanelDep.Children.Count != 1)
{
StackPanelDep.Children.RemoveAt(StackPanelDep.Children.Count - 1);
}
MsAccess acs = new MsAccess();
DataTable dt = new DataTable();
//{DatePickerNorm.SelectedDate:#M-d-yyyy#}
string sql = $@"
Select
dep.[dep_mn] As Отдел,
Sum(nrm.[nrm_scr]) As Норма,
(Select
Sum(cor2.[cor_scr]) As [Sum_cor_scr]
From
((((stf stf2 Inner Join
flw flw2 On flw2.[stf_tn] = stf2.[stf_tn]) Inner Join
dep dep2 On dep2.[dep_id] = stf2.[dep_id]) Inner Join
ord ord2 On ord2.[ord_id] = flw2.[ord_id]) Inner Join
app app2 On ord2.[ord_id] = app2.[ord_id]) Inner Join
cor cor2 On cor2.[cor_id] = app2.[cor_id]
Where
dep2.[dep_id] = dep.[dep_id] And
ord2.[ord_dt] = {DatePickerNorm.SelectedDate:#M-d-yyyy#}) As Итого,
(Select
Count(flw1.[ord_id])
From
(((flw flw1 Inner Join
stf stf1 On stf1.[stf_tn] = flw1.[stf_tn]) Inner Join
dep dep1 On dep1.[dep_id] = stf1.[dep_id]) Inner Join
ord ord1 On ord1.[ord_id] = flw1.[ord_id])
Where
dep1.[dep_id] = dep.[dep_id] And
ord1.[ord_dt] = {DatePickerNorm.SelectedDate:#M-d-yyyy#}) As [Кол-во],
Round(100 * (Select
Sum(cor2.[cor_scr]) As [Sum_cor_scr]
From
((((stf stf2 Inner Join
flw flw2 On flw2.[stf_tn] = stf2.[stf_tn]) Inner Join
dep dep2 On dep2.[dep_id] = stf2.[dep_id]) Inner Join
ord ord2 On ord2.[ord_id] = flw2.[ord_id]) Inner Join
app app2 On ord2.[ord_id] = app2.[ord_id]) Inner Join
cor cor2 On cor2.[cor_id] = app2.[cor_id]
Where
dep2.[dep_id] = dep.[dep_id] And
ord2.[ord_dt] = {DatePickerNorm.SelectedDate:#M-d-yyyy#}) / Sum(nrm.[nrm_scr]), 0) As Выполнение
From
((((((stf Inner Join
nrm On nrm.[stf_tn] = stf.[stf_tn]) Inner Join
dep On dep.[dep_id] = stf.[dep_id])))))
Where
nrm.[nrm_dt] = {DatePickerNorm.SelectedDate:#M-d-yyyy#}
Group By
dep.[dep_mn], nrm.[nrm_dt], dep.[dep_id]
";
dt = acs.CreateDataTable(sql);
//Перебираю строки в DataTable
int i = 0;
int[] arraySum = new int[5];
foreach (DataRow row in dt.Rows)
{
CreatePivotTable(StackPanelDep, arraySum, row);
}
CreatePivotTable(StackPanelDep, arraySum);
}
