/// <summary>
/// Include in the view model only the properties you want to update. After the MVC model linker is finished,
/// copy the properties of the view model into the entity instance and use the tool as an automapper.
/// </summary>
/// <param name="registrationViewModel"></param>
/// <returns>True all good, False something goes wrong </returns>
public bool CreatePeople(RegistrationViewModel registrationViewModel)
{
_database = new dbSIACEntities();
var peopleCount = _database.people.Count();
using (_database)
{
var newPeople = new people
{
creationDate = DateTime.Now,
address = registrationViewModel.address,
dni = registrationViewModel.dni,
name = registrationViewModel.name,
lastname = registrationViewModel.lastname,
email = registrationViewModel.email,
pass = Encrypt.GetSHA256(registrationViewModel.pass),
idRol = registrationViewModel.idRol,
isActive = 1
};
_database.people.Add(newPeople);
_database.SaveChanges();
if (peopleCount == _database.people.Count())
{
return(false);
}
return(true);
}
}
public TicketViewModel GeTicketViewModel(int ticketIdLocal)
{
_database = new dbSIACEntities();
var ticketViewModel = new TicketViewModel();
using (_database)
{
var ticket = _database.ticket.FirstOrDefault(currentTicket => currentTicket.idLocal == ticketIdLocal);
if (ticket == null)
{
return(null);
}
if (ticket.idClient != null)
{
ticketViewModel.idClient = (int)ticket.idClient;
}
ticketViewModel.idAssignedTechnician = ticket.idAssignedTechnician;
ticketViewModel.creationDate = ticket.creationDate;
ticketViewModel.estimatedFinishDate = ticket.estimatedFinishDate;
ticketViewModel.idPriority = ticket.idPriority;
ticketViewModel.idCategory = ticket.idCategory;
ticketViewModel.idStatus = ticket.idStatus;
ticketViewModel.creationDate = ticket.creationDate;
ticketViewModel.description = ticket.description;
}
_database.Dispose();
return(ticketViewModel);
}
/// <summary>
/// </summary>
/// <param name="IquerableTickets"></param>
/// <returns></returns>
private IEnumerable <TicketPrintableModel> _IQuerablePrintableModels(IEnumerable <ticket> IquerableTickets)
{
_database = new dbSIACEntities();
var iqTicketPrintableModel = (from tick in IquerableTickets
join creator in _database.people on tick.idCreatorPeople equals creator.id
join clientAddress in _database.people on tick.idClient equals clientAddress.id
join technician in _database.people on tick.idAssignedTechnician equals technician.id
select new TicketPrintableModel
{
idLocal = tick.idLocal,
address = clientAddress.address,
assignedTechnician = technician.name,
assignedTechnicianLastname = technician.lastname,
client = clientAddress.name,
clientLastname = clientAddress.lastname,
CreationDateTime = tick.creationDate,
creatorPeople = creator.name,
creatorPeopleLastname = creator.lastname,
description = tick.description,
email = clientAddress.email,
status = tick.idStatus
}).OrderByDescending(x => x.CreationDateTime);
return(iqTicketPrintableModel);
}
/// <summary>
/// </summary>
/// <param name="idRol"></param>
/// <param name="userId"></param>
/// <returns>IQueryable<ticket></returns>
private IEnumerable <ticket> _filterTicketsByRolAndId(int idRol, int userId)
{
_database = new dbSIACEntities();
IQueryable <ticket> smallFilterTickets = null;
switch (idRol)
{
case (int)ERols.Technician:
{
smallFilterTickets = _database.ticket.Where(ticket => ticket.idAssignedTechnician == userId);
break;
}
case (int)ERols.Client:
{
smallFilterTickets = _database.ticket.Where(ticket => ticket.idClient == userId);
break;
}
case (int)ERols.TechnicianLead:
{
smallFilterTickets = _database.ticket;
break;
}
}
return(smallFilterTickets.AsEnumerable());
}
public bool EditTicket(TicketViewModel baseTicket, string ticketId)
{
if (!int.TryParse(ticketId, out var currentTicketId))
{
return(false);
}
_database = new dbSIACEntities();
using (_database)
{
var result = _database.ticket.FirstOrDefault(b => b.idLocal == currentTicketId);
if (result == null)
{
_database.Dispose();
return(false);
}
result.idCategory = baseTicket.idCategory;
result.idAssignedTechnician = baseTicket.idAssignedTechnician;
result.idPriority = baseTicket.idPriority;
result.idStatus = baseTicket.idStatus;
result.description = baseTicket.description;
_database.SaveChanges();
}
return(true);
}
public bool UpdateTicketStatus(int idStatus, int ticketId)
{
if (ticketId <= 0)
{
return(false);
}
_database = new dbSIACEntities();
using (_database)
{
var ticketDefault = _database.ticket.FirstOrDefault(ticket => ticket.id == ticketId);
if (ticketDefault == null)
{
return(false);
}
ticketDefault.idStatus = idStatus;
ticketDefault.editionDate = DateTime.Now;
try
{
_database.ticket.AddOrUpdate(ticketDefault);
_database.SaveChanges();
}
catch (Exception)
{
return(false);
}
return(true);
}
}
public bool UpdateTicket(TicketHistoryViewModel baseTicket, string ticketIdLocal)
{
var entityTicketId = _searchQueriesService.SearchTicketId(ticketIdLocal);
if (!UpdateTicketStatus(baseTicket.idStatus, entityTicketId))
{
return(false);
}
_database = new dbSIACEntities();
var updateTicket = new ticketHistory
{
date = DateTime.Now,
idPeople = baseTicket.idPeople,
idStatus = baseTicket.idStatus,
idTicket = entityTicketId,
note = baseTicket.note
};
try
{
using (_database)
{
_database.ticketHistory.Add(updateTicket);
_database.SaveChanges();
}
}
catch (Exception)
{
return(false);
}
return(true);
}
public List <SelectListItem> GetListOfStatus()
{
_database = new dbSIACEntities();
List <StatusViewModel> listOfStatus = null;
using (_database)
{
listOfStatus = (from status in _database.status
select new StatusViewModel
{
keyStatus = status.id,
valueStatus = status.name
}
).ToList();
}
var statusList = listOfStatus.ConvertAll(data => new SelectListItem
{
Text = data.valueStatus,
Value = data.keyStatus.ToString(),
Selected = false
});
_database.Dispose();
return(statusList);
}
public List <SelectListItem> GetListOfPriorities()
{
_database = new dbSIACEntities();
List <PriorityViewModel> listOfPriorities = null;
using (_database)
{
listOfPriorities = (from priority in _database.priority
select new PriorityViewModel
{
keyPriority = priority.id,
valuePriority = priority.name
}
).ToList();
}
var prioritiesList = listOfPriorities.ConvertAll(data => new SelectListItem
{
Text = data.valuePriority,
Value = data.keyPriority.ToString(),
Selected = false
});
_database.Dispose();
return(prioritiesList);
}
/// <summary>
/// Use idRol and userId to reduce the number of records in the database.
/// Then, search within that list, comparing idLocal and possible hits even if it is not a complete word.
/// </summary>
/// <param name="stringToSearch"></param>
/// <param name="idRol"></param>
/// <param name="userId"></param>
/// <returns>List<TicketPrintableModel></returns>
public List <TicketPrintableModel> SearchTicket(string stringToSearch, int idRol, int userId)
{
if (stringToSearch.IsNullOrWhiteSpace())
{
return(null);
}
var toSearch = stringToSearch.Trim().ToLower();
var smallFilterTickets = _filterTicketsByRolAndId(idRol, userId);
var filteredTickets = _IQuerablePrintableModels(smallFilterTickets);
_database = new dbSIACEntities();
using (_database)
{
if (int.TryParse(toSearch, out var toSearchInt))
{
var foundedTicketbyId =
filteredTickets.Where(currentTicket => currentTicket.idLocal.Equals(toSearchInt));
var founderdList = foundedTicketbyId.ToList();
if (founderdList.Any())
{
return(founderdList);
}
}
filteredTickets = filteredTickets.Where(currentTicket =>
currentTicket.assignedTechnician.ToLower().Contains(toSearch) ||
currentTicket.assignedTechnicianLastname.ToLower().Contains(toSearch) ||
currentTicket.client.ToLower().Contains(toSearch) ||
currentTicket.clientLastname.ToLower().Contains(toSearch) ||
currentTicket.address.ToLower().Contains(toSearch) ||
currentTicket.description.ToLower().Contains(toSearch));
return(filteredTickets.ToList());
}
}
public List <SelectListItem> GetListOfRols()
{
_database = new dbSIACEntities();
List <RolsViewModel> listOfRols = null;
using (_database)
{
listOfRols = (from rol in _database.rol
select new RolsViewModel
{
keyRols = rol.id,
valueRols = rol.name
}
).ToList();
}
var rolList = listOfRols.ConvertAll(data => new SelectListItem
{
Text = data.valueRols,
Value = data.keyRols.ToString(),
Selected = false
});
_database.Dispose();
return(rolList);
}
/// <summary>
/// Shortcut to get the real id of a ticket according to its localID.
/// </summary>
/// <param name="ticketIdLocal"></param>
/// <returns></returns>
public int SearchTicketId(string ticketIdLocal)
{
if (!int.TryParse(ticketIdLocal, out var localTicketId))
{
return(0);
}
_database = new dbSIACEntities();
using (_database)
{
var entityTicketId = _database.ticket.FirstOrDefault(current => current.idLocal == localTicketId);
return(entityTicketId?.id != null ? entityTicketId.id : 0); //demasiado comprimido?
}
}
/// <summary>
/// Search email
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string SearchEmailPeople(int id)
{
if (id <= 0)
{
return(null);
}
_database = new dbSIACEntities();
using (_database)
{
var email = _database.people.Find(id)?.email;
return(email);
}
}
/// <summary>
/// Get a user by email and password in the database.
/// </summary>
/// <param name="email">Email</param>
/// <param name="password">Password</param>
/// <returns>object person or null depending if email and password match with a active User</returns>
public people SearchPeople(string email, string password)
{
_database = new dbSIACEntities();
people activePerson = null;
var truePassword = Encrypt.GetSHA256(password.Trim());
using (_database)
{
var objPeople =
_database.people.FirstOrDefault(e => e.email == email.Trim() && e.pass == truePassword);
activePerson = objPeople;
}
return(activePerson);
}
/// <summary>
/// Create a new ticket, if a client is the creator idClient is taken from the sessionUser info. Othewise from the
/// ViewModel.
/// </summary>
/// <param name="baseTicket"></param>
/// <param name="sessionUser"></param>
/// <returns>True ticket created. False something went wrong</returns>
public (bool result, int idLocal) CreateTicket(TicketViewModel baseTicket, people sessionUser)
{
var random = new Random();
baseTicket.idLocal = random.Next(1000, 99999); //Algun dia llegara a mas de esa cantidad de tickets?
_database = new dbSIACEntities();
if (baseTicket.idClient == 0)
{
baseTicket.idClient = sessionUser.id;
}
if (baseTicket.idAssignedTechnician == null)
{
baseTicket.idAssignedTechnician = 1;
}
var ticket = new ticket
{
idLocal = baseTicket.idLocal,
idStatus = baseTicket.idStatus,
idCreatorPeople = sessionUser.id,
creationDate = baseTicket.creationDate,
estimatedFinishDate = baseTicket.estimatedFinishDate,
idPriority = baseTicket.idPriority,
idCategory = baseTicket.idCategory,
description = baseTicket.description,
idClient = baseTicket.idClient,
idAssignedTechnician = baseTicket.idAssignedTechnician
};
try
{
using (_database)
{
_database.ticket.Add(ticket);
_database.SaveChanges();
}
return(true, baseTicket.idLocal);
}
catch (Exception)
{
return(false, 0);
}
}
public static bool IsAuthonized(int idOperation = 0)
{
_database = new dbSIACEntities();
using (_database)
{
var people = (people)HttpContext.Current.Session["User"];
if (people == null)
{
return(false);
}
var userOperations =
_database.rolOperations.Where(m => m.idRol == people.idRol && m.idOperations == idOperation);
if (userOperations.ToList().Count < 1)
{
return(false);
}
return(true);
}
}
public PeopleViewModel GetDetailsPeople(int peopleId)
{
_database = new dbSIACEntities();
PeopleViewModel searchPeople = null;
using (_database)
{
var currentPeople = _database.people.FirstOrDefault(current => current.id == peopleId);
searchPeople = new PeopleViewModel
{
addressClient = currentPeople.address,
keyClient = currentPeople.id,
lastNameClient = currentPeople.lastname,
nameClient = currentPeople.name
};
}
return(searchPeople);
}
public override void OnAuthorization(AuthorizationContext filterContext)
{
_database = new dbSIACEntities();
using (_database)
{
_people = (people)HttpContext.Current.Session["User"];
var userOperations =
_database.rolOperations.Where(m => m.idRol == _people.idRol && m.idOperations == _idOperation);
if (userOperations == null || userOperations.ToList().Count < 1)
{
var operation = _database.operations.Find(_idOperation);
var idModule = operation.idModule;
var nameOperation = getOperationName(_idOperation);
var nameModule = getModuleName(idModule);
filterContext.Result =
new RedirectResult("~/Error/UnauthorizedOperation?operation=" + nameOperation +
nameModule + "&msjeErrorExcepcion=");
}
}
}
/// <summary>
/// Find the ticket that has the same idLocal assigned to it.
/// Respecting that the user who performs the search is the creator, the client or the assigned technician.
/// </summary>
/// <param name="ticketIdLocal"></param>
/// <param name="idRol"></param>
/// <param name="userId"></param>
/// <returns name="TicketPrintableModel"></returns>
public TicketPrintableModel SearchTicketByIdAndUser(int ticketIdLocal, int idRol, int userId)
{
if (ticketIdLocal <= 0)
{
return(null);
}
var smallFilterTickets = _filterTicketsByRolAndId(idRol, userId);
_database = new dbSIACEntities();
using (_database)
{
if (smallFilterTickets.FirstOrDefault(currentTicket => currentTicket.idLocal == ticketIdLocal) !=
null)
{
var foundTickets = (from ticket in smallFilterTickets
where ticket.idLocal == ticketIdLocal
join creator in _database.people on ticket.idCreatorPeople equals creator.id
join clientAddress in _database.people on ticket.idClient equals clientAddress.id
join tecnician in _database.people on ticket.idAssignedTechnician equals tecnician.id
select new TicketPrintableModel
{
idLocal = ticket.idLocal,
address = clientAddress.address,
assignedTechnician = tecnician.name,
assignedTechnicianLastname = tecnician.lastname,
client = clientAddress.name,
clientLastname = clientAddress.lastname,
CreationDateTime = ticket.creationDate,
creatorPeople = creator.name,
creatorPeopleLastname = creator.lastname,
description = ticket.description,
email = clientAddress.email,
status = ticket.idStatus
}).FirstOrDefault();
return(foundTickets);
}
}
return(null);
}
public List <AdminsViewModel> GetListAdmins()
{
_database = new dbSIACEntities();
var AdminIdRol = 1;
List <AdminsViewModel> listOfAdmins = null;
using (_database)
{
listOfAdmins = (from admins in _database.people
where admins.idRol == AdminIdRol
select new AdminsViewModel
{
keyAdmin = admins.id,
emailAdmin = admins.email
}
).ToList();
}
_database.Dispose();
return(listOfAdmins);
}
public List <TechniciansViewModel> GetListTechnicians()
{
_database = new dbSIACEntities();
var technicianIdRol = 2;
List <TechniciansViewModel> listOfTechnicians = null;
using (_database)
{
listOfTechnicians = (from technicians in _database.people
where technicians.idRol == technicianIdRol
select new TechniciansViewModel
{
keyTechnician = technicians.id,
nameTechnician = technicians.name
}
).ToList();
}
_database.Dispose();
return(listOfTechnicians);
}
public List <TicketHistoryViewModel> GeTicketHistoryViewModel(int ticketIdLocal)
{
_database = new dbSIACEntities();
using (_database)
{
var ticketId = _searchQueriesService.SearchTicketId(ticketIdLocal.ToString());
var ticketHistoryList = (from ticket in _database.ticketHistory
join creator in _database.people on ticket.idPeople equals creator.id
where ticket.idTicket == ticketId
select new TicketHistoryViewModel
{
idTicket = ticket.id,
date = ticket.date,
idPeople = ticket.idPeople,
idStatus = ticket.idStatus,
note = ticket.note,
peopleName = creator.name,
peopleLastName = creator.lastname
}).ToList();
return(ticketHistoryList);
}
}
public List <SelectListItem> GetListOfCategories()
{
_database = new dbSIACEntities();
List <CategoriesViewModel> listOfCategories = null;
listOfCategories = (from cat in _database.category
select new CategoriesViewModel
{
keyCategories = cat.id,
nameCategories = cat.name
}
).ToList();
var categoriesList = listOfCategories.ConvertAll(data => new SelectListItem
{
Text = data.nameCategories,
Value = data.keyCategories.ToString(),
Selected = false
});
_database.Dispose();
return(categoriesList);
}
public List <TicketPrintableModel> GetActiveTicketsList(people currentPeople)
{
if (currentPeople.idRol != (int)ERols.TechnicianLead)
{
return(_printableTickets =
_searchQueriesService.SearchActiveTicketByClient(currentPeople.id, currentPeople.idRol));
}
_database = new dbSIACEntities();
using (_database)
{
_printableTickets = (from ticket in _database.ticket
where ticket.idStatus != (int)EStatus.Finalizado
where ticket.idStatus != (int)EStatus.Cancelado
join creator in _database.people on ticket.idCreatorPeople equals creator.id
join clientAddress in _database.people on ticket.idClient equals clientAddress.id
join tecnician in _database.people on ticket.idAssignedTechnician equals tecnician.id
select new TicketPrintableModel
{
idLocal = ticket.idLocal,
address = clientAddress.address,
assignedTechnician = tecnician.name,
assignedTechnicianLastname = tecnician.lastname,
client = clientAddress.name,
clientLastname = clientAddress.lastname,
CreationDateTime = ticket.creationDate,
creatorPeople = creator.name,
creatorPeopleLastname = creator.lastname,
description = ticket.description,
email = clientAddress.email,
status = ticket.idStatus
}).ToList();
}
return(_printableTickets);
}
/// <summary>
/// Find all active tickets of a client.
/// </summary>
/// <param name="idPeople"></param>
/// <param name="idRol"></param>
/// <returns>List<TicketPrintableModel></returns>
public List <TicketPrintableModel> SearchActiveTicketByClient(int idPeople, int idRol)
{
if (idPeople <= 0)
{
return(null);
}
var filteredTickets = _filterTicketsByRolAndId(idRol, idPeople);
_database = new dbSIACEntities();
using (_database)
{
var foundTickets = (from foundTicket in filteredTickets
where foundTicket.idStatus != (int)EStatus.Finalizado
where foundTicket.idStatus != (int)EStatus.Cancelado
join creator in _database.people on foundTicket.idCreatorPeople equals creator.id
join clientAddress in _database.people on foundTicket.idClient equals clientAddress.id
join tecnician in _database.people on foundTicket.idAssignedTechnician equals tecnician.id
select new TicketPrintableModel
{
idLocal = foundTicket.idLocal,
address = clientAddress.address,
assignedTechnician = tecnician.name,
assignedTechnicianLastname = tecnician.lastname,
client = clientAddress.name,
clientLastname = clientAddress.lastname,
CreationDateTime = foundTicket.creationDate,
creatorPeople = creator.name,
creatorPeopleLastname = creator.lastname,
description = foundTicket.description,
email = clientAddress.email,
status = foundTicket.idStatus
}).OrderByDescending(x => x.CreationDateTime);
return(foundTickets.ToList());
}
}
public List <PeopleViewModel> GetListClients()
{
_database = new dbSIACEntities();
var clientsIdRol = 3;
List <PeopleViewModel> listOfClients = null;
using (_database)
{
listOfClients = (from people in _database.people
where people.idRol == clientsIdRol
select new PeopleViewModel
{
keyClient = people.id,
nameClient = people.name,
addressClient = people.address,
lastNameClient = people.lastname
}
).ToList();
}
_database.Dispose();
return(listOfClients);
}
