using System;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace Dotmyself
{
    /// <summary>
    /// Generate and transform an XML document using LINQ to XML.
    /// Author : Hyacinthe MENIET
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            string ConnectStr = ConfigurationManager.ConnectionStrings["TransformationConnectionString"].ConnectionString;
            TransformationDataContext context = new TransformationDataContext(ConnectStr);

            // Querying the SQL Server Transformation Database to produce 
            // an XML document from a LINQ to SQL query

            Console.WriteLine("Generating the XML document");
            XElement xml = new XElement("peoples",
                from p in context.People
                select new XElement("people",
                new XElement("id", p.id),
                new XElement("firstname", p.firstname),
                new XElement("name", p.name),
                new XElement("role", p.Role.label)));
            xml.Save(@"C:\DOTNET_WORKSPACE\repos\Output\Transformation.xml");

            // Transforming the XML Document into HTML Code

            Console.WriteLine("Generating the HTML document");
            XElement html = new XElement("html",
                new XElement("body",
                new XElement("table",
                new XElement("tr",
                new XElement("th", "Id"),
                new XElement("th", "Full Name"),
                new XElement("th", "Role")),
                from p in xml.Descendants("people")
                select new XElement("tr",
                    new XElement("td", p.Element("id").Value),
                    new XElement("td", p.Element("firstname").Value
                    + " " + p.Element("name").Value),
                    new XElement("td", p.Element("role").Value)))));
            html.Save(@"C:\DOTNET_WORKSPACE\repos\Output\Transformation.html");

            // Fetching the XML Document with the comma delimiter

            Console.WriteLine("Generating the CSV document");
            string csv = (from p in xml.Descendants("people")
                          select String.Format("{0}, {1}, {2}, {3} {4}",
                          (string)p.Element("id"),
                          (string)p.Element("firstname"),
                          (string)p.Element("name"),
                          (string)p.Element("role"),
                          Environment.NewLine)
                          ).Aggregate(
                            new StringBuilder(),
                                (sb, s) => sb.Append(s),
                                sb => sb.ToString()
                            );
            File.WriteAllText(@"C:\DOTNET_WORKSPACE\repos\Output\Transformation.csv", csv);

            Console.ReadLine();

        }
    }
}