Imports Microsoft.VisualBasic
Imports System.IO
Imports BaseClasses.Data
Imports CarlosAg.ExcelXmlWriter

Namespace FileUploadDownload

' The ExportData class exports to Excel and sends the .xls or .csv file to the response stream.
Public Class ExportData

#Region "Properties"
        Private _tbl As BaseTable
        Private Property DBTable() As BaseTable
            Get
                Return Me._tbl
            End Get
            Set(ByVal value As BaseTable)
                Me._tbl = value
            End Set
        End Property

        Private _wc As WhereClause
        Private Property SelectWhereClause() As WhereClause
            Get
                Return Me._wc
            End Get
            Set(ByVal value As WhereClause)
                Me._wc = value
            End Set
        End Property

        Private _orderby As OrderBy
        Private Property SelectOrderBy() As OrderBy
            Get
                Return Me._orderby
            End Get
            Set(ByVal value As OrderBy)
                Me._orderby = value
            End Set
        End Property

        Private _columns() As BaseColumn
        Private Property DisplayColumns() As BaseColumn()
            Get
                Return _columns
            End Get
            Set(ByVal value As BaseColumn())
                Me._columns = value
            End Set
        End Property
        Private _headerString As String
        Private Property HeaderString() As String
            Get
                Return _headerString
            End Get
            Set(ByVal value As String)
                Me._headerString = value
            End Set
        End Property
#End Region

#Region "Constructor"
        Public Sub New(ByVal tbl As BaseTable, ByVal wc As WhereClause, ByVal orderBy As OrderBy, ByVal columns() As BaseColumn)
            DBTable = tbl
            SelectWhereClause = wc
            SelectOrderBy = orderBy
            DisplayColumns = columns
            HeaderString = Nothing
        End Sub
        Public Sub New(ByVal tbl As BaseTable, ByVal wc As WhereClause, ByVal orderBy As OrderBy)
            DBTable = tbl
            SelectWhereClause = wc
            SelectOrderBy = orderBy
            HeaderString = Nothing
        End Sub
        Public Sub New(ByVal tbl As BaseTable, ByVal wc As WhereClause, ByVal orderBy As OrderBy, ByVal columns() As BaseColumn, ByVal header As String)
            DBTable = tbl
            SelectWhereClause = wc
            SelectOrderBy = orderBy
            DisplayColumns = columns
            HeaderString = header
        End Sub
#End Region


#Region "Public Methods"

        Public Sub ExportToCSV(ByVal response As System.Web.HttpResponse)
            Dim done As Boolean = False
            Dim pageIndex As Integer = 0
            Dim pageSize As Integer = 100
            Dim totalRowsReturned As Integer = 0
            Dim rec As BaseRecord
            Dim col As BaseColumn
            Dim val As String
            Dim skip As Boolean
            Dim IsString As Boolean

            ' Use the list separator of the current culture.
            Dim separator As String = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator

            If Not (IsNothing(response)) Then
                Dim fileName As String = DBTable.TableDefinition.Name & ".csv"
                response.ClearHeaders()
                response.Clear()
                response.Cache.SetCacheability(HttpCacheability.Private)
                response.Cache.SetMaxAge(New TimeSpan(0))
                response.Cache.SetExpires(New DateTime(0))
                response.Cache.SetNoServerCaching()
                response.ContentType = "text/plain"
                response.AppendHeader("Content-Disposition", "attachment; filename=""" & fileName & """")
                Dim writer As StreamWriter = New StreamWriter(response.OutputStream, System.Text.Encoding.UTF8)

                ' First write out the Column Headers
                ' If the DisplayString is not null then, write the contents of DisplayString as column headers
                If (Not IsNothing(Me.HeaderString) And Not Me.HeaderString = "") Then
                    writer.Write(HeaderString.Replace("""", """"""))
                Else
                    For Each col In DisplayColumns
                        If Not IsNothing(col) Then
                            If col.ColumnType <> BaseColumn.ColumnTypes.Binary AndAlso _
                               col.ColumnType <> BaseColumn.ColumnTypes.Image Then
                                writer.Write("""" & col.Name.Replace("""", """""") & """" & separator)
                            End If
                        End If
                    Next
                End If
                writer.WriteLine()

                ' Read 100 records at a time and write out the CSV file.
                While (Not done)
                    Dim recList As ArrayList = Nothing
                    If SelectWhereClause.RunQuery Then
                        recList = DBTable.GetRecordList(SelectWhereClause.GetFilter(), SelectOrderBy, pageIndex, pageSize, totalRowsReturned)
                        totalRowsReturned = recList.Count
                    End If

                    If totalRowsReturned <= 0 Then
                        done = True
                    Else
                        For Each rec In recList
                            For Each col In DisplayColumns
                                If Not IsNothing(col) Then
                                    skip = False
                                    val = ""
                                    IsString = True
                                    If col.TableDefinition.IsExpandableNonCompositeForeignKey(col) Then
                                        ' Foreign Key column, so we will use DFKA and String type.
                                        val = rec.Format(col)
                                    Else
                                        Select Case col.ColumnType
                                            Case BaseColumn.ColumnTypes.Binary, _
                                                 BaseColumn.ColumnTypes.Image
                                                ' Skip - do nothing for these columns
                                                skip = True
                                            Case BaseColumn.ColumnTypes.Currency, _
                                                 BaseColumn.ColumnTypes.Number, _
                                                 BaseColumn.ColumnTypes.Percentage
                                                IsString = False
                                                val = rec.Format(col)
                                            Case Else
                                                val = rec.Format(col)
                                        End Select
                                    End If
                                    If IsNothing(val) Then val = ""
                                    If Not skip Then
                                        If (IsString Or val.Contains(separator)) Then
                                            ' If the value contains a separator, enclose the text in quotes.
                                            writer.Write("""" & val.Replace("""", """""") & """")
                                        Else
                                            writer.Write(val)
                                        End If
                                        writer.Write(separator)
                                    End If
                                End If
                            Next
                            writer.WriteLine()
                        Next
                        pageIndex += 1
                        ' If we already are below the pageSize, then we are done.
                        If totalRowsReturned < pageSize Then
                            done = True
                        End If
                    End If
                End While
                writer.Flush()
                response.End()
            End If
        End Sub

        Dim ColumnList As ArrayList = New ArrayList
        Public Sub AddColumn(ByVal col As ExcelColumn)
            Me.ColumnList.Add(col)
        End Sub

        Public Sub ExportToExcel(ByVal response As System.Web.HttpResponse)
            Dim done As Boolean = False
            Dim rec As BaseRecord
            Dim elem As ExcelColumn
            Dim val As String
            Dim skip As Boolean
            Dim skipHeader As Boolean

            If Not IsNothing(response) Then
                Dim excelBook As New CarlosAg.ExcelXmlWriter.Workbook()
                excelBook.Properties.Title = DBTable.TableDefinition.Name
                excelBook.Properties.Created = DateTime.Now
                excelBook.Properties.Author = ""

                Dim excelSheet As CarlosAg.ExcelXmlWriter.Worksheet = excelBook.Worksheets.Add("Sheet1")
                Dim excelRow As CarlosAg.ExcelXmlWriter.WorksheetRow = excelSheet.Table.Rows.Add()
                Dim excelDataType As CarlosAg.ExcelXmlWriter.DataType

                excelSheet.Options.SplitHorizontal = 1
                excelSheet.Options.FreezePanes = True
                excelSheet.Options.TopRowBottomPane = 1

                Dim excelHeaderStyle As CarlosAg.ExcelXmlWriter.WorksheetStyle = excelBook.Styles.Add("HeaderRowStyle")
                excelHeaderStyle.Font.Bold = True
                excelHeaderStyle.Font.FontName = "Verdana"
                excelHeaderStyle.Font.Size = 8
                excelHeaderStyle.Font.Color = "#FFFFFF"
                excelHeaderStyle.Interior.Color = "#000000"
                excelHeaderStyle.Interior.Pattern = StyleInteriorPattern.Solid

                Dim width As Integer = 0

               ' First write out the Column Headers
                For Each elem In ColumnList
                    Dim exCol As ExcelColumn = CType(elem, ExcelColumn)
                    Dim col As BaseColumn = exCol.DisplayColumn
                    If Not (IsNothing(col)) Then
                        skipHeader = False
                        If col.TableDefinition.IsExpandableNonCompositeForeignKey(col) Then
                            'Set width if field is a foreign key field
                            width = 100
                        Else
                            Select Case col.ColumnType
                                Case BaseColumn.ColumnTypes.Binary, _
                                     BaseColumn.ColumnTypes.Image
                                    ' Skip - do nothing for these columns
                                    skipHeader = True
                                Case BaseColumn.ColumnTypes.Currency, _
                                     BaseColumn.ColumnTypes.Number, _
                                     BaseColumn.ColumnTypes.Percentage
                                    width = 60
                                Case BaseColumn.ColumnTypes.String, _
                                    BaseColumn.ColumnTypes.Very_Large_String
                                    width = 110
                                Case Else
                                    width = 50
                            End Select
                        End If
                        If Not skipHeader Then
                            'excelHeaderStyle.NumberFormat = exCol.DisplayFormat
                            excelRow.Cells.Add(New WorksheetCell(col.Name, "HeaderRowStyle"))
                            Dim worksheetColumn As WorksheetColumn = excelSheet.Table.Columns.Add(width)
                        End If
                    End If
                Next

                ' Read 100 records at a time and write out the Excel file.
                Dim pageIndex As Integer = 0
                Dim pageSize As Integer = 100
                Dim totalRowsReturned As Integer = 0
                Dim rowCounter As Integer = 0
                Dim deciNumber as Decimal

                While (Not done)
                    Dim recList As ArrayList = Nothing
                    If SelectWhereClause.RunQuery Then
                        recList = DBTable.GetRecordList(SelectWhereClause.GetFilter(), SelectOrderBy, pageIndex, pageSize, totalRowsReturned)
                        totalRowsReturned = recList.Count
                    End If

                    If totalRowsReturned <= 0 Then
                        done = True
                    Else
                        For Each rec In recList
                            excelRow = excelSheet.Table.Rows.Add()
                            Dim columnCounter As Integer = 0
                            For Each elem In ColumnList
                                Dim exCol As ExcelColumn = CType(elem, ExcelColumn)
                                Dim col As BaseColumn = exCol.DisplayColumn
                                If Not IsNothing(col) Then
                                    skip = False
                                    val = ""
                                    If col.TableDefinition.IsExpandableNonCompositeForeignKey(col) Then
                                        ' Foreign Key column, so we will use DFKA and String type.
                                        excelDataType = DataType.String
                                        val = rec.Format(col)
                                    Else
                                        Select Case col.ColumnType
                                            Case BaseColumn.ColumnTypes.Binary, _
                                                 BaseColumn.ColumnTypes.Image
                                                ' Skip - do nothing for these columns
                                                skip = True

                                            Case BaseColumn.ColumnTypes.Number, _
                                                BaseColumn.ColumnTypes.Percentage
                                                excelDataType = DataType.Number
                                                Dim numVal As ColumnValue = rec.GetValue(col)
                                                ' If the value of the column to be exported is nothing, add an empty cell to the Excel file
                                                If (numVal.IsNull) Then
                                                    excelRow.Cells.Add(New WorksheetCell())
                                                    skip = True
                                                Else
                                                    deciNumber = numVal.ToDecimal
                                                    val = deciNumber.ToString(System.Globalization.CultureInfo.InvariantCulture)
                                                End If

                                            Case BaseColumn.ColumnTypes.Currency
                                                excelDataType = DataType.Number
                                                Dim numVal As ColumnValue = rec.GetValue(col)
                                                ' If the value of the column to be exported is nothing, add an empty cell to the Excel file
                                                If (numVal.IsNull) Then
                                                    excelRow.Cells.Add(New WorksheetCell())
                                                    skip = True
                                                Else
                                                    deciNumber = numVal.ToDecimal
                                                    val = deciNumber.ToString(System.Globalization.CultureInfo.InvariantCulture)                                                                                               
                                                End If

                                            Case BaseColumn.ColumnTypes.Date
                                                excelDataType = DataType.DateTime
                                                Dim dateVal As ColumnValue = rec.GetValue(col)
                                                If (dateVal.IsNull) Then
                                                    excelRow.Cells.Add(New WorksheetCell())
                                                    skip = True
                                                Else
                                                    ' Specify the default Excel format for the date field
                                                    val = rec.Format(col, "s")
                                                    val += ".000"
                                                End If

                                            Case BaseColumn.ColumnTypes.Very_Large_String
                                                excelDataType = DataType.String
                                                val = rec.GetValue(col).ToString
                                            
                                            Case BaseColumn.ColumnTypes.Boolean
                                                excelDataType = DataType.String
                                                val = rec.Format(col)

                                            Case Else
                                                excelDataType = DataType.String
                                                val = rec.Format(col)
                                        End Select
                                    End If
                                    ' Define a unique column style for each column in the table to be exported
                                    If (rowCounter = 0) Then
                                        Dim excelColumnStyle As CarlosAg.ExcelXmlWriter.WorksheetStyle = excelBook.Styles.Add(exCol.DisplayColumn.ToString() & columnCounter)
                                        excelColumnStyle.Font.FontName = "Verdana"
                                        excelColumnStyle.Font.Size = 8
                                        If Not (exCol.DisplayFormat.Equals("Default")) Then
                                            excelColumnStyle.NumberFormat = exCol.DisplayFormat
                                        End If
                                    End If
                                    If Not skip Then
                                        excelRow.Cells.Add(New WorksheetCell(val, excelDataType, exCol.DisplayColumn.ToString() & columnCounter))
                                    End If
                                End If
                                columnCounter += 1
                            Next
                            rowCounter += 1
                        Next
                        pageIndex += 1
                        ' If we already are below the pageSize, then we are done.
                        If totalRowsReturned < pageSize Then
                            done = True
                        End If
                    End If
                End While

                Dim fileName As String
                If (Not IsNothing(DBTable.TableDefinition.Name)) Then
                    fileName = DBTable.TableDefinition.Name & ".xls"
                Else
                    fileName = "Untitled.xls"
                End If
                response.ClearHeaders()
                response.Clear()
                response.Cache.SetCacheability(HttpCacheability.Private)
                response.Cache.SetMaxAge(New TimeSpan(0))
                response.Cache.SetExpires(New DateTime(0))
                response.Cache.SetNoServerCaching()
                response.ContentType = "application/vnd.ms-excel"
                response.AppendHeader("Content-Disposition", "attachment; filename=""" & fileName & """")
                excelBook.Save(response.OutputStream)
                response.End()
            End If
        End Sub
#End Region
End Class

Public Class ExcelColumn
#Region "Properties"
        Private _column As BaseColumn
        Public Property DisplayColumn() As BaseColumn
            Get
                Return _column
            End Get
            Set(ByVal value As BaseColumn)
                Me._column = value
            End Set
        End Property

        Private _format As String
        public Property DisplayFormat() As String
            Get
                Return _format
            End Get
            Set(ByVal value As String)
                Me._format = value
            End Set
        End Property
#End Region

#Region "Constructor"
        Public Sub New(ByVal col As BaseColumn, ByVal format As String)
            DisplayColumn = col
            DisplayFormat = format
        End Sub
#End Region

#Region "Public Methods"

#End Region

End Class

End Namespace