6. Event Reservation, store in db and send a Confirmation Email
How to add an event reservation function, store in db and send a confirmation email.

Topics in this video
Preparing HTML-Email Template (00:50)
Design Confirmation Page (01:35)
Adding a Reservations Table to DB (02:00)
Creating and setting up Node.js API (02:17)
Verifying User Credentials (03:50)
Getting Selected Event Information (04:37)
Adding Reservation to the Database (05:16)
Preparing and Sending out Email (05:40)
Assigning API to Frontend Button (07:33)
Redirection to and from Login.html (08:46)


Hi and welcome to the sixth episode of AppDrag Academy.  In this episode, we're going to activate the registration button of the event detail page, whereby upon clicking, a logged-in user will be added to the Reservations table in the database, and a nicely-designed email confirmation containing the event details will be sent to his associated address.  If the button was clicked by a visitor that is not logged in, he will be redirected to the login page.  Then once he logs in, he's automatically redirected back to the event page to successfully register.

Designing the Email Template
This time, before we go to the backend, let's first design our confirmation-email template.  And we're going to do that by using the newsletter campaign manager supplied by AppDrag.  Let's select a template and make some changes in design.  I'm going to plug the placeholders where I need them to be, and these, of course, will be replaced in our API.  For the image, I'm going to access the source code and replace the current URL for the image placeholder.  Let's save that and copy the URL for a second.  I'm going to paste it in my notepad and provide you here with another URL that we're going to need later on.

When you replace the parameters with your own, remove brackets as well

This URL requires two parameters that are found in our newsletter's URL, so we're going to replace these for those.  And we'll hold on to this for now.

Designing the Confirmation Page
Next, we're going to design the Confirmation page that we'll redirect to upon a successful registration.  So I add a page without a header or footer, remove it's visibility from the menu, and fill it with some confirmation content and a back to home button.  

Nice and simple, which takes us to our next step, cloud backend.

Backend Development - Reservations Table
We're going to create a reservations table that will get populated every time a user books an event, so the columns we'll need are the event ID and the user ID.  

Creating and Setting up Node.js API
Then we'll create a Node.js API that once activated, it'll grab the user's email and token from the local storage, and the event ID from the URL, and with those run through the process of creating a reservation and sending a confirmation email.  

(Complete API source code is found at the end of this transcript)

So the first step will be to get those input parameters which are the email, token, and event ID.  For the test values, I'm going to make sure that I'm putting an email and token that match in my users table. 

Let's step into the handler and add cloudbackend from the library manager.  Open the documentation to retrieve the initialization specs.  I'm going to leave this tab open, as we'll be going back and forth to it. Paste it before the handler begins, and we're going to select the environment variables option.
Another module we're going to include in our API is "Request", which will allow us to make an HTTP call.

So what will this API do this time? Nine things. 
   - First, get the user's email and token, and event ID from the input parameters. 
   - Second will be to verify the user's credentials.
   - Third, if the credentials don't match, we'll return an error object.
   - Fourth will be to get the first and last name of the user.
   - Fifth will be getting the activity's information.
   - Sixth will insert the reservation into the table.
   - Seventh, we're going to prepare the email components to build the confirmation email.
   - Eighth is to send the email out.
   - And finally, once all is nice and done, we send out a success message.

And we begin.  So before anything, let's make the handler asynchronous.  And to get the email, token, and activity ID, we'll need to include the cleanVar function below.  

Verifying the User's Credentials
Next, I'm going to add a variable that will hold the user's details and leave it as null for now.  To get the user's name, we need to search the database.  So let's copy the SQL select from the documentation and paste it in.  We await it, replace the table, replace the value for the user's email and token, parse the response, and check if it returned null.  If it did, return an error object, and we're out.  Else, assign the user's info to the var we created right above.  We split it into the individual variables we'll need for later, so ID, first name, and last name, and we move on. 

Getting Selected Event Information
Next up is the event details, so let's create a variable that will hold those and leave it null for now.  And once again, I'm going to fetch those using the SQL select.  So await, replace the table and value, and add in also a formatting column of the returned date, so it looks nice and neat when we use it.  This particular format returns the full name of the month, the day with the suffix, followed by a coma and a four-digit year.  Parse the response, and assign the details to the variable above.  Now that we have the event object, we, once again, split it into individual variables for later.  And those will be the name, formatted date, price, location, and image.

Adding the Reservation into the Table
Sixth step is inserting the reservation into the database.  So let's prepare the columns, and those are the eventID and userID.  And for the values, those are the activityID and userID we got in step 2. 

I'm also going to prepare the insert query as a query variable so I can just pluck it in, then grab the raw SQL query from the documentation.  There it is,  await it, and drop in the query. 

Preparing and Sending out the Confirmation Email
So all the variables are ready and the registration is done, and what's left is the confirmation email.  The first thing we're going to need is the template's URL which we have on the Notepad, so let's get that.  Next will be a variable that will hold the content of the email, which is the HTML that designs it.  So to get that, we're going to call an awaited function by the name of  "get template content from URL", and pass in the template URL.  

Let's include that function below our handler and see what it does.  It's a bit confusing, but in short, it returns the resolved promise that went to fetch the HTML from the URL, unzipped it, and stringified it.  Now that we have the email content, we're going to, one by one,  replace the placeholders for the variables we've been creating since the top.  For the image, just include "HTTPS:" before the variable, and that completes the preparation of the HTML for a beautiful confirmation email containing all the details of our user and the event he booked.  

So let's send it to him.  We're going to prepare the subject line containing the event name, and then one last time, we visit the Cloud Backend documentation to pull the send email method.   Await that method, and replace the parameters.  The sender email will be mine, the sender will be the Reservations at RunFun, the to-address is the user email, the subject line will be the var we just created, and the message is the content variable.  The last Boolean is whether the message is made up of HTML or simple text.  And since it's HTML, we make that true.

Finally,  the success message will be an object returning "Confirmation Sent".  

Let's hit save and give it a try.  And there's our confirmation.  And here's an email that has just arrived.  

Open it, and there's the template with all the right details.
Let's visit the Reservations table, and there's our reservation.

Assigning the API to the Frontend Button
Now let's go to the frontend details page and set up the button.  Go to Cloud Backend, select the API we just wrote, and match the parameter with a formula.  To get them, we're going to fetch them from the local storage. The activity ID comes from the URL, and to grab that parameter, AppDrag provides a method called "get param from URL", and all you need to send in is the name of the parameter you want, which, in our case, is ID.  

After the API call, we open the JavaScript execution, and check out what came back.  So let's parse that response and look out for the error in the payload.  If it's arrived, then the user wasn't logged in, and we redirect him to Login.  B ut before that, we need to send him this URL so he can land back here as soon as he's done.  So create a var called currentURL that'll call the encode "URI component method" which will encode our URL.  And now, we can redirect to login with an attached parameter called redirect URL that is equal to our encoded URL. Else, and that's when all went nice and smooth, redirect to the confirmation page. 

Redirection to and from Login.html
What's left is looking out for the redirect URL in the Login page.  So let's go to the login button's Cloud Backend options, and open the JavaScript.  A fter the login API call, and after it's a success, but before we redirect the user anywhere, we, once again, call the "get param from URL" method and send in the redirect URL parameter that we just set, and check to see if it's not an empty string.  If it's not, we redirect to it, but only once we decoded it using the reverse URI method of "decode URI component".  Else, and that's when the parameter is empty, redirect to the client area, just as it was previously.

Let's save, go to the Events page and preview.  Select one of the activities, and hit register.  We're not logged in, so we're redirected to the login page, and note that the URL now contains the encoded URI URL.  We log in, and we're redirected back to our activity page instead of the client area.  Click register once again, and now we're redirected to the Confirmation page.  And here's the new email with the confirmation.

Other episodes

► 1. Create your first database and API function.

► 2. Create an authentication system

► 3. Create an account form.

► 4. Add a Forgot Password function and an updatable account details page

► 5. List dynamic data from the db and display a detail page for a particular item of the list.

► 6. Event Reservation function, email template with variables, send.