'''' This code is part of Document Solutions for Word demos.'' Copyright (c) MESCIUS inc. All rights reserved.''Imports System.DataImports System.GlobalizationImports System.IOImports System.LinqImports GrapeCity.Documents.Word
'' This data template sample prints an invoice containing the list'' of products in the purchase order.'' The invoice data is generated for a randomly selected'' order from the sample DsNWind database.Public Class DataTplOrderInvoice    Function CreateDocx(Optional idx As Integer = 0) As GcWordDocument        Using ds = New DataSet()            '' Load the sample database, fetch a random supplier            '' and a random order from that supplier:            ds.ReadXml(Path.Combine("Resources", "data", "DsNWind.xml"))
            '' Database tables used by the invoice:            Dim dtSuppliers = ds.Tables("Suppliers")            Dim dtOrders = ds.Tables("OrdersCustomersEmployees")            Dim dtOrdersDetails = ds.Tables("EmployeesProductsOrders")
            '' Collect order data:            Dim random = Util.NewRandom()
            Dim fetchedIndex = random.Next(dtSuppliers.Select().Count())            Dim supplier =                dtSuppliers.Select() _                .Skip(fetchedIndex).Take(1) _                .Select(Function(it) New With {                    .SupplierID = Convert.ToInt32(it("SupplierID")),                    .CompanyName = it("CompanyName").ToString(),                    .ContactName = it("ContactName").ToString(),                    .ContactTitle = it("ContactTitle").ToString(),                    .Address = it("Address").ToString(),                    .City = it("City").ToString(),                    .Region = it("Region").ToString(),                    .PostalCode = it("PostalCode").ToString(),                    .Country = it("Country").ToString(),                    .Phone = it("Phone").ToString(),                    .Fax = it("Fax").ToString(),                    .HomePage = it("HomePage").ToString()                }).FirstOrDefault()
            fetchedIndex = random.Next(dtOrders.Select().Count())            Dim order = dtOrders.Select() _                .Skip(fetchedIndex).Take(1) _                .Select(Function(it) New With {                    .OrderDate = it("OrderDate"),                    .OrderID = Convert.ToInt32(it("OrderID")),                    .CompanyName = it("CompanyName").ToString(),                    .Name = $"{it("FirstName")} {it("LastName")}",                    .Address = $"{it("ShipAddress")}," & vbLf & $"{it("ShipCity")} {it("ShipRegion")} {it("ShipPostalCode")} {it("ShipCountry")}",                    .Email = GetEmail(it("FirstName").ToString(), it("LastName").ToString(), it("CompanyName").ToString())                }).FirstOrDefault()
            Dim orderDetails = dtOrdersDetails.Select() _                .Select(Function(it) New With {                    .OrderID = Convert.ToInt32(it("OrderID")),                    .ProductName = it("ProductName").ToString(),                    .UnitPrice = Convert.ToDecimal(it("UnitPrice")),                    .Quantity = Convert.ToDecimal(it("Quantity")),                    .Total = Convert.ToDecimal(it("UnitPrice")) * Convert.ToDecimal(it("Quantity"))                }) _                .Where(Function(it) it.OrderID = order.OrderID) _                .OrderBy(Function(it) it.ProductName).ToList()
            '' Finally, prep the integrated data source for the template:            Dim data = New With {                .o = order,                .ps = orderDetails,                .total = orderDetails.Sum(Function(od_) od_.Total)            }
            '' Load the template DOCX, add the data source and process the template:            Dim doc = New GcWordDocument()            doc.Load(Path.Combine("Resources", "WordDocs", "InvoiceTemplate.docx"))            doc.DataTemplate.DataSources.Add("ds", data)            doc.DataTemplate.Process(CultureInfo.GetCultureInfo("en-US"))
            '' Done:            Return doc        End Using    End Function
    '' Generate a sample email address from other data:    Private Function GetEmail(firstName As String, lastName As String, companyName As String) As String        Dim x = New String(companyName.ToLower().Where(Function(c_) Char.IsLetterOrDigit(c_)).ToArray())        Return $"{firstName.ToLower()}.{Char.ToLower(lastName(0))}@{x}.com"    End Function
    Public Shared Function GetSampleParamsList() As List(Of String())        Return New List(Of String())() From {            New String() {"@data-templates/Order Invoice", "Generate the invoice for a random order fetched from a database", Nothing},            New String() {"@use-data-tpl/Order Invoice", "Generate the invoice for a random order fetched from a database", Nothing}        }    End FunctionEnd Class