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