using HtmlAgilityPack;

using System;

using System.IO;

using System.Linq;

using System.Net;

using System.Text;

​

namespace WebScraping

{

    public class Program

    {

        public static void Main(string[] args)

        {

            HtmlDocument htmlDoc = new HtmlDocument();

            string url = "https://www.procyclingstats.com/race/tour-de-france/2022/stage-1";

            var stage = url.Substring(url.Length - 2) ;

            StringBuilder sb = new StringBuilder();

            string urlResponse = URLRequest(url);

​

            //Convert the Raw HTML into an HTML Object

            htmlDoc.LoadHtml(urlResponse);                

​

            var resultRiders = htmlDoc.DocumentNode.SelectNodes("//div[contains(@class, 'result-cont')]").FirstOrDefault();

            if (resultRiders != null)

            {   

                //Find all A rider tags in the document

                var riderAnchors = resultRiders.SelectNodes("//a").Where(a => a.ParentNode.Name == "div" && a.Attributes.Any(att => att.Name == "href" && att.Value.StartsWith("rider")));                  

​

                if (riderAnchors != null && riderAnchors.Any() == true)

                {       

                    int i = 0;

                    sb.AppendFormat("Declare @StageId int = {0};\n\r",stage);

                    sb.AppendLine("Insert Into RawData.StageResult(StageId,Position,Name)");

                    sb.AppendLine("Values");

                    foreach (var anchorNode in riderAnchors)

                    {

                        //Console.WriteLine(String.Format("{0} - {1}", anchorNode.ParentNode.Name, anchorNode.Attributes.First(att => att.Name == "href").Value) );

                        sb.AppendLine(String.Format("(@StageId, {1},  '{0}'  )", anchorNode.InnerText,++i));

                        if (i == 20)

                            break;

                        else

                            sb.Append(",");

                    }

                    sb.AppendFormat(_template,stage);

                }

                else

                    Console.WriteLine("GEEN RESULTS");

                Console.WriteLine(sb);

            }

        }

​

        //General Function to request data from a Server

        static string URLRequest(string url)

        {

            // Prepare the Request

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

​

            // Set method to GET to retrieve data

            request.Method = "GET";

            request.Timeout = 6000; //60 second timeout

            request.UserAgent = "Mozilla/5.0 (compatible; MSIE 9.0; Windows Phone OS 7.5; Trident/5.0; IEMobile/9.0)";

​

            string responseContent = null;

​

            // Get the Response

            using (WebResponse response = request.GetResponse())

            {

                // Retrieve a handle to the Stream

                using (Stream stream = response.GetResponseStream())

                {

                    // Begin reading the Stream

                    using (StreamReader streamreader = new StreamReader(stream))

                    {

                        // Read the Response Stream to the end

                        responseContent = streamreader.ReadToEnd();

                    }

                }

            }

​

            return (responseContent);

        }

        static string _template = @"

Select *

From RawData.StageResult rsr

Join Cyclist c

    on c.Name = rsr.Name

where rsr.StageId = {0}

order by position asc

​

Insert Into [dbo].[StageResult]( StageId, Position, CyclistID)

Select rsr.StageId,rsr.Position,c.CyclistID 

From RawData.StageResult rsr

Join Cyclist c

    on c.Name = rsr.Name

where rsr.StageId = {0}

order by position asc

​

​

​

Declare @StageId int = {0};

Insert Into ParticipantStageResult(StageId,ParticipantID,PreviousPoints,StagePoints,TotalPoints)

Select --@StageId, pc.ParticipantID, psr.TotalPoints, (isnull(21-sr.Position,0)), psr.TotalPoints + (isnull(21-sr.Position,0))

@StageId, pc.ParticipantID, psr.TotalPoints, SUM(isnull(21-sr.Position,0)), psr.TotalPoints + SUM(isnull(21-sr.Position,0))

From ParticipantCyclist pc

Join ParticipantStageResult psr

    on psr.StageId = @StageId -1

    and psr.ParticipantID = pc.ParticipantID

Left Join StageResult sr

    on sr.StageId = @StageId

    and pc.CyclistID = sr.CyclistID

Group by

    pc.ParticipantID,psr.StageId, psr.TotalPoints

​

-- Uitslag punten per Renner

Select sr.Position as Positie, c.Name as Naam, 21-sr.Position as Punten

From StageResult sr

Join Cyclist c

    on c.CyclistID = sr.CyclistID

Where sr.StageID = {0}

order by sr.Position asc

​

-- Uitslag punten per Renner, per gekozen renner

Select 

    sr.Position as Positie, 

    c.Name as Naam, 

    21-sr.Position as Punten,

    ISNULL(LEFT(names.name, Len(names.name) - 1),'') as Deelnemers

From StageResult sr

Join Cyclist c

    on c.CyclistID = sr.CyclistID

outer apply 

(

    SELECT CONVERT(VARCHAR(100), p.Name) + ',' AS [text()]

    FROM   ParticipantCyclist pc WITH(readuncommitted)

    Join Participant p on p.ParticipantID = pc.ParticipantID

    WHERE pc.CyclistId = c.CyclistId

    GROUP  BY pc.CyclistId,p.Name

    Order By Name

    FOR xml path('')

) names (name)

Where sr.StageID = {0}

order by sr.Position asc

​

​

Select p.Name as Naam, psr.PreviousPoints as SubTotaal, psr.StagePoints as Etappe, psr.TotalPoints as Tussenstand

From ParticipantStageResult psr

Join Participant p

    on p.ParticipantID = psr.ParticipantID

Where StageID = {0}

Order by TotalPoints asc, Naam asc";

    }

}

​