Contacting hotels

A brute force approach would identify every Event held within the past month then iterate the list of Events. For each Event, capture the pyGUIDs of the hotels that were contacted. Next have the D_AddressesWithinDistance Data Page search IsFor=HOTEL records with 5 miles of the Venue where the Event was held. The Reference values in the D_AddressesWithinDistance result set would then be captured. Any Reference not in the list of FSG-Data-Hotel pyGUIDs would be kept; any Reference that does match an FSG-Data-Hotel pyGUID would be ignored. Every remaining Reference would be used to lookup an FSG-Data-Hotel record. Then capture contact information (pyFirstName, pyLastName, and pyEmail1) from each Hotel record’s embedded Contact Field Group.

A problem with this iterative approach is that it is possible for multiple events to be held at the same Venue within the past month. Also, two Venues may be within 10 miles of each other, meaning overlap could exist within a 5 mile radius for each Venue. A Hotel may not have been contacted for one Event but could have been contacted for a different Event at some point within the past month.

The D_AddressesWithinDistance data page sources an activity that calls a Connect-SQL rule that 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 could 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 would be made across multiple venue address coordinates.

The above modification to the Haversine Formula, however does not filter out addresses for Hotels that were contacted during the past month. That needs to be done elsewhere in the query.

The solution requires that the expression below also be implemented

In a future release of the Pega Platform, Connect-SQL rule queries may not allow tables that contain Pega-specific columns, (columns that that start with px, py, or pz. The FSG-Data-Address table (pegadata.pc_fsg_data), is defined in the CustomerData schema and has no pega specific columns and thus can be referenced in Connect-SQL rule queries. However, the work pool table for the Booking application case types (pc_fsg_booking_work) should not be queried using Connect-SQL rules since that table contains columns such as pzInsKey and pzPvStream.

In the long run the best approach is either (A) store the history of pyGUIDs for contacted hotels in the CustomerData schema or (B) find a way to “insert” lists of pyGUIDs into a Connect-SQL query where each list of pyGUIDs is obtained from the PegaData schema.

The challenge with approach (A) is that storing historical pyGUIDs and date-times in the CustomerData schema requires extra work since it is the near equivalent of using a temporary table. A Savable Data Page could also be used to reinsert data. Approach (A) adds complexity and potentially 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}. Below is a revised expression for HotelAddressesNotContacted.

An example AsisExpression property value is shown below.

The number of Hotel pyGUIDs should be small enough not to cause the entire SQL statement to exceed the database’s maximum allowed length. As lengthy as the above example may appear due to the 32 hex character length of a GUID, it would be 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.

A 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 would first be copied to Connect_SQL_HotelsNotContacted. Next a post-processing activity such as Post_Connect_SQL_HotelsNotContacted would be created.

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

The solution for deriving AllAddressesWithinDistance (of an Event Venue) requires similar treatment. The goal is to solve the right side of the expression below. Here we want to use “IN” as opposed to “NOT IN”.

This AsIsExpression value would be the sequence of Venue Address pyGUID values associated to FSG Events held in the past month. An example D_VenuesUsed.AsisExpression property value is shown below. D_VenuesUsed.AsisExpression would be shorter than D_HotelsContacted.AsisExpression since multiple hotels can be contacted per Event. See the example below.

Expose .VenueGUID within FSG-Booking-Work-BookEvent

1. In the Case Designer click on the Book Event case and open the Data Model tab
2. Add a .VenueGUID (Text) property
3. Within the App Explorer right click on .VenueGUID and choose “Define expression”
4. Set .VenueGUID = .Venue.pyGUID
5. Within the App Explorer right click on .VenueGUID and choose Optimize for reporting

Expose .Event.StartDT within FSG-Booking-Work-BookEvent

1. Within the App Explorer right click on .Event.StartDT and choose “Optimize for reporting
   
2. To verify, create a BookEvent case advancing it past the start date entry screen
3. Using pgAmin4, execute:
   
   SELECT startdt_1,
   pxcreatedatetime
   FROM pegadata.pc_fsg_booking_work
   WHERE startdt_1 is not null
   AND pxobjclass = 'FSG-Booking-Work-BookEvent'
   ORDER BY pxcreatedatetime desc

Create an Address report definition that JOINS BookEvent by .Reference = .VenueGUID

1. See below:
   

Expose the HotelGUID property within FSG-Booking-Work-RoomsRequest

1. In the Case Designer click on the RoomRequest case and open the Data Model tab
2. Add a .HotelGUID (Text) property
3. Within the App Explorer right click on .HotelGUID and choose Define expression
4. Set .HotelGUID = .RoomsRequest.HotelGUID
5. Within the App Explorer right click on .HotelGUID and choose Optimize for reporting
6. To verify, create a BookEvent case, select Hotel Service, spin off at least one RoomsRequest subcase
7. Using pgAmin4, execute:
   
   SELECT hotelguid,
   pxcreatedatetime
   FROM pegadata.pc_fsg_booking_work
   WHERE hotelguid is not null
   AND pxobjclass = 'FSG-Booking-Work-RoomsRequest’
   ORDER BY pxcreatedatetime desc

Create an Address Report Definition that JOINS RoomsRequest by .Reference = ROOMS.HotelGUID

1. Query tab exactly the same as in the VenueAddressLatLong report definition
2. However, the Data Access tab is different. .Reference = ROOMS.HotelGUID and ROOMS.pxCoverInsKey = EVENT.pzInsKey
   

Define Data Pages that source the Hotel and Venue Address-querying Report Definitions that also Transform the Response

The logic to define the two Report Definitions was discussed during solution design. In the table below the Response Data Transform sets pyNote.

Modify the HotelsNotContacted Connect-SQL rule to utilize the two Report Definition-sourced Data Pages

• The final state of the HotelsNotContacted Connect-SQL rule is shown below.

• AddressPage.pyNote = (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 pegadata.pr_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

Verify your work

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

Locate the Code-Pega-List Connect_SQL_HotelsNotContacted Activity. Select Actions > Trace, then Actions > Run. Enter 5 as the distance and mi as dist_unit.

The query returns the address for hotels that were not contacted. To identity the hotel and the hotel’s contact would require the pyTempListPage.pxResults() PageList to be iterated.

For each page in D_Hotels, invoke the D_Hotel Lookup data page using “reference” as the pyGUID parameter