// $Id: PopulateRoadwaySegmentsFromDisplayLinks.cs,v 1.2 2013-01-03 21:04:52 jonesk Exp $
// Copyright TransCore 2012, All Rights Reserved
//
// PROPRIETARY
// THIS SOURCE CODE IS THE PROPERTY OF TRANSCORE. IT MAY BE USED BY RECIPIENT
// ONLY FOR THE PURPOSE FOR WHICH IT WAS TRANSMITTED AND MUST BE RETURNED UPON
// REQUEST OR WHEN NO LONGER NEEDED BY RECIPIENT. IT MAY NOT BE COPIED OR
// COMMUNICATED WITHOUT THE WRITTEN CONSENT OF TRANSCORE.
//
//-----------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Linq;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    /// <summary>
    /// Loads WebMap_RoadwaySegment and WebMap_RoadwaySegmentDetectorStation 
    /// using data in FMS_DetectorStation, FMS_ComponentConnect, and FMSDisplayLink
    /// </summary>
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void PopulateRoadwaySegmentsFromDisplayLinks()
    {
        using (SqlConnection sqlConnection = new SqlConnection("context connection=true"))
        {
            sqlConnection.Open();

            try
            {
                #region Delete any existing RoadwaySegments

                string sqlDelete = @"DELETE FROM WebMap_RoadwaySegmentDisplayLink " + 
                                    "DELETE FROM WebMap_RoadwaySegmentDetectorStation " +
                                    "DELETE FROM WebMap_RoadwaySegment";
                using (SqlTransaction deleteTransaction = sqlConnection.BeginTransaction("txnRoadwaySegmentDelete"))
                using (SqlCommand deleteCommand = new SqlCommand(sqlDelete, sqlConnection, deleteTransaction))
                {
                    deleteCommand.ExecuteNonQuery();
                    deleteTransaction.Commit();
                }

                #endregion

                List<List<SegmentDetectorStationData>> segmentsDsData = new List<List<SegmentDetectorStationData>>();
                #region Populate segmentsDsData from FMS_ComponentConnect and FMS_DetectorStation

                string sqlDsConnects = @"SELECT FMS_DetectorStation.IntId, RoadwayName, TmddLinkDirection, " +
                                        "FMS_DetectorStation.LinearReference, FMS_ComponentConnect.Distance " +
                                        "FROM FMS_DetectorStation " +
                                        "JOIN FMS_ComponentConnect ON FMS_DetectorStation.IntId = FMS_ComponentConnect.DSIntId " +
                                        "ORDER BY FMS_ComponentConnect.DSIntId, DSIntIdDownstream";
                using (SqlCommand sqlCommand = new SqlCommand(sqlDsConnects, sqlConnection))
                using (SqlDataReader sqlDsConnectsReader = sqlCommand.ExecuteReader())
                {
                    string lastRoadway = string.Empty;
                    int lastDirection = -1;
                    float lastLinearReference = -1;
                    List<SegmentDetectorStationData> thisSegmentData = new List<SegmentDetectorStationData>();
                    while (sqlDsConnectsReader.Read())
                    {
                        Object[] dsConnectValues = new Object[sqlDsConnectsReader.FieldCount];
                        sqlDsConnectsReader.GetValues(dsConnectValues);

                        int dsIntId = (int)dsConnectValues[0];

                        string roadwayName = dsConnectValues[1].ToString();
                        int tmddLinkDirection = int.Parse(dsConnectValues[2].ToString());
                        float linearReference = float.Parse(dsConnectValues[3].ToString());
                        float distance = float.Parse(dsConnectValues[4].ToString());

                        if (roadwayName != lastRoadway || tmddLinkDirection != lastDirection || linearReference < lastLinearReference)
                        {
                            if (thisSegmentData.Count > 0)
                            {
                                segmentsDsData.Add(thisSegmentData);
                            }

                            thisSegmentData = new List<SegmentDetectorStationData>();
                            thisSegmentData.Add(new SegmentDetectorStationData(dsIntId, distance));
                        }
                        else
                        {
                            thisSegmentData.Add(new SegmentDetectorStationData(dsIntId, distance));
                        }

                        lastRoadway = roadwayName;
                        lastDirection = tmddLinkDirection;
                        lastLinearReference = linearReference;
                    }

                    if (thisSegmentData.Count > 0)
                    {
                        segmentsDsData.Add(thisSegmentData);
                    }
                }

                #endregion

                string roadwaySegmentInsertValues = string.Empty;
                string roadwaySegmentDlInsertValues = string.Empty;
                string roadwaySegmentDsInsertValues = string.Empty;
                #region Build values portions of insert statements

                int segmentId = 0;
                foreach (List<SegmentDetectorStationData> thisSegmentData in segmentsDsData)
                {
                    bool segmentDisplayLinkFound = false;
                    int dlSequence = 0;

                    foreach (SegmentDetectorStationData segmentDsData in thisSegmentData)
                    {
                        int dsSequence = 0;
                        int dlIntId = -1;
                        int vertexCount = -1;
                        #region roadwaySegmentDsInsertValues = FMS_DisplayLinks.Where(DsIntId = dsIntId) with greatest amount of verticies

                        string sqlDisplayLinks = @"SELECT VertexData, FMS_DisplayLink.IntId " +
                                                 "FROM FMS_DisplayLinkDetStation " +
                                                 "JOIN FMS_DisplayLink ON FMS_DisplayLinkDetStation.DLIntId = FMS_DisplayLink.IntId " +
                                                 "WHERE DSIntId = " + segmentDsData.DsIntId;
                        using (SqlCommand sqlCommand2 = new SqlCommand(sqlDisplayLinks, sqlConnection))
                        using (SqlDataReader sqlDisplayLinksReader = sqlCommand2.ExecuteReader())
                        {
                            while (sqlDisplayLinksReader.Read())
                            {
                                Object[] dsDisplayLinkValues = new Object[sqlDisplayLinksReader.FieldCount];
                                sqlDisplayLinksReader.GetValues(dsDisplayLinkValues);

                                string vertexData = dsDisplayLinkValues[0].ToString();
                                int countDataSeperatorIdx = vertexData.IndexOf(' ');
                                int thisCount = int.Parse(vertexData.Substring(0, countDataSeperatorIdx));
                                if (thisCount > vertexCount)
                                {
                                    dlIntId = Int32.Parse(dsDisplayLinkValues[1].ToString());
                                    vertexCount = thisCount;
                                }
                            }
                        }

                        #endregion

                        if (vertexCount > 0)
                        {
                            segmentDisplayLinkFound = true;

                            if (roadwaySegmentDsInsertValues.Length > 0)
                            {
                                roadwaySegmentDsInsertValues += ",";
                            }
                            ++dsSequence;
                            roadwaySegmentDsInsertValues += string.Format("((SELECT IntId FROM WebMap_RoadwaySegment WHERE ExtId={0}),{1},{2},{3})",
                                                segmentId + 1, segmentDsData.DsIntId, segmentDsData.Distance, dsSequence);

                            if (roadwaySegmentDlInsertValues.Length > 0)
                            {
                                roadwaySegmentDlInsertValues += ",";
                            }
                            ++dlSequence;
                            roadwaySegmentDlInsertValues += string.Format("((SELECT IntId FROM WebMap_RoadwaySegment WHERE ExtId={0}),{1},{2})",
                                                segmentId + 1, dlIntId, dlSequence);
                        }
                    }

                    if (segmentDisplayLinkFound)
                    {
                        ++segmentId;
                        if (roadwaySegmentInsertValues.Length > 0)
                        {
                            roadwaySegmentInsertValues += ",";
                        }
                        roadwaySegmentInsertValues += string.Format("({0},'Roadway Segment {0}')", segmentId);
                    }
                }

                #endregion

                #region Insert all rows

                using (SqlTransaction insertTransaction = sqlConnection.BeginTransaction("txnRoadwaySegmentInsert"))
                {
                    string sqlInsert = @"INSERT INTO WebMap_RoadwaySegment (ExtId, Name) VALUES " + roadwaySegmentInsertValues + " ";
                    using (SqlCommand insertCommand = new SqlCommand(sqlInsert, sqlConnection, insertTransaction))
                    {
                        insertCommand.ExecuteNonQuery();
                    }

                    sqlInsert = @"INSERT INTO WebMap_RoadwaySegmentDisplayLink (RoadwaySegmentIntId, DLIntId, Sequence) VALUES " + roadwaySegmentDlInsertValues;
                    using (SqlCommand insertCommand = new SqlCommand(sqlInsert, sqlConnection, insertTransaction))
                    {
                        insertCommand.ExecuteNonQuery();
                    }

                    sqlInsert = @"INSERT INTO WebMap_RoadwaySegmentDetectorStation (RoadwaySegmentIntId, DsIntId, Distance, Sequence) VALUES " + roadwaySegmentDsInsertValues;
                    using (SqlCommand insertCommand = new SqlCommand(sqlInsert, sqlConnection, insertTransaction))
                    {
                        insertCommand.ExecuteNonQuery();
                    }

                    insertTransaction.Commit();
                }

                #endregion
            }
            catch (Exception ex)
            {
                SqlContext.Pipe.Send(ex.Message);
            }
            finally
            {
                sqlConnection.Close();
            }
        }
    }

    private class SegmentDetectorStationData
    {
        public SegmentDetectorStationData(int dsIntId, float distance)
        {
            DsIntId = dsIntId;
            Distance = distance;
        }

        public int DsIntId { get; private set; }
        public float Distance { get; private set; }
    }
};