Multi thread sample

From Logic Wiki
Jump to: navigation, search



Imports System.Data
Imports System.Data.SqlClient
Imports System
Imports System.Collections
Imports System.Collections.Specialized
Imports System.IO
Imports System.Web
Imports System.Web.HttpContext
Imports System.Web.Services.Protocols
Imports System.Configuration
Imports System.Xml
Imports NetMarketeers.TravelServer.DB
Imports NetMarketeers.Utility.Data


Imports System.Threading

Namespace NetMarketeers.TravelServer.Engine


    <Serializable()> Public Class BedBankSupplier : Implements IAsyncSupplier

        Private inAgentId As Integer
        Private inAffiliateId As Integer
        Private stWebSessionId As String
        Private RatingCodes As String = String.Empty
        Private DestinationCodesGloballyExcluded() As String, ResortCodesGloballyExcluded() As String
        Private _LoadLocal As Boolean

        Protected ServerApplicationState As HttpApplicationState
        Protected Session As SessionState.HttpSessionState

        ' Live Web Service:
        Protected bbc As BedbankWebService.BedBankContext

        Private iasBB As IAsyncResult
        Private _AllowSearch As Boolean = True


        Public ReadOnly Property AllowSearch() As Boolean Implements IAsyncSupplier.AllowSearch
            Get
                Return _AllowSearch
            End Get
        End Property



#Region "Constructors"

        Public Sub New(ByVal inNewAgentId As Integer, ByVal inNewAffiliateId As Integer, _
        ByVal stNewWebSessionId As String)
            inAgentId = inNewAgentId
            inAffiliateId = inNewAffiliateId
            stWebSessionId = stNewWebSessionId
        End Sub

        Public Sub New(ByVal inNewAgentId As Integer, ByVal inNewAffiliateId As Integer, _
        ByVal stNewWebSessionId As String, ByRef Session As SessionState.HttpSessionState, _
        ByRef Application As HttpApplicationState, ByRef excludedDestinationCodes() As String, _
        ByRef excludedResortCodes() As String)
            inAgentId = inNewAgentId
            inAffiliateId = inNewAffiliateId
            stWebSessionId = stNewWebSessionId

            Me.Session = Session
            Me.ServerApplicationState = Application
            Me.DestinationCodesGloballyExcluded = excludedDestinationCodes
            Me.ResortCodesGloballyExcluded = excludedResortCodes
        End Sub

        Public Sub New(ByRef Session As SessionState.HttpSessionState, ByRef Application As HttpApplicationState, _
        ByVal stNewWebSessionId As String)
            Me.ServerApplicationState = Application
            Me.Session = Session
            stWebSessionId = stNewWebSessionId

            Me._LoadLocal = Application("RUN_LOCAL")
        End Sub

#End Region


#Region " Search "
        Public DBConnStr As String
        Public CacheConnStr As String
 

        Private Function GetRoomsAvailable(ByVal dteArrivalDate As DateTime, _
        ByVal dteDepartureDate As DateTime, ByVal intDestinationID As Integer, _
        ByVal intResortID As Object, ByVal intBoardTypeID As Object, _
        ByVal intPassengers As Integer) As DataTable
            Dim strConn As String = Current.Application.Get("SearchCacheConnectionString")
            Dim sqlConn As New SqlConnection(strConn)

            'SQLRun("INSERT INTO AliLog (LogTxt)  VALUES ( 'GetRoomsAvailable Function Entered " & Now.ToLongTimeString & " " & Now.Millisecond & "')")

            Dim strSQL As String = "" & _
                "SELECT gra.* FROM tblGetRoomsAvailableCache grac " & _
                "LEFT JOIN tblGetRoomsAvailable gra ON gra.CacheID = grac.CacheID " & _
                "WHERE grac.CacheIsInvalidated = 'N' " & _
                "AND grac.FromDate = @FromDate AND grac.ToDate = @ToDate " & _
                "AND grac.DestinationID = @DestinationID "

            If IsNumeric(intResortID) Then
                strSQL &= "AND grac.ResortID = @ResortID "
            End If
            If IsNumeric(intBoardTypeID) Then
                strSQL &= "AND grac.BoardTypeID = @BoardTypeID "
            End If

            strSQL &= "AND grac.OccupancyID = @Passengers;"

            Dim sqlComm As New SqlCommand(strSQL, sqlConn)
            sqlComm.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = dteArrivalDate
            sqlComm.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = dteDepartureDate
            sqlComm.Parameters.Add("@DestinationID", SqlDbType.BigInt).Value = intDestinationID
            If IsNumeric(intResortID) Then
                sqlComm.Parameters.Add("@ResortID", SqlDbType.BigInt).Value = intResortID
            End If
            If IsNumeric(intBoardTypeID) Then
                sqlComm.Parameters.Add("@BoardTypeID", SqlDbType.Int).Value = intBoardTypeID
            End If
            sqlComm.Parameters.Add("@Passengers", SqlDbType.Int).Value = intPassengers

            Dim sqlDA As New SqlDataAdapter(sqlComm)
            Dim dt As New DataTable
            sqlDA.Fill(dt)
            'SQLRun("INSERT INTO AliLog (LogTxt)  VALUES ( 'GetRoomsAvailable Function Finished dt.rows.count : " & dt.Rows.Count & " - " & Now.ToLongTimeString & " " & Now.Millisecond & "')")
            sqlDA = Nothing
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
            sqlComm = Nothing
            sqlConn = Nothing

            'For i As Integer = 0 To dt.Rows.Count - 1
            '    DBTools.SQLRun("INSERT INTO AliLog (LogTxt) VALUES ('Loop " & dt.Rows(i).Item("RoomID") & " Infant : " & dt.Rows(i).Item("Infant") & "')")
            'Next
            Return dt
        End Function

        Private Sub UpdateCacheHit(ByVal strCacheID As String)
            Dim strConn As String = Current.Application.Get("SearchCacheConnectionString")
            Dim sqlConn As New SqlConnection(strConn)

            Dim strSQL As String = "" & _
                "UPDATE tblGetRoomsAvailableCache SET " & _
                "CacheHitCount = CacheHitCount + 1 WHERE CacheID = @CacheID;"
            Dim sqlComm As New SqlCommand(strSQL, sqlConn)
            sqlComm.Parameters.Add("@CacheID", SqlDbType.VarChar, 36).Value = strCacheID
            sqlConn.Open()

            Try
                sqlComm.ExecuteNonQuery()
            Finally
                sqlConn.Close()
            End Try

            sqlComm = Nothing
            sqlConn = Nothing
        End Sub

        ''' <summary>
        ''' Multi Thread search uses this sub for waiting the threads. 
        ''' Other methods of waiting multi thread failed. This tricky sub works fine
        ''' </summary>
        ''' <param name="waitHandles"></param>
        ''' <remarks></remarks>
        Sub WaitAll(ByVal waitHandles() As WaitHandle)

            If Thread.CurrentThread.GetApartmentState() = ApartmentState.STA Then
                For Each waitHandle As WaitHandle In waitHandles
                    waitHandle.WaitAny(New WaitHandle() {waitHandle})
                Next
            Else
                WaitHandle.WaitAll(waitHandles)
            End If

        End Sub

        ''' <summary>
        ''' This Delegate is for Multithread search. 
        ''' </summary>
        ''' <param name="CP">This parameter is a class holding parameters for Calculation </param>
        ''' <remarks></remarks>
        Delegate Sub CalcHandler(CP As CalcParam)

        ' -----------------------------
        ' START: Standard search method
        ' -----------------------------
        Public Function Search(ByRef sc As SearchCriteria, ByVal blnWebServiceRequest As Boolean) As RoomList
            Dim SearchStartTime As DateTime = DateTime.Now
            Dim Timings As String = ""
            DBConnStr = Current.Application.Get("ConnectionString")
            CacheConnStr = Current.Application.Get("SearchCacheConnectionString")

            Dim Infant As Integer = 0
            If Not IsNothing(Session("InfantQty")) Then
                'ALI   
                Infant = CInt(Session("InfantQty"))
            End If

            SqlConnection.ClearAllPools()
                     

            '##############################################
            ' MultiThread Parameters
            '##############################################
            Dim RoomsPerThread As Integer = 50
            Dim MultiThreadEnabled As Boolean = True
            '##############################################
            Dim RetVal As String = ""
            ' Check search parameters
            Dim LogStr As String = ""
            LogStr &= "Search Started : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf

            If sc.DestinationID.Length = 0 Then
                ' Do not start the search if the destination has not been specified
                Me._AllowSearch = False
                Return Nothing
            ElseIf Not Me.DestinationCodesGloballyExcluded Is Nothing Then
                ' Do not start the search if the specified destination is excluded at a global level
                For intIndex As Integer = 0 To UBound(Me.DestinationCodesGloballyExcluded)
                    If LCase(Me.DestinationCodesGloballyExcluded(intIndex)).Equals(LCase(sc.DestinationID)) Then
                        Me._AllowSearch = False
                        Return Nothing
                    End If
                Next
            End If

            If sc.ResortID.Length > 0 Then
                If Not Me.ResortCodesGloballyExcluded Is Nothing Then
                    ' Do not start the search if the specified resort is excluded at a global level
                    For intIndex As Integer = 0 To UBound(Me.ResortCodesGloballyExcluded)
                        If LCase(Me.ResortCodesGloballyExcluded(intIndex)).Equals(LCase(sc.ResortID)) Then
                            Me._AllowSearch = False
                            Return Nothing
                        End If
                    Next
                End If
            End If



            If Not sc.RatingType Is Nothing AndAlso IsNumeric(sc.RatingType) AndAlso CInt(sc.RatingType) <> 0 Then
                Dim params() As Object = {Convert.ToInt32(sc.RatingType)}
                Dim DS As DataSet = SqlHelper.ExecuteDataset(ServerApplicationState("SearchConnectionString"), "usp_Engine_Bedbank_GetRatingCodes_NEW", params)

                Me.RatingCodes = String.Empty
                If Not DS Is Nothing AndAlso Not DS.Tables Is Nothing AndAlso DS.Tables.Count > 0 Then
                    If Not DS.Tables(0) Is Nothing AndAlso Not DS.Tables(0).Rows Is Nothing AndAlso DS.Tables(0).Rows.Count > 0 Then
                        If Not DS.Tables(0).Rows(0)("BedbankCodes") Is Nothing Then
                            Me.RatingCodes = Convert.ToString(DS.Tables(0).Rows(0)("BedbankCodes"))
                        End If
                    End If
                End If

                If Me.RatingCodes.Length = 0 Then
                    Me._AllowSearch = False
                    Return Nothing
                End If
            Else
                Me.RatingCodes = "ALL"
            End If

            Dim oList As New RoomList
            Dim intDestinationID As Integer = CInt(sc.DestinationID)

            ' Resort ID is optional
            Dim intResortID As Object = Convert.DBNull
            If sc.ResortID.Length > 0 Then
                intResortID = CInt(sc.ResortID)
            End If

            ' Board type ID is optional
            Dim intBoardTypeID As Object = Convert.DBNull
            If sc.BoardType.Length > 0 Then
                intBoardTypeID = CInt(sc.BoardType)
            End If

            ' Log search parameters
            Dim dteSearchDate As DateTime = Now()

            Try
                Dim strOutput As String = "" & dteSearchDate.ToString() & "," & Me.stWebSessionId & "," & _
                sc.AgentID & "," & sc.ArrivalDate.ToString() & "," & sc.DepartureDate.ToString() & "," & sc.CountryID & "," & _
                intDestinationID & "," & intResortID & ",'" & sc.AccommodationIDList & "'," & intBoardTypeID & "," & sc.PassengerString

                Dim strLogFilePath As String = Current.Application.Get("SEARCH_LOG_FILE_DIR") & "Engine_" & dteSearchDate.Year & "_" & dteSearchDate.Month & "_" & dteSearchDate.Day & "_" & dteSearchDate.Hour & ".csv"
                Dim fs As New System.IO.FileStream(strLogFilePath, System.IO.FileMode.Append)
                Dim sw As New System.IO.StreamWriter(fs)
                sw.WriteLine(strOutput)
                sw.Close()
                fs.Close()
                sw = Nothing
                fs = Nothing
            Catch

            End Try
            LogStr &= "Validation and Log Phase completed : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf

            Dim tblRooms As DataTable = Nothing
            Dim blnUseSearchCacheMode As Boolean = False
            If Not IsNothing(Current.Application.Get("CACHE_SEARCH_MODE_ON")) _
            AndAlso CBool(Current.Application.Get("CACHE_SEARCH_MODE_ON")) Then
                blnUseSearchCacheMode = True
            End If

            If blnUseSearchCacheMode Then
                'SQLRun("INSERT INTO AliLog (LogTxt)  VALUES ( 'CACHE MODE Line 260 " & Now.ToLongTimeString & " " & Now.Millisecond & "')")
                ' CACHE SEARCH RESULTS
                ' Start availability search by querying cached table



                tblRooms = GetRoomsAvailable(sc.ArrivalDate, sc.DepartureDate, _
                    intDestinationID, intResortID, intBoardTypeID, CInt(sc.PassengerString))

                LogStr &= "Get Rooms Available Completed: " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
                LogStr &= "Available Rooms (" & tblRooms.Rows.Count & ") " & vbCrLf

                If tblRooms.Rows.Count > 0 And Infant = 0 Then ' if there is an infant, cached results will always be wrong. A fresh search is needed. 
                    ' If a cache record can be found, update its hit count
                    UpdateCacheHit(tblRooms.Rows(0).Item("CacheID").ToString())

                    LogStr &= "Cache Hit Completed : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
                Else


                    ' If the cached table returns no rooms, call the cached availability stored procedure
                    Dim sqlConn As New SqlConnection(Current.Application.Get("ConnectionString"))
                    Dim sqlComm As New SqlCommand("usp_GetRoomsAvailableUseCache_NEWPRICING", sqlConn)
                    If blnWebServiceRequest Then
                        sqlComm.CommandTimeout = 12     ' XML requests should return within 12 seconds
                    End If


                    sc.PassengerString = CStr(CInt(sc.PassengerString) + Infant)

                    sqlComm.CommandType = CommandType.StoredProcedure
                    sqlComm.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = sc.ArrivalDate
                    sqlComm.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = sc.DepartureDate
                    sqlComm.Parameters.Add("@DestinationID", SqlDbType.BigInt).Value = intDestinationID
                    sqlComm.Parameters.Add("@ResortID", SqlDbType.BigInt).Value = intResortID
                    sqlComm.Parameters.Add("@BoardTypeID", SqlDbType.Int).Value = intBoardTypeID
                    sqlComm.Parameters.Add("@Passengers", SqlDbType.Int).Value = CInt(sc.PassengerString)
                    sqlComm.Parameters.Add("@DoTotalPricing", SqlDbType.Int).Value = 1
                    sqlComm.Parameters.Add("@ValuateRoomID", SqlDbType.BigInt).Value = Convert.DBNull
                    sqlComm.Parameters.Add("@RetCacheID", SqlDbType.Char, 50)
                    sqlComm.Parameters("@RetCacheID").Direction = ParameterDirection.Output
                    sqlConn.Open()

                    Try
                        sqlComm.ExecuteNonQuery()
                        RetVal = sqlComm.Parameters("@RetCacheID").Value
                    Finally
                        sqlConn.Close()
                    End Try

                    Timings &= "|CS:" & DateTime.Now.Subtract(SearchStartTime).ToString

                    LogStr &= "Stored Procedure Run Completed : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf

                    '############################################################
                    ' PLACE FOR PRICING
                    '############################################################


                    If MultiThreadEnabled Then
                        If RetVal.Trim.Length > 0 Then

                            Dim strConn1 As String = Current.Application.Get("SearchCacheConnectionString")
                            Dim sqlConn1 As New SqlConnection(strConn1)
                            sqlConn1.Open()

                            Dim strSQL1 As String = "" & _
                                "SELECT * FROM tblGetRoomsAvailable WHERE CacheID = '" & RetVal & "'"
                            Dim sqlComm1 As New SqlCommand(strSQL1, sqlConn1)


                            Dim sqlDA1 As New SqlDataAdapter(sqlComm1)
                            Dim dt1 As New DataTable

                            sqlDA1.Fill(dt1)
                            sqlConn1.Close()


                            Dim TotalRowCount As Integer
                            TotalRowCount = dt1.Rows.Count
                            Dim Divider As Integer
                            Divider = Int(TotalRowCount / RoomsPerThread)

                            ' Here I divide the search results into 50 (parameter is in the begining of this function) 
                            ' Each thread will handle 50 calculations and begin simultaneously. 
                            ' After For Next Loop, "WaitAll" waits for all these threads to finish their work and 
                            ' Function will continue as single thread again. 
                            '  +----------------------------------+
                            '  | Available Rooms Returned         |
                            '  +----------------------------------+
                            '         /             |            \   
                            '     Price Calc    Price Calc    Price Calc
                            '         \             |            /
                            '          \            |           /
                            '  +----------------------------------+
                            '  | Calculated Result Set            |
                            '  +----------------------------------+      

                            Dim waitHandles As List(Of WaitHandle) = New List(Of WaitHandle)()

                            Dim caller As CalcHandler = New CalcHandler(AddressOf Calc)

                            For j As Integer = 0 To Divider + 1
                                Dim CP As New CalcParam
                                CP.DTBl = dt1
                                CP.StartingRow = j * RoomsPerThread
                                CP.CacheID = RetVal

                                Dim results As IAsyncResult = caller.BeginInvoke(CP, Nothing, Nothing)
                                waitHandles.Add(results.AsyncWaitHandle)
                            Next

                            WaitAll(waitHandles.ToArray())

                            Timings &= "|PC(" & TotalRowCount & "):" & DateTime.Now.Subtract(SearchStartTime).ToString

                        End If
                    Else


                        If RetVal.Trim.Length > 0 Then
                            Dim MyPrice As New PriceCalculationDLL.Price
                            Dim strConn1 As String = Current.Application.Get("SearchCacheConnectionString")
                            Dim sqlConn1 As New SqlConnection(strConn1)
                            sqlConn1.Open()


                            Dim strSQL1 As String = "" & _
                                "SELECT * FROM tblGetRoomsAvailable WHERE CacheID = '" & RetVal & "'"


                            Dim sqlComm1 As New SqlCommand(strSQL1, sqlConn1)

                            Dim sqlDA1 As New SqlDataAdapter(sqlComm1)
                            Dim dt1 As New DataTable

                            sqlDA1.Fill(dt1)
                            ' sqlConn1.Close()



                            For z As Integer = 0 To dt1.Rows.Count - 1
                                Dim TotalAdultDisc As Decimal = 0
                                Dim TotalChildDisc As Decimal = 0
                                Dim TotalAdultPrice As Decimal = 0
                                Dim TotalChildPrice As Decimal = 0
                                Dim TotalHandlingFee As Decimal = 0
                                For y As Integer = 1 To 5
                                    If CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) > 0 Then


                                        Dim MyDS As New Data.DataSet
                                        MyPrice.Connection = New SqlConnection(Current.Application.Get("ConnectionString"))

                                        MyDS = MyPrice.GetPrice(dt1.Rows(z).Item("RoomID"), Int(CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) / 10), CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) - (Int(CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) / 10) * 10), CDate(dt1.Rows(z).Item("FromDate")), CDate(dt1.Rows(z).Item("ToDate")), dt1.Rows(z).Item("BoardTypeID"), Now.Date)
                                        Dim ResTotal As New PriceCalculationDLL.Price.PriceTotals
                                        ResTotal = PriceCalculationDLL.Price.GetTotals(MyDS, True, False)


                                        TotalAdultPrice += ResTotal.TotalAdult
                                        TotalChildPrice += ResTotal.TotalChild
                                        TotalAdultDisc += ResTotal.DiscountTotal
                                        TotalChildDisc += 0
                                        TotalHandlingFee += ResTotal.HandlingFee

                                    End If
                                Next
                                Dim GrandTotal As Decimal = (TotalAdultPrice + TotalChildPrice + TotalHandlingFee + CDec(dt1.Rows(z).Item("TotalMandatoryAdultSupplement")) + CDec(dt1.Rows(z).Item("TotalMandatoryChildSupplement"))) - TotalAdultDisc
                                'Dim NetOTBLtdPrice As Decimal

                                'NetOTBLtdPrice = GrandTotal / 0.99

                                Dim SQLUpdate As String = "UPDATE tblGetRoomsAvailable SET HandlingFee = '" & TotalHandlingFee & "' , TotalAdultPrice = '" & TotalAdultPrice & "', TotalChildPrice = '" & TotalChildPrice & "', TotalAdultDiscount = '" & TotalAdultDisc & "', TotalChildDiscount = '" & TotalChildDisc & "', Total = '" & GrandTotal & "' WHERE CacheID = '" & RetVal & "' AND RoomID = " & dt1.Rows(z).Item("RoomID") & " AND BoardTypeID = " & dt1.Rows(z).Item("BoardTypeID")
              
                                Dim sqlCommUpd As New SqlCommand(SQLUpdate, sqlConn1)

                                sqlCommUpd.ExecuteNonQuery()
                                sqlCommUpd = Nothing

                            Next

                            If sqlConn1.State = ConnectionState.Open Then
                                sqlConn1.Close()
                            End If
                        End If
                    End If

                    LogStr &= "Pricing Completed: " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
                    sqlComm = Nothing
                    If sqlConn.State = ConnectionState.Open Then
                        sqlConn.Close()
                    End If
                    sqlConn = Nothing

                    ' Now query the cached table again
                    tblRooms = GetRoomsAvailable(sc.ArrivalDate, sc.DepartureDate, intDestinationID, _
                        intResortID, intBoardTypeID, CInt(sc.PassengerString))
                End If
            Else

            End If

            ' Process availability
            Try
                Dim blnAllowed As Boolean = True

                If blnAllowed Then
                    Dim strAccommodationIDs(-1) As String
                    If Not IsNothing(sc.AccommodationIDList) Then
                        strAccommodationIDs = sc.AccommodationIDList.Split(",")
                    End If

                    For i As Integer = 0 To tblRooms.Rows.Count - 1
                        blnAllowed = True

                        ' Check prices have been returned
                        If Not IsDBNull(tblRooms.Rows(i).Item("Total")) Then
                            ' -------------------------------------------
                            ' START: Addition by Cyber Media (15/05/2007)
                            ' -------------------------------------------
                            Dim accommID As String = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("SupplierAccommodationCode"))
                            Dim resortID As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("ResortID"))
                            Dim boardTypeID As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("BoardTypeID"))
                            Dim ratingCode As String = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("AccommodationCategoryName"))

                            If blnAllowed AndAlso Not Me.ResortCodesGloballyExcluded Is Nothing Then
                                ' Do not allow the room if the located resort is excluded at a global level
                                For intIndex As Integer = 0 To UBound(Me.ResortCodesGloballyExcluded)
                                    If Me.ResortCodesGloballyExcluded(intIndex).Equals(Convert.ToString(resortID)) Then
                                        blnAllowed = False
                                        Exit For
                                    End If
                                Next
                            End If

                            ' If accommodation IDs have been specified in the search, see if they match the ID of the current hotel
                            If blnAllowed AndAlso Not IsNothing(sc.AccommodationIDList) AndAlso sc.AccommodationIDList.Length > 0 Then
                                Dim intAccommodationID As Integer = CInt(tblRooms.Rows(i).Item("AccommodationID"))
                                blnAllowed = False
                                For intIndex As Integer = 0 To UBound(strAccommodationIDs)
                                    If IsNumeric(strAccommodationIDs(intIndex)) AndAlso CInt(strAccommodationIDs(intIndex)) = intAccommodationID Then
                                        ' The specified accommodation ID matches the ID of the current hotel
                                        blnAllowed = True
                                    End If
                                Next
                            End If

                            ' If accommodation names have been specified in the search, see if they match the name of the current hotel
                            If blnAllowed AndAlso sc.AccommodationNames.Count > 0 Then
                                Dim blnMatchName As Boolean = False
                                Dim strHotelName As String = LCase(Trim(SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("Name"))))

                                For Each strAccName As String In sc.AccommodationNames
                                    strAccName = LCase(Trim(strAccName))

                                    If strHotelName.Equals(strAccName) Then
                                        ' The specified accommodation name matches the title of the current hotel exactly
                                        blnMatchName = True
                                    ElseIf strHotelName.IndexOf(strAccName) > -1 Then
                                        ' The specified accommodation name is contained within the title of the current hotel
                                        blnMatchName = True
                                    Else
                                        ' Check if each keyword in the specified accommodation name is contained within the title of the current hotel
                                        Dim intKeywordMatchCount As Integer = 0
                                        Dim strAccNameKeywords() As String = strAccName.Split(" ")
                                        Dim intAccNameKeywordCount As Integer = strAccNameKeywords.Length

                                        For idx As Integer = 0 To UBound(strAccNameKeywords)
                                            If strHotelName.IndexOf(strAccNameKeywords(idx)) > -1 Then
                                                intKeywordMatchCount += 1
                                            End If
                                        Next

                                        If intAccNameKeywordCount > 0 AndAlso intKeywordMatchCount = intAccNameKeywordCount Then
                                            ' Each keyword in the specified accommodation name is contained within the title of the current hotel
                                            blnMatchName = True
                                        End If
                                    End If
                                Next

                                If Not blnMatchName Then
                                    ' The current room should not be added to the list of available rooms
                                    ' as the name of the hotel does not match any of the accommodation names specified in the search
                                    blnAllowed = False
                                End If
                            End If

                            ' Check the accommodation rating matches the accommodation rating selected by the user in the search form
                            If blnAllowed AndAlso Not Me.RatingCodes Is Nothing AndAlso Me.RatingCodes <> "ALL" _
                            AndAlso Not ratingCode Is Nothing AndAlso ratingCode <> String.Empty Then
                                blnAllowed = False
                                Dim rCodes() As String = Me.RatingCodes.Split(",")

                                For index As Integer = 0 To UBound(rCodes)
                                    If Not rCodes(index) Is Nothing AndAlso rCodes(index).ToUpper().Equals(ratingCode.ToUpper()) Then
                                        blnAllowed = True
                                    End If
                                Next
                            End If
                            ' ----------------------------
                            ' END: Addition by Cyber Media
                            ' ----------------------------

                            If blnAllowed Then
                                Dim oRoom As New BedBankRoom

                                With oRoom
                                    .Board = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("BoardTypeDescription"))
                                    .BoardCode = boardTypeID
                                    .BoardDescription = .Board    ' this was in the original!
                                    .HotelName = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("Name"))
                                    .Resort = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("Resort"))
                                    .DestinationCode = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("DestinationID"))
                                    .ResortCode = resortID
                                    .RoomCode = tblRooms.Rows(i).Item("RoomID")
                                    .RoomDescription = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("RoomDescription"))
                                    .RoomType = .RoomDescription   ' this was in the original!
                                    .FromDate = sc.ArrivalDate
                                    .ToDate = sc.DepartureDate
                                    .CategoryDescription = ratingCode
                                    .EDICode = accommID


                                    Dim intNumRooms As Integer = CInt(tblRooms.Rows(i).Item("NumRooms"))
                                    Dim intOccupancyID As Integer = CInt(SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("OccupancyID")))
                                    Dim intRoomAdults As Integer = CInt(Math.Floor(intOccupancyID / 10))
                                    Dim intRoomChildren As Integer = intOccupancyID - (10 * intRoomAdults)

                                    .OccupancyID = intOccupancyID
                                    .OccupancyPublic = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("OccupancyPublicText"))
                                    .OccupancyBackOffice = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("OccupancyBackOfficeText"))
                                    .RoomAdults = intRoomAdults
                                    .RoomChildren = intRoomChildren
                                    .RoomQuantity = intNumRooms
                                    .IsSelected = False

                                    Dim intPriceTypeID As String = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("PriceTypeID"))
                                    Dim intSpecialOffer As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("SpecialOffer"))
                                    Dim intHotPick As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("HotPick"))
                                    Dim intRequestOnly As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("RequestOnly"))
                                    Dim intCostTypeID As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("CostTypeID"))

                                    If intPriceTypeID = 0 Then
                                        ' sale price
                                        .AllowMarkups = False
                                    Else
                                        ' cost price (markups will apply)
                                        .AllowMarkups = True
                                    End If

                                    If intSpecialOffer = 1 Then
                                        .IsSpecialOffer = True
                                    Else
                                        .IsSpecialOffer = False
                                    End If

                                    If intHotPick = 1 Then
                                        .IsHotPick = True
                                    Else
                                        .IsHotPick = False
                                    End If

                                    If intRequestOnly = 1 Then
                                        .IsRequestOnly = True
                                    Else
                                        .IsRequestOnly = False
                                    End If

                                    ' Total is inclusive of handling fee, discounts and supplements
                                    Dim dblTotalPrice As Double = SqlCommandWrapper.HandleDBNullFloat(tblRooms.Rows(i).Item("TOTAL"))
                                    Dim supplierCurrencyCode As String = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("Currency"))

                                    ' Prices should be returned in the native currency, as is
                                    .SupplierPriceCurrency = supplierCurrencyCode
                                    .SupplierTotalPriceCurrency = supplierCurrencyCode
                                    .SupplierPrice = dblTotalPrice / sc.GetDurationOfStay()
                                    .SupplierTotalPrice = dblTotalPrice

                                    .HandlingFee = SqlCommandWrapper.HandleDBNullFloat(tblRooms.Rows(i).Item("HandlingFee"))

                                    If intCostTypeID = 1 OrElse intCostTypeID = 3 Then
                                        .PriceType = Enumerations.RoomPriceType.PerPerson
                                    ElseIf intCostTypeID = 2 OrElse intCostTypeID = 4 Then
                                        .PriceType = Enumerations.RoomPriceType.PerUnit
                                    Else
                                        .PriceType = Enumerations.RoomPriceType.UnSpecified
                                    End If
                                End With

                                oList.Add(oRoom)
                            End If
                        End If

                    Next i
                End If
            Catch ex As Exception

            End Try

            If Infant > 0 Then

                'INVALIDATE CACHE

                Dim strSQL As String = "" & _
                    "UPDATE tblGetRoomsAvailableCache SET " & _
                    "CacheIsInvalidated = 'Y', CacheEndDate = GETDATE() " & _
                    "WHERE CacheID = '" & RetVal & "'"
                Dim CacheConn As New SqlConnection(CacheConnStr)
                CacheConn.Open()

                Dim sqlCommUpd As New SqlCommand(strSQL, CacheConn)

                sqlCommUpd.ExecuteNonQuery()
                sqlCommUpd = Nothing
                CacheConn.Close()
                CacheConn.Dispose()
                CacheConn = Nothing
            End If


            If RetVal.Trim.Length > 0 Then
                Timings &= "|SC:" & DateTime.Now.Subtract(SearchStartTime).ToString
                DBTools.SQLRuninCache("UPDATE tblGetRoomsAvailableCache SET SearchDuration = '" & DateTime.Now.Subtract(SearchStartTime).ToString & "', SearchLog='" & Timings & "' WHERE CacheID ='" & RetVal & "'")
            End If
            LogStr &= "Search Completed : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
            'DBTools.SQLRun("INSERT INTO AliLog (LogTxt) VALUES ('" & LogStr & "')")

            ' Return availability
            Return oList
        End Function

        Public Class CalcParam
            Public DTBl As Data.DataTable
            Public StartingRow As Integer
            Public CacheID As String
        End Class

        Private Sub Calc(IncomingParam As CalcParam)
            Dim RoomsPerTread As Integer = 50

            Dim dt1 As Data.DataTable
            dt1 = IncomingParam.DTBl
            Dim StartingRow As Integer
            StartingRow = IncomingParam.StartingRow
            Dim Retval As String = IncomingParam.CacheID
            'SQLRun("INSERT INTO AliLog (LogTxt)  VALUES ( ' Calc Started " & Now.ToLongTimeString & " " & Now.Millisecond & "')")

            Dim Infant As Integer = 0




            For z As Integer = StartingRow To dt1.Rows.Count - 1
                If z = StartingRow + RoomsPerTread Then
                    Exit For
                End If
                Dim TotalAdultDisc As Decimal = 0
                Dim TotalChildDisc As Decimal = 0
                Dim TotalAdultPrice As Decimal = 0
                Dim TotalChildPrice As Decimal = 0
                Dim TotalHandlingFee As Decimal = 0

                If Not IsNothing(Session("InfantQty")) Then
                    'ALI    
                    Infant = CInt(Session("InfantQty"))
                End If

                Dim ConvertedChildren As Integer = 0
                ConvertedChildren = (Int(CInt(dt1.Rows(z).Item("Room1Occupancy")) / 10) + Int(CInt(dt1.Rows(z).Item("Room2Occupancy")) / 10) + Int(CInt(dt1.Rows(z).Item("Room3Occupancy")) / 10) + Int(CInt(dt1.Rows(z).Item("Room4Occupancy")) / 10) + Int(CInt(dt1.Rows(z).Item("Room5Occupancy")) / 10)) - Int(CInt(dt1.Rows(z).Item("OccupancyID")) / 10)

                If Infant > 0 Then
                    Infant = Infant + ConvertedChildren
                End If

                For y As Integer = 1 To 5
                    If CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) > 0 Then


                        Dim MyDS As New Data.DataSet

                        Dim MyPrice As New PriceCalculationDLL.Price
                        Dim DBConn As New SqlConnection(DBConnStr) '  New Data.SqlClient.SqlConnection(Current.Application.Get("ConnectionString"))
                        'If DBConn.State <> ConnectionState.Open Then
                        '    DBConn.Open()
                        'End If

                        MyPrice.Connection = DBConn

                        If Infant > 0 Then
                            If CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) - (Int(CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) / 10) * 10) >= Infant Then
                                dt1.Rows(z).Item("Room" & y & "Occupancy") = CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) - Infant
                                Infant = 0
                            Else
                                Dim RoomChild As Integer
                                RoomChild = (CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) - (Int(CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) / 10) * 10))
                                dt1.Rows(z).Item("Room" & y & "Occupancy") = Int(CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) / 10) * 10
                                Infant = Infant - RoomChild
                            End If

                        End If




                        MyDS = MyPrice.GetPrice(dt1.Rows(z).Item("RoomID"), Int(CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) / 10), CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) - (Int(CInt(dt1.Rows(z).Item("Room" & y & "Occupancy")) / 10) * 10), CDate(dt1.Rows(z).Item("FromDate")), CDate(dt1.Rows(z).Item("ToDate")), dt1.Rows(z).Item("BoardTypeID"), Now.Date)

                        Dim ResTotal As New PriceCalculationDLL.Price.PriceTotals
                        ResTotal = PriceCalculationDLL.Price.GetTotals(MyDS, True, False)

                        TotalAdultPrice += ResTotal.TotalAdult
                        TotalChildPrice += ResTotal.TotalChild
                        TotalAdultDisc += ResTotal.DiscountTotal
                        TotalChildDisc += 0
                        TotalHandlingFee += ResTotal.HandlingFee
                        MyPrice = Nothing
                        ' DBConn.Close()

                    End If
                Next
                Dim GrandTotal As Decimal = (TotalAdultPrice + TotalChildPrice + TotalHandlingFee + CDec(dt1.Rows(z).Item("TotalMandatoryAdultSupplement")) + CDec(dt1.Rows(z).Item("TotalMandatoryChildSupplement"))) - TotalAdultDisc
                'Dim NetOTBLtdPrice As Decimal

                'NetOTBLtdPrice = GrandTotal / 0.99
                Dim CacheConn As New SqlConnection(CacheConnStr)
                CacheConn.Open()
                Dim SQLUpdate As String = "UPDATE tblGetRoomsAvailable SET HandlingFee = '" & TotalHandlingFee & "' , TotalAdultPrice = '" & TotalAdultPrice & "', TotalChildPrice = '" & TotalChildPrice & "', TotalAdultDiscount = '" & TotalAdultDisc & "', TotalChildDiscount = '" & TotalChildDisc & "', Total = '" & GrandTotal & "' WHERE CacheID = '" & Retval & "' AND RoomID = " & dt1.Rows(z).Item("RoomID") & " AND BoardTypeID = " & dt1.Rows(z).Item("BoardTypeID")
                Dim sqlCommUpd As New SqlCommand(SQLUpdate, CacheConn)

                sqlCommUpd.ExecuteNonQuery()
                sqlCommUpd = Nothing
                CacheConn.Close()
                CacheConn.Dispose()
                CacheConn = Nothing
                'SQLRun("INSERT INTO AliLog (LogTxt)  VALUES ( 'Calc End " & Now.ToLongTimeString & " " & Now.Millisecond & "')")
            Next
        End Sub
        ' ---------------------------
        ' END: Standard search method
        ' ---------------------------

        ' ----------------------------------
        ' START: Asynchronous search methods
        ' ----------------------------------
        Public Sub StartSearch(ByRef sc As SearchCriteria) Implements IAsyncSupplier.StartSearch
            If sc.DestinationID.Length = 0 Then
                ' Do not start the search if the destination has not been specified
                Me._AllowSearch = False
                Exit Sub
            ElseIf Not Me.DestinationCodesGloballyExcluded Is Nothing Then
                ' Do not start the search if the specified destination is excluded at a global level
                For intIndex As Integer = 0 To UBound(Me.DestinationCodesGloballyExcluded)
                    If LCase(Me.DestinationCodesGloballyExcluded(intIndex)).Equals(LCase(sc.DestinationID)) Then
                        Me._AllowSearch = False
                        Exit Sub
                    End If
                Next
            End If

            If sc.ResortID.Length > 0 Then
                If Not Me.ResortCodesGloballyExcluded Is Nothing Then
                    ' Do not start the search if the specified resort is excluded at a global level
                    For intIndex As Integer = 0 To UBound(Me.ResortCodesGloballyExcluded)
                        If LCase(Me.ResortCodesGloballyExcluded(intIndex)).Equals(LCase(sc.ResortID)) Then
                            Me._AllowSearch = False
                            Exit Sub
                        End If
                    Next
                End If
            End If

            If Not sc.RatingType Is Nothing AndAlso IsNumeric(sc.RatingType) AndAlso CInt(sc.RatingType) <> 0 Then
                Dim params() As Object = {Convert.ToInt32(sc.RatingType)}
                Dim DS As DataSet = SqlHelper.ExecuteDataset(ServerApplicationState("SearchConnectionString"), "usp_Engine_Bedbank_GetRatingCodes_NEW", params)

                Me.RatingCodes = String.Empty
                If Not DS Is Nothing AndAlso Not DS.Tables Is Nothing AndAlso DS.Tables.Count > 0 Then
                    If Not DS.Tables(0) Is Nothing AndAlso Not DS.Tables(0).Rows Is Nothing AndAlso DS.Tables(0).Rows.Count > 0 Then
                        If Not DS.Tables(0).Rows(0)("BedbankCodes") Is Nothing Then
                            Me.RatingCodes = Convert.ToString(DS.Tables(0).Rows(0)("BedbankCodes"))
                        End If
                    End If
                End If

                If Me.RatingCodes.Length = 0 Then
                    Me._AllowSearch = False
                    Exit Sub
                End If
            Else
                Me.RatingCodes = "ALL"
            End If

            ' async web service
            bbc = New BedbankWebService.BedBankContext
            bbc.Credentials = System.Net.CredentialCache.DefaultCredentials

            Dim req As New BedbankWebService.SearchRequest

            With req
                .intTimeOut = Convert.ToInt32(ConfigurationManager.AppSettings("Engine_Bedbank_Timeout"))
                .intSupplierId = Enumerations.Supplier.BedBank
                .intWebSiteId = ConfigurationManager.AppSettings("WEBSITE_ID")
                .strSessionId = stWebSessionId
                .dteArrivalDate = sc.ArrivalDate
                .dteDepartureDate = sc.DepartureDate
                .strDestinationId = sc.DestinationID
                .strResortId = sc.ResortID
                .strBoardTypeId = sc.BoardType
                .intPassengerString = sc.PassengerString
            End With

            Me.iasBB = bbc.BeginSearch(req, Nothing, Nothing)
        End Sub

        Public Function EndSearch(ByRef sc As SearchCriteria) As RoomList Implements IAsyncSupplier.EndSearch
            Dim SearchStartTime As DateTime = DateTime.Now
            Dim ds As New DataSet
            Dim oList As New RoomList
            Dim Timings As String = ""

            Try
                Dim res As BedbankWebService.SearchResponse = bbc.EndSearch(iasBB)
                If IsNothing(res) OrElse IsNothing(res.xdResponse) Then
                    Return oList
                End If

                Dim nr As New XmlNodeReader(res.xdResponse)
                ds.ReadXml(nr)

                ' Obtain the destination ID specified in the search
                Dim destID As Integer = -1
                If Not sc.DestinationID Is Nothing AndAlso IsNumeric(sc.DestinationID) Then
                    destID = Convert.ToInt32(sc.DestinationID)
                End If

                Dim blnAllowed As Boolean = True
                Dim tblRooms As DataTable = ds.Tables(0)

                If blnAllowed Then
                    Dim strAccommodationIDs(-1) As String
                    If Not IsNothing(sc.AccommodationIDList) Then
                        strAccommodationIDs = sc.AccommodationIDList.Split(",")
                    End If

                    For i As Integer = 0 To tblRooms.Rows.Count - 1
                        blnAllowed = True

                        ' -------------------------------------------
                        ' START: Addition by Cyber Media (15/05/2007)
                        ' -------------------------------------------
                        Dim accommID As String = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("SupplierAccommodationCode"))
                        Dim resortID As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("ResortID"))
                        Dim boardTypeID As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("BoardTypeID"))
                        Dim ratingCode As String = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("AccommodationCategoryName"))

                        If blnAllowed AndAlso Not Me.ResortCodesGloballyExcluded Is Nothing Then
                            ' Do not allow the room if the located resort is excluded at a global level
                            For intIndex As Integer = 0 To UBound(Me.ResortCodesGloballyExcluded)
                                If Me.ResortCodesGloballyExcluded(intIndex).Equals(Convert.ToString(resortID)) Then
                                    blnAllowed = False
                                    Exit For
                                End If
                            Next
                        End If

                        ' If accommodation IDs have been specified in the search, see if they match the ID of the current hotel
                        If blnAllowed AndAlso Not IsNothing(sc.AccommodationIDList) AndAlso sc.AccommodationIDList.Length > 0 Then
                            Dim intAccommodationID As Integer = CInt(tblRooms.Rows(i).Item("AccommodationID"))
                            blnAllowed = False
                            For intIndex As Integer = 0 To UBound(strAccommodationIDs)
                                If IsNumeric(strAccommodationIDs(intIndex)) AndAlso CInt(strAccommodationIDs(intIndex)) = intAccommodationID Then
                                    ' The specified accommodation ID matches the ID of the current hotel
                                    blnAllowed = True
                                End If
                            Next
                        End If

                        ' If accommodation names have been specified in the search, see if they match the name of the current hotel
                        If blnAllowed AndAlso sc.AccommodationNames.Count > 0 Then
                            Dim blnMatchName As Boolean = False
                            Dim strHotelName As String = LCase(Trim(SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("Name"))))

                            For Each strAccName As String In sc.AccommodationNames
                                strAccName = LCase(Trim(strAccName))

                                If strHotelName.Equals(strAccName) Then
                                    ' The specified accommodation name matches the title of the current hotel exactly
                                    blnMatchName = True
                                ElseIf strHotelName.IndexOf(strAccName) > -1 Then
                                    ' The specified accommodation name is contained within the title of the current hotel
                                    blnMatchName = True
                                Else
                                    ' Check if each keyword in the specified accommodation name is contained within the title of the current hotel
                                    Dim intKeywordMatchCount As Integer = 0
                                    Dim strAccNameKeywords() As String = strAccName.Split(" ")
                                    Dim intAccNameKeywordCount As Integer = strAccNameKeywords.Length

                                    For idx As Integer = 0 To UBound(strAccNameKeywords)
                                        If strHotelName.IndexOf(strAccNameKeywords(idx)) > -1 Then
                                            intKeywordMatchCount += 1
                                        End If
                                    Next

                                    If intAccNameKeywordCount > 0 AndAlso intKeywordMatchCount = intAccNameKeywordCount Then
                                        ' Each keyword in the specified accommodation name is contained within the title of the current hotel
                                        blnMatchName = True
                                    End If
                                End If
                            Next

                            If Not blnMatchName Then
                                ' The current room should not be added to the list of available rooms
                                ' as the name of the hotel does not match any of the accommodation names specified in the search
                                blnAllowed = False
                            End If
                        End If

                        ' Check the accommodation rating matches the accommodation rating selected by the user in the search form
                        If blnAllowed AndAlso Not Me.RatingCodes Is Nothing AndAlso Me.RatingCodes <> "ALL" _
                        AndAlso Not ratingCode Is Nothing AndAlso ratingCode <> String.Empty Then
                            blnAllowed = False
                            Dim rCodes() As String = Me.RatingCodes.Split(",")

                            For index As Integer = 0 To UBound(rCodes)
                                If Not rCodes(index) Is Nothing AndAlso rCodes(index).ToUpper().Equals(ratingCode.ToUpper()) Then
                                    blnAllowed = True
                                End If
                            Next
                        End If
                        ' ----------------------------
                        ' END: Addition by Cyber Media
                        ' ----------------------------

                        If blnAllowed Then
                            Dim oRoom As New BedBankRoom

                            With oRoom
                                .Board = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("BoardTypeDescription"))
                                .BoardCode = boardTypeID
                                .BoardDescription = .Board    ' this was in the original!
                                .HotelName = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("Name"))
                                .Resort = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("Resort"))
                                .DestinationCode = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("DestinationID"))
                                .ResortCode = resortID
                                .RoomCode = tblRooms.Rows(i).Item("RoomID")
                                .RoomDescription = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("RoomDescription"))
                                .RoomType = .RoomDescription   ' this was in the original!
                                .FromDate = sc.ArrivalDate
                                .ToDate = sc.DepartureDate
                                .CategoryDescription = ratingCode
                                .EDICode = accommID

                                Dim intNumRooms As Integer = CInt(tblRooms.Rows(i).Item("NumRooms"))
                                Dim intOccupancyID As Integer = CInt(SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("OccupancyID")))
                                Dim intRoomAdults As Integer = CInt(Math.Floor(intOccupancyID / 10))
                                Dim intRoomChildren As Integer = intOccupancyID - (10 * intRoomAdults)

                                .OccupancyID = intOccupancyID
                                .OccupancyPublic = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("OccupancyPublicText"))
                                .OccupancyBackOffice = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("OccupancyBackOfficeText"))
                                .RoomAdults = intRoomAdults
                                .RoomChildren = intRoomChildren
                                .RoomQuantity = intNumRooms
                                .IsSelected = False

                                Dim intPriceTypeID As String = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("PriceTypeID"))
                                Dim intSpecialOffer As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("SpecialOffer"))
                                Dim intHotPick As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("HotPick"))
                                Dim intRequestOnly As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("RequestOnly"))
                                Dim intCostTypeID As Integer = SqlCommandWrapper.HandleDBNullInt(tblRooms.Rows(i).Item("CostTypeID"))

                                If intPriceTypeID = 0 Then
                                    ' sale price
                                    .AllowMarkups = False
                                Else
                                    ' cost price (markups will apply)
                                    .AllowMarkups = True
                                End If

                                If intSpecialOffer = 1 Then
                                    .IsSpecialOffer = True
                                Else
                                    .IsSpecialOffer = False
                                End If

                                If intHotPick = 1 Then
                                    .IsHotPick = True
                                Else
                                    .IsHotPick = False
                                End If

                                If intRequestOnly = 1 Then
                                    .IsRequestOnly = True
                                Else
                                    .IsRequestOnly = False
                                End If

                                ' Total is inclusive of handling fee, discounts and supplements
                                Dim dblTotalPrice As Double = SqlCommandWrapper.HandleDBNullFloat(tblRooms.Rows(i).Item("TOTAL"))
                                Dim supplierCurrencyCode As String = SqlCommandWrapper.HandleDBNullString(tblRooms.Rows(i).Item("Currency"))

                                ' Prices should be returned in the native currency, as is
                                .SupplierPriceCurrency = supplierCurrencyCode
                                .SupplierTotalPriceCurrency = supplierCurrencyCode
                                .SupplierPrice = dblTotalPrice / sc.GetDurationOfStay()
                                .SupplierTotalPrice = dblTotalPrice

                                .HandlingFee = SqlCommandWrapper.HandleDBNullFloat(tblRooms.Rows(i).Item("HandlingFee"))

                                If intCostTypeID = 1 OrElse intCostTypeID = 3 Then
                                    .PriceType = Enumerations.RoomPriceType.PerPerson
                                ElseIf intCostTypeID = 2 OrElse intCostTypeID = 4 Then
                                    .PriceType = Enumerations.RoomPriceType.PerUnit
                                Else
                                    .PriceType = Enumerations.RoomPriceType.UnSpecified
                                End If
                            End With

                            oList.Add(oRoom)
                        End If

                    Next i
                End If
            Catch ex As Exception

            End Try
             
            Return oList
        End Function
        ' --------------------------------
        ' END: Asynchronous search methods
        ' --------------------------------

        Private Function GetBedbankRatingCodesFromID(ByVal ratingID As Integer) As String()
            Dim strRatingCodes(-1) As String
            Dim params() As Object = {ratingID}

            Dim obj As Object = SqlHelper.ExecuteScalar(ServerApplicationState("ConnectionString"), "usp_Engine_Bedbank_GetRatingCodes_NEW", params)

            If Not obj Is Nothing Then
                Try
                    Dim strRatingCodeList As String = CType(obj, String)
                    strRatingCodeList = strRatingCodeList.Trim()

                    If strRatingCodeList.EndsWith(",") Then
                        strRatingCodeList = strRatingCodeList.Substring(0, Len(strRatingCodeList) - 1)
                    End If

                    If Len(strRatingCodeList) > 0 Then
                        strRatingCodes = strRatingCodeList.Split(",")
                    End If
                Catch ex As Exception

                End Try
            End If

            Return strRatingCodes
        End Function

#End Region


#Region " Valuate "

        Public Function ValuateRoom(ByRef sc As SearchCriteria, ByVal intResortID As Integer, _
        ByVal intRoomID As Integer, ByVal intBoardTypeID As Integer) As BaseRoom Implements IAsyncSupplier.ValuateRoom

            Dim strConn As String = Current.Application.Get("ConnectionString")

            ' Cyber Media Edit (08/08/2011): The new usp_GetRoomsAvailable stored procedure is capable of working out
            ' how many rooms are required based on the number of adults and children specified
            Dim sqlConn As New SqlConnection(strConn)
            Dim sqlComm As New SqlCommand
            sqlComm.Connection = sqlConn
            sqlComm.CommandType = CommandType.StoredProcedure
            sqlComm.CommandText = "usp_GetRoomsAvailable"
            sqlComm.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = sc.ArrivalDate
            sqlComm.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = sc.DepartureDate
            sqlComm.Parameters.Add("@DestinationID", SqlDbType.Int).Value = sc.DestinationID
            sqlComm.Parameters.Add("@ResortID", SqlDbType.Int).Value = intResortID
            sqlComm.Parameters.Add("@BoardTypeID", SqlDbType.Int).Value = intBoardTypeID
            Dim infant As Integer = 0
            If Not IsNothing(Session("InfantQty")) Then
                'ALI    
                infant = Session("InfantQty")
            End If
            sc.PassengerString = CStr(CInt(sc.PassengerString) - infant)

            sqlComm.Parameters.Add("@Passengers", SqlDbType.Int).Value = sc.PassengerString
            sqlComm.Parameters.Add("@DoTotalPricing", SqlDbType.Int).Value = 1
            sqlComm.Parameters.Add("@ValuateRoomID", SqlDbType.BigInt).Value = intRoomID

            Dim sqlDA As New SqlDataAdapter(sqlComm)
            Dim dtRoom As New DataTable
            sqlDA.Fill(dtRoom)
            sqlDA = Nothing
            sqlComm = Nothing
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
            sqlConn = Nothing

            ' if the room to valuate is not available (often due to choosing more than 1 of it)
            ' return an unbookable room
            If dtRoom.Rows.Count = 0 Then
                Return New BedBankRoom(True, False)
            End If

            Dim dr As DataRow = dtRoom.Rows(0)

            If (CInt(dr.Item("QtyAvailable")) - CInt(dr.Item("NumRooms"))) < 0 Then
                Return New BedBankRoom(True, False)
            End If

            Dim intNumNights As Integer = CInt(dr.Item("NumNights"))
            Dim strCurrencyCode As String = CStr(dr.Item("Currency"))
            Dim intCurrencyID As Integer = -1
            Select Case strCurrencyCode
                Case "GBP"
                    intCurrencyID = CurrencyConverter.Currency.GBP
                Case "EUR"
                    intCurrencyID = CurrencyConverter.Currency.EUR
                Case "USD"
                    intCurrencyID = CurrencyConverter.Currency.USD
            End Select

            Dim intAdultDiscountFirstMatchID As Integer = -1
            If Not IsDBNull(dr.Item("AdultDiscountFirstMatchID")) Then
                intAdultDiscountFirstMatchID = CInt(dr.Item("AdultDiscountFirstMatchID"))
            End If
            Dim intChildDiscountFirstMatchID As Integer = -1
            If Not IsDBNull(dr.Item("ChildDiscountFirstMatchID")) Then
                intChildDiscountFirstMatchID = CInt(dr.Item("ChildDiscountFirstMatchID"))
            End If

            Dim intNumRooms As Integer = CInt(dr.Item("NumRooms"))
            Dim dblNetCostTotal As Double = CDbl(dr.Item("Total"))
            '-- TOTAL 
            Dim MyPrice As New PriceCalculationDLL.Price


            ' Cyber Media Edit (08/08/2011): The new usp_GetRoomsAvailable stored procedure is capable of working out
            ' how many rooms are required based on the number of adults and children specified
            Dim TotalAdultDisc As Decimal = 0
            Dim TotalChildDisc As Decimal = 0
            Dim TotalAdultPrice As Decimal = 0
            Dim TotalChildPrice As Decimal = 0
            Dim TotalHandlingFee As Decimal = 0

            MyPrice.Connection = New SqlConnection(Current.Application.Get("ConnectionString"))
            For y As Integer = 1 To 5
                If CInt(dr.Item("Room" & y & "Occupancy")) > 0 Then
                    Dim MyDS As New Data.DataSet
                    MyPrice.Connection = New SqlConnection(Current.Application.Get("ConnectionString"))
                    MyDS = MyPrice.GetPrice(dr.Item("RoomID"), Int(CInt(dr.Item("Room" & y & "Occupancy")) / 10), CInt(dr.Item("Room" & y & "Occupancy")) - (Int(CInt(dr.Item("Room" & y & "Occupancy")) / 10) * 10), CDate(sc.ArrivalDate), CDate(sc.DepartureDate), intBoardTypeID, Now.Date)
                    Dim ResTotal As New PriceCalculationDLL.Price.PriceTotals
                    ResTotal = PriceCalculationDLL.Price.GetTotals(MyDS, True, True)

                    TotalAdultPrice += ResTotal.TotalAdult
                    TotalChildPrice += ResTotal.TotalChild
                    TotalAdultDisc += ResTotal.DiscountTotal
                    TotalChildDisc += 0
                    TotalHandlingFee += ResTotal.HandlingFee

                End If
            Next
            Dim GrandTotal As Decimal = (TotalAdultPrice + TotalChildPrice + TotalHandlingFee + CDec(dr.Item("TotalMandatoryAdultSupplement")) + CDec(dr.Item("TotalMandatoryChildSupplement"))) - TotalAdultDisc

            'Dim MyDS As New Data.DataSet
            'MyDS = MyPrice.GetPrice(intRoomID, Int(CInt(sc.PassengerString) / 10), CInt(sc.PassengerString) - (Int(CInt(sc.PassengerString) / 10) * 10), sc.ArrivalDate, sc.DepartureDate, intBoardTypeID, Now.Date)
            'dblNetCostTotal = PriceCalculationDLL.Price.GetTotals(MyDS, True, True).GrandTotal + CDbl(dr.Item("TotalMandatoryAdultSupplement")) + CDbl(dr.Item("TotalMandatoryChildSupplement"))
            dblNetCostTotal = GrandTotal

            ' ----------------------------------------------------
            ' START: Cache availability (used at point of booking)
            ' ----------------------------------------------------
            Dim intAvailabilityCacheID As Integer = AvailabilityCache.InsertAvailability(CDbl(dr.Item("HandlingFee")), _
            CDbl(dr.Item("TotalAdultPrice")), CDbl(dr.Item("TotalChildPrice")), _
            CDbl(dr.Item("TotalAdultDiscount")), CDbl(dr.Item("TotalChildDiscount")), _
            CDbl(dr.Item("TotalMandatoryAdultSupplement")), CDbl(dr.Item("TotalMandatoryChildSupplement")), _
            dblNetCostTotal, intCurrencyID, intAdultDiscountFirstMatchID, intChildDiscountFirstMatchID)

            sc.AvailabilityCacheID = intAvailabilityCacheID
            ' --------------------------------------------------
            ' END: Cache availability (used at point of booking)
            ' --------------------------------------------------

            Dim oBBRoom As New BedBankRoom(True, True)
            oBBRoom.Board = SqlCommandWrapper.HandleDBNullString(dr.Item("BoardTypeDescription"))
            oBBRoom.BoardCode = CInt(dr.Item("BoardTypeID"))
            oBBRoom.BoardDescription = oBBRoom.Board    ' this was in the original!
            oBBRoom.HotelName = SqlCommandWrapper.HandleDBNullString(dr.Item("Name"))
            oBBRoom.Resort = SqlCommandWrapper.HandleDBNullString(dr.Item("Resort"))
            oBBRoom.DestinationCode = SqlCommandWrapper.HandleDBNullInt(dr.Item("DestinationID"))
            oBBRoom.ResortCode = SqlCommandWrapper.HandleDBNullInt(dr.Item("ResortID"))
            oBBRoom.RoomCode = dr.Item("RoomID")
            oBBRoom.RoomDescription = SqlCommandWrapper.HandleDBNullString(dr.Item("RoomDescription"))
            oBBRoom.RoomType = oBBRoom.RoomDescription   ' this was in the original!
            oBBRoom.FromDate = sc.ArrivalDate
            oBBRoom.ToDate = sc.DepartureDate
            oBBRoom.Category = SqlCommandWrapper.HandleDBNullString(dr.Item("AccommodationCategoryID"))
            oBBRoom.CategoryDescription = SqlCommandWrapper.HandleDBNullString(dr.Item("AccommodationCategoryName"))
            oBBRoom.EDICode = dr.Item("AccommodationID")

            Dim intOccupancyID As Integer = CInt(SqlCommandWrapper.HandleDBNullString(dr.Item("OccupancyID")))
            Dim intRoomAdults As Integer = CInt(Math.Floor(intOccupancyID / 10))
            Dim intRoomChildren As Integer = intOccupancyID - (10 * intRoomAdults)

            oBBRoom.OccupancyID = CStr(dr.Item("OccupancyID"))
            oBBRoom.OccupancyPublic = CStr(dr.Item("OccupancyPublicText"))
            oBBRoom.OccupancyBackOffice = CStr(dr.Item("OccupancyBackOfficeText"))
            oBBRoom.RoomAdults = intRoomAdults
            oBBRoom.RoomChildren = intRoomChildren
            oBBRoom.RoomQuantity = intNumRooms
            oBBRoom.IsSelected = False

            Dim intPriceTypeID As String = SqlCommandWrapper.HandleDBNullInt(dr.Item("PriceTypeID"))
            Dim intSpecialOffer As Integer = SqlCommandWrapper.HandleDBNullInt(dr.Item("SpecialOffer"))
            Dim intHotPick As Integer = SqlCommandWrapper.HandleDBNullInt(dr.Item("HotPick"))
            Dim intRequestOnly As Integer = SqlCommandWrapper.HandleDBNullInt(dr.Item("RequestOnly"))
            Dim intCostTypeID As Integer = SqlCommandWrapper.HandleDBNullInt(dr.Item("CostTypeID"))

            If intPriceTypeID = 0 Then
                ' sale price
                oBBRoom.AllowMarkups = False
            Else
                ' cost price (markups will apply)
                oBBRoom.AllowMarkups = True
            End If

            If intSpecialOffer = 1 Then
                oBBRoom.IsSpecialOffer = True
            Else
                oBBRoom.IsSpecialOffer = False
            End If

            If intHotPick = 1 Then
                oBBRoom.IsHotPick = True
            Else
                oBBRoom.IsHotPick = False
            End If

            If intRequestOnly = 1 Then
                oBBRoom.IsRequestOnly = True
            Else
                oBBRoom.IsRequestOnly = False
            End If

            ' Total is inclusive of handling fee, discounts and supplements
            Dim dblTotalPrice As Double = SqlCommandWrapper.HandleDBNullFloat(dr.Item("TOTAL"))
            Dim supplierCurrencyCode As String = SqlCommandWrapper.HandleDBNullString(dr.Item("Currency"))

            ' Prices should be returned in the native currency, as is
            oBBRoom.SupplierPriceCurrency = strCurrencyCode
            oBBRoom.SupplierTotalPriceCurrency = strCurrencyCode
            oBBRoom.SupplierPrice = dblNetCostTotal / sc.GetDurationOfStay()
            oBBRoom.SupplierTotalPrice = dblNetCostTotal

            oBBRoom.HandlingFee = SqlCommandWrapper.HandleDBNullFloat(dr.Item("HandlingFee"))

            If intCostTypeID = 1 OrElse intCostTypeID = 3 Then
                oBBRoom.PriceType = Enumerations.RoomPriceType.PerPerson
            ElseIf intCostTypeID = 2 OrElse intCostTypeID = 4 Then
                oBBRoom.PriceType = Enumerations.RoomPriceType.PerUnit
            Else
                oBBRoom.PriceType = Enumerations.RoomPriceType.UnSpecified
            End If

            ' now lets stuff on optional supplements if needed
            Dim s() As Supplement = Supplement.GetOptionalSupplements(oBBRoom.RoomCode, oBBRoom.BoardCode, sc.ArrivalDate, sc.DepartureDate)

            If Not s Is Nothing AndAlso s.Length > 0 Then
                ReDim oBBRoom.OptionalSupplements(s.Length - 1)
                For i As Integer = 0 To s.Length - 1
                    oBBRoom.OptionalSupplements(i) = GetOptionalSupplement(oBBRoom, s(i))
                Next
            End If

            Return oBBRoom
        End Function 'ValuateRoom

        Private Function GetOptionalSupplement(ByVal r As BaseRoom, ByVal s As Supplement) As OptionalSupplement
            Dim bbbs As New BedBankBaseSupplement(SettingReader.GetConnectionString(), r, s)
            Dim decSupplementPriceEUR As Decimal = bbbs.GetSupplementPrice()
            Dim decSupplementPriceGBP As Decimal = decSupplementPriceEUR * CurrencyConverter.GetCurrencyMultiplier(SettingReader.GetConnectionString(), 2, 1, r.FromDate)
            Dim os As OptionalSupplement = New OptionalSupplement(Enumerations.Supplier.BedBank, decSupplementPriceEUR, decSupplementPriceGBP, s.SuppDesc)
            os("supplementID") = s.SupplementID

            Return os
        End Function

#End Region


#Region " Book "

        Private Function GetRoomOccupancies(ByVal intOccupancyID As Integer) As DataTable
            Dim strSQL As String = "" & _
                "SELECT Room1Occupancy, Room2Occupancy, Room3Occupancy, " & _
                "Room4Occupancy, Room5Occupancy FROM tblOccupancyNEW " & _
                "WHERE OccupancyID = @OccupancyID;"

            Dim sqlConn As New SqlConnection(Current.Application.Get("ConnectionString"))
            Dim sqlComm As New SqlCommand(strSQL, sqlConn)
            sqlComm.Parameters.Add("@OccupancyID", SqlDbType.Int).Value = intOccupancyID
            Dim sqlDA As New SqlDataAdapter(sqlComm)
            Dim dtOccupancies As New DataTable
            sqlDA.Fill(dtOccupancies)
            sqlDA = Nothing
            sqlComm = Nothing
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
            sqlConn = Nothing


            Return dtOccupancies
        End Function

        Public Function BookRooms(ByRef sc As SearchCriteria, ByVal wc As WebCustomer, ByVal brStandard As BaseRoom) As BookRoomDetailsList Implements IAsyncSupplier.BookRooms
            Dim LogStr As String = ""
            LogStr &= "BookRooms Start : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
            Dim intDestinationID As Integer = -1
            Dim brdListForBooking As New BookRoomDetailsList(wc)
            Dim sqlConn As New SqlConnection(Generic.GetConnectionString())
            sqlConn.Open()
            Dim sqlTrans As SqlTransaction = sqlConn.BeginTransaction()
            Dim oCmd As SqlCommandWrapper
            LogStr &= "BookRooms 1 : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
            Try
                Dim brCurrent As BaseRoom
                Dim intRoomBookingID As Integer
                Dim intRoomOccupancyID As Integer
                Dim dteStart As DateTime = brStandard.FromDate
                Dim dteEnd As DateTime = brStandard.ToDate
                intDestinationID = Convert.ToInt32(brStandard.DestinationCode)
                Dim intRoomID As Integer = Convert.ToInt32(brStandard.RoomCode)
                Dim intBoardTypeID As Integer = Convert.ToInt32(brStandard.BoardCode)
                Dim intNumRooms As Integer = brStandard.RoomQuantity
                Dim intOccupancyID As Integer = (10 * brStandard.RoomAdults) + brStandard.RoomChildren
                'Dim infant As Integer = 0
                'If Not IsNothing(Session("InfantQty")) Then
                '    infant = Session("InfantQty")
                'End If
                'intOccupancyID = intOccupancyID - infant

                Dim dtRoomOccupancies As DataTable = GetRoomOccupancies(intOccupancyID)

                ' Divide by the total price by the number of rooms required, so we get a price per room
                Dim decRoomPriceTotal As Decimal = Strings.FormatNumber(Convert.ToDecimal(brStandard.SupplierTotalPrice) / intNumRooms, 2)

                ' Loop through each room required according to the total occupancy
                For intRoomNum As Integer = 1 To intNumRooms
                    oCmd = New SqlCommandWrapper("spMakeNewBookingPrice", sqlTrans)
                    oCmd.AddSqlOutputParameter("@roomBookingId", SqlDbType.Int)
                    oCmd.AddSqlParameter("@roomId", SqlDbType.Int, intRoomID)
                    oCmd.AddSqlParameter("@boardTypeId", SqlDbType.Int, intBoardTypeID)
                    oCmd.AddSqlParameter("@occupancyId", SqlDbType.Int, intOccupancyID)      ' Not actually used
                    oCmd.AddSqlParameter("@agentId", SqlDbType.Int, inAgentId)
                    oCmd.AddSqlParameter("@start", SqlDbType.DateTime, dteStart)
                    oCmd.AddSqlParameter("@end", SqlDbType.DateTime, dteEnd)
                    oCmd.AddSqlParameter("@total", SqlDbType.Money, 4, 4, decRoomPriceTotal)
                    oCmd.AddSqlParameter("@sessionId", SqlDbType.VarChar, 250, stWebSessionId)
                    oCmd.ExecuteNonQuery()
                    intRoomBookingID = CInt(oCmd.GetIntParameterValue("@roomBookingId"))
                    LogStr &= "BookRooms SP Executed " & intRoomNum & "  : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
                    ' As the standard room object passed to this method contains the total price for all rooms required,
                    ' and the total number of adults and children across all rooms, we should create individual objects
                    ' for each room required so we can store the specific number of adults and children who will occupy each room
                    brCurrent = New BedBankRoom(True, True)
                    BedBankRoom.Copy(brStandard, brCurrent)


                    ' If more than one room is required,
                    ' obtain the number of adults and children who may occupy the current room
                    If intNumRooms > 1 Then
                        intRoomOccupancyID = CInt(dtRoomOccupancies.Rows(0).Item("Room" & intRoomNum & "Occupancy"))
                        If intRoomOccupancyID <> 0 Then
                            brCurrent.RoomAdults = CInt(Math.Floor(intRoomOccupancyID / 10))
                            brCurrent.RoomChildren = intRoomOccupancyID - (10 * brCurrent.RoomAdults)
                        Else
                            brCurrent.RoomAdults = 0
                            brCurrent.RoomChildren = 0
                        End If
                    End If

                    ' As we're creating individual objects for each room required, the room quantity
                    ' for each room will always be 1
                    brCurrent.RoomQuantity = 1

                    If intRoomNum <> 1 Then
                        ' If it's not the first room, set the room price to be zero
                        brCurrent.SupplierTotalPrice = 0
                        brCurrent.SupplierPrice = 0
                    End If

                    ' Add each room required to a booking list object
                    brdListForBooking.Add(New BookRoomDetails(brCurrent, intRoomBookingID, "", ""))

                    ' now lets check for optional supplements
                    If brCurrent.HasOptionalSupplements Then
                        For Each os As OptionalSupplement In brCurrent.OptionalSupplements
                            If os.Status = OptionalSupplement.SupplementStatus.Selected Then
                                oCmd = New SqlCommandWrapper("spInsertBookingSupplement", sqlTrans)
                                oCmd.AddSqlOutputParameter("@BookingSupplementID", SqlDbType.Int)
                                oCmd.AddSqlParameter("@RoomBookingID", SqlDbType.Int, intRoomBookingID)
                                oCmd.AddSqlParameter("@SupplementID", SqlDbType.Int, os("supplementID"))
                                oCmd.AddSqlParameter("@AgentID", SqlDbType.Int, inAgentId)
                                oCmd.AddSqlParameter("@DateFrom", SqlDbType.DateTime, dteStart)
                                oCmd.AddSqlParameter("@DateTo", SqlDbType.DateTime, dteEnd)
                                oCmd.AddSqlParameter("@TotalPrice", SqlDbType.Money, os.Price)
                                oCmd.AddSqlParameter("@SessionID", SqlDbType.VarChar, 250, stWebSessionId)
                                oCmd.ExecuteNonQuery()
                            End If
                        Next
                    End If
                    LogStr &= "BookRooms SpInsertBookingSupplement Executed " & intRoomNum & "  : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
                Next

                sqlTrans.Commit()
            Catch ex As Exception
                sqlTrans.Rollback()
                brdListForBooking.Clear()

                Dim objMailer As New System.Net.Mail.SmtpClient(Current.Application.Get("SMTP_SERVER"))
                objMailer.Send(CStr(Current.Application.Get("ADMIN_EMAIL_FROM_ADDRESS")), "Ali.Iybar@onthebeach.co.uk", "BedBankSupplier - BookRooms Error", ex.ToString())
            End Try

            sqlConn.Close()
            sqlTrans = Nothing
            LogStr &= "BookRooms Before Cache Update    : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
            If intDestinationID <> -1 Then
                ' Invalidate the room availability cache record (if one exists)
                sqlConn = New SqlConnection(Current.Application.Get("SearchCacheConnectionString"))
                Dim dteStart As DateTime = brStandard.FromDate
                Dim dteEnd As DateTime = brStandard.ToDate
                Dim intOccupancyID As Integer = (10 * brStandard.RoomAdults) + brStandard.RoomChildren
                Dim strSQL As String = "" & _
                    "UPDATE tblGetRoomsAvailableCache SET " & _
                    "CacheIsInvalidated = 'Y', CacheEndDate = GETDATE() " & _
                    "WHERE DestinationID = @DestinationID " & _
                    "AND FromDate = @DateFrom " & _
                    "AND ToDate = @DateTo " & _
                    "AND OccupancyID =@occupancyId ;"
                Dim sqlComm As New SqlCommand(strSQL, sqlConn)
                sqlComm.Parameters.Add("@DestinationID", SqlDbType.Int).Value = intDestinationID
                sqlComm.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = dteStart
                sqlComm.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = dteEnd
                sqlComm.Parameters.Add("@occupancyId", SqlDbType.Int).Value = intOccupancyID

                sqlConn.Open()
                Try
                    sqlComm.ExecuteNonQuery()
                    LogStr &= "BookRooms After Cache Update    : " & Now.ToLongTimeString & " " & Now.Millisecond & vbCrLf
                Finally
                    sqlConn.Close()
                    sqlComm = Nothing
                End Try
            End If

            'Dim objMailer1 As New System.Net.Mail.SmtpClient(Current.Application.Get("SMTP_SERVER"))
            'objMailer1.Send(CStr(Current.Application.Get("ADMIN_EMAIL_FROM_ADDRESS")), "Ali.Iybar@onthebeach.co.uk", "BedBankSupplier Measure", LogStr)

            sqlConn = Nothing
            Return brdListForBooking
        End Function

        Private Function isRoomStillAvailable(ByVal dtStart As DateTime, ByVal dtEnd As DateTime, ByVal inBoardTypeId As Integer, _
            ByVal stResortId As String, ByVal inDestinationId As Integer, ByVal inOccupancyId As Integer, _
            ByVal inRoomId As Integer, ByVal inRoomQty As Integer) As Boolean

            'spGetAvailabilityOfARoom

            Dim oResortId As Object

            If IsNumeric(stResortId) Then
                oResortId = Convert.ToInt32(stResortId)
            Else
                oResortId = Convert.DBNull
            End If

            Dim oCmd As New SqlCommandWrapper("spGetAvailabilityOfARoom")
            oCmd.AddSqlParameter("@start", SqlDbType.DateTime, dtStart)
            oCmd.AddSqlParameter("@end", SqlDbType.DateTime, dtEnd)
            oCmd.AddSqlParameter("@boardId", SqlDbType.Int, inBoardTypeId)
            oCmd.AddSqlParameter("@resortId", SqlDbType.Int, oResortId)
            oCmd.AddSqlParameter("@destinationId", SqlDbType.Int, inDestinationId)
            oCmd.AddSqlParameter("@occupancyIds", SqlDbType.Int, inOccupancyId)
            oCmd.AddSqlParameter("@roomId", SqlDbType.Int, inRoomId)
            oCmd.AddSqlOutputParameter("@numAvailable", SqlDbType.Int)
            oCmd.ExecuteNonQuery()

            Dim blNl As Boolean
            Dim inQty = oCmd.GetIntParameterValue("@numAvailable", blNl)

            If (inQty >= inRoomQty) Then
                Return True
            Else
                Return False
            End If
        End Function

#End Region


#Region " Hotel Info "

        Public Function GetHotelInfo(ByRef sc As SearchCriteria, ByVal HotelCode As String) As HotelDetails
            Dim intAccommID As Integer = CInt(HotelCode)
            Dim localImageURL As String = "http://www.apartmentsabroad.co.uk/"

            If Not ServerApplicationState("LocalImageUrl") Is Nothing _
            AndAlso Convert.ToString(ServerApplicationState("LocalImageUrl")) <> String.Empty Then
                localImageURL = Convert.ToString(ServerApplicationState("LocalImageUrl"))
            End If

            Dim hd As New HotelDetails
            Dim accomm As Accommodation = Accommodation.GetAccommodation(intAccommID)

            If Accommodation.UseExtDescriptionsForAccommodation(intAccommID) Then
                ' ------------------------------------------------------------------------------
                ' Populate the hotel details object with the extended Bedbank accommodation data
                ' ------------------------------------------------------------------------------

                hd.LongDescription = accomm.Ext_GeneralInfo
                hd.LocationDescription = accomm.Ext_LocationInfo
                hd.AccommodationDescription = accomm.Ext_AccommodationInfo

                ' Populate the hotel photos
                Dim photos() As String = accomm.GetAccommodationImages_Ext()

                If Not photos Is Nothing Then
                    For index As Integer = 0 To UBound(photos)
                        If Not photos(index) Is Nothing AndAlso photos(index).Trim() <> String.Empty Then
                            Dim photoURL As String = photos(index).Trim().Replace("\", "/")

                            If Not photoURL.StartsWith(localImageURL) Then
                                If localImageURL.EndsWith("/") AndAlso photoURL.StartsWith("/") Then
                                    photoURL = Right(photoURL, Len(photoURL) - 1)
                                End If

                                photoURL = localImageURL & photoURL
                            End If

                            hd.AddHotelPhoto(photoURL)
                        End If
                    Next
                End If

                ' Populate the hotel facilities
                Dim hotelFacilities() As String = accomm.GetAccommodationFacilityDescriptions_Ext(200)

                If Not hotelFacilities Is Nothing Then
                    For index As Integer = 0 To UBound(hotelFacilities)
                        If Not hotelFacilities(index) Is Nothing AndAlso hotelFacilities(index).Trim() <> String.Empty Then
                            hd.AddHotelFacility(hotelFacilities(index))
                        End If
                    Next
                End If

                ' Populate the room facilities
                Dim roomFacilities() As String = accomm.GetAccommodationFacilityDescriptions_Ext(100)

                If Not roomFacilities Is Nothing Then
                    For index As Integer = 0 To UBound(roomFacilities)
                        If Not roomFacilities(index) Is Nothing AndAlso roomFacilities(index).Trim() <> String.Empty Then
                            hd.AddRoomFacility(roomFacilities(index))
                        End If
                    Next
                End If

            Else
                ' ------------------------------------------------------------------------------
                ' Populate the hotel details object with the standard Bedbank accommodation data
                ' ------------------------------------------------------------------------------

                hd.LongDescription = accomm.Description
                hd.LocationDescription = String.Empty
                hd.AccommodationDescription = String.Empty

                ' Populate the hotel photos
                Dim photos() As String = accomm.GetAccommodationImages()

                If Not photos Is Nothing Then
                    For index As Integer = 0 To UBound(photos)
                        If Not photos(index) Is Nothing AndAlso photos(index).Trim() <> String.Empty Then
                            Dim photoURL As String = photos(index).Trim().Replace("\", "/")

                            If Not photoURL.StartsWith(localImageURL) Then
                                If localImageURL.EndsWith("/") AndAlso photoURL.StartsWith("/") Then
                                    photoURL = Right(photoURL, Len(photoURL) - 1)
                                End If

                                photoURL = localImageURL & photoURL
                            End If

                            hd.AddHotelPhoto(photoURL)
                        End If
                    Next
                End If

                ' Populate the hotel facilities
                Dim sFacility_Hotel() As String = accomm.GetAccommodationFacilityDescriptions(200)

                For index As Integer = 0 To UBound(sFacility_Hotel)
                    hd.AddHotelFacility(sFacility_Hotel(index))
                Next

                ' Populate the room facilities
                Dim sFacility_Room() As String = accomm.GetAccommodationFacilityDescriptions(100)

                For index As Integer = 0 To UBound(sFacility_Room)
                    hd.AddRoomFacility(sFacility_Room(index))
                Next

            End If

            ' Obtain the thumbnail image
            Dim thumbnailPhotoURL As String = String.Empty
            Dim thumbnailPhotos() As String = accomm.GetAccommodationThumbnailImages()

            If Not thumbnailPhotos Is Nothing AndAlso thumbnailPhotos.Length > 0 _
            AndAlso Not thumbnailPhotos(0) Is Nothing AndAlso thumbnailPhotos(0).Trim() <> String.Empty Then
                thumbnailPhotoURL = thumbnailPhotos(0).Trim().Replace("\", "/")

                If Not thumbnailPhotoURL.StartsWith(localImageURL) Then
                    If localImageURL.EndsWith("/") AndAlso thumbnailPhotoURL.StartsWith("/") Then
                        thumbnailPhotoURL = Right(thumbnailPhotoURL, Len(thumbnailPhotoURL) - 1)
                    End If

                    thumbnailPhotoURL = localImageURL & thumbnailPhotoURL
                End If
            End If

            ' Populate the common accommodation details
            hd.ThumbnailImage = thumbnailPhotoURL
            hd.HotelName = accomm.Name
            hd.Address = accomm.Address
            hd.CategoryDescription = accomm.CategoryName
            hd.Destination = accomm.DestinationName
            hd.Resort = accomm.ResortName
            hd.MinimumNights = accomm.MinimumNights
            hd.ChangeOver = accomm.ChangOver

            ' EDICode is used as the main identifier of an accommodation
            hd.EDICode = accomm.SupplierAccommodationCode

            ' ExternalCode is used to show a Bedbank description if available for a Barcelo property
            hd.ExternalCode = "" 'Needs to be connected to acccode which is not available in the Accommodation object

            ' InternalCode is used to internally reference a Bedbank property
            hd.InternalCode = accomm.AccommodationCode

            Return hd
        End Function

        Function RCIFacilities(ByVal Code As String) As String
            Code = UCase(Code)
            Select Case Code
                Case "A"
                    Return "All Units"
                Case "N"
                    Return "Nearby (less than 1/2 mile or 1k)"
                Case "X"
                    Return "On site"
                Case "XE"
                    Return "On site, equipment available"
                Case "*"
                    Return "Indoor pool on-site"
                Case "X*"
                    Return "Indoor and outdoor pools on-site"
                Case "D"
                    Return String.Empty
            End Select

            Return Nothing
        End Function

#End Region


    End Class

End Namespace