
package demos.columnImpact;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.ETableSource;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.nodes.IExpressionVisitor;
import gudusoft.gsqlparser.nodes.TCTE;
import gudusoft.gsqlparser.nodes.TCaseExpression;
import gudusoft.gsqlparser.nodes.TExpression;
import gudusoft.gsqlparser.nodes.TFunctionCall;
import gudusoft.gsqlparser.nodes.TGroupByItem;
import gudusoft.gsqlparser.nodes.TJoin;
import gudusoft.gsqlparser.nodes.TJoinItem;
import gudusoft.gsqlparser.nodes.TOrderBy;
import gudusoft.gsqlparser.nodes.TOrderByItem;
import gudusoft.gsqlparser.nodes.TOrderByItemList;
import gudusoft.gsqlparser.nodes.TParseTreeNode;
import gudusoft.gsqlparser.nodes.TParseTreeNodeList;
import gudusoft.gsqlparser.nodes.TResultColumn;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.nodes.TTableList;
import gudusoft.gsqlparser.nodes.TWhenClauseItem;
import gudusoft.gsqlparser.nodes.TWhenClauseItemList;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;
import gudusoft.gsqlparser.stmt.TUpdateSqlStatement;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintStream;
import java.io.StringWriter;
import java.io.Writer;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class ColumnImpact
{

	enum ClauseType {
		connectby, groupby, join, orderby, select, startwith, undefine, where
	}

	class columnsInExpr implements IExpressionVisitor
	{

		private List<TColumn> columns;
		private TExpression expr;
		private ColumnImpact impact;
		private int level;
		private TCustomSqlStatement stmt;

		public columnsInExpr( ColumnImpact impact, TExpression expr,
				List<TColumn> columns, TCustomSqlStatement stmt, int level )
		{
			this.stmt = stmt;
			this.impact = impact;
			this.expr = expr;
			this.columns = columns;
			this.level = level;
		}

		private void addColumnToList( TParseTreeNodeList list,
				TCustomSqlStatement stmt )
		{
			if ( list != null )
			{
				for ( int i = 0; i < list.size( ); i++ )
				{
					List<TExpression> exprList = new ArrayList<TExpression>( );
					Object element = list.getElement( i );

					if ( element instanceof TGroupByItem )
					{
						exprList.add( ( (TGroupByItem) element ).getExpr( ) );
					}
					else if ( element instanceof TExpression )
					{
						exprList.add( (TExpression) element );
					}
					else if ( element instanceof TWhenClauseItem )
					{
						exprList.add( ( (TWhenClauseItem) element ).getComparison_expr( ) );
						exprList.add( ( (TWhenClauseItem) element ).getReturn_expr( ) );
					}

					for ( TExpression expr : exprList )
					{
						expr.inOrderTraverse( this );
					}
				}
			}
		}

		public boolean exprVisit( TParseTreeNode pNode, boolean isLeafNode )
		{
			TExpression lcexpr = (TExpression) pNode;
			if ( lcexpr.getExpressionType( ) == TExpression.simpleObjectname )
			{
				columns.add( impact.attrToColumn( lcexpr, stmt ) );
			}
			else if ( lcexpr.getExpressionType( ) == TExpression.funcationCallOperator )
			{
				TFunctionCall func = (TFunctionCall) lcexpr.getFunctionCall( );
				if ( func.getArgs( ) != null )
				{
					for ( int k = 0; k < func.getArgs( ).size( ); k++ )
					{
						TExpression expr = func.getArgs( ).getExpression( k );
						if ( expr.toString( ).trim( ).equals( "*" ) )
							continue;
						expr.inOrderTraverse( this );
					}
				}
				if ( func.getAnalyticFunction( ) != null )
				{
					TParseTreeNodeList list = func.getAnalyticFunction( )
							.getPartitionBy_ExprList( );
					addColumnToList( list, stmt );

					if ( func.getAnalyticFunction( ).getOrderBy( ) != null )
					{
						list = func.getAnalyticFunction( )
								.getOrderBy( )
								.getItems( );
						addColumnToList( list, stmt );
					}
				}

			}
			else if ( lcexpr.getExpressionType( ) == TExpression.subqueryExprOperator )
			{

				for ( int i = 0; i < ( (TSelectSqlStatement) lcexpr.getSubQuery( ) ).getResultColumnList( )
						.size( ); i++ )
				{
					impact.findColumnInSubQuery( (TSelectSqlStatement) lcexpr.getSubQuery( ),
							( (TSelectSqlStatement) lcexpr.getSubQuery( ) ).getResultColumnList( )
									.getResultColumn( i )
									.toString( ),
							level );
				}

			}
			else if ( lcexpr.getExpressionType( ) == TExpression.caseExprOperator )
			{
				TCaseExpression expr = lcexpr.getCaseExpression( );
				TExpression conditionExpr = expr.getInput_expr( );
				if ( conditionExpr != null )
				{
					conditionExpr.inOrderTraverse( this );
				}
				TExpression defaultExpr = expr.getElse_expr( );
				if ( defaultExpr != null )
				{
					defaultExpr.inOrderTraverse( this );
				}
				TWhenClauseItemList list = expr.getWhenClauseItemList( );
				addColumnToList( list, stmt );
			}
			return true;
		}

		public void searchColumn( )
		{
			this.expr.inOrderTraverse( this );
		}
	}

	public static interface IMetaDatabaseFilter
	{

		boolean metaDatabaseTableColumn( String tableOwner, String tableName,
				String columnName );
	}

	static class MetaDatabaseFilter implements IMetaDatabaseFilter
	{

		public boolean metaDatabaseTableColumn( String tableOwner,
				String tableName, String columnName )
		{
			if ( "other_table".equalsIgnoreCase( tableName ) )
			{
				if ( "c1".equalsIgnoreCase( columnName ) )
					return true;
				else
					return false;
			}
			if ( "some_table".equalsIgnoreCase( tableName ) )
			{
				if ( "c1".equalsIgnoreCase( columnName ) )
					return false;
				else
					return true;
			}
			return true;
		}
	}

	class Table
	{

		public String prefixName;
		public String tableAlias;
		public String tableName;
	}

	class TAlias
	{

		public String alias;
		public String column;
	}

	class TColumn
	{

		public String columnName;
		public String columnPrex;
		public String orignColumn;
		public List<String> tableNames = new ArrayList<String>( );

		public String getFullName( String tableName )
		{
			if ( tableName != null )
			{
				return tableName + "." + columnName;
			}
			else
			{
				return columnName;
			}
		}

		public String getOrigName( )
		{
			if ( columnPrex != null )
			{
				return columnPrex + "." + columnName;
			}
			else
			{
				return columnName;
			}
		}

	}

	class TResultEntry
	{

		public ClauseType clause;

		public String targetColumn;
		public TTable targetTable;

		public TResultEntry( TTable table, String column, ClauseType clause )
		{
			this.targetTable = table;
			this.targetColumn = column;
			this.clause = clause;
		}
	}

	class TSourceColumn
	{

		public List<ClauseType> clauses = new ArrayList<ClauseType>( );
		public String name;
		public String tableName;
		public String tableOwner;
	}

	public static void main( String[] args )
	{
		if ( args.length == 0 )
		{
			System.out.println( "Usage: java ColumnImpact scriptfile [/s]/[/d] [/xml] [/o <output file path>] [/t <database type>]" );
			System.out.println( "/s: Option, display the analysis result simply." );
			System.out.println( "/d: Option, display the analysis result in detail." );
			System.out.println( "/xml: Option, export the analysis results to XML format, it's valid only if /s is specified" );
			System.out.println( "/o: Option, write the output stream to the specified file." );
			System.out.println( "/t: Option, set the database type. Support oracle,mssql,mysql and db2, the default type is oracle" );
			// Console.Read();
			return;
		}

		List<String> argList = Arrays.asList( args );

		boolean simply = argList.indexOf( "/s" ) != -1;

		boolean isXML = simply && argList.indexOf( "/xml" ) != -1;

		String outputFile = null;

		int index = argList.indexOf( "/o" );

		if ( index != -1 && args.length > index + 1 )
		{
			outputFile = args[index + 1];
		}

		FileOutputStream writer = null;
		if ( outputFile != null )
		{
			try
			{
				writer = new FileOutputStream( outputFile );
				System.setOut( new PrintStream( writer ) );
			}
			catch ( FileNotFoundException e )
			{
				e.printStackTrace( );
			}
		}

		EDbVendor vendor = EDbVendor.dbvoracle;

		index = argList.indexOf( "/t" );

		if ( index != -1 && args.length > index + 1 )
		{
			if ( args[index + 1].equalsIgnoreCase( "mssql" ) )
			{
				vendor = EDbVendor.dbvmssql;
			}
			else if ( args[index + 1].equalsIgnoreCase( "db2" ) )
			{
				vendor = EDbVendor.dbvdb2;
			}
			else if ( args[index + 1].equalsIgnoreCase( "mysql" ) )
			{
				vendor = EDbVendor.dbvmysql;
			}
		}

		ColumnImpact impact = new ColumnImpact( new File( args[0] ),
				vendor,
				simply,
				isXML,
				new MetaDatabaseFilter( ) );
		System.out.print( impact.getImpactResult( ) );

		if ( !simply )
		{
			System.out.println( "\r\nYou can add /s directive to display the analysis result in a simple format." );
		}

		try
		{
			if ( outputFile == null )
			{
				System.in.read( );
			}
			else
			{
				if ( writer != null )
				{
					writer.close( );
				}
			}
		}
		catch ( IOException e )
		{
			e.printStackTrace( );
		}

	} // main
	/* store the relations of alias to column */
	private List<TAlias> aliases = new ArrayList<TAlias>( );
	private StringBuffer buffer = new StringBuffer( );
	private HashMap<String, TCustomSqlStatement> cteMap = new HashMap<String, TCustomSqlStatement>( );
	private HashMap<TCustomSqlStatement, ClauseType> currentClauseMap = new HashMap<TCustomSqlStatement, ClauseType>( );
	private String currentSource = null;
	/* store the dependency relations */
	private HashMap<String, List<TResultEntry>> dependMap = new HashMap<String, List<TResultEntry>>( );
	private IMetaDatabaseFilter filter;

	private boolean isXML = false;

	private boolean simply = false;

	public ColumnImpact( File file, EDbVendor dbVendor, Boolean simply,
			Boolean isXML )
	{
		this.simply = simply;
		this.isXML = isXML;
		TGSqlParser sqlparser = new TGSqlParser( dbVendor );
		sqlparser.sqlfilename = file.getAbsolutePath( );
		impactSQL( sqlparser );
	}

	public ColumnImpact( File file, EDbVendor dbVendor, Boolean simply,
			Boolean isXML, IMetaDatabaseFilter filter )
	{
		this.simply = simply;
		this.isXML = isXML;
		this.filter = filter;
		TGSqlParser sqlparser = new TGSqlParser( dbVendor );
		sqlparser.sqlfilename = file.getAbsolutePath( );
		impactSQL( sqlparser );
	}

	public ColumnImpact( String sql, EDbVendor dbVendor, Boolean simply,
			Boolean isXML )
	{
		this.simply = simply;
		this.isXML = isXML;
		TGSqlParser sqlparser = new TGSqlParser( dbVendor );
		sqlparser.sqltext = sql;
		impactSQL( sqlparser );
	}

	public ColumnImpact( String sql, EDbVendor dbVendor, Boolean simply,
			Boolean isXML, IMetaDatabaseFilter filter )
	{
		this.simply = simply;
		this.isXML = isXML;
		this.filter = filter;
		TGSqlParser sqlparser = new TGSqlParser( dbVendor );
		sqlparser.sqltext = sql;
		impactSQL( sqlparser );
	}

	private TColumn attrToColumn( TExpression attr, TCustomSqlStatement stmt )
	{
		TColumn column = new TColumn( );
		column.columnName = attr.getEndToken( ).toString( );

		if ( attr.toString( ).indexOf( "." ) > 0 )
		{
			column.columnPrex = attr.toString( ).substring( 0,
					attr.toString( ).indexOf( "." ) );

			String tableName = column.columnPrex;
			if ( !column.tableNames.contains( tableName ) )
			{
				column.tableNames.add( tableName );
			}
		}
		else
		{
			TTableList tables = stmt.tables;
			for ( int i = 0; i < tables.size( ); i++ )
			{
				TTable lztable = tables.getTable( i );
				Table table = TLzTaleToTable( lztable );
				if ( !column.tableNames.contains( table.tableName ) )
				{
					column.tableNames.add( table.tableName );
				}
			}
		}

		column.orignColumn = column.columnName;

		return column;
	}

	private String buildString( String string, int level )
	{
		StringBuffer buffer = new StringBuffer( );
		for ( int i = 0; i < level; i++ )
		{
			buffer.append( string );
		}
		return buffer.toString( );
	}

	private TCustomSqlStatement containClasuse(
			HashMap<TCustomSqlStatement, ClauseType> currentClauseMap,
			TCustomSqlStatement select )
	{
		if ( currentClauseMap.containsKey( select ) )
			return select;
		else if ( select.getParentStmt( ) instanceof TCustomSqlStatement )
		{
			return containClasuse( currentClauseMap,
					(TCustomSqlStatement) select.getParentStmt( ) );
		}
		else
			return null;
	}

	private List<TColumn> exprToColumn( TExpression expr,
			TCustomSqlStatement stmt, int level )
	{
		List<TColumn> columns = new ArrayList<TColumn>( );

		columnsInExpr c = new columnsInExpr( this, expr, columns, stmt, level );
		c.searchColumn( );

		return columns;
	}

	private boolean findColumnInSubQuery( TSelectSqlStatement select,
			String columnName, int level )
	{
		boolean ret = false;
		if ( select.getSetOperator( ) != TSelectSqlStatement.setOperator_none )
		{
			boolean left = findColumnInSubQuery( select.getLeftStmt( ),
					columnName,
					level + 1 );
			boolean right = findColumnInSubQuery( select.getRightStmt( ),
					columnName,
					level + 1 );
			ret = left && right;
		}
		else
		{
			// check colum name in select list of subquery
			TResultColumn columnField = null;
			if ( !"*".equals( columnName ) )
			{
				for ( int i = 0; i < select.getResultColumnList( ).size( ); i++ )
				{
					TResultColumn field = select.getResultColumnList( )
							.getResultColumn( i );
					if ( field.getAliasClause( ) != null )
					{
						if ( field.getAliasClause( )
								.toString( )
								.equalsIgnoreCase( columnName ) )
						{
							columnField = field;
							break;
						}
					}
					else
					{
						if ( field.getExpr( ).getExpressionType( ) == TExpression.simpleObjectname )
						{
							TColumn column = attrToColumn( field.getExpr( ),
									select );
							if ( columnName.equalsIgnoreCase( column.columnName ) )
							{
								columnField = field;
								break;
							}
						}
					}
				}
			}
			for ( int i = 0; i < select.getResultColumnList( ).size( ); i++ )
			{
				TResultColumn field = select.getResultColumnList( )
						.getResultColumn( i );
				if ( columnField != null && !field.equals( columnField ) )
				{
					continue;
				}
				if ( field.getAliasClause( ) != null )
				{
					ret = "*".equals( columnName )
							|| field.getAliasClause( )
									.toString( )
									.equalsIgnoreCase( columnName );
					if ( ret )
					{
						// let's check where this column come from?
						if ( !simply )
						{
							buffer.append( buildString( " ", level )
									+ "--> "
									+ field.getAliasClause( ).toString( )
									+ "(alias)\r\n" );
						}
						linkFieldToTables( field, select, level + 1 );
					}
				}
				else
				{
					if ( field.getExpr( ).getExpressionType( ) == TExpression.simpleObjectname )
					{
						TColumn column = attrToColumn( field.getExpr( ), select );
						ret = "*".equals( columnName )
								|| columnName.equalsIgnoreCase( column.columnName );
						if ( ret || "*".equals( column.columnName ) )
						{
							findColumnInTables( column,
									select,
									level,
									ret == false ? columnName : null );
							findColumnsFromClauses( select, level + 1 );
						}
					}
				}

				if ( ret && !"*".equals( columnName ) )
					break;
			}
		}
		return ret;
	} // findColumnInSubQuery

	private boolean findColumnInTables( TColumn column, String tableName,
			TCustomSqlStatement select, int level )
	{
		return findColumnInTables( column,
				tableName,
				select,
				level,
				ClauseType.undefine );
	}

	private boolean findColumnInTables( TColumn column, String tableName,
			TCustomSqlStatement select, int level, ClauseType clause )
	{
		boolean ret = false;
		TTableList tables = select.tables;

		if ( tables.size( ) == 1 )
		{
			TTable lzTable = tables.getTable( 0 );
			// buffer.AppendLine(lzTable.AsText);
			if ( ( lzTable.getTableType( ) == ETableSource.objectname )
					&& ( lzTable.getAliasClause( ) == null || lzTable.getAliasClause( )
							.toString( )
							.length( ) == 0 ) )
			{
				ret = true;

				if ( !simply )
				{
					buffer.append( buildString( " ", level )
							+ "--> "
							+ lzTable.getName( )
							+ "."
							+ column.columnName
							+ "\r\n" );
				}
				if ( cteMap.containsKey( lzTable.getName( ) ) )
				{
					if ( !simply )
					{
						buffer.append( buildString( " ", level )
								+ "--> WITH CTE\r\n" );
					}
					ret = findColumnInSubQuery( (TSelectSqlStatement) cteMap.get( lzTable.getName( ) ),
							column.columnName,
							level + 1 );
				}
				else
				{
					if ( currentSource != null
							&& dependMap.containsKey( currentSource ) )
					{
						TCustomSqlStatement stmt = containClasuse( currentClauseMap,
								select );
						if ( stmt != null )
						{
							dependMap.get( currentSource )
									.add( new TResultEntry( lzTable,
											column.columnName,
											(ClauseType) currentClauseMap.get( stmt ) ) );
						}
						else if ( select instanceof TSelectSqlStatement )
						{
							if ( ClauseType.undefine.equals( clause ) )
								dependMap.get( currentSource )
										.add( new TResultEntry( lzTable,
												column.columnName,
												ClauseType.select ) );
							else
								dependMap.get( currentSource )
										.add( new TResultEntry( lzTable,
												column.columnName,
												clause ) );
						}
						else
						{
							dependMap.get( currentSource )
									.add( new TResultEntry( lzTable,
											column.columnName,
											ClauseType.undefine ) );
						}
					}
				}
			}
		}

		if ( ret )
			return ret;

		for ( int x = 0; x < tables.size( ); x++ )
		{
			TTable lztable = tables.getTable( x );
			switch ( lztable.getTableType( ) )
			{
				case objectname :
					Table table = TLzTaleToTable( lztable );
					String alias = table.tableAlias;
					if ( alias != null )
						alias = alias.trim( );
					if ( ( tableName != null )
							&& ( ( tableName.equalsIgnoreCase( alias ) || tableName.equalsIgnoreCase( table.tableName ) ) ) )
					{
						if ( !simply )
						{
							buffer.append( buildString( " ", level )
									+ "--> "
									+ table.tableName
									+ "."
									+ column.columnName
									+ "\r\n" );
						}
						if ( cteMap.containsKey( lztable.getName( ) ) )
						{
							if ( !simply )
							{
								buffer.append( buildString( " ", level )
										+ "--> WITH CTE\r\n" );
							}
							ret = findColumnInSubQuery( (TSelectSqlStatement) cteMap.get( lztable.getName( ) ),
									column.columnName,
									level + 1 );
						}
						else
						{
							if ( dependMap.containsKey( currentSource ) )
							{
								if ( currentClauseMap.containsKey( select ) )
								{
									dependMap.get( currentSource )
											.add( new TResultEntry( lztable,
													column.orignColumn,
													(ClauseType) currentClauseMap.get( select ) ) );
								}
								else if ( select instanceof TSelectSqlStatement )
								{
									dependMap.get( currentSource )
											.add( new TResultEntry( lztable,
													column.orignColumn,
													ClauseType.select ) );
								}
								else
								{
									dependMap.get( currentSource )
											.add( new TResultEntry( lztable,
													column.orignColumn,
													ClauseType.undefine ) );
								}
							}
							ret = true;
						}
					}
					break;
				case subquery :
					for ( int i = 0; i < column.tableNames.size( ); i++ )
					{
						String name = column.tableNames.get( i );
						TSelectSqlStatement selectStat = (TSelectSqlStatement) lztable.getSubquery( );

						if ( name == null )
						{
							ret = findColumnInSubQuery( selectStat,
									column.columnName,
									level + 1 );
							break;
						}

						if ( lztable.getAliasClause( ) != null
								&& lztable.getAliasClause( )
										.getAliasName( )
										.toString( )
										.equalsIgnoreCase( name ) )
						{
							ret = findColumnInSubQuery( selectStat,
									column.columnName,
									level + 1 );
							break;
						}

						boolean flag = false;
						for ( int j = 0; j < selectStat.tables.size( ); j++ )
						{
							if ( selectStat.tables.getTable( i )
									.getAliasClause( ) != null )
							{
								if ( selectStat.tables.getTable( i )
										.getAliasClause( )
										.getAliasName( )
										.toString( )
										.equalsIgnoreCase( name ) )
								{
									ret = findColumnInSubQuery( selectStat,
											column.columnName,
											level + 1 );
									flag = true;
									break;
								}
							}
							else
							{
								if ( selectStat.tables.getTable( i )
										.getTableName( )
										.toString( )
										.equalsIgnoreCase( name ) )
								{
									ret = findColumnInSubQuery( selectStat,
											column.columnName,
											level + 1 );
									flag = true;
									break;
								}
							}
						}
						if ( flag )
							break;
					}
					break;
				default :
					break;
			}
			if ( ret )
				break;
		}

		return ret;
	}

	private boolean findColumnInTables( TColumn column,
			TCustomSqlStatement select, int level, String columnName )
	{
		boolean ret = false;
		for ( String tableName : column.tableNames )
		{
			if ( columnName != null && filter != null )
			{
				int dotIndex = tableName.lastIndexOf( "." );
				String tableOwner = null;
				String tableRealName = null;
				if ( dotIndex >= 0 )
				{
					tableOwner = tableName.substring( 0, dotIndex );
					tableRealName = tableName.replace( tableOwner + ".", "" );
				}
				else
				{
					tableRealName = tableName;
				}
				if ( filter.metaDatabaseTableColumn( tableOwner,
						tableRealName,
						columnName ) )
				{
					column.columnName = columnName;
					// column.orignColumn = "*";
					ret |= findColumnInTables( column, tableName, select, level );
				}
				else
					ret |= false;
			}
			else
				ret |= findColumnInTables( column, tableName, select, level );
		}
		return ret;
	}

	private void findColumnsFromClauses( TCustomSqlStatement select, int level )
	{
		currentClauseMap.put( select, ClauseType.undefine );
		HashMap<TExpression, ClauseType> clauseTable = new LinkedHashMap<TExpression, ClauseType>( );
		if ( select instanceof TSelectSqlStatement )
		{

			TSelectSqlStatement statement = (TSelectSqlStatement) select;

			if ( statement.getOrderbyClause( ) != null )
			{
				TOrderBy sortList = statement.getOrderbyClause( );
				for ( int i = 0; i < sortList.getItems( ).size( ); i++ )
				{
					TOrderByItem orderBy = sortList.getItems( )
							.getOrderByItem( i );
					TExpression expr = orderBy.getSortKey( );
					clauseTable.put( expr, ClauseType.orderby );
				}
			}

			if ( statement.getWhereClause( ) != null )
			{
				clauseTable.put( statement.getWhereClause( ).getCondition( ),
						ClauseType.where );
			}
			if ( statement.getHierarchicalClause( ) != null
					&& statement.getHierarchicalClause( ).getConnectByClause( ) != null )
			{
				clauseTable.put( statement.getHierarchicalClause( )
						.getConnectByClause( ), ClauseType.connectby );
			}
			if ( statement.getHierarchicalClause( ) != null
					&& statement.getHierarchicalClause( ).getStartWithClause( ) != null )
			{
				clauseTable.put( statement.getHierarchicalClause( )
						.getStartWithClause( ), ClauseType.startwith );
			}
			if ( statement.joins != null )
			{
				for ( int i = 0; i < statement.joins.size( ); i++ )
				{
					TJoin join = statement.joins.getJoin( i );
					if ( join.getJoinItems( ) != null )
					{
						for ( int j = 0; j < join.getJoinItems( ).size( ); j++ )
						{
							TJoinItem joinItem = join.getJoinItems( )
									.getJoinItem( j );
							TExpression expr = joinItem.getOnCondition( );
							if ( expr != null )
								clauseTable.put( expr, ClauseType.join );
						}
					}
				}
			}
		}
		else if ( select instanceof TUpdateSqlStatement )
		{
			TUpdateSqlStatement statement = (TUpdateSqlStatement) select;
			if ( statement.getOrderByClause( ) != null )
			{
				TOrderByItemList sortList = statement.getOrderByClause( )
						.getItems( );
				for ( int i = 0; i < sortList.size( ); i++ )
				{
					TOrderByItem orderBy = sortList.getOrderByItem( i );
					TExpression expr = orderBy.getSortKey( );
					clauseTable.put( expr, ClauseType.orderby );
				}
			}
			if ( statement.getWhereClause( ) != null )
			{
				clauseTable.put( statement.getWhereClause( ).getCondition( ),
						ClauseType.where );
			}

			if ( statement.joins != null )
			{
				for ( int i = 0; i < statement.joins.size( ); i++ )
				{
					TJoin join = statement.joins.getJoin( i );
					if ( join.getJoinItems( ) != null )
					{
						for ( int j = 0; j < join.getJoinItems( ).size( ); j++ )
						{
							TJoinItem joinItem = join.getJoinItems( )
									.getJoinItem( j );
							TExpression expr = joinItem.getOnCondition( );
							if ( expr != null )
								clauseTable.put( expr, ClauseType.join );
						}
					}
				}
			}
		}

		for ( TExpression expr : clauseTable.keySet( ) )
		{
			currentClauseMap.put( select, clauseTable.get( expr ) );

			if ( !simply )
			{
				switch ( (ClauseType) currentClauseMap.get( select ) )
				{
					case where :
						buffer.append( buildString( " ", level )
								+ "--> Where Clause\r\n" );
						break;
					case connectby :
						buffer.append( buildString( " ", level )
								+ "--> Connect By Clause\r\n" );
						break;
					case startwith :
						buffer.append( buildString( " ", level )
								+ "--> Start With Clause\r\n" );
						break;
					case orderby :
						buffer.append( buildString( " ", level )
								+ "--> Order By Clause\r\n" );
						break;
					case join :
						buffer.append( buildString( " ", level )
								+ "--> Join\r\n" );
						break;
				}

			}

			List<TColumn> columns = exprToColumn( expr, select, level );
			for ( TColumn column1 : columns )
			{
				for ( String tableName : column1.tableNames )
				{
					if ( !simply )
					{

						switch ( (ClauseType) currentClauseMap.get( select ) )
						{
							case where :
								buffer.append( buildString( " ", level + 1 )
										+ "--> "
										+ column1.getFullName( tableName )
										+ "(Where)\r\n" );
								break;
							case connectby :
								buffer.append( buildString( " ", level + 1 )
										+ "--> "
										+ column1.getFullName( tableName )
										+ "(Connect By)\r\n" );
								break;
							case startwith :
								buffer.append( buildString( " ", level + 1 )
										+ "--> "
										+ column1.getFullName( tableName )
										+ "(Start With)\r\n" );
								break;
							case orderby :
								buffer.append( buildString( " ", level + 1 )
										+ "--> "
										+ column1.getFullName( tableName )
										+ "(Order By)\r\n" );
								break;
							case join :
								buffer.append( buildString( " ", level + 1 )
										+ "--> "
										+ column1.getFullName( tableName )
										+ "(Join)\r\n" );
								break;
						}

					}
					findColumnInTables( column1, tableName, select, level );
				}

			}
		}
		currentClauseMap.remove( select );

		// check order by clause
		findColumnsFromGroupBy( select, level );
	}

	private void findColumnsFromGroupBy( TCustomSqlStatement select, int level )
	{
		if ( select instanceof TSelectSqlStatement
				&& ( (TSelectSqlStatement) select ).getGroupByClause( ) != null )
		{
			for ( int j = 0; j < ( (TSelectSqlStatement) select ).getGroupByClause( )
					.getItems( )
					.size( ); j++ )
			{
				TGroupByItem i = ( (TSelectSqlStatement) select ).getGroupByClause( )
						.getItems( )
						.getGroupByItem( j );
				List<TColumn> columns1 = exprToColumn( i.getExpr( ),
						select,
						level );

				TColumn column1 = columns1.get( 0 );
				for ( String tableName : column1.tableNames )
				{
					if ( !simply )
					{
						buffer.append( buildString( " ", level )
								+ "--> "
								+ column1.getFullName( tableName )
								+ "(group by)\r\n" );
					}
					findColumnInTables( column1,
							tableName,
							select,
							level,
							ClauseType.groupby );
				}

			}

		}
	}

	private void findColumnsFromList( TCustomSqlStatement select, int level,
			TParseTreeNodeList list )
	{
		if ( list == null )
			return;

		for ( int i = 0; i < list.size( ); i++ )
		{
			Object element = list.getElement( i );
			TExpression lcexpr = null;
			if ( element instanceof TGroupByItem )
			{
				lcexpr = ( (TGroupByItem) element ).getExpr( );
			}
			else if ( element instanceof TExpression )
			{
				lcexpr = (TExpression) element;
			}

			if ( lcexpr != null )
			{
				List<TColumn> columns = exprToColumn( lcexpr, select, level );
				for ( TColumn column1 : columns )
				{
					findColumnInTables( column1, select, level, null );
					findColumnsFromClauses( select, level );
				}
			}
		}
	}

	public String getImpactResult( )
	{
		return buffer.toString( );
	}

	private void impactSQL( TGSqlParser sqlparser )
	{
		int ret = sqlparser.parse( );

		if ( ret != 0 )
		{
			buffer.append( sqlparser.getErrormessage( ) + "\r\n" );
		}
		else
		{
			Document doc = null;
			Element columnImpactResult = null;
			if ( simply && isXML )
			{
				DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance( );
				try
				{
					DocumentBuilder builder = factory.newDocumentBuilder( );
					doc = builder.newDocument( );
					doc.setXmlVersion( "1.0" );
					columnImpactResult = doc.createElement( "columnImpactResult" );
					doc.appendChild( columnImpactResult );
				}
				catch ( ParserConfigurationException e )
				{
					e.printStackTrace( );
				}
			}

			for ( int k = 0; k < sqlparser.sqlstatements.size( ); k++ )
			{
				if ( sqlparser.sqlstatements.get( k ) instanceof TCustomSqlStatement )
				{
					dependMap.clear( );
					aliases.clear( );
					currentSource = null;
					cteMap.clear( );
					currentClauseMap.clear( );

					TCustomSqlStatement select = (TCustomSqlStatement) sqlparser.sqlstatements.get( k );
					if ( select.getCteList( ) != null
							&& select.getCteList( ).size( ) > 0 )
					{
						for ( int i = 0; i < select.getCteList( ).size( ); i++ )
						{
							TCTE expression = (TCTE) select.getCteList( )
									.getCTE( i );
							cteMap.put( expression.getTableName( ).toString( ),
									expression.getSubquery( ) );
						}
					}

					impactSqlFromStatement( select );

					if ( simply )
					{
						if ( !isXML )
						{
							for ( TAlias alias : aliases )
							{

								buffer.append( alias.alias + " depends on: " );

								List<String> collections = new ArrayList<String>( );

								if ( dependMap.containsKey( alias.alias ) )
								{
									List<TResultEntry> results = (List<TResultEntry>) dependMap.get( alias.alias );
									for ( TResultEntry result : results )
									{
										String column = null;
										if ( "*".equals( result.targetColumn ) )
										{
											column = result.targetTable.getFullName( )
													.toLowerCase( );
										}
										else
										{
											column = ( result.targetTable.getFullName( )
													+ "." + result.targetColumn ).toLowerCase( );
										}
										if ( !collections.contains( column ) )
											collections.add( column );
									}
								}

								List<String> list = new ArrayList<String>( collections );
								for ( int i = 0; i < list.size( ); i++ )
								{
									if ( i < collections.size( ) - 1 )
										buffer.append( list.get( i ) + ", " );
									else
										buffer.append( list.get( i ) );
								}

								buffer.append( "\r\n" );

							}
						}
						else
						{

							for ( TAlias alias : aliases )
							{
								Element targetColumn = doc.createElement( "targetColumn" );
								targetColumn.setAttribute( "name", alias.column );
								if ( !alias.alias.equals( alias.column ) )
									targetColumn.setAttribute( "alias",
											alias.alias );
								columnImpactResult.appendChild( targetColumn );

								HashMap<String, TSourceColumn> collections = new LinkedHashMap<String, TSourceColumn>( );

								if ( dependMap.containsKey( alias.alias ) )
								{
									List<TResultEntry> results = (List<TResultEntry>) dependMap.get( alias.alias );
									for ( TResultEntry result : results )
									{
										String key = null;
										if ( "*".equals( result.targetColumn ) )
										{
											key = result.targetTable.getFullName( )
													.toLowerCase( );
										}
										else
										{
											key = ( result.targetTable.getFullName( )
													.toLowerCase( )
													+ "." + result.targetColumn ).toLowerCase( );
										}

										TSourceColumn sourceColumn = null;
										if ( collections.containsKey( key ) )
										{
											sourceColumn = (TSourceColumn) collections.get( key );
											if ( !sourceColumn.clauses.contains( result.clause )
													&& !ClauseType.undefine.equals( result.clause ) )
											{
												sourceColumn.clauses.add( result.clause );
											}
										}
										else
										{
											sourceColumn = new TSourceColumn( );
											collections.put( key, sourceColumn );
											sourceColumn.tableOwner = result.targetTable.getTableName( )
													.getSchemaString( );
											sourceColumn.tableName = result.targetTable.getName( );
											if ( !"*".equals( result.targetColumn ) )
											{
												sourceColumn.name = result.targetColumn;
											}
											if ( !sourceColumn.clauses.contains( result.clause )
													&& !ClauseType.undefine.equals( result.clause ) )
											{
												sourceColumn.clauses.add( result.clause );
											}
										}
									}

									Iterator<String> iter = collections.keySet( )
											.iterator( );

									while ( iter.hasNext( ) )
									{
										TSourceColumn sourceColumn = (TSourceColumn) collections.get( iter.next( ) );
										if ( sourceColumn.clauses.size( ) > 0 )
										{
											for ( int j = 0; j < sourceColumn.clauses.size( ); j++ )
											{
												ClauseType clause = sourceColumn.clauses.get( j );
												Element element = doc.createElement( "sourceColumn" );
												if ( sourceColumn.tableOwner != null )
												{
													element.setAttribute( "tableOwner",
															sourceColumn.tableOwner );
												}
												if ( sourceColumn.tableName != null )
												{
													element.setAttribute( "tableName",
															sourceColumn.tableName );
												}
												if ( sourceColumn.name != null )
												{
													element.setAttribute( "name",
															sourceColumn.name );
												}
												{
													StringBuilder buffer = new StringBuilder( );
													switch ( clause )
													{
														case where :
															buffer.append( "where" );
															break;
														case connectby :
															buffer.append( "connect by" );
															break;
														case startwith :
															buffer.append( "start with" );
															break;
														case orderby :
															buffer.append( "order by" );
															break;
														case join :
															buffer.append( "join" );
															break;
														case select :
															buffer.append( "select" );
															break;
														case groupby :
															buffer.append( "group by" );
															break;
													}
													element.setAttribute( "clause",
															buffer.toString( ) );
												}
												targetColumn.appendChild( element );
											}
										}
										else
										{
											Element element = doc.createElement( "sourceColumn" );
											if ( sourceColumn.tableOwner != null )
											{
												element.setAttribute( "tableOwner",
														sourceColumn.tableOwner );

											}
											if ( sourceColumn.tableName != null )
											{
												element.setAttribute( "tableName",
														sourceColumn.tableName );
											}
											if ( sourceColumn.name != null )
											{
												element.setAttribute( "name",
														sourceColumn.name );
											}
											targetColumn.appendChild( element );
										}
									}
								}
							}
						}
					}
				}
			}
			if ( doc != null )
			{
				try
				{
					StringWriter sw = new StringWriter( );
					com.sun.org.apache.xml.internal.serialize.OutputFormat format = new com.sun.org.apache.xml.internal.serialize.OutputFormat( doc );
					format.setIndenting( true );
					format.setIndent( 2 );
					Writer output = new BufferedWriter( sw );
					com.sun.org.apache.xml.internal.serialize.XMLSerializer serializer = new com.sun.org.apache.xml.internal.serialize.XMLSerializer( output,
							format );
					serializer.serialize( doc );
					buffer.append( sw.toString( ).trim( ) );
				}
				catch ( IOException e )
				{
					e.printStackTrace( );
				}
			}
		}
	}

	private void impactSqlFromStatement( TCustomSqlStatement select )
	{
		if ( select instanceof TSelectSqlStatement )
		{
			TSelectSqlStatement stmt = (TSelectSqlStatement) select;
			if ( stmt.getSetOperator( ) != TSelectSqlStatement.setOperator_none )
			{
				impactSqlFromStatement( stmt.getLeftStmt( ) );
				impactSqlFromStatement( stmt.getRightStmt( ) );
			}
			else
			{
				for ( int i = 0; i < select.getResultColumnList( ).size( ); i++ )
				{
					linkFieldToTables( select.getResultColumnList( )
							.getResultColumn( i ), select, 0 );
				}
			}
		}
		else if ( select instanceof gudusoft.gsqlparser.stmt.TInsertSqlStatement
				&& ( (gudusoft.gsqlparser.stmt.TInsertSqlStatement) select ).getSubQuery( ) != null )
		{
			impactSqlFromStatement( ( (gudusoft.gsqlparser.stmt.TInsertSqlStatement) select ).getSubQuery( ) );
		}
		else
		{
			if ( select.getResultColumnList( ) != null )
			{
				for ( int i = 0; i < select.getResultColumnList( ).size( ); i++ )
				{
					linkFieldToTables( select.getResultColumnList( )
							.getResultColumn( i ), select, 0 );
				}
			}
		}
	}

	private boolean isPseudocolumn( String column )
	{
		if ( column == null )
			return false;
		if ( "rownum".equalsIgnoreCase( column.trim( ) ) )
			return true;
		else if ( "rowid".equalsIgnoreCase( column.trim( ) ) )
			return true;
		else if ( "nextval".equalsIgnoreCase( column.trim( ) ) )
			return true;
		else if ( "sysdate".equalsIgnoreCase( column.trim( ) ) )
			return true;
		return false;
	}

	private boolean linkFieldToTables( TResultColumn field,
			TCustomSqlStatement select, int level )
	{
		boolean ret = false;
		// all items in select list was represented by a TLzField Objects
		switch ( field.getExpr( ).getExpressionType( ) )
		{
			case TExpression.simpleObjectname :
				TColumn column = attrToColumn( field.getExpr( ), select );
				if ( level == 0 )
				{
					TAlias alias = new TAlias( );
					alias.column = field.toString( );
					alias.alias = field.toString( );
					if ( field.getAliasClause( ) != null )
					{
						alias.alias = field.getAliasClause( ).toString( );
						alias.column = field.toString( );
					}
					aliases.add( alias );
					currentSource = alias.alias;
					if ( !dependMap.containsKey( currentSource ) )
						dependMap.put( currentSource,
								new ArrayList<TResultEntry>( ) );

					if ( !simply )
					{
						if ( !alias.alias.equalsIgnoreCase( column.getOrigName( ) ) )
						{
							buffer.append( "\r\nSearch " + alias.alias + "\r\n" );
							buffer.append( "--> "
									+ column.getOrigName( )
									+ "\r\n" );
						}
						buffer.append( "\r\nSearch "
								+ column.getOrigName( )
								+ "\r\n" );
					}

				}
				if ( select.dbvendor == EDbVendor.dbvoracle
						&& this.isPseudocolumn( column.columnName ) )
				{
					break;
				}
				ret = findColumnInTables( column, select, level, null );
				findColumnsFromClauses( select, level + 1 );
				break;
			case TExpression.subqueryExprOperator :
				TAlias alias1 = new TAlias( );
				alias1.column = field.toString( );
				alias1.alias = field.toString( );
				if ( field.getAliasClause( ) != null )
				{
					alias1.alias = field.getAliasClause( ).toString( );
					alias1.column = field.toString( );
				}
				if ( level == 0 )
				{
					aliases.add( alias1 );
					if ( !simply )
					{
						buffer.append( "\r\n"
								+ "Search "
								+ alias1.alias
								+ "\r\n" );
					}
				}
				findColumnInSubQuery( (TSelectSqlStatement) field.getExpr( )
						.getSubQuery( ), alias1.alias, level );
				break;
			default :
				if ( level == 0 )
				{
					TAlias alias = new TAlias( );

					if ( select instanceof TUpdateSqlStatement )
					{
						alias.column = field.getExpr( )
								.getLeftOperand( )
								.toString( );
						alias.alias = alias.column;
					}
					else
					{
						alias.column = field.toString( );
						alias.alias = alias.column;

					}
					if ( field.getAliasClause( ) != null )
					{
						alias.alias = field.getAliasClause( ).toString( );
						alias.column = field.toString( );
					}
					aliases.add( alias );
					if ( !simply )
					{
						buffer.append( "\r\n"
								+ "Search "
								+ alias.alias
								+ "\r\n" );
					}

					currentSource = alias.alias;
					if ( !dependMap.containsKey( currentSource ) )
						dependMap.put( currentSource,
								new ArrayList<TResultEntry>( ) );
				}

				List<TColumn> columns = exprToColumn( field.getExpr( ),
						select,
						level );
				if ( select instanceof TUpdateSqlStatement )
				{
					while ( columns.size( ) > 1 )
					{
						columns.remove( columns.size( ) - 1 );
					}
				}
				if ( !simply )
				{
					for ( TColumn column1 : columns )
					{
						if ( level == 0 )
						{
							buffer.append( buildString( " ", level )
									+ "--> "
									+ column1.getOrigName( )
									+ "\r\n" );
						}
					}
				}

				for ( TColumn column1 : columns )
				{
					if ( level == 0 )
					{
						if ( !simply )
						{
							buffer.append( "\r\n"
									+ "Search "
									+ column1.getOrigName( )
									+ "\r\n" );
						}
					}
					if ( !( select instanceof TUpdateSqlStatement ) )
					{
						findColumnInTables( column1, select, level, null );
					}
					findColumnsFromClauses( select, level + 1 );
				}

				if ( field.getExpr( ).getExpressionType( ) == TExpression.funcationCallOperator )
				{
					TFunctionCall func = (TFunctionCall) field.getExpr( )
							.getFunctionCall( );
					// buffer.AppendLine("function name {0}",
					// func.funcname.AsText);
					if ( func.getFunctionName( )
							.toString( )
							.equalsIgnoreCase( "count" )
							|| func.getFunctionName( )
									.toString( )
									.equalsIgnoreCase( "sum" )
							|| func.getFunctionName( )
									.toString( )
									.equalsIgnoreCase( "row_number" ) )
					{
						if ( !simply )
						{
							buffer.append( buildString( " ", level )
									+ "--> aggregate function "
									+ func.toString( )
									+ "\r\n" );
							for ( int i = 0; i < select.tables.size( ); i++ )
							{
								buffer.append( buildString( " ", level )
										+ "--> table "
										+ select.tables.getTable( i )
												.toString( ) + "\r\n" );
							}
						}
						// check column in function arguments
						int argCount = 0;
						if ( func.getArgs( ) != null )
						{
							for ( int k = 0; k < func.getArgs( ).size( ); k++ )
							{
								TExpression expr = func.getArgs( )
										.getExpression( k );
								if ( expr.toString( ).trim( ).equals( "*" ) )
									continue;
								List<TColumn> columns1 = exprToColumn( expr,
										select,
										level );
								for ( TColumn column1 : columns1 )
								{
									findColumnInTables( column1,
											select,
											level,
											null );
									findColumnsFromClauses( select, level + 1 );
								}
								argCount++;
							}
						}

						if ( argCount == 0 )
						{
							if ( dependMap.containsKey( currentSource ) )
							{

								if ( currentClauseMap.containsKey( select ) )
								{
									dependMap.get( currentSource )
											.add( new TResultEntry( select.tables.getTable( 0 ),
													"*",
													(ClauseType) currentClauseMap.get( select ) ) );
								}
								else if ( select instanceof TSelectSqlStatement )
								{
									dependMap.get( currentSource )
											.add( new TResultEntry( select.tables.getTable( 0 ),
													"*",
													ClauseType.select ) );
								}
								else
								{
									dependMap.get( currentSource )
											.add( new TResultEntry( select.tables.getTable( 0 ),
													"*",
													ClauseType.undefine ) );
								}
							}
						}

						if ( func.getAnalyticFunction( ) != null )
						{
							TParseTreeNodeList list = func.getAnalyticFunction( )
									.getPartitionBy_ExprList( );
							findColumnsFromList( select, level, list );

							if ( func.getAnalyticFunction( ).getOrderBy( ) != null )
							{
								list = func.getAnalyticFunction( )
										.getOrderBy( )
										.getItems( );
								findColumnsFromList( select, level, list );
							}
						}

						findColumnsFromClauses( select, level );

					}
				}
				break;
		}

		return ret;
	}

	private Table TLzTaleToTable( TTable lztable )
	{
		Table table = new Table( );
		if ( lztable.getTableName( ) != null )
		{
			table.tableName = lztable.getName( );
			if ( lztable.getTableName( ).toString( ).indexOf( "." ) > 0 )
			{
				table.prefixName = lztable.getTableName( )
						.toString( )
						.substring( 0, lztable.getFullName( ).indexOf( '.' ) );
			}
		}

		if ( lztable.getAliasClause( ) != null )
		{
			table.tableAlias = lztable.getAliasClause( ).toString( );
		}
		return table;
	}

}
