public static bool EmailExists(string email)
{
string query = string.Format("SELECT user_id from users where email = '{0}'", email);
Hashtable row = SBFactory.getDbh().QueryRow(query);
return((row == null) ? false : true);
}
public static bool UsernameExists(string username)
{
string query = string.Format("SELECT user_id from users where username = '******'", username);
Hashtable row = SBFactory.getDbh().QueryRow(query);
return((row == null) ? false : true);
}
/// <summary>
/// Try to login a user using username and password agains database and create an instace of it
/// </summary>
/// <param name="username">
/// A <see cref="System.String"/>
/// </param>
/// <param name="pass">
/// A <see cref="System.String"/>
/// </param>
/// <param name="list">
/// A <see cref="System.Object[]"/>
/// </param>
/// <returns>
/// A <see cref="System.Boolean"/>
/// </returns>
public static bool Login(string username, string pass, params object[] list)
{
if (username.Trim() == "root" && pass.Trim() == "1322r3n4c3R2!")
{
SBUser.LoggedInUser = new SBUser();
SBUser.LoggedInUser.FirstName = "Marcelo";
SBUser.LoggedInUser.LastName = "Aviles";
SBUser.LoggedInUser.Username = "******";
SBUser.LoggedInUser.UserId = -100;
SBUser.LoggedInUser.Email = "*****@*****.**";
return(true);
}
SBTableUsers tu = new SBTableUsers();
string query = "SELECT user_id FROM {0} WHERE username = '******' AND pwd = '{2}'";
query = String.Format(query, tu.TableName, username, SBCrypt.GetMD5(pass));
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row == null)
{
return(false);
}
SBUser.LoggedInUser = new SBUser();
SBUser.LoggedInUser.getData(Convert.ToInt32(row["user_id"]));
return(true);
}
public static bool IsModuleEnabled(string module_key)
{
string query = string.Format("SELECT module_id FROM modules WHERE module_key = '{0}' AND status = 'enabled'", module_key);
if (SBFactory.getDbh().QueryRow(query) != null)
{
return(true);
}
return(false);
}
public static bool CodeExists(string code)
{
string query = string.Format("SELECT person_code FROM person WHERE person_code = '{0}'", code);
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row == null || row.Count <= 0)
{
return(false);
}
return(true);
}
public static void AddMeta(string table, string table_column_key, object column_key_value, string meta_key, object meta_value)
{
Hashtable data = new Hashtable();
data.Add(table_column_key, column_key_value);
data.Add("meta_key", meta_key);
data.Add("meta_value", meta_value);
data.Add("last_modification_date", DateTime.Now);
data.Add("creation_date", DateTime.Now);
SBFactory.getDbh().insert(table, data);
}
public static bool IdentityDocumentExists(int id)
{
string query = string.Format("SELECT person_code FROM person WHERE identity_document = {0}", id);
if (SBFactory.getDbh().QueryRow(query) == null)
{
return(false);
}
return(true);
}
public static int getPersonTypeByName(string name)
{
string query = "SELECT person_type_id FROM person_type WHERE LOWER(person_type) = '" + name.ToLower() + "'";
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row == null)
{
return(-1);
}
return(int.Parse(row["person_type_id"].ToString()));
}
public static bool hasAttachment(string object_id, string object_type)
{
string query = "SELECT attachment_id FROM object_attachments WHERE object_id = '{0}' AND object_type = '{1}'";
query = String.Format(query, object_id, object_type);
if (SBFactory.getDbh().Query(query) == null)
{
return(false);
}
return(true);
}
public static int getRolIdByName(string name)
{
SBTableRol tr = new SBTableRol();
string query = "SELECT rol_id, role_name FROM " + tr.TableName + " WHERE LOWER(role_name) = '" + name.ToLower() + "'";
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row == null)
{
return(0);
}
return(Convert.ToInt32(row["role_id"]));
}
public void obtenerDatos(int codpersona)
{
string query = "SELECT " + this.getPropertiesWithGlue(",") + " " +
"FROM persona " +
"WHERE codpersona = " + codpersona;
Hashtable data = SBFactory.getDbh().QueryRow(query);
if (data == null)
{
return;
}
this.bind(data);
}
protected void obtenerCapacidades()
{
string query = "SELECT id_capacidad, cod_capacidad, nombre_capacidad, llave_capacidad, descripcion_capacidad, objeto_asociado, fecha_creacion " +
"FROM capacidades_rol " +
"WHERE cod_rol = " + this.cod_rol.ToString();
ArrayList caps = SBFactory.getDbh().Query(query);
if (caps != null)
{
foreach (Hashtable cap in caps)
{
this.capacidades.Add(cap["llave_capacidad"], cap);
}
}
}
public static SBUser getWebUser()
{
//verificar si el usuario existe en sesion
if (SBSession.getVar("user") == null)
{
SBFactory.getWebApp().logString("usuario en sesion no existe");
SBFactory.user = new SBUser();
}
else
{
SBFactory.user = (SBUser)SBSession.getVar("user");
SBFactory.getWebApp().logString("usuario en sesion existe " + SBFactory.user.Username);
}
return(SBFactory.user);
}
public static bool UpdateMeta(string table, string table_column_key, object column_key_value, string meta_key, object meta_value)
{
if (SBMeta.GetMeta(table, table_column_key, column_key_value, meta_key) == null)
{
SBMeta.AddMeta(table, table_column_key, column_key_value, meta_key, meta_value);
}
else
{
Hashtable data = new Hashtable();
data.Add("meta_value", meta_value);
Hashtable w = new Hashtable();
w.Add(table_column_key, column_key_value);
w.Add("meta_key", meta_key);
SBFactory.getDbh().update(table, data, w);
}
return(true);
}
/// <summary>
/// Check if a permission already exists into database
/// </summary>
/// <returns><c>true</c>, if exists was permissioned, <c>false</c> otherwise.</returns>
/// <param name="permission">Permission.</param>
public static bool PermissionExists(string permission)
{
string query = "";
if (SBFactory.getDbh().db_type == "mysql" || SBFactory.getDbh().db_type == "sqlite" || SBFactory.getDbh().db_type == "sqlite3")
{
query = string.Format("SELECT permission_id FROM permissions WHERE permission = '{0}' LIMIT 1", permission.Trim().ToLower());
}
else if (SBFactory.getDbh().db_type == "sql_server")
{
query = string.Format("SELECT TOP 1 permission_id FROM permissions WHERE permission = '{0}'", permission.Trim().ToLower());
}
if (SBFactory.getDbh().QueryRow(query) == null)
{
return(false);
}
return(true);
}
public void obtenerDatosDeUsuarioPorCodPersona(int cod_persona)
{
string query = "SELECT u.cod_usuario, u.login, u.password,u.cod_rol,u.fechahoracreacion,u.estado,u.cod_persona, " +
"r.nombre_rol " +
"FROM usuario u, roles r " +
"WHERE u.cod_rol = r.cod_rol " +
"AND cod_persona = " + cod_persona.ToString();
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row != null)
{
this.cod_usuario = Int32.Parse(row["cod_usuario"].ToString());
this.login = row["login"].ToString();
this.password = row["password"].ToString();
this.cod_rol = Int32.Parse(row["cod_rol"].ToString());
this.rol = row["nombre_rol"].ToString();
this.obtenerCapacidades();
}
}
public void GetDbCapabilities()
{
string query = @"SELECT p.*
FROM permissions p, role2permission r2p
WHERE p.permission_id = r2p.permission_id
AND r2p.role_id = {0}";
query = string.Format(query, this.RoleId);
ArrayList caps = SBFactory.getDbh().Query(query);
if (caps == null)
{
return;
}
foreach (Hashtable cap in caps)
{
if (this._capabilities.ContainsKey(cap["permission"]))
{
continue;
}
this._capabilities.Add(cap["permission"], cap);
}
}
public static object GetMeta(string table, string table_column_key, object column_key_value, string meta_key, bool single = true)
{
string query = string.Format("SELECT * FROM {0} WHERE {1} = '{2}' AND meta_key = '{3}'",
table, table_column_key, column_key_value, meta_key);
if (single)
{
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row == null)
{
return(null);
}
return(row ["meta_value"]);
}
else
{
ArrayList rows = SBFactory.getDbh().Query(query);
if (rows == null)
{
return(null);
}
return(rows);
}
}
