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 ScDBFunc::ScDBFunc( vcl::Window* pParent, ScDocShell& rDocSh, ScTabViewShell* pViewShell ) :
  41     ScViewFunc( pParent, rDocSh, pViewShell )
  42 {
  43 }
  44
  45 ScDBFunc::~ScDBFunc()
  46 {
  47 }
  48
  49 //      auxiliary functions
  50
  51 void ScDBFunc::GotoDBArea( const OUString& rDBName )
  52 {
  53     ScDocument* pDoc = GetViewData().GetDocument();
  54     ScDBCollection* pDBCol = pDoc->GetDBCollection();
  55     ScDBData* pData = pDBCol->getNamedDBs().findByUpperName(ScGlobal::pCharClass->uppercase(rDBName));
  56     if (pData)
  57     {
  58         SCTAB nTab = 0;
  59         SCCOL nStartCol = 0;
  60         SCROW nStartRow = 0;
  61         SCCOL nEndCol = 0;
  62         SCROW nEndRow = 0;
  63
  64         pData->GetArea( nTab, nStartCol, nStartRow, nEndCol, nEndRow );
  65         SetTabNo( nTab );
  66
  67         MoveCursorAbs( nStartCol, nStartRow, ScFollowMode( SC_FOLLOW_JUMP ),
  68                        false, false );  // bShift,bControl
  69         DoneBlockMode();
  70         InitBlockMode( nStartCol, nStartRow, nTab );
  71         MarkCursor( nEndCol, nEndRow, nTab );
  72         SelectionChanged();
  73     }
  74 }
  75
  76 //  search current datarange for sort / filter
  77
  78 ScDBData* ScDBFunc::GetDBData( bool bMark, ScGetDBMode eMode, ScGetDBSelection eSel )
  79 {
  80     ScDocShell* pDocSh = GetViewData().GetDocShell();
  81     ScDBData* pData = NULL;
  82     ScRange aRange;
  83     ScMarkType eMarkType = GetViewData().GetSimpleArea(aRange);
  84     if ( eMarkType == SC_MARK_SIMPLE || eMarkType == SC_MARK_SIMPLE_FILTERED )
  85     {
  86         bool bShrinkColumnsOnly = false;
  87         if (eSel == SC_DBSEL_ROW_DOWN)
  88         {
  89             // Don't alter row range, additional rows may have been selected on
  90             // purpose to append data, or to have a fake header row.
  91             bShrinkColumnsOnly = true;
  92             // Select further rows only if only one row or a portion thereof is
  93             // selected.
  94             if (aRange.aStart.Row() != aRange.aEnd.Row())
  95             {
  96                 // If an area is selected shrink that to the actual used
  97                 // columns, don't draw filter buttons for empty columns.
  98                 eSel = SC_DBSEL_SHRINK_TO_USED_DATA;
  99             }
 100             else if (aRange.aStart.Col() == aRange.aEnd.Col())
 101             {
 102                 // One cell only, if it is not marked obtain entire used data
 103                 // area.
 104                 const ScMarkData& rMarkData = GetViewData().GetMarkData();
 105                 if (!(rMarkData.IsMarked() || rMarkData.IsMultiMarked()))
 106                     eSel = SC_DBSEL_KEEP;
 107             }
 108         }
 109         switch (eSel)
 110         {
 111             case SC_DBSEL_SHRINK_TO_SHEET_DATA:
 112                 {
 113                     // Shrink the selection to sheet data area.
 114                     ScDocument& rDoc = pDocSh->GetDocument();
 115                     SCCOL nCol1 = aRange.aStart.Col(), nCol2 = aRange.aEnd.Col();
 116                     SCROW nRow1 = aRange.aStart.Row(), nRow2 = aRange.aEnd.Row();
 117                     if (rDoc.ShrinkToDataArea( aRange.aStart.Tab(), nCol1, nRow1, nCol2, nRow2))
 118                     {
 119                         aRange.aStart.SetCol(nCol1);
 120                         aRange.aEnd.SetCol(nCol2);
 121                         aRange.aStart.SetRow(nRow1);
 122                         aRange.aEnd.SetRow(nRow2);
 123                     }
 124                 }
 125                 break;
 126             case SC_DBSEL_SHRINK_TO_USED_DATA:
 127             case SC_DBSEL_ROW_DOWN:
 128                 {
 129                     // Shrink the selection to actual used area.
 130                     ScDocument& rDoc = pDocSh->GetDocument();
 131                     SCCOL nCol1 = aRange.aStart.Col(), nCol2 = aRange.aEnd.Col();
 132                     SCROW nRow1 = aRange.aStart.Row(), nRow2 = aRange.aEnd.Row();
 133                     bool bShrunk;
 134                     rDoc.ShrinkToUsedDataArea( bShrunk, aRange.aStart.Tab(),
 135                             nCol1, nRow1, nCol2, nRow2, bShrinkColumnsOnly);
 136                     if (bShrunk)
 137                     {
 138                         aRange.aStart.SetCol(nCol1);
 139                         aRange.aEnd.SetCol(nCol2);
 140                         aRange.aStart.SetRow(nRow1);
 141                         aRange.aEnd.SetRow(nRow2);
 142                     }
 143                 }
 144                 break;
 145             default:
 146                 ;   // nothing
 147         }
 148         pData = pDocSh->GetDBData( aRange, eMode, eSel );
 149     }
 150     else if ( eMode != SC_DB_OLD )
 151         pData = pDocSh->GetDBData(
 152                     ScRange( GetViewData().GetCurX(), GetViewData().GetCurY(),
 153                              GetViewData().GetTabNo() ),
 154                     eMode, SC_DBSEL_KEEP );
 155
 156     if (!pData)
 157         return NULL;
 158
 159     if (bMark)
 160     {
 161         ScRange aFound;
 162         pData->GetArea(aFound);
 163         MarkRange( aFound, false );
 164     }
 165     return pData;
 166 }
 167
 168 ScDBData* ScDBFunc::GetAnonymousDBData()
 169 {
 170     ScDocShell* pDocSh = GetViewData().GetDocShell();
 171     ScRange aRange;
 172     ScMarkType eMarkType = GetViewData().GetSimpleArea(aRange);
 173     if (eMarkType != SC_MARK_SIMPLE && eMarkType != SC_MARK_SIMPLE_FILTERED)
 174         return NULL;
 175
 176     // Expand to used data area if not explicitly marked.
 177     const ScMarkData& rMarkData = GetViewData().GetMarkData();
 178     if (!rMarkData.IsMarked() && !rMarkData.IsMultiMarked())
 179     {
 180         SCCOL nCol1 = aRange.aStart.Col();
 181         SCCOL nCol2 = aRange.aEnd.Col();
 182         SCROW nRow1 = aRange.aStart.Row();
 183         SCROW nRow2 = aRange.aEnd.Row();
 184         pDocSh->GetDocument().GetDataArea(aRange.aStart.Tab(), nCol1, nRow1, nCol2, nRow2, false, false);
 185         aRange.aStart.SetCol(nCol1);
 186         aRange.aStart.SetRow(nRow1);
 187         aRange.aEnd.SetCol(nCol2);
 188         aRange.aEnd.SetRow(nRow2);
 189     }
 190
 191     return pDocSh->GetAnonymousDBData(aRange);
 192 }
 193
 194 //      main functions
 195
 196 // Sort
 197
 198 void ScDBFunc::UISort( const ScSortParam& rSortParam, bool bRecord )
 199 {
 200     ScDocShell* pDocSh = GetViewData().GetDocShell();
 201     ScDocument& rDoc = pDocSh->GetDocument();
 202     SCTAB nTab = GetViewData().GetTabNo();
 203     ScDBData* pDBData = rDoc.GetDBAtArea( nTab, rSortParam.nCol1, rSortParam.nRow1,
 204                                                     rSortParam.nCol2, rSortParam.nRow2 );
 205     if (!pDBData)
 206     {
 207         OSL_FAIL( "Sort: no DBData" );
 208         return;
 209     }
 210
 211     ScSubTotalParam aSubTotalParam;
 212     pDBData->GetSubTotalParam( aSubTotalParam );
 213     if (aSubTotalParam.bGroupActive[0] && !aSubTotalParam.bRemoveOnly)
 214     {
 215         //  repeat subtotals, with new sortorder
 216
 217         DoSubTotals( aSubTotalParam, bRecord, &rSortParam );
 218     }
 219     else
 220     {
 221         Sort( rSortParam, bRecord );        // just sort
 222     }
 223 }
 224
 225 void ScDBFunc::Sort( const ScSortParam& rSortParam, bool bRecord, bool bPaint )
 226 {
 227     ScDocShell* pDocSh = GetViewData().GetDocShell();
 228     SCTAB nTab = GetViewData().GetTabNo();
 229     ScDBDocFunc aDBDocFunc( *pDocSh );
 230     bool bSuccess = aDBDocFunc.Sort( nTab, rSortParam, bRecord, bPaint, false );
 231     if ( bSuccess && !rSortParam.bInplace )
 232     {
 233         //  mark target
 234         ScRange aDestRange( rSortParam.nDestCol, rSortParam.nDestRow, rSortParam.nDestTab,
 235                             rSortParam.nDestCol + rSortParam.nCol2 - rSortParam.nCol1,
 236                             rSortParam.nDestRow + rSortParam.nRow2 - rSortParam.nRow1,
 237                             rSortParam.nDestTab );
 238         MarkRange( aDestRange );
 239     }
 240
 241     ResetAutoSpell();
 242 }
 243
 244 //  filters
 245
 246 void ScDBFunc::Query( const ScQueryParam& rQueryParam, const ScRange* pAdvSource, bool bRecord )
 247 {
 248     ScDocShell* pDocSh = GetViewData().GetDocShell();
 249     SCTAB nTab = GetViewData().GetTabNo();
 250     ScDBDocFunc aDBDocFunc( *pDocSh );
 251     bool bSuccess = aDBDocFunc.Query( nTab, rQueryParam, pAdvSource, bRecord, false );
 252
 253     if (bSuccess)
 254     {
 255         bool bCopy = !rQueryParam.bInplace;
 256         if (bCopy)
 257         {
 258             //  mark target range (data base range has been set up if applicable)
 259             ScDocument& rDoc = pDocSh->GetDocument();
 260             ScDBData* pDestData = rDoc.GetDBAtCursor(
 261                                             rQueryParam.nDestCol, rQueryParam.nDestRow,
 262                                             rQueryParam.nDestTab, true );
 263             if (pDestData)
 264             {
 265                 ScRange aDestRange;
 266                 pDestData->GetArea(aDestRange);
 267                 MarkRange( aDestRange );
 268             }
 269         }
 270
 271         if (!bCopy)
 272         {
 273             UpdateScrollBars();
 274             SelectionChanged();     // for attribute states (filtered rows are ignored)
 275         }
 276
 277         GetViewData().GetBindings().Invalidate( SID_UNFILTER );
 278     }
 279 }
 280
 281 //  autofilter-buttons show / hide
 282
 283 void ScDBFunc::ToggleAutoFilter()
 284 {
 285     ScDocShell* pDocSh = GetViewData().GetDocShell();
 286     ScDocShellModificator aModificator( *pDocSh );
 287
 288     ScQueryParam    aParam;
 289     ScDocument*     pDoc    = GetViewData().GetDocument();
 290     ScDBData*       pDBData = GetDBData(false, SC_DB_MAKE, SC_DBSEL_ROW_DOWN);
 291
 292     pDBData->SetByRow( true );              //! undo, retrieve beforehand ??
 293     pDBData->GetQueryParam( aParam );
 294
 295     SCCOL  nCol;
 296     SCROW  nRow = aParam.nRow1;
 297     SCTAB  nTab = GetViewData().GetTabNo();
 298     sal_Int16   nFlag;
 299     bool    bHasAuto = true;
 300     bool    bHeader  = pDBData->HasHeader();
 301     bool    bPaint   = false;
 302
 303     //!     instead retrieve from DB-range?
 304
 305     for (nCol=aParam.nCol1; nCol<=aParam.nCol2 && bHasAuto; nCol++)
 306     {
 307         nFlag = static_cast<const ScMergeFlagAttr*>( pDoc->
 308                 GetAttr( nCol, nRow, nTab, ATTR_MERGE_FLAG ))->GetValue();
 309
 310         if ( (nFlag & SC_MF_AUTO) == 0 )
 311             bHasAuto = false;
 312     }
 313
 314     if (bHasAuto)                               // remove
 315     {
 316         //  hide filter buttons
 317
 318         for (nCol=aParam.nCol1; nCol<=aParam.nCol2; nCol++)
 319         {
 320             nFlag = static_cast<const ScMergeFlagAttr*>( pDoc->
 321                     GetAttr( nCol, nRow, nTab, ATTR_MERGE_FLAG ))->GetValue();
 322             pDoc->ApplyAttr( nCol, nRow, nTab, ScMergeFlagAttr( nFlag & ~SC_MF_AUTO ) );
 323         }
 324
 325         // use a list action for the AutoFilter buttons (ScUndoAutoFilter) and the filter operation
 326
 327         OUString aUndo = ScGlobal::GetRscString( STR_UNDO_QUERY );
 328         pDocSh->GetUndoManager()->EnterListAction( aUndo, aUndo );
 329
 330         ScRange aRange;
 331         pDBData->GetArea( aRange );
 332         pDocSh->GetUndoManager()->AddUndoAction(
 333             new ScUndoAutoFilter( pDocSh, aRange, pDBData->GetName(), false ) );
 334
 335         pDBData->SetAutoFilter(false);
 336
 337         //  remove filter (incl. Paint / Undo)
 338
 339         SCSIZE nEC = aParam.GetEntryCount();
 340         for (SCSIZE i=0; i<nEC; i++)
 341             aParam.GetEntry(i).bDoQuery = false;
 342         aParam.bDuplicate = true;
 343         Query( aParam, NULL, true );
 344
 345         pDocSh->GetUndoManager()->LeaveListAction();
 346
 347         bPaint = true;
 348     }
 349     else                                    // show filter buttons
 350     {
 351         if ( !pDoc->IsBlockEmpty( nTab,
 352                                   aParam.nCol1, aParam.nRow1,
 353                                   aParam.nCol2, aParam.nRow2 ) )
 354         {
 355             if (!bHeader)
 356             {
 357                 if ( ScopedVclPtr<MessBox>::Create( GetViewData().GetDialogParent(), WinBits(WB_YES_NO | WB_DEF_YES),
 358                         ScGlobal::GetRscString( STR_MSSG_DOSUBTOTALS_0 ),       // "StarCalc"
 359                         ScGlobal::GetRscString( STR_MSSG_MAKEAUTOFILTER_0 )     // header from first row?
 360                     )->Execute() == RET_YES )
 361                 {
 362                     pDBData->SetHeader( true );     //! Undo ??
 363                     bHeader = true;
 364                 }
 365             }
 366
 367             ScRange aRange;
 368             pDBData->GetArea( aRange );
 369             pDocSh->GetUndoManager()->AddUndoAction(
 370                 new ScUndoAutoFilter( pDocSh, aRange, pDBData->GetName(), true ) );
 371
 372             pDBData->SetAutoFilter(true);
 373
 374             for (nCol=aParam.nCol1; nCol<=aParam.nCol2; nCol++)
 375             {
 376                 nFlag = static_cast<const ScMergeFlagAttr*>( pDoc->
 377                         GetAttr( nCol, nRow, nTab, ATTR_MERGE_FLAG ))->GetValue();
 378                 pDoc->ApplyAttr( nCol, nRow, nTab, ScMergeFlagAttr( nFlag | SC_MF_AUTO ) );
 379             }
 380             pDocSh->PostPaint(ScRange(aParam.nCol1, nRow, nTab, aParam.nCol2, nRow, nTab),
 381                               PAINT_GRID);
 382             bPaint = true;
 383         }
 384         else
 385         {
 386             ScopedVclPtrInstance<MessageDialog> aErrorBox(GetViewData().GetDialogParent(),
 387                                 ScGlobal::GetRscString(STR_ERR_AUTOFILTER));
 388             aErrorBox->Execute();
 389         }
 390     }
 391
 392     if ( bPaint )
 393     {
 394         aModificator.SetDocumentModified();
 395
 396         SfxBindings& rBindings = GetViewData().GetBindings();
 397         rBindings.Invalidate( SID_AUTO_FILTER );
 398         rBindings.Invalidate( SID_AUTOFILTER_HIDE );
 399     }
 400 }
 401
 402 //      just hide, no data change
 403
 404 void ScDBFunc::HideAutoFilter()
 405 {
 406     ScDocShell* pDocSh = GetViewData().GetDocShell();
 407     ScDocShellModificator aModificator( *pDocSh );
 408
 409     ScDocument& rDoc = pDocSh->GetDocument();
 410
 411     ScQueryParam aParam;
 412     ScDBData* pDBData = GetDBData( false );
 413
 414     SCTAB nTab;
 415     SCCOL nCol1, nCol2;
 416     SCROW nRow1, nRow2;
 417     pDBData->GetArea(nTab, nCol1, nRow1, nCol2, nRow2);
 418
 419     for (SCCOL nCol=nCol1; nCol<=nCol2; nCol++)
 420     {
 421         sal_Int16 nFlag = static_cast<const ScMergeFlagAttr*>( rDoc.
 422                                 GetAttr( nCol, nRow1, nTab, ATTR_MERGE_FLAG ))->GetValue();
 423         rDoc.ApplyAttr( nCol, nRow1, nTab, ScMergeFlagAttr( nFlag & ~SC_MF_AUTO ) );
 424     }
 425
 426     ScRange aRange;
 427     pDBData->GetArea( aRange );
 428     pDocSh->GetUndoManager()->AddUndoAction(
 429         new ScUndoAutoFilter( pDocSh, aRange, pDBData->GetName(), false ) );
 430
 431     pDBData->SetAutoFilter(false);
 432
 433     pDocSh->PostPaint(ScRange(nCol1, nRow1, nTab, nCol2, nRow1, nTab), PAINT_GRID );
 434     aModificator.SetDocumentModified();
 435
 436     SfxBindings& rBindings = GetViewData().GetBindings();
 437     rBindings.Invalidate( SID_AUTO_FILTER );
 438     rBindings.Invalidate( SID_AUTOFILTER_HIDE );
 439 }
 440
 441 //      Re-Import
 442
 443 bool ScDBFunc::ImportData( const ScImportParam& rParam, bool bRecord )
 444 {
 445     ScDocument* pDoc = GetViewData().GetDocument();
 446     ScEditableTester aTester( pDoc, GetViewData().GetTabNo(), rParam.nCol1,rParam.nRow1,
 447                                                             rParam.nCol2,rParam.nRow2 );
 448     if ( !aTester.IsEditable() )
 449     {
 450         ErrorMessage(aTester.GetMessageId());
 451         return false;
 452     }
 453
 454     ScDBDocFunc aDBDocFunc( *GetViewData().GetDocShell() );
 455     return aDBDocFunc.DoImport( GetViewData().GetTabNo(), rParam, NULL, bRecord );
 456 }
 457
 458 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */