<?php
// cms/api/reports/feealliesreport

    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",'order','datefrom','dateto','fee','tags');
    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"]);
    $order = determinateOrder(mysqli_real_escape_string($db, $_GET['order']));
    $datefrom = mysqli_real_escape_string($db, $_GET['datefrom']);
    $dateto = mysqli_real_escape_string($db, $_GET['dateto']);
    $fee = mysqli_real_escape_string($db, $_GET['fee']);
    $tags = explode('-',mysqli_real_escape_string($db, $_GET['tags']));
    $tagsfilter = '';
    
    //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"));
    
    // SI recibimos filter, armamos 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 .= " IF(userpersons.fname IS NULL, ".
                "           CONCAT(usercompanies.name, IF(usercompanies.comercialname = '', '', CONCAT(' ', usercompanies.comercialname)) ), ".
                "           CONCAT(userpersons.fname, IF(userpersons.sname = '', '', CONCAT(' ', userpersons.sname)),' ',userpersons.flastname, IF(userpersons.slastname = '', '', CONCAT(' ', userpersons.slastname))) ".
                "       ) LIKE '%".$el."%' ".
                "       OR users.usr LIKE '%".$el."%'";
                
            }else{
                
                $filter .= " OR IF(userpersons.fname IS NULL, ".
                "           CONCAT(usercompanies.name, IF(usercompanies.comercialname = '', '', CONCAT(' ', usercompanies.comercialname)) ), ".
                "           CONCAT(userpersons.fname, IF(userpersons.sname = '', '', CONCAT(' ', userpersons.sname)),' ',userpersons.flastname, IF(userpersons.slastname = '', '', CONCAT(' ', userpersons.slastname))) ".
                "       ) LIKE '%".$el."%' ".
                "       OR users.usr LIKE '%".$el."%'";
                
            }
        }
        
        $filter .= " ) ";
        
    }
    
    
    // Obtenemos los rates de los pares
    //Consultamos los datos requeridos
    $sql = "SELECT pairs.refcurrencyid AS pairsrefcurrency, ".
    "       currencies.name AS currencyname, currencies.symbol AS currencysymbol, ".
    "       currencies.icon AS currencyicon, ".
    "       currencies.id AS currencyid, pairs.id AS pairsid,".
    "       currencies.decimals AS currencydecimals ".
    "       FROM pairs, currencies ".
    "       WHERE pairs.status = 1 AND ".
    "       currencies.id = pairs.currencyid ".
    "       GROUP BY pairs.id".
    "       ORDER BY pairs.id ASC";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    while($pairdata = $rs->fetch_assoc()){
        $record = new stdClass();
        $record->id = (int) $pairdata["pairsid"];
            
        //Obtenemos la ultima transaccion realizada
        $sql = "SELECT (SELECT ord.price FROM orders ord ".
        "       WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS lastprice ".
        "       FROM orders, accounts, transactions t  ".
        "       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 orders.pairid = ".$record->id.
        "       AND t.id = t.txid ".
        "       AND t.dsc NOT LIKE 'Comisi%' ".
        "       AND accounts.id = orders.accountid ".
        "       GROUP BY t.txid ".
        "       ORDER BY t.datecreated DESC, t.id DESC".
        "       LIMIT 1";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $res->fetch_assoc();
        
        $record->lastprice = new stdClass();
        $record->lastprice->number = (float)number_format($row["lastprice"], $pairdata["currencydecimals"],".",'');
        $record->lastprice->formatted = number_format($record->lastprice->number,$pairdata["currencydecimals"],",",".");
        
        $sql = "SELECT * FROM currencies WHERE id = ".$pairdata["pairsrefcurrency"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $row = $res->fetch_assoc();
        
        $record->pair = $row["symbol"]."/".$pairdata["currencysymbol"];
        
        $rates [] = $record;
    }
    
    $out->rates = $rates;
    
    // Retornamos los tags
    $out->tags = implode(', ',$tags);
    
    // Hacemos el filtro para los tags
    foreach($tags as $index => $tag){
        
        if($index == 0){
            $tagsfilter .= " (users.tag LIKE  '%".$tag."%'";
        }else{
            $tagsfilter .= " OR users.tag LIKE '%".$tag."%'";
        }
        
    }
    
    $tagsfilter .= ")";
    
    $out->totalVES = new stdClass();
    
    $out->totals = [];
    
    // Obtenemos a los usuarios con esos tags
    $sql = "SELECT users.usr AS email, ".
    "       users.id AS id, ".
    "       users.datecreated AS datecreated, ".
    "       IF(userpersons.fname IS NULL, ".
    "           CONCAT(usercompanies.name, IF(usercompanies.comercialname = '', '', CONCAT(' ', usercompanies.comercialname)) ), ".
    "           CONCAT(userpersons.fname, IF(userpersons.sname = '', '', CONCAT(' ', userpersons.sname)),' ',userpersons.flastname, IF(userpersons.slastname = '', '', CONCAT(' ', userpersons.slastname))) ".
    "       ) AS name, ".
    "       users.level AS level, users.lang AS lang ".
    "       FROM users ".
    "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
    "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
    "       WHERE ".$tagsfilter.
    "       ".$filter.
    "       ".$order;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $records = [];
    while($userdata = $rs->fetch_assoc()){
        $record = new stdClass();
        
        // Ordenamos los datos del usuario
        $record->id = (int)$userdata['id'];
        $record->fullname = $userdata['name'];
        $record->usr = $userdata['email'];
        
        $record->datecreated = new stdClass();
        $record->datecreated->date = date('Y-m-d', strtotime($userdata['datecreated']));
        $record->datecreated->formatted = date('d/m/Y', strtotime($userdata['datecreated']));
        
        // Consultamos las cuentas internas del usuario
        $sql = "SELECT accounts.id AS accountid, ".
        "       currencies.interface AS interface, ".
        "       currencies.id AS currencyid, ".
        "       currencies.symbol AS currencysymbol, ".
        "       currencies.decimals AS decimals, ".
        "       currencies.icon AS icon, ".
        "       currencies.name AS currencyname ".
        "       FROM accounts, currencies ".
        "       WHERE accounts.paymentmethodid = -1".
        "       AND accounts.currencyid = currencies.id ".
        "       AND accounts.userid = ".$record->id.
        "       ORDER BY currencies.id ASC";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $accounts = [];
        while($accountdata = $res->fetch_assoc()){
            $account = new stdClass();
            
            $account->id = (int)$accountdata['accountid'];
            $account->interface = (int)$accountdata['interface'];
            
            $account->currency = new stdClass();
            $account->currency->id = (int)$accountdata['currencyid'];
            $account->currency->symbol = $accountdata['currencysymbol'];
            $account->currency->name = $accountdata['currencyname'];
            $account->currency->icon = $accountdata['icon'];
            $account->currency->decimals = (int)$accountdata['decimals'];
            
            // INgresamos la moneda para los totales si no existe
            if(sizeof($out->totals) == 0){
                $currencytmp = new stdClass();
                $currencytmp->currency = new stdClass();
                $currencytmp->currency->id = (int)$accountdata['currencyid'];
                $currencytmp->currency->symbol = $accountdata['currencysymbol'];
                $currencytmp->currency->name = $accountdata['currencyname'];
                $currencytmp->currency->icon = $accountdata['icon'];
                $currencytmp->currency->decimals = (int)$accountdata['decimals'];
                $currencytmp->number = 0;
                
                $out->totals [] = $currencytmp;
            }
            
            $currencyExist = false;
            
            foreach($out->totals as $totalcurrency){
                if($totalcurrency->currency->id == $account->currency->id){
                    $currencyExist = true;
                }
            }
            
            if(!$currencyExist){
                $currencytmp = new stdClass();
                $currencytmp->currency = new stdClass();
                $currencytmp->currency->id = (int)$accountdata['currencyid'];
                $currencytmp->currency->symbol = $accountdata['currencysymbol'];
                $currencytmp->currency->name = $accountdata['currencyname'];
                $currencytmp->currency->icon = $accountdata['icon'];
                $currencytmp->currency->decimals = (int)$accountdata['decimals'];
                $currencytmp->number = 0;
                
                $out->totals [] = $currencytmp;
            }
            
            // Obtenemos la fecha de creacion
            $sql = "SELECT date FROM audit ".
            "       WHERE userid = ".$record->id.
            "       AND application = 'APP' ".
            "       AND module = 'F. DE PAGO' ".
            "       AND dsc LIKE '%".$account->currency->name."%'";
            if (!$resdate=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $datecreated = $resdate->fetch_assoc();
            
            $account->datecreated = new stdClass();
            $account->datecreated->date = date('Y-m-d',strtotime($datecreated['date']));
            $account->datecreated->formatted = date('d/m/Y',strtotime($datecreated['date']));
            
            // Consultamos sus intercambios
            $sql = "SELECT orders.id AS orderid, ".
            "       orders.quantity AS orderqty, ".
            "       orders.type AS ordertype, ".
            "       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)) AS orderprice, ".
            "       t.txid AS txid, ".
            "       t.datecreated AS datecreated,  ".
            "       t.amount AS qty ".
            "       FROM orders, accounts, transactions t  ".
            "       WHERE t.orderid IS NOT NULL ".
            "       AND (orders.accountid = ".$account->id.
            '       OR orders.paymentid = '.$account->id.") ".
            "       AND t.accountid = ".$account->id.
            "       AND t.dsc LIKE 'Comisi%' ".
            "       AND t.orderid = orders.id ".
            "       AND accounts.id = orders.accountid ".
            "       AND DATE(t.datecreated) >= DATE('".$datefrom."') ".
            "       AND DATE(t.datecreated) <= DATE('".$dateto."') ".
            "       GROUP BY t.txid ".
            "       ORDER BY datecreated DESC, txid DESC";
            if (!$resEx=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
        
            $exchanges = [];
            $totalExchangeFee = 0;
            $totalExchange = 0;
            while($rowExchange = $resEx->fetch_assoc()){
                $exchange = new stdClass();
                $exchange->id = (int)$rowExchange['txid'];
                
                $exchange->datecreated = new stdClass();
                $exchange->datecreated->cannonical = date("Y-m-d", strtotime($rowExchange['datecreated']));
                $exchange->datecreated->formatted = date("d/m/Y", strtotime($rowExchange['datecreated']));
                
                $exchange->order = new stdClass();
                $exchange->order->id = (int)$rowExchange['orderid'];
                $exchange->order->type = $rowExchange['ordertype'] == 1 ? 'Market' : 'Limit';
                $exchange->order->side = $rowExchange['orderside'] == 1 ? 'Venta' : 'Compra';
                
                // Obtenemos el nombre del par
                $sql = "SELECT pairs.refcurrencyid AS pairsrefcurrency, ".
                "       currencies.name AS currencyname, currencies.symbol AS currencysymbol, ".
                "       currencies.icon AS currencyicon, ".
                "       currencies.id AS currencyid, pairs.id AS pairsid,".
                "       currencies.decimals AS currencydecimals ".
                "       FROM pairs, currencies, orders ".
                "       WHERE currencies.id = pairs.currencyid ".
                "       AND orders.pairid = pairs.id ".
                "       AND orders.id = ".$exchange->order->id;
                if (!$resPar=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                
                $parData = $resPar->fetch_assoc();
                    
                $sql = "SELECT * FROM currencies WHERE id = ".$parData["pairsrefcurrency"];
                if (!$resSecond=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $rowSecond = $resSecond->fetch_assoc();
                
                $exchange->pair = $rowSecond["symbol"]."/".$parData["currencysymbol"];
                
                // Obtenemos el fee del movimiento
                $sql = "SELECT ABS(amount) as amount ".
                "       FROM transactions ".
                "       WHERE txid = ".$exchange->id.
                "       AND orderid = ".$exchange->order->id.
                "       AND dsc LIKE 'Comisi%'".
                "       AND accountid = ".$account->id;
                if (!$resFee=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $feerow = $resFee->fetch_assoc();
                
                $exchange->feeamount = new stdClass();
                $exchange->feeamount->number = (float)$feerow['amount'];
                $exchange->feeamount->formatted = numberFormatt($exchange->feeamount->number, $account->currency->decimals);
                
                $exchange->feeallies = new stdClass();
                $exchange->feeallies->number = (float)($feerow['amount']*$fee)/100;
                $exchange->feeallies->formatted = numberFormatt($exchange->feeallies->number, $account->currency->decimals);
                
                $totalExchange += $exchange->feeallies->number;
                
                $totalExchangeFee += $exchange->feeamount->number;
                
                $exchanges [] = $exchange;
            }
            
            $account->subtotal = new stdClass();
            $account->subtotal->number = (float)number_format($totalExchange,$account->currency->decimals,'.','');
            $account->subtotal->formatted = numberFormatt($totalExchange, $account->currency->decimals);
            
            $account->subtotalFee = new stdClass();
            $account->subtotalFee->number = (float)number_format($totalExchangeFee,$account->currency->decimals,'.','');
            $account->subtotalFee->formatted = numberFormatt($totalExchangeFee, $account->currency->decimals);
            
            $account->exchanges = $exchanges;
            
            $accounts [] = $account;
        }
        
        $record->accounts = $accounts;
        
        $records [] = $record;
    }
    
    $out->records = $records;
    
    
    // Recorremos los usuarios
    foreach($out->records as $users){
        //REcorremos las cuentas de los usuarios
        foreach($users->accounts as $accounts){
            
            // REcorremos el arreglo de totales
            foreach($out->totals as $index => $total){
                if($total->currency->id == $accounts->currency->id){
                    $out->totals[$index]->number += $accounts->subtotal->number;
                }
            }
        }
    }
    
    // Recorremos el arreglo de totales
    foreach($out->totals as $index => $total){
        $out->totals[$index]->formatted = numberFormatt($total->number, $total->currency->decimals);
    }
    
    // Recorremos los totales para sacar el total en VES
    foreach($out->totals as $index => $total){
        
        if($total->currency->id == 1){
            $out->totalVES->number += $total->number;
        }else{
            
            // COnsultamos los pares relacionados con la moneda
            $sql = "SELECT COUNT(id) AS qty FROM pairs ".
            "       WHERE status = 1".
            "       AND ((currencyid = 1 AND refcurrencyid = ".$total->currency->id.")".
            "       OR (refcurrencyid = 1 AND currencyid = ".$total->currency->id."))";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $row = $rs->fetch_assoc();
            
            // Validamos que exista algun par
            if($row['qty'] > 0){
                
                // Consultamos los pares relacionados con la moneda
                $sql = "SELECT * FROM pairs ".
                "       WHERE status = 1".
                "       AND ((currencyid = 1 AND refcurrencyid = ".$total->currency->id.")".
                "       OR (refcurrencyid = 1 AND currencyid = ".$total->currency->id."))";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $rowData = $res->fetch_assoc();
                
                // Buscamos el ultimo precio
                $ratio = new stdClass();
                foreach($out->rates as $index2 => $rate){
                    if($rowData['id'] == $rate->id){
                        $ratio = $rate;
                    }
                }
                
                //Luego de que tenemos la moneda, necesitamos saber en que moneda esta el precio
                if($pairres['currencyid'] == $currencyid){
                    
                    $out->totalVES->number += $total->number * $ratio->lastprice->number;
                    
                    
                }else if($pairres['refcurrencyid'] == $currencyid){
                    
                    $out->totalVES->number += ($ratio->lastprice->number > 0 ? $total->number / $ratio->lastprice->number : 0);
                    
                }
                
            }else{
                // Primero, buscaremos todos los pares donde se encuentra nuestra moneda inicial(1)
                $sql = "SELECT COUNT(pairs.id) AS qty ".
                "       FROM pairs ".
                "       WHERE ".
                "       (currencyid = 1".
                "           OR refcurrencyid = 1".
                "       )";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                
                $pairres = $res->fetch_assoc();
                
                if($pairres['qty'] > 0){
                    // Ahora debemos recorrer los pares buscando por su otra moneda(2)
                    $sql = "SELECT * ".
                    "       FROM pairs ".
                    "       WHERE ".
                    "       (currencyid = 1".
                    "           OR refcurrencyid = 1".
                    "       )";
                    if (!$res=$db->query($sql))
                        badEnd("500", array("msg"=>determinateDBError($db)));
                        
                    while($pairres = $res->fetch_assoc()){
                        //Guardamos la otra moneda(2) del par
                        $secondcurrencyid = $pairres['currencyid'] == 1 ? $pairres['refcurrencyid'] : $pairres['currencyid'];
                        
                        // algun par que este con la moneda(3) que buscamos
                        $sql = "SELECT COUNT(pairs.id) AS qty ".
                        "       FROM pairs ".
                        "       WHERE ".
                        "       (currencyid = ".$secondcurrencyid.
                        "           AND refcurrencyid = ".$pairres['currencyid'].
                        "       ) OR ".
                        "       (currencyid = ".$pairres['currencyid'].
                        "           AND refcurrencyid = ".$secondcurrencyid.
                        "       )";
                        if (!$res=$db->query($sql))
                            badEnd("500", array("msg"=>determinateDBError($db)));
                        
                        $secondpairres = $res->fetch_assoc();
                        
                        if($secondpairres['qty'] > 0){
                            $sql = "SELECT * ".
                            "       FROM pairs ".
                            "       WHERE ".
                            "       (currencyid = ".$secondcurrencyid.
                            "           AND refcurrencyid = ".$pairres['currencyid'].
                            "       ) OR ".
                            "       (currencyid = ".$pairres['currencyid'].
                            "           AND refcurrencyid = ".$secondcurrencyid.
                            "       )";
                            if (!$res=$db->query($sql))
                                badEnd("500", array("msg"=>determinateDBError($db)));
            
                            $seconpairres = $res->fetch_assoc();
                            
                            // Buscamos el ultimo precio
                            $ratio = new stdClass();
                            foreach($out->rates as $index2 => $rate){
                                if($secondpairres['id'] == $rate->id){
                                    $ratio = $rate;
                                }
                            }
                            
                            // Ahora hacemos una conversion de la moneda(3) que buscamos a la otra moneda(2)
                            if($pairres['currencyid'] == $currencyid){
                                $balancetmp = $total->number * $ratio->lastprice->number;
                            }else if($pairres['refcurrencyid'] == $currencyid){
                                $balancetmp = ($ratio->lastprice->number > 0 ? $total->number / $ratio->lastprice->number : 0);
                            }
                            
                            // Buscamos el ultimo precio
                            $ratio = new stdClass();
                            foreach($out->rates as $index2 => $rate){
                                if($pairres['id'] == $rate->id){
                                    $ratio = $rate;
                                }
                            }
                            
                            //Luego de que tenemos la moneda, necesitamos saber en que moneda esta el precio
                            if($pairres['currencyid'] == 1){
                                $out->totalVES->number += $balancetmp * $ratio->lastprice->number;
                                
                                
                            }else if($pairres['refcurrencyid'] == 1){
                                $out->totalVES->number += ($ratio->lastprice->number > 0 ? $balancetmp / $ratio->lastprice->number : 0);
                                
                            }
                        }
                    }
                }
            }
        }
    }
    
    $out->totalVES->formatted = numberFormatt($out->totalVES->number, 2);
    
    // Consultamos quien es el usuario que lo emite
    $sql = "SELECT usr, name, NOW() as emited FROM cmsusers WHERE id = ".$userid;
    if (!$res=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $userdata = $res->fetch_assoc();
    
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getDefaultColumnDimension()->setWidth(18);
    $sheet->getDefaultRowDimension()->setRowHeight(22);
    
    /*Cabecera*/
    $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
    $drawing->setName('Logo');
    $drawing->setDescription('Logo');
    $drawing->setPath('../../settings/img/logo-email.png');
    $drawing->setHeight(100);
    $drawing->setCoordinates('A1');
    $drawing->setWorksheet($spreadsheet->getActiveSheet());
    $sheet->mergeCells('A1:A3');
    
    $sheet->setCellValue('B1', 'Asesoría Financiera IO, C.A. !');
    $sheet->mergeCells('B1:E1');
    
    $sheet->setCellValue('B2', 'J - 30252203-0');
    $sheet->mergeCells('B2:E2');
    
    $sheet->setCellValue('B3', 'Fee obtenido en un período de tiempo bajo convenio');
    $sheet->mergeCells('B3:E3');
    
    $sheet->setCellValue('F1','Emitido el '.date("d/m/Y",strtotime($userdata['emited']))." por: ");
    $sheet->getStyle('F1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet->getStyle('F1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $sheet->mergeCells('F1:G1');
    
    $sheet->setCellValue('F2',$userdata['name']);
    $sheet->getStyle('F2')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet->getStyle('F2')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $sheet->mergeCells('F2:G3');
    
    $sheet->setCellValue('A4', 'Fee obtenido en un período de tiempo bajo convenio');
    $sheet->mergeCells('A4:G6');
    
    $sheet->getStyle('A4')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle('A4')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    
    // DEtalles
    $sheet->setCellValue('A7','Fee: '.$fee.'% | Tags: '.implode(', ',$tags).' | Período: Del '.date('d/m/Y',strtotime($datefrom)).' al '.date('d/m/Y',strtotime($dateto)));
    $sheet->getStyle('A7')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet->getStyle('A7')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $sheet->mergeCells('A7:G7');
    
    $ratiosstr = '';
    // COlocamos las tasas
    foreach($out->rates as $index => $ratios){
        if($index == 0){
            $ratiosstr .= $ratios->pair.' '.$ratios->lastprice->formatted;
        }else{
            $ratiosstr .= " | ".$ratios->pair.' '.$ratios->lastprice->formatted;
        }
    }
    
    $sheet->setCellValue('A8','Tasas de cambio vigentes: '.$ratiosstr);
    $sheet->getStyle('A8')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet->getStyle('A8')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $sheet->mergeCells('A8:G8');
    
    $currentRow = 9;
    
    // Recorremos los usuarios
    foreach($out->records as $recordrow){
        
        $sheet->setCellValue("A$currentRow",$recordrow->fullname." ".$recordrow->usr." (Registrado el ".$recordrow->datecreated->formatted.")");
        $sheet->mergeCells("A$currentRow:G$currentRow");
        $sheet->getStyle("A$currentRow:G$currentRow")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('E1E4E8');
        $sheet->getStyle("A$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $currentRow++;
        
        $currencyTotal = '';
        foreach($recordrow->accounts as $accountrow){
            $sheet->setCellValue("A$currentRow",$accountrow->currency->name." - ".$accountrow->currency->symbol." (Creación el: ".$accountrow->datecreated->formatted.")");
            $sheet->mergeCells("A$currentRow:G$currentRow");
            $sheet->getStyle("A$currentRow:G$currentRow")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('F5F6F8');
            $sheet->getStyle("A$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            $currentRow++;
            
            $totalTmpFee = 0;
            $totalTmpAllies = 0;
            foreach($accountrow->exchanges as $index => $exchangerow){
                // SI es la primera hacemos la cabecera
                if($index == 0){
                    $sheet->setCellValue("A$currentRow","Fecha de intercambio");
                    $sheet->setCellValue("B$currentRow","Ref");
                    $sheet->setCellValue("C$currentRow","Par");
                    $sheet->setCellValue("D$currentRow","Tipo");
                    $sheet->setCellValue("E$currentRow","# Orden");
                    $sheet->setCellValue("F$currentRow","Comisión");
                    $sheet->setCellValue("G$currentRow","Monto Fee con %");
                    
                    $sheet->getStyle("A$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                    $sheet->getStyle("B$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                    $sheet->getStyle("C$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
                    $sheet->getStyle("D$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
                    $sheet->getStyle("E$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                    $sheet->getStyle("F$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
                    $sheet->getStyle("G$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
                    
                    $sheet->getStyle("A$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                    $sheet->getStyle("B$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                    $sheet->getStyle("C$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                    $sheet->getStyle("D$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                    $sheet->getStyle("E$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                    $sheet->getStyle("F$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                    $sheet->getStyle("G$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                    $currentRow++;
                }
                
                $sheet->setCellValue("A$currentRow",$exchangerow->datecreated->formatted);
                $sheet->setCellValue("B$currentRow",$exchangerow->id);
                $sheet->setCellValue("C$currentRow",$exchangerow->pair);
                $sheet->setCellValue("D$currentRow",$exchangerow->order->side);
                $sheet->setCellValue("E$currentRow",$exchangerow->order->id);
                $sheet->setCellValue("F$currentRow",$exchangerow->feeamount->formatted);
                $totalTmpFee += $exchangerow->feeamount->number;
                $sheet->setCellValue("G$currentRow",$exchangerow->feeallies->formatted);
                $totalTmpAllies += $exchangerow->feeallies->number;
                $sheet->getStyle("A$currentRow:G$currentRow")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB( $index % 2 == 0  ? 'FFFFFF' : 'F5F6F8');
                $sheet->getStyle("A$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                $sheet->getStyle("B$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                $sheet->getStyle("C$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                $sheet->getStyle("D$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                $sheet->getStyle("E$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                $sheet->getStyle("F$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                $sheet->getStyle("G$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                
                $sheet->getStyle("A$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle("B$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle("C$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
                $sheet->getStyle("D$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
                $sheet->getStyle("E$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle("F$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
                $sheet->getStyle("G$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
                    
                $currentRow++;
            }
            
            // Colocamos el subtotal
            
            $sheet->setCellValue("A$currentRow",'Subtotal '.$accountrow->currency->name);
            $sheet->setCellValue("F$currentRow",numberFormatt($totalTmpFee, $accountrow->currency->decimals));
            $sheet->setCellValue("G$currentRow",numberFormatt($totalTmpAllies, $accountrow->currency->decimals));
            $sheet->getStyle("A$currentRow:G$currentRow")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('E1E4E8');
            $sheet->getStyle("F$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            $sheet->getStyle("G$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            $sheet->getStyle("F$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
            $sheet->getStyle("G$currentRow")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
            $currentRow++;
            
            $currencyTotal .= "  |  ".$accountrow->currency->name.": ".numberFormatt($totalTmpFee, $accountrow->currency->decimals); 
        }
        
        // Total del usuario
        $sheet->setCellValue("A$currentRow","Subtotal ".$recordrow->fullname.$currencyTotal);
        $sheet->getStyle("A$currentRow:G$currentRow")->getFont()->getColor()->setARGB('FFFFFF');
        $sheet->getStyle("A$currentRow:G$currentRow")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('4287CE');
        $sheet->getStyle("A$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $sheet->mergeCells("A$currentRow:G$currentRow");
        $currentRow++;
        
        //$sheet->setCellValue("A$currentRow","Subtotal ".$recordrow->fullname.$currencyTotal);
        $sheet->getStyle("A$currentRow:G$currentRow")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FFFFFF');
        $sheet->getStyle("A$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $sheet->mergeCells("A$currentRow:G$currentRow");
        $currentRow++;
    }
    
    $totalFeeAllie = '';
    // Ultima fila
    foreach($out->totals as $index => $totally){
        $totalFeeAllie .= "  |  ".$totally->currency->name.': '.$totally->formatted;
    }
    
    $sheet->setCellValue("A$currentRow","Totales Fee ".implode(', ',$tags).$totalFeeAllie);
    $sheet->getStyle("A$currentRow:G$currentRow")->getFont()->getColor()->setARGB('FFFFFF');
    $sheet->getStyle("A$currentRow:G$currentRow")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('4287CE');
    $sheet->getStyle("A$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $sheet->mergeCells("A$currentRow:G$currentRow");
    $currentRow++;
    
    $sheet->setCellValue("A$currentRow","Totales Bolívares a ".$out->totalVES->formatted);
    $sheet->getStyle("A$currentRow:G$currentRow")->getFont()->getColor()->setARGB('FFFFFF');
    $sheet->getStyle("A$currentRow:G$currentRow")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('4287CE');
    $sheet->getStyle("A$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $sheet->mergeCells("A$currentRow:G$currentRow");
    $currentRow++;
    /*
    $sheet->setCellValue("A$currentRow","Nota: Considerando tasas de cambio indicadas por usuario en input");
    $sheet->getStyle("A$currentRow:G$currentRow")->getFont()->getColor()->setARGB('FFFFFF');
    $sheet->getStyle("A$currentRow:G$currentRow")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('4287CE');
    $sheet->getStyle("A$currentRow")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $sheet->mergeCells("A$currentRow:G$currentRow");
    $currentRow++;
    */
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    die();
    //echo json_encode($out);
    //die();
    
    function determinateOrder($order){
        $str = "";
        
        switch($order){
            case 1:
                $str = "ORDER BY name ASC";
                break;
            case -1:
                $str = "ORDER BY name DESC";
                break;
        }
        
        return $str;
    }
?>