private void buttonYearReport4_Click(object sender, EventArgs e)
{
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = "";
string[,] arr = { { "Январь-Март", "1", "4" }, { "Апрель-Май", "4", "6" }, { "Июнь", "6", "7" }, { "Июль", "7", "8" }, { "Август", "8", "9" }, { "Сентябрь", "9", "10" }, { "Октябрь-Декабрь", "10", "12" } };
string[,] arr2 = { { "Мастер-класс по глине", "15а" }, { "Мастер-класс по куклам", "16а" } };
bool ini = true;
for (int x = 0; x < (arr.Length / 3); x++)
{
for (int i = 0; i < (arr2.Length / 2); i++)
{
if (ini == false) { sql += " union all "; }
sql += string.Format("select \"{0}\" as month, \"{1}\" as name, (ifnull(sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)),0) + (select ifnull(sum(`plategki`.`Kol_czel`),0) from `plategki` where `plategki`.`N_ekskursii` = \"{2}\" and (`plategki`.`date` >= \"{5}-{3}-01\") and (`plategki`.`date` < \"{5}-{4}-01\")) ) AS `Kol_czel`, ifnull(sum((`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))),0) + (select ifnull(sum((`ekskursii`.`stoimost` * (`plategki`.`Kol_czel`))),0) from (`plategki` join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where `plategki`.`N_ekskursii` = \"{2}\" and (`plategki`.`date` >= \"{5}-{3}-01\") and (`plategki`.`date` < \"{5}-{4}-01\")) AS `stoim` from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where `zhurnal`.`N_ekskursii` = \"{2}\" and (`zhurnal`.`date` >= \"{5}-{3}-01\") and (`zhurnal`.`date` < \"{5}-{4}-01\")", arr[x, 0], arr2[i, 0], arr2[i, 1], arr[x, 1], arr[x, 2], comboBoxYear.Text.ToString().Trim());
ini = false;
}
}
//throw new System.InvalidOperationException(sql);
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewReports.Items.Clear();
listViewReports.Columns.Clear();
listViewReports.Columns.Add("Месяц", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Назв.мастер-класса", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Сумма", -2, HorizontalAlignment.Left);
listViewReports.Columns[0].Width = 150;
listViewReports.Columns[1].Width = 150;
listViewReports.Columns[2].Width = 150;
listViewReports.Columns[3].Width = 150;
queueReports.Clear();
_Reports cv = new _Reports();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["month"].ToString().Trim());
cv.date = dataRow["month"].ToString().Trim();
item1.SubItems.Add(dataRow["name"].ToString().Trim());
cv.date = dataRow["name"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
cv.numer = dataRow["Kol_czel"].ToString().Trim();
item1.SubItems.Add(dataRow["stoim"].ToString().Trim());
cv.cena = dataRow["stoim"].ToString().Trim();
listViewReports.Items.Add(item1);
listViewReports.Items[listViewReports.Items.Count - 1].Tag = dataRow["name"].ToString();
queueReports.Enqueue(cv);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
private void buttonYearReport_Click(object sender, EventArgs e)
{
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = "";
string[,] arr = { { "Январь-Март", "1", "4" }, { "Апрель-Май", "4", "6" }, { "Июнь", "6", "7" }, { "Июль", "7", "8" }, { "Август", "8", "9" }, { "Сентябрь", "9", "10" }, { "Октябрь-Декабрь", "10", "12" } };
bool ini = true;
for (int x = 0; x < (arr.Length / 3); x++)
{
if (ini == false) { sql += " union all "; }
sql += string.Format("select \"{1}\" as month, ((count(0) + (select count(0) from (`plategki` join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where (`plategki`.`date` >= \"{0}-{2}-01\") and (`plategki`.`date` < \"{0}-{3}-01\"))) - (select count(0) from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where (`zhurnal`.`date` >= \"{0}-{2}-01\") and (`zhurnal`.`date` < \"{0}-{3}-01\") and (`ekskursii`.`nazvanie` like 'Входной%'))) as `Kol_eks`, ifnull(sum((`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))),0) AS `stoimost`, (select ifnull(sum((`ekskursii`.`stoimost` * (`plategki`.`Kol_czel`))),0) from (`plategki` join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where (`plategki`.`date` >= \"{0}-{2}-01\") and (`plategki`.`date` < \"{0}-{3}-01\")) as `stoim_bez`, (ifnull(sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)),0) + (select ifnull(sum(`plategki`.`Kol_czel`),0) from `plategki` where (`plategki`.`date` >= \"{0}-{2}-01\") and (`plategki`.`date` < \"{0}-{3}-01\")) ) AS `Kol_czel`, (select ifnull(sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)),0) from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where `ekskursii`.`nazvanie` like \"%(л)\" and (`zhurnal`.`date` >= \"{0}-{2}-01\") and (`zhurnal`.`date` < \"{0}-{3}-01\") ) as lgot_kol, (select ifnull(sum((`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))),0) from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where `ekskursii`.`nazvanie` like \"%(л)\" and (`zhurnal`.`date` >= \"{0}-{2}-01\") and (`zhurnal`.`date` < \"{0}-{3}-01\") )AS `lgot_stoimost` from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where (`zhurnal`.`date` >= \"{0}-{2}-01\") and (`zhurnal`.`date` < \"{0}-{3}-01\")", comboBoxYear.Text.ToString().Trim(), arr[x, 0], arr[x, 1], arr[x, 2]);
ini = false;
}
//throw new System.InvalidOperationException(sql);
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewReports.Items.Clear();
listViewReports.Columns.Clear();
listViewReports.Columns.Add("Месяц", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Количество экскурсий", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Стоимость", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Стоимость без", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Кол-во льготных", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Стоимость льготных", -2, HorizontalAlignment.Left);
listViewReports.Columns[0].Width = 150;
listViewReports.Columns[1].Width = 150;
listViewReports.Columns[2].Width = 150;
listViewReports.Columns[3].Width = 150;
listViewReports.Columns[4].Width = 150;
listViewReports.Columns[5].Width = 150;
listViewReports.Columns[6].Width = 150;
queueReports.Clear();
_Reports cv = new _Reports();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["month"].ToString().Trim());
cv.date = dataRow["month"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_eks"].ToString().Trim());
cv.numer = dataRow["Kol_eks"].ToString().Trim();
item1.SubItems.Add(dataRow["stoimost"].ToString().Trim());
cv.cena= dataRow["stoimost"].ToString().Trim();
item1.SubItems.Add(dataRow["stoim_bez"].ToString().Trim());
cv.kvn = dataRow["stoim_bez"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
cv.kvk = dataRow["Kol_czel"].ToString().Trim();
item1.SubItems.Add(dataRow["lgot_kol"].ToString().Trim());
cv.kol_czel = dataRow["lgot_kol"].ToString().Trim();
item1.SubItems.Add(dataRow["lgot_stoimost"].ToString().Trim());
cv.kol_czel = dataRow["lgot_stoimost"].ToString().Trim();
listViewReports.Items.Add(item1);
listViewReports.Items[listViewReports.Items.Count - 1].Tag = dataRow["month"].ToString();
queueReports.Enqueue(cv);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
private void buttonYearReport3_Click(object sender, EventArgs e)
{
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = "";
string[,] arr = { { "Январь-Март", "1", "4" }, { "Апрель-Май", "4", "6" }, { "Июнь", "6", "7" }, { "Июль", "7", "8" }, { "Август", "8", "9" }, { "Сентябрь", "9", "10" }, { "Октябрь-Декабрь", "10", "12" } };
string[,] arr2 = { { "ЧиП (л)", "Входной билет (ЧиП) (л)" }, { "Др. пр. (л)", "Входной билет (Др.Пр.)(л)" }, { "Комплексная (л)", "0" }, { "Темир (л)", "Входной билет (Темир)(л)" }, { "Казачья усадьба (л)", "Входной билет (каз)(л)" }, { "0", "Входной билет (ЖКВ)(л)" }, { "бесплатн%", "Бесплатный входной билет(л)" } };
bool ini = true;
for (int x = 0; x < (arr.Length / 3); x++)
{
for (int i = 0; i < (arr2.Length / 2); i++)
{
if (ini == false) { sql += " union all "; }
sql += string.Format("SELECT \"{4}\" AS name, \"{0}\" AS eks, (count(0)+(SELECT count(0) FROM plategki JOIN ekskursii ON((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`)) WHERE (ekskursii.nazvanie = \"{0}\") AND (`plategki`.`date` >= \"{5}-{2}-01\") and (`plategki`.`date` < \"{5}-{3}-01\"))) AS count_eks, (ifnull(sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)),0) + (SELECT ifnull(sum(`plategki`.`Kol_czel`),0) FROM (`plategki` JOIN `ekskursii` ON((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) WHERE (ekskursii.nazvanie = \"{0}\") AND (`plategki`.`date` >= \"{5}-{2}-01\") AND (`plategki`.`date` < \"{5}-{3}-01\"))) AS Kol_czel, ((SELECT ifnull(sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)),0) FROM (`zhurnal` JOIN `ekskursii` ON((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) WHERE (ekskursii.nazvanie LIKE \"{1}%\") AND (`zhurnal`.`date` >= \"{5}-{2}-01\") AND (`zhurnal`.`date` < \"{5}-{3}-01\"))+ (SELECT ifnull(sum(`plategki`.`Kol_czel`),0) FROM (`plategki` JOIN `ekskursii` ON((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) WHERE (ekskursii.nazvanie like \"{1}%\") AND (`plategki`.`date` >= \"{5}-{2}-01\") AND (`plategki`.`date` < \"{5}-{3}-01\"))) AS Vhodn, (ifnull(sum((`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))),0) + (SELECT ifnull(sum((`ekskursii`.`stoimost` * (`plategki`.`Kol_czel`))),0) FROM (`plategki` JOIN `ekskursii` ON((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) WHERE (ekskursii.nazvanie = \"{0}\") AND (`plategki`.`date` >= \"{5}-{2}-01\") AND (`plategki`.`date` < \"{5}-{3}-01\"))) AS `stoim_eks`, ((SELECT ifnull(sum((`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))),0) FROM (`zhurnal` JOIN `ekskursii` ON((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) WHERE (ekskursii.nazvanie LIKE \"{1}%\") AND (`zhurnal`.`date` >= \"{5}-{2}-01\") AND (`zhurnal`.`date` < \"{5}-{3}-01\")) + (SELECT ifnull(sum((`ekskursii`.`stoimost` * (`plategki`.`Kol_czel`))),0) FROM (`plategki` JOIN `ekskursii` ON((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) WHERE (ekskursii.nazvanie LIKE \"{1}%\") and (`plategki`.`date` >= \"{5}-{2}-01\") and (`plategki`.`date` < \"{5}-{3}-01\"))) as `stoim_vhod` from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where (ekskursii.nazvanie = \"{0}\") and (`zhurnal`.`date` >= \"{5}-{2}-01\") and (`zhurnal`.`date` < \"{5}-{3}-01\")", arr2[i, 0], arr2[i, 1], arr[x, 1], arr[x, 2], arr[x, 0], comboBoxYear.Text.ToString().Trim());
ini = false;
}
}
//throw new System.InvalidOperationException(sql);
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewReports.Items.Clear();
listViewReports.Columns.Clear();
listViewReports.Columns.Add("Месяц", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Назв.экскурсии", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Кол-во экскурсий", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Кол-во чел по входным", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Стоим экскурсий", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Стоим входных билетов", -2, HorizontalAlignment.Left);
listViewReports.Columns[0].Width = 150;
listViewReports.Columns[1].Width = 150;
listViewReports.Columns[2].Width = 150;
listViewReports.Columns[3].Width = 150;
listViewReports.Columns[4].Width = 150;
listViewReports.Columns[5].Width = 150;
listViewReports.Columns[6].Width = 150;
queueReports.Clear();
_Reports cv = new _Reports();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["name"].ToString().Trim());
cv.date = dataRow["name"].ToString().Trim();
item1.SubItems.Add(dataRow["eks"].ToString().Trim());
cv.date = dataRow["eks"].ToString().Trim();
item1.SubItems.Add(dataRow["count_eks"].ToString().Trim());
cv.numer = dataRow["count_eks"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
cv.cena = dataRow["Kol_czel"].ToString().Trim();
item1.SubItems.Add(dataRow["Vhodn"].ToString().Trim());
cv.kvn = dataRow["Vhodn"].ToString().Trim();
item1.SubItems.Add(dataRow["stoim_eks"].ToString().Trim());
cv.kvk = dataRow["stoim_eks"].ToString().Trim();
item1.SubItems.Add(dataRow["stoim_vhod"].ToString().Trim());
cv.kol_czel = dataRow["stoim_vhod"].ToString().Trim();
listViewReports.Items.Add(item1);
listViewReports.Items[listViewReports.Items.Count - 1].Tag = dataRow["name"].ToString();
queueReports.Enqueue(cv);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
/*отчеты по экскурсиям
*------------------
*-----------------------
*
*
*/
public void refreshReportsEks()
{
listViewReports.Items.Clear();
queueReports.Clear();
listViewReports.Columns.Clear();
listViewReports.Columns.Add("Кол-во экскурсий", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Стоимость", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewReports.Columns[0].Width = 150;
listViewReports.Columns[1].Width = 150;
listViewReports.Columns[2].Width = 150;
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = String.Format("select count(0) AS `count`, sum((`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))) AS `stoimost`,sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)) AS `Kol_czel` from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`ekskursii`.`nazvanie` = '{0}') and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}')) union all select count(0) AS `count`, sum((`ekskursii`.`stoimost` * (`plategki`.`Kol_czel`))) AS `stoimost`, sum(`plategki`.`Kol_czel`) AS `Kol_czel` from (`plategki` join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`ekskursii`.`nazvanie` = '{0}') and (`plategki`.`date` >= '{1}') and (`plategki`.`date` <= '{2}'))", comboBoxEkskursija.Text, DateTime.Parse(dateTimePicker1.Text).Year + "-" + DateTime.Parse(dateTimePicker1.Text).Month + "-" + DateTime.Parse(dateTimePicker1.Text).Day, DateTime.Parse(dateTimePicker2.Text).Year + "-" + DateTime.Parse(dateTimePicker2.Text).Month + "-" + DateTime.Parse(dateTimePicker2.Text).Day);
if (comboBoxEkskursija.SelectedIndex == -1)
sql = String.Format("select count(0) AS `count`,sum((`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))) AS `stoimost`,sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)) AS `Kol_czel` from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`zhurnal`.`FiO` <> '') and (`zhurnal`.`date` >= '{0}') and (`zhurnal`.`date` <= '{1}')) union all select count(0) AS `count`,sum((`ekskursii`.`stoimost` * (`plategki`.`Kol_czel`))) AS `stoimost`,sum(`plategki`.`Kol_czel`) AS `Kol_czel` from (`plategki` join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`plategki`.`FiO` <> ''and (`plategki`.`date` >= '{0}') and (`plategki`.`date` <= '{1}')))", DateTime.Parse(dateTimePicker1.Text).Year + "-" + DateTime.Parse(dateTimePicker1.Text).Month + "-" + DateTime.Parse(dateTimePicker1.Text).Day, DateTime.Parse(dateTimePicker2.Text).Year + "-" + DateTime.Parse(dateTimePicker2.Text).Month + "-" + DateTime.Parse(dateTimePicker2.Text).Day);
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewReports.Items.Clear();
queueReports.Clear();
_Reports v = new _Reports();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["count"].ToString().Trim());
v.date = dataRow["count"].ToString().Trim();
item1.SubItems.Add(dataRow["stoimost"].ToString().Trim());
v.numer = dataRow["stoimost"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
v.kol_czel = dataRow["Kol_czel"].ToString().Trim();
listViewReports.Items.Add(item1);
listViewReports.Items[listViewReports.Items.Count - 1].Tag = dataRow["count"].ToString();
queueReports.Enqueue(v);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
//отчет с номерами билетов
public void refreshReportsBilety()
{
listViewReports.Items.Clear();
queueReports.Clear();
listViewReports.Columns.Clear();
listViewReports.Columns.Add("Дата", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("№ начало", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("№ конец", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Стоимость", -2, HorizontalAlignment.Left);
listViewReports.Columns[0].Width = 150;
listViewReports.Columns[1].Width = 150;
listViewReports.Columns[2].Width = 150;
listViewReports.Columns[3].Width = 150;
listViewReports.Columns[4].Width = 150;
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = String.Format("select `zhurnal`.`date` AS `Data`, `zhurnal`.`N_kvit_nach` AS `N_kvit_nach`,`zhurnal`.`N_kvit_koniec` AS `N_kvit_koniec`,((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1) AS `KolCzel`,(`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)) AS `Stoimost` from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`ekskursii`.`nazvanie` = '{0}') and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}')) union all select '' AS `Data`, 'Общая стоимость' AS `N_kvit_nach`,'' AS `N_kvit_koniec`,sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)) AS `KolCzel`,sum((`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))) AS `Stoimost` from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`ekskursii`.`nazvanie` = '{0}') and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}')) order by `N_kvit_nach`", comboBoxEkskursija.Text, DateTime.Parse(dateTimePicker1.Text).Year + "-" + DateTime.Parse(dateTimePicker1.Text).Month + "-" + DateTime.Parse(dateTimePicker1.Text).Day, DateTime.Parse(dateTimePicker2.Text).Year + "-" + DateTime.Parse(dateTimePicker2.Text).Month + "-" + DateTime.Parse(dateTimePicker2.Text).Day);
if (comboBoxEkskursija.SelectedIndex == -1)
sql = String.Format("select `zhurnal`.`date` AS `Data`, `zhurnal`.`N_kvit_nach` AS `N_kvit_nach`,`zhurnal`.`N_kvit_koniec` AS `N_kvit_koniec`,((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1) AS `KolCzel`,(`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)) AS `Stoimost` from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`zhurnal`.`date` >= '{0}') and (`zhurnal`.`date` <= '{1}')) union all select '' AS `Data`, 'Общая стоимость' AS `N_kvit_nach`,'' AS `N_kvit_koniec`,sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)) AS `KolCzel`,sum((`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))) AS `Stoimost` from (`zhurnal` join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`zhurnal`.`date` >= '{0}') and (`zhurnal`.`date` <= '{1}')) order by `N_kvit_nach`", DateTime.Parse(dateTimePicker1.Text).Year + "-" + DateTime.Parse(dateTimePicker1.Text).Month + "-" + DateTime.Parse(dateTimePicker1.Text).Day, DateTime.Parse(dateTimePicker2.Text).Year + "-" + DateTime.Parse(dateTimePicker2.Text).Month + "-" + DateTime.Parse(dateTimePicker2.Text).Day);
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewReports.Items.Clear();
queueReports.Clear();
_Reports v = new _Reports();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["Data"].ToString().Trim());
v.date = dataRow["Data"].ToString().Trim();
item1.SubItems.Add(dataRow["N_kvit_nach"].ToString().Trim());
v.kvn = dataRow["N_kvit_nach"].ToString().Trim();
item1.SubItems.Add(dataRow["N_kvit_koniec"].ToString().Trim());
v.kvk = dataRow["N_kvit_koniec"].ToString().Trim();
item1.SubItems.Add(dataRow["KolCzel"].ToString().Trim());
v.kol_czel = dataRow["KolCzel"].ToString().Trim();
item1.SubItems.Add(dataRow["Stoimost"].ToString().Trim());
v.cena = dataRow["Stoimost"].ToString().Trim();
listViewReports.Items.Add(item1);
listViewReports.Items[listViewReports.Items.Count - 1].Tag = dataRow["N_kvit_nach"].ToString();
queueReports.Enqueue(v);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
/*-----------------------
*-----------------------
*отчеты по ЗП
*-----------------------
*-----------------------
*/
public void refreshReports()
{
listViewReports.Items.Clear();
queueReports.Clear();
listViewReports.Columns.Clear();
listViewReports.Columns.Add("Дата", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Номер экскурсии", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Цена", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Цена", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Номер начало", -2, HorizontalAlignment.Left);
listViewReports.Columns.Add("Номер конец", -2, HorizontalAlignment.Left);
listViewReports.Columns[0].Width = 150;
listViewReports.Columns[1].Width = 150;
listViewReports.Columns[2].Width = 150;
listViewReports.Columns[3].Width = 150;
listViewReports.Columns[4].Width = 150;
listViewReports.Columns[5].Width = 150;
listViewReports.Columns[6].Width = 150;
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = string.Format("select `zhurnal`.`id` AS `id`,cast(`zhurnal`.`date` as date) AS `DATA`,`zhurnal`.`N_kvit_nach` AS `N_kvit_nach`,`zhurnal`.`N_kvit_koniec` AS `N_kvit_koniec`,((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1) AS `Kol_czel`,`zhurnal`.`N_ekskursii` AS `N`,'' AS `cena` from `zhurnal` where ((`zhurnal`.`FiO` = 'БДСМ') and `zhurnal`.`id` in (select (`zhurnal`.`id` + 1) AS `id` from `zhurnal` where (`zhurnal`.`FiO` = '{0}')) and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}')) union all select `zhurnal`.`id` AS `id`,cast(`zhurnal`.`date` as date) AS `DATA`,`zhurnal`.`N_kvit_nach` AS `N_kvit_nach`,`zhurnal`.`N_kvit_koniec` AS `N_kvit_koniec`,((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1) AS `Kol_czel`,`zhurnal`.`N_ekskursii` AS `N`,((case `ekskursovody`.`category` when 1 then `ekskursii`.`1_kat` when 2 then `ekskursii`.`2_kat` when 3 then `ekskursii`.`3_kat` end) * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)) AS `cena` from ((`zhurnal` join `ekskursovody` on((`zhurnal`.`FiO` = `ekskursovody`.`FiO`))) join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`zhurnal`.`FiO` = '{0}') and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}') and (`zhurnal`.`N_ekskursii` like '%а')) union all select `zhurnal`.`id` AS `id`, cast(`zhurnal`.`date` as date) AS `DATA`,`zhurnal`.`N_kvit_nach` AS `N_kvit_nach`,`zhurnal`.`N_kvit_koniec` AS `N_kvit_koniec`,((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1) AS `Kol_czel`,`zhurnal`.`N_ekskursii` AS `N`,(case `ekskursovody`.`category` when 1 then `ekskursii`.`1_kat` when 2 then `ekskursii`.`2_kat` when 3 then `ekskursii`.`3_kat` end) AS `cena` from ((`zhurnal` join `ekskursovody` on((`zhurnal`.`FiO` = `ekskursovody`.`FiO`))) join `ekskursii` on((`zhurnal`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`zhurnal`.`FiO` = '{0}') and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}') and (not((`zhurnal`.`N_ekskursii` like '%а')))) union all select `plategki`.`id` AS `id`,cast(`plategki`.`date` as date) AS `DATA`,`plategki`.`N_platezhki` AS `N_kvit_nach`,`plategki`.`N_platezhki` AS `N_kvit_koniec`,`plategki`.`Kol_czel` AS `Kol_czel`,`plategki`.`N_ekskursii` AS `N`,(case `ekskursovody`.`category` when 1 then `ekskursii`.`1_kat` when 2 then `ekskursii`.`2_kat` when 3 then `ekskursii`.`3_kat` end) AS `cena` from ((`plategki` join `ekskursovody` on((`plategki`.`FiO` = `ekskursovody`.`FiO`))) join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`plategki`.`FiO` = '{0}') and (`plategki`.`date` >= '{1}') and (`plategki`.`date` <= '{2}')) order by `DATA`", comboBoxEkskursovod.Text, DateTime.Parse(dateTimePicker1.Text).Year + "-" + DateTime.Parse(dateTimePicker1.Text).Month + "-" + DateTime.Parse(dateTimePicker1.Text).Day, DateTime.Parse(dateTimePicker2.Text).Year + "-" + DateTime.Parse(dateTimePicker2.Text).Month + "-" + DateTime.Parse(dateTimePicker2.Text).Day);
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewReports.Items.Clear();
queueReports.Clear();
_Reports cv = new _Reports();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["DATA"].ToString().Trim());
cv.date = dataRow["DATA"].ToString().Trim();
item1.SubItems.Add(dataRow["N"].ToString().Trim());
cv.numer = dataRow["N"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
cv.kol_czel = dataRow["Kol_czel"].ToString().Trim();
item1.SubItems.Add(dataRow["Cena"].ToString().Trim());
cv.cena = dataRow["Cena"].ToString().Trim();
item1.SubItems.Add(dataRow["Cena"].ToString().Trim());
cv.cena = dataRow["Cena"].ToString().Trim();
item1.SubItems.Add(dataRow["N_kvit_nach"].ToString().Trim());
cv.kvn = dataRow["N_kvit_nach"].ToString().Trim();
item1.SubItems.Add(dataRow["N_kvit_koniec"].ToString().Trim());
cv.kvk = dataRow["N_kvit_koniec"].ToString().Trim();
listViewReports.Items.Add(item1);
listViewReports.Items[listViewReports.Items.Count - 1].Tag = dataRow["DATA"].ToString();
queueReports.Enqueue(cv);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
private void listViewEquipmentGroups_SelectedIndexChanged(object sender, EventArgs e)
{
if (listViewReports.FocusedItem == null)
return;
int k = queueReports.Count;
for (int i = 0; i < k; i++)
{
m_reports = (_Reports)queueReports.Dequeue();
if (m_reports.date.ToString() == (string)listViewReports.Items[listViewReports.FocusedItem.Index].Tag)
{
//фигня про экскурсии и экскурсоводов
_Ekskursii c;
int k2 = queueEkskursii.Count;
for (int i2 = 0; i2 < k2; i2++)
{
c = (_Ekskursii)queueEkskursii.Dequeue();
if (c.id.ToString() == m_reports.numer.ToString())
{
for (int iii = 0; iii < comboBoxEkskursija.Items.Count; iii++)
{
if (((_Ekskursii)comboBoxEkskursija.Items[iii]).id == c.id)
{
comboBoxEkskursija.SelectedIndex = iii;
m_ekskursii = c;
break;
}
}
};
queueEkskursii.Enqueue(c);
}
queueReports.Enqueue(m_reports);
break;
};
}
}
