public static List <ElectricityWaterBills> GetElectricityWaterBillsByRoom(string roomName)
{
List <ElectricityWaterBills> electricityWaterBills = new List <ElectricityWaterBills>();
SqlConnection conn = DAO.getConnections();
string sql = "select * from ElectricityWaterBills where roomName = '" + roomName + "'";
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
int id = int.Parse(da.GetValue(0).ToString());
string roomName1 = da.GetValue(1).ToString();
DateTime monthYear = DateTime.Parse(da.GetValue(2).ToString());
int soDien = int.Parse(da.GetValue(3).ToString());
int soNuoc = int.Parse(da.GetValue(4).ToString());
int paid = int.Parse(da.GetValue(5).ToString());
ElectricityWaterBills account = new ElectricityWaterBills(id, roomName1, monthYear, soDien, soNuoc, paid);
electricityWaterBills.Add(account);
}
conn.Close();
return(electricityWaterBills);
}
public static bool Delete(ElectricityWaterBills ewb)
{
string sql = "delete ElectricityWaterBills where id = @id";
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@id", ewb.Id);
return(DAO.UpdateTable(cmd));
}
private void button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtElectric.Text) || string.IsNullOrEmpty(txtWater.Text))
{
MessageBox.Show("TextField is not empty");
}
else
{
int el = int.Parse(txtElectric.Text);
int wt = int.Parse(txtElectric.Text);
List <Room> list = RoomDAO.getRoomBedByRoomName(comboBoxRoom.SelectedValue.ToString());
int count = 0;
for (int i = 0; i < list.Count; i++)
{
if (list[i].BedStatus == 1)
{
count++;
}
}
if (count != 0)
{
int moneyEL = 0;
int moneyWt = 0;
if ((el - count * 36) < 0)
{
moneyEL = count * 36 - el;
}
if ((wt - count * 6) < 0)
{
moneyWt = count * 6 - wt;
}
int total = (moneyEL * 2000 + moneyWt * 5000) / count;
List <Student> ls = StudentDAO.getStudents();
for (int i = 0; i < ls.Count; i++)
{
Student stu = ls[i];
if (stu.RoomName == comboBoxRoom.SelectedValue.ToString() && stu.BedNo != 0)
{
stu.Debt = stu.Debt + total;
StudentDAO.Update(stu);
}
}
ElectricityWaterBills eee = new ElectricityWaterBills(1, comboBoxRoom.SelectedValue.ToString(), dateTimePicker1.Value, el, wt, 1);
ElectricityWaterBillsDAO.Insert(eee);
}
else
{
ElectricityWaterBills eee = new ElectricityWaterBills(1, comboBoxRoom.SelectedValue.ToString(), dateTimePicker1.Value, el, wt, 0);
ElectricityWaterBillsDAO.Insert(eee);
}
DataTable dt1 = ElectricityWaterBillsDAO.GetDataTable();
dataGridView1.DataSource = dt1;
}
}
public static bool Update(ElectricityWaterBills ewb)
{
string sql = "update ElectricityWaterBills set soDien = @soDien, soNuoc = @soNuoc, paid = @paid where id = @id";
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@id", ewb.Id);
cmd.Parameters.AddWithValue("@soDien", ewb.SoDien);
cmd.Parameters.AddWithValue("@soNuoc", ewb.SoNuoc);
cmd.Parameters.AddWithValue("@paid", ewb.Paid);
return(DAO.UpdateTable(cmd));
}
// Get by month year??
public static bool Insert(ElectricityWaterBills ewb)
{
string sql = "insert into ElectricityWaterBills (roomName, monthYear, soDien, soNuoc, paid) values (@roomName, @monthYear, @soDien, @soNuoc, @paid)";
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@roomName", ewb.RoomName);
cmd.Parameters.AddWithValue("@monthYear", ewb.MonthYear);
cmd.Parameters.AddWithValue("@soDien", ewb.SoDien);
cmd.Parameters.AddWithValue("@soNuoc", ewb.SoNuoc);
cmd.Parameters.AddWithValue("@paid", ewb.Paid);
return(DAO.UpdateTable(cmd));
}
