Change Sales Order Line Background Color

[nsbandara]

Hi,

We are trying to change sales order line background color (or font style) based on inventory item status.

Public Sub SalesOrderLine_Added(ByVal item As JiwaSales.SalesOrder.SalesOrderLine)
If item.InventoryID.Trim.Length > 0 Then
Dim inventory As JiwaInventory.Inventory = JiwaApplication.Manager.Instance.BusinessLogicFactory.CreateBusinessLogic(Of JiwaInventory.Inventory)(Nothing)
inventory.Read(item.InventoryID)

'Need to access order line (row object reference here
End If
End Sub

Can we access sales order line's row object by item in particular row or is there any alternative method to change row style based on inventory item status.

Q2: Is there a API documentation available for Jiwa7 ?

[SBarnes]

Hi

I hope this is of some help.

In answer to you second question about the API see https://forums.jiwa.com.au/viewtopic.php?f=27&t=171 but my recommendation is that you get some sort of .net reflection tool as you can then see the actual Jiwa code inside the DLLs, I use .net Relector by Redgate.

To achieve what you are trying to do you actually want to write a form plugin and then attach you code to the forms sales order object, you do this in a similar fashion to the code below but you would attach to salesorder.SalesOrderLineAdded, to get at the grid use salesform.grdlines and declare salesform as JiwaFinancials.Jiwa.JiwaSalesUI.SalesOrder.SalesOrderEntryForm at the class level so you can access it in your function, to do something with the foreground you would do someting like salesform.grdlines.ActiveSheet.Rows[Row].ForeColor = Color.Red;

To work with grid and its feature the best advice I can give you is what Scott Pearce from Jiwa suggested to me download and install the trial for Spread 8 by Grapecity and then you'll have the help file.

Code: Select all

public void Setup(JiwaFinancials.Jiwa.JiwaApplication.IJiwaForm JiwaForm, JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin)
    {
      if (JiwaForm.GetType() == typeof(JiwaFinancials.Jiwa.JiwaSalesUI.SalesOrder.SalesOrderEntryForm))
      {

         salesform = (JiwaFinancials.Jiwa.JiwaSalesUI.SalesOrder.SalesOrderEntryForm)JiwaForm;

         JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder salesorder = salesform.SalesOrder;

         if (JiwaFinancials.Jiwa.JiwaApplication.Manager.Instance.MDIParentForm != null )
         {

            
            salesorder.Created  += CreateOrderProcessing;   
            salesorder.SaveEnd += CreateOrderSaved;



         
         }         
      
      }

      
    }

[Mike.Sheen]

To change the cell background colour, take a look at the attached plugin.

It changes the demand cell to red (and the forecolour to white) if the demand quantity is > 0

screenshot

DemandCellsRed.PNG (11.84 KiB) Viewed 1461 times

Mike

[Ernst]

Hi Mike/ Scott,
Have a similar request to this. Have used sample code.. See below. But the InventoryStatus, does not seem to be updating for each line.
It is only correct when you add a new line, but not on the display. The partno changes for each line.

Is this an error in JIWA, or are we doing it wrong?

Private Sub SalesOrderForm_LineDisplayed(ByVal sender As Object, ByVal e As System.EventArgs, ByVal SalesOrderLine As JiwaSales.SalesOrder.SalesOrderLine)
' Highlight Obsolete Items
Dim salesOrderForm As JiwaSalesUI.SalesOrder.SalesOrderEntryForm = sender
If salesOrderLine.PhysicalItem = True AndAlso salesOrderLine.CommentLine = False Then
For row As Integer = 0 To salesOrderForm.grdLines.ActiveSheet.RowCount - 1
Dim key As String = salesOrderForm.grdLines.GridText("Key", row)
If key = salesOrderLine.RecID Then
' msgbox (salesOrderLine.InventoryStatus)
If salesOrderLine.InventoryStatus = 4 Then
salesOrderForm.grdLines.ActiveSheet.Cells(row, salesOrderForm.grdLines.ActiveSheet.Columns("ExtPrice").Index).BackColor = System.Drawing.Color.Blue
Else
salesOrderForm.grdLines.ActiveSheet.Cells(row, salesOrderForm.grdLines.ActiveSheet.Columns("ExtPrice").Index).BackColor = System.Drawing.Color.White
End If
Exit For
End If
Next
End If
End Sub

[Mike.Sheen]

Have a similar request to this. Have used sample code.. See below. But the InventoryStatus, does not seem to be updating for each line.
It is only correct when you add a new line, but not on the display. The partno changes for each line.

Is this an error in JIWA, or are we doing it wrong?

Hi Ernst,

Confirmed this is a bug - logged as DEV-5667 - the inventory status is not being populated into the InventoryStatus property of the SalesOrderLine class when reading a sales order.

As a work around, an ad-hoc query can read and set this. Sample plugin attached.

Also, note my sample uses a shared / static method of the Manager class to determine the best forecolor to use with a given backcolor to provide high contrast (JiwaApplication.Manager.ContrastColour).

Code: Select all

Private Sub SalesOrderForm_LineDisplayed(ByVal sender As Object, ByVal e As System.EventArgs, ByVal SalesOrderLine As JiwaSales.SalesOrder.SalesOrderLine)
   ' Highlight Obsolete Items   
   Dim salesOrderForm As JiwaSalesUI.SalesOrder.SalesOrderEntryForm = sender
   If salesOrderLine.PhysicalItem = True AndAlso salesOrderLine.CommentLine = False Then
      For row As Integer = 0 To salesOrderForm.grdLines.ActiveSheet.RowCount - 1
         Dim key As String = salesOrderForm.grdLines.GridText("Key", row)
         If key = salesOrderLine.RecID Then
            '   msgbox (salesOrderLine.InventoryStatus)
            If SalesOrderLine.NonStock = False AndAlso SalesOrderLine.CommentLine = False Then
               Dim SQLReader As SqlDataReader = Nothing
               Dim SQLParam As SqlParameter = Nothing
               Try
                  With JiwaApplication.Manager.Instance.Database
                     Dim SQL As String  = "SELECT Status " &
                                      "FROM IN_Main " &
                                      "WHERE InventoryID = @InventoryID "

                     Using SQLCmd As SqlCommand = New SqlCommand(SQL, .SQLConnection, .SQLTransaction)
                        SQLParam = New SqlParameter("@InventoryID", System.Data.SqlDbType.VarChar)
                        SQLParam.Value = SalesOrderLine.InventoryID                           
                        SQLCmd.Parameters.Add(SQLParam)
                        
                        SQLReader = .ExecuteReader(SQLCmd)

                        If SQLReader.Read = True Then
                           SalesOrderLine.InventoryStatus = SQLReader("Status")                                    
                        Else
                           Throw New JiwaFinancials.Jiwa.JiwaApplication.Exceptions.RecordNotFoundException("Inventory Item Not Found")
                        End If
                     End Using
                  End With
               Finally
                  If Not SQLReader Is Nothing Then
                     SQLReader.Close()
                  End If
               End Try
            End If
            If salesOrderLine.InventoryStatus = 4 Then
               salesOrderForm.grdLines.ActiveSheet.Cells(row, salesOrderForm.grdLines.ActiveSheet.Columns("ExtPrice").Index).BackColor = System.Drawing.Color.Blue
               salesOrderForm.grdLines.ActiveSheet.Cells(row, salesOrderForm.grdLines.ActiveSheet.Columns("ExtPrice").Index).ForeColor = JiwaApplication.Manager.ContrastColour(System.Drawing.Color.Blue)
            Else
               salesOrderForm.grdLines.ActiveSheet.Cells(row, salesOrderForm.grdLines.ActiveSheet.Columns("ExtPrice").Index).BackColor = System.Drawing.Color.White
               salesOrderForm.grdLines.ActiveSheet.Cells(row, salesOrderForm.grdLines.ActiveSheet.Columns("ExtPrice").Index).ForeColor = JiwaApplication.Manager.ContrastColour(System.Drawing.Color.White)
            End If
         Exit For
      End If
      Next
   End If
End Sub


[Ernst]

Brilliant.. Thanks

[nsbandara]

Attached is the solution I implemented.

[Mike.Sheen]

Attached is the solution I implemented.

Thanks for sharing your solution.

A few pointers to help improve it:

1. You create an Inventory business logic object without using our Factory:

Code: Select all

JiwaFinancials.Jiwa.JiwaInventory.Inventory inventory = new JiwaFinancials.Jiwa.JiwaInventory.Inventory();

You really should use our factory to create business logic objects.

2. You perform a read of the inventory business logic without checking to see first if the item is a comment line or non inventory item - this will throw an exception if a sales order has a comment line or non-inventory item on it:

Code: Select all

inventory.Read(SalesOrderLine.InventoryID);

You should check before doing the .Read if the item is not a comment or non-inventory item.

3. Related to point 1. - every time a sales order line is displayed, you perform an inventory business logic read for the line - when reading a sales order with say a few dozen lines this will be extremely expensive performance-wise.
I recommend performing a small ad-hoc query to get just the information you need instead.