public List <Reservacion> GetAllReservacion()
{
DataSet ds = null;
List <Reservacion> lista = new List <Reservacion>();
SqlCommand command = new SqlCommand();
IDALHabitacion _DALHabitacion = new DALHabitacion();
IDALHuesped _DALHuesped = new DALHuesped();
string sql = @"usp_SELECT_Reservacion_All";
try
{
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió filas
if (ds.Tables[0].Rows.Count > 0)
{
// Iterar en todas las filas y Mapearlas
foreach (DataRow dr in ds.Tables[0].Rows)
{
Reservacion oReservacion = new Reservacion()
{
ID = double.Parse(dr["ID"].ToString()),
_Huesped = _DALHuesped.GetHuespedById(double.Parse(dr["IDHuesped"].ToString())),
_Habitacion = _DALHabitacion.GetHabitacionById(double.Parse(dr["NUMHabitacion"].ToString())),
CheckIN = (DateTime)dr["CheckIN"],
CheckOUT = (DateTime)dr["CheckOUT"],
CantDias = (int)dr["CantDias"],
Subtotal = double.Parse(dr["Subtotal"].ToString())
};
lista.Add(oReservacion);
}
}
return(lista);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public static IDataBase CreateDefaultDataBase()
{
IDbConnection conexion = null;
try
{
string pStringConnection = FactoryConexion.CreateConnection();
IDataBase db = new DataBase();
conexion = new SqlConnection(pStringConnection);
conexion.Open();
db.Conexion = conexion;
if (conexion.State != ConnectionState.Open)
{
throw new Exception("No se pudo abrir la Base de Datos, revise los parámetros de conexión! ");
}
return(db);
}
catch (Exception)
{
throw;
}
}
public bool Login(string pUsuario, string pContrasena)
{
StringBuilder conexion = new StringBuilder();
try
{
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(pUsuario, pContrasena)))
{
// Si esto da error es porque el usuario no existe!
}
return(true);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public List <Huesped> GetAllHuesped()
{
DataSet ds = null;
List <Huesped> lista = new List <Huesped>();
SqlCommand command = new SqlCommand();
IDALPais _Pais = new DALPais();
string sql = @"usp_SELECT_Huesped_All";
try
{
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió filas
if (ds.Tables[0].Rows.Count > 0)
{
//// Iterar en todas las filas y Mapearlas
foreach (DataRow dr in ds.Tables[0].Rows)
{
Huesped oHuesped = new Huesped();
oHuesped.ID = (int)dr["ID"];
oHuesped.Nombre = dr["Nombre"].ToString();
oHuesped.Apellido1 = dr["Apellido1"].ToString();
oHuesped.Apellido2 = dr["Apellido2"].ToString();
oHuesped.Telefono = dr["Telefono"].ToString();
oHuesped.Correo = dr["Correo"].ToString();
oHuesped._Pais = _Pais.GetPaisById(Double.Parse(dr["IDPais"].ToString()));
lista.Add(oHuesped);
}
}
return(lista);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public List <SysUser> GetSysUserByFilter(string pDescripcion)
{
DataSet ds = null;
SqlCommand command = new SqlCommand();
List <SysUser> lista = new List <SysUser>();
string sql = @" select * from [SysUser] where [Login] like @Login";
try
{
// Pasar Parámetro
command.Parameters.AddWithValue("@Login", pDescripcion);
command.CommandText = sql;
command.CommandType = CommandType.Text;
// Ejecutar
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió valores
if (ds.Tables[0].Rows.Count > 0)
{
// Itetarar en las filas
foreach (DataRow dr in ds.Tables[0].Rows)
{
SysUser oSysUser = new SysUser()
{
Login = (dr["Login"].ToString()),
Password = (dr["Password"].ToString()),
IdRol = (int)(dr["IdRol"]),
IDUser = (int)(dr["IDUser"])
};
lista.Add(oSysUser);
}
}
return(lista);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public Huesped GetHuespedById(double pIdHuesped)
{
DataSet ds = null;
Huesped oHuesped = new Huesped();
SqlCommand command = new SqlCommand();
IDALPais _DALPais = new DALPais();
string sql = @"select * from [Huesped] where ID = @ID";
try
{
command.Parameters.AddWithValue("@ID", pIdHuesped);
command.CommandText = sql;
command.CommandType = CommandType.Text;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió filas
if (ds.Tables[0].Rows.Count > 0)
{
// Iterar en todas las filas y Mapearlas
foreach (DataRow dr in ds.Tables[0].Rows)
{
oHuesped.ID = (int)dr["ID"];
oHuesped.Nombre = dr["Nombre"].ToString();
oHuesped.Apellido1 = dr["Apellido1"].ToString();
oHuesped.Apellido2 = dr["Apellido2"].ToString();
oHuesped.Telefono = dr["Telefono"].ToString();
oHuesped.Correo = dr["Correo"].ToString();
oHuesped._Pais = _DALPais.GetPaisById(Double.Parse(dr["IDPais"].ToString()));
}
}
return(oHuesped);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public List <SysUserRolDTO> GetAllSysUser()
{
DataSet ds = null;
List <SysUserRolDTO> lista = new List <SysUserRolDTO>();
SqlCommand command = new SqlCommand();
string sql = @"usp_SELECT_SysUser_All";
try
{
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió filas
if (ds.Tables[0].Rows.Count > 0)
{
// Iterar en todas las filas y Mapearlas
foreach (DataRow dr in ds.Tables[0].Rows)
{
SysUserRolDTO oSysUserRolDTO = new SysUserRolDTO()
{
Login = (dr["Login"].ToString()),
Password = (dr["Password"].ToString()),
Id = double.Parse(dr["IdRol"].ToString()),
//Descripcion = (dr["Descripcion"].ToString()),
IDUser = (int)(dr["IDUser"])
};
lista.Add(oSysUserRolDTO);
}
}
return(lista);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public List <HabitacionDTO> GetAllHabitacion()
{
DataSet ds = null;
List <HabitacionDTO> lista = new List <HabitacionDTO>();
SqlCommand command = new SqlCommand();
string sql = @"usp_SELECT_Habitacion_All";
try
{
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió filas
if (ds.Tables[0].Rows.Count > 0)
{
// Iterar en todas las filas y Mapearlas
foreach (DataRow dr in ds.Tables[0].Rows)
{
HabitacionDTO oHabitacionDTO = new HabitacionDTO()
{
NUM = double.Parse(dr["NUM"].ToString()),
Descripcion = dr["Descripcion"].ToString(),
Foto = (byte[])dr["Foto"],
Estado = (int)dr["Estado"],
Precio = double.Parse(dr["Precio"].ToString())
};
lista.Add(oHabitacionDTO);
}
}
return(lista);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
//_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
//_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public Habitacion UpdateHabitacion(Habitacion pHabitacion)
{
Habitacion oHabitacion = null;
string sql = @"usp_UPDATE_Habitacion";
SqlCommand command = new SqlCommand();
double rows = 0;
try
{
// Pasar parámetros
command.Parameters.AddWithValue("@NUM", pHabitacion.NUM);
command.Parameters.AddWithValue("@Descripcion", pHabitacion.Descripcion);
command.Parameters.AddWithValue("@Foto", pHabitacion.Foto.ToArray());
command.Parameters.AddWithValue("Estado", pHabitacion.Estado);
command.Parameters.AddWithValue("Precio", pHabitacion.Precio);
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
rows = db.ExecuteNonQuery(command, IsolationLevel.ReadCommitted);
}
// Si devuelve filas quiere decir que se salvo entonces extraerlo
if (rows > 0)
{
oHabitacion = GetHabitacionById(pHabitacion.NUM);
}
return(oHabitacion);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
//_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
//_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public Habitacion GetHabitacionById(double pId)
{
DataSet ds = null;
Habitacion oHabitacion = null;
string sql = @"Select * from [Habitacion] where NUM = @NUM";
SqlCommand command = new SqlCommand();
try
{
command.Parameters.AddWithValue("@NUM", pId);
command.CommandText = sql;
command.CommandType = CommandType.Text;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si retornó valores
if (ds.Tables[0].Rows.Count > 0)
{
//Extraer la primera fila, como se buscó por Id entonces solo una debe devolver
DataRow dr = ds.Tables[0].Rows[0];
oHabitacion = new Habitacion()
{
NUM = double.Parse(dr["NUM"].ToString()),
Descripcion = dr["Descripcion"].ToString(),
Foto = (byte[])dr["Foto"],
Estado = (int)dr["Estado"],
Precio = double.Parse(dr["Precio"].ToString())
};
}
return(oHabitacion);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
//_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
//_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public SysUser GetSysUserById(double pId)
{
DataSet ds = null;
SysUser oSysUser = null;
SysUserRolDTO DTO = new SysUserRolDTO();
string sql = @" select * from [SysUser] where IDUser = @IDUser";
SqlCommand command = new SqlCommand();
try
{
command.Parameters.AddWithValue("@IDUser", pId);
command.CommandText = sql;
command.CommandType = CommandType.Text;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si retornó valores
if (ds.Tables[0].Rows.Count > 0)
{
//Extraer la primera fila, como se buscó por Id entonces solo una debe devolver
DataRow dr = ds.Tables[0].Rows[0];
oSysUser = new SysUser()
{
Login = (dr["Login"].ToString()),
Password = (dr["Password"].ToString()),
IdRol = (int)(dr["IdRol"]),
IDUser = (int)(dr["IDUser"])
};
}
return(oSysUser);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public SysUser UpdateSysUser(SysUser pSysUser)
{
SysUser oSysUser = null;
string sql = @"usp_UPDATE_SysUser";
SqlCommand command = new SqlCommand();
double rows = 0;
try
{
// Pasar parámetros
command.Parameters.AddWithValue("@Login", pSysUser.Login);
command.Parameters.AddWithValue("@Password", pSysUser.Password);
command.Parameters.AddWithValue("@IdRol", pSysUser.IdRol);
command.Parameters.AddWithValue("@IDUser", pSysUser.IDUser);
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
rows = db.ExecuteNonQuery(command, IsolationLevel.ReadCommitted);
}
// Si devuelve filas quiere decir que se salvo entonces extraerlo
if (rows > 0)
{
oSysUser = GetSysUserById(pSysUser.IDUser);
}
return(oSysUser);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public Huesped UpdateHuesped(Huesped pHuesped)
{
Huesped _Huesped = null;
string sql = @"usp_UPDATE_Huesped";
SqlCommand cmd = new SqlCommand();
double rows = 0;
try
{
cmd.Parameters.AddWithValue("@ID", pHuesped.ID);
cmd.Parameters.AddWithValue("@Nombre", pHuesped.Nombre);
cmd.Parameters.AddWithValue("@Apellido1", pHuesped.Apellido1);
cmd.Parameters.AddWithValue("@Apellido2", pHuesped.Apellido2);
cmd.Parameters.AddWithValue("@Telefono", pHuesped.Telefono);
cmd.Parameters.AddWithValue("@Correo", pHuesped.Correo);
cmd.Parameters.AddWithValue("@IDPais", pHuesped._Pais.ID);
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
rows = db.ExecuteNonQuery(cmd, IsolationLevel.ReadCommitted);
}
// Si devuelve filas quiere decir que se salvo entonces extraerlo
if (rows > 0)
{
_Huesped = GetHuespedById(pHuesped.ID);
}
return(_Huesped);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", cmd.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public Pais GetPaisById(double pId)
{
DataSet ds = null;
Pais oPais = new Pais();
SqlCommand command = new SqlCommand();
string sql = @"select * from [Pais] where ID = @ID";
try
{
command.Parameters.AddWithValue("@ID", pId);
command.CommandText = sql;
command.CommandType = CommandType.Text;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió filas
if (ds.Tables[0].Rows.Count > 0)
{
// Iterar en todas las filas y Mapearlas
foreach (DataRow dr in ds.Tables[0].Rows)
{
oPais.ID = (int)dr["ID"];
oPais.Detalle = dr["Detalle"].ToString();
}
}
return(oPais);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public List <Pais> GetAllPais()
{
IDataReader reader = null;
List <Pais> lista = new List <Pais>();
SqlCommand command = new SqlCommand();
string sql = @"usp_SELECT_Pais_All";
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
try
{
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
reader = db.ExecuteReader(command);
while (reader.Read())
{
Pais oPais = new Pais();
oPais.ID = int.Parse(reader["ID"].ToString());
oPais.Detalle = reader["Detalle"].ToString();
lista.Add(oPais);
}
}
return(lista);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public Tarjeta GetTarjetaById(double pIdTarjeta)
{
DataSet ds = null;
Tarjeta oTarjeta = null;
SqlCommand command = new SqlCommand();
string sql = @"usp_SELECT_Tarjeta_ByID";
try
{
command.Parameters.AddWithValue("@ID", pIdTarjeta);
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió filas
if (ds.Tables[0].Rows.Count > 0)
{
// Iterar en todas las filas y Mapearlas
foreach (DataRow dr in ds.Tables[0].Rows)
{
oTarjeta = new Tarjeta();
oTarjeta.ID = int.Parse(dr["ID"].ToString());
oTarjeta.Tipo = dr["Tipo"].ToString();
}
}
return(oTarjeta);
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat("Message {0}\n", er.Message);
msg.AppendFormat("Source {0}\n", er.Source);
msg.AppendFormat("InnerException {0}\n", er.InnerException);
msg.AppendFormat("StackTrace {0}\n", er.StackTrace);
msg.AppendFormat("TargetSite {0}\n", er.TargetSite);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
/// <summary>
/// Extraer los usuarios por Base de Datos
/// </summary>
/// <param name="pBaseDatos"></param>
/// <returns></returns>
public IEnumerable <string> GetLoginsXDataBase(string pBaseDatos)
{
SqlCommand command = new SqlCommand();
DataSet ds = null;
string sql = @" select name from sys.syslogins where dbname = @basedatos ";
List <string> lista = new List <string>();
try
{
command.Parameters.AddWithValue("@basedatos", pBaseDatos);
command.CommandText = sql;
command.CommandType = CommandType.Text;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
foreach (DataRow row in ds.Tables[0].Rows)
{
lista.Add(row[0].ToString());
}
return(lista);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
// msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
//msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public bool DeleteHuesped(double pId)
{
bool retorno = false;
double rows = 0d;
SqlCommand command = new SqlCommand();
try
{
/// string sql = @"Delete from Cliente Where (IdCliente = @IdCliente) ";
string sql = "usp_DELETE_Huesped_ByID";
command.Parameters.AddWithValue("@ID", pId);
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
rows = db.ExecuteNonQuery(command, IsolationLevel.ReadCommitted);
}
// Si devuelve filas quiere decir que se salvo entonces extraerlo
if (rows > 0)
{
retorno = true;
}
return(retorno);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public Impuesto GetImpuesto()
{
IDataReader reader = null;
SqlCommand command = new SqlCommand();
Impuesto oImpuesto = new Impuesto();
string sql = @"select * from [dbo].[Impuesto]";
try
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
reader = db.ExecuteReader(command);
while (reader.Read())
{
oImpuesto.Porcentaje = Double.Parse(reader["Porcentaje"].ToString());
}
}
return(oImpuesto);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public int GetNextNumeroReserva()
{
DataSet ds = null;
IDbCommand command = new SqlCommand();
int numeroReserva = 0;
string sql = @"SELECT NEXT VALUE FOR SequenceNoReserva";
DataTable dt = null;
try
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Extraer la tabla
dt = ds.Tables[0];
//Extraer el valor que viene en el DataTable
numeroReserva = int.Parse(dt.Rows[0][0].ToString());
return(numeroReserva);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public List <Tarjeta> GetAllTarjeta()
{
IDataReader reader = null;
List <Tarjeta> lista = new List <Tarjeta>();
Tarjeta oTarjeta = null;
SqlCommand command = new SqlCommand();
try
{
string sql = @"usp_SELECT_Tarjeta_All";
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
reader = db.ExecuteReader(command);
// Si devolvió filas
while (reader.Read())
{
oTarjeta = new Tarjeta();
oTarjeta.ID = int.Parse(reader["ID"].ToString());
oTarjeta.Tipo = reader["Tipo"].ToString();
lista.Add(oTarjeta);
}
}
return(lista);
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat("Message {0}\n", er.Message);
msg.AppendFormat("Source {0}\n", er.Source);
msg.AppendFormat("InnerException {0}\n", er.InnerException);
msg.AppendFormat("StackTrace {0}\n", er.StackTrace);
msg.AppendFormat("TargetSite {0}\n", er.TargetSite);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public bool DeleteHabitacion(double pId)
{
double rows = 0;
// Crear el SQL
string sql = @"usp_DELETE_Habitacion_ByID";
SqlCommand command = new SqlCommand();
try
{
// Pasar parámetros
command.Parameters.AddWithValue("@NUM", pId);
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
// Ejecutar
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
rows = db.ExecuteNonQuery(command, IsolationLevel.ReadCommitted);
}
return(rows > 0);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
//_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
//_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
/// <summary>
/// Crea un usuario dependiendo de la Base de Datos
/// </summary>
/// <param name="pUsuario">Usuario</param>
/// <param name="pContrasena">Contrasena</param>
/// <param name="pBaseDatos">Base de Datos</param>
public void CreateUser(string pUsuario, string pContrasena, string pBaseDatos)
{
StringBuilder sql = new StringBuilder();
SqlCommand command = new SqlCommand();
try
{
sql.AppendFormat("CREATE LOGIN [{0}] WITH PASSWORD=N'{1}', DEFAULT_DATABASE=[{2}],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ",
pUsuario, pContrasena, pBaseDatos);
sql.AppendFormat("EXEC master..sp_addsrvrolemember @loginame = N'{0}', @rolename = N'sysadmin' ", pUsuario);
sql.AppendFormat("USE [{0}] ", pBaseDatos);
sql.AppendFormat("CREATE USER [{0}] FOR LOGIN [{1}] ", pUsuario, pUsuario);
command.CommandText = sql.ToString();
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
// Note ExecuteNonQuery NO LLEVA TRANSACCION!!!
db.ExecuteNonQuery(command);
}
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
//msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
//msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public EncFactura GetFactura(double pNumeroFactura)
{
EncFactura oFacturaEncabezado = new EncFactura();
DataSet ds = null;
IDALHuesped _DALHuesped = new DALHuesped();
IDALTarjeta _DALTarjeta = new DALTarjeta();
IDALReservacion _DALReservacion = new DALReservacion();
IDALEncFactura _DALEncFactura = new DALEncFactura();
IBLLImpuesto _BLLImpuestotest = new BLLImpuesto();
SqlCommand command = new SqlCommand();
string sql = @"";
sql = @"usp_SELECT_EncFactura_ByID";
try
{
command.Parameters.AddWithValue("@IDFactura", pNumeroFactura);
command.CommandText = sql;
command.CommandType = CommandType.StoredProcedure;
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió filas
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
oFacturaEncabezado = new EncFactura()
{
IDFactura = double.Parse(dr["IDFactura"].ToString()),
_Tarjeta = _DALTarjeta.GetTarjetaById(int.Parse(dr["IDTarjeta"].ToString())),
Fecha = DateTime.Parse(dr["Fecha"].ToString()),
EstadoFact = dr["EstadoFact"].ToString()
};
foreach (var item in ds.Tables[0].Rows)
{
DetFactura oFacturaDetalle = new DetFactura();
oFacturaDetalle._EncFactura = _DALEncFactura.GetFactura(pNumeroFactura);
oFacturaDetalle.Numero = int.Parse(dr["Numero"].ToString());
oFacturaDetalle.Precio = double.Parse(dr["Precio"].ToString());
oFacturaDetalle._Impuesto = _BLLImpuestotest.GetImpuesto();
// Enumerar la secuencia
oFacturaDetalle._Reservacion = _DALReservacion.GetReserva(pNumeroFactura);
// Agregar
oFacturaEncabezado.AddDetalle(oFacturaDetalle);
}
}
return(oFacturaEncabezado);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public EncFactura SaveFactura(EncFactura pFactura)
{
EncFactura oFacturaEncabezado = new EncFactura();
IBLLTarjeta _BLLTarjeta = new BLLTarjeta();
string sqlEncabezado = string.Empty;
string sqlDetalle = string.Empty;
string sqlHabitacion = string.Empty;
SqlCommand cmdFacturaEncabezado = new SqlCommand();
SqlCommand cmdFacturaDetalle = new SqlCommand();
SqlCommand cmdHabitacion = new SqlCommand();
List <IDbCommand> listaCommands = new List <IDbCommand>();
double rows = 0;
sqlEncabezado = @"usp_INSERT_EncFactura";
try
{
// Encabezado de factura
cmdFacturaEncabezado.Parameters.AddWithValue("@IDFactura", pFactura.IDFactura);
cmdFacturaEncabezado.Parameters.AddWithValue("@IDTarjeta", pFactura._Tarjeta.ID);
cmdFacturaEncabezado.Parameters.AddWithValue("@Fecha", DateTime.Now.Date);
cmdFacturaEncabezado.Parameters.AddWithValue("@EstadoFact", pFactura.EstadoFact);
cmdFacturaEncabezado.CommandText = sqlEncabezado;
cmdFacturaEncabezado.CommandType = CommandType.StoredProcedure;
// Agregar a la lista de commands
listaCommands.Add(cmdFacturaEncabezado);
// Detalle de factura
// Guardar el detalle de la factura y a la vez rebajar el saldo del producto en Electronico
foreach (DetFactura pFacturaDetalle in pFactura._ListaFacturaDetalle)
{
sqlDetalle = @"usp_INSERT_DetFactura";
cmdFacturaDetalle = new SqlCommand();
cmdFacturaDetalle.Parameters.AddWithValue("@IDFactura", GetCurrentNumeroFactura());
cmdFacturaDetalle.Parameters.AddWithValue("@Numero", pFacturaDetalle.Numero);
cmdFacturaDetalle.Parameters.AddWithValue("@Precio", pFacturaDetalle._Reservacion.Subtotal);
cmdFacturaDetalle.Parameters.AddWithValue("@Impuesto", pFacturaDetalle._Impuesto.Porcentaje);
cmdFacturaDetalle.Parameters.AddWithValue("@IDReservacion", pFacturaDetalle._Reservacion.ID);
cmdFacturaDetalle.CommandText = sqlDetalle;
cmdFacturaDetalle.CommandType = CommandType.StoredProcedure;
// Agregar a la lista de comandos
listaCommands.Add(cmdFacturaDetalle);
// Cambiar
//cmdHabitacion = new SqlCommand();
//cmdHabitacion.Parameters.AddWithValue("@NUM", pFacturaDetalle._Reservacion._Habitacion.NUM);
//cmdHabitacion.Parameters.AddWithValue("@Estado", 1);
//sqlHabitacion = @"usp_UPDATE_Habitacion";
//cmdHabitacion.CommandText = sqlHabitacion;
//cmdHabitacion.CommandType = CommandType.StoredProcedure;
//listaCommands.Add(cmdHabitacion);
}
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
rows = db.ExecuteNonQuery(listaCommands, IsolationLevel.ReadCommitted);
}
// Si devuelve filas quiere decir que se salvo entonces extraerlo
if (rows == 0)
{
throw new Exception("No se pudo correctamente la factura");
}
else
{
//oFacturaEncabezado = GetFactura(pFactura.IDFactura);
}
return(oFacturaEncabezado);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", cmdFacturaEncabezado.CommandText);
msg.AppendFormat("SQL {0}\n", cmdFacturaDetalle.CommandText);
msg.AppendFormat("SQL {0}\n", cmdHabitacion.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public void SaveReserva(Reservacion pReserva)
{
string sqlReserva = string.Empty;
SqlCommand cmdReservacion = new SqlCommand();
SqlCommand cmdHabitacion = new SqlCommand();
List <IDbCommand> listaCommands = new List <IDbCommand>();
double rows = 0;
//pReserva.CheckIN = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
//pReserva.CheckOUT = (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue;
sqlReserva = @"usp_INSERT_Reservacion";
cmdReservacion.CommandType = CommandType.StoredProcedure;
try
{
// Encabezado de factura
cmdReservacion.Parameters.AddWithValue("@ID", pReserva.ID);
cmdReservacion.Parameters.AddWithValue("@IDHuesped", pReserva._Huesped.ID);
cmdReservacion.Parameters.AddWithValue("@NUMHabitacion", pReserva._Habitacion.NUM);
cmdReservacion.Parameters.AddWithValue("@CheckIN", pReserva.CheckIN);
cmdReservacion.Parameters.AddWithValue("@CheckOUT", pReserva.CheckOUT);
cmdReservacion.Parameters.AddWithValue("@CantDias", pReserva.CantDias);
cmdReservacion.Parameters.AddWithValue("@Subtotal", pReserva.Subtotal);
cmdReservacion.CommandText = sqlReserva;
// Agregar a la lista de commands
listaCommands.Add(cmdReservacion);
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
rows = db.ExecuteNonQuery(listaCommands, IsolationLevel.ReadCommitted);
}
// Si devuelve filas quiere decir que se salvo entonces extraerlo
if (rows == 0)
{
throw new Exception("No se pudo guardar correctamente la Reserva");
}
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", cmdReservacion.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}
public Reservacion GetReserva(double pNumeroReserva)
{
Reservacion oReservacion = new Reservacion();
DataSet ds = null;
IDALHabitacion _DALHabitacion = new DALHabitacion();
IDALHuesped _DALHuesped = new DALHuesped();
SqlCommand command = new SqlCommand();
string sql = @" ";
sql = @"select * from [Reservacion] where ID = @ID";
try
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@ID", pNumeroReserva);
using (IDataBase db = FactoryDataBase.CreateDataBase(FactoryConexion.CreateConnection(_Usuario.Login, _Usuario.Password)))
{
ds = db.ExecuteReader(command, "query");
}
// Si devolvió filas
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
oReservacion = new Reservacion()
{
ID = (int)dr["ID"],
_Huesped = _DALHuesped.GetHuespedById(double.Parse(dr["IDHuesped"].ToString())),
_Habitacion = _DALHabitacion.GetHabitacionById(double.Parse(dr["NUMHabitacion"].ToString())),
CheckIN = (DateTime)(dr["CheckIN"]),
CheckOUT = (DateTime)(dr["CheckIN"]),
CantDias = (int)dr["CantDias"],
Subtotal = double.Parse(dr["Subtotal"].ToString())
};
foreach (var item in ds.Tables[0].Rows)
{
oReservacion.ID = double.Parse(dr["ID"].ToString());
oReservacion._Huesped = _DALHuesped.GetHuespedById(double.Parse(dr["IDHuesped"].ToString()));
oReservacion._Habitacion = _DALHabitacion.GetHabitacionById(double.Parse(dr["NUMHabitacion"].ToString()));
oReservacion.CheckIN = (DateTime)(dr["CheckIN"]);
oReservacion.CheckOUT = (DateTime)(dr["CheckOUT"]);
oReservacion.CantDias = double.Parse(dr["CantDias"].ToString());
oReservacion.Subtotal = double.Parse(dr["Subtotal"].ToString());
}
}
return(oReservacion);
}
catch (SqlException sqlError)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateSQLExceptionsErrorDetails(sqlError));
msg.AppendFormat("SQL {0}\n", command.CommandText);
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
catch (Exception er)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat(Utilitarios.CreateGenericErrorExceptionDetail(er));
_MyLogControlEventos.ErrorFormat("Error {0}", msg.ToString());
throw;
}
}