Skip to main content

Contacting hotels

Archived

5 Tasks

30 mins

Visible to: All users
Advanced Pega Platform 8.5 English
This content is now archived and is no longer updated. Progress is not calculated. Pega Cloud instances are disabled, and badges are no longer awarded. Click here to continue your progress in the latest version.

Scenario

FSG wants to ensure that each hotel with which it has agreements to book rooms for events is treated as equally as possible. FSG wants to identify hotels within 5 miles (or 8 kilometers) of an event within the last month that have not been asked to reserve a block of rooms for an event. FSG plans to use this information to send an email to those hotels and make them aware that FSG prioritizes a room request at the earliest opportunity.

The following table provides the credentials you need to complete the challenge.

Role User name Password
Admin admin@booking rules

Design a query that outputs hotel contact information according to the business requirements. Expose any properties that are not currently exposed that are output by the report or must be exposed to perform joins.

Detailed Tasks

1 Identify design options

A brute force approach can identify every event held within the past month then iterate the list of events. For each event, capture the pyGUIDs of the contacted hotels. Next, have the D_AddressesWithinDistance data page search IsFor=Hotel records with 5 miles of the venue where the event is held. The reference values in the D_AddressesWithinDistance result set are then captured. Any reference that is not in the list of FSG-Data-Hotel pyGUIDs is kept; any reference that does match an FSG-Data-Hotel pyGUID is ignored. Every remaining reference is used to lookup an FSG-Data-Hotel record. Capture the contact information (pyFirstName, pyLastName, and pyEmail1) from each referenced FSG-Data-Contact..

A problem with this iterative approach is that it is possible for multiple events to be held at the same venue in the past month. Also, two venues may be within 10 miles (16 kilometers) of each other, meaning overlap can exist within a 5 mile/8 kilometer radius for each venue. The same hotel might have been contacted for a different events within the past month; one contact is sufficient to rule out that hotel. The goal is to find hotels that have not been contacted.

Hotels in/out of range of venues

The D_AddressesWithinDistance data page sources an activity that calls a Connect-SQL rule. The rule contains a query based on the Haversine formula. The second table subselect, the one aliased as “p” in the Haversine formula, currently asks for a single location’s coordinates.

Alternatively, a table subselect can list the latitudes and longitudes of every venue for every event held in the past month. Instead of joining to one row as it does now, the join can move across multiple venue address coordinates.

Current Definition of “p” Table Subselect

JOIN ( 

SELECT {AddressPage.Latitude Decimal } AS latpoint,

{AddressPage.Longitude Decimal } AS longpoint,

{AddressPage.Distance Decimal } AS radius,

{AddressPage.pyNote Decimal } AS distanceunit,

{AddressPage.IsFor } AS isfor

) AS p ON p.isfor = z.isfor

Possible Definition of “p” Table Subselect

JOIN (

SELECT VenueAddress.latitude AS latpoint,  VenueAddress.longitude AS longpoint

FROM pegadata.p_fsg_data_address AS VenueAddress

INNER JOIN pegadata.pc_fsg_booking_work AS BookEvent

ON BookEvent.{exposedVenueGUID}= VenueAddress.reference

AND BookEvent.pxobclass = 'FSG-Booking-Work-BookEvent

WHERE BookEvent.{exposedEventStartDt}  -- is within the past month

) AS p ON 1 = 1'

Note: Radius, distanceunit, and isfor are removed because they can be defined elsewhere.

The modification to the Haversine Formula does not filter out addresses for hotels that have been contacted during the past month. That modification needs to be made elsewhere within the query.

The solution requires the implementation of the following expression: 

HotelAddressesNotContacted ::= AllAddressesWithinDistance EXCEPT HotelAddressesContacted

Options are:

  • (A) Store the history of venues and contacted hotels within the CustomerData schema
  • (B) Find a way to “insert” lists of  venue and hotel  keys (pyGUIDs) into a Connect-SQL query where each list of pyGUIDs is obtained from the PegaData schema.

In a future release of the Pega Platform™, Connect-SQL rule queries may not be allowed against PegaData schema tables where Pega Platform-specific columns, ones that start with px, py, or pz, are present. The FSG-Data-Address table is defined in the CustomerData schema and has no Pega Platform-specific columns. As a result, that table can be referenced in Connect-SQL rule queries. However, the work pool table for the Booking application case types must not be queried using Connect-SQL rules because that table contains columns such as pzInsKey and pzPvStream.

The challenge with approach (A) is that storing historical pyGUIDs and date-times in the CustomerData schema requires extra work because it is the equivalent of using a temporary table. Approach (A) could also result in loss of data integrity since the same information would be stored in multiple locations.

On the other hand, with approach (B), it is possible to insert SQL using syntax such as: {Asis: property-name}. The following example is a revised expression for HotelAddressesNotContacted.

HotelAddressesNotContacted ::= AllAddressesWithinDistance WHERE Reference NOT IN ({Asis:D_HotelsContacted.AsisExpression })

An example AsisExpression property value is shown as follows.

'5de1376c-aa4d-41dc-acd2-c5cd44eb3f61','d45d7130-471b-4138-87f2-efb689983ee2','6584d826-06e6-4c47-b4ee-2a9c6cd62e7d','f7620988-c687-474f-8b3d-27bb0371680e','6681b769-f2de-45cb-aa7a-e89526765f34','44eb05f1-9c16-48e7-b363-7eaeb63e08e2'

The number of Hotel pyGUIDs must be small enough not to cause the entire SQL statement to exceed the database’s maximum allowed length. As lengthy as the previous example may appear due to the 32 hex character length of a GUID, it is difficult to exceed the maximum allowable SQL statement length. For example, the maximum length of an SQL statement in PostgresSQL is now 2,147,483,648 characters or roughly 2GB.

D_HotelsContacted data page that applies to FSG-Data-Address could be modeled after D_AddressesWithinDistance. To do this, the Connect_SQL_pr_fsg_data_address activity is first be copied to Connect_SQL_HotelsNotContacted

The Connect_SQL_HotelsNotContacted activity first uses a D_HotelsContacted data page that invokes a report definition that obtains the list of GUIDs for hotels contacted within the past month. Afterward, the D_HotelsContacted data page post-processes the pxResults() PageList to construct the value for a property named AsisExpression.

The solution to derive AllAddressesWithinDistance (of an event venue) requires similar treatment. The goal is to solve the right side of the following expression. Here, you want to use “IN” as opposed to “NOT IN.”

AllAddressesWithinDistance ::= SELECT latitude AS latpoint, longitude AS longpoint FROM pegadata.p_fsg_data_address WHERE Reference IN ({D_VenuesUsed.AsIsExpression})

This AsIsExpression value is the sequence of Venue Address pyGUID values associated to FSG Events held in the past month. The following value is an example D_VenuesUsed.AsisExpression property value. D_VenuesUsed.AsisExpression is shorter than D_HotelsContacted.AsisExpression because multiple hotels can be contacted per event.

'7e99dbc3-4e54-4419-a15f-f2ca1f0be265','b7206d9a-36d9-45c2-9849-c5fcd995dadb','d8825749-e2fb-46b3-97aa-3ead12b89584'

2 Expose Properties

 

Event.StartDT

As previously described, the Data Pages that derive the AsIsExpression values for venue address pyGUIDs and hotel address pyGUIDs source a Report Definition. Those Report Definitions, VenueAddressLatLong and HotelAddressLatLong, should only examine events that started in the previous month. Exposing the BookEvent case’s .Event.StartDT property as a case-level property is a straightforward use of Optimize for reporting. The result is shown below.

Book Event Join

A portion of the filter within each Report Definition is:  EVENT.Event.StartDT  Greater Than Previous Month

.RoomsRequest.HotelGUID

Currently the GUID for each contacted hotel exists within each RoomsRequest case’s RoomsRequest abstract Field Group. Recall that his Field Group is shared between the Hotel and HotelProxy applications by way of a Component. Unlike the BookEvent case that has an exposed VenueGUID property, the RoomsRequest case does not have an exposed case-level .HotelGUID property.

The exposed HotelGUID property should reside within the FSG-Booking-Work-RoomsRequest class, not the built-on Hotel application’s FSG-Hotel-Work-RoomsRequest class. It is a simple matter to define a HotelGUID property against FSG-Booking-Work-RoomsRequest. The value for that property can be set using a Rule-Declare-Expression defined as .HotelGUID = .RoomsRequest.HotelGUID.

3 Create Data Pages

The previous task mentioned the VenueAddressLatLong and HotelAddressLatLong Report Definitions. How these Report Definitions are defined can be viewed within the provided solution. As they should be, the filter conditions with the Query tab are identical,.

Because the RoomsRequest case is BookEvent child case, the definition of the EVENT alias would be different for the two Report Defintioins.  Recall that, an FSG-Data-Address references an FSG-Data-Location, not a case.

Report Definition Alias Class Definition
VenueAddressLatLong EVENT FSG-Booking-Work-BookEvent Reference = EVENT.VenueGUID
HotelAddressLatLong ROOMS FSG-Booking-Work-RoomsRequest .Reference = ROOMS.RoomsRequest.HotelGUID
  EVENT FSG-Booking-Work-BookEvent ROOMS.pxCoverInsKey = EVENT.pzInsKey

The rest of the solution is summarized in the table below.  Note that the Response Data Transform, GenerateAsisExpressionFromAddressReference, sets pyNote.

Data Page Report Definition Response Data Transform
D_HotelsContacts HotelAddressLatLong GenerateAsisExpressionFromAddressReference
D_VenueUsed VenueAddressLatLong GenerateAsisExpressionFromAddressReference

4 Create the Connect-SQL rule

That final piece of the puzzle is to define the HotelsNotContacted Connect-SQL rules. The content of the Browse tab is shown below.

Note that the pyNote property on AdddressPage is decided by ternary expression: (param.dist_unit = "km" ? "111.045" : "69.407").

  • SELECT
    pyGUID AS "pyGUID",
    Reference AS "Reference", IsFor AS "IsFor", Street AS "Street", City AS "City", State AS "State", PostalCode AS "PostalCode", Country AS "Country", Latitude AS "Latitude", Longitude AS "Longitude", Distance AS "Distance"
    FROM (
    SELECT z.pyguid AS pyGUID,
    z.reference AS Reference,
    z.isfor AS IsFor,
    z.street AS Street,
    z.city AS City,
    z.state AS State,
    z.postalcode AS PostalCode,
    z.country AS Country,
    z.latitude AS Latitude,
    z.longitude AS Longitude, {AddressPage.pyNote Decimal }
    * DEGREES(ACOS(COS(RADIANS(p.latpoint))
    * COS(RADIANS(z.latitude))
    * COS(RADIANS(p.longpoint - z.longitude))
    + SIN(RADIANS(p.latpoint))
    * SIN(RADIANS(z.latitude)))) AS Distance
    FROM {Class:FSG-Data-Address} AS z
    JOIN (
    SELECT latitude AS latpoint, longitude AS longpoint
    FROM {Class:FSG-Data-Address}
    WHERE reference IN ({Asis:D_VenuesUsed.pyNote}) ) AS p ON 1 = 1 ) AS d
    WHERE distance <= {AddressPage.Distance Decimal }
    AND reference NOT IN ({Asis:D_HotelsContacted.pyNote})
    AND isFor = 'Hotel'
    ORDER BY distance
    LIMIT 15

5 Confirm your work

Proving the solution works requires at least two hotels within the specified radius from a venue. One of the hotels is asked to provide rooms. The second hotel is not be asked to provide rooms. When the query runs, the second hotel is returned in the result set.

  1. Locate the Code-Pega-List Connect_SQL_HotelsNotContacted Activity.
  2. Click Actions > Trace.
  3. Click Actions > Run.
  4. Enter 5 as the distance and mi as dist_unit.
  5. Optional: Enter 8 as the distance and km as the dist_unit.

For each page in D_Hotels, invoke the D_Hotel Lookup data page using “reference” as the pyGUID parameter. The query returns the address for hotels that were not contacted. Identifying the hotel and the hotel’s contact requires iteration of the pyTempListPage.pxResults() PageList.



Available in the following mission:

We'd prefer it if you saw us at our best.

Pega Academy has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice