public List <PendientesLlenos> ListaStockLlenos()
{
List <PendientesLlenos> dtResult = new List <PendientesLlenos>();
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_CONSULTA_DSREPSTOCKCONTLLENOS_V2", cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
PendientesLlenos a = new PendientesLlenos();
a.YardCode = dr.IsDBNull(dr.GetOrdinal("NUMIDE")) ? "" : dr.GetString(dr.GetOrdinal("NUMIDE"));
a.EquipmentNumber = dr.IsDBNull(dr.GetOrdinal("NROCON")) ? "" : dr.GetString(dr.GetOrdinal("NROCON"));
dtResult.Add(a);
}
}
catch (Exception ex)
{
dtResult = null;
}
finally
{
cn.Close();
}
return(dtResult);
}
public string ObtenerNaveViaje(string ordenServicio)
{
string NaveViaje = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_OBTENERNAVE_VIAJE", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("XNORSRN", iDB2DbType.iDB2VarChar).Value = ordenServicio;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NaveViaje = (dr.IsDBNull(dr.GetOrdinal("TCMPVP")) ? "" : dr.GetString(dr.GetOrdinal("TCMPVP")).Trim()) + " - " + (dr.IsDBNull(dr.GetOrdinal("NVJES")) ? "" : dr.GetString(dr.GetOrdinal("NVJES")));
}
}
catch (Exception ex)
{
NaveViaje = "";
}
finally
{
cn.Close();
}
return(NaveViaje);
}
public string ObtenerRazonSocialAS400(string ruc)
{
string dResultado = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
try
{
iDB2Command cmd = new iDB2Command("SELECT * FROM RZZM01 WHERE NRUC = @Ruc FETCH FIRST 1 ROWS ONLY", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Ruc", SqlDbType.VarChar).Value = ruc;
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dResultado = dr.IsDBNull(dr.GetOrdinal("TCMPCL")) ? "" : dr.GetString(dr.GetOrdinal("TCMPCL"));
}
}
catch (Exception ex)
{
dResultado = "";
}
finally
{
cn.Close();
}
return(dResultado);
}
private string obtenerTipoOperacion(string ordenServicio)
{
string tipoOperacion = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SELECT CTPOOP FROM [email protected] WHERE NORSRN = @NORSRN", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@NORSRN", iDB2DbType.iDB2VarChar).Value = ordenServicio;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tipoOperacion = (dr.IsDBNull(dr.GetOrdinal("CTPOOP")) ? 1 : dr.GetInt32(dr.GetOrdinal("CTPOOP"))) == 1 ? "I" : "E";
}
}
catch (Exception ex)
{
tipoOperacion = "I";
}
finally
{
cn.Close();
}
return(tipoOperacion);
}
public List <PendientesLlenos> ListaPendientesLlenos(string tipo)
{
List <PendientesLlenos> dtResult = new List <PendientesLlenos>();
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_INTEGRACIONTDEPOT_JMY", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("XTIPO", iDB2DbType.iDB2VarChar).Value = tipo;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
PendientesLlenos a = new PendientesLlenos();
a.NUMID = dr.IsDBNull(dr.GetOrdinal("NUMID")) ? 0 : dr.GetInt32(dr.GetOrdinal("NUMID"));
a.VesselIMO = dr.IsDBNull(dr.GetOrdinal("CINDVP")) ? "" : dr.GetString(dr.GetOrdinal("CINDVP"));
a.OceanCarrier = dr.IsDBNull(dr.GetOrdinal("CLINE")) ? "" : dr.GetString(dr.GetOrdinal("CLINE"));
a.CustomerERPCode = dr.IsDBNull(dr.GetOrdinal("NRUCA")) ? "" : dr.GetString(dr.GetOrdinal("NRUCA"));
a.YardCode = dr.IsDBNull(dr.GetOrdinal("CTPDEP")) ? "" : dr.GetString(dr.GetOrdinal("CTPDEP"));
a.ActionType = dr.IsDBNull(dr.GetOrdinal("CACTTP")) ? "" : dr.GetString(dr.GetOrdinal("CACTTP"));
a.VoyageNumber = dr.IsDBNull(dr.GetOrdinal("VOYNUM")) ? "" : dr.GetString(dr.GetOrdinal("VOYNUM"));
a.VoyageStopExternalCode = dr.IsDBNull(dr.GetOrdinal("CRECAL")) ? "" : dr.GetString(dr.GetOrdinal("CRECAL"));
a.ReferenceType = dr.IsDBNull(dr.GetOrdinal("CTDCRF")) ? "" : dr.GetString(dr.GetOrdinal("CTDCRF"));
a.ReferenceNumber = dr.IsDBNull(dr.GetOrdinal("NDCREF")) ? "" : dr.GetString(dr.GetOrdinal("NDCREF"));
a.OperationType = dr.IsDBNull(dr.GetOrdinal("CTIPOP")) ? "" : dr.GetString(dr.GetOrdinal("CTIPOP"));
a.PortOfDischarge = dr.IsDBNull(dr.GetOrdinal("CPRLLN")) ? "" : dr.GetString(dr.GetOrdinal("CPRLLN"));
a.PlaceOfDelivery = dr.IsDBNull(dr.GetOrdinal("CPRLLN")) ? "" : dr.GetString(dr.GetOrdinal("CPRLLN"));
a.PlaceOfReceipt = dr.IsDBNull(dr.GetOrdinal("CPROR1")) ? "" : dr.GetString(dr.GetOrdinal("CPROR1"));
a.PortOfLoading = dr.IsDBNull(dr.GetOrdinal("CPROR1")) ? "" : dr.GetString(dr.GetOrdinal("CPROR1"));
a.ShipmentType = dr.IsDBNull(dr.GetOrdinal("CTPCNC")) ? "" : dr.GetString(dr.GetOrdinal("CTPCNC"));
a.ActivityCode = dr.IsDBNull(dr.GetOrdinal("CACTVC")) ? "" : dr.GetString(dr.GetOrdinal("CACTVC"));
a.direction = dr.IsDBNull(dr.GetOrdinal("CDIREC")) ? "" : dr.GetString(dr.GetOrdinal("CDIREC"));
a.Status = dr.IsDBNull(dr.GetOrdinal("CSTSMV")) ? "" : dr.GetString(dr.GetOrdinal("CSTSMV"));
a.VehiclePlate = dr.IsDBNull(dr.GetOrdinal("NPLCUN")) ? "" : dr.GetString(dr.GetOrdinal("NPLCUN"));
a.ExecutionDate = dr.IsDBNull(dr.GetOrdinal("FCHMOV")) ? "" : dr.GetString(dr.GetOrdinal("FCHMOV"));
a.EquipmentNumber = dr.IsDBNull(dr.GetOrdinal("NROCON")) ? "" : dr.GetString(dr.GetOrdinal("NROCON"));
a.EquipmentTypeSizeISOCode = dr.IsDBNull(dr.GetOrdinal("ISOCODE")) ? "" : dr.GetString(dr.GetOrdinal("ISOCODE"));
dtResult.Add(a);
}
}
catch (Exception ex)
{
dtResult = null;
}
finally
{
cn.Close();
}
return(dtResult);
}
public List <DetalleNave> ListarDetallesdeEnvio(string ordenServicio)
{
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
string tipoOperacion = obtenerTipoOperacion(ordenServicio);
if (tipoOperacion.Equals("I"))
{
cmd = new iDB2Command("SP_INTEGRACION_CARCOOL_IMPO", cn);
}
else
{
cmd = new iDB2Command("SP_INTEGRACION_CARCOOL_EXPO", cn);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("XNORSRN", iDB2DbType.iDB2VarChar).Value = ordenServicio;
List <DetalleNave> detalleNave = new List <DetalleNave>();
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//refrescar datos
DetalleNave e = new DetalleNave();
e.Recalada = dr.GetString(dr.GetOrdinal("OrdenServicio"));
e.Manifiesto = dr.GetString(dr.GetOrdinal("Manifiesto"));
e.Nave = dr.GetString(dr.GetOrdinal("Nave")).Trim();
e.Viaje = dr.GetString(dr.GetOrdinal("Viaje")).Trim();
e.Operador = dr.GetString(dr.GetOrdinal("Operador"));
e.FechaArribo = dr.GetDateTime(dr.GetOrdinal("FechaArribo"));
//e.FechaTerminoDescarga = dr.IsDBNull(dr.GetOrdinal("FechaterminoDescarga")) ? default(DateTime) : dr.GetDateTime(dr.GetOrdinal("FechaterminoDescarga"));
e.FechaTerminoDescarga = dr.GetString(dr.GetOrdinal("FechaterminoDescarga")) == "" ? DateTime.Now : dr.GetDateTime(dr.GetOrdinal("FechaterminoDescarga"));
//e.FechaCutOffdry = dr.IsDBNull(dr.GetOrdinal("FechaCutOffdry")) ? default(DateTime) : dr.GetDateTime(dr.GetOrdinal("FechaCutOffdry"));
e.FechaCutOffdry = dr.GetString(dr.GetOrdinal("FechaCutOffdry")) == "" ? DateTime.Now : dr.GetDateTime(dr.GetOrdinal("FechaCutOffdry"));
//e.FechaCutOffReefer = dr.IsDBNull(dr.GetOrdinal("FechaCutOffReefer")) ? default(DateTime) : dr.GetDateTime(dr.GetOrdinal("FechaCutOffReefer"));
e.FechaCutOffReefer = dr.GetString(dr.GetOrdinal("FechaCutOffReefer")) == "" ? DateTime.Now : dr.GetDateTime(dr.GetOrdinal("FechaCutOffReefer"));
e.FechaZarpe = dr.IsDBNull(dr.GetOrdinal("FechaZarpe")) ? DateTime.Now : dr.GetDateTime(dr.GetOrdinal("FechaZarpe"));
e.TipoOperacion = dr.GetString(dr.GetOrdinal("TipoOperacion"));
e.Contenedor = dr.GetString(dr.GetOrdinal("Contenedor")).Trim();
e.Tipo = dr.GetString(dr.GetOrdinal("TipodeDocumento"));
e.BlBooking = dr.GetString(dr.GetOrdinal("BlBooking")).Trim();
e.Consignatario = dr.GetString(dr.GetOrdinal("Consignatario")).Trim();
e.TipoContenedor = dr.GetString(dr.GetOrdinal("TipoContenedor"));
e.Dimension = dr.GetString(dr.GetOrdinal("Dimension"));
e.Condicion = dr.GetString(dr.GetOrdinal("Condicion"));
e.Peso = dr.GetDecimal(dr.GetOrdinal("Peso"));
e.Temperatura = dr.GetString(dr.GetOrdinal("Temperatura"));
e.Imo = dr.GetBoolean(dr.GetOrdinal("IMO"));
e.Iqbf = dr.GetBoolean(dr.GetOrdinal("IQBF"));
e.SINI = dr.GetBoolean(dr.GetOrdinal("SINI"));
e.Conexion = dr.GetBoolean(dr.GetOrdinal("Conexion"));
detalleNave.Add(e);
}
}
catch (Exception ex)
{
List <string> listaDestinatarios = new List <string>();
List <string> listaCopia = new List <string>();
List <string> listaCopiaOculta = new List <string>();
string destinatarios = ConfigurationManager.AppSettings["Destinatarios"].ToString();
if (destinatarios != "" && destinatarios.Contains(";"))
{
var correosDestino = destinatarios.Split(';');
foreach (string item in correosDestino)
{
listaDestinatarios.Add(item);
}
}
else
{
listaDestinatarios.Add(destinatarios);
}
string copia = ConfigurationManager.AppSettings["ConCopia"].ToString();
if (copia != "" && copia.Contains(";"))
{
var correosCopia = copia.Split(';');
foreach (string item in correosCopia)
{
listaCopia.Add(item);
}
}
else
{
listaCopia.Add(copia);
}
string copiaOculta = ConfigurationManager.AppSettings["CopiaOculta"].ToString();
if (copiaOculta != "" && copiaOculta.Contains(";"))
{
var correosCopiaOculta = copiaOculta.Split(';');
foreach (string item in correosCopiaOculta)
{
listaCopiaOculta.Add(item);
}
}
else
{
listaCopiaOculta.Add(copiaOculta);
}
new ManejadorCorreos().EnviarCorreo(ConfigurationManager.AppSettings["AsuntoCorreoError"].ToString(), ex.Message.ToString(), listaDestinatarios, listaCopia, listaCopiaOculta);
detalleNave = null;
}
finally
{
cn.Close();
}
return(detalleNave);
}
public static T GetNullable <T>(this iDB2DataReader reader, string columnName, T defaultValue)
{
return(reader.IsDBNull(columnName)
? defaultValue
: (T)reader[columnName]);
}