Expand|Select|Wrap|Line Numbers
- 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.