[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>&nbsp;--&gt;&nbsp;<a href="$exitpage">Support</a>&nbsp;--&gt;&nbsp;<b>Choose an event</b></font>
      </td>
      <td>&nbsp;</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>&nbsp;</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>&nbsp;</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">&nbsp;</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">&nbsp;</td>
   <td width="8" bgcolor="#CC6633">&nbsp;</td>
  </tr>
  <tr>
   <td colspan="4" height="4"  bgcolor="#CC6633">&nbsp;</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>&nbsp;--&gt;&nbsp;<a href="$exitpage">Support</a>&nbsp;--&gt;&nbsp;<a href="$portal">Choose an Event</a>&nbsp;--&gt;&nbsp;<b>Choose data to display or export to Excel</b></font>
      </td>
      <td>&nbsp;</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>&nbsp;</td>
    <td valign="bottom"><nobr><img src="/images/requests/bl_step1.gif" width="21" height="24" valign="middle"">&nbsp;&nbsp;</nobr>
    </td>
    <td align="left"><nobr>
     <font face="arial,helvetica,sans-serif"><b>Fields to display</b></nobr>
     </font>
    </td>
    <td width="100%">&nbsp;</td>
   </tr>
   <tr>
    <td colspan="4">&nbsp;</td>
   </tr>
  <tr>
   <td>&nbsp;</td>
   <td colspan="3">
    <nobr>
     <input type="button" class="buttonred" value="check all"
     onclick="javascript:checkAll(document.adminForm.fields)" />
     &nbsp;&nbsp;
     <input type="button" class="buttonred" value="uncheck all"
     onclick="javascript:uncheckAll(document.adminForm.fields)" />
    </nobr>
   </td>
  </tr>
  <tr>
   <td>&nbsp;</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>&nbsp;&nbsp;&nbsp;');
            }
            $r->print('</nobr>');
        } else {
            $r->print('<nobr><label><input type="checkbox" name="fields" value="'.$$rowitems[$i][0].'" />&nbsp;<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">&nbsp;</td>
   </tr>
   <tr bgcolor="#ffcc33">
    <td>&nbsp;</td>
    <td valign="bottom"><nobr><img src="/images/requests/bl_step2.gif" width="21" height="24" valign="middle"">&nbsp;&nbsp;</nobr>
    </td>
    <td align="left"><nobr>
     <font face="arial,helvetica,sans-serif"><b>Registration status of users</b></nobr>
     </font>
    </td>
    <td width="100%">&nbsp;</td>
   </tr>
   <tr>
    <td colspan="4">&nbsp;</td>
   </tr>
   <tr>
    <td>&nbsp;</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">&nbsp;</td>
   </tr>
   <tr bgcolor="#ffcc33">
    <td>&nbsp;</td>
    <td valign="bottom"><nobr><img src="/images/requests/bl_step3.gif" width="21" height="24" valign="middle"">&nbsp;&nbsp;</nobr>
    </td>
    <td align="left"><nobr>
     <font face="arial,helvetica,sans-serif"><b>Date Range</b></nobr>
     </font>
    </td>
    <td width="100%">&nbsp;</td>
   </tr>
   <tr>
    <td colspan="4">&nbsp;</td>
   </tr>
   <tr>
    <td>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</font><input type="checkbox" name="noenddate" value="1"/></label></nobr>
                          </td>
                         </tr>
                        </table>
       </td>
      </tr>
     </table>
    </td>
   </tr>
   <tr>
    <td colspan="4">&nbsp;</td>
   </tr>
   <tr bgcolor="#ffcc33">
    <td>&nbsp;</td>
    <td valign="bottom"><nobr><img src="/images/requests/bl_step4.gif" width="21" height="24" valign="middle"">&nbsp;&nbsp;</nobr>
    </td>
    <td align="left"><nobr>
     <font face="arial,helvetica,sans-serif"><b>Domain(s) of Registrants</b></nobr>
     </font>
    </td>
    <td width="100%">&nbsp;</td>
   </tr>
   <tr>
    <td colspan="4">&nbsp;</td>
   </tr>
   <tr>
    <td>&nbsp;</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>&nbsp;&nbsp;&nbsp;&nbsp;</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">&nbsp;</td>
   </tr>
   <tr>
    <td>&nbsp;</td>
    <td colspan="3"><input type="button" class="buttonred" name="display" value="Display data" onClick="javascript:validForm()"></td>
   </tr>
   <tr>
    <td colspan="4">&nbsp;</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>&nbsp;--&gt;&nbsp;<a href="$exitpage">Support</a>&nbsp;--&gt;&nbsp;<a href="$portal">Choose an Event</a>&nbsp;--&gt;&nbsp;<a href="/registrations?$regurl">Choose data to display/export</a>&nbsp;--&gt;&nbsp;<b>Result</b></font>
      </td>
      <td>&nbsp;</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>&nbsp;</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>&nbsp;</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">&nbsp;</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--