/// <summary>
        /// Check the drawings that are flagged as alignment sheets
        /// Create the raw png for georef point location
        /// if the sheet is ready, perform the georeferencing process
        /// </summary>
        public static void generateAlignmentPNG()
        {
            /*Avoid a lengthy open datbase connection by loading all the records into
             * a list of AlignmentSheetRecord objects
             * These have the appropriate methods to complete the png generation
             * and georeferencing tasks */
            List<AlignmentSheetRecord> alignSheets = new List<AlignmentSheetRecord>();

            using (SqlConnection conn = new SqlConnection(AppConstants.CONN_STRING_SUPSQL_ARCGIS))
            {
                //Join the drawings table with the drawingslineloopref table that holds values common to all aligmnent sheets for the route
                SqlCommand comm = conn.CreateCommand();
                comm.CommandText = "SELECT draw.LineLoopName, draw.DrawingID, draw.Extension, draw.NeedsUpdate, ";
                comm.CommandText += "linereftable.RghtBorLeft, linereftable.RghtBorTop, linereftable.RghtBorRght, linereftable.RghtBorBot,  ";
                comm.CommandText += "linereftable.DetLeft, linereftable.DetTop, linereftable.DetRght, linereftable.DetBot, ";
                comm.CommandText += "linereftable.TrrnLeft, linereftable.TrrnTop, linereftable.TrrnRght, linereftable.TrrnBot, ";
                comm.CommandText += "linereftable.PlanTop, linereftable.PlanBot, ";
                comm.CommandText += "linereftable.LineLoopID, linereftable.TrimBorder, ";
                comm.CommandText += "draw.MatchStartPixX, draw.MatchStartPixY, draw.MatchEndPixX, draw.MatchEndPixY, ";
                comm.CommandText += "draw.MatchStartStn, draw.MatchEndStn, draw.MP_KP_Start, draw.MP_KP_End ";
                comm.CommandText += "FROM drawings AS draw  ";
                comm.CommandText += "INNER JOIN DRAWINGSLINELOOPREF AS linereftable ";
                comm.CommandText += "ON draw.LineLoopName = linereftable.LineLoopName ";
                comm.CommandText += "WHERE draw.LineLoopName IS NOT NULL; ";

                try
                {
                    conn.Open();
                    SqlDataReader reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        alignSheets.Add(new AlignmentSheetRecord(reader, true));
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    comm.Dispose();
                    conn.Close();
                }
            }

            /*processHelper object contains persistent reference to arcobjects
             * Avoids repeatedly creating new arcobjects */
            using (DrawingProcessorHelper processHelper = new DrawingProcessorHelper())
            {
                for (int i = 0; i < alignSheets.Count; i++)
                {
                    AlignmentSheetRecord theSheet = alignSheets[i];
                    Console.WriteLine(theSheet.DrawingID);
                    Console.WriteLine("Update Flag " + theSheet.NeedsUpdate);
                    if (theSheet.NeedsUpdate == 0)
                        continue;

                    //Make the directories to hold the pieces of the alignment sheet
                    theSheet.makeDirectories();
                    //if the update flag is set to 1 (new record) make the raw png
                    if (theSheet.NeedsUpdate == 1)
                    {
                        theSheet.generateRawPng();
                        //set update flag to 2, awaiting georeferencing
                        theSheet.setNeedsUpdate(2);
                        Console.WriteLine("Raw PNG Made");

                    }

                    //sheetReady property derived from the present of georeferencing point values
                    //skip to next sheet if not ready
                    if (!theSheet.sheetReady)
                        continue;

                    //crop out the pieces of the drawing
                    theSheet.splitDrawing();
                    Console.WriteLine("Drawing Split");
                    //Geoference the drawing based on input points, note the processHelper passed as a parameter
                    theSheet.georefDrawing(processHelper);
                    Console.WriteLine("Geo Ref");
                    //Make the footprint
                    theSheet.makeFootprint();
                    Console.WriteLine("Make Footprint");
                    //Set the update flag to 0, georeferenced and finished
                    theSheet.setNeedsUpdate(0);
                    Console.WriteLine("Set needs update to false");
                }
            }
        }
        /// <summary>
        /// Georeference the plan
        /// </summary>
        /// <param name="processHelper">reference to persistant arcobjects</param>
        public void georefDrawing(DrawingProcessorHelper processHelper)
        {
            //Locate the start and end match points by stationing
            Enbridge.LinearReferencing.ContLineLocatorSQL locator = new Enbridge.LinearReferencing.ContLineLocatorSQL(this.LLId);
            double startX, startY, startZ, endX, endY, endZ, meas;
            locator.getMPFromStn(this.MatchStartStn, out meas, out startX, out startY, out startZ);
            locator.getMPFromStn(this.MatchEndStn, out meas, out endX, out endY, out endZ);

            //convert long lat coordinates to web mercator
            double startMercX, startMercY, endMercX, endMercY;
            Enbridge.Utilities.ProjectionConversion.toWebMercator(startX, startY, out startMercX, out startMercY);
            Enbridge.Utilities.ProjectionConversion.toWebMercator(endX, endY, out endMercX, out endMercY);

            //dictionary to hold the mercator xy values
            mercCoords = new Dictionary<string, double>() { { "startMercX", startMercX }, { "startMercY", startMercY }, { "endMercX", endMercX }, { "endMercY", endMercY } };

            //define the projection of the plan to 3857
            processHelper.defineProj.in_dataset = pngPlanPath;
            processHelper.gp.Execute(processHelper.defineProj, null);

            //open the raster workspace
            processHelper.rastWorkspace =
                (ESRI.ArcGIS.DataSourcesRaster.IRasterWorkspace)processHelper.workspaceFact.OpenFromFile(Path.GetDirectoryName(pngPlanPath), 0);

            //open the raster dataset
            processHelper.rasDataset = processHelper.rastWorkspace.OpenRasterDataset(Path.GetFileName(pngPlanPath));
            //open the raster data
            processHelper.rast = processHelper.rasDataset.CreateDefaultRaster();

            //Set location of image source points
            ESRI.ArcGIS.Geometry.IPoint sourcePoint1 = new ESRI.ArcGIS.Geometry.Point();
            sourcePoint1.X = planCoords["X1"];
            sourcePoint1.Y = planCoords["Y1"];
            processHelper.sourcePoints.UpdatePoint(0, sourcePoint1);
            ESRI.ArcGIS.Geometry.IPoint sourcePoint2 = new ESRI.ArcGIS.Geometry.Point();
            sourcePoint2.X = planCoords["X2"];
            sourcePoint2.Y = planCoords["Y2"];
            processHelper.sourcePoints.UpdatePoint(1, sourcePoint2);

            //Set location of target points, that of the stationing match locations
            ESRI.ArcGIS.Geometry.IPoint targetPoint1 = new ESRI.ArcGIS.Geometry.Point();
            targetPoint1.X = mercCoords["startMercX"];
            targetPoint1.Y = mercCoords["startMercY"];
            processHelper.targetPoints.UpdatePoint(0, targetPoint1);
            ESRI.ArcGIS.Geometry.IPoint targetPoint2 = new ESRI.ArcGIS.Geometry.Point();
            targetPoint2.X = mercCoords["endMercX"];
            targetPoint2.Y = mercCoords["endMercY"];
            processHelper.targetPoints.UpdatePoint(1, targetPoint2);

            //complete the georeferencing
            processHelper.rasterPropc.TwoPointsAdjust(processHelper.sourcePoints, processHelper.targetPoints, processHelper.rast);
            //save the georeferencing to disk
            processHelper.rasterPropc.Register(processHelper.rast);
        }