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)
));
}
