gilour

Back Back Up Advanced Editor

<?php

use secure\Secure;
use crud\ElementsUtilities;
use export\ExportData;
use export\ExportDataExcel;
use export\ExportDataCSV;
use export\ExportDataTSV;
use phpformbuilder\database\DB;
use phpformbuilder\database\Pagination;

header("X-Robots-Tag: noindex", true);

session_start();
include_once '../../conf/conf.php';
include_once ADMIN_DIR . 'secure/class/secure/Secure.php';
include_once ADMIN_DIR . 'class/export/ExportData.php';

// lock page
Secure::lock();

if (!isset($_GET['table']) || !isset($_GET['npp']) || !is_numeric($_GET['npp']) || (isset($_GET['p']) && !is_numeric($_GET['p']))) {
    exit('error 1');
}
$npp = $_GET['npp'];
if ($_GET['npp'] < 0) {
    $npp = 100000;
}
$table = addslashes($_GET['table']);
if (!isset($_SESSION['export'][$table]['pdo_settings'])) {
    exit('error 2');
}

// get the item name
$upperCamelCaseTable = ElementsUtilities::upperCamelCase($table);
$item = mb_strtolower($upperCamelCaseTable);

$db = new Pagination();
$columns = $db->getColumnsNames($table);

// get the foreign fields displayed values (a foreign field can display 2 combined values from the relational table)
$select_data = json_decode(file_get_contents('../crud-data/' . $item . '-select-data.json'));
$select_data_tables = array();
foreach ($columns as $column_name) {
    // default
    $outputRows[$column_name] = array(
        'field_1'    => $column_name,
        'field_2'    => ''
    );
    // if 2 combined values
    $column_select_data = $select_data->$column_name;
    if ($column_select_data->from === 'from_table' && strpos($_SESSION['export'][$table]['pdo_settings']['from'], $column_select_data->from_table) !== false) {
        $outputRows[$column_name] = array(
            'field_1'    => $column_select_data->from_field_1,
            'field_2'    => $column_select_data->from_field_2
        );
        if (!in_array($column_select_data->from_table, $select_data_tables)) {
            $select_data_tables[] = $column_select_data->from_table;
        }
    }
}
/* Example:
$_SESSION['export'][$table]['pdo_settings'] = array(
    'function' => 'select',
    'from'    => 'actor' . $active_filters_join_queries,
    'values'   => $columns,
    'where'    => $where,
    'extras'   => array('order_by' => $this->sorting),
    'debug'    => DEBUG_DB_QUERIES
);
*/

$pdo_settings_keys = array('function', 'from', 'values', 'where', 'extras', 'debug');

foreach ($pdo_settings_keys as $key) {
    if (!array_key_exists($key, $_SESSION['export'][$table]['pdo_settings'])) {
        exit('Missing key <em>' . $key . '</em> in $pdo_settings_keys(admin/inc/export-data.php)');
    }
}

$table_alias = array();

if ($element_joins_count = preg_match_all('`(LEFT|INNER|RIGHT) JOIN ([a-zA-Z0-9_]+)\s?([a-zA-Z0-9_]+)? ON ([a-zA-Z0-9_]+).([a-zA-Z0-9_]+)(?:[\s]*=[\s]*)([a-zA-Z0-9_]+).([a-zA-Z0-9_]+)`', $_SESSION['export'][$table]['pdo_settings']['from'], $out)) {
    for ($i = 0; $i < $element_joins_count; $i++) {
        if (!empty($out[3][$i])) {
            // if table alias
            $original_field_name = $out[7][$i];
            $field_alias = $out[4][$i] . '_' . $out[5][$i];
            $table_alias[$original_field_name] = $out[3][$i];
            // 'original_language_id' => string 't1'
        }
    }
}

// get the columns names from the SELECT query and replace aliases
$fields_query = $_SESSION['export'][$table]['pdo_settings']['values'];
$fields_array = explode(',', $fields_query);
if (empty($fields_array)) {
    exit('failed to parse the query (1)');
}

if (!is_null($_SESSION['export'][$table]['pdo_settings']['values'])) {
    $values_array = explode(', ', $_SESSION['export'][$table]['pdo_settings']['values']);
    foreach ($values_array as $field_query) {
        // e.g.:t1.language_id AS t1_language_id
        if (preg_match('/([^.]+)\.([^\s]+)( AS ([a-zA-Z0-9_-]+))?/', trim($field_query), $out)) {
            if (!isset($out[2])) {
                exit('failed to parse the query (1) - ' . $field_query);
            }
            if (isset($out[4])) {
                foreach ($outputRows as $cname => $fields) {
                    if (isset($table_alias[$cname])) {
                        if ($fields['field_1'] === $out[2]) {
                            $outputRows[$cname]['field_1'] = ElementsUtilities::shortenAlias($table_alias[$cname] . '_' . $outputRows[$cname]['field_1']);
                        }
                        if ($fields['field_2'] === $out[2]) {
                            $outputRows[$cname]['field_2'] = ElementsUtilities::shortenAlias($table_alias[$cname] . '_' . $outputRows[$cname]['field_2']);
                        }
                    } else {
                        if ($fields['field_1'] === $out[2]) {
                            $outputRows[$cname]['field_1'] = ElementsUtilities::shortenAlias($out[1] . '_' . $outputRows[$cname]['field_1']);
                        } elseif ($fields['field_2'] === $out[2]) {
                            $outputRows[$cname]['field_2'] = ElementsUtilities::shortenAlias($out[1] . '_' . $outputRows[$cname]['field_2']);
                        } elseif ($fields['field_1'] === $out[1] . '.' . $out[2]) {
                            // if we're in a secondary relation. e.g: store => address.postal_code
                            $outputRows[$cname]['field_1'] = $out[4];
                        } elseif ($fields['field_2'] === $out[1] . '.' . $out[2]) {
                            $outputRows[$cname]['field_2'] = $out[4];
                        }
                    }
                }
            }
        } else {
            exit('failed to parse the query (2) - ' . $field_query);
        }
    }
}

$db->pagine($_SESSION['export'][$table]['pdo_settings'], $npp, 'p', '', 1, false, '/', '');
$nbre = $db->rowCount();

// var_dump($_SESSION['export'][$table]['pdo_settings']);
// var_dump($outputRows);
// var_dump($columns);

if (!empty($nbre)) {
    $output = array(
        'thead' => $columns,
        'rows'  => array()
    );
    $i = 0;
    while ($row = $db->fetch()) {
        foreach ($columns as $column_name) {
            $fieldname = $outputRows[$column_name]['field_1'];
            $out = $row->$fieldname;
            if (!empty($outputRows[$column_name]['field_2'])) {
                $fieldname_2 = $outputRows[$column_name]['field_2'];
                $out .= ' ' . $row->$fieldname_2;
            }
            $output['rows'][$i][] = $out;
        }
        $i++;
    }
    $thead = '<thead><tr><th>' . implode('</th><th>', $output['thead']) . '</th></tr></thead>';
    $tbody = '<tbody>' . array_reduce($output['rows'], function ($a, $b) {
        return $a .= '<tr><td>' . implode('</td><td>', $b) . '</td></tr>';
    }) . '</tbody>';
}
?>
<!doctype html>
<html lang="en">

<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.1.3/css/bootstrap.min.css" />
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs5/jq-3.6.0/jszip-2.5.0/dt-1.12.1/b-2.2.3/b-colvis-2.2.3/b-html5-2.2.3/b-print-2.2.3/datatables.min.css" />

<body>
    <?php echo '<table id="export-table" class="display compact" style="width:100%">' . $thead . $tbody . '</table>'; ?>
    
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.1.3/js/bootstrap.bundle.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/v/bs5/jq-3.6.0/jszip-2.5.0/dt-1.12.1/b-2.2.3/b-colvis-2.2.3/b-html5-2.2.3/b-print-2.2.3/datatables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            $('#export-table').DataTable({
                dom: 'Bfrtip',
                paging: false,
                ordering: false,
                searching: false,
                info: false,
                scrollX: '90vw',
                scrollY: 'calc(90vh - 110px)',
                buttons: [{
                    extend: 'excel',
                    filename: '<?php echo $table . '-export-' . date('Y-m-d'); ?>',
                    className: 'btn-success'
                }, 'spacer', {
                    extend: 'csv',
                    filename: '<?php echo $table . '-export-' . date('Y-m-d'); ?>',
                    className: 'btn-warning'
                }, 'spacer', {
                    extend: 'pdf',
                    filename: '<?php echo $table . '-export-' . date('Y-m-d'); ?>',
                    className: 'btn-danger'
                }, 'spacer', {
                    extend: 'print',
                    className: 'btn-info',
                    exportOptions: {
                        stripHtml: false
                    }
                }],
                createdRow: function(row) {
                    $(row).find('td table')
                        .DataTable({
                            dom: 'Bfrtip',
                            paging: false,
                            ordering: false,
                            searching: false,
                            buttons: []
                        })
                }
            });
        });
    </script>
</body>

</html>