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