using NorthwindLibrary;
using System.Drawing;
using System.Linq;

namespace HowTo_ExcelWithEF
{
    public partial class ThisWorkbook
    {
        private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
            using(NorthwindEntities context=new NorthwindEntities())
            {
                #region Entity View içeriklerinin çekilmesi

                var invoices = from i in context.Invoices
                               orderby i.CustomerName
                               select new
                               {
                                   From = i.Country + "," + i.City,
                                   i.CustomerID,
                                   i.CustomerName,
                                   i.OrderDate,
                                   ShipTo = i.ShipCountry + "," + i.ShipCity
                               };

                var prdoductSalesFor1997 = from s in context.Product_Sales_for_1997
                                           orderby s.ProductSales descending
                                           ,s.CategoryName ascending
                                           ,s.ProductName ascending
                                           select new
                                           {
                                               Product=s.CategoryName+"-"+s.ProductName,
                                               s.ProductSales
                                           };

                #endregion

                #region Sheet2 nin Invoices içeriği ile doldurulması

                int rowIndex = 1;

                Sheets["sheet2"].Cells[rowIndex, 1] = "From";
                Sheets["sheet2"].Cells[rowIndex, 1].Font.Bold = true;
                Sheets["sheet2"].Cells[rowIndex, 2] = "Customer ID";
                Sheets["sheet2"].Cells[rowIndex, 2].Font.Bold = true;
                Sheets["sheet2"].Cells[rowIndex, 3] = "Customer";
                Sheets["sheet2"].Cells[rowIndex, 3].Font.Bold = true;
                Sheets["sheet2"].Cells[rowIndex, 4] = "Order Date";
                Sheets["sheet2"].Cells[rowIndex, 4].Font.Bold = true;
                Sheets["sheet2"].Cells[rowIndex, 5] = "Ship To";
                Sheets["sheet2"].Cells[rowIndex, 5].Font.Bold = true;

                foreach (var invoice in invoices)
                {
                    rowIndex++;
                    Sheets["sheet2"].Cells[rowIndex, 1] = invoice.From;
                    Sheets["sheet2"].Cells[rowIndex, 2] = invoice.CustomerID;
                    Sheets["sheet2"].Cells[rowIndex, 3] = invoice.CustomerName;
                    Sheets["sheet2"].Cells[rowIndex, 4] = invoice.OrderDate;
                    Sheets["sheet2"].Cells[rowIndex, 5] = invoice.ShipTo;
                }

                #endregion Sheet2 nin Invoices içeriği ile doldurulması

                #region Sheet3 ün 1997 yılına ait satış verileri ile doldurulması

                rowIndex = 1;

                Sheets["sheet3"].Cells[rowIndex, 1] = "Product";
                Sheets["sheet3"].Cells[rowIndex, 1].Font.Bold = true;
                Sheets["sheet3"].Cells[rowIndex, 2] = "Sales";
                Sheets["sheet3"].Cells[rowIndex, 2].Font.Bold = true;

                foreach (var sales in prdoductSalesFor1997)
                {
                    rowIndex++;
                    Sheets["sheet3"].Cells[rowIndex, 1] = sales.Product;
                    if (sales.ProductSales > 30000)
                    {
                        Sheets["sheet3"].Cells[rowIndex, 2].Interior.Color = Color.Black;
                        Sheets["sheet3"].Cells[rowIndex, 1].Interior.Color = Color.Black;
                        Sheets["sheet3"].Cells[rowIndex, 2].Font.Color = Color.Gold;
                        Sheets["sheet3"].Cells[rowIndex, 1].Font.Color = Color.Gold;
                    }

                    Sheets["sheet3"].Cells[rowIndex, 2] = sales.ProductSales;
                }

                #endregion Sheet3 ün 1997 yılına ait satış verileri ile doldurulması
            }         
        }

        private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisWorkbook_Startup);
            this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown);
        }

        #endregion

    }
}
