score:0
i wrote a couple of classes to handle this optimization problem, however, it's not finished yet (i'm off from my PC now and will be back tmr - you may take a look at this first and see whether it's useful)...
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TestCSharp.Optimizer
{
public interface IUnit
{
int Id { get; set; }
float UnitPrice { get; set; }
}
public interface IOrder
{
int Quantity { get; set; }
float GetTotalPrice();
}
public interface IOrder<T> : IOrder where T : IUnit
{
T Unit { get; }
}
public abstract class AbstractUnit : IUnit
{
public int Id { get; set; }
public float UnitPrice { get; set; }
}
public class Item : AbstractUnit
{
public string Name { get; set; }
}
public class Package : AbstractUnit
{
private List<Order<Item>> _items = new List<Order<Item>>();
public IEnumerable<Order<Item>> Items
{
get { return this._items; }
}
}
public class Order<T> : IOrder<T> where T : IUnit
{
T _unit;
public Order(T unit)
{
_unit = unit;
}
public T Unit { get { return _unit; } }
public int Quantity { get; set; }
public float GetTotalPrice()
{
return _unit.UnitPrice * Quantity;
}
}
public class Combination
{
private List<IOrder> _orders = new List<IOrder>();
private Combination()
{
// Private constructor
}
public List<IOrder> Orders { get { return _orders; } }
public float GetTotalPrice()
{
if (_orders.Any())
{
return _orders.Select(m => m.GetTotalPrice()).Sum();
}
return 0;
}
public static Combination GetBestCombination(IEnumerable<Order<Item>> intendedItems, IEnumerable<Package> allPacksSetupFromDB)
{
var potentialCombinations = new List<Combination>();
// First comb: treat each item as a standalone IOrder:
var com = new Combination();
foreach (var ele in intendedItems)
{
com.Orders.Add(ele);
}
potentialCombinations.Add(com);
// check each possible pack (loaded from db) and find all possible combinations
var possiblePacks = allPacksSetupFromDB.Where(m => m.Items.All(n => (intendedItems.Any(t => t.Unit.Id == n.Unit.Id && t.Quantity >= n.Quantity)))).ToArray();
//ToDo: in the possible packages to use, find out all possible combinations
// This is the tricky part...
// The one with lowest price is desired!
return potentialCombinations.OrderBy(m => m.GetTotalPrice()).FirstOrDefault();
}
}
}
EDIT:
I thought you would have continued with this, anyway, i just came out an untested version:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TestCSharp.Optimizer
{
public interface IUnit
{
int Id { get; set; }
float UnitPrice { get; set; }
}
public interface IOrder
{
int Quantity { get; set; }
float GetTotalPrice();
}
public interface IOrder<T> : IOrder where T : IUnit
{
T Unit { get; }
}
public abstract class AbstractUnit : IUnit
{
public int Id { get; set; }
public float UnitPrice { get; set; }
}
public class Item : AbstractUnit
{
public string Name { get; set; }
}
public class Package : AbstractUnit
{
private List<Order<Item>> _items = new List<Order<Item>>();
// Items here are required by the package - number of items is specified by the quantity.
public IEnumerable<Order<Item>> Items
{
get { return this._items; }
}
}
public class Order<T> : IOrder<T> where T : IUnit
{
T _unit;
public Order(T unit)
{
_unit = unit;
}
public T Unit { get { return _unit; } }
public int Quantity { get; set; }
public float GetTotalPrice()
{
return _unit.UnitPrice * Quantity;
}
}
public class Combination
{
private List<IOrder> _orders = new List<IOrder>();
private Combination()
{
// Private constructor
}
public List<IOrder> Orders { get { return _orders; } }
/// <summary>
/// Get the total number of items ordered (combine package as well)
/// </summary>
/// <returns></returns>
public Dictionary<int, int> GetItemIdAndQuantity()
{
var output = new Dictionary<int, int>();
foreach (var order in this._orders)
{
if (order is Order<Item>)
{
var orderedItem = (Order<Item>)order;
if (!output.Keys.Contains(orderedItem.Unit.Id))
{
output.Add(orderedItem.Unit.Id, 0);
}
output[orderedItem.Unit.Id] = output[orderedItem.Unit.Id] + ((Order<Item>)order).Quantity;
}
else
{
var orderedPackage = (Order<Package>)order;
foreach (var item in orderedPackage.Unit.Items)
{
var itemId = item.Unit.Id;
if (!output.Keys.Contains(itemId))
{
output.Add(itemId, 0);
}
output[itemId] = output[itemId] + item.Quantity * orderedPackage.Quantity;
}
}
}
return output;
}
public float GetTotalPrice()
{
if (_orders.Any())
{
return _orders.Select(m => m.GetTotalPrice()).Sum();
}
return 0;
}
public static Combination GetBestCombination(IEnumerable<Order<Item>> intendedItems, IEnumerable<Package> allPacksSetupFromDB)
{
var potentialCombinations = new List<Combination>();
// check each possible pack (loaded from db) and find all possible combinations
// Step 1: for each package, if it could be used potentially, collect it into a list
var possibleRanges = new List<PackageUseRange>();
foreach (var p in allPacksSetupFromDB)
{
// for each required item in a package, intended items have to fulfill at least one occurrence (with given quantity)
if (p.Items.All(n => (intendedItems.Any(t => t.Unit.Id == n.Unit.Id && t.Quantity >= n.Quantity))))
{
var rng = new PackageUseRange(p);
rng.Min = 0;
// Find the possible max occurrence of the package
var matchedOrders = intendedItems.Where(x => p.Items.Any(m => m.Unit.Id == x.Unit.Id));
rng.Max = matchedOrders.Select(m => m.Quantity / p.Items.First(t => t.Unit.Id == m.Unit.Id).Quantity).Min();
possibleRanges.Add(rng);
}
}
// By now we should have something like:
// package 1: min 0, max 2
// package 2: min 0, max 1
// package 3: min 0, max 3
// ...
// Step 2: find all possible combinations:
if (possibleRanges.Any())
{
// define a collection to collect combinations
// also define a method to clear unwanted combinations.
var combinations = new List<Combination>();
Action invalidOrderRemover = delegate()
{
for (int j = combinations.Count - 1; j >= 0; j++)
{
var theCom = combinations[j];
var orderedQuantities = theCom.GetItemIdAndQuantity();
foreach (var itemId in orderedQuantities.Keys)
{
var intended = intendedItems.First(m => m.Unit.Id == itemId).Quantity;
if (orderedQuantities[itemId] > intended)
{
combinations.Remove(theCom);
break;
}
}
}
};
// For first package, let's create orders with different quantities
var firstPack = possibleRanges[0];
for (int i = firstPack.Min; i <= firstPack.Max; i++)
{
var order = new Order<Package>(firstPack.Package);
order.Quantity = i;
var com = new Combination();
com.Orders.Add(order);
combinations.Add(com);
}
// From second onwards:
// 1. we expand the orders created earlier and collect current pack (with different quantity)
// 2. we also take out those impossible combinations so far (total quantity exceeds wanted)
for (int i = 1; i < possibleRanges.Count - 1; i++)
{
invalidOrderRemover.Invoke(); // to avoid the list/array unreasonably large
// expand orders based on current pack's range:
var currPack = possibleRanges[i];
var expanded = new List<Combination>();
foreach (var oldCom in combinations)
{
for (int j = currPack.Min; j <= currPack.Max; j++)
{
// Clone from previous and pick up new ones from existing package.
var newCom = new Combination();
newCom.Orders.AddRange(oldCom.Orders);
var newOrder = new Order<Package>(currPack.Package);
newOrder.Quantity = j;
newCom.Orders.Add(newOrder);
}
}
// Clear old and add back the expanded:
combinations.Clear();
combinations.AddRange(expanded);
}
// Clear unwanted again:
invalidOrderRemover.Invoke();
// Add back balanced items:
foreach (var cb in combinations)
{
var fulfilled = cb.GetItemIdAndQuantity();
foreach (var item in intendedItems)
{
if (!fulfilled.Keys.Contains(item.Unit.Id))
{
// no such item in any package
// thus just add new Item based order
var newOrder = new Order<Item>(item.Unit);
newOrder.Quantity = item.Quantity;
cb.Orders.Add(newOrder);
}
else
{
// check balance:
if (fulfilled[item.Unit.Id] < item.Quantity)
{
var newOrder = new Order<Item>(item.Unit);
newOrder.Quantity = item.Quantity - fulfilled[item.Unit.Id];
cb.Orders.Add(newOrder);
}
}
}
}
// Add to the final combination collection
potentialCombinations.AddRange(combinations);
}
// Step 3: If there is no package used at all, treat each item as a standalone IOrder:
if (!potentialCombinations.Any())
{
var com = new Combination();
foreach (var ele in intendedItems)
{
com.Orders.Add(ele);
}
potentialCombinations.Add(com);
}
// The one with lowest price is desired!
return potentialCombinations.OrderBy(m => m.GetTotalPrice()).FirstOrDefault();
}
private class PackageUseRange
{
public PackageUseRange(Package p)
{
this.Package = p;
this.Min = 0;
this.Max = 0;
}
public Package Package { get; private set; }
public int Min { get; set; }
public int Max { get; set; }
}
}
}
The idea is quite simple: find all possible combinations and then get the one with lowest price.
To achieve that, you have to find all applicable packages, and then find the min (literally 0) and maximum possible.
From the min and max of each pack, find all combinations and then take out incorrect combinations (those exceeds the buying quantity).
hope it helps, though I'm not sure about the performance - it should be fast for non complex packages, but could be slow for complex situations. you may write some test cases for this...
score:0
public string[] getItems(string [] Items)
{
List<string> lstReturn = new List<string>();
using(DBContext context = new DBContext())
{
lstReturn = (from c in context.ItemPackageItems
where Items.Contains(c.Item)
select c.ItemPackage).Distinct().ToList();
var PackagesItems = (from c in context.ItemPackageItems
where lstReturn.Contains(c.ItemPackage)
select c.Item).Distinct();
foreach (string strItem in Items)
{
if (!PackagesItems.Contains(strItem))
lstReturn.Add(strItem);
}
}
return lstReturn.ToArray();
}
Here your result order my be different, first are Packages and the single items
score:-1
In sql just cuse i wanted to practice btw it works with 3 but if u have more then 3 items its a lot more complicated
declare @ck1 varchar (20),
@ck2 varchar (20),
@ck3 varchar (20),
@out varchar (100),
@flag bit,
@Itemck int
--if 1 then only @ck1 use, if 2 it used @ck1+@ck2 if 3 @ck1+@ck3 if 4 then @ck1+@ck2+@ck3
--usage manu
--1 @ck1
--2 @ck1+@ck2
--3 @ck1+@ck3
--4 @ck1+@ck2+@ck3
--5 @ck2+@ck3
set @ck1='Item1'
set @ck2='Item2'
set @ck3='Item2'
if @ck1='Item1'
BEGIN
if @ck2 = 'Item2' and @ck3='Item2'
begin
set @out='ItemPackge 3'
set @flag=1
set @Itemck=4
end
else
begin
if @ck2 = 'Item2'
begin
set @out='ItemPackge 1'
set @flag=1
set @Itemck=2
end
else if @ck3='Item2'
begin
set @out='ItemPackge 1'
set @flag=1
set @Itemck=3
end
else
begin
set @out='Item1'
set @flag=1
set @Itemck=1
end
end
END
else
if @ck1='Item2'
BEGIN
set @out='Item2'
set @flag=1
END
if @ck1='Item3'
BEGIN
if @ck2 = 'Item4' or @ck3='Item4'
if @flag=0
set @out='ItemPackge 2'
else
set @out=@out +' + ItemPackge 2'
else
if @flag=0
set @out='Item3'
else set @out=@out +' + Item 3'
set @flag=1
END
else
if @ck1='Item4'
BEGIN
if @flag=0 set @out='Item4'
else set @out=@out +' + Item 4'
set @flag=1
END
if @Itemck=0 or @Itemck=1 or @Itemck=3
BEGIN
if @ck2 ='Item1'
BEGIN
if @ck3='Item2'
BEGIN
if @flag=0
set @out='ItemPackge 2'
else
set @out=@out +' + ItemPackge 2'
set @Itemck=5
END
Else
BEGIN
if @flag=0
set @out='Item1'
else
set @out=@out +' + Item1'
END
end
else if @ck2='Item2'
BEGIN
if @flag=0
set @out='Item2'
else
set @out=@out +' + Item2'
END
if @ck2 ='Item3'
BEGIN
if @ck3='Item4'
BEGIN
if @flag=0
set @out='ItemPackge 2'
else
set @out=@out +' + ItemPackge 2'
set @Itemck=5
END
else
BEGIN
if @flag=0
set @out='Item3'
else
set @out=@out +' + Item3'
END
END
ELSE if @ck2='Item4'
BEGIN
if @flag=0
set @out='Item4'
else
set @out=@out +' + Item4'
END
END
if @Itemck =1 or @Itemck =2
BEGIN
if @flag=0
set @out=@ck3
else
set @out=@out +' + ' + @ck3
END
print @out
Source: stackoverflow.com
Related Articles
- SQL query for product combination
- Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet'
- LINQ query to perform a projection, skipping or wrapping exceptions where source throws on IEnumerable.GetNext()
- Is there any way to create a LINQ query as a variable without having the data source (yet)?
- LINQ Source Code Available
- prevent unnecessary cross joins in count query of generated sql code
- .NET 4 Code Contracts: "requires unproven: source != null"
- EF Code First comparing null values generates strange query
- linq - how do you do a query for items in one query source that are not in another one?
- What is the LINQ query to get a Cartesian Product even when one set is EMPTY?
- How can I write the following code more elegantly using LINQ query syntax?
- How to dynamic add filters to a LINQ query against an Odata Source in C#
- Avoid extra loop and could not find implementation of query pattern for source type int Select not found
- C# Linq query help removing foreach loops creating cleaner code
- Use a linq query as microsoft local report Data Source (WinForms)
- Can I use a compiled query as a source in a second query?
- Determine the source DataContext for a Linq to Sql query
- Query expressions over source type 'dynamic' or with a join sequence of type 'dynamic' are not allowed
- LINQ query returns old results when source list is re-initialized
- How to query by where clause with EF code first
- How to get SQL query into LINQ form in C# code
- Code Example for Add DateTime and TimeSpan in EF query
- Could not find an implementation of the query pattern for source type
- How can I code a Linq query to do an upward Include?
- creating Linq to sqlite dbml from DbLinq source code
- Exclusive use entity from a Product of Query
- What would be a reasonably fast way to code this sql query in c#?
- Identify source of linq to sql query
- NHibernate LINQ query performance, which code fragment is better?
- Linq sub query when using a repository pattern with EF code first
- Case statement in Linq for a nullable datetime field
- Is it possible to group LINQ elements based on a sequence?
- How do I OrderBy and ThenBy in LINQ based on user input?
- SQL Server Express / MS Access LINQ Features, Which One to Use?
- LINQ: Sum from M-M table
- Getting attributes from xml using Linq
- How to Join with a column having Comma Separated Values in Linq
- Observing incoming websocket messages with Reactive Extensions?
- Run LINQ query with date range check in C# with correct date format
- Bind multiple dropdowns from a single list
- Select data from two tables with Linq/Lambda
- The LINQ expression node type 'Invoke' is not supported in LINQ to Entities with skip
- LINQ to SQL *compiled* queries and when they execute
- How to create an asynchronous repository with EF Core?
- Best way to compare date in Entity Framework
- How do I perform a DISTINCT query using LINQ (.NET 4)?
- linq compare with previous item
- Handling null array in a linq query
- Linq to Entity Attach method issue
- Pivot column entries into a comma separated list in a single row for distinct adjacent column?