private void btnAdd_Click(object sender, EventArgs e)
{
DataRowView drw = cbHouse.SelectedItem as DataRowView;
string AdressHouse = drw["Улица"].ToString();
connection con = new connection();
ID_house = Convert.ToInt32(con.select1String("[Код дом]", "[Дом]", "Улица LIKE '" + AdressHouse + "'"));
ID_flat= Convert.ToInt32(con.select1String("[Код квартира]", "[Квартира]", "Номер = " + cbFlat.Text + " AND [Код дом] = "+ ID_house));
con.insert("[Квитанция]([Код квартира],[Дата],[Холодное водоснабжение],[Горячее водоснабжение],[Водоотведение],[Электроснабжение],[Газоснабжение],[Отопление],[Оплачен])", ""
+ ID_flat + ", '" + tbDate.Text + "', " + tbCW.Text + ", " + tbHW.Text
+ ", " + tbOW.Text + ", " + tbE.Text + ", " + tbG.Text + ", " + tbH.Text + ", '" + chb.Checked + "'");
con.Close();
ClearAll();
MessageBox.Show("Запись успешно добавлена!");
}
private void btnEdit_Click(object sender, EventArgs e)
{
ID = (int)dgvT.Rows[dgvT.CurrentRow.Index].Cells[0].Value;
connection connection = new connection();
switch (btnEdit.Text)
{
case "Изменить":
btnAdd.Visible = false;
btnDel.Visible = false;
this.ID_house = Convert.ToInt32(connection.select1String("[Код дом]", "Квартира", "[Код квартира] = " + ID_flat));
cbHouse.Text = connection.select1String("Улица", "Дом", "[Код дом] = " + ID_house);
cbFlat.Text = connection.select1String("Номер", "Квартира", "[Код квартира] = " + ID_flat);
SqlDataReader myReader = connection.select("*", "[Квитанция]", "[Код квитанция]=" + ID);
while (myReader.Read())
{
tbDate.Text = Convert.ToString(myReader[2]);
tbCW.Text = Convert.ToString(myReader[3]);
tbHW.Text = Convert.ToString(myReader[4]); ;
tbOW.Text = Convert.ToString(myReader[5]);
tbE.Text = Convert.ToString(myReader[6]);
tbG.Text = Convert.ToString(myReader[7]);
tbH.Text = Convert.ToString(myReader[8]);
chb.Checked =(bool)(myReader[9]);
}
myReader.Close();
btnEdit.Text = "Сохранить";
break;
case "Сохранить":
DataRowView drw = cbHouse.SelectedItem as DataRowView;
string AdressHouse = drw["Улица"].ToString();
connection con = new connection();
int ID_house_tmp = Convert.ToInt32(con.select1String("[Код дом]", "[Дом]", "Улица LIKE '" + AdressHouse + "'"));
int ID_flat_tmp = Convert.ToInt32(con.select1String("[Код квартира]", "[Квартира]", "Номер = " + cbFlat.Text + " AND [Код дом] = " + ID_house_tmp));
connection.update("Квитанция", "[Код квартира] = " + ID_flat_tmp
+ ", [Дата] ='" + tbDate.Text + "', [Холодное водоснабжение] =" + tbCW.Text
+ ", [Горячее водоснабжение] =" + tbHW.Text + ", [Водоотведение] =" + tbOW.Text + ",Электроснабжение="
+ tbE.Text+ ", [Газоснабжение] =" + tbG.Text + ", [Отопление] =" + tbH.Text + ",Оплачен='" + chb.Checked+ "'", "[Код квитанция] = " + ID);
ClearAll();
MessageBox.Show("Запись успешно изменена!");
break;
}
connection.Close();
dgvT.DataSource = connection.FillDGV("*", "Квитанция", "[Код квартира] = " + ID_flat);
}
public Ticket(int ID_flat)
{
InitializeComponent();
dgvT.DataSource = connection.FillDGV("*", "Квитанция", "[Код квартира] = " + ID_flat);
this.ID_flat = ID_flat;
cbHouse.Items.Clear();
cbHouse.DataSource = connection.FillDGV("*", "Дом");
cbHouse.DisplayMember = "Улица";
connection con = new connection();
this.ID_house = Convert.ToInt32(con.select1String("[Код дом]","Квартира","[Код квартира] = "+ ID_flat));
house= con.select1String("Улица", "Дом", "[Код дом] = " + ID_house);
flat = con.select1String("Номер", "Квартира", "[Код квартира] = " + ID_flat);
con.Close();
ClearAll();
}
private void cbHouse_SelectedIndexChanged(object sender, EventArgs e)
{
DataRowView drw = cbHouse.SelectedItem as DataRowView;
string AdressHouse = drw["Улица"].ToString();
connection con = new connection();
ID_house = Convert.ToInt32(con.select1String("[Код дом]", "[Дом]", "Улица LIKE '" + AdressHouse+"'"));
con.Close();
cbFlat.Text = "";
cbFlat.DataSource = connection.FillDGV("*", "Квартира","[Код дом] = "+ID_house);
cbFlat.DisplayMember = "Номер";
}
private void btnPrint_Click(object sender, EventArgs e)
{
Application application=new Application();
Workbook workBook;
Worksheet worksheet;
string IDtmp = dgvT.Rows[dgvT.CurrentRow.Index].Cells[0].Value.ToString();
string ID_flat_tmp="0";
// Файл шаблона
const string template = "\\ticket.xlsx";
workBook = application.Workbooks.Open(Environment.CurrentDirectory + template);
// Получаем активную таблицу
worksheet = workBook.ActiveSheet as Worksheet;
connection connection = new connection();
// Записываем данные
SqlDataReader myReader = connection.select("*", "[Квитанция]", "[Код квитанция]=" + IDtmp);
while (myReader.Read())
{
ID_flat_tmp = Convert.ToString(myReader[1]);
worksheet.Range["G2"].Value = Convert.ToString(myReader[2]);
worksheet.Range["C5"].Value = Convert.ToString(myReader[3]);
worksheet.Range["C6"].Value = Convert.ToString(myReader[4]); ;
worksheet.Range["C7"].Value = Convert.ToString(myReader[5]);
worksheet.Range["C8"].Value = Convert.ToString(myReader[6]);
worksheet.Range["C9"].Value = Convert.ToString(myReader[7]);
worksheet.Range["C10"].Value = Convert.ToString(myReader[8]);
}
myReader.Close();
connection.Close();
connection con = new connection();
worksheet.Range["C2"].Value = con.select1String("Владелец","Квартира", "[Код квартира] = "+ID_flat_tmp);
con.Close();
worksheet.Range["H3"].Value = con.select1String("Задолженность", "Квартира", "[Код квартира] = " + ID_flat_tmp);
string number= connection.select1String("Номер", "Квартира", "[Код квартира] = " + ID_flat_tmp);
string ID_house_tmp = connection.select1String("[Код дом]", "Квартира", "[Код квартира] = " + ID_flat_tmp);
worksheet.Range["B3"].Value = connection.select1String("Улица", "Дом", "[Код дом] = " + ID_house_tmp) + ", " + number;
////Вызываем нашу созданную эксельку.
application.Visible = true;
TopMost = true;
}