//#define ArrowWMS_2_1_x

#if !ArrowWMS_2_1_x

#define STACKABLE_ARTICLES

#define USE_TEMP_OND_TABLE // OrderNeedsDestination

#define CUBING_STRATEGY

#endif

#if !UNITY_WEBGL

#define SQL_ENABLED

#endif

#define USE_MAXWEIGHT

#define ALLOW_OVERRIDE

​

using System.Collections.Generic;

using System.Data;

#if SQL_ENABLED

using System.Data.SqlClient;

#endif

using System.Data.SqlTypes;

#if SQL_ENABLED

using Microsoft.SqlServer.Server;

#endif

using System;

public class Program

{

    // ----------------------------------------------------------------

​

  // ----------------------------------------------------------------

​

    // FIXME: simplify this now we are using temp table #tempOrderNeedsDestination

    private const string sqlSelectOrderNeedsDestination =

  "select ond.ArticleID,ond.Size1,ond.Size2,ond.Size3,ond.MeasuredArticleQuantity,ond.PlannedQuantity" +

#if CUBING_STRATEGY

    ",(a.AllowXYRotation & isnull(cs.AllowXYRotation,1)) as AllowXYRotation" +

      ",(a.AllowZRotation & isnull(cs.AllowZRotation,1)) as AllowZRotation" +

#else

    ",a.AllowXYRotation,a.AllowZRotation" + 

#endif

    ",ond.OrderID,ond.PutID" +

#if USE_MAXWEIGHT

    ",ond.ArticleWeight" +

#endif

#if STACKABLE_ARTICLES

    ",a.MaxStack,a.StackDeltaX,a.StackDeltaY,a.StackDeltaZ" +

#endif

    @",ArticlePriority,AssignmentVolume

from   " + vw_OrderNeedsDestination + @" ond

join   core.Article a on a.ArticleID = ond.MeasuredArticleID"

#if CUBING_STRATEGY

+ @"

left join core.Assignment o on o.AssignmentID = ond.PutID

left join core.CubingStrategy cs on cs.CubingStrategyID = o.CubingStrategyID"

#endif

;

​

    private const string sqlContainerTypeAndAreaGroupID =

     "(@pContainerType is null or ond.ContainerType = @pContainerType)"

#if ArrowWMS_2_1_x

    + @"

  and  (ond.Area = @pArea or @pArea is null)";

#else

    + @"

  and  (@pAreaGroupID is null

        or

        ond.Area in

        (

          select ar.ChildArea

          from   core.AreaAreaGroup aag

            join core.vw_AreaRec ar

              on aag.Area = ar.ParentArea

          where  aag.AreaGroupID = @pAreaGroupID

        )

       )";

#endif

​

#if !USE_TEMP_OND_TABLE

    private const string sqlWhereOrder = @"

where  OrderID = @pStartOrderID

  and  " + sqlContainerTypeAndAreaGroupID;

​

#if ArrowWMS_2_1_x

    private const string sqlDestinationRouteStopID = "RouteID";

#else

    private const string sqlDestinationRouteStopID = "DestinationRouteStopID";

#endif

    private const string sqlWhereRoute = @"

where  @pPackByRouteStop = 1

  and  ond." + sqlDestinationRouteStopID + @" is not null

  and  OrderID != @pStartOrderID

  and  ond." + sqlDestinationRouteStopID + @" in (select " + sqlDestinationRouteStopID + @" from core.Assignment where AssignmentID = @pStartOrderID)

  and  " + sqlContainerTypeAndAreaGroupID;

#endif

​

    // --------------------------------

​

#if !USE_TEMP_OND_TABLE

    private const string sqlQueryOrderItems = sqlSelectOrderNeedsDestination + sqlWhereOrder + @"

union

" + sqlSelectOrderNeedsDestination + sqlWhereRoute + @"

order by ArticlePriority desc, AssignmentVolume desc

option (force order)";

#else

    private const string sqlQueryOrderItems = sqlSelectOrderNeedsDestination + @"

where  " + sqlContainerTypeAndAreaGroupID + @"

order by ArticlePriority desc, AssignmentVolume desc";

#endif

​

#if false

    private static SqlCommand CreateCommandOrderItems()

    {

        var cmd = new SqlCommand(sqlQueryOrderItems);

#if !USE_TEMP_OND_TABLE

        cmd.Parameters.Add("@pStartOrderID", SqlDbType.Int);

        cmd.Parameters.Add("@pPackByRouteStop", SqlDbType.Int);

#endif

        cmd.Parameters.Add("@pContainerType", SqlDbType.VarChar, 50);

#if ArrowWMS_2_1_x

        cmd.Parameters.Add("@pArea", SqlDbType.VarChar, 50);

#else

        cmd.Parameters.Add("@pAreaGroupID", SqlDbType.Int);

#endif

        return cmd;

    }

#endif

​

    //public static object s_SqlLock = new object();

​

    /// <summary>

    /// get articles already assigned to the container

    /// </summary>

    public static void AddOrderItems(SqlConnection connection, IList<Item> items, Container container,

        SqlInt32 StartOrderID, SqlInt32 PackByRouteStop, SqlString ContainerType,

#if ArrowWMS_2_1_x

        SqlString Area,

#else

        SqlInt32 AreaGroupID,

#endif

        string OverrideQuery = null)

    {

        //lock (s_SqlLock)

        {

            SqlCommand OrderItemsCommand = null;

            if (!string.IsNullOrEmpty(OverrideQuery))

            {

                OrderItemsCommand = new SqlCommand(OverrideQuery, connection);

                OrderItemsCommand.Parameters.Add("@pStartOrderID", SqlDbType.Int).SqlValue = StartOrderID;

                OrderItemsCommand.Parameters.Add("@pPackByRouteStop", SqlDbType.Int).SqlValue = PackByRouteStop;

                OrderItemsCommand.Parameters.Add("@pContainerType", SqlDbType.VarChar, 50).SqlValue = ContainerType;

#if ArrowWMS_2_1_x

                OrderItemsCommand.Parameters.Add("@pArea", SqlDbType.VarChar, 50).SqlValue = Area;

#else

                OrderItemsCommand.Parameters.Add("@pAreaGroupID", SqlDbType.Int).SqlValue = AreaGroupID;

#endif

            }

            else

            {

                OrderItemsCommand = new SqlCommand(sqlQueryOrderItems, connection);

#if !USE_TEMP_OND_TABLE

                OrderItemsCommand.Parameters.Add("@pStartOrderID", SqlDbType.Int).SqlValue = StartOrderID;

                OrderItemsCommand.Parameters.Add("@pPackByRouteStop", SqlDbType.Int).SqlValue = PackByRouteStop;

#endif

                OrderItemsCommand.Parameters.Add("@pContainerType", SqlDbType.VarChar, 50).SqlValue = ContainerType;

#if ArrowWMS_2_1_x

                OrderItemsCommand.Parameters.Add("@pArea", SqlDbType.VarChar, 50).SqlValue = Area;

#else

                OrderItemsCommand.Parameters.Add("@pAreaGroupID", SqlDbType.Int).SqlValue = AreaGroupID;

#endif

            }

​

            using (OrderItemsCommand)

            {

                using (SqlDataReader reader = OrderItemsCommand.ExecuteReader())

                {

                    while (reader.Read())

                    {

                        int i = 0;

                        var ArticleID = reader.GetSqlInt32(i++);

                        var SizeX = GetDecimal(reader, ref i, 0);

                        var SizeY = GetDecimal(reader, ref i, 0);

                        var SizeZ = GetDecimal(reader, ref i, 0);

                        var artquantity = GetDecimal(reader, ref i, 0);

                        var plannedQuantity = GetDecimal(reader, ref i, 0);

                        var allowXYRotation = GetBoolean(reader, ref i, true);

                        var allowZRotation = GetBoolean(reader, ref i, true);

                        var orderID = GetInt32(reader, ref i, 0);

                        var putID = GetInt32(reader, ref i, 0);

#if USE_MAXWEIGHT

                        var weight = GetDecimal(reader, ref i, 0);

#endif

#if STACKABLE_ARTICLES

                        var maxStack = GetInt32(reader, ref i, 1);

                        var stackDeltaX = 0m;

                        var stackDeltaY = 0m;

                        var stackDeltaZ = 0m;

                        if (maxStack > 1)

                        {

                            stackDeltaX = GetDecimal(reader, ref i, 0);

                            stackDeltaY = GetDecimal(reader, ref i, 0);

                            stackDeltaZ = GetDecimal(reader, ref i, 0);

                        }

#endif

​

                        var quantity = (int)(artquantity > 0 ? System.Math.Ceiling(plannedQuantity / artquantity) : plannedQuantity);

                        if (!ArticleID.IsNull && quantity > 0)

                        {

                            StackItem(new Item(ArticleID.Value, (decimal)SizeX, (decimal)SizeY, (decimal)SizeZ, quantity)

                            {

                                OrderID = orderID,

                                PutID = putID,

                                ArticleQuantity = (int)artquantity,

                                PlannedQuantity = (int)plannedQuantity,

                                AllowXZRotation = allowXYRotation,

                                AllowYRotation = allowZRotation

#if USE_MAXWEIGHT

                            ,

                                Weight = weight

#endif

#if STACKABLE_ARTICLES

                            ,

                                MaxStack = maxStack,

                                StackDelta1 = stackDeltaX,

                                StackDelta2 = stackDeltaZ,

                                StackDelta3 = stackDeltaY

#endif

                            }, items, container);

                        }

                    }

                    //OrderItemsCommand.Cancel();

                }

                OrderItemsCommand.Connection = null;

            }

        } // s_SqlLock

    }

​

    // --------------------------------

​

#if ALLOW_OVERRIDE

    private const string sqlQueryItemsFunc = @"IF EXISTS (select null

           from   sys.objects

           where  object_id = OBJECT_ID(N'[core].[BinpackingContainerItems]')

                   AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))

  select ArticleID,SizeX,SizeY,SizeZ,Quantity,PlannedQuantity" +

      ",AllowXYRotation,AllowZRotation" +

#if USE_MAXWEIGHT

    ",ArticleWeight" +

#endif

#if STACKABLE_ARTICLES

    ",MaxStack,StackDeltaX,StackDeltaY,StackDeltaZ" +

#endif

    @" from [core].[BinpackingContainerItems] (@pContainerID);

else

";

#else

    private const string sqlQueryItemsFunc = "";

#endif

​

    // FIXME: outdated get article sql

    private const string sqlQueryItems = sqlQueryItemsFunc +

  "select asgn.ArticleID,wa.SizeX,wa.SizeY,wa.SizeZ,wa.Quantity,asgn.PlannedQuantity" +

#if CUBING_STRATEGY

    ",(wa.AllowXYRotation & isnull(cs.AllowXYRotation,1)) as AllowXYRotation" +

      ",(wa.AllowZRotation & isnull(cs.AllowZRotation,1)) as AllowZRotation" +

#else

    ",wa.AllowXYRotation,wa.AllowZRotation" +

#endif

    ",asgn.AssignmentID" +

#if USE_MAXWEIGHT

    ",wa.ArticleWeight" +

#endif

#if STACKABLE_ARTICLES

    ",wa.MaxStack,wa.StackDeltaX,wa.StackDeltaY,wa.StackDeltaZ" +

#endif

@"

from core.Assignment asgn join core.Article a on a.ArticleID = asgn.ArticleID

    outer apply (

        select top 1 wa.SizeX,wa.SizeY,wa.SizeZ,wa.Quantity,wa.AllowXYRotation,wa.AllowZRotation" +

#if USE_MAXWEIGHT

        ",wa.Weight / wa.Quantity as ArticleWeight" +

#endif

#if STACKABLE_ARTICLES

        ",wa.MaxStack,wa.StackDeltaX,wa.StackDeltaY,wa.StackDeltaZ" +

#endif

#if ArrowWMS_2_1_x

@"

        from   core.Article wa

        where  wa.BaseArticleID = a.BaseArticleID

        and    wa.Weight > 0

        and    wa.SizeX > 0

        and    wa.SizeY > 0

        and    wa.SizeZ > 0

        order by abs(wa.Quantity - a.Quantity),

                 case when wa.ArticleID = a.ArticleID then 0 else 1 end" +

#else

@"

        from   core.Article wa

        where  wa.BaseArticleID = a.BaseArticleID

        and    wa.Weight is not null

        and    wa.SizeX is not null

        and    wa.SizeY is not null

        and    wa.SizeZ is not null

        and    wa.Volume is not null

        and    wa.ArticleStatus != 'INACTIVE'

        order by abs(wa.Quantity - a.Quantity), 

                case when wa.ArticleID = a.ArticleID then 0 else 1 end" +

#endif

  @"

    ) wa" +

#if CUBING_STRATEGY

    @"

left join core.CubingStrategy cs on cs.CubingStrategyID = asgn.CubingStrategyID" +

#endif

    @"

where (asgn.DestinationLocationID = @pContainerID)";

​

    private static SqlCommand CreateItemsCommand()

    {

        var cmd = new SqlCommand(sqlQueryItems);

        cmd.Parameters.Add("@pContainerID", SqlDbType.Int);

        return cmd;

    }

​

    //private static readonly SqlCommand ItemsCommand = CreateItemsCommand();

​

    /// <summary>

    /// get articles already assigned to the container

    /// </summary>

    public static void AddItems(SqlConnection connection, IList<Item> items, Container container, SqlInt32 ContainerID)

    {

        //lock (s_SqlLock)

        {

            //var ItemsCommand = CreateItemsCommand();

            //ItemsCommand.Connection = connection;

            //ItemsCommand.Parameters["@pContainerID"].SqlValue = ContainerID;

            var ItemsCommand = new SqlCommand(sqlQueryItems, connection);

            ItemsCommand.Parameters.Add("@pContainerID", SqlDbType.Int).SqlValue = ContainerID;

            using (ItemsCommand)

            {

                using (SqlDataReader reader = ItemsCommand.ExecuteReader())

                {

                    while (reader.Read())

                    {

                        int i = 0;

                        var ArticleID = reader.GetSqlInt32(i++);

                        var SizeX = GetDecimal(reader, ref i, 0);

                        var SizeY = GetDecimal(reader, ref i, 0);

                        var SizeZ = GetDecimal(reader, ref i, 0);

                        var artquantity = GetDecimal(reader, ref i, 0);

                        var plannedQuantity = GetDecimal(reader, ref i, 0);

                        var allowXYRotation = GetBoolean(reader, ref i, true);

                        var allowZRotation = GetBoolean(reader, ref i, true);

                        var putID = GetInt32(reader, ref i, -1);

#if USE_MAXWEIGHT

                        var weight = GetDecimal(reader, ref i, 0);

#endif

#if STACKABLE_ARTICLES

                        var maxStack = GetInt32(reader, ref i, 1);

                        var stackDeltaX = 0m;

                        var stackDeltaY = 0m;

                        var stackDeltaZ = 0m;

                        if (maxStack > 1)

                        {

                            stackDeltaX = GetDecimal(reader, ref i, 0);

                            stackDeltaY = GetDecimal(reader, ref i, 0);

                            stackDeltaZ = GetDecimal(reader, ref i, 0);

                        }

#endif

​

                        //var assignmentID = reader.GetSqlInt32(6);

                        var quantity = (int)(artquantity > 0 ? System.Math.Ceiling(plannedQuantity / artquantity) : plannedQuantity);

                        if (!ArticleID.IsNull && quantity > 0)

                            StackItem(new Item(ArticleID.Value, (decimal)SizeX, (decimal)SizeY, (decimal)SizeZ, quantity)

                            {

                                PutID = putID,

                                ArticleQuantity = (int)artquantity,

                                PlannedQuantity = (int)plannedQuantity,

                                AllowXZRotation = allowXYRotation,

                                AllowYRotation = allowZRotation

#if USE_MAXWEIGHT

                        ,

                                Weight = weight

#endif

#if STACKABLE_ARTICLES

                        ,

                                MaxStack = maxStack,

                                StackDelta1 = stackDeltaX,

                                StackDelta2 = stackDeltaZ,

                                StackDelta3 = stackDeltaY

#endif

                            }, items, container);

                    }

                }

            }

            ItemsCommand.Connection = null;

        } // s_SqlLock

    }

​

    private static decimal GetDecimal(SqlDataReader reader, ref int i, decimal defaultvalue = 0)

    {

        var value = (i >= reader.FieldCount || reader.IsDBNull(i))

            ? defaultvalue

            : System.Convert.ToDecimal(reader.GetValue(i));

        i++;

        return value;

    }

​

    private static int GetInt32(SqlDataReader reader, ref int i, int defaultvalue = 0)

    {

        var value = (i >= reader.FieldCount || reader.IsDBNull(i))

            ? defaultvalue

            : System.Convert.ToInt32(reader.GetValue(i));

        i++;

        return value;

    }

​

    private static bool GetBoolean(SqlDataReader reader, ref int i, bool defaultvalue = false)

    {

        var value = (i >= reader.FieldCount || reader.IsDBNull(i))

            ? defaultvalue

            : System.Convert.ToBoolean(reader.GetValue(i));

        i++;

        return value;

    }

​

    // ----------------------------------------------------------------

    public static void Main()

    {

        Console.WriteLine("declare @pStartOrderID int = null;");

        Console.WriteLine("declare @pPackByRouteStop bit = null;");

        Console.WriteLine("declare @pContainerType varchar(50) = null;");

#if ArrowWMS_2_1_x

        Console.WriteLine("declare @pArea varchar(50) = null;");

#else

        Console.WriteLine("declare @pAreaGroupID int = null;");

#endif

        Console.WriteLine("declare @pContainerID int = null;");

        Console.WriteLine("-----------------");

        Console.WriteLine(sqlQueryOrderItems);

        Console.WriteLine("-----------------");

        Console.WriteLine(sqlQueryItems);

    }

​

    private const string functionSuffix = "";

​

#if SQL_ENABLED

#if !USE_TEMP_OND_TABLE

    private const string vw_OrderNeedsDestination = "outbound.vw_OrderNeedsDestination";

#else

    private const string vw_OrderNeedsDestination = "#tempOrderNeedsDestination";

#endif

    private const SystemDataAccessKind systemDataAccessKind = SystemDataAccessKind.Read; // Must be Read for sql server 2014

    private const DataAccessKind dataAccessKind = DataAccessKind.Read;

#endif

    private const decimal maxResizeSidePercentage = 0.5m; // (fake)rotate when too large

​

    // --------------------------------

​

    // merge stackable items together to a single item

    public static List<Item> StackItems(IList<Item> items, Container container)

    {

        List<Item> additems = new List<Item>();

        foreach (var item in items)

        {

            StackItem(item, additems, container);

        }

        return additems;

    }

​

    // Create item stacks (= single item) when needed and add to itemlist

    public static void StackItem(Item item, IList<Item> additems, Container container)

    {

#if STACKABLE_ARTICLES

        if (item.MaxStack > 1)

        {

            var xs = item.Dim1; // x, Length

            var ys = item.Dim2; // z, Height

            var zs = item.Dim3; // y, Width

            int i = 1;

            for (i = 1; i < item.MaxStack && i < item.Quantity; i++)

            {

                xs += item.StackDelta1;

                ys += item.StackDelta2;

                zs += item.StackDelta3;

​

                // FIXME: does this make sense? we do need some check to make sure it can at least fit.

                if (ys > container.Height ||

                    xs > container.Length ||

                    zs > container.Width)

                {

                    xs -= item.StackDelta1;

                    ys -= item.StackDelta2;

                    zs -= item.StackDelta3;

                    break;

                }

            }

            item.MaxStack = i;

            if (i > 1)

            {

                int qtyLeft = item.Quantity - i;

                if (qtyLeft > 0)

                {

                    var srcItem = new Item(item);

                    srcItem.Quantity = qtyLeft;

                    StackItem(srcItem, additems, container);

                }

​

                item.Dim1 = xs;

                item.Dim2 = ys;

                item.Dim3 = zs;

                item.Quantity = 1;

            }

        }

#endif

        additems.Add(item);

    }

​

    // ----------------------------------------------------------------

    public class Container { public decimal Height; public decimal Length; public decimal Width; public decimal MaxWeight;

                            public Container(int i, decimal x, decimal y ,decimal z) {} }

    public class Item { 

        public int OrderID;

        public int PutID;

        public int ArticleQuantity;

        public int PlannedQuantity;

        public bool AllowXZRotation;

        public bool AllowYRotation;

        public int Quantity;

        public decimal Weight;

        public int MaxStack;

        public decimal StackDelta1;

        public decimal StackDelta2;

        public decimal StackDelta3;

        public decimal Dim1;

        public decimal Dim2;

        public decimal Dim3;

        public Item(int i, decimal x, decimal y ,decimal z, int q) {       }

        public Item(Item it) {}

    }

}