Subversion Repositories VORC

#!/usr/bin/perl

use strict;
use cPanelUserConfig;
use RollerCon;
use tableViewer;
use DateTime;
use HTML::Tiny;
my $h = HTML::Tiny->new( mode => 'html' );

my $dt = DateTime->today;
my $day = $dt->day_name;
my $date = $dt->date;
my $year = $dt->year;

my $dbh = getDBConnection ();
my $DEPT = getDepartments ();

my $RCidListQuery=<<endRCidListQuery;
  select distinct RCid from (
        select distinct RCid from v_shift             where year(date) = year(curdate()) and dept <> "PER" and dept <> "CLA" union
    select distinct RCid from v_shift_announcer   where year(date) = year(curdate()) union
    select distinct RCid from v_shift_officiating where year(date) = year(curdate())
    ) t1
  where RCid <> ''
  order by cast(RCid as unsigned)
endRCidListQuery
my $sth1 = $dbh->prepare($RCidListQuery);

my $SchedQuery=<<endSchedQuery;
  select * from (
        select id, date, dayofweek,          location, time, volhours,          dept, role, '' as teams from v_shift             where year(date) = year(curdate()) and RCid = ? and dept <> "PER" and dept <> "CLA" union
    select id, date, dayofweek, track as location, time, volhours, "ANN" as dept, role,       teams from v_shift_announcer   where year(date) = year(curdate()) and RCid = ? union
    select id, date, dayofweek, track as location, time, volhours, "OFF" as dept, role,       teams from v_shift_officiating where year(date) = year(curdate()) and RCid = ?
      ) temp
  order by date, time
endSchedQuery
my $sth2 = $dbh->prepare($SchedQuery);

$sth1->execute();
while (my ($RCid) = $sth1->fetchrow_array()) {
        my $email = getUserEmail($RCid);
        my $dname = getUserDerbyName($RCid);
        my @shifts = ();
        my $hours;
        
        $sth2->execute($RCid, $RCid, $RCid);
        while (my $S = $sth2->fetchrow_hashref()) {
                push @shifts, $S;
                $hours += $S->{volhours};
        }
        
        my $message = $h->p ("Greetings $dname,", "Thanks for all your hard work at RollerCon. Here are the volunteer hours we captured for you:");

  $message .= $h->table ([
    map { $h->tr ([ $h->td ( $_->{date},
                             $_->{dayofweek},
                             $_->{time},
                             $_->{volhours}." hr(s)",
                             $DEPT->{$_->{dept}},
                             $_->{role},
                             $_->{teams} ) ]) } @shifts
  ]);
  $message .= $h->b ($hours." TOTAL HOURS");
#       foreach (@shifts) {
#         $message .= join " ", $_->{date}, sprintf ("%-9s", $_->{dayofweek}), $_->{time}, sprintf ("%-12s", $_->{volhours}." hr(s)"), $DEPT->{$_->{dept}},     $_->{role},     $_->{teams}."\n";
#               $message .= "$_->{date} $_->{dayofweek} $_->{time}  $_->{volhours} hr(s)  $DEPT->{$_->{dept}}   $_->{role}      $_->{teams}\n";
#       }
  
  $message .= $h->p (
    "Thanks for your time and work at RollerCon! Please review your hours listed above. If you see any discrepancies, please submit a ".$h->a ({ href=>"https://volunteers.rollercon.com/schedule/missing_hours.pl" }, "Missing Volunteer Time")." request immediately. We will process requests until <B>July 31st.</B>",
    "When we process yours, you will see the updates on ".$h->a ({ href=>"https://volunteers.rollercon.com/schedule/view_user.pl" }, "your user profile page")." in VORC. Submissions cannot be accepted after July 31st, so please handle this now - it's a quick form!",
    "Look for an email (to the address in your VORC account: this one!) before November 15th with redemption links for next year's free & discounted passes. See you next year!",
    "And thanks again. You're the one who keeps RollerCon rolling!!!!",
    $h->a ({ href=>"https://rollercon.com/help-wanted/" }, "More about freebies & discounts"),
    $h->hr.$h->p ({ style=>"text-align:center" }, $h->b ("Volunteer Compensation")).$h->hr,
    "All RollerCon volunteers get cool stuff at RollerCon, including a raffle ticket, limited-edition volunteer-only tee (while they last), and other schwag, snacks, goodwill and more every year, plus cool stuff from our sponsors.",
    $h->ul ([
      $h->li (
        $h->b ("5-19 hours")." qualifies you for a discounted pass that can be redeemed at a lower price than we ever sell until June.",
        $h->b ("20 hours and over")." gets you a free pass plus all the other stuff above."
        )
    ]),
    $h->br,
    "--RollerCon HQ".$h->br.'rollercon@gmail.com'.$h->br."rollercon.com"
  );


#       $message .= "\n$hours TOTAL HOURS\n\nAgain, thank you for volunteering at RollerCon.\n\n-RollerCon Leadership Team";
        
        my $subject = $dname."'s $year RollerCon Volunteer Hours Summary";
        
        print "Emailing $RCid. $dname ($email)...\n";
        use RCMailer;
        EmailUser($email, $subject, $message);
        sleep (15);
}

print "\ndone.\n\n";