Editing the built-in search SQL

[DannyC]

Hi guys,

Is it possible to edit the SQL used to construct search window results?
I know how I can add my custom ones to various search forms, but in this scenario I want to change the default results returned when searching.

In Purchase Orders on the lines, in Creditor Purchases on the lines, Creditor Payment on the lines, and possibly other forms the users are able to select Job Costing jobs with a status of closed. I want to stop that from happening by adding a WHERE clause to check for job status.
Whilst I could add my own SQL or my own filter, the users would still have the option just to change the query dropdown or filter dropdown to get around it, hence I think I need to change the built in SQL.

Cheers

Danny

[Scott.Pearce]

You could hook in at "Search.Showing":

Code: Select all

AddHandler JiwaApplication.Manager.Instance.Search.Showing, AddressOf Search_Showing


and do something like:

Code: Select all

Private Sub Search_Showing(sender As Object, e As System.EventArgs)
   With JiwaApplication.Manager.Instance.Search
      If .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswInventory AND .FilterNo = 12 Then 'Purchase order inventory lookup
        'Go through all searches and modify the WHERE clause
        For Each existingSearchOption As JiwaApplication.JiwaSearch.SearchOption In .Options
          'Determine if a WHERE clause already exists and append the appropriate keyword
          If InStr(LCase(existingSearchOption.SQLStr), "where") > 0 Then
            existingSearchOption.SQLStr += " AND "
          Else
            existingSearchOption.SQLStr += " WHERE "
          End If
         
          'Append your WHERE clause.  Buckle up!  This is a big one!!
          existingSearchOption.SQLStr += " PO_Lines.JB_JobStages_RecID NOT IN (SELECT RecID FROM JB_JobStages WHERE JB_JobCostCentres_RecID IN (SELECT RecID FROM JB_JobCostCentres WHERE JobID IN (SELECT JobID FROM JB_Main WHERE JB_Statuses_RecID = (SELECT RecID FROM JB_Statuses WHERE Name = 'Closed'))))"
        Next
      End If
   End With
End Sub


[DannyC]

Thanks Scott,
Would never have worked that out myself!

I have put that within the Class code block
Public Class FormPlugin

but it hasn't changed anything. Is that the correct code block?
Is the following line correct if I need to tweak the Job Costing search? Looks like it is tweaking the Inventory search.

Code: Select all

      If .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswInventory AND .FilterNo = 12 Then 'Purchase order inventory lookup

Is there a list of the .FilterNo somewhere as I need to also tweak the SQL for Creditor Purchases and Creditor Payments?

Cheers

Danny

[Scott.Pearce]

That IF statement means the code will only execute for the search screen that is used when adding lines to a purchase order. To find the correct values for other areas, simply msgbox out the .CurrentSearchMode and .FilterNo, and then alter the IF statement to suit.

[DannyC]

Yes I understand what the IF statement is doing. I can get all the available jsw search modes by using the Intellisense, but how do I get a list of the filter numbers?

And is the Public Class FormPlugin the correct code block?

[Scott.Pearce]

but how do I get a list of the filter numbers?

Msgbox it out, or run the search in question, and click the "User Settings" button (the magnifying glass with the cog on the search screen ribbon). On the User Setting screen the .FilterNo is the value for "Search Filter No."

And is the Public Class FormPlugin the correct code block?

Yes.

[DannyC]

Ahhh, the user settings!

So I have trying to get this to work since knowing how to get the FilterNo. I am pretty sure that I need to use jswJobCostingStages search mode.
I can't get it to work. I am not sure I have the

Code: Select all

AddHandler JiwaApplication.Manager.Instance.Search.Showing, AddressOf Search_Showing

in the right spot.

From the very top of the plugin, I have this:

Code: Select all

Imports JiwaFinancials.Jiwa
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Data

Public Class FormPlugin
    Inherits System.MarshalByRefObject
    Implements JiwaApplication.IJiwaFormPlugin

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

    Public Sub SetupBeforeHandlers(ByVal JiwaForm As JiwaApplication.IJiwaForm, ByVal Plugin As JiwaApplication.Plugin.Plugin) Implements JiwaApplication.IJiwaFormPlugin.SetupBeforeHandlers
    End Sub

    Public Sub Setup(ByVal JiwaForm As JiwaApplication.IJiwaForm, ByVal Plugin As JiwaApplication.Plugin.Plugin) Implements JiwaApplication.IJiwaFormPlugin.Setup
      AddHandler JiwaApplication.Manager.Instance.Search.Showing, AddressOf Search_Showing
    End Sub


    Private Sub Search_Showing(sender As Object, e As System.EventArgs)
       With JiwaApplication.Manager.Instance.Search
'          If .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswInventory And .FilterNo = 12 Then 'Purchase order inventory lookup
          If .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswJobCostingStages  And .FilterNo = 149 Then
            'Go through all searches and modify the WHERE clause
            For Each existingSearchOption As JiwaApplication.JiwaSearch.SearchOption In .Options
              'Determine if a WHERE clause already exists and append the appropriate keyword
              If InStr(LCase(existingSearchOption.SQLStr), "where") > 0 Then
                existingSearchOption.SQLStr += " AND "
              Else
                existingSearchOption.SQLStr += " WHERE "
              End If
             
              'Append your WHERE clause.  Buckle up!  This is a big one!!
'              existingSearchOption.SQLStr += " PO_Lines.JB_JobStages_RecID NOT IN (SELECT RecID FROM JB_JobStages WHERE JB_JobCostCentres_RecID IN (SELECT RecID FROM JB_JobCostCentres WHERE JobID IN (SELECT JobID FROM JB_Main WHERE JB_Statuses_RecID = (SELECT RecID FROM JB_Statuses WHERE Name = 'Closed'))))"
              existingSearchOption.SQLStr += " JB_Main.JobStatus <> 2"
            Next
          End If
       End With
    End Sub

   
End Class

Can you tell me where it's incorrect?

Cheers

[Mike.Sheen]

Can you tell me where it's incorrect?

I think it's the two If conditions you have being mutually exclusive:

Code: Select all

If .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswInventory And .FilterNo = 12 Then 'Purchase order inventory lookup
          If .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswJobCostingStages  And .FilterNo = 149 Then

The CurrentSearchMode can't be both jswInventory and jswJobCostingStages. Your code will never proceed past the second If statement.

[Scott.Pearce]

But the first IF statement is commented out.

During which search exactly are you wanting to invoke this? i.e Job Costing search (search for a job from the job screen)?

[Scott.Pearce]

Aha! You need to add the forms involved (i.e. Purchase Orders) to the Forms tab of the plugin so the plugin knows when to fire!

Or, you could place the code in ApplicationManagerPlugin. This would execute upon login, and as such your hook would be in place for any time the search screen is shown, regardless of the form calling it.