<?php
// cms/api/audit/list

    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array("sessionid","offset",'numofrec');
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
    
    //Declaramos e inicializamos nuestras variables
    $out = new stdClass();
    $sessionid = mysqli_real_escape_string($db, $_GET["sessionid"]);
    $appid = '';
    $offset = mysqli_real_escape_string($db, $_GET['offset']);
    $numofrec = mysqli_real_escape_string($db, $_GET['numofrec']);
    $filter = '';
    $order = determinateOrder(mysqli_real_escape_string($db, $_GET['order']));
    $datefrom = ''; 
    $dateto = '';
    $user = '';
    $modul = '';
    
    // 1 = CMS | 2 = APP
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 70))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
    
    //En caso de que el appid sea enviado, se construye el query
    if(isset($_GET["appid"]) && $_GET["appid"] != ""){
        $appid = " AND AU.application = '".(intval($_GET["appid"]) == 1 ? 'CMS' : 'APP')."' ";
    }
    
    //En caso de que el modul sea enviado, se construye el query
    if(isset($_GET["modul"]) && $_GET["modul"] != ""){
        $modul = " AND AU.module = '".mysqli_real_escape_string($db, $_GET["modul"])."' ";
    }
    
    //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(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 .= " cms.usr LIKE '%".$el."%' ".
                "           OR cms.name LIKE '%".$el."%' ".
                "           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 AU.module LIKE '".$el."%' ".
                "           OR AU.dsc LIKE '%".$el."%' ".
                "           OR DATE(AU.date) = DATE('".$dateel."') ";
            }else{
                $filter .= "  OR cms.usr LIKE '%".$el."%' ".
                "           OR cms.name LIKE '%".$el."%' ".
                "           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 AU.module LIKE '".$el."%' ".
                "           OR AU.dsc LIKE '%".$el."%' ".
                "           OR DATE(AU.date) = DATE('".$dateel."') ";
            }
        }
        
        
        $filter .= " ) ";
    }
    
    //En caso de que el datefrom sea enviado, se construye el query
    if(isset($_GET["datefrom"]) && $_GET["datefrom"] != ""){
        $datefrom = " AND DATE(AU.date) >= DATE('".mysqli_real_escape_string($db, $_GET['datefrom'])."') ";
    }
    
    //En caso de que el dateto sea enviado, se construye el query
    if(isset($_GET["dateto"]) && $_GET["dateto"] != ""){
        $dateto = " AND DATE(AU.date) <= DATE('".mysqli_real_escape_string($db, $_GET['dateto'])."') ";
    }
    
    //En caso de que el user sea enviado, se construye el query
    if(isset($_GET["user"]) && $_GET["user"] != ""){
        $user = " AND (cms.usr = '".mysqli_real_escape_string($db, $_GET['user'])."' ".
        "           OR usr.usr = '".mysqli_real_escape_string($db, $_GET['user'])."') ";
    }
    
    $sql = "SELECT COUNT(AU.id) AS qty ".
    "       FROM audit AU ".
    "           LEFT JOIN users usr ON usr.id = AU.userid AND AU.application = 'APP'".
    "           LEFT JOIN cmsusers cms ON cms.id = AU.userid AND AU.application = 'CMS' AND AU.module != 'API'".
    "           LEFT JOIN apiusers api ON api.id = AU.userid AND AU.module = 'API'".
    "           LEFT JOIN userpersons usrperson ON usr.id = usrperson.usrid ".
    "           LEFT JOIN usercompanies usrcompany ON usr.id = usrcompany.usrid ".
    "       WHERE AU.id IS NOT NULL ".
    "       ".$user.
    "       ".$modul.
    "       ".$filter.
    "       ".$datefrom.
    "       ".$dateto.
    "       ".$appid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $row = $rs->fetch_assoc();
    
    $out->numofrecords = (int)$row["qty"];
    //$out->consulta = $sql;
    
    $sql = "SELECT AU.id AS id, ".
    "       AU.date AS auditdate, ".
    "       AU.application AS auditapp, ".
    "       AU.module AS auditmodule, ".
    "       AU.dsc AS auditdsc, ".
    "       AU.ipaddress AS ip, ".
    "       usr.type AS usrtype, ".
    "       CASE ".
    "           WHEN usr.id IS NOT NULL THEN usr.id ".
    "           WHEN cms.id IS NOT NULL THEN cms.id ".
    "           WHEN api.id IS NOT NULL THEN api.id ".
    "       END AS usrid, ".
    "       CASE ".
    "           WHEN usr.id IS NOT NULL THEN usr.usr ".
    "           WHEN cms.id IS NOT NULL THEN cms.usr ".
    "           WHEN api.id IS NOT NULL THEN api.user ".
    "           ELSE 'Sistema AFX'".
    "       END AS usr, ".
    "       CASE ".
    "           WHEN usr.id IS NOT NULL THEN IF(usr.type = 1, CONCAT(usrperson.fname,IF(".
    "               usrperson.sname IS NOT NULL, CONCAT(' ',usrperson.sname), ''".
    "           )), ".
    "           usrcompany.name) ".
    "           WHEN cms.id IS NOT NULL THEN cms.name ".
    "           WHEN api.id IS NOT NULL THEN api.user ".
    "           ELSE 'Sistema AFX'".
    "       END AS name, ".
    "       CASE ".
    "           WHEN usr.id IS NOT NULL THEN IF(usr.type = 1, CONCAT(usrperson.flastname,IF(".
    "               usrperson.slastname IS NOT NULL, CONCAT(' ',usrperson.slastname), ''".
    "           )), ".
    "           usrcompany.comercialname) ".
    "           WHEN cms.id IS NOT NULL THEN cms.name ".
    "           WHEN api.id IS NOT NULL THEN api.user ".
    "       END AS secondname ".
    "       FROM audit AU ".
    "           LEFT JOIN users usr ON usr.id = AU.userid AND AU.application = 'APP'".
    "           LEFT JOIN cmsusers cms ON cms.id = AU.userid AND AU.application = 'CMS' AND AU.module != 'API'".
    "           LEFT JOIN apiusers api ON api.id = AU.userid AND AU.module = 'API'".
    "           LEFT JOIN userpersons usrperson ON usr.id = usrperson.usrid ".
    "           LEFT JOIN usercompanies usrcompany ON usr.id = usrcompany.usrid ".
    "       WHERE AU.id IS NOT NULL ".
    "       ".$user.
    "       ".$appid.
    "       ".$modul.
    "       ".$datefrom.
    "       ".$dateto.
    "       ".$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->date = new stdClass();
        $record->date->date = date("Y-m-d H:i:s", strtotime($row["auditdate"]));
        $record->date->formatted = date("d/m/Y - h:i a", strtotime($row["auditdate"]));
        
        $record->appdsc = $row['auditapp'];
        $record->modul = $row['auditmodule'];
        
        $record->user = new stdClass();
        $record->user->id = (int)$row['usrid'];
        $record->user->email = $row['usr'] == null ? '' : $row['usr'];
        $record->user->ipaddress = $row['ip'] == NULL ? '' : $row['ip'];
        
        if($row['usrtype'] == 2){
            $record->user->name = $row['name']." / ".$row['secondname'];
        }else if($row['usrtype'] == 1){
            $record->user->name = $row['name']." ".$row['secondname'];
        }else{
            $record->user->name = $row['name'] == null ? '' : $row['name'];
        }
        
        $record->dsc = $row['auditdsc'];
        
        $records [] = $record;
    }
    
    $out->records = $records;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    /* Funcion para retornar orden de resultados, donde el parametro que recibe:
    *   1 = Mas reciente
    *   2 = Fecha ASCENDENTE
    *   3 = Aplicacion DESCENDENTE y mas reciente
    *   4 = Modulo ASCENDENTE y mas reciente
    *   -1 = Menos reciente
    *   -2 = Fecha DESCENDENTE
    *   -3 = Aplicacion ASCENDENTE y mas reciente
    *   -4 = Modulo DESCENDENTE y mas reciente
    */
    function determinateOrder($order){
        $str = '';
        
        switch($order){
            case 1:
                $str = ' ORDER BY AU.id DESC';
                break;
            case 2:
                $str = ' ORDER BY AU.date ASC';
                break;
            case 3:
                $str = ' ORDER BY AU.application DESC, AU.id DESC';
                break;
            case 4:
                $str = ' ORDER BY AU.module ASC, AU.id DESC';
                break;
            case -1:
                $str = ' ORDER BY AU.id ASC';
                break;
            case -2:
                $str = ' ORDER BY AU.date DESC';
                break;
            case -3:
                $str = ' ORDER BY AU.application ASC, AU.id DESC';
                break;
            case -4:
                $str = ' ORDER BY AU.module DESC, AU.id DESC';
                break;
            default:
                $str = ' ORDER BY AU.id DESC';
                break;
        }
        
        return $str;
    }
?>
