sc/source/ui/view/dbfunc.cxx

   1 /*************************************************************************
   2  *
   3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
   4  *
   5  * Copyright 2008 by Sun Microsystems, Inc.
   6  *
   7  * OpenOffice.org - a multi-platform office productivity suite
   8  *
   9  * $RCSfile: dbfunc.cxx,v $
  10  * $Revision: 1.15.126.1 $
  11  *
  12  * This file is part of OpenOffice.org.
  13  *
  14  * OpenOffice.org is free software: you can redistribute it and/or modify
  15  * it under the terms of the GNU Lesser General Public License version 3
  16  * only, as published by the Free Software Foundation.
  17  *
  18  * OpenOffice.org is distributed in the hope that it will be useful,
  19  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  20  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  21  * GNU Lesser General Public License version 3 for more details
  22  * (a copy is included in the LICENSE file that accompanied this code).
  23  *
  24  * You should have received a copy of the GNU Lesser General Public License
  25  * version 3 along with OpenOffice.org.  If not, see
  26  * <http://www.openoffice.org/license.html>
  27  * for a copy of the LGPLv3 License.
  28  *
  29  ************************************************************************/
  30
  31 // MARKER(update_precomp.py): autogen include statement, do not remove
  32 #include "precompiled_sc.hxx"
  33
  34
  35
  36 // INCLUDE ---------------------------------------------------------------
  37
  38 #include "scitems.hxx"
  39 #include <sfx2/app.hxx>
  40 #include <sfx2/bindings.hxx>
  41 #include <vcl/msgbox.hxx>
  42
  43 #include <com/sun/star/sdbc/XResultSet.hpp>
  44
  45 #include "dbfunc.hxx"
  46 #include "docsh.hxx"
  47 #include "attrib.hxx"
  48 #include "sc.hrc"
  49 #include "undodat.hxx"
  50 #include "dbcolect.hxx"
  51 #include "globstr.hrc"
  52 #include "global.hxx"
  53 #include "dbdocfun.hxx"
  54 #include "editable.hxx"
  55
  56 //==================================================================
  57
  58 ScDBFunc::ScDBFunc( Window* pParent, ScDocShell& rDocSh, ScTabViewShell* pViewShell ) :
  59     ScViewFunc( pParent, rDocSh, pViewShell )
  60 {
  61 }
  62
  63 //UNUSED2008-05  ScDBFunc::ScDBFunc( Window* pParent, const ScDBFunc& rDBFunc, ScTabViewShell* pViewShell ) :
  64 //UNUSED2008-05      ScViewFunc( pParent, rDBFunc, pViewShell )
  65 //UNUSED2008-05  {
  66 //UNUSED2008-05  }
  67
  68 ScDBFunc::~ScDBFunc()
  69 {
  70 }
  71
  72 //
  73 //              Hilfsfunktionen
  74 //
  75
  76 void ScDBFunc::GotoDBArea( const String& rDBName )
  77 {
  78     ScDocument* pDoc = GetViewData()->GetDocument();
  79     ScDBCollection* pDBCol = pDoc->GetDBCollection();
  80
  81     USHORT nFoundAt = 0;
  82     if ( pDBCol->SearchName( rDBName, nFoundAt ) )
  83     {
  84         ScDBData* pData = (*pDBCol)[nFoundAt];
  85         DBG_ASSERT( pData, "GotoDBArea: Datenbankbereich nicht gefunden!" );
  86
  87         if ( pData )
  88         {
  89             SCTAB nTab = 0;
  90             SCCOL nStartCol = 0;
  91             SCROW nStartRow = 0;
  92             SCCOL nEndCol = 0;
  93             SCROW nEndRow = 0;
  94
  95             pData->GetArea( nTab, nStartCol, nStartRow, nEndCol, nEndRow );
  96             SetTabNo( nTab );
  97
  98             MoveCursorAbs( nStartCol, nStartRow, ScFollowMode( SC_FOLLOW_JUMP ),
  99                                FALSE, FALSE );  // bShift,bControl
 100             DoneBlockMode();
 101             InitBlockMode( nStartCol, nStartRow, nTab );
 102             MarkCursor( nEndCol, nEndRow, nTab );
 103             SelectionChanged();
 104         }
 105     }
 106 }
 107
 108 //      aktuellen Datenbereich fuer Sortieren / Filtern suchen
 109
 110 ScDBData* ScDBFunc::GetDBData( BOOL bMark, ScGetDBMode eMode, bool bShrinkToData, bool bExpandRows )
 111 {
 112     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 113     ScDBData* pData = NULL;
 114     ScRange aRange;
 115     ScMarkType eMarkType = GetViewData()->GetSimpleArea(aRange);
 116     if ( eMarkType == SC_MARK_SIMPLE || eMarkType == SC_MARK_SIMPLE_FILTERED )
 117     {
 118         if (bShrinkToData)
 119         {
 120             // Shrink the range to only include data area.
 121             ScDocument* pDoc = pDocSh->GetDocument();
 122             SCCOL nCol1 = aRange.aStart.Col(), nCol2 = aRange.aEnd.Col();
 123             SCROW nRow1 = aRange.aStart.Row(), nRow2 = aRange.aEnd.Row();
 124             if (pDoc->ShrinkToDataArea(aRange.aStart.Tab(), nCol1, nRow1, nCol2, nRow2))
 125             {
 126                 aRange.aStart.SetCol(nCol1);
 127                 aRange.aEnd.SetCol(nCol2);
 128                 aRange.aStart.SetRow(nRow1);
 129                 aRange.aEnd.SetRow(nRow2);
 130             }
 131         }
 132         pData = pDocSh->GetDBData( aRange, eMode, FALSE );
 133     }
 134     else if ( eMode != SC_DB_OLD )
 135         pData = pDocSh->GetDBData(
 136                     ScRange( GetViewData()->GetCurX(), GetViewData()->GetCurY(),
 137                              GetViewData()->GetTabNo() ),
 138                     eMode, FALSE );
 139     if (!pData)
 140         return NULL;
 141
 142     if (bExpandRows)
 143     {
 144         // Dynamically expand rows to include any new data rows that are
 145         // immediately below the original range.
 146         GetViewData()->GetDocument()->UpdateDynamicEndRow(*pData);
 147     }
 148
 149     if (bMark)
 150     {
 151         ScRange aFound;
 152         pData->GetArea(aFound, bExpandRows);
 153         MarkRange( aFound, FALSE );
 154     }
 155     return pData;
 156 }
 157
 158 //      Datenbankbereiche aendern (Dialog)
 159
 160 void ScDBFunc::NotifyCloseDbNameDlg( const ScDBCollection& rNewColl, const List& rDelAreaList )
 161 {
 162
 163     ScDocShell* pDocShell = GetViewData()->GetDocShell();
 164     ScDocShellModificator aModificator( *pDocShell );
 165     ScDocument* pDoc = pDocShell->GetDocument();
 166     ScDBCollection* pOldColl = pDoc->GetDBCollection();
 167     ScDBCollection* pUndoColl = NULL;
 168     ScDBCollection* pRedoColl = NULL;
 169     const BOOL bRecord (pDoc->IsUndoEnabled());
 170
 171     long nDelCount = rDelAreaList.Count();
 172     for (long nDelPos=0; nDelPos<nDelCount; nDelPos++)
 173     {
 174         ScRange* pEntry = (ScRange*) rDelAreaList.GetObject(nDelPos);
 175
 176         if ( pEntry )
 177         {
 178             ScAddress& rStart = pEntry->aStart;
 179             ScAddress& rEnd   = pEntry->aEnd;
 180             pDocShell->DBAreaDeleted( rStart.Tab(),
 181                                        rStart.Col(), rStart.Row(),
 182                                        rEnd.Col(),   rEnd.Row() );
 183
 184             //  Targets am SBA abmelden nicht mehr noetig
 185         }
 186     }
 187
 188     if (bRecord)
 189         pUndoColl = new ScDBCollection( *pOldColl );
 190
 191     //  neue Targets am SBA anmelden nicht mehr noetig
 192
 193     pDoc->CompileDBFormula( TRUE );             // CreateFormulaString
 194     pDoc->SetDBCollection( new ScDBCollection( rNewColl ) );
 195     pDoc->CompileDBFormula( FALSE );    // CompileFormulaString
 196     pOldColl = NULL;
 197     pDocShell->PostPaint( 0,0,0, MAXCOL,MAXROW,MAXTAB, PAINT_GRID );
 198     aModificator.SetDocumentModified();
 199     SFX_APP()->Broadcast( SfxSimpleHint( SC_HINT_DBAREAS_CHANGED ) );
 200
 201     if (bRecord)
 202     {
 203         pRedoColl = new ScDBCollection( rNewColl );
 204         pDocShell->GetUndoManager()->AddUndoAction(
 205             new ScUndoDBData( pDocShell, pUndoColl, pRedoColl ) );
 206     }
 207 }
 208
 209 //
 210 //              wirkliche Funktionen
 211 //
 212
 213 // Sortieren
 214
 215 void ScDBFunc::UISort( const ScSortParam& rSortParam, BOOL bRecord )
 216 {
 217     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 218     ScDocument* pDoc = pDocSh->GetDocument();
 219     SCTAB nTab = GetViewData()->GetTabNo();
 220     ScDBData* pDBData = pDoc->GetDBAtArea( nTab, rSortParam.nCol1, rSortParam.nRow1,
 221                                                     rSortParam.nCol2, rSortParam.nRow2 );
 222     if (!pDBData)
 223     {
 224         DBG_ERROR( "Sort: keine DBData" );
 225         return;
 226     }
 227
 228     ScSubTotalParam aSubTotalParam;
 229     pDBData->GetSubTotalParam( aSubTotalParam );
 230     if (aSubTotalParam.bGroupActive[0] && !aSubTotalParam.bRemoveOnly)
 231     {
 232         //      Subtotals wiederholen, mit neuer Sortierung
 233
 234         DoSubTotals( aSubTotalParam, bRecord, &rSortParam );
 235     }
 236     else
 237     {
 238         Sort( rSortParam, bRecord );            // nur sortieren
 239     }
 240 }
 241
 242 void ScDBFunc::Sort( const ScSortParam& rSortParam, BOOL bRecord, BOOL bPaint )
 243 {
 244     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 245     SCTAB nTab = GetViewData()->GetTabNo();
 246     ScDBDocFunc aDBDocFunc( *pDocSh );
 247     BOOL bSuccess = aDBDocFunc.Sort( nTab, rSortParam, bRecord, bPaint, FALSE );
 248     if ( bSuccess && !rSortParam.bInplace )
 249     {
 250         //      Ziel markieren
 251         ScRange aDestRange( rSortParam.nDestCol, rSortParam.nDestRow, rSortParam.nDestTab,
 252                             rSortParam.nDestCol + rSortParam.nCol2 - rSortParam.nCol1,
 253                             rSortParam.nDestRow + rSortParam.nRow2 - rSortParam.nRow1,
 254                             rSortParam.nDestTab );
 255         MarkRange( aDestRange );
 256     }
 257 }
 258
 259 //      Filtern
 260
 261 void ScDBFunc::Query( const ScQueryParam& rQueryParam, const ScRange* pAdvSource, BOOL bRecord )
 262 {
 263     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 264     SCTAB nTab = GetViewData()->GetTabNo();
 265     ScDBDocFunc aDBDocFunc( *pDocSh );
 266     BOOL bSuccess = aDBDocFunc.Query( nTab, rQueryParam, pAdvSource, bRecord, FALSE );
 267
 268     if (bSuccess)
 269     {
 270         BOOL bCopy = !rQueryParam.bInplace;
 271         if (bCopy)
 272         {
 273             //  Zielbereich markieren (DB-Bereich wurde ggf. angelegt)
 274             ScDocument* pDoc = pDocSh->GetDocument();
 275             ScDBData* pDestData = pDoc->GetDBAtCursor(
 276                                             rQueryParam.nDestCol, rQueryParam.nDestRow,
 277                                             rQueryParam.nDestTab, TRUE );
 278             if (pDestData)
 279             {
 280                 ScRange aDestRange;
 281                 pDestData->GetArea(aDestRange);
 282                 MarkRange( aDestRange );
 283             }
 284         }
 285
 286         if (!bCopy)
 287         {
 288             UpdateScrollBars();
 289             SelectionChanged();     // for attribute states (filtered rows are ignored)
 290         }
 291
 292         GetViewData()->GetBindings().Invalidate( SID_UNFILTER );
 293     }
 294 }
 295
 296 //      Autofilter-Knoepfe ein-/ausblenden
 297
 298 void ScDBFunc::ToggleAutoFilter()
 299 {
 300     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 301     ScDocShellModificator aModificator( *pDocSh );
 302
 303     ScQueryParam        aParam;
 304     ScDocument*         pDoc    = GetViewData()->GetDocument();
 305     ScDBData*           pDBData = GetDBData(false, SC_DB_MAKE, false, true);
 306
 307     pDBData->SetByRow( TRUE );                          //! Undo, vorher abfragen ??
 308     pDBData->GetQueryParam( aParam );
 309
 310
 311     SCCOL  nCol;
 312     SCROW  nRow = aParam.nRow1;
 313     SCTAB  nTab = GetViewData()->GetTabNo();
 314     INT16   nFlag;
 315     BOOL        bHasAuto = TRUE;
 316     BOOL        bHeader  = pDBData->HasHeader();
 317     BOOL        bPaint   = FALSE;
 318
 319     //!         stattdessen aus DB-Bereich abfragen?
 320
 321     for (nCol=aParam.nCol1; nCol<=aParam.nCol2 && bHasAuto; nCol++)
 322     {
 323         nFlag = ((ScMergeFlagAttr*) pDoc->
 324                 GetAttr( nCol, nRow, nTab, ATTR_MERGE_FLAG ))->GetValue();
 325
 326         if ( (nFlag & SC_MF_AUTO) == 0 )
 327             bHasAuto = FALSE;
 328     }
 329
 330     if (bHasAuto)                                                               // aufheben
 331     {
 332         //      Filterknoepfe ausblenden
 333
 334         for (nCol=aParam.nCol1; nCol<=aParam.nCol2; nCol++)
 335         {
 336             nFlag = ((ScMergeFlagAttr*) pDoc->
 337                     GetAttr( nCol, nRow, nTab, ATTR_MERGE_FLAG ))->GetValue();
 338             pDoc->ApplyAttr( nCol, nRow, nTab, ScMergeFlagAttr( nFlag & ~SC_MF_AUTO ) );
 339         }
 340
 341         // use a list action for the AutoFilter buttons (ScUndoAutoFilter) and the filter operation
 342
 343         String aUndo = ScGlobal::GetRscString( STR_UNDO_QUERY );
 344         pDocSh->GetUndoManager()->EnterListAction( aUndo, aUndo );
 345
 346         ScRange aRange;
 347         pDBData->GetArea( aRange );
 348         pDocSh->GetUndoManager()->AddUndoAction(
 349             new ScUndoAutoFilter( pDocSh, aRange, pDBData->GetName(), FALSE ) );
 350
 351         pDBData->SetAutoFilter(FALSE);
 352
 353         //      Filter aufheben (incl. Paint / Undo)
 354
 355         SCSIZE nEC = aParam.GetEntryCount();
 356         for (SCSIZE i=0; i<nEC; i++)
 357             aParam.GetEntry(i).bDoQuery = FALSE;
 358         aParam.bDuplicate = TRUE;
 359         Query( aParam, NULL, TRUE );
 360
 361         pDocSh->GetUndoManager()->LeaveListAction();
 362
 363         bPaint = TRUE;
 364     }
 365     else                                                                        // Filterknoepfe einblenden
 366     {
 367         if ( !pDoc->IsBlockEmpty( nTab,
 368                                   aParam.nCol1, aParam.nRow1,
 369                                   aParam.nCol2, aParam.nRow2 ) )
 370         {
 371             if (!bHeader)
 372             {
 373                 if ( MessBox( GetViewData()->GetDialogParent(), WinBits(WB_YES_NO | WB_DEF_YES),
 374                         ScGlobal::GetRscString( STR_MSSG_DOSUBTOTALS_0 ),               // "StarCalc"
 375                         ScGlobal::GetRscString( STR_MSSG_MAKEAUTOFILTER_0 )     // Koepfe aus erster Zeile?
 376                     ).Execute() == RET_YES )
 377                 {
 378                     pDBData->SetHeader( TRUE );         //! Undo ??
 379                     bHeader = TRUE;
 380                 }
 381             }
 382
 383             ScRange aRange;
 384             pDBData->GetArea( aRange );
 385             pDocSh->GetUndoManager()->AddUndoAction(
 386                 new ScUndoAutoFilter( pDocSh, aRange, pDBData->GetName(), TRUE ) );
 387
 388             pDBData->SetAutoFilter(TRUE);
 389
 390             for (nCol=aParam.nCol1; nCol<=aParam.nCol2; nCol++)
 391             {
 392                 nFlag = ((ScMergeFlagAttr*) pDoc->
 393                         GetAttr( nCol, nRow, nTab, ATTR_MERGE_FLAG ))->GetValue();
 394                 pDoc->ApplyAttr( nCol, nRow, nTab, ScMergeFlagAttr( nFlag | SC_MF_AUTO ) );
 395             }
 396             pDocSh->PostPaint( aParam.nCol1, nRow, nTab, aParam.nCol2, nRow, nTab,
 397                                                      PAINT_GRID );
 398             bPaint = TRUE;
 399         }
 400         else
 401         {
 402             ErrorBox aErrorBox( GetViewData()->GetDialogParent(), WinBits( WB_OK | WB_DEF_OK ),
 403                                 ScGlobal::GetRscString( STR_ERR_AUTOFILTER ) );
 404             aErrorBox.Execute();
 405         }
 406     }
 407
 408     if ( bPaint )
 409     {
 410         aModificator.SetDocumentModified();
 411
 412         SfxBindings& rBindings = GetViewData()->GetBindings();
 413         rBindings.Invalidate( SID_AUTO_FILTER );
 414         rBindings.Invalidate( SID_AUTOFILTER_HIDE );
 415     }
 416 }
 417
 418 //              nur ausblenden, keine Daten veraendern
 419
 420 void ScDBFunc::HideAutoFilter()
 421 {
 422     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 423     ScDocShellModificator aModificator( *pDocSh );
 424
 425     ScDocument* pDoc = pDocSh->GetDocument();
 426
 427     ScQueryParam aParam;
 428     ScDBData* pDBData = GetDBData( FALSE );
 429
 430     SCTAB nTab;
 431     SCCOL nCol1, nCol2;
 432     SCROW nRow1, nRow2;
 433     pDBData->GetArea(nTab, nCol1, nRow1, nCol2, nRow2);
 434
 435     for (SCCOL nCol=nCol1; nCol<=nCol2; nCol++)
 436     {
 437         INT16 nFlag = ((ScMergeFlagAttr*) pDoc->
 438                                 GetAttr( nCol, nRow1, nTab, ATTR_MERGE_FLAG ))->GetValue();
 439         pDoc->ApplyAttr( nCol, nRow1, nTab, ScMergeFlagAttr( nFlag & ~SC_MF_AUTO ) );
 440     }
 441
 442     ScRange aRange;
 443     pDBData->GetArea( aRange );
 444     pDocSh->GetUndoManager()->AddUndoAction(
 445         new ScUndoAutoFilter( pDocSh, aRange, pDBData->GetName(), FALSE ) );
 446
 447     pDBData->SetAutoFilter(FALSE);
 448
 449     pDocSh->PostPaint( nCol1,nRow1,nTab, nCol2,nRow1,nTab, PAINT_GRID );
 450     aModificator.SetDocumentModified();
 451
 452     SfxBindings& rBindings = GetViewData()->GetBindings();
 453     rBindings.Invalidate( SID_AUTO_FILTER );
 454     rBindings.Invalidate( SID_AUTOFILTER_HIDE );
 455 }
 456
 457 //              Re-Import
 458
 459 BOOL ScDBFunc::ImportData( const ScImportParam& rParam, BOOL bRecord )
 460 {
 461     ScDocument* pDoc = GetViewData()->GetDocument();
 462     ScEditableTester aTester( pDoc, GetViewData()->GetTabNo(), rParam.nCol1,rParam.nRow1,
 463                                                             rParam.nCol2,rParam.nRow2 );
 464     if ( !aTester.IsEditable() )
 465     {
 466         ErrorMessage(aTester.GetMessageId());
 467         return FALSE;
 468     }
 469
 470     ScDBDocFunc aDBDocFunc( *GetViewData()->GetDocShell() );
 471     ::com::sun::star::uno::Reference< ::com::sun::star::sdbc::XResultSet > xResultSet;
 472     return aDBDocFunc.DoImport( GetViewData()->GetTabNo(), rParam, xResultSet, NULL, bRecord );
 473 }
 474
 475
 476