private void comboBoxZakazczik_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBoxZakazczik.SelectedIndex == -1)
{
return;
}
m_zakazczik = (_Zakazczik)comboBoxZakazczik.SelectedItem;
}
public void refreshZakazczik()
{
listViewZakazczik.Items.Clear();
queueZakazczik.Clear();
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = "SELECT `id`, `name`, `ownership`, `contact_person`, `phone`, `email`, `icq`, `address`, `site` FROM `zakazczik` ORDER BY `name`";
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewZakazczik.Items.Clear();
queueZakazczik.Clear();
_Zakazczik t = new _Zakazczik();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["id"].ToString().Trim());
t.id = dataRow["id"].ToString().Trim();
item1.SubItems.Add(dataRow["name"].ToString().Trim());
t.name = dataRow["name"].ToString().Trim();
item1.SubItems.Add(dataRow["ownership"].ToString().Trim());
t.ownership = dataRow["ownership"].ToString().Trim();
item1.SubItems.Add(dataRow["contact_person"].ToString().Trim());
t.contact_person = dataRow["contact_person"].ToString().Trim();
item1.SubItems.Add(dataRow["phone"].ToString().Trim());
t.phone = dataRow["phone"].ToString().Trim();
item1.SubItems.Add(dataRow["email"].ToString().Trim());
t.email = dataRow["email"].ToString().Trim();
item1.SubItems.Add(dataRow["icq"].ToString().Trim());
t.icq = dataRow["icq"].ToString().Trim();
item1.SubItems.Add(dataRow["address"].ToString().Trim());
t.address = dataRow["address"].ToString().Trim();
item1.SubItems.Add(dataRow["site"].ToString().Trim());
t.site = dataRow["site"].ToString().Trim();
listViewZakazczik.Items.Add(item1);
listViewZakazczik.Items[listViewZakazczik.Items.Count - 1].Tag = dataRow["id"].ToString();
queueZakazczik.Enqueue(t);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
public void refreshZakazczik()
{
string N_zakazczika = "";
if (comboBoxZakazczik.SelectedIndex != -1)
{
N_zakazczika = m_zakazczik.id.ToString();
}
comboBoxZakazczik.Items.Clear();
queueZakazczik.Clear();
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = "SELECT `id`, `name` FROM `zakazczik` ORDER BY `name`";
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
_Zakazczik c = new _Zakazczik();
foreach (DataRow dataRow in dataTable.Rows)
{
c.id = dataRow["id"].ToString();
c.name = dataRow["name"].ToString().Trim();
comboBoxZakazczik.Items.Add(c);
if (N_zakazczika != "" && N_zakazczika == c.id.ToString())
{
comboBoxZakazczik.SelectedItem = c;
}
queueZakazczik.Enqueue(c);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
private void listViewZakazczik_SelectedIndexChanged(object sender, EventArgs e)
{
buttonApply.Enabled = true;
buttonDelete.Enabled = true;
textBoxName.Enabled = true;
textBoxTel.Enabled = true;
textBoxOwnership.Enabled = true;
textBoxMail.Enabled = true;
textBoxIcq.Enabled = true;
textBoxAddress.Enabled = true;
textBoxContactPerson.Enabled = true;
textBoxSite.Enabled = true;
textBoxZakazczik.Enabled = true;
dateTimePicker1.Enabled = true;
dateTimePicker2.Enabled = true;
bNew = false;
if (listViewZakazczik.FocusedItem == null)
{
return;
}
int k = queueZakazczik.Count;
for (int i = 0; i < k; i++)
{
m_zakazczik = (_Zakazczik)queueZakazczik.Dequeue();
if (m_zakazczik.id == (string)listViewZakazczik.Items[listViewZakazczik.FocusedItem.Index].Tag)
{
textBoxName.Text = m_zakazczik.name;
textBoxTel.Text = m_zakazczik.phone;
textBoxOwnership.Text = m_zakazczik.ownership;
textBoxMail.Text = m_zakazczik.email;
textBoxIcq.Text = m_zakazczik.icq;
textBoxAddress.Text = m_zakazczik.address;
textBoxSite.Text = m_zakazczik.site;
textBoxContactPerson.Text = m_zakazczik.contact_person;
queueZakazczik.Enqueue(m_zakazczik);
break;
}
;
queueZakazczik.Enqueue(m_zakazczik);
}
}
private void listViewZhurnal_SelectedIndexChanged(object sender, EventArgs e)
{
buttonApply.Enabled = true;
buttonDelete.Enabled = true;
comboBoxEkskursovod.SelectedIndex = -1;
comboBoxEkskursovod.Enabled = true;
comboBoxEkskursija.SelectedIndex = -1;
comboBoxEkskursija.Enabled = true;
comboBoxZakazczik.SelectedIndex = -1;
comboBoxZakazczik.Enabled = true;
textBoxKvN.Text = "";
textBoxKvN.Enabled = true;
textBoxKvK.Text = "";
textBoxKvK.Enabled = true;
textBoxKolCzel.Text = "";
textBoxKolCzel.Enabled = true;
dateTimePickerDate.Enabled = true;
bNew = false;
if (listViewZhurnal.FocusedItem == null)
{
return;
}
int k = queueZhurnal.Count;
for (int i = 0; i < k; i++)
{
m_zhurnal = (_Zhurnal)queueZhurnal.Dequeue();
if (m_zhurnal.id.ToString() == (string)listViewZhurnal.Items[listViewZhurnal.FocusedItem.Index].Tag)
{
comboBoxEkskursija.Text = m_ekskursii.id;
comboBoxEkskursovod.Text = m_zhurnal.fio;
comboBoxZakazczik.Text = m_zhurnal.zakaz;
textBoxKvN.Text = m_zhurnal.kvn;
textBoxKvK.Text = m_zhurnal.kvk;
textBoxKolCzel.Text = m_zhurnal.kolczel;
dateTimePickerDate.Value = DateTime.Parse(m_zhurnal.date);
//фигня для comboboxов
_Ekskursii c;
int k2 = queueEkskursii.Count;
for (int i2 = 0; i2 < k2; i2++)
{
c = (_Ekskursii)queueEkskursii.Dequeue();
if (c.id.ToString() == m_zhurnal.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);
}
_Zakazczik d;
k2 = queueZakazczik.Count;
for (int i2 = 0; i2 < k2; i2++)
{
d = (_Zakazczik)queueZakazczik.Dequeue();
if (d.id.ToString() == m_zhurnal.zakaz.ToString())
{
for (int iii = 0; iii < comboBoxZakazczik.Items.Count; iii++)
{
if (((_Zakazczik)comboBoxZakazczik.Items[iii]).id == d.id)
{
comboBoxZakazczik.SelectedIndex = iii;
m_zakazczik = d;
break;
}
}
}
;
queueZakazczik.Enqueue(d);
}
_Ekskursovody a;
k2 = queueEkskursovod.Count;
for (int i2 = 0; i2 < k2; i2++)
{
a = (_Ekskursovody)queueEkskursovod.Dequeue();
if (a.name.ToString() == m_zhurnal.fio.ToString())
{
for (int iii = 0; iii < comboBoxEkskursovod.Items.Count; iii++)
{
if (((_Ekskursovody)comboBoxEkskursovod.Items[iii]).id == a.id)
{
comboBoxEkskursovod.SelectedIndex = iii;
m_ekskursovody = a;
break;
}
}
}
;
queueEkskursovod.Enqueue(a);
}
queueZhurnal.Enqueue(m_zhurnal);
break;
}
;
queueZhurnal.Enqueue(m_zhurnal);
}
}
//отчеты по заказчикам
public void refreshReportsZakaz()
{
listViewZakazczik.Items.Clear();
queueZakazczik.Clear();
listViewZakazczik.Columns.Clear();
listViewZakazczik.Columns.Add("Дата", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns.Add("№ экскурсии/проживание", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns.Add("Заказчик", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns.Add("Стоимость", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns[0].Width = 150;
listViewZakazczik.Columns[1].Width = 150;
listViewZakazczik.Columns[2].Width = 150;
listViewZakazczik.Columns[3].Width = 150;
listViewZakazczik.Columns[4].Width = 150;
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = string.Format("select `zhurnal`.`date` AS `date`,`zhurnal`.`N_ekskursii` AS `eksn`,`zhurnal`.`zakazczik` AS `imia`,(`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))AS `stoimost`,((`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`.`zakazczik` = '{0}') and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}')) union all select `plategki`.`date` AS `date`,`plategki`.`N_ekskursii` AS `eksn`,`plategki`.`zakazczik` AS `imia`,(`ekskursii`.`stoimost` * `plategki`.`Kol_czel`) AS `stoimost`,`plategki`.`Kol_czel` AS `Kol_czel` from (`plategki` join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`plategki`.`zakazczik` = '{0}') and (`plategki`.`date` >= '{1}') and (`plategki`.`date` <= '{2}')) union all select `rasselenie`.`date` AS `date`,`rasselenie`.`zhitie` AS `eksn`,`rasselenie`.`zakazczik` AS `imia`,(((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) AS `stoimost`,`rasselenie`.`Kol_czel` AS `Kol_czel` from (`rasselenie` join `zhitie` on((`zhitie`.`nazvanie` = `rasselenie`.`zhitie`))) where ((`rasselenie`.`zakazczik` = '{0}') and (`rasselenie`.`date` >= '{1}') and (`rasselenie`.`date` <= '{2}')) union all select '' AS `date`,'' AS `eksn`,'общая сумма' AS `imia`,(((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 ((`zhurnal`.`zakazczik` = '{0}')and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}'))) + (select ifnull(sum((`ekskursii`.`stoimost` * `plategki`.`Kol_czel`)),0) from (`plategki` join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`plategki`.`zakazczik` = '{0}') and (`plategki`.`date` >= '{1}') and (`plategki`.`date` <= '{2}')))) + (select ifnull(sum((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`)),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where ((`rasselenie`.`zakazczik` = '{0}')and (`rasselenie`.`date` >= '{1}') and (`rasselenie`.`date` <= '{2}')))) AS `stoimost`,(((select ifnull(sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)),0) from `zhurnal` where ((`zhurnal`.`zakazczik` = '{0}')and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}')))+ (select ifnull(sum(`plategki`.`Kol_czel`),0) from `plategki` where ((`plategki`.`zakazczik` = '{0}')and (`plategki`.`date` >= '{1}') and (`plategki`.`date` <= '{2}')))) + (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from `rasselenie` where ((`rasselenie`.`zakazczik` = '{0}')and (`rasselenie`.`date` >= '{1}') and (`rasselenie`.`date` <= '{2}')))) AS `Kol_czel`", textBoxZakazczik.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];
listViewZakazczik.Items.Clear();
queueZakazczik.Clear();
_Zakazczik v = new _Zakazczik();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["date"].ToString().Trim());
v.date = dataRow["date"].ToString().Trim();
item1.SubItems.Add(dataRow["eksn"].ToString().Trim());
v.eksn = dataRow["eksn"].ToString().Trim();
item1.SubItems.Add(dataRow["imia"].ToString().Trim());
v.imia = dataRow["imia"].ToString().Trim();
item1.SubItems.Add(dataRow["stoimost"].ToString().Trim());
v.stoimost = dataRow["stoimost"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
v.kol_czel = dataRow["Kol_czel"].ToString().Trim();
listViewZakazczik.Items.Add(item1);
listViewZakazczik.Items[listViewZakazczik.Items.Count - 1].Tag = dataRow["date"].ToString();
queueZakazczik.Enqueue(v);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
private void listViewZakazczik_SelectedIndexChanged(object sender, EventArgs e)
{
buttonApply.Enabled = true;
buttonDelete.Enabled = true;
textBoxName.Enabled = true;
textBoxTel.Enabled = true;
textBoxOwnership.Enabled = true;
textBoxMail.Enabled = true;
textBoxIcq.Enabled = true;
textBoxAddress.Enabled = true;
textBoxContactPerson.Enabled = true;
textBoxSite.Enabled = true;
textBoxZakazczik.Enabled = true;
dateTimePicker1.Enabled = true;
dateTimePicker2.Enabled = true;
bNew = false;
if (listViewZakazczik.FocusedItem == null)
return;
int k = queueZakazczik.Count;
for (int i = 0; i < k; i++)
{
m_zakazczik = (_Zakazczik)queueZakazczik.Dequeue();
if (m_zakazczik.id == (string)listViewZakazczik.Items[listViewZakazczik.FocusedItem.Index].Tag)
{
textBoxName.Text = m_zakazczik.name;
textBoxTel.Text = m_zakazczik.phone;
textBoxOwnership.Text = m_zakazczik.ownership;
textBoxMail.Text = m_zakazczik.email;
textBoxIcq.Text = m_zakazczik.icq;
textBoxAddress.Text = m_zakazczik.address;
textBoxSite.Text = m_zakazczik.site;
textBoxContactPerson.Text = m_zakazczik.contact_person;
queueZakazczik.Enqueue(m_zakazczik);
break;
};
queueZakazczik.Enqueue(m_zakazczik);
}
}
public void refreshZakazczik()
{
listViewZakazczik.Items.Clear();
queueZakazczik.Clear();
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = "SELECT `id`, `name`, `ownership`, `contact_person`, `phone`, `email`, `icq`, `address`, `site` FROM `zakazczik` ORDER BY `name`";
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
listViewZakazczik.Items.Clear();
queueZakazczik.Clear();
_Zakazczik t = new _Zakazczik();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["id"].ToString().Trim());
t.id = dataRow["id"].ToString().Trim();
item1.SubItems.Add(dataRow["name"].ToString().Trim());
t.name = dataRow["name"].ToString().Trim();
item1.SubItems.Add(dataRow["ownership"].ToString().Trim());
t.ownership = dataRow["ownership"].ToString().Trim();
item1.SubItems.Add(dataRow["contact_person"].ToString().Trim());
t.contact_person = dataRow["contact_person"].ToString().Trim();
item1.SubItems.Add(dataRow["phone"].ToString().Trim());
t.phone = dataRow["phone"].ToString().Trim();
item1.SubItems.Add(dataRow["email"].ToString().Trim());
t.email = dataRow["email"].ToString().Trim();
item1.SubItems.Add(dataRow["icq"].ToString().Trim());
t.icq = dataRow["icq"].ToString().Trim();
item1.SubItems.Add(dataRow["address"].ToString().Trim());
t.address = dataRow["address"].ToString().Trim();
item1.SubItems.Add(dataRow["site"].ToString().Trim());
t.site = dataRow["site"].ToString().Trim();
listViewZakazczik.Items.Add(item1);
listViewZakazczik.Items[listViewZakazczik.Items.Count - 1].Tag = dataRow["id"].ToString();
queueZakazczik.Enqueue(t);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
//отчеты по заказчикам
public void refreshReportsZakaz()
{
listViewZakazczik.Items.Clear();
queueZakazczik.Clear();
listViewZakazczik.Columns.Clear();
listViewZakazczik.Columns.Add("Дата", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns.Add("№ экскурсии/проживание", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns.Add("Заказчик", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns.Add("Стоимость", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns.Add("Кол-во человек", -2, HorizontalAlignment.Left);
listViewZakazczik.Columns[0].Width = 150;
listViewZakazczik.Columns[1].Width = 150;
listViewZakazczik.Columns[2].Width = 150;
listViewZakazczik.Columns[3].Width = 150;
listViewZakazczik.Columns[4].Width = 150;
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = string.Format("select `zhurnal`.`date` AS `date`,`zhurnal`.`N_ekskursii` AS `eksn`,`zhurnal`.`zakazczik` AS `imia`,(`ekskursii`.`stoimost` * ((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1))AS `stoimost`,((`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`.`zakazczik` = '{0}') and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}')) union all select `plategki`.`date` AS `date`,`plategki`.`N_ekskursii` AS `eksn`,`plategki`.`zakazczik` AS `imia`,(`ekskursii`.`stoimost` * `plategki`.`Kol_czel`) AS `stoimost`,`plategki`.`Kol_czel` AS `Kol_czel` from (`plategki` join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`plategki`.`zakazczik` = '{0}') and (`plategki`.`date` >= '{1}') and (`plategki`.`date` <= '{2}')) union all select `rasselenie`.`date` AS `date`,`rasselenie`.`zhitie` AS `eksn`,`rasselenie`.`zakazczik` AS `imia`,(((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`) AS `stoimost`,`rasselenie`.`Kol_czel` AS `Kol_czel` from (`rasselenie` join `zhitie` on((`zhitie`.`nazvanie` = `rasselenie`.`zhitie`))) where ((`rasselenie`.`zakazczik` = '{0}') and (`rasselenie`.`date` >= '{1}') and (`rasselenie`.`date` <= '{2}')) union all select '' AS `date`,'' AS `eksn`,'общая сумма' AS `imia`,(((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 ((`zhurnal`.`zakazczik` = '{0}')and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}'))) + (select ifnull(sum((`ekskursii`.`stoimost` * `plategki`.`Kol_czel`)),0) from (`plategki` join `ekskursii` on((`plategki`.`N_ekskursii` = `ekskursii`.`N_ekskursii`))) where ((`plategki`.`zakazczik` = '{0}') and (`plategki`.`date` >= '{1}') and (`plategki`.`date` <= '{2}')))) + (select ifnull(sum((((`rasselenie`.`Kol_czel` * `zhitie`.`cena`) * `rasselenie`.`Kol_dney`) + `rasselenie`.`parkovka`)),0) from (`rasselenie` join `zhitie` on((`rasselenie`.`zhitie` = `zhitie`.`nazvanie`))) where ((`rasselenie`.`zakazczik` = '{0}')and (`rasselenie`.`date` >= '{1}') and (`rasselenie`.`date` <= '{2}')))) AS `stoimost`,(((select ifnull(sum(((`zhurnal`.`N_kvit_koniec` - `zhurnal`.`N_kvit_nach`) + 1)),0) from `zhurnal` where ((`zhurnal`.`zakazczik` = '{0}')and (`zhurnal`.`date` >= '{1}') and (`zhurnal`.`date` <= '{2}')))+ (select ifnull(sum(`plategki`.`Kol_czel`),0) from `plategki` where ((`plategki`.`zakazczik` = '{0}')and (`plategki`.`date` >= '{1}') and (`plategki`.`date` <= '{2}')))) + (select ifnull(sum(`rasselenie`.`Kol_czel`),0) from `rasselenie` where ((`rasselenie`.`zakazczik` = '{0}')and (`rasselenie`.`date` >= '{1}') and (`rasselenie`.`date` <= '{2}')))) AS `Kol_czel`", textBoxZakazczik.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];
listViewZakazczik.Items.Clear();
queueZakazczik.Clear();
_Zakazczik v = new _Zakazczik();
foreach (DataRow dataRow in dataTable.Rows)
{
ListViewItem item1 = new ListViewItem(dataRow["date"].ToString().Trim());
v.date = dataRow["date"].ToString().Trim();
item1.SubItems.Add(dataRow["eksn"].ToString().Trim());
v.eksn = dataRow["eksn"].ToString().Trim();
item1.SubItems.Add(dataRow["imia"].ToString().Trim());
v.imia = dataRow["imia"].ToString().Trim();
item1.SubItems.Add(dataRow["stoimost"].ToString().Trim());
v.stoimost= dataRow["stoimost"].ToString().Trim();
item1.SubItems.Add(dataRow["Kol_czel"].ToString().Trim());
v.kol_czel= dataRow["Kol_czel"].ToString().Trim();
listViewZakazczik.Items.Add(item1);
listViewZakazczik.Items[listViewZakazczik.Items.Count - 1].Tag = dataRow["date"].ToString();
queueZakazczik.Enqueue(v);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}
private void listViewZhurnal_SelectedIndexChanged(object sender, EventArgs e)
{
buttonApply.Enabled = true;
buttonDelete.Enabled = true;
comboBoxEkskursovod.SelectedIndex = -1;
comboBoxEkskursovod.Enabled = true;
comboBoxEkskursija.SelectedIndex = -1;
comboBoxEkskursija.Enabled = true;
comboBoxZakazczik.SelectedIndex = -1;
comboBoxZakazczik.Enabled = true;
textBoxKvN.Text = "";
textBoxKvN.Enabled = true;
textBoxKvK.Text = "";
textBoxKvK.Enabled = true;
textBoxKolCzel.Text = "";
textBoxKolCzel.Enabled = true;
dateTimePickerDate.Enabled = true;
bNew = false;
if (listViewZhurnal.FocusedItem == null)
return;
int k = queueZhurnal.Count;
for (int i = 0; i < k; i++)
{
m_zhurnal = (_Zhurnal)queueZhurnal.Dequeue();
if (m_zhurnal.id.ToString() == (string)listViewZhurnal.Items[listViewZhurnal.FocusedItem.Index].Tag)
{
comboBoxEkskursija.Text = m_ekskursii.id;
comboBoxEkskursovod.Text = m_zhurnal.fio;
comboBoxZakazczik.Text = m_zhurnal.zakaz;
textBoxKvN.Text = m_zhurnal.kvn;
textBoxKvK.Text = m_zhurnal.kvk;
textBoxKolCzel.Text = m_zhurnal.kolczel;
dateTimePickerDate.Value = DateTime.Parse(m_zhurnal.date);
//фигня для comboboxов
_Ekskursii c;
int k2 = queueEkskursii.Count;
for (int i2 = 0; i2 < k2; i2++)
{
c = (_Ekskursii)queueEkskursii.Dequeue();
if (c.id.ToString() == m_zhurnal.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);
}
_Zakazczik d;
k2 = queueZakazczik.Count;
for (int i2 = 0; i2 < k2; i2++)
{
d = (_Zakazczik)queueZakazczik.Dequeue();
if (d.id.ToString() == m_zhurnal.zakaz.ToString())
{
for (int iii = 0; iii < comboBoxZakazczik.Items.Count; iii++)
{
if (((_Zakazczik)comboBoxZakazczik.Items[iii]).id == d.id)
{
comboBoxZakazczik.SelectedIndex = iii;
m_zakazczik = d;
break;
}
}
};
queueZakazczik.Enqueue(d);
}
_Ekskursovody a;
k2 = queueEkskursovod.Count;
for (int i2 = 0; i2 < k2; i2++)
{
a = (_Ekskursovody)queueEkskursovod.Dequeue();
if (a.name.ToString() == m_zhurnal.fio.ToString())
{
for (int iii = 0; iii < comboBoxEkskursovod.Items.Count; iii++)
{
if (((_Ekskursovody)comboBoxEkskursovod.Items[iii]).id == a.id)
{
comboBoxEkskursovod.SelectedIndex = iii;
m_ekskursovody = a;
break;
}
}
};
queueEkskursovod.Enqueue(a);
}
queueZhurnal.Enqueue(m_zhurnal);
break;
};
queueZhurnal.Enqueue(m_zhurnal);
}
}
private void comboBoxZakazczik_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBoxZakazczik.SelectedIndex == -1)
return;
m_zakazczik = (_Zakazczik)comboBoxZakazczik.SelectedItem;
}
public void refreshZakazczik()
{
string N_zakazczika = "";
if (comboBoxZakazczik.SelectedIndex != -1)
{
N_zakazczika = m_zakazczik.id.ToString();
}
comboBoxZakazczik.Items.Clear();
queueZakazczik.Clear();
try
{
mainWin.m_dbConnector.Lock();
MySqlConnection conn = mainWin.m_dbConnector.getMySqlConnection();
string sql = "SELECT `id`, `name` FROM `zakazczik` ORDER BY `name`";
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = new MySqlCommand(sql, conn);
DataSet dataSet = new DataSet();
myAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
_Zakazczik c = new _Zakazczik();
foreach (DataRow dataRow in dataTable.Rows)
{
c.id = dataRow["id"].ToString();
c.name = dataRow["name"].ToString().Trim();
comboBoxZakazczik.Items.Add(c);
if (N_zakazczika != "" && N_zakazczika == c.id.ToString())
{
comboBoxZakazczik.SelectedItem = c;
}
queueZakazczik.Enqueue(c);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
mainWin.m_dbConnector.Unlock();
}
}