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

//cat decider
$where="";
if($_SESSION['accesslevel']<2){
    preg_match("/[0-9]+/", $_SESSION['class'], $match);
    $class=$match[0];
    if($class==0){
        $cat1=0;
        $cat2=10;
    }
    else if($class==1 || $class==2){
        $cat1=1;
        $cat2=10;
    }
    else if($class==3 || $class==4){
        $cat1=2;
        $cat2=10;
    }
    else if($class==5 || $class==6){
        $cat1=3;
        $cat2=11;
    }
    else if($class==7 || $class==8){
        $cat1=4;
        $cat2=11;
    }
    else if($class==9 || $class==10){
        $cat1=5;
        $cat2=12;
    }
    else if($class==11 || $class==12){
        $cat1=6;
        $cat2=12;
    }
    $cat3=20;
    $where="WHERE ts.allow_signup=1 and (p.category=".$cat1." or p.category=".$cat2." or p.category=".$cat3.")";
}

if($_SESSION['accesslevel']>=2){ //just for elevated users
    /*
     * Add new entries
     */
    if(isset($_POST['nts_name'])){
        $sql=$db->prepare("SELECT COUNT(id) AS count FROM time_sequences WHERE name=:name");
        $sql->execute(array(":name"=>$_POST['nts_name']));
        $res=$sql->fetch(PDO::FETCH_ASSOC);
        
        if($res['count']>0){
            functions::setError(5);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
        else{
            $sql=$db->prepare("INSERT INTO time_sequences (name) VALUES (:name)");
            $sql->execute(array(":name"=>$_POST['nts_name']));
            $res=$sql->rowCount();
            
            if($res<1){
                functions::setError(6);
                if(!isset($_GET['backend'])) header("Location: ./programs");
            }
            else{
                functions::setMessage(3);
                if(!isset($_GET['backend'])) header("Location: ./programs");
            }
        }
    }
    if(isset($_POST['ntb_name']) && isset($_POST['ntb_timesequence'])){
        $sql=$db->prepare("SELECT COUNT(id) AS count FROM time_blocks WHERE name=:name and sequence=:seq");
        $sql->execute(array(":name"=>$_POST['ntb_name'], ":seq"=>$_POST['ntb_timesequence']));
        $res=$sql->fetch(PDO::FETCH_ASSOC);
        
        if($res['count']>0){
            functions::setError(5);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
        else{
            $sql=$db->prepare("INSERT INTO time_blocks (name, sequence) VALUES (:name, :seq)");
            $sql->execute(array(":name"=>$_POST['ntb_name'], ":seq"=>$_POST['ntb_timesequence']));
            $res=$sql->rowCount();
            
            if($res<1){
                functions::setError(6);
                if(!isset($_GET['backend'])) header("Location: ./programs");
            }
            else{
                functions::setMessage(3);
                if(!isset($_GET['backend'])) header("Location: ./programs");
            }
        }
    }
    if(isset($_POST['n_name']) && isset($_POST['n_description']) && isset($_POST['n_instructor']) && isset($_POST['n_location']) && isset($_POST['n_category']) && isset($_POST['n_timeblock']) && isset($_POST['n_maxpart'])){
        $sql=$db->prepare("INSERT INTO programs (name, description, instructor, location, category, time_block, max_participants) VALUES (:name, :desc, :inst, :loc, :cat, :tb, :maxpart)");
        $sql->execute(array(":name"=>$_POST['n_name'], ":desc"=>$_POST['n_description'], ":inst"=>$_POST['n_instructor'], ":loc"=>$_POST['n_location'], ":cat"=>$_POST['n_category'], ":tb"=>$_POST['n_timeblock'], ":maxpart"=>$_POST['n_maxpart']));
        $res=$sql->rowCount();
        
        if($res<1){
            functions::setError(6);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
        else{
            functions::setMessage(3);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
    }
    
    
    /*
     * delete entry
     */
    if(isset($_GET['ts_delete'])){
        $sql=$db->prepare("DELETE FROM time_sequences WHERE id=:id");
        $sql->execute(array(":id"=>$_GET['ts_delete']));
        $res=$sql->rowCount();
        
        if($res<1){
            functions::setError(6);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
        else{
            functions::setMessage(4);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
    }
    if(isset($_GET['tb_delete'])){
        $sql=$db->prepare("DELETE FROM time_blocks WHERE id=:id");
        $sql->execute(array(":id"=>$_GET['tb_delete']));
        $res=$sql->rowCount();
        
        if($res<1){
            functions::setError(6);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
        else{
            functions::setMessage(4);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
    }
    if(isset($_GET['delete'])){
        $sql=$db->prepare("DELETE FROM programs WHERE id=:id");
        $sql->execute(array(":id"=>$_GET['delete']));
        $res=$sql->rowCount();
        
        if($res<1){
            functions::setError(6);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
        else{
            functions::setMessage(4);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
    }
}


/*
 * Subscribe/unsubscribe
 */
if($_SESSION['accesslevel']==0){ //only they need it
    if(isset($_GET['sub'])){
        if((!$config['allowsignup'] && $_SESSION['except_signup']!=1) || $_SESSION['except_signup']==2){ //check if signup allowed
            functions::setError(11);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
        else{
            $sql=$db->prepare("SELECT COUNT(p.id) AS count, p.category, p.time_block, p.max_participants, (SELECT COUNT(r.id) FROM registrations AS r WHERE r.program=p.id) AS cur_participants, ts.allow_signup FROM programs AS p INNER JOIN time_blocks AS tb ON (tb.id=p.time_block) INNER JOIN time_sequences AS ts ON (ts.id=tb.sequence) WHERE p.id=:id");
            $sql->execute(array(":id"=>$_GET['sub']));
            $prog=$sql->fetch(PDO::FETCH_ASSOC);
            
            if($prog['count']<1){ //check if exists
                functions::setError(7);
                if(!isset($_GET['backend'])) header("Location: ./programs");
            }
            else{
                if($prog['cur_participants']>=$prog['max_participants']){ //check if not full
                    functions::setError(8);
                    if(!isset($_GET['backend'])) header("Location: ./programs");
                }
                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"=>$_SESSION['id'], ":tb"=>$prog['time_block']));
                    $res=$sql->fetch(PDO::FETCH_ASSOC);
                    
                    if($res['count']>0){ //check if not occupied on that time
                        functions::setError(9);
                        if(!isset($_GET['backend'])) header("Location: ./programs");
                    }
                    else{
                        if($prog['category']!=$cat1 && $prog['category']!=$cat2 && $prog['category']!=$cat3){ //check if category coresponds
                            functions::setError(10);
                            if(!isset($_GET['backend'])) header("Location: ./programs");
                        }
                        else{
                            if($prog['allow_signup']!=1){ //check if it is actually possible to sign up to this
                                functions::setError(13);
                                if(!isset($_GET['backend'])) header("Location: ./programs");
                            }
                            else{
                                //subscribe
                                $sql=$db->prepare("INSERT INTO registrations(user, program) VALUES (:uid, :pid)");
                                $sql->execute(array(":uid"=>$_SESSION['id'], ":pid"=>$_GET['sub']));
                                $res=$sql->rowCount();
                                
                                if($res<1){
                                    functions::setError(6);
                                    if(!isset($_GET['backend'])) header("Location: ./programs");
                                }
                                else{
                                    //add to history
                                    $sql=$db->prepare("INSERT INTO registration_log (user, date, action, program) VALUES (:uid, :date, :act, :pid)");
                                    $sql->execute(array(":uid"=>$_SESSION['id'], ":date"=>date("Y-m-d H:i:s"), ":act"=>1, ":pid"=>$_GET['sub']));
                                    functions::setMessage(5);
                                    if(!isset($_GET['backend'])) header("Location: ./programs");
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    if(isset($_GET['unsub'])){
        if((!$config['allowsignup'] && $_SESSION['except_signup']!=1) || $_SESSION['except_signup']==2){ //check if signup allowed
            functions::setError(11);
            if(!isset($_GET['backend'])) header("Location: ./programs");
        }
        else{
            $sql=$db->prepare("SELECT COUNT(id) AS count FROM registrations WHERE user=:uid and program=:pid");
            $sql->execute(array(":uid"=>$_SESSION['id'], ":pid"=>$_GET['unsub']));
            $res=$sql->fetch(PDO::FETCH_ASSOC);
            
            if($res['count']<1){ //check if signed up
                functions::setError(7);
                if(!isset($_GET['backend'])) header("Location: ./programs");
            }
            else{
                $sql=$db->prepare("SELECT ts.allow_signup 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 user=:uid and program=:pid");
                $sql->execute(array(":uid"=>$_SESSION['id'], ":pid"=>$_GET['unsub']));
                $res=$sql->fetch(PDO::FETCH_ASSOC);
                if($res['allow_signup']!=1){ //check if signup/down allowed
                    functions::setError(13);
                    if(!isset($_GET['backend'])) header("Location: ./programs");
                }
                else{
                    //unsubscribe
                    $sql=$db->prepare("DELETE FROM registrations WHERE user=:uid and program=:pid");
                    $sql->execute(array(":uid"=>$_SESSION['id'], ":pid"=>$_GET['unsub']));
                    $res=$sql->rowCount();
                    
                    if($res<1){
                        functions::setError(6);
                        if(!isset($_GET['backend'])) header("Location: ./programs");
                    }
                    else{
                        //add to history
                        $sql=$db->prepare("INSERT INTO registration_log (user, date, action, program) VALUES (:uid, :date, :act, :pid)");
                        $sql->execute(array(":uid"=>$_SESSION['id'], ":date"=>date("Y-m-d H:i:s"), ":act"=>0, ":pid"=>$_GET['unsub']));
                        functions::setMessage(6);
                        if(!isset($_GET['backend'])) header("Location: ./programs");
                    }
                }
            }
        }
    }
}


/*
 * Main query
 */
$msql=$db->prepare("SELECT p.id, p.name, p.description, p.instructor, p.location, p.category, tb.name AS time_block, ts.name AS time_sequence, p.max_participants, (SELECT COUNT(r.id) FROM registrations AS r WHERE r.program=p.id) AS cur_participants FROM programs AS p INNER JOIN time_blocks AS tb ON (tb.id=p.time_block) INNER JOIN time_sequences AS ts ON (ts.id=tb.sequence) ".$where." GROUP BY(p.id) ORDER BY p.name ASC");
$msql->execute();

/*
 * EXPORT
 */
if(isset($_GET['export'])){
    $csv=$BOM;
    $csv.=$config['general']['org']."\n".$config['general']['title']."\n\n";
    $csv.=$lang['id'].";".$lang['name'].";".$lang['description'].";".$lang['instructor'].";".$lang['location'].";".$lang['category'].";".$lang['timeblock'].";".$lang['maxpart'].";".$lang['curpart']."\n";
    
    while($row=$msql->fetch(PDO::FETCH_ASSOC)){
        $csv.=$row['id'].";".$row['name'].";".$row['description'].";".$row['instructor'].";".$row['location'].";".$lang['cat'][$row['category']].";".$row['time_sequence']."/".$row['time_block'].";".$row['max_participants'].";".$row['cur_participants']."\n";
    }
    
    //print
    header("Content-type: application/octet-stream");
    //header("Content-length: ".mb_strlen($csv));
    header("Content-disposition: attachment; filename='".$config['general']['title']."_programs_export_".date("Y-m-d H-i-s").".csv'");
    echo $csv;
    die();
}