public static Education Get(int userID, int educationID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlSelect + sqlWhereID, userID, educationID)));
}
}
public static UserEmail Get(int userID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle("SELECT userID, email FROM userprofile WHERE userID=@0", userID)));
}
}
public static Platform GetItem(int platformID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, platformID)));
}
}
public static Platform Get(int userID, int platformID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetUserItem, userID, languageID, countryID, platformID)));
}
}
public static CancellationPolicy Get(int cancellationPolicyID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, cancellationPolicyID, languageID, countryID)));
}
}
public static LicenseCertification GetItem(int licenseCertificationID, int languageID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, licenseCertificationID, languageID)));
}
}
public static UserFeePayment Get(int userID, DateTime paymentDate)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, userID, paymentDate)));
}
}
/// <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 UserExternalListing Get(int userID, int userExternalListingID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, userID, userExternalListingID)));
}
}
public static Solution Get(int solutionID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle("SELECT TOP 1 " + sqlGetList + sqlAndId, languageID, countryID, solutionID)));
}
}
public static Thread Get(int threadID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, threadID)));
}
}
public static PublicUserStats Get(int userID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle("SELECT userID, responseTimeMinutes FROM UserStats WHERE UserID = @0", userID)));
}
}
public static ServiceProfessionalClient Get(int serviceProfessionalUserID, int clientUserID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, serviceProfessionalUserID, clientUserID)));
}
}
public static BookingType Get(int id)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, id)));
}
}
/// <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 a record by the payment gateway 'subscriptionID', that is meant to be
/// unique.
/// </summary>
/// <param name="subscriptionID"></param>
/// <returns></returns>
public static UserPaymentPlan GetBySubscriptionID(string subscriptionID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetBySubscriptionID, subscriptionID)));
}
}
/// <summary>
/// Get the current, active plan for a user, null if nothing found.
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static UserPaymentPlan GetUserActivePlan(int userID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetByUser + sqlConditionOnlyActivePlans, userID)));
}
}
public static PostingTemplate Get(int postingTemplateID, int languageID, int countryID, bool fillQuestions)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGet, postingTemplateID, languageID, countryID), fillQuestions));
}
}
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)));
}
}
public static UserEarningsReport Query(int userID, EarningsFilterValues filter)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlQuery, userID, filter.fromDate, filter.toDate, filter.jobTitleID, filter.userExternalListingID)));
}
}
public static UserPaymentPlan Get(int userID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem)));
}
}
public static ExperienceLevel GetItem(int experienceLevelID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetItem, languageID, countryID, experienceLevelID)));
}
}
/// <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 UserFeePayment GetBySubscriptionTransaction(int userID,
string subscriptionID, string paymentTransactionID)
{
using (var db = new LcDatabase())
{
return(FromDB(db.QuerySingle(sqlGetBySubscriptionTransaction, userID, subscriptionID, paymentTransactionID)));
}
}
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)));
}
}
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));
}
}
/// <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 UpcomingAppointmentsInfo GetList(int userID)
{
var ret = new UpcomingAppointmentsInfo();
using (var db = new LcDatabase())
{
dynamic d = null;
d = db.QuerySingle(sqlGetBookingsSumSinceDate, userID, DateTime.Now, LcEnum.BookingStatus.confirmed);
ret.nextOnes = new Summary
{
quantity = d.count,
time = d.startTime
};
// NOTE: what if there is a booking for several days and we are in the middel of that? First work hour on the date?
d = db.QuerySingle(sqlGetBookingsSumSinceDate, userID, DateTime.Now, LcEnum.BookingStatus.request);
ret.pendingRequests = new Summary
{
quantity = d.count,
time = d.startTime
};
// TODO Implement multi-session pending scheduling look-up
//d = db.QuerySingle(sqlGetBookingsSumByDateRange, userID, DateTime.Now, ...);
//ret.pendingScheduling = new Summary
//{
// quantity = d.count,
// time = d.startTime
//};
ret.pendingScheduling = new Summary();
var nextBookingID = (int?)db.QueryValue(sqlGetNextBookingID, userID, DateTime.Now, LcEnum.BookingStatus.confirmed);
if (nextBookingID.HasValue)
{
ret.nextBooking = Booking.Get(nextBookingID.Value, true, false, userID);
}
}
return(ret);
}
public static PublicUserJobTitle Get(int serviceProfessionalUserID, int languageID, int countryID, int jobTitleID, bool includeDeactivatedProfile = false, bool bookMeButtonRequired = false)
{
using (var db = new LcDatabase())
{
var sql = includeDeactivatedProfile ? sqlGetActiveOrInactiveItem : sqlGetActiveItem;
if (bookMeButtonRequired)
{
sql += sqlAndBookMeButtonReady;
}
return(FromDB(db.QuerySingle(sql, serviceProfessionalUserID, languageID, countryID, jobTitleID)));
}
}
