''
'' This code is part of Document Solutions for Word demos.
'' Copyright (c) MESCIUS inc. All rights reserved.
''
Imports System.Data
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports 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 Function
End Class