Code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OracleClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Globalization
Partial Class plaza_dailyDeclareExcessShort
Inherits System.Web.UI.Page
Private Report1 As ReportDocument
Private OraCon2 As New OracleConnection(ConfigurationManager.AppSettings("OraCon2"))
Private sqlCon As New SqlConnection(ConfigurationManager.AppSettings("SQLCon"))
Private selectCmd, selectCmd2, selectCmd4 As String
Private selectCmd3, selectAdp As OracleDataAdapter
Private sqlCom, sqlCom2, sqlCom3, sqlCom4 As OracleCommand
Private sqlCom1 As String
Private ora As New OracleHandler
Dim ds As New DataSet
Dim value1 As String
Dim paid, unpaid, excess As Decimal
Dim sumExcess As Decimal = 0
Dim sumPaid As Decimal = 0
Dim sumUnpaid As Decimal = 0
Dim i As Integer = 0
Dim j As Integer = 0
Dim k As Integer = 0
Dim lblerror As Label
Private checkpriv As New getaccesspriv()
Private priv1, priv2, priv3, priv4, loginid As String
Private privval As Integer
Private priv As AccessPriv = New AccessPriv()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'ddlPlazano.Text = Session("plazano").ToString()
'txtCalendar1.Text = Date.Today.ToShortDateString()
If Not Page.IsPostBack Then
'chkpriv()
' Panel1.Height = ConfigurationManager.AppSettings("PnlNormalH2")
ds = ora.GetColumnsByCriteria("HQ_PLAZACONSTANT", "HQCS_PLAZAABBREVIATION, HQCS_PLAZANO", "ROWNUM < 2")
''ds = Sql.GetTableByColumns("HQ_PLAZACONSTANT", "top(1)HQCS_PLAZAABBREVIATION,HQCS_PLAZANO")
txtCalendar1.Text = Format(Now, "dd/MM/yyyy") 'Format(Now, "dd/MM/yyyy") 'Date.Today.ToShortDateString()
Session("date") = txtCalendar1.Text
'txtAbbr.Text = ds.Tables("HQ_PLAZACONSTANT").Rows(0).Item(0).ToString()
Session("plazaabbr") = ds.Tables(0).Rows(0).Item(0).ToString()
Session("noplaza") = ds.Tables(0).Rows(0).Item(1).ToString()
End If
End Sub
Sub chkpriv()
If Not Session("loginid") Is Nothing Then
loginid = Session("loginid").ToString()
priv = getaccesspriv.checkpriv(loginid, "P401")
If priv.access1 = False Then 'check page authorization
Session("progname") = "P401"
Response.Redirect("~/cannotaccess.aspx")
End If
If priv.access2 = False Then 'check report authorization
'Button1.Enabled = False
GridView3.Columns(12).Visible = False
End If
End If
End Sub
Protected Sub btnQuery_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnQuery.Click
lblerror = Master.FindControl("lblerror")
lblerror.Text = ""
Try
Session("date") = txtCalendar1.Text
Session("noplaza") = ddlPlazano.SelectedItem.Text
Session("plazaabbr") = ddlPlazano.SelectedValue
sqlCom1 = " SELECT DISTINCT HQ_CASHRECONCILEHEADER.HQRH_BADGENO, HQ_CASHRECONCILEHEADER.HQRH_SHIFT, HQ_BADGE.HQBD_BADGENAME," & _
" Case sign(HQ_CASHRECONCILEHEADER.HQRH_DISCRRECONCILE) when 1 then HQRH_DISCRRECONCILE when -1 then 0 " & _
" end as HQRH_DISCRRECONCILE ," & _
" case sign (hqrh_adjustamount) when 1 then hqrh_adjustamount " & _
" when -1 then 0 end as hqrh_adjustamount," & _
" case sign(hqrh_discradjustment) when -1 then (hqrh_discradjustment * -1) " & _
" end as hqrh_discradjustment " & _
" FROM HQ_CASHRECONCILEHEADER, HQ_BADGE WHERE HQ_CASHRECONCILEHEADER.HQRH_PLAZANO = HQ_BADGE.HQBD_PLAZANO " & _
" AND HQ_CASHRECONCILEHEADER.HQRH_BADGENO = HQ_BADGE.HQBD_BADGENO AND (HQ_BADGE.HQBD_REVISIONNUMBER = (SELECT MAX(HQBD_REVISIONNUMBER) AS EXPR1 " & _
" FROM HQ_BADGE HQ_BADGE_1 WHERE (HQBD_PLAZANO = '" & Session("noplaza") & "') AND (HQBD_BADGENO IN (SELECT DISTINCT HQRH_BADGENO " & _
" FROM HQ_CASHRECONCILEHEADER HQ_CASHRECONCILEHEADER_1 WHERE HQRH_OPERATIONALDATE =TO_DATE( '" & Session("date") & "','DD/MM/YYYY') " & _
" AND(HQRH_PLAZANO = '" & Session("noplaza") & "'))))) AND HQRH_OPERATIONALDATE =TO_DATE( '" & Session("date") & "','DD/MM/YYYY') " & _
" AND (HQ_CASHRECONCILEHEADER.HQRH_PLAZANO = '" & Session("noplaza") & "')"
SqlDataSource4.SelectCommand = sqlCom1
GridView3.DataSource = SqlDataSource4
GridView3.DataBind()
GridView3.Visible = True
Catch ex As Exception
lblerror.Text = ex.Message ' "Operational date must be in 'dd/mm/yyyy' format. "
End Try
End Sub
Protected Sub ddlPlazano_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlPlazano.SelectedIndexChanged
txtAbbr.Text = ddlPlazano.SelectedValue.ToString()
Session("plazaabbr") = txtAbbr.Text
End Sub
Protected Sub PopCalendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles PopCalendar1.SelectionChanged
'txtCalendar1.Text = PopCalendar1.SelectedDate.ToString()
txtCalendar1.Text = Format(PopCalendar1.DateValue, "dd/MM/yyyy").ToString()
Session("date") = txtCalendar1.Text
End Sub
Protected Sub GridView3_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView3.RowCommand
If e.CommandName = "select" Then
Dim index As Integer = Convert.ToInt32(e.CommandArgument)
Dim badge As String = CType(GridView3.Rows(index).FindControl("lblBadgeNo"), Label).Text
Dim oper As String = txtCalendar1.Text 'CType(GridView1.Rows(index).FindControl("lblOperationalDate"), Label).Text
Dim badgename As String = CType(GridView3.Rows(index).FindControl("lblbadgename"), Label).Text
Dim shift As String = CType(GridView3.Rows(index).FindControl("lblshift"), Label).Text
Dim discrreconcile As String = CType(GridView3.Rows(index).FindControl("lbldiscrreconcile"), Label).Text
Dim adjustamount As String = CType(GridView3.Rows(index).FindControl("lbladjustamount"), Label).Text
Dim discradjustment As String = CType(GridView3.Rows(index).FindControl("lbldiscradjustment"), Label).Text
Session("noplaza") = ddlPlazano.SelectedItem.Text
Session("plazaabb") = ddlPlazano.SelectedValue
Dim plazano As String = ddlPlazano.SelectedItem.Text
Response.Redirect("/" + ConfigurationManager.AppSettings("foldername") + "/rplaza/DailyDeclarationExcessShort.aspx?badge=" & badge & "&oper=" & oper & "&badgename=" & badgename & "&shift=" & shift & "&discrreconcile=" & discrreconcile & "&adjustamount=" & adjustamount & "&discradjustment=" & discradjustment & "&plazano=" & plazano)
End If
End Sub
Protected Sub GridView3_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView3.RowCreated
If e.Row.RowType = DataControlRowType.DataRow Then
'CType(e.Row.FindControl("LinkDetails"), LinkButton).CommandArgument = e.Row.RowIndex.ToString()
End If
End Sub
Protected Sub GridView3_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView3.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
excess = IIf((DataBinder.Eval(e.Row.DataItem, "HQRH_DISCRRECONCILE")) Is DBNull.Value, "0.00", (DataBinder.Eval(e.Row.DataItem, "HQRH_DISCRRECONCILE")))
paid = IIf((DataBinder.Eval(e.Row.DataItem, "HQRH_ADJUSTAMOUNT")) Is DBNull.Value, "0.00", (DataBinder.Eval(e.Row.DataItem, "HQRH_ADJUSTAMOUNT")))
unpaid = IIf((DataBinder.Eval(e.Row.DataItem, "HQRH_DISCRADJUSTMENT")) Is DBNull.Value, "0.00", (DataBinder.Eval(e.Row.DataItem, "HQRH_DISCRADJUSTMENT")))
sumExcess = sumExcess + excess
sumPaid = sumPaid + paid
sumUnpaid = sumUnpaid + unpaid
'CType(e.Row.Cells(2).FindControl("label45"), Label).Text = Format(excess, "0.00")
'CType(e.Row.Cells(3).FindControl("label1"), Label).Text = Format(paid, "0.00")
'CType(e.Row.Cells(3).FindControl("label5"), Label).Text = Format(unpaid, "0.00")
ElseIf e.Row.RowType = DataControlRowType.Footer Then
' if (gvFooterSample.Rows[0].Cells[1].Text == "No records found")
'e.Row.Cells[0].Text="Total No of rows: "+Convert.ToString((gvFooterSample.Rows.Count)-1);
' Else
'e.Row.Cells[0].Text = "Total No of rows: " + Convert.ToString(gvFooterSample.Rows.Count);
CType(e.Row.Cells(3).FindControl("Label46"), Label).Text = GridView3.Rows.Count.ToString
CType(e.Row.Cells(4).FindControl("Label43"), Label).Text = GridView3.Rows.Count.ToString
CType(e.Row.Cells(4).FindControl("Label22"), Label).Text = GridView3.Rows.Count.ToString
'e.Row.Cells(0).Text = "Totals:"
CType(e.Row.Cells(3).FindControl("Label47"), Label).Text = Format(sumExcess, "0.00")
CType(e.Row.Cells(4).FindControl("Label44"), Label).Text = Format(sumPaid, "0.00")
CType(e.Row.Cells(4).FindControl("Label4"), Label).Text = Format(sumUnpaid, "0.00")
End If
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnreport.Click
lblerror = Master.FindControl("lblerror")
lblerror.Text = ""
Try
Dim d1 As Date = Date.ParseExact(txtCalendar1.Text, "dd/MM/yyyy", New CultureInfo("fr-FR", True))
'txtCalendar1.Text = PopCalendar1.SelectedDate.ToString()
Session("date") = txtCalendar1.Text
Session("noplaza") = ddlPlazano.SelectedItem.Text
Session("plazaabb") = ddlPlazano.SelectedValue
'txtCalendar1.Text = ""
Response.Redirect("~/rplaza/DailyDeclarationExcessShort.aspx")
Catch
lblerror.Text = "Operational date must be in 'dd/mm/yyyy' format. "
End Try
End Sub
End Class
Hi,
i need to count the records in row data bound.
but in my db some rows have 0 values
here i no need to count the 0 or empty rows
i need to count only >0 values
for example
A B C
1 0 2
0 1 0
2 3 empty
2 2 1 -> TotalCount
like this i need.
is it possible
any one plz suggest me
Thanks in advance.