00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022 #include "kspread_cell.h"
00023 #include "kspread_dlg_database.h"
00024 #include "kspread_doc.h"
00025 #include "kspread_sheet.h"
00026 #include "kspread_util.h"
00027 #include "kspread_undo.h"
00028 #include "kspread_view.h"
00029
00030 #include <kdebug.h>
00031 #include <klistview.h>
00032 #include <klocale.h>
00033 #include <kmessagebox.h>
00034 #include <knumvalidator.h>
00035 #include <kpushbutton.h>
00036
00037 #include <qcheckbox.h>
00038 #include <qcombobox.h>
00039 #include <qframe.h>
00040 #include <qheader.h>
00041 #include <qlabel.h>
00042 #include <qlayout.h>
00043 #include <qlineedit.h>
00044 #include <qpushbutton.h>
00045 #include <qradiobutton.h>
00046 #include <qsqldatabase.h>
00047 #include <qsqlerror.h>
00048 #include <qsqlfield.h>
00049 #include <qsqlquery.h>
00050 #include <qsqlrecord.h>
00051 #include <qtextedit.h>
00052 #include <qtooltip.h>
00053 #include <qvariant.h>
00054 #include <qwhatsthis.h>
00055 #include <qwidget.h>
00056
00057
00058 #ifndef QT_NO_SQL
00059
00060
00061
00062
00063
00064 KSpreadDatabaseDlg::KSpreadDatabaseDlg( KSpreadView * parent, QRect const & rect, const char * name, bool modal, WFlags fl )
00065 : KWizard( (QWidget *) parent, name, modal, fl ),
00066 m_currentPage( eDatabase ),
00067 m_pView( parent ),
00068 m_targetRect( rect ),
00069 m_dbConnection( 0L )
00070 {
00071 if ( !name )
00072 setName( "KSpreadDatabaseDlg" );
00073
00074 setCaption( i18n( "Insert Data From Database" ) );
00075
00076
00077
00078 m_database = new QWidget( this, "m_database" );
00079 m_databaseLayout = new QGridLayout( m_database, 1, 1, -1, -1, "m_databaseLayout");
00080
00081 QFrame * Frame5 = new QFrame( m_database, "Frame5" );
00082 Frame5->setFrameShape( QFrame::MShape );
00083 Frame5->setFrameShadow( QFrame::MShadow );
00084 QVBoxLayout * Frame5Layout = new QVBoxLayout( Frame5, 11, 6, "Frame5Layout");
00085
00086
00087 QFrame * Frame16 = new QFrame( Frame5, "Frame16" );
00088 Frame16->setFrameShape( QFrame::NoFrame );
00089 Frame16->setFrameShadow( QFrame::Plain );
00090 QGridLayout * Frame16Layout = new QGridLayout( Frame16, 1, 1, 11, 7, "Frame16Layout");
00091
00092 m_Type = new QLabel( Frame16, "m_Type" );
00093 m_Type->setText( i18n( "Type:" ) );
00094
00095 Frame16Layout->addWidget( m_Type, 0, 0 );
00096
00097 QLabel * TextLabel4 = new QLabel( Frame16, "TextLabel4" );
00098 TextLabel4->setText( i18n( "User name:\n"
00099 "(if necessary)" ) );
00100 Frame16Layout->addWidget( TextLabel4, 4, 0 );
00101
00102 QLabel * TextLabel2 = new QLabel( Frame16, "TextLabel2" );
00103 TextLabel2->setText( i18n( "Host:" ) );
00104 Frame16Layout->addWidget( TextLabel2, 2, 0 );
00105
00106 m_driver = new QComboBox( FALSE, Frame16, "m_driver" );
00107 Frame16Layout->addWidget( m_driver, 0, 1 );
00108
00109 m_username = new QLineEdit( Frame16, "m_username" );
00110 Frame16Layout->addWidget( m_username, 4, 1 );
00111
00112 m_host = new QLineEdit( Frame16, "m_host" );
00113 m_host->setText("localhost");
00114 Frame16Layout->addWidget( m_host, 2, 1 );
00115
00116 QLabel * TextLabel3 = new QLabel( Frame16, "TextLabel3" );
00117 TextLabel3->setText( i18n( "Port:\n(if necessary)") );
00118 Frame16Layout->addWidget( TextLabel3, 3, 0 );
00119
00120 m_password = new QLineEdit( Frame16, "m_password" );
00121 m_password->setEchoMode( QLineEdit::Password );
00122 Frame16Layout->addWidget( m_password, 5, 1 );
00123
00124 m_port = new QLineEdit( Frame16, "m_port" );
00125 m_port->setValidator( new KIntValidator( m_port ) );
00126 Frame16Layout->addWidget( m_port, 3, 1 );
00127
00128 QLabel * dbName = new QLabel( Frame16, "dbName" );
00129 dbName->setText( i18n( "Database name: ") );
00130 Frame16Layout->addWidget( dbName, 1, 0 );
00131
00132 m_databaseName = new QLineEdit( Frame16, "m_databaseName" );
00133 Frame16Layout->addWidget( m_databaseName, 1, 1 );
00134
00135 QLabel * TextLabel5 = new QLabel( Frame16, "TextLabel5" );
00136 TextLabel5->setText( i18n( "Password:\n"
00137 "(if necessary)" ) );
00138 Frame16Layout->addWidget( TextLabel5, 5, 0 );
00139 Frame5Layout->addWidget( Frame16 );
00140
00141 m_databaseStatus = new QLabel( Frame5, "m_databaseStatus" );
00142 m_databaseStatus->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)5, 0, 0, m_databaseStatus->sizePolicy().hasHeightForWidth() ) );
00143 m_databaseStatus->setMaximumSize( QSize( 32767, 30 ) );
00144 m_databaseStatus->setText( " " );
00145 Frame5Layout->addWidget( m_databaseStatus );
00146
00147 m_databaseLayout->addWidget( Frame5, 0, 1 );
00148
00149 QFrame * Frame17 = new QFrame( m_database, "Frame17" );
00150 Frame17->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17->sizePolicy().hasHeightForWidth() ) );
00151 Frame17->setMinimumSize( QSize( 111, 0 ) );
00152 Frame17->setFrameShape( QFrame::NoFrame );
00153 Frame17->setFrameShadow( QFrame::Plain );
00154
00155 m_databaseLayout->addWidget( Frame17, 0, 0 );
00156 addPage( m_database, i18n( "Database" ) );
00157
00158
00159
00160 m_sheet = new QWidget( this, "m_table" );
00161 m_sheetLayout = new QGridLayout( m_sheet, 1, 1, 11, 6, "m_tableLayout");
00162
00163 QFrame * Frame5_2 = new QFrame( m_sheet, "Frame5_2" );
00164 Frame5_2->setFrameShape( QFrame::MShape );
00165 Frame5_2->setFrameShadow( QFrame::MShadow );
00166 QGridLayout * Frame5_2Layout = new QGridLayout( Frame5_2, 1, 1, 11, 6, "Frame5_2Layout");
00167
00168 QHBoxLayout * Layout21 = new QHBoxLayout( 0, 0, 6, "Layout21");
00169
00170
00171
00172
00173
00174
00175
00176
00177
00178
00179
00180
00181 Frame5_2Layout->addLayout( Layout21, 0, 0 );
00182
00183 m_sheetStatus = new QLabel( Frame5_2, "m_tableStatus" );
00184 m_sheetStatus->setText( " " );
00185 Frame5_2Layout->addWidget( m_sheetStatus, 3, 0 );
00186
00187 m_SelectSheetLabel = new QLabel( Frame5_2, "m_SelectSheetLabel" );
00188 m_SelectSheetLabel->setText( i18n( "Select tables:" ) );
00189 Frame5_2Layout->addWidget( m_SelectSheetLabel, 1, 0 );
00190
00191 m_sheetView = new KListView( Frame5_2, "m_tableView" );
00192 m_sheetView->addColumn( i18n( "Sheet" ) );
00193 m_sheetView->setRootIsDecorated( FALSE );
00194
00195 Frame5_2Layout->addWidget( m_sheetView, 2, 0 );
00196
00197 m_sheetLayout->addWidget( Frame5_2, 0, 1 );
00198
00199 QFrame * Frame17_2 = new QFrame( m_sheet, "Frame17_2" );
00200 Frame17_2->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17_2->sizePolicy().hasHeightForWidth() ) );
00201 Frame17_2->setMinimumSize( QSize( 111, 0 ) );
00202 Frame17_2->setFrameShape( QFrame::NoFrame );
00203 Frame17_2->setFrameShadow( QFrame::Plain );
00204
00205 m_sheetLayout->addWidget( Frame17_2, 0, 0 );
00206 addPage( m_sheet, i18n( "Sheets" ) );
00207
00208 m_columns = new QWidget( this, "m_columns" );
00209 m_columnsLayout = new QGridLayout( m_columns, 1, 1, 11, 6, "m_columnsLayout");
00210
00211 QFrame * Frame5_2_2 = new QFrame( m_columns, "Frame5_2_2" );
00212 Frame5_2_2->setFrameShape( QFrame::MShape );
00213 Frame5_2_2->setFrameShadow( QFrame::MShadow );
00214 QGridLayout * Frame5_2_2Layout = new QGridLayout( Frame5_2_2, 1, 1, 11, 6, "Frame5_2_2Layout");
00215
00216 QLabel * TextLabel11_2 = new QLabel( Frame5_2_2, "TextLabel11_2" );
00217 TextLabel11_2->setText( i18n( "Select columns:" ) );
00218
00219 Frame5_2_2Layout->addWidget( TextLabel11_2, 0, 0 );
00220
00221 m_columnView = new KListView( Frame5_2_2, "m_columnView" );
00222 m_columnView->addColumn( i18n( "Column" ) );
00223 m_columnView->addColumn( i18n( "Sheet" ) );
00224 m_columnView->addColumn( i18n( "Data Type" ) );
00225 m_columnView->setRootIsDecorated( FALSE );
00226
00227 Frame5_2_2Layout->addWidget( m_columnView, 1, 0 );
00228
00229 m_columnsStatus = new QLabel( Frame5_2_2, "m_columnsStatus" );
00230 m_columnsStatus->setText( " " );
00231 Frame5_2_2Layout->addWidget( m_columnsStatus, 2, 0 );
00232
00233 m_columnsLayout->addWidget( Frame5_2_2, 0, 1 );
00234
00235 QFrame * Frame17_3 = new QFrame( m_columns, "Frame17_3" );
00236 Frame17_3->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17_3->sizePolicy().hasHeightForWidth() ) );
00237 Frame17_3->setMinimumSize( QSize( 111, 0 ) );
00238 Frame17_3->setFrameShape( QFrame::NoFrame );
00239 Frame17_3->setFrameShadow( QFrame::Plain );
00240
00241 m_columnsLayout->addWidget( Frame17_3, 0, 0 );
00242 addPage( m_columns, i18n( "Columns" ) );
00243
00244
00245
00246 m_options = new QWidget( this, "m_options" );
00247 m_optionsLayout = new QGridLayout( m_options, 1, 1, 11, 6, "m_optionsLayout");
00248
00249 QFrame * optionsFrame = new QFrame( m_options, "optionsFrame" );
00250 optionsFrame->setFrameShape( QFrame::MShape );
00251 optionsFrame->setFrameShadow( QFrame::MShadow );
00252 QGridLayout * optionsFrameLayout = new QGridLayout( optionsFrame, 1, 1, 11, 6, "optionsFrameLayout");
00253
00254 m_columns_1 = new QComboBox( false, optionsFrame, "m_columns_1" );
00255 optionsFrameLayout->addWidget( m_columns_1, 2, 0 );
00256
00257 m_operatorValue_2 = new QLineEdit( optionsFrame, "m_operatorValue_2" );
00258 optionsFrameLayout->addWidget( m_operatorValue_2, 3, 2 );
00259
00260 m_andBox = new QRadioButton( optionsFrame, "m_andBox" );
00261 m_andBox->setText( i18n( "Match all of the following (AND)" ) );
00262 m_andBox->setChecked( true );
00263
00264 optionsFrameLayout->addMultiCellWidget( m_andBox, 0, 0, 0, 2 );
00265
00266 m_orBox = new QRadioButton( optionsFrame, "m_orBox" );
00267 m_orBox->setText( i18n( "Match any of the following (OR)" ) );
00268 optionsFrameLayout->addMultiCellWidget( m_orBox, 1, 1, 0, 2 );
00269
00270 m_operatorValue_1 = new QLineEdit( optionsFrame, "m_operatorValue" );
00271 optionsFrameLayout->addWidget( m_operatorValue_1, 2, 2 );
00272
00273 m_columns_2 = new QComboBox( FALSE, optionsFrame, "m_columns_2" );
00274 optionsFrameLayout->addWidget( m_columns_2, 3, 0 );
00275
00276 m_operatorValue_3 = new QLineEdit( optionsFrame, "m_operatorValue_3" );
00277 optionsFrameLayout->addWidget( m_operatorValue_3, 4, 2 );
00278
00279 m_operator_1 = new QComboBox( FALSE, optionsFrame, "m_operator_1" );
00280 m_operator_1->insertItem( i18n( "equals" ) );
00281 m_operator_1->insertItem( i18n( "not equal" ) );
00282 m_operator_1->insertItem( i18n( "in" ) );
00283 m_operator_1->insertItem( i18n( "not in" ) );
00284 m_operator_1->insertItem( i18n( "like" ) );
00285 m_operator_1->insertItem( i18n( "greater" ) );
00286 m_operator_1->insertItem( i18n( "lesser" ) );
00287 m_operator_1->insertItem( i18n( "greater or equal" ) );
00288 m_operator_1->insertItem( i18n( "less or equal" ) );
00289
00290 optionsFrameLayout->addWidget( m_operator_1, 2, 1 );
00291
00292 m_operator_2 = new QComboBox( FALSE, optionsFrame, "m_operator_2" );
00293 m_operator_2->insertItem( i18n( "equals" ) );
00294 m_operator_2->insertItem( i18n( "not equal" ) );
00295 m_operator_2->insertItem( i18n( "in" ) );
00296 m_operator_2->insertItem( i18n( "not in" ) );
00297 m_operator_2->insertItem( i18n( "like" ) );
00298 m_operator_2->insertItem( i18n( "greater" ) );
00299 m_operator_2->insertItem( i18n( "lesser" ) );
00300
00301 optionsFrameLayout->addWidget( m_operator_2, 3, 1 );
00302
00303 m_operator_3 = new QComboBox( FALSE, optionsFrame, "m_operator_3" );
00304 m_operator_3->insertItem( i18n( "equals" ) );
00305 m_operator_3->insertItem( i18n( "not equal" ) );
00306 m_operator_3->insertItem( i18n( "in" ) );
00307 m_operator_3->insertItem( i18n( "not in" ) );
00308 m_operator_3->insertItem( i18n( "like" ) );
00309 m_operator_3->insertItem( i18n( "greater" ) );
00310 m_operator_3->insertItem( i18n( "lesser" ) );
00311
00312 optionsFrameLayout->addWidget( m_operator_3, 4, 1 );
00313
00314 m_columns_3 = new QComboBox( false, optionsFrame, "m_columns_3" );
00315
00316 optionsFrameLayout->addWidget( m_columns_3, 4, 0 );
00317
00318 m_distinct = new QCheckBox( optionsFrame, "m_distinct" );
00319 m_distinct->setText( i18n( "Distinct" ) );
00320
00321 optionsFrameLayout->addWidget( m_distinct, 7, 2 );
00322
00323 QLabel * TextLabel19 = new QLabel( optionsFrame, "TextLabel19" );
00324 TextLabel19->setText( i18n( "Sorted by" ) );
00325 optionsFrameLayout->addWidget( TextLabel19, 5, 0 );
00326
00327 m_columnsSort_1 = new QComboBox( false, optionsFrame, "m_columnsSort_1" );
00328 optionsFrameLayout->addWidget( m_columnsSort_1, 5, 1 );
00329
00330 m_sortMode_1 = new QComboBox( false, optionsFrame, "m_sortMode_1" );
00331 m_sortMode_1->insertItem( i18n( "Ascending" ) );
00332 m_sortMode_1->insertItem( i18n( "Descending" ) );
00333 optionsFrameLayout->addWidget( m_sortMode_1, 5, 2 );
00334
00335 QLabel * TextLabel19_2 = new QLabel( optionsFrame, "TextLabel19_2" );
00336 TextLabel19_2->setText( i18n( "Sorted by" ) );
00337 optionsFrameLayout->addWidget( TextLabel19_2, 6, 0 );
00338
00339 m_columnsSort_2 = new QComboBox( false, optionsFrame, "m_columnsSort_2" );
00340 optionsFrameLayout->addWidget( m_columnsSort_2, 6, 1 );
00341
00342 m_sortMode_2 = new QComboBox( false, optionsFrame, "m_sortMode_2" );
00343 m_sortMode_2->insertItem( i18n( "Ascending" ) );
00344 m_sortMode_2->insertItem( i18n( "Descending" ) );
00345
00346 optionsFrameLayout->addWidget( m_sortMode_2, 6, 2 );
00347 QSpacerItem* spacer = new QSpacerItem( 20, 20, QSizePolicy::Expanding, QSizePolicy::Minimum );
00348 optionsFrameLayout->addItem( spacer, 7, 1 );
00349 QSpacerItem* spacer_2 = new QSpacerItem( 20, 20, QSizePolicy::Expanding, QSizePolicy::Minimum );
00350 optionsFrameLayout->addItem( spacer_2, 7, 0 );
00351
00352 m_optionsLayout->addWidget( optionsFrame, 0, 1 );
00353
00354 QFrame * Frame17_4 = new QFrame( m_options, "Frame17_4" );
00355 Frame17_4->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17_4->sizePolicy().hasHeightForWidth() ) );
00356 Frame17_4->setMinimumSize( QSize( 111, 0 ) );
00357 Frame17_4->setFrameShape( QFrame::NoFrame );
00358 Frame17_4->setFrameShadow( QFrame::Plain );
00359
00360 m_optionsLayout->addWidget( Frame17_4, 0, 0 );
00361 addPage( m_options, i18n( "Query Options" ) );
00362
00363
00364
00365 m_result = new QWidget( this, "m_result" );
00366 m_resultLayout = new QGridLayout( m_result, 1, 1, 11, 6, "m_resultLayout");
00367
00368 QFrame * Frame5_2_2_3 = new QFrame( m_result, "Frame5_2_2_3" );
00369 Frame5_2_2_3->setFrameShape( QFrame::MShape );
00370 Frame5_2_2_3->setFrameShadow( QFrame::MShadow );
00371 QGridLayout * Frame5_2_2_3Layout = new QGridLayout( Frame5_2_2_3, 1, 1, 11, 6, "Frame5_2_2_3Layout");
00372
00373 QLabel * TextLabel17 = new QLabel( Frame5_2_2_3, "TextLabel17" );
00374 TextLabel17->setText( i18n( "SQL query:" ) );
00375 Frame5_2_2_3Layout->addWidget( TextLabel17, 0, 0 );
00376
00377 m_sqlQuery = new QTextEdit( Frame5_2_2_3, "m_sqlQuery" );
00378 Frame5_2_2_3Layout->addWidget( m_sqlQuery, 1, 0 );
00379
00380 QFrame * Frame12 = new QFrame( Frame5_2_2_3, "Frame12" );
00381 Frame12->setFrameShape( QFrame::StyledPanel );
00382 Frame12->setFrameShadow( QFrame::Raised );
00383 QGridLayout * Frame12Layout = new QGridLayout( Frame12, 1, 1, 11, 6, "Frame12Layout");
00384
00385 m_startingRegion = new QRadioButton( Frame12, "m_startingRegion" );
00386 m_startingRegion->setText( i18n( "Insert in region" ) );
00387 Frame12Layout->addWidget( m_startingRegion, 0, 0 );
00388
00389 m_cell = new QLineEdit( Frame12, "m_cell" );
00390 Frame12Layout->addWidget( m_cell, 1, 1 );
00391
00392 m_region = new QLineEdit( Frame12, "m_region" );
00393 Frame12Layout->addWidget( m_region, 0, 1 );
00394
00395 m_startingCell = new QRadioButton( Frame12, "m_startingCell" );
00396 m_startingCell->setText( i18n( "Starting in cell" ) );
00397 m_startingCell->setChecked( TRUE );
00398 Frame12Layout->addWidget( m_startingCell, 1, 0 );
00399
00400 Frame5_2_2_3Layout->addWidget( Frame12, 2, 0 );
00401 m_resultLayout->addWidget( Frame5_2_2_3, 0, 1 );
00402
00403 QFrame * Frame17_5 = new QFrame( m_result, "Frame17_5" );
00404 Frame17_5->setSizePolicy( QSizePolicy( (QSizePolicy::SizeType)0, (QSizePolicy::SizeType)7, 0, 0, Frame17_5->sizePolicy().hasHeightForWidth() ) );
00405 Frame17_5->setMinimumSize( QSize( 111, 0 ) );
00406 Frame17_5->setFrameShape( QFrame::NoFrame );
00407 Frame17_5->setFrameShadow( QFrame::Plain );
00408
00409 m_resultLayout->addWidget( Frame17_5, 0, 0 );
00410 addPage( m_result, i18n( "Result" ) );
00411
00412 finishButton()->setEnabled(false);
00413
00414
00415 connect( m_orBox, SIGNAL( clicked() ), this, SLOT( orBox_clicked() ) );
00416 connect( m_andBox, SIGNAL( clicked() ), this, SLOT( andBox_clicked() ) );
00417 connect( m_startingCell, SIGNAL( clicked() ), this, SLOT( startingCell_clicked() ) );
00418 connect( m_startingRegion, SIGNAL( clicked() ), this, SLOT( startingRegion_clicked() ) );
00419 connect( m_driver, SIGNAL( activated(int) ), this, SLOT( databaseDriverChanged(int) ) );
00420 connect( m_host, SIGNAL( textChanged(const QString &) ), this, SLOT( databaseHostChanged(const QString &) ) );
00421 connect( m_databaseName, SIGNAL( textChanged(const QString &) ), this, SLOT( databaseNameChanged(const QString &) ) );
00422 connect( m_sheetView, SIGNAL( contextMenuRequested( QListViewItem *, const QPoint &, int ) ),
00423 this, SLOT( popupSheetViewMenu(QListViewItem *, const QPoint &, int ) ) );
00424 connect( m_sheetView, SIGNAL( clicked( QListViewItem * ) ), this, SLOT( sheetViewClicked( QListViewItem * ) ) );
00425
00426 QStringList str = QSqlDatabase::drivers();
00427 m_driver->insertItem("");
00428 m_driver->insertStringList( str );
00429 if ( str.isEmpty() )
00430 {
00431 KMessageBox::error( this, i18n("No database drivers available; you need "
00432 "to compile Qt with the necessary drivers.") );
00433 }
00434
00435 helpButton()->hide();
00436 setNextEnabled(m_database, false);
00437 setNextEnabled(m_sheet, false);
00438 setNextEnabled(m_columns, false);
00439 setNextEnabled(m_options, false);
00440 setNextEnabled(m_result, false);
00441 }
00442
00443 KSpreadDatabaseDlg::~KSpreadDatabaseDlg()
00444 {
00445
00446 if ( m_dbConnection )
00447 m_dbConnection->close();
00448 }
00449
00450 void KSpreadDatabaseDlg::switchPage( int id )
00451 {
00452 if ( id > eResult )
00453 --m_currentPage;
00454 if ( id < eDatabase )
00455 ++m_currentPage;
00456
00457 switch ( id )
00458 {
00459 case eDatabase:
00460 showPage(m_database);
00461 break;
00462
00463 case eSheets:
00464 showPage(m_sheet);
00465 break;
00466
00467 case eColumns:
00468 showPage(m_columns);
00469 break;
00470
00471 case eOptions:
00472 showPage(m_options);
00473 break;
00474
00475 case eResult:
00476 showPage(m_result);
00477 break;
00478
00479 default:
00480 break;
00481 }
00482 }
00483
00484 void KSpreadDatabaseDlg::next()
00485 {
00486 switch ( m_currentPage )
00487 {
00488 case eDatabase:
00489 if (!databaseDoNext())
00490 return;
00491 break;
00492
00493 case eSheets:
00494 if (!sheetsDoNext())
00495 return;
00496 break;
00497
00498 case eColumns:
00499 if (!columnsDoNext())
00500 return;
00501 break;
00502
00503 case eOptions:
00504 if (!optionsDoNext())
00505 return;
00506 break;
00507
00508 case eResult:
00509
00510 break;
00511
00512 default:
00513 break;
00514 }
00515
00516 ++m_currentPage;
00517
00518 switchPage( m_currentPage );
00519 }
00520
00521 void KSpreadDatabaseDlg::back()
00522 {
00523 --m_currentPage;
00524
00525 switchPage( m_currentPage );
00526 }
00527
00528 void KSpreadDatabaseDlg::accept()
00529 {
00530 KSpreadSheet * sheet = m_pView->activeSheet();
00531 int top;
00532 int left;
00533 int width = -1;
00534 int height = -1;
00535 if ( m_startingRegion->isChecked() )
00536 {
00537 KSpreadRange range( m_region->text() );
00538 if ( range.isSheetKnown() )
00539 {
00540 KMessageBox::error( this, i18n("You cannot specify a table here.") );
00541 m_region->setFocus();
00542 m_region->selectAll();
00543 return;
00544 }
00545
00546 range.sheet = sheet;
00547
00548 if ( !range.isValid() )
00549 {
00550 KMessageBox::error( this, i18n("You have to specify a valid region.") );
00551 m_region->setFocus();
00552 m_region->selectAll();
00553 return;
00554 }
00555 top = range.range.top();
00556 left = range.range.left();
00557 width = range.range.width();
00558 height = range.range.height();
00559 }
00560 else
00561 {
00562 KSpreadPoint point( m_cell->text() );
00563 if ( point.isSheetKnown() )
00564 {
00565 KMessageBox::error( this, i18n("You cannot specify a table here.") );
00566 m_cell->setFocus();
00567 m_cell->selectAll();
00568 return;
00569 }
00570 point.sheet = sheet;
00571
00572 if ( !point.isValid() )
00573 {
00574 KMessageBox::error( this, i18n("You have to specify a valid cell.") );
00575 m_cell->setFocus();
00576 m_cell->selectAll();
00577 return;
00578 }
00579 top = point.pos.y();
00580 left = point.pos.x();
00581 }
00582
00583 int i;
00584 QString queryStr;
00585 QString tmp = m_sqlQuery->text();
00586 for ( i = 0; i < (int) tmp.length(); ++i )
00587 {
00588 if ( tmp[i] != '\n' )
00589 queryStr += tmp[i];
00590 else
00591 queryStr += " ";
00592 }
00593
00594 KSpreadCell * cell;
00595 QSqlQuery query( QString::null, m_dbConnection );
00596
00597
00598
00599
00600
00601
00602
00603 QRegExp couldModifyDB( "(^|[( \\s])(UPDATE|DELETE|INSERT|CREATE) ", false );
00604 QRegExp couldQueryDB( "(^|[( \\s])(SELECT) ", false );
00605
00606 if (couldModifyDB.search( queryStr ) != -1 || couldQueryDB.search ( queryStr ) == -1 )
00607 {
00608 KMessageBox::error( this, i18n("You are not allowed to change data in the database.") );
00609 m_sqlQuery->setFocus();
00610 return;
00611 }
00612
00613 if ( !query.exec( queryStr ) )
00614 {
00615 KMessageBox::error( this, i18n( "Executing query failed." ) );
00616 m_sqlQuery->setFocus();
00617 return;
00618 }
00619
00620 if ( query.size() == 0 )
00621 {
00622 KMessageBox::error( this, i18n( "You did not get any results with this query." ) );
00623 m_sqlQuery->setFocus();
00624 return;
00625 }
00626
00627 int y = 0;
00628 int count = m_columns_1->count();
00629 if ( width != -1 )
00630 {
00631 if ( count > width )
00632 count = width;
00633 }
00634
00635 if ( height == -1 )
00636 {
00637 height = 0;
00638 if ( query.first() )
00639 {
00640 if ( query.isValid() )
00641 ++height;
00642 }
00643 while ( query.next() )
00644 {
00645 if ( query.isValid() )
00646 ++height;
00647 }
00648 }
00649
00650 if ( !m_pView->doc()->undoLocked() )
00651 {
00652 QRect r(left, top, count, height);
00653 KSpreadUndoInsertData * undo = new KSpreadUndoInsertData( m_pView->doc(), sheet, r );
00654 m_pView->doc()->addCommand( undo );
00655 }
00656
00657 m_pView->doc()->emitBeginOperation();
00658
00659 if ( query.first() )
00660 {
00661 if ( query.isValid() )
00662 {
00663 for ( i = 0; i < count; ++i )
00664 {
00665 cell = sheet->nonDefaultCell( left + i, top + y );
00666 cell->setCellText( query.value( i ).toString() );
00667 }
00668 ++y;
00669 }
00670 }
00671
00672 if ( y != height )
00673 {
00674 while ( query.next() )
00675 {
00676 if ( !query.isValid() )
00677 continue;
00678
00679 for ( i = 0; i < count; ++i )
00680 {
00681 cell = sheet->nonDefaultCell( left + i, top + y );
00682 cell->setCellText( query.value( i ).toString() );
00683 }
00684 ++y;
00685
00686 if ( y == height )
00687 break;
00688 }
00689 }
00690
00691 m_pView->slotUpdateView( sheet );
00692 KWizard::accept();
00693 }
00694
00695 bool KSpreadDatabaseDlg::databaseDoNext()
00696 {
00697 m_dbConnection = QSqlDatabase::addDatabase( m_driver->currentText() );
00698
00699 if ( m_dbConnection )
00700 {
00701 m_dbConnection->setDatabaseName( m_databaseName->text() );
00702 m_dbConnection->setHostName( m_host->text() );
00703
00704 if ( !m_username->text().isEmpty() )
00705 m_dbConnection->setUserName( m_username->text() );
00706
00707 if ( !m_password->text().isEmpty() )
00708 m_dbConnection->setPassword( m_password->text() );
00709
00710 if ( !m_port->text().isEmpty() )
00711 {
00712 bool ok = false;
00713 int port = m_port->text().toInt( &ok );
00714 if (!ok)
00715 {
00716 KMessageBox::error( this, i18n("The port must be a number") );
00717 return false;
00718 }
00719 m_dbConnection->setPort( port );
00720 }
00721
00722 m_databaseStatus->setText( i18n("Connecting to database...") );
00723 if ( m_dbConnection->open() )
00724 {
00725 m_databaseStatus->setText( i18n("Connected. Retrieving table information...") );
00726 QStringList sheetList( m_dbConnection->tables() );
00727
00728 if ( sheetList.isEmpty() )
00729 {
00730 KMessageBox::error( this, i18n("This database contains no tables") );
00731 return false;
00732 }
00733
00734 unsigned int i;
00735 m_sheetView->clear();
00736
00737 for ( i = 0; i < sheetList.size(); ++i )
00738 {
00739 QCheckListItem * item = new QCheckListItem( m_sheetView, sheetList[i],
00740 QCheckListItem::CheckBox );
00741 item->setOn(false);
00742 m_sheetView->insertItem( item );
00743 }
00744
00745 m_sheetView->setEnabled( true );
00746 m_databaseStatus->setText( " " );
00747 }
00748 else
00749 {
00750 QSqlError error = m_dbConnection->lastError();
00751 QString errorMsg;
00752 QString err1 = error.driverText();
00753 QString err2 = error.databaseText();
00754 if ( !err1.isEmpty() )
00755 {
00756 errorMsg.append( error.driverText() );
00757 errorMsg.append( "\n" );
00758 }
00759 if ( !err2.isEmpty() && err1 != err2)
00760 {
00761 errorMsg.append( error.databaseText() );
00762 errorMsg.append( "\n" );
00763 }
00764 m_databaseStatus->setText( " " );
00765
00766 KMessageBox::error( this, errorMsg );
00767 return false;
00768 }
00769 }
00770 else
00771 {
00772 KMessageBox::error( this, i18n("Driver could not be loaded") );
00773 m_databaseStatus->setText( " " );
00774 return false;
00775 }
00776 setNextEnabled(m_sheet, true);
00777
00778 return true;
00779 }
00780
00781 bool KSpreadDatabaseDlg::sheetsDoNext()
00782 {
00783 m_databaseStatus->setText( i18n("Retrieving meta data of tables...") );
00784 QStringList sheets;
00785
00786 for (QListViewItem * item = (QCheckListItem *) m_sheetView->firstChild(); item; item = item->nextSibling())
00787 {
00788 if (((QCheckListItem * ) item)->isOn())
00789 {
00790 sheets.append(((QCheckListItem * ) item)->text());
00791 }
00792 }
00793
00794 if (sheets.empty())
00795 {
00796 KMessageBox::error( this, i18n("You have to select at least one table.") );
00797 return false;
00798 }
00799
00800 m_columnView->clear();
00801 QSqlRecord info;
00802 QCheckListItem * item;
00803 for (int i = 0; i < (int) sheets.size(); ++i)
00804 {
00805 info = m_dbConnection->record( sheets[i] );
00806 for (int j = 0; j < (int) info.count(); ++j)
00807 {
00808 QString name = info.fieldName(j);
00809 item = new QCheckListItem( m_columnView, name,
00810 QCheckListItem::CheckBox );
00811 item->setOn(false);
00812 m_columnView->insertItem( item );
00813 item->setText( 1, sheets[i] );
00814 QSqlField * field = info.field(name);
00815 item->setText( 2, QVariant::typeToName(field->type()) );
00816 }
00817 }
00818 m_columnView->setSorting(1, true);
00819 m_columnView->sort();
00820 m_columnView->setSorting( -1 );
00821
00822 setNextEnabled(m_columns, true);
00823
00824 return true;
00825 }
00826
00827 bool KSpreadDatabaseDlg::columnsDoNext()
00828 {
00829 QStringList columns;
00830 for (QListViewItem * item = m_columnView->firstChild(); item; item = item->nextSibling())
00831 {
00832 if (((QCheckListItem * ) item)->isOn())
00833 {
00834 columns.append( item->text(1) + "." + ((QCheckListItem * ) item)->text());
00835 }
00836 }
00837
00838 if (columns.empty())
00839 {
00840 KMessageBox::error( this, i18n("You have to select at least one column.") );
00841 return false;
00842 }
00843
00844 m_columns_1->clear();
00845 m_columns_2->clear();
00846 m_columns_3->clear();
00847 m_columns_1->insertStringList(columns);
00848 m_columns_2->insertStringList(columns);
00849 m_columns_3->insertStringList(columns);
00850 m_columnsSort_1->clear();
00851 m_columnsSort_2->clear();
00852 m_columnsSort_1->insertItem( i18n("None") );
00853 m_columnsSort_2->insertItem( i18n("None") );
00854 m_columnsSort_1->insertStringList(columns);
00855 m_columnsSort_2->insertStringList(columns);
00856
00857 setNextEnabled(m_options, true);
00858
00859 return true;
00860 }
00861
00862
00863 QString KSpreadDatabaseDlg::getWhereCondition(QString const & column,
00864 QString const & value,
00865 int op)
00866 {
00867 QString wherePart;
00868
00869 switch( op )
00870 {
00871 case 0:
00872 wherePart += column;
00873 wherePart += " = ";
00874 break;
00875 case 1:
00876 wherePart += "NOT ";
00877 wherePart += column;
00878 wherePart += " = ";
00879 break;
00880 case 2:
00881 wherePart += column;
00882 wherePart += " IN ";
00883 break;
00884 case 3:
00885 wherePart += "NOT ";
00886 wherePart += column;
00887 wherePart += " IN ";
00888 break;
00889 case 4:
00890 wherePart += column;
00891 wherePart += " LIKE ";
00892 break;
00893 case 5:
00894 wherePart += column;
00895 wherePart += " > ";
00896 break;
00897 case 6:
00898 wherePart += column;
00899 wherePart += " < ";
00900 break;
00901 case 7:
00902 wherePart += column;
00903 wherePart += " >= ";
00904 break;
00905 case 8:
00906 wherePart += column;
00907 wherePart += " <= ";
00908 break;
00909 }
00910
00911 if ( op != 2 && op != 3 )
00912 {
00913 QString val;
00914 bool ok = false;
00915 value.toDouble(&ok);
00916
00917 if ( !ok )
00918 {
00919 if (value[0] != '\'')
00920 val = "'";
00921
00922 val += value;
00923
00924 if (value[value.length() - 1] != '\'')
00925 val += "'";
00926 }
00927 else
00928 val = value;
00929
00930 wherePart += val;
00931 }
00932 else
00933 {
00934 QString val;
00935 if (value[0] != '(')
00936 val = "(";
00937 val += value;
00938 if ( value[value.length() - 1] != ')' )
00939 val += ")";
00940 wherePart += val;
00941 }
00942
00943 return wherePart;
00944 }
00945
00946 QString KSpreadDatabaseDlg::exchangeWildcards(QString const & value)
00947 {
00948 QString str(value);
00949 int p = str.find('*');
00950 while ( p > -1 )
00951 {
00952 str = str.replace( p, 1, "%" );
00953 p = str.find('*');
00954 }
00955
00956 p = str.find('?');
00957 while ( p > -1 )
00958 {
00959 str = str.replace( p, 1, "_" );
00960 p = str.find('?');
00961 }
00962 return str;
00963 }
00964
00965 bool KSpreadDatabaseDlg::optionsDoNext()
00966 {
00967 if ( m_operator_1->currentItem() == 4 )
00968 {
00969 if ( ( m_operatorValue_1->text().find('*') != -1 )
00970 || ( m_operatorValue_1->text().find('?') != -1 ) )
00971 {
00972
00973 int res = KMessageBox::warningYesNo( this, i18n("'*' or '?' are not valid wildcards in SQL. "
00974 "The proper replacements are '%' or '_'. Do you want to replace them?") );
00975
00976 if ( res == KMessageBox::Yes )
00977 m_operatorValue_1->setText(exchangeWildcards(m_operatorValue_1->text()));
00978 }
00979 }
00980
00981 if ( m_operator_2->currentItem() == 4 )
00982 {
00983 if ( ( m_operatorValue_2->text().find('*') != -1 )
00984 || ( m_operatorValue_2->text().find('?') != -1 ) )
00985 {
00986
00987 int res = KMessageBox::warningYesNo( this, i18n("'*' or '?' are not valid wildcards in SQL. "
00988 "The proper replacements are '%' or '_'. Do you want to replace them?") );
00989
00990 if ( res == KMessageBox::Yes )
00991 m_operatorValue_2->setText(exchangeWildcards(m_operatorValue_2->text()));
00992 }
00993 }
00994
00995 if ( m_operator_3->currentItem() == 4 )
00996 {
00997 if ( ( m_operatorValue_3->text().find('*') != -1 )
00998 || ( m_operatorValue_3->text().find('?') != -1 ) )
00999 {
01000
01001 int res = KMessageBox::warningYesNo( this, i18n("'*' or '?' are not valid wildcards in SQL. "
01002 "The proper replacements are '%' or '_'. Do you want to replace them?") );
01003
01004 if ( res == KMessageBox::Yes )
01005 m_operatorValue_3->setText(exchangeWildcards(m_operatorValue_3->text()));
01006 }
01007 }
01008
01009 QString query("SELECT ");
01010
01011 if (m_distinct->isChecked())
01012 query += "DISTINCT ";
01013
01014 int i;
01015 int l = m_columns_1->count() - 1;
01016 for ( i = 0; i < l; ++i )
01017 {
01018 query += m_columns_1->text( i );
01019 query += ", ";
01020 }
01021 query += m_columns_1->text( l );
01022
01023 query += "\nFROM ";
01024
01025 QListViewItem * item = (QCheckListItem *) m_sheetView->firstChild();
01026 bool b = false;
01027 while ( item )
01028 {
01029 if (((QCheckListItem * ) item)->isOn())
01030 {
01031 if ( b )
01032 query += ", ";
01033 b = true;
01034 query += ((QCheckListItem * ) item)->text();
01035 }
01036 item = item->nextSibling();
01037 }
01038
01039 if ( ( !m_operatorValue_1->text().isEmpty() )
01040 || ( !m_operatorValue_2->text().isEmpty() )
01041 || ( !m_operatorValue_3->text().isEmpty() ) )
01042 query += "\nWHERE ";
01043
01044 bool added = false;
01045 if ( !m_operatorValue_1->text().isEmpty() )
01046 {
01047 query += getWhereCondition(m_columns_1->currentText(),
01048 m_operatorValue_1->text(),
01049 m_operator_1->currentItem());
01050 added = true;
01051 }
01052
01053 if ( !m_operatorValue_2->text().isEmpty() )
01054 {
01055 if (added)
01056 query += ( m_andBox->isChecked() ? " AND " : " OR " );
01057
01058 query += getWhereCondition(m_columns_2->currentText(),
01059 m_operatorValue_2->text(),
01060 m_operator_2->currentItem());
01061 added = true;
01062 }
01063
01064 if ( !m_operatorValue_3->text().isEmpty() )
01065 {
01066 if (added)
01067 query += ( m_andBox->isChecked() ? " AND " : " OR " );
01068
01069 query += getWhereCondition(m_columns_3->currentText(),
01070 m_operatorValue_3->text(),
01071 m_operator_3->currentItem());
01072 }
01073
01074 if ( (m_columnsSort_1->currentItem() != 0)
01075 || (m_columnsSort_2->currentItem() != 0) )
01076 {
01077 query += "\nORDER BY ";
01078 bool added = false;
01079 if ( m_columnsSort_1->currentItem() != 0 )
01080 {
01081 added = true;
01082 query += m_columnsSort_1->currentText();
01083 if ( m_sortMode_1->currentItem() == 1 )
01084 query += " DESC ";
01085 }
01086
01087 if ( m_columnsSort_2->currentItem() != 0 )
01088 {
01089 if ( added )
01090 query += ", ";
01091
01092 query += m_columnsSort_2->currentText();
01093 if ( m_sortMode_2->currentItem() == 1 )
01094 query += " DESC ";
01095 }
01096 }
01097
01098 m_sqlQuery->setText(query);
01099 m_cell->setText(KSpreadCell::name( m_targetRect.left(), m_targetRect.top() ) );
01100 m_region->setText(util_rangeName( m_targetRect ) );
01101
01102 setFinishEnabled( m_result, true );
01103
01104 return true;
01105 }
01106
01107 void KSpreadDatabaseDlg::orBox_clicked()
01108 {
01109 m_andBox->setChecked( false );
01110 m_orBox->setChecked( true );
01111 }
01112
01113 void KSpreadDatabaseDlg::andBox_clicked()
01114 {
01115 m_andBox->setChecked( true );
01116 m_orBox->setChecked( false );
01117 }
01118
01119 void KSpreadDatabaseDlg::startingCell_clicked()
01120 {
01121 m_startingCell->setChecked( true );
01122 m_startingRegion->setChecked( false );
01123 }
01124
01125 void KSpreadDatabaseDlg::startingRegion_clicked()
01126 {
01127 m_startingCell->setChecked( false );
01128 m_startingRegion->setChecked( true );
01129 }
01130
01131 void KSpreadDatabaseDlg::connectButton_clicked()
01132 {
01133 qWarning( "KSpreadDatabaseDlg::connectButton_clicked(): Not implemented yet!" );
01134 }
01135
01136 void KSpreadDatabaseDlg::databaseNameChanged(const QString & s)
01137 {
01138 if ( !m_driver->currentText().isEmpty() && !s.isEmpty()
01139 && !m_host->text().isEmpty() )
01140 setNextEnabled(m_database, true);
01141 else
01142 setNextEnabled(m_database, false);
01143 }
01144
01145 void KSpreadDatabaseDlg::databaseHostChanged(const QString & s)
01146 {
01147 if ( !m_driver->currentText().isEmpty() && !s.isEmpty()
01148 && !m_databaseName->text().isEmpty() )
01149 setNextEnabled(m_database, true);
01150 else
01151 setNextEnabled(m_database, false);
01152 }
01153
01154 void KSpreadDatabaseDlg::databaseDriverChanged(int index)
01155 {
01156 if ( index > 0 && !m_host->text().isEmpty()
01157 && !m_databaseName->text().isEmpty() )
01158 setNextEnabled(m_database, true);
01159 else
01160 setNextEnabled(m_database, false);
01161 }
01162
01163 void KSpreadDatabaseDlg::popupSheetViewMenu( QListViewItem *, const QPoint &, int )
01164 {
01165
01166 }
01167
01168 void KSpreadDatabaseDlg::sheetViewClicked( QListViewItem * )
01169 {
01170
01171
01172
01173
01174
01175
01176 }
01177
01178
01179 #include "kspread_dlg_database.moc"
01180
01181 #endif // QT_NO_SQL