[LON-CAPA-cvs] cvs: modules /raeburn/register registrations.pm
raeburn
lon-capa-cvs@mail.lon-capa.org
Sat, 04 Feb 2006 01:09:00 -0000
This is a MIME encoded message
--raeburn1139015340
Content-Type: text/plain
raeburn Fri Feb 3 20:09:00 2006 EDT
Added files:
/modules/raeburn/register registrations.pm
Log:
Provides web interface for authorized administrators to view registration information for LON-CAPA events. Also creates Excel file of registration data.
--raeburn1139015340
Content-Type: text/plain
Content-Disposition: attachment; filename="raeburn-20060203200900.txt"
Index: modules/raeburn/register/registrations.pm
+++ modules/raeburn/register/registrations.pm
package Apache::LON::registrations;
use strict;
use Apache::RequestRec();
use Apache::Const qw(OK SERVER_ERROR);
use HTML::Entities;
use DBI;
use LWP;
use CGI::Apache qw(:standard);
use Apache::LON::processform;
use Spreadsheet::WriteExcel::Simple;
sub handler {
my $r = shift;
my $dbpwd;
my $authkeydir = "/home/helpdesk/admindata";
my $authkeyfile = $authkeydir.'/mysql.dat';
if (open (my $fh, "<$authkeyfile") ) {
$dbpwd = <$fh>;
close($fh);
chomp($dbpwd);
}
my %attr = (
data_source => 'dbi:mysql:loncapasupport',
username => 'support',
password => $dbpwd
);
# connect to database
my $dbh = DBI->connect($attr{data_source}, $attr{username},
$attr{password});
unless ($dbh) {
return SERVER_ERROR;
}
my $user = $ENV{'REMOTE_USER'};
if ($user eq '') {
return SERVER_ERROR;
}
my $quoted_user = $dbh->quote( $user );
my %params = ();
my %qry_items = ();
my @rowitems = ();
my @rowtitles = ();
&Apache::LON::processform::postitems($r,\%qry_items);
&Apache::LON::processform::getitems($r->args,\%qry_items);
foreach (keys %qry_items) {
if (@{$qry_items{$_}} > 1) {
@{$params{$_}} = @{$qry_items{$_}};
for (my $i=0; $i<@{$params{$_}}; $i++) {
$params{$_}[$i] =~ s#(`)#'#g;
$params{$_}[$i] =~ s#\$#\(\$\)#g;
}
} else {
$params{$_} = $qry_items{$_}[0];
$params{$_} =~ s#`#'#g;
$params{$_} =~ s#\$#\(\$\)#g;
}
}
$r->content_type('text/html');
$r->send_http_header;
my $exitpage = "http://support.loncapa.org/";
my %form_elements = ();
my @curr_workshops = ();
my @regitems = ();
my @workshop = ();
my @info = ();
my @sponsors = ();
my %sponsorinfo = ();
my ($page,$year,$event,$portal,$returnpage,$event_id,$webpath,$year_quoted,$event_quoted);
$portal = "/registrations";
if (exists $params{'return'}) {
$returnpage = $params{'return'};
}
if (exists $params{'year'}) {
if (ref($params{'year'}) eq 'ARRAY') {
$year = $params{'year'}[0];
} else {
$year = $params{'year'};
}
$year_quoted = $dbh->quote( $year );
}
if (exists $params{'event'}) {
if (ref($params{'event'}) eq 'ARRAY') {
$event = $params{'event'}[0];
} else {
$event = $params{'event'};
}
$event_quoted = $dbh->quote( $event );
}
if ($year && $event) {
if (!&authorized($dbh,$quoted_user,$event_quoted,$year_quoted)) {
&invalid_event($r,$dbh,$page,$year,$event,$portal,$returnpage,$exitpage,\@sponsors,\%sponsorinfo,$quoted_user);
return OK;
}
my $sth = $dbh->prepare("SELECT name,url,image_path,orientation FROM event_sponsors WHERE event_id = '$event_id' ORDER BY position");
$sth->execute();
while (my ($name,$url,$image_path,$orientation) = $sth->fetchrow_array) {
push @sponsors, $name;
%{$sponsorinfo{$name}} = ();
$sponsorinfo{$name}{url} = $url;
$sponsorinfo{$name}{image_path} = $image_path;
$sponsorinfo{$name}{orientation} = $orientation;
}
$sth->finish;
($event_id,$webpath) = $dbh->selectrow_array("SELECT event_id,webpath FROM event_config WHERE year=$year_quoted AND event=$event_quoted");
if ($event_id eq '') {
&invalid_event($r,$dbh,$page,$year,$event,$portal,$returnpage,$exitpage,\@sponsors,\%sponsorinfo,$quoted_user);
return OK;
} else {
# get items that should appear in event_registration table
my $sth = $dbh->prepare("SELECT name FROM event_formfields WHERE event_id = '$event_id' AND tablename = 'event_registration'");
$sth->execute;
while (my $name = $sth->fetchrow_array) {
push @regitems, $name;
}
$sth->finish;
# get items that should appear in support_user table
$sth = $dbh->prepare("SELECT name FROM event_formfields WHERE event_id = '$event_id' AND tablename = 'support_user'");
$sth->execute;
while (my $name = $sth->fetchrow_array) {
push @info, $name;
}
$sth->finish;
if (!grep/^created$/,@info) {
push @info, 'created';
}
# get items that should appear in workshop_registration table
$sth = $dbh->prepare("SELECT name FROM event_formfields WHERE event_id = '$event_id' AND tablename = 'workshop_registration'");
$sth->execute;
while (my @row = $sth->fetchrow_array) {
if (@row > 0) {
push @workshop, @row;
}
}
$sth->finish;
# get formfield values
$sth = $dbh->prepare("SELECT name,v_position,h_position,required,title,rowtitle,tablename FROM event_formfields WHERE event_id = '$event_id'");
$sth->execute();
while (my ($name,$v_position,$h_position,$required,$title,$rowtitle,$tablename) = $sth->fetchrow_array) {
$form_elements{$name}{'v_position'} = $v_position;
$form_elements{$name}{'h_position'} = $h_position;
$form_elements{$name}{'required'} = $required;
$form_elements{$name}{'title'} = $title;
$form_elements{$name}{'rowtitle'} = $rowtitle;
$form_elements{$name}{'tablename'} = $tablename;
my $vpos = $v_position-1;
my $hpos = $h_position-1;
unless(exists($rowitems[$vpos])) {
@{$rowitems[$vpos]} = ();
$rowtitles[$vpos] = $form_elements{$name}{'rowtitle'};
}
$rowitems[$vpos][$hpos] = $name;
}
$sth->finish;
}
} else {
&invalid_event($r,$dbh,$page,$year,$event,$portal,$returnpage,$exitpage,\@sponsors,\%sponsorinfo,$quoted_user);
return OK;
}
my $contact_email = "helpdesk\@lon-capa.org";
my $contact_name = "LON-CAPA support team";
# Special entries for username and modified
my $fields = @rowitems;
@{$rowitems[$fields+1]} = ('user_id');
$rowtitles[$fields+1] = 'Username';
@{$rowitems[$fields]} = ('modified');
$rowtitles[$fields] = 'Date last modified';
my $extracount = $fields;
my %extrafields = (
user_id => 'Username',
modified => 'Date last modified',
);
foreach my $extra (sort(keys(%extrafields))) {
$extracount ++;
$form_elements{$extra}{'v_position'} = $extracount;
$form_elements{$extra}{'h_position'} = 1;
$form_elements{$extra}{'required'} = 'N';
$form_elements{$extra}{'title'} = $extrafields{$extra};
$form_elements{$extra}{'rowtitle'} = $extrafields{$extra};
$form_elements{$extra}{'tablename'} = 'event_registration';
my $vpos = $extracount-1;
my $hpos = 0;
unless(exists($rowitems[$vpos])) {
@{$rowitems[$vpos]} = ();
$rowtitles[$vpos] = $form_elements{$extra}{'rowtitle'};
}
$rowitems[$vpos][$hpos] = $extra;
}
# figure out what page we're on and where we're heading.
$page = $params{'page'};
my $command = $params{'go'};
my $current_page = &calculate_page($page,$command);
if ($current_page == 2) {
&display_two($r,$event,$year,$event_id,\%params,$user,$portal,$returnpage,$exitpage,\@info,\@workshop,$dbh,$current_page,$contact_email,$contact_name,\%form_elements,\@rowitems,\@rowtitles,\@sponsors,\%sponsorinfo,$webpath);
} else {
&display_one($r,$event_id,\%params,$user,$portal,$returnpage,$exitpage,\@info,\@workshop,$dbh,$current_page,$contact_email,$contact_name,\%form_elements,\@rowitems,\@rowtitles,\@sponsors,\%sponsorinfo);
}
return OK;
}
sub calculate_page() {
my ($prev,$dir) = @_;
return 1 if $prev eq ''; # start with first page
return $prev + 1 if $dir eq 'NextPage';
return $prev - 1 if $dir eq 'PreviousPage';
}
sub authorized {
my ($dbh,$quoted_user,$event_quoted,$year_quoted) = @_;
my $statement = "SELECT COUNT(ea.event_id) FROM event_admin ea, event_config ec WHERE (ea.user_id = $quoted_user AND ea.event_id = ec.event_id AND ec.year = $year_quoted AND ec.event = $event_quoted)";
my $admincount = $dbh->selectrow_array("$statement");
return $admincount;
}
sub invalid_event {
my ($r,$dbh,$page,$year,$event,$portal,$returnpage,$exitpage,$sponsors,$sponsorinfo,$quoted_user) = @_;
if ($portal eq '') {
$portal = $exitpage;
}
my $logoutportal = $portal;
if ($logoutportal =~ /^\/update/) {
$logoutportal = '/register';
}
$r->print(<<"END_OF_A");
<html><head><title>View LON-CAPA Event Registrations</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
buttonred
{
font-size: 13px; font-family: arial, Trebuchet MS, verdana, helvetica, san-serif; padding: 4px;
line-height: 15px; color: #FFFFFF; text-align: center; background: #CC6633; font-weight: bold;
}
-->
</style>
</head>
<body bgcolor="#ffffff" link="#003333" alink="#003333" vlink="#003333">
END_OF_A
&main_box($r,$year,$event);
$r->print(<<"END_OF_B");
<table cellpadding="0" cellspacing="0" width="100%" border="0">
<tr bgcolor="#ffffff">
<td><font face="Arial,Helvetica,sans-serif">
<a HREF="http://loncapa.org/">Home</a> --> <a href="$exitpage">Support</a> --> <b>Choose an event</b></font>
</td>
<td> </td>
<td align="right" valign="top">
<img border="0" src="/images/login/logout_red.gif" width="90" height="23" align="right" usemap="#event_header">
<map name = "event_header">
<area shape='rect' coords=10,2,80,18' href="/logout?portal=$logoutportal">
<area shape = 'default' nohref>
</map>
</td>
</tr>
</table>
END_OF_B
my $admincount = $dbh->selectrow_array("SELECT COUNT(event_id) FROM event_admin WHERE user_id = $quoted_user");
if (!$admincount) {
$r->print(<<"END_OF_C");
<table border="0" width="100%">
<tr>
<td> </td>
<td><font face='arial,helvetica,sans serif'>
<br /><br />Sorry, you are not currently authorized to view information about registration for any LON-CAPA conferences or workshops. Please use the LON-CAPA <a href="http://support.loncapa.org/help">support form</a> to request addition of your username to the list of administrators. In your request please include a list of the event(s) for which you would like to be able to access registration information.<br /><br />A list of all LON-CAPA events for which registration information is available is shown below.
<br /><br />
END_OF_C
} else {
$r->print(<<"END_OF_C");
<table border="0" width="100%">
<tr>
<td> </td>
<td><font face='arial,helvetica,sans serif'>
<br /><br />Click on the link to the LON-CAPA event for which you wish to display registration information:<br /><br />
END_OF_C
}
$r->print(<<"END_OF_D");
<table>
<tr>
<th>Event</th>
<th>Location</th>
<th>Dates</th>
</tr>
END_OF_D
my ($sth,$event_item);
if ($admincount > 0) {
$sth=$dbh->prepare("SELECT ec.event,ec.year,ec.eventstart,ec.eventend,ec.location,ec.webpath FROM event_config ec, event_admin ea WHERE (ea.user_id = $quoted_user AND ea.event_id = ec.event_id) ORDER BY ec.eventstart");
} else {
$sth=$dbh->prepare("SELECT event,year,eventstart,eventend,location,webpath FROM event_config ORDER BY eventstart");
}
$sth->execute;
while ( my ($event,$year,$eventstart,$eventend,$location,$webpath) = $sth->fetchrow_array) {
if ($admincount > 0) {
my $regurl = "event=$event&year=$year";
$regurl = &HTML::Entities::encode($regurl);
$regurl =~ tr/ /+/;
$event_item = "<a href='/registrations?$regurl'>LON-CAPA $event ($year)</a>";
} else {
$event_item = "LON-CAPA $event ($year)";
}
$r->print("<tr><td>$event_item</td><td>$location</td><td>$eventstart - $eventend</td></tr>");
}
$sth->finish;
$r->print(<<"END_OF_E");
</table>
</td>
</tr>
</table><br />
END_OF_E
&main_footer($r,$page,$year,$event,$returnpage,$sponsors,$sponsorinfo);
}
sub main_box {
my ($r,$year,$event) = @_;
$r->print(<<"END_OF_BOX");
<table border="0">
<tr>
<td align="middle">
<table width="800" BORDER="0" CELLSPACING="0" CELLPADDING="4" WIDTH="100%">
<tr>
<td colspan="4" valign="top" align="center" bgcolor="#CC6633">
<table border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td align="left">
<img border='0' src="/images/login/tmcc.jpg" width="420" height="60">
</td>
<td><font face="arial,helvetica,sans-serif" size="+2" color="#ffffff"><b>LON-CAPA <br />$event $year</b></font></td>
</tr>
</table>
</td>
</tr>
<tr>
<td width="8" bgcolor="#CC6633"> </td>
<td bgcolor="#FFFF99" width="100%" height="350" valign="top">
END_OF_BOX
}
sub main_footer() {
my ($r,$page,$year,$event,$returnpage,$sponsors,$sponsorinfo) = @_;
$r->print(<<"START_OF_FOOT");
</td>
<td bgcolor="#FFFF99" width="8"> </td>
<td width="8" bgcolor="#CC6633"> </td>
</tr>
<tr>
<td colspan="4" height="4" bgcolor="#CC6633"> </td>
</tr>
</table>
</td>
START_OF_FOOT
if (ref($sponsors) eq 'ARRAY') {
$r->print('<td valign="middle" align="right">');
foreach (@{$sponsors}) {
$r->print('<a href="'.$$sponsorinfo{$_}{url}.'"><img src="'.
$$sponsorinfo{$_}{image_path}.'" border="0" target="sponsor">'.
'</a><br /><br />'
);
}
$r->print('</td>');
}
$r->print(<<"END_OF_FOOT");
</tr>
</table>
<input type="hidden" name="go" value="">
<input type="hidden" name="page" value="$page">
<input type="hidden" name="year" value="$year">
<input type="hidden" name="event" value="$event">
<input type="hidden" name="return" value="$returnpage">
</form>
</body>
</html>
END_OF_FOOT
}
sub display_one() {
my ($r,$event_id,$params,$user,$portal,$returnpage,$exitpage,$info,$workshop,$dbh,$page,$contact_email,$contact_name,$form_elements,$rowitems,$rowtitles,$sponsors,$sponsorinfo) = @_;
my $year = $$params{'year'};
my $event = $$params{'event'};
my $quoted_user = $dbh->quote( $user );
my %curr=();
my @workshops=();
if ($portal eq '') {
$portal = $exitpage;
}
my $logoutportal = $portal;
if ($logoutportal =~ /^\/update/) {
$logoutportal = '/register';
}
#
# get current year and season
#
my @date_list=localtime(time);
my $cur_year = $date_list[5];
my $long_year = 1900 + $cur_year;
my $day = $date_list[3];
my $month = $date_list[4];
my $short_year;
my @months = ("January","February","March","April","May","June","July","August","September","October","November","December");
my $jscript = &check_uncheck_jscript();
$jscript .= <<"END_OF_SCRIPT";
function validForm() {
var checkok = 1
var msg = "The information you provided was incomplete.\\nThe following actions need to be taken:\\n"
var fieldschk = 0;
for (i = 0; i < document.adminForm.fields.length; i++) {
if (document.adminForm.fields[i].checked) {
fieldschk = 1;
};
}
if (fieldschk == 0) {
checkok = 0;
msg = msg + "You must check at least one field for display.\\n";
}
if (document.adminForm.status.options[document.adminForm.status.selectedIndex].value == -1) {
checkok = 0;
msg = msg + "Select an option for the Registration status select box that is not 'Please select'.\\n";
}
if (document.adminForm.domains.options[document.adminForm.domains.selectedIndex].value == -1) {
checkok = 0;
msg = msg + "Select an option for the Domains select box that is not 'Please select'.\\n";
}
if (checkok == 0) {
alert(msg)
return
}
else
{
document.adminForm.go.value = "NextPage"
document.adminForm.submit()
return
}
}
END_OF_SCRIPT
$r->print(<<"END_OF_A");
<html><head><title>View LON-CAPA Event Registrations</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
buttonred
{
font-size: 13px; font-family: arial, Trebuchet MS, verdana, helvetica, san-serif; padding: 4px;
line-height: 15px; color: #FFFFFF; text-align: center; background: #CC6633; font-weight: bold;
}
-->
</style>
<script type="text/javascript">
$jscript
</script>
</head>
<body bgcolor="#ffffff" link="#003333" alink="#003333" vlink="#003333">
<form method="post" name="adminForm">
END_OF_A
&main_box($r,$year,$event);
$r->print(<<"END_OF_B");
<table cellpadding="0" cellspacing="0" width="100%" border="0">
<tr bgcolor="#ffffff">
<td><font face="Arial,Helvetica,sans-serif">
<a HREF="http://loncapa.org/">Home</a> --> <a href="$exitpage">Support</a> --> <a href="$portal">Choose an Event</a> --> <b>Choose data to display or export to Excel</b></font>
</td>
<td> </td>
<td align="right" valign="top">
<img border="0" src="/images/login/logout_red.gif" width="90" height="23" align="right" usemap="#event_header">
<map name = "event_header">
<area shape='rect' coords=10,2,80,18' href="/logout?portal=$logoutportal">
<area shape = 'default' nohref>
</map>
</td>
</tr>
</table>
END_OF_B
my $rowcount = 0;
my $rowCol;
my @rowcolors = ('#ffeebb','#ffffbb');
my $rowCol = $rowcolors[0];
$r->print(<<"END_OF_BLOCK");
<br />
<table cellpadding="2" cellspacing="0" border="0" width="100%">
<tr bgcolor="#ffcc33">
<td> </td>
<td valign="bottom"><nobr><img src="/images/requests/bl_step1.gif" width="21" height="24" valign="middle""> </nobr>
</td>
<td align="left"><nobr>
<font face="arial,helvetica,sans-serif"><b>Fields to display</b></nobr>
</font>
</td>
<td width="100%"> </td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
<tr>
<td> </td>
<td colspan="3">
<nobr>
<input type="button" class="buttonred" value="check all"
onclick="javascript:checkAll(document.adminForm.fields)" />
<input type="button" class="buttonred" value="uncheck all"
onclick="javascript:uncheckAll(document.adminForm.fields)" />
</nobr>
</td>
</tr>
<tr>
<td> </td>
<td colspan="3">
<table width="90%" border="0" cellpadding="0" cellspacing="1" bgcolor="#000000">
<tr>
<td>
<table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#ffffff">
<tr>
<td>
<table width="100%" border="0" cellpadding="2" cellspacing="1" bgcolor="#FFFFFF"><tr bgcolor="$rowCol">
END_OF_BLOCK
my $total = @{$rowitems};
my $cells = 4;
my $remainder = ($total)%($cells);
my $colspan;
if ($remainder > 0) {
$colspan= $cells - $remainder +1;
}
for (my $i=0; $i<@{$rowitems}; $i++) {
if ($i>0 && $i%$cells == 0) {
$rowcount ++;
my $rowindex = $rowcount%2;
$rowCol = $rowcolors[$rowindex];
$r->print('</tr><tr bgcolor="'.$rowCol.'">');
}
$r->print('<td valign="top" ');
if ($i == @{$rowitems}-1) {
if ($remainder > 0) {
$r-print('colspan="'.$colspan.'"');
}
}
$r->print('><font face="arial,helvetica,sans-serif"');
if (@{$$rowitems[$i]} > 1) {
$r->print('<b>'.$$rowtitles[$i].'</b><br /><nobr>');
for (my $j=0; $j<@{$$rowitems[$i]}; $j++) {
$r->print('<label><input type="checkbox" name="fields" value="'.$$rowitems[$i][$j].'" />'.$$form_elements{$$rowitems[$i][$j]}{title}.'</label> ');
}
$r->print('</nobr>');
} else {
$r->print('<nobr><label><input type="checkbox" name="fields" value="'.$$rowitems[$i][0].'" /> <b>'.$$rowtitles[$i].'</b></label></nobr>');
}
$r->print('</font></td>');
}
$r->print('</tr></table></td></tr></table></td></tr></table>');
$r->print(<<"END_OF_THREE");
<tr>
<td colspan="4"> </td>
</tr>
<tr bgcolor="#ffcc33">
<td> </td>
<td valign="bottom"><nobr><img src="/images/requests/bl_step2.gif" width="21" height="24" valign="middle""> </nobr>
</td>
<td align="left"><nobr>
<font face="arial,helvetica,sans-serif"><b>Registration status of users</b></nobr>
</font>
</td>
<td width="100%"> </td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
<tr>
<td> </td>
<td colspan="3">
<select name="status">
<option value="-1" selected="selected">Please select</option>
<option value="enroll">Currently registered</option>
<option value="cancel">Cancelled registrations</option>
<option value="any">Any registration status</option>
</select>
</td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
<tr bgcolor="#ffcc33">
<td> </td>
<td valign="bottom"><nobr><img src="/images/requests/bl_step3.gif" width="21" height="24" valign="middle""> </nobr>
</td>
<td align="left"><nobr>
<font face="arial,helvetica,sans-serif"><b>Date Range</b></nobr>
</font>
</td>
<td width="100%"> </td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
<tr>
<td> </td>
<td colspan="3">
<table border="0" cellpadding="2" cellspacing="0">
<tr>
<td><font face="Arial, Helvetica, sans-serif">Registrations last modified after: </td><td> </td>
<td><font face="Arial, Helvetica, sans-serif">
<table border="0" cellpadding="4" align="left">
<tr>
<td valign="top" align="center">
<font face="Arial, Helvetica, sans-serif">
<b>Month</b><br>
<select name="month_start">
<option value=-1>Please select</option>
END_OF_THREE
for (my $k = 0; $k<12; $k ++) {
if ($month == $k) {
$r->print(qq( <option value="$k" selected>$months[$k]</option>)); }
else {
$r->print(qq( <option value="$k">$months[$k]</option>));
}
}
$r->print(qq( </select>
</font>
</td>
<td valign="top" align="center">
<font face="Arial, Helvetica, sans-serif">
<b>Day</b><br>
<select name="day_start">
<option value=-1>Please select</option>));
for (my $j = 1; $j<32; $j++) {
if ($day == $j) {
$r->print(qq( <option value="$j" selected>$j</option>\n));
}
else {
$r->print(qq( <option value="$j">$j</option>\n));
}
}
$r->print(qq( </select>
</font>
</td>
<td valign="top" align="center">
<font face="Arial, Helvetica, sans-serif">
<b>Year</b><br>
<select name="year_start">
<option value=-1>Please select</option>
));
for (my $i = $long_year; $i<$long_year+5; $i++) {
$short_year = substr($i,2);
if (($cur_year%100) == $short_year) {
$r->print(qq( <option value="$short_year" selected>$i</option>\n));
}
else {
$r->print(qq( <option value="$short_year">$i</option>\n));
}
}
$r->print(<<"END_OF_FOUR");
</select>
</font>
</td>
<td valign="bottom" align="center">
<nobr><label><font face="Arial, Helvetica, sans-serif"><b>
No start date?</b> </font><input type="checkbox" name="nostartdate" value="1"/></label></nobr>
</td>
</tr>
</table>
</td>
<tr>
<td><font face="Arial, Helvetica, sans-serif">Registrations last modified before: </td><td> </td>
<td><font face="Arial, Helvetica, sans-serif">
<table border="0" cellpadding="4" align="left">
<tr>
<td valign="top" align="center">
<font face="Arial, Helvetica, sans-serif">
<b>Month</b><br>
<select name="month_end">
<option value=-1>Please select</option>
END_OF_FOUR
for (my $k = 0; $k<12; $k ++) {
if ($month == $k) {
$r->print(qq( <option value=$k selected>$months[$k]</option>)); }
else {
$r->print(qq( <option value=$k>$months[$k]</option>));
}
}
$r->print(qq( </select>
</font>
</td>
<td valign="top" align="center">
<font face="Arial, Helvetica, sans-serif">
<b>Day</b><br>
<select name="day_end">
<option value=-1>Please select</option>));
for (my $j = 1; $j<32; $j++) {
if ($day == $j) {
$r->print(qq( <option value="$j" selected>$j</option>\n));
}
else {
$r->print(qq( <option value="$j">$j</option>\n));
}
}
$r->print(qq( </select>
</font>
</td>
<td valign="top" align="center">
<font face="Arial, Helvetica, sans-serif">
<b>Year</b><br>
<select name="year_end">
<option value=-1>Please select</option>
));
for (my $i = $long_year; $i<$long_year+5; $i++) {
$short_year = substr($i,2);
if (($cur_year%100) == $short_year) {
$r->print(qq( <option value="$short_year" selected>$i</option>\n));
}
else {
$r->print(qq( <option value="$short_year">$i</option>\n));
}
}
$r->print(<<"END_OF_FIVE");
</select>
</font>
</td>
<td valign="bottom" align="center">
<nobr><label><font face="Arial, Helvetica, sans-serif"><b>
No end date?</b> </font><input type="checkbox" name="noenddate" value="1"/></label></nobr>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
<tr bgcolor="#ffcc33">
<td> </td>
<td valign="bottom"><nobr><img src="/images/requests/bl_step4.gif" width="21" height="24" valign="middle""> </nobr>
</td>
<td align="left"><nobr>
<font face="arial,helvetica,sans-serif"><b>Domain(s) of Registrants</b></nobr>
</font>
</td>
<td width="100%"> </td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
<tr>
<td> </td>
<td colspan="3">
<table>
<tr>
<td>
<select name="domains" multiple="multiple" size="5">
<option value="-1" selected="selected">Please select</option>
<option value="none">No domain</option>
<option value="all">All domains</option>
END_OF_FIVE
my @domains = ();
my $alldomains = '';
my $dom_response = &get_domains(\$alldomains);
if ($dom_response eq 'ok') {
my @domains = split/,/,$alldomains;
foreach my $domain (@domains) {
$domain =~ s/^"([^"]+)"$/$1/;
$r->print('<option value="'.$domain.'">'.$domain.'</option>'."\n");
}
}
$r->print('
</select>
</td>
<td> </td>
<td><font face="arial,helvetica,sans-serif"><small>
Select multiple domains by using the following keyboard and mouse combinations:<ul><li>Apple-click: MacOS</li><li>Ctrl-click: Windows</li><li>Ctrl-click: Linux</li></ul></small></font>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
<tr>
<td> </td>
<td colspan="3"><input type="button" class="buttonred" name="display" value="Display data" onClick="javascript:validForm()"></td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
</table>
');
&main_footer($r,$page,$year,$event,$returnpage,$sponsors,$sponsorinfo);
return;
}
sub display_two {
my ($r,$event,$year,$event_id,$params,$user,$portal,$returnpage,$exitpage,$info,$workshop,$dbh,$page,$contact_email,$contact_name,$form_elements,$rowitems,$rowtitles,$sponsors,$sponsorinfo,$webpath) = @_;
my $enc_event = &HTML::Entities::encode($event);
my %quoted_params;
my $quoted_user = $dbh->quote( $user );
my @workshops=();
if ($portal eq '') {
$portal = $exitpage;
}
my $logoutportal = $portal;
if ($logoutportal =~ /^\/update/) {
$logoutportal = '/register';
}
my $regurl = "event=$event&year=$year";
$regurl = &HTML::Entities::encode($regurl);
$regurl =~ tr/ /+/;
my @titles;
my @headers;
my @domains;
my @fields;
my $domainfilter = '';
my $statusfilter = '';
my $startstr = '';
my $endstr = '';
foreach my $param (%{$params}) {
if ($param eq 'domains') {
if (ref($$params{$param}) eq 'ARRAY') {
@domains = @{$$params{$param}};
} else {
@domains = ($$params{$param});
}
} elsif ($param eq 'fields') {
if (ref($$params{$param}) eq 'ARRAY') {
@fields = @{$$params{$param}};
} else {
@fields = ($$params{$param});
}
} elsif ($param eq 'status') {
my $status = $$params{$param};
unless (($status eq 'any') || ($status == -1)) {
$statusfilter = " AND er.status='$status' ";
}
}
}
unless (defined($$params{'nostartdate'})) {
my $startyear = '20'.$$params{'year_start'};
my $startmonth = $$params{'month_start'} + 1;
if (length($startmonth) == 1) {
$startmonth = '0'.$startmonth;
}
my $startday = $$params{'day_start'};
if (length($startday) == 1) {
$startday = '0'.$startday;
}
$startstr = " AND er.modified >= '".$startyear.'-'.$startmonth.'-'.$startday."'";
}
unless (defined($$params{'noenddate'})) {
my $endyear = '20'.$$params{'year_end'};
my $endmonth = $$params{'month_end'} + 1;
if (length($endmonth) == 1) {
$endmonth = '0'.$endmonth;
}
my $endday = $$params{'day_end'};
if (length($endday) == 1) {
$endday = '0'.$endday;
}
$endstr = " AND er.modified <= '".$endyear.'-'.$endmonth.'-'.$endday."'";
}
unless (grep/^all$/,@domains) {
if (grep/^none$/,@domains) {
$domainfilter = " AND su.domain IS NULL";
} else {
$domainfilter = " AND (";
foreach my $domain (@domains) {
unless ($domain == -1) {
$domainfilter .= "su.domain = '$domain' OR ";
}
}
$domainfilter =~ s/OR $//;
$domainfilter .= " )";
}
}
for (my $i=0; $i<@{$rowitems}; $i++) {
if (@{$$rowitems[$i]} > 1) {
push(@titles,$$rowtitles[$i]);
for (my $j=0; $j<@{$$rowitems[$i]}; $j++) {
if (grep/^$$rowitems[$i][$j]$/,@fields) {
push @headers,$$rowitems[$i][$j];
}
}
} else {
push(@titles,$$rowtitles[$i]);
push(@headers,$$rowitems[$i][0]);
}
}
my %currvalue;
my @users = ();
my $statement = "SELECT su.user_id,";
foreach my $field (@fields) {
if ($$form_elements{$field}{tablename} eq 'support_user') {
$statement .= "su.$field,";
} elsif ($$form_elements{$field}{tablename} eq 'event_registration') {
$statement .= "er.$field,";
}
}
chop($statement);
my $inc;
$statement .= " FROM event_registration er, support_user su WHERE (er.event_id = '$event_id' AND er.user_id = su.user_id $domainfilter $statusfilter $startstr $endstr) ORDER BY su.lastname";
my $sth = $dbh->prepare("$statement");
$sth->execute();
while (my @row = $sth->fetchrow_array) {
$inc = -1;
if (@row > 0) {
my $user_id = $row[0];
push(@users,$user_id);
for (my $i=1; $i<@row; $i++) {
my $tablename = $$form_elements{$fields[$i+$inc]}{tablename};
unless (($tablename eq 'support_user') || ($tablename eq 'event_registration')) {
$inc++ ;
}
$currvalue{$user_id}{$fields[$i+$inc]} = $row[$i];
}
}
}
$sth->finish;
my %workshops;
my @workshopnames = ('coord','author','portfolio','other');
foreach my $user_id (@users) {
%{$workshops{$user_id}} = ();
my $user_id_quoted = $dbh->quote($user_id);
$sth= $dbh->prepare("SELECT name FROM workshop_registration WHERE user_id = $user_id_quoted AND event_id = '$event_id'");
$sth->execute();
while (my @row = $sth->fetchrow_array) {
if (@row > 0) {
foreach my $workshop (@row) {
$workshops{$user_id}{$workshop} = 'Y';
}
}
}
$sth->finish;
}
my $jscript = '';
my $headerweb = '';
my @webcontent = ();
my @header = ();
my @data = ();
my $rowcount = 0;
my $rowCol;
my @rowcolors = ('#ffeebb','#ffffbb');
my $rowCol = $rowcolors[0];
if (@users > 0) {
foreach my $field (@fields) {
if ($field eq 'workshop') {
foreach my $workshop (@workshopnames) {
push(@header,'workshop - '.$workshop);
$headerweb .= '<td align="center"><font face="Arial,Helvetica,sans-serif"><b>workshop - '.$workshop.'</b></font></td>';
}
} else {
$headerweb .= '<td align="center"><font face="Arial,Helvetica,sans-serif"><b>'.$$form_elements{$field}{title}.'</b></font></td>';
push(@header,$$form_elements{$field}{title});
}
}
foreach my $user_id (@users) {
@{$data[$rowcount]} = ();
my $rowindex = $rowcount%2;
$rowCol = $rowcolors[$rowindex];
$webcontent[$rowcount] = '<tr bgcolor="'.$rowCol.'">';
foreach my $field (@fields) {
if ($field eq 'workshop') {
foreach my $workshop (@workshopnames) {
if (defined($workshops{$user_id}{$workshop})) {
$webcontent[$rowcount] .= '<td align="right"><font face="Arial,Helvetica,sans-serif">Y</font></td>';
push(@{$data[$rowcount]},'Y');
} else {
$webcontent[$rowcount] .= '<td align="right"><font face="Arial,Helvetica,sans-serif">N</font></td>';
push(@{$data[$rowcount]},'N');
}
}
} else {
$webcontent[$rowcount] .= '<td align="right"><font face="Arial,Helvetica,sans-serif">'.$currvalue{$user_id}{$field}.'</font></td>';
push(@{$data[$rowcount]},$currvalue{$user_id}{$field});
}
}
$webcontent[$rowcount] .= '</tr>';
$rowcount ++;
}
}
$r->print(<<"END_OF_A");
<html><head><title>View LON-CAPA Event Registrations</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
buttonred
{
font-size: 13px; font-family: arial, Trebuchet MS, verdana, helvetica, san-serif; padding: 4px;
line-height: 15px; color: #FFFFFF; text-align: center; background: #CC6633; font-weight: bold;
}
-->
</style>
<script type="text/javascript">
$jscript
</script>
</head>
<body bgcolor="#ffffff" link="#003333" alink="#003333" vlink="#003333">
<form method="post" name="adminForm">
END_OF_A
&main_box($r,$year,$event);
$r->print(<<"END_OF_B");
<table cellpadding="0" cellspacing="0" width="100%" border="0">
<tr bgcolor="#ffffff">
<td><font face="Arial,Helvetica,sans-serif">
<a HREF="http://loncapa.org/">Home</a> --> <a href="$exitpage">Support</a> --> <a href="$portal">Choose an Event</a> --> <a href="/registrations?$regurl">Choose data to display/export</a> --> <b>Result</b></font>
</td>
<td> </td>
<td align="right" valign="top">
<img border="0" src="/images/login/logout_red.gif" width="90" height="23" align="right" usemap="#event_header">
<map name = "event_header">
<area shape='rect' coords=10,2,80,18' href="/logout?portal=$logoutportal">
<area shape = 'default' nohref>
</map>
</td>
</tr>
</table>
<br />
<table cellpadding="2" cellspacing="0" border="0" width="100%">
<tr>
<td> </td>
END_OF_B
if (@users == 0) {
$r->print('<td><font face="Arial,Helvetica,sans-serif">Sorry, no users matched your search criteria</font></td>');
} else{
my $url = &build_excel($webpath,$event_id,$user,\@header,\@data);
$r->print('<td><font face="Arial,Helvetica,sans-serif"><a href="'.$url.'">Download an Excel file</a> with the following contents:<br /></font></td>
</tr>
<tr>
<td> </td>
<td>
<table width="90%" border="0" cellpadding="0" cellspacing="1" bgcolor="#000000"> <tr>
<td>
<table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#ffffff">
<tr>
<td>
<table width="100%" border="0" cellpadding="2" cellspacing="1" bgcolor="#FFFFFF">
<tr bgcolor="#ffcc33">
'.$headerweb.'</tr>');
foreach my $line (@webcontent) {
$r->print("$line\n");
}
$r->print('
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
');
}
$r->print('
</tr>
<tr>
<td colspan="4"> </td>
</tr>
</table>
');
&main_footer($r,$page,$year,$event,$returnpage,$sponsors,$sponsorinfo);
return;
}
sub build_excel {
my ($webpath,$event_id,$user,$headings,$data) = @_;
my $ss = Spreadsheet::WriteExcel::Simple->new;
$ss->write_bold_row($headings);
for (my $i=0; $i<@{$data}; $i++) {
$ss->write_row($$data[$i]);
}
my $filename = $event_id.'-'.time.'-'.$user.'.xls';
my $path = '/home/helpdesk/web/'.$webpath.'excel/'.$filename;
$ss->save($path);
my $url = $webpath.'excel/'.$filename;
return $url;
}
sub check_uncheck_jscript {
my $jscript = <<"ENDSCRT";
function checkAll(field) {
if (field.length > 0) {
for (i = 0; i < field.length; i++) {
field[i].checked = true ;
}
} else {
field.checked = true
}
}
function uncheckAll(field) {
if (field.length > 0) {
for (i = 0; i < field.length; i++) {
field[i].checked = false ;
} } else {
field.checked = false ;
}
}
ENDSCRT
return $jscript;
}
sub get_domains {
my $domref = shift;
my $URL = "http://s10.lite.msu.edu/cgi-bin/check_auth.pl";
my $request = HTTP::Request->new(POST => $URL);
$request->content_type('application/x-www-form-urlencoded');
$request->content('action=domainlist');
$request->authorization_basic("lonadm","litelite");
my $res = LWP::UserAgent->new->request($request);
if ($res->is_success) {
$$domref = $res->content;
$$domref =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
return 'ok';
} else {
return 'no_doms';
}
}
1;
--raeburn1139015340--