Marown/swad-core
Marown
/
swad-core
mirror of https://github.com/acanas/swad-core.git
1
0
Fork 0
Code Issues Packages Projects Releases Wiki Activity
swad-core/swad_user_database.c

1595 lines
54 KiB
C
Raw Permalink Blame History

// swad_user_database.c: users, operations with database
/*
SWAD (Shared Workspace At a Distance),
is a web platform developed at the University of Granada (Spain),
and used to support university teaching.
This file is part of SWAD core.
Copyright (C) 1999-2024 Antonio Cañas Vargas
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Affero General 3 License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Affero General Public License for more details.
You should have received a copy of the GNU Affero General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
/*****************************************************************************/
/*********************************** Headers *********************************/
/*****************************************************************************/
#include <stdlib.h> // For free
#include "swad_action_list.h"
#include "swad_database.h"
#include "swad_error.h"
#include "swad_global.h"
#include "swad_user_database.h"
/*****************************************************************************/
/************** External global variables from others modules ****************/
/*****************************************************************************/
extern struct Globals Gbl;
/*****************************************************************************/
/******************************* Update my office ****************************/
/*****************************************************************************/
void Usr_DB_UpdateMyOffice (void)
{
DB_QueryUPDATE ("can not update office",
"UPDATE usr_data"
" SET Office='%s'"
" WHERE UsrCod=%ld",
Gbl.Usrs.Me.UsrDat.Tch.Office,
Gbl.Usrs.Me.UsrDat.UsrCod);
}
/*****************************************************************************/
/***************************** Update my office phone ************************/
/*****************************************************************************/
void Usr_DB_UpdateMyOfficePhone (void)
{
DB_QueryUPDATE ("can not update office phone",
"UPDATE usr_data"
" SET OfficePhone='%s'"
" WHERE UsrCod=%ld",
Gbl.Usrs.Me.UsrDat.Tch.OfficePhone,
Gbl.Usrs.Me.UsrDat.UsrCod);
}
/*****************************************************************************/
/************** Check if a user exists with a given user's code **************/
/*****************************************************************************/
bool Usr_DB_ChkIfUsrCodExists (long UsrCod)
{
/***** Trivial check: user's code should be > 0 *****/
if (UsrCod <= 0) // Wrong user's code
return false;
/***** Check if a user exists in database *****/
return
DB_QueryEXISTS ("can not check if a user exists",
"SELECT EXISTS"
"(SELECT *"
" FROM usr_data"
" WHERE UsrCod=%ld)",
UsrCod);
}
/*****************************************************************************/
/******** Get user's code from database using encrypted user's code **********/
/*****************************************************************************/
// Input: UsrDat->EncryptedUsrCod must hold user's encrypted code
long Usr_DB_GetUsrCodFromEncryptedUsrCod (const char EncryptedUsrCod[Cry_BYTES_ENCRYPTED_STR_SHA256_BASE64 + 1])
{
return
DB_QuerySELECTCode ("can not get user's code",
"SELECT UsrCod"
" FROM usr_data"
" WHERE EncryptedUsrCod='%s'",
EncryptedUsrCod);
}
/*****************************************************************************/
/***************** Get user's code from database using nickname **************/
/*****************************************************************************/
long Usr_DB_GetUsrCodFromNick (const char *NickWithoutArr)
{
return
DB_QuerySELECTCode ("can not get user's code",
"SELECT UsrCod"
" FROM usr_nicknames"
" WHERE Nickname='%s'",
NickWithoutArr);
}
/*****************************************************************************/
/******** Get user's code from database using nickname and password **********/
/*****************************************************************************/
long Usr_DB_GetUsrCodFromNickPwd (const char *NickWithoutArr,const char *Password)
{
return
DB_QuerySELECTCode ("can not get user's code",
"SELECT usr_nicknames.UsrCod"
" FROM usr_nicknames,"
"usr_data"
" WHERE usr_nicknames.Nickname='%s'"
" AND usr_nicknames.UsrCod=usr_data.UsrCod"
" AND usr_data.Password='%s'",
NickWithoutArr,
Password);
}
/*****************************************************************************/
/**************** Get user's code from database using email ******************/
/*****************************************************************************/
long Usr_DB_GetUsrCodFromEmail (const char *Email)
{
return
DB_QuerySELECTCode ("can not get user's code",
"SELECT UsrCod"
" FROM usr_emails"
" WHERE E_mail='%s'", // TODO: Get only if email confirmed?
Email);
}
/*****************************************************************************/
/********** Get user's code from database using email and password ***********/
/*****************************************************************************/
long Usr_DB_GetUsrCodFromEmailPwd (const char *Email,const char *Password)
{
return
DB_QuerySELECTCode ("can not get user's code",
"SELECT usr_emails.UsrCod"
" FROM usr_emails,"
"usr_data"
" WHERE usr_emails.E_mail='%s'" // TODO: Get only if email confirmed?
" AND usr_emails.UsrCod=usr_data.UsrCod"
" AND usr_data.Password='%s'",
Email,
Password);
}
/*****************************************************************************/
/**************** Get user's code from database using ID *********************/
/*****************************************************************************/
long Usr_DB_GetUsrCodFromID (const char *ID)
{
return
DB_QuerySELECTCode ("can not get user's code",
"SELECT UsrCod"
" FROM usr_ids"
" WHERE UsrID='%s'", // TODO: Get only if ID confirmed?
ID);
}
/*****************************************************************************/
/*********** Get user's code from database using ID and password *************/
/*****************************************************************************/
long Usr_DB_GetUsrCodFromIDPwd (const char *ID,const char *Password)
{
return
DB_QuerySELECTCode ("can not get user's code",
"SELECT usr_ids.UsrCod"
" FROM usr_ids,"
"usr_data"
" WHERE usr_ids.UsrID='%s'" // TODO: Get only if ID confirmed?
" AND usr_ids.UsrCod=usr_data.UsrCod"
" AND usr_data.Password='%s'",
ID,
Password);
}
/*****************************************************************************/
/************ Get user's data from database giving a user's code *************/
/*****************************************************************************/
// UsrDat->UsrCod must contain an existing user's code
unsigned Usr_DB_GetUsrDataFromUsrCod (MYSQL_RES **mysql_res,long UsrCod,
Usr_GetPrefs_t GetPrefs)
{
switch (GetPrefs)
{
case Usr_DONT_GET_PREFS:
return (unsigned)
DB_QuerySELECT (mysql_res,"can not get user's data",
"SELECT EncryptedUsrCod," // row[ 0]
"Password," // row[ 1]
"Surname1," // row[ 2]
"Surname2," // row[ 3]
"FirstName," // row[ 4]
"Sex," // row[ 5]
"Photo," // row[ 6]
"PhotoVisibility," // row[ 7]
"BaPrfVisibility," // row[ 8]
"ExPrfVisibility," // row[ 9]
"CtyCod," // row[10]
"InsCtyCod," // row[11]
"InsCod," // row[12]
"DptCod," // row[13]
"CtrCod," // row[14]
"Office," // row[15]
"OfficePhone," // row[16]
"LocalPhone," // row[17]
"FamilyPhone," // row[18]
"DATE_FORMAT(Birthday,"
"'%%Y%%m%%d')," // row[19]
"Comments," // row[20]
"NotifNtfEvents," // row[21]
"EmailNtfEvents" // row[22]
" FROM usr_data"
" WHERE UsrCod=%ld",
UsrCod);
case Usr_GET_PREFS:
default:
return (unsigned)
DB_QuerySELECT (mysql_res,"can not get user's data",
"SELECT EncryptedUsrCod," // row[ 0]
"Password," // row[ 1]
"Surname1," // row[ 2]
"Surname2," // row[ 3]
"FirstName," // row[ 4]
"Sex," // row[ 5]
"Photo," // row[ 6]
"PhotoVisibility," // row[ 7]
"BaPrfVisibility," // row[ 8]
"ExPrfVisibility," // row[ 9]
"CtyCod," // row[10]
"InsCtyCod," // row[11]
"InsCod," // row[12]
"DptCod," // row[13]
"CtrCod," // row[14]
"Office," // row[15]
"OfficePhone," // row[16]
"LocalPhone," // row[17]
"FamilyPhone," // row[18]
"DATE_FORMAT(Birthday,"
"'%%Y%%m%%d')," // row[19]
"Comments," // row[20]
"NotifNtfEvents," // row[21]
"EmailNtfEvents," // row[22]
// Settings (usually not necessary
// when getting another user's data)
"Language," // row[23]
"FirstDayOfWeek," // row[24]
"DateFormat," // row[25]
"Theme," // row[26]
"IconSet," // row[27]
"Menu," // row[28]
"SideCols," // row[29]
"PhotoShape," // row[30]
"ThirdPartyCookies" // row[31]
" FROM usr_data"
" WHERE UsrCod=%ld",
UsrCod);
}
}
/*****************************************************************************/
/********** Get some user's data from database giving a user's code **********/
/*****************************************************************************/
// UsrDat->UsrCod must contain an existing user's code
unsigned Usr_DB_GetSomeUsrDataFromUsrCod (MYSQL_RES **mysql_res,long UsrCod)
{
return (unsigned)
DB_QuerySELECT (mysql_res,"can not get user's data",
"SELECT Surname1," // row[0]
"Surname2," // row[1]
"FirstName," // row[2]
"Photo," // row[3]
"DATE_FORMAT(Birthday,'%%Y%%m%%d')" // row[4]
" FROM usr_data"
" WHERE UsrCod=%ld",
UsrCod);
}
/*****************************************************************************/
/****** Check if a string is found in first name or surnames of anybody ******/
/*****************************************************************************/
bool Usr_DB_FindStrInUsrsNames (const char *Str)
{
return
DB_QueryEXISTS ("can not check if a string matches a first name or a surname",
"SELECT EXISTS"
"(SELECT *"
" FROM usr_data"
" WHERE FirstName='%s'"
" OR Surname1='%s'"
" OR Surname2='%s')",
Str,
Str,
Str);
}
/*****************************************************************************/
/*********** Get list of users with a given role in a given scope ************/
/*****************************************************************************/
// Role can be:
// - Rol_STD Student
// - Rol_NET Non-editing teacher
// - Rol_TCH Teacher
void Usr_DB_BuildQueryToGetUsrsLst (Hie_Level_t Level,Rol_Role_t Role,
char **Query)
{
const char *QueryFields =
"DISTINCT "
"usr_data.UsrCod," // row[ 0]
"usr_data.EncryptedUsrCod," // row[ 1]
"usr_data.Password," // row[ 2]
"usr_data.Surname1," // row[ 3]
"usr_data.Surname2," // row[ 4]
"usr_data.FirstName," // row[ 5]
"usr_data.Sex," // row[ 6]
"usr_data.Photo," // row[ 7]
"usr_data.PhotoVisibility," // row[ 8]
"usr_data.CtyCod," // row[ 9]
"usr_data.InsCod"; // row[10]
static const char *OrderBySubQuery =
" ORDER BY usr_data.Surname1,"
"usr_data.Surname2,"
"usr_data.FirstName,"
"usr_data.UsrCod";
/***** Build query *****/
switch (Level)
{
case Hie_SYS:
/* Get users in courses from the whole platform */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data,"
"crs_users"
" WHERE usr_data.UsrCod=crs_users.UsrCod"
" AND crs_users.Role=%u"
" %s",
QueryFields,
(unsigned) Role,
OrderBySubQuery);
break;
case Hie_CTY:
/* Get users in courses from the current country */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data,"
"crs_users,"
"crs_courses,"
"deg_degrees,"
"ctr_centers,"
"ins_instits"
" WHERE usr_data.UsrCod=crs_users.UsrCod"
" AND crs_users.Role=%u"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=ins_instits.InsCod"
" AND ins_instits.CtyCod=%ld"
" %s",
QueryFields,
(unsigned) Role,
Gbl.Hierarchy.Node[Hie_CTY].HieCod,
OrderBySubQuery);
break;
case Hie_INS:
/* Get users in courses from the current institution */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data,"
"crs_users,"
"crs_courses,"
"deg_degrees,"
"ctr_centers"
" WHERE usr_data.UsrCod=crs_users.UsrCod"
" AND crs_users.Role=%u"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=%ld"
" %s",
QueryFields,
(unsigned) Role,
Gbl.Hierarchy.Node[Hie_INS].HieCod,
OrderBySubQuery);
break;
case Hie_CTR:
/* Get users in courses from the current center */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data,"
"crs_users,"
"crs_courses,"
"deg_degrees"
" WHERE usr_data.UsrCod=crs_users.UsrCod"
" AND crs_users.Role=%u"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=%ld"
" ORDER BY usr_data.Surname1,"
"usr_data.Surname2,"
"usr_data.FirstName,"
"usr_data.UsrCod",
QueryFields,
(unsigned) Role,
Gbl.Hierarchy.Node[Hie_CTR].HieCod);
break;
case Hie_DEG:
/* Get users in courses from the current degree */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data,"
"crs_users,"
"crs_courses"
" WHERE usr_data.UsrCod=crs_users.UsrCod"
" AND crs_users.Role=%u"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=%ld"
" ORDER BY usr_data.Surname1,"
"usr_data.Surname2,"
"usr_data.FirstName,"
"usr_data.UsrCod",
QueryFields,
(unsigned) Role,
Gbl.Hierarchy.Node[Hie_DEG].HieCod);
break;
case Hie_CRS:
/* Get users from the current course */
Usr_DB_BuildQueryToGetUsrsLstCrs (Query,Role);
break;
default:
Err_WrongHierarchyLevelExit ();
break;
}
/*
if (Gbl.Usrs.Me.Roles.LoggedRole == Rol_SYS_ADM)
Lay_ShowAlert (Lay_INFO,Query);
*/
}
/*****************************************************************************/
/******* Build query to get list with data of users in current course ********/
/*****************************************************************************/
#define Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS (16 * 1024 - 1)
void Usr_DB_BuildQueryToGetUsrsLstCrs (char **Query,Rol_Role_t Role)
{
unsigned NumPositiveCods = 0;
unsigned NumNegativeCods = 0;
char LongStr[Cns_MAX_DECIMAL_DIGITS_LONG + 1];
unsigned NumGrpSel;
long GrpCod;
unsigned NumGrpTyp;
bool *AddStdsWithoutGroupOf;
const char *QueryFields =
"usr_data.UsrCod,"
"usr_data.EncryptedUsrCod,"
"usr_data.Password,"
"usr_data.Surname1,"
"usr_data.Surname2,"
"usr_data.FirstName,"
"usr_data.Sex,"
"usr_data.Photo,"
"usr_data.PhotoVisibility,"
"usr_data.CtyCod,"
"usr_data.InsCod,"
"crs_users.Role,"
"crs_users.Accepted";
/*
row[ 0]: usr_data.UsrCod
row[ 1]: usr_data.EncryptedUsrCod
row[ 2]: usr_data.Password (used to check if a teacher can edit user's data)
row[ 3]: usr_data.Surname1
row[ 4]: usr_data.Surname2
row[ 5]: usr_data.FirstName
row[ 6]: usr_data.Sex
row[ 7]: usr_data.Photo
row[ 8]: usr_data.PhotoVisibility
row[ 9]: usr_data.CtyCod
row[10]: usr_data.InsCod
row[11]: crs_users.Role (only if Scope == Hie_CRS)
row[12]: crs_users.Accepted (only if Scope == Hie_CRS)
*/
/***** If there are no groups selected, don't do anything *****/
if (!Gbl.Crs.Grps.AllGrps &&
!Gbl.Crs.Grps.LstGrpsSel.NumGrps)
{
*Query = NULL;
return;
}
/***** Allocate space for query *****/
if ((*Query = malloc (Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS + 1)) == NULL)
Err_NotEnoughMemoryExit ();
/***** Create query for users in the course *****/
if (Gbl.Action.Act == ActReqMsgUsr) // Selecting users to write a message
snprintf (*Query,Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS + 1,
"SELECT %s"
" FROM crs_users,"
"usr_data"
" WHERE crs_users.CrsCod=%ld"
" AND crs_users.Role=%u"
" AND crs_users.UsrCod NOT IN"
" (SELECT ToUsrCod"
" FROM msg_banned"
" WHERE FromUsrCod=%ld)"
" AND crs_users.UsrCod=usr_data.UsrCod", // Do not get banned users
QueryFields,
Gbl.Hierarchy.Node[Hie_CRS].HieCod,
(unsigned) Role,
Gbl.Usrs.Me.UsrDat.UsrCod);
else
snprintf (*Query,Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS + 1,
"SELECT %s"
" FROM crs_users,"
"usr_data"
" WHERE crs_users.CrsCod=%ld"
" AND crs_users.Role=%u"
" AND crs_users.UsrCod=usr_data.UsrCod",
QueryFields,
Gbl.Hierarchy.Node[Hie_CRS].HieCod,
(unsigned) Role);
/***** Select users in selected groups *****/
if (!Gbl.Crs.Grps.AllGrps)
{
/***** Get list of groups types in current course *****/
Grp_GetListGrpTypesInCurrentCrs (Grp_ONLY_GROUP_TYPES_WITH_GROUPS);
/***** Allocate memory for list of booleans AddStdsWithoutGroupOf *****/
if ((AddStdsWithoutGroupOf = calloc (Gbl.Crs.Grps.GrpTypes.NumGrpTypes,
sizeof (*AddStdsWithoutGroupOf))) == NULL)
Err_NotEnoughMemoryExit ();
/***** Initialize vector of booleans that indicates whether it's necessary add to the list
the students who don't belong to any group of each type *****/
for (NumGrpTyp = 0;
NumGrpTyp < Gbl.Crs.Grps.GrpTypes.NumGrpTypes;
NumGrpTyp++)
AddStdsWithoutGroupOf[NumGrpTyp] = false;
/***** Create query with the students who belong to the groups selected *****/
if (Gbl.Crs.Grps.LstGrpsSel.NumGrps) // If there are groups selected...
{
/* Check if there are positive and negative codes in the list */
for (NumGrpSel = 0;
NumGrpSel < Gbl.Crs.Grps.LstGrpsSel.NumGrps;
NumGrpSel++)
if ((GrpCod = Gbl.Crs.Grps.LstGrpsSel.GrpCods[NumGrpSel]) > 0)
NumPositiveCods++;
else
for (NumGrpTyp = 0;
NumGrpTyp < Gbl.Crs.Grps.GrpTypes.NumGrpTypes;
NumGrpTyp++)
if (Gbl.Crs.Grps.GrpTypes.LstGrpTypes[NumGrpTyp].GrpTypCod == -GrpCod)
{
AddStdsWithoutGroupOf[NumGrpTyp] = true;
break;
}
/* If there are positive codes, add the students who belong to groups with those codes */
if (NumPositiveCods)
{
Str_Concat (*Query," AND (crs_users.UsrCod IN"
" (SELECT DISTINCT "
"UsrCod"
" FROM grp_users"
" WHERE",
Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
NumPositiveCods = 0;
for (NumGrpSel = 0;
NumGrpSel < Gbl.Crs.Grps.LstGrpsSel.NumGrps;
NumGrpSel++)
if ((GrpCod = Gbl.Crs.Grps.LstGrpsSel.GrpCods[NumGrpSel]) > 0)
{
Str_Concat (*Query,NumPositiveCods ? " OR GrpCod='" :
" GrpCod='",
Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
snprintf (LongStr,sizeof (LongStr),"%ld",GrpCod);
Str_Concat (*Query,LongStr,Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
Str_Concat (*Query,"'",Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
NumPositiveCods++;
}
Str_Concat (*Query,")",Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
}
}
/***** Create a query with the students who don't belong to any group *****/
for (NumGrpTyp = 0;
NumGrpTyp < Gbl.Crs.Grps.GrpTypes.NumGrpTypes;
NumGrpTyp++)
if (AddStdsWithoutGroupOf[NumGrpTyp])
{
if (NumPositiveCods || NumNegativeCods)
Str_Concat (*Query," OR ",Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
else
Str_Concat (*Query," AND (",Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
/* Select all students of the course who don't belong to any group of type GrpTypCod */
Str_Concat (*Query,"crs_users.UsrCod NOT IN"
" (SELECT DISTINCT "
"grp_users.UsrCod"
" FROM grp_groups,"
"grp_users"
" WHERE grp_groups.GrpTypCod='",
Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
snprintf (LongStr,sizeof (LongStr),"%ld",
Gbl.Crs.Grps.GrpTypes.LstGrpTypes[NumGrpTyp].GrpTypCod);
Str_Concat (*Query,LongStr,Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
Str_Concat (*Query,"' AND grp_groups.GrpCod=grp_users.GrpCod)",
Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
NumNegativeCods++;
}
if (NumPositiveCods ||
NumNegativeCods)
Str_Concat (*Query,")",Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
/***** Free memory used by the list of booleans AddStdsWithoutGroupOf *****/
free (AddStdsWithoutGroupOf);
/***** Free list of groups types in current course *****/
Grp_FreeListGrpTypesAndGrps ();
}
/***** The last part of the query is for ordering the list *****/
Str_Concat (*Query," ORDER BY usr_data.Surname1,"
"usr_data.Surname2,"
"usr_data.FirstName,"
"usr_data.UsrCod",
Usr_DB_MAX_BYTES_QUERY_GET_LIST_USRS);
}
/*****************************************************************************/
/************ Build query to get list with data of administrators ************/
/*****************************************************************************/
void Usr_DB_BuildQueryToGetAdmsLst (Hie_Level_t Level,char **Query)
{
static const char *QueryFields =
"UsrCod," // row[ 0]
"EncryptedUsrCod," // row[ 1]
"Password," // row[ 2]
"Surname1," // row[ 3]
"Surname2," // row[ 4]
"FirstName," // row[ 5]
"Sex," // row[ 6]
"Photo," // row[ 7]
"PhotoVisibility," // row[ 8]
"CtyCod," // row[ 9]
"InsCod"; // row[10]
static const char *OrderBySubQuery =
" ORDER BY Surname1,"
"Surname2,"
"FirstName,"
"UsrCod";
/***** Build query *****/
// Important: it is better to use:
// SELECT... WHERE UsrCod IN (SELECT...) OR UsrCod IN (SELECT...) <-- fast
// instead of using or with different joins:
// SELECT... WHERE (...) OR (...) <-- very slow
switch (Level)
{
case Hie_SYS: // All admins
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data"
" WHERE UsrCod IN "
"(SELECT DISTINCT "
"UsrCod"
" FROM usr_admins)"
"%s",
QueryFields,
OrderBySubQuery);
break;
case Hie_CTY: // System admins
// and admins of the institutions, centers and degrees in the current country
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data"
" WHERE UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s')"
" OR UsrCod IN "
"(SELECT usr_admins.UsrCod"
" FROM usr_admins,"
"ins_instits"
" WHERE usr_admins.Scope='%s'"
" AND usr_admins.Cod=ins_instits.InsCod"
" AND ins_instits.CtyCod=%ld)"
" OR UsrCod IN "
"(SELECT usr_admins.UsrCod"
" FROM usr_admins,"
"ctr_centers,"
"ins_instits"
" WHERE usr_admins.Scope='%s'"
" AND usr_admins.Cod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=ins_instits.InsCod"
" AND ins_instits.CtyCod=%ld)"
" OR UsrCod IN "
"(SELECT usr_admins.UsrCod"
" FROM usr_admins,"
"deg_degrees,"
"ctr_centers,"
"ins_instits"
" WHERE usr_admins.Scope='%s'"
" AND usr_admins.Cod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=ins_instits.InsCod"
" AND ins_instits.CtyCod=%ld)"
" %s",
QueryFields,
Hie_GetDBStrFromLevel (Hie_SYS),
Hie_GetDBStrFromLevel (Hie_INS),Gbl.Hierarchy.Node[Hie_CTY].HieCod,
Hie_GetDBStrFromLevel (Hie_CTR),Gbl.Hierarchy.Node[Hie_CTY].HieCod,
Hie_GetDBStrFromLevel (Hie_DEG),Gbl.Hierarchy.Node[Hie_CTY].HieCod,
OrderBySubQuery);
break;
case Hie_INS: // System admins,
// admins of the current institution,
// and admins of the centers and degrees in the current institution
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data"
" WHERE UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s')"
" OR UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s'"
" AND Cod=%ld)"
" OR UsrCod IN "
"(SELECT usr_admins.UsrCod"
" FROM usr_admins,"
"ctr_centers"
" WHERE usr_admins.Scope='%s'"
" AND usr_admins.Cod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=%ld)"
" OR UsrCod IN "
"(SELECT usr_admins.UsrCod"
" FROM usr_admins,"
"deg_degrees,"
"ctr_centers"
" WHERE usr_admins.Scope='%s'"
" AND usr_admins.Cod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=%ld)"
"%s",
QueryFields,
Hie_GetDBStrFromLevel (Hie_SYS),
Hie_GetDBStrFromLevel (Hie_INS),Gbl.Hierarchy.Node[Hie_INS].HieCod,
Hie_GetDBStrFromLevel (Hie_CTR),Gbl.Hierarchy.Node[Hie_INS].HieCod,
Hie_GetDBStrFromLevel (Hie_DEG),Gbl.Hierarchy.Node[Hie_INS].HieCod,
OrderBySubQuery);
break;
case Hie_CTR: // System admins,
// admins of the current institution,
// admins and the current center,
// and admins of the degrees in the current center
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data"
" WHERE UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s')"
" OR UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s'"
" AND Cod=%ld)"
" OR UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s'"
" AND Cod=%ld)"
" OR UsrCod IN "
"(SELECT usr_admins.UsrCod"
" FROM usr_admins,"
"deg_degrees"
" WHERE usr_admins.Scope='%s'"
" AND usr_admins.Cod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=%ld)"
"%s",
QueryFields,
Hie_GetDBStrFromLevel (Hie_SYS),
Hie_GetDBStrFromLevel (Hie_INS),Gbl.Hierarchy.Node[Hie_INS].HieCod,
Hie_GetDBStrFromLevel (Hie_CTR),Gbl.Hierarchy.Node[Hie_CTR].HieCod,
Hie_GetDBStrFromLevel (Hie_DEG),Gbl.Hierarchy.Node[Hie_CTR].HieCod,
OrderBySubQuery);
break;
case Hie_DEG: // System admins
// and admins of the current institution, center or degree
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data"
" WHERE UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s')"
" OR UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s'"
" AND Cod=%ld)"
" OR UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s'"
" AND Cod=%ld)"
" OR UsrCod IN "
"(SELECT UsrCod"
" FROM usr_admins"
" WHERE Scope='%s'"
" AND Cod=%ld)"
"%s",
QueryFields,
Hie_GetDBStrFromLevel (Hie_SYS),
Hie_GetDBStrFromLevel (Hie_INS),Gbl.Hierarchy.Node[Hie_INS].HieCod,
Hie_GetDBStrFromLevel (Hie_CTR),Gbl.Hierarchy.Node[Hie_CTR].HieCod,
Hie_GetDBStrFromLevel (Hie_DEG),Gbl.Hierarchy.Node[Hie_DEG].HieCod,
OrderBySubQuery);
break;
default: // not aplicable
Err_WrongHierarchyLevelExit ();
break;
}
}
/*****************************************************************************/
/************************ Get list with data of guests ***********************/
/*****************************************************************************/
void Usr_DB_BuildQueryToGetGstsLst (Hie_Level_t Level,char **Query)
{
static const char *QueryFields =
"UsrCod," // row[ 0]
"EncryptedUsrCod," // row[ 1]
"Password," // row[ 2]
"Surname1," // row[ 3]
"Surname2," // row[ 4]
"FirstName," // row[ 5]
"Sex," // row[ 6]
"Photo," // row[ 7]
"PhotoVisibility," // row[ 8]
"CtyCod," // row[ 9]
"InsCod"; // row[10]
static const char *OrderBySubQuery =
" ORDER BY Surname1,"
"Surname2,"
"FirstName,"
"UsrCod";
/***** Build query *****/
switch (Level)
{
case Hie_SYS:
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data"
" WHERE UsrCod NOT IN"
" (SELECT UsrCod"
" FROM crs_users)"
"%s",
QueryFields,
OrderBySubQuery);
break;
case Hie_CTY:
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data"
" WHERE (CtyCod=%ld"
" OR"
" InsCtyCod=%ld)"
" AND UsrCod NOT IN"
" (SELECT UsrCod"
" FROM crs_users)"
"%s",
QueryFields,
Gbl.Hierarchy.Node[Hie_CTY].HieCod,
Gbl.Hierarchy.Node[Hie_CTY].HieCod,
OrderBySubQuery);
break;
case Hie_INS:
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data"
" WHERE InsCod=%ld"
" AND UsrCod NOT IN"
" (SELECT UsrCod"
" FROM crs_users)"
"%s",
QueryFields,
Gbl.Hierarchy.Node[Hie_INS].HieCod,
OrderBySubQuery);
break;
case Hie_CTR:
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_data"
" WHERE CtrCod=%ld"
" AND UsrCod NOT IN"
" (SELECT UsrCod"
" FROM crs_users)"
"%s",
QueryFields,
Gbl.Hierarchy.Node[Hie_CTR].HieCod,
OrderBySubQuery);
break;
default: // not aplicable
Err_WrongHierarchyLevelExit ();
break; // Not reached
}
}
/*****************************************************************************/
/*********** Search for users with a given role in current scope *************/
/*****************************************************************************/
void Usr_DB_BuildQueryToSearchListUsrs (Rol_Role_t Role,char **Query)
{
char SubQueryRole[64];
static const char *QueryFields =
"DISTINCT "
"usr_data.UsrCod," // row[ 0]
"usr_data.EncryptedUsrCod," // row[ 1]
"usr_data.Password," // row[ 2]
"usr_data.Surname1," // row[ 3]
"usr_data.Surname2," // row[ 4]
"usr_data.FirstName," // row[ 5]
"usr_data.Sex," // row[ 6]
"usr_data.Photo," // row[ 7]
"usr_data.PhotoVisibility," // row[ 8]
"usr_data.CtyCod," // row[ 9]
"usr_data.InsCod"; // row[10]
static const char *OrderBySubQuery =
"usr_candidate_users.UsrCod=usr_data.UsrCod"
" ORDER BY usr_data.Surname1,"
"usr_data.Surname2,"
"usr_data.FirstName,"
"usr_data.UsrCod";
/***** Build query *****/
// if Gbl.Scope.Current is course ==> 3 columns are retrieved: UsrCod, Sex, Accepted
// else ==> 2 columns are retrieved: UsrCod, Sex
// Search is faster (aproximately x2) using a temporary table to store users found in the whole platform
switch (Role)
{
case Rol_UNK: // Here Rol_UNK means any rol (role does not matter)
switch (Gbl.Scope.Current)
{
case Hie_SYS:
/* Search users from the whole platform */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,usr_data"
" WHERE %s",
QueryFields,
OrderBySubQuery);
break;
case Hie_CTY:
/* Search users in courses from the current country */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"crs_users,"
"crs_courses,"
"deg_degrees,"
"ctr_centers,"
"ins_instits,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=ins_instits.InsCod"
" AND ins_instits.CtyCod=%ld"
" AND %s",
QueryFields,
Gbl.Hierarchy.Node[Hie_CTY].HieCod,
OrderBySubQuery);
break;
case Hie_INS:
/* Search users in courses from the current institution */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"crs_users,"
"crs_courses,"
"deg_degrees,"
"ctr_centers,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=%ld"
" AND %s",
QueryFields,
Gbl.Hierarchy.Node[Hie_INS].HieCod,
OrderBySubQuery);
break;
case Hie_CTR:
/* Search users in courses from the current center */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"crs_users,"
"crs_courses,"
"deg_degrees,"
" usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=%ld"
" AND %s",
QueryFields,
Gbl.Hierarchy.Node[Hie_CTR].HieCod,
OrderBySubQuery);
break;
case Hie_DEG:
/* Search users in courses from the current degree */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"crs_users,"
"crs_courses,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=%ld"
" AND %s",
QueryFields,
Gbl.Hierarchy.Node[Hie_DEG].HieCod,
OrderBySubQuery);
break;
case Hie_CRS:
/* Search users in courses from the current course */
DB_BuildQuery (Query,
"SELECT %s,"
"crs_users.Role," // row[11]
"crs_users.Accepted" // row[12]
" FROM usr_candidate_users,"
"crs_users,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
" AND crs_users.CrsCod=%ld"
" AND %s",
QueryFields,
Gbl.Hierarchy.Node[Hie_CRS].HieCod,
OrderBySubQuery);
break;
default:
Err_WrongHierarchyLevelExit ();
break;
}
break;
case Rol_GST: // Guests (scope is not used)
/* Search users with no courses */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"usr_data"
" WHERE usr_candidate_users.UsrCod NOT IN"
" (SELECT UsrCod"
" FROM crs_users)"
" AND %s",
QueryFields,
OrderBySubQuery);
break;
case Rol_STD: // Student
case Rol_NET: // Non-editing teacher
case Rol_TCH: // Teacher
/*
To achieve maximum speed, it's important to do the things in this order:
1) Search for user's name (UsrQuery) getting candidate users
2) Filter the candidate users according to scope
*/
switch (Role)
{
case Rol_STD: // Student
sprintf (SubQueryRole," AND crs_users.Role=%u",
(unsigned) Rol_STD);
break;
case Rol_NET: // Non-editing teacher
case Rol_TCH: // or teacher
sprintf (SubQueryRole," AND crs_users.Role IN (%u,%u)",
(unsigned) Rol_NET,
(unsigned) Rol_TCH);
break;
default:
SubQueryRole[0] = '\0';
break;
}
switch (Gbl.Scope.Current)
{
case Hie_SYS:
/* Search users in courses from the whole platform */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"crs_users,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
"%s"
" AND %s",
QueryFields,
SubQueryRole,
OrderBySubQuery);
break;
case Hie_CTY:
/* Search users in courses from the current country */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"crs_users,"
"crs_courses,"
"deg_degrees,"
"ctr_centers,"
"ins_instits,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
"%s"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=ins_instits.InsCod"
" AND ins_instits.CtyCod=%ld"
" AND %s",
QueryFields,
SubQueryRole,
Gbl.Hierarchy.Node[Hie_CTY].HieCod,
OrderBySubQuery);
break;
case Hie_INS:
/* Search users in courses from the current institution */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"crs_users,"
"crs_courses,"
"deg_degrees,"
"ctr_centers,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
"%s"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=ctr_centers.CtrCod"
" AND ctr_centers.InsCod=%ld"
" AND %s",
QueryFields,
SubQueryRole,
Gbl.Hierarchy.Node[Hie_INS].HieCod,
OrderBySubQuery);
break;
case Hie_CTR:
/* Search users in courses from the current center */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"crs_users,"
"crs_courses,"
"deg_degrees,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
"%s"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=deg_degrees.DegCod"
" AND deg_degrees.CtrCod=%ld"
" AND %s",
QueryFields,
SubQueryRole,
Gbl.Hierarchy.Node[Hie_CTR].HieCod,
OrderBySubQuery);
break;
case Hie_DEG:
/* Search users in courses from the current degree */
DB_BuildQuery (Query,
"SELECT %s"
" FROM usr_candidate_users,"
"crs_users,"
"crs_courses,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
"%s"
" AND crs_users.CrsCod=crs_courses.CrsCod"
" AND crs_courses.DegCod=%ld"
" AND %s",
QueryFields,
SubQueryRole,
Gbl.Hierarchy.Node[Hie_DEG].HieCod,
OrderBySubQuery);
break;
case Hie_CRS:
/* Search users in courses from the current course */
DB_BuildQuery (Query,
"SELECT %s,"
"crs_users.Role,"
"crs_users.Accepted"
" FROM usr_candidate_users,"
"crs_users,"
"usr_data"
" WHERE usr_candidate_users.UsrCod=crs_users.UsrCod"
"%s"
" AND crs_users.CrsCod=%ld"
" AND %s",
QueryFields,
SubQueryRole,
Gbl.Hierarchy.Node[Hie_CRS].HieCod,
OrderBySubQuery);
break;
default:
Err_WrongHierarchyLevelExit ();
break;
}
break;
default:
Err_WrongRoleExit ();
break;
}
// if (Gbl.Usrs.Me.Roles.LoggedRole == Rol_SYS_ADM)
// Lay_ShowAlert (Lay_INFO,Query);
}
/*****************************************************************************/
/****** Build query to get the user's codes of all students of a degree ******/
/*****************************************************************************/
void Usr_DB_BuildQueryToGetUnorderedStdsCodesInDeg (long DegCod,char **Query)
{
DB_BuildQuery (Query,
"SELECT DISTINCT "
"usr_data.UsrCod," // row[ 0]
"usr_data.EncryptedUsrCod," // row[ 1]
"usr_data.Password," // row[ 2]
"usr_data.Surname1," // row[ 3]
"usr_data.Surname2," // row[ 4]
"usr_data.FirstName," // row[ 5]
"usr_data.Sex," // row[ 6]
"usr_data.Photo," // row[ 7]
"usr_data.PhotoVisibility," // row[ 8]
"usr_data.CtyCod," // row[ 9]
"usr_data.InsCod" // row[10]
" FROM crs_courses,"
"crs_users,"
"usr_data"
" WHERE crs_courses.DegCod=%ld"
" AND crs_courses.CrsCod=crs_users.CrsCod"
" AND crs_users.Role=%u"
" AND crs_users.UsrCod=usr_data.UsrCod",
DegCod,
(unsigned) Rol_STD);
}
/*****************************************************************************/
/************** Get number of users who have chosen an option ****************/
/*****************************************************************************/
unsigned Usr_DB_GetNumUsrsWhoChoseAnOption (const char *SubQuery)
{
switch (Gbl.Scope.Current)
{
case Hie_SYS:
return (unsigned)
DB_QueryCOUNT ("can not get the number of users who have chosen an option",
"SELECT COUNT(*)"
" FROM usr_data"
" WHERE %s",
SubQuery);
case Hie_CTY:
return (unsigned)
DB_QueryCOUNT ("can not get the number of users who have chosen an option",
"SELECT COUNT(DISTINCT usr_data.UsrCod)"
" FROM ins_instits,"
"ctr_centers,"
"deg_degrees,"
"crs_courses,"
"crs_users,"
"usr_data"
" WHERE ins_instits.CtyCod=%ld"
" AND ins_instits.InsCod=ctr_centers.InsCod"
" AND ctr_centers.CtrCod=deg_degrees.CtrCod"
" AND deg_degrees.DegCod=crs_courses.DegCod"
" AND crs_courses.CrsCod=crs_users.CrsCod"
" AND crs_users.UsrCod=usr_data.UsrCod"
" AND %s",
Gbl.Hierarchy.Node[Hie_CTY].HieCod,SubQuery);
case Hie_INS:
return (unsigned)
DB_QueryCOUNT ("can not get the number of users who have chosen an option",
"SELECT COUNT(DISTINCT usr_data.UsrCod)"
" FROM ctr_centers,"
"deg_degrees,"
"crs_courses,"
"crs_users,"
"usr_data"
" WHERE ctr_centers.InsCod=%ld"
" AND ctr_centers.CtrCod=deg_degrees.CtrCod"
" AND deg_degrees.DegCod=crs_courses.DegCod"
" AND crs_courses.CrsCod=crs_users.CrsCod"
" AND crs_users.UsrCod=usr_data.UsrCod"
" AND %s",
Gbl.Hierarchy.Node[Hie_INS].HieCod,SubQuery);
case Hie_CTR:
return (unsigned)
DB_QueryCOUNT ("can not get the number of users who have chosen an option",
"SELECT COUNT(DISTINCT usr_data.UsrCod)"
" FROM deg_degrees,"
"crs_courses,"
"crs_users,"
"usr_data"
" WHERE deg_degrees.CtrCod=%ld"
" AND deg_degrees.DegCod=crs_courses.DegCod"
" AND crs_courses.CrsCod=crs_users.CrsCod"
" AND crs_users.UsrCod=usr_data.UsrCod"
" AND %s",
Gbl.Hierarchy.Node[Hie_CTR].HieCod,SubQuery);
case Hie_DEG:
return (unsigned)
DB_QueryCOUNT ("can not get the number of users who have chosen an option",
"SELECT COUNT(DISTINCT usr_data.UsrCod)"
" FROM crs_courses,"
"crs_users,"
"usr_data"
" WHERE crs_courses.DegCod=%ld"
" AND crs_courses.CrsCod=crs_users.CrsCod"
" AND crs_users.UsrCod=usr_data.UsrCod"
" AND %s",
Gbl.Hierarchy.Node[Hie_DEG].HieCod,SubQuery);
case Hie_CRS:
return (unsigned)
DB_QueryCOUNT ("can not get the number of users who have chosen an option",
"SELECT COUNT(DISTINCT usr_data.UsrCod)"
" FROM crs_users,"
"usr_data"
" WHERE crs_users.CrsCod=%ld"
" AND crs_users.UsrCod=usr_data.UsrCod"
" AND %s",
Gbl.Hierarchy.Node[Hie_CRS].HieCod,SubQuery);
default:
Err_WrongHierarchyLevelExit ();
return 0; // Not reached
}
}
/*****************************************************************************/
/************************* Get old users from database ***********************/
/*****************************************************************************/
unsigned Usr_DB_GetOldUsrs (MYSQL_RES **mysql_res,time_t SecondsWithoutAccess)
{
return (unsigned)
DB_QuerySELECT (mysql_res,"can not get old users",
"SELECT UsrCod"
" FROM (SELECT UsrCod"
" FROM usr_last"
" WHERE LastTime<FROM_UNIXTIME(UNIX_TIMESTAMP()-%llu)"
" UNION "
"SELECT UsrCod"
" FROM usr_data"
" WHERE UsrCod NOT IN"
" (SELECT UsrCod"
" FROM usr_last)"
") AS candidate_usrs"
" WHERE UsrCod NOT IN"
" (SELECT DISTINCT "
"UsrCod"
" FROM crs_users)",
(unsigned long long) SecondsWithoutAccess);
}
/*****************************************************************************/
/*************** Create temporary table with candidate users *****************/
/*****************************************************************************/
void Usr_DB_CreateTmpTableAndSearchCandidateUsrs (const char SearchQuery[Sch_MAX_BYTES_SEARCH_QUERY + 1])
{
/***** Create temporary table with candidate users *****/
/*
- Search is faster (aproximately x2) using temporary tables.
- Searching for names is made in the whole platform
and stored in this table.
*/
DB_CreateTmpTable ("CREATE TEMPORARY TABLE usr_candidate_users"
" (UsrCod INT NOT NULL,UNIQUE INDEX(UsrCod)) ENGINE=MEMORY"
" SELECT UsrCod"
" FROM usr_data"
" WHERE %s",
SearchQuery);
}
/*****************************************************************************/
/***************** Drop temporary table with candidate users *****************/
/*****************************************************************************/
void Usr_DB_DropTmpTableWithCandidateUsrs (void)
{
DB_DropTmpTable ("usr_candidate_users");
}
/*****************************************************************************/
/****************************** Remove user's data ***************************/
/*****************************************************************************/
void Usr_DB_RemoveUsrData (long UsrCod)
{
/***** Remove user's data *****/
DB_QueryDELETE ("can not remove user's data",
"DELETE FROM usr_data"
" WHERE UsrCod=%ld",
UsrCod);
}
/*****************************************************************************/
/*************** Create new entry for my last data in database ***************/
/*****************************************************************************/
void Usr_DB_InsertMyLastData (void)
{
/***** Insert my last accessed course, tab and time of click in database *****/
DB_QueryINSERT ("can not insert last user's data",
"INSERT INTO usr_last"
" (UsrCod,WhatToSearch,"
"LastSco,LastCod,LastAct,LastRole,LastTime,LastAccNotif)"
" VALUES"
" (%ld,%u,"
"'%s',%ld,%ld,%u,NOW(),FROM_UNIXTIME(%ld))",
Gbl.Usrs.Me.UsrDat.UsrCod,
(unsigned) Sch_SEARCH_ALL,
Hie_GetDBStrFromLevel (Gbl.Hierarchy.Level),
Gbl.Hierarchy.Node[Gbl.Hierarchy.Level].HieCod,
Act_GetActCod (Gbl.Action.Act),
(unsigned) Gbl.Usrs.Me.Role.Logged,
(long) (time_t) 0); // The user never accessed to notifications
}
/*****************************************************************************/
/********* Update my last accessed course, tab and time in database **********/
/*****************************************************************************/
void Usr_DB_UpdateMyLastData (void)
{
/***** Update my last accessed course, tab and time of click in database *****/
// WhatToSearch, LastAccNotif remain unchanged
DB_QueryUPDATE ("can not update last user's data",
"UPDATE usr_last"
" SET LastSco='%s',"
"LastCod=%ld,"
"LastAct=%ld,"
"LastRole=%u,"
"LastTime=NOW()"
" WHERE UsrCod=%ld",
Hie_GetDBStrFromLevel (Gbl.Hierarchy.Level),
Gbl.Hierarchy.Node[Gbl.Hierarchy.Level].HieCod,
Act_GetActCod (Gbl.Action.Act),
(unsigned) Gbl.Usrs.Me.Role.Logged,
Gbl.Usrs.Me.UsrDat.UsrCod);
}
/*****************************************************************************/
/********************** Update my last type of search ************************/
/*****************************************************************************/
void Usr_DB_UpdateMyLastWhatToSearch (Sch_WhatToSearch_t WhatToSearch)
{
// WhatToSearch is stored in usr_last for next time I log in
// In other existing sessions distinct to this, WhatToSearch will remain unchanged
DB_QueryUPDATE ("can not update type of search in user's last data",
"UPDATE usr_last"
" SET WhatToSearch=%u"
" WHERE UsrCod=%ld",
(unsigned) WhatToSearch,
Gbl.Usrs.Me.UsrDat.UsrCod);
}
/*****************************************************************************/
/************** Check if it exists an entry for me in last data **************/
/*****************************************************************************/
bool Usr_DB_CheckMyLastData (void)
{
return
DB_QueryEXISTS ("can not check last user's data",
"SELECT EXISTS"
"(SELECT *"
" FROM usr_last"
" WHERE UsrCod=%ld)",
Gbl.Usrs.Me.UsrDat.UsrCod);
}
/*****************************************************************************/
/********** Get user's last data from database giving a user's code **********/
/*****************************************************************************/
unsigned Usr_DB_GetMyLastData (MYSQL_RES **mysql_res)
{
return (unsigned)
DB_QuerySELECT (mysql_res,"can not get user's last data",
"SELECT WhatToSearch," // row[0]
"LastSco," // row[1]
"LastCod," // row[2]
"LastAct," // row[3]
"LastRole," // row[4]
"UNIX_TIMESTAMP(LastTime)," // row[5]
"UNIX_TIMESTAMP(LastAccNotif)" // row[6]
" FROM usr_last"
" WHERE UsrCod=%ld",
Gbl.Usrs.Me.UsrDat.UsrCod);
}
/*****************************************************************************/
/*************************** Remove user's last data *************************/
/*****************************************************************************/
void Usr_DB_RemoveUsrLastData (long UsrCod)
{
DB_QueryDELETE ("can not remove user's last data",
"DELETE FROM usr_last"
" WHERE UsrCod=%ld",
UsrCod);
}
/*****************************************************************************/
/*** Insert my user's code in the table of birthdays already congratulated ***/
/*****************************************************************************/
void Usr_DB_MarkMyBirthdayAsCongratulated (void)
{
DB_QueryINSERT ("can not insert birthday",
"INSERT INTO usr_birthdays_today"
" (UsrCod,Today)"
" VALUES"
" (%ld,CURDATE())",
Gbl.Usrs.Me.UsrDat.UsrCod);
}
/*****************************************************************************/
/*************** Check if my birthday is already congratulated ***************/
/*****************************************************************************/
bool Usr_DB_CheckIfMyBirthdayHasNotBeenCongratulated (void)
{
return
DB_QueryEXISTS ("can not check if my birthday has been congratulated",
"SELECT EXISTS"
"(SELECT *"
" FROM usr_birthdays_today"
" WHERE UsrCod=%ld)",
Gbl.Usrs.Me.UsrDat.UsrCod);
}
/*****************************************************************************/
/****************************** Delete old birthdays *************************/
/*****************************************************************************/
void Usr_DB_DeleteOldBirthdays (void)
{
DB_QueryDELETE ("can not delete old birthdays",
"DELETE FROM usr_birthdays_today"
" WHERE Today<>CURDATE()");
}
Reference in New Issue View Git Blame Copy Permalink