Imports EDSL.Entities
Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.data
Imports System.Configuration
Imports System.Data.Common
Imports System.Text.RegularExpressions

Public Class dbMainCaseStudy
    Private Shared strMainConnString As String = "MainConnString"
    Public Function GetAll(ByVal strWHEREclause As String, ByVal strORDERBYclause As String, Optional ByVal intLimit As Integer = 0) As List(Of entMainCaseStudy)
        Dim objCaseStudy As New List(Of entMainCaseStudy)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudyGetAll")
                db.AddInParameter(dbCommand, "WHEREclause", DbType.String, strWHEREclause)
                db.AddInParameter(dbCommand, "ORDERBYclause", DbType.String, strORDERBYclause)
                If intLimit > 0 Then
                    db.AddInParameter(dbCommand, "LIMIT", DbType.String, " LIMIT " & intLimit)
                Else
                    db.AddInParameter(dbCommand, "LIMIT", DbType.String, "")
                End If


                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim strCaseStudyDate As String
                    If Convert.ToString(oRecordset("CaseStudyDate")) = "" Then
                        strCaseStudyDate = Nothing
                    Else
                        strCaseStudyDate = FormatDateTime(Convert.ToString(oRecordset("CaseStudyDate")), DateFormat.ShortDate)
                    End If
                    Dim RegExStr As String = "<[^>]*>"
                    Dim R As New Regex(RegExStr)
                    Dim caseSummary As String = R.Replace(Convert.ToString(oRecordset("CaseStudySummary")), " ")
                    objCaseStudy.Add(New entMainCaseStudy() With { _
                      .CaseStudyID = Convert.ToInt32(oRecordset("CaseStudyID")), _
                      .CaseStudyHeadline = Convert.ToString(oRecordset("CaseStudyHeadline")), _
                      .CaseStudySummary = caseSummary, _
                      .CaseStudyText = Convert.ToString(oRecordset("CaseStudyText")), _
                      .CaseStudyDate = strCaseStudyDate, _
                      .ThumbImage = Convert.ToString(oRecordset("CaseStudyThumbImage")), _
                      .MainImage = Convert.ToString(oRecordset("CaseStudyMainImage")), _
                      .Testimonial = Convert.ToString(oRecordset("Testimonial")), _
                      .TestimonialSignature = Convert.ToString(oRecordset("TestimonialSignature")), _
                      .Active = Convert.ToString(oRecordset("Active")), _
                      .Deleted = Convert.ToString(oRecordset("Deleted")), _
                      .isCaseStudy = True, _
                      .CreatedDate = FormatDateTime(Convert.ToString(oRecordset("CreatedDate")), DateFormat.ShortDate) _
                    })
                End While
                oRecordset.Close()
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return objCaseStudy
    End Function
    ''' <summary>
    ''' Get All Case Studies Related to a page
    ''' </summary>
    ''' <param name="caseStudyPagesID"></param>
    ''' <returns>A list of case studies</returns>
    ''' <remarks></remarks>
    Public Function GetCaseStudiesByCaseStudyPage(ByVal caseStudyPagesID As Integer) As List(Of entMainCaseStudy)
        Dim caseStudies As New List(Of entMainCaseStudy)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainGetCaseStudy_CaseStudyPages")
                db.AddInParameter(dbCommand, "CaseStudyPagesID", DbType.Int32, caseStudyPagesID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    Dim caseStudy As New entMainCaseStudy
                    Dim strCaseStudyDate As String
                    With caseStudy
                        If Convert.ToString(oRecordset("CaseStudyDate")) = "" Then
                            strCaseStudyDate = Nothing
                        Else
                            strCaseStudyDate = FormatDateTime(Convert.ToString(oRecordset("CaseStudyDate")), DateFormat.ShortDate)
                        End If
                        .CaseStudyID = Convert.ToInt32(oRecordset("CaseStudyID"))
                        .CaseStudyHeadline = Convert.ToString(oRecordset("CaseStudyHeadline"))
                        .CaseStudySummary = Convert.ToString(oRecordset("CaseStudySummary"))
                        .CaseStudyText = Convert.ToString(oRecordset("CaseStudyText"))
                        .CaseStudyDate = strCaseStudyDate
                        .ThumbImage = Convert.ToString(oRecordset("CaseStudyThumbImage"))
                        .MainImage = Convert.ToString(oRecordset("CaseStudyMainImage"))
                        .Testimonial = Convert.ToString(oRecordset("Testimonial"))
                        .TestimonialSignature = Convert.ToString(oRecordset("TestimonialSignature"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .isCaseStudy = True
                        .CreatedDate = FormatDateTime(Convert.ToString(oRecordset("CreatedDate")), DateFormat.ShortDate)
                    End With
                    caseStudies.Add(caseStudy)
                End While
                oRecordset.Close()
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return caseStudies
    End Function
    Public Function GetItem(ByVal intCaseStudyID As Integer) As entMainCaseStudy
        Dim objCaseStudy As New entMainCaseStudy
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudyGet")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intCaseStudyID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    With objCaseStudy
                        Dim strCaseStudyDate As String
                        If Convert.ToString(oRecordset("CaseStudyDate")) = "" Then
                            strCaseStudyDate = Nothing
                        Else
                            strCaseStudyDate = FormatDateTime(Convert.ToString(oRecordset("CaseStudyDate")), DateFormat.ShortDate)
                        End If
                        .CaseStudyID = Convert.ToInt32(oRecordset("CaseStudyID"))
                        .CaseStudyHeadline = Convert.ToString(oRecordset("CaseStudyHeadline"))
                        .CaseStudySummary = Convert.ToString(oRecordset("CaseStudySummary"))
                        .CaseStudyText = Convert.ToString(oRecordset("CaseStudyText"))
                        .CaseStudyDate = strCaseStudyDate
                        .ThumbImage = Convert.ToString(oRecordset("CaseStudyThumbImage"))
                        .MainImage = Convert.ToString(oRecordset("CaseStudyMainImage"))
                        .Testimonial = Convert.ToString(oRecordset("Testimonial"))
                        .TestimonialSignature = Convert.ToString(oRecordset("TestimonialSignature"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .CreatedDate = FormatDateTime(Convert.ToString(oRecordset("CreatedDate")), DateFormat.ShortDate)
                        .CaseStudyType = Convert.ToString(oRecordset("CaseStudyType"))
                        .isCaseStudy = True
                    End With
                End While
                oRecordset.Close()
            End Using
            Return objCaseStudy
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
    Public Function DeleteItem(ByVal intCaseStudyID As Integer, ByVal intUserID As Integer) As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudyDelete")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intCaseStudyID)
                db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    'Public Function SaveItem(Byval intCaseStudyID As Integer, Byval strHeadline As String, Byval strCaseStudySummary As String, Byval strCaseStudytext As String, Byval strDate As String, Byval strThumbnail As String, Byval strMainImage As String, Byval intActive As integer, ByVal intUserID As integer) As Integer
    '  Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
    '  Dim strSPName As String = ""
    '  Try
    '    If intCaseStudyID = 0 then
    '      strSPName = "MainAdminCaseStudyINSERT"
    '    Else
    '      strSPName = "MainAdminCaseStudyUPDATE"
    '    End If
    '    Using dbCommand As DbCommand = db.GetStoredProcCommand(strSPName)
    '      If intCaseStudyID <> 0 then
    '        db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intCaseStudyID)
    '      End If
    '      db.AddInParameter(dbCommand, "CaseStudyHeadline", DbType.String, strHeadline)
    '      db.AddInParameter(dbCommand, "CaseStudySummary", DbType.String, strCaseStudySummary)
    '      db.AddInParameter(dbCommand, "CaseStudyText", DbType.String, strCaseStudytext)
    '      If strDate = "" Then
    '        db.AddInParameter(dbCommand, "CaseStudyDate", DbType.DateTime, DBNull.Value)
    '      else
    '        db.AddInParameter(dbCommand, "CaseStudyDate", DbType.DateTime, strDate)
    '      End If
    '      db.AddInParameter(dbCommand, "ThumbImage", DbType.String, strThumbnail)
    '      db.AddInParameter(dbCommand, "MainImage", DbType.String, strMainImage)
    '      db.AddInParameter(dbCommand, "Active", DbType.Int16, intActive)
    '      db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
    '      db.ExecuteNonQuery(dbCommand)
    '    End Using
    '    Return 0
    '  Catch ex As Exception
    '    Return 1
    '  End Try
    'End Function
    Public Function SaveItemPage1(ByVal intCaseStudyID As Integer, ByVal strHeadline As String, ByVal strCaseStudySummary As String, ByVal strDate As String, ByVal intActive As Integer, ByVal intUserID As Integer, ByVal caseStudyType As Integer) As Integer
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim strSPName As String = ""
        ' Try
        If intCaseStudyID = 0 Then
            strSPName = "MainAdminCaseStudyINSERT1"
        Else
            strSPName = "MainAdminCaseStudyUPDATE1"
        End If
        Using dbCommand As DbCommand = db.GetStoredProcCommand(strSPName)
            If intCaseStudyID <> 0 Then
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intCaseStudyID)
            End If
            db.AddInParameter(dbCommand, "CaseStudyHeadline", DbType.String, strHeadline)
            db.AddInParameter(dbCommand, "CaseStudySummary", DbType.String, strCaseStudySummary)
            If strDate = "" Then
                db.AddInParameter(dbCommand, "CaseStudyDate", DbType.DateTime, DBNull.Value)
            Else
                db.AddInParameter(dbCommand, "CaseStudyDate", DbType.DateTime, strDate)
            End If
            db.AddInParameter(dbCommand, "Active", DbType.Int16, intActive)
            db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
            db.AddInParameter(dbCommand, "CaseStudyType", DbType.Int32, caseStudyType)
            db.AddInParameter(dbCommand, "Thumbimage", DbType.String, "")
            db.AddInParameter(dbCommand, "mainimage", DbType.String, "")
            If intCaseStudyID = 0 Then
                db.AddOutParameter(dbCommand, "NewID", DbType.Int16, 255)
            End If
            db.ExecuteNonQuery(dbCommand)

            If intCaseStudyID = 0 Then
                Dim strNewID As String = db.GetParameterValue(dbCommand, "NewID")
                Return strNewID  '@@IDENTITY
            Else
                Return intCaseStudyID
            End If
        End Using
        'Catch ex As Exception
        Return 0
        'End Try
    End Function
    Public Function SaveItemPage1Categories(ByVal intCaseStudyID As Integer, ByVal intUserID As Integer) As Integer
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim intCount As Integer
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudyUPDATE1CategoriesDELETE")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intCaseStudyID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            'For intCount = 0 To objSelectorBoxSelected.Count - 1
            '    Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudyUPDATE1CategoriesINSERT")
            '        db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intCaseStudyID)
            '        db.AddInParameter(dbCommand, "CaseStudyPagesID", DbType.Int16, 0
            '        db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
            '        db.ExecuteNonQuery(dbCommand)
            '    End Using
            'Next intCount
            Return 1
        Catch ex As Exception
            Return 0
        End Try
    End Function
    Public Function SaveItemPage2(ByVal intCaseStudyID As Integer, ByVal strCaseStudyText As String, ByVal testimonial As String, ByVal testimonialSignature As String, ByVal intUserID As Integer) As Integer
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim strSPName As String = ""
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudyUPDATE2")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intCaseStudyID)
                db.AddInParameter(dbCommand, "CaseStudyText", DbType.String, strCaseStudyText)
                db.AddInParameter(dbCommand, "Testimonial", DbType.String, testimonial)
                db.AddInParameter(dbCommand, "TestimonialSignature", DbType.String, testimonialSignature)
                db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return 1
        Catch ex As Exception
            Return 0
        End Try
    End Function
    Public Function SaveItemPage3(ByVal intCaseStudyID As Integer, ByVal strMainImage As String, ByVal strThumbnail As String, ByVal intUserID As Integer) As Integer
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Dim strSPName As String = ""
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainAdminCaseStudyUPDATE3")
                db.AddInParameter(dbCommand, "CaseStudyID", DbType.Int16, intCaseStudyID)
                db.AddInParameter(dbCommand, "MainImage", DbType.String, strMainImage)
                db.AddInParameter(dbCommand, "ThumbImage", DbType.String, strThumbnail)
                db.AddInParameter(dbCommand, "UserID", DbType.Int16, intUserID)
                db.ExecuteNonQuery(dbCommand)
            End Using
            Return 1
        Catch ex As Exception
            Return 0
        End Try
    End Function
    Public Function GetItemSideBar(ByVal caseStudyType As Integer) As entMainCaseStudy
        Dim objCaseStudy As New entMainCaseStudy
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainCaseStudyGetByType")
                db.AddInParameter(dbCommand, "CaseStudyType", DbType.Int32, caseStudyType)

                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    With objCaseStudy
                        Dim strCaseStudyDate As String
                        If Convert.ToString(oRecordset("CaseStudyDate")) = "" Then
                            strCaseStudyDate = Nothing
                        Else
                            strCaseStudyDate = FormatDateTime(Convert.ToString(oRecordset("CaseStudyDate")), DateFormat.ShortDate)
                        End If
                        .CaseStudyID = Convert.ToInt32(oRecordset("CaseStudyID"))
                        .CaseStudyHeadline = Convert.ToString(oRecordset("CaseStudyHeadline"))
                        .CaseStudySummary = Convert.ToString(oRecordset("CaseStudySummary"))
                        .CaseStudyText = Convert.ToString(oRecordset("CaseStudyText"))
                        .CaseStudyDate = strCaseStudyDate
                        .ThumbImage = Convert.ToString(oRecordset("CaseStudyThumbImage"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .Testimonial = Convert.ToString(oRecordset("Testimonial"))
                        .TestimonialSignature = Convert.ToString(oRecordset("TestimonialSignature"))
                        .isCaseStudy = True

                    End With
                End While
                oRecordset.Close()
            End Using
            Return objCaseStudy
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function

    Public Function GetItemSideBar_Product(ByVal caseStudyType As Integer, ByVal productID As Integer) As entMainCaseStudy
        Dim objCaseStudy As New entMainCaseStudy
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainCaseStudyGetByProductAndType")
                db.AddInParameter(dbCommand, "Type", DbType.Int32, caseStudyType)
                db.AddInParameter(dbCommand, "productID", DbType.Int32, productID)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    With objCaseStudy
                        Dim strCaseStudyDate As String
                        If Convert.ToString(oRecordset("CaseStudyDate")) = "" Then
                            strCaseStudyDate = Nothing
                        Else
                            strCaseStudyDate = FormatDateTime(Convert.ToString(oRecordset("CaseStudyDate")), DateFormat.ShortDate)
                        End If
                        .CaseStudyID = Convert.ToInt32(oRecordset("CaseStudyID"))
                        .CaseStudyHeadline = Convert.ToString(oRecordset("CaseStudyHeadline"))
                        .CaseStudySummary = Convert.ToString(oRecordset("CaseStudySummary"))
                        .CaseStudyText = Convert.ToString(oRecordset("CaseStudyText"))
                        .CaseStudyDate = strCaseStudyDate
                        .ThumbImage = Convert.ToString(oRecordset("CaseStudyThumbImage"))
                        .Testimonial = Convert.ToString(oRecordset("Testimonial"))
                        .TestimonialSignature = Convert.ToString(oRecordset("TestimonialSignature"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .isCaseStudy = True

                    End With
                End While
                oRecordset.Close()
            End Using
            Return objCaseStudy
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function


    Public Function GetItemSideBar_Random() As entMainCaseStudy
        Dim objCaseStudy As New entMainCaseStudy
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)
        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainCaseStudyGetRandomForSidebar")
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()
                    With objCaseStudy
                        Dim strCaseStudyDate As String
                        If Convert.ToString(oRecordset("CaseStudyDate")) = "" Then
                            strCaseStudyDate = Nothing
                        Else
                            strCaseStudyDate = FormatDateTime(Convert.ToString(oRecordset("CaseStudyDate")), DateFormat.ShortDate)
                        End If
                        .CaseStudyID = Convert.ToInt32(oRecordset("CaseStudyID"))
                        .CaseStudyHeadline = Convert.ToString(oRecordset("CaseStudyHeadline"))
                        .CaseStudySummary = Convert.ToString(oRecordset("CaseStudySummary"))
                        .CaseStudyText = Convert.ToString(oRecordset("CaseStudyText"))
                        .CaseStudyDate = strCaseStudyDate
                        .ThumbImage = Convert.ToString(oRecordset("CaseStudyThumbImage"))
                        .Testimonial = Convert.ToString(oRecordset("Testimonial"))
                        .TestimonialSignature = Convert.ToString(oRecordset("TestimonialSignature"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .isCaseStudy = True

                    End With
                End While
                oRecordset.Close()
            End Using
            Return objCaseStudy
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function

    Public Function GetItemsByType(ByVal caseStudyType As Integer) As List(Of entMainCaseStudy)
        Dim caseStudies As New List(Of entMainCaseStudy)
        Dim db As Database = DatabaseFactory.CreateDatabase(strMainConnString)

        Try
            Using dbCommand As DbCommand = db.GetStoredProcCommand("MainCaseStudyGetByTypeList")
                db.AddInParameter(dbCommand, "CaseStudyType", DbType.Int32, caseStudyType)
                Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
                While oRecordset.Read()

                    Dim caseStudy As New entMainCaseStudy
                    With caseStudy
                        Dim strCaseStudyDate As String
                        If Convert.ToString(oRecordset("CaseStudyDate")) = "" Then
                            strCaseStudyDate = Nothing
                        Else
                            strCaseStudyDate = FormatDateTime(Convert.ToString(oRecordset("CaseStudyDate")), DateFormat.ShortDate)
                        End If
                        .CaseStudyID = Convert.ToInt32(oRecordset("CaseStudyID"))
                        .CaseStudyHeadline = Convert.ToString(oRecordset("CaseStudyHeadline"))
                        .CaseStudySummary = Convert.ToString(oRecordset("CaseStudySummary"))
                        .CaseStudyText = Convert.ToString(oRecordset("CaseStudyText"))
                        .CaseStudyDate = strCaseStudyDate
                        .ThumbImage = Convert.ToString(oRecordset("CaseStudyThumbImage"))
                        .MainImage = Convert.ToString(oRecordset("CaseStudyMainImage"))
                        .Active = Convert.ToString(oRecordset("Active"))
                        .Deleted = Convert.ToString(oRecordset("Deleted"))
                        .isCaseStudy = True
                        .CreatedDate = FormatDateTime(Convert.ToString(oRecordset("CreatedDate")), DateFormat.ShortDate)
                    End With
                    caseStudies.Add(caseStudy)
                End While
                oRecordset.Close()
            End Using
            Return caseStudies
        Catch ex As Exception
            Throw ex
            Return Nothing
        End Try
    End Function
End Class
