/// <summary>
/// Retrieves all the RSS urls to parse from database store.
/// </summary>
/// <returns>List of Urls to match</returns>
private List <RssUrlInfo> LoadRSSUrls()
{
RssUrlInfo info = null;
List <RssUrlInfo> lst = new List <RssUrlInfo>();
HandleDatabase hdb = new HandleDatabase(_connection);
string sqlToUse =
"select orden, codObjetoSe, texto AS rss " +
"from objetosecontenido ,objetose " +
"where id_objetose = codobjetose AND nomobjetose = 'EC-Bloguer' AND atributo = 'rutaRssBlog' " +
"order by orden ";
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("loadRssUrls");
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sqlToUse);
while (rdr.Read())
{
info = new RssUrlInfo();
string url = rdr["rss"].ToString();
if (url != null && url != "")
{
info.Url = url;
info.Order = Convert.ToInt32(rdr["orden"].ToString());
info.ObjectSECode = Convert.ToInt32(rdr["codObjetoSe"].ToString());
lst.Add(info);
}
}
rdr.Close();
transaction.Commit();
hdb.Close();
return(lst);
}
public List <DetallePeliculaDto> getPelicula(PeliculaDto peliculaObj)
{
SqlTransaction transaction = null;
SqlDataReader rdr = null;
HandleDatabase hdb = null;
try {
List <DetallePeliculaDto> lstPelicula = new List <DetallePeliculaDto>();
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter();
param.ParameterName = "@IDPELICULA";
param.Value = peliculaObj.idPelicula;
param.SqlDbType = SqlDbType.Int;
String sql = "sp_obtenerPelicula @IDPELICULA";
transaction = hdb.BeginTransaction("getPelicula");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
while (rdr.Read())
{
DetallePeliculaDto objPelicula = new DetallePeliculaDto();
int idPelicula = Convert.ToInt32(rdr["idPelicula"]);
int idDetallePelicula = Convert.ToInt32(rdr["idDetallePelicula"]);
string nombrePelicula = rdr["nombrePelicula"].ToString();
int idUsuarioCreador = Convert.ToInt32(rdr["idUsuarioCreador"].ToString());
string fechaCreacionPelicula = rdr["fechaCreacionPelicula"].ToString();
int idGeneroPelicula = Convert.ToInt32(rdr["idGeneroPelicula"].ToString());
string sinopsis = rdr["sinopsis"].ToString();
string url = rdr["urlArticuloEC"].ToString();
string activo = rdr["activo"].ToString();
string imagenCartelera = rdr["imagenCartelera"].ToString();
objPelicula.idPelicula = idPelicula;
objPelicula.idDetallePelicula = idDetallePelicula;
objPelicula.nombrePelicula = nombrePelicula;
objPelicula.idUsuarioCreador = idUsuarioCreador;
objPelicula.fechaCreacionPelicula = Convert.ToDateTime(fechaCreacionPelicula);
objPelicula.idGeneroPelicula = idGeneroPelicula;
objPelicula.imagenCartelera = imagenCartelera;
objPelicula.sinopsis = sinopsis;
objPelicula.urlArticuloEc = url;
objPelicula.enCartelera = activo;
lstPelicula.Add(objPelicula);
}
return(lstPelicula);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
public List <MovieFullInfo> getMovieFullinfo()
{
string sql = "";
List <MovieFullInfo> movieFullList = new List <MovieFullInfo>();
sql += "select * from vw_datospelicula ";
sql += "where activo = 'S' ";
sql += " and DATEFROMPARTS (annoHorarioPelicula, mesHorarioPelicula, diaHorarioPelicula) >= ";
sql += " DATEADD(DAY, -1, DATEFROMPARTS (YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), DAY(CURRENT_TIMESTAMP))) ";
sql += "order by idPelicula, idTeatro, frecuencia, idformato ";
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
transaction = hdb.BeginTransaction("getMovieFullinfo");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read())
{
movieFullList.Add(new MovieFullInfo()
{
id = Convert.ToInt32(rdr["idPelicula"]),
name = rdr["nombrePelicula"].ToString(),
nameFull = rdr["nombrePelicula"].ToString() + " " + rdr["nombreFormato"].ToString(),
img = rdr["imagenCartelera"].ToString(),
url = rdr["urlArticuloEC"].ToString(),
active = rdr["activo"].ToString(),
idGenre = Convert.ToInt32(rdr["idGeneroPelicula"]),
genre = rdr["nombreGenero"].ToString(),
idLocation = Convert.ToInt32(rdr["idTeatro"]),
nameLocation = rdr["nombreCine"].ToString(),
branchName = rdr["nombreTeatro"].ToString(),
nameFullLocation = rdr["nombreCine"].ToString() + " " + rdr["nombreTeatro"].ToString(),
address = rdr["direccionTeatro"].ToString(),
idFormat = Convert.ToInt32(rdr["idFormato"]),
nameFormat = rdr["nombreFormato"].ToString(),
idShow = Convert.ToInt32(rdr["idHorarioPelicula"]),
dt = new DateTime(Convert.ToInt32(rdr["annoHorarioPelicula"]), Convert.ToInt32(rdr["mesHorarioPelicula"]), Convert.ToInt32(rdr["diaHorarioPelicula"]))
});
}
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
return(movieFullList);
}
/// <summary>
/// Queries database to locate hours for a time period of time for a movie.
/// </summary>
/// <param name="id">Record to locate</param>
/// <returns>List of hours for movie.</returns>
public List <MovieShowHour> getMovieShowHoursFor(int id)
{
string sql = "select * from tbl_hora where idHorarioPelicula = @id order by horaPelicula, minutoPelicula ";
List <MovieShowHour> movieHours = new List <MovieShowHour>();
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
transaction = hdb.BeginTransaction("getMovieHoursFor");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, new SqlParameter()
{
ParameterName = "@id", Value = id, SqlDbType = SqlDbType.BigInt
});
while (rdr.Read())
{
movieHours.Add(new MovieShowHour()
{
id = Convert.ToInt32(rdr["idHora"]),
timeHour = Convert.ToInt32(rdr["horaPelicula"]),
timeMinute = Convert.ToInt32(rdr["minutoPelicula"])
});
}
// Let's fill field timeFull
movieHours.ForEach(h => {
string ampm = "am";
int hour = h.timeHour;
if (hour > 12)
{
ampm = "pm";
hour -= 12;
}
if (hour == 12)
{
ampm = "pm";
}
h.timeFull = hour + ":" + h.timeMinute.ToString().PadLeft(2, '0') + " " + ampm;
});
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
return(movieHours);
}
/// <summary>
/// Puts info into DB.
/// </summary>
/// <param name="info">Record information to submit</param>
/// <param name="op">Which kind to operation to make. 1:Insert, 2:update, 3:delete</param>
/// <returns>Identity ID for just created record.</returns>
public int crearEntidad(EntidadDto info, int op)
{
HandleDatabase hdb = null;
SqlTransaction transaction = null;
try {
int rslt = 0;
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter()
{
ParameterName = "@operacion", Value = op, SqlDbType = SqlDbType.Int
});
paramList.Add(new SqlParameter()
{
ParameterName = "@id", Value = info.idEntidad, SqlDbType = SqlDbType.Int
});
paramList.Add(new SqlParameter()
{
ParameterName = "@codigo", Value = info.codEntidad.ToString(), SqlDbType = SqlDbType.VarChar
});
paramList.Add(new SqlParameter()
{
ParameterName = "@nombre", Value = info.nombreEntidad, SqlDbType = SqlDbType.VarChar
});
paramList.Add(new SqlParameter()
{
ParameterName = "@valor", Value = info.valorEntidad, SqlDbType = SqlDbType.VarChar
});
paramList.Add(new SqlParameter()
{
ParameterName = "@descripcion", Value = info.descripcionEntidad, SqlDbType = SqlDbType.VarChar
});
String sql = "sp_crearActualizarEntidad @operacion, @id, @codigo, @nombre, @valor, @descripcion";
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
transaction = hdb.BeginTransaction("crearEntidad");
rslt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sql, paramList.ToArray());
return(rslt);
} catch (Exception) {
return(-1);
} finally {
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
/// <summary>
/// Retrieves one record from DB.
/// </summary>
/// <param name="id">Filter to use</param>
/// <returns>NULL if no record found.</returns>
public TeatroDto getTeatro(int id)
{
SqlTransaction transaction = null;
SqlDataReader rdr = null;
HandleDatabase hdb = null;
try {
TeatroDto r = null;
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = id;
param.SqlDbType = SqlDbType.Int;
string sql = "sp_obtenerTeatro @id";
transaction = hdb.BeginTransaction("getTeatro");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
if (rdr.HasRows)
{
rdr.Read();
r = new TeatroDto()
{
idTeatro = Convert.ToInt32(rdr["idteatro"]),
idCine = Convert.ToInt32(rdr["idcine"]),
nombreTeatro = rdr["nombreteatro"].ToString(),
telefono1Teatro = rdr["telefono1teatro"].ToString(),
telefono2Teatro = rdr["telefono2teatro"].ToString(),
telefono3Teatro = rdr["telefono3teatro"].ToString(),
idMunicipioTeatro = Convert.ToInt32(rdr["idMunicipioTeatro"]),
idDepeartamentoTeatro = Convert.ToInt32(rdr["idDepartamentoTeatro"]),
direccionTeatro = rdr["direccionteatro"].ToString()
};
}
return(r);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
public List <TeatroExDto> getTeatrosEx()
{
SqlDataReader rdr = null;
SqlTransaction transaction = null;
HandleDatabase hdb = null;
try {
List <TeatroExDto> lstTeatros = new List <TeatroExDto>();
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
String sql = "sp_obtenerExTeatros";
transaction = hdb.BeginTransaction("getTeatrosEx");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read())
{
lstTeatros.Add(new TeatroExDto()
{
idTeatro = Convert.ToInt32(rdr["idteatro"]),
idCine = Convert.ToInt32(rdr["idcine"]),
nombreTeatro = rdr["nombreteatro"].ToString(),
telefono1Teatro = rdr["telefono1teatro"].ToString(),
telefono2Teatro = rdr["telefono2teatro"].ToString(),
telefono3Teatro = rdr["telefono3teatro"].ToString(),
idMunicipioTeatro = Convert.ToInt32(rdr["idMunicipioTeatro"]),
idDepeartamentoTeatro = Convert.ToInt32(rdr["idDepartamentoTeatro"]),
direccionTeatro = rdr["direccionteatro"].ToString(),
nombreCine = rdr["nombreCine"].ToString(),
nombreMunicipio = rdr["nombreMunicipio"].ToString(),
nombreDepartamento = rdr["nombreDepartamento"].ToString()
});
}
return(lstTeatros);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
/// <summary>
/// Queries database to locate hours for a time period of time for a movie.
/// </summary>
/// <param name="id">Record to locate</param>
/// <returns>List of hours for movie.</returns>
private List <MovieLookupShowHours> LoadHoursFor(int id)
{
string sql = "select * from tbl_hora where idHorarioPelicula = @id order by horaPelicula, minutoPelicula ";
List <MovieLookupShowHours> hours = new List <MovieLookupShowHours>();
HandleDatabase hdb = new HandleDatabase(dbConnection);
hdb.Open();
SqlTransaction transaction = hdb.BeginTransaction("LoadHoursFor");
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@id";
param1.Value = id;
param1.SqlDbType = SqlDbType.BigInt;
SqlDataReader rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param1);
while (rdr.Read())
{
MovieLookupShowHours msh = new MovieLookupShowHours()
{
id = Convert.ToInt32(rdr["idHora"]),
timeHour = Convert.ToInt32(rdr["horaPelicula"]),
timeMinute = Convert.ToInt32(rdr["minutoPelicula"]),
room = rdr["sala"].ToString()
};
hours.Add(msh);
}
// Now that we have all of the hours set, we need now to compute a time format on timeHour
// and timeMinute fields.
foreach (var item in hours)
{
string ampm = "am";
int hour = item.timeHour;
if (hour > 12)
{
ampm = "pm";
hour -= 12;
}
if (hour == 12)
{
ampm = "pm";
}
item.timeFull = hour + ":" + item.timeMinute.ToString().PadLeft(2, '0') + " " + ampm;
}
return(hours);
}
/// <summary>
/// Retrieves one record from DB.
/// </summary>
/// <param name="nombreParametro">Filter to use</param>
/// <returns>NULL if no record found.</returns>
public ParametroSistemaDto ObtenerValorParametroSistema(string nombreParametro)
{
SqlTransaction transaction = null;
SqlDataReader rdr = null;
HandleDatabase hdb = null;
try {
ParametroSistemaDto r = null;
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter();
param.ParameterName = "@np";
param.Value = nombreParametro;
param.SqlDbType = SqlDbType.VarChar;
string sql = "sp_obtenerValorParametroSistema @np";
transaction = hdb.BeginTransaction("ObtenerValorParametroSistema");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
if (rdr.HasRows)
{
rdr.Read();
r = new ParametroSistemaDto()
{
idParametro = Convert.ToInt32(rdr["idParametro"]),
nombreParametro = rdr["nombreParametro"].ToString(),
valorParametro = rdr["valorParametro"].ToString(),
descValorParametro = rdr["descValorParametro"].ToString(),
visible = Convert.ToChar(rdr["visible"])
};
}
return(r);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
/// <summary>
/// Puts info into DB.
/// </summary>
/// <param name="info">Record information to submit</param>
/// <param name="op">Which kind to operation to make. 1:Insert, 2:update, 3:delete</param>
/// <returns>Identity ID for just created record.</returns>
public int crearCine(CineDto info, int op)
{
SqlTransaction transaction = null;
HandleDatabase hdb = null;
int rslt = 0;
try {
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter()
{
ParameterName = "@operacion", Value = op, SqlDbType = SqlDbType.Int
});
paramList.Add(new SqlParameter()
{
ParameterName = "@id", Value = info.idCine, SqlDbType = SqlDbType.Int
});
paramList.Add(new SqlParameter()
{
ParameterName = "@nombreCine", Value = info.nombreCine.ToString(), SqlDbType = SqlDbType.VarChar
});
paramList.Add(new SqlParameter()
{
ParameterName = "@fechaCreacionCine", Value = info.fechaCreacionCine, SqlDbType = SqlDbType.DateTime
});
paramList.Add(new SqlParameter()
{
ParameterName = "@nit", Value = info.nit, SqlDbType = SqlDbType.VarChar
});
String sql = "sp_crearActualizarCine @operacion, @id, @nombreCine, @fechaCreacionCine, @nit";
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
transaction = hdb.BeginTransaction("crearCine");
rslt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sql, paramList.ToArray());
return(rslt);
} catch (Exception) {
return(0);
} finally {
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
/// <summary>
/// Get all records given name entity.
/// </summary>
/// <param name="nombreEntidad">The entity to retrieve from</param>
/// <returns>A list of EntidadDto objects, empty if no records found</returns>
public List <EntidadDto> obtenerValoresEntidad(string nombreEntidad)
{
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
try {
List <EntidadDto> listaResultado = new List <EntidadDto>();
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter();
param.ParameterName = "@ne";
param.Value = nombreEntidad.Trim().ToString();
param.SqlDbType = SqlDbType.VarChar;
String sql = "sp_obtenerValoresEntidad @ne";
transaction = hdb.BeginTransaction(sql);
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
EntidadDto entidad;
while (rdr.Read())
{
entidad = new EntidadDto();
entidad.idEntidad = Convert.ToInt32(rdr["IDENTIDAD"]);
entidad.codEntidad = Convert.ToInt32(rdr["CODENTIDAD"]);
entidad.nombreEntidad = rdr["NOMBREENTIDAD"].ToString();
entidad.valorEntidad = rdr["VALORENTIDAD"].ToString();
entidad.descripcionEntidad = rdr["DESCRIPCIONENTIDAD"].ToString();
listaResultado.Add(entidad);
}
return(listaResultado);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
/// <summary>
/// Retrieves one record from DB.
/// </summary>
/// <param name="id">Filter to use</param>
/// <returns>NULL if no record found.</returns>
public CineDto getCine(int id)
{
SqlDataReader rdr = null;
SqlTransaction transaction = null;
HandleDatabase hdb = null;
try {
CineDto r = null;
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = id;
param.SqlDbType = SqlDbType.Int;
string sql = "sp_obtenerCine @id";
transaction = hdb.BeginTransaction("getCine");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
if (rdr.HasRows)
{
rdr.Read();
r = new CineDto()
{
idCine = Convert.ToInt32(rdr["idCine"]),
nit = rdr["nit"].ToString(),
fechaCreacionCine = Convert.ToDateTime(rdr["fechaCreacionCine"]),
nombreCine = rdr["nombreCine"].ToString()
};
}
return(r);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
/// <summary>
/// Retrieves one record from DB given id.
/// </summary>
/// <param name="id">id to match</param>
/// <returns>NULL if not found, else record information</returns>
public EntidadDto obtenerValorEntidad(int id)
{
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
try {
EntidadDto r = null;
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = id;
param.SqlDbType = SqlDbType.Int;
String sql = "sp_obtenerValorEntidad @id";
transaction = hdb.BeginTransaction("obtenerValorEntidad");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
if (rdr.HasRows)
{
rdr.Read();
r = new EntidadDto();
r.idEntidad = Convert.ToInt32(rdr["IDENTIDAD"]);
r.codEntidad = Convert.ToInt32(rdr["CODENTIDAD"]);
r.nombreEntidad = rdr["NOMBREENTIDAD"].ToString();
r.valorEntidad = rdr["VALORENTIDAD"].ToString();
r.descripcionEntidad = rdr["DESCRIPCIONENTIDAD"].ToString();
}
return(r);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
public List <PeliculaDto> getPeliculasPorTeatro(int teatro)
{
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
try {
List <PeliculaDto> lstPeliculasCine = new List <PeliculaDto>();
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter paramTeatro = new SqlParameter();
paramTeatro.ParameterName = "@TEATRO";
paramTeatro.Value = teatro;
paramTeatro.SqlDbType = SqlDbType.Int;
String sql = "sp_obtenerPeliculasPorTeatro @TEATRO";
transaction = hdb.BeginTransaction("PeliculasPorTeatro");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramTeatro);
while (rdr.Read())
{
PeliculaDto objPeliculaCine = new PeliculaDto();
int idPelicula = Convert.ToInt32(rdr["idPelicula"]);
int idTeatro = Convert.ToInt32(rdr["idTeatro"]);
objPeliculaCine.idPelicula = idPelicula;
objPeliculaCine.idTeatro = idTeatro;
lstPeliculasCine.Add(objPeliculaCine);
}
return(lstPeliculasCine);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
/// <summary>
/// Retrieves all values for table PARAMETROSISTEMA.
/// </summary>
/// <returns>List of ParametroSistemaDto objects</returns>
public List <ParametroSistemaDto> ObtenerValoresParametroSistema()
{
SqlDataReader rdr = null;
SqlTransaction transaction = null;
HandleDatabase hdb = null;
try {
string sql = "sp_obtenerValoresParametroSistema";
List <ParametroSistemaDto> sysParamList = new List <ParametroSistemaDto>();
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
transaction = hdb.BeginTransaction("obtenerValoresParametroSistema");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read())
{
sysParamList.Add(new ParametroSistemaDto()
{
idParametro = Convert.ToInt32(rdr["idParametro"]),
nombreParametro = rdr["nombreParametro"].ToString(),
valorParametro = rdr["valorParametro"].ToString(),
descValorParametro = rdr["descValorParametro"].ToString(),
visible = Convert.ToChar(rdr["visible"])
});
}
return(sysParamList);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
/// <summary>
/// Retrieves all values for table CINE.
/// </summary>
/// <returns>List of CineDto objects</returns>
public List <CineDto> getCines()
{
HandleDatabase hdb = null;
SqlDataReader rdr = null;
SqlTransaction transaction = null;
try {
List <CineDto> movieList = new List <CineDto>();
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
string sql = "sp_obtenerCines";
transaction = hdb.BeginTransaction("getCines");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read())
{
movieList.Add(new CineDto()
{
idCine = Convert.ToInt32(rdr["idCine"]),
nit = rdr["nit"].ToString(),
fechaCreacionCine = Convert.ToDateTime(rdr["fechaCreacionCine"]),
nombreCine = rdr["nombreCine"].ToString()
});
}
return(movieList);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
public int crearPeliculaTeatro(int teatro, string peliculas)
{
SqlTransaction transaction = null;
SqlDataReader rdr = null;
HandleDatabase hdb = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter paramPeliculas = new SqlParameter();
SqlParameter paramTeatro = new SqlParameter();
paramPeliculas.ParameterName = "@peliculas";
paramTeatro.ParameterName = "@teatro";
paramPeliculas.Value = peliculas;
paramPeliculas.SqlDbType = SqlDbType.VarChar;
paramTeatro.Value = teatro;
paramTeatro.SqlDbType = SqlDbType.Int;
String sql = "sp_crearPeliculasPorTeatro @teatro,@peliculas";
transaction = hdb.BeginTransaction("crearPeliculaPorTeatro");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramTeatro, paramPeliculas);
return(0);
} catch (Exception) {
return(-1);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
public void TestAddIntoDatabaseOfHandleDatabseClassPassEmployeeDetailsObejctsAndGetTheNumberOfRowsAffected()
{
EmployeeDetails employeeDetails = new EmployeeDetails()
{
EmployeeID = 12,
Name = "New Name",
StartDate = Convert.ToDateTime("05/04/2020"),
Gender = "F",
Phone = "7889564512",
SalaryID = 115,
Address = "New Address",
IsActive = 1
};
CompanyData companyData = new CompanyData()
{
DepartmentID = 510,
DepartmentName = "New Department 5"
};
Department department = new Department()
{
DepartmentID = 510,
EmployeeID = 12
};
PayrollDetails payrollDetails = new PayrollDetails()
{
SalaryID = 115,
BasicPay = 7889,
Deduction = 78,
IncomeTax = 56,
NetPay = 7715,
Taxable = 156
};
int actual = HandleDatabase.AddAnEmployee(employeeDetails, companyData, department, payrollDetails);
Assert.AreEqual(4, actual);
}
/// <summary>
/// Retrieves one record from DB.
/// </summary>
/// <param name="id">Filter to use</param>
/// <returns>NULL if no record found.</returns>
public TeatroDto getTeatro(int id)
{
if (log.IsDebugEnabled)
{
log.Debug("getTeatro Starts");
}
SqlTransaction transaction = null;
SqlDataReader rdr = null;
HandleDatabase hdb = null;
TeatroDto r = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter()
{
ParameterName = "@id", Value = id, SqlDbType = SqlDbType.Int
};
string sql = "sp_obtenerTeatro @id";
if (log.IsDebugEnabled)
{
log.Debug("SQL=[" + sql + "]");
var paramValues = "ParameterName=[" + param.ParameterName + "], Value=[" + param.Value + "], SqlDbType=[" + param.SqlDbType + "]";
log.Debug("Parameter val=[" + paramValues + "]");
}
transaction = hdb.BeginTransaction("getTeatro");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
if (rdr.HasRows)
{
rdr.Read();
r = new TeatroDto()
{
idTeatro = Convert.ToInt32(rdr["idteatro"]),
idCine = Convert.ToInt32(rdr["idcine"]),
nombreTeatro = rdr["nombreteatro"].ToString(),
telefono1Teatro = rdr["telefono1teatro"].ToString(),
telefono2Teatro = rdr["telefono2teatro"].ToString(),
telefono3Teatro = rdr["telefono3teatro"].ToString(),
idMunicipioTeatro = Convert.ToInt32(rdr["idMunicipioTeatro"]),
idDepeartamentoTeatro = Convert.ToInt32(rdr["idDepartamentoTeatro"]),
direccionTeatro = rdr["direccionteatro"].ToString()
};
}
} catch (Exception ex) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Result sets to null");
}
r = null;
} finally {
try {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
} catch (Exception e) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + e.Message);
log.Fatal("Exception trace=[" + e.StackTrace + "]");
log.Fatal("Result sets to null");
}
r = null;
}
}
if (log.IsDebugEnabled)
{
if (r == null)
{
log.Debug("Result is NULL");
}
else
{
log.Debug("Result sets to [" + r.ToString() + "]");
}
log.Debug("getTeatro Ends");
}
return(r);
}
/// <summary>
/// Puts info into DB.
/// </summary>
/// <param name="info">Record information to submit</param>
/// <param name="op">Which kind to operation to make. 1:Insert, 2:update, 3:delete</param>
/// <returns>Identity ID for just created record.</returns>
public int createCine(CineDto info, int op)
{
if (log.IsDebugEnabled)
{
log.Debug("createCine Starts");
}
SqlTransaction transaction = null;
HandleDatabase hdb = null;
int rslt = 0;
try {
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter()
{
ParameterName = "@operacion", Value = op, SqlDbType = SqlDbType.Int
});
paramList.Add(new SqlParameter()
{
ParameterName = "@id", Value = info.idCine, SqlDbType = SqlDbType.Int
});
paramList.Add(new SqlParameter()
{
ParameterName = "@nombreCine", Value = info.nombreCine.ToString(), SqlDbType = SqlDbType.VarChar
});
paramList.Add(new SqlParameter()
{
ParameterName = "@fechaCreacionCine", Value = info.fechaCreacionCine, SqlDbType = SqlDbType.DateTime
});
paramList.Add(new SqlParameter()
{
ParameterName = "@nit", Value = info.nit, SqlDbType = SqlDbType.VarChar
});
String sql = "sp_crearActualizarCine @operacion, @id, @nombreCine, @fechaCreacionCine, @nit";
var i = 1;
if (log.IsDebugEnabled)
{
log.Debug("SQL=[" + sql + "]");
paramList.ForEach(p => {
var paramValues = "ParameterName=[" + p.ParameterName + "], Value=[" + p.Value + "], SqlDbType=[" + p.SqlDbType + "]";
log.Debug("Parameter " + i++ + " val=[" + paramValues + "]");
});
}
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
transaction = hdb.BeginTransaction("crearCine");
rslt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sql, paramList.ToArray());
if (log.IsDebugEnabled)
{
log.Debug("createCine Ends");
}
} catch (Exception ex) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Returns 0");
}
rslt = 0;
} finally {
try {
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
} catch (Exception e) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + e.Message);
log.Fatal("Exception trace=[" + e.StackTrace + "]");
log.Fatal("Returns 0");
}
rslt = 0;
}
}
log.Debug("Rslt=[" + rslt + "]");
if (log.IsDebugEnabled)
{
log.Debug("createCine Ends");
}
return(rslt);
}
/// <summary>
/// Retrieves all values for table CINE.
/// </summary>
/// <returns>List of CineDto objects</returns>
public List <CineDto> getCines()
{
if (log.IsDebugEnabled)
{
log.Debug("getCines Starts");
}
HandleDatabase hdb = null;
SqlDataReader rdr = null;
SqlTransaction transaction = null;
List <CineDto> movieList = new List <CineDto>();
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
string sql = "sp_obtenerCines";
if (log.IsDebugEnabled)
{
log.Debug("Sql=[" + sql + "]");
}
transaction = hdb.BeginTransaction("getCines");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read())
{
movieList.Add(new CineDto()
{
idCine = Convert.ToInt32(rdr["idCine"]),
nit = rdr["nit"].ToString(),
fechaCreacionCine = Convert.ToDateTime(rdr["fechaCreacionCine"]),
nombreCine = rdr["nombreCine"].ToString()
});
}
} catch (Exception ex) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Empty list returned");
}
movieList = new List <CineDto>();
} finally {
try {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
} catch (Exception e) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + e.Message);
log.Fatal("Exception trace=[" + e.StackTrace + "]");
log.Fatal("Empty list returned");
}
movieList = new List <CineDto>();
}
}
if (log.IsDebugEnabled)
{
log.Debug("getCines Ends");
}
return(movieList);
}
/// <summary>
/// Retrieves one record from DB.
/// </summary>
/// <param name="id">Filter to use</param>
/// <returns>NULL if no record found.</returns>
public CineDto getCine(int id)
{
if (log.IsDebugEnabled)
{
log.Debug("getCine Starts");
log.Debug("id=[" + id + "]");
}
SqlDataReader rdr = null;
SqlTransaction transaction = null;
HandleDatabase hdb = null;
CineDto r = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter()
{
ParameterName = "@id", Value = id, SqlDbType = SqlDbType.Int
};
string sql = "sp_obtenerCine @id";
if (log.IsDebugEnabled)
{
log.Debug("SQL=[" + sql + "]");
var paramValues = "ParameterName=[" + param.ParameterName + "], Value=[" + param.Value + "], SqlDbType=[" + param.SqlDbType + "]";
log.Debug("Parameter val=[" + paramValues + "]");
}
transaction = hdb.BeginTransaction("getCine");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
if (rdr.HasRows)
{
rdr.Read();
r = new CineDto()
{
idCine = Convert.ToInt32(rdr["idCine"]),
nit = rdr["nit"].ToString(),
fechaCreacionCine = Convert.ToDateTime(rdr["fechaCreacionCine"]),
nombreCine = rdr["nombreCine"].ToString()
};
if (log.IsDebugEnabled)
{
log.Debug("Record retrieved =[" + r.ToString() + "]");
}
}
} catch (Exception ex) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
}
r = null;
} finally {
try {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
} catch (Exception e) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + e.Message);
log.Fatal("Exception trace=[" + e.StackTrace + "]");
}
r = null;
}
}
if (log.IsDebugEnabled)
{
if (r == null)
{
log.Debug("Result is NULL");
}
else
{
log.Debug("Result sets to [" + r.ToString() + "]");
}
log.Debug("getCine Ends");
}
return(r);
}
/// <summary>
/// Retrieves one record from DB.
/// </summary>
/// <param name="nombreParametro">Filter to use</param>
/// <returns>NULL if no record found.</returns>
public ParametroSistemaDto getValorParametroSistema(string nombreParametro)
{
if (log.IsDebugEnabled)
{
log.Debug("getValorParametroSistema Starts");
}
SqlTransaction transaction = null;
SqlDataReader rdr = null;
HandleDatabase hdb = null;
ParametroSistemaDto r = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter()
{
ParameterName = "@np", Value = nombreParametro, SqlDbType = SqlDbType.VarChar
};
string sql = "sp_obtenerValorParametroSistema @np";
if (log.IsDebugEnabled)
{
log.Debug("SQL=[" + sql + "]");
var paramValues = "ParameterName=[" + param.ParameterName + "], Value=[" + param.Value + "], SqlDbType=[" + param.SqlDbType + "]";
log.Debug("Parameter val=[" + paramValues + "]");
}
transaction = hdb.BeginTransaction("ObtenerValorParametroSistema");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
if (rdr.HasRows)
{
rdr.Read();
r = new ParametroSistemaDto()
{
idParametro = Convert.ToInt32(rdr["idParametro"]),
nombreParametro = rdr["nombreParametro"].ToString(),
valorParametro = rdr["valorParametro"].ToString(),
descValorParametro = rdr["descValorParametro"].ToString(),
visible = Convert.ToChar(rdr["visible"])
};
}
} catch (Exception ex) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Return null");
}
r = null;
} finally {
try {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
} catch (Exception e) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + e.Message);
log.Fatal("Exception trace=[" + e.StackTrace + "]");
log.Fatal("Return null");
}
r = null;
}
}
if (log.IsDebugEnabled)
{
if (r == null)
{
log.Debug("Result is NULL");
}
else
{
log.Debug("Result sets to [" + r.ToString() + "]");
}
log.Debug("getValorParametroSistema Ends");
}
return(r);
}
/// <summary>
/// Retrieves all values for table PARAMETROSISTEMA.
/// </summary>
/// <returns>List of ParametroSistemaDto objects</returns>
public List <ParametroSistemaDto> getValoresParametroSistema()
{
if (log.IsDebugEnabled)
{
log.Debug("getValoresParametroSistema Starts");
}
SqlDataReader rdr = null;
SqlTransaction transaction = null;
HandleDatabase hdb = null;
List <ParametroSistemaDto> sysParamList = new List <ParametroSistemaDto>();
try {
string sql = "sp_obtenerValoresParametroSistema";
if (log.IsDebugEnabled)
{
log.Debug("SQL=[" + sql + "]");
}
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
transaction = hdb.BeginTransaction("obtenerValoresParametroSistema");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read())
{
sysParamList.Add(new ParametroSistemaDto()
{
idParametro = Convert.ToInt32(rdr["idParametro"]),
nombreParametro = rdr["nombreParametro"].ToString(),
valorParametro = rdr["valorParametro"].ToString(),
descValorParametro = rdr["descValorParametro"].ToString(),
visible = Convert.ToChar(rdr["visible"])
});
}
} catch (Exception ex) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("List returned is empty");
}
sysParamList = new List <ParametroSistemaDto>();
} finally {
try {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
} catch (Exception e) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + e.Message);
log.Fatal("Exception trace=[" + e.StackTrace + "]");
log.Fatal("List returned is empty");
}
sysParamList = new List <ParametroSistemaDto>();
}
}
if (log.IsDebugEnabled)
{
log.Debug("getValoresParametroSistema Ends");
}
return(sysParamList);
}
/// <summary>
/// Extends the data transfer object to expose the theater name and movie name
/// </summary>
/// <returns>A list of TeatroExDto objects</returns>
public List <TeatroExDto> getTeatrosEx()
{
if (log.IsDebugEnabled)
{
log.Debug("getTeatrosEx Starts");
}
SqlDataReader rdr = null;
SqlTransaction transaction = null;
HandleDatabase hdb = null;
List <TeatroExDto> lstTeatros = new List <TeatroExDto>();
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
String sql = "sp_obtenerExTeatros";
if (log.IsDebugEnabled)
{
log.Debug("Sql=[" + sql + "]");
}
transaction = hdb.BeginTransaction("getTeatrosEx");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read())
{
lstTeatros.Add(new TeatroExDto()
{
idTeatro = Convert.ToInt32(rdr["idteatro"]),
idCine = Convert.ToInt32(rdr["idcine"]),
nombreTeatro = rdr["nombreteatro"].ToString(),
telefono1Teatro = rdr["telefono1teatro"].ToString(),
telefono2Teatro = rdr["telefono2teatro"].ToString(),
telefono3Teatro = rdr["telefono3teatro"].ToString(),
idMunicipioTeatro = Convert.ToInt32(rdr["idMunicipioTeatro"]),
idDepeartamentoTeatro = Convert.ToInt32(rdr["idDepartamentoTeatro"]),
direccionTeatro = rdr["direccionteatro"].ToString(),
nombreCine = rdr["nombreCine"].ToString(),
nombreMunicipio = rdr["nombreMunicipio"].ToString(),
nombreDepartamento = rdr["nombreDepartamento"].ToString()
});
}
} catch (Exception ex) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Result sets to empty");
}
lstTeatros = new List <TeatroExDto>();
} finally {
try {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
} catch (Exception e) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + e.Message);
log.Fatal("Exception trace=[" + e.StackTrace + "]");
log.Fatal("Result sets to empty");
}
lstTeatros = new List <TeatroExDto>();
}
}
if (log.IsDebugEnabled)
{
log.Debug("getTeatrosEx Ends");
}
return(lstTeatros);
}
public List <DetallePeliculaDto> getPeliculas(PeliculaDto peliculaObj)
{
SqlDataReader rdr = null;
SqlTransaction transaction = null;
HandleDatabase hdb = null;
try {
List <DetallePeliculaDto> lstPelicula = new List <DetallePeliculaDto>();
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
String sql = "sp_obtenerPeliculas";
transaction = hdb.BeginTransaction("getPeliculas");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read())
{
DetallePeliculaDto objPelicula = new DetallePeliculaDto();
int idPelicula = Convert.ToInt32(rdr["idPelicula"]);
int idDetallePelicula = Convert.ToInt32(rdr["idDetallePelicula"]);
string nombrePelicula = rdr["nombrePelicula"].ToString();
int idUsuarioCreador = Convert.ToInt32(rdr["idUsuarioCreador"].ToString());
string fechaCreacionPelicula = rdr["fechaCreacionPelicula"].ToString();
int idGeneroPelicula = Convert.ToInt32(rdr["idGeneroPelicula"].ToString());
string sinopsis = rdr["sinopsis"].ToString();
string url = rdr["urlArticuloEC"].ToString();
string activo = rdr["activo"].ToString();
objPelicula.idPelicula = idPelicula;
objPelicula.idDetallePelicula = idDetallePelicula;
objPelicula.nombrePelicula = nombrePelicula;
objPelicula.idUsuarioCreador = idUsuarioCreador;
objPelicula.fechaCreacionPelicula = Convert.ToDateTime(fechaCreacionPelicula);
objPelicula.idGeneroPelicula = idGeneroPelicula;
objPelicula.sinopsis = sinopsis;
objPelicula.urlArticuloEc = url;
if (activo.Equals("S"))
{
objPelicula.enCartelera = "Si";
}
else
{
objPelicula.enCartelera = "No";
}
lstPelicula.Add(objPelicula);
}
return(lstPelicula);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
public int crearActualizarProgramacionPelicula(ProgramacionPeliculaDto datosProgramacion)
{
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter paramIdHorario = new SqlParameter();
SqlParameter paramIdFormato = new SqlParameter();
SqlParameter paramIdPelicula = new SqlParameter();
SqlParameter paramIdTeatro = new SqlParameter();
SqlParameter paramAnnoHorario = new SqlParameter();
SqlParameter paramMesHorario = new SqlParameter();
SqlParameter paramDiaHorario = new SqlParameter();
SqlParameter paramHoraMinuto = new SqlParameter();
SqlParameter paramSala = new SqlParameter();
SqlParameter paramNombreDia = new SqlParameter();
SqlParameter paramFrecuencia = new SqlParameter();
paramIdHorario.ParameterName = "@IDHORARIOPELICULA";
paramIdFormato.ParameterName = "@IDFORMATO";
paramIdPelicula.ParameterName = "@IDPELICULA";
paramIdTeatro.ParameterName = "@IDTEATRO";
paramAnnoHorario.ParameterName = "@ANNOHORARIO";
paramMesHorario.ParameterName = "@MESHORARIO";
paramDiaHorario.ParameterName = "@DIAHORARIO";
paramHoraMinuto.ParameterName = "@HORAMINUTO";
paramSala.ParameterName = "@SALA";
paramNombreDia.ParameterName = "@NOMBREDIA";
paramFrecuencia.ParameterName = "@FRECUENCIA";
paramIdHorario.Value = datosProgramacion.idHorarioPelicula;
paramIdHorario.SqlDbType = SqlDbType.Int;
paramIdFormato.Value = datosProgramacion.idFormato;
paramIdFormato.SqlDbType = SqlDbType.Int;
paramIdPelicula.Value = datosProgramacion.idPelicula;
paramIdPelicula.SqlDbType = SqlDbType.Int;
paramIdTeatro.Value = datosProgramacion.idTeatro;
paramIdTeatro.SqlDbType = SqlDbType.Int;
paramAnnoHorario.Value = datosProgramacion.annoHorarioPelicula;
paramAnnoHorario.SqlDbType = SqlDbType.Int;
paramMesHorario.Value = datosProgramacion.mesHorarioPelicula;
paramMesHorario.SqlDbType = SqlDbType.Int;
paramDiaHorario.Value = datosProgramacion.diaHorarioPelicula;
paramDiaHorario.SqlDbType = SqlDbType.Int;
paramHoraMinuto.Value = datosProgramacion.horaMinutoPelicula;
paramHoraMinuto.SqlDbType = SqlDbType.VarChar;
paramSala.Value = datosProgramacion.sala;
paramSala.SqlDbType = SqlDbType.VarChar;
paramNombreDia.Value = datosProgramacion.nombreDiaSemanaHorarioPelicula;
paramNombreDia.SqlDbType = SqlDbType.VarChar;
paramFrecuencia.Value = datosProgramacion.frecuencia;
paramFrecuencia.SqlDbType = SqlDbType.Int;
String sql = "SP_CREARACTUALIZARPROGRAMACIONPELICULA @IDHORARIOPELICULA,@IDFORMATO,@IDPELICULA,@IDTEATRO,@ANNOHORARIO,@MESHORARIO, @DIAHORARIO,@HORAMINUTO,@SALA, @NOMBREDIA,@FRECUENCIA";
transaction = hdb.BeginTransaction("crearProgramacionPelicula");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramIdHorario, paramIdFormato, paramIdPelicula, paramIdTeatro,
paramAnnoHorario, paramMesHorario, paramDiaHorario, paramHoraMinuto, paramSala,
paramNombreDia, paramFrecuencia);
return(0);
} catch (Exception) {
return(-1);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
public int crearPelicula(DetallePeliculaDto pelicula, int operacion)
{
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter paramOperacion = new SqlParameter();
SqlParameter paramPelicula = new SqlParameter();
SqlParameter paramNombre = new SqlParameter();
SqlParameter paramUsuario = new SqlParameter();
SqlParameter paramGenero = new SqlParameter();
SqlParameter paramSinopsis = new SqlParameter();
SqlParameter paramImagen = new SqlParameter();
SqlParameter paramUrl = new SqlParameter();
SqlParameter paramActivo = new SqlParameter();
paramOperacion.ParameterName = "@OPERACION";
paramPelicula.ParameterName = "@PELICULA";
paramNombre.ParameterName = "@NOMBRE";
paramUsuario.ParameterName = "@USUARIO";
paramGenero.ParameterName = "@GENERO";
paramSinopsis.ParameterName = "@SINOPSIS";
paramImagen.ParameterName = "@IMAGEN";
paramUrl.ParameterName = "@URL";
paramActivo.ParameterName = "@ACTIVO";
paramOperacion.Value = operacion;
paramOperacion.SqlDbType = SqlDbType.Int;
paramPelicula.Value = pelicula.idPelicula;
paramPelicula.SqlDbType = SqlDbType.Int;
paramNombre.Value = pelicula.nombrePelicula;
paramNombre.SqlDbType = SqlDbType.VarChar;
paramUsuario.Value = pelicula.idUsuarioCreador;
paramUsuario.SqlDbType = SqlDbType.Int;
paramGenero.Value = pelicula.idGeneroPelicula;
paramGenero.SqlDbType = SqlDbType.Int;
paramSinopsis.Value = pelicula.sinopsis;
paramSinopsis.SqlDbType = SqlDbType.VarChar;
paramImagen.Value = pelicula.imagenCartelera;
paramImagen.SqlDbType = SqlDbType.VarChar;
paramUrl.Value = pelicula.urlArticuloEc;
paramUrl.SqlDbType = SqlDbType.VarChar;
paramActivo.Value = pelicula.enCartelera;
paramActivo.SqlDbType = SqlDbType.VarChar;
String sql = "sp_crearActualizarPelicula @OPERACION,@PELICULA,@NOMBRE,@USUARIO,@GENERO,@SINOPSIS,@IMAGEN,@URL,@ACTIVO";
transaction = hdb.BeginTransaction("crearPelicula");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramOperacion, paramPelicula, paramNombre, paramUsuario,
paramGenero, paramSinopsis, paramImagen, paramUrl, paramActivo);
return(0);
} catch (Exception) {
return(-1);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
/// <summary>
/// Puts info into DB.
/// </summary>
/// <param name="info">Record information to submit</param>
/// <param name="op">Which kind to operation to make. 1:Insert, 2:update, 3:delete</param>
/// <returns>Identity ID for just created record.</returns>
public int crearTeatro(TeatroDto info, int op)
{
if (log.IsDebugEnabled)
{
log.Debug("crearTeatro Starts");
}
HandleDatabase hdb = null;
SqlTransaction transaction = null;
int rslt = 0;
try {
List <SqlParameter> paramList = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@operacion", Value = op, SqlDbType = SqlDbType.Int
},
new SqlParameter()
{
ParameterName = "@id", Value = info.idTeatro, SqlDbType = SqlDbType.Int
},
new SqlParameter()
{
ParameterName = "@idCine", Value = info.idCine, SqlDbType = SqlDbType.Int
},
new SqlParameter()
{
ParameterName = "@nombreTeatro", Value = info.nombreTeatro.ToString(), SqlDbType = SqlDbType.VarChar
},
new SqlParameter()
{
ParameterName = "@telefono1Teatro", Value = info.telefono1Teatro.ToString(), SqlDbType = SqlDbType.VarChar
},
new SqlParameter()
{
ParameterName = "@telefono2Teatro", Value = info.telefono2Teatro.ToString(), SqlDbType = SqlDbType.VarChar
},
new SqlParameter()
{
ParameterName = "@telefono3Teatro", Value = info.telefono3Teatro.ToString(), SqlDbType = SqlDbType.VarChar
},
new SqlParameter()
{
ParameterName = "@idMunicipioTeatro", Value = info.idMunicipioTeatro, SqlDbType = SqlDbType.Int
},
new SqlParameter()
{
ParameterName = "@idDepartamentoTeatro", Value = info.idDepeartamentoTeatro, SqlDbType = SqlDbType.Int
},
new SqlParameter()
{
ParameterName = "@direccionTeatro", Value = info.direccionTeatro, SqlDbType = SqlDbType.VarChar
}
};
String sql = "sp_crearActualizarTeatro @operacion, @id, @idCine, @nombreTeatro, @telefono1Teatro, @telefono2Teatro, @telefono3Teatro, @idMunicipioTeatro, @idDepartamentoTeatro, @direccionTeatro";
var i = 1;
if (log.IsDebugEnabled)
{
log.Debug("SQL=[" + sql + "]");
paramList.ForEach(p => {
var paramValues = "ParameterName=[" + p.ParameterName + "], Value=[" + p.Value + "], SqlDbType=[" + p.SqlDbType + "]";
log.Debug("Parameter " + i++ + " val=[" + paramValues + "]");
});
}
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
transaction = hdb.BeginTransaction("crearCine");
rslt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sql, paramList.ToArray());
} catch (Exception ex) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Returns -1");
}
rslt = -1;
} finally {
try {
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
} catch (Exception e) {
if (log.IsFatalEnabled)
{
log.Fatal("Exception occurred " + e.Message);
log.Fatal("Exception trace=[" + e.StackTrace + "]");
log.Fatal("Returns -1");
}
rslt = -1;
}
}
if (log.IsDebugEnabled)
{
log.Debug("rslt=[" + rslt + "]");
log.Debug("crearTeatro Ends");
}
return(rslt);
}
public List <PeliculaFullInfoDto> obtenerProgramacionPelicula(int idPelicula, int idTeatro)
{
HandleDatabase hdb = null;
SqlDataReader rdr = null;
SqlTransaction transaction = null;
try {
List <PeliculaFullInfoDto> lstResultado = new List <PeliculaFullInfoDto>();
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter paramPelicula = new SqlParameter();
SqlParameter paramTeatro = new SqlParameter();
paramPelicula.ParameterName = "@idPelicula";
paramTeatro.ParameterName = "@idTeatro";
paramPelicula.Value = idPelicula;
paramPelicula.SqlDbType = SqlDbType.VarChar;
paramTeatro.Value = idTeatro;
paramTeatro.SqlDbType = SqlDbType.Int;
String sql = "sp_obtenerProgramacionPelicula @idPelicula,@idTeatro";
transaction = hdb.BeginTransaction("obtenerProgramacionPelicula");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramPelicula, paramTeatro);
while (rdr.Read())
{
PeliculaFullInfoDto peliculaObj = new PeliculaFullInfoDto();
int idFormato = Convert.ToInt32(rdr["idFormato"]);
string nombreFormato = rdr["nombreFormato"].ToString();
int Pelicula = Convert.ToInt32(rdr["idPelicula"]);
int idHorarioPelicula = Convert.ToInt32(rdr["idHorarioPelicula"]);
string annoHorarioPelicula = rdr["annoHorarioPelicula"].ToString();
string mesHorarioPelicula = rdr["mesHorarioPelicula"].ToString();
string diaHorarioPelicula = rdr["diaHorarioPelicula"].ToString();
string nombreDiaSemanaHorarioPelicula = rdr["nombreDiaSemanaHorarioPelicula"].ToString();
int Teatro = Convert.ToInt32(rdr["idTeatro"]);
string nombreTeatro = rdr["nombreTeatro"].ToString();
int frecuencia = Convert.ToInt32(rdr["frecuencia"]);
string horaPelicula = rdr["horaPelicula"].ToString();
string minutoPelicula = rdr["minutoPelicula"].ToString();
int sala = Convert.ToInt32(rdr["sala"]);
peliculaObj.idFormato = idFormato;
peliculaObj.nombreFormato = nombreFormato;
peliculaObj.idPelicula = Pelicula;
peliculaObj.idHorarioPelicula = idHorarioPelicula;
peliculaObj.annoHorarioPelicula = annoHorarioPelicula;
peliculaObj.mesHorarioPelicula = mesHorarioPelicula;
peliculaObj.diaHorarioPelicula = diaHorarioPelicula;
peliculaObj.nombreDiaSemanaHorarioPelicula = nombreDiaSemanaHorarioPelicula;
peliculaObj.idTeatro = Teatro;
peliculaObj.nombreTeatro = nombreTeatro;
peliculaObj.frecuencia = frecuencia;
peliculaObj.horaPelicula = horaPelicula;
peliculaObj.minutoPelicula = minutoPelicula;
peliculaObj.sala = sala;
lstResultado.Add(peliculaObj);
}
return(lstResultado);
} catch (Exception) {
return(null);
} finally {
if (rdr != null)
{
rdr.Close();
}
if (transaction != null)
{
transaction.Commit();
}
if (hdb != null)
{
hdb.Close();
}
}
}
