public IEnumerable <BookingGuestRpt> CheckinReportAll(int type, BookingGuestRpt bg)
        {
            List <BookingGuestRpt> obj = new List <BookingGuestRpt>();

            try
            {
                MySqlCommand cmd = new MySqlCommand("sp_rpt_BookingByCheckinReport", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("type", type);
                cmd.Parameters.AddWithValue("pCheckinDate", bg.FromDate);

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    HotelDetailstbl hd = new HotelDetailstbl()
                    {
                        HotelDetailsId = Convert.ToInt32(dr["HotelDetailsId"]),
                        //HotelId = Convert.ToInt32(dr["HotelId"]),
                        HotelName = dr["HotelName"].ToString(),
                        Address   = dr["Address"].ToString(),
                        //HotelLocation = dr["HotelLocation"].ToString(),
                        ContactNumber = dr["ContactNumber"].ToString(),
                    };
                    BookingDetailstbl bd = new BookingDetailstbl()
                    {
                        BookingDetailsId = Convert.ToInt32(dr["BookingDetailsId"]),
                        //BookingId = dr["BookingId"].ToString(),
                        HotelDetailsId = Convert.ToInt32(dr["HotelDetailsId"]),
                        Checkin        = dr["Checkin"].ToString(),
                        Checkout       = dr["Checkout"].ToString(),
                        NoofRooms      = Convert.ToInt32(dr["NoofRooms"]),
                        AgentPrice     = dr["AgentPrice"].ToString(),
                        AgentID        = Convert.ToInt32(dr["AgentId"]),
                    };
                    List <RoomDetailstbl>  lstrd = new List <RoomDetailstbl>();
                    List <GuestDetailstbl> lstgd = new List <GuestDetailstbl>();
                    Supplierstbl           s     = new Supplierstbl()
                    {
                        SupplierID       = Convert.ToInt32(dr["SupplierID"]),
                        Supplier         = dr["Supplier"].ToString(),
                        PersonName       = dr["PersonName"].ToString(),
                        Email            = dr["Email"].ToString(),
                        MobileNo         = dr["MobileNo"].ToString(),
                        Address          = dr["Address"].ToString(),
                        ApiPurchaseDate  = dr["ApiPurchaseDate"].ToString(),
                        ApiKey           = dr["ApiKey"].ToString(),
                        ApiPurchaseValue = dr["ApiPurchaseValue"].ToString(),
                        AgentLink        = dr["AgentLink"].ToString(),
                        SupplierCode     = dr["SupplierCode"].ToString(),
                    };
                    Agentstbl a = new Agentstbl()
                    {
                        AgentID     = Convert.ToInt32(dr["AgentID"]),
                        AgencyCode  = dr["AgencyCode"].ToString(),
                        AgencyName  = dr["AgentName"].ToString(),
                        AgencyEmail = dr["AgencyEmail"].ToString(),
                        FirstName   = dr["FirstName"].ToString(),
                        LastName    = dr["LastName"].ToString(),
                        UserName    = dr["UserName"].ToString(),
                        Password    = dr["Password"].ToString(),
                        ContactName = dr["ContactName"].ToString(),
                        MobileNo    = dr["MobileNo"].ToString(),
                        CategoryId  = Convert.ToInt32(dr["CategoryId"]),
                        Address     = dr["Address"].ToString(),
                    };
                    lstrd.Add(new RoomDetailstbl
                    {
                        RoomDetailsId    = Convert.ToInt32(dr["RoomDetailsId"]),
                        BookingDetailsId = Convert.ToInt32(dr["BookingDetailsId"]),
                        Adults           = Convert.ToInt32(dr["Adults"]),
                        Kids             = Convert.ToInt32(dr["Kids"]),
                        RoomType         = dr["RoomType"].ToString(),
                        Price            = dr["Price"].ToString(),
                    });
                    lstgd.Add(new GuestDetailstbl
                    {
                        GuestId          = Convert.ToInt32(dr["GuestId"]),
                        BookingDetailsId = Convert.ToInt32(dr["BookingDetailsId"]),
                        RoomDetailsId    = Convert.ToInt32(dr["RoomDetailsId"]),
                        FirstName        = dr["FirstName"].ToString(),
                        LastName         = dr["LastName"].ToString(),
                        Salutation       = dr["Salutation"].ToString(),
                        Type             = dr["Type"].ToString(),
                        Status           = Convert.ToInt32(dr["Status"]),
                        Age = Convert.ToInt32(dr["Age"]),
                    });

                    obj.Add(new BookingGuestRpt
                    {
                        HotelDetailscls   = hd,
                        BookingDetailscls = bd,
                        RoomDetailscls    = lstrd,
                        GuestDetailscls   = lstgd,
                        Supplierscls      = s,
                        Agentscls         = a
                    });
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                obj.Add(new BookingGuestRpt
                {
                    Message      = "Error",
                    ErrorMessage = ex.Message,
                });
            }
            finally
            {
                con.Close();
            }
            return(obj);
        }
        public IEnumerable <HotelDetailstbl> HotelDetails(int type, HotelDetailstbl hd)
        {
            List <HotelDetailstbl> objHD = new List <HotelDetailstbl>();

            try
            {
                MySqlCommand cmd = new MySqlCommand("Sp_BD_HotelDetails", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("type", type);
                cmd.Parameters.AddWithValue("pHotelDetailsId", hd.HotelDetailsId);
                cmd.Parameters.AddWithValue("pHotelCode", hd.HotelCode);
                cmd.Parameters.AddWithValue("pHotelName", hd.HotelName);
                cmd.Parameters.AddWithValue("pDescription", hd.Description);
                cmd.Parameters.AddWithValue("pAddress", hd.Address);
                cmd.Parameters.AddWithValue("pCategory", hd.Category);
                cmd.Parameters.AddWithValue("pContactNumber", hd.ContactNumber);
                cmd.Parameters.AddWithValue("pCityID", hd.CityID);
                cmd.Parameters.AddWithValue("pCountryID", hd.CountryID);
                cmd.Parameters.AddWithValue("pLongitude", hd.Longitude);
                cmd.Parameters.AddWithValue("pLatitude", hd.Latitude);
                cmd.Parameters.AddWithValue("pCreatedBy", hd.CreatedBy);
                cmd.Parameters.AddWithValue("pCreatedDate", hd.CreatedDate);

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    objHD.Add(new HotelDetailstbl
                    {
                        HotelDetailsId = Convert.ToInt32(dr["HotelDetailsId"]),
                        HotelCode      = dr["HotelCode"].ToString(),
                        HotelName      = dr["HotelName"].ToString(),
                        Address        = dr["Address"].ToString(),
                        Description    = dr["Description"].ToString(),
                        Category       = Convert.ToInt32(dr["Category"]),
                        CityID         = dr["CityID"].ToString(),
                        CountryID      = dr["CountryID"].ToString(),
                        Longitude      = dr["Longitude"].ToString(),
                        Latitude       = dr["Latitude"].ToString(),
                        ContactNumber  = dr["ContactNumber"].ToString(),
                        CreatedBy      = dr["CreatedBy"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CreatedBy"]),
                        CreatedDate    = dr["CreatedDate"].ToString(),
                    });
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                objHD.Add(new HotelDetailstbl
                {
                    Message      = "Error",
                    ErrorMessage = ex.Message,
                });
            }
            finally
            {
                con.Close();
            }
            return(objHD);
        }