Dim sqlstr As String
sqlstr = "select * from trackmain where " + pwhereClause + "ORDER BY datee DESC"
Dim dt As DataTable
Dim datatable1 As DataTable
Dim command As New DbCommand(sqlstr, CommandType.Text, Nothing)
datatable1 = command.ExecuteDataTable()
dt = GetCustDT()
If (datatable1.Rows.Count > 0) Then
Dim str As String()
str = (datatable1.Rows.Count / _rowCount).ToString().Split(".")
If (str.Count > 1) Then
hfPageCount.Value = Convert.ToInt32(str(0)) + 1
hflastpagecount.Value = datatable1.Rows.Count Mod _rowCount
Else
hfPageCount.Value = Convert.ToInt32(str(0))
hflastpagecount.Value = datatable1.Rows.Count Mod _rowCount
End If
If (hfCurrentPage.Value = 1) Then
hfStartIndex.Value = 0
If (hfPageCount.Value = hfCurrentPage.Value) Then
hfEndIndex.Value = hflastpagecount.Value - 1
btnNext.Enabled = False
btnPrevious.Enabled = False
Else
hfEndIndex.Value = _rowCount - 1
btnNext.Enabled = True
btnPrevious.Enabled = False
End If
Else
If (Convert.ToInt32(hfPageCount.Value) > Convert.ToInt32(hfCurrentPage.Value)) Then
hfStartIndex.Value = (hfCurrentPage.Value - 1) * _rowCount
hfEndIndex.Value = hfStartIndex.Value + _rowCount - 1
btnNext.Enabled = True
btnPrevious.Enabled = True
ElseIf (Convert.ToInt32(hfPageCount.Value) = Convert.ToInt32(hfCurrentPage.Value)) Then
hfStartIndex.Value = (hfCurrentPage.Value - 1) * _rowCount
If (Convert.ToInt32(hflastpagecount.Value) = 0) Then
hfEndIndex.Value = Convert.ToInt32(hfStartIndex.Value) + _rowCount - 1
Else
hfEndIndex.Value = Convert.ToInt32(hfStartIndex.Value) + Convert.ToInt32(hflastpagecount.Value - 1)
End If
btnNext.Enabled = False
btnPrevious.Enabled = True
End If
End If
Dim i As Integer
Dim inhouse As Decimal = 0
Dim payouts As Decimal = 0
Dim expenses As Decimal = 0
Dim net As Decimal = 0
For i = Convert.ToInt32(hfStartIndex.Value) To Convert.ToInt32(hfEndIndex.Value) 'datatable1.Rows.Count - 1
dt.Rows.Add(New String() {datatable1.Rows(i)("ID").ToString(), datatable1.Rows(i)("dayy").ToString(), datatable1.Rows(i)("datee").ToString(), datatable1.Rows(i)("Client").ToString(), datatable1.Rows(i)("fromto").ToString(), datatable1.Rows(i)("collect").ToString(), datatable1.Rows(i)("via").ToString(), datatable1.Rows(i)("gross").ToString(), datatable1.Rows(i)("inhouse").ToString(), datatable1.Rows(i)("payouts").ToString(), datatable1.Rows(i)("expenses").ToString(), datatable1.Rows(i)("Net").ToString(), datatable1.Rows(i)("reconciliated").ToString(), datatable1.Rows(i)("farm").ToString()})
SubTotal = SubTotal + (Convert.ToDecimal(datatable1.Rows(i)("gross").ToString)) 'gross
inhouse = inhouse + (Convert.ToDecimal(datatable1.Rows(i)("inhouse").ToString))
payouts = payouts + (Convert.ToDecimal(datatable1.Rows(i)("payouts").ToString))
expenses = expenses + (Convert.ToDecimal(datatable1.Rows(i)("expenses").ToString))
net = net + (Convert.ToDecimal(datatable1.Rows(i)("net").ToString))
Next
If (btnType = "next") Then
hfTotalGross.Value = Convert.ToDecimal(hfTotalGross.Value) + Convert.ToDecimal(SubTotal)
hfTotalInhouse.Value = Convert.ToDecimal(hfTotalInhouse.Value) + Convert.ToDecimal(inhouse)
hfTotalPayouts.Value = Convert.ToDecimal(hfTotalPayouts.Value) + Convert.ToDecimal(payouts)
hfTotalexpense.Value = Convert.ToDecimal(hfTotalexpense.Value) + Convert.ToDecimal(expenses)
hfTotalNet.Value = Convert.ToDecimal(hfTotalNet.Value) + Convert.ToDecimal(net)
ElseIf (btnType = "pre") Then
hfTotalGross.Value = Convert.ToDecimal(hfTotalGross.Value) - Convert.ToDecimal(hfSubGross.Value)
hfTotalInhouse.Value = Convert.ToDecimal(hfTotalInhouse.Value) - Convert.ToDecimal(hfSubInhouse.Value)
hfTotalPayouts.Value = Convert.ToDecimal(hfTotalPayouts.Value) - Convert.ToDecimal(hfSubPayouts.Value)
hfTotalexpense.Value = Convert.ToDecimal(hfTotalexpense.Value) - Convert.ToDecimal(hfSubexpense.Value)
hfTotalNet.Value = Convert.ToDecimal(hfTotalNet.Value) - Convert.ToDecimal(hfSubNet.Value)
Else
hfTotalGross.Value = Convert.ToDecimal(SubTotal)
hfTotalInhouse.Value = inhouse
hfTotalPayouts.Value = payouts
hfTotalexpense.Value = expenses
hfTotalNet.Value = net
btnPrevious.Enabled = False
End If
'hfSubVia.Value = via
hfSubGross.Value = Convert.ToDecimal(SubTotal)
hfSubInhouse.Value = inhouse
hfSubPayouts.Value = payouts
hfSubexpense.Value = expenses
hfSubNet.Value = net
dt.Rows.Add(New String() {"", "", "", "", "", "Sub Total", "", Convert.ToString(hfSubGross.Value), Convert.ToString(inhouse), Convert.ToString(payouts), Convert.ToString(expenses), Convert.ToString(net), "", ""})
dt.Rows.Add(New String() {"", "", "", "", "", "Total", "", Convert.ToString(hfTotalGross.Value), Convert.ToString(hfTotalInhouse.Value), Convert.ToString(hfTotalPayouts.Value), Convert.ToString(hfTotalexpense.Value), Convert.ToString(hfTotalNet.Value)})
' GridView1.AutoGenerateEditButton = True
GridView1.DataSource = dt
GridView1.DataBind()
Else
lblmsg.Text = "no result found"
btnNext.Enabled = False
btnPrevious.Enabled = False
btnPrint.Enabled = False
End If
End Sub
Public Function GetCustDT() As DataTable
Dim dt As New DataTable
dt.Columns.Add("Id", GetType(String))
dt.Columns.Add("Day", GetType(String))
dt.Columns.Add("Date", GetType(String))
dt.Columns.Add("Client", GetType(String))
dt.Columns.Add("From-To", GetType(String))
dt.Columns.Add("Collect", GetType(String))
dt.Columns.Add("Via", GetType(String))
dt.Columns.Add("Gross", GetType(String))
dt.Columns.Add("In-House", GetType(String))
dt.Columns.Add("Payouts", GetType(String))
dt.Columns.Add("Expenses", GetType(String))
dt.Columns.Add("Net", GetType(String))
dt.Columns.Add("Status", GetType(String))
dt.Columns.Add("Farm", GetType(String))
Return dt
End Function
Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs)
hfCurrentPage.Value = hfCurrentPage.Value - 1
btnType = "pre"
setQueryValue()
getSearchResult(txtDate, txtclient, txtfarm, txtcollect, txtvia, txtgross, txtgross2, txtNet, txtNet2, txtexpenses, txtexpenses2, txtDay, txtdate1, txtdate2)
End Sub
Protected Sub btnnext_Click(ByVal sender As Object, ByVal e As System.EventArgs)
hfCurrentPage.Value = hfCurrentPage.Value + 1
btnType = "next"
setQueryValue()
getSearchResult(txtDate, txtclient, txtfarm, txtcollect, txtvia, txtgross, txtgross2, txtNet, txtNet2, txtexpenses, txtexpenses2, txtDay, txtdate1, txtdate2)
End Sub
Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs)
' Dim GridView1 As GridView
Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
GridView1.EditIndex = e.NewEditIndex
BindData()
End Sub
Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)
Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
GridView1.PageIndex = e.NewPageIndex
'Bind data to the GridView control.
BindData()
End Sub
Private Sub BindData()
Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
GridView1.DataSource = Session("trackmain")
GridView1.DataBind()
End Sub
Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs)
Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
GridView1.EditIndex = -1
End Sub
Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
Dim GridView1 As GridView = TryCast(LoginView1.FindControl("GridView1"), GridView)
Dim dt = CType(Session("trackmain"), DataTable)
'Update the values.
Dim row = GridView1.Rows(e.RowIndex)
dt.Rows(row.DataItemIndex)("Id") = (CType((row.Cells(0).Controls(0)), TextBox)).Text
'Reset the edit index.
GridView1.EditIndex = -1
End Sub
End Class