示例#1
0
        public int ThemPhong(PhongDTO p)
        {
            int      nRow     = 0;
            Provider provider = new Provider();

            try
            {
                string SqlPhong = "INSERT INTO PHONG(MaPhong,TenPhong,MaLoaiPhong,MaTinhTrang) VALUES (@MaPhong,@TenPhong,@MaLoaiPhong,@MaTinhTrang)";
                provider.Connect();
                nRow = provider.ExecuteNonQuery(CommandType.Text, SqlPhong,
                                                new SqlParameter {
                    ParameterName = "@MaPhong", Value = p.MaPhong
                },
                                                new SqlParameter {
                    ParameterName = "@TenPhong", Value = p.TenPhong
                },
                                                new SqlParameter {
                    ParameterName = "@MaLoaiPhong", Value = p.MaLoaiPhong
                },
                                                new SqlParameter {
                    ParameterName = "@MaTinhTrang", Value = p.MaLoaiTinhTrang
                }
                                                );
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
            return(nRow);
        }
        public DataTable loadLoaiTinhTrang()
        {
            Provider provider = new Provider();

            provider.Connect();

            DataTable dt       = new DataTable();
            string    sqlQuery = "SELECT * FROM LOAITINHTRANG";

            dt = provider.Select(CommandType.Text, sqlQuery);

            provider.DisConnect();

            return(dt);
        }
示例#3
0
        public DataTable DuLieuMCuoiDanhSach()
        {
            Provider provider = new Provider();

            provider.Connect();

            DataTable dt = new DataTable();

            string sqlQuery = "select max(p.MaPhong) from PHONG p";

            dt = provider.Select(CommandType.Text, sqlQuery);

            provider.DisConnect();
            return(dt);
        }
示例#4
0
        public DataTable loadLoaiPhong()
        {
            Provider provider = new Provider();

            provider.Connect();

            DataTable dt = new DataTable();

            string sqlQuery = "SELECT MaLoaiPhong, TenLoaiPhong FROM LOAIPHONG";

            dt = provider.Select(CommandType.Text, sqlQuery);

            provider.DisConnect();
            return(dt);
        }
示例#5
0
        public DataTable DanhSachLoaiTinhTrang()
        {
            Provider provider = new Provider();

            provider.Connect();

            DataTable dt = new DataTable();

            string sqlQuery = "SELECT ltt.MaTinhTrang,ltt.TenTinhTrang FROM LOAITINHTRANG ltt";

            dt = provider.Select(CommandType.Text, sqlQuery);

            provider.DisConnect();
            return(dt);
        }
示例#6
0
        public DataTable DanhSachLoaiKhachHang()
        {
            Provider provider = new Provider();

            provider.Connect();

            DataTable dt = new DataTable();

            string sqlQuery = "select lkh.MaLoaiKhachHang,lkh.TenLoaiKhach from LOAIKHACHHANG lkh";

            dt = provider.Select(CommandType.Text, sqlQuery);

            provider.DisConnect();
            return(dt);
        }
示例#7
0
        public DataTable DuLieuMCuoiDanhSach()
        {
            Provider provider = new Provider();

            provider.Connect();

            DataTable dt = new DataTable();

            string sqlQuery = "select max(kh.MaKhachHang) from KHACHHANG kh";

            dt = provider.Select(CommandType.Text, sqlQuery);

            provider.DisConnect();
            return(dt);
        }
示例#8
0
        public DataTable LayMaPhieuBaoCaoMatDOCuoiCung()
        {
            Provider provider = new Provider();

            try
            {
                string strSql = "SELECT MAX(BC.MaPhieuBaoCaoMatDoSuDungPhong) FROM PHIEUBAOCAOMATDOSUDUNGPHONG BC";
                provider.Connect();;
                DataTable dt = provider.Select(CommandType.Text, strSql);
                return(dt);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
        }
示例#9
0
        public DataTable DanhSachLoaiPhong()
        {
            Provider provider = new Provider();

            try
            {
                string strSql = "SELECT LP.MaLoaiPhong,LP.TenLoaiPhong FROM LOAIPHONG LP";
                provider.Connect();;
                DataTable dt = provider.Select(CommandType.Text, strSql);
                return(dt);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
        }
示例#10
0
        public DataTable LayMaPhieuBaoCaoCuoiCung()
        {
            Provider provider = new Provider();

            try
            {
                string strSql = "SELECT MAX(BC.MaPhieuBaoCaoDoanhThu) FROM PHIEUBAOCAODOANHTHU BC";
                provider.Connect();;
                DataTable dt = provider.Select(CommandType.Text, strSql);
                return(dt);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
        }
示例#11
0
        public DataTable LoadKhachHang()
        {
            Provider provider = new Provider();

            try
            {
                string strSql = "select kh.MaKhachHang, kh.TenKhachHang, kh.CMND, kh.DiaChi, lkh.TenLoaiKhach FROM KHACHHANG kh join LOAIKHACHHANG lkh on kh.MaLoaiKhachHang = lkh.MaLoaiKhachHang";
                provider.Connect();;
                DataTable dt = provider.Select(CommandType.Text, strSql);
                return(dt);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
        }
示例#12
0
        public DataTable getRevenue(int month)
        {
            Provider provider = new Provider();

            try
            {
                provider.Connect();
                List <SqlParameter> pars = new List <SqlParameter> {
                    new SqlParameter("@month", month)
                };
                return(provider.Select(CommandType.StoredProcedure, "getRevenue", pars.ToArray()));
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
        }
示例#13
0
        public int ThemDataVaoPhieuBaoCaoMatDo(PhieuCaoBaoMatDo bc)
        {
            int      n        = 0;
            Provider provider = new Provider();

            try
            {
                string strSql = "INSERT INTO PHIEUBAOCAOMATDOSUDUNGPHONG(MaPhieuBaoCaoMatDoSuDungPhong,MaLoaiPhong,MatDo,TiLe,ThangBaoCaoMatDo) VALUES (@MaPhieuBaoCaoMatDoSuDungPhong,@MaLoaiPhong,@MatDo,@TiLe,@ThangBaoCaoMatDo)";
                provider.Connect();;
                n = provider.ExecuteNonQuery(CommandType.Text, strSql,
                                             new SqlParameter {
                    ParameterName = "@MaPhieuBaoCaoMatDoSuDungPhong", Value = bc.MaPhieuMatDo
                },
                                             new SqlParameter {
                    ParameterName = "@MaLoaiPhong", Value = bc.MaLoaiPhong
                },
                                             new SqlParameter {
                    ParameterName = "@MatDo", Value = bc.MatDo
                },
                                             new SqlParameter {
                    ParameterName = "@TiLe", Value = bc.TiLe
                },
                                             new SqlParameter {
                    ParameterName = "@ThangBaoCaoMatDo", Value = bc.Thang
                }

                                             );
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
            return(n);
        }
示例#14
0
        public int ThemDataVaoPhieuBaoCao(PhieuCaoBaoDanhThu bc)
        {
            int      n        = 0;
            Provider provider = new Provider();

            try
            {
                string strSql = "INSERT INTO PHIEUBAOCAODOANHTHU(MaPhieuBaoCaoDoanhThu,ThangBaoCaoDoanhThu,DoanhThu,TiLe,MaLoaiPhong) VALUES (@MaPhieu,@Thang,@DoanhThu,@TiLe,@MaLoaiPhong)";
                provider.Connect();;
                n = provider.ExecuteNonQuery(CommandType.Text, strSql,
                                             new SqlParameter {
                    ParameterName = "@MaPhieu", Value = bc.MaPhieuBaoCao
                },
                                             new SqlParameter {
                    ParameterName = "@Thang", Value = bc.Thang
                },
                                             new SqlParameter {
                    ParameterName = "@DoanhThu", Value = bc.DoanhThu
                },
                                             new SqlParameter {
                    ParameterName = "@TiLe", Value = bc.TiLe
                },
                                             new SqlParameter {
                    ParameterName = "@MaLoaiPhong", Value = bc.MaLoaiPhong
                }

                                             );
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
            return(n);
        }
示例#15
0
        public DataTable PhieuCaoBaoMatDo(string Thang)
        {
            Provider provider = new Provider();

            try
            {
                string strSql = "SELECT MD.ThangBaoCaoMatDo,MD.MaLoaiPhong,MD.MatDo,MD.TiLe FROM PHIEUBAOCAOMATDOSUDUNGPHONG MD WHERE MD.ThangBaoCaoMatDo=@Thang";
                provider.Connect();;
                DataTable dt = provider.Select(CommandType.Text, strSql,
                                               new SqlParameter {
                    ParameterName = "@Thang", Value = Thang
                });
                return(dt);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
        }
示例#16
0
        public DataTable PhieuCaoBaoDanhThu(string Thang)
        {
            Provider provider = new Provider();

            try
            {
                string strSql = "SELECT PBC.MaLoaiPhong,PBC.DoanhThu,PBC.TiLe,PBC.ThangBaoCaoDoanhThu FROM PHIEUBAOCAODOANHTHU PBC WHERE PBC.ThangBaoCaoDoanhThu=@Thang";
                provider.Connect();;
                DataTable dt = provider.Select(CommandType.Text, strSql,
                                               new SqlParameter {
                    ParameterName = "@Thang", Value = Thang
                });
                return(dt);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
        }
示例#17
0
        public int ThemKhachHnag(KhachHangDTO kh)
        {
            int      nRow     = 0;
            Provider provider = new Provider();

            try
            {
                string SqlPhong = "INSERT INTO KHACHHANG(MaKhachHang,TenKhachHang,CMND,DiaChi,MaLoaiKhachHang) VALUES (@MaKhachHang,@TenKhachHang,@CMND,@DiaChi,@MaLoaiKhachHang)";
                provider.Connect();
                nRow = provider.ExecuteNonQuery(CommandType.Text, SqlPhong,
                                                new SqlParameter {
                    ParameterName = "@MaKhachHang", Value = kh.MaKhachHang
                },
                                                new SqlParameter {
                    ParameterName = "@TenKhachHang", Value = kh.TenKhachHang
                },
                                                new SqlParameter {
                    ParameterName = "@CMND", Value = kh.CMND
                },
                                                new SqlParameter {
                    ParameterName = "@DiaChi", Value = kh.DiaChi
                },
                                                new SqlParameter {
                    ParameterName = "@MaLoaiKhachHang", Value = kh.MaLoaiKhachHang
                }
                                                );
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
            return(nRow);
        }
示例#18
0
        public int ThemThanhToan(HoaDonDTO hoaDonDTO, List <ChiTietHoaDonDTO> dSChiTietHoaDonDTO)
        {
            Provider provider = new Provider();

            try
            {
                DataTable dt = new DataTable();
                provider.Connect();

                // tạo ra mã hóa đơn và ghi vào trong database
                string sqlQueryHD = "SELECT MaHoaDon FROM HOADON";
                dt = provider.Select(CommandType.Text, sqlQueryHD);
                string MaHoaDon;
                if (dt.Rows.Count == 0)
                {
                    MaHoaDon = "HD001";
                }
                else
                {
                    string so0       = "000";
                    string increment = (int.Parse(dt.Rows[dt.Rows.Count - 1].Field <string>(0).Substring(2)) + 1).ToString();
                    MaHoaDon = "HD" + so0.Substring(increment.Length) + increment;
                }

                string sqlQuery = "INSERT INTO HOADON(MaHoaDon, NgayThanhToan, TongThanhTien, MaKhachHang) VALUES(@MaHoaDon, @NgayThanhToan, @TongThanhTien, @MaKhachHang)";

                provider.ExecuteNonQuery(CommandType.Text, sqlQuery,
                                         new SqlParameter {
                    ParameterName = "@MaHoaDon", Value = MaHoaDon
                },
                                         new SqlParameter {
                    ParameterName = "@NgayThanhToan", Value = hoaDonDTO.NgayThanhToan
                },
                                         new SqlParameter {
                    ParameterName = "@TongThanhTien", Value = hoaDonDTO.TriGia
                },
                                         new SqlParameter {
                    ParameterName = "@MaKhachHang", Value = hoaDonDTO.MaKhachHang
                }
                                         );

                // tạo ra mã chi tiết hóa đơn và ghi vào trong database

                for (int i = 0; i < dSChiTietHoaDonDTO.Count; i++)
                {
                    string    sqlQueryMCTHD = "SELECT MaChiTietHoaDon FROM CHITIETHOADON";
                    DataTable dt_2          = new DataTable();

                    dt_2 = provider.Select(CommandType.Text, sqlQueryMCTHD);

                    string MaChiTietHoaDon;
                    if (dt_2.Rows.Count == 0)
                    {
                        MaChiTietHoaDon = "CTHD001";
                    }
                    else
                    {
                        string so0       = "000";
                        string increment = (int.Parse(dt_2.Rows[dt_2.Rows.Count - 1].Field <string>(0).Substring(4)) + 1).ToString();
                        MaChiTietHoaDon = "CTHD" + so0.Substring(increment.Length) + increment;
                    }

                    string sqlQuery_2 = "INSERT INTO CHITIETHOADON(MaChiTietHoaDon, SoNgayThue, DonGia, ThanhTien, MaPhong, MaHoaDon) VALUES(@MaChiTietHoaDon, @SoNgayThue, @DonGia, @ThanhTien, @MaPhong, @MaHoaDon)";

                    provider.ExecuteNonQuery(CommandType.Text, sqlQuery_2,
                                             new SqlParameter {
                        ParameterName = "@MaChiTietHoaDon", Value = MaChiTietHoaDon
                    },
                                             new SqlParameter {
                        ParameterName = "@SoNgayThue", Value = dSChiTietHoaDonDTO[i].SoNgayThue
                    },
                                             new SqlParameter {
                        ParameterName = "@DonGia", Value = dSChiTietHoaDonDTO[i].DonGia
                    },
                                             new SqlParameter {
                        ParameterName = "@ThanhTien", Value = dSChiTietHoaDonDTO[i].ThanhTien
                    },
                                             new SqlParameter {
                        ParameterName = "@MaPhong", Value = dSChiTietHoaDonDTO[i].MaPhong
                    },
                                             new SqlParameter {
                        ParameterName = "@MaHoaDon", Value = MaHoaDon
                    }
                                             );

                    // cập nhật tình trạng phòng

                    string sqlQuery_3 = "UPDATE PHONG SET MaTinhTrang = 'TT001' WHERE MaPhong = @MaPhong";
                    provider.ExecuteNonQuery(CommandType.Text, sqlQuery_3,
                                             new SqlParameter {
                        ParameterName = "@MaPhong", Value = dSChiTietHoaDonDTO[i].MaPhong
                    }
                                             );
                }


                provider.DisConnect();

                return(1);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                provider.DisConnect();
            }
        }