More ranges for Manual Backorder Release

[DannyC]

How easy/hard is it to add From/To ranges to the Manual Backorder Release module?
I am thinking it would be handy to have a range for Supplier (I guess just the default supplier) so that when products have been received the user can just chuck in the supplier & all recent receipted stock will show.
Or maybe sales order delivery postcode and/or state so any backorders for a region can display.
Or maybe debtor classification.
Or maybe inventory classification.

I have actually had a request for Delivery Address Country and wondering how it could be implemented.

Cheers

[SBarnes]

It would actually be handy if there was a way to do this with a number of screens such Sales order export, Sales order processing and sales order batch printing as I have also had requests to change the parameters to these at times.

Given I am assuming that most of these inherit from the processing screen and use the processing object, would there not be a common way of doing this where you could get at the grid to add columns and then get at the SQL to be executed by the batch object?

It would also be good if you could control the columns that appeared in the results.

[Nina Tulic]

Hi,
We have added a stock received date and delivery zone filter to the Manual Backorder Release with a plugin for v7.2 and implemented a custom stored procedure. It was not an overly difficult plugin.

Nina
[email protected]

[DannyC]

Nina,

Are you able to share that plugin here? Remove any client sensitive code...

[Mike.Sheen]

We have added a stock received date and delivery zone filter to the Manual Backorder Release with a plugin for v7.2 and implemented a custom stored procedure. It was not an overly difficult plugin.

Are you sure you're talking about the same thing? I just had a look and came to the conclusion you'd essentially have to re-create the Manual BO release form to extend it with additional ranges which get passed through to the stored procedure.

[SBarnes]

Are you sure you're talking about the same thing? I just had a look and came to the conclusion you've essentially have to re-create the Manual BO release form to extend it with additional ranges which get passed through to the stored procedure.

Hence my comments it would be good if there was a way of adjusting any of the processing screens as like Mike I would have thought you needed to reinvent the wheel/screen.

[Nina Tulic]

Hi All,
Apologies very busy week.
We did not have to create a new form. We added the new range options to the FormPlugin region and then modified the Go button action to use our custom stored procedure rather than the standard.

Below is the code that we used.

Code: Select all

        public void SetupBeforeHandlers(JiwaFinancials.Jiwa.JiwaApplication.IJiwaForm JiwaForm, JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin)
    {
        if (JiwaForm is JiwaFinancials.Jiwa.JiwaManualBOBatchUI.MainForm)
        {
            JiwaFinancials.Jiwa.JiwaManualBOBatchUI.MainForm manualBoReleaseForm = JiwaForm as JiwaFinancials.Jiwa.JiwaManualBOBatchUI.MainForm;
            manualBoReleaseForm.GetCandidatesUltraButton.Click += new EventHandler(GetCandidatesUltraButton_Click);
        }
    }

    public void Setup(JiwaFinancials.Jiwa.JiwaApplication.IJiwaForm JiwaForm, JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin)
    {
        if (JiwaForm is JiwaFinancials.Jiwa.JiwaManualBOBatchUI.MainForm)
        {
            JiwaFinancials.Jiwa.JiwaManualBOBatchUI.MainForm manualBoReleaseForm = JiwaForm as JiwaFinancials.Jiwa.JiwaManualBOBatchUI.MainForm;

            manualBoReleaseForm.grdRanges.AddColumn("StockReceivedDate", new FarPoint.Win.Spread.CellType.DateTimeCellType(), "Stock Received Date", 8, false, true, false, false, 255, false, false, 0, false);
            manualBoReleaseForm.grdRanges.AddColumn("DeliveryZone", new JiwaFinancials.Jiwa.JiwaApplication.JiwaManageGrid.JiwaTextCellType(), "Delivery Zone", 10, false, true, false, true, 50, false, false, 0, false);

            manualBoReleaseForm.grdRanges.set_GridText("StockReceivedDate", 0, new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1));
            manualBoReleaseForm.grdRanges.set_GridText("StockReceivedDate", 1, DateTime.Today);

            manualBoReleaseForm.grdRanges.set_GridText("DeliveryZone", 0, string.Empty);
            manualBoReleaseForm.grdRanges.set_GridText("DeliveryZone", 1, string.Empty);
        }
    }   

[Mike.Sheen]

That's a bit teasy - the juicy bit you left out!

For those following along at home, you'll need to add some custom code in your handler for the GetCandidatesUltraButton.Click event that Nina shows being wired up in the SetupBeforeHandlers.

The handler we have looks like this - but you don't want to replicate this - :

Code: Select all

Private Sub GetCandidatesUltraButton_Click(sender As Object, e As EventArgs)
   Dim oldCursor As Cursor = Cursor
   Try
      Cursor = Cursors.WaitCursor

      Dim orderBy As Integer
      If OrderByUltraOptionSet.CheckedIndex = 0 Then
         orderBy = 0
      Else
         orderBy = 1
      End If
      ManualBOBatch.BatchLines.AddRange(orderBy)

      CheckEditStatus()

   Finally
      Cursor = oldCursor
   End Try
End Sub

ManualBOBatch in the above is a property of the form - this is the business logic object backing the form. In order to be able to customise the parameters to the stored procedure, you need to not call the ManualBOBatch.BatchLines.AddRange() method above, but instead write your own code which does what that does - here's the AddRange method we have in our ManualBOBatch.BatchLineCollection business logic class - it should be enough for you to customise the parameters as required. Just keep in mind this code is within the AddRange method of the ManualBOBatch.BatchLines - so calls like Add(newBatchLine) seen below would actually need to be ManualBOBatch.BatchLines.Add(newBatchLine)

Code: Select all

Public Sub AddRange(ByVal OrderBy As Integer)

   Dim SQLReader As SqlDataReader = Nothing
   Dim SQLParam As SqlParameter
   Dim e As New System.ComponentModel.CancelEventArgs

   OnRangeAdding(e)
   If e.Cancel = False Then
      Try

         With Manager.Database

            Dim SQL As String = _ManualBOBatch.SystemSettings.ReadBackOrderDetailsListStoredProcedureName

            Using SQLCmd As SqlCommand = New SqlCommand(SQL, .SQLConnection, .SQLTransaction)
               SQLCmd.CommandType = Data.CommandType.StoredProcedure

               SQLParam = New SqlParameter("@IN_LogicalID", System.Data.SqlDbType.Char)
               SQLParam.Value = _ManualBOBatch.Warehouse.IN_LogicalID
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@From_InvoiceNo", System.Data.SqlDbType.VarChar)
               SQLParam.Value = ManualBOBatch.Ranges.SalesOrder.From.InvoiceNo
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@To_InvoiceNo", System.Data.SqlDbType.VarChar)
               SQLParam.Value = ManualBOBatch.Ranges.SalesOrder.To.InvoiceNo
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@From_ExpectedDeliveryDate", System.Data.SqlDbType.DateTime)
               SQLParam.Value = ManualBOBatch.Ranges.ExpectedDeliveryDate.From
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@To_ExpectedDeliveryDate", System.Data.SqlDbType.DateTime)
               SQLParam.Value = CDate(ManualBOBatch.Ranges.ExpectedDeliveryDate.To.ToShortDateString & " 23:59:59")
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@From_DebtorAccountNo", System.Data.SqlDbType.VarChar)
               SQLParam.Value = ManualBOBatch.Ranges.Debtor.From.AccountNo
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@To_DebtorAccountNo", System.Data.SqlDbType.VarChar)
               SQLParam.Value = ManualBOBatch.Ranges.Debtor.To.AccountNo
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@From_PartNo", System.Data.SqlDbType.VarChar)
               SQLParam.Value = ManualBOBatch.Ranges.Inventory.From.PartNo
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@To_PartNo", System.Data.SqlDbType.VarChar)
               SQLParam.Value = ManualBOBatch.Ranges.Inventory.To.PartNo
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@From_BillType", System.Data.SqlDbType.SmallInt)
               SQLParam.Value = ManualBOBatch.Ranges.BillType.From
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@To_BillType", System.Data.SqlDbType.SmallInt)
               SQLParam.Value = ManualBOBatch.Ranges.BillType.To
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@From_WholesaleInvoice", System.Data.SqlDbType.SmallInt)
               SQLParam.Value = ManualBOBatch.Ranges.InvoiceType.From
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@To_WholesaleInvoice", System.Data.SqlDbType.SmallInt)
               SQLParam.Value = ManualBOBatch.Ranges.InvoiceType.To
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@From_OrderType", System.Data.SqlDbType.SmallInt)
               SQLParam.Value = ManualBOBatch.Ranges.OrderType.From
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@To_OrderType", System.Data.SqlDbType.SmallInt)
               SQLParam.Value = ManualBOBatch.Ranges.OrderType.To
               SQLCmd.Parameters.Add(SQLParam)

               SQLParam = New SqlParameter("@OrderBy", System.Data.SqlDbType.SmallInt)
               SQLParam.Value = OrderBy
               SQLCmd.Parameters.Add(SQLParam)

               SQLReader = .ExecuteReader(SQLCmd)

               RemoveAll()
               _ManualBOBatch.InventoryItems.RemoveAll()

               Dim OrdersOnBackID As String = ""
               Dim InventoryID As String = ""
               Dim InvoiceID As String = ""
               Dim newInventoryItem As InventoryItem = Nothing
               Dim newSalesOrder As SalesOrder = Nothing
               Dim newBatchLine As BatchLine = Nothing
               Do While SQLReader.Read = True
                  OrdersOnBackID = .Sanitise(SQLReader, "OrdersOnBackID").ToString.Trim()
                  If OrdersOnBackID.Trim.Length > 0 And BackOrderExists(OrdersOnBackID) = False Then
                     InventoryID = .Sanitise(SQLReader, "InventoryID").ToString.Trim()
                     If _ManualBOBatch.InventoryItems.Contains(InventoryID) = False Then
                        newInventoryItem = Manager.CollectionItemFactory.CreateCollectionItem(Of InventoryItem)
                        newInventoryItem.Inventory.ReadRecord(InventoryID)
                        newInventoryItem.QuantityAvailable = .Sanitise(SQLReader, "QuantityAvailable")
                        newInventoryItem.RecID = InventoryID
                        _ManualBOBatch.InventoryItems.Add(newInventoryItem)
                     End If

                     InvoiceID = .Sanitise(SQLReader, "InvoiceID").ToString.Trim()
                     If _ManualBOBatch.SalesOrders.Contains(InvoiceID) = False Then
                        newSalesOrder = Manager.CollectionItemFactory.CreateCollectionItem(Of SalesOrder)
                        newSalesOrder.SalesOrder.ReadRecord(InvoiceID)
                        newSalesOrder.InvoiceHistoryID = .Sanitise(SQLReader, "InvoiceHistoryID").ToString.Trim()
                        newSalesOrder.InvoiceHistoryNo = .Sanitise(SQLReader, "HistoryNo")
                        newSalesOrder.RecID = InvoiceID
                        _ManualBOBatch.SalesOrders.Add(newSalesOrder)
                     End If

                     newBatchLine = Manager.CollectionItemFactory.CreateCollectionItem(Of BatchLine)
                     newBatchLine.IN_OnBackOrder_OrdersOnBackID = OrdersOnBackID
                     newBatchLine.InventoryItemKey = InventoryID
                     newBatchLine.BackOrderQuantity = .Sanitise(SQLReader, "BackOrderQuantity")
                     newBatchLine.BackOrderValue = .Sanitise(SQLReader, "BackOrderValue")
                     newBatchLine.SalesOrderItemKey = InvoiceID
                     newBatchLine.BackOrderPriority = .Sanitise(SQLReader, "Priority")
                     newBatchLine.BackOrderPriorityWeight = .Sanitise(SQLReader, "PriorityWeight")
                     newBatchLine.InvoiceLineID = .Sanitise(SQLReader, "InvoiceLineID").ToString.Trim()
                     newBatchLine.KitType = .Sanitise(SQLReader, "KitType")
                     Add(newBatchLine)
                     _ManualBOBatch.InventoryItems.RecalculateAvailable(_ManualBOBatch.InventoryItems(newBatchLine.InventoryItemKey).Inventory.InventoryID)
                  End If
               Loop

               SQLReader.Close()
            End Using
         End With
         OnRangeAdded()
      Finally
         If Not SQLReader Is Nothing Then
            SQLReader.Close()
         End If
      End Try
   End If

End Sub

[DannyC]

Thanks Nina. That's great.
Can you also provide your code to

Code: Select all

GetCandidatesUltraButton_Click

?
I've tried to follow Mike's suggestion but after a couple of hours fiddling (and converting the code to c#) I still can't quite get it.

Thanks

[Nina Tulic]

Hi Danny,

Hopefully this helps.

Code: Select all

private void GetCandidatesUltraButton_Click(object sender, EventArgs e)
    {
        JiwaFinancials.Jiwa.JiwaManualBOBatchUI.MainForm manualBoReleaseForm = (sender as Control).FindForm() as JiwaFinancials.Jiwa.JiwaManualBOBatchUI.MainForm;
        int num;
        Cursor cursor = manualBoReleaseForm.Cursor;
        try
        {
            manualBoReleaseForm.Cursor = Cursors.WaitCursor;
            num = (manualBoReleaseForm.OrderByUltraOptionSet.CheckedIndex != 0 ? 1 : 0);

            CustomRanges customRanges = new CustomRanges();
            DateTime dateTime = DateTime.Today;
            object gridValue = manualBoReleaseForm.grdRanges.get_GridText("StockReceivedDate", 0);
            if (gridValue != null)
            {
                DateTime.TryParse(Convert.ToString(gridValue), out dateTime);
                customRanges.StockReceivedDateFrom = dateTime;
            }
            gridValue = manualBoReleaseForm.grdRanges.get_GridText("StockReceivedDate", 1);
            if (gridValue != null)
            {
                DateTime.TryParse(Convert.ToString(gridValue), out dateTime);
                customRanges.StockReceivedDateTo = dateTime;
            }
            gridValue = manualBoReleaseForm.grdRanges.get_GridText("DeliveryZone", 0);
            if (gridValue != null)
            {
                customRanges.DeliveryZoneFrom = Convert.ToString(gridValue);
            }
            gridValue = manualBoReleaseForm.grdRanges.get_GridText("DeliveryZone", 1);
            if (gridValue != null)
            {
                customRanges.DeliveryZoneTo = Convert.ToString(gridValue);
            }

            -- Private function is called here which is specific to our client;
        }
        finally
        {
            manualBoReleaseForm.Cursor = cursor;
            throw new JiwaFinancials.Jiwa.JiwaApplication.Exceptions.ClientCancelledException();
        }
    }