//Sólo recupera aquellos con estado 1 (Recibido)
public List <DonationDTO> GetAvaliableDonations()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <DonationDTO> result = new List <DonationDTO>();
sql = "SELECT d.*, SUM(CASE WHEN s.quantity IS NULL THEN 0 ELSE s.quantity END) stocked ";
sql += "FROM donation d LEFT JOIN stock s ON s.donationId = d.id ";
sql += "WHERE d.statusId = 1 AND d.volunteerId is not null ";
sql += "GROUP BY d.id, d.items, d.arrival, d.statusId, d.donorId, d.comment, d.volunteerId ";
//sql += "HAVING SUM(CASE WHEN s.quantity IS NULL THEN 0 ELSE s.quantity END) < items";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <StockDTO> GetAvailableStock()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <StockDTO> result = new List <StockDTO>();
sql = "SELECT s.id, s.name, s.quantity - SUM(CASE WHEN r.quantity IS NULL THEN 0 ELSE r.quantity END) quantity, s.itemTypeId, ";
sql += "s.donationId, s.depotId, s.dueDate, s.location ";
sql += "FROM stock s INNER JOIN donation d ON d.id = s.donationId LEFT JOIN release_order_detail r ON r.stockId = s.id ";
sql += "WHERE d.statusId = 2 GROUP BY s.id, s.name, s.quantity, s.itemTypeId, s.donationId, s.depotId, s.dueDate, s.location ";
sql += "HAVING s.quantity > SUM(CASE WHEN r.quantity IS NULL THEN 0 ELSE r.quantity END) ";
sql += "ORDER BY s.itemTypeId ASC, s.dueDate ASC";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
/// <summary>
/// Devuelve true si no se está utilizando el perfil y puede ser eliminado.
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public bool CanDeleteProfile(string code)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT 1 FROM users WHERE permissionId = '" + code + "'";
reader = dbsql.executeReader(sql);
return(reader.Count == 0);
}
public bool IsInUse(int id)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT count(1) FROM release_order_detail WHERE stockId = " + id;
reader = dbsql.executeReader(sql);
List <String> item = reader.First();
return(int.Parse(item[0]) > 0);
}
public VolunteerDTO GetVolunteer(int id)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT p.*, v.id volunteerId, v.branchId, v.userId FROM volunteer v INNER JOIN person p ON p.id = v.personId WHERE v.id = " + id;
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public ItemTypeDTO GetItemType(int id)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT * FROM item_type WHERE id = " + id;
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public BeneficiaryDTO GetBeneficiary(int id)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT p.*, b.id beneficiaryId, destination, ages, health, accessibility, majorProblem FROM beneficiary b INNER JOIN person p ON p.id = b.personId WHERE b.id = " + id;
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public DonationStatusDTO GetDonationStatus(int id)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT * FROM donation_status WHERE id = " + id;
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public DonorDTO GetDonor(int id)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT p.*, d.id donorId, d.organizationId, d.CanBeContacted FROM donor d INNER JOIN person p ON p.id = d.personId WHERE d.id = " + id;
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public ReleaseOrderDTO GetReleaseOrder(int id)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT * FROM release_order WHERE id = " + id;
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public UserDTO GetUser(string userName)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT * FROM users WHERE name = '" + userName + "'";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public PersonDTO GetPerson(int id)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT * FROM person WHERE id = " + id;
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public UserDTO LogIn(string user, string password)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT * FROM users WHERE name = '" + user + "' AND password = '******' and ACTIVE = 1";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public CountryDTO GetCountry(string iso2)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT * FROM country WHERE iso2 = '" + iso2 + "'";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public DonationDTO GetDonation(int id)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
sql = "SELECT d.*, SUM(CASE WHEN s.quantity IS NULL THEN 0 ELSE s.quantity END) stocked FROM donation d LEFT JOIN stock s ON s.donationId = d.id WHERE d.id = " + id;
sql += " GROUP BY d.id, d.items, d.arrival, d.statusId, d.donorId, d.comment, d.volunteerId";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public LanguageDTO GetLanguage(int languageId)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <LanguageDTO> result = new List <LanguageDTO>();
sql = "SELECT * FROM language where id = " + languageId;
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
return(Resolve(reader.First()));
}
return(null);
}
public List <TranslationDTO> GetTranslations(int languageId)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <TranslationDTO> result = new List <TranslationDTO>();
sql = "SELECT * FROM translation WHERE languageId = '" + languageId + "'";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <ReleaseOrderDTO> GetReleaseOrders()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <ReleaseOrderDTO> result = new List <ReleaseOrderDTO>();
sql = "SELECT * FROM release_order";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
/// <summary>
/// Devuelve un listado con todos los permisos (no jerarquía)
/// </summary>
/// <returns></returns>
public List <PermissionDTO> GetProfiles()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <PermissionDTO> result = new List <PermissionDTO>();
sql = "SELECT null fatherCode, id, description, system, 'false' excluded FROM permission WHERE system = 0";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <StockDTO> GetStocks()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <StockDTO> result = new List <StockDTO>();
sql = "SELECT * FROM stock order by donationId desc";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <DonorDTO> GetDonors()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <DonorDTO> result = new List <DonorDTO>();
sql = "SELECT p.*, d.id donorId, d.organizationId, d.CanBeContacted FROM donor d INNER JOIN person p ON p.id = d.personId WHERE d.deleted = 0";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <DigitVerificatorDTO> GetEntityDigits()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <DigitVerificatorDTO> result = new List <DigitVerificatorDTO>();
sql = "SELECT * FROM vdv";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <ItemTypeDTO> GetItemTypes()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <ItemTypeDTO> result = new List <ItemTypeDTO>();
sql = "SELECT * FROM item_type";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <BeneficiaryDTO> GetBeneficiaries()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <BeneficiaryDTO> result = new List <BeneficiaryDTO>();
sql = "SELECT p.*, b.id beneficiaryId, destination, ages, health, accessibility, majorProblem FROM beneficiary b INNER JOIN person p ON p.id = b.personId WHERE b.deleted = 0";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
/// <summary>
/// Recupera el listado de idiomas disponibles en el sistema.
/// </summary>
/// <returns></returns>
public List <LanguageDTO> GetLanguages()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <LanguageDTO> result = new List <LanguageDTO>();
sql = "SELECT * FROM language ORDER BY name";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <VolunteerDTO> GetVolunteers()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <VolunteerDTO> result = new List <VolunteerDTO>();
sql = "SELECT p.*, v.id volunteerId, v.branchId, v.userId FROM volunteer v INNER JOIN person p ON p.id = v.personId";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <PermissionDTO> GetProfile(String profileId)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <PermissionDTO> result = new List <PermissionDTO>();
// Se recupera el árbol de jerarquías utilizando CTE
sql = "WITH permisos (permissionIdBranch, permissionIdLeaf) AS ( ";
sql += "SELECT pr.permissionIdBranch, pr.permissionIdLeaf ";
sql += "FROM permission_hierarchy pr WHERE pr.permissionIdBranch = '" + profileId + "' ";
sql += "UNION ALL ";
sql += "SELECT pr.permissionIdBranch, pr.permissionIdLeaf ";
sql += "FROM permission_hierarchy pr INNER JOIN permisos p ";
sql += "ON p.permissionIdLeaf = pr.permissionIdBranch) ";
// Al final queda: id del permiso, descripción, permiso por el que se accedió, padre // autoreferencia
// El permiso por el que se busca siempre se excluye, razón por la que hay que incluirlo con un union
//sql += "SELECT v.permissionIdBranch fatherCode, v.permissionIdLeaf code, pe.description, pe.system ";
//sql += "FROM (SELECT p.* FROM permisos p UNION SELECT NULL, '" + profileId + "') v ";
//sql += "LEFT JOIN permission pe ON pe.id = v.permissionIdLeaf ORDER BY 1, 2";
sql += "SELECT v.permissionIdBranch fatherCode, v.permissionIdLeaf code, pe.description, pe.system, CASE WHEN px.excluded IS NULL THEN 'false' ELSE 'true' END excluded ";
sql += "FROM (SELECT p.* FROM permisos p UNION SELECT NULL, '" + profileId + "') v ";
sql += "LEFT JOIN permission pe ON pe.id = v.permissionIdLeaf ";
sql += "LEFT JOIN permission_exclusion px ON px.id = '" + profileId + "' AND (px.excluded = v.permissionIdLeaf OR px.excluded = v.permissionIdBranch) ";
sql += "group by v.permissionIdBranch, v.permissionIdLeaf, pe.description, pe.system, CASE WHEN px.excluded IS NULL THEN 'false' ELSE 'true' END ";
sql += "ORDER BY 1, 2";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <LogDTO> GetLogs(LogDTO filter = null)
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <LogDTO> result = new List <LogDTO>();
string whereClause = "";
if (filter != null)
{
whereClause = "WHERE ";
whereClause += "loglevel >= " + (int)filter.logLevel + " AND "; //siempre se incluye
whereClause += filter.action != null ? " action LIKE '%" + filter.action + "%' AND" : "";
whereClause += filter.description != null ? " description LIKE '%" + filter.description + "%' AND " : "";
whereClause += filter.entity != null ? " entity LIKE '%" + filter.entity + "%' AND ": "";
if (filter.created.Year != 1900)
{
whereClause += "created > CONVERT(datetime, '" + filter.created + "', 103)";
}
if (whereClause.EndsWith(" AND "))
{
whereClause.Remove(whereClause.Length - 4);
}
}
sql = "SELECT * FROM logs " + whereClause + " ORDER BY created desc";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}
public List <PermissionDTO> GetSystemPermissions()
{
DBSql dbsql = new DBSql();
String sql;
List <List <String> > reader;
List <PermissionDTO> result = new List <PermissionDTO>();
// Recupera los roots de todos los permisos de sistema
sql = "SELECT DISTINCT null fatherCode, p.id, p.description, p.system, 'false' excluded FROM permission p INNER JOIN permission_hierarchy ph ON ph.permissionIdBranch = p.id AND p.system = 1 ";
sql += "WHERE p.id NOT IN ";
sql += "(SELECT DISTINCT ph.permissionIdLeaf FROM permission p INNER JOIN permission_hierarchy ph ON ph.permissionIdBranch = p.id AND p.system = 1)";
reader = dbsql.executeReader(sql);
if (reader.Count > 0)
{
for (int i = 0; i < reader.Count; ++i)
{
result.Add(Resolve(reader[i]));
}
}
return(result);
}