Subversion Repositories ORC

#!/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 report them ".$h->a ({ href=>"https://docs.google.com/forms/d/e/1FAIpQLSdbzcuEz2k_6ycf826-R3lGc3L37mfENUm7pQXxlFPb2LlvWA/viewform"}, "HERE")." before August 31st. We'll update our records based on your form entries, and you will see the updates on your home page in VORC. Entries made after August can't be updated in VORC. So maybe you should update it now - it's a short form!",
    "Then in October, we'll send out free / discounted info for RollerCon 2023 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 ("10-25 hours")." qualifies you for a discounted pass that can be redeemed at a lower price than we ever sell until June.",
        $h->b ("Over 25 hours")." 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 RollerCon Volunteer Hours Summary";
        
        print "Emailing $dname ($email)...\n";
        use RCMailer;
        EmailUser($email, $subject, $message);
        sleep (15);
}

print "\ndone.\n\n";