public List <NhanVienDTO> DanhSachNhanVien() { List <NhanVienDTO> lstnhanvien = new List <NhanVienDTO>(); string caulenh = "SELECT MaNV, HoNV, TenNV, LoaiNV, MatKhau, GioiTinh,NgaySinh, DiaChi, SDT, Luong, HinhAnh, nv.TrangThai FROM NhanVien nv INNER JOIN LoaiNhanVien lnv ON nv.MaLoaiNV=lnv.MaLoaiNV WHERE nv.TrangThai = 1"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { NhanVienDTO nhanviendto = new NhanVienDTO(); nhanviendto.MaNV = dr["MaNV"].ToString(); nhanviendto.HoNV = dr[1].ToString(); nhanviendto.TenNV = dr[2].ToString(); nhanviendto.MaLoaiNV = dr[3].ToString(); nhanviendto.MatKhau = dr[4].ToString(); nhanviendto.GioiTinh = dr[5].ToString(); nhanviendto.NgaySinh = DateTime.Parse(dr[6].ToString()); nhanviendto.DiaChi = dr[7].ToString(); nhanviendto.SDT = dr[8].ToString(); nhanviendto.Luong = Convert.ToDouble(dr[9].ToString()); nhanviendto.HinhAnh = dr[10].ToString(); nhanviendto.TrangThai = int.Parse(dr[11].ToString()); lstnhanvien.Add(nhanviendto); } dr.Close(); conn.Close(); return(lstnhanvien); }
public string TangSanPham() { string newMaSP = "SP0001"; string caulenh = "SELECT MAX(MaSP) FROM SanPham"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); if (dr.Read()) { if (!dr.IsDBNull(0)) { int sp = Convert.ToInt32(dr.GetString(0).Remove(0, 2)); sp++; newMaSP = "SP" + sp.ToString("d4"); dr.Close(); conn.Close(); return(newMaSP); } else { return(newMaSP); } } return(newMaSP); }
public List <SanPhamDTO> DanhSachSanPham() { List <SanPhamDTO> lstsanpham = new List <SanPhamDTO>(); string caulenh = "Select MaSP, TenSP, TenLoaiSP, XuatXu, GiaTien, HinhAnh, SoLuong, TenNCC, sp.TrangThai FROM SanPham sp INNER JOIN LoaiSanPham lsp ON sp.MaLoaiSP = lsp.MaLoaiSP INNER JOIN NhaCungCap ncc ON sp.MaNCC = ncc.MaNCC where sp.TrangThai = 1"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { SanPhamDTO sanphamdto = new SanPhamDTO(); sanphamdto.MaSP = dr[0].ToString(); sanphamdto.TenSP = dr[1].ToString(); sanphamdto.MaLoaiSP = dr[2].ToString(); sanphamdto.XuatXu = dr[3].ToString(); sanphamdto.GiaTien = Convert.ToDouble(dr[4].ToString()); sanphamdto.HinhAnh = dr[5].ToString(); sanphamdto.SoLuong = int.Parse(dr[6].ToString()); sanphamdto.MaNCC = dr[7].ToString(); sanphamdto.TrangThai = int.Parse(dr[8].ToString()); lstsanpham.Add(sanphamdto); } dr.Close(); conn.Close(); return(lstsanpham); }
public string LayMaSPMax() { string masp; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); string query = "SELECT max(MaSP) from SanPham"; SqlDataReader read = DataProvider_QLCHTapHoa.TruyVanDuLieu(query, conn); read.Read(); masp = read[0].ToString(); return(masp); }
public static List <LoaiSanPhamDTO> DanhSachLoaiSanPham() { List <LoaiSanPhamDTO> lstloaisanpham = new List <LoaiSanPhamDTO>(); string caulenh = "SELECT * FROM LoaiSanPham"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { LoaiSanPhamDTO loai = new LoaiSanPhamDTO(); loai.MaLoaiSP = dr[0].ToString(); loai.TenLoaiSP = dr[1].ToString(); loai.TrangThai = int.Parse(dr[2].ToString()); lstloaisanpham.Add(loai); } return(lstloaisanpham); }
public static List <LoaiNhanVienDTO> DanhSachLoaiNhanVien() { List <LoaiNhanVienDTO> lstloainhanvien = new List <LoaiNhanVienDTO>(); string caulenh = "SELECT * FROM LoaiNhanVien"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { LoaiNhanVienDTO loai = new LoaiNhanVienDTO(); loai.MaLoaiNV = dr[0].ToString(); loai.LoaiNV = dr[1].ToString(); loai.TrangThai = int.Parse(dr[2].ToString()); lstloainhanvien.Add(loai); } return(lstloainhanvien); }
public List <LoaiNhanVienDTO> DanhSachLoaiNhanVien() { List <LoaiNhanVienDTO> lstlnv = new List <LoaiNhanVienDTO>(); string query = "SELECT * FROM LoaiNhanVien"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(query, conn); while (dr.Read()) { LoaiNhanVienDTO lnvdto = new LoaiNhanVienDTO(); lnvdto.MaLoaiNV = dr[0].ToString(); lnvdto.LoaiNV = dr[1].ToString(); lstlnv.Add(lnvdto); } dr.Close(); conn.Close(); return(lstlnv); }
public HoaDonNhapDTO LayHoaDonNhapTheoMa(string maHD) { HoaDonNhapDTO hoadonnhap = new HoaDonNhapDTO(); string caulenh = "SELECT MaHDN, nv.TenNV, NgayNhap, TongTien, hdn.TrangThai FROM HoaDonNhap hdn INNER JOIN NhanVien nv ON hdn.MaNV = nv.MaNV Where hdn.TrangThai = 1 AND hdn.MaHDN='" + maHD + "'"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { hoadonnhap.MaHDN = dr[0].ToString(); hoadonnhap.MaNV = dr[1].ToString(); hoadonnhap.NgayNhap = Convert.ToDateTime(dr[2].ToString()); hoadonnhap.TongTien = int.Parse(dr[3].ToString()); hoadonnhap.TrangThai = int.Parse(dr[4].ToString()); } dr.Close(); conn.Close(); return(hoadonnhap); }
public string TangHD() { string caulenh = "SELECT MAX(MaHDX) FROM HoaDonXuat"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); if (dr.Read()) { if (!dr.IsDBNull(0)) { int hd = Convert.ToInt32(dr.GetString(0).Remove(0, 3)); hd++; dr.Close(); conn.Close(); return("HDX" + hd.ToString("d4")); } } return("HDX0001"); }
public string TangNhaCungCap() { string caulenh = "SELECT MAX(MaNCC) FROM NhaCungCap"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); if (dr.Read()) { if (!dr.IsDBNull(0)) { int ncc = Convert.ToInt32(dr.GetString(0).Remove(0, 3)); ncc++; dr.Close(); conn.Close(); return("NCC" + ncc.ToString("d4")); } } return("NCC0001"); }
public string TangNhanVien() { string caulenh = "SELECT MAX(MaNV) FROM NhanVien"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); if (dr.Read()) { if (!dr.IsDBNull(0)) { int nv = Convert.ToInt32(dr.GetString(0).Remove(0, 2)); nv++; dr.Close(); conn.Close(); return("NV" + nv.ToString("d4")); } } return("NV0001"); }
public KhachHangDTO LayKHTheoMa(string MaKH) { KhachHangDTO khdto = new KhachHangDTO(); string caulenh = "SELECT *FROM KhachHang WHERE TrangThai=1 AND MaKH='" + MaKH + "'"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { khdto.MaKH = dr["MaKH"].ToString(); khdto.TenKH = dr["TenKH"].ToString(); khdto.DiaChi = dr["DiaChi"].ToString(); khdto.SDT = dr["SDT"].ToString(); khdto.TrangThai = int.Parse(dr["TrangThai"].ToString()); } dr.Close(); conn.Close(); return(khdto); }
public string TangKhachHang() { string caulenh = "SELECT MAX(MaKH) FROM KhachHang"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); if (dr.Read()) { if (!dr.IsDBNull(0)) { int kh = Convert.ToInt32(dr.GetString(0).Remove(0, 2)); kh++; dr.Close(); conn.Close(); return("KH" + kh.ToString("d4")); } } return("KH0001"); }
public HoaDonXuatDTO LayHoaDonTheoMa(string maHD) { HoaDonXuatDTO hoadon = new HoaDonXuatDTO(); string caulenh = "SELECT MaHDX, nv.TenNV, kh.TenKH, NgayXuat, TongTien, hdx.TrangThai FROM HoaDonXuat hdx INNER JOIN NhanVien nv ON hdx.MaNV=nv.MaNV INNER JOIN KhachHang kh ON hdx.MaKH=kh.MaKH WHERE hdx.TrangThai=1 AND hdx.MaHDX='" + maHD + "'"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { hoadon.MaHDX = dr[0].ToString(); hoadon.MaNV = dr[1].ToString(); hoadon.MaKH = dr[2].ToString(); hoadon.NgayXuat = Convert.ToDateTime(dr[3].ToString()); hoadon.TongTien = int.Parse(dr[4].ToString()); hoadon.TrangThai = int.Parse(dr[5].ToString()); } dr.Close(); conn.Close(); return(hoadon); }
public List <SanPhamDTO> DanhSachSanPhamHetHang() { List <SanPhamDTO> lstsanpham = new List <SanPhamDTO>(); string caulenh = "Select MaSP, TenSP, TenLoaiSP, SoLuong, sp.TrangThai FROM SanPham sp INNER JOIN LoaiSanPham lsp ON sp.MaLoaiSP = lsp.MaLoaiSP where sp.TrangThai = 1 AND SoLuong < 20"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { SanPhamDTO sanphamdto = new SanPhamDTO(); sanphamdto.MaSP = dr[0].ToString(); sanphamdto.TenSP = dr[1].ToString(); sanphamdto.MaLoaiSP = dr[2].ToString(); sanphamdto.SoLuong = int.Parse(dr[3].ToString()); sanphamdto.TrangThai = int.Parse(dr[4].ToString()); lstsanpham.Add(sanphamdto); } dr.Close(); conn.Close(); return(lstsanpham); }
public List <HoaDonNhapDTO> DSHoaDonNhap() { List <HoaDonNhapDTO> lsthoadonhap = new List <HoaDonNhapDTO>(); string caulenh = "SELECT MaHDN, nv.TenNV, NgayNhap, TongTien, hdn.TrangThai FROM HoaDonNhap hdn INNER JOIN NhanVien nv ON hdn.MaNV = nv.MaNV Where hdn.TrangThai = 1"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { HoaDonNhapDTO hdndto = new HoaDonNhapDTO(); hdndto.MaHDN = dr[0].ToString(); hdndto.MaNV = dr[1].ToString(); hdndto.NgayNhap = Convert.ToDateTime(dr[2].ToString()); hdndto.TongTien = int.Parse(dr[3].ToString()); hdndto.TrangThai = int.Parse(dr[4].ToString()); lsthoadonhap.Add(hdndto); } dr.Close(); conn.Close(); return(lsthoadonhap); }
public List <HoaDonNhapDTO> TimHoaDonNhap(DateTime tungay, DateTime denngay) { List <HoaDonNhapDTO> lsthoadonhap = new List <HoaDonNhapDTO>(); string caulenh = "SELECT * FROM HoaDonNhap Where TrangThai = 1 and NgayNhap >= convert (nvarchar,'" + tungay + "',103) and NgayNhap <= convert (nvarchar,'" + denngay + "',103)"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { HoaDonNhapDTO hdndto = new HoaDonNhapDTO(); hdndto.MaHDN = dr[0].ToString(); hdndto.MaNV = dr[1].ToString(); hdndto.NgayNhap = Convert.ToDateTime(dr[2].ToString()); hdndto.TongTien = int.Parse(dr[3].ToString()); hdndto.TrangThai = int.Parse(dr[4].ToString()); lsthoadonhap.Add(hdndto); } dr.Close(); conn.Close(); return(lsthoadonhap); }
public List <CTHDXuatDTO> DanhSachCTHDXuat() { List <CTHDXuatDTO> lstcthdx = new List <CTHDXuatDTO>(); string caulenh = "SELECT MaCTHoaDonXuat, sp.TenSP, GiaBan, cthdx.SoLuong, ThanhTien, cthdx.TrangThai FROM ChiTietHoaDonXuat cthdx INNER JOIN SanPham sp ON cthdx.MaSP=sp.MaSP WHERE cthdx.TrangThai=1"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { CTHDXuatDTO chitiet = new CTHDXuatDTO(); chitiet.MaCTHoaDonXuat = dr[0].ToString(); chitiet.MaSP = dr[1].ToString(); chitiet.GiaBan = int.Parse(dr[2].ToString()); chitiet.SoLuong = int.Parse(dr[3].ToString()); chitiet.ThanhTien = int.Parse(dr[4].ToString()); chitiet.TrangThai = int.Parse(dr[5].ToString()); lstcthdx.Add(chitiet); } dr.Close(); conn.Close(); return(lstcthdx); }
public bool TimMaSP(string MaSP) { string caulenh = "SELECT * FROM SanPham WHERE MaSP='" + MaSP + "'"; try { SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); if (dr.Read()) { return(true); } else { return(false); } } catch { return(false); } }
public List <SanPhamDTO> DanhSachSanPhamBanChay() { List <SanPhamDTO> lstsanpham = new List <SanPhamDTO>(); string caulenh = "SELECT TOP 10 cthd.MaSP, sp.TenSP, sp.GiaTien, sp.XuatXu, ncc.TenNCC , SUM( cthd.SoLuong ) as SoLuongDaBan FROM SanPham sp, NhaCungCap ncc, ChiTietHoaDonXuat cthd, HoaDonXuat hd WHERE hd.MaHDX = cthd.MaCTHoaDonXuat AND sp.MaSP = cthd.MaSP AND sp.MaNCC = ncc.MaNCC and sp.TrangThai = 1 GROUP BY cthd.MaSP, sp.TenSP, ncc.TenNCC, sp.GiaTien, sp.XuatXu ORDER BY SUM( cthd.SoLuong) desc"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { SanPhamDTO sanphamdto = new SanPhamDTO(); sanphamdto.MaSP = dr[0].ToString(); sanphamdto.TenSP = dr[1].ToString(); sanphamdto.GiaTien = Convert.ToDouble(dr[2].ToString()); sanphamdto.XuatXu = dr[3].ToString(); sanphamdto.MaNCC = dr[4].ToString(); sanphamdto.TrangThai = int.Parse(dr[5].ToString()); lstsanpham.Add(sanphamdto); } dr.Close(); conn.Close(); return(lstsanpham); }
public List <KhachHangDTO> DanhSachKhachHang() { List <KhachHangDTO> lstkh = new List <KhachHangDTO>(); string caulenh = "SELECT *FROM KhachHang WHERE TrangThai=1"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { KhachHangDTO khdto = new KhachHangDTO(); khdto.MaKH = dr["MaKH"].ToString(); khdto.TenKH = dr["TenKH"].ToString(); khdto.DiaChi = dr["DiaChi"].ToString(); khdto.SDT = dr["SDT"].ToString(); khdto.TrangThai = int.Parse(dr["TrangThai"].ToString()); lstkh.Add(khdto); } dr.Close(); conn.Close(); return(lstkh); }
public List <CTHDNhapDTO> DSCTHDNhap(string MaHDN) { List <CTHDNhapDTO> lscthdn = new List <CTHDNhapDTO>(); string caulenh = "SELECT MaCTHoaDonNhap, sp.TenSP, GiaNhap, cthdn.SoLuong, ThanhTien, cthdn.TrangThai FROM ChiTietHoaDonNhap cthdn INNER JOIN SanPham sp ON cthdn.MaSP = sp.MaSP WHERE MaCTHoaDonNhap='" + MaHDN + "' AND cthdn.TrangThai=1"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { CTHDNhapDTO chitiet = new CTHDNhapDTO(); chitiet.MaCTHoaDonNhap = dr[0].ToString(); chitiet.MaSP = dr[1].ToString(); chitiet.GiaNhap = int.Parse(dr[2].ToString()); chitiet.SoLuong = int.Parse(dr[3].ToString()); chitiet.ThanhTien = int.Parse(dr[4].ToString()); chitiet.TrangThai = int.Parse(dr[5].ToString()); lscthdn.Add(chitiet); } dr.Close(); conn.Close(); return(lscthdn); }
public List <HoaDonXuatDTO> TimDanhSachHoaDon(DateTime tungay, DateTime denngay) { List <HoaDonXuatDTO> lsthoadonxuat = new List <HoaDonXuatDTO>(); string caulenh = "SELECT MaHDX, nv.TenNV, kh.TenKH, NgayXuat, TongTien, hdx.TrangThai FROM HoaDonXuat hdx INNER JOIN NhanVien nv ON hdx.MaNV=nv.MaNV INNER JOIN KhachHang kh ON hdx.MaKH=kh.MaKH WHERE hdx.TrangThai=1 and NgayXuat >= convert (nvarchar,'" + tungay + "',103) and NgayXuat <= convert (nvarchar,'" + denngay + "',103)"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { HoaDonXuatDTO hoadon = new HoaDonXuatDTO(); hoadon.MaHDX = dr[0].ToString(); hoadon.MaNV = dr[1].ToString(); hoadon.MaKH = dr[2].ToString(); hoadon.NgayXuat = DateTime.Parse(dr[3].ToString()); hoadon.TongTien = int.Parse(dr[4].ToString()); hoadon.TrangThai = int.Parse(dr[5].ToString()); lsthoadonxuat.Add(hoadon); } dr.Close(); conn.Close(); return(lsthoadonxuat); }
public static List <NhaCungCapDTO> DanhSachNhaCungCap() { List <NhaCungCapDTO> lstncc = new List <NhaCungCapDTO>(); string caulenh = "SELECT * FROM NhaCungCap WHERE TrangThai=1"; SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi(); SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn); while (dr.Read()) { NhaCungCapDTO ncc = new NhaCungCapDTO(); ncc.MaNCC = dr["MaNCC"].ToString(); ncc.TenNCC = dr["TenNCC"].ToString(); ncc.DiaChi = dr["DiaChi"].ToString(); ncc.SDT = dr["SDT"].ToString(); ncc.Email = dr["Email"].ToString(); ncc.TrangThai = int.Parse(dr["TrangThai"].ToString()); lstncc.Add(ncc); } dr.Close(); conn.Close(); return(lstncc); }