/// <summary>
/// Regster User
/// </summary>
/// <param name="user"></param>
/// <param name="password"></param>
/// <returns></returns>
public static async Task <CommonResponse> Register(User user, string password)
{
var response = new CommonResponse();
string passwordHash, passwordSalt;
GeneratePasswordHash(password, out passwordHash, out passwordSalt);
user.Password = passwordHash;
user.Salt = passwordSalt;
try
{
using (SqlConnection dbConn = new SqlConnection(selectConnection("main")))
{
dbConn.Open();
String SQL = GetUserInsertQuery(user);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
}
response.IsError = false;
//var verificationMailSent = SendEmailVerification(user);
//if (verificationMailSent)
//{
// response.IsError = false;
//}
//else
//{
// response.IsError = true;
// response.Message = "Error in sending email with verification link";
//}
}
catch (Exception)
{
response.IsError = true;
response.Message = "Error in Registering User!";
}
finally
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = user.UserID,
ActionPerformed = "Check If User Exists ",
MethodName = "UserExists",
IsError = false
},
user.Location);
}
return(response);
}
/// <summary>
/// Service Method To Get All Ballots
/// </summary>
/// <param name="ballot"></param>
/// <returns></returns>
public static async Task <List <BallotModel> > GetBallots(BallotModel ballot)
{
List <BallotModel> Ballots = new List <BallotModel>();
using (SqlConnection dbConn = new SqlConnection(selectConnection(ballot.Location)))
{
var Query = "SELECT * from Ballot";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(Query, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
BallotModel ballotItem = new BallotModel();
ballotItem.ID = reader.GetInt32(0);
ballotItem.CandidateID = reader.GetInt32(1);
ballotItem.DistrictID = reader.GetInt32(2);
ballotItem.CenterID = reader.GetInt32(3);
ballotItem.Voted = reader.GetBoolean(4);
ballotItem.DateCreated = reader.GetDateTime(5);
Ballots.Add(ballotItem);
}
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = ballot.UserID,
ActionPerformed = "Ballots Error : " + ex.Message,
MethodName = "GetBallots",
IsError = true
},
ballot.Location);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = ballot.UserID,
ActionPerformed = "Get All Existing Ballots ",
MethodName = "GetBallots",
IsError = false
},
ballot.Location);
}
return(Ballots);
}
}
/// <summary>
/// Service Method To Get All The Candidates
/// </summary>
/// <param name="candidate"></param>
/// <returns></returns>
public static async Task <List <CandidateModel> > GetCandidates(CandidateModel candidate)
{
List <CandidateModel> Candidates = new List <CandidateModel>();
using (SqlConnection dbConn = new SqlConnection(selectConnection(candidate.Location)))
{
var Query = "SELECT * from Candidate";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(Query, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
CandidateModel candidateItem = new CandidateModel();
candidateItem.ID = reader.GetInt32(0);
candidateItem.Name = reader.GetString(1);
candidateItem.PartyID = reader.GetInt32(2);
candidateItem.DistrictID = reader.GetInt32(3);
candidateItem.Votes = reader.GetInt32(4);
Candidates.Add(candidateItem);
}
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = candidate.UserID,
ActionPerformed = "Candidates Error : " + ex.Message,
MethodName = "GetCandidates",
IsError = true
},
candidate.Location);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = candidate.UserID,
ActionPerformed = "Get All Existing Candidates ",
MethodName = "GetCandidates",
IsError = false
},
candidate.Location);
}
return(Candidates);
}
}
// public static string conString = selectConnection("colombo");
/// <summary>
/// Service Method To Get The Total Ballot Count
/// </summary>
/// <param name="authBaseModel"></param>
/// <returns></returns>
public static async Task <List <TotalBallotModel> > GetTotalBallotCount(AuthBaseModel authBaseModel)
{
List <TotalBallotModel> TotalBallots = new List <TotalBallotModel>();
using (SqlConnection dbConn = new SqlConnection(selectConnection(authBaseModel.Location)))
{
var Query = "SELECT CandidateID, COUNT(Voted) AS TotalNoOfVotes FROM Ballot WHERE Voted = 1 GROUP BY CandidateID ORDER BY TotalNoOfVotes DESC";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(Query, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
TotalBallotModel Ballots = new TotalBallotModel();
Ballots.CandidateID = reader.GetInt32(0);
Ballots.TotalNoOfVotes = reader.GetInt32(1);
Ballots.DateTallied = DateTime.Now;
TotalBallots.Add(Ballots);
}
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = authBaseModel.UserID,
ActionPerformed = "Get Total Ballot Count Error : " + ex.Message,
MethodName = "GetTotalBallotCount",
IsError = true
},
authBaseModel.Location);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = authBaseModel.UserID,
ActionPerformed = "Get Total Ballot Count For All Candidates",
MethodName = "GetTotalBallotCount",
IsError = false
},
authBaseModel.Location);
}
return(TotalBallots);
}
}
/// <summary>
/// Service Method To Get Centers
/// </summary>
/// <param name="center"></param>
/// <returns></returns>
public static async Task <List <CenterModel> > GetCenter(CenterModel center)
{
List <CenterModel> centers = new List <CenterModel>();
using (SqlConnection dbConn = new SqlConnection(selectConnection(center.Location)))
{
var isExistingCenter = "SELECT * from Center";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(isExistingCenter, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
CenterModel centerItem = new CenterModel();
centerItem.ID = reader.GetInt32(0);
centerItem.Name = reader.GetString(1);
centerItem.CityID = reader.GetInt32(2);
centerItem.NoOfVoters = reader.GetInt32(3);
centers.Add(centerItem);
}
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = center.UserID,
ActionPerformed = "Center Exists Error : " + ex.Message,
MethodName = "CenterExists",
IsError = true
},
center.Location);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = center.UserID,
ActionPerformed = "Check If Center Exists ",
MethodName = "CenterExists",
IsError = false
},
center.Location);
}
return(centers);
}
}
/// <summary>
/// Service Method To Get Total No Of Votes
/// </summary>
/// <param name="ballot"></param>
/// <returns></returns>
public static async Task <List <TotalBallotModel> > GetTotalVotes(BallotModel ballot)
{
List <TotalBallotModel> Ballots = new List <TotalBallotModel>();
using (SqlConnection dbConn = new SqlConnection(selectConnection(ballot.Location)))
{
var Query = "SELECT CandidateID, COUNT(Voted) AS NoOFVotes from Ballot group by CandidateID order by NoOFVotes desc";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(Query, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
TotalBallotModel ballotItem = new TotalBallotModel();
ballotItem.CandidateID = reader.GetInt32(0);
ballotItem.TotalNoOfVotes = reader.GetInt32(1); //
ballotItem.DateTallied = DateTime.Now;
Ballots.Add(ballotItem);
}
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = ballot.UserID,
ActionPerformed = "Ballots Error : " + ex.Message,
MethodName = "GetBallots",
IsError = true
},
ballot.Location);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = ballot.UserID,
ActionPerformed = "Get All Existing Ballots ",
MethodName = "GetBallots",
IsError = false
},
ballot.Location);
}
return(Ballots);
}
}
/// <summary>
/// Service Method To Get All The Candidates (Since All The Locations Will Have The Same candidates, Can Get Them From One Location)
/// </summary>
/// <param name="location"></param>
/// <param name="userID"></param>
/// <returns></returns>
public static List <CandidateModel> GetCandidates(string location, int userID)
{
List <CandidateModel> candidates = new List <CandidateModel>();
using (SqlConnection dbConn = new SqlConnection(selectConnection(location)))
{
string query = @"SELECT * FROM Candidate";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(query, dbConn);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CandidateModel candidate = new CandidateModel();
candidate.ID = reader.GetInt32(0);
candidate.Name = reader.GetString(1);
candidate.Votes = reader.GetInt32(2);
candidates.Add(candidate);
}
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = userID,
ActionPerformed = " Error In Consolidating Votes : " + ex.Message,
MethodName = "ConsolidateVotes",
IsError = true
},
location);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = userID,
ActionPerformed = "Consolidate Votes",
MethodName = "ConsolidateVotes",
IsError = false
},
location);
}
return(candidates);
}
}
/// <summary>
/// Service Method To Get User Roles
/// </summary>
/// <param name="role"></param>
/// <returns></returns>
public static async Task <List <RoleModel> > GetUserRoles(RoleModel role)
{
List <RoleModel> Roles = new List <RoleModel>();
using (SqlConnection dbConn = new SqlConnection(selectConnection("main")))
{
var isExistingUserQuery = "SELECT * from Role";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(isExistingUserQuery, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
RoleModel roleItem = new RoleModel();
roleItem.ID = reader.GetInt32(0);
roleItem.Name = reader.GetString(1);
roleItem.DateAdded = reader.GetDateTime(2);
Roles.Add(roleItem);
}
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = role.UserID,
ActionPerformed = "Role Exists Error : " + ex.Message,
MethodName = "RoleExists",
IsError = true
},
role.Location);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = role.UserID,
ActionPerformed = "Check If User Exists ",
MethodName = "UserExists",
IsError = false
},
role.Location);
}
return(Roles);
}
}
/// <summary>
/// Service Method To Get Districts
/// </summary>
/// <param name="district"></param>
/// <returns></returns>
public static async Task <List <DistrictModel> > GetDistrict(DistrictModel district)
{
List <DistrictModel> Districts = new List <DistrictModel>();
using (SqlConnection dbConn = new SqlConnection(selectConnection(district.Location)))
{
var isExistingDistrict = "SELECT * from District";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(isExistingDistrict, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
DistrictModel districtItem = new DistrictModel();
districtItem.ID = reader.GetInt32(0);
districtItem.Name = reader.GetString(1);
districtItem.IsAvailable = reader.GetBoolean(2);
Districts.Add(districtItem);
}
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = district.UserID,
ActionPerformed = "District Exists Error : " + ex.Message,
MethodName = "GetDistrict",
IsError = true
},
district.Location);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = district.UserID,
ActionPerformed = "Check If District Exists ",
MethodName = "GetDistrict",
IsError = false
},
district.Location);
}
return(Districts);
}
}
/// <summary>
/// Service Method To Get Cities
/// </summary>
/// <param name="city"></param>
/// <returns></returns>
public static async Task <List <CityModel> > GetCity(CityModel city)
{
List <CityModel> Cities = new List <CityModel>();
using (SqlConnection dbConn = new SqlConnection(selectConnection(city.Location)))
{
var isExistingCity = "SELECT * from City";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(isExistingCity, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
CityModel cityItem = new CityModel();
cityItem.ID = reader.GetInt32(0);
cityItem.Name = reader.GetString(1);
cityItem.DistrictID = reader.GetInt32(2);
Cities.Add(cityItem);
}
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = city.UserID,
ActionPerformed = "City Exists Error : " + ex.Message,
MethodName = "GetCity",
IsError = true
},
city.Location);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = city.UserID,
ActionPerformed = "Check If City Exists ",
MethodName = "GetCity",
IsError = false
},
city.Location);
}
return(Cities);
}
}
/// <summary>
/// Update Elector
/// </summary>
/// <param name="elector"></param>
/// <returns></returns>
public static async Task <bool> UpdateElector(ElectorModel elector)
{
String SQL = @"UPDATE Elector SET Name = '" + elector.Name + "'" +
" ,NIC = '" + elector.NIC + "'" +
" ,DOB = '" + elector.DOB + "'" +
" ,Address = '" + elector.Address + "'" +
" ,Contact = '" + elector.Contact + "'" +
" ,HasVoted = '" + elector.HasVoted + "'" +
" ,CityID = '" + elector.CityID + "'" +
" ,CenterID = '" + elector.CenterID + "'" +
" WHERE ID = '" + elector.ID + "'";
using (SqlConnection dbConn = new SqlConnection(selectConnection(elector.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = elector.UserID,
ActionPerformed = "Elector Update Error : " + ex.Message,
MethodName = "UpdateElector",
IsError = true
},
elector.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = elector.UserID,
ActionPerformed = "Elector Updated",
MethodName = "UpdateElector",
IsError = false
},
elector.Location);
}
}
}
/// <summary>
/// Service Method To Record All The Consolidated Votes In The Main Database
/// </summary>
/// <param name="loggedInUser"></param>
/// <param name="candidates"></param>
/// <returns></returns>
public static CommonResponse RecordConsolidatedVotes(AuthBaseModel loggedInUser, List <CandidateModel> candidates)
{
CommonResponse commonResponse = new CommonResponse();
using (SqlConnection dbConn = new SqlConnection(selectConnection("main")))
{
try
{
foreach (var candidate in candidates)
{
String SQL = @"INSERT INTO ConsolidatedVotes(CandidateID, CandidateName, TotalNoOfVotes, Year, DateAdded)" +
"VALUES('" + candidate.ID + "','" + candidate.Name + "','" + candidate.Votes + "', GetDate(), GetDate())";
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
cmd.ExecuteNonQuery();
dbConn.Close();
}
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = loggedInUser.UserID,
ActionPerformed = "Error In Recording Consolidated Votes : " + ex.Message,
MethodName = "RecordConsolidatedVotes",
IsError = true
},
loggedInUser.Location);
commonResponse.IsError = true;
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = loggedInUser.UserID,
ActionPerformed = "Record Consolidated Votes Completed!",
MethodName = "RecordConsolidatedVotes",
IsError = false
},
loggedInUser.Location);
}
}
return(commonResponse);
}
/// <summary>
/// Service Method To Check If The Elector Has Voted
/// </summary>
/// <param name="elector"></param>
/// <returns></returns>
public static async Task <bool> ElectorHasVoted(CastVoteModel elector)
{
using (SqlConnection dbConn = new SqlConnection(selectConnection(elector.Location)))
{
var isExistingUserQuery = "SELECT * from Elector WHERE ID ='" + elector.ElectorID + "' AND HasVoted = 1 ";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(isExistingUserQuery, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = elector.UserID,
ActionPerformed = "Elector Has Voted Error : " + ex.Message,
MethodName = "ElectorHasVoted",
IsError = true
},
elector.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = elector.UserID,
ActionPerformed = "Check If Elector Has Voted ",
MethodName = "ElectorHasVoted",
IsError = false
},
elector.Location);
}
}
}
/// <summary>
/// Method to Validate the Token And The User
/// </summary>
/// <param name="token"></param>
/// <param name="userID"></param>
/// <param name="email"></param>
/// <returns></returns>
public static async Task <bool> ValidateUserAndToken(string token, int userID, string email, string location)
{
HttpContext httpContext = HttpContext.Current;
string authHeader = httpContext.Request.Headers["Authorization"];
if (authHeader == null)
{
return(false);
}
else
{
var name = GetName(token);
if (checkIfTokenHasExpired(token))
{
var response = await DeleteTokenInfo(token, userID, email, location);
if (!response.IsError)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = userID,
ActionPerformed = "Error In Deleteing Token Info",
MethodName = "DeleteTokenInfo",
IsError = true
},
location);
}
return(false);
}
else
{
if (AuthorizeUser(token, userID, location))
{
if (email == name)
{
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
}
}
/// <summary>
/// Service Method To Login The User Login Information
/// </summary>
/// <param name="user"></param>
/// <param name="location"></param>
/// <returns></returns>
public static async Task <CommonResponse> UserLoginDetailsAdded(LoggedInUserDTO user, string location)
{
var response = new CommonResponse();
String SQL = "INSERT INTO User_Login(userID," +
"user_role," +
"user_login_os," +
"user_login_date," +
"user_logged_in_timezone," +
"user_logged_in_IP," +
"user_logged_out_date," +
"token)" +
"VALUES('" + user.ID + "','"
+ user.RoleID + "','"
+ user.UserLoginOs + "', GETDATE() ,'"
+ user.UserLoggedInTimezone + "','"
+ user.UserLoggedInIP + "','"
+ user.UserLoggedOutDate + "','"
+ user.Token
+ "')";
try
{
using (SqlConnection dbConn = new SqlConnection(selectConnection("main")))
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
}
}
catch (Exception ex)
{
response.IsError = true;
response.Message = "Error in Registering User!";
ActionLogService.LogAction(new ActionLogModel()
{
UserID = user.UserID,
ActionPerformed = "User Login Error : " + ex.Message,
MethodName = "UserLoginDetailsAdded",
IsError = true
},
location);
}
return(response);
}
/// <summary>
/// Service Method to Add New Ballot
/// </summary>
/// <param name="vote"></param>
/// <returns></returns>
public static async Task <bool> AddNewBallot(BallotModel vote)
{
var dateAdded = DateTime.Now;
vote.Voted = true;
String SQL = "INSERT INTO Ballot(CandidateID, DistrictID, CenterID, Voted, DateCreated)" +
"VALUES('" + vote.CandidateID + "','" + vote.DistrictID + "','" + vote.CenterID + "','" + vote.Voted + "',GetDate())";
using (SqlConnection dbConn = new SqlConnection(selectConnection(vote.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = vote.UserID,
ActionPerformed = "Add New Ballot Error : " + ex.Message,
MethodName = "AddNewBallot",
IsError = true
},
vote.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = vote.UserID,
ActionPerformed = "New Ballot Added",
MethodName = "AddNewBallot",
IsError = false
},
vote.Location);
}
}
}
/// <summary>
/// Authorize User Based on Information in the User Login Table
/// </summary>
/// <param name="token"></param>
/// <param name="userID"></param>
/// <param name="location"></param>
/// <returns></returns>
public static bool AuthorizeUser(string token, int userID, string location)
{
using (SqlConnection dbConn = new SqlConnection(selectConnection("main")))
{
var isExistingUserQuery = "SELECT * from User_Login WHERE Token = '" + token + "' AND userID ='" + userID + "' AND user_logged_out_date IS NOT NULL ";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(isExistingUserQuery, dbConn);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = userID,
ActionPerformed = "User Login Error : " + ex.Message,
MethodName = "UserLoginDetailsAdded",
IsError = true
},
location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = userID,
ActionPerformed = "Check If User Exists ",
MethodName = "AuthorizeUser",
IsError = false
},
location);
}
}
}
/// <summary>
/// Check If The Role Exists
/// </summary>
/// <param name="role"></param>
/// <returns></returns>
public static async Task <bool> RoleExists(RoleModel role)
{
using (SqlConnection dbConn = new SqlConnection(selectConnection("main")))
{
var isExistingUserQuery = "SELECT * from Role WHERE ID ='" + role.ID + "'";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(isExistingUserQuery, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = role.UserID,
ActionPerformed = "Role Exists Error : " + ex.Message,
MethodName = "RoleExists",
IsError = true
},
role.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = role.UserID,
ActionPerformed = "Check If User Exists ",
MethodName = "RoleExists",
IsError = false
},
role.Location);
}
}
}
/// <summary>
/// Service Method To Check If A City Exists
/// </summary>
/// <param name="city"></param>
/// <returns></returns>
public static async Task <bool> CityExists(CityModel city)
{
using (SqlConnection dbConn = new SqlConnection(selectConnection(city.Location)))
{
var query = "SELECT * from City WHERE Name ='" + city.Name + "' OR ID = '" + city.ID + "'";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(query, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = city.UserID,
ActionPerformed = "City Exists Error : " + ex.Message,
MethodName = "CityExists",
IsError = true
},
city.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = city.UserID,
ActionPerformed = "Check If City Exists ",
MethodName = "CityExists",
IsError = false
},
city.Location);
}
}
}
/// <summary>
/// Service Method To Update A Ballot
/// </summary>
/// <param name="ballot"></param>
/// <returns></returns>
public static async Task <bool> UpdateBallot(BallotModel ballot)
{
String SQL = @"UPDATE Ballot SET CandidateID = '" + ballot.CandidateID + "'" +
" DistrictID = '" + ballot.DistrictID + "'" +
" CenterID = '" + ballot.CenterID + "'" +
" Voted = '" + ballot.Voted + "'" +
" WHERE ID = '" + ballot.ID + "')";
using (SqlConnection dbConn = new SqlConnection(selectConnection(ballot.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = ballot.UserID,
ActionPerformed = "Ballot Update Error : " + ex.Message,
MethodName = "UpdateBallot",
IsError = true
},
ballot.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = ballot.UserID,
ActionPerformed = "Ballot Updated",
MethodName = "UpdateBallot",
IsError = false
},
ballot.Location);
}
}
}
/// <summary>
/// Service Method To Check If A Ballot Exists
/// </summary>
/// <param name="ballot"></param>
/// <returns></returns>
public static async Task <bool> BallotExists(BallotModel ballot)
{
using (SqlConnection dbConn = new SqlConnection(selectConnection(ballot.Location)))
{
var isExistingBallotQuery = "SELECT * from Ballot WHERE CandidateID ='" + ballot.CandidateID + "' AND DistrictID = '" + ballot.DistrictID + "' AND CenterID = '" + ballot.CenterID + "'";
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand(isExistingBallotQuery, dbConn);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
reader = null;
ActionLogService.LogAction(new ActionLogModel()
{
UserID = ballot.UserID,
ActionPerformed = "Ballot Exists Error : " + ex.Message,
MethodName = "BallotExists",
IsError = true
},
ballot.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = ballot.UserID,
ActionPerformed = "Check If Ballot Exists ",
MethodName = "BallotExists",
IsError = false
},
ballot.Location);
}
}
}
/// <summary>
/// Service Method To Add A New District
/// </summary>
/// <param name="district"></param>
/// <returns></returns>
public static async Task <bool> AddNewDistrict(DistrictModel district)
{
var Availability = true;
String SQL = "INSERT INTO District(Name,IsAvailable)" +
"VALUES('" + district.Name + "','" + Availability + "')";
using (SqlConnection dbConn = new SqlConnection(selectConnection(district.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = district.UserID,
ActionPerformed = "District Add Error : " + ex.Message,
MethodName = "AddNewDistrict",
IsError = true
},
district.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = district.UserID,
ActionPerformed = "Added New District ",
MethodName = "AddNewDistrict",
IsError = false
},
district.Location);
}
}
}
/// <summary>
/// Add New Role
/// </summary>
/// <param name="role"></param>
/// <returns></returns>
public static async Task <bool> AddNewRole(RoleModel role)
{
var dateCreated = DateTime.Now;
String SQL = "INSERT INTO Role(Name,DateAdded)" +
"VALUES('" + role.Name + "','" + dateCreated + "')";
using (SqlConnection dbConn = new SqlConnection(selectConnection("main")))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = role.UserID,
ActionPerformed = "Role Add Error : " + ex.Message,
MethodName = "AddNewRole",
IsError = true
},
role.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = role.UserID,
ActionPerformed = "Role Added",
MethodName = "AddNewRole",
IsError = false
},
role.Location);
}
}
}
/// <summary>
/// Service Method To Add A New Party
/// </summary>
/// <param name="party"></param>
/// <returns></returns>
public static async Task <bool> AddNewParty(PartyModel party)
{
/// var dateCreated = DateTime.Now;
String SQL = "INSERT INTO Party(Name)" +
"VALUES('" + party.Name + "')";
using (SqlConnection dbConn = new SqlConnection(selectConnection(party.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = party.UserID,
ActionPerformed = "Party Add Error : " + ex.Message,
MethodName = "AddNewParty",
IsError = true
},
party.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = party.UserID,
ActionPerformed = "Party Added",
MethodName = "AddNewParty",
IsError = false
},
party.Location);
}
}
}
/// <summary>
/// Service Method To Add A New Elector
/// </summary>
/// <param name="elector"></param>
/// <returns></returns>
public static async Task <bool> AddNewElector(ElectorModel elector)
{
var dateAdded = DateTime.Now;
String SQL = "INSERT INTO Elector(Name, NIC, DOB, Address, Contact, HasVoted, CityID, CenterID, DateAdded)" +
"VALUES('" + elector.Name + "','" + elector.NIC + "','" + elector.DOB + "','" + elector.Address + "','" + elector.Contact + "','" + elector.HasVoted + "','" + elector.CityID + "','" + elector.CenterID + "',GETDATE())";
using (SqlConnection dbConn = new SqlConnection(selectConnection(elector.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = elector.UserID,
ActionPerformed = "Elector Add Error : " + ex.Message,
MethodName = "AddNewElector",
IsError = true
},
elector.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = elector.UserID,
ActionPerformed = "Elector Added",
MethodName = "AddNewElector",
IsError = false
},
elector.Location);
}
}
}
/// <summary>
/// Service Method To Add A New City
/// </summary>
/// <param name="city"></param>
/// <returns></returns>
public static async Task <bool> AddNewCity(CityModel city)
{
String SQL = "INSERT INTO City(Name,DistrictID)" +
"VALUES('" + city.Name + "','" + city.DistrictID + "')";
using (SqlConnection dbConn = new SqlConnection(selectConnection(city.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = city.UserID,
ActionPerformed = "City Add Error : " + ex.Message,
MethodName = "AddNewCity",
IsError = true
},
city.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = city.UserID,
ActionPerformed = "Added New City",
MethodName = "AddNewCity",
IsError = false
},
city.Location);
}
}
}
/// <summary>
/// Service Method To Add A New Candidate
/// </summary>
/// <param name="candidate"></param>
/// <returns></returns>
public static async Task <bool> AddNewCandidate(CandidateModel candidate)
{
String SQL = @"INSERT INTO Candidate (Name, PartyID, DistrictID, Votes) " +
"VALUES('" + candidate.Name + "', '" + candidate.PartyID + "', '" + candidate.DistrictID + "','" + candidate.Votes + "')";
using (SqlConnection dbConn = new SqlConnection(selectConnection(candidate.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = candidate.UserID,
ActionPerformed = "Candidate Insert Error : " + ex.Message,
MethodName = "AddNewCandidate",
IsError = true
},
candidate.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = candidate.UserID,
ActionPerformed = "Add New Candidate",
MethodName = "AddNewCandidate",
IsError = false
},
candidate.Location);
}
}
}
/// <summary>
/// Service Method to Delete A Candidate
/// </summary>
/// <param name="candidate"></param>
/// <returns></returns>
public static async Task <bool> DeleteCandidate(CandidateModel candidate)
{
String SQL = "DELETE FROM Candidate WHERE Name = '" + candidate.Name + "' AND ID = '" + candidate.ID + "'";
using (SqlConnection dbConn = new SqlConnection(selectConnection(candidate.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex);
ActionLogService.LogAction(new ActionLogModel()
{
UserID = candidate.UserID,
ActionPerformed = "Delete Candidate Error : " + ex.Message,
MethodName = "DeleteCandidate",
IsError = true
},
candidate.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = candidate.UserID,
ActionPerformed = "Delete Candidate",
MethodName = "DeleteCandidate",
IsError = false
},
candidate.Location);
}
}
}
/// <summary>
/// Service Method To Updated when the user has Voted
/// </summary>
/// <param name="elector"></param>
/// <returns></returns>
public static async Task <bool> ElectorVoted(CastVoteModel elector)
{
String SQL = @"UPDATE Elector SET HasVoted = 1" +
" WHERE ID = '" + elector.ElectorID + "'";
using (SqlConnection dbConn = new SqlConnection(selectConnection(elector.Location)))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = elector.UserID,
ActionPerformed = "Elector Voted Error : " + ex.Message,
MethodName = "ElectorVoted",
IsError = true
},
elector.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = elector.UserID,
ActionPerformed = "Elector Voted",
MethodName = "ElectorVoted",
IsError = false
},
elector.Location);
}
}
}
/// <summary>
/// Update Role
/// </summary>
/// <param name="role"></param>
/// <returns></returns>
public static async Task <bool> UpdateRole(RoleModel role)
{
String SQL = "UPDATE Role SET Name = '" + role.Name + "' WHERE ID = '" + role.ID + "'";
using (SqlConnection dbConn = new SqlConnection(selectConnection("main")))
{
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQL;
cmd.Connection = dbConn;
await cmd.ExecuteNonQueryAsync();
dbConn.Close();
return(true);
}
catch (Exception ex)
{
ActionLogService.LogAction(new ActionLogModel()
{
UserID = role.UserID,
ActionPerformed = "Role Update Error : " + ex.Message,
MethodName = "UpdateRole",
IsError = true
},
role.Location);
return(false);
}
finally
{
dbConn.Close();
ActionLogService.LogAction(new ActionLogModel()
{
UserID = role.UserID,
ActionPerformed = "Role Updated",
MethodName = "UpdateRole",
IsError = false
},
role.Location);
}
}
}
