<?php
// app/api/users/validateamounts
    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    //Definicion de variables y almacenamiento de valores enviados por POST
    $out = new stdClass();
    
    // Obtenemos los usuarios que tengan datos transacionales y que tengan almenos una ultima conexion de 4 meses
    $sql = "SELECT users.id AS userid, ".
    "       users.usr AS usr, ".
    "       IF(users.type = 1, userpersons.amountmonthlyop, usercompanies.amountmontlyop) AS amountmon, ".
    "       IF(users.type = 1, userpersons.qtymonthlyop, usercompanies.qtymonthlyop) AS qtyop ".
    "       FROM users ".
    "       LEFT JOIN userpersons ON users.id = userpersons.usrid ".
    "       LEFT JOIN usercompanies ON users.id = usercompanies.usrid ".
    "       WHERE users.lastsession >= DATE_SUB(NOW(), INTERVAL 6 MONTH)".
    "       AND IF(users.type = 1, userpersons.qtymonthlyop, usercompanies.qtymonthlyop) IS NOT NULL".
    "       AND IF(users.type = 1, userpersons.amountmonthlyop, usercompanies.amountmontlyop) IS NOT NULL";
    if (!$rs =$db->query($sql)){
        badEnd("500", array("msg"=>determinateDBError($db)));
    }
    
    // Recorremos los usuarios encontrados
    while($row = $rs->fetch_assoc()){
        // Obtenemos la cantidad de operaciones TOTALES
        
        // Obtenemos las cuentas internas del usuario 
        $sql = "SELECT curr.id AS currencyid,".
        "       curr.name AS currencyname, ".
        "       curr.symbol AS currencysymbol, ".
        "       curr.decimals AS currencydecimals, ".
        "       curr.status AS currencystatus, ".
        "       curr.interface AS currencyinterface, ".
        "       curr.icon AS currencyicon, ".
        "       curr.xfer AS currencyxfer, ".
        "       acc.id AS accountid, ".
        "       acc.paymentmethodid AS paymentmethodid, ".
        "       acc.status AS accountstatus ".
        "       FROM currencies curr, accounts acc".
        "       WHERE acc.currencyid = curr.id ".
        "       AND acc.userid = ".$row['userid'].
        "       AND acc.paymentmethodid = -1 ".
        "       AND curr.status = 1 ".
        "       AND curr.id > 0";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $qtyop = 0;
        $amount = 0;
        while($accounts = $res->fetch_assoc()){
            
            // CANTIDAD DE OPERACIONES
            $sql = "SELECT COUNT(withdrawals.amount) AS qty ".
            "       FROM withdrawals, transactions t, accounts, users ".
            "       WHERE t.id = withdrawals.transactionid ".
            "       AND t.accountid = accounts.id ".
            "       AND accounts.userid = users.id ".
            "       AND accounts.id = ".$accounts['accountid'].
            "       AND (withdrawals.valid IS NOT NULL ".
            "       OR withdrawals.valid IS NULL) ".
            "       AND withdrawals.rejected IS NULL".
            "       AND MONTH(t.datecreated) = MONTH(NOW())".
            "       AND YEAR(t.datecreated) = YEAR(NOW())";
            if (!$resop = $db->query($sql)){
                badEnd("500", array("msg"=>determinateDBError($db)));
            }
        
            $movementData = $resop->fetch_assoc();
            
            $qtyop += $movementData['qty'];
            
            $sql = "SELECT COUNT(deposits.amount) AS qty ".
            "       FROM deposits, transactions t, accounts, users ".
            "       WHERE t.id = deposits.transactionid ".
            "       AND t.accountid = accounts.id ".
            "       AND accounts.userid = users.id ".
            "       AND accounts.id = ".$accounts['accountid'].
            "       AND (deposits.valid IS NOT NULL ".
            "       OR deposits.valid IS NULL) ".
            "       AND deposits.rejected IS NULL".
            "       AND MONTH(t.datecreated) = MONTH(NOW())".
            "       AND YEAR(t.datecreated) = YEAR(NOW())";
            if (!$resop = $db->query($sql)){
                badEnd("500", array("msg"=>determinateDBError($db)));
            }
        
            $movementData = $resop->fetch_assoc();
            
            $qtyop += $movementData['qty'];
            
            $sql = "SELECT COUNT(t.amount) AS qty
               FROM transactions t, accounts
               WHERE t.accountid = accounts.id 
               AND accounts.id = ".$accounts['accountid']."
               AND t.id = t.txid
               AND t.amount != 0
               AND (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4".
            "       AND MONTH(t.datecreated) = MONTH(NOW() AND YEAR(t.datecreated) = YEAR(NOW()))";
            if (!$resop = $db->query($sql)){
                badEnd("500", array("msg"=>determinateDBError($db)));
            }
            
            $movementData = $resop->fetch_assoc();
            
            $qtyop += $movementData['qty'];
            
            // FIN CANTIDAD DE OPERACIONES
            
            // MONTOS
            $amounttmp = 0;
            $sql = "SELECT SUM(deposits.amount) AS qty ".
            "       FROM deposits, transactions t, accounts, users ".
            "       WHERE t.id = deposits.transactionid ".
            "       AND t.accountid = accounts.id ".
            "       AND accounts.userid = users.id ".
            "       AND accounts.id = ".$accounts['accountid'].
            "       AND (deposits.valid IS NOT NULL ".
            "       OR deposits.valid IS NULL) ".
            "       AND deposits.rejected IS NULL".
            "       AND MONTH(t.datecreated) = MONTH(NOW())".
            "       AND YEAR(t.datecreated) = YEAR(NOW())";
            if (!$res = $db->query($sql)){
                badEnd("500", array("msg"=>determinateDBError($db)));
            }
        
            $movementData = $res->fetch_assoc();
            
            $amounttmp += abs($movementData['qty']);
            
            $sql = "SELECT SUM(withdrawals.amount) AS qty ".
            "       FROM withdrawals, transactions t, accounts, users ".
            "       WHERE t.id = withdrawals.transactionid ".
            "       AND t.accountid = accounts.id ".
            "       AND accounts.userid = users.id ".
            "       AND accounts.id = ".$accounts['accountid'].
            "       AND (withdrawals.valid IS NOT NULL ".
            "       OR withdrawals.valid IS NULL) ".
            "       AND withdrawals.rejected IS NULL".
            "       AND MONTH(t.datecreated) = MONTH(NOW())".
            "       AND YEAR(t.datecreated) = YEAR(NOW())";
            if (!$res = $db->query($sql)){
                badEnd("500", array("msg"=>determinateDBError($db)));
            }
            
            $movementData = $res->fetch_assoc();
            
            $amounttmp += abs($movementData['qty']);
            
            $sql = "SELECT SUM(t.amount) AS qty
               FROM transactions t, accounts
               WHERE t.accountid = accounts.id 
               AND accounts.id = ".$accounts['accountid']."
               AND t.id = t.txid
               AND t.amount != 0
               AND (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4".
            "       AND MONTH(t.datecreated) = MONTH(NOW() AND YEAR(t.datecreated) = YEAR(NOW()))";
            if (!$resop = $db->query($sql)){
                badEnd("500", array("msg"=>determinateDBError($db)));
            }
            
            $movementData = $res->fetch_assoc();
            
            $amounttmp += abs($movementData['qty']);
            
            $amount = convertAmount($db, $accounts['accountid'], $amounttmp);
        }
        
        if ($row['userid'] == 297) {
            $out->amouont = $amount;
            $out->qop = $qtyop;
        }
        
        
        if ($qtyop > validateOpype($row['qtyop'])) {
            createAlert($db, 3, $row['userid'], "Cantidad de operaciones promedio mensuales = ".$row['qtyop']." | Cantidad de Operaciones realizadas = ".$qtyop);
        }
        
        if ($amount > validateAmountType($row['amountmon'])) {
            createAlert($db, 3, $row['userid'], "Monto de operaciones promedio mensuales = ".$row['amountmon']." | Monto de Operaciones realizadas = ".numberFormatt($amount, 2));
        }
    }
    
    
    // Obtenemos los usuarios que tengan datos transacionales y que tengan almenos una ultima conexion de 4 meses
    $sql = "SELECT users.id AS userid, ".
    "       users.usr AS usr ".
    "       FROM users ".
    "       LEFT JOIN userpersons ON users.id = userpersons.usrid ".
    "       LEFT JOIN usercompanies ON users.id = usercompanies.usrid ".
    "       WHERE users.lastsession >= DATE_SUB(NOW(), INTERVAL 1 DAY)".
    "       AND (".
    "           SELECT COUNT(withdrawals.transactionid) FROM withdrawals, transactions, accounts ".
    "           WHERE DATE(withdrawals.createddate) = DATE(NOW())".
    "           AND transactions.id = withdrawals.transactionid ".
    "           AND transactions.accountid = accounts.id ".
    "           AND accounts.userid = users.id".
    "       ) >= 5";
    if (!$rs =$db->query($sql)){
        badEnd("500", array("msg"=>determinateDBError($db)));
    }
    
    // Recorremos los usuarios encontrados
    while($row = $rs->fetch_assoc()){
        createAlert($db, 7, $row['userid'], 'El usuario '.$row['usr'].' realizo 5 retiros en el mismo día');
    }
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    function convertAmount($db, $accountid, $amount) {
        // Obtenemos la moneda de la cuenta
        $sql = "SELECT currencies.id AS currencyid, ".
        "       currencies.decimals AS decimals ".
        "       FROM currencies, accounts".
        "       WHERE accounts.id = ".$accountid.
        "       AND accounts.currencyid = currencies.id ";
        if (!$res = $db->query($sql)){
            badEnd("500", array("msg"=>determinateDBError($db)));
        }
            
        $currdata = $res->fetch_assoc();
        
        // Obtenemos el rate
        $sql = "SELECT rate FROM cmsprefrates ".
        "       WHERE currencyid = ".$currdata['currencyid'];
         if (!$res = $db->query($sql)){
            badEnd("500", array("msg"=>determinateDBError($db)));
        }
            
        $rate = $res->fetch_assoc();
        
        if ($rate['rate'] > 0) {
            return $amount / $rate['rate'];
        } else {
            return 1;
        }
    }
    
    function validateAmountType($option){
        $number = 0;
        
        if($option == '1000 a 10000'){
            $number = 10000;
        } else if($option == '100 a 1000'){
            $number = 1000;
        } else if ($option == '1 a 100'){
            $number = 100;
        } else {
            $number = 10000000000;
        }
        
        return $number;
    }
    
    function validateOpype($option){
        $number = 0;
        
        if($option == 'Más de 1.000'){
            $number = 1000;
        } else if($option == '	10 a 100'){
            $number = 100;
        } else if ($option == '1 a 10'){
            $number = 10;
        } else {
            $number = 10000000000;
        }
        
        return $number;
    }
?>
