/// <summary>
/// Ingresa un nuevo registro con la información del reporte que esta siendo levantado
/// </summary>
/// <param name="reporte"></param>
public void SetNewReporte(LevantaReporte reporte)
{
SqlConnection connection = Conexion.GetConexion();
try
{
connection.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO ReportesComputo(FechaReporte,FechaReporteInt,IdEquipo,Expediente,Reporto,Problema,NumReporte)" +
" VALUES(@FechaReporte,@FechaReporteInt,@IdEquipo,@Expediente,@Reporto,@Problema,@NumReporte)", connection);
cmd.Parameters.AddWithValue("@FechaReporte", reporte.FechaReporte);
cmd.Parameters.AddWithValue("@FechaReporteInt", DateTimeUtilities.DateToInt(reporte.FechaReporte));
cmd.Parameters.AddWithValue("@IdEquipo", reporte.IdEquipo);
cmd.Parameters.AddWithValue("@Expediente", reporte.Expediente);
cmd.Parameters.AddWithValue("@Reporto", reporte.Reporto);
cmd.Parameters.AddWithValue("@Problema", reporte.Problema);
cmd.Parameters.AddWithValue("@NumReporte", reporte.NumReporte);
cmd.ExecuteNonQuery();
LevantaReporteSingleton.Reportes.Add(reporte);
}
catch (SqlException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,LevantaReporteModel", "Inventario");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,LevantaReporteModel", "Inventario");
}
finally
{
connection.Close();
}
}
/// <summary>
/// Actualiza los datos de cierre de reporte
/// </summary>
/// <param name="reporte"></param>
public void UpdateReporte(LevantaReporte reporte)
{
SqlConnection connection = Conexion.GetConexion();
try
{
connection.Open();
SqlCommand cmd = new SqlCommand("UPDATE ReportesComputo SET Problema = @Problema, Atendio = @Atendio," +
"FechaCierre = @FechaCierre, FechaCierreInt = @FechaCierreInt, Observaciones = @Observaciones " +
" WHERE IdReporte = @IdReporte", connection);
cmd.Parameters.AddWithValue("@Problema", reporte.Problema);
cmd.Parameters.AddWithValue("@Atendio", reporte.Atendio);
cmd.Parameters.AddWithValue("@FechaCierre", reporte.FechaCierre);
cmd.Parameters.AddWithValue("@FechaCierreInt", DateTimeUtilities.DateToInt(reporte.FechaCierre));
cmd.Parameters.AddWithValue("@Observaciones", reporte.Observaciones);
cmd.Parameters.AddWithValue("@IdReporte", reporte.IdReporte);
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,LevantaReporteModel", "Inventario");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,LevantaReporteModel", "Inventario");
}
finally
{
connection.Close();
}
}
public bool InsertaEncargado(Encargado encargado)
{
SqlConnection connection = new SqlConnection(connectionString);
bool insertCompleted = false;
encargado.IdTitular = DataBaseUtilities.GetNextIdForUse("C_Encargado", "IdEncargado", connection);
if (encargado.IdTitular == 0)
{
encargado.IdTitular = 1;
}
try
{
connection.Open();
string sqlQuery = "INSERT INTO C_Encargado(IdEncargado, Nombre,Apellidos,IdTitulo,NombMay,IdOrganismo,IdUsr,Fecha,Obs,Genero,IdTpoOrg,IdFuncion)" +
"VALUES (@IdEncargado,@Nombre,@Apellidos,@IdTitulo,@NombMay,@IdOrganismo,@IdUsr,@Fecha,@Obs,@Genero,@IdTpoOrg,@IdFuncion)";
SqlCommand cmd = new SqlCommand(sqlQuery, connection);
cmd.Parameters.AddWithValue("@IdEncargado", encargado.IdTitular);
cmd.Parameters.AddWithValue("@Nombre", encargado.Nombre);
cmd.Parameters.AddWithValue("@Apellidos", encargado.Apellidos);
cmd.Parameters.AddWithValue("@IdTitulo", encargado.IdTitulo);
cmd.Parameters.AddWithValue("@NombMay", encargado.NombreStr);
cmd.Parameters.AddWithValue("@IdOrganismo", encargado.IdOrganismo);
cmd.Parameters.AddWithValue("@IdUsr", AccesoUsuario.Llave);
cmd.Parameters.AddWithValue("@Fecha", DateTimeUtilities.DateToInt(DateTime.Now));
cmd.Parameters.AddWithValue("@Obs", encargado.Observaciones);
cmd.Parameters.AddWithValue("@Genero", encargado.Genero);
cmd.Parameters.AddWithValue("@IdTpoOrg", encargado.IdTpoOrg);
cmd.Parameters.AddWithValue("@IdFuncion", encargado.IdFuncion);
cmd.ExecuteNonQuery();
cmd.Dispose();
insertCompleted = true;
}
catch (SqlException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,EncargadosModel", "DirectorioSjf2017");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,EncargadosModel", "DirectorioSjf2017");
}
finally
{
connection.Close();
}
return(insertCompleted);
}
public bool SetCheckOut(Usuarios usuario)
{
bool updateComplete = false;
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
DataSet dataSet = new DataSet();
DataRow dr;
try
{
string sqlCadena = "SELECT * FROM Asistencia WHERE FechaInt = @Fecha AND IdPersonal = @IdPersonal";
dataAdapter.SelectCommand = new OleDbCommand(sqlCadena, connection);
dataAdapter.SelectCommand.Parameters.AddWithValue("@Fecha", DateTimeUtilities.DateToInt(DateTime.Now));
dataAdapter.SelectCommand.Parameters.AddWithValue("@IdPersonal", usuario.IdUsuario);
dataAdapter.Fill(dataSet, "Asistencia");
dr = dataSet.Tables["Asistencia"].Rows[0];
dr.BeginEdit();
dr["IdPersonal"] = usuario.IdUsuario;
dr.EndEdit();
dataAdapter.UpdateCommand = connection.CreateCommand();
dataAdapter.UpdateCommand.CommandText =
"UPDATE Asistencia SET HoraSalida = TIME() WHERE FechaInt = @Fecha AND IdPersonal = @IdPersonal";
dataAdapter.UpdateCommand.Parameters.Add("@Fecha", OleDbType.Numeric, 0, "FechaInt");
dataAdapter.UpdateCommand.Parameters.Add("@IdPersonal", OleDbType.Numeric, 0, "IdPersonal");
dataAdapter.Update(dataSet, "Asistencia");
dataSet.Dispose();
dataAdapter.Dispose();
connection.Close();
updateComplete = true;
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,AsistenciaModel", "AsistenciaLibreria");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,AsistenciaModel", "AsistenciaLibreria");
}
finally
{
connection.Close();
}
return(updateComplete);
}
public List <GeneroPorAnio> GetFechasToConvert()
{
List <GeneroPorAnio> fechas = new List <GeneroPorAnio>();
string sqlCadena = "select idSocio, fcinscripcion, genero from SociosSiger";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = null;
SqlDataReader reader = null;
int queRegistro = 0;
string folio = String.Empty;
try
{
connection.Open();
cmd = new SqlCommand(sqlCadena, connection);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
GeneroPorAnio year = new GeneroPorAnio();
year.IdSocio = Convert.ToInt32(reader["IdSocio"]);
year.FechaSocio = DateTimeUtilities.GetDateFromReader(reader, "FcInscripcion");
year.Genero = Convert.ToInt32(reader["Genero"]);
year.FechaInt = Convert.ToInt32(DateTimeUtilities.DateToInt(year.FechaSocio));
fechas.Add(year);
}
}
cmd.Dispose();
reader.Close();
}
catch (SqlException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + queRegistro + " Exception,PadronModel" + folio, "Padron");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + queRegistro + " Exception,PadronModel" + folio, "Padron");
}
finally
{
connection.Close();
}
return(fechas);
}
/// <summary>
/// Actualiza los datos del encargado seleccionado
/// </summary>
/// <param name="encargado"></param>
/// <returns></returns>
public bool UpdateEncargado(Encargado encargado)
{
SqlConnection connection = new SqlConnection(connectionString);
bool updateCompleted = false;
try
{
connection.Open();
string sqlQuery = "UPDATE C_Encargado SET Nombre = @Nombre,Apellidos = @Apellidos," +
"NombMay = @NombMay,Obs = @Obs, IdTitulo = @IdTitulo, IdOrganismo = @IdOrganismo, IdTpoOrg = @IdTpoOrg, " +
" IdUsr = @IdUsr, Fecha = @Fecha, Genero = @Genero, IdFuncion = @IdFuncion WHERE IdEncargado = @IdEncargado";
SqlCommand cmd = new SqlCommand(sqlQuery, connection);
cmd.Parameters.AddWithValue("@Nombre", encargado.Nombre);
cmd.Parameters.AddWithValue("@Apellidos", encargado.Apellidos);
cmd.Parameters.AddWithValue("@NombMay", encargado.NombreStr);
cmd.Parameters.AddWithValue("@Obs", encargado.Observaciones);
cmd.Parameters.AddWithValue("@IdTitulo", encargado.IdTitulo);
cmd.Parameters.AddWithValue("@IdOrganismo", encargado.IdOrganismo);
cmd.Parameters.AddWithValue("@IdTpoOrg", encargado.IdTpoOrg);
cmd.Parameters.AddWithValue("@IdUsr", AccesoUsuario.Llave);
cmd.Parameters.AddWithValue("@Fecha", DateTimeUtilities.DateToInt(DateTime.Now));
cmd.Parameters.AddWithValue("@Genero", encargado.Genero);
cmd.Parameters.AddWithValue("@IdFuncion", encargado.IdFuncion);
cmd.Parameters.AddWithValue("@IdEncargado", encargado.IdTitular);
cmd.ExecuteNonQuery();
cmd.Dispose();
updateCompleted = true;
}
catch (SqlException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,EncargadosModel", "DirectorioSjf2017");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,EncargadosModel", "DirectorioSjf2017");
}
finally
{
connection.Close();
}
return(updateCompleted);
}
/// <summary>
/// Verifica si el usuario registro su entrada previamente
/// </summary>
/// <param name="usuario"></param>
/// <returns></returns>
public int DoUserCheckInToday(Usuarios usuario)
{
int doCheckIn = -3;
OleDbCommand cmd;
OleDbDataReader reader;
OleDbConnection connection = new OleDbConnection(connectionString);
try
{
connection.Open();
string sSql = "SELECT * FROM Asistencia WHERE FechaInt = @Fecha AND IdPersonal = @Id";
cmd = new OleDbCommand(sSql, connection);
cmd.Parameters.AddWithValue("@Fecha", DateTimeUtilities.DateToInt(DateTime.Now));
cmd.Parameters.AddWithValue("@Id", usuario.IdUsuario);
reader = cmd.ExecuteReader();
while (reader.Read())
{
doCheckIn = Convert.ToInt32(reader["IdAsistencia"]);
}
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,AsistenciaModel", "AsistenciaLibreria");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,AsistenciaModel", "AsistenciaLibreria");
}
finally
{
connection.Close();
}
return(doCheckIn);
}
public bool SetCheckIn(Usuarios usuario)
{
bool checkInComplete = false;
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter dataAdapter;
DataSet dataSet = new DataSet();
DataRow dr;
try
{
int id = DataBaseUtilities.GetNextIdForUse("Asistencia", "IdAsistencia", connection);
if (id != 0)
{
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM Asistencia WHERE IdAsistencia = 0", connection);
dataAdapter.Fill(dataSet, "Asistencia");
dr = dataSet.Tables["Asistencia"].NewRow();
dr["IdAsistencia"] = id;
dr["IdPersonal"] = usuario.IdUsuario;
dr["IdLibreria"] = usuario.IdLibreria;
dr["Fecha"] = DateTime.Now;
dr["FechaInt"] = DateTimeUtilities.DateToInt(DateTime.Now);
dataSet.Tables["Asistencia"].Rows.Add(dr);
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText = "INSERT INTO Asistencia(IdAsistencia,IdPersonal,IdLibreria,Fecha,HoraEntrada,FechaInt) " +
"VALUES (@IdAsistencia,@IdPersonal,@IdLibreria,@Fecha,TIME(),@FechaInt)";
dataAdapter.InsertCommand.Parameters.Add("@IdAsistencia", OleDbType.Numeric, 0, "IdAsistencia");
dataAdapter.InsertCommand.Parameters.Add("@IdPersonal", OleDbType.Numeric, 0, "IdPersonal");
dataAdapter.InsertCommand.Parameters.Add("@IdLibreria", OleDbType.Numeric, 0, "IdLibreria");
dataAdapter.InsertCommand.Parameters.Add("@Fecha", OleDbType.Date, 0, "Fecha");
dataAdapter.InsertCommand.Parameters.Add("@FechaInt", OleDbType.Numeric, 0, "FechaInt");
dataAdapter.Update(dataSet, "Asistencia");
dataSet.Dispose();
dataAdapter.Dispose();
connection.Close();
checkInComplete = true;
}
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,AsistenciaModel", "AsistenciaLibreria");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,AsistenciaModel", "AsistenciaLibreria");
}
finally
{
connection.Close();
}
return(checkInComplete);
}
public void SetNewTerminoGenericos(Genericos generico)
{
OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["Diccionario"].ToString());
OleDbDataAdapter dataAdapter;
DataSet dataSet = new DataSet();
DataRow dr;
try
{
generico.IdGenerico = DataBaseUtilities.GetNextIdForUse("Genericos", "IdConcepto", connection);
string sqlCadena = "SELECT * FROM Genericos WHERE IdConcepto = 0";
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(sqlCadena, connection);
dataAdapter.Fill(dataSet, "Genericos");
dr = dataSet.Tables["Genericos"].NewRow();
dr["IdConcepto"] = generico.IdGenerico;
dr["Concepto"] = StringUtilities.UppercaseFirst(generico.Termino);
dr["ConceptoStr"] = ScjnUtilities.StringUtilities.PrepareToAlphabeticalOrder(generico.Termino);
dr["FechaAlta"] = DateTime.Now;
dr["FechaAltaInt"] = DateTimeUtilities.DateToInt(DateTime.Now);
dataSet.Tables["Genericos"].Rows.Add(dr);
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText = "INSERT INTO Genericos VALUES (@IdConcepto,@Concepto,@ConceptoStr,@FechaAlta,@FechaAltaInt)";
dataAdapter.InsertCommand.Parameters.Add("@IdConcepto", OleDbType.Numeric, 0, "IdConcepto");
dataAdapter.InsertCommand.Parameters.Add("@Concepto", OleDbType.VarChar, 0, "Concepto");
dataAdapter.InsertCommand.Parameters.Add("@ConceptoStr", OleDbType.VarChar, 0, "ConceptoStr");
dataAdapter.InsertCommand.Parameters.Add("@FechaAlta", OleDbType.Date, 0, "FechaAlta");
dataAdapter.InsertCommand.Parameters.Add("@FechaAltaInt", OleDbType.Numeric, 0, "FechaAltaInt");
dataAdapter.Update(dataSet, "Genericos");
dataSet.Dispose();
dataAdapter.Dispose();
if (generico.Definiciones != null)
{
foreach (Definiciones definicion in generico.Definiciones)
{
definicion.IdGenerico = generico.IdGenerico;
new DefinicionModel().SetDefiniciones(definicion);
}
}
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,SetNewTerminoGenericos", "Diccionario");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,SetNewTerminoGenericos", "Diccionario");
}
finally
{
connection.Close();
}
}
public void SetNewConcept(Conceptos concepto)
{
OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["Diccionario"].ToString());
OleDbDataAdapter dataAdapter;
DataSet dataSet = new DataSet();
DataRow dr;
try
{
concepto.IdConcepto = DataBaseUtilities.GetNextIdForUse("Conceptos", "IdConcepto", connection);
string sqlCadena = "SELECT * FROM Conceptos WHERE IdConcepto = 0";
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(sqlCadena, connection);
dataAdapter.Fill(dataSet, "Conceptos");
dr = dataSet.Tables["Conceptos"].NewRow();
dr["IdConcepto"] = concepto.IdConcepto;
dr["Concepto"] = concepto.Concepto;
dr["ConceptoStr"] = concepto.ConceptoStr;
dr["FechaAlta"] = DateTime.Now;
dr["FechaAltaInt"] = DateTimeUtilities.DateToInt(DateTime.Now);
dataSet.Tables["Conceptos"].Rows.Add(dr);
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText = "INSERT INTO Conceptos VALUES (@IdConcepto,@Concepto,@ConceptoStr,@FechaAlta,@FechaAltaInt)";
dataAdapter.InsertCommand.Parameters.Add("@IdConcepto", OleDbType.Numeric, 0, "IdConcepto");
dataAdapter.InsertCommand.Parameters.Add("@Concepto", OleDbType.VarChar, 0, "Concepto");
dataAdapter.InsertCommand.Parameters.Add("@ConceptoStr", OleDbType.VarChar, 0, "ConceptoStr");
dataAdapter.InsertCommand.Parameters.Add("@FechaAlta", OleDbType.Date, 0, "FechaAlta");
dataAdapter.InsertCommand.Parameters.Add("@FechaAltaInt", OleDbType.Numeric, 0, "FechaAltaInt");
dataAdapter.Update(dataSet, "Conceptos");
dataSet.Dispose();
dataAdapter.Dispose();
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
MessageBox.Show("Error ({0}) : {1}" + ex.Source + ex.Message, methodName, MessageBoxButtons.OK, MessageBoxIcon.Warning);
ErrorUtilities.SetNewErrorMessage(ex, methodName, 0);
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
MessageBox.Show("Error ({0}) : {1}" + ex.Source + ex.Message, methodName, MessageBoxButtons.OK, MessageBoxIcon.Warning);
ErrorUtilities.SetNewErrorMessage(ex, methodName, 0);
}
finally
{
connection.Close();
}
}
/// <summary>
/// Agrega una propuesta de definición para un término
/// </summary>
/// <param name="generico"></param>
public void SetDefiniciones(Definiciones definicion)
{
OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["Diccionario"].ToString());
OleDbDataAdapter dataAdapter;
DataSet dataSet = new DataSet();
DataRow dr;
try
{
string sqlCadena = "SELECT * FROM Definiciones WHERE Id = 0";
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(sqlCadena, connection);
dataAdapter.Fill(dataSet, "Definiciones");
dr = dataSet.Tables["Definiciones"].NewRow();
dr["IdConcepto"] = definicion.IdGenerico;
dr["Definicion"] = definicion.Definicion;
if (!String.IsNullOrWhiteSpace(definicion.Definicion))
{
dr["DefinicionStr"] = ScjnUtilities.StringUtilities.PrepareToAlphabeticalOrder(definicion.Definicion);
}
else
{
dr["DefinicionStr"] = String.Empty;
}
dr["FechaAlta"] = DateTime.Now;
dr["FechaAltaInt"] = DateTimeUtilities.DateToInt(DateTime.Now);
dr["Fuente"] = definicion.Fuente;
if (!String.IsNullOrWhiteSpace(definicion.Fuente))
{
dr["FuenteStr"] = ScjnUtilities.StringUtilities.PrepareToAlphabeticalOrder(definicion.Fuente);
}
else
{
dr["FuenteStr"] = String.Empty;
}
dataSet.Tables["Definiciones"].Rows.Add(dr);
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText = "INSERT INTO Definiciones (IdConcepto,Definicion,DefinicionStr,FechaAlta,FechaAltaInt,Fuente,FuenteStr) VALUES (@IdConcepto,@Definicion,@DefinicionStr,@FechaAlta,@FechaAltaInt,@Fuente,@FuenteSrt)";
dataAdapter.InsertCommand.Parameters.Add("@IdConcepto", OleDbType.Numeric, 0, "IdConcepto");
dataAdapter.InsertCommand.Parameters.Add("@Definicion", OleDbType.VarChar, 0, "Definicion");
dataAdapter.InsertCommand.Parameters.Add("@DefinicionStr", OleDbType.VarChar, 0, "DefinicionStr");
dataAdapter.InsertCommand.Parameters.Add("@FechaAlta", OleDbType.Date, 0, "FechaAlta");
dataAdapter.InsertCommand.Parameters.Add("@FechaAltaInt", OleDbType.Numeric, 0, "FechaAltaInt");
dataAdapter.InsertCommand.Parameters.Add("@Fuente", OleDbType.VarChar, 0, "Fuente");
dataAdapter.InsertCommand.Parameters.Add("@FuenteStr", OleDbType.VarChar, 0, "FuenteStr");
dataAdapter.Update(dataSet, "Definiciones");
dataSet.Dispose();
dataAdapter.Dispose();
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,SetDefiniciones", "Diccionario");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,SetDefiniciones", "Diccionario");
}
finally
{
connection.Close();
}
}
private string GetPackFilename(int package, int nr, DateTime date2)
{
return(string.Format("{0}{1}Pack-{2}+_{3}.txt", filePathDestiny1, package, nr, DateTimeUtilities.DateToInt(date2)));
}
/// <summary>
/// Actualiza la fecha real de entrega cuando quien valida considera que la capturada no es la real
/// </summary>
/// <param name="tesis"></param>
/// <param name="fechaReal"></param>
public void UpdateTesis(Tesis tesis, DateTime?fechaReal)
{
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter dataAdapter;
OleDbCommand cmd = connection.CreateCommand();
cmd.Connection = connection;
try
{
connection.Open();
DataSet dataSet = new DataSet();
DataRow dr;
string sqlCadena = "SELECT * FROM Tesis WHERE IdTesis = " + tesis.IdTesis;
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(sqlCadena, connection);
dataAdapter.Fill(dataSet, "Tesis");
dr = dataSet.Tables[0].Rows[0];
dr.BeginEdit();
dr["FechaReal"] = fechaReal;
dr["FechaRealInt"] = DateTimeUtilities.DateToInt(fechaReal);
dr["IdUsuarioValida"] = AccesoUsuarioModel.Llave;
dr.EndEdit();
dataAdapter.UpdateCommand = connection.CreateCommand();
string sSql = "UPDATE Tesis " +
"SET FechaReal = @FechaReal, FechaRealInt = @FechaRealInt, IdUsuarioValida = @IdUsuarioValida" +
" WHERE IdTesis = @IdTesis";
dataAdapter.UpdateCommand.CommandText = sSql;
dataAdapter.UpdateCommand.Parameters.Add("@FechaReal", OleDbType.Date, 0, "FechaReal");
dataAdapter.UpdateCommand.Parameters.Add("@FechaRealInt", OleDbType.Numeric, 0, "FechaRealInt");
dataAdapter.UpdateCommand.Parameters.Add("@IdUsuarioValida", OleDbType.Numeric, 0, "IdUsuarioValida");
dataAdapter.UpdateCommand.Parameters.Add("@IdTesis", OleDbType.Numeric, 0, "IdTesis");
dataAdapter.Update(dataSet, "Tesis");
dataSet.Dispose();
dataAdapter.Dispose();
tesis.IdUsuarioValida = AccesoUsuarioModel.Llave;
tesis.FechaReal = fechaReal;
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TesisModel", "ListadoDeTesis");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TesisModel", "ListadoDeTesis");
}
finally
{
connection.Close();
}
}
/// <summary>
/// Actualiza la información de la tesis antes de que sea validada
/// </summary>
/// <param name="tesis"></param>
public void UpdateTesis(Tesis tesis)
{
OleDbConnection connection = new OleDbConnection(connectionString);
string sSql;
OleDbDataAdapter dataAdapter;
DataSet dataSet = new DataSet();
DataRow dr;
try
{
string sqlCadena = "SELECT * FROM Tesis WHERE IdTesis = " + tesis.IdTesis;
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand(sqlCadena, connection);
dataAdapter.Fill(dataSet, "Tesis");
dr = dataSet.Tables["Tesis"].Rows[0];
dr.BeginEdit();
dr["IdTesis"] = tesis.IdTesis;
dr["Tesis"] = tesis.ClaveTesis;
dr["Rubro"] = tesis.Rubro;
dr["RubroStr"] = StringUtilities.PrepareToAlphabeticalOrder(tesis.Rubro);
dr["Tatj"] = tesis.Tatj;
dr["IdInstancia"] = tesis.IdInstancia;
dr["IdSubInstancia"] = tesis.IdSubInstancia;
dr["OrdenInstancia"] = tesis.OrdenInstancia;
dr["IdColor"] = tesis.IdColor;
dr["MateriaAsignada"] = tesis.MateriaAsignada;
dr["Oficio"] = tesis.Oficio;
dr["FechaEnvio"] = tesis.FechaEnvio;
dr["FechaEnvioInt"] = DateTimeUtilities.DateToInt(tesis.FechaEnvio);
dr.EndEdit();
dataAdapter.UpdateCommand = connection.CreateCommand();
sSql = "UPDATE Tesis SET Tesis = @Tesis,Rubro = @Rubro,RubroStr = @RubroStr,Tatj = @Tatj,IdInstancia = @IdInstancia, " +
"IdSubInstancia = @IdSubInstancia,OrdenInstancia = @OrdenInstancia,IdColor = @IdColor," +
"MateriaAsignada = @MateriaAsignada,Oficio = @Oficio,FechaEnvio = @FechaEnvio,FechaEnvioInt = @FechaEnvioInt " +
" WHERE IdTesis = @IdTesis";
dataAdapter.UpdateCommand.CommandText = sSql;
dataAdapter.UpdateCommand.Parameters.Add("@Tesis", OleDbType.VarChar, 0, "Tesis");
dataAdapter.UpdateCommand.Parameters.Add("@Rubro", OleDbType.VarChar, 0, "Rubro");
dataAdapter.UpdateCommand.Parameters.Add("@RubroStr", OleDbType.VarChar, 0, "RubroStr");
dataAdapter.UpdateCommand.Parameters.Add("@Tatj", OleDbType.Numeric, 0, "Tatj");
dataAdapter.UpdateCommand.Parameters.Add("@IdInstancia", OleDbType.Numeric, 0, "IdInstancia");
dataAdapter.UpdateCommand.Parameters.Add("@IdSubInstancia", OleDbType.Numeric, 0, "IdSubInstancia");
dataAdapter.UpdateCommand.Parameters.Add("@OrdenInstancia", OleDbType.Numeric, 0, "OrdenInstancia");
dataAdapter.UpdateCommand.Parameters.Add("@IdColor", OleDbType.Numeric, 0, "IdColor");
dataAdapter.UpdateCommand.Parameters.Add("@MateriaAsignada", OleDbType.VarChar, 0, "MateriaAsignada");
dataAdapter.UpdateCommand.Parameters.Add("@Oficio", OleDbType.VarChar, 0, "Oficio");
dataAdapter.UpdateCommand.Parameters.Add("@FechaEnvio", OleDbType.Date, 0, "FechaEnvio");
dataAdapter.UpdateCommand.Parameters.Add("@FechaEnvioInt", OleDbType.Numeric, 0, "FechaEnvioInt");
dataAdapter.UpdateCommand.Parameters.Add("@IdTesis", OleDbType.Numeric, 0, "IdTesis");
dataAdapter.Update(dataSet, "Tesis");
dataSet.Dispose();
dataAdapter.Dispose();
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TesisModel", "ListadoDeTesis");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TesisModel", "ListadoDeTesis");
}
finally
{
connection.Close();
}
}
/// <summary>
/// Obtiene las tesis de un periodo específico para generar el listado que se enviará
/// </summary>
/// <param name="fInicio">Fecha en que se envió el último listado</param>
/// <param name="fFinal">Fecha en que se enviará el listado que se genere</param>
/// <returns></returns>
public ObservableCollection <Tesis> GetTesis(DateTime?fInicio, DateTime?fFinal)
{
string inicio = DateTimeUtilities.DateToInt(fInicio.Value.AddDays(1));
string final = DateTimeUtilities.DateToInt(fFinal);
ObservableCollection <Tesis> listaTesis = new ObservableCollection <Tesis>();
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand cmd = null;
OleDbDataReader reader = null;
String sqlCadena = "SELECT * FROM Tesis " +
"WHERE FechaRealInt BETWEEN @Inicio AND @Final ORDER BY FechaAltaInt desc, RubroStr asc";
try
{
connection.Open();
cmd = new OleDbCommand(sqlCadena, connection);
cmd.Parameters.AddWithValue("@Inicio", inicio);
cmd.Parameters.AddWithValue("@Final", final);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Tesis tesis = new Tesis();
tesis.IdTesis = Convert.ToInt32(reader["IdTesis"]);
tesis.ClaveTesis = reader["Tesis"].ToString();
tesis.Rubro = reader["Rubro"].ToString();
tesis.Tatj = Convert.ToInt32(reader["tatj"]);
tesis.IdInstancia = Convert.ToInt32(reader["IdInstancia"]);
tesis.IdSubInstancia = Convert.ToInt32(reader["IdSubInstancia"]);
tesis.OrdenInstancia = Convert.ToInt32(reader["OrdenInstancia"]);
tesis.IdColor = Convert.ToInt32(reader["IdColor"]);
tesis.MateriaAsignada = reader["MateriaAsignada"].ToString();
tesis.Oficio = reader["Oficio"].ToString();
tesis.IdUsuario = Convert.ToInt32(reader["IdUsuario"]);
tesis.MateriaAsignada = reader["MateriaAsignada"].ToString();
tesis.Oficio = reader["Oficio"].ToString();
tesis.FechaAltaSistema = DateTimeUtilities.GetDateFromReader(reader, "FechaAlta");
tesis.FechaEnvio = DateTimeUtilities.GetDateFromReader(reader, "FechaEnvio");
tesis.FechaReal = DateTimeUtilities.GetDateFromReader(reader, "FechaReal");
tesis.IdUsuarioValida = Convert.ToInt32(reader["IdUsuarioValida"]);
listaTesis.Add(tesis);
}
}
cmd.Dispose();
reader.Close();
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TesisModel", "ListadoDeTesis");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TesisModel", "ListadoDeTesis");
}
finally
{
connection.Close();
}
return(listaTesis);
}
public void SetNewTesis(Tesis tesis)
{
OleDbConnection connection = new OleDbConnection(connectionString);
string sSql;
OleDbDataAdapter dataAdapter;
DataSet dataSet = new DataSet();
DataRow dr;
try
{
tesis.IdTesis = DataBaseUtilities.GetNextIdForUse("Tesis", "IdTesis", connection);
dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM Tesis WHERE IdTesis = 0", connection);
dataAdapter.Fill(dataSet, "Tesis");
dr = dataSet.Tables["Tesis"].NewRow();
dr["IdTesis"] = tesis.IdTesis;
dr["Tesis"] = tesis.ClaveTesis;
dr["Rubro"] = tesis.Rubro;
dr["RubroStr"] = StringUtilities.PrepareToAlphabeticalOrder(tesis.Rubro);
dr["Tatj"] = tesis.Tatj;
dr["IdInstancia"] = tesis.IdInstancia;
dr["IdSubInstancia"] = tesis.IdSubInstancia;
dr["OrdenInstancia"] = tesis.OrdenInstancia;
dr["IdColor"] = tesis.IdColor;
dr["MateriaAsignada"] = tesis.MateriaAsignada;
dr["Oficio"] = tesis.Oficio;
dr["FechaAlta"] = DateTime.Now;
dr["FechaAltaInt"] = DateTimeUtilities.DateToInt(DateTime.Now);
dr["FechaEnvio"] = tesis.FechaEnvio;
dr["FechaEnvioInt"] = DateTimeUtilities.DateToInt(tesis.FechaEnvio);
dr["IdUsuario"] = AccesoUsuarioModel.Llave;
dataSet.Tables["Tesis"].Rows.Add(dr);
dataAdapter.InsertCommand = connection.CreateCommand();
sSql = "INSERT INTO Tesis (IdTesis,Tesis,Rubro,RubroStr,Tatj,IdInstancia,IdSubInstancia,OrdenInstancia,IdColor,MateriaAsignada,Oficio,FechaAlta,FechaAltaInt,FechaEnvio,FechaEnvioInt,IdUsuario) " +
" VALUES (@IdTesis,@Tesis,@Rubro,@RubroStr,@Tatj,@IdInstancia,@IdSubInstancia,@OrdenInstancia,@IdColor,@MateriaAsignada,@Oficio,@FechaAlta,@FechaAltaInt,@FechaEnvio,@FechaEnvioInt,@IdUsuario)";
dataAdapter.InsertCommand.CommandText = sSql;
dataAdapter.InsertCommand.Parameters.Add("@IdTesis", OleDbType.Numeric, 0, "IdTesis");
dataAdapter.InsertCommand.Parameters.Add("@Tesis", OleDbType.VarChar, 0, "Tesis");
dataAdapter.InsertCommand.Parameters.Add("@Rubro", OleDbType.VarChar, 0, "Rubro");
dataAdapter.InsertCommand.Parameters.Add("@RubroStr", OleDbType.VarChar, 0, "RubroStr");
dataAdapter.InsertCommand.Parameters.Add("@Tatj", OleDbType.Numeric, 0, "Tatj");
dataAdapter.InsertCommand.Parameters.Add("@IdInstancia", OleDbType.Numeric, 0, "IdInstancia");
dataAdapter.InsertCommand.Parameters.Add("@IdSubInstancia", OleDbType.Numeric, 0, "IdSubInstancia");
dataAdapter.InsertCommand.Parameters.Add("@OrdenInstancia", OleDbType.Numeric, 0, "OrdenInstancia");
dataAdapter.InsertCommand.Parameters.Add("@IdColor", OleDbType.Numeric, 0, "IdColor");
dataAdapter.InsertCommand.Parameters.Add("@MateriaAsignada", OleDbType.VarChar, 0, "MateriaAsignada");
dataAdapter.InsertCommand.Parameters.Add("@Oficio", OleDbType.VarChar, 0, "Oficio");
dataAdapter.InsertCommand.Parameters.Add("@FechaAlta", OleDbType.Date, 0, "FechaAlta");
dataAdapter.InsertCommand.Parameters.Add("@FechaAltaInt", OleDbType.Numeric, 0, "FechaAltaInt");
dataAdapter.InsertCommand.Parameters.Add("@FechaEnvio", OleDbType.Date, 0, "FechaEnvio");
dataAdapter.InsertCommand.Parameters.Add("@FechaEnvioInt", OleDbType.Numeric, 0, "FechaEnvioInt");
dataAdapter.InsertCommand.Parameters.Add("@IdUsuario", OleDbType.Numeric, 0, "IdUsuario");
dataAdapter.Update(dataSet, "Tesis");
dataSet.Dispose();
dataAdapter.Dispose();
}
catch (OleDbException ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TesisModel", "ListadoDeTesis");
}
catch (Exception ex)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
ErrorUtilities.SetNewErrorMessage(ex, methodName + " Exception,TesisModel", "ListadoDeTesis");
}
finally
{
connection.Close();
}
}
