<?php

require_once("./wp-load.php");
include 'vendor/autoload.php';

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);


/* blocco upload */
if(isset($_POST["submit"])) {
    $target_dir = "uploads/";
    $target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);

    if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
        echo "The file ". htmlspecialchars( basename( $_FILES["fileToUpload"]["name"])). " has been uploaded.<br /><br />";

        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $reader->setReadDataOnly(false);

        $spreadsheet = $reader->load( "./uploads/" . $_FILES["fileToUpload"]["name"]);
        $worksheet = $spreadsheet->getActiveSheet();

        $columnA = 'A';
        $columnB = 'B';
        $columnC = 'C';
        $columnD = 'D';
        $columnE = 'E';
        $columnF = 'F';
        $columnG = 'G';
        $columnH = 'H';
        $columnI = 'I';
        $lastRow = $worksheet->getHighestRow();


        //@mauri il problema è qui!
        for ($row = 1; $row <= $lastRow; $row++) {

            //A
            $cell = $worksheet->getCell($columnA.$row)->getFormattedValue();
            echo $cell . "; ";

            //B
            $cell = $worksheet->getCell($columnB.$row)->getFormattedValue();
            echo $cell . "; ";

            //C
            $cell = $worksheet->getCell($columnC.$row)->getFormattedValue();
            echo $cell . "; ";

            //D
            $cell = $worksheet->getCell($columnD.$row)->getFormattedValue();
            echo $cell . "; ";

            //E
            $cell = $worksheet->getCell($columnE.$row)->getFormattedValue();
            echo $cell . "; ";

            //F
            $cell = $worksheet->getCell($columnF.$row)->getFormattedValue();
            echo $cell . "; ";

            //G
            $cell = $worksheet->getCell($columnG.$row)->getFormattedValue();
            echo $cell . "; ";

            //H
            $cell = $worksheet->getCell($columnH.$row)->getFormattedValue();
            echo $cell . "; ";

            //I
            $cell = $worksheet->getCell($columnI.$row)->getFormattedValue();
            echo $cell . "; ";

            echo "<br /><br />";

        }



    }

    echo '<br /><br /><a href="/inoutpost.php">Torna indietro</a>';
    die();
}
/* fine blocco upload */




if($_GET && $_GET['cat']) {
    $cat = $_GET['cat'];
}

if($_GET && $_GET['download']) {
    $download = $_GET['download'];
}


$traduttore_html_to_rich = new \PhpOffice\PhpSpreadsheet\Helper\Html();
$PhpSpreadsheet_per_scrittura = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$oggetto_excel = $PhpSpreadsheet_per_scrittura->getActiveSheet();

get_header(); ?>
<?php $mystique_option = mystique_global_var_declare('kid_option');?>
<br />
<h1>Import / Export WP to XLSX</h1>
<br />
<form method="post" action="#" enctype="multipart/form-data">
<input type="file" name="fileToUpload" id="fileToUpload" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />
<input type="submit" name="submit" value="IMPORTA" style="position: relative; padding: 15px; color: #fff; background: #ae0018;" />
</form>
<br /><hr><br />

<br />
<select id="cats">
<?php
$categories = get_categories();
echo '<option value="">---</option>';
foreach($categories as $category) {
?>

    <option <?php if(isset($cat) && $cat == $category->term_id) { echo 'selected'; } ?> value="<?=$category->term_id?>"><?=$category->name?></option>

<?php } ?>
</select>

<a href="/inoutpost.php?cat=<?=$cat?>&download=true" style="position: absolute; right: 0; padding: 15px; color: #fff; background: #ae0018;">SCARICA EXCEL</a>


<?php

echo "<br /><br />";


if(isset($cat) && $cat != "") {
    $args = array(
      'post_type' => 'post' ,
      'orderby' => 'date' ,
      'order' => 'DESC' ,
      'post_status' => 'publish',
      'posts_per_page' => -1,
      'cat'         => $cat,
    );
} else {
    $args = array(
      'post_type' => 'post' ,
      'orderby' => 'date' ,
      'order' => 'DESC' ,
      'post_status' => 'publish',
      'posts_per_page' => -1
    );
}


$q = new WP_Query($args);
if ( $q->have_posts() ) {

    if(isset($download) && $download == "true") {
        $i = 1;

        $cella_da_scrivere = $oggetto_excel->getCell('A'.($i));
        $cella_da_scrivere->setValue("ID");
        $cella_da_scrivere = $oggetto_excel->getCell('B'.($i));
        $cella_da_scrivere->setValue("TITOLO ITA");
        $cella_da_scrivere = $oggetto_excel->getCell('C'.($i));
        $cella_da_scrivere->setValue("TITOLO FRA");
        $cella_da_scrivere = $oggetto_excel->getCell('D'.($i));
        $cella_da_scrivere->setValue("TRAMA");
        $cella_da_scrivere = $oggetto_excel->getCell('E'.($i));
        $cella_da_scrivere->setValue("ABSTRACT");
        $cella_da_scrivere = $oggetto_excel->getCell('F'.($i));
        $cella_da_scrivere->setValue("BIO AUTORE");
        $cella_da_scrivere = $oggetto_excel->getCell('G'.($i));
        $cella_da_scrivere->setValue("DIRITTI ESTERI");
        $cella_da_scrivere = $oggetto_excel->getCell('H'.($i));
        $cella_da_scrivere->setValue("LUOGO PUBBLICAZIONE");
        $cella_da_scrivere = $oggetto_excel->getCell('I'.($i));
        $cella_da_scrivere->setValue("PREMI");

        while ($q->have_posts()) : $q->the_post();

            $cella_da_scrivere = $oggetto_excel->getCell('A'.($i+1));
            $cella_da_scrivere->setValue(get_the_ID());
            // $cella_da_scrivere->getStyle('A'.($i+1))->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED);
            $cella_da_scrivere->getStyle('A'.($i+1))->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_PROTECTED);

            $cella_da_scrivere = $oggetto_excel->getCell('B'.($i+1));
            $rich_value = $traduttore_html_to_rich->toRichTextObject(get_the_title());
            echo '<pre>';
            print_r ($rich_value);
            echo '</pre>';
            $cella_da_scrivere->setValue($rich_value);

            $cella_da_scrivere = $oggetto_excel->getCell('D'.($i+1));
            $rich_value = $traduttore_html_to_rich->toRichTextObject(str_replace("\\", "", get_field('trama')));
            echo '<pre>';
            print_r ($rich_value);
            echo '</pre>';
            $cella_da_scrivere->setValue($rich_value);

            $cella_da_scrivere = $oggetto_excel->getCell('E'.($i+1));
            $rich_value = $traduttore_html_to_rich->toRichTextObject(str_replace("\\", "", get_field('abstract')));
            echo '<pre>';
            print_r ($rich_value);
            echo '</pre>';
            $cella_da_scrivere->setValue($rich_value);

            $cella_da_scrivere = $oggetto_excel->getCell('F'.($i+1));
            $rich_value = $traduttore_html_to_rich->toRichTextObject(str_replace("\\", "", get_field('bio_autore')));
            echo '<pre>';
            print_r ($rich_value);
            echo '</pre>';
            $cella_da_scrivere->setValue($rich_value);

            $cella_da_scrivere = $oggetto_excel->getCell('G'.($i+1));
            $rich_value = $traduttore_html_to_rich->toRichTextObject(str_replace("\\", "", get_field('diritti_esteri_gia_venduti')));
            echo '<pre>';
            print_r ($rich_value);
            echo '</pre>';
            $cella_da_scrivere->setValue($rich_value);

            $cella_da_scrivere = $oggetto_excel->getCell('H'.($i+1));
            $rich_value = $traduttore_html_to_rich->toRichTextObject(str_replace("\\", "", get_field('luogo_pubblicazione')));
            echo '<pre>';
            print_r ($rich_value);
            echo '</pre>';
            $cella_da_scrivere->setValue($rich_value);

            $cella_da_scrivere = $oggetto_excel->getCell('I'.($i+1));
            $rich_value = $traduttore_html_to_rich->toRichTextObject(str_replace("\\", "", get_field('premi')));
            echo '<pre>';
            print_r ($rich_value);
            echo '</pre>';
            $cella_da_scrivere->setValue($rich_value);

            $i++;
        endwhile;

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($PhpSpreadsheet_per_scrittura, 'Xlsx');
        $writer->save('export_' . $cat . "_" . date('d-m-y') . '.xlsx');

        downloadFile('/','export_' . $cat . "_" . date('d-m-y') . '.xlsx');


    } else {
       while ($q->have_posts()) : $q->the_post();

            echo "<strong>ID:</strong> " . get_the_ID();
            echo "<br />";

            echo "<strong>TITOLO:</strong> " . get_the_title();
            echo "<br />";

            echo "<strong>TRAMA:</strong> " . str_replace("\\", "", get_field('trama'));
            echo "<br />";

            echo "<strong>ABSTRACT:</strong> " . str_replace("\\", "", get_field('abstract'));
            echo "<br />";

            echo "<strong>BIO AUTORE:</strong> " . str_replace("\\", "", get_field('bio_autore'));
            echo "<br />";

            echo "<strong>DIRITTI ESTERI:</strong> " . str_replace("\\", "", get_field('diritti_esteri_gia_venduti'));
            echo "<br />";

            echo "<strong>LUOGO PUBBLICAZIONE:</strong> " . str_replace("\\", "", get_field('luogo_pubblicazione'));
            echo "<br />";

            echo "<strong>PREMI:</strong> " . str_replace("\\", "", get_field('premi'));
            echo "<br />";

            echo "<br /><hr><br />";
        endwhile;
    }
    wp_reset_postdata();
}

?>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js" integrity="sha512-bLT0Qm9VnAYZDflyKcBaQ2gg0hSYNQrJ8RilYldYQ1FxQYoCLtUjuuRuZo+fjqhx/qtq/1itJ0C2ejDxltZVFg==" crossorigin="anonymous"></script>
<script>

$('#cats').on('change', function() {
    self.location = 'inoutpost.php?cat=' + this.value;
});

</script>
<?php
get_footer();

function downloadFile($path,$name) {
    echo '<script>window.location.href = "' . $path . $name . '"</script>';
}

?>