public static dynamic GetUserJobTitles(int userID, int jobTitleID = -1)
{
using (var db = Database.Open("sqlloco"))
{
return(db.Query(@"
SELECT
u.UserID As userID,
u.PositionID As jobTitleID,
u.PositionIntro As intro,
u.StatusID As statusID,
u.CancellationPolicyID As cancellationPolicyID,
u.InstantBooking As instantBooking,
u.CreateDate As createdDate,
u.UpdatedDate As updatedDate,
u.bookMeButtonReady As bookMeButtonReady,
u.collectPaymentAtBookMeButton As collectPaymentAtBookMeButton
FROM
userprofilepositions as u
INNER JOIN
positions on u.positionID = positions.positionID AND positions.languageID = @1 and positions.countryID = @2
WHERE
u.UserID = @0
AND u.LanguageID = @1
AND u.CountryID = @2
AND u.Active = 1
AND u.StatusID > 0
AND (@3 = -1 OR @3 = u.PositionID)
-- Double check for approved positions
AND positions.Active = 1
AND (positions.Approved = 1 Or positions.Approved is null) -- Avoid not approved, allowing pending (null) and approved (1)
", userID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID(), jobTitleID));
}
}
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>
/// Reactivation consists in switch the status of the profile
/// to the 'active' state but only if the profile fullfill the constraints
/// for 'active profiles', so the profile can get stucked
/// in the non-active state as a result, pending on requesting
/// a reactivation once its contraints are fullfilled.
/// </summary>
/// <param name="userID"></param>
/// <param name="jobTitleID"></param>
/// <returns></returns>
public static bool ReactivateUserJobTitle(int userID, int jobTitleID)
{
using (var db = Database.Open("sqlloco")) {
// Check the current StatusID
var statusID = LcData.UserInfo.GetUserPositionStatus(userID, jobTitleID);
// If status is -1, the records does not exists
if (statusID == -1)
{
return(false);
}
// If status is of type 3 'private profile, manual activation'..
if (statusID == 3)
{
// ..modify the profile StatusID from 3 to 2 'private profile, automatic activation'
// (because the TestProfileActivation only works for StatusID:2 to avoid unexpected changes)
db.Execute(@"
UPDATE UserProfilePositions
SET StatusID = 2,
UpdatedDate = getdate()
WHERE UserID = @0 AND PositionID = @1
AND LanguageID = @2
AND CountryID = @3
", userID, jobTitleID,
LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID());
}
// It executes the profile activation that checks that all required alerts must be off to activate:
db.Execute("EXEC TestProfileActivation @0, @1", userID, jobTitleID);
// Check the updated StatusID
var newStatusID = LcData.UserInfo.GetUserPositionStatus(userID, jobTitleID);
// If the status is 1 'public profile', success!
if (newStatusID == 1)
{
return(true);
}
else
{
// It is Not activated still, required alerts are pending, back to the original
// StatusID if was not 2 originally (it was updated in the middle of the process to run
// the TestProfileActivation procedure)
if (statusID >= 2)
{
db.Execute(@"
UPDATE UserProfilePositions
SET StatusID = @2,
UpdatedDate = getdate()
WHERE UserID = @0 AND PositionID = @1
AND LanguageID = @3
AND CountryID = @4
", userID, jobTitleID, statusID,
LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID());
}
return(false);
}
}
}
/* Get a data object with the Position row of the user 'userId' with PositionId 'posId' from the database
*/
public static dynamic GetUserPos(int userId, int posId, int langID = 0, int countryID = 0)
{
if (langID == 0)
{
langID = LcData.GetCurrentLanguageID();
}
if (countryID == 0)
{
countryID = LcData.GetCurrentCountryID();
}
// Implemented a basic, per-page, cache, faster and simplified for each page
var u = HelperPage.PageData["userpos:" + userId.ToString() + ":" + posId.ToString()];
if (u == null)
{
using (var db = Database.Open("sqlloco")){
var sqlpositions = @"
SELECT a.UserID, a.PositionID, a.Active, a.StatusID, InstantBooking,
PositionSingular, PositionPlural, a.UpdatedDate, a.PositionIntro
FROM dbo.userprofilepositions a join positions c on a.PositionID = c.PositionID
WHERE a.UserID = @0 and a.PositionID = @1 and c.LanguageID = @2 and c.CountryID = @3
AND c.Active = 1 AND a.Active = 1 AND a.StatusID > 0";
u = db.QuerySingle(sqlpositions, userId, posId, langID, countryID);
}
HelperPage.PageData["userpos:" + userId.ToString() + ":" + posId.ToString()] = u;
}
return(u);
}
public static IEnumerable <UserJobTitle> GetByUser(int userID)
{
using (var db = Database.Open("sqlloco"))
{
return(db.Query(sqlGet, userID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID(), -1).Select(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 dynamic GetActiveUserAlerts(int userID, int positionID = -1)
{
using (var db = Database.Open("sqlloco")) {
return(db.Query(@"
SELECT A.AlertID,
A.AlertTypeID,
A.AlertName,
A.AlertHeadlineDisplay,
A.AlertTextDisplay,
A.AlertDescription,
A.AlertPageURL,
A.DisplayRank,
A.PositionSpecific,
A.Required,
UA.PositionID,
AT.AlertTypeName,
AT.AlertTypeDescription,
P.PositionSingular
FROM Alert As A
INNER JOIN
UserAlert As UA
ON A.AlertID = UA.AlertID
INNER JOIN
AlertType As AT
ON AT.AlertTypeID = A.AlertTypeID
LEFT JOIN (
Positions As P
INNER JOIN
UserProfilePositions As UP
ON UP.PositionID = P.PositionID
AND UP.Active = 1
AND UP.StatusID > 0
AND UP.LanguageID = P.LanguageID
AND UP.CountryID = P.CountryID
)
ON P.PositionID = UA.PositionID
AND P.LanguageID = A.LanguageID
AND P.CountryID = A.CountryID
AND UP.UserID = UA.UserID
WHERE UA.Active = 1 AND A.Active = 1 AND UA.UserID = @0
AND A.LanguageID = @1 AND A.CountryID = @2
AND (UA.PositionID = 0 OR P.PositionID is not null)
-- Filtered optionally by position (-1 to not filter by position)
AND (UA.PositionID = 0 OR @3 = -1 OR UA.PositionID = @3)
-- Added dismissed feature #243: not show if is dismissed
-- except for required ones, that cannot be dismissed
AND (A.Required = 1 OR UA.Dismissed = 0)
ORDER BY AT.DisplayRank, AT.AlertTypeName, A.DisplayRank, A.AlertName
", userID,
LcData.GetCurrentLanguageID(),
LcData.GetCurrentCountryID(),
positionID));
}
}
private static dynamic QueryPackageServiceAttributesByMulti(Database db, int providerUserID, int positionID = -1, int packageID = -1, int pricingTypeID = -1, bool?isAddon = null)
{
return(db.Query(SQLGetPackageServiceAttributesByMulti,
providerUserID,
positionID,
LcData.GetCurrentLanguageID(),
LcData.GetCurrentCountryID(),
packageID,
pricingTypeID,
(isAddon.HasValue ? (isAddon.Value ? 1 : 0) : -1)
));
}
public static IEnumerable <Alert> GetActive(int userID, int positionID = -1)
{
using (var db = new LcDatabase())
{
return(db.Query(
sqlSelect, userID,
LcData.GetCurrentLanguageID(),
LcData.GetCurrentCountryID(),
positionID)
.Select(FromDB));
}
}
private static void Load(PricingVariables data, int userID, int packageID, int pricingEstimateID = 0, int pricingEstimateRevision = 0)
{
using (var db = Database.Open("sqlloco"))
{
var vars = db.Query(sqlGetVariablesActualValues, userID, packageID, pricingEstimateID, pricingEstimateRevision,
LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID());
foreach (var r in vars)
{
var varValue = PricingVariableValue.CreateFromDbRecord(r);
data[r.InternalName] = varValue;
// Update index
data.idIndex[varValue.PricingVariableID] = varValue;
}
}
}
public static int GetStateFromZipCode(string zipcode)
{
var sqlGetStateIDFromZipCode = @"
SELECT TOP 1 StateProvinceID
FROM PostalCode As PC
WHERE PC.PostalCode = @0
AND
CountryID = @1
";
using (var db = Database.Open("sqlloco"))
{
var stateID = db.QueryValue(sqlGetStateIDFromZipCode, zipcode, LcData.GetCurrentCountryID());
return(stateID == null ? 0 : (int)stateID);
}
}
public static AccountEmailInfo GetAccountInfo(int userID, int?jobTitleID = null)
{
var a = new AccountEmailInfo
{
userID = userID
};
if (jobTitleID.HasValue)
{
var languageID = LcData.GetCurrentLanguageID();
var countryID = LcData.GetCurrentCountryID();
a.userJobTitle = LcRest.PublicUserJobTitle.Get(userID, languageID, countryID, jobTitleID.Value, true);
}
return(a);
}
public static void Create(UserJobTitle userJobTitle)
{
userJobTitle.ValidateAndFixBookingPolicies();
using (var db = new LcDatabase())
{
var results = db.QuerySingle("EXEC dbo.InsertUserProfilePositions @0, @1, @2, @3, @4, @5, @6, @7, @8",
userJobTitle.userID,
userJobTitle.jobTitleID,
LcData.GetCurrentLanguageID(),
LcData.GetCurrentCountryID(),
userJobTitle.cancellationPolicyID,
userJobTitle.intro,
userJobTitle.instantBooking,
userJobTitle.collectPaymentAtBookMeButton,
userJobTitle.title);
if (results.Result != "Success")
{
// TODO: Add better error checks (codes) at new back-end when porting this rather than local text errors
var message = (string)results.Result;
if (message.Contains("Cannot insert duplicate key"))
{
if (userJobTitle.jobTitleID == UserGeneratedJobTitleID)
{
throw new ConstraintException("We're sorry, but we currently only support one custom job title (stay tunned, this will change soon!).");
}
else
{
throw new ConstraintException("You already have a listing with that job title.");
}
}
else
{
throw new Exception("We're sorry, there was an error creating your listing: " + message);
}
}
else
{
// Additional data for the new listing:
// Needs the default solutions
if ((int)results.userListingID > 0)
{
UserSolution.SetDefaultSolutionsForListing((int)results.userListingID);
}
}
}
}
public static bool SoftDeleteUserJobTitle(int userID, int jobTitleID)
{
using (var db = Database.Open("sqlloco")) {
// 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);
}
}
private static dynamic QueryPackagesByMulti(Database db, int providerUserID, int positionID = -1, int packageID = -1, int pricingTypeID = -1, bool?isAddon = null, Visibility clientVisibility = null)
{
// By default, return pricings that are bookable by the public
clientVisibility = clientVisibility ?? Visibility.BookableByPublic();
const string SQLGetPackagesByMulti = SQLSelectFromPackage + @"
INNER JOIN
PricingType As PT
ON P.PricingTypeID = PT.PricingTypeID
AND P.LanguageID = PT.LanguageID
AND P.CountryID = PT.CountryID
INNER JOIN
PositionPricingType AS PPT
ON PPT.PositionID = P.PositionID
AND PPT.PricingTypeID = PT.PricingTypeID
AND PPT.LanguageID = PT.LanguageID
AND PPT.CountryID = PT.CountryID
AND PPT.Active = 1
WHERE p.ProviderUserID = @0
AND (@1 = -1 OR P.PositionID = @1)
AND
p.LanguageID = @2 AND p.CountryID = @3
AND
p.Active = 1
AND (@4 = -1 OR p.ProviderPackageID = @4)
AND (@5 = -1 OR p.PricingTypeID = @5)
AND (@6 = -1 OR P.IsAddOn = @6)
AND P.VisibleToClientID IN ({0})
ORDER BY PT.DisplayRank ASC
";
// Database.Query does not natively expand SQL IN clause list, so do it manually :(
string query = String.Format(SQLGetPackagesByMulti, String.Join(",", clientVisibility.VisibleToClientIDs()));
return(db.Query(query,
providerUserID,
positionID,
LcData.GetCurrentLanguageID(),
LcData.GetCurrentCountryID(),
packageID,
pricingTypeID,
(isAddon.HasValue ? (isAddon.Value ? 1 : 0) : -1)
));
}
public static dynamic GetPositionRatings(int positionID)
{
using (var db = Database.Open("sqlloco"))
{
return(db.QuerySingle(@"
SELECT TOP 1
Rating1, Rating2, Rating3
,Rating1FormDescription, Rating2FormDescription, Rating3FormDescription
,Rating1ProfileDescription, Rating2ProfileDescription, Rating3ProfileDescription
FROM PositionRatings
WHERE (PositionID = @0 OR PositionID = -1)
AND LanguageID = @1
AND CountryID = @2
-- First, the specific ID, then the default PositionID=0.
-- If there is no specific, with TOP 1 we get the default
ORDER BY PositionID DESC
", positionID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID()));
}
}
/// <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 DeactivateUserJobTitle(int userID, int jobTitleID)
{
using (var db = Database.Open("sqlloco")) {
// 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 Create(UserJobTitle userJobTitle)
{
using (var db = new LcDatabase())
{
var results = db.QuerySingle("EXEC dbo.InsertUserProfilePositions @0, @1, @2, @3, @4, @5, @6, @7",
userJobTitle.userID,
userJobTitle.jobTitleID,
LcData.GetCurrentLanguageID(),
LcData.GetCurrentCountryID(),
userJobTitle.cancellationPolicyID,
userJobTitle.intro,
userJobTitle.instantBooking,
userJobTitle.collectPaymentAtBookMeButton);
if (results.Result != "Success")
{
throw new Exception("We're sorry, there was an error creating your job title: " + results.Result);
}
}
}
/// <summary>
/// Load the set of pricingVariables with the saved values for the given provider package
/// updated with current set of variables assigned to the position and pricingType,
/// suitable to fill the 'edit package' form.
/// </summary>
/// <param name="data"></param>
/// <param name="userID"></param>
/// <param name="packageID"></param>
/// <param name="positionID"></param>
/// <param name="pricingTypeID"></param>
private static void LoadUpdated(PricingVariables data, int userID, int packageID, int positionID, int pricingTypeID)
{
using (var db = Database.Open("sqlloco"))
{
var vars = db.Query(sqlGetVariablesForEdit,
userID,
packageID,
positionID,
pricingTypeID,
LcData.GetCurrentLanguageID(),
LcData.GetCurrentCountryID());
foreach (var r in vars)
{
var varValue = PricingVariableValue.CreateFromDbRecord(r);
data[r.InternalName] = varValue;
// Update index
data.idIndex[varValue.PricingVariableID] = varValue;
}
}
}
private static void LoadNew(PricingVariables data, int positionID, int pricingTypeID)
{
using (var db = Database.Open("sqlloco"))
{
var vars = db.Query(sqlGetVariablesForNewPackage, positionID, pricingTypeID,
LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID());
foreach (var r in vars)
{
var varValue = new PricingVariableValue {
PricingVariableID = r.PricingVariableID
, Value = null
, ProviderNumberIncluded = null
, ProviderMinNumberAllowed = null
, ProviderMaxNumberAllowed = null
, Def = PricingVariableDefinition.CreateFromDbRecord(r)
};
data[r.InternalName] = varValue;
// Update index
data.idIndex[varValue.PricingVariableID] = varValue;
}
}
}
public static dynamic GetUserVerifications(int userID)
{
using (var db = Database.Open("sqlloco"))
{
return(db.Query(@"
SELECT UV.LastVerifiedDate,
UV.VerificationStatusID,
VS.VerificationStatusName,
V.VerificationType,
V.Icon,
V.VerificationID,
V.VerificationCategoryID
FROM UserVerification As UV
INNER JOIN
Verification As V
ON UV.VerificationID = V.VerificationID
INNER JOIN
VerificationStatus As VS
ON UV.VerificationStatusID = VS.VerificationStatusID
AND V.LanguageID = @1 AND V.CountryID = @2
WHERE UserID = @0
", userID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID()));
}
}
public PostingEmailInfo(int userID, int userPostingID, int serviceProfessionalID)
{
posting = LcRest.UserPosting.Get(userID, userPostingID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID(), false);
this.serviceProfessionalID = serviceProfessionalID;
}
/* Get a data object with the Positions rows of the user identified with 'userId' from the database
*/
public static dynamic GetUserPos(int userId, bool onlyActivePositions = false)
{
var cachekey = String.Format("userposrows:{0}:{1}", userId, onlyActivePositions ? "active" : "all");
var poss = HelperPage.PageData[cachekey];
if (poss == null)
{
using (var db = Database.Open("sqlloco")) {
var sqlpositions = @"
SELECT a.UserID, a.PositionID, a.Active, a.StatusID, InstantBooking,
PositionSingular, PositionPlural, a.UpdatedDate, a.PositionIntro
FROM dbo.userprofilepositions a join
positions c on a.PositionID = c.PositionID and a.CountryID = c.CountryID and a.LanguageID = c.LanguageID
WHERE a.UserID = @0 and c.LanguageID = @2 and c.CountryID = @3
AND c.Active = 1
AND a.Active = 1 AND ((@1 = 0 AND a.StatusID > 0) OR a.StatusID = 1)
AND (c.Approved = 1 Or c.Approved is null) -- Avoid not approved, allowing pending (null) and approved (1)
";
poss = db.Query(sqlpositions, userId, onlyActivePositions ? 1 : 0, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID());
}
HelperPage.PageData[cachekey] = poss;
}
return(poss);
}
public static BookingEmailInfo GetBookingInfo(int bookingID)
{
var bID = bookingID;
// We need the booking including some internal sensitive data (like 'payment last four digits')
// since here we have not enough context to know what userID is the target, and must not be a problem
// since this data is never provided as is, but only used by the templates that must be carefull with what
// data to show to every case.
var b = LcRest.Booking.Get(bID, true, true);
if (b == null)
{
throw new Exception("BookingID not found #" + bID + ", at Email template");
}
/* Generics not used in new email template organization, but keep this commented
* for any future chance:
* var url = Request.Url.OriginalString.ToUpper();
* var sentTo = LcData.UserInfo.UserType.None;
* if (url.IndexOf("/TOCLIENT/") > -1) {
* sentTo = LcData.UserInfo.UserType.Client;
* }
* else if (url.IndexOf("/TOSERVICEPROFESSIONAL/") > -1) {
* sentTo = LcData.UserInfo.UserType.ServiceProfessional;
* }
* int toUserID = 0;
* if (sentTo == LcData.UserInfo.UserType.ServiceProfessional) {
* toUserID = b.serviceProfessionalUserID;
* }
* else if (sentTo == LcData.UserInfo.UserType.Client) {
* toUserID = b.clientUserID;
* }
*/
// Cancellation policy
var policy = LcRest.CancellationPolicy.Get(b.cancellationPolicyID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID());
return(new BookingEmailInfo
{
booking = b,
//servicePricing = GetForPricingSummary(b.pricingSummary),
userJobTitle = b.userJobTitle,
cancellationPolicy = policy
//,SentTo = sentTo
//,SentToUserID = toUserID
});
}
/// <summary>
/// Return a user job title in the current locale, even if the user job title is not
/// active.
/// </summary>
/// <param name="userID"></param>
/// <param name="jobTitleID"></param>
public static UserJobTitle GetItem(int userID, int jobTitleID)
{
return(GetItem(userID, LcData.GetCurrentCountryID(), LcData.GetCurrentLanguageID(), jobTitleID, true, false));
}
private static IEnumerable <UserJobTitle> GetListByUser(string sql, int userID)
{
using (var db = new LcDatabase())
{
var userJobTitles = db.Query(sql, userID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID())
.Select(FromDB);
return(BindAlerts(userJobTitles, Alert.IndexByPosition(Alert.GetActive(userID))));
}
}
public static int GetUserPositionStatus(int userID, int positionID)
{
using (var db = Database.Open("sqlloco"))
{
var statusID = db.QueryValue("SELECT StatusID FROM UserProfilePositions WHERE UserID = @0 AND PositionID = @1 AND LanguageID = @2 AND LanguageID = @3",
userID, positionID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID());
if (statusID is int)
{
return(statusID);
}
else
{
// There is no position for the user, there is no user, or not for the languagen and country
return(-1);
}
}
}
public static Dictionary <int, Dictionary <string, object> > GetServiceCatsAndItsAttributes(int positionId, string filters = null, int userId = 0)
{
var rcats = new Dictionary <int, Dictionary <string, object> >();
var catsFilter = new List <int>();
// Set if the catsFilter is the list of cats to be excluded from the total (value: true)
// or is a list of unique cats to be returned (value: false)
bool excludeCats = false;
// This bool config set that only attributes related to the userId must be returned (query field 'UserChecked' == True)
bool onlyUserChecked = false;
bool onlyBookingServices = false;
List <string> filterList = new List <string>();
// Reading filters:
if (filters != null)
{
filterList.AddRange(filters.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));
}
foreach (string filter in filterList)
{
switch (filter)
{
case "provider-services-without-virtual-cats":
case "provider-services":
//catsFilter.AddRange(new int[]{1, 2, 3, 4, 5, 7});
//catsFilter.AddRange(new int[]{1, 2, 4, 5, 7});
excludeCats = false;
break;
case "without-special-cats":
catsFilter = SpecialServicesAttCats;
excludeCats = true;
break;
case "only-special-cats":
catsFilter = SpecialServicesAttCats;
excludeCats = false;
break;
case "only-user-checked":
onlyUserChecked = true;
break;
case "booking-services":
onlyBookingServices = true;
break;
}
}
var sqlcat = "exec GetServiceAttributeCategories @0, @1, @2, @3";
var sqlattribute = "exec GetServiceAttributes @0, @1, @2, @3, @4, @5";
using (var db = Database.Open("sqlloco"))
{
var catrow = db.Query(sqlcat, positionId, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID(), onlyBookingServices);
// Iterate the categories
foreach (var cat in catrow)
{
// Apply filtering, if there are
if (catsFilter.Count > 0 &&
(excludeCats && catsFilter.Contains(cat.ServiceAttributeCategoryID)
||
!excludeCats && !catsFilter.Contains(cat.ServiceAttributeCategoryID)
))
{
continue;
}
// Copy data to a new structure
var rcat = new Dictionary <string, object>()
{
{ "ServiceAttributeCategoryID", cat.ServiceAttributeCategoryID },
{ "ServiceAttributeCategoryName", cat.ServiceCat },
{ "ServiceAttributeCategoryDescription", cat.ServiceAttributeCategoryDescription },
{ "RequiredInput", cat.RequiredInput },
{ "SideBarCategory", cat.SideBarCategory }
};
// Getting attributes of the category
rcat["ServiceAttributes"] = db.Query(sqlattribute,
positionId,
cat.ServiceAttributeCategoryID,
LcData.GetCurrentLanguageID(),
LcData.GetCurrentCountryID(),
(userId == 0 ? null : (object)userId),
onlyUserChecked);
rcats.Add(cat.ServiceAttributeCategoryID, rcat);
}
/* SPECIAL CASES */
if (filterList.Contains("provider-services") || filterList.Contains("only-special-cats"))
{
// Adding the extra tables Language Levels and Experience Levels as 'virtual' categories, using the same
// fields name to be easy to implement
// Returning a 'virtual' language levels category
var rcat = new Dictionary <string, object>()
{
{ "ServiceAttributeCategoryID", ServiceAttCatIDLanguageLevel },
{ "ServiceAttributeCategoryName", LcRessources.GetText("Language Level") },
{ "ServiceAttributeCategoryDescription", LcRessources.GetText("Language Level Description") },
{ "RequiredInput", false },
{ "SideBarCategory", true }
};
var levelsIndex = new Dictionary <int, int>();
var langlevels = new List <object>();
foreach (var level in LcData.GetLanguageLevels())
{
langlevels.Add(new Dictionary <string, object> {
{ "ServiceAttributeDescription", level.LanguageLevelDescription },
{ "ServiceAttributeID", level.LanguageLevelID },
{ "ServiceAttribute", level.LanguageLevelName },
{ "UserChecked", false }
});
levelsIndex.Add(level.LanguageLevelID, langlevels.Count - 1);
}
rcat["ServiceAttributes"] = langlevels;
if (userId > 0)
{
rcat["LevelsIndex"] = levelsIndex;
rcat["UserSelectedLevels"] = LcData.GetUserLanguageLevels(userId, positionId);
}
rcats[ServiceAttCatIDLanguageLevel] = rcat;
// Returning a 'virtual' experience levels category
rcat = new Dictionary <string, object>()
{
{ "ServiceAttributeCategoryID", ServiceAttCatIDExperienceLevel },
{ "ServiceAttributeCategoryName", LcRessources.GetText("Experience Level") },
{ "ServiceAttributeCategoryDescription", LcRessources.GetText("Experience Level Description") },
{ "RequiredInput", false },
{ "SideBarCategory", true }
};
var explevels = new List <object>();
foreach (var level in GetExperienceLevels(userId, positionId))
{
if (!onlyUserChecked || level.UserChecked)
{
explevels.Add(new Dictionary <string, object> {
{ "ServiceAttributeDescription", level.ExperienceLevelDescription },
{ "ServiceAttributeID", level.ExperienceLevelID },
{ "ServiceAttribute", level.ExperienceLevelName },
{ "UserChecked", level.UserChecked }
});
}
}
rcat["ServiceAttributes"] = explevels;
rcats[ServiceAttCatIDExperienceLevel] = rcat;
}
}
return(rcats);
}
/// <summary>
/// Register an user and returns relevant registration information about the new account,
/// or raise and exception on error of type System.Web.Security.MembershipCreateUserException.
/// IMPORTANT: For code that doesn't uses this but the CreateAccount directly,
/// is required to validate the password against ValidPasswordRegex.
/// It's recommended to use form validation with that regex before even call this to avoid extra computation, checks,
/// but this will check the regex too.
/// </summary>
/// <param name="email"></param>
/// <param name="firstname"></param>
/// <param name="lastname"></param>
/// <param name="password"></param>
/// <param name="isProvider"></param>
/// <returns></returns>
public static RegisteredUser RegisterUser(
string email,
string firstname,
string lastname,
string password,
bool isProvider,
string marketingSource = null,
int genderID = -1,
string aboutMe = null,
string phone = null,
string signupDevice = null
)
{
// Check password validity.
if (!PasswordValidator.IsValid(password))
{
throw new ConstraintException(PasswordValidator.InvalidPasswordErrorMessage);
}
using (var db = Database.Open("sqlloco"))
{
// IMPORTANT: The whole process must be complete or rollback, but since
// a transaction cannot be done from the start because will collide
// with operations done by WebSecurity calls, the first step must
// be protected manually.
string token = null;
try
{
// Insert email into the profile table
db.Execute("INSERT INTO UserProfile (Email) VALUES (@0)", email);
// Create and associate a new entry in the membership database (is connected automatically
// with the previous record created using the automatic UserID generated for it).
token = WebSecurity.CreateAccount(email, password, true);
}
catch (Exception ex)
{
// Manual rollback previous operation:
// If CreateAccount failed, nothing was persisted there so nothing require rollback,
// only the UserProfile record
db.Execute("DELETE FROM UserProfile WHERE Email like @0", email);
// Relay exception
throw ex;
}
// Create Loconomics Customer user
int userid = WebSecurity.GetUserId(email);
try
{
// Automatic transaction can be used now:
db.Execute("BEGIN TRANSACTION");
// TODO:CONFIRM: SQL executed inside a procedure is inside the transaction? Some errors on testing showed that maybe not, and that's a problem.
db.Execute("exec CreateCustomer @0,@1,@2,@3,@4,@5,@6,@7",
userid, firstname, lastname,
LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID(),
genderID, aboutMe, phone
);
// If is provider, update profile with that info (being both customer and provider)
// It assigns the first OnboardingStep 'welcome' for the new Onboarding Dashboard #454
if (isProvider)
{
BecomeProvider(userid, db);
}
else
{
// Per #978, clients have an onboarding starting with 'welcome' too
db.Execute(@"UPDATE Users SET OnboardingStep = 'welcome' WHERE UserID = @0", userid);
}
// Partial email confirmation to allow user login but still show up email-confirmation-alert. Details:
// IMPORTANT: 2012-07-17, issue #57; We decided use the email-confirmation-code only as a dashboard alert (id:15) instead of blocking the user
// login, what means user MUST can login but too MUST have an email-confirmation-code; we do that reusing the confirmation code
// created by asp.net starter-app as until now, but HACKING that system doing a minor change on database, in the
// asp.net webpages generated table called 'webpages_Membership': there are two fields to manage confirmation, a bit field (this
// we will hack changing it to true:1 manually -before of time-) and the confirmationToken that we will mantain to allow user confirmation
// from the welcome-email sent and to off the alert:15 (with custom code on the Account/Confirm page).
db.Execute(@"
UPDATE webpages_Membership SET
IsConfirmed = 1
WHERE UserId = @0
", userid);
// Log Marketing URL parameters
if (marketingSource == null && System.Web.HttpContext.Current != null)
{
marketingSource = System.Web.HttpContext.Current.Request.Url.Query;
}
if (marketingSource != null)
{
db.Execute("UPDATE users SET MarketingSource = @1 WHERE UserID = @0", userid, marketingSource);
}
// Device
if (!string.IsNullOrEmpty(signupDevice))
{
db.Execute("UPDATE users SET SignupDevice = @1 WHERE UserID = @0", userid, signupDevice);
}
db.Execute("COMMIT TRANSACTION");
// All done:
return(new RegisteredUser
{
UserID = userid,
Email = email,
ConfirmationToken = token,
IsProvider = isProvider
});
}
catch (Exception ex)
{
db.Execute("ROLLBACK TRANSACTION");
// If profile creation failed, there was a rollback, now must ensure the userprofile record is removed too:
db.Execute("DELETE FROM UserProfile WHERE Email like @0", email);
throw ex;
}
}
}
public static UserJobTitle GetItem(int userID, int jobTitleID)
{
using (var db = Database.Open("sqlloco"))
{
return(FromDB(db.QuerySingle(sqlGet, userID, LcData.GetCurrentLanguageID(), LcData.GetCurrentCountryID(), jobTitleID)));
}
}
