sc/source/ui/view/dbfunc.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  *
   9  * This file incorporates work covered by the following license notice:
  10  *
  11  *   Licensed to the Apache Software Foundation (ASF) under one or more
  12  *   contributor license agreements. See the NOTICE file distributed
  13  *   with this work for additional information regarding copyright
  14  *   ownership. The ASF licenses this file to you under the Apache
  15  *   License, Version 2.0 (the "License"); you may not use this file
  16  *   except in compliance with the License. You may obtain a copy of
  17  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  18  */
  19
  20 #include "scitems.hxx"
  21 #include <sfx2/app.hxx>
  22 #include <sfx2/bindings.hxx>
  23 #include <vcl/msgbox.hxx>
  24
  25 #include <com/sun/star/sdbc/XResultSet.hpp>
  26
  27 #include "dbfunc.hxx"
  28 #include "docsh.hxx"
  29 #include "attrib.hxx"
  30 #include "sc.hrc"
  31 #include "undodat.hxx"
  32 #include "dbdata.hxx"
  33 #include "globstr.hrc"
  34 #include "global.hxx"
  35 #include "dbdocfun.hxx"
  36 #include "editable.hxx"
  37 #include "queryentry.hxx"
  38 #include "markdata.hxx"
  39
  40 //==================================================================
  41
  42 ScDBFunc::ScDBFunc( Window* pParent, ScDocShell& rDocSh, ScTabViewShell* pViewShell ) :
  43     ScViewFunc( pParent, rDocSh, pViewShell )
  44 {
  45 }
  46
  47 ScDBFunc::~ScDBFunc()
  48 {
  49 }
  50
  51 //
  52 //      auxiliary functions
  53 //
  54
  55 void ScDBFunc::GotoDBArea( const OUString& rDBName )
  56 {
  57     ScDocument* pDoc = GetViewData()->GetDocument();
  58     ScDBCollection* pDBCol = pDoc->GetDBCollection();
  59     ScDBData* pData = pDBCol->getNamedDBs().findByUpperName(ScGlobal::pCharClass->uppercase(rDBName));
  60     if (pData)
  61     {
  62         SCTAB nTab = 0;
  63         SCCOL nStartCol = 0;
  64         SCROW nStartRow = 0;
  65         SCCOL nEndCol = 0;
  66         SCROW nEndRow = 0;
  67
  68         pData->GetArea( nTab, nStartCol, nStartRow, nEndCol, nEndRow );
  69         SetTabNo( nTab );
  70
  71         MoveCursorAbs( nStartCol, nStartRow, ScFollowMode( SC_FOLLOW_JUMP ),
  72                        false, false );  // bShift,bControl
  73         DoneBlockMode();
  74         InitBlockMode( nStartCol, nStartRow, nTab );
  75         MarkCursor( nEndCol, nEndRow, nTab );
  76         SelectionChanged();
  77     }
  78 }
  79
  80 //  search current datarange for sort / filter
  81
  82 ScDBData* ScDBFunc::GetDBData( bool bMark, ScGetDBMode eMode, ScGetDBSelection eSel )
  83 {
  84     ScDocShell* pDocSh = GetViewData()->GetDocShell();
  85     ScDBData* pData = NULL;
  86     ScRange aRange;
  87     ScMarkType eMarkType = GetViewData()->GetSimpleArea(aRange);
  88     if ( eMarkType == SC_MARK_SIMPLE || eMarkType == SC_MARK_SIMPLE_FILTERED )
  89     {
  90         bool bShrinkColumnsOnly = false;
  91         if (eSel == SC_DBSEL_ROW_DOWN)
  92         {
  93             // Don't alter row range, additional rows may have been selected on
  94             // purpose to append data, or to have a fake header row.
  95             bShrinkColumnsOnly = true;
  96             // Select further rows only if only one row or a portion thereof is
  97             // selected.
  98             if (aRange.aStart.Row() != aRange.aEnd.Row())
  99             {
 100                 // If an area is selected shrink that to the actual used
 101                 // columns, don't draw filter buttons for empty columns.
 102                 eSel = SC_DBSEL_SHRINK_TO_USED_DATA;
 103             }
 104             else if (aRange.aStart.Col() == aRange.aEnd.Col())
 105             {
 106                 // One cell only, if it is not marked obtain entire used data
 107                 // area.
 108                 const ScMarkData& rMarkData = GetViewData()->GetMarkData();
 109                 if (!(rMarkData.IsMarked() || rMarkData.IsMultiMarked()))
 110                     eSel = SC_DBSEL_KEEP;
 111             }
 112         }
 113         switch (eSel)
 114         {
 115             case SC_DBSEL_SHRINK_TO_SHEET_DATA:
 116                 {
 117                     // Shrink the selection to sheet data area.
 118                     ScDocument* pDoc = pDocSh->GetDocument();
 119                     SCCOL nCol1 = aRange.aStart.Col(), nCol2 = aRange.aEnd.Col();
 120                     SCROW nRow1 = aRange.aStart.Row(), nRow2 = aRange.aEnd.Row();
 121                     if (pDoc->ShrinkToDataArea( aRange.aStart.Tab(), nCol1, nRow1, nCol2, nRow2))
 122                     {
 123                         aRange.aStart.SetCol(nCol1);
 124                         aRange.aEnd.SetCol(nCol2);
 125                         aRange.aStart.SetRow(nRow1);
 126                         aRange.aEnd.SetRow(nRow2);
 127                     }
 128                 }
 129                 break;
 130             case SC_DBSEL_SHRINK_TO_USED_DATA:
 131             case SC_DBSEL_ROW_DOWN:
 132                 {
 133                     // Shrink the selection to actual used area.
 134                     ScDocument* pDoc = pDocSh->GetDocument();
 135                     SCCOL nCol1 = aRange.aStart.Col(), nCol2 = aRange.aEnd.Col();
 136                     SCROW nRow1 = aRange.aStart.Row(), nRow2 = aRange.aEnd.Row();
 137                     bool bShrunk;
 138                     pDoc->ShrinkToUsedDataArea( bShrunk, aRange.aStart.Tab(),
 139                             nCol1, nRow1, nCol2, nRow2, bShrinkColumnsOnly);
 140                     if (bShrunk)
 141                     {
 142                         aRange.aStart.SetCol(nCol1);
 143                         aRange.aEnd.SetCol(nCol2);
 144                         aRange.aStart.SetRow(nRow1);
 145                         aRange.aEnd.SetRow(nRow2);
 146                     }
 147                 }
 148                 break;
 149             default:
 150                 ;   // nothing
 151         }
 152         pData = pDocSh->GetDBData( aRange, eMode, eSel );
 153     }
 154     else if ( eMode != SC_DB_OLD )
 155         pData = pDocSh->GetDBData(
 156                     ScRange( GetViewData()->GetCurX(), GetViewData()->GetCurY(),
 157                              GetViewData()->GetTabNo() ),
 158                     eMode, SC_DBSEL_KEEP );
 159
 160     if (!pData)
 161         return NULL;
 162
 163     if (bMark)
 164     {
 165         ScRange aFound;
 166         pData->GetArea(aFound);
 167         MarkRange( aFound, false );
 168     }
 169     return pData;
 170 }
 171
 172 ScDBData* ScDBFunc::GetAnonymousDBData()
 173 {
 174     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 175     ScRange aRange;
 176     ScMarkType eMarkType = GetViewData()->GetSimpleArea(aRange);
 177     if (eMarkType != SC_MARK_SIMPLE && eMarkType != SC_MARK_SIMPLE_FILTERED)
 178         return NULL;
 179
 180     // Expand to used data area if not explicitly marked.
 181     const ScMarkData& rMarkData = GetViewData()->GetMarkData();
 182     if (!rMarkData.IsMarked() && !rMarkData.IsMultiMarked())
 183     {
 184         SCCOL nCol1 = aRange.aStart.Col();
 185         SCCOL nCol2 = aRange.aEnd.Col();
 186         SCROW nRow1 = aRange.aStart.Row();
 187         SCROW nRow2 = aRange.aEnd.Row();
 188         pDocSh->GetDocument()->GetDataArea(aRange.aStart.Tab(), nCol1, nRow1, nCol2, nRow2, false, false);
 189         aRange.aStart.SetCol(nCol1);
 190         aRange.aStart.SetRow(nRow1);
 191         aRange.aEnd.SetCol(nCol2);
 192         aRange.aEnd.SetRow(nRow2);
 193     }
 194
 195     return pDocSh->GetAnonymousDBData(aRange);
 196 }
 197
 198 //  change database range (dialog)
 199
 200 void ScDBFunc::NotifyCloseDbNameDlg( const ScDBCollection& rNewColl, const std::vector<ScRange> &rDelAreaList )
 201 {
 202
 203     ScDocShell* pDocShell = GetViewData()->GetDocShell();
 204     ScDocShellModificator aModificator( *pDocShell );
 205     ScDocument* pDoc = pDocShell->GetDocument();
 206     ScDBCollection* pOldColl = pDoc->GetDBCollection();
 207     ScDBCollection* pUndoColl = NULL;
 208     const sal_Bool bRecord (pDoc->IsUndoEnabled());
 209
 210     std::vector<ScRange>::const_iterator iter;
 211     for (iter = rDelAreaList.begin(); iter != rDelAreaList.end(); ++iter)
 212     {
 213         // unregistering target in SBA no longer necessary
 214         const ScAddress& rStart = iter->aStart;
 215         const ScAddress& rEnd   = iter->aEnd;
 216         pDocShell->DBAreaDeleted( rStart.Tab(),
 217                                   rStart.Col(), rStart.Row(),
 218                                   rEnd.Col(),   rEnd.Row() );
 219
 220     }
 221
 222     if (bRecord)
 223         pUndoColl = new ScDBCollection( *pOldColl );
 224
 225     //  register target in SBA no longer necessary
 226
 227     pDoc->CompileDBFormula( sal_True );     // CreateFormulaString
 228     pDoc->SetDBCollection( new ScDBCollection( rNewColl ) );
 229     pDoc->CompileDBFormula( false );    // CompileFormulaString
 230     pOldColl = NULL;
 231     pDocShell->PostPaint(ScRange(0, 0, 0, MAXCOL, MAXROW, MAXTAB), PAINT_GRID);
 232     aModificator.SetDocumentModified();
 233     SFX_APP()->Broadcast( SfxSimpleHint( SC_HINT_DBAREAS_CHANGED ) );
 234
 235     if (bRecord)
 236     {
 237         ScDBCollection* pRedoColl = new ScDBCollection( rNewColl );
 238         pDocShell->GetUndoManager()->AddUndoAction(
 239             new ScUndoDBData( pDocShell, pUndoColl, pRedoColl ) );
 240     }
 241 }
 242
 243 //
 244 //      main functions
 245 //
 246
 247 // Sort
 248
 249 void ScDBFunc::UISort( const ScSortParam& rSortParam, sal_Bool bRecord )
 250 {
 251     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 252     ScDocument* pDoc = pDocSh->GetDocument();
 253     SCTAB nTab = GetViewData()->GetTabNo();
 254     ScDBData* pDBData = pDoc->GetDBAtArea( nTab, rSortParam.nCol1, rSortParam.nRow1,
 255                                                     rSortParam.nCol2, rSortParam.nRow2 );
 256     if (!pDBData)
 257     {
 258         OSL_FAIL( "Sort: no DBData" );
 259         return;
 260     }
 261
 262     ScSubTotalParam aSubTotalParam;
 263     pDBData->GetSubTotalParam( aSubTotalParam );
 264     if (aSubTotalParam.bGroupActive[0] && !aSubTotalParam.bRemoveOnly)
 265     {
 266         //  repeat subtotals, with new sortorder
 267
 268         DoSubTotals( aSubTotalParam, bRecord, &rSortParam );
 269     }
 270     else
 271     {
 272         Sort( rSortParam, bRecord );        // just sort
 273     }
 274 }
 275
 276 void ScDBFunc::Sort( const ScSortParam& rSortParam, sal_Bool bRecord, sal_Bool bPaint )
 277 {
 278     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 279     SCTAB nTab = GetViewData()->GetTabNo();
 280     ScDBDocFunc aDBDocFunc( *pDocSh );
 281     sal_Bool bSuccess = aDBDocFunc.Sort( nTab, rSortParam, bRecord, bPaint, false );
 282     if ( bSuccess && !rSortParam.bInplace )
 283     {
 284         //  mark target
 285         ScRange aDestRange( rSortParam.nDestCol, rSortParam.nDestRow, rSortParam.nDestTab,
 286                             rSortParam.nDestCol + rSortParam.nCol2 - rSortParam.nCol1,
 287                             rSortParam.nDestRow + rSortParam.nRow2 - rSortParam.nRow1,
 288                             rSortParam.nDestTab );
 289         MarkRange( aDestRange );
 290     }
 291
 292     ResetAutoSpell();
 293 }
 294
 295 //  filters
 296
 297 void ScDBFunc::Query( const ScQueryParam& rQueryParam, const ScRange* pAdvSource, sal_Bool bRecord )
 298 {
 299     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 300     SCTAB nTab = GetViewData()->GetTabNo();
 301     ScDBDocFunc aDBDocFunc( *pDocSh );
 302     sal_Bool bSuccess = aDBDocFunc.Query( nTab, rQueryParam, pAdvSource, bRecord, false );
 303
 304     if (bSuccess)
 305     {
 306         sal_Bool bCopy = !rQueryParam.bInplace;
 307         if (bCopy)
 308         {
 309             //  mark target range (data base range has been set up if applicable)
 310             ScDocument* pDoc = pDocSh->GetDocument();
 311             ScDBData* pDestData = pDoc->GetDBAtCursor(
 312                                             rQueryParam.nDestCol, rQueryParam.nDestRow,
 313                                             rQueryParam.nDestTab, sal_True );
 314             if (pDestData)
 315             {
 316                 ScRange aDestRange;
 317                 pDestData->GetArea(aDestRange);
 318                 MarkRange( aDestRange );
 319             }
 320         }
 321
 322         if (!bCopy)
 323         {
 324             UpdateScrollBars();
 325             SelectionChanged();     // for attribute states (filtered rows are ignored)
 326         }
 327
 328         GetViewData()->GetBindings().Invalidate( SID_UNFILTER );
 329     }
 330 }
 331
 332 //  autofilter-buttons show / hide
 333
 334 void ScDBFunc::ToggleAutoFilter()
 335 {
 336     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 337     ScDocShellModificator aModificator( *pDocSh );
 338
 339     ScQueryParam    aParam;
 340     ScDocument*     pDoc    = GetViewData()->GetDocument();
 341     ScDBData*       pDBData = GetDBData(false, SC_DB_MAKE, SC_DBSEL_ROW_DOWN);
 342
 343     pDBData->SetByRow( sal_True );              //! undo, retrieve beforehand ??
 344     pDBData->GetQueryParam( aParam );
 345
 346
 347     SCCOL  nCol;
 348     SCROW  nRow = aParam.nRow1;
 349     SCTAB  nTab = GetViewData()->GetTabNo();
 350     sal_Int16   nFlag;
 351     sal_Bool    bHasAuto = sal_True;
 352     sal_Bool    bHeader  = pDBData->HasHeader();
 353     sal_Bool    bPaint   = false;
 354
 355     //!     instead retrieve from DB-range?
 356
 357     for (nCol=aParam.nCol1; nCol<=aParam.nCol2 && bHasAuto; nCol++)
 358     {
 359         nFlag = ((ScMergeFlagAttr*) pDoc->
 360                 GetAttr( nCol, nRow, nTab, ATTR_MERGE_FLAG ))->GetValue();
 361
 362         if ( (nFlag & SC_MF_AUTO) == 0 )
 363             bHasAuto = false;
 364     }
 365
 366     if (bHasAuto)                               // remove
 367     {
 368         //  hide filter buttons
 369
 370         for (nCol=aParam.nCol1; nCol<=aParam.nCol2; nCol++)
 371         {
 372             nFlag = ((ScMergeFlagAttr*) pDoc->
 373                     GetAttr( nCol, nRow, nTab, ATTR_MERGE_FLAG ))->GetValue();
 374             pDoc->ApplyAttr( nCol, nRow, nTab, ScMergeFlagAttr( nFlag & ~SC_MF_AUTO ) );
 375         }
 376
 377         // use a list action for the AutoFilter buttons (ScUndoAutoFilter) and the filter operation
 378
 379         OUString aUndo = ScGlobal::GetRscString( STR_UNDO_QUERY );
 380         pDocSh->GetUndoManager()->EnterListAction( aUndo, aUndo );
 381
 382         ScRange aRange;
 383         pDBData->GetArea( aRange );
 384         pDocSh->GetUndoManager()->AddUndoAction(
 385             new ScUndoAutoFilter( pDocSh, aRange, pDBData->GetName(), false ) );
 386
 387         pDBData->SetAutoFilter(false);
 388
 389         //  remove filter (incl. Paint / Undo)
 390
 391         SCSIZE nEC = aParam.GetEntryCount();
 392         for (SCSIZE i=0; i<nEC; i++)
 393             aParam.GetEntry(i).bDoQuery = false;
 394         aParam.bDuplicate = sal_True;
 395         Query( aParam, NULL, sal_True );
 396
 397         pDocSh->GetUndoManager()->LeaveListAction();
 398
 399         bPaint = sal_True;
 400     }
 401     else                                    // show filter buttons
 402     {
 403         if ( !pDoc->IsBlockEmpty( nTab,
 404                                   aParam.nCol1, aParam.nRow1,
 405                                   aParam.nCol2, aParam.nRow2 ) )
 406         {
 407             if (!bHeader)
 408             {
 409                 if ( MessBox( GetViewData()->GetDialogParent(), WinBits(WB_YES_NO | WB_DEF_YES),
 410                         ScGlobal::GetRscString( STR_MSSG_DOSUBTOTALS_0 ),       // "StarCalc"
 411                         ScGlobal::GetRscString( STR_MSSG_MAKEAUTOFILTER_0 )     // header from first row?
 412                     ).Execute() == RET_YES )
 413                 {
 414                     pDBData->SetHeader( sal_True );     //! Undo ??
 415                     bHeader = sal_True;
 416                 }
 417             }
 418
 419             ScRange aRange;
 420             pDBData->GetArea( aRange );
 421             pDocSh->GetUndoManager()->AddUndoAction(
 422                 new ScUndoAutoFilter( pDocSh, aRange, pDBData->GetName(), sal_True ) );
 423
 424             pDBData->SetAutoFilter(sal_True);
 425
 426             for (nCol=aParam.nCol1; nCol<=aParam.nCol2; nCol++)
 427             {
 428                 nFlag = ((ScMergeFlagAttr*) pDoc->
 429                         GetAttr( nCol, nRow, nTab, ATTR_MERGE_FLAG ))->GetValue();
 430                 pDoc->ApplyAttr( nCol, nRow, nTab, ScMergeFlagAttr( nFlag | SC_MF_AUTO ) );
 431             }
 432             pDocSh->PostPaint(ScRange(aParam.nCol1, nRow, nTab, aParam.nCol2, nRow, nTab),
 433                               PAINT_GRID);
 434             bPaint = sal_True;
 435         }
 436         else
 437         {
 438             ErrorBox aErrorBox( GetViewData()->GetDialogParent(), WinBits( WB_OK | WB_DEF_OK ),
 439                                 ScGlobal::GetRscString( STR_ERR_AUTOFILTER ) );
 440             aErrorBox.Execute();
 441         }
 442     }
 443
 444     if ( bPaint )
 445     {
 446         aModificator.SetDocumentModified();
 447
 448         SfxBindings& rBindings = GetViewData()->GetBindings();
 449         rBindings.Invalidate( SID_AUTO_FILTER );
 450         rBindings.Invalidate( SID_AUTOFILTER_HIDE );
 451     }
 452 }
 453
 454 //      just hide, no data change
 455
 456 void ScDBFunc::HideAutoFilter()
 457 {
 458     ScDocShell* pDocSh = GetViewData()->GetDocShell();
 459     ScDocShellModificator aModificator( *pDocSh );
 460
 461     ScDocument* pDoc = pDocSh->GetDocument();
 462
 463     ScQueryParam aParam;
 464     ScDBData* pDBData = GetDBData( false );
 465
 466     SCTAB nTab;
 467     SCCOL nCol1, nCol2;
 468     SCROW nRow1, nRow2;
 469     pDBData->GetArea(nTab, nCol1, nRow1, nCol2, nRow2);
 470
 471     for (SCCOL nCol=nCol1; nCol<=nCol2; nCol++)
 472     {
 473         sal_Int16 nFlag = ((ScMergeFlagAttr*) pDoc->
 474                                 GetAttr( nCol, nRow1, nTab, ATTR_MERGE_FLAG ))->GetValue();
 475         pDoc->ApplyAttr( nCol, nRow1, nTab, ScMergeFlagAttr( nFlag & ~SC_MF_AUTO ) );
 476     }
 477
 478     ScRange aRange;
 479     pDBData->GetArea( aRange );
 480     pDocSh->GetUndoManager()->AddUndoAction(
 481         new ScUndoAutoFilter( pDocSh, aRange, pDBData->GetName(), false ) );
 482
 483     pDBData->SetAutoFilter(false);
 484
 485     pDocSh->PostPaint(ScRange(nCol1, nRow1, nTab, nCol2, nRow1, nTab), PAINT_GRID );
 486     aModificator.SetDocumentModified();
 487
 488     SfxBindings& rBindings = GetViewData()->GetBindings();
 489     rBindings.Invalidate( SID_AUTO_FILTER );
 490     rBindings.Invalidate( SID_AUTOFILTER_HIDE );
 491 }
 492
 493 //      Re-Import
 494
 495 sal_Bool ScDBFunc::ImportData( const ScImportParam& rParam, sal_Bool bRecord )
 496 {
 497     ScDocument* pDoc = GetViewData()->GetDocument();
 498     ScEditableTester aTester( pDoc, GetViewData()->GetTabNo(), rParam.nCol1,rParam.nRow1,
 499                                                             rParam.nCol2,rParam.nRow2 );
 500     if ( !aTester.IsEditable() )
 501     {
 502         ErrorMessage(aTester.GetMessageId());
 503         return false;
 504     }
 505
 506     ScDBDocFunc aDBDocFunc( *GetViewData()->GetDocShell() );
 507     return aDBDocFunc.DoImport( GetViewData()->GetTabNo(), rParam, NULL, bRecord );
 508 }
 509
 510
 511
 512 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */