Next Birthday

Introduction

We will be sending email reminders about forthcoming birthdays 21, 14 and 7 days before a birthday.  We will therefore need to know, on a daily basis, whose birthdays are coming up and the number of days to their birthday.

It turns out that this is a lot trickier than one might expect.  We will be using SQL to provide this data.

YouTube Video

Summary of Tasks

  • Create a SQL function to calculate 'Next Birthday'.
  • Create a SQL stored procedure to retrieve a list of people with their full name, their next birthday, the number of days to their next birthday and their age at next birthday.
  • Amend the Person class to accommodate full name, next birthday, number of days to next birthday and age at next birthday.
  • Amend the Person Service and Interface to take account of the additional stored procedure.
  • Modify the Email Test Page to send emails where any birthday reminders are due.
  • Modify the Email Test Page to add Toast messages to notify the user what, if anything, has happened.

SQL - Next Birthday

Date calculations are notoriously difficult.  What may seem plainly obvious to us, such as the date of someone's next birthday, is actually quite difficult to program.

I had, originally, intended simply to write a SQL stored procedure to return a person's next birthday and their age next birthday.  This got surprisingly complicated. (I think mainly because subtracting the year part of one date from another doesn't take into account whether the birthday is in January, say, and the current date is November, or the birthday is in November and the current date January.)  I therefore decided to make use of a SQL feature known as 'Functions' to do the work of calculating the next birthday.

To create the function (called 'NextBirthday), open a new SQL query window and paste in the following code:

USE [Birthdays]
GO

/****** Object:  UserDefinedFunction [dbo].[NextBirthday]   ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[NextBirthday]
(
    @DateOfBirth Date
)
RETURNS Date
AS
BEGIN
    DECLARE @CurrentAge int
    DECLARE @NextBirthday Date

	/******   DateDiff(interval, Date1, Date2) ******/

    SET @CurrentAge = DateDiff(year, @DateOfBirth, GETDATE())
	
	/******   DateAdd(interval, number, Date) ******/

    SET @NextBirthday = DateAdd(year,@CurrentAge,@DateOfBirth)
            
    IF @NextBirthday < GetDate()
        SET @NextBirthday = DateAdd(year,1,@NextBirthday)

    RETURN @NextBirthday

END
GO

The person's date of birth is passed to the function and it returns the next birthday.  The 'If' statement checks the raw calculated next birthday and if it is less than (before) the current date it adds a year to the calculated value.  (Note that it returns a Date type - not a DateTime.)

With this function in place we can now create a stored procedure to provide details of those people whose birthday is a set number of days in the future.  Open another SQL new query window and paste in the following:

USE [Birthdays]
GO

/****** Object:  StoredProcedure [dbo].[spNextBirthday_GetByDaysToNextBirthday]   ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spPerson_GetByDaysToNextBirthday]

@DaysToNextBirthday int
AS
BEGIN
--SQL for Select stored procedure.
SELECT TOP (100) PERCENT PersonFirstName,
        ISNULL(PersonFirstName, '') + ' ' + ISNULL(PersonLastName, '') AS PersonFullName, 
		dbo.NextBirthday(PersonDateOfBirth) AS NextBirthday, 
		DATEDIFF(day, GETDATE(), dbo.NextBirthday(PersonDateOfBirth)) AS DaysToNextBirthday, 
		DATEDIFF(year, PersonDateOfBirth, dbo.NextBirthday(PersonDateOfBirth)) AS AgeNextBirthday, 
		PersonSendReminderTo
FROM   dbo.Person
WHERE DATEDIFF(day, GETDATE(), dbo.NextBirthday(PersonDateOfBirth)) = @DaysToNextBirthday
ORDER BY NextBirthday
END

GO

There are a few things to note about this stored procedure:

  • I am adding FirstName to LastName to create a FullName.  (I am also guarding against null in both FirstName and LastName, although in theory there is no need as I am not allowing Null in the Person Table for these columns.)
  • The NextBirthday function is being called to get the next birthday.
  • I am calculating the number of days to the next birthday by using DateDiff to calculate the difference in days from today (GETDATE()) and the next birthday.
  • Similarly I am calculating the person's age at next birthday.
  • The stored procedure is also providing the email address of the person to whom the email should be sent.
  • The Where clause limits the records returned to those whose next birthday is equal to the number of days being passed to the stored procedure.

Person, PersonService and Interface

With the new stored procedure we need to amend the Person class to accommodate these changes, as well as thePersonService and IPersonService. 

Open Person.cs and add the following lines:

public string? PersonFullName { get;}
public DateTime NextBirthday { get; }
public int? DaysToNextBirthday { get; }
public int? AgeNextBirthday { get; }

Modify PersonService.cs by adding the following method.

public async Task<IEnumerable<Person>> Person_GetByDaysToNextBirthday(int DaysToNextBirthday)
{
    var parameters = new DynamicParameters();
    parameters.Add("@DaysToNextBirthday", DaysToNextBirthday, DbType.Int32);
    IEnumerable<Person> people;
    using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
    {
        people = await conn.QueryAsync<Person>("spPerson_GetByDaysToNextBirthday", parameters, commandType: CommandType.StoredProcedure);
    }
    return people;
}

And IPersonService.cs by adding the following line:

Task<IEnumerable<Person>> Person_GetByDaysToNextBirthday(int DaysToNextBirthday);

Email Test Page

Before moving on to automate the sending of Email Reminders we should check our logic by updating the Email Test page to incorporate semi-automatic despatch of emails.

Open EmailTestPage.razor and add the following at the top of the file (under the other @inject line)

@inject IPersonService PersonService

At the top of the code section declare the following variables that will be used in the 'SendMail' method.

private IEnumerable<Person>? people;
private List<Person>? birthdaypeople;

Now replace the whole of the existing 'SendMail' method with the following:

    public async Task SendEmail()
    {
        string? SendTo;
        string? EmailSubject;
        string? EmailBody;

        for(int daysNotice = 7; daysNotice < 22; daysNotice = daysNotice + 7)
        {
            people = await PersonService.Person_GetByDaysToNextBirthday(daysNotice);  

            birthdaypeople = people.ToList(); //Convert from IEnumerable to List

            foreach(var person in birthdaypeople)
            {
                SendTo = person.PersonSendReminderTo;
                EmailSubject = "It's " + person.PersonFullName + "'s Birthday!";
                EmailBody = "<p>Hello</p>";
                if(daysNotice == 21)
                {
                    EmailBody = EmailBody + "<p>A bit of warning that it's ";
                }
                else if(daysNotice == 14)
                {
                    EmailBody = EmailBody + "<p>Don't forget, it's ";
                }
                else
                {
                    EmailBody = EmailBody + "<p>Better get cracking, it's only " + daysNotice + "days until "; 
                }

                EmailBody = EmailBody + person.PersonFirstName + "'s birthday on " + person.NextBirthday.ToString("dd/MM/yyyy") + ".</p>";
                if (person.AgeNextBirthday < 21)
                {
                    EmailBody = EmailBody + "<p>" + person.PersonFirstName + " will be " + person.AgeNextBirthday + ".</p>";
                }
                EmailBody = EmailBody + "<p>Chris <br/>";
                EmailBody = EmailBody + "Chris's automated Birthday Reminder Service<p>";

                if (EmailService.Send(SendTo, EmailSubject, EmailBody))
                {
                    //do something
                }
                else
                {
                    //do something else
                };
            }
        }
    }

We start by declaring variables for the 'SendTo', 'EmailSubject' and 'EmailBody'.  We want to send reminders 7, 14 and 21 days before someone's birthday (this is somewhat arbitrary, but its just what I have decided!), so we set up a 'for' loop and cycle round the loop where 'daysNotice' is 7, 14 and 21 days, in each case getting any records from the 'Person_GetByDaysToNextBirthday' where the days to the next birthday matches the looping value.

If any records are found, the IEnumerable retrieved by the stored procedure is converted to a List, and for everyone in the list the 'SendTo', 'EmailSubject' and 'EmailBody' composed (varying according to the days notice and age of the birthday person) and finally the 'EmailService' is used to send the email.

Adding Toast

Although this is just a test page to verify that we can send emails, it would be good to have some indication that an email has been sent successfully.  We will use the Syncfusion 'Toast' control to provide that feedback.

  • Add the Toast control to the HTML section:
<div class="col-lg-12 control-section toast-default-section">
    <SfToast @ref="ToastObj" Timeout="4000">
        <ToastPosition X="Right" Y="Top"></ToastPosition>
    </SfToast>
</div>

The 'class' is used for styling - see later. The Timeout property is the time the Toast is displayed in milliseconds, here I have set it to 4 seconds, and the ToastPosition controls where the Toast is displayed. The X options are 'Left', 'Center' and 'Right' and the Y options 'Top' or 'Bottom'.

  • Add the styling for the Toast. (Most of this seems to be associated with the icons displayed on the Toast. And the url really is that long!)  Place this between the HTML and Code sections.
<style>
    @@font-face {
        font-family: 'Toast_icons';
        src: url(data:application/x-font-ttf;charset=utf-8;base64,AAEAAAAKAIAAAwAgT1MvMj0gSRkAAAEoAAAAVmNtYXDnM+eRAAABsAAAAEpnbHlmzVnmlwAAAhgAAAZAaGVhZBEYIl8AAADQAAAANmhoZWEHlgN3AAAArAAAACRobXR4LvgAAAAAAYAAAAAwbG9jYQnUCGIAAAH8AAAAGm1heHABHQBcAAABCAAAACBuYW1lfUUTYwAACFgAAAKpcG9zdAxfTDgAAAsEAAAAggABAAADUv9qAFoEAAAAAAAD6AABAAAAAAAAAAAAAAAAAAAADAABAAAAAQAACcU5MF8PPPUACwPoAAAAANcI7skAAAAA1wjuyQAAAAAD6APoAAAACAACAAAAAAAAAAEAAAAMAFAABwAAAAAAAgAAAAoACgAAAP8AAAAAAAAAAQPqAZAABQAAAnoCvAAAAIwCegK8AAAB4AAxAQIAAAIABQMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAUGZFZABA5wDnCgNS/2oAWgPoAJYAAAABAAAAAAAABAAAAAPoAAAD6AAAA+gAAAPoAAAD6AAAA+gAAAPoAAAD6AAAA+gAAAPoAAAD6AAAAAAAAgAAAAMAAAAUAAMAAQAAABQABAA2AAAABAAEAAEAAOcK//8AAOcA//8AAAABAAQAAAABAAIAAwAEAAUABgAHAAgACQAKAAsAAAAAAAAAQgB8AMIA4gEcAZQCBgJwAo4DAAMgAAAAAwAAAAADlAOUAAsAFwAjAAABFwcXNxc3JzcnBycFDgEHLgEnPgE3HgEFHgEXPgE3LgEnDgEBTXh4L3h4L3h4L3h4AbwDt4qKtwMDt4qKt/0eBeuxsesFBeuxsesCbHh4L3h4L3h4L3h4p4q3AwO3ioq3AwO3irHrBQXrsbHrBQXrAAAAAwAAAAADlAOUAAUAEQAdAAABJwcXAScXDgEHLgEnPgE3HgEFHgEXPgE3LgEnDgEBr2UylwEbMqADt4qKtwMDt4qKt/0eBeuxsesFBeuxsesBrGQylgEcMqKKtwMDt4qKtwMDt4qx6wUF67Gx6wUF6wAAAAAFAAAAAAOUA5cABQARAB0AIQAlAAABFzcnNSMFDgEHLgEnPgE3HgEFHgEXPgE3LgEnDgElFzcnBRc3JwHKxiCnPwFOA6V8fKUDA6V8fKX9aATToJ/UBATUn5/UAh7ANsD9fja/NQGedzNj29F8pAMDpHx8pQMDpXyf1AQE1J+g0wQE0/GhQKGhQKFAAAQAAAAAA74DfgADAAcACgANAAAlMzUjNTM1IwEhCQEhAQHLUlJSUgFj/YwBOv42A5T+NuZUUqf+igIc/ZADFgAEAAAAAAOUA5QAAwAHABMAHwAAATM1IzUzNSMFDgEHLgEnPgE3HgEFHgEXPgE3LgEnDgEBylRUVFQBbgO3ioq3AwO3ioq3/R4F67Gx6wUF67Gx6wEk+lNT0Iq3AwO3ioq3AwO3irHrBQXrsbHrBQXrAAAAAAcAAAAAA+gDMQALABUAJQAuADcAQQBLAAABFhcVITUmJz4BMxYFFhcVITU+ATcWJQYHFSE1LgEjIgYHLgEjIgEWFAYiJjQ2MgUWFAYiJjQ2MiUGFBYXPgE0JiIFBhQWFz4BNCYiA1xEBP6sAxUeRiRX/qxEBP45BIlXV/7xZQsD6AvKUypvMzNvKlMCKxozTTMzTP6CGTNMNDRMAQItWUREWlqI/jstWkREWVmIAWMbFjc3IBgKDwQcGxY3NxY3BAQjJUt7e0tKFxgYFwEMGU01NU0zGhlNNTVNMxYthloCAlqGWy4thloCAlqGWwAAAAQAAAAAA5wCxwAIABQANABFAAABFBYyNjQmIgYXDgEHLgEnPgE3HgEfAQcOAQ8BNz4BNS4BJw4BBxQWHwEnLgEvATc+ATc2FiUOAQ8BFx4BNz4BPwEnJiciAb8fLR4eLR+wAkU0NEUBAUU0NEX8BgEemG0FBB8kAlZBQFcBKyUCCkeVTAYBH76RVMP+3bDPBwcKZclcu/AGCwrM2AoBxxYfHy0eHhc0RQEBRTQ1RQEBRSgEARpWGAECFUIoQVcCAldBLEYUAQEIQkAGASJsBwFCoRbFFAoJW0sBCo8LCgztAQAAAAIAAAAAA4ADbAA4AEEAAAEEJCcmDgEWFx4BHwEVFAYHDgEnJg4BFhcWNjc2Fx4BBx4BFzc+ASc2JicmJzUzPgE3PgEnJicjIiUUFjI2NCYiBgNM/tz+pwwMGxEDDAaMfAcSETKEQw8WBg8Og80hNSg4JwICEw0FDhECAjFJEBICPYhKDQgGChQCB/5dMUgxMUgxAuB/ZRcIAxgbCQdHEQGTGi8TOVgKAw8dFwMNuDUFHTGDCA0QAQECFQ8Mnz8LCasJKiUHGg0SATMkMDBJMDAAAAAAAgAAAAAC/QMkAAMADQAAAQchJxMeATMhMjY3EyEC2x3+bB0kBCQZAQQZJARH/ewDBuDg/fcZICAZAicAAwAAAAACzwPoACwAQwBPAAABERQfARYfAzMVHgE7ATI2NRE0JisBNTEWOwEyNjQmJyMiJi8BLgErAQ4BAxUzNTQ2NzMeARcVMzUuAScjIgcjESM1HgEXPgE3LgEnDgEBVQEBAwQCCAjXARENOg0REQ2zDROVExoaE2UQGAQfAxAKYg0RPR8RDZcNEQEeASIalxANAR8CTTo6TQEBTTo6TQJ8/nYEBQIGBAIFArYNERENARENEUoNGicZARMPfQoNARH98Hl5DREBARENeXkaIgEIAe3FOk0CAk06Ok0BAU0AAAAAAgAAAAAC5gMyAAkAEQAAJRQWMyEyNjURITcjFSE1IycjASApHgEaHin+WFBuAeR+JLD8HigoHgGfeT09HgAAAAAAEgDeAAEAAAAAAAAAAQAAAAEAAAAAAAEAEgABAAEAAAAAAAIABwATAAEAAAAAAAMAEgAaAAEAAAAAAAQAEgAsAAEAAAAAAAUACwA+AAEAAAAAAAYAEgBJAAEAAAAAAAoALABbAAEAAAAAAAsAEgCHAAMAAQQJAAAAAgCZAAMAAQQJAAEAJACbAAMAAQQJAAIADgC/AAMAAQQJAAMAJADNAAMAAQQJAAQAJADxAAMAAQQJAAUAFgEVAAMAAQQJAAYAJAErAAMAAQQJAAoAWAFPAAMAAQQJAAsAJAGnIEZpbmFsIFRvYXN0IE1ldHJvcFJlZ3VsYXJGaW5hbCBUb2FzdCBNZXRyb3BGaW5hbCBUb2FzdCBNZXRyb3BWZXJzaW9uIDEuMEZpbmFsIFRvYXN0IE1ldHJvcEZvbnQgZ2VuZXJhdGVkIHVzaW5nIFN5bmNmdXNpb24gTWV0cm8gU3R1ZGlvd3d3LnN5bmNmdXNpb24uY29tACAARgBpAG4AYQBsACAAVABvAGEAcwB0ACAATQBlAHQAcgBvAHAAUgBlAGcAdQBsAGEAcgBGAGkAbgBhAGwAIABUAG8AYQBzAHQAIABNAGUAdAByAG8AcABGAGkAbgBhAGwAIABUAG8AYQBzAHQAIABNAGUAdAByAG8AcABWAGUAcgBzAGkAbwBuACAAMQAuADAARgBpAG4AYQBsACAAVABvAGEAcwB0ACAATQBlAHQAcgBvAHAARgBvAG4AdAAgAGcAZQBuAGUAcgBhAHQAZQBkACAAdQBzAGkAbgBnACAAUwB5AG4AYwBmAHUAcwBpAG8AbgAgAE0AZQB0AHIAbwAgAFMAdAB1AGQAaQBvAHcAdwB3AC4AcwB5AG4AYwBmAHUAcwBpAG8AbgAuAGMAbwBtAAAAAAIAAAAAAAAACgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADAECAQMBBAEFAQYBBwEIAQkBCgELAQwBDQAFRXJyb3IHU3VjY2VzcwVBbGFybQdXYXJuaW5nBEluZm8HTWVldGluZwVCbGluawdTdHJldGNoA1NpcANTaXQFVHJhc2gAAAAA) format('truetype');
        font-weight: normal;
        font-style: normal;
    }

    #toast_types button {
        margin: 5px;
        min-width: 160px;
        max-width: 160px;
    }

    .toast-icons {
        font-family: 'Toast_icons' !important;
        speak: none;
        font-size: 55px;
        font-style: normal;
        font-weight: normal;
        font-variant: normal;
        text-transform: none;
        line-height: 1;
        -webkit-font-smoothing: antialiased;
        -moz-osx-font-smoothing: grayscale;
    }

    #toast_type .e-toast-icon.e-icons {
        height: auto;
        font-size: 30px;
    }

    .toast-icons.e-success::before {
        content: "\e701";
    }

    .toast-icons.e-error::before {
        content: "\e700";
    }

    .toast-icons.e-info::before {
        content: "\e704";
    }

    .toast-icons.e-warning::before {
        content: "\e703";
    }

    #toast_types {
        text-align: center;
    }
</style>
  • Declare the Toast object. To provide the Title, Content and Icons for the Toast add a List of possible values. (The values shown here are taken from the Syncfusion documentation and include values that we will not be using, but are included here for completeness.)
SfToast ToastObj;

private List<ToastModel> Toast = new List<ToastModel>
{
    new ToastModel{ Title = "Warning!", Content="There was a problem with your network connection.", CssClass="e-toast-warning", Icon="e-warning toast-icons" },
    new ToastModel{ Title = "Success!", Content="Your message has been sent successfully.", CssClass="e-toast-success", Icon="e-success toast-icons" },
    new ToastModel{ Title = "Error!", Content="A problem has been occurred while submitting your email", CssClass="e-toast-danger", Icon="e-error toast-icons" },
    new ToastModel{ Title = "Information!", Content="Please read the comments carefully.", CssClass="e-toast-info", Icon="e-info toast-icons" }
};

Having these parts in place we can now replace the placeholders with code to display the appropriate Toast message:

//do something
await this.ToastObj.Show(Toast[1]);

//do something else
await this.ToastObj.Show(Toast[2]);

The result of this is that if an email is sent is successfully the Toast is displayed with the title and content from index item 1 (remember that the index starts at 0, so 1 is the second entry). Similarly, if the email fails to be sent the Toast with index 2 is displayed.

We will therefore get a message (or messages) if any emails are sent, or if an email should be sent but has failed for any reason.  However, if there are no emails due to be sent there is still no feedback.  To correct this we can declare a boolean and set it to false; if any birthdays are found we can set the boolean to true and then test at the end of the SendEmail method for the value of the boolean.  If it's still false we can display a Toast that states that no birthdays were found.  To add this extra Toast:

  • Declare a variable for the boolean.  Add the following at the top of the code section:
bool SomeoneWithBirthdayFound = false;
  • If someone with a birthday is found we need to change the boolean to true.  To do this place the following at the top of the foreach loop 
SomeoneWithBirthdayFound = true;
  • Add the following within the 'SendEmail' method, after the 'for' loop.
if(!SomeoneWithBirthdayFound)
{
    await this.ToastObj.Show(Toast[3]); 
}

As it wasn't being used we are going to repurpose (Toast[3]) by changing the text displayed to the user by replacing the existing 'Content' phrase with the following:

Content="No one with an upcoming birthday was found."

Save and Test

Save all files are run the app to test.  It will require entering appropriate contacts with various dates of birth to test all combinations of birthdays due 7, 14 and 21 days ahead (not forgetting contacts with birthdays due further into the future.

References

Code for this post: Next Birthday Code

Powered by Publii