//Генерация перечня дат, по которым есть нормы 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 GenerateComboBoxMp(string sql) { MsAccess acs = new MsAccess(); ComboBoxMp.ItemsSource = acs.CreateDataTable(sql).DefaultView; }
//Обновление DataGrid private void GenerateDataGridReq(string sql) { MsAccess acs = new MsAccess(); DataGridRequests.DataContext = acs.CreateDataTable(sql); }
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); }