/// <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 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 DeleteUser(int userId)
{
DBSql dbsql = new DBSql();
String sql;
sql = "DELETE FROM users WHERE id = " + userId;
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 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 PerformBackup(string fullBackupPath)
{
DBSql dbsql = new DBSql();
String sql;
sql = "BACKUP DATABASE " + DBSql.DATABASE + " TO DISK = '" + fullBackupPath + "'";
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 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);
}
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 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 DeleteItemType(int id)
{
DBSql dbsql = new DBSql();
String sql;
sql = "DELETE FROM item_type WHERE id = " + id;
dbsql.ExecuteNonQuery(sql);
return(true);
}
/// <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 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 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 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 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 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 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 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 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 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 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 UpdateDonation(DonationDTO donationDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE donation SET ";
sql += "items = " + donationDto.items + ", ";
sql += "statusId = " + donationDto.statusId + ", ";
sql += "donorId = " + donationDto.donorId + ", ";
sql += "comment = '" + donationDto.comment + "', ";
sql += "volunteerId = " + (donationDto.volunteerId == 0 ? "null" : donationDto.volunteerId.ToString()) + " ";
sql += "WHERE id = " + donationDto.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);
}
public void SaveLog(LogDTO log)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO logs (loglevel, action, description, entity, created) ";
sql += " VALUES (";
sql += (int)log.logLevel + ", ";
sql += "'" + log.action + "' , ";
sql += "'" + log.description + "' , ";
sql += "'" + log.entity + "' , ";
sql += "GETDATE()";
sql += "); SELECT @@IDENTITY";
log.id = dbsql.ExecuteNonQuery(sql);
}
public bool UpdateDonor(BeneficiaryDTO beneficiaryDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "UPDATE beneficiary SET ";
sql += "personId = " + beneficiaryDto.id + ", ";
sql += "destination = " + beneficiaryDto.destination + ", ";
sql += "ages = " + beneficiaryDto.ages + ", ";
sql += "health = " + beneficiaryDto.health + ", ";
sql += "accessibility = " + beneficiaryDto.accessibility + ", ";
sql += "majorProblem = " + beneficiaryDto.majorProblem + " ";
sql += "WHERE id = " + beneficiaryDto.beneficiaryId;
dbsql.ExecuteNonQuery(sql);
return(true);
}
public bool SaveBeneficiary(BeneficiaryDTO beneficiaryDto)
{
DBSql dbsql = new DBSql();
String sql;
sql = "INSERT INTO beneficiary (personId, destination, ages, health, accessibility, majorProblem) VALUES (";
sql += beneficiaryDto.id + ", ";
sql += beneficiaryDto.destination + ", ";
sql += beneficiaryDto.ages + ", ";
sql += beneficiaryDto.health + ", ";
sql += beneficiaryDto.accessibility + ", ";
sql += beneficiaryDto.majorProblem + " ";
sql += ");SELECT @@IDENTITY";
beneficiaryDto.beneficiaryId = dbsql.ExecuteNonQuery(sql);
return(true);
}