//Генерация перечня дат, по которым есть нормы
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);
}