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