<?php
 document::$snippets['title'][] = language::translate('title_import_export_csv', 'Import/Export CSV'); breadcrumbs::add(language::translate('title_import_export_csv', 'Import/Export CSV')); $collections = include __DIR__.'/collections.inc.php'; if (isset($_POST['import'])) { try { if (!isset($_FILES['file']['tmp_name']) || !is_uploaded_file($_FILES['file']['tmp_name'])) { throw new Exception(language::translate('error_must_select_file_to_upload', 'You must select a file to upload')); } if (!empty($_FILES['file']['error'])) { throw new Exception(language::translate('error_uploaded_file_rejected', 'An uploaded file was rejected for unknown reason')); } $csv = file_get_contents($_FILES['file']['tmp_name']); if (!$csv = functions::csv_decode($csv, $_POST['delimiter'], $_POST['enclosure'], $_POST['escapechar'], $_POST['charset'])) { throw new Exception(language::translate('error_failed_decoding_csv', 'Failed decoding CSV')); } if (empty($csv[0]['code'])) throw new Exception(language::translate('error_missing_code_column', 'Missing column for code')); $language_codes = array_diff(array_keys($csv[0]), ['code']); foreach ($language_codes as $language_code) { if (!in_array($language_code, array_keys(language::$languages))) { throw new Exception('Skipping unknown language ('. $language_code .') which is either missing or disabled'); } } $updated = 0; $inserted = 0; $line = 0; foreach ($csv as $row) { $line++; if (preg_match('#^\[([a-z_]+):([0-9]+)\](.*)$#', $row['code'], $matches)) { if (!$collection = $collections[array_search($matches[1], array_column($collections, 'entity'))]) { throw new Exception('Unsupported entity on line '.$line); } list($entity, $id, $column) = array_slice($matches, 1); foreach ($language_codes as $language_code) { $translation_query = database::query( "select * from ". DB_TABLE_PREFIX . $collection['info_table'] ."
              where `". database::input($collection['entity_column']) ."` = '". database::input($id) ."'
              and language_code = '". database::input($language_code) ."'
              limit 1;" ); if ($translation = database::fetch($translation_query)) { if (empty($row[$language_code])) continue; if (empty($_POST['update']) && empty($_POST['append'])) continue; if (empty($translation['text_'.$language_code]) && empty($_POST['append'])) continue; if (!empty($translation['text_'.$language_code]) && empty($_POST['update'])) continue; if (!in_array($language_code, array_keys(language::$languages))) continue; database::query( "update ". DB_TABLE_PREFIX . $collection['info_table'] ."
                set `". database::input($column) ."` = '". database::input($row[$language_code], true) ."'
                where id = '". database::input($translation['id']) ."'
                limit 1;" ); $updated++; } else { if (empty($_POST['append'])) continue; database::query( "insert into ". DB_TABLE_PREFIX . $collection['info_table'] ."
                (`". database::input($collection['entity_column']) ."`, language_code, `". database::input($column, !empty($translation['html'])) ."`)
                values ('". database::input($id) ."', '". database::input($language_code) ."', '". database::input($row[$language_code]) ."');" ); $inserted++; } } } else { $translation_query = database::query( "select * from ". DB_TABLE_PREFIX ."translations
            where code = '". database::input($row['code']) ."'
            limit 1;" ); if ($translation = database::fetch($translation_query)) { foreach ($language_codes as $language_code) { if (empty($row[$language_code])) continue; if (empty($_POST['update']) && empty($_POST['append'])) continue; if (empty($translation['text_'.$language_code]) && empty($_POST['append'])) continue; if (!empty($translation['text_'.$language_code]) && empty($_POST['update'])) continue; if (!in_array($language_code, array_keys(language::$languages))) continue; database::query( "update ". DB_TABLE_PREFIX ."translations
                set `text_". database::input($language_code) ."` = '". database::input($row[$language_code], true) ."'
                where code = '". database::input($row['code']) ."'
                limit 1;" ); $updated++; } } else { if (empty($_POST['insert'])) continue; database::query( "insert into ". DB_TABLE_PREFIX ."translations
              (code) values ('". database::input($row['code']) ."');" ); foreach ($language_codes as $language_code) { if (empty($row[$language_code])) continue; if (!in_array($language_code, array_keys(language::$languages))) continue; database::query( "update ". DB_TABLE_PREFIX ."translations
                set text_". $language_code ." = '". database::input($row[$language_code], true) ."'
                where code = '". database::input($row['code']) ."'
                limit 1;" ); $inserted++; } } } } cache::clear_cache(); notices::add($updated ? 'success' : 'notice', strtr(language::translate('success_updated_n_existing_entries', 'Updated %n existing entries'), ['%n' => $updated])); notices::add($inserted ? 'success' : 'notice', strtr(language::translate('success_insert_n_new_entries', 'Inserted %n new entries'), ['%n' => $inserted])); header('Location: '. document::link(WS_DIR_ADMIN, ['app' => $_GET['app'], 'doc' => $_GET['doc']])); exit; } catch (Exception $e) { notices::add('errors', $e->getMessage()); } } if (isset($_POST['export'])) { try { if (empty($_POST['collections'])) { throw new Exception(language::translate('error_must_select_at_least_one_collection', 'You must select at least one collection')); } if (empty($_POST['language_codes'])) { throw new Exception(language::translate('error_must_select_at_least_one_language', 'You must select at least one language')); } $_POST['language_codes'] = array_filter($_POST['language_codes']); $csv = []; if (in_array('translations', $_POST['collections'])) { $sql_union[] = "select 'translation' as entity, frontend, backend, code, date_updated, html,
                       ". implode(", ", array_map(function($language_code) { return "`text_". database::input($language_code) ."`"; }, $_POST['language_codes'])) ."
                       from ". DB_TABLE_PREFIX ."translations
                       where code not regexp '^(settings_group:|settings_key:|cm|job|om|ot|pm|sm)_'"; } if (in_array('modules', $_POST['collections'])) { $sql_union[] = "select 'translation' as entity, frontend, backend, code, date_updated, html,
                       ". implode(", ", array_map(function($language_code) { return "`text_". database::input($language_code) ."`"; }, $_POST['language_codes'])) ."
                       from ". DB_TABLE_PREFIX ."translations
                       where code regexp '^(cm|job|om|ot|pm|sm)_'"; } if (in_array('setting_groups', $_POST['collections'])) { $sql_union[] = "select 'translation' as entity, frontend, backend, code, date_updated, html,
                       ". implode(", ", array_map(function($language_code) { return "`text_". database::input($language_code) ."`"; }, $_POST['language_codes'])) ."
                       from ". DB_TABLE_PREFIX ."translations
                       where code regexp '^settings_group:'"; } if (in_array('settings', $_POST['collections'])) { $sql_union[] = "select 'translation' as entity, frontend, backend, code, date_updated, html,
                       ". implode(", ", array_map(function($language_code) { return "`text_". database::input($language_code) ."`"; }, $_POST['language_codes'])) ."
                       from ". DB_TABLE_PREFIX ."translations
                       where code regexp '^settings_key:'"; } $union_select = function($entity, $entity_table, $info_table, $id, $field) { return ( "select '$entity' as entity, '1' as frontend, '1' as backend, concat('[$entity', ':', e.id, ']$field') as code, '' as date_updated,
            coalesce(". implode(', ', array_map(function($language_code) use($field) { return "if($language_code.$field regexp '<', 1, null)"; }, $_POST['language_codes'])) .", 0) as html,
            ". implode(', ', array_map(function($language_code) use($field) { return "`". database::input($language_code) ."`.$field as `text_". database::input($language_code) ."`"; }, $_POST['language_codes'])) ."
          from ". DB_TABLE_PREFIX ."$entity_table e
          ". implode(PHP_EOL, array_map(function($language_code) use($info_table, $id) { return "left join ". DB_TABLE_PREFIX ."$info_table `". database::input($language_code) ."` on (`". database::input($language_code) ."`.$id = e.id and `". database::input($language_code) ."`.language_code = '$language_code')"; }, $_POST['language_codes'])) ); }; foreach ($collections as $collection) { if (in_array($collection['id'], $_POST['collections'])) { foreach ($collection['info_columns'] as $column) { $sql_union[] = $union_select($collection['entity'], $collection['entity_table'], $collection['info_table'], $collection['entity_column'], $column); } } } $translations_query = database::query( "select * from (
          ". implode(PHP_EOL . PHP_EOL . "union ", $sql_union) ."
        ) x
        where x.code != ''
        order by x.code;" ); while ($translation = database::fetch($translations_query)) { $row = ['code' => $translation['code']]; foreach ($_POST['language_codes'] as $language_code) { $row[$language_code] = $translation['text_'.$language_code]; } $csv[] = $row; } ob_clean(); if ($_POST['output'] == 'screen') { header('Content-Type: text/plain; charset='. $_POST['charset']); } else { header('Content-Type: application/csv; charset='. $_POST['charset']); header('Content-Disposition: attachment; filename=translations-'. implode('-', $_POST['language_codes']) .'.csv'); } switch($_POST['eol']) { case 'Linux': echo functions::csv_encode($csv, $_POST['delimiter'], $_POST['enclosure'], $_POST['escapechar'], $_POST['charset'], "\r"); break; case 'Mac': echo functions::csv_encode($csv, $_POST['delimiter'], $_POST['enclosure'], $_POST['escapechar'], $_POST['charset'], "\n"); break; case 'Win': default: echo functions::csv_encode($csv, $_POST['delimiter'], $_POST['enclosure'], $_POST['escapechar'], $_POST['charset'], "\r\n"); break; } exit; } catch (Exception $e) { notices::add('errors', $e->getMessage()); } } ?>
<div class="card card-app">
  <div class="card-header">
    <div class="card-title">
      <?php echo $app_icon; ?> <?php echo language::translate('title_csv_import_export', 'CSV Import/Export'); ?>
    </div>
  </div>

  <div class="card-body">
    <div class="row" style="max-width: 980px;">

      <div class="col-xl-6">
        <?php echo functions::form_draw_form_begin('import_form', 'post', '', true); ?>

          <fieldset>
            <legend><?php echo language::translate('title_import', 'Import'); ?></legend>

            <div class="form-group">
              <label><?php echo language::translate('title_csv_file', 'CSV File'); ?></label>
              <?php echo functions::form_draw_file_field('file'); ?></td>
            </div>

            <div class="row">
              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_delimiter', 'Delimiter'); ?></label>
                <?php echo functions::form_draw_select_field('delimiter', [[language::translate('title_auto', 'Auto') .' ('. language::translate('text_default', 'default') .')', ''], [','], [';'], ['TAB', "\t"], ['|']], true); ?>
              </div>

              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_enclosure', 'Enclosure'); ?></label>
                <?php echo functions::form_draw_select_field('enclosure', [['" ('. language::translate('text_default', 'default') .')', '"']], true); ?>
              </div>
            </div>

            <div class="row">
              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_escape_character', 'Escape Character'); ?></label>
                <?php echo functions::form_draw_select_field('escapechar', [['" ('. language::translate('text_default', 'default') .')', '"'], ['\\', '\\']], true); ?>
              </div>

              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_charset', 'Charset'); ?></label>
                <?php echo functions::form_draw_encodings_list('charset', !empty($_POST['charset']) ? true : 'UTF-8'); ?>
              </div>
            </div>

            <div class="form-group">
              <div class="checkbox">
                <label><?php echo functions::form_draw_checkbox('update', '1', true); ?> <?php echo language::translate('title_update_existing', 'Update Existing'); ?></label>
              </div>
              <div class="checkbox">
                <label><?php echo functions::form_draw_checkbox('insert', '1', true); ?> <?php echo language::translate('text_insert_new', 'Insert New'); ?></label>
              </div>
              <div class="checkbox">
                <label><?php echo functions::form_draw_checkbox('append', '1', isset($_POST['append']) ? true : '1'); ?> <?php echo language::translate('text_append_missing', 'Append Missing'); ?></label>
              </div>
            </div>

            <p><?php echo language::translate('description_scan_before_importing_translations', 'It is recommended to always scan your installation for unregistered translations before performing an import or export.'); ?></p>

            <?php echo functions::form_draw_button('import', language::translate('title_import', 'Import'), 'submit'); ?>
          </fieldset>

        <?php echo functions::form_draw_form_end(); ?>
      </div>

      <div class="col-xl-6">
        <?php echo functions::form_draw_form_begin('export_form', 'post'); ?>

          <fieldset>
            <legend><?php echo language::translate('title_export', 'Export'); ?></legend>

              <div class="form-group">
                <?php echo language::translate('title_collections', 'Collections'); ?>
                <?php echo functions::form_draw_select_multiple_field('collections[]', array_map(function($c) { return [$c['name'], $c['id']]; }, $collections), true); ?>
              </ul>

            <div class="form-group">
              <label><?php echo language::translate('title_languages', 'Languages'); ?></label>
              <?php echo functions::form_draw_languages_list('language_codes[]', true, true); ?></td>
            </div>

            <div class="row">
              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_delimiter', 'Delimiter'); ?></label>
                <?php echo functions::form_draw_select_field('delimiter', [[', ('. language::translate('text_default', 'default') .')', ','], [';'], ['TAB', "\t"], ['|']], true); ?>
              </div>

              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_enclosure', 'Enclosure'); ?></label>
                <?php echo functions::form_draw_select_field('enclosure', [['" ('. language::translate('text_default', 'default') .')', '"']], true); ?>
              </div>
            </div>

            <div class="row">
              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_escape_character', 'Escape Character'); ?></label>
                <?php echo functions::form_draw_select_field('escapechar', [['" ('. language::translate('text_default', 'default') .')', '"'], ['\\', '\\']], true); ?>
              </div>

              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_charset', 'Charset'); ?></label>
                <?php echo functions::form_draw_encodings_list('charset', !empty($_POST['charset']) ? true : 'UTF-8'); ?>
              </div>
            </div>

            <div class="row">
              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_line_ending', 'Line Ending'); ?></label>
                <?php echo functions::form_draw_select_field('eol', [['Win'], ['Mac'], ['Linux']], true); ?>
              </div>

              <div class="form-group col-md-6">
                <label><?php echo language::translate('title_output', 'Output'); ?></label>
                <?php echo functions::form_draw_select_field('output', [[language::translate('title_file', 'File'), 'file'], [language::translate('title_screen', 'Screen'), 'screen']], true); ?>
              </div>
            </div>

            <?php echo functions::form_draw_button('export', language::translate('title_export', 'Export'), 'submit'); ?>
          </fieldset>

        <?php echo functions::form_draw_form_end(); ?>
      </div>
    </div>
  </div>
</div>
