EasiReports

using System;
using System.Collections;
using System.Windows.Forms;
using System.Runtime.Serialization;
using System.Globalization;
using System.Diagnostics;

using EasiReports.Report;
using EasiReports.Metadata;
using EasiReports.Where;

using Tree;

namespace EasiReports
{
//-----------------------------------------------------------------------------

	/// <summary>
	///    Summary description for CSql.
	/// </summary>
	internal class CSql
	{
		public bool bAutoJoin = true;

		public ArrayList m_alSelect   = new ArrayList (); // CSelectItem
		public ArrayList m_alFrom     = new ArrayList (); // CFromItem
		public ArrayList m_alJoin     = new ArrayList (); // CJoinItem
		public ArrayList m_alOrder    = new ArrayList (); // OrderItem
		public WhereTreeCollection m_WhereTree = null;

		private string m_sConnection = null;
		private CMetadata m_metadata = null;
		private CWriterBase m_writer = null;

//-----------------------------------------------------------------------------
// Constructor

		public CSql( string sConnection, CMetadata metadata )
		{
			m_sConnection = sConnection;
			m_metadata = metadata;
			m_writer = CreateWriter();
		}

//-----------------------------------------------------------------------------

		public char cDateDelimiter
		{ 
			get
			{
				return m_writer.cDateDelimiter;
			}
		}

		public string sStatement
		{
			get
			{
				return m_writer.sStatement;
			}
		}

		public string ToStringConstant( object o )
		{
			string s = o.ToString();

			switch ( o.GetType().FullName )
			{
				case "System.Boolean" :
					s = ( (Boolean)o ) ? "true" : "false";
					break;

				case "System.Byte" :
				case "System.Decimal" :
				case "System.Double" :
				case "System.GUID" :
				case "System.Int16" :
				case "System.Int32" :
				case "System.Int64" :
				case "System.SByte" :
				case "System.Single" :
				case "System.UInt16" :
				case "System.UInt32" :
				case "System.UInt64" :
					break;

				case "System.Char" :
					s = "'" + o.ToString() + "'";
					break;

				case "System.DateTime" :
				{
					DateTime dt = (DateTime) o;

					s = "";
					s += cDateDelimiter;

					if ( dt.TimeOfDay == TimeSpan.Zero )
						s += dt.ToString( "dd-MMM-yyyy", CultureInfo.CurrentCulture );
					else
						s += dt.ToString( "dd-MMM-yyyy HH:mm:ss", CultureInfo.CurrentCulture );

					s += cDateDelimiter;

					break;
				}

				case "System.TimeSpan" :
				case "System.String" :
					s = "'" + o.ToString() + "'";
					break;

				case "System.DBNull" :
					s = "NULL";
					break;

				default:
					Debug.Assert(false);
					break;
			}

			return s;
		}


//-----------------------------------------------------------------------------
// CWriterBase Factory

		public CWriterBase CreateWriter()
		{
			if ( m_sConnection == null ) return new CWriterAccess( this );

			string[] sa = m_sConnection.Split( new char[] {';'} );
			foreach ( string s in sa )
				if ( s.IndexOf( "Provider" ) >= 0 )
				{
					if ( s.IndexOf( "Jet.OLEDB" ) >= 0 ) return new CWriterAccess    ( this );
					if ( s.IndexOf( "SQLOLEDB"  ) >= 0 ) return new CWriterSqlServer ( this );
				}
			
			return new CWriterSqlServer( this );
		}

//-----------------------------------------------------------------------------
// Select

		public class CSelectItem
		{
			public string m_sTable;
			public string m_sField;
			public string _Name;

			public CSelectItem( string table, string field, string name )
			{
				m_sTable = table;
				m_sField = field;
				_Name = name;
			}
		}

//-----------------------------------------------------------------------------
// From

		public class CFromItem
		{
			public string sTable;

			public CFromItem( string sTable )
			{
				this.sTable = sTable;
			}
		}

//-----------------------------------------------------------------------------
// Join

		public enum eJoinType { Inner, Left, Right, Outer };

		public class CJoinItem
		{
			public eJoinType m_JoinType;
			public string m_sPrimaryTable;
			public string m_sFunction;
			public string m_sForeignTable;
			public ArrayList m_alFields = new ArrayList(); // CJoinItemItem

			public CJoinItem( eJoinType jt, string pt, string fn, string ft )
			{
				m_JoinType      = jt;
				m_sPrimaryTable = pt;
				m_sFunction     = fn;
				m_sForeignTable = ft;
			}
		}

		public class CJoinItemItem
		{
			public string m_sPrimaryField;
			public string m_sForiegnField;

			public CJoinItemItem( string pf, string ff )
			{
				m_sPrimaryField = pf;
				m_sForiegnField = ff;
			}
		}

//-----------------------------------------------------------------------------
// Where

//-----------------------------------------------------------------------------
// Order

//-----------------------------------------------------------------------------
// CWriterBase

		public abstract class CWriterBase
		{
			public CSql sql;

			public CWriterBase( CSql s )
			{
				sql = s;
			}

			public abstract char cDateDelimiter { get; }

			public abstract string sStatement
			{
				get;
			}
		}

//-----------------------------------------------------------------------------
// CWriterMicrosoft

		public abstract class CWriterMicrosoft : CWriterBase
		{
			public CWriterMicrosoft( CSql s ) : base( s ) {}

			private bool[] m_bFrom;
			private bool[] m_bJoin;

			public override string sStatement
			{
				get
				{
					string s = "";

					AddSelect( ref s );
					AddFrom  ( ref s );
					AddWhere ( ref s );
					AddOrder ( ref s );

					int i = s.IndexOf( ';' );
					if ( i >= 0 ) s = s.Substring( 0, i );

					return s;
				}
			}

			private void AddWithBraces( ref string s, string Text )
			{
				bool bSpace = ( Text.IndexOf( ' ' ) >= 0 );
				if ( bSpace ) s+= "[";
				s += Text;
				if ( bSpace ) s+= "]";
			}

			private void AddTableField( ref string s, string sTable, string sField )
			{
				AddWithBraces( ref s, sTable );
				s += ".";
				AddWithBraces( ref s, sField );
			}

			private void AddSelect( ref string s )
			{
				if ( sql.m_alSelect.Count == 0 ) return;
					
				s = "SELECT ";

				bool bFirst = true;
				foreach ( CSelectItem SelectItem in sql.m_alSelect )
				{
					if ( !bFirst ) s+= ", "; bFirst = false;

					AddTableField( ref s, SelectItem.m_sTable, SelectItem.m_sField );

					if ( SelectItem._Name != null )
						s += " AS " + Helper.Brackets( SelectItem._Name );
				}
			}

			private void AddFrom( ref string s )
			{
				if ( sql.m_alFrom.Count == 0 ) return;
					
				s += " FROM ";


				m_bFrom = new bool[ sql.m_alFrom.Count ];
				for ( int i = 0 ; i < m_bFrom.Length ; i++ ) m_bFrom[i] = false;

				m_bJoin = new bool[ sql.m_alJoin.Count ];
				for ( int i = 0 ; i < m_bJoin.Length ; i++ ) m_bJoin[i] = false;

				bool bFirst = true;
				int iFromItem = 0;
				string t = "";
				foreach ( CFromItem FromItem in sql.m_alFrom )
				{
					if ( !m_bFrom[ iFromItem ] )
					{
						m_bFrom[ iFromItem ] = true;
						
						if ( !bFirst ) s += ","; bFirst = false;

						AddWithBraces( ref t, FromItem.sTable );

						AddJoins( ref t, iFromItem, true );

						s += t; t = "";
					}

					iFromItem++;
				}
			}				

//-----------------------------------------------------------------------------

			private void AddJoins( ref string t, int iFromItem, bool bFirst )
			{
				CFromItem FromItem = (CFromItem) sql.m_alFrom[ iFromItem ];

				int iJoinItem = 0;
				foreach ( CJoinItem JoinItem in sql.m_alJoin )
				{
					if ( !m_bJoin[ iJoinItem ] && string.Compare( JoinItem.m_sPrimaryTable, JoinItem.m_sForeignTable, true, CultureInfo.CurrentCulture ) != 0 )
//					if ( !m_bJoin[ iJoinItem ] )
					{
						bool bPrimary = ( string.Compare( JoinItem.m_sPrimaryTable, FromItem.sTable, true, CultureInfo.CurrentCulture ) == 0 );
						bool bForeign = ( string.Compare( JoinItem.m_sForeignTable, FromItem.sTable, true, CultureInfo.CurrentCulture ) == 0 );

						if ( bPrimary || bForeign  )
						{
							m_bJoin[ iJoinItem ] = true;

							if ( !bFirst ) t = "(" + t + ")"; bFirst = false;

							if ( bPrimary ) AddJoinPrimary( ref t, iJoinItem );
							else
								if ( bForeign ) AddJoinForeign( ref t, iJoinItem );
						}
					}

					iJoinItem++;
				}
			}

			private void AddJoinPrimary( ref string t, int iJoinItem )
			{
				CJoinItem JoinItem = (CJoinItem) sql.m_alJoin[ iJoinItem ];

				switch ( JoinItem.m_JoinType )
				{
					case eJoinType.Inner : t += " INNER JOIN "; break;
					case eJoinType.Left  : t += " LEFT JOIN " ; break;
					case eJoinType.Right : t += " RIGHT JOIN "; break;
					case eJoinType.Outer : t += " OUTER JOIN "; break;
				}

				AddWithBraces( ref t, JoinItem.m_sForeignTable );

				t += " ON ";

				if ( JoinItem.m_alFields.Count > 1 ) t += "(";
				bool bFirst = true;
				foreach ( CJoinItemItem JoinItemItem in JoinItem.m_alFields )
				{
					if ( !bFirst ) t += ") AND ("; bFirst = false;

					AddTableField( ref t, JoinItem.m_sPrimaryTable, JoinItemItem.m_sPrimaryField );
					t += JoinItem.m_sFunction;
					AddTableField( ref t, JoinItem.m_sForeignTable, JoinItemItem.m_sForiegnField );
				}
				if ( JoinItem.m_alFields.Count > 1 ) t += ")";

				SetTableDone( ref t, JoinItem.m_sForeignTable );
			}

			private void AddJoinForeign( ref string t, int iJoinItem )
			{
				CJoinItem JoinItem = (CJoinItem) sql.m_alJoin[ iJoinItem ];

				switch ( JoinItem.m_JoinType )
				{
					case eJoinType.Inner : t += " INNER JOIN "; break;
					case eJoinType.Left  : t += " RIGHT JOIN "; break;
					case eJoinType.Right : t += " LEFT JOIN " ; break;
					case eJoinType.Outer : t += " OUTER JOIN "; break;
				}

				AddWithBraces( ref t, JoinItem.m_sPrimaryTable );

				t += " ON ";

				if ( JoinItem.m_alFields.Count > 1 ) t += "(";
				bool bFirst = true;
				foreach ( CJoinItemItem JoinItemItem in JoinItem.m_alFields )
				{
					if ( !bFirst ) t += ") AND ("; bFirst = false;

					AddTableField( ref t, JoinItem.m_sForeignTable, JoinItemItem.m_sForiegnField );
					t += JoinItem.m_sFunction;
					AddTableField( ref t, JoinItem.m_sPrimaryTable, JoinItemItem.m_sPrimaryField );
				}
				if ( JoinItem.m_alFields.Count > 1 ) t += ")";

				SetTableDone( ref t, JoinItem.m_sPrimaryTable );
			}

			private void SetTableDone( ref string t, string Table )
			{
				int iFromItem = 0;
				foreach( CFromItem FromItem in sql.m_alFrom )
				{
					if ( FromItem.sTable == Table )
					{
						m_bFrom[ iFromItem ] = true;
						AddJoins( ref t, iFromItem, false );
					}
					iFromItem++;
				}
			}

//-----------------------------------------------------------------------------

			private void AddWhere( ref string s )
			{
				if ( sql.m_WhereTree.Count == 0 ) return;

				s += " WHERE ";

				for ( TreeNodeEX node = sql.m_WhereTree.Root.Child ; node != null ; node = node.Next )
					RecursiveAddWhereNode( ref s, node );
			}

			private void RecursiveAddWhereNode( ref string s, TreeNodeEX n )
			{
				WhereTreeNode node = n as WhereTreeNode;
				if ( node == null ) { Debug.Assert(false); return; }

				if ( node.Data is WhereClause )
					s += " ( ";
				else
				if ( node.Data is WhereAndOr )
					s += ' ' + node.Data.ToString() + ' ';
				else
				if ( node.Data is WhereItem )
					s += node.Data.ToString();
				else
				{
					Debug.Assert(false);
					return;
				}
				

				for ( TreeNodeEX childNode = node.Child ; childNode != null ; childNode = childNode.Next )
					RecursiveAddWhereNode( ref s, childNode );
		
				if ( node.Data is WhereClause )
					s += " ) ";
			}

//-----------------------------------------------------------------------------

			private void AddOrder( ref string s )
			{
				if ( sql.m_alOrder.Count == 0 ) return;

				s += " ORDER BY ";
				
				bool bFirst = true;
				foreach( OrderItem i in sql.m_alOrder )
				{
					if ( !bFirst ) s += ","; bFirst = false;
					AddTableField( ref s, i.Table, i.Field );
					if ( i.OrderDirection == OrderDirection.Ascending  ) s += " ASC";
					if ( i.OrderDirection == OrderDirection.Descending ) s += " DESC";
				}
			}
		}

//-----------------------------------------------------------------------------
// CWriterAccess

		public class CWriterAccess : CWriterMicrosoft
		{
			public CWriterAccess( CSql s ) : base( s ) {}

			public override char cDateDelimiter
			{
				get
				{
					return '#';
				}
			}
		}

//-----------------------------------------------------------------------------
// CWriterSqlServer

		public class CWriterSqlServer : CWriterMicrosoft
		{
			public CWriterSqlServer( CSql s ) : base( s ) {}

			public override char cDateDelimiter
			{
				get
				{
					return '\'';
				}
			}
		}

//-----------------------------------------------------------------------------
// Manipulation functions

		public void AddSelect( string table, string field, string alias )
		{
			foreach ( CSelectItem s in m_alSelect )
				if ( string.Compare( s.m_sTable, table, true, CultureInfo.CurrentCulture ) == 0 )
				if ( string.Compare( s.m_sField, field, true, CultureInfo.CurrentCulture ) == 0 )
				if ( string.Compare( s._Name, alias, true, CultureInfo.CurrentCulture ) == 0 )
					return;

			m_alSelect.Add( new CSelectItem( table, field, alias ) );
			AddTable( table );
		}

		public void AddTable( string table )
		{
			foreach ( CFromItem FromItem in m_alFrom )
				if ( string.Compare( FromItem.sTable, table, true, CultureInfo.CurrentCulture ) == 0 )
					return;

			m_alFrom.Add( new CFromItem( table ) );
			if ( bAutoJoin ) AddJoin( table );
		}

		private void AddJoin( string table )
		{
			foreach ( CFromItem FromItem in m_alFrom )
			{
				if ( string.Compare( FromItem.sTable, table, true, CultureInfo.CurrentCulture ) == 0 ) continue;

				if ( m_metadata.GetForeignKeys( table, FromItem.sTable ) != null )
					AddJoin( table, FromItem.sTable );

				if ( m_metadata.GetForeignKeys( FromItem.sTable, table ) != null )
					AddJoin( FromItem.sTable, table );
			}
		}

		private void AddJoin( string sPrimaryTable, string sForeignTable )
		{
			ForeignKeyInfoCollection alForeignKeys = m_metadata.GetForeignKeys( sPrimaryTable, sForeignTable );
			if ( alForeignKeys == null ) return;

			CJoinItem JoinItem = new CJoinItem( eJoinType.Inner, sPrimaryTable, "=", sForeignTable );
			foreach ( ForeignKeyInfo fki in alForeignKeys )
				JoinItem.m_alFields.Add( new CJoinItemItem( fki.PKColumnName, fki.FKColumnName ) );

			m_alJoin.Add( JoinItem );
		}

		public void AddOrder( OrderItem item )
		{
			m_alOrder.Add( item );
		}

		public void AddWhereTree( WhereTreeCollection whereTree )
		{
			m_WhereTree = whereTree;
		}

		public bool bIsTableAlreadyPresent( string sTable )
		{
			foreach ( CFromItem fi in m_alFrom )
				if ( string.Compare( sTable, fi.sTable, true, CultureInfo.CurrentCulture ) == 0 ) return true;

			return false;
		}

//-----------------------------------------------------------------------------
// Detect unjoined tables
		
		public class CJoinSuggestion
		{
			public string sTableUnjoined = "";
			public string sTableOther = "";
			public string sTableLink = "";

			public CJoinSuggestion( string sTableUnjoined, string sTableOther, string sTableLink )
			{
				this.sTableUnjoined = sTableUnjoined;
				this.sTableOther = sTableOther;
				this.sTableLink = sTableLink;
			}
		}

		public CJoinSuggestion[] GetJoinSuggestions()
		{
			ArrayList alJoinSuggestion = new ArrayList();

			foreach ( CFromItem fi in m_alFrom )
				if ( !bIsJoined( fi.sTable ) )
				{
					ArrayList aljs = GetJoinSuggestions( fi.sTable );
					alJoinSuggestion.AddRange( aljs );
				}

			CJoinSuggestion[] aJoinSuggestion = new CJoinSuggestion[ alJoinSuggestion.Count ];

			int i = 0;
			foreach ( CJoinSuggestion js in alJoinSuggestion )
				aJoinSuggestion[ i++ ] = js;

			return aJoinSuggestion;
		}

		public bool bIsJoined( string sTable )
		{
			foreach ( CJoinItem ji in m_alJoin )
			{
				if ( string.Compare( ji.m_sPrimaryTable, sTable, true, CultureInfo.CurrentCulture ) == 0 ) return true;
				if ( string.Compare( ji.m_sForeignTable, sTable, true, CultureInfo.CurrentCulture ) == 0 ) return true;
			}

			foreach ( object o in m_WhereTree )
				if ( o is WhereItem )
				{
					WhereItem i = (WhereItem) o;

					if ( i.Mode != WhereItem.EMode.Auto ) continue;
					if ( i.Function != WhereItem.EFunction.Equals ) continue;

					if ( i.Lhs.Mode != WhereItemItem.EMode.Field ) continue;
					if ( i.Rhs.Mode != WhereItemItem.EMode.Field ) continue;

					if ( string.Compare( sTable, i.Lhs.Table, true, CultureInfo.CurrentCulture ) == 0 ) return true;
					if ( string.Compare( sTable, i.Rhs.Table, true, CultureInfo.CurrentCulture ) == 0 ) return true;
				}

			return false;
		}

		private ArrayList GetJoinSuggestions( string sTable )
		{
			ArrayList alJoinSuggestions = new ArrayList();

			foreach ( CFromItem fi in m_alFrom )
				if ( string.Compare( sTable, fi.sTable, true, CultureInfo.CurrentCulture ) != 0 )
				{
					ArrayList alLink = GetJoinSuggestions( sTable, fi.sTable );
					foreach ( string sLink in alLink )
						alJoinSuggestions.Add( new CJoinSuggestion( sTable, fi.sTable, sLink ) );
				}

			return alJoinSuggestions;
		}

		private ArrayList GetJoinSuggestions( string sTableUnjoined, string sTableOther )
		{
			ArrayList alTableLink = new ArrayList();

			TableItemDictionary htTables = m_metadata.Tables;

			foreach ( TableItem ti in htTables.Values )
				if ( bIsLinkTable( sTableUnjoined, sTableOther, ti.Name ) )
					alTableLink.Add( ti.Name );

			return alTableLink;
		}

		private bool bIsLinkTable( string sTableUnjoined, string sTableOther, string sTableLink )
		{
			bool bJoined = false;
			if ( m_metadata.GetForeignKeys( sTableUnjoined, sTableLink ) != null ) bJoined = true;
			if ( m_metadata.GetForeignKeys( sTableLink, sTableUnjoined ) != null ) bJoined = true;
			if ( !bJoined ) return false;

			bJoined = false;
			if ( m_metadata.GetForeignKeys( sTableOther, sTableLink ) != null ) bJoined = true;
			if ( m_metadata.GetForeignKeys( sTableLink, sTableOther ) != null ) bJoined = true;
			if ( !bJoined ) return false;

			return true;
		}

//-----------------------------------------------------------------------------

	}
}