66.142.0.142/66.142.0.142.tar/66.142.0.142/rptScheduleReport.aspx.vb

Text File | 2012-03-03 | 14KB | 284 lines

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

Public Class rptScheduleReport
    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 = 74
    '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 sDate As String, eDate As String, tmpCount As Long, SortOption As Long, sEmployeeID As Long
    Private sEmployeeName As String, lastEmpName As String
    Private tmpEventTotalMinutes As Long, tmpDayTotalMinutes As Long

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sDate = Request.QueryString("sDate")
        eDate = Request.QueryString("eDate")
        If ew_Session("tfpssnet_Status_UserLevel") = -1 Then
            sEmployeeID = Request.QueryString("sEmployeeID")
            sEmployeeName = tlcGetEmployeeName(sEmployeeID)
        Else
            sEmployeeID = ew_Session("tfpssnet_Status_UserID")
            sEmployeeName = tlcGetEmployeeName(ew_Session("tfpssnet_Status_UserID"))
        End If
        lastEmpName = "ZZZZZZZ"

        'Create a document and set it's properties
        Dim objDocument As Document = New Document
        objDocument.Creator = "rptScheduleReport.aspx"
        objDocument.Author = "Tony L. Clayton"
        objDocument.Title = "PSS Schedule 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("rptScheduleReport.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("Schedule 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))

            .Add(New Label("From " & ew_FormatDateTime(sDate, vbShortDate) & " through " & ew_FormatDateTime(eDate, vbShortDate), 0, 40, 684, 9, Font.HelveticaBold, 9, TextAlign.Left))
            If sEmployeeID = 0 Then
                .Add(New Label("For All Employees", 0, 40, 684, 9, Font.HelveticaBold, 9, TextAlign.Right))
            Else
                .Add(New Label("For Employee: " & sEmployeeName, 0, 40, 684, 9, Font.HelveticaBold, 9, TextAlign.Right))
            End If

            .Add(New Label("Date", 1, 58, 50, 9, Font.TimesBold, 9))
            .Add(New Label("Event Name", 50, 58, 120, 9, Font.TimesBold, 9))
            .Add(New Label("Part Name", 170, 58, 120, 9, Font.TimesBold, 9))
            .Add(New Label("Position", 270, 58, 120, 9, Font.TimesBold, 9))
            .Add(New Label("Status", 335, 58, 120, 9, Font.TimesBold, 9))
            .Add(New Label("Start Time", 385, 58, 120, 9, Font.TimesBold, 9))
            .Add(New Label("End Time", 435, 58, 120, 9, Font.TimesBold, 9))
            .Add(New Label("Call Time", 485, 58, 120, 9, Font.TimesBold, 9))
            .Add(New Label("Out Time", 535, 58, 120, 9, Font.TimesBold, 9))
            .Add(New Label("Actual Start", 580, 58, 120, 9, Font.TimesBold, 9))
            .Add(New Label("Actual End", 635, 58, 120, 9, Font.TimesBold, 9))

            m_objPageHeader.Add(New Line(0, 72, 684, 72))
        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
        Call PrintTotals(document)
    End Sub

    Private Sub AddRecord(ByVal document As Document, ByVal data As OleDbDataReader)
        Dim intReqHeight As Integer, tmpTime As Long, tmpHours As String, tmpMinutes As String
        intReqHeight = 9

        '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("schNotes")) Then
                m_objCurrentPage.Elements.Add(New Rectangle(0, m_fltCurrentY, 684, (intReqHeight + 4) * 2, Grayscale.Black, New WebColor("E0E0FF"), 0.0F))
            Else
                m_objCurrentPage.Elements.Add(New Rectangle(0, m_fltCurrentY, 684, (intReqHeight + 4), Grayscale.Black, New WebColor("E0E0FF"), 0.0F))
            End If
        End If

        If data("empName") <> lastEmpName Then
            If lastEmpName <> "ZZZZZZZ" Then
                m_objCurrentPage.Elements.Add(New TextArea("Total Scheduled Time: " & Int(tmpEventTotalMinutes / 60) & ":" & Int(tmpEventTotalMinutes Mod 60).ToString.PadLeft(2, "0"), 480, m_fltCurrentY, 200, intReqHeight, Font.TimesBold, 10))
                tmpEventTotalMinutes = 0
                m_fltCurrentY += intReqHeight + 5
            End If
            m_objCurrentPage.Elements.Add(New TextArea(data("empName"), 1, m_fltCurrentY, 200, intReqHeight, Font.TimesBold, 12))
            m_fltCurrentY += intReqHeight + 5
        End If
        lastEmpName = data("empName")

        'Adds Textarea elements to the document with data from current record
        If Not IsDBNull(data("schDate")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("schDate"), 1, m_fltCurrentY, 50, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("evtDescription")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("evtDescription"), 50, m_fltCurrentY, 120, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("prtDescription")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("prtDescription"), 170, m_fltCurrentY, 100, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("posDescription")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("posDescription"), 270, m_fltCurrentY, 65, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("stDescription")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("stDescription"), 335, m_fltCurrentY, 60, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("schStartTime")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("schStartTime"), 14), 395, m_fltCurrentY, 50, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("schEndTime")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("schEndTime"), 14), 445, m_fltCurrentY, 50, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("schCallTime")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("schCallTime"), 14), 495, m_fltCurrentY, 50, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("schOutTime")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("schOutTime"), 14), 545, m_fltCurrentY, 50, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("schActualStart")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("schActualStart"), 14), 595, m_fltCurrentY, 50, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("schActualEnd")) Then
            m_objCurrentPage.Elements.Add(New TextArea(ew_FormatDateTime(data("schActualEnd"), 14), 645, m_fltCurrentY, 50, intReqHeight, Font.TimesRoman, 10))
        End If
        m_fltCurrentY += intReqHeight + 5
        If Not IsDBNull(data("schNotes")) Then
            m_objCurrentPage.Elements.Add(New TextArea(data("schNotes"), 25, m_fltCurrentY, 500, intReqHeight, Font.TimesRoman, 10))
        End If
        If Not IsDBNull(data("schCallTime")) And Not IsDBNull(data("schOutTime")) Then
            tmpTime = DateDiff(DateInterval.Minute, data("schCallTime"), data("schOuttime"))
            tmpHours = Int(tmpTime / 60)
            tmpMinutes = Int(tmpTime Mod 60)
            m_objCurrentPage.Elements.Add(New TextArea("Scheduled Time: " & tmpHours & ":" & tmpMinutes.ToString.PadLeft(2, "0"), 510, m_fltCurrentY, 150, intReqHeight, Font.TimesRoman, 10))
            tmpEventTotalMinutes = tmpEventTotalMinutes + tmpMinutes + (tmpHours * 60)
        End If

        tmpCount += 1

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

    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
        Dim objCommand As OleDbCommand = connection.CreateCommand()
        strSQL = "SELECT tblSchedule.schDate, tblSchedule.schEmpID, tblEmployees.empName, tblEvents.evtDescription, " _
& "tblParts.prtDescription, tblPositions.posDescription, tblScheduleStatus.stDescription, tblSchedule.schStartTime, " _
& "tblSchedule.schEndTime, tblSchedule.schCallTime, tblSchedule.schOutTime, tblSchedule.schActualStart, " _
& "tblSchedule.schActualEnd, tblSchedule.schStatus, tblSchedule.schNotes " _
& "FROM tblScheduleStatus RIGHT JOIN (tblPositions RIGHT JOIN (tblParts RIGHT JOIN (tblEvents RIGHT JOIN " _
& "(tblEmployees INNER JOIN tblSchedule ON tblEmployees.empID = tblSchedule.schEmpID) ON tblEvents.evtID = " _
& "tblSchedule.schEvtID) ON tblParts.prtID = tblSchedule.schPartsID) ON tblPositions.posID = tblSchedule.schPosID) " _
& "ON tblScheduleStatus.stID = tblSchedule.schStatus "
        If sEmployeeID = 0 Then
            strSQL = strSQL & "WHERE "
        Else
            strSQL = strSQL & "WHERE (tblSchedule.schEmpID = " & sEmployeeID & ") AND "
        End If
        If Not IsDBNull(sDate) Or Not IsDBNull(eDate) Then
            If Not IsDBNull(sDate) Then
                strSQL = strSQL & "(schDate >= #" & sDate & "#) "
            End If
            If Not IsDBNull(eDate) Then
                If Not IsDBNull(sDate) Then
                    strSQL = strSQL & " AND "
                End If
                strSQL = strSQL & "(schDate <= #" & eDate & "#) "
            End If
        End If
        strSQL = strSQL & "ORDER BY empName, schDate, schStartTime"
        objCommand.CommandText = strSQL
        Dim objDataReader As OleDbDataReader = objCommand.ExecuteReader()

        Return objDataReader
    End Function

    Private Sub PrintTotals(ByVal document As Document)
        If lastEmpName <> "ZZZZZZZ" Then
            m_objCurrentPage.Elements.Add(New TextArea("Total Scheduled Time: " & Int(tmpEventTotalMinutes / 60) & ":" & Int(tmpEventTotalMinutes Mod 60).ToString.PadLeft(2, "0"), 480, m_fltCurrentY, 200, 10, Font.TimesBold, 10))
            tmpEventTotalMinutes = 0
        End If
    End Sub

End Class