<?php
// cms/api/blacklist/uploadpicture
    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    require('../../hooks/vendor/autoload.php');
    
    // parametros obligatorios
    $parmsob = array('sessionid');
    if (!parametrosValidos($_POST, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
    
    if(!isset($_FILES['file'])){
        badEnd("400", array("msg"=>"Parametros obligatorios file"));
    }
    
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    
    //Declaramos e inicializamos nuestras variables
    $out = new stdClass();
    $sessionid = $_POST["sessionid"];
    $excel = $_FILES['file'];
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 89))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
        
        
    $inputFileName = $excel["tmp_name"];
    $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
    
    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
    
    $spreadsheet = $reader->load($inputFileName);
    
    $rows = $spreadsheet->getActiveSheet()->toArray();
    
    //$out->testing = $rows;
    
    // Insert
    $sqlinsert = "INSERT INTO blacklist(doctype, doc, name, account, email, organism, date, createddate, lastupload, link) VALUES";
    
    $count = 0;
    $updated = 0;
    $failed = 0;
    foreach($rows as $index => $row){
        // Validamos si hay registros
        if ($row[1] != null && $index > 4) {
            // Validamos que tenga almenos 1 dato requerido
            if (($row[2] != '' && $row[3] != '') || $row[4] != '' || $row[5] != '' || $row[6] != '') {
                // Validamos que el registro no exista
                $sql = "SELECT id ".
                "       FROM blacklist ".
                "       WHERE doctype = '".validateDocType($row[2])."' ".
                "       AND doc = '".$row[3]."' ".
                "       AND name = '".$row[4]."' ".
                "       AND account = '".$row[5]."' ".
                "       AND email = '".$row[6]."' ".
                "       AND organism = '".$row[10]."' ".
                "       AND link = '".$row[12]."'";
                if (!$rs=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $res = $rs->fetch_assoc();
                
                if($res['id'] != null){
                    // Actualizamos ese registro
                    $sql = "UPDATE blacklist SET ".
                    "       lastupload = NOW() ".
                    "       WHERE id = ".$res['id'];
                    if (!$db->query($sql))
                        badEnd("500", array("msg"=>determinateDBError($db)));
                        
                    $updated++;
                } else {
                    $record = "(".
                    "           '".validateDocType($row[2])."', ".
                    "           '".$row[3]."', ".
                    "           '".$row[4]."', ".
                    "           '".$row[5]."', ".
                    "           '".$row[6]."', ".
                    "           '".$row[10]."', ".
                    "           ".($row[11] == null ? 'NULL' : "'".date('Y-m-d', strtotime(str_replace('/', '-', $row[11])))."'").", ".
                    "           NOW(), ".
                    "           NOW(), ".
                    //"           ".($row[8] == null ? 'NULL' : $row[8]).", ". No se insertan estos id's se machean automatico de la plataforma porque esos son de dayco
                    "           '".$row[12]."'".
                    "           ),";
                    $sqlinsert .= $record;
                    $count++;
                }
            } else {
                $failed++;
            }
        }
    }
    
    if($count > 0){
        
        $sqlinsert = substr($sqlinsert, 0, -1);
        
        if (!$db->query($sqlinsert))
            badEnd("500", array("msg"=>determinateDBError($db)));
    }
        
    $out->loaded = new stdClass();
    $out->loaded->news = (int)$count;
    $out->loaded->updated = (int)$updated;
    $out->loaded->failed = (int)$failed;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    function validateDocType($docType){
        $str = '';
        
        if(strtoupper($docType) == 'V' || strtoupper($docType) == 'F' || $docType == 1){
            $str = 1;
        } else if($docType == 2) {
            $str = 2;
        } else if ($docType == 3){
            $str = 3;
        } else if ($docType == 4 || strtoupper($docType) == 'J') {
        
        } else {
            $str = '';
        }
        
        return $str;
    }
?>