//Recibe el nombre de la tabla sacada de Conexion.Tabla, y un diccionario con el par
//("nombre de columna en BD", dato a insertar)
//retorna true si se pudo realizar, false si fallo
public int Insertar(string tabla, Dictionary <string, object> data)
{
try
{
string comandoString = string.Copy(comandoInsert) + tabla + " (";
data.Keys.ToList().ForEach(k => comandoString += k + ", ");
comandoString = comandoString.Substring(0, comandoString.Length - 2) + ") VALUES (";
data.Keys.ToList().ForEach(k => comandoString += "@" + k + ", ");
comandoString = comandoString.Substring(0, comandoString.Length - 2) + "); SELECT SCOPE_IDENTITY();";
PinkieLogger.logInsert(comandoString, data);
using (SqlConnection sqlConnection = new SqlConnection(conectionString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = sqlConnection;
command.CommandType = CommandType.Text;
command.CommandText = comandoString;
foreach (KeyValuePair <string, object> entry in data)
{
command.Parameters.AddWithValue("@" + entry.Key, entry.Value);
}
return(Convert.ToInt32(command.ExecuteScalar()));
}
}
}
catch (SqlException ex)
{
PinkieLogger.logExcepcion(ex);
return(-1);
}
}
//Recibe el id de la fila, nombre de la tabla sacada de Conexion.Tabla, y
//un diccionario con el par ("nombre de columna en BD", dato a insertar
//retorna true si se pudo realizar, false si fallo
public bool Modificar(int pk, string tabla, Dictionary <string, object> data)
{
try
{
string comandoString = string.Copy(comandoUpdate) + tabla + " SET ";
foreach (KeyValuePair <string, object> entry in data)
{
comandoString += entry.Key + " = @" + entry.Key + ", ";
}
comandoString = comandoString.Substring(0, comandoString.Length - 2) + " WHERE id = @id";
PinkieLogger.logUpdate(comandoString, data);
using (SqlConnection sqlConnection = new SqlConnection(conectionString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = sqlConnection;
command.CommandType = CommandType.Text;
command.CommandText = comandoString;
command.Parameters.AddWithValue("@id", pk);
foreach (KeyValuePair <string, object> entry in data)
{
command.Parameters.AddWithValue("@" + entry.Key, entry.Value);
}
command.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
PinkieLogger.logExcepcion(ex);
return(false);
}
return(true);
}
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 bool PagarReserva(int codigo, string tabla)
{
try
{
string comandoString = string.Copy(comandoUpdate) + tabla + " SET pagado = 1 WHERE codigo = @id";
using (SqlConnection sqlConnection = new SqlConnection(conectionString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = sqlConnection;
command.CommandType = CommandType.Text;
command.CommandText = comandoString;
command.Parameters.AddWithValue("@id", codigo);
command.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
PinkieLogger.logExcepcion(ex);
return(false);
}
return(true);
}
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);
}
private void CambiarHabilitacion(string tabla, int id, string cambio)
{
string comandoString = string.Copy(comandoUpdate) + tabla + " SET habilitado = " + cambio + " WHERE id = @id";
PinkieLogger.EscribirArchivo("Se genera cambio de habilitacion con id = " + id.ToString() + "\n" + comandoString);
using (SqlConnection connection = new SqlConnection(conectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
command.CommandText = comandoString;
command.CommandType = CommandType.Text;
command.Connection = connection;
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
}
}
}
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;
}
}
}