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);
}
//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 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);
}