kspread_dlg_cons.cpp

/* This file is part of the KDE project
   Copyright (C) 2002-2003 Norbert Andres <nandres@web.de>
             (C) 2002 Ariya Hidayat <ariya@kde.org> 
             (C) 2002 Philipp Mueller <philipp.mueller@gmx.de>
             (C) 2002 John Dailey <dailey@vt.edu>
             (C) 2000-2001 Werner Trobin <trobin@kde.org>
             (C) 2000-2001 Laurent Montel <montel@kde.org>
             (C) 1999-2002 David Faure <faure@kde.org>
             (C) 1999 Stephan Kulow <coolo@kde.org> 
             (C) 1999 Reginald Stadlbauer <reggie@kde.org>
             (C) 1998-1999 Torben Weis <weis@kde.org>
 
   This library is free software; you can redistribute it and/or
   modify it under the terms of the GNU Library General Public
   License as published by the Free Software Foundation; either
   version 2 of the License, or (at your option) any later version.
 
   This library is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
   Library General Public License for more details.
 
   You should have received a copy of the GNU Library General Public License
   along with this library; see the file COPYING.LIB.  If not, write to
   the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
 * Boston, MA 02110-1301, USA.
*/
 
#include <assert.h>
 
#include <tqcheckbox.h>
#include <tqcombobox.h>
#include <tqlabel.h>
#include <tqlayout.h>
#include <tqpushbutton.h>
 
#include <kdebug.h>
#include <kdialogbase.h>
#include <tdemessagebox.h>
 
#include "kspread_dlg_cons.h"
 
#include <kspread_canvas.h>
#include <kspread_doc.h>
#include <kspread_global.h>
#include <kspread_locale.h>
#include "selection.h"
#include <kspread_sheet.h>
#include <kspread_util.h>
#include <kspread_view.h>
 
#include <formula.h>
#include <valueconverter.h>
 
using namespace KSpread;
 
ConsolidateDialog::ConsolidateDialog( View* parent, const char* name )
    : KDialogBase( parent, name, false, i18n("Consolidate"), Ok|Cancel )
{
  m_pView = parent;
 
  TQWidget* page = new TQWidget( this );
  setMainWidget( page );
 
  TQGridLayout *grid1 = new TQGridLayout( page, 12, 2, marginHint(), spacingHint() );
 
  TQLabel* tmpTQLabel;
  tmpTQLabel = new TQLabel( page, "Label_1" );
  grid1->addWidget(tmpTQLabel,0,0);
  tmpTQLabel->setText( i18n("&Function:") );
 
  m_pFunction = new TQComboBox( page );
  grid1->addWidget(m_pFunction,1,0);
  tmpTQLabel->setBuddy(m_pFunction);
 
  m_pFunction->insertItem( i18n("Sum"), Sum );
  m_pFunction->insertItem( i18n("Average"), Average );
  m_pFunction->insertItem( i18n("Count"), Count );
  m_pFunction->insertItem( i18n("Max"), Max );
  m_pFunction->insertItem( i18n("Min"), Min );
  m_pFunction->insertItem( i18n("Product"), Product );
  m_pFunction->insertItem( i18n("Standard Deviation"), StdDev );
  m_pFunction->insertItem( i18n("Variance"), Var );
 
  tmpTQLabel = new TQLabel( page, "Label_1" );
  tmpTQLabel->setText( i18n("Re&ference:") );
  grid1->addWidget(tmpTQLabel,2,0);
 
  m_pRef = new TQLineEdit( page );
  grid1->addWidget(m_pRef,3,0);
  tmpTQLabel->setBuddy(m_pRef);
 
  tmpTQLabel = new TQLabel( page, "Label_1" );
  grid1->addWidget(tmpTQLabel,4,0);
  tmpTQLabel->setText( i18n("&Entered references:") );
 
  m_pRefs = new TQListBox( page );
  grid1->addMultiCellWidget( m_pRefs,5,8,0,0);
  tmpTQLabel->setBuddy(m_pRefs);
 
  m_pRow = new TQCheckBox( i18n("&Description in row"), page );
  grid1->addWidget( m_pRow,9,0);
  m_pCol = new TQCheckBox( i18n("De&scription in column"), page );
  grid1->addWidget(m_pCol,10,0);
  m_pCopy = new TQCheckBox( i18n("Co&py data"), page );
  grid1->addWidget(m_pCopy,11,0);
 
  m_pAdd = new TQPushButton( i18n("&Add"), page );
  grid1->addWidget(m_pAdd,2,1);
  m_pRemove = new TQPushButton( i18n("&Remove"), page );
  grid1->addWidget(m_pRemove,3,1);
 
 
  connect( m_pAdd, TQ_SIGNAL( clicked() ), this, TQ_SLOT( slotAdd() ) );
  connect( m_pRemove, TQ_SIGNAL( clicked() ), this, TQ_SLOT( slotRemove() ) );
  connect( m_pRef, TQ_SIGNAL( returnPressed() ), this, TQ_SLOT( slotReturnPressed() ) );
 
  connect(m_pView->selectionInfo(), TQ_SIGNAL(changed(const Region&)),
          this, TQ_SLOT(slotSelectionChanged()));
}
 
ConsolidateDialog::~ConsolidateDialog()
{
    kdDebug(36001)<<"Consolidate::~Consolidate()\n";
}
 
enum Description { D_ROW, D_COL, D_NONE, D_BOTH };
 
struct st_cell
{
  TQString xdesc;
  TQString ydesc;
  Cell* cell;
  TQString sheet;
  int x;
  int y;
};
 
void ConsolidateDialog::slotOk()
{
  m_pView->doc()->emitBeginOperation( false );
 
  Map *map = m_pView->doc()->map();
 
  Sheet* sheet = m_pView->activeSheet();
  int dx = m_pView->selectionInfo()->selection().left();
  int dy = m_pView->selectionInfo()->selection().top();
 
  TQString function;
 
  switch( m_pFunction->currentItem() )
  {
    case Sum:      function = "SUM"; break;
    case Average:  function = "AVERAGE"; break;
    case Count:    function = "COUNT"; break;
    case Max:      function = "MAX"; break;
    case Min:      function = "MIN"; break;
    case Product:  function = "PRODUCT"; break;
    case StdDev:   function = "STDDEV"; break;
    case Var:      function = "VARIANCE"; break;
    default: break; // bad bad !
  }
 
  TQStringList r = refs();
  TQValueList<Range> ranges;
  TQStringList::Iterator s = r.begin();
  for( ; s != r.end(); ++s )
  {
    Range r( *s, map );
    // TODO: Check for valid
    Q_ASSERT( r.isValid() );
 
    if ( r.sheet() == 0 )
    {
      r.setSheet(sheet);
      r.setSheetName(sheet->sheetName());
    }
    ranges.append( r  );
  }
 
  Description desc;
  if ( m_pRow->isChecked() && m_pCol->isChecked() )
    desc = D_BOTH;
  else if ( m_pRow->isChecked() )
    desc = D_ROW;
  else if ( m_pCol->isChecked() )
    desc = D_COL;
  else
    desc = D_NONE;
 
  // Check whether all ranges have same size
  Q_ASSERT( ranges.count() > 0 );
  TQValueList<Range>::Iterator it = ranges.begin();
  int w = (*it).range().right() - (*it).range().left() + 1;
  int h = (*it).range().bottom() - (*it).range().top() + 1;
  if ( w <= ( ( desc == D_BOTH || desc == D_COL ) ? 1 : 0 ) ||
       h <= ( ( desc == D_BOTH || desc == D_ROW ) ? 1 : 0 ) )
  {
    m_pView->slotUpdateView( m_pView->activeSheet() );
    KMessageBox::error( this, i18n( "The range\n%1\nis too small" ).arg( *( r.begin() ) ));
    return;
  }
 
  if( (*it).range().bottom()==KS_rowMax || (*it).range().right()== KS_colMax )
  {
    m_pView->slotUpdateView( m_pView->activeSheet() );
    KMessageBox::error( this, i18n( "The range\n%1\nis too large" ).arg( *( r.begin() ) ));
    return;
  }
 
  ++it;
  int i = 1;
  for( ; it != ranges.end(); ++it, i++ )
  {
    TQRect currentRange=(*it).range();
    
    int w2 = currentRange.right() - currentRange.left() + 1;
    int h2 = currentRange.bottom() - currentRange.top() + 1;
    
    if(currentRange.bottom()==KS_rowMax || currentRange.right()== KS_colMax)
    {
      m_pView->slotUpdateView( m_pView->activeSheet() );
      KMessageBox::error( this, i18n( "The range\n%1\nis too large" ).arg( r[i]));
      return;
    }
    if ( ( desc == D_NONE && ( w != w2 || h != h2 ) ) ||
     ( desc == D_ROW && h != h2 ) ||
     ( desc == D_COL && w != w2 ) )
    {
      m_pView->slotUpdateView( m_pView->activeSheet() );
      TQString tmp = i18n( "The ranges\n%1\nand\n%2\nhave different size").arg( *( r.begin() ) ).arg( r[i] );
      KMessageBox::error( this, tmp);
      return;
    }
  }
 
  // Create the consolidation sheet
  if ( desc == D_NONE )
  {
    // Check whether the destination is part of the source ...
    TQRect dest;
    dest.setCoords( dx, dy, dx + w - 1, dy + h - 1 );
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
    {
      Sheet *t = (*it).sheet();
      Q_ASSERT( t );
      TQRect r;
      
      TQRect currentRange=(*it).range();
      
      r.setCoords( currentRange.left(), currentRange.top(), currentRange.right(), currentRange.bottom() );
      if ( t == sheet && r.intersects( dest ) )
      {
        m_pView->slotUpdateView( m_pView->activeSheet() );
    TQString tmp( i18n("The source tables intersect with the destination table") );
    KMessageBox::error( this, tmp);
    return;
      }
    }
 
    for( int x = 0; x < w; x++ )
    {
      for( int y = 0; y < h; y++ )
      {
        bool novalue=true;
        TQString formula = "=" + function + "(";
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
        {
      Sheet *t = (*it).sheet();
      assert( t );
      Cell *c = t->cellAt( x + (*it).range().left(), y + (*it).range().top() );
          if(!c->isDefault())
                novalue=false;
      if ( it != ranges.begin() )
        formula += ";";
      formula += (*it).sheetName() + "!";
      formula += c->name();
    }
    formula += ")";
 
        if(!novalue)
      sheet->setText( dy + y, dx + x,
            m_pCopy->isChecked() ? evaluate( formula, sheet ) : formula );
      }
    }
  }
  else if ( desc == D_ROW )
  {
    // Get list of all descriptions in the rows
    TQStringList lst;
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
    {
      Sheet *t = (*it).sheet();
      assert( t );
//      kdDebug(36001) << "FROM " << (*it).range.left() << " to " << (*it).range.right() << endl;
      for( int x = (*it).range().left(); x <= (*it).range().right() ; ++x )
      {
    Cell *c = t->cellAt( x, (*it).range().top() );
    if ( c )
    {
      TQString s = c->value().asString();
      if ( !lst.contains( s ) )
        lst.append( s );
    }
      }
    }
    lst.sort();
 
    // Check whether the destination is part of the source ...
    TQRect dest;
    dest.setCoords( dx, dy, dx + lst.count() - 1, dy + h - 1 );
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
    {
      Sheet *t = (*it).sheet();
      assert( t );
      TQRect r;
      TQRect currentRange=(*it).range();
      r.setCoords( currentRange.left(), currentRange.top(), currentRange.right(), currentRange.bottom() );
      if ( t == sheet && r.intersects( dest ) )
      {
        m_pView->slotUpdateView( m_pView->activeSheet() );
    TQString tmp( i18n("The source tables intersect with the destination table") );
    KMessageBox::error( this, tmp);
    return;
      }
    }
 
    // Now create the consolidation sheet
    int x = 0;
    TQStringList::Iterator s = lst.begin();
    for( ; s != lst.end(); ++s, ++x )
    {
      sheet->setText( dy, dx + x, *s );
 
      for( int y = 1; y < h; ++y )
      {
    int count = 0;
        TQString formula = "=" + function + "(";
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
        {
      for( int i = (*it).range().left(); i <= (*it).range().right(); ++i )
      {
        Sheet *t = (*it).sheet();
        assert( t );
        Cell *c = t->cellAt( i, (*it).range().top() );
        if ( c )
        {
          if ( c->value().asString() == *s )
          {
//      Cell *c2 = t->cellAt( i, y + (*it).range.top() );
        count++;
        if ( it != ranges.begin() )
          formula += ";";
        formula += (*it).sheetName() + "!";
        formula += Cell::name( i, y + (*it).range().top() );
          }
        }
      }
    }
    formula += ")";
 
    sheet->setText( dy + y, dx + x,
          m_pCopy->isChecked() ? evaluate( formula, sheet ) : formula );
      }
    }
  }
  else if ( desc == D_COL )
  {
    // Get list of all descriptions in the columns
    TQStringList lst;
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
    {
      Sheet *t = (*it).sheet();
      assert( t );
      for( int y = (*it).range().top(); y <= (*it).range().bottom() ; ++y )
      {
    Cell *c = t->cellAt( (*it).range().left(), y );
    if ( c )
    {
      TQString s = c->value().asString();
      if ( !s.isEmpty() && lst.find( s ) == lst.end() )
        lst.append( s );
    }
      }
    }
    lst.sort();
 
    // Check whether the destination is part of the source ...
    TQRect dest;
    dest.setCoords( dx, dy, dx + w - 1, dy + lst.count() - 1 );
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
    {
      Sheet *t = (*it).sheet();
      assert( t );
      TQRect r;
      TQRect currentRange=(*it).range();
      r.setCoords( currentRange.left(), currentRange.top(), currentRange.right(), currentRange.bottom() );
      if ( t == sheet && r.intersects( dest ) )
      {
        m_pView->slotUpdateView( m_pView->activeSheet() );
    TQString tmp( i18n("The source tables intersect with the destination table") );
    KMessageBox::error( this, tmp);
    return;
      }
    }
 
    // Now create the consolidation sheet
    int y = 0;
    TQStringList::Iterator s = lst.begin();
    for( ; s != lst.end(); ++s, ++y )
    {
      sheet->setText( dy + y, dx, *s );
 
      for( int x = 1; x < w; ++x )
      {
    int count = 0;
        TQString formula = "=" + function + "(";
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
        {
      for( int i = (*it).range().top(); i <= (*it).range().bottom(); i++ )
      {
        Sheet *t = (*it).sheet();
        assert( t );
        Cell *c = t->cellAt( (*it).range().left(), i );
        if ( c )
        {
          TQString v = c->value().asString();
          if ( !v.isEmpty() && *s == v )
          {
//      Cell *c2 = t->cellAt( x + (*it).range.left(), i );
        count++;
        if ( it != ranges.begin() ) formula += ";";
        formula += (*it).sheetName() + "!";
        formula += Cell::name( i, y + (*it).range().top() );
          }
        }
      }
    }
 
    formula += ")";
 
    sheet->setText( dy + y, dx + x,
          m_pCopy->isChecked() ? evaluate( formula, sheet ) : formula );
      }
    }
  }
  else if ( desc == D_BOTH )
  {
    // Get list of all descriptions in the columns
    TQStringList cols;
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
    {
      Sheet *t = (*it).sheet();
      assert( t );
      for( int y = (*it).range().top() + 1; y <= (*it).range().bottom() ; ++y )
      {
    Cell *c = t->cellAt( (*it).range().left(), y );
    if ( c )
    {
      TQString s = c->value().asString();
      if ( !s.isEmpty() && cols.find( s ) == cols.end() )
        cols.append( s );
    }
      }
    }
    cols.sort();
 
    // Get list of all descriptions in the rows
    TQStringList rows;
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
    {
      Sheet *t = (*it).sheet();
      assert( t );
      for( int x = (*it).range().left() + 1; x <= (*it).range().right() ; ++x )
      {
    Cell *c = t->cellAt( x, (*it).range().top() );
    if ( c )
    {
      TQString s = c->value().asString();
      if ( !s.isEmpty() && rows.find( s ) == rows.end() )
        rows.append( s );
    }
      }
    }
    rows.sort();
 
    // Check whether the destination is part of the source ...
    TQRect dest;
    dest.setCoords( dx, dy, dx + cols.count(), dy + rows.count() );
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
    {
      Sheet *t = (*it).sheet();
      assert( t );
      TQRect r;
      TQRect currentRange=(*it).range();
      r.setCoords( currentRange.left(), currentRange.top(), currentRange.right(), currentRange.bottom() );
      if ( t == sheet && r.intersects( dest ) )
      {
        m_pView->slotUpdateView( m_pView->activeSheet() );
    TQString tmp( i18n("The source tables intersect with the destination table") );
    KMessageBox::error( this, tmp);
    return;
      }
    }
 
    // Fill the list with all interesting cells
    TQValueList<st_cell> lst;
    it = ranges.begin();
    for( ; it != ranges.end(); ++it )
    {
      Sheet *t = (*it).sheet();
      assert( t );
      for( int x = (*it).range().left() + 1; x <= (*it).range().right() ; ++x )
      {
    Cell *c = t->cellAt( x, (*it).range().top() );
    if ( c )
    {
      TQString ydesc = c->value().asString();
      for( int y = (*it).range().top() + 1; y <= (*it).range().bottom() ; ++y )
      {
        Cell *c2 = t->cellAt( (*it).range().left(), y );
        if ( c2 )
        {
          TQString xdesc = c2->value().asString();
          Cell *c3 = t->cellAt( x, y );
          if ( c3 && c3->value().isNumber() )
          {
        st_cell k;
        k.xdesc = xdesc;
        k.ydesc = ydesc;
        k.cell = c3;
        k.sheet = (*it).sheetName();
        k.x = x;
        k.y = y;
        lst.append( k );
          }
        }
      }
    }
      }
    }
 
    // Draw the row description
    int i = 1;
    TQStringList::Iterator s = rows.begin();
    for( ; s != rows.end(); ++s, ++i )
      sheet->setText( dy, dx + i, *s );
 
    // Draw the column description
    i = 1;
    s = cols.begin();
    for( ; s != cols.end(); ++s, ++i )
      sheet->setText( dy + i, dx, *s );
 
    // Draw the data
    int x = 1;
    TQStringList::Iterator ydesc = rows.begin();
    for( ; ydesc != rows.end(); ++ydesc, x++ )
    {
      int y = 1;
      TQStringList::Iterator xdesc = cols.begin();
      for( ; xdesc != cols.end(); ++xdesc, y++ )
      {
    int count = 0;
        TQString formula = "=" + function + "(";
    TQValueList<st_cell>::Iterator lit = lst.begin();
    for( ; lit != lst.end(); ++lit )
    {
      if ( (*lit).xdesc == *xdesc && (*lit).ydesc == *ydesc )
      {
        count++;
        if ( it != ranges.begin() ) formula += ";";
        formula += (*it).sheetName() + "!";
        formula += Cell::name( i, y + (*it).range().top() );
      }
    }
    formula += ")";
 
    sheet->setText( dy + y, dx + x,
          m_pCopy->isChecked() ? evaluate( formula, sheet ) : formula );
      }
    }
  }
  m_pView->updateEditWidget();
  m_pView->slotUpdateView( m_pView->activeSheet() );
  accept();
  delete this;
}
 
void ConsolidateDialog::slotCancel()
{
  reject();
  delete this;
}
 
void ConsolidateDialog::slotAdd()
{
  slotReturnPressed();
}
 
void ConsolidateDialog::slotRemove()
{
  int i = m_pRefs->currentItem();
  if ( i < 0 )
    return;
 
  m_pRefs->removeItem( i );
 
  if ( m_pRefs->count() == 0 )
    actionButton( Ok )->setEnabled( false );
}
 
TQStringList ConsolidateDialog::refs()
{
  TQStringList list;
  int c = m_pRefs->count();
 
  for( int i = 0; i < c; i++ )
    list.append( m_pRefs->text( i ) );
 
  return list;
}
 
void ConsolidateDialog::slotSelectionChanged()
{
  if (!m_pView->selectionInfo()->isValid())
  {
    m_pRef->setText( "" );
    return;
  }
 
  TQString area = m_pView->selectionInfo()->name();
  m_pRef->setText( area );
  m_pRef->setSelection( 0, area.length() );
}
 
void ConsolidateDialog::slotReturnPressed()
{
  TQString txt = m_pRef->text();
 
  Range r( txt, m_pView->doc()->map() );
  if ( !r.isValid() )
  {
    KMessageBox::error( this, i18n("The range\n%1\n is malformed").arg( txt ));
    return;
  }
 
  if ( !txt.isEmpty() )
  {
    m_pRefs->insertItem( txt );
    actionButton( Ok )->setEnabled( true );
  }
}
 
void ConsolidateDialog::closeEvent ( TQCloseEvent * )
{
    delete this;
}
 
TQString ConsolidateDialog::evaluate( const TQString& formula, Sheet* sheet )
{
  TQString result = "###";
  Formula *f = new Formula (sheet);
  f->setExpression (formula);
  if (!f->isValid()) {
    delete f;
    return result;
  }
 
  Value res = f->eval ();
  delete f;
  result = sheet->doc()->converter()->asString (res).asString ();
  return result;
}
 
#include "kspread_dlg_cons.moc"