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 <BookingDetailstbl> BookingDetails(int type, BookingDetailstbl bd)
        {
            List <BookingDetailstbl> objBD = new List <BookingDetailstbl>();

            try
            {
                MySqlCommand cmd = new MySqlCommand("Sp_BD_BookingDetails", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("type", type);
                cmd.Parameters.AddWithValue("pBookingDetailsId", bd.BookingDetailsId);
                cmd.Parameters.AddWithValue("pHotelDetailsId", bd.HotelDetailsId);
                cmd.Parameters.AddWithValue("pAgentID", bd.AgentID);
                cmd.Parameters.AddWithValue("pSupplierID", bd.SupplierID);
                cmd.Parameters.AddWithValue("pSupBookingId", bd.SupBookingId);
                cmd.Parameters.AddWithValue("pAgentBookingId", bd.AgentBookingId);
                cmd.Parameters.AddWithValue("pCheckin", bd.Checkin);
                cmd.Parameters.AddWithValue("pCheckout", bd.Checkout);
                cmd.Parameters.AddWithValue("pNoofRooms", bd.NoofRooms);
                cmd.Parameters.AddWithValue("pAgentPrice", bd.AgentPrice);
                cmd.Parameters.AddWithValue("pSupplierPrice", bd.SupplierPrice);
                cmd.Parameters.AddWithValue("pStatus", bd.Status);
                cmd.Parameters.AddWithValue("pCancellation", bd.Cancellation);
                cmd.Parameters.AddWithValue("pAmendment", bd.Amendment);
                cmd.Parameters.AddWithValue("pPaymentType", bd.PaymentType);
                cmd.Parameters.AddWithValue("pPaymentStatus", bd.PaymentStatus);
                cmd.Parameters.AddWithValue("pNonRefundable", bd.NonRefundable);
                cmd.Parameters.AddWithValue("pCurrency", bd.Currency);
                cmd.Parameters.AddWithValue("pSupBookRef", bd.SupBookRef);
                cmd.Parameters.AddWithValue("pBookingDate", bd.BookingDate);
                cmd.Parameters.AddWithValue("pBookingComment", bd.BookingComment);
                cmd.Parameters.AddWithValue("pAdditionalInfo", bd.AdditionalInfo);
                cmd.Parameters.AddWithValue("pTitle", bd.Title);
                cmd.Parameters.AddWithValue("pName", bd.Name);
                cmd.Parameters.AddWithValue("pSurName", bd.SurName);
                cmd.Parameters.AddWithValue("pNationality", bd.Nationality);
                cmd.Parameters.AddWithValue("pMobileNumber", bd.MobileNumber);
                cmd.Parameters.AddWithValue("pEmailID", bd.EmailID);
                cmd.Parameters.AddWithValue("pBookingType", bd.BookingType);
                cmd.Parameters.AddWithValue("pConfirmationID", bd.ConfirmationID);
                cmd.Parameters.AddWithValue("pReConfirmationID", bd.ReConfirmationID);
                cmd.Parameters.AddWithValue("pCreatedBy", bd.CreatedBy);
                cmd.Parameters.AddWithValue("pCreatedDate", bd.CreatedDate);
                cmd.Parameters.AddWithValue("pUpdatedBy", bd.UpdatedBy);
                cmd.Parameters.AddWithValue("pUpdatedDate", bd.UpdatedDate);


                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    objBD.Add(new BookingDetailstbl
                    {
                        BookingDetailsId = Convert.ToInt32(dr["BookingDetailsId"]),
                        HotelDetailsId   = dr["HotelDetailsId"] == DBNull.Value ? 0 : Convert.ToInt32(dr["HotelDetailsId"]),
                        AgentID          = dr["AgentID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["AgentID"].ToString()),
                        SupplierID       = dr["SupplierID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SupplierID"].ToString()),
                        SupBookingId     = dr["SupBookingId"].ToString(),
                        AgentBookingId   = dr["AgentBookingId"].ToString(),
                        Checkin          = dr["Checkin"].ToString(),
                        Checkout         = dr["Checkout"].ToString(),
                        NoofRooms        = dr["NoofRooms"] == DBNull.Value ? 0 : Convert.ToInt32(dr["NoofRooms"].ToString()),
                        AgentPrice       = dr["AgentPrice"].ToString(),
                        SupplierPrice    = dr["SupplierPrice"].ToString(),
                        Status           = dr["Status"].ToString(),
                        Cancellation     = dr["Cancellation"].ToString(),
                        Amendment        = dr["Amendment"].ToString(),
                        PaymentType      = dr["PaymentType"].ToString(),
                        PaymentStatus    = dr["PaymentStatus"].ToString(),
                        NonRefundable    = dr["NonRefundable"].ToString(),
                        Currency         = dr["Currency"].ToString(),
                        SupBookRef       = dr["SupBookRef"].ToString(),
                        BookingDate      = dr["BookingDate"].ToString(),
                        BookingComment   = dr["BookingComment"].ToString(),
                        AdditionalInfo   = dr["AdditionalInfo"].ToString(),
                        Title            = dr["Title"].ToString(),
                        Name             = dr["Name"].ToString(),
                        SurName          = dr["SurName"].ToString(),
                        //Nationality = dr["Nationality"] == DBNull.Value ? 0 : Convert.ToInt32(dr["Nationality"]),
                        BookingType      = dr["BookingType"].ToString(),
                        MobileNumber     = dr["MobileNumber"].ToString(),
                        EmailID          = dr["EmailID"].ToString(),
                        CreatedBy        = dr["CreatedBy"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CreatedBy"]),
                        CreatedDate      = dr["CreatedDate"].ToString(),
                        UpdatedBy        = dr["UpdatedBy"] == DBNull.Value ? 0 : Convert.ToInt32(dr["UpdatedBy"]),
                        UpdatedDate      = dr["UpdatedDate"].ToString(),
                        ConfirmationID   = dr["ConfirmationID"].ToString(),
                        ReConfirmationID = dr["ReConfirmationID"].ToString(),
                        Message          = "Success",
                    });
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                objBD.Add(new BookingDetailstbl
                {
                    Message      = "Error",
                    ErrorMessage = ex.Message,
                });
            }
            finally
            {
                con.Close();
            }
            return(objBD);
        }