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>());
}
}
