DynamicForms_ItemType.vb

Imports System.Collections.Generic

Namespace DotNetNuke.Modules.DynamicForms

    Public Class DynamicForms_DAL

        Public Shared Function GetForms() As IDataReader

            Dim mySqlString As New StringBuilder()

            mySqlString.Append("SELECT FormID, FormName ")

            mySqlString.Append("FROM {databaseOwner}{objectQualifier}DynamicForms_Forms ")

            mySqlString.Append("ORDER BY FormName")

            Return CType(DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), Nothing), IDataReader)

        End Function

        Public Shared Function GetControlName(ByVal FormID As Integer) As String

            Dim strControlName As String = ""

            Dim mySqlString As New StringBuilder()

            mySqlString.Append("SELECT ControlName ")

            mySqlString.Append("FROM {databaseOwner}{objectQualifier}DynamicForms_Forms ")

            mySqlString.Append("WHERE FormID = @FormID ")

            Dim myParam As SqlParameter = New SqlParameter("@FormID", SqlDbType.Int, 4)

            myParam.Value = FormID

            Using dr As IDataReader = CType(DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), myParam), IDataReader)

                While dr.Read

                    strControlName = Convert.ToString(dr("ControlName"))

                End While

            End Using

            Return strControlName

        End Function

        Public Shared Function GetFormRecords(ByVal RecordID As Integer) As List(Of ItemType)

            Dim colItemType As List(Of ItemType) = New List(Of ItemType)

            Dim mySqlString As New StringBuilder()

            mySqlString.Append("SELECT FieldID, FieldValue ")

            mySqlString.Append("FROM {databaseOwner}{objectQualifier}DynamicForms_RecordData ")

            mySqlString.Append("WHERE RecordID = @RecordID ")

            mySqlString.Append("UNION ALL SELECT FieldID, FieldValue ")

            mySqlString.Append("FROM {databaseOwner}{objectQualifier}DynamicForms_RecordData_Large ")

            mySqlString.Append("WHERE RecordID = @RecordID ")

            Dim parmRecordID As SqlParameter = New SqlParameter("@RecordID", SqlDbType.Int, 4)

            parmRecordID.Value = RecordID

            Using dr As IDataReader = CType(DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmRecordID), IDataReader)

                While dr.Read

                    Dim objItemType As New ItemType()

                    objItemType.RecordID = RecordID

                    objItemType.ItemName = Convert.ToString(dr("FieldID"))

                    objItemType.ItemValue = Convert.ToString(dr("FieldValue"))

                    colItemType.Add(objItemType)

                End While

            End Using

            Return colItemType

        End Function

        Public Shared Function GetRecords(ByVal PortalID As Integer, ByVal intPage As Integer) As RecordData

            Dim objRecordData As New RecordData()

            Dim mySqlString As New StringBuilder()

            mySqlString.Append("SELECT RowNumber, RecordID, RecordName ")

            mySqlString.Append("FROM ")

            mySqlString.Append("(SELECT RecordID, RecordName, ROW_NUMBER() OVER(ORDER BY RecordID) AS RowNumber ")

            mySqlString.Append("From {databaseOwner}{objectQualifier}DynamicForms_Records ")

            mySqlString.Append("WHERE PortalID = @PortalID ")

            mySqlString.Append(") AS RecordsWithRowNumber ")

            mySqlString.Append("WHERE RowNumber = @intPage ")

            Dim myParam As SqlParameter = New SqlParameter("@PortalID", SqlDbType.Int, 4)

            Dim myParam2 As SqlParameter = New SqlParameter("@intPage", SqlDbType.Int, 4)

            myParam.Value = PortalID

            myParam2.Value = intPage

            Using dr As IDataReader = CType(DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), myParam, myParam2), IDataReader)

                While dr.Read

                    objRecordData.RecordID = Convert.ToInt32(dr("RecordID"))

                    objRecordData.RecordName = Convert.ToString(dr("RecordName"))

                End While

            End Using

            mySqlString = New StringBuilder()

            mySqlString.Append("SELECT count(*) as TotalRecords ")

            mySqlString.Append("From {databaseOwner}{objectQualifier}DynamicForms_Records ")

            mySqlString.Append("WHERE PortalID = @PortalID ")

            Using dr As IDataReader = CType(DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), myParam), IDataReader)

                While dr.Read

                    objRecordData.TotalRecords = Convert.ToInt32(dr("TotalRecords"))

                End While

            End Using

            objRecordData.CurrentRecord = intPage

            objRecordData.IsFirstRecord = (intPage = 1)

            objRecordData.IsLastRecord = (intPage = objRecordData.TotalRecords)

            Return objRecordData

        End Function

        Public Shared Sub DeleteExistingData(ByVal RecordID As Integer, ByVal FormID As Integer)

            Dim parmRecordID As SqlParameter = New SqlParameter("@RecordID", SqlDbType.Int, 4)

            Dim parmFormID As SqlParameter = New SqlParameter("@FormID", SqlDbType.Int, 4)

            parmRecordID.Value = RecordID

            parmFormID.Value = FormID

            Dim mySqlString As New StringBuilder()

            mySqlString.Append("DELETE FROM {databaseOwner}{objectQualifier}DynamicForms_RecordData ")

            mySqlString.Append("WHERE ")

            mySqlString.Append("(RecordID = @RecordID) ")

            mySqlString.Append("AND FieldID IN ")

            mySqlString.Append("(SELECT FieldID FROM DynamicForms_FormData WHERE (RecordID = @RecordID) AND (FormID = @FormID))")

            DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmRecordID, parmFormID)

            mySqlString = New StringBuilder()

            mySqlString.Append("DELETE FROM {databaseOwner}{objectQualifier}DynamicForms_RecordData_Large ")

            mySqlString.Append("WHERE ")

            mySqlString.Append("(RecordID = @RecordID) ")

            mySqlString.Append("AND FieldID IN ")

            mySqlString.Append("(SELECT FieldID FROM DynamicForms_FormData WHERE (RecordID = @RecordID) AND (FormID = @FormID))")

            DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmRecordID, parmFormID)

            mySqlString = New StringBuilder()

            mySqlString.Append("DELETE FROM {databaseOwner}{objectQualifier}DynamicForms_FormData ")

            mySqlString.Append("WHERE ")

            mySqlString.Append("(RecordID = @RecordID) ")

            mySqlString.Append("AND ")

            mySqlString.Append("(FormID = @FormID) ")

            DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmRecordID, parmFormID)

        End Sub

        Public Shared Sub AddRecordData(ByVal objItemType As ItemType)

            Dim mySqlString As New StringBuilder()

            Dim parmFieldValue As SqlParameter

            Dim parmRecordID As SqlParameter = New SqlParameter("@RecordID", SqlDbType.Int, 4)

            Dim parmFieldID As SqlParameter = New SqlParameter("@FieldID", SqlDbType.NVarChar, 50)

            parmRecordID.Value = objItemType.RecordID

            parmFieldID.Value = Left(objItemType.ItemName, 50)

            If objItemType.LargeText Then

                mySqlString.Append("INSERT INTO {databaseOwner}{objectQualifier}DynamicForms_RecordData_Large ")

                parmFieldValue = New SqlParameter("@FieldValue", SqlDbType.Text)

                parmFieldValue.Value = objItemType.ItemValue

            Else

                mySqlString.Append("INSERT INTO {databaseOwner}{objectQualifier}DynamicForms_RecordData ")

                parmFieldValue = New SqlParameter("@FieldValue", SqlDbType.NVarChar, 500)

                parmFieldValue.Value = Left(objItemType.ItemValue, 500)

            End If

            mySqlString.Append("(RecordID, FieldID, FieldValue) ")

            mySqlString.Append(" VALUES (@RecordID, @FieldID, @FieldValue) ")

            DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmRecordID, parmFieldID, parmFieldValue)

        End Sub

        Public Shared Sub AddFormData(ByVal objItemType As ItemType)

            Dim mySqlString As New StringBuilder()

            mySqlString.Append("INSERT INTO {databaseOwner}{objectQualifier}DynamicForms_FormData ")

            mySqlString.Append("(RecordID, FormID, FieldID, FieldType) ")

            mySqlString.Append(" VALUES (@RecordID, @FormID, @FieldID, @FieldType) ")

            Dim parmRecordID As SqlParameter = New SqlParameter("@RecordID", SqlDbType.Int, 4)

            Dim parmFormID As SqlParameter = New SqlParameter("@FormID", SqlDbType.Int, 4)

            Dim parmFieldID As SqlParameter = New SqlParameter("@FieldID", SqlDbType.NVarChar, 50)

            Dim parmFieldType As SqlParameter = New SqlParameter("@FieldType", SqlDbType.NVarChar, 50)

            parmRecordID.Value = objItemType.RecordID

            parmFormID.Value = objItemType.FormID

            parmFieldID.Value = Left(objItemType.ItemName, 50)

            parmFieldType.Value = Left(objItemType.ItemTypeName, 50)

            DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmRecordID, parmFormID, parmFieldID, parmFieldType)

        End Sub

        Public Shared Function AddNewRecord(ByVal PortalID As Integer) As Integer

            Dim i As Integer = 0

            Dim mySqlString As New StringBuilder()

            mySqlString.Append("INSERT INTO {databaseOwner}{objectQualifier}DynamicForms_Records ")

            mySqlString.Append("(PortalID, RecordName) ")

            mySqlString.Append(" VALUES (@PortalID,'');Select SCOPE_IDENTITY() as myIdentity ")

            Dim parmPortalID As SqlParameter = New SqlParameter("@PortalID", SqlDbType.Int, 4)

            parmPortalID.Value = PortalID

            Using dr As IDataReader = CType(DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmPortalID), IDataReader)

                While dr.Read

                    i = Convert.ToInt32(dr.Item("myIdentity"))

                End While

            End Using

            Return i

        End Function

    End Class

End Namespace