/Current/ODX.Core/Pager.cs

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;



namespace ODX.Core

{

    /// <summary>

    /// <see cref="Pager{T}"/> can query underlying data source with free SQL query or using a simple filter.

    /// <see cref="Pager{T}"/> with SQL can be executen on the SQL data source only.

    /// <see cref="Pager{T}"/> with filter could be executed on any data source and even on the disconnected <see cref="Session"/>

    /// (Session with no any <see cref="IDataProvider"/> assigned).

    /// </summary>

    public enum PagerFilterType

    {

        /// <summary>Filter-based <see cref="Pager{T}"/></summary>

        Filter,



        /// <summary>SQL-based <see cref="Pager{T}"/></summary>

        Select

    }



    /// <summary>

    /// Supports paging and paged sequential (cursor-like) data source quering without connected cursors.

    /// </summary>

    /// <typeparam name="T">Tyoe of objects, returned by <see cref="Pager{T}"/></typeparam>

    public class Pager<T> : IEnumerable<T> where T : Entity

    {

        private struct SF

        {

            public string field;

            public bool desc;

        }



        private readonly Session session;

        private readonly string filterOrSelect;

        private readonly int pageSize;

        private T lastEntity;

        private T firstEntity;

        private SF[] srt;

        private readonly object[] listFilterParameters;

        private readonly PagerFilterType filterType;

        private string alias = string.Empty;



        /// <summary>Creates <see cref="Pager{T}"/> object</summary>

        public Pager(Session session, string filterOrSelect, string sort, int pageSize, params object[] listFilterParameters)

            : this(session, filterOrSelect, sort, pageSize, PagerFilterType.Filter, listFilterParameters)

        {

        }



        /// <summary>Creates <see cref="Pager{T}"/> object</summary>

        public Pager(Session session, string filterOrSelect, string sort, int pageSize, PagerFilterType filterType, params object[] listFilterParameters)

            : this(session, filterOrSelect, sort, pageSize, filterType)

        {

            this.listFilterParameters = listFilterParameters;

        }



        /// <summary>Creates <see cref="Pager{T}"/> object</summary>

        public Pager(Session session, string filterOrSelect, string sort, int pageSize)

            : this(session, filterOrSelect, sort, pageSize, PagerFilterType.Filter)

        {

        }



        /// <summary>Creates <see cref="Pager{T}"/> object</summary>

        public Pager(Session session, string filterOrSelect, string sort, int pageSize, PagerFilterType filterType)

        {

            this.session = session;

            this.filterOrSelect = filterOrSelect;

            this.pageSize = pageSize;

            this.filterType = filterType;



            if (sort == null || sort.Trim().Length == 0)

                sort = "ID";

            else

                sort = sort + ", ID";



            string[] fields = sort.Split(',');

            srt = new SF[fields.Length];

            for (int i = 0; i < srt.Length; i++ )

            {

                if (fields[i].Trim().ToUpper().EndsWith(" DESC"))

                    srt[i].desc = true;

                else

                    srt[i].desc = false;



                srt[i].field = fields[i].Trim().Split(' ')[0];

            }

        }



        /// <summary>

        /// If free SQL is used to create a <see cref="Pager{T}"/>

        /// and a number of tables were joined to create appropriate query

        /// you should specify an alias name you gave to the main table

        /// which fields will be selected.

        /// </summary>

        /// <remarks>

        /// Assume we have an addresses database in which every Person assigned one ore more addresses.

        /// if we want to discover addresses of people elder a particular age we can use the following  SQL

        /// <code>

        /// SELECT a.* FROM Adress a INNER JOIN Person p ON p.ID = a.PersonID

        /// WHERE p.BirthDate > ?

        /// </code>

        /// In this case <see cref="Alias"/> should be set to

        /// <code>

        /// pager.Alias = "a.";

        /// </code>

        /// </remarks>

        public string Alias

        {

            get { return alias; }

            set { alias = value ?? string.Empty; }

        }



        private NullsPosition NullsPosition

        {

            get

            {

                if ( session.DataProvider == null )

                    return NullsPosition.Minimum;

                NullsPosition pos = session.DataProvider.NullsPosition;

                if ( pos == NullsPosition.Undefined )

                    throw new OdxException(

                        "Can't page query: Nulls relative position is undefined for the data provider!!!");



                return pos;

            }

        }



        private static string BuildClause(string field, string sign)

        {

            return string.Format("{0} {1} ?", field, sign);

        }



        private string GetSort(bool forward)

        {

            List<string> sort = new List<string>();

            foreach ( SF sf in srt )

            {

                if ( forward ^ sf.desc )

                    sort.Add(alias + sf.field);

                else

                    sort.Add(alias + sf.field + " DESC");

            }



            return string.Join(", ", sort.ToArray());

        }



        private string ForwardSort {get{ return GetSort(true); }}

        private string BackwardSort {get{ return GetSort(false); }}



        private string GetFilter(bool forward, out object[] values)

        {

            Entity e = forward ? lastEntity : firstEntity;

            List<string> F = new List<string>();

            List<object> pValues = new List<object>();

            for (int i = 0; i < srt.Length && e != null; i++)

            {

                List<string> f = new List<string>();

                for (int j = 0; j <= i; j++)

                {

                    object value = e.GetProperty(srt[j].field);

                    

                    if ( j < i )

                    {

                        if (value == null || value is DBNull)

                            f.Add(alias + srt[j].field + " IS NULL");

                        else

                        {

                            f.Add(BuildClause(alias + srt[j].field, "="));

                            pValues.Add(value);

                        }

                    }

                    else

                    {

                        bool ascending = forward ^ srt[j].desc;

                        bool nullsMin = NullsPosition == NullsPosition.Minimum;



                        if (value == null || value is DBNull)

                        {

                            if ( !(ascending ^ nullsMin) )

                                f.Add(alias + srt[j].field + " IS NOT NULL");

                            else

                                break;

                        }

                        else

                        {

                            string sign = ascending ? ">" : "<";

                            string flt = BuildClause(alias + srt[j].field, sign);



                            if ( ascending ^ nullsMin )

                                f.Add(string.Format("( {0} OR {1})",

                                                    flt,

                                                    alias + srt[j].field + " IS NULL"));

                            else 

                                f.Add(flt);



                            pValues.Add(value);

                        }

                    }

                }



                if (f.Count > 0)

                    F.Add("(" + string.Join(" AND ", f.ToArray()) + ")");

            }



            string filter = F.Count > 0 ? "(" + string.Join(" OR ", F.ToArray()) + ")" : string.Empty;



            switch(filterType)

            {

                case PagerFilterType.Filter:

                    if (filterOrSelect != null)

                    {

                        if (filter.Length > 0)

                            filter += " AND ";

                        filter += "(" + filterOrSelect + ")";

                        if (listFilterParameters != null)

                            pValues.AddRange(listFilterParameters);

                    }

                    break;



                case PagerFilterType.Select:

                    if ( filter.Length == 0 )

                        filter = filterOrSelect.Replace("<PagerWhere>", "1=1");

                    else

                        filter = filterOrSelect.Replace("<PagerWhere>", filter);



                    if (listFilterParameters != null)

                        pValues.AddRange(listFilterParameters);

                    break;

            }





            values = pValues.ToArray();

            return filter;

        }



        private void ClearFirstLast()

        {

            firstEntity = null;

            lastEntity = null;

        }

        private void SetFirstLast(ICollection<T> c)

        {

            if ( c.Count > 0 )

            {

                List<T> al = new List<T>(c);

                lastEntity = al[al.Count - 1];

                firstEntity = al[0];

            }

            else

                ClearFirstLast();

        }



        private ICollection<T> Select(string filter, string sort, int count, object[] parameters)

        {

            if (session.DataProvider != null)

            {

                IDataProvider provider = session.DataProvider;

                DataSet selection;



                if ( filterType == PagerFilterType.Filter )

                {

                    string tableName = session.Pm.GetTypeTable(typeof(T));

                    selection = provider.Select(tableName, filter, sort, count, parameters);

                }

                else

                {

                    string tableName = session.Pm.GetTypeTable(typeof(T));

                    selection = ((ISqlDataProvider)provider).SelectSql(filter + " ORDER BY " + sort, tableName, count, parameters);

                }

                return session.Merge<T>(selection);

            }

            return

                session.Select<T>(filter, sort, count, parameters);

        }



        /// <summary>

        /// Selects first N objects.

        /// </summary>

        /// <param name="count">Number of objects to retrieve</param>

        /// <returns>First N objects.</returns>

        public ICollection<T> SelectFirst(int count)

        {

            ClearFirstLast();

            object[] values;

            string filter = GetFilter(true, out values);



            ICollection<T> c = Select(filter, ForwardSort, count, values);

            SetFirstLast(c);

            return c;

        }





        /// <summary>

        /// Selects next N objects. Can be called only after <see cref="SelectFirst"/>.

        /// </summary>

        /// <param name="count">Number of objects to retrieve.</param>

        /// <returns>Next N objects.</returns>

        public ICollection<T> SelectNext(int count)

        {

            if (lastEntity == null)

                return new T[0];

            object[] values;

            string filter = GetFilter(true, out values);



            ICollection<T> c = Select(filter, ForwardSort, count, values);

            SetFirstLast(c);

            return c;

        }

        

        /// <summary>

        /// Select last N objects.

        /// </summary>

        /// <param name="count">Number of object to retrieve.</param>

        /// <returns>Last N objects</returns>

        public ICollection<T> SelectLast(int count)

        {

            ClearFirstLast();

            object[] values;

            string filter = GetFilter(false, out values);



            List<T> al = new List<T>(Select(filter, BackwardSort, count, values));

            al.Reverse();

            SetFirstLast(al);

            return al.ToArray();

        }



        /// <summary>

        /// Selects N previous objects

        /// </summary>

        /// <param name="count">Number of object to select.</param>

        /// <returns>Previous N objects.</returns>

        public ICollection<T> SelectPrev(int count)

        {

            if (firstEntity == null)

                return new T[0];



            object[] values;

            string filter = GetFilter(false, out values);



            List<T> al = new List<T>(Select(filter, BackwardSort, count, values));

            al.Reverse();

            SetFirstLast(al);

            return al.ToArray();

        }



        /// <summary>Returns first page</summary>

        public ICollection<T> First { get { return SelectFirst(pageSize); } }



        /// <summary>Returns next page</summary>

        public ICollection<T> Next { get { return SelectNext(pageSize); } }



        /// <summary>Returns last page</summary>

        public ICollection<T> Last { get { return SelectLast(pageSize); } }



        /// <summary>Returns previous page</summary>

        public ICollection<T> Prev { get { return SelectPrev(pageSize); } }



        IEnumerator<T> IEnumerable<T>.GetEnumerator()

        {

            ICollection<T> first = First;

            foreach (T t in first)

                yield return t;

            ICollection<T> next;

            while((next = Next).Count > 0 )

                foreach (T t in next)

                    yield return t;

        }



        /// <summary>

        /// Gets an enumerator to interate through all the query result set

        /// reading data page by page.

        /// </summary>

        /// <returns>An iterator object, linked to the <see cref="Pager{T}"/> object.</returns>

        public IEnumerator GetEnumerator()

        {

            return ((IEnumerable<T>) this).GetEnumerator();

        }

    }

}