private void loadDataGridView(string tableName)
{
if (tableName == "owners")
{
owners owners = new owners();
owners.dataGridView1.DataSource = null;
PublicClasses.sql = "select * from owners where isDeleted=0";
owners.dataGridView1.DataSource = PublicClasses.executeSqlRequest().Tables[0];
owners.dataGridView1.Columns[0].Visible = false;
owners.dataGridView1.Columns[6].Visible = false;
owners.dataGridView1.Columns[1].HeaderText = "Фамилия";
owners.dataGridView1.Columns[2].HeaderText = "Имя";
owners.dataGridView1.Columns[3].HeaderText = "Отчество";
owners.dataGridView1.Columns[4].HeaderText = "Адрес";
owners.dataGridView1.Columns[5].HeaderText = "Телефон";
owners.Show();
}
else
{
clients clients = new clients();
clients.dataGridView1.DataSource = null;
PublicClasses.sql = "select * from clients where isDeleted=0";
clients.dataGridView1.DataSource = PublicClasses.executeSqlRequest().Tables[0];
clients.dataGridView1.Columns[0].Visible = false;
clients.dataGridView1.Columns[6].Visible = false;
clients.dataGridView1.Columns[1].HeaderText = "Фамилия";
clients.dataGridView1.Columns[2].HeaderText = "Имя";
clients.dataGridView1.Columns[3].HeaderText = "Отчество";
clients.dataGridView1.Columns[4].HeaderText = "Адрес";
clients.dataGridView1.Columns[5].HeaderText = "Телефон";
clients.Show();
}
}
private void addOwner(object sender, EventArgs e) //добавление владельца
{
string columnsTable = "isDeleted,";
string values = "0,";
if (textBox1.Text != "")
{
columnsTable += "surname,"; values += "'" + textBox1.Text + "'" + ",";
}
if (textBox2.Text != "")
{
columnsTable += "name,"; values += "'" + textBox2.Text + "'" + ",";
}
if (textBox3.Text != "")
{
columnsTable += "lastname,"; values += "'" + textBox3.Text + "'" + ",";
}
if (textBox4.Text != "")
{
columnsTable += "adres,"; values += "'" + textBox4.Text + "'" + ",";
}
if (textBox5.Text != "")
{
columnsTable += "phone"; values += "'" + textBox5.Text + "'" + "";
}
if (columnsTable != "isDeleted," && values != "0,")
{
PublicClasses.insertIntoTable("owners", columnsTable, values);
MessageBox.Show("Клиент добавлен успешно", "Добавление клиента", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void changeClientsData(object sender, EventArgs e) //добавление клиента
{
string set = "";
if (textBox1.Text != "")
{
set += "surname=" + "'" + textBox1.Text + "',";
}
if (textBox2.Text != "")
{
set += "name=" + "'" + textBox2.Text + "',";
}
if (textBox3.Text != "")
{
set += "lastname=" + "'" + textBox3.Text + "',";
}
if (textBox4.Text != "")
{
set += "adres=" + "'" + textBox4.Text + "',";
}
if (textBox5.Text != "")
{
set += "phone=" + "'" + textBox5.Text + "',";
}
if (set != "")
{
PublicClasses.sql = "update clients set " + set.Remove(set.Length - 1) + " where idClient=" + PublicClasses.selectedRowIndex + "";
MessageBox.Show(PublicClasses.sql);
PublicClasses.executeSqlRequest();
MessageBox.Show("Данные изменены успешно", "Изменение данных клиента", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void loadDataGridView()
{
if (searchValue == "")
{
PublicClasses.sql = "select idPerson, persons.idSpecial, surname, name, lastname, special, dateReceipt from persons left join specials on persons.idSpecial=specials.idSpecial where isDeleted=0";
dataGridView1.DataSource = PublicClasses.executeSqlRequest().Tables[0];
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].Visible = false;
dataGridView1.Columns[2].HeaderText = "Фамилия";
dataGridView1.Columns[3].HeaderText = "Имя";
dataGridView1.Columns[4].HeaderText = "Отчество";
dataGridView1.Columns[5].HeaderText = "Должность";
dataGridView1.Columns[6].HeaderText = "Дата приема";
}
else
{
PublicClasses.sql = "select idPerson, persons.idSpecial, surname, name, lastname, special, dateReceipt from persons left join specials on persons.idSpecial=specials.idSpecial where isDeleted=0 and " + searchValue.Remove(searchValue.Length - 3) + "";
dataGridView1.DataSource = PublicClasses.executeSqlRequest().Tables[0];
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].Visible = false;
dataGridView1.Columns[2].HeaderText = "Фамилия";
dataGridView1.Columns[3].HeaderText = "Имя";
dataGridView1.Columns[4].HeaderText = "Отчество";
dataGridView1.Columns[5].HeaderText = "Должность";
dataGridView1.Columns[6].HeaderText = "Дата приема";
}
}
private void personal_Load(object sender, EventArgs e)
{
loadDataGridView();
PublicClasses.sql = "select concat(surname,' ', left(name,1),'.',left(lastname,1),'.') from persons where isDeleted=0";
comboBox1.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select special from specials";
comboBox2.Items.AddRange(PublicClasses.loadStringsToCmbbox());
}
private void button6_Click(object sender, EventArgs e)
{
PublicClasses.sql = "select * from property";
if (PublicClasses.rowIndex < PublicClasses.executeSqlRequest().Tables[0].Rows.Count - 1)
{
PublicClasses.rowIndex++;
}
loadFormFields(-1, PublicClasses.rowIndex);
}
private void button2_Click(object sender, EventArgs e)
{
if (PublicClasses.checkConnection() == 0)
{
MessageBox.Show("Ой, что-то пошло не так. Возникли ошибки с подключением к базе. Попробуйте снова подключиться к базе", "Соединение с базой", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
this.Close();
}
}
public void loadDataGridView()
{
dataGridView1.DataSource = new DataSet();
PublicClasses.sql = "select * from owners where isDeleted=0";
dataGridView1.DataSource = PublicClasses.executeSqlRequest().Tables[0];
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[6].Visible = false;
dataGridView1.Columns[1].HeaderText = "Фамилия";
dataGridView1.Columns[2].HeaderText = "Имя";
dataGridView1.Columns[3].HeaderText = "Отчество";
dataGridView1.Columns[4].HeaderText = "Адрес";
dataGridView1.Columns[5].HeaderText = "Телефон";
}
private void loadDatasToFields(string tableName, string columName)
{
PublicClasses.sql = "select surname from " + tableName + " where " + columName + "=" + PublicClasses.selectedRowIndex + "";
textBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0].ToString();
PublicClasses.sql = "select name from " + tableName + " where " + columName + "=" + PublicClasses.selectedRowIndex + "";
textBox2.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0].ToString();
PublicClasses.sql = "select lastname from " + tableName + " where " + columName + "=" + PublicClasses.selectedRowIndex + "";
textBox3.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0].ToString();
PublicClasses.sql = "select adres from " + tableName + " where " + columName + "=" + PublicClasses.selectedRowIndex + "";
textBox4.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0].ToString();
PublicClasses.sql = "select phone from " + tableName + " where " + columName + "=" + PublicClasses.selectedRowIndex + "";
textBox5.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0].ToString();
}
public void loadFormFields(int idProperty, int rowIndex)
{
PublicClasses.sql = "select distinct(concat(type.type,' ',typeproperty.typeProperty)) from type left join typeproperty on type.idTypeProperty = typeproperty.idTypeProperty";
comboBox5.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select city from cities";
comboBox1.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select area from area";
comboBox2.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select district from district";
comboBox3.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select undergroundStation from undergroundstations";
comboBox4.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select concat(surname,' ',left(name,1),'. ',left(lastname,1),'.') from owners";
comboBox6.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select concat(type.type, ' ', typeproperty.typeProperty), cities.city, area.area, district.district, undergroundstations.undergroundStation, if(property.buyRent=1,'Продажу','Аренду'), concat(surname,' ',left(name,1),'. ',left(lastname,1),'.'), price, isLoggia, isFloor, countFloor, countRoom, description " +
"from property left join type on property.type = type.idType " +
"left join typeproperty on type.idTypeProperty = typeproperty.idTypeProperty " +
"left join cities on property.idCity = cities.idCity " +
"left join area on property.idArea = area.idArea " +
"left join district on property.idDistrict = district.idDistrict " +
"left join undergroundstations on property.idUndergroundStation = undergroundstations.idUndergroundStation " +
"left join owners on property.idOwner=owners.idOwner " +
"where property.idProperty=" + idProperty + "";
if (idProperty == -1)
{
PublicClasses.sql = PublicClasses.sql.Remove(PublicClasses.sql.IndexOf("where"));
}
comboBox5.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[0].ToString();
comboBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[1].ToString();
comboBox2.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[2].ToString();
comboBox3.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[3].ToString();
comboBox4.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[4].ToString();
comboBox6.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[6].ToString();
textBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[7].ToString();
if (!string.IsNullOrEmpty(PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[8].ToString()))
{
checkBox1.Checked = true;
}
textBox3.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[9].ToString();
textBox4.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[10].ToString();
textBox2.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[11].ToString();
if (PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[5].ToString() == "Аренду")
{
radioButton1.Checked = true;
}
else
{
radioButton2.Checked = true;
}
richTextBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[12].ToString();
}
private void Form6_Load(object sender, EventArgs e)
{
PublicClasses.sql = "select distinct(concat(type.type,' ',typeproperty.typeProperty)) from type left join typeproperty on type.idTypeProperty = typeproperty.idTypeProperty";
comboBox5.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select city from cities";
comboBox1.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select area from areas";
comboBox2.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select district from district";
comboBox3.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select undergroundStation from undergroundstations";
comboBox4.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select concat(surname,' ',left(name,1),'. ',left(lastname,1),'.') from owners";
comboBox6.Items.AddRange(PublicClasses.loadStringsToCmbbox());
}
private void button6_Click(object sender, EventArgs e)
{
PublicClasses.sql = "select idProperty, concat(type.type, ' ', typeproperty.typeProperty) as 'Тип Недвижимости', cities.city as 'Город', areas.area as 'Область', district.district as 'Район', undergroundstations.undergroundStation as 'ст. Метро', if(property.buyRent=1,'Продажy','Арендy') as 'Выставлен на', price " +
"from property " +
"left join type on property.type = type.idType " +
"left join typeproperty on type.idTypeProperty = typeproperty.idTypeProperty " +
"left join cities on property.idCity = cities.idCity " +
"left join areas on property.idArea = areas.idArea " +
"left join district on property.idDistrict = district.idDistrict " +
"left join undergroundstations on property.idUndergroundStation = undergroundstations.idUndergroundStation where isRemoveBuyRent<>1";
if (PublicClasses.rowIndex >= 0)
{
PublicClasses.rowIndex++;
PublicClasses.selectedRowIndex = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[PublicClasses.rowIndex].ItemArray[0]);
}
loadFormFields(-1, PublicClasses.rowIndex);
}
private void button1_Click(object sender, EventArgs e)
{
searchValue = "";
PublicClasses.sql = "select idSpecial from specials where special='" + comboBox2.Text + "'";
int idSpecial = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
MessageBox.Show(idSpecial.ToString());
if (comboBox1.Text != "")
{
searchValue += "concat(surname,' ', left(name,1),'.',left(lastname,1),'.')='" + comboBox1.Text + "' and";
}
if (comboBox2.Text != "")
{
searchValue += "persons.idSpecial='" + idSpecial + "' and";
}
loadDataGridView();
}
private void button1_Click(object sender, EventArgs e) //регистрация пользователя
{
bool l;
errorProvider1.Clear();
if (string.IsNullOrWhiteSpace(textBox1.Text))
{
errorProvider1.SetError(textBox1, "Поле не должно быть пустым"); l = false;
}
else
{
l = true;
}
if (string.IsNullOrWhiteSpace(textBox2.Text))
{
errorProvider1.SetError(textBox2, "Поле не должно быть пустым"); l = false;
}
else
{
l = true;
}
if (l)
{
if (PublicClasses.idUser == null)
{
PublicClasses.sql = "insert into autorization_datas(login,password,password1) values('" + textBox1.Text + "','" + GetHashString(textBox2.Text) + "','" + textBox2.Text + "')";
try
{
PublicClasses.executeSqlRequest();
MessageBox.Show("Регистрация прошла успешно", "Регистрация пользователя", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex) { MessageBox.Show(ex.Message, "Регистрация пользователя", MessageBoxButtons.OK, MessageBoxIcon.Warning); }
}
else
{
PublicClasses.sql = "update autorization_datas set login='******',password='******', password1='" + textBox2.Text + "' where idUser="******"";
try
{
PublicClasses.executeSqlRequest();
MessageBox.Show("Регистрация прошла успешно", "Регистрация пользователя", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex) { MessageBox.Show(ex.Message, "Регистрация пользователя", MessageBoxButtons.OK, MessageBoxIcon.Warning); }
}
}
}
private void Form1_Shown(object sender, EventArgs e)
{
if (PublicClasses.checkConnection() == 0)
{
var result = MessageBox.Show("Ой, что-то пошло не так. Возникли ошибки с подключением к базе. Попробуйте снова подключиться к базе", "Соединение с базой", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (result == DialogResult.OK)
{
dbSettings form5 = new dbSettings();
form5.ShowDialog();
}
}
if (Convert.ToInt16(PublicClasses.readFromFileUser()) != 0)
{
PublicClasses.sql = "select * from autorization_datas where idUser=" + PublicClasses.readFromFileUser();
textBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[1].ToString();
textBox2.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[3].ToString();
}
}
private void button1_Click(object sender, EventArgs e)
{
PublicClasses.server = textBox1.Text;
PublicClasses.user = textBox2.Text;
PublicClasses.password = textBox3.Text;
PublicClasses.db = textBox4.Text;
PublicClasses.port = textBox5.Text;
PublicClasses.setDbPathToFile();
if (PublicClasses.checkConnection() == 0)
{
MessageBox.Show("Не удалось соединиться с базой.", "Соединение с базой", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("Успешное соединение с базой", "Соединение с базой", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text != "")
{
PublicClasses.sql = "select * from owners where concat(surname,' ',left(name,1),' ',left(lastname,1))='" + comboBox1.Text + "'";
dataGridView1.DataSource = PublicClasses.executeSqlRequest().Tables[0];
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[6].Visible = false;
dataGridView1.Columns[1].HeaderText = "Фамилия";
dataGridView1.Columns[2].HeaderText = "Имя";
dataGridView1.Columns[3].HeaderText = "Отчество";
dataGridView1.Columns[4].HeaderText = "Адрес";
dataGridView1.Columns[5].HeaderText = "Телефон";
}
else
{
errorProvider1.SetError(comboBox1, "Поле не должно быть пустым");
}
}
private void Form2_Load(object sender, EventArgs e) //загрузка формы
{
if (PublicClasses.privelege == 1)
{
contextMenuStrip1.Items[0].Visible = false;
contextMenuStrip1.Items[1].Visible = false;
contextMenuStrip1.Items[2].Visible = false;
contextMenuStrip1.Items[3].Visible = false;
contextMenuStrip1.Items[5].Visible = false;
}
time = DateTime.Now;
toolStrip1.Items[0].Text = " Время: " + time.ToLongTimeString();
toolStrip1.Items[1].Text = "Пользователь: " + PublicClasses.UserLogin;
PublicClasses.sql = "select distinct(concat(type.type,' ',typeproperty.typeProperty)) from type left join typeproperty on type.idTypeProperty = typeproperty.idTypeProperty";
comboBox1.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select city from cities";
comboBox2.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select area from areas";
comboBox3.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select district from district";
comboBox4.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select undergroundStation from undergroundstations";
comboBox5.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select idProperty, concat(type.type, ' ', typeproperty.typeProperty) as 'Тип Недвижимости', cities.city as 'Город', areas.area as 'Область', district.district as 'Район', undergroundstations.undergroundStation as 'ст. Метро', if(property.buyRent=1,'Продажу','Аренду') as 'Выставлен на', price " +
"from property " +
"left join type on property.type = type.idType " +
"left join typeproperty on type.idTypeProperty = typeproperty.idTypeProperty " +
"left join cities on property.idCity = cities.idCity " +
"left join areas on property.idArea = areas.idArea " +
"left join district on property.idDistrict = district.idDistrict " +
"left join undergroundstations on property.idUndergroundStation = undergroundstations.idUndergroundStation " +
"where isRemoveBuyRent<>1";
dataGridView1.DataSource = PublicClasses.executeSqlRequest().Tables[0];
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].HeaderText = "Тип недвижимости";
dataGridView1.Columns[2].HeaderText = "Город";
dataGridView1.Columns[3].HeaderText = "Область";
dataGridView1.Columns[4].HeaderText = "Район";
dataGridView1.Columns[5].HeaderText = "Ст. метро";
dataGridView1.Columns[6].HeaderText = "Выставлен на";
dataGridView1.Columns[7].HeaderText = "Цена";
}
private void executeSqlRequest() //пользовательская функция
{
PublicClasses.sql = "select special from specials";
comboBox1.Items.AddRange(PublicClasses.loadStringsToCmbbox());
if (this.Text != "Добавление сотрудника")
{
date = DateTime.Now;
}
else
{
PublicClasses.sql = "select surname, name, lastname, adres, phone, special, photo from persons left join specials on persons.idSpecial=specials.idSpecial where idPerson=" + PublicClasses.selectedRowIndex + "";
textBox3.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0].ToString();
textBox4.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[1].ToString();
textBox5.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[2].ToString();
textBox6.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[3].ToString();
maskedTextBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[4].ToString();
comboBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[5].ToString();
string photoPath = System.IO.Path.Combine(Application.StartupPath, @"Photos\" + PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[6] + "");
pictureBox1.Load(photoPath);
}
}
private void contracts_Load(object sender, EventArgs e)
{
PublicClasses.sql = "select * from showcontracts";
dataGridView1.DataSource = PublicClasses.executeSqlRequest().Tables[0];
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].Visible = false;
dataGridView1.Columns[2].HeaderText = "Сотрудник";
dataGridView1.Columns[3].HeaderText = "Владелец";
dataGridView1.Columns[4].HeaderText = "Клиент";
dataGridView1.Columns[5].HeaderText = "Дата оформления";
dataGridView1.Columns[6].HeaderText = "Начало периода";
dataGridView1.Columns[7].HeaderText = "Конец периода";
dataGridView1.Columns[8].HeaderText = "Цена";
dataGridView1.Columns[9].HeaderText = "Выставлен на";
dataGridView1.Columns[10].HeaderText = "Статус договора";
PublicClasses.sql = "select concat(surname,' ', left(name,1),'.',left(lastname,1),'.') from persons where isDeleted=0";
comboBox1.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select concat(surname,' ', left(name,1),'.',left(lastname,1),'.') from clients where isDeleted=0";
comboBox2.Items.AddRange(PublicClasses.loadStringsToCmbbox());
PublicClasses.sql = "select concat(surname,' ', left(name,1),'.',left(lastname,1),'.') from owners where isDeleted=0";
comboBox3.Items.AddRange(PublicClasses.loadStringsToCmbbox());
}
public void loadDataGridView() //пользовательская функция для вывода dataGridView
{
property form2 = new property();
form2.dataGridView1.DataSource = null;
PublicClasses.sql = "select idProperty, concat(type.type, ' ', typeproperty.typeProperty) as 'Тип Недвижимости', cities.city as 'Город', areas.area as 'Область', district.district as 'Район', undergroundstations.undergroundStation as 'ст. Метро', if(property.buyRent=1,'Продажy','Арендy') as 'Выставлен на' " +
"from property " +
"left join type on property.type = type.idType " +
"left join typeproperty on type.idTypeProperty = typeproperty.idTypeProperty " +
"left join cities on property.idCity = cities.idCity " +
"left join areas on property.idArea = areas.idArea " +
"left join district on property.idDistrict = district.idDistrict " +
"left join undergroundstations on property.idUndergroundStation = undergroundstations.idUndergroundStation " +
"where isRemoveBuyRent<>1";
form2.dataGridView1.DataSource = PublicClasses.executeSqlRequest().Tables[0];
form2.dataGridView1.Columns[0].Visible = false;
form2.dataGridView1.Columns[1].HeaderText = "Тип недвижимости";
form2.dataGridView1.Columns[2].HeaderText = "Город";
form2.dataGridView1.Columns[3].HeaderText = "Область";
form2.dataGridView1.Columns[4].HeaderText = "Район";
form2.dataGridView1.Columns[5].HeaderText = "Ст. метро";
form2.dataGridView1.Columns[6].HeaderText = "Выставлен на";
form2.Show();
}
public void loadFormFields(int idProperty, int rowIndex)
{
PublicClasses.sql = "select cities.city, areas.area, district.district, undergroundstations.undergroundStation, concat(type.type, ' ', typeproperty.typeProperty), countRoom, isFloor, countFloor, isLoggia, if(property.buyRent=1,'Продажу','Аренду'), price, concat(surname,' ',left(name,1),'. ',left(lastname,1),'.'), description " +
"from property left join type on property.type = type.idType " +
"left join typeproperty on type.idTypeProperty = typeproperty.idTypeProperty " +
"left join cities on property.idCity = cities.idCity " +
"left join areas on property.idArea = areas.idArea " +
"left join district on property.idDistrict = district.idDistrict " +
"left join undergroundstations on property.idUndergroundStation = undergroundstations.idUndergroundStation " +
"left join owners on property.idOwner = owners.idOwner " +
"where property.idProperty =" + idProperty;
if (idProperty > -1)
{
comboBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0].ToString();
comboBox2.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[1].ToString();
comboBox3.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[2].ToString();
comboBox4.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[3].ToString();
comboBox5.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[4].ToString();
textBox2.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[5].ToString();
textBox3.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[6].ToString();
textBox4.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[7].ToString();
if (Convert.ToBoolean(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[8]) == true)
{
checkBox1.Checked = true;
}
if (PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[9].ToString() == "Продажу")
{
radioButton2.Checked = true;
}
else
{
radioButton1.Checked = true;
}
textBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[10].ToString();
comboBox6.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[11].ToString();
richTextBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[12].ToString();
}
else
{
PublicClasses.sql = PublicClasses.sql.Remove(PublicClasses.sql.IndexOf("where"));
comboBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[0].ToString();
comboBox2.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[1].ToString();
comboBox3.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[2].ToString();
comboBox4.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[3].ToString();
comboBox5.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[4].ToString();
textBox2.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[5].ToString();
textBox3.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[6].ToString();
textBox4.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[7].ToString();
if (Convert.ToBoolean(PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[8]) == true)
{
checkBox1.Checked = true;
}
if (PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[9].ToString() == "Продажу")
{
radioButton2.Checked = true;
}
else
{
radioButton1.Checked = true;
}
textBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[10].ToString();
comboBox6.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[11].ToString();
richTextBox1.Text = PublicClasses.executeSqlRequest().Tables[0].Rows[rowIndex].ItemArray[12].ToString();
}
}
private void button3_Click(object sender, EventArgs e) //снятие блокировки с полей
{
PublicClasses.sql = "update property set isRemoveBuyRent=1 where idProperty=" + PublicClasses.selectedRowIndex + "";
PublicClasses.executeSqlRequest();
MessageBox.Show("Недвижимость снята с продажи.", "Снятие недвижимости с продажи", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void button4_Click(object sender, EventArgs e) //сохранение изменений
{
bool l = true;
if (string.IsNullOrWhiteSpace(textBox1.Text))
{
errorProvider1.SetError(label11, "Поле не должно быть пустым"); l = false;
}
else
{
l = true;
}
if (string.IsNullOrWhiteSpace(comboBox5.Text))
{
errorProvider1.SetError(comboBox5, "Поле не должно быть пустым"); l = false;
}
else
{
l = true;
}
if (string.IsNullOrWhiteSpace(comboBox6.Text))
{
errorProvider1.SetError(comboBox6, "Поле не должно быть пустым"); l = false;
}
else
{
l = true;
}
if (l)
{
string set = "";
if (comboBox1.Text != "")
{
PublicClasses.sql = "select * from cities where city='" + comboBox1.Text + "'";
int idCity = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
set += "idCity=" + idCity + ",";
}
if (comboBox2.Text != "")
{
PublicClasses.sql = "select * from areas where area='" + comboBox2.Text + "'";
int idArea = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
set += "idArea=" + idArea + ",";
}
if (comboBox3.Text != "")
{
PublicClasses.sql = "select * from district where district='" + comboBox3.Text + "'";
int idDistrict = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
set += "idDistrict=" + idDistrict + ",";
}
if (comboBox4.Text != "")
{
PublicClasses.sql = "select * from undergroundstations where undergroundStation='" + comboBox4.Text + "'";
int idUndergroundStation = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
set += "idUndergroundStation=" + idUndergroundStation + ",";
}
if (comboBox5.Text != "")
{
PublicClasses.sql = "select type.idType, concat(type.type,' ',typeproperty.typeProperty) from type left join typeproperty on type.idTypeProperty = typeproperty.idTypeProperty where concat(type.type,' ',typeproperty.typeProperty)='" + comboBox5.Text + "'";
int idType = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
set += "type=" + idType + ",";
}
if (comboBox6.Text != "")
{
PublicClasses.sql = "select * from owners where concat(surname,' ',left(name,1),'. ',left(lastname,1),'.')='" + comboBox6.Text + "'";
int idOwner = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
set += "idOwner=" + idOwner + ",";
}
if (textBox2.Text != "")
{
set += "countRoom=" + textBox2.Text + ",";
}
if (textBox3.Text != "")
{
set += "isFloor=" + textBox3.Text + ",";
}
if (textBox4.Text != "")
{
set += "countFloor=" + textBox4.Text + ",";
}
if (textBox1.Text != "")
{
set += "price=" + textBox1.Text.Replace(",", ".") + ",";
}
if (checkBox1.Checked)
{
set += "isLoggia=1,";
}
if (radioButton1.Checked)
{
set += "buyRent=0,";
}
if (radioButton2.Checked)
{
set += "buyRent=1,";
}
try
{
PublicClasses.sql = "update property set " + set.Remove(set.Length - 1) + " where idProperty=" + PublicClasses.selectedRowIndex + "";
PublicClasses.executeSqlRequest();
MessageBox.Show("Изменения успешно занесены.", "Изменение данных недвижимости", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex) { MessageBox.Show(ex.Message, "Изменение данных недвижимости", MessageBoxButtons.OK, MessageBoxIcon.Warning); }
}
}
private void button2_Click(object sender, EventArgs e) //вход в ситему
{
bool l = false, l1 = false;
property form2 = new property();
if (string.IsNullOrWhiteSpace(textBox1.Text))
{
errorProvider1.SetError(textBox1, "Поле не должно быть пустым");
}
else
{
l = true;
}
if (string.IsNullOrWhiteSpace(textBox2.Text))
{
errorProvider1.SetError(textBox2, "Поле не должно быть пустым");
}
else
{
l = true;
}
//if (string.IsNullOrWhiteSpace(textBox3.Text)) { errorProvider1.SetError(textBox3, "Введите капчу"); } else { l = true; }
if (l)
{
PublicClasses.sql = "call checkPassword('" + textBox1.Text + "','" + textBox2.Text + "')";
try
{
PublicClasses.executeSqlRequest();
l1 = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Окно предупреждения", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
if (l1)
{
try
{
PublicClasses.sql = "select * from autorization_datas where login='******' and password='******';";
PublicClasses.idUser = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0].ToString();
PublicClasses.UserLogin = PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[1].ToString();
if (PublicClasses.executeSqlRequest().Tables[0].Rows.Count == 0 /*|| captcha!=textBox3.Text*/)
{
MessageBox.Show("Ой что-то пошло не так. Попробуйте заполнить поля снова.", "Окно предупреждения", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
PublicClasses.sql = "select * from persons where idUser="******"";
if (PublicClasses.executeSqlRequest().Tables[0].Rows.Count != 0)
{
PublicClasses.privelege = 2;
}
else
{
PublicClasses.privelege = 1;
}
if (textBox1.Text == "admin" && textBox2.Text == "admin")
{
PublicClasses.privelege = 3;
}
this.Hide();
form2.Show();
}
PublicClasses.writeToFileUser(checkBox1);
}
catch (Exception ex) { MessageBox.Show("Неправильные пароль или логин.", "Вход в систему", MessageBoxButtons.OK, MessageBoxIcon.Error); }
}
}
}
private void удалитьToolStripMenuItem_Click(object sender, EventArgs e)
{
PublicClasses.deleteSelectedRow(dataGridView1, "clients", "isDeleted=1", "idClient");
MessageBox.Show("Клиент успешно удален", "Удаление клиента", MessageBoxButtons.OK, MessageBoxIcon.Information);
loadDataGridView();
}
private void addNewPerson(object sender, EventArgs e)
{
date = DateTime.Now;
errorProvider1.Clear();
bool l;
int idSpecial;
string columnName = "", values = "";
if (string.IsNullOrEmpty(textBox3.Text))
{
errorProvider1.SetError(textBox3, "Поле не должно быть пустым."); l = false;
}
else
{
l = true;
}
if (string.IsNullOrEmpty(textBox4.Text))
{
errorProvider1.SetError(textBox4, "Поле не должно быть пустым."); l = false;
}
else
{
l = true;
}
if (string.IsNullOrEmpty(textBox5.Text))
{
errorProvider1.SetError(textBox5, "Поле не должно быть пустым."); l = false;
}
else
{
l = true;
}
if (string.IsNullOrEmpty(comboBox1.Text))
{
errorProvider1.SetError(comboBox1, "Поле не должно быть пустым."); l = false;
}
else
{
l = true;
}
if (l)
{
PublicClasses.sql = "select max(idUser) from autorization_datas";
PublicClasses.idUser = (Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]) + 1).ToString();
if (textBox3.Text != "")
{
columnName += "surname,"; values += "'" + textBox3.Text + "',";
}
if (textBox4.Text != "")
{
columnName += "name,"; values += "'" + textBox4.Text + "',";
}
if (textBox5.Text != "")
{
columnName += "lastname,"; values += "'" + textBox5.Text + "',";
}
if (textBox6.Text != "")
{
columnName += "adres,"; values += "'" + textBox6.Text + "',";
}
if (maskedTextBox1.MaskFull)
{
columnName += "phone,"; values += "'" + maskedTextBox1.Text + "',";
}
if (opndlg.FileName != null)
{
columnName += "photo,"; values += "'" + opndlg.FileName.Substring(opndlg.FileName.LastIndexOf(@"\") + 1) + "',";
}
if (comboBox1.Text != "")
{
PublicClasses.sql = "select idSpecial from specials where special='" + comboBox1.Text + "'";
idSpecial = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
columnName += "idSpecial,"; values += idSpecial + ",";
}
if (columnName != "" && values != "") //добавление нового сторудникa
{
date = date.Date;
try
{
PublicClasses.sql = "insert into persons(" + columnName.Remove(columnName.Length - 1) + ", dateReceipt, isDeleted) values(" + values.Remove(values.Length - 1) + ",'" + date.ToString("yyyy-MM-dd") + "', 0)";
MessageBox.Show(PublicClasses.sql);
PublicClasses.executeSqlRequest();
var result = MessageBox.Show("Сотрудник успешно добавлен. Создать аккаунт данному сотруднику?", "Добавление сотрудника", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (result == DialogResult.Yes)
{
PublicClasses.sql = "insert into autorization_datas(password1) values(' ')";
PublicClasses.executeSqlRequest();
PublicClasses.sql = "select idPerson from persons order by 1 desc limit 1";
int idPerson = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
PublicClasses.sql = "update persons set idUser="******" where idPerson=" + idPerson + "";
MessageBox.Show(PublicClasses.sql);
PublicClasses.executeSqlRequest();
registration rgstr = new registration();
rgstr.ShowDialog();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Окно предупреждения", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
}
private void changePersonsData(object sender, EventArgs e)
{
string set = "";
int idSpecial;
bool l;
errorProvider1.Clear();
if (string.IsNullOrEmpty(textBox3.Text))
{
errorProvider1.SetError(textBox3, "Поле не должно быть пустым."); l = false;
}
else
{
l = true;
}
if (string.IsNullOrEmpty(textBox4.Text))
{
errorProvider1.SetError(textBox4, "Поле не должно быть пустым."); l = false;
}
else
{
l = true;
}
if (string.IsNullOrEmpty(textBox5.Text))
{
errorProvider1.SetError(textBox5, "Поле не должно быть пустым."); l = false;
}
else
{
l = true;
}
if (string.IsNullOrEmpty(comboBox1.Text))
{
errorProvider1.SetError(comboBox1, "Поле не должно быть пустым."); l = false;
}
else
{
l = true;
}
if (l)
{
if (opndlg.FileName != null)
{
set += "photo='" + opndlg.FileName.Substring(opndlg.FileName.LastIndexOf(@"\") + 1) + "',";
}
if (textBox3.Text != "")
{
set += "surname='" + textBox3.Text + "',";
}
if (textBox4.Text != "")
{
set += "name='" + textBox4.Text + "',";
}
if (textBox5.Text != "")
{
set += "lastname='" + textBox5.Text + "',";
}
if (textBox6.Text != "")
{
set += "adres='" + textBox6.Text + "',";
}
if (maskedTextBox1.MaskFull)
{
set += "phone='" + maskedTextBox1.Text + "',";
}
if (comboBox1.Text != "")
{
PublicClasses.sql = "select idSpecial from specials where special='" + comboBox1.Text + "'";
idSpecial = Convert.ToInt16(PublicClasses.executeSqlRequest().Tables[0].Rows[0].ItemArray[0]);
set += "idSpecial='" + idSpecial + "',";
}
if (set != "") //изменение данных сотрудника
{
try
{
MessageBox.Show(set);
PublicClasses.sql = "update persons set " + set.Remove(set.Length - 1) + " where idPerson=" + PublicClasses.selectedRowIndex + "";
MessageBox.Show(PublicClasses.sql);
PublicClasses.executeSqlRequest();
MessageBox.Show("Данные успешно изменены", "Изменение данных сотрудника", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Окно предупреждения", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
}
private void Form12_Load(object sender, EventArgs e)
{
PublicClasses.sql = "select concat(surname,' ',left(name,1),' ',left(lastname,1)) from owners";
comboBox1.Items.AddRange(PublicClasses.loadStringsToCmbbox());
loadDataGridView();
}
private void удалитьToolStripMenuItem_Click(object sender, EventArgs e)
{
PublicClasses.deleteSelectedRow(dataGridView1, "clients", "isDeleted=1", "idClient");
loadDataGridView();
}
