public static bool ThemTaiKhoan(TaiKhoanDTO taiKhoan) { SqlConnection connection = DataProvider.GetConnection(); string query = "INSERT INTO TaiKhoan (ho_ten, mat_khau, ngay_bat_dau, loai_tai_khoan, hinh, trang_thai) VALUES (@hoTen, @matKhau, @ngayBatDau, @loaiTaiKhoan, @hinh, @trangThai)"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@hoTen", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.HoTen; command.Parameters.Add("@matKhau", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.MatKhau; command.Parameters.Add("@ngayBatDau", System.Data.SqlDbType.DateTime, 0).Value = taiKhoan.NgayBatDau; command.Parameters.Add("@loaiTaiKhoan", System.Data.SqlDbType.Int, 0).Value = taiKhoan.LoaiTaiKhoan; command.Parameters.Add("@hinh", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.Hinh; command.Parameters.Add("@TrangThai", System.Data.SqlDbType.Bit, 0).Value = taiKhoan.TrangThai; connection.Open(); int reader = command.ExecuteNonQuery(); connection.Close(); if (reader == 1) { return(true); } return(false); }
public static bool SuaTaiKhoan(TaiKhoanDTO taiKhoan) { SqlConnection connection = DataProvider.GetConnection(); string query = "UPDATE TaiKhoan SET ho_ten=@hoTen, mat_khau=@matKhau, loai_tai_khoan=@loaiTaiKhoan, hinh=@hinh, trang_thai=@trangThai WHERE ma_tai_khoan=@maTaiKhoan"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@maTaiKhoan", System.Data.SqlDbType.Int, 0).Value = taiKhoan.MaTaiKhoan; command.Parameters.Add("@hoTen", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.HoTen; command.Parameters.Add("@matKhau", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.MatKhau; command.Parameters.Add("@loaiTaiKhoan", System.Data.SqlDbType.Int, 0).Value = taiKhoan.LoaiTaiKhoan; command.Parameters.Add("@hinh", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.Hinh; command.Parameters.Add("@trangThai", System.Data.SqlDbType.Bit, 0).Value = taiKhoan.TrangThai; connection.Open(); int reader = command.ExecuteNonQuery(); connection.Close(); if (reader == 1) { return(true); } return(false); }
public static TaiKhoanDTO LayThongTinTaiKhoan(int maTaiKhoan) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ho_ten, mat_khau, ngay_bat_dau, loai_tai_khoan, hinh, trang_thai FROM TaiKhoan WHERE ma_tai_khoan=@maTaiKhoan"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@maTaiKhoan", SqlDbType.Int, 0).Value = maTaiKhoan; connection.Open(); SqlDataReader reader = command.ExecuteReader(); TaiKhoanDTO result = new TaiKhoanDTO(); if (reader.HasRows) { while (reader.Read()) { result.HoTen = reader.GetString(0); result.MatKhau = reader.GetString(1); result.NgayBatDau = reader.GetDateTime(2); result.LoaiTaiKhoan = reader.GetInt32(3); if (!reader.IsDBNull(4)) { result.Hinh = reader.GetString(4); } result.TrangThai = reader.GetBoolean(5); } } connection.Close(); return(result); }
public static List <CTPhieuNhapDTO> LayDanhSachCTPhieuNhap(int maPhieuNhap) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ma_phieu_nhap, ma_nguyen_lieu, so_luong, don_gia, don_vi_tinh, ghi_chu FROM CTPhieuNhap WHERE ma_phieu_nhap=@maPhieuNhap"; SqlCommand command = new SqlCommand(); command.Parameters.Add("@maPhieuNhap", System.Data.SqlDbType.NVarChar, 255).Value = maPhieuNhap; command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <CTPhieuNhapDTO> result = new List <CTPhieuNhapDTO>(); if (reader.HasRows) { while (reader.Read()) { CTPhieuNhapDTO ctpn = new CTPhieuNhapDTO(); ctpn.MaPhieuNhap = reader.GetInt32(0); ctpn.MaNguyenLieu = reader.GetInt32(1); ctpn.SoLuong = reader.GetDouble(2); ctpn.DonGia = reader.GetDouble(3); ctpn.DonViTinh = reader.GetString(4); ctpn.GhiChu = reader.GetString(5); result.Add(ctpn); } } connection.Close(); return(result); }
public static List <ChucNang_LoaiTaiKhoanDTO> LayDanhSachChucNang_LoaiTaiKhoanTheoMaTaiKhoan(int maTaiKhoan) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ma_chuc_nang, ChucNang_LoaiTaiKhoan.ma_loai_tai_khoan FROM ChucNang_LoaiTaiKhoan, TaiKhoan WHERE TaiKhoan.ma_tai_khoan=@maTaiKhoan AND TaiKhoan.loai_tai_khoan=ChucNang_LoaiTaiKhoan.ma_loai_tai_khoan"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@maTaiKhoan", System.Data.SqlDbType.Int, 0).Value = maTaiKhoan; command.Connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <ChucNang_LoaiTaiKhoanDTO> result = new List <ChucNang_LoaiTaiKhoanDTO>(); if (reader.HasRows) { while (reader.Read()) { ChucNang_LoaiTaiKhoanDTO chucNang_LoaiTaiKhoan = new ChucNang_LoaiTaiKhoanDTO(); chucNang_LoaiTaiKhoan.MaChucNang = reader.GetInt32(0); chucNang_LoaiTaiKhoan.MaLoaiTaiKhoan = reader.GetInt32(1); result.Add(chucNang_LoaiTaiKhoan); } } command.Connection.Close(); return(result); }
public static List <TrendingMonDTO> LayDanhSachTop10MonDaBan() { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT TOP 10 ten_mon, SUM(so_luong) tong_so_luong FROM CTHoaDon INNER JOIN Mon ON mon=ma_mon INNER JOIN HoaDon ON HoaDon.ma_hoa_don=CTHoaDon.hoa_don WHERE HoaDon.trang_thai=1 GROUP BY ten_mon ORDER BY tong_so_luong DESC"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <TrendingMonDTO> result = new List <TrendingMonDTO>(); if (reader.HasRows) { while (reader.Read()) { TrendingMonDTO trendingMon = new TrendingMonDTO(); trendingMon.TenMon = reader.GetString(0); trendingMon.SoLuong = reader.GetInt32(1); result.Add(trendingMon); } } connection.Close(); return(result); }
public static List <NguyenLieuDTO> LayDanhSachTatCaNguyenLieu() { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ma_nguyen_lieu, ten_nguyen_lieu, so_luong, don_vi_tinh, trang_thai FROM NguyenLieu"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <NguyenLieuDTO> result = new List <NguyenLieuDTO>(); if (reader.HasRows) { while (reader.Read()) { NguyenLieuDTO nguyenlieu = new NguyenLieuDTO(); nguyenlieu.MaNguyenLieu = reader.GetInt32(0); nguyenlieu.TenNguyenLieu = reader.GetString(1); nguyenlieu.SoLuong = reader.GetDouble(2); nguyenlieu.DonViTinh = reader.GetString(3); nguyenlieu.TrangThai = reader.GetBoolean(4); result.Add(nguyenlieu); } } connection.Close(); return(result); }
public static List <rptHoaDon_TaiKhoanDTO> DanhSachHoaDonVuaMoiLapTrongNgay() { SqlConnection connection = DataProvider.GetConnection(); string query = "select HoaDon.ma_hoa_don, HoaDon.ngay_lap, TaiKhoan.ho_ten, HoaDon.tien_mat, HoaDon.tien_thua, HoaDon.tong_tien from HoaDon,TaiKhoan where HoaDon.nhan_vien_lap=TaiKhoan.ma_tai_khoan and DAY(GETDATE()) = DAY(ngay_lap) and MONTH(GETDATE()) = MONTH(ngay_lap) and YEAR(GETDATE()) = YEAR(ngay_lap)"; SqlCommand command = new SqlCommand(); command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <rptHoaDon_TaiKhoanDTO> result = new List <rptHoaDon_TaiKhoanDTO>(); if (reader.HasRows) { while (reader.Read()) { rptHoaDon_TaiKhoanDTO hoaDon_TaiKhoanDTO = new rptHoaDon_TaiKhoanDTO(); hoaDon_TaiKhoanDTO.MaHoaDon = reader.GetInt32(0); hoaDon_TaiKhoanDTO.NgayLap = reader.GetDateTime(1); hoaDon_TaiKhoanDTO.TenNhanVien = reader.GetString(2); hoaDon_TaiKhoanDTO.TienMat = reader.GetDouble(3); hoaDon_TaiKhoanDTO.TienThua = reader.GetDouble(4); hoaDon_TaiKhoanDTO.TongTien = reader.GetDouble(5); result.Add(hoaDon_TaiKhoanDTO); } } connection.Close(); return(result); }
public static List <LoaiToppingDTO> LayDanhSachCTLoaiMon_LoaiTopping(int maLoaiMon) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT DISTINCT LoaiTopping.ma_loai_topping, LoaiTopping.ten_loai_topping FROM CTLoaiMon_LoaiTopping,LoaiTopping WHERE CTLoaiMon_LoaiTopping.ma_loai_topping=LoaiTopping.ma_loai_topping AND CTLoaiMon_LoaiTopping.ma_loai_mon=@maLoaiMon AND LoaiTopping.trang_thai=1"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@maLoaiMon", System.Data.SqlDbType.Int, 0).Value = maLoaiMon; command.Connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <LoaiToppingDTO> result = new List <LoaiToppingDTO>(); if (reader.HasRows) { while (reader.Read()) { LoaiToppingDTO loaiTopping = new LoaiToppingDTO(); loaiTopping.MaLoaiTopping = reader.GetInt32(0); loaiTopping.TenLoaiTopping = reader.GetString(1); result.Add(loaiTopping); } } command.Connection.Close(); return(result); }
public static bool KiemTraDangNhap(int maTaiKhoan, string matKhau) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT mat_khau FROM TaiKhoan WHERE ma_tai_khoan=@maTaiKhoan"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@maTaiKhoan", System.Data.SqlDbType.Int, 0).Value = maTaiKhoan; connection.Open(); SqlDataReader reader = command.ExecuteReader(); bool result = false; if (reader.HasRows) { while (reader.Read()) { if (matKhau == reader.GetString(0)) { result = true; } } } connection.Close(); return(result); }
public static List <rptPhieuNhap_CTPhieuNhapDTO> DoiMaNguyenLieuThanhTenNguyenLieu(int maPhieuNhap) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT NguyenLieu.ten_nguyen_lieu, CTPhieuNhap.so_luong, CTPhieuNhap.don_vi_tinh, CTPhieuNhap.don_gia FROM CTPhieuNhap,NguyenLieu WHERE CTPhieuNhap.ma_nguyen_lieu=NguyenLieu.ma_nguyen_lieu and CTPhieuNhap.ma_phieu_nhap=@maPhieuNhap"; SqlCommand command = new SqlCommand(); command.Parameters.Add("@maPhieuNhap", System.Data.SqlDbType.Int, 0).Value = maPhieuNhap; command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <rptPhieuNhap_CTPhieuNhapDTO> result = new List <rptPhieuNhap_CTPhieuNhapDTO>(); if (reader.HasRows) { while (reader.Read()) { rptPhieuNhap_CTPhieuNhapDTO phieuNhap_CTPhieuNhapDTO = new rptPhieuNhap_CTPhieuNhapDTO(); phieuNhap_CTPhieuNhapDTO.TenNguyenLieu = reader.GetString(0); phieuNhap_CTPhieuNhapDTO.SoLuong = reader.GetDouble(1); phieuNhap_CTPhieuNhapDTO.DonViTinh = reader.GetString(2); phieuNhap_CTPhieuNhapDTO.DonGia = reader.GetDouble(3); result.Add(phieuNhap_CTPhieuNhapDTO); } } connection.Close(); return(result); }
public static bool LuuHoaDon(HoaDonDTO hoaDon) { SqlConnection connection = DataProvider.GetConnection(); string query = "INSERT INTO HoaDon (nhan_vien_lap, ngay_lap, tong_tien, tien_mat, tien_thua, trang_thai) VALUES (@nhanVienLap, @ngayLap, @tongTien, @tienMat, @tienThua, @trangThai)"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@nhanVienLap", System.Data.SqlDbType.Int, 0).Value = hoaDon.NhanVienLap; command.Parameters.Add("@ngayLap", System.Data.SqlDbType.DateTime, 0).Value = hoaDon.NgayLap; command.Parameters.Add("@tongTien", System.Data.SqlDbType.Float, 0).Value = hoaDon.TongTien; command.Parameters.Add("@tienMat", System.Data.SqlDbType.Float, 0).Value = hoaDon.TienMat; command.Parameters.Add("@tienThua", System.Data.SqlDbType.Float, 0).Value = hoaDon.TienThua; command.Parameters.Add("@trangThai", System.Data.SqlDbType.Bit, 0).Value = hoaDon.TrangThai; connection.Open(); int reader = command.ExecuteNonQuery(); connection.Close(); if (reader > 0) { return(true); } return(false); }
public static List <rptHoaDon_TaiKhoanDTO> LayHoaDonMaMoiNhat() { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT HoaDon.ma_hoa_don, HoaDon.ngay_lap, TaiKhoan.ho_ten, HoaDon.tien_mat, HoaDon.tien_thua, HoaDon.tong_tien FROM HoaDon,TaiKhoan where HoaDon.nhan_vien_lap=TaiKhoan.ma_tai_khoan and ma_hoa_don = (SELECT MAX(ma_hoa_don)FROM HoaDon)"; SqlCommand command = new SqlCommand(); command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <rptHoaDon_TaiKhoanDTO> result = new List <rptHoaDon_TaiKhoanDTO>(); if (reader.HasRows) { while (reader.Read()) { rptHoaDon_TaiKhoanDTO hoaDon_TaiKhoanDTO = new rptHoaDon_TaiKhoanDTO(); hoaDon_TaiKhoanDTO.MaHoaDon = reader.GetInt32(0); hoaDon_TaiKhoanDTO.NgayLap = reader.GetDateTime(1); hoaDon_TaiKhoanDTO.TenNhanVien = reader.GetString(2); hoaDon_TaiKhoanDTO.TienMat = reader.GetDouble(3); hoaDon_TaiKhoanDTO.TienThua = reader.GetDouble(4); hoaDon_TaiKhoanDTO.TongTien = reader.GetDouble(5); result.Add(hoaDon_TaiKhoanDTO); } } connection.Close(); return(result); }
// Lấy danh sách theo khoảng thời gian public static List <HoaDonDTO> GetListBillByTime(DateTime dateFrom, DateTime dateEnd) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ma_hoa_don, nhan_vien_lap, ngay_lap, tong_tien, tien_mat, tien_thua, trang_thai FROM HoaDon WHERE ngay_lap BETWEEN @dateFrom AND @dateEnd"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@dateFrom", System.Data.SqlDbType.DateTime, 0).Value = dateFrom; command.Parameters.Add("@dateEnd", System.Data.SqlDbType.DateTime, 0).Value = dateEnd; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <HoaDonDTO> result = new List <HoaDonDTO>(); if (reader.HasRows) { while (reader.Read()) { HoaDonDTO hd = new HoaDonDTO(); hd.MaHoaDon = reader.GetInt32(0); hd.NhanVienLap = reader.GetInt32(1); hd.NgayLap = reader.GetDateTime(2); hd.TongTien = reader.GetDouble(3); hd.TienMat = reader.GetDouble(4); hd.TienThua = reader.GetDouble(5); hd.TrangThai = reader.GetBoolean(6); result.Add(hd); } } connection.Close(); return(result); }
// Lấy hóa đơn một ngày cụ thể public static List <HoaDonDTO> GetListBillTimeline(DateTime timeLine) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ma_hoa_don, nhan_vien_lap, ngay_lap, tong_tien, tien_mat, tien_thua, trang_thai FROM HoaDon WHERE DATEPART(YYYY, ngay_lap) = YEAR(@timeLine) AND DATEPART(MM, ngay_lap) = MONTH(@timeLine) AND DATEPART(DD, ngay_lap) = DAY(@timeLine)"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@timeLine", System.Data.SqlDbType.DateTime, 0).Value = timeLine; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <HoaDonDTO> result = new List <HoaDonDTO>(); if (reader.HasRows) { while (reader.Read()) { HoaDonDTO hd = new HoaDonDTO(); hd.MaHoaDon = reader.GetInt32(0); hd.NhanVienLap = reader.GetInt32(1); hd.NgayLap = reader.GetDateTime(2); hd.TongTien = reader.GetDouble(3); hd.TienMat = reader.GetDouble(4); hd.TienThua = reader.GetDouble(5); hd.TrangThai = reader.GetBoolean(6); result.Add(hd); } } connection.Close(); return(result); }
public static List <LoaiTaiKhoanDTO> LayDanhSachTatCaLoaiTaiKhoan() { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ma_loai_tai_khoan, ten_loai_tai_khoan, trang_thai FROM LoaiTaiKhoan"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <LoaiTaiKhoanDTO> result = new List <LoaiTaiKhoanDTO>(); if (reader.HasRows) { while (reader.Read()) { LoaiTaiKhoanDTO loaiTaiKhoan = new LoaiTaiKhoanDTO(); loaiTaiKhoan.MaLoaiTaiKhoan = reader.GetInt32(0); loaiTaiKhoan.TenLoaiTaiKhoan = reader.GetString(1); loaiTaiKhoan.TrangThai = reader.GetBoolean(2); result.Add(loaiTaiKhoan); } } connection.Close(); return(result); }
public static List <DoanhThuDTO> LayDoanhThuHoaDon() { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT SUM(tong_tien) doanh_thu, MONTH(ngay_lap), YEAR(ngay_lap) FROM HoaDon WHERE trang_thai=1 AND YEAR(ngay_lap)=YEAR(GETDATE()) GROUP BY MONTH(ngay_lap), YEAR(ngay_lap) ORDER BY doanh_thu DESC"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <DoanhThuDTO> result = new List <DoanhThuDTO>(); if (reader.HasRows) { while (reader.Read()) { DoanhThuDTO dt = new DoanhThuDTO(); dt.Thang = reader.GetInt32(1); dt.DoanhThu = reader.GetDouble(0); result.Add(dt); } } connection.Close(); return(result); }
public static List <rptHoaDon_TaiKhoanDTO> DoiMaNhanVienThanhTenNhanVien(DateTime timeStart, DateTime timeEnd) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT HoaDon.ma_hoa_don, HoaDon.ngay_lap, TaiKhoan.ho_ten, HoaDon.tien_mat, HoaDon.tien_thua, HoaDon.tong_tien FROM HoaDon,TaiKhoan WHERE HoaDon.nhan_vien_lap=TaiKhoan.ma_tai_khoan and ngay_lap between @timeStart and @timeEnd"; SqlCommand command = new SqlCommand(); command.Parameters.Add("@timeStart", System.Data.SqlDbType.DateTime, 0).Value = timeStart; command.Parameters.Add("@timeEnd", System.Data.SqlDbType.DateTime, 0).Value = timeEnd; command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <rptHoaDon_TaiKhoanDTO> result = new List <rptHoaDon_TaiKhoanDTO>(); if (reader.HasRows) { while (reader.Read()) { rptHoaDon_TaiKhoanDTO hoaDon_TaiKhoanDTO = new rptHoaDon_TaiKhoanDTO(); hoaDon_TaiKhoanDTO.MaHoaDon = reader.GetInt32(0); hoaDon_TaiKhoanDTO.NgayLap = reader.GetDateTime(1); hoaDon_TaiKhoanDTO.TenNhanVien = reader.GetString(2); hoaDon_TaiKhoanDTO.TienMat = reader.GetDouble(3); hoaDon_TaiKhoanDTO.TienThua = reader.GetDouble(4); hoaDon_TaiKhoanDTO.TongTien = reader.GetDouble(5); result.Add(hoaDon_TaiKhoanDTO); } } connection.Close(); return(result); }
public static bool SuaMon(MonDTO mon) { SqlConnection connection = DataProvider.GetConnection(); string query = "UPDATE Mon SET ten_mon=@tenMon, loai_mon=@loaiMon, hinh=@hinh, gia_tien=@giaTien, trang_thai=@trangThai WHERE ma_mon=@maMon"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@maMon", System.Data.SqlDbType.Int, 0).Value = mon.MaMon; command.Parameters.Add("@tenMon", System.Data.SqlDbType.NVarChar, 255).Value = mon.TenMon; command.Parameters.Add("@loaiMon", System.Data.SqlDbType.NVarChar, 255).Value = mon.LoaiMon; command.Parameters.Add("@hinh", System.Data.SqlDbType.NVarChar, 255).Value = mon.Hinh; command.Parameters.Add("@giaTien", System.Data.SqlDbType.Float, 0).Value = mon.GiaTien; command.Parameters.Add("@trangThai", System.Data.SqlDbType.Bit, 0).Value = mon.TrangThai; connection.Open(); int reader = command.ExecuteNonQuery(); connection.Close(); if (reader == 1) { return(true); } return(false); }
public static List <rptMon_LoaiMonDTO> DoiMaLoaiMonThanhLoaiMon(int maMon) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT Mon.ma_mon, Mon.ten_mon, LoaiMon.ten_loai_mon, Mon.gia_tien FROM Mon,LoaiMon WHERE Mon.loai_mon=LoaiMon.ma_loai_mon and Mon.ma_mon=@maMon"; SqlCommand command = new SqlCommand(); command.Parameters.Add("@maMon", System.Data.SqlDbType.DateTime, 0).Value = maMon; command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <rptMon_LoaiMonDTO> result = new List <rptMon_LoaiMonDTO>(); if (reader.HasRows) { while (reader.Read()) { rptMon_LoaiMonDTO mon_LoaiMonDTO = new rptMon_LoaiMonDTO(); mon_LoaiMonDTO.MaMon = reader.GetInt32(0); mon_LoaiMonDTO.TenMon = reader.GetString(1); mon_LoaiMonDTO.TenLoaiMon = reader.GetString(2); mon_LoaiMonDTO.GiaTien = reader.GetDouble(3); result.Add(mon_LoaiMonDTO); } } connection.Close(); return(result); }
public static bool SuaTopping(ToppingDTO topping) { SqlConnection connection = DataProvider.GetConnection(); string query = "UPDATE Topping SET ten_topping=@tenTopping, loai_topping=@loaiTopping, gia_tien=@giaTien, hinh=@hinh, trang_thai=@trangThai WHERE ma_topping=@maTopping"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@maTopping", System.Data.SqlDbType.Int, 0).Value = topping.MaTopping; command.Parameters.Add("@tenTopping", System.Data.SqlDbType.NVarChar, 255).Value = topping.TenTopping; command.Parameters.Add("@loaiTopping", System.Data.SqlDbType.Int, 0).Value = topping.LoaiTopping; command.Parameters.Add("@giaTien", System.Data.SqlDbType.Float, 0).Value = topping.GiaTien; command.Parameters.Add("@hinh", System.Data.SqlDbType.NVarChar, 255).Value = topping.Hinh; command.Parameters.Add("@trangThai", System.Data.SqlDbType.Bit, 0).Value = topping.TrangThai; connection.Open(); int reader = command.ExecuteNonQuery(); connection.Close(); if (reader == 1) { return(true); } return(false); }
public static List <ChiPhiDTO> LayChiPhiPhieuNhap() { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT SUM(tong_tien) chi_phi, MONTH(ngay_lap), YEAR(ngay_lap) FROM PhieuNhap WHERE trang_thai=1 AND YEAR(ngay_lap)=YEAR(GETDATE()) GROUP BY MONTH(ngay_lap), YEAR(ngay_lap) ORDER BY chi_phi DESC"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <ChiPhiDTO> result = new List <ChiPhiDTO>(); if (reader.HasRows) { while (reader.Read()) { ChiPhiDTO cp = new ChiPhiDTO(); cp.Thang = reader.GetInt32(1); cp.ChiPhi = reader.GetDouble(0); result.Add(cp); } } connection.Close(); return(result); }
public static bool LuuCTPhieuNhap(CTPhieuNhapDTO ctpn) { SqlConnection connection = DataProvider.GetConnection(); string query = "INSERT INTO CTPhieuNhap (ma_phieu_nhap, ma_nguyen_lieu, so_luong, don_vi_tinh, don_gia, ghi_chu) VALUES (@maPhieuNhap, @maNguyenLieu, @soLuong, @donViTinh, @donGia, @ghiChu)"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add("@maPhieuNhap", System.Data.SqlDbType.Int, 0).Value = ctpn.MaPhieuNhap; command.Parameters.Add("@maNguyenLieu", System.Data.SqlDbType.Int, 0).Value = ctpn.MaNguyenLieu; command.Parameters.Add("@soLuong", System.Data.SqlDbType.Float, 0).Value = ctpn.SoLuong; command.Parameters.Add("@donViTinh", System.Data.SqlDbType.NVarChar, 255).Value = ctpn.DonViTinh; command.Parameters.Add("@donGia", System.Data.SqlDbType.Float, 0).Value = ctpn.DonGia; command.Parameters.Add("@ghiChu", System.Data.SqlDbType.NVarChar, 255).Value = ctpn.GhiChu; connection.Open(); int reader = command.ExecuteNonQuery(); connection.Close(); if (reader > 0) { return(true); } return(false); }
public static List <rptNhaCungCap_PhieuNhapDTO> DoiMaNhaCungCapThanhTenNhaCungCap() { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT PhieuNhap.ma_phieu_nhap,NhaCungCap.ten_nha_cung_cap, PhieuNhap.ngay_lap, PhieuNhap.tong_tien FROM NhaCungCap,PhieuNhap WHERE ma_nha_cung_cap = nha_cung_cap"; SqlCommand command = new SqlCommand(); command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <rptNhaCungCap_PhieuNhapDTO> result = new List <rptNhaCungCap_PhieuNhapDTO>(); if (reader.HasRows) { while (reader.Read()) { rptNhaCungCap_PhieuNhapDTO nhaCungCap_PhieuNhap = new rptNhaCungCap_PhieuNhapDTO(); nhaCungCap_PhieuNhap.MaPhieuNhap = reader.GetInt32(0); nhaCungCap_PhieuNhap.TenNhaCungCap = reader.GetString(1); nhaCungCap_PhieuNhap.NgayLap = reader.GetDateTime(2); nhaCungCap_PhieuNhap.TongTien = reader.GetDouble(3); result.Add(nhaCungCap_PhieuNhap); } } connection.Close(); return(result); }
public static List <rptNhaCungCap_PhieuNhapDTO> DoiMaNhaCungCapThanhTenNhaCungCap(DateTime timeLine) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT PhieuNhap.ma_phieu_nhap,NhaCungCap.ten_nha_cung_cap, PhieuNhap.ngay_lap, PhieuNhap.tong_tien FROM NhaCungCap,PhieuNhap WHERE ma_nha_cung_cap = nha_cung_cap and DATEPART(YYYY, ngay_lap) = YEAR(@timeLine) AND DATEPART(MM, ngay_lap) = MONTH(@timeLine) AND DATEPART(DD, ngay_lap) = DAY(@timeLine)"; SqlCommand command = new SqlCommand(); command.Parameters.Add("@timeLine", System.Data.SqlDbType.DateTime, 0).Value = timeLine; command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <rptNhaCungCap_PhieuNhapDTO> result = new List <rptNhaCungCap_PhieuNhapDTO>(); if (reader.HasRows) { while (reader.Read()) { rptNhaCungCap_PhieuNhapDTO nhaCungCap_PhieuNhap = new rptNhaCungCap_PhieuNhapDTO(); nhaCungCap_PhieuNhap.MaPhieuNhap = reader.GetInt32(0); nhaCungCap_PhieuNhap.TenNhaCungCap = reader.GetString(1); nhaCungCap_PhieuNhap.NgayLap = reader.GetDateTime(2); nhaCungCap_PhieuNhap.TongTien = reader.GetDouble(3); result.Add(nhaCungCap_PhieuNhap); } } connection.Close(); return(result); }
public static List <MonDTO> LayDanhSachMon(int maLoaiMon, string timKiem, bool trangThai) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ma_mon, ten_mon, loai_mon, LoaiMon.ten_loai_mon, hinh, gia_tien, Mon.trang_thai FROM Mon, LoaiMon WHERE LoaiMon.ma_loai_mon=Mon.loai_mon"; SqlCommand command = new SqlCommand(); if (maLoaiMon != 0) { query += " AND Mon.loai_mon=@maLoaiMon"; command.Parameters.Add("@maLoaiMon", System.Data.SqlDbType.Int, 0).Value = maLoaiMon; } if (timKiem != string.Empty) { query += " AND ten_mon LIKE N'%'+@timKiem+'%'"; command.Parameters.Add("@timKiem", System.Data.SqlDbType.NVarChar, 255).Value = timKiem; } if (trangThai) { query += " AND Mon.trang_thai=1 AND LoaiMon.trang_thai=1"; } else { query += " AND Mon.trang_thai=0 AND LoaiMon.trang_thai=1"; } command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <MonDTO> result = new List <MonDTO>(); if (reader.HasRows) { while (reader.Read()) { MonDTO mon = new MonDTO(); mon.MaMon = reader.GetInt32(0); mon.TenMon = reader.GetString(1); mon.LoaiMon = reader.GetInt32(2); mon.TenLoaiMon = reader.GetString(3); if (!reader.IsDBNull(4)) { mon.Hinh = reader.GetString(4); } mon.GiaTien = reader.GetDouble(5); mon.TrangThai = reader.GetBoolean(6); result.Add(mon); } } connection.Close(); return(result); }
public static List <TaiKhoanDTO> LayDanhSachTaiKhoan(string timKiem, int maLoaiTaiKhoan, bool trangThai) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ma_tai_khoan, ho_ten, ngay_bat_dau, loai_tai_khoan, hinh, TaiKhoan.trang_thai FROM TaiKhoan, LoaiTaiKhoan WHERE TaiKhoan.loai_tai_khoan=LoaiTaiKhoan.ma_loai_tai_khoan"; SqlCommand command = new SqlCommand(); if (timKiem != "") { query += " AND ho_ten LIKE N'%'+@timKiem+'%'"; command.Parameters.Add("@timKiem", System.Data.SqlDbType.NVarChar, 255).Value = timKiem; } if (maLoaiTaiKhoan != 0) { query += " AND loai_tai_khoan=@loaiTaiKhoan"; command.Parameters.Add("@loaiTaiKhoan", System.Data.SqlDbType.Int, 0).Value = maLoaiTaiKhoan; } if (trangThai) { query += " AND LoaiTaiKhoan.trang_thai=1 AND TaiKhoan.trang_thai=1"; } else { query += " AND LoaiTaiKhoan.trang_thai=1 AND TaiKhoan.trang_thai=0"; } command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <TaiKhoanDTO> result = new List <TaiKhoanDTO>(); if (reader.HasRows) { while (reader.Read()) { TaiKhoanDTO taiKhoan = new TaiKhoanDTO(); taiKhoan.MaTaiKhoan = reader.GetInt32(0); taiKhoan.HoTen = reader.GetString(1); taiKhoan.NgayBatDau = reader.GetDateTime(2); taiKhoan.LoaiTaiKhoan = reader.GetInt32(3); if (!reader.IsDBNull(4)) { taiKhoan.Hinh = reader.GetString(4); } taiKhoan.TrangThai = reader.GetBoolean(5); result.Add(taiKhoan); } } connection.Close(); return(result); }
public static int LayMaLoaiMonMoiNhat() { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT MAX(ma_loai_mon) FROM LoaiMon"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); int result = Convert.ToInt32(command.ExecuteScalar()); connection.Close(); return(result); }
public static int LayMaToppingMoiNhat() { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT MAX(ma_topping) FROM Topping"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); var result = command.ExecuteScalar(); connection.Close(); return(result is DBNull ? 0 : Convert.ToInt32(result)); }
public static List <ToppingDTO> LayDanhSachTopping(int maLoaiTopping, string timKiem, bool trangThai) { SqlConnection connection = DataProvider.GetConnection(); string query = "SELECT ma_topping, ten_topping, loai_topping, gia_tien, hinh, Topping.trang_thai FROM Topping, LoaiTopping WHERE LoaiTopping.ma_loai_topping=Topping.loai_topping"; SqlCommand command = new SqlCommand(); if (maLoaiTopping != 0) { query += " AND Topping.loai_topping=@MaLoaiTopping"; command.Parameters.Add("@MaLoaiTopping", System.Data.SqlDbType.Int, 0).Value = maLoaiTopping; } if (timKiem != "") { query += " AND ten_topping LIKE N'%'+@TimKiem+'%'"; command.Parameters.Add("@TimKiem", System.Data.SqlDbType.NVarChar, 255).Value = timKiem; } if (trangThai) { query += " AND Topping.trang_thai=1 AND LoaiTopping.trang_thai=1"; } else { query += " AND Topping.trang_thai=0 AND LoaiTopping.trang_thai=1"; } command.CommandText = query; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <ToppingDTO> result = new List <ToppingDTO>(); if (reader.HasRows) { while (reader.Read()) { ToppingDTO topping = new ToppingDTO(); topping.MaTopping = reader.GetInt32(0); topping.TenTopping = reader.GetString(1); topping.LoaiTopping = reader.GetInt32(2); topping.GiaTien = reader.GetDouble(3); topping.Hinh = reader.GetString(4); topping.TrangThai = reader.GetBoolean(5); result.Add(topping); } } connection.Close(); return(result); }