using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using OfficeOpenXml;
using NLog;

namespace Ingeneo.Console.Geo
{
    class Program
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        private static string sourceJson = ConfigurationManager.AppSettings.Get("Path.SourceJson");
        private static string DestinationJson = ConfigurationManager.AppSettings.Get("Path.DestinationJson");
        private static string sourceCSV = ConfigurationManager.AppSettings.Get("Path.SourceCSV");
        private static string destinationCVS = ConfigurationManager.AppSettings.Get("Path.DestinationCVS");
        private static string ResultCsv = ConfigurationManager.AppSettings.Get("Path.ResultCsv");

        static void Main(string[] args)
        {
            logger.Info("Start Process");
            ProcessConvertAddress2Coordinates3();
            //Csv2Json(sourceCSV, sourceJson);
            //Csv2Json(destinationCVS, DestinationJson);

            //ProcessConvertAddress2Coordinates(sourceJson);
            //ProcessConvertAddress2Coordinates(DestinationJson);

            //ProcessDistAddress(sourceJson, DestinationJson);

            //Json2Csv(sourceJson, sourceCSV);
            //Json2Csv(DestinationJson, destinationCVS);
            logger.Info("End Process");
        }

        static void ProcessConvertAddress2Coordinates2(string[] args)
        {
            // Create a request for the URL.   
            string apiKey = ConfigurationManager.AppSettings.Get("Google.API.Key");
            #region Leer Excel
            List<string> direcciones = new List<string>();
            string pathExcel = ConfigurationManager.AppSettings.Get("Path.Excel");
            // path to your excel file
            FileInfo fileInfo = new FileInfo(pathExcel);
            ExcelPackage package = new ExcelPackage(fileInfo);
            ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
            // get number of rows and columns in the sheet
            int rows = worksheet.Dimension.Rows;
            int columns = worksheet.Dimension.Columns;
            string cadena_excel = "";
            // loop through the worksheet rows and columns  (in this case the info start in second row):
            for (int i = 2; i <= rows; i++)
            {
                for (int j = 1; j <= columns; j++)
                {
                    if (worksheet.Cells[i, j].Value == null)
                    {
                        break;
                    }
                    else
                    {
                        cadena_excel += worksheet.Cells[i, j].Value.ToString();
                        cadena_excel += "+";
                    }
                    //System.Console.WriteLine(cadena_excel);
                    cadena_excel = formatAddress(cadena_excel);
                }
                System.Console.WriteLine(cadena_excel);

                direcciones.Add(cadena_excel);
                cadena_excel = "";
            }

            #endregion
            // Indica en qué fila y columna comienza a escribir los datos
            int col_number = 4;
            int row_number = 2;

            float latitud;
            float longitud;
            string direccionFormateada;

            foreach (string item in direcciones)
            {

                var location = GetGeo(item, apiKey);

                #region Escribir Excel

                if (location.results.Count == 0)
                {
                    //Nothing
                    latitud = 0;
                    longitud = 0;
                    direccionFormateada = "No data found";

                }
                else
                {
                    latitud = location.results[0].geometry.location.lat;
                    longitud = location.results[0].geometry.location.lng;
                    direccionFormateada = location.results[0].formatted_address;
                }

                //create a fileinfo object of an excel file on the disk
                FileInfo file_1 = new FileInfo(pathExcel);

                //create a new Excel package from the file
                using (ExcelPackage excelPackage = new ExcelPackage(file_1))
                {
                    //create an instance of the the first sheet in the loaded file
                    ExcelWorksheet worksheet_1 = excelPackage.Workbook.Worksheets[1];

                    //add some data
                    worksheet_1.Cells[row_number, col_number].Value = latitud;
                    worksheet_1.Cells[row_number, (col_number + 1)].Value = longitud;
                    worksheet_1.Cells[row_number, (col_number + 2)].Value = direccionFormateada;

                    //save the changes
                    excelPackage.Save();
                }


                row_number++;

                #endregion
            }
        }

        static void ProcessConvertAddress2Coordinates(string connection)
        {
            logger.Info("Start ProcessConvertAddress2Coordinates");
            // Create a request for the URL.   
            string apiKey = ConfigurationManager.AppSettings.Get("Google.API.Key");
            List<Adress> address = GetAddress(connection);
            int cont = 1;
            foreach (var addres in address)
            {
                System.Console.WriteLine("cont:" + cont);
                cont = cont + 1;
                try
                {
                    if (!string.IsNullOrEmpty(addres.Address) || addres.Address != "SIN DIRECCION")
                    {
                        string addressF = string.Format("{0} {1} {2}",
                            addres.Address,
                            addres.City,
                            addres.State);
                        addressF = formatAddress(addressF);
                        logger.Debug("Process start address:" + address);
                        System.Console.WriteLine("Process start address:" + address);
                        var res = GetGeo(addressF, apiKey);
                        if (res != null)
                        {
                            addres.Lat = res.results.First().geometry.location.lat;
                            addres.Lng = res.results.First().geometry.location.lng;
                            SaveAddress(addres, connection);
                            logger.Debug("Process ok address:" + address);
                            System.Console.WriteLine("Process ok address:" + address);
                        }
                        else
                        {
                            logger.Debug("Process fail address:" + address);
                            System.Console.WriteLine("Process fail address:" + address);
                        }
                    }
                }
                catch (Exception ex)
                {
                    logger.Error(ex);
                }
            }
            logger.Info("End ProcessConvertAddress2Coordinates");
        }
        static void ProcessConvertAddress2Coordinates3()
        {
            logger.Info("Start ProcessConvertAddress2Coordinates3");
            // Create a request for the URL.   
            string apiKey = ConfigurationManager.AppSettings.Get("Google.API.Key");
            List<Adress> addressDest = GetAddress(DestinationJson);
            try
            {
                string[] content = File.ReadAllLines(sourceCSV, Encoding.UTF8);
                int cont = 1;
                foreach (var item in content)
                {
                    System.Console.WriteLine("cont:" + cont);
                    cont = cont + 1;
                    try
                    {
                        string[] items = item.Split(';');
                        Adress addres = new Adress()
                        {
                            Id = items[0],
                            Address = items[1],
                            City = items[2],
                            State = items[3],
                        };

                        if (!string.IsNullOrEmpty(addres.Address) || addres.Address != "SIN DIRECCION")
                        {
                            string addressF = string.Format("{0} {1} {2}",
                                addres.Address,
                                addres.City,
                                addres.State);
                            addressF = formatAddress(addressF);
                            logger.Debug("Process start address:" + addres.Id);
                            System.Console.WriteLine("Process start address:" + addres.Id);
                            var res = GetGeo(addressF, apiKey);
                            if (res != null)
                            {
                                addres.Lat = res.results.First().geometry.location.lat;
                                addres.Lng = res.results.First().geometry.location.lng;
                                //SaveAddress(addres, connection);
                                logger.Debug("Process ok address:" + addres.Id);
                                System.Console.WriteLine("Process ok address:" + addres.Id);

                                try
                                {
                                    bool first = true;
                                    double minDistance = 0;
                                    GeoCoordinate coord1 = new GeoCoordinate() { Latitude = (float)addres.Lat, Longitude = (float)addres.Lng };
                                    foreach (var addresD in addressDest)
                                    {
                                        GeoCoordinate coord2 = new GeoCoordinate() { Latitude = (float)addresD.Lat, Longitude = (float)addresD.Lng };
                                        double distance = GetDistance(coord1, coord2);
                                        if (first)
                                        {
                                            minDistance = distance;
                                            first = false;
                                        }
                                        if (distance <= minDistance)
                                        {
                                            minDistance = distance;
                                            addres.Distance = minDistance;
                                            addres.IdNear = addresD.Id;
                                            //SaveAddress(addresS, connectionS);
                                        }
                                    }

                                    //
                                    string line = string.Format("{0};{1};{2};{3};{4};{5};{6};{7}"
                                                , addres.Id
                                                , addres.Address
                                                , addres.City
                                                , addres.State
                                                , addres.Lat
                                                , addres.Lng
                                                , addres.Distance
                                                , addres.IdNear
                                                );
                                    using (StreamWriter sw = File.AppendText(ResultCsv))
                                    {
                                        sw.WriteLine(line);
                                    }
                                    //File.AppendAllText(ResultCsv, line);
                                }
                                catch (Exception ex)
                                {
                                    logger.Error(ex);
                                }
                            }
                            else
                            {
                                logger.Debug("Process fail address:" + addres.Id);
                                System.Console.WriteLine("Process fail address:" + addres.Id);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex);
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
            }
            logger.Info("End ProcessConvertAddress2Coordinates3");
        }


        static void ProcessDistAddress(string connectionS, string connectionD)
        {
            logger.Info("Start ProcessDistAddress");
            // Create a request for the URL.   
            string apiKey = ConfigurationManager.AppSettings.Get("Google.API.Key");
            List<Adress> addressSource = GetAddress(connectionS);
            List<Adress> addressDest = GetAddress(connectionD);
            foreach (var addresS in addressSource)
            {
                try
                {
                    bool first = true;
                    double minDistance = 0;
                    GeoCoordinate coord1 = new GeoCoordinate() { Latitude = (float)addresS.Lat, Longitude = (float)addresS.Lng };
                    foreach (var addresD in addressDest)
                    {
                        GeoCoordinate coord2 = new GeoCoordinate() { Latitude = (float)addresD.Lat, Longitude = (float)addresD.Lng };
                        double distance = GetDistance(coord1, coord2);
                        if (first)
                        {
                            minDistance = distance;
                            first = false;
                        }
                        if (distance <= minDistance)
                        {
                            minDistance = distance;
                            addresS.Distance = minDistance;
                            addresS.IdNear = addresD.Id;
                            SaveAddress(addresS, connectionS);
                        }
                    }
                }
                catch (Exception ex)
                {
                    logger.Error(ex);
                }
            }
            logger.Info("End ProcessDistAddress");
        }

        public static Result GetGeo(string address, string apiKey)
        {
            System.Threading.Thread.Sleep(100);
            Result geoResponse = new Result();
            try
            {
                string url = string.Format("https://maps.googleapis.com/maps/api/geocode/json?address={0}&key={1}", address, apiKey);
                WebRequest request = WebRequest.Create(url);
                // If required by the server, set the credentials.  
                request.Credentials = CredentialCache.DefaultCredentials;

                // Get the response.  
                WebResponse response = request.GetResponse();
                // Display the status.  
                //System.Console.WriteLine(((HttpWebResponse)response).StatusDescription);

                // Get the stream containing content returned by the server. 
                // The using block ensures the stream is automatically closed. 
                using (Stream dataStream = response.GetResponseStream())
                {
                    // Open the stream using a StreamReader for easy access.  
                    StreamReader reader = new StreamReader(dataStream);
                    // Read the content.  
                    string responseFromServer = reader.ReadToEnd();
                    geoResponse = JsonConvert.DeserializeObject<Result>(responseFromServer);
                    // Display the content.  
                    //System.Console.WriteLine(responseFromServer);
                }

                // Close the response.  
                response.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                logger.Debug("Process error address:" + address);
                logger.Error(ex);
            }
            return geoResponse;
        }

        public static void SaveAddress(Adress adress, string connection)
        {
            try
            {
                string content = File.ReadAllText(connection, Encoding.UTF8);
                List<Adress> list = JsonConvert.DeserializeObject<List<Adress>>(content);
                list = list != null ? list : new List<Adress>();
                if (list.Where(session => session.Id == adress.Id).Count() > 0)
                {
                    list.Remove(list.Where(session => session.Id == adress.Id).First());
                }
                list.Add(adress);
                content = JsonHelper.FormatJson(JsonConvert.SerializeObject(list));
                File.WriteAllText(connection, content, Encoding.UTF8);
            }
            catch (Exception ex)
            {
                logger.Error(ex);
            }
        }

        public static List<Adress> GetAddress(string connection)
        {
            try
            {
                string content = File.ReadAllText(connection, Encoding.UTF8);
                List<Adress> list = JsonConvert.DeserializeObject<List<Adress>>(content);
                return list;
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                return null;
            }
        }

        public const double EarthRadius = 6371;
        public static double GetDistance(GeoCoordinate point1, GeoCoordinate point2)
        {
            double distance = 0;
            double Lat = (point2.Latitude - point1.Latitude) * (Math.PI / 180);
            double Lon = (point2.Longitude - point1.Longitude) * (Math.PI / 180);
            double a = Math.Sin(Lat / 2) * Math.Sin(Lat / 2) + Math.Cos(point1.Latitude * (Math.PI / 180)) * Math.Cos(point2.Latitude * (Math.PI / 180)) * Math.Sin(Lon / 2) * Math.Sin(Lon / 2);
            double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
            distance = EarthRadius * c;
            return distance;
        }

        public static void Csv2Json(string sourceCsv, string destinationJson)
        {
            try
            {
                string[] content = File.ReadAllLines(sourceCsv, Encoding.UTF8);
                int cont = 1;
                foreach (var item in content)
                {
                    System.Console.WriteLine("cont:" + cont);
                    cont = cont + 1;
                    try
                    {
                        string[] items = item.Split(';');
                        Adress add = new Adress()
                        {
                            Id = items[0],
                            Address = items[1],
                            City = items[2],
                            State = items[3],
                            //Lat = float.Parse(items[4]),
                            //Lng = float.Parse(items[5])
                        };
                        SaveAddress(add, destinationJson);
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex);
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
            }
        }

        public static void Json2Csv(string sourceJson, string destinationCsv)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                string content = File.ReadAllText(sourceJson);
                List<Adress> list = JsonConvert.DeserializeObject<List<Adress>>(content);
                foreach (var item in list)
                {
                    sb.AppendLine(string.Format("{0};{1};{2};{3};{4};{5};{6};{7}"
                        , item.Id
                        , item.Address
                        , item.City
                        , item.State
                        , item.Lat
                        , item.Lng
                        , item.Distance
                        , item.IdNear
                        ));
                }
                File.WriteAllText(destinationCsv, sb.ToString());
            }
            catch (Exception ex)
            {
                logger.Error(ex);
            }
        }

        public static string formatAddress(string cadena_excel)
        {
            try
            {
                #region Formatting string

                cadena_excel = cadena_excel.Replace(" A ", "A ");
                cadena_excel = cadena_excel.Replace(" a ", "a ");
                cadena_excel = cadena_excel.Replace("#", "+numero+");
                cadena_excel = cadena_excel.Replace("-", " ");
                cadena_excel = cadena_excel.Replace(".", " ");
                cadena_excel = cadena_excel.Replace("ÑON", "ión"); //ión
                cadena_excel = cadena_excel.Replace("Ã-", "i");     //í
                cadena_excel = cadena_excel.Replace("Ñ", "ñ");     // ?
                cadena_excel = cadena_excel.Replace(" AUT ", " autopista");
                cadena_excel = cadena_excel.Replace(" AUTP ", " autopista");
                cadena_excel = cadena_excel.Replace(" CL ", " calle ");
                cadena_excel = cadena_excel.Replace(" Cl ", " calle ");
                cadena_excel = cadena_excel.Replace(" cl ", " calle ");
                cadena_excel = cadena_excel.Replace(" CLL ", " calle ");
                cadena_excel = cadena_excel.Replace(" Cll ", " calle ");
                cadena_excel = cadena_excel.Replace(" CRA ", " Carrera ");
                cadena_excel = cadena_excel.Replace(" CR ", " Carrera ");
                cadena_excel = cadena_excel.Replace(" Cr ", " Carrera ");
                cadena_excel = cadena_excel.Replace(" cr ", " Carrera ");
                cadena_excel = cadena_excel.Replace(" CRR ", " Carrera ");
                cadena_excel = cadena_excel.Replace(" Crr ", " Carrera ");
                cadena_excel = cadena_excel.Replace(" BR ", " Barrio ");
                cadena_excel = cadena_excel.Replace(" BRR ", " Barrio ");
                cadena_excel = cadena_excel.Replace(" Br ", " Barrio ");
                cadena_excel = cadena_excel.Replace(" Brr ", " Barrio ");
                cadena_excel = cadena_excel.Replace(" brr ", " Barrio ");
                cadena_excel = cadena_excel.Replace(" AV ", " Avenida ");
                cadena_excel = cadena_excel.Replace(" Av ", " Avenida ");
                cadena_excel = cadena_excel.Replace(" av ", " Avenida ");
                cadena_excel = cadena_excel.Replace(" KM ", " Kilometro ");
                cadena_excel = cadena_excel.Replace(" Km ", " Kilometro ");
                cadena_excel = cadena_excel.Replace(" km ", " Kilometro ");
                cadena_excel = cadena_excel.Replace(" SEC ", " Sector ");
                cadena_excel = cadena_excel.Replace(" C CIAL ", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace(" CC ", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace(" CC ", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace(" C.C ", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace(" LC ", " Local ");
                cadena_excel = cadena_excel.Replace(" DG ", " Diagonal ");
                cadena_excel = cadena_excel.Replace(" TV ", " Transversal ");
                cadena_excel = cadena_excel.Replace(" TVR ", " Transversal ");
                cadena_excel = cadena_excel.Replace(" TRANSV ", " Transversal ");
                cadena_excel = cadena_excel.Replace(" VDA ", " Vereda ");
                cadena_excel = cadena_excel.Replace(" VRD ", " Vereda ");
                cadena_excel = cadena_excel.Replace(" LT ", " Lote ");
                cadena_excel = cadena_excel.Replace(" PA ", " Parcela ");
                cadena_excel = cadena_excel.Replace(" MZ ", " Manzana ");
                cadena_excel = cadena_excel.Replace(" Mz ", " Manzana ");
                cadena_excel = cadena_excel.Replace(" mz ", " Manzana ");
                cadena_excel = cadena_excel.Replace(" VTE ", " Variante ");
                cadena_excel = cadena_excel.Replace(" FCA ", " Finca ");
                cadena_excel = cadena_excel.Replace(" N ", " Número ");
                cadena_excel = cadena_excel.Replace(" No ", " Número ");


                cadena_excel = cadena_excel.Replace("+AUT ", " autopista");
                cadena_excel = cadena_excel.Replace("+AUTP ", " autopista");
                cadena_excel = cadena_excel.Replace("+CL ", " calle ");
                cadena_excel = cadena_excel.Replace("+Cl ", " calle ");
                cadena_excel = cadena_excel.Replace("+cl ", " calle ");
                cadena_excel = cadena_excel.Replace("+CLL ", " calle ");
                cadena_excel = cadena_excel.Replace("+Cll ", " calle ");
                cadena_excel = cadena_excel.Replace("+CRA ", " Carrera ");
                cadena_excel = cadena_excel.Replace("+CR ", " Carrera ");
                cadena_excel = cadena_excel.Replace("+Cr ", " Carrera ");
                cadena_excel = cadena_excel.Replace("+cr ", " Carrera ");
                cadena_excel = cadena_excel.Replace("+CRR ", " Carrera ");
                cadena_excel = cadena_excel.Replace("+Crr ", " Carrera ");
                cadena_excel = cadena_excel.Replace("+BR ", " Barrio ");
                cadena_excel = cadena_excel.Replace("+BRR ", " Barrio ");
                cadena_excel = cadena_excel.Replace("+Br ", " Barrio ");
                cadena_excel = cadena_excel.Replace("+Brr ", " Barrio ");
                cadena_excel = cadena_excel.Replace("+brr ", " Barrio ");
                cadena_excel = cadena_excel.Replace("+AV ", " Avenida ");
                cadena_excel = cadena_excel.Replace("+Av ", " Avenida ");
                cadena_excel = cadena_excel.Replace("+av ", " Avenida ");
                cadena_excel = cadena_excel.Replace("+KM ", " Kilometro ");
                cadena_excel = cadena_excel.Replace("+Km ", " Kilometro ");
                cadena_excel = cadena_excel.Replace("+km ", " Kilometro ");
                cadena_excel = cadena_excel.Replace("+SEC ", " Sector ");
                cadena_excel = cadena_excel.Replace("+C CIAL ", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace("+CC ", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace("+CC ", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace("+C.C ", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace("+LC ", " Local ");
                cadena_excel = cadena_excel.Replace("+DG ", " Diagonal ");
                cadena_excel = cadena_excel.Replace("+TV ", " Transversal ");
                cadena_excel = cadena_excel.Replace("+TVR ", " Transversal ");
                cadena_excel = cadena_excel.Replace("+TRANSV ", " Transversal ");
                cadena_excel = cadena_excel.Replace("+VDA ", " Vereda ");
                cadena_excel = cadena_excel.Replace("+VRD ", " Vereda ");
                cadena_excel = cadena_excel.Replace("+LT ", " Lote ");
                cadena_excel = cadena_excel.Replace("+PA ", " Parcela ");
                cadena_excel = cadena_excel.Replace("+MZ ", " Manzana ");
                cadena_excel = cadena_excel.Replace("+Mz ", " Manzana ");
                cadena_excel = cadena_excel.Replace("+mz ", " Manzana ");
                cadena_excel = cadena_excel.Replace("+VTE ", " Variante ");
                cadena_excel = cadena_excel.Replace("+FCA ", " Finca ");
                cadena_excel = cadena_excel.Replace("+N ", " Número ");
                cadena_excel = cadena_excel.Replace("+No ", " Número ");

                // Fin 1 +


                // Ini 2 +

                cadena_excel = cadena_excel.Replace("+AUT+", " autopista");
                cadena_excel = cadena_excel.Replace("+AUTP+", " autopista");
                cadena_excel = cadena_excel.Replace("+CL+", " calle ");
                cadena_excel = cadena_excel.Replace("+Cl+", " calle ");
                cadena_excel = cadena_excel.Replace("+cl+", " calle ");
                cadena_excel = cadena_excel.Replace("+CLL+", " calle ");
                cadena_excel = cadena_excel.Replace("+Cll+", " calle ");
                cadena_excel = cadena_excel.Replace("+CRA+", " Carrera ");
                cadena_excel = cadena_excel.Replace("+CR+", " Carrera ");
                cadena_excel = cadena_excel.Replace("+Cr+", " Carrera ");
                cadena_excel = cadena_excel.Replace("+cr+", " Carrera ");
                cadena_excel = cadena_excel.Replace("+CRR+", " Carrera ");
                cadena_excel = cadena_excel.Replace("+Crr+", " Carrera ");
                cadena_excel = cadena_excel.Replace("+BR+", " Barrio ");
                cadena_excel = cadena_excel.Replace("+BRR+", " Barrio ");
                cadena_excel = cadena_excel.Replace("+Br+", " Barrio ");
                cadena_excel = cadena_excel.Replace("+Brr+", " Barrio ");
                cadena_excel = cadena_excel.Replace("+brr+", " Barrio ");
                cadena_excel = cadena_excel.Replace("+AV+", " Avenida ");
                cadena_excel = cadena_excel.Replace("+Av+", " Avenida ");
                cadena_excel = cadena_excel.Replace("+av+", " Avenida ");
                cadena_excel = cadena_excel.Replace("+KM+", " Kilometro ");
                cadena_excel = cadena_excel.Replace("+Km+", " Kilometro ");
                cadena_excel = cadena_excel.Replace("+km+", " Kilometro ");
                cadena_excel = cadena_excel.Replace("+SEC+", " Sector ");
                cadena_excel = cadena_excel.Replace("+C CIAL+", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace("+CC+", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace("+CC+", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace("+C.C+", " Centro Comercial ");
                cadena_excel = cadena_excel.Replace("+LC+", " Local ");
                cadena_excel = cadena_excel.Replace("+DG+", " Diagonal ");
                cadena_excel = cadena_excel.Replace("+TV+", " Transversal ");
                cadena_excel = cadena_excel.Replace("+TVR+", " Transversal ");
                cadena_excel = cadena_excel.Replace("+TRANSV+", " Transversal ");
                cadena_excel = cadena_excel.Replace("+VDA+", " Vereda ");
                cadena_excel = cadena_excel.Replace("+VRD+", " Vereda ");
                cadena_excel = cadena_excel.Replace("+LT+", " Lote ");
                cadena_excel = cadena_excel.Replace("+PA+", " Parcela ");
                cadena_excel = cadena_excel.Replace("+MZ+", " Manzana ");
                cadena_excel = cadena_excel.Replace("+Mz+", " Manzana ");
                cadena_excel = cadena_excel.Replace("+mz+", " Manzana ");
                cadena_excel = cadena_excel.Replace("+VTE+", " Variante ");
                cadena_excel = cadena_excel.Replace("+FCA+", " Finca ");
                cadena_excel = cadena_excel.Replace("+N+", " Número ");
                cadena_excel = cadena_excel.Replace("+No+", " Número ");



                // Ini - Nomenclatura - DIAN 2020

                cadena_excel = cadena_excel.Replace(" AC ", " Administración ");
                cadena_excel = cadena_excel.Replace(" AD ", " Avenida calle ");
                cadena_excel = cadena_excel.Replace(" ADL ", " Adelante ");
                cadena_excel = cadena_excel.Replace(" AER ", " Aeropuerto ");
                cadena_excel = cadena_excel.Replace(" AG ", " Agencia ");
                cadena_excel = cadena_excel.Replace(" AGP ", " Agrupación ");
                cadena_excel = cadena_excel.Replace(" AK ", " Avenida carrera ");
                cadena_excel = cadena_excel.Replace(" AL ", " Altillo ");
                cadena_excel = cadena_excel.Replace(" ALD ", " Al lado ");
                cadena_excel = cadena_excel.Replace(" ALM ", " Almacén ");
                cadena_excel = cadena_excel.Replace(" AP ", " Apartamento ");
                cadena_excel = cadena_excel.Replace(" APTDO ", " Apartado ");
                cadena_excel = cadena_excel.Replace(" ATR ", " Atrás ");
                cadena_excel = cadena_excel.Replace(" AUT ", " Autopista ");
                cadena_excel = cadena_excel.Replace(" AV ", " Avenida ");
                cadena_excel = cadena_excel.Replace(" AVIAL ", " Anillo vial ");
                cadena_excel = cadena_excel.Replace(" BG ", " Bodega ");
                cadena_excel = cadena_excel.Replace(" BL ", " Bloque ");
                cadena_excel = cadena_excel.Replace(" BLV ", " Boulevard ");
                cadena_excel = cadena_excel.Replace(" BRR ", " Barrio ");
                cadena_excel = cadena_excel.Replace(" C ", " Corregimiento ");
                cadena_excel = cadena_excel.Replace(" CA ", " Casa ");
                cadena_excel = cadena_excel.Replace(" CAS ", " Caserío ");
                cadena_excel = cadena_excel.Replace(" CC ", " Centro comercial ");
                cadena_excel = cadena_excel.Replace(" CD ", " Ciudadela ");
                cadena_excel = cadena_excel.Replace(" CEL ", " Célula ");
                cadena_excel = cadena_excel.Replace(" CEN ", " Centro ");
                cadena_excel = cadena_excel.Replace(" CIR ", " Circular ");
                cadena_excel = cadena_excel.Replace(" CL ", " Calle ");
                cadena_excel = cadena_excel.Replace(" CLJ ", " Callejón ");
                cadena_excel = cadena_excel.Replace(" CN ", " Camino ");
                cadena_excel = cadena_excel.Replace(" CON ", " Conjunto residencial ");
                cadena_excel = cadena_excel.Replace(" CONJ ", " Conjunto ");
                cadena_excel = cadena_excel.Replace(" CR ", " Carrera ");
                cadena_excel = cadena_excel.Replace(" CRT ", " Carretera ");
                cadena_excel = cadena_excel.Replace(" CRV ", " Circunvalar ");
                cadena_excel = cadena_excel.Replace(" CS ", " Consultorio ");
                cadena_excel = cadena_excel.Replace(" DG ", " Diagonal ");
                cadena_excel = cadena_excel.Replace(" DP ", " Depósito ");
                cadena_excel = cadena_excel.Replace(" DPTO ", " Departamento ");
                cadena_excel = cadena_excel.Replace(" DS ", " Depósito sótano ");
                cadena_excel = cadena_excel.Replace(" ED ", " Edificio ");
                cadena_excel = cadena_excel.Replace(" EN ", " Entrada ");
                cadena_excel = cadena_excel.Replace(" ES ", " Escalera ");
                cadena_excel = cadena_excel.Replace(" ESQ ", " Esquina ");
                cadena_excel = cadena_excel.Replace(" ESTE ", " Este ");
                cadena_excel = cadena_excel.Replace(" ET ", " Etapa ");
                cadena_excel = cadena_excel.Replace(" EX ", " Exterior ");
                cadena_excel = cadena_excel.Replace(" FCA ", " Finca ");
                cadena_excel = cadena_excel.Replace(" GJ ", " Garaje ");
                cadena_excel = cadena_excel.Replace(" GS ", " Garaje sótano ");
                cadena_excel = cadena_excel.Replace(" GT ", " Glorieta ");
                cadena_excel = cadena_excel.Replace(" HC ", " Hacienda ");
                cadena_excel = cadena_excel.Replace(" HG ", " Hangar ");
                cadena_excel = cadena_excel.Replace(" IN ", " Interior ");
                cadena_excel = cadena_excel.Replace(" IP ", "Inspección  de Policía ");
                cadena_excel = cadena_excel.Replace(" IPD ", " Inspección Departamental ");
                cadena_excel = cadena_excel.Replace(" IPM ", " Inspección Municipal ");
                cadena_excel = cadena_excel.Replace(" KM ", " Kilómetro ");
                cadena_excel = cadena_excel.Replace(" LC ", " Local ");
                cadena_excel = cadena_excel.Replace(" LM ", " Local mezzanine ");
                cadena_excel = cadena_excel.Replace(" LT ", " Lote ");
                cadena_excel = cadena_excel.Replace(" MD ", " Módulo ");
                cadena_excel = cadena_excel.Replace(" MJ ", " Mojón ");
                cadena_excel = cadena_excel.Replace(" MLL ", " Muelle ");
                cadena_excel = cadena_excel.Replace(" MN ", " Mezzanine ");
                cadena_excel = cadena_excel.Replace(" MZ ", " Manzana ");
                cadena_excel = cadena_excel.Replace(" NORTE ", " Norte ");
                cadena_excel = cadena_excel.Replace(" O ", " Oriente ");
                cadena_excel = cadena_excel.Replace(" OCC ", " Occidente ");
                cadena_excel = cadena_excel.Replace(" OESTE ", " Oeste ");
                cadena_excel = cadena_excel.Replace(" OF ", " Oficina ");
                cadena_excel = cadena_excel.Replace(" P ", " Piso ");
                cadena_excel = cadena_excel.Replace(" PA ", " Parcela ");
                cadena_excel = cadena_excel.Replace(" PAR ", " Parque ");
                cadena_excel = cadena_excel.Replace(" PD ", " Predio ");
                cadena_excel = cadena_excel.Replace(" PH ", " Penthouse ");
                cadena_excel = cadena_excel.Replace(" PJ ", " Pasaje ");
                cadena_excel = cadena_excel.Replace(" PL ", " Planta ");
                cadena_excel = cadena_excel.Replace(" PN ", " Puente ");
                cadena_excel = cadena_excel.Replace(" POR ", " Portería ");
                cadena_excel = cadena_excel.Replace(" POS ", " Poste ");
                cadena_excel = cadena_excel.Replace(" PQ ", " Parqueadero ");
                cadena_excel = cadena_excel.Replace(" PRJ ", " Paraje ");
                cadena_excel = cadena_excel.Replace(" PS ", " Paseo ");
                cadena_excel = cadena_excel.Replace(" PT ", " Puesto ");
                cadena_excel = cadena_excel.Replace(" PW ", " Park Way ");
                cadena_excel = cadena_excel.Replace(" RP ", " Round Point ");
                cadena_excel = cadena_excel.Replace(" SA ", " Salón ");
                cadena_excel = cadena_excel.Replace(" SC ", " Salón comunal ");
                cadena_excel = cadena_excel.Replace(" SD ", " Salida ");
                cadena_excel = cadena_excel.Replace(" SEC ", " Sector ");
                cadena_excel = cadena_excel.Replace(" SL ", " Solar ");
                cadena_excel = cadena_excel.Replace(" SM ", " Súper manzana ");
                cadena_excel = cadena_excel.Replace(" SS ", " Semisótano ");
                cadena_excel = cadena_excel.Replace(" ST ", " Sótano ");
                cadena_excel = cadena_excel.Replace(" SUITE ", " Suite ");
                cadena_excel = cadena_excel.Replace(" SUR ", " Sur ");
                cadena_excel = cadena_excel.Replace(" TER ", " Terminal ");
                cadena_excel = cadena_excel.Replace(" TERPLN ", " Terraplén ");
                cadena_excel = cadena_excel.Replace(" TO ", " Torre ");
                cadena_excel = cadena_excel.Replace(" TV ", " Transversal ");
                cadena_excel = cadena_excel.Replace(" TZ ", " Terraza ");
                cadena_excel = cadena_excel.Replace(" UN ", " Unidad ");
                cadena_excel = cadena_excel.Replace(" UR ", " Unidad residencial ");
                cadena_excel = cadena_excel.Replace(" URB ", " Urbanización ");
                cadena_excel = cadena_excel.Replace(" VRD ", " Vereda ");
                cadena_excel = cadena_excel.Replace(" VTE ", " Variante ");
                cadena_excel = cadena_excel.Replace(" ZN ", " Zona ");
                cadena_excel = cadena_excel.Replace(" ZF ", " Zona franca ");


                // Ini 1 +

                cadena_excel = cadena_excel.Replace("+AC ", " Administración ");
                cadena_excel = cadena_excel.Replace("+AD ", " Avenida calle ");
                cadena_excel = cadena_excel.Replace("+ADL ", " Adelante ");
                cadena_excel = cadena_excel.Replace("+AER ", " Aeropuerto ");
                cadena_excel = cadena_excel.Replace("+AG ", " Agencia ");
                cadena_excel = cadena_excel.Replace("+AGP ", " Agrupación ");
                cadena_excel = cadena_excel.Replace("+AK ", " Avenida carrera ");
                cadena_excel = cadena_excel.Replace("+AL ", " Altillo ");
                cadena_excel = cadena_excel.Replace("+ALD ", " Al lado ");
                cadena_excel = cadena_excel.Replace("+ALM ", " Almacén ");
                cadena_excel = cadena_excel.Replace("+AP ", " Apartamento ");
                cadena_excel = cadena_excel.Replace("+APTDO ", " Apartado ");
                cadena_excel = cadena_excel.Replace("+ATR ", " Atrás ");
                cadena_excel = cadena_excel.Replace("+AUT ", " Autopista ");
                cadena_excel = cadena_excel.Replace("+AV ", " Avenida ");
                cadena_excel = cadena_excel.Replace("+AVIAL ", " Anillo vial ");
                cadena_excel = cadena_excel.Replace("+BG ", " Bodega ");
                cadena_excel = cadena_excel.Replace("+BL ", " Bloque ");
                cadena_excel = cadena_excel.Replace("+BLV ", " Boulevard ");
                cadena_excel = cadena_excel.Replace("+BRR ", " Barrio ");
                cadena_excel = cadena_excel.Replace("+C ", " Corregimiento ");
                cadena_excel = cadena_excel.Replace("+CA ", " Casa ");
                cadena_excel = cadena_excel.Replace("+CAS ", " Caserío ");
                cadena_excel = cadena_excel.Replace("+CC ", " Centro comercial ");
                cadena_excel = cadena_excel.Replace("+CD ", " Ciudadela ");
                cadena_excel = cadena_excel.Replace("+CEL ", " Célula ");
                cadena_excel = cadena_excel.Replace("+CEN ", " Centro ");
                cadena_excel = cadena_excel.Replace("+CIR ", " Circular ");
                cadena_excel = cadena_excel.Replace("+CL ", " Calle ");
                cadena_excel = cadena_excel.Replace("+CLJ ", " Callejón ");
                cadena_excel = cadena_excel.Replace("+CN ", " Camino ");
                cadena_excel = cadena_excel.Replace("+CON ", " Conjunto residencial ");
                cadena_excel = cadena_excel.Replace("+CONJ ", " Conjunto ");
                cadena_excel = cadena_excel.Replace("+CR ", " Carrera ");
                cadena_excel = cadena_excel.Replace("+CRT ", " Carretera ");
                cadena_excel = cadena_excel.Replace("+CRV ", " Circunvalar ");
                cadena_excel = cadena_excel.Replace("+CS ", " Consultorio ");
                cadena_excel = cadena_excel.Replace("+DG ", " Diagonal ");
                cadena_excel = cadena_excel.Replace("+DP ", " Depósito ");
                cadena_excel = cadena_excel.Replace("+DPTO ", " Departamento ");
                cadena_excel = cadena_excel.Replace("+DS ", " Depósito sótano ");
                cadena_excel = cadena_excel.Replace("+ED ", " Edificio ");
                cadena_excel = cadena_excel.Replace("+EN ", " Entrada ");
                cadena_excel = cadena_excel.Replace("+ES ", " Escalera ");
                cadena_excel = cadena_excel.Replace("+ESQ ", " Esquina ");
                cadena_excel = cadena_excel.Replace("+ESTE ", " Este ");
                cadena_excel = cadena_excel.Replace("+ET ", " Etapa ");
                cadena_excel = cadena_excel.Replace("+EX ", " Exterior ");
                cadena_excel = cadena_excel.Replace("+FCA ", " Finca ");
                cadena_excel = cadena_excel.Replace("+GJ ", " Garaje ");
                cadena_excel = cadena_excel.Replace("+GS ", " Garaje sótano ");
                cadena_excel = cadena_excel.Replace("+GT ", " Glorieta ");
                cadena_excel = cadena_excel.Replace("+HC ", " Hacienda ");
                cadena_excel = cadena_excel.Replace("+HG ", " Hangar ");
                cadena_excel = cadena_excel.Replace("+IN ", " Interior ");
                cadena_excel = cadena_excel.Replace("+IP ", "Inspección  de Policía ");
                cadena_excel = cadena_excel.Replace("+IPD ", " Inspección Departamental ");
                cadena_excel = cadena_excel.Replace("+IPM ", " Inspección Municipal ");
                cadena_excel = cadena_excel.Replace("+KM ", " Kilómetro ");
                cadena_excel = cadena_excel.Replace("+LC ", " Local ");
                cadena_excel = cadena_excel.Replace("+LM ", " Local mezzanine ");
                cadena_excel = cadena_excel.Replace("+LT ", " Lote ");
                cadena_excel = cadena_excel.Replace("+MD ", " Módulo ");
                cadena_excel = cadena_excel.Replace("+MJ ", " Mojón ");
                cadena_excel = cadena_excel.Replace("+MLL ", " Muelle ");
                cadena_excel = cadena_excel.Replace("+MN ", " Mezzanine ");
                cadena_excel = cadena_excel.Replace("+MZ ", " Manzana ");
                cadena_excel = cadena_excel.Replace("+NORTE ", " Norte ");
                cadena_excel = cadena_excel.Replace("+O ", " Oriente ");
                cadena_excel = cadena_excel.Replace("+OCC ", " Occidente ");
                cadena_excel = cadena_excel.Replace("+OESTE ", " Oeste ");
                cadena_excel = cadena_excel.Replace("+OF ", " Oficina ");
                cadena_excel = cadena_excel.Replace("+P ", " Piso ");
                cadena_excel = cadena_excel.Replace("+PA ", " Parcela ");
                cadena_excel = cadena_excel.Replace("+PAR ", " Parque ");
                cadena_excel = cadena_excel.Replace("+PD ", " Predio ");
                cadena_excel = cadena_excel.Replace("+PH ", " Penthouse ");
                cadena_excel = cadena_excel.Replace("+PJ ", " Pasaje ");
                cadena_excel = cadena_excel.Replace("+PL ", " Planta ");
                cadena_excel = cadena_excel.Replace("+PN ", " Puente ");
                cadena_excel = cadena_excel.Replace("+POR ", " Portería ");
                cadena_excel = cadena_excel.Replace("+POS ", " Poste ");
                cadena_excel = cadena_excel.Replace("+PQ ", " Parqueadero ");
                cadena_excel = cadena_excel.Replace("+PRJ ", " Paraje ");
                cadena_excel = cadena_excel.Replace("+PS ", " Paseo ");
                cadena_excel = cadena_excel.Replace("+PT ", " Puesto ");
                cadena_excel = cadena_excel.Replace("+PW ", " Park Way ");
                cadena_excel = cadena_excel.Replace("+RP ", " Round Point ");
                cadena_excel = cadena_excel.Replace("+SA ", " Salón ");
                cadena_excel = cadena_excel.Replace("+SC ", " Salón comunal ");
                cadena_excel = cadena_excel.Replace("+SD ", " Salida ");
                cadena_excel = cadena_excel.Replace("+SEC ", " Sector ");
                cadena_excel = cadena_excel.Replace("+SL ", " Solar ");
                cadena_excel = cadena_excel.Replace("+SM ", " Súper manzana ");
                cadena_excel = cadena_excel.Replace("+SS ", " Semisótano ");
                cadena_excel = cadena_excel.Replace("+ST ", " Sótano ");
                cadena_excel = cadena_excel.Replace("+SUITE ", " Suite ");
                cadena_excel = cadena_excel.Replace("+SUR ", " Sur ");
                cadena_excel = cadena_excel.Replace("+TER ", " Terminal ");
                cadena_excel = cadena_excel.Replace("+TERPLN ", " Terraplén ");
                cadena_excel = cadena_excel.Replace("+TO ", " Torre ");
                cadena_excel = cadena_excel.Replace("+TV ", " Transversal ");
                cadena_excel = cadena_excel.Replace("+TZ ", " Terraza ");
                cadena_excel = cadena_excel.Replace("+UN ", " Unidad ");
                cadena_excel = cadena_excel.Replace("+UR ", " Unidad residencial ");
                cadena_excel = cadena_excel.Replace("+URB ", " Urbanización ");
                cadena_excel = cadena_excel.Replace("+VRD ", " Vereda ");
                cadena_excel = cadena_excel.Replace("+VTE ", " Variante ");
                cadena_excel = cadena_excel.Replace("+ZN ", " Zona ");
                cadena_excel = cadena_excel.Replace("+ZF ", " Zona franca ");



                cadena_excel = cadena_excel.Replace("+AC+", " Administración ");
                cadena_excel = cadena_excel.Replace("+AD+", " Avenida calle ");
                cadena_excel = cadena_excel.Replace("+ADL+", " Adelante ");
                cadena_excel = cadena_excel.Replace("+AER+", " Aeropuerto ");
                cadena_excel = cadena_excel.Replace("+AG+", " Agencia ");
                cadena_excel = cadena_excel.Replace("+AGP+", " Agrupación ");
                cadena_excel = cadena_excel.Replace("+AK+", " Avenida carrera ");
                cadena_excel = cadena_excel.Replace("+AL+", " Altillo ");
                cadena_excel = cadena_excel.Replace("+ALD+", " Al lado ");
                cadena_excel = cadena_excel.Replace("+ALM+", " Almacén ");
                cadena_excel = cadena_excel.Replace("+AP+", " Apartamento ");
                cadena_excel = cadena_excel.Replace("+APTDO+", " Apartado ");
                cadena_excel = cadena_excel.Replace("+ATR+", " Atrás ");
                cadena_excel = cadena_excel.Replace("+AUT+", " Autopista ");
                cadena_excel = cadena_excel.Replace("+AV+", " Avenida ");
                cadena_excel = cadena_excel.Replace("+AVIAL+", " Anillo vial ");
                cadena_excel = cadena_excel.Replace("+BG+", " Bodega ");
                cadena_excel = cadena_excel.Replace("+BL+", " Bloque ");
                cadena_excel = cadena_excel.Replace("+BLV+", " Boulevard ");
                cadena_excel = cadena_excel.Replace("+BRR+", " Barrio ");
                cadena_excel = cadena_excel.Replace("+C+", " Corregimiento ");
                cadena_excel = cadena_excel.Replace("+CA+", " Casa ");
                cadena_excel = cadena_excel.Replace("+CAS+", " Caserío ");
                cadena_excel = cadena_excel.Replace("+CC+", " Centro comercial ");
                cadena_excel = cadena_excel.Replace("+CD+", " Ciudadela ");
                cadena_excel = cadena_excel.Replace("+CEL+", " Célula ");
                cadena_excel = cadena_excel.Replace("+CEN+", " Centro ");
                cadena_excel = cadena_excel.Replace("+CIR+", " Circular ");
                cadena_excel = cadena_excel.Replace("+CL+", " Calle ");
                cadena_excel = cadena_excel.Replace("+CLJ+", " Callejón ");
                cadena_excel = cadena_excel.Replace("+CN+", " Camino ");
                cadena_excel = cadena_excel.Replace("+CON+", " Conjunto residencial ");
                cadena_excel = cadena_excel.Replace("+CONJ+", " Conjunto ");
                cadena_excel = cadena_excel.Replace("+CR+", " Carrera ");
                cadena_excel = cadena_excel.Replace("+CRT+", " Carretera ");
                cadena_excel = cadena_excel.Replace("+CRV+", " Circunvalar ");
                cadena_excel = cadena_excel.Replace("+CS+", " Consultorio ");
                cadena_excel = cadena_excel.Replace("+DG+", " Diagonal ");
                cadena_excel = cadena_excel.Replace("+DP+", " Depósito ");
                cadena_excel = cadena_excel.Replace("+DPTO+", " Departamento ");
                cadena_excel = cadena_excel.Replace("+DS+", " Depósito sótano ");
                cadena_excel = cadena_excel.Replace("+ED+", " Edificio ");
                cadena_excel = cadena_excel.Replace("+EN+", " Entrada ");
                cadena_excel = cadena_excel.Replace("+ES+", " Escalera ");
                cadena_excel = cadena_excel.Replace("+ESQ+", " Esquina ");
                cadena_excel = cadena_excel.Replace("+ESTE+", " Este ");
                cadena_excel = cadena_excel.Replace("+ET+", " Etapa ");
                cadena_excel = cadena_excel.Replace("+EX+", " Exterior ");
                cadena_excel = cadena_excel.Replace("+FCA+", " Finca ");
                cadena_excel = cadena_excel.Replace("+GJ+", " Garaje ");
                cadena_excel = cadena_excel.Replace("+GS+", " Garaje sótano ");
                cadena_excel = cadena_excel.Replace("+GT+", " Glorieta ");
                cadena_excel = cadena_excel.Replace("+HC+", " Hacienda ");
                cadena_excel = cadena_excel.Replace("+HG+", " Hangar ");
                cadena_excel = cadena_excel.Replace("+IN+", " Interior ");
                cadena_excel = cadena_excel.Replace("+IP+", "Inspección  de Policía ");
                cadena_excel = cadena_excel.Replace("+IPD+", " Inspección Departamental ");
                cadena_excel = cadena_excel.Replace("+IPM+", " Inspección Municipal ");
                cadena_excel = cadena_excel.Replace("+KM+", " Kilómetro ");
                cadena_excel = cadena_excel.Replace("+LC+", " Local ");
                cadena_excel = cadena_excel.Replace("+LM+", " Local mezzanine ");
                cadena_excel = cadena_excel.Replace("+LT+", " Lote ");
                cadena_excel = cadena_excel.Replace("+MD+", " Módulo ");
                cadena_excel = cadena_excel.Replace("+MJ+", " Mojón ");
                cadena_excel = cadena_excel.Replace("+MLL+", " Muelle ");
                cadena_excel = cadena_excel.Replace("+MN+", " Mezzanine ");
                cadena_excel = cadena_excel.Replace("+MZ+", " Manzana ");
                cadena_excel = cadena_excel.Replace("+NORTE+", " Norte ");
                cadena_excel = cadena_excel.Replace("+O+", " Oriente ");
                cadena_excel = cadena_excel.Replace("+OCC+", " Occidente ");
                cadena_excel = cadena_excel.Replace("+OESTE+", " Oeste ");
                cadena_excel = cadena_excel.Replace("+OF+", " Oficina ");
                cadena_excel = cadena_excel.Replace("+P+", " Piso ");
                cadena_excel = cadena_excel.Replace("+PA+", " Parcela ");
                cadena_excel = cadena_excel.Replace("+PAR+", " Parque ");
                cadena_excel = cadena_excel.Replace("+PD+", " Predio ");
                cadena_excel = cadena_excel.Replace("+PH+", " Penthouse ");
                cadena_excel = cadena_excel.Replace("+PJ+", " Pasaje ");
                cadena_excel = cadena_excel.Replace("+PL+", " Planta ");
                cadena_excel = cadena_excel.Replace("+PN+", " Puente ");
                cadena_excel = cadena_excel.Replace("+POR+", " Portería ");
                cadena_excel = cadena_excel.Replace("+POS+", " Poste ");
                cadena_excel = cadena_excel.Replace("+PQ+", " Parqueadero ");
                cadena_excel = cadena_excel.Replace("+PRJ+", " Paraje ");
                cadena_excel = cadena_excel.Replace("+PS+", " Paseo ");
                cadena_excel = cadena_excel.Replace("+PT+", " Puesto ");
                cadena_excel = cadena_excel.Replace("+PW+", " Park Way ");
                cadena_excel = cadena_excel.Replace("+RP+", " Round Point ");
                cadena_excel = cadena_excel.Replace("+SA+", " Salón ");
                cadena_excel = cadena_excel.Replace("+SC+", " Salón comunal ");
                cadena_excel = cadena_excel.Replace("+SD+", " Salida ");
                cadena_excel = cadena_excel.Replace("+SEC+", " Sector ");
                cadena_excel = cadena_excel.Replace("+SL+", " Solar ");
                cadena_excel = cadena_excel.Replace("+SM+", " Súper manzana ");
                cadena_excel = cadena_excel.Replace("+SS+", " Semisótano ");
                cadena_excel = cadena_excel.Replace("+ST+", " Sótano ");
                cadena_excel = cadena_excel.Replace("+SUITE+", " Suite ");
                cadena_excel = cadena_excel.Replace("+SUR+", " Sur ");
                cadena_excel = cadena_excel.Replace("+TER+", " Terminal ");
                cadena_excel = cadena_excel.Replace("+TERPLN+", " Terraplén ");
                cadena_excel = cadena_excel.Replace("+TO+", " Torre ");
                cadena_excel = cadena_excel.Replace("+TV+", " Transversal ");
                cadena_excel = cadena_excel.Replace("+TZ+", " Terraza ");
                cadena_excel = cadena_excel.Replace("+UN+", " Unidad ");
                cadena_excel = cadena_excel.Replace("+UR+", " Unidad residencial ");
                cadena_excel = cadena_excel.Replace("+URB+", " Urbanización ");
                cadena_excel = cadena_excel.Replace("+VRD+", " Vereda ");
                cadena_excel = cadena_excel.Replace("+VTE+", " Variante ");
                cadena_excel = cadena_excel.Replace("+ZN+", " Zona ");
                cadena_excel = cadena_excel.Replace("+ZF+", " Zona franca ");

                // Fin - Nomenclatura - DIAN 2020

                cadena_excel = cadena_excel.Replace("\u0081", " ");

                cadena_excel = cadena_excel.Replace(" ", "+");

                #endregion Formatting string
            }
            catch (Exception ex)
            {
                logger.Error(ex);
            }
            return cadena_excel;
        }
    }

    public class GeoCoordinate
    {
        public float Latitude { get; set; }
        public float Longitude { get; set; }
    }

    public class Adress
    {
        public string Id { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public float? Lat { get; set; }
        public float? Lng { get; set; }
        public string IdNear { get; set; }
        public double Distance { get; set; }
    }

    public class Result
    {
        public List<GEOResponse> results { get; set; }

        public string status { get; set; }
    }

    public class GEOResponse
    {
        public List<AddressComponents> address_components { get; set; }

        public string formatted_address { get; set; }

        public Geometry geometry { get; set; }
    }

    public class AddressComponents
    {
        public string long_name { get; set; }
        public string short_name { get; set; }

        public string[] types { get; set; }
    }

    public class Geometry
    {
        public Location location { get; set; }

        public string location_type { get; set; }

        public ViewPort view_port { get; set; }

        public string place_id { get; set; }

        public PlusCode plus_code { get; set; }

        public string[] types { get; set; }
    }

    public class Location
    {
        public float lat { get; set; }

        public float lng { get; set; }
    }

    public class ViewPort
    {
        public Location northeast { get; set; }

        public Location southwest { get; set; }
    }
    public class PlusCode
    {
        public string compound_code { get; set; }

        public string global_code { get; set; }
    }

}