kspread

kspread_dlg_goalseek.cc

00001 /* This file is part of the KDE project
00002    Copyright (C) 2002-2003 Norbert Andres <nandres@web.de>
00003              (C) 2002-2003 Philipp Mueller <philipp.mueller@gmx.de>
00004              (C) 2002 Laurent Montel <montel@kde.org>
00005              (C) 2002 John Dailey <dailey@vt.edu>
00006              (C) 2002 Ariya Hidayat <ariya@kde.org>
00007              (C) 2002 Werner Trobin <trobin@kde.org>
00008              (C) 2002 Harri Porten <porten@kde.org>
00009 
00010    This library is free software; you can redistribute it and/or
00011    modify it under the terms of the GNU Library General Public
00012    License as published by the Free Software Foundation; either
00013    version 2 of the License, or (at your option) any later version.
00014 
00015    This library is distributed in the hope that it will be useful,
00016    but WITHOUT ANY WARRANTY; without even the implied warranty of
00017    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00018    Library General Public License for more details.
00019 
00020    You should have received a copy of the GNU Library General Public License
00021    along with this library; see the file COPYING.LIB.  If not, write to
00022    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00023  * Boston, MA 02110-1301, USA.
00024 */
00025 
00026 #include "kspread_dlg_goalseek.h"
00027 
00028 #include "kspread_canvas.h"
00029 #include "kspread_cell.h"
00030 #include "kspread_doc.h"
00031 #include "kspread_map.h"
00032 #include "selection.h"
00033 #include "kspread_sheet.h"
00034 #include "kspread_undo.h"
00035 #include "kspread_util.h"
00036 #include "kspread_view.h"
00037 
00038 #include <kapplication.h>
00039 #include <kdebug.h>
00040 #include <klocale.h>
00041 #include <kmessagebox.h>
00042 #include <kstdguiitem.h>
00043 #include <kpushbutton.h>
00044 
00045 #include <qframe.h>
00046 #include <qlabel.h>
00047 #include <qlayout.h>
00048 #include <qlineedit.h>
00049 #include <qtooltip.h>
00050 #include <qvariant.h>
00051 #include <qwhatsthis.h>
00052 
00053 #include <math.h>
00054 
00055 using namespace KSpread;
00056 
00057 GoalSeekDialog::GoalSeekDialog( View * parent,  QPoint const & marker,
00058                                         const char * name, bool, WFlags fl )
00059   : KDialog( parent, name, false, fl ),
00060     m_pView( parent ),
00061     m_maxIter( 1000 ),
00062     m_restored( true ),
00063     m_focus(0),
00064     m_anchor( m_pView->selectionInfo()->anchor() ),
00065     m_marker( m_pView->selectionInfo()->marker() ),
00066     m_selection( m_pView->selectionInfo()->selection() )
00067 {
00068   setWFlags( Qt::WDestructiveClose );
00069 
00070   if ( !name )
00071     setName( "GoalSeekDialog" );
00072 
00073   resize( 458, 153 );
00074   setCaption( i18n( "Goal Seek" ) );
00075   setSizeGripEnabled( true );
00076 
00077   GoalSeekDialogLayout = new QGridLayout( this, 1, 1, 11, 6, "GoalSeekDialogLayout");
00078 
00079   m_startFrame = new QFrame( this, "m_startFrame" );
00080   m_startFrame->setFrameShape( QFrame::StyledPanel );
00081   m_startFrame->setFrameShadow( QFrame::Raised );
00082   m_startFrameLayout = new QGridLayout( m_startFrame, 1, 1, 11, 6, "m_startFrameLayout");
00083 
00084   QLabel * TextLabel4 = new QLabel( m_startFrame, "TextLabel4" );
00085   TextLabel4->setText( i18n( "To value:" ) );
00086   m_startFrameLayout->addWidget( TextLabel4, 1, 0 );
00087 
00088   m_targetValueEdit = new QLineEdit( m_startFrame, "m_targetValueEdit" );
00089   m_startFrameLayout->addWidget( m_targetValueEdit, 1, 1 );
00090 
00091   m_targetEdit = new QLineEdit( m_startFrame, "m_targetEdit" );
00092   m_startFrameLayout->addWidget( m_targetEdit, 0, 1 );
00093   m_targetEdit->setText( Cell::name( marker.x(), marker.y() ) );
00094 
00095   m_sourceEdit = new QLineEdit( m_startFrame, "m_sourceEdit" );
00096   m_startFrameLayout->addWidget( m_sourceEdit, 2, 1 );
00097 
00098   QLabel * TextLabel5 = new QLabel( m_startFrame, "TextLabel5" );
00099   TextLabel5->setText( i18n( "By changing cell:" ) );
00100 
00101   m_startFrameLayout->addWidget( TextLabel5, 2, 0 );
00102 
00103   QLabel * TextLabel3 = new QLabel( m_startFrame, "TextLabel3" );
00104   TextLabel3->setText( i18n( "Set cell:" ) );
00105 
00106   m_startFrameLayout->addWidget( TextLabel3, 0, 0 );
00107   GoalSeekDialogLayout->addWidget( m_startFrame, 0, 0 );
00108 
00109   QVBoxLayout * Layout5 = new QVBoxLayout( 0, 0, 6, "Layout5");
00110 
00111   m_buttonOk = new QPushButton( this, "m_buttonOk" );
00112   m_buttonOk->setText( i18n( "&Start" ) );
00113   m_buttonOk->setAccel( 276824143 );
00114   m_buttonOk->setAutoDefault( TRUE );
00115   m_buttonOk->setDefault( TRUE );
00116   Layout5->addWidget( m_buttonOk );
00117 
00118   m_buttonCancel = new KPushButton( KStdGuiItem::cancel(), this, "m_buttonCancel" );
00119   m_buttonCancel->setAccel( 276824131 );
00120   m_buttonCancel->setAutoDefault( TRUE );
00121   Layout5->addWidget( m_buttonCancel );
00122   QSpacerItem* spacer = new QSpacerItem( 20, 20, QSizePolicy::Minimum, QSizePolicy::Expanding );
00123   Layout5->addItem( spacer );
00124 
00125   GoalSeekDialogLayout->addMultiCellLayout( Layout5, 0, 1, 1, 1 );
00126 
00127   m_resultFrame = new QFrame( this, "m_resultFrame" );
00128   m_resultFrame->setFrameShape( QFrame::StyledPanel );
00129   m_resultFrame->setFrameShadow( QFrame::Raised );
00130   m_resultFrame->setMinimumWidth( 350 );
00131   m_resultFrameLayout = new QGridLayout( m_resultFrame, 1, 1, 11, 6, "m_resultFrameLayout");
00132 
00133   m_currentValueLabel = new QLabel( m_resultFrame, "m_currentValueLabel" );
00134   m_currentValueLabel->setText( i18n( "Current value:" ) );
00135 
00136   m_resultFrameLayout->addWidget( m_currentValueLabel, 2, 0 );
00137 
00138   m_newValueDesc = new QLabel( m_resultFrame, "m_newValueDesc" );
00139   m_newValueDesc->setText( i18n( "New value:" ) );
00140 
00141   m_resultFrameLayout->addWidget( m_newValueDesc, 1, 0 );
00142 
00143   m_newValue = new QLabel( m_resultFrame, "m_newValue" );
00144   m_newValue->setText( "m_targetValueEdit" );
00145 
00146   m_resultFrameLayout->addWidget( m_newValue, 1, 1 );
00147 
00148   m_currentValue = new QLabel( m_resultFrame, "m_currentValue" );
00149   m_currentValue->setText( "m_currentValue" );
00150 
00151   m_resultFrameLayout->addWidget( m_currentValue, 2, 1 );
00152 
00153   m_resultText = new QLabel( m_resultFrame, "m_resultText" );
00154   m_resultText->setText( "Goal seeking with cell <cell> found <a | no> solution:" );
00155   m_resultText->setAlignment( int( QLabel::WordBreak | QLabel::AlignVCenter ) );
00156 
00157   m_resultFrameLayout->addMultiCellWidget( m_resultText, 0, 0, 0, 1 );
00158 
00159   //  GoalSeekDialogLayout->addWidget( m_resultFrame, 1, 0 );
00160 
00161   m_resultFrame->hide();
00162 
00163   m_sheetName = m_pView->activeSheet()->sheetName();
00164 
00165   // Allow the user to select cells on the spreadsheet.
00166   m_pView->canvasWidget()->startChoose();
00167 
00168   qApp->installEventFilter( this );
00169 
00170   // signals and slots connections
00171   connect( m_buttonOk, SIGNAL( clicked() ), this, SLOT( buttonOkClicked() ) );
00172   connect( m_buttonCancel, SIGNAL( clicked() ), this, SLOT( buttonCancelClicked() ) );
00173 
00174   connect( m_pView->choice(), SIGNAL(changed(const Region&)),
00175            this, SLOT(slotSelectionChanged()));
00176 
00177   // tab order
00178   setTabOrder( m_targetEdit,      m_targetValueEdit );
00179   setTabOrder( m_targetValueEdit, m_sourceEdit );
00180   setTabOrder( m_sourceEdit,      m_buttonOk );
00181   setTabOrder( m_buttonOk,        m_buttonCancel );
00182 }
00183 
00184 GoalSeekDialog::~GoalSeekDialog()
00185 {
00186   kdDebug() << "~GoalSeekDialog" << endl;
00187 
00188   if ( !m_restored )
00189   {
00190     m_pView->doc()->emitBeginOperation( false );
00191     m_sourceCell->setValue(m_oldSource);
00192     m_targetCell->setCalcDirtyFlag();
00193     m_targetCell->calc();
00194     m_pView->slotUpdateView( m_pView->activeSheet() );
00195   }
00196 }
00197 
00198 bool GoalSeekDialog::eventFilter( QObject* obj, QEvent* ev )
00199 {
00200   if ( obj == m_targetValueEdit && ev->type() == QEvent::FocusIn )
00201     m_focus = m_targetValueEdit;
00202   else if ( obj == m_targetEdit && ev->type() == QEvent::FocusIn )
00203     m_focus = m_targetEdit;
00204   else if ( obj == m_sourceEdit && ev->type() == QEvent::FocusIn )
00205     m_focus = m_sourceEdit;
00206   else
00207     return FALSE;
00208 
00209   if ( m_focus )
00210     m_pView->canvasWidget()->startChoose();
00211 
00212   return FALSE;
00213 }
00214 
00215 void GoalSeekDialog::closeEvent ( QCloseEvent * e )
00216 {
00217   e->accept();
00218 }
00219 
00220 void GoalSeekDialog::slotSelectionChanged()
00221 {
00222   if ( !m_focus )
00223     return;
00224 
00225   if (m_pView->choice()->isValid())
00226   {
00227     QString area = m_pView->choice()->name();
00228     m_focus->setText( area );
00229   }
00230 }
00231 
00232 void GoalSeekDialog::buttonOkClicked()
00233 {
00234   Doc * pDoc = m_pView->doc();
00235   pDoc->emitBeginOperation( false );
00236   if (m_maxIter > 0)
00237   {
00238     Sheet * sheet = m_pView->activeSheet();
00239 
00240     Point source( m_sourceEdit->text(), sheet->workbook(), sheet );
00241     if (!source.isValid())
00242     {
00243       KMessageBox::error( this, i18n("Cell reference is invalid.") );
00244       m_sourceEdit->selectAll();
00245       m_sourceEdit->setFocus();
00246 
00247       m_pView->slotUpdateView( m_pView->activeSheet() );
00248       return;
00249     }
00250 
00251     Point target( m_targetEdit->text(), sheet->workbook(), sheet );
00252     if (!target.isValid())
00253     {
00254       KMessageBox::error( this, i18n("Cell reference is invalid.") );
00255       m_targetEdit->selectAll();
00256       m_targetEdit->setFocus();
00257 
00258       m_pView->slotUpdateView( m_pView->activeSheet() );
00259       return;
00260     }
00261 
00262     bool ok = false;
00263     double goal = m_pView->doc()->locale()->readNumber(m_targetValueEdit->text(), &ok );
00264     if ( !ok )
00265     {
00266       KMessageBox::error( this, i18n("Target value is invalid.") );
00267       m_targetValueEdit->selectAll();
00268       m_targetValueEdit->setFocus();
00269 
00270       m_pView->slotUpdateView( m_pView->activeSheet() );
00271       return;
00272     }
00273 
00274     m_sourceCell = source.cell();
00275     m_targetCell = target.cell();
00276 
00277     if ( !m_sourceCell->value().isNumber() )
00278     {
00279       KMessageBox::error( this, i18n("Source cell must contain a numeric value.") );
00280       m_sourceEdit->selectAll();
00281       m_sourceEdit->setFocus();
00282 
00283       m_pView->slotUpdateView( m_pView->activeSheet() );
00284       return;
00285     }
00286 
00287     if ( !m_targetCell->isFormula() )
00288     {
00289       KMessageBox::error( this, i18n("Target cell must contain a formula.") );
00290       m_targetEdit->selectAll();
00291       m_targetEdit->setFocus();
00292 
00293       m_pView->slotUpdateView( m_pView->activeSheet() );
00294       return;
00295     }
00296 
00297     m_buttonOk->setText( i18n("&OK") );
00298     m_buttonOk->setEnabled(false);
00299     m_buttonCancel->setEnabled(false);
00300     GoalSeekDialogLayout->addWidget( m_resultFrame, 0, 0 );
00301     m_startFrame->hide();
00302     m_resultFrame->show();
00303     if ( m_startFrame->width() > 350 )
00304       m_resultFrame->setMinimumWidth( m_startFrame->width() );
00305 
00306     m_restored = false;
00307 
00308     startCalc( m_sourceCell->value().asFloat(), goal );
00309     m_pView->slotUpdateView( m_pView->activeSheet() );
00310 
00311     return;
00312   }
00313   else
00314   {
00315     if ( !pDoc->undoLocked() )
00316     {
00317       UndoSetText * undo
00318         = new UndoSetText( pDoc, m_pView->activeSheet(), QString::number(m_oldSource),
00319                                   m_sourceCell->column(), m_sourceCell->row(),
00320                                   m_sourceCell->formatType() );
00321 
00322       pDoc->addCommand( undo );
00323     }
00324 
00325     m_restored = true;
00326   }
00327   chooseCleanup();
00328 
00329   m_pView->slotUpdateView( m_pView->activeSheet() );
00330   accept();
00331 }
00332 
00333 void GoalSeekDialog::buttonCancelClicked()
00334 {
00335   if ( !m_restored )
00336   {
00337     m_pView->doc()->emitBeginOperation( false );
00338     m_sourceCell->setValue(m_oldSource);
00339     m_targetCell->setCalcDirtyFlag();
00340     m_targetCell->calc();
00341     m_restored = true;
00342     m_pView->slotUpdateView( m_pView->activeSheet() );
00343   }
00344 
00345   chooseCleanup();
00346   reject();
00347 }
00348 
00349 void GoalSeekDialog::chooseCleanup()
00350 {
00351   m_pView->canvasWidget()->endChoose();
00352 
00353   Sheet * sheet = 0;
00354 
00355   // Switch back to the old sheet
00356   if ( m_pView->activeSheet()->sheetName() !=  m_sheetName )
00357   {
00358     sheet = m_pView->doc()->map()->findSheet( m_sheetName );
00359     if ( sheet )
00360       m_pView->setActiveSheet( sheet );
00361   }
00362   else
00363     sheet = m_pView->activeSheet();
00364 
00365   // Revert the marker to its original position
00366   m_pView->selectionInfo()->initialize(QRect(m_marker, m_anchor));//, sheet );
00367 }
00368 
00369 
00370 void GoalSeekDialog::startCalc(double _start, double _goal)
00371 {
00372   m_resultText->setText( i18n( "Starting..." ) );
00373   m_newValueDesc->setText( i18n( "Iteration:" ) );
00374 
00375   // lets be optimistic
00376   bool ok = true;
00377 
00378   // TODO: make this configurable
00379   double eps = 0.0000001;
00380 
00381   double startA = 0.0, startB;
00382   double resultA, resultB;
00383 
00384   // save old value
00385   m_oldSource = m_sourceCell->value().asFloat();
00386   resultA = _goal;
00387 
00388   // initialize start value
00389   startB = _start;
00390   double x = startB + 0.5;
00391 
00392   // while the result is not close enough to zero
00393   // or while the max number of iterations is not reached...
00394   while ( fabs( resultA ) > eps && ( m_maxIter >= 0 ) )
00395   {
00396     startA = startB;
00397     startB = x;
00398 
00399     m_sourceCell->setValue(startA);
00400     //    m_sourceCell->updateDepending();
00401     m_sourceCell->setCalcDirtyFlag();
00402     m_targetCell->calc( false );
00403     resultA = m_targetCell->value().asFloat() - _goal;
00404     //    kdDebug() << "Target A: " << m_targetCell->value().asFloat() << ", " << m_targetCell->text() << " Calc: " << resultA << endl;
00405 
00406     m_sourceCell->setValue(startB);
00407     //    m_sourceCell->updateDepending();
00408     m_sourceCell->setCalcDirtyFlag();
00409     m_targetCell->calc( false );
00410     resultB = m_targetCell->value().asFloat() - _goal;
00411     /*
00412       kdDebug() << "Target B: " << m_targetCell->value().asFloat() << ", " << m_targetCell->text() << " Calc: " << resultB << endl;
00413 
00414       kdDebug() << "Iteration: " << m_maxIter << ", StartA: " << startA
00415               << ", ResultA: " << resultA << " (eps: " << eps << "), StartB: "
00416               << startB << ", ResultB: " << resultB << endl;
00417     */
00418 
00419     // find zero with secant method (rough implementation was provided by Franz-Xaver Meier):
00420     // if the function returns the same for two different
00421     // values we have something like a horizontal line
00422     // => can't get zero.
00423     if ( resultB == resultA )
00424     {
00425       //      kdDebug() << " resultA == resultB" << endl;
00426       if ( fabs( resultA ) < eps )
00427       {
00428         ok = true;
00429         break;
00430       }
00431 
00432       ok = false;
00433       break;
00434     }
00435 
00436     // Point of intersection of secant with x-axis
00437     x = ( startA * resultB - startB * resultA ) / ( resultB - resultA );
00438 
00439     if ( fabs(x) > 100000000 )
00440     {
00441       //      kdDebug() << "fabs(x) > 100000000: " << x << endl;
00442       ok = false;
00443       break;
00444     }
00445 
00446     //    kdDebug() << "X: " << x << ", fabs (resultA): " << fabs(resultA) << ", Real start: " << startA << ", Real result: " << resultA << ", Iteration: " << m_maxIter << endl;
00447 
00448     --m_maxIter;
00449     if ( m_maxIter % 20 == 0 )
00450       m_newValue->setText( QString::number(m_maxIter) );
00451   }
00452 
00453   m_newValueDesc->setText( i18n( "New value:" ) );
00454   if ( ok )
00455   {
00456     m_sourceCell->setValue( startA );
00457     m_sourceCell->setCalcDirtyFlag();
00458     m_sourceCell->sheet()->setRegionPaintDirty(m_sourceCell->cellRect());
00459     //    m_targetCell->setCalcDirtyFlag();
00460     m_targetCell->calc( false );
00461 
00462     m_resultText->setText( i18n( "Goal seeking with cell %1 found a solution:" ).arg( m_sourceEdit->text() ) );
00463     m_newValue->setText( m_pView->doc()->locale()->formatNumber( startA ) );
00464     m_currentValue->setText( m_pView->doc()->locale()->formatNumber( m_oldSource ) );
00465     m_restored = false;
00466   }
00467   else
00468   {
00469     // restore the old value
00470     m_sourceCell->setValue( m_oldSource );
00471     m_targetCell->setCalcDirtyFlag();
00472     m_sourceCell->sheet()->setRegionPaintDirty(m_sourceCell->cellRect());
00473     m_targetCell->calc( false );
00474     m_resultText->setText( i18n( "Goal seeking with cell %1 has found NO solution." ).arg( m_sourceEdit->text() ) );
00475     m_newValue->setText( "" );
00476     m_currentValue->setText( m_pView->doc()->locale()->formatNumber( m_oldSource ) );
00477     m_restored = true;
00478   }
00479 
00480   m_buttonOk->setEnabled( true );
00481   m_buttonCancel->setEnabled( true );
00482   m_maxIter = 0;
00483 }
00484 
00485 #include "kspread_dlg_goalseek.moc"
00486 
KDE Home | KDE Accessibility Home | Description of Access Keys