<?php
    header("Content-Type:application/json");
    include("../connection.php");
    include("../utils.php");
    
    //Parametros obligatorios
    $parmsob = array("sessionid","schoolid","offset","order","numofrecords");
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros invalidos " . implode(", ", $parmsob)));
        
    //Inicializamos nuestras variables
    $out = new stdClass();
    $sessionid = $_GET["sessionid"];
    $schoolid = $_GET["schoolid"];
    $filter = determinateFilter($_GET["filter"]);
    $offset = $_GET["offset"];
    $order = $_GET["order"];
    $number = $_GET["numofrecords"];
    
    //Validamos que la session sea valida para continuar
    isSessionValid($db, $sessionid);
    
    //Contamos el total de alumnos que posee el colegio
    $sql = "SELECT count(pupils.id) as number FROM pupils, users ".
    "       WHERE users.sessionid = '".$sessionid."' ".
    "       AND pupils.schoolid = users.schoolid ".
    "       ".$filter." ";
    
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
    
    //Guardamos el total de alumnos que posee el colegio
    $row = $rs->fetch_assoc();
    $out->numofrecords = $row["number"];
    
    //Obtenemos los codigos de los estudiantes
    $sql = "SELECT CONCAT(LPAD(pupils.id,'7','0'),RIGHT(CRC32(pupils.id),2)) as code FROM users, pupils".
    "       WHERE  users.sessionid = '".$sessionid."' ".
    "       AND pupils.schoolid = users.schoolid ".
    "       ".$filter." ".
    "       ORDER BY pupils.".$order." ".
    "       LIMIT ".$offset.",".$number." ";
    
     if (!$coders=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
    
    //Obtenemos los datos de los estudiantes
    $sql = "SELECT pupils.id, pupils.birthdate, ".
    "       pupils.name, pupils.surname, ".
    "       pupils.ci, pupils.gender, ".
    "       pupils.section, pupils.level ".
    "       FROM users, pupils ".
    "       WHERE users.sessionid = '".$sessionid."' ".
    "       AND pupils.schoolid = users.schoolid ".
    "       ".$filter." ".
    "       ORDER BY ".$order." ".
    "       LIMIT ".$offset.",".$number." ";
    
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
    
    //Ordenamos los datos de los estudiantes
    while($student= $rs->fetch_assoc()){
        $code = $coders->fetch_assoc();
        $record = new stdClass();
        $record->code = $code["code"];
        $record->id = $student["id"];
        $record->ci = $student["ci"];
        $record->name = $student["name"];
        $record->surname = $student["surname"];
        $record->gender = new stdClass();
        $record->gender->id = $student["gender"];
        $record->gender->dsc = $student["gender"] == "M" ? "Masculino" : "Femenino";
        
        $dateFormat = strtotime($student["birthdate"]);
    
        $record->birthdate = new stdClass();
        $record->birthdate->date = date("Y",$dateFormat).date("m",$dateFormat).date("d",$dateFormat);
        $record->birthdate->formatted = $student["birthdate"];
        $record->level = $student["level"];
        $record->grade = $student["section"];
        
        $records [] = $record;
    }
    
    $out->records = $records;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    function determinateFilter(){
        $filter = "";
        
        if(isset($_GET["filter"]) && $_GET["filter"] != ''){
            $filter = " AND (pupils.surname LIKE '%".$_GET["filter"]."%' OR ".
            "           pupils.name LIKE '%".$_GET["filter"]."%' OR ".
            "           pupils.ci LIKE '%".$_GET["filter"]."%')";
        }
        
        return $filter;
    }
?>