//получение данных фио клиентов и риэлторов
private string[] get_fio(string[] time_arr)
{
for (int i = 0; i < time_arr.Length; i++)
{
@base @base = new @base("");
int id_ag = Convert.ToInt32(time_arr[i].Substring(time_arr[i].IndexOf('Р')).Replace("Риэлтор:", "").Remove(time_arr[i].Substring(time_arr[i].IndexOf('Р')).Replace("Риэлтор:", "").IndexOf(" ")));
int id_kl = Convert.ToInt32(time_arr[i].Substring(time_arr[i].LastIndexOf(':') + 1).Replace("Клиент:", "").Remove(time_arr[i].Substring(time_arr[i].LastIndexOf(':') + 1).Replace("Клиент:", "").IndexOf(" ")));
@base.smena_zaprosa("select distinct agent.Id, man.fam, man.name, man.otch from man, klient, agent where man.dop_info = " + id_ag + " and klient.Id <> " + id_ag + " and man.dop_info = agent.Id");
string per = @base.vuvod();
arr[i] = arr[i].Replace("Риэлтор:" + id_ag.ToString(), "Риэлтор:" + per.Replace("&", ""));
@base.smena_zaprosa("select distinct klient.Id, man.fam, man.name, man.otch from man, klient, agent where man.dop_info = " + id_kl + " and agent.Id <> " + id_kl + " and man.dop_info = klient.Id");
per = @base.vuvod();
time_arr[i] = time_arr[i].Replace("Клиент:" + id_kl.ToString(), "Клиент:" + per.Replace("&", ""));
}
return(time_arr);
}
//получение данных из БД
private void get_data(ComboBox cmb1, ComboBox cmb2)
{
@base @base = new @base("select distinct agent.Id, man.fam, man.name, man.otch from man, agent, klient where man.dop_info = agent.Id and agent.Id <> klient.Id");
znachenia = @base.vuvod();
zapis_v_combobox(cmb1);
@base.smena_zaprosa("select distinct klient.Id, man.fam, man.name, man.otch from man, klient, agent where man.dop_info = klient.Id and agent.Id <> klient.Id");
znachenia = @base.vuvod();
zapis_v_combobox(cmb2);
}
//обновление данных
private void reboot(ComboBox cmb1, ComboBox cmb2)
{
cmb1.Items.Clear();
string s = "";
@base @base = new @base(izm_zapr("select distinct potr.Id, potr.mincena, potr.maxcena, potr.agent, potr.klient, potr.city, potr.street, filter_h.minetag, filter_h.maxetag, filter_h.minrooms, filter_h.maxrooms, filter_h.mins, filter_h.maxs from potr, filter_h, sdelka where potr.dop_info = filter_h.Id and potr.obj = N'Дом' and not potr.Id in (select potr from sdelka)"));
s += @base.vuvod_zakazov("house");
@base.smena_zaprosa(izm_zapr("select distinct potr.Id, potr.mincena, potr.maxcena, potr.agent, potr.klient, potr.city, potr.street, filter_kw.minetag, filter_kw.maxetag, filter_kw.minrooms, filter_kw.maxrooms, filter_kw.mins, filter_kw.maxs from potr, filter_kw, sdelka where potr.dop_info = filter_kw.Id and potr.obj = N'Квартира' and not potr.Id in (select potr from sdelka)"));
s += @base.vuvod_zakazov("kw");
@base.smena_zaprosa(izm_zapr("select distinct potr.Id, potr.mincena, potr.maxcena, potr.agent, potr.klient, potr.city, potr.street, filter_l.mins, filter_l.maxs from potr, filter_l, sdelka where potr.dop_info = filter_l.Id and potr.obj = N'Земля' and not potr.Id in (select potr from sdelka)"));
s += @base.vuvod_zakazov("land");
cmb1.Items.Clear();
if (s == "")
{
cmb1.Items.Add("Отсутствуют данные в БД");
}
else
{
s = s.Remove(s.Length - 1);
arr = s.Split('&');
cmb1.Items.AddRange(get_fio(arr));
}
s = "";
arr = null;
cmb2.Items.Clear();
@base.smena_zaprosa(izm_zapr("select distinct predlog.Id, obj.city, obj.street, obj.nm_h, house.etag, house.rooms, house.s, predlog.agent, predlog.klient from obj, house, predlog, sdelka where obj.dop_inf = house.Id and obj.Id = predlog.obj and obj.nm_kw is null and not predlog.Id in (select predl from sdelka)"));
s += @base.vuvod_obj("house", true);
@base.smena_zaprosa(izm_zapr("select distinct predlog.Id, obj.city, obj.street, obj.nm_h, obj.nm_kw, kw.etag, kw.rooms, kw.s, predlog.agent, predlog.klient from obj, kw, predlog, sdelka where obj.dop_inf = kw.Id and obj.Id = predlog.obj and obj.nm_kw is not null and not predlog.Id in (select predl from sdelka)"));
s += @base.vuvod_obj("kw", true);
@base.smena_zaprosa(izm_zapr("select distinct predlog.Id, obj.city, obj.street, land.s, predlog.agent, predlog.klient from obj, land, predlog, sdelka where obj.dop_inf = land.Id and obj.Id = predlog.obj and obj.nm_kw is null and obj.nm_h is null and not predlog.Id in (select predl from sdelka)"));
s += @base.vuvod_obj("land", true);
if (s == "")
{
cmb2.Items.Add("Отсутствуют данные в БД");
}
else
{
s = s.Remove(s.Length - 1);
arr = s.Split('&');
cmb2.Items.AddRange(get_cena(arr));
}
}
//получение данных
private void get_data(ComboBox cmb1, ComboBox cmb2, ComboBox cmb3)
{
@base @base = new @base("select distinct agent.Id, man.fam, man.name, man.otch from man, agent, klient where man.dop_info = agent.Id and agent.Id <> klient.Id");
znachenia = @base.vuvod();
zapis_v_combobox(cmb1);
@base.smena_zaprosa("select distinct klient.Id, man.fam, man.name, man.otch from man, klient, agent where man.dop_info = klient.Id and agent.Id <> klient.Id");
znachenia = @base.vuvod();
zapis_v_combobox(cmb2);
@base.smena_zaprosa("select obj.Id, obj.city, obj.street, obj.nm_h, obj.nm_kw, kw.etag, kw.rooms, kw.s from obj, kw where obj.dop_inf = kw.Id");
znachenia = @base.vuvod_obj("kw", false);
@base.smena_zaprosa("select obj.Id, obj.city, obj.street, obj.nm_h, house.etag, house.rooms, house.s from obj, house where obj.dop_inf = house.Id");
znachenia += @base.vuvod_obj("house", false);
@base.smena_zaprosa("select obj.Id, obj.city, obj.street, land.s from obj, land where obj.dop_inf = land.Id");
znachenia += @base.vuvod_obj("land", false);
if (znachenia == "")
{
znachenia = "Отсутствую данные в БД";
}
zapis_v_combobox(cmb3);
}
//удаление данных
private void delete(TextBox txt1, TextBox txt2, string type)
{
@base @base = new @base("select * from sdelka, potr where sdelka.potr = " + txt2.Text + "");
Boolean rez = @base.proverka_znachenei_v_bd();
if (rez == false)
{
@base.smena_zaprosa("delete from " + type + " where Id = " + txt1.Text + " delete from potr where Id = " + txt2.Text + "");
@base.zapis_v_bd();
MessageBox.Show("Удаление объекта завершено");
update_data();
}
else
{
MessageBox.Show("Данный объект учавствует в зделке", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//удаление объекта
private void del(string type, string id_obj, string id_dopinf)
{
Boolean rez1;
@base @base = new @base("select obj.Id from obj, predlog where predlog.obj = " + id_ob + " and obj.Id = predlog.obj");
rez1 = @base.proverka_znachenei_v_bd();
if (rez1 == true)
{
MessageBox.Show("Данный объект доступен для покупки", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
@base.smena_zaprosa("delete from " + type + " where Id = " + id_dopinf + " delete from obj where Id = " + id_obj + " and dop_inf = " + id_dopinf + "");
@base.zapis_v_bd();
reboot();
MessageBox.Show("Удаление объекта завершено", "Сообщение");
}
}
private string[] get_cena(string[] time_arr)
{
string time_str;
@base @base = new @base("");
for (int i = 0; i < time_arr.Length; i++)
{
try
{
time_str = "";
@base.smena_zaprosa("select cena from predlog where Id = " + time_arr[i].Remove(time_arr[i].IndexOf(" ")));
time_str = @base.get_cena();
time_arr[i] = time_arr[i].Insert(time_arr[i].Contains("Э") == true ? time_arr[i].IndexOf("Э") : time_arr[i].IndexOf("П"), " " + time_str + " ");
}
catch
{
continue;
}
}
return(time_arr);
}
//удаление данных из БД
private void toolStripButton1_Click(object sender, EventArgs e)
{
open_txb();
id_kl = idTextBox.Text;
id_mn = idTextBox1.Text;
close_txb();
@base @base = new @base("select klient.Id from klient, predlog, potr where predlog.klient = " + id_kl + " or potr.klient = " + id_kl + "");
Boolean per = @base.proverka_znachenei_v_bd();
if (per == false)
{
@base.smena_zaprosa("delete from klient where Id = " + id_kl + " delete from man where Id = " + id_mn + " and dop_info = " + id_kl + "");
@base.zapis_v_bd();
reboot();
MessageBox.Show("Удаление клиента завершено", "Сообщение");
}
else
{
MessageBox.Show("Данный клиент принимает участие в сделке и не может быть удален.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//удаление данных
private void toolStripButton1_Click(object sender, EventArgs e)
{
open_txt();
@base @base = new @base("select * from predlog, sdelka where predlog.Id = sdelka.predl and predlog.Id = " + idTextBox.Text + "");
clos_txt();
Boolean rez = @base.proverka_znachenei_v_bd();
if (rez == true)
{
MessageBox.Show("Данное предложение учавствует в зделке", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
open_txt();
@base.smena_zaprosa("delete from predlog where Id = " + idTextBox.Text + "");
clos_txt();
@base.zapis_v_bd();
MessageBox.Show("Удаление завершено");
this.predlogTableAdapter.Fill(this.baseDataSet.predlog);
}
}
//получение данных из БД и запись из в comboBox3, comboBox4
private void tab2()
{
reboot(comboBox3, comboBox4);
if (idTextBox.Text == "")
{
comboBox3.Enabled = false;
comboBox4.Enabled = false;
sdelkaBindingNavigator.Enabled = false;
}
else
{
string s = "";
bool b;
@base @base = new @base("select predlog.Id, obj.city, obj.street, obj.nm_h, house.etag, house.rooms, house.s, predlog.agent, predlog.klient from obj, house, predlog where obj.dop_inf = house.Id and obj.Id = predlog.obj and obj.nm_kw is null and predlog.Id = " + predlTextBox.Text + "");
b = @base.proverka_znachenei_v_bd();
if (b == true)
{
s = @base.vuvod_obj("house", true);
}
else
{
@base.smena_zaprosa("select predlog.Id, obj.city, obj.street, obj.nm_h, obj.nm_kw, kw.etag, kw.rooms, kw.s, predlog.agent, predlog.klient from obj, kw, predlog where obj.dop_inf = kw.Id and obj.Id = predlog.obj and obj.nm_kw is not null and predlog.Id = " + predlTextBox.Text + "");
b = @base.proverka_znachenei_v_bd();
if (b == true)
{
s = @base.vuvod_obj("kw", true);
}
else
{
@base.smena_zaprosa("select predlog.Id, obj.city, obj.street, land.s, predlog.agent, predlog.klient from obj, land, predlog where obj.dop_inf = land.Id and obj.Id = predlog.obj and obj.nm_kw is null and obj.nm_h is null and predlog.Id = " + predlTextBox.Text + "");
s = @base.vuvod_obj("land", true);
}
}
s = s.Remove(s.Length - 1);
arr = s.Split('&');
arr = get_cena(arr);
comboBox4.Items.Add(arr[0]);
comboBox4.SelectedItem = arr[0];
@base.smena_zaprosa("select potr.Id, potr.mincena, potr.maxcena, potr.agent, potr.klient, potr.city, potr.street, filter_h.minetag, filter_h.maxetag, filter_h.minrooms, filter_h.maxrooms, filter_h.mins, filter_h.maxs from potr, filter_h, sdelka where potr.dop_info = filter_h.Id and potr.obj = N'Дом' and potr.Id = " + potrTextBox.Text + " and sdelka.potr = " + potrTextBox.Text + "");
b = @base.proverka_znachenei_v_bd();
if (b == true)
{
s = @base.vuvod_zakazov("house");
}
else
{
@base.smena_zaprosa("select potr.Id, potr.mincena, potr.maxcena, potr.agent, potr.klient, potr.city, potr.street, filter_kw.minetag, filter_kw.maxetag, filter_kw.minrooms, filter_kw.maxrooms, filter_kw.mins, filter_kw.maxs from potr, filter_kw, sdelka where potr.dop_info = filter_kw.Id and potr.obj = N'Квартира' and potr.Id = " + potrTextBox.Text + " and sdelka.potr = " + potrTextBox.Text + "");
b = @base.proverka_znachenei_v_bd();
if (b == true)
{
s = @base.vuvod_zakazov("kw");
}
else
{
@base.smena_zaprosa("select potr.Id, potr.mincena, potr.maxcena, potr.agent, potr.klient, potr.city, potr.street, filter_l.mins, filter_l.maxs from potr, filter_l, sdelka where potr.dop_info = filter_l.Id and potr.obj = N'Земля' and potr.Id = " + potrTextBox.Text + " and sdelka.potr = " + potrTextBox.Text + "");
s = @base.vuvod_zakazov("land");
}
}
s = s.Remove(s.Length - 1);
arr = s.Split('&');
arr = get_fio(arr);
comboBox3.Items.Add(arr[0]);
comboBox3.SelectedItem = arr[0];
}
}
private void button1_Click(object sender, EventArgs e)
{
@base.smena_zaprosa("insert into tbl(dat, kl_vo) values(CONVERT(date, '" + dateTimePicker1.Value + "', 104), " + numericUpDown1.Value + ")");
@base.zapis_v_bd();
MessageBox.Show("зАПИСЬ ДОБАВЛЕНа");
}