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