kspread

kspread_dlg_goalseek.cpp
1/* This file is part of the KDE project
2 Copyright (C) 2002-2003 Norbert Andres <nandres@web.de>
3 (C) 2002-2003 Philipp Mueller <philipp.mueller@gmx.de>
4 (C) 2002 Laurent Montel <montel@kde.org>
5 (C) 2002 John Dailey <dailey@vt.edu>
6 (C) 2002 Ariya Hidayat <ariya@kde.org>
7 (C) 2002 Werner Trobin <trobin@kde.org>
8 (C) 2002 Harri Porten <porten@kde.org>
9
10 This library is free software; you can redistribute it and/or
11 modify it under the terms of the GNU Library General Public
12 License as published by the Free Software Foundation; either
13 version 2 of the License, or (at your option) any later version.
14
15 This library is distributed in the hope that it will be useful,
16 but WITHOUT ANY WARRANTY; without even the implied warranty of
17 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
18 Library General Public License for more details.
19
20 You should have received a copy of the GNU Library General Public License
21 along with this library; see the file COPYING.LIB. If not, write to
22 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
23 * Boston, MA 02110-1301, USA.
24*/
25
26#include "kspread_dlg_goalseek.h"
27
28#include "kspread_canvas.h"
29#include "kspread_cell.h"
30#include "kspread_doc.h"
31#include "kspread_map.h"
32#include "selection.h"
33#include "kspread_sheet.h"
34#include "kspread_undo.h"
35#include "kspread_util.h"
36#include "kspread_view.h"
37
38#include <tdeapplication.h>
39#include <kdebug.h>
40#include <tdelocale.h>
41#include <tdemessagebox.h>
42#include <kstdguiitem.h>
43#include <kpushbutton.h>
44
45#include <tqframe.h>
46#include <tqlabel.h>
47#include <tqlayout.h>
48#include <tqlineedit.h>
49#include <tqtooltip.h>
50#include <tqvariant.h>
51#include <tqwhatsthis.h>
52
53#include <math.h>
54
55using namespace KSpread;
56
57GoalSeekDialog::GoalSeekDialog( View * parent, TQPoint const & marker,
58 const char * name, bool, WFlags fl )
59 : KDialog( parent, name, false, fl ),
60 m_pView( parent ),
61 m_maxIter( 1000 ),
62 m_restored( true ),
63 m_focus(0),
64 m_anchor( m_pView->selectionInfo()->anchor() ),
65 m_marker( m_pView->selectionInfo()->marker() ),
66 m_selection( m_pView->selectionInfo()->selection() )
67{
68 setWFlags( TQt::WDestructiveClose );
69
70 if ( !name )
71 setName( "GoalSeekDialog" );
72
73 resize( 458, 153 );
74 setCaption( i18n( "Goal Seek" ) );
75 setSizeGripEnabled( true );
76
77 GoalSeekDialogLayout = new TQGridLayout( this, 1, 1, 11, 6, "GoalSeekDialogLayout");
78
79 m_startFrame = new TQFrame( this, "m_startFrame" );
80 m_startFrame->setFrameShape( TQFrame::StyledPanel );
81 m_startFrame->setFrameShadow( TQFrame::Raised );
82 m_startFrameLayout = new TQGridLayout( m_startFrame, 1, 1, 11, 6, "m_startFrameLayout");
83
84 TQLabel * TextLabel4 = new TQLabel( m_startFrame, "TextLabel4" );
85 TextLabel4->setText( i18n( "To value:" ) );
86 m_startFrameLayout->addWidget( TextLabel4, 1, 0 );
87
88 m_targetValueEdit = new TQLineEdit( m_startFrame, "m_targetValueEdit" );
89 m_startFrameLayout->addWidget( m_targetValueEdit, 1, 1 );
90
91 m_targetEdit = new TQLineEdit( m_startFrame, "m_targetEdit" );
92 m_startFrameLayout->addWidget( m_targetEdit, 0, 1 );
93 m_targetEdit->setText( Cell::name( marker.x(), marker.y() ) );
94
95 m_sourceEdit = new TQLineEdit( m_startFrame, "m_sourceEdit" );
96 m_startFrameLayout->addWidget( m_sourceEdit, 2, 1 );
97
98 TQLabel * TextLabel5 = new TQLabel( m_startFrame, "TextLabel5" );
99 TextLabel5->setText( i18n( "By changing cell:" ) );
100
101 m_startFrameLayout->addWidget( TextLabel5, 2, 0 );
102
103 TQLabel * TextLabel3 = new TQLabel( m_startFrame, "TextLabel3" );
104 TextLabel3->setText( i18n( "Set cell:" ) );
105
106 m_startFrameLayout->addWidget( TextLabel3, 0, 0 );
107 GoalSeekDialogLayout->addWidget( m_startFrame, 0, 0 );
108
109 TQVBoxLayout * Layout5 = new TQVBoxLayout( 0, 0, 6, "Layout5");
110
111 m_buttonOk = new TQPushButton( this, "m_buttonOk" );
112 m_buttonOk->setText( i18n( "&Start" ) );
113 m_buttonOk->setAccel( 276824143 );
114 m_buttonOk->setAutoDefault( true );
115 m_buttonOk->setDefault( true );
116 Layout5->addWidget( m_buttonOk );
117
118 m_buttonCancel = new KPushButton( KStdGuiItem::cancel(), this, "m_buttonCancel" );
119 m_buttonCancel->setAccel( 276824131 );
120 m_buttonCancel->setAutoDefault( true );
121 Layout5->addWidget( m_buttonCancel );
122 TQSpacerItem* spacer = new TQSpacerItem( 20, 20, TQSizePolicy::Minimum, TQSizePolicy::Expanding );
123 Layout5->addItem( spacer );
124
125 GoalSeekDialogLayout->addMultiCellLayout( Layout5, 0, 1, 1, 1 );
126
127 m_resultFrame = new TQFrame( this, "m_resultFrame" );
128 m_resultFrame->setFrameShape( TQFrame::StyledPanel );
129 m_resultFrame->setFrameShadow( TQFrame::Raised );
130 m_resultFrame->setMinimumWidth( 350 );
131 m_resultFrameLayout = new TQGridLayout( m_resultFrame, 1, 1, 11, 6, "m_resultFrameLayout");
132
133 m_currentValueLabel = new TQLabel( m_resultFrame, "m_currentValueLabel" );
134 m_currentValueLabel->setText( i18n( "Current value:" ) );
135
136 m_resultFrameLayout->addWidget( m_currentValueLabel, 2, 0 );
137
138 m_newValueDesc = new TQLabel( m_resultFrame, "m_newValueDesc" );
139 m_newValueDesc->setText( i18n( "New value:" ) );
140
141 m_resultFrameLayout->addWidget( m_newValueDesc, 1, 0 );
142
143 m_newValue = new TQLabel( m_resultFrame, "m_newValue" );
144 m_newValue->setText( "m_targetValueEdit" );
145
146 m_resultFrameLayout->addWidget( m_newValue, 1, 1 );
147
148 m_currentValue = new TQLabel( m_resultFrame, "m_currentValue" );
149 m_currentValue->setText( "m_currentValue" );
150
151 m_resultFrameLayout->addWidget( m_currentValue, 2, 1 );
152
153 m_resultText = new TQLabel( m_resultFrame, "m_resultText" );
154 m_resultText->setText( "Goal seeking with cell <cell> found <a | no> solution:" );
155 m_resultText->setAlignment( int( TQLabel::WordBreak | TQLabel::AlignVCenter ) );
156
157 m_resultFrameLayout->addMultiCellWidget( m_resultText, 0, 0, 0, 1 );
158
159 // GoalSeekDialogLayout->addWidget( m_resultFrame, 1, 0 );
160
161 m_resultFrame->hide();
162
163 m_sheetName = m_pView->activeSheet()->sheetName();
164
165 // Allow the user to select cells on the spreadsheet.
166 m_pView->canvasWidget()->startChoose();
167
168 tqApp->installEventFilter( this );
169
170 // signals and slots connections
171 connect( m_buttonOk, TQ_SIGNAL( clicked() ), this, TQ_SLOT( buttonOkClicked() ) );
172 connect( m_buttonCancel, TQ_SIGNAL( clicked() ), this, TQ_SLOT( buttonCancelClicked() ) );
173
174 connect( m_pView->choice(), TQ_SIGNAL(changed(const Region&)),
175 this, TQ_SLOT(slotSelectionChanged()));
176
177 // tab order
178 setTabOrder( m_targetEdit, m_targetValueEdit );
179 setTabOrder( m_targetValueEdit, m_sourceEdit );
180 setTabOrder( m_sourceEdit, m_buttonOk );
181 setTabOrder( m_buttonOk, m_buttonCancel );
182}
183
184GoalSeekDialog::~GoalSeekDialog()
185{
186 kdDebug() << "~GoalSeekDialog" << endl;
187
188 if ( !m_restored )
189 {
190 m_pView->doc()->emitBeginOperation( false );
191 m_sourceCell->setValue(m_oldSource);
192 m_targetCell->setCalcDirtyFlag();
193 m_targetCell->calc();
194 m_pView->slotUpdateView( m_pView->activeSheet() );
195 }
196}
197
198bool GoalSeekDialog::eventFilter( TQObject* obj, TQEvent* ev )
199{
200 if ( obj == m_targetValueEdit && ev->type() == TQEvent::FocusIn )
201 m_focus = m_targetValueEdit;
202 else if ( obj == m_targetEdit && ev->type() == TQEvent::FocusIn )
203 m_focus = m_targetEdit;
204 else if ( obj == m_sourceEdit && ev->type() == TQEvent::FocusIn )
205 m_focus = m_sourceEdit;
206 else
207 return false;
208
209 if ( m_focus )
210 m_pView->canvasWidget()->startChoose();
211
212 return false;
213}
214
215void GoalSeekDialog::closeEvent ( TQCloseEvent * e )
216{
217 e->accept();
218}
219
220void GoalSeekDialog::slotSelectionChanged()
221{
222 if ( !m_focus )
223 return;
224
225 if (m_pView->choice()->isValid())
226 {
227 TQString area = m_pView->choice()->name();
228 m_focus->setText( area );
229 }
230}
231
232void GoalSeekDialog::buttonOkClicked()
233{
234 Doc * pDoc = m_pView->doc();
235 pDoc->emitBeginOperation( false );
236 if (m_maxIter > 0)
237 {
238 Sheet * sheet = m_pView->activeSheet();
239
240 Point source( m_sourceEdit->text(), sheet->workbook(), sheet );
241 if (!source.isValid())
242 {
243 KMessageBox::error( this, i18n("Cell reference is invalid.") );
244 m_sourceEdit->selectAll();
245 m_sourceEdit->setFocus();
246
247 m_pView->slotUpdateView( m_pView->activeSheet() );
248 return;
249 }
250
251 Point target( m_targetEdit->text(), sheet->workbook(), sheet );
252 if (!target.isValid())
253 {
254 KMessageBox::error( this, i18n("Cell reference is invalid.") );
255 m_targetEdit->selectAll();
256 m_targetEdit->setFocus();
257
258 m_pView->slotUpdateView( m_pView->activeSheet() );
259 return;
260 }
261
262 bool ok = false;
263 double goal = m_pView->doc()->locale()->readNumber(m_targetValueEdit->text(), &ok );
264 if ( !ok )
265 {
266 KMessageBox::error( this, i18n("Target value is invalid.") );
267 m_targetValueEdit->selectAll();
268 m_targetValueEdit->setFocus();
269
270 m_pView->slotUpdateView( m_pView->activeSheet() );
271 return;
272 }
273
274 m_sourceCell = source.cell();
275 m_targetCell = target.cell();
276
277 if ( !m_sourceCell->value().isNumber() )
278 {
279 KMessageBox::error( this, i18n("Source cell must contain a numeric value.") );
280 m_sourceEdit->selectAll();
281 m_sourceEdit->setFocus();
282
283 m_pView->slotUpdateView( m_pView->activeSheet() );
284 return;
285 }
286
287 if ( !m_targetCell->isFormula() )
288 {
289 KMessageBox::error( this, i18n("Target cell must contain a formula.") );
290 m_targetEdit->selectAll();
291 m_targetEdit->setFocus();
292
293 m_pView->slotUpdateView( m_pView->activeSheet() );
294 return;
295 }
296
297 m_buttonOk->setText( i18n("&OK") );
298 m_buttonOk->setEnabled(false);
299 m_buttonCancel->setEnabled(false);
300 GoalSeekDialogLayout->addWidget( m_resultFrame, 0, 0 );
301 m_startFrame->hide();
302 m_resultFrame->show();
303 if ( m_startFrame->width() > 350 )
304 m_resultFrame->setMinimumWidth( m_startFrame->width() );
305
306 m_restored = false;
307
308 startCalc( m_sourceCell->value().asFloat(), goal );
309 m_pView->slotUpdateView( m_pView->activeSheet() );
310
311 return;
312 }
313 else
314 {
315 if ( !pDoc->undoLocked() )
316 {
317 UndoSetText * undo
318 = new UndoSetText( pDoc, m_pView->activeSheet(), TQString::number(m_oldSource),
319 m_sourceCell->column(), m_sourceCell->row(),
320 m_sourceCell->formatType() );
321
322 pDoc->addCommand( undo );
323 }
324
325 m_restored = true;
326 }
327 chooseCleanup();
328
329 m_pView->slotUpdateView( m_pView->activeSheet() );
330 accept();
331}
332
333void GoalSeekDialog::buttonCancelClicked()
334{
335 if ( !m_restored )
336 {
337 m_pView->doc()->emitBeginOperation( false );
338 m_sourceCell->setValue(m_oldSource);
339 m_targetCell->setCalcDirtyFlag();
340 m_targetCell->calc();
341 m_restored = true;
342 m_pView->slotUpdateView( m_pView->activeSheet() );
343 }
344
345 chooseCleanup();
346 reject();
347}
348
349void GoalSeekDialog::chooseCleanup()
350{
351 m_pView->canvasWidget()->endChoose();
352
353 Sheet * sheet = 0;
354
355 // Switch back to the old sheet
356 if ( m_pView->activeSheet()->sheetName() != m_sheetName )
357 {
358 sheet = m_pView->doc()->map()->findSheet( m_sheetName );
359 if ( sheet )
360 m_pView->setActiveSheet( sheet );
361 }
362 else
363 sheet = m_pView->activeSheet();
364
365 // Revert the marker to its original position
366 m_pView->selectionInfo()->initialize(TQRect(m_marker, m_anchor));//, sheet );
367}
368
369
370void GoalSeekDialog::startCalc(double _start, double _goal)
371{
372 m_resultText->setText( i18n( "Starting..." ) );
373 m_newValueDesc->setText( i18n( "Iteration:" ) );
374
375 // lets be optimistic
376 bool ok = true;
377
378 // TODO: make this configurable
379 double eps = 0.0000001;
380
381 double startA = 0.0, startB;
382 double resultA, resultB;
383
384 // save old value
385 m_oldSource = m_sourceCell->value().asFloat();
386 resultA = _goal;
387
388 // initialize start value
389 startB = _start;
390 double x = startB + 0.5;
391
392 // while the result is not close enough to zero
393 // or while the max number of iterations is not reached...
394 while ( fabs( resultA ) > eps && ( m_maxIter >= 0 ) )
395 {
396 startA = startB;
397 startB = x;
398
399 m_sourceCell->setValue(startA);
400 // m_sourceCell->updateDepending();
401 m_sourceCell->setCalcDirtyFlag();
402 m_targetCell->calc( false );
403 resultA = m_targetCell->value().asFloat() - _goal;
404 // kdDebug() << "Target A: " << m_targetCell->value().asFloat() << ", " << m_targetCell->text() << " Calc: " << resultA << endl;
405
406 m_sourceCell->setValue(startB);
407 // m_sourceCell->updateDepending();
408 m_sourceCell->setCalcDirtyFlag();
409 m_targetCell->calc( false );
410 resultB = m_targetCell->value().asFloat() - _goal;
411 /*
412 kdDebug() << "Target B: " << m_targetCell->value().asFloat() << ", " << m_targetCell->text() << " Calc: " << resultB << endl;
413
414 kdDebug() << "Iteration: " << m_maxIter << ", StartA: " << startA
415 << ", ResultA: " << resultA << " (eps: " << eps << "), StartB: "
416 << startB << ", ResultB: " << resultB << endl;
417 */
418
419 // find zero with secant method (rough implementation was provided by Franz-Xaver Meier):
420 // if the function returns the same for two different
421 // values we have something like a horizontal line
422 // => can't get zero.
423 if ( resultB == resultA )
424 {
425 // kdDebug() << " resultA == resultB" << endl;
426 if ( fabs( resultA ) < eps )
427 {
428 ok = true;
429 break;
430 }
431
432 ok = false;
433 break;
434 }
435
436 // Point of intersection of secant with x-axis
437 x = ( startA * resultB - startB * resultA ) / ( resultB - resultA );
438
439 if ( fabs(x) > 100000000 )
440 {
441 // kdDebug() << "fabs(x) > 100000000: " << x << endl;
442 ok = false;
443 break;
444 }
445
446 // kdDebug() << "X: " << x << ", fabs (resultA): " << fabs(resultA) << ", Real start: " << startA << ", Real result: " << resultA << ", Iteration: " << m_maxIter << endl;
447
448 --m_maxIter;
449 if ( m_maxIter % 20 == 0 )
450 m_newValue->setText( TQString::number(m_maxIter) );
451 }
452
453 m_newValueDesc->setText( i18n( "New value:" ) );
454 if ( ok )
455 {
456 m_sourceCell->setValue( startA );
457 m_sourceCell->setCalcDirtyFlag();
458 m_sourceCell->sheet()->setRegionPaintDirty(m_sourceCell->cellRect());
459 // m_targetCell->setCalcDirtyFlag();
460 m_targetCell->calc( false );
461
462 m_resultText->setText( i18n( "Goal seeking with cell %1 found a solution:" ).arg( m_sourceEdit->text() ) );
463 m_newValue->setText( m_pView->doc()->locale()->formatNumber( startA ) );
464 m_currentValue->setText( m_pView->doc()->locale()->formatNumber( m_oldSource ) );
465 m_restored = false;
466 }
467 else
468 {
469 // restore the old value
470 m_sourceCell->setValue( m_oldSource );
471 m_targetCell->setCalcDirtyFlag();
472 m_sourceCell->sheet()->setRegionPaintDirty(m_sourceCell->cellRect());
473 m_targetCell->calc( false );
474 m_resultText->setText( i18n( "Goal seeking with cell %1 has found NO solution." ).arg( m_sourceEdit->text() ) );
475 m_newValue->setText( "" );
476 m_currentValue->setText( m_pView->doc()->locale()->formatNumber( m_oldSource ) );
477 m_restored = true;
478 }
479
480 m_buttonOk->setEnabled( true );
481 m_buttonCancel->setEnabled( true );
482 m_maxIter = 0;
483}
484
485#include "kspread_dlg_goalseek.moc"
486
KSpread::Cell::name
TQString name() const
Returns the name of the cell.
Definition kspread_cell.cpp:369
KSpread::Doc::undoLocked
bool undoLocked() const
Returns true if undo buffer is locked.
Definition kspread_doc.cpp:1609
KSpread::Doc::emitBeginOperation
void emitBeginOperation(bool waitCursor)
Functions to begin a kspread 'operation'.
Definition kspread_doc.cpp:2186
KSpread::Doc::addCommand
void addCommand(KCommand *command)
Adds a command to the command history.
Definition kspread_doc.cpp:1560
KSpread::Map::findSheet
Sheet * findSheet(const TQString &_name)
searches for a sheet named _name
Definition kspread_map.cpp:401
KSpread::Region
The one for all class for points and ranges.
Definition region.h:44
KSpread::View
The View class displays a KSpread document.
Definition kspread_view.h:188
KSpread
The KSpread namespace.
Definition commands.h:39