path: root/kbarcode/csvimportdlg.cpp

/***************************************************************************
                          csvimportdlg.cpp  -  description
                             -------------------
    begin                : Don Aug 21 2003
    copyright            : (C) 2003 by Dominik Seichter
    email                : domseichter@web.de
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program 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.                                   *
 *                                                                         *
 ***************************************************************************/

#include "csvimportdlg.h"
#include "printersettings.h"
#include "sqltables.h"
#include "encodingcombo.h"

// TQt includes
#include <tqcheckbox.h>
#include <tqcursor.h>
#include <tqfile.h>
#include <tqframe.h>
#include <tqgroupbox.h>
#include <tqhbuttongroup.h>
#include <tqheader.h>
#include <tqlabel.h>
#include <tqlayout.h>
#include <tqlistbox.h>
#include <tqprogressdialog.h>
#include <tqsqlquery.h>
#include <tqtable.h>
#include <tqradiobutton.h>
#include <tqtextstream.h>
#include <tqvbox.h>

// KDE includes
#include <kapplication.h>
#include <kcombobox.h>
#include <klineedit.h>
#include <klocale.h>
#include <kmessagebox.h>
#include <knuminput.h>
#include <kpushbutton.h>
#include <kurlrequester.h>
#include "csvfile.h"

// import from labelprinter.cpp
extern TQString removeQuote( TQString text, TQString quote );

const char* NOFIELD = "<NONE>";

CSVImportDlg::CSVImportDlg(TQWidget *parent, const char *name )
    : KDialogBase( KDialogBase::Tabbed, i18n("Import"),
      KDialogBase::Ok | KDialogBase::Close, KDialogBase::Ok, parent,name,false,true)
{
    setButtonOKText( i18n("&Import"), i18n("Import the selected file into your tables.") );
    
    createPage1();
    createPage2();

    connect( requester, TQT_SIGNAL( textChanged( const TQString & ) ), this, TQT_SLOT( settingsChanged() ) );
    connect( buttonSet, TQT_SIGNAL( clicked() ), this, TQT_SLOT( setCol() ) );
    connect( comboSQL, TQT_SIGNAL( activated( int ) ), this, TQT_SLOT( updateFields() ) );
    connect( databaseName, TQT_SIGNAL( textChanged( const TQString & ) ), this, TQT_SLOT( updateFields() ) );
    connect( comboEncoding, TQT_SIGNAL( activated( int ) ), this, TQT_SLOT( settingsChanged() ) );
    connect( table->horizontalHeader(), TQT_SIGNAL( clicked( int ) ), this, TQT_SLOT( updateCol( int ) ) );
    connect( radioCSVFile, TQT_SIGNAL( clicked() ), this, TQT_SLOT( enableControls() ) );
    connect( radioFixedFile, TQT_SIGNAL( clicked() ), this, TQT_SLOT( enableControls() ) );
    connect( buttonAdd, TQT_SIGNAL( clicked() ), this, TQT_SLOT( addWidth() ) );
    connect( buttonRemove,TQT_SIGNAL( clicked() ), this, TQT_SLOT( removeWidth() ) );
    connect( comment, TQT_SIGNAL( textChanged( const TQString & ) ), this, TQT_SLOT( settingsChanged() ) );
    connect( quote, TQT_SIGNAL( textChanged( const TQString & ) ), this, TQT_SLOT( settingsChanged() ) );
    connect( separator, TQT_SIGNAL( textChanged( const TQString & ) ), this, TQT_SLOT( settingsChanged() ) );
    connect( checkLoadAll, TQT_SIGNAL( clicked() ), this, TQT_SLOT( enableControls() ) );

    updateFields();
    enableControls();
        
    show();
}

CSVImportDlg::~CSVImportDlg()
{
}

void CSVImportDlg::createPage1()
{
    TQFrame* box = addPage( i18n("&Import Data") );
    TQVBoxLayout* tqlayout = new TQVBoxLayout( box, 6, 6 );
    TQGridLayout* grid = new TQGridLayout( 2 );

    requester = new KURLRequester( box );
    comboEncoding = new EncodingCombo( box );
    comboSQL = new KComboBox( false, box );
    comboSQL->insertItem( TABLE_BASIC );
    comboSQL->insertItem( TABLE_CUSTOMER );
    comboSQL->insertItem( TABLE_CUSTOMER_TEXT );
    comboSQL->insertItem( TABLE_LABEL_DEF );
    comboSQL->insertItem( i18n("Other table...") );

    databaseName = new KLineEdit( box );
    checkLoadAll = new TQCheckBox( i18n("&Load complete file into preview"), box );
    spinLoadOnly = new KIntNumInput( box );
    spinLoadOnly->setLabel( i18n("Load only a number of datasets:"), AlignLeft | AlignVCenter );
    spinLoadOnly->setRange( 0, 10000, 1, false );
    checkLoadAll->setChecked( true );

    table = new TQTable( box );
    table->setReadOnly( true );

    frame = new TQFrame( box );
    TQHBoxLayout* tqlayout2 = new TQHBoxLayout( frame, 6, 6 );
    
    spinCol = new KIntNumInput( frame );
    spinCol->setLabel( i18n("Column:"), AlignLeft | AlignVCenter );
    spinCol->setRange( 0, 0, 0, false );

    comboField = new KComboBox( false, frame );
    buttonSet = new KPushButton( i18n("Set"), frame );
    
    tqlayout2->addWidget( spinCol );
    tqlayout2->addWidget( new TQLabel( i18n("Database field to use for this column:"), frame ) );
    tqlayout2->addWidget( comboField );
    tqlayout2->addWidget( buttonSet );
    
    grid->addWidget( new TQLabel( i18n("File to import:"), box ), 0, 0 );
    grid->addWidget( requester, 0, 1 );
    grid->addWidget( new TQLabel( i18n("Encoding:"), box ), 1, 0 );
    grid->addWidget( comboEncoding, 1, 1 );
    grid->addWidget( new TQLabel( i18n("Import into table:"), box ), 2, 0 );
    grid->addWidget( comboSQL, 2, 1 );
    grid->addWidget( new TQLabel( i18n("Table Name:"), box ), 3, 0 );
    grid->addWidget( databaseName, 3, 1 );
    grid->addWidget( checkLoadAll, 4, 0 );
    grid->addWidget( spinLoadOnly, 4, 1 );

    tqlayout->addLayout( grid );
    tqlayout->addWidget( table );
    tqlayout->setStretchFactor( table, 2 );
    tqlayout->addWidget( frame );
}

void CSVImportDlg::createPage2()
{
    labelprinterdata* lb = PrinterSettings::getInstance()->getData();
    TQFrame* mainBox = addPage( i18n("&Import Settings") );
    TQVBoxLayout* tqlayout = new TQVBoxLayout( mainBox, 6, 6 );
    TQSpacerItem* spacer1 = new TQSpacerItem( 20, 20, TQSizePolicy::Minimum, TQSizePolicy::Expanding );
    TQSpacerItem* spacer2 = new TQSpacerItem( 20, 20, TQSizePolicy::Minimum, TQSizePolicy::Expanding );

    TQHButtonGroup* buttonGroup = new TQHButtonGroup( i18n("File Format:"), mainBox );
    radioCSVFile = new TQRadioButton( i18n("&CSV File"), buttonGroup );
    radioFixedFile = new TQRadioButton( i18n("File with &fixed field width"), buttonGroup );

    TQHBox* hboxFrame = new TQHBox( mainBox );

    groupCSV = new TQGroupBox( i18n("CSV File"), hboxFrame );
    groupFixed = new TQGroupBox( i18n("Fixed Field Width File"), hboxFrame );

    groupCSV->setColumnLayout(0, Qt::Vertical );
    groupCSV->tqlayout()->setSpacing( 6 );
    groupCSV->tqlayout()->setMargin( 11 );

    TQVBoxLayout* vbox = new TQVBoxLayout( groupCSV->tqlayout() );
    TQGridLayout* grid = new TQGridLayout( 2, 2 );
    grid->setSpacing( 6 );
    grid->setMargin( 11 );
    
    TQLabel* label = new TQLabel( groupCSV );
    label->setText( i18n("Comment:") );
    grid->addWidget( label, 0, 0 );

    comment = new KLineEdit( lb->comment, groupCSV );
    grid->addWidget( comment, 0, 1 );

    label = new TQLabel( groupCSV );
    label->setText( i18n( "Separator:" ) );
    grid->addWidget( label, 1, 0 );

    separator = new KLineEdit( lb->separator, groupCSV );
    grid->addWidget( separator, 1, 1 );

    label = new TQLabel( groupCSV );
    label->setText( i18n("Quote Character:") );
    grid->addWidget( label, 2, 0 );

    quote  = new KLineEdit( lb->quote, groupCSV );
    grid->addWidget( quote, 2, 1 );

    vbox->addLayout( grid );
    vbox->addItem( spacer1 );

    groupFixed->setColumnLayout(0, Qt::Horizontal );
    groupFixed->tqlayout()->setSpacing( 6 );
    groupFixed->tqlayout()->setMargin( 11 );
    TQHBoxLayout* groupFixedLayout = new TQHBoxLayout( groupFixed->tqlayout() );
    groupFixedLayout->tqsetAlignment( TQt::AlignTop );

    listWidth = new KListBox( groupFixed );

    buttonAdd = new KPushButton( groupFixed );
    buttonAdd->setText( i18n( "&Add Field" ) );

    buttonRemove = new KPushButton( groupFixed );
    buttonRemove->setText( i18n( "&Remove Field" ) );

    spinNumber = new KIntNumInput( groupFixed );
    spinNumber->setMinValue( 0 );
    spinNumber->setValue( 1 );
    spinNumber->setFocus();

    TQVBoxLayout* tqlayout2 = new TQVBoxLayout( 0, 6, 6 );
    tqlayout2->addWidget( buttonAdd );
    tqlayout2->addWidget( buttonRemove );
    tqlayout2->addWidget( spinNumber );
    tqlayout2->addItem( spacer2 );
    
    groupFixedLayout->addWidget( listWidth );
    groupFixedLayout->addLayout( tqlayout2 );

    tqlayout->addWidget( buttonGroup );
    tqlayout->addWidget( hboxFrame );

    radioCSVFile->setChecked( true );
}

void CSVImportDlg::settingsChanged()
{
    CSVFile file( requester->url() );
    TQStringList list;

    int i = 0;
    unsigned int z;

    initCsvFile( &file );

    table->setNumCols( 0 );
    table->setNumRows( 0 );

    if( !file.isValid() )
        return;
     
    while( !file.isEof() )
    {
        list = file.readNextLine();

        if( table->numCols() < (int)list.count() )
            table->setNumCols( list.count() );
            
        if( table->numRows() <= i )
            // add 100 rows to get a reasonable speed
            table->setNumRows( i + 100 );

        for( z = 0; z < list.count(); z++ )
            table->setText( i, z, list[z] );
            
        if( !checkLoadAll->isChecked() && i > spinLoadOnly->value() ) 
            break;

        i++;
    }
    
    table->setNumRows( i );
    spinCol->setRange( 1, table->numCols(), 1, false );
       
    enableControls();
}

void CSVImportDlg::setCol()
{
    TQString text = comboField->currentText();
    int v = spinCol->value() - 1;
    if( text == NOFIELD )
        table->horizontalHeader()->setLabel( v, TQString::number( v + 1 ) );
    else {
        for( int i = 0; i < table->horizontalHeader()->count(); i++ )
            if( table->horizontalHeader()->label( i ) == text )
                table->horizontalHeader()->setLabel( i, TQString::number( i + 1 ) );
                
        table->horizontalHeader()->setLabel( v, text );
    }
}

TQString CSVImportDlg::getDatabaseName() 
{
    bool b = comboSQL->currentItem() == (comboSQL->count()-1);

    databaseName->setEnabled( b );
    return b ? databaseName->text() : comboSQL->currentText();
}

void CSVImportDlg::updateFields()
{
    // also enables databaseName if necessary
    TQString name = getDatabaseName();

    comboField->clear();
    comboField->insertItem( NOFIELD );
    TQSqlQuery query( SqlTables::getInstance()->driver()->showColumns( name ) );
    while( query.next() )
        comboField->insertItem( query.value( 0 ).toString() );

    for( int i = 0; i < table->horizontalHeader()->count(); i++ )
        table->horizontalHeader()->setLabel( i, TQString::number( i + 1 ) );
}

void CSVImportDlg::enableControls()
{
    bool b = table->numRows() && table->numCols();
    
    groupCSV->setEnabled( radioCSVFile->isChecked() );
    groupFixed->setEnabled( radioFixedFile->isChecked() );

    spinLoadOnly->setEnabled( !checkLoadAll->isChecked() );

    enableButtonOK( b );
    frame->setEnabled( b );
}

void CSVImportDlg::updateCol( int c )
{
    spinCol->setValue( ++c );
}

void CSVImportDlg::accept()
{
    CSVFile file( requester->url() );
    TQHeader* h = table->horizontalHeader();
    TQValueList<int> headers;
    TQStringList list;
    TQString name = getDatabaseName();
    int i = 0;

    TQString q = "INSERT INTO " + name + " (";
    for( int c = 0; c < table->horizontalHeader()->count(); c++ ) {
        bool ok = true;
        h->label( c ).toInt( &ok );
        if( !ok ) {
            q = q + table->horizontalHeader()->label( c ) + ",";
            headers << c;
        }
    }

    // remove last ","
    if( q.right( 1 ) == "," )
        q = q.left( q.length() - 1 );

    q = q + ") VALUES (";

    initCsvFile( &file );
    if( !file.isValid() )
        KMessageBox::error( this, i18n("Cannot load data from the file:") + requester->url() );

    

    KApplication::setOverrideCursor( TQCursor( TQt::WaitCursor) );
    while( !file.isEof() )
    {
        list = file.readNextLine();

        TQString line = q;
        for( unsigned int c = 0; c < headers.count(); c++ )
            line.append( "'" + list[ headers[c] ] + "'" + "," );

        // remove last ","
        if( line.right( 1 ) == "," )
            line = line.left( line.length() - 1 );

        line = line + ");";

        TQSqlQuery query;
        if( !query.exec( line ) )
            qDebug( i18n("Could not import the following line:") + line );
            //KMessageBox::error( this, i18n("Could not import the following line:") + line );
    }

    KApplication::restoreOverrideCursor();
    KMessageBox::information( this, i18n("Data was imported successfully.") );
    KDialogBase::accept();
}

void CSVImportDlg::addWidth()
{
    listWidth->insertItem( TQString("%1").tqarg(spinNumber->value()), -1 );
    settingsChanged();
}

void CSVImportDlg::removeWidth()
{
    unsigned int i = 0;
    do {
        if(listWidth->isSelected( i )) {
            listWidth->removeItem( i );
            listWidth->setSelected( i-1, true );
            return;
        } else
            i++;
    } while( i < listWidth->count() );
    settingsChanged();
}

TQValueList<int> CSVImportDlg::getFieldWidth()
{
    TQValueList<int> list;

    for( unsigned int i=0;i<listWidth->count();i++ ) 
        list << listWidth->text( i ).toInt();

    return list;
}

void CSVImportDlg::initCsvFile( CSVFile* file )
{
    TQValueList<int> width = getFieldWidth();

    file->setEncoding( comboEncoding->currentText() );
    file->setCSVFile( radioCSVFile->isChecked() );
    file->setComment( comment->text() );
    file->setSeparator( separator->text() );
    file->setQuote( quote->text() );
    file->setFieldWidth( width );
}


#include "csvimportdlg.moc"