//отчет по квитанциям
public void refreshRasselenieKvit()
{
listViewRasselenie.Items.Clear();
queueRasselenie.Clear();
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
//string sql = String.Format("select `rasselenie`.`id` AS `id`,cast(`rasselenie`.`date` as char charset utf8) AS `date`,`rasselenie`.`N_kvit` AS `N_kvit`,`rasselenie`.`FiO` AS `FIO`,`rasselenie`.`City` AS `City`,`rasselenie`.`Kol_dney` AS `Kol_dney`,`rasselenie`.`Kol_czel` AS `Kol_czel`,`rasselenie`.`zhitie` AS `zhitie`,'' AS `zhitie_bez`,`rasselenie`.`parkovka` AS `parkovka`,`rasselenie`.`zakazczik` AS `Zakazczik`,`rasselenie`.`bron` AS `bron`,'' AS `bron_bez`,((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) WHERE `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`,cast(`rasselenie`.`date` as char charset utf8) AS `date`,`rasselenie`.`N_kvit` AS `N_kvit`,`rasselenie`.`FiO` AS `FIO`,`rasselenie`.`City` AS `City`,`rasselenie`.`Kol_dney` AS `Kol_dney`,`rasselenie`.`Kol_czel` AS `Kol_czel`,'' AS `zhitie`,`rasselenie`.`zhitie_bez` AS `zhitie_bez`,'' AS `parkovka`,`rasselenie`.`zakazczik` AS `Zakazczik`,'' AS `bron`,`rasselenie`.`bron_bez` AS `bron_bez`,(((`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 `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`, '' AS `date`,'{1}' AS `N_kvit`,'' AS `FIO`,'' AS `City`,'' AS `Kol_dney`,'' AS `Kol_czel`,'' AS `zhitie`,'' AS `zhitie_bez`,'' AS `parkovka`,'Итого наличность:' AS `Zakazczik`,'' AS `bron`,'' AS `bron_bez`,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 `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`,'' AS `date`,'{1}' AS `N_kvit`,'' AS `FIO`,'' AS `City`,'' AS `Kol_dney`,'' AS `Kol_czel`,'' AS `zhitie`,'' AS `zhitie_bez`,'' AS `parkovka`, 'Безналичность:' AS `Zakazczik`,'' AS `bron`,'' AS `bron_bez`,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 `N_kvit` >= '{0}' and `N_kvit` <= '{1}' ORDER BY `N_kvit`", textBoxKvN.Text, textBoxKvK.Text);
string sql = String.Format("select `rasselenie`.`id` AS `id`,cast(`rasselenie`.`date` as char charset utf8) AS `date`,`rasselenie`.`N_kvit` AS `N_kvit`,`rasselenie`.`FiO` AS `FIO`,`rasselenie`.`City` AS `City`,`rasselenie`.`Kol_dney` AS `Kol_dney`,`rasselenie`.`Kol_czel` AS `Kol_czel`,`rasselenie`.`zhitie` AS `zhitie`,`rasselenie`.`parkovka` AS `parkovka`,`rasselenie`.`zakazczik` AS `Zakazczik`,`rasselenie`.`bron` AS `bron`,`rasselenie`.`bron_bez` AS `bron_bez`,((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) WHERE `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`, '' AS `date`,'{1}' AS `N_kvit`,'' AS `FIO`,'' AS `City`,'' AS `Kol_dney`,'' AS `Kol_czel`,'' AS `zhitie`,'' AS `parkovka`,'Итого наличность:' AS `Zakazczik`,'' AS `bron`,'' AS `bron_bez`,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 `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`,'' AS `date`,'{1}' AS `N_kvit`,'' AS `FIO`,'' AS `City`,'' AS `Kol_dney`,'' AS `Kol_czel`,'' AS `zhitie`,'' AS `parkovka`, 'Безналичность:' AS `Zakazczik`,'' AS `bron`,'' AS `bron_bez`,sum(ifnull(`rasselenie`.`bron_bez`,0)) AS `cena` from `rasselenie` WHERE `N_kvit` >= '{0}' and `N_kvit` <= '{1}' ORDER BY `N_kvit`", textBoxKvN.Text, textBoxKvK.Text);
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewRasselenie.Items.Clear();
queueRasselenie.Clear();
_Rasselenie t = new _Rasselenie();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["id"].ToString().Trim(), 0);
t.id = int.Parse(dataRow["id"].ToString());
item1.SubItems.Add(dataRow["date"].ToString().Trim());
t.date = dataRow["date"].ToString().Trim();
item1.SubItems.Add(dataRow["N_kvit"].ToString().Trim());
t.N_kvit = dataRow["N_kvit"].ToString().Trim();
item1.SubItems.Add(dataRow["FiO"].ToString().Trim());
t.FiO = dataRow["FiO"].ToString().Trim();
item1.SubItems.Add(dataRow["City"].ToString().Trim());
t.City = dataRow["City"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_dney"].ToString().Trim());
t.Kol_dney = dataRow["Kol_dney"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
t.Kol_czel = dataRow["Kol_czel"].ToString().Trim();
item1.SubItems.Add(dataRow["zhitie"].ToString().Trim());
t.zhitie = dataRow["zhitie"].ToString().Trim();
item1.SubItems.Add(dataRow["parkovka"].ToString().Trim());
t.parkovka = dataRow["parkovka"].ToString().Trim();
item1.SubItems.Add(dataRow["bron"].ToString().Trim());
t.bron = dataRow["bron"].ToString().Trim();
item1.SubItems.Add(dataRow["bron_bez"].ToString().Trim());
t.bron_bez = dataRow["bron_bez"].ToString().Trim();
item1.SubItems.Add(dataRow["zakazczik"].ToString().Trim());
t.zakazczik = dataRow["zakazczik"].ToString().Trim();
item1.SubItems.Add(dataRow["cena"].ToString().Trim());
t.summa = dataRow["cena"].ToString().Trim();
listViewRasselenie.Items.Add(item1);
listViewRasselenie.Items[listViewRasselenie.Items.Count - 1].Tag = dataRow["id"].ToString();
queueRasselenie.Enqueue(t);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
private void listViewRasselenie_SelectedIndexChanged(object sender, EventArgs e)
{
buttonApply.Enabled = true;
buttonDelete.Enabled = true;
textBoxName.Enabled = true;
comboBoxZhitie.SelectedIndex = -1;
dateTimePicker1.Enabled = true;
comboBoxZhitie.Enabled = true;
textBoxKolDays.Enabled = true;
textBoxKolCzel.Enabled = true;
textBoxPark.Enabled = true;
textBoxNkvit.Enabled = true;
comboBoxCity.Enabled = true;
comboBoxZakazczik.Enabled = true;
textBoxBron.Enabled = true;
textBoxBez.Enabled = true;
bNew = false;
if (listViewRasselenie.FocusedItem == null)
return;
int k = queueRasselenie.Count;
for (int i = 0; i < k; i++)
{
m_rasselenie = (_Rasselenie)queueRasselenie.Dequeue();
if (m_rasselenie.id.ToString() == (string)listViewRasselenie.Items[listViewRasselenie.FocusedItem.Index].Tag)
{
textBoxName.Text = m_rasselenie.FiO;
textBoxKolDays.Text = m_rasselenie.Kol_dney;
textBoxKolCzel.Text = m_rasselenie.Kol_czel;
textBoxPark.Text = m_rasselenie.parkovka;
textBoxNkvit.Text = m_rasselenie.N_kvit;
comboBoxCity.Text = m_rasselenie.City;
comboBoxZakazczik.Text = m_rasselenie.zakazczik;
//if (m_rasselenie.zhitie_bez == "")
comboBoxZhitie.Text = m_rasselenie.zhitie;
//else comboBoxZhitie.Text = m_rasselenie.zhitie_bez;
//if (m_rasselenie.bron_bez == "")
textBoxBron.Text = m_rasselenie.bron;
//else textBoxBron.Text = m_rasselenie.bron_bez;
textBoxBez.Text = m_rasselenie.bron_bez;
dateTimePicker1.Value = DateTime.Parse(m_rasselenie.date);
_Zhitie c;
int k2 = queueZhitie.Count;
for (int i2 = 0; i2 < k2; i2++)
{
c = (_Zhitie)queueZhitie.Dequeue();
if (c.nazvanie.ToString() == m_rasselenie.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);
}
queueRasselenie.Enqueue(m_rasselenie);
break;
};
queueRasselenie.Enqueue(m_rasselenie);
}
}
private void listViewRasselenie_SelectedIndexChanged(object sender, EventArgs e)
{
buttonApply.Enabled = true;
buttonDelete.Enabled = true;
textBoxName.Enabled = true;
comboBoxZhitie.SelectedIndex = -1;
dateTimePicker1.Enabled = true;
comboBoxZhitie.Enabled = true;
textBoxKolDays.Enabled = true;
textBoxKolCzel.Enabled = true;
textBoxPark.Enabled = true;
textBoxNkvit.Enabled = true;
comboBoxCity.Enabled = true;
comboBoxZakazczik.Enabled = true;
textBoxBron.Enabled = true;
textBoxBez.Enabled = true;
bNew = false;
if (listViewRasselenie.FocusedItem == null)
{
return;
}
int k = queueRasselenie.Count;
for (int i = 0; i < k; i++)
{
m_rasselenie = (_Rasselenie)queueRasselenie.Dequeue();
if (m_rasselenie.id.ToString() == (string)listViewRasselenie.Items[listViewRasselenie.FocusedItem.Index].Tag)
{
textBoxName.Text = m_rasselenie.FiO;
textBoxKolDays.Text = m_rasselenie.Kol_dney;
textBoxKolCzel.Text = m_rasselenie.Kol_czel;
textBoxPark.Text = m_rasselenie.parkovka;
textBoxNkvit.Text = m_rasselenie.N_kvit;
comboBoxCity.Text = m_rasselenie.City;
comboBoxZakazczik.Text = m_rasselenie.zakazczik;
//if (m_rasselenie.zhitie_bez == "")
comboBoxZhitie.Text = m_rasselenie.zhitie;
//else comboBoxZhitie.Text = m_rasselenie.zhitie_bez;
//if (m_rasselenie.bron_bez == "")
textBoxBron.Text = m_rasselenie.bron;
//else textBoxBron.Text = m_rasselenie.bron_bez;
textBoxBez.Text = m_rasselenie.bron_bez;
dateTimePicker1.Value = DateTime.Parse(m_rasselenie.date);
_Zhitie c;
int k2 = queueZhitie.Count;
for (int i2 = 0; i2 < k2; i2++)
{
c = (_Zhitie)queueZhitie.Dequeue();
if (c.nazvanie.ToString() == m_rasselenie.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);
}
queueRasselenie.Enqueue(m_rasselenie);
break;
}
;
queueRasselenie.Enqueue(m_rasselenie);
}
}
//отчет по квитанциям
public void refreshRasselenieKvit()
{
listViewRasselenie.Items.Clear();
queueRasselenie.Clear();
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
//string sql = String.Format("select `rasselenie`.`id` AS `id`,cast(`rasselenie`.`date` as char charset utf8) AS `date`,`rasselenie`.`N_kvit` AS `N_kvit`,`rasselenie`.`FiO` AS `FIO`,`rasselenie`.`City` AS `City`,`rasselenie`.`Kol_dney` AS `Kol_dney`,`rasselenie`.`Kol_czel` AS `Kol_czel`,`rasselenie`.`zhitie` AS `zhitie`,'' AS `zhitie_bez`,`rasselenie`.`parkovka` AS `parkovka`,`rasselenie`.`zakazczik` AS `Zakazczik`,`rasselenie`.`bron` AS `bron`,'' AS `bron_bez`,((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) WHERE `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`,cast(`rasselenie`.`date` as char charset utf8) AS `date`,`rasselenie`.`N_kvit` AS `N_kvit`,`rasselenie`.`FiO` AS `FIO`,`rasselenie`.`City` AS `City`,`rasselenie`.`Kol_dney` AS `Kol_dney`,`rasselenie`.`Kol_czel` AS `Kol_czel`,'' AS `zhitie`,`rasselenie`.`zhitie_bez` AS `zhitie_bez`,'' AS `parkovka`,`rasselenie`.`zakazczik` AS `Zakazczik`,'' AS `bron`,`rasselenie`.`bron_bez` AS `bron_bez`,(((`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 `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`, '' AS `date`,'{1}' AS `N_kvit`,'' AS `FIO`,'' AS `City`,'' AS `Kol_dney`,'' AS `Kol_czel`,'' AS `zhitie`,'' AS `zhitie_bez`,'' AS `parkovka`,'Итого наличность:' AS `Zakazczik`,'' AS `bron`,'' AS `bron_bez`,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 `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`,'' AS `date`,'{1}' AS `N_kvit`,'' AS `FIO`,'' AS `City`,'' AS `Kol_dney`,'' AS `Kol_czel`,'' AS `zhitie`,'' AS `zhitie_bez`,'' AS `parkovka`, 'Безналичность:' AS `Zakazczik`,'' AS `bron`,'' AS `bron_bez`,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 `N_kvit` >= '{0}' and `N_kvit` <= '{1}' ORDER BY `N_kvit`", textBoxKvN.Text, textBoxKvK.Text);
string sql = String.Format("select `rasselenie`.`id` AS `id`,cast(`rasselenie`.`date` as char charset utf8) AS `date`,`rasselenie`.`N_kvit` AS `N_kvit`,`rasselenie`.`FiO` AS `FIO`,`rasselenie`.`City` AS `City`,`rasselenie`.`Kol_dney` AS `Kol_dney`,`rasselenie`.`Kol_czel` AS `Kol_czel`,`rasselenie`.`zhitie` AS `zhitie`,`rasselenie`.`parkovka` AS `parkovka`,`rasselenie`.`zakazczik` AS `Zakazczik`,`rasselenie`.`bron` AS `bron`,`rasselenie`.`bron_bez` AS `bron_bez`,((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) + `rasselenie`.`bron`) AS `cena` from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) WHERE `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`, '' AS `date`,'{1}' AS `N_kvit`,'' AS `FIO`,'' AS `City`,'' AS `Kol_dney`,'' AS `Kol_czel`,'' AS `zhitie`,'' AS `parkovka`,'Итого наличность:' AS `Zakazczik`,'' AS `bron`,'' AS `bron_bez`,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 `N_kvit` >= '{0}' and `N_kvit` <= '{1}' union all select `rasselenie`.`id` AS `id`,'' AS `date`,'{1}' AS `N_kvit`,'' AS `FIO`,'' AS `City`,'' AS `Kol_dney`,'' AS `Kol_czel`,'' AS `zhitie`,'' AS `parkovka`, 'Безналичность:' AS `Zakazczik`,'' AS `bron`,'' AS `bron_bez`,sum(ifnull(`rasselenie`.`bron_bez`,0)) AS `cena` from `rasselenie` WHERE `N_kvit` >= '{0}' and `N_kvit` <= '{1}' ORDER BY `N_kvit`", textBoxKvN.Text, textBoxKvK.Text);
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewRasselenie.Items.Clear();
queueRasselenie.Clear();
_Rasselenie t = new _Rasselenie();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["id"].ToString().Trim(), 0);
t.id = int.Parse(dataRow["id"].ToString());
item1.SubItems.Add(dataRow["date"].ToString().Trim());
t.date = dataRow["date"].ToString().Trim();
item1.SubItems.Add(dataRow["N_kvit"].ToString().Trim());
t.N_kvit = dataRow["N_kvit"].ToString().Trim();
item1.SubItems.Add(dataRow["FiO"].ToString().Trim());
t.FiO = dataRow["FiO"].ToString().Trim();
item1.SubItems.Add(dataRow["City"].ToString().Trim());
t.City = dataRow["City"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_dney"].ToString().Trim());
t.Kol_dney = dataRow["Kol_dney"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
t.Kol_czel = dataRow["Kol_czel"].ToString().Trim();
item1.SubItems.Add(dataRow["zhitie"].ToString().Trim());
t.zhitie = dataRow["zhitie"].ToString().Trim();
item1.SubItems.Add(dataRow["parkovka"].ToString().Trim());
t.parkovka = dataRow["parkovka"].ToString().Trim();
item1.SubItems.Add(dataRow["bron"].ToString().Trim());
t.bron = dataRow["bron"].ToString().Trim();
item1.SubItems.Add(dataRow["bron_bez"].ToString().Trim());
t.bron_bez = dataRow["bron_bez"].ToString().Trim();
item1.SubItems.Add(dataRow["zakazczik"].ToString().Trim());
t.zakazczik = dataRow["zakazczik"].ToString().Trim();
item1.SubItems.Add(dataRow["cena"].ToString().Trim());
t.summa = dataRow["cena"].ToString().Trim();
listViewRasselenie.Items.Add(item1);
listViewRasselenie.Items[listViewRasselenie.Items.Count - 1].Tag = dataRow["id"].ToString();
queueRasselenie.Enqueue(t);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}