using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using Telerik.Web.UI;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using System.Text;

public partial class _Default : System.Web.UI.Page
{
    [WebMethod]
    public static int GetCount(List<GridFilterExpression> filterExpressions)
    {
        int count = 0;

        using (SqlConnection connection =
            new SqlConnection(
                ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
        {
            int counter = 0;
            List<DbParameter> parameters = new List<DbParameter>();

            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT COUNT(*) FROM Employees");

            if (filterExpressions.Count > 0)
                sqlBuilder.Append(" WHERE ");

            foreach (GridFilterExpression expression in filterExpressions)
            {
                counter++;
                Pair parameter = BuildParameter(expression);
                parameters.Add((DbParameter)parameter.Second);

                sqlBuilder.AppendFormat((string)parameter.First);
                if (counter < filterExpressions.Count)
                {
                    sqlBuilder.AppendFormat(" AND ");
                }
            }

            SqlCommand command = new SqlCommand(sqlBuilder.ToString(),
                    connection);

            parameters.ForEach(
                delegate(DbParameter parameter) { if (parameter != null) command.Parameters.Add(parameter); });

            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                count = reader.GetInt32(0);
            }

            reader.Close();

        }
        return count;
    }

    [WebMethod]
    public static List<Employee> GetData(int startIndex, int maximumRows,
        string sortExpressions, List<GridFilterExpression> filterExpressions)
    {

        List<Employee> list = new List<Employee>();

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.AppendLine("DECLARE @startRow int    ");
        sqlBuilder.AppendLine("SET ROWCOUNT @maximumRows");

        sqlBuilder.AppendLine("WITH OrderedEmployees As");
        sqlBuilder.AppendLine("(");

        if (!String.IsNullOrEmpty(sortExpressions))
        {
            string firstExpression = sortExpressions.Split(',')[0];
            sqlBuilder.AppendLine(String.Format("SELECT *, ROW_NUMBER() OVER (Order By {0} ) as RowNum FROM Employees ", firstExpression));
        }
        else
            sqlBuilder.AppendLine("SELECT *, ROW_NUMBER() OVER (Order By EmployeeID ASC) as RowNum FROM Employees ");

        int counter = 0;
        List<DbParameter> parameters = new List<DbParameter>();

        if (filterExpressions.Count > 0)
            sqlBuilder.Append(" WHERE ");

        foreach (GridFilterExpression expression in filterExpressions)
        {
            counter++;
            Pair parameter = BuildParameter(expression);
            parameters.Add((DbParameter)parameter.Second);

            sqlBuilder.AppendFormat((string)parameter.First);
            if (counter < filterExpressions.Count)
            {
                sqlBuilder.AppendFormat(" And ");
            }
        }

        sqlBuilder.AppendLine(")");

        sqlBuilder.AppendLine(" SELECT * FROM OrderedEmployees Where RowNum > @startRowIndex");

        if (!String.IsNullOrEmpty(sortExpressions))
        {
            sqlBuilder.AppendLine(String.Format(" Order By {0}", sortExpressions));
        }
        else
            sqlBuilder.AppendLine(" Order By EmployeeID ASC");


        using (SqlConnection connection =
            new SqlConnection(
                ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
        {
            SqlCommand command = new SqlCommand(sqlBuilder.ToString(), connection);

            command.Parameters.AddWithValue("@startRowIndex", startIndex);
            command.Parameters.AddWithValue("@maximumRows", maximumRows);

            parameters.ForEach(

                delegate(DbParameter parameter)
                {
                    if (parameter != null)
                        command.Parameters.Add(parameter);
                }
            );

            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    Employee newEmployee = new Employee();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        switch (reader.GetName(i))
                        {
                            case "EmployeeID":
                                newEmployee.EmployeeID = reader.GetInt32(i);
                                break;
                            case "LastName":
                                newEmployee.LastName = reader.GetString(i);
                                break;
                            case "FirstName":
                                newEmployee.FirstName = reader.GetString(i);
                                break;
                            case "Title":
                                newEmployee.Title = reader.GetString(i);
                                break;
                            case "TitleOfCourtesy":
                                newEmployee.TitleOfCourtesy = reader.GetString(i);
                                break;
                            case "BirthDate":
                                newEmployee.BirthDate = reader.GetDateTime(i);
                                break;
                            default:
                                break;
                        }
                    }

                    list.Add(newEmployee);
                }
            }
            finally
            {
                reader.Close();
            }
        }

        return list;
    }
    private static Pair BuildParameter(GridFilterExpression expression)
    {
        string fieldName = expression.FieldName.Trim().Split(' ')[0];
        GridKnownFunction filterFunction =
            (GridKnownFunction)Enum.Parse(typeof(GridKnownFunction), expression.FilterFunction);

        SqlParameter sqlParameter = null;

        string filterExpression = string.Empty;
        switch (filterFunction)
        {
            case GridKnownFunction.NoFilter:
                filterExpression = "1 = 1";
                break;
            case GridKnownFunction.Contains:
                filterExpression = string.Format("[{0}] LIKE @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     string.Format("%{0}%", Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName))));
                break;
            case GridKnownFunction.DoesNotContain:
                filterExpression = string.Format("[{0}] NOT LIKE @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     string.Format("%{0}%", Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName))));
                break;
            case GridKnownFunction.StartsWith:
                filterExpression = string.Format("[{0}] LIKE @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     string.Format("{0}%", Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName))));
                break;
            case GridKnownFunction.EndsWith:
                filterExpression = string.Format("[{0}] LIKE @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     string.Format("%{0}", Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName))));
                break;
            case GridKnownFunction.EqualTo:
                filterExpression = string.Format("[{0}] = @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                break;
            case GridKnownFunction.NotEqualTo:
                filterExpression = string.Format("[{0}] <> @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                break;
            case GridKnownFunction.GreaterThan:
                filterExpression = string.Format("[{0}] > @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                    Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                break;
            case GridKnownFunction.LessThan:
                filterExpression = string.Format("[{0}] < @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                break;
            case GridKnownFunction.GreaterThanOrEqualTo:
                filterExpression = string.Format("[{0}] >= @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                    Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                break;
            case GridKnownFunction.LessThanOrEqualTo:
                filterExpression = string.Format("[{0}] <= @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                break;
            case GridKnownFunction.Between:
                filterExpression = string.Format(" ([{0}] >= @{0}) AND ([{0}] <= @{0})", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                    Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                break;
            case GridKnownFunction.NotBetween:
                filterExpression = string.Format(" ([{0}] < @{0}) OR ([{0}] > @{0})", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                    Convert.ChangeType(expression.FieldValue, Type.GetType(expression.DataTypeName)));
                break;
            case GridKnownFunction.IsEmpty:
                filterExpression = string.Format(" [{0}] = @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     string.Empty);
                break;
            case GridKnownFunction.NotIsEmpty:
                filterExpression = string.Format(" [{0}] <> @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                      string.Empty);
                break;
            case GridKnownFunction.IsNull:
                filterExpression = string.Format(" [{0}] = @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     DBNull.Value);
                break;
            case GridKnownFunction.NotIsNull:
                filterExpression = string.Format(" [{0}] <> @{0}", fieldName);
                sqlParameter =
                    new SqlParameter(string.Format("@{0}", fieldName),
                                     DBNull.Value);
                break;
        }

        return new Pair(filterExpression,
                        sqlParameter);
    }

    public class Employee
    {
        private int? _EmployeeID;
        private string _LastName;
        private string _FirstName;
        private string _Title;
        private string _TitleOfCourtesy;
        private System.Nullable<System.DateTime> _BirthDate;

        public Employee()
        {

        }

        public int? EmployeeID
        {
            get
            {
                return null;
            }
            set
            {
                if ((this._EmployeeID != value))
                {
                    this._EmployeeID = null;
                }
            }
        }

        public string LastName
        {
            get
            {
                return this._LastName;
            }
            set
            {
                if ((this._LastName != value))
                {
                    this._LastName = value;
                }
            }
        }

        public string FirstName
        {
            get
            {
                return this._FirstName;
            }
            set
            {
                if ((this._FirstName != value))
                {
                    this._FirstName = value;
                }
            }
        }

        public string Title
        {
            get
            {
                return this._Title;
            }
            set
            {
                if ((this._Title != value))
                {
                    this._Title = value;
                }
            }
        }

        public string TitleOfCourtesy
        {
            get
            {
                return this._TitleOfCourtesy;
            }
            set
            {
                if ((this._TitleOfCourtesy != value))
                {
                    this._TitleOfCourtesy = value;
                }
            }
        }

        public System.Nullable<System.DateTime> BirthDate
        {
            get
            {
                return this._BirthDate;
            }
            set
            {
                if ((this._BirthDate != value))
                {
                    this._BirthDate = value;
                }
            }
        }
    }
}



