<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2008-12-22
 * Modified    : 2010-11-24
 * For LOVD    : 2.0-30
 *
 * Access      : Administrator and managers.
 * Purpose     : Import previously downloaded custom columns file into this LOVD
 *               installation.
 *
 * Copyright   : 2004-2009 Leiden University Medical Center; http://www.LUMC.nl/
 * Programmers : Ing. Ivo F.A.C. Fokkema <I.F.A.C.Fokkema@LUMC.nl>
 *               Ir. Gerard C.P. Schaafsma <G.C.P.Schaafsma@LUMC.nl>
 * Last edited : Ing. Ivo F.A.C. Fokkema <I.F.A.C.Fokkema@LUMC.nl>
 *
 *
 * This file is part of LOVD.
 *
 * LOVD is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * LOVD is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with LOVD; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 *************/

define('ROOT_PATH', './');
require ROOT_PATH . 'inc-init.php';

if (HAS_AUTH) {
    // If authorized, check for updates.
    require ROOT_PATH . 'inc-upgrade.php';
}

// Require manager clearance.
lovd_requireAUTH(LEVEL_MANAGER);

require ROOT_PATH . 'inc-lib-form.php';
require ROOT_PATH . 'inc-lib-columns.php';





$nMaxSize = 2097152; // 2 MB.

if (isset($_GET['sent'])) {
    lovd_errorClean();

    // No file...
    if ($_FILES['upload_file']['error'] > 0 && $_FILES['upload_file']['error'] < 4) {
        lovd_errorAdd('There was a problem with the file transfer. Please try again. The file cannot be larger than ' . round($nMaxSize/pow(1024, 2), 1) . ' MB.');

    } else if ($_FILES['upload_file']['error'] == 4 || !$_FILES['upload_file']['size']) {
        lovd_errorAdd('Please select a file to upload.');

    } else if ($_FILES['upload_file']['size'] > $nMaxSize) {
        lovd_errorAdd('The file cannot be larger than ' . round($nMaxSize/pow(1024, 2), 1) . ' MB.');

    } elseif ($_FILES['upload_file']['error']) {
        // Various errors available from 4.3.0 or later.
        lovd_errorAdd('There was an unknown problem with receiving the file properly, possibly because of the current server settings. If the problem persists, contact the database administrator.');
    }

    if (!lovd_error()) {
        $fInput = @fopen($_FILES['upload_file']['tmp_name'], 'r');
        if (!$fInput) {
            lovd_errorAdd('Cannot open file after it was received by the server.');
        }
    }



    if (!lovd_error()) {
        // Start reading out text file and gathering data.
        $nLine = 1;

        // On purpose, the HGVS column has been left out. This way, all imported columns will be regarded non-HGVS. Otherwise, they can never be removed again.
        // On purpose, the created* and edited* fields have been left out. They will be reset to the default values.
        $aLOVDCols      = array('colid', 'standard', 'mandatory', 'head_column', 'description_form', 'description_legend_short', 'description_legend_full', 'mysql_type', 'form_type', 'select_options', 'preg_pattern', 'public', 'public_form', 'allow_count_all');
        $aColsMandatory = array('colid', 'head_column', 'description_legend_short', 'description_legend_full', 'mysql_type', 'form_type', 'select_options');

        // Read version information or column names from file.
        $sLine = @fgets($fInput, 4096);
        $sLine = rtrim($sLine, "\r\n");

        // Check if the first line with LOVD-version etc. is present
        if (!preg_match('/^### LOVD-version ([0-9]{4}\-[0-9]{2}[a-z0-9]) ###/', ltrim($sLine, '"'), $aRegs)) {
            lovd_errorAdd('The first line of the imported file should contain the LOVD header! (### LOVD-version ... etc.)');
        } else {
            // Version number.
            $sFormatVersion = $aRegs[1];

            // 2009-01-27; 2.0-15; Check for data type.
            if (preg_match('/^### LOVD-version [0-9a-z-]+ ### ([A-Za-z\/]+) ###/', ltrim($sLine, '"'), $aRegs)) {
                // We received a datatype. It should be Columns.
                if ($aRegs[1] != 'Columns') {
                    lovd_errorAdd('The header of this file indicates it contains "' . $aRegs[1] . '". Expected "Columns".');
                    if ($aRegs[1] == 'Variants/Patients') {
                        lovd_errorAdd('Maybe you meant to import your file <A href="' . ROOT_PATH . 'config_import.php">here</A>?');
                    }
                }
            } else {
                lovd_errorAdd('The header of this file does not indicate a data type. Expected "Columns".');
            }

            // Read the next line for the column names.
            $sLine = @fgets($fInput, 4096);
            $sLine = rtrim($sLine, "\r\n");
        }

        // Column handling.
        $aColumns = explode("\t", $sLine);
        foreach ($aColumns as $nCol => $sCol) {
            $sCol = trim($sCol, '"{ }');
            // Unknown cols? Ignore.
            if (!in_array($sCol, $aLOVDCols)) {
                unset($aColumns[$nCol]);
            } else {
                $aColumns[$nCol] = $sCol;
            }
        }

        // LOVD mandatory columns really *must* be available.
        foreach ($aColsMandatory as $sCol) {
            if (!in_array($sCol, $aColumns)) {
                lovd_errorAdd('Cannot import this file, as it lacks the crucial "' . $sCol . '" columnn.');
            }
        }



        if (!lovd_error()) {
            // Loop lines.
            $nLine = 2; // Header = line 2.
            $nMaxErrors = 25;
            // Increase execution time to script to help import bigger files.
            if ((int) ini_get('max_execution_time') < 60) {
                set_time_limit(60);
            }

            // Save us a lot of queries, by saving this first.
            $aIDsCols = array();
            $qCols = mysql_query('SELECT colid FROM ' . TABLE_COLS);
            while ($r = mysql_fetch_row($qCols)) {
                $aIDsCols[] = $r[0];
            }

            $aCols = array();
            $nTotal = 0;



            // Read rest of the file.
            // 2009-06-26; 2.0-19; Read up to 20 KB. 4 KB was not enough for large columns.
            // 2010-11-24; 2.0-23; Totally empty lines made the while-loop quit. Moving the rtrim() elsewhere.
            while (!feof($fInput) && $sLine = fgets($fInput, 20480)) {
                $nLine ++;
                if (!trim($sLine)) {
                    // Empty line.
                    continue;
                } else {
                    // 2010-11-24; 2.0-23; Totally empty lines made the while-loop quit. Moving the rtrim() here.
                    $sLine = rtrim($sLine, "\r\n");
                }
                $aLine = explode("\t", $sLine);
                $aLineCol = array();

                // Clean first.
                foreach ($aLine as $nKey => $sVal) {
                    // We need an extra loop, because we're going to access values of $aLine directly, and then all values should be cleaned.
                    if (substr($sVal, 0, 1) == '"' && substr($sVal, -1) == '"') {
                        // 2010-09-01; 2.0-29; Fixed bug; by including the fix below, this change here was undone. Apparently this wasn't tested properly.
                        $sVal = substr($sVal, 1, -1);
                    }

                    // 2010-06-24; 2.0-27; Fix problem with not properly quoted data, by unquoting what we have here.
                    // This is now like how config_import.php is also doing it, since last release.
                    // Data from text files is not actually quoted, but when downloading data, LOVD quotes the data.
                    // Since we're now using mysql_real_escape_string() later, unquote the data from the text file!
                    $aLine[$nKey] = stripslashes($sVal);
                }

                // Check if column already exists in the database.
                if (in_array($aLine[array_search('colid', $aColumns)], $aIDsCols)) {
                    // Column already exists; skip.
                    continue;
                }

                foreach ($aLine as $nKey => $sVal) {
                    // Loop data, and verify it.
                    if (!isset($aColumns[$nKey])) {
                        // We're ignoring this column.
                        continue;
                    }
                    $sCol = $aColumns[$nKey];

                    // Mandatory fields.
                    if (in_array($sCol, $aColsMandatory) && !$sVal) {
                        // Mandatory col not filled in. Select_options is only allowed empty for non-selectionlist items.
                        if ($sCol == 'select_options') {
                            if (substr_count($aLine[array_search('form_type', $aColumns)], '|select')) {
                                lovd_errorAdd('Error in line ' . $nLine . ': missing value in mandatory "' . $sCol . '" column.');
                            }
                        } else {
                            lovd_errorAdd('Error in line ' . $nLine . ': missing value in mandatory "' . $sCol . '" column.');
                        }
                    }

                    // Field lengths.
                    $nMaxLength = lovd_getColumnLength(TABLE_COLS, $sCol);
                    $nLength = strlen($sVal);
                    if ($nMaxLength < $nLength) {
                        lovd_errorAdd('Error in line ' . $nLine . ': value of ' . $nLength . ' characters is too long for the "' . $sCol . '" column, which allows ' . $nMaxLength . ' characters.');
                    }

                    if ($sVal) {
                        // 2010-03-31; 2.0-26; Data actually needs to be checked, like when creating a column normally through the form.
                        switch ($sCol) {
                            case 'colid':
                                // ColID format.
                                if (!preg_match('/^[A-Za-z0-9_]+(\/[A-Za-z0-9_]+)*$/', $sVal)) {
                                    lovd_errorAdd('Error in line ' . $nLine . ': the column ID is not of the correct format. It can contain only letters, numbers and underscores. Subcategories must be devided by a slash (/).');
                                }
                                break;
                            case 'mysql_type':
                                // MySQL type format.
                                // 2009-02-16; 2.0-16; Allow for input of default values using "DEFAULT ..."
                                // 2009-06-11; 2.0-19; Added DEC, DATE and DATETIME types.
                                if (!preg_match('/^(TEXT|VARCHAR\([0-9]{1,3}\)|DATE(TIME)?|((TINY|SMALL|MEDIUM|BIG)?INT\([0-9]{1,2}\)|DEC\([0-9]{1,2}\,[0-9]{1,2}\))( UNSIGNED)?)( DEFAULT ([0-9]+|"[^"]+"))?$/i', $sVal)) {
                                    lovd_errorAdd('Error in line ' . $nLine . ': the MySQL data type is not recognized. Please use the data type wizard to generate a proper MySQL data type.');
                                }
                                break;
                        }

                        // Field types.
                        $sType = lovd_getColumnType(TABLE_COLS, $sCol);
                        switch ($sType) {
                            case 'INT':
                                if (!preg_match('/^[0-9]+$/', $sVal)) {
                                    lovd_errorAdd('Error in line ' . $nLine . ': value "' . htmlspecialchars($sVal) . '" in "' . $sCol . '" column is not an integer.');
                                }
                                break;
                            case 'DEC':
                                if (!is_numeric($sVal)) {
                                    lovd_errorAdd('Error in line ' . $nLine . ': value "' . htmlspecialchars($sVal) . '" in "' . $sCol . '" column is not numeric.');
                                }
                                break;
                            case 'DATETIME':
                                if (!preg_match('/^[0-9]{4}[.\/-][0-9]{2}[.\/-][0-9]{2}( [0-9]{2}\:[0-9]{2}\:[0-9]{2})?$/', $sVal)) {
                                    lovd_errorAdd('Error in line ' . $nLine . ': value "' . htmlspecialchars($sVal) . '" in "' . $sCol . '" column is not a date I understand.');
                                }
                                break;
                            case 'DATE':
                                if (!lovd_matchDate($sVal)) {
                                    lovd_errorAdd('Error in line ' . $nLine . ': value "' . htmlspecialchars($sVal) . '" in "' . $sCol . '" column is not a date I understand.');
                                }
                                break;
                        }
                    }

                    // Store column value.
                    $aLineCol[$sCol] = $sVal;

                    if (substr_count($_ERROR, "\n") >= $nMaxErrors) {
                        // Too many errors. Quit now.
                        lovd_errorAdd('Too many errors, stopping file processing.');
                        break 2;
                    }
                }

                // Auto-fill values with useful defaults!
                // created_by.
                if (empty($aLineCol['created_by'])) {
                    $aLineCol['created_by'] = $_AUTH['userid'];
                }

                // created_date.
                if (empty($aLineCol['created_date'])) {
                    $aLineCol['created_date'] = date('Y-m-d H:i:s');
                }

                // Store information.
                $aCols[$aLineCol['colid']] = $aLineCol;
                $nTotal ++;
            }
            fclose($fInput);



            if (!lovd_error()) {
                // Start importing from the memory!
                require ROOT_PATH . 'inc-top.php';
                lovd_printHeader('setup_columns_import', 'LOVD Setup - Import custom columns');

                if (!$nTotal) {
                    print('      No entries found that need to be imported in the database. Either your uploaded file contains no custom columns, or all columns are already in the database.<BR>' . "\n\n");
                    require ROOT_PATH . 'inc-bot.php';
                    exit;
                }

                // Progress bar.
                print('      Input file verified. Importing entries...<BR><BR>' . "\n" .
                      '      <TABLE border="0" cellpadding="0" cellspacing="0" class="S11" width="250">' . "\n" .
                      '        <TR style="height : 15px;">' . "\n" .
                      '          <TD width="100%" style="border : 1px solid black;">' . "\n" .
                      '            <IMG src="' . ROOT_PATH . 'gfx/trans.png" alt="" title="" width="0%" height="15" id="lovd_progress_import" style="background : #AFC8FA;"></TD>' . "\n" .
                      '          <TD align="right" width="25"><INPUT type="text" id="lovd_progress_import_value" size="3" value="0%" style="border : 0px; margin : 0px; padding : 0px; text-align : right;"></TD></TR></TABLE><BR>' . "\n\n" .
                      '      <SCRIPT type="text/javascript">var progressImport = document.getElementById(\'lovd_progress_import\'); var progressImportValue = document.getElementById(\'lovd_progress_import_value\');</SCRIPT>' . "\n");

                // Import...
                $nProgressPrev = '0%';
                $n = 0;



                // Import columns.
                foreach ($aCols as $nKey => $aVal) {
                    $n++;
                    $sQ = 'INSERT INTO ' . TABLE_COLS . ' (';
                    $sCols = '';
                    $sVals = '';
                    foreach ($aVal as $sCol => $sVal) {
                        $sCols .= ($sCols? ', ' : '') . '`' . $sCol . '`';
                        // Quick fix; change edited* fields "" to NULL.
                        // 2010-06-24; 2.0-27; Added mysql_real_escape_string() because we're unquoting data now.
                        $sVals .= ($sVals? ', ' : '') . (substr($sCol, 0, 7) == 'edited_' && !$sVal? 'NULL' : '"' . mysql_real_escape_string($sVal) . '"');
                    }
                    $sQ .= $sCols . ') VALUES (' . $sVals . ')';
                    $q = mysql_query($sQ);
                    if (!$q) {
                        lovd_dbFout('ColumnImport', $sQ, mysql_error());
                    }

                    lovd_writeLog('MySQL:Event', 'ColumnImport', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully imported column ' . $aVal['colid']);

                    $nProgress = round(($n*100)/$nTotal) . '%';
                    if ($nProgress != $nProgressPrev) {
                        print('      <SCRIPT type="text/javascript">progressImport.style.width = \'' . $nProgress . '\'; progressImportValue.value = \'' . $nProgress . '\';</SCRIPT>' . "\n");
                    }

                    flush();
                    usleep(1000);
                    $nProgressPrev = $nProgress;
                }



                // Done!!!
                print('      Done importing!<BR>' . "\n\n" .
                      '      <SCRIPT type="text/javascript">' . "\n" .
                      '        <!--' . "\n" .
                      '        setTimeout("window.location.href = \'' . PROTOCOL . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/') . '/setup.php' . lovd_showSID() . '\'", 3000);' . "\n" .
                      '        // -->' . "\n" .
                      '      </SCRIPT>' . "\n\n");

                require ROOT_PATH . 'inc-bot.php';
                exit;
            }
        }
        @fclose($fInput);
    }
}



require ROOT_PATH . 'inc-top.php';
lovd_printHeader('setup_columns_import', 'LOVD Setup - Import custom columns');

print('      Please select the file containing the custom column data, that you wish to import into this LOVD installation.<BR>' . "\n" .
      '      <SPAN class="S11">(<A href="' . ROOT_PATH . 'docs/lovd_setup/custom_columns/importing_custom_columns_from_text_files.php">Important information about the file format</A>)</SPAN><BR>' . "\n" .
      '      <BR>' . "\n\n");

lovd_errorPrint();

print('      <FORM action="' . $_SERVER['PHP_SELF'] . '?sent" method="post" enctype="multipart/form-data">' . "\n" .
      '        <INPUT type="hidden" name="MAX_FILE_SIZE" value="' . $nMaxSize . '">' . "\n" .
      '        <INPUT type="file" name="upload_file" size="30"><BR>' . "\n" .
      '        <INPUT type="submit" value="Import custom columns">' . "\n" .
      '      </FORM><BR>' . "\n\n");

require ROOT_PATH . 'inc-bot.php';
?>
