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;

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

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

my $user = getUser($EML);

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

my $RCid = $user->{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_lead_shift';

our @allFields = (qw(id date dayofweek track time assignee_id derby_name));

our @defaultFields = (qw(date track time derby_name));

our @displayFields = ();

our @hideFields = ();

our %NAME = (

	"id" => "ID",

	"date" => "Date",

	"dayofweek" => "Day",

	"track" => "Track",

	"time" => "Time",

	"assignee_id" => "RCid",

	"derby_name" => "Name"

	);

our %colOrderHash = (

	"id" => 5,

	"date" => 10,

	"dayofweek" => 12,

	"track" => 15,

	"time" => 25,

	"assignee_id" => 30,

	"derby_name" => 35

		);

our %colFilterTypeHash = (

	"id" => "number",

	"date" => "date",

	"dayofweek" => "select",

	"track" => "select",

	"time" => "text",

	"assignee_id" => "number",

	"derby_name" => "select"

		);

my $orderby = "date, track, time";

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

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

}

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

{

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

	{

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

		@displayFields = split /:/,$disF;

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

		{

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

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

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

		}

		$FORM{autoload} = $al;

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

if ($FORM{excel})

{

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

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

        # Create a new Excel workbook

	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=>'RC_Lead_Shifts',

			-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 Track Leads</title></head>

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

	<form action="lead_shifts.pl" 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=720 bgcolor="#0077BD" height=60><FONT color=white face=verdana size=6><i><strong>&nbsp;Officiating Track Lead Schedule - $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";

 	 					}

					print<<header3;

						<TR>

							<TD colspan=4 align=center><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>

						</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>

				<TR>

					<TD colspan=$cw><P><P></TD>

				</TR>

header2

my $co = '#FFFFFF';

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

{

	if ($t->{assignee_id} == $RCid or ($LVL >= 3 and $t->{derby_name})) {

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

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

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

	}

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

	foreach $f (@displayFields)

		{

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

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

			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>

tail