186 lines
No EOL
9.3 KiB
Markdown
186 lines
No EOL
9.3 KiB
Markdown
# Payment Validation Set data problems
|
||
|
||
# Summary
|
||
|
||
- We can’t tell what paid services or what prices were offered in any past Guest Journey.
|
||
- We can only know what services and prices are set right now (you can find some explainers and queries below on how to achieve this).
|
||
- This won’t change until . Even in that case, past history is gone for good and we can’t rebuild it.
|
||
|
||
# Research
|
||
|
||
While working on Joan’s request 19459, where we were trying to obtain guests that selected `FeewithDeposit` from certain hosts that have the option available in their listings, we discovered that we couldn’t make the join between `live.dbo.PaymentValidationSet` and `live.dbo.VerificationRequest`.
|
||
|
||
Taking a closer look to these tables we encountered many `NULL` values inside`live.dbo.VerificationRequest` for `PaymentValidationSetId`, in the following query result it shows that around 99% of the values are missing.
|
||
|
||
**select** PaymentValidationSetId, **count**(*)
|
||
|
||
**from** VerificationRequest *vr*
|
||
|
||
**group** **by** PaymentValidationSetId
|
||
|
||
|
||
|
||
Here is also the query used to obtain the `PaymentValidationSetId` with `FeewithDeposit` active
|
||
|
||
**select** *
|
||
|
||
**from** PaymentValidationSet *pvs*
|
||
|
||
**left** **join** PaymentValidationSetToCurrency *pvstc* **on** *pvstc*.PaymentValidationSetId = *pvs*.Id
|
||
|
||
**where** pvstc.DisabledValidationOptions & 4 <= 0
|
||
|
||
**and** pvs.IsActive = 1
|
||
|
||
**and** pvstc.CurrencyIso = **'GBP'**
|
||
|
||
## How it works and how to get the data
|
||
|
||
This `PaymentValidationSetId` inside `live.dbo.VerificationRequest` was added by someone that didn’t backfill the data and they are not sure how correct it is [https://guardhog.visualstudio.com/Superhog/_workitems/edit/13564](https://guardhog.visualstudio.com/Superhog/_workitems/edit/13564).
|
||
|
||
Currently the way we can obtain the data of options available that the hosts is setting for each accommodation is looking into `PaymentValidationSetId` inside `live.dbo.Accommodation`, **which unfortunately will only give the current `PaymentValidationSetId` so we won’t have any history**. In case this value is `NULL` then is using the default `PaymentValidationSetId` (**that it could be modified**), otherwise it uses a custom version payment.
|
||
|
||
In case is using the default `PaymentValidationSetId` , we have to check for all the default versions on the `live.dbo.PaymentValidationSetId` table which can be found by the `SuperhogUserId = Booking.CreatedByUserId or SuperhogUserId is NULL` and use the version that would have been active for this booking when created (`Booking.CreatedDate` )
|
||
|
||
Using the query given by Lawrence we can obtain the options available for each of these `PaymentValidationSetId` , though this is still for confirmation on if it is working correctly.
|
||
|
||
**Example:**
|
||
|
||
1- First for a random **BookingId=738395**
|
||
|
||
**`select** *`
|
||
|
||
**`from** Booking *b*`
|
||
|
||
**`where** BookingId =738395`
|
||
|
||
`--2024-06-01 CreatedDate`
|
||
|
||
`--760891 VerificatioRequestId`
|
||
|
||
`--75feb1f7-848d-4394-aa08-c8d82f34f80e CreatedByUserId`
|
||
|
||
`--85757 AccommodationId`
|
||
|
||
We get the **AccommodationId, VerificationRequestId, CreatedByUsedId (Host)** and `CreatedDate`
|
||
|
||
2- We check the **PaymentValidationId** inside **Accommodation**
|
||
|
||
**`select** * **from** Accommodation a`
|
||
|
||
**`where** AccommodationId = 85757`
|
||
|
||
|
||
|
||
**PaymentValidationSetId** is set to NULL so it uses the default version of **PaymentValidationSet**
|
||
|
||
3- We obtain the data from **PaymentValidationSet**
|
||
|
||
**`select** *`
|
||
|
||
**`from** PaymentValidationSet pvs`
|
||
|
||
**`where** SuperhogUserId = **'75feb1f7-848d-4394-aa08-c8d82f34f80e'** **or** SuperhogUserId **is** **null**`
|
||
|
||
|
||
|
||
Here we can see that it has multiple versions for the default payment being the most recent one **Id=2186** and it was also created before the **booking.CreatedDate,** but it is not active. So in this case the **PaymentValidationSet** is **Id=1**
|
||
|
||
FYI This is also a weird case because **booking.CreatedDate** is before the **PaymentValidationSet.UpdatedDate** of **Id=2186,** so it makes sense that it uses that one but we cannot confirm it
|
||
|
||
4- We use Lawrence’s query to obtain Payment options for the previously obtained **PaymentValidationSet**
|
||
|
||
```sql
|
||
SELECT
|
||
PaymentValidationSet ,
|
||
CASE WHEN DisabledValidationOptions & 1 > 0 THEN 0 ELSE 1 END AS "Fee(1)",
|
||
CASE WHEN DisabledValidationOptions & 2 > 0 THEN 0 ELSE 1 END AS "Membership(2)",
|
||
CASE WHEN DisabledValidationOptions & 4 > 0 THEN 0 ELSE 1 END AS "FeeWithDeposit(4)",
|
||
CASE WHEN DisabledValidationOptions & 8 > 0 THEN 0 ELSE 1 END AS "Waiver(8)",
|
||
CASE WHEN DisabledValidationOptions & 16 > 0 THEN 0 ELSE 1 END AS "NoCover(16)"
|
||
FROM
|
||
PaymentValidationSetToCurrency pvstc
|
||
WHERE
|
||
CurrencyIso = 'GBP'
|
||
and PaymentValidationSetId = 1
|
||
```
|
||
|
||
5- Finally we can verify that the Payment chosen by the guest coincides with the available options for this **PaymentValidationSet**
|
||
|
||
**`select** Value`
|
||
|
||
**`from** Verification v`
|
||
|
||
**`where** Name = **'PaymentValidation'**`
|
||
|
||
**`and** VerificationRequestId = 760891`
|
||
|
||
## Current explanation and work from Development Team
|
||
|
||
There are 4 levels of settings when it comes the payment validation sets
|
||
|
||
- Global level
|
||
- Account/User level
|
||
- Listing Level
|
||
- Host&Stay override
|
||
|
||
**Global Level PaymentValidationSet**
|
||
|
||
- these were set in the db when we first implemented payment
|
||
- in the db there is one row with SuperhogUserId == null, this is the Global PaymentValidationSet
|
||
- where can we see them? **(nowhere - yet)**
|
||
|
||
Rules
|
||
|
||
- If no override has been set for an account, all guest journeys created via this account use the Global PaymentValidationSet
|
||
|
||
**Account Level PaymentValidationSet**
|
||
|
||
- Set in Wilbur
|
||
- When a host account is set up and the user navigates to the Wilbur -> Verification Terms tab, they will see their account set. If no previous account set is saved, they will see the Global set by default
|
||
- When they press save (regardless of if any actual changes have been made) this account will now have an account override set.
|
||
- Each time they save, the old set is expired and a new set is created so we can track history
|
||
|
||
Rules
|
||
|
||
- If an account level PaymentValidationSet has been set for an account (AND NO OTHER SET exists - e.g. listing level or host and stay), all guest journeys created via this account will use this Account Level PaymentValidationSet
|
||
|
||
**Listing Level PaymentValidationSet**
|
||
|
||
- A host can use their dashboard to create one or more PaymentValidationSets and then optionally apply these to one or more listings
|
||
- Part 1 - setting up and editing listing level paymentvalidationsets
|
||
- Load host dashboard and go to account -> Deposit/Waiver Price Plans
|
||
- The default override (account or global level, whichever applies) will come first and will not be editable
|
||
- All other sets are listing level sets. You can add new ones and edit existing ones (cant yet delete)
|
||
- Part 2 - using the listing level paymentvalidationsets
|
||
- Now that you have some listing level paymentvalidationsets, you can now apply them to individual listings
|
||
- Go to the dashboard and go to Listings
|
||
- you can use the dropdown to set a price plan for each listing.
|
||
- by default, all listings will be assigned the default paymentvalidationset. when you choose another paymentvalidationsets from the dropdown dev then this paymentvalidationsets will be set against that listings
|
||
- Dev Note: if you dont select one, or change it back to default the listing will simply have a NULL PaymentValidationSetId - this is how we apply the default
|
||
- **Dev Note (nice to have)**: should really a save notification when these are applied
|
||
|
||
Rules
|
||
|
||
If a booking is created against a listing with a listing paymentvalidationsets, then the payment values that are used are from that listing paymentvalidationsets. If the listing does not have one, then the default is used (Account level first, global if none of the above)
|
||
|
||
**Host&Stay override**
|
||
|
||
With Host & Stay, lets say
|
||
|
||
- there were 100 guest journeys already existing
|
||
- the Existing Account/User level PaymentValidationSet existed lets say PaymentValidationSetId = 456
|
||
- **All existing guest journeys need to use 456, end of story - regardless of any subsequent changes**
|
||
- **All guest journeys created after this point need to follow the normal rules**
|
||
|
||
This is the work that Yaseen did as part of sprint 39. Unfortunately it didnt work very well as this work exposed an existing bug - we were under the mistaken impression that that because no listing level paymentvalidationsets were applied against any Host&Stay listings (**but two did actually exist**) - this exposed an error in the code whereby we just retrieved all of the paymentvalidationsets for that host and just simple picked the top paymentvalidationset without applying any other filter. We need to fix this because it's wrong (!)
|
||
|
||
So, three things need to be done
|
||
|
||
- fix the bug that host&stay exposed
|
||
- remove duplicate code which handles paymentvalidationsets and have a single source of truth
|
||
- unit test and end to end test to make sure all rules are followed correctly
|
||
|
||
## Upcoming Changes
|
||
|
||
In future work there is a plan to add the PaymentValidationSet to VerificationRequest for easier connection between as well as having some historic data for each of them, which is inexistent today. Also a simplification as to how to get the available payment options set by the host in their PaymentValidationSet, so we don’t have to depend on a complex query with virtually no explanation as to how it works |