/// <summary>
/// Unsubscribes a Customer from a Calendar.
/// </summary>
/// <param name="calendarID">The Guid of the Calendar to delete.</param>
public static void UnsubscribeFromCustomerCalendar(int customerID, Guid calendarID)
{
// Establish a SQL Connection
using (var ctx = ExigoDAL.Sql())
{
// Delete the Calendar Subscription Record
ctx.ExecuteReader(
@" DELETE FROM ExigoWebContext.CalendarSubscriptions WHERE CustomerID = @customerID AND CalendarID = @calendarID",
new { customerID, calendarID });
}
// Invalidate the Get Calendar Cache Key
InvalidateCalendarCache("GetCalendars/{0}/{1}", customerID, true);
}
public static void CreateResourceCategoryItem(ResourceCategoryItem item)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
INSERT
ExigoWebContext.ResourceCategoryItems
(ItemID, CategoryID, ItemOrder)
VALUES
(@itemid, @categoryid, @itemorder)
", new { itemid = item.ItemID, categoryid = item.CategoryID, itemorder = item.ItemOrder });
}
}
public static void ModifyResourceCategoryItemOrder(ResourceCategoryItem categoryItem)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
UPDATE ExigoWebContext.ResourceCategoryItems
SET ItemOrder = @neworder
WHERE ItemID = @itemid
AND CategoryID = @categoryid
", new { neworder = categoryItem.ItemOrder, itemid = categoryItem.ItemID, categoryid = categoryItem.CategoryID });
}
}
public static void DeleteResourceCategory(ResourceCategory category)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
DELETE FROM
ExigoWebContext.ResourceCategories
WHERE
CategoryID = @category
", new { category = category.CategoryID });
}
}
public static void DeleteResourceItem(ResourceItem item)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
DELETE FROM
ExigoWebContext.ResourceItems
WHERE
ItemID = @item
", new { item = item.ItemID });
}
}
public static void CreateResourceItem(ResourceItem item)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
INSERT INTO
ExigoWebContext.ResourceItems
(ItemID, TypeID, Title, Url, UrlThumbnail, CreatedDate, PostDate, StatusID, ItemDescription, LanguageID)
VALUES
(@itemid, @typeid, @title, @url, @urlthumb, @create, @post, @statusid, @description, @languageid)
", new { itemid = item.ItemID, typeid = item.TypeID, title = item.Title, url = item.Url, urlthumb = item.UrlThumbnail, create = item.CreatedDate, post = item.PostDate, statusid = item.StatusID, description = item.ItemDescription, languageid = item.LanguageID });
}
}
public static void CreateResourceItemTag(ResourceItemTag tag)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
INSERT
ExigoWebContext.ResourceItemTags
(TagID, ItemID)
VALUES
(@tagid, @itemid)
", new { tagid = tag.TagID, itemid = tag.ItemID });
}
}
public static int GetLastCommissionRunID(int periodTypeID)
{
using (var context = ExigoDAL.Sql())
{
return(context.Query <int>(@"
Select Top 1 CommissionRunID From CommissionRuns
Where PeriodTypeID = @periodTypeID
Order by PeriodID Desc
", new
{
periodTypeID
}).FirstOrDefault());
}
}
public static void DeleteResourceItemTags(List <Guid> tagids)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
DELETE FROM
ExigoWebContext.ResourceItemTags
WHERE
TagID IN @tags
", new { tags = tagids });
}
}
/// <summary>
/// Search Calendar Subscriptions using the conditional paramater as a filter.
/// </summary>
/// <param name="conditional">The condition to use for filtering e.g. "John" .Where(cal => cal.FirstName.Contains("John").</param>
/// <param name="customerID">The Customer ID of the current user. This prevents the user from subscribing to themselves.</param>
/// <returns>An Enumerable of Calendar Subscription Customer.</returns>
public static IEnumerable <Calendar> SearchSubscriptions(string conditional, int customerID)
{
// Set the Base SQL Command
var sql = @"
SELECT
ca.CalendarID,
ca.CustomerID,
ca.Description,
ca.CalendarTypeID,
ca.CreatedDate,
c.CustomerID as 'CusID',
c.FirstName,
c.LastName,
c.Company
FROM ExigoWebContext.Calendars ca
INNER JOIN Customers c
on c.CustomerID = ca.CustomerID
WHERE ca.CalendarID NOT IN
(SELECT cs.CalendarID FROM ExigoWebContext.CalendarSubscriptions cs INNER JOIN ExigoWebContext.Calendars c ON c.CalendarID = cs.CalendarID WHERE cs.CustomerID = @customerid)
AND
";
// If the conditional passed in can be parsed as an Int then the user is filtering on CustomerID
// However, if the conditional can't be parsed as an Int then the user is filtering on Customer Name
sql += conditional.CanBeParsedAs <int>()
? string.Format("ca.CustomerID = {0}", conditional)
: string.Format("(FirstName LIKE '%{0}%' OR LastName LIKE '%{0}%')", conditional);
// Create a Collection of Calendars
var calendarSubscriptions = new List <Calendar>();
// Establish a SQL Connection
using (var ctx = ExigoDAL.Sql())
{
// Excecute the SQL Command and set the results to the Collection constructed above
calendarSubscriptions = ctx.Query <Calendar, Customer, Calendar>(sql, (cal, cus) =>
{
cal.Customer = cus;
return(cal);
}
, param: new { customerid = customerID }
, splitOn: "CusID"
).ToList();
}
// Using the query, select the Calendars and Group By Customer. Then Create Calendar Subscription Customers for each row and return those items.
return
(calendarSubscriptions);
}
public static void AddResourceCategory(ResourceCategory newCategory)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
INSERT
ExigoWebContext.ResourceCategories
(CategoryID, CategoryDescription, CategoryOrder, ParentID)
VALUES
(@categoryid, @categorydescription, @categoryorder, @parentid)
", new { categoryid = newCategory.CategoryID, categorydescription = newCategory.CategoryDescription, categoryorder = newCategory.CategoryOrder, parentid = newCategory.ParentID });
}
}
public static List <ResourceItem> GetResourceItems(GetResourcesRequest request)
{
// Establish the base query
var query = "SELECT ItemID, typeID, Title, Url, UrlThumbnail, CreatedDate, PostDate, ItemDescription, StatusID, LanguageID FROM ExigoWebContext.ResourceItems";
// Apply any filters
var filters = 0;
if (request.ItemID != Guid.Empty)
{
query += " WHERE ItemID = @itemid";
filters++;
}
if (request.TypeID != Guid.Empty)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} TypeID = @typeid", filterText);
filters++;
}
if (request.ItemIDs != null && request.ItemIDs.Count() > 0)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} ItemID in @itemidlist", filterText);
filters++;
}
if (request.SearchFilter.IsNotNullOrEmpty())
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} Title LIKE @searchfilter", filterText);
filters++;
}
var model = new List <ResourceItem>();
using (var context = ExigoDAL.Sql())
{
model = context.Query <ResourceItem>(query, new { itemid = request.ItemID, typeid = request.TypeID, itemidlist = request.ItemIDs, searchfilter = request.SearchFilter }).ToList();
}
return(model);
}
public static List <ResourceTranslatedCategoryItem> GetCategoryTranslations(GetTranslatedCategoryRequest request)
{
// Establish the base query
var query = "SELECT Language, CategoryID, TranslatedCategoryDescription, TranslatedCategoryID FROM ExigoWebContext.ResourceTranslatedCategoryItems";
// Apply any filters
var filters = 0;
if (request.CategoryID != Guid.Empty)
{
query += " WHERE CategoryID = @catid";
filters++;
}
if (request.Language != null && request.Language.Count() > 0)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} Language = @lang", filterText);
filters++;
}
if (request.TranslatedCategoryID != Guid.Empty)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} TranslatedCategoryID = @tcid", filterText);
filters++;
}
if (request.CategoryIDs != null && request.CategoryIDs.Count() > 0)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} CategoryID in @catidlist", filterText);
filters++;
}
// Run the query
var model = new List <ResourceTranslatedCategoryItem>();
using (var context = ExigoDAL.Sql())
{
model = context.Query <ResourceTranslatedCategoryItem>(query, new { catid = request.CategoryID, lang = request.Language, tcid = request.TranslatedCategoryID, catidlist = request.CategoryIDs }).ToList();
}
return(model);
}
public static void DeleteResourceCategoryItems(Guid itemID, List <Guid> categoryID)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
DELETE FROM
ExigoWebContext.ResourceCategoryItems
WHERE ItemID = @id
AND CategoryID IN @categoryids
", new {
id = itemID,
categoryids = categoryID
});
}
}
public static void AddCategoryTranslation(ResourceTranslatedCategoryItem rtcItems)
{
using (var context = ExigoDAL.Sql())
{
context.Execute(@"
INSERT
ExigoWebContext.ResourceTranslatedCategoryItems
(TranslatedCategoryID, CategoryID, Language, TranslatedCategoryDescription)
VALUES
(@translatedcategoryid, @categoryid, @language, @categorydescription)
", new { translatedcategoryid = rtcItems.TranslatedCategoryID, categoryid = rtcItems.CategoryID, language = rtcItems.Language, categorydescription = rtcItems.TranslatedCategoryDescription });
}
}
public static List <int> GetDepartments()
{
var newsDepartments = new List <int>();
using (var context = ExigoDAL.Sql())
{
newsDepartments = context.Query <int>(@"
SELECT
d.DepartmentID
FROM
Departments d
").ToList();
};
return(newsDepartments);
}
public static IEnumerable <Subscription> GetSubscriptions()
{
var subscriptions = new List <Subscription>();
using (var context = ExigoDAL.Sql())
{
subscriptions = context.Query <Subscription>(@"
SELECT
SubscriptionID
, SubscriptionDescription
FROM Subscriptions
").ToList();
}
return(subscriptions);
}
public static List <ResourceCategory> GetResourceCategories(GetResourceCategoriesRequest request)
{
var query = "SELECT CategoryDescription, CategoryID, CategoryOrder, ParentID FROM ExigoWebContext.ResourceCategories";
// Apply the filters
var filters = 0;
if (request.CategoryID != null && request.CategoryID != Guid.Empty)
{
query += " WHERE CategoryID = @catid";
filters++;
}
if (request.CategoryIDs != null && request.CategoryIDs.Count() > 0)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} CategoryID in @catidlist", filterText);
filters++;
}
if (request.CategoryOrder != 0)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} CategoryOrder > @categoryorder", filterText);
filters++;
}
if (request.ParentID != null && request.ParentID != Guid.Empty)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} ParentID = @parentid", filterText);
filters++;
}
var model = new List <ResourceCategory>();
using (var context = ExigoDAL.Sql())
{
model = context.Query <ResourceCategory>(query, new { catid = request.CategoryID, catidlist = request.CategoryIDs, categoryorder = request.CategoryOrder, parentid = request.ParentID }).ToList();
};
return(model);
}
public static DateTime GetCustomerCreatedDate(int customerID)
{
using (var context = ExigoDAL.Sql())
{
return(context.ExecuteScalar <DateTime>(@"
SELECT
CreatedDate
FROM
Customers
WHERE
CustomerID = @CustomerID
", new
{
CustomerID = customerID
}));
}
}
public static IEnumerable <Country> GetCountries()
{
dynamic countries = new List <Country>();
using (var context = ExigoDAL.Sql())
{
countries = context.Query <Country>(@"
SELECT CountryCode
,CountryName = CountryDescription
,Priority
FROM Countries
ORDER BY Priority
").AsEnumerable();
}
return(countries);
}
public static List <ResourceAvailability> GetResourceAvailabilities(GetResourceAvailabilitiesRequest request)
{
var query = "SELECT AvailabilityID, LanguageID, ItemID, CountryCode FROM ExigoWebContext.ResourceAvailabilities";
// Apply any filters
var filters = 0;
if (request.AvailabilityID != Guid.Empty)
{
query += " WHERE AvailabilityID = @availabilityid";
filters++;
}
if (request.ItemID != Guid.Empty)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} ItemID = @itemid", filterText);
filters++;
}
if (request.LanguageID != null)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} LanguageID = @languageid", filterText);
filters++;
}
if (request.CountryCode != null && request.CountryCode != "")
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} CountryCode = @countrycode", filterText);
filters++;
}
var model = new List <ResourceAvailability>();
using (var context = ExigoDAL.Sql())
{
model = context.Query <ResourceAvailability>(query, new { availabilityid = request.AvailabilityID, itemid = request.ItemID, languageid = request.LanguageID, countrycode = request.CountryCode }).ToList();
}
return(model);
}
/// <summary>
/// Get all Calendar Event Types
/// </summary>
/// <returns>An Enumerable of CalendarEventType</returns>
public static IEnumerable <CalendarEventType> GetCalendarEventTypes()
{
// Set the SQL Command
const string sql = @" SELECT * FROM ExigoWebContext.CalendarEventTypes ";
// Create a Collection of Calendar Event Types
var eventTypes = new List <CalendarEventType>();
// Create a SQL Connetion
using (var ctx = ExigoDAL.Sql())
{
// Execute the SQL Command and set the results to the Collection constructed above
eventTypes = ctx.Query <CalendarEventType>(sql).ToList();
}
// Return the Event Types
return(eventTypes);
}
/// <summary>
/// Retrieves an enumerable of Speaker Names
/// </summary>
/// <param name="calendarIds">If provided, the Calendar ID(s) to pull speaker names from.</param>
/// <returns>An enumerable of string that represent Speaker's Names at events</returns>
public static IEnumerable <CalendarSpeaker> GetCalendarEventSpeakers()
{
// Set the SQL Command
const string sql = @" SELECT * FROM ExigoWebContext.CalendarSpeakers ce WHERE ce.Description <> '' ";
// Create a Collection of CalendarSpeaker to hold Speaker Names
var speakers = new List <CalendarSpeaker>();
// Create a SQL Connetion
using (var ctx = ExigoDAL.Sql())
{
// Execute the SQL Command and set the results to the Collection constructed above
speakers = ctx.Query <CalendarSpeaker>(sql).ToList();
}
// Return the Speaker Names colletion
return(speakers);
}
/// <summary>
/// Retrieves an enumerable of Tags.
/// </summary>
/// <returns>An enumerable of Tag Names.</returns>
public static IEnumerable <string> GetTags()
{
// Set the SQL Command
const string sql = @" SELECT Name FROM ExigoWebContext.Tags ";
// Create a collection of string to hold Tag Names
var tags = new List <string>();
// Establish a SQL Command
using (var ctx = ExigoDAL.Sql())
{
// Execute the SQL Command and set the results to the Collection constructed above
tags = ctx.Query <string>(sql).ToList();
}
// Return the collection of Tag Name
return(tags);
}
/// <summary>
/// Creates a Calendar Subscription Record in the DB
/// </summary>
/// <param name="customerID">The CustomerID of the subscribee.</param>
/// <param name="calendarID">The CalendarID of the subscription.</param>
public static void SubscribeToCustomerCalendar(int customerID, Guid calendarID)
{
// Establish a SQL Connection
using (var ctx = ExigoDAL.Sql())
{
// Insert a Calendar Subscription Record
ctx.Execute(
@" INSERT INTO ExigoWebContext.CalendarSubscriptions (CustomerID, CalendarID) VALUES (@customerID, @calendarID) ",
new
{
customerID,
calendarID
});
}
// Clear Get Calendars Cache
InvalidateCalendarCache("GetCalendars/{0}/{1}", customerID, true);
}
//Only Used for the Dashboard Card... Really all this does is return the customerID's then lets the customer model pull the avatar URL.
public static List <Customer> GetNewestDistributors(GetNewestDistributorsRequest request)
{
var newestDistributors = new List <Customer>();
var customerTypes = request.CustomerTypes;
var customerStatuses = request.CustomerStatuses;
using (var context = ExigoDAL.Sql())
{
newestDistributors = context.Query <Customer>(@"
SELECT TOP (@RowCount) un.DownlineCustomerID
, c.CustomerID
, c.FirstName
, c.MiddleName
, c.LastName
, c.CreatedDate
, c.Phone AS PrimaryPhone
, c.Email
FROM UniLevelDownline un
LEFT JOIN Customers c
ON un.CustomerID = c.CustomerID
WHERE un.DownlineCustomerID = @CustomerID
AND c.CustomerID <> @CustomerID
AND un.Level <= @Level
AND c.CustomerTypeID IN @CustomerTypes
AND c.CustomerStatusID IN @CustomerStatuses
AND c.CreatedDate >= CASE
WHEN @days > 0
THEN getdate()-@Days
ELSE c.CreatedDate
END
ORDER BY CreatedDate
", new
{
CustomerID = request.CustomerID,
Level = request.MaxLevel,
RowCount = request.RowCount,
CustomerTypes = customerTypes,
CustomerStatuses = customerStatuses,
Days = request.Days
}).ToList();
}
return(newestDistributors);
}
private static IEnumerable <Period> GetAllPeriods(int periodTypeID)
{
var cacheKey = GlobalSettings.Exigo.Api.CompanyKey + "AllPeriods_" + periodTypeID.ToString();
if (!MemoryCache.Default.Contains(cacheKey))
{
using (var context = ExigoDAL.Sql())
{
var periods = context.Query <Period>(@"
SELECT
PeriodTypeID
, PeriodID
, PeriodDescription
, StartDate
, EndDate
, AcceptedDate
FROM
Periods
WHERE
PeriodTypeID = @PeriodTypeID
", new
{
PeriodTypeID = periodTypeID
}).ToList();
MemoryCache.Default.Add(cacheKey, periods, DateTime.Now.AddMinutes(GlobalSettings.Exigo.CacheTimeout));
foreach (var period in periods)
{
yield return(period);
}
}
}
var cachePeriods = MemoryCache.Default.Get(cacheKey) as IEnumerable <Period>;
if (cachePeriods == null)
{
yield break;
}
foreach (var period in cachePeriods)
{
yield return(period);
}
}
public static List <Tag> GetTagsForResources(GetTagsForResourcesRequest request)
{
// Set the SQL Command
var query = @" SELECT TagID, Name FROM ExigoWebContext.Tags ";
// Apply any filters
var filters = 0;
if (request.TagID != Guid.Empty)
{
query += " WHERE TagID = @tagid";
filters++;
}
if (request.Name != null && request.Name != "")
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} Name = @name", filterText);
filters++;
}
if (request.Names != null && request.Names.Count() > 0)
{
var filterText = (filters > 0) ? "AND" : "WHERE";
query += String.Format(" {0} Name in @names", filterText);
filters++;
}
// Establish a SQL Command
var model = new List <Tag>();
using (var context = ExigoDAL.Sql())
{
// Execute the SQL Command and set the results to the Collection constructed above
model = context.Query <Tag>(query, new { tagid = request.TagID, name = request.Name, names = request.Names }).ToList();
}
// Return the collection of Tag Name
return(model);
}
public static bool IsEmailCustomersEmail(string email, int hostID)
{
var isEmailAvailable = false;
using (var context = ExigoDAL.Sql())
{
var customers = context.Query <int>(@"
select CustomerID from Customers
where (Email = @email or Loginname = @email)
and CustomerID = @customerID
", new { email = email, customerID = hostID }).ToList();
if (customers.Count() > 0)
{
isEmailAvailable = true;
}
}
return(isEmailAvailable);
}
public static Subscription GetSubscription(int subscriptionID)
{
var subscriptions = new Subscription();
using (var context = ExigoDAL.Sql())
{
subscriptions = context.Query <Subscription>(@"
SELECT
SubscriptionID
, SubscriptionDescription
FROM Subscriptions
WHERE SubscriptionID = @SubscriptionID
", new
{
SubscriptionID = subscriptionID
}).FirstOrDefault();
}
return(subscriptions);
}