none
Creating a report that updates according to datepicker RRS feed

  • Question

  • I recently aquired a new position and my first task is to go over a previous employee's work, understand it, document it, and recreate it.

    The problem is that I am new to Visual Studio, VB, and C#.

    I have been researching Visual Studio and understand DAL's and setting that all up. I have come to the point where I need to work on the Code-Behind. This has proven to be my biggest stumbling block. I understand what is happening in the code, but I have no idea how to slim it down to the point where all I have is the datepicker. I have included my code below, any help would be greatly appreciated.

    Imports appTools
    Imports System.Data.OleDb
    Imports System.Data
    Imports Microsoft.Reporting.WebForms
    Imports System
    Imports System.Data.SqlClient
    Imports System.Configuration
    Imports System.Collections
    Imports System.Web
    Imports System.Web.Security
    Imports System.Web.UI
    Imports System.Web.UI.WebControls
    Imports System.Web.UI.WebControls.WebParts
    Imports System.Web.UI.HtmlControls
    Imports System.Data.Odbc
    Imports System.Text.RegularExpressions

    Partial Class Reports_GreenCards
        Inherits System.Web.UI.Page
        Dim xmlDocPath As String = Server.MapPath("~/Log/XXXXXXXLog.xml")
        Dim ipPath As String = ""
        Dim sqlWhere As String
        Dim lblDates As String
        Dim EndDate As DateTime
        Dim BeginDate As DateTime
        Dim DSReport As New Data.DataSet
        Dim nds As New DataSet
        Dim DSReport2 As New Data.DataSet
        Dim DSReport3 As New Data.DataSet
        Dim sqlRptStr As String = ""
        Dim rptDataSetName As String = ""
        Dim reportPath As String = ""
        Dim Btime As Date
        Dim Etime As Date
        Dim tBDate As Date
        Dim tEDate As Date
        Dim dTBFlag As Boolean = False
        Dim dTEFlag As Boolean = False
        Dim blnIntType As Boolean = False
      

        Const qryGreenCards As String = "SELECT GreenCards.GreenCard_ID, GreenCards.CardNumber, " _
        & " Employee_Listing.Employee_L_Name + ', ' + Employee_Listing.Employee_F_Name AS Employee, " _
        & " Supervisor_Listing.Supervisor_L_Name + ', ' + Supervisor_Listing.Supervisor_F_Name AS Supervisor, " _
        & " GreenCards.Date_of_Report, Status.Status_Text, Summary_of_Observations.Summary_of_Observation, " _
        & " GreenCards.Location_ID, GreenCards.Sub_Location_ID, Sub_Locations.Sub_Location_Text, " _
        & " Locations.Location_Text, GreenCards.Status_ID, GreenCards.Employee_ID, GreenCards.Supervisor_ID, " _
        & " GreenCards.Date_Card_Was_Completed, GreenCards.Followup_Responsibility_ID, " _
        & " Employee_Listing_1.Employee_L_Name & ', ' & Employee_Listing_1.Employee_F_Name AS FollowupName, " _
        & " GreenCards.Link_CardNumber, GreenCards.Link_Status, Departments.Department_Text, Employee_Listing.Department_ID " _
        & " FROM (((((Sub_Locations RIGHT OUTER JOIN " _
        & " (Departments RIGHT OUTER JOIN " _
        & " (Employee_Listing INNER JOIN " _
        & " (GreenCards LEFT OUTER JOIN " _
        & " Summary_of_Observations ON GreenCards.GreenCard_ID = Summary_of_Observations.SOO_GreenCard_ID) ON " _
        & " Employee_Listing.Employee_ID = GreenCards.Employee_ID) ON Departments.Department_ID = Employee_Listing.Department_ID) ON " _
        & " Sub_Locations.Sub_Location_ID = GreenCards.Sub_Location_ID) LEFT OUTER JOIN " _
        & " Locations ON GreenCards.Location_ID = Locations.Location_ID) LEFT OUTER JOIN " _
        & " Supervisor_Listing ON GreenCards.Supervisor_ID = Supervisor_Listing.Supervisor_ID) LEFT OUTER JOIN " _
        & " Status ON GreenCards.Status_ID = Status.Status_ID) LEFT OUTER JOIN " _
        & " Employee_Listing Employee_Listing_1 ON GreenCards.Followup_Responsibility_ID = Employee_Listing_1.Employee_ID)"

      

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

            ipPath = Request.ServerVariables("REMOTE_ADDR")
            If Request.QueryString("ReportName") & "" <> "" Then
                Session("ReportName") = Request.QueryString("ReportName")
            Else
                Response.Redirect("~/Default.aspx")
            End If
            lblReportName.Text = Session("ReportName")

            GreenCard_Controls.Visible = False
         
            Select Case Session("ReportName")
                Case "Green Cards"
                    GreenCard_Controls.Visible = True
                 

            End Select
            If Not IsPostBack Then  'first load
                ClearAllSessionValues()
            Else                        'not first page load
                txtB_EndDate.Text = FixDateTextBox(txtB_EndDate.Text)
                txtB_BeginDate.Text = FixDateTextBox(txtB_BeginDate.Text)
                tBDate = FormatDateTime(CDate(txtB_BeginDate.Text), DateFormat.ShortDate)
                tEDate = FormatDateTime(CDate(txtB_EndDate.Text), DateFormat.ShortDate)
            End If
            Btime = Now()
            FillDatasetWithResults()
            ComputeAllValues()
            Select Case Session("ReportName")
                Case "Green Cards"
                    FillDDL(ddl_Supervisor, DSReport, "Supervisor")
                    FillDDL(ddl_Employee, DSReport, "Employee")
                    FillDDL(ddl_Status, DSReport, "Status_Text")
                    FillDDL(ddl_Location, DSReport, "Location_Text")
                    FillDDL(ddl_Dept, DSReport, "Department_Text")

            End Select

            ShowReport()
            Etime = Now()

            Dim tString As String = "This report took " & FormatNumber((Etime - Btime).TotalSeconds.ToString, 2) & " seconds to run!"
            If BeginDate = EndDate Then
                Label2.Text = Session("ReportName") & " for " & BeginDate
            Else
                Label2.Text = Session("ReportName") & " for dates " & BeginDate & " to " & EndDate
            End If
            Label3.Text = tString & " There are " & DSReport.Tables(0).Rows.Count & " records returned from this search!"
            txtB_EndDate.Text = FixDateTextBox(txtB_EndDate.Text)
            txtB_BeginDate.Text = FixDateTextBox(txtB_BeginDate.Text)
            SaveXMLData(xmlDocPath, ipPath, "Report", Label2.Text, tString)

        End Sub
        '*******************************************************************************
        '
        '*******************************************************************************
        Public Sub FillDatasetWithResults()
            dTBFlag = False
            dTEFlag = False
            Dim myConnection As New OleDbConnection(ConfigurationManager.ConnectionStrings("XXXXXXXXXXXXConnectionString").ConnectionString)
            Select Case Session("ReportName")
                Case "Green Cards"
                    sqlRptStr = qryGreenCards + Getwhere()
                    rptDataSetName = "XXXXXX"
                    reportPath = "XXXXXX\XXXXXXX\XXXXXXXX\XXXXXXXX.rdlc"

     

            End Select
            Dim da As New OleDbDataAdapter(sqlRptStr, myConnection)
            da.Fill(DSReport)


        End Sub

        '********************************************************************************
        ' change the values of all fields to reflect formula fields.
        '********************************************************************************
        Public Sub ComputeAllValues()

            Select Case Session("ReportName")
                Case "Green Cards"

             
            End Select
        End Sub
        Public Function GetSortby() As String
            Return ""

        End Function

        Public Function GetTitle(ByVal Title As String) As String
           
            Return Session("ReportName")

        End Function
        Public Sub ShowReport()

            Dim Sortby As String = GetSortby()
            'Sortby = "Fields!" & Sortby & ".value"
            Dim p As New ReportParameter("BeginDate", BeginDate)
            Dim p1 As New ReportParameter("EndDate", EndDate)
            Dim p2 As New ReportParameter("Sortby", Sortby)
            Dim p3 As New ReportParameter("Title", GetTitle(Session("ReportName")))

            Dim datasource As ReportDataSource
            ReportViewer1.ProcessingMode = ProcessingMode.Local
            ReportViewer1.Reset()
            ReportViewer1.LocalReport.ReportPath = reportPath
            If Session("ReportName") = "Green Cards" Then
                ReportViewer1.LocalReport.SetParameters(New ReportParameter() {p, p1})
            End If

            datasource = New ReportDataSource(rptDataSetName, DSReport.Tables(0))
            ReportViewer1.LocalReport.DataSources.Clear()
            ReportViewer1.LocalReport.DataSources.Add(datasource)


            ReportViewer1.LocalReport.Refresh()

        End Sub
        Function Getwhere() As String
            '**********************
            ' Create filter clause for results query by looking at all selected values on form
            '**********************  

            Dim where As String = ""
            BeginDate = FormatDateTime(CDate(txtB_BeginDate.Text), DateFormat.ShortDate)
            EndDate = FormatDateTime(CDate(txtB_EndDate.Text), DateFormat.ShortDate)
            EndDate = FixDateTextBox(EndDate)
            BeginDate = FixDateTextBox(BeginDate)
            tBDate = CDate(txtB_BeginDate.Text)
            tEDate = CDate(txtB_EndDate.Text)
            Select Case Session("ReportName")
                Case "Green Cards"
                    where = " WHERE ((GreenCards.Date_of_Report) Between #" & BeginDate & "# And #" & EndDate & "#)"
                    If ddl_Supervisor.SelectedIndex <> 0 Then
                        where = where & " AND (Supervisor_Listing.Supervisor_L_Name + ', ' + Supervisor_Listing.Supervisor_F_Name = '" & ddl_Supervisor.SelectedValue & "')"
                    End If
                    If ddl_Employee.SelectedIndex <> 0 Then
                        where = where & " AND (Employee_Listing.Employee_L_Name + ', ' + Employee_Listing.Employee_F_Name = '" & ddl_Employee.SelectedValue & "')"
                    End If
                    If ddl_Location.SelectedIndex <> 0 Then
                        where = where & " AND (Locations.Location_Text = '" & ddl_Location.SelectedValue & "')"
                    End If
                    If ddl_Status.SelectedIndex <> 0 Then
                        where = where & " AND (Status.Status_Text = '" & ddl_Status.SelectedValue & "')"
                    End If
                    where = where & " ORDER BY GreenCards.Date_of_Report"

                Case Else

            End Select
            sqlWhere = where
            Getwhere = where
            lblDates = " for dates " & BeginDate & " and " & EndDate
        End Function
        Private Function GetTxtB_ItemsWhereString(ByRef txtB As TextBox) As String
            Dim cCount As Integer = NumOccurrences(txtB.Text, ",")
            If cCount >= 1 Then FixWhiteSpace(txtB)
            'check if cCount > 0
            'Yes, then loop and add records to result
            If cCount = 0 Then
                Return " AND (ITEM.ITEMNUM = '" & txtB.Text & "') "
            Else
                Dim vArray As Object
                Dim tmpstr As String = ""
                Dim firstpass As Boolean = True
                vArray = Split(txtB.Text, ",")
                For Each itm In vArray
                    If firstpass Then
                        firstpass = False
                        tmpstr = " AND (ITEM.ITEMNUM = '" & itm & "'"
                    Else
                        tmpstr = tmpstr & " OR ITEM.ITEMNUM = '" & itm & "'"
                    End If
                Next
                tmpstr = tmpstr & ")"
                Return tmpstr
            End If

        End Function
      
        Private Sub FixWhiteSpace(ByRef tBox As TextBox)
            'loop thru all char of textbox and remove spaces.
            Dim strString, strResult, strCharacter As String
            Dim i As Integer

            strString = tBox.Text

            For i = 1 To Len(strString)
                strCharacter = Mid(strString, i, 1)
                If strCharacter <> " " Then
                    strResult = strResult & strCharacter
                End If
            Next i
            tBox.Text = strResult
        End Sub
        Function NumOccurrences(ByVal StrToCheck As String, ByVal LookFor As String) As Integer

            Dim exp As New Regex(LookFor, RegexOptions.IgnoreCase)
            Dim Count As Integer = exp.Matches(StrToCheck).Count

            Return Count
        End Function
        Public Function FixDateTextBoxforOracle(ByVal DTextbox As String) As String
            Dim dtext As String
            dtext = DTextbox
            dtext = Convert.ToDateTime(dtext).ToString("yyyy-MM-dd")
            Dim strDay, strMonth, strYear As String
            strDay = Day(dtext)
            strMonth = Month(dtext)
            strYear = Year(dtext)
            If Len(strDay) = 1 Then
                strDay = "0" + strDay
            End If
            If Len(strMonth) = 1 Then
                strMonth = "0" + strMonth
            End If
            Return strYear + strMonth + strDay

        End Function
        Public Function FixDateTextBoxforOracleED(ByVal DTextbox As String) As String
            Dim dtext As String
            dtext = DTextbox

            Dim tDate As Date = Convert.ToDateTime(DTextbox)
            tDate = tDate.AddDays(1)
            dtext = tDate.ToString

            dtext = Convert.ToDateTime(dtext).ToString("yyyy-MM-dd")
            Dim strDay, strMonth, strYear As String
            strDay = Day(dtext)
            strMonth = Month(dtext)
            strYear = Year(dtext)
            If Len(strDay) = 1 Then
                strDay = "0" + strDay
            End If
            If Len(strMonth) = 1 Then
                strMonth = "0" + strMonth
            End If
            Return strYear + strMonth + strDay

        End Function

        Public Function FixDateTextBox(ByVal DTextbox As String) As String
            Dim dtext As String
            dtext = DTextbox
            dtext = Convert.ToDateTime(dtext).ToString("yyyy-MM-dd")
            FixDateTextBox = dtext

        End Function
        Private Sub ClearAllSessionValues()
            'Clear all values
            txtB_EndDate.Text = Convert.ToDateTime(DateTime.Now.AddDays(-1)).ToString("yyyy-MM-dd")
            txtB_BeginDate.Text = Convert.ToDateTime(DateTime.Now.AddDays(-1)).ToString("yyyy-MM-dd")
            txtB_EndDate.Text = FixDateTextBox(txtB_EndDate.Text)
            txtB_BeginDate.Text = FixDateTextBox(txtB_BeginDate.Text)
            ddl_Supervisor.SelectedValue = "- Select from List -"

        End Sub

        Protected Sub ddl_Supervisor_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddl_Supervisor.DataBound
            ddl_Supervisor.Items.Insert(0, "- Select from List -")
            RemoveDuplicateDropDownList(ddl_Supervisor)
        End Sub

       
        Private Sub FillDDL(ByRef ddl As DropDownList, ByVal ds As DataSet, ByVal dsField As String)
            For Each r As Data.DataRow In ds.Tables(0).Rows  ' loop thru all rows of the table 
                If r.Item(dsField) & "" <> "" Then
                    ddl.Items.Add(r.Item(dsField))
                End If
            Next
            ddl.Items.Insert(0, "- Select from List -")
            RemoveDuplicateDropDownList(ddl)
            ddl.SelectedIndex = 0
        End Sub

        Private Function SeparateintoMonths() As DataSet
            DSReport2 = DSReport.Copy
            For Each r As Data.DataRow In DSReport2.Tables(0).Rows  ' loop thru all rows of the table
                r.Item("ACTUALDATE") = makeNewWKDAY(CDate(r.Item("ACTUALDATE"))) ' set new values            
            Next
            Return DSReport2
        End Function
        Function makeNewWKDAY(ByVal nDate As Date) As Date
            Dim thisMonth As Integer
            thisMonth = Month(CDate(nDate))

            Select Case thisMonth
                Case 1
                    makeNewWKDAY = "1/31/" & Year(nDate).ToString
                Case 2
                    If Date.IsLeapYear(nDate.Year()) Then
                        makeNewWKDAY = "2/29/" & Year(nDate).ToString
                    Else
                        makeNewWKDAY = "2/28/" & Year(nDate).ToString
                    End If
                Case 3
                    makeNewWKDAY = "3/31/" & Year(nDate).ToString
                Case 4
                    makeNewWKDAY = "4/30/" & Year(nDate).ToString
                Case 5
                    makeNewWKDAY = "5/31/" & Year(nDate).ToString
                Case 6
                    makeNewWKDAY = "6/30/" & Year(nDate).ToString
                Case 7
                    makeNewWKDAY = "7/31/" & Year(nDate).ToString
                Case 8
                    makeNewWKDAY = "8/31/" & Year(nDate).ToString
                Case 9
                    makeNewWKDAY = "9/30/" & Year(nDate).ToString
                Case 10
                    makeNewWKDAY = "10/31/" & Year(nDate).ToString
                Case 11
                    makeNewWKDAY = "11/30/" & Year(nDate).ToString
                Case 12
                    makeNewWKDAY = "12/31/" & Year(nDate).ToString
            End Select
        End Function

        Protected Sub btn_RedirectToReports_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_RedirectToReports.Click
            Response.Redirect("http://XXXXXXX.com/XXXXXX/XXXXX/default.aspx")
        End Sub
    End Class

     

    Wednesday, November 11, 2009 4:56 PM