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 package com.sun.star.wizards.query; 24 25 import com.sun.star.lang.XMultiServiceFactory; 26 import com.sun.star.awt.VclWindowPeerAttribute; 27 import com.sun.star.awt.XWindowPeer; 28 import com.sun.star.beans.PropertyValue; 29 import com.sun.star.frame.XFrame; 30 import com.sun.star.sdb.CommandType; 31 import com.sun.star.sdbc.SQLException; 32 import com.sun.star.uno.AnyConverter; 33 import com.sun.star.wizards.ui.UIConsts; 34 import com.sun.star.uno.UnoRuntime; 35 import com.sun.star.wizards.common.Helper; 36 import com.sun.star.wizards.common.JavaTools; 37 import com.sun.star.wizards.common.PropertyNames; 38 import com.sun.star.wizards.common.Resource; 39 import com.sun.star.wizards.db.DatabaseObjectWizard; 40 import com.sun.star.wizards.db.QueryMetaData; 41 import com.sun.star.wizards.ui.AggregateComponent; 42 import com.sun.star.wizards.ui.CommandFieldSelection; 43 import com.sun.star.wizards.ui.FieldSelection; 44 import com.sun.star.wizards.ui.FilterComponent; 45 import com.sun.star.wizards.ui.SortingComponent; 46 import com.sun.star.wizards.ui.TitlesComponent; 47 48 public class QueryWizard extends DatabaseObjectWizard 49 { 50 51 public static final String SFILLUPFIELDSLISTBOX = "fillUpFieldsListbox"; 52 private static final int SOFIELDSELECTION_PAGE = 1; 53 private static final int SOSORTING_PAGE = 2; 54 private static final int SOFILTER_PAGE = 3; 55 private static final int SOAGGREGATE_PAGE = 4; 56 private static final int SOGROUPSELECTION_PAGE = 5; 57 private static final int SOGROUPFILTER_PAGE = 6; 58 private static final int SOTITLES_PAGE = 7; 59 protected static final int SOSUMMARY_PAGE = 8; 60 private CommandFieldSelection m_DBCommandFieldSelectio; 61 private SortingComponent m_sortingComponent; 62 private FieldSelection m_groupFieldSelection; 63 private TitlesComponent m_titlesComponent; 64 private FilterComponent m_filterComponent; 65 private FilterComponent m_groupFilterComponent; 66 private AggregateComponent m_aggregateComponent; 67 private Finalizer m_finalizer; 68 private QuerySummary m_DBMetaData; 69 private String reslblFieldHeader; 70 private String reslblAliasHeader; 71 private String reslblFields; 72 private String reslblSelFields; 73 private String reslblTables; 74 private String reslblGroupBy; 75 private String resmsgNonNumericAsGroupBy; 76 private String m_createdQuery; 77 QueryWizard(XMultiServiceFactory xMSF, PropertyValue[] i_wizardContext)78 public QueryWizard(XMultiServiceFactory xMSF, PropertyValue[] i_wizardContext) 79 { 80 super(xMSF, 40970, i_wizardContext); 81 addResourceHandler("QueryWizard", "dbw"); 82 m_DBMetaData = new QuerySummary(xMSF, m_oResource); 83 } 84 main(String i_args[])85 public static void main(String i_args[]) 86 { 87 executeWizardFromCommandLine( i_args, QueryWizard.class.getName() ); 88 } 89 getFrame()90 public final XFrame getFrame() 91 { 92 return m_frame; 93 } 94 start()95 public String start() 96 { 97 try 98 { 99 if (m_DBMetaData.getConnection(m_wizardContext)) 100 { 101 reslblFields = m_oResource.getResText(UIConsts.RID_QUERY + 4); 102 reslblFieldHeader = m_oResource.getResText(UIConsts.RID_QUERY + 19); //Fielnames in AliasComponent 103 reslblAliasHeader = m_oResource.getResText(UIConsts.RID_QUERY + 20); //Fieldtitles header in AliasComponent 104 reslblSelFields = m_oResource.getResText(UIConsts.RID_QUERY + 50); 105 reslblTables = m_oResource.getResText(UIConsts.RID_QUERY + 3); 106 reslblGroupBy = m_oResource.getResText(UIConsts.RID_QUERY + 18); 107 String resQueryWizard = m_oResource.getResText(UIConsts.RID_QUERY + 2); 108 resmsgNonNumericAsGroupBy = m_oResource.getResText(UIConsts.RID_QUERY + 88); 109 Helper.setUnoPropertyValues(xDialogModel, new String[] 110 { 111 PropertyNames.PROPERTY_HEIGHT, PropertyNames.PROPERTY_MOVEABLE, PropertyNames.PROPERTY_NAME, PropertyNames.PROPERTY_POSITION_X, PropertyNames.PROPERTY_POSITION_Y, PropertyNames.PROPERTY_STEP, PropertyNames.PROPERTY_TABINDEX, PropertyNames.PROPERTY_TITLE, PropertyNames.PROPERTY_WIDTH 112 }, 113 new Object[] 114 { 115 210, Boolean.TRUE, "DialogQuery", 102, 41, 1, new Short((short) 0), resQueryWizard, 310 116 }); 117 drawNaviBar(); 118 setRightPaneHeaders(m_oResource, UIConsts.RID_QUERY + 70, 8); 119 this.setMaxStep(8); 120 buildSteps(); 121 this.m_DBCommandFieldSelectio.preselectCommand(m_wizardContext, false); 122 123 XWindowPeer windowPeer = UnoRuntime.queryInterface(XWindowPeer.class, m_frame.getContainerWindow()); 124 createWindowPeer(windowPeer); 125 m_DBMetaData.setWindowPeer(this.xControl.getPeer()); 126 insertQueryRelatedSteps(); 127 executeDialog(m_frame.getContainerWindow().getPosSize()); 128 } 129 } 130 catch (java.lang.Exception jexception) 131 { 132 jexception.printStackTrace(System.out); 133 } 134 m_groupFilterComponent = null; 135 m_titlesComponent = null; 136 m_aggregateComponent = null; 137 m_DBCommandFieldSelectio = null; 138 xWindowPeer = null; 139 m_finalizer = null; 140 m_DBMetaData.finish(); 141 m_DBMetaData = null; 142 System.gc(); 143 144 return m_createdQuery; 145 } 146 enableRoadmapItems(String[] _FieldNames, boolean _bEnabled)147 public void enableRoadmapItems(String[] _FieldNames, boolean _bEnabled) 148 { 149 try 150 { 151 Object oRoadmapItem; 152 int CurStep = AnyConverter.toInt(Helper.getUnoPropertyValue(xDialogModel, PropertyNames.PROPERTY_STEP)); 153 boolean bEnabled = false; 154 int CurItemID; 155 for (int i = 0; i < getRMItemCount(); i++) 156 { 157 oRoadmapItem = this.xIndexContRoadmap.getByIndex(i); 158 CurItemID = AnyConverter.toInt(Helper.getUnoPropertyValue(oRoadmapItem, "ID")); 159 switch (CurItemID) 160 { 161 case SOAGGREGATE_PAGE: 162 if (_bEnabled) 163 { 164 bEnabled = ((m_DBMetaData.hasNumericalFields()) && (m_DBMetaData.xDBMetaData.supportsCoreSQLGrammar())); 165 } 166 break; 167 case SOGROUPSELECTION_PAGE: 168 bEnabled = m_DBMetaData.Type == QueryMetaData.QueryType.SOSUMMARYQUERY; 169 break; 170 case SOGROUPFILTER_PAGE: 171 bEnabled = false; 172 if (_bEnabled) 173 { 174 bEnabled = (m_DBMetaData.GroupByFilterConditions.length > 0); 175 } 176 177 break; 178 default: 179 if (CurItemID > CurStep) 180 { 181 bEnabled = _bEnabled; 182 } 183 else 184 { 185 bEnabled = true; 186 } 187 break; 188 } 189 super.setStepEnabled(CurItemID, bEnabled); 190 } 191 } 192 catch (com.sun.star.uno.Exception exception) 193 { 194 exception.printStackTrace(System.out); 195 } 196 } 197 insertQueryRelatedSteps()198 public void insertQueryRelatedSteps() 199 { 200 try 201 { 202 setRMItemLabels(m_oResource, UIConsts.RID_QUERY + 80); 203 addRoadmap(); 204 int i = 0; 205 i = insertRoadmapItem(0, true, SOFIELDSELECTION_PAGE - 1, SOFIELDSELECTION_PAGE); 206 i = insertRoadmapItem(i, false, SOSORTING_PAGE - 1, SOSORTING_PAGE); // Orderby is always supported 207 i = insertRoadmapItem(i, false, SOFILTER_PAGE - 1, SOFILTER_PAGE); 208 if (m_DBMetaData.xDBMetaData.supportsCoreSQLGrammar()) 209 { 210 i = insertRoadmapItem(i, m_DBMetaData.hasNumericalFields(), SOAGGREGATE_PAGE - 1, SOAGGREGATE_PAGE); 211 } 212 if (m_DBMetaData.xDBMetaData.supportsGroupBy()) 213 { 214 i = insertRoadmapItem(i, false, SOGROUPSELECTION_PAGE - 1, SOGROUPSELECTION_PAGE); 215 i = insertRoadmapItem(i, false, SOGROUPFILTER_PAGE - 1, SOGROUPFILTER_PAGE); 216 } 217 // if (CurDBMetaData.xDBMetaData.supportsColumnAliasing()) don't use -> too dangerous!!! 218 i = insertRoadmapItem(i, false, SOTITLES_PAGE - 1, SOTITLES_PAGE); 219 i = insertRoadmapItem(i, false, SOSUMMARY_PAGE - 1, SOSUMMARY_PAGE); 220 setRoadmapInteractive(true); 221 setRoadmapComplete(true); 222 setCurrentRoadmapItemID((short) 1); 223 } 224 catch (com.sun.star.uno.Exception exception) 225 { 226 Resource.showCommonResourceError(xMSF); 227 } 228 } 229 buildSteps()230 public void buildSteps() 231 { 232 try 233 { 234 m_DBCommandFieldSelectio = new CommandFieldSelection( 235 this, m_DBMetaData, 120, reslblFields, reslblSelFields, reslblTables, 236 m_DBMetaData.supportsQueriesInFrom(), 40850); 237 m_DBCommandFieldSelectio.setAppendMode(true); 238 m_DBCommandFieldSelectio.addFieldSelectionListener(new FieldSelectionListener()); 239 m_sortingComponent = new SortingComponent(this, SOSORTING_PAGE, 95, 27, 210, 40865); 240 m_filterComponent = new FilterComponent(this, xMSF, SOFILTER_PAGE, 97, 27, 209, 3, m_DBMetaData, 40878); 241 m_filterComponent.addNumberFormats(); 242 243 if (m_DBMetaData.xDBMetaData.supportsCoreSQLGrammar()) 244 { 245 m_aggregateComponent = new AggregateComponent(this, m_DBMetaData, SOAGGREGATE_PAGE, 97, 69, 209, 5, 40895); 246 } 247 if (m_DBMetaData.xDBMetaData.supportsGroupBy()) 248 { 249 m_groupFieldSelection = new FieldSelection(this, SOGROUPSELECTION_PAGE, 95, 27, 210, 150, reslblFields, this.reslblGroupBy, 40915, false); 250 m_groupFieldSelection.addFieldSelectionListener(new FieldSelectionListener()); 251 m_groupFilterComponent = new FilterComponent(this, xMSF, SOGROUPFILTER_PAGE, 97, 27, 209, 3, m_DBMetaData, 40923); 252 } 253 m_titlesComponent = new TitlesComponent(this, SOTITLES_PAGE, 97, 37, 207, 7, reslblFieldHeader, reslblAliasHeader, 40940); 254 m_finalizer = new Finalizer(this, m_DBMetaData); 255 enableNavigationButtons(false, false, false); 256 } 257 catch (com.sun.star.uno.Exception exception) 258 { 259 Resource.showCommonResourceError(xMSF); 260 } 261 } 262 finishWizard()263 public boolean finishWizard() 264 { 265 int ncurStep = getCurrentStep(); 266 if ((ncurStep == SOSUMMARY_PAGE) 267 || (switchToStep(ncurStep, SOSUMMARY_PAGE))) 268 { 269 m_createdQuery = m_finalizer.finish(); 270 if (m_createdQuery.length() > 0) 271 { 272 loadSubComponent(CommandType.QUERY, m_createdQuery, m_finalizer.displayQueryDesign()); 273 xDialog.endExecute(); 274 return true; 275 } 276 } 277 return false; 278 } 279 enterStep(int nOldStep, int nNewStep)280 protected void enterStep(int nOldStep, int nNewStep) 281 { 282 try 283 { 284 if (nOldStep <= SOGROUPSELECTION_PAGE && nNewStep > SOGROUPSELECTION_PAGE) 285 { 286 if (m_DBMetaData.xDBMetaData.supportsGroupBy()) 287 { 288 m_DBMetaData.setGroupFieldNames(m_groupFieldSelection.getSelectedFieldNames()); 289 m_DBMetaData.GroupFieldNames = JavaTools.removeOutdatedFields(m_DBMetaData.GroupFieldNames, m_DBMetaData.NonAggregateFieldNames); 290 m_DBMetaData.GroupByFilterConditions = JavaTools.removeOutdatedFields(m_DBMetaData.GroupByFilterConditions, m_DBMetaData.GroupFieldNames); 291 } 292 } 293 switch (nNewStep) 294 { 295 case SOFIELDSELECTION_PAGE: 296 break; 297 case SOSORTING_PAGE: 298 m_sortingComponent.initialize(m_DBMetaData.getDisplayFieldNames(), m_DBMetaData.getSortFieldNames()); 299 break; 300 case SOFILTER_PAGE: 301 m_filterComponent.initialize(m_DBMetaData.getFilterConditions(), m_DBMetaData.getDisplayFieldNames()); 302 break; 303 case SOAGGREGATE_PAGE: 304 m_aggregateComponent.initialize(); 305 break; 306 case SOGROUPSELECTION_PAGE: 307 break; 308 case SOGROUPFILTER_PAGE: 309 m_groupFilterComponent.initialize(m_DBMetaData.GroupByFilterConditions, m_DBMetaData.getGroupFieldNames()); 310 break; 311 case SOTITLES_PAGE: 312 m_titlesComponent.initialize(m_DBMetaData.getDisplayFieldNames(), m_DBMetaData.FieldTitleSet); 313 break; 314 case SOSUMMARY_PAGE: 315 m_finalizer.initialize(); 316 break; 317 default: 318 break; 319 } 320 } 321 catch (SQLException e) 322 { 323 e.printStackTrace(System.out); 324 } 325 } 326 leaveStep(int nOldStep, int nNewStep)327 protected void leaveStep(int nOldStep, int nNewStep) 328 { 329 switch (nOldStep) 330 { 331 case SOFIELDSELECTION_PAGE: 332 m_DBMetaData.reorderFieldColumns(m_DBCommandFieldSelectio.getSelectedFieldNames()); 333 m_DBMetaData.initializeFieldTitleSet(true); 334 m_DBMetaData.setNumericFields(); 335 searchForOutdatedFields(); 336 break; 337 case SOSORTING_PAGE: 338 m_DBMetaData.setSortFieldNames(m_sortingComponent.getSortFieldNames()); 339 break; 340 case SOFILTER_PAGE: 341 m_DBMetaData.setFilterConditions(m_filterComponent.getFilterConditions()); 342 break; 343 case SOAGGREGATE_PAGE: 344 m_DBMetaData.AggregateFieldNames = m_aggregateComponent.getAggregateFieldNames(); 345 break; 346 case SOGROUPSELECTION_PAGE: 347 break; 348 case SOGROUPFILTER_PAGE: 349 m_DBMetaData.setGroupByFilterConditions(this.m_groupFilterComponent.getFilterConditions()); 350 break; 351 case SOTITLES_PAGE: 352 m_DBMetaData.setFieldTitles(m_titlesComponent.getFieldTitles()); 353 break; 354 case SOSUMMARY_PAGE: 355 break; 356 default: 357 break; 358 } 359 if (nOldStep < SOGROUPSELECTION_PAGE && nNewStep >= SOGROUPSELECTION_PAGE) 360 { 361 try 362 { 363 if (m_DBMetaData.Type == QueryMetaData.QueryType.SOSUMMARYQUERY) 364 { 365 if (m_DBMetaData.xDBMetaData.supportsGroupBy()) 366 { 367 m_DBMetaData.setNonAggregateFieldNames(); 368 m_groupFieldSelection.initialize(m_DBMetaData.getUniqueAggregateFieldNames(), false, m_DBMetaData.xDBMetaData.getMaxColumnsInGroupBy()); 369 m_groupFieldSelection.intializeSelectedFields(m_DBMetaData.NonAggregateFieldNames); 370 m_groupFieldSelection.setMultipleMode(false); 371 setStepEnabled(SOGROUPFILTER_PAGE, m_aggregateComponent.isGroupingpossible() && m_DBMetaData.NonAggregateFieldNames.length > 0); 372 } 373 } 374 } 375 catch (SQLException e) 376 { 377 e.printStackTrace( System.err ); 378 } 379 } 380 } 381 searchForOutdatedFields()382 private void searchForOutdatedFields() 383 { 384 String[] sFieldNames = m_DBMetaData.getFieldNames(); 385 String[][] sRemovedFields = JavaTools.removeOutdatedFields(m_DBMetaData.getSortFieldNames(), sFieldNames); 386 m_DBMetaData.setSortFieldNames(sRemovedFields); 387 m_DBMetaData.setFilterConditions(JavaTools.removeOutdatedFields(m_DBMetaData.getFilterConditions(), sFieldNames)); 388 m_DBMetaData.AggregateFieldNames = JavaTools.removeOutdatedFields(m_DBMetaData.AggregateFieldNames, sFieldNames); 389 } 390 enableWizardSteps(String[] NewItems)391 private void enableWizardSteps(String[] NewItems) 392 { 393 boolean bEnabled = NewItems.length > 0; 394 setControlProperty("btnWizardNext", PropertyNames.PROPERTY_ENABLED, bEnabled); 395 setControlProperty("btnWizardFinish", PropertyNames.PROPERTY_ENABLED, bEnabled); 396 enableRoadmapItems(NewItems, bEnabled); // Note: Performancewise this could be improved 397 } 398 399 public class FieldSelectionListener implements com.sun.star.wizards.ui.XFieldSelectionListener 400 { 401 402 protected int ID; 403 getID()404 public int getID() 405 { 406 return ID; 407 } 408 setID(String sIncSuffix)409 public void setID(String sIncSuffix) 410 { 411 ID = 1; 412 if (sIncSuffix != null) 413 { 414 if ((!sIncSuffix.equals(PropertyNames.EMPTY_STRING)) && (!sIncSuffix.equals("_"))) 415 { 416 String sID = JavaTools.ArrayoutofString(sIncSuffix, "_")[1]; 417 ID = Integer.parseInt(sID); 418 int a = 0; 419 } 420 } 421 } 422 shiftFromLeftToRight(String[] SelItems, String[] NewItems)423 public void shiftFromLeftToRight(String[] SelItems, String[] NewItems) 424 { 425 if (ID == 1) 426 { 427 m_DBMetaData.addSeveralFieldColumns(SelItems, m_DBCommandFieldSelectio.getSelectedCommandName()); 428 enableWizardSteps(NewItems); 429 m_DBCommandFieldSelectio.changeSelectedFieldNames(m_DBMetaData.getDisplayFieldNames()); 430 m_DBCommandFieldSelectio.toggleCommandListBox(NewItems); 431 } 432 else 433 { 434 boolean bEnabled = (m_groupFieldSelection.getSelectedFieldNames().length > 0); 435 Helper.setUnoPropertyValue(getRoadmapItemByID(SOGROUPFILTER_PAGE), PropertyNames.PROPERTY_ENABLED, bEnabled); 436 } 437 } 438 shiftFromRightToLeft(String[] SelItems, String[] NewItems)439 public void shiftFromRightToLeft(String[] SelItems, String[] NewItems) 440 { 441 // TODO When the ListFieldbox is refilled only fields of the current Command may be merged into the Listbox 442 if (ID == 1) 443 { 444 enableWizardSteps(NewItems); 445 String[] sSelfieldNames = m_DBMetaData.getFieldNames(SelItems, m_DBCommandFieldSelectio.getSelectedCommandName()); 446 m_DBCommandFieldSelectio.addItemsToFieldsListbox(sSelfieldNames); 447 m_DBMetaData.removeSeveralFieldColumnsByDisplayFieldName(SelItems); 448 m_DBCommandFieldSelectio.toggleCommandListBox(NewItems); 449 450 } 451 else 452 { 453 boolean bEnabled = (m_groupFieldSelection.getSelectedFieldNames().length > 0); 454 String CurDisplayFieldName = SelItems[0]; 455 if (JavaTools.FieldInList(m_DBMetaData.NonAggregateFieldNames, CurDisplayFieldName) > -1) 456 { 457 showMessageBox("ErrorBox", VclWindowPeerAttribute.OK, resmsgNonNumericAsGroupBy); 458 m_groupFieldSelection.xSelectedFieldsListBox.addItems(SelItems, m_groupFieldSelection.xSelectedFieldsListBox.getItemCount()); 459 String FieldList[] = m_groupFieldSelection.xFieldsListBox.getItems(); 460 int index = JavaTools.FieldInList(FieldList, CurDisplayFieldName); 461 if (index > -1) 462 { 463 m_groupFieldSelection.xFieldsListBox.removeItems((short) index, (short) 1); 464 } 465 } 466 else 467 { 468 Helper.setUnoPropertyValue(getRoadmapItemByID(SOGROUPFILTER_PAGE), PropertyNames.PROPERTY_ENABLED, bEnabled); 469 } 470 } 471 } 472 moveItemDown(String item)473 public void moveItemDown(String item) 474 { 475 } 476 moveItemUp(String item)477 public void moveItemUp(String item) 478 { 479 } 480 } 481 } 482