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