Đồ án Quản lý mua bán Laptop

m, CD/DVD, Screen, Pin, HDD,… -Loại hàng, hãng sản xuất, thông tin bảo hành, thông tin nhà cung cấp, xuất xứ, giá hàng hóa,… [2] Thông tin khách hàng: - Thông tin giao dịch - Thông tin cá nhân [3] Thông tin nhà cung cấp: - Thông tin giao dịch - Thông tin cá nhân [4] Chi tiết giao dịch - Hóa đơn và chi tiết hóa đơn nhập cũng như xuất cần co các thông tin như, số lượng hàng hóa giao dịch, giá hàng giao dịch,… [5] Thông tin kho hàng -Số lượng tồn và giá tồn các mặt hàng [6] Thông tin hỗ trợ ra quyết định -Doanh thu, lợi nhuận -Các mặt hàng bán nhiều nhất, ít nhất,… 2. Xây dựng CSDL lưu trữ: 2.1 Giải quyết yêu cầu lưu trữ: Sử dụng SQL server 2005: với các bảng như sau: Chi tiết các bảng: [1] Chi tiết hóa đơn bán hàng: [2] Chi tiết hóa đơn mua hàng: [3] Hàng hóa [4] Hóa đơn bán hàng [5] Hóa đơn mua hàng [6] Quản lý khách hàng [7] Loại hàng [8] Nhà cung cấp [9] Nhan vien [10] Trung tâm bảo hành Tổng kết các bảng: LoaiHang (MaLH, TenLH, MaNCC, MaTTBH) HangHoa (MaHH, TenHang, MaLH, Chip, Main, HDD, RAM, VGA, CDRW, SLTon, Gia, GiaBan) KhachHang (MaKH, TenKH, DiaChi, DienThoai) NhaCungCap (MaNCC, TenNCC, DiaChi, DienThoai) TTBH (MaTTBH, DiaChi, DienThoai) HoaDonBan (MaHD, MaKH, TongGiaTri, NgayLap) ChiTietHDBan (MaHD, MaHH, SLBan, GiaBan, MaNV) HoaDonMua (MaHD, MaNCC, TongGiaTri, NgayLap) ChiTietHDMua (MaHD, MaHH, SLMua, GiaMua) NhanVienBH (MaNV, TenNV, DiaChi, DienThoai) Sơ đồ thực thể và mối kết hợp: 2.2 Ràng buộc toàn vẹn 2.2.1 Ràng buộc Khóa chính NhaCungCap: t1,t2 NhaCungCap, t1t2 t1.MaNCCt2.MaNCC R Thêm Xóa Sửa NhaCungCap + - +(MaNCC) TTBaoHanh: t1,t2 TTBaoHanh, t1t2 t1. MaTTBH t2. MaTTBH R Thêm Xóa Sửa TTBaoHanh + - +( MaTTBH) LoaiHang: t1,t2 TTBaoHanh, t1t2 t1. MaLoai t2. MaLoai R Thêm Xóa Sửa LoaiHang + - +( MaLoai) HangHoa: t1,t2 HangHoa, t1t2 t1. MaHH t2. MaHH R Thêm Xóa Sửa HangHoa + - +( MaHH) HoaDonMua: t1,t2 HoaDonMua, t1t2 t1. MaHD t2. MaHD R Thêm Xóa Sửa HoaDonMua + - +( MaHD) HoaDonBan: t1,t2 HoaDonBan, t1t2 t1. MaHD t2. MaHD R Thêm Xóa Sửa HoaDonBan + - +( MaHD) NhanVien: t1,t2 NhanVien, t1t2 t1. MaNV t2. MaNV R Thêm Xóa Sửa NhanVien + - +( MaNV) KhachHang: t1,t2 KhachHang, t1t2 t1. MaKH t2. MaKH R Thêm Xóa Sửa KhachHang + - +( MaKH) 2.2.2 Ràng buộc Khóa ngoại NhaCungCap – LoaiHang t LoaiHang, !t’NhaCungCap, t’.NhaCungCap = t.LoaiHang R Thêm Xóa Sửa LoaiHang + - +(MaNCC) NhaCungCap - + TTBaoHanh – LoaiHang t LoaiHang, !t’ TTBaoHanh, t’. TTBaoHanh = t.LoaiHang R Thêm Xóa Sửa LoaiHang + - +(MaTTBH) TTBaoHanh - + LoaiHang - HangHoa t HangHoa, !t’ LoaiHang, t’. LoaiHang = t. HangHoa R Thêm Xóa Sửa HangHoa + - +(MaLoai) LoaiHang - + HangHoa - ChiTietHDMua t ChiTietHDMua, !t’ HangHoa, t’. HangHoa = t. ChiTietHDMua R Thêm Xóa Sửa ChiTietHDMua + - +(MaHH) HangHoa - + HangHoa - ChiTietHDBan t ChiTietHDBan, !t’ HangHoa, t’. HangHoa = t. ChiTietHDBan R Thêm Xóa Sửa ChiTietHDBan + - +(MaHH) HangHoa - + NhanVien - ChiTietHDBan t ChiTietHDBan, !t’ NhanVien, t’. NhanVien = t. ChiTietHDBan R Thêm Xóa Sửa ChiTietHDBan + - +(MaNV) NhanVien - + HoaDonMua - ChiTietHDMua t ChiTietHDMua, !t’ HoaDonMua, t’. HoaDonMua = t. ChiTietHDMua R Thêm Xóa Sửa ChiTietHDMua + - +(MaHD) HoaDonMua - + HoaDonBan - ChiTietHDBan t ChiTietHDBan, !t’ HoaDonBan, t’. HoaDonBan = t. ChiTietHDBan R Thêm Xóa Sửa ChiTietHDBan + - +(MaHD) HoaDonBan - + KhachHang - HoaDonBan t HoaDonBan, !t’ KhachHang, t’. KhachHang = t. HoaDonBan R Thêm Xóa Sửa HoaDonBan + - +(MaKH) KhachHang - + 2.2.3 Ràng buộc khác: Các ràng buộc này có thể được xử lý bằng Trigger hoặc C#, trong đồ án này sẽ hướng xử lý bằng C# ADO.NET với sqlCommand.ExecuteNonQuery( ) (1) HangHoa – ChiTietHDMua - ChiTietHDBan t HangHoa, t1 ChiTietHDBan, t2 ChiTietHDMua, t.SLTon =Sum( t2.SLMua) –Sum( t1.SLBan) ^ t.MaHH=t1.MaHH=t2.MaHH R Thêm Xóa Sửa ChiTietHDMua + + +(SLTon) ChiTietHDBan + + +(SLTon) Cú pháp Update sau đây sẽ dùng gán cho một sự kiện nào đó khi thiết lập trên Demo C#: Lưu ý đây không phải là Trigger chỉ là một Command Text. UPDATE HangHoa SET SLTon = IsNull ((SELECT sum(SLMua) FROM ChiTietHDMua WHERE ChiTietHDMua.MaHH = HangHoa.MaHH) - (SELECT sum(SLBan) FROM ChiTietHDBan WHERE ChiTietHDBan.MaHH = HangHoa.MaHH), (SELECT sum(SLMua) FROM ChiTietHDMua WHERE ChiTietHDMua.MaHH = HangHoa.MaHH)) Dùng chung cho cả 2 sự kiện ở 2 bảng (2) ChiTietHD (Mua/Ban) – HoaDon (Mua/Ban) t HoaDon, t’ChiTietHD, t.TongGiaTri=Sum(t’.SL*t’.Gia) R Thêm Xóa Sửa ChiTietHD + + +(TongGiaTri) Cú pháp Update sau đây sẽ dùng gán cho một sự kiện nào đó khi thiết lập trên Demo C#: UPDATE HoaDonMua SET TongGiaTri = (SELECT SUM(SLMua * GiaMua) FROM ChiTietHDMua WHERE (MaHD = HoaDonMua.MaHD)) UPDATE HoaDonBan SET TongGiaTri = (SELECT SUM(SLBan * GiaBan) FROM ChiTietHDBan WHERE (MaHD = HoaDonBan.MaHD)) 2.3 Phụ thuộc hàm (1) NhaCungCap = {MaNCC, TenNCC, DiaChi, DienThoai} F = { MaNCC à TenNCC MaNCC à DiaChi MaNCC à DienThoai } (2) TTBaoHanh = {MaTTBH, DiaChi, DienThoai} F = { MaTTBH à DiaChi MaTTBH à DienThoai } (3) LoaiHang = {MaLoai, MaNCC, MaTTBH} F = { MaLoai à MaNCC MaLoai à MaTTBH } (4) HangHoa = {MaHH, TenHang, MaLH, Chip, Screen, Ram, HDD, VGA, CDRW, SLTon, Gia, GiaBan} F = { MaHH à TenHang MaHH à MaLH MaHH à Chip MaHH à Screen MaHH à Ram MaHH à HDD MaHH à VGA MaHH à CDRW MaHH à SLTon MaHH à Gia MaHH à GiaBan } (5) ChiTietHDMua{MaHD, MaHH, SLMua, GiaMua} F = { MaHD, MaHH à SLMua, MaHD, MaHH à GiaMua } (6) ChiTietHDBan{MaHD, MaHH, SLBan, GiaBan, MaNV} F = { MaHD, MaHH à SLBan MaHD, MaHH à GiaBan MaHD, MaHH à MaNV } (7) NhanVien{MaNV, TenNV, DiaChi, DienThoai} F = { MaNV à TenNV MaNV à DiaChi MaNV à DienThoai } (8) HoaDonMua{MaHD, MaNCC, TongGiaTri, NgayLap} F = { MaHD à MaNCC MaHD à TongGiaTri MaHD à NgayLap } (9) HoaDonBan{MaHD, MaNCC, TongGiaTri, NgayLap} F = { MaHD à MaKH MaHD à TongGiaTri MaHD à NgayLap } (10) KhachHang{MaKH, TenKH, DiaChi, DienThoai} F = { MaKH à TenNV MaKH à TenKH MaKH à DiaChi MaKH à DienThoai } 2.4 Chuẩn hóa CSDL 2.4.1 Xét bảng NhanVien (MaNV, TenNV, DiaChi, DienThoai} - Một NV có thể có nhiều số điện thoại hoặc nhiều nhiều địa chỉ liên hệ, như vậy mô hình không thể đạt chuẩn BCNF. Lúc đó ta phải tách bảng NhanVien thành 3 bảng. NhanVien{MaNV, TenNV}, NV-DC{MaNV, DiaChi} NV-DT{MaNV, DienThoai} lúc này thì quan hệ NhanVien đã được chuẩn hóa với chuẩn Boyce-Codd. Nhưng chúng ta sẽ đạt kèm 1 số quy định như: + Các nhân viên chỉ được sử dụng 1 số điện thoại và 1 địa chỉ liên hệ trong quá trình làm việc, tuy nhiên quy định này cũng có vẻ khắc khe. 2.4.2 Tương tự với bảng KhachHang{MaKH, TenKH, DiaChi, DienThoai} Chúng ta vẫn nói trên phương diện như với bảng NhanVien, thì mô hình vẫn chưa thể đạt chuẩn BCNF. Và ta vẫn phải tiến hành thao tác như bảng NhanVien. Bằng cách tách bảng: KhachHang{MaKH, TenKH}, KH-DC{MaKH,DiaChi}, KH-DT{MaKH,DT} +Tuy nhiên bằng một số quy định như trên ta cũng có thể bỏ qua việc chuẩn hóa này mà mô hình vẫn có thể đạt chuẩn BCNF. 2.4.3 Với cấu trúc tương tự như KhachHang ta phải chuẩn hóa tiếp bảng NhaCungCap như sau NhaCungCap{MaNCC, TenNCC}, NCC-DC{MaNCC,DiaChi}, NCC-DT{MaNCC,DT} 2.4.4 Và không chỉ dừng lại ở những bảng trên, ở tất cả các bảng, các phụ thuộc hàm của một tập hợp hàm với phản ánh như sau: X à A trong đó X là khóa (siêu khóa) nhưng giá trị A lại không phải là duy nhất đối với khóa này thì mô hình sẽ không đạt chuẩn và tại phải làm thao tác chuẩn hóa CSDL à BCNF. Kết luận: Nếu như không có các điều kiện ràng buộc thì mô hình CSDL của chúng ta sẽ làm việc chưa logic về tính thực tế. Tuy nhiên mô hình của chúng ta sẽ rất tốt và có thể nói là đạt chuẩn 4 hay BCNF với các điều kiện ràng buộc như sau: - Nhân viên, khách hàng, nhà cung cấp, … chỉ dùng duy nhất 1 số điện thoại liên lạc và 1 địa chỉ liên hệ. - Vấn đề thứ 2 là chúng ta vẫn sử dụng CSDL như ban đầu, với những điều kiện ràng buộc như trên và nó đạt chuẩn BCNF. 2.5 Truy vấn SQL 2.5.1 Các yêu cầu từ nghiệp vụ bán hàng: (1.1) Liệt kê danh mục hàng và các thông tin về hàng (giống như khách hàng xem bảng báo giá) SELECT * FROM HangHoa (1.2) Xem thông tin hàng hóa có mã là x hoặc tên hàng là y Mã hàng là x SELECT * FROM HangHoa WHERE MaHH=@X Tên hàng là y SELECT * FROM HangHoa WHERE (TenHang LIKE '%' + @Y + '%') Mã và tên các laptop có cấu hình: Chip=x Hoặc Sreen=y Hoặc Ram=z Ta giải trường hợp Chip=x=2Ghz SELECT MaHH, TenHang, MaLH, Chip FROM HangHoa WHERE (Chip LIKE '%' + @X + '%') Trường hợp khách hàng có yêu cầu kiểm tra kỹ thuật máy ta cần kiểm tra xem có phải đó là hàng đã được công ty bán ra hay không: (1.4.1) Kiểm tra tất cả các mục hàng mà khách hàng có tên là x đã mua. VD:x=”Bùi Thế Duy” SELECT HDB.MaKH, KH.TenKH, CTB.MaHH, CTB.SLBan,CTB.NgayLap FROM ChiTietHDBan as CTB INNER JOIN HoaDonBan as HDB ON CTB.MaHD = HDB.MaHD INNER JOIN KhachHang as KH ON HDB.MaKH = KH.MaKH WHERE (KH.TenKH = @X) (1.4.2) Kiểm tra thông tin khách hàng để liên hệ khi cần. VD: Cần kiểm tra thông tin Khách hàng Bùi Thế Duy SELECT * FROM KhachHang WHERE TenKH=@X Giải quyết các vấn đề sau: 1.5.1 Danh sách hàng bán ra trong ngày x 1.5.2 Danh sách hàng bán ra trong tháng x 1.5.3 Danh sách hàng bán ra trong năm x Ta sẽ giải quyết cả 3 câu trên bằng 1 truy vấn duy nhất: Danh sách hàng bán ra từ ngày x đến ngày y. VD: 1.5.1 từ ngày 15/3/2009 -15/3/2009 1.5.2 từ ngày 15/2/2009-15/3/2009 1.5.3 từ ngày 15/3/2008-15/3/2009 Ta sẽ chạy câu truy vấn tổng quát, và chạy VD: danh sách mặt hàng bán ra trong tháng 3. SELECT dbo.ChiTietHDBan.MaHH, dbo.HangHoa.TenHang, dbo.ChiTietHDBan.SLBan, dbo.HoaDonBan.NgayLap FROM dbo.ChiTietHDBan INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH INNER JOIN dbo.HoaDonBan ON dbo.ChiTietHDBan.MaHD = dbo.HoaDonBan.MaHD WHERE (dbo.HoaDonBan.NgayLap BETWEEN @X AND @Y) Lập danh sách tất cả mặt hàng của loại hàng x. Vd: x là COMPAQ – HP SELECT dbo.HangHoa.TenHang, dbo.LoaiHang.TenLH FROM dbo.HangHoa INNER JOIN dbo.LoaiHang ON dbo.HangHoa.MaLH = dbo.LoaiHang.MaLoai WHERE (dbo.LoaiHang.TenLH = @X) (1.7) Danh sách các hàng hóa mua trong hóa đơn có mã là x. VD:x=7 SELECT dbo.ChiTietHDBan.MaHD, dbo.ChiTietHDBan.MaHH, dbo.ChiTietHDBan.GiaBan, dbo.ChiTietHDBan.SLBan FROM dbo.ChiTietHDBan INNER JOIN dbo.HoaDonBan ON dbo.ChiTietHDBan.MaHD = dbo.HoaDonBan.MaHD WHERE (dbo.ChiTietHDBan.MaHD = @X) Tổng doanh thu trong 1 thời từ nào đó từ thời gian xày Phần này ta có thể giải quyết được các bài toán sau, doanh thu trong ngày, trong tháng, trong quý, trong năm, và trong một thời kỳ. VD: Ta tính doanh thu trong tháng 3. SELECT SUM(TongGiaTri) AS DoanhThu FROM dbo.HoaDonBan WHERE (NgayLap BETWEEN @X AND @Y) (Doanh thu tính bằng USD, đừng thắc mắc tại sao nó bé thế! Vì dữ liệu đầu vào chưa nhiều) Tương tự ta giải quyết bài toán Lợi nhuận như Doanh thu trên Ta giải Lợi nhuận trong tháng 3 (nhưng trong CSDL vì dữ liệu quá ít nên chắc chỉ đc có 10 ngày) SELECT SUM(dbo.ChiTietHDBan.SLBan * (dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia)) AS LoiNhuan FROM dbo.ChiTietHDBan INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH INNER JOIN dbo.HoaDonBan ON dbo.ChiTietHDBan.MaHD = dbo.HoaDonBan.MaHD WHERE (dbo.HoaDonBan.NgayLap BETWEEN @X AND @Y) Thống kê mặt hàng bán ra từ trong thời kỳ từ thời gian x cho đến thời gian y, VD: trong tháng 3. SELECT dbo.ChiTietHDBan.MaHH, dbo.HangHoa.TenHang FROM dbo.ChiTietHDBan INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH INNER JOIN dbo.HoaDonBan ON dbo.ChiTietHDBan.MaHD = dbo.HoaDonBan.MaHD WHERE (dbo.HoaDonBan.NgayLap BETWEEN @X AND @Y) (1.10.1)Các mặt hàng bán nhiều nhất trong thời kỳ từ thời gian x đến thời gian y. Vd: trong tháng 3. SELECT dbo.ChiTietHDBan.MaHH, dbo.ChiTietHDBan.SLBan FROM dbo.ChiTietHDBan INNER JOIN dbo.HoaDonBan ON dbo.ChiTietHDBan.MaHD = dbo.HoaDonBan.MaHD WHERE (dbo.HoaDonBan.NgayLap BETWEEN @X AND @Y) AND (dbo.ChiTietHDBan.SLBan >= ALL (SELECT SLBan FROM dbo.ChiTietHDBan)) (1.10.2)Các mặt hàng bán ít nhất trong thời kỳ từ thời gian x đến thời gian y. Vd: trong tháng 3. SELECT dbo.ChiTietHDBan.MaHH, dbo.ChiTietHDBan.SLBan FROM dbo.ChiTietHDBan INNER JOIN dbo.HoaDonBan ON dbo.ChiTietHDBan.MaHD = dbo.HoaDonBan.MaHD WHERE (dbo.HoaDonBan.NgayLap BETWEEN @X AND @Y) AND (dbo.ChiTietHDBan.SLBan <= ALL (SELECT SLBan FROM dbo.ChiTietHDBan)) (1.10.3) Tình hình tiêu thụ hàng trong các tháng trong năm x. Cái này hơi giống với WalMart của Thầy nên e không làm, mà muốn làm cũng không đc vì hệ thống ảo (CSDL vừa tạo) này chỉ có thể hoạt động trong tháng 3/2009 ^^ Tuy nhiên ta có thể giải quyết bài toán này với 4 mức ý nghĩa: Thống kê doanh thu, lợi nhuận, xuất, nhập trên từng mặt hàng. Tất cả được giải quyết đầy đủ trong chương trình QLBanHangLT, ở đây chỉ đưa ra một Ví dụ là thống kê Lợi nhuận: SELECT MaHH, TenHang, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 1)), 0) AS Thang1, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 2)), 0) AS Thang2, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 3)), 0) AS Thang3, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 4)), 0) AS Thang4, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 5)), 0) AS Thang5, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 6)), 0) AS Thang6, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 7)), 0) AS Thang7, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 8)), 0) AS Thang8, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 9)), 0) AS Thang9, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 10)), 0) AS Thang10, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 11)), 0) AS Thang11, ISNULL ((SELECT SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS Expr1 FROM dbo.HoaDonBan INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH AND dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH WHERE (YEAR(dbo.HoaDonBan.NgayLap) = 2009) AND (dbo.ChiTietHDBan.MaHH = HangHoa_1.MaHH) AND (MONTH(dbo.HoaDonBan.NgayLap) = 12)), 0) AS Thang12 FROM dbo.HangHoa AS HangHoa_1 (1.11) Tổng giá trị mà các nhân viên bán hàng đã bán được SELECT dbo.ChiTietHDBan.MaNV, dbo.NhanVien.TenNV, SUM(dbo.ChiTietHDBan.SLBan * dbo.ChiTietHDBan.GiaBan) AS TGT FROM dbo.ChiTietHDBan INNER JOIN dbo.NhanVien ON dbo.ChiTietHDBan.MaNV = dbo.NhanVien.MaNV GROUP BY dbo.ChiTietHDBan.MaNV, dbo.NhanVien.TenNV 2.5.1 Các yêu cầu từ nghiệp vụ nhập hàng: (2.1.1) Xem danh mục hàng đã từng nhập SELECT ChiTietHDMua.MaHH, HangHoa.TenHang FROM ChiTietHDMua INNER JOIN HangHoa ON ChiTietHDMua.MaHH = dbo.HangHoa.MaHH (2.1.2) Thông tin Hàng nhập về trong một khoảng thời gian, từ ngày x à ngày y. Có thể dùng để xem trong tháng, năm, … VD: 1/3/2009 à 20/03/2009 SELECT ChiTietHDMua.MaHH, HangHoa.TenHang, ChiTietHDMua.GiaMua, ChiTietHDMua.SLMua, HoaDonMua.NgayLap FROM ChiTietHDMua INNER JOIN HangHoa ON ChiTietHDMua.MaHH = HangHoa.MaHH INNER JOIN HoaDonMua ON ChiTietHDMua.MaHD = HoaDonMua.MaHD WHERE (HoaDonMua.NgayLap BETWEEN @X AND @Y) (2.2) Thống kê các mục hàng giảm dần theo số lượng nhập SELECT TOP (100) PERCENT dbo.ChiTietHDMua.MaHH, dbo.HangHoa.TenHang, dbo.ChiTietHDMua.SLMua, dbo.HoaDonMua.TongGiaTri, dbo.HoaDonMua.NgayLap FROM dbo.ChiTietHDMua INNER JOIN dbo.HangHoa ON dbo.ChiTietHDMua.MaHH = dbo.HangHoa.MaHH INNER JOIN dbo.HoaDonMua ON dbo.ChiTietHDMua.MaHD = dbo.HoaDonMua.MaHD ORDER BY dbo.ChiTietHDMua.SLMua (2.3) Tổng số tiền mà doanh nghiệp đã đầu tư mua hàng trong một thời gian từ ngày x à ngày y. VD: trong tháng 3/2009 SELECT Sum(TongGiaTri) as TGiaTri FROM HoaDonMua WHERE NgayLap between @X and @Y (2.4) Lập danh sách các mặt hàng tồn kho lâu hơn một khoảng thời gian nào đó. VD: Tồn kho lâu hơn 1 tháng. SELECT dbo.HangHoa.MaHH, dbo.HangHoa.TenHang, dbo.HangHoa.SLTon FROM dbo.HangHoa INNER JOIN dbo.ChiTietHDMua ON dbo.HangHoa.MaHH = dbo.ChiTietHDMua.MaHH INNER JOIN dbo.HoaDonMua ON dbo.ChiTietHDMua.MaHD = dbo.HoaDonMua.MaHD WHERE (dbo.HangHoa.SLTon > 1) AND (dbo.HoaDonMua.NgayLap < { fn NOW() } - 30) 2.5.3 Các yêu cầu từ nghiệp vụ quản lý hóa đơn, khách hàng và nhà cung cấp: (3.1) Thông tin Khách hàng và hàng hóa có mã hóa đơn là 4 SELECT dbo.KhachHang.MaKH, dbo.ChiTietHDBan.MaHH, dbo.ChiTietHDBan.SLBan AS SoLuong, dbo.ChiTietHDBan.GiaBan * dbo.ChiTietHDBan.SLBan AS TGT, dbo.HoaDonBan.NgayLap FROM dbo.KhachHang INNER JOIN dbo.HoaDonBan ON dbo.KhachHang.MaKH = dbo.HoaDonBan.MaKH INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD WHERE (dbo.HoaDonBan.MaHD = 4) (3.2) Doanh thu và lợi nhuận các khách hàng mang đến cho công ty SELECT dbo.KhachHang.MaKH, dbo.KhachHang.TenKH, SUM(dbo.ChiTietHDBan.GiaBan * dbo.ChiTietHDBan.SLBan) AS DoanhThu, SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS LoiNhuan FROM dbo.KhachHang INNER JOIN dbo.HoaDonBan ON dbo.KhachHang.MaKH = dbo.HoaDonBan.MaKH INNER JOIN dbo.ChiTietHDBan ON dbo.HoaDonBan.MaHD = dbo.ChiTietHDBan.MaHD INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH GROUP BY dbo.KhachHang.MaKH, dbo.KhachHang.TenKH (3.3) Thống kê giao dịch với các nhà cung cấp SELECT dbo.HoaDonMua.MaNCC, dbo.NhaCungCap.TenNCC, SUM(dbo.ChiTietHDMua.SLMua * dbo.ChiTietHDMua.GiaMua) AS GiaoDich FROM dbo.ChiTietHDMua INNER JOIN dbo.HoaDonMua ON dbo.ChiTietHDMua.MaHD = dbo.HoaDonMua.MaHD INNER JOIN dbo.NhaCungCap ON dbo.HoaDonMua.MaNCC = dbo.NhaCungCap.MaNCC GROUP BY dbo.HoaDonMua.MaNCC, dbo.NhaCungCap.TenNCC (3.4) Lập danh sách tất cả các trung tâm bảo hành SELECT * FROM TTBaoHanh (3.5) Lập danh sách các mặt hàng cần nhập thêm do số lượng còn ít. Ví dụ là số lượng tồn kho nhỏ hơn hoặc bằng 8. SELECT MaHH, TenHang, SLTon FROM HangHoa WHERE SLTon <= 8 2.5.4 Các nhận định và các yêu cầu đặt ra trong tương lai (vì lý do thời gian hoạt động của công ty chưa lâu): (4.1.1) Thống kê doanh thu và lợi nhuận từ các mặt hàng, chọn top 5 mặt hàng đã đem lại lợi nhuận nhiều nhất cho công ty SELECT TOP (5) dbo.ChiTietHDBan.MaHH, dbo.HangHoa.TenHang, SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS LoiNhuan FROM dbo.ChiTietHDBan INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH INNER JOIN dbo.HoaDonBan ON dbo.ChiTietHDBan.MaHD = dbo.HoaDonBan.MaHD GROUP BY dbo.ChiTietHDBan.MaHH, dbo.HangHoa.TenHang ORDER BY LoiNhuan DESC (4.1.2) Thống kê doanh thu và lợi nhuận từ các mặt hàng, chọn top 5 mặt hàng đã đem lại lợi nhuận thấp nhất cho công ty SELECT TOP (5) dbo.ChiTietHDBan.MaHH, dbo.HangHoa.TenHang, SUM((dbo.ChiTietHDBan.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDBan.SLBan) AS LoiNhuan FROM dbo.ChiTietHDBan INNER JOIN dbo.HangHoa ON dbo.ChiTietHDBan.MaHH = dbo.HangHoa.MaHH INNER JOIN dbo.HoaDonBan ON dbo.ChiTietHDBan.MaHD = dbo.HoaDonBan.MaHD GROUP BY dbo.ChiTietHDBan.MaHH, dbo.HangHoa.TenHang ORDER BY LoiNhuan (4.2) Thống kê top 3 đối tác chiến lược cung cấp mặt hàng đem lại lợi nhuận cao nhất SELECT TOP (3) dbo.HoaDonMua.MaNCC, dbo.NhaCungCap.TenNCC, SUM((dbo.HangHoa.GiaBan - dbo.HangHoa.Gia) * dbo.ChiTietHDMua.SLMua) AS LoiNhuan FROM dbo.ChiTietHDMua INNER JOIN dbo.HangHoa ON dbo.ChiTietHDMua.MaHH = dbo.HangHoa.MaHH INNER JOIN dbo.HoaDonMua ON dbo.ChiTietHDMua.MaHD = dbo.HoaDonMua.MaHD INNER JOIN dbo.NhaCungCap ON dbo.HoaDonMua.MaNCC = dbo.NhaCungCap.MaNCC GROUP BY dbo.HoaDonMua.MaNCC, dbo.NhaCungCap.TenNCC ORDER BY LoiNhuan DESC (4.3) Doanh thu và lợi nhuận trung bình mỗi năm (4.4) Kiểm định các dòng máy phổ thông sẽ được nhiều người ưa chuộng, các dòng máy cao cấp sẽ ít người mua hơn. (4.5) Các khách hàng thường tập trung trong khu vực thành phố HCM và lân cận, khảo sát tính chính xác của nhận định. (…) Và các câu hỏi khác cần c