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);
}
//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 bool DeleteBeneficiary(int id)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE beneficiary SET deleted = 1 WHERE id = " + id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool UpdateEntityDigit(DigitVerificatorDTO digitDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE vdv SET vdv = '" + digitDto.vdv + "' WHERE entity = '" + digitDto.entity + "'";
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool DeleteUser(int userId)
{
DBSql dbsql = new DBSql();
String sql;
sql = "DELETE FROM users WHERE id = " + userId;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool DeleteItemType(int id)
{
DBSql dbsql = new DBSql();
String sql;
sql = "DELETE FROM item_type WHERE id = " + id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool DeleteDonor(int id)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE donor SET deleted = 1 WHERE id = " + id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool DeleteStock(int id)
{
DBSql dbsql = new DBSql();
String sql;
sql = "DELETE FROM stock WHERE id = " + id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool DeleteReleaseOrder(int id)
{
DBSql dbsql = new DBSql();
String sql;
sql = "DELETE FROM release_order_detail WHERE releaseOrderId = " + id + "; DELETE FROM release_order WHERE id = " + id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool PerformBackup(string fullBackupPath)
{
DBSql dbsql = new DBSql();
String sql;
sql = "BACKUP DATABASE " + DBSql.DATABASE + " TO DISK = '" + fullBackupPath + "'";
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool PerformRestore(string fullBackupPath)
{
DBSql dbsql = new DBSql();
String sql;
sql = "USE MASTER ALTER DATABASE " + DBSql.DATABASE + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE " + DBSql.DATABASE + " FROM DISK = '" + fullBackupPath + "' WITH REPLACE";
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool UpdateToStatus(int id, int statusId)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE donation SET statusId = " + statusId + " WHERE id = " + id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
/// <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);
}
/// <summary>
/// Elimina el perfil según el código provisto por parámetro, incluyendo relaciones
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public bool DeleteProfile(string code)
{
DBSql dbsql = new DBSql();
String sql;
DeleteRelation(code);
sql = "DELETE FROM permission WHERE id = '" + code + "'";
dbsql.ExecuteNonQuery(sql);
return(true);
}
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);
}
/// <summary>
/// Elimina las relaciones: exclusiones y jerarquía de dependencias asociadas al perfil
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public bool DeleteRelation(string code)
{
DBSql dbsql = new DBSql();
String sql;
sql = "DELETE FROM permission_exclusion WHERE id = '" + code + "'";
dbsql.ExecuteNonQuery(sql);
sql = "DELETE FROM permission_hierarchy WHERE permissionIdBranch = '" + code + "'";
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool SaveDonor(DonorDTO donorDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO donor (personId, organizationId, canBeContacted) VALUES (";
sql += donorDto.id + ", ";
sql += donorDto.organizationId == 0 ? "null, " : donorDto.organizationId + ", ";
sql += "'" + donorDto.canBeContacted + "'";
sql += ");SELECT @@IDENTITY";
donorDto.donorId = dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool UpdateDonor(DonorDTO donorDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE donor SET ";
sql += "personId = " + donorDto.id + ", ";
sql += "organizationId = " + (donorDto.organizationId == 0 ? "null" : donorDto.organizationId.ToString()) + ", ";
sql += "canBeContacted = '" + donorDto.canBeContacted + "' ";
sql += "WHERE id = " + donorDto.donorId;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool UpdateStatusToStored(int id, int statusId)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE donation set statusId = " + statusId + " WHERE id IN ( ";
sql += "SELECT d.id FROM donation d LEFT JOIN stock s ON s.donationId = d.id ";
sql += "WHERE d.id = " + id + " ";
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)";
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool SaveVolunteer(VolunteerDTO volunteerDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO volunteer (personId, branchId, userId) VALUES (";
sql += volunteerDto.id + ", ";
sql += volunteerDto.branchId + ", ";
sql += volunteerDto.userId != 0 ? volunteerDto.userId.ToString() : "null";
sql += ");SELECT @@IDENTITY";
volunteerDto.volunteerId = dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool SaveProfile(PermissionDTO permissionDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO permission (id, description, system) VALUES (";
sql += "'" + permissionDto.code + "', ";
sql += "'" + permissionDto.description + "', ";
sql += "'" + permissionDto.system + "'";
sql += ")";
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool UpdateReleaseOrder(ReleaseOrderDTO releaseOrderDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE release_order SET ";
sql += "beneficiaryId = " + releaseOrderDto.beneficiaryId + ", ";
sql += "comment = '" + releaseOrderDto.comment + "', ";
sql += "status = '" + releaseOrderDto.status + "' ";
sql += "WHERE id = " + releaseOrderDto.id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool SaveProfileExclusionRelation(string fatherCode, List <PermissionDTO> permissionsDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO permission_exclusion (id, excluded) VALUES ";
foreach (PermissionDTO permission in permissionsDto)
{
sql += "('" + fatherCode + "', '" + permission.code + "'), ";
}
//se remueve la coma del final
dbsql.ExecuteNonQuery(sql.Remove(sql.Length - 2));
return(true);
}
public bool SaveItemType(ItemTypeDTO itemTypeDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO item_type (name, category, comment, perishable) VALUES (";
sql += "'" + itemTypeDto.name + "', ";
sql += "'" + itemTypeDto.category + "', ";
sql += "'" + itemTypeDto.comment + "', ";
sql += "'" + itemTypeDto.perishable + "'";
sql += ");SELECT @@IDENTITY";
itemTypeDto.id = dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool UpdateItemType(ItemTypeDTO itemTypeDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE item_type SET ";
sql += "name = '" + itemTypeDto.name + "', ";
sql += "category = '" + itemTypeDto.category + "', ";
sql += "comment = '" + itemTypeDto.comment + "', ";
sql += "perishable = '" + itemTypeDto.perishable + "' ";
sql += "WHERE id = " + itemTypeDto.id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool SaveProfileRelation(List <PermissionDTO> permissionsDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO permission_hierarchy (permissionIdBranch, permissionIdLeaf) VALUES ";
foreach (PermissionDTO permission in permissionsDto)
{
sql += "('" + permission.fatherCode + "', '" + permission.code + "'), ";
}
//se remueve la coma del final
dbsql.ExecuteNonQuery(sql.Remove(sql.Length - 2));
return(true);
}
public bool SaveReleaseOrderDetail(List <ReleaseOrderDetailDTO> releaseOrdersDetail)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO release_order_detail (releaseOrderId, stockId, quantity) VALUES ";
foreach (ReleaseOrderDetailDTO detail in releaseOrdersDetail)
{
sql += "(" + detail.releaseOrderId + ", " + detail.stockId + ", " + detail.quantity + "), ";
}
//se remueve la coma del final
dbsql.ExecuteNonQuery(sql.Remove(sql.Length - 2));
return(true);
}
public bool UpdateOrganization(OrganizationDTO organizationDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE organization SET ";
sql += "name = '" + organizationDto.name + "', ";
sql += "category = '" + organizationDto.category + "', ";
sql += "comment = '" + organizationDto.comment + "', ";
sql += "phone = '" + organizationDto.phone + "', ";
sql += "email = '" + organizationDto.email + "', ";
sql += "WHERE id = " + organizationDto.id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool SaveOrganization(OrganizationDTO organizationDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO organization (name, category, comment, phone, email) VALUES (";
sql += "'" + organizationDto.name + "', ";
sql += "'" + organizationDto.category + "', ";
sql += "'" + organizationDto.comment + "', ";
sql += "'" + organizationDto.phone + "', ";
sql += "'" + organizationDto.email + "'";
sql += ");SELECT @@IDENTITY";
organizationDto.id = dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool SaveReleaseOrder(ReleaseOrderDTO releaseOrderDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO release_order (beneficiaryId, comment, released, received, status) VALUES (";
sql += releaseOrderDto.beneficiaryId + ", ";
sql += "'" + releaseOrderDto.comment + "', ";
sql += "null,";
sql += "null,";
sql += "'" + releaseOrderDto.status + "' ";
sql += ");SELECT @@IDENTITY";
releaseOrderDto.id = dbsql.ExecuteNonQuery(sql);
return(true);
}