<?php
// cms/api/reports/userdetailsreport

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="reportefeealiados.xlsx"');
    //header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    require('../../hooks/vendor/autoload.php');
    
    
    // parametros obligatorios
    $parmsob = array("sessionid",'userid');
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    //Declaramos e inicializamos nuestras variables
    $out = new stdClass();
    $sessionid = mysqli_real_escape_string($db, $_GET["sessionid"]);
    $userid = mysqli_real_escape_string($db, $_GET['userid']);
    
    //Validamos que la session del usuario sea valida para continuar
    $userid = isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 71))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
        
    // Obtenemos los detalles del USUARIO
    $sql = "SELECT * ".
    "       FROM users ".
    "       WHERE id = ".$userid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $row = $rs->fetch_assoc();
    
    // DAtos del usuario
    $out->entry = new stdClass();
    $out->entry->id = (int)$row['id'];
    $out->entry->usr = $row['usr'];
    $out->entry->datecreated = date("Y-m-d",strtotime($row['datecreated']));
    $out->entry->tags = $row['tag'];
    
    // status del usuario
    $out->entry->status = new stdClass();
    $out->entry->status->id = (int)$row['status'];
    $out->entry->status->dsc = $row['status'] == 1 ? 'Activo' : 'Inactivo';
    
    // riesgo del usuario
    $out->entry->risklevel = new stdClass();
    $out->entry->risklevel->id = (int)$row['risklevel'];
    $out->entry->risklevel->dsc = "Nivel ".$row['risklevel'];
    
    // nivel del usuario
    $out->entry->level = new stdClass();
    $out->entry->level->id = (int)$row['level'];
    $out->entry->level->dsc = "Nivel ".$row['level'];
    
    // Ultima sesion
    $out->entry->lastsession = new stdClass();
    $out->entry->lastsession->date = date("Y-m-d H:i",strtotime($row['lastsession']));
    $out->entry->lastsession->formatted = date("d/mY h:ia",strtotime($row['lastsession']));
    
    // Terminos
    $out->entry->terms = new stdClass();
    $out->entry->terms->date = date("Y-m-d H:i",strtotime($row['terms']));
    $out->entry->terms->formatted = date("d/mY h:ia",strtotime($row['terms']));
    
    // Declaracion
    $out->entry->declaration = new stdClass();
    $out->entry->declaration->date = date("Y-m-d H:i",strtotime($row['declaration']));
    $out->entry->declaration->formatted = date("d/mY h:ia",strtotime($row['declaration']));
    
    // Tipo del usuario
    $out->entry->type = new stdClass();
    $out->entry->type->id = (int)$row['type'];
    
    // Si es uno es natural, guardamos todos sus datos
    if($row['type'] == 1){
        $out->entry->type->dsc = 'Natural';
        
        $sql = "SELECT *".
        "       FROM userpersons".
        "       WHERE usrid = ".$out->entry->id;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $userdetail = $rs->fetch_assoc();
        //Al ser usuario natural se coloca el out de company como null
        $out->entry->company = null;
        
    }else if($row['type'] == 2){
        // Si es 2 es juridico, guardamos sus datos de una vez
        $out->entry->type->dsc = 'Juridico';
        
        $sql = "SELECT *".
        "       FROM usercompanies".
        "       WHERE usrid = ".$out->entry->id;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $userdetail = $rs->fetch_assoc();
        //Al ser usuario juridico se coloca el out de person como null
        $out->entry->person = null;
    }else{
        $out->entry->type->dsc = 'No definido';
    }
    
    // Procedemos a construir la salida de datos
    if($out->entry->type->id == 1){
        
        $rejecteds = explode(",",$userdetail["rejected"]);
        $approveds = explode(",",$userdetail["approved"]);
        
        $out->entry->person = new stdClass();

        $out->entry->person->step = (int)$userdetail["step"];
        
        //Validamos el primer nombre de la persona
        $out->entry->person->firstname = $userdetail["fname"];

        //Validamos el segundo nombre de la persona
        $out->entry->person->secondname = $userdetail["sname"];
        
        //Validamos el primer apellido de la persona
        $out->entry->person->firstlastname = $userdetail["flastname"];
        
        //Validamos el segundo apellido de la persona
        $out->entry->person->secondlastname =  $userdetail["slastname"];
        
        $out->entry->person->document = new stdClass();
        
        //Validamos la descripcion del documento de identidad
        $out->entry->person->document->dsc = $userdetail["doc"];
        
        //Validamos el tipo de documento de identidad
        $out->entry->person->document->type = validateDoc($userdetail["doctype"]);
        
        //Validamos el pais del documento de identidad
        $out->entry->person->document->country = new stdClass();
        $out->entry->person->document->country = getCountry($userdetail["doccountryid"], $db);
        
        //Validamos la fecha de impresion del documento de identidad
        $out->entry->person->document->datecreated = new stdClass();
        $out->entry->person->document->datecreated->date = date("Y-m-d H:i", strtotime($userdetail["docissue"]));
        $out->entry->person->document->datecreated->formatted = date("d/m/Y h:i a", strtotime($userdetail["docissue"]));
        
        //Validamos la fecha de expiracion del documento de identidad
        $out->entry->person->document->dateexpiration = new stdClass();
        $out->entry->person->document->dateexpiration->date = date("Y-m-d H:i", strtotime($userdetail["docexpiry"]));
        $out->entry->person->document->dateexpiration->formatted = date("d/m/Y h:i a", strtotime($userdetail["docexpiry"]));
        
        //Validamos el genero de la persona (je)
        $out->entry->person->gender = $userdetail["gender"];
        
        //Validamos el estado civil de la persona (ju)
        $out->entry->person->maritalstatus = validateMarital($userdetail["maritalstatus"], strtoupper($userdetail["gender"]));
        
        //Validamos la fecha de nacimiento de la persona
        $out->entry->person->birthdate = new stdClass();
        $out->entry->person->birthdate->date = date("Y-m-d H:i", strtotime($userdetail["birthdate"]));
        $out->entry->person->birthdate->formatted = date("d/m/Y h:i a", strtotime($userdetail["birthdate"]));
        
        //Validamos el pais de nacimiento de la persona
        /*
        $out->entry->person->birthcountry = new stdClass();
        $out->entry->person->birthcountry = getCountry($userdetail["birthcountryid"], $db);
        */
        //Validamos el telefono de la persona
        $out->entry->person->phone = $userdetail["phone"];
        
        //Validamos el pais de residencia de la persona
        $out->entry->person->country = new stdClass();
        $out->entry->person->country = getCountry($userdetail["countryid"], $db);
        
        //Validamos el estado de residencia de la persona
        $out->entry->person->region = new stdClass();
        $out->entry->person->region = getRegion($userdetail["regionid"], $db);
        
        //Validamos la ciudad de residencia de la persona
        $out->entry->person->city = new stdClass();
        $out->entry->person->city = getCity($userdetail["cityid"], $db);
        
        //Validamos la calle donde reside la persona
        $out->entry->person->street = $userdetail["street"];
        
        //Validamos el domicilio de la persona
        //$out->entry->person->home = $userdetail["domicile"];
        
        //Validamos el codigo postal de la persona
        $out->entry->person->postalcode = $userdetail["postalcode"];
        
        //Validamos la profesion de la persona
        $out->entry->person->profession = $userdetail["profession"];
        
        //Validamos la ocupacion de la persona
        $sql = "SELECT * ".
        "       FROM ocupations ".
        "       WHERE id = ".$userdetail["ocupation"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $rowData = $res->fetch_assoc();
        
        $out->entry->person->ocupation = $rowData["dsc"];
        
        //Validamos si la persona es politicamente expuesta
        $out->entry->person->politicalexposed = $userdetail["politicallyexposed"] == null ? null : (int)$userdetail["politicallyexposed"];
        
        //Validamos affiliationbody(????? no recuerdo que era este campo)
        $out->entry->person->affiliationbody = $userdetail["affiliationbody"];
        
        //Validamos la positionheld??
        $out->entry->person->positionheld = $userdetail["positionheld"];
        
        //Validamos el pais de la pep
        $out->entry->person->pepcountryid = (int)$userdetail["pepcountryid"];
        
        //Validamos la identificacion del pep
        $out->entry->person->peprelatedid = $userdetail["peprelatedid"];
        
        //Validamos si es un comerciante independiente
        $out->entry->person->independentdealer = $userdetail["independentdealer"] == null ? null : (int)$userdetail["independentdealer"];
        
        //Validamos la compania en la que trabaja
        $out->entry->person->company = $userdetail["company"];
        
        //Validamos el ingreso anual de la persona
        $out->entry->person->anualincome = new stdClass();
        $out->entry->person->anualincome->number = $userdetail["anualincome"] == null ? null : (float)$userdetail["anualincome"];
        $out->entry->person->anualincome->formatted = $userdetail["anualincome"] == null ? null : number_format($out->entry->person->anualincome->number,2,",",".");
        
        //Validamos el patrimonio de la persona
        $out->entry->person->patrimony = new stdClass();
        $out->entry->person->patrimony->number = $userdetail["patrimony"] == null ? null : (float)$userdetail["patrimony"];
        $out->entry->person->patrimony->formatted = $userdetail["patrimony"] == null ? null : number_format($out->entry->person->patrimony->number,2,",",".");
        
        
        //Validamos la actividad economica de la persona
        $sql = "SELECT * ".
        "       FROM economicactivities ".
        "       WHERE id = ".$userdetail["economicactivity"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $rowData = $res->fetch_assoc();
        
        $out->entry->person->economicalactivity = $rowData['dsc'];
        
        //Validamos la cantidad de operaciones que realiza al mes
        $out->entry->person->qtyoperationsmothly = $userdetail["qtymonthlyop"];
        
        //Validamos el monto al mes de la persona
        $out->entry->person->amooperationsmonthly = new stdClass();
        $out->entry->person->amooperationsmonthly->number = $userdetail["amountmonthlyop"];
        //$out->entry->person->amooperationsmonthly->formatted = number_format($userdetail["amountmonthlyop"],2,",",".");
        
        //Validamos la operacion de preferencia del usuario
        $out->entry->person->preferoptype = new stdClass();
        $out->entry->person->preferoptype->id = (int)$userdetail["prefertypeop"] == null ? null : $userdetail["prefertypeop"];
        
        if($out->entry->person->preferoptype->id == 1){
            $out->entry->person->preferoptype->dsc = "Cripto - Cripto (C-C)";
        }else if($out->entry->person->preferoptype->id == 2){
            $out->entry->person->preferoptype->dsc = "Cripto - Dinero Fiat (C-DF)";
        }else if($out->entry->person->preferoptype->id == 3){
            $out->entry->person->preferoptype->dsc = "Dinero Fiat - Cripto (DF-C)";
        }else{
            $out->entry->person->preferoptype->dsc = "";
        }
        
        //Validamos si tiene relaciones economicas
        $out->entry->person->economicrelation = $userdetail["economicrelation"] == null ? null : (int)$userdetail["economicrelation"];
        
        //Validamos si es americano
        $out->entry->person->american = $userdetail["american"] != null ? (int)$userdetail["american"] : null;
        
        //Validamos si es venezolano
        $out->entry->person->venezuelan = $userdetail["venezuelan"] != null ? (int)$userdetail["venezuelan"] : null;
        
        //Consulta para obtener los documentos que ha subido el usuario 
        $sql = "SELECT * FROM useruploads".
        "       WHERE userid = ".$out->entry->id;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        while($uploadarr = $rs->fetch_assoc()){
            
            //Consultamos los datos de cada tipo de documento
            $sql = "SELECT * FROM cmsprefuploads".
            "       WHERE id = ".$uploadarr["prefuploadid"];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $uploaddetail = $res->fetch_assoc();
            
            //Buscamos el archivo para obtener su nombre y extension
            $filestest = glob("../../../uploads/users/".$out->entry->id."/".str_pad($uploadarr["prefuploadid"], 2, "0", STR_PAD_LEFT).".{jpg,png,jpeg,pdf,PNG,JPG,JPEG,PDF}", GLOB_BRACE);
            
            //El primer resultado
            $info = pathinfo($filestest[0]);
            
            
            if($info['basename'] != ""){
                $upload = new stdClass();
                //Generamos el id de salida
                $upload->id = str_pad($uploadarr["prefuploadid"], 2, "0", STR_PAD_LEFT);
                
                $upload->dsc = $uploaddetail["dsc"];
            
                //Url dentro de la carpeta api
                $upload->type = ".".pathinfo($filestest[0], PATHINFO_EXTENSION);
                //Url dentro de la carpeta api
                //$upload->url = $uploadsURL."users/".$out->entry->id."/".$info["basename"];
                $upload->url = $uploadsURLapp."?upid=". $upload->id."&sessionid=".$sessionid;
                $upload->validated = (int)$uploadarr["validated"];
                
                $upload->level = new stdClass();
            
                //Consultamos los datos del nivel
                $sql = "SELECT * FROM levels";
                if (!$resUp=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                
                while($levelsTemp = $resUp->fetch_assoc()){
                    
                    $levelsarrTemp = explode("-",$levelsTemp["cmsprefuploads"]);
                    foreach($levelsarrTemp as $whichlevel){
                        if($uploadarr["prefuploadid"] == $whichlevel){
                            $upload->level->id = (int)$levelsTemp["orden"];
                            $upload->level->dsc = $levelsTemp["name"];
                        }
                    }
                }
                
                $arrRequiresUp = explode("-",$levels["cmsprefuploads"]);
                $upload->required = validateUpload($uploadarr["prefuploadid"],$arrRequiresUp);
                
                $uploads[] = $upload;
            }
        }
        
        $out->entry->person->uploads = $uploads;
        
    }else if($out->entry->type->id == 2){
        
        $out->entry->company = new stdClass();
        
        $out->entry->company->step = (int)$userdetail["step"];
        
        //Validamos el nombre de la empresa
        $out->entry->company->name = $userdetail["name"];
        
        //Validamos el nombre comercial de la empresa
        $out->entry->company->comercialname = $userdetail["comercialname"];
        
        //Validamos la fecha de creacion de la empresa
        $out->entry->company->datecreated = new stdClass();
        $out->entry->company->datecreated->date = date("Y-m-d H:i", strtotime($userdetail["creationdate"]));
        $out->entry->company->datecreated->formatted = date("d/m/Y h:i a", strtotime($userdetail["creationdate"]));
        
        //Validamos la descripcion del documento de identificacion
        $out->entry->company->document = new stdClass();
        $out->entry->company->document->dsc = $userdetail["doc"];
        
        //Validamos el pais del documento de identificacion
        $out->entry->company->document->country = new stdClass();
        $out->entry->company->document->country = getCountry($userdetail["doccountryid"], $db);
        
        //Validamos el estado del documento de identificacion
        $out->entry->company->document->region = new stdClass();
        $out->entry->company->document->region = getRegion($userdetail["docregionid"], $db);
        
        //Validamos la ciudad del documento de identificacion
        $out->entry->company->document->city = new stdClass();
        $out->entry->company->document->city = getCity($userdetail["doccityid"], $db);
        
        //Validamos el codigo postal del documento de identificacion
        $out->entry->company->document->postalcode = new stdClass();
        $out->entry->company->document->postalcode->value = $userdetail["postalcode"];
        
        //Validamos el tipo de empresa
        $out->entry->company->type = new stdClass();
        $out->entry->company->type = getCompanyType($userdetail["ctype"]);
        
        //Validamos la pagina web de la empresa
        $out->entry->company->website = $userdetail["website"];
        
        //Validamos la cantidad de oficinas de la empresa
        $out->entry->company->branchoffices = $userdetail["branchoffices"] == null ? null : (int)$userdetail["branchoffices"];
        
        //Validamos la cantidad de socios
        $out->entry->company->partnersnumber = (int)$userdetail["partners"];
        
        //Validamos si la empresa es venezolana
        $out->entry->company->venezuelan = $userdetail["venezuelan"] == null ? null : (int)$userdetail["venezuelan"];
        
        //Consulta para obtener socios
        $sql = "SELECT * FROM usercompanypartners".
        "       WHERE companyid = ".$out->entry->id;
        if (!$partnerrow=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        while($partnerdata = $partnerrow->fetch_assoc()){
            
            $partnerrejecteds = explode(",",$partnerdata["rejected"]);
            $partnerapproveds = explode(",",$partnerdata["approved"]);
        
            $partner = new stdClass();
            $partner->id = (int)$partnerdata["id"];
            
            $partner->name = $partnerdata["name"];
            
            $partner->document = $partnerdata["doc"];
            
            $partner->phone = $partnerdata["phone"];
            
            $partner->legalrep = (int)$partnerdata["legalrep"];
            
            $partner->politicalexposed = (int)$partnerdata["politicallyexposed"];
            
            $partner->affiliationbody = $partnerdata["affiliationbody"];
            
            $partner->positionheld = $partnerdata["positionheld"];
            
            $partner->pepcountryid = (int)$partnerdata["pepcountryid"];
            
            $partner->peprelatedid = $partnerdata["peprelatedid"];
            
            $partners[] = $partner;
        }
        
        $out->entry->company->partners = $partners;
        
        //Validamos el numero de teleono de la empresa
        $out->entry->company->phone = $userdetail["phone"];
        
        //Valida el pais base de la empresa
        $out->entry->company->country = new stdClass();
        $out->entry->company->country = getCountry($userdetail["countryid"], $db);
        
        //Validamos la region base de la empresa
        $out->entry->company->region = new stdClass();
        $out->entry->company->region = getRegion($userdetail["regionid"], $db);
        
        //Validamos la ciudad base de la empresa
        $out->entry->company->city = new stdClass();
        $out->entry->company->city = getCity($userdetail["cityid"], $db);
        
        //Validamos la calle de la empresa
        $out->entry->company->street = $userdetail["street"];
        
        //Validamos el sitio de la empresa
        $out->entry->company->home = $userdetail["domicile"];
        
        //Validamos si es un comerciante independiente
        $out->entry->company->independentdealer = $userdetail["independentdealer"] == null ? null : (int)$userdetail["independentdealer"];
        
        //Validamos si se encuentras politicamente expuestos
        $out->entry->company->politicalexposed = $userdetail["politicallyexposed"] == null ? null : (int)$userdetail["politicallyexposed"];
        
        //Por que todos los campos tienen que validarse:(
        
        //Validamos el ingreso anual de la empresa
        $out->entry->company->anualincome = new stdClass();
        $out->entry->company->anualincome->number = $userdetail["anualincome"] == null ? null : (float)$userdetail["anualincome"];
        $out->entry->company->anualincome->formatted = $userdetail["anualincome"] == null ? null : number_format($out->entry->company->anualincome->number,2,",",".");
        
        //Validamos el patrimonio de la empresa
        $out->entry->company->patrimony = new stdClass();
        $out->entry->company->patrimony->number = $userdetail["patrimony"] == null ? null : (float)$userdetail["patrimony"];
        $out->entry->company->patrimony->formatted = $userdetail["patrimony"] == null ? null : number_format($out->entry->company->patrimony->number,2,",",".");
        
        //Validamos la actividad economica
        $out->entry->company->economicalactivity = $userdetail["economicactivity"];
        
        //Validamos la cantidad de operaciones mensuales
        $out->entry->company->qtyoperationsmothly = $userdetail["qtymonthlyop"];
        
        //Validamos el monto por operaciones mensuales
        $out->entry->company->amooperationsmonthly = new stdClass();
        $out->entry->company->amooperationsmonthly->number = $userdetail["amountmontlyop"];
        //$out->entry->company->amooperationsmonthly->formatted = number_format($out->entry->company->amooperationsmonthly->number,2,",",".");
        
        //Validamos el tipo de operacion preferida
        $out->entry->company->preferoptype = new stdClass();
        $out->entry->company->preferoptype->id = $userdetail["prefertypeop"] == null ? null : $userdetail["prefertypeop"];
        $out->entry->company->preferoptype->dsc = "";
        
        if($out->entry->company->preferoptype->id != null){
            // separamos los ids
            $idArr = explode('-',$out->entry->company->preferoptype->id);
            
            foreach($idArr as $idop){
                if($idop == 1){
                    $out->entry->company->preferoptype->dsc .= ($out->entry->company->preferoptype->dsc != '' ? ',' : '')."Cripto - Cripto (C-C)";
                }else if($idop == 2){
                    $out->entry->company->preferoptype->dsc .= ($out->entry->company->preferoptype->dsc != '' ? ',' : '')."Cripto - Dinero Fiat (C-DF)";
                }else if($idop == 3){
                    $out->entry->company->preferoptype->dsc .= ($out->entry->company->preferoptype->dsc != '' ? ',' : '')."Dinero Fiat - Cripto (DF-C)";
                }else{
                    $out->entry->company->preferoptype->dsc = "";
                }
            }
        }else{
            $out->entry->company->preferoptype->dsc = "";
        }
        
        //Validamos si tiene relaciones economicas
        $out->entry->company->economicrelation = $userdetail["economicrelation"] == null ? null : (int)$userdetail["economicrelation"];
        
        //Consulta para obtener los documentos que ha subido el usuario 
        $sql = "SELECT * FROM useruploads".
        "       WHERE userid = ".$out->entry->id;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        while($uploadarr = $rs->fetch_assoc()){
            
            //Consultamos los datos de cada tipo de documento
            $sql = "SELECT * FROM cmsprefuploads".
            "       WHERE id = ".$uploadarr["prefuploadid"];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $uploaddetail = $res->fetch_assoc();
            
            //Buscamos el archivo para obtener su nombre y extension
            $filestest = glob("../../../uploads/users/".$out->entry->id."/".str_pad($uploadarr["prefuploadid"], 2, "0", STR_PAD_LEFT).".{jpg,png,jpeg,pdf,PNG,JPG,JPEG,PDF}", GLOB_BRACE);
            
            //El primer resultado
            $info = pathinfo($filestest[0]);
            
            
            if($info['basename'] != ""){
                $upload = new stdClass();
                //Generamos el id de salida
                $upload->id = str_pad($uploadarr["prefuploadid"], 2, "0", STR_PAD_LEFT);
                
                $upload->dsc = $uploaddetail["dsc"];
            
                //Url dentro de la carpeta api
                $upload->type = ".".pathinfo($filestest[0], PATHINFO_EXTENSION);
                //Url dentro de la carpeta api
                //$upload->url = $uploadsURL."users/".$out->entry->id."/".$info["basename"];
                $upload->url = $uploadsURLapp."?upid=". $upload->id."&sessionid=".$sessionid;
                $upload->validated = (int)$uploadarr["validated"];
                
                $upload->level = new stdClass();
            
                //Consultamos los datos del nivel
                $sql = "SELECT * FROM levels";
                if (!$resUp=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                
                while($levelsTemp = $resUp->fetch_assoc()){
                    
                    $levelsarrTemp = explode("-",$levelsTemp["cmsprefuploads"]);
                    foreach($levelsarrTemp as $whichlevel){
                        if($uploadarr["prefuploadid"] == $whichlevel){
                            $upload->level->id = (int)$levelsTemp["orden"];
                            $upload->level->dsc = $levelsTemp["name"];
                        }
                    }
                }
                
                $arrRequiresUp = explode("-",$levels["cmsprefuploads"]);
                $upload->required = validateUpload($uploadarr["prefuploadid"],$arrRequiresUp);
                
                $uploads[] = $upload;
            }
        }
        
        $out->entry->company->uploads = $uploads;
    }
    
    // Obtenemos las cuentas del usuario
    $sql = "SELECT acc.id AS accountid, ".
    "       acc.status AS status, ".
    "       users.id AS userid, ".
    "       cur.symbol AS currencysymbol, ".
    "       cur.name AS currencyname, ".
    "       cur.id AS currencyid, ".
    "       cur.interface AS currencyinterface, ".
    "       cur.decimals AS currencydecimals, ".
    "       IF((SELECT SUM(transactions.amount) ".
    "         	FROM transactions ".
    "          	WHERE transactions.accountid = acc.id ".
    "       ) IS NOT NULL, (SELECT SUM(transactions.amount) ".
    "       	FROM transactions ".
    "          	WHERE transactions.accountid = acc.id ".
    "       ), 0) AS avaliable, ".
    "       IF((SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND acc.id = transactions.accountid ".
    "           AND transactions.amountghost >= 0 ".
    "       ) IS NOT NULL, (SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND acc.id = transactions.accountid ".
    "           AND transactions.amountghost >= 0 ".
    "       ), 0) AS deferred, ".
    "       ( IF((SELECT SUM(transactions.amount) ".
    "          	FROM transactions ".
    "          	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
    "           (SELECT SUM(transactions.amount) ".
    "             	FROM transactions ".
    "              	WHERE transactions.accountid = acc.id), ".
    "       0) ".
    "       + IF((SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND acc.id = transactions.accountid ".
    "           AND transactions.amountghost >= 0) IS NOT NULL, ".
    "           (SELECT SUM(deposits.amount - deposits.fee) ".
    "               FROM transactions, deposits ".
    "               WHERE transactions.id = deposits.transactionid ".
    "               AND deposits.rejected IS NULL ".
    "               AND deposits.valid IS NULL ".
    "               AND acc.id = transactions.accountid ".
    "               AND transactions.amountghost >= 0), ".
    "       0)) AS total, ".
    "       (SELECT transactions.datecreated FROM  transactions ".
    "           WHERE acc.id = transactions.accountid ".
    "           AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
    "           ORDER BY transactions.datecreated DESC, transactions.id DESC ".
    "           LIMIT 1 ".
    "       ) AS lasttx ".
    "       FROM accounts acc, users, currencies cur ".
	"       WHERE users.id = acc.userid ".
	"       AND users.id = ".$userid.
    "       AND acc.paymentmethodid = -1 ".
    "       AND acc.currencyid = cur.id";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $out->entry->accounts = [];
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        
        $record->id = (int)$row['accountid'];
        
        $record->currency = new stdClass();
        $record->currency->id = (int)$row['currencyid'];
        $record->currency->dsc = $row['currencyname'];
        $record->currency->symbol = $row['currencysymbol'];
        $record->currency->decimals = $row['currencydecimals'];
        $record->currency->interface = $row['currencyinterface'];
        
        $record->avaliable = new stdClass();
        $record->avaliable->number = number_format($row['avaliable'],$row['currencydecimals'], '.', '');
        $record->avaliable->formatted = $row['avaliable'] == null ? numberFormatt(0, $row['currencydecimals']) : numberFormatt($row['avaliable'], $row['currencydecimals']);
        
        $record->committed = new stdClass();
        $record->committed->number = number_format($row['deferred'],$row['currencydecimals'], '.', '');
        $record->committed->formatted = $row['deferred'] == null ? numberFormatt(0, $row['currencydecimals']) : numberFormatt($row['deferred'], $row['currencydecimals']);
        
        $record->total = new stdClass();
        $record->total->number = number_format($row['total'],$row['currencydecimals'], '.', '');
        $record->total->formatted = $row['total'] == null ? numberFormatt(0, $row['currencydecimals']) : numberFormatt($row['total'], $row['currencydecimals']);
        
        $record->lasttransaction = new stdClass();
        $record->lasttransaction->date = $row['lasttx'] == null ? '' : date("Y-m-d H:i", strtotime($row["lasttx"]));
        $record->lasttransaction->formatted = $row['lasttx'] == null ? '' : date("d/m/Y h:i a", strtotime($row["lasttx"]));
        
        $record->status = (int)$row['status'];
        
        // Validamos si tiene wallet
        $sql = "SELECT COUNT(*) AS qty ".
        "       FROM wallets ".
        "       WHERE accountid = ".$row['accountid'];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $rowWallet = $res->fetch_assoc();
        
        if($rowWallet['qty'] > 0){
            $record->wallet = new stdClass();
            
            $sql = "SELECT *".
            "       FROM wallets ".
            "       WHERE accountid = ".$row['accountid'];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $rowWallet = $res->fetch_assoc();
            
            $record->wallet->id = (int)$rowWallet['id'];
            $record->wallet->dsc = $rowWallet['wallet'];
            
            $record->expiry = new stdClass();
            $record->expiry->date = date("Y-m-d h:i:s", strtotime($rowWallet['expirationDate']));
            $record->expiry->formatted = date("d/m/Y", strtotime($rowWallet['expirationDate']));
        
        }else{
            $record->wallet = null;
        }
        
        $out->entry->accounts [] = $record;
    }
    
    // Operaciones
    $out->entry->operations = new stdClass();
    
    $sql = "SELECT t.id AS transactionid, ".
    "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
    "       deposits.createddate AS createddate, ".
    "       deposits.amount AS amount, ".
    "       deposits.fee AS fee, ".
    "       deposits.valid AS valid, ".
    "       deposits.rejected AS rejected, ".
    "       (ABS(deposits.amount) + ABS(deposits.fee)) AS test, ".
    "       t.accountid AS txaccount, ".
    "       t.dsc AS txdsc, ".
    "       t.accountdestination AS txaccdestination, ".
    "       accounts.currencyid AS accountcurrencyid, ".
    "       accounts.paymentmethodid AS accountpaymentmethodid, ".
    "       accounts.status AS accountstatus, ".
    "       accounts.rejected AS accountrejected, ".
    "       accounts.approved AS accountapproved, ".
    "       users.usr AS usr, ".
    "       users.id AS usrid, ".
    "       userpersons.fname AS personname, ".
    "       userpersons.flastname AS personlastname, ".
    "       usercompanies.name AS companyname, ".
    "       usercompanies.comercialname AS companycomercialname, ".
    "       CASE ".
    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 0 ".
    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 1 ".
    "           WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 2 ".
    "       END AS status, ".
    "       CASE ".
    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 'Pendiente' ".
    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 'Aprobado' ".
    "           WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 'Rechazado' ".
    "       END AS statusorder ".
    "       FROM deposits, transactions t, accounts, users ".
    "".
    "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
    "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
    "".
    "       WHERE t.id = deposits.transactionid ".
    "       AND t.validator IS NULL ".
    "       AND t.accountid = accounts.id ".
    "       AND accounts.userid = users.id ".
    "       AND users.id = ".$userid.
    "".
    "       UNION ".
    "".
    "       SELECT t.id AS transactionid, ".
    "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
    "       withdrawals.createddate AS createddate, ".
    "       withdrawals.amount AS amount, ".
    "       withdrawals.fee AS fee, ".
    "       withdrawals.valid AS valid, ".
    "       withdrawals.rejected AS rejected, ".
    "       (ABS(withdrawals.amount) + ABS(withdrawals.fee)) AS test, ".
    "       t.accountid AS txaccount, ".
    "       t.dsc AS txdsc, ".
    "       t.accountdestination AS txaccdestination, ".
    "       accounts.currencyid AS accountcurrencyid, ".
    "       accounts.paymentmethodid AS accountpaymentmethodid, ".
    "       accounts.status AS accountstatus, ".
    "       accounts.rejected AS accountrejected, ".
    "       accounts.approved AS accountapproved, ".
    "       users.usr AS usr, ".
    "       users.id AS usrid, ".
    "       userpersons.fname AS personname, ".
    "       userpersons.flastname AS personlastname, ".
    "       usercompanies.name AS companyname, ".
    "       usercompanies.comercialname AS companycomercialname, ".
    "       CASE ".
    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 0 ".
    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN 1 ".
    "           WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 2 ".
    "       END AS status, ".
    "       CASE ".
    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 'Pendiente' ".
    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN 'Aprobado' ".
    "           WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 'Rechazado' ".
    "       END AS statusorder ".
    "       FROM withdrawals, transactions t, accounts, users ".
    "".
    "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
    "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
    "".
    "       WHERE t.id = withdrawals.transactionid ".
    "       AND t.validator IS NULL ".
    "       AND t.accountid = accounts.id ".
    "       AND accounts.userid = users.id ".
    "       AND users.id = ".$userid.
    "".
    "       UNION ".
    "".
    "       SELECT t.id AS transactionid, ".
    "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
    "       t.datecreated AS createddate, ".
    "       t.amount AS amount, ".
    "       t.amountghost AS fee, ".
    "       t.txid AS valid, ".
    "       t.txid AS rejected, ".
    "       (t.amount) AS test, ".
    "       t.accountid AS txaccount, ".
    "       t.dsc AS txdsc, ".
    "       t.accountdestination AS txaccdestination, ".
    "       accounts.currencyid AS accountcurrencyid, ".
    "       accounts.paymentmethodid AS accountpaymentmethodid, ".
    "       accounts.status AS accountstatus, ".
    "       accounts.rejected AS accountrejected, ".
    "       accounts.approved AS accountapproved, ".
    "       users.usr AS usr, ".
    "       users.id AS usrid, ".
    "       userpersons.fname AS personname, ".
    "       userpersons.flastname AS personlastname, ".
    "       usercompanies.name AS companyname, ".
    "       usercompanies.comercialname AS companycomercialname, ".
    "       CASE ".
    "           WHEN t.amountghost = 0 THEN 1 ".
    "           ELSE 0 ".
    "       END AS status, ".
    "       CASE ".
    "           WHEN t.amountghost = 0 THEN 'Aprobado' ".
    "           ELSE 'Pendiente' ".
    "       END AS statusorder ".
    "       FROM transactions t, accounts, users ".
    "".
    "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
    "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
    "".
    "       WHERE t.dsc LIKE 'Transferencia%'".
    "       AND t.validator IS NULL ".
    "       AND t.amount != 0".
    "       AND t.accountid = accounts.id ".
    "       AND accounts.userid = users.id ".
    "       AND users.id = ".$userid.
    
    "       ORDER BY createddate DESC";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $out->entry->operations->records = [];
    //Ordenamos los resultados(bof)
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        $record->id = (int)$row["transactionid"];
        
        $record->date = new stdClass();
        $record->date->cannonical = date("YmdHi", strtotime($row["createddate"]));
        $record->date->formatted = date("d/m/Y h:i a", strtotime($row["createddate"]))." - ".date("h:i a", strtotime($row["createddate"]));
        
        //Consultamos para saber si es un retiro o un deposito
        $sql = "SELECT transactions.id AS transactionid, ".
        "       deposits.transactionid AS depositid, ".
        "       withdrawals.transactionid AS withdrawalid ".
        "       FROM transactions ".
        "       LEFT JOIN deposits ON transactions.id = deposits.transactionid".
        "       LEFT JOIN withdrawals ON transactions.id = withdrawals.transactionid".
        "       WHERE transactions.id = ".$record->id;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
    
        $validtype = $res->fetch_assoc();
        $record->type = new stdClass();
    
        // 1 = DEPOSITO; 2 = RETIRO WN
        //Si el campo depositid es distinto a null significa que es un deposito
        if($validtype["depositid"] != null){
            $record->type->id = 1;
            $record->type->dsc = "Dep贸sito";
        }else if($validtype["withdrawalid"] != null){
            $record->type->id = 2;
            $record->type->dsc = "Retiro";
        }else{
            $record->type->id = 3;
            $record->type->dsc = "Transferencia";
        }
    
        //Validamos el status
        if($row["valid"] != null && $row["rejected"] == null){
            $record->status = 1;
        }else if($row["valid"] == null && $row["rejected"] != null){
            $record->status = 2;
        }else{
            $record->status = 0;
        }
        
        if($record->type->id == 3){
            $record->status = (int)$row["status"];
        }
        
        //Consultamos la cuenta del movimiento
        $record->account = new stdClass();
        
        $record->account->id = (int)$row["txaccount"];
        
        //Ordenamos los datos del usuario
        $record->account->user = new stdClass();
        $record->account->user->id = (int)$row["usrid"];
        $record->account->user->usr = $row["usr"];
    
        if($row["personname"] == null){
            $record->account->user->fullname = $row["companyname"].( $row["companycomercialname"] == "" ? "" : " / ".$row["companycomercialname"] );
        }else{
            $record->account->user->fullname = $row["personname"].( $row["personlastname"] == "" ? "" : " ".$row["personlastname"] );
        }
        
        $sql = "SELECT dsc ".
        "       FROM paymentmethods ".
        "       WHERE id = ".$row["accountpaymentmethodid"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $paymentmethod = $res->fetch_assoc();
        
        $record->account->paymentmethod = new stdClass();
        $record->account->paymentmethod->id = (int)$row["accountpaymentmethodid"];
        $record->account->paymentmethod->dsc = $paymentmethod["dsc"];
        
        //Consultamos la wallet(si existejjeje)
        $sql = "SELECT * FROM wallets".
        "       WHERE accountid = ".$record->account->id;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $wallet = $res->fetch_assoc();
    
        $record->account->wallet = $wallet["wallet"];
    
        $sql = "SELECT * FROM currencies WHERE id = ".$row["accountcurrencyid"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $currencydata = $res->fetch_assoc();
        
        $record->account->currency = new stdClass();
        $record->account->currency->id = (int)$currencydata["id"];
        $record->account->currency->name = $currencydata["name"];
        $record->account->currency->symbol = $currencydata["symbol"];
        $record->account->currency->interface = $currencydata['interface'];
        $record->account->currency->decimals = $currencydata['decimals'];
        
        $record->account->status = new stdClass();
        $record->account->status->id = (int)$row["accountstatus"];
        $record->account->status->dsc = $record->account->status->id == 1 ? "Activa" : "Inactiva";
    
        if($row["accountrejected"] != null){
            $record->account->valid = -1;
        }else if($row["accountapproved"] != null && $row["accountrejected"] == null){
            $record->account->valid = 1;
        }else{
            $record->account->valid = 0;
        }
        // 1 = DEPOSITO; 2 = RETIRO WN
        //Datos de la cuenta de AFX DEPOSITOS
        //Si no existe acc destination es por que es un retiro
        if($record->type->id == 2){
            $sql = "SELECT * FROM withdrawals WHERE transactionid = ".$record->id;
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $accountdata = $res->fetch_assoc();
            
            if($accountdata["paymentwallet"] != null){
                $record->userpaymentmethod = new stdClass();
                $record->userpaymentmethod->wallet = $accountdata["paymentwallet"];
            }else{
                $record->userpaymentmethod = new stdClass();
            
                $sql = "SELECT ".
                "       users.id AS userid, ".
                "       users.usr AS usr, ".
                "       accounts.id AS accountid, ".
                "       accounts.details1 AS accountdetails1, ".
                "       accounts.details2 AS accountdetails2, ".
                "       accounts.details3 AS accountdetails3, ".
                "       accounts.details4 AS accountdetails4, ".
                "       accounts.currencyid AS accountcurrencyid, ".
                "       paymentmethods.id AS paymentmethodid, ".
                "       paymentmethods.dsc AS paymetmenthoddsc, ".
                "       paymentmethods.details1 AS paymentmethoddetails1, ".
                "       paymentmethods.details2 AS paymentmethoddetails2, ".
                "       paymentmethods.details3 AS paymentmethoddetails3, ".
                "       paymentmethods.details4 AS paymentmethoddetails4 ".
                "       FROM accounts, users, paymentmethods ".
                "       WHERE accounts.id = ".$accountdata["paymentaccountid"].
                "       AND accounts.userid = users.id ".
                "       AND accounts.paymentmethodid = paymentmethods.id";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                
                $paymentaccountdata = $res->fetch_assoc();
    
                $record->userpaymentmethod->id = (int)$paymentaccountdata["accountid"];
                
                //Ordenamos los datos del usuario
                $record->userpaymentmethod->user = new stdClass();
                $record->userpaymentmethod->user->id = (int)$paymentaccountdata["userid"];
                $record->userpaymentmethod->user->fullname = $paymentaccountdata["usr"];
                
                $record->userpaymentmethod->paymentmethod = new stdClass();
                $record->userpaymentmethod->paymentmethod->id = (int)$paymentaccountdata["paymentmethodid"];
                $record->userpaymentmethod->paymentmethod->dsc = $paymentaccountdata["paymetmenthoddsc"];
                $record->userpaymentmethod->paymentmethod->details1 = $paymentaccountdata["paymentmethoddetails1"];
                $record->userpaymentmethod->paymentmethod->details2 = $paymentaccountdata["paymentmethoddetails2"];
                $record->userpaymentmethod->paymentmethod->details3 = $paymentaccountdata["paymentmethoddetails3"];
                $record->userpaymentmethod->paymentmethod->details4 = $paymentaccountdata["paymentmethoddetails4"];
                
                //Consultamos la wallet(si existejjeje)
                $sql = "SELECT * FROM wallets".
                "       WHERE accountid = ".$record->userpaymentmethod->id;
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $wallet = $res->fetch_assoc();
                
                $record->userpaymentmethod->wallet = $wallet["wallet"];
                
                $sql = "SELECT * FROM currencies WHERE id = ".$paymentaccountdata["accountcurrencyid"];
                if (!$res=$db->query($sql))
                     badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $currencydata = $res->fetch_assoc();
                
                $record->userpaymentmethod->currency = new stdClass();
                $record->userpaymentmethod->currency->id = (int)$currencydata["id"];
                $record->userpaymentmethod->currency->name = $currencydata["name"];
                $record->userpaymentmethod->currency->symbol = $currencydata["symbol"];
                $record->userpaymentmethod->currency->interface = $currencydata["interface"];
                
                $record->userpaymentmethod->details1 = $paymentaccountdata["details1"] == null ? '' : $paymentaccountdata["details1"];
                $record->userpaymentmethod->details2 = $paymentaccountdata["details2"] == null ? '' : $paymentaccountdata["details2"];
                $record->userpaymentmethod->details3 = $paymentaccountdata["details3"] == null ? '' : $paymentaccountdata["details3"];
                $record->userpaymentmethod->details4 = $paymentaccountdata["details4"] == null ? '' : $paymentaccountdata["details4"];
            }
        }else if($row["txaccdestination"] == null){
            $record->userpaymentmethod = null;
        } else {
            $record->userpaymentmethod = new stdClass();
            
            $sql = "SELECT ".
            "       users.id AS userid, ".
            "       users.usr AS usr, ".
            "       accounts.id AS accountid, ".
            "       accounts.details1 AS accountdetails1, ".
            "       accounts.details2 AS accountdetails2, ".
            "       accounts.details3 AS accountdetails3, ".
            "       accounts.details4 AS accountdetails4, ".
            "       accounts.currencyid AS accountcurrencyid, ".
            "       paymentmethods.id AS paymentmethodid, ".
            "       paymentmethods.dsc AS paymetmenthoddsc, ".
            "       paymentmethods.details1 AS paymentmethoddetails1, ".
            "       paymentmethods.details2 AS paymentmethoddetails2, ".
            "       paymentmethods.details3 AS paymentmethoddetails3, ".
            "       paymentmethods.details4 AS paymentmethoddetails4 ".
            "       FROM accounts, users, paymentmethods ".
            "       WHERE accounts.id = ".$row["txaccdestination"].
            "       AND accounts.userid = users.id ".
            "       AND accounts.paymentmethodid = paymentmethods.id";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $accountdata = $res->fetch_assoc();
    
            $record->userpaymentmethod->id = (int)$accountdata["accountid"];
                
            //Ordenamos los datos del usuario
            $record->userpaymentmethod->user = new stdClass();
            $record->userpaymentmethod->user->id = (int)$accountdata["userid"];
            $record->userpaymentmethod->user->fullname = $accountdata["usr"];
            
            $record->userpaymentmethod->paymentmethod = new stdClass();
            $record->userpaymentmethod->paymentmethod->id = (int)$accountdata["paymentmethodid"];
            $record->userpaymentmethod->paymentmethod->dsc = $accountdata["paymetmenthoddsc"];
            //Consultamos la wallet(si existejjeje)
            $sql = "SELECT * FROM wallets".
            "       WHERE accountid = ".$record->userpaymentmethod->id;
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $wallet = $res->fetch_assoc();
                
            $record->userpaymentmethod->wallet = $wallet["wallet"];
            
            $sql = "SELECT * FROM currencies WHERE id = ".$accountdata["accountcurrencyid"];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $currencydata = $res->fetch_assoc();
                
            $record->userpaymentmethod->currency = new stdClass();
            $record->userpaymentmethod->currency->id = (int)$currencydata["id"];
            $record->userpaymentmethod->currency->name = $currencydata["name"];
            $record->userpaymentmethod->currency->symbol = $currencydata["symbol"];
            $record->userpaymentmethod->currency->interface = $currencydata["interface"];
            
            $record->userpaymentmethod->details1 = $accountdata["details1"] == null ? '' : $accountdata["details1"];
            $record->userpaymentmethod->details2 = $accountdata["details2"] == null ? '' : $accountdata["details2"];
            $record->userpaymentmethod->details3 = $accountdata["details3"] == null ? '' : $accountdata["details3"];
            $record->userpaymentmethod->details4 = $accountdata["details4"] == null ? '' : $accountdata["details4"];
        }
        
        //Si es una transferencia necesitamos consultar el movimiento con su amountghost por si fue rechazada
        if($record->type->id == 3){
            $sql = "SELECT * FROM transactions".
            "       WHERE id != ".$record->id.
            "       AND txid = ".$record->id.
            "       AND accountid = ".$record->account->id;
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $txdataFee = $res->fetch_assoc();
            //Monto del movimiento
            $record->amount = new stdClass();
            $record->amount->number = ($row["amount"] + $row["fee"]) + ($txdataFee["amount"] + $txdataFee["amountghost"]);
            $record->amount->formatted = numberFormatt($record->amount->number,$currencydata["decimals"]);
        }else{
            //Monto del movimiento
            $record->amount = new stdClass();
            $record->amount->number = (float)$row["amount"] ;
            $record->amount->formatted = numberFormatt($record->amount->number,$currencydata["decimals"]);
        }
        
    
        //Si es una transferencia necesitamos consultar el movimiento con su comision
        if($record->type->id == 3){
            //Consultamos el movimiento de comision de la cuenta
            $sql = "SELECT * FROM transactions".
            "       WHERE id != ".$record->id.
            "       AND txid = ".$record->id.
            "       AND accountid = ".$record->account->id;
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $row = $res->fetch_assoc();
            //Comision del movimiento
            $record->fee = new stdClass();
            $record->fee->number = abs((float)$row["amount"] + $row["amountghost"]);
            $record->fee->formatted = numberFormatt($record->fee->number,$currencydata["decimals"]);
        }else{
            //Comision del movimiento
            $record->fee = new stdClass();
            $record->fee->number = (float)$row["fee"];
            $record->fee->formatted = numberFormatt($record->fee->number,$currencydata["decimals"]);
        }
        
        $out->entry->operations->records [] = $record;
    }


    // Obtenemos los subtotales (deposito,retiro,transacciones) POR MONEDA
    
    // Depositos
    $sql = "SELECT acc.id AS accountid, ".
    "       acc.status AS status, ".
    "       users.id AS userid, ".
    "       cur.symbol AS currencysymbol, ".
    "       cur.name AS currencyname, ".
    "       cur.id AS currencyid, ".
    "       cur.interface AS currencyinterface, ".
    "       cur.decimals AS currencydecimals, ".
    "       ( SELECT SUM(ABS(deposits.amount)) AS amount ".
    "           FROM deposits, transactions ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND transactions.accountid = acc.id ".
    "           AND deposits.valid IS NOT NULL ".
    "       ) AS amount".
    "       FROM accounts acc, users, currencies cur ".
	"       WHERE users.id = acc.userid ".
	"       AND users.id = ".$userid.
    "       AND acc.paymentmethodid = -1 ".
    "       AND acc.currencyid = cur.id";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $out->entry->operations->subtotals->deposit = [];
    
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        
        $record->currency = new stdClass();
        $record->currency->id = (int)$row['currencyid'];
        $record->currency->name = $row['currencyname'];
        $record->currency->symbol = $row['currencysymbol'];
        
        $record->amount = new stdClass();
        $record->amount->number = (float)number_format($row['amount'], $row['currencydecimals'], '.', '');
        $record->amount->formatted = numberFormatt($row['amount'], $row['currencydecimals']);
        
        $out->entry->operations->subtotals->deposit [] = $record;
    }
    
    // Retiros
    $sql = "SELECT acc.id AS accountid, ".
    "       acc.status AS status, ".
    "       users.id AS userid, ".
    "       cur.symbol AS currencysymbol, ".
    "       cur.name AS currencyname, ".
    "       cur.id AS currencyid, ".
    "       cur.interface AS currencyinterface, ".
    "       cur.decimals AS currencydecimals, ".
    "       ( SELECT SUM(ABS(withdrawals.amount)) AS amount ".
    "           FROM withdrawals, transactions ".
    "           WHERE transactions.id = withdrawals.transactionid ".
    "           AND transactions.accountid = acc.id ".
    "           AND withdrawals.valid IS NOT NULL ".
    "       ) AS amount".
    "       FROM accounts acc, users, currencies cur ".
	"       WHERE users.id = acc.userid ".
	"       AND users.id = ".$userid.
    "       AND acc.paymentmethodid = -1 ".
    "       AND acc.currencyid = cur.id";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $out->entry->operations->subtotals->witdrawals = [];
    
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        
        $record->currency = new stdClass();
        $record->currency->id = (int)$row['currencyid'];
        $record->currency->name = $row['currencyname'];
        $record->currency->symbol = $row['currencysymbol'];
        
        $record->amount = new stdClass();
        $record->amount->number = (float)number_format($row['amount'], $row['currencydecimals'], '.', '');
        $record->amount->formatted = numberFormatt($row['amount'], $row['currencydecimals']);
        
        $out->entry->operations->subtotals->witdrawals [] = $record;
    }
    
    // transferencias
    $sql = "SELECT acc.id AS accountid, ".
    "       acc.status AS status, ".
    "       users.id AS userid, ".
    "       cur.symbol AS currencysymbol, ".
    "       cur.name AS currencyname, ".
    "       cur.id AS currencyid, ".
    "       cur.interface AS currencyinterface, ".
    "       cur.decimals AS currencydecimals, ".
    "       ( SELECT SUM(ABS(t.amount)) AS amount ".
    "           FROM transactions t".
    "           WHERE t.accountid = acc.id ".
    "           AND (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4".
    "       ) AS amount".
    "       FROM accounts acc, users, currencies cur ".
	"       WHERE users.id = acc.userid ".
	"       AND users.id = ".$userid.
    "       AND acc.paymentmethodid = -1 ".
    "       AND acc.currencyid = cur.id";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $out->entry->operations->subtotals->xfer = [];
    
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        
        $record->currency = new stdClass();
        $record->currency->id = (int)$row['currencyid'];
        $record->currency->name = $row['currencyname'];
        $record->currency->symbol = $row['currencysymbol'];
        
        $record->amount = new stdClass();
        $record->amount->number = (float)number_format($row['amount'], $row['currencydecimals'], '.', '');
        $record->amount->formatted = numberFormatt($row['amount'], $row['currencydecimals']);
        
        $out->entry->operations->subtotals->xfer [] = $record;
    }
    
    // Intercambio
    $sql = "SELECT orders.id AS orderid, ".
    "       orders.quantity AS orderqty, ".
    "       IF(orders.type = 1, orders.side, IF(orders.side = 1, 2, 1)) AS orderside, ".
    "       (SELECT ord.price FROM orders ord ".
    "       WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS orderprice, ".
    "       orders.type AS ordertype, ".
    "       orders.pairid AS orderpair, ".
    "       orders.total AS ordertotal, ".
    "       t.id AS txid, ".
    "       t.txid AS exchangeid, ".
    "       t.orderid AS txotherorderid, ".
    "       t.datecreated AS datecreated, ".
    "       ABS(IF(orders.side = 2, t.amount, tx.amount)) AS qty, ".
    "       (IF(orders.side = 2, t.amount, tx.amount) * orders.price) AS totally ".
    "       FROM orders, accounts, users, transactions t ".
    "           LEFT JOIN transactions tx ".
    "               ON t.orderid = tx.orderid ".
    "               AND t.txid = tx.txid ".
    "               AND tx.amount < 0 ".
    "               AND tx.dsc NOT LIKE 'Comisi%'".
    "           LEFT JOIN transactions txother ".
    "               ON t.orderid != txother.orderid ".
    "               AND t.txid = txother.txid ".
    "               AND txother.amount > 0 ".
    "               AND txother.dsc NOT LIKE 'Comisi%'".
    "       WHERE t.orderid IS NOT NULL ".
    "       AND orders.id = txother.orderid ".
    "       AND t.id = t.txid ".
    "       AND t.dsc NOT LIKE 'Comisi%'".
    "       AND accounts.id = orders.accountid ".
    "       AND accounts.userid = users.id ".
    "       AND users.id = ".$userid.
    "       GROUP BY t.txid";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        $record->id = (int)$row['exchangeid'];
        
        $record->date = new stdClass();
        $record->date->cannonical = date("Y-m-d H:i:s", strtotime($row['datecreated']));
        $record->date->formatted = date("d/m/Y H:i", strtotime($row['datecreated']));
        
        $record->orderbuy = $row['ordertype'] == 1 && $row['orderside'] == 1 ? (int)$row['orderid'] : (int)$row['txotherorderid'];
        $record->ordersell = $row['orderid'] != $record->orderbuy ? (int)$row['orderid'] : (int)$row['txotherorderid'];
        
        $record->side = new stdClass();
        $record->side->id = (int)$row['orderside'];
        $record->side->dsc = $record->side->id == 1 ? 'Venta' : 'Compra';
        
        $record->type = new stdClass();
        $record->type->id = (int)$row['ordertype'];
        $record->type->dsc = $record->type->id == 1 ? 'Market' : 'Limit' ;
        
        $record->pair = new stdClass();
        $record->pair->id = (int)$row['orderpair'];
        
        //Consultamos el nombre de la moneda ref
        $sql = "SELECT currencies.symbol AS symbol ".
        "       FROM currencies, pairs ".
        "       WHERE pairs.id = ".$row["orderpair"].
        "       AND pairs.refcurrencyid = currencies.id ".
        "       LIMIT 1";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $refcurrency = $res->fetch_assoc();
    
        //Consultamos el nombre de la moneda base
        $sql = "SELECT currencies.symbol AS symbol ".
        "       FROM currencies, pairs ".
        "       WHERE pairs.id = ".$row['orderpair'].
        "       AND pairs.currencyid = currencies.id ".
        "       LIMIT 1";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $currency = $res->fetch_assoc();
        
        $record->pair->dsc = $refcurrency['symbol'].'/'.$currency['symbol'];
        
        //Obtenemos los datos del par para obtener los decimales base
        $sql = "SELECT pairs.refcurrencyid AS pairscurrency, ".
        "       currencies.decimals AS currencydecimals ".
        "       FROM pairs, currencies ".
        "       WHERE pairs.id AND".
        "       pairs.status = 1 AND ".
        "       pairs.id = ".$row['orderpair']." AND ".
        "       currencies.id = pairs.currencyid ".
        "       GROUP BY pairs.id";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $currencydata = $res->fetch_assoc();
    
        //Obtenemos los datos del par para obtener los decimales ref
        $sql = "SELECT pairs.refcurrencyid AS pairscurrency, ".
        "       currencies.decimals AS currencydecimals ".
        "       FROM pairs, currencies ".
        "       WHERE pairs.id AND".
        "       pairs.status = 1 AND ".
        "       pairs.id = ".$row['orderpair']." AND ".
        "       currencies.id = pairs.refcurrencyid ".
        "       GROUP BY pairs.id";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $refcurrencydata = $res->fetch_assoc();
    
        $record->price = new stdClass();
        $record->price->number = (float)$row['orderprice'];
        $record->price->formatted = number_format($record->price->number,$currencydata['currencydecimals'],",",".");
    
        $record->qty = new stdClass();
        $record->qty->number = (float)abs($row['qty']);
        $record->qty->formatted = number_format($record->qty->number,$refcurrencydata['currencydecimals'],",",".");
        
        //$record->testing = $row;
        $record->total = new stdClass();
        $record->total->number = (float) $record->qty->number * $record->price->number;
        $record->total->formatted = number_format($record->total->number,$currencydata['currencydecimals'],",",".");
    
        $out->entry->exchanges [] = $record;
    }
        
    $out->privileges = new stdClass();
    $out->privileges->read = tienePrivilegio($db, $sessionid, 71);
    
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    die();
    
    
    /* UTILIDAD */
    
    /*  Funcion para validar el tipo de empresa, donde se recibe:
    *   -ctype: ID para determinar el tipo de empresa
    *   donde:
    *   1 = Privada
    *   2 = Gubernametal
    *   3 = Mixta
    */
    function getCompanyType($ctype){
        $type = new stdClass();
        $type->id = (int)$ctype;
        
        if($type->id == 1){
            $type->dsc = "Privada";
        }else if($type->id == 2){
            $type->dsc = "Gubernamental";
        }else if($type->id == 3){
            $type->dsc = "Mixta";
        }
        
        return $type;
    }
    
    /* 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 = new stdClass();
        $type->id = (int)$typeid;
        
        if($type->id == 1){
            $type->dsc = "c茅dula";
        }else if($type->id == 2){
            $type->dsc = "pasaporte";
        }else if($type->id == 3){
            $type->dsc = "licencia de conducir";
        }else if($type->id == 0){
            $type->dsc = null;
        }
        
        return $type;
    }
    
    /* Funcion para validar el tipo de estado civil, donde:
    * 1 = Soltero/Soltera
    * 2 = Casado/Casada
    * 3 = Divorciado/Divorciada
    * 4 = Viudo/Viuda
    * la funciona recibe dos parametros que son:
    * -gender = genero de la persona que se esta consultando
    * -maritalid = id del estado civil que sera evaluado
    */
    function validateMarital($maritalid, $gender){
        //	1 Single | 2 Married | 3 Divorced | 4 Widower
        $maritalstatus = new stdClass();
        $maritalstatus->id = (int)$maritalid;
        
        if($maritalstatus->id == 1){
            $maritalstatus->dsc = $gender == "F" ? "Soltera" : "Soltero";
        }else if($maritalstatus->id == 2){
            $maritalstatus->dsc = $gender == "F" ? "Casada" : "Casado";
        }else if($maritalstatus->id == 3){
            $maritalstatus->dsc = $gender == "F" ? "Divorciada" : "Divorciado";
        }else if($maritalstatus->id == 4){
            $maritalstatus->dsc = $gender == "F" ? "Viuda" : "Viudo";
        }
        
        return $maritalstatus;
    }
    
    /* Funcion para obtener el pais por su id devolviendo un objeto:
    *  country: {
    *        id: id del pais,
    *        dsc: nombre del pais
    *   }
    *   
    *   Los parametros que recibe son countryid siendo el id del pais, y la conexion de la BD
    */
    function getCountry($countryid, $db){
        if($countryid != 0){
            $sql = "SELECT id, name".
            "       FROM countries".
            "       WHERE id = ".$countryid;
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$determinateDBError($db)));
            
            $countrydata = $rs->fetch_assoc();
        
            $countryformatted = new stdClass();
            $countryformatted->id = (int)$countrydata["id"];
            $countryformatted->dsc = $countrydata["name"];
        }else{
            $countryformatted = new stdClass();
            $countryformatted->id = null;
            $countryformatted->dsc = null;
        }
        
        return $countryformatted;
    }
    
    /* Funcion para obtener el estado por su id devolviendo un objeto:
    *  region: {
    *        id: id del estado,
    *        dsc: nombre del estado
    *   }
    *   
    *   Los parametros que recibe son regionid siendo el id del estado, y la conexion de la BD
    */
    function getRegion($regionid, $db){
        if($regionid != 0){
            $sql = "SELECT id, name".
            "       FROM regions".
            "       WHERE id = ".$regionid;
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $regiondata = $rs->fetch_assoc();
        
            $regionformatted = new stdClass();
            $regionformatted->id = (int)$regiondata["id"];
            $regionformatted->dsc = $regiondata["name"];
        }else{
            $regionformatted = new stdClass();
            $regionformatted->id = null;
            $regionformatted->dsc = null;
        }
        
        return $regionformatted;
    }
    
    /* Funcion para obtener la ciudad por su id devolviendo un objeto:
    *  region: {
    *        id: id de la ciudad,
    *        dsc: nombre de la ciudad
    *   }
    *   
    *   Los parametros que recibe son cityid siendo el id de la ciudad, y la conexion de la BD
    */
    function getCity($cityid, $db){
        if($cityid != 0){
            $sql = "SELECT id, name".
            "       FROM cities".
            "       WHERE id = ".$cityid;
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $citydata = $rs->fetch_assoc();
        
            $cityformatted = new stdClass();
            $cityformatted->id = (int)$citydata["id"];
            $cityformatted->dsc = $citydata["name"];
        }else{
            $cityformatted = new stdClass();
            $cityformatted->id = null;
            $cityformatted->dsc = null;
        }
        
        return $cityformatted;
    }
    
    /*  Funcion que valida si una imagen es requerida para subir de nivel o no
    *   $field = Campo que se esta validando
    *   $rejecteds = arreglo con imagenes requeridas
    */
    function validateUpload($field, $requireds){
        
        foreach($requireds as $required){
            if($required == $field){
                return true;
            }
        }
        
        return false;
    }
?>