/// <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;
}
/// <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>
/// Gets a list of active movies to work.
/// </summary>
/// <returns>A list of DetallePeliculaDto objects</returns>
public List<DetallePeliculaDto> getPeliculasActivas() {
if (log.IsDebugEnabled) {
log.Debug("getPeliculasActivas Starts");
}
SqlDataReader rdr = null;
SqlTransaction transaction = null;
HandleDatabase hdb = null;
List<DetallePeliculaDto> lstPelicula = new List<DetallePeliculaDto>();
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
String sql = "sp_obtenerPeliculasActivas";
if (log.IsDebugEnabled) {
log.Debug("Sql=[" + sql + "]");
}
transaction = hdb.BeginTransaction("getPeliculasActivas");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql);
while (rdr.Read()) {
lstPelicula.Add(new DetallePeliculaDto() {
idPelicula = Convert.ToInt32(rdr["idPelicula"]),
idDetallePelicula = Convert.ToInt32(rdr["idDetallePelicula"]),
nombrePelicula = rdr["nombrePelicula"].ToString(),
idUsuarioCreador = Convert.ToInt32(rdr["idUsuarioCreador"].ToString()),
fechaCreacionPelicula = Convert.ToDateTime(rdr["fechaCreacionPelicula"].ToString()),
idGeneroPelicula = Convert.ToInt32(rdr["idGeneroPelicula"].ToString()),
sinopsis = rdr["sinopsis"].ToString(),
urlArticuloEc = rdr["urlArticuloEC"].ToString(),
enCartelera = (rdr["activo"].ToString() == "S" ? "Si" : "No")
});
}
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Return sets to emtpy list");
}
lstPelicula = new List<DetallePeliculaDto>();
} 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 sets to emtpy list");
}
lstPelicula = new List<DetallePeliculaDto>();
}
}
if (log.IsDebugEnabled) {
log.Debug("getPeliculasActivas Ends");
}
return lstPelicula;
}
/// <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 createEntidad(EntidadDto info, int op) {
if (log.IsDebugEnabled) {
log.Debug("createEntidad 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.idEntidad, SqlDbType = SqlDbType.Int },
new SqlParameter() { ParameterName = "@codigo", Value = info.codEntidad.ToString(), SqlDbType = SqlDbType.VarChar },
new SqlParameter() { ParameterName = "@nombre", Value = info.nombreEntidad, SqlDbType = SqlDbType.VarChar },
new SqlParameter() { ParameterName = "@valor", Value = info.valorEntidad, SqlDbType = SqlDbType.VarChar },
new SqlParameter() { ParameterName = "@descripcion", Value = info.descripcionEntidad, SqlDbType = SqlDbType.VarChar }
};
String sql = "sp_crearActualizarEntidad @operacion, @id, @codigo, @nombre, @valor, @descripcion";
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("crearEntidad");
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) {
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("createEntidad 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>
/// 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) {
if (log.IsDebugEnabled) {
log.Debug("getMovieShowHoursFor Starts");
}
string sql = "select * from tbl_hora where idHorarioPelicula = @id order by horaPelicula, minutoPelicula ";
if (log.IsDebugEnabled) {
log.Debug("SQL=[" + sql + "]");
}
List<MovieShowHour> movieHoursList = 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()) {
movieHoursList.Add(new MovieShowHour() {
id = Convert.ToInt32(rdr["idHora"]),
timeHour = Convert.ToInt32(rdr["horaPelicula"]),
timeMinute = Convert.ToInt32(rdr["minutoPelicula"])
});
}
// Let's fill field timeFull
movieHoursList.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;
});
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Empty list returned");
}
movieHoursList = new List<MovieShowHour>();
} 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");
}
movieHoursList = new List<MovieShowHour>();
}
}
if (log.IsDebugEnabled) {
log.Debug("getMovieShowHoursFor Ends");
}
return movieHoursList;
}
/// <summary>
/// A core method in application. It has two tasks to make. First to update the premiere status, and second to
/// retrieve all records scheduled for this week for billboard.
/// </summary>
/// <returns>A list of records for this week billboard.</returns>
public List<MovieFullInfo> updateBillboardAndGetMovieFullInfo() {
if (log.IsDebugEnabled) {
log.Debug("updateBillboardAndGetMovieFullInfo Starts");
}
string sql = "";
List<MovieFullInfo> movieFullList = new List<MovieFullInfo>();
sql = "sp_actualizarcartelera";
if (log.IsDebugEnabled) {
log.Debug("SQL=[" + sql + "]");
}
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
transaction = hdb.BeginTransaction("updBillboardAndGetMovieFullinfo");
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(),
premiere = rdr["premiere"].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"]),
createDate = Convert.ToDateTime(rdr["fechaCreacionPelicula"]),
dt = new DateTime(Convert.ToInt32(rdr["annoHorarioPelicula"]), Convert.ToInt32(rdr["mesHorarioPelicula"]), Convert.ToInt32(rdr["diaHorarioPelicula"]))
});
}
// Before returning records it needs to normalize field MovieFullInfo.url
movieFullList.ForEach(m => {
if (!m.url.Contains(Settings.UrlMilleniumPrefix)) {
m.url = Settings.UrlMilleniumPrefix + m.url;
}
});
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Empty list returned");
}
movieFullList = new List<MovieFullInfo>();
} 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");
}
movieFullList = new List<MovieFullInfo>();
}
}
if (log.IsDebugEnabled) {
log.Debug("updateBillboardAndGetMovieFullInfo Ends");
}
return movieFullList;
}
/// <summary>
/// Create or updates a schedule programming.
/// </summary>
/// <param name="datosProgramacion">What to update/create</param>
/// <returns>0: Success, -1: Failure</returns>
public int createUpdateProgramacionPelicula(ProgramacionPeliculaDto datosProgramacion) {
if (log.IsDebugEnabled) {
log.Debug("createUpdateProgramacionPelicula Starts");
}
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
int rslt = 0;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
List<SqlParameter> paramList = new List<SqlParameter>() {
new SqlParameter() {ParameterName = "@IDHORARIOPELICULA", Value = datosProgramacion.idHorarioPelicula, SqlDbType = SqlDbType.Int},
new SqlParameter() {ParameterName = "@IDFORMATO", Value = datosProgramacion.idFormato, SqlDbType = SqlDbType.Int},
new SqlParameter() {ParameterName = "@IDPELICULA", Value = datosProgramacion.idPelicula, SqlDbType = SqlDbType.Int},
new SqlParameter() {ParameterName = "@IDTEATRO", Value = datosProgramacion.idTeatro, SqlDbType = SqlDbType.Int},
new SqlParameter() {ParameterName = "@ANNOHORARIO", Value = datosProgramacion.annoHorarioPelicula, SqlDbType = SqlDbType.Int},
new SqlParameter() {ParameterName = "@MESHORARIO", Value = datosProgramacion.mesHorarioPelicula, SqlDbType = SqlDbType.Int},
new SqlParameter() {ParameterName = "@DIAHORARIO", Value = datosProgramacion.diaHorarioPelicula, SqlDbType = SqlDbType.Int},
new SqlParameter() {ParameterName = "@HORAMINUTO", Value = datosProgramacion.horaMinutoPelicula, SqlDbType = SqlDbType.VarChar},
new SqlParameter() {ParameterName = "@SALA", Value = datosProgramacion.sala, SqlDbType = SqlDbType.VarChar},
new SqlParameter() {ParameterName = "@NOMBREDIA", Value = datosProgramacion.nombreDiaSemanaHorarioPelicula, SqlDbType = SqlDbType.VarChar},
new SqlParameter() {ParameterName = "@FRECUENCIA", Value = datosProgramacion.frecuencia, SqlDbType = SqlDbType.Int}
};
String sql = "SP_CREARACTUALIZARPROGRAMACIONPELICULA @IDHORARIOPELICULA,@IDFORMATO,@IDPELICULA,@IDTEATRO,@ANNOHORARIO,@MESHORARIO, @DIAHORARIO,@HORAMINUTO,@SALA, @NOMBREDIA,@FRECUENCIA";
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 + "]");
});
}
transaction = hdb.BeginTransaction("crUpdProgramacionPelicula");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramList.ToArray());
rslt = 1;
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Return sets to -1");
}
rslt = -1;
} 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 sets to -1");
}
rslt = -1;
}
}
if (log.IsDebugEnabled) {
log.Debug("rslt=[" + rslt + "]");
log.Debug("createUpdateProgramacionPelicula Ends");
}
return rslt;
}
/// <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;
}
/// <summary>
/// Creates a Movie in DB.
/// </summary>
/// <param name="teatro">The theater to reference</param>
/// <param name="peliculas">The list of ids for movies referenced in this theater.</param>
/// <returns>0 if successful, -1 if error</returns>
public int createPeliculaTeatro(int teatro, string peliculas) {
if (log.IsDebugEnabled) {
log.Debug("createPeliculaTeatro Starts");
}
SqlTransaction transaction = null;
SqlDataReader rdr = null;
HandleDatabase hdb = null;
int rslt = 0;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
List<SqlParameter> paramList = new List<SqlParameter>() {
new SqlParameter() {ParameterName = "@peliculas", Value = peliculas, SqlDbType = SqlDbType.VarChar},
new SqlParameter() {ParameterName = "@teatro", Value = teatro, SqlDbType = SqlDbType.Int}
};
String sql = "sp_crearPeliculasPorTeatro @teatro,@peliculas";
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 + "]");
});
}
transaction = hdb.BeginTransaction("crearPeliculaPorTeatro");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramList.ToArray());
rslt = 1;
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Result is set to -1");
}
rslt = -1;
} 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 is set to -1");
}
rslt = -1;
}
}
if (log.IsDebugEnabled) {
log.Debug("r=[" + rslt + "]");
log.Debug("createPeliculaTeatro Ends");
}
return rslt;
}
/// <summary>
/// Retrieves which movies are in which thater.
/// </summary>
/// <param name="teatro">The theater id to retrieve</param>
/// <returns>A list of PeliculaDto objects</returns>
public List<PeliculaDto> getPeliculasPorTeatro(int teatro) {
if (log.IsDebugEnabled) {
log.Debug("getPeliculasPorTeatro Starts");
}
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
List<PeliculaDto> lstPeliculasCine = new List<PeliculaDto>();
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
String sql = "sp_obtenerPeliculasPorTeatro @TEATRO";
SqlParameter param = new SqlParameter() { ParameterName = "@TEATRO", Value = teatro, SqlDbType = SqlDbType.Int };
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("PeliculasPorTeatro");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
while (rdr.Read()) {
lstPeliculasCine.Add(new PeliculaDto() {
idPelicula = Convert.ToInt32(rdr["idPelicula"]),
idTeatro = Convert.ToInt32(rdr["idTeatro"]),
nombrePelicula = rdr["nombrePelicula"].ToString(),
nombreTeatro = rdr["nombreTeatro"].ToString()
});
}
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("List is empty");
}
lstPeliculasCine = new List<PeliculaDto>();
} 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 is empty");
}
lstPeliculasCine = new List<PeliculaDto>();
}
}
if (log.IsDebugEnabled) {
log.Debug("getPeliculasPorTeatro Ends");
}
return lstPeliculasCine;
}
/// <summary>
/// Retrieves a record from DB about a PeliculaDto object.
/// </summary>
/// <param name="idPelicula">Parameter to retrieve.</param>
/// <returns>One record with data or NULL if none found</returns>
public DetallePeliculaDto getPelicula(int idPelicula) {
if (log.IsDebugEnabled) {
log.Debug("getPelicula Starts");
}
SqlTransaction transaction = null;
SqlDataReader rdr = null;
HandleDatabase hdb = null;
DetallePeliculaDto r = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
String sql = "sp_obtenerPelicula @IDPELICULA";
SqlParameter param = new SqlParameter() { ParameterName = "@IDPELICULA", Value = idPelicula, SqlDbType = SqlDbType.Int };
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("getPelicula");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param);
if (rdr.HasRows) {
rdr.Read();
r = new DetallePeliculaDto() {
idPelicula = Convert.ToInt32(rdr["idPelicula"]),
idDetallePelicula = Convert.ToInt32(rdr["idDetallePelicula"]),
nombrePelicula = rdr["nombrePelicula"].ToString(),
idUsuarioCreador = Convert.ToInt32(rdr["idUsuarioCreador"].ToString()),
fechaCreacionPelicula = Convert.ToDateTime(rdr["fechaCreacionPelicula"].ToString()),
idGeneroPelicula = Convert.ToInt32(rdr["idGeneroPelicula"].ToString()),
imagenCartelera = rdr["imagenCartelera"].ToString(),
sinopsis = rdr["sinopsis"].ToString(),
urlArticuloEc = rdr["urlArticuloEC"].ToString(),
enCartelera = rdr["activo"].ToString(),
premiere = rdr["premiere"].ToString()
};
}
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Record return is 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("Record return is null");
}
r = null;
}
}
if (log.IsDebugEnabled) {
if (r == null) {
log.Debug("Result is NULL");
}
else {
log.Debug("Result sets to [" + r.ToString() + "]");
}
log.Debug("getPelicula 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>
/// 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 getValorEntidad(int id) {
if (log.IsDebugEnabled) {
log.Debug("getValorEntidad Starts");
}
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
EntidadDto r = null;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
SqlParameter param = new SqlParameter() { ParameterName = "@id", Value = id, SqlDbType = SqlDbType.Int };
String sql = "sp_obtenerValorEntidad @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("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();
}
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Returns 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 + "]");
}
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="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="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>
/// Given a movie Id and a theater Id, retrieves the schedule for those params.
/// </summary>
/// <param name="idPelicula">Which movie to retrieve</param>
/// <param name="idTeatro">Which theater to retrieve</param>
/// <returns>A list of PeliculaFullInfoDto objecs </returns>
public List<PeliculaFullInfoDto> getProgramacionPelicula(int idPelicula, int idTeatro) {
if (log.IsDebugEnabled) {
log.Debug("getProgramacionPelicula Starts");
}
HandleDatabase hdb = null;
SqlDataReader rdr = null;
SqlTransaction transaction = null;
List<PeliculaFullInfoDto> lstResultado = new List<PeliculaFullInfoDto>();
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
List<SqlParameter> paramList = new List<SqlParameter>() {
new SqlParameter() { ParameterName = "@idPelicula", Value = idPelicula, SqlDbType = SqlDbType.Int},
new SqlParameter() { ParameterName = "@idTeatro", Value = idTeatro,SqlDbType = SqlDbType.Int}
};
String sql = "sp_obtenerProgramacionPelicula @idPelicula,@idTeatro";
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 + "]");
});
}
transaction = hdb.BeginTransaction("obtenerProgramacionPelicula");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramList.ToArray());
while (rdr.Read()) {
lstResultado.Add(new PeliculaFullInfoDto() {
idFormato = Convert.ToInt32(rdr["idFormato"]),
nombreFormato = rdr["nombreFormato"].ToString(),
idPelicula = Convert.ToInt32(rdr["idPelicula"]),
idHorarioPelicula = Convert.ToInt32(rdr["idHorarioPelicula"]),
annoHorarioPelicula = rdr["annoHorarioPelicula"].ToString(),
mesHorarioPelicula = rdr["mesHorarioPelicula"].ToString(),
diaHorarioPelicula = rdr["diaHorarioPelicula"].ToString(),
nombreDiaSemanaHorarioPelicula = rdr["nombreDiaSemanaHorarioPelicula"].ToString(),
idTeatro = Convert.ToInt32(rdr["idTeatro"]),
nombreTeatro = rdr["nombreTeatro"].ToString(),
frecuencia = Convert.ToInt32(rdr["frecuencia"]),
horaPelicula = rdr["horaPelicula"].ToString(),
minutoPelicula = rdr["minutoPelicula"].ToString(),
sala = Convert.ToInt32(rdr["sala"]),
});
}
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Empty list returned");
}
lstResultado = new List<PeliculaFullInfoDto>();
} 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");
}
lstResultado = new List<PeliculaFullInfoDto>();
}
}
if (log.IsDebugEnabled) {
log.Debug("getProgramacionPelicula Ends");
}
return lstResultado;
}
/// <summary>
/// Creates a new record in DB.
/// </summary>
/// <param name="pelicula">Movie details</param>
/// <param name="operacion">Operation to accomplish: 1:create, 2:update</param>
/// <returns></returns>
public int createPelicula(DetallePeliculaDto pelicula, int operacion) {
if (log.IsDebugEnabled) {
log.Debug("createPelicula Starts");
}
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
int rslt = 0;
try {
hdb = new HandleDatabase(Settings.Connection);
hdb.Open();
List<SqlParameter> paramList = new List<SqlParameter>() {
new SqlParameter() {ParameterName = "@OPERACION", Value = operacion, SqlDbType = SqlDbType.Int},
new SqlParameter() { ParameterName = "@PELICULA", Value = pelicula.idPelicula, SqlDbType = SqlDbType.Int},
new SqlParameter() { ParameterName = "@NOMBRE", Value = pelicula.nombrePelicula, SqlDbType = SqlDbType.VarChar},
new SqlParameter() { ParameterName = "@USUARIO", Value = pelicula.idUsuarioCreador, SqlDbType = SqlDbType.Int},
new SqlParameter() { ParameterName = "@GENERO", Value = pelicula.idGeneroPelicula, SqlDbType = SqlDbType.Int},
new SqlParameter() { ParameterName = "@SINOPSIS", Value = pelicula.sinopsis, SqlDbType = SqlDbType.VarChar},
new SqlParameter() { ParameterName = "@IMAGEN", Value = pelicula.imagenCartelera, SqlDbType = SqlDbType.VarChar},
new SqlParameter() { ParameterName = "@URL", Value = pelicula.urlArticuloEc, SqlDbType = SqlDbType.VarChar},
new SqlParameter() { ParameterName = "@ACTIVO", Value = pelicula.enCartelera, SqlDbType = SqlDbType.VarChar}
};
String sql = "sp_crearActualizarPelicula @OPERACION,@PELICULA,@NOMBRE,@USUARIO,@GENERO,@SINOPSIS,@IMAGEN,@URL,@ACTIVO";
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 + "]");
});
}
transaction = hdb.BeginTransaction("crearPelicula");
rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramList.ToArray());
rslt = 1;
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Result sets to -1");
}
rslt = -1;
} 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 -1");
}
rslt = -1;
}
}
if (log.IsDebugEnabled) {
log.Debug("rslt=[" + rslt + "]");
log.Debug("createPelicula Ends");
}
return rslt;
}
/// <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> getValoresEntidad(string nombreEntidad) {
if (log.IsDebugEnabled) {
log.Debug("getValoresEntidad Starts");
}
HandleDatabase hdb = null;
SqlTransaction transaction = null;
SqlDataReader rdr = null;
List<EntidadDto> listaResultado = new List<EntidadDto>();
try {
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";
if (log.IsDebugEnabled) {
log.Debug("sql=[" + sql + "]");
}
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);
}
} catch (Exception ex) {
if (log.IsFatalEnabled) {
log.Fatal("Exception occurred " + ex.Message);
log.Fatal("Exception trace=[" + ex.StackTrace + "]");
log.Fatal("Empty list returned");
}
listaResultado = new List<EntidadDto>();
} 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");
}
listaResultado = new List<EntidadDto>();
}
}
if (log.IsDebugEnabled) {
log.Debug("getValoresEntidad Ends");
}
return listaResultado;
}
