/*******************************************************************************
 * Copyright (c) 2004, 2005 Actuate Corporation. All rights reserved. This
 * program and the accompanying materials are made available under the terms of
 * the Eclipse Public License v1.0 which accompanies this distribution, and is
 * available at http://www.eclipse.org/legal/epl-v10.html
 * 
 * Contributors: Actuate Corporation - initial API and implementation
 ******************************************************************************/

package org.eclipse.birt.tests.data.engine.smoke;

import org.eclipse.birt.data.engine.api.IBaseExpression;
import org.eclipse.birt.data.engine.api.IPreparedQuery;
import org.eclipse.birt.data.engine.api.IQueryResults;
import org.eclipse.birt.data.engine.api.IResultIterator;
import org.eclipse.birt.data.engine.api.ISortDefinition;
import org.eclipse.birt.data.engine.api.querydefn.ConditionalExpression;
import org.eclipse.birt.data.engine.api.querydefn.FilterDefinition;
import org.eclipse.birt.data.engine.api.querydefn.GroupDefinition;
import org.eclipse.birt.data.engine.api.querydefn.OdaDataSetDesign;
import org.eclipse.birt.data.engine.api.querydefn.QueryDefinition;
import org.eclipse.birt.data.engine.api.querydefn.ScriptExpression;
import org.eclipse.birt.data.engine.api.querydefn.SortDefinition;
import org.eclipse.birt.data.engine.core.DataException;

import testutil.APITestCase;
import testutil.ConfigText;
import testutil.APITestCase.DataSourceInfo;

/**
 * DtE features test.
 */
public class DsGroupTest extends APITestCase
{

	/*
	 * @see org.eclipse.birt.data.engine.api.APITestCase#getDataSourceInfo()
	 */
	protected DataSourceInfo getDataSourceInfo( )
	{
		return new DataSourceInfo( ConfigText
				.getString( "Api.TestData.TableName" ), ConfigText
				.getString( "Api.TestData.TableSQL" ), ConfigText
				.getString( "Api.TestData.TestDataFileName" ) );
	}

	/**
	 * Test feature of aggregation expression
	 */
	public void test_RunningAggregationExpression( ) throws Exception
	{
		// Test a SQL with duplicate column name (quite common with join data
		// sets)
		String testSQL = "select COUNTRY, AMOUNT from " + getTestTableName( );
		( (OdaDataSetDesign) this.dataSet ).setQueryText( testSQL );

		IBaseExpression[] expressions = new IBaseExpression[]{
				new ScriptExpression( "row.COUNTRY", 0 ),
				new ScriptExpression( "Total.runningSum(row.AMOUNT)", 2 )};

		// define a query design
		QueryDefinition queryDefn = newReportQuery( );
		queryDefn.addResultSetExpression( expressions[0].toString(), expressions[0] );
		queryDefn.addResultSetExpression( expressions[1].toString(), expressions[1] );

		IPreparedQuery preparedQuery = dataEngine.prepare( queryDefn );
		IQueryResults queryResults = preparedQuery.execute( null );
		IResultIterator resultIt = queryResults.getResultIterator( );
		assertTrue( resultIt.next( ) );

		resultIt.getValue( expressions[0].toString( ));
		resultIt.getValue( expressions[1].toString( )  );
		
		IPreparedQuery preparedQuery1 = dataEngine.prepare( queryDefn );
		IQueryResults queryResults1 = preparedQuery.execute( jsScope );
		IResultIterator resultIt1 = queryResults.getResultIterator( );
		outputQueryResult( resultIt1, new String[]{"COL_COUNTRY", "COL_AMOUNT"} );
		checkOutputFile( );

	}

	/**
	 * Test feature of aggregation expression
	 */
	public void test_AggregationExpression( ) throws Exception
	{
		// Test a SQL with duplicate column name (quite common with join data
		// sets)
		String testSQL = "select COUNTRY, AMOUNT from " + getTestTableName( );
		( (OdaDataSetDesign) this.dataSet ).setQueryText( testSQL );

		IBaseExpression[] expressions = new IBaseExpression[]{
				new ScriptExpression( "row.COUNTRY", 0 ),
				new ScriptExpression( "Total.sum(row.AMOUNT)", 2 )};

		// define a query design
		QueryDefinition queryDefn = newReportQuery( );
		queryDefn.addResultSetExpression( expressions[0].toString(), expressions[0] );
		queryDefn.addResultSetExpression( expressions[1].toString(), expressions[1] );

		IPreparedQuery preparedQuery = dataEngine.prepare( queryDefn );
		IQueryResults queryResults = preparedQuery.execute( null );
		IResultIterator resultIt = queryResults.getResultIterator( );
		assertTrue( resultIt.next( ) );

		resultIt.getValue( expressions[0].toString( ) );
		resultIt.getValue( expressions[1].toString( ) );

	}

	/**
	 * Test feature of two level group.
	 */
	public void test_Group( ) throws Exception
	{
		GroupDefinition[] groupDefn = new GroupDefinition[]{
				new GroupDefinition( ), new GroupDefinition( )};
		groupDefn[0].setKeyExpression( "row.COUNTRY" );
		groupDefn[1].setKeyExpression( "row.CITY" );

		IBaseExpression[] expressions = new IBaseExpression[]{
				new ScriptExpression( "row.COUNTRY", 0 ),
				new ScriptExpression( "row.CITY", 0 ),
				new ScriptExpression( "row.SALE_DATE", 6 ),
				new ScriptExpression( "row.AMOUNT", 0 )};
		createAndRunQuery( expressions, groupDefn, null, null );
		checkOutputFile( );
	}

	/**
	 * A ReportQueryDefn with a wrong column key for filter
	 */
	public void test_WrongColumnKey( ) throws Exception
	{
		GroupDefinition[] groupDefn = new GroupDefinition[]{
				new GroupDefinition( ), new GroupDefinition( )};
		groupDefn[0].setKeyExpression( "row.COUNTRY" );
		groupDefn[1].setKeyExpression( "row.CITY" );

		SortDefinition[] sortDefn = new SortDefinition[]{new SortDefinition( )};
		sortDefn[0].setColumn( "SALE_DATE" );
		sortDefn[0].setSortDirection( ISortDefinition.SORT_DESC );

		IBaseExpression[] expressions = new IBaseExpression[]{
				new ScriptExpression( "row.COUNTRY", 0 ),
				new ScriptExpression( "row.CITY", 0 ),
				new ScriptExpression( "row.SALE_DATE", 0 ),
				new ScriptExpression( "row.AMOUNT", 0 )};

		// Wrong Column Name
		FilterDefinition[] filters = new FilterDefinition[]{new FilterDefinition(
				new ScriptExpression( "row.AMONT > 100" ) )};

		try
		{
			createAndRunQuery( expressions, groupDefn, sortDefn, filters );
			fail( "Should throw DteException!" );
		}
		catch ( DataException e )
		{
			// TODO: verify that e has correct error code
		}
	}

	/**
	 * Test feature of incorrect filter expression, throw exception
	 */
	public void test_incorrectExpression( ) throws Exception
	{
		GroupDefinition[] groupDefn = new GroupDefinition[]{
				new GroupDefinition( ), new GroupDefinition( )};
		groupDefn[0].setKeyExpression( "row.COUNTRY" );
		groupDefn[1].setKeyExpression( "row.CITY" );

		SortDefinition[] sortDefn = new SortDefinition[]{new SortDefinition( )};

		// wrong sort column
		sortDefn[0].setColumn( "WRONG_SALE_DATE" );
		sortDefn[0].setSortDirection( ISortDefinition.SORT_DESC );

		IBaseExpression[] expressions = new IBaseExpression[]{
				new ScriptExpression( "row.COUNTRY", 0 ),
				new ScriptExpression( "row.CITY", 0 ),
				new ScriptExpression( "row.SALE_DATE", 0 ),
				new ScriptExpression( "row.AMOUNT", 0 )};

		// wrong operator
		// FilterDefinition[] filters = new FilterDefinition[]{new
		// FilterDefinition(
		// new ScriptExpression( "row.AMONT && 100" ) )};

		try
		{
			createAndRunQuery( expressions, groupDefn, sortDefn, null );
			fail( "Should throw DteException!" );
		}
		catch ( DataException e )
		{
			// TODO: verify that e has correct error code
		}
	}

	/**
	 * Test feature of group, sort, ConditionalExpression
	 */
	public void test_ConditionalExpression( ) throws Exception
	{
		GroupDefinition[] groupDefn = new GroupDefinition[]{
				new GroupDefinition( ), new GroupDefinition( )};
		groupDefn[0].setKeyExpression( "row.COUNTRY" );
		groupDefn[1].setKeyExpression( "row.CITY" );

		SortDefinition[] sortDefn = new SortDefinition[]{new SortDefinition( )};
		sortDefn[0].setColumn( "SALE_DATE" );
		sortDefn[0].setSortDirection( ISortDefinition.SORT_DESC );

		IBaseExpression[] expressions = new IBaseExpression[]{
				new ScriptExpression( "row.COUNTRY", 0 ),
				new ScriptExpression( "row.CITY", 0 ),
				new ScriptExpression( "row.SALE_DATE", 0 ),
				new ScriptExpression( "row.AMOUNT", 0 ),
				new ConditionalExpression( "row.AMOUNT",
						ConditionalExpression.OP_GT, "5" ),
				new ConditionalExpression( "row.CITY",
						ConditionalExpression.OP_EQ, "'Beijing'" ),
				new ConditionalExpression( "row.CITY",
						ConditionalExpression.OP_NE, "'Beijing'" ),
				new ConditionalExpression( "row.SALE_DATE",
						ConditionalExpression.OP_GE, "'01/01/2004'" ),
				new ConditionalExpression( "row.AMOUNT",
						ConditionalExpression.OP_BETWEEN, "5", "100" )};

		createAndRunQuery( expressions, groupDefn, sortDefn, null );
		checkOutputFile( );
	}

	/**
	 * Test feature of group, sort, filter
	 */
	public void test_Combination( ) throws Exception
	{
		GroupDefinition[] groupDefn = new GroupDefinition[]{
				new GroupDefinition( ), new GroupDefinition( )};
		groupDefn[0].setKeyExpression( "row.COUNTRY" );
		groupDefn[1].setKeyExpression( "row.CITY" );

		SortDefinition[] sortDefn = new SortDefinition[]{new SortDefinition( )};
		sortDefn[0].setColumn( "SALE_DATE" );
		sortDefn[0].setSortDirection( ISortDefinition.SORT_DESC );

		IBaseExpression[] expressions = new IBaseExpression[]{
				new ScriptExpression( "row.COUNTRY", 0 ),
				new ScriptExpression( "row.CITY", 0 ),
				new ScriptExpression( "row.SALE_DATE", 0 ),
				new ScriptExpression( "row.AMOUNT", 0 )};
		FilterDefinition[] filters = new FilterDefinition[]{new FilterDefinition(
				new ConditionalExpression( "row.AMOUNT",
						ConditionalExpression.OP_GT, "400" ) )};

		createAndRunQuery( expressions, groupDefn, sortDefn, filters );
		checkOutputFile( );
	}

	/**
	 * Create query definition from passed parameteres and then run it.
	 * 
	 * @param expressions
	 * @param groupDefn
	 * @param sortDefn
	 * @param filters
	 * @throws Exception
	 */
	private void createAndRunQuery( IBaseExpression[] expressions,
			GroupDefinition[] groupDefn, SortDefinition[] sortDefn,
			FilterDefinition[] filters ) throws Exception
	{
		QueryDefinition queryDefn = createQueryDefn( expressions, groupDefn,
				sortDefn, filters );

		IPreparedQuery preparedQuery = dataEngine.prepare( queryDefn );
		IQueryResults queryResults = preparedQuery.execute( jsScope );
		IResultIterator resultIt = queryResults.getResultIterator( );
		outputQueryResult( resultIt, new String[]{"COL_COUNTRY", "COL_CITY",
				"COL_SALE_DATE", "COL_AMOUNT"} );
	}

	/**
	 * Create query definition from passed parameters
	 * 
	 * @param expressions
	 *            row expression
	 * @param groupDefn
	 * @param sortDefn
	 * @param filters
	 * @return QueryDefinition
	 */
	private QueryDefinition createQueryDefn( IBaseExpression[] expressions,
			GroupDefinition[] groupDefn, SortDefinition[] sortDefn,
			FilterDefinition[] filters )
	{
		// define a query design
		QueryDefinition queryDefn = newReportQuery( );

		if ( groupDefn != null )
			for ( int i = 0; i < groupDefn.length; i++ )
				queryDefn.addGroup( groupDefn[i] );
		if ( sortDefn != null )
			for ( int i = 0; i < sortDefn.length; i++ )
				queryDefn.addSort( sortDefn[i] );
		if ( expressions != null )
			for ( int i = 0; i < expressions.length; i++ )
				queryDefn.addResultSetExpression( expressions[i].toString( ),
						expressions[i] );
		if ( filters != null )
			for ( int i = 0; i < filters.length; i++ )
				queryDefn.addFilter( filters[i] );

		return queryDefn;
	}

}