public void XoaNV(string ma)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl2 = "SELECT MaHoaDon FROM HoaDon WHERE MaNhanVien='" + ma + "'";
SqlCommand cmd2 = new SqlCommand(sqlsl2, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd2.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCoSoDuLieu.Dongketnoi();
for (int i = 0; i < tb.Rows.Count; i++)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "DELETE FROM HoaDonChiTiet WHERE MaHoaDon='" + tb.Rows[i]["MaHoaDon"].ToString() + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl3 = "DELETE FROM HoaDon WHERE MaNhanVien='" + ma + "'";
SqlCommand cmd3 = new SqlCommand(sqlsl3, KetNoiCoSoDuLieu.chuoiketnoi);
cmd3.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl4 = "DELETE FROM NhanVien WHERE MaNhanVien='" + ma + "'";
SqlCommand cmd4 = new SqlCommand(sqlsl4, KetNoiCoSoDuLieu.chuoiketnoi);
cmd4.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void SuaHDCT(string ma, string ten, string gc)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "UPDATE HoaDonChiTiet SET SoLuong='" + gc + "' WHERE MaHoaDon='" + ma + "' AND MaHang='" + ten + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void SuaKH(string ma, string ten, string dg, string sl)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "UPDATE KhachHang SET HoTen='" + ten + "', DiaChi='" + dg + "', SoDienThoai='" + sl + "' WHERE MaKhachHang='" + ma + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void SuaLSG(string ma, string ten, string dg, string sl, string ncc)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "UPDATE LichSuGia SET NgayKetThuc='" + dg + "', DonGia='" + sl + "', NgayCapNhat='" + ncc + "' WHERE MaHang='" + ma + "' AND NgayBatDau='" + ten + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void XoaNV(string ma)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "DELETE FROM NhanVien WHERE MaNhanVien='" + ma + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void XoaHDCT(string ma, string ten)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "DELETE FROM HoaDonChiTiet WHERE MaHoaDon='" + ma + "' AND MaHang='" + ten + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void SuaHD(string ma, string ten, string dg, string sl)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "UPDATE HoaDon SET MaKhachHang='" + ten + "', NgayLap='" + dg + "', MaNhanVien='" + sl + "' WHERE MaHoaDon='" + ma + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void SuaLoaiHang(string ma, string ten, string gc)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "UPDATE LoaiHang SET TenLoai='" + ten + "', GhiChu='" + gc + "' WHERE MaLoai='" + ma + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void XoaLSG(string ma, string nbd)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "DELETE FROM LichSuGia WHERE MaHang='" + ma + "' AND NgayBatDau='" + nbd + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void SuaNCC(string ma, string ten, string dg, string sl)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "UPDATE NhaCungCap SET TenNhaCungCap='" + ten + "', DiaChi='" + dg + "', SoDienThoai='" + sl + "' WHERE MaNhaCungCap='" + ma + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void SuaHang(string ma, string ten, string dvt, string dg, string sl)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "UPDATE Hang SET TenSanPham='" + ten + "', DonGia='" + dg + "', SoLuong='" + sl + "', DonViTinh='" + dvt + "' WHERE MaSanPham='" + ma + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public DataTable XemLSG(string ma, string ten)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "SELECT * FROM LichSuGia WHERE MaHang='" + ma + "' AND NgayBatDau='" + ten + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCoSoDuLieu.Dongketnoi();
return(tb);
}
public void ThemHDCT(string ma, string ten, string gc)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "INSERT INTO HoaDonChiTiet VALUES(@ma, @ten, @gc)";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.Parameters.AddWithValue("ma", ma);
cmd.Parameters.AddWithValue("ten", ten);
cmd.Parameters.AddWithValue("gc", gc);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public DataTable XemHang(string ma)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "SELECT * FROM Hang WHERE MaSanPham='" + ma + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCoSoDuLieu.Dongketnoi();
return(tb);
}
public void ThemKH(string ma, string ten, string dvt, string dg)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "INSERT INTO KhachHang VALUES(@ma, @ten, @dvt, @dg)";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.Parameters.AddWithValue("ma", ma);
cmd.Parameters.AddWithValue("ten", ten);
cmd.Parameters.AddWithValue("dvt", dvt);
cmd.Parameters.AddWithValue("dg", dg);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public NhaCungCapDTO getNhaCungCap(String ma)
{
NhaCungCapDTO ncc = null;
KetNoiCoSoDuLieu.Moketnoi();
string SQlSL = "SELECT * FROM NhaCungCap";
SqlCommand cmd = new SqlCommand(SQlSL, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ncc = new NhaCungCapDTO(dr["MaNhaCungCap"].ToString(), dr["TenNhaCungCap"].ToString(), dr["DiaChi"].ToString(), dr["SoDienThoai"].ToString());
}
KetNoiCoSoDuLieu.Dongketnoi();
return(ncc);
}
public NhanVienDTO getNhanVien(String ma)
{
NhanVienDTO nv = new NhanVienDTO();
KetNoiCoSoDuLieu.Moketnoi();
string SQlSL = "select * from NhanVien where MaNhanVien='" + ma + "'";
SqlCommand cmd = new SqlCommand(SQlSL, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
nv = new NhanVienDTO(dr["MaNhanVien"].ToString(), dr["HoTen"].ToString(), dr["DiaChi"].ToString(), dr["SoDienThoai"].ToString());
}
KetNoiCoSoDuLieu.Dongketnoi();
return(nv);
}
public List <LichSuGiaDTO> BangLichSuGia()
{
List <LichSuGiaDTO> ds = new List <LichSuGiaDTO>();
KetNoiCoSoDuLieu.Moketnoi();
string SQlSL = "SELECT * FROM LichSuGia";
SqlCommand cmd = new SqlCommand(SQlSL, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LichSuGiaDTO sp = new LichSuGiaDTO(dr["MaHang"].ToString(), dr["NgayBatDau"].ToString(), dr["NgayKetThuc"].ToString(), dr["DonGia"].ToString(), dr["NgayCapNhat"].ToString());
ds.Add(sp);
}
KetNoiCoSoDuLieu.Dongketnoi();
return(ds);
}
public List <HangDTO> BangHang()
{
List <HangDTO> ds = new List <HangDTO>();
KetNoiCoSoDuLieu.Moketnoi();
string SQlSL = "select *, h.DonGia as 'STT' from Hang h";
SqlCommand cmd = new SqlCommand(SQlSL, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
HangDTO sp = new HangDTO(dr["DonGia"].ToString(), dr["MaSanPham"].ToString(), dr["TenSanPham"].ToString(), dr["DonViTinh"].ToString(), dr["DonGia"].ToString(), dr["MaLoai"].ToString(), dr["SoLuong"].ToString(), dr["MaNhaCungCap"].ToString());
ds.Add(sp);
}
KetNoiCoSoDuLieu.Dongketnoi();
return(ds);
}
public List <KhachHangDTO> BangKhachHang()
{
List <KhachHangDTO> ds = new List <KhachHangDTO>();
KetNoiCoSoDuLieu.Moketnoi();
string SQlSL = "SELECT * FROM KhachHang";
SqlCommand cmd = new SqlCommand(SQlSL, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
KhachHangDTO sp = new KhachHangDTO(dr["MaKhachHang"].ToString(), dr["HoTen"].ToString(), dr["DiaChi"].ToString(), dr["SoDienThoai"].ToString());
ds.Add(sp);
}
KetNoiCoSoDuLieu.Dongketnoi();
return(ds);
}
public List <HoaDonDTO> BangHoaDon()
{
List <HoaDonDTO> ds = new List <HoaDonDTO>();
KetNoiCoSoDuLieu.Moketnoi();
string SQlSL = "SELECT * FROM HoaDon";
SqlCommand cmd = new SqlCommand(SQlSL, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
HoaDonDTO sp = new HoaDonDTO(dr["MaHoaDon"].ToString(), dr["MaKhachHang"].ToString(), dr["NgayLap"].ToString(), dr["MaNhanVien"].ToString());
ds.Add(sp);
}
KetNoiCoSoDuLieu.Dongketnoi();
return(ds);
}
public List <NhaCungCapDTO> BangNhaCungCap()
{
List <NhaCungCapDTO> ds = new List <NhaCungCapDTO>();
KetNoiCoSoDuLieu.Moketnoi();
string SQlSL = "SELECT * FROM NhaCungCap";
SqlCommand cmd = new SqlCommand(SQlSL, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NhaCungCapDTO sp = new NhaCungCapDTO(dr["MaNhaCungCap"].ToString(), dr["TenNhaCungCap"].ToString(), dr["DiaChi"].ToString(), dr["SoDienThoai"].ToString());
ds.Add(sp);
}
KetNoiCoSoDuLieu.Dongketnoi();
return(ds);
}
public List <LoaiHangDTO> BangLoaiHang()
{
List <LoaiHangDTO> ds = new List <LoaiHangDTO>();
KetNoiCoSoDuLieu.Moketnoi();
string SQlSL = "select *, lh.MaLoai as 'STT' from LoaiHang lh";
SqlCommand cmd = new SqlCommand(SQlSL, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LoaiHangDTO sp = new LoaiHangDTO(dr["STT"].ToString(), dr["MaLoai"].ToString(), dr["TenLoai"].ToString(), dr["GhiChu"].ToString());
ds.Add(sp);
}
KetNoiCoSoDuLieu.Dongketnoi();
return(ds);
}
public void ThemHang(string ma, string ten, string dvt, string dg, string ml, string sl, string ncc)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "INSERT INTO Hang VALUES(@ma, @ten, @dvt, @dg, @ml, @sl, @ncc)";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.Parameters.AddWithValue("ma", ma);
cmd.Parameters.AddWithValue("ten", ten);
cmd.Parameters.AddWithValue("dvt", dvt);
cmd.Parameters.AddWithValue("dg", dg);
cmd.Parameters.AddWithValue("ml", ml);
cmd.Parameters.AddWithValue("sl", sl);
cmd.Parameters.AddWithValue("ncc", ncc);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public bool KTMaTrung(string ma, string nbd)
{
bool check = false;
KetNoiCoSoDuLieu.Moketnoi();
string Sql = "select * from LichSuGia where MaHang='" + ma + "' AND NgayBatDau='" + nbd + "'";
SqlDataAdapter da_kiemtra = new SqlDataAdapter(Sql, KetNoiCoSoDuLieu.chuoiketnoi);
DataTable dt_kiemtra = new DataTable();
da_kiemtra.Fill(dt_kiemtra);
if (dt_kiemtra.Rows.Count > 0)
{
check = true;
}
da_kiemtra.Dispose();
KetNoiCoSoDuLieu.Dongketnoi();
return(check);
}
public bool KTMaTrung(string ma)
{
bool check = false;
KetNoiCoSoDuLieu.Moketnoi();
string Sql = "select * from NhaCungCap where MaNhaCungCap='" + ma + "'";
SqlDataAdapter da_kiemtra = new SqlDataAdapter(Sql, KetNoiCoSoDuLieu.chuoiketnoi);
DataTable dt_kiemtra = new DataTable();
da_kiemtra.Fill(dt_kiemtra);
if (dt_kiemtra.Rows.Count > 0)
{
check = true;
}
da_kiemtra.Dispose();
KetNoiCoSoDuLieu.Dongketnoi();
return(check);
}
public void XoaLoaiHang(string ma)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl2 = "SELECT MaSanPham FROM Hang WHERE MaLoai='" + ma + "'";
SqlCommand cmd2 = new SqlCommand(sqlsl2, KetNoiCoSoDuLieu.chuoiketnoi);
SqlDataReader dr = cmd2.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCoSoDuLieu.Dongketnoi();
for (int i = 0; i < tb.Rows.Count; i++)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "DELETE FROM HoaDonChiTiet WHERE MaHang='" + tb.Rows[i]["MaSanPham"].ToString() + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl1 = "DELETE FROM LichSuGia WHERE MaHang='" + tb.Rows[i]["MaSanPham"].ToString() + "'";
SqlCommand cmd1 = new SqlCommand(sqlsl1, KetNoiCoSoDuLieu.chuoiketnoi);
cmd1.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
KetNoiCoSoDuLieu.Moketnoi();
string sqls3 = "DELETE FROM Hang WHERE MaLoai='" + ma + "'";
SqlCommand cmd3 = new SqlCommand(sqls3, KetNoiCoSoDuLieu.chuoiketnoi);
cmd3.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl4 = "DELETE FROM LoaiHang WHERE MaLoai='" + ma + "'";
SqlCommand cmd4 = new SqlCommand(sqlsl4, KetNoiCoSoDuLieu.chuoiketnoi);
cmd4.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
public void XoaHang(string ma)
{
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl1 = "DELETE FROM HoaDonChiTiet WHERE MaHang='" + ma + "'";
SqlCommand cmd2 = new SqlCommand(sqlsl1, KetNoiCoSoDuLieu.chuoiketnoi);
cmd2.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
KetNoiCoSoDuLieu.Moketnoi();
string sqlsl = "DELETE FROM LichSuGia WHERE MaHang='" + ma + "'";
SqlCommand cmd = new SqlCommand(sqlsl, KetNoiCoSoDuLieu.chuoiketnoi);
cmd.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
KetNoiCoSoDuLieu.Moketnoi();
string sqls = "DELETE FROM Hang WHERE MaSanPham='" + ma + "'";
SqlCommand cmd1 = new SqlCommand(sqls, KetNoiCoSoDuLieu.chuoiketnoi);
cmd1.ExecuteNonQuery();
KetNoiCoSoDuLieu.Dongketnoi();
}
