public int SaveBill(Customer c, string staffId, int sum, List<BillDetail>proList)
{
try {
string now = DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
string lastBillId = dal.GetLastBillId().Rows.Count == 0 ? "" : dal.GetLastBillId().Rows[0][0].ToString();
Bill b = new Bill(AddBillBLL.NextId("HD", lastBillId), now, c.CustomerId, staffId, "aaa", sum, proList);
return dal.AddBill(b, c, "KH" + GetLastCustomer());
}
catch (Exception ex) { throw ex; }
}
public List<Bill> GetAllBill()
{
try
{
//DataTable dt = SqlQuery.readSQL("Select MaHD, NgayHD, HOADON.MaKH, MaNV, TenKH,ThanhTien from HOADON join KHACHHANG on HOADON.MaKH=KHACHHANG.MaKH ");
DataTable dt = SqlHelper.ExecuteDataset(Constants.ConnectionString, CommandType.Text, "Select MaHD, NgayHD, HOADON.MaKH, MaNV, TenKH,ThanhTien from HOADON join KHACHHANG on HOADON.MaKH=KHACHHANG.MaKH ").Tables[0];
List<Bill> listBill = new List<Bill>();
for (int i = 0; i < dt.Rows.Count; i++)
{
List<BillDetail> detailList = this.GetBillDetail(dt.Rows[0][0].ToString());
Bill b = new Bill(dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString(), dt.Rows[i][3].ToString(), dt.Rows[i][4].ToString(), int.Parse(dt.Rows[i][5].ToString().Substring(0, dt.Rows[i][5].ToString().IndexOf("."))), detailList);
listBill.Add(b);
}
return listBill;
}
catch (Exception ex)
{ throw ex; }
}
//proList: Danh sách mã sản phẩm và số lượng tương ứng của một hóa đơn id= billId
public int AddBill(Bill data, Customer c, string idcusnew)
{
try {
SqlConnection con = new SqlConnection(Constants.ConnectionString);
con.Open();
SqlTransaction tran = con.BeginTransaction();
try {
Customer cc = GetCustomerByID(c.CustomerId);
int p0 = 1;
if (cc.Equals(c) == false)
{
data.CustomerId = idcusnew;
SqlParameter[] para0 =
{
new SqlParameter("@MaKH",idcusnew),
new SqlParameter("@TenKH",c.CustomerName),
new SqlParameter("@DiaChi",c.Address),
new SqlParameter("@SoDT",c.PhoneNumber),
new SqlParameter("@CMND",c.IdNumber),
new SqlParameter("@Email",""),
};
//save cus
// p0 = SqlQuery.writeSQL("insert into KHACHHANG VALUES (@MaKH, @TenKH, @DiaChi, @SoDT, @CMND,@Email)", para0);
p0 = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "insert into KHACHHANG VALUES (@MaKH, @TenKH, @DiaChi, @SoDT, @CMND,@Email)", para0);
}
SqlParameter[] para1 =
{
new SqlParameter("@MaHD",data.BillId),
new SqlParameter("@NgayHD",data.BillDate.ToString()),
new SqlParameter("@MaKH",data.CustomerId),
new SqlParameter("@MaNV",data.StaffId),
new SqlParameter("@ThanhTien",data.Sum),
};
//int p1 = SqlQuery.writeSQL("insert into HOADON VALUES (@MaHD, @NgayHD, @MaKH, @MaNV, @ThanhTien)", para1);
int p1 = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "insert into HOADON VALUES (@MaHD, @NgayHD, @MaKH, @MaNV, @ThanhTien)", para1);
int p2 = 0, p3 = 0;
for (int i = 0; i < data.ProductList.Count; i++)
{
SqlParameter[] listpara ={
new SqlParameter("@MaHD", data.BillId),
new SqlParameter("@MaSP",data.ProductList[i].ProductId),
new SqlParameter("@SL", data.ProductList[i].Amount),
};
SqlParameter[] listpara11 ={
new SqlParameter("@MaSP",data.ProductList[i].ProductId),
new SqlParameter("@SL", data.ProductList[i].Amount),
};
//string aaa = "insert into CHITIETHOADON VALUES ('"+billId+"','"+proList[i].proId+"'," + proList[i].num + ")";
//SqlQuery.writeSQL(aaa);
// p2 = SqlQuery.writeSQL("insert into CHITIETHOADON VALUES (@MaHD,@MaSP,@SL)", listpara);
p2 = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "insert into CHITIETHOADON VALUES (@MaHD,@MaSP,@SL)", listpara);
//p3 = SqlQuery.writeSQL("update SANPHAM set SoLuong=SoLuong-@SL where MaSanPham=@MaSP", listpara);
p3 = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, "update SANPHAM set SoLuong=SoLuong-@SL where MaSanPham=@MaSP", listpara11);
}
tran.Commit();
return p0 * p1 * p2 * p3;
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
catch(Exception ex)
{
throw new Exception("Lỗi kết nối CSDL");
}
}