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