public static List <Item> GetItems(IEnumerable <ShoppingCartItem> shoppingCartItems, IOrderConfiguration configuration, int languageID, int _priceTypeID = 0)
{
var results = new List <Item>();
// If we don't have what we need to make this call, stop here.
if (configuration == null)
{
throw new InvalidRequestException("ExigoService.GetItems() requires an OrderConfiguration.");
}
if (shoppingCartItems.Count() == 0)
{
return(results);
}
// Create the contexts we will use
var priceTypeID = (_priceTypeID > 0) ? _priceTypeID : configuration.PriceTypeID;
var itemcodes = new List <string>();
shoppingCartItems.ToList().ForEach(c => itemcodes.Add(c.ItemCode));
var apiItems = GetItemInformation(new GetItemsRequest {
Configuration = configuration, LanguageID = languageID, ItemCodes = itemcodes.ToArray()
}, priceTypeID);
// Populate the group members and dynamic kits
if (apiItems.Any())
{
var request = new GetItemsRequest
{
LanguageID = languageID,
Configuration = configuration
};
PopulateAdditionalItemData(apiItems, request);
}
foreach (var cartItem in shoppingCartItems)
{
var apiItem = apiItems.FirstOrDefault(i => i.ItemCode == cartItem.ItemCode);
if (apiItem != null)
{
var newItem = (Item)apiItem.DeepClone();
newItem.ID = cartItem.ID;
newItem.Quantity = cartItem.Quantity;
newItem.ParentItemCode = cartItem.ParentItemCode;
newItem.GroupMasterItemCode = cartItem.GroupMasterItemCode;
newItem.DynamicKitCategory = cartItem.DynamicKitCategory;
newItem.Type = cartItem.Type;
results.Add(newItem);
}
}
// Return the data
return(results);
}
public static IEnumerable <Product> GetProducts(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.");
}
;
// Build the request.
//var getItemsrequest = new Common.Api.ExigoWebService.GetItemsRequest
//{
// ItemCodes = request.ItemCodes,
// CurrencyCode = request.Configuration.CurrencyCode,
// PriceType = request.Configuration.PriceTypeID,
// LanguageID = 0,
// WarehouseID = request.Configuration.WarehouseID,
// WebID = 1,
// WebCategoryID = (request.ItemCodes.Length == 0 ?
// request.Configuration.CategoryID as Nullable<int> :
// null)
//};
//var api = Exigo.WebService();
// Execute request and get the response.
//var response = api.GetItems(getItemsrequest);
//proc
List <Common.Api.ExigoWebService.ItemResponse> responseItems = new List <Common.Api.ExigoWebService.ItemResponse>();
using (var context = Exigo.Sql())
{
int webCategoryID = request.ItemCodes.Length == 0 ? request.Configuration.CategoryID : 0;
int webID = 1;
bool returnLongDetail = false;
string itemCodes = request.ItemCodes.Length == 0 ? "" : request.ItemCodes.FirstOrDefault();
string sqlProcedure = string.Format("GetPersonalOrderItems {0},'{1}',{2},{3},{4},'{5}',{6}"
, request.Configuration.PriceTypeID, request.Configuration.CurrencyCode,
request.Configuration.WarehouseID, webCategoryID, webID, itemCodes,
returnLongDetail);
responseItems = context.Query <Common.Api.ExigoWebService.ItemResponse>(sqlProcedure).ToList();
}
// Project items into products.
var products = responseItems.Select(item => new Product(item)).ToList();
//end proc
//foreach (var itemResponse in response.Items)
//{
// yield return new Product(itemResponse);
//}
return(products);
}
// Calls to populate additional data
private static void PopulateAdditionalItemData(IEnumerable <Item> items, GetItemsRequest request)
{
GlobalUtilities.RunAsyncTasks(
() => { PopulateItemImages(items); },
() => { PopulateGroupMembers(items, request); },
() =>
{
if (request.IncludeDynamicKitChildren)
{
PopulateDynamicKitMembers(items, request.Configuration, request.LanguageID, request);
}
}
);
}
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.");
}
;
// Build the request.
var getItemsrequest = new Common.Api.ExigoWebService.GetItemsRequest
{
ItemCodes = request.ItemCodes,
CurrencyCode = request.Configuration.CurrencyCode,
PriceType = request.Configuration.PriceTypeID,
LanguageID = 0,
WarehouseID = request.Configuration.WarehouseID,
WebID = 1,
WebCategoryID = (request.ItemCodes.Length == 0 ?
request.Configuration.CategoryID as Nullable <int> :
null)
};
var api = Exigo.WebService();
// Execute request and get the response.
var response = api.GetItems(getItemsrequest);
foreach (var itemResponse in response.Items)
{
yield return((Item)itemResponse);
}
}
public JsonNetResult GetItemList(int categoryID = 0)
{
try
{
var items = new List <Item>();
var newItems = new List <Item>();
ExigoService.GetItemsRequest itemsRequest;
var defaultCategoryID = (categoryID == 0) ? OrderConfiguration.CategoryID : categoryID;
var categories = Exigo.GetWebCategoriesRecursively(defaultCategoryID).OrderBy(c => c.SortOrder);
if (categories != null && categories.Count() > 0)
{
var featuredCategoryID = OrderConfiguration.FeaturedCategoryID.Equals(0) ? OrderConfiguration.CategoryID : OrderConfiguration.FeaturedCategoryID;
var category = categories.Where(c => c.ParentID == featuredCategoryID).FirstOrDefault();
itemsRequest = new ExigoService.GetItemsRequest
{
Configuration = OrderConfiguration,
IncludeChildCategories = true,
CategoryID = category.WebCategoryID
};
newItems = Exigo.GetItems(itemsRequest).OrderBy(c => c.SortOrder).ToList();
foreach (var newItem in newItems)
{
if (items.Count(i => i.ItemCode == newItem.ItemCode).Equals(0))
{
items.Add(newItem);
}
}
}
else
{
itemsRequest = new ExigoService.GetItemsRequest
{
Configuration = OrderConfiguration,
IncludeChildCategories = true,
CategoryID = categoryID
};
items = Exigo.GetItems(itemsRequest).OrderBy(c => c.SortOrder).ToList();
}
var html = this.RenderPartialViewToString("Partials/Items/_ShoppingItemList", items);
return(new JsonNetResult(new
{
success = true,
html = html
}));
}
catch (Exception ex)
{
return(new JsonNetResult(new
{
success = false,
message = ex.Message
}));
}
}
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;
}
// Create the contexts we will use
var context = Exigo.OData();
// Determine how many categories we need to pull based on the levels. Currently designed to go one level deep.
var categoryIDs = new List <int>();
if (request.CategoryID != null)
{
categoryIDs.Add((int)request.CategoryID);
if (request.IncludeChildCategories)
{
// Get the child categories
var ids = context.WebCategories
.Where(c => c.WebID == 1)
.Where(c => c.ParentID == (int)request.CategoryID)
.Select(c => new
{
c.WebCategoryID
}).ToList();
categoryIDs.AddRange(ids.Select(c => c.WebCategoryID));
}
}
// If we requested a specific category, get the item codes in the category
if (categoryIDs.Count > 0)
{
var categoryItemCodes = context.WebCategoryItems
.Where(c => c.WebID == 1)
.Where(categoryIDs.ToOrExpression <WebCategoryItem, int>("WebCategoryID"))
.Select(c => new
{
c.Item.ItemCode
}).ToList();
var existingItemCodes = request.ItemCodes.ToList();
existingItemCodes.AddRange(categoryItemCodes.Select(c => c.ItemCode).ToList());
request.ItemCodes = existingItemCodes.ToArray();
}
// If we don't have any items, stop here.
if (request.ItemCodes.Length == 0)
{
yield break;
}
// Get the data
var query = context.ItemWarehousePrices.Expand("Item/GroupMembers")
.Where(c => c.WarehouseID == request.Configuration.WarehouseID)
.Where(c => c.PriceTypeID == request.Configuration.PriceTypeID)
.Where(c => c.CurrencyCode == request.Configuration.CurrencyCode);
if (request.ItemCodes != null && request.ItemCodes.Count() > 0)
{
query = query.Where(request.ItemCodes.ToList().ToOrExpression <ItemWarehousePrice, string>("Item.ItemCode"));
}
var odataItems = query.ToList();
// Return the data
foreach (var item in odataItems)
{
yield return((ExigoService.Item)item);
}
}
/// <summary>
/// Capture IItemDetailFilterRequest details and pass up to sibling constructor to capture language/configuration
/// </summary>
public ItemInformationRequest(GetItemsRequest request) : this(request.Configuration, request.LanguageID)
{
request.CopyPropertiesTo <IItemDetailFilterRequest>(this);
}
// Get Static Kit item and Children
public static List <Item> GetStaticKitChildren(List <string> itemCode, GetItemsRequest request)
{
var items = new List <Item>();
using (var context = Exigo.Sql())
{
items = context.Query <Item>(@"
SELECT
ItemID = i.ItemID,
ItemCode = i.ItemCode,
ItemDescription =
case
when i.IsGroupMaster = 1 then COALESCE(i.GroupDescription, il.ItemDescription, i.ItemDescription)
when il.ItemDescription != '' then COALESCE(il.ItemDescription, i.ItemDescription) else i.ItemDescription
end,
Weight = i.Weight,
ItemTypeID = i.ItemTypeID,
TinyImageUrl = i.TinyImageName,
SmallImageUrl = i.SmallImageName,
LargeImageUrl = i.LargeImageName,
ShortDetail1 = COALESCE(il.ShortDetail, i.ShortDetail),
ShortDetail2 = COALESCE(il.ShortDetail2, i.ShortDetail2),
ShortDetail3 = COALESCE(il.ShortDetail3, i.ShortDetail3),
ShortDetail4 = COALESCE(il.ShortDetail4, i.ShortDetail4),
LongDetail1 = COALESCE(il.LongDetail, i.LongDetail),
LongDetail2 = COALESCE(il.LongDetail2, i.LongDetail2),
LongDetail3 = COALESCE(il.LongDetail3, i.LongDetail3),
LongDetail4 = COALESCE(il.LongDetail4, i.LongDetail4),
IsVirtual = i.IsVirtual,
AllowOnAutoOrder = i.AllowOnAutoOrder,
AllowPersonalization = i.OtherCheck1,
IsGroupMaster = i.IsGroupMaster,
IsDynamicKitMaster = cast(case when i.ItemTypeID = 2 then 1 else 0 end as bit),
GroupMasterItemDescription = i.GroupDescription,
GroupMembersDescription = i.GroupMembersDescription,
Field1 = i.Field1,
Field2 = i.Field2,
Field3 = i.Field3,
Field4 = i.Field4,
Field5 = i.Field5,
Field6 = i.Field6,
Field7 = i.Field7,
Field8 = i.Field8,
Field9 = i.Field9,
Field10 = i.Field10,
OtherCheck1 = i.OtherCheck1,
OtherCheck2 = i.OtherCheck2,
OtherCheck3 = i.OtherCheck3,
OtherCheck4 = i.OtherCheck4,
OtherCheck5 = i.OtherCheck5,
Auto1 = i.Auto1,
Auto2 = i.Auto2,
Auto3 = i.Auto3,
--Price = ip.Price,
--CurrencyCode = ip.CurrencyCode,
--BV = ip.BusinessVolume,
--CV = ip.CommissionableVolume,
--OtherPrice1 = ip.Other1Price,
--OtherPrice2 = ip.Other2Price,
--OtherPrice3 = ip.Other3Price,
--OtherPrice4 = ip.Other4Price,
--OtherPrice5 = ip.Other5Price,
--OtherPrice6 = ip.Other6Price,
--OtherPrice7 = ip.Other7Price,
--OtherPrice8 = ip.Other8Price,
--OtherPrice9 = ip.Other9Price,
--OtherPrice10 = ip.Other10Price,
Quantity = iskm.Quantity,
ParentItemCode = mi.ItemCode
FROM ItemStaticKitMembers iskm
INNER JOIN Items i
ON i.ItemID = iskm.ItemID
INNER JOIN Items mi
ON mi.ItemID = iskm.MasterItemID
--INNER JOIN ItemPrices ip
-- ON ip.ItemID = i.ItemID
-- AND ip.PriceTypeID = @priceTypeID
INNER JOIN ItemWarehouses iw
ON iw.ItemID = i.ItemID
AND iw.WarehouseID = @warehouse
LEFT JOIN ItemLanguages il
ON il.ItemID = i.ItemID
AND il.LanguageID = @languageID
WHERE iskm.MasterItemID in (select ItemID from Items where ItemCode = @itemcode)
--AND ip.CurrencyCode = @currencyCode
", new
{
warehouse = request.Configuration.WarehouseID,
currencyCode = request.Configuration.CurrencyCode,
priceTypeID = request.Configuration.PriceTypeID,
languageID = request.LanguageID,
itemcode = itemCode
}).ToList();
}
// Return the data
foreach (var item in items)
{
// get the correct product path
item.LargeImageUrl = GlobalUtilities.GetProductImagePath(item.LargeImageUrl);
item.SmallImageUrl = GlobalUtilities.GetProductImagePath(item.SmallImageUrl);
item.TinyImageUrl = GlobalUtilities.GetProductImagePath(item.TinyImageUrl);
}
return(items);
}
public GetItemInformationRequest(GetItemsRequest request) : base(request)
{
this.ItemCodes = new List <string>();
}
private static void PopulateDynamicKitMembers(IEnumerable <Item> items, IOrderConfiguration configuration, int languageID, GetItemsRequest request)
{
try
{
// Determine if we have any dynamic kit items
var dynamicKitMasterItemCodes = items.Where(c => c.IsDynamicKitMaster).Select(c => c.ItemCode).ToList();
if (dynamicKitMasterItemCodes.Count == 0)
{
return;
}
using (var context = Exigo.Sql())
{
var allKitCategoryItems = context.Query <DynamicKitCategoryItem>(@"
select
MasterItemID = idkcm.MasterItemID,
DynamicKitCategoryID = idkcm.DynamicKitCategoryID,
DynamicKitCategory = dkc.DynamicKitCategoryDescription,
Quantity = idkcm.Quantity,
ItemID = i.ItemID,
ItemCode = i.ItemCode,
ItemDescription = i.ItemDescription,
TinyImage = i.TinyImageName,
SmallImage = i.SmallImageName,
LargeImage = i.LargeImageName
from ItemDynamicKitCategoryMembers idkcm
inner join ItemDynamicKitCategories dkc
on dkc.DynamicKitCategoryID = idkcm.DynamicKitCategoryID
inner join Items mi
on idkcm.MasterItemID = mi.ItemID
and mi.ItemCode in @itemcodes
left join ItemDynamicKitCategoryItemMembers dkitem
on dkitem.DynamicKitCategoryID = idkcm.DynamicKitCategoryID
left join Items i
on dkitem.ItemID = i.ItemID
", new { itemCodes = dynamicKitMasterItemCodes }).ToList();
foreach (var dynamicKitMasterItemCode in dynamicKitMasterItemCodes)
{
// Get the specifc master DK item
var item = items.Where(c => c.ItemCode == dynamicKitMasterItemCode).FirstOrDefault();
if (item == null)
{
continue;
}
// Get the appropriate kit category items for the master DK item
var kitCategoryItems = allKitCategoryItems.Where(c => c.MasterItemID == item.ItemID).ToList();
var itemCodeCategoryKeys = kitCategoryItems.Select(c => new
{
ItemCode = c.ItemCode,
DynamicKitCategoryID = c.DynamicKitCategoryID
});
var itemCodes = itemCodeCategoryKeys.Select(c => c.ItemCode);
var categories = new List <DynamicKitCategory>();
// Assemble a unique list of kit categories
foreach (var cat in kitCategoryItems)
{
if (categories.Where(c => c.DynamicKitCategoryID == cat.DynamicKitCategoryID).Count() == 0)
{
var category = new DynamicKitCategory();
category.Quantity = cat.Quantity;
category.DynamicKitCategoryDescription = cat.DynamicKitCategory;
category.DynamicKitCategoryID = cat.DynamicKitCategoryID;
categories.Add(category);
}
}
// Add the list of kit categories to the master DK item
item.DynamicKitCategories = categories;
// get the information for the child kit category items
var childrenItems = GetItems(new GetItemsRequest {
Configuration = configuration, LanguageID = languageID, ItemCodes = itemCodes.ToArray()
});
// Map the items to correct kit categories on the the master DK item
foreach (var citem in childrenItems)
{
var _category = itemCodeCategoryKeys.Where(c => c.ItemCode == citem.ItemCode).FirstOrDefault();
if (_category != null)
{
var itemCategory = item.DynamicKitCategories.FirstOrDefault(c => c.DynamicKitCategoryID == _category.DynamicKitCategoryID);
itemCategory.Items.Add(new DynamicKitCategoryItem
{
ItemID = citem.ItemID,
ItemDescription = citem.ItemDescription,
TinyImageUrl = citem.TinyImageUrl,
SmallImageUrl = citem.SmallImageUrl,
LargeImageUrl = citem.LargeImageUrl,
ItemCode = citem.ItemCode,
Quantity = itemCategory.Quantity,
ParentItemCode = dynamicKitMasterItemCode,
GroupMasterItemCode = dynamicKitMasterItemCode,
DynamicKitCategory = itemCategory.DynamicKitCategoryDescription,
DynamicKitCategoryID = itemCategory.DynamicKitCategoryID
});
}
// Get the static kit members if needed
if (citem.ItemTypeID == 1)
{
var itemCodeAsList = new List <string>()
{
citem.ItemCode
};
item.StaticKitChildren = GetStaticKitChildren(itemCodeAsList, request);
}
}
}
}
}
catch { }
}
private static void PopulateGroupMembers(IEnumerable <Item> items, GetItemsRequest request)
{
int languageID = request.LanguageID;
string currencyCode = request.Configuration.CurrencyCode;
int warehouseID = request.Configuration.WarehouseID;
try
{
// Determine if we have any group master items
var groupMasterItemIDs = items.Where(c => c.IsGroupMaster).Select(c => c.ItemID).ToList();
if (groupMasterItemIDs.Count == 0)
{
return;
}
// Get a list of group member items for all the group master items
var itemGroupMembers = new List <ItemGroupMember>();
using (var context = Exigo.Sql())
{
context.Open();
itemGroupMembers = context.Query <Item, ItemGroupMember, ItemGroupMember>(@"
SELECT
ItemCode = i.ItemCode,
ItemDescription = i.ItemDescription,
Weight = i.Weight,
ItemTypeID = i.ItemTypeID,
TinyImageUrl = i.TinyImageName,
SmallImageUrl = i.SmallImageName,
LargeImageUrl = i.LargeImageName,
ShortDetail1 = COALESCE(il.ShortDetail, i.ShortDetail),
ShortDetail2 = COALESCE(il.ShortDetail2, i.ShortDetail2),
ShortDetail3 = COALESCE(il.ShortDetail3, i.ShortDetail3),
ShortDetail4 = COALESCE(il.ShortDetail4, i.ShortDetail4),
LongDetail1 = COALESCE(il.LongDetail, i.LongDetail),
LongDetail2 = COALESCE(il.LongDetail2, i.LongDetail2),
LongDetail3 = COALESCE(il.LongDetail3, i.LongDetail3),
LongDetail4 = COALESCE(il.LongDetail4, i.LongDetail4),
IsVirtual = i.IsVirtual,
AllowOnAutoOrder = i.AllowOnAutoOrder,
IsGroupMaster = i.IsGroupMaster,
IsDynamicKitMaster = cast(case when i.ItemTypeID = 2 then 1 else 0 end as bit),
GroupMasterItemDescription = i.GroupDescription,
GroupMembersDescription = i.GroupMembersDescription,
Field1 = i.Field1,
Field2 = i.Field2,
Field3 = i.Field3,
Field4 = i.Field4,
Field5 = i.Field5,
Field6 = i.Field6,
Field7 = i.Field7,
Field8 = i.Field8,
Field9 = i.Field9,
Field10 = i.Field10,
OtherCheck1 = i.OtherCheck1,
OtherCheck2 = i.OtherCheck2,
OtherCheck3 = i.OtherCheck3,
OtherCheck4 = i.OtherCheck4,
OtherCheck5 = i.OtherCheck5,
Auto1 = i.Auto1,
Auto2 = i.Auto2,
Auto3 = i.Auto3,
Price = ip.Price,
CurrencyCode = ip.CurrencyCode,
BV = ip.BusinessVolume,
CV = ip.CommissionableVolume,
OtherPrice1 = ip.Other1Price,
OtherPrice2 = ip.Other2Price,
OtherPrice3 = ip.Other3Price,
OtherPrice4 = ip.Other4Price,
OtherPrice5 = ip.Other5Price,
OtherPrice6 = ip.Other6Price,
OtherPrice7 = ip.Other7Price,
OtherPrice8 = ip.Other8Price,
OtherPrice9 = ip.Other9Price,
OtherPrice10 = ip.Other10Price,
MasterItemID = im.MasterItemID,
MemberDescription = im.GroupMemberDescription,
SortOrder = im.Priority,
ItemID = i.ItemID
FROM ItemGroupMembers im
inner join Items i
on i.ItemID = im.ItemID
INNER JOIN ItemPrices ip
ON ip.ItemID = i.ItemID
AND ip.PriceTypeID = @priceTypeID
INNER JOIN ItemWarehouses iw
ON iw.ItemID = i.ItemID
AND iw.WarehouseID = @warehouse
LEFT JOIN ItemLanguages il
ON il.ItemID = i.ItemID
AND il.LanguageID = @languageID
WHERE ip.CurrencyCode = @currencyCode
AND im.ItemID != im.MasterItemID
AND im.MasterItemID in @groupMasterItemIDs
", (Item, ItemGroupMember) =>
{
ItemGroupMember.Item = Item;
ItemGroupMember.ItemCode = Item.ItemCode;
return(ItemGroupMember);
}, new
{
warehouse = request.Configuration.WarehouseID,
currencyCode = request.Configuration.CurrencyCode,
languageID = request.LanguageID,
priceTypeID = request.Configuration.PriceTypeID,
groupMasterItemIDs = groupMasterItemIDs
}, splitOn: "MasterItemID").ToList();
context.Close();
}
//bind the item group members to the group master items
foreach (var groupmasteritemid in groupMasterItemIDs)
{
var masteritem = items.Where(c => c.ItemID == groupmasteritemid).FirstOrDefault();
if (masteritem == null)
{
continue;
}
masteritem.GroupMembers = itemGroupMembers
.Where(c => c.MasterItemID == groupmasteritemid)
.OrderBy(c => c.SortOrder)
.ToList();
// populate the master item's basic details for cart purposes
foreach (var groupmember in masteritem.GroupMembers)
{
groupmember.Item = groupmember.Item ?? new Item();
groupmember.Item.ItemCode = groupmember.ItemCode;
groupmember.Item.GroupMasterItemCode = masteritem.ItemCode;
}
}
}
catch { }
}
private static List <Item> GetItemInformation(GetItemsRequest request, int priceTypeID)
{
try
{
var apiItems = new List <Item>();
int warehouseID = request.Configuration.WarehouseID;
string currencyCode = request.Configuration.CurrencyCode;
int languageID = request.LanguageID;
List <string> itemCodes = request.ItemCodes.ToList();
using (var context = Exigo.Sql())
{
apiItems = context.Query <Item>(@"
SELECT
ItemID = i.ItemID,
ItemCode = i.ItemCode,
ItemDescription =
case
when i.IsGroupMaster = 1 then COALESCE(i.GroupDescription, il.ItemDescription, i.ItemDescription)
when il.ItemDescription != '' then COALESCE(il.ItemDescription, i.ItemDescription)
else i.ItemDescription
end,
Weight = i.Weight,
ItemTypeID = i.ItemTypeID,
TinyImageUrl = i.TinyImageName,
SmallImageUrl = i.SmallImageName,
LargeImageUrl = i.LargeImageName,
ShortDetail1 = COALESCE(il.ShortDetail, i.ShortDetail),
ShortDetail2 = COALESCE(il.ShortDetail2, i.ShortDetail2),
ShortDetail3 = COALESCE(il.ShortDetail3, i.ShortDetail3),
ShortDetail4 = COALESCE(il.ShortDetail4, i.ShortDetail4),
LongDetail1 = COALESCE(il.LongDetail, i.LongDetail),
LongDetail2 = COALESCE(il.LongDetail2, i.LongDetail2),
LongDetail3 = COALESCE(il.LongDetail3, i.LongDetail3),
LongDetail4 = COALESCE(il.LongDetail4, i.LongDetail4),
IsVirtual = i.IsVirtual,
AllowOnAutoOrder = i.AllowOnAutoOrder,
IsGroupMaster = i.IsGroupMaster,
IsDynamicKitMaster = cast(case when i.ItemTypeID = 2 then 1 else 0 end as bit),
GroupMasterItemDescription = i.GroupDescription,
GroupMembersDescription = i.GroupMembersDescription,
Field1 = i.Field1,
Field2 = i.Field2,
Field3 = i.Field3,
Field4 = i.Field4,
Field5 = i.Field5,
Field6 = i.Field6,
Field7 = i.Field7,
Field8 = i.Field8,
Field9 = i.Field9,
Field10 = i.Field10,
OtherCheck1 = i.OtherCheck1,
OtherCheck2 = i.OtherCheck2,
OtherCheck3 = i.OtherCheck3,
OtherCheck4 = i.OtherCheck4,
OtherCheck5 = i.OtherCheck5,
Price = ip.Price,
CurrencyCode = ip.CurrencyCode,
BV = ip.BusinessVolume,
CV = ip.CommissionableVolume,
OtherPrice1 = ip.Other1Price,
OtherPrice2 = ip.Other2Price,
OtherPrice3 = ip.Other3Price,
OtherPrice4 = ip.Other4Price,
OtherPrice5 = ip.Other5Price,
OtherPrice6 = ip.Other6Price,
OtherPrice7 = ip.Other7Price,
OtherPrice8 = ip.Other8Price,
OtherPrice9 = ip.Other9Price,
OtherPrice10 = ip.Other10Price
FROM Items i
INNER JOIN ItemPrices ip
ON ip.ItemID = i.ItemID
AND ip.PriceTypeID = @priceTypeID
AND ip.CurrencyCode = @currencyCode
INNER JOIN ItemWarehouses iw
ON iw.ItemID = i.ItemID
AND iw.WarehouseID = @warehouse
LEFT JOIN ItemLanguages il
ON il.ItemID = i.ItemID
AND il.LanguageID = @languageID
WHERE i.ItemCode in @itemCodes
", new
{
warehouse = warehouseID,
currencyCode = currencyCode,
languageID = languageID,
itemCodes = itemCodes,
priceTypeID = priceTypeID
}).ToList();
}
// Handle Sorting here, the sort order was based on the order of the Item Codes passed in originally
var length = request.ItemCodes.Count();
var orderedItems = new List <Item>();
for (var i = 0; i < length; i++)
{
var matchingItem = apiItems.FirstOrDefault(c => c.ItemCode == request.ItemCodes[i]);
if (matchingItem != null)
{
orderedItems.Add(matchingItem);
}
}
return(orderedItems);
}
catch (Exception e)
{
throw;
}
}
public PopulateAdditionalItemDataRequest(GetItemsRequest request) : base(request)
{
request.CopyPropertiesTo <IChildItemRequest>(this);
this.Items = new List <Item>();
}
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;
}
// Create the contexts we will use
var context = Exigo.OData();
// Determine how many categories we need to pull based on the levels. Currently designed to go one level deep.
var categoryIDs = new List<int>();
if (request.CategoryID != null)
{
categoryIDs.Add((int)request.CategoryID);
if (request.IncludeChildCategories)
{
// Get the child categories
var ids = context.WebCategories
.Where(c => c.WebID == 1)
.Where(c => c.ParentID == (int)request.CategoryID)
.Select(c => new
{
c.WebCategoryID
}).ToList();
categoryIDs.AddRange(ids.Select(c => c.WebCategoryID));
}
}
// If we requested a specific category, get the item codes in the category
if (categoryIDs.Count > 0)
{
var categoryItemCodes = context.WebCategoryItems
.Where(c => c.WebID == 1)
.Where(categoryIDs.ToOrExpression<WebCategoryItem, int>("WebCategoryID"))
.Select(c => new
{
c.Item.ItemCode
}).ToList();
var existingItemCodes = request.ItemCodes.ToList();
existingItemCodes.AddRange(categoryItemCodes.Select(c => c.ItemCode).Distinct().ToList());
request.ItemCodes = existingItemCodes.ToArray();
}
// If we don't have any items, stop here.
if (request.ItemCodes.Length == 0) yield break;
// Get the data
var query = context.ItemWarehousePrices.Expand("Item")
.Where(c => c.WarehouseID == request.Configuration.WarehouseID)
.Where(c => c.PriceTypeID == request.Configuration.PriceTypeID)
.Where(c => c.CurrencyCode == request.Configuration.CurrencyCode);
if (request.ItemCodes != null && request.ItemCodes.Count() > 0)
{
query = query.Where(request.ItemCodes.ToList().ToOrExpression<ItemWarehousePrice, string>("Item.ItemCode"));
}
var odataItems = query.ToList();
var items = odataItems.Select(c => (ExigoService.Item)c).ToList();
// Populate the group members and dynamic kits
PopulateGroupMembers(items, request.Configuration);
PopulateDynamicKitMembers(items);
// Return the data
foreach (var item in items)
{
yield return item;
}
}
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);
}
}
// Item call for product lists
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 = 1
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
{
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
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 = 1
and c.WebCategoryID in @webcategoryids
", new
{
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)
AS (SELECT WebCategoryID,
WebCategoryDescription,
ParentID,
NestedLevel
FROM WebCategories
WHERE WebCategoryID = @masterCategoryID
AND WebID = 1
UNION ALL
SELECT w.WebCategoryID,
w.WebCategoryDescription,
w.ParentID,
w.NestedLevel
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 CASE WHEN WebCategoryDescription like 'Special%' THEN 0 ELSE 1 END asc)
", new
{
masterCategoryID = request.CategoryID
}).ToList();
}
request.ItemCodes = tempItemCodeList.ToArray();
}
// If we don't have any items, stop here.
if (request.ItemCodes.Length == 0)
{
yield break;
}
// get the item information
var items = new List <ExigoService.Item>();
var priceTypeID = (request.PriceTypeID > 0) ? request.PriceTypeID : request.Configuration.PriceTypeID;
try
{
using (var context = Exigo.Sql())
{
items = context.Query <ExigoService.Item>(@"
SELECT
ItemID = i.ItemID,
ItemCode = i.ItemCode,
ItemDescription =
case
when i.IsGroupMaster = 1 then COALESCE(i.GroupDescription, il.ItemDescription, i.ItemDescription)
when il.ItemDescription != '' then COALESCE(il.ItemDescription, i.ItemDescription)
else i.ItemDescription
end,
Weight = i.Weight,
ItemTypeID = i.ItemTypeID,
TinyImageUrl = i.TinyImageName,
SmallImageUrl = i.SmallImageName,
LargeImageUrl = i.LargeImageName,
ShortDetail1 = COALESCE(il.ShortDetail, i.ShortDetail),
ShortDetail2 = COALESCE(il.ShortDetail2, i.ShortDetail2),
ShortDetail3 = COALESCE(il.ShortDetail3, i.ShortDetail3),
ShortDetail4 = COALESCE(il.ShortDetail4, i.ShortDetail4),
LongDetail1 = COALESCE(il.LongDetail, i.LongDetail),
LongDetail2 = COALESCE(il.LongDetail2, i.LongDetail2),
LongDetail3 = COALESCE(il.LongDetail3, i.LongDetail3),
LongDetail4 = COALESCE(il.LongDetail4, i.LongDetail4),
IsVirtual = i.IsVirtual,
AllowOnAutoOrder = i.AllowOnAutoOrder,
IsGroupMaster = i.IsGroupMaster,
IsDynamicKitMaster = cast(case when i.ItemTypeID = 2 then 1 else 0 end as bit),
GroupMasterItemDescription = i.GroupDescription,
GroupMembersDescription = i.GroupMembersDescription,
Field1 = i.Field1,
Field2 = i.Field2,
Field3 = i.Field3,
Field4 = i.Field4,
Field5 = i.Field5,
Field6 = i.Field6,
Field7 = i.Field7,
Field8 = i.Field8,
Field9 = i.Field9,
Field10 = i.Field10,
OtherCheck1 = i.OtherCheck1,
OtherCheck2 = i.OtherCheck2,
OtherCheck3 = i.OtherCheck3,
OtherCheck4 = i.OtherCheck4,
OtherCheck5 = i.OtherCheck5,
Price = ip.Price,
CurrencyCode = ip.CurrencyCode,
BV = ip.BusinessVolume,
CV = ip.CommissionableVolume,
OtherPrice1 = ip.Other1Price,
OtherPrice2 = ip.Other2Price,
OtherPrice3 = ip.Other3Price,
OtherPrice4 = ip.Other4Price,
OtherPrice5 = ip.Other5Price,
OtherPrice6 = ip.Other6Price,
OtherPrice7 = ip.Other7Price,
OtherPrice8 = ip.Other8Price,
OtherPrice9 = ip.Other9Price,
OtherPrice10 = ip.Other10Price,
a.Price AS RetailPrice,
a.PriceTypeID AS PriceTypeID1,
b.Price AS SmartShopperPrice,
b.PriceTypeID AS PriceTypeID2
FROM Items i
INNER JOIN ItemPrices ip
ON ip.ItemID = i.ItemID
AND ip.PriceTypeID = @priceTypeID
AND ip.CurrencyCode = @currencyCode
INNER JOIN ItemPrices a
ON a.ItemID = ip.ItemID
AND a.CurrencyCode = @currencyCode
AND a.PriceTypeID = @FirstPriceTypeID
INNER JOIN ItemPrices b
ON b.ItemID = ip.ItemID
AND b.CurrencyCode = @currencyCode
AND b.PriceTypeID = @SecondPriceTypeID
INNER JOIN ItemWarehouses iw
ON iw.ItemID = i.ItemID
AND iw.WarehouseID = @warehouse
LEFT JOIN ItemLanguages il
ON il.ItemID = i.ItemID
AND il.LanguageID = @languageID
WHERE i.ItemCode in @itemCodes
", new
{
warehouse = request.Configuration.WarehouseID,
currencyCode = request.Configuration.CurrencyCode,
languageID = request.LanguageID,
itemCodes = request.ItemCodes,
priceTypeID = priceTypeID,
FirstPriceTypeID = PriceTypes.Retail,
SecondPriceTypeID = PriceTypes.Wholesale
}).ToList();
}
}
catch (Exception e)
{
Console.Write(e);
}
if (items.Any())
{
// Populate the group members and dynamic kits
PopulateAdditionalItemData(items, request.LanguageID, request.Configuration.CurrencyCode, request.Configuration.WarehouseID);
}
// Return the data
foreach (var item in items)
{
yield return(item);
}
}
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.HasValue && !request.ItemCodes.Any())
{
throw new InvalidRequestException("ExigoService.GetItems() requires either a CategoryID or a collection of item codes.");
}
;
#region Categories
// Set some defaults
var topCategoryID = request.CategoryID ?? request.Configuration.CategoryID;
var categoryIDs = new List <int>()
{
topCategoryID
};
// Get all category ids underneath the request's category id
if (request.IncludeAllChildCategories)
{
var childCategoryIDs = GetChildCategoryIDs(topCategoryID);
categoryIDs.AddRange(childCategoryIDs);
}
// if no categories, stop here (this should never happen. included for consistency)
if (!categoryIDs.Any())
{
yield break;
}
#endregion
#region Item Codes
// If we requested specific categories, get the item codes in the categories
var itemCodes = new List <string>();
var hasCartItems = request.ShoppingCartItems.Any();
// if we have cart items, use those first
if (hasCartItems)
{
var parentItems = request.ShoppingCartItems
.ToList();
var parentItemCodes = parentItems
.Select(i => i.ItemCode)
.Distinct()
.ToList();
itemCodes.AddRange(parentItemCodes);
}
// get and/or filter itemcodes
else
{
var availableItemCodes = ExigoDAL.GetWebCategoryItemCodes(categoryIDs);
if (request.ItemCodes.Any())
{
// if we have no cart items, decide whether to restrict or not.
if (request.IgnoreCategoryRestrictions)
{
// If we're ignoring category restrictions, get the item codes requested only.
// (This should only be done for [dynamic/static] kit children. Items must still be available in the requested warehouse)
itemCodes.AddRange(request.ItemCodes);
}
else
{
// Default behavior: filter request item codes by the item codes available in the selected categories
var filteredItemCodes = availableItemCodes.Where(c => request.ItemCodes.Any(rc => rc == c));
itemCodes.AddRange(filteredItemCodes);
}
}
else
{
// if no item codes are requested, add all available item codes
itemCodes.AddRange(availableItemCodes);
}
}
// If we don't have any item codes, stop here. (This will happen if request.ItemCodes are not available in the categories requested)
if (!itemCodes.Any())
{
yield break;
}
#endregion
#region Items
// get the item information
var priceTypeID = (request.PriceTypeID.HasValue && request.PriceTypeID.Value > 0) ? request.PriceTypeID.Value : request.Configuration.PriceTypeID;
var items = GetItemInformation(new GetItemInformationRequest(request)
{
PriceTypeID = priceTypeID,
ItemCodes = itemCodes
});
// If we don't have any items, stop here.
if (!items.Any())
{
yield break;
}
#endregion
#region Additional Details
// Populate the group members and dynamic kits
PopulateAdditionalItemData(new PopulateAdditionalItemDataRequest(request)
{
Items = items,
WebCategoryIDs = categoryIDs
});
#endregion
// if we provided shopping cart items, return our items relative to our shoppingCartItems.
// (Note: This process will duplicate items if there were both order and autoorder items of the same code. This is necessary and expected.)
if (hasCartItems)
{
items = MergeShoppingCartItemDetailsWith <Item>(request.ShoppingCartItems, items).ToList();
}
// Return the data
foreach (var item in items)
{
yield return(item);
}
}
