public DataTable ConseguirTabla(string tabla, List <Filtro> filtros)
{
string comandoString = string.Copy(comandoSelect) + " * FROM " + tabla;
if (filtros != null && filtros.Count > 0)
{
comandoString = PonerFiltros(comandoString, filtros);
}
PinkieLogger.logSelect(comandoString, "llenar grid");
using (SqlConnection sqlConnection = new SqlConnection(conectionString))
{
sqlConnection.Open();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConnection;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = comandoString;
SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);
DataTable dtRecord = new DataTable();
sqlDataAdap.Fill(dtRecord);
return(dtRecord);
}
}
}
public DataTable TraerLitadoEstadistico(string nombreView, DateTime fechaInicio, DateTime fechaFin)
{
string condicion = " WHERE fecha_fin BETWEEN '" + fechaInicio.ToString("yyyy-MM-dd") + "' AND '" + fechaFin.ToString("yyyy-MM-dd") + "'";
string comandoString = String.Empty;
switch (nombreView)
{
case "PINKIE_PIE.top_5_recorridos":
comandoString = "SELECT TOP 5 codigo_recorrido, puerto_origen, puerto_destino, SUM(cant_pasaje) as cant FROM PINKIE_PIE.top_5_recorridos " +
condicion + " GROUP BY codigo_recorrido, puerto_origen, puerto_destino ORDER BY cant DESC";
break;
case "PINKIE_PIE.top_5_clientes_puntos":
return(ConseguirTabla(nombreView, null));
case "PINKIE_PIE.top_5_viajes_cabinas_vacias":
comandoString = "SELECT TOP 5 viaje_id, cod_recorrido, SUM(cant_cabinas) as cant FROM " + nombreView + condicion
+ " GROUP BY viaje_id, cod_recorrido ORDER BY cant DESC";
break;
case "PINKIE_PIE.top_5_dias_crucero_fuera_servicio":
comandoString = "SELECT TOP 5 identificador, fabricante, modelo, SUM(cant_dias) as cant FROM PINKIE_PIE.top_5_dias_crucero_fuera_servicio"
+ condicion + " GROUP BY identificador, fabricante, modelo ORDER BY cant DESC";
break;
}
PinkieLogger.logSelect(comandoString, "listado estadistico");
DataTable dtRecord = new DataTable();
using (SqlConnection sqlConnection = new SqlConnection(conectionString))
{
sqlConnection.Open();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConnection;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = comandoString;
SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);
sqlDataAdap.Fill(dtRecord);
}
}
return(dtRecord);
}
public void LlenarDataGridViewCruceros(ref DataGridView dataGrid)
{
string comandoString = "SELECT cru.id, cru.modelo, fab.nombre as 'fabricante', cru.identificador, cru.fecha_de_alta, cru.fecha_baja_definitiva, cru.baja_fuera_de_servicio, cru.baja_vida_util FROM " + Tabla.Crucero + "cru JOIN " + Tabla.Fabricante + " fab ON cru.fabricante_id=fab.id";
PinkieLogger.logSelect(comandoString, "llenar grid");
using (SqlConnection sqlConnection = new SqlConnection(conectionString))
{
sqlConnection.Open();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConnection;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = comandoString;
SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);
DataTable dtRecord = new DataTable();
sqlDataAdap.Fill(dtRecord);
dataGrid.DataSource = dtRecord;
}
}
}
