public static List <Item> GetItems(int[] itemIds) { using (var context = Exigo.Sql()) { return(context.Query <Item>(@" SELECT i.ItemID ,i.ItemCode ,i.ItemTypeID ,ISNULL(il.ItemDescription, i.ItemDescription) as ItemDescription ,ISNULL(il.ShortDetail, i.ShortDetail) as 'ShortDetail1' ,ISNULL(il.ShortDetail2, i.ShortDetail2) as 'ShortDetail2' ,ISNULL(il.ShortDetail3, i.ShortDetail3) as 'ShortDetail3' ,ISNULL(il.ShortDetail4, i.ShortDetail4) as 'ShortDetail4' ,ISNULL(il.LongDetail, i.LongDetail) as 'LongDetail1' ,ISNULL(il.LongDetail2, i.LongDetail2) as 'LongDetail2' ,ISNULL(il.LongDetail3, i.LongDetail3) as 'LongDetail3' ,ISNULL(il.LongDetail4, i.LongDetail4) as 'LongDetail4' ,i.TinyImageName as 'TinyImageUrl' ,i.SmallImageName as 'SmallImageUrl' ,i.LargeImageName as 'LargeImageUrl' FROM Items i LEFT JOIN ItemLanguages il ON il.ItemID = i.ItemID AND il.LanguageID = @languageID WHERE i.ItemID in @ids ", new { ids = itemIds, languageID = Exigo.GetSelectedLanguageID() }).ToList()); } }
public static IEnumerable <Item> GetItems(GetItemsRequest request) { // If we don't have what we need to make this call, stop here. if (request.Configuration == null) { throw new InvalidRequestException("ExigoService.GetItems() requires an OrderConfiguration."); } if (request.Configuration.CategoryID == 0 && request.CategoryID == null && request.ItemCodes.Length == 0) { throw new InvalidRequestException("ExigoService.GetItems() requires either a CategoryID or a collection of item codes."); } ; // Set some defaults if (request.CategoryID == null && request.ItemCodes.Length == 0) { request.CategoryID = request.Configuration.CategoryID; } var tempCategoryIDs = new List <int>(); var categoryIDs = new List <int>(); if (request.CategoryID != null) { // Get all category ids underneath the request's category id if (request.IncludeChildCategories) { using (var context = Exigo.Sql()) { categoryIDs.AddRange(context.Query <int>(@" WITH webcat (WebCategoryID, WebCategoryDescription, ParentID, NestedLevel) AS (SELECT WebCategoryID, WebCategoryDescription, ParentID, NestedLevel FROM WebCategories WHERE WebCategoryID = @masterCategoryID AND WebID = @webid UNION ALL SELECT w.WebCategoryID, w.WebCategoryDescription, w.ParentID, w.NestedLevel FROM WebCategories w INNER JOIN webcat c ON c.WebCategoryID = w.ParentID) SELECT WebCategoryID FROM webcat ", new { webid = GlobalSettings.Items.WebID, masterCategoryID = request.CategoryID }).ToList()); } } else { categoryIDs.Add(Convert.ToInt32(request.CategoryID)); } } // If we requested specific categories, get the item codes in the categories if (categoryIDs.Count > 0) { var categoryItemCodes = new List <string>(); using (var context = Exigo.Sql()) { categoryItemCodes = context.Query <string>(@" SELECT DISTINCT i.ItemCode ,c.SortOrder FROM WebCategoryItems c INNER JOIN Items i on c.ItemID = i.ItemID INNER JOIN WebCategories w on w.WebID = c.WebID and w.WebCategoryID = c.WebCategoryID WHERE c.WebID = @webid and c.WebCategoryID in @webcategoryids ORDER By c.SortOrder ", new { webid = GlobalSettings.Items.WebID, webcategoryids = categoryIDs }).ToList(); } var existingItemCodes = request.ItemCodes.ToList(); existingItemCodes.AddRange(categoryItemCodes); request.ItemCodes = existingItemCodes.ToArray(); } // Do a final check to ensure if the category we are looking at does not contain a item directly nested within it, we pull back the first child category if (request.ItemCodes.Length == 0 && request.CategoryID != null) { var tempItemCodeList = new List <string>(); using (var context = Exigo.Sql()) { tempItemCodeList = context.Query <string>(@" ;WITH webcat ( WebCategoryID ,WebCategoryDescription ,ParentID ,NestedLevel ,SortOrder ) AS ( SELECT WebCategoryID ,WebCategoryDescription ,ParentID ,NestedLevel ,SortOrder FROM WebCategories WHERE WebCategoryID = @masterCategoryID AND WebID = @webid UNION ALL SELECT w.WebCategoryID ,w.WebCategoryDescription ,w.ParentID ,w.NestedLevel ,w.SortOrder FROM WebCategories w INNER JOIN webcat c ON c.WebCategoryID = w.ParentID ) SELECT i.ItemCode FROM WebCategoryItems c INNER JOIN Items i ON c.ItemID = i.ItemID WHERE c.WebCategoryID = ( SELECT TOP 1 WebCategoryID FROM webcat WHERE ParentID = @masterCategoryID ORDER BY SortOrder ) ORDER BY c.SortOrder ", new { webid = GlobalSettings.Items.WebID, masterCategoryID = request.CategoryID }).ToList(); } request.ItemCodes = tempItemCodeList.ToArray(); } // If we don't have any items, stop here. if (request.ItemCodes.Length == 0) { yield break; } // Ensure our language ID is pulled from the Language Cookie request.LanguageID = Exigo.GetSelectedLanguageID(); // get the item information var priceTypeID = (request.PriceTypeID > 0) ? request.PriceTypeID : request.Configuration.PriceTypeID; var items = GetItemInformation(request, priceTypeID); // Populate the group members and dynamic kits if (items.Any()) { PopulateAdditionalItemData(items, request); } // Return the data foreach (var item in items) { yield return(item); } }