/*
* JasperReports - Free Java Reporting Library.
* Copyright (C) 2001 - 2009 Jaspersoft Corporation. All rights reserved.
* http://www.jaspersoft.com
*
* Unless you have purchased a commercial license agreement from Jaspersoft,
* the following license terms apply:
*
* This program is part of JasperReports.
*
* JasperReports is free software: you can redistribute it and/or modify
* it under the terms of the GNU Lesser General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* JasperReports is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License
* along with JasperReports. If not, see <http://www.gnu.org/licenses/>.
*/
package net.sf.jasperreports.olap.xmla;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.xml.soap.MessageFactory;
import javax.xml.soap.MimeHeaders;
import javax.xml.soap.Name;
import javax.xml.soap.SOAPBody;
import javax.xml.soap.SOAPConnection;
import javax.xml.soap.SOAPConnectionFactory;
import javax.xml.soap.SOAPElement;
import javax.xml.soap.SOAPEnvelope;
import javax.xml.soap.SOAPException;
import javax.xml.soap.SOAPFactory;
import javax.xml.soap.SOAPFault;
import javax.xml.soap.SOAPMessage;
import javax.xml.soap.SOAPPart;
import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JRDataset;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRRuntimeException;
import net.sf.jasperreports.engine.query.JRAbstractQueryExecuter;
import net.sf.jasperreports.olap.JROlapDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* @author Michael G�nther (m.guenther at users.sourceforge.net)
* @version $Id: JRXmlaQueryExecuter.java 3748 2010-04-16 17:12:43Z lucianc $
*/
public class JRXmlaQueryExecuter extends JRAbstractQueryExecuter
{
private static final Log log = LogFactory.getLog(JRXmlaQueryExecuter.class);
private static final String SLICER_AXIS_NAME = "SlicerAxis";
private static final String MDD_URI = "urn:schemas-microsoft-com:xml-analysis:mddataset";
private static final String XMLA_URI = "urn:schemas-microsoft-com:xml-analysis";
private static final Pattern LEVEL_UNIQUE_NAME_PATTERN = Pattern.compile("\\[[^\\]]+\\]\\.\\[([^\\]]+)\\]");
private static final int LEVEL_UNIQUE_NAME_PATTERN_NAME_GROUP = 1;
private SOAPFactory sf;
private SOAPConnection connection;
private JRXmlaResult xmlaResult;
public JRXmlaQueryExecuter(JRDataset dataset, Map parametersMap)
{
super(dataset, parametersMap);
parseQuery();
}
protected String getParameterReplacement(String parameterName)
{
return String.valueOf(getParameterValue(parameterName));
}
public JRDataSource createDatasource() throws JRException
{
try
{
this.sf = SOAPFactory.newInstance();
this.connection = createSOAPConnection();
SOAPMessage queryMessage = createQueryMessage();
URL soapURL = new URL(getSoapUrl());
SOAPMessage resultMessage = executeQuery(queryMessage, soapURL);
xmlaResult = new JRXmlaResult();
parseResult(resultMessage);
}
catch (MalformedURLException e)
{
throw new JRRuntimeException(e);
}
catch (SOAPException e)
{
throw new JRRuntimeException(e);
}
return new JROlapDataSource(dataset, xmlaResult);
}
protected String getSoapUrl() throws MalformedURLException
{
String soapUrl;
String xmlaUrl = (String) getParameterValue(JRXmlaQueryExecuterFactory.PARAMETER_XMLA_URL);
String user = (String) getParameterValue(JRXmlaQueryExecuterFactory.PARAMETER_XMLA_USER, true);
if (user == null || user.length() == 0)
{
soapUrl = xmlaUrl;
}
else
{
URL url = new URL(xmlaUrl);
soapUrl = url.getProtocol() + "://" + user;
String password = (String) getParameterValue(JRXmlaQueryExecuterFactory.PARAMETER_XMLA_PASSWORD, true);
if (password != null && password.length() > 0)
{
soapUrl += ":" + password;
}
soapUrl += "@" + url.getHost() + ":" + url.getPort() + url.getPath();
}
return soapUrl;
}
public boolean cancelQuery() throws JRException
{
return false;
}
public void close()
{
if (connection != null)
{
try
{
connection.close();
}
catch (SOAPException e)
{
throw new JRRuntimeException(e);
}
connection = null;
}
}
protected SOAPConnection createSOAPConnection()
{
try
{
SOAPConnectionFactory scf = SOAPConnectionFactory.newInstance();
return scf.createConnection();
}
catch (UnsupportedOperationException e)
{
throw new JRRuntimeException(e);
}
catch (SOAPException e)
{
throw new JRRuntimeException(e);
}
}
protected SOAPMessage createQueryMessage()
{
String queryStr = getQueryString();
if (log.isDebugEnabled())
{
log.debug("MDX query: " + queryStr);
}
try
{
MessageFactory mf = MessageFactory.newInstance();
SOAPMessage message = mf.createMessage();
MimeHeaders mh = message.getMimeHeaders();
mh.setHeader("SOAPAction", "\"urn:schemas-microsoft-com:xml-analysis:Execute\"");
SOAPPart soapPart = message.getSOAPPart();
SOAPEnvelope envelope = soapPart.getEnvelope();
SOAPBody body = envelope.getBody();
Name nEx = envelope.createName("Execute", "", XMLA_URI);
SOAPElement eEx = body.addChildElement(nEx);
// add the parameters
// COMMAND parameter
// <Command>
// <Statement>queryStr</Statement>
// </Command>
Name nCom = envelope.createName("Command", "", XMLA_URI);
SOAPElement eCommand = eEx.addChildElement(nCom);
Name nSta = envelope.createName("Statement", "", XMLA_URI);
SOAPElement eStatement = eCommand.addChildElement(nSta);
eStatement.addTextNode(queryStr);
// <Properties>
// <PropertyList>
// <DataSourceInfo>dataSource</DataSourceInfo>
// <Catalog>catalog</Catalog>
// <Format>Multidimensional</Format>
// <AxisFormat>TupleFormat</AxisFormat>
// </PropertyList>
// </Properties>
Map paraList = new HashMap();
String datasource = (String) getParameterValue(JRXmlaQueryExecuterFactory.PARAMETER_XMLA_DATASOURCE);
paraList.put("DataSourceInfo", datasource);
String catalog = (String) getParameterValue(JRXmlaQueryExecuterFactory.PARAMETER_XMLA_CATALOG);
paraList.put("Catalog", catalog);
paraList.put("Format", "Multidimensional");
paraList.put("AxisFormat", "TupleFormat");
addParameterList(envelope, eEx, "Properties", "PropertyList", paraList);
message.saveChanges();
if (log.isDebugEnabled())
{
log.debug("XML/A query message: " + message.toString());
}
return message;
}
catch (SOAPException e)
{
throw new JRRuntimeException(e);
}
}
protected void addParameterList(SOAPEnvelope envelope, SOAPElement eParent, String typeName, String listName, Map params) throws SOAPException
{
Name nPara = envelope.createName(typeName, "", XMLA_URI);
SOAPElement eType = eParent.addChildElement(nPara);
nPara = envelope.createName(listName, "", XMLA_URI);
SOAPElement eList = eType.addChildElement(nPara);
if (params == null)
{
return;
}
for (Iterator entryIt = params.entrySet().iterator(); entryIt.hasNext();)
{
Map.Entry entry = (Map.Entry) entryIt.next();
String tag = (String) entry.getKey();
String value = (String) entry.getValue();
nPara = envelope.createName(tag, "", XMLA_URI);
SOAPElement eTag = eList.addChildElement(nPara);
eTag.addTextNode(value);
}
}
/**
* Sends the SOAP Message over the connection and returns the
* Result-SOAP-Message
*
* @return Reply-Message
*/
protected SOAPMessage executeQuery(SOAPMessage message, URL url)
{
try
{
return connection.call(message, url);
}
catch (SOAPException e)
{
throw new JRRuntimeException("Message-Call failed.", e);
}
}
/**
* Parses the result-Message into this class's structure
*
* @param reply
* The reply-Message from the Server
*/
protected void parseResult(SOAPMessage reply) throws SOAPException
{
SOAPPart soapPart = reply.getSOAPPart();
SOAPEnvelope soapEnvelope = soapPart.getEnvelope();
SOAPBody soapBody = soapEnvelope.getBody();
SOAPElement eElement = null;
if (log.isDebugEnabled())
{
log.debug("XML/A result envelope: " + soapEnvelope.toString());
}
SOAPFault fault = soapBody.getFault();
if (fault != null)
{
handleResultFault(fault);
}
Name eName = soapEnvelope.createName("ExecuteResponse", "", XMLA_URI);
// Get the ExecuteResponse-Node
Iterator responseElements = soapBody.getChildElements(eName);
if (responseElements.hasNext())
{
Object eObj = responseElements.next();
if (eObj == null)
{
throw new JRRuntimeException("ExecuteResponse Element is null.");
}
eElement = (SOAPElement) eObj;
}
else
{
throw new JRRuntimeException("Could not retrieve ExecuteResponse Element.");
}
// Get the return-Node
Name rName = soapEnvelope.createName("return", "", XMLA_URI);
Iterator returnElements = eElement.getChildElements(rName);
SOAPElement returnElement = null;
if (returnElements.hasNext())
{
Object eObj = returnElements.next();
if (eObj == null)
{
throw new JRRuntimeException("return Element is null.");
}
returnElement = (SOAPElement) eObj;
}
else
{
// Should be old-Microsoft XMLA-SDK. Try without m-prefix
Name rName2 = soapEnvelope.createName("return", "", "");
returnElements = eElement.getChildElements(rName2);
if (returnElements.hasNext())
{
Object eObj = returnElements.next();
if (eObj == null)
{
throw new JRRuntimeException("return Element is null.");
}
returnElement = (SOAPElement) eObj;
}
else
{
throw new JRRuntimeException("Could not retrieve return Element.");
}
}
// Get the root-Node
Name rootName = soapEnvelope.createName("root", "", MDD_URI);
SOAPElement rootElement = null;
Iterator rootElements = returnElement.getChildElements(rootName);
if (rootElements.hasNext())
{
Object eObj = rootElements.next();
if (eObj == null)
{
throw new JRRuntimeException("root Element is null.");
}
rootElement = (SOAPElement) eObj;
}
else
{
throw new JRRuntimeException("Could not retrieve root Element.");
}
// Get the OlapInfo-Node
Name olapInfoName = soapEnvelope.createName("OlapInfo", "", MDD_URI);
SOAPElement olapInfoElement = null;
Iterator olapInfoElements = rootElement.getChildElements(olapInfoName);
if (olapInfoElements.hasNext())
{
Object eObj = olapInfoElements.next();
if (eObj == null)
{
throw new JRRuntimeException("OlapInfo Element is null.");
}
olapInfoElement = (SOAPElement) eObj;
}
else
{
throw new JRRuntimeException("Could not retrieve OlapInfo Element.");
}
parseOLAPInfoElement(olapInfoElement);
// Get the Axes Element
Name axesName = soapEnvelope.createName("Axes", "", MDD_URI);
SOAPElement axesElement = null;
Iterator axesElements = rootElement.getChildElements(axesName);
if (axesElements.hasNext())
{
Object eObj = axesElements.next();
if (eObj == null)
{
throw new JRRuntimeException("Axes Element is null");
}
axesElement = (SOAPElement) eObj;
}
else
{
throw new JRRuntimeException("Could not retrieve Axes Element.");
}
parseAxesElement(axesElement);
// Get the CellData Element
Name cellDataName = soapEnvelope.createName("CellData", "", MDD_URI);
SOAPElement cellDataElement = null;
Iterator cellDataElements = rootElement.getChildElements(cellDataName);
if (cellDataElements.hasNext())
{
Object eObj = cellDataElements.next();
if (eObj == null)
{
throw new JRRuntimeException("CellData element is null");
}
cellDataElement = (SOAPElement) eObj;
}
else
{
throw new JRRuntimeException("Could not retrieve CellData Element.");
}
parseCellDataElement(cellDataElement);
}
protected void handleResultFault(SOAPFault fault)
{
StringBuffer errorMsg = new StringBuffer();
errorMsg.append("XML/A fault: ");
String faultString = fault.getFaultString();
if (faultString != null)
{
errorMsg.append(faultString);
errorMsg.append("; ");
}
String faultActor = fault.getFaultActor();
if (faultActor != null)
{
errorMsg.append("Actor: ");
errorMsg.append(faultActor);
errorMsg.append("; ");
}
String faultCode = fault.getFaultCode();
if (faultCode != null)
{
errorMsg.append("Code: ");
errorMsg.append(faultCode);
errorMsg.append("; ");
}
throw new JRRuntimeException(errorMsg.toString());
}
protected void parseOLAPInfoElement(SOAPElement olapInfoElement) throws SOAPException
{
// CubeInfo-Element is not needed
// Get the AxesInfo-Node
Name axesInfoName = sf.createName("AxesInfo", "", MDD_URI);
SOAPElement axesElement = null;
Iterator axesInfoElements = olapInfoElement.getChildElements(axesInfoName);
if (axesInfoElements.hasNext())
{
Object axesObj = axesInfoElements.next();
if (axesObj == null)
{
throw new JRRuntimeException("AxisInfo Element is null.");
}
axesElement = (SOAPElement) axesObj;
}
else
{
throw new JRRuntimeException("Could not retrieve AxesInfo Element.");
}
parseAxesInfoElement(axesElement);
// CellInfo is not needed
}
protected void parseAxesInfoElement(SOAPElement axesInfoElement) throws SOAPException
{
// Cycle over AxisInfo-Elements
Name axisInfoName = sf.createName("AxisInfo", "", MDD_URI);
Iterator itAxis = axesInfoElement.getChildElements(axisInfoName);
while (itAxis.hasNext())
{
SOAPElement axisElement = (SOAPElement) itAxis.next();
Name name = sf.createName("name");
String axisName = axisElement.getAttributeValue(name);
if (axisName.equals(SLICER_AXIS_NAME))
{
continue;
}
JRXmlaResultAxis axis = new JRXmlaResultAxis(axisName);
xmlaResult.addAxis(axis);
// retrieve the hierarchies by <HierarchyInfo>
name = sf.createName("HierarchyInfo", "", MDD_URI);
Iterator itHierInfo = axisElement.getChildElements(name);
while (itHierInfo.hasNext())
{
SOAPElement eHierInfo = (SOAPElement) itHierInfo.next();
handleHierInfo(axis, eHierInfo);
}
}
}
protected void parseAxesElement(SOAPElement axesElement) throws SOAPException
{
// Cycle over Axis-Elements
Name aName = sf.createName("Axis", "", MDD_URI);
Iterator itAxis = axesElement.getChildElements(aName);
while (itAxis.hasNext())
{
SOAPElement axisElement = (SOAPElement) itAxis.next();
Name name = sf.createName("name");
String axisName = axisElement.getAttributeValue(name);
if (axisName.equals(SLICER_AXIS_NAME))
{
continue;
}
// LookUp for the Axis
JRXmlaResultAxis axis = xmlaResult.getAxisByName(axisName);
// retrieve the tuples by <Tuples>
name = sf.createName("Tuples", "", MDD_URI);
Iterator itTuples = axisElement.getChildElements(name);
if (itTuples.hasNext())
{
SOAPElement eTuples = (SOAPElement) itTuples.next();
handleTuplesElement(axis, eTuples);
}
}
}
protected void parseCellDataElement(SOAPElement cellDataElement) throws SOAPException
{
Name name = sf.createName("Cell", "", MDD_URI);
Iterator itCells = cellDataElement.getChildElements(name);
while (itCells.hasNext())
{
SOAPElement cellElement = (SOAPElement) itCells.next();
Name errorName = sf.createName("Error", "", MDD_URI);
Iterator errorElems = cellElement.getChildElements(errorName);
if (errorElems.hasNext())
{
handleCellErrors(errorElems);
}
Name ordinalName = sf.createName("CellOrdinal");
String cellOrdinal = cellElement.getAttributeValue(ordinalName);
Object value = null;
Iterator valueElements = cellElement.getChildElements(sf.createName("Value", "", MDD_URI));
if (valueElements.hasNext())
{
SOAPElement valueElement = (SOAPElement) valueElements.next();
String valueType = valueElement.getAttribute("xsi:type");
if (valueType.equals("xsd:int"))
{
value = new Long(valueElement.getValue());
}
else if (valueType.equals("xsd:double"))
{
value = new Double(valueElement.getValue());
}
else if (valueType.equals("xsd:decimal"))
{
value = new Double(valueElement.getValue());
}
else
{
value = valueElement.getValue();
}
}
String fmtValue = "";
Iterator fmtValueElements = cellElement.getChildElements(sf.createName("FmtValue", "", MDD_URI));
if (fmtValueElements.hasNext())
{
SOAPElement fmtValueElement = ((SOAPElement) fmtValueElements.next());
fmtValue = fmtValueElement.getValue();
}
int pos = Integer.parseInt(cellOrdinal);
JRXmlaCell cell = new JRXmlaCell(value, fmtValue);
xmlaResult.setCell(cell, pos);
}
}
protected void handleCellErrors(Iterator errorElems) throws SOAPException
{
SOAPElement errorElem = (SOAPElement) errorElems.next();
StringBuffer errorMsg = new StringBuffer();
errorMsg.append("Cell error: ");
Iterator descriptionElems = errorElem.getChildElements(sf.createName("Description", "", MDD_URI));
if (descriptionElems.hasNext())
{
SOAPElement descrElem = (SOAPElement) descriptionElems.next();
errorMsg.append(descrElem.getValue());
errorMsg.append("; ");
}
Iterator sourceElems = errorElem.getChildElements(sf.createName("Source", "", MDD_URI));
if (sourceElems.hasNext())
{
SOAPElement sourceElem = (SOAPElement) sourceElems.next();
errorMsg.append("Source: ");
errorMsg.append(sourceElem.getValue());
errorMsg.append("; ");
}
Iterator codeElems = errorElem.getChildElements(sf.createName("ErrorCode", "", MDD_URI));
if (codeElems.hasNext())
{
SOAPElement codeElem = (SOAPElement) codeElems.next();
errorMsg.append("Code: ");
errorMsg.append(codeElem.getValue());
errorMsg.append("; ");
}
throw new JRRuntimeException(errorMsg.toString());
}
protected void handleHierInfo(JRXmlaResultAxis axis, SOAPElement hierInfoElement) throws SOAPException
{
Name name = sf.createName("name");
String dimName = hierInfoElement.getAttributeValue(name); // Get the Dimension Name
JRXmlaHierarchy hier = new JRXmlaHierarchy(dimName);
axis.addHierarchy(hier);
}
protected void handleTuplesElement(JRXmlaResultAxis axis, SOAPElement tuplesElement) throws SOAPException
{
Name tName = sf.createName("Tuple", "", MDD_URI);
for (Iterator itTuple = tuplesElement.getChildElements(tName); itTuple.hasNext();)
{
SOAPElement eTuple = (SOAPElement) itTuple.next();
handleTupleElement(axis, eTuple);
}
}
protected void handleTupleElement(JRXmlaResultAxis axis, SOAPElement tupleElement) throws SOAPException
{
JRXmlaMemberTuple tuple = new JRXmlaMemberTuple(axis.getHierarchiesOnAxis().length);
Name memName = sf.createName("Member", "", MDD_URI);
Iterator itMember = tupleElement.getChildElements(memName);
int memNum = 0;
while (itMember.hasNext())
{
SOAPElement memElement = (SOAPElement) itMember.next();
Name name = sf.createName("Hierarchy", "", "");
String hierName = memElement.getAttributeValue(name);
String uName = "";
Iterator uNameElements = memElement.getChildElements(sf.createName("UName", "", MDD_URI));
if (uNameElements.hasNext())
{
uName = ((SOAPElement) uNameElements.next()).getValue();
}
String caption = "";
Iterator captionElements = memElement.getChildElements(sf.createName("Caption", "", MDD_URI));
if (captionElements.hasNext())
{
caption = ((SOAPElement) captionElements.next()).getValue();
}
String lName = "";
Iterator lNameElements = memElement.getChildElements(sf.createName("LName", "", MDD_URI));
if (lNameElements.hasNext())
{
String levelUniqueName = ((SOAPElement) lNameElements.next()).getValue();
Matcher matcher = LEVEL_UNIQUE_NAME_PATTERN.matcher(levelUniqueName);
if (matcher.matches())
{
lName = matcher.group(LEVEL_UNIQUE_NAME_PATTERN_NAME_GROUP);
}
}
int lNum = 0;
Iterator lNumElements = memElement.getChildElements(sf.createName("LNum", "", MDD_URI));
if (lNumElements.hasNext())
{
lNum = Integer.parseInt(((SOAPElement) lNumElements.next()).getValue());
}
JRXmlaMember member = new JRXmlaMember(caption, uName, hierName, lName, lNum);
tuple.setMember(memNum++, member);
}
axis.addTuple(tuple);
}
}