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