#!/usr/bin/perl # Copyright 2001-2012 Leslie Richardson # This file is part of Open Admin for Schools. # Open Admin for Schools is free software; you can redistribute it # and/or modify it under the terms of the GNU General Public License # as published by the Free Software Foundation; either version 2 of # the License, or (at your option) any later version. my %lex = ('Main' => 'Main', 'Enrol' => 'Enrol', 'Student' => 'Student', 'Continue' => 'Continue', 'Contact' => 'Contact', 'Error' => 'Error', 'Grade' => 'Grade', 'Description' => 'Description', 'Reason' => 'Reason', 'Date' => 'Date', 'Withdrawn' => 'Withdrawn', 'School' => 'School', 'Cannot open' => 'Cannot open', 'Record(s) Stored' => 'Record(s) Stored', 'Select' => 'Select', 'Students' => 'Students', 'Duplicate Check' => 'Duplicate Check', 'Mass' => 'Mass', 'Blank=All' => 'Blank=All', 'Password' => 'Password', 'Eoy' => 'Eoy', ); use DBI; use CGI; use Fcntl qw(:DEFAULT :flock); use Crypt::GeneratePassword qw(:all); # password generation. my $self = 'massenrol.pl'; my $pwd = 'tansi'; # later, pull from security system. eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; if ( not -e "$globdir/global.conf" ) { print $lex{'Cannot open'}. " global.conf file!"; print "\n"; die; } # Read database names and also the global enrol/withdraw reasons. eval { require "$globdir/global.conf"; }; if ( $@ ) { print "

". $lex{'Cannot open'}. " global.conf:\n $@

\n"; die $lex{'Cannot open'}. " global.conf: $@\n"; } my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; # Page Header. my $title = "$lex{Mass} $lex{Enrol} $lex{Students}"; print "$doctype\n$title \n"; if ( not $arr{page} ) { # calendar popup. print "\n"; print "\n"; print "\n"; print "\n"; } # Set Focus to Last Name field. print "$chartype\n"; if ( not $arr{page} ) { print "\n"; } else { print "\n"; } print "

[ $lex{Main} | "; print " $lex{Eoy} ]

\n"; print "

$title

\n"; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; selectStudents(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; enrolStudents(); } #----------------- sub selectStudents { #----------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Check Password if ( not $arr{password} or $arr{password} ne $pwd ) { print "

$lex{Password} $lex{Error}

\n"; print "\n"; exit; } # Open the remote database and get the students. my $db = $arr{dbase}; my $dsn1 = "DBI:$dbtype:dbname=$db"; my $dbh1 = DBI->connect($dsn1,$guser,$gpassword); my $sth; if ( $arr{grade} ) { $sth = $dbh1->prepare("select * from studentwd where grade = ? order by grade, lastname, firstname"); $sth->execute( $arr{grade} ); } else { $sth = $dbh1->prepare("select * from studentwd order by lastname, firstname"); $sth->execute; } print "

\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "

$lex{Select} $lex{Students}

\n"; print "\n"; print "\n"; print qq{\n}; while ( my $ref = $sth->fetchrow_hashref ) { my %rec = (); %rec = %$ref; print "\n}; print qq{\n}; } print qq{\n}; print "

$lex{Students}	$lex{Grade}

"; print qq{ $rec{lastname}, $rec{firstname}	$rec{grade}

\n"; print "

\n"; print "\n"; exit; } # end of selectStudents #---------------- sub enrolStudents { #---------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } my $date = $arr{date}; delete $arr{date}; my $description = $arr{description}; delete $arr{description}; my $dbase = $arr{dbase}; delete $arr{dbase}; my $entrytype = $arr{entrytype}; delete $arr{entrytype}; # Now only student numbers left in %arr. One approach. # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Open the remote database and get the students. my $db = $dbase; my $dsn1 = "DBI:$dbtype:dbname=$db"; my $dbh1 = DBI->connect($dsn1,$guser,$gpassword); my $sth1 = $dbh1->prepare("select * from studentwd where studnum = ?"); # Check for duplicates setup $sth = $dbh->prepare("select count(*) from student where studnum = ?"); # Loop through all students to enrol foreach my $remotestudnum ( keys %arr ) { # Load the remote record into a hash. $sth1->execute( $remotestudnum ); if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr \n";} my $ref = $sth1->fetchrow_hashref; my %rec = %$ref; ## # print "Student Record:
\n"; # foreach my $id ( sort keys %rec ) { print "K:$id V:$rec{$id}
\n"; } ## delete $rec{studid}; delete $rec{utag}; delete $rec{graddate}; delete $rec{owing}; if ( $rec{birthdate} eq '0000-00-00' ) { delete $rec{birthdate}; } # Get next Student number and update file. sysopen (SNUM, "../../etc/studentnumber", O_RDWR | O_CREAT) or die "$lex{'Cannot open'} student number file\n"; flock(SNUM,LOCK_EX); $studentnum = || 0; chomp $studentnum; seek(SNUM,0,0) or die "Cannot rewind student number file: $!\n"; truncate(SNUM,0) or die "Cannot truncate student number file'}: $!\n"; my $done = 0; while ( not $done ) { # Duplicate Check $sth->execute( $studentnum ); if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr \n";} my $count = $sth->fetchrow; if ( $count > 0 ) { print "

$lex{'Duplicate Check'} $studentnum

\n"; $studentnum++; # try next number } else { $done = 1; } } # Write next available student number into the file $newstudentnum = $studentnum + 1; print SNUM $newstudentnum or die "Cannot write to student number file: $!\n"; close SNUM or die "Cannot close student number file: $! \n"; print "New Student Number:$studentnum
\n"; $rec{studnum} = $studentnum; # Create array called "@fields" storing fieldid values. my @fields = (); $sth2 = $dbh->prepare("select fieldid from meta where tableid = 'student' order by arrayidx"); $sth2->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} while (my $fld = $sth2->fetchrow){ push @fields,$fld; } # Add a Password ... my $password = word( $g_studentpwd_minlen, $g_studentpwd_maxlen, $g_studentpwd_lang, $g_studentpwd_signs, $g_studentpwd_caps, $g_studentpwd_minfreq, $g_studentpwd_avgfreq ); $rec{password} = $password; # Now run through @fields array and populate arrays. my @values = (); my @fieldnames = (); foreach my $fld ( @fields ) { if ( $rec{$fld} ) { # if matching hash value exists, use it. push @fieldnames, $fld; push @values, $dbh->quote( $rec{$fld} ); } } my $fields = join(',', @fieldnames ); my $values = join(',', @values ); # print "Insert Fields: $fields
Values: $values
\n"; my $sth = $dbh->prepare("insert into student ( $fields ) values( $values )"); $sth->execute; if ($DBI::errstr){ print "$lex{Error}: $DBI::errstr; $!\n"; die $DBI::errstr; } # Add Transfer Record my $sth = $dbh->prepare("insert into transfer ( studnum, date, type, description, entrytype, lastname, firstname, middlename, birthdate, provnum ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"); $sth->execute( $rec{studnum}, $date, 'enrol', $description, $entrytype, $rec{lastname}, $rec{firstname}, $rec{initial}, $rec{birthdate}, $rec{provnum} ); if ($DBI::errstr) { print "$DBI::errstr\n"; die $DBI::errstr; } } # end of loop to add student records if ( not $DBI::errstr ) { print "

$lex{'Record(s) Stored'}

\n"; } else { print "

$lex{Error}: $DBI::errstr
\n"; print "$lex{Contact}:$adminname [ "; print "$adminemail ]

\n"; } print "

[ $title |\n"; print " $lex{Main} | \n"; print " $lex{Eoy} ]

\n"; print "\n"; exit; } # end of enrolStudents #---------------- sub showStartPage { #---------------- # Get current date my @tim = localtime(time); my $year = @tim[5] + 1900; my $month = @tim[4] + 1; my $day = @tim[3]; my $currdate = "$year-$month-$day"; print "

\n"; print "\n"; print "\n"; # Password print "\n"; print "\n"; # School Database print "\n"; print qq{\n}; # Student Grade print "\n"; print "\n"; # Enrol Date print "\n\n"; # Enrol Reason print "\n"; print "\n"; # Enrolment Description print "\n\n"; print "\n"; print "

$lex{Password}
$lex{School}
$lex{Withdrawn} $lex{Student} $lex{Grade}	$lex{'Blank=All'}
$lex{Enrol} $lex{Date}	"; print "\n"; print "\n"; print "
$lex{Enrol} $lex{Reason}
$lex{Enrol} $lex{Description}	"; print "

"; print "\n"; print "\n"; exit; } # end of showStartPage