Public Function FilterClicked() As Integer
Dim startDateString As String = Nothing
Dim startDateCombo As String = ThisForm.Components(Comps.StartDateCombo).Value
If (startDateCombo <> "") Then
Dim startDateDate As Date
If (Date.TryParse(startDateCombo, startDateDate) = False) Then
Return -1
Else
startDateString = startDateDate.ToString("yyyy-MM-dd")
End If
End If
Dim endDateString As String = Nothing
Dim endDateCombo As String = ThisForm.Components(Comps.EndDateCombo).Value
If (endDateCombo <> "") Then
Dim endDateDate As Date
If (Date.TryParse(endDateCombo, endDateDate) = False) Then
Return -1
Else
endDateString = endDateDate.ToString("yyyy-MM-dd")
End If
End If
Dim sb As New Text.StringBuilder
' Date filter
If (startDateString IsNot Nothing) AndAlso (endDateString IsNot Nothing) Then
sb.AppendFormat("TransDate BETWEEN '{0}' AND '{1}'", startDateString, endDateString)
ElseIf (startDateString IsNot Nothing) Then
sb.AppendFormat("TransDate >= '{0}'", startDateString)
ElseIf (endDateString IsNot Nothing) Then
sb.AppendFormat("TransDate <= '{0}'", endDateString)
End If
' Job filter
Dim jobStart As String = ThisForm.Variables(Vars.JobStartVar).Value
Dim jobEnd As String = ThisForm.Variables(Vars.JobEndVar).Value
If (jobStart <> "") AndAlso (jobEnd <> "") Then
If (jobStart = jobEnd) Then
sb.AppendFormat(" AND Job = '{0}'", jobStart)
Dim suffixStartString As String = ThisForm.Variables(Vars.SuffixStartVar).Value
Dim suffixEndString As String = ThisForm.Variables(Vars.SuffixEndVar).Value
Dim suffixStartInteger As Integer = If(suffixStartString = "", 0, CInt(suffixStartString))
Dim suffixEndInteger As Integer = If(suffixEndString = "", 0, CInt(suffixEndString))
' integers, no single quotes!
sb.AppendFormat(" AND Suffix BETWEEN {0} AND {1}", suffixStartInteger, suffixEndInteger)
Else
sb.AppendFormat(" AND Job BETWEEN '{0}' AND '{1}'", jobStart, jobEnd)
End If
ElseIf (jobStart <> "") Then
sb.AppendFormat(" AND Job >= '{0}'", jobStart)
ElseIf (jobEnd <> "") Then
sb.AppendFormat(" AND Job <= '{0}'", jobEnd)
End If
' Item filter
Dim itemStart As String = ThisForm.Variables(Vars.ItemStartVar).Value
Dim itemEnd As String = ThisForm.Variables(Vars.ItemEndVar).Value
If (itemStart <> "" AndAlso itemEnd <> "") Then
sb.AppendFormat(" AND Item BETWEEN '{0}' AND '{1}'", itemStart, itemEnd)
ElseIf itemStart <> "" Then
sb.AppendFormat(" AND Item >= '{0}'", itemStart)
ElseIf itemEnd <> "" Then
sb.AppendFormat(" AND Item <= '{0}'", itemEnd)
End If
' Project filter
Dim projNumStart As String = ThisForm.Variables(Vars.ProjNumStartVar).Value
Dim projNumEnd As String = ThisForm.Variables(Vars.ProjNumEndVar).Value
If (projNumStart <> "" AndAlso projNumEnd <> "") Then
sb.AppendFormat(" AND ProjNum BETWEEN '{0}' AND '{1}'", projNumStart, projNumEnd)
ElseIf projNumStart <> "" Then
sb.AppendFormat(" AND ProjNum >= '{0}'", projNumStart)
ElseIf projNumEnd <> "" Then
sb.AppendFormat(" AND ProjNum <= '{0}'", projNumEnd)
End If
' TaskNum filter...
If (projNumStart = projNumEnd) Then
Dim taskNumStart As String = ThisForm.Variables(Vars.TaskNumStartVar).Value
Dim taskNumEnd As String = ThisForm.Variables(Vars.TaskNumEndVar).Value
' TaskNum type INT, no single quotes needed
If (taskNumStart <> "" AndAlso taskNumEnd <> "") Then
sb.AppendFormat(" AND TaskNum BETWEEN {0} AND {1}", taskNumStart, taskNumEnd)
ElseIf taskNumStart <> "" Then
sb.AppendFormat(" AND TaskNum >= {0}", taskNumStart)
ElseIf taskNumEnd <> "" Then
sb.AppendFormat(" AND TaskNum <= {0}", taskNumEnd)
End If
End If
' WorkCenter filter...
Dim wcStart As String = ThisForm.Variables(Vars.WorkCenterStartVar).Value
Dim wcEnd As String = ThisForm.Variables(Vars.WorkCenterEndVar).Value
If (wcStart <> "" AndAlso wcEnd <> "") Then
sb.AppendFormat(" AND Wc BETWEEN '{0}' AND '{1}'", wcStart, wcEnd)
ElseIf wcStart <> "" Then
sb.AppendFormat(" AND Wc >= '{0}'", wcStart)
ElseIf wcEnd <> "" Then
sb.AppendFormat(" AND Wc <= '{0}'", wcEnd)
End If
' CostCode filter...
Dim costCodeStart As String = ThisForm.Variables(Vars.CostCodeStartVar).Value
Dim costCodeEnd As String = ThisForm.Variables(Vars.CostCodeEndVar).Value
If costCodeStart <> "" AndAlso costCodeEnd <> "" Then
sb.AppendFormat(" AND CostCode BETWEEN '{0}' AND '{1}'", costCodeStart, costCodeEnd)
ElseIf costCodeStart <> "" Then
sb.AppendFormat(" AND CostCode >= '{0}'", costCodeStart)
ElseIf costCodeEnd <> "" Then
sb.AppendFormat(" AND CostCode <= '{0}'", costCodeEnd)
End If
' Shift filter...
Dim shiftStart As String = ThisForm.Variables(Vars.ShiftStartVar).Value
Dim shiftEnd As String = ThisForm.Variables(Vars.ShiftEndVar).Value
If (shiftStart <> "" AndAlso shiftEnd <> "") Then
sb.AppendFormat(" AND Shift BETWEEN '{0}' AND '{1}'", shiftStart, shiftEnd)
ElseIf shiftStart <> "" Then
sb.AppendFormat(" AND Shift >= '{0}'", shiftStart)
ElseIf shiftEnd <> "" Then
sb.AppendFormat(" AND Shift <= '{0}'", shiftEnd)
End If
' Indirect filter...
Dim indirectStart As String = ThisForm.Variables(Vars.IndCodeStartVar).Value
Dim indirectEnd As String = ThisForm.Variables(Vars.IndCodeEndVar).Value
If (indirectStart <> "" AndAlso indirectEnd <> "") Then
sb.AppendFormat(" AND IndCode BETWEEN '{0}' AND '{1}'", indirectStart, indirectEnd)
ElseIf indirectStart <> "" Then
sb.AppendFormat(" AND IndCode >= '{0}'", indirectStart)
ElseIf indirectEnd <> "" Then
sb.AppendFormat(" AND IndCode <= '{0}'", indirectEnd)
End If
' Deptartment filter...
Dim deptStart As String = ThisForm.Variables(Vars.DeptStartVar).Value
Dim deptEnd As String = ThisForm.Variables(Vars.DeptEndVar).Value
If (deptStart <> "" AndAlso deptEnd <> "") Then
sb.AppendFormat(" AND Dept BETWEEN '{0}' AND '{1}'", deptStart, deptEnd)
ElseIf deptStart <> "" Then
sb.AppendFormat(" AND Dept >= '{0}'", deptStart)
ElseIf deptEnd <> "" Then
sb.AppendFormat(" AND Dept <= '{0}'", deptEnd)
End If
ThisForm.Variables(Vars.SSSWBSuperGridDataViewFilter).Value = sb.ToString
ThisForm.Variables(Vars.SSSWBSuperGridMode).Value = "View"
ThisForm.Variables(Vars.SSSWBSuperGridLayoutName).Value = ""
Return 0
End Function
Comments