66.142.0.142/66.142.0.142.tar/66.142.0.142/rptLPRReport.aspx.vb

Text File | 2012-02-19 | 17KB | 345 lines

Imports System.Data
Imports System.Data.OleDb
Imports ceTe.DynamicPDF
Imports ceTe.DynamicPDF.PageElements
Imports System.Web.UI

Public Class rptLPRReport
    Inherits AspNetMaker7_tfpssnet

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub


    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    'Groups for header elements
    Private m_objPageHeader As Group = New Group
    'Groups for footer elements
    Private m_objPageFooter As Group = New Group

    'Page Dimensions of pages
    Private Shared s_objPageDimensions As PageDimensions = New PageDimensions(PageSize.Letter, PageOrientation.Landscape, 54.0F)
    'Current page that elements are being added to
    Private m_objCurrentPage As ceTe.DynamicPDF.Page
    'Top Y coordinate for the body of the report
    Private m_fltBodyTop As Single = 100
    'Bottom Y coordinate for the body of the report
    Private m_fltBodyBottom As Single = s_objPageDimensions.Body.Bottom - s_objPageDimensions.Body.Top
    'Current Y coordinate where elements are being added
    Private m_fltCurrentY As Single
    'Used to control the alternating background
    Private m_blnAlternateBG As Boolean

    Private tmpStartDate As String, tmpEndDate As String, tmpCount As Long, SortOption As Long, tmpEmployeeID As Long, tmpEmployeeName As String
    Private tmpLocationID As Long, tmpLocationDesc As String, tmpStatusID As Long, tmpStatusDesc As String
    Private intReqHeight As Integer, intLine1ReqHeight As Integer

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        tmpStartDate = ew_Session("tmpStartDate")
        tmpEndDate = ew_Session("tmpEndDate")
        If ew_Session("tfpssnet_Status_UserLevel") = -1 Or ew_Session("tfpssnet_Status_UserLevel") = 3 Or ew_Session("tfpssnet_Status_Userlevel") = 4 Then
            tmpEmployeeID = ew_Session("tmpEmployeeID")
            '            tmpEmployeeName = tlcGetEmployeeName(tmpEmployeeID)
            tmpEmployeeName = ew_Session("tmpEmployeeText")
        Else
            tmpEmployeeID = ew_Session("tfpssnet_Status_UserID")
            tmpEmployeeName = tlcGetEmployeeName(ew_Session("tfpssnet_Status_UserID"))
        End If
        tmpLocationID = ew_Session("tmpLocationID")
        tmpLocationDesc = ew_Session("tmpLocationDesc")
        tmpStatusID = ew_Session("tmpStatusID")
        tmpStatusDesc = ew_Session("tmpStatusDesc")

        'Create a document and set it's properties
        Dim objDocument As Document = New Document
        objDocument.Creator = "rptLPRReport.aspx"
        objDocument.Author = "Tony L. Clayton"
        objDocument.Title = "Lighting Problem Report"
        objDocument.InitialPageZoom = PageZoom.FitWidth

        'Adds elements to the header groups
        SetPageHeader()
        SetPageFooter()

        'Establises connection to the database
        Dim objconnection As OleDbConnection = GetOpenDBConn()
        Dim objData As OleDbDataReader = GetData(objconnection)

        'Builds the report
        BuildDocument(objDocument, objData)

        'Cleans up database connections
        objData.Close()
        objconnection.Close()

        'Outputs the SimpleReport to the current web page
        objDocument.DrawToWeb("rptLPRReport.pdf")
        Response.End()
    End Sub

    Private Sub SetPageHeader()
        'Adds elements to the header group
        With m_objPageHeader
            .Add(New Image(Server.MapPath("images\logo.png"), 0, 0, 0.8F))
            .Add(New Image(Server.MapPath("images\logo.png"), 570, 0, 0.8F))

            .Add(New Label("P R O D U C T I O N   S E R V I C E S   S C H E D U L I N G   S Y S T E M", 0, 0, 684, 10, Font.HelveticaBold, 8, TextAlign.Center))
            .Add(New Label("Problem Report", 0, 12, 684, 16, Font.HelveticaBold, 16, TextAlign.Center))
            .Add(New Label(ew_FormatDateTime(Now(), vbLongDate), 0, 30, 684, 12, Font.HelveticaBold, 12, TextAlign.Center))

            If tmpStartDate = "" Then
                .Add(New Label("Ending Date: " & FormatDateTime(tmpEndDate), 0, 40, 684, 9, Font.Helvetica, 9, TextAlign.Left))
            Else
                .Add(New Label("From " & FormatDateTime(tmpStartDate) & " through " & FormatDateTime(tmpEndDate), 0, 40, 684, 9, Font.Helvetica, 9, TextAlign.Left))
            End If
            If tmpEmployeeID = "0" Then
                .Add(New Label("All Employees", 0, 50, 684, 9, Font.Helvetica, 9, TextAlign.Left))
            Else
                .Add(New Label("Employee: " & tmpEmployeeName, 0, 50, 684, 9, Font.Helvetica, 9, TextAlign.Left))
            End If
            If tmpLocationID = "0" Then
                .Add(New Label("All Locations", 0, 60, 684, 9, Font.Helvetica, 9, TextAlign.Left))
            Else
                .Add(New Label("Location: " & tmpLocationDesc, 0, 60, 684, 9, Font.Helvetica, 9, TextAlign.Left))
            End If
            If tmpStatusID = "0" Then
                .Add(New Label("All Status Codes", 0, 70, 684, 9, Font.Helvetica, 9, TextAlign.Left))
            Else
                .Add(New Label("Status: " & tmpStatusDesc, 0, 70, 684, 9, Font.Helvetica, 9, TextAlign.Left))
            End If

            .Add(New Label("Fixture", 230, 76, 100, 9, Font.TimesBold, 9))
            .Add(New Label("Fixture", 305, 76, 100, 9, Font.TimesBold, 9))
            .Add(New Label("Fixture", 355, 76, 100, 9, Font.TimesBold, 9))

            .Add(New Label("Date", 1, 84, 50, 9, Font.TimesBold, 9))
            .Add(New Label("Employee", 50, 84, 100, 9, Font.TimesBold, 9))
            .Add(New Label("Location", 125, 84, 100, 9, Font.TimesBold, 9))
            .Add(New Label("Location", 230, 84, 100, 9, Font.TimesBold, 9))
            .Add(New Label("Type", 310, 84, 50, 9, Font.TimesBold, 9))
            .Add(New Label("Num", 360, 84, 50, 9, Font.TimesBold, 9))
            .Add(New Label("Channel", 390, 84, 50, 9, Font.TimesBold, 9))
            .Add(New Label("Priority", 430, 84, 50, 9, Font.TimesBold, 9))
            .Add(New Label("Short Desc", 475, 84, 75, 9, Font.TimesBold, 9))
            .Add(New Label("Status", 565, 84, 75, 9, Font.TimesBold, 9))
            .Add(New Label("Status Chngd By", 620, 84, 75, 9, Font.TimesBold, 9))

            m_objPageHeader.Add(New Line(0, 96, 684, 96))
        End With
    End Sub

    Private Sub SetPageFooter()
        'Adds elements to the footer group
        Dim objLabel As PageNumberingLabel = New PageNumberingLabel("Page %%CP%% of %%TP%%", 0, 510, 684, 8, Font.HelveticaBold, 8, TextAlign.Right)
        'objLabel.HasPageNumbering = True
        m_objPageFooter.Add(objLabel)
    End Sub

    Private Sub AddNewPage(ByVal document As Document)
        'Adds a new page to the document
        m_objCurrentPage = New ceTe.DynamicPDF.Page(s_objPageDimensions)
        m_objCurrentPage.Elements.Add(m_objPageHeader)
        m_objCurrentPage.Elements.Add(m_objPageFooter)

        'Uncomment the line below to add a layout grid to the each page
        'm_objCurrentPage.Elements.Add(New LayoutGrid)

        m_fltCurrentY = m_fltBodyTop
        m_blnAlternateBG = False

        document.Pages.Add(m_objCurrentPage)
    End Sub

    Private Sub BuildDocument(ByVal document As Document, ByVal data As OleDbDataReader)
        'Builds the PDF document with data from the DataReader
        AddNewPage(document)
        While data.Read()
            'Add current record to the document
            AddRecord(document, data)
        End While
        PrintTotals(document)
    End Sub

    Private Sub AddRecord(ByVal document As Document, ByVal data As OleDbDataReader)
        Dim tmpTemp As String
        intReqHeight = GetReqRecordHeight(data)
        intLine1ReqHeight = GetLine1RecordHeight(data)

        'Adds a new page to the document if needed
        If m_fltCurrentY > m_fltBodyBottom - 20 Then AddNewPage(document)

        'Adds alternating background to document if needed
        If m_blnAlternateBG Then
            If Not IsDBNull(data("lprDesc")) Then
                m_objCurrentPage.Elements.Add(New Rectangle(0, m_fltCurrentY, 684, (intReqHeight + 16), Grayscale.Black, New WebColor("E0E0FF"), 0.0F))
            Else
                m_objCurrentPage.Elements.Add(New Rectangle(0, m_fltCurrentY, 684, (intLine1ReqHeight + 4), Grayscale.Black, New WebColor("E0E0FF"), 0.0F))
            End If
        End If

        'Adds Textarea elements to the document with data from current record
        If Not IsDBNull(data("lprDate")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("lprDate"), 1, m_fltCurrentY, 50, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("empName")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("empName"), 50, m_fltCurrentY, 120, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("locDescription")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("locDescription"), 125, m_fltCurrentY, 100, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("floDescription")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("floDescription"), 230, m_fltCurrentY, 65, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("ftyDescription")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("ftyDescription"), 310, m_fltCurrentY, 60, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("lprFixtureNumber")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("lprFixtureNumber"), 14), 365, m_fltCurrentY, 50, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("lprChannelNumber")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("lprChannelNumber"), 14), 400, m_fltCurrentY, 50, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("lprPriority")) Then
            Select Case data("lprPriority")
                Case "1"
                    tmpTemp = "Low"
                Case "2"
                    tmpTemp = "Medium"
                Case "3"
                    tmpTemp = "High"
                Case Else
                    tmpTemp = ""
            End Select
            m_objCurrentPage.Elements.Add(New TextArea(tmpTemp, 430, m_fltCurrentY, 50, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("lprShortDesc")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("lprShortDesc"), 14), 475, m_fltCurrentY, 50, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("lstDescription")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("lstDescription"), 14), 565, m_fltCurrentY, 50, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("lprSTChangedBy")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("lprSTChangedBy"), 14), 620, m_fltCurrentY, 50, intLine1ReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("lprDesc")) Then
            m_fltCurrentY += intLine1ReqHeight + 2
            m_objCurrentPage.Elements.Add(New TextArea(data("lprDesc"), 15, m_fltCurrentY, 650, intReqHeight, Font.TimesRoman, 10))
            m_fltCurrentY += intReqHeight + 2
        End If

        tmpCount += 1

        'Toggles alternating background
        m_blnAlternateBG = Not m_blnAlternateBG
        'Increments the current Y position on the page
        m_fltCurrentY += intLine1ReqHeight + 2
    End Sub

    Function GetReqRecordHeight(ByVal data As OleDbDataReader) As Long
        Dim intRecordHeight As Integer, intNameHeight As Integer, intDescHeight As Integer
        Dim fontTNR As Font, tmpName As String
        fontTNR = Font.TimesRoman
        tmpName = SafeDBNull(data("empName"))
        intNameHeight = fontTNR.GetTextLines(tmpName.ToCharArray(), 120, 9).GetTextHeight()
        intRecordHeight = intNameHeight
        If Not IsDBNull(data("lprDesc")) Then
            intDescHeight = fontTNR.GetTextLines((data("lprDesc")).ToCharArray(), 635, 9).GetTextHeight()
            If intDescHeight > intRecordHeight Then intRecordHeight = intDescHeight
        End If
        GetReqRecordHeight = intRecordHeight
    End Function

    Function GetLine1RecordHeight(ByVal data As OleDbDataReader) As Long
        Dim intRecordHeight As Integer, intNameHeight As Integer
        Dim fontTNR As Font, tmpName As String
        fontTNR = Font.TimesRoman
        tmpName = SafeDBNull(data("empName"))
        intNameHeight = fontTNR.GetTextLines(tmpName.ToCharArray(), 120, 9).GetTextHeight()
        intRecordHeight = intNameHeight
        GetLine1RecordHeight = intRecordHeight
    End Function

    Function SafeDBNull(ByVal Value As Object) As String
        If IsDBNull(Value) Then
            Return String.Empty
        Else
            Return CType(Value, String)
        End If
    End Function

    Private Function GetOpenDBConn() As OleDbConnection
        'Creates and opens a database connection
        Dim sConnStr As String = EW_DB_CONNECTION_STRING
        Dim objConnection As New OleDbConnection(sConnStr)
        objConnection.Open()
        Return objConnection
    End Function

    Private Function GetData(ByVal connection As OleDbConnection) As OleDbDataReader
        'Creates a DataReader for the report
        Dim strSQL As String, tmpAFlag As String
        Dim objCommand As OleDbCommand = connection.CreateCommand()

        tmpAFlag = "N"
        strSQL = "SELECT tblLPR.*, tblEmployees.empName, tblLPRStatus.lstDescription, tblLocations.locDescription, tblLPRFixtureType.ftyDescription, tblLPRFixtureLocation.floDescription FROM tblLPRFixtureLocation RIGHT JOIN (tblLPRFixtureType RIGHT JOIN (tblLocations RIGHT JOIN (tblEmployees RIGHT JOIN (tblLPRStatus RIGHT JOIN tblLPR ON tblLPRStatus.lstID = tblLPR.lprSTID) ON tblEmployees.empID = tblLPR.lprEmpID) ON tblLocations.locID = tblLPR.lprLocationID) ON tblLPRFixtureType.ftyID = tblLPR.lprFixtureTypeID) ON tblLPRFixtureLocation.floID = tblLPR.lprFixtureLocationID"
        If Not IsDBNull(tmpEmployeeID) Or Not IsDBNull(tmpStartDate) Or Not IsDBNull(tmpEndDate) Or Not IsDBNull(tmpLocationID) Or Not IsDBNull(tmpStatusID) Then strSQL = strSQL & " WHERE "
        If Not IsDBNull(tmpStartDate) Or Not IsDBNull(tmpEndDate) Then
            If tmpAFlag = "Y" Then strSQL = strSQL & " AND "
            If Not IsDBNull(tmpStartDate) And tmpStartDate <> "" Then
                strSQL = strSQL & "(lprDate >= #" & tmpStartDate & "#) "
            End If
            If Not IsDBNull(tmpEndDate) And tmpEndDate <> "" Then
                If Not IsDBNull(tmpStartDate) And tmpStartDate <> "" Then
                    strSQL = strSQL & " AND "
                End If
                strSQL = strSQL & "(lprDate <= #" & tmpEndDate & "#) "
            End If
            tmpAFlag = "Y"
        End If
        If Not IsDBNull(tmpEmployeeID) And tmpEmployeeID <> 0 Then
            If tmpAFlag = "Y" Then strSQL = strSQL & " AND "
            strSQL = strSQL & "(tblLPR.lprEmpID = " & tmpEmployeeID & ") "
            tmpAFlag = "Y"
        End If
        If Not IsDBNull(tmpLocationID) And tmpLocationID <> 0 Then
            If tmpAFlag = "Y" Then strSQL = strSQL & " AND "
            strSQL = strSQL & "(tblLPR.lprLocationID = " & tmpLocationID & ") "
            tmpAFlag = "Y"
        End If
        If Not IsDBNull(tmpStatusID) And tmpStatusID <> 0 Then
            If tmpAFlag = "Y" Then strSQL = strSQL & " AND "
            strSQL = strSQL & "(tblLPR.lprSTID = " & tmpStatusID & ") "
            tmpAFlag = "Y"
        End If

        strSQL = strSQL & "ORDER BY lprPriority DESC, tblLPRFixtureLocation.floDescription, tblLPRFixtureType.ftyDescription, tblLPR.lprFixtureNumber, lprDate DESC"
        Session("strSQL") = strSQL


        objCommand.CommandText = strSQL
        Dim objDataReader As OleDbDataReader = objCommand.ExecuteReader()

        Return objDataReader
    End Function

    Sub PrintTotals(ByVal document)
        m_fltCurrentY = m_fltCurrentY + 9
        'Adds a new page if the record will not fit on the current page
        '664 is the lowest point on the page that records should appear at
        If 664 - m_fltCurrentY < (intReqHeight * 4) Then AddNewPage(Document)

        m_objCurrentPage.Elements.Add(New TextArea(FormatNumber(tmpCount, 0, -2, -2, -2) & " Record(s)", 100, m_fltCurrentY, 650, intReqHeight, Font.TimesRoman, 10))
    End Sub
End Class