<?php
// File           data.php / ibWebAdmin
// Purpose        working with data (import/export, type in, ...)
// Author         Lutz Brueckner <irie@gmx.de>
// Copyright      (c) 2000, 2001, 2002, 2003, 2004 by Lutz Brueckner,
//                published under the terms of the GNU General Public Licence v.2,
//                see file LICENCE for details
// Created        <00/10/02 09:00:29 lb>
//
// $Id: data.php,v 1.26 2004/05/31 15:35:44 lbrueckner Exp $


require('./inc/script_start.inc.php');
require('./inc/handle_watchtable.inc.php');

if (isset($s_edit_where)  && count($s_edit_where) > 0) {
    include('./inc/handle_editdata.inc.php');
}

//
// setup $s_tables[] and $s_fields[] if necessary 
//
if ($s_connected && $s_tables_valid == FALSE) {
    include_once('./inc/get_tables.inc.php');
    if (get_tables($dbhandle)){
        $s_tables_valid = TRUE;
    }
}


//
// select on the dt_enter-panel was pushed
//
if (isset($HTTP_POST_VARS['dt_enter_select'])) {
    $s_enter_name = $HTTP_POST_VARS['dt_enter_name'];
    if (has_blob($s_enter_name)) {
        $version_info = get_php_version();
        if ((int)$version_info['VER'].$version_info['SUB'].$version_info['MIN'] < 404) {
            $message .= $MESSAGES['BLOB_BROKEN_INFO'];
        }
    }
}


//
// the Insert button on the dt_enter-panel was pushed
//
if (isset($HTTP_POST_VARS['dt_enter_insert'])) {

    // the origin types of domain-based columns are needed
    if (!$s_domains_valid) {

        include_once('./inc/domains.inc.php');

        $s_domains = get_domain_definitions($s_domains);
        $s_domains_valid = TRUE;
    }

    $idx      = 0;
    $bindargs = $cols = $s_enter_values = array();
    foreach($s_fields as $field) {
        if ($field['table'] != $s_enter_name  ||  isset($field['comp'])) {
            continue;
        }

        if (isset($HTTP_POST_FILES['dt_insert_field_'.$idx])) {
            $value = $HTTP_POST_FILES['dt_insert_field_'.$idx];
            $s_enter_values[] = $value['name'];
        } else {
             $value = get_request_data('dt_insert_field_'.$idx);
             $s_enter_values[] = $value;
        }

        // type of the field or the origin type of a domain-based field
        $type = !isset($field['domain']) ? $field['type'] : $s_domains[$field['type']]['type'];

        switch($type) {
        case 'CHARACTER':
        case 'VARCHAR':
            $value = str_replace("'", "''", $value);
        case 'DATE':
        case 'TIME':
        case 'TIMESTAMP':
            $cols[] = $field['name'];
            $bindargs[] = empty($field['notnull'])  &&  empty($value) ? NULL : "$value";
            break;
        case 'BLOB' :
            if (is_array($value)  &&  !empty($value['name'])) {
                $bfname = $value['tmp_name'];
                $bfhandle = fopen($bfname, 'r') or die('cannot open file '.$bfname);
                $bstr = ibase_blob_import($dbhandle, $bfhandle);
                fclose($bfhandle);

                $cols[] = $field['name'];
                $bindargs[] = $bstr;
            }
            break;
        default:
            if ($value == '') {
                $value = NULL;
            }

            $cols[] = $field['name'];
            $bindargs[] = $value;
        }
        $idx++;
    }

    if (count($cols) > 0) {
        $sql = 'INSERT INTO '.$s_enter_name.' ('.implode(', ', $cols).')'
             .' VALUES ('.substr(str_repeat('?, ', count($bindargs)), 0, -2).')';
        if (DEBUG) add_debug('$sql: '.$sql, __FILE__, __LINE__);

        $query = ibase_prepare($dbhandle, $sql) or ib_error(__FILE__, __LINE__, $sql);
        call_user_func_array('ibase_execute', array_merge(array($query), $bindargs))
            or $ib_error = ibase_errmsg();

        if (empty($ib_error)) {
            $s_enter_values = array();
            $s_watch_buffer = '';
        }
    }
}


//
// the Ready button on the dt_enter-panel was pushed
//
if (isset($HTTP_POST_VARS['dt_enter_ready'])) {
    $s_enter_name = '';
    $s_enter_values = array();
}


//
// the Export button on the csv-panel was pushed
//
if ($s_connected && isset($HTTP_POST_VARS['dt_export_doit'])) {
    if ($HTTP_POST_VARS['dt_export_table'] == '') {
        $warning .= $WARNINGS['SELECT_TABLE_FIRST'];
    } else {
        $efile  = $HTTP_POST_VARS['dt_export_file'];
        $etable = $HTTP_POST_VARS['dt_export_table'];

        // assemble the SELECT-query to get all values from the selected table,
        // but omit non-text blob fields
        $lsql = 'SELECT ';
        $col_type = array();
        $idx = 0;
        foreach ($s_fields as $field) {
            if ($field['table'] != $etable) {
                continue;
            }
            // only text-blobs are handled
            if ($field['type'] == 'BLOB'  &&  $field['stype'] != 1) {
                $warning .= $WARNINGS['CAN_NOT_EXPORT_BLOBS'];
                continue;
            }
            
            $col_type[$idx] = $field['type'];
            $lsql .= $field['name'].', ';
            $idx++;
        }
        $lsql = substr($lsql, 0, -2);
        $lsql .= ' FROM '.$etable;

        if (DEBUG) add_debug($lsql, __FILE__, __LINE__);

        $trans = ibase_trans(TRANS_READ, $dbhandle);
        $res = @ibase_query($trans, $lsql);
        if ($res === FALSE) {
            $ib_error = ibase_errmsg();
            $warning = '';
        }

        if (empty($ib_error)) {
            $n = ibase_num_fields($res);

            // send headers for user-download
            send_export_headers('application/octet-stream', $efile);

            // build one line for the csv file from every result object
            while ($row = @ibase_fetch_row($res)) {
                $line = '';
                for ($i=0; $i<$n; $i++){
                    $val = $row[$i];

                    switch ($col_type[$i]) {
                    case 'VARCHAR':
                    case 'CHARACTER':
                        $val = rtrim($val);
                        break;
                    case 'BLOB':
                        $blob_handle = ibase_blob_open($val);
                        $blob_info   = ibase_blob_info($val);
                        $blob_length = $blob_info[0];
                        $blob = ibase_blob_get($blob_handle, $blob_length);
                        ibase_blob_close($blob_handle);
                        $val = $blob;
                        break;
                    }

                    $val = str_replace('"', '""', $val);
                    $line .= '"'.$val.'",';
                }
                $line  = substr($line, 0, -1);
                $line .= "\n";

                // send line to client
                echo $line;
            }
            ibase_free_result($res);
            ibase_commit($trans);

            // if we don't stop the execution, the client will download 
            // all the html from the panels ...
            exit();
        }
    }
}


//
// the Import button on the csv-panel was pushed
//
if ($s_connected && isset($HTTP_POST_VARS['dt_import_doit'])) {

    // import empty values as NULL option
    $s_csv['import_null'] = isset($HTTP_POST_VARS['dt_import_null']) ? TRUE : FALSE;

    if ($HTTP_POST_VARS['dt_import_table'] == '') {
        $warning .= $WARNINGS['SELECT_TABLE_FIRST']; 
    }
    elseif (isset($HTTP_POST_FILES['dt_import_file']['name'])  
        &&  $HTTP_POST_FILES['dt_import_file']['name'] == '') {
        $warning .= $WARNINGS['SELECT_FILE_FIRST'];
    }
    else {
        $ifile = $HTTP_POST_FILES['dt_import_file']['tmp_name'];
        $itable = $HTTP_POST_VARS['dt_import_table'];
        $ihandle = fopen($ifile, 'r') or die('Error opening '.$ifile);

        // fill $columns[] with the $s_fields[] elements for $itable
        // but ignore blob fields and computed fields
        foreach($s_fields as $field) {
            if ($field['table'] != $itable
            ||  ($field['type'] == 'BLOB'  &&  $field['stype'] != 1)            // only text-blobs are handled
            ||  (isset($field['comp'])  &&  $field['comp'] == 'Yes')) {         // no computed columns please

                continue;
            }
            $col_names[] = $field['name'];
            $columns[] = $field;
        }

        $sql = 'INSERT INTO '.$itable. '('.implode(', ', $col_names).')'
                              .' VALUES ('.implode(', ',array_fill(0, count($col_names), '?')).')';
        $query = ibase_prepare($sql) or ib_error(__FILE__, __LINE__, $sql);

        // string of variablenames needed for ibase_execute()
        $var_string = '';
        foreach (array_keys($col_names) as $idx) {
            $var_string .= '$data['.$idx.'],';
        }
        $var_string = substr($var_string, 0, -1);

        // find indexes of blob fields and NULL-able fields
        $blob_fields = array();
        $null_fields = array();
        $idx = 0;
        foreach($s_fields as $field) {
            if ($field['table'] != $itable) {
                continue;
            }
            if ($field['type'] == 'BLOB') {
                $blob_fields[] = $idx;
            }

            if ($s_csv['import_null'] == TRUE &&
                (!isset($field['notnull'])  || empty($field['notnull']))) {
                $null_fields[] = $idx;
            }
            $idx++;
        }

        // assemble the INSERT-query for putting all values into the selected table,
        // but omit blob fields and computed fields
        $csv_cnt = 0;
        while ($data = fgetcsv ($ihandle, MAX_CSV_LINE)) {

            // handle blobs
            if (!empty($blob_fields)) {
                foreach ($blob_fields as $idx) {
                    $blob_handle = ibase_blob_create($dbhandle) or ib_error();
                    ibase_blob_add($blob_handle, $data[$idx]) or ib_error();
                    $data[$idx] = ibase_blob_close($blob_handle) or ib_error();
                }
            }

            // handle NULL values
            if (!empty($null_fields)) {
                foreach ($null_fields as $idx) {
                    if ($data[$idx] == '') {
                        $data[$idx] = NULL;
                    }
                }
            }

            call_user_func_array('ibase_execute', array_merge(array($query), $data))
                or $ib_error = ibase_errmsg();

            // an error occurs during the import
            if (!empty($ib_error)) {
                break;
            }
            $csv_cnt++;
        }
        fclose($ihandle);
        $sql = '';

        // cleanup the watchtable output buffer
        $s_watch_buffer = '';

        $message .= sprintf($MESSAGES['CSV_IMPORT_COUNT'], $csv_cnt, $itable);
    }
}


//
// print out all the panels
//
$active = 'Data';
$panels = &$s_data_panels;
require('./inc/script_end.inc.php');

?>