public static int?GetUserIdByAuthorizationToken(string token)
{
using (var db = new LcDatabase())
{
return((int?)db.QueryValue("SELECT UserID FROM authorizations WHERE DeletedDate is null AND token like @0", token));
}
}
public static IEnumerable <UserLicenseCertification> GetList(int userID, int jobTitleID, int languageID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetList, userID, jobTitleID, languageID).Select(FromDB));
}
}
/// <summary>
/// Save the given pricing summary and returns a copy of the record from database after
/// that (so it includes any generated IDs, dates,..)
/// </summary>
/// <param name="data"></param>
public static PricingSummary Set(PricingSummary data, Database sharedDb = null)
{
using (var db = new LcDatabase(sharedDb))
{
PricingSummary newData = FromDB(db.QuerySingle(sqlInsertItem,
data.pricingSummaryID,
data.serviceDurationMinutes, data.firstSessionDurationMinutes,
data.subtotalPrice, data.clientServiceFeePrice,
data.totalPrice, data.serviceFeeAmount,
data.cancellationDate, data.cancellationFeeCharged,
data.firstTimeServiceFeeFixed,
data.firstTimeServiceFeePercentage,
data.paymentProcessingFeePercentage,
data.paymentProcessingFeeFixed,
data.firstTimeServiceFeeMaximum,
data.firstTimeServiceFeeMinimum
));
if (data.details != null)
{
// Set original with details,
// since the saving needs to set-up the generated IDs
newData.details = data.details;
// After save it, gets the just generated records, with any timestamp.
newData.details = SetDetails(newData, sharedDb);
}
return(newData);
}
}
/// <summary>
/// Get the record by user
/// </summary>
/// <param name="userDI"></param>
/// <returns></returns>
public static OwnerAcknowledgment Get(int userID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGet, userID)));
}
}
public static UserLicenseCertification Get(int userID, int jobTitleID, int languageID, int userLicenseCertificationID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, userID, jobTitleID, languageID, userLicenseCertificationID)));
}
}
/// <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);
}
}
/// <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);
}
}
public static bool HasMembershipRecord(int userID)
{
using (var db = new LcDatabase())
{
return(db.QueryValue("SELECT userid FROM webpages_Membership WHERE userid = @0", userID) != null);
}
}
/// <summary>
/// Get active subscriptions, all ones with a non final status (Active, Pending, Past_due)
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public static IEnumerable <UserPaymentPlan> QueryActiveSubscriptions(LcDatabase db)
{
return(db.Query(sqlSelectAll + sqlConditionOnlyActivePlans + " WHERE planStatus IN (@0, @1, @2)",
Braintree.SubscriptionStatus.PENDING.ToString(),
Braintree.SubscriptionStatus.PAST_DUE.ToString(),
Braintree.SubscriptionStatus.ACTIVE.ToString())
.Select(FromDB));
}
public static UserFeePayment GetBySubscriptionTransaction(int userID,
string subscriptionID, string paymentTransactionID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetBySubscriptionTransaction, userID, subscriptionID, paymentTransactionID)));
}
}
/// <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);
}
}
/// <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);
}
}
/// <summary>
/// Provides a list of service professionals suggested to apply to a given userPostingID
/// </summary>
/// <param name="userPostingID"></param>
/// <param name="languageID"></param>
/// <param name="countryID"></param>
/// <returns></returns>
public static IEnumerable <UserEmail> ListSuggestedProfessionals(int userPostingID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlSelectSuggestedProfessionals, userPostingID, languageID, countryID)
.Select((r) => (UserEmail)UserEmail.FromDB(r)));
}
}
/// <summary>
/// List postings of an user
/// </summary>
/// <param name="userID"></param>
/// <param name="languageID"></param>
/// <param name="countryID"></param>
/// <returns></returns>
public static IEnumerable <UserPosting> List(int userID, int languageID, int countryID, bool fillLinks)
{
using (var db = new LcDatabase())
{
var sql = sqlSelect + sqlWhereUser + sqlOrderByDate;
return(db.Query(sql, userID, languageID, countryID).Select((r) => (UserPosting)FromDB(r, fillLinks, fillQuestionsResponses: fillLinks)));
}
}
public static UserPosting Get(int userID, int userPostingID, int languageID, int countryID, bool fillLinks)
{
using (var db = new LcDatabase())
{
var sql = sqlSelect + sqlWhereUserAndID;
return(FromDB(db.QuerySingle(sql, userID, languageID, countryID, userPostingID), fillLinks, fillQuestionsResponses: fillLinks));
}
}
public static UserEarningsEntry Get(int userID, int earningsEntryID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
var sql = (sqlSelect + sqlAndID).Replace("@LIMIT", "1");
return(FromDB(db.QuerySingle(sql, userID, languageID, countryID, earningsEntryID)));
}
}
public static UserBadge Get(int userID, int userBadgeID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
var sql = sqlSelect + sqlWhere + sqlByID;
return(FromDB(db.QuerySingle(sql, userID, languageID, countryID, userBadgeID)));
}
}
/// <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>
/// Includes strictly only badges assigned to the solutionID. Can be null to fetch non-solution badges (apply to the whole user profile).
/// </summary>
/// <param name="userID"></param>
/// <param name="solutionID"></param>
/// <param name="languageID"></param>
/// <param name="countryID"></param>
/// <returns></returns>
public static IEnumerable <UserBadge> ListBySolution(int userID, int?solutionID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
var sql = sqlSelect + (solutionID.HasValue ? sqlBySolution : sqlWhere + " AND b.solutionID is null");
return(db.Query(sql, userID, languageID, countryID, solutionID).Select(FromDB));
}
}
/// <summary>
/// List all (non deleted) user badges, for internal, admin, usage.
/// </summary>
/// <param name="userID"></param>
/// <param name="languageID"></param>
/// <param name="countryID"></param>
/// <returns></returns>
public static IEnumerable <UserBadge> ListAllByUser(int userID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
var sql = sqlSelect + sqlWhere;
return(db.Query(sql, userID, languageID, countryID).Select(FromDB));
}
}
public static IEnumerable <PublicUserJobTitle> GetList(int serviceProfessionalUserID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetList, serviceProfessionalUserID, languageID, countryID)
.Select <dynamic, PublicUserJobTitle>(FromDB));
}
}
/// <summary>
/// Includes all badges assigned to solutions included in the listing, plus user profile badges (where solutionID is null)
/// </summary>
/// <param name="userID"></param>
/// <param name="userListingID"></param>
/// <param name="languageID"></param>
/// <param name="countryID"></param>
/// <param name="onlyPublicOnes">Request only badges that can be displayed publicly</param>
/// <returns></returns>
public static IEnumerable <UserBadge> ListByListing(int userID, int userListingID, int languageID, int countryID, bool onlyPublicOnes = false)
{
using (var db = new LcDatabase())
{
var sql = sqlSelect + sqlByListing + (onlyPublicOnes ? sqlAndPublicOnly : "");
return(db.Query(sql, userID, languageID, countryID, userListingID).Select(FromDB));
}
}
static public string GetUserTimeZone(int userID)
{
using (var db = new LcDatabase())
{
// By default is null, in case it has no events will let the client app to auto pick one
return((string)N.D(db.QueryValue("SELECT TOP 1 timeZone FROM CalendarProviderAttributes WHERE UserID=@0", userID)));
}
}
public static IEnumerable <PublicJobTitle> SearchByCategory(string category, string city, Locale locale)
{
using (var db = new LcDatabase())
{
return(db.Query("EXEC dbo.SearchPositionsByCategory @0, @1, @2", locale.languageID, locale.countryID, category, city)
.Select(FromDB));
}
}
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 IEnumerable <Solution> Search(string searchText, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
var sql = sqlLimitedSelect + sqlGetList + sqlSearchConditions + sqlCommonOrder;
return(db.Query(sql, languageID, countryID, searchText).Select(FromDB));
}
}
/// <summary>
/// It performs a limited search of specializations for the autocomplete, just with the
/// subset of fields needed (matches UserPostingSpecialization type).
/// </summary>
/// <param name="searchText"></param>
/// <param name="languageID"></param>
/// <param name="countryID"></param>
/// <returns></returns>
public static IEnumerable <UserPostingSpecialization> AutocompleteSearch(
string searchText, int solutionID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
var sql = sqlAutocomplete;
return(db.Query(sql, languageID, countryID, searchText, solutionID).Select(UserPostingSpecialization.FromDB));
}
}
/// <summary>
/// Checks whether the user has the job title assigned already (publicly active or not).
/// Does not include blocked records (Active=0).
/// </summary>
/// <param name="userID"></param>
/// <param name="jobTitleID"></param>
/// <param name="languageID"></param>
/// <param name="countryID"></param>
/// <returns></returns>
public static Boolean HasItem(int userID, int jobTitleID, int?languageID = null, int?countryID = null)
{
languageID = languageID ?? LcData.GetCurrentLanguageID();
countryID = countryID ?? LcData.GetCurrentCountryID();
using (var db = new LcDatabase())
{
return(db.QuerySingle(sqlGetActiveOrInactiveItem, userID, languageID, countryID, jobTitleID) != null);
}
}
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.
}
}
}
}
public static string GetEmail(int userID)
{
using (var db = new LcDatabase())
{
return((string)db.QueryValue(@"
SELECT email FROM UserProfile WHERE UserID = @0
", userID));
}
}
