/// <summary>
/// Кнопка для виклику вікна EmployersDT_Set
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void MenuItem_Click_EmpSetTimes(object sender, RoutedEventArgs e)//Set employers inidividual times for work
{
Set_Emp_times = new EmployersDT_Set();
Set_Emp_times.Show();
Set_Emp_times.initialALL();
}
/// <summary>
/// Кнопка виконання
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Query_Click(object sender, RoutedEventArgs e)
{
try
{
dt1 = DateTime.Parse(Start_date.Text);
dt2 = DateTime.Parse(End_date.Text);
}
catch { MessageBox.Show("Введіть дату"); }
try { delta = dt2 - dt1; }
catch { MessageBox.Show("Дата введена невірно"); }
int count = delta.Days;
List <DateTime> allDates = new List <DateTime>();
for (int i = 0; i <= count; i++)
{
allDates.Add(new DateTime(dt1.Year, dt1.Month, dt1.Day).AddDays(i));
}
listread = new List <DataEmployers>(count);
Dictionary <int, List <string> > listread1 = new Dictionary <int, List <string> >(count);
List <string> tmp_list;
//Для всіх з врахуванням повторного входу
if (ChoiseQuery.SelectedIndex == 0 && Check_redirect.IsChecked == true) //when redirect check
{
for (int i = 0; i <= count; i++) //card id to list
{
tmp_list = new List <string>();
NpgsqlCommand cmd_tmp1 = new NpgsqlCommand();
cmd_tmp1.CommandText = @"select distinct card from events where etimesirius>='" + allDates[i].ToShortDateString() + " 00:00' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and direction=" + MainWindow.direction + " and worker<>0";
cmd_tmp1.Connection = con;
NpgsqlDataReader reader1;
reader1 = cmd_tmp1.ExecuteReader();
while (reader1.Read())
{
tmp_list.Add(reader1[0].ToString());
}
listread1.Add(i, tmp_list);
cmd_tmp1.Dispose();
reader1.Close();
}
for (int i = 0; i <= count; i++)//main query
{
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.CommandText = @"select F.last_name, F.first_name, F.middle_name, F.card, F.worker, departments.id, F.btm, F.table_no, departments.name from ( select E.card, E.worker, E.mtm, E.btm, workers.last_name, workers.first_name, workers.middle_name, workers.table_no, workers.department from ( select C.card, C.worker, C.mtm, D.btm from ( select A.card, A.worker, B.mtm from (select distinct card, worker from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker)A LEFT JOIN (select card, worker, min(etimeserver) as MTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius<='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + "and worker<>0 group by card, worker )B ON A.card = B.card AND A.worker = B.worker) C LEFT JOIN (select card, worker, min(etimeserver) as BTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker )D ON C.card = D.card AND C.worker = D.worker)" + "E LEFT JOIN workers ON E.worker = workers.id) F LEFT JOIN departments ON F.department = departments.id order by btm";
cmd.Connection = con;
NpgsqlDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (string s in listread1[i])
{
if (s == reader[3].ToString())
{
goto breakout;
}
}
listread.Add(new DataEmployers(reader[1].ToString(), reader[0].ToString(), reader[2].ToString(), /**/ reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(), reader[8].ToString()));
breakout:
continue;
}
cmd.Dispose();
reader.Close();
}
DataGridWindow = new DataGridWindow(listread);
}
//Для всіх без врахування повторного входу
if (ChoiseQuery.SelectedIndex == 0 && Check_redirect.IsChecked == false)//не враховується повторний вхід для всіх
{
for (int i = 0; i <= count; i++)
{
NpgsqlCommand cmd = new NpgsqlCommand
{
CommandText = @"select F.last_name, F.first_name, F.middle_name, F.card, F.worker, departments.id, F.btm, F.table_no, departments.name from ( select E.card, E.worker, E.mtm, E.btm, workers.last_name, workers.first_name, workers.middle_name, workers.table_no, workers.department from ( select C.card, C.worker, C.mtm, D.btm from ( select A.card, A.worker, B.mtm from (select distinct card, worker from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker)A LEFT JOIN (select card, worker, min(etimeserver) as MTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius<='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + "and worker<>0 group by card, worker )B ON A.card = B.card AND A.worker = B.worker) C LEFT JOIN (select card, worker, min(etimeserver) as BTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker )D ON C.card = D.card AND C.worker = D.worker)" + "E LEFT JOIN workers ON E.worker = workers.id) F LEFT JOIN departments ON F.department = departments.id order by btm",
Connection = con
};
NpgsqlDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
listread.Add(new DataEmployers(reader[1].ToString(), reader[0].ToString(), reader[2].ToString(), /**/ reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(), reader[8].ToString()));
}
cmd.Dispose();
reader.Close();
}
DataGridWindow = new DataGridWindow(listread);
}
//Для департаменту з врахуванням повторного входу
if (ChoiseQuery.SelectedIndex == 1 && Check_redirect.IsChecked == true)
{ //card id to list
for (int i = 0; i <= count; i++)
{
tmp_list = new List <string>();
NpgsqlCommand cmd_tmp1 = new NpgsqlCommand
{
CommandText = @"select distinct card from events where etimesirius>='" + allDates[i].ToShortDateString() + " 00:00' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and direction=" + MainWindow.direction + " and worker<>0",
Connection = con
};//query for departments
NpgsqlDataReader reader1;
reader1 = cmd_tmp1.ExecuteReader();
while (reader1.Read())
{
tmp_list.Add(reader1[0].ToString());
}
listread1.Add(i, tmp_list);
cmd_tmp1.Dispose();
reader1.Close();
}
for (int i = 0; i <= count; i++)
{
NpgsqlCommand cmd1 = new NpgsqlCommand();
cmd1.CommandText = @"select F.last_name, F.first_name, F.middle_name, F.card, F.worker, departments.id, F.btm, F.table_no, departments.name from ( select E.card, E.worker, E.mtm, E.btm, workers.last_name, workers.first_name, workers.middle_name, workers.table_no, workers.department from ( select C.card, C.worker, C.mtm, D.btm from ( select A.card, A.worker, B.mtm from (select distinct card, worker from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker)A LEFT JOIN (select card, worker, min(etimeserver) as MTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius<='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + "and worker<>0 group by card, worker )B ON A.card = B.card AND A.worker = B.worker) C LEFT JOIN (select card, worker, min(etimeserver) as BTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker )D ON C.card = D.card AND C.worker = D.worker)" + "E LEFT JOIN workers ON E.worker = workers.id) F LEFT JOIN departments ON F.department = departments.id where departments.name like '" + DepartamentParse(List_box_intellishell.SelectedItem.ToString()) + "' order by btm";
cmd1.Connection = con;
NpgsqlDataReader reader;
reader = cmd1.ExecuteReader();
while (reader.Read())
{
foreach (string s in listread1[i])
{
if (s == reader[3].ToString())
{
goto breakout1;
}
}
listread.Add(new DataEmployers(reader[1].ToString(), reader[0].ToString(), reader[2].ToString(), /**/ reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(), reader[8].ToString()));
breakout1:
continue;
}
cmd1.Dispose();
reader.Close();
}
DataGridWindow = new DataGridWindow(listread);
}
//Для департаменту без повторного входу
if (ChoiseQuery.SelectedIndex == 1 && Check_redirect.IsChecked == false)
{
for (int i = 0; i <= count; i++)
{
NpgsqlCommand cmd1 = new NpgsqlCommand
{
CommandText = @"select F.last_name, F.first_name, F.middle_name, F.card, F.worker, departments.id, F.btm, F.table_no, departments.name from ( select E.card, E.worker, E.mtm, E.btm, workers.last_name, workers.first_name, workers.middle_name, workers.table_no, workers.department from ( select C.card, C.worker, C.mtm, D.btm from ( select A.card, A.worker, B.mtm from (select distinct card, worker from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker)A LEFT JOIN (select card, worker, min(etimeserver) as MTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius<='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + "and worker<>0 group by card, worker )B ON A.card = B.card AND A.worker = B.worker) C LEFT JOIN (select card, worker, min(etimeserver) as BTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker )D ON C.card = D.card AND C.worker = D.worker)" + "E LEFT JOIN workers ON E.worker = workers.id) F LEFT JOIN departments ON F.department = departments.id where departments.name like '" + DepartamentParse(List_box_intellishell.SelectedItem.ToString()) + "' order by btm",
Connection = con
};
NpgsqlDataReader reader;
reader = cmd1.ExecuteReader();
while (reader.Read())
{
listread.Add(new DataEmployers(reader[1].ToString(), reader[0].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(), reader[8].ToString()));
}
cmd1.Dispose();
reader.Close();
}
DataGridWindow = new DataGridWindow(listread);
}
//Для одного працівника не всі події
if (ChoiseQuery.SelectedIndex == 2 && Check_redirect.IsChecked == true && Check_Events.IsChecked == false)
{
for (int i = 0; i <= count; i++)
{
tmp_list = new List <string>();
NpgsqlCommand cmd_tmp1 = new NpgsqlCommand();
cmd_tmp1.CommandText = @"select F.last_name, F.first_name, F.middle_name, F.card, F.worker, departments.id, F.btm, F.table_no, departments.name from ( select E.card, E.worker, E.mtm, E.btm, workers.last_name, workers.first_name, workers.middle_name, workers.table_no, workers.department from ( select C.card, C.worker, C.mtm, D.btm from ( select A.card, A.worker, B.mtm from (select distinct card, worker from events where etimesirius>='" + allDates[i].ToShortDateString() + " 00:00' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker)A LEFT JOIN (select card, worker, min(etimeserver) as MTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " 00:00 ' and etimesirius<='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and direction=" + MainWindow.direction + "and worker<>0 group by card, worker )B ON A.card = B.card AND A.worker = B.worker) C LEFT JOIN (select card, worker, min(etimeserver) as BTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " 00:00' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker )D ON C.card = D.card AND C.worker = D.worker)" + "E LEFT JOIN workers ON E.worker = workers.id where workers.table_no='" + List_box_intellishell.SelectedItem.ToString().Split(' ').Last() + "') F LEFT JOIN departments ON F.department = departments.id order by btm";
cmd_tmp1.Connection = con;
NpgsqlDataReader reader1;
reader1 = cmd_tmp1.ExecuteReader();
while (reader1.Read())
{
tmp_list.Add(reader1[3].ToString());
}
listread1.Add(i, tmp_list);
cmd_tmp1.Dispose();
reader1.Close();
}
for (int i = 0; i <= count; i++)
{
NpgsqlCommand cmd2 = new NpgsqlCommand();
cmd2.CommandText = @"select F.last_name, F.first_name, F.middle_name, F.card, F.worker, departments.id, F.btm, F.table_no, departments.name from ( select E.card, E.worker, E.mtm, E.btm, workers.last_name, workers.first_name, workers.middle_name, workers.table_no, workers.department from ( select C.card, C.worker, C.mtm, D.btm from ( select A.card, A.worker, B.mtm from (select distinct card, worker from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker)A LEFT JOIN (select card, worker, min(etimeserver) as MTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius<='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + "and worker<>0 group by card, worker )B ON A.card = B.card AND A.worker = B.worker) C LEFT JOIN (select card, worker, min(etimeserver) as BTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + Time_start.Text + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + Time_end.Text + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker )D ON C.card = D.card AND C.worker = D.worker)" + "E LEFT JOIN workers ON E.worker = workers.id where workers.table_no='" + List_box_intellishell.SelectedItem.ToString().Split(' ').Last() + "') F LEFT JOIN departments ON F.department = departments.id order by btm";
cmd2.Connection = con;
NpgsqlDataReader reader;
reader = cmd2.ExecuteReader();
while (reader.Read())
{
foreach (string s in listread1[i])
{
if (s == reader[3].ToString())
{
goto breakout3;
}
}
listread.Add(new DataEmployers(reader[1].ToString(), reader[0].ToString(), reader[2].ToString(), /**/ reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(), reader[8].ToString()));
breakout3:
continue;
}
cmd2.Dispose();
reader.Close();
}
DataGridWindow = new DataGridWindow(listread);
}
//для одного працівника відображення усіх подій
if (ChoiseQuery.SelectedIndex == 2 && Check_redirect.IsChecked == false && Check_Events.IsChecked == true) //checked all events
{
NpgsqlCommand cmd2 = new NpgsqlCommand();
cmd2.CommandText = @"select workers.last_name, workers.first_name, workers.middle_name, events.etimeserver ,events.direction , events.controler, departments.name from events,workers, departments where events.card=workers.card and departments.id=workers.department and workers.table_no=" + List_box_intellishell.SelectedItem.ToString().Split(' ').Last() + " and events.etimeserver>='" + Start_date.Text + " 00:00'and events.etimeserver<='" + End_date.Text + " 23:00'order by events.etimeserver";
cmd2.Connection = con;
NpgsqlDataReader reader;
reader = cmd2.ExecuteReader();
while (reader.Read())
{
listread.Add(new DataEmployers(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[4].ToString() == "0" ? "Зайшов" : "Вийшов", reader[5].ToString() == "7" ? "На правий турнікет" : "На лівий турнікет", "", reader[3].ToString(), List_box_intellishell.SelectedItem.ToString().Split(' ').Last(), reader[6].ToString()));
}
cmd2.Dispose();
reader.Close();
DataGridWindow = new DataGridWindow(listread);
}
if (ChoiseQuery.SelectedIndex == 2 && Check_redirect.IsChecked == false && Check_Events.IsChecked == false)
{
MessageBox.Show("Вибір для працівника з даними параметрами неможливий");
}
if (ChoiseQuery.SelectedIndex == 2 && Check_redirect.IsChecked == true && Check_Events.IsChecked == true)
{
MessageBox.Show("Вибір для працівника з даними параметрами неможливий");
}
//Для працівників з окремим графіком
if (ChoiseQuery.SelectedIndex == 3)
{
List <WorkerIndivid> workers = new List <WorkerIndivid>();
Set_Emp_times = new EmployersDT_Set();
Set_Emp_times.initialALL(); //зчитуємо з файлика збережених працівників
workers = Set_Emp_times.list_workers;
foreach (var lst in workers)
{
listread1 = new Dictionary <int, List <string> >(count);
for (int i = 0; i <= count; i++)//перевіряємо повторний вхід
{
tmp_list = new List <string>();
NpgsqlCommand cmd_tmp1 = new NpgsqlCommand();
cmd_tmp1.CommandText = @"select F.last_name, F.first_name, F.middle_name, F.card, F.worker, departments.id, F.btm, F.table_no, departments.name from ( select E.card, E.worker, E.mtm, E.btm, workers.last_name, workers.first_name, workers.middle_name, workers.table_no, workers.department from ( select C.card, C.worker, C.mtm, D.btm from ( select A.card, A.worker, B.mtm from (select distinct card, worker from events where etimesirius>='" + allDates[i].ToShortDateString() + " 00:00' and etimesirius <='" + allDates[i].ToShortDateString() + " " + lst.Time_startW + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker)A LEFT JOIN (select card, worker, min(etimeserver) as MTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " 00:00 ' and etimesirius<='" + allDates[i].ToShortDateString() + " " + lst.Time_startW + "' and direction=" + MainWindow.direction + "and worker<>0 group by card, worker )B ON A.card = B.card AND A.worker = B.worker) C LEFT JOIN (select card, worker, min(etimeserver) as BTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " 00:00' and etimesirius <='" + allDates[i].ToShortDateString() + " " + lst.Time_startW + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker )D ON C.card = D.card AND C.worker = D.worker)" + "E LEFT JOIN workers ON E.worker = workers.id where workers.table_no='" + lst.Table_Number + "') F LEFT JOIN departments ON F.department = departments.id order by btm";
cmd_tmp1.Connection = con;
NpgsqlDataReader reader1;
reader1 = cmd_tmp1.ExecuteReader();
while (reader1.Read())
{
tmp_list.Add(reader1[3].ToString());
}
listread1.Add(i, value: tmp_list);
cmd_tmp1.Dispose();
reader1.Close();
}
for (int i = 0; i <= count; i++)
{
NpgsqlCommand cmd2 = new NpgsqlCommand();
cmd2.CommandText = @"select F.last_name, F.first_name, F.middle_name, F.card, F.worker, departments.id, F.btm, F.table_no, departments.name from ( select E.card, E.worker, E.mtm, E.btm, workers.last_name, workers.first_name, workers.middle_name, workers.table_no, workers.department from ( select C.card, C.worker, C.mtm, D.btm from ( select A.card, A.worker, B.mtm from (select distinct card, worker from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + lst.Time_startW + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + lst.Time_endW + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker)A LEFT JOIN (select card, worker, min(etimeserver) as MTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + lst.Time_startW + "' and etimesirius<='" + allDates[i].ToShortDateString() + " " + lst.Time_endW + "' and direction=" + MainWindow.direction + "and worker<>0 group by card, worker )B ON A.card = B.card AND A.worker = B.worker) C LEFT JOIN (select card, worker, min(etimeserver) as BTM from events where etimesirius>='" + allDates[i].ToShortDateString() + " " + lst.Time_startW + "' and etimesirius <='" + allDates[i].ToShortDateString() + " " + lst.Time_endW + "' and direction=" + MainWindow.direction + " and worker<>0 group by card, worker )D ON C.card = D.card AND C.worker = D.worker)" + "E LEFT JOIN workers ON E.worker = workers.id where workers.table_no='" + lst.Table_Number + "') F LEFT JOIN departments ON F.department = departments.id order by btm";
cmd2.Connection = con;
NpgsqlDataReader reader;
reader = cmd2.ExecuteReader();
while (reader.Read())
{
foreach (string s in listread1[i])
{
if (s == reader[3].ToString())
{
goto breakout3;
}
}
listread.Add(new DataEmployers(reader[1].ToString(), reader[0].ToString(), reader[2].ToString(), /**/ reader[3].ToString(), reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(), reader[8].ToString()));
breakout3:
continue;
}
cmd2.Dispose();
reader.Close();
}
}
DataGridWindow = new DataGridWindow(listread);
}
}