public List <ThongTinDichVu> getDV()
{
List <ThongTinDichVu> serviceInfo = new List <ThongTinDichVu>();
string query = "select IdDV,TenDV,DonGia from DichVu";
using (SqlConnection conn = new SqlConnection(ConnectionString.connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ThongTinDichVu info;
info = new ThongTinDichVu(reader.GetString(0), reader.GetString(1), 0);
if (!reader.IsDBNull(2))
{
info.Dg = reader.GetInt32(2);
}
serviceInfo.Add(info);
}
}
conn.Close();
}
return(serviceInfo);
}
public bool addDV(string bill, ThongTinDichVu dv)
{
string query = "insert into BillInfo(IdBill,IdDV,DonGia,SoLuong) " +
"values ('" + bill + "','" + dv.Dv + "'," + dv.Dg + "," + dv.Sl + ")";
bool b = false;
using (SqlConnection conn = new SqlConnection(ConnectionString.connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
if (cmd.ExecuteNonQuery() > 0)
{
b = true;
}
conn.Close();
}
return(b);
}
public List <ThongTinDichVu> getDV(string idBill, string a)
{
List <ThongTinDichVu> serviceInfo = new List <ThongTinDichVu>();
string query = "";
if (a == "ADD")
{
query = "select TenDV,DichVu.DonGia,SoLuong from DichVu join BillInfo on DichVu.IdDV=BillInfo.IdDV where IdBill='" + idBill + "'";
}
if (a == "EDIT")
{
query = "select TenDV,BillInfo.DonGia,SoLuong from DichVu join BillInfo on DichVu.IdDV=BillInfo.IdDV where IdBill='" + idBill + "'";
}
using (SqlConnection conn = new SqlConnection(ConnectionString.connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ThongTinDichVu info;
info = new ThongTinDichVu(reader.GetString(0), 0, 0);
if (!reader.IsDBNull(1))
{
info.Dg = reader.GetInt32(1);
}
if (!reader.IsDBNull(2))
{
info.Sl = reader.GetInt32(2);
}
info.Sum = info.Sl * info.Dg;
serviceInfo.Add(info);
//MessageBox.Show(info.Dg.ToString() + "-------" + info.Sl.ToString() + "----------+" + info.Sum.ToString());
}
}
conn.Close();
}
return(serviceInfo);
}
//
// Chức năng ADD
//
public void add()
{
string bill = new DatabaseConnection().getIDBill(curentZoom);
ThongTinDichVu dv = comboBox1.SelectedItem as ThongTinDichVu;
ThongTinDichVu add = new ThongTinDichVu(dv.Id, 0, 0);
if (tbDonGia.Text != "")
{
add.Dg = Convert.ToInt32(tbDonGia.Text);
}
if (tbSoLuong.Text != "")
{
add.Sl = Convert.ToInt32(tbSoLuong.Text);
}
bool b = new DatabaseConnection().addDV(bill, add);
if (b)
{
MessageBox.Show("Thêm thành công!");
this.Dispose();
}
else
{
MessageBox.Show("Thất bại");
}
//var package = new ExcelPackage(new FileInfo("CurrentCustomer.xlsx"));
//ExcelWorksheet a = package.Workbook.Worksheets[0];
//for (int i = 1; i <= a.Dimension.End.Row; i++)
//{
// if (Convert.ToString(a.Cells[i + 1, 2].Value) == curentZoom)
// {
// ///Load thong tin dich vu vao ListDV
// if (Convert.ToString(a.Cells[i + 3, 3].Value) == "")
// {
// listDV.Add(new ThongTinDichVu(Convert.ToString(a.Cells[i + 3, 1].Value), Convert.ToInt32(a.Cells[i + 3, 2].Value), 0));
// }
// else listDV.Add(new ThongTinDichVu(Convert.ToString(a.Cells[i + 3, 1].Value), Convert.ToInt32(a.Cells[i + 3, 2].Value), Convert.ToInt32(a.Cells[i + 3, 3].Value)));
// for (int j = 1; j < 9; j++)
// {
// if (Convert.ToString(a.Cells[i + 3 + j, 1].Value) == "") break;
// else
// {
// listDV.Add(new ThongTinDichVu(Convert.ToString(a.Cells[i + 3 + j, 1].Value), Convert.ToInt32(a.Cells[i + 3 + j, 2].Value), Convert.ToInt32(a.Cells[i + 3 + j, 3].Value)));
// //MessageBox.Show(Convert.ToString(a.Cells[i + 3 + j, 3].Value));
// }
// }
// }
//}
//if (tbDichVu.Text != "" && tbDonGia.Text != "" && tbSoLuong.Text != "")
//{
// // Thêm 1 dịch vụ
// listDV.Add(new ThongTinDichVu(tbDichVu.Text, Convert.ToInt32(tbDonGia.Text), Convert.ToInt32(tbSoLuong.Text)));
// //Fill List bang cac dich vu rong
// int count = listDV.Count;
// for (int i = count; i < 10; i++)
// {
// listDV.Add(new ThongTinDichVu());
// }
// // Ghi đè lại list về Excel
// for (int i = 1; i <= a.Dimension.End.Row; i++)
// {
// if (Convert.ToString(a.Cells[i + 1, 2].Value) == curentZoom)
// {
// for (int j = 0; j < 10; j++)
// {
// a.Cells[i + 3 + j, 1].Value = listDV[j].Dv;
// a.Cells[i + 3 + j, 2].Value = listDV[j].Dg;
// a.Cells[i + 3 + j, 3].Value = listDV[j].Sl;
// }
// }
// }
// //
// //Lưu File
// //
// Byte[] bin = package.GetAsByteArray();
// File.WriteAllBytes("CurrentCustomer.xlsx", bin);
// this.Dispose();
//}
//else
//{
// MessageBox.Show("Điền đầy đủ thông tin trước!!");
//}
}