What it does
Finds customers whose
Filters exclude purchased packages (
reward_card_transaction shows an earned award (award_status = 'Award') on the previous open business day. The date stepping walks backward through the shop schedule to find the correct target date regardless of weekends or closed days.Filters exclude purchased packages (
service_package_id is null) and require a real RO number (ro_number != ''), so only legitimate earned awards trigger the message. Customer must have opted into marketing via customer_locations.marketing_ok.
CTEs
shopSchedule
Open/closed status per day of week
Pulled from config_client_location_hours for the target shop
dateSteps
Recursive backward date walk
Steps back one calendar day at a time, counting only open days until nDays is reached
collectDate
The resolved target date
The open business day that is nDays back from today
rewardsBalance
Current balance per customer
credits − debits from cash_ledgers, familyID scoped, deleted_at excluded
mainQuery
Candidate selection with dedup
Anchors on reward_card_transaction. having clause matches max(created_at) to collectDate.
Output fields
candidateID
companyID
company
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsBalance"
"OilChangeEarnedDate"
"CusType"
"CusGrade"