/// <summary> /// Assign to the given user listing all the solutions that exist as the 'default' ones for the /// listing job title /// </summary> /// <param name="userListingID"></param> public static void SetDefaultSolutionsForListing(int userListingID) { using (var db = new LcDatabase()) { var listing = db.QuerySingle(@" SELECT TOP 1 userID, positionID as jobTitleID, languageID, countryID FROM userprofilepositions WHERE userListingID=@0", userListingID); if (listing != null) { var defaultSolutions = db.Query(@" SELECT solutionID, displayRank FROM JobTitleSolution WHERE DefaultSelected = 1 AND jobTitleID=@0 AND languageID=@1 AND countryID=@2", listing.jobTitleID, listing.languageID, listing.countryID); var locale = Locale.From(listing.languageID, listing.countryID); db.Execute("BEGIN TRANSACTION"); foreach (var solution in defaultSolutions) { Set(listing.userID, userListingID, solution.solutionID, solution.displayRank, locale, db); } db.Execute("COMMIT TRANSACTION"); } } }
public static int Set(UserPosting entry, Locale locale, LcDatabase sharedDb = null) { using (var db = new LcDatabase(sharedDb)) { var neededSpecializationsText = string.Join(",", entry.neededSpecializationIDs); var desiredSpecializationsText = string.Join(",", entry.desiredSpecializationIDs); db.Execute("BEGIN TRANSACTION"); var id = 0; if (entry.userPostingID > 0) { db.QueryValue(sqlUpdate, entry.userID, entry.userPostingID, entry.title, neededSpecializationsText, desiredSpecializationsText ); id = entry.userPostingID; foreach (var qr in entry.questionsResponses) { qr.userPostingID = id; UserPostingQuestionResponse.Update(qr, db); } } else { id = (int)db.QueryValue(sqlInsert, entry.userID, entry.solutionID, entry.postingTemplateID, entry.statusID, entry.title, neededSpecializationsText, desiredSpecializationsText, locale.languageID, locale.countryID, entry.userID ); foreach (var qr in entry.questionsResponses) { qr.userPostingID = id; UserPostingQuestionResponse.Insert(qr, db); } } db.Execute("COMMIT TRANSACTION"); return(id); } }
public static void SetList(int userID, int userListingID, IEnumerable <int> sortedSolutions, Locale locale) { using (var db = new LcDatabase()) { db.Execute("BEGIN TRANSACTION"); db.Execute(sqlSoftDeleteByUserListing, userID, userListingID, locale.languageID, locale.countryID); var displayRank = 1; foreach (var solutionID in sortedSolutions) { Set(userID, userListingID, solutionID, displayRank, locale, db); displayRank += 1; } db.Execute("COMMIT TRANSACTION"); } }
/// <summary> /// Remove all current authorization tokens for the user /// </summary> /// <param name="userID"></param> static void RemoveUserAuthorizations(int userID) { using (var db = new LcDatabase()) { db.Execute("UPDATE authorizations SET DeletedDate=@1 WHERE UserID=@0", userID, DateTimeOffset.Now); } }
/// <summary> /// Inserts specializations proposed by the user by names, since couldn't find existent ones that satisfies /// its needs. This records are tagged as user-generated with the userID and pending of public approval /// (available on its profile, but not still for other users to pick them). /// </summary> /// <param name="list"></param> /// <param name="solutionID"></param> /// <param name="locale"></param> /// <param name="userID"></param> /// <returns></returns> public static List <int> InsertProposedSpecializations(IEnumerable <string> list, int solutionID, Locale locale, int userID) { var newSpecializationsList = new List <int>(); using (var db = new LcDatabase()) { db.Execute("BEGIN TRANSACTION"); foreach (var name in list) { var id = InsertUserGenerated(name, solutionID, userID, locale, db); newSpecializationsList.Add(id); } db.Execute("COMMIT TRANSACTION"); } return(newSpecializationsList); }
/// <summary> /// Removes the given authorization token /// </summary> /// <param name="auth"></param> static void RemoveUserAuthorization(UserAuthorization auth) { using (var db = new LcDatabase()) { db.Execute("UPDATE authorizations SET DeletedDate=@0 WHERE UserID=@1 AND Token=@2", DateTimeOffset.Now, auth.userID, auth.token); } }
public static bool Update(UserExternalListing externalListing) { externalListing.AutoRegisterUserJobTitles(); var sqlUpdate = @" UPDATE UserExternalListing SET title = @2, jobTitles = @3, notes = @4, UpdatedDate = getdate() WHERE UserID = @0 AND UserExternalListingID = @1 AND Active = 1 "; using (var db = new LcDatabase()) { var affected = db.Execute(sqlUpdate, externalListing.userID, externalListing.userExternalListingID, externalListing.title, Newtonsoft.Json.JsonConvert.SerializeObject(externalListing.jobTitles), externalListing.notes ); // Task done? Almost a record must be affected to be a success return(affected > 0); } }
/// <summary> /// Delete an education record. /// </summary> /// <param name="userID"></param> /// <param name="educationID"></param> public static void Delete(int userID, int educationID) { using (var db = new LcDatabase()) { db.Execute(sqlDelete, educationID, userID); } }
public static bool Update(UserJobTitle userJobTitle) { userJobTitle.ValidateAndFixBookingPolicies(); var sqlUpdate = @" UPDATE UserProfilePositions SET PositionIntro = @4, CancellationPolicyID = @5, InstantBooking = @6, collectPaymentAtBookMeButton = @7, UpdatedDate = getdate() WHERE UserID = @0 AND PositionID = @1 AND LanguageID = @2 AND CountryID = @3 "; using (var db = new LcDatabase()) { var affected = db.Execute(sqlUpdate, userJobTitle.userID, userJobTitle.jobTitleID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID(), userJobTitle.intro, userJobTitle.cancellationPolicyID, userJobTitle.instantBooking, userJobTitle.collectPaymentAtBookMeButton ); // Task done? Almost a record must be affected to be a success return(affected > 0); } }
/// <summary> /// Delete an entry. /// </summary> /// <param name="userID"></param> /// <param name="earningsEntryID"></param> public static void Delete(int userID, int earningsEntryID) { using (var db = new LcDatabase()) { db.Execute(sqlDelete, userID, earningsEntryID); } }
/// <summary> /// Delete an entry. Only if user generated /// </summary> /// <param name="userID"></param> /// <param name="userBadgeID"></param> public static void Delete(int userID, int userBadgeID, int languageID, int countryID, bool byAdmin = false) { using (var db = new LcDatabase()) { var sql = sqlDelete + (byAdmin ? "" : sqlRestrictToUserCreated); db.Execute(sql, userID, languageID, countryID, userBadgeID); } }
/// <summary> /// Saves a record with the service professional reaction to a user posting. It does NOT send any message on cases /// that should being needed ('apply'), check LcMessaging for one to run manually. /// </summary> /// <param name="userPostingID"></param> /// <param name="serviceProfessionalUserID"></param> /// <param name="reaction"></param> /// <param name="message"></param> public static void SetServiceProfessionalReaction( int userPostingID, int serviceProfessionalUserID, LcEnum.UserPostingReactionType reaction, string message = null) { using (var db = new LcDatabase()) { db.Execute(sqlSetReaction, userPostingID, serviceProfessionalUserID, (short)reaction, message); } }
public static LcRest.UserProfile ConfirmAccount(string confirmationCode) { using (var db = new LcDatabase()) { var userID = (int?)db.QueryValue(@" SELECT UserId FROM webpages_Membership WHERE ConfirmationToken = @0 ", confirmationCode); if (userID.HasValue) { // Check if the account requires to complete the sign-up: // - it happens for user whose record was created by a professional (added him as client) // - so, the user has an accountStatusID serviceProfessional's client // -> On that case, we cannot confirm the account yet, since we need from the client to // complete the sign-up, generating a password by itself. We just follow up returning the user // profile data that can be used to pre-populate the 'client activation' sign-up form. var user = LcRest.UserProfile.Get(userID.Value); if (user.accountStatusID != (int)LcEnum.AccountStatus.serviceProfessionalClient) { // User can confirm it's account, proceed: db.Execute(@" UPDATE webpages_Membership SET ConfirmationToken = null, IsConfirmed = 1 WHERE ConfirmationToken like @0 AND UserID = @1 ", confirmationCode, userID); // In the lines above, we cannot use the aps.net WebSecurity standard logic: // //WebSecurity.ConfirmAccount(confirmationToken) // because the change of confirmation first-time optional step, alert at dashboard // and (sometimes this business logic changes) required for second and following login attempts. // Because of this a hack is done on provider-sign-up login over the IsConfirmed field, and this becomes the ConfirmAccount // standard method unuseful (do nothing, really, because it checks IsConfirmed field previuosly and ever is true, doing nothing -we need set to null // ConfirmationToken to off the alert-). On success, ConfirmationToken is set to null and IsConfirmed to 1 (true), supporting both cases, when IsConfirmed is // already true and when no. db.Execute("EXEC TestAlertVerifyEmail @0", userID); // IMPORTANT: Since 2012-09-27, issue #134, Auto-login is done on succesful confirmation; // some code after next lines (comented as 'starndard logic' will not be executed, and some html, but preserved as documentation) // Confirmation sucess, we need user name (email) to auto-login: FormsAuthentication.SetAuthCookie(user.email, false); } return(user); } } return(null); }
/// <summary> /// /// </summary> /// <param name="userID"></param> /// <param name="userPostingID"></param> /// <param name="languageID"></param> /// <param name="countryID"></param> public static void Delete(int userID, int userPostingID, int languageID, int countryID) { using (var db = new LcDatabase()) { var sql = sqlDelete; db.Execute(sql, userID, languageID, countryID, userPostingID); } }
public static void Update(UserPostingQuestionResponse entry, LcDatabase sharedDb = null) { using (var db = new LcDatabase(sharedDb)) { db.Execute(sqlUpdate, entry.userPostingID, entry.questionID, Newtonsoft.Json.JsonConvert.SerializeObject(entry.responses)); } }
public static void Set(UserPaymentPlan data, LcDatabase sharedDb = null) { using (var db = new LcDatabase(sharedDb)) { db.Execute(sqlSet, data.userPaymentPlanID, data.userID, data.subscriptionID, data.paymentPlan.ToString(), data.paymentMethod, data.paymentPlanLastChangedDate, data.nextPaymentDueDate, data.nextPaymentAmount, data.firstBillingDate, data.subscriptionEndDate, data.paymentMethodToken, data.paymentExpiryDate, data.planStatus, data.daysPastDue ); try { // Set OwnerStatus if (IsPartnershipPlan(data.paymentPlan)) { var ow = new Owner(); ow.userID = data.userID; ow.statusID = (int)OwnerStatus.notYetAnOwner; Owner.Set(ow); } else { // Run Membership Checks to enable/disable member (OwnerStatus update) UserProfile.CheckAndSaveOwnerStatus(data.userID); } } catch (Exception ex) { // An error checking status must NOT prevent us from saving/creating // the payment-plan, but we must notify staff so we can take manual action // to fix the error and run the check again for this user try { LcLogger.LogAspnetError(ex); LcMessaging.NotifyError("UserPaymentPlan.Set->UserProfile.CheckAndSaveOwnerStatus::userID=" + data.userID, System.Web.HttpContext.Current.Request.RawUrl, ex.ToString()); } catch { // Prevent cancel paymentplan creation/update because of email or log failing. Really strange // and webhook-scheduleTask for subscriptions would attempt again this. } } } }
/// <summary> /// Change a posting status, by the author, but only posible if status is editable (0:incomplete, 1:active) /// </summary> /// <param name="userID"></param> /// <param name="userPostingID"></param> /// <param name="status"></param> public static void SetStatus(int userID, int userPostingID, LcEnum.UserPostingStatus status) { using (var db = new LcDatabase()) { db.Execute(@"UPDATE UserPosting SET StatusID = @2, updatedDate = getdate() WHERE UserPostingID = @0 AND userID = @1 AND StatusID < 2", userPostingID, userID, (short)status); } }
public static void Insert(UserPostingQuestionResponse entry, LcDatabase sharedDb = null) { using (var db = new LcDatabase(sharedDb)) { db.Execute(sqlInsert, entry.userPostingID, entry.questionID, entry.questionTypeID, entry.question, entry.label, entry.helpBlock, entry.legend, Newtonsoft.Json.JsonConvert.SerializeObject(entry.options), Newtonsoft.Json.JsonConvert.SerializeObject(entry.responses), Newtonsoft.Json.JsonConvert.SerializeObject(entry.branchLogic)); } }
/// <summary> /// Sets the OnboardingStep of the user to 'welcome', so can start the onboarding process /// </summary> /// <param name="userID"></param> private static void StartOnboardingForUser(int userID) { using (var db = new LcDatabase()) { db.Execute(@" UPDATE Users SET OnboardingStep = 'welcome' WHERE UserID = @0 ", userID); } }
public static void Set(OwnerAcknowledgment data) { using (var db = new LcDatabase()) { db.Execute(sqlSet, data.userID, data.dateAcknowledged, data.acknowledgedFromIP, data.detectedIPs ); } }
public static void Set(OwnerStatusHistory data, LcDatabase database = null) { using (var db = new LcDatabase(database)) { db.Execute(sqlSet, data.userID, data.ownerStatusID, data.ownerStatusChangedDate, data.ownerStatusChangedBy ); } }
/// <summary> /// Soft delete the external listing /// </summary> /// <param name="userID"></param> /// <param name="userExternalListingID"></param> /// <returns></returns> public static bool Delete(int userID, int userExternalListingID) { using (var db = new LcDatabase()) { // Set StatusID to 0 'deleted by user' int affected = db.Execute(@" UPDATE UserExternalListing SET Active = 0 WHERE UserID = @0 AND userExternalListingID = @1 ", userID, userExternalListingID); // Task done? Almost a record must be affected to be a success return(affected > 0); } }
public static void Set(UserFeePayment data) { using (var db = new LcDatabase()) { db.Execute(sqlSet, data.userID, data.paymentDate, data.paymentAmount, data.paymentMethod, data.paymentPlan, data.paymentTransactionID, data.paymentStatus ); } }
/// <summary> /// Creates an authorization record for the given userID using the current /// token generator. /// </summary> /// <param name="userID"></param> public static string RegisterAuthorizationForUser(int userID) { RemovesCurrentUserAuthorization(); var token = CreateUserGuidToken(userID); var userAgent = HttpContext.Current.Request.UserAgent; var userAddress = HttpContext.Current.Request.UserHostAddress; using (var db = new LcDatabase()) { db.Execute(@"INSERT INTO authorizations (Token, UserID, Scope, CreatedDate, ClientAddress, UserAgent) VALUES (@0, @1, @2, @3, @4, @5)", token, userID, "", DateTimeOffset.Now, userAddress, userAgent); } return(token); }
public static void Set(UserPaymentPlan data) { using (var db = new LcDatabase()) { db.Execute(sqlSet, data.userID, data.paymentPlan, data.paymentMethod, data.paymentPlanLastChangedDate, data.nextPaymentDueDate, data.nextPaymentAmount, data.lastPaymentDate, data.lastPaymentAmount, data.totalPastDueAmount ); } }
public static bool Delete(int userID, int jobTitleID) { using (var db = new LcDatabase()) { // Set StatusID to 0 'deleted by user' int affected = db.Execute(@" UPDATE UserProfilePositions SET StatusID = 0, UpdatedDate = getdate() WHERE UserID = @0 AND PositionID = @1 AND LanguageID = @2 AND CountryID = @3 ", userID, jobTitleID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID()); // Task done? Almost a record must be affected to be a success return(affected > 0); } }
public static void Set(ServiceProfessionalClient spc, Database sharedDb = null) { using (var db = new LcDatabase(sharedDb)) { db.Execute(@" IF EXISTS (SELECT * FROM ServiceProfessionalClient WHERE ServiceProfessionalUserID = @0 AND ClientUserID = @1) UPDATE ServiceProfessionalClient SET -- Passing null, will keep current notes; to delete them, pass in an empty string NotesAboutClient = coalesce(@2, NotesAboutClient), UpdatedDate = getdate() -- Other fields are not allowed to be updated ,DeletedByServiceProfessional = @5 WHERE ServiceProfessionalUserID = @0 AND ClientUserID = @1 ELSE INSERT INTO ServiceProfessionalClient ( ServiceProfessionalUserID, ClientUserID, NotesAboutClient, ReferralSourceID, CreatedByBookingID, CreatedDate, UpdatedDate, Active ) VALUES ( @0, @1, coalesce(@2, ''), @3, @4, getdate(), getdate(), 1 -- Active ) ", spc.serviceProfessionalUserID, spc.clientUserID, spc.notesAboutClient, spc.referralSourceID, spc.createdByBookingID, spc.deletedByServiceProfessional ); } }
private static void Update(UserLicenseCertification item, bool internalUpdate = false) { // TODO, for an admin dashboard, we may need to implement internalUpdate allowing for update of all non-ID fields. if (internalUpdate) { throw new NotImplementedException("Internal update not implemented"); } using (var db = new LcDatabase()) { db.Execute(@" UPDATE userlicensecertifications SET submittedImageLocalURL = @3 WHERE ProviderUserID = @0 AND PositionID = @1 AND userLicenseCertificationID = @2 ", item.userID, item.jobTitleID, item.userLicenseCertificationID, item.submittedImageLocalURL); } }
/// <summary> /// Deactivation consists in switch the status of the profile /// to 'manually disabled / private'. /// </summary> /// <param name="userID"></param> /// <param name="jobTitleID"></param> /// <returns></returns> public static bool Deactivate(int userID, int jobTitleID) { using (var db = new LcDatabase()) { // It just update StatusID to 3 'private profile, manual activation' var affected = db.Execute(@" UPDATE UserProfilePositions SET StatusID = 3, UpdatedDate = getdate() WHERE UserID = @0 AND PositionID = @1 AND LanguageID = @2 AND CountryID = @3 ", userID, jobTitleID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID()); // Task done? Almost a record must be affected to be a success return(affected > 0); } }
public static void Set(UserPaymentPlan data) { using (var db = new LcDatabase()) { db.Execute(sqlSet, data.userPaymentPlanID, data.userID, data.subscriptionID, data.paymentPlan.ToString(), data.paymentMethod, data.paymentPlanLastChangedDate, data.nextPaymentDueDate, data.nextPaymentAmount, data.firstBillingDate, data.subscriptionEndDate, data.paymentMethodToken, data.paymentExpiryDate, data.planStatus, data.daysPastDue ); } }