public List <DoanhSoTheoNgay> layDoanhSo(string thang, string nam)
{
List <DoanhSoTheoNgay> arr = new List <DoanhSoTheoNgay>();
KetNoiCSDL.moKetNoi();
string get = "SELECT ngayLap,SUM(soLuong*donGia) as 'gia' FROM HoaDon inner join HoaDonChiTiet on HoaDon.maHoaDon = HoaDonChiTiet.maHoaDon inner join Hang on Hang.maHang = HoaDonChiTiet.maHang where MONTH(ngayLap) = @thang and YEAR(ngayLap) = @nam group by ngayLap";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("thang", int.Parse(thang));
cmd.Parameters.AddWithValue("nam", int.Parse(nam));
SqlDataReader dr = cmd.ExecuteReader();
int stt = 1;
while (dr.Read())
{
DoanhSoTheoNgay ds = new DoanhSoTheoNgay(stt + "", ((DateTime)dr["ngayLap"]).ToString("dd/MM/yyyy"), float.Parse(dr["gia"] + ""));
arr.Add(ds);
stt++;
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public void xoaNhanVien(NhanVien nv)
{
KetNoiCSDL.moKetNoi();
string xoa = "delete from NhanVien where maNhanVien=@maNhanVien";
SqlCommand cmd = new SqlCommand(xoa, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhanVien", nv.MaNhanVien);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaNhaCungCap(NhaCungCap ncc)
{
KetNoiCSDL.moKetNoi();
string xoa = "delete from NhaCungCap where maNhaCungCap=@maNhaCungCap";
SqlCommand cmd = new SqlCommand(xoa, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhaCungCap", ncc.MaNhaCungCap);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaLichSuGia(LichSuGia lsg)
{
KetNoiCSDL.moKetNoi();
string del = "delete from LichSuGia where maHang=@maHang and ngayBatDau=@ngayBatDau ";
SqlCommand cmd = new SqlCommand(del, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHang", lsg.MaHang);
cmd.Parameters.AddWithValue("ngayBatDau", lsg.NgayBatDau);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaHang(Hang h)
{
string sqlUpdate = "delete from Hang where maHang=@maHang";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHang", h.MaHang);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaLop(Lop l)
{
string sqlUpdate = "delete from Lop where maLop=@maLop";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLop", l.MaLop);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaLoaiHang(LoaiHang lh)
{
string sqlUpdate = "delete from LoaiHang where maLoai=@maLoai";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLoai", lh.MaLoai);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaMonHoc(MonHoc mh)
{
string sqlUpdate = "delete from MonHoc where maMon=@maMon";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maMon", mh.MaMon);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaHoSoHocSinh(HoSoHocSinh hs)
{
string sqlUpdate = "delete from HoSoHocSinh where maHocSinh=@maHocSinh";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHocSinh", hs.MaHocSinh);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaCanBoGiaoVien(CanBoGiaoVien cb)
{
string sqlUpdate = "delete from CanBoGiaoVien where maCBGV=@maCBGV";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maCBGV", cb.MaCBGV);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public DataTable layHoSoHocSinhCoTenLop()
{
KetNoiCSDL.moKetNoi();
string get = "select maHocSinh ,hoTen ,ngaySinh, gioiTinh, diaChi, diemVaoTruong, hoTenBoMe, soDienThoai ,tenLop from HoSoHocSinh inner join Lop on HoSoHocSinh.maLop=Lop.maLop";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}
public DataTable layLopCoTen()
{
KetNoiCSDL.moKetNoi();
string get = "select maLop ,tenLop ,nienKhoa, siSo, hoTen from CanBoGiaoVien inner join Lop on CanBoGiaoVien.maCBGV=Lop.maCBGV";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}
/* public List<PhanCongGiangDay> layMonHoc()
* {
* List<PhanCongGiangDay> arr = new List<PhanCongGiangDay>();
* KetNoiCSDL.moKetNoi();
* string get = "select * from PhanCongGiangDay";
* SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
* SqlDataReader dr = cmd.ExecuteReader();
* while (dr.Read())
* {
* PhanCongGiangDay x = new PhanCongGiangDay(dr["maLop"] + "", dr["maMon"] + "", dr["maCBGV"] + "", ((DateTime)dr["ngayPhanCong"]).ToString("dd/MM/yyyy"));
* arr.Add(x);
* }
* KetNoiCSDL.dongKetNoi();
* return arr;
* }*/
public DataTable layPhanCongGiangDayTheoTen()
{
KetNoiCSDL.moKetNoi();
string get = "select tenLop ,tenMon ,hoTen, ngayPhanCong from PhanCongGiangDay inner join MonHoc on PhanCongGiangDay.maMon=MonHoc.maMon inner join CanBoGiaoVien on CanBoGiaoVien.maCBGV = PhanCongGiangDay.maCBGV inner join Lop on Lop.maLop = PhanCongGiangDay.maLop";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}
public void suaNhaCungCap(NhaCungCap ncc)
{
KetNoiCSDL.moKetNoi();
string sua = "update NhaCungCap set tenNhaCungCap=@tenNhaCungCap,diaChi=@diaChi,soDienThoai=@soDienThoai where maNhaCungCap=@maNhaCungCap";
SqlCommand cmd = new SqlCommand(sua, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhaCungCap", ncc.MaNhaCungCap);
cmd.Parameters.AddWithValue("tenNhaCungCap", ncc.TenNhaCungCap);
cmd.Parameters.AddWithValue("diaChi", ncc.DiaChi);
cmd.Parameters.AddWithValue("soDienThoai", ncc.SoDienThoai);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void themNhaCungCap(NhaCungCap ncc)
{
KetNoiCSDL.moKetNoi();
string them = "insert into NhaCungCap values (@maNhaCungCap,@tenNhaCungCap,@diaChi,@soDienThoai)";
SqlCommand cmd = new SqlCommand(them, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhaCungCap", ncc.MaNhaCungCap);
cmd.Parameters.AddWithValue("tenNhaCungCap", ncc.TenNhaCungCap);
cmd.Parameters.AddWithValue("diaChi", ncc.DiaChi);
cmd.Parameters.AddWithValue("soDienThoai", ncc.SoDienThoai);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void suaNhanVien(NhanVien nv)
{
KetNoiCSDL.moKetNoi();
string sua = "update NhanVien set hoTen=@hoTen,diaChi=@diaChi,soDienThoai=@soDienThoai where maNhanVien=@maNhanVien";
SqlCommand cmd = new SqlCommand(sua, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhanVien", nv.MaNhanVien);
cmd.Parameters.AddWithValue("hoTen", nv.HoTen);
cmd.Parameters.AddWithValue("diaChi", nv.DiaChi);
cmd.Parameters.AddWithValue("soDienThoai", nv.SoDienThoai);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void themLoaiHang(LoaiHang lh)
{
string sqlSelect = "insert into LoaiHang values(@maLoai, @tenLoai, @ghiChu)";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLoai", lh.MaLoai);
cmd.Parameters.AddWithValue("tenLoai", lh.TenLoai);
cmd.Parameters.AddWithValue("ghiChu", lh.GhiChu);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void suaDiem2(Diem d)
{
string sqlUpdate = "update Diem set diemTBKy2 = @diemTBKy2 where maHocSinh =@maHocSinh and maMon=@maMon ";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("diemTBKy2", d.DiemTBKy2);
cmd.Parameters.AddWithValue("maHocSinh", d.MaHocSinh);
cmd.Parameters.AddWithValue("maMon", d.MaMonHoc);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void suaMonHoc(MonHoc mh)
{
string sqlUpdate = "update MonHoc set tenMon =@tenMon , soTiet =@soTiet where maMon =@maMon ";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maMon", mh.MaMon);
cmd.Parameters.AddWithValue("tenMon", mh.TenMon);
cmd.Parameters.AddWithValue("soTiet", mh.SoTiet);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void themMonHoc(MonHoc mh)
{
string sqlSelect = "insert into MonHoc values(@maMon ,@tenMon ,@soTiet)";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maMon", mh.MaMon);
cmd.Parameters.AddWithValue("tenMon", mh.TenMon);
cmd.Parameters.AddWithValue("soTiet", mh.SoTiet);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public DataTable inHoaDon()
{
string get = "select HoaDon.maHoaDon, HoaDon.maKhachHang, KhachHang.hoTen,HoaDon.ngayLap,HoaDon.maNhanVien,NhanVien.hoTen,HoaDonChiTiet.maHang,Hang.donGia,HoaDonChiTiet.soLuong,HoaDonChiTiet.soLuong*Hang.donGia as 'thanh tien' from HoaDon inner join HoaDonChiTiet on HoaDonChiTiet.maHoaDon=HoaDon.maHoaDon inner join Hang on Hang.maHang=HoaDonChiTiet.maHang inner join KhachHang on HoaDon.maKhachHang=KhachHang.maKhachHang inner join NhanVien on NhanVien.maNhanVien=HoaDon.maNhanVien group by HoaDon.maHoaDon, HoaDon.maKhachHang, KhachHang.hoTen,HoaDon.ngayLap,HoaDon.maNhanVien,NhanVien.hoTen,HoaDonChiTiet.maHang,Hang.donGia,HoaDonChiTiet.soLuong";
KetNoiCSDL.moKetNoi();
SqlCommand sqlCommand = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(sqlDataReader);
KetNoiCSDL.dongKetNoi();
return(dataTable);
}
public void themNhanVien(NhanVien nv)
{
KetNoiCSDL.moKetNoi();
string them = "insert into NhanVien values (@maNhanVien,@hoTen,@diaChi,@soDienThoai)";
SqlCommand cmd = new SqlCommand(them, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhanVien", nv.MaNhanVien);
cmd.Parameters.AddWithValue("hoTen", nv.HoTen);
cmd.Parameters.AddWithValue("diaChi", nv.DiaChi);
cmd.Parameters.AddWithValue("soDienThoai", nv.SoDienThoai);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void suaLoaiHang(LoaiHang lh)
{
string sqlUpdate = "update LoaiHang set tenLoai=@tenLoai, ghiChu=@ghiChu where maLoai=@maLoai";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLoai", lh.MaLoai);
cmd.Parameters.AddWithValue("tenLoai", lh.TenLoai);
cmd.Parameters.AddWithValue("ghiChu", lh.GhiChu);;
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public DataTable layPhanCongGiangDayTheoTenCoDieuKienTheoLopHoc(string maLop)
{
KetNoiCSDL.moKetNoi();
string get = "select tenLop ,tenMon ,hoTen, ngayPhanCong from PhanCongGiangDay inner join MonHoc on PhanCongGiangDay.maMon=MonHoc.maMon inner join CanBoGiaoVien on CanBoGiaoVien.maCBGV = PhanCongGiangDay.maCBGV inner join Lop on Lop.maLop = PhanCongGiangDay.maLop where PhanCongGiangDay.maLop = @maLop";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLop", maLop);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}
public DataTable layHoSoHocSinhCoTenLopTheoDieuKien(string maHocSinh)
{
KetNoiCSDL.moKetNoi();
string get = "select maHocSinh ,hoTen ,ngaySinh, gioiTinh, diaChi, diemVaoTruong, hoTenBoMe, soDienThoai ,tenLop from HoSoHocSinh inner join Lop on HoSoHocSinh.maLop=Lop.maLop where maHocSinh=@maHocSinh";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHocSinh", maHocSinh);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}
public void themHoaDon(HoaDon hoaDon)
{
KetNoiCSDL.moKetNoi();
string add = "insert into HoaDon values (@maHoaDon, @maKhachHang, @ngayLap, @maNhanVien)";
SqlCommand cmd = new SqlCommand(add, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHoaDon", hoaDon.MaHoaDon);
cmd.Parameters.AddWithValue("maKhachHang", hoaDon.MaKhachHang);
cmd.Parameters.AddWithValue("ngayLap", hoaDon.NgayLap);
cmd.Parameters.AddWithValue("maNhanVien", hoaDon.MaNhanVien);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void xoaKhachHang(KhachHang kh)
{
List <KhachHang> arr = new List <KhachHang>();
KetNoiCSDL.moKetNoi();
string update = "delete from KhachHang where maKhachHang=@maKhachHang ";
SqlCommand cmd = new SqlCommand(update, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maKhachHang", kh.MaKhachHang);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void suaLichSuGia(LichSuGia lsg)
{
KetNoiCSDL.moKetNoi();
string update = "update LichSuGia set ngayKetThuc = @ngayKetThuc, donGia = @donGia, ngayCapNhat=@ngayCapNhat where maHang=@maHang and ngayBatDau=@ngayBatDau ";
SqlCommand cmd = new SqlCommand(update, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("ngayKetThuc", lsg.NgayKetThuc);
cmd.Parameters.AddWithValue("donGia", lsg.DonGia);
cmd.Parameters.AddWithValue("ngayCapNhat", lsg.NgayCapNhat);
cmd.Parameters.AddWithValue("maHang", lsg.MaHang);
cmd.Parameters.AddWithValue("ngayBatDau", lsg.NgayBatDau);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void themLichSuGia(LichSuGia ls)
{
KetNoiCSDL.moKetNoi();
string add = "insert into LichSuGia values (@maHang, @ngayBatDau, @ngayKetThuc, @donGia, @ngayCapNhat)";
SqlCommand cmd = new SqlCommand(add, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHang", ls.MaHang);
cmd.Parameters.AddWithValue("ngayBatDau", (ls.NgayBatDau));
cmd.Parameters.AddWithValue("ngayKetThuc", ls.NgayKetThuc);
cmd.Parameters.AddWithValue("donGia", ls.DonGia);
cmd.Parameters.AddWithValue("ngayCapNhat", ls.NgayCapNhat);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public DataTable layTatCaLichSuGiaCoTenHang()
{
List <LichSuGia> arr = new List <LichSuGia>();
KetNoiCSDL.moKetNoi();
string get = "select Hang.maHang, tenHang, ngayBatDau, ngayKetThuc, LichSuGia.donGia, ngayCapNhat from LichSuGia inner join Hang on Hang.maHang = LichSuGia.maHang";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}
