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);
        }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        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);
        }
Beispiel #9
0
        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);
        }
Beispiel #11
0
        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);
        }
Beispiel #12
0
        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);
        }
Beispiel #14
0
        // 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);
        }
Beispiel #15
0
        // 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);
        }
Beispiel #16
0
        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);
        }
Beispiel #17
0
        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);
        }
Beispiel #19
0
        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);
        }
Beispiel #21
0
        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);
        }
Beispiel #22
0
        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);
        }
Beispiel #26
0
        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);
        }
Beispiel #29
0
        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));
        }
Beispiel #30
0
        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);
        }