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