public async Task SuspendVote(int id)
{
var values = new SqlValuesBuilder().Add("Id", id).Build();
var query = $@"UPDATE [dbo].[Votes]
SET IsActive = 0
WHERE Id = @Id";
await this._context.ExecuteNonQuery(query, values);
}
public async Task <IList <Vote> > GetAllInitiatedFromEmployeeVotes(string loggedUser)
{
var values = new SqlValuesBuilder().Add("LoggedUser", loggedUser).Build();
string query = $@"SELECT v.* FROM Votes AS v
WHERE v.Owner = @LoggedUser";
var votes = await this._context.ExecuteReader <Vote>(query, values);
return(votes);
}
public async Task <IList <Vote> > GetAllSuspendedVotes(string loggedUser)
{
var values = new SqlValuesBuilder().Add("LoggedUser", loggedUser).Build();
var query = $@"SELECT * FROM [dbo].[Votes] AS v
WHERE IsActive = 0 AND v.Receiver != @LoggedUser";
var votes = await this._context.ExecuteReader <Vote>(query, values);
return(votes);
}
public async Task <IList <Vote> > GetAllActiveVotes(string loggedUser)
{
var values = new SqlValuesBuilder().Add("LoggedUser", loggedUser).Build();
var query = $@"SELECT v.* FROM [dbo].[Votes] AS v
WHERE IsActive = 1 AND v.Receiver != @LoggedUser
AND v.Id NOT IN (SELECT VoteId FROM EmployeeVoteGift AS ev WHERE ev.EmployeeId = (SELECT TOP(1)Id FROM Employees AS e WHERE e.UserName = @LoggedUser))";
var votes = await this._context.ExecuteReader <Vote>(query, values);
return(votes);
}
public async Task <IList <Employee> > GetAllUnvotedForVoteEmployees(int voteId)
{
var values = new SqlValuesBuilder().Add("VoteId", voteId).Build();
string query = $@"SELECT * FROM Employees AS e
WHERE e.Id NOT IN (SELECT evg.EmployeeId
FROM EmployeeVoteGift AS evg
WHERE evg.VoteId = @VoteId)" ;
var employees = await this._context.ExecuteReader <Employee>(query, values);
return(employees);
}
public async Task <Employee> GetEmployeeByUserNameAndPassword(string userName, string password)
{
var values = new SqlValuesBuilder().Add("UserName", userName)
.Add("Password", password)
.Build();
string query = $@"SELECT * FROM Employees AS e
WHERE e.UserName = @UserName AND e.Password = @Password";
var employee = await this._context.ExecuteReader <Employee>(query, values);
return(employee.FirstOrDefault());
}
public async Task VoteForGift(VotingViewModel voteModel)
{
var values = new SqlValuesBuilder().Add("VoteId", voteModel.VoteId)
.Add("GiftId", voteModel.GiftId)
.Add("LoggedUser", voteModel.LoggedUser)
.Build();
string query = $@"INSERT INTO [dbo].[EmployeeVoteGift] (EmployeeId, VoteId, GiftId)
VALUES ((SELECT TOP(1)Id FROM Employees AS e WHERE e.UserName = @LoggedUser),
@VoteId,
@GiftId
)";
await this._context.ExecuteNonQuery(query, values);
}
public async Task <IList <IList <string> > > GetVoteDetails(int id)
{
var values = new SqlValuesBuilder().Add("Id", id).Build();
string query = $@"SELECT v.ReceiverName, e.UserName, e.Name, g.Name FROM Votes AS v
LEFT JOIN EmployeeVoteGift AS ev
ON v.Id = ev.VoteId
LEFT JOIN Employees AS e
ON e.Id = ev.EmployeeId
LEFT JOIN Gifts AS g
ON g.Id = ev.GiftId
WHERE v.Id = @Id";
var result = await this._context.ExecuteReader(query, values);
return(result);
}
public async Task InsertVote(Vote vote)
{
var values = new SqlValuesBuilder().Add("Owner", vote.Owner)
.Add("Receiver", vote.Receiver)
.Add("IsActive", vote.IsActive)
.Build();
string query = $@"INSERT INTO [dbo].[Votes] (Owner, Receiver, DateCreated, IsActive, OwnerName, ReceiverName)
VALUES ( @Owner,
@Receiver,
GETDATE(),
@IsActive,
(SELECT TOP(1)Name FROM Employees AS e WHERE e.UserName = @Owner),
(SELECT TOP(1)Name FROM Employees AS e WHERE e.UserName = @Receiver)
)";
await this._context.ExecuteNonQuery(query, values);
}
