<?php
/**
 * /subs/timetable.backend.php
 * @version 1.0
 * @desc Timetable sub backend
 * @author Fándly Gergő Zoltán
 * @copy 2017 Fándly Gergő Zoltán
 */

if($_SESSION['accesslevel']>=2){
    if(isset($_GET['delete'])){
        $sql=$db->prepare("SELECT COUNT(id) AS count, user, program FROM registrations WHERE id=:id");
        $sql->execute(array(":id"=>$_GET['delete']));
        $reg=$sql->fetch(PDO::FETCH_ASSOC);
        
        if($reg['count']<1){
            functions::setError(7);
            if(!isset($_GET['backend'])) header("Location: ./timetable");
        }
        else{
            $sql=$db->prepare("DELETE FROM registrations WHERE id=:id");
            $sql->execute(array(":id"=>$_GET['delete']));
            $res=$sql->rowCount();
            
            if($res<1){
                functions::setError(6);
                if(!isset($_GET['backend'])) header("Location: ./timetable");
            }
            else{
                //keep history integrity
                $sql=$db->prepare("INSERT INTO registration_log (user, date, action, program) VALUES (:uid, :date, :act, :pid)");
                $sql->execute(array(":uid"=>$reg['user'], ":date"=>date("Y-m-d H:i:s"), ":act"=>10, ":pid"=>$reg['program']));
                
                functions::setMessage(4);
                if(!isset($_GET['backend'])) header("Location: ./timetable");
            }
        }
    }
    
    //force add
    if(isset($_POST['fa_user']) && isset($_POST['fa_program'])){
        $sql=$db->prepare("SELECT COUNT(id) AS count FROM users WHERE id=:uid");
        $sql->execute(array(":uid"=>$_POST['fa_user']));
        $res=$sql->fetch(PDO::FETCH_ASSOC);
        
        if($res['count']<1){ //check if user exists
            functions::setError(7);
            if(!isset($_GET['backend'])) header("Location: ./timetable");
        }
        else{
            $sql=$db->prepare("SELECT COUNT(id) AS count, time_block FROM programs WHERE id=:pid");
            $sql->execute(array(":pid"=>$_POST['fa_program']));
            $prog=$sql->fetch(PDO::FETCH_ASSOC);
            
            if($prog['count']<1){ //check if program exists
                functions::setError(7);
                if(!isset($_GET['backend'])) header("Location: ./timetable");
            }
            else{
                $sql=$db->prepare("SELECT COUNT(r.id) AS count FROM registrations AS r INNER JOIN programs AS p ON (p.id=r.program) WHERE r.user=:uid and p.time_block=:tb");
                $sql->execute(array(":uid"=>$_POST['fa_user'], ":tb"=>$prog['time_block']));
                $res=$sql->fetch(PDO::FETCH_ASSOC);
                
                if($res['count']>0){ //check if not occupied
                    functions::setError(12);
                    if(!isset($_GET['backend'])) header("Location: ./timetable");
                }
                else{ //do this!
                    $sql=$db->prepare("INSERT INTO registrations (user, program) VALUES (:uid, :pid)");
                    $sql->execute(array(":uid"=>$_POST['fa_user'], ":pid"=>$_POST['fa_program']));
                    $res=$sql->rowCount();
                    
                    if($res<1){ //check insert failure
                        functions::setError(6);
                        if(!isset($_GET['backend'])) header("Location: ./timetable");
                    }
                    else{
                        //keep history integrity
                        $sql=$db->prepare("INSERT INTO registration_log (user, date, action, program) VALUES (:uid, :date, :act, :pid)");
                        $sql->execute(array(":uid"=>$_POST['fa_user'], ":date"=>date("Y-m-d H:i:s"), ":act"=>11, ":pid"=>$_POST['fa_program']));
                        
                        functions::setMessage(3);
                        if(!isset($_GET['backend'])) header("Location: ./timetable");
                    }
                }
            }
        }
    }
    if(isset($_POST['fa_class']) && isset($_POST['fa_program'])){
        $sql=$db->prepare("SELECT COUNT(id) AS count FROM users WHERE class=:c");
        $sql->execute(array(":c"=>$_POST['fa_class']));
        $res=$sql->fetch(PDO::FETCH_ASSOC);
        
        if($res['count']<1){ //check if class exists
            functions::setError(7);
            if(!isset($_GET['backend'])) header("Location: ./timetable");
        }
        else{
            $sql=$db->prepare("SELECT COUNT(id) AS count, time_block FROM programs WHERE id=:pid");
            $sql->execute(array(":pid"=>$_POST['fa_program']));
            $prog=$sql->fetch(PDO::FETCH_ASSOC);
            
            if($prog['count']<1){ //check if program exists
                functions::setError(7);
                if(!isset($_GET['backend'])) header("Location: ./timetable");
            }
            else{
                $sql=$db->prepare("SELECT COUNT(r.id) AS count, r.id FROM registrations AS r INNER JOIN programs AS p ON (p.id=r.program) INNER JOIN users AS u ON (u.id=r.user) WHERE u.class=:c and u.accesslevel=0 and p.time_block=:tb");
                $sql->execute(array(":c"=>$_POST['fa_class'], ":tb"=>$prog['time_block']));
                $res=$sql->fetch(PDO::FETCH_ASSOC);
                
                if($res['count']>0){ //check if not occupied
                    functions::setError(12);
                    if(!isset($_GET['backend'])) header("Location: ./timetable");
                }
                else{ //do this!
                    $sql=$db->prepare("INSERT INTO registrations (user, program) SELECT id, :pid FROM users WHERE class=:c and accesslevel=0");
                    $sql->execute(array(":c"=>$_POST['fa_class'], ":pid"=>$_POST['fa_program']));
                    $res=$sql->rowCount();
                    
                    if($res<1){ //check insert failure
                        functions::setError(6);
                        if(!isset($_GET['backend'])) header("Location: ./timetable");
                    }
                    else{
                        //keep history integrity
                        $sql=$db->prepare("INSERT INTO registration_log (user, date, action, program) SELECT id, :date, :act, :pid FROM users WHERE class=:c and accesslevel=0");
                        $sql->execute(array(":c"=>$_POST['fa_class'], ":date"=>date("Y-m-d H:i:s"), ":act"=>11, ":pid"=>$_POST['fa_program']));
                        
                        functions::setMessage(3);
                        if(!isset($_GET['backend'])) header("Location: ./timetable");
                    }
                }
            }
        }
    }
    
    $msql=$db->prepare("SELECT id, name, class FROM users WHERE id<>1 and accesslevel=0 ORDER BY class ASC, name ASC");
    $msql->execute();
}

if($_SESSION['accesslevel']==1){
    $msql=$db->prepare("SELECT id, name, class FROM users WHERE id<>1 and accesslevel=0 and class=:class ORDER BY name ASC");
    $msql->execute(array(":class"=>$_SESSION['class']));
}

if($_SESSION['accesslevel']<1){
    $msql=$db->prepare("SELECT p.id, p.name, p.description, p.instructor, p.location, tb.name AS time_block, ts.name AS time_sequence FROM registrations AS r INNER JOIN programs AS p ON (p.id=r.program) INNER JOIN time_blocks AS tb ON (tb.id=p.time_block) INNER JOIN time_sequences AS ts ON (ts.id=tb.sequence) WHERE r.user=:uid ORDER BY ts.id ASC, tb.name ASC, p.name ASC");
    $msql->execute(array(":uid"=>$_SESSION['id']));
}

/*
 * EXPORT
 */
if(isset($_GET['export']) && $_SESSION['accesslevel']>=1){
    $csv=$BOM;
    $csv.=$config['general']['org']."\n".$config['general']['title']."\n\n";
    
    $prog="";
    $sql=$db->prepare("SELECT tb.id, ts.name AS time_sequence, tb.name AS time_block FROM time_blocks AS tb INNER JOIN time_sequences AS ts ON (ts.id=tb.sequence) ORDER BY ts.id ASC, tb.name ASC");
    $sql->execute();
    $tbs=array();
    while($row=$sql->fetch(PDO::FETCH_ASSOC)){
        $prog.=$row['time_sequence']."/".$row['time_block'].";";
        array_push($tbs, $row['id']);
    }
    $prog=rtrim($prog, ";");
    
    $csv.=$lang['uid'].";".$lang['name'].";".$lang['class'].";".$prog."\n";
    
    while($row=$msql->fetch(PDO::FETCH_ASSOC)){
        $i=0;
        $prog="";
        $sql=$db->prepare("SELECT r.id AS regid, tb.id AS time_block, p.name FROM registrations AS r INNER JOIN programs AS p ON (p.id=r.program) INNER JOIN time_blocks AS tb ON (tb.id=p.time_block) WHERE r.user=:uid ORDER BY tb.sequence ASC, tb.name ASC");
        $sql->execute(array(":uid"=>$row['id']));
        while($row2=$sql->fetch(PDO::FETCH_ASSOC)){
            while($row2['time_block']!=$tbs[$i]){
                $prog.="-;";
                $i++;
            }
            $prog.=$row2['name'].";";
            $i++;
        }
        for(;$i<count($tbs); $i++){
            $prog.="-;";
        }
        $prog=rtrim($prog, ";");
        
        $csv.=$row['id'].";".$row['name'].";".$row['class'].";".$prog."\n";
    }
    
    //print
    header("Content-type: application/octet-stream");
    //header("Content-length: ".mb_strlen($csv));
    header("Content-disposition: attachment; filename='".$config['general']['title']."_timetable_export_".date("Y-m-d H-i-s").".csv'");
    echo $csv;
    die();
}

/*
 * PRINT STUDENT CARD
 */
if(isset($_GET['studentcard'])){
    if($_SESSION['accesslevel']<1){
        $html="";
        $html.="<table style=\"page-break-after: always; page-break-inside: avoid; height: 100%; width: 100%; text-align: center; border-spacing: 0.4em\">";
        $html.="<tr>";
        $html.="<td style=\"padding: 1em; border: 1px solid rgb(0,0,0); height: 45%; vertical-align: top\">";
        $html.="<h3>".$config['general']['title']."</h3>";
        $html.="<h3><i>".$config['general']['org']."</i></h3>";
        $html.="<hr>";
        $html.="<p>".$lang['name'].": ".$_SESSION['name']." | ".$lang['class'].": ".$_SESSION['class']." | ".$lang['studentprinted']."</p>";
        
        $html.="<table style=\"width: 95%; font-size: 0.9em\" border=\"1\">";
        $html.="<tr>";
        $html.="<th>".$lang['timeblock']."</th>";
        $html.="<th>".$lang['progname']."</th>";
        $html.="<th>".$lang['instructor']."</th>";
        $html.="<th>".$lang['signature']."</th>";
        $html.="</tr>";
        
        while($row=$msql->fetch(PDO::FETCH_ASSOC)){
            $html.="<tr>";
            $html.="<td>".$row['time_sequence']."<br>".$row['time_block']."</td>";
            $html.="<td>".$row['name']."</td>";
            $html.="<td>".$row['instructor']."</td>";
            $html.="<td></td>";
            $html.="</tr>";
        }
        
        $html.="</table>";
        
        $html.="</td>";
        $html.="</tr>";
        $html.="</table>";
        
        echo "<html><body><center>".$html."</center><script>window.print()</script></body></html>";
        die();
    }
    else{
        $html="";
        $second=false;
        
        while($row=$msql->fetch(PDO::FETCH_ASSOC)){
            //header
            if(!$second){
                $html.="<table style=\"page-break-after: always; page-break-inside: avoid; height: 100%; width: 100%; text-align: center; border-spacing: 0.4em\">";
            }
            //content
            $html.="<tr>";
            $html.="<td style=\"padding: 1em; border: 1px solid rgb(0,0,0); height: 45%; vertical-align: top\">";
            $html.="<h3>".$config['general']['title']."</h3>";
            $html.="<h3><i>".$config['general']['org']."</i></h3>";
            $html.="<hr>";
            $html.="<p>".$lang['name'].": ".$row['name']." | ".$lang['class'].": ".$row['class']."</p>";
            
            //programs
            $html.="<table style=\"width: 95%; font-size: 0.9em\" border=\"1\">";
            $html.="<tr>";
            $html.="<th>".$lang['timeblock']."</th>";
            $html.="<th>".$lang['progname']."</th>";
            $html.="<th>".$lang['instructor']."</th>";
            $html.="<th>".$lang['signature']."</th>";
            $html.="</tr>";
            
            //subquerry
            $sql=$db->prepare("SELECT tb.name AS time_block, ts.name AS time_sequence, p.instructor, p.name FROM registrations AS r INNER JOIN programs AS p ON (p.id=r.program) INNER JOIN time_blocks AS tb ON (tb.id=p.time_block) INNER JOIN time_sequences AS ts ON (ts.id=tb.sequence) WHERE r.user=:uid ORDER BY ts.id ASC, tb.name ASC");
            $sql->execute(array(":uid"=>$row['id']));
            while($row2=$sql->fetch(PDO::FETCH_ASSOC)){
                $html.="<tr>";
                $html.="<td>".$row2['time_sequence']."<br>".$row2['time_block']."</td>";
                $html.="<td>".$row2['name']."</td>";
                $html.="<td>".$row2['instructor']."</td>";
                $html.="<td></td>";
                $html.="</tr>";
            }
            
            $html.="</table>";
            $html.="</td>";
            $html.="</tr>";
            
            if($second){
                $html.="</table>";
            }
            
            $second=!$second;
        }
        
        echo "<html><body><center>".$html."</center><script>window.print()</script></body></html>";
        die();
    }
}