xref: /aoo42x/main/sc/source/ui/view/dbfunc.cxx (revision 22407013)
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