/*
    Set-Operator
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace _05LINQBasics
{
    public class Customer
    {
        public string customerID;
        public string companyName;
        public string country;

        public Order[] orders;
    }

    public class Order
    {
        public int orderID;
        public DateTime orderDate;
        public string shipCountry;

        public int productID;
    }

    public class Product
    {
        public int productID;
        public double unitPrice;
        public string name;
    }

    public class Program
    {
        public static void Main(string[] args)
        {
            Product p1 = new Product { productID = 31, unitPrice = 12.5, name = "Gorgonzola Telino" };
            Product p2 = new Product { productID = 52, unitPrice = 7.0, name = "Filo Mix" };
            Product p3 = new Product { productID = 63, unitPrice = 43.9, name = "Vegie-spread" };
            Product p4 = new Product { productID = 48, unitPrice = 12.7, name = "Chocolade" };
            Product p5 = new Product { productID = 43, unitPrice = 46.0, name = "Ipoh Coffee" };
            Product p6 = new Product { productID = 37, unitPrice = 26.0, name = "Gravad lax" };

            Product p7 = new Product { productID = 9999, unitPrice = 146.45, name = "new Product" };

            Product[] products = new Product[] { p1, p2, p3, p4, p5, p6, p7 };

            //Orders
            Order[] orderALFKI = new Order[]
                {
                    new Order {orderID = 10643, orderDate = DateTime.Parse("25.08.1997"), shipCountry = "Germany", productID = 31},
                    new Order {orderID = 10692, orderDate = DateTime.Parse("03.10.1997"), shipCountry = "Germany", productID = 63},
                    new Order {orderID = 10702, orderDate = DateTime.Parse("13.10.1997"), shipCountry = "Germany", productID = 48},

                };

            Order[] orderBERGS = new Order[]
                {
                    new Order {orderID = 10278, orderDate = DateTime.Parse("12.08.1996"), shipCountry = "Sweden", productID = 48},
                    new Order {orderID = 10280, orderDate = DateTime.Parse("14.08.1996"), shipCountry = "Sweden", productID = 52},
                    new Order {orderID = 10384, orderDate = DateTime.Parse("16.12.1996"), shipCountry = "Sweden", productID = 48},

                };

            Order[] orderDUMON = new Order[]
                {
                    new Order {orderID = 10311, orderDate = DateTime.Parse("20.09.1996"), shipCountry = "France", productID = 37},
                    new Order {orderID = 10609, orderDate = DateTime.Parse("24.07.1997"), shipCountry = "France", productID = 31},
                    new Order {orderID = 10683, orderDate = DateTime.Parse("26.09.1997"), shipCountry = "France", productID = 43},
                };

            Order[] orderLEHMS = new Order[]
                {
                    new Order {orderID = 10279, orderDate = DateTime.Parse("13.08.1996"), shipCountry = "Germany", productID = 37},
                    new Order {orderID = 10284, orderDate = DateTime.Parse("19.08.1996"), shipCountry = "Germany", productID = 43},
                    new Order {orderID = 10343, orderDate = DateTime.Parse("31.10.1996"), shipCountry = "Germany", productID = 43},
                };

            //Customers
            Customer[] customers = new Customer[]
                {
                    new Customer {customerID = "ALFKI", companyName = "Alfreds Futterkiste", country = "Germany", orders = orderALFKI},
                    new Customer {customerID = "BERGS", companyName = "Berglunds snabbköp", country = "Sweden", orders = orderBERGS},
                    new Customer {customerID = "DUMON", companyName = "Du monde entier", country = "France", orders = orderDUMON},
                    new Customer {customerID = "LEHMS", companyName = "Lehmanns Marktstand", country = "Germany", orders = orderLEHMS}
                };

            //Codefragment//
            #region einfache Count-Abfrage
            {
                var result =
                from c in customers
                select new { c.customerID, c.companyName, numberOfOrders = c.orders.Count() };

                foreach (var item in result)
                {
                    Console.WriteLine(item.customerID + " " + item.companyName + " " + item.numberOfOrders);
                }
            }
            #endregion

            Console.WriteLine();

            #region Count-Abfrage mit Join-Operator
            {
                var result2 =
                from p in products
                join o in
                    (
                       from c in customers
                       from o in c.orders
                       select o
                    )
                on p.productID equals o.productID into ox
                select new { p.name, p.productID, Orders = ox, numberOfOrders = ox.Count() };

                foreach (var item in result2)
                {
                    Console.WriteLine(item.productID + " " + item.name + " " + item.numberOfOrders);

                    foreach (var item2 in item.Orders)
                    {
                        Console.WriteLine("\t" + item2.orderID);
                    }
                }
            }
            #endregion

            Console.WriteLine();

            #region Sum-Operator
            {
                var result3 =
                from p in products
                join o in
                    (
                       from c in customers
                       from o in c.orders
                       select o
                    )
                on p.productID equals o.productID into ox
                select new { p.name, p.productID, sumOfProducts = ox.Sum(sum => p.unitPrice), Orders = ox, numberOfOrders = ox.Count() };

                foreach (var item in result3)
                {
                    Console.WriteLine(item.productID + " " + item.name + " " + item.numberOfOrders + " " + item.sumOfProducts);

                    foreach (var item2 in item.Orders)
                    {
                        Console.WriteLine("\t" + item2.orderID);
                    }
                }
            }
            #endregion

            Console.WriteLine();

            #region Min-Max-Operator
            {
                var result4 =
                 from p in products
                 select p.unitPrice;

                Console.WriteLine(result4.Min());


                var result5 =
                    from p in products
                    select p.unitPrice;

                Console.WriteLine(result5.Max());
            }
            #endregion

            Console.WriteLine();

            #region Average
            {
                var result6 =
                     from p in products
                     select new { p.unitPrice };

                Console.WriteLine(result6.Average(p => p.unitPrice));
            }
            #endregion

            //Codefragment//

            Console.ReadLine();
        }
    }
}