public static string selectBecaUser(int idUser)
{
string result = "";
storedProcedure sp = new storedProcedure("ConnectionString");
string queryprom = "select promedio from alumno where idAlumno = " + idUser + "";
List <string> lsp = sp.recuperaRegistros(queryprom);
float prom = float.Parse(lsp[0]);
string querybeca = "select b.idBeca,b.nombreBeca from beca as b where b.promedio <= " + prom + " and b.idBeca not in (select distinct ba.idBeca from becasAlumnos as ba where ba.idAlumno = " + idUser + " and ba.idBeca = b.idBeca )";
List <string> lsb = sp.recuperaRegistros(querybeca);
if (lsb.Count > 0)
{
for (int i = 0; i < lsb.Count; i = i + 2)
{
result += "<h3><label>Beca:" + lsb[i + 1] + " </label><img src='images/images.jpg' heit='40px' width='40px' style='margin-left: 90px;' onclick='JavaScript:solicitar(" + lsb[i + 0] + ");'/>Solicitar</h3>";
}
}
else
{
result = "<h2>No hay becas disponibles en este momento</h2>";
}
return(result);
}
public DataTable getAmountOfUnreadMsg(string UserId)
{
DataTable table = new DataTable();
storedProcedure myProc = new storedProcedure("getAmountOfUnreadMsg");
myProc.addParam(new paramter("@UserId", UserId));
return table = myProc.executeReader();
}
public DataTable GetUserList()
{
DataTable table = new DataTable();
storedProcedure myProc = new storedProcedure("GetUserList");
return(table = myProc.executeReader());
}
public void search_Click(object sender, EventArgs e)
{
//if the user enters anything in the search box
if (!(String.IsNullOrEmpty(usernameInSearchBox)))
{
//check the entered text to see if it exist in the users table
storedProcedure myProc = new storedProcedure("checkUserName");
paramter param1 = new paramter("@theUsername", usernameInSearchBox);
myProc.addParam(param1);
//if that username exist the userID will be returned
userId = myProc.executeScalarParam();
//if the userId is not null or empty means the user was found
if (!(String.IsNullOrEmpty(userId)))
{
userFoundLabel.Text = "user " + usernameInSearchBox + " found";
userFoundLabel.Visible = true;
//send this username to the viewProfile page
string name = usernameInSearchBox;
Session["name"] = name;
Response.Redirect("viewProfile.aspx");
}
else
{
userFoundLabel.Text = "" + usernameInSearchBox + " is not a valid username";
userFoundLabel.Visible = true;
}
}
else
{
userFoundLabel.Text = "Enter a username";
userFoundLabel.Visible = true;
}
}//end of search_Click
private void btnNotificaciones_Click(object sender, EventArgs e)
{
btnTemas.BackColor = Color.FromArgb(41, 66, 91);
btnNotificaciones.BackColor = Color.FromArgb(50, 81, 112);
panelContTemas.Visible = false;
contNotificicaciones.Visible = true;
try
{
storedProcedure sp = new storedProcedure();
string[] notiParametros = new string[1];
listNotificaciones.Items.Clear();
notiParametros[0] = "@id_usuario = " + MenuVertical.usuarioSesion + "";
List <object[]> datos = sp.lt(notiParametros, "verNotificacion");
datos.Reverse();
foreach (object[] notificacion in datos)
{
listNotificaciones.Items.Add("[" + notificacion[1].ToString() + "] •" + notificacion[2].ToString());
}
}
catch
{
}
}
public static string obtenerTabla(int origen)
{
string tabla = "<table id='tablam'><thead><tr><th style='padding: 10px;'>Descripcion</th><th style='padding: 10px;'>Editar</th><th style='padding: 10px;'>Eliminar</th></tr></thead><tbod>";
string query = "";
storedProcedure sp = new storedProcedure("ConnectionString");
if (origen == 1)
{
query = "select nombreBeca,modificar,eliminar from view_beca";
}
else if (origen == 2)
{
query = "select * from view_grupo";
}
else if (origen == 3)
{
query = "select * from view_carrera";
}
else if (origen == 4)
{
query = "select * from view_tiposb";
}
List <string> ls = sp.recuperaRegistros(query);
for (int i = 0; i < ls.Count; i = i + 3)
{
tabla += "<tr><td>" + ls[i + 0] + "</td><td>" + ls[i + 1] + "</td><td>" + ls[i + 2] + "</td></tr>";
}
tabla += "</tbody></table>";
return(tabla);
}
public static List <AutoComplete> grupo(string term)
{
List <AutoComplete> resultado = new List <AutoComplete>();
List <string> obtener = new List <string>();
AutoComplete ac;
string query = "";
term = term.ToLower();
storedProcedure sp = new storedProcedure("ConnectionString");
query = "select idgrupo,grupo from grupo where grupo like '%" + term + "%'";
obtener = sp.recuperaRegistros(query);
if (obtener != null && obtener.Count > 0)
{
for (int i = 0; i < obtener.Count; i += 6)
{
ac = new AutoComplete();
ac.ID = obtener[i];
ac.nombre = obtener[i + 1];
resultado.Add(ac);
}
}
else
{
resultado.Add(new AutoComplete {
ID = "", nombre = "No se encontraron resultados"
});
}
return(resultado);
}
//carga variables locales
public static void cargarVariables()
{
try
{
storedProcedure sp = new storedProcedure();
string[] docParametros = new string[1]; //creamos un string para los parametros
docParametros[0] = "@id_usuario = " + MenuVertical.usuarioSesion + ""; //guardamos los parametros que queremos, no le ponemos las comillas simples al parametro, dará error
List <object[]> usuarios = sp.lt(docParametros, "verUsuario"); //mandamos a llamar la clase sp con la funcion lt
foreach (object[] usuario in usuarios) //cargamos los datos en nuestra lista
{
MenuVertical.clinicasesion = Convert.ToInt32(usuario[11]);
IDrolSesion = Convert.ToInt32(usuario[9]);
var array = Convert.FromBase64String(usuario[8].ToString());
using (var ms = new MemoryStream(array))
{
try
{
fotoPerfil = Image.FromStream(ms);
}
catch
{
fotoPerfil = capaPresentacion.Properties.Resources.Don_Bosco;
}
}
}
}
catch
{
}
}
public DataTable GetSentMessages(string userID)
{
DataTable table = new DataTable();
storedProcedure myProc = new storedProcedure("GetSentMessages");
myProc.addParam(new paramter("@userId", userID));
return table = myProc.executeReader();
}
public DataTable getAllUsernames()
{
storedProcedure myProc = new storedProcedure("getAllUsernames");
DataTable table = new DataTable();
return(table = myProc.executeReader());
}
private void configStudent()
{
storedProcedure myProc1 = new storedProcedure("getMajorName");
paramter getMajorNameParam1 = new paramter("@student_id", this.student_id);
myProc1.addParam(getMajorNameParam1);
this.major = myProc1.executeScalarParam();
storedProcedure myProc2 = new storedProcedure("getProfilePic");
paramter getProfilePicParam1 = new paramter("@UserId", this.student_id);
myProc2.addParam(getProfilePicParam1);
this.profilePic = myProc2.executeScalarParam();
storedProcedure myProc3 = new storedProcedure("getActualName");
paramter getActualNameParam1 = new paramter("@student_id", this.student_id);
myProc3.addParam(getActualNameParam1);
this.actualName = myProc3.executeScalarParam();
storedProcedure myProc4 = new storedProcedure("getAboutMe");
paramter getAboutMeParam1 = new paramter("@student_id", this.student_id);
myProc4.addParam(getAboutMeParam1);
this.aboutMe = myProc4.executeScalarParam();
}
public static bool validarUsuario(string email, string password)
{
storedProcedure sp = new storedProcedure("ConnectionString");
bool resultado = true;
string query = "select idAlumno,nombreCompleto from alumno as al inner join" +
" usuarioRol as ur on ur.idUsuario = al.idAlumno" +
" where al.usuario = '" + email + "'and contrasena = '" + password + "'";
List <string> listaUser = sp.recuperaRegistros(query);
if (listaUser.Count > 0)
{
int idUser = int.Parse(listaUser[0]);
string emailq = listaUser[1];
HttpContext.Current.Session["cve"] = idUser;
HttpContext.Current.Session["nombre"] = emailq;
HttpContext.Current.Session["logged"] = true;
resultado = true;
}
else
{
resultado = false;
}
return(resultado);
}
public bool MarkMessageRead(int msgId)
{
storedProcedure myProc = new storedProcedure("MarkAsRead");
myProc.addParam(new paramter("@id", msgId.ToString()));
return myProc.executeNonQueryParam();
}
public Tablas()
{
this.Table = "<table class='display' id=' align='center' ";
this.Thead = "<thead>";
this.Theadd = "<thead style=' background-color:#ffcc66;font-style:italic;font-weight:bolder;font-size:large;text-align:center;'>";
this.Th = "<th width='200px'>";
this.Cth = "</th>";
this.Tr = "<tr>";
this.Ctr = "</tr>";
this.Cthead = "</thead>";
this.Tbody = "<tbody>";
this.Ctbody = "</tbody>";
this.Td = "<td>";
this.Ctd = "</td>";
this.Ctable = "</table>";
this.A = "<a href='javascript:";
this.Ca = "</a>";
this.Input = "<input type='checkbox' name='selNP' class='nc' onclick='javascript:habilitarC(this);' value='";
this.Pinput = "<input type='checkbox' id='all' onclick='javascript:seleccionarTodos();' />";
this.InputText = "<input type='text' name='comentario' id='";
this.Tfoot = "<tfoot>";
this.Cfoot = "</tfoot>";
this.Tabla = "";
sp = new storedProcedure("sccConnectionString");
// utileria = new Util();
resultado = new List<string>();
}
public DataTable getFriendsList(string user_id)
{
storedProcedure myProc = new storedProcedure("getFriendsList");
myProc.addParam(new paramter("@user_id", user_id));
DataTable table = new DataTable();
return table = myProc.executeReader();
}
public void removeFriend(string user_id, string friend_id)
{
storedProcedure myProc = new storedProcedure("removeFriend");
myProc.addParam(new paramter("@user_id", user_id));
myProc.addParam(new paramter("@friend_id", friend_id));
myProc.executeNonQueryParam();
}
public DataTable GetUserDetails(string userId)
{
DataTable table = new DataTable();
storedProcedure myProc = new storedProcedure("GetUserDetails");
myProc.addParam(new paramter("@userId", userId));
return table = myProc.executeReader();
}
public bool MarkMessageRead(int msgId)
{
storedProcedure myProc = new storedProcedure("MarkAsRead");
myProc.addParam(new paramter("@id", msgId.ToString()));
return(myProc.executeNonQueryParam());
}
public DataTable getAmountOfUnreadMsg(string UserId)
{
DataTable table = new DataTable();
storedProcedure myProc = new storedProcedure("getAmountOfUnreadMsg");
myProc.addParam(new paramter("@UserId", UserId));
return(table = myProc.executeReader());
}
public DataTable GetSentMessages(string userID)
{
DataTable table = new DataTable();
storedProcedure myProc = new storedProcedure("GetSentMessages");
myProc.addParam(new paramter("@userId", userID));
return(table = myProc.executeReader());
}
public DataTable GetMessageDetails(int messageId)
{
DataTable table = new DataTable();
storedProcedure myProc = new storedProcedure("ReadMessage");
myProc.addParam(new paramter("@id", messageId.ToString()));
return table = myProc.executeReader();
}
public static string eliminarS(int idB)
{
storedProcedure sp = new storedProcedure("ConnectionString");
sp.ejecutaSQL("delete becasAlumnos where idBecasAlumnos = " + idB + " ");
return("0");
}
public static string saveUser(string name, string email, string pass, int idRol, int idCarrera, int idGrupo, float prom)
{
string result = "";
storedProcedure sp = new storedProcedure("ConnectionString");
result = sp.saveUser(name, email, pass, idRol, idCarrera, idGrupo, prom);
return(result);
}
public static string solicitarB(int iduser, int idBeca)
{
storedProcedure sp = new storedProcedure("ConnectionString");
sp.ejecutaSQL("insert into becasAlumnos values(" + iduser + "," + idBeca + ")");
return("0");
}
public DataTable getConversation(string recieverID, string senderID)
{
storedProcedure myProc = new storedProcedure("getConversation");
myProc.addParam(new paramter("@recieverID", recieverID));
myProc.addParam(new paramter("@senderID", senderID));
DataTable table = new DataTable();
return table = myProc.executeReader();
}
public void removeFriend(string user_id, string friend_id)
{
storedProcedure myProc = new storedProcedure("removeFriend");
myProc.addParam(new paramter("@user_id", user_id));
myProc.addParam(new paramter("@friend_id", friend_id));
myProc.executeNonQueryParam();
}
public DataTable getFriendsList(string user_id)
{
storedProcedure myProc = new storedProcedure("getFriendsList");
myProc.addParam(new paramter("@user_id", user_id));
DataTable table = new DataTable();
return(table = myProc.executeReader());
}
public DataTable checkIfFriend(string user_id, string friend_id)
{
storedProcedure myProc = new storedProcedure("checkIfFriend");
myProc.addParam(new paramter("@user_id", user_id));
myProc.addParam(new paramter("@friend_id", friend_id));
DataTable table = new DataTable();
return table = myProc.executeReader();
}
public DataTable GetMessageDetails(int messageId)
{
DataTable table = new DataTable();
storedProcedure myProc = new storedProcedure("ReadMessage");
myProc.addParam(new paramter("@id", messageId.ToString()));
return(table = myProc.executeReader());
}
public DataTable mutualFriends(string user_id, string visitedUser_Id)
{
storedProcedure myProc = new storedProcedure("mutualFriends");
myProc.addParam(new paramter("@user_id", user_id));
myProc.addParam(new paramter("@visitedUser_Id", visitedUser_Id));
DataTable table = new DataTable();
return table = myProc.executeReader();
}
public DataTable mutualFriends(string user_id, string visitedUser_Id)
{
storedProcedure myProc = new storedProcedure("mutualFriends");
myProc.addParam(new paramter("@user_id", user_id));
myProc.addParam(new paramter("@visitedUser_Id", visitedUser_Id));
DataTable table = new DataTable();
return(table = myProc.executeReader());
}
public DataTable checkIfFriend(string user_id, string friend_id)
{
storedProcedure myProc = new storedProcedure("checkIfFriend");
myProc.addParam(new paramter("@user_id", user_id));
myProc.addParam(new paramter("@friend_id", friend_id));
DataTable table = new DataTable();
return(table = myProc.executeReader());
}
public DataTable getConversation(string recieverID, string senderID)
{
storedProcedure myProc = new storedProcedure("getConversation");
myProc.addParam(new paramter("@recieverID", recieverID));
myProc.addParam(new paramter("@senderID", senderID));
DataTable table = new DataTable();
return(table = myProc.executeReader());
}
public static bool deleteUser(int idUser)
{
bool result = true;
storedProcedure sp = new storedProcedure("ConnectionString");
result = sp.ejecutaSQL("delete usuarioRol where idUsuario =" + idUser + "");
result = sp.ejecutaSQL("delete becasAlumnos where idAlumno = " + idUser + "");
result = sp.ejecutaSQL("delete alumno where idAlumno = " + idUser + "");
return(result);
}
public bool SendMessage(string senderID, string recieverID, string subject, string body)
{
storedProcedure myProc = new storedProcedure("SendMessage");
myProc.addParam(new paramter("@senderID", senderID));
myProc.addParam(new paramter("@recieverID", recieverID));
myProc.addParam(new paramter("@subject", subject));
myProc.addParam(new paramter("@body", body));
return myProc.executeNonQueryParam();
}
public static List <string> obtenerDatosUser(int iduser)
{
storedProcedure sp = new storedProcedure("ConnectionString");
string query = "select nombreCompleto,promedio,carrera,grupo from alumno as a inner join carrera as c" +
" on c.idCarrera = a.iCarrera inner join grupo as g on g.idgrupo = a.idGrupo" +
" where a.idAlumno = " + iduser + "";
List <string> lsalumno = sp.recuperaRegistros(query);
return(lsalumno);
}
public bool SendMessage(string senderID, string recieverID, string subject, string body)
{
storedProcedure myProc = new storedProcedure("SendMessage");
myProc.addParam(new paramter("@senderID", senderID));
myProc.addParam(new paramter("@recieverID", recieverID));
myProc.addParam(new paramter("@subject", subject));
myProc.addParam(new paramter("@body", body));
return(myProc.executeNonQueryParam());
}
//verifica los roles, muesta los botones dependiendo el rol
private void roles()
{
storedProcedure sp = new storedProcedure();
string[] permisosParametros = new string[1]; //creamos un string para los parametros
permisosParametros[0] = "@ID_rol = " + MenuVertical.IDrolSesion + ""; //guardamos los parametros que queremos, no le ponemos las comillas simples al parametro, dará error
List <object[]> usuariosPermisos = sp.lt(permisosParametros, "verPermisos"); //mandamos a llamar la clase sp con la funcion lt
foreach (object[] permiso in usuariosPermisos)
{
foreach (Control ctl in this.PanelBotonLaboratorio.Controls)
{
var a = ctl;
var tg = a.Tag;
if (tg != null && tg.Equals(permiso[1].ToString()))
{
a.Visible = true;
}
var b = 0;
}
foreach (Control ctl in this.panelBotonClinica.Controls)
{
var a = ctl;
var tg = a.Tag;
if (tg != null && tg.Equals(permiso[1].ToString()))
{
a.Visible = true;
}
var b = 0;
}
}
switch (MenuVertical.clinicasesion)
{
case 1:
break;
case 2:
panelBotonClinica.Visible = false;
PanelAdministracion.Visible = false;
break;
case 3:
PanelBotonLaboratorio.Visible = false;
PanelAdministracion.Visible = false;
break;
}
}
//this constructor is a little patch so that we can simulate a constructor overiding
//because we need to make it possible to create a class using two different types of strings so the int 0 is just to make
//the distinction between the two
public student22(String UserID, int dontCareNum)
{
this.student_id = UserID;
storedProcedure myProc1 = new storedProcedure("getUserNameFromUserID");
myProc1.addParam(new paramter("@UserID", UserID));
this.username = myProc1.executeScalarParam();
con = new SqlConnection(myDbConString);
configStudent();
}
public student22(String currentUserName)
{
this.username = currentUserName;
storedProcedure myProc1 = new storedProcedure("getUserId");
myProc1.addParam(new paramter("@UserName", currentUserName));
this.student_id = myProc1.executeScalarParam();
con = new SqlConnection(myDbConString);
configStudent();
}
//this constructor is a little patch so that we can simulate a constructor overiding
//because we need to make it possible to create a class using two different types of strings so the int 0 is just to make
//the distinction between the two
public student22(String UserID, int dontCareNum)
{
this.student_id = UserID;
storedProcedure myProc1 = new storedProcedure("getUserNameFromUserID");
myProc1.addParam(new paramter("@UserID", UserID));
this.username = myProc1.executeScalarParam();
con = new SqlConnection(myDbConString);
configStudent();
}
public student22(String currentUserName)
{
this.username = currentUserName;
storedProcedure myProc1 = new storedProcedure("getUserId");
myProc1.addParam(new paramter("@UserName", currentUserName));
this.student_id = myProc1.executeScalarParam();
con = new SqlConnection(myDbConString);
configStudent();
}
public static List <string> selectUser(int idUser)
{
string result = "";
storedProcedure sp = new storedProcedure("ConnectionString");
string query = "select a.usuario,a.contrasena,r.idrol,c.idCarrera,c.carrera,g.idgrupo,g.grupo,a.promedio from alumno as a inner join " +
" carrera as c on c.idCarrera = a.iCarrera inner join " +
" grupo as g on g.idgrupo = a.idGrupo inner join usuarioRol as ur" +
" on ur.idUsuario = a.idAlumno inner join rol as r on r.idRol = ur.idRol" +
" where idAlumno = " + idUser + "";
List <string> listaUser = sp.recuperaRegistros(query);
return(listaUser);
}
public static string cargarCombo()
{
string combo = "<select id='ss'>";
storedProcedure sp = new storedProcedure("ConnectionString");
string querycombo = "select distinct idTipoBeca, tipoBeca from tipoBeca";
List <string> lsbeca = sp.recuperaRegistros(querycombo);
for (int i = 0; i < lsbeca.Count; i = i = i + 2)
{
combo += "<option value='" + lsbeca[i + 0] + "'>" + lsbeca[i + 1] + "</option>";
}
combo += "</select>";
return(combo);
}
private void configure()
{
storedProcedure myProcReader = new storedProcedure("getAllMajors");
DataTable dt = myProcReader.executeReader();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
String majorName = dr["major_name"].ToString();
this.majorsList.Add(majorName);
}
}
else
{
Console.WriteLine("No rows found.");
}
}
public void search_Click(object sender, EventArgs e)
{
//if the user enters anything in the search box
if (!(String.IsNullOrEmpty(usernameInSearchBox)))
{
//check the entered text to see if it exist in the users table
storedProcedure myProc = new storedProcedure("checkUserName");
paramter param1 = new paramter("@theUsername", usernameInSearchBox);
myProc.addParam(param1);
//if that username exist the userID will be returned
userId = myProc.executeScalarParam();
//if the userId is not null or empty means the user was found
if (!(String.IsNullOrEmpty(userId)))
{
userFoundLabel.Text = "user " + usernameInSearchBox + " found";
userFoundLabel.Visible = true;
//send this username to the viewProfile page
string name = usernameInSearchBox;
Session["name"] = name;
Response.Redirect("viewProfile.aspx");
}
else
{
userFoundLabel.Text = "" + usernameInSearchBox + " is not a valid username";
userFoundLabel.Visible = true;
}
}
else
{
userFoundLabel.Text = "Enter a username";
userFoundLabel.Visible = true;
}
}
public DataTable getAllUsernames()
{
storedProcedure myProc = new storedProcedure("getAllUsernames");
DataTable table = new DataTable();
return table = myProc.executeReader();
}
public DataTable GetUserList()
{
DataTable table = new DataTable();
storedProcedure myProc = new storedProcedure("GetUserList");
return table = myProc.executeReader();
}
public void setStudentAttributes(String newName,String newSelectedMajor,String newAboutMe)
{
if (String.IsNullOrEmpty(newName))
newName = this.actualName;
else this.actualName = newName;
if (String.IsNullOrEmpty(newSelectedMajor))
newSelectedMajor = this.major;
else this.major = newSelectedMajor;
this.aboutMe = newAboutMe;
paramter param1 = new paramter("@student_id", this.student_id);
paramter param2 = new paramter("@actualName", newName);
paramter param3 = new paramter("@major_name", newSelectedMajor);
paramter param4 = new paramter("@text", newAboutMe);
storedProcedure myProc = new storedProcedure("updateStudentInfo");
myProc.addParam(param1);
myProc.addParam(param2);
myProc.addParam(param3);
myProc.addParam(param4);
myProc.executeNonQueryParam();
}
private void configStudent()
{
storedProcedure myProc1 = new storedProcedure("getMajorName");
paramter getMajorNameParam1 = new paramter("@student_id", this.student_id);
myProc1.addParam(getMajorNameParam1);
this.major = myProc1.executeScalarParam();
storedProcedure myProc2 = new storedProcedure("getProfilePic");
paramter getProfilePicParam1 = new paramter("@UserId", this.student_id);
myProc2.addParam(getProfilePicParam1);
this.profilePic = myProc2.executeScalarParam();
storedProcedure myProc3 = new storedProcedure("getActualName");
paramter getActualNameParam1 = new paramter("@student_id", this.student_id);
myProc3.addParam(getActualNameParam1);
this.actualName = myProc3.executeScalarParam();
storedProcedure myProc4 = new storedProcedure("getAboutMe");
paramter getAboutMeParam1 = new paramter("@student_id", this.student_id);
myProc4.addParam(getAboutMeParam1);
this.aboutMe = myProc4.executeScalarParam();
}
