<?php
// app/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 = $_GET["sessionid"];
    $order = determinateOrder(intval($_GET["order"]));
    $offset = $_GET["offset"];
    $numofrec = $_GET["numofrec"];
    $filter = '';
    $daterange = '';
    $type = '';
    $status = '';
    
    //Validamos que la session del usuario sea valida para continuar
    $userid = isSessionValid($db, $sessionid);
    
    //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 .= " (SELECT users.usr FROM users WHERE users.id = comunications.userid) LIKE '%".$el."%'".
                "       OR message LIKE '%".$el."%'".
                "       OR 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)) )".
                "       ) LIKE '%".$el."%' ".
                "       OR DATE(comunications.createddate) = DATE('".$dateel."') ";
            }else{
                
                $filter .= " OR (SELECT users.usr FROM users WHERE users.id = comunications.userid) LIKE '%".$el."%'".
                "       OR comunications.message LIKE '%".$el."%'".
                "       OR 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)) )".
                "       ) LIKE '%".$el."%' ".
                "       OR DATE(comunications.createddate) = 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($_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'];
        
        if($_GET['type'] == 2){
            $type .= "  AND comunications.sentdate IS NOT NULL";
        }
    }
    
    // Si recibimos el status armamos el query
    if(isset($_GET['status']) && $_GET['status'] != ''){
        if($_GET['status'] == 1){
            $status = " AND comunications.readed IS NOT NULL ";
        }else if($_GET['status'] == 2){
            $status = " AND comunications.readed IS NULL ";
        }
    }
    
    // Obtenemos la cantidad de mensajes
    $sql = "SELECT ".
    "       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.userid = ".$userid.
    "       ".($type != '' ? '' : " AND comunications.sentdate IS NOT NULL ").
    "       ".$type.
    "       ".$status.
    "       ".$daterange.
    "       ".$filter;
    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, ".
    "       comunications.userid AS userid, ".
    "       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)) )".
    "       ) AS name, ".
    "       (SELECT users.usr FROM users WHERE users.id = comunications.userid) AS usr, ".
    "       comunications.ref AS ref, ".
    "       comunications.type AS type, ".
    "       comunications.createddate AS datecreated, ".
    "       comunications.date AS date, ".
    "       comunications.sentdate AS sentdate, ".
    "       IF(comunications.readed IS NULL, 'NULL', comunications.readed) AS readed ".
    "       FROM comunications ".
    "       LEFT JOIN usercompanies ON usercompanies.usrid = comunications.userid ".
    "       LEFT JOIN userpersons ON userpersons.usrid = comunications.userid ".
    "       WHERE comunications.userid = ".$userid.
    "       ".($type != '' ? '' : " AND comunications.sentdate IS NOT NULL ").
    "       ".$type.
    "       ".$status.
    "       ".$filter.
    "       ".$daterange.
    "       ".$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->user = new stdClass(); 
        
        $record->user->id = (int)$row['userid'];
        $record->user->fullname = $row['name'];
        $record->user->user = $row['usr'];
        
        if($row['readed'] == NULL || $row['readed'] == 'NULL'){
            $record->readed = null;
        }else{
            $record->readed = new stdClass();
            $record->readed->date = $row['readed'];
            $record->readed->formatted = date('d/m/Y - h:i a',strtotime($row['readed']));
        }
        
        $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;
    
    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 1:
                $str = " ORDER BY comunications.createddate ASC";
                break;
            case -1:
                $str = " ORDER BY comunications.createddate 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;
    }
?>