/*******************************************************************************
 * 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 org.eclipse.birt.data.engine.i18n.ResourceConstants;

import testutil.APITestCase;
import testutil.ConfigText;

/**
 * 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 );

		String[] bindingNameRow = new String[2];
		bindingNameRow[0] = "ROW_1";
		bindingNameRow[1] = "ROW_2";

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

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

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

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

		QueryDefinition qd = this.createQuery( null, null, null, null, null,
				null, null, null, null, bindingNameRow, bindingExprRow );
		String[] columnStr = new String[]{"Country", "RunningSum"};
		String outputStr = getOutputStrForGroupTest( 17, qd, 0, bindingNameRow,
				columnStr );
		testPrint( outputStr );
		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( "dataSetRow.COUNTRY", 0 ),
				new ScriptExpression( "Total.sum(dataSetRow.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
	{
		String[] bindingNameGroup = new String[2];
		bindingNameGroup[0] = "GROUP_GROUP1";
		bindingNameGroup[1] = "GROUP_GROUP2";
		IBaseExpression[] bindingExprGroup = new IBaseExpression[2];
		bindingExprGroup[0] = new ScriptExpression( "dataSetRow.COUNTRY" );
		bindingExprGroup[1] = new ScriptExpression( "dataSetRow.CITY" );

		GroupDefinition[] groupDefn = new GroupDefinition[]{
				new GroupDefinition( "group1" ), new GroupDefinition( "group2" )};
		groupDefn[0].setKeyExpression( "row.GROUP_GROUP1" );
		groupDefn[1].setKeyExpression( "row.GROUP_GROUP2" );

		String[] bindingNameRow = new String[4];
		bindingNameRow[0] = "ROW_1";
		bindingNameRow[1] = "ROW_2";
		bindingNameRow[2] = "ROW_3";
		bindingNameRow[3] = "ROW_4";

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

		String[] columnStr = new String[]{"Country", "City", "Amount",
				"Sale Date"};
		
		QueryDefinition qd = this.createQuery( bindingNameGroup,
				bindingExprGroup, groupDefn, null, null, null, null, null,
				null, bindingNameRow, bindingExprRow );
		String outputStr = getOutputStrForGroupTest( 17, qd, groupDefn.length,
				bindingNameRow, columnStr );
		testPrint( outputStr );
		checkOutputFile( );
	}

	/**
	 * A ReportQueryDefn with a wrong column key for filter
	 */
	public void test_WrongColumnKey( ) throws Exception
	{
		String[] bindingNameRow = new String[4];
		bindingNameRow[0] = "ROW_1";
		bindingNameRow[1] = "ROW_2";
		bindingNameRow[2] = "ROW_3";
		bindingNameRow[3] = "ROW_4";

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

		// Wrong Column Name
		String[] bindingNameFilter = new String[1];
		bindingNameFilter[0] = "FILTER_COL0";
		IBaseExpression[] bindingExprFilter = new IBaseExpression[1];
		bindingExprFilter[0] = new ScriptExpression( "dataSetRow.AT" );

		FilterDefinition[] filterDefn = new FilterDefinition[]{new FilterDefinition(
				new ConditionalExpression( "dataSetRow.Error",
						ConditionalExpression.OP_LE, "400" ) )};

		// output the results
		QueryDefinition qd = this.createQuery( null, null, null, null, null,
				null, bindingNameFilter, bindingExprFilter, filterDefn,
				bindingNameRow, bindingExprRow );

		try
		{
			executeQuery( qd );
			fail( "Should throw DteException!" );
		}
		catch ( DataException e )
		{
			String errorCode = e.getErrorCode( );
			assertEquals( ResourceConstants.INVALID_DEFINITION_IN_FILTER,
					errorCode );
		}
	}

	/**
	 * Test feature of incorrect group key expression, throw exception
	 */
	public void test_incorrectExpression( ) throws Exception
	{
		String[] bindingNameGroup = new String[1];
		bindingNameGroup[0] = "GROUP_GROUP1";
		IBaseExpression[] bindingExprGroup = new IBaseExpression[1];
		bindingExprGroup[0] = new ScriptExpression( "dataSetRow.ErrorColumn" );

		GroupDefinition[] groupDefn = new GroupDefinition[]{new GroupDefinition(
				"group1" )};
		groupDefn[0].setKeyExpression( "row.GROUP_GROUP1" );

		String[] bindingNameRow = new String[4];
		bindingNameRow[0] = "ROW_1";
		bindingNameRow[1] = "ROW_2";
		bindingNameRow[2] = "ROW_3";
		bindingNameRow[3] = "ROW_4";
		IBaseExpression[] bindingExprRow = new IBaseExpression[4];
		bindingExprRow[0] = new ScriptExpression( "dataSetRow.COUNTRY", 0 );
		bindingExprRow[1] = new ScriptExpression( "dataSetRow.CITY", 0 );
		bindingExprRow[2] = new ScriptExpression( "dataSetRow.AMOUNT", 0 );
		bindingExprRow[3] = new ScriptExpression( "dataSetRow.SALE_DATE", 6 );

		QueryDefinition qd = this.createQuery( bindingNameGroup,
				bindingExprGroup, groupDefn, null, null, null, null, null,
				null, bindingNameRow, bindingExprRow );

		try
		{
			executeQuery( qd );
			fail( "Should throw DteException!" );
		}
		catch ( DataException e )
		{
			String errorCode = e.getErrorCode( );
			assertEquals( ResourceConstants.INAVLID_FIELD_NAME, errorCode );
		}
	}

	/**
	 * Test feature of group, sort, ConditionalExpression
	 */
	public void test_ConditionalExpression( ) throws Exception
	{
		String[] bindingNameGroup = new String[2];
		bindingNameGroup[0] = "GROUP_GROUP1";
		bindingNameGroup[1] = "GROUP_GROUP2";
		IBaseExpression[] bindingExprGroup = new IBaseExpression[2];
		bindingExprGroup[0] = new ScriptExpression( "dataSetRow.COUNTRY" );
		bindingExprGroup[1] = new ScriptExpression( "dataSetRow.CITY" );

		GroupDefinition[] groupDefn = new GroupDefinition[]{
				new GroupDefinition( "group1" ), new GroupDefinition( "group2" )};
		groupDefn[0].setKeyExpression( "row.GROUP_GROUP1" );
		groupDefn[1].setKeyExpression( "row.GROUP_GROUP2" );

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

		String[] bindingNameRow = new String[7];
		bindingNameRow[0] = "ROW_1";
		bindingNameRow[1] = "ROW_2";
		bindingNameRow[2] = "ROW_3";
		bindingNameRow[3] = "ROW_4";
		bindingNameRow[4] = "ROW_5";
		bindingNameRow[5] = "ROW_6";
		bindingNameRow[6] = "ROW_6";
		IBaseExpression[] bindingExprRow = new IBaseExpression[]{
				new ScriptExpression( "dataSetRow.COUNTRY", 0 ),
				new ScriptExpression( "dataSetRow.CITY", 0 ),
				new ConditionalExpression( "dataSetRow.AMOUNT",
						ConditionalExpression.OP_GT, "500" ),
				new ConditionalExpression( "dataSetRow.CITY",
						ConditionalExpression.OP_EQ, "'Beijing'" ),
				new ConditionalExpression( "dataSetRow.CITY",
						ConditionalExpression.OP_NE, "'Beijing'" ),
				new ConditionalExpression( "dataSetRow.SALE_DATE",
						ConditionalExpression.OP_GE, "'01/01/2004'" ),
				new ConditionalExpression( "dataSetRow.AMOUNT",
						ConditionalExpression.OP_BETWEEN, "5", "100" )};
		String[] columnStr = new String[]{"Country", "City", "Con1", "Con2",
				"Con3", "Con4", "Con5"};

		QueryDefinition qd = this.createQuery( bindingNameGroup,
				bindingExprGroup, groupDefn, null, null, sortDefn, null, null,
				null, bindingNameRow, bindingExprRow );
		String outputStr = getOutputStrForGroupTest( 17, qd, groupDefn.length,
				bindingNameRow, columnStr );
		testPrint( outputStr );
		checkOutputFile( );
	}

	/**
	 * Test feature of group, sort, filter
	 */
	public void test_Combination( ) throws Exception
	{ // SetGroup
		String[] bindingNameGroup = new String[1];
		bindingNameGroup[0] = "GROUP_GROUP1";
		IBaseExpression[] bindingExprGroup = new IBaseExpression[1];
		bindingExprGroup[0] = new ScriptExpression( "dataSetRow.COUNTRY" );

		GroupDefinition[] groupDefn = new GroupDefinition[]{new GroupDefinition(
				"group1" )};
		groupDefn[0].setKeyExpression( "row.GROUP_GROUP1" );
		// setColumnBinding
		String[] bindingNameRow = new String[4];
		bindingNameRow[0] = "ROW_1";
		bindingNameRow[1] = "ROW_2";
		bindingNameRow[2] = "ROW_3";
		bindingNameRow[3] = "ROW_4";
		IBaseExpression[] bindingExprRow = new IBaseExpression[]{
				new ScriptExpression( "dataSetRow.COUNTRY", 0 ),
				new ScriptExpression( "dataSetRow.CITY", 0 ),
				new ScriptExpression( "dataSetRow.AMOUNT", 0 ),
				new ScriptExpression( "dataSetRow.SALE_DATE", 0 )};

		// set filter
		String[] bindingNameFilter = new String[1];
		bindingNameFilter[0] = "FILTER_COL0";
		IBaseExpression[] bindingExprFilter = new IBaseExpression[1];
		bindingExprFilter[0] = new ScriptExpression( "dataSetRow.AMOUNT" );
		FilterDefinition[] filterDefn = new FilterDefinition[]{new FilterDefinition(
				new ConditionalExpression( "row.FILTER_COL0",
						ConditionalExpression.OP_LE, "400" ) )};
		// set Sorting
		SortDefinition[] sortDefn = new SortDefinition[]{new SortDefinition( )};
		sortDefn[0].setExpression( "dataSetRow.SALE_DATE" );
		sortDefn[0].setSortDirection( ISortDefinition.SORT_DESC );

		String[] columnStr = new String[]{"Country", "City", "Amount",
				"Sale Date"};
		// output the results
		QueryDefinition qd = this.createQuery( bindingNameGroup,
				bindingExprGroup, groupDefn, null, null, sortDefn,
				bindingNameFilter, bindingExprFilter, filterDefn,
				bindingNameRow, bindingExprRow );
		String outputStr = getOutputStrForGroupTest( 17, qd, groupDefn.length,
				bindingNameRow, columnStr );
		testPrint( outputStr );
		checkOutputFile( );
	}
}