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