using cbConector;
using CopaBox.Extensions;
using CopaBox.Models;
using CopaBox.RouteLocalization;
using Microsoft.AspNet.Identity;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Threading.Tasks;
using System.Web.Mvc;
using System.Web.Script.Serialization;

namespace CopaBox.Controllers
{
    public class HomeController : BaseController
    {
        private string RutaVista { get { return "~/Views/Home"; } }
        [AllowAnonymous]
        [LocalizedRoute("~/home")]
        [LocalizedRoute("", translateUrl: false)]
        public ActionResult Index()
        {
            return View($"{RutaVista}/Index.cshtml");
        }
        [LocalizedRoute("~/como_funciona")]
        [AllowAnonymous]
        public ActionResult ComoFunciona()
        {
            if (User.Identity.IsAuthenticated)
            {
                try
                {
                    CopaCursoEntities bFirstEntidades = new CopaCursoEntities();
                    decimal idEntidad = Convert.ToDecimal(User.Identity.GetFieldValue("IdEntidad"));
                    aetent taetent = bFirstEntidades.aetent.Find(idEntidad);
                    //decimal IdTipoDireccion = bFirstEntidades.aebdirt.Where(x => x.cdirtip == "FISCAL").Select(s => s.idirtip).FirstOrDefault();
                    //decimal IdDireccion = bFirstEntidades.aetentdir.Where(x => x.idirtip == IdTipoDireccion && x.ient == idEntidad).Select(s => s.idir).FirstOrDefault();
                    //aebdir taebdir = bFirstEntidades.aebdirs.Where(x => x.idir == IdDireccion).FirstOrDefault();
                    //aebpai taebpai = bFirstEntidades.aebpais.Where(x => x.ipai == taebdir.ipai).FirstOrDefault();
                    //aebren taebren = bFirstEntidades.aebren.Where(x => x.iren == taebdir.iren).FirstOrDefault();
                    //aebpro taebpro = bFirstEntidades.aebpro.Where(x => x.ipro == taebdir.ipro).FirstOrDefault();
                    //string poblacionProvincia = string.Empty;
                    //string regionPais = string.Empty;
                                      
                    ViewBag.Entidad = taetent.cent;
                    ViewBag.Direccion = "990 NW 49th Ave";
                    ViewBag.PoblacionProvincia = "MIAMI";
                    ViewBag.RegionPais = "FL 33126, EE. UU";

                    //ViewBag.Direccion = taebdir.ddir;

                    //if (!string.IsNullOrWhiteSpace(taebdir.dpob))
                    //{
                    //    ViewBag.PoblacionProvincia = taebdir.dpob;
                    //}
                    //if(taebpro!= null && !string.IsNullOrWhiteSpace(taebpro.dpro))
                    //{
                    //    if (string.IsNullOrWhiteSpace(taebdir.dpob))
                    //    {
                    //        ViewBag.PoblacionProvincia = taebpro.dpro;
                    //    }else
                    //    {
                    //        ViewBag.PoblacionProvincia = string.Format("{0}, ({1}) ", taebdir.dpob, taebpro.dpro);
                    //    }
                    //}

                    //if (taebren != null && !string.IsNullOrWhiteSpace(taebren.dren))
                    //{
                    //    ViewBag.RegionPais = string.Format("{0}, {1}", taebren.dren, taebpai.dpai);
                    //}else
                    //{
                    //    ViewBag.RegionPais = taebpai.dpai;
                    //}

                }
                catch
                {

                }
                
            }
            return View($"{RutaVista}/ComoFunciona.cshtml");
        }
        [LocalizedRoute("~/opciones_pago")]
        [AllowAnonymous]
        public ActionResult OpcionesPago()
        {
            return View($"{RutaVista}/OpcionesPago.cshtml");
        }



        [Authorize]
        [LocalizedRoute("~/contactanos")]
        [AllowAnonymous]
        public ActionResult Contactanos()
        {
            return View($"{RutaVista}/Contactanos.cshtml");
        }
        [LocalizedRoute("~/contactanos")]
        [HttpPost]
        [AllowAnonymous]
        //[ValidateAntiForgeryToken]
        public async Task<ActionResult> Contactanos(ContactanosViewModel model)
        {
            try
            {


                if (ModelState.IsValid)

                {
                    
                    SMTP objSMTP = new SMTP();
                    string texto = EnviarContactanos(model.Nombre, model.Apellidos, model.Telefono, model.Email, model.Consulta);
                    objSMTP.EsHTML = true;
                    objSMTP.EnviarMail(model.Email, texto, Resources.Resource.asunto_contactanos, true);
                    model.EmailEnviado = true;
                }
            }
            catch
            {
                model.EmailEnviado = false;
            }

            return View(model);

        }


        private string EnviarContactanos(string nom, string apell, string telef, string email, string consul)
        {
            System.Text.StringBuilder stbRetorno = new System.Text.StringBuilder();
            string strImagen = "https://descargas.bytemaster.es/ImagenPieCorreoBOX.png";

            stbRetorno.AppendLine("<html>");
            stbRetorno.AppendLine("<table width='100%'>");

            stbRetorno.AppendLine("<tr>");
            stbRetorno.AppendLine("<td  colspan='3'><font face='verdana' size ='4' color='0060A9'>");

            stbRetorno.AppendFormat(  "<p>" + Resources.Resource.hola + " {0}, </p>", nom).AppendLine();
            stbRetorno.AppendLine("<p>" + Resources.Resource.solicitud_recibida + "</p>");
            //stbRetorno.AppendLine("<p>Nos pondremos en contacto con usted a la mayor brevedad posible.</p>");

            stbRetorno.AppendLine("<p>" + Resources.Resource.revisar_consulta + "</p>");
            
            stbRetorno.AppendLine("<p>" + Resources.Resource.detalles_consulta + "</p>");
            stbRetorno.AppendFormat("<p>"+Resources.Resource.nombre + ": {0} </p>", nom).AppendLine();
            stbRetorno.AppendFormat("<p>" + Resources.Resource.apellidos + ": {0} </p>", apell).AppendLine();
            stbRetorno.AppendFormat("<p>" + Resources.Resource.telefono + ": {0} </p>", telef).AppendLine();
            stbRetorno.AppendFormat("<p>Email: {0} </p>", email).AppendLine();
            stbRetorno.AppendFormat("<p>" + Resources.Resource.consulta + ": {0} </p>", consul).AppendLine();
            stbRetorno.AppendLine("<p>" + Resources.Resource.gracias + "</p>");
            stbRetorno.AppendLine("</font></td>");
            stbRetorno.AppendLine("</tr>");
            stbRetorno.AppendLine("<tr  height='100'>");
            stbRetorno.AppendFormat("<td colspan='3'><img src='{0}'></td>", strImagen);
            stbRetorno.AppendLine("</tr>");
            stbRetorno.AppendLine("</table>");
            stbRetorno.AppendLine("</font>");
            stbRetorno.AppendLine("</html>");

            return stbRetorno.ToString();
        }


        [LocalizedRoute("~/smart_lockers")]
        [AllowAnonymous]
        public ActionResult SmartLockers()
        {
            return View($"{RutaVista}/SmartLockers.cshtml");
        }


        /*Nuestras Tarifas*/

        [LocalizedRoute("~/nuestras_tarifas")]
        [AllowAnonymous]
        public ActionResult NuestrasTarifas()
        {
            return View($"{RutaVista}/NuestrasTarifas.cshtml");
        }


        [LocalizedRoute("~/puntos_entrega")]
        [AllowAnonymous]
        public ActionResult PuntosEntrega()
        {

            var copaBoxEntities = new CopaCursoEntities();
            List<PuntoEntrega> listaLockers = copaBoxEntities.tdaptoetrs.Where(x => x.tptoetr == "L").Select(x => new PuntoEntrega
            {
                cptoetr = x.cptoetr,
                tptoetr = x.tptoetr,
                dptoetr = x.dptoetr,
                latptoetr = string.Empty,
                lonptoetr = string.Empty,
                ddir = x.aebdir.ddir,
                cdir = x.aebdir.cdir,
                ddi2 = x.aebdir.ddi2,
                dpob = x.aebdir.dpob
            }).ToList();

            foreach (PuntoEntrega locker in listaLockers)
            {
                ObtieneCoordenadas(locker);
            }

            var list = JsonConvert.SerializeObject(listaLockers, Formatting.None, new JsonSerializerSettings()
            {
                ReferenceLoopHandling = ReferenceLoopHandling.Ignore
            });

            ViewBag.ListaLockers = list;
            ViewBag.ListaOficinas = GetOficinas();

            return View($"{RutaVista}/PuntosEntrega.cshtml");
        }
        [AllowAnonymous]
        public string GetOficinas()
        {
            var copaBoxEntities = new CopaCursoEntities();
            List<PuntoEntrega> listaOficinas = copaBoxEntities.tdaptoetrs.Where(x => x.tptoetr == "O").Select(x => new PuntoEntrega
            {
                cptoetr = x.cptoetr,
                tptoetr = x.tptoetr,
                dptoetr = x.dptoetr,
                latptoetr = string.Empty,
                lonptoetr = string.Empty,
                ddir = x.aebdir.ddir,
                cdir = x.aebdir.cdir,
                ddi2 = x.aebdir.ddi2,
                dpob = x.aebdir.dpob
            }).ToList();

            foreach (PuntoEntrega oficina in listaOficinas)
            {
                ObtieneCoordenadas(oficina);
            }
           
            var list = JsonConvert.SerializeObject(listaOficinas, Formatting.None, new JsonSerializerSettings()
            {
                ReferenceLoopHandling = ReferenceLoopHandling.Ignore
            });

            return list;
        }
        private void ObtieneCoordenadas(PuntoEntrega cPuntoEntrega)
        {
            try
            {
                string addr = string.Format("{0} {1} {2}", cPuntoEntrega.ddir, cPuntoEntrega.ddi2, cPuntoEntrega.dpob);
                string url = "https://maps.googleapis.com/maps/api/geocode/json?address=" + addr + "&key=AIzaSyDFDX9b1wFD62LaRvtOcBJ3owsPZzCtYms";
                WebRequest request = WebRequest.Create(url);
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    MemoryStream ms = new MemoryStream();
                    Stream responseStream = response.GetResponseStream();
                    byte[] buffer = new byte[2048];
                    int count = responseStream.Read(buffer, 0, buffer.Length);
                    while (count > 0) {
                        ms.Write(buffer, 0, count);
                        count = responseStream.Read(buffer, 0, buffer.Length);
                    }
                    responseStream.Close();
                    ms.Close();
                    byte[] responseBytes = ms.ToArray();
                    System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
                    string coords = encoding.GetString(responseBytes);
                    JavaScriptSerializer jss = new JavaScriptSerializer();
                    dynamic obj = jss.Deserialize<dynamic>(coords);
                    double lat = 0;
                    double lng = 0;

                    if ( obj != null && obj["results"][0].Count > 0){
                        lat = double.Parse(obj["results"][0]["geometry"]["location"]["lat"].ToString());
                        lng = double.Parse(obj["results"][0]["geometry"]["location"]["lng"].ToString());
                    }

                    cPuntoEntrega.latptoetr = lat.ToString();
                    cPuntoEntrega.lonptoetr = lng.ToString();

                }
            }
            catch(Exception ex)
            {

            }
        }
        private class PuntoEntrega
        {
            public string cptoetr { get; set; }
            public string tptoetr { get; set; }
            public string dptoetr { get; set; }
            public string latptoetr { get; set; }
            public string lonptoetr { get; set; }
            public string ddir { get; set; }
            public string cdir { get; set; }
            public string ddi2 { get; set; }
            public string dpob { get; set; }
        }
        [LocalizedRoute("~/preguntas_frecuentes")]
        [AllowAnonymous]
        public ActionResult PreguntasFrecuentes()
        {
            if (User.Identity.IsAuthenticated)
            {
                try
                {
                    CopaCursoEntities bFirstEntidades = new CopaCursoEntities();
                    decimal idEntidad = Convert.ToDecimal(User.Identity.GetFieldValue("IdEntidad"));
                    aetent taetent = bFirstEntidades.aetent.Find(idEntidad);
                    //decimal IdTipoDireccion = bFirstEntidades.aebdirt.Where(x => x.cdirtip == "FISCAL").Select(s => s.idirtip).FirstOrDefault();
                    //decimal IdDireccion = bFirstEntidades.aetentdir.Where(x => x.idirtip == IdTipoDireccion && x.ient == idEntidad).Select(s => s.idir).FirstOrDefault();
                    //aebdir taebdir = bFirstEntidades.aebdirs.Where(x => x.idir == IdDireccion).FirstOrDefault();
                    //aebpai taebpai = bFirstEntidades.aebpais.Where(x => x.ipai == taebdir.ipai).FirstOrDefault();
                    //aebren taebren = bFirstEntidades.aebren.Where(x => x.iren == taebdir.iren).FirstOrDefault();
                    //aebpro taebpro = bFirstEntidades.aebpro.Where(x => x.ipro == taebdir.ipro).FirstOrDefault();
                    //string poblacionProvincia = string.Empty;
                    //string regionPais = string.Empty;

                    ViewBag.Entidad = taetent.cent;
                    ViewBag.Direccion = "990 NW 49th Ave";
                    ViewBag.PoblacionProvincia = "MIAMI";
                    ViewBag.RegionPais = "FL 33126, EE. UU";
                    //ViewBag.Direccion = taebdir.ddir;

                    //if (!string.IsNullOrWhiteSpace(taebdir.dpob))
                    //{
                    //    ViewBag.PoblacionProvincia = taebdir.dpob;
                    //}
                    //if (taebpro != null && !string.IsNullOrWhiteSpace(taebpro.dpro))
                    //{
                    //    if (string.IsNullOrWhiteSpace(taebdir.dpob))
                    //    {
                    //        ViewBag.PoblacionProvincia = taebpro.dpro;
                    //    }
                    //    else
                    //    {
                    //        ViewBag.PoblacionProvincia = string.Format("{0}, ({1}) ", taebdir.dpob, taebpro.dpro);
                    //    }
                    //}

                    //if (taebren != null && !string.IsNullOrWhiteSpace(taebren.dren))
                    //{
                    //    ViewBag.RegionPais = string.Format("{0}, {1}", taebren.dren, taebpai.dpai);
                    //}
                    //else
                    //{
                    //    ViewBag.RegionPais = taebpai.dpai;
                    //}

                }
                catch
                {

                }

            }
            return View($"{RutaVista}/PreguntasFrecuentes.cshtml");
        }
        [LocalizedRoute("~/ofertas")]
        [AllowAnonymous]
        public ActionResult Ofertas()
        {
            return View($"{RutaVista}/Ofertas.cshtml");
        }
        [LocalizedRoute("~/lo_compramos_por_ti")]
        [Authorize]
        public ActionResult LoCompramosPorTi()
        {
            return View($"{RutaVista}/LoCompramosPorTi.cshtml");
        }
        [LocalizedRoute("~/lo_compramos_por_ti")]
        [HttpPost]
        [Authorize]
        public async Task <ActionResult> LoCompramosPorTi(LoCompramosPorTiViewModels model)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    string UserId = User.Identity.GetFieldValue("UserId");
                    CopaBoxEntities cbdb = new CopaBoxEntities();
                    AspNetUser objUsuario = cbdb.AspNetUsers.Where(x => x.Id == UserId).FirstOrDefault();
                    if(objUsuario != null)
                    {
                        SMTP objSMTP = new SMTP();
                        string nombre = string.Format("{0} {1} {2}", objUsuario.Nombre, objUsuario.SegundoNombre, objUsuario.Apellidos);
                        string texto = CorreoLoCompramosPorTi(nombre, model);
                        objSMTP.EsHTML = true;
                        objSMTP.EnviarMail(objUsuario.Email, texto, Resources.Resource.asunto_compramos_por_ti, true);
                        model.EmailEnviado = true;
                    }
                }
            }
            catch
            {
                model.EmailEnviado = false;
            }

            return View(model);
            
        }
        private string CorreoLoCompramosPorTi(string cliente, LoCompramosPorTiViewModels model)
        {

            System.Text.StringBuilder stbRetorno = new System.Text.StringBuilder();
            string strImagen = "https://descargas.bytemaster.es/ImagenPieCorreoBOX.png";

            stbRetorno.AppendLine("<html>");
            stbRetorno.AppendLine("<table width='100%'>");

            stbRetorno.AppendLine("<tr>");
            stbRetorno.AppendLine("<td  colspan='3'><font face='verdana' size ='4' color='0060A9'>");

            stbRetorno.AppendFormat("<p>"+Resources.Resource.hola + " {0}, </p>", cliente).AppendLine();
            stbRetorno.AppendLine("<p>"+Resources.Resource.recibida_correctamente+"</p>");
            stbRetorno.AppendLine("<p>"+Resources.Resource.facilitar_datos+"</p>");
            stbRetorno.AppendFormat("<p>"+Resources.Resource.enlace_web +": <a href = '{0}'>{0}</a></p>", model.EnlaceWeb).AppendLine();
            stbRetorno.AppendFormat("<p>"+Resources.Resource.precio_impuesto +": ${0}</p>", model.Precio).AppendLine();
            stbRetorno.AppendFormat("<p>"+Resources.Resource.unidades +": {0}</p>", model.Unidades).AppendLine();
            stbRetorno.AppendLine("<p>"+Resources.Resource.descripcion+": </p>");
            stbRetorno.AppendFormat("<p>{0}</p>", model.Descripcion);
            if (!string.IsNullOrWhiteSpace(model.Observaciones))
            {
                stbRetorno.AppendLine("<p>"+Resources.Resource.observaciones+": </p>");
                stbRetorno.AppendFormat("<p>{0}</p>", model.Observaciones);
            }
            stbRetorno.AppendLine("<p>"+Resources.Resource.su_solicitud+"</p>");
            stbRetorno.AppendLine("<p>"+Resources.Resource.gracias+"</p>");
            stbRetorno.AppendLine("</font></td>");

            stbRetorno.AppendLine("</tr>");
            stbRetorno.AppendLine("<tr  height='100'>");
            stbRetorno.AppendFormat("<td colspan='3'><img src='{0}'></td>", strImagen);
            stbRetorno.AppendLine("</tr>");

            stbRetorno.AppendLine("</table>");
            stbRetorno.AppendLine("</font>");
            stbRetorno.AppendLine("</html>");

            return stbRetorno.ToString();
        }
        [Authorize]
        public ActionResult ComprobarEnlaceWeb(string EnlaceWeb)
        {
            string strRetorno = string.Empty;
            bool blnError = false;
            var culture = CultureInfo.CurrentCulture;
            try
            {
                if (EnlaceWeb != null)
                {
                    Uri url;
                    blnError = !Uri.TryCreate(EnlaceWeb, UriKind.Absolute, out url);

                    if (url != null)
                    {
                        WebRequest request = WebRequest.Create(url);
                        HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                        blnError = response.StatusCode != HttpStatusCode.OK;
                    }
                }
                else
                {
                    blnError = true;
                }
                if (blnError)
                {
                    if (culture.Name.ToLower() == "es")
                    {
                        strRetorno = "El enlace web no es correcto";
                    }
                    else
                    {
                        strRetorno = "Invalid URL";
                    }
                    return Json(strRetorno, JsonRequestBehavior.AllowGet);
                }
                else
                {
                    return Json(true, JsonRequestBehavior.AllowGet);
                }
            }
            catch(Exception ex)
            {
                return Json(strRetorno, JsonRequestBehavior.AllowGet);
            }
        }
        [LocalizedRoute("~/contrasena_olvidada")]
        [AllowAnonymous]
        public ActionResult ContrasenaOlvidada()
        {         
            return View($"{RutaVista}/ContrasenaOlvidada.cshtml");
        }
        [LocalizedRoute("~/contrasena_olvidada")]
        [HttpPost]
        [AllowAnonymous]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> ContrasenaOlvidada(ForgotViewModel model)
        {
            try
            {
                CopaBoxEntities cbdb = new CopaBoxEntities();
                AspNetUser objUsuario = cbdb.AspNetUsers.Where(x => x.Email.ToLower() == model.Email.ToLower()).FirstOrDefault();

                if(objUsuario != null)
                {
                    model.EmailRegistrado = true;
                    string PASS_CHARS_MINUSCULAS = "abcdefghijkmnopqrstuvwxyz";
                    string PASS_CHARS_MAYUSCULAS = "ABCDEFGHJKLMNPQRSTUVWXYZ";
                    string PASS_CHARS_NUMEROS = "1234567890";
                    System.Text.StringBuilder stbPass = new System.Text.StringBuilder();
                    PasswordHasher hadsher = new PasswordHasher();
                    Random rnd = new Random();
                    for (int i = 0; i < 10; i++)
                    {                        
                        int indexMin = rnd.Next(0, PASS_CHARS_MINUSCULAS.Length);
                        int indexMax = rnd.Next(0, PASS_CHARS_MAYUSCULAS.Length);
                        int indexNum = rnd.Next(0, PASS_CHARS_NUMEROS.Length);

                        string strMin = PASS_CHARS_MINUSCULAS.Substring(indexMin, 1);
                        string strMax = PASS_CHARS_MAYUSCULAS.Substring(indexMax, 1);
                        string strNum = PASS_CHARS_NUMEROS.Substring(indexNum, 1);

                        stbPass.AppendFormat("{0}{1}{2}", strMin, strMax, strNum);
                    }

                    objUsuario.PasswordHash = hadsher.HashPassword(stbPass.ToString());
                    cbdb.SaveChanges();
                    string nombre = string.Format("{0} {1} {2}", objUsuario.Nombre, objUsuario.SegundoNombre, objUsuario.Apellidos);
                    string texto = CorreoElectronicoRecuperarContrasena(nombre, model.Email, stbPass.ToString());

                    SMTP objSMTP = new SMTP();

                    objSMTP.EsHTML = true;
                    objSMTP.EnviarMail(model.Email, texto, Resources.Resource.asunto_recuperar_contrasena, true);
                    model.EmailEnviado = true;
                }
                else{
                    model.EmailRegistrado = false;
                }              
            }
            catch (System.Exception ex)
            {

            }
            return View(model);
        }
        private string CorreoElectronicoRecuperarContrasena(string cliente, string email,string pass) {

            System.Text.StringBuilder stbRetorno = new System.Text.StringBuilder();
            string strImagen = "https://descargas.bytemaster.es/ImagenPieCorreoBOX.png";

            stbRetorno.AppendLine("<html>");
            stbRetorno.AppendLine("<table width='100%'>");

            stbRetorno.AppendLine("<tr>");
            stbRetorno.AppendLine("<td  colspan='3'><font face='verdana' size ='4' color='0060A9'>");

            stbRetorno.AppendFormat("<p>" + Resources.Resource.hola + " {0}, </p>", cliente).AppendLine();
            stbRetorno.AppendLine("<p>"+Resources.Resource.contrasena_restablecida+"</p>");
            stbRetorno.AppendFormat("<p>"+Resources.Resource.usuario+": {0}", email).AppendLine();
            stbRetorno.AppendFormat("<p>"+Resources.Resource.contrasena+": {0}", pass).AppendLine();
            stbRetorno.AppendLine("<p>"+Resources.Resource.gracias+"</p>");
            stbRetorno.AppendLine("</font></td>");

            stbRetorno.AppendLine("</tr>");
            stbRetorno.AppendLine("<tr  height='100'>");
            stbRetorno.AppendFormat("<td colspan='3'><img src='{0}'></td>", strImagen);
            stbRetorno.AppendLine("</tr>");

            stbRetorno.AppendLine("</table>");
            stbRetorno.AppendLine("</font>");
            stbRetorno.AppendLine("</html>");

            return stbRetorno.ToString();
        }
        [HttpGet]
        public ActionResult ConsultarTracking(string numeroguia1)
        {

            var rootObject = new JObject()
            {
                new JProperty("tracking", JObject.Parse("{}"))
            };

            try
            {
                System.Text.StringBuilder stbSQL = new System.Text.StringBuilder();

                stbSQL.AppendLine("select top 1 ctdahaw as Guia, ori.dpue as Origen, ");
                stbSQL.AppendLine("       (select top 1 convert(varchar(100),fhit,106) from aebtrk");
                stbSQL.AppendLine("        INNER JOIN aebhit ON aebhit.ihit = aebtrk.ihit ");
                stbSQL.AppendLine("        where aebtrk.creg = tdahaw.itdahaw ");
                stbSQL.AppendLine("          and aebtrk.dtab = 'tdahaw' ");
                stbSQL.AppendLine("          and aebhit.chit = 'HAWB') as FechaRecibido, ");
                stbSQL.AppendLine("       (select top 1 convert(varchar(100),fhit,106) from aebtrk ");
                stbSQL.AppendLine("        INNER JOIN aebhit ON aebhit.ihit = aebtrk.ihit ");
                stbSQL.AppendLine("        where aebtrk.creg = tdahaw.itdahaw ");
                stbSQL.AppendLine("          and aebtrk.dtab = 'tdahaw' ");
                stbSQL.AppendLine("          and aebhit.chit = 'ASIHAWMAW') as FechaTransito, ");
                stbSQL.AppendLine("          des.dpue as Destino,");
                stbSQL.AppendLine("          tdahaw.itdahaw as IdHaw,");
                stbSQL.AppendLine("       (select top 1 convert(varchar(100),fhit,106) from aebtrk ");
                stbSQL.AppendLine("        INNER JOIN aebhit ON aebhit.ihit = aebtrk.ihit ");
                stbSQL.AppendLine("        where aebtrk.creg = tdahaw.itdahaw ");
                stbSQL.AppendLine("          and aebtrk.dtab = 'tdahaw' ");
                stbSQL.AppendLine("          and aebhit.chit = 'DESTINO') as FechaRetiro ");
                stbSQL.AppendLine("from tdahaw");
                stbSQL.AppendLine("INNER JOIN aebpue ori ON ori.ipue = tdahaw.ipueori");
                stbSQL.AppendLine("INNER JOIN taeser ON taeser.iser = tdahaw.iser");
                stbSQL.AppendLine("INNER JOIN aebpue des ON des.ipue = tdahaw.ipuedes");
                stbSQL.AppendLine("INNER JOIN tdaprehaw ON tdahaw.itdahaw = tdaprehaw.itdahaw");
                stbSQL.AppendLine("where tdahaw.ttdahaw = 'H'");
                stbSQL.AppendLine("and taeser.cser = 'BOX'");
                stbSQL.AppendFormat("and (tdahaw.ctdahaw = '{0}' OR tdaprehaw.nrefprehaw = '{0}' OR ctdaprehaw = '{0}')", numeroguia).AppendLine();
                stbSQL.AppendLine("union all");
                stbSQL.AppendLine("SELECT ctdaprehaw as Guia,");
                stbSQL.AppendLine("'MIAMI' AS Origen,");
                stbSQL.AppendLine("convert(varchar(100),fprehaw,106) AS FechaRecibido, ");
                stbSQL.AppendLine("NULL AS FechaTransito,");
                stbSQL.AppendLine("NULL AS Destino,");
                stbSQL.AppendLine("NULL AS IdHaw,");
                stbSQL.AppendLine("NULL AS FechaRetiro");
                stbSQL.AppendLine("FROM tdaprehaw");
                stbSQL.AppendFormat("WHERE ctdaprehaw = '{0}' OR nrefprehaw = '{0}'", numeroguia).AppendLine();

                CopaCursoEntities dbbf = new CopaCursoEntities();
                ConsultaTracking consultaTracking = dbbf.Database.SqlQuery<ConsultaTracking>(stbSQL.ToString()).FirstOrDefault();

                if (consultaTracking != null)
                {
                    rootObject["tracking"] = JToken.FromObject(consultaTracking);
                }         
            }
            catch (Exception e)
            {
                var tokenError = JToken.FromObject(e);
                rootObject["error"] = JToken.FromObject(e);
            }

            return new ContentResult
            {
                Content = rootObject.ToString(),
                ContentType = "application/json"
            };
        }

        [HttpGet]
        public ActionResult DetallesTracking(string numeroguia)
        {
            var rootObject = new JObject()
            {
                new JProperty("tracking", JObject.Parse("{}"))
            };

            try
            {
                System.Text.StringBuilder stbSQL = new System.Text.StringBuilder();

                stbSQL.AppendLine("SELECT convert(varchar(100),fhit,106) as Fecha, ");
                stbSQL.AppendLine("hhit as Hora, ");
                stbSQL.AppendLine("dtrk as Tracking, ");
                stbSQL.AppendLine("hit.dhit as Descripcion ");
                stbSQL.AppendLine("FROM aebtrk trk ");
                stbSQL.AppendLine("INNER JOIN aebhit hit on trk.ihit = hit.ihit ");
                stbSQL.AppendFormat("WHERE creg= = '{0}' and dtab='tdahaw'", numeroguia).AppendLine();
                stbSQL.AppendLine("ORDER BY Fecha ASC ");


                CopaCursoEntities dbbf = new CopaCursoEntities();
                DetallesTracking detallesTracking = dbbf.Database.SqlQuery<DetallesTracking>(stbSQL.ToString()).FirstOrDefault();

                if (detallesTracking != null)
                {
                    rootObject["tracking"] = JToken.FromObject(detallesTracking);
                }
            }
            catch (Exception e)
            {
                var tokenError = JToken.FromObject(e);
                rootObject["error"] = JToken.FromObject(e);
            }

            return new ContentResult
            {
                Content = rootObject.ToString(),
                ContentType = "application/json"
            };







        }


        [AllowAnonymous]
        public PartialViewResult UserResources()
        {
            Response.ContentType = "text/javascript;charset=UTF-8";
            return PartialView();
        }

    }
}