Public Function ExportBalances(ByVal exportType As String) As Boolean
Dim result As Boolean
Dim balances As DataTable
Dim fields As New HelperClasses.ResultsetFields(19)
Dim relations As IRelationCollection = New RelationCollection
Dim sorter As ISortExpression = New SortExpression
Dim filter As IPredicateExpression = New PredicateExpression
Dim exportFileName As String
'define fields
fields.DefineField(TblKlantFieldIndex.KlantNummer, 0, "KlantNummer")
fields.DefineField(TblKlantFieldIndex.Naam, 1, "KlantNaam")
fields.DefineField(TblBoekingFieldIndex.DepotId, 2, "DepotNummer")
fields.DefineField(TblDepotFieldIndex.DepotNaam, 3, "DepotNaam")
fields.DefineField(TblBoekingFieldIndex.Klm1, 4, "Klm1", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm2, 5, "Klm2", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm3, 6, "Klm3", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm4, 7, "Klm4", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm5, 8, "Klm5", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm6, 9, "Klm6", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm7, 10, "Klm7", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm8, 11, "Klm8", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm9, 12, "Klm9", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm10, 13, "Klm10", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm11, 14, "Klm11", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm12, 15, "Klm12", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm13, 16, "Klm13", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm14, 17, "Klm14", "", AggregateFunction.Sum)
fields.DefineField(TblBoekingFieldIndex.Klm15, 18, "Klm15", "", AggregateFunction.Sum)
'add relations
sorter.Add(FactoryClasses.SortClauseFactory.Create(TblKlantFieldIndex.KlantNummer, SortOperator.Ascending))
filter.Add(FactoryClasses.PredicateFactory.CompareValue(TblBoekingFieldIndex.Datum, ComparisonOperator.LessEqual, Date.Today))
relations.Add(EntityClasses.TblKlantEntity.Relations.TblBoekingEntityUsingKlantNummer)
relations.Add(EntityClasses.TblBoekingEntity.Relations.TblDepotEntityUsingDepotId)
Try
exportFileName = settings.GetConfigValue("strExportFolder")
exportFileName &= "\BalancePerClient_" & Format(Date.Today, "yyyyMMdd")
balances = FetchTypedList(fields, filter, relations, sorter, False, 0, True, Nothing)
balances.TableName = "BalancePerClient"
If balances.Rows.Count > 0 Then
Select Case exportType
Case "XML"
Dim ds As New DataSet("Balances")
ds.Tables.Add(balances)
ds.WriteXml(exportFileName & ".xml")
ds.WriteXmlSchema(exportFileName & ".xsd")
ds.Dispose() : ds = Nothing
Case "CSV"
exportFileName &= ".csv"
Dim output As New StreamWriter(exportFileName, False, System.Text.UnicodeEncoding.Default)
' write out the header row
For Each col As DataColumn In balances.Columns
output.Write(col.ColumnName)
output.Write(delimiter)
Next
output.WriteLine()
' write out each data row
For Each row As DataRow In balances.Rows
For Each value As Object In row.ItemArray
If TypeOf value Is String Then
output.Write(""""c) 'that's four double quotes and a c
output.Write(value)
output.Write(""""c) 'that's four double quotes and a c
Else
output.Write(value)
End If
output.Write(delimiter)
Next
output.WriteLine()
Next
output.Close()
output.Flush()
output = Nothing
End Select
Else
Console.WriteLine("No data found !")
End If
Catch ex As Exception
LogError("ExportBalances", ex.Message)
Finally
balances = Nothing
fields = Nothing
relations = Nothing
sorter = Nothing
End Try
End Function
