<?php
// cms/api/blacklist/list
    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array('sessionid', 'offset', 'numofrec', 'order');
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
    
    //Declaramos e inicializamos nuestras variables
    $out= new stdClass();
    $sessionid = $_GET["sessionid"];
    $offset = $_GET['offset'];
    $numofrecords = $_GET['numofrec'];
    $order = determinateOrder($_GET['order']);
    $fiter = '';
    $afxusers = '';
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 89))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
        
    // Si obtenemos afxusers, armamos el query
    if(isset($_GET['afxusers']) && $_GET['afxusers'] != ''){
        if ($_GET['afxusers'] == 0) {
            $afxusers = ' AND blacklist.userid IS NOT NULL';
        } else if ($_GET['afxusers'] == 1) {
            $afxusers = ' AND blacklist.userid IS NULL';
        }
    }
    
    //En caso de que el filter sea enviado, se construye el query
    if(isset($_GET["filter"]) && $_GET["filter"] != ""){
        // Iniciamos la cadena del query
        $filter = " AND ( ";
        
        //Separamos el filtro por el simbolo +
        $filterEl = separateFilter($_GET["filter"]);
        
        //Recorremos cada elemento
        foreach($filterEl as $index => $el){
            
            
            //Si la fecha tiene / lo partimos en mil
            if(strpos($el, '/') !== false){
                $dateelements = explode("/",$el);
                
                foreach($dateelements as $i => $element){
                    if($i == 0){
                        $dateConverted .= $element;
                    }else{
                        $dateConverted .= "-".$element;
                    }
                }
                
                $dateel = strftime("%Y-%m-%d", strtotime($dateConverted));
            }else{
                $dateel = strftime("%Y-%m-%d", strtotime($el));
            }
            
            if($index == 0){
                $filter .= " blacklist.name LIKE '%".$el."%' ".
                "           OR blacklist.doc LIKE '%".$el."%'".
                "           OR blacklist.name LIKE '%".$el."%'".
                "           OR blacklist.email LIKE '%".$el."%'".
                "           OR blacklist.account LIKE '%".$el."%'".
                "           OR blacklist.organism LIKE '%".$el."%'".
                "           OR blacklist.link LIKE '%".$el."%'".
                "           OR CONCAT('#',blacklist.id) = '".$el."' " .
                "           OR DATE(blacklist.date) = DATE('".$dateel."') ";
            }else{
                $filter .= " OR blacklist.name LIKE '%".$el."%' ".
                "           OR blacklist.doc LIKE '%".$el."%'".
                "           OR blacklist.name LIKE '%".$el."%'".
                "           OR blacklist.email LIKE '%".$el."%'".
                "           OR blacklist.account LIKE '%".$el."%'".
                "           OR blacklist.organism LIKE '%".$el."%'".
                "           OR blacklist.link LIKE '%".$el."%'".
                "           OR CONCAT('#',blacklist.id) = '".$el."' " .
                "           OR DATE(blacklist.date) = DATE('".$dateel."') ";
            }
        }
        
        
        $filter .= " ) ";
    }
        
    // Obtenemos la cantidad total de registros
    $sql = "SELECT COUNT(id) AS qty ".
    "       FROM blacklist".
    "       WHERE true ".
    "       ".$afxusers.
    "       ".$filter;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $row = $rs->fetch_assoc();
    
    $out->numofrecords = (int)$row['qty'];
    
    // Obtenemos los registros
    $sql = "SELECT blacklist.id AS id, ".
    "       blacklist.doctype AS doctype, ".
    "       blacklist.doc AS doc, ".
    "       blacklist.name AS name, ".
    "       blacklist.account AS account, ".
    "       blacklist.email AS email, ".
    "       blacklist.organism AS organism,".
    "       blacklist.date AS date, ".
    "       blacklist.link AS link, ".
    "       IF(users.id IS NOT NULL, users.id, 0) AS usrid, ".
    "       blacklist.createddate AS createddate, ".
    "       IF(users.id IS NOT NULL, users.level, '') AS level".
    "       FROM blacklist".
    "       LEFT JOIN users ON users.id = blacklist.userid".
    "       WHERE true".
    "       ".$afxusers.
    "       ".$filter.
    "       ".$order.
    "       LIMIT ".$offset.", ".$numofrecords;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        $record->id = (int)$row['id'];
        
        $record->doc = new stdClass();
        $record->doc->type = nvl(validateDoc($row['doctype']),"");
        $record->doc->dsc = $row['doc'];
        
        $record->name = $row['name'];
        $record->account = $row['account'];
        $record->email = $row['email'];
        $record->organism = $row['organism'];
        if($row['date']!=='0000-00-00' && $row['date']!==null)
            $record->date = date('d/m/Y', strtotime($row['date']));
        else
            $record->date = " - ";
        //$record->date = date('d/m/Y', strtotime($row['date']));
        $record->link = $row['link'];
    
        $record->user = new stdClass();
        $record->user->id = (int)$row['usrid'];
        
        if ($row['level'] != '') {
            $level = getLevelById($db, $row['level']);
            
            $record->user->level = (int)$level['orden'];
        } else {
            $record->user->level = '';
        }
        
        $records [] = $record;
    }
    
    $out->records = $records;
    
    $out->privileges = new stdClass();
    $out->privileges->read = tienePrivilegio($db, $sessionid, 89);
    $out->privileges->update = tienePrivilegio($db, $sessionid, 90);
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    /* Funcion para validar el tipo de documento que fue ingresado, donde:
    * 1 = CI
    * 2 = Passport
    * 3 = Driver license
    * el parametro typeid es el id del tipo de documento
    */
    function validateDoc($typeid){
        $type = '';
        
        if($typeid == 1){
            $type = "C.I";
        }else if($typeid == 4){
            $type = "RIF";
        }else if($typeid == 2){
            $type = "Pasaporte";
        }else if($typeid == 3){
            $type = "Licencia de conducir";
        }else if($typeid == 0){
            $type = null;
        }
        
        return $type;
    }
    
    /**/
    function determinateOrder($order) {
        $str = '';
        
        switch($order){
            case 1:
                $str = 'ORDER BY createddate DESC';
                break;
            case 2:
                $str = 'ORDER BY name ASC';
                break;
            case 3:
                $str = 'ORDER BY email ASC';
                break;
            case 4:
                $str = 'ORDER BY account ASC';
                break;
            case 5:
                $str = 'ORDER BY organism ASC';
                break;
            case 6:
                $str = 'ORDER BY date DESC';
                break;
            case -1:
                $str = 'ORDER BY createddate ASC';
                break;
            case -2:
                $str = 'ORDER BY name DESC';
                break;
            case -3:
                $str = 'ORDER BY email DESC';
                break;
            case -4:
                $str = 'ORDER BY account DESC';
                break;
            case -5:
                $str = 'ORDER BY organism DESC';
                break;
            case -6:
                $str = 'ORDER BY date ASC';
                break;
            default:
                $str = 'ORDER BY createddate DESC';
                
        }
        
        return $str;
    }
?>