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";