We’re almost at the end of the SQL side of things, just needing to send out notifications/reminders. I was torn here, I already had a proc to send a link to a single user, but using it here would mean a loop. Even if it was slow it wouldn’t matter, we do a handful of votes in a year, but just couldn’t do it! I get good reuse out of MergeBallotInfo. Note that I’m passing in a schedule ID, will show you that next.

ALTER proc [Voting].[NotifyVotersOfBallot] @ScheduleID uniqueidentifier

as

declare @Rows int
declare @Template varchar(max)

set nocount on
set xact_abort on

–make sure we don’t send twice
if not exists (select * from Voting.NotificationSchedule where ScheduleID = @ScheduleID and DateSent is null)
    begin
        raiserror (‘This schedule item has already been processed. Add a new schedule if you want to send again’, 16, 1)
        return
    end

–get the template once
select
    @Template = TemplateHTML
from Voting.Templates
where
    TemplateName = ‘BallotEmail’

begin transaction

insert into Voting.Email (
    MsgFrom,
    MsgTo,
    Subject,
    Message,
    Priority,
    DBName)
select
    ‘hq@sqlpass.org’,
    EV.EmailAddress,
    ‘PASS Vote: ‘ + B.Title,
    Voting.MergeBallotInfo (@Template, B.BallotID, EV.VoterID, B.Title, B.Description, B.ClosingDate),
    2,
    db_name()
from Voting.EligibleVoters EV inner join Voting.Ballot B on EV.BallotID = B.BallotID
inner join Voting.NotificationSchedule NS on B.BallotID = NS.BallotID
where
    EV.HasVoted = 0
    and B.ClosingDate > GetUTCDate()
    and NS.ScheduleID = @ScheduleID
set @Rows = @@Rowcount

–mark as done
update Voting.NotificationSchedule set
    DateSent = getutcdate(),
    NumberSent = @Rows
where
    ScheduleId = @ScheduleID

commit transaction

I wanted to make this as set and forget as possible, and it’s not as easy as saying each ballot gets 2 reminders. I created this table which can be populated with as many rows as needed, but I’d guess the average will be 2-3. I logged the number of emails sent which may be overkill, but it’s a nice way to know that things happened as expected.

I didn’t avoid loops entirely. I could have written the above to handle all notifications that were due at once (rarely more than one), but I hate not having the ability to just send one if I need to – say a quick resend if something has failed. I’m using a read only cursor, and it’s really a just in case solution, doesn’t cost much to code or execute in this context.

ALTER proc [Voting].[VotingProcessSchedule]

as

declare @ScheduleId uniqueidentifier

set nocount on

DECLARE curSchedule CURSOR
READ_ONLY
FOR SELECT scheduleid from Voting.NotificationSchedule
where
    Datesent is null
    and DateToSend > GetUTCDate()

OPEN curSchedule

FETCH NEXT FROM curSchedule INTO @ScheduleId
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        exec Voting.NotifyVotersOfBallot @ScheduleID

    END
    FETCH NEXT FROM curSchedule INTO @ScheduleId
END

CLOSE curSchedule
DEALLOCATE curSchedule

Finally, we’ll have to do some reporting, which for now consists of selecting from the view, but not a big thing to add a simple RS report to finish things up:

CREATE view [Voting].[BallotResults]

as

select
    b.BallotID,
    b.Title,
    bd.Title as BallotOption,
    isnull(a.VotesCast,0) as VotesCast
from Voting.Ballot B inner join Voting.BallotDetail BD
on B.BallotID = BD.BallotID
left join
(
select
    BallotDetailID,
    count(*) as VotesCast
from Voting.VotesCast
group by
    BallotDetailID)
a on BD.BallotDetailID = A.BallotDetailID

For security there is a role called Voter, and I’ve granted execute on the procs that can be called from the web app. Not much else to it. Next post will look at some of the UI pieces.