data.php

<?php
// Purpose        working with data (import/export, type in, ...)
// Author         Lutz Brueckner <irie@gmx.de>
// Copyright      (c) 2000-2006 by Lutz Brueckner,
//                published under the terms of the GNU General Public Licence v.2,
//                see file LICENCE for details

require './inc/script_start.inc.php';
require './inc/foreign_keys.inc.php';
require './inc/DataForm.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;
    }
}

require './inc/handle_watchtable.inc.php';

//
// handle foreign key lookup configuration
//
$customize_changed = false;
if (isset($_POST['dt_column_config_save'])) {
    $column = get_request_data('dt_column_config_column');
    $table = get_request_data('dt_column_config_table');
    $fk_column = get_request_data('dt_column_config_fk_column');
    if ($fk_column == '') {
        unset($s_cust['fk_lookups'][$table][$column]);
        if (empty($s_cust['fk_lookups'][$table])) {
            unset($s_cust['fk_lookups'][$table]);
        }
    } else {
        $s_cust['fk_lookups'][$table][$column] = $fk_column;
    }
    $customize_changed = true;
}

// 
// handle the customize cookie settings
// when 'dt_(enter|edit)_(insert|ready|save|cancel)'-button was pushed
if (array_filter(array_keys($_POST), function($a) {return preg_match("/dt_(enter|edit)_(insert|ready|save|cancel)/", $a);} )) {
    if ((isset($_POST['dt_config_fk_lookup'])  &&  $s_cust['enter']['fk_lookup'] == false)  ||
        (!isset($_POST['dt_config_fk_lookup'])  &&  $s_cust['enter']['fk_lookup'] == true)) {

        // 'foreign key lookup'-setting is changed
        $s_cust['enter']['fk_lookup'] = isset($_POST['dt_config_fk_lookup']);
        $customize_changed = true;
    }

    if (isset($_POST['dt_enter_insert'])  ||  isset($_POST['dt_enter_ready'])) {
        if ((isset($_POST['dt_config_more'])  &&  $s_cust['enter']['another_row'] == false)  ||
            (!isset($_POST['dt_config_more'])  &&  $s_cust['enter']['another_row'] == true)) {

            // 'insert another row'-setting is changed
            $s_cust['enter']['another_row'] = isset($_POST['dt_config_more']);
            $customize_changed = true;
        }
    } else {
        if ((isset($_POST['dt_config_as_new'])  &&  $s_cust['enter']['as_new'] == false)  ||
            (!isset($_POST['dt_config_as_new'])  &&  $s_cust['enter']['as_new'] == true)) {

            // 'foreign key lookup'-setting is changed
            $s_cust['enter']['as_new'] = isset($_POST['dt_config_as_new']);
            $customize_changed = true;
        }
    }
}
if ($customize_changed == true) {
    set_customize_cookie($s_cust);
}

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

//
// select on the dt_enter-panel was pushed
//
if (isset($_POST['dt_enter_select'])) {
    $s_enter_name = get_request_data('dt_enter_name');

    if (is_array($s_fields[$s_enter_name])) {
        $s_fields = get_table_defaults_sources($s_enter_name, $s_fields);
        $s_fields = get_table_computed_sources($s_enter_name, $s_fields);

        $s_enter_values = init_enter_values($s_fields[$s_enter_name]);
    }
}

//
// the Insert button on the dt_enter-panel was pushed
//
if (have_panel_permissions($s_login['user'], 'dt_enter', true)
&&  isset($_POST['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;
    }

    // needed for the have_active_trigger() check
    include_once './inc/triggers.inc.php';
    if ($s_triggers_valid == false) {
        $s_triggers = get_triggers($s_triggers);
        $s_triggers_valid = true;
    }

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

        if (isset($_FILES['dt_enter_file_'.$idx])  &&
            !empty($_FILES['dt_enter_file_'.$idx]['name'])) {
            $value = $_FILES['dt_enter_file_'.$idx];
            $s_enter_values[] = $value;
        } else {
            $value = get_request_data('dt_enter_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'];

        // take care for autoincrement fields implemented with before insert trigger and generator
        if ($idx == 0  &&  $value === ''  &&
            in_array($type, array('INTEGER', 'BIGINT', 'SMALLINT'))  &&
            isset($field['notnull'])  &&  $field['notnull'] == 'Yes'  &&
            have_active_trigger($s_triggers, $s_enter_name, 'before', 'insert')) {
            ++$idx;
            continue;
        }

        switch ($type) {
        case 'CHARACTER':
        case 'VARCHAR':
        case 'DATE':
        case 'TIME':
        case 'TIMESTAMP':
            $bindargs[] = empty($field['notnull'])  &&  empty($value) ? null : "$value";
            break;
        case 'BLOB' :
            // blob from file-upload
            if (is_array($value)  &&  !empty($value['name'])) {
                $bfname = $value['tmp_name'];
                $bfhandle = fopen($bfname, 'r') or die('cannot open file '.$bfname);
                $bstr = fbird_blob_import($dbhandle, $bfhandle);
                fclose($bfhandle);
                $bindargs[] = $bstr;
            }
            // blob from textarea
            elseif (!empty($value)) {
                $bhandle = fbird_blob_create($dbhandle) or die('cannot create blob: '.__FILE__.', '.__LINE__);
                fbird_blob_add($bhandle, $value);
                $bstr = fbird_blob_close($bhandle);
                $bindargs[] = $bstr;
            } else {
                $bindargs[] = null;
            }
            break;
        default:
            if ($value === '') {
                $value = null;
            }

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

    if (count($cols) > 0) {
        $fb_error = insert_row($s_enter_name, $cols, $bindargs);

        if (empty($fb_error)) {
            $s_watch_buffer = '';
            $s_enter_values = $s_cust['enter']['another_row'] == false
                ? array()
                : init_enter_values($s_fields[$s_enter_name]);
        }
    }
}

//
// the Ready button on the dt_enter-panel was pushed
//
if (isset($_POST['dt_enter_ready'])  ||
    (isset($_POST['dt_enter_insert'])  &&  $s_cust['enter']['another_row'] == false  &&  empty($fb_error))) {
    $s_enter_name = '';
    $s_enter_values = array();
}

//
// the Export button on the csv-panel was pushed
//
if (have_panel_permissions($s_login['user'], 'dt_export', true)) {
    include './inc/export.inc.php';

    if (empty($s_export)) {
        $s_export = get_export_defaults();
    }

    // set default values for general options and selected format options
    if (isset($_POST['dt_export_defaults'])) {
        $s_export = set_export_defaults($s_export['format'], $s_export);
    }

    if (isset($_POST['dt_export_doit'])) {
        $s_export = get_export_form_data($s_export);
        list($warning, $error) = check_export_form_data($s_export);

        if (empty($error)  &&  empty($warning)) {

            // display result in an iframe by iframe_content.php
            if ($s_export['target']['option'] == 'screen') {

                // remove pending dbstat-jobs from session
                $s_iframejobs = array_filter($s_iframejobs, function($a) {return '$a["job"]!="export";';});

                $iframekey_export = md5(uniqid('export'));
                $s_iframejobs[$iframekey_export] = array('job' => 'export',
                                                         'data' => $s_export,
                                                         'timestamp' => time(), );
            }

            // write result into a file
            else {
                $filename = export_filename($s_export);
                send_export_headers(get_export_mimetype($s_export['format']), $filename);

                export_data($s_export);

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

    $js_stack .= js_data_export();
}

//
// the Import button on the csv-panel was pushed
//
if (have_panel_permissions($s_login['user'], 'dt_import', true)
&&  isset($_POST['dt_import_doit'])) {

    // import empty values as NULL option
    $s_csv['import_null'] = isset($_POST['dt_import_null']) ? true : false;

    if ($_POST['dt_import_table'] == '') {
        $warning .= $WARNINGS['SELECT_TABLE_FIRST'];
    } elseif (isset($_FILES['dt_import_file']['name'])
        &&  $_FILES['dt_import_file']['name'] == '') {
        $warning .= $WARNINGS['SELECT_FILE_FIRST'];
    } else {
        $ifile = $_FILES['dt_import_file']['tmp_name'];
        $itable = $_POST['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[$itable] as $field) {
            if (($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 = fbird_prepare($sql) or fb_error(__FILE__, __LINE__, $sql);

        // string of variablenames needed for fbird_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[$itable] as $field) {
            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 NULL values
            if (!empty($null_fields)) {
                foreach ($null_fields as $idx) {
                    if ($data[$idx] == '') {
                        $data[$idx] = null;
                    }
                }
            }

            // handle blobs
            if (!empty($blob_fields)) {
                foreach ($blob_fields as $idx) {
                    if (empty($data[$idx])) {
                        $data[$idx] = null;
                    } else {
                        $blob_handle = fbird_blob_create($dbhandle) or fb_error(__FILE__, __LINE__);
                        fbird_blob_add($blob_handle, $data[$idx]);
                        $data[$idx] = fbird_blob_close($blob_handle) or fb_error(__FILE__, __LINE__);
                    }
                }
            }

            call_user_func_array('fbird_execute', array_merge(array($query), $data))
                or $fb_error = fb_error(__FILE__, __LINE__, $query);

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

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

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

$js_stack .= js_request_column_config_form();

//
// print out all the panels
//
$s_page = 'Data';
$panels = $s_data_panels;

require './inc/script_end.inc.php';

function init_enter_values($fields)
{
    $values = array();
    foreach ($fields as $field) {
        if (isset($field['default'])) {
            $values[] = $field['dsource'];
        } elseif (isset($field['comp'])) {
            $values[] = $field['csource'];
        } else {
            $values[] = '';
        }
    }

    return $values;
}