Dear Sir,
I'm Trying to use multi-criteria stockcard when filtering can be calculated in the "BLC" column via dapper .
Example : When I filter in the textbox criteria1 i.e. "A-1" Then I can do the calculation in the "BLC" column
Please Guide me
Thanks
Link video result Code
Link Sample database MS access
Imports System.Data.OleDb
Imports Dapper
Public Class Form1
Private PurchaseDetails, SaleDetails As New List(Of Detail)
Private _criteriasBindingList As New SortableBindingList(Of StockCards)()
Private bindingSource As BindingSource = Nothing
Public Function GetOledbConnectionString() As String
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Stockscard.accdb;Persist Security Info=False;"
End Function
Private Sub BtxtcodeproductIN_KeyPress(sender As Object, e As KeyPressEventArgs) Handles BtxtcodeproductIN.KeyPress
If e.KeyChar = Convert.ToChar(Keys.Enter) Then
LoadData()
End If
End Sub
Private Sub LoadData()
Using Connection = New OleDbConnection(GetOledbConnectionString())
PurchaseDetails = CType(Connection.Query(Of Detail)("SELECT * FROM Stocksin"), List(Of Detail))
SaleDetails = CType(Connection.Query(Of Detail)("SELECT * FROM Stocksout"), List(Of Detail))
End Using
Dim ps = From pd In PurchaseDetails
Where pd.CodeProduct = BtxtcodeproductIN.Text
Select
Invodate = pd.InvnoDate,
pd.CodeProduct,
pd.Criteria1,
pd.Criteria2,
pd.Criteria3,
pd.Criteria4,
pd.Criteria5,
[IN] = pd.Qty,
[OUT] = 0,
BLC = pd.Qty
Order By Invodate
Dim ss = From sd In SaleDetails
Where sd.CodeProduct = BtxtcodeproductIN.Text
Select
Invodate = sd.InvnoDate,
sd.CodeProduct,
sd.Criteria1,
sd.Criteria2,
sd.Criteria3,
sd.Criteria4,
sd.Criteria5,
[IN] = 0,
[OUT] = sd.Qty,
BLC = -sd.Qty
Order By Invodate
Dim Card_temp = ps.Union(ss).OrderBy(Function(w) w.Invodate)
Dim Card As New List(Of StockCards)
Dim RunningBalance As Integer = 0
For Each ct In Card_temp
Dim sc As New StockCards
With sc
.InvnoDate = ct.Invodate
.CodeProduct = ct.CodeProduct
.Criteria1 = ct.Criteria1
.Criteria2 = ct.Criteria2
.Criteria3 = ct.Criteria3
.Criteria4 = ct.Criteria4
.Criteria5 = ct.Criteria5
.IN = ct.IN
.OUT = ct.OUT
.BLC = RunningBalance + ct.BLC
RunningBalance = .BLC
End With
Card.Add(sc)
Next
_criteriasBindingList = New SortableBindingList(Of StockCards)(CType(Card, IList(Of StockCards)))
bindingSource = New BindingSource With {.DataSource = _criteriasBindingList}
DataGridView1.DataSource = bindingSource
End Sub
Private Sub myFilter(str1 As String, str2 As String, str3 As String, str4 As String, str5 As String)
If (String.IsNullOrEmpty(str1) AndAlso String.IsNullOrEmpty(str2) AndAlso String.IsNullOrEmpty(str3) AndAlso String.IsNullOrEmpty(str4) AndAlso String.IsNullOrEmpty(str5)) Then
BindingSource.DataSource = _criteriasBindingList
ElseIf (String.IsNullOrEmpty(str1)) Then
bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.Criteria2.ToLower().Contains(str2) AndAlso c.Criteria3.ToLower().Contains(str3) AndAlso c.Criteria4.ToLower().Contains(str4) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
ElseIf (String.IsNullOrEmpty(str2)) Then
bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.Criteria1.ToLower().Contains(str1) AndAlso c.Criteria3.ToLower().Contains(str3) AndAlso c.Criteria4.ToLower().Contains(str4) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
ElseIf (String.IsNullOrEmpty(str3)) Then
bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.Criteria1.ToLower().Contains(str1) AndAlso c.Criteria2.ToLower().Contains(str2) AndAlso c.Criteria4.ToLower().Contains(str4) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
ElseIf (String.IsNullOrEmpty(str4)) Then
bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.Criteria1.ToLower().Contains(str1) AndAlso c.Criteria2.ToLower().Contains(str2) AndAlso c.Criteria3.ToLower().Contains(str3) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
Else
bindingSource.DataSource = _criteriasBindingList.Where(Function(c) c.Criteria1.ToLower().Contains(str1) AndAlso c.Criteria2.ToLower().Contains(str2) AndAlso c.Criteria3.ToLower().Contains(str3) AndAlso c.Criteria4.ToLower().Contains(str4) AndAlso c.Criteria5.ToLower().Contains(str5)).ToList()
End If
End Sub
Private Sub txt_TextChanged(sender As Object, e As EventArgs) Handles txtCriteria1.TextChanged, txtCriteria2.TextChanged, txtCriteria3.TextChanged, txtCriteria4.TextChanged, txtCriteria5.TextChanged
Dim str1 = txtCriteria1.Text.Trim().ToLower()
Dim str2 = txtCriteria2.Text.Trim().ToLower()
Dim str3 = txtCriteria3.Text.Trim().ToLower()
Dim str4 = txtCriteria4.Text.Trim().ToLower()
Dim str5 = txtCriteria5.Text.Trim().ToLower()
myFilter(str1, str2, str3, str4, str5)
End Sub
End Class
Public Class StockCards
Public Property InvnoDate() As DateTime
Public Property CodeProduct() As String
Public Property Criteria1() As String
Public Property Criteria2() As String
Public Property Criteria3() As String
Public Property Criteria4() As String
Public Property Criteria5() As String
Public Property [IN] As Integer
Public Property [OUT] As Integer
Public Property BLC As Integer
End Class
Public Class Detail
Public Property InvnoDate() As DateTime
Public Property CodeProduct() As String
Public Property Criteria1() As String
Public Property Criteria2() As String
Public Property Criteria3() As String
Public Property Criteria4() As String
Public Property Criteria5() As String
Public Property Qty() As Integer
End Class
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
''' <summary>
''' Provides a generic collection that supports data binding and additionally supports sorting.
''' See http://msdn.microsoft.com/en-us/library/ms993236.aspx
''' If the elements are IComparable it uses that; otherwise compares the ToString()
''' </summary>
''' <typeparam name="T">The type of elements in the list.</typeparam>
Public Class SortableBindingList(Of T As Class)
Inherits BindingList(Of T)
Private _isSorted As Boolean
Private _sortDirection As ListSortDirection = ListSortDirection.Ascending
Private _sortProperty As PropertyDescriptor
''' <summary>
''' Initializes a new instance of the <see cref="SortableBindingList{T}"/> class.
''' </summary>
Public Sub New()
End Sub
''' <summary>
''' Initializes a new instance of the <see cref="SortableBindingList{T}"/> class.
''' </summary>
''' <param name="list">An <see cref="T:System.Collections.Generic.IList`1" /> of items to be contained in the <see cref="T:System.ComponentModel.BindingList`1" />.</param>
Public Sub New(ByVal list As IList(Of T))
MyBase.New(list)
End Sub
''' <summary>
''' Gets a value indicating whether the list supports sorting.
''' </summary>
Protected Overrides ReadOnly Property SupportsSortingCore As Boolean
Get
Return True
End Get
End Property
''' <summary>
''' Gets a value indicating whether the list is sorted.
''' </summary>
Protected Overrides ReadOnly Property IsSortedCore As Boolean
Get
Return _isSorted
End Get
End Property
''' <summary>
''' Gets the direction the list is sorted.
''' </summary>
Protected Overrides ReadOnly Property SortDirectionCore As ListSortDirection
Get
Return _sortDirection
End Get
End Property
''' <summary>
''' Gets the property descriptor that is used for sorting the list if sorting is implemented in a derived class; otherwise, returns null
''' </summary>
Protected Overrides ReadOnly Property SortPropertyCore As PropertyDescriptor
Get
Return _sortProperty
End Get
End Property
''' <summary>
''' Removes any sort applied with ApplySortCore if sorting is implemented
''' </summary>
Protected Overrides Sub RemoveSortCore()
_sortDirection = ListSortDirection.Ascending
_sortProperty = Nothing
_isSorted = False 'thanks Luca
End Sub
''' <summary>
''' Sorts the items if overridden in a derived class
''' </summary>
''' <param name="prop"></param>
''' <param name="direction"></param>
Protected Overrides Sub ApplySortCore(ByVal prop As PropertyDescriptor, ByVal direction As ListSortDirection)
_sortProperty = prop
_sortDirection = direction
Dim list As List(Of T) = TryCast(Items, List(Of T))
If list Is Nothing Then
Return
End If
list.Sort(AddressOf Compare)
_isSorted = True
'fire an event that the list has been changed.
OnListChanged(New ListChangedEventArgs(ListChangedType.Reset, -1))
End Sub
Private Function Compare(ByVal lhs As T, ByVal rhs As T) As Integer
Dim result = OnComparison(lhs, rhs)
'invert if descending
If _sortDirection = ListSortDirection.Descending Then
result = -result
End If
Return result
End Function
Private Function OnComparison(ByVal lhs As T, ByVal rhs As T) As Integer
Dim lhsValue As Object = If(lhs Is Nothing, Nothing, _sortProperty.GetValue(lhs))
Dim rhsValue As Object = If(rhs Is Nothing, Nothing, _sortProperty.GetValue(rhs))
If lhsValue Is Nothing Then
Return If(rhsValue Is Nothing, 0, -1) 'nulls are equal
End If
If rhsValue Is Nothing Then
Return 1 'first has value, second doesn't
End If
If TypeOf lhsValue Is IComparable Then
Return DirectCast(lhsValue, IComparable).CompareTo(rhsValue)
End If
If lhsValue.Equals(rhsValue) Then
Return 0 'both are the same
End If
'not comparable, compare ToString
Return lhsValue.ToString().CompareTo(rhsValue.ToString())
End Function
End Class
