'****************************************************************************************************************
'* Id Application modified by:   Date(MM/dd/yyyy)   Description													*
'* -- ------------------------   --------           -------------------------------------------	    			*
'* 01 Raśl Callupe - PACNET      16/04/2006         Creation of the class
'****************************************************************************************************************
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Imports System.Globalization

Public Class CC_QuarterlySummary

    Private myDTFI As New DateTimeFormatInfo
    Private strDateFormat As String = "dd/MM/yyyy"

    Sub New()
        myDTFI.ShortDatePattern = strDateFormat
    End Sub

    Public Function GetTableA(ByVal oQuarters As EC_Quarters) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_REPORT_QSUMM_TABLEA"
        Dim arrParams(7) As SqlParameter
        Dim dsResult As DataSet

        With oQuarters
            arrParams(0) = New SqlParameter("@Q1FECINI", SqlDbType.DateTime)
            If .Q1DateIni.Trim.Equals(String.Empty) Then
                arrParams(0).Value = DBNull.Value
            Else
                arrParams(0).Value = DateTime.Parse(.Q1DateIni, myDTFI)
            End If

            arrParams(1) = New SqlParameter("@Q1FECEND", SqlDbType.DateTime)
            If .Q1DateEnd.Trim.Equals(String.Empty) Then
                arrParams(1).Value = DBNull.Value
            Else
                arrParams(1).Value = DateTime.Parse(.Q1DateEnd, myDTFI)
            End If

            arrParams(2) = New SqlParameter("@Q2FECINI", SqlDbType.DateTime)
            If .Q2DateIni.Trim.Equals(String.Empty) Then
                arrParams(2).Value = DBNull.Value
            Else
                arrParams(2).Value = DateTime.Parse(.Q2DateIni, myDTFI)
            End If

            arrParams(3) = New SqlParameter("@Q2FECEND", SqlDbType.DateTime)
            If .Q2DateEnd.Trim.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = DateTime.Parse(.Q2DateEnd, myDTFI)
            End If

            arrParams(4) = New SqlParameter("@Q3FECINI", SqlDbType.DateTime)
            If .Q3DateIni.Trim.Equals(String.Empty) Then
                arrParams(4).Value = DBNull.Value
            Else
                arrParams(4).Value = DateTime.Parse(.Q3DateIni, myDTFI)
            End If

            arrParams(5) = New SqlParameter("@Q3FECEND", SqlDbType.DateTime)
            If .Q3DateEnd.Trim.Equals(String.Empty) Then
                arrParams(5).Value = DBNull.Value
            Else
                arrParams(5).Value = DateTime.Parse(.Q3DateEnd, myDTFI)
            End If

            arrParams(6) = New SqlParameter("@Q4FECINI", SqlDbType.DateTime)
            If .Q4DateIni.Trim.Equals(String.Empty) Then
                arrParams(6).Value = DBNull.Value
            Else
                arrParams(6).Value = DateTime.Parse(.Q4DateIni, myDTFI)
            End If

            arrParams(7) = New SqlParameter("@Q4FECEND", SqlDbType.DateTime)
            If .Q4DateEnd.Trim.Equals(String.Empty) Then
                arrParams(7).Value = DBNull.Value
            Else
                arrParams(7).Value = DateTime.Parse(.Q4DateEnd, myDTFI)
            End If

        End With

        Try
            dsResult = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)
        Catch ex As Exception
            dsResult = Nothing
        End Try

        Return dsResult
    End Function

    Public Function GetGrantTypes(ByVal oQuarters As EC_Quarters) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_REPORT_QSUMM_GRANTTYPES"
        Dim arrParams(7) As SqlParameter
        Dim dsResult As DataSet

        With oQuarters
            arrParams(0) = New SqlParameter("@Q1FECINI", SqlDbType.DateTime)
            If .Q1DateIni.Trim.Equals(String.Empty) Then
                arrParams(0).Value = DBNull.Value
            Else
                arrParams(0).Value = DateTime.Parse(.Q1DateIni, myDTFI)
            End If

            arrParams(1) = New SqlParameter("@Q1FECEND", SqlDbType.DateTime)
            If .Q1DateEnd.Trim.Equals(String.Empty) Then
                arrParams(1).Value = DBNull.Value
            Else
                arrParams(1).Value = DateTime.Parse(.Q1DateEnd, myDTFI)
            End If

            arrParams(2) = New SqlParameter("@Q2FECINI", SqlDbType.DateTime)
            If .Q2DateIni.Trim.Equals(String.Empty) Then
                arrParams(2).Value = DBNull.Value
            Else
                arrParams(2).Value = DateTime.Parse(.Q2DateIni, myDTFI)
            End If

            arrParams(3) = New SqlParameter("@Q2FECEND", SqlDbType.DateTime)
            If .Q2DateEnd.Trim.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = DateTime.Parse(.Q2DateEnd, myDTFI)
            End If

            arrParams(4) = New SqlParameter("@Q3FECINI", SqlDbType.DateTime)
            If .Q3DateIni.Trim.Equals(String.Empty) Then
                arrParams(4).Value = DBNull.Value
            Else
                arrParams(4).Value = DateTime.Parse(.Q3DateIni, myDTFI)
            End If

            arrParams(5) = New SqlParameter("@Q3FECEND", SqlDbType.DateTime)
            If .Q3DateEnd.Trim.Equals(String.Empty) Then
                arrParams(5).Value = DBNull.Value
            Else
                arrParams(5).Value = DateTime.Parse(.Q3DateEnd, myDTFI)
            End If

            arrParams(6) = New SqlParameter("@Q4FECINI", SqlDbType.DateTime)
            If .Q4DateIni.Trim.Equals(String.Empty) Then
                arrParams(6).Value = DBNull.Value
            Else
                arrParams(6).Value = DateTime.Parse(.Q4DateIni, myDTFI)
            End If

            arrParams(7) = New SqlParameter("@Q4FECEND", SqlDbType.DateTime)
            If .Q4DateEnd.Trim.Equals(String.Empty) Then
                arrParams(7).Value = DBNull.Value
            Else
                arrParams(7).Value = DateTime.Parse(.Q4DateEnd, myDTFI)
            End If

        End With

        Try
            dsResult = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)
        Catch ex As Exception
            dsResult = Nothing
        End Try

        Return dsResult
    End Function

    Public Function GetTableB(ByVal oQuarters As EC_Quarters) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_REPORT_QSUMM_TABLEB"
        Dim arrParams(7) As SqlParameter
        Dim dsResult As DataSet

        With oQuarters
            arrParams(0) = New SqlParameter("@Q1FECINI", SqlDbType.DateTime)
            If .Q1DateIni.Trim.Equals(String.Empty) Then
                arrParams(0).Value = DBNull.Value
            Else
                arrParams(0).Value = DateTime.Parse(.Q1DateIni, myDTFI)
            End If

            arrParams(1) = New SqlParameter("@Q1FECEND", SqlDbType.DateTime)
            If .Q1DateEnd.Trim.Equals(String.Empty) Then
                arrParams(1).Value = DBNull.Value
            Else
                arrParams(1).Value = DateTime.Parse(.Q1DateEnd, myDTFI)
            End If

            arrParams(2) = New SqlParameter("@Q2FECINI", SqlDbType.DateTime)
            If .Q2DateIni.Trim.Equals(String.Empty) Then
                arrParams(2).Value = DBNull.Value
            Else
                arrParams(2).Value = DateTime.Parse(.Q2DateIni, myDTFI)
            End If

            arrParams(3) = New SqlParameter("@Q2FECEND", SqlDbType.DateTime)
            If .Q2DateEnd.Trim.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = DateTime.Parse(.Q2DateEnd, myDTFI)
            End If

            arrParams(4) = New SqlParameter("@Q3FECINI", SqlDbType.DateTime)
            If .Q3DateIni.Trim.Equals(String.Empty) Then
                arrParams(4).Value = DBNull.Value
            Else
                arrParams(4).Value = DateTime.Parse(.Q3DateIni, myDTFI)
            End If

            arrParams(5) = New SqlParameter("@Q3FECEND", SqlDbType.DateTime)
            If .Q3DateEnd.Trim.Equals(String.Empty) Then
                arrParams(5).Value = DBNull.Value
            Else
                arrParams(5).Value = DateTime.Parse(.Q3DateEnd, myDTFI)
            End If

            arrParams(6) = New SqlParameter("@Q4FECINI", SqlDbType.DateTime)
            If .Q4DateIni.Trim.Equals(String.Empty) Then
                arrParams(6).Value = DBNull.Value
            Else
                arrParams(6).Value = DateTime.Parse(.Q4DateIni, myDTFI)
            End If

            arrParams(7) = New SqlParameter("@Q4FECEND", SqlDbType.DateTime)
            If .Q4DateEnd.Trim.Equals(String.Empty) Then
                arrParams(7).Value = DBNull.Value
            Else
                arrParams(7).Value = DateTime.Parse(.Q4DateEnd, myDTFI)
            End If

        End With

        Try
            dsResult = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)
        Catch ex As Exception
            dsResult = Nothing
        End Try

        Return dsResult
    End Function

    Public Function GetGCDetails(ByVal oQuarters As EC_Quarters) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_REPORT_QSUMM_GCDETAILS"
        Dim arrParams(7) As SqlParameter
        Dim dsResult As DataSet

        With oQuarters
            arrParams(0) = New SqlParameter("@Q1FECINI", SqlDbType.DateTime)
            If .Q1DateIni.Trim.Equals(String.Empty) Then
                arrParams(0).Value = DBNull.Value
            Else
                arrParams(0).Value = DateTime.Parse(.Q1DateIni, myDTFI)
            End If

            arrParams(1) = New SqlParameter("@Q1FECEND", SqlDbType.DateTime)
            If .Q1DateEnd.Trim.Equals(String.Empty) Then
                arrParams(1).Value = DBNull.Value
            Else
                arrParams(1).Value = DateTime.Parse(.Q1DateEnd, myDTFI)
            End If

            arrParams(2) = New SqlParameter("@Q2FECINI", SqlDbType.DateTime)
            If .Q2DateIni.Trim.Equals(String.Empty) Then
                arrParams(2).Value = DBNull.Value
            Else
                arrParams(2).Value = DateTime.Parse(.Q2DateIni, myDTFI)
            End If

            arrParams(3) = New SqlParameter("@Q2FECEND", SqlDbType.DateTime)
            If .Q2DateEnd.Trim.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = DateTime.Parse(.Q2DateEnd, myDTFI)
            End If

            arrParams(4) = New SqlParameter("@Q3FECINI", SqlDbType.DateTime)
            If .Q3DateIni.Trim.Equals(String.Empty) Then
                arrParams(4).Value = DBNull.Value
            Else
                arrParams(4).Value = DateTime.Parse(.Q3DateIni, myDTFI)
            End If

            arrParams(5) = New SqlParameter("@Q3FECEND", SqlDbType.DateTime)
            If .Q3DateEnd.Trim.Equals(String.Empty) Then
                arrParams(5).Value = DBNull.Value
            Else
                arrParams(5).Value = DateTime.Parse(.Q3DateEnd, myDTFI)
            End If

            arrParams(6) = New SqlParameter("@Q4FECINI", SqlDbType.DateTime)
            If .Q4DateIni.Trim.Equals(String.Empty) Then
                arrParams(6).Value = DBNull.Value
            Else
                arrParams(6).Value = DateTime.Parse(.Q4DateIni, myDTFI)
            End If

            arrParams(7) = New SqlParameter("@Q4FECEND", SqlDbType.DateTime)
            If .Q4DateEnd.Trim.Equals(String.Empty) Then
                arrParams(7).Value = DBNull.Value
            Else
                arrParams(7).Value = DateTime.Parse(.Q4DateEnd, myDTFI)
            End If

        End With

        Try
            dsResult = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)
        Catch ex As Exception
            dsResult = Nothing
        End Try

        Return dsResult
    End Function

    Public Function GetPercentageOfVisitsComp(ByVal oQuarters As EC_Quarters) As DataSet
        Dim strConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim strSPName As String = "SP_CAMS_GET_REPORT_QSUMM_VISITSCOMPLETED"
        Dim arrParams(7) As SqlParameter
        Dim dsResult As DataSet

        With oQuarters
            arrParams(0) = New SqlParameter("@Q1FECINI", SqlDbType.DateTime)
            If .Q1DateIni.Trim.Equals(String.Empty) Then
                arrParams(0).Value = DBNull.Value
            Else
                arrParams(0).Value = DateTime.Parse(.Q1DateIni, myDTFI)
            End If

            arrParams(1) = New SqlParameter("@Q1FECEND", SqlDbType.DateTime)
            If .Q1DateEnd.Trim.Equals(String.Empty) Then
                arrParams(1).Value = DBNull.Value
            Else
                arrParams(1).Value = DateTime.Parse(.Q1DateEnd, myDTFI)
            End If

            arrParams(2) = New SqlParameter("@Q2FECINI", SqlDbType.DateTime)
            If .Q2DateIni.Trim.Equals(String.Empty) Then
                arrParams(2).Value = DBNull.Value
            Else
                arrParams(2).Value = DateTime.Parse(.Q2DateIni, myDTFI)
            End If

            arrParams(3) = New SqlParameter("@Q2FECEND", SqlDbType.DateTime)
            If .Q2DateEnd.Trim.Equals(String.Empty) Then
                arrParams(3).Value = DBNull.Value
            Else
                arrParams(3).Value = DateTime.Parse(.Q2DateEnd, myDTFI)
            End If

            arrParams(4) = New SqlParameter("@Q3FECINI", SqlDbType.DateTime)
            If .Q3DateIni.Trim.Equals(String.Empty) Then
                arrParams(4).Value = DBNull.Value
            Else
                arrParams(4).Value = DateTime.Parse(.Q3DateIni, myDTFI)
            End If

            arrParams(5) = New SqlParameter("@Q3FECEND", SqlDbType.DateTime)
            If .Q3DateEnd.Trim.Equals(String.Empty) Then
                arrParams(5).Value = DBNull.Value
            Else
                arrParams(5).Value = DateTime.Parse(.Q3DateEnd, myDTFI)
            End If

            arrParams(6) = New SqlParameter("@Q4FECINI", SqlDbType.DateTime)
            If .Q4DateIni.Trim.Equals(String.Empty) Then
                arrParams(6).Value = DBNull.Value
            Else
                arrParams(6).Value = DateTime.Parse(.Q4DateIni, myDTFI)
            End If

            arrParams(7) = New SqlParameter("@Q4FECEND", SqlDbType.DateTime)
            If .Q4DateEnd.Trim.Equals(String.Empty) Then
                arrParams(7).Value = DBNull.Value
            Else
                arrParams(7).Value = DateTime.Parse(.Q4DateEnd, myDTFI)
            End If

        End With

        Try
            dsResult = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, strSPName, arrParams)
        Catch ex As Exception
            dsResult = Nothing
        End Try

        Return dsResult
    End Function

End Class

Public Class EC_Quarters
    Public Q1DateIni As String = ""
    Public Q1DateEnd As String = ""
    Public Q2DateIni As String = ""
    Public Q2DateEnd As String = ""
    Public Q3DateIni As String = ""
    Public Q3DateEnd As String = ""
    Public Q4DateIni As String = ""
    Public Q4DateEnd As String = ""
End Class