<?php
    header("Content-Type:application/json");
    include("../connection.php");
    include("../utils.php");
    
    //Parametros obligatorios
    $parmsob = array("schoolid","grade1","grade2","grade3","grade4","grade5","grade6","ssid", 'events', 'modalidad');
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
        
    //Inicializamos nuestras variables
    $out = new stdClass();
    $schoolid = $_GET["schoolid"];
    $sessionid = $_GET["ssid"];
    $modalidad = $_GET['modalidad'];
    $events = $_GET['events'];
    $grade1 = $_GET["grade1"];
    $grade2 = $_GET["grade2"];
    $grade3 = $_GET["grade3"];
    $grade4 = $_GET["grade4"];
    $grade5 = $_GET["grade5"];
    $grade6 = $_GET["grade6"];
    $block = $_GET["block"];
    
    //Validamos que la session sea valida para continuar
    isSessionValid($db, $sessionid);
    
    // Obtenemos los eventos
    $sql = "SELECT * FROM settings WHERE id = 8";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
    
    $row= $rs->fetch_assoc();
    
    $events = $row['content'];
    
    // Obtenemos si es colegio privado o no
    $sql = "SELECT * FROM schools WHERE id = ".$schoolid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
    
    $row= $rs->fetch_assoc();
    
    if ($row['private']) {
        badEnd("422", array("msg"=>'Debe realizar un pago'));
    }
    
    $schooldata = $row;
    
    $sql ="SELECT sum(qty) as totally FROM estimatedpupils WHERE schoolid=".$schoolid."";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
        
    //Ordenamos los datos para el correo de notificacion de pago
    $row= $rs->fetch_assoc();
            
    if ($row['totally'] > 0) {
        //Actualizacion de datos en caso de que existan
        $sql = "UPDATE estimatedpupils SET qty = CASE ".updating($grade1,1).
        "       ".updating($grade2,2).
        "       ".updating($grade3,3).
        "       ".updating($grade4,4).
        "       ".updating($grade5,5).
        "       ".updating($grade6,6).
        "       ELSE qty END ".
        "       WHERE schoolid=".$schoolid;
        if (!$db->query($sql))
            badEnd("500", array("msg"=>$db->error));
        
        
        if($block == 1) {
            
            //Obtenemos el total de alumnos estimados
            $sql ="SELECT sum(qty) as totally FROM estimatedpupils WHERE schoolid=".$schoolid."";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
            
            //Ordenamos los datos para el correo de notificacion de pago
            $row= $rs->fetch_assoc();
            $estimatedpupils = $row['totally'];
        
            $sql = "UPDATE schools SET ".
            "       estimatedpupilsedit = 0, ".
            "       ".($schooldata['events'] == null ? " events = '".$events."', " : '').
            "       ".($schooldata['modality'] == null ? " modality = '".$modalidad."', " : '').
            "       allowedpupils = ".$estimatedpupils.
            "       WHERE schools.id =".$schoolid."";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
        }
    
    } else {
        //Insercion de datos
        $sql =  "INSERT INTO estimatedpupils (schoolid, level, section, qty) ".
                "       VALUES ".getqty($schoolid,$grade1,1)."
                ".getqty($schoolid,$grade2,2)."
                ".getqty($schoolid,$grade3,3)."
                ".getqty($schoolid,$grade4,4)."
                ".getqty($schoolid,$grade5,5)."
                ".getqty($schoolid,$grade6,6);
        
        $sql = rtrim($sql, ", ");
                
        if (!$db->query($sql))
            badEnd("500", array("msg"=>$db->error."insercion"));
            
        if($block == 1) {
                
            //Obtenemos el total de alumnos estimados
            $sql ="SELECT sum(qty) as totally FROM estimatedpupils WHERE schoolid=".$schoolid."";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
                
            //Ordenamos los datos para el correo de notificacion de pago
            $row= $rs->fetch_assoc();
            $estimatedpupils = $row['totally'];
            
            $sql = "UPDATE schools SET ".
            "       estimatedpupilsedit = 0, ".
            "       ".($schooldata['events'] == null ? " events = '".$events."', " : '').
            "       ".($schooldata['modality'] == null ? " modality = '".$modalidad."', " : '').
            "       allowedpupils = ".$estimatedpupils.
            "       WHERE schools.id =".$schoolid."";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
        }
    }
        
    
    $out->id = (integer) $schoolid;
    $out->block = $block;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();  
    
    function getqty($id,$grade,$level){
        $sections = ["A","B","C","D","E","F","G","H","I","J"];
        $values = explode('-',$grade);
        $len = sizeof($values);
        $str = "";
        
        for($i = 0; $i < $len; $i++){
            $str = $str."( ".$id.", ".$level.", '".$sections[$i]."', ".$values[$i]."),";
        }
        
        return $str;
    }
    
    function updating($grade,$level){
        $sections = ["A","B","C","D","E","F","G","H","I","J"];
        $values = explode('-',$grade);
        $len = sizeof($values);
        $str = "";
        
        for($i = 0; $i < $len; $i ++){
            $str = $str."WHEN section = '".$sections[$i]."' AND level = ".$level." THEN ".$values[$i]." + qty ";
        }
        
        return $str;
    }
?>