public static Address GetServiceAddress(int userID, int jobTitleID, int addressID) { using (var db = Database.Open("sqlloco")) { return(GetSingleFrom(db.Query( sqlSelectOne + sqlFields + sqlAndCreatedByItself + sqlAndUserID + sqlAndJobTitleID + sqlAndAddressID + sqlcondOnlyActiveServiceAddress, LcData.GetCurrentLanguageID(), userID, jobTitleID, addressID ))); } }
public static List <Address> GetServiceAddresses(int userID, int jobTitleID) { using (var db = Database.Open("sqlloco")) { var sql = sqlSelect + sqlFields + sqlAndCreatedByItself + sqlAndUserID + sqlAndJobTitleID + (jobTitleID > 0 ? sqlcondOnlyActiveServiceAddress : sqlcondOnlyNamedAddresses); return(db.Query(sql, LcData.GetCurrentLanguageID(), userID, jobTitleID) .Select(FromDB) .ToList()); } }
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)); } }
public static List <Address> GetBillingAddresses(int userID) { using (var db = Database.Open("sqlloco")) { // Parameter jobTitleID needs to be specified as 0 to avoid to join // the service-address table // Null value as 3th parameter since that placeholder is reserved for addressID return(db.Query(sqlSelect + sqlFields + sqlAndCreatedByItself + sqlAndUserID + sqlAndJobTitleID + sqlAndTypeID, LcData.GetCurrentLanguageID(), userID, NotAJobTitleID, null, AddressType.Billing) .Select(FromDB) .ToList()); } }
/// <summary> /// Returns the address for the ID only if is owned by one of the given userIds. /// This is usefull at bookings, when the address for the service can be a service professional service address /// or a personal client address, there is no knowledgment of who is the owner in the booking info so /// can be any of both. /// </summary> /// <param name="addressID"></param> /// <param name="anyFromUserIds"></param> /// <returns></returns> public static Address GetAddress(int addressID, IEnumerable <int> anyFromUserIds) { using (var db = Database.Open("sqlloco")) { var idList = String.Join(",", anyFromUserIds); var sqlAndUserIdInList = " AND L.UserID IN (" + idList + ") "; return(GetSingleFrom(db.Query( sqlSelectOne + sqlFields + sqlAndUserIdInList + sqlAndAddressID, LcData.GetCurrentLanguageID(), null, // There is no @1 on this SQL NotAJobTitleID, // @2 has special meaning on the SQL, avoid some bad results addressID ))); } }
/// <summary> /// Get the list of active service addresses created by a given user on behalf of another user, optionally for a specific /// jobTitleID /// </summary> /// <param name="createdByUserID"></param> /// <param name="onBehalfOfUserID"></param> /// <param name="jobTitleID"></param> /// <returns></returns> public static IEnumerable <Address> GetAddressesCreatedByOnBehalfOf(int createdByUserID, int onBehalfOfUserID, int jobTitleID = 0) { using (var db = Database.Open("sqlloco")) { var sql = sqlSelect + sqlFields + sqlAndUserID + sqlcondOnlyActiveServiceAddress; if (jobTitleID > 0) { sql += sqlAndJobTitleID; } sql += sqlAndCreatedBy; return(db.Query(sql, LcData.GetCurrentLanguageID(), onBehalfOfUserID, jobTitleID, createdByUserID) .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 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); }
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) )); }