
package demos.removeCondition;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.nodes.TCTE;
import gudusoft.gsqlparser.nodes.TExpression;
import gudusoft.gsqlparser.nodes.TResultColumn;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.nodes.TTableList;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;

import java.io.File;
import java.util.HashMap;
import java.util.Map;

public class removeCondition
{

	private String result;
	
	public removeCondition( File sqlFile, EDbVendor vendor,
			Map<String, String> conditionMap )
	{
		TGSqlParser sqlparser = new TGSqlParser( vendor );
		sqlparser.setSqlfilename( sqlFile.getAbsolutePath( ) );
		remove( sqlparser, conditionMap );
	}

	public removeCondition( String sql, EDbVendor vendor,
			Map<String, String> conditionMap )
	{
		TGSqlParser sqlparser = new TGSqlParser( vendor );
		sqlparser.setSqltext( sql );
		remove( sqlparser, conditionMap );
	}

	void remove( TGSqlParser sqlparser, Map<String, String> conditionMap )
	{
		int i = sqlparser.parse( );
		if ( i == 0 )
		{
			TCustomSqlStatement stat = sqlparser.sqlstatements.get( 0 );
			result = stat.toString( );
			getParserString( stat, conditionMap );
		}
		else
			System.err.println( sqlparser.getErrormessage( ) );
	}

	String remove( TCustomSqlStatement stat, Map<String, String> conditionMap )
	{
		String whereCondition = null;
		if ( stat.getResultColumnList( ) != null )
		{
			for ( int j = 0; j < stat.getResultColumnList( ).size( ); j++ )
			{
				TResultColumn column = stat.getResultColumnList( )
						.getResultColumn( j );
				if ( column.getExpr( ) != null
						&& column.getExpr( ).getSubQuery( ) instanceof TCustomSqlStatement )
				{
					TCustomSqlStatement query = (TCustomSqlStatement) column.getExpr( )
							.getSubQuery( );
					if ( getParserString( query, conditionMap ) )
						return null;
				}
			}
		}
		if ( stat.getCteList( ) != null )
		{
			for ( int i = 0; i < stat.getCteList( ).size( ); i++ )
			{
				TCTE cte = stat.getCteList( ).getCTE( i );
				if ( cte.getSubquery( ) != null )
				{
					if ( getParserString( cte.getSubquery( ), conditionMap ) )
						return null;
				}
				if ( cte.getInsertStmt( ) != null )
				{
					if ( getParserString( cte.getInsertStmt( ), conditionMap ) )
						return null;
				}
				if ( cte.getUpdateStmt( ) != null )
				{
					if ( getParserString( cte.getUpdateStmt( ), conditionMap ) )
						return null;
				}
				if ( cte.getPreparableStmt( ) != null )
				{
					if ( getParserString( cte.getPreparableStmt( ),
							conditionMap ) )
						return null;
				}
				if ( cte.getDeleteStmt( ) != null )
				{
					if ( getParserString( cte.getDeleteStmt( ), conditionMap ) )
						return null;
				}
			}
		}
		if ( stat.getStatements( ) != null && stat.getStatements( ).size( ) > 0 )
		{
			for ( int i = 0; i < stat.getStatements( ).size( ); i++ )
			{
				if ( getParserString( stat.getStatements( ).get( i ),
						conditionMap ) )
					return null;
			}
		}
		if ( stat.getReturningClause( ) != null )
		{
			if ( stat.getReturningClause( ).getColumnValueList( ) != null )
			{
				for ( int i = 0; i < stat.getReturningClause( )
						.getColumnValueList( )
						.size( ); i++ )
				{
					if ( stat.getReturningClause( )
							.getColumnValueList( )
							.getExpression( i )
							.getSubQuery( ) != null )
					{
						if ( getParserString( stat.getReturningClause( )
								.getColumnValueList( )
								.getExpression( i )
								.getSubQuery( ), conditionMap ) )
							return null;
					}
				}
			}
			if ( stat.getReturningClause( ).getVariableList( ) != null )
			{
				for ( int i = 0; i < stat.getReturningClause( )
						.getVariableList( )
						.size( ); i++ )
				{
					if ( stat.getReturningClause( )
							.getVariableList( )
							.getExpression( i )
							.getSubQuery( ) != null )
					{
						if ( getParserString( stat.getReturningClause( )
								.getVariableList( )
								.getExpression( i )
								.getSubQuery( ), conditionMap ) )
							return null;
					}
				}
			}
		}
		if ( stat instanceof TSelectSqlStatement )
		{
			TTableList list = ( (TSelectSqlStatement) stat ).tables;
			for ( int i = 0; i < list.size( ); i++ )
			{
				TTable table = list.getTable( i );
				if ( table.getSubquery( ) != null )
				{
					if ( getParserString( table.getSubquery( ), conditionMap ) )
						return null;
				}
				if ( table.getFuncCall( ) != null )
				{
					ExpressionChecker w = new ExpressionChecker( this );
					w.checkFunctionCall( table.getFuncCall( ), conditionMap );
				}
			}
		}

		
		if ( stat.getWhereClause( ) != null
				&& stat.getWhereClause( ).getCondition( ) != null )
		{
			String oldString = stat.toString( );
			StringBuffer buffer = new StringBuffer( );
			TExpression whereExpression = stat.getWhereClause( ).getCondition( );
			ExpressionChecker w = new ExpressionChecker( this );
			w.checkExpression( stat.getWhereClause( ).getCondition( ),
					conditionMap );
			String newString = stat.toString( );
			if(oldString.equals( newString ))
				return oldString;
			if ( whereExpression != null )
			{
				if ( whereExpression.toString( ) != null )
				{
					whereCondition = whereExpression.toString( ).trim( );
					whereExpression.remove( );

					String[] splits = stat.toString( )
							.split( stat.getWhereClause( ).toString( ) );
					if ( splits.length == 2 )
					{
						buffer.append( splits[0] )
								.append( stat.getWhereClause( ).toString( ) )
								.append( whereCondition )
								.append( splits[1] );
					}
					else
					{
						buffer.append( stat.toString( ) );
					}
				}
				else
				{
					String[] splits = stat.toString( )
							.split( stat.getWhereClause( ).toString( ) );
					if ( splits.length == 2 )
					{
						buffer.append( splits[0].trim( ) + splits[1] );
					}
					else
					{
						buffer.append( stat.toString( ) );
					}
				}
			}
			return buffer.toString( );
		}
		else return stat.toString( );
	
	}

	private boolean getParserString( TCustomSqlStatement query,
			Map<String, String> conditionMap )
	{
		String oldString = query.toString( );
		String newString = remove( query, conditionMap );
		if(newString == null)
			return true;
		if ( newString!=null && !oldString.equals( newString ) )
		{
			result = result.replace( oldString, newString );
			TGSqlParser parser = query.getGsqlparser( );
			parser.setSqltext( result );
			remove( parser, conditionMap );
			return true;
		}
		return false;
	}

	public String getRemoveResult( )
	{
		return result.toString( );
	}

	public static void main( String[] args )
	{
		// String sql = "SELECT SUM (d.amt) \r\n"
		// + "FROM   summit.cntrb_detail d \r\n"
		// + "WHERE"
		// + " d.id = summit.mstr.id \r\n"
		// + "AND d.system_gift_type IN ( 'OG', 'PLP', 'PGP' ) \r\n"
		// // + " d.fund_coll_attrb IN ( '$Institute$' ) \r\n"
		// + "AND d.fund_coll_attrb IN ( '$Institute$' ) \r\n"
		// + "AND d.fund_acct IN ( '$Fund$' ) \r\n"
		// + "AND d.cntrb_date >= '$From_Date$' \r\n"
		// + "AND d.cntrb_date <= '$Thru_Date$' \r\n"
		// + "GROUP  BY d.id; ";
		//File sql = new File( "C:\\test.sql" );

        String sql ="SELECT (SELECT sum (d.amt)\n" +
                "        FROM   SUMMIT.CNTRB_DETAIL d\n" +
                "        WHERE  d.id = summit.mstr.id\n" +
                "               AND d.SYSTEM_GIFT_TYPE IN ( 'OG', 'PLP', 'PGP' )\n" +
                "               AND d.FUND_COLL_ATTRB IN ( '$Institute$' )\n" +
                "               AND d.FUND_ACCT IN ( '$Fund$' )\n" +
                "               AND d.CNTRB_DATE >= '$From_Date$'\n" +
                "               AND d.CNTRB_DATE <= '$Thru_Date$'\n" +
                "        GROUP  BY d.id)               AS \"TOTAMT\",\n" +
                "       (SELECT d.mngr1\n" +
                "        FROM   summit.PRSPCT d\n" +
                "        WHERE  d.id = summit.mstr.id) AS \"BCONT1DS\",\n" +
                "       (SELECT sum (d.amt)\n" +
                "        FROM   SUMMIT.CNTRB_DETAIL d\n" +
                "        WHERE  d.id = summit.mstr.id\n" +
                "               AND d.SYSTEM_GIFT_TYPE IN ( 'PLA' )\n" +
                "               AND d.FUND_COLL_ATTRB IN ( '$Institute$' )\n" +
                "               AND d.FUND_ACCT IN ( '$Fund$' )\n" +
                "               AND d.CNTRB_DATE >= '$From_Date$'\n" +
                "               AND d.CNTRB_DATE <= '$Thru_Date$'\n" +
                "        GROUP  BY d.id)               AS \"PLTAMT\",\n" +
                "       (SELECT sum (d.amt)\n" +
                "        FROM   SUMMIT.CNTRB_DETAIL d\n" +
                "        WHERE  d.id = summit.mstr.id\n" +
                "               AND d.SYSTEM_GIFT_TYPE IN ( 'PGA' )\n" +
                "               AND d.FUND_COLL_ATTRB IN ( '$Institute$' )\n" +
                "               AND d.FUND_ACCT IN ( '$Fund$' )\n" +
                "               AND d.CNTRB_DATE >= '$From_Date$'\n" +
                "               AND d.CNTRB_DATE <= '$Thru_Date$'\n" +
                "        GROUP  BY d.id)               AS \"PGTEST\",\n" +
                "       SUMMIT.MSTR.ID                 AS \"SMS.ID\",\n" +
                "       SUMMIT.MSTR.FIRST_NAME         AS \"SMS.FIRST_NAME\"\n" +
                "FROM   SUMMIT.MSTR\n" +
                "WHERE  SUMMIT.MSTR.ID IN ( '112233445' ) ";

		Map<String, String> conditionMap = new HashMap<String, String>( );
		conditionMap.put( "Institute", "ShanXi University" );
		conditionMap.put( "Fund", "Eclipse.org" );
		removeCondition remove = new removeCondition( sql,
				EDbVendor.dbvmssql,
				conditionMap );
		System.out.println( remove.getRemoveResult( ) );
	}
}
