<?php
// cms/api/blacklist/validate
    ini_set('default_charset','utf-8');
    header("Content-Type:application/json; charset=UTF-8");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // Consultamos los registros que no tienen un userid
    $sql = "SELECT * FROM blacklist WHERE userid IS NULL";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    while($row = $rs->fetch_assoc()){
        
        // Recorremos cada uno y buscamos similitudes en los datos de los usuarios naturales
        $sql = "SELECT users.id AS userid, wallets.wallet AS wallet ".
        "       FROM users, accounts ".
        /*"       LEFT JOIN (".
        "           SELECT wallets.wallet AS wallet, wallets.accountid AS accountid".
        "           FROM wallets".
        "           INNER JOIN (SELECT MAX(id) AS id FROM wallets GROUP BY accountid) max " .
        "           ON wallets.id = max.id " .
        "           GROUP BY wallets.accountid".
        "       ) AS wallets ON wallets.accountid = accounts.id".*/
        "       LEFT JOIN wallets ON wallets.accountid = accounts.id".
        "       LEFT JOIN userpersons ON userid = userpersons.usrid ".
        "       LEFT JOIN usercompanies ON userid = usercompanies.usrid ".
        "       WHERE users.id = accounts.userid ".
        "       ".($row['account'] != null && $row['account'] != '' ?  " AND wallets.wallet = '".$row['account']."' " : '').
        "       ".($row['name'] != null && $row['name'] != '' ? " AND (CONCAT(".
        "           userpersons.fname,".
        "           IF(".
        "               userpersons.sname IS NOT NULL AND userpersons.sname != '', ".
        "               CONCAT(' ',userpersons.sname), ".
        "               ''".
        "           ),".
        "           CONCAT(' ', userpersons.flastname), ".
        "           IF(".
        "               userpersons.slastname IS NOT NULL AND userpersons.slastname != '', ".
        "               CONCAT(' ',userpersons.slastname), ".
        "               ''".
        "           )".
        "       ) LIKE '%".trim($row['name'])."%' OR CONCAT(".
        "               usercompanies.name, ".
        "               IF(".
        "                   usercompanies.comercialname IS NULL ".
        "                   OR usercompanies.comercialname = '', ".
        "                   '', ".
        "                   CONCAT(' / ', usercompanies.comercialname)".
        "               )".
        "           ) LIKE '%".trim($row['name'])."%')" : '').
        "       ".($row['doctype'] != null && $row['doctype'] != '0' && $row['doctype'] != '' && $row['doctype'] != '4' ? " AND userpersons.doctype = ".$row['doctype'] : '').
        "       ".($row['doc'] != null && $row['doc'] != '' ? " AND IF(userpersons.usrid IS NULL, usercompanies.doc, userpersons.doc) = '".$row['doc']."' " : '').
        "       ".($row['email'] != null && $row['email'] != '' ? " AND UPPER(users.usr) = UPPER('".$row['email']."')" : '');
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $userpersons = $res->fetch_assoc();
        
        $out->sqls [] = $sql;
        
        if ($userpersons['userid'] != null) {
            // Agregamos el userid al blacklist
            $sql = "UPDATE blacklist SET ".
            "       userid = ".$userpersons['userid'].
            "       WHERE id = ".$row['id'];
            if (!$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            createAlert($db, 5, $userpersons['userid'], 'Usuario encontrado en lista negativa <span>#'.$row['id'].'</span>');
            
            if ($row['account'] != '' && $row['account'] == $userpersons['wallet']) {
                createAlert($db, 5, $userpersons['userid'], 'Cuenta encontrada en lista negativa <span>#'.$row['id'].'</span>');
            }
        }
        
    }
    echo (json_encode($out));
    die();
?>