Subversion Repositories ORC

#!/usr/bin/perl

######################################

#

#

# $Log: req.pl,v $

#

######################################

#if ($ENV{SHELL}) { die "This script shouldn't be executed from the command line!\n"; }

use CGI qw/:standard/;

use lib "/home/rollerco/perl5/lib/perl5";

use scanFunctions;

use RollerCon;

use Spreadsheet::WriteExcel;

use DateTime;

use DateTime::Duration;

my $cookie_string = authenticate(1) || die;

my ($EML, $PWD, $LVL) = split /&/, $cookie_string;

my $username = $ORCUSER->{derby_name};

my $RCid = $ORCUSER->{RCid};

my $RCAUTH_cookie = CGI::Cookie->new(-name=>'RCAUTH',-value=>"$cookie_string",-expires=>"+30m");

my $YEAR = 1900 + (localtime)[5]; #which year of data to display, default to current

our $DBTABLE = 'v_shift_admin_view';

our %NAME = qw(

		id						 ID

		teams					 Teams

		date					 Date

		dayofweek			 Day

		time					 Time

		track					 Track

		level					 Level

		restrictions   Rs

		gtype					 Type

		notes  				 Notes

		role					 Role

		tla						 TLA

		name					 Position

		type					 Class

		RCid					 RCid

		derby_name     Assignee

	);

our %colOrderHash = qw(id 1 track 2 date 3 dayofweek 4 time 5 teams 6 level 7 restrictions 8 gtype 9 notes 10 role 11 tla 12 name 13 type 14 RCid 15 derby_name 16);

my $orderby = "date, track, time, gtype, type, tla, derby_name";

our @allFields = sort { $colOrderHash{$a} <=> $colOrderHash{$b} } keys %NAME;

our @defaultFields = (qw(date time track teams level gtype tla type derby_name));

our @displayFields = ();

our @hideFields = ();

our %colFilterTypeHash = qw(

		id						 number

		teams					 text

		date					 date

		dayofweek			 select

		time					 text

		track					 select

		level					 select

		restrictions   select

		gtype					 select

		notes  				 text

		role					 select

		tla						 select

		name					 text

		type					 select

		RCid					 number

		derby_name     select

	);

if ($LVL > 1) {

# Add:

#		real_name			 RealName

#		email					 Email

	$NAME{'real_name'} = "RealName";

	$NAME{'email'} = "EMail";

	$colOrderHash{'real_name'} = 17;

	$colOrderHash{'email'} = 18;

	push @allFields, qw(real_name email);

	$colFilterTypeHash{'real_name'} = 'text';

	$colFilterTypeHash{'email'} = 'text';

}

foreach (param())

{

	if (/^year$/) { #

		$YEAR = param($_);

		next;

	}

	$FORM{$_} = param($_);				# Retrieve all of the FORM data submitted

	if ((/^filter/) and ($FORM{$_} ne ''))		# Build a set of filters to apply

		{

		my ($filter,$field) = split /-/, $_;

		$FILTER->{$field} = $FORM{$_};

		}

	elsif ($FORM{$_} eq "true")	{		# Compile list of fields to display

	  if ($_ ne "shiftinclude") {

		  push @displayFields, $_;

		}

	}			# @displayFields is declared in scanFunctions.pm

}

if (exists $FORM{autoload})				# If the FORM was submitted (i.e. the page is being redisplayed),

{							#	build the data for the cookie that remembers the page setup

	my $disFields = join ":", @displayFields;

	my @filters;

	my @f = keys %{$FILTER};

	foreach $key (@f)

		{ push @filters, "$key=$FILTER->{$key}"; }

	my $fils = join ":", @filters;

	$QUERY_STRING = "$disFields\&$fils\&$FORM{autoload}&$FORM{shiftinclude}";

}

if (!(exists $FORM{autoload}))				# No FORM was submitted, suppply a default list of columns

{

	if (my $prefs = cookie('RCSHIFTS'))	# Has this user been here before and saved a cookie?

	{

		my ($disF, $filts, $al, $si) = split /&/,$prefs;

		@displayFields = split /:/,$disF;

		foreach $pair (split /:/, $filts)

		{

			my ($key, $value) = split /=/, $pair;

			$FORM{"filter-$key"} = $value;

			$FILTER->{$key} = $value;

		}

		$FORM{autoload} = $al;

		$FORM{shiftinclude} = $si;

		$QUERY_STRING = $prefs;

	}

	else

		{ @displayFields = @defaultFields; }

}

							# Build the field lists to display and hide columns.

							#	If the field isn't in the displayFields list,

							#	then add it to the hideFields list.

							#

@displayFields = sort byfield @displayFields;

foreach $field (@allFields) { if (! &inArray($field, \@displayFields)) { push @hideFields, $field; } }

my @whereClause = ("year(date) = '$YEAR'");					# Process the filters to build the components of the where clause

foreach $field (@allFields)

{

	if (! &inArray($field, \@hideFields))

	{

		if ($FILTER->{$field})

		{

			push @whereClause, &generica($field, $FILTER->{$field});

			delete $FILTER->{$field};

		}

	}

}

							#  Given the fields to display and the where conditions,

							#	"getData" will return a reference to an array of

							#	hash references of the results.

my @ProductList = @{&getData(\@displayFields, \@whereClause, $DBTABLE, $orderby)};

my $x = scalar @ProductList;

my @shifts;

if ($FORM{shiftinclude} eq "true") {

  my @SIWhere = ("year(date) = '$YEAR'");

  push @SIWhere, "RCid = $ORCUSER->{RCid}";

  @shifts = @{&getData(\@displayFields, \@SIWhere, $DBTABLE, $orderby)};

}

if ($FORM{excel})

{

	my $date = `date +"%m%d%y%H%M%S"`; chomp $date;

#	$filename = `dirname $ENV{REQUEST_URI}`; chomp $filename; $filename .= "/schedule/xls/${date}_$$.xls";

	$filename = "/schedule/xls/${date}_$$.xls";

        # Create a new Excel workbook

#	my $workbook = Spreadsheet::WriteExcel->new("/var/www/html${filename}");

	my $workbook = Spreadsheet::WriteExcel->new("/home/rollerco/officials.rollercon.com${filename}");

        # Add a worksheet

        my $worksheet = $workbook->add_worksheet();

#	open my $fh, '>', \my $str or die "Failed to open filehandle: $!";

#	my $workbook  = Spreadsheet::WriteExcel->new($fh);

#	my $worksheet = $workbook->add_worksheet();

	my $format = $workbook->add_format();

	$format->set_bold();

	my $col = $row = 0;

	foreach $f (@displayFields)

		{ $worksheet->write($row, $col++, "$NAME{$f}", $format); }

	foreach $t (sort @ProductList)			# Unt now we print the tickets!

	{

		$col = 0;

		$row++;

		foreach $f (@displayFields)

			{

				$f =~ s/^HOST\.//;

				$f =~ s/^FRAME\.//;

				$t->{$f} =~ s/<br>/\n/ig; $worksheet->write($row, $col++, "$t->{$f}");

			}

	}

	$workbook->close();

}

my $path = `dirname $ENV{REQUEST_URI}`; chomp $path; $path .= '/';

my $queryCookie = cookie(-NAME=>'RCSHIFTS',

			-VALUE=>"$QUERY_STRING",

			-PATH=>"$path",

			-EXPIRES=>'+365d');

#my $Auth_Cook = cookie(-NAME=>'RequestToolAuthorized',

#			-VALUE=>"$authCookie",

#			-PATH=>'/cbrt/cgi-bin/');

print header(-cookie=>[$RCAUTH_cookie,$queryCookie]);

 	print "<!-- FORM \n\n";				# Debug code to dump the FORM to a html comment

	print "I'm catching updates!!!\n\n";

	foreach $key (sort (keys %FORM))		#	Must be done after the header is written!

 		{ print "\t$key:  $FORM{$key}\n"; }

 	print "--> \n\n";

#

#

# 	print "<!-- ENV \n\n";				# Debug code to dump the ENV to a html comment

# 	foreach $key (sort (keys %ENV))			#	Must be done after the header is written!

# 		{ print "\t$key:  $ENV{$key}\n"; }

# 	print "--> \n\n";

#

# 	print "\n\n\n\n<!-- $QUERY_STRING --> \n\n\n\n";

#------------------

if ($FORM{autoload})					  # Toggle the autoload fields within the table elements

	{ $auto = "onClick='submit();'";

	 $auto2 = "onChange='submit();'"; }

else

	{ $auto = "";

	 $auto2 = ""; }

my $signedOnAs = $username ? "You are currently signed in as $username. <font size=-2><a href='index.pl' onClick=\"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/';return true;\">[Log Out]</a></font>" : "You are not signed in.";

my $xlsLink = $filename ? "&nbsp; &nbsp; <A href='$filename'><FONT color='#0077BD'>Download Now.</FONT></a>" : "";

my $yearoptions;

foreach (@{&getYears()}) {

	$yearoptions .= $YEAR eq $_ ? "<option selected>$_</option>" : "<option>$_</option>";

}

print<<header;

	<html><head><title>RollerCon Officials Schedule Manager - $YEAR Game Assignments</title></head>

	<body text="#000000" bgcolor="#FFFFFF" link="#000000" vlink="#000000" alink="#FF0000">

	<form action="$ENV{SCRIPT_NAME}" method=POST name=Req>

	<input type=hidden name=excel value=0>

	<TABLE cellpadding=0 cellspacing=0 hspace=0 vspace=0 border=0>

		<TR>

			<TD>

			<TABLE cellpadding=0 cellspacing=0 hspace=0 vspace=0 border=0>

				<TR>

					<TD width=680 bgcolor="#0077BD" height=60><FONT color=white face=verdana size=6><i><strong>&nbsp;RollerCon Game Assignments - $YEAR</strong></i></font></TD>

					<TD><img SRC="/images/headerblank.gif" NOSAVE height=38 width=165></TD>

				</TR>

			</TABLE>

			</TD>

		<TR>

			<TD>&nbsp</TD>

		</TR>

		<TR>

			<TD>

			<TABLE cellpadding=0 cellspacing=0 hspace=0 vspace=0 border=0>

				<TR>

					<TD rowspan=2 align=left width=610>

					<TABLE border=0 cellspacing=0>

header

							# Print the Hidden fields' check boxes

					my $tc = 1;

					foreach $field (sort { $NAME{$a} cmp $NAME{$b}; } @hideFields)

						{

						if ($tc == 1)

							{print "\t\t\t\t\t\t<TR>\n\t\t\t\t\t\t\t<TD width=25% nowrap><FONT face=verdana size=1><b><INPUT type=checkbox name=$field value=true $auto>$NAME{$field}</TD>\n"; $tc++;}

						elsif ($tc == 4)

							{print "\t\t\t\t\t\t\t<TD width=25% nowrap><FONT face=verdana size=1><b><INPUT type=checkbox name=$field value=true $auto>$NAME{$field}</TD>\n\t\t\t\t\t\t</TR>\n"; $tc=1;}

						else

							{print "\t\t\t\t\t\t\t<TD width=25% nowrap><FONT face=verdana size=1><b><INPUT type=checkbox name=$field value=true $auto>$NAME{$field}</TD>\n"; $tc++;}

						}

 					if ($FORM{autoload})

 						{

 						$trueChecked = "checked";

 						$falseChecked = "";

 						}

 					else

 						{

 						$trueChecked = "";

 						$falseChecked = "checked";

 	 					}

 	 				my $SIChecked="";

 	 				if ($FORM{shiftinclude}) { $SIChecked = "checked"; }

					print<<header3;

						<TR>

							<TD colspan=2 align=left><FONT face=verdana size=1 color=#0077BD><B>

							Autoload: <INPUT type=radio name=autoload value=1 onClick='Req.submit();' $trueChecked>On  <INPUT type=radio name=autoload value=0 onClick='Req.submit();' $falseChecked>Off

							</TD>

							<TD colspan=2 align=right><FONT face=verdana size=1 color=#0077BD><B>

							Show my shifts: <input type=checkbox name=shiftinclude value=true $SIChecked $auto>

							</TD>

						</TR>

					</TABLE>

					</TD>

					<TD nowrap>

						<A HREF='' onClick="window.document.Req.submit(); return false;"><IMG SRC='/images/refresh.button.gif' border=0></A><br>

						<strong><a href='' onClick="window.document.Req.excel.value=1; window.document.Req.submit(); return false;"><FONT face=verdana size=1>Export Displayed Data as Excel Document.</a>$xlsLink</font>

					</TD>

				</TR>

				<TR>

					<TD align=left><B><FONT face=verdana size=1>$signedOnAs<br>

					Display <A HREF='' onClick="window.document.Req.method = 'GET'; window.document.Req.submit(); return false;">Full URL</a> : <select name=year onchange='Req.submit();'>$yearoptions</select> : <a href=/schedule/>[Go HOME]</a></TD>

				</TR>

			</TABLE>

			</TD>

		</TR>

		<TR>

			<TD>&nbsp</TD>

		</TR>

		<TR>

			<TD>

			<TABLE border=0 cellspacing=2 cellpadding=4 width=100\%>

				<TR bgcolor=#0077BD>

header3

							# Print the Column headings

				foreach $f (@displayFields)

					{ print "\t\t\t\t\t<TD align=left nowrap><FONT face=verdana color=white size=1><B><INPUT type=checkbox name=$f value=true checked $auto>$NAME{$f}</TD>\n"; }

				print "\t\t\t\t</TR>\n\t\t\t\t<TR>\n";

							# and now the filter boxes

				foreach $f (@displayFields)

					{

					print "\t\t\t\t\t<TD align=left bgcolor=#E6E6E6><FONT size=1 face=verdana>";

					print &generica($f);

#					print " ";

					print "</TD>\n";

					}

				print "\t\t\t\t</TR></FORM>\n";

				my $cw = scalar @displayFields;

				print<<header2;

				</TR>

header2

my $co = '#FFFFFF';

my $now = DateTime->now;

$now->set_time_zone('America/Los_Angeles');

if ($SIChecked) {

  print "<TR style='border:solid 1px #555'>";

  foreach my $t (@shifts)			# Unt now we print the tickets!

  {

  	my ($yyyy, $mm, $dd) = split /\-/, $t->{date};

  	my $cutoff = DateTime->new(

          year => $yyyy,

          month => $mm,

          day => $dd,

          hour => 5,

          minute => 0,

          second => 0,

          time_zone => 'America/Los_Angeles'

          );

  	if (($t->{RCid} == $RCid and $t->{gtype} ne "selected staffing" and $now < $cutoff) or ($LVL >= 2 and $t->{derby_name})) {

  		# DROP

  		$t->{derby_name} = "$t->{derby_name} <A HREF='#' onClick=\"window.open('make_shift_change.pl?change=del&RCid=$t->{RCid}&id=$t->{id}&role=$t->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false;\">[DROP]</a>";

  		if ($LVL >= 2) {

  		  $t->{derby_name} .= " | <A HREF='#' onClick=\"if (confirm('Really? They were a no show?')==true) { window.open('make_shift_change.pl?noshow=true&change=del&RCid=$t->{RCid}&id=$t->{id}&role=$t->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false; }\">[NO SHOW]</a>";

  		}

  	} elsif (!$t->{derby_name}) {

  		if (signUpEligible($ORCUSER, $t) and $now < $cutoff) {

  			# SIGN UP

  			$t->{derby_name} = "<A HREF='#' onClick=\"window.open('make_shift_change.pl?change=add&RCid=$RCid&id=$t->{id}&role=$t->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false;\">[SIGN UP]</a>";

  		}

  		if ($LVL >= 2) {

  			# ADD USER

  			$t->{derby_name} ? $t->{derby_name} .= " | " : {};

  			$t->{derby_name} .= "<A HREF='#' onClick=\"window.open('make_shift_change.pl?change=lookup&RCid=$RCid&id=$t->{id}&role=$t->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false;\">[ADD USER]</a>";

  		}

  	}

  	print "\t\t\t\t<TR>\n";

  	foreach $f (@displayFields)

  		{

  			print "\t\t\t\t\t<TD align=left valign=top bgcolor='#ffff99'><FONT face=verdana size=1>".$t->{$f}."&nbsp</TD>\n";

  		}

  	print "\t\t\t\t</TR>\n";

  }

  print "</tr><TR><td colspan=$cw><hr></td></tr>";

}

foreach $t (@ProductList)			# Unt now we print the tickets!

{

	my ($yyyy, $mm, $dd) = split /\-/, $t->{date};

	my $cutoff = DateTime->new(

        year => $yyyy,

        month => $mm,

        day => $dd,

        hour => 5,

        minute => 0,

        second => 0,

        time_zone => 'America/Los_Angeles'

        );

	if (($t->{RCid} == $RCid and $t->{gtype} ne "selected staffing" and $now < $cutoff) or ($LVL >= 2 and $t->{derby_name})) {

		# DROP

		$t->{derby_name} = "$t->{derby_name} <A HREF='#' onClick=\"window.open('make_shift_change.pl?change=del&RCid=$t->{RCid}&id=$t->{id}&role=$t->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false;\">[DROP]</a>";

		if ($LVL >= 2) {

		  $t->{derby_name} .= " | <A HREF='#' onClick=\"if (confirm('Really? They were a no show?')==true) { window.open('make_shift_change.pl?noshow=true&change=del&RCid=$t->{RCid}&id=$t->{id}&role=$t->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false; }\">[NO SHOW]</a>";

		}

	} elsif (!$t->{derby_name}) {

		if (signUpEligible($ORCUSER, $t) and $now < $cutoff) {

			# SIGN UP

			$t->{derby_name} = "<A HREF='#' onClick=\"window.open('make_shift_change.pl?change=add&RCid=$RCid&id=$t->{id}&role=$t->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false;\">[SIGN UP]</a>";

		} # else { $t->{derby_name} = signUpEligible($ORCUSER, $t); }

		if ($LVL >= 2) {

			# ADD USER

			$t->{derby_name} ? $t->{derby_name} .= " | " : {};

			$t->{derby_name} .= "<A HREF='#' onClick=\"window.open('make_shift_change.pl?change=lookup&RCid=$RCid&id=$t->{id}&role=$t->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false;\">[ADD USER]</a>";

		}

	}

	print "\t\t\t\t<TR>\n";

	foreach $f (@displayFields)

		{

		  if ($t->{RCid} eq $ORCUSER->{RCid}) {

			  print "\t\t\t\t\t<TD align=left valign=top bgcolor='#ffff99'><FONT face=verdana size=1>".$t->{$f}."&nbsp</TD>\n";

		  } else {

			  print "\t\t\t\t\t<TD align=left valign=top bgcolor='$co'><FONT face=verdana size=1>".$t->{$f}."&nbsp</TD>\n";

			}

		}

	print "\t\t\t\t</TR>\n";

	if ($co eq '#FFFFFF')

		{ $co = '#F9F9F9'; }

	else

		{ $co = '#FFFFFF'; }

}

print<<tail;

			</TABLE>

			</TD>

		</TR>

	</TABLE>

	<br><br>

	<FONT face=verdana size=2><B>$x Record(s) Displayed</font>

	<BR><BR>

	<FONT face=verdana size=1><B>This page was displayed on $now<BR>

	Please direct questions, problems, and concerns to <FONT face=verdana color=#0077BD>officials.rollercon.schedule\@gmail.com</FONT>

	<SCRIPT language="JavaScript">

	<!--

	var ticket_window, severity_window, user_window;

	function NewWindow () {

		if ((ticket_window == null) || (ticket_window.closed == true))

			ticket_window = open(\"\",\"Details\",\"width=650,height=650,menubar,scrollbars,resizable\");

		ticket_window.focus();

		return true;

		}

	function SevWindow () {

		if ((severity_window == null) || (severity_window.closed == true))

			severity_window = open(\"\",\"SevDetails\",\"width=500,height=300,menubar\");

		severity_window.focus();

		return true;

		}

	function UserWindow () {

		if ((user_window == null) || (user_window.closed == true))

			user_window = open(\"\",\"UserDetails\",\"width=310,height=115,menubar\");

		user_window.focus();

		return true;

		}

	//-->

	</SCRIPT>

</body></html>

tail