using System;
using Model;
using Telerik.OpenAccess;
using Telerik.OpenAccess.Query;

namespace _1_UsingSQL
{
  class Program
  {
    static void Main(string[] args)
    {
      using (IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope())
      {
        Console.WriteLine(scope.Database.ConnectionURL);
        Console.WriteLine("===========================");
        RunNativeSQL(scope);
        Console.WriteLine("===========================");
        RunOQL(scope);
        Console.WriteLine("===========================");
        RunLINQ(scope);
        Console.WriteLine("===========================");
        Console.WriteLine("Press any key...");
        Console.ReadKey();
      }
    }

    private static void RunLINQ(IObjectScope scope)
    {
      Console.WriteLine("Using Extent()");
      var result = scope.Extent<Employee>();
      foreach (Employee employee in result)
      {
        Console.WriteLine(employee.LastName);
      }
      Console.WriteLine(result.BackendQuery);

      Console.WriteLine("Using LINQ methods");
      var employeeResult = scope.Extent<Employee>();
      var employeeLastYear =
        employeeResult.Where(emp => emp.HireDate.Value.Year == 1993);
      long max = employeeLastYear.Max(emp => emp.EmployeeID);
      Console.WriteLine(max);

      Console.WriteLine("Using LINQ expressions");
      var expressionResult =
        (from emp in (scope.Extent<Employee>())
         where emp.HireDate.Value.Year == 1993
         select emp).Max(emp => emp.EmployeeID);
      Console.WriteLine(expressionResult);

      Console.WriteLine("Sorting with LINQ");
      var sortedResult =
        from emp in (scope.Extent<Employee>())
        orderby emp.LastName descending, emp.FirstName ascending
        select emp;
      foreach (Employee employee in sortedResult)
      {
        Console.WriteLine(employee.LastName);
      }

      Console.WriteLine("Joins with LINQ");
      var query =
        from p in scope.Extent<Product>()
        select new
        {
          Product = p.ProductName,
          Category = p.Category.CategoryName
        };

      //var query =
      //  from c in scope.Extent<Category>()
      //  join p in scope.Extent<Product>()
      //  on c equals p.Category
      //  select new { Product = p.ProductName, Category = c.CategoryName };

      //var query =
      //  from c in scope.Extent<Category>()
      //  join p in scope.Extent<Product>()
      //  on c.CategoryID equals p.CategoryID
      //  select new { Product = p.ProductName, Category = c.CategoryName };


      foreach (var pc in query)
      {
        Console.WriteLine(pc.Product + " - " + pc.Category);
      }

      Console.WriteLine("String methods");
      var stringResult = from cust in scope.Extent<Customer>()
                         where
                           // tests for equality
                             cust.CompanyName == "Island Trading"
                           // finds Great Lakes Food Market,…
                             || cust.CompanyName.StartsWith("Great")
                           // finds The Big Cheese,…
                             || cust.CompanyName.EndsWith("Cheese")
                           // finds The Cracker Box,…
                             || cust.CompanyName.Contains("rac")
                           // finds Wilman Kala using wildcards,…   
                             || cust.CompanyName.Matches("*lma?")
                         select cust;
      foreach (var customer in stringResult)
      {
        Console.WriteLine(customer.CompanyName);
      }
    }

    private static void RunOQL(IObjectScope scope)
    {
      Console.WriteLine("All employees");
      string sql = "select * FROM EmployeeExtent";
      var result = scope.GetOqlQuery(sql).Execute();
      foreach (Employee emp in result)
      {
        Console.WriteLine(emp.Country);
      }

      Console.WriteLine("EmployeeID = 1, Country = USA");
      string sqlWithParams = "select emp FROM EmployeeExtent AS emp " +
        "WHERE emp.EmployeeID = $1 AND emp.Country= $2";
      var result2 = scope.GetOqlQuery(sqlWithParams).Execute(1, "USA");
      foreach (Employee emp in result2)
      {
        Console.WriteLine(emp.Country);
      }

      Console.WriteLine("Employees with last name starting with D");
      string sqlWithWildCard = "select emp FROM EmployeeExtent AS emp " +
        "WHERE emp.LastName LIKE $1";
      var result3 = scope.GetOqlQuery(sqlWithWildCard).Execute("D*");
      foreach (Employee emp in result3)
      {
        Console.WriteLine(emp.LastName);
      }

      Console.WriteLine("Customer/Order/Product Join");
      string sqlWithJoin = "SELECT c.CustomerID, o.OrderID, od.ProductID " +
        "FROM CustomerExtent as c, c.Orders as o, o.OrderDetails as od " +
        "WHERE o.OrderID == 10285";
      var result4 = scope.GetOqlQuery(sqlWithJoin).Execute();
      const string format = "Customer: {0} Order: {1} Product: {2}";
      foreach (object[] obj in result4)
      {
        Console.WriteLine(String.Format(format, obj[0], obj[1], obj[2]));
      }

      Console.WriteLine("Update");
      const string sqlForUpdate =
        "Select * from OrderExtent Where OrderID = 10248";

      scope.Transaction.Begin();

      //retrieve an object using its id 
      Order order = (Order)scope.GetOqlQuery(sqlForUpdate).Execute()[0];

      //make some modifications to the object 
      order.Freight++;

      //store the modified object 
      scope.Transaction.Commit();
    }

    private static void RunNativeSQL(IObjectScope scope)
    {

      Query<Category> query =
      scope.GetSqlQuery<Category>(
        "select * from categories where CategoryID = ?",
        "integer CategoryIDParameter");

      QueryResultList<Category> result =
        query.ExecuteList(new object[] { 8 });
      foreach (Category category in result)
      {
        Console.WriteLine(category.CategoryName);
      }

      string sql =
        "select emp.BirthDate, emp.EmployeeID " +
        "from Employees as emp where emp.BirthDate > ?";
      IQuery dateQuery =
        scope.GetSqlQuery(sql, null, "timestamp myDate");
      var employeeResult = dateQuery.Execute(DateTime.Parse("1/1/1960"));
      foreach (object[] obj in employeeResult)
      {
        // Prints the Birth Date since it was selected first in the query 
        Console.WriteLine(obj[0]);
        // Prints the Employee ID 
        Console.WriteLine(obj[1]);
      }

    }
  }
}
