public void UpdateIndicator(E_Indicador e_Indicador)
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("UpdateIndicator", connectionBD.OpenConnection());
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@IdIndicador", e_Indicador.IdIndicador));
cmd.Parameters.Add(new SqlParameter("@Indicador", e_Indicador.Indicador));
cmd.Parameters.Add(new SqlParameter("@IdTipoValor", e_Indicador.IdTipoValor));
cmd.Parameters.Add(new SqlParameter("@IdMaxMin", e_Indicador.IdMaxMin));
cmd.Parameters.Add(new SqlParameter("@IdPerspectiva", e_Indicador.IdPerspectiva));
cmd.Parameters.Add(new SqlParameter("@Activo", e_Indicador.Activo));
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
}
public DataTable M_GetObjectives(int IdBSC)
{
connectionBD = new D_ConnectionBD();
DataTable dt = new DataTable();
String cadena = "select O.IdObjetivo, O.Objetivo" +
" from Objetivo O " +
"join Perspectiva P on P.IdPerspectiva = O.IdPerspectiva where P.IdBalanced_Scorecard = @IdBalanced_Scorecard";
try
{
cmd = new SqlCommand(cadena, connectionBD.OpenConnection());
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdBalanced_Scorecard", IdBSC);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
return(dt);
}
public DataTable GetMonths()
{
connectionBD = new D_ConnectionBD();
DataTable dt = new DataTable();
string command = "select * from Mes";
try
{
cmd = new SqlCommand(command, connectionBD.OpenConnection());
cmd.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
return(dt);
}
public void UpdateControlObj(E_ObjectiveControl OControl)
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("UpdateControlObj", connectionBD.OpenConnection());
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@IdValorObjetivo", OControl.IdValorObjetivo));
cmd.Parameters.Add(new SqlParameter("@ValorObjetivo", OControl.Meta));
cmd.Parameters.Add(new SqlParameter("@ValorReal", OControl.Alcanzado));
cmd.Parameters.Add(new SqlParameter("@IdObjetivo", OControl.IdObjetivo));
cmd.Parameters.Add(new SqlParameter("@IdMes", OControl.IdMes));
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
}
public void UpdateObjective(E_Objetivo objective)
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("UpdateObjective", connectionBD.OpenConnection());
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@IdObjetivo", objective.IdObjetivo));
cmd.Parameters.Add(new SqlParameter("@Objetivo", objective.Objetivo));
cmd.Parameters.Add(new SqlParameter("@IdPerspectiva", objective.IdPerspectiva));
cmd.Parameters.Add(new SqlParameter("@IdIndicador", objective.IdIndicador));
cmd.Parameters.Add(new SqlParameter("@Activo", objective.Activo));
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
}
//Insert en la tabla Objetivo
public DataTable GetO_Indicators(int IdBSC)
{
connectionBD = new D_ConnectionBD();
DataTable dt = new DataTable();
try
{
cmd = new SqlCommand("QueryIndicators", connectionBD.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IdBalanced_Scorecard", IdBSC);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
return(dt);
}
public bool InsertUser(E_Register ObjSign)
{
connection = new D_ConnectionBD();
SqlCommand command = new SqlCommand("InsertUser", connection.OpenConnection());
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@NombreUsuario", ObjSign.Username);
command.Parameters.AddWithValue("@Contrasegna", ObjSign.Password);
command.Parameters.AddWithValue("@IdTipo_Usuario", ObjSign.UserRoleId);
command.Parameters.AddWithValue("@Activo", 1);
command.Parameters.AddWithValue("@Nombre", ObjSign.Name);
command.Parameters.AddWithValue("@Apellido1", ObjSign.Surname1);
command.Parameters.AddWithValue("@Apellido2", ObjSign.Surname2);
try
{
return(connection.EIDB(command));
}
catch (Exception ex)
{
throw ex;
}
}
public bool InsertM_Metrica(E_Metrica ObjMetrica)
{
connectionBD = new D_ConnectionBD();
string command = "insert into Metrica (Metrica, IdObjetivo) values (@Metrica, @IdObjetivo)";
cmd = new SqlCommand(command, connectionBD.OpenConnection());
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Metrica", ObjMetrica.Metrica);
cmd.Parameters.AddWithValue("@IdObjetivo", ObjMetrica.IdObjective);
try
{
return(connectionBD.EIDB(cmd));
}
catch (Exception ex)
{
throw new Exception("Error al insertar los datos en la base de datos", ex);
}
}
public List <E_ObjectiveControl> Query_ControlO_Dg(int IdObjective)
{
List <E_ObjectiveControl> returnedlist = null;
try
{
SqlDataReader datareader;
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("QueryControlOb", connectionBD.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IdObjetivo", IdObjective);
cmd.CommandTimeout = 0;
datareader = cmd.ExecuteReader();
returnedlist = new List <E_ObjectiveControl>();
while (datareader.Read())
{
E_ObjectiveControl objOCDg = new E_ObjectiveControl();
objOCDg.IdValorObjetivo = datareader.GetInt32(0);
objOCDg.Perspectiva = datareader.GetString(1);
objOCDg.Objetivo = datareader.GetString(2);
objOCDg.Mes = datareader.GetString(3);
objOCDg.IdObjetivo = datareader.GetInt32(4);
objOCDg.Meta = datareader.GetString(5);
objOCDg.Alcanzado = datareader.GetString(6);
objOCDg.IdMes = datareader.GetInt32(7);
objOCDg.Valor = datareader.GetString(8);
objOCDg.TipoDato = datareader.GetString(9);
objOCDg.MaxMin = datareader.GetString(10);
objOCDg.Indicador = datareader.GetString(11);
returnedlist.Add(objOCDg);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
return(returnedlist);
}
public bool InsertIndicator(E_Indicador Indicator)
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("InsertIndicator", connectionBD.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Indicador", Indicator.Indicador);
cmd.Parameters.AddWithValue("@IdTipoValor_Indicador", Indicator.IdTipoValor);
cmd.Parameters.AddWithValue("@IdTipoMaxMin_Indicador", Indicator.IdMaxMin);
cmd.Parameters.AddWithValue("@IdPerspectiva", Indicator.IdPerspectiva);
cmd.Parameters.AddWithValue("@Activo", Indicator.Activo);
try
{
return(connectionBD.EIDB(cmd));
}
catch (Exception ex)
{
throw new Exception("Error al insertar los datos en la base de datos", ex);
}
}
public bool InsertO_Objetivo(E_Objetivo ObjObjetive)
{
connectionBD = new D_ConnectionBD();
string command = "insert into Objetivo (Objetivo, IdPerspectiva, IdIndicador, Activo) values (@Objetivo, @IdPerspectiva, @IdIndicador, @Activo)";
cmd = new SqlCommand(command, connectionBD.OpenConnection());
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Objetivo", ObjObjetive.Objetivo);
cmd.Parameters.AddWithValue("@IdPerspectiva", ObjObjetive.IdPerspectiva);
cmd.Parameters.AddWithValue("@IdIndicador", ObjObjetive.IdIndicador);
cmd.Parameters.AddWithValue("@Activo", ObjObjetive.Activo);
try
{
return(connectionBD.EIDB(cmd));
}
catch (Exception ex)
{
throw new Exception("Error al insertar los datos en la base de datos", ex);
}
}
//Función para insertar Cuadros de Mando Integral en la base de datos
public bool InsertBSC(E_BalancedScorecard ObjBSC, int UserId)
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("InsertCMI", connectionBD.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BS_Nombre", ObjBSC.BSNombre);
cmd.Parameters.AddWithValue("@N_Empresa", ObjBSC.N_Empresa);
cmd.Parameters.AddWithValue("@Vision", ObjBSC.Vision);
cmd.Parameters.AddWithValue("@Mision", ObjBSC.Mision);
cmd.Parameters.AddWithValue("@Agno", ObjBSC.Agno);
cmd.Parameters.AddWithValue("@Activo", ObjBSC.Active);
cmd.Parameters.AddWithValue("@IdUsuario", UserId);
try
{
return(connectionBD.EIDB(cmd));
}
catch (Exception ex)
{
throw new Exception("Error al insertar los datos en la base de datos", ex);
}
}
public DataTable LogQuery(E_User ObjUser)
{
DataTable table = new DataTable();
try
{
connection = new D_ConnectionBD();
SqlCommand command = new SqlCommand("QueryLog", connection.OpenConnection());
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Usr", ObjUser.Username);
command.Parameters.AddWithValue("@Pass", ObjUser.Password);
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(table);
command.Connection = connection.CloseConnection();
}
catch (Exception ex)
{
throw new Exception("No se pudo solicitar los datos para el inicio de sesión", ex);
}
return(table);
}
//Consulta para crear los botones en el panel vertical de la interfaz Menu
public List <E_BalancedScorecard> QueryCMI(string username)
{
List <E_BalancedScorecard> returnedlist = null;
try
{
SqlDataReader datareader;
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("QueryCMI", connectionBD.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", username);
cmd.CommandTimeout = 0;
datareader = cmd.ExecuteReader();
returnedlist = new List <E_BalancedScorecard>();
while (datareader.Read())
{
E_BalancedScorecard objBSC = new E_BalancedScorecard();
objBSC.IdBalanced_Scorecard = datareader.GetInt32(0);
objBSC.BSNombre = datareader.GetString(1);
objBSC.N_Empresa = datareader.GetString(2);
objBSC.Vision = datareader.GetString(3);
objBSC.Mision = datareader.GetString(4);
objBSC.Agno = datareader.GetDateTime(5);
objBSC.Active = datareader.GetBoolean(6);
returnedlist.Add(objBSC);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
return(returnedlist);
}
public List <E_ObjectivesDg> QueryObjectivesDg(int CMI)
{
List <E_ObjectivesDg> returnedlist = null;
try
{
SqlDataReader datareader;
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("QueryObjectives", connectionBD.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IdBalanced_Scorecard", CMI);
cmd.CommandTimeout = 0;
datareader = cmd.ExecuteReader();
returnedlist = new List <E_ObjectivesDg>();
while (datareader.Read())
{
E_ObjectivesDg objObDg = new E_ObjectivesDg();
objObDg.IdObjective = datareader.GetInt32(0);
objObDg.Perspective = datareader.GetString(1);
objObDg.Objetive = datareader.GetString(2);
objObDg.Indicator = datareader.GetString(3);
objObDg.Value = datareader.GetString(4);
objObDg.MaxMin = datareader.GetString(5);
objObDg.Active = datareader.GetBoolean(6);
returnedlist.Add(objObDg);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
return(returnedlist);
}
public void DeleteIndicator(E_Indicador e_Indicador)
{
string command = "delete from Indicador where IdIndicador = @IdIndicador";
try
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand(command, connectionBD.OpenConnection());
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@IdIndicador", e_Indicador.IdIndicador));
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
}
public void DeleteObjective(E_Objetivo objetivo)
{
string command = "delete from Objetivo where IdObjetivo = @IdObjetivo";
try
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand(command, connectionBD.OpenConnection());
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@IdObjetivo", objetivo.IdObjetivo));
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
}
public bool InsertControlObj(E_ObjectiveControl objectiveControl)
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("InsertValorObjetivo", connectionBD.OpenConnection());
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ValorObjetivo", objectiveControl.Meta));
cmd.Parameters.Add(new SqlParameter("@ValorReal", objectiveControl.Alcanzado));
cmd.Parameters.Add(new SqlParameter("@IdObjetivo", objectiveControl.IdObjetivo));
cmd.Parameters.Add(new SqlParameter("@IdMes", objectiveControl.IdMes));
return(connectionBD.EIDB(cmd));
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
}
public void UpdateMetrica(E_Metrica metrica)
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("UpdateMetrica", connectionBD.OpenConnection());
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@IdMetrica", metrica.IdMetrica));
cmd.Parameters.Add(new SqlParameter("@Metrica", metrica.Metrica));
cmd.Parameters.Add(new SqlParameter("@IdObjetivo", metrica.IdObjective));
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
}
public void DeleteMetrica(E_Metrica metrica)
{
string command = "delete from Metrica where IdMetrica = @IdMetrica";
try
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand(command, connectionBD.OpenConnection());
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@IdMetrica", metrica.IdMetrica));
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
}
public List <E_Metrica> QueryMetricasDg(int IdObjective)
{
List <E_Metrica> returnedlist = null;
try
{
SqlDataReader datareader;
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("QueryMetricas", connectionBD.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IdObjetivo", IdObjective);
cmd.CommandTimeout = 0;
datareader = cmd.ExecuteReader();
returnedlist = new List <E_Metrica>();
while (datareader.Read())
{
E_Metrica objMeDg = new E_Metrica();
objMeDg.IdObjective = datareader.GetInt32(0);
objMeDg.Perspective = datareader.GetString(1);
objMeDg.Objective = datareader.GetString(2);
objMeDg.IdMetrica = datareader.GetInt32(3);
objMeDg.Metrica = datareader.GetString(4);
returnedlist.Add(objMeDg);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
return(returnedlist);
}
//Consulta para construir la interfaz del Menu con la información de base de datos
public E_BalancedScorecard Select_BSC_Menu(int idBalancedScorecard)
{
connectionBD = new D_ConnectionBD();
cmd = new SqlCommand("QueryMenu", connectionBD.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IdBalanced_Scorecard", idBalancedScorecard);
cmd.CommandTimeout = 0;
E_BalancedScorecard bsc;
SqlDataReader reader;
try
{
reader = cmd.ExecuteReader();
bsc = new E_BalancedScorecard();
if (reader.Read())
{
bsc.IdBalanced_Scorecard = reader.GetInt32(0);
bsc.BSNombre = reader.GetString(1);
bsc.N_Empresa = reader.GetString(2);
bsc.Vision = reader.GetString(3);
bsc.Mision = reader.GetString(4);
bsc.Agno = reader.GetDateTime(5);
bsc.Active = reader.GetBoolean(6);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection = connectionBD.CloseConnection();
cmd.Dispose();
}
return(bsc);
}