#!/usr/bin/perl 
#  Copyright 2001-2018 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.

#  Design: We have an array called '@sortorder' with the studnum's in
#  the correct order for display.

#  If there is no test named 'sortorder' then we will put students
#  initially in lastname, firstname order. The order values will start
#  at 5 and increment by 5.

# If there IS a sortorder test, then it will be used to populate the
# sortorder array in order.

my %lex = ('Change' => 'Change',
	   'Name' => 'Name',
	   'GB&nbsp;Main' => 'GB&nbsp;Main',
	   'Main' => 'Main',
	   'Please Log In' => 'Please Log In',
	   'Error' => 'Error',
	   'Student' => 'Student',
	   'Sort Order' => 'Sort Order',
	   'Save' => 'Save',
	   'Record(s) Updated' => 'Record(s) Updated',
	   'Drag' => 'Drag',

	   );

use DBI;
use CGI;
use CGI::Session;

my $self = 'sortorder.pl';

eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@<br>\n";
}

my $q = new CGI;
my %arr = $q->Vars;

my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);



# Get Session Information...
my $session = new CGI::Session("driver:mysql;serializer:FreezeThaw",
 undef,{Handle => $dbh}) or die CGI::Session->errstr;

my $logged_in = $session->param(logged_in);
if ( not $logged_in ){
    print $q->header( -charset, $charset );
    print $lex{'Please Log In'}. "<br>\n";
    print qq{</body></html>\n};
    exit;
}

my $subjsec = $session->param('subjsec');
print $session->header( -charset, $charset );


# Now setup HTML header
my $title = "$lex{Change} $lex{Student} $lex{'Sort Order'}";

print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$tchcss" type="text/css">\n};
print qq{<style type="text/css">
ul { list-style:none; }
li { padding:0.4em; }</style>\n};

if ( not $arr{page} ) { # load jQuery libs
    print qq{<script type="text/javascript" };
    print qq{src="$g_jquery_url">};
    print qq{</script>\n};

    print qq{<script type="text/javascript" };
    print qq{src="$g_jquery_ui_url">};
    print qq{</script>\n};
}

print qq{$chartype\n};
print qq{</head><body style="padding:1em 2em;">\n};


# Get Subject Description
my $sth = $dbh->prepare("select description from subject where subjsec = ?");
$sth->execute($subjsec);
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
my $desc = $sth->fetchrow;

print qq{<div>[ <a href="$tchpage">$lex{Main}</a> | \n};
print qq{<a href="gbmain.pl">$lex{'GB&nbsp;Main'}</a> ]</div>\n};

print qq{<h1>$desc - $title</h1>\n};


# Update / Add sortorder test and scores
if ( $arr{page} ){ # write values into gbscore, gbtest
    delete $arr{page};
    updateRecords();
}


# get 'sortorder' test id,  if any.
$sth = $dbh->prepare("select id from gbtest where subjsec = ? and name = 'sortorder'");
$sth->execute( $subjsec );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
my $id = $sth->fetchrow; # id is used to find all of the sortorder recs.
#(no $id = no test)

my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

if ( $id ){ # We HAVE a sortorder test id

    # Get the sortorder score records (giving the order)
    $sth = $dbh->prepare("select studnum, score from gbscore
     where testid = ?");
    $sth->execute($id);
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

    my %presort;
    while ( my ($studnum, $score) = $sth->fetchrow ) {

	# Get Name of student
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname ) = $sth1->fetchrow;
	$studname{$studnum} = "<b>$lastname</b>, $firstname";

	$presort{$score} = $studnum; 
    }

    foreach my $key ( sort {$a <=> $b} keys %presort ) {
	push @sortorder, $presort{$key};
    }


} else {  # no sortorder test found, order by lastname, firstname

    # Find the enrollments for this class and read into %studname hash
    #  ordered by lastname, firstname.

    $sth = $dbh->prepare("select distinct e.studnum, s.lastname, 
     s.firstname from eval e
     left outer join studentall s on s.studnum = e.studnum 
     where e.subjcode = ? 
     order by s.lastname, s.firstname");
    $sth->execute( $subjsec );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    while ( my ( $studnum, $lastname,$firstname) = $sth->fetchrow){
        push @sortorder, $studnum;
	$studname{$studnum} = "<b>$lastname</b>, $firstname";
    }

}

print qq{<h3>$lex{Drag} $lex{Name} Up/Down to $lex{Change} $lex{'Sort Order'}</h3>\n};

print qq{<form action="$self" method="post"> 
<input type="hidden" name="page" value="1">
<input type="hidden" name="subjsec" value="$subjsec">
<input type="hidden" name="id" value="$id">\n};

print qq{<input type="submit" value="$lex{Save} " style="margin:0.5em 4em;">\n};

print qq{<div id="sortstudent">\n};

foreach my $studnum ( @sortorder ) {
    $id = $recnum{$studnum};
    print qq{<div id="s_$studnum" style="padding:0.6em 0em;cursor:move">};
    print qq{$studname{$studnum} ($studnum)</div>\n};
}


print qq{</div>};
print qq{<input type="hidden" id="inputsort" name="sortorder">\n};
print qq{</form>\n};


print q{<script type="text/javascript">
  $(function(){  //ready handler

    $("#sortstudent").sortable({
      update: function () {
	var order =  $("#sortstudent").sortable('toArray');
	$("#inputsort").attr("value", order);
      },
      axis:'y',
      cursor:'move'
    });

 });
</script>
};

print qq{</body></html>\n};



#----------------
sub updateRecords {
#----------------

    # foreach my $key (keys %arr) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # single sortorder value, comma separated with leading 's_' before studnum;
    
    my @students = split(",", $arr{sortorder} );
    foreach my $stud ( @students ) {
	$stud =~ s/s\_//;
    }
			 
    # print qq{Students:", @students, "<br>\n};

    my $id = $arr{id};
    delete $arr{id};

    my $subjsec = $arr{subjsec};
    delete $arr{subjsec};

    # These records are passed as studentnumber:number. If the number is
    # one then a record already exists and it is updated. If the number is
    # a 0 then no record exists and a new one is added to the gbscore
    # table.

    if ( not $id ){ # We don't have a test 'sortorder' for this subject.

	# Insert Test
	my $testdesc = qq{$lex{Student} $lex{'Sort Order'}};
	my $sth = $dbh->prepare("insert into gbtest (subjsec, name, description ) values( ?,?,? ) ");
	$sth->execute( $subjsec, 'sortorder', $testdesc );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	# Now get the id for this test.
	$sth = $dbh->prepare("select id from gbtest 
          where name = 'sortorder' and subjsec = ? ");
	$sth->execute( $subjsec );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	$id = $sth->fetchrow;
	    
    } # We have an $id now

    my $sth1 = $dbh->prepare("select id from gbscore where testid = ? and studnum = ?");
    my $sth2 = $dbh->prepare("update gbscore set score = ? where id = ?");
    my $sth3 = $dbh->prepare("insert into gbscore (studnum, testid, score ) values ( ?, ?, ? )");

    my $counter = 100;
    foreach my $studnum ( @students ) {

	# Check for existing score record;
	$sth1->execute($id, $studnum);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $scoreid = $sth1->fetchrow;

	if ( $scoreid ) { # update the score with the counter value.
	    $sth2->execute( $counter, $scoreid );

	} else { # add a new record.
	    $sth3->execute( $studnum, $id, $counter );
	}
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	$counter += 5;
    }

    print qq{<h1>$lex{'Record(s) Updated'}</h1>\n};
    print qq{</body></html>\n};

    exit;

} # End of updateRecords
