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 $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 (
    "Do you see any discrepancies? If so, please <b>forward this email</b> (do not reply) <i>before</i> September 30th to:  rollerconstaffing\@gmail.com, rollercon\@gmail.com<br>\n...and then in the body of the email tell us why we're updating your hours.",
    "We'll update our records, and you will see the updates on your user profile page in VORC. Emails received after September 29th can't be updated in VORC, so maybe take care of it right now - it's a quick email!",
    "Then in October, we'll send out free / discounted info for next year's RollerCon to the email you listed in your profile in ".$h->a ({ href=>"https://volunteers.rollercon.com/schedule/" }, "VORC").".  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 $dt->year RollerCon Volunteer Hours Summary";
        
        print "Emailing $RCid. $dname ($email)...\n";
        use RCMailer;
        EmailUser($email, $subject, $message);
        sleep (15);
}

print "\ndone.\n\n";