public Tax GetWithTaxRate(int taxId, int countryId, int stateProvinceId, string zipOrPostalCode)
{
Expression <Func <TaxRate, bool> > where = rate => rate.CountryId == countryId;
var tax = Repository.Where(x => x.Id == taxId)
.Join <TaxRate>("Id", "TaxId", joinType: JoinType.LeftOuter)
.Where(where)
.Relate(RelationTypes.OneToMany <Tax, TaxRate>())
.SelectNested()
.FirstOrDefault();
if (tax == null)
{
return(null);
}
//filter by state and zip
var taxRates = tax.TaxRates.Where(x => x.StateOrProvinceId == stateProvinceId &&
x.ZipOrPostalCode == zipOrPostalCode)
.ToList();
if (taxRates.Any())
{
tax.TaxRates = taxRates;
return(tax);
}
//filter by state only
taxRates = tax.TaxRates.Where(x => x.StateOrProvinceId == stateProvinceId).ToList();
if (taxRates.Any())
{
tax.TaxRates = taxRates;
return(tax);
}
return(tax);
}
public override ProductSpecification Get(int id)
{
return(Repository.Where(x => x.Id == id)
.Join <ProductSpecificationValue>("Id", "ProductSpecificationId", joinType: JoinType.LeftOuter)
.Join <AvailableAttributeValue>("AvailableAttributeValueId", "Id", joinType: JoinType.LeftOuter)
.Join <AvailableAttribute>("AvailableAttributeId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <ProductSpecification, ProductSpecificationValue>())
.Relate <AvailableAttributeValue>((productSpecification, availableAttributeValue) =>
{
if (productSpecification.Tag == null)
{
productSpecification.Tag = new List <AvailableAttributeValue>();
}
var attributeList = (List <AvailableAttributeValue>)productSpecification.Tag;
if (!attributeList.Contains(availableAttributeValue))
{
attributeList.Add(availableAttributeValue);
}
var pav = productSpecification.ProductSpecificationValues.FirstOrDefault(
x => x.AvailableAttributeValueId == availableAttributeValue.Id);
if (pav != null)
{
pav.AvailableAttributeValue = availableAttributeValue;
}
})
.Relate <AvailableAttribute>((productSpecification, availableAttribute) =>
{
availableAttribute.AvailableAttributeValues = (List <AvailableAttributeValue>)productSpecification.Tag;
productSpecification.AvailableAttribute = availableAttribute;
})
.SelectNested()
.FirstOrDefault());
}
public override AvailableAttribute Get(int id)
{
return(Repository.Join <AvailableAttributeValue>("Id", "AvailableAttributeId", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <AvailableAttribute, AvailableAttributeValue>())
.Where(x => x.Id == id)
.SelectNested()
.FirstOrDefault());
}
public override Vendor Get(int id)
{
return(Repository.Where(x => x.Id == id)
.Join <VendorUser>("Id", "VendorId", joinType: JoinType.LeftOuter)
.Join <User>("UserId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <Vendor, User>())
.SelectNested()
.FirstOrDefault());
}
public override Role Get(int id)
{
return(Repository.Where(x => x.Id == id)
.Join <RoleCapability>("Id", "RoleId", joinType: JoinType.LeftOuter)
.Join <Capability>("CapabilityId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <Role, Capability>())
.SelectNested()
.FirstOrDefault());
}
public ConsentGroup GetWithConsents(int consentGroupId)
{
Expression <Func <Consent, object> > orderBy = consent => consent.DisplayOrder;
var consentGroup = Repository.Join <Consent>("Id", "ConsentGroupId")
.Relate(RelationTypes.OneToMany <ConsentGroup, Consent>())
.Where(x => x.Id == consentGroupId)
.OrderBy(orderBy, RowOrder.Ascending)
.SelectNested()
.FirstOrDefault();
return(consentGroup);
}
public IList <ProductAttribute> GetByProductId(int productId, bool onlyVariantSpecific = false)
{
var q = Repository.Where(x => x.ProductId == productId)
.Join <AvailableAttribute>("AvailableAttributeId", "Id", joinType: JoinType.LeftOuter)
//.Join<Product>("ProductId", "Id")
.Join <ProductAttributeValue>("Id", "ProductAttributeId", SourceColumn.Parent,
joinType: JoinType.LeftOuter)
.Join <AvailableAttributeValue>("AvailableAttributeValueId", "Id", joinType: JoinType.LeftOuter);
//should we restrict to only variant specific attributes
if (onlyVariantSpecific)
{
var allowedInputTypes = VariantHelper.GetVariantSpecificFieldTypes();
Expression <Func <ProductAttribute, bool> > variantWhere =
attribute => allowedInputTypes.Contains(attribute.InputFieldType);
q.Where(variantWhere);
}
return(q
.Relate(RelationTypes.OneToMany <ProductAttribute, ProductAttributeValue>())
.Relate <AvailableAttributeValue>((productAttribute, availableAttributeValue) =>
{
if (productAttribute.Tag == null)
{
productAttribute.Tag = new List <AvailableAttributeValue>();
}
var attributeList = (List <AvailableAttributeValue>)productAttribute.Tag;
if (!attributeList.Contains(availableAttributeValue))
{
attributeList.Add(availableAttributeValue);
}
var pav = productAttribute.ProductAttributeValues.FirstOrDefault(
x => x.AvailableAttributeValueId == availableAttributeValue.Id);
if (pav != null)
{
pav.AvailableAttributeValue = availableAttributeValue;
}
})
.Relate <AvailableAttribute>((productAttribute, availableAttribute) =>
{
availableAttribute.AvailableAttributeValues = (List <AvailableAttributeValue>)productAttribute.Tag;
productAttribute.AvailableAttribute = availableAttribute;
})
.OrderBy(x => x.DisplayOrder)
.SelectNested()
.ToList());
}
public IEnumerable <AvailableAttribute> GetAvailableAttributes(out int totalResults, string searchText = null, int page = 1, int count = Int32.MaxValue)
{
var query = Repository;
if (!searchText.IsNullEmptyOrWhiteSpace())
{
query = query.Where(x => x.Name.Contains(searchText));
}
return(query.Join <AvailableAttributeValue>("Id", "AvailableAttributeId", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <AvailableAttribute, AvailableAttributeValue>())
.OrderBy(x => x.Name)
.SelectNestedWithTotalMatches(out totalResults, page, count));
}
public ProductVariant GetByAttributeValueIds(IList <int> productAttributeValueIds)
{
//let's create a custom query here
var providerSpecificSelector = DatabaseManager.IsSqlServerProvider() ? "top 1" : "";
var variantIdFetcher =
$"select {providerSpecificSelector} productvariantid from productvariantattribute where productattributevalueid in ({string.Join(",", productAttributeValueIds)}) group by productvariantid HAVING COUNT(*) = {productAttributeValueIds.Count} order by count(*) desc";
string sql = "";
if (DatabaseManager.IsSqlServerProvider())
{
sql =
$"SELECT t2.[ProductVariantId] AS ProductVariantAttribute_ProductVariantId,t2.[ProductAttributeId] AS ProductVariantAttribute_ProductAttributeId,t2.[ProductAttributeValueId] AS ProductVariantAttribute_ProductAttributeValueId,t2.[Id] AS ProductVariantAttribute_Id,t3.[ProductId] AS ProductAttribute_ProductId,t3.[AvailableAttributeId] AS ProductAttribute_AvailableAttributeId,t3.[InputFieldType] AS ProductAttribute_InputFieldType,t3.[Label] AS ProductAttribute_Label,t3.[DisplayOrder] AS ProductAttribute_DisplayOrder,t3.[IsRequired] AS ProductAttribute_IsRequired,t3.[Id] AS ProductAttribute_Id,t4.[ProductAttributeId] AS ProductAttributeValue_ProductAttributeId,t4.[AvailableAttributeValueId] AS ProductAttributeValue_AvailableAttributeValueId,t4.[Label] AS ProductAttributeValue_Label,t4.[Id] AS ProductAttributeValue_Id,t5.[ProductId] AS WarehouseInventory_ProductId,t5.[ProductVariantId] AS WarehouseInventory_ProductVariantId,t5.[WarehouseId] AS WarehouseInventory_WarehouseId,t5.[TotalQuantity] AS WarehouseInventory_TotalQuantity,t5.[ReservedQuantity] AS WarehouseInventory_ReservedQuantity,t5.[Id] AS WarehouseInventory_Id,t1.[ProductId] AS ProductVariant_ProductId,t1.[Sku] AS ProductVariant_Sku,t1.[Gtin] AS ProductVariant_Gtin,t1.[Mpn] AS ProductVariant_Mpn,t1.[Price] AS ProductVariant_Price,t1.[ComparePrice] AS ProductVariant_ComparePrice,t1.[TrackInventory] AS ProductVariant_TrackInventory,t1.[CanOrderWhenOutOfStock] AS ProductVariant_CanOrderWhenOutOfStock,t1.[MediaId] AS ProductVariant_MediaId,t1.[Id] AS ProductVariant_Id FROM (SELECT [ProductId],[Sku],[Gtin],[Mpn],[Price],[ComparePrice],[TrackInventory],[CanOrderWhenOutOfStock],[MediaId],[Id] FROM [ProductVariant] t1) AS t1 LEFT OUTER JOIN [ProductVariantAttribute] t2 ON t1.[Id] = t2.[ProductVariantId] LEFT OUTER JOIN [ProductAttribute] t3 ON t2.[ProductAttributeId] = t3.[Id] LEFT OUTER JOIN [ProductAttributeValue] t4 ON t3.[Id] = t4.[ProductAttributeId] LEFT OUTER JOIN [WarehouseInventory] t5 ON t1.[Id] = t5.[ProductVariantId] WHERE t1.[Id] IN ({variantIdFetcher})";
}
else
{
sql =
$"SELECT t2.`ProductVariantId` AS ProductVariantAttribute_ProductVariantId,t2.`ProductAttributeId` AS ProductVariantAttribute_ProductAttributeId,t2.`ProductAttributeValueId` AS ProductVariantAttribute_ProductAttributeValueId,t2.`Id` AS ProductVariantAttribute_Id,t3.`ProductId` AS ProductAttribute_ProductId,t3.`AvailableAttributeId` AS ProductAttribute_AvailableAttributeId,t3.`InputFieldType` AS ProductAttribute_InputFieldType,t3.`Label` AS ProductAttribute_Label,t3.`DisplayOrder` AS ProductAttribute_DisplayOrder,t3.`IsRequired` AS ProductAttribute_IsRequired,t3.`Id` AS ProductAttribute_Id,t4.`ProductAttributeId` AS ProductAttributeValue_ProductAttributeId,t4.`AvailableAttributeValueId` AS ProductAttributeValue_AvailableAttributeValueId,t4.`Label` AS ProductAttributeValue_Label,t4.`Id` AS ProductAttributeValue_Id,t5.`ProductId` AS WarehouseInventory_ProductId,t5.`ProductVariantId` AS WarehouseInventory_ProductVariantId,t5.`WarehouseId` AS WarehouseInventory_WarehouseId,t5.`TotalQuantity` AS WarehouseInventory_TotalQuantity,t5.`ReservedQuantity` AS WarehouseInventory_ReservedQuantity,t5.`Id` AS WarehouseInventory_Id,t1.`ProductId` AS ProductVariant_ProductId,t1.`Sku` AS ProductVariant_Sku,t1.`Gtin` AS ProductVariant_Gtin,t1.`Mpn` AS ProductVariant_Mpn,t1.`Price` AS ProductVariant_Price,t1.`ComparePrice` AS ProductVariant_ComparePrice,t1.`TrackInventory` AS ProductVariant_TrackInventory,t1.`CanOrderWhenOutOfStock` AS ProductVariant_CanOrderWhenOutOfStock,t1.`MediaId` AS ProductVariant_MediaId,t1.`Id` AS ProductVariant_Id FROM (SELECT `ProductId`,`Sku`,`Gtin`,`Mpn`,`Price`,`ComparePrice`,`TrackInventory`,`CanOrderWhenOutOfStock`,`MediaId`,`Id` FROM `ProductVariant` t1) AS t1 LEFT OUTER JOIN `ProductVariantAttribute` t2 ON t1.`Id` = t2.`ProductVariantId` LEFT OUTER JOIN `ProductAttribute` t3 ON t2.`ProductAttributeId` = t3.`Id` LEFT OUTER JOIN `ProductAttributeValue` t4 ON t3.`Id` = t4.`ProductAttributeId` LEFT OUTER JOIN `WarehouseInventory` t5 ON t1.`Id` = t5.`ProductVariantId` WHERE t1.`Id` IN ({variantIdFetcher})";
}
var variant = Repository
.Join <ProductVariantAttribute>("Id", "ProductVariantId", joinType: JoinType.LeftOuter)
.Join <ProductAttribute>("ProductAttributeId", "Id", joinType: JoinType.LeftOuter)
.Join <ProductAttributeValue>("Id", "ProductAttributeId", joinType: JoinType.LeftOuter)
.Join <WarehouseInventory>("Id", "ProductVariantId", SourceColumn.Parent, joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <ProductVariant, ProductVariantAttribute>())
.Relate <ProductAttribute>((productVariant, attribute) =>
{
var pva = productVariant.ProductVariantAttributes.FirstOrDefault(x => x.ProductAttributeId == attribute.Id);
if (pva != null)
{
pva.ProductAttribute = attribute;
}
})
.Relate <ProductAttributeValue>((productVariant, value) =>
{
var pva = productVariant.ProductVariantAttributes.FirstOrDefault(x => x.ProductAttributeValueId == value.Id);
if (pva != null)
{
pva.ProductAttributeValue = value;
}
})
.Relate(RelationTypes.OneToMany <ProductVariant, WarehouseInventory>())
.QueryNested(sql)
.FirstOrDefault();
return(variant);
}
public IList <Shipment> GetShipmentsByOrderId(int orderId)
{
Expression <Func <OrderItem, bool> > whereOrderIdMatches = item => item.OrderId == orderId;
return(Repository.Join <ShipmentItem>("Id", "ShipmentId", joinType: JoinType.LeftOuter)
.Join <OrderItem>("OrderItemId", "Id", joinType: JoinType.LeftOuter)
.Join <Product>("ProductId", "Id", joinType: JoinType.LeftOuter)
.Join <ShipmentHistory>("Id", "ShipmentId", SourceColumn.Parent, JoinType.LeftOuter)
.Join <Warehouse>("WarehouseId", "Id", SourceColumn.Parent, JoinType.LeftOuter)
.Join <Address>("AddressId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <Shipment, ShipmentItem>())
.Relate(RelationTypes.OneToMany <Shipment, ShipmentHistory>())
.Relate <OrderItem>((shipment, item) =>
{
if (shipment.ShipmentItems == null)
{
return;
}
var shipmentItem = shipment.ShipmentItems.FirstOrDefault(x => x.OrderItemId == item.Id);
if (shipmentItem != null)
{
shipmentItem.OrderItem = item;
}
})
.Relate <Product>((shipment, product) =>
{
if (shipment.ShipmentItems == null)
{
return;
}
var shipmentItem =
shipment.ShipmentItems.FirstOrDefault(
x => x.OrderItem != null && x.OrderItem.ProductId == product.Id);
if (shipmentItem != null)
{
shipmentItem.OrderItem.Product = product;
}
})
.Relate(RelationTypes.OneToOne <Shipment, Warehouse>())
.Relate <Address>((shipment, address) => { shipment.Warehouse.Address = address; })
.Where(whereOrderIdMatches)
.SelectNested()
.ToList());
}
public IList <ProductSpecification> GetByProductId(int productId, int?groupId = null)
{
var query = Repository
.Join <ProductSpecificationGroup>("ProductSpecificationGroupId", "Id", joinType: JoinType.LeftOuter)
.Join <ProductSpecificationValue>("Id", "ProductSpecificationId", SourceColumn.Parent,
JoinType.LeftOuter)
.Join <AvailableAttributeValue>("AvailableAttributeValueId", "Id", joinType: JoinType.LeftOuter)
.Join <AvailableAttribute>("AvailableAttributeId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToOne <ProductSpecification, ProductSpecificationGroup>())
.Relate(RelationTypes.OneToMany <ProductSpecification, ProductSpecificationValue>())
.Relate <AvailableAttributeValue>((specification, value) =>
{
if (specification.Tag == null)
{
specification.Tag = new List <AvailableAttributeValue>();
}
var attributeList = (List <AvailableAttributeValue>)specification.Tag;
if (!attributeList.Contains(value))
{
attributeList.Add(value);
}
var pav = specification.ProductSpecificationValues.FirstOrDefault(
x => x.AvailableAttributeValueId == value.Id);
if (pav != null)
{
pav.AvailableAttributeValue = value;
}
})
.Relate <AvailableAttribute>((specification, availableAttribute) =>
{
availableAttribute.AvailableAttributeValues = (List <AvailableAttributeValue>)specification.Tag;
specification.AvailableAttribute = availableAttribute;
})
.Where(x => x.ProductId == productId)
.OrderBy(x => x.DisplayOrder);
if (groupId.HasValue)
{
query = query.Where(x => x.ProductSpecificationGroupId == groupId);
}
return(query.SelectNested().ToList());
}
public IList <User> GetUsers(string searchText, int[] restrictToRoles, Expression <Func <User, object> > orderBy, SortOrder sortOrder, int page, int count, out int totalMatches, bool negateRoleRestriction = false, Expression <Func <User, bool> > where = null)
{
var query = Repository
.Where(x => !x.Deleted)
.Join <UserRole>("Id", "UserId", joinType: JoinType.LeftOuter)
.Join <Role>("RoleId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <User, Role>());
if (!searchText.IsNullEmptyOrWhiteSpace())
{
searchText = searchText.Trim();
query = query.Where(x => x.FirstName.Contains(searchText) ||
x.LastName.Contains(searchText) ||
x.LastLoginIpAddress.Contains(searchText) ||
x.Email.Contains(searchText));
}
if (restrictToRoles != null && restrictToRoles.Any())
{
var roleIds = restrictToRoles.ToList();
Expression <Func <Role, bool> > roleWhere;
if (negateRoleRestriction)
{
roleWhere = role => !roleIds.Contains(role.Id);
}
else
{
roleWhere = role => roleIds.Contains(role.Id);
}
query = query.Where(roleWhere);
}
if (orderBy == null)
{
orderBy = x => x.Name;
}
if (where != null)
{
query = query.Where(where);
}
query = query.OrderBy(orderBy, sortOrder == SortOrder.Ascending ? RowOrder.Ascending : RowOrder.Descending);
return(query.SelectNestedWithTotalMatches(out totalMatches, page, count).ToList());
}
private IEntitySet <Menu> GetByWhere(Expression <Func <Menu, bool> > @where)
{
Expression <Func <MenuItem, object> > orderBy = x => x.DisplayOrder;
return(_eventPublisherService.Filter(Repository
.Join <MenuItem>("Id", "MenuId", joinType: JoinType.LeftOuter)
.Join <SeoMeta>("SeoMetaId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <Menu, MenuItem>())
.Relate <SeoMeta>((menu, meta) =>
{
var menuItems = menu.MenuItems.Where(x => x.SeoMetaId == meta.Id);
foreach (var menuItem in menuItems)
{
menuItem.SeoMeta = meta;
}
})
.Where(where)
.OrderBy(orderBy, RowOrder.Ascending)));
}
public IEntitySet <T> Filter(IEntitySet <T> entity)
{
var entityName = typeof(T).Name;
Expression <Func <EntityStore, bool> > storeWhere = x => true;
if (!_alwaysAllTypes.Contains(typeof(T)))
{
//restrict to current store
var storeId = ApplicationEngine.CurrentStore?.Id ?? 0;
var allowedStoreIds = new List <int> {
storeId, 0
};
storeWhere = store => store.StoreId == null || allowedStoreIds.Contains(store.StoreId);
}
entity.Join <EntityStore>("Id", "EntityId", SourceColumn.Parent, JoinType.LeftOuter, (storeEntity, store) =>
store.EntityName == entityName)
.Join <Store>("StoreId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <T, Store>())
.Where(storeWhere);
return(entity);
}
private User GetByWhere(Expression <Func <User, bool> > where)
{
var query = Repository.Where(where)
.Join <UserRole>("Id", "UserId", joinType: JoinType.LeftOuter)
.Join <Role>("RoleId", "Id", joinType: JoinType.LeftOuter)
.Join <RoleCapability>("Id", "RoleId", joinType: JoinType.LeftOuter)
.Join <Capability>("CapabilityId", "Id", joinType: JoinType.LeftOuter)
.Join <UserCapability>("Id", "UserId", SourceColumn.Parent, JoinType.LeftOuter)
.Join <Capability>("CapabilityId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <User, Role>())
.Relate <Capability>((user, capability) =>
{
user.Capabilities = user.Capabilities ?? new List <Capability>();
if (user.Capabilities.All(x => x.Id != capability.Id))
{
user.Capabilities.Add(capability);
}
});
var userObject = query.SelectNested().FirstOrDefault();
return(userObject);
}
private IEntitySet <ProductVariant> GetQuery(Expression <Func <ProductVariant, bool> > where)
{
return(Repository.Where(where)
.Join <ProductVariantAttribute>("Id", "ProductVariantId", joinType: JoinType.LeftOuter)
.Join <ProductAttributeValue>("ProductAttributeValueId", "Id", joinType: JoinType.LeftOuter)
.Join <ProductAttribute>("ProductAttributeId", "Id", joinType: JoinType.LeftOuter)
.Join <AvailableAttribute>("AvailableAttributeId", "Id", joinType: JoinType.LeftOuter)
.Join <AvailableAttributeValue>("Id", "AvailableAttributeId", joinType: JoinType.LeftOuter)
.Join <WarehouseInventory>("Id", "ProductVariantId", SourceColumn.Parent, joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <ProductVariant, ProductVariantAttribute>())
.Relate <ProductAttributeValue>((variant, value) =>
{
var productVariantAttribute =
variant.ProductVariantAttributes.FirstOrDefault(x => x.ProductAttributeValueId == value.Id);
if (productVariantAttribute != null)
{
productVariantAttribute.ProductAttributeValue = value;
}
})
.Relate <ProductAttribute>((variant, attribute) =>
{
var productVariantAttribute =
variant.ProductVariantAttributes.FirstOrDefault(x => x.ProductAttributeId == attribute.Id);
if (productVariantAttribute != null && productVariantAttribute.ProductAttribute == null)
{
productVariantAttribute.ProductAttribute = attribute;
}
})
.Relate <AvailableAttribute>((variant, attribute) =>
{
var productVariantAttribute =
variant.ProductVariantAttributes.FirstOrDefault(
x => x.ProductAttribute.AvailableAttribute == null &&
x.ProductAttribute.AvailableAttributeId == attribute.Id);
if (productVariantAttribute != null)
{
productVariantAttribute.ProductAttribute.AvailableAttribute = attribute;
if (productVariantAttribute.ProductAttribute.Label.IsNullEmptyOrWhiteSpace())
{
productVariantAttribute.ProductAttribute.Label = attribute.Name;
}
}
})
.Relate <AvailableAttributeValue>((variant, attributeValue) =>
{
var pav = variant.ProductVariantAttributes.Select(x => x.ProductAttributeValue)
.FirstOrDefault(x => x.AvailableAttributeValueId == attributeValue.Id);
if (pav != null)
{
if (pav.AvailableAttributeValue == null)
{
pav.AvailableAttributeValue = attributeValue;
}
if (pav.Label.IsNullEmptyOrWhiteSpace())
{
pav.Label = attributeValue?.Value;
}
}
})
.Relate(RelationTypes.OneToMany <ProductVariant, WarehouseInventory>()));
}
private IEntitySet <DiscountCoupon> GetByWhere(Expression <Func <DiscountCoupon, bool> > where)
{
return(Repository.Where(where)
.Join <RestrictionValue>("Id", "DiscountCouponId", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <DiscountCoupon, RestrictionValue>()));
}
public IEnumerable <Order> GetOrders(out int totalResults, string productName = null, int?userId = null, int?storeId = null, IList <int> orderIds = null, IList <int> productIds = null, IList <OrderStatus> orderStatus = null, IList <PaymentStatus> paymentStatus = null, IList <int> vendorIds = null, DateTime?startDate = null, DateTime?endDate = null, int page = 1, int count = int.MaxValue)
{
var query = Repository;
//check one by one for each filter
if (userId.HasValue)
{
query = query.Where(x => x.UserId == userId);
}
if (storeId.HasValue)
{
query = query.Where(x => x.StoreId == storeId);
}
if (orderIds != null && orderIds.Any())
{
query = query.Where(x => orderIds.Contains(x.Id));
}
//date filters
if (startDate.HasValue)
{
query = query.Where(x => x.CreatedOn >= startDate);
}
if (endDate.HasValue)
{
query = query.Where(x => x.CreatedOn <= endDate);
}
query = query.Join <OrderItem>("Id", "OrderId", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <Order, OrderItem>());
query = query.Join <Product>("ProductId", "Id", joinType: JoinType.LeftOuter)
.Relate <Product>((order, product) =>
{
foreach (var orderItem in order.OrderItems)
{
if (orderItem.ProductId == product.Id)
{
orderItem.Product = product;
}
}
});
if (productIds?.Any() ?? false)
{
Expression <Func <OrderItem, bool> > whereProductIdMatches = (orderItem) => productIds.Contains(orderItem.ProductId);
query = query.Where(whereProductIdMatches);
}
if (!productName.IsNullEmptyOrWhiteSpace() || (vendorIds?.Any() ?? false))
{
if (vendorIds?.Any() ?? false)
{
Expression <Func <ProductVendor, bool> > whereVendorIdMatches = (vendorItem) => vendorIds.Contains(vendorItem.VendorId);
query = query.Join <ProductVendor>("Id", "ProductId", joinType: JoinType.LeftOuter)
.Where(whereVendorIdMatches);
}
if (!productName.IsNullEmptyOrWhiteSpace())
{
Expression <Func <Product, bool> > whereProductName = product => product.Name.Contains(productName);
query = query.Where(whereProductName);
}
}
if (orderStatus != null && orderStatus.Any())
{
query = query.Where(x => orderStatus.Contains(x.OrderStatus));
}
if (paymentStatus != null && paymentStatus.Any())
{
query = query.Where(x => paymentStatus.Contains(x.PaymentStatus));
}
query = query.Join <User>("UserId", "Id", SourceColumn.Parent, JoinType.LeftOuter).Relate(RelationTypes.OneToOne <Order, User>());
; query = query.OrderBy(x => x.CreatedOn, RowOrder.Descending);
query.SkipColumns <Order>(nameof(Order.BillingAddressSerialized), nameof(Order.ShippingAddressSerialized), nameof(Order.Remarks));
query.SkipColumns <User>(nameof(User.Password), nameof(User.PasswordSalt), nameof(User.PasswordFormat));
var orders = query.SelectNestedWithTotalMatches(out totalResults, page, count);
return(orders);
}
public override Shipment Get(int id)
{
Expression <Func <SeoMeta, bool> > where = meta => meta.EntityName == nameof(Product);
return(Repository.Where(x => x.Id == id)
.Join <ShipmentItem>("Id", "ShipmentId", joinType: JoinType.LeftOuter)
.Join <OrderItem>("OrderItemId", "Id", joinType: JoinType.LeftOuter)
.Join <Order>("OrderId", "Id", joinType: JoinType.LeftOuter)
.Join <ShipmentHistory>("Id", "ShipmentId", SourceColumn.Parent, JoinType.LeftOuter)
.Join <User>("UserId", "Id", typeof(Order), JoinType.LeftOuter)
.Join <Warehouse>("WarehouseId", "Id", SourceColumn.Parent, JoinType.LeftOuter)
.Join <Address>("AddressId", "Id", joinType: JoinType.LeftOuter)
.Join <Product>("ProductId", "Id", typeof(OrderItem), JoinType.LeftOuter)
.Join <SeoMeta>("Id", "EntityId", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <Shipment, ShipmentItem>())
.Relate(RelationTypes.OneToMany <Shipment, ShipmentHistory>())
.Relate <OrderItem>((shipment, item) =>
{
var shipmentItem = shipment.ShipmentItems.FirstOrDefault(x => x.OrderItemId == item.Id);
if (shipmentItem != null)
{
shipmentItem.OrderItem = item;
}
})
.Relate <Order>((shipment, order) =>
{
var shipmentItem = shipment.ShipmentItems.FirstOrDefault(x => x.OrderItem.OrderId == order.Id);
if (shipmentItem == null)
{
return;
}
shipmentItem.OrderItem.Order = order;
order.OrderItems = order.OrderItems ?? new List <OrderItem>();
if (!order.OrderItems.Contains(shipmentItem.OrderItem))
{
order.OrderItems.Add(shipmentItem.OrderItem);
}
order.Shipments = order.Shipments ?? new List <Shipment>();
if (!order.Shipments.Contains(shipment))
{
order.Shipments.Add(shipment);
}
})
.Relate <User>((shipment, user) =>
{
shipment.User = user;
})
.Relate(RelationTypes.OneToOne <Shipment, Warehouse>())
.Relate <Address>((shipment, address) => { shipment.Warehouse.Address = address; })
.Relate <Product>((shipment, product) =>
{
foreach (var shipmentItem in shipment.ShipmentItems)
{
if (shipmentItem.OrderItem.ProductId == product.Id)
{
shipmentItem.OrderItem.Product = product;
}
}
})
.Relate <SeoMeta>((shipment, meta) =>
{
foreach (var shipmentItem in shipment.ShipmentItems)
{
if (shipmentItem.OrderItem.ProductId == meta.EntityId)
{
shipmentItem.OrderItem.Product.SeoMeta = meta;
}
}
})
.SelectNested()
.FirstOrDefault());
}
private Cart GetCart(int userId, bool isWishlist)
{
Expression <Func <SeoMeta, bool> > seoMetaWhere = meta => meta.EntityName == "Product";
Expression <Func <Product, bool> > productWhere = product => product.Published;
Expression <Func <Address, bool> > addressWhere = address => address.EntityName == null || address.EntityName == nameof(User);
var userCart = Repository.Where(x => x.UserId == userId && x.IsWishlist == isWishlist)
.Join <CartItem>("Id", "CartId", joinType: JoinType.LeftOuter)
.Join <Product>("ProductId", "Id", joinType: JoinType.LeftOuter)
.Join <SeoMeta>("Id", "EntityId", joinType: JoinType.LeftOuter)
.Join <ProductMedia>("EntityId", "ProductId", joinType: JoinType.LeftOuter)
.Join <Media>("MediaId", "Id", joinType: JoinType.LeftOuter)
.Join <DiscountCoupon>("DiscountCouponId", "Id", SourceColumn.Parent, JoinType.LeftOuter)
.Join <RestrictionValue>("Id", "DiscountCouponId", joinType: JoinType.LeftOuter)
.Join <User>("UserId", "Id", SourceColumn.Parent, JoinType.LeftOuter)
.Join <UserRole>("Id", "UserId", joinType: JoinType.LeftOuter)
.Join <Role>("RoleId", "Id", joinType: JoinType.LeftOuter)
.Join <Address>("BillingAddressId", "Id", SourceColumn.Parent, JoinType.LeftOuter)
.Join <Country>("CountryId", "Id", joinType: JoinType.LeftOuter)
.Join <Address>("ShippingAddressId", "Id", SourceColumn.Parent, JoinType.LeftOuter)
.Join <Country>("CountryId", "Id", joinType: JoinType.LeftOuter)
.Relate(RelationTypes.OneToMany <Cart, CartItem>())
.Relate <Product>((cart, product) =>
{
var cartItem = cart.CartItems.FirstOrDefault(x => x.ProductId == product.Id && x.Product == null);
if (cartItem != null)
{
cartItem.Product = product;
}
})
.Relate <SeoMeta>((cart, meta) =>
{
var cartItem = cart.CartItems.FirstOrDefault(x => x.ProductId == meta.EntityId);
if (cartItem != null)
{
cartItem.Product.SeoMeta = meta;
}
})
.Relate <ProductMedia>((cart, media) =>
{
var cartItem = cart.CartItems.FirstOrDefault(x => x.ProductId == media.ProductId);
if (cartItem?.Product != null)
{
//temporary storage for media ids
cartItem.Tag = cartItem.Tag ?? new List <int>();
cartItem.Product.MediaItems = cartItem.Product.MediaItems ?? new List <Media>();
(cartItem.Tag as List <int>).Add(media.MediaId);
}
})
.Relate <Media>((cart, media) =>
{
var cartItem =
cart.CartItems.FirstOrDefault(
x => x.Tag != null && (x.Tag as List <int>).Contains(media.Id));
if (cartItem != null)
{
if (cartItem.Product.MediaItems.All(x => x.Id != media.Id))
{
cartItem.Product.MediaItems.Add(media);
}
}
})
.Relate(RelationTypes.OneToOne <Cart, DiscountCoupon>())
.Relate <RestrictionValue>((cart, value) =>
{
cart.DiscountCoupon.RestrictionValues =
cart.DiscountCoupon.RestrictionValues ?? new List <RestrictionValue>();
cart.DiscountCoupon.RestrictionValues.Add(value);
})
.Relate(RelationTypes.OneToOne <Cart, User>())
.Relate <Role>((cart, role) =>
{
cart.User.Roles = cart.User.Roles ?? new List <Role>();
cart.User.Roles.Add(role);
})
.Relate <Address>((cart, address) =>
{
if (cart.BillingAddressId == address.Id)
{
cart.BillingAddress = address;
}
if (cart.ShippingAddressId == address.Id)
{
cart.ShippingAddress = address;
}
})
.Relate <Country>((cart, country) =>
{
if (cart.BillingAddress?.CountryId == country.Id)
{
cart.BillingAddress.Country = country;
}
if (cart.ShippingAddress != null && cart.ShippingAddress?.CountryId == country.Id)
{
cart.ShippingAddress.Country = country;
}
})
.Where(seoMetaWhere)
.Where(productWhere)
.Where(addressWhere)
.SelectNested()
.FirstOrDefault();
//do we have an empty cart
userCart = userCart ?? Repository.Where(x => x.UserId == userId && x.IsWishlist == isWishlist).SelectSingle() ?? new Cart();
if (userCart.Id == 0)
{
//save a new cart for user
userCart.UserId = userId;
userCart.IsWishlist = isWishlist;
Insert(userCart);
}
return(userCart);
}
private IEntitySet <Order> GetByWhere(Expression <Func <Order, bool> > where)
{
Expression <Func <SeoMeta, bool> > meteWhere = x => x.EntityName == "Product";
return(Repository.Where(where)
.Join <OrderItem>("Id", "OrderId", joinType: JoinType.LeftOuter)
.Join <ShipmentItem>("Id", "OrderItemId", joinType: JoinType.LeftOuter)
.Join <Shipment>("ShipmentId", "Id", joinType: JoinType.LeftOuter)
.Join <User>("UserId", "Id", SourceColumn.Parent, JoinType.LeftOuter)
.Join <Product>("ProductId", "Id", typeof(OrderItem))
.Join <ProductMedia>("Id", "ProductId", joinType: JoinType.LeftOuter)
.Join <Media>("MediaId", "Id", joinType: JoinType.LeftOuter)
.Join <SeoMeta>("Id", "EntityId", typeof(Product))
.Relate(RelationTypes.OneToMany <Order, OrderItem>())
.Relate(RelationTypes.OneToMany <Order, Shipment>())
.Relate(RelationTypes.OneToOne <Order, User>())
.Relate <ShipmentItem>((order, shipmentItem) =>
{
var orderItem = order.OrderItems.First(x => x.Id == shipmentItem.OrderItemId);
orderItem.Shipment = order.Shipments.First(x => x.Id == shipmentItem.ShipmentId);
orderItem.Shipment.ShipmentItems = orderItem.Shipment.ShipmentItems ?? new List <ShipmentItem>();
if (!orderItem.Shipment.ShipmentItems.Contains(shipmentItem))
{
orderItem.Shipment.ShipmentItems.Add(shipmentItem);
shipmentItem.OrderItem = orderItem;
}
})
.Relate <Product>((order, product) =>
{
foreach (var orderItem in order.OrderItems)
{
if (orderItem.ProductId == product.Id)
{
orderItem.Product = product;
}
}
})
.Relate <ProductMedia>((order, media) =>
{
foreach (var orderItem in order.OrderItems)
{
if (orderItem.ProductId == media.ProductId)
{
orderItem.Product.Tag = media.MediaId;
}
}
})
.Relate <Media>((order, media) =>
{
foreach (var orderItem in order.OrderItems)
{
if ((int)orderItem.Product.Tag == media.Id)
{
orderItem.Product.MediaItems = orderItem.Product.MediaItems ?? new List <Media>();
if (!orderItem.Product.MediaItems.Contains(media))
{
orderItem.Product.MediaItems.Add(media);
}
}
}
})
.Relate <SeoMeta>((order, meta) =>
{
var orderItem = order.OrderItems.FirstOrDefault(x => x.ProductId == meta.EntityId);
if (orderItem != null)
{
orderItem.Product.SeoMeta = meta;
}
})
.Where(meteWhere));
}