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

    //$mt = microtime(true);
    header("Content-Type:application/json");
    include("../dbconn.php");
    include("../utils.php");
    $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";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error.$sql));
        
    while($currencydata = $rs->fetch_assoc()){
        //Aqui colocaremos un switch con los interface de las apis
        switch(intval($currencydata['interface'])){
            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"=>$db->error.$sql));
                    
                $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']);
                    
                    // 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']);
                    
                    // Analizamos el deposito para ingresarlo y aceptarlo o solo aceptarlo(VALID)
                    foreach($accepted as $deposit){
                        if(isset($deposit['id'])){
                            acceptedDeposit($db, $deposit, $currencydata);
                        }
                    }
                    
                    //Consultamos los depositos rechazados
                    $rejected = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'rejected']);
                    
                    // 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']);
                    
                    // 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']);
                    
                    // 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']);
                    
                    // Analizamos el deposito para ingresarlo y aceptarlo o solo aceptarlo(VALID)
                    foreach($accepted as $deposit){
                        if(isset($deposit['id'])){
                            acceptedDeposit($db, $deposit, $currencydata);
                        }
                    }
                    
                    //Consultamos los depositos rechazados
                    $rejected = queryPrivate("deposits", $TCV_JWT, $TCV_URL, $VERSION_API, true , ['limit' => 100, 'currency' => 'PTR', 'state' => 'rejected']);
                    
                    // 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']);
                    
                    // 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, ".
                "       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"=>$db->error.$sql));
                    
                $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);
                    
                    // 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"=>$db->error.$sql));
                        
                            $wallet = $resW->fetch_assoc();
                                /*
                            if($wallet['qty'] > 0 && $deposit['confirmations'] < 5){
                                $parms = array('nblocks'=>1,'address'=>$deposit['address']);
                                $request = array('method'=>'generatetoaddress','parms' => $parms);
                                $rest = json_decode(queryServicesBTC($BTC_URL, $request), true);
                            }*/
                            
                            // 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);
                                //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);
                    
                    // 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"=>$db->error.$sql));
                        
                                $wallet = $resW->fetch_assoc();
                                /*
                                if($wallet['qty'] > 0 && $deposit['confirmations'] < 5){
                                    $parms = array('nblocks'=>1,'address'=>$deposit['address']);
                                    $request = array('method'=>'generatetoaddress','parms' => $parms);
                                    $rest = json_decode(queryServicesBTC($BTC_URL, $request), true);
                                }*/
                                
                                // 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);
                                    //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']."'";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>$db->error.$sql));
        
        $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"=>$db->error.$sql));
                    
        $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){
                $realfee = number_format($deposit['amount'] - ($deposit['amount'] - $fee["nmlfeedeposits"]), $currencydata['decimals'], '.', '');
                $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
            }else{
                $realfee = number_format(($fee["pctfeedeposits"]*$deposit['amount'])/100, $currencydata['decimals'], '.', '');
                $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
            }
        }else{
            $realfee = 0;
            $realamount = number_format($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, ".
        "       ".$deposit['amount'].", ".
        "       ".$accountdata['accountid'].", ".
        "       NULL, ".
        "       NULL, ".
        "       NULL, ".
        "       ".$accountdata['accountid'].
        "       )";
        if (!$db->query($sql)){
            $error = $db->error;
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error.$sql));
        }
    
        //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 = $db->error;
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error.$sql));
        }
                            
        //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 = $db->error;
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error.$sql));
        }
            
        //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 = $db->error;
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error.$sql));
        }
        
        //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, ".
        "       '".$deposit['created_at']."', ".
        "       '".$deposit['created_at']."', ".
        "       ".$realfee.", ".
        "       ".$txid.", ".
        "       NULL, ".
        "       ".$deposit['amount'].", ".
        "       '".$deposit['id']."' ".
        "       )";
        if (!$db->query($sql)){
            $error = $db->error;
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error.$sql));
        }
    
        $db->commit();
        $db->autocommit(TRUE);
        }
    }
    
    /*  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){
        //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"=>$db->error.$sql));
                    
        $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"=>$db->error.$sql));
        
        $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){
                    $realfee = number_format($deposit['amount'] - ($deposit['amount'] - $fee["nmlfeedeposits"]), $currencydata['decimals'], '.', '');
                    $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                }else{
                    $realfee = number_format(($fee["pctfeedeposits"]*$deposit['amount'])/100, $currencydata['decimals'], '.', '');
                    $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                }
            }else{
                $realfee = 0;
                $realamount = number_format($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, ".
            "       ".$deposit['amount'].", ".
            "       ".$accountdata['accountid'].", ".
            "       NULL, ".
            "       NULL, ".
            "       NULL, ".
            "       ".$accountdata['accountid'].
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
    
            //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 = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
        
            //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 = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
            
            //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 = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
                            
            //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, ".
            "       '".$deposit['created_at']."', ".
            "       '".$deposit['created_at']."', ".
            "       ".$realfee.", ".
            "       ".$txid.", ".
            "       '".$deposit['completed_at']."', ".
            "       ".$deposit['amount'].", ".
            "       '".$deposit['id']."' ".
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
    
            $db->commit();
            $db->autocommit(TRUE);
        }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"=>$db->error.$sql));
              
            $depositdata = $res->fetch_assoc();
            
            if($depositdata['valid'] == null){
                //Actualizamos que esta validada en la tabla
                $sql = "UPDATE deposits SET".
                "       valid = '".$deposit['completed_at']."', ".
                "       paymentref = '".$deposit['txid']."' ".
                "       WHERE ref = '".$deposit['id']."'";
                if (!$db->query($sql))
                    badEnd("500", array("msg"=>$db->error.$sql));
                            
                //Actualizamos la transaccion
                $sql = "UPDATE transactions SET".
                "       amount = amount + amountghost, ".
                "       amountghost = 0 ".
                "       WHERE txid = ".$depositdata['transactionid'];
                if (!$db->query($sql))
                    badEnd("500", array("msg"=>$db->error.$sql));
            }
                                
            
        }
    }
    
    /*  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"=>$db->error.$sql));
                    
        $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"=>$db->error.$sql));
                    
        $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){
                    $realfee = number_format($deposit['amount'] - ($deposit['amount'] - $fee["nmlfeedeposits"]), $currencydata['decimals'], '.', '');
                    $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                }else{
                    $realfee = number_format(($fee["pctfeedeposits"]*$deposit['amount'])/100, $currencydata['decimals'], '.', '');
                    $realamount = number_format($deposit['amount'] - $realfee, $currencydata['decimals'], '.', '');
                }
            }else{
                $realfee = 0;
                $realamount = number_format($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, ".
            "       ".$deposit['amount'].", ".
            "       ".$accountdata['accountid'].", ".
            "       NULL, ".
            "       NULL, ".
            "       NULL, ".
            "       ".$accountdata['accountid'].
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
    
            //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 = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
                            
            //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 = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
            
            //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 = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
                            
            //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, ".
            "       '".$deposit['created_at']."', ".
            "       '".$deposit['created_at']."', ".
            "       ".$realfee.", ".
            "       ".$txid.", ".
            "       '".$deposit['completed_at']."', ".
            "       ".$deposit['amount'].", ".
            "       '".$deposit['id']."' ".
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
    
            $db->commit();
            $db->autocommit(TRUE);
            
        }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"=>$db->error.$sql));
             
            $depositrejected = $res->fetch_assoc();
            
            //Actualizamos que esta validada en la tabla
            $sql = "UPDATE deposits SET".
            "       paymentref = '".$deposit['txid']."', ".
            "       rejected = '".$deposit['completed_at']."'".
            "       WHERE ref = '".$deposit['id']."'";
            if (!$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
                            
            //Actualizamos la transaccion
            $sql = "UPDATE transactions SET".
            "       amount = 0, ".
            "       amountghost = amount + amountghost ".
            "       WHERE txid = ".$depositrejected['transactionid'];
            if (!$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
        }
    }
    
    function getUserdata($db, $userid, $msg = ''){
        $sql = "SELECT * FROM users WHERE id = ".$userid;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>$db->error.$sql,'msg2'=>$msg));
                    
        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("msg"=>$db->error.$sql));
    
        //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("msg"=>$db->error.$sql));
    
        //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.paymentmethodid = -1";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>$db->error.$sql));
                
        return $res->fetch_assoc();
    }
?>