<?php
// app/api/deposits/depositvalid

    //$mt = microtime(true);
    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    require '../../hooks/PHPMailer5/PHPMailerAutoload.php';
    date_default_timezone_set('Etc/UTC');
    $out = new stdClass();
    //Con este servicio consultaremos los depositos que estan pendientes buscandolos en la API(DINAMICO)
    
    //Obtenemos los datos de las monedas
    $sql = "SELECT * FROM currencies WHERE status = 1";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    while($currencydata = $rs->fetch_assoc()){
        //Aqui colocaremos un switch con los interface de las apis
        switch(intval($currencydata['interface'])){
            case 7:
                
                //Interface = 7 | DASH
                //Primero debemos consultar en la DB el ultimo deposito que tenemos, y si no tenemos recorremos todos los depositos de la API
                $sql = "SELECT deposits.ref AS ref, ".
                "       deposits.paymentref AS blocktxid, ".
                "       DATE_ADD(deposits.createddate, INTERVAL -10 MINUTE) AS date, ".
                "       deposits.transactionid AS transactionid ".
                "       FROM accounts, deposits, transactions ".
                "       WHERE deposits.valid IS NOT NULL ".
                "       AND deposits.rejected IS NULL ".
                "       AND transactions.id = deposits.transactionid ".
                "       AND transactions.accountid = accounts.id ".
                "       AND accounts.currencyid = ".$currencydata['id'].
                "       AND accounts.entityusrid = -1 ".
                "       ORDER BY deposits.createddate DESC ".
                "       LIMIT 1";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $row = $res->fetch_assoc();
                
                //Si la fecha es null insertamos los nuevos registros a las wallets que tenemos
                if($row['date'] == NULL || $row['date'] == ""){
                    // Consultamos las transacciones realizadas que sean 'receive'
                    $parms = array("label"=>"afxaddr", 'count'=>100);
                    $request = array('method'=>'listtransactions','parms' => $parms);
                    $rest = json_decode(queryServicesDash($DASH_URL, $request),true);
                    
                    // Validamos que el error no sea nulo para mandar el mensaje
                    if($rest['error'] != null){
                        continue 2;
                        //badEnd('500',array("msg"=>$rest['error']['message']));
                    }
                    
                    // Recorremos las trasacciones
                    foreach($rest['result'] as $deposit){
                        // Validamos que sea recibir dinero
                        if($deposit['category'] == 'receive'){
                            
                            // Generamos un bloque
                            //Consultamos que la wallet exista
                            $sql = "SELECT COUNT(id) AS qty ".
                            "       FROM wallets ".
                            "       WHERE wallet = '".$deposit['address']."'";
                            if (!$resW=$db->query($sql))
                                badEnd("500", array("msg"=>determinateDBError($db)));
                        
                            $wallet = $resW->fetch_assoc();
                            
                            // Validamos la cantidad de confirmaciones
                            // Si las confirmaciones son mayores a 5, lo validamos
                            if($deposit['confirmations'] >= 5){
                                
                                $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                $currentDate = $date->format('Y-m-d H:i:s');
                                acceptedDeposit($db, array('id'=>$deposit['txid'],
                                'address'=>$deposit['address'],
                                'amount'=>$deposit['amount'],
                                'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'txid'=>$deposit['txid']), $currencydata, $STANDAR_STYLE_EMAIL, $homeurl);
                                
                                //Si las validaciones son 0 esta en proceso, lo ingresamos
                            }elseif($deposit['confirmations'] >= 0 && $deposit['confirmations'] <= 5){
                                
                                $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                $currentDate = $date->format('Y-m-d H:i:s');
                                submitDeposit($db, array('id'=>$deposit['txid'],
                                'address'=>$deposit['address'],
                                'amount'=>$deposit['amount'],
                                'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'completed_at'=>null,
                                'txid'=>$deposit['txid']), $currencydata);
                                // Si las validaciones son menores a 0, significa que se cancelo, la cancelamos
                                
                            }elseif($deposit['confirmations'] < 0){
                                
                                $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                $currentDate = $date->format('Y-m-d H:i:s');
                                cancelDeposit($db, array('id'=>$deposit['txid'],
                                'address'=>$deposit['address'],
                                'amount'=>$deposit['amount'],
                                'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'txid'=>$deposit['txid']), $currencydata);
                            }
                        }
                    }
                    
                }else{
                    // Consultamos las transacciones que sean receive
                    $parms = array("label"=>"afxaddr", 'count'=>100);
                    $request = array('method'=>'listtransactions','parms' => $parms);
                    $rest = json_decode(queryServicesDash($DASH_URL, $request),true);
                    
                    // Validamos que el error no sea nulo para mandar el mensaje
                    if($rest['error'] != null){
                        continue 2;
                        //badEnd('500',array("msg"=>$rest['error']['message']));
                    }
                    
                    if (is_array($rest) || is_object($rest)){ // Se valida el resultado
                    // Recorremos las trasacciones
                        foreach($rest['result'] as $deposit){
                            // Validamos que sea recibir dinero
                            if($deposit['category'] == 'receive'){
                                if(strtotime($row['date']) < $deposit['time']){
                                    // Generamos un bloque
                                    //Consultamos que la wallet exista
                                    $sql = "SELECT COUNT(id) AS qty ".
                                    "       FROM wallets ".
                                    "       WHERE wallet = '".$deposit['address']."'";
                                    if (!$resW=$db->query($sql))
                                        badEnd("500", array("msg"=>determinateDBError($db)));
                            
                                    $wallet = $resW->fetch_assoc();
                                    
                                    // Validamos la cantidad de confirmaciones
                                    // Si las confirmaciones son mayores a 5, lo validamos
                                    if($deposit['confirmations'] >= 5){
                                        
                                        $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                        $currentDate = $date->format('Y-m-d H:i:s');
                                        acceptedDeposit($db, array('id'=>$deposit['txid'],
                                        'address'=>$deposit['address'],
                                        'amount'=>$deposit['amount'],
                                        'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'txid'=>$deposit['txid']), $currencydata, $STANDAR_STYLE_EMAIL, $homeurl);
                                        
                                        //Si las validaciones son 0 esta en proceso, lo ingresamos
                                        
                                    }elseif($deposit['confirmations'] >= 0 && $deposit['confirmations'] <= 5){
                                        $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                        $currentDate = $date->format('Y-m-d H:i:s');
                                        submitDeposit($db, array('id'=>$deposit['txid'],
                                        'address'=>$deposit['address'],
                                        'amount'=>$deposit['amount'],
                                        'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'completed_at'=>null,
                                        'txid'=>$deposit['txid']), $currencydata);
                                        
                                        // Si las validaciones son menores a 0, significa que se cancelo, la cancelamos
                                    }elseif($deposit['confirmations'] < 0){
                                        
                                        $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                        $currentDate = $date->format('Y-m-d H:i:s');
                                        cancelDeposit($db, array('id'=>$deposit['txid'],
                                        'address'=>$deposit['address'],
                                        'amount'=>$deposit['amount'],
                                        'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'txid'=>$deposit['txid']), $currencydata);
                                    }
                                }
                            }
                        }
                    }
                }
                break;
                
            case 6:
                // Interface = 6 | TETHER
                //Primero debemos consultar en la DB el ultimo deposito que tenemos, y si no tenemos recorremos todos los depositos de la API
                $sql = "SELECT deposits.ref AS ref, ".
                "       deposits.paymentref AS blocktxid, ".
                "       DATE_ADD(deposits.createddate, INTERVAL -10 MINUTE) AS date, ".
                "       deposits.transactionid AS transactionid ".
                "       FROM accounts, deposits, transactions ".
                "       WHERE deposits.valid IS NOT NULL ".
                "       AND deposits.rejected IS NULL ".
                "       AND transactions.id = deposits.transactionid ".
                "       AND transactions.accountid = accounts.id ".
                "       AND accounts.entityusrid = -1 ".
                "       AND accounts.currencyid = ".$currencydata['id'].
                "       ORDER BY deposits.createddate DESC ".
                "       LIMIT 1";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $row = $res->fetch_assoc();
                
                //Si la fecha es null insertamos los nuevos registros a las wallets que tenemos
                if($row['date'] == NULL || $row['date'] == ""){
                    
                    // Tenemos que consultar TODAS las WALLETS
                    $sql = "SELECT wallets.wallet AS wallet ".
                    "       FROM wallets, accounts ".
                    "       WHERE wallets.accountid = accounts.id ".
                    "       AND accounts.currencyid = ".$currencydata['id'];
                    "       AND accounts.entityusrid = -1 ".
                    "       AND wallets.expirationDate > NOW()".
                    "       AND wallets.txid IS NULL".
                    "       AND wallets.txidApprove IS NULL";
                    if (!$resW=$db->query($sql))
                        badEnd("500", array("msg"=>determinateDBError($db)));
                        
                    while($rowW = $resW->fetch_assoc()){
                        // Obtenemos las transacciones pendientes (unconfirmed)
                        $request = array('method'=>'trc20byaddress',"address" => $rowW['wallet'], 'confirmed' => false);
                        $restUnconfirmed = json_decode(queryServicesUSDT($USDT_URL, $request), true);
                        
                        // Validamos que el error no sea nulo para mandar el mensaje
                        if($restUnconfirmed['error'] != null){
                            continue;
                            //badEnd('500',array("msg"=>$restUnconfirmed['error']['message']));
                        }
                        if(count($restUnconfirmed['data']) >0){
                            foreach($restUnconfirmed['data'] as $deposit){
                                //#AÑADIR
                                if(!validateMin($currencydata['id'],1, 1, 1, $db, formattUsdtAmount($deposit['value'], $deposit['token_info']['decimals']))){
                
                                    $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                    $currentDate = $date->format('Y-m-d H:i:s');
                                    submitDeposit($db, array('id'=>$deposit['transaction_id'],
                                        'address'=>$deposit['to'],
                                        'amount'=>formattUsdtAmount($deposit['value'], $deposit['token_info']['decimals']),
                                        'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'txid'=>$deposit['transaction_id']), $currencydata);
                                    
                                }
                            }
                        }
                        
                        
                        //  Obtenemos las transacciones confirmadas (confirmed)
                        $request = array('method'=>'trc20byaddress',"address" => $rowW['wallet'], 'confirmed' => true);
                        $restConfirmed = json_decode(queryServicesUSDT($USDT_URL, $request), true);
                        
                        // Validamos que el error no sea nulo para mandar el mensaje
                        if($restConfirmed['error'] != null){
                            continue;
                            //badEnd('500',array("msg"=>$restConfirmed['error']['message']));
                        }
                        
                        if(sizeof($restConfirmed['data']) > 0){
                            foreach($restConfirmed['data'] as $deposit){
                                //#AÑADIR
                                if(!validateMin($currencydata['id'],1, 1, 1, $db, formattUsdtAmount($deposit['value'], $deposit['token_info']['decimals']))){
                
                                    // Validamos que este movimiento ya se haya aprobado
                                    //Obtenemos los datos de el deposito para efectos de ajustar montos en transactions
                                    $sql = "SELECT * ".
                                    "       FROM deposits ".
                                    "       WHERE paymentref = '".$deposit['transaction_id']."'";
                                    if (!$res=$db->query($sql))
                                        badEnd("500", array("msg"=>determinateDBError($db)));
                                      
                                    $depositdata = $res->fetch_assoc();
                                    
                                    if($depositdata['valid'] == null){
                                        // Luego de que el deposito es aceptado, pasamos el dinero a la cuenta central
                                        $request = array('method'=>'transferFrom',"address" => $rowW['wallet'], "amount"=>$deposit['value'], "feeLimit"=>10000000);
                                        //die($request);
                                        $restTransfer = json_decode(queryServicesUSDT($USDT_URL, $request), true);
                                        //$restTransfer['error'] = "error1";
                                        // Validamos que el error no sea nulo para mandar el mensaje
                                        if($restTransfer['error'] != null){
                                            continue;
                                            //badEnd('500',array("msg"=>$restConfirmed['error']['message']));
                                        }
                                    }
                                    
                                    $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                    $currentDate = $date->format('Y-m-d H:i:s');
                                    
                                    acceptedDeposit($db, array('id'=>$deposit['transaction_id'],
                                        'address'=>$deposit['to'],
                                        'amount'=>formattUsdtAmount($deposit['value'], $deposit['token_info']['decimals']),
                                        'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'txid'=>$deposit['transaction_id']), $currencydata, $STANDAR_STYLE_EMAIL, $homeurl);
                                    
                                }
                            }
                        }
                    }
                }else{
                    // Tenemos que consultar TODAS las WALLETS
                    $sql = "SELECT wallets.wallet AS wallet ".
                    "       FROM wallets, accounts ".
                    "       WHERE wallets.accountid = accounts.id ".
                    "       AND accounts.currencyid = ".$currencydata['id'];
                    "       AND wallets.expirationDate > NOW()".
                    "       AND accounts.entityusrid = -1 ".
                    "       AND wallets.txid IS NULL".
                    "       AND wallets.txidApprove IS NULL";
                    if (!$resW=$db->query($sql))
                        badEnd("500", array("msg"=>determinateDBError($db)));
                        
                    while($rowW = $resW->fetch_assoc()){
                        
                        // Obtenemos las transacciones pendientes (unconfirmed)
                        $request = array('method'=>'trc20byaddress',"address" => $rowW['wallet'], 'confirmed' => false, 'offset' => strtotime($row['date']));
                        $restUnconfirmed = json_decode(queryServicesUSDT($USDT_URL, $request), true);
                        
                        // Validamos que el error no sea nulo para mandar el mensaje
                        if($restUnconfirmed['error'] != null){
                            continue;
                            //badEnd('500',array("msg"=>$restUnconfirmed['error']['message']));
                        }
                        
                        //SE VALIDA QUE SE CUENTE UN ARREGLO SIEMPRE PARA EVITAR ERROR_LOG #NEW
                        $uncData = ($restUnconfirmed['data']!=null && $restUnconfirmed['data']!='undefined') ? $restUnconfirmed['data'] : [];
                        if(count($uncData) >0){//#MODIFICADO
                            foreach($restUnconfirmed['data'] as $deposit){
                                //#AÑADIR
                                if(!validateMin($currencydata['id'],1, 1, 1, $db, formattUsdtAmount($deposit['value'], $deposit['token_info']['decimals']))){
                                    $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                    $currentDate = $date->format('Y-m-d H:i:s');
                                    submitDeposit($db, array('id'=>$deposit['transaction_id'],
                                        'address'=>$deposit['to'],
                                        'amount'=>formattUsdtAmount($deposit['value'], $deposit['token_info']['decimals']),
                                        'created_at'=>date('Y-m-d H:i:s',strtotime($currentDate)),
                                        'completed_at'=>date('Y-m-d H:i:s',strtotime($currentDate)),
                                        'txid'=>$deposit['transaction_id']), $currencydata);
                                }
                            }
                        }
                        
                        
                        //  Obtenemos las transacciones confirmadas (confirmed)
                        $request = array('method'=>'trc20byaddress',"address" => $rowW['wallet'], 'confirmed' => true, 'offset' => strtotime($row['date']));
                        $restConfirmed = json_decode(queryServicesUSDT($USDT_URL, $request), true);
                        
                        // Validamos que el error no sea nulo para mandar el mensaje
                        if($restConfirmed['error'] != null){
                            continue;
                            //badEnd('500',array("msg"=>$restConfirmed['error']['message']));
                        }
                        
                        //SE VALIDA QUE SE CUENTE UN ARREGLO SIEMPRE PARA EVITAR ERROR_LOG #NEW
                        $confData = ($restConfirmed['data']!=null && $restConfirmed['data']!='undefined') ? $restConfirmed['data'] : [];
                        if(sizeof($confData) > 0){//#MODIFICADO
                            foreach($restConfirmed['data'] as $deposit){
                                //#AÑADIR
                                if(!validateMin($currencydata['id'],1, 1, 1, $db, formattUsdtAmount($deposit['value'], $deposit['token_info']['decimals']))){
                
                                    // Validamos que este movimiento ya se haya aprobado
                                    //Obtenemos los datos de el deposito para efectos de ajustar montos en transactions
                                    $sql = "SELECT * ".
                                    "       FROM deposits ".
                                    "       WHERE paymentref = '".$deposit['transaction_id']."'";
                                    if (!$res=$db->query($sql))
                                        badEnd("500", array("msg"=>determinateDBError($db)));
                                      
                                    $depositdata = $res->fetch_assoc();
                                    
                                    if($depositdata['valid'] == null){
                                        // Luego de que el deposito es aceptado, pasamos el dinero a la cuenta central
                                        $request = array('method'=>'transferFrom',"address" => $rowW['wallet'], "amount"=>$deposit['value'], "feeLimit"=>10000000);
                                        $restTransfer = json_decode(queryServicesUSDT($USDT_URL, $request), true);
                                        // Validamos que el error no sea nulo para mandar el mensaje
                                        if($restTransfer['error'] != null){
                                            continue;
                                            //badEnd('500',array("msg"=>$restConfirmed['error']['message']));
                                        }
                                    }
                                    
                                    $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                    $currentDate = $date->format('Y-m-d H:i:s');
    
                                    acceptedDeposit($db, array('id'=>$deposit['transaction_id'],
                                        'address'=>$deposit['to'],
                                        'amount'=>formattUsdtAmount($deposit['value'], $deposit['token_info']['decimals']),
                                        'created_at'=>date('Y-m-d H:i:s',strtotime($currentDate)),
                                        'completed_at'=>date('Y-m-d H:i:s',strtotime($currentDate)),
                                        'txid'=>$deposit['transaction_id']), $currencydata, $STANDAR_STYLE_EMAIL, $homeurl);
                                    
                                }
                            }
                        }
                    }
                }
                break;
            case 5:
                //Interface = 5 | PTR
                //Primero debemos consultar en la DB el ultimo deposito que tenemos, y si no tenemos recorremos todos los depositos de la API
                $sql = "SELECT deposits.ref AS ref, ".
                "       deposits.paymentref AS blocktxid, ".
                "       deposits.createddate AS date, ".
                "       deposits.transactionid AS transactionid ".
                "       FROM accounts, deposits, transactions ".
                "       WHERE deposits.valid IS NULL ".
                "       AND deposits.rejected IS NULL ".
                "       AND transactions.id = deposits.transactionid ".
                "       AND transactions.accountid = accounts.id ".
                "       AND accounts.currencyid = ".$currencydata['id'].
                "       ORDER BY deposits.createddate DESC ".
                "       LIMIT 1";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $row = $res->fetch_assoc();
                
                //Si la fecha es null insertamos los nuevos registros a las wallets que tenemos
                
                if($row['date'] == NULL || $row['date'] == ""){
                    //Consultamos los depositos en proceso para ingresarlos en espera
                    $submitted = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'submitted']);
                    
                    if($submitted['httpres'] == 200){
                        // Consultamos los depositos en proceso para ingresarlos y que el usuario pueda verlo
                        foreach($submitted as $deposit){
                            if(isset($deposit['id'])){
                                submitDeposit($db, $deposit, $currencydata);
                            }
                        }
                    }
                    
                    //Consultamos los depositos completados para ingresarlos en el saldo
                    $accepted = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'accepted']);
                    
                    if($accepted['httpres'] == 200){
                        // Analizamos el deposito para ingresarlo y aceptarlo o solo aceptarlo(VALID)
                        foreach($accepted as $deposit){
                            if(isset($deposit['id'])){
                                acceptedDeposit($db, $deposit, $currencydata, $STANDAR_STYLE_EMAIL, $homeurl);
                            }
                        }
                    }
                    
                    //Consultamos los depositos rechazados
                    $rejected = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'rejected']);
                    
                    if($rejected['httpres'] == 200){
                        // Analizamos el deposito para ingresarlo o actualizarlo(REJECTED)
                        foreach($rejected as $deposit){
                            if(isset($deposit['id'])){
                                cancelDeposit($db, $deposit, $currencydata);
                            }
                        }
                    }
                    
                    //Consultamos los depositos cancelados
                    $canceled = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'canceled']);
                    
                    if($rejected['httpres'] == 200){
                        // Analizamos el deposito para ingresarlo o actualizarlo(REJECTED)
                        foreach($canceled as $deposit){
                            if(isset($deposit['id'])){
                                cancelDeposit($db, $deposit, $currencydata);
                            }
                        }
                    }
                }else{
                    //Si no es nulo, consultamos el ultimo deposito de esa moneda para buscar por esa fecha
                    
                    //Consultamos los depositos en proceso para ingresarlos en espera
                    $submitted = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'submitted']);
                    
                    if($submitted['httpres'] == 200){
                        // Consultamos los depositos en proceso para ingresarlos y que el usuario pueda verlo
                        foreach($submitted as $deposit){
                            if(strtotime($row['date']) < strtotime($deposit['created_at']) && isset($deposit['id'])){
                                submitDeposit($db, $deposit, $currencydata);
                            }
                        }
                    }
                    
                    //Consultamos los depositos completados para ingresarlos en el saldo
                    $accepted = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'accepted']);
                    
                    if($accepted['httpres'] == 200){
                        // Analizamos el deposito para ingresarlo y aceptarlo o solo aceptarlo(VALID)
                        foreach($accepted as $deposit){
                            if(isset($deposit['id'])){
                                acceptedDeposit($db, $deposit, $currencydata, $STANDAR_STYLE_EMAIL, $homeurl);
                            }
                        }
                    }
                    
                    //Consultamos los depositos rechazados
                    $rejected = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'rejected']);
                    
                    if($rejected['httpres'] == 200){
                        // Analizamos el deposito para ingresarlo o actualizarlo(REJECTED)
                        foreach($rejected as $deposit){
                            if(isset($deposit['id'])){
                                cancelDeposit($db, $deposit, $currencydata);
                            }
                        }
                    }
                    
                    //Consultamos los depositos cancelados
                    $canceled = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'canceled']);
                    
                    if($rejected['httpres'] == 200){
                        // Analizamos el deposito para ingresarlo o actualizarlo(REJECTED)
                        foreach($canceled as $deposit){
                            if(isset($deposit['id'])){
                                cancelDeposit($db, $deposit, $currencydata);
                            }
                        }
                    }
                    
                }
                break;
            case 4:
                //Interface = 4 | BTC
                //Primero debemos consultar en la DB el ultimo deposito que tenemos, y si no tenemos recorremos todos los depositos de la API
                $sql = "SELECT deposits.ref AS ref, ".
                "       deposits.paymentref AS blocktxid, ".
                "       DATE_ADD(deposits.createddate, INTERVAL -10 MINUTE) AS date, ".
                "       deposits.transactionid AS transactionid ".
                "       FROM accounts, deposits, transactions ".
                "       WHERE deposits.valid IS NOT NULL ".
                "       AND deposits.rejected IS NULL ".
                "       AND transactions.id = deposits.transactionid ".
                "       AND transactions.accountid = accounts.id ".
                "       AND accounts.currencyid = ".$currencydata['id'].
                "       AND accounts.entityusrid = -1 ".
                "       ORDER BY deposits.createddate DESC ".
                "       LIMIT 1";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $row = $res->fetch_assoc();
                
                //Si la fecha es null insertamos los nuevos registros a las wallets que tenemos
                if($row['date'] == NULL || $row['date'] == ""){
                    // Consultamos las transacciones realizadas que sean 'receive'
                    $parms = array('label'=>'afxaddr','count'=>100);
                    $request = array('method'=>'listtransactions','parms' => $parms);
                    $rest = json_decode(queryServicesBTC($BTC_URL, $request), true);
                    
                    // Validamos que el error no sea nulo para mandar el mensaje
                    if($rest['error'] != null){
                        continue 2;
                        //badEnd('500',array("msg"=>$rest['error']['message']));
                    }
                    
                    // Recorremos las trasacciones
                    foreach($rest['result'] as $deposit){
                        // Validamos que sea recibir dinero
                        if($deposit['category'] == 'receive'){
                            
                            // Generamos un bloque
                            //Consultamos que la wallet exista
                            $sql = "SELECT COUNT(id) AS qty ".
                            "       FROM wallets ".
                            "       WHERE wallet = '".$deposit['address']."'";
                            if (!$resW=$db->query($sql))
                                badEnd("500", array("msg"=>determinateDBError($db)));
                        
                            $wallet = $resW->fetch_assoc();
                            
                            // Validamos la cantidad de confirmaciones
                            // Si las confirmaciones son mayores a 3, lo validamos
                            if($deposit['confirmations'] >= 3){
                                $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                $currentDate = $date->format('Y-m-d H:i:s');
                                acceptedDeposit($db, array('id'=>$deposit['txid'],
                                'address'=>$deposit['address'],
                                'amount'=>$deposit['amount'],
                                'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'txid'=>$deposit['txid']), $currencydata, $STANDAR_STYLE_EMAIL, $homeurl);
                                //Si las validaciones son 0 esta en proceso, lo ingresamos
                            }elseif($deposit['confirmations'] >= 0 && $deposit['confirmations'] <= 3){
                                $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                $currentDate = $date->format('Y-m-d H:i:s');
                                submitDeposit($db, array('id'=>$deposit['txid'],
                                'address'=>$deposit['address'],
                                'amount'=>$deposit['amount'],
                                'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'completed_at'=>null,
                                'txid'=>$deposit['txid']), $currencydata);
                                // Si las validaciones son menores a 0, significa que se cancelo, la cancelamos
                            }elseif($deposit['confirmations'] < 0){
                                $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                $currentDate = $date->format('Y-m-d H:i:s');
                                cancelDeposit($db, array('id'=>$deposit['txid'],
                                'address'=>$deposit['address'],
                                'amount'=>$deposit['amount'],
                                'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                'txid'=>$deposit['txid']), $currencydata);
                            }
                        }
                    }
                    
                }else{
                    // Consultamos las transacciones que sean receive
                    $parms = array('label'=>'afxaddr','count'=>100);
                    $request = array('method'=>'listtransactions','parms' => $parms);
                    $rest = json_decode(queryServicesBTC($BTC_URL, $request), true);
                    
                    // Validamos que el error no sea nulo para mandar el mensaje
                    if($rest['error'] != null){
                        continue 2;
                        //badEnd('500',array("msg"=>$rest['error']['message']));
                    }
                    
                    if (is_array($rest) || is_object($rest)){ // Se valida el resultado
                        // Recorremos las trasacciones
                        foreach($rest['result'] as $deposit){
                            // Validamos que sea recibir dinero
                            if($deposit['category'] == 'receive'){
                                if(strtotime($row['date']) < $deposit['time']){
                                    // Generamos un bloque
                                    //Consultamos que la wallet exista
                                    $sql = "SELECT COUNT(id) AS qty ".
                                    "       FROM wallets ".
                                    "       WHERE wallet = '".$deposit['address']."'";
                                    if (!$resW=$db->query($sql))
                                        badEnd("500", array("msg"=>determinateDBError($db)));
                            
                                    $wallet = $resW->fetch_assoc();
                                    
                                    // Validamos la cantidad de confirmaciones
                                    // Si las confirmaciones son mayores a 3, lo validamos
                                    if($deposit['confirmations'] >= 3){
                                        $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                        $currentDate = $date->format('Y-m-d H:i:s');
                                        acceptedDeposit($db, array('id'=>$deposit['txid'],
                                        'address'=>$deposit['address'],
                                        'amount'=>$deposit['amount'],
                                        'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'txid'=>$deposit['txid']), $currencydata, $STANDAR_STYLE_EMAIL, $homeurl);
                                        //Si las validaciones son 0 esta en proceso, lo ingresamos
                                    }elseif($deposit['confirmations'] >= 0 && $deposit['confirmations'] <= 3){
                                        $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                        $currentDate = $date->format('Y-m-d H:i:s');
                                        submitDeposit($db, array('id'=>$deposit['txid'],
                                        'address'=>$deposit['address'],
                                        'amount'=>$deposit['amount'],
                                        'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'completed_at'=>null,
                                        'txid'=>$deposit['txid']), $currencydata);
                                        // Si las validaciones son menores a 0, significa que se cancelo, la cancelamos
                                    }elseif($deposit['confirmations'] < 0){
                                        $date = new DateTime('now', new DateTimeZone('America/Caracas'));
                                        $currentDate = $date->format('Y-m-d H:i:s');
                                        cancelDeposit($db, array('id'=>$deposit['txid'],
                                        'address'=>$deposit['address'],
                                        'amount'=>$deposit['amount'],
                                        'created_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'completed_at'=>date('Y-m-d H:i:s', strtotime($currentDate)),
                                        'txid'=>$deposit['txid']), $currencydata);
                                    }
                                }
                            }
                        }
                    }
                }
                break;
        }
    }
    
    $db->close();
    //$out->tiempo = microtime(true) - $mt;
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    /*  Funcion para ingresar o cancelar depositos cancelados, recibe:
    *   $db = conexion de la DB
    *   $deposit = deposito a procesar
    *   [{
    *       id: 12,
    *       address: 'sakgdsjahdgsahjdas',
    *       amount: 0.5,
    *       created_at: 2020-09-16 10:49:59,
    *       complete_at: 2020-09-16 10:49:59,
    *       txid: 'kgjhg4j3h4g32jh4v32hjjv4hj32'
    *   }]
    *   $currencydata = datos de la moneda
    */
    function submitDeposit($db, $deposit, $currencydata){
        //Validamos que el deposito no este ingresado y que la wallet exista
        $sql = "SELECT COUNT(ref) AS qty ".
        "       FROM deposits ".
        "       WHERE ref = '".$deposit['id']."' OR paymentref='".$deposit['txid']."'";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $depositdata = $res->fetch_assoc();
        
        //Consultamos que la wallet exista
        $sql = "SELECT COUNT(id) AS qty ".
        "       FROM wallets ".
        "       WHERE wallet = '".$deposit['address']."'";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
                    
        $wallet = $res->fetch_assoc();
                        
        //Si es verdadero, consultamos los datos del usuario(cuenta, nivel, fee)
        if(intval($wallet['qty']) == 1 && intval($depositdata['qty']) == 0){
            //Consultamos los datos de la wallet para obtener el id de la account
            $accountdata = getAccountData($db, $deposit['address']);
                            
            //Consultamos los datos del usuario para determinar su fee
            $userdata = getUserdata($db, $accountdata['userid'], $deposit['address']);
            
            $level = 1;
        
            if($userdata["level"] == 0){
                $level = 1;
            }else{
                $level = $userdata["level"];
            }
            
            //Consultamos la cuenta que tiene la moneda del retiro de AFX
            $afxdata = getAccountAFX($db, $currencydata['id']);
            
            //Obtenemos el fee de cada operacion por la moneda de la cuenta
            $fee = getFee($db, $level, $currencydata['id'], $userdata["type"]);
            
            //Calculamos el monto real quitandole la comision
            if($fee["pctfeedeposits"] != null || $fee["nmlfeedeposits"] != null ){
                if($fee["pctfeedeposits"] == null){
                    
                    $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userportfolioprefs ".
                    "       WHERE currencyid = ".$currencydata['id'].
                    "       AND userid = ".$accountdata['userid'].
                    "       AND preffeesfield = 'nmlfeedeposits'";
                    if (!$res=$db->query($sql))
                        badEnd("500", array("msg"=>determinateDBError($db)));
                    
                    $userExg = $res->fetch_assoc();
                    
                    $realfee = number_format($deposit['amount'] - ($deposit['amount'] - ($fee["nmlfeedeposits"] - ($fee["nmlfeedeposits"]/100 * $userExg['exonerated']) )), $currencydata['decimals'], '.', '');
                    $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                }else{
                    
                    $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userportfolioprefs ".
                    "       WHERE currencyid = ".$currencydata['id'].
                    "       AND userid = ".$accountdata['userid'].
                    "       AND preffeesfield = 'pctfeedeposits'";
                    if (!$res=$db->query($sql))
                        badEnd("500", array("msg"=>determinateDBError($db)));
                    
                    $userExg = $res->fetch_assoc();
                    
                    $realfee = number_format((($fee["pctfeedeposits"] - ($fee["pctfeedeposits"]/100 * $userExg['exonerated']) )*$deposit['amount'])/100, $currencydata['decimals'], '.', '');
                    $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                }
            }else{
                $realfee = 0;
                $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
            }
            
            $db->autocommit(FALSE);
            
            //insertamos la trasaccion del deposito
            $sql = "INSERT INTO transactions".
            "       (".
            "       datecreated, ".
            "       dsc, ".
            "       amount, ".
            "       amountghost, ".
            "       accountid, ".
            "       orderid, ".
            "       txid, ".
            "       accountorigin, ".
            "       accountdestination".
            "       )".
            "       VALUES(".
            "       NOW(), ".
            "       'Depósito', ".
            "       0, ".
            "       ".number_format($deposit['amount'],$currencydata['decimals'],'.','').", ".
            "       ".$accountdata['accountid'].", ".
            "       NULL, ".
            "       NULL, ".
            "       NULL, ".
            "       ".$accountdata['accountid'].
            "       )";
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
        
            //Guardamos el id de la transaccion de deposito
            $txid = (int)$db->insert_id;
        
            //Le colocamos a la primera transferencia el txid
            $sql = "UPDATE transactions SET".
            "       txid = ".$txid.
            "       WHERE id = ".$txid;
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
                                
            //insertamos la comision del deposito
            $sql = "INSERT INTO transactions".
            "       (".
            "       datecreated, ".
            "       dsc, ".
            "       amount, ".
            "       amountghost, ".
            "       accountid, ".
            "       orderid, ".
            "       txid, ".
            "       accountorigin, ".
            "       accountdestination".
            "       )".
            "       VALUES(".
            "       NOW(), ".
            "       'Comisión de Depósito', ".
            "       0, ".
            "       -".$realfee.", ".
            "       ".$accountdata['accountid'].", ".
            "       NULL, ".
            "       ".$txid.", ".
            "       NULL, ".
            "       ".$afxdata["id"].
            "       )";
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
                
            //insertamos la comision del deposito
            $sql = "INSERT INTO transactions".
            "       (".
            "       datecreated, ".
            "       dsc, ".
            "       amount, ".
            "       amountghost, ".
            "       accountid, ".
            "       orderid, ".
            "       txid, ".
            "       accountorigin, ".
            "       accountdestination".
            "       )".
            "       VALUES(".
            "       NOW(), ".
            "       'Comisión de Depósito', ".
            "       0, ".
            "       ".$realfee.", ".
            "       ".$afxdata["id"].", ".
            "       NULL, ".
            "       ".$txid.", ".
            "       NULL, ".
            "       ".$afxdata["id"].
            "       )";
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
            $tmpdate = explode("T",$deposit['created_at']);
            //insertamos la comision del deposito
            $sql = "INSERT INTO deposits".
            "       (".
            "       paymentref, ".
            "       paymentaccountid, ".
            "       filetype, ".
            "       paymentdate, ".
            "       createddate, ".
            "       fee, ".
            "       transactionid, ".
            "       valid, ".
            "       amount, ".
            "       ref ".
            "       )".
            "       VALUES(".
            "       '".$deposit['txid']."', ".
            "       ".$accountdata['accountid'].", ".
            "       NULL, ".
            "       '".date("Y-m-d", strtotime($deposit['created_at']))."', ".
            "       '".date("Y-m-d H:i:s", strtotime($deposit['created_at']))."', ".
            "       ".$realfee.", ".
            "       ".$txid.", ".
            "       NULL, ".
            "       ".number_format($deposit['amount'],$currencydata['decimals'],'.','').", ".
            "       '".$deposit['address']."' ".
            "       )";
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
        
            $db->commit();
            $db->autocommit(TRUE);
            
            createAlertWallet($db, $deposit['address'], $txid);
        }
    }
    
    /*  Funcion para ingresar o cancelar depositos cancelados, recibe:
    *   $db = conexion de la DB
    *   $deposit = deposito a procesar
    *   [{
    *       id: 12,
    *       address: 'sakgdsjahdgsahjdas',
    *       amount: 0.5,
    *       created_at: 2020-09-16 10:49:59,
    *       complete_at: 2020-09-16 10:49:59,
    *       txid: 'kgjhg4j3h4g32jh4v32hjjv4hj32'
    *   }]
    *   $currencydata = datos de la moneda
    */
    function acceptedDeposit($db, $deposit, $currencydata, $STANDAR_STYLE_EMAIL, $homeurl){
        
        //Validamos que el deposito no este ingresado y que la wallet exista
        /* SE VALIDA LA REF (guardada con address) CONTRA EL ID DEL MOVIMIENTO NO DA MATCH
        $sql = "SELECT COUNT(ref) AS qty ".
        "       FROM deposits ".
        "       WHERE ref = '".$deposit['id']."';*/
        
        //Se agregó validación adicional que valida que no exista ese "txid" en la columna paymentref
        $sql = "SELECT COUNT(ref) AS qty ".
        "       FROM deposits ".
        "       WHERE ref = '".$deposit['id']."' OR paymentref='".$deposit['txid']."'";
        
        if (!$res=$db->query($sql))
            badEnd("500", array("msg1"=>determinateDBError($db)));
                    
        $depositdata = $res->fetch_assoc();
        
        //Consultamos que la wallet exista
        $sql = "SELECT COUNT(id) AS qty ".
        "       FROM wallets ".
        "       WHERE wallet = '".$deposit['address']."'";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg2"=>determinateDBError($db)));
        
        $wallet = $res->fetch_assoc();
        
        //Si es verdadero, consultamos los datos del usuario(cuenta, nivel, fee)
        if(intval($wallet['qty']) == 1 && intval($depositdata['qty']) == 0){
            
            //Consultamos los datos de la wallet para obtener el id de la account
            $accountdata = getAccountData($db, $deposit['address']);
            if($accountdata!==""){
                //Consultamos los datos del usuario para determinar su fee
                $userdata = getUserdata($db, $accountdata['userid'],  $deposit['address']);
                                
                $level = 1;
        
                if($userdata["level"] == 0){
                    $level = 1;
                }else{
                    $level = $userdata["level"];
                }
                
                //Consultamos la cuenta que tiene la moneda del retiro de AFX
                $afxdata = getAccountAFX($db, $currencydata['id']);
                                    
                //Obtenemos el fee de cada operacion por la moneda de la cuenta
                $fee = getFee($db, $level, $currencydata['id'], $userdata["type"]);
            
                //Calculamos el monto real quitandole la comision
                if($fee["pctfeedeposits"] != null || $fee["nmlfeedeposits"] != null ){
                    if($fee["pctfeedeposits"] == null){
                        
                        $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userportfolioprefs ".
                        "       WHERE currencyid = ".$currencydata['id'].
                        "       AND userid = ".$accountdata['userid'].
                        "       AND preffeesfield = 'nmlfeedeposits'";
                        if (!$res=$db->query($sql))
                            badEnd("500", array("msg"=>determinateDBError($db)));
                        
                        $userExg = $res->fetch_assoc();
                        
                        $realfee = number_format($deposit['amount'] - ($deposit['amount'] - ($fee["nmlfeedeposits"] - ($fee["nmlfeedeposits"]/100 * $userExg['exonerated']) )), $currencydata['decimals'], '.', '');
                        $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                    }else{
                        
                        $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userportfolioprefs ".
                        "       WHERE currencyid = ".$currencydata['id'].
                        "       AND userid = ".$accountdata['userid'].
                        "       AND preffeesfield = 'pctfeedeposits'";
                        if (!$res=$db->query($sql))
                            badEnd("500", array("msg"=>determinateDBError($db)));
                        
                        $userExg = $res->fetch_assoc();
                        
                        $realfee = number_format((($fee["pctfeedeposits"] - ($fee["pctfeedeposits"]/100 * $userExg['exonerated']) )*$deposit['amount'])/100, $currencydata['decimals'], '.', '');
                        $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                    }
                }else{
                    $realfee = 0;
                    $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                }
            
                $db->autocommit(FALSE);
                //insertamos la trasaccion del deposito
                $sql = "INSERT INTO transactions".
                "       (".
                "       datecreated, ".
                "       dsc, ".
                "       amountghost, ".
                "       amount, ".
                "       accountid, ".
                "       orderid, ".
                "       txid, ".
                "       accountorigin, ".
                "       accountdestination".
                "       )".
                "       VALUES(".
                "       NOW(), ".
                "       'Depósito', ".
                "       0, ".
                "       ".number_format($deposit['amount'],$currencydata['decimals'],'.','').", ".
                "       ".$accountdata['accountid'].", ".
                "       NULL, ".
                "       NULL, ".
                "       NULL, ".
                "       ".$accountdata['accountid'].
                "       )";
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
        
                //Guardamos el id de la transaccion de deposito
                $txid = (int)$db->insert_id;
        
                //Le colocamos a la primera transferencia el txid
                $sql = "UPDATE transactions SET".
                "       txid = ".$txid.
                "       WHERE id = ".$txid;
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
            
                //insertamos la comision del deposito
                $sql = "INSERT INTO transactions".
                "       (".
                "       datecreated, ".
                "       dsc, ".
                "       amountghost, ".
                "       amount, ".
                "       accountid, ".
                "       orderid, ".
                "       txid, ".
                "       accountorigin, ".
                "       accountdestination".
                "       )".
                "       VALUES(".
                "       NOW(), ".
                "       'Comisión de Depósito', ".
                "       0, ".
                "       -".$realfee.", ".
                "       ".$accountdata['accountid'].", ".
                "       NULL, ".
                "       ".$txid.", ".
                "       NULL, ".
                "       ".$afxdata["id"].
                "       )";
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
                
                //insertamos la comision del deposito
                $sql = "INSERT INTO transactions".
                "       (".
                "       datecreated, ".
                "       dsc, ".
                "       amountghost, ".
                "       amount, ".
                "       accountid, ".
                "       orderid, ".
                "       txid, ".
                "       accountorigin, ".
                "       accountdestination".
                "       )".
                "       VALUES(".
                "       NOW(), ".
                "       'Comisión de Depósito', ".
                "       0, ".
                "       ".$realfee.", ".
                "       ".$afxdata["id"].", ".
                "       NULL, ".
                "       ".$txid.", ".
                "       NULL, ".
                "       ".$afxdata["id"].
                "       )";
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
                
                $tmpdate = explode("T",$deposit['created_at']);         
                //insertamos la comision del deposito
                $sql = "INSERT INTO deposits".
                "       (".
                "       paymentref, ".
                "       paymentaccountid, ".
                "       filetype, ".
                "       paymentdate, ".
                "       createddate, ".
                "       fee, ".
                "       transactionid, ".
                "       valid, ".
                "       amount, ".
                "       ref ".
                "       )".
                "       VALUES(".
                "       '".$deposit['txid']."', ".
                "       ".$accountdata['accountid'].", ".
                "       NULL, ".
                "       '".date("Y-m-d", strtotime($deposit['created_at']))."', ".
                "       '".date("Y-m-d H:i:s", strtotime($deposit['created_at']))."', ".
                "       ".$realfee.", ".
                "       ".$txid.", ".
                "       '".date("Y-m-d H:i:s", strtotime($deposit['completed_at']))."', ".
                "       ".number_format($deposit['amount'],$currencydata['decimals'],'.','').", ".
                "       '".$deposit['address']."' ".
                "       )";
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
        
                $db->commit();
                $db->autocommit(TRUE);
                
                createAlertWallet($db, $deposit['address'], $txid);
                
                // Consultamos los datos del mensaje
                $sql = "SELECT * FROM cmsmessages WHERE id = 5";
                if (!$rs=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $data = $rs->fetch_assoc();
                
                // Validamos que el usuario tenga las notificaciones activas
                $sql = "SELECT * FROM usermessages ".
                "       WHERE categoryid = ".$data['category'].
                "       AND usrid = ".$userdata['id'];
                if (!$rs=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $userPref = $rs->fetch_assoc();
                
                if($userPref['email'] == null || $userPref['email'] == 1){
                    $subject = $userdata['lang'] == 'esp' ? $data['name_esp'] : $data['name_eng'];
                    $subject = '=?UTF-8?B?'.base64_encode($subject).'?=';
                    $body = "<div class='emailBox'>".
                        "           <img class='afxLogo' src='".$homeurl."./settings/img/logo-email.png'/>".
                        "           <div class='mainBox'>".
                        "               <h1 class='titleEmail'>".
                        "                   ".($userdata['lang'] == "esp" ? "Hola" : "Hello")." ".$userdata['name'].
                        "               </h1>".
                        "               <p class='msgEmail'>".
                        "                   ".($userdata['lang'] == 'esp' ? $data['dsc_esp'] : $data['dsc_eng']).
                        "               </p>".
                        "           </div>".
                        "           <p class='copy'>".$footerEmails."</p>".
                        "       </div>";
                        
                    $body = templateEmail($body, $STANDAR_STYLE_EMAIL);
                                    
                    enviarCorreo2($fromeMail, strtolower($userdata['email']), $subject, $body);
                }
                // Se valida la preferenca del mensaje y se envía según el idioma
                if($userPref['sms'] == null || $userPref['sms'] == 1){
                    $msg = ($userdata['lang'] == 'esp' ? $data['sms_esp'] : $data['sms_eng']);
                    enviarSMS($userdata['phone'], $msg);
                }
                if($userPref['system'] == null || $userPref['system'] == 1){
                
                    $email = '';
                    $sms = '';
                    
                    // Si se envia por correo obtenemos el correo del usuario
                    if($userPref['email'] == null || $userPref['email'] == 1){
                        $sql = "SELECT usr ".
                        "       FROM users ".
                        "       WHERE id = ".$userdata['id'];
                        if (!$resPref=$db->query($sql))
                            badEnd("500", array("msg"=>determinateDBError($db)));
                                
                        $rowPrefCom = $resPref->fetch_assoc();
                        
                        $email = $rowPrefCom['usr'];
                    }
                    
                    // SI se envia por sms, obtenemos el numero del usuario
                    if($userPref['sms'] == null || $userPref['sms'] == 1){
                        $sql = "SELECT IF(users.type = 1, userpersons.phone, usercompanies.phone) AS phone ".
                        "       FROM users ".
                        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
                        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
                        "       WHERE users.id = ".$userdata['id'];
                        if (!$resPref=$db->query($sql))
                            badEnd("500", array("msg"=>determinateDBError($db)));
                            
                        $rowPrefCom = $resPref->fetch_assoc();
                        
                        $sms = $rowPrefCom['phone'];
                        
                    }
                                    
                    // Enviamos el mensaje por la mensajeria interna
                    $msg = ( $userdata['lang'] == 'esp' ? $data['dsc_esp'] : $data['dsc_eng']);
                    saveInternalMsg($db, $msg, $userdata['id'], $email, $sms);
                    
                }
            }  
        }else if(intval($wallet['qty']) == 1 && intval($depositdata['qty']) == 1){
            //Obtenemos los datos de el deposito para efectos de ajustar montos en transactions
            $sql = "SELECT * ".
            "       FROM deposits ".
            "       WHERE ref = '".$deposit['id']."' OR paymentref='".$deposit['txid']."'";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg3"=>determinateDBError($db)));
              
            $depositdata = $res->fetch_assoc();
            
            if($depositdata['valid'] == null){
                //Actualizamos que esta validada en la tabla
                $sql = "UPDATE deposits SET".
                "       valid = '".date("Y-m-d H:i:s", strtotime($deposit['completed_at']))."', ".
                "       paymentref = '".$deposit['txid']."' ".
                "       WHERE ref = '".$deposit['id']."' OR paymentref='".$deposit['txid']."'";
                if (!$db->query($sql))
                    badEnd("500", array("msg4"=>determinateDBError($db)));
                            
                //Actualizamos la transaccion
                $sql = "UPDATE transactions SET".
                "       amount = amount + amountghost, ".
                "       amountghost = 0 ".
                "       WHERE txid = ".$depositdata['transactionid'];
                if (!$db->query($sql))
                    badEnd("500", array("msg5"=>determinateDBError($db)));
                    
                    
                createAlertWallet($db, $deposit['address'], $depositdata['transactionid']);
                    
                //Consultamos los datos de la wallet para obtener el id de la account
                $accountdata = getAccountData($db, $deposit['address']);
                                
                //Consultamos los datos del usuario para determinar su fee
                $userdata = getUserdata($db, $accountdata['userid'],  $deposit['address']);
                    
                // Consultamos los datos del mensaje
                $sql = "SELECT * FROM cmsmessages WHERE id = 5";
                if (!$rs=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $data = $rs->fetch_assoc();
                
                // Validamos que el usuario tenga las notificaciones activas
                $sql = "SELECT * FROM usermessages ".
                "       WHERE categoryid = ".$data['category'].
                "       AND usrid = ".$userdata['id'];
                if (!$rs=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $userPref = $rs->fetch_assoc();
                
                if($userPref['email'] == null || $userPref['email'] == 1){
                    $subject = $userdata['lang'] == 'esp' ? $data['name_esp'] : $data['name_eng'];
                    $subject = '=?UTF-8?B?'.base64_encode($subject).'?=';
                    $body = "<div class='emailBox'>".
                    "           <img class='afxLogo' src='".$homeurl."./settings/img/logo-email.png'/>".
                    "           <div class='mainBox'>".
                    "               <h1 class='titleEmail'>".
                    "                   ".($userdata['lang'] == "esp" ? "Hola" : "Hello")." ".$userdata['name'].
                    "               </h1>".
                    "               <p class='msgEmail'>".
                    "                   ".($userdata['lang'] == 'esp' ? $data['dsc_esp'] : $data['dsc_eng']).
                    "               </p>".
                    "           </div>".
                    "           <p class='copy'>".$footerEmails."</p>".
                    "       </div>";
                    
                    $body = templateEmail($body, $STANDAR_STYLE_EMAIL);
                    
                    enviarCorreo2($fromeMail, strtolower($userdata['email']), $subject, $body);
                }
                // Se valida la preferenca del mensaje y se envía según el idioma
                if($userPref['sms'] == null || $userPref['sms'] == 1){
                    $msg = ($userdata['lang'] == 'esp' ? $data['sms_esp'] : $data['sms_eng']);
                    enviarSMS($userdata['phone'], $msg);
                }
                
                if($userPref['system'] == null || $userPref['system'] == 1){
            
                    $email = '';
                    $sms = '';
                    
                    // Si se envia por correo obtenemos el correo del usuario
                    if($userPref['email'] == null || $userPref['email'] == 1){
                        $sql = "SELECT usr ".
                        "       FROM users ".
                        "       WHERE id = ".$userdata['id'];
                        if (!$resPref=$db->query($sql))
                            badEnd("500", array("msg"=>determinateDBError($db)));
                                
                        $rowPrefCom = $resPref->fetch_assoc();
                        
                        $email = $rowPrefCom['usr'];
                    }
                    
                    // SI se envia por sms, obtenemos el numero del usuario
                    if($userPref['sms'] == null || $userPref['sms'] == 1){
                        $sql = "SELECT IF(users.type = 1, userpersons.phone, usercompanies.phone) AS phone ".
                        "       FROM users ".
                        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
                        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
                        "       WHERE users.id = ".$userdata['id'];
                        if (!$resPref=$db->query($sql))
                            badEnd("500", array("msg"=>determinateDBError($db)));
                            
                        $rowPrefCom = $resPref->fetch_assoc();
                        
                        $sms = $rowPrefCom['phone'];
                        
                    }
                                    
                    // Enviamos el mensaje por la mensajeria interna
                    $msg = ($userdata['lang'] == "esp" ? $data['dsc_esp'] : $data['dsc_eng']);
                    saveInternalMsg($db, $msg, $userdata['id'], $email, $sms);
                }
            }
        }
    }
    
    /*  Funcion para ingresar o cancelar depositos cancelados, recibe:
    *   $db = conexion de la DB
    *   $deposit = deposito a procesar
    *   [{
    *       id: 12,
    *       address: 'sakgdsjahdgsahjdas',
    *       amount: 0.5,
    *       created_at: 2020-09-16 10:49:59,
    *       complete_at: 2020-09-16 10:49:59,
    *       txid: 'kgjhg4j3h4g32jh4v32hjjv4hj32'
    *   }]
    *   $currencydata = datos de la moneda
    */
    function cancelDeposit($db, $deposit, $currencydata){
        //Validamos que el deposito no este ingresado y que la wallet exista
        $sql = "SELECT COUNT(ref) AS qty ".
        "       FROM deposits ".
        "       WHERE ref = '".$deposit['id']."'";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
                    
        $depositdata = $res->fetch_assoc();
                        
        //Consultamos que la wallet exista
        $sql = "SELECT COUNT(id) AS qty ".
        "       FROM wallets ".
        "       WHERE wallet = '".$deposit['address']."'";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
                    
        $wallet = $res->fetch_assoc();
                        
        //Si es verdadero, consultamos los datos del usuario(cuenta, nivel, fee)
        if(intval($wallet['qty']) == 1 && intval($depositdata['qty']) == 0){
            //Consultamos los datos de la wallet para obtener el id de la account
            $accountdata = getAccountData($db, $deposit['address']);
            if($accountdata!==""){
                //Consultamos los datos del usuario para determinar su fee
                $userdata = getUserdata($db, $accountdata['userid'], $deposit['address']);
                                
                $level = 1;
        
                if($userdata["level"] == 0){
                    $level = 1;
                }else{
                    $level = $userdata["level"];
                }
                                
                //Consultamos la cuenta que tiene la moneda del retiro de AFX
                $afxdata = getAccountAFX($db, $currencydata['id']);
                                
                //Obtenemos el fee de cada operacion por la moneda de la cuenta
                $fee = getFee($db, $level, $currencydata['id'], $userdata["type"]);
        
                //Calculamos el monto real quitandole la comision
                if($fee["pctfeedeposits"] != null || $fee["nmlfeedeposits"] != null ){
                    if($fee["pctfeedeposits"] == null){
                        
                        $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userportfolioprefs ".
                        "       WHERE currencyid = ".$currencydata['id'].
                        "       AND userid = ".$accountdata['userid'].
                        "       AND preffeesfield = 'nmlfeedeposits'";
                        if (!$res=$db->query($sql))
                            badEnd("500", array("msg"=>determinateDBError($db)));
                        
                        $userExg = $res->fetch_assoc();
                        
                        $realfee = number_format($deposit['amount'] - ($deposit['amount'] - ($fee["nmlfeedeposits"] - ($fee["nmlfeedeposits"]/100 * $userExg['exonerated']) )), $currencydata['decimals'], '.', '');
                        $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                    }else{
                        
                        $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userportfolioprefs ".
                        "       WHERE currencyid = ".$currencydata['id'].
                        "       AND userid = ".$accountdata['userid'].
                        "       AND preffeesfield = 'pctfeedeposits'";
                        if (!$res=$db->query($sql))
                            badEnd("500", array("msg"=>determinateDBError($db)));
                        
                        $userExg = $res->fetch_assoc();
                        
                        $realfee = number_format((($fee["pctfeedeposits"] - ($fee["pctfeedeposits"]/100 * $userExg['exonerated']) )*$deposit['amount'])/100, $currencydata['decimals'], '.', '');
                        $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                    }
                }else{
                    $realfee = 0;
                    $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                }
                                
                $db->autocommit(FALSE);
            
                //insertamos la trasaccion del deposito
                $sql = "INSERT INTO transactions".
                "       (".
                "       datecreated, ".
                "       dsc, ".
                "       amount, ".
                "       amountghost, ".
                "       accountid, ".
                "       orderid, ".
                "       txid, ".
                "       accountorigin, ".
                "       accountdestination".
                "       )".
                "       VALUES(".
                "       NOW(), ".
                "       'Depósito', ".
                "       0, ".
                "       ".number_format($deposit['amount'],$currencydata['decimals'],'.','').", ".
                "       ".$accountdata['accountid'].", ".
                "       NULL, ".
                "       NULL, ".
                "       NULL, ".
                "       ".$accountdata['accountid'].
                "       )";
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
        
                //Guardamos el id de la transaccion de deposito
                $txid = (int)$db->insert_id;
        
                //Le colocamos a la primera transferencia el txid
                $sql = "UPDATE transactions SET".
                "       txid = ".$txid.
                "       WHERE id = ".$txid;
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
                                
                //insertamos la comision del deposito
                $sql = "INSERT INTO transactions".
                "       (".
                "       datecreated, ".
                "       dsc, ".
                "       amount, ".
                "       amountghost, ".
                "       accountid, ".
                "       orderid, ".
                "       txid, ".
                "       accountorigin, ".
                "       accountdestination".
                "       )".
                "       VALUES(".
                "       NOW(), ".
                "       'Comisión de Depósito', ".
                "       0, ".
                "       -".$realfee.", ".
                "       ".$accountdata['accountid'].", ".
                "       NULL, ".
                "       ".$txid.", ".
                "       NULL, ".
                "       ".$afxdata["id"].
                "       )";
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
                
                //insertamos la comision del deposito
                $sql = "INSERT INTO transactions".
                "       (".
                "       datecreated, ".
                "       dsc, ".
                "       amount, ".
                "       amountghost, ".
                "       accountid, ".
                "       orderid, ".
                "       txid, ".
                "       accountorigin, ".
                "       accountdestination".
                "       )".
                "       VALUES(".
                "       NOW(), ".
                "       'Comisión de Depósito', ".
                "       0, ".
                "       ".$realfee.", ".
                "       ".$afxdata["id"].", ".
                "       NULL, ".
                "       ".$txid.", ".
                "       NULL, ".
                "       ".$afxdata["id"].
                "       )";
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
                                
                $tmpdate = explode("T",$deposit['created_at']);
                //insertamos la comision del deposito
                $sql = "INSERT INTO deposits".
                "       (".
                "       paymentref, ".
                "       paymentaccountid, ".
                "       filetype, ".
                "       paymentdate, ".
                "       createddate, ".
                "       fee, ".
                "       transactionid, ".
                "       rejected, ".
                "       amount, ".
                "       ref ".
                "       )".
                "       VALUES(".
                "       '".$deposit['txid']."', ".
                "       ".$accountdata['accountid'].", ".
                "       NULL, ".
                "       '".date("Y-m-d", strtotime($deposit['created_at']))."', ".
                "       '".date("Y-m-d H:i:s", strtotime($deposit['created_at']))."', ".
                "       ".$realfee.", ".
                "       ".$txid.", ".
                "       '".date("Y-m-d H:i:s", strtotime($deposit['completed_at']))."', ".
                "       ".number_format($deposit['amount'],$currencydata['decimals'],'.','').", ".
                "       '".$deposit['address']."' ".
                "       )";
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
        
                $db->commit();
                $db->autocommit(TRUE);
                
                createAlertWallet($db, $deposit['address'], $txid);
            }
        }else if(intval($wallet['qty']) == 1 && intval($depositdata['qty']) == 1){
            //Obtenemos los datos de el deposito para efectos de ajustar montos en transactions
            $sql = "SELECT * ".
            "       FROM deposits ".
            "       WHERE ref = '".$deposit['id']."'";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
             
            $depositrejected = $res->fetch_assoc();
            
            //Actualizamos que esta validada en la tabla
            $sql = "UPDATE deposits SET".
            "       paymentref = '".$deposit['txid']."', ".
            "       rejected = '".date("Y-m-d H:i:s", strtotime($deposit['completed_at']))."'".
            "       WHERE ref = '".$deposit['id']."'";
            if (!$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                            
            //Actualizamos la transaccion
            $sql = "UPDATE transactions SET".
            "       amount = 0, ".
            "       amountghost = amount + amountghost ".
            "       WHERE txid = ".$depositrejected['transactionid'];
            if (!$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            createAlertWallet($db, $deposit['address'], $depositrejected['transactionid']);
        }
    }
    
    function getUserdata($db, $userid, $msg = ''){
        $sql = "SELECT users.*, users.usr AS email, IF(userpersons.fname IS NULL, usercompanies.name, CONCAT(userpersons.fname,' ',userpersons.flastname)) AS name,  ".
        "       IF(users.type = 1, userpersons.phone, usercompanies.phone) AS phone " .
        "       FROM users ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       WHERE id = ".$userid;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg2"=>$db->error));
                    
        return $res->fetch_assoc();
    }
    
    function getAccountAFX($db, $currencyid){
        $sql = "SELECT * FROM accounts WHERE userid = -1".
        "       AND currencyid = ".$currencyid.
        "       AND paymentmethodid = -3";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg3"=>determinateDBError($db)));
    
        //Guardamos la data de la cuenta AFX
        return $res->fetch_assoc();
    }
    
    function getFee($db, $level, $currencyid, $usertype){
        $sql = "SELECT * FROM cmspreffees WHERE currencyid = ".$currencyid.
        "       AND levelid = ".$level.
        "       AND usertype = ".$usertype;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg5"=>determinateDBError($db)));
    
        //Guardamos la data de la cuenta
        return $res->fetch_assoc();
    }
    
    function getAccountData($db, $depositaddress){
        $sql = "SELECT wallets.wallet AS wallet, ".
        "       accounts.id AS accountid, ".
        "       accounts.userid AS userid ".
        "       FROM wallets, accounts ".
        "       WHERE wallets.wallet = '".$depositaddress."'".
        "       AND accounts.id = wallets.accountid ".
        "       AND accounts.entityusrid = -1 ".
        "       AND accounts.paymentmethodid = -1";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg1"=>determinateDBError($db)));
        
        $rsp = $res->fetch_assoc();
        if($rsp['userid']==""||$rsp['userid']==null)$rsp = "";
        return $rsp;
    }
    
    function formattUsdtAmount($value, $decimals){
        $amount = 0;
        
        // Si el numero es menor a los decimales, significa que el monto es decimales en USDT
        if (strlen($value) <= $decimals) {
            // Al ser decimal, empieza en 0
            $begin = 0;
            // Le agregamos la cantidad de 0 dependiento del valor
            $end = str_pad($value, $decimals, '0', STR_PAD_LEFT);
            $amount = $begin.".".$end;
        } else {
            // Tomamos los valores por delante de la cantidad de decimales
            $begin = substr($value, 0, strlen($value)-$decimals);
            // tomamos los valores por detras de la cantidad de decimales
            $end = substr($value, strlen($value)-$decimals);
            $amount = $begin.".".$end;
        }
        
        return $amount;
    }
    
    function createAlertWallet($db, $wallet, $txid = '') {
        $sql = "SELECT COUNT(id) AS qty, id FROM blacklist ".
        "       WHERE account = '".$wallet."'";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
                
        $row = $res->fetch_assoc();
        
        // Si existe levantamos una alerta con el id del usuario
        if ($row['qty'] > 0) {
            // Obtenemos el userid
            $useraccount = getAccountData($db, $wallet);
            
            createAlert($db, 6, $useraccount['userid'], ' Cuenta encontrada en lista negativa <span>#'.$row['id'].'</span>. Transacción nro: <span>'.$txid.'</span>');
        }
    }
?>