/// <summary>
/// Get all service attributes assigned to the given jobTitleID
/// in groups indexed by the serviceAttributeCategoryID.
/// </summary>
/// <param name="userID"></param>
/// <param name="jobTitleID"></param>
/// <param name="languageID"></param>
/// <param name="countryID"></param>
/// <returns></returns>
public static IEnumerable <ServiceAttributeCategory> GetGroupedJobTitleAttributes(int jobTitleID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
// Get all existent attributes
var data = db.Query(sqlGetJobTitleList, jobTitleID, 0, languageID, countryID);
// Get all existent attributes grouped by category
var catAtts = data.GroupBy(att => (int)att.serviceAttributeCategoryID, (k, l) => (ServiceAttributeCategory)ServiceAttributeCategory.FromDB(l.First(), l));
// But we still need to read all the categories that has no attributes still
// and were assigned to the job-title using the special [PositionReference] field.
var referenceCats = db.Query(sqlGetJobTitleReferenceCats, jobTitleID, languageID, countryID)
.Select <dynamic, ServiceAttributeCategory>(c => ServiceAttributeCategory.FromDB(c, new List <dynamic>())).ToList();
// We iterate and return all the cats with attributes
// while creating and index of IDs
var indexCats = new List <int>();
foreach (var cat in catAtts)
{
yield return(cat);
indexCats.Add(cat.serviceAttributeCategoryID);
}
// Return empty reference cats, all the ones not included in the generated index
foreach (var cat in referenceCats)
{
if (!indexCats.Contains(cat.serviceAttributeCategoryID))
{
yield return(cat);
}
}
}
}
private static IEnumerable <dynamic> GetUserJobTitleListData(int userID, int jobTitleID, int serviceAttributeCategoryID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetUserList, jobTitleID, serviceAttributeCategoryID, languageID, countryID, userID));
}
}
/// <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 IEnumerable <UserExternalListing> GetByPlatformID(int userID, int platformID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetByPlatformID, userID, platformID).Select(FromDB));
}
}
public static IEnumerable <OwnerStatusHistory> GetList(int userID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetList).Select(FromDB));
}
}
public static IEnumerable <Platform> GetFullList(int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetFullList, languageID, countryID).Select(FromDB));
}
}
public static IEnumerable <ExperienceLevel> GetList(int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetList, languageID, countryID).Select(FromDB));
}
}
public static dynamic SearchByJobTitleAutocomplete(string searchTerm, Locale locale)
{
using (var db = new LcDatabase())
{
return(db.Query(@"
DECLARE @searchTerm AS varchar(150)
SET @searchTerm = @0
DECLARE @LanguageID int
SET @LanguageID = @1
DECLARE @CountryID int
SET @CountryID = @2
SELECT TOP 25
PositionID as jobTitleID
,PositionSingular as singularName
,PositionDescription as description
,PositionSearchDescription as searchDescription
FROM
positions
WHERE
Active = 1
AND LanguageID = @LanguageID
AND CountryID = @CountryID
AND Approved = 1
AND (PositionSingular like '%' + @searchTerm + '%'
OR PositionDescription like '%' + @searchTerm + '%'
OR GovPositionDescription like '%' + @searchTerm + '%'
OR PositionSearchDescription like '%' + @searchTerm + '%'
OR Aliases like '%' + @searchTerm + '%')
ORDER BY
DisplayRank, PositionSingular
", searchTerm, locale.languageID, locale.countryID));
}
}
public static IEnumerable <JobTitlePricingType> GetList(int jobTitleID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetList, jobTitleID, languageID, countryID).Select(FromDB));
}
}
public static IEnumerable <JobTitleStateProvinceLicense> GetList(int userID, int jobTitleID, int languageID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetList, userID, jobTitleID, languageID).Select(FromDB));
}
}
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>
/// Get the full list of plans (active and history) for a given user
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static IEnumerable <UserPaymentPlan> GetByUser(int userID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetByUser, userID).Select(FromDB));
}
}
public static IEnumerable <Solution> BySearchSubcategory(int searchSubcategoryID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlBySearchSubcategoryID, languageID, countryID, searchSubcategoryID).Select(FromDB));
}
}
public static IEnumerable <UserPostingQuestionResponse> List(int userPostingID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlList, userPostingID).Select(FromDB));
}
}
public static IEnumerable <Solution> ByJobTitle(int jobTitleID, int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlByJobTitleID, languageID, countryID, jobTitleID).Select(FromDB));
}
}
/// <summary>
/// List all
/// </summary>
/// <param name="languageID"></param>
/// <param name="countryID"></param>
/// <returns></returns>
public static IEnumerable <Solution> List(int languageID, int countryID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlSelect + sqlGetList + sqlCommonOrder, languageID, countryID).Select(FromDB));
}
}
public static IEnumerable <PostingTemplateQuestion> List(int postingTemplateID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGet, postingTemplateID).Select(FromDB));
}
}
public static IEnumerable <UserFeePayment> GetList(int userID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetList, userID).Select(FromDB));
}
}
public static IEnumerable <PublicUserVerification> GetList(int userID, int jobTitleID = 0)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlGetList, userID, jobTitleID).Select(FromDB));
}
}
/// <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>
/// 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 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>
/// 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)));
}
}
/// <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));
}
}
/// <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));
}
}
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));
}
}
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 List <Education> GetList(int userID)
{
using (var db = new LcDatabase())
{
return(db.Query(sqlSelect + sqlWhereUser, userID)
.Select(FromDB)
.ToList());
}
}
/// <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));
}
}