public string RemoveBothSearchingRecords(string buyerReferralCode, string matchedBuyerReferralCode)
{
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var deleteSearchingCommand = $@"delete from UsersBuying where UserId in (select Id from AspNetUsers where Email in (@buyerReferralCode, @matchedBuyerReferralCode))";
using (var command = new SqlCommand(deleteSearchingCommand, SqlConnection))
{
command.Parameters.AddWithValue("@buyerReferralCode", buyerReferralCode);
command.Parameters.AddWithValue("@matchedBuyerReferralCode", matchedBuyerReferralCode);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//do nothing
}
}
}
}
}
catch (Exception e)
{
return($"DB Call Failed in RemoveBothSearchingRecords function in the deleteSearchingCommand: {e}");
}
return("Records Deleted Successfully");
}
public string GetNumberOfUsersSearching()
{
errorRepository = new ErrorRepository();
var numberOfUsersBuying = "";
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var getBuyingCountCommand = $@"select count(*) from UsersBuying";
using (var command = new SqlCommand(getBuyingCountCommand, SqlConnection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
numberOfUsersBuying = reader[0].ToString();
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError("Home Page", $"DB Call Failed in GetNumberOfUsersSearching function in the getBuyingCountCommand: {e}");
return("Error Occurred.");
}
return(numberOfUsersBuying);
}
public bool RemoveSearchingRecord(string referralCode)
{
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var deleteSearchingCommand = $@"delete from UsersBuying where UserId in (select Id from AspNetUsers where Email = @referralCode)";
using (var command = new SqlCommand(deleteSearchingCommand, SqlConnection))
{
command.Parameters.AddWithValue("@referralCode", referralCode);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//do nothing
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(referralCode, $"DB Call Failed in RemoveSearchingRecord function in the deleteSearchingCommand: {e}");
return(false);
}
return(true);
}
//we will need to call this right beofre we start searching for users then another when we find a possible match
//we will need to check if thereis more than one record here, if so, there was a race con
public string[] CheckIfUserAlreadyMatched(string referralCode)
{
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var getAlreadyMatchedCommand = $@"DECLARE @UserId uniqueidentifier;
DECLARE @Count int;
select @UserId = UserId, @Count = @@ROWCOUNT from UsersMatched
join AspNetUsers on AspNetUsers.Id = UsersMatched.UserMatchedWith
where Email = '{referralCode}'
select @Count, Email, Username from AspNetUsers where Id = @UserId";
using (var command = new SqlCommand(getAlreadyMatchedCommand, SqlConnection))
{
command.Parameters.AddWithValue("@referralCode", referralCode);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//we wrote the wuery this way because we need the userId of the user searching
//in order to get the user matched with userid and referral code
if (reader.HasRows)
{
if (int.Parse(reader[0].ToString()) > 1)
{
return(new string[] { "Error: User matched with more than one person." });
}
return(new string[] { reader[1].ToString(), reader[2].ToString() });
}
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(referralCode, $"DB Call Failed in CheckIfUserAlreadyMatched function in the getAlreadyMatchedCommand: {e}");
return(new string[] { "Error Occurred." });
}
return(new string[] { "User is not already matched." });
}
public bool InsertTrackingRecord(string referralCode, string trackingType, string description)
{
var trackingTypeId = "";
errorRepository = new ErrorRepository();
switch (trackingType)
{
case "User Reported": trackingTypeId = "96999E58-ABB3-42FD-A0DE-5DD0D45D0E7D"; break;
case "Started Searching": trackingTypeId = "032B2C5F-F983-48DD-9C89-7629BBC2C7F3"; break;
case "Found Match": trackingTypeId = "3696C17B-451B-484A-9161-F8149FE3579E"; break;
case "User Banned": trackingTypeId = "0ED601B8-6EC7-40FE-ABBE-5E737E9F88B1"; break;
case "User Stopped Searching": trackingTypeId = "000A9275D-AEE5-41DA-B89E-E86E25C17895"; break;
}
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var insertTrackerCommand = $@"insert into UserTracking
values(NEWID(), @referralCode, '{trackingTypeId}', '{description}', GETDATE())";
using (var command = new SqlCommand(insertTrackerCommand, SqlConnection))
{
command.Parameters.Add("@referralCode", SqlDbType.VarChar);
command.Parameters["@referralCode"].Value = referralCode;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(referralCode, $"DB Call Failed in InsertTrackingRecord function in the insertTrackerCommand: {e}");
return(false);
}
return(true);
}
public string[] FindAnotherUserSearching(string referralCode)
{
errorRepository = new ErrorRepository();
var matchedUserReferralCode = "";
var matchedUserUsername = "";
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var getSearchingCommand = $@"select top 1 Email, Username from UsersBuying
join AspNetUsers on AspNetUsers.Id = UsersBuying.UserId
where AspNetUsers.Email <> @referralCode
order by CreatedOn desc";
using (var command = new SqlCommand(getSearchingCommand, SqlConnection))
{
command.Parameters.Add("@referralCode", SqlDbType.VarChar);
command.Parameters["@referralCode"].Value = referralCode;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.HasRows)
{
matchedUserReferralCode = reader[0].ToString();
matchedUserUsername = reader[1].ToString();
}
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(referralCode, $"DB Call Failed in FindAnotherUserSearching function in the getSearchingCommand: {e}");
return(new[] { "Error Occurred." });
}
return(new[] { matchedUserReferralCode, matchedUserUsername });
}
//we need this function to ensure a user can only report another once
public string CheckIfUserIsAlreadyReportedByUser(string reportingReferralCode, string reportedReferralCode)
{
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var checkReportCommand = $@"DECLARE @ReportingUserId uniqueidentifier;
DECLARE @ReportedUserId uniqueidentifier;
Select @ReportingUserId = Id from AspNetUsers where Email = @reportingReferralCode
Select @ReportedUserId = Id from AspNetUsers where Email = @reportedReferralCode
select * from UsersReported where ReportedBy = @reportingReferralCode and UserReported = @reportedReferralCode";
using (var command = new SqlCommand(checkReportCommand, SqlConnection))
{
command.Parameters.AddWithValue("@reportingReferralCode", reportingReferralCode);
command.Parameters.AddWithValue("@reportedReferralCode", reportedReferralCode);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.HasRows)
{
return("true");
}
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(reportingReferralCode, $"DB Call Failed in CheckIfUserIsAlreadyReportedByUser function in the checkReportCommand: {e}");
return("Error Occurred.");
}
return("false");
}
public bool CheckIfTwoUsersExists(string firstReferralCode, string secondReferralCode)
{
var bothUsersExist = false;
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var checkTwoUsersCommand = $@"select * from AspNetUsers where Email = @firstReferralCode and Email = @secondReferralCode";
using (var command = new SqlCommand(checkTwoUsersCommand, SqlConnection))
{
command.Parameters.Add("@firstReferralCode", SqlDbType.VarChar);
command.Parameters["@firstReferralCode"].Value = firstReferralCode;
command.Parameters.Add("@secondReferralCode", SqlDbType.VarChar);
command.Parameters["@secondReferralCode"].Value = secondReferralCode;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.HasRows)
{
bothUsersExist = true;
}
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(firstReferralCode, $"DB Call Failed in CheckIfTwoUsersExists function in the checkTwoUsersCommand: {e}");
return(bothUsersExist);
}
return(bothUsersExist);
}
public string InsertUserMatchedRecord(string referralCode, string userMatchedReferralCode)
{
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var insertMatchedCommand = $@"DECLARE @UserId uniqueidentifier;
DECLARE @UserMatchedId uniqueidentifier;
Select @UserId = Id from AspNetUsers where Email = @referralCode
Select @UserMatchedId = Id from AspNetUsers where Email = @userMatchedReferralCode
insert into UsersMatched
values(@UserId, @UserMatchedId, GETDATE())";
using (var command = new SqlCommand(insertMatchedCommand, SqlConnection))
{
command.Parameters.AddWithValue("@referralCode", referralCode);
command.Parameters.AddWithValue("@userMatchedReferralCode", userMatchedReferralCode);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//do nothing
}
}
}
}
}
catch (Exception e)
{
return($"DB Call Failed in InsertUserMatchedRecord function in the insertMatchedCommand: {e}");
}
return("Matched Record Inserted Successfully");
}
public string CheckIfUserIsAlreadySearching(string referralCode)
{
errorRepository = new ErrorRepository();
var alreadySearching = "false";
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var getAlreadySearchingCommand = $@"select * from AspNetUsers
join UsersBuying on UsersBuying.UserId = AspNetUsers.Id
where Email = @referralCode";
using (var command = new SqlCommand(getAlreadySearchingCommand, SqlConnection))
{
command.Parameters.Add("@referralCode", SqlDbType.VarChar);
command.Parameters["@referralCode"].Value = referralCode;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.HasRows)
{
alreadySearching = "true";
}
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(referralCode, $"DB Call Failed in CheckIfUserIsAlreadySearching function in the getAlreadySearchingCommand: { e }");
return($"");
}
return(alreadySearching);
}
public bool InsertReportRecord(string reportingReferralCode, string reportedReferralCode)
{
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var insertReportCommand = $@"DECLARE @ReportingUserId uniqueidentifier;
DECLARE @ReportedUserId uniqueidentifier;
Select @ReportingUserId = Id from AspNetUsers where Email = @reportingReferralCode
Select @ReportedUserId = Id from AspNetUsers where Email = @reportedReferralCode
insert into UsersReported
values(@ReportingUserId, @ReportedUserId, GETDATE())";
using (var command = new SqlCommand(insertReportCommand, SqlConnection))
{
command.Parameters.AddWithValue("@reportingReferralCode", reportingReferralCode);
command.Parameters.AddWithValue("@reportedReferralCode", reportedReferralCode);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//do nothing
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(reportingReferralCode, $"DB Call Failed in InsertReportRecord function in the insertReportCommand: {e}");
return(false);
}
return(true);
}
public bool IsUserBanned(string referralCode)
{
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var getBannedUserCommand = $@"DECLARE @UserId uniqueidentifier;
Select @UserId = Id from AspNetUsers where Email = @referralCode
select * from BannedUsers where UserId = @UserId)";
using (var command = new SqlCommand(getBannedUserCommand, SqlConnection))
{
command.Parameters.AddWithValue("@referralCode", referralCode);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.HasRows)
{
return(true);
}
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(referralCode, $"DB Call Failed in IsUserBanned function in the getBannedUserCommand: {e}");
return(false);
}
return(false);
}
public string GetNumberOfReportsByUser(string referralCode)
{
var timesReported = "";
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var getReportsCommand = $@"DECLARE @UserId uniqueidentifier;
Select @UserId = Id from AspNetUsers where Email = @referralCode
select count(*) from UsersReported where UserReported = @UserId";
using (var command = new SqlCommand(getReportsCommand, SqlConnection))
{
command.Parameters.AddWithValue("@referralCode", referralCode);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
timesReported = reader[0].ToString();
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(referralCode, $"DB Call Failed in GetNumberOfReportsByUser function in the getReportsCommand: {e}");
return("Error Occurred.");
}
return(timesReported);
}
//we have to return strings in these functions because we want to return string errors.
public string CheckIfUserExists(string referralCode)
{
var userExists = "false";
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var checkUserCommand = $@"select * from AspNetUsers where Email = @referralCode";
using (var command = new SqlCommand(checkUserCommand, SqlConnection))
{
command.Parameters.Add("@referralCode", SqlDbType.VarChar);
command.Parameters["@referralCode"].Value = referralCode;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.HasRows)
{
userExists = "true";
}
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(referralCode, $"DB Call Failed in CheckIfUserExists function in the checkUserCommand: {e}");
return("Error Occurred.");
}
return(userExists);
}
public bool AddNewUser(string referralCode, string username)
{
errorRepository = new ErrorRepository();
try
{
using (SqlConnection)
{
SqlConnection.ConnectionString = ConnectionString;
SqlConnection.Open();
var insertUserCommand = $@"insert into AspNetUsers
values(NEWID(), @referralCode, 0, null, null, null, 0, 0, null, 0, 0, @userName)";
using (var command = new SqlCommand(insertUserCommand, SqlConnection))
{
command.Parameters.Add("@referralCode", SqlDbType.VarChar);
command.Parameters["@referralCode"].Value = referralCode;
command.Parameters.Add("@userName", SqlDbType.VarChar);
command.Parameters["@userName"].Value = username;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//do nothing
}
}
}
}
}
catch (Exception e)
{
errorRepository.LogError(referralCode, $"DB Call Failed in AddNewUser function in the insertUserCommand: {e}");
return(false);
}
return(true);
}