<?php
// cms/api/alerts/listcsv
    header("Content-Type: application/csv;");
    header("Content-Disposition: attachment; filename=alertas.csv");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array('sessionid');
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
    
    //Declaramos e inicializamos nuestras variables
    $out= new stdClass();
    $sessionid = $_GET["sessionid"];
    $daterange = '';
    $type = '';
    $status = '';
    $filter = '';
    $offset = $_GET['offset'];
    $numofrec = $_GET['numofrec'];
    $order = determinateOrder($_GET["order"]);
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 87))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
    
    //En caso de que el type sea enviado, se construye el query
    if(isset($_GET["type"]) && $_GET["type"] != ""){
        $type = "AND LEFT(alerts.type,1) = '".mysqli_real_escape_string($db,$_GET['type'])."'";
    }
    
    //En caso de que el status sea enviado, se construye el query
    if(isset($_GET["status"]) && $_GET["status"] != ""){
        $status = "AND alertsupdates.status = ".mysqli_real_escape_string($db,$_GET['status']);
    }
    
    //En caso de que el daterange sea enviado, se construye el query
    if(isset($_GET["daterange"]) && $_GET["daterange"] != ""){
        $daterange = determinateRange(mysqli_real_escape_string($db, $_GET["daterange"]));
    }
    
    //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 .= " usr.usr LIKE '%".$el."%' ".
                "           OR CONCAT(usrperson.fname,IF(".
                "               usrperson.sname IS NOT NULL, CONCAT(' ',usrperson.sname), ''".
                "           )) LIKE '%".$el."%' ".
                "           OR usrcompany.name LIKE '%".$el."%' ".
                "           OR alerts.dsc LIKE '".$el."%' ".
                "           OR alerts.type LIKE '%".$el."%' ".
                "           OR IF(usr.type = 1, ".
                "           CONCAT(usrperson.flastname,IF(".
                "               usrperson.slastname IS NOT NULL, CONCAT(' ',usrperson.slastname), '')), ".
                "           usrcompany.comercialname) LIKE '%".$el."%'".
                "           OR DATE(alerts.createddate) = DATE('".$dateel."') ";
            }else{
                $filter .= " OR usr.usr LIKE '%".$el."%' ".
                "           OR CONCAT(usrperson.fname,IF(".
                "               usrperson.sname IS NOT NULL, CONCAT(' ',usrperson.sname), ''".
                "           )) LIKE '%".$el."%' ".
                "           OR usrcompany.name LIKE '%".$el."%' ".
                "           OR alerts.dsc LIKE '".$el."%' ".
                "           OR alerts.type LIKE '%".$el."%' ".
                "           OR IF(usr.type = 1, ".
                "           CONCAT(usrperson.flastname,IF(".
                "               usrperson.slastname IS NOT NULL, CONCAT(' ',usrperson.slastname), '')), ".
                "           usrcompany.comercialname) LIKE '%".$el."%'".
                "           OR DATE(alerts.createddate) = DATE('".$dateel."') ";
            }
        }
        
        
        $filter .= " ) ";
    }
    
    $sql = "SELECT COUNT(alerts.id) AS qty ".
    "       FROM alerts  ".
    "       LEFT JOIN (".
    "           SELECT alertsupdates.date AS date,". // Ajustado porque no estaba devolviendo el status del max
    "           alertsupdates.alertid AS alertid,". //agregado inner join adicional
    "           alertsupdates.status AS status".
    "           FROM alertsupdates".
    "           INNER JOIN (SELECT MAX(id) AS id FROM alertsupdates GROUP BY alertid) max " .
    "           ON alertsupdates.id = max.id " .
    "           GROUP BY alertsupdates.alertid".
    "       ) AS alertsupdates ON alertsupdates.alertid = alerts.id".
    "       LEFT JOIN users usr ON usr.id = alerts.userid ".
    "       LEFT JOIN userpersons usrperson ON alerts.userid = usrperson.usrid ".
    "       LEFT JOIN usercompanies usrcompany ON alerts.userid = usrcompany.usrid ".
    "       WHERE alerts.id IS NOT NULL ".
    "       ".$daterange.//Añadido error en definición de apiary era daterange
    "       ".$type.
    "       ".$filter.
    "       ".$status;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $row = $rs->fetch_assoc();
    
    $out->numofrecords = (int)$row['qty'];
    
    $sql = "SELECT alerts.id AS alertid, ".
    "       usr.id AS userid, ".
    "       usr.usr AS usr, ".
    "       usr.risklevel AS risklevel, ".
    "       IF(usr.type = 1, ".
    "           CONCAT(usrperson.fname,IF(".
    "               usrperson.sname IS NOT NULL, CONCAT(' ',usrperson.sname), '')), ".
    "           usrcompany.name) AS name, ".
    "       IF(usr.type = 1, ".
    "           CONCAT(usrperson.flastname,IF(".
    "               usrperson.slastname IS NOT NULL, CONCAT(' ',usrperson.slastname), '')), ".
    "           usrcompany.comercialname) AS secondname, ".
    "       IF(usr.type = 1, usrperson.politicallyexposed, usrcompany.politicallyexposed) AS pep,".
    "       IF(usr.type = 1, usrperson.doctype , 1) AS doctype, ".
    "       IF(usr.type = 1, usrperson.doc, usrcompany.doc) AS doc,".
    "       alertsupdates.status AS alertstatus, ".
    "       alerts.type AS alerttype, ".
    "       alerts.dsc AS alertdsc, ".
    "       alerts.createddate AS createddate".
    "       FROM alerts  ".
    "       LEFT JOIN (".
    "           SELECT alertsupdates.date AS date,". // Ajustado porque no estaba devolviendo el status del max
    "           alertsupdates.alertid AS alertid,". //agregado inner join adicional
    "           alertsupdates.status AS status".
    "           FROM alertsupdates".
    "           INNER JOIN (SELECT MAX(id) AS id FROM alertsupdates GROUP BY alertid) max " .
    "           ON alertsupdates.id = max.id " .
    "           GROUP BY alertsupdates.alertid".
    "       ) AS alertsupdates ON alertsupdates.alertid = alerts.id".
    "       LEFT JOIN users usr ON usr.id = alerts.userid ".
    "       LEFT JOIN userpersons usrperson ON alerts.userid = usrperson.usrid ".
    "       LEFT JOIN usercompanies usrcompany ON alerts.userid = usrcompany.usrid ".
    "       WHERE alerts.id IS NOT NULL ".
    "       ".$daterange.//Añadido error en definición de apiary era daterange
    "       ".$dateto.
    "       ".$datefrom.
    "       ".$type.
    "       ".$filter.
    "       ".$status.
    "       ".$order.
    "       LIMIT ".$offset.", ".$numofrec;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        
        $record->id = (int)$row['alertid'];
        
        $record->user = new stdClass();
        $record->user->id = (int)$row['userid'];
        $record->user->email = $row['usr'];
        $record->user->fullname = $row['name']." ".$row['secondname'];
        
        $record->user->risklevel = new stdClass();
        $record->user->risklevel->id = (int)$row['risklevel'];
        $record->user->risklevel->dsc = "Nivel ".$record->user->risklevel->id;
        
        $record->user->doc = new stdClass();
        $record->user->doc->type = validateDoc($row['doctype']);
        $record->user->doc->dsc = $row['doc'];
        
        $record->user->pep = (int)$row['pep'];
        
        $record->status = new stdClass();
        $record->status->id = (int)$row['alertstatus'];
        $record->status->dsc = alertStatus($record->status->id);
        
        $record->alert = explode("-",$row['alerttype'])[1];
        $record->dsc = $row['alertdsc'];
        
        $record->datecreated = new stdClass();
        $record->datecreated->date = date('Y-m-d H:i:s', strtotime($row['createddate']));
        $record->datecreated->formatted = date('d/m/Y - h:i A', strtotime($row['createddate']));
        
        $records [] = $record;
        
        $csvarray [] = array(
            //'id' => $record->id,
            'alert' => utf8_decode($record->alert),
            'dsc' => utf8_decode($record->dsc),
            'status' => $record->status->dsc,
            'date' => $record->datecreated->formatted,
            'user' => $record->user->email,
            'name' => utf8_decode($record->user->fullname),
            'doctype' => utf8_decode($record->user->doc->type),
            'doc' => $record->user->doc->dsc,
        );
    }
    
    $out->records = $records;
    
    $fp = fopen('php://output', 'wb');
    fwrite($fp, $BOM);
    
    $line = array(/*'ID',*/'ALERTA',utf8_decode('DESCRIPCIÓN'), 'STATUS', 'FECHA', 'USUARIO', 'NOMBRE', 'TIPO DOC.', 'DOCUMENTO');
    
    fputcsv($fp, $line, ';', '"');
    
    foreach($csvarray as $arr){
        fputcsv($fp,$arr,';');
    }
    
    fclose($fp);
    
    die();
    
    /* Funcion para determnar el status 
    *   1 = Verificado
    *   0 = No verificado
    *   -1 = Moderado
    *   -2 = Alto
    */
    function alertStatus($statusid) {
        $str = '';
        
        switch(intval($statusid)){
            case 1:
                $str = 'Verificado';
                break;
            case 0:
                $str = 'Por verificar';
                break;
            case -1:
                $str = 'Moderado';
                break;
            case -2:
                $str = 'Alto';
                break;
            default:
                $str = 'Status no definido';
        }
        
        return $str;
    }
    
    /* 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){
        $str = '';
        
        if($typeid == 1){
            $str = "Cédula";
        }else if($typeid == 2){
            $str = "Pasaporte";
        }else if($typeid == 3){
            $str = "Licencia de conducir";
        }else if($typeid == 0){
            $str = null;
        }
        
        return $str;
    }
    
    /*  Funcion para determinar el orden de los resultados, donde:
    *   1 = ID de la alerta (mas reciente)
    *   2 = Fecha antigua
    *   3 = Tipo
    *   -1 = ID de la alerta (mas antiguo)
    *   -2 = Fecha reciente
    *   -3 = Tipo
    */
    function determinateOrder($order){
        $orderres = "";
        switch($order){
            case 1:
                $orderres = "ORDER BY alerts.id DESC";
                break;
            case 2:
                $orderres = "ORDER BY alerts.createddate ASC";
                break;
            case 3:
                $orderres = "ORDER BY alerts.type ASC";
                break;
            case -1:
                $orderres = "ORDER BY alerts.id ASC";
                break;
            case -2:
                $orderres = "ORDER BY alerts.createddate DESC";
                break;
            case -3:
                $orderres = "ORDER BY alerts.type DESC";
                break;
            default:
                $orderres = "ORDER BY alerts.id DESC";
                break;
        }
        
        return $orderres;
    }
     /* Funcion para determinar el rango de operaciones, donde:
    *   1 = Hoy
    *   2 = Ultimos 7 dias
    *   3 = Ultimos 30 dias
    *   4 = Ultimos 3 meses
    *   5 = Ultimos 5 meses
    */
    function determinateRange($daterange){
        $str = '';
        
        switch($daterange){
            case 1:
                $str = " AND DATE(alerts.createddate) >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
                break;
            case 2:
                $str = " AND DATE(alerts.createddate) >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
                break;
            case 3:
                $str = " AND DATE(alerts.createddate) >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
                break;
            case 4:
                $str = " AND DATE(alerts.createddate) >= DATE_SUB(NOW(), INTERVAL 90 DAY)";
                break;
            case 5:
                $str = " AND DATE(alerts.createddate) >= DATE_SUB(NOW(), INTERVAL 6 MONTH)";
                break;
            default: 
                badEnd("500", array("msg"=>"El parametro fecha solo puede ser 1, 2, 3, 4 o 5"));
                break;
        }
        
        return $str;
    }
?>