private void listViewReportsRasselenie_SelectedIndexChanged(object sender, EventArgs e)
{
if (listViewReportsRasselenie.FocusedItem == null)
return;
int k = queueReportsRasselenie.Count;
for (int i = 0; i < k; i++)
{
m_reportsrasselenie = (_ReportsRasselenie)queueReportsRasselenie.Dequeue();
if (m_reportsrasselenie.date.ToString() == (string)listViewReportsRasselenie.Items[listViewReportsRasselenie.FocusedItem.Index].Tag)
{
// textBoxName.Text = m_reportsrasselenie.date;
// textBoxCity.Text = m_reportsrasselenie.city;
_Zhitie c;
int k2 = queueZhitie.Count;
for (int i2 = 0; i2 < k2; i2++)
{
c = (_Zhitie)queueZhitie.Dequeue();
if (c.nazvanie.ToString() == m_reportsrasselenie.zhitie.ToString())
{
for (int iii = 0; iii < comboBoxZhitie.Items.Count; iii++)
{
if (((_Zhitie)comboBoxZhitie.Items[iii]).nazvanie == c.nazvanie)
{
comboBoxZhitie.SelectedIndex = iii;
m_zhitie = c;
break;
}
}
};
queueZhitie.Enqueue(c);
}
queueReportsRasselenie.Enqueue(m_reportsrasselenie);
break;
};
queueReportsRasselenie.Enqueue(m_reportsrasselenie);
}
}
private void buttonYearReport2_Click(object sender, EventArgs e)
{
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = String.Format("select distinct `nazvanie` from `zhitie`");
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
mainWin.m_dbConnector.Unlock();
List<string> zhitie = new List<string>();
foreach (DataRow dataRow in dataTable.Rows)
{
zhitie.Add(dataRow["nazvanie"].ToString().Trim());
}
string[] arr = zhitie.ToArray();
bool ini = true;
sql = "";
for (int x = 0; x < arr.Length; x++)
{
if (ini == false) { sql += " union all "; }
//sql += string.Format("select \"{0}\" AS name, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-01-01\") and (`rasselenie`.`date` < \"{1}-04-01\") and `zhitie` = \"{0}\") AS `Kol_czel_jan`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-01-01\") and (`rasselenie`.`date` < \"{1}-04-01\") and `zhitie` = \"{0}\" ) AS `Sum_jan`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-04-01\") and (`rasselenie`.`date` < \"{1}-06-01\") and `zhitie` = \"{0}\") AS `Kol_czel_apr`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-04-01\") and (`rasselenie`.`date` < \"{1}-06-01\") and `zhitie` = \"{0}\" ) AS `Sum_apr`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-06-01\") and (`rasselenie`.`date` < \"{1}-07-01\") and `zhitie` = \"{0}\") AS `Kol_czel_may`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-06-01\") and (`rasselenie`.`date` < \"{1}-07-01\") and `zhitie` = \"{0}\" ) AS `Sum_may`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-07-01\") and (`rasselenie`.`date` < \"{1}-08-01\") and `zhitie` = \"{0}\" ) AS `Kol_czel_jun`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-07-01\") and (`rasselenie`.`date` < \"{1}-08-01\") and `zhitie` = \"{0}\" ) AS `Sum_jun`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-08-01\") and (`rasselenie`.`date` < \"{1}-09-01\") and `zhitie` = \"{0}\" ) AS `Kol_czel_jul`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-08-01\") and (`rasselenie`.`date` < \"{1}-09-01\") and `zhitie` = \"{0}\" ) AS `Sum_jul`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-09-01\") and (`rasselenie`.`date` < \"{1}-10-01\") and `zhitie` = \"{0}\" ) AS `Kol_czel_aug`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-09-01\") and (`rasselenie`.`date` < \"{1}-10-01\") and `zhitie` = \"{0}\" ) AS `Sum_aug`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-10-01\") and (`rasselenie`.`date` < \"{1}-12-01\") and `zhitie` = \"{0}\") AS `Kol_czel_oct`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-10-01\") and (`rasselenie`.`date` < \"{1}-12-01\") and `zhitie` = \"{0}\" ) AS `Sum_oct`", arr[x], comboBoxYear.Text.ToString().Trim());
sql += string.Format("select \"{0}\" AS name, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-01-01\") and (`rasselenie`.`date` < \"{1}-04-01\") and `zhitie` = \"{0}\") AS `Kol_czel_jan`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-01-01\") and (`rasselenie`.`date` < \"{1}-04-01\") and `zhitie` = \"{0}\" ) AS `Sum_jan`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-04-01\") and (`rasselenie`.`date` < \"{1}-06-01\") and `zhitie` = \"{0}\") AS `Kol_czel_apr`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-04-01\") and (`rasselenie`.`date` < \"{1}-06-01\") and `zhitie` = \"{0}\" ) AS `Sum_apr`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-06-01\") and (`rasselenie`.`date` < \"{1}-07-01\") and `zhitie` = \"{0}\") AS `Kol_czel_may`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-06-01\") and (`rasselenie`.`date` < \"{1}-07-01\") and `zhitie` = \"{0}\" ) AS `Sum_may`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-07-01\") and (`rasselenie`.`date` < \"{1}-08-01\") and `zhitie` = \"{0}\" ) AS `Kol_czel_jun`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-07-01\") and (`rasselenie`.`date` < \"{1}-08-01\") and `zhitie` = \"{0}\" ) AS `Sum_jun`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-08-01\") and (`rasselenie`.`date` < \"{1}-09-01\") and `zhitie` = \"{0}\" ) AS `Kol_czel_jul`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-08-01\") and (`rasselenie`.`date` < \"{1}-09-01\") and `zhitie` = \"{0}\" ) AS `Sum_jul`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-09-01\") and (`rasselenie`.`date` < \"{1}-10-01\") and `zhitie` = \"{0}\" ) AS `Kol_czel_aug`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-09-01\") and (`rasselenie`.`date` < \"{1}-10-01\") and `zhitie` = \"{0}\" ) AS `Sum_aug`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-10-01\") and (`rasselenie`.`date` < \"{1}-12-01\") and `zhitie` = \"{0}\") AS `Kol_czel_oct`, (select ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{1}-10-01\") and (`rasselenie`.`date` < \"{1}-12-01\") and `zhitie` = \"{0}\" ) AS `Sum_oct`", arr[x], comboBoxYear.Text.ToString().Trim());
ini = false;
}
//throw new System.InvalidOperationException(sql);
myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
dataSet = new DataSet();
myAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
listViewReportsRasselenie.Items.Clear();
listViewReportsRasselenie.Columns.Clear();
listViewReportsRasselenie.Columns.Add("Виды расселения", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Янв-Март Туристы", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Янв-Март Сумма", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Апр-Май Туристы", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Апр-Май Сумма", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Июнь Туристы", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Июнь Сумма", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Июль Туристы", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Июль Сумма", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Август Туристы", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Август Сумма", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Сент Туристы", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Сент Сумма", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Окт-Дек Туристы", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Окт-Дек Сумма", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns[0].Width = 150;
listViewReportsRasselenie.Columns[1].Width = 150;
listViewReportsRasselenie.Columns[2].Width = 150;
listViewReportsRasselenie.Columns[3].Width = 150;
listViewReportsRasselenie.Columns[4].Width = 150;
listViewReportsRasselenie.Columns[5].Width = 150;
listViewReportsRasselenie.Columns[6].Width = 150;
listViewReportsRasselenie.Columns[7].Width = 150;
listViewReportsRasselenie.Columns[8].Width = 150;
listViewReportsRasselenie.Columns[9].Width = 150;
listViewReportsRasselenie.Columns[10].Width = 150;
listViewReportsRasselenie.Columns[11].Width = 150;
listViewReportsRasselenie.Columns[12].Width = 150;
listViewReportsRasselenie.Columns[13].Width = 150;
listViewReportsRasselenie.Columns[14].Width = 150;
queueReportsRasselenie.Clear();
_ReportsRasselenie cv = new _ReportsRasselenie();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["name"].ToString().Trim());
cv.date= dataRow["name"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel_jan"].ToString().Trim());
cv.money = dataRow["Kol_czel_jan"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_jan"].ToString().Trim());
cv.money = dataRow["Sum_jan"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel_apr"].ToString().Trim());
cv.kol_czel = dataRow["Kol_czel_apr"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_apr"].ToString().Trim());
cv.money = dataRow["Sum_apr"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel_may"].ToString().Trim());
cv.money = dataRow["Kol_czel_may"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_may"].ToString().Trim());
cv.kol_czel = dataRow["Sum_may"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel_jun"].ToString().Trim());
cv.zhitie = dataRow["Kol_czel_jun"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_jun"].ToString().Trim());
cv.kol_czel = dataRow["Sum_jun"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel_jul"].ToString().Trim());
cv.money = dataRow["Kol_czel_jul"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_jul"].ToString().Trim());
cv.money = dataRow["Sum_jul"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel_aug"].ToString().Trim());
cv.money = dataRow["Kol_czel_aug"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_aug"].ToString().Trim());
cv.money = dataRow["Sum_aug"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel_oct"].ToString().Trim());
cv.money = dataRow["Kol_czel_oct"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_oct"].ToString().Trim());
cv.money = dataRow["Sum_oct"].ToString().Trim();
listViewReportsRasselenie.Items.Add(item1);
listViewReportsRasselenie.Items[listViewReportsRasselenie.Items.Count - 1].Tag = dataRow["name"].ToString();
queueReportsRasselenie.Enqueue(cv);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
private void buttonYearReport1_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, ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) AS `cena_nal`, ifnull(sum(`rasselenie`.`zhitie_bez`),0) AS `cena_bez`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) AS `Kol_czel` from `rasselenie` where (`rasselenie`.`date` >= \"{0}-{2}-01\") and (`rasselenie`.`date` < \"{0}-{3}-01\") and (`rasselenie`.`bron` = \"\" and `rasselenie`.`bron_bez` = \"\")) AS `Kol_czel_nal`, ifnull(sum(`rasselenie`.`bron`),0) AS `bron_nal`, ifnull(sum(`rasselenie`.`bron_bez`),0) AS `bron_bez`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) AS `Kol_czel` from `rasselenie` where (`rasselenie`.`date` >= \"{0}-{2}-01\") and (`rasselenie`.`date` < \"{0}-{3}-01\") and (`rasselenie`.`bron` != \"\" OR `rasselenie`.`bron_bez` != \"\")) AS `Kol_czel_bron`, ifnull(sum(parkovka),0) AS `park`, ifnull(sum(`rasselenie`.`Kol_czel`),0) AS `Sum_czel`, (ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) AS `Sum_nal`, (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) AS `Sum_bez`, ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) + ifnull(sum(parkovka),0)) AS `Sum_all` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{0}-{2}-01\") and (`rasselenie`.`date` < \"{0}-{3}-01\")", comboBoxYear.Text.ToString().Trim(), arr[x, 0], arr[x, 1], arr[x, 2]);
sql += string.Format("select \"{1}\" as month, ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) AS `cena_nal`, ifnull(sum(`rasselenie`.`zhitie_bez`),0) AS `cena_bez`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) AS `Kol_czel` from `rasselenie` where (`rasselenie`.`date` >= \"{0}-{2}-01\") and (`rasselenie`.`date` < \"{0}-{3}-01\") and (`rasselenie`.`bron` = \"\")) AS `Kol_czel_nal`, ifnull(sum(`rasselenie`.`bron`),0) AS `bron_nal`, ifnull(sum(`rasselenie`.`bron_bez`),0) AS `bron_bez`, (select ifnull(sum(`rasselenie`.`Kol_czel`),0) AS `Kol_czel` from `rasselenie` where (`rasselenie`.`date` >= \"{0}-{2}-01\") and (`rasselenie`.`date` < \"{0}-{3}-01\") and (`rasselenie`.`bron` != \"\")) AS `Kol_czel_bron`, ifnull(sum(parkovka),0) AS `park`, ifnull(sum(`rasselenie`.`Kol_czel`),0) AS `Sum_czel`, (ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + sum(ifnull(`rasselenie`.`parkovka`,0)) + ifnull(sum(`rasselenie`.`bron`),0)) AS `Sum_nal`, (ifnull(sum(`rasselenie`.`zhitie_bez`),0) + ifnull(sum(`rasselenie`.`bron_bez`),0)) AS `Sum_bez`, ((ifnull(sum((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`),0) + ifnull(sum(`rasselenie`.`bron`),0)) + ifnull(sum(`rasselenie`.`bron_bez`),0) + ifnull(sum(parkovka),0)) AS `Sum_all` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where (`rasselenie`.`date` >= \"{0}-{2}-01\") and (`rasselenie`.`date` < \"{0}-{3}-01\")", comboBoxYear.Text.ToString().Trim(), arr[x, 0], arr[x, 1], arr[x, 2]);
ini = false;
}
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewReportsRasselenie.Items.Clear();
listViewReportsRasselenie.Columns.Clear();
listViewReportsRasselenie.Columns.Add("Месяц", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Проживание", -2, HorizontalAlignment.Left);
//listViewReportsRasselenie.Columns.Add("Проживание без", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Проживание чел", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Бронь", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Безнал", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Бронь чел", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Парковка", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Итого чел", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Итого нал", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Итого без", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Итого сумм", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns[0].Width = 150;
listViewReportsRasselenie.Columns[1].Width = 150;
listViewReportsRasselenie.Columns[2].Width = 150;
listViewReportsRasselenie.Columns[3].Width = 150;
listViewReportsRasselenie.Columns[4].Width = 150;
listViewReportsRasselenie.Columns[5].Width = 150;
listViewReportsRasselenie.Columns[6].Width = 150;
listViewReportsRasselenie.Columns[7].Width = 150;
listViewReportsRasselenie.Columns[8].Width = 150;
listViewReportsRasselenie.Columns[9].Width = 150;
listViewReportsRasselenie.Columns[10].Width = 150;
//listViewReportsRasselenie.Columns[11].Width = 150;
queueReportsRasselenie.Clear();
_ReportsRasselenie cv = new _ReportsRasselenie();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["month"].ToString().Trim());
cv.date = dataRow["month"].ToString().Trim();
item1.SubItems.Add(dataRow["cena_nal"].ToString().Trim());
cv.money = dataRow["cena_nal"].ToString().Trim();
//item1.SubItems.Add(dataRow["cena_bez"].ToString().Trim());
//cv.money = dataRow["cena_bez"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel_nal"].ToString().Trim());
cv.kol_czel = dataRow["Kol_czel_nal"].ToString().Trim();
item1.SubItems.Add(dataRow["bron_nal"].ToString().Trim());
cv.money = dataRow["bron_nal"].ToString().Trim();
item1.SubItems.Add(dataRow["bron_bez"].ToString().Trim());
cv.money = dataRow["bron_bez"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel_bron"].ToString().Trim());
cv.kol_czel = dataRow["Kol_czel_bron"].ToString().Trim();
item1.SubItems.Add(dataRow["park"].ToString().Trim());
cv.zhitie = dataRow["park"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_czel"].ToString().Trim());
cv.kol_czel = dataRow["Sum_czel"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_nal"].ToString().Trim());
cv.money = dataRow["Sum_nal"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_bez"].ToString().Trim());
cv.money = dataRow["Sum_bez"].ToString().Trim();
item1.SubItems.Add(dataRow["Sum_all"].ToString().Trim());
cv.money = dataRow["Sum_all"].ToString().Trim();
listViewReportsRasselenie.Items.Add(item1);
listViewReportsRasselenie.Items[listViewReportsRasselenie.Items.Count - 1].Tag = dataRow["month"].ToString();
queueReportsRasselenie.Enqueue(cv);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
//
//всякие отчеты
//житие
public void refreshReportsZhitie()
{
listViewReportsRasselenie.Items.Clear();
queueReportsRasselenie.Clear();
listViewReportsRasselenie.Columns.Clear();
listViewReportsRasselenie.Columns.Add("Дата", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Житие в", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Кол-во денег", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Безнал", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns[0].Width = 150;
listViewReportsRasselenie.Columns[1].Width = 150;
listViewReportsRasselenie.Columns[2].Width = 150;
listViewReportsRasselenie.Columns[3].Width = 150;
listViewReportsRasselenie.Columns[4].Width = 150;
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
//string sql = String.Format("select 'наличные' AS `date`, `rasselenie`.`zhitie`,sum(`rasselenie`.`Kol_czel`) AS `Kol_czel`,sum((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where `zhitie`.`nazvanie` = '{0}' and `rasselenie`.`date` >= '{1}' and `rasselenie`.`date` <= '{2}' union all select 'безналичность' AS `date`, `rasselenie`.`zhitie_bez`, sum(`rasselenie`.`Kol_czel`) AS `Kol_czel`, sum(((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + ifnull(`rasselenie`.`bron_bez`,0)) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie_bez` = `zhitie`.`nazvanie`))) where `zhitie`.`nazvanie` = '{0}' and `rasselenie`.`date` >= '{1}' and `rasselenie`.`date` <= '{2}'", comboBoxZhitie.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 (comboBoxZhitie.SelectedIndex == -1)
// sql = String.Format("select `rasselenie`.`date` AS `date`,`rasselenie`.`zhitie` AS `zhitie`,sum(`rasselenie`.`Kol_czel`) AS `Kol_czel`,sum(((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`)) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where `rasselenie`.`date` >= '{0}' and `rasselenie`.`date` <= '{1}' group by `rasselenie`.`zhitie` union all select `rasselenie`.`date` AS `date`,concat(`rasselenie`.`zhitie_bez`,'(б/н)') AS `zhitie`,sum(`rasselenie`.`Kol_czel`) AS `Kol_czel`,sum((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + ifnull(`rasselenie`.`bron_bez`,0))) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie_bez` = `zhitie`.`nazvanie`))) where `rasselenie`.`date` >= '{0}' and `rasselenie`.`date` <= '{1}' group by `rasselenie`.`zhitie_bez`", 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);
string sql = String.Format("select `rasselenie`.`date` AS `date`, `rasselenie`.`zhitie`,sum(`rasselenie`.`Kol_czel`) AS `Kol_czel`,sum((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`) AS `cena`, sum(ifnull(`rasselenie`.`bron_bez`,0)) AS `bez` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where `zhitie`.`nazvanie` = '{0}' and `rasselenie`.`date` >= '{1}' and `rasselenie`.`date` <= '{2}'", comboBoxZhitie.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 (comboBoxZhitie.SelectedIndex == -1)
sql = String.Format("select `rasselenie`.`date` AS `date`,`rasselenie`.`zhitie` AS `zhitie`,sum(`rasselenie`.`Kol_czel`) AS `Kol_czel`,sum(((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`)) AS `cena`, sum(ifnull(`rasselenie`.`bron_bez`,0)) AS `bez` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where `rasselenie`.`date` >= '{0}' and `rasselenie`.`date` <= '{1}' group by `rasselenie`.`zhitie`", 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];
listViewReportsRasselenie.Items.Clear();
queueReportsRasselenie.Clear();
_ReportsRasselenie v = new _ReportsRasselenie();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["date"].ToString().Trim());
v.date = dataRow["date"].ToString().Trim();
item1.SubItems.Add(dataRow["zhitie"].ToString().Trim());
v.zhitie= dataRow["zhitie"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
v.kol_czel = dataRow["Kol_czel"].ToString().Trim();
item1.SubItems.Add(dataRow["cena"].ToString().Trim());
v.money = dataRow["cena"].ToString().Trim();
item1.SubItems.Add(dataRow["bez"].ToString().Trim());
v.money = dataRow["bez"].ToString().Trim();
listViewReportsRasselenie.Items.Add(item1);
listViewReportsRasselenie.Items[listViewReportsRasselenie.Items.Count - 1].Tag = dataRow["date"].ToString();
queueReportsRasselenie.Enqueue(v);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
public void refreshReportsRasselenie()
{
listViewReportsRasselenie.Items.Clear();
queueReportsRasselenie.Clear();
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = "SELECT `date`, `zhitie` FROM `rasselenie` ORDER BY `date`";
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewReportsRasselenie.Items.Clear();
queueReportsRasselenie.Clear();
_ReportsRasselenie t = new _ReportsRasselenie();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["date"].ToString().Trim(), 0);
t.date= dataRow["date"].ToString();
item1.SubItems.Add(dataRow["zhitie"].ToString().Trim());
t.zhitie = dataRow["zhitie"].ToString().Trim();
listViewReportsRasselenie.Items.Add(item1);
listViewReportsRasselenie.Items[listViewReportsRasselenie.Items.Count - 1].Tag = dataRow["date"].ToString();
queueReportsRasselenie.Enqueue(t);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
//города
public void refreshReportsCity()
{
listViewReportsRasselenie.Items.Clear();
queueReportsRasselenie.Clear();
listViewReportsRasselenie.Columns.Clear();
listViewReportsRasselenie.Columns.Add("Дата", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Город", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns.Add("Кол-во денег", -2, HorizontalAlignment.Left);
listViewReportsRasselenie.Columns[0].Width = 150;
listViewReportsRasselenie.Columns[1].Width = 150;
listViewReportsRasselenie.Columns[2].Width = 150;
listViewReportsRasselenie.Columns[3].Width = 150;
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = String.Format("select `rasselenie`.`date` AS `date`,`rasselenie`.`City` AS `City`,sum(`rasselenie`.`Kol_czel`) AS `kol_czel`,sum(((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`)) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where `rasselenie`.`City`='{0}' group by `rasselenie`.`City` union all select `rasselenie`.`date` AS `date`,concat(`rasselenie`.`City`,' б/н') AS `City`,sum(`rasselenie`.`Kol_czel`) AS `kol_czel`,sum((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + ifnull(`rasselenie`.`bron_bez`,0))) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie_bez` = `zhitie`.`nazvanie`))) where `rasselenie`.`City`='{0}' group by `rasselenie`.`City` order by `cena` desc", textBoxCity.Text);
if(textBoxCity.Text.ToString() == "")
sql = "select `rasselenie`.`date` AS `date`,`rasselenie`.`City` AS `City`,sum(`rasselenie`.`Kol_czel`) AS `kol_czel`,sum(((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`)) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) group by `rasselenie`.`City` union all select `rasselenie`.`date` AS `date`,concat(`rasselenie`.`City`,' б/н') AS `City`,sum(`rasselenie`.`Kol_czel`) AS `kol_czel`,sum((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + ifnull(`rasselenie`.`bron_bez`,0))) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie_bez` = `zhitie`.`nazvanie`))) group by `rasselenie`.`City` order by `cena` desc";
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewReportsRasselenie.Items.Clear();
queueReportsRasselenie.Clear();
_ReportsRasselenie v = new _ReportsRasselenie();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["date"].ToString().Trim());
v.date= dataRow["date"].ToString().Trim();
item1.SubItems.Add(dataRow["City"].ToString().Trim());
v.zhitie = dataRow["City"].ToString().Trim();
item1.SubItems.Add(dataRow["kol_czel"].ToString().Trim());
v.kol_czel = dataRow["kol_czel"].ToString().Trim();
item1.SubItems.Add(dataRow["cena"].ToString().Trim());
v.money = dataRow["cena"].ToString().Trim();
//item1.SubItems.Add(dataRow["zhitie"].ToString().Trim());
//v.zhitie = dataRow["zhitie"].ToString().Trim();
listViewReportsRasselenie.Items.Add(item1);
listViewReportsRasselenie.Items[listViewReportsRasselenie.Items.Count - 1].Tag = dataRow["date"].ToString();
queueReportsRasselenie.Enqueue(v);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
