<?php
// cms/api/messages/list

    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array("order","offset","numofrec","sessionid");
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
    
    
    //Definimos las variables que usaremos y las inicializamos
    $out= new stdClass();
    $sessionid = mysqli_real_escape_string($db, $_GET["sessionid"]);
    $order = determinateOrder(intval(mysqli_real_escape_string($db, $_GET["order"])));
    $offset = mysqli_real_escape_string($db, $_GET["offset"]);
    $numofrec = mysqli_real_escape_string($db, $_GET["numofrec"]);
    $filter = '';
    $daterange = '';
    $type = '';
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 72))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
    
    //En caso de que el filter sea enviado, se construye el query
    if(isset($_GET["filter"]) && $_GET["filter"] != ""){
        // Iniciamos la cadena del query
        $filter = " HAVING ( ";
        
        //Separamos el filtro por el simbolo +
        $filterEl = separateFilter(mysqli_real_escape_string($db, $_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 LIKE '%".$el."%'".
                "       OR message LIKE '%".$el."%'".
                "       OR name LIKE '%".$el."%' ";
                "       OR DATE(datecreated) = DATE('".$dateel."') ";
            }else{
                
                $filter .= " OR usr LIKE '%".$el."%'".
                "       OR message LIKE '%".$el."%'".
                "       OR name LIKE '%".$el."%' ";
                "       OR DATE(datecreated) = DATE('".$dateel."') ";
            }
        }
        
        $filter .= " ) ";
    }
    
    //Verificamos que no estemos recibiendo el filtro y si es asi construimos el query
    if(isset($_GET["daterange"]) && $_GET["daterange"] != ""){
        $daterange = determinateRange(mysqli_real_escape_string($db, $_GET['daterange']));
    }
    
    //Verificamos que no estemos recibiendo el filtro y si es asi construimos el query
    if(isset($_GET["type"]) && $_GET["type"] != "" && $_GET['type'] != "0"){
        $type = " AND comunications.type = ".($_GET['type'] == 2 ? 1 : 2);
    }
    
    // Obtenemos la cantidad de mensajes
    $sql = "SELECT COUNT(*) AS qty FROM ( SELECT ".
    "       GROUP_CONCAT((SELECT users.usr FROM users WHERE users.id = comunications.userid) SEPARATOR ';') AS usr, ".
    "       comunications.message AS message, ".
    "       GROUP_CONCAT(IF(userpersons.fname IS NULL, ".
    "           CONCAT(usercompanies.name, ".
    "               IF(usercompanies.comercialname IS NULL, '', CONCAT('/',usercompanies.comercialname))), ".
    "           CONCAT(userpersons.fname,IF(userpersons.sname IS NULL, '', CONCAT(' ',userpersons.sname)),' ',userpersons.flastname, IF(userpersons.slastname IS NULL, '', CONCAT(' ',userpersons.slastname)) )".
    "       ) SEPARATOR '|') AS name, ".
    "       comunications.createddate AS datecreated, ".
    "       COUNT(comunications.id) AS qty ".
    "       FROM comunications ".
    "       LEFT JOIN usercompanies ON usercompanies.usrid = comunications.userid ".
    "       LEFT JOIN userpersons ON userpersons.usrid = comunications.userid ".
    "       WHERE (comunications.sentdate IS NOT NULL AND comunications.type=1 ".
    "       OR comunications.sentdate IS NULL AND comunications.type=2 OR " .
    "       comunications.sentdate IS NOT NULL AND comunications.type=2) " .
    "       ".$type.
    "       ".$daterange.
    "       GROUP BY ref, createddate".
    "       ".$filter.
    "       ) suma ";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $out->numofrecords = 0;
    while($row = $rs->fetch_assoc()){
        $out->numofrecords += (int)$row['qty'];
    }
    
    // Obtenemos los detalles de los mensajes
    $sql = "SELECT comunications.id AS id, ".
    "       comunications.message AS message, ".
    "       comunications.tags AS tags, ".
    "       GROUP_CONCAT(comunications.userid SEPARATOR '|') AS userid, ".
    "       GROUP_CONCAT(IF(userpersons.fname IS NULL, ".
    "           CONCAT(usercompanies.name, ".
    "               IF(usercompanies.comercialname IS NULL, '', CONCAT('/',usercompanies.comercialname))), ".
    "           CONCAT(userpersons.fname,IF(userpersons.sname IS NULL, '', CONCAT(' ',userpersons.sname)),' ',userpersons.flastname, IF(userpersons.slastname IS NULL, '', CONCAT(' ',userpersons.slastname)) )".
    "       ) SEPARATOR '|') AS name, ".
    "       GROUP_CONCAT((SELECT users.usr FROM users WHERE users.id = comunications.userid) SEPARATOR ';') AS usr, ".
    "       comunications.ref AS ref, ".
    "       comunications.type AS type, ".
    "       comunications.createddate AS datecreated, ".
    "       comunications.date AS date, ".
    "       comunications.sentdate AS sentdate, ".
    "       GROUP_CONCAT(IF(comunications.readed IS NULL, 'NULL', comunications.readed) SEPARATOR ',') AS readed ".
    "       FROM comunications ".
    "       LEFT JOIN usercompanies ON usercompanies.usrid = comunications.userid ".
    "       LEFT JOIN userpersons ON userpersons.usrid = comunications.userid ".
    "       WHERE (comunications.sentdate IS NOT NULL AND comunications.type=1 ".
    "       OR comunications.sentdate IS NULL AND comunications.type=2 OR " .
    "       comunications.sentdate IS NOT NULL AND comunications.type=2) " .
    "       ".$type.
    "       ".$daterange.
    "       GROUP BY ref, createddate".
    "       ".$filter.
    "       ".$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['id'];
        //$record->row= $row;
        
        $record->users = [];
        
        // Obtenemos los usuarios
        $usrArr = explode(';',$row['usr']);
        $useridArr = explode('|',$row['userid']);
        $nameArr = explode('|',$row['name']);
        $readedArr = explode(',',$row['readed']);
        
        foreach($usrArr as $index => $usr){
            $user = new stdClass();
            
            $user->id = (int)$useridArr[$index];
            $user->fullname = $nameArr[$index];
            $user->user = $usrArr[$index];
            
            if($readedArr[$index] == NULL || $readedArr[$index] == 'NULL'){
                $user->readed = null;
            }else{
                $user->readed = new stdClass();
                $user->readed->date = $readedArr[$index];
                $user->readed->formatted = date('d/m/Y - h:i a',strtotime($readedArr[$index]));
            }
            
            $record->users [] = $user;
        }
        
        $record->message = $row['message'];
        
        $record->ref = new stdClass();
        $record->ref->dsc = $row['ref'];
        
        $record->type = (int)$row['type'];
        
        $record->tags = $row['tags'];
        
        $record->datecreated = new stdClass();
        $record->datecreated->date = $row['datecreated'];
        $record->datecreated->formatted = date('d/m/Y - h:i a',strtotime($row['datecreated']));
        
        $record->date = new stdClass();
        $record->date->date = $row['date'];
        $record->date->formatted = date('d/m/Y - h:i a',strtotime($row['date']));
        
        $record->sentdate = new stdClass();
        $record->sentdate->date = $row['sentdate'];
        $record->sentdate->formatted = date('d/m/Y - h:i a',strtotime($row['sentdate']));
        
        $records [] = $record;
    }
    
    $out->records = $records;
    
    $privileges = new stdClass();
    $privileges->read = tienePrivilegio($db, $sessionid, 72);
    $privileges->update = tienePrivilegio($db, $sessionid, 73);
    
    $out->privileges = $privileges;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    /* Con esta funcion determinamos el orden, donde:
    *   1 = fecha ASC
    *   -1 = fecha DESC
    */    
    function determinateOrder($id){
        $str = "";
        
        switch($id){
            case 0:
                $str = " ORDER BY id DESC";
                break;
            case 1:
                $str = " ORDER BY datecreated ASC";
                break;
            case -1:
                $str = " ORDER BY datecreated DESC";
                break;
            case 2:
                $str = " ORDER BY usr ASC";
                break;
            case -2:
                $str = " ORDER BY usr DESC";
                break; 
            case 3:
                $str = " ORDER BY message ASC";
                break;
            case -3:
                $str = " ORDER BY message DESC";
                break; 
        }
        
        return $str;
    }
    
    /*  Funcion para determinar el orden de los resultados
    *   Esta recibe un parametro que retorna dependiendo del valor:
    *   1 = Hoy
    *   2 = Ultimos 7 Dias
    *   3 = Ultimos 30 dias
    *   4 = Ultimos 3 meses
    *   5 = Ultimos 6 meses
    */
    function determinateRange($range){
        $str = "";
        
        switch($range){
            case 1:
                $str = " AND DATE(comunications.createddate) >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
                break;
            case 2:
                $str = " AND DATE(comunications.createddate) >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
                break;
            case 3:
                $str = " AND DATE(comunications.createddate) >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
                break;
            case 4:
                $str = " AND DATE(comunications.createddate) >= DATE_SUB(NOW(), INTERVAL 90 DAY)";
                break;
            case 5:
                $str = " AND DATE(comunications.createddate) >= DATE_SUB(NOW(), INTERVAL 6 MONTH)";
                break;
            default: 
                badEnd("500", array("msg"=>"El parametro fecha solo puede ser 0, 1, 2, 3, 4 o 5"));
                break;
        }
        
        return $str;
    }
?>