score:0

Accepted answer

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:-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

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


Related Query

More Query from same tag