Sales Order Sort Order

[Ernst]

Am trying to add a simple sort by staff member to the sales order sort list.
When I go Next its jumping to the next staffname in the alphabet instead of same staffname, next invoice

Also if I then try another sort e.g. sort by name it gets this error.

Capture.PNG (10.44 KiB) Viewed 4908 times

Have this code. Anybody tried adding extra sorts to sales orders ?

Public Sub Setup(ByVal JiwaBusinessLogic As JiwaApplication.IJiwaBusinessLogic, ByVal Plugin As JiwaApplication.Plugin.Plugin) Implements JiwaApplication.IJiwaBusinessLogicPlugin.Setup
If TypeOf JiwaBusinessLogic Is JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder Then
Dim Salesorder As JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder = JiwaBusinessLogic
Salesorder.SortOrders.Add(New JiwaFinancials.Jiwa.JiwaApplication.IJiwaNavigable.SortKey With {
.Description = "Staff Name",
.FieldName = "hr_staff.fname",
.SortFieldValueMethod = Function() Salesorder.Staff.FirstName
})
Salesorder.BaseFindSQLQuery = "SELECT TOP 1 SO_Main.InvoiceID FROM SO_Main INNER JOIN HR_Staff ON HR_Staff.StaffID = SO_Main.StaffID"


End If
End Sub

[SBarnes]

I am pretty sure you are being brought undone by the table property on the sales order which is being set by the following code, so the sort must work against this

Code: Select all

        Public Overrides ReadOnly Property TableName As String
            Get
                Return "SO_Main"
            End Get
        End Property


[SBarnes]

I was wrong the table name has nothing to do with it, your assumption as to what the base find sql was to start with was incorrect, change it to the following and you don't get the error

Code: Select all

Salesorder.BaseFindSQLQuery = "SELECT TOP 1 SO_Main.InvoiceID FROM SO_Main JOIN DB_Main ON DB_Main.DebtorID = SO_Main.DebtorID JOIN HR_Staff ON HR_Staff.StaffID = SO_Main.StaffID"


Whether that makes it actually sort correctly you'll have to test, which I actually thing it won't and you should change it to

Code: Select all

.FieldName = "hr_staff.fname+SO_Main.InvoiceNo",


as the code for the sales order has

Code: Select all

            SortOrders.Add(New JiwaApplication.IJiwaNavigable.SortKey With {.Description = "Invoice No.", .FieldName = "SO_Main.InvoiceNo"})
            SortOrders.Add(New JiwaApplication.IJiwaNavigable.SortKey With {.Description = "Account No.", .FieldName = "DB_Main.AccountNo+SO_Main.InvoiceNo"})
            SortOrders.Add(New JiwaApplication.IJiwaNavigable.SortKey With {.Description = "Name", .FieldName = "DB_Main.Name+SO_Main.InvoiceNo"})
            SortOrders.Add(New JiwaApplication.IJiwaNavigable.SortKey With {.Description = "Reference", .FieldName = "SO_Main.SOReference+SO_Main.InvoiceNo"})
            SortOrders.Add(New JiwaApplication.IJiwaNavigable.SortKey With {.Description = "Invoice Initiated Date", .FieldName = "CONVERT(VARCHAR(10), SO_Main.InvoiceInitDate,  112)+SO_Main.InvoiceNo"})
            BaseFindSQLQuery = String.Format("SELECT TOP 1 SO_Main.InvoiceID FROM SO_Main JOIN DB_Main ON DB_Main.DebtorID = SO_Main.DebtorID")


[Ernst]

Still no Joy on this, You would think it would be easy enough. Have adjusted per your suggestion Stuart.. The extra Inner Join db_main, means I dont get any errors now, when using other sort orders.

But the sort by staff, just jumps all over the place, doesent stay on one staff. If you go to start. It just stays there ..weird.

Any suggestions on how to make the next next back, back function actually move by staff username/ invoiceno.

Here is code currently using.

Code: Select all

Public Class BusinessLogicPlugin
    Inherits System.MarshalByRefObject
    Implements JiwaApplication.IJiwaBusinessLogicPlugin

    Public Overrides Function InitializeLifetimeService() As Object
        ' returning null here will prevent the lease manager
        ' from deleting the Object.
        Return Nothing
    End Function

    Public Sub Setup(ByVal JiwaBusinessLogic As JiwaApplication.IJiwaBusinessLogic, ByVal Plugin As JiwaApplication.Plugin.Plugin) Implements JiwaApplication.IJiwaBusinessLogicPlugin.Setup
    If TypeOf JiwaBusinessLogic Is JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder Then
      Dim Salesorder As JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder = JiwaBusinessLogic
      Salesorder.SortOrders.Add(New JiwaFinancials.Jiwa.JiwaApplication.IJiwaNavigable.SortKey With {
                               .Description = "Staff Name",
                               .FieldName = "hr_staff.username+SO_main.InvoiceNo",
                                 .SortFieldValueMethod = Function() Salesorder.Staff.UserName
                              })
         Salesorder.BaseFindSQLQuery = "SELECT TOP 1 SO_Main.InvoiceID FROM  SO_Main join db_main on db_main.debtorid = so_main.debtorid INNER JOIN HR_Staff ON HR_Staff.StaffID = SO_Main.StaffID"


   End If
   End Sub

End Class

[SBarnes]

I am pretty sure you should remove

Code: Select all

  .SortFieldValueMethod = Function() Salesorder.Staff.UserName


[Ernst]

Hi Stuart,

If you take the line out, you get..Try the code for yourself.

JIWAerror.jpg (35.3 KiB) Viewed 4856 times

[SBarnes]

In that case try having the function combine the UserName and Invoice Number below is the actual find code that is getting called, FieldValue is what's getting handed in

Code: Select all

 Public Overridable Sub Find(ByVal ReadMode As JiwaApplication.IJiwaNavigable.ReadModes, FieldName As String, ByVal FieldValue As String, ByVal FilterSQL As String) Implements IJiwaNavigable.Find
            Dim SQL As String
            Dim SQLWhere As String
            Dim SQLReader As SqlDataReader = Nothing
            Dim SQLParam As SqlParameter = Nothing

            Try
                With Manager.Database

                    SQL = BaseFindSQLQuery '"SELECT TOP 1 " & RecIDFieldName & " FROM " & TableName

                    SQLWhere = " WHERE "

                    If TypeOf Me Is IJiwaWarehouseFiltering Then
                        SQL += SQLWhere + TableName + "." + DirectCast(Me, IJiwaWarehouseFiltering).WarehouseColumnName + " = @LogicalWarehouseResidingIn "
                        SQLWhere = " AND "
                    End If

                    If FilterSQL IsNot Nothing AndAlso FilterSQL.Trim.Length > 0 Then
                        SQL += SQLWhere & FilterSQL
                        SQLWhere = " AND "
                    End If

                    If FixedFilterString IsNot Nothing AndAlso FixedFilterString.Trim.Length > 0 Then
                        SQL += SQLWhere & FixedFilterString
                        SQLWhere = " AND "
                    End If

                    Select Case ReadMode
                        Case JiwaApplication.IJiwaNavigable.ReadModes.First
                            SQL += " ORDER BY " & FieldName
                        Case JiwaApplication.IJiwaNavigable.ReadModes.Previous
                            SQL += SQLWhere & " " & FieldName & " < @SeedValue ORDER BY " & FieldName & " DESC"
                        Case JiwaApplication.IJiwaNavigable.ReadModes.[Next]
                            SQL += SQLWhere & " " & FieldName & " > @SeedValue ORDER BY " & FieldName
                        Case JiwaApplication.IJiwaNavigable.ReadModes.Last
                            SQL += " ORDER BY " & FieldName & " DESC"
                        Case JiwaApplication.IJiwaNavigable.ReadModes.Actual
                            SQL += SQLWhere & " " & FieldName & " = @SeedValue"
                    End Select


                    Using SQLCmd As New SqlCommand(SQL, .SQLConnection, .SQLTransaction)

                        Select Case ReadMode
                            Case JiwaApplication.IJiwaNavigable.ReadModes.Previous, JiwaApplication.IJiwaNavigable.ReadModes.[Next]
                                SQLParam = New SqlParameter("@SeedValue", System.Data.SqlDbType.VarChar)
                                SQLParam.Value = FieldValue
                                SQLCmd.Parameters.Add(SQLParam)

                            Case JiwaApplication.IJiwaNavigable.ReadModes.Actual
                                SQLParam = New SqlParameter("@SeedValue", System.Data.SqlDbType.Char)
                                SQLParam.Value = FieldValue
                                SQLCmd.Parameters.Add(SQLParam)
                        End Select

                        If TypeOf Me Is IJiwaWarehouseFiltering Then
                            SQLParam = New SqlParameter("@LogicalWarehouseResidingIn", System.Data.SqlDbType.Char)
                            SQLParam.Value = DirectCast(Me, IJiwaWarehouseFiltering).LogicalWarehouseResidingIn.IN_LogicalID
                            SQLCmd.Parameters.Add(SQLParam)
                        End If

                        SQLReader = .ExecuteReader(SQLCmd)

                        Dim foundRecID As String = ""
                        If SQLReader.Read = True Then
                            foundRecID = .Sanitise(SQLReader, 1).ToString
                        End If

                        SQLReader.Close()

                        If foundRecID.Trim.Length > 0 Then
                            Read(foundRecID)
                        Else
                            Throw New Exceptions.RecordNotFoundException
                        End If
                    End Using
                End With
            Finally
                If SQLReader IsNot Nothing Then
                    SQLReader.Close()
                End If
            End Try
        End Sub


[Ernst]

OH my Golly, kept on playing and it eventually worked. Your code helped me see what was happening.

The Fieldname bit and the sortvaluemethod have to have identical fields to make it work. Works like a dream now.

Now i can extend it and develop the others they want.

Thanks for help Stuart.. Merry Christmas to you and your family..))))

[SBarnes]

Glad it's working, likewise Merry Christmas