List <guiaRemision> fx_GeneradorXML_IGuia.GetGuiaLogistica(DateTime FechaIni, DateTime FechaFin, string ConexionDBCliente, string OconEfirm, string FormatoFechaSRI, string formatoFechaDB) { DateTime Fi = Convert.ToDateTime(FechaIni.ToShortDateString()); DateTime Ff = Convert.ToDateTime(FechaFin.ToShortDateString()); string sFi, sFf; sFi = string.Format(formatoFechaDB, Fi); sFf = string.Format(formatoFechaDB, Ff); try { List <guiaRemision> lista = new List <guiaRemision>(); using (SqlConnection conexion = new SqlConnection(ConexionDBCliente)) { conexion.Open(); string sqlcabFact = "SELECT gui.IdGuia, gui.IdEmpacadora, gui.IdVehiculo, gui.IdChofer, gui.IdPlanificacion,cast( gui.Fecha as date)Fecha, gui.Hora, cat_ori.Descripcion AS cat_ori, cat_des.Descripcion AS cat_des, gui.CodDocumentoTipo, gui.Establecimiento, " + " gui.PuntoEmision, gui.NumDocumento, gui.Observacion, gui.CapacidadLibrasTotal, gui.CapacidadLibrasProg, gui.CapacidadBines, gui.ValorFlete, gui.ValorPorLibra, gui.ValorAFacturar, emp.Descripcion AS emp, " + " emp.Direccion AS dir_emp, veh.Placa, chof.pe_cedulaRuc AS CedRucChof, chof.pe_nombreCompleto AS Chofer, dbo.tb_persona.pe_cedulaRuc AS CedRuccli, dbo.tb_persona.pe_direccion AS DirCli, " + " dbo.tb_persona.pe_nombreCompleto AS RazSocCli, em_ruc, RazonSocial, em_direccion,cat_tipo.Descripcion " + " FROM Logistica.lo_Guia AS gui INNER JOIN " + " Logistica.lo_Empacadora AS emp ON gui.IdEmpacadora = emp.IdEmpacadora INNER JOIN " + " Logistica.lo_chofer AS chof ON gui.IdChofer = chof.IdChofer INNER JOIN " + " Logistica.lo_vehiculo AS veh ON gui.IdVehiculo = veh.IdVehiculo INNER JOIN " + " Logistica.lo_catalogo AS cat_ori ON gui.IdCatalogoOrigen = cat_ori.IdCatalogo INNER JOIN " + " Logistica.lo_catalogo AS cat_des ON gui.IdCatalogoDestino = cat_des.IdCatalogo INNER JOIN " + " dbo.fa_cliente ON emp.IdEmpresa = dbo.fa_cliente.IdEmpresa AND emp.IdCliente = dbo.fa_cliente.IdCliente INNER JOIN " + " dbo.tb_persona ON dbo.fa_cliente.IdPersona = dbo.tb_persona.IdPersona AND dbo.fa_cliente.IdPersona = dbo.tb_persona.IdPersona AND dbo.fa_cliente.IdPersona = dbo.tb_persona.IdPersona AND " + " dbo.fa_cliente.IdPersona = dbo.tb_persona.IdPersona AND dbo.fa_cliente.IdPersona = dbo.tb_persona.IdPersona INNER JOIN " + " dbo.tb_empresa ON emp.IdEmpresa = dbo.tb_empresa.IdEmpresa INNER JOIN " + " Logistica.lo_catalogo AS cat_tipo ON gui.IdCatalogoTipoGuia = cat_tipo.IdCatalogo AND gui.IdCatalogoTipoGuia = cat_tipo.IdCatalogo " + " where Fecha between datefromparts(" + FechaIni.Year.ToString() + "," + FechaIni.Month.ToString() + "," + FechaIni.Day.ToString() + ") and datefromparts(" + FechaFin.Year.ToString() + "," + FechaFin.Month.ToString() + "," + FechaFin.Day.ToString() + ") and generado is null"; SqlCommand cmd_cab = new SqlCommand(sqlcabFact, conexion); SqlDataReader dtr_cab = cmd_cab.ExecuteReader(); while (dtr_cab.Read()) { #region cabecera guia decimal IdGuia = Convert.ToDecimal(dtr_cab.GetValue(0)); guiaRemision myObject = new guiaRemision(); myObject.version = "1.1.0"; myObject.id = guiaRemisionID.comprobante; infoTributaria info = new infoTributaria(); myObject.infoGuiaRemision = new guiaRemisionInfoGuiaRemision(); destinatario destinatario = new Info.class_sri.GuiaRemision.destinatario(); myObject.infoTributaria = info; myObject.destinatarios = new guiaRemisionDestinatarios(); info.ambiente = "1"; myObject.infoTributaria.tipoEmision = "1"; myObject.infoTributaria.razonSocial = dtr_cab.GetValue(29).ToString(); myObject.infoTributaria.nombreComercial = dtr_cab.GetValue(29).ToString(); myObject.infoTributaria.ruc = dtr_cab.GetValue(28).ToString(); myObject.infoTributaria.claveAcceso = "0000000000000000000000000000000000000000000000000"; //********************************************************************************* myObject.infoTributaria.codDoc = "06"; myObject.infoTributaria.estab = dtr_cab.GetValue(10).ToString(); myObject.infoTributaria.ptoEmi = dtr_cab.GetValue(11).ToString(); myObject.infoTributaria.secuencial = dtr_cab.GetValue(12).ToString(); myObject.infoTributaria.dirMatriz = dtr_cab.GetValue(30).ToString(); myObject.infoGuiaRemision.dirEstablecimiento = dtr_cab.GetValue(30).ToString(); myObject.infoGuiaRemision.dirPartida = dtr_cab.GetValue(7).ToString(); myObject.infoGuiaRemision.razonSocialTransportista = dtr_cab.GetValue(24).ToString(); myObject.infoGuiaRemision.rucTransportista = dtr_cab.GetValue(23).ToString(); myObject.infoGuiaRemision.placa = dtr_cab.GetValue(11).ToString(); if (dtr_cab.GetValue(23).ToString().Length == 10) { myObject.infoGuiaRemision.tipoIdentificacionTransportista = "05"; } if (dtr_cab.GetValue(23).ToString().Length == 13) { myObject.infoGuiaRemision.tipoIdentificacionTransportista = "04"; } DateTime fecha = Convert.ToDateTime(dtr_cab.GetValue(5)); string Fe = fecha.Day.ToString().PadLeft(2, '0') + "/" + fecha.Month.ToString().PadLeft(2, '0') + "/" + fecha.Year.ToString(); myObject.infoGuiaRemision.fechaIniTransporte = string.Format(FormatoFechaSRI, Fe); myObject.infoGuiaRemision.fechaFinTransporte = string.Format(FormatoFechaSRI, Fe); //if (item.ContribuyenteEspecial != "") // myObject.infoGuiaRemision.contribuyenteEspecial = item.ContribuyenteEspecial; myObject.destinatarios.destinatario = new List <destinatario>(); destinatario.identificacionDestinatario = dtr_cab.GetValue(25).ToString(); destinatario.razonSocialDestinatario = dtr_cab.GetValue(27).ToString(); destinatario.dirDestinatario = dtr_cab.GetValue(26).ToString(); destinatario.motivoTraslado = dtr_cab.GetValue(31).ToString(); destinatario.codEstabDestino = "001"; destinatario.ruta = "S/N"; myObject.destinatarios.destinatario.Add(destinatario); #endregion #region detalle guia string sql_detalle = "SELECT Logistica.lo_GuiaDet.IdGuiaDet, Logistica.lo_GuiaDet.IdGuia, Logistica.lo_GuiaDet.IdRecurso, Logistica.lo_GuiaDet.Cantidad, Logistica.lo_recurso.NomRecurso, Logistica.lo_recurso.ControlaSerie, " + "Logistica.lo_recurso.ControlaKardex, Logistica.lo_recurso.CantidadPorDefecto " + "FROM Logistica.lo_GuiaDet INNER JOIN " + "Logistica.lo_recurso ON Logistica.lo_GuiaDet.IdRecurso = Logistica.lo_recurso.IdRecurso" + " where Logistica.lo_GuiaDet.IdGuia=" + IdGuia + ""; destinatario.detalles = new destinatarioDetalles(); destinatario.detalles.detalle = new List <detalle>(); using (SqlConnection conexion2 = new SqlConnection(ConexionDBCliente)) { conexion2.Open(); SqlCommand cmd_det = new SqlCommand(sql_detalle, conexion2); SqlDataReader dtr_det = cmd_det.ExecuteReader(); while (dtr_det.Read()) { destinatario.detalles.detalle.Add(new detalle { codigoAdicional = dtr_det.GetValue(2).ToString(), codigoInterno = dtr_det.GetValue(2).ToString(), descripcion = dtr_det.GetValue(4).ToString(), cantidad = Convert.ToDecimal(dtr_det.GetValue(3).ToString()), }); } } #endregion lista.Add(myObject); #region actaulizar a registro generado string sqlUpdate = "update Logistica.lo_Guia set generado=1 where IdGuia=" + IdGuia + ""; using (SqlConnection conexion3 = new SqlConnection(ConexionDBCliente)) { conexion3.Open(); SqlCommand cmdupdate = new SqlCommand(sqlUpdate, conexion3); SqlDataReader dtrupdate = cmdupdate.ExecuteReader(); } #endregion } } return(lista); } catch (Exception ex) { return(new List <guiaRemision>()); } }
List <guiaRemision> fx_GeneradorXML_IGuia.GetGuiaEfixed(DateTime FechaIni, DateTime FechaFin, string ConexionDBCliente, string OconEfirm, string FormatoFechaSRI, string formatoFechaDB) { DateTime Fi = Convert.ToDateTime(FechaIni.ToShortDateString()); DateTime Ff = Convert.ToDateTime(FechaFin.ToShortDateString()); string sFi, sFf; sFi = string.Format(formatoFechaDB, Fi); sFf = string.Format(formatoFechaDB, Ff); try { List <guiaRemision> lista = new List <guiaRemision>(); using (Entity_conexion_efixed context = new Entity_conexion_efixed()) { var guiaRemisions = context.vwfe_guia_remision.Where(v => v.gi_fecha >= Fi && v.gi_fecha <= Ff).ToList(); foreach (var item in guiaRemisions) { guiaRemision myObject = new guiaRemision(); myObject.version = "1.1.0"; myObject.id = guiaRemisionID.comprobante; infoTributaria info = new infoTributaria(); myObject.infoGuiaRemision = new guiaRemisionInfoGuiaRemision(); destinatario destinatario = new Info.class_sri.GuiaRemision.destinatario(); myObject.infoTributaria = info; myObject.destinatarios = new guiaRemisionDestinatarios(); info.ambiente = "1"; myObject.infoTributaria.tipoEmision = "1"; myObject.infoTributaria.razonSocial = item.RazonSocial; myObject.infoTributaria.nombreComercial = item.NombreComercial; myObject.infoTributaria.ruc = item.em_ruc; myObject.infoTributaria.claveAcceso = "0000000000000000000000000000000000000000000000000"; //********************************************************************************* myObject.infoTributaria.codDoc = "06"; myObject.infoTributaria.estab = item.Serie1; myObject.infoTributaria.ptoEmi = item.Serie2; myObject.infoTributaria.secuencial = item.NumGuia_Preimpresa; myObject.infoTributaria.dirMatriz = item.em_direccion; myObject.infoGuiaRemision.dirEstablecimiento = item.em_direccion; myObject.infoGuiaRemision.dirPartida = item.Direccion_Origen; myObject.infoGuiaRemision.razonSocialTransportista = item.Nombre; myObject.infoGuiaRemision.rucTransportista = item.Cedula; myObject.infoGuiaRemision.placa = item.placa; if (item.Cedula.Length == 10) { myObject.infoGuiaRemision.tipoIdentificacionTransportista = "05"; } if (item.Cedula.Length == 13) { myObject.infoGuiaRemision.tipoIdentificacionTransportista = "04"; } myObject.infoGuiaRemision.fechaIniTransporte = string.Format("{0:dd/MM/yyyy}", item.gi_FechaInicioTraslado); myObject.infoGuiaRemision.fechaFinTransporte = string.Format("{0:dd/MM/yyyy}", item.gi_FechaFinTraslado); if (item.ContribuyenteEspecial != "") { myObject.infoGuiaRemision.contribuyenteEspecial = item.ContribuyenteEspecial; } var facturas_x_guias = context.vwfe_guia_remision_x_factura.Where(v => v.IdEmpresa == item.IdEmpresa && v.IdGuiaRemision == item.IdGuiaRemision).ToList(); myObject.destinatarios.destinatario = new List <destinatario>(); destinatario.identificacionDestinatario = item.pe_cedulaRuc; destinatario.razonSocialDestinatario = item.pe_nombreCompleto; destinatario.dirDestinatario = item.Direccion; destinatario.motivoTraslado = item.tr_Descripcion; destinatario.codEstabDestino = "001"; destinatario.ruta = item.ruta == "" ? "S/N" : item.ruta; foreach (var item_fac in facturas_x_guias) { destinatario.codDocSustento = "01"; destinatario.numDocSustento = item.vt_serie1 + "-" + item_fac.vt_serie2 + "-" + item_fac.vt_NumFactura; if (item_fac.vt_autorizacion != null) { if (item_fac.vt_autorizacion.Length > 10) { destinatario.numAutDocSustento = item_fac.vt_autorizacion.Substring(0, 10); } } destinatario.fechaEmisionDocSustento = string.Format("{0:dd/MM/yyyy}", item.gi_fecha); } myObject.destinatarios.destinatario.Add(destinatario); var guia_detalle = context.vwfe_guia_remision_detalle.Where(v => v.IdEmpresa == item.IdEmpresa && v.IdGuiaRemision == item.IdGuiaRemision).ToList(); destinatario.detalles = new destinatarioDetalles(); destinatario.detalles.detalle = new List <detalle>(); foreach (var item_det in guia_detalle) { destinatario.detalles.detalle.Add(new detalle { codigoAdicional = item_det.pr_codigo, codigoInterno = item_det.pr_codigo, descripcion = item_det.pr_descripcion, cantidad = Convert.ToDecimal(item_det.gi_cantidad), }); } // campos adicionales fx_GeneradorXML_ValidarEmail_Info datosAdc = new fx_GeneradorXML_ValidarEmail_Info(); if (item.Correo != null && item.Correo != "") { if (myObject.infoAdicional == null) { myObject.infoAdicional = new List <guiaRemisionCampoAdicional>(); } guiaRemisionCampoAdicional compoadicional = new guiaRemisionCampoAdicional(); compoadicional.nombre = "MAIL"; compoadicional.Value = item.Correo; myObject.infoAdicional.Add(compoadicional); } if (item.Telefono != null && item.Telefono != "") { if (myObject.infoAdicional == null) { myObject.infoAdicional = new List <guiaRemisionCampoAdicional>(); } guiaRemisionCampoAdicional compoadicional = new guiaRemisionCampoAdicional(); compoadicional.nombre = "TELEFONO"; compoadicional.Value = item.Telefono; myObject.infoAdicional.Add(compoadicional); } lista.Add(myObject); } } return(lista); } catch (Exception ex) { return(new List <guiaRemision>()); } }